/**
 * Print spool + Print-Agent registry (instance-local SQLite, data/print-spool.db).
 *
 * Everything agent-related lives here and NOT in iDempiere, so a rented instance
 * needs no Application-Dictionary change:
 *   print_agents            enrolled LogShip Print Agents (tenant-LAN boxes), token hash,
 *                           last heartbeat, reported CUPS inventory + queue depth + health
 *   print_enroll_codes      one-time enrolment codes created in Settings → Printers
 *   print_printer_bindings  CUST_Printer_ID → agent (per printer, never per org/tenant);
 *                           cups_name/org_id are denormalised so the hot path
 *                           (printDispatch) resolves the agent with ONE SQLite read
 *   print_jobs              the spool: queued → sent → spooled → completed | failed
 *   print_agent_commands    update / diagnostics / support-tunnel commands for an agent
 *
 * better-sqlite3 (sync, WAL) exactly like pushDb.ts / chatDb.ts — shared by all PM2
 * workers through the file; the long-poll in print-agent/jobs/next.get.ts re-reads it
 * every 200 ms, which is how a job inserted by worker A reaches an agent held by worker B.
 */

import Database from 'better-sqlite3'
import path from 'node:path'
import fs from 'node:fs'
import { createHash, randomBytes, randomUUID } from 'node:crypto'

export type PrintJobStatus = 'queued' | 'sent' | 'spooled' | 'completed' | 'failed' | 'cancelled'

export interface PrintAgentRow {
  id: number
  client_id: number
  org_id: number
  name: string
  created_by: number | null
  created_at: string
  last_seen: string | null
  version: string | null
  hostname: string | null
  platform: string | null
  printers_json: string | null
  queue_depth_json: string | null
  health_json: string | null
  revoked_at: string | null
  support_until: string | null
  /** per-customer remote-access switches (1 = allowed, default) */
  allow_cups_relay: number
  allow_support: number
}

export interface PrintBindingRow {
  cust_printer_id: number
  agent_id: number
  cups_name: string
  org_id: number
  created_at: string
  updated_at: string
}

export interface PrintJobRow {
  id: number
  batch_id: string | null
  agent_id: number
  queue: string
  options: string
  file_path: string
  file_name: string
  file_size: number
  source: string
  status: PrintJobStatus
  cups_job_id: string | null
  attempts: number
  lp_output: string | null
  error: string | null
  created_by_user: number | null
  created_by_role: number | null
  created_at: string
  sent_at: string | null
  spooled_at: string | null
  completed_at: string | null
  cancelled_at: string | null
  updated_at: string
}

export interface AgentPrinterInfo {
  queue: string
  state?: string
  accepting?: boolean
  deviceUri?: string
  makeModel?: string
  media?: { w: number; h: number } | null
  jobs?: number
}

const DB_KEY = Symbol.for('logship.printSpoolDb')
const g = globalThis as any

