-- =============================================
-- Stored Procedure: usp_Dashboard_GetAssetContribution
-- Description: Returns top asset contribution by asset type (Business, Building, Land, Vehicle)
-- Parameters: @StartDate, @EndDate
-- =============================================

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

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

    CREATE TABLE #TempSettlementItem (
        siid INT,
        AAIID INT NULL,
        SettlementAmount DECIMAL(18, 2),
        assettypeid INT,
        ASSETtypeName NVARCHAR(255) NULL
    );

    -- Insert settlement items for the period
    INSERT INTO #TempSettlementItem (siid, AAIID, SettlementAmount)
    SELECT
        si.SIID,
        si.AAIID,
        si.SettlementAmount
    FROM MAP_Settlement_SettlementItem si
    INNER JOIN Settlement s ON si.SettlementID = s.SettlementID
    WHERE s.SettlementDate BETWEEN @StartDate AND @EndDate
        AND s.SettlementMethodID < 7
        AND si.aaiid IS NOT NULL;

    -- Update Assessment Item details with asset type info
    UPDATE #TempSettlementItem
    SET
        assettypeid = mar.assettypeid,
        ASSETtypeName = ats.assettypename
    FROM #TempSettlementItem tsi
    LEFT JOIN MAP_Assessment_AssessmentItem mai ON tsi.aaiid = mai.AAIID
    LEFT JOIN MAP_Assessment_AssessmentRule mar ON mai.AARID = mar.AARID
    LEFT JOIN Asset_Types ats ON mar.AssetTypeID = ats.AssetTypeID;

    -- Return final results grouped by asset type
    SELECT
        a.assettypename,
        SUM(tsi.settlementamount) AS revenue
    FROM #TempSettlementItem tsi
    RIGHT JOIN Asset_Types a ON a.AssetTypeName = tsi.ASSETtypeName
    GROUP BY a.assettypeid, a.ASSETtypeName, tsi.assettypeid, tsi.ASSETtypeName
    ORDER BY revenue DESC;

    -- Cleanup
    DROP TABLE #TempSettlementItem;
END
GO
