// 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',
//     'masakreditpajak', // sudah ada
//     'masaKreditPajak', // optional: camelCase variant
//     'tahunkreditpajak', // <--- TAMBAHKAN INI (penting)
//     'tahunKreditPajak',
//   ]);

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

//   // 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;

type FilterItem = {
  field: string;
  operator: string;
  value?: string | number | Array<string | number> | null;
  join?: 'AND' | 'OR';
};

type BaseParams = Record<string, any>;

export function useAdvancedFilter() {
  /* ===================== FIELD GROUP ===================== */

  // numeric fields → SEMUA disimpan lowercase
  const numericFields = new Set(
    [
      'masapajak',
      'tahunpajak',
      'masakreditpajak',
      'tahunkreditpajak',
      'totaldpp',
      'totaldpplain',
      'totalppn',
      'totalppnbm',
      'jumlahuangmuka',
      'jumlahpelunasan',
      'uangmukadpp',
      'uangmukadpplain',
      'uangmukappn',
      'uangmukappnbm',
      'ppn',
      'ppnbm',
      'dpp',
    ].map((v) => v.toLowerCase())
  );

  // date fields
  const dateFields = new Set(
    ['tanggalfaktur', 'tanggalapproval', 'created_at', 'updated_at', 'tanggal_approval'].map((v) =>
      v.toLowerCase()
    )
  );

  // status fields
  const statusFields = new Set(['fgstatus', 'fg_status', 'statusfaktur']);

  /* ===================== HELPERS ===================== */

  const dbField = (f: string) => f;

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

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

  const 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];
  };

  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}`;
    }
    return String(value)
      .replace(/[^0-9]/g, '')
      .slice(0, 8);
  };

  /* ===================== JENIS INVOICE ===================== */

  const mapJenisInvoiceToExpr = (val: any) => {
    const v = String(val ?? '').toLowerCase();
    if (v === 'uang-muka') return `"fguangmuka" = 'true'`;
    if (v === 'pelunasan') return `"fgpelunasan" = 'true'`;
    if (v === 'full-payment') return `("fguangmuka" = 'false' AND "fgpelunasan" = 'false')`;
    return '';
  };

  /* ===================== MAIN BUILDER ===================== */

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

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

    for (const f of filters) {
      if (!f?.field) continue;

      const fieldName = dbField(f.field);
      const fieldKey = fieldName.toLowerCase();
      const op = normalizeOp(f.operator);

      let expr: string | null = null;

      /* ---------- JENIS INVOICE ---------- */
      if (fieldKey === 'jenisinvoice') {
        const values = toArrayValues(f.value);
        if (op === 'is') {
          expr = mapJenisInvoiceToExpr(values[0]);
        } else if (op === 'is any of') {
          const ors = values.map(mapJenisInvoiceToExpr).filter(Boolean);
          if (ors.length) expr = `(${ors.join(' OR ')})`;
        } else if (op === 'is not') {
          const e = mapJenisInvoiceToExpr(values[0]);
          if (e) expr = `NOT (${e})`;
        }
      }

      /* ---------- DATE ---------- */
      if (!expr && dateFields.has(fieldKey)) {
        const ymd = toDbDate(f.value as any);
        if (op === 'is') {
          expr = `"${fieldName}" BETWEEN '${ymd} 00:00:00' AND '${ymd} 23:59:59'`;
        } else if (op === 'is on or after') {
          expr = `"${fieldName}" >= '${ymd}'`;
        } else if (op === 'is on or before') {
          expr = `"${fieldName}" <= '${ymd}'`;
        } else if (op === 'is empty') {
          expr = `"${fieldName}" IS NULL`;
        } else if (op === 'is not empty') {
          expr = `"${fieldName}" IS NOT NULL`;
        }
      }

      /* ---------- IS EMPTY ---------- */
      if (!expr && op === 'is empty') {
        expr = `"${fieldName}" IS NULL`;
      }
      if (!expr && op === 'is not empty') {
        expr = `"${fieldName}" IS NOT NULL`;
      }

      /* ---------- IS ANY OF ---------- */
      if (!expr && op === 'is any of') {
        const values = toArrayValues(f.value);
        if (numericFields.has(fieldKey)) {
          const ors = values
            .map((v) => Number(v))
            .filter((n) => !Number.isNaN(n))
            .map((n) => `"${fieldName}" = ${n}`);
          if (ors.length) expr = `(${ors.join(' OR ')})`;
        } else if (statusFields.has(fieldKey)) {
          const ors = values.map(
            (v) => `LOWER("${fieldName}") LIKE '%${escape(String(v).toLowerCase())}%'`
          );
          expr = `(${ors.join(' OR ')})`;
        } else {
          const ors = values.map(
            (v) => `LOWER("${fieldName}") = '${escape(String(v).toLowerCase())}'`
          );
          expr = `(${ors.join(' OR ')})`;
        }
      }

      /* ---------- NUMERIC ---------- */
      if (!expr && numericFields.has(fieldKey)) {
        const num = Number(String(f.value).replace(/[^0-9.-]/g, ''));
        if (!Number.isNaN(num)) {
          const opMap: Record<string, string> = {
            is: '=',
            equals: '=',
            '=': '=',
            '>': '>',
            '<': '<',
            '>=': '>=',
            '<=': '<=',
            '!=': '!=',
            contains: '=', // ⬅️ FIX UTAMA
          };
          const sqlOp = opMap[op] ?? '=';
          expr = `"${fieldName}" ${sqlOp} ${num}`;
        }
      }

      /* ---------- TEXT ---------- */
      if (!expr && f.value != null) {
        const val = escape(String(f.value).toLowerCase());
        if (op === 'contains') {
          expr = `LOWER("${fieldName}") LIKE '%${val}%'`;
        } else if (op === 'is' || op === 'equals') {
          expr = `LOWER("${fieldName}") = '${val}'`;
        } else if (op === 'is not') {
          expr = `LOWER("${fieldName}") != '${val}'`;
        }
      }

      if (expr) {
        exprs.push(expr);
        joins.push(f.join ?? 'AND');
      }
    }

    if (!exprs.length) return '';

    let out = exprs[0];
    for (let i = 1; i < exprs.length; i++) {
      out = `(${out}) ${joins[i]} (${exprs[i]})`;
    }
    return out;
  }

  function buildRequestParams(base: BaseParams = {}, advanced: string) {
    return {
      ...base,
      advanced: advanced || '',
    };
  }

  return { buildAdvancedFilter, buildRequestParams } as const;
}

export default useAdvancedFilter;
