type FilterItem = {
  field: string;
  operator: string;
  value?: string | number | Array<string | number> | null;
  join?: 'AND' | 'OR'; // optional: join connector BEFORE this item (first item usually undefined)
};

type BaseParams = Record<string, any>;

export function useAdvancedFilter() {
  // numeric fields (match the column field names exactly)
  const numericFields = new Set([
    'masapajak',
    'masaPajak',
    'tahunpajak',
    'tahunPajak',
    'totaldpp',
    'dpp',
    'totaldpplain',
    'totaldpplain',
    'totalppn',
    'ppn',
    'totalppnbm',
    'ppnbm',
    'jumlahuangmuka',
    'jumlahUangMuka',
    'nilaireturdpp',
    'nilaireturdpplain',
    'nilaireturdiskon',
    'nilaireturppn',
    'nilaireturppnbm',
    'tahunpajakretur',
    'masapajakretur',
  ]);

  // date fields (columns that expect date operators)
  const dateFields = new Set([
    'tanggalfaktur',
    'tanggalapproval',
    'created_at',
    'updated_at',
    'tanggal_approval',
    'tanggalretur',
  ]);

  // fields that should be treated as "status" (use LIKE semantics)
  const statusFields = new Set(['fgStatus', 'fg_status', 'statusfaktur']);

  const fieldMap: Record<string, string> = {
    // add mappings if needed; otherwise dbField returns same name
    noBupot: 'noBupot',
  };

  const dbField = (f: string) => fieldMap[f] ?? f;

  const escape = (v: string) => String(v).replace(/'/g, "''");

  const toDbDate = (value: string | Date) => {
    if (!value) return '';
    if (value instanceof Date) {
      const y = value.getFullYear();
      const m = String(value.getMonth() + 1).padStart(2, '0');
      const d = String(value.getDate()).padStart(2, '0');
      return `${y}${m}${d}`;
    }
    const digits = String(value).replace(/[^0-9]/g, '');
    if (digits.length >= 8) return digits.slice(0, 8);
    return digits;
  };

  const normalizeOp = (op?: string) => (op ?? '').toString().trim();

  function toArrayValues(v?: string | number | Array<string | number> | null) {
    if (v == null) return [];
    if (Array.isArray(v)) return v;
    if (typeof v === 'string') {
      return v
        .split(',')
        .map((s) => s.trim())
        .filter(Boolean);
    }
    return [v];
  }

  /* === ADDED: helper to map jenisInvoice value -> SQL expression ===
     - 'uang-muka'   -> fguangmuka = 'true'
     - 'pelunasan'   -> fgpelunasan = 'true'
     - 'full-payment'-> (fguangmuka = 'false' AND fgpelunasan = 'false')
  */
  const mapJenisInvoiceToExpr = (val: any) => {
    const v = String(val ?? '').trim();
    if (!v) return '';
    if (v === 'uang-muka') {
      return `"fguangmuka" = 'true'`;
    }
    if (v === 'pelunasan') {
      return `"fgpelunasan" = 'true'`;
    }
    if (v === 'full-payment') {
      // both false => full payment
      return `("fguangmuka" = 'false' AND "fgpelunasan" = 'false')`;
    }
    // unknown => fallback to false expression (so it won't match)
    return `("fguangmuka" = 'false' AND "fgpelunasan" = 'false')`;
  };

  function buildAdvancedFilter(filters?: FilterItem[] | null) {
    if (!filters || filters.length === 0) return '';

    const exprs: string[] = [];
    const joins: ('AND' | 'OR')[] = [];

    for (let i = 0; i < filters.length; i++) {
      const f = filters[i];
      if (!f || !f.field) continue;

      const rawOp = normalizeOp(f.operator);
      const fieldName = dbField(f.field);
      let expr: string | null = null;

      // ---------- SPECIAL: jenisInvoice (derived field) ----------
      if (fieldName === 'jenisInvoice') {
        // allowed operators: is, is not, is any of
        const vals = toArrayValues(f.value);
        if (/is any of/i.test(rawOp)) {
          const ors = vals.map((v) => mapJenisInvoiceToExpr(v)).filter(Boolean);
          if (ors.length) expr = `(${ors.join(' OR ')})`;
        } else if (/^is$/i.test(rawOp)) {
          const v = String(vals[0] ?? '');
          const e = mapJenisInvoiceToExpr(v);
          if (e) expr = e;
        } else if (/is not/i.test(rawOp)) {
          // "is not X" : negate mapped expression.
          // For single value, produce NOT (expr).
          const notVals = vals.length ? vals : ['']; // if empty, skip
          const notExprs = notVals
            .map((v) => {
              const e = mapJenisInvoiceToExpr(v);
              return e ? `NOT (${e})` : '';
            })
            .filter(Boolean);
          if (notExprs.length === 1) expr = notExprs[0];
          else if (notExprs.length > 1) {
            // if multiple "is not" values, they all must be true -> combine with AND
            expr = `(${notExprs.join(' AND ')})`;
          }
        }
      }

      // ---------- DATE FIELDS ----------
      if (!expr && dateFields.has(fieldName)) {
        const rawVal = f.value;
        // allow is empty / is not empty which don't need value
        if (!rawVal && !/is empty|is not empty/i.test(rawOp)) continue;

        const ymd = toDbDate(rawVal as string | Date);
        if (!ymd && !/is empty|is not empty/i.test(rawOp)) continue;

        if (/^is$/i.test(rawOp)) {
          // full day range (00:00:00 - 23:59:59)
          expr = `"${fieldName}" >= '${ymd} 00:00:00' AND "${fieldName}" <= '${ymd} 23:59:59'`;
        } else if (/is on or after/i.test(rawOp)) {
          expr = `"${fieldName}" >= '${ymd}'`;
        } else if (/is on or before/i.test(rawOp)) {
          expr = `"${fieldName}" <= '${ymd}'`;
        } else if (/is empty/i.test(rawOp)) {
          expr = `LOWER("${fieldName}") IS NULL`;
        } else if (/is not empty/i.test(rawOp)) {
          expr = `LOWER("${fieldName}") IS NOT NULL`;
        }
      }

      // ---------- EMPTY / NOT EMPTY ----------
      if (!expr && /is empty/i.test(rawOp)) {
        // per your rule: use LOWER(column) IS NULL
        expr = `LOWER("${fieldName}") IS NULL`;
      } else if (!expr && /is not empty/i.test(rawOp)) {
        expr = `LOWER("${fieldName}") IS NOT NULL`;
      }

      // ---------- IS ANY OF ----------
      if (!expr && /is any of/i.test(rawOp)) {
        const values = toArrayValues(f.value);
        if (values.length > 0) {
          if (statusFields.has(fieldName)) {
            // status fields: LIKE per item
            const ors = values.map((v) => {
              const s = escape(String(v).toLowerCase());
              return `LOWER("${fieldName}") LIKE LOWER('%${s}%')`;
            });
            expr = `(${ors.join(' OR ')})`;
          } else if (numericFields.has(fieldName)) {
            // numeric field: equality ORs
            const ors = values
              .map((v) => Number(String(v).replace(/[^0-9.-]/g, '')))
              .filter((n) => !Number.isNaN(n))
              .map((n) => `"${fieldName}" = '${n}'`);
            if (ors.length) expr = `(${ors.join(' OR ')})`;
          } else {
            // text fields: equality (LOWER)
            const ors = values.map((v) => {
              const s = escape(String(v).toLowerCase());
              return `LOWER("${fieldName}") = '${s}'`;
            });
            expr = `(${ors.join(' OR ')})`;
          }
        }
      }

      // ---------- STATUS single-value is / is not ----------
      if (
        !expr &&
        (fieldName === 'fgStatus' || fieldName === 'fg_status' || fieldName === 'statusfaktur')
      ) {
        const valRaw = f.value == null ? '' : String(f.value);
        if (valRaw !== '' || /is any of|is empty|is not empty/i.test(rawOp)) {
          const valEsc = escape(valRaw.toLowerCase());
          if (/^is$/i.test(rawOp)) {
            expr = `LOWER("${fieldName}") LIKE LOWER('%${valEsc}%')`;
          } else if (/is not/i.test(rawOp)) {
            expr = `LOWER("${fieldName}") NOT LIKE LOWER('%${valEsc}%')`;
          }
        }
      }

      // ---------- GENERIC (text & numeric) ----------
      if (!expr) {
        const valRaw = f.value == null ? '' : String(f.value);
        if (valRaw !== '') {
          const valEsc = escape(valRaw.toLowerCase());

          // numeric fields => only numeric comparisons (=, >=, <=) (we also accept >, <, != as bonus)
          if (numericFields.has(fieldName)) {
            const opMap: Record<string, string> = {
              '=': '=',
              equals: '=',
              is: '=',
              '>': '>',
              '<': '<',
              '>=': '>=',
              '<=': '<=',
              '!=': '!=',
              '<>': '!=',
            };
            const opNormalized = opMap[rawOp.toLowerCase()] ?? rawOp;
            const num = Number(String(valRaw).replace(/[^0-9.-]/g, ''));
            if (!Number.isNaN(num)) {
              if (/^(=|>=|<=|>|<|!=)$/.test(opNormalized)) {
                expr = `"${fieldName}" ${opNormalized} '${num}'`;
              } else {
                // fallback -> equality
                expr = `"${fieldName}" = '${num}'`;
              }
            } else {
              // invalid numeric value -> skip this filter
              expr = null;
            }
          } else if (/^contains$/i.test(rawOp)) {
            // contains -> wildcard LIKE (lowercased)
            expr = `LOWER("${fieldName}") LIKE LOWER('%${valEsc}%')`;
          } else if (/^equals$/i.test(rawOp)) {
            // equals -> support array -> IN(...) else equality
            const arr = Array.isArray(f.value) ? (f.value as any[]) : [String(f.value)];
            const vals = arr.map((v) => escape(String(v).toLowerCase()));
            if (vals.length === 1) {
              expr = `LOWER("${fieldName}") = '${vals[0]}'`;
            } else {
              expr = `LOWER("${fieldName}") IN (${vals.map((v) => `'${v}'`).join(',')})`;
            }
          } else if (/^(=|>=|<=|>|<|!=)$/.test(rawOp) && !numericFields.has(fieldName)) {
            // comparison on non-numeric field (allowed but lowercased)
            expr = `LOWER("${fieldName}") ${rawOp} '${valEsc}'`;
          } else if (/^(is)$/i.test(rawOp)) {
            expr = `LOWER("${fieldName}") = '${valEsc}'`;
          } else {
            // default: equality lowercased
            expr = `LOWER("${fieldName}") = '${valEsc}'`;
          }
        }
      }

      if (expr) {
        exprs.push(expr);
        const joinBefore = f.join ?? (exprs.length > 1 ? 'AND' : 'AND');
        joins.push(joinBefore);
      }
    }

    // combine expressions with provided joins (preserve order & parentheses)
    if (exprs.length === 0) return '';
    let out = exprs[0];
    for (let i = 1; i < exprs.length; i++) {
      const j = joins[i] ?? 'AND';
      out = `(${out}) ${j} (${exprs[i]})`;
    }
    return out;
  }

  function buildRequestParams(base: BaseParams = {}, advanced: string) {
    const out: BaseParams = { ...(base ?? {}) };
    // attach advanced SQL string
    out.advanced = advanced || '';
    return out;
  }

  return { buildAdvancedFilter, buildRequestParams } as const;
}

export default useAdvancedFilter;
