using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Dapper;

namespace GB5Shared.ListQuery
{
    // ─────────────────────────────────────────────────────────────────────
    //  SqlClause  – the composable unit of parameterised SQL
    //
    //  Rules for all clause methods:
    //    • Return SqlClause.Empty when the filter does not apply (null/default input)
    //    • Never throw on null/default — callers always include all clauses in AsWhere
    //    • Parameter names must be unique within one query — use descriptive names
    //    • Never concatenate user values into the Sql string — always use parameters
    // ─────────────────────────────────────────────────────────────────────
    public sealed class SqlClause
    {
        public static readonly SqlClause Empty = new(string.Empty, new DynamicParameters());

        public string            Sql    { get; }
        public DynamicParameters Params { get; }
        public bool              IsEmpty => string.IsNullOrWhiteSpace(Sql);

        public SqlClause(string sql, DynamicParameters parameters)
        {
            Sql    = sql;
            Params = parameters;
        }

        // ── Combining clauses ─────────────────────────────────────────────

        /// <summary>Merges non-empty clauses with AND, merging their parameters.</summary>
        public static SqlClause Combine(IEnumerable<SqlClause> clauses)
        {
            var active = clauses.Where(c => !c.IsEmpty).ToList();
            if (active.Count == 0) return Empty;

            var merged = new DynamicParameters();
            foreach (var c in active) merged.AddDynamicParams(c.Params);

            return new SqlClause(
                string.Join("\r\n    AND ", active.Select(c => c.Sql)),
                merged);
        }

        /// <summary>
        /// Combines clauses and returns a complete WHERE block + merged parameters.
        /// Always emits at least "WHERE 1=1".
        /// </summary>
        public static (string Sql, DynamicParameters Params) AsWhere(IEnumerable<SqlClause> clauses)
        {
            var combined = Combine(clauses);
            return combined.IsEmpty
                ? ("WHERE 1=1", new DynamicParameters())
                : ($"WHERE 1=1\r\n    AND {combined.Sql}", combined.Params);
        }

        /// <summary>Merges multiple DynamicParameters instances into one.</summary>
        public static DynamicParameters Merge(params DynamicParameters[] all)
        {
            var merged = new DynamicParameters();
            foreach (var p in all) merged.AddDynamicParams(p);
            return merged;
        }
    }

    // ─────────────────────────────────────────────────────────────────────
    //  SqlClauses  – reusable, context-aware WHERE filter library
    //
    //  All methods:
    //    • Accept a QueryContext — resolve the correct alias and dialect
    //    • Return SqlClause.Empty when the filter should not apply
    //    • Use ctx.Dialect.Param("name") for parameter placeholders
    //    • Use ctx.Col("table", "COLUMN") for alias-qualified columns
    //
    //  To add a new clause:
    //    1. Add a static method here
    //    2. Return Empty on null/default input
    //    3. Use Clause() helper for simple single-param filters
    //    4. Reuse this method across all modules that share the filter
    // ─────────────────────────────────────────────────────────────────────
    public static class SqlClauses
    {
        // ── Allocation / document structural filters ──────────────────────

        public static SqlClause HeaderType(QueryContext ctx, int id) =>
            Clause($"{ctx.Col("allocation", "OBJECTHEADERTYPEID")} = {ctx.Dialect.Param("headTypeId")}",
                   "headTypeId", id);

        public static SqlClause ObjectType(QueryContext ctx, int id) =>
            id == 0 ? SqlClause.Empty :
            Clause($"{ctx.Col("allocation", "OBJECTTYPEID")} = {ctx.Dialect.Param("objTypeId")}",
                   "objTypeId", id);

        public static SqlClause AllocationType(QueryContext ctx, int type) =>
            Clause($"{ctx.Col("allocation", "ALLOCATIONTYPE")} = {ctx.Dialect.Param("allocType")}",
                   "allocType", type);

        public static SqlClause AllocationNature(QueryContext ctx, int nature) =>
            Clause($"{ctx.Col("allocation", "ALLOCATIONNATURE")} = {ctx.Dialect.Param("allocNature")}",
                   "allocNature", nature);

