using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using MMDAL.DTO.BatchNumberMapping;
using MMDAL.DTO.Item;
using MMDAL.Query.BatchNumberMapping;
using Newtonsoft.Json;
using static GB5Shared.GB5Constant.Constant;

namespace MMDAL.CustomCode.BatchNumberMapping
{
    public class BatchNumberMappingDAL : IBatchNumberMappingDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

        public BatchNumberMappingDAL(IQueryExecutor QueryExecutor)
        {
            _QueryExecutor = QueryExecutor;
        }

        // ── #1 Input item picklist ────────────────────────────────────────────
        public async Task<string> GetInputItemSelectListInBatchMapping(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                var parameters = new DynamicParameters();
                string additionalConditions = ApplyItemPicklistCriteria(CriteriaDTO, parameters);
                string sql = BatchNumberMappingQB.INPUT_ITEM_PICKLIST_IN_BATCH_MAPPING + additionalConditions;

                var result = await _QueryExecutor.QueryAsync<ListItemDTO>(LoginDTO, sql, parameters).ConfigureAwait(false);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        // ── #2 Output item picklist ───────────────────────────────────────────
        public async Task<string> GetOutputItemSelectListInBatchMapping(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                var parameters = new DynamicParameters();
                string additionalConditions = ApplyItemPicklistCriteria(CriteriaDTO, parameters);
                string sql = BatchNumberMappingQB.OUTPUT_ITEM_PICKLIST_IN_BATCH_MAPPING + additionalConditions;

                var result = await _QueryExecutor.QueryAsync<ListItemDTO>(LoginDTO, sql, parameters).ConfigureAwait(false);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        // ── #3 Lot picklist (paged) ───────────────────────────────────────────
        public async Task<string> GetLotSelectListForBatchNumberMapping(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string paramPrefix = LoginDTO.DatabaseType == DBType.PostGre ? ":" : "@";

                var parameters = new DynamicParameters();
                string additionalConditions = ApplyLotCriteria(CriteriaDTO, parameters, paramPrefix);

                string template = LoginDTO.DatabaseType switch
                {
                    DBType.SQL => BatchNumberMappingQB.GET_LOT_SELECTLIST_FOR_BATCHMAPPING_SQL,
                    DBType.PostGre => BatchNumberMappingQB.GET_LOT_SELECTLIST_FOR_BATCHMAPPING_PG,
                    _ => throw new Exception("Unsupported database type")
                };

                string sql = template.Replace("/*CRITERIA*/", additionalConditions);

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

                var result = await _QueryExecutor.QueryAsync<LotPicklistinBatchNumberMappingDTO>(LoginDTO, sql, parameters).ConfigureAwait(false);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        // Builds fully-parameterized AND-conditions for the input/output item picklists.
        // Criteria field-name → SQL column mapping (matches GB4 ApplyforItemSelectListinBatchMapping):
        //   ProductionId -> c.PRODUCTIONID (equality, numeric)
        //   Code         -> a.ITEMCODE     (LIKE)
        //   Name         -> a.ITEMNAME     (LIKE)
        // Values are always bound via DynamicParameters — never inlined into the SQL text.
        private string ApplyItemPicklistCriteria(CriteriaDTO criteriaDTO, DynamicParameters parameters)
        {
            if (criteriaDTO?.SectionCriteriaList == null || criteriaDTO.SectionCriteriaList.Count == 0)
                return "";

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

            foreach (var attr in attrs)
            {
                string val = ExtractStringValue(attr);
                if (string.IsNullOrEmpty(val)) continue;

                if (attr.FieldName?.Equals("ProductionId", StringComparison.OrdinalIgnoreCase) == true)
                {
                    if (int.TryParse(val, out int productionId))
                    {
                        sb.Append(" AND c.PRODUCTIONID = @ProductionId");
                        parameters.Add("ProductionId", productionId);
                    }
                }
                else if (attr.FieldName?.Equals("Code", StringComparison.OrdinalIgnoreCase) == true)
                {
                    sb.Append(" AND a.ITEMCODE LIKE @Code");
                    parameters.Add("Code", $"%{val}%");
                }
                else if (attr.FieldName?.Equals("Name", StringComparison.OrdinalIgnoreCase) == true)
                {
                    sb.Append(" AND a.ITEMNAME LIKE @Name");
                    parameters.Add("Name", $"%{val}%");
                }
            }

            return sb.ToString();
        }

        // Builds fully-parameterized AND-conditions for the lot picklist, injected into the
        // "/*CRITERIA*/" marker inside GET_LOT_SELECTLIST_FOR_BATCHMAPPING_SQL / _PG, before
        // GROUP BY. paramPrefix is "@" for SQL Server, ":" for PostgreSQL — matching the
        // dialect-specific placeholder convention used across this DAL's dual-dialect queries.
        //   LotNumber -> a.LOTNUMBER (LIKE)
        //   ItemId    -> a.ITEMID    (equality, numeric)
        //   SkuId     -> a.SKUID     (equality, numeric)
        private string ApplyLotCriteria(CriteriaDTO criteriaDTO, DynamicParameters parameters, string paramPrefix)
        {
            if (criteriaDTO?.SectionCriteriaList == null || criteriaDTO.SectionCriteriaList.Count == 0)
                return "";

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

            foreach (var attr in attrs)
            {
                string val = ExtractStringValue(attr);
                if (string.IsNullOrEmpty(val)) continue;

                if (attr.FieldName?.Equals("ItemId", StringComparison.OrdinalIgnoreCase) == true)
                {
                    if (int.TryParse(val, out int itemId))
                    {
                        sb.Append($" AND a.ITEMID = {paramPrefix}ItemId");
                        parameters.Add("ItemId", itemId);
                    }
                }
                else if (attr.FieldName?.Equals("SkuId", StringComparison.OrdinalIgnoreCase) == true)
                {
                    if (int.TryParse(val, out int skuId))
                    {
                        sb.Append($" AND a.SKUID = {paramPrefix}SkuId");
                        parameters.Add("SkuId", skuId);
                    }
                }
                else if (attr.FieldName?.Equals("LotNumber", StringComparison.OrdinalIgnoreCase) == true)
                {
                    sb.Append($" AND a.LOTNUMBER LIKE {paramPrefix}LotNumber");
                    parameters.Add("LotNumber", $"%{val}%");
                }
            }

            return sb.ToString();
        }

        private static string ExtractStringValue(AttributesCriteriaDTO attr)
        {
            if (attr?.FieldValue == null) return "";
            if (attr.FieldValue is System.Text.Json.JsonElement je)
                return je.ValueKind == System.Text.Json.JsonValueKind.String
                    ? je.GetString() ?? ""
                    : je.GetRawText() ?? "";
            return attr.FieldValue.ToString() ?? "";
        }
    }
}