-- =============================================
-- Script: Tạo Indexes cho Báo Cáo Doanh Thu Theo Khách Hàng
-- Mục đích: Tối ưu hóa performance cho sp_GetSaleRevenueByCustomer
-- Đặc biệt khi filter theo SaleId
-- =============================================

USE [Ladaer2]
GO

-- Index 1: Covering index cho Order table khi filter theo SaleId
-- Bao gồm tất cả các columns được sử dụng trong WHERE và JOIN
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_Order_SaleId_CustomerId_CreatedTime_IsDeleted' AND object_id = OBJECT_ID('Order'))
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Order_SaleId_CustomerId_CreatedTime_IsDeleted]
    ON [dbo].[Order] ([SaleId], [CustomerId], [CreatedTime], [IsDeleted])
    INCLUDE ([Id])
    WITH (FILLFACTOR = 90);
    
    PRINT 'Created index: IX_Order_SaleId_CustomerId_CreatedTime_IsDeleted on Order table';
END
ELSE
BEGIN
    PRINT 'Index IX_Order_SaleId_CustomerId_CreatedTime_IsDeleted already exists';
END
GO

-- Index 2: Index cho Customer.ManagerId để JOIN với User
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_Customer_ManagerId' AND object_id = OBJECT_ID('Customer'))
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Customer_ManagerId]
    ON [dbo].[Customer] ([ManagerId])
    INCLUDE ([Id], [UserName], [FullName], [PhoneNumber])
    WITH (FILLFACTOR = 90);
    
    PRINT 'Created index: IX_Customer_ManagerId on Customer table';
END
ELSE
BEGIN
    PRINT 'Index IX_Customer_ManagerId already exists';
END
GO

-- Index 3: Composite index cho OrderOps (Status và PhysicalStatus)
-- Vì query filter và aggregate theo cả Status và PhysicalStatus
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_OrderOps_Status_PhysicalStatus' AND object_id = OBJECT_ID('OrderOps'))
BEGIN
    CREATE NONCLUSTERED INDEX [IX_OrderOps_Status_PhysicalStatus]
    ON [dbo].[OrderOps] ([Status], [PhysicalStatus])
    INCLUDE ([Id])
    WITH (FILLFACTOR = 90);
    
    PRINT 'Created index: IX_OrderOps_Status_PhysicalStatus on OrderOps table';
END
ELSE
BEGIN
    PRINT 'Index IX_OrderOps_Status_PhysicalStatus already exists';
END
GO

-- Index 4: Index cho OrderFin để JOIN nhanh
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_OrderFin_Id_Financial' AND object_id = OBJECT_ID('OrderFin'))
BEGIN
    CREATE NONCLUSTERED INDEX [IX_OrderFin_Id_Financial]
    ON [dbo].[OrderFin] ([Id])
    INCLUDE ([Fee], [PickupSubFee], [DeliverySubFee], [CodFee], [InsuranceFee], [ReturnFee], [Discount], [CollectAmount])
    WITH (FILLFACTOR = 90);
    
    PRINT 'Created index: IX_OrderFin_Id_Financial on OrderFin table';
END
ELSE
BEGIN
    PRINT 'Index IX_OrderFin_Id_Financial already exists';
END
GO

-- =============================================
-- Kiểm tra và hiển thị thông tin về các indexes đã tạo
-- =============================================

PRINT '';
PRINT '========================================';
PRINT 'SUMMARY OF INDEXES FOR CUSTOMER REVENUE REPORT';
PRINT '========================================';

SELECT 
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    STUFF((
        SELECT ', ' + c.name
        FROM sys.index_columns ic
        INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
        WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0
        ORDER BY ic.key_ordinal
        FOR XML PATH('')
    ), 1, 2, '') AS KeyColumns,
    STUFF((
        SELECT ', ' + c.name
        FROM sys.index_columns ic
        INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
        WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1
        ORDER BY ic.index_column_id
        FOR XML PATH('')
    ), 1, 2, '') AS IncludedColumns
FROM sys.indexes i
WHERE i.name IN (
    'IX_Order_SaleId_CustomerId_CreatedTime_IsDeleted',
    'IX_Customer_ManagerId',
    'IX_OrderOps_Status_PhysicalStatus',
    'IX_OrderFin_Id_Financial'
)
ORDER BY OBJECT_NAME(i.object_id), i.name;

PRINT '';
PRINT '========================================';
PRINT 'RECOMMENDATIONS';
PRINT '========================================';
PRINT '1. Chạy UPDATE STATISTICS sau khi tạo indexes';
PRINT '2. Monitor query performance với SQL Server Profiler hoặc Extended Events';
PRINT '3. Xem xét REBUILD indexes định kỳ nếu fragmentation > 30%';
PRINT '4. Index IX_Order_SaleId_CustomerId_CreatedTime_IsDeleted là quan trọng nhất cho filter theo SaleId';
PRINT '';

-- =============================================
-- Script để UPDATE STATISTICS (chạy sau khi tạo indexes)
-- =============================================

PRINT 'Updating statistics...';
UPDATE STATISTICS [dbo].[Order];
UPDATE STATISTICS [dbo].[Customer];
UPDATE STATISTICS [dbo].[OrderOps];
UPDATE STATISTICS [dbo].[OrderFin];
PRINT 'Statistics updated successfully!';
GO
