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

type BaseParams = Record<string, any>;

/**
 * Advanced filtering hook untuk building SQL WHERE clauses
 * 
 * @example
 * const { buildAdvancedFilter, buildRequestParams } = useAdvancedFilter();
 * const filters = [{ field: 'noBupot', operator: 'contains', value: '123' }];
 * const sql = buildAdvancedFilter(filters);
 */
export function useAdvancedFilter() {
  // ✅ Konstanta untuk field types
  const numericFields = new Set(['masaPajak', 'tahunPajak', 'dpp', 'pphDipotong']);
  const dateFields = new Set(['created_at', 'updated_at']);
  
  /**
   * ✅ FIXED: Ubah mapping dari nomorBupot → noBupot
   * Sekarang konsisten: frontend menggunakan noBupot, backend juga noBupot
   */
  const fieldMap: Record<string, string> = {
    // Tambahkan mapping lain jika diperlukan di sini
    // Contoh: 'frontendField': 'backendField'
  };

  /**
   * Get database field name with mapping
   */
  const dbField = (field: string): string => fieldMap[field] ?? field;

  /**
   * Escape single quotes untuk prevent SQL injection
   * ⚠️ NOTE: Ini partial protection, gunakan parameterized queries di backend!
   */
  const escape = (v: string): string => String(v).replace(/'/g, "''");

  /**
   * Convert various date formats to YYYYMMDD
   */
  const toDbDate = (value: string | Date): string => {
    try {
      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;
    } catch (error) {
      console.warn('Invalid date format:', value);
      return '';
    }
  };

  /**
   * ✅ IMPROVED: Normalize operator dengan lowercase
   */
  const normalizeOp = (op: string): string => 
    op?.toString().trim().toLowerCase() || '';

  /**
   * Build advanced filter SQL WHERE clause
   */
  function buildAdvancedFilter(filters?: FilterItem[] | null): string {
    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;

      // ============================================
      // 1️⃣ DATE FIELDS
      // ============================================
      if (dateFields.has(fieldName)) {
        const rawVal = f.value;
        if (!rawVal && !op.match(/is empty|is not empty/)) continue;

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

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

      // ============================================
      // 2️⃣ EMPTY/NOT EMPTY
      // ============================================
      if (op === 'is empty') {
        expr = `"${fieldName}" IS NULL`;
      } else if (op === 'is not empty') {
        expr = `"${fieldName}" IS NOT NULL`;
      }

      // ============================================
      // 3️⃣ IS ANY OF (Multiple values)
      // ============================================
      if (!expr && op === 'is any of') {
        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) {
          // ✅ IMPROVED: Normalize field name comparison
          const isStatusField = fieldName.toLowerCase() === 'fgstatus';
          
          if (isStatusField) {
            // Status field: LIKE for partial matching
            const ors = values.map((v) => {
              const s = escape(String(v).toLowerCase());
              return `LOWER("${fieldName}") LIKE '%${s}%'`;
            });
            expr = `(${ors.join(' OR ')})`;
          } else {
            // Other fields: Exact match
            const ors = values.map((v) => {
              const s = escape(String(v).toLowerCase());
              return `LOWER("${fieldName}") = '${s}'`;
            });
            expr = `(${ors.join(' OR ')})`;
          }
        }
      }

      // ============================================
      // 4️⃣ FGSTATUS SPECIAL (Single value)
      // ============================================
      if (!expr && fieldName.toLowerCase() === 'fgstatus') {
        const valRaw = f.value == null ? '' : String(f.value);
        
        if (valRaw !== '' && !['is any of', 'is empty', 'is not empty'].includes(op)) {
          const valEscaped = escape(valRaw.toLowerCase());
          
          if (op === 'is') {
            expr = `LOWER("${fieldName}") LIKE '%${valEscaped}%'`;
          } else if (op === 'is not') {
            expr = `LOWER("${fieldName}") NOT LIKE '%${valEscaped}%'`;
          }
        }
      }

      // ============================================
      // 5️⃣ GENERIC OPERATORS
      // ============================================
      if (!expr) {
        const valRaw = f.value == null ? '' : String(f.value);
        
        if (valRaw !== '') {
          const valEscaped = escape(valRaw.toLowerCase());
          const isNumericField = numericFields.has(fieldName);

          // Numeric field operators
          if (isNumericField && /^(=|>=|<=|>|<)$/.test(op)) {
            expr = `"${fieldName}" ${op} '${valEscaped}'`;
          } 
          // Text operators
          else if (op === 'contains') {
            expr = `LOWER("${fieldName}") LIKE '%${valEscaped}%'`;
          } 
          else if (op === 'equals') {
            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 (op === 'is') {
            expr = `LOWER("${fieldName}") = '${valEscaped}'`;
          }
          // Fallback
          else {
            expr = `LOWER("${fieldName}") = '${valEscaped}'`;
          }
        }
      }

      // Add expression with proper join
      if (expr) {
        exprs.push(expr);
        const joinBefore = f.join ?? 'AND';
        joins.push(joinBefore);
      }
    }

    // Build final SQL
    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;
  }

  /**
   * ✅ FIXED: Tidak ada lagi field mapping untuk noBupot
   * Build request parameters dengan clean up undefined values
   */
  function buildRequestParams(base: BaseParams = {}, advanced: string): BaseParams {
    const out: BaseParams = {};

    // Copy all defined params
    Object.keys(base).forEach((key) => {
      if (base[key] !== undefined) {
        out[key] = base[key];
      }
    });

    // ✅ REMOVED: Field mapping noBupot → nomorBupot
    // Sekarang frontend dan backend sama-sama gunakan 'noBupot'

    // Add advanced filter if exists
    if (advanced && advanced.trim() !== '') {
      out.advanced = advanced.trim();
    }

    // Clean up undefined sorting params
    if (out.sortingMode === undefined) {
      delete out.sortingMode;
    }
    if (out.sortingMethod === undefined) {
      delete out.sortingMethod;
    }

    return out;
  }

  return {
    buildAdvancedFilter,
    buildRequestParams
  } as const;
}
