using Dapper;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.Validation;
using Microsoft.Extensions.Configuration;
using Newtonsoft.Json;
using PayrollDAL.DTO.Adjustment;
using PayRollDAL.CustomeCode.PayProcess;
using PayRollDAL.DTO.Adjustment;
using PayRollDAL.DTO.Employee;
using PayRollDAL.DTO.Leave;
using PayRollDAL.DTO.PayProcess;
using PayRollDAL.DTO.TLeave;
using PayRollDAL.Query.Employee;
using PayRollDAL.Query.Leave;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using static Azure.Core.HttpHeader;
using static GB5Shared.GB5Constant.Constant;
namespace PayRollDAL.CustomeCode.Leave
{
    public class LeaveDAL : ILeaveDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

        public LeaveDAL(IQueryExecutor IQueryExecutor, IValidation IValidation)
        {
            _QueryExecutor = IQueryExecutor;
            _Validation = IValidation;
        }

        public async Task<string> GetLeave(int LeaveId, LoginDTO LoginDTO)
        {
            try
            {
                string sql = LeaveQB.GET_LEAVE;
                var parameters = new { leaveid = LeaveId };
                LeaveDTO LeaveDTO = await _QueryExecutor.QuerySingleAsync<LeaveDTO>(LoginDTO, sql, parameters);
                return JsonConvert.SerializeObject(LeaveDTO);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> SaveLeave(LeaveDTO LeaveDTO, LoginDTO LoginDTO)
        {
            try
            {
                string Sql = LeaveQB.SAVE_LEAVE;
                return await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, LeaveDTO);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.SaveErrorMessage}");
                throw new Exception(Error);
            }
        }
        public async Task<int> UpdateLeave(LeaveDTO LeaveDTO, LoginDTO LoginDTO)
        {
            try
            {
                string Sql = LeaveQB.UPDATE_LEAVE; // You'll need to add this query
                return await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, LeaveDTO);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.UpdateErrorMessage}");
                throw new Exception(Error);
            }
        }
        public async Task<int> UpdateLeaveStatusAsync(long leaveId, int status, LoginDTO loginDTO)
        {
            try
            {
                string sql = LeaveQB.UPDATE_LEAVE_STATUS;
                var param = new { LeaveId = leaveId, Status = status };
                return await _QueryExecutor.ExecuteAsync(loginDTO, sql, param);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.UpdateErrorMessage}");
                throw new Exception(Error);
            }
        }
        //public async Task<bool> CheckDuplicateLeaveAsync(string leaveCode, int tenantId)
        //{
        //    if (_db.State == System.Data.ConnectionState.Closed)
        //        await _db.OpenAsync();

        //    return await _db.QueryFirstOrDefaultAsync<int>(
        //        LeaveQB.CHECK_LEAVE_DUPLICATE,
        //        new { LeaveCode = leaveCode, TenantId = tenantId }
        //    ) == 1;
        //}

        public async Task<string> DeleteLeave(int LeaveId, LoginDTO LoginDTO)
        {
            try
            {
                string Sql = LeaveQB.DELETE_LEAVE;
                var Parameters = new { leaveid = LeaveId };
                int result = await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, Parameters);

                if (result > 0)
                {
                    return $"{SuccessResponse.DeleteSuccessMessage}";
                }
                else
                {
                    return $"{ErrorResponse.DeleteNotFoundMessage}";
                }
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.DeleteErrorMessage}");
                throw new Exception(Error);
            }
        }

        public async Task<string> GetSelectListLeave(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string SQL = LeaveQB.GET_SELECTLIST_LEAVE;
                var Result = await _QueryExecutor.QueryAsync<LeavePicklistDTO>(LoginDTO, SQL, null!);
                string Json = JsonConvert.SerializeObject(Result);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }



        public async Task<RootsEmployeePermissionInfoDTO> GetRootsEmployeePermissionInfo(int EmployeeId, DateTime Date, LoginDTO LoginDTO)
        {
            try
            {
                string sql = LeaveQB.GET_ROOTSEMPLOYEEPERMISSIONINFO;
                var parameters = new { employeeid = EmployeeId, date = Date };
                RootsEmployeePermissionInfoDTO RootsEmployeePermissionInfoDTOs = await _QueryExecutor.QuerySingleAsync<RootsEmployeePermissionInfoDTO>(LoginDTO, sql, parameters);
                return RootsEmployeePermissionInfoDTOs;
            }
            catch (Exception)
            {
                throw;
            }
        }


        public async Task<string> GetSelectListMLeave(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool IsCount = false)
        {
            try
            {
                string Json = "";
                if (!IsCount)
                {
                    string Sql = LeaveQB.GET_SELECTLIST_MLEAVE;
                    var Parameters = new { firstnumber = FirstNumber, maxresult = MaxResult };
                    IEnumerable<LeavePicklistDTO> MLeaveDTO = await _QueryExecutor.QueryAsync<LeavePicklistDTO>(LoginDTO, Sql, Parameters);
                    Json = JsonConvert.SerializeObject(MLeaveDTO);
                }
                else
                {
                    int Count = 0;
                    Json = Count.ToString();
                }

                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }













        #region 🔹 DAL Methods

        public async Task<IEnumerable<AllLeaveDetailInOneDTO>> GetEmployeeDetailsAsync(
    LoginDTO loginDTO,
    int? employeeId = null,
    int? ouId = null,
    int? departmentId = null)
        {
            try
            {
                return await _QueryExecutor.QueryAsync<AllLeaveDetailInOneDTO>(
                    loginDTO,
                    LeaveQB.GET_EMPLOYEE_DETAILS,
                    new
                    {
                        EmployeeId = employeeId,
                        OuId = ouId,
                        DepartmentId = departmentId
                    }
                );
            }
            catch (Exception ex)
            {
                throw new Exception("Error fetching employee details", ex);
            }
        }

        public async Task<IEnumerable<PermssionDetailDTO>> GetEmployeePermissionDetailsAsync(
    LoginDTO loginDTO,
    DateTime? periodFrom,
    DateTime? periodTo,
    int? employeeId = null,
    int? ouId = null,
    int? departmentId = null,
    CancellationToken cancellationToken = default)
        {
            if (loginDTO == null)
                throw new ArgumentNullException(nameof(loginDTO));

            try
            {
                // ✅ Truncate to second precision for SQL consistency
                DateTime? fromParam = periodFrom.HasValue
                    ? new DateTime(
                        periodFrom.Value.Year,
                        periodFrom.Value.Month,
                        periodFrom.Value.Day,
                        periodFrom.Value.Hour,
                        periodFrom.Value.Minute,
                        periodFrom.Value.Second)
                    : null;

                DateTime? toParam = periodTo.HasValue
                    ? new DateTime(
                        periodTo.Value.Year,
                        periodTo.Value.Month,
                        periodTo.Value.Day,
                        periodTo.Value.Hour,
                        periodTo.Value.Minute,
                        periodTo.Value.Second)
                    : null;

                var result = await _QueryExecutor.QueryAsync<PermssionDetailDTO>(
    loginDTO,
    LeaveQB.GET_EMPLOYEE_PERMISSION_DETAILS,
    new
    {
        PeriodFrom = fromParam,
        PeriodTo = toParam,
        EmployeeId = employeeId,
        OuId = ouId,
        DepartmentId = departmentId
    },
    cancellationToken: cancellationToken
);

                // Apply defaults safely
                foreach (var item in result)
                {
                    item.Type ??= "Official";
                    item.Location ??= string.Empty;
                    item.Remarks ??= string.Empty;
                    item.ApprovedByCode ??= string.Empty;
                    item.ApprovedByName ??= string.Empty;
                }

                return result;
            }
            catch (Exception ex)
            {
                throw new Exception("Error fetching employee permission details", ex);
            }
        }

        public async Task<IEnumerable<LeaveCreditDebitDetailDTO>> GetEmployeeLeaveCreditDebitDetailsAsync(
    LoginDTO loginDTO,
    DateTime? periodFrom = null,
    DateTime? periodTo = null,
    int? employeeId = null,
    int? ouId = null,
    int? departmentId = null)
        {
            try
            {
                // ✅ Truncate to second precision for SQL consistency
                DateTime? fromParam = periodFrom.HasValue
                    ? new DateTime(
                        periodFrom.Value.Year,
                        periodFrom.Value.Month,
                        periodFrom.Value.Day,
                        periodFrom.Value.Hour,
                        periodFrom.Value.Minute,
                        periodFrom.Value.Second)
                    : null;

                DateTime? toParam = periodTo.HasValue
                    ? new DateTime(
                        periodTo.Value.Year,
                        periodTo.Value.Month,
                        periodTo.Value.Day,
                        periodTo.Value.Hour,
                        periodTo.Value.Minute,
                        periodTo.Value.Second)
                    : null;

                return await _QueryExecutor.QueryAsync<LeaveCreditDebitDetailDTO>(
                    loginDTO,
                    LeaveQB.GET_EMPLOYEE_LEAVE_CREDIT_DEBIT_DETAILS,
                    new
                    {
                        PeriodFrom = fromParam,
                        PeriodTo = toParam,
                        EmployeeId = employeeId,
                        OuId = ouId,
                        DepartmentId = departmentId
                    }
                );
            }
            catch (Exception ex)
            {
                throw new Exception("Error fetching leave credit/debit details", ex);
            }
        }

        public async Task<IEnumerable<LeaveRequestDebitDetailDTO>> GetEmployeeLeaveRequestDetailsAsync(
    LoginDTO loginDTO,
    DateTime? periodFrom = null,
    DateTime? periodTo = null,
    int? employeeId = null,
    int? ouId = null,
    int? departmentId = null)
        {
            try
            {
                // ✅ Truncate to second precision for SQL consistency
                DateTime? fromParam = periodFrom.HasValue
                    ? new DateTime(
                        periodFrom.Value.Year,
                        periodFrom.Value.Month,
                        periodFrom.Value.Day,
                        periodFrom.Value.Hour,
                        periodFrom.Value.Minute,
                        periodFrom.Value.Second)
                    : null;

                DateTime? toParam = periodTo.HasValue
                    ? new DateTime(
                        periodTo.Value.Year,
                        periodTo.Value.Month,
                        periodTo.Value.Day,
                        periodTo.Value.Hour,
                        periodTo.Value.Minute,
                        periodTo.Value.Second)
                    : null;

                return await _QueryExecutor.QueryAsync<LeaveRequestDebitDetailDTO>(
                    loginDTO,
                    LeaveQB.GET_EMPLOYEE_LEAVE_REQUEST_DETAILS,
                    new
                    {
                        PeriodFrom = fromParam,
                        PeriodTo = toParam,
                        EmployeeId = employeeId,
                        OuId = ouId,
                        DepartmentId = departmentId
                    }
                );
            }
            catch (Exception ex)
            {
                throw new Exception("Error fetching leave request details", ex);
            }
        }


        private static DateTime? TruncateToSecond(DateTime? dt) =>
    dt.HasValue
        ? new DateTime(dt.Value.Year, dt.Value.Month, dt.Value.Day,
                       dt.Value.Hour, dt.Value.Minute, dt.Value.Second)
        : null;

        public async Task<IEnumerable<DailyAttendanceDTO>> GetEmployeeDailyAttendanceAsync(
            LoginDTO loginDTO,
            DateTime? periodFrom = null,
            DateTime? periodTo = null,
            int? employeeId = null,
            int? ouId = null,
            int? departmentId = null)
        {
            try
            {
                return await _QueryExecutor.QueryAsync<DailyAttendanceDTO>(
                    loginDTO,
                    LeaveQB.GET_EMPLOYEE_DAILY_ATTENDANCE,
                    new
                    {
                        PeriodFrom = TruncateToSecond(periodFrom),
                        PeriodTo = TruncateToSecond(periodTo),
                        EmployeeId = employeeId,
                        OuId = ouId,
                        DepartmentId = departmentId
                    }
                );
            }
            catch (Exception ex)
            {
                throw new Exception("Error fetching daily attendance details", ex);
            }
        }

        public async Task<IEnumerable<LeaveOnDutyRequestDTO>> GetEmployeeLeaveOnDutyRequestAsync(
            LoginDTO loginDTO,
            DateTime? periodFrom = null,
            DateTime? periodTo = null,
            int? employeeId = null,
            int? ouId = null,
            int? departmentId = null)
        {
            try
            {
                return await _QueryExecutor.QueryAsync<LeaveOnDutyRequestDTO>(
                    loginDTO,
                    LeaveQB.GET_EMPLOYEE_LEAVE_ONDUTY_REQUEST,
                    new
                    {
                        PeriodFrom = TruncateToSecond(periodFrom),
                        PeriodTo = TruncateToSecond(periodTo),
                        EmployeeId = employeeId,
                        OuId = ouId,
                        DepartmentId = departmentId
                    }
                );
            }
            catch (Exception ex)
            {
                throw new Exception("Error fetching leave/on duty request details", ex);
            }
        }

        public async Task<IEnumerable<MissedPunchDTO>> GetEmployeeMissedPunchAsync(
    LoginDTO loginDTO,
    DateTime? periodFrom = null,
    DateTime? periodTo = null,
    int? employeeId = null,
    string type = "PUNCH",
    CancellationToken cancellationToken = default)
        {
            try
            {
                if (string.IsNullOrWhiteSpace(loginDTO?.DatabaseName))
                    throw new ArgumentException("DatabaseName is required in LoginDTO");

                return await _QueryExecutor.QueryAsync<MissedPunchDTO>(
                    loginDTO,
                    LeaveQB.GET_EMPLOYEE_MISSED_PUNCH,
                    new
                    {
                        DatabaseName = loginDTO.DatabaseName,
                        Type = type,
                        EmployeeId = employeeId,
                        PeriodFrom = SafeSqlDate(periodFrom?.Date),
                        PeriodTo = SafeSqlDate(periodTo?.Date)
                    }
                );
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<IEnumerable<PunchDetailDTO>> GetEmployeePunchDetailsAsync(
    LoginDTO loginDTO,
    DateTime? periodFrom = null,
    DateTime? periodTo = null,
    int? employeeId = null,
    int? ouId = null,
    int? departmentId = null,
    CancellationToken cancellationToken = default)
        {
            try
            {
                return await _QueryExecutor.QueryAsync<PunchDetailDTO>(
                    loginDTO,
                    LeaveQB.GET_EMPLOYEE_PUNCH_DETAILS,
                    new
                    {
                        PeriodFrom = SafeSqlDate(periodFrom?.Date),
                        PeriodTo = SafeSqlDate(periodTo?.Date),
                        EmployeeId = employeeId,
                        OuId = ouId,
                        DepartmentId = departmentId
                    }
                );
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion
        private DateTime? SafeSqlDate(DateTime? date)
        {
            if (!date.HasValue) return null;

            return date.Value < new DateTime(1753, 1, 1)
                ? null
                : date.Value;
        }

        public async Task<int> GetEmployeeIdByUserCodeAsync(string userCode, LoginDTO loginDTO)
        {
            bool isPg = loginDTO.DatabaseType == (byte)DBTYPE.POSTGRESQL;
            string sql = isPg ? LeaveQB.GET_EMPLOYEEID_BY_USERCODE_PG : LeaveQB.GET_EMPLOYEEID_BY_USERCODE;
            return await _QueryExecutor.QuerySingleAsync<int>(loginDTO, sql, new { UserCode = userCode });
        }

    }
}