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

namespace MMDAL.CustomCode.LotDetail
{
    public class LotDetailDAL : ILotDetailDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

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

        // Criteria field name → SQL column. Ported from GB4 LotDAL.ApplySelectListForLot.
        // OUId is deliberately absent: it is bound once as @ouid (see ResolveOUId). GB4
        // emitted both an @ouid binding and a redundant b.OUID = <same value> predicate;
        // binding once is behaviourally identical and cheaper.
        private static readonly string[,] CriteriaWithQueryField = new string[,]
        {
            { "ItemId",         "L.ITEMID"          },
            { "SKUId",          "L.SKUID"           },
            { "SkuId",          "L.SKUID"           },
            { "ProcessId",      "L.PROCESSID"       },
            { "LotTypeId",      "L.LOTTYPEID"       },
            { "LotNumber",      "L.LOTNUMBER"       },
            { "ObjectHeaderId", "LD.OBJECTHEADERID" },
            { "ObjectId",       "LD.OBJECTID"       },
            { "StoreId",        "LD.STOREID"        },
        };

        public async Task<string> GetSelectListLotDetail(
            int FirstNumber,
            int MaxResult,
            CriteriaDTO criteriaDTO,
            LoginDTO loginDTO,
            bool IsCount,
            CancellationToken ct)
        {
            try
            {
                var parameters = new DynamicParameters();
                parameters.Add("ouid", ResolveOUId(criteriaDTO, loginDTO));

                string innerSQL = ApplyLotDetailCriteria(
                                      LotDetailQB.GET_SELECTLIST_LOTDETAIL_SELECT,
                                      CriteriaWithQueryField,
                                      criteriaDTO,
                                      parameters)
                                + LotDetailQB.GET_SELECTLIST_LOTDETAIL_GROUPBY;

                if (IsCount)
                {
                    // The inner query is parameterised (@ouid + criteria), so the count
                    // must be executed with the same parameter set.
                    string countSQL = $"SELECT COUNT(*) FROM ({innerSQL}) AS CountResult";
                    var countRows = await _QueryExecutor
                        .QueryAsync<int>(loginDTO, countSQL, parameters, cancellationToken: ct)
                        .ConfigureAwait(false);
                    return countRows.FirstOrDefault().ToString();
                }

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

                string fullSQL = $"WITH PagedLotDetail AS ({innerSQL}) {LotDetailQB.GET_SELECTLIST_LOTDETAIL_OUTER}";

                var result = await _QueryExecutor
                    .QueryAsync<LotDetailPendingPicklistDTO>(loginDTO, fullSQL, parameters, cancellationToken: ct)
                    .ConfigureAwait(false);

                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        // GB4 parity: the OU filter defaults to the logged-in work OU but an `ouid`
        // criteria attribute overrides it (used by screens that pick stock for a
        // different OU than the operator's own).
        private static int ResolveOUId(CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            if (criteriaDTO?.SectionCriteriaList == null)
                return loginDTO.WorkOUId;

            foreach (var section in criteriaDTO.SectionCriteriaList)
            {
                var attr = section?.AttributesCriteriaList?.FirstOrDefault(a =>
                    a.FieldName?.Equals("OUId", StringComparison.OrdinalIgnoreCase) == true);

                if (attr != null &&
                    int.TryParse(NormalizeValue(attr.FieldValue), out int ouid) &&
                    ouid > 0)
                {
                    return ouid;
                }
            }

            return loginDTO.WorkOUId;
        }

        // Builds WHERE conditions from CriteriaDTO using join-forward semantics:
        // JoinType on attribute[i] specifies how attribute[i] connects to attribute[i+1].
        // Consecutive attributes connected by Or are wrapped in a single AND (...OR...)
        // group, which prevents the "WHERE 1=1 OR condition" bug.
        //
        // Unlike ItemDAL.ApplyItemCriteria, every value is bound as a Dapper parameter
        // rather than interpolated into the SQL text.
        private static string ApplyLotDetailCriteria(
            string baseSql,
            string[,] fieldMap,
            CriteriaDTO criteriaDTO,
            DynamicParameters parameters)
        {
            if (criteriaDTO?.SectionCriteriaList == null || criteriaDTO.SectionCriteriaList.Count == 0)
                return baseSql;

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

            var conditions = new List<(string Cond, CriteriaDTO.AttributeJoinOperationType JoinToNext)>();
            int paramIndex = 0;

            foreach (var attr in attrs)
            {
                string value = NormalizeValue(attr.FieldValue);
                if (string.IsNullOrWhiteSpace(value))
                    continue;

                string column = null;
                for (int m = 0; m < fieldMap.GetLength(0); m++)
                {
                    if (fieldMap[m, 0].Equals(attr.FieldName, StringComparison.OrdinalIgnoreCase))
                    {
                        column = fieldMap[m, 1];
                        break;
                    }
                }

                // Unmapped field names (including the OUId override handled separately,
                // and GB4's legacy control flags type/fifoqty/isstock/fifotag/inpqty)
                // are ignored rather than injected into the SQL.
                if (column == null)
                    continue;

                string condition = BuildCondition(column, attr, value, parameters, ref paramIndex);
                if (condition != null)
                    conditions.Add((condition, 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 BuildCondition(
            string column,
            AttributesCriteriaDTO attr,
            string value,
            DynamicParameters parameters,
            ref int paramIndex)
        {
            string name = $"crit{paramIndex}";

            switch (attr.OperationType)
            {
                // GB4 rendered Equal as IN(...) so a comma-separated value list works
                // for both operators. Dapper expands a collection parameter into an
                // (@crit01, @crit02, ...) list.
                case CriteriaDTO.OperationType.Equal:
                case CriteriaDTO.OperationType.In:
                    parameters.Add(name, SplitValues(value));
                    paramIndex++;
                    return $"{column} IN @{name}";

                case CriteriaDTO.OperationType.NotIn:
                    parameters.Add(name, SplitValues(value));
                    paramIndex++;
                    return $"{column} NOT IN @{name}";

                case CriteriaDTO.OperationType.NotEqual:
                    parameters.Add(name, ScalarValue(value));
                    paramIndex++;
                    return $"{column} <> @{name}";

                case CriteriaDTO.OperationType.Like:
                    parameters.Add(name, $"%{value}%");
                    paramIndex++;
                    return $"{column} LIKE @{name}";

                case CriteriaDTO.OperationType.StartWith:
                    parameters.Add(name, $"{value}%");
                    paramIndex++;
                    return $"{column} LIKE @{name}";

                case CriteriaDTO.OperationType.EndsWith:
                    parameters.Add(name, $"%{value}");
                    paramIndex++;
                    return $"{column} LIKE @{name}";

                case CriteriaDTO.OperationType.GreaterThan:
                    parameters.Add(name, ScalarValue(value));
                    paramIndex++;
                    return $"{column} > @{name}";

                case CriteriaDTO.OperationType.LessThan:
                    parameters.Add(name, ScalarValue(value));
                    paramIndex++;
                    return $"{column} < @{name}";

                case CriteriaDTO.OperationType.GreaterThanOrEqualTo:
                    parameters.Add(name, ScalarValue(value));
                    paramIndex++;
                    return $"{column} >= @{name}";

                case CriteriaDTO.OperationType.LessThanOrEqualTo:
                    parameters.Add(name, ScalarValue(value));
                    paramIndex++;
                    return $"{column} <= @{name}";

                default:
                    return null;
            }
        }

        // Splits "1,2,3" into a typed collection. Numeric lists are passed as long so
        // SQL Server can still use an index on the ID columns; anything else stays text.
        private static object SplitValues(string value)
        {
            var parts = value.Split(',')
                             .Select(p => p.Trim())
                             .Where(p => p.Length > 0)
                             .ToArray();

            if (parts.Length == 0)
                return new[] { value };

            if (parts.All(p => long.TryParse(p, out _)))
                return parts.Select(long.Parse).ToArray();

            return parts;
        }

        private static object ScalarValue(string value)
            => long.TryParse(value, out long number) ? number : value;

        // FieldValue arrives as a System.Text.Json JsonElement when the request body is
        // bound by FastEndpoints; unwrap it so Dapper never sees a JsonElement.
        private static string NormalizeValue(object fieldValue)
        {
            if (fieldValue is null)
                return null;

            if (fieldValue is JsonElement element)
            {
                return element.ValueKind switch
                {
                    JsonValueKind.String => element.GetString(),
                    JsonValueKind.Null => null,
                    JsonValueKind.Undefined => null,
                    _ => element.GetRawText()
                };
            }

            return fieldValue.ToString();
        }
    }
}