using Dapper;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.Telemetry;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.Json;
using System.Text.RegularExpressions;
using static GB5Shared.DTO.Framework.Criteria.CriteriaDTO;

namespace GB5Shared.CriteriaHandler
{
    public static class CriteriaBuilder
    {
        // =====================================================
        // Compiled regex — extract alias from FROM clause
        // Matches: FROM DBO.TABLENAME alias  OR  FROM TABLENAME alias
        // =====================================================
        private static readonly Regex _aliasRegex = new(
            @"\bFROM\s+(?:\w+\.)?\w+\s+(\w+)",
            RegexOptions.IgnoreCase | RegexOptions.Compiled);

        // Matches the LAST "<alias>.<column>" reference before a trailing
        // "AS <ColumnAlias>" in a single SELECT-list item, so a criteria
        // FieldName that matches a SELECT alias can be resolved back to the
        // table it actually came from — needed for multi-join queries where
        // different fields belong to different tables (e.g. BalanceQuantity
        // lives on the joined R table, not the query's main FROM alias).
        // The [^,\n]*? gap allows wrapping expressions like
        // "ISNULL(R.USEDQUANTITY,0)        AS UsedQuantity" — anything up to
        // the next comma or newline (i.e. still the same SELECT item).
        private static readonly Regex _selectAliasRegex = new(
            @"(\w+)\.\w+[^,\n]*?\bAS\s+(\w+)",
            RegexOptions.IgnoreCase | RegexOptions.Compiled);

        /// <summary>
        /// Builds a dynamic SQL WHERE clause and Dapper parameters from CriteriaDTO.
        /// Table alias is auto-extracted from the SQL — no manual alias needed.
        /// </summary>
        public static (string WhereClause, DynamicParameters Parameters) Build(
            CriteriaDTO criteriaDTO,
            DynamicParameters existingParams,
            string sql = "")
        {
            var parameters = existingParams;
            var sectionResults = new List<string>();
            var prefix = ExtractAliasFromSql(sql);
            var fieldAliasMap = ExtractFieldAliasMapFromSql(sql);

            if (criteriaDTO?.SectionCriteriaList == null)
                return (string.Empty, parameters);

            var firstSection = criteriaDTO.SectionCriteriaList.FirstOrDefault();

            foreach (var section in criteriaDTO.SectionCriteriaList)
            {
                if (section?.AttributesCriteriaList == null) continue;

                var sectionConditions = new List<string>();
                // Kept in lockstep with sectionConditions — JoinConditions reads
                // matchedAttrs[i-1].JoinType as the connector between conditions[i-1]
                // and conditions[i]. Passing the ORIGINAL (unfiltered)
                // AttributesCriteriaList here instead would misalign the two lists
                // the moment any attribute produces an empty condition (e.g. an
                // empty search box) while others don't — JoinType would get paired
                // with the wrong condition pair, silently changing AND/OR grouping.
                // Only the early-return in JoinConditions for a single surviving
                // condition masked this until multiple non-empty conditions with a
                // mix of dropped ones combine.
                var matchedAttrs = new List<AttributesCriteriaDTO>();

                foreach (var attr in section.AttributesCriteriaList)
                {
                    if (string.IsNullOrWhiteSpace(attr.FieldName)) continue;

                    // Dynamic/defensive filtering: only build a condition for fields that
                    // actually exist in this query's SQL (as a raw column or a SELECT alias).
                    // Some FE grid/autocomplete components send extra "housekeeping" attributes
                    // (e.g. "{field}distincttag") that never correspond to a real column — those
                    // used to blindly produce an "Invalid column name" SQL error. Since every
                    // FieldName this framework can ever correctly resolve necessarily appears
                    // literally in the SQL (per the alias-must-match-column convention the
                    // derived-table wrapping in ApplyCriteria already depends on), a field that
                    // doesn't appear anywhere in the SQL text can never have worked — skip it.
                    if (!FieldExistsInSql(attr.FieldName, sql))
                    {
                        GB5Trace.Step("criteria-field-skipped-not-in-sql", new { attr.FieldName });
                        continue;
                    }

                    var condition = BuildCondition(attr, prefix, fieldAliasMap, parameters);
                    if (!string.IsNullOrEmpty(condition))
                    {
                        sectionConditions.Add(condition);
                        matchedAttrs.Add(attr);
                    }
                }

                if (!sectionConditions.Any()) continue;

                var sectionSql = JoinConditions(sectionConditions, matchedAttrs);
                sectionResults.Add($"({sectionSql})");
            }

            if (!sectionResults.Any())
                return (string.Empty, parameters);

            var sectionJoin = firstSection != null
                && firstSection.OperationType == SectionOperationType.Or
                ? " OR "
                : " AND ";

            var whereClause = "AND " + string.Join(sectionJoin, sectionResults);
            return (whereClause, parameters);
        }


