// 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',
//   ]);

//   // 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'; // 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(['statusdokumen', '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 === 'statusdokumen' ||
          fieldName === 'statusdokumen' ||
          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;
