using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using MMDAL.DTO.InspectionParamSetOverride;
using MMDAL.DTO.InspectionType;
using MMDAL.DTO.Item;
using MMDAL.Query.InspectionParamSetOverride;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace MMDAL.CustomCode.InspectionParamSetOverride
{
    public class InspectionParamSetOverrideDAL : IInspectionParamSetOverrideDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

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

        public async Task<string> GetInspectionParamSetOverride(int inspectionTypeParameterSetId, LoginDTO loginDTO, CancellationToken ct)
        {
            try
            {
                var result = await _QueryExecutor.QueryAsync<InspectionTypeParameterSetDTO>(
                    loginDTO,
                    InspectionParamSetOverrideQB.GET_INSPECTIONPARAMSETOVERRIDE,
                    new { InspectionTypeParameterSetId = inspectionTypeParameterSetId },
                    cancellationToken: ct);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception ex)
            {
                throw new Exception($"Failed to retrieve InspectionParamSetOverride: {ex.Message}", ex);
            }
        }

        public async Task<string> GetInspectionParamSetOverrideList(InspectionParamSetOverrideListRequestDTO request, LoginDTO loginDTO, CancellationToken ct)
        {
            try
            {
                var result = await _QueryExecutor.QueryAsync<InspectionTypeParameterSetDTO>(
                    loginDTO,
                    InspectionParamSetOverrideQB.GET_INSPECTIONPARAMSETOVERRIDE_LIST,
                    new { request.PartyId, request.ItemId, request.InspectionTypeId },
                    cancellationToken: ct);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception ex)
            {
                throw new Exception($"Failed to retrieve InspectionParamSetOverride list: {ex.Message}", ex);
            }
        }

        public async Task<int> GetMaxSlNo(int inspectionTypeId, int partyId, int itemId, LoginDTO loginDTO, CancellationToken ct)
        {
            try
            {
                return await _QueryExecutor.ExecuteScalarAsync<int>(
                    loginDTO,
                    InspectionParamSetOverrideQB.GET_MAX_SLNO,
                    new { InspectionTypeId = inspectionTypeId, PartyId = partyId, ItemId = itemId },
                    cancellationToken: ct);
            }
            catch (Exception ex)
            {
                throw new Exception($"Failed to retrieve max SlNo for InspectionParamSetOverride: {ex.Message}", ex);
            }
        }

        public async Task<int> SaveInspectionParamSetOverride(InspectionTypeParameterSetDTO dto, LoginDTO loginDTO, CancellationToken ct)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(
                    loginDTO,
                    InspectionParamSetOverrideQB.SAVE_INSPECTIONPARAMSETOVERRIDE,
                    dto,
                    cancellationToken: ct);
                return dto.InspectionTypeParameterSetId;
            }
            catch (Exception ex)
            {
                throw new Exception($"Failed to save InspectionParamSetOverride: {ex.Message}", ex);
            }
        }

        public async Task<int> UpdateInspectionParamSetOverride(InspectionTypeParameterSetDTO dto, LoginDTO loginDTO, CancellationToken ct)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(
                    loginDTO,
                    InspectionParamSetOverrideQB.UPDATE_INSPECTIONPARAMSETOVERRIDE,
                    dto,
                    cancellationToken: ct);
                return dto.InspectionTypeParameterSetId;
            }
            catch (Exception ex)
            {
                throw new Exception($"Failed to update InspectionParamSetOverride: {ex.Message}", ex);
            }
        }

        public async Task<int> DeleteInspectionParamSetOverride(int inspectionTypeParameterSetId, LoginDTO loginDTO, CancellationToken ct)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(
                    loginDTO,
                    InspectionParamSetOverrideQB.DELETE_INSPECTIONPARAMSETOVERRIDE,
                    new
                    {
                        InspectionTypeParameterSetId = inspectionTypeParameterSetId,
                        ModifiedById = loginDTO.UserId,
                        ModifiedOn   = DateTime.UtcNow
                    },
                    cancellationToken: ct);
            }
            catch (Exception ex)
            {
                throw new Exception($"Failed to delete InspectionParamSetOverride ID {inspectionTypeParameterSetId}: {ex.Message}", ex);
            }
        }

        public async Task<string> GetPicklistInspectionParamSetOverride(int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct)
        {
            try
            {
                var parameters = new
                {
                    firstnumber = firstNumber,
                    maxresult   = maxResult,
                    searchtext  = criteriaDTO?.SearchKeyword
                };

                var result = await _QueryExecutor.QueryAsync<InspectionParamSetOverridePicklistDTO>(
                    loginDTO,
                    InspectionParamSetOverrideQB.GET_PICKLIST_INSPECTIONPARAMSETOVERRIDE,
                    parameters,
                    cancellationToken: ct);

                return JsonConvert.SerializeObject(result);
            }
            catch (Exception ex)
            {
                throw new Exception($"Failed to retrieve InspectionParamSetOverride picklist: {ex.Message}", ex);
            }
        }

        public async Task<IEnumerable<InspectionParamSetOverridePicklistDTO>> GetInspectionParamSetOverrideByCriteria(
            int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct)
        {
            try
            {
                string innerSql = ApplyInspectionParamSetOverrideCriteria(
                    InspectionParamSetOverrideQB.GET_INSPECTIONPARAMSETOVERRIDE_CRITERIA_INNER, criteriaDTO);

                string fullSql = $"WITH PagedInspectionParamSetOverride AS ({innerSql}) {InspectionParamSetOverrideQB.GET_INSPECTIONPARAMSETOVERRIDE_CRITERIA_OUTER}";

                var parameters = new { firstnumber = firstNumber, maxresult = maxResult };
                var result = await _QueryExecutor.QueryAsync<InspectionParamSetOverridePicklistDTO>(loginDTO, fullSql, parameters, cancellationToken: ct);
                return result ?? Enumerable.Empty<InspectionParamSetOverridePicklistDTO>();
            }
            catch (Exception ex)
            {
                throw new Exception($"Failed to retrieve InspectionParamSetOverride by criteria: {ex.Message}", ex);
            }
        }

        public async Task<IEnumerable<ItemDetailDTO>> GetInspectionParamSetOverrideDetailByCriteria(
            int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct)
        {
            try
            {
                string innerSql = ApplyInspectionParamSetOverrideCriteria(
                    InspectionParamSetOverrideQB.GET_INSPECTIONPARAMSETOVERRIDE_DETAIL_INNER, criteriaDTO);

                string fullSql = $"WITH PagedInspectionParamSetOverrideDetail AS ({innerSql}) {InspectionParamSetOverrideQB.GET_INSPECTIONPARAMSETOVERRIDE_DETAIL_OUTER}";

                var parameters = new { firstnumber = firstNumber, maxresult = maxResult };
                var result = await _QueryExecutor.QueryAsync<ItemDetailDTO>(loginDTO, fullSql, parameters, cancellationToken: ct);
                return result ?? Enumerable.Empty<ItemDetailDTO>();
            }
            catch (Exception ex)
            {
                throw new Exception($"Failed to retrieve InspectionParamSetOverride detail by criteria: {ex.Message}", ex);
            }
        }

        // PartyId sent via CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList filters
        // ITP.PARTYID so the override row(s)/parameters for that party come back filled in.
        // Mirrors PartyDAL.ApplyPartyQualityCriteria.
        private static string ApplyInspectionParamSetOverrideCriteria(string baseSql, CriteriaDTO criteriaDTO)
        {
            if (criteriaDTO?.SectionCriteriaList == null || criteriaDTO.SectionCriteriaList.Count == 0)
                return baseSql;

            var fieldMap = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
            {
                { "PartyId",           "ITP.PARTYID"           },
                { "ItemId",            "ITP.ITEMID"            },
                { "InspectionTypeId",  "ITP.INSPECTIONTYPEID"  },
                { "ParameterSetId",    "ITP.PARAMETERSETID"    },
            };

            var sectionAttrs = criteriaDTO.SectionCriteriaList[0]?.AttributesCriteriaList
                               ?? new List<AttributesCriteriaDTO>();

            var conditions = new List<(string Cond, CriteriaDTO.AttributeJoinOperationType JoinToNext)>();
            foreach (var attr in sectionAttrs)
            {
                if (!fieldMap.TryGetValue(attr.FieldName ?? "", out string col)) continue;

                string val = attr.FieldValue?.ToString() ?? "";
                if (string.IsNullOrWhiteSpace(val)) continue;

                string cond = BuildConditionSql(col, attr, val);
                if (cond != null)
                    conditions.Add((cond, attr.JoinType));
            }

            var sb = new StringBuilder(baseSql);
            int idx = 0;
            while (idx < conditions.Count)
            {
                var orGroup = new List<string> { conditions[idx].Cond };
                while (idx < conditions.Count - 1 &&
                       conditions[idx].JoinToNext == CriteriaDTO.AttributeJoinOperationType.Or)
                { idx++; orGroup.Add(conditions[idx].Cond); }

                sb.Append(orGroup.Count > 1
                    ? $" AND ({string.Join(" OR ", orGroup)})"
                    : $" AND {orGroup[0]}");
                idx++;
            }

            return sb.ToString();
        }

        private static string BuildConditionSql(string col, AttributesCriteriaDTO attr, string val)
        {
            return attr.OperationType switch
            {
                CriteriaDTO.OperationType.Equal                => $"{col} = {val}",
                CriteriaDTO.OperationType.NotEqual             => $"{col} <> {val}",
                CriteriaDTO.OperationType.Like                 => $"{col} LIKE '%{val}%'",
                CriteriaDTO.OperationType.StartWith            => $"{col} LIKE '{val}%'",
                CriteriaDTO.OperationType.EndsWith             => $"{col} LIKE '%{val}'",
                CriteriaDTO.OperationType.GreaterThan          => $"{col} > {val}",
                CriteriaDTO.OperationType.GreaterThanOrEqualTo => $"{col} >= {val}",
                CriteriaDTO.OperationType.LessThan             => $"{col} < {val}",
                CriteriaDTO.OperationType.LessThanOrEqualTo    => $"{col} <= {val}",
                _ => null
            };
        }
    }
}