using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Text.Json;
using System.Threading;
using System.Threading.Tasks;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Validation;
using Newtonsoft.Json;
using PayRollDAL.DTO.OverTime;
using PayRollDAL.Query.OverTime;

namespace PayRollDAL.CustomeCode.OverTime
{
    public class OverTimeDAL : IOverTimeDAL
    {
        private readonly IQueryExecutor _queryExecutor;
        private readonly IValidation _Validation;

        public OverTimeDAL(IQueryExecutor queryExecutor, IValidation IValidation)
        {
            _queryExecutor = queryExecutor;
            _Validation = IValidation;
        }

        public async Task<string> GetSelectListOverTime(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool IsCount = false)
        {
            try
            {
                string Json = "";
                string overtimeNumber = "";
                string employeeCode = "";
                string employeeName = "";
                int bizTransactionTypeId = -1;
                int employeeId = -1;
                DateTime? overtimeFromDate = null;
                DateTime? overtimeToDate = null;

                if (!IsCount)
                {
                    if (CriteriaDTO != null)
                    {
                        foreach (var section in CriteriaDTO.SectionCriteriaList)
                        {
                            foreach (var attribute in section.AttributesCriteriaList)
                            {
                                if (attribute.FieldValue is not JsonElement value)
                                    continue;
                                switch (attribute.FieldName?.ToLower())
                                {
                                    case "overtimenumber":
                                        overtimeNumber = value.GetString() ?? "";
                                        break;
                                    case "employeecode":
                                        employeeCode = value.GetString() ?? "";
                                        break;
                                    case "employeename":
                                        employeeName = value.GetString() ?? "";
                                        break;
                                    case "biztransactiontype.id":
                                        bizTransactionTypeId = value.GetInt32();
                                        break;
                                    case "employeeid":
                                        employeeId = value.GetInt32();
                                        break;
                                    case "overtimefromdate":
                                        overtimeFromDate = value.ValueKind == JsonValueKind.String ? DateTime.Parse(value.GetString()!) : (DateTime?)null;
                                        break;
                                    case "overtimetodate":
                                        overtimeToDate = value.ValueKind == JsonValueKind.String ? DateTime.Parse(value.GetString()!) : (DateTime?)null;
                                        break;
                                }
                            }
                        }
                    }
                    string SQL = OverTimeQB.GET_SELECTLIST_OVERTIME;
                    var parameters = new
                    {
                        firstnumber = FirstNumber,
                        maxresult = MaxResult,
                        biztransactiontypeid = bizTransactionTypeId,
                        overtimenumber = overtimeNumber,
                        employeecode = employeeCode,
                        employeename = employeeName,
                        employeeid = employeeId,
                        overtimefromdate = overtimeFromDate,
                        overtimetodate = overtimeToDate
                    };
                    var result = (await _queryExecutor.QueryAsync<OverTimePicklistDTO>(LoginDTO, SQL, parameters)).ToList();
                    Json = JsonConvert.SerializeObject(result);
                }
                else
                {
                    int Count = 0;
                    Json = Count.ToString();
                }
                return Json;
            }
            catch
            {
                throw;
            }
        }

        // ── SaveOverTime ─────────────────────────────────────────────────────────────

        public async Task<int> SaveOverTime(OverTimeDTO dto, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            var now = DateTime.UtcNow;
            var p = new
            {
                dto.OverTimeId,
                dto.BIZTransactionTypeId,
                dto.OUId,
                dto.PeriodId,
                dto.OverTimeOverTimeNumber,
                dto.OverTimeOverTimeDate,
                dto.OverTimeForTheDate,
                dto.OverTimeRemarks,
                dto.OverTimeStatus,
                dto.OverTimeVersion,
                dto.OverTimeSourcetype,
                dto.FunctionalReportingTo,
                dto.AdminReportingTo,
                dto.PayConfigurationId,
                dto.OuWiseDepartmentWiseHeadId,
                TenantId = loginDTO.ClientId,
                UserId = loginDTO.UserId,
                CreatedOn = now,
                ModifiedOn = now
            };
            return await _queryExecutor.ExecuteAsync(loginDTO, OverTimeQB.SAVE_OVERTIME, p, transaction);
        }

