using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using AccountsDAL.DTO.Instrument;
using AccountsDAL.DTO.Narration;
using AccountsDAL.Query.DeferalPlan;
using AccountsDAL.Query.Instrument;
using AccountsDAL.Query.Narration;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Logging;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.ResponseStandard;
using GB5Shared.Validation;
using Newtonsoft.Json;

namespace AccountsDAL.CustomCode.Instrument
{
    public class InstrumentDAL : IInstrumentDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;
        public InstrumentDAL(IQueryExecutor QueryExecutor, IValidation Validation)
        {
            _QueryExecutor = QueryExecutor;
            _Validation = Validation;
        }

        public async Task<string> GetInstrument(int InstrumentId, LoginDTO LoginDTO)
        {
            try
            {
                string sql = InstrumentQB.GET_INSTRUMNET;
                var parameters = new { Instrumentid = InstrumentId };
                InstrumentDTO InstrumentDTOs = await _QueryExecutor.QuerySingleAsync<InstrumentDTO>(LoginDTO, sql, parameters);
                return JsonConvert.SerializeObject(InstrumentDTOs);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> SaveInstrument(
                InstrumentDTO InstrumentDTO,
                    LoginDTO LoginDTO,
                    DbTransaction tx)
        {
            try
            {
                InstrumentDTO.InstrumentCreatedById = LoginDTO.UserId;
                InstrumentDTO.InstrumentCreatedOn = DateTime.UtcNow;
                InstrumentDTO.InstrumentModifiedById = LoginDTO.UserId;
                InstrumentDTO.InstrumentModifiedOn = DateTime.UtcNow;
                int rows = await _QueryExecutor
                    .ExecuteAsync(
                        LoginDTO,
                        InstrumentQB.SAVE_INSTRUMENT,
                        InstrumentDTO,
                        tx)
                    .ConfigureAwait(false);

                return rows > 0
                    ? "Instrument Saved Successfully."
                    : "Error while Updating Instrument";
            }
            catch
            {
                throw;
            }
        }

        public async Task<string> UpdateInstrument(InstrumentDTO InstrumentDTO, LoginDTO LoginDTO, DbTransaction tx)
        {
            try
            {
                InstrumentDTO.InstrumentModifiedById = LoginDTO.UserId;
                InstrumentDTO.InstrumentModifiedOn = DateTime.UtcNow;
                InstrumentDTO.TenantId = LoginDTO.ClientId;

                string Sql = InstrumentQB.UPDATE_INSTRUMENT;

                int rows = await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, InstrumentDTO, tx)
                    .ConfigureAwait(false);

                return rows > 0
                    ? "Instrument Updated Successfully."
                    : "Error while Updating Instrument";
            }
            catch (Exception ex)
            {
                throw new Exception("Error while updating Instrument", ex);
            }
        }

        public async Task<Result<string>> DeleteInstrument(int instrumentId, LoginDTO LoginDTO, DbTransaction tx)
        {
            //try
            //{
            //    string Sql = InstrumentQB.DELETE_INSTRUMENT;
            //    var Parameters = new { InstrumentId = InstrumentId
            //    };
            //    int result = await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, Parameters);

            //    if (result > 0)
            //    {
            //        return "Instrument deleted successfully.";
            //    }
            //    else
            //    {
            //        return "Instrument not found or could not be deleted.";
            //    }
            //}
            //catch (Exception ex)
            //{
            //    throw new Exception("Error while deleting Instrument", ex);
            //}
            try
            {
                int result = await _QueryExecutor.ExecuteAsync(LoginDTO, InstrumentQB.DELETE_INSTRUMENT,
                    new { InstrumentId = instrumentId },
                    tx).ConfigureAwait(false);

                return result > 0
                    ? Result<string>.Success("Instrument deleted successfully.")
                    : Result<string>.Failure("Instrument not found or could not be deleted");
            }
            catch (Exception)
            {
                throw;
            }
        }
        public async Task<string> GetSelectListInstrument(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                StringBuilder SqlBuilder = new StringBuilder();
                SqlBuilder.Append(InstrumentQB.GET_SELECTLIST_INSTRUMENT);
                SqlBuilder.Append(" WHERE 1 = 1 ");
                var Parameters = new Dictionary<string, object?>();

                if (CriteriaDTO?.SectionCriteriaList != null && CriteriaDTO.SectionCriteriaList.Any())
                {
                    var attributes = CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList;

                    foreach (var attr in attributes)
                    {
                        string fieldName = attr.FieldName?.Trim().ToLower() ?? string.Empty;
                        string? fieldValue = attr.FieldValue?.ToString();

                        if (string.IsNullOrWhiteSpace(fieldValue))
                            continue;

                        switch (fieldName)
                        {
                            case "ouids":
                                SqlBuilder.Append(" AND A.OUId IN (SELECT value FROM STRING_SPLIT(@OUIds, ',')) ");
                                Parameters["OUIds"] = fieldValue;
                                break;

                            case "name":
                                SqlBuilder.Append(" AND A.InstrumentName LIKE '%' + @Name + '%' ");
                                Parameters["Name"] = fieldValue;
                                break;

                            case "instrumenttype":
                                SqlBuilder.Append(" AND A.InstrumentType IN (SELECT value FROM STRING_SPLIT(@InstrumentType, ',')) ");
                                Parameters["InstrumentType"] = fieldValue;
                                break;
                        }
                    }
                }
                string Sql = SqlBuilder.ToString();
                var Result = await _QueryExecutor.QueryAsync<InstrumentPicklistDTO>(LoginDTO, Sql, Parameters);
                string Json = JsonConvert.SerializeObject(Result);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        // Full-DTO list (InstrumentDTO, not InstrumentPicklistDTO) — same criteria filtering pattern
        // as AccountDAL.GetAccountList/AccountScheduleDAL.GetAccountScheduleList (a plain CriteriaDTO-
        // filtered List call), but no FirstNumber/MaxResult paging.
        public async Task<string> GetInstrumentList(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                var sql        = InstrumentQB.GET_INSTRUMENT_LIST;
                var searchText = ExtractLikeSearchText(CriteriaDTO);
                var (statusEquals, statusNotEquals) = ExtractStatusFilter(CriteriaDTO);
                var parameter = new
                {
                    SearchText = searchText,
                    StatusEquals = statusEquals,
                    StatusNotEquals = statusNotEquals
                };
                var list = (await _QueryExecutor.QueryAsync<InstrumentDTO>(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 with the same FieldValue repeated per field — take the first non-empty one
        // found.
        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.
        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);
        }

    }
}