        public static SqlClause BizType(QueryContext ctx, int? id) =>
            id is null ? SqlClause.Empty :
            Clause($"{ctx.Col("allocation", "BIZTRANSACTIONTYPEID")} = {ctx.Dialect.Param("bizTypeId")}",
                   "bizTypeId", id.Value);

        // ── Pending quantity ──────────────────────────────────────────────

        public static SqlClause PendingOnly(QueryContext ctx) =>
            new($"{ctx.Col("pending", "PENDINGQUANTITY")} > 0", new DynamicParameters());

        public static SqlClause LoadTypeQuantity(QueryContext ctx, int? loadBizTypeId,
            string pendingAlias = "pending")
        {
            if (loadBizTypeId is null)
                return new SqlClause($"{ctx.Col(pendingAlias, "PENDINGQUANTITY")} > 0",
                                     new DynamicParameters());

            var p = ctx.Col(pendingAlias, "PENDING");
            return Clause(
                $"{ctx.Col("loadtype", "BIZTRANSACTIONTYPEID")} = {ctx.Dialect.Param("loadBizTypeId")}\r\n" +
                $"    AND (   ({ctx.Col("loadtype", "ISGOODQUANTITY")}     = 0 AND {p}QUANTITY         > 0)\r\n" +
                $"         OR ({ctx.Col("loadtype", "ISREJECTEDQUANTITY")} = 0 AND {p}REJECTEDQUANTITY > 0)\r\n" +
                $"         OR ({ctx.Col("loadtype", "ISREWORKQUANTITY")}   = 0 AND {p}REWORKQUANTITY   > 0)\r\n" +
                $"         OR ({ctx.Col("loadtype", "ISOTHERQUANTITY")}    = 0 AND {p}OTHERQUANTITY    > 0))",
                "loadBizTypeId", loadBizTypeId.Value);
        }

        public static SqlClause RemainingPackQuantity(QueryContext ctx) => new(
            $"{ctx.Dialect.IsNull(ctx.Col("detail", "GOODQUANTITY"), "0")}" +
            $" - {ctx.Dialect.IsNull("packdtl.GOODQUANTITY", "0")} > 0",
            new DynamicParameters());

        // ── Organisation filters ──────────────────────────────────────────

        /// <summary>
        /// Filters by OU across both header and detail OU columns (OR logic).
        /// </summary>
        public static SqlClause OUFilter(QueryContext ctx, int? ouId)
        {
            if (ouId is null) return SqlClause.Empty;
            var docOu = ctx.Col("document", "OUID");
            var dtlOu = ctx.Col("detail",   "INTEROUID");
            return Clause(
                $"({docOu} = {ctx.Dialect.Param("ouId")} OR {dtlOu} = {ctx.Dialect.Param("ouId")})",
                "ouId", ouId.Value);
        }

        /// <summary>
        /// Filters by OU on a single table (typically header).
        /// </summary>
        public static SqlClause OUFilterSingle(QueryContext ctx, int? ouId,
            string logicalTable = "document")
        {
            if (ouId is null) return SqlClause.Empty;
            return Clause(
                $"{ctx.Col(logicalTable, "OUID")} = {ctx.Dialect.Param("ouId")}",
                "ouId", ouId.Value);
        }

        public static SqlClause PartyFilter(QueryContext ctx, int[]? partyIds)
        {
            if (partyIds is not { Length: > 0 }) return SqlClause.Empty;
            var col = ctx.Col("document", "PARTYID");
            return Clause(
                $"({col} IN {ctx.Dialect.Param("partyIds")} OR {col} = -1)",
                "partyIds", partyIds);
        }

        public static SqlClause PartyBranchFilter(QueryContext ctx, int? partyBranchId,
            string logicalTable = "header")
        {
            if (partyBranchId is null) return SqlClause.Empty;
            return Clause(
                $"{ctx.Col(logicalTable, "PARTYBRANCHID")} = {ctx.Dialect.Param("partyBranchId")}",
                "partyBranchId", partyBranchId.Value);
        }

