using System.Data.Common;
using Dapper;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using Newtonsoft.Json;
using PayRollDAL.DTO.Policy;
using PayRollDAL.Query.Policy;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text.Json;
using System.Threading.Tasks;
using static GB5Shared.GB5Constant.Constant;

namespace PayRollDAL.CustomeCode.Policy
{
    public class PolicyDAL : IPolicyDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

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

        // ── Get header + all version rows (each with its own details) ────

        public async Task<PolicyDTO> GetPolicy(int PolicyId, LoginDTO LoginDTO)
        {
            var param = new { policyid = PolicyId };

            var header = await _QueryExecutor.QuerySingleAsync<PolicyDTO>(
                LoginDTO, PolicyQB.GET_POLICY, param).ConfigureAwait(false);

            if (header != null)
            {
                var versions = await _QueryExecutor.QueryAsync<PolicyVersionDTO>(
                    LoginDTO, PolicyQB.GET_POLICYVERSION_BY_POLICYID, param).ConfigureAwait(false);

                var versionList = versions?.ToList() ?? new List<PolicyVersionDTO>();

                foreach (var version in versionList)
                {
                    var details = await _QueryExecutor.QueryAsync<PolicyVersionDetailDTO>(
                        LoginDTO, PolicyQB.GET_POLICYVERSIONDETAIL,
                        new { policyversionid = version.PolicyVersionId }).ConfigureAwait(false);

                    version.PolicyVersionDetailArray = details?.ToList() ?? new List<PolicyVersionDetailDTO>();
                }

                header.PolicyVersionArray = versionList;
            }

            return header!;
        }

        // ── Insert header + version + detail rows (transaction provided by BLL) ─

        public async Task<int> SavePolicy(PolicyDTO PolicyDTO, LoginDTO LoginDTO, DbTransaction DbTransaction)
        {
            int rows = await _QueryExecutor.ExecuteAsync(
                LoginDTO, PolicyQB.SAVE_POLICY, PolicyDTO, DbTransaction).ConfigureAwait(false);

            if (rows > 0 && PolicyDTO.PolicyVersionArray?.Count > 0)
            {
                foreach (var version in PolicyDTO.PolicyVersionArray)
                {
                    version.PolicyId = PolicyDTO.PolicyId;
                    await _QueryExecutor.ExecuteAsync(
                        LoginDTO, PolicyQB.SAVE_POLICYVERSION, version, DbTransaction).ConfigureAwait(false);

                    if (version.PolicyVersionDetailArray?.Count > 0)
                    {
                        short slNo = 1;
                        foreach (var detail in version.PolicyVersionDetailArray)
                        {
                            detail.PolicyVersionParentId = version.PolicyVersionId;
                            detail.PolicyVersionDetailSlNo = slNo++;
                            await _QueryExecutor.ExecuteAsync(
                                LoginDTO, PolicyQB.SAVE_POLICYVERSIONDETAIL, detail, DbTransaction).ConfigureAwait(false);
                        }
                    }
                }
            }

            return rows;
        }

        // ── Update header + replace version/detail rows (transaction provided by BLL) ─

