/**
 * Storage for the Amazon invoices page (uploaded / seen invoices) — ONE async API,
 * two backends:
 *
 *   1. iDempiere table CUST_AmazonInvoiceInbox (preferred — nothing lives on the app
 *      server: the row is in Postgres via REST, the PDF in Strapi attached to the row).
 *      Columns used: AmazonInvoiceNo, Name (= the number, identifier), InvoiceKind
 *      (ads | fee | credit), InboxStatus (new | imported | voided | error), DateInvoiced,
 *      GrandTotal, ExtractedJson (the normalised AdsInvoice), FileName, ByteSize,
 *      StrapiAttachmentId, StrapiFileUrl, C_Invoice_ID, ErrorMsg, Description.
 *   2. the frontend-owned SQLite file (adsStore.ts) — fallback when the table does not
 *      exist / is not reachable (other instances, a dev box without the table). It was the
 *      only store until 2026-09-18, when a deploy wiped it (it was missing from the deploy
 *      script's preserve list) and booked invoices vanished from the page.
 *
 * Booked invoices are listed from iDempiere's documents anyway (invoices.get.ts) — this
 * store matters for uploads that are NOT imported yet, plus the parsed details.
 *
 * Rows still sitting in the SQLite file are moved over by migrateSqliteRows() the first
 * time the page loads with the table available (bounded per call, fail-soft, idempotent).
 * Ads API connection settings stay in adsStore (the Ads API is not connected yet).
 *
 * Write casing follows the iDempiere REST rule: lowercase-first scalars, PascalCase FK
 * objects { id, tableName }; an FK is cleared by sending 0. Reads accept both casings.
 */
import { string } from 'alga-js'
import fetchHelper from '../fetchHelper'
import { attachToStrapi, fetchStrapiFileBytes } from '../inbox/strapiAttach'
import type { AdsInvoice } from './types'
import {
  type StoredAdsInvoice, listStoredAdsInvoices, getStoredAdsInvoice, upsertStoredAdsInvoice, readStoredAdsPdf,
  markAdsInvoiceImported, markAdsInvoiceVoided, clearAdsInvoiceImport, findStoredByCInvoiceId
} from './adsStore'