        public async Task<int> UpdateOverTime(OverTimeDTO dto, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            var p = new
            {
                dto.OverTimeId,
                dto.BIZTransactionTypeId,
                dto.OUId,
                dto.PeriodId,
                dto.OverTimeOverTimeDate,
                dto.OverTimeForTheDate,
                dto.OverTimeRemarks,
                dto.OverTimeStatus,
                dto.OverTimeSourcetype,
                dto.FunctionalReportingTo,
                dto.AdminReportingTo,
                dto.PayConfigurationId,
                dto.OuWiseDepartmentWiseHeadId,
                TenantId = loginDTO.ClientId,
                UserId = loginDTO.UserId,
                ModifiedOn = DateTime.UtcNow
            };
            return await _queryExecutor.ExecuteAsync(loginDTO, OverTimeQB.UPDATE_OVERTIME, p, transaction);
        }

        public async Task<int> SaveOverTimeDetail(OverTimeDetailDTO detail, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            var p = new
            {
                detail.OverTimeDetailId,
                detail.OverTimeId,
                detail.OverTimeDetailSlNo,
                detail.EmployeeId,
                detail.OverTimeDetailFromTime,
                detail.OverTimeDetailToTime,
                detail.OverTimeDetailDuration,
                detail.OverTimeDetailFactor,
                detail.OverTimeDetailFactorHrs,
                detail.OverTimeDetailActualHours,
                detail.OverTimeDetailCompOffDays,
                TenantId = loginDTO.ClientId
            };
            return await _queryExecutor.ExecuteAsync(loginDTO, OverTimeQB.SAVE_OVERTIME_DETAIL, p, transaction);
        }

        public async Task<int> DeleteOverTimeDetails(int overTimeId, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            return await _queryExecutor.ExecuteAsync(
                loginDTO, OverTimeQB.DELETE_OVERTIME_DETAIL_ONLY,
                new { OverTimeId = overTimeId, TenantId = loginDTO.ClientId }, transaction);
        }

        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> GetOuWiseDepartmentWiseHeadId(int ouId, int departmentId, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            // GB4 wraps this lookup in try/catch because Z_DEPTWISEOUWISEHEAD is an
            // addon table that may not exist in every deployment. Preserve that guard.
            try
            {
                var result = await _queryExecutor.QuerySingleAsync<int?>(
                    loginDTO, OverTimeQB.GET_OUWISE_DEPARTMENTWISE_HEAD,
                    new { OUId = ouId, DepartmentId = departmentId }, transaction);
                return result ?? -1;
            }
            catch
            {
                // Table may not exist in this deployment — no error surfaced, matches GB4.
                return -1;
            }
        }

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

        public async Task<int?> GetBizTransactionTypeNOGenerationType(int bizTransactionTypeId, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            return await _queryExecutor.QuerySingleAsync<int?>(
                loginDTO, OverTimeQB.GET_BIZTRANSACTIONTYPE_NOGEN,
                new { BizTransactionTypeId = bizTransactionTypeId, TenantId = loginDTO.ClientId }, transaction);
        }

        public async Task<OverTimeDetailDTO?> CheckDuplicateOverTimeEntry(int employeeId, DateTime forTheDate, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            return await _queryExecutor.QuerySingleAsync<OverTimeDetailDTO>(
                loginDTO, OverTimeQB.CHECK_ENTRIES_IN_OVERTIMEDETAIL,
                new { EmployeeId = employeeId, ForTheDate = forTheDate, TenantId = loginDTO.ClientId }, transaction);
        }

        // ── Get / Delete ─────────────────────────────────────────────────────────────

        public async Task<OverTimeDTO?> GetOverTime(int overTimeId, LoginDTO loginDTO)
        {
            return await _queryExecutor.QuerySingleAsync<OverTimeDTO>(
                loginDTO, OverTimeQB.GET_OVERTIME,
                new { OverTimeId = overTimeId, TenantId = loginDTO.ClientId });
        }