        // ── Text search ───────────────────────────────────────────────────

        /// <summary>
        /// Multi-column progressive search across document number, reference, party reference.
        /// Returns Empty when text is null or whitespace.
        /// </summary>
        public static SqlClause DocumentSearch(QueryContext ctx, string? text)
        {
            if (string.IsNullOrWhiteSpace(text)) return SqlClause.Empty;
            var p    = ctx.Dialect.Param("search");
            var like = ctx.Dialect.LikeCaseOp;
            return Clause(
                $"({ctx.Col("document", "DOCUMENTNUMBER")} {like} {p}\r\n" +
                $"  OR {ctx.Col("document", "PARTYREFERENCENUMBER")} {like} {p}\r\n" +
                $"  OR {ctx.Col("document", "REFERENCENUMBER")} {like} {p})",
                "search", $"%{text.Trim()}%");
        }

        /// <summary>Multi-column search for indent header (number + reference).</summary>
        public static SqlClause IndentSearch(QueryContext ctx, string? text)
        {
            if (string.IsNullOrWhiteSpace(text)) return SqlClause.Empty;
            var p    = ctx.Dialect.Param("search");
            var like = ctx.Dialect.LikeCaseOp;
            return Clause(
                $"({ctx.Col("header", "INDENTNUMBER")} {like} {p}\r\n" +
                $"  OR {ctx.Col("header", "REFERENCENUMBER")} {like} {p})",
                "search", $"%{text.Trim()}%");
        }

        /// <summary>Item code / name search.</summary>
        public static SqlClause ItemSearch(QueryContext ctx, string? text)
        {
            if (string.IsNullOrWhiteSpace(text)) return SqlClause.Empty;
            var p    = ctx.Dialect.Param("itemSearch");
            var like = ctx.Dialect.LikeCaseOp;
            return Clause(
                $"({ctx.Col("item", "ITEMCODE")} {like} {p}\r\n" +
                $"  OR {ctx.Col("item", "ITEMNAME")} {like} {p})",
                "itemSearch", $"%{text.Trim()}%");
        }

        // ── Generic exact-match filters ───────────────────────────────────
        // Used by module QBs that need simple equality filters on nullable fields.
        // Parameter name is derived from the column name (underscores stripped, lowercased).

        /// <summary>Filters by exact nullable int value. Returns Empty when null.</summary>
        public static SqlClause ExactInt(QueryContext ctx, int? value, string table, string column)
        {
            if (value is null) return SqlClause.Empty;
            var pname = column.Replace("_", "").ToLowerInvariant();
            var p = new DynamicParameters();
            p.Add(pname, value.Value);
            return new SqlClause($"{ctx.Col(table, column)} = {ctx.Dialect.Param(pname)}", p);
        }

        /// <summary>Filters by exact nullable byte value. Returns Empty when null.</summary>
        public static SqlClause ExactByte(QueryContext ctx, byte? value, string table, string column)
        {
            if (value is null) return SqlClause.Empty;
            var pname = column.Replace("_", "").ToLowerInvariant();
            var p = new DynamicParameters();
            p.Add(pname, value.Value);
            return new SqlClause($"{ctx.Col(table, column)} = {ctx.Dialect.Param(pname)}", p);
        }

        /// <summary>Filters by exact nullable short value. Returns Empty when null.</summary>
        public static SqlClause ExactShort(QueryContext ctx, short? value, string table, string column)
        {
            if (value is null) return SqlClause.Empty;
            var pname = column.Replace("_", "").ToLowerInvariant();
            var p = new DynamicParameters();
            p.Add(pname, value.Value);
            return new SqlClause($"{ctx.Col(table, column)} = {ctx.Dialect.Param(pname)}", p);
        }

        /// <summary>Filters by exact nullable Guid (UUID) value. Returns Empty when null.</summary>
        public static SqlClause ExactGuid(QueryContext ctx, Guid? value, string table, string column)
        {
            if (value is null) return SqlClause.Empty;
            var pname = column.Replace("_", "").ToLowerInvariant();
            var p = new DynamicParameters();
            p.Add(pname, value.Value);
            return new SqlClause($"{ctx.Col(table, column)} = {ctx.Dialect.Param(pname)}", p);
        }