const MODEL = 'cust_amazoninvoiceinbox'
const TABLE = 'CUST_AmazonInvoiceInbox'
const enc = (s: string) => string.urlEncode(s)
const esc = (s: string) => String(s || '').replace(/'/g, "''")
const fkId = (v: any) => (v && typeof v === 'object') ? (v.id ?? null) : (v ?? null)
const safeParse = (s: any) => { if (s == null) return null; if (typeof s === 'object') return s; try { return JSON.parse(s) } catch { return null } }

export interface StoredInvoice extends StoredAdsInvoice {
  backend: 'idempiere' | 'sqlite'
  inboxId?: number | null
  inboxUid?: string | null
  strapiFileUrl?: string | null
  status?: string | null
  errorMsg?: string | null
}

/* ------------------------------ backend probe ------------------------------ */
let probe: { ok: boolean; at: number; error?: string } | null = null
export const inboxTableAvailable = async (event: any, token: string, force = false): Promise<boolean> => {
  if (!force && probe && Date.now() - probe.at < (probe.ok ? 5 * 60 * 1000 : 60 * 1000)) return probe.ok
  try {
    const res: any = await fetchHelper(event, `models/${MODEL}?$top=1&$select=AmazonInvoiceNo`, 'GET', token, null)
    probe = { ok: Array.isArray(res?.records), at: Date.now() }
  } catch (e: any) {
    // 401/403 = the caller's token problem, not a missing table → let the route's refresh logic handle it
    const st = Number(e?.status || e?.statusCode || 0)
    if (st === 401 || st === 403) throw e
    probe = { ok: false, at: Date.now(), error: e?.data?.detail || e?.message || String(e) }
  }
  return probe.ok
}
export const inboxBackendInfo = () => ({ backend: probe?.ok ? 'idempiere' : 'sqlite', error: probe?.error || null })

/* ------------------------------ mapping ------------------------------ */
const kindOf = (inv: AdsInvoice) => inv.kind === 'fee' ? (inv.isCreditNote ? 'credit' : 'fee') : 'ads'

const mapRow = (r: any): StoredInvoice | null => {
  if (!r?.id) return null
  const invoice: any = safeParse(r.ExtractedJson ?? r.extractedJson)
  const no = String(r.AmazonInvoiceNo ?? r.amazonInvoiceNo ?? '')
  if (!invoice || !no) return null
  const fileUrl = r.StrapiFileUrl ?? r.strapiFileUrl ?? null
  const status = String(r.InboxStatus ?? r.inboxStatus ?? 'new')
  const cInvoiceId = Number(fkId(r.C_Invoice_ID)) || null
  return {
    backend: 'idempiere', inboxId: r.id, inboxUid: r.uid || null, status, errorMsg: r.ErrorMsg ?? r.errorMsg ?? null,
    invoiceNo: no, source: invoice.source === 'api' ? 'api' : 'pdf', invoice,
    // truthy = "a PDF is stored" for the callers that only test it (the list's pdfCached flag)
    pdfPath: fileUrl ? `strapi:${fileUrl}` : null, strapiFileUrl: fileUrl,
    pdfName: r.FileName ?? r.fileName ?? invoice.fileName ?? null,
    cInvoiceId, cInvoiceDocNo: cInvoiceId ? no : null,
    importedAt: status === 'imported' ? (r.Updated || null) : null,
    importedBy: status === 'imported' ? (r.UpdatedBy?.identifier || null) : null,
    voidedAt: status === 'voided' ? (r.Updated || null) : null,
    updatedAt: r.Updated || null
  }
}
const fromSqlite = (r: StoredAdsInvoice | null): StoredInvoice | null => r ? ({ ...r, backend: 'sqlite' }) : null

/* ------------------------------ reads ------------------------------ */
export const listInvoices = async (event: any, token: string, opts: { from?: string | null; to?: string | null } = {}): Promise<StoredInvoice[]> => {
  if (!(await inboxTableAvailable(event, token))) return listStoredAdsInvoices(opts).map(r => fromSqlite(r)!)
  const res: any = await fetchHelper(event, `models/${MODEL}?$filter=${enc('IsActive eq true')}&$orderby=${enc('DateInvoiced desc')}&$top=2000`, 'GET', token, null)
  const rows: StoredInvoice[] = (res?.records || []).map(mapRow).filter((r: any): r is StoredInvoice => !!r)
  // rows that are still ONLY in the SQLite file (migration pending or failing) must not vanish from the page
  const have = new Set(rows.map(r => r.invoiceNo))
  for (const l of listStoredAdsInvoices({})) if (l.invoice && !have.has(l.invoiceNo)) rows.push(fromSqlite(l)!)
  // date range applied here (like the SQLite store did) — no dependency on the REST date-literal format
  return rows.filter((r: StoredInvoice) => {
    const d = String(r.invoice?.invoiceDate || '')
    if (opts.from && d && d < opts.from) return false
    if (opts.to && d && d > opts.to) return false
    return true
  })
}

export const getInvoice = async (event: any, token: string, invoiceNo: string): Promise<StoredInvoice | null> => {
  if (!(await inboxTableAvailable(event, token))) return fromSqlite(getStoredAdsInvoice(invoiceNo))
  const res: any = await fetchHelper(event, `models/${MODEL}?$filter=${enc(`AmazonInvoiceNo eq '${esc(invoiceNo)}' AND IsActive eq true`)}&$orderby=${enc('Created desc')}&$top=1`, 'GET', token, null)
  // not in the table (yet) → the SQLite row, so a not-yet-migrated upload can still be opened / imported
  return mapRow(res?.records?.[0]) || fromSqlite(getStoredAdsInvoice(invoiceNo))
}

export const findByCInvoiceId = async (event: any, token: string, cInvoiceId: number): Promise<StoredInvoice | null> => {
  if (!(await inboxTableAvailable(event, token))) return fromSqlite(findStoredByCInvoiceId(cInvoiceId))
  const res: any = await fetchHelper(event, `models/${MODEL}?$filter=${enc(`C_Invoice_ID eq ${Number(cInvoiceId)} AND IsActive eq true`)}&$top=1`, 'GET', token, null)
  return mapRow(res?.records?.[0])
}

export const readInvoicePdf = async (event: any, token: string, row: StoredInvoice | null): Promise<Buffer | null> => {
  if (!row) return null
  if (row.backend === 'sqlite') return readStoredAdsPdf(row.invoiceNo)
  return row.strapiFileUrl ? await fetchStrapiFileBytes(event, row.strapiFileUrl) : null
}

/* ------------------------------ writes ------------------------------ */
/**
 * Insert or refresh an invoice. Import bookkeeping (status, C_Invoice_ID) of an existing row is
 * kept; a PDF is attached once (Strapi, linked to the inbox row) and its url stored on the row.
 */
export const upsertInvoice = async (event: any, token: string, organizationId: number, invoice: AdsInvoice,
  opts: { pdf?: Buffer | null; pdfName?: string | null } = {}): Promise<StoredInvoice | null> => {
  if (!(await inboxTableAvailable(event, token))) return fromSqlite(upsertStoredAdsInvoice(invoice, opts))
  const found = await getInvoice(event, token, invoice.invoiceNo)
  const existing = found?.backend === 'idempiere' ? found : null
  const fields: any = {
    invoiceKind: kindOf(invoice),
    ...(invoice.invoiceDate ? { dateInvoiced: invoice.invoiceDate } : {}),
    grandTotal: Math.round((Number(invoice.gross) || 0) * 100) / 100,
    extractedJson: JSON.stringify(invoice),
    ...(opts.pdfName || invoice.fileName ? { fileName: String(opts.pdfName || invoice.fileName).slice(0, 255) } : {}),
    ...(opts.pdf?.length ? { byteSize: opts.pdf.length } : {})
  }
  let id = existing?.inboxId || null
  let uid = existing?.inboxUid || null
  if (id) {
    await fetchHelper(event, `models/${MODEL}/${id}`, 'PUT', token, fields)
  } else {
    const created: any = await fetchHelper(event, `models/${MODEL}`, 'POST', token, {
      AD_Org_ID: { id: organizationId, tableName: 'AD_Org' },
      isActive: true,
      amazonInvoiceNo: invoice.invoiceNo.slice(0, 60),
      name: invoice.invoiceNo.slice(0, 60),
      inboxStatus: 'new',
      ...fields,
      tableName: TABLE
    })
    id = Number(created?.id) || null
    uid = created?.uid || null
    if (!id) throw new Error(`Eintrag für ${invoice.invoiceNo} konnte nicht angelegt werden`)
  }
  // PDF → Strapi (once; a re-upload of the same invoice keeps the first file)
  if (opts.pdf?.length && !existing?.strapiFileUrl) {
    const att = await attachToStrapi(event, token, { tableName: TABLE, recordId: id, recordUu: uid || undefined, buffer: opts.pdf, filename: opts.pdfName || invoice.fileName || `${invoice.invoiceNo}.pdf`, mimeType: 'application/pdf' })
    await fetchHelper(event, `models/${MODEL}/${id}`, 'PUT', token, { strapiFileUrl: att.fileUrl || '', ...(att.strapiAttachmentId != null ? { strapiAttachmentId: att.strapiAttachmentId } : {}) })
  }
  return await getInvoice(event, token, invoice.invoiceNo)
}

export const markImported = async (event: any, token: string, row: StoredInvoice | null, invoiceNo: string, cInvoiceId: number, cInvoiceDocNo: string, user: string | null) => {
  if (!row || row.backend === 'sqlite') return markAdsInvoiceImported(invoiceNo, cInvoiceId, cInvoiceDocNo, user)
  await fetchHelper(event, `models/${MODEL}/${row.inboxId}`, 'PUT', token, { inboxStatus: 'imported', errorMsg: '', C_Invoice_ID: { id: cInvoiceId, tableName: 'C_Invoice' } })
}

export const markImportError = async (event: any, token: string, row: StoredInvoice | null, message: string) => {
  if (!row || row.backend === 'sqlite' || !row.inboxId) return
  try { await fetchHelper(event, `models/${MODEL}/${row.inboxId}`, 'PUT', token, { errorMsg: String(message || '').slice(0, 500) }) } catch {}
}

/** The created document was voided: forget the link so the invoice can be imported again. */
export const markVoided = async (event: any, token: string, row: StoredInvoice | null, invoiceNo: string) => {
  if (!row || row.backend === 'sqlite') { markAdsInvoiceVoided(invoiceNo); clearAdsInvoiceImport(invoiceNo); return }
  await fetchHelper(event, `models/${MODEL}/${row.inboxId}`, 'PUT', token, { inboxStatus: 'voided', C_Invoice_ID: 0 })
}

/* ------------------------------ one-time migration ------------------------------ */
/**
 * Move rows that still live in the SQLite file into the iDempiere table. Idempotent (a row that
 * already exists there is skipped), bounded per call, fail-soft per row. For an IMPORTED row the
 * PDF is NOT copied — the original is already attached to its c_invoice document and is served
 * from there; for a pending row the PDF goes to Strapi so nothing depends on the local file.
 */
let migrationPausedUntil = 0
export const migrateSqliteRows = async (event: any, token: string, organizationId: number, max = 25): Promise<{ migrated: number; failed: string[]; pending: number; paused?: boolean }> => {
  const out: { migrated: number; failed: string[]; pending: number; paused?: boolean } = { migrated: 0, failed: [], pending: 0 }
  // a run where NOTHING could be written (e.g. a mandatory column the app does not know) is not
  // retried on every page load — next attempt in 10 minutes
  if (Date.now() < migrationPausedUntil) { out.paused = true; return out }
  if (!(await inboxTableAvailable(event, token))) return out
  const local = listStoredAdsInvoices({})
  if (!local.length) return out
  const res: any = await fetchHelper(event, `models/${MODEL}?$filter=${enc('IsActive eq true')}&$select=AmazonInvoiceNo&$top=2000`, 'GET', token, null)
  const have = new Set((res?.records || []).map((r: any) => String(r.AmazonInvoiceNo ?? r.amazonInvoiceNo ?? '')))
  const todo = local.filter(r => r.invoice && !have.has(r.invoiceNo))
  out.pending = Math.max(0, todo.length - max)
  for (const r of todo.slice(0, max)) {
    try {
      const pdf = r.cInvoiceId ? null : readStoredAdsPdf(r.invoiceNo)
      const row = await upsertInvoice(event, token, organizationId, r.invoice, { pdf, pdfName: r.pdfName })
      if (row && r.cInvoiceId) await markImported(event, token, row, r.invoiceNo, r.cInvoiceId, r.cInvoiceDocNo || r.invoiceNo, r.importedBy)
      else if (row && r.voidedAt) await fetchHelper(event, `models/${MODEL}/${row.inboxId}`, 'PUT', token, { inboxStatus: 'voided' })
      out.migrated++
    } catch (e: any) {
      out.failed.push(`${r.invoiceNo}: ${String(e?.data?.detail || e?.message || e).split('\n')[0].slice(0, 220)}`)
      if (!out.migrated) break   // the first row already fails → the rest would fail the same way
    }
  }
  if (out.failed.length && !out.migrated) migrationPausedUntil = Date.now() + 10 * 60 * 1000
  return out
}