/**
 * GET /api/accounting/amazon-ads/invoices?from=YYYY-MM-DD&to=YYYY-MM-DD&refresh=1
 * The selectable list for the page. Sources, merged by Amazon invoice number:
 *   1. the Amazon Ads API (paid invoices in the date range) when connected —
 *      summaries are cached in the store so a later import has the payload;
 *   2. the local store (uploaded PDFs + everything seen before).
 * Each row is annotated with iDempiere's truth: the fixed Amazon vendor's AP
 * invoices are loaded ONCE (DocumentNo → id/DocStatus/Lexoffice flag) and
 * matched by number (a "<no>-VOID-<id>" rename counts as voided history, not as
 * booked), so the page never has to guess from the local cache.
 * SOURCE OF TRUTH = iDempiere. Every Amazon invoice that HAS a document is listed from
 * iDempiere itself (the AP documents of the Amazon vendors, identified by POReference =
 * Amazon invoice number; PDF = the document's attachment), whether or not the local store
 * knows it. The store (data/amazon-ads.db — a frontend-owned file that a deploy can lose)
 * only contributes what iDempiere cannot know yet: uploaded PDFs that are NOT imported, plus
 * richer parsed details (campaign lines, payer) and the PDF cache for the rest. A wiped
 * store therefore never hides booked invoices — it only drops pending uploads.
 *
 * Seller FEE invoices (kind 'fee') are issued by other Amazon entities: their
 * vendors are looked up by the issuers' VAT ids and included in the same check
 * (vendorId/vendorMissing on the row tell the page whether the import will
 * create the partner).
 */
import { string } from 'alga-js'
import { withRefresh, orgIdOf } from '../../../utils/amazonAds/routeShared'
import fetchHelper from '../../../utils/fetchHelper'
import getTokenHelper from '../../../utils/getTokenHelper'
import { getAdsConfig, isAdsApiConnected, listAdsInvoiceSummaries, mapAdsApiInvoice } from '../../../utils/amazonAds/adsApi'
import { listInvoices, upsertInvoice, migrateSqliteRows, inboxBackendInfo, markImported, markVoided } from '../../../utils/amazonAds/invoiceStore'
import { PAYMENT_METHOD_LABELS } from '../../../utils/amazonAds/types'
import { findFeeVendorId } from '../../../utils/amazonAds/importInvoice'

// Amazon vendor partners, found WITHOUT the local store (vendor flag + name) — cached 10 min
let amazonVendorCache: { ids: number[]; at: number } | null = null
const findAmazonVendorIds = async (event: any, token: string): Promise<number[]> => {
  if (amazonVendorCache && Date.now() - amazonVendorCache.at < 10 * 60 * 1000) return amazonVendorCache.ids
  const res: any = await fetchHelper(event, `models/c_bpartner?$filter=${string.urlEncode("IsVendor eq true AND IsActive eq true AND contains(Name,'Amazon')")}&$select=Name&$top=50`, 'GET', token, null)
  const ids = (res?.records || []).map((r: any) => Number(r.id)).filter(Boolean)
  amazonVendorCache = { ids, at: Date.now() }
  return ids
}

const deDate = (v?: string) => { const m = String(v || '').match(/(\d{2})\.(\d{2})\.(\d{4})/); return m ? `${m[3]}-${m[2]}-${m[1]}` : null }

