using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using AccountsDAL.DTO.PartyDetails;
using AccountsDAL.Query.PartyDetail;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.GB5Exception;
using GB5Shared.QueryExecutor;
using Newtonsoft.Json;

namespace AccountsDAL.CustomCode.PartyDetail
{
    public class PartyDetailDAL : IPartyDetailDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

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

        //public async Task<List<PartyDetailsDTO>> GetPartyDetailsForInventory(
        //    CriteriaDTO criteriaDTO,
        //    LoginDTO loginDTO,
        //    CancellationToken ct)
        //{
        //    try
        //    {
        //        int partyBranchId = GetCriteriaValue(criteriaDTO, "PartyBranchId") ?? -1;
        //        int partyId = GetCriteriaValue(criteriaDTO, "PartyId") ?? -1;

        //        if (partyBranchId <= 0 && partyId > 0)
        //        {
        //            partyBranchId = await GetPartyBranchIdByPartyId(partyId, loginDTO, ct);
        //        }

        //        if (partyBranchId <= 0)
        //        {
        //            partyBranchId = -1;
        //        }

        //        var parameters = new { PartyBranchId = partyBranchId, WorkOUId = loginDTO.WorkOUId };
        //        var partyDetails = (await _QueryExecutor.QueryAsync<PartyDetailsDTO>(
        //            loginDTO,
        //            PartyDetailQB.GET_PARTY_DETAILS_INVENTORY,
        //            parameters)).ToList();

        //        if (!partyDetails.Any())
        //        {
        //            throw new NotFoundException("Check the PartyOU Master - details not found.");
        //        }

        //        var detail = partyDetails[0];

        //        var taxTypes = (await _QueryExecutor.QueryAsync<dynamic>(
        //            loginDTO,
        //            PartyDetailQB.GET_PARTY_TAXTYPEDETAILS,
        //            new { TaxTypeId = detail.TaxTypeId })).ToList();

        //        if (taxTypes.Any())
        //        {
        //            var tt = taxTypes[0];
        //            detail.TransactionTypeId = tt.TRANSACTIONTYPEID ?? detail.TransactionTypeId;
        //            detail.TransactionTypeName = tt.TRANSACTIONTYPEID?.ToString();
        //            detail.DutyTransactionTypeId = tt.DUTYTRANSACTIONTYPEID ?? detail.DutyTransactionTypeId;
        //            detail.DutyTransactionTypeName = tt.DUTYTRANSACTIONTYPEID?.ToString();
        //        }

        //        var gst = await _QueryExecutor.QuerySingleAsync<dynamic>(
        //            loginDTO,
        //            PartyDetailQB.GET_GST_NUMBER,
        //            new { PartyBranchId = detail.PartyBranchId });

        //        detail.GSTNumber = gst?.GSTIN;

        //        var commentResult = (await _QueryExecutor.QueryAsync<dynamic>(
        //            loginDTO,
        //            "select COMMENTTEXT as CommentCommentText from TCOMMENT where COMMENTSTATUS=0 and SECURITYLEVEL=0 and OBJECTTYPEID=-1399392247 and OBJECTID=" + detail.PartyBranchPartyId)).ToList();

        //        if (commentResult.Any())
        //        {
        //            detail.PartyComment = string.Join(" <br> ", commentResult.Select(c => (string)(c.CommentCommentText ?? "")));
        //        }

        //        var ouCurrency = await _QueryExecutor.QuerySingleAsync<dynamic>(
        //            loginDTO,
        //            PartyDetailQB.GET_OU_CURRENCY,
        //            new { WorkOUId = loginDTO.WorkOUId });

        //        if (ouCurrency != null && ouCurrency.CurrencyId != detail.PartyBranchCurrencyId)
        //        {
        //            var exchange = await _QueryExecutor.QuerySingleAsync<dynamic>(
        //                loginDTO,
        //                PartyDetailQB.GET_EXCHANGE_BY_CURRENCY,
        //                new { BaseCurrencyId = ouCurrency.CurrencyId, CurrencyId = detail.PartyBranchCurrencyId });

