using AccountsDAL.DTO.AccountsReports;
using AccountsDAL.Query.AccountReports;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;

namespace AccountsDAL.CustomCode.AccountReports.AccountProfitLossDashboard
{
    // Profit & Loss Dashboard — reads FFINANCE (already posted this session), period-bucketed by
    // PeriodType using DimDate's own calendar/fiscal attributes. See AccountProfitLossDashboardQB.cs
    // for the shared query template and the plan file's "Finance Dashboard Migration" section for
    // the full design.
    public class AccountProfitLossDashboardDAL : IAccountProfitLossDashboardDAL
    {
        private readonly IQueryExecutor _queryExecutor;

        public AccountProfitLossDashboardDAL(IQueryExecutor queryExecutor)
        {
            _queryExecutor = queryExecutor;
        }

        // Fixed, allow-listed (GroupByColumns, LabelExpr, SlNoExpr) triples keyed by PeriodType —
        // never built from request input. All reference only DimDate columns confirmed live on
        // GB5DEMO (WeekOfYear/Month/MonthName/Quarter/QuarterName/Year/YearName/FINANCIALQUARTER/
        // FINANCIALQUARTERNAME/FINANCIALYEAR/FINANCIALYEARNAME).
        private static readonly Dictionary<byte, (string GroupBy, string Label, string SlNo)> PeriodBuckets = new()
        {
            [0] = ("()", "'Total'", "1"),
            [1] = ("d.DateKey", "CONVERT(varchar, d.[Date], 105)", "d.DateKey"),
            [2] = ("d.Year, d.WeekOfYear", "'Week ' + CAST(d.WeekOfYear AS varchar) + ' ' + CAST(d.Year AS varchar)", "d.Year * 100 + d.WeekOfYear"),
            [3] = ("d.Year, d.Month", "d.MonthName + ' ' + CAST(d.Year AS varchar)", "d.Year * 100 + d.Month"),
            [4] = ("d.Year, d.Quarter", "d.QuarterName + ' ' + CAST(d.Year AS varchar)", "d.Year * 10 + d.Quarter"),
            [5] = ("d.Year", "d.YearName", "d.Year"),
            [6] = ("d.FINANCIALYEAR, d.FINANCIALQUARTER", "d.FINANCIALQUARTERNAME", "d.FINANCIALYEAR * 10 + d.FINANCIALQUARTER"),
            [7] = ("d.FINANCIALYEAR", "d.FINANCIALYEARNAME", "d.FINANCIALYEAR"),
        };

        public async Task<AccountProfitLossDashboardResultDTO> GetProfitLossDashboard(
            DateTime fromDate, DateTime toDate, byte periodType, List<int>? ouIds, int ouGroupId,
            LoginDTO loginDTO, CancellationToken ct)
        {
            var accessibleOuIds = (await _queryExecutor
                .QueryAsync<int>(loginDTO, AccountProfitLossDashboardQB.GET_ACCESSIBLE_OU_IDS,
                    new { UserId = loginDTO.UserId }, cancellationToken: ct)
                .ConfigureAwait(false)).ToHashSet();

            var requestedOuIds = await AccountReportFilterBuilder
                .ResolveOuIdsWithGroupAsync(_queryExecutor, loginDTO, ouIds, ouGroupId, ct)
                .ConfigureAwait(false);

            List<int> effectiveOuIds;
            if (requestedOuIds is { Count: > 0 })
            {
                effectiveOuIds = requestedOuIds.Where(accessibleOuIds.Contains).ToList();
                if (effectiveOuIds.Count == 0)
                    throw new InvalidOperationException(
                        "You do not have access to the requested organizational unit(s).");
            }
            else
            {
                effectiveOuIds = accessibleOuIds.ToList();
            }

            var bucket = PeriodBuckets.TryGetValue(periodType, out var b) ? b : PeriodBuckets[0];

            var sql = AccountProfitLossDashboardQB.GET_PROFIT_LOSS_DASHBOARD
                .Replace("{GroupByColumns}", bucket.GroupBy)
                .Replace("{LabelExpr}", bucket.Label)
                .Replace("{SlNoExpr}", bucket.SlNo);

            var rows = (await _queryExecutor
                .QueryAsync<AccountProfitLossDashboardRowDTO>(loginDTO, sql,
                    new { FromDate = fromDate, ToDate = toDate, OuIds = effectiveOuIds, TenantId = loginDTO.ClientId },
                    cancellationToken: ct)
                .ConfigureAwait(false)).ToList();

            var totals = new AccountProfitLossDashboardTotalsDTO
            {
                Revenue = rows.Sum(r => r.Revenue),
                COGS = rows.Sum(r => r.COGS),
                GrossMargin = rows.Sum(r => r.GrossMargin),
                OtherIncome = rows.Sum(r => r.OtherIncome),
                GPAndOtherIncome = rows.Sum(r => r.GPAndOtherIncome),
                OperatingExpenses = rows.Sum(r => r.OperatingExpenses),
                EBITDA = rows.Sum(r => r.EBITDA),
                Depreciation = rows.Sum(r => r.Depreciation),
                EBIT = rows.Sum(r => r.EBIT),
                Interest = rows.Sum(r => r.Interest),
                IncomeTax = rows.Sum(r => r.IncomeTax),
                NetProfit = rows.Sum(r => r.NetProfit),
            };

            return new AccountProfitLossDashboardResultDTO { Items = rows, Totals = totals };
        }
    }
}