using Dapper;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using PayRollDAL.DTO.OverTime;
using PayRollDAL.DTO.Periodic;
using PayRollDAL.Query.OverTime;
using PayRollDAL.Query.Periodic;
using System.Data.Common;
using System.Linq;
using System.Threading.Tasks;

namespace PayRollDAL.CustomeCode.Periodic
{
    public class PeriodicDAL : IPeriodicDAL
    {
        private readonly IQueryExecutor _queryExecutor;

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

        public async Task<int> GetMainSubTypeId(int employeeId, int ouId, int payPeriodId, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            // ISNULL(MAX(...), -1) in the SQL guarantees exactly one row back.
            return await _queryExecutor.ExecuteScalarAsync<int>(
                loginDTO, PeriodicQB.GET_MAIN_SUBTYPEID_FOR_EMPLOYEE,
                new { EmployeeId = employeeId, OuId = ouId, PayPeriodId = payPeriodId }, transaction);
        }

        public async Task<int> GetMMDetailId(int payPeriodId, int employeeId, string? partyCode, string? partyBranchCode, string? workTypeCode, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            // TOP 1 with no ISNULL wrapper — no matching row returns null, not a default value.
            var result = await _queryExecutor.ExecuteScalarAsync<int?>(
                loginDTO, PeriodicQB.GET_MMDETAILID_FOR_EMPLOYEE,
                new
                {
                    PayPeriodId = payPeriodId,
                    EmployeeId = employeeId,
                    PartyCode = partyCode ?? "",
                    PartyBranchCode = partyBranchCode ?? "",
                    WorkTypeCode = workTypeCode ?? ""
                }, transaction);
            return result ?? -1;
        }

        public async Task<OverTimeEmployeeReportingInfoDTO?> GetEmployeeReportingInfo(int employeeId, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            return await _queryExecutor.QuerySingleAsync<OverTimeEmployeeReportingInfoDTO>(
                loginDTO, OverTimeQB.GET_EMPLOYEE_REPORTING_INFO,
                new { EmployeeId = employeeId, TenantId = loginDTO.ClientId }, transaction);
        }

        public async Task<int> SavePeriodic(PeriodicDTO periodicDTO, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            await _queryExecutor.ExecuteAsync(loginDTO, PeriodicQB.INSERT_PERIODIC, periodicDTO, transaction);
            return periodicDTO.PeriodicId;
        }

        public async Task<int> UpdatePeriodic(PeriodicDTO periodicDTO, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            await _queryExecutor.ExecuteAsync(loginDTO, PeriodicQB.UPDATE_PERIODIC, periodicDTO, transaction);
            return periodicDTO.PeriodicId;
        }
        public async Task<IEnumerable<PeriodicDTO>> GetPeriodicList(
    CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, CancellationToken ct = default)
        {
            try
            {
                var filter = ExtractPeriodicFilter(CriteriaDTO);

                return await _queryExecutor.QueryAsync<PeriodicDTO>(
                    LoginDTO,
                    PeriodicQB.GET_PERIODIC_LIST,
                    new
                    {
                        payperiodid = filter.PayPeriodId,
                        applicable = filter.Applicable,
                        ouid = filter.OUId,
                        paygroupid = filter.PayGroupId,
                        payconfigurationid = filter.PayConfigurationId,
                        dagroupid = filter.DAGroupId,
                        employeeid = filter.EmployeeId
                    },
                    cancellationToken: ct);
            }
            catch (Exception)
            {
                throw;
            }
        }

