/**
 * Frontend-owned store for the Amazon Ads invoice import (`data/amazon-ads.db`,
 * better-sqlite3 — same lazy/fail-soft pattern as legalDocsDb.ts / chatDb.ts).
 * Holds:
 *   - settings: the Amazon Ads API connection (LWA refresh token from the OAuth
 *     consent, chosen advertising profile) — the token NEVER leaves the server;
 *   - invoices: every Ads invoice the page has seen (from the API or an uploaded
 *     PDF) with its normalised payload, the stored PDF path and the import
 *     bookkeeping (created c_invoice id, voided flag). iDempiere stays the
 *     authority for "is it booked?" (the list re-checks the vendor's AP
 *     invoices); this table is the fast index + the PDF/JSON cache.
 * PDFs live next to it in `data/amazon-ads/<invoiceNo>.pdf` (git-ignored).
 */
import Database from 'better-sqlite3'
import path from 'path'
import fs from 'fs'
import type { AdsInvoice } from './types'

let db: any = null
let initFailed = false

const dataDir = () => path.join(process.cwd(), 'data')
export const adsPdfDir = () => path.join(dataDir(), 'amazon-ads')

const getDb = () => {
  if (db) return db
  if (initFailed) return null
  try {
    if (!fs.existsSync(dataDir())) fs.mkdirSync(dataDir(), { recursive: true })
    const _db = new Database(path.join(dataDir(), 'amazon-ads.db'))
    _db.pragma('journal_mode = WAL')
    _db.exec(`
      CREATE TABLE IF NOT EXISTS settings (
        key TEXT PRIMARY KEY,
        value TEXT,
        updated_at TEXT NOT NULL DEFAULT (datetime('now'))
      );
      CREATE TABLE IF NOT EXISTS invoices (
        invoice_no TEXT PRIMARY KEY,
        source TEXT NOT NULL,
        payload TEXT NOT NULL,
        pdf_path TEXT,
        pdf_name TEXT,
        c_invoice_id INTEGER,
        c_invoice_docno TEXT,
        imported_at TEXT,
        imported_by TEXT,
        voided_at TEXT,
        created_at TEXT NOT NULL DEFAULT (datetime('now')),
        updated_at TEXT NOT NULL DEFAULT (datetime('now'))
      );
      CREATE INDEX IF NOT EXISTS idx_ads_invoices_cinv ON invoices(c_invoice_id);
    `)
    db = _db
    return db
  } catch (e: any) {
    initFailed = true
    console.warn('[AmazonAds] store unavailable:', e?.message || e)
    return null
  }
}

export const adsStoreAvailable = () => !!getDb()

/* ------------------------------ settings ------------------------------ */
export const getAdsSetting = (key: string): string | null => {
  const d = getDb(); if (!d) return null
  try { return d.prepare('SELECT value FROM settings WHERE key = ?').get(key)?.value ?? null } catch { return null }
}
export const setAdsSetting = (key: string, value: string | null) => {
  const d = getDb(); if (!d) return false
  try {
    if (value == null) d.prepare('DELETE FROM settings WHERE key = ?').run(key)
    else d.prepare(`INSERT INTO settings(key, value, updated_at) VALUES(?, ?, datetime('now'))
                    ON CONFLICT(key) DO UPDATE SET value = excluded.value, updated_at = excluded.updated_at`).run(key, value)
    return true
  } catch { return false }
}

/* ------------------------------ invoices ------------------------------ */
export interface StoredAdsInvoice {
  invoiceNo: string
  source: 'api' | 'pdf'
  invoice: AdsInvoice
  pdfPath: string | null
  pdfName: string | null
  cInvoiceId: number | null
  cInvoiceDocNo: string | null
  importedAt: string | null
  importedBy: string | null
  voidedAt: string | null
  updatedAt: string | null
}

const mapRow = (r: any): StoredAdsInvoice | null => {
  if (!r) return null
  let invoice: any = null
  try { invoice = JSON.parse(r.payload) } catch { invoice = null }
  return {
    invoiceNo: r.invoice_no, source: r.source, invoice,
    pdfPath: r.pdf_path || null, pdfName: r.pdf_name || null,
    cInvoiceId: r.c_invoice_id || null, cInvoiceDocNo: r.c_invoice_docno || null,
    importedAt: r.imported_at || null, importedBy: r.imported_by || null,
    voidedAt: r.voided_at || null, updatedAt: r.updated_at || null
  }
}

