using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using Newtonsoft.Json;
using TMSDAL.DTO.TrainingPlanDashboard;
using TMSDAL.Query.TrainingPlanDashboard;

namespace TMSDAL.CustomCode.TrainingPlanDashboard
{
    public class TrainingPlanDashboardDAL : ITrainingPlanDashboardDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

        public TrainingPlanDashboardDAL(IQueryExecutor queryExecutor)
        {
            _QueryExecutor = queryExecutor;
        }

        public async Task<string> GetTrainingPlanSummary(LoginDTO loginDTO)
        {
            var result = await _QueryExecutor.QuerySingleAsync<TrainingPlanSummaryDTO>(
                loginDTO, TrainingPlanDashboardQB.GET_TRAINING_PLAN_SUMMARY,
                new { TenantId = loginDTO.ClientId });
            return JsonConvert.SerializeObject(result);
        }

        public async Task<string> GetEnrollmentFunnel(LoginDTO loginDTO)
        {
            var result = await _QueryExecutor.QuerySingleAsync<EnrollmentFunnelDTO>(
                loginDTO, TrainingPlanDashboardQB.GET_ENROLLMENT_FUNNEL,
                new { TenantId = loginDTO.ClientId });
            return JsonConvert.SerializeObject(result);
        }

        public async Task<string> GetActiveInstances(LoginDTO loginDTO)
        {
            var result = await _QueryExecutor.QueryAsync<InstanceStatusBreakdownDTO>(
                loginDTO, TrainingPlanDashboardQB.GET_ACTIVE_INSTANCES,
                new { TenantId = loginDTO.ClientId });
            EnrichInstanceTypeMeta(result);
            return JsonConvert.SerializeObject(result);
        }

        public async Task<string> GetDomainCompletion(LoginDTO loginDTO)
        {
            var result = await _QueryExecutor.QueryAsync<DomainCompletionDTO>(
                loginDTO, TrainingPlanDashboardQB.GET_DOMAIN_COMPLETION,
                new { TenantId = loginDTO.ClientId });
            return JsonConvert.SerializeObject(result);
        }

        public async Task<string> GetDomainEmployees(int trainingDomainId, LoginDTO loginDTO)
        {
            var result = await _QueryExecutor.QueryAsync<DomainEmployeeDTO>(
                loginDTO, TrainingPlanDashboardQB.GET_DOMAIN_EMPLOYEES,
                new { TenantId = loginDTO.ClientId, TrainingDomainId = trainingDomainId });
            return JsonConvert.SerializeObject(result);
        }

        public async Task<string> GetTrainerWorkload(LoginDTO loginDTO)
        {
            var result = await _QueryExecutor.QueryAsync<TrainerWorkloadDTO>(
                loginDTO, TrainingPlanDashboardQB.GET_TRAINER_WORKLOAD,
                new { TenantId = loginDTO.ClientId });
            return JsonConvert.SerializeObject(result);
        }

        public async Task<string> GetExpiringCertificates(LoginDTO loginDTO)
        {
            var result = await _QueryExecutor.QueryAsync<ExpiringCertificateDTO>(
                loginDTO, TrainingPlanDashboardQB.GET_EXPIRING_CERTIFICATES,
                new { TenantId = loginDTO.ClientId });
            return JsonConvert.SerializeObject(result);
        }

        public async Task<string> GetTrainingNeeds(LoginDTO loginDTO)
        {
            var result = await _QueryExecutor.QueryAsync<TrainingNeedDTO>(
                loginDTO, TrainingPlanDashboardQB.GET_TRAINING_NEEDS,
                new { TenantId = loginDTO.ClientId });
            return JsonConvert.SerializeObject(result);
        }

        public async Task<string> GetProgrammePerformance(LoginDTO loginDTO)
        {
            var result = await _QueryExecutor.QueryAsync<ProgrammePerformanceDTO>(
                loginDTO, TrainingPlanDashboardQB.GET_PROGRAMME_PERFORMANCE,
                new { TenantId = loginDTO.ClientId });
            EnrichProgrammeTypeMeta(result);
            return JsonConvert.SerializeObject(result);
        }

