using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using Newtonsoft.Json;
using PayRollDAL.DTO.Calendar;
using PayRollDAL.DTO.Leave;
using PayRollDAL.DTO.TeamCalendar;
using PayRollDAL.DTO.TLeave;
using PayRollDAL.DTO.YearlyCalendar;
using PayRollDAL.Query.TeamCalendar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace PayRollDAL.CustomeCode.TeamCalendar
{
    public class TeamCalendarDAL : ITeamCalendarDAL
    {
        private readonly IQueryExecutor _queryExecutor;

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

        public async Task<string> GetFiltersTeamCalendar(
            long employeeId,
            int employeeLevel,
            DateTime fromDate,
            DateTime toDate,
            bool showAllEmployees,
            string ouIds,
            byte employeeStatus,
            bool team,
            int firstNumber,
            int maxNumber,
            CriteriaDTO criteriaDTO,
            LoginDTO loginDTO)
        {
            try
            {
                // ── STEP 1: Resolve employee list ──────────────────────────
                // showAllEmployees = true  → all employees in given OUIDs
                //                           filtered by EmployeeStatus
                // showAllEmployees = false → junior hierarchy under employeeId
                //                           filtered by EmployeeLevel + EmployeeStatus

                // Convert 1-based FirstNumber/MaxNumber to SQL OFFSET/FETCH
                int offset = Math.Max(0, firstNumber - 1);
                int fetchCount = Math.Max(1, maxNumber - firstNumber + 1);

                List<EmployeeLevelDTO> employees;

                if (showAllEmployees)
                {
                    var ouEmployees = await _queryExecutor.QueryAsync<EmployeeLevelDTO>(
                        loginDTO,
                        TeamCalendarQB.GET_EMPLOYEES_BY_OU,
                        new
                        {
                            OUIds = ouIds,
                            EmployeeStatus = employeeStatus,
                            Offset = offset,
                            FetchCount = fetchCount
                        });

                    employees = ouEmployees.ToList();
                }
                else
                {
                    var hierarchy = await _queryExecutor.QueryAsync<EmployeeLevelDTO>(
                        loginDTO,
                        TeamCalendarQB.GET_EMPLOYEE_BASED_ON_LEVEL,
                        new
                        {
                            EmployeeId = employeeId,
                            EmployeeLevel = employeeLevel,
                            EmployeeStatus = employeeStatus,
                            Offset = offset,
                            FetchCount = fetchCount
                        });

                    employees = hierarchy
                        .Where(h => h.EmployeeId.HasValue)
                        .ToList();
                }

                if (employees.Count == 0)
                    return JsonConvert.SerializeObject(
                        new List<TeamCalendarResponseDTO>());

                // ── STEP 2: Build employee ID CSV ──────────────────────────
                // Scopes both calendar and permission queries to only the
                // resolved employee list — no extra in-memory filtering needed

                var employeeIdsCsv = string.Join(",",
                    employees
                        .Where(e => e.EmployeeId.HasValue)
                        .Select(e => e.EmployeeId!.Value));

                // ── STEP 3: Fire calendar + permission in parallel ──────────

                var calendarTask = _queryExecutor.QueryAsync<TeamCalendarMonthDTO>(
                    loginDTO,
                    TeamCalendarQB.GET_TEAM_CALENDAR,
                    new
                    {
                        EmployeeIds = employeeIdsCsv,
                        FromDate = fromDate,
                        ToDate = toDate
                    });

                var permissionTask = _queryExecutor.QueryAsync<PermssionDetailDTO>(
                    loginDTO,
                    TeamCalendarQB.GET_TEAM_CALENDAR_PERMISSIONS,
                    new
                    {
                        EmployeeIds = employeeIdsCsv,
                        PeriodFrom = TruncateToSecond(fromDate),
                        PeriodTo = TruncateToSecond(toDate)
                    });

                await Task.WhenAll(calendarTask, permissionTask);

                var rawRows = (await calendarTask).ToList();
                var allPerms = (await permissionTask).ToList();

                // ── STEP 4: Apply permission field defaults ─────────────────
                foreach (var p in allPerms)
                {
                    p.Type ??= "Official";
                    p.Location ??= string.Empty;
                    p.Remarks ??= string.Empty;
                    p.ApprovedByCode ??= string.Empty;
                    p.ApprovedByName ??= string.Empty;
                }

                // ── STEP 5: Build lookups ──────────────────────────────────

                // Calendar grouped by EmployeeId
                var calendarLookup = rawRows
                    .GroupBy(r => r.EmployeeId)
                    .ToDictionary(g => g.Key, g => g.ToList());

                // Permissions grouped by EmployeeId
                var permissionLookup = allPerms
                    .GroupBy(p => (long)p.EmployeeId)
                    .ToDictionary(g => g.Key, g => g.ToList());

                // ── STEP 6: Build response — one entry per employee ─────────

                var response = new List<TeamCalendarResponseDTO>();

                foreach (var emp in employees.Where(e => e.EmployeeId.HasValue))
                {
                    long empId = (long)emp.EmployeeId!.Value;

                    // Scaffold: all months in range with empty day data
                    var scaffold = GenerateScaffold(fromDate, toDate);

                    // Merge actual calendar data into scaffold
                    if (calendarLookup.TryGetValue(empId, out var monthRows))
                    {
                        var monthLookup = monthRows
                            .ToDictionary(m => new
                            {
                                Year = m.Year ?? 0,
                                MonthNumber = m.MonthNumber ?? 0
                            });

                        foreach (var scaffoldMonth in scaffold)
                        {
                            var key = new
                            {
                                Year = scaffoldMonth.Year ?? 0,
                                MonthNumber = scaffoldMonth.MonthNumber ?? 0
                            };

                            if (monthLookup.TryGetValue(key, out var actual))
                                MergeMonth(actual, scaffoldMonth);
                        }
                    }

                    // Apply defaults to scaffold
                    ApplyDefaults(scaffold);

                    // Get permissions — empty list if none in date range
                    var empPermissions = permissionLookup
                        .TryGetValue(empId, out var perms)
                        ? perms
                        : new List<PermssionDetailDTO>();

                    response.Add(new TeamCalendarResponseDTO
                    {
                        EmployeeId = empId,
                        EmployeeCode = emp.EmployeeCode ?? string.Empty,
                        EmployeeName = emp.EmployeeName ?? string.Empty,
                        Months = scaffold,
                        Permissions = empPermissions
                    });
                }

                // ── STEP 7: Serialize ──────────────────────────────────────
                return JsonConvert.SerializeObject(response);
            }
            catch (Exception ex)
            {
                throw new Exception(
                    "Error in TeamCalendarDAL.GetFiltersTeamCalendar: " + ex.Message, ex);
            }
        }

        // ── Private Helpers ────────────────────────────────────────────────

        private static DateTime TruncateToSecond(DateTime dt) =>
            new DateTime(dt.Year, dt.Month, dt.Day,
                         dt.Hour, dt.Minute, dt.Second);

        private List<YearlyCalendarDTO> GenerateScaffold(
            DateTime fromDate, DateTime toDate)
        {
            var result = new List<YearlyCalendarDTO>();
            for (var d = new DateTime(fromDate.Year, fromDate.Month, 1);
                 d <= toDate;
                 d = d.AddMonths(1))
            {
                result.Add(new YearlyCalendarDTO
                {
                    Year = d.Year,
                    MonthNumber = d.Month,
                    Month = d.ToString("MMM")
                });
            }
            return result;
        }

        private void MergeMonth(
     TeamCalendarMonthDTO source, YearlyCalendarDTO target)
        {
            target.Year = source.Year;
            target.MonthNumber = source.MonthNumber;
            target.Month = source.Month;

            for (int day = 1; day <= 31; day++)
            {
                CopyProp(source, target, $"D{day}");
                CopyProp(source, target, $"LeaveId{day}");
                CopyProp(source, target, $"LeaveCode{day}");
                CopyProp(source, target, $"LeaveName{day}");
                CopyProp(source, target, $"LateIn{day}");    // ← NEW
                CopyProp(source, target, $"EarlyOut{day}");  // ← NEW
            }
        }

        private static void CopyProp(
            TeamCalendarMonthDTO src,
            YearlyCalendarDTO tgt,
            string propName)
        {
            var srcProp = typeof(TeamCalendarMonthDTO).GetProperty(propName);
            var tgtProp = typeof(YearlyCalendarDTO).GetProperty(propName);
            if (srcProp == null || tgtProp == null) return;
            object val = srcProp.GetValue(src);
            if (val != null) tgtProp.SetValue(tgt, val);
        }

        private void ApplyDefaults(List<YearlyCalendarDTO> months)
        {
            foreach (var month in months)
            {
                for (int day = 1; day <= 31; day++)
                {
                    // D → -1 when null (no data working day)
                    var dProp = typeof(YearlyCalendarDTO).GetProperty($"D{day}");
                    if (dProp != null && dProp.GetValue(month) == null)
                        dProp.SetValue(month, -1);

                    // LeaveId → -1 when null
                    var lidProp = typeof(YearlyCalendarDTO).GetProperty($"LeaveId{day}");
                    if (lidProp != null && lidProp.GetValue(month) == null)
                        lidProp.SetValue(month, (long)-1);

                    // LeaveCode → "NONE" when null
                    var lcProp = typeof(YearlyCalendarDTO).GetProperty($"LeaveCode{day}");
                    if (lcProp != null && lcProp.GetValue(month) == null)
                        lcProp.SetValue(month, "NONE");

                    // LeaveName → "NONE" when null
                    var lnProp = typeof(YearlyCalendarDTO).GetProperty($"LeaveName{day}");
                    if (lnProp != null && lnProp.GetValue(month) == null)
                        lnProp.SetValue(month, "NONE");

                    // LateIn → null means no late in (no default needed)
                    // EarlyOut → null means no early out (no default needed)
                }
            }
        }
    }
}