import { query } from "./db";

// ── Types ──────────────────────────────────────────────────────────────

export interface TableFilters {
  documentNumber?: string;
  vendorName?: string;
  startDate?: string;
  endDate?: string;
  status?: string;
}

export interface QueryResult<T> {
  rows: T[];
  total: number;
  summary: {
    totalAmount: number;
    taxAmount: number;
    grandTotal: number;
    count: number;
  };
}

export interface ExportResult {
  csv: string;
  filename: string;
}

export type FaDetailRow = {
  id: number;
  query_start_date: string;
  query_end_date: string;
  document_number: string;
  document_date: string;
  vendor_code: string;
  vendor_name: string;
  material_code: string;
  material_name: string;
  material_spec: string;
  unit: string;
  quantity: number;
  unit_price: number;
  amount: number;
  currency: string;
  tax_rate: number;
  tax_amount: number;
  total_amount: number;
  status: string;
  approver: string;
  approval_date: string;
  payment_status: string;
  payment_date: string;
  business_unit: string;
  cost_center: string;
  project_code: string;
  description: string;
  remarks: string;
  batch_id: string;
  extraction_date: string;
  source_url: string;
};

export type CrmContactRow = {
  id: number;
  query_start_date: string;
  query_end_date: string;
  document_number: string;
  document_date: string;
  vendor_code: string;
  vendor_name: string;
  description: string;
  amount: number;
  currency: string;
  total_amount: number;
  status: string;
  approver: string;
  approval_date: string;
  payment_status: string;
  payment_date: string;
  business_unit: string;
  cost_center: string;
  project_code: string;
  material_code: string;
  material_name: string;
  material_spec: string;
  unit: string;
  quantity: number;
  unit_price: number;
  tax_rate: number;
  tax_amount: number;
  remarks: string;
  batch_id: string;
  extraction_date: string;
  source_url: string;
};

// ── Helpers ────────────────────────────────────────────────────────────

export function buildWhereClause(
  filters: TableFilters
): { clause: string; params: unknown[] } {
  const conditions: string[] = [];
  const params: unknown[] = [];
  let idx = 1;

  if (filters.documentNumber) {
    conditions.push(`document_number ILIKE $${idx}`);
    params.push(`%${filters.documentNumber}%`);
    idx++;
  }

  if (filters.vendorName) {
    conditions.push(`vendor_name ILIKE $${idx}`);
    params.push(`%${filters.vendorName}%`);
    idx++;
  }

  if (filters.startDate) {
    conditions.push(`document_date >= $${idx}`);
    params.push(filters.startDate);
    idx++;
  }

  if (filters.endDate) {
    conditions.push(`document_date <= $${idx}`);
    params.push(filters.endDate);
    idx++;
  }

  if (filters.status) {
    conditions.push(`status = $${idx}`);
    params.push(filters.status);
    idx++;
  }

  const clause = conditions.length > 0 ? `WHERE ${conditions.join(" AND ")}` : "";
  return { clause, params };
}

// ── queryTable ─────────────────────────────────────────────────────────

export async function queryTable<T>(
  tableName: string,
  filters: TableFilters,
  page: number = 1,
  pageSize: number = 20
): Promise<QueryResult<T>> {
  const { clause, params } = buildWhereClause(filters);
  const offset = (page - 1) * pageSize;

  // Data query — cast date columns to text to avoid JS Date timezone issues
  const dateCols = ["query_start_date", "query_end_date", "document_date", "approval_date", "payment_date", "extraction_date"];
  const selectExpr = `id, ${dateCols.map(c => `${c}::text AS ${c}`).join(", ")}, ${dateCols.length > 0 ? "" : ""}batch_id, row_hash, page_number, row_index, source_url, document_number, vendor_code, vendor_name, material_code, material_name, material_spec, unit, quantity, unit_price, amount, currency, tax_rate, tax_amount, total_amount, status, approver, payment_status, description, business_unit, cost_center, project_code, remarks, synced_to_supabase`;
  const dataSql = `SELECT ${selectExpr} FROM ${tableName} ${clause} ORDER BY id DESC LIMIT $${params.length + 1} OFFSET $${params.length + 2}`;
  const dataParams = [...params, pageSize, offset];
  const dataResult = await query(dataSql, dataParams);

  // Count query
  const countSql = `SELECT COUNT(*) AS total FROM ${tableName} ${clause}`;
  const countResult = await query(countSql, params);
  const total = Number(countResult.rows[0].total);

  // Summary query
  const summarySql = `SELECT COALESCE(SUM(amount), 0) AS total_amount_sum, COALESCE(SUM(tax_amount), 0) AS tax_amount_sum, COALESCE(SUM(total_amount), 0) AS grand_total_sum, COUNT(*) AS count FROM ${tableName} ${clause}`;
  const summaryResult = await query(summarySql, params);
  const summaryRow = summaryResult.rows[0];

  return {
    rows: dataResult.rows as T[],
    total,
    summary: {
      totalAmount: Number(summaryRow.total_amount_sum),
      taxAmount: Number(summaryRow.tax_amount_sum),
      grandTotal: Number(summaryRow.grand_total_sum),
      count: Number(summaryRow.count),
    },
  };
}

// ── exportTable ────────────────────────────────────────────────────────

function escapeCsvField(value: unknown): string {
  if (value === null || value === undefined) return "";
  const str = String(value);
  if (str.includes(",") || str.includes('"') || str.includes("\n") || str.includes("\r")) {
    return `"${str.replace(/"/g, '""')}"`;
  }
  return str;
}

export async function exportTable(
  tableName: string,
  columns: { key: string; label: string }[],
  filters: TableFilters
): Promise<ExportResult> {
  const { clause, params } = buildWhereClause(filters);

  const colKeys = columns.map((c) => c.key);
  const selectSql = colKeys.join(", ");
  const sql = `SELECT ${selectSql} FROM ${tableName} ${clause} ORDER BY id DESC`;
  const result = await query(sql, params);

  const header = columns.map((c) => escapeCsvField(c.label)).join(",");
  const rows = result.rows.map((row: Record<string, unknown>) =>
    colKeys.map((key) => escapeCsvField(row[key])).join(",")
  );
  const csv = [header, ...rows].join("\n");

  const timestamp = new Date().toISOString().slice(0, 10);
  const filename = `${tableName}_${timestamp}.csv`;

  return { csv, filename };
}