        //            if (exchange != null)
        //            {
        //                detail.ExcId = exchange.ExchangeId;
        //                detail.CurrencyConversion = exchange.ExchangeConversionRate;
        //            }
        //            else
        //            {
        //                detail.ExcId = 0;
        //                detail.CurrencyConversion = 1;
        //            }
        //        }
        //        else if (ouCurrency != null && ouCurrency.CurrencyId == detail.PartyBranchCurrencyId)
        //        {
        //            detail.CurrencyConversion = 1;
        //        }

        //        var closing = await _QueryExecutor.QuerySingleAsync<dynamic>(
        //            loginDTO,
        //            PartyDetailQB.GET_CLOSING_BALANCE,
        //            new { AccountId = detail.PartyBranchPartyId });

        //        if (closing != null && closing.PartyClosingBalance != null)
        //        {
        //            detail.PartyClosingBalance = Convert.ToDouble(closing.PartyClosingBalance);
        //        }

        //        var account = await _QueryExecutor.QuerySingleAsync<dynamic>(
        //            loginDTO,
        //            PartyDetailQB.GET_ACCOUNT_DETAILS,
        //            new { AccountId = detail.PartyBranchPartyId });

        //        detail.AccountDTO = account;

        //        return partyDetails;
        //    }
        //    catch (Exception)
        //    {
        //        throw;
        //    }
        //}
        public async Task<List<PartyDetailsDTO>> GetPartyDetailsForInventory(
    CriteriaDTO criteriaDTO,
    LoginDTO loginDTO,
    CancellationToken ct)
        {
            try
            {
                //int partyBranchId = GetCriteriaValue(criteriaDTO, "PARTYBRANCHID") ?? -1;
                //int partyId = GetCriteriaValue(criteriaDTO, "PartyId") ?? -1;
                int partyBranchId = GetCriteriaValue(criteriaDTO, "PartyBranchId") ?? -1;
                int partyId = GetCriteriaValue(criteriaDTO, "Id") ?? GetCriteriaValue(criteriaDTO, "PartyId") ?? -1;

                //if (partyBranchId <= 0 && partyId > 0)
                //    partyBranchId = await GetPartyBranchIdByPartyId(partyId, loginDTO, ct);
                if (partyBranchId <= 0 && partyId != 0)
                {
                    partyBranchId = await GetPartyBranchIdByPartyId(partyId, loginDTO, ct);
                }

                //if (partyBranchId <= 0)
                //    partyBranchId = -1;

                var parameters = new { PartyBranchId = partyBranchId, WorkOUId = loginDTO.WorkOUId };

                var partyDetails = (await _QueryExecutor.QueryAsync<PartyDetailsDTO>(
                    loginDTO,
                    PartyDetailQB.GET_PARTY_DETAILS_INVENTORY,
                    parameters)).ToList();

                if (!partyDetails.Any())
                    throw new NotFoundException("Check the PartyOU Master - details not found.");

                var detail = partyDetails[0];

                // ✅ Fix: Force the resolved ID onto the DTO. 
                // Sometimes Dapper mapping for PartyBranchId fails if column names are slightly off or casing is strict.
                if (detail.PartyBranchId <= 0 && partyBranchId > 0)
                    detail.PartyBranchId = partyBranchId;

                // ✅ 1. Tax Type Details - fetch names from MTRANSACTIONTYPE
                var taxTypes = (await _QueryExecutor.QueryAsync<dynamic>(
                    loginDTO,
                    PartyDetailQB.GET_PARTY_TAXTYPEDETAILS,
                    new { TaxTypeId = detail.TaxTypeId })).ToList();

                if (taxTypes.Any())
                {
                    var tt = taxTypes[0];

                    int transTypeId = (int)(tt.TRANSACTIONTYPEID ?? detail.TransactionTypeId);
                    int dutyTransTypeId = (int)(tt.DUTYTRANSACTIONTYPEID ?? detail.DutyTransactionTypeId);

                    detail.TransactionTypeId = transTypeId;
                    detail.DutyTransactionTypeId = dutyTransTypeId;

                    // Fetch TransactionType name + type
                    var transType = await _QueryExecutor.QuerySingleAsync<dynamic>(
                        loginDTO,
                        @"SELECT TRANSACTIONTYPENAME, TYPE 
                  FROM MTRANSACTIONTYPE 
                  WHERE TRANSACTIONTYPEID = @Id",
                        new { Id = transTypeId });

                    detail.TransactionTypeName = transType?.TRANSACTIONTYPENAME ?? "NONE";
                    detail.TransactionTypeType = transType != null
                        ? Convert.ToByte(transType.TYPE)
                        : (byte)0;

                    // Fetch DutyTransactionType name
                    var dutyTransType = await _QueryExecutor.QuerySingleAsync<dynamic>(
                        loginDTO,
                        @"SELECT TRANSACTIONTYPENAME, TYPE 
                  FROM MTRANSACTIONTYPE 
                  WHERE TRANSACTIONTYPEID = @Id",
                        new { Id = dutyTransTypeId });

                    detail.DutyTransactionTypeName = dutyTransType?.TRANSACTIONTYPENAME ?? "NONE";
                    detail.DutyTranscType = dutyTransType != null
                        ? Convert.ToInt32(dutyTransType.TYPE)
                        : 0;
                }

                // ✅ 2. GST Number
                var gst = await _QueryExecutor.QuerySingleAsync<dynamic>(
                    loginDTO,
                    PartyDetailQB.GET_GST_NUMBER,
                    new { PartyBranchId = detail.PartyBranchId });

                detail.GSTNumber = gst?.GSTIN ?? "";

                // ✅ 3. Party Comment - parameterized to avoid SQL injection
                var commentResult = (await _QueryExecutor.QueryAsync<dynamic>(
                    loginDTO,
                    @"SELECT COMMENTTEXT AS CommentCommentText 
              FROM TCOMMENT 
              WHERE COMMENTSTATUS = 0 
              AND SECURITYLEVEL = 0 
              AND OBJECTTYPEID = -1399392247 
              AND OBJECTID = @PartyId",
                    new { PartyId = detail.PartyBranchPartyId })).ToList();

                detail.PartyComment = commentResult.Any()
                    ? string.Join(" <br> ", commentResult.Select(c => (string)(c.CommentCommentText ?? "")))
                    : "";

                // ✅ 4. Currency Conversion
                var ouCurrency = await _QueryExecutor.QuerySingleAsync<dynamic>(
                    loginDTO,
                    PartyDetailQB.GET_OU_CURRENCY,
                    new { WorkOUId = loginDTO.WorkOUId });

                if (ouCurrency != null && ouCurrency.CurrencyId != detail.PartyBranchCurrencyId)
                {
                    var exchange = await _QueryExecutor.QuerySingleAsync<dynamic>(
                        loginDTO,
                        PartyDetailQB.GET_EXCHANGE_BY_CURRENCY,
                        new { BaseCurrencyId = ouCurrency.CurrencyId, CurrencyId = detail.PartyBranchCurrencyId });

                    if (exchange != null)
                    {
                        detail.ExcId = Convert.ToInt32(exchange.ExchangeId);
                        detail.CurrencyConversion = Convert.ToDouble(exchange.ExchangeConversionRate);
                    }
                    else
                    {
                        detail.ExcId = 0;
                        detail.CurrencyConversion = 1;
                    }
                }
                else
                {
                    detail.ExcId = -1;
                    detail.CurrencyConversion = 1;
                }

                // ✅ 5. Closing Balance
                var closing = await _QueryExecutor.QuerySingleAsync<dynamic>(
                    loginDTO,
                    PartyDetailQB.GET_CLOSING_BALANCE,
                    new { AccountId = detail.PartyBranchPartyId });

                detail.PartyClosingBalance = closing?.PartyClosingBalance != null
                    ? Convert.ToDouble(closing.PartyClosingBalance)
                    : 0;

                // ✅ 6. Account DTO
                var account = await _QueryExecutor.QuerySingleAsync<dynamic>(
                    loginDTO,
                    PartyDetailQB.GET_ACCOUNT_DETAILS,
                    new { AccountId = detail.PartyBranchPartyId });

                detail.AccountDTO = account;

                // ✅ 7. RouteIds - build comma-prefixed string to match old format ",-1"
                if (detail.RouteId > 0)
                    detail.RouteIds = $",{detail.RouteId}";
                else
                    detail.RouteIds = ",-1";

                // ✅ 8. Defaults for fields not sourced from DB
                if (string.IsNullOrEmpty(detail.FormReceived))
                    detail.FormReceived = "NONE";

                if (string.IsNullOrEmpty(detail.FormIssued))
                    detail.FormIssued = "NONE";

                detail.IsFormReceived = detail.IsFormReceived == 0 ? (byte)1 : detail.IsFormReceived;
                detail.IsFormIssued = detail.IsFormIssued == 0 ? (byte)1 : detail.IsFormIssued;

                if (string.IsNullOrEmpty(detail.TransporterName))
                    detail.TransporterName = "NONE";

                if (detail.TransporterId == 0)
                    detail.TransporterId = -1;

                if (string.IsNullOrEmpty(detail.RouteName))
                    detail.RouteName = "NONE";

                // ✅ 9. PartyBranchAddon - was completely missing before
                var addonFields = (await _QueryExecutor.QueryAsync<dynamic>(
                    loginDTO,
                    PartyDetailQB.GET_PARTYBRANCH_ADDON_FIELDS,
                    new { EntityId = -1398358517  })).ToList(); // * PartyBranch EntityId */ - 1399999839 

                if (addonFields.Any())
                {
                    var fieldNames = string.Join(", ", addonFields
                        .Select(f => (string)f.DBObjectFieldsName)
                        .Where(f => !string.IsNullOrWhiteSpace(f)));

                    if (!string.IsNullOrEmpty(fieldNames))
                    {
                        var addonSql = string.Format(
                            PartyDetailQB.GET_PARTYBRANCH_ADDON_BASED_ON_ID,
                            fieldNames);

                        var addonRaw = await _QueryExecutor.QuerySingleAsync<dynamic>(
                            loginDTO,
                            addonSql,
                            new { PartyBranchId = detail.PartyBranchId });

                        if (addonRaw != null)
                        {
                            var addonJson = JsonConvert.SerializeObject(addonRaw);
                            detail.PartyBranchAddon = new List<dynamic>
                    {
                        new
                        {
                            Id = detail.PartyBranchId,
                            FeAddon = addonJson,
                            GetAddon = addonJson,
                            Attributes = new { }
                        }
                    };
                        }
                    }
                }

                // ✅ Fix: Only return the fully populated record in the list.
                // If the main query returns multiple rows due to joins, 
                // we ensure we only return the one we've processed and enriched.
                return new List<PartyDetailsDTO> { detail };
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<List<PartyDetailsDTO>> GetPartyDetails(CriteriaDTO criteria, LoginDTO loginDTO)
        {
            return await GetPartyDetailsForInventory(criteria, loginDTO, default);
        }

        public async Task<int> GetOUCurrencyId(int ouId, int tenantId)
        {
            var result = await _QueryExecutor.QuerySingleAsync<dynamic>(
                LoginDTO: null,
                sql: PartyDetailQB.GET_OU_CURRENCY,
                parameters: new { WorkOUId = ouId });

            return result?.CurrencyId ?? 0;
        }

        public async Task<dynamic> GetAccountDetails(int accountId, int tenantId)
        {
            var result = await _QueryExecutor.QuerySingleAsync<dynamic>(
                LoginDTO: null,
                sql: PartyDetailQB.GET_ACCOUNT_DETAILS,
                parameters: new { AccountId = accountId });

            return result;
        }

        public async Task<decimal> GetClosingBalance(int accountId, int tenantId)
        {
            var result = await _QueryExecutor.QuerySingleAsync<dynamic>(
                LoginDTO: null,
                sql: PartyDetailQB.GET_CLOSING_BALANCE,
                parameters: new { AccountId = accountId });

            return result?.PartyClosingBalance ?? 0m;
        }

        public async Task<List<dynamic>> GetTaxTypeDetails(int taxTypeId, int tenantId)
        {
            var result = await _QueryExecutor.QueryAsync<dynamic>(
                LoginDTO: null,
                sql: PartyDetailQB.GET_PARTY_TAXTYPEDETAILS,
                parameters: new { TaxTypeId = taxTypeId });

            return result.ToList();
        }

        public async Task<string?> GetGSTNumber(int partyBranchId, int tenantId)
        {
            var result = await _QueryExecutor.QuerySingleAsync<dynamic>(
                LoginDTO: null,
                sql: PartyDetailQB.GET_GST_NUMBER,
                parameters: new { PartyBranchId = partyBranchId });

            return result?.GSTIN;
        }

        public async Task<List<dynamic>> GetComments(int partyId, int tenantId)
        {
            var sql = "select COMMENTTEXT as CommentCommentText from TCOMMENT where COMMENTSTATUS=0 and SECURITYLEVEL=0 and OBJECTTYPEID=-1399392247 and OBJECTID=" + partyId;

            var result = await _QueryExecutor.QueryAsync<dynamic>(LoginDTO: null, sql: sql);

            return result.ToList();
        }

        public async Task<List<dynamic>> GetAddonFields(int entityId, int tenantId)
        {
            var result = await _QueryExecutor.QueryAsync<dynamic>(
                LoginDTO: null,
                sql: PartyDetailQB.GET_PARTYBRANCH_ADDON_FIELDS,
                parameters: new { EntityId = entityId });

            return result.ToList();
        }

        public async Task<dynamic> GetPartyBranchAddon(int partyBranchId, string fields, int tenantId)
        {
            var sql = string.Format(PartyDetailQB.GET_PARTYBRANCH_ADDON_BASED_ON_ID, fields);

            var result = await _QueryExecutor.QuerySingleAsync<dynamic>(
                LoginDTO: null,
                sql: sql,
                parameters: new { PartyBranchId = partyBranchId });

            return result;
        }

        private async Task<int> GetPartyBranchIdByPartyId(int partyId, LoginDTO loginDTO, CancellationToken ct)
        {
            var sql = @"SELECT TOP 1 PARTYBRANCHID FROM MPARTYBRANCH WHERE PARTYID = @PartyId ORDER BY PARTYBRANCHID";
            var result = await _QueryExecutor.QuerySingleAsync<dynamic>(loginDTO, sql, new { PartyId = partyId });
            return result?.PARTYBRANCHID ?? result?.PartyBranchId ?? -1;
        }

        private static int? GetCriteriaValue(CriteriaDTO criteriaDTO, string fieldName)
        {
            if (criteriaDTO == null) return null;
            foreach (var section in criteriaDTO.SectionCriteriaList ?? new List<GB5Shared.DTO.Framework.Criteria.SectionCriteriaDTO>())
            {
                if (section.AttributesCriteriaList == null) continue;
                foreach (var attr in section.AttributesCriteriaList)
                {
                    dynamic dynamicAttr = attr;
                    string? attrFieldName = dynamicAttr.FieldName;
                    if (!string.IsNullOrWhiteSpace(attrFieldName) && attrFieldName.Equals(fieldName, StringComparison.OrdinalIgnoreCase))
                    {
                        object? fieldValue = dynamicAttr.FieldValue;
                        if (fieldValue != null && int.TryParse(fieldValue.ToString(), out int value))
                        {
                            return value;
                        }
                    }
                }
            }
            return null;
        }

        private static string? GetCriteriaStringValue(CriteriaDTO criteriaDTO, string fieldName)
        {
            if (criteriaDTO == null) return null;
            foreach (var section in criteriaDTO.SectionCriteriaList ?? new List<GB5Shared.DTO.Framework.Criteria.SectionCriteriaDTO>())
            {
                if (section.AttributesCriteriaList == null) continue;
                foreach (var attr in section.AttributesCriteriaList)
                {
                    dynamic dynamicAttr = attr;
                    string? attrFieldName = dynamicAttr.FieldName;
                    if (!string.IsNullOrWhiteSpace(attrFieldName) && attrFieldName.Equals(fieldName, StringComparison.OrdinalIgnoreCase))
                    {
                        object? fieldValue = dynamicAttr.FieldValue;
                        var stringValue = fieldValue?.ToString();
                        if (!string.IsNullOrWhiteSpace(stringValue))
                            return stringValue;
                    }
                }
            }
            return null;
        }

        public async Task<List<UniquePhoneDTO>> GetUniquePhone(CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            try
            {
                string? mobileNo = GetCriteriaStringValue(criteriaDTO, "MobileNo");
                var parameters = new { MobileNo = mobileNo };

                var result = (await _QueryExecutor.QueryAsync<UniquePhoneDTO>(
                    loginDTO,
                    PartyDetailQB.GET_UNIQUE_PHONE,
                    parameters)).ToList();

                return result;
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}