namespace AccountsDAL.Query.AccountReports
{
    // Profit & Loss Dashboard — reads the already-posted FFINANCE fact table (see the plan file's
    // "Finance Dashboard Migration" section) instead of aggregating TVOUCHER live, same family as
    // RatioAnalysisReportQB/WarehouseFactPostingQB. Every measure here is a period-flow SUM (no
    // AsOfBalance split needed, unlike Ratio Analysis) since Revenue/COGS/GrossMargin/OtherIncome/
    // OpertatingExpenses/EBITDA/Depreciation/EBIT/Interest/IncomeTax/NetProfit are all already-posted
    // period-flow columns on FFINANCE.
    public static class AccountProfitLossDashboardQB
    {
        // Reuses the same standalone accessible-OU-list query already built for Ratio Analysis.
        public const string GET_ACCESSIBLE_OU_IDS = @"
SELECT UAR.OUID AS OUID FROM MUSERACCESSRIGHTS UAR WHERE UAR.USERID = @UserId AND UAR.OUID <> -1
UNION
SELECT OGD.OUID AS OUID FROM MUSERACCESSRIGHTS UAR2
INNER JOIN MORGANIZATIONGROUPDETAIL OGD ON OGD.ORGANIZATIONGROUPID = UAR2.OUGROUPID
WHERE UAR2.USERID = @UserId AND UAR2.OUGROUPID <> -1";

        // {GroupByColumns}/{LabelExpr}/{SlNoExpr} are substituted in the DAL from a fixed,
        // allow-listed set of 8 expressions keyed by PeriodType (0=Total..7=FinancialYear) — never
        // built from request input, same discipline as AccountReportSortBuilder/ComparativeAxisMap
        // elsewhere in this codebase. COGSPercent etc. are all "measure / SUM(DIRECTINCOME) * 100",
        // guarded against a zero-revenue bucket, matching legacy's own percent-of-revenue convention.
        public const string GET_PROFIT_LOSS_DASHBOARD = @"
SELECT
    MIN({LabelExpr}) AS Periodtype,
    {SlNoExpr} AS SlNo,
    MIN(d.[Date]) AS FromDate,
    MAX(d.[Date]) AS ToDate,
    SUM(f.DIRECTINCOME) AS Revenue,
    SUM(f.DIRECTEXPENSES) AS COGS,
    CASE WHEN SUM(f.DIRECTINCOME) <> 0 THEN ROUND(SUM(f.DIRECTEXPENSES) / SUM(f.DIRECTINCOME) * 100, 2) ELSE 0 END AS COGSPercent,
    SUM(f.GROSSPROFIT) AS GrossMargin,
    CASE WHEN SUM(f.DIRECTINCOME) <> 0 THEN ROUND(SUM(f.GROSSPROFIT) / SUM(f.DIRECTINCOME) * 100, 2) ELSE 0 END AS GrossMarginPercent,
    SUM(f.INDIRECTINCOME) AS OtherIncome,
    CASE WHEN SUM(f.DIRECTINCOME) <> 0 THEN ROUND(SUM(f.INDIRECTINCOME) / SUM(f.DIRECTINCOME) * 100, 2) ELSE 0 END AS OtherIncomePercent,
    SUM(f.GROSSPROFIT + f.INDIRECTINCOME) AS GPAndOtherIncome,
    CASE WHEN SUM(f.DIRECTINCOME) <> 0 THEN ROUND(SUM(f.GROSSPROFIT + f.INDIRECTINCOME) / SUM(f.DIRECTINCOME) * 100, 2) ELSE 0 END AS GPAndOtherIncomePercent,
    SUM(f.INDIRECTEXPENSES - f.DEPRECIATION - f.INTEREST - f.INCOMETAX) AS OperatingExpenses,
    CASE WHEN SUM(f.DIRECTINCOME) <> 0 THEN ROUND(SUM(f.INDIRECTEXPENSES - f.DEPRECIATION - f.INTEREST - f.INCOMETAX) / SUM(f.DIRECTINCOME) * 100, 2) ELSE 0 END AS OperatingExpensesPercent,
    SUM(f.EBITDA) AS EBITDA,
    CASE WHEN SUM(f.DIRECTINCOME) <> 0 THEN ROUND(SUM(f.EBITDA) / SUM(f.DIRECTINCOME) * 100, 2) ELSE 0 END AS EBITDAPercent,
    SUM(f.DEPRECIATION) AS Depreciation,
    CASE WHEN SUM(f.DIRECTINCOME) <> 0 THEN ROUND(SUM(f.DEPRECIATION) / SUM(f.DIRECTINCOME) * 100, 2) ELSE 0 END AS DepreciationPercent,
    SUM(f.EBIT) AS EBIT,
    CASE WHEN SUM(f.DIRECTINCOME) <> 0 THEN ROUND(SUM(f.EBIT) / SUM(f.DIRECTINCOME) * 100, 2) ELSE 0 END AS EBITPercent,
    SUM(f.INTEREST) AS Interest,
    CASE WHEN SUM(f.DIRECTINCOME) <> 0 THEN ROUND(SUM(f.INTEREST) / SUM(f.DIRECTINCOME) * 100, 2) ELSE 0 END AS InterestPercent,
    SUM(f.INCOMETAX) AS IncomeTax,
    SUM(f.NETPROFIT) AS NetProfit,
    CASE WHEN SUM(f.DIRECTINCOME) <> 0 THEN ROUND(SUM(f.NETPROFIT) / SUM(f.DIRECTINCOME) * 100, 2) ELSE 0 END AS NetProfitPercent
FROM FFINANCE f
INNER JOIN DimDate d ON f.DATEID = d.DateKey
WHERE d.[Date] BETWEEN @FromDate AND @ToDate AND f.OUID IN @OuIds AND f.TENANTID = @TenantId
GROUP BY {GroupByColumns}
ORDER BY MIN(d.DateKey)";
    }
}