using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Validation;
using PayRollDAL.DTO.Adjustment;
using PayrollDAL.DTO.Adjustment;
using PayRollDAL.Query.Adjustment;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;

namespace PayRollDAL.CustomeCode.Adjustment
{
    public class AdjustmentDAL : IAdjustmentDAL
    {
        private readonly IQueryExecutor _queryExecutor;
        private readonly IValidation _validation;

        public AdjustmentDAL(IQueryExecutor queryExecutor, IValidation validation)
        {
            _queryExecutor = queryExecutor;
            _validation = validation;
        }

        public async Task<IList<AdjustmentReportDTO>> GetAdjustmentReportAsync(
            int firstNumber,
            int maxResult,
            CriteriaDTO criteriaDTO,
            LoginDTO loginDTO,
            bool isCount = false)
        {
            try
            {
                string sql;
                Dictionary<string, object> parameters = new Dictionary<string, object>
                {
                    { "ouid", loginDTO.WorkOUId }
                };

                if (!isCount)
                {
                    // Use correct query constant
                    sql = AdjustmentQB.GET_ADJUSTMENT_REPORT;

                    // Apply criteria, returns tuple (sql, parameters)
                    (sql, parameters) = ApplyAdjustmentReportCriteria(sql, criteriaDTO, loginDTO);

                    // Execute query with paging
                    var adjustmentReports = await _queryExecutor.QueryPagedAsync<AdjustmentReportDTO>(
                        loginDTO,
                        sql,
                        parameters
                    );

                    return (adjustmentReports.Items ?? []).ToList();
                }
                else
                {
                    // Count query
                    sql = AdjustmentQB.GET_ADJUSTMENT_REPORT_COUNT;

                    (sql, parameters) = ApplyAdjustmentReportCriteria(sql, criteriaDTO, loginDTO);

                    var totalCount = await _queryExecutor.ExecuteScalarAsync<int>(
                        loginDTO,
                        sql,
                        parameters
                    );

                    return new List<AdjustmentReportDTO>
                    {
                        new AdjustmentReportDTO { TotalCount = totalCount }
                    };
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<IList<AdjustmentPicklistDTO>> AdjustmentNumberSelectlist(
            int firstNumber,
            int maxResult,
            CriteriaDTO criteriaDTO,
            LoginDTO loginDTO,
            bool isCount = false)
        {
            try
            {
                var (whereFragment, criteriaParameters) = ApplyAdjustmentNumberSelectlistCriteria(criteriaDTO);

                if (!isCount)
                {
                    string sql = AdjustmentQB.GET_ADJUSTMENTNUMBER_SELECTLIST.Replace("/*CRITERIA*/", whereFragment);

                    var parameters = new Dictionary<string, object>(criteriaParameters)
                    {
                        { "FirstNumber", firstNumber },
                        { "MaxResult", maxResult }
                    };

                    var result = await _queryExecutor.QueryAsync<AdjustmentPicklistDTO>(loginDTO, sql, parameters);
                    return result.ToList();
                }
                else
                {
                    string sql = AdjustmentQB.GET_ADJUSTMENTNUMBER_SELECTLIST_COUNT.Replace("/*CRITERIA*/", whereFragment);
                    var totalCount = await _queryExecutor.ExecuteScalarAsync<int>(loginDTO, sql, criteriaParameters);
                    return new List<AdjustmentPicklistDTO> { new AdjustmentPicklistDTO { TotalCount = totalCount } };
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        private static (string WhereFragment, Dictionary<string, object> Parameters) ApplyAdjustmentNumberSelectlistCriteria(CriteriaDTO criteriaDTO)
        {
            var parameters = new Dictionary<string, object>();
            var whereFragment = new System.Text.StringBuilder();

            if (criteriaDTO?.SectionCriteriaList == null)
                return (whereFragment.ToString(), parameters);

            var criteriaMap = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
            {
                { "AdjustmentNumber", "a.ADJUSTMENTNUMBER" },
                { "BizTransactionTypeId", "a.ADJUSTMENTBIZTRANSACTIONTYPEID" },
                { "EmployeeCode", "c.EMPLOYEECODE" },
                { "EmployeeName", "c.EMPLOYEENAME" },
                { "AdjustmentUserCode", "d.USERCODE" },
                { "AdjustmentUserName", "d.USERNAME" }
            };

            foreach (var section in criteriaDTO.SectionCriteriaList)
            {
                if (section?.AttributesCriteriaList == null) continue;
                foreach (var attr in section.AttributesCriteriaList)
                {
                    if (string.IsNullOrWhiteSpace(attr.FieldName) || attr.FieldValue == null) continue;
                    if (!criteriaMap.TryGetValue(attr.FieldName, out var column)) continue;

                    string paramName = attr.FieldName.Replace(".", "_");

                    if (string.Equals(attr.FieldName, "BizTransactionTypeId", StringComparison.OrdinalIgnoreCase))
                    {
                        whereFragment.Append($" AND {column} = @{paramName}");
                        parameters[paramName] = attr.FieldValue;
                    }
                    else
                    {
                        whereFragment.Append($" AND {column} LIKE @{paramName}");
                        parameters[paramName] = $"%{attr.FieldValue}%";
                    }
                }
            }

            return (whereFragment.ToString(), parameters);
        }

        private (string Sql, Dictionary<string, object> Parameters) ApplyAdjustmentReportCriteria(
            string sql,
            CriteriaDTO criteriaDTO,
            LoginDTO loginDTO)
        {
            var parameters = new Dictionary<string, object>();

            if (criteriaDTO == null)
                return (sql, parameters);

            var criteriaMap = new Dictionary<string, string>
            {
                { "OUId", "b.OUID" },
                { "AdjustmentId", "b.ADJUSTMENTID" },
                { "PeriodFromDate", "b.CREATEDON" },
                { "PeriodToDate", "b.CREATEDON" },
                { "EmployeeId", "a.EmployeeId" },
                { "DepartmentId", "a.DepartmentId" },
                { "CreatedById", "created.UserId" },
                { "ModifiedById", "Modified.UserId" },
                { "ReasonId", "b.ReasonId" },
                { "PeriodFrom", "c.ATTENDANCEDATE" },
                { "PeriodTo", "c.ATTENDANCEDATE" },
                { "CurrentOUId", "a.CurrentOUId" }
            };

            foreach (var kvp in criteriaMap)
            {
                var value = criteriaDTO.GetType().GetProperty(kvp.Key)?.GetValue(criteriaDTO, null);
                if (value != null)
                {
                    string paramName = kvp.Key;
                    sql += $" AND {kvp.Value} = @{paramName}";
                    parameters.Add(paramName, value);
                }
            }

            return (sql, parameters);
        }

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

        public async Task<(DateTime FromDate, DateTime ToDate)?> GetPeriodRange(int periodId, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            var row = await _queryExecutor.QuerySingleAsync<PeriodRangeRow>(
                loginDTO, AdjustmentQB.GET_PERIOD_RANGE_FOR_ADJUSTMENT,
                new { PeriodId = periodId, TenantId = loginDTO.ClientId }, transaction);
            if (row == null) return null;
            return (row.FromDate, row.ToDate);
        }

        private sealed class PeriodRangeRow
        {
            public int PeriodId { get; set; }
            public DateTime FromDate { get; set; }
            public DateTime ToDate { get; set; }
        }

        public async Task<int> SaveAdjustment(AdjustmentDTO dto, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            var p = new
            {
                dto.AdjustmentId,
                dto.AdjustmentAdjustmentNumber,
                dto.AdjustmentReferenceNumber,
                dto.AdjustmentReferenceDate,
                dto.AdjustmentRemarks,
                dto.AdjustmentBiztransactiontypeId,
                dto.OUId,
                dto.PeriodId,
                dto.ReasonId,
                dto.AdjustmentStatus,
                dto.AdjustmentVersion,
                dto.AdjustmentCreatedById,
                dto.AdjustmentCreatedOn,
                dto.AdjustmentModifiedById,
                dto.AdjustmentModifiedOn,
                TenantId = loginDTO.ClientId
            };
            return await _queryExecutor.ExecuteAsync(loginDTO, AdjustmentQB.SAVE_ADJUSTMENT, p, transaction);
        }

        public async Task<int> UpdateAdjustment(AdjustmentDTO dto, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            var p = new
            {
                dto.AdjustmentId,
                dto.AdjustmentReferenceNumber,
                dto.AdjustmentReferenceDate,
                dto.AdjustmentRemarks,
                dto.AdjustmentBiztransactiontypeId,
                dto.OUId,
                dto.PeriodId,
                dto.ReasonId,
                dto.AdjustmentStatus,
                dto.AdjustmentModifiedById,
                dto.AdjustmentModifiedOn,
                TenantId = loginDTO.ClientId
            };
            return await _queryExecutor.ExecuteAsync(loginDTO, AdjustmentQB.UPDATE_ADJUSTMENT, p, transaction);
        }

        public async Task<int> SaveAdjustmentDetail(AdjustmentDetailDTO detail, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            var p = new
            {
                detail.AdjustmentDetailId,
                detail.AdjustmentId,
                detail.AdjustmentDetailSlNo,
                detail.EmployeeId,
                detail.AdjustmentDetailInTime,
                detail.AdjustmentDetailOutTime,
                detail.AdjustmentDetailPreviousInTime,
                detail.AdjustmentDetailPreviousOutTime,
                detail.AdjustmentDetailDayType,
                detail.AdjustmentDetailPreviousDayType,
                detail.LeaveId,
                detail.PreviousLeaveId,
                detail.ShiftId,
                detail.PreviousShiftId,
                detail.AdjustmentDetailAttendanceDate,
                TenantId = loginDTO.ClientId
            };
            return await _queryExecutor.ExecuteAsync(loginDTO, AdjustmentQB.SAVE_ADJUSTMENT_DETAIL, p, transaction);
        }

        public async Task<int> DeleteAdjustmentDetails(int adjustmentId, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            return await _queryExecutor.ExecuteAsync(
                loginDTO, AdjustmentQB.DELETE_ADJUSTMENT_DETAIL_ONLY,
                new { AdjustmentId = adjustmentId, TenantId = loginDTO.ClientId }, transaction);
        }

        public async Task<AdjustmentDTO?> GetAdjustment(int adjustmentId, LoginDTO loginDTO, CancellationToken ct)
        {
            return await _queryExecutor.QuerySingleAsync<AdjustmentDTO>(
                loginDTO, AdjustmentQB.GET_ADJUSTMENT,
                new { AdjustmentId = adjustmentId, TenantId = loginDTO.ClientId }, cancellationToken: ct);
        }

        public async Task<List<AdjustmentDetailDTO>> GetAdjustmentDetailsByHeader(int adjustmentId, LoginDTO loginDTO, CancellationToken ct)
        {
            var result = await _queryExecutor.QueryAsync<AdjustmentDetailDTO>(
                loginDTO, AdjustmentQB.GET_ADJUSTMENT_DETAILS_BY_HEADER,
                new { AdjustmentId = adjustmentId, TenantId = loginDTO.ClientId }, cancellationToken: ct);
            return result.ToList();
        }

        public async Task<int> DeleteAdjustment(int adjustmentId, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            return await _queryExecutor.ExecuteAsync(
                loginDTO, AdjustmentQB.DELETE_ADJUSTMENT_HEADER_ONLY,
                new { AdjustmentId = adjustmentId, TenantId = loginDTO.ClientId }, transaction);
        }

        // GB4 parity: AdjustmentBLL.ApplyAdjustmentForImportCriteria's CriteriaWithQueryField map.
        private static readonly (string FieldName, string Column)[] ImportCriteriaFields =
        {
            ("employeetypeid", "employee.EMPLOYEETYPEID"),
            ("paygroupid", "employee.PAYGROUPID"),
            ("stafftypeid", "employee.STAFFTYPEID"),
            ("unitid", "employee.UNITID"),
            ("religionid", "employee.RELIGIONID"),
            ("ourbranchid", "employee.OURBRANCHID"),
            ("divisionid", "employee.DIVISIONID"),
            ("payconfigurationid", "employee.PAYCONFIGURATIONID"),
            ("shiftid", "employee.SHIFTID"),
            ("departmentid", "employee.DEPARTMENTID"),
            ("reportingtoid", "employee.REPORTINGTOEMPLOYEEID"),
            ("employeeid", "employee.EMPLOYEEID")
        };

        public async Task<List<AdjustmentImportDTO>> GetAdjustmentForImport(CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct)
        {
            DateTime? periodFromDate = null;
            DateTime? periodToDate = null;
            var whereClauses = new List<string>();
            var parameters = new Dapper.DynamicParameters();
            parameters.Add("OUId", loginDTO.WorkOUId);
            parameters.Add("PeriodId", loginDTO.WorkPeriodId);

            void ApplyCriteria(CriteriaDTO criteria)
            {
                if (criteria?.SectionCriteriaList == null)
                    return;

                foreach (var section in criteria.SectionCriteriaList)
                {
                    foreach (var attr in section?.AttributesCriteriaList ?? new())
                    {
                        string field = attr?.FieldName?.ToLower() ?? string.Empty;
                        string value = attr?.FieldValue?.ToString() ?? string.Empty;

                        if (field == "periodfromdate")
                        {
                            periodFromDate = DateTimeOffset.FromUnixTimeSeconds(Convert.ToInt64(value)).UtcDateTime;
                            continue;
                        }
                        if (field == "periodtodate")
                        {
                            periodToDate = DateTimeOffset.FromUnixTimeSeconds(Convert.ToInt64(value)).UtcDateTime;
                            continue;
                        }

                        foreach (var (fieldName, column) in ImportCriteriaFields)
                        {
                            if (field != fieldName)
                                continue;

                            string paramName = $"Crit_{fieldName}";
                            whereClauses.Add($"{column} = @{paramName}");
                            parameters.Add(paramName, Convert.ToInt32(value));
                            break;
                        }
                    }
                }
            }

            ApplyCriteria(criteriaDTO);
            ApplyCriteria(loginDTO.UserCriteriaDTO);

            if (periodFromDate.HasValue && periodToDate.HasValue)
            {
                whereClauses.Add("dailyatt.ATTENDANCEDATE >= @PeriodFromDate AND dailyatt.ATTENDANCEDATE <= @PeriodToDate");
                parameters.Add("PeriodFromDate", periodFromDate.Value);
                parameters.Add("PeriodToDate", periodToDate.Value);
            }

            string extraWhere = whereClauses.Count > 0
                ? " AND " + string.Join(" AND ", whereClauses)
                : string.Empty;

            string sql = AdjustmentQB.GET_ADJUSTMENT_FOR_IMPORT.Replace(":replacefields", extraWhere);

            var rows = (await _queryExecutor.QueryAsync<dynamic>(loginDTO, sql, parameters, cancellationToken: ct)).ToList();

            var result = new List<AdjustmentImportDTO>();
            foreach (var row in rows)
            {
                var dict = (IDictionary<string, object>)row;
                result.Add(new AdjustmentImportDTO
                {
                    AdjustmentNumber = dict["AdjustmentNumber"] as string,
                    BizType = dict["BizType"] as string,
                    OU = dict["OU"] as string,
                    Period = dict["Period"] as string,
                    ReferenceNumber = dict["ReferenceNumber"] as string,
                    ReferenceDate = dict["ReferenceDate"] as string,
                    Remarks = dict["Remarks"] as string,
                    Reason = dict["Reason"] as string,
                    Slno = Convert.ToInt32(dict["Slno"]),
                    EmployeeCode = dict["EmployeeCode"] as string,
                    DetailAttendanceDate = dict["DetailAttendanceDate"] as string,
                    InTime = MinutesIntoTime(Convert.ToInt32(dict["InTimeMinutes"])),
                    OutTime = MinutesIntoTime(Convert.ToInt32(dict["OutTimeMinutes"])),
                    DayType = Convert.ToInt32(dict["DayType"]),
                    Leave = dict["Leave"] as string,
                    Shift = dict["Shift"] as string,
                    PreviousInTime = MinutesIntoTime(Convert.ToInt32(dict["PreviousInTimeMinutes"])),
                    PreviousOutTime = MinutesIntoTime(Convert.ToInt32(dict["PreviousOutTimeMinutes"])),
                    PreviousDayType = Convert.ToInt32(dict["PreviousDayType"]),
                    PreviousLeave = dict["PreviousLeave"] as string,
                    PreviousShift = dict["PreviousShift"] as string,
                    AdjustmentId = Convert.ToInt32(dict["AdjustmentId"])
                });
            }

            return result;
        }

        private static string MinutesIntoTime(int minutes)
        {
            if (minutes <= 0) return "00:00";
            int hours = minutes / 60;
            int remaining = minutes % 60;
            return $"{hours:D2}:{remaining:D2}";
        }

        public async Task<string?> GetReasonName(int reasonId, LoginDTO loginDTO, CancellationToken ct)
        {
            var result = await _queryExecutor.QueryAsync<string>(
                loginDTO, AdjustmentQB.GET_REASON_NAME, new { ReasonId = reasonId }, cancellationToken: ct);
            return result.FirstOrDefault();
        }
    }
}