/**
 * Bank statement import query helpers — bank-statement-import (wave-10).
 *
 * Supports Israeli bank CSV formats:
 *   - Leumi      (Bank Leumi)
 *   - Hapoalim   (Bank Hapoalim)
 *   - Discount   (Bank Discount)
 *   - Visa Cal   (Cal credit card)
 *   - Generic    (date, description, amount — auto-detect credit/debit)
 *
 * Duplicate detection: skips rows where (tenant_id, expense_date, amount, vendor_name)
 * already exists in the expenses table.
 */
import { and, eq, sql } from 'drizzle-orm'
import type { Db } from '../client'
import { expenses } from '../schema/expenses'

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

export type BankFormat = 'leumi' | 'hapoalim' | 'discount' | 'visa-cal' | 'generic'

export interface ParsedTransaction {
  date: string          // YYYY-MM-DD
  description: string
  amount: string        // positive numeric string
  type: 'credit' | 'debit'
  rawRow: string[]
}

export interface ImportSummary {
  imported: number
  skipped: number
  duplicates: number
}

// ── CSV parsing helpers ───────────────────────────────────────────────────────

/**
 * Split a single CSV line into fields, handling quoted fields.
 */
function parseCsvLine(line: string): string[] {
  const fields: string[] = []
  let current = ''
  let inQuotes = false

  for (let i = 0; i < line.length; i++) {
    const ch = line[i]
    if (ch === '"') {
      if (inQuotes && line[i + 1] === '"') {
        current += '"'
        i++
      } else {
        inQuotes = !inQuotes
      }
    } else if (ch === ',' && !inQuotes) {
      fields.push(current.trim())
      current = ''
    } else {
      current += ch
    }
  }
  fields.push(current.trim())
  return fields
}

/**
 * Parse an Israeli date string to YYYY-MM-DD.
 * Supports: DD/MM/YYYY, DD.MM.YYYY, YYYY-MM-DD.
 */
function parseIsraeliDate(raw: string): string | null {
  const s = raw.trim()
  // YYYY-MM-DD already
  if (/^\d{4}-\d{2}-\d{2}$/.test(s)) return s

  // DD/MM/YYYY or DD.MM.YYYY
  const m = s.match(/^(\d{1,2})[/.](\d{1,2})[/.](\d{4})$/)
  if (m) {
    const day = m[1] ?? ''
    const month = m[2] ?? ''
    const year = m[3] ?? ''
    return `${year}-${month.padStart(2, '0')}-${day.padStart(2, '0')}`
  }

  return null
}

/**
 * Parse a numeric amount string (may include commas, minus signs, parentheses).
 * Returns { amount: positive string, negative: boolean }.
 */
function parseAmount(raw: string): { amount: string; negative: boolean } | null {
  let s = raw.trim().replace(/,/g, '').replace(/\s/g, '')
  let negative = false

  if (s.startsWith('(') && s.endsWith(')')) {
    negative = true
    s = s.slice(1, -1)
  }
  if (s.startsWith('-')) {
    negative = true
    s = s.slice(1)
  }
  if (s.startsWith('+')) {
    s = s.slice(1)
  }

  const n = parseFloat(s)
  if (isNaN(n) || n === 0) return null
  return { amount: Math.abs(n).toFixed(2), negative }
}

// ── Format-specific parsers ───────────────────────────────────────────────────

/**
 * Leumi CSV format:
 *   תאריך,תיאור,מסמך,אסמכתא,חובה,זכות,יתרה
 *   (Date, Description, Document, Reference, Debit, Credit, Balance)
 */
function parseLeumiRow(fields: string[]): ParsedTransaction | null {
  if (fields.length < 6) return null
  const date = parseIsraeliDate(fields[0] ?? '')
  if (!date) return null
  const description = fields[1]?.trim() ?? ''
  const debitRaw = fields[4]?.trim() ?? ''
  const creditRaw = fields[5]?.trim() ?? ''

  if (debitRaw && debitRaw !== '0' && debitRaw !== '-') {
    const parsed = parseAmount(debitRaw)
    if (!parsed) return null
    return { date, description, amount: parsed.amount, type: 'debit', rawRow: fields }
  }
  if (creditRaw && creditRaw !== '0' && creditRaw !== '-') {
    const parsed = parseAmount(creditRaw)
    if (!parsed) return null
    return { date, description, amount: parsed.amount, type: 'credit', rawRow: fields }
  }
  return null
}

/**
 * Hapoalim CSV format:
 *   תאריך ערך,תיאור,סכום,יתרה
 *   (Value date, Description, Amount [negative=debit], Balance)
 */
function parseHapoalimRow(fields: string[]): ParsedTransaction | null {
  if (fields.length < 3) return null
  const date = parseIsraeliDate(fields[0] ?? '')
  if (!date) return null
  const description = fields[1]?.trim() ?? ''
  const parsed = parseAmount(fields[2] ?? '')
  if (!parsed) return null
  return {
    date,
    description,
    amount: parsed.amount,
    type: parsed.negative ? 'debit' : 'credit',
    rawRow: fields,
  }
}

/**
 * Discount CSV format:
 *   תאריך,פרטים,זיכוי,חיוב,יתרה
 *   (Date, Details, Credit, Debit, Balance)
 */