        public async Task<List<OverTimeDetailDTO>> GetOverTimeDetailsByHeader(int overTimeId, LoginDTO loginDTO)
        {
            var result = await _queryExecutor.QueryAsync<OverTimeDetailDTO>(
                loginDTO, OverTimeQB.GET_OVERTIME_DETAILS_BY_HEADER,
                new { OverTimeId = overTimeId, TenantId = loginDTO.ClientId });
            return result.ToList();
        }

        public async Task<int> DeleteOverTime(int overTimeId, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            return await _queryExecutor.ExecuteAsync(
                loginDTO, OverTimeQB.DELETE_OVERTIME_HEADER_ONLY,
                new { OverTimeId = overTimeId, TenantId = loginDTO.ClientId }, transaction);
        }

        // ── LoadOvertimeRecords ──────────────────────────────────────────────────────

        public async Task<List<OverTimeDTO>> LoadOvertimeRecords(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, CancellationToken ct = default)
        {
            DateTime forTheDate = default;
            int departmentId = -1, workTypeId = -1, employeeTypeId = -1, staffTypeId = -1,
                otType = -1, employerStatus = -1, status = -1, workOUId = -1, reportingToId = -1;

            if (CriteriaDTO?.SectionCriteriaList != null)
            {
                foreach (var section in CriteriaDTO.SectionCriteriaList)
                {
                    if (section.AttributesCriteriaList == null) continue;
                    foreach (var attr in section.AttributesCriteriaList)
                    {
                        if (attr.FieldValue is not JsonElement value) continue;
                        switch (attr.FieldName?.ToLower())
                        {
                            case "forthedate":
                                if (value.ValueKind == JsonValueKind.String && DateTime.TryParse(value.GetString(), out var d))
                                    forTheDate = d;
                                break;
                            case "department.id": departmentId = GetInt(value); break;
                            case "worktype.id": workTypeId = GetInt(value); break;
                            case "employeetype.id": employeeTypeId = GetInt(value); break;
                            case "stafftype.id": staffTypeId = GetInt(value); break;
                            case "ottype": otType = GetInt(value); break;
                            case "employerstatus": employerStatus = GetInt(value); break;
                            case "status": status = GetInt(value); break;
                            case "workou.id": workOUId = GetInt(value); break;
                            case "reportingto.id": reportingToId = GetInt(value); break;
                        }
                    }
                }
            }

            var filters = new StringBuilder();
            if (departmentId != -1) filters.Append(" AND emp.DEPARTMENTID = @departmentid");
            if (workTypeId != -1) filters.Append(" AND emp.WORKTYPEID = @worktypeid");
            if (employeeTypeId != -1) filters.Append(" AND emp.EMPLOYEETYPEID = @employeetypeid");
            if (staffTypeId != -1) filters.Append(" AND emp.STAFFTYPEID = @stafftypeid");
            if (otType != -1) filters.Append(" AND emp.OTTYPE = @ottype");
            if (employerStatus != -1) filters.Append(" AND emp.EMPLOYEESTATUS = @employerstatus");
            if (status != -1) filters.Append(" AND emp.STATUS = @status");
            if (workOUId != -1) filters.Append(" AND emp.WORKOUID = @workouid");
            if (reportingToId != -1) filters.Append(" AND emp.REPORTINGTOEMPLOYEEID = @reportingtoid");

            string sql = OverTimeQB.GET_OVERTIME_RECORDS.Replace("{DYNAMIC_FILTERS}", filters.ToString());

            var p = new
            {
                forthedate = forTheDate,
                TenantId = LoginDTO.ClientId,
                departmentid = departmentId,
                worktypeid = workTypeId,
                employeetypeid = employeeTypeId,
                stafftypeid = staffTypeId,
                ottype = otType,
                employerstatus = employerStatus,
                status,
                workouid = workOUId,
                reportingtoid = reportingToId
            };

            var result = await _queryExecutor.QueryAsync<OverTimeDTO>(LoginDTO, sql, p, cancellationToken: ct);
            return result.ToList();
        }

        // ── LoadDailyAttendanceDataToOverTime ────────────────────────────────────────

