// Per-day cold archive. When prune evicts data older than the retention
// window from the active store, we first copy it — grouped by the action's
// UTC day — into standalone `archive/YYYY-MM-DD.db` files (each a full,
// self-contained Thomas schema). So retention bounds the *active* store
// without throwing history away: old days become portable, inspectable
// files you can `thomas show`/`report` against later or just delete.
//
// The active store stays a single file, so task=thread correlation and
// cross-day outcome verification are unaffected — archiving is a read-then-
// copy of already-evicted data, never a change to the live read/write path.

import { mkdirSync } from 'node:fs'
import { join } from 'node:path'
import Database from 'better-sqlite3'
import { logger } from '../../core/logger.ts'
import { paths } from '../../core/paths.ts'
import { applySchema } from './db.ts'

// Tables copied into a day archive, in FK-safe order. `actions` first so the
// later scoping subqueries can read `arch.actions`.
const COPY_STEPS: Array<{ table: string; where: string }> = [
  { table: 'actions', where: 'ts < ? AND archday(ts) = ?' },
  { table: 'action_payloads', where: 'action_id IN (SELECT id FROM arch.actions)' },
  { table: 'runs', where: 'id IN (SELECT DISTINCT run_id FROM arch.actions)' },
  { table: 'threads', where: 'id IN (SELECT DISTINCT thread_id FROM arch.actions)' },
  { table: 'outcomes', where: 'action_id IN (SELECT id FROM arch.actions)' },
  { table: 'tool_uses', where: 'action_id IN (SELECT id FROM arch.actions)' },
  { table: 'tool_targets', where: 'action_id IN (SELECT id FROM arch.actions)' },
]

// SQLite expression for an action's archive day (UTC, matching the rest of
// Thomas which buckets on UTC `toISOString().slice(0,10)`).
const ARCHDAY = "strftime('%Y-%m-%d', ts / 1000, 'unixepoch')"

/**
 * Copy every prunable row (ts < cutoff) into per-day archive files. Returns
 * the days archived. Idempotent: re-archiving a day INSERT-OR-IGNOREs, so a
 * retried prune doesn't duplicate. Throws on failure — the caller must then
 * skip the delete so no data is lost.
 */
export function archivePrunable(raw: Database.Database, cutoff: number): string[] {
  const days = (
    raw
      .prepare(`SELECT DISTINCT ${ARCHDAY} AS d FROM actions WHERE ts < ? ORDER BY d`)
      .all(cutoff) as Array<{ d: string }>
  ).map((r) => r.d)

  if (days.length === 0) return []

  mkdirSync(paths.wire.tracesArchive, { recursive: true })

  for (const day of days) {
    const file = join(paths.wire.tracesArchive, `${day}.db`)
    // Pre-create the archive file with the full schema, via its own handle.
    const adb = new Database(file)
    try {
      applySchema(adb)
    } finally {
      adb.close()
    }

    raw.prepare('ATTACH DATABASE ? AS arch').run(file)
    try {
      raw.transaction(() => {
        for (const step of COPY_STEPS) {
          const where = step.where.replace('archday(ts)', ARCHDAY)
          const params = step.table === 'actions' ? [cutoff, day] : []
          raw
            .prepare(
              `INSERT OR IGNORE INTO arch.${step.table} SELECT * FROM main.${step.table} WHERE ${where}`,
            )
            .run(...params)
        }
      })()
    } finally {
      raw.exec('DETACH DATABASE arch')
    }
    logger.debug(`archive: wrote ${file}`)
  }

  return days
}