        // Pulls the 7 known Periodic filter fields out of the generic criteria payload.
        // A field is only applied as a filter when it's actually present in
        // AttributesCriteriaList — its value (including -1, a real "NONE" FK value in this
        // schema) is then used verbatim. Fields not sent are left null (unfiltered).
        // FieldName is matched both as a flat name ("OUId") and a dotted GB4-style name
        // ("OU.Id"). OperationType.Equal and .In (used here with a single scalar value, no
        // InArray) are both treated as an equality match — mirrors the two real request
        // bodies this endpoint has actually received.
        private static PeriodicFilterDTO ExtractPeriodicFilter(CriteriaDTO? criteriaDTO)
        {
            var filter = new PeriodicFilterDTO();
            if (criteriaDTO?.SectionCriteriaList == null) return filter;

            foreach (var section in criteriaDTO.SectionCriteriaList)
            {
                if (section?.AttributesCriteriaList == null) continue;

                foreach (var attr in section.AttributesCriteriaList)
                {
                    if (attr == null) continue;
                    if (attr.OperationType != CriteriaDTO.OperationType.Equal
                        && attr.OperationType != CriteriaDTO.OperationType.In)
                        continue;
                    if (attr.FieldValue == null || !int.TryParse(attr.FieldValue.ToString(), out int value))
                        continue;

                    switch (NormalizeFieldName(attr.FieldName))
                    {
                        case "payperiodid": filter.PayPeriodId = value; break;
                        case "applicable": filter.Applicable = value; break;
                        case "ouid": filter.OUId = value; break;
                        case "paygroupid": filter.PayGroupId = value; break;
                        case "payconfigurationid": filter.PayConfigurationId = value; break;
                        case "dagroupid": filter.DAGroupId = value; break;
                        case "employeeid": filter.EmployeeId = value; break;
                    }
                }
            }

            return filter;
        }

        private static string NormalizeFieldName(string? fieldName)
            => (fieldName ?? "").Replace(".", "").ToLowerInvariant();
        public async Task<bool> ValidatePayGroupId(int payGroupId, LoginDTO loginDTO)
        {
            if (payGroupId <= 0) return true; // Skip validation for default/negative values
            const string sql = "SELECT COUNT(1) FROM MPAYGROUP WHERE PAYGROUPID = @PayGroupId";
            var count = await _queryExecutor.ExecuteScalarAsync<int>(loginDTO, sql, new { PayGroupId = payGroupId });
            return count > 0;
        }

        public async Task<bool> ValidatePayPeriodId(int payPeriodId, LoginDTO loginDTO)
        {
            if (payPeriodId <= 0) return true;
            const string sql = "SELECT COUNT(1) FROM MPAYPERIOD WHERE PAYPERIODID = @PayPeriodId";
            var count = await _queryExecutor.ExecuteScalarAsync<int>(loginDTO, sql, new { PayPeriodId = payPeriodId });
            return count > 0;
        }

        public async Task<bool> ValidateEmployeeId(int employeeId, LoginDTO loginDTO)
        {
            if (employeeId <= 0) return true;
            const string sql = "SELECT COUNT(1) FROM MEMPLOYEE WHERE EMPLOYEEID = @EmployeeId";
            var count = await _queryExecutor.ExecuteScalarAsync<int>(loginDTO, sql, new { EmployeeId = employeeId });
            return count > 0;
        }

        public async Task<bool> ValidateOUId(int ouId, LoginDTO loginDTO)
        {
            if (ouId <= 0) return true;
            const string sql = "SELECT COUNT(1) FROM MORGANIZATIONUNIT WHERE OUID = @OUId";
            var count = await _queryExecutor.ExecuteScalarAsync<int>(loginDTO, sql, new { OUId = ouId });
            return count > 0;
        }
    
        // ── GetPeriodic (Periodic.svc/GetPeriodic) ──────────────────────────────────────

        public async Task<int?> GetPayConfigurationIsPartialProcessRequired(int payConfigurationId, LoginDTO loginDTO, CancellationToken ct = default)
        {
            return await _queryExecutor.ExecuteScalarAsync<int?>(
                loginDTO, PeriodicQB.GET_PAYCONFIG_ISPARTIALPROCESSREQUIRED,
                new { PayConfigurationId = payConfigurationId });
        }