        public async Task<List<LoadOverTimeFromDailyAttendanceDTO>> LoadDailyAttendanceDataToOverTime(
            int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, CancellationToken ct = default)
        {
            if (CriteriaDTO == null)
                throw new InvalidOperationException("Please select any filter to load records..");

            int employeeId = -1, shiftId = -1, payConfigurationId = -1;
            DateTime? periodFromDate = null, periodToDate = null;

            foreach (var section in CriteriaDTO.SectionCriteriaList ?? new List<SectionCriteriaDTO>())
            {
                foreach (var attr in section.AttributesCriteriaList ?? new List<AttributesCriteriaDTO>())
                {
                    if (attr.FieldValue is not JsonElement value) continue;
                    switch (attr.FieldName?.ToLower())
                    {
                        case "employeeid": employeeId = GetInt(value); break;
                        case "shiftid": shiftId = GetInt(value); break;
                        case "payconfigurationid": payConfigurationId = GetInt(value); break;
                        case "periodfromdate":
                            if (value.ValueKind == JsonValueKind.String && DateTime.TryParse(value.GetString(), out var fd))
                                periodFromDate = fd;
                            break;
                        case "periodtodate":
                            if (value.ValueKind == JsonValueKind.String && DateTime.TryParse(value.GetString(), out var td))
                                periodToDate = td;
                            break;
                    }
                }
            }

            // GB4 parity: "Fromdate and ToDate should be same for loading.." — same-day-only load.
            if (periodFromDate?.Date != periodToDate?.Date)
                throw new InvalidOperationException("Fromdate and ToDate should be same for loading..");

            var filters = new StringBuilder();
            if (employeeId != -1) filters.Append(" AND a.EMPLOYEEID = @employeeid");
            if (shiftId != -1) filters.Append(" AND a.SHIFTID = @shiftid");
            if (payConfigurationId != -1) filters.Append(" AND b.PAYCONFIGURATIONID = @payconfigurationid");
            if (periodFromDate.HasValue) filters.Append(" AND a.ATTENDANCEDATE >= @periodfromdate");
            if (periodToDate.HasValue) filters.Append(" AND a.ATTENDANCEDATE <= @periodtodate");

            string sql = OverTimeQB.LOAD_OVER_TIME_FROM_DAILY_ATTENDANCE.Replace("{DYNAMIC_FILTERS}", filters.ToString());

            var p = new
            {
                TenantId = LoginDTO.ClientId,
                employeeid = employeeId,
                shiftid = shiftId,
                payconfigurationid = payConfigurationId,
                periodfromdate = periodFromDate,
                periodtodate = periodToDate,
                ouid = LoginDTO.WorkOUId
            };

            var result = await _queryExecutor.QueryAsync<LoadOverTimeFromDailyAttendanceDTO>(LoginDTO, sql, p, cancellationToken: ct);
            return result.ToList();
        }

        // ── PostAutoOverTime support ─────────────────────────────────────────────────

        public async Task<int?> GetBizTypeForOverTime(int ouId, int periodId, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            return await _queryExecutor.QuerySingleAsync<int?>(
                loginDTO, OverTimeQB.GET_BIZTYPE_FOR_OVERTIME,
                new { OUId = ouId, PeriodId = periodId, TenantId = loginDTO.ClientId }, transaction);
        }

        public async Task<List<OverTimeDetailDTO>> GetEmployeesWithOvertime(int ouId, DateTime attendanceDate, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            var result = await _queryExecutor.QueryAsync<OverTimeDetailDTO>(
                loginDTO, OverTimeQB.GET_EMPLOYEE_WITH_OVERTIME,
                new { OUId = ouId, AttendanceDate = attendanceDate.Date, TenantId = loginDTO.ClientId }, transaction);
            return result.ToList();
        }

        public async Task DeleteAutoPostedOverTime(int ouId, int periodId, DateTime attendanceDate, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            var p = new { OUId = ouId, PeriodId = periodId, AttendanceDate = attendanceDate.Date, TenantId = loginDTO.ClientId };
            await _queryExecutor.ExecuteAsync(loginDTO, OverTimeQB.DELETE_AUTO_POSTED_OVERTIME_DETAIL, p, transaction);
            await _queryExecutor.ExecuteAsync(loginDTO, OverTimeQB.DELETE_AUTO_POSTED_OVERTIME_HEADER, p, transaction);
        }