        /// <summary>Filters by exact nullable bool value. Returns Empty when null.</summary>
        public static SqlClause ExactBool(QueryContext ctx, bool? value, string table, string column)
        {
            if (value is null) return SqlClause.Empty;
            var pname = column.Replace("_", "").ToLowerInvariant();
            var p = new DynamicParameters();
            p.Add(pname, value.Value);
            return new SqlClause($"{ctx.Col(table, column)} = {ctx.Dialect.Param(pname)}", p);
        }

        /// <summary>Filters by exact string value. Returns Empty when null or whitespace.</summary>
        public static SqlClause ExactString(QueryContext ctx, string? value, string table, string column)
        {
            if (string.IsNullOrWhiteSpace(value)) return SqlClause.Empty;
            var pname = column.Replace("_", "").ToLowerInvariant();
            var p = new DynamicParameters();
            p.Add(pname, value.Trim());
            return new SqlClause($"{ctx.Col(table, column)} = {ctx.Dialect.Param(pname)}", p);
        }

        /// <summary>
        /// LIKE/ILIKE search across code and name columns.
        /// Returns Empty when text is null or whitespace.
        /// </summary>
        public static SqlClause CodeNameSearch(
            QueryContext ctx, string? text,
            string table, string codeColumn, string nameColumn)
        {
            if (string.IsNullOrWhiteSpace(text)) return SqlClause.Empty;
            var p = new DynamicParameters();
            p.Add("codeNameSearch", $"%{text.Trim()}%");
            var like = ctx.Dialect.LikeCaseOp;
            var pr   = ctx.Dialect.Param("codeNameSearch");
            return new SqlClause(
                $"({ctx.Col(table, codeColumn)} {like} {pr}" +
                $" OR {ctx.Col(table, nameColumn)} {like} {pr})", p);
        }

        // ── Status filters ────────────────────────────────────────────────

        public static SqlClause DocumentStatus(QueryContext ctx, params int[] statuses) =>
            Clause($"{ctx.Col("document", "STATUS")} IN {ctx.Dialect.Param("statuses")}",
                   "statuses", statuses);

        public static SqlClause IndentStatus(QueryContext ctx, int status = 1) =>
            Clause($"{ctx.Col("header", "STATUS")} = {ctx.Dialect.Param("indentStatus")}",
                   "indentStatus", status);

        public static SqlClause ActiveReleaseStatus(QueryContext ctx) =>
            new($"{ctx.Col("detail", "RELEASESTATUS")} IN (0, 3, 6)", new DynamicParameters());

        // ── Work-order filters ────────────────────────────────────────────

        public static SqlClause WorkOrderAvailability(QueryContext ctx, bool showOnlyAvailable)
        {
            var d    = ctx.Alias("bizclass");
            var pdtl = ctx.Alias("posteddetail");
            var min  = ctx.Alias("postedmin");

            return new SqlClause(
                $"(   {d}.BIZTRANSACTIONCLASSID <> -1399999908\r\n" +
                $"  OR (\r\n" +
                $"        {d}.BIZTRANSACTIONCLASSID = -1399999908\r\n" +
                $"        AND (\r\n" +
                $"               {pdtl}.INDENTID = {pdtl}.COMBINEINDENTID\r\n" +
                $"            OR {ctx.Dialect.IsNull($"{pdtl}.AVAILABLETOWORK", "0")} > 0\r\n" +
                $"            OR {(showOnlyAvailable ? "1" : "0")} = 1\r\n" +
                $"            OR {ctx.Dialect.IsNull($"{min}.ROUTINGDETAILSLNO", "1")} = {pdtl}.ROUTINGDETAILSLNO\r\n" +
                $"        )\r\n" +
                $"  )\r\n" +
                $"  OR {pdtl}.AVAILABLETOWORK IS NULL\r\n" +
                ")",
                new DynamicParameters());
        }