const open = () => {
  const dbPath = path.join(process.cwd(), 'data', 'print-spool.db')
  const dataDir = path.dirname(dbPath)
  if (!fs.existsSync(dataDir)) fs.mkdirSync(dataDir, { recursive: true })
  const db = new Database(dbPath)
  db.pragma('journal_mode = WAL')
  db.pragma('busy_timeout = 5000')
  db.pragma('synchronous = NORMAL')
  db.exec(`
    CREATE TABLE IF NOT EXISTS print_agents (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      client_id INTEGER NOT NULL,
      org_id INTEGER NOT NULL DEFAULT 0,
      name TEXT NOT NULL,
      token_hash TEXT UNIQUE NOT NULL,
      created_by INTEGER,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      last_seen DATETIME,
      version TEXT,
      hostname TEXT,
      platform TEXT,
      printers_json TEXT,
      queue_depth_json TEXT,
      health_json TEXT,
      revoked_at DATETIME,
      support_until DATETIME
    );
    CREATE TABLE IF NOT EXISTS print_enroll_codes (
      code TEXT PRIMARY KEY,
      client_id INTEGER NOT NULL,
      org_id INTEGER NOT NULL DEFAULT 0,
      name TEXT NOT NULL,
      created_by INTEGER,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      expires_at DATETIME NOT NULL,
      used_at DATETIME,
      agent_id INTEGER
    );
    CREATE TABLE IF NOT EXISTS print_printer_bindings (
      cust_printer_id INTEGER PRIMARY KEY,
      agent_id INTEGER NOT NULL,
      cups_name TEXT NOT NULL,
      org_id INTEGER NOT NULL DEFAULT 0,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
    );
    CREATE INDEX IF NOT EXISTS idx_print_bindings_queue ON print_printer_bindings(cups_name);
    CREATE TABLE IF NOT EXISTS print_jobs (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      batch_id TEXT,
      agent_id INTEGER NOT NULL,
      queue TEXT NOT NULL,
      options TEXT NOT NULL DEFAULT '',
      file_path TEXT NOT NULL,
      file_name TEXT NOT NULL,
      file_size INTEGER NOT NULL DEFAULT 0,
      source TEXT NOT NULL DEFAULT '',
      status TEXT NOT NULL DEFAULT 'queued',
      cups_job_id TEXT,
      attempts INTEGER NOT NULL DEFAULT 0,
      lp_output TEXT,
      error TEXT,
      created_by_user INTEGER,
      created_by_role INTEGER,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      sent_at DATETIME,
      spooled_at DATETIME,
      completed_at DATETIME,
      cancelled_at DATETIME,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
    );
    CREATE INDEX IF NOT EXISTS idx_print_jobs_agent_status ON print_jobs(agent_id, status, id);
    CREATE INDEX IF NOT EXISTS idx_print_jobs_user ON print_jobs(created_by_user, updated_at);
    CREATE INDEX IF NOT EXISTS idx_print_jobs_batch ON print_jobs(batch_id);
    CREATE TABLE IF NOT EXISTS print_agent_commands (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      agent_id INTEGER NOT NULL,
      command TEXT NOT NULL,
      payload_json TEXT,
      status TEXT NOT NULL DEFAULT 'queued',
      result TEXT,
      created_by INTEGER,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      sent_at DATETIME,
      done_at DATETIME
    );
    CREATE INDEX IF NOT EXISTS idx_print_commands_agent ON print_agent_commands(agent_id, status, id);
  `)
  // additive migrations (older spool files): per-box remote-access switches, default ON
  const agentCols = new Set((db.prepare(`PRAGMA table_info(print_agents)`).all() as any[]).map(c => c.name))
  if (!agentCols.has('allow_cups_relay')) db.exec(`ALTER TABLE print_agents ADD COLUMN allow_cups_relay INTEGER NOT NULL DEFAULT 1`)
  if (!agentCols.has('allow_support')) db.exec(`ALTER TABLE print_agents ADD COLUMN allow_support INTEGER NOT NULL DEFAULT 1`)
  console.log('[PrintSpoolDB] SQLite ready at', dbPath)
  return db
}

if (!g[DB_KEY]) g[DB_KEY] = open()
const db: InstanceType<typeof Database> = g[DB_KEY]

export const printSpoolDb = db

// ── helpers ──────────────────────────────────────────────────────────────────

export const hashAgentToken = (token: string) => createHash('sha256').update(String(token)).digest('hex')

const nowIso = () => new Date().toISOString().replace('T', ' ').slice(0, 19)

/** An agent counts as online when its last heartbeat/poll is younger than this. */
export const AGENT_ONLINE_MS = 3 * 60 * 1000

export const isAgentOnline = (a: Pick<PrintAgentRow, 'last_seen' | 'revoked_at'> | null | undefined) => {
  if (!a || a.revoked_at || !a.last_seen) return false
  return Date.now() - new Date(a.last_seen.replace(' ', 'T') + 'Z').getTime() < AGENT_ONLINE_MS
}

