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

namespace PayRollDAL.CustomeCode.TimeSlip
{
    public class TimeSlipDAL : ITimeSlipDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

        public TimeSlipDAL(IQueryExecutor QueryExecutor, IValidation Validation)
        {
            _QueryExecutor = QueryExecutor;
            _Validation = Validation;
        }


        public async Task<TimeSlipDTO> GetTimeSlip(int TimeSlipId, LoginDTO LoginDTO)
        {
            try
            {
                string sql = TimeSlipQB.GET_TIMESLIP;
                var parameters = new { timeslipid = TimeSlipId };

                TimeSlipDTO timeSlipDTO = await _QueryExecutor.QuerySingleAsync<TimeSlipDTO>(LoginDTO, sql, parameters);

                if (timeSlipDTO != null)
                {
                    // Format Cal Start/End times from raw int (minutes) to "hh:mm tt"
                    timeSlipDTO.TimeSlipTimeSlipCalStartTime = ConvertMinutesToTimeString(timeSlipDTO.TimeSlipTimeSlipStartTime);
                    timeSlipDTO.TimeSlipTimeSlipCalEndTime = ConvertMinutesToTimeString(timeSlipDTO.TimeSlipTimeSlipEndTime);

                    // Format Duration into "hh:mm" string (using CalDuration)
                    timeSlipDTO.TimeSlipCalDuration = ConvertMinutesToDurationString(timeSlipDTO.TimeSlipDuration);
                }

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

        public async Task<string> GetSelectListTimeSlip(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool IsCount = false)
        {
            try
            {
                string Json = "";
                if (IsCount == false)
                {
                    string SQL = TimeSlipQB.GET_SELECTLIST_TIMESLIP;
                    var parameter = new { firstnumber = FirstNumber, maxresult = MaxResult };
                    var Result = await _QueryExecutor.QueryAsync<TimeSlipPicklistDTO>(LoginDTO, SQL, parameter);
                    var settings = new JsonSerializerSettings
                    {
                        DateFormatHandling = DateFormatHandling.MicrosoftDateFormat,
                        DateTimeZoneHandling = DateTimeZoneHandling.Utc
                    };
                    Json = JsonConvert.SerializeObject(Result, settings);
                }
                else
                {
                    int Count = 0;
                    Json = Count.ToString();
                }
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }


        public async Task<List<TimeSlipSummaryDTO>> GetTimeSlipSummary(CriteriaDTO criteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                var parameters = new
                {
                    EmployeeId = GetEmployeeId(criteriaDTO),
                    FromDate = GetFromDate(criteriaDTO),
                    ToDate = GetToDate(criteriaDTO)
                };

                var result = await _QueryExecutor.QueryAsync<TimeSlipSummaryDTO>(LoginDTO, TimeSlipQB.GET_TIMESLIP_SUMMARY, parameters);
                return result.ToList();
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> GetNextTimeSlipNumber(LoginDTO login, DbTransaction tx)
        {
            var result = await _QueryExecutor.ExecuteScalarAsync<int>(
                login,
                TimeSlipQB.GET_NEXT_TIMESLIP_NUMBER,
                null,
                tx
            );

            return result;
        }
        public async Task<int> SaveTimeSlip(TimeSlipDTO dto, LoginDTO login, DbTransaction dbTransaction)
        {
            try
            {
                // Audit fields are set by BLL before this call — no override here
                const string Sql = TimeSlipQB.SAVE_TIMESLIP;
                return await _QueryExecutor.ExecuteAsync(login, Sql, dto, dbTransaction);
            }
            catch (Exception ex)
            {
                throw new Exception($"Error while saving TimeSlip: {ex.InnerException?.Message ?? ex.Message}", ex);
            }
        }

        public async Task<int> UpdateTimeSlip(TimeSlipDTO dto, LoginDTO login, DbTransaction trans)
        {
            try
            {
                const string sql = TimeSlipQB.UPDATE_TIMESLIP;

                return await _QueryExecutor.ExecuteAsync(login, sql, dto, trans);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.UpdateErrorMessage);
                throw new Exception(error);
            }
        }

        public async Task<string> DeleteTimeSlip(int TimeSlipId, LoginDTO LoginDTO)
        {
            try
            {
                string Sql = TimeSlipQB.DELETE_TIMESLIP;
                var parameter = new { timeslipid = TimeSlipId };
                var result = await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, parameter);

                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);
            }
        }

        private int? GetEmployeeId(CriteriaDTO criteriaDTO)
        {
            var value = criteriaDTO.SectionCriteriaList?
                .SelectMany(s => s.AttributesCriteriaList)
                .FirstOrDefault(x => x.FieldName == "EmployeeId")?.FieldValue;

            if (value == null)
                return null;

            if (value is JsonElement element)
            {
                if (element.ValueKind == JsonValueKind.Number)
                    return element.GetInt32();

                if (element.ValueKind == JsonValueKind.String)
                    return int.Parse(element.GetString()!);
            }

            return Convert.ToInt32(value);
        }
        private DateTime? GetFromDate(CriteriaDTO criteria)
        {
            var value = criteria.SectionCriteriaList?
                .SelectMany(s => s.AttributesCriteriaList)
                .FirstOrDefault(x => x.FieldName == "PeriodFromDate")?.FieldValue;

            return ConvertUnixToUtc(value);
        }

        private DateTime? GetToDate(CriteriaDTO criteria)
        {
            var value = criteria.SectionCriteriaList?
                .SelectMany(s => s.AttributesCriteriaList)
                .FirstOrDefault(x => x.FieldName == "PeriodToDate")?.FieldValue;

            var date = ConvertUnixToUtc(value);

            if (date == null)
                return null;

            // Full day
            return date.Value.Date.AddDays(1).AddTicks(-1);
        }

        private DateTime? ConvertUnixToUtc(object? value)
        {
            if (value == null)
                return null;

            long unixTime;

            if (value is System.Text.Json.JsonElement element)
            {
                unixTime = element.ValueKind == System.Text.Json.JsonValueKind.Number
                    ? element.GetInt64()
                    : long.Parse(element.GetString()!);
            }
            else
            {
                unixTime = Convert.ToInt64(value);
            }

            return DateTimeOffset
                .FromUnixTimeSeconds(unixTime)
                .UtcDateTime;
        }

        private string ConvertMinutesToTimeString(int totalMinutes)
        {
            if (totalMinutes <= 0 || totalMinutes >= 1440) return "00:00"; // 1440 = 24hrs max
            int hours = totalMinutes / 60;
            int minutes = totalMinutes % 60;
            return $"{hours:D2}:{minutes:D2}";
        }

        private string ConvertMinutesToDurationString(int durationMinutes)
        {
            if (durationMinutes <= 0 || durationMinutes >= 1440) return "00:00";
            int hours = durationMinutes / 60;
            int minutes = durationMinutes % 60;
            return $"{hours:D2}:{minutes:D2}";
        }
        public async Task<string> TimeSlip(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool IsCount = false)
        {
            try
            {
                string Json = "";
                if (IsCount == false)
                {
                    string SQL = TimeSlipQB.GET_LIST_TIMESLIP;

                    // int fields
                    int employeeId = GetCriteriaValue(CriteriaDTO, "EmployeeId", -1);
                    int ouId = GetCriteriaValue(CriteriaDTO, "OUId", -1);
                    int entityId = GetCriteriaValue(CriteriaDTO, "EntityId", -1);
                    int bizTransactionClassId = GetCriteriaValue(CriteriaDTO, "BIZTransactionClassId", -1);

                    // long fields (Unix timestamps)
                    long periodFromDate = GetCriteriaValueLong(CriteriaDTO, "PeriodFromDate", -1);
                    long periodToDate = GetCriteriaValueLong(CriteriaDTO, "PeriodToDate", -1);

                    var parameter = new
                    {
                        firstnumber = FirstNumber,
                        maxresult = MaxResult,
                        employeeid = employeeId,
                        ouid = ouId,
                        periodfromdate = periodFromDate,
                        periodtodate = periodToDate,
                        entityid = entityId,
                        biztransactionclassid = bizTransactionClassId
                    };

                    var Result = await _QueryExecutor.QueryAsync<TimeSlipListDTO>(LoginDTO, SQL, parameter);

                    // Apply calculated fields
                    foreach (var item in Result)
                    {
                        item.TimeSlipTimeSlipCalStartTime = ConvertMinutesToTimeString(item.TimeSlipTimeSlipStartTime);
                        item.TimeSlipTimeSlipCalEndTime = ConvertMinutesToTimeString(item.TimeSlipTimeSlipEndTime);
                        item.TimeSlipCalDuration = ConvertMinutesToDurationString(item.TimeSlipDuration);
                        item.TimeSlipDurationHour = item.TimeSlipDuration / 60;
                        item.TotalPermission = ConvertMinutesToDurationString(item.TotalDurationMinutes);
                        item.PersonalDuration = ConvertMinutesToDurationString(item.PersonalDurationMinutes);
                        item.OfficialDuration = item.OfficialDurationMinutes > 0
                                                            ? ConvertMinutesToDurationString(item.OfficialDurationMinutes)
                                                            : "0";
                    }

                    var settings = new JsonSerializerSettings
                    {
                        DateFormatHandling = DateFormatHandling.MicrosoftDateFormat,
                        DateTimeZoneHandling = DateTimeZoneHandling.Utc
                    };

                    Json = JsonConvert.SerializeObject(Result, settings);
                }
                else
                {
                    int Count = 0;
                    Json = Count.ToString();
                }
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        private int GetCriteriaValue(CriteriaDTO criteriaDTO, string fieldName, int defaultValue)
        {
            try
            {
                var attr = criteriaDTO?.SectionCriteriaList?
                    .SelectMany(s => s.AttributesCriteriaList)
                    .FirstOrDefault(a => a.FieldName == fieldName);

                if (attr?.FieldValue is System.Text.Json.JsonElement je)
                    return je.ValueKind == System.Text.Json.JsonValueKind.Number ? je.GetInt32()
                         : int.TryParse(je.GetString(), out int p) ? p : defaultValue;

                return attr != null ? Convert.ToInt32(attr.FieldValue) : defaultValue;
            }
            catch { return defaultValue; }
        }
        private long GetCriteriaValueLong(CriteriaDTO criteriaDTO, string fieldName, long defaultValue)
        {
            try
            {
                var attr = criteriaDTO?.SectionCriteriaList?
                    .SelectMany(s => s.AttributesCriteriaList)
                    .FirstOrDefault(a => a.FieldName == fieldName);

                if (attr?.FieldValue is System.Text.Json.JsonElement je)
                    return je.ValueKind == System.Text.Json.JsonValueKind.Number ? je.GetInt64()
                         : long.TryParse(je.GetString(), out long p) ? p : defaultValue;

                return attr != null ? Convert.ToInt64(attr.FieldValue) : defaultValue;
            }
            catch { return defaultValue; }
        }


        public async Task<string> SelectList(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool IsCount = false)
        {
            try
            {
                string Json = "";
                if (IsCount == false)
                {
                    string SQL = TimeSlipQB.SELECT_LIST;

                    // Extract criteria values
                    int bizTransactionTypeId = GetCriteriaValue(CriteriaDTO, "BizTransactionTypeId", -1);
                    int employeeId = GetCriteriaValue(CriteriaDTO, "EmployeeId", -1);
                    int timeSlipType = GetCriteriaValue(CriteriaDTO, "TimeSlipType", -1);
                    string number = GetCriteriaStringValue(CriteriaDTO, "Number", "");
                    string employeeCode = GetCriteriaStringValue(CriteriaDTO, "EmployeeCode", "");
                    string employeeName = GetCriteriaStringValue(CriteriaDTO, "EmployeeName", "");

                    var parameter = new
                    {
                        firstnumber = FirstNumber,
                        maxresult = MaxResult,
                        biztransactiontypeid = bizTransactionTypeId,
                        employeeid = employeeId,
                        timesliptype = timeSlipType,
                        number = number,
                        employeecode = employeeCode,
                        employeename = employeeName
                    };

                    var Result = await _QueryExecutor.QueryAsync<TimeSlipPicklistDTO>(LoginDTO, SQL, parameter);

                    var settings = new JsonSerializerSettings
                    {
                        DateFormatHandling = DateFormatHandling.MicrosoftDateFormat,
                        DateTimeZoneHandling = DateTimeZoneHandling.Utc
                    };

                    Json = JsonConvert.SerializeObject(Result, settings);
                }
                else
                {
                    int Count = 0;
                    Json = Count.ToString();
                }
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        // String criteria extractor
        private string GetCriteriaStringValue(CriteriaDTO criteriaDTO, string fieldName, string defaultValue)
        {
            try
            {
                var attr = criteriaDTO?.SectionCriteriaList?
                    .SelectMany(s => s.AttributesCriteriaList)
                    .FirstOrDefault(a => a.FieldName == fieldName);

                if (attr?.FieldValue is System.Text.Json.JsonElement je)
                    return je.ValueKind == System.Text.Json.JsonValueKind.String
                           ? je.GetString() ?? defaultValue
                           : defaultValue;

                return attr != null ? Convert.ToString(attr.FieldValue) ?? defaultValue : defaultValue;
            }
            catch { return defaultValue; }
        }
        public async Task<TimeSlipDTO?> CheckDuplicateAsync(TimeSlipDTO dto, LoginDTO login, DbTransaction tx)
        {
            var result = await _QueryExecutor.QueryAsync<TimeSlipDTO>(
                login,
                TimeSlipQB.CHECK_ATTENDANCE_FOR_SAME_DATE,
                new
                {
                    EmployeeId = dto.EmployeeId,
                    AttendanceDate = dto.TimeSlipAttendanceDate,
                    StartTime = dto.TimeSlipTimeSlipStartTime,
                    EndTime = dto.TimeSlipTimeSlipEndTime
                },
                tx
            );

            return result.FirstOrDefault();
        }

        /* ================= PERIOD ================= */

        public async Task<PeriodDTO?> GetPeriodAsync(int periodId, LoginDTO login, DbTransaction tx)
        {
            var result = await _QueryExecutor.QueryAsync<PeriodDTO>(
                login,
                TimeSlipQB.GET_PERIOD,
                new { PeriodId = periodId },
                tx
            );

            return result.FirstOrDefault();
        }

        /* ================= PERMISSION ================= */

        public async Task<string?> CheckPermissionAsync(TimeSlipDTO dto, LoginDTO login, DbTransaction tx)
        {
            return await _QueryExecutor.ExecuteScalarAsync<string>(
                login,
                TimeSlipQB.CHECK_PERMISSION,
                new
                {
                    EmployeeId = dto.EmployeeId,
                    AttendanceDate = dto.TimeSlipAttendanceDate,
                    TimeSlipType = dto.TimeSlipType,
                    Duration = dto.TimeSlipDuration,
                    TimeSlipId = dto.TimeSlipId
                },
                tx
            );
        }

        public async Task<TimeSlipDTO?> GetEmployeeDataAsync(int employeeId, LoginDTO login, DbTransaction tx)
        {
            var result = await _QueryExecutor.QueryAsync<TimeSlipDTO>(
                login,
                TimeSlipQB.GET_EMPLOYEE_FOR_TIMESLIP,
                new { EmployeeId = employeeId },
                tx
            );
            return result.FirstOrDefault();
        }
    public async Task<TimeSlipDTO?> GetTimeSlipBasic(int timeSlipId, LoginDTO login)
        {
            const string sql = @"SELECT 
                            TIMESLIPID     AS TimeSlipId,
                            TYPE           AS TimeSlipType,
                            TIMESLIPNUMBER AS TimeSlipNumber
                         FROM TTIMESLIP
                         WHERE TIMESLIPID = @TimeSlipId";

            var result = await _QueryExecutor.QueryAsync<TimeSlipDTO>(
                login,
                sql,
                new { TimeSlipId = timeSlipId }
            );

            return result.FirstOrDefault();
        }
    }
}