-- ============================================
-- Database Index Optimization Script
-- For MyBiz_ACCGST Application
-- Run this script in SQL Server Management Studio
-- ============================================

-- Index for Bill table queries (most frequently queried)
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_Bill_FYearId_Flag')
BEGIN
    CREATE NONCLUSTERED INDEX IX_Bill_FYearId_Flag 
    ON Bill(FYearId, Flag) 
    INCLUDE (BillNo, CustId, BillDate, GrandTotal);
    PRINT 'Created index: IX_Bill_FYearId_Flag';
END

-- Index for BillDtls table 
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_BillDtls_BillNo_FYearId')
BEGIN
    CREATE NONCLUSTERED INDEX IX_BillDtls_BillNo_FYearId 
    ON BillDtls(BillNo, FYearId) 
    WHERE Flag = 'A';
    PRINT 'Created index: IX_BillDtls_BillNo_FYearId';
END

-- Index for CustomerMaster lookups
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_CustomerMaster_CustId')
BEGIN
    CREATE NONCLUSTERED INDEX IX_CustomerMaster_CustId 
    ON CustomerMaster(Custid) 
    INCLUDE (CustName, MobileNo, Address);
    PRINT 'Created index: IX_CustomerMaster_CustId';
END

-- Index for ProductDtls searches
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_ProductDtls_Flag')
BEGIN
    CREATE NONCLUSTERED INDEX IX_ProductDtls_Flag 
    ON ProductDtls(Flag) 
    INCLUDE (Itemname, ProdId, SaleRate, PurchaseRate);
    PRINT 'Created index: IX_ProductDtls_Flag';
END

-- Index for CustomerOpening (used in reports)
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_CustomerOpening_FYearId_CustId')
BEGIN
    CREATE NONCLUSTERED INDEX IX_CustomerOpening_FYearId_CustId 
    ON CustomerOpening(fYearId, Custid);
    PRINT 'Created index: IX_CustomerOpening_FYearId_CustId';
END

-- Index for BillConfig (accessed on every bill)
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_BillConfig_FYearId')
BEGIN
    CREATE NONCLUSTERED INDEX IX_BillConfig_FYearId 
    ON BillConfig(FYearId);
    PRINT 'Created index: IX_BillConfig_FYearId';
END

PRINT '------------------------------------';
PRINT 'Index optimization complete!';
PRINT '------------------------------------';