        // =====================================================
        // AUTO-DETECT: Extract table alias from FROM clause
        // FROM DBO.MPROGRAMMESESSION ps  →  "ps."
        // FROM DBO.MTRAININGPROGRAMME tp →  "tp."
        // FROM DBO.SOMETABLE             →  ""  (no alias, no prefix)
        // =====================================================
        private static string ExtractAliasFromSql(string sql)
        {
            if (string.IsNullOrWhiteSpace(sql)) return "";

            var match = _aliasRegex.Match(sql);
            if (!match.Success) return "";

            var alias = match.Groups[1].Value;

            var reserved = new HashSet<string>(StringComparer.OrdinalIgnoreCase)
            {
                "WHERE", "JOIN", "INNER", "LEFT", "RIGHT", "FULL",
                "OUTER", "ON", "GROUP", "ORDER", "HAVING", "UNION",
                "SELECT", "SET", "WITH", "AND", "OR", "NOT", "IN",
                "IS", "AS", "BY", "FROM", "INTO", "VALUES", "TOP"
            };

            return reserved.Contains(alias) ? "" : $"{alias}.";
        }


        // =====================================================
        // AUTO-DETECT: Map each SELECT-list column alias to the table
        // alias it was projected from, e.g. "R.USEDQUANTITY AS UsedQuantity"
        // → { "USEDQUANTITY": "R." }. Lookup is by FieldName (case-insensitive),
        // matching a criteria FieldName against the SELECT alias, not the
        // underlying column name — that's what the caller actually filters on.
        // =====================================================
        private static Dictionary<string, string> ExtractFieldAliasMapFromSql(string sql)
        {
            var map = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);
            if (string.IsNullOrWhiteSpace(sql)) return map;

            foreach (Match m in _selectAliasRegex.Matches(sql))
            {
                var tableAlias = m.Groups[1].Value;
                var columnAlias = m.Groups[2].Value;
                map[columnAlias] = $"{tableAlias}.";
            }

            return map;
        }


        // =====================================================
        // PRIVATE: Whole-word, case-insensitive check for whether FieldName
        // appears anywhere in the query's SQL text — as a raw column reference
        // or as a SELECT-list "AS <Alias>" name. Cached per (FieldName, sql)
        // isn't needed: Build() already runs this once per attribute per request.
        // =====================================================
        private static bool FieldExistsInSql(string fieldName, string sql)
        {
            if (string.IsNullOrWhiteSpace(sql)) return false;
            return Regex.IsMatch(sql, $@"\b{Regex.Escape(fieldName)}\b", RegexOptions.IgnoreCase);
        }


