-- =============================================
-- Stored Procedure: usp_Dashboard_GetTopRevenueOffices
-- Description: Returns top performing revenue offices based on settlement data
-- Parameters: @StartDate, @EndDate
-- =============================================

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

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

    -- Create temp table for settlement data with taxpayer info
    CREATE TABLE #TempSettlement (
        SettlementID INT,
        AssessmentID INT NULL,
        ServiceBillID INT NULL,
        SettlementAmount DECIMAL(18, 2),
        TaxPayerID INT,
        TaxPayerTypeID INT,
        TaxOfficeID INT NULL,
        TaxOfficeName NVARCHAR(255) NULL
    );

    -- Insert Assessment settlements
    INSERT INTO #TempSettlement (SettlementID, AssessmentID, SettlementAmount, TaxPayerID, TaxPayerTypeID)
    SELECT
        s.SettlementID,
        s.AssessmentID,
        s.SettlementAmount,
        a.TaxPayerID,
        a.TaxPayerTypeID
    FROM Settlement s
    INNER JOIN Assessment a ON s.AssessmentID = a.AssessmentID
    WHERE s.SettlementDate BETWEEN @StartDate AND @EndDate
        AND s.SettlementMethodID < 7
        AND s.AssessmentID IS NOT NULL;

    -- Insert ServiceBill settlements
    INSERT INTO #TempSettlement (SettlementID, ServiceBillID, SettlementAmount, TaxPayerID, TaxPayerTypeID)
    SELECT
        s.SettlementID,
        s.ServiceBillID,
        s.SettlementAmount,
        sb.TaxPayerID,
        sb.TaxPayerTypeID
    FROM Settlement s
    INNER JOIN ServiceBill sb ON s.ServiceBillID = sb.ServiceBillID
    WHERE s.SettlementDate BETWEEN @StartDate AND @EndDate
        AND s.SettlementMethodID < 7
        AND s.ServiceBillID IS NOT NULL;

    -- Update TaxOfficeID for Individual taxpayers (TaxPayerTypeID = 1)
    UPDATE ts
    SET TaxOfficeID = i.TaxOfficeID
    FROM #TempSettlement ts
    INNER JOIN Individual i ON ts.TaxPayerID = i.IndividualID
    WHERE ts.TaxPayerTypeID = 1;

    -- Update TaxOfficeID for Company taxpayers (TaxPayerTypeID = 2)
    UPDATE ts
    SET TaxOfficeID = c.TaxOfficeID
    FROM #TempSettlement ts
    INNER JOIN Company c ON ts.TaxPayerID = c.CompanyID
    WHERE ts.TaxPayerTypeID = 2;

    -- Update TaxOfficeID for Government taxpayers (TaxPayerTypeID = 3)
    UPDATE ts
    SET TaxOfficeID = g.TaxOfficeID
    FROM #TempSettlement ts
    INNER JOIN Government g ON ts.TaxPayerID = g.GovernmentID
    WHERE ts.TaxPayerTypeID = 3;

    -- Update TaxOfficeName
    UPDATE ts
    SET TaxOfficeName = t.TaxOfficeName
    FROM #TempSettlement ts
    INNER JOIN Tax_Offices t ON ts.TaxOfficeID = t.TaxOfficeID;

    -- Return aggregated results
    SELECT
        TaxOfficeName,
        SUM(SettlementAmount) AS SettlementAmount
    FROM #TempSettlement
    WHERE TaxOfficeName IS NOT NULL
    GROUP BY TaxOfficeName
    ORDER BY SUM(SettlementAmount) DESC;

    -- Cleanup
    DROP TABLE #TempSettlement;
END
GO