export const parseJson = <T = any>(s: string | null | undefined, fallback: T): T => {
  if (!s) return fallback
  try { return JSON.parse(s) as T } catch { return fallback }
}

// ── enrolment ────────────────────────────────────────────────────────────────

const CODE_ALPHABET = 'ABCDEFGHJKLMNPQRSTUVWXYZ23456789'
const randomCode = () => {
  const bytes = randomBytes(12)
  let out = ''
  for (let i = 0; i < 12; i++) {
    out += CODE_ALPHABET[bytes[i] % CODE_ALPHABET.length]
    if (i === 3 || i === 7) out += '-'
  }
  return 'LSPA-' + out
}

export const createEnrollCode = (clientId: number, orgId: number, name: string, createdBy: number | null, ttlHours = 24) => {
  const code = randomCode()
  const expires = new Date(Date.now() + ttlHours * 3600_000).toISOString().replace('T', ' ').slice(0, 19)
  db.prepare(`INSERT INTO print_enroll_codes (code, client_id, org_id, name, created_by, expires_at) VALUES (?, ?, ?, ?, ?, ?)`)
    .run(code, clientId, orgId, name, createdBy, expires)
  return { code, expiresAt: expires }
}

export const listEnrollCodes = (clientId: number) =>
  db.prepare(`SELECT code, org_id, name, created_at, expires_at, used_at, agent_id FROM print_enroll_codes WHERE client_id = ? AND used_at IS NULL AND expires_at > ? ORDER BY created_at DESC`)
    .all(clientId, nowIso()) as any[]

export const deleteEnrollCode = (clientId: number, code: string) =>
  db.prepare(`DELETE FROM print_enroll_codes WHERE client_id = ? AND code = ? AND used_at IS NULL`).run(clientId, code)

/** Exchanges a valid code for a new agent + plaintext token (returned once). */
export const enrollAgent = (code: string, meta: { hostname?: string; platform?: string; version?: string; name?: string }) => {
  const row: any = db.prepare(`SELECT * FROM print_enroll_codes WHERE code = ?`).get(String(code || '').trim().toUpperCase())
  if (!row) return { error: 'unknown code' as const }
  if (row.used_at) return { error: 'code already used' as const }
  if (row.expires_at <= nowIso()) return { error: 'code expired' as const }

  const token = 'lspa_' + randomBytes(32).toString('base64url')
  const tx = db.transaction(() => {
    const res = db.prepare(`INSERT INTO print_agents (client_id, org_id, name, token_hash, created_by, last_seen, version, hostname, platform)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`)
      .run(row.client_id, row.org_id, meta.name || row.name, hashAgentToken(token), row.created_by, nowIso(), meta.version || null, meta.hostname || null, meta.platform || null)
    const agentId = Number(res.lastInsertRowid)
    db.prepare(`UPDATE print_enroll_codes SET used_at = ?, agent_id = ? WHERE code = ?`).run(nowIso(), agentId, row.code)
    return agentId
  })
  const agentId = tx()
  return { agentId, token, clientId: row.client_id, orgId: row.org_id, name: meta.name || row.name }
}

// ── agents ───────────────────────────────────────────────────────────────────

export const getAgentByToken = (token: string): PrintAgentRow | null => {
  if (!token) return null
  const row = db.prepare(`SELECT * FROM print_agents WHERE token_hash = ? AND revoked_at IS NULL`).get(hashAgentToken(token)) as PrintAgentRow | undefined
  return row || null
}

export const getAgent = (id: number): PrintAgentRow | null =>
  (db.prepare(`SELECT * FROM print_agents WHERE id = ?`).get(id) as PrintAgentRow | undefined) || null