export const getStoredAdsInvoice = (invoiceNo: string): StoredAdsInvoice | null => {
  const d = getDb(); if (!d) return null
  try { return mapRow(d.prepare('SELECT * FROM invoices WHERE invoice_no = ?').get(invoiceNo)) } catch { return null }
}

export const listStoredAdsInvoices = (opts: { from?: string | null; to?: string | null } = {}): StoredAdsInvoice[] => {
  const d = getDb(); if (!d) return []
  try {
    const rows: any[] = d.prepare('SELECT * FROM invoices ORDER BY json_extract(payload, \'$.invoiceDate\') DESC, invoice_no DESC').all()
    return rows.map(mapRow).filter((r): r is StoredAdsInvoice => !!r && !!r.invoice).filter(r => {
      const dte = r.invoice.invoiceDate || ''
      if (opts.from && dte && dte < opts.from) return false
      if (opts.to && dte && dte > opts.to) return false
      return true
    })
  } catch { return [] }
}

/** Insert or refresh the cached payload. Import bookkeeping columns are kept. */
export const upsertStoredAdsInvoice = (invoice: AdsInvoice, opts: { pdf?: Buffer | null; pdfName?: string | null } = {}) => {
  const d = getDb(); if (!d) return null
  let pdfPath: string | null = null
  if (opts.pdf?.length) {
    try {
      if (!fs.existsSync(adsPdfDir())) fs.mkdirSync(adsPdfDir(), { recursive: true })
      pdfPath = path.join(adsPdfDir(), `${invoice.invoiceNo.replace(/[^A-Za-z0-9_-]/g, '_')}.pdf`)
      fs.writeFileSync(pdfPath, opts.pdf)
    } catch (e: any) { console.warn('[AmazonAds] PDF cache write failed:', e?.message || e); pdfPath = null }
  }
  try {
    d.prepare(`INSERT INTO invoices(invoice_no, source, payload, pdf_path, pdf_name, updated_at)
               VALUES(?, ?, ?, ?, ?, datetime('now'))
               ON CONFLICT(invoice_no) DO UPDATE SET
                 source = CASE WHEN excluded.source = 'api' THEN 'api' ELSE invoices.source END,
                 payload = excluded.payload,
                 pdf_path = COALESCE(excluded.pdf_path, invoices.pdf_path),
                 pdf_name = COALESCE(excluded.pdf_name, invoices.pdf_name),
                 updated_at = excluded.updated_at`)
      .run(invoice.invoiceNo, invoice.source, JSON.stringify(invoice), pdfPath, opts.pdfName || invoice.fileName || null)
    return getStoredAdsInvoice(invoice.invoiceNo)
  } catch (e: any) { console.warn('[AmazonAds] store upsert failed:', e?.message || e); return null }
}

export const readStoredAdsPdf = (invoiceNo: string): Buffer | null => {
  const row = getStoredAdsInvoice(invoiceNo)
  if (!row?.pdfPath) return null
  try { return fs.existsSync(row.pdfPath) ? fs.readFileSync(row.pdfPath) : null } catch { return null }
}

export const markAdsInvoiceImported = (invoiceNo: string, cInvoiceId: number, cInvoiceDocNo: string, user: string | null) => {
  const d = getDb(); if (!d) return
  try {
    d.prepare(`UPDATE invoices SET c_invoice_id = ?, c_invoice_docno = ?, imported_at = datetime('now'), imported_by = ?, voided_at = NULL, updated_at = datetime('now') WHERE invoice_no = ?`)
      .run(cInvoiceId, cInvoiceDocNo, user, invoiceNo)
  } catch {}
}

export const markAdsInvoiceVoided = (invoiceNo: string) => {
  const d = getDb(); if (!d) return
  try { d.prepare(`UPDATE invoices SET voided_at = datetime('now'), updated_at = datetime('now') WHERE invoice_no = ?`).run(invoiceNo) } catch {}
}

/** Forget the import link (the c_invoice is gone / voided + freed) so the row is importable again. */
export const clearAdsInvoiceImport = (invoiceNo: string) => {
  const d = getDb(); if (!d) return
  try { d.prepare(`UPDATE invoices SET c_invoice_id = NULL, c_invoice_docno = NULL, imported_at = NULL, imported_by = NULL, updated_at = datetime('now') WHERE invoice_no = ?`).run(invoiceNo) } catch {}
}

export const findStoredByCInvoiceId = (cInvoiceId: number): StoredAdsInvoice | null => {
  const d = getDb(); if (!d) return null
  try { return mapRow(d.prepare('SELECT * FROM invoices WHERE c_invoice_id = ?').get(cInvoiceId)) } catch { return null }
}