/**
 * Frontend-owned export log for the marketplace settlement pages (Amazon
 * settlement periods / eBay payouts) → lexoffice bank-statement CSV.
 * `data/settlement-exports.db`, better-sqlite3 — same lazy/fail-soft pattern
 * as amazonAds/adsStore.ts / legalDocsDb.ts.
 *
 * WHY a per-PERIOD log next to the per-ORDER marker on C_Order
 * (isLexofficeCsvExported): the lexoffice clearing account only reconciles when
 * EVERY line of a settlement went out — sales, refunds, order fees, service
 * fees, Ads invoices, adjustments AND the payout (Geldtransit) line. Only the
 * matched order rows can carry the C_Order marker; fee / payout / unmatched rows
 * have no order to write to, so the bookkeeper could not see whether a period
 * was already imported into lexoffice (and risked importing it twice). This log
 * records, per period, when its positions and when its payout row went out,
 * whether the positions export was complete (every row of the period, not a
 * filtered subset) and whether the combined file netted to 0,00.
 *
 * iDempiere stays the authority for the order-level marker; this store is the
 * period-level ledger plus an append-only audit trail of every file produced.
 */
import Database from 'better-sqlite3'
import path from 'path'
import fs from 'fs'

export type SettlementMarketplace = 'amazon' | 'ebay'
export type SettlementExportVariant = 'combined' | 'payouts' | 'positions'

export interface PeriodExportRecord {
  marketplace: SettlementMarketplace
  orderSourceId: number
  periodId: string
  periodLabel: string
  payoutDate: string
  amount: number | null
  currency: string
  positionsExportedAt: string | null
  positionsComplete: boolean
  positionsRowCount: number
  payoutExportedAt: string | null
  // null = unknown (no complete combined export yet), true = netted to 0,00
  balanced: boolean | null
  lastVariant: string | null
  lastFile: string | null
  lastUser: string | null
  updatedAt: string
}

export interface PeriodExportInput {
  periodId: string
  periodLabel?: string
  payoutDate?: string
  amount?: number | null
  currency?: string
  // What this file contained for the period
  positions: boolean
  complete: boolean // positions = every row of the period (no grid filter applied)
  rowCount?: number
  payout: boolean
  // Net of the period's rows in a combined export (0,00 = balanced); null when n/a
  net?: number | null
}

let db: any = null
let initFailed = false

const dataDir = () => path.join(process.cwd(), 'data')

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(), 'settlement-exports.db'))
    _db.pragma('journal_mode = WAL')
    _db.exec(`
      CREATE TABLE IF NOT EXISTS period_exports (
        marketplace           TEXT NOT NULL,
        order_source_id       INTEGER NOT NULL,
        period_id             TEXT NOT NULL,
        period_label          TEXT NOT NULL DEFAULT '',
        payout_date           TEXT NOT NULL DEFAULT '',
        amount                REAL,
        currency              TEXT NOT NULL DEFAULT '',
        positions_exported_at TEXT,
        positions_complete    INTEGER NOT NULL DEFAULT 0,
        positions_row_count   INTEGER NOT NULL DEFAULT 0,
        payout_exported_at    TEXT,
        balanced              INTEGER,
        last_variant          TEXT,
        last_file             TEXT,
        last_user             TEXT,
        updated_at            TEXT NOT NULL DEFAULT (datetime('now')),
        PRIMARY KEY (marketplace, order_source_id, period_id)
      );
      CREATE TABLE IF NOT EXISTS export_runs (
        id              INTEGER PRIMARY KEY AUTOINCREMENT,
        marketplace     TEXT NOT NULL,
        order_source_id INTEGER NOT NULL,
        variant         TEXT NOT NULL,
        file_name       TEXT,
        period_count    INTEGER NOT NULL DEFAULT 0,
        row_count       INTEGER NOT NULL DEFAULT 0,
        periods         TEXT NOT NULL DEFAULT '[]',
        user            TEXT,
        created_at      TEXT NOT NULL DEFAULT (datetime('now'))
      );
      CREATE INDEX IF NOT EXISTS idx_export_runs_source ON export_runs(marketplace, order_source_id, created_at);
    `)
    db = _db
    return db
  } catch (e: any) {
    initFailed = true
    console.warn('[SettlementExportLog] store unavailable:', e?.message || e)
    return null
  }
}

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

export const isSettlementMarketplace = (v: any): v is SettlementMarketplace => v === 'amazon' || v === 'ebay'