        public async Task<int> UpdatePolicy(PolicyDTO PolicyDTO, LoginDTO LoginDTO, DbTransaction DbTransaction)
        {
            int rows = await _QueryExecutor.ExecuteAsync(
                LoginDTO, PolicyQB.UPDATE_POLICY, PolicyDTO, DbTransaction).ConfigureAwait(false);

            if (PolicyDTO.IsUpdate == 1)
            {
                // Version change — append a new version row, keep existing versions intact
                if (PolicyDTO.PolicyVersionArray?.Count > 0)
                {
                    foreach (var version in PolicyDTO.PolicyVersionArray.Where(v => v.PolicyVersionId > 0))
                    {
                        version.PolicyId = PolicyDTO.PolicyId;
                        await _QueryExecutor.ExecuteAsync(
                            LoginDTO, PolicyQB.SAVE_POLICYVERSION, version, DbTransaction).ConfigureAwait(false);

                        if (version.PolicyVersionDetailArray?.Count > 0)
                        {
                            short slNo = 1;
                            foreach (var detail in version.PolicyVersionDetailArray)
                            {
                                detail.PolicyVersionParentId = version.PolicyVersionId;
                                detail.PolicyVersionDetailSlNo = slNo++;
                                await _QueryExecutor.ExecuteAsync(
                                    LoginDTO, PolicyQB.SAVE_POLICYVERSIONDETAIL, detail, DbTransaction).ConfigureAwait(false);
                            }
                        }
                    }
                }
            }
            else
            {
                // Plain update — replace all version/detail rows for this policy
                await _QueryExecutor.ExecuteAsync(
                    LoginDTO, PolicyQB.DELETE_POLICYVERSION_BY_POLICYID,
                    new { policyid = PolicyDTO.PolicyId }, DbTransaction).ConfigureAwait(false);

                if (PolicyDTO.PolicyVersionArray?.Count > 0)
                {
                    foreach (var version in PolicyDTO.PolicyVersionArray)
                    {
                        version.PolicyId = PolicyDTO.PolicyId;
                        await _QueryExecutor.ExecuteAsync(
                            LoginDTO, PolicyQB.SAVE_POLICYVERSION, version, DbTransaction).ConfigureAwait(false);

                        if (version.PolicyVersionDetailArray?.Count > 0)
                        {
                            short slNo = 1;
                            foreach (var detail in version.PolicyVersionDetailArray)
                            {
                                detail.PolicyVersionParentId = version.PolicyVersionId;
                                detail.PolicyVersionDetailSlNo = slNo++;
                                await _QueryExecutor.ExecuteAsync(
                                    LoginDTO, PolicyQB.SAVE_POLICYVERSIONDETAIL, detail, DbTransaction).ConfigureAwait(false);
                            }
                        }
                    }
                }
            }

            return rows;
        }

        // ── Delete header (versions/details removed explicitly) ─

        public async Task<string> DeletePolicy(int PolicyId, LoginDTO LoginDTO)
        {
            await _QueryExecutor.ExecuteAsync(
                LoginDTO, PolicyQB.DELETE_POLICYVERSION_BY_POLICYID,
                new { policyid = PolicyId }).ConfigureAwait(false);

            int result = await _QueryExecutor.ExecuteAsync(
                LoginDTO, PolicyQB.DELETE_POLICY,
                new { policyid = PolicyId }).ConfigureAwait(false);

            return result > 0 ? SuccessResponse.DeleteSuccessMessage : ErrorResponse.DeleteNotFoundMessage;
        }

        // ── Picklist for grid ─────────────────────────────────────────────

