using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using Newtonsoft.Json;
using PayRollDAL.DTO.MonthlyAttendance;
using PayRollDAL.Query.MonthlyAttendance;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace PayRollDAL.CustomeCode.MonthlyAttendance
{
    // Classic master-detail CRUD DAL for TMONTHLYATTENDANCE (header) + TMONTHLYATTENDANCEADDON
    // (child). Out of scope: Crew-Timesheet Prepare/Submit/Approve/Status workflow, BPM/Activiti,
    // wage-rule/posting-position "standard data load" reports, formula-based recalculation - see
    // MonthlyAttendanceQB for the column-mapping rationale.
    public class MonthlyAttendanceDAL : IMonthlyAttendanceDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

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

        public async Task<string> GetMonthlyAttendance(int monthlyAttendanceId, LoginDTO loginDTO)
        {
            try
            {
                var parameters = new { MonthlyAttendanceId = monthlyAttendanceId };

                MonthlyAttendanceDTO data =
                    await _QueryExecutor.QuerySingleAsync<MonthlyAttendanceDTO>(loginDTO, MonthlyAttendanceQB.GET_MONTHLYATTENDANCE, parameters);

                if (data != null)
                {
                    IEnumerable<dynamic> addonRows =
                        await _QueryExecutor.QueryAsync<dynamic>(loginDTO, MonthlyAttendanceQB.GET_MONTHLYATTENDANCEADDON_BY_HEADER, parameters);

                    var firstAddon = addonRows?.FirstOrDefault();
                    if (firstAddon != null)
                    {
                        data.FeAddon = (string)firstAddon.FeAddon;
                    }
                }

                return JsonConvert.SerializeObject(data);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> SaveMonthlyAttendance(MonthlyAttendanceDTO dto, LoginDTO loginDTO)
        {
            try
            {
                int rows = await _QueryExecutor.ExecuteAsync(loginDTO, MonthlyAttendanceQB.SAVE_MONTHLYATTENDANCE, dto);
                await SaveMonthlyAttendanceAddon(dto, loginDTO);
                return rows;
            }
            catch (Exception ex)
            {
                throw new Exception("Error while saving MonthlyAttendance", ex);
            }
        }

        public async Task<int> UpdateMonthlyAttendance(MonthlyAttendanceDTO dto, LoginDTO loginDTO)
        {
            try
            {
                int rows = await _QueryExecutor.ExecuteAsync(loginDTO, MonthlyAttendanceQB.UPDATE_MONTHLYATTENDANCE, dto);
                await SaveMonthlyAttendanceAddon(dto, loginDTO);
                return rows;
            }
            catch (Exception ex)
            {
                throw new Exception("Error while updating MonthlyAttendance", ex);
            }
        }

        // Delete-then-reinsert the addon row(s), matching GB4's DeleteMonthlyAttendnaceDetails +
        // SaveMonthlyAttendance combination, but parameterized instead of string-concatenated and
        // scoped to the child rows only (the header itself is UPDATE-d, never dropped, on Save).
        private async Task SaveMonthlyAttendanceAddon(MonthlyAttendanceDTO dto, LoginDTO loginDTO)
        {
            var headerParameters = new { MonthlyAttendanceId = dto.MonthlyAttendanceId };

            await _QueryExecutor.ExecuteAsync(loginDTO, MonthlyAttendanceQB.DELETE_MONTHLYATTENDANCEADDON_BY_HEADER, headerParameters);

            if (!string.IsNullOrEmpty(dto.FeAddon))
            {
                var addonParameters = new
                {
                    Id = dto.MonthlyAttendanceId,
                    MonthlyAttendanceId = dto.MonthlyAttendanceId,
                    FeAddon = dto.FeAddon,
                    GetAddon = dto.FeAddon
                };

                await _QueryExecutor.ExecuteAsync(loginDTO, MonthlyAttendanceQB.SAVE_MONTHLYATTENDANCEADDON, addonParameters);
            }
        }

        public async Task<int> DeleteMonthlyAttendance(int monthlyAttendanceId, LoginDTO loginDTO)
        {
            try
            {
                var parameters = new { MonthlyAttendanceId = monthlyAttendanceId };

                await _QueryExecutor.ExecuteAsync(loginDTO, MonthlyAttendanceQB.DELETE_MONTHLYATTENDANCEADDON_BY_HEADER, parameters);
                return await _QueryExecutor.ExecuteAsync(loginDTO, MonthlyAttendanceQB.DELETE_MONTHLYATTENDANCE, parameters);
            }
            catch (Exception ex)
            {
                throw new Exception("Error while deleting MonthlyAttendance", ex);
            }
        }

        public async Task<string> GetSelectListMonthlyAttendance(
            int firstNumber,
            int maxResult,
            CriteriaDTO criteriaDTO,
            LoginDTO loginDTO,
            bool isCount = false)
        {
            try
            {
                if (!isCount)
                {
                    var parameters = new { FirstNumber = firstNumber, MaxResult = maxResult };

                    List<MonthlyAttendancePicklistDTO> list =
                        (await _QueryExecutor.QueryAsync<MonthlyAttendancePicklistDTO>(loginDTO, MonthlyAttendanceQB.GET_SELECTLIST_MONTHLYATTENDANCE, parameters)).ToList();

                    return JsonConvert.SerializeObject(list);
                }
                else
                {
                    int count = await _QueryExecutor.ExecuteScalarAsync<int>(loginDTO, MonthlyAttendanceQB.GET_SELECTLIST_MONTHLYATTENDANCE_COUNT, null);
                    return count.ToString();
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"Error in GetSelectListMonthlyAttendance DAL: {ex.Message}", ex);
            }
        }

        public async Task<string> GetMonthlyAttendanceListAsync(
            int firstNumber,
            int maxResult,
            CriteriaDTO criteriaDTO,
            LoginDTO loginDTO,
            bool isCount = false)
        {
            try
            {
                (int payPeriodId, int employeeId) = ParseCriteria(criteriaDTO);

                if (!isCount)
                {
                    var parameters = new
                    {
                        FirstNumber = firstNumber,
                        MaxResult = maxResult,
                        PayPeriodId = payPeriodId,
                        EmployeeId = employeeId
                    };

                    List<MonthlyAttendanceDTO> list =
                        (await _QueryExecutor.QueryAsync<MonthlyAttendanceDTO>(loginDTO, MonthlyAttendanceQB.GET_MONTHLYATTENDANCE_LIST, parameters)).ToList();

                    return JsonConvert.SerializeObject(list);
                }
                else
                {
                    var parameters = new { PayPeriodId = payPeriodId, EmployeeId = employeeId };

                    int count = await _QueryExecutor.ExecuteScalarAsync<int>(loginDTO, MonthlyAttendanceQB.GET_MONTHLYATTENDANCE_LIST_COUNT, parameters);
                    return count.ToString();
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"Error in GetMonthlyAttendanceListAsync DAL: {ex.Message}", ex);
            }
        }

        // Natural filter dimensions for the list: PayPeriodId / EmployeeId, both optional (-1 = no filter).
        private (int PayPeriodId, int EmployeeId) ParseCriteria(CriteriaDTO criteriaDTO)
        {
            int payPeriodId = -1;
            int employeeId = -1;

            if (criteriaDTO?.SectionCriteriaList != null)
            {
                foreach (var section in criteriaDTO.SectionCriteriaList)
                {
                    if (section.AttributesCriteriaList == null)
                    {
                        continue;
                    }

                    foreach (var attr in section.AttributesCriteriaList)
                    {
                        if (string.Equals(attr.FieldName, "PayPeriodId", StringComparison.OrdinalIgnoreCase))
                        {
                            payPeriodId = ExtractIntValue(attr.FieldValue);
                        }
                        else if (string.Equals(attr.FieldName, "EmployeeId", StringComparison.OrdinalIgnoreCase))
                        {
                            employeeId = ExtractIntValue(attr.FieldValue);
                        }
                    }
                }
            }

            return (payPeriodId, employeeId);
        }

        private int ExtractIntValue(object value)
        {
            if (value == null)
            {
                return -1;
            }

            try
            {
                if (value is System.Text.Json.JsonElement jsonElement)
                {
                    switch (jsonElement.ValueKind)
                    {
                        case System.Text.Json.JsonValueKind.Number:
                            return jsonElement.GetInt32();
                        case System.Text.Json.JsonValueKind.String:
                            string strValue = jsonElement.GetString();
                            if (int.TryParse(strValue, out int intValue))
                            {
                                return intValue;
                            }
                            break;
                        default:
                            string rawText = jsonElement.GetRawText();
                            if (int.TryParse(rawText, out int parsedValue))
                            {
                                return parsedValue;
                            }
                            break;
                    }
                }
                else if (value is Newtonsoft.Json.Linq.JToken jToken)
                {
                    return jToken.ToObject<int>();
                }
                else
                {
                    return Convert.ToInt32(value);
                }
            }
            catch
            {
                return -1;
            }

            return -1;
        }
    }
}