const rowToRecord = (r: any): PeriodExportRecord => ({
  marketplace: r.marketplace,
  orderSourceId: Number(r.order_source_id),
  periodId: String(r.period_id),
  periodLabel: r.period_label || '',
  payoutDate: r.payout_date || '',
  amount: r.amount == null ? null : Number(r.amount),
  currency: r.currency || '',
  positionsExportedAt: r.positions_exported_at || null,
  positionsComplete: !!r.positions_complete,
  positionsRowCount: Number(r.positions_row_count || 0),
  payoutExportedAt: r.payout_exported_at || null,
  balanced: r.balanced == null ? null : !!r.balanced,
  lastVariant: r.last_variant || null,
  lastFile: r.last_file || null,
  lastUser: r.last_user || null,
  updatedAt: r.updated_at
})

/** Every logged period of one order source, keyed by period id. */
export const listPeriodExports = (marketplace: SettlementMarketplace, orderSourceId: number): Record<string, PeriodExportRecord> => {
  const d = getDb()
  if (!d) return {}
  const rows = d.prepare(
    'SELECT * FROM period_exports WHERE marketplace = ? AND order_source_id = ?'
  ).all(marketplace, orderSourceId)
  const out: Record<string, PeriodExportRecord> = {}
  for (const r of rows) out[String(r.period_id)] = rowToRecord(r)
  return out
}

export const getPeriodExports = (marketplace: SettlementMarketplace, orderSourceId: number, periodIds: string[]): Record<string, PeriodExportRecord> => {
  const all = listPeriodExports(marketplace, orderSourceId)
  const out: Record<string, PeriodExportRecord> = {}
  for (const id of periodIds) if (all[id]) out[id] = all[id]
  return out
}

/**
 * Records one produced CSV file: upserts every period it contained and appends
 * an audit row. Positions "complete" never regresses (a later filtered re-export
 * does not un-complete a period whose full file already went out); `balanced`
 * is only (re)written by a complete combined export.
 */
export const recordSettlementExport = (opts: {
  marketplace: SettlementMarketplace
  orderSourceId: number
  variant: SettlementExportVariant
  fileName?: string | null
  rowCount?: number
  user?: string | null
  periods: PeriodExportInput[]
}): Record<string, PeriodExportRecord> => {
  const d = getDb()
  if (!d) return {}
  const now = new Date().toISOString()
  const select = d.prepare('SELECT * FROM period_exports WHERE marketplace = ? AND order_source_id = ? AND period_id = ?')
  const upsert = d.prepare(`
    INSERT INTO period_exports (
      marketplace, order_source_id, period_id, period_label, payout_date, amount, currency,
      positions_exported_at, positions_complete, positions_row_count, payout_exported_at, balanced,
      last_variant, last_file, last_user, updated_at
    ) VALUES (
      @marketplace, @orderSourceId, @periodId, @periodLabel, @payoutDate, @amount, @currency,
      @positionsExportedAt, @positionsComplete, @positionsRowCount, @payoutExportedAt, @balanced,
      @lastVariant, @lastFile, @lastUser, @updatedAt
    )
    ON CONFLICT(marketplace, order_source_id, period_id) DO UPDATE SET
      period_label          = excluded.period_label,
      payout_date           = excluded.payout_date,
      amount                = excluded.amount,
      currency              = excluded.currency,
      positions_exported_at = excluded.positions_exported_at,
      positions_complete    = excluded.positions_complete,
      positions_row_count   = excluded.positions_row_count,
      payout_exported_at    = excluded.payout_exported_at,
      balanced              = excluded.balanced,
      last_variant          = excluded.last_variant,
      last_file             = excluded.last_file,
      last_user             = excluded.last_user,
      updated_at            = excluded.updated_at
  `)
  const insertRun = d.prepare(`
    INSERT INTO export_runs (marketplace, order_source_id, variant, file_name, period_count, row_count, periods, user, created_at)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
  `)

  const tx = d.transaction(() => {
    for (const p of opts.periods) {
      if (!p?.periodId) continue
      const prev = select.get(opts.marketplace, opts.orderSourceId, String(p.periodId))
      const positionsExportedAt = p.positions ? now : (prev?.positions_exported_at || null)
      const positionsComplete = p.positions ? (!!p.complete || !!prev?.positions_complete) : !!prev?.positions_complete
      const positionsRowCount = p.positions ? Number(p.rowCount || 0) : Number(prev?.positions_row_count || 0)
      const payoutExportedAt = p.payout ? now : (prev?.payout_exported_at || null)
      let balanced: number | null = prev?.balanced ?? null
      if (opts.variant === 'combined' && p.positions && p.payout && p.complete && p.net != null && Number.isFinite(Number(p.net))) {
        balanced = Math.abs(Number(p.net)) < 0.005 ? 1 : 0
      }
      upsert.run({
        marketplace: opts.marketplace,
        orderSourceId: opts.orderSourceId,
        periodId: String(p.periodId),
        periodLabel: p.periodLabel || prev?.period_label || '',
        payoutDate: p.payoutDate || prev?.payout_date || '',
        amount: p.amount == null ? (prev?.amount ?? null) : Number(p.amount),
        currency: p.currency || prev?.currency || '',
        positionsExportedAt,
        positionsComplete: positionsComplete ? 1 : 0,
        positionsRowCount,
        payoutExportedAt,
        balanced,
        lastVariant: opts.variant,
        lastFile: opts.fileName || null,
        lastUser: opts.user || null,
        updatedAt: now
      })
    }
    insertRun.run(
      opts.marketplace, opts.orderSourceId, opts.variant, opts.fileName || null,
      opts.periods.length, Number(opts.rowCount || 0),
      JSON.stringify(opts.periods.map(p => ({
        periodId: p.periodId, label: p.periodLabel || '', positions: !!p.positions, complete: !!p.complete,
        rowCount: Number(p.rowCount || 0), payout: !!p.payout, net: p.net ?? null
      }))),
      opts.user || null, now
    )
  })
  tx()
  return getPeriodExports(opts.marketplace, opts.orderSourceId, opts.periods.map(p => String(p.periodId)))
}

/**
 * Clears the export state of the given periods ('all' removes the rows, the
 * other scopes only blank the respective timestamp). Also logged as a run so the
 * audit trail shows who un-marked what.
 */
export const resetSettlementExports = (opts: {
  marketplace: SettlementMarketplace
  orderSourceId: number
  periodIds: string[]
  scope?: 'all' | 'positions' | 'payout'
  user?: string | null
}): Record<string, PeriodExportRecord> => {
  const d = getDb()
  if (!d) return {}
  const ids = [...new Set(opts.periodIds.map(String).filter(Boolean))]
  if (!ids.length) return {}
  const scope = opts.scope || 'all'
  const now = new Date().toISOString()
  const tx = d.transaction(() => {
    for (const id of ids) {
      if (scope === 'all') {
        d.prepare('DELETE FROM period_exports WHERE marketplace = ? AND order_source_id = ? AND period_id = ?')
          .run(opts.marketplace, opts.orderSourceId, id)
      } else if (scope === 'positions') {
        d.prepare(`UPDATE period_exports SET positions_exported_at = NULL, positions_complete = 0, positions_row_count = 0,
                   balanced = NULL, last_user = ?, updated_at = ? WHERE marketplace = ? AND order_source_id = ? AND period_id = ?`)
          .run(opts.user || null, now, opts.marketplace, opts.orderSourceId, id)
      } else {
        d.prepare(`UPDATE period_exports SET payout_exported_at = NULL, balanced = NULL, last_user = ?, updated_at = ?
                   WHERE marketplace = ? AND order_source_id = ? AND period_id = ?`)
          .run(opts.user || null, now, opts.marketplace, opts.orderSourceId, id)
      }
    }
    d.prepare(`INSERT INTO export_runs (marketplace, order_source_id, variant, file_name, period_count, row_count, periods, user, created_at)
               VALUES (?, ?, ?, NULL, ?, 0, ?, ?, ?)`)
      .run(opts.marketplace, opts.orderSourceId, `reset:${scope}`, ids.length, JSON.stringify(ids.map(periodId => ({ periodId }))), opts.user || null, now)
  })
  tx()
  return getPeriodExports(opts.marketplace, opts.orderSourceId, ids)
}

/** Recent audit rows (newest first) for one order source. */
export const listSettlementExportRuns = (marketplace: SettlementMarketplace, orderSourceId: number, limit = 50) => {
  const d = getDb()
  if (!d) return []
  return d.prepare(
    'SELECT * FROM export_runs WHERE marketplace = ? AND order_source_id = ? ORDER BY id DESC LIMIT ?'
  ).all(marketplace, orderSourceId, limit).map((r: any) => ({
    id: r.id,
    variant: r.variant,
    fileName: r.file_name,
    periodCount: r.period_count,
    rowCount: r.row_count,
    periods: (() => { try { return JSON.parse(r.periods || '[]') } catch { return [] } })(),
    user: r.user,
    createdAt: r.created_at
  }))
}