        // GB4: standard path (Periodic.GetPeriodicDetails, IsPartialProcessRequired != 0 branch).
        // Criteria map matches GB4's GetPeriodicandaddonCriteria (data path — 15 fields).
        private static readonly Dictionary<string, string> _standardCriteriaMap = new(StringComparer.OrdinalIgnoreCase)
        {
            ["Department.Id"] = "emp.DEPARTMENTID",
            ["WorkType.Id"] = "emp.WORKTYPEID",
            ["PayPeriod.Id"] = "periodic.PAYPERIODID",
            ["StaffType.Id"] = "emp.STAFFTYPEID",
            ["EmployeeType.Id"] = "emp.EMPLOYEETYPEID",
            ["Location.Id"] = "emp.LOCATIONID",
            ["EmployerStatus"] = "emp.EMPLOYEESTATUS",
            ["Status"] = "emp.STATUS",
            ["PayConfiguration"] = "emp.PAYCONFIGURATIONID",
            ["OUId"] = "emp.WORKOUID",
            ["Applicable"] = "periodic.APPLICABLE",
            ["PayConfiguration.Id"] = "emp.PAYCONFIGURATIONID",
            ["PartyId"] = "d.PARTYID",
            ["PartyBranchId"] = "d.PARTYBRANCHID",
            ["MMDetailId"] = "periodic.MMDETAILID",
        };

        // GB4: standard path COUNT (GetPeriodicandaddonCriterias — narrower map, 2 fields only).
        // Preserved verbatim per GB4 parity note in PeriodicQB.BuildGetPeriodicStandardCount.
        private static readonly Dictionary<string, string> _standardCountCriteriaMap = new(StringComparer.OrdinalIgnoreCase)
        {
            ["OUId"] = "periodic.OUID",
            ["Applicable"] = "periodic.APPLICABLE",
        };

        // GB4: "New" path (GetPeriodicDetailsNew / GetPeriodicandaddonCriteriaNew).
        private static readonly Dictionary<string, string> _newCriteriaMap = new(StringComparer.OrdinalIgnoreCase)
        {
            ["Department.Id"] = "emp.DepartmentId",
            ["WorkType.Id"] = "EMP.WorkTypeId",
            ["StaffType.Id"] = "emp.StaffTypeId",
            ["EmployeeType.Id"] = "emp.EMPLOYEETYPEID",
            ["Location.Id"] = "EMP.LocationId",
            ["EmployerStatus"] = "EMP.EMPLOYEESTATUS",
            ["Status"] = "B.Status",
            ["PayConfiguration"] = "EMP.PAYCONFIGURATIONID",
            ["Applicable"] = "ISNULL(B.Applicable,5)",
            ["PayConfiguration.Id"] = "EMP.PAYCONFIGURATIONID",
            ["PartyId"] = "D.PARTYID",
            ["PartyBranchId"] = "D.PARTYBRANCHID",
            ["MMDetailId"] = "postposition.MMDetailId",
            ["OUId"] = "oU.OUID",
            ["CategoryId"] = "cat.CATEGORYID",
        };

        public async Task<(List<PeriodicDTO> Items, int Total)> GetPeriodicStandard(
            int payPeriodId, int firstNumber, int maxResult, string orderByClause,
            CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct = default)
        {
            var (filter, filterParams) = PeriodicCriteriaHelper.Build(criteriaDTO, _standardCriteriaMap);
            var (countFilter, countFilterParams) = PeriodicCriteriaHelper.Build(criteriaDTO, _standardCountCriteriaMap);

            var sql = PeriodicQB.BuildGetPeriodicStandard(orderByClause, filter);
            var countSql = PeriodicQB.BuildGetPeriodicStandardCount(countFilter);

            filterParams.Add("PayPeriodId", payPeriodId);
            filterParams.Add("TenantId", loginDTO.ClientId);
            filterParams.Add("FirstNumber", firstNumber);
            filterParams.Add("MaxResult", maxResult);

            countFilterParams.Add("PayPeriodId", payPeriodId);
            countFilterParams.Add("TenantId", loginDTO.ClientId);

            var items = (await _queryExecutor.QueryAsync<PeriodicDTO>(loginDTO, sql, filterParams, cancellationToken: ct)).ToList();
            var total = await _queryExecutor.ExecuteScalarAsync<int>(loginDTO, countSql, countFilterParams);

            return (items, total);
        }

