// guard:allow-unscoped -- schema migrations and data backfills run system-wide
// during startup, not in a user-scoped request path.
import {
  ensureAdditiveColumns,
  getDbExec,
  runMigrations,
} from "@agent-native/core/db";

import * as schema from "../db/schema.js";

/**
 * Every Drizzle table exported from schema.ts. Filters out type-only and
 * helper exports the same way db.spec.ts's `isDrizzleTable` regression guard
 * does: a real table carries a Symbol-keyed drizzle metadata bag, plain
 * exports don't.
 */
function isDrizzleTable(value: unknown): value is object {
  return (
    !!value &&
    typeof value === "object" &&
    Object.getOwnPropertySymbols(value).some((s) =>
      s.toString().includes("drizzle"),
    )
  );
}

const schemaTables = Object.values(schema).filter(isDrizzleTable);

// Convention: every new migration below MUST set a unique `name:` slug (see
// packages/core/src/db/migrations.ts for the full rationale). Version numbers
// alone are not a safe identity across parallel branches that each extend
// this list independently.
const runPlanMigrations = runMigrations(
  [
    {
      version: 1,
      sql: `CREATE TABLE IF NOT EXISTS plans (
  id TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  brief TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'draft',
  source TEXT NOT NULL DEFAULT 'manual',
  repo_path TEXT,
  current_focus TEXT,
  html TEXT,
  markdown TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  approved_at TEXT,
  owner_email TEXT NOT NULL,
  org_id TEXT,
  visibility TEXT NOT NULL DEFAULT 'private'
)`,
    },
    {
      version: 2,
      sql: `CREATE TABLE IF NOT EXISTS plan_sections (
  id TEXT PRIMARY KEY,
  plan_id TEXT NOT NULL REFERENCES plans(id),
  type TEXT NOT NULL DEFAULT 'custom',
  title TEXT NOT NULL,
  body TEXT NOT NULL DEFAULT '',
  html TEXT,
  sort_order INTEGER NOT NULL DEFAULT 0,
  created_by TEXT NOT NULL DEFAULT 'agent',
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
)`,
    },
    {
      version: 3,
      sql: `CREATE TABLE IF NOT EXISTS plan_comments (
  id TEXT PRIMARY KEY,
  plan_id TEXT NOT NULL REFERENCES plans(id),
  section_id TEXT REFERENCES plan_sections(id),
  kind TEXT NOT NULL DEFAULT 'comment',
  status TEXT NOT NULL DEFAULT 'open',
  anchor TEXT,
  message TEXT NOT NULL,
  created_by TEXT NOT NULL DEFAULT 'human',
  consumed_at TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
)`,
    },
    {
      version: 4,
      sql: `CREATE TABLE IF NOT EXISTS plan_events (
  id TEXT PRIMARY KEY,
  plan_id TEXT NOT NULL REFERENCES plans(id),
  type TEXT NOT NULL,
  message TEXT NOT NULL,
  payload TEXT,
  created_by TEXT NOT NULL DEFAULT 'agent',
  created_at TEXT NOT NULL
)`,
    },
    {
      version: 5,
      sql: {
        postgres: `CREATE TABLE IF NOT EXISTS plan_shares (
  id TEXT PRIMARY KEY,
  resource_id TEXT NOT NULL,
  principal_type TEXT NOT NULL,
  principal_id TEXT NOT NULL,
  role TEXT NOT NULL DEFAULT 'viewer',
  created_by TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (now())
)`,
        sqlite: `CREATE TABLE IF NOT EXISTS plan_shares (
  id TEXT PRIMARY KEY,
  resource_id TEXT NOT NULL,
  principal_type TEXT NOT NULL,
  principal_id TEXT NOT NULL,
  role TEXT NOT NULL DEFAULT 'viewer',
  created_by TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
)`,
      },
    },
    {
      version: 6,
      sql: `CREATE INDEX IF NOT EXISTS plans_owner_status_idx ON plans(owner_email, org_id, status, updated_at)`,
    },
    {
      version: 7,
      sql: `CREATE INDEX IF NOT EXISTS plan_sections_plan_idx ON plan_sections(plan_id, sort_order)`,
    },
    {
      version: 8,
      sql: `CREATE INDEX IF NOT EXISTS plan_comments_plan_status_idx ON plan_comments(plan_id, status, consumed_at)`,
    },
    {
      version: 9,
      sql: {
        postgres: `ALTER TABLE plans ADD COLUMN IF NOT EXISTS content TEXT`,
        sqlite: `ALTER TABLE plans ADD COLUMN content TEXT`,
      },
    },
    {
      version: 10,
      sql: {
        postgres: `ALTER TABLE plans ADD COLUMN IF NOT EXISTS hosted_plan_id TEXT`,
        sqlite: `ALTER TABLE plans ADD COLUMN hosted_plan_id TEXT`,
      },
    },
    {
      version: 11,
      sql: {
        postgres: `ALTER TABLE plans ADD COLUMN IF NOT EXISTS hosted_plan_url TEXT`,
        sqlite: `ALTER TABLE plans ADD COLUMN hosted_plan_url TEXT`,
      },
    },
    {
      version: 12,
      sql: `CREATE TABLE IF NOT EXISTS plan_guest_mints (
  id TEXT PRIMARY KEY,
  ip_hash TEXT NOT NULL,
  created_at TEXT NOT NULL
)`,
    },
    {
      version: 13,
      sql: `CREATE INDEX IF NOT EXISTS plan_guest_mints_ip_created_idx ON plan_guest_mints(ip_hash, created_at)`,
    },
    {
      version: 14,
      sql: `CREATE INDEX IF NOT EXISTS plans_owner_created_idx ON plans(owner_email, created_at)`,
    },
    {
      version: 15,
      sql: `ALTER TABLE plan_comments ADD COLUMN IF NOT EXISTS author_email TEXT;
ALTER TABLE plan_comments ADD COLUMN IF NOT EXISTS author_name TEXT`,
    },
    {
      version: 16,
      sql: `ALTER TABLE plan_comments ADD COLUMN IF NOT EXISTS parent_comment_id TEXT REFERENCES plan_comments(id);
CREATE INDEX IF NOT EXISTS plan_comments_parent_idx ON plan_comments(parent_comment_id)`,
    },
    {
      version: 17,
      sql: `ALTER TABLE plan_comments ADD COLUMN IF NOT EXISTS resolution_target TEXT;
ALTER TABLE plan_comments ADD COLUMN IF NOT EXISTS mentions_json TEXT;
ALTER TABLE plan_comments ADD COLUMN IF NOT EXISTS resolved_by TEXT;
ALTER TABLE plan_comments ADD COLUMN IF NOT EXISTS resolved_at TEXT;
CREATE INDEX IF NOT EXISTS plan_comments_resolution_idx ON plan_comments(plan_id, resolution_target, status, consumed_at)`,
    },
    {
      version: 18,
      sql: `CREATE TABLE IF NOT EXISTS plan_versions (
  id TEXT PRIMARY KEY,
  owner_email TEXT NOT NULL DEFAULT 'local@localhost',
  plan_id TEXT NOT NULL REFERENCES plans(id),
  title TEXT NOT NULL,
  snapshot_json TEXT NOT NULL,
  change_label TEXT,
  created_by TEXT NOT NULL DEFAULT 'agent',
  created_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS plan_versions_plan_owner_created_idx ON plan_versions(plan_id, owner_email, created_at)`,
    },
    {
      // `kind` distinguishes read-only visual recaps from editable plans. Add it
      // with a 'plan' default, then backfill existing recaps (identified by the
      // recap-review focus the create-visual-recap action sets).
      version: 19,
      sql: {
        postgres: `ALTER TABLE plans ADD COLUMN IF NOT EXISTS kind TEXT NOT NULL DEFAULT 'plan';
UPDATE plans SET kind = 'recap' WHERE kind = 'plan' AND current_focus = 'visual recap review'`,
        sqlite: `ALTER TABLE plans ADD COLUMN kind TEXT NOT NULL DEFAULT 'plan';
UPDATE plans SET kind = 'recap' WHERE kind = 'plan' AND current_focus = 'visual recap review'`,
      },
    },
    {
      // plan_events is an append-only log shared across every plan. loadPlanBundle
      // reads `WHERE plan_id = ? ORDER BY created_at` on each plan open, which
      // seq-scanned the whole growing table (plan_sections.plan_id and
      // plan_comments.plan_id are already covered by v7/v8/v17 composites; this
      // was the one hot-path lookup left unindexed).
      version: 20,
      sql: `CREATE INDEX IF NOT EXISTS plan_events_plan_created_idx ON plan_events(plan_id, created_at)`,
    },
    {
      // Token usage + derived cost for the LLM run that produced a recap. All
      // nullable and additive — only populated for kind="recap" rows by the PR
      // Visual Recap workflow. Cost is centicents (1/100¢), matching core's
      // token_usage.cost_cents_x100 so the two surfaces are directly comparable.
      version: 21,
      sql: {
        postgres: `ALTER TABLE plans ADD COLUMN IF NOT EXISTS usage_agent TEXT;
ALTER TABLE plans ADD COLUMN IF NOT EXISTS usage_model TEXT;
ALTER TABLE plans ADD COLUMN IF NOT EXISTS usage_input_tokens INTEGER;
ALTER TABLE plans ADD COLUMN IF NOT EXISTS usage_output_tokens INTEGER;
ALTER TABLE plans ADD COLUMN IF NOT EXISTS usage_cache_read_tokens INTEGER;
ALTER TABLE plans ADD COLUMN IF NOT EXISTS usage_cache_write_tokens INTEGER;
ALTER TABLE plans ADD COLUMN IF NOT EXISTS usage_cost_cents_x100 INTEGER;
ALTER TABLE plans ADD COLUMN IF NOT EXISTS usage_cost_source TEXT;
ALTER TABLE plans ADD COLUMN IF NOT EXISTS usage_recorded_at TEXT`,
        // SQLite has no ADD COLUMN IF NOT EXISTS; runMigrations only runs this
        // once (tracked in plans_migrations), so a plain ALTER per column is safe.
        sqlite: `ALTER TABLE plans ADD COLUMN usage_agent TEXT;
ALTER TABLE plans ADD COLUMN usage_model TEXT;
ALTER TABLE plans ADD COLUMN usage_input_tokens INTEGER;
ALTER TABLE plans ADD COLUMN usage_output_tokens INTEGER;
ALTER TABLE plans ADD COLUMN usage_cache_read_tokens INTEGER;
ALTER TABLE plans ADD COLUMN usage_cache_write_tokens INTEGER;
ALTER TABLE plans ADD COLUMN usage_cost_cents_x100 INTEGER;
ALTER TABLE plans ADD COLUMN usage_cost_source TEXT;
ALTER TABLE plans ADD COLUMN usage_recorded_at TEXT`,
      },
    },
    {
      version: 22,
      sql: `CREATE TABLE IF NOT EXISTS plan_assets (
  id TEXT PRIMARY KEY,
  plan_id TEXT NOT NULL REFERENCES plans(id),
  filename TEXT NOT NULL,
  mime_type TEXT NOT NULL,
  data TEXT NOT NULL,
  byte_size INTEGER NOT NULL,
  created_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS plan_assets_plan_idx ON plan_assets(plan_id, created_at)`,
    },
    {
      version: 23,
      sql: `ALTER TABLE plans ADD COLUMN IF NOT EXISTS source_url TEXT`,
    },
    {
      version: 24,
      sql: `CREATE TABLE IF NOT EXISTS plan_reports (
  id TEXT PRIMARY KEY,
  plan_id TEXT NOT NULL REFERENCES plans(id),
  reason TEXT NOT NULL,
  details TEXT,
  status TEXT NOT NULL DEFAULT 'open',
  reporter_email TEXT,
  reporter_name TEXT,
  page_url TEXT,
  occurrence_count INTEGER NOT NULL DEFAULT 1,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS plan_reports_plan_status_idx ON plan_reports(plan_id, status, updated_at);
CREATE INDEX IF NOT EXISTS plan_reports_status_updated_idx ON plan_reports(status, updated_at)`,
    },
    {
      version: 25,
      sql: `CREATE INDEX IF NOT EXISTS plan_reports_plan_reporter_status_idx ON plan_reports(plan_id, reporter_email, status)`,
    },
    {
      version: 26,
      sql: `CREATE INDEX IF NOT EXISTS plan_shares_resource_principal_idx ON plan_shares(resource_id, principal_type, principal_id)`,
    },
    {
      version: 27,
      sql: `CREATE INDEX IF NOT EXISTS plan_comments_plan_created_idx ON plan_comments(plan_id, created_at)`,
    },
    {
      version: 28,
      sql: `ALTER TABLE plan_comments ADD COLUMN IF NOT EXISTS deleted_at TEXT;
ALTER TABLE plan_comments ADD COLUMN IF NOT EXISTS deleted_by TEXT;
CREATE INDEX IF NOT EXISTS plan_comments_plan_deleted_created_idx ON plan_comments(plan_id, deleted_at, created_at)`,
    },
    {
      version: 29,
      sql: {
        postgres: `ALTER TABLE plans ADD COLUMN IF NOT EXISTS recap_idempotency_key TEXT;
CREATE INDEX IF NOT EXISTS plans_recap_idempotency_key_idx ON plans(recap_idempotency_key)`,
        sqlite: `ALTER TABLE plans ADD COLUMN recap_idempotency_key TEXT;
CREATE INDEX IF NOT EXISTS plans_recap_idempotency_key_idx ON plans(recap_idempotency_key)`,
      },
    },
    {
      version: 30,
      sql: `CREATE UNIQUE INDEX IF NOT EXISTS plans_recap_idempotency_key_unique_idx
ON plans(owner_email, COALESCE(org_id, ''), recap_idempotency_key)
WHERE kind = 'recap' AND recap_idempotency_key IS NOT NULL`,
    },
    {
      version: 31,
      sql: `ALTER TABLE plans ADD COLUMN IF NOT EXISTS deleted_at TEXT;
ALTER TABLE plans ADD COLUMN IF NOT EXISTS deleted_by TEXT;
CREATE INDEX IF NOT EXISTS plans_owner_deleted_updated_idx ON plans(owner_email, deleted_at, updated_at)`,
    },
    {
      version: 32,
      sql: {
        postgres: `ALTER TABLE plans ADD COLUMN IF NOT EXISTS source_type TEXT;
ALTER TABLE plans ADD COLUMN IF NOT EXISTS source_repo TEXT;
ALTER TABLE plans ADD COLUMN IF NOT EXISTS source_pr_number INTEGER;
ALTER TABLE plans ADD COLUMN IF NOT EXISTS source_pr_state TEXT;
ALTER TABLE plans ADD COLUMN IF NOT EXISTS source_pr_merged_at TEXT;
CREATE INDEX IF NOT EXISTS plans_recap_pr_merged_idx ON plans(kind, source_type, source_pr_merged_at, updated_at);
CREATE INDEX IF NOT EXISTS plans_source_pr_idx ON plans(source_repo, source_pr_number)`,
        sqlite: `ALTER TABLE plans ADD COLUMN source_type TEXT;
ALTER TABLE plans ADD COLUMN source_repo TEXT;
ALTER TABLE plans ADD COLUMN source_pr_number INTEGER;
ALTER TABLE plans ADD COLUMN source_pr_state TEXT;
ALTER TABLE plans ADD COLUMN source_pr_merged_at TEXT;
CREATE INDEX IF NOT EXISTS plans_recap_pr_merged_idx ON plans(kind, source_type, source_pr_merged_at, updated_at);
CREATE INDEX IF NOT EXISTS plans_source_pr_idx ON plans(source_repo, source_pr_number)`,
      },
    },
    {
      version: 33,
      sql: {
        postgres: `ALTER TABLE plans ADD COLUMN IF NOT EXISTS source_author_email TEXT;
ALTER TABLE plans ADD COLUMN IF NOT EXISTS source_author_name TEXT;
ALTER TABLE plans ADD COLUMN IF NOT EXISTS source_author_login TEXT`,
        sqlite: `ALTER TABLE plans ADD COLUMN source_author_email TEXT;
ALTER TABLE plans ADD COLUMN source_author_name TEXT;
ALTER TABLE plans ADD COLUMN source_author_login TEXT`,
      },
    },
    {
      // Repair migration for hosted databases that recorded an earlier migration
      // while still missing additive columns now present in schema.ts. Missing
      // optional plan columns make Drizzle's full-row access lookup throw before
      // plan pages can render or show a clean access error.
      version: 36,
      sql: {
        postgres: `ALTER TABLE plans ADD COLUMN IF NOT EXISTS deleted_at TEXT;
ALTER TABLE plans ADD COLUMN IF NOT EXISTS deleted_by TEXT;
ALTER TABLE plans ADD COLUMN IF NOT EXISTS source_type TEXT;
ALTER TABLE plans ADD COLUMN IF NOT EXISTS source_repo TEXT;
ALTER TABLE plans ADD COLUMN IF NOT EXISTS source_pr_number INTEGER;
ALTER TABLE plans ADD COLUMN IF NOT EXISTS source_pr_state TEXT;
ALTER TABLE plans ADD COLUMN IF NOT EXISTS source_pr_merged_at TEXT;
ALTER TABLE plans ADD COLUMN IF NOT EXISTS source_author_email TEXT;
ALTER TABLE plans ADD COLUMN IF NOT EXISTS source_author_name TEXT;
ALTER TABLE plans ADD COLUMN IF NOT EXISTS source_author_login TEXT;
ALTER TABLE plan_comments ADD COLUMN IF NOT EXISTS deleted_at TEXT;
ALTER TABLE plan_comments ADD COLUMN IF NOT EXISTS deleted_by TEXT;
CREATE INDEX IF NOT EXISTS plans_owner_deleted_updated_idx ON plans(owner_email, deleted_at, updated_at);
CREATE INDEX IF NOT EXISTS plans_recap_pr_merged_idx ON plans(kind, source_type, source_pr_merged_at, updated_at);
CREATE INDEX IF NOT EXISTS plans_source_pr_idx ON plans(source_repo, source_pr_number);
CREATE INDEX IF NOT EXISTS plan_comments_plan_deleted_created_idx ON plan_comments(plan_id, deleted_at, created_at)`,
      },
    },
    {
      // Denormalized summary fields for plan_versions, populated at snapshot-write
      // time (createPlanVersionSnapshot). list-plan-versions previously ran a
      // bare `.select()` that pulled every row's full snapshot_json (the entire
      // plan + sections blob) just to JSON.parse it and compute these same small
      // values via summarizePlanVersion on every list call. Nullable so existing
      // rows fall back to the legacy parse-on-read path until they're
      // re-snapshotted.
      //
      // Confirmed swallowed on the live plan Neon DB: plans_migrations' recorded
      // MAX(version) was 36 (this v37 entry had never actually run — a parallel
      // branch's DB state advanced past v37 without ever applying this specific
      // DDL), and information_schema confirmed plan_versions was missing all
      // seven of these columns. Named so it applies by name on next boot
      // regardless of any database's recorded MAX(version) — its SQL was
      // already idempotent (ADD COLUMN IF NOT EXISTS on postgres) before this
      // name was added.
      version: 37,
      name: "plan-versions-summary-columns",
      sql: {
        postgres: `ALTER TABLE plan_versions ADD COLUMN IF NOT EXISTS summary_status TEXT;
ALTER TABLE plan_versions ADD COLUMN IF NOT EXISTS summary_source TEXT;
ALTER TABLE plan_versions ADD COLUMN IF NOT EXISTS block_count INTEGER;
ALTER TABLE plan_versions ADD COLUMN IF NOT EXISTS section_count INTEGER;
ALTER TABLE plan_versions ADD COLUMN IF NOT EXISTS has_canvas BOOLEAN;
ALTER TABLE plan_versions ADD COLUMN IF NOT EXISTS has_prototype BOOLEAN;
ALTER TABLE plan_versions ADD COLUMN IF NOT EXISTS preview_text TEXT`,
        // `ADD COLUMN IF NOT EXISTS` is required on BOTH dialects: this entry
        // is tracked by `name:`, so it re-applies on any database that already
        // ran it under the legacy version gate. SQLite has no native
        // IF NOT EXISTS for ADD COLUMN, but the migration runner emulates it
        // (strips the clause and swallows duplicate-column errors) only for
        // statements that originally carry it — a plain ADD COLUMN would
        // throw on re-apply and crash local dev boot.
        sqlite: `ALTER TABLE plan_versions ADD COLUMN IF NOT EXISTS summary_status TEXT;
ALTER TABLE plan_versions ADD COLUMN IF NOT EXISTS summary_source TEXT;
ALTER TABLE plan_versions ADD COLUMN IF NOT EXISTS block_count INTEGER;
ALTER TABLE plan_versions ADD COLUMN IF NOT EXISTS section_count INTEGER;
ALTER TABLE plan_versions ADD COLUMN IF NOT EXISTS has_canvas INTEGER;
ALTER TABLE plan_versions ADD COLUMN IF NOT EXISTS has_prototype INTEGER;
ALTER TABLE plan_versions ADD COLUMN IF NOT EXISTS preview_text TEXT`,
      },
    },
  ],
  { table: "plans_migrations" },
);

/**
 * The migration list above is the authoritative source for tables, indexes,
 * and data transforms. `ensureAdditiveColumns` runs after it as a
 * belt-and-braces safety net for the failure mode where a column is added to
 * schema.ts without a matching hand-written ALTER migration, which silently
 * 500s every query touching a pre-existing production table. It only ever
 * adds missing columns — never drops, renames, or retypes anything — and any
 * failure here is logged and swallowed so it can never fail boot.
 */
export default async (nitroApp: any): Promise<void> => {
  await runPlanMigrations(nitroApp);
  try {
    const summary = await ensureAdditiveColumns({
      db: getDbExec(),
      tables: schemaTables,
    });
    if (summary.errors.length > 0) {
      console.warn(
        "[db] ensureAdditiveColumns completed with errors:",
        summary.errors,
      );
    }
  } catch (err) {
    // Never fail boot over the safety net itself — the authoritative
    // migrations above already ran.
    console.warn(
      "[db] ensureAdditiveColumns failed (non-fatal):",
      err instanceof Error ? err.message : err,
    );
  }
};