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() {
  const numericFields = new Set(['masaPajak', 'tahunPajak', 'dpp', 'pphDipotong']);
  const dateFields = new Set(['created_at', 'updated_at']);

  const fieldMap: Record<string, string> = {
    noBupot: 'nomorBupot',
  };

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

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

  const toDbDate = (value: string | Date) => {
    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 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 op = normalizeOp(f.operator ?? '');
      const fieldName = dbField(f.field);

      let expr: string | null = null;

      // --- DATE FIELDS ---
      if (dateFields.has(fieldName)) {
        const rawVal = f.value;
        if (!rawVal && !/is empty|is not empty/i.test(op)) continue;

        const ymd = toDbDate(rawVal as string | Date);
        if (!ymd) continue;

        if (/^is$/i.test(op)) {
          expr = `"${fieldName}" >= '${ymd} 00:00:00' AND "${fieldName}" <= '${ymd} 23:59:59'`;
        } else if (/is on or after/i.test(op)) {
          expr = `"${fieldName}" >= '${ymd}'`;
        } else if (/is on or before/i.test(op)) {
          expr = `"${fieldName}" <= '${ymd}'`;
        }
      }

      // --- EMPTY ---
      if (/is empty/i.test(op)) {
        expr = `LOWER("${fieldName}") IS NULL`;
      } else if (/is not empty/i.test(op)) {
        expr = `LOWER("${fieldName}") IS NOT NULL`;
      }

      // --- IS ANY OF ---
      if (!expr && /is any of/i.test(op)) {
        let values: Array<string | number> = [];
        if (Array.isArray(f.value)) values = f.value as any;
        else if (typeof f.value === 'string')
          values = f.value
            .split(',')
            .map((s) => s.trim())
            .filter(Boolean);
        else if (f.value != null) values = [f.value as any];

        if (values.length > 0) {
          if (fieldName === 'fgStatus' || fieldName === 'fg_status') {
            const ors = values.map((v) => {
              const s = escape(String(v).toLowerCase());
              return `LOWER("${fieldName}") LIKE LOWER('%${s}%')`;
            });
            expr = `(${ors.join(' OR ')})`;
          } else {
            const ors = values.map((v) => {
              const s = escape(String(v).toLowerCase());
              return `LOWER("${fieldName}") = '${s}'`;
            });
            expr = `(${ors.join(' OR ')})`;
          }
        }
      }

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

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

          if (numericFields.has(fieldName) && /^(=|>=|<=)$/.test(op)) {
            expr = `"${fieldName}" ${op} '${valEscaped}'`;
          } else if (/^contains$/i.test(op)) {
            expr = `LOWER("${fieldName}") LIKE LOWER('%${valEscaped}%')`;
          } else if (/^equals$/i.test(op)) {
            const values = Array.isArray(f.value)
              ? (f.value as any[]).map((v) => escape(String(v).toLowerCase()))
              : [escape(String(f.value).toLowerCase())];
            expr = `LOWER("${fieldName}") IN (${values.map((v) => `'${v}'`).join(',')})`;
          } else if (/^(>=|<=|=)$/.test(op) && !numericFields.has(fieldName)) {
            expr = `LOWER("${fieldName}") ${op} '${valEscaped}'`;
          } else if (/^(is)$/i.test(op)) {
            expr = `LOWER("${fieldName}") = '${valEscaped}'`;
          } else {
            expr = `LOWER("${fieldName}") = '${valEscaped}'`;
          }
        }
      }

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

    // combine expressions
    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 ?? {}) };
    if ('noBupot' in out) {
      out.nomorBupot = out.noBupot;
      delete out.noBupot;
    }
    out.advanced = advanced || '';
    return out;
  }

  return { buildAdvancedFilter, buildRequestParams } as const;
}

export default useAdvancedFilter;