        public async Task<string> GetTrainingPlanDashboard(
            int? trainingDomainId, LoginDTO loginDTO,
            string? updationResponsibility = null, string? reviewFrequency = null)
        {
            var p     = new { TenantId = loginDTO.ClientId, TrainingDomainId = trainingDomainId ?? 0 };
            var basep = new { TenantId = loginDTO.ClientId };

            var summaryTask     = _QueryExecutor.QuerySingleAsync<TrainingPlanSummaryDTO>(loginDTO, TrainingPlanDashboardQB.GET_TRAINING_PLAN_SUMMARY, basep);
            var funnelTask      = _QueryExecutor.QuerySingleAsync<EnrollmentFunnelDTO>(loginDTO, TrainingPlanDashboardQB.GET_ENROLLMENT_FUNNEL, basep);
            var instancesTask   = _QueryExecutor.QueryAsync<InstanceStatusBreakdownDTO>(loginDTO, TrainingPlanDashboardQB.GET_ACTIVE_INSTANCES, basep);
            var domainTask      = _QueryExecutor.QueryAsync<DomainCompletionDTO>(loginDTO, TrainingPlanDashboardQB.GET_DOMAIN_COMPLETION, basep);
            var trainerTask     = _QueryExecutor.QueryAsync<TrainerWorkloadDTO>(loginDTO, TrainingPlanDashboardQB.GET_TRAINER_WORKLOAD, basep);
            var certTask        = _QueryExecutor.QueryAsync<ExpiringCertificateDTO>(loginDTO, TrainingPlanDashboardQB.GET_EXPIRING_CERTIFICATES, basep);
            var needsTask       = _QueryExecutor.QueryAsync<TrainingNeedDTO>(loginDTO, TrainingPlanDashboardQB.GET_TRAINING_NEEDS, basep);
            var programmeTask   = _QueryExecutor.QueryAsync<ProgrammePerformanceDTO>(loginDTO, TrainingPlanDashboardQB.GET_PROGRAMME_PERFORMANCE, basep);
            var ganttTask       = _QueryExecutor.QueryAsync<GanttFlatRowDTO>(loginDTO, TrainingPlanDashboardQB.GET_GANTT_TIMELINE, p);
            var headerTask      = _QueryExecutor.QuerySingleAsync<PlanHeaderFlatDTO>(loginDTO, TrainingPlanDashboardQB.GET_PLAN_HEADER, p);

            await Task.WhenAll(summaryTask, funnelTask, instancesTask, domainTask, trainerTask, certTask, needsTask, programmeTask, ganttTask, headerTask);

            // Domain employee drill-down — only fired when a specific domain is requested
            List<DomainEmployeeDTO>? domainEmployees = null;
            if (trainingDomainId.HasValue && trainingDomainId.Value > 0)
            {
                var dp = new { TenantId = loginDTO.ClientId, TrainingDomainId = trainingDomainId.Value };
                var empResult = await _QueryExecutor.QueryAsync<DomainEmployeeDTO>(
                    loginDTO, TrainingPlanDashboardQB.GET_DOMAIN_EMPLOYEES, dp);
                // Assign sequential Sl.No. per PDF column
                if (empResult != null)
                {
                    int slno = 1;
                    foreach (var emp in empResult)
                        emp.SlNo = slno++;
                }
                domainEmployees = empResult?.ToList();
            }

            var instances   = instancesTask.Result?.ToList();
            var programmes  = programmeTask.Result?.ToList();
            EnrichInstanceTypeMeta(instances);
            EnrichProgrammeTypeMeta(programmes);

            var ganttRows = BuildGanttTimeline(ganttTask.Result);

            // Build plan header — merge DB values with caller-supplied document metadata
            var hFlat = headerTask.Result;
            var planHeader = new TrainingPlanHeaderDTO
            {
                UpdationResponsibility = updationResponsibility,
                ReviewFrequency        = reviewFrequency,
                DepartmentId           = hFlat?.DepartmentId,
                DepartmentName         = hFlat?.DepartmentName,
                UpdationDate           = hFlat?.UpdationDate,
                TrainingDomainId       = hFlat?.TrainingDomainId,
                DomainName             = hFlat?.DomainName,
                GeneratedOn            = DateTime.Now
            };

            var dashboard = new TrainingPlanFullDashboardDTO
            {
                PlanHeader           = planHeader,
                TrainingTypeLegend   = TrainingTypeMeta.All(),
                Summary              = summaryTask.Result,
                EnrollmentFunnel     = funnelTask.Result,
                Instances            = instances,
                DomainCompletion     = domainTask.Result?.ToList(),
                GanttMonthColumns    = BuildGanttMonthColumns(ganttRows),
                GanttTimeline        = ganttRows,
                TrainerWorkload      = trainerTask.Result?.ToList(),
                ExpiringCertificates = certTask.Result?.ToList(),
                TrainingNeeds        = needsTask.Result?.ToList(),
                ProgrammePerformance = programmes
            };

            // Attach domain employees if requested — filter instances list too
            if (trainingDomainId.HasValue && trainingDomainId.Value > 0)
            {
                dashboard.DomainEmployees = domainEmployees;
                dashboard.Instances = dashboard.Instances?
                    .Where(i => i.TrainingDomainId == trainingDomainId.Value)
                    .ToList();
                dashboard.DomainCompletion = dashboard.DomainCompletion?
                    .Where(d => d.TrainingDomainId == trainingDomainId.Value)
                    .ToList();
            }

            return JsonConvert.SerializeObject(dashboard);
        }

