using System.Globalization;
using System.Text.Json;
using System.Runtime.CompilerServices;
using Dapper;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.GB5Exception;
using GB5Shared.QueryExecutor;
using Microsoft.Extensions.Logging;
using PayRollDAL.CustomeCode.PayConfiguration;
using PayRollDAL.DTO.Employee;
using PayRollDAL.DTO.PayConfiguration;
using PayRollDAL.DTO.PayProcess;
using PayRollDAL.Query.PayProcess;
using static GB5Shared.GB5Constant.Constant;

namespace PayRollDAL.CustomeCode.PayProcess;

/// <summary>
/// Ported from GB4 PayProcessDAL.SalaryAbstractSummary
/// (D:\GB4Project\GB4Solution\DAL\PayrollDAL\CustomeCode\PayProcess\PayProcessDAL.cs:797-1533).
/// Covers SalaryAbstractType 0-7 ("summary" mode, GbPeriodId == 0). Types 8/9 and the
/// period-comparison mode (GbPeriodId != 0) depend on MGBPERIOD/MGBPERIODDETAIL, which no other
/// GB5 code touches today, and are rejected rather than silently mis-reported. The BRFL
/// client-specific bank-details merge (GB4's SalaryAbstractType==2 && OUType==0 branch,
/// hardcoded to one client's MGCM/bank surrogate IDs) is intentionally not ported.
/// </summary>
public sealed class SalaryAbstractSummaryDAL(
    IQueryExecutor queryExecutor,
    IPayConfigurationDAL payConfigurationDAL,
    ILogger<SalaryAbstractSummaryDAL> logger) : ISalaryAbstractSummaryDAL
{
    public async Task<object> GetSalaryAbstractSummary(
        int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO,
        CancellationToken ct = default)
    {
        var rows = await QueryRows(criteriaDTO, loginDTO, ct);
        return ApplyPaging(rows, firstNumber, maxResult);
    }

    public async IAsyncEnumerable<SalaryAbstractSummaryDTO> GetSalaryAbstractSummaryStream(
        int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO,
        [EnumeratorCancellation] CancellationToken ct = default)
    {
        var rows = await QueryRows(criteriaDTO, loginDTO, ct);
        foreach (var row in ApplyPaging(rows, firstNumber, maxResult))
        {
            ct.ThrowIfCancellationRequested();
            yield return row;
        }
    }

    private async Task<List<SalaryAbstractSummaryDTO>> QueryRows(
        CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct)
    {
        var criteria = SalaryAbstractCriteria.From(criteriaDTO);

        var ouFilterIds = await ResolveOuFilterIds(criteria, loginDTO, ct);
        var pivot = await BuildPivotExpression(criteria.PayConfigurationId, loginDTO);
        var grouping = GroupingByType[criteria.SalaryAbstractType];

        var parameters = new DynamicParameters();
        parameters.Add("payconfigid", criteria.PayConfigurationId);

        string sql;
        if (criteria.SalaryAbstractType == 7)
        {
            sql = loginDTO.DatabaseType switch
            {
                DBType.SQL => SalaryAbstractSummaryQB.COSTCENTER_SQL,
                DBType.PostGre => SalaryAbstractSummaryQB.COSTCENTER_POSTGRESQL,
                _ => throw new NotSupportedException($"Database type '{loginDTO.DatabaseType}' is not supported.")
            };
        }
        else
        {
            sql = loginDTO.DatabaseType switch
            {
                DBType.SQL => SalaryAbstractSummaryQB.SUMMARY_SQL,
                DBType.PostGre => SalaryAbstractSummaryQB.SUMMARY_POSTGRESQL,
                _ => throw new NotSupportedException($"Database type '{loginDTO.DatabaseType}' is not supported.")
            };
        }

        var ouColumn = loginDTO.DatabaseType == DBType.PostGre ? "ou.ouid" : "OU.OUID";
        var ouFilter = ouFilterIds.Count > 0 ? $"AND {ouColumn} IN @ouids" : string.Empty;
        if (ouFilterIds.Count > 0)
            parameters.Add("ouids", ouFilterIds);

        // GB4 validates PeriodFromDate/PeriodToDate/PayPeriodId as required inputs but resolves
        // the period range and pay-period id to a filter via its generic ApplyCriteria mapping
        // (PayProcessDAL.cs:1544-1547: PeriodFromDate/PeriodToDate -> payperiod.FromDate/ToDate,
        // PayPeriodId -> payperiod.PayPeriodId). The exact operator used by that generic helper
        // isn't visible in the GB4 source available here; a from/to overlap range (matching the
        // interpretation the prior GB5 port already used for this same field pair) is applied
        // when the dates are supplied, and an equality filter when PayPeriodId is supplied.
        var extraFilter = new List<string>();
        var periodCol = loginDTO.DatabaseType == DBType.PostGre ? "ppy" : "PPY";
        if (criteria.PeriodFromDate is not null && criteria.PeriodToDate is not null)
        {
            extraFilter.Add($"AND {periodCol}.FROMDATE <= @periodtodate AND {periodCol}.TODATE >= @periodfromdate");
            parameters.Add("periodfromdate", criteria.PeriodFromDate.Value.Date);
            parameters.Add("periodtodate", criteria.PeriodToDate.Value.Date);
        }
        if (criteria.PayPeriodId is > 0)
        {
            extraFilter.Add($"AND {periodCol}.PAYPERIODID = @payperiodid");
            parameters.Add("payperiodid", criteria.PayPeriodId.Value);
        }

        sql = sql
            .Replace("{HeaderReplacePart}", grouping.HeaderReplacePart(loginDTO.DatabaseType))
            .Replace("{GroupByReplacePart}", grouping.GroupByReplacePart(loginDTO.DatabaseType))
            .Replace("{OrderByReplacePart}", grouping.OrderByReplacePart(loginDTO.DatabaseType))
            .Replace("{AddOnValuesReplacePart}", pivot)
            .Replace("{OuFilter}", ouFilter)
            .Replace("{ExtraFilter}", string.Join(" ", extraFilter));

        var rows = (await queryExecutor.QueryAsync<SalaryAbstractSummaryDTO>(loginDTO, sql, parameters)).ToList();
        foreach (var row in rows)
            row.SalaryAbstractType = criteria.SalaryAbstractType;

        logger.LogInformation(
            "SalaryAbstractSummary returned {RowCount} row(s). PayConfigurationId={PayConfigurationId}, " +
            "SalaryAbstractType={SalaryAbstractType}, OuIds={OuIds}",
            rows.Count, criteria.PayConfigurationId, criteria.SalaryAbstractType, string.Join(',', ouFilterIds));
        return rows;
    }

    private async Task<List<int>> ResolveOuFilterIds(SalaryAbstractCriteria criteria, LoginDTO loginDTO, CancellationToken ct)
    {
        // Two-layer OU filter, matching GB4's PayProcessDAL.SalaryAbstractSummary (source
        // lines 990-1022) and the equivalent already-ported GB5 GetPaySlipReport
        // (PayProcessDAL.cs:348-418): restrict to OUs the user has access rights to, further
        // narrowed to the criteria's OuIds when supplied, else the caller's WorkOUId.
        var accessSql = loginDTO.DatabaseType switch
        {
            DBType.SQL => PayProcessQB.USER_OU_ACCESS_RIGHTS_REPORT,
            DBType.PostGre => PayProcessQB.USER_OU_ACCESS_RIGHTS_REPORT_PG,
            _ => throw new NotSupportedException($"Database type '{loginDTO.DatabaseType}' is not supported.")
        };
        var userAccessOus = (await queryExecutor.QueryAsync<UserAccessOUReportDTO>(
            loginDTO, accessSql, new { userid = loginDTO.UserId })).Select(r => r.OUId).ToHashSet();

        var requested = criteria.OuIds.Count > 0 ? criteria.OuIds.ToHashSet() : new HashSet<int> { loginDTO.WorkOUId };
        if (userAccessOus.Count == 0)
            return requested.ToList();

        return requested.Intersect(userAccessOus).ToList();
    }

    private async Task<string> BuildPivotExpression(int payConfigurationId, LoginDTO loginDTO)
    {
        // Same technique as the already-ported PayProcessDAL.GetPaySlipReport's AddonReplaceMent
        // (PayRollDAL/CustomeCode/PayProcess/PayProcessDAL.cs:466-493), wrapped in SUM(...) to
        // match GB4's SALARY_ABSTRACT_SUMMARY/SALARY_ABSTRACT_COST_CENTER pivot column.
        var payConfiguration = await payConfigurationDAL.GetPayConfiguration(payConfigurationId, loginDTO);
        var isPg = loginDTO.DatabaseType == DBType.PostGre;
        var ppaCol = isPg ? "ppa" : "PPA";

        var pivot = new System.Text.StringBuilder(isPg ? "SUM(CASE pcd.fieldcode " : "SUM(CASE PCD.FIELDCODE ");
        foreach (var detail in payConfiguration.PayConfigurationDetailArray)
        {
            var column = detail.PayConfigurationDetailFieldType switch
            {
                0 => $"{ppaCol}.{detail.PayConfigurationDetailFieldCode}",
                1 => $"{ppaCol}.F_{detail.PayConfigurationDetailFieldCode}",
                3 => $"{ppaCol}.A_{detail.PayConfigurationDetailFieldCode}",
                _ => null
            };
            if (column is null)
                continue;

            pivot.Append(" WHEN '").Append(detail.PayConfigurationDetailFieldCode).Append("' THEN ").Append(column);
        }
        pivot.Append(" ELSE 0 END) AS ");
        pivot.Append(isPg ? "\"FieldValue\"" : "FieldValue");
        return pivot.ToString();
    }

    private static IEnumerable<SalaryAbstractSummaryDTO> ApplyPaging(
        IReadOnlyList<SalaryAbstractSummaryDTO> rows, int firstNumber, int maxResult) =>
        firstNumber > 0 && maxResult > 0
            ? rows.Skip(firstNumber - 1).Take(maxResult)
            : rows;

    // Per-SalaryAbstractType grouping SELECT/GROUP BY/ORDER BY columns, ported from the
    // corresponding GB4 branch (PayProcessDAL.cs line ranges noted per type).
    private sealed record Grouping(
        Func<int, string> HeaderReplacePart, Func<int, string> GroupByReplacePart, Func<int, string> OrderByReplacePart);

    // TotalCount is a per-group employee headcount, computed the same way GB4 does (a
    // COUNT(*)-grouped MEMPLOYEE subquery joined back on the grouping key, wrapped in MAX()
    // since it's constant per group) but as a correlated subquery rather than GB4's separately
    // joined derived table - simpler, same result.
    private static string TotalCountSql(bool isPg, string employeeColumn) =>
        isPg
            ? $"MAX((SELECT COUNT(*) FROM memployee me2 WHERE me2.{employeeColumn} = e.{employeeColumn})) AS \"TotalCount\","
            : $"MAX((SELECT COUNT(*) FROM MEMPLOYEE ME2 WHERE ME2.{employeeColumn} = E.{employeeColumn})) AS TotalCount,";

    private static readonly Dictionary<int, Grouping> GroupingByType = new()
    {
        // 0: Department - PayProcessDAL.cs:1037-1064 (TotalCount joined on employee.DEPARTMENTID)
        [0] = new Grouping(
            db => (db == DBType.PostGre ? "d.departmentcode AS \"Code\", d.departmentname AS \"Name\", " : "D.DEPARTMENTCODE AS Code, D.DEPARTMENTNAME AS Name, ") + TotalCountSql(db == DBType.PostGre, "DEPARTMENTID"),
            db => db == DBType.PostGre ? "d.departmentcode, d.departmentname," : "D.DEPARTMENTCODE, D.DEPARTMENTNAME,",
            db => db == DBType.PostGre ? "d.departmentcode," : "D.DEPARTMENTCODE,"),
        // 1: EmployeeType - PayProcessDAL.cs:1083-1112 (TotalCount joined on employee.EmployeeTypeID)
        [1] = new Grouping(
            db => (db == DBType.PostGre ? "gcm.gcmcode AS \"Code\", gcm.gcmname AS \"Name\", " : "GCM.GCMCODE AS Code, GCM.GCMNAME AS Name, ") + TotalCountSql(db == DBType.PostGre, "EMPLOYEETYPEID") + (db == DBType.PostGre ? " gcm.sortorder AS \"SortOrder\"," : " GCM.SORTORDER AS SortOrder,"),
            db => db == DBType.PostGre ? "gcm.sortorder, gcm.gcmcode, gcm.gcmname," : "GCM.SORTORDER, GCM.GCMCODE, GCM.GCMNAME,",
            db => db == DBType.PostGre ? "gcm.sortorder," : "GCM.SORTORDER,"),
        // 2: OU - PayProcessDAL.cs:1135-1163 (TotalCount joined on employee.WORKOUID, not
        // OU.OUID - GB4 counts employees whose *home* OU matches, not the pay-processed OU;
        // BRFL bank-details merge intentionally not ported)
        [2] = new Grouping(
            db => (db == DBType.PostGre ? "ou.organizationunitcode AS \"Code\", ou.organizationunitname AS \"Name\", " : "OU.ORGANIZATIONUNITCODE AS Code, OU.ORGANIZATIONUNITNAME AS Name, ") + TotalCountSql(db == DBType.PostGre, "WORKOUID"),
            db => db == DBType.PostGre ? "ou.organizationunitcode, ou.organizationunitname," : "OU.ORGANIZATIONUNITCODE, OU.ORGANIZATIONUNITNAME,",
            db => db == DBType.PostGre ? "ou.organizationunitcode," : "OU.ORGANIZATIONUNITCODE,"),
        // 3: Overall - PayProcessDAL.cs:1183-1199 (no grouping columns, single bucket)
        [3] = new Grouping(_ => string.Empty, _ => string.Empty, _ => string.Empty),
        // 4: StaffType - PayProcessDAL.cs:1215-1242 (TotalCount joined on employee.stafftypeid)
        [4] = new Grouping(
            db => (db == DBType.PostGre ? "stgcm.gcmcode AS \"Code\", stgcm.gcmname AS \"Name\", " : "STGCM.GCMCODE AS Code, STGCM.GCMNAME AS Name, ") + TotalCountSql(db == DBType.PostGre, "STAFFTYPEID"),
            db => db == DBType.PostGre ? "stgcm.gcmcode, stgcm.gcmname," : "STGCM.GCMCODE, STGCM.GCMNAME,",
            db => db == DBType.PostGre ? "stgcm.gcmcode," : "STGCM.GCMCODE,"),
        // 5: WorkType - PayProcessDAL.cs:1262-1289 (TotalCount joined on employee.worktypeid)
        [5] = new Grouping(
            db => (db == DBType.PostGre ? "wtgcm.gcmcode AS \"Code\", wtgcm.gcmname AS \"Name\", " : "WTGCM.GCMCODE AS Code, WTGCM.GCMNAME AS Name, ") + TotalCountSql(db == DBType.PostGre, "WORKTYPEID"),
            db => db == DBType.PostGre ? "wtgcm.gcmcode, wtgcm.gcmname," : "WTGCM.GCMCODE, WTGCM.GCMNAME,",
            db => db == DBType.PostGre ? "wtgcm.gcmcode," : "WTGCM.GCMCODE,"),
        // 6: PayGroup - PayProcessDAL.cs:1308-1340. GB4's TotalCount subquery for this branch is
        // joined into the FROM list with no correlating predicate (DownReplacePart is left
        // empty), an unintentional cross join that would multiply every SUM() in the row by the
        // pay-group count - deliberately not reproduced; TotalCount here is instead correctly
        // correlated on employee.PAYGROUPID, matching GB4's evident intent without its bug.
        [6] = new Grouping(
            db => (db == DBType.PostGre ? "mpg.paygroupcode AS \"Code\", mpg.paygroupname AS \"Name\", " : "MPG.PAYGROUPCODE AS Code, MPG.PAYGROUPNAME AS Name, ") + TotalCountSql(db == DBType.PostGre, "PAYGROUPID"),
            db => db == DBType.PostGre ? "mpg.paygroupcode, mpg.paygroupname," : "MPG.PAYGROUPCODE, MPG.PAYGROUPNAME,",
            db => db == DBType.PostGre ? "mpg.paygroupcode," : "MPG.PAYGROUPCODE,"),
        // 7: CostCenter - PayProcessDAL.cs:1365-1392 (TotalCount not selected in GB4 either)
        [7] = new Grouping(
            db => db == DBType.PostGre ? "cc.costcentercode AS \"Code\", cc.costcentername AS \"Name\"," : "CC.COSTCENTERCODE AS Code, CC.COSTCENTERNAME AS Name,",
            db => db == DBType.PostGre ? "cc.costcentercode, cc.costcentername," : "CC.COSTCENTERCODE, CC.COSTCENTERNAME,",
            db => db == DBType.PostGre ? "cc.costcentercode," : "CC.COSTCENTERCODE,"),
    };

    private sealed class SalaryAbstractCriteria
    {
        public int PayConfigurationId { get; private init; }
        public int SalaryAbstractType { get; private init; }
        public int? PayPeriodId { get; private init; }
        public DateTime? PeriodFromDate { get; private init; }
        public DateTime? PeriodToDate { get; private init; }
        public List<int> OuIds { get; } = [];

        public static SalaryAbstractCriteria From(CriteriaDTO? dto)
        {
            int payConfigurationId = 0, salaryAbstractType = 0, gbPeriodId = 0, monthlyWage = -1;
            int? payPeriodId = null;
            DateTime? periodFrom = null, periodTo = null;
            var ouIds = new List<int>();

            foreach (var attribute in dto?.SectionCriteriaList?
                         .SelectMany(s => s.AttributesCriteriaList ?? []) ?? [])
            {
                var field = attribute.FieldName?.Trim().ToLowerInvariant();
                switch (field)
                {
                    case "payconfigurationid": payConfigurationId = ParseInt(attribute.FieldValue) ?? 0; break;
                    case "salaryabstracttype": salaryAbstractType = ParseInt(attribute.FieldValue) ?? 0; break;
                    case "gbperiodid": gbPeriodId = ParseInt(attribute.FieldValue) ?? 0; break;
                    case "payperiodid": payPeriodId = ParseInt(attribute.FieldValue); break;
                    case "periodfromdate": periodFrom = ParseDate(attribute.FieldValue); break;
                    case "periodtodate": periodTo = ParseDate(attribute.FieldValue); break;
                    case "monthlywage": monthlyWage = ParseInt(attribute.FieldValue) ?? -1; break;
                    case "ouid": ouIds.AddRange(ParseIntList(attribute.FieldValue)); break;
                }
            }

            if (payConfigurationId == 0)
                throw new NotFoundException("Supply Criteria does not have PayConfigurationId filter or value is 0, please check.");
            if (salaryAbstractType is 8 or 9)
                throw new MethodNotAllowedException(
                    $"SalaryAbstractType {salaryAbstractType} (period-wise comparison) is not supported - it requires MGBPERIOD/MGBPERIODDETAIL, which this GB5 build does not populate.");
            if (salaryAbstractType is < 0 or > 7)
                throw new NotFoundException($"Unknown SalaryAbstractType '{salaryAbstractType}'.");
            if (gbPeriodId != 0)
                throw new MethodNotAllowedException(
                    "SalaryAbstractSummary's period-wise comparison mode (GbPeriodId != 0) is not supported - it requires MGBPERIOD/MGBPERIODDETAIL, which this GB5 build does not populate.");

            // GB4 validates these as required (PayProcessDAL.cs:883-902) but does not apply them
            // as a query filter for the "summary" branches ported here beyond what's noted above.
            if (monthlyWage == 0)
            {
                if (periodFrom is null)
                    throw new NotFoundException("Supply Criteria does not have PeriodFromDate filter or value is empty, please check.");
                if (periodTo is null)
                    throw new NotFoundException("Supply Criteria does not have PeriodToDate filter or value is empty, please check.");
            }
            else if (payPeriodId is null or 0 && periodFrom is null && periodTo is null)
            {
                throw new NotFoundException("Supply Criteria does not have PayPeriodId filter or value is 0, please check.");
            }

            return new SalaryAbstractCriteria
            {
                PayConfigurationId = payConfigurationId,
                SalaryAbstractType = salaryAbstractType,
                PayPeriodId = payPeriodId,
                PeriodFromDate = periodFrom,
                PeriodToDate = periodTo
            }.WithOuIds(ouIds);
        }

        private SalaryAbstractCriteria WithOuIds(IEnumerable<int> ouIds)
        {
            OuIds.AddRange(ouIds.Distinct());
            return this;
        }

        private static int? ParseInt(object? value) => int.TryParse(Raw(value), out var number) ? number : null;

        private static IEnumerable<int> ParseIntList(object? value) => Raw(value)
            .Split(',', StringSplitOptions.RemoveEmptyEntries | StringSplitOptions.TrimEntries)
            .Select(value => int.TryParse(value, out var id) ? id : 0)
            .Where(id => id != 0);

        private static DateTime? ParseDate(object? value)
        {
            var raw = Raw(value);
            if (long.TryParse(raw, out var epoch))
            {
                try
                {
                    return epoch > 100_000_000_000
                        ? DateTimeOffset.FromUnixTimeMilliseconds(epoch).LocalDateTime
                        : DateTimeOffset.FromUnixTimeSeconds(epoch).LocalDateTime;
                }
                catch (ArgumentOutOfRangeException) { return null; }
            }
            return DateTime.TryParse(raw, CultureInfo.InvariantCulture, DateTimeStyles.None, out var date) ? date : null;
        }

        private static string Raw(object? value) => value is JsonElement element
            ? element.ValueKind == JsonValueKind.String ? element.GetString() ?? string.Empty : element.GetRawText()
            : value?.ToString() ?? string.Empty;
    }
}