import { and, count, lt, sql } from 'drizzle-orm'
import { archivePrunable } from './archive.ts'
import { getDb, getRawDb } from './db.ts'
import { actions, runs, threads } from './schema.ts'

export type PrunePreview = {
  cutoff: number
  totalActions: number
  totalRuns: number
  totalThreads: number
  toDeleteActions: number
  toDeleteRuns: number
  toDeleteThreads: number
  estimatedBytes: number
}

export type PruneResult = PrunePreview & {
  deletedActions: number
  deletedRuns: number
  deletedThreads: number
  vacuumed: boolean
  /** Per-day archive files (YYYY-MM-DD) written before deletion. */
  archivedDays: string[]
}

export async function previewPrune(cutoff: number): Promise<PrunePreview> {
  const db = await getDb()
  const raw = await getRawDb()

  const totalActionsRow = db.select({ n: count() }).from(actions).get()
  const totalRunsRow = db.select({ n: count() }).from(runs).get()
  const totalThreadsRow = db.select({ n: count() }).from(threads).get()

  const toDeleteActionsRow = db
    .select({ n: count() })
    .from(actions)
    .where(lt(actions.ts, cutoff))
    .get()

  // A run is prunable when its newest activity is older than cutoff.
  // Approximate: runs whose startedAt is below cutoff AND whose endedAt
  // is either present-and-below-cutoff or whose newest action is below
  // cutoff. NULL endedAt means in-flight, never prune.
  const toDeleteRunsRow = db
    .select({ n: count() })
    .from(runs)
    .where(
      and(
        lt(runs.startedAt, cutoff),
        sql`${runs.endedAt} IS NOT NULL AND ${runs.endedAt} < ${cutoff}`,
      ),
    )
    .get()

  // Threads with no remaining runs after the prune become orphans.
  const toDeleteThreadsRow = raw
    .prepare(
      `SELECT COUNT(*) AS n FROM threads
       WHERE id IN (
         SELECT thread_id FROM runs
         GROUP BY thread_id
         HAVING MAX(started_at) < ?
       )`,
    )
    .get(cutoff) as { n: number } | undefined

  // Estimate freed bytes: sum the sidecar payloads of prunable actions.
  // Approximate; the real SQLite-on-disk reclaim depends on VACUUM.
  const sizeRow = raw
    .prepare(
      `SELECT COALESCE(SUM(LENGTH(p.payload) + COALESCE(LENGTH(p.raw_req), 0) + COALESCE(LENGTH(p.raw_res), 0)), 0) AS bytes
       FROM action_payloads p
       JOIN actions a ON a.id = p.action_id
       WHERE a.ts < ?`,
    )
    .get(cutoff) as { bytes: number } | undefined

  return {
    cutoff,
    totalActions: totalActionsRow?.n ?? 0,
    totalRuns: totalRunsRow?.n ?? 0,
    totalThreads: totalThreadsRow?.n ?? 0,
    toDeleteActions: toDeleteActionsRow?.n ?? 0,
    toDeleteRuns: toDeleteRunsRow?.n ?? 0,
    toDeleteThreads: toDeleteThreadsRow?.n ?? 0,
    estimatedBytes: sizeRow?.bytes ?? 0,
  }
}

export async function runPrune(
  cutoff: number,
  opts: { vacuum?: boolean; archive?: boolean } = {},
): Promise<PruneResult> {
  const preview = await previewPrune(cutoff)
  const raw = await getRawDb()

  // Archive the about-to-be-evicted data into per-day cold files BEFORE we
  // delete anything. If this throws, the error propagates and the delete
  // below never runs — so retention never silently loses history.
  const archivedDays = opts.archive === false ? [] : archivePrunable(raw, cutoff)

  // Delete the payload sidecar rows for the actions about to be pruned,
  // then the actions themselves.
  raw
    .prepare(
      `DELETE FROM action_payloads
       WHERE action_id IN (SELECT id FROM actions WHERE ts < ?)`,
    )
    .run(cutoff)
  const deletedActions = raw.prepare('DELETE FROM actions WHERE ts < ?').run(cutoff)
  const deletedRuns = raw
    .prepare(`DELETE FROM runs WHERE started_at < ? AND ended_at IS NOT NULL AND ended_at < ?`)
    .run(cutoff, cutoff)
  const deletedThreads = raw
    .prepare(`DELETE FROM threads WHERE id NOT IN (SELECT DISTINCT thread_id FROM runs)`)
    .run()

  let vacuumed = false
  if (opts.vacuum) {
    raw.exec('VACUUM')
    vacuumed = true
  }

  return {
    ...preview,
    deletedActions: deletedActions.changes,
    deletedRuns: deletedRuns.changes,
    deletedThreads: deletedThreads.changes,
    vacuumed,
    archivedDays,
  }
}