using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.PickListGenerator;
using GB5Shared.QueryExecutor;
using Newtonsoft.Json;
using PayRollDAL.DTO.TDSSection;
using PayRollDAL.Query.TDSSection;
using static GB5Shared.GB5Constant.Constant;

namespace PayRollDAL.CustomeCode.TDSSection
{
    public class TDSSectionDAL : ITDSSectionDAL
    {
        private readonly IQueryExecutor queryExecutor;
        private readonly IPickListExecutor pickListExecutor;

        public TDSSectionDAL(IQueryExecutor queryExecutor, IPickListExecutor pickListExecutor)
        {
            this.queryExecutor = queryExecutor;
            this.pickListExecutor = pickListExecutor;
        }

        public async Task<List<TDSSectionDetailDTO>> GetSelectListTDSSectionDetail(CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            var transaction = await queryExecutor.BeginTransactionAsync(loginDTO);
            try
            {
                string sql;

                // Choose query based on database type
                if (loginDTO.DatabaseType == DBTYPE.SQL)
                    sql = TDSSectionQB.GET_SELECTLISTTDSSECTIONDETAIL_SQL;
                else if (loginDTO.DatabaseType == DBTYPE.POSTGRESQL)
                    sql = TDSSectionQB.GET_SELECTLISTTDSSECTIONDETAIL_PG;
                else
                    throw new NotSupportedException("Database type not supported.");

                // Apply criteria
                sql = await ApplySelectListTDSSectionDetail(sql, criteriaDTO, loginDTO);
                sql = await ApplySelectListTDSSectionDetail(sql, loginDTO.UserCriteriaDTO, loginDTO);

                // Execute query
                var result = (await queryExecutor.QueryAsync<TDSSectionDetailDTO>(loginDTO, sql, null!, transaction)).ToList();

                await queryExecutor.CommitAsync(transaction);
                return result;
            }
            catch
            {
                await queryExecutor.RollbackAsync(transaction);
                throw;
            }
        }

        public async Task<string> ApplySelectListTDSSectionDetail(string sql, CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            if (criteriaDTO == null) return sql;

            string[,] criteriaMap = new string[,]
            {
                { "SectionDetail", "b.SECTIONDETAIL" },
                { "SectionParticulars", "b.PARTICULARS" },
                { "TDSSectionId", "b.TDSSECTIONID" },
                { "PeriodId", "c.PeriodId" },
                { "TDSSection", "a.TDSSECTION" }
            };

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

        // ── Picklist for grid ─────────────────────────────────────────────
        // GB4 parity: TDSSectionBLL.GetSelectListTDSSection

        public async Task<string> GetSelectListTDSSection(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool IsCount = false)
        {
            string searchText = GetCriteriaStringValue(CriteriaDTO, "SearchText");
            var parameters = new DynamicParameters();
            parameters.Add("searchtext", string.IsNullOrWhiteSpace(searchText) ? null : $"%{searchText}%");

            if (IsCount)
            {
                string countSql = LoginDTO.DatabaseType switch
                {
                    DBTYPE.SQL => TDSSectionQB.GET_SELECTLIST_TDSSECTION_COUNT_SQL,
                    DBTYPE.POSTGRESQL => TDSSectionQB.GET_SELECTLIST_TDSSECTION_COUNT_PG,
                    _ => throw new Exception("Unsupported database type")
                };

                var countRows = await queryExecutor.QueryAsync<int>(LoginDTO, countSql, parameters).ConfigureAwait(false);
                return countRows.FirstOrDefault().ToString();
            }

            string sql = LoginDTO.DatabaseType switch
            {
                DBTYPE.SQL => TDSSectionQB.GET_SELECTLIST_TDSSECTION_SQL,
                DBTYPE.POSTGRESQL => TDSSectionQB.GET_SELECTLIST_TDSSECTION_PG,
                _ => throw new Exception("Unsupported database type")
            };

            parameters.Add("firstnumber", FirstNumber);
            parameters.Add("maxresult", MaxResult);

            var result = await queryExecutor.QueryAsync<TDSSectionPicklistDTO>(LoginDTO, sql, parameters).ConfigureAwait(false);
            return JsonConvert.SerializeObject(result);
        }

        private string GetCriteriaStringValue(CriteriaDTO criteriaDTO, string fieldName)
        {
            try
            {
                var attr = criteriaDTO?.SectionCriteriaList?
                    .SelectMany(s => s.AttributesCriteriaList)
                    .FirstOrDefault(a => string.Equals(a.FieldName, fieldName, StringComparison.OrdinalIgnoreCase));

                if (attr?.FieldValue is System.Text.Json.JsonElement je)
                    return je.ValueKind == System.Text.Json.JsonValueKind.String ? je.GetString() : je.ToString();

                return attr?.FieldValue?.ToString();
            }
            catch { return null; }
        }
    }
}