        // =====================================================
        // PRIVATE: Build single condition
        // FIX: NormalizeValue called on ALL FieldValue usages
        //      so JsonElement is always unwrapped before Dapper sees it
        // =====================================================
        private static string BuildCondition(
            AttributesCriteriaDTO attr,
            string prefix,
            Dictionary<string, string> fieldAliasMap,
            DynamicParameters parameters)
        {
            // Resolution order: explicit per-field override (rare escape hatch)
            // > SELECT-list alias auto-detected from the SQL (handles the
            // common multi-join case with zero caller involvement)
            // > single auto-detected FROM alias (single-table queries).
            string effectivePrefix;
            if (!string.IsNullOrWhiteSpace(attr.TableAlias))
                effectivePrefix = $"{attr.TableAlias}.";
            else if (fieldAliasMap.TryGetValue(attr.FieldName, out var mappedAlias))
                effectivePrefix = mappedAlias;
            else
                effectivePrefix = prefix;
            var col = $"{effectivePrefix}{attr.FieldName.ToUpper()}";
            var paramName = attr.FieldName;

            // ── KEY FIX: normalize once here, use fieldValue everywhere ──
            var fieldValue = NormalizeValue(attr.FieldValue);

            switch (attr.OperationType)
            {
                case OperationType.Equal:
                    if (IsSentinel(fieldValue)) return string.Empty;
                    parameters.Add(paramName, fieldValue);
                    return $"{col} = @{paramName}";

                case OperationType.NotEqual:
                    if (IsSentinel(fieldValue)) return string.Empty;
                    parameters.Add(paramName, fieldValue);
                    return $"{col} <> @{paramName}";

                case OperationType.Like:
                    if (IsSentinel(fieldValue)) return string.Empty;
                    parameters.Add(paramName, $"%{fieldValue}%");
                    return $"{col} LIKE @{paramName}";

                case OperationType.StartWith:
                    if (IsSentinel(fieldValue)) return string.Empty;
                    parameters.Add(paramName, $"{fieldValue}%");
                    return $"{col} LIKE @{paramName}";

                case OperationType.EndsWith:
                    if (IsSentinel(fieldValue)) return string.Empty;
                    parameters.Add(paramName, $"%{fieldValue}");
                    return $"{col} LIKE @{paramName}";

                case OperationType.GreaterThan:
                    if (IsSentinel(fieldValue)) return string.Empty;
                    parameters.Add(paramName, fieldValue);
                    return $"{col} > @{paramName}";

                case OperationType.LessThan:
                    if (IsSentinel(fieldValue)) return string.Empty;
                    parameters.Add(paramName, fieldValue);
                    return $"{col} < @{paramName}";

                case OperationType.GreaterThanOrEqualTo:
                    if (IsSentinel(fieldValue)) return string.Empty;
                    parameters.Add(paramName, fieldValue);
                    return $"{col} >= @{paramName}";

                case OperationType.LessThanOrEqualTo:
                    if (IsSentinel(fieldValue)) return string.Empty;
                    parameters.Add(paramName, fieldValue);
                    return $"{col} <= @{paramName}";

                case OperationType.In:
                    // GB4-style callers send a single scalar ID — or a comma-delimited
                    // list of IDs as one string ("3,4,5,6") — via FieldValue with
                    // OperationType=In and no InArray. Splitting here rather than
                    // treating the whole string as a single element avoids handing
                    // Dapper e.g. "3,4,5,6" as the value for an int column.
                    if (attr.InArray == null || attr.InArray.Length == 0)
                    {
                        if (IsSentinel(fieldValue)) return string.Empty;
                        parameters.Add(paramName, SplitScalarValue(fieldValue));
                        return $"{col} IN @{paramName}";
                    }
                    parameters.Add(paramName, attr.InArray.Select(NormalizeValue).ToArray());
                    return $"{col} IN @{paramName}";

                case OperationType.NotIn:
                    if (attr.InArray == null || attr.InArray.Length == 0)
                    {
                        if (IsSentinel(fieldValue)) return string.Empty;
                        parameters.Add(paramName, SplitScalarValue(fieldValue));
                        return $"{col} NOT IN @{paramName}";
                    }
                    parameters.Add(paramName, attr.InArray.Select(NormalizeValue).ToArray());
                    return $"{col} NOT IN @{paramName}";

                case OperationType.Between:
                    if (attr.InArray == null || attr.InArray.Length < 2) return string.Empty;
                    string fromParam = $"{paramName}_From";
                    string toParam = $"{paramName}_To";
                    parameters.Add(fromParam, NormalizeValue(attr.InArray[0]));
                    parameters.Add(toParam, NormalizeValue(attr.InArray[1]));
                    return $"{col} BETWEEN @{fromParam} AND @{toParam}";

                default:
                    return string.Empty;
            }
        }