export const listAgents = (clientId: number): PrintAgentRow[] =>
  db.prepare(`SELECT * FROM print_agents WHERE client_id = ? AND revoked_at IS NULL ORDER BY id`).all(clientId) as PrintAgentRow[]

export const touchAgent = (id: number) =>
  db.prepare(`UPDATE print_agents SET last_seen = ? WHERE id = ?`).run(nowIso(), id)

export const updateAgentHeartbeat = (id: number, data: {
  version?: string; hostname?: string; platform?: string;
  printers?: AgentPrinterInfo[]; queueDepth?: Record<string, number>; health?: any
}) => {
  db.prepare(`UPDATE print_agents SET last_seen = ?, version = COALESCE(?, version), hostname = COALESCE(?, hostname),
      platform = COALESCE(?, platform), printers_json = COALESCE(?, printers_json),
      queue_depth_json = COALESCE(?, queue_depth_json), health_json = COALESCE(?, health_json) WHERE id = ?`)
    .run(nowIso(), data.version ?? null, data.hostname ?? null, data.platform ?? null,
      data.printers ? JSON.stringify(data.printers) : null,
      data.queueDepth ? JSON.stringify(data.queueDepth) : null,
      data.health ? JSON.stringify(data.health) : null, id)
}

export const renameAgent = (clientId: number, id: number, name: string) =>
  db.prepare(`UPDATE print_agents SET name = ? WHERE id = ? AND client_id = ?`).run(name, id, clientId)

export const revokeAgent = (clientId: number, id: number) => {
  const tx = db.transaction(() => {
    db.prepare(`UPDATE print_agents SET revoked_at = ? WHERE id = ? AND client_id = ?`).run(nowIso(), id, clientId)
    db.prepare(`DELETE FROM print_printer_bindings WHERE agent_id = ?`).run(id)
    db.prepare(`UPDATE print_jobs SET status = 'cancelled', cancelled_at = ?, updated_at = ?, error = 'agent revoked' WHERE agent_id = ? AND status IN ('queued','sent')`).run(nowIso(), nowIso(), id)
  })
  tx()
}

/** Per-box remote-access switches (customer decision): CUPS web relay / support shell tunnel. */
export const setAgentAccess = (clientId: number, id: number, access: { allowCupsRelay?: boolean; allowSupport?: boolean }) => {
  if (access.allowCupsRelay !== undefined) db.prepare(`UPDATE print_agents SET allow_cups_relay = ? WHERE id = ? AND client_id = ?`).run(access.allowCupsRelay ? 1 : 0, id, clientId)
  if (access.allowSupport !== undefined) db.prepare(`UPDATE print_agents SET allow_support = ? WHERE id = ? AND client_id = ?`).run(access.allowSupport ? 1 : 0, id, clientId)
}

export const setAgentSupportUntil = (clientId: number, id: number, until: string | null) =>
  db.prepare(`UPDATE print_agents SET support_until = ? WHERE id = ? AND client_id = ?`).run(until, id, clientId)

/** Queue inventory of one agent as reported by its last heartbeat. */
export const getAgentPrinters = (agent: PrintAgentRow | null): AgentPrinterInfo[] =>
  parseJson<AgentPrinterInfo[]>(agent?.printers_json, [])

// ── bindings ─────────────────────────────────────────────────────────────────

export const setBinding = (custPrinterId: number, agentId: number, cupsName: string, orgId: number) =>
  db.prepare(`INSERT INTO print_printer_bindings (cust_printer_id, agent_id, cups_name, org_id) VALUES (?, ?, ?, ?)
    ON CONFLICT(cust_printer_id) DO UPDATE SET agent_id = excluded.agent_id, cups_name = excluded.cups_name, org_id = excluded.org_id, updated_at = CURRENT_TIMESTAMP`)
    .run(custPrinterId, agentId, cupsName, orgId)

export const deleteBinding = (custPrinterId: number) =>
  db.prepare(`DELETE FROM print_printer_bindings WHERE cust_printer_id = ?`).run(custPrinterId)

