-- =============================================
-- Stored Procedure: usp_Dashboard_GetTopRevenueStreams
-- Description: Returns top revenue streams based on settlement data
-- Parameters: @StartDate, @EndDate
-- =============================================

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Dashboard_GetTopRevenueStreams]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[usp_Dashboard_GetTopRevenueStreams]
GO

CREATE PROCEDURE [dbo].[usp_Dashboard_GetTopRevenueStreams]
    @StartDate DATETIME,
    @EndDate DATETIME
AS
BEGIN
    SET NOCOUNT ON;

    -- Create temp table to hold combined data
    CREATE TABLE #TempRevenueData (
        ItemID INT,
        ItemName NVARCHAR(255),
        RevenueStreamID INT,
        RevenueStreamName NVARCHAR(255),
        SettlementAmount DECIMAL(18, 2),
        SettlementDate DATETIME
    );

    -- Insert Assessment Item settlements
    INSERT INTO #TempRevenueData (ItemID, ItemName, RevenueStreamID, RevenueStreamName, SettlementAmount, SettlementDate)
    SELECT
        mai.AAIID AS ItemID,
        ai.AssessmentItemName AS ItemName,
        ai.RevenueStreamID,
        rs.RevenueStreamName,
        si.SettlementAmount,
        st.SettlementDate
    FROM MAP_Assessment_AssessmentItem mai
    INNER JOIN Assessment_Items ai ON mai.AssessmentItemID = ai.AssessmentItemID
    INNER JOIN MAP_Settlement_SettlementItem si ON mai.AAIID = si.AAIID
    LEFT JOIN Settlement st ON si.SettlementID = st.SettlementID
    INNER JOIN Revenue_Stream rs ON ai.RevenueStreamID = rs.RevenueStreamID
    WHERE st.SettlementMethodID < 7
        AND st.SettlementDate BETWEEN @StartDate AND @EndDate;

    -- Insert Service Bill Item settlements
    INSERT INTO #TempRevenueData (ItemID, ItemName, RevenueStreamID, RevenueStreamName, SettlementAmount, SettlementDate)
    SELECT
        smsi.SBSIID AS ItemID,
        msi.MDAServiceItemName AS ItemName,
        msi.RevenueStreamID,
        rs.RevenueStreamName,
        si.SettlementAmount,
        st.SettlementDate
    FROM MAP_ServiceBill_MDAServiceItem smsi
    INNER JOIN MDA_Service_Items msi ON smsi.MDAServiceItemID = msi.MDAServiceItemID
    INNER JOIN MAP_Settlement_SettlementItem si ON smsi.SBSIID = si.SBSIID
    LEFT JOIN Settlement st ON si.SettlementID = st.SettlementID
    INNER JOIN Revenue_Stream rs ON msi.RevenueStreamID = rs.RevenueStreamID
    WHERE st.SettlementMethodID < 7
        AND st.SettlementDate BETWEEN @StartDate AND @EndDate;

    -- Return aggregated results
    SELECT
        RevenueStreamName,
        SUM(SettlementAmount) AS SettlementAmount
    FROM #TempRevenueData
    GROUP BY RevenueStreamName
    ORDER BY SUM(SettlementAmount) DESC;

    -- Cleanup
    DROP TABLE #TempRevenueData;
END
GO