        public static SqlClause WorkOrderNotClosed(QueryContext ctx)
        {
            var d    = ctx.Alias("bizclass");
            var pdtl = ctx.Alias("posteddetail");
            return new SqlClause(
                $"-1 <> CASE\r\n" +
                $"          WHEN {d}.BIZTRANSACTIONCLASSID <> -1399999908 THEN -2100000000\r\n" +
                $"          ELSE {ctx.Dialect.IsNull($"{pdtl}.INDENTID", "-1")}\r\n" +
                "       END",
                new DynamicParameters());
        }

        public static SqlClause BomItemSectionFilter(QueryContext ctx) => new(
            $"{ctx.Alias("posteddetail")}.ROUTINGDETAILSLNO = 1\r\n" +
            $"  OR {ctx.Dialect.IsNull($"{ctx.Alias("bomitemsection")}.Indmatcount", "0")} > 1",
            new DynamicParameters());

        public static SqlClause ProductionRemainingFilter(QueryContext ctx) => new(
            $"{ctx.Dialect.IsNull(ctx.Col("detail", "PLANNEDQUANTITY"), "0")}" +
            $" - {ctx.Dialect.IsNull("vv.Goodquantity", "0")} > 0",
            new DynamicParameters());

        // ── Paging ────────────────────────────────────────────────────────

        /// <summary>
        /// Generates the ORDER BY + paging tail. Always call this last when building a query.
        /// Injects @pgOffset and @pgSize parameters.
        /// </summary>
        public static (string Sql, DynamicParameters Params) Paging(
            QueryContext ctx, ICriteria c, string orderBy = "1")
        {
            var p = new DynamicParameters();
            p.Add("pgOffset", c.PageOffset);
            p.Add("pgSize",   c.PageSize);
            return (ctx.Dialect.Paging(orderBy, "pgOffset", "pgSize"), p);
        }

        // ── Sort helpers ──────────────────────────────────────────────────

        /// <summary>
        /// Builds a safe single-column ORDER BY expression.
        /// <paramref name="sortBy"/> is validated case-insensitively against
        /// <paramref name="allowList"/>. Unknown or null values fall back to
        /// <paramref name="defaultCol"/> (which is emitted verbatim — it must be
        /// a trusted compile-time constant, never client input).
        ///
        /// NEVER concatenate client-supplied sort columns directly into SQL.
        /// Always pass them through this method.
        /// </summary>
        public static string OrderBy(
            string?                             sortBy,
            bool                                sortDesc,
            IReadOnlyDictionary<string, string> allowList,
            string                              defaultCol)
        {
            if (sortBy is null || !allowList.TryGetValue(sortBy, out var col))
                return defaultCol;
            return sortDesc ? $"{col} DESC" : $"{col} ASC";
        }

        /// <summary>
        /// Builds a compound ORDER BY from "field:direction" tokens
        /// (e.g. <c>["headerdate:desc", "partyname:asc"]</c>).
        /// Unknown fields are silently dropped. Falls back to
        /// <paramref name="defaultCol"/> if the result is empty.
        /// </summary>
        public static string MultiOrderBy(
            string[]?                           sortFields,
            IReadOnlyDictionary<string, string> allowList,
            string                              defaultCol)
        {
            if (sortFields is null or { Length: 0 }) return defaultCol;

            var parts = new List<string>(sortFields.Length);
            foreach (var f in sortFields)
            {
                var tokens = f.Split(':', 2);
                var key    = tokens[0].Trim();
                var desc   = tokens.Length > 1 &&
                             tokens[1].Trim().Equals("desc", StringComparison.OrdinalIgnoreCase);
                if (allowList.TryGetValue(key, out var col))
                    parts.Add(desc ? $"{col} DESC" : $"{col} ASC");
                // unknown fields are silently dropped — no injection risk
            }

            return parts.Count == 0 ? defaultCol : string.Join(", ", parts);
        }

        // ── Internal helper ───────────────────────────────────────────────

        private static SqlClause Clause(string sql, string paramName, object value)
        {
            var p = new DynamicParameters();
            p.Add(paramName, value);
            return new SqlClause(sql, p);
        }
    }
}