export default withRefresh(async (event, authToken = null) => {
  const token = authToken ?? await getTokenHelper(event)
  const q = getQuery(event)
  const from = q.from ? String(q.from).slice(0, 10) : null
  const to = q.to ? String(q.to).slice(0, 10) : null
  const cfg = getAdsConfig(event)
  const notices: string[] = []

  // 1. API
  let apiCount = 0
  if (isAdsApiConnected(event) && String(q.source || 'all') !== 'store') {
    if (!cfg.profileId) notices.push('Amazon Ads ist verbunden, aber noch kein Werbeprofil (Marktplatz) gewählt.')
    else {
      try {
        // the page has no date filter by default — the Amazon Ads API call still gets a bounded
        // window (last 12 months) so "show all" never turns into an unbounded remote query;
        // everything seen earlier stays in the local store and is listed regardless
        const now = new Date()
        const apiFrom = from || new Date(now.getFullYear(), now.getMonth() - 12, 1).toISOString().slice(0, 10)
        const apiTo = to || now.toISOString().slice(0, 10)
        const summaries = await listAdsInvoiceSummaries(event, { statuses: ['PAID_IN_FULL'], startDate: apiFrom, endDate: apiTo })
        for (const s of summaries) {
          const inv = mapAdsApiInvoice(s, null, null)
          await upsertInvoice(event, token, orgIdOf(event), inv)
          apiCount++
        }
      } catch (e: any) { notices.push(`Amazon Ads API: ${e?.message || e}`) }
    }
  }

  // 2. store — iDempiere table CUST_AmazonInvoiceInbox (SQLite file only as fallback). Rows still in
  //    the SQLite file are moved over first (one-time, bounded, fail-soft).
  let migration: any = null
  if (orgIdOf(event)) {
    try {
      migration = await migrateSqliteRows(event, token, orgIdOf(event))
      if (migration.migrated) notices.push(`${migration.migrated} Eintrag/Einträge aus der lokalen Datei in die iDempiere-Tabelle übernommen${migration.pending ? ` (${migration.pending} folgen beim nächsten Laden)` : ''}.`)
      if (migration.failed?.length) notices.push(`Übernahme in iDempiere fehlgeschlagen für: ${migration.failed.slice(0, 3).join(' | ')}`)
    } catch (e: any) { notices.push(`Übernahme der lokalen Einträge nicht möglich: ${e?.data?.detail || e?.message || e}`) }
  }
  const stored = await listInvoices(event, token, { from, to })

  // 3. iDempiere truth for the vendor(s): the fixed Ads vendor + one per fee-invoice issuer VAT id
  const feeVendors = new Map<string, number | null>()
  for (const r of stored) {
    if (r.invoice?.kind !== 'fee') continue
    const vat = String(r.invoice.issuer?.vatId || '').replace(/[^A-Za-z0-9]/g, '').toUpperCase()
    if (!vat || feeVendors.has(vat)) continue
    try { feeVendors.set(vat, await findFeeVendorId(event, token, vat)) }
    catch (e: any) { feeVendors.set(vat, null); notices.push(`Lieferant zu ${vat} konnte nicht geprüft werden: ${e?.message || e}`) }
  }
  let namedVendors: number[] = []
  try { namedVendors = await findAmazonVendorIds(event, token) }
  catch (e: any) { notices.push(`Amazon-Lieferanten konnten nicht ermittelt werden: ${e?.message || e}`) }
  const vendorIds = [...new Set([cfg.vendorBpartnerId, ...namedVendors, ...[...feeVendors.values()].filter((v): v is number => !!v)])]
  const booked = new Map<string, any>()
  try {
    const res: any = await fetchHelper(event,
      `models/c_invoice?$filter=${string.urlEncode(`(${vendorIds.map(id => `C_BPartner_ID eq ${id}`).join(' OR ')}) AND IsSOTrx eq false`)}` +
      `&$select=DocumentNo,POReference,DocStatus,GrandTotal,TotalLines,Description,DateInvoiced,IsPaid,isUploadToLexoffice,UploadDateLexoffice,AD_Org_ID,C_BPartner_ID,C_DocTypeTarget_ID,C_Currency_ID,DateAcct&$orderby=C_Invoice_ID desc&$top=2000`, 'GET', token, null)
    for (const r of (res?.records || [])) {
      const docNo = String(r.DocumentNo || '')
      // Match key = the Amazon invoice number. Neither number field is reliable on its own:
      //   - DocumentNo of a voided document is renamed '<no>-VOID-<id>' and TRUNCATED to 30 chars
      //     ('DE-AEU-2025-2978769' → 'DE-AEU-2025-29787-VOID-1000449'); iDempiere's reversal document gets
      //     an auto number ('1000006');
      //   - POReference is only 20 chars long — a credit-note number ('DE-CN-AEU-2026-134720', 21 chars) is
      //     cut to 'DE-CN-AEU-2026-13472', and the cut value is even ambiguous (…134720 … …134729).
      // The DESCRIPTION the importer writes starts with the FULL number on every document — the active one,
      // the voided one and the reversal (iDempiere copies it) — so that is the primary key; the number
      // fields are the fallback for documents that were not created by the importer.
      const fromDescription = String(r.Description || '').match(/^Amazon (?:Steuergutschrift|Gebührenrechnung|Ads Rechnung)\s+([A-Z0-9][A-Z0-9/_-]{4,})/)?.[1] || ''
      const poRef = String(r.POReference || '').trim()
      const strippedDocNo = docNo.replace(/-VOID-\d+$/, '').replace(/\^+$/, '')
      const renamedOrReversal = /-VOID-\d+$/.test(docNo) || (['VO', 'RE'].includes(r.DocStatus?.id || r.DocStatus || '') && !!poRef && !docNo.startsWith(poRef))
      const base = fromDescription || (renamedOrReversal ? (poRef || strippedDocNo) : strippedDocNo)
      const st = r.DocStatus?.id || r.DocStatus || ''
      // iDempiere's reversal document: reversed status but neither our -VOID- rename nor the Amazon number as DocumentNo
      const isReversal = ['VO', 'RE'].includes(st) && !/-VOID-\d+$/.test(docNo) && docNo !== base && !docNo.startsWith(base)
      const entry = { id: r.id, documentNo: docNo, docStatus: st, isReversal, grandTotal: r.GrandTotal, dateInvoiced: r.DateInvoiced, isPaid: r.IsPaid === true || r.IsPaid === 'Y',
        lexofficeUploaded: r.isUploadToLexoffice === true || r.isUploadToLexoffice === 'Y', lexofficeDate: r.UploadDateLexoffice || null, orgId: r.AD_Org_ID?.id ?? null, orgName: r.AD_Org_ID?.identifier ?? null,
        vendorName: r.C_BPartner_ID?.identifier ?? null, dateAcct: r.DateAcct || null,
        vendorId: r.C_BPartner_ID?.id ?? null, totalLines: r.TotalLines, description: r.Description || '', hasPoReference: !!fromDescription || !!poRef,
        isCreditMemo: /credit/i.test(r.C_DocTypeTarget_ID?.identifier || ''), currency: (String(r.C_Currency_ID?.identifier || '').match(/[A-Z]{3}/)?.[0]) || 'EUR' }
      const prev = booked.get(base)
      // an active booking wins over voided history
      const active = !['VO', 'RE'].includes(st) && !/-VOID-\d+$/.test(docNo) && !/\^$/.test(docNo)
      if (!prev || (active && !prev.active)) booked.set(base, { ...entry, active, history: prev ? [prev, ...(prev.history || [])] : [] })
      else prev.history = [...(prev.history || []), entry]
    }
  } catch (e: any) { notices.push(`Verbuchte Rechnungen konnten nicht geladen werden: ${e?.message || e}`) }

  // 4. documents the local store does not know (wiped store, imported from another host, …):
  //    rebuild the row from the iDempiere document. Only importer-created documents (POReference set).
  const known = new Set(stored.map(r => r.invoice?.invoiceNo))
  const fromIdempiere: any[] = []
  for (const [key, b] of booked) {
    if (known.has(key)) continue
    const docs = [b, ...(b.history || [])]
    const doc = b.active ? b : (docs.find((e: any) => !e.isReversal) || b)
    if (!doc?.hasPoReference) continue
    const date = String(doc.dateInvoiced || '').slice(0, 10)
    if ((from && date && date < from) || (to && date && date > to)) continue
    const sign = doc.isCreditMemo ? -1 : 1
    const net = Number(doc.totalLines) || 0, gross = Number(doc.grandTotal) || 0
    const per = String(doc.description || '').match(/Zeitraum\s+(\d{2}\.\d{2}\.\d{4})(?:\s*[–-]\s*(\d{2}\.\d{2}\.\d{4}))?/)
    fromIdempiere.push({
      invoiceNo: key, source: 'idempiere', pdfPath: null, pdfName: null, importedAt: null, importedBy: null, voidedAt: null,
      invoice: {
        kind: Number(doc.vendorId) === Number(cfg.vendorBpartnerId) ? 'ads' : 'fee',
        isCreditNote: !!doc.isCreditMemo,
        originalInvoiceNo: String(doc.description || '').match(/zu Rechnung\s+([A-Z0-9][A-Z0-9/_-]{5,})/)?.[1] || null,
        invoiceNo: key, invoiceDate: date, periodFrom: deDate(per?.[1]), periodTo: deDate(per?.[2]) || deDate(per?.[1]),
        currency: doc.currency || 'EUR',
        net: Math.round(sign * net * 100) / 100, tax: Math.round(sign * (gross - net) * 100) / 100, gross: Math.round(sign * gross * 100) / 100,
        taxRate: net ? Math.round(((gross - net) / net) * 1000) / 10 : null,
        paymentMethod: /Seller Payable/i.test(doc.description || '') ? 'DEDUCT_FROM_PAYMENT' : null,
        status: 'PAID_IN_FULL', countryCode: null,
        issuer: { name: doc.vendorName || undefined }, payer: null,
        lines: [], source: 'idempiere', documentAvailable: true, fileName: `${key}.pdf`
      }
    })
  }
  fromIdempiere.sort((x, y) => String(y.invoice.invoiceDate).localeCompare(String(x.invoice.invoiceDate)))

  // 5. keep the inbox rows consistent with iDempiere's truth (self-heal, bounded, fail-soft): a row whose
  //    document exists but is not linked (imported on another host / before the table existed) gets
  //    status 'imported' + the link; a row that still points to a document that is voided or gone is
  //    released ('voided', link cleared). The page itself never depends on this — it reads the documents.
  const heal: (() => Promise<any>)[] = []
  for (const r of stored as any[]) {
    if (r.backend !== 'idempiere' || !r.inboxId) continue
    const b = booked.get(r.invoiceNo)
    const active = b && b.active ? b : null
    if (active && (Number(r.cInvoiceId) !== Number(active.id) || r.status !== 'imported')) heal.push(() => markImported(event, token, r, r.invoiceNo, active.id, r.invoiceNo, null))
    else if (!active && (r.cInvoiceId || r.status === 'imported')) heal.push(() => markVoided(event, token, r, r.invoiceNo))
  }
  let healed = 0
  for (const fn of heal.slice(0, 25)) { try { await fn(); healed++ } catch (e: any) { console.warn('[AmazonAds] inbox self-heal failed:', e?.data?.detail || e?.message || e) } }

  const rows = [...stored, ...fromIdempiere].map(r => {
    const inv = r.invoice
    const b = booked.get(inv.invoiceNo)
    const isBooked = !!(b && b.active)
    // voided / reversed documents of this Amazon invoice (newest first): the entry itself when it is not
    // active, plus everything in its history — so the page can name and open the Storno document(s)
    const voidedDocs = b ? [b, ...(b.history || [])].filter((e: any) => e && (e.active === false || ['VO', 'RE'].includes(e.docStatus) || /-VOID-\d+$/.test(e.documentNo || '')))
      .map((e: any) => ({ id: e.id, documentNo: e.documentNo, docStatus: e.docStatus, dateInvoiced: e.dateInvoiced, grandTotal: e.grandTotal, orgName: e.orgName, isReversal: !!e.isReversal }))
      // the ORIGINAL voided document first (newest), reversal documents last
      .sort((x: any, y: any) => (Number(x.isReversal) - Number(y.isReversal)) || (y.id - x.id)) : []
    const isFee = inv.kind === 'fee'
    const feeVat = isFee ? String(inv.issuer?.vatId || '').replace(/[^A-Za-z0-9]/g, '').toUpperCase() : ''
    const feeVendorId = isFee ? (feeVendors.get(feeVat) ?? null) : null
    return {
      ...inv,
      kind: inv.kind || 'ads',
      issuerName: inv.issuer?.name || (isFee ? null : 'Amazon Online Germany GmbH'),
      payerName: inv.payer?.name || null,
      vendorId: isFee ? feeVendorId : cfg.vendorBpartnerId,
      vendorMissing: isFee && !feeVendorId,
      paymentMethodLabel: inv.paymentMethodLabel || (inv.paymentMethod ? (PAYMENT_METHOD_LABELS[inv.paymentMethod] || inv.paymentMethod) : null),
      lineCount: inv.lines?.length || 0,
      // a booked invoice always has its original PDF on the iDempiere document (attachment) — also when
      // the inbox row carries no file of its own (rows migrated from the local file, rows rebuilt from documents)
      hasPdf: !!r.pdfPath || (inv.source === 'api' && inv.documentAvailable !== false) || inv.source === 'idempiere' || isBooked,
      pdfOnDocument: !r.pdfPath && (isBooked || inv.source === 'idempiere'),
      fromIdempiere: inv.source === 'idempiere',
      pdfCached: !!r.pdfPath,
      cInvoiceId: isBooked ? b.id : null,
      cInvoiceDocStatus: isBooked ? b.docStatus : null,
      cInvoiceOrg: isBooked ? b.orgName : null,
      cInvoiceVendor: isBooked ? b.vendorName : null,
      cInvoiceDateAcct: isBooked ? (b.dateAcct || b.dateInvoiced) : null,
      cInvoicePaid: isBooked ? b.isPaid : false,
      lexofficeUploaded: isBooked ? b.lexofficeUploaded : false,
      lexofficeDate: isBooked ? b.lexofficeDate : null,
      voidedBefore: !!(b && !b.active) || !!r.voidedAt || voidedDocs.length > 0,
      voidedDocs,
      voidedInvoiceId: voidedDocs[0]?.id ?? null,
      // a row rebuilt from iDempiere has no parsed payload / PDF in the store → re-import needs a new upload
      importable: !isBooked && inv.source !== 'idempiere',
      needsUpload: !isBooked && inv.source === 'idempiere',
      importedAt: r.importedAt, importedBy: r.importedBy
    }
  })

  return { status: 200, rows, apiCount, healed, storeBackend: inboxBackendInfo().backend, storeError: inboxBackendInfo().error, connected: isAdsApiConnected(event), profileId: cfg.profileId, vendorBpartnerId: cfg.vendorBpartnerId, organizationId: orgIdOf(event), notices }
})