using AccountsDAL.DTO.PartyBranch;
using AccountsDAL.Query.PartyBranch;
using Dapper;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.ResponseStandard;
using Newtonsoft.Json;
using System.Data.Common;

namespace AccountsDAL.CustomCode.PartyBranch
{
    public class PartyBranchDAL : IPartyBranchDAL
    {
        private readonly IQueryExecutor _queryExecutor;

        // MADDONFIELDS.ENTITYID for the PartyBranch entity. Reused from the value already live in
        // PartyDetailDAL.GetPartyDetailsForInventory (-1398358517) rather than the alternate
        // -1399999839 noted in a comment there — that call is the only other place in the codebase
        // that queries this entity's addon fields today, so this stays consistent with it. Worth
        // confirming against MENTITY via schema introspection before relying on this in a new client DB.
        private const int PartyBranchAddonEntityId = -1398358517;

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

        public async Task<PartyBranchDTO?> GetPartyBranch(int partyBranchId, LoginDTO loginDTO)
        {
            return await _queryExecutor
                .QuerySingleAsync<PartyBranchDTO>(loginDTO, PartyBranchQB.GET_PARTY_BRANCH, new { PartyBranchId = partyBranchId })
                .ConfigureAwait(false);
        }

        // Matches GB4's conditional TDS/Account enrichment — null means either the owning Party is
        // itself flagged as an account (ISACCOUNT <> 0, gated in the query's own WHERE clause) or no
        // linked MACCOUNT row exists; the caller (BLL) leaves the DTO's own fallback defaults in place.
        public async Task<PartyBranchDTO?> GetPartyBranchAccountTDS(int partyId, LoginDTO loginDTO)
        {
            return await _queryExecutor
                .QuerySingleAsync<PartyBranchDTO>(loginDTO, PartyBranchQB.GET_PARTY_BRANCH_ACCOUNT_TDS, new { PartyId = partyId })
                .ConfigureAwait(false);
        }

        public async Task<List<PartyBranchDTO>> GetPartyBranchesOnParty(int partyId, LoginDTO loginDTO)
        {
            var list = await _queryExecutor
                .QueryAsync<PartyBranchDTO>(loginDTO, PartyBranchQB.GET_PARTY_BRANCHES_ON_PARTY, new { PartyId = partyId })
                .ConfigureAwait(false);
            return list.ToList();
        }

        // Matches GB4's exact TodayFrom/TodayTo computation (UTC 00:00:00.000 / 23:59:59.000) —
        // only PartyBranchInfo rows whose effective window fully spans today are returned.
        public async Task<string> GetSiteDetails(int partyBranchId, LoginDTO loginDTO)
        {
            var today = DateTime.UtcNow.Date;
            var todayFrom = today;
            var todayTo = today.AddDays(1).AddSeconds(-1);

            var list = await _queryExecutor
                .QueryAsync<PartyBranchSiteDetailDTO>(loginDTO, PartyBranchQB.GET_PARTY_BRANCH_SITE_DETAILS,
                    new { PartyBranchId = partyBranchId, TodayFrom = todayFrom, TodayTo = todayTo, TenantId = loginDTO.ClientId })
                .ConfigureAwait(false);

            return JsonConvert.SerializeObject(list);
        }

        public async Task<string> GetSelectListPartyBranch(int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            int partyId = ReadIntCriteria(criteriaDTO, "partyid");
            string? searchText = ExtractLikeSearchText(criteriaDTO);
            var list = await _queryExecutor
                .QueryAsync<object>(loginDTO, PartyBranchQB.GET_SELECTLIST_PARTY_BRANCH,
                    new { FirstNumber = Math.Max(firstNumber, 0), MaxResult = maxResult, PartyId = partyId, SearchText = searchText })
                .ConfigureAwait(false);
            return JsonConvert.SerializeObject(list);
        }

        // Full-DTO party branch list (PartyBranchDTO, not PartyBranchListDTO — that's an unrelated
        // pre-existing wide report DTO) — same column set/joins as GET_PARTY_BRANCH but for every
        // matching row instead of one, criteria-filtered like GetSelectListPartyBranch above rather
        // than paged, mirroring AccountDAL.GetAccountList's shape — no FirstNumber/MaxResult.
        public async Task<string> GetPartyBranchList(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                var sql = PartyBranchQB.GET_PARTYBRANCH_LIST;
                var searchText = ExtractLikeSearchText(CriteriaDTO);
                var (statusEquals, statusNotEquals) = ExtractStatusFilter(CriteriaDTO);
                var parameter = new
                {
                    SearchText = searchText,
                    StatusEquals = statusEquals,
                    StatusNotEquals = statusNotEquals
                };
                var list = (await _queryExecutor.QueryAsync<PartyBranchDTO>(LoginDTO, sql, parameter)
                                                .ConfigureAwait(false)).ToList();
                return JsonConvert.SerializeObject(list);
            }
            catch (Exception)
            {
                throw;
            }
        }