        // ── Color + duration enrichment ───────────────────────────────────────
        private static void EnrichInstanceTypeMeta(IEnumerable<InstanceStatusBreakdownDTO>? items)
        {
            if (items == null) return;
            foreach (var item in items)
            {
                var meta = TrainingTypeMeta.Get(item.ProgrammeType);
                item.TrainingTypeName    = meta.TypeName;
                item.ColorCode           = meta.ColorCode;
                item.PatternStyle        = meta.PatternStyle;
                item.StandardDurationDays = meta.StandardDurationDays;
                item.PlannedDurationDays  = (int)(item.PlannedEndDate - item.PlannedStartDate).TotalDays;
                item.ActualDurationDays   = item.ActualEndDate.HasValue && item.ActualStartDate.HasValue
                    ? (int)(item.ActualEndDate.Value - item.ActualStartDate.Value).TotalDays
                    : null;
                item.IsDelayed    = item.ActualEndDate.HasValue && item.ActualEndDate.Value > item.PlannedEndDate;
                item.IsRetraining = item.ProgrammeType == 13;
                item.StartMonthLabel = item.PlannedStartDate.ToString("MMM-yy");
                item.EndMonthLabel   = item.PlannedEndDate.ToString("MMM-yy");
            }
        }

        private static void EnrichProgrammeTypeMeta(IEnumerable<ProgrammePerformanceDTO>? items)
        {
            if (items == null) return;
            foreach (var item in items)
            {
                var meta = TrainingTypeMeta.Get(item.ProgrammeType);
                item.TrainingTypeName = meta.TypeName;
                item.ColorCode        = meta.ColorCode;
                item.PatternStyle     = meta.PatternStyle;
            }
        }