        public async Task<string> GetSelectListPolicy(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            string sql = PolicyQB.GET_SELECTLIST_POLICY;
            var parameters = new DynamicParameters();

            string searchText = GetCriteriaStringValue(CriteriaDTO, "SearchText");
            if (!string.IsNullOrWhiteSpace(searchText))
            {
                sql += "\r\n AND (A.POLICYCODE LIKE @searchtext OR A.POLICYNAME LIKE @searchtext)";
                parameters.Add("searchtext", $"%{searchText}%");
            }

            sql += "\r\n ORDER BY A.POLICYNAME";

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

        // ── Get a single policy version by id ────────────────────────────

        public async Task<PolicyVersionDTO> GetPolicyVersion(int PolicyVersionId, LoginDTO LoginDTO)
        {
            var param = new { policyversionid = PolicyVersionId };

            var version = await _QueryExecutor.QuerySingleAsync<PolicyVersionDTO>(
                LoginDTO, PolicyQB.GET_POLICYVERSION_BY_ID, param).ConfigureAwait(false);

            if (version != null)
            {
                var details = await _QueryExecutor.QueryAsync<PolicyVersionDetailDTO>(
                    LoginDTO, PolicyQB.GET_POLICYVERSIONDETAIL, param).ConfigureAwait(false);

                version.PolicyVersionDetailArray = details?.ToList() ?? new List<PolicyVersionDetailDTO>();
            }

            return version!;
        }

        // ── Get the policy version effective as of FromDate ──────────────

        public async Task<PolicyVersionDTO> GetPolicyVersionByFromDate(int PolicyVersionId, DateTime FromDate, LoginDTO LoginDTO)
        {
            var param = new { policyversionid = PolicyVersionId, fromdate = FromDate };

            var version = await _QueryExecutor.QuerySingleAsync<PolicyVersionDTO>(
                LoginDTO, PolicyQB.GET_POLICYVERSION_BY_FROMDATE, param).ConfigureAwait(false);

            if (version != null)
            {
                var details = await _QueryExecutor.QueryAsync<PolicyVersionDetailDTO>(
                    LoginDTO, PolicyQB.GET_POLICYVERSIONDETAIL,
                    new { policyversionid = version.PolicyVersionId }).ConfigureAwait(false);

                version.PolicyVersionDetailArray = details?.ToList() ?? new List<PolicyVersionDetailDTO>();
            }

            return version!;
        }

        public async Task<string> GetSelectListPolicyVersion(int PolicyId, LoginDTO LoginDTO)
        {
            var result = await _QueryExecutor.QueryAsync<PolicyVersionPicklistDTO>(
                LoginDTO, PolicyQB.GET_SELECTLIST_POLICYVERSION,
                new { policyid = PolicyId }).ConfigureAwait(false);

            return JsonConvert.SerializeObject(result);
        }

        // ── Policy Version Picklist Report (cross-policy) ────────────────

        public async Task<string> GetPolicyVersionPicklistReport(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool IsCount = false)
        {
            try
            {
                string policyIdFilter = GetCriteriaStringValue(CriteriaDTO, "PolicyId");
                string versionFilter = GetCriteriaStringValue(CriteriaDTO, "Version");

                var parameters = new DynamicParameters();
                string filterClause = "";

                if (!string.IsNullOrWhiteSpace(policyIdFilter))
                {
                    filterClause += " AND CAST(a.POLICYID AS VARCHAR(50)) LIKE @policyid";
                    parameters.Add("policyid", $"%{policyIdFilter}%");
                }

                if (!string.IsNullOrWhiteSpace(versionFilter))
                {
                    filterClause += " AND CAST(b.VERSION AS VARCHAR(50)) LIKE @version";
                    parameters.Add("version", $"%{versionFilter}%");
                }

                if (IsCount)
                {
                    string countSql = LoginDTO.DatabaseType switch
                    {
                        DBType.SQL => PolicyQB.GET_POLICYVERSION_PICKLIST_REPORT_COUNT_SQL,
                        DBType.PostGre => PolicyQB.GET_POLICYVERSION_PICKLIST_REPORT_COUNT_PG,
                        _ => throw new Exception("Unsupported database type")
                    };
                    countSql = countSql.Replace("/*FILTER*/", filterClause);

                    int count = await _QueryExecutor.ExecuteScalarAsync<int>(LoginDTO, countSql, parameters)
                        .ConfigureAwait(false);
                    return count.ToString();
                }
                else
                {
                    string sql = LoginDTO.DatabaseType switch
                    {
                        DBType.SQL => PolicyQB.GET_POLICYVERSION_PICKLIST_REPORT_SQL,
                        DBType.PostGre => PolicyQB.GET_POLICYVERSION_PICKLIST_REPORT_PG,
                        _ => throw new Exception("Unsupported database type")
                    };
                    sql = sql.Replace("/*FILTER*/", filterClause);

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

                    var result = await _QueryExecutor.QueryAsync<PolicyVersionReportPicklistDTO>(LoginDTO, sql, parameters)
                        .ConfigureAwait(false);
                    return JsonConvert.SerializeObject(result);
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Error in GetPolicyVersionPicklistReport", ex);
            }
        }

        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 JsonElement je)
                    return je.ValueKind == JsonValueKind.String ? je.GetString() : je.ToString();

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