        // Picklist search-as-you-type sends the typed text as a Like-operation attribute on each
        // searchable field (Code/Name) with the same FieldValue repeated per field — take the first
        // non-empty one found. Mirrors PartyDAL.ExtractLikeSearchText.
        private static string? ExtractLikeSearchText(CriteriaDTO criteriaDTO)
        {
            if (criteriaDTO?.SectionCriteriaList == null) return null;
            foreach (var section in criteriaDTO.SectionCriteriaList)
            {
                if (section?.AttributesCriteriaList == null) continue;
                foreach (var attr in section.AttributesCriteriaList)
                {
                    if (attr?.OperationType == CriteriaDTO.OperationType.Like && attr.FieldValue != null)
                    {
                        var text = attr.FieldValue.ToString();
                        if (!string.IsNullOrWhiteSpace(text)) return text;
                    }
                }
            }
            return null;
        }

        // Picklist also sends a Status attribute (e.g. NotEqual 5 = exclude Archived) alongside the
        // search text — extract it as (Equals, NotEquals) so both directions are honored; whichever
        // one wasn't sent stays null and its corresponding SQL condition is a no-op. Mirrors
        // AccountDAL.ExtractStatusFilter.
        private static (int? EqualTo, int? NotEqualTo) ExtractStatusFilter(CriteriaDTO criteriaDTO)
        {
            if (criteriaDTO?.SectionCriteriaList == null) return (null, null);
            foreach (var section in criteriaDTO.SectionCriteriaList)
            {
                if (section?.AttributesCriteriaList == null) continue;
                foreach (var attr in section.AttributesCriteriaList)
                {
                    if (attr == null || !string.Equals(attr.FieldName, "Status", StringComparison.OrdinalIgnoreCase))
                        continue;
                    if (attr.FieldValue == null || !int.TryParse(attr.FieldValue.ToString(), out int value))
                        continue;

                    if (attr.OperationType == CriteriaDTO.OperationType.Equal)
                        return (value, null);
                    if (attr.OperationType == CriteriaDTO.OperationType.NotEqual)
                        return (null, value);
                }
            }
            return (null, null);
        }

        public async Task<Result<string>> SavePartyBranch(PartyBranchDTO dto, LoginDTO loginDTO, DbTransaction tx)
        {
            dto.PartyBranchCreatedById = loginDTO.UserId;
            dto.PartyBranchCreatedOn = DateTime.UtcNow;
            dto.PartyBranchModifiedById = loginDTO.UserId;
            dto.PartyBranchModifiedOn = DateTime.UtcNow;

            int rows = await _queryExecutor.ExecuteAsync(loginDTO, PartyBranchQB.SAVE_PARTY_BRANCH, dto, tx)
                .ConfigureAwait(false);
            return rows > 0
                ? Result<string>.Success("Party branch saved successfully.")
                : Result<string>.Failure("Failed to save party branch.");
        }

        public async Task<Result<string>> UpdatePartyBranch(PartyBranchDTO dto, LoginDTO loginDTO, DbTransaction tx)
        {
            dto.PartyBranchModifiedById = loginDTO.UserId;
            dto.PartyBranchModifiedOn = DateTime.UtcNow;

            int rows = await _queryExecutor.ExecuteAsync(loginDTO, PartyBranchQB.UPDATE_PARTY_BRANCH, dto, tx)
                .ConfigureAwait(false);
            return rows > 0
                ? Result<string>.Success("Party branch updated successfully.")
                : Result<string>.Failure("Failed to update party branch.");
        }

        public async Task<Result<string>> DeletePartyBranch(int partyBranchId, LoginDTO loginDTO, DbTransaction tx)
        {
            var param = new { PartyBranchId = partyBranchId };

            await _queryExecutor.ExecuteAsync(loginDTO, PartyBranchQB.DELETE_PARTY_BRANCH_ACCOUNT_OU, param, tx)
                .ConfigureAwait(false);

            int rows = await _queryExecutor.ExecuteAsync(loginDTO, PartyBranchQB.DELETE_PARTY_BRANCH, param, tx)
                .ConfigureAwait(false);

            return rows > 0
                ? Result<string>.Success("Party branch deleted successfully.")
                : Result<string>.Failure("Party branch not found.");
        }