        // ── Gantt grouping ────────────────────────────────────────────────────
        private static List<GanttRowDTO> BuildGanttTimeline(IEnumerable<GanttFlatRowDTO>? flat)
        {
            if (flat == null) return new();

            var groups = flat
                .GroupBy(r => new { r.EmployeeId, r.EmployeeCode, r.EmployeeName })
                .OrderBy(g => g.Key.EmployeeName)
                .ToList();

            return groups
                .Select((g, idx) => new GanttRowDTO
                {
                    SlNo         = idx + 1,
                    EmployeeId   = g.Key.EmployeeId,
                    EmployeeCode = g.Key.EmployeeCode,
                    EmployeeName = g.Key.EmployeeName,
                    Bars = g.Select(r =>
                    {
                        var meta = TrainingTypeMeta.Get(r.ProgrammeType);
                        return new GanttBarDTO
                        {
                            InstanceId              = r.InstanceId,
                            InstanceCode            = r.InstanceCode,
                            ProgrammeTitle          = r.ProgrammeTitle,
                            ProgrammeType           = r.ProgrammeType,
                            TrainingTypeName        = meta.TypeName,
                            ColorCode               = meta.ColorCode,
                            PatternStyle            = meta.PatternStyle,
                            PlannedStartDate        = r.PlannedStartDate,
                            PlannedEndDate          = r.PlannedEndDate,
                            PlannedDurationDays     = r.PlannedDurationDays,
                            StandardDurationDays    = meta.StandardDurationDays,
                            PlannedStartMonthLabel  = r.PlannedStartDate.ToString("MMM-yy"),
                            PlannedStartWeek        = WeekOfMonth(r.PlannedStartDate),
                            PlannedEndMonthLabel    = r.PlannedEndDate.ToString("MMM-yy"),
                            PlannedEndWeek          = WeekOfMonth(r.PlannedEndDate),
                            ActualStartDate         = r.ActualStartDate,
                            ActualEndDate           = r.ActualEndDate,
                            ActualDurationDays      = r.ActualDurationDays,
                            ActualStartMonthLabel   = r.ActualStartDate?.ToString("MMM-yy"),
                            ActualStartWeek         = r.ActualStartDate.HasValue ? WeekOfMonth(r.ActualStartDate.Value) : null,
                            ActualEndMonthLabel     = r.ActualEndDate?.ToString("MMM-yy"),
                            ActualEndWeek           = r.ActualEndDate.HasValue ? WeekOfMonth(r.ActualEndDate.Value) : null,
                            EnrollmentStatus        = r.EnrollmentStatus,
                            CompletionStatus        = r.CompletionStatus,
                            IsDelayed               = r.IsDelayed == 1,
                            IsRetraining            = r.IsRetraining == 1
                        };
                    }).OrderBy(b => b.PlannedStartDate).ToList()
                })
                .ToList();
        }

        // Week-of-month: day 1–7 → W1, 8–14 → W2, 15–21 → W3, 22+ → W4
        private static int WeekOfMonth(DateTime date) => Math.Min(4, (date.Day - 1) / 7 + 1);

        // ── Gantt month column headers ─────────────────────────────────────────
        // Builds the month axis from the earliest planned start to the latest planned end
        // across all Gantt rows. Always returns at least 12 months (matching PDF Apr-25 → Mar-26).
        internal static List<GanttMonthColumnDTO> BuildGanttMonthColumns(List<GanttRowDTO> rows)
        {
            DateTime axisStart, axisEnd;

            var allBars = rows.SelectMany(r => r.Bars).ToList();
            if (allBars.Count > 0)
            {
                var minDate = allBars.Min(b => b.PlannedStartDate);
                var maxDate = allBars.Max(b => b.PlannedEndDate);
                // Snap to start of month / end of month
                axisStart = new DateTime(minDate.Year, minDate.Month, 1);
                axisEnd   = new DateTime(maxDate.Year, maxDate.Month, 1);
            }
            else
            {
                // Default: current month for 12 months
                axisStart = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
                axisEnd   = axisStart.AddMonths(11);
            }

            var columns = new List<GanttMonthColumnDTO>();
            var current = axisStart;
            int idx = 0;
            while (current <= axisEnd)
            {
                columns.Add(new GanttMonthColumnDTO
                {
                    MonthIndex = idx++,
                    MonthLabel = current.ToString("MMM-yy"),
                    Year       = current.Year,
                    Month      = current.Month
                });
                current = current.AddMonths(1);
            }

            // Ensure at least 12 months in the axis
            while (columns.Count < 12)
            {
                var last = columns.Last();
                var next = new DateTime(last.Year, last.Month, 1).AddMonths(1);
                columns.Add(new GanttMonthColumnDTO
                {
                    MonthIndex = idx++,
                    MonthLabel = next.ToString("MMM-yy"),
                    Year       = next.Year,
                    Month      = next.Month
                });
            }

            return columns;
        }
    }
}