        public async Task<(List<PeriodicDTO> Items, int Total)> GetPeriodicNew(
            int payPeriodId, int firstNumber, int maxResult,
            CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct = default)
        {
            var (filter, filterParams) = PeriodicCriteriaHelper.Build(criteriaDTO, _newCriteriaMap);
            // GB4 parity: Count path always uses the STANDARD count query, even on the "New"
            // (IsPartialProcessRequired==0) branch — see PeriodicQB.cs note next to
            // BuildGetPeriodicStandardCount. There is no GB4 "New"-path count query.
            var (countFilter, countFilterParams) = PeriodicCriteriaHelper.Build(criteriaDTO, _standardCountCriteriaMap);

            var sql = PeriodicQB.BuildGetPeriodicNew(filter);
            var countSql = PeriodicQB.BuildGetPeriodicStandardCount(countFilter);

            filterParams.Add("PayPeriodId", payPeriodId);
            filterParams.Add("TenantId", loginDTO.ClientId);
            filterParams.Add("FirstNumber", firstNumber);
            filterParams.Add("MaxResult", maxResult);

            countFilterParams.Add("PayPeriodId", payPeriodId);
            countFilterParams.Add("TenantId", loginDTO.ClientId);

            var items = (await _queryExecutor.QueryAsync<PeriodicDTO>(loginDTO, sql, filterParams, cancellationToken: ct)).ToList();
            var total = await _queryExecutor.ExecuteScalarAsync<int>(loginDTO, countSql, countFilterParams);

            return (items, total);
        }

        // ── PeriodicServiceBasedExport (Periodic.svc/Periodic/Service/Based/Export) ─────────
        //
        // GB4: DumpPerodicServiceBased.PeriodicServiceBasedExport. Single query — see
        // PeriodicQB.BuildPeriodicServiceBasedExport's header comment for why the GB4 temp-table
        // + second-SELECT sequence collapses to one query here (MGCM SortOrder/PeriodicId
        // ordering is applied directly, no local temp table / connection pinning needed).
        public async Task<List<PeriodicDTO>> GetPeriodicServiceBasedExport(
            int payPeriodId, CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct = default)
        {
            var (filter, filterParams) = PeriodicCriteriaHelper.Build(criteriaDTO, _newCriteriaMap);
            var sql = PeriodicQB.BuildPeriodicServiceBasedExport(filter);

            filterParams.Add("PayPeriodId", payPeriodId);
            filterParams.Add("TenantId", loginDTO.ClientId);

            return (await _queryExecutor.QueryAsync<PeriodicDTO>(loginDTO, sql, filterParams, cancellationToken: ct)).ToList();
        }

        // GB4: GenericLazy.GetCondition<AddOnField>("Entity.Id", entityId, ..., Equal), filtered
        // to IsMandatory fields, ordered by SortOrder. See PeriodicQB.GET_PERIODICADDON_FIELDS.
        public async Task<List<string>> GetPeriodicAddonExportFieldNames(int entityId, LoginDTO loginDTO, CancellationToken ct = default)
        {
            return (await _queryExecutor.QueryAsync<string>(
                loginDTO, PeriodicQB.GET_PERIODICADDON_FIELDS, new { EntityId = entityId }, cancellationToken: ct)).ToList();
        }