        // =====================================================
        // PRIVATE: Join conditions using each attr's JoinType
        // =====================================================
        // Builds LEFT-TO-RIGHT, explicitly parenthesized: each step wraps the
        // accumulated result before appending the next operator + condition, e.g.
        // ((cond1 OR cond2) OR cond3) AND cond4. Without this, mixing AND and OR
        // JoinTypes in the same section is broken by SQL's native operator
        // precedence — AND binds tighter than OR, so an unparenthesized
        // "A OR B OR C AND D" silently becomes "A OR B OR (C AND D)" instead of
        // "(A OR B OR C) AND D", which can make an otherwise-correct earlier OR
        // match get nullified by a later AND condition that can never be true
        // (e.g. a numeric column searched with a text LIKE pattern).
        private static string JoinConditions(
            List<string> conditions,
            List<AttributesCriteriaDTO> attrs)
        {
            if (conditions.Count == 1) return conditions[0];

            var result = conditions[0];

            for (int i = 1; i < conditions.Count; i++)
            {
                // The connector belongs to the condition it follows: attrs[i-1].JoinType
                // says how attrs[i-1] connects to attrs[i] (e.g. Code=Or, Name=None means
                // "Code LIKE ... OR Name LIKE ..."). The trailing condition's own JoinType
                // has nothing left to connect to and is never read.
                var join = attrs[i - 1].JoinType == AttributeJoinOperationType.Or ? " OR " : " AND ";
                result = $"({result}){join}{conditions[i]}";
            }

            return result;
        }


        // =====================================================
        // PRIVATE: Sentinel / empty value detection
        // Always call NormalizeValue first — value may be JsonElement
        // "NONE" is treated as a UI-default no-filter sentinel.
        // =====================================================
        private static bool IsSentinel(object? value)
        {
            value = NormalizeValue(value);

            if (value == null) return true;
            if (value is string s)
            {
                if (string.IsNullOrWhiteSpace(s)) return true;
                if (s.Equals("NONE", StringComparison.OrdinalIgnoreCase)) return true;
            }

            return false;
        }


        // =====================================================
        // PRIVATE: For In/NotIn with no InArray — split a comma-delimited
        // scalar FieldValue ("3,4,5,6") into individual trimmed values;
        // a single-value FieldValue ("3") becomes a 1-element array.
        // =====================================================
        private static object?[] SplitScalarValue(object? fieldValue)
        {
            if (fieldValue is string s && s.Contains(','))
            {
                return s.Split(',')
                    .Select(part => part.Trim())
                    .Where(part => part.Length > 0)
                    .Select(part => (object?)part)
                    .ToArray();
            }
            return new[] { fieldValue };
        }


        // =====================================================
        // PRIVATE: Unwrap JsonElement to native CLR type
        // System.Text.Json deserializes object fields as JsonElement
        // Dapper cannot use JsonElement as a SQL parameter — must unwrap
        // =====================================================
        private static object? NormalizeValue(object? value)
        {
            if (value is not JsonElement je)
                return value;

            return je.ValueKind switch
            {
                JsonValueKind.String => je.GetString(),

                JsonValueKind.Number =>
                    je.TryGetInt32(out var i) ? (object)i :
                    je.TryGetInt64(out var l) ? (object)l :
                    je.TryGetDecimal(out var d) ? (object)d :
                    (object)je.GetDouble(),

                JsonValueKind.True => (object)true,
                JsonValueKind.False => (object)false,
                JsonValueKind.Null => null,
                _ => (object)je.ToString()
            };
        }
    }
}