        // ── GetOverTimeDetailReport — GB4 IOverTime.GetOverTimeDetailReport ────────────

        public async Task<string> GetOverTimeDetailReport(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool IsCount = false)
        {
            try
            {
                int ouId = -1;
                int bizTransactionTypeId = -1;
                int employeeId = -1;
                int departmentId = -1;
                int designationId = -1;
                int workTypeId = -1;
                int shiftId = -1;
                DateTime? periodFromDate = null;
                DateTime? periodToDate = null;

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

                        foreach (var attribute in section.AttributesCriteriaList)
                        {
                            if (attribute.FieldValue is not JsonElement value)
                                continue;
                            switch (attribute.FieldName?.ToLower())
                            {
                                case "ouid":
                                    ouId = GetInt(value);
                                    break;
                                case "biztransactiontypeid":
                                    bizTransactionTypeId = GetInt(value);
                                    break;
                                case "employeeid":
                                    employeeId = GetInt(value);
                                    break;
                                case "departmentid":
                                    departmentId = GetInt(value);
                                    break;
                                case "designationid":
                                    designationId = GetInt(value);
                                    break;
                                case "worktypeid":
                                    workTypeId = GetInt(value);
                                    break;
                                case "shiftid":
                                    shiftId = GetInt(value);
                                    break;
                                case "periodfromdate":
                                    periodFromDate = value.ValueKind == JsonValueKind.String ? DateTime.Parse(value.GetString()!) : (DateTime?)null;
                                    break;
                                case "periodtodate":
                                    periodToDate = value.ValueKind == JsonValueKind.String ? DateTime.Parse(value.GetString()!) : (DateTime?)null;
                                    break;
                            }
                        }
                    }
                }

                var parameters = new
                {
                    tenantid = LoginDTO.ClientId,
                    ouid = ouId,
                    biztransactiontypeid = bizTransactionTypeId,
                    employeeid = employeeId,
                    departmentid = departmentId,
                    designationid = designationId,
                    worktypeid = workTypeId,
                    shiftid = shiftId,
                    periodfromdate = periodFromDate,
                    periodtodate = periodToDate,
                    firstnumber = FirstNumber,
                    maxresult = MaxResult
                };

                if (IsCount)
                {
                    int count = await _queryExecutor.ExecuteScalarAsync<int>(LoginDTO, OverTimeQB.GET_OVERTIME_DETAIL_REPORT_COUNT, parameters)
                        .ConfigureAwait(false);
                    return count.ToString();
                }

                var result = (await _queryExecutor.QueryAsync<OverTimeReportDTO>(LoginDTO, OverTimeQB.GET_OVERTIME_DETAIL_REPORT, parameters)
                    .ConfigureAwait(false)).ToList();

                foreach (var row in result)
                {
                    row.OverTimeCalFactorHrs = TimeSpan.FromMinutes(row.OverTimeDetailFactorHrs).ToString(@"hh\:mm");
                    row.OverTimeCalDuration = TimeSpan.FromMinutes(row.OverTimeDetailDuration).ToString(@"hh\:mm");
                    row.CallFromTime = TimeSpan.FromMinutes(row.FromTime).ToString(@"hh\:mm");
                    row.CallToTime = TimeSpan.FromMinutes(row.ToTime).ToString(@"hh\:mm");
                    row.OverTimeCalActualOTHours = TimeSpan.FromMinutes(row.OverTimeDetailActualOTHours).ToString(@"hh\:mm");
                }


                return JsonConvert.SerializeObject(result);
            }
            catch (Exception ex)
            {
                throw new Exception("Error in GetOverTimeDetailReport", ex);
            }
        }

        private static int GetInt(JsonElement value)
        {
            if (value.ValueKind == JsonValueKind.Number) return value.GetInt32();
            if (value.ValueKind == JsonValueKind.String && int.TryParse(value.GetString(), out var r)) return r;
            return -1;
        }

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