function parseDiscountRow(fields: string[]): ParsedTransaction | null {
  if (fields.length < 4) return null
  const date = parseIsraeliDate(fields[0] ?? '')
  if (!date) return null
  const description = fields[1]?.trim() ?? ''
  const creditRaw = fields[2]?.trim() ?? ''
  const debitRaw = fields[3]?.trim() ?? ''

  if (debitRaw && debitRaw !== '0' && debitRaw !== '-') {
    const parsed = parseAmount(debitRaw)
    if (!parsed) return null
    return { date, description, amount: parsed.amount, type: 'debit', rawRow: fields }
  }
  if (creditRaw && creditRaw !== '0' && creditRaw !== '-') {
    const parsed = parseAmount(creditRaw)
    if (!parsed) return null
    return { date, description, amount: parsed.amount, type: 'credit', rawRow: fields }
  }
  return null
}

/**
 * Visa Cal credit card CSV format:
 *   תאריך עסקה,שם בית עסק,סכום עסקה,סוג עסקה,תאריך חיוב
 *   (Transaction date, Merchant name, Amount, Type, Billing date)
 *   All entries are debits (charges to card).
 */
function parseVisaCalRow(fields: string[]): ParsedTransaction | null {
  if (fields.length < 3) return null
  const date = parseIsraeliDate(fields[0] ?? '')
  if (!date) return null
  const description = fields[1]?.trim() ?? ''
  const parsed = parseAmount(fields[2] ?? '')
  if (!parsed) return null
  // Cal charges are always debits (expenses)
  return { date, description, amount: parsed.amount, type: 'debit', rawRow: fields }
}

/**
 * Generic CSV format: date, description, amount
 * Positive = credit, negative = debit.
 */
function parseGenericRow(fields: string[]): ParsedTransaction | null {
  if (fields.length < 3) return null
  const date = parseIsraeliDate(fields[0] ?? '')
  if (!date) return null
  const description = fields[1]?.trim() ?? ''
  const parsed = parseAmount(fields[2] ?? '')
  if (!parsed) return null
  return {
    date,
    description,
    amount: parsed.amount,
    type: parsed.negative ? 'debit' : 'credit',
    rawRow: fields,
  }
}

// ── Public: parse bank statement ──────────────────────────────────────────────

/**
 * Parse a bank/credit card CSV string into transaction records.
 * Skips header rows (non-numeric date field) and blank lines.
 */
export function parseBankStatement(
  csvContent: string,
  format: BankFormat,
): ParsedTransaction[] {
  const lines = csvContent
    .split(/\r?\n/)
    .map((l) => l.trim())
    .filter(Boolean)

  const transactions: ParsedTransaction[] = []

  const rowParser: (fields: string[]) => ParsedTransaction | null =
    format === 'leumi'    ? parseLeumiRow
    : format === 'hapoalim' ? parseHapoalimRow
    : format === 'discount' ? parseDiscountRow
    : format === 'visa-cal' ? parseVisaCalRow
    : parseGenericRow

  for (const line of lines) {
    const fields = parseCsvLine(line)
    // Skip obvious header rows (first field is non-date text or empty)
    if (!fields[0] || !/\d/.test(fields[0])) continue

    const tx = rowParser(fields)
    if (tx) transactions.push(tx)
  }

  return transactions
}

// ── Public: import transactions ───────────────────────────────────────────────

/**
 * Import parsed transactions into the expenses table.
 * Only imports debit transactions as expenses.
 * Credit transactions are counted as skipped.
 * Duplicate detection: same (tenantId, expenseDate, amount, vendorName).
 */
export async function importBankTransactions(
  db: Db,
  tenantId: string,
  userId: string,
  transactions: ParsedTransaction[],
): Promise<ImportSummary> {
  let imported = 0
  let skipped = 0
  let duplicates = 0

  for (const tx of transactions) {
    // Only import debits as expenses (credits = income, out of scope)
    if (tx.type === 'credit') {
      skipped++
      continue
    }

    // Duplicate check
    const existing = await db
      .select({ id: expenses.id })
      .from(expenses)
      .where(
        and(
          eq(expenses.tenantId, tenantId),
          sql`${expenses.expenseDate} = ${tx.date}`,
          sql`${expenses.amount} = ${tx.amount}::numeric`,
          sql`${expenses.vendorName} = ${tx.description}`,
        ),
      )
      .limit(1)

    if (existing.length > 0) {
      duplicates++
      continue
    }

    try {
      await db.insert(expenses).values({
        tenantId,
        createdBy: userId,
        r2Key: `bank-import/${tenantId}/${Date.now()}-${Math.random().toString(36).slice(2)}`,
        fileName: `bank-import-${tx.date}.csv`,
        fileType: 'csv',
        fileSizeBytes: 0,
        vendorName: tx.description,
        currency: 'ILS',
        amount: tx.amount,
        expenseDate: tx.date,
        source: 'upload',
        sourceMetadata: { importedFrom: 'bank-statement', rawRow: tx.rawRow },
        status: 'COMPLETED',
        notes: `Imported from bank statement`,
      })
      imported++
    } catch {
      skipped++
    }
  }

  return { imported, skipped, duplicates }
}