using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Linq;
using System.Text.Json;
using System.Threading.Tasks;
using Dapper;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.DateConverter;
using PayRollDAL.DTO.Calendar;
using PayRollDAL.DTO.Employee;
using PayRollDAL.Query.Calendar;

namespace PayRollDAL.CustomeCode.Calendar
{
    public class CalendarDAL : ICalendarDAL
    {
        private readonly IQueryExecutor _queryExecutor;

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

        public async Task<string> GetCalendarInfo(
            int EmployeeId,
            int EmployeeLevel,
            string FromDate,
            string ToDate,
            bool ShowAllEmployees,
            LoginDTO loginDTO)
        {
            try
            {
                if (EmployeeLevel < -1)
                    EmployeeLevel = 0;

                /* =========================
                   JSON OPTIONS (CREATE ONCE)
                   ========================= */

                var options = new JsonSerializerOptions
                {
                    PropertyNamingPolicy = null
                };
                options.AddGB5Converters();

                /* =========================
                   STEP 2 — DATE PARSE SAFE
                   ========================= */

                if (!DateTime.TryParse(FromDate, CultureInfo.InvariantCulture, DateTimeStyles.None, out var from))
                    throw new ArgumentException("Invalid FromDate");

                if (!DateTime.TryParse(ToDate, CultureInfo.InvariantCulture, DateTimeStyles.None, out var to))
                    throw new ArgumentException("Invalid ToDate");

                /* =========================
                   STEP 3 — CALL PROCEDURE
                   ========================= */

                SqlMapper.GridReader multi;

                if (!ShowAllEmployees)
                {
                    /* ── PATH A: ShowAllEmployees = 0 ───────────────────────────
                       Original behaviour — build employee list from hierarchy
                       and pass it to the procedure (3 parameters).
                       ─────────────────────────────────────────────────────────── */

                    /* STEP 1 — EMPLOYEE LIST */

                    var employees =
                        await _queryExecutor.QueryAsync<EmployeeHierarchyDTO>(
                            loginDTO,
                            CalendarQB.GET_EMPLOYEE_BASED_ON_LEVEL,
                            new
                            {
                                EmployeeId,
                                EmployeeLevel
                            });

                    var employeeTable = new DataTable();
                    employeeTable.Columns.Add("EmployeeId", typeof(long));

                    foreach (var emp in employees)
                        employeeTable.Rows.Add(emp.EmployeeId);

                    if (employeeTable.Rows.Count == 0)
                        return JsonSerializer.Serialize(new List<MonthlyCalendarDTO>(), options);

                    multi = await _queryExecutor.QueryMultipleAsync(
    loginDTO,
    "dbo.GetEmployeeAttendanceMonthWise",
    new
    {
        EmployeeIds = employeeTable,
        FromDate = from,
        ToDate = to
    },
    transaction: null,
    commandType: CommandType.StoredProcedure);
                }
                else
                {
                    /* ── PATH B: ShowAllEmployees = 1 ───────────────────────────
                       New behaviour — pass the logged-in user's UserId via the
                       EmployeeIds TVP. The SP joins MUSERACCESSRIGHTS on that
                       UserId to resolve all accessible OUIDs, then returns every
                       active employee whose WORKOUID is in those OUIDs.
                       ─────────────────────────────────────────────────────────── */

                    var userIdTable = new DataTable();
                    userIdTable.Columns.Add("EmployeeId", typeof(long));
                    userIdTable.Rows.Add(loginDTO.UserId);

                    multi = await _queryExecutor.QueryMultipleAsync(
    loginDTO,
    "dbo.GetEmployeeAttendanceMonthWise",
    new
    {
        EmployeeIds = userIdTable,
        FromDate = from,
        ToDate = to,
        ShowAllEmployees = true
    },
    transaction: null,
    commandType: CommandType.StoredProcedure);
                }

                List<MonthlyCalendarDTO> monthly;
                List<dynamic>            dayCounts;
                List<CalendarDetailDTO>  details;

                using (multi)
                {
                    monthly   = (await multi.ReadAsync<MonthlyCalendarDTO>()).ToList();
                    dayCounts = (await multi.ReadAsync<dynamic>()).ToList(); // optional
                    details   = (await multi.ReadAsync<CalendarDetailDTO>()).ToList();
                }

                /* =========================
                   STEP 4 — MERGE DETAILS FAST
                   ========================= */

                var detailLookup = details
                    .GroupBy(d => new
                    {
                        d.EmployeeId,
                        Year = d.AttendanceDate.Year,
                        Month = d.AttendanceDate.Month
                    })
                    .ToDictionary(g => g.Key, g => g.ToList());

                foreach (var month in monthly)
                {
                    var key = new
                    {
                        month.EmployeeId,
                        Year = month.Year,
                        Month = month.MonthNumber
                    };

                    if (detailLookup.TryGetValue(key, out var empDetails))
                        month.Details = empDetails;
                    else
                        month.Details = new List<CalendarDetailDTO>();
                }

                /* =========================
                   STEP 5 — SERIALIZE
                   ========================= */

                return JsonSerializer.Serialize(monthly, options);
            }
            catch (Exception ex)
            {
                throw new ApplicationException(
                    "An error occurred while fetching calendar information.",
                    ex);
            }
        }

    }
}