        // GB4: PeriodicQueryBuilder.PERIODDIC_SERVICE_BASED_EXPORT_ADDON. {addonColumns} is a
        // pre-validated (PeriodicBLL.IsSafeIdentifier), comma-prefixed column list — see
        // PeriodicQB.BuildPeriodicServiceBasedExportAddon.
        public async Task<IEnumerable<dynamic>> GetPeriodicServiceBasedExportAddonValues(
            int payPeriodId, string addonColumns, LoginDTO loginDTO, CancellationToken ct = default)
        {
            var sql = PeriodicQB.BuildPeriodicServiceBasedExportAddon(addonColumns);
            return await _queryExecutor.QueryAsync<dynamic>(
                loginDTO, sql, new { PayPeriodId = payPeriodId, TenantId = loginDTO.ClientId }, cancellationToken: ct);
        }

        //public async Task<bool> ValidatePayGroupId(int payGroupId, LoginDTO loginDTO)
        //{
        //    if (payGroupId <= 0) return true; // Skip validation for default/negative values
        //    const string sql = "SELECT COUNT(1) FROM MPAYGROUP WHERE PAYGROUPID = @PayGroupId";
        //    var count = await _queryExecutor.ExecuteScalarAsync<int>(loginDTO, sql, new { PayGroupId = payGroupId });
        //    return count > 0;
        //}

        //public async Task<bool> ValidatePayPeriodId(int payPeriodId, LoginDTO loginDTO)
        //{
        //    if (payPeriodId <= 0) return true;
        //    const string sql = "SELECT COUNT(1) FROM MPAYPERIOD WHERE PAYPERIODID = @PayPeriodId";
        //    var count = await _queryExecutor.ExecuteScalarAsync<int>(loginDTO, sql, new { PayPeriodId = payPeriodId });
        //    return count > 0;
        //}

        //public async Task<bool> ValidateEmployeeId(int employeeId, LoginDTO loginDTO)
        //{
        //    if (employeeId <= 0) return true;
        //    const string sql = "SELECT COUNT(1) FROM MEMPLOYEE WHERE EMPLOYEEID = @EmployeeId";
        //    var count = await _queryExecutor.ExecuteScalarAsync<int>(loginDTO, sql, new { EmployeeId = employeeId });
        //    return count > 0;
        //}

        //public async Task<bool> ValidateOUId(int ouId, LoginDTO loginDTO)
        //{
        //    if (ouId <= 0) return true;
        //    const string sql = "SELECT COUNT(1) FROM MORGANIZATIONUNIT WHERE OUID = @OUId";
        //    var count = await _queryExecutor.ExecuteScalarAsync<int>(loginDTO, sql, new { OUId = ouId });
        //    return count > 0;
        //}

        // ── LoadPeriodic (bulk generation) ──────────────────────────────────────────────
        public async Task<DateTime?> GetPayPeriodToDate(int payPeriodId, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            return await _queryExecutor.ExecuteScalarAsync<DateTime?>(
                loginDTO, PeriodicQB.GET_PAYPERIOD_TODATE, new { PayPeriodId = payPeriodId }, transaction);
        }

        public async Task<IEnumerable<PeriodicAddonFieldDefaultDTO>> GetPeriodicAddonFieldDefaults(LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            return await _queryExecutor.QueryAsync<PeriodicAddonFieldDefaultDTO>(
                loginDTO, PeriodicQB.GET_PERIODIC_ADDON_FIELD_DEFAULTS, null, transaction);
        }

        public async Task<int> LoadPeriodic(
            int payPeriodId, int payConfigurationId, int autoId, DateTime toDate,
            string fieldNameList, string fieldValueList, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            var sql = PeriodicQB.BuildLoadPeriodic(fieldNameList, fieldValueList);
            return await _queryExecutor.ExecuteAsync(
                loginDTO, sql,
                new
                {
                    OUId = loginDTO.WorkOUId,
                    PayPeriodId = payPeriodId,
                    PayConfigurationId = payConfigurationId,
                    AutoId = autoId,
                    UserId = loginDTO.UserId,
                    ToDate = toDate,
                    TenantId = loginDTO.ClientId
                },
                transaction);
        }
    }
}