        // Configurable (MADDONFIELDS-driven) addon block for one PartyBranch. Only fields registered
        // for PartyBranchAddonEntityId are ever selected — MPARTYBRANCHADDON's own bookkeeping
        // columns (PARTYBRANCHID, audit columns, etc.) never leak into the FeAddon JSON blob.
        public async Task<List<PartyBranchAddonDTO>> GetPartyBranchAddon(int partyBranchId, LoginDTO loginDTO)
        {
            var fields = (await _queryExecutor
                .QueryAsync<dynamic>(loginDTO, PartyBranchQB.GET_PARTYBRANCH_ADDON_FIELDS, new { EntityId = PartyBranchAddonEntityId })
                .ConfigureAwait(false))
                .Select(f => (string)f.DBObjectFieldsName)
                .Where(f => !string.IsNullOrWhiteSpace(f))
                .ToList();

            if (fields.Count == 0)
                return new List<PartyBranchAddonDTO>();

            var sql = string.Format(PartyBranchQB.GET_PARTYBRANCH_ADDON, string.Join(", ", fields));
            var addonRow = await _queryExecutor
                .QuerySingleAsync<dynamic>(loginDTO, sql, new { PartyBranchId = partyBranchId })
                .ConfigureAwait(false);

            if (addonRow == null)
                return new List<PartyBranchAddonDTO>();

            var addonJson = JsonConvert.SerializeObject(addonRow);
            return new List<PartyBranchAddonDTO>
            {
                new PartyBranchAddonDTO { Id = partyBranchId, FeAddon = addonJson, GetAddon = addonJson }
            };
        }

        // Dynamic upsert: only JSON keys in feAddonJson that match a field registered in MADDONFIELDS
        // for PartyBranchAddonEntityId are written — an unregistered key is silently ignored rather
        // than rejected, matching how the read side only ever surfaces registered fields. Column
        // names come exclusively from MADDONFIELDS (trusted metadata, not raw client input) before
        // being interpolated into SQL text — same trust boundary already relied on by the read side
        // (PartyBranchQB.GET_PARTYBRANCH_ADDON's {0} template); only bound VALUES come from the client.
        public async Task SavePartyBranchAddon(int partyBranchId, string? feAddonJson, LoginDTO loginDTO, DbTransaction tx)
        {
            if (string.IsNullOrWhiteSpace(feAddonJson))
                return;

            var fields = (await _queryExecutor
                .QueryAsync<dynamic>(loginDTO, PartyBranchQB.GET_PARTYBRANCH_ADDON_FIELDS, new { EntityId = PartyBranchAddonEntityId }, tx)
                .ConfigureAwait(false))
                .Select(f => (string)f.DBObjectFieldsName)
                .Where(f => !string.IsNullOrWhiteSpace(f))
                .ToList();

            if (fields.Count == 0)
                return;

            var values = JsonConvert.DeserializeObject<Dictionary<string, object>>(feAddonJson) ?? new Dictionary<string, object>();

            var matched = new List<(string Field, object? Value)>();
            foreach (var field in fields)
            {
                var key = values.Keys.FirstOrDefault(k => string.Equals(k, field, StringComparison.OrdinalIgnoreCase));
                if (key != null)
                    matched.Add((field, values[key]));
            }

            if (matched.Count == 0)
                return;

            var parameters = new DynamicParameters();
            parameters.Add("PartyBranchId", partyBranchId);
            foreach (var (field, value) in matched)
                parameters.Add(field, value?.ToString());

            bool exists = await _queryExecutor
                .ExecuteScalarAsync<int>(loginDTO, PartyBranchQB.PARTYBRANCH_ADDON_EXISTS, new { PartyBranchId = partyBranchId }, tx)
                .ConfigureAwait(false) > 0;

            string sql = exists
                ? $"UPDATE MPARTYBRANCHADDON SET {string.Join(", ", matched.Select(m => $"{m.Field} = @{m.Field}"))} WHERE PARTYBRANCHID = @PartyBranchId"
                : $"INSERT INTO MPARTYBRANCHADDON (PARTYBRANCHID, {string.Join(", ", matched.Select(m => m.Field))}) VALUES (@PartyBranchId, {string.Join(", ", matched.Select(m => "@" + m.Field))})";

            await _queryExecutor.ExecuteAsync(loginDTO, sql, parameters, tx).ConfigureAwait(false);
        }

        private static int ReadIntCriteria(CriteriaDTO criteriaDTO, string fieldName)
        {
            if (criteriaDTO?.SectionCriteriaList == null) return 0;

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

                foreach (var attr in section.AttributesCriteriaList)
                {
                    if (!string.Equals(attr.FieldName, fieldName, StringComparison.OrdinalIgnoreCase)) continue;
                    return int.TryParse(attr.FieldValue?.ToString(), out var value) ? value : 0;
                }
            }

            return 0;
        }
    }
}