// 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[] = []; // each item's expression
//     const joins: ('AND' | 'OR')[] = []; // join before each expr (for item 0, push nothing/AND by default)

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

//       // build expression for this item
//       let expr: string | null = null;

//       // DATE handling
//       if (dateFields.has(fieldName)) {
//         const rawVal = f.value;
//         if (!rawVal && !/is empty|is not empty/i.test(op)) {
//           continue;
//         }
//         if (/^is$/i.test(op)) {
//           const ymd = toDbDate(rawVal as string | Date);
//           if (!ymd) continue;
//           expr = `\"${fieldName}\" >= '${ymd} 00:00:00' AND \"${fieldName}\" <= '${ymd} 23:59:59'`;
//         } else if (/is on or after/i.test(op)) {
//           const ymd = toDbDate(rawVal as string | Date);
//           if (!ymd) continue;
//           expr = `\"${fieldName}\" >= '${ymd}'`;
//         } else if (/is on or before/i.test(op)) {
//           const ymd = toDbDate(rawVal as string | Date);
//           if (!ymd) continue;
//           expr = `\"${fieldName}\" <= '${ymd}'`;
//         }
//       }

//       // EMPTY checks (user requested LOWER("col") IS NULL semantics)
//       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 handling
//       if (!expr && /is any of/i.test(op)) {
//         // collect values array
//         let values: Array<string | number> = [];
//         if (Array.isArray(f.value)) values = f.value as any;
//         else if (typeof f.value === 'string')
//           values = (f.value as string)
//             .split(',')
//             .map((s) => s.trim())
//             .filter(Boolean);
//         else if (f.value != null) values = [f.value as any];

//         if ((values || []).length === 0) {
//           expr = null;
//         } else {
//           // special-case fgStatus: need LIKE %val% OR LIKE %val2%
//           if (fieldName === 'fgStatus' || fieldName === 'fg_status') {
//             const ors = (values as any[]).map((v) => {
//               const s = escape(String(v).toLowerCase());
//               return `LOWER(\"${fieldName}\") LIKE LOWER('%${s}%')`;
//             });
//             expr = `(${ors.join(' OR ')})`;
//           } else {
//             // default: OR of equality (case-insensitive)
//             const ors = (values as any[]).map((v) => {
//               const s = escape(String(v).toLowerCase());
//               return `LOWER(\"${fieldName}\") = '${s}'`;
//             });
//             expr = `(${ors.join(' OR ')})`;
//           }
//         }
//       }

//       // FGSTATUS special single-value is / is not / contains semantics
//       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)) {
//           expr = null;
//         } else {
//           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 text/numeric handling when expr still not set
//       if (!expr) {
//         const valRaw = f.value == null ? '' : String(f.value);
//         if (valRaw === '') {
//           expr = null;
//         } else {
//           const valEscaped = escape(valRaw.toLowerCase());

//           // numeric fields: operators (=, >=, <=)
//           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)) {
//             // equals should produce IN (wrap single value as IN)
//             // attempt to parse CSV if provided
//             let values: string[] = [];
//             if (Array.isArray(f.value))
//               values = (f.value as any[]).map((v) => escape(String(v).toLowerCase()));
//             else values = [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)) {
//             // fallback: treat as equals
//             expr = `LOWER(\"${fieldName}\") = '${valEscaped}'`;
//           } else {
//             // fallback equality
//             expr = `LOWER(\"${fieldName}\") = '${valEscaped}'`;
//           }
//         }
//       }

//       if (expr) {
//         exprs.push(expr);
//         // record join for this item (use provided join or default AND except for first item)
//         const joinBefore = (f.join as 'AND' | 'OR') ?? (exprs.length > 1 ? 'AND' : 'AND');
//         joins.push(joinBefore);
//       }
//     }

//     // now combine exprs with joins; joins[i] is join BEFORE exprs[i]
//     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;

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;
