using FrameworkDAL.DTO.GBPeriod;
using FrameworkDAL.Query.GBPeriod;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.PickListGenerator;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Framework;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace FrameworkDAL.CustomCode.GBPeriod
{
    public class GBPeriodDAL : IGBPeriodDAL
    {
        private readonly IQueryExecutor _queryExecutor;

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

        public async Task<string> GetGBPeriod(int gbPeriodId, LoginDTO loginDTO)
        {
            try
            {
                var sql = GBPeriodQB.GET_GBPERIOD; 
                var parameters = new { gbperiodid = gbPeriodId };

                var gbPeriodDict = new Dictionary<int, GBPeriodDTO>();

                var result = await _queryExecutor.QueryMultiMapAsync<GBPeriodDTO, GBPeriodDetailDTO, GBPeriodDTO>(
                    loginDTO,
                    sql,
                    (parent, child) =>
                    {
                        if (!gbPeriodDict.TryGetValue(parent.GBPeriodId, out var existingParent))
                        {
                            existingParent = parent;
                            existingParent.GBPeriodDetailArray = new List<GBPeriodDetailDTO>();
                            gbPeriodDict[parent.GBPeriodId] = existingParent;
                        }

                        if (child != null && child.GBPeriodDetailId != 0)
                        {
                            existingParent.GBPeriodDetailArray.Add(child);
                        }

                        return existingParent;
                    },
                    parameters,
                    splitOn: "GBPeriodDetailId" 
                );

                var finalResult = gbPeriodDict.Values.FirstOrDefault();
                return JsonConvert.SerializeObject(finalResult);
            }
            catch (Exception ex)
            {
                throw new Exception($"Failed to retrieve GBPeriod data: {ex.Message}", ex);
            }
        }
        /// <summary>
        /// Inserts a new GBPeriod record into the database.
        /// Automatically sets created and modified metadata fields.
        /// </summary>
        /// <param name="gbPeriodDTO">The GBPeriod data to insert.</param>
        /// <param name="loginDTO">The current user's login details.</param>
        /// <returns>The number of rows affected (should be 1 if successful).</returns>
        public async Task<int> SaveGBPeriod(GBPeriodDTO gbPeriodDTO, LoginDTO loginDTO)
        {
            try
            {
                string sql = GBPeriodQB.SAVE_GBPERIOD;
                return await _queryExecutor.ExecuteAsync(loginDTO, sql, gbPeriodDTO);
            }
            catch (Exception ex)
            {
                throw new Exception(FrameworkResource.SaveFailed, ex);
            }
        }

        /// <summary>
        /// Updates an existing GBPeriod record in the database.
        /// Automatically updates modified metadata fields.
        /// </summary>
        /// <param name="gbPeriodDTO">The GBPeriod data to update.</param>
        /// <param name="loginDTO">The current user's login details.</param>
        /// <returns>The number of rows affected (should be 1 if successful).</returns>
        public async Task<int> UpdateGBPeriod(GBPeriodDTO gbPeriodDTO, LoginDTO loginDTO)
        {
            try
            {
                string sql = GBPeriodQB.UPDATE_GBPERIOD;
                return await _queryExecutor.ExecuteAsync(loginDTO, sql, gbPeriodDTO);
            }
            catch (Exception ex)
            {
                throw new Exception(FrameworkResource.UpdateFailed, ex);
            }
        }

        /// <summary>
        /// Deletes a GBPeriod record by its unique identifier.
        /// </summary>
        /// <param name="gbPeriodId">The ID of the GBPeriod to delete.</param>
        /// <param name="loginDTO">The current user's login details.</param>
        /// <returns>
        /// A message string indicating success or failure of the delete operation.
        /// </returns>
        public async Task<string> DeleteGBPeriod(int gbPeriodId, LoginDTO loginDTO)
        {
            try
            {
                string sql = GBPeriodQB.DELETE_GBPERIOD;
                var parameters = new { gbperiodid = gbPeriodId };

                int result = await _queryExecutor.ExecuteAsync(loginDTO, sql, parameters);

                if (result > 0)
                {
                    return FrameworkResource.DeletedSuccessfully;
                }
                else
                {
                    return FrameworkResource.NotFoundToDelete;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(FrameworkResource.DeleteFailed, ex);
            }
        }
        /// <summary>
        /// Retrieves a paginated list of GBPeriod records based on specified filter criteria.
        /// Can optionally return the total count of records instead of the list.
        /// </summary>
        /// <param name="firstNumber">The starting index of records to fetch (for pagination).</param>
        /// <param name="maxResult">The maximum number of records to fetch.</param>
        /// <param name="criteriaDTO">Filter criteria for selecting GBPeriod records.</param>
        /// <param name="loginDTO">The current user's login details.</param>
        /// <param name="isCount">If true, returns the total count of matching records instead of the list.</param>
        /// <returns>A JSON string representing either the list of GBPeriod records or the total count.</returns>
        public async Task<string> GetSelectListGBPeriod(int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO, bool isCount = false)
        {
            try
            {
                string json = "";
                if (!isCount)
                {
                    string sql = GBPeriodQB.GET_SELECTLIST_GBPERIOD; 
                    var parameters = new { firstnumber = firstNumber, maxresult = maxResult };
                    List<GBPeriodPicklistDTO> list = (await _queryExecutor.QueryAsync<GBPeriodPicklistDTO>(loginDTO, sql, parameters)).ToList();
                    json = JsonConvert.SerializeObject(list);
                }
                else
                {
                    int count = 0;
                    json = count.ToString();
                }
                return json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// Retrieves a paginated list of GBPeriodDetail records (sub-periods) across all GBPeriods.
        /// Can optionally return the total count of records instead of the list.
        /// </summary>
        /// <param name="firstNumber">The starting index of records to fetch (for pagination).</param>
        /// <param name="maxResult">The maximum number of records to fetch.</param>
        /// <param name="criteriaDTO">Filter criteria for selecting GBPeriodDetail records.</param>
        /// <param name="loginDTO">The current user's login details.</param>
        /// <param name="isCount">If true, returns the total count of matching records instead of the list.</param>
        /// <returns>A JSON string representing either the list of GBPeriodDetail records or the total count.</returns>
        public async Task<string> GetSelectListGBPeriodDetail(int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO, bool isCount = false)
        {
            try
            {
                string json = "";
                if (!isCount)
                {
                    string sql = GBPeriodQB.GET_SELECTLIST_GBPERIODDETAIL;
                    var parameters = new { firstnumber = firstNumber, maxresult = maxResult };
                    List<GBPeriodDetailPicklistDTO> list = (await _queryExecutor.QueryAsync<GBPeriodDetailPicklistDTO>(loginDTO, sql, parameters)).ToList();
                    json = JsonConvert.SerializeObject(list);
                }
                else
                {
                    int count = 0;
                    json = count.ToString();
                }
                return json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// Retrieves the full GBPeriod/GBPeriodDetail picklist (no paging) filtered by the supplied criteria.
        /// Mirrors the GB4 Gb4PeriodPicklist behavior used by legacy period-selection pickers.
        /// </summary>
        /// <param name="criteriaDTO">Filter criteria (e.g. PeriodType, SubPeriodName, PeiodFromDate, PeiodToDate).</param>
        /// <param name="loginDTO">The current user's login details.</param>
        /// <returns>A JSON string representing the list of matching GBPeriodDetail picklist records.</returns>
        public async Task<string> Gb4PeriodPicklist(CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            try
            {
                string sql = GBPeriodQB.GET_GB4PERIOD_PICKLIST;
                sql = await ApplyGb4PeriodCriteria(sql, criteriaDTO, loginDTO);
                sql = await ApplyGb4PeriodCriteria(sql, loginDTO.UserCriteriaDTO, loginDTO);

                List<GBPeriodDetailPicklistDTO> list = (await _queryExecutor.QueryAsync<GBPeriodDetailPicklistDTO>(loginDTO, sql)).ToList();
                return JsonConvert.SerializeObject(list);
            }
            catch (Exception)
            {
                throw;
            }
        }

        private static async Task<string> ApplyGb4PeriodCriteria(string sql, CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            IPickListExecutor pickListExecutor = new PickListExecutor();
            string[,] criteriaWithQueryField = new string[,]
            {
                { "PeriodType",     "MG.PERIODTYPE" },
                { "SubPeriodName",  "MD.SUBPERIODNAME" },
                { "PeiodFromDate",  "MD.SUBPERIODFROMDATE" },
                { "PeiodToDate",    "MD.SUBPERIODTODATE" }
            };

            return await pickListExecutor.ApplyCriteria(sql, criteriaWithQueryField, criteriaDTO, loginDTO);
        }
    }
}