export const listBindings = (): PrintBindingRow[] =>
  db.prepare(`SELECT b.* FROM print_printer_bindings b JOIN print_agents a ON a.id = b.agent_id WHERE a.revoked_at IS NULL`).all() as PrintBindingRow[]

export const getBinding = (custPrinterId: number): PrintBindingRow | null =>
  (db.prepare(`SELECT * FROM print_printer_bindings WHERE cust_printer_id = ?`).get(custPrinterId) as PrintBindingRow | undefined) || null

let bindingCountCache: { n: number; ts: number } | null = null
/** Cheap hot-path guard: with zero bindings (our own prod) dispatch never touches anything else. */
export const hasAnyBindings = () => {
  if (bindingCountCache && Date.now() - bindingCountCache.ts < 5000) return bindingCountCache.n > 0
  const r: any = db.prepare(`SELECT COUNT(*) AS n FROM print_printer_bindings`).get()
  bindingCountCache = { n: Number(r?.n || 0), ts: Date.now() }
  return bindingCountCache.n > 0
}
export const invalidateBindingCache = () => { bindingCountCache = null }

/**
 * Binding for a CUPS queue name: the row of the caller's org wins over a shared (org 0)
 * row; any other org's row is ignored. Returns the agent too (null when revoked).
 */
export const findBindingForQueue = (cupsName: string, orgId: number): { binding: PrintBindingRow; agent: PrintAgentRow } | null => {
  const rows = db.prepare(`SELECT * FROM print_printer_bindings WHERE cups_name = ?`).all(cupsName) as PrintBindingRow[]
  if (!rows.length) return null
  const pick = rows.find(r => Number(r.org_id) === Number(orgId)) || rows.find(r => Number(r.org_id) === 0) || null
  if (!pick) return null
  const agent = getAgent(pick.agent_id)
  if (!agent || agent.revoked_at) return null
  return { binding: pick, agent }
}

/** Label media (mm) an agent reported for a queue, for labelMedia.ts / cups-media. */
export const findAgentMediaForQueue = (cupsName: string, orgId: number): { w: number; h: number } | null => {
  const hit = findBindingForQueue(cupsName, orgId)
  if (!hit) return null
  const p = getAgentPrinters(hit.agent).find(x => x.queue === cupsName)
  return p?.media && p.media.w > 0 && p.media.h > 0 ? { w: p.media.w, h: p.media.h } : null
}

// ── jobs ─────────────────────────────────────────────────────────────────────

export interface NewPrintJob {
  agentId: number
  queue: string
  options: string
  filePath: string
  fileName: string
  fileSize: number
  source: string
  batchId?: string | null
  createdByUser?: number | null
  createdByRole?: number | null
}

const insertJobStmt = () => db.prepare(`INSERT INTO print_jobs
  (batch_id, agent_id, queue, options, file_path, file_name, file_size, source, status, created_by_user, created_by_role)
  VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'queued', ?, ?)`)

export const enqueueJob = (job: NewPrintJob): number => {
  const res = insertJobStmt().run(job.batchId ?? null, job.agentId, job.queue, job.options, job.filePath, job.fileName, job.fileSize, job.source, job.createdByUser ?? null, job.createdByRole ?? null)
  return Number(res.lastInsertRowid)
}

/** One transaction for a whole batch (800 labels = one fsync). */
export const enqueueJobs = (jobs: NewPrintJob[]): number[] => {
  const stmt = insertJobStmt()
  const tx = db.transaction((items: NewPrintJob[]) => items.map(j =>
    Number(stmt.run(j.batchId ?? null, j.agentId, j.queue, j.options, j.filePath, j.fileName, j.fileSize, j.source, j.createdByUser ?? null, j.createdByRole ?? null).lastInsertRowid)))
  return tx(jobs)
}

export const newBatchId = () => randomUUID()

/** Atomically claims up to `max` queued jobs of an agent (oldest first) and marks them sent. */
export const claimJobs = (agentId: number, max = 50): PrintJobRow[] => {
  const tx = db.transaction(() => {
    const rows = db.prepare(`SELECT * FROM print_jobs WHERE agent_id = ? AND status = 'queued' ORDER BY id LIMIT ?`).all(agentId, max) as PrintJobRow[]
    if (!rows.length) return rows
    const upd = db.prepare(`UPDATE print_jobs SET status = 'sent', sent_at = ?, attempts = attempts + 1, updated_at = ? WHERE id = ?`)
    const ts = nowIso()
    for (const r of rows) upd.run(ts, ts, r.id)
    return rows.map(r => ({ ...r, status: 'sent' as PrintJobStatus, attempts: r.attempts + 1, sent_at: ts }))
  })
  return tx()
}

export const countQueuedJobs = (agentId: number): number => {
  const r: any = db.prepare(`SELECT COUNT(*) AS n FROM print_jobs WHERE agent_id = ? AND status = 'queued'`).get(agentId)
  return Number(r?.n || 0)
}

export const getJob = (id: number): PrintJobRow | null =>
  (db.prepare(`SELECT * FROM print_jobs WHERE id = ?`).get(id) as PrintJobRow | undefined) || null

export interface AckInput { id: number; status: 'spooled' | 'completed' | 'failed'; cupsJobId?: string | null; output?: string | null; error?: string | null }

/** Agent acknowledgement; ignores jobs of other agents and never regresses a final state. */
export const ackJob = (agentId: number, ack: AckInput): PrintJobRow | null => {
  const job = getJob(ack.id)
  if (!job || job.agent_id !== agentId) return null
  if (['completed', 'failed', 'cancelled'].includes(job.status) && ack.status !== 'failed') return job
  const ts = nowIso()
  const out = ack.output ? String(ack.output).slice(0, 4000) : null
  const err = ack.error ? String(ack.error).slice(0, 2000) : null
  if (ack.status === 'spooled') {
    db.prepare(`UPDATE print_jobs SET status = 'spooled', spooled_at = ?, cups_job_id = COALESCE(?, cups_job_id), lp_output = COALESCE(?, lp_output), error = NULL, updated_at = ? WHERE id = ?`)
      .run(ts, ack.cupsJobId ?? null, out, ts, job.id)
  } else if (ack.status === 'completed') {
    db.prepare(`UPDATE print_jobs SET status = 'completed', completed_at = ?, spooled_at = COALESCE(spooled_at, ?), cups_job_id = COALESCE(?, cups_job_id), lp_output = COALESCE(?, lp_output), updated_at = ? WHERE id = ?`)
      .run(ts, ts, ack.cupsJobId ?? null, out, ts, job.id)
  } else {
    db.prepare(`UPDATE print_jobs SET status = 'failed', completed_at = ?, cups_job_id = COALESCE(?, cups_job_id), lp_output = COALESCE(?, lp_output), error = ?, updated_at = ? WHERE id = ?`)
      .run(ts, ack.cupsJobId ?? null, out, err || 'failed', ts, job.id)
  }
  return getJob(job.id)
}

export const cancelJob = (id: number): PrintJobRow | null => {
  const ts = nowIso()
  db.prepare(`UPDATE print_jobs SET status = 'cancelled', cancelled_at = ?, updated_at = ? WHERE id = ? AND status IN ('queued','sent')`).run(ts, ts, id)
  return getJob(id)
}

export const retryJob = (id: number): PrintJobRow | null => {
  const ts = nowIso()
  db.prepare(`UPDATE print_jobs SET status = 'queued', sent_at = NULL, spooled_at = NULL, completed_at = NULL, cancelled_at = NULL, error = NULL, cups_job_id = NULL, updated_at = ? WHERE id = ? AND status IN ('failed','cancelled','sent')`).run(ts, id)
  return getJob(id)
}

export interface JobListFilter { agentId?: number; status?: PrintJobStatus | PrintJobStatus[]; userId?: number; batchId?: string; sinceIso?: string; limit?: number }

export const listJobs = (f: JobListFilter = {}): PrintJobRow[] => {
  const where: string[] = []
  const args: any[] = []
  if (f.agentId) { where.push('agent_id = ?'); args.push(f.agentId) }
  if (f.userId) { where.push('created_by_user = ?'); args.push(f.userId) }
  if (f.batchId) { where.push('batch_id = ?'); args.push(f.batchId) }
  if (f.sinceIso) { where.push('updated_at > ?'); args.push(f.sinceIso) }
  if (f.status) {
    const st = Array.isArray(f.status) ? f.status : [f.status]
    where.push(`status IN (${st.map(() => '?').join(',')})`); args.push(...st)
  }
  const sql = `SELECT * FROM print_jobs ${where.length ? 'WHERE ' + where.join(' AND ') : ''} ORDER BY id DESC LIMIT ?`
  args.push(Math.min(Math.max(Number(f.limit) || 200, 1), 2000))
  return db.prepare(sql).all(...args) as PrintJobRow[]
}

export const jobStats = (agentId?: number) => {
  const rows = db.prepare(`SELECT agent_id, status, COUNT(*) AS n FROM print_jobs ${agentId ? 'WHERE agent_id = ?' : ''} GROUP BY agent_id, status`).all(...(agentId ? [agentId] : [])) as any[]
  const out: Record<number, Record<string, number>> = {}
  for (const r of rows) {
    out[r.agent_id] = out[r.agent_id] || {}
    out[r.agent_id][r.status] = Number(r.n)
  }
  return out
}

/**
 * Maintenance (server/plugins/print-spool-maintenance.ts):
 *  - 'sent' jobs without an ack for `staleMinutes` go back to 'queued' (the agent keeps an
 *    "already spooled" ledger by job id, so a resend never double-prints);
 *  - final jobs older than `keepHours` lose their spool file and row.
 */
/** An agent that just (re)started lost its in-memory claims: hand its 'sent' jobs back at once. */
export const requeueSentJobsForAgent = (agentId: number): number => {
  const r = db.prepare(`UPDATE print_jobs SET status = 'queued', updated_at = ? WHERE agent_id = ? AND status = 'sent'`).run(nowIso(), agentId)
  return Number(r.changes || 0)
}

/** 'spooled' jobs of an agent whose completion was never reported (handed to a freshly started agent). */
export const listUnconfirmedSpooledJobs = (agentId: number, limit = 2000) =>
  db.prepare(`SELECT id, cups_job_id, queue FROM print_jobs WHERE agent_id = ? AND status = 'spooled' ORDER BY id LIMIT ?`).all(agentId, limit) as { id: number; cups_job_id: string | null; queue: string }[]

/**
 * Safety net: 'spooled' = CUPS on the device accepted the file. If no completion report arrives for
 * `staleMinutes` (agent lost its ledger entry in a crash, box re-imaged, …) the job is closed as
 * completed with a note rather than hanging in the list forever.
 */
export const finalizeStaleSpooledJobs = (staleMinutes = 30): number => {
  const cutoff = new Date(Date.now() - staleMinutes * 60_000).toISOString().replace('T', ' ').slice(0, 19)
  const ts = nowIso()
  const r = db.prepare(`UPDATE print_jobs SET status = 'completed', completed_at = ?, updated_at = ?,
      lp_output = COALESCE(lp_output, '') || ' | completion not reported by the agent within ${staleMinutes} min — assumed printed (CUPS had accepted it)'
    WHERE status = 'spooled' AND spooled_at < ?`).run(ts, ts, cutoff)
  return Number(r.changes || 0)
}

export const requeueStaleSentJobs = (staleMinutes = 3): number => {
  const cutoff = new Date(Date.now() - staleMinutes * 60_000).toISOString().replace('T', ' ').slice(0, 19)
  const r = db.prepare(`UPDATE print_jobs SET status = 'queued', updated_at = ? WHERE status = 'sent' AND sent_at < ? AND attempts < 5`).run(nowIso(), cutoff)
  return Number(r.changes || 0)
}

export const purgeOldJobs = (keepHours = 24): { rows: number; files: string[] } => {
  const cutoff = new Date(Date.now() - keepHours * 3600_000).toISOString().replace('T', ' ').slice(0, 19)
  const rows = db.prepare(`SELECT id, file_path FROM print_jobs WHERE status IN ('completed','failed','cancelled') AND updated_at < ?`).all(cutoff) as any[]
  const files = rows.map(r => r.file_path).filter(Boolean)
  if (rows.length) db.prepare(`DELETE FROM print_jobs WHERE id IN (${rows.map(() => '?').join(',')})`).run(...rows.map(r => r.id))
  db.prepare(`DELETE FROM print_enroll_codes WHERE used_at IS NOT NULL AND used_at < ?`).run(cutoff)
  return { rows: rows.length, files }
}

// ── commands ─────────────────────────────────────────────────────────────────

export const AGENT_COMMANDS = ['update', 'lpstat', 'restart-cups', 'journal', 'cancel-all', 'support-tunnel-on', 'support-tunnel-off', 'reboot', 'backup-now',
  // remote printer administration on the device (lpinfo / lpadmin / lpoptions / cupsenable …)
  'discover-printers', 'add-printer', 'remove-printer', 'set-queue-option', 'queue-control', 'test-print', 'list-queues',
  // HTTP relay to the device's CUPS web UI (server/api/settings/print-agents/[id]/cups/[...path].ts)
  'http'] as const
export type AgentCommand = typeof AGENT_COMMANDS[number]

export const queueCommand = (agentId: number, command: AgentCommand, payload: any, createdBy: number | null) => {
  const res = db.prepare(`INSERT INTO print_agent_commands (agent_id, command, payload_json, created_by) VALUES (?, ?, ?, ?)`)
    .run(agentId, command, payload ? JSON.stringify(payload) : null, createdBy)
  return Number(res.lastInsertRowid)
}

export const claimCommands = (agentId: number) => {
  const tx = db.transaction(() => {
    const rows = db.prepare(`SELECT * FROM print_agent_commands WHERE agent_id = ? AND status = 'queued' ORDER BY id`).all(agentId) as any[]
    const ts = nowIso()
    const upd = db.prepare(`UPDATE print_agent_commands SET status = 'sent', sent_at = ? WHERE id = ?`)
    for (const r of rows) upd.run(ts, r.id)
    return rows.map(r => ({ id: r.id, command: r.command, payload: parseJson(r.payload_json, null) }))
  })
  return tx()
}

export const finishCommand = (agentId: number, id: number, ok: boolean, result: string) =>
  db.prepare(`UPDATE print_agent_commands SET status = ?, result = ?, done_at = ? WHERE id = ? AND agent_id = ?`)
    .run(ok ? 'done' : 'failed', String(result || '').slice(0, 8 * 1024 * 1024), nowIso(), id, agentId)

/** Relay results (whole CUPS pages/images) are transient: drop them 10 min after completion. */
export const purgeRelayCommands = () =>
  db.prepare(`DELETE FROM print_agent_commands WHERE command = 'http' AND done_at IS NOT NULL AND done_at < ?`)
    .run(new Date(Date.now() - 10 * 60_000).toISOString().replace('T', ' ').slice(0, 19))

export const listCommands = (agentId: number, limit = 20) =>
  db.prepare(`SELECT * FROM print_agent_commands WHERE agent_id = ? ORDER BY id DESC LIMIT ?`).all(agentId, limit) as any[]