import assert from "node:assert/strict";
import { readFile } from "node:fs/promises";
import { DatabaseSync } from "node:sqlite";
import test from "node:test";
import {
  ACQUIRE_RETENTION_JOB_SQL,
  COMPLETE_RETENTION_JOB_SQL,
  DELETE_EXPIRED_CLAIM_ATTEMPTS_SQL,
  DELETE_EXPIRED_CLAIM_RATE_WINDOWS_SQL,
  DELETE_EXPIRED_IDEMPOTENCY_SQL,
  DELETE_EXPIRED_DEVICE_REGISTRATION_REPLAYS_SQL,
  DELETE_RETIRED_PENDING_PHONE_PRINCIPALS_SQL,
  DELETE_RETIRED_PENDING_PHONE_ROUTES_SQL,
  DELETE_RETIRED_PHONE_HANDOFF_TICKETS_SQL,
  deriveRetentionJobHealth,
  FAIL_RETENTION_JOB_SQL,
  getRetentionCutoffs,
  RETENTION_CRON,
  RETENTION_JOB_KEY,
  RETENTION_RUNNING_STALE_MS,
  RETIRE_EXPIRED_PAIRING_CODES_SQL,
} from "../db/retention.ts";

test("derives the documented 24-hour and 30-day retention cutoffs", () => {
  assert.deepEqual(getRetentionCutoffs("2026-08-12T12:00:00.000Z"), {
    now: "2026-08-12T12:00:00.000Z",
    claimRateBefore: "2026-08-11T12:00:00.000Z",
    claimAttemptBefore: "2026-07-13T12:00:00.000Z",
    handoffTicketBefore: "2026-08-11T12:00:00.000Z",
  });
  assert.throws(() => getRetentionCutoffs("not-a-date"), /ISO timestamp/);
});

test("retires only expired pairing secrets and deletes only records older than their cutoffs", () => {
  const db = new DatabaseSync(":memory:");
  db.exec(`
    CREATE TABLE pairing_requests (
      id TEXT PRIMARY KEY,
      code_hash TEXT NOT NULL UNIQUE,
      expires_at TEXT NOT NULL
    );
    CREATE TABLE pairing_claim_rate_limits (
      source_hash TEXT NOT NULL,
      window_start TEXT NOT NULL,
      PRIMARY KEY (source_hash, window_start)
    );
    CREATE TABLE pairing_claim_attempts (
      id TEXT PRIMARY KEY,
      created_at TEXT NOT NULL
    );
    CREATE TABLE idempotency_records (
      scope TEXT NOT NULL,
      key TEXT NOT NULL,
      expires_at TEXT NOT NULL,
      PRIMARY KEY (scope, key)
    );
    CREATE TABLE phone_handoff_tickets (
      id TEXT PRIMARY KEY,
      ticket_hash TEXT NOT NULL,
      tenant_id TEXT NOT NULL,
      expires_at TEXT NOT NULL,
      consumed_at TEXT,
      completed_phone_id TEXT,
      completed_phone_token_hash TEXT,
      completed_route_handle_hash TEXT
    );
    CREATE TABLE relay_phone_principals (
      phone_id TEXT PRIMARY KEY,
      tenant_id TEXT NOT NULL,
      token_hash TEXT NOT NULL,
      status TEXT NOT NULL
    );
    CREATE TABLE phone_route_handles (
      id TEXT PRIMARY KEY,
      handle_hash TEXT NOT NULL,
      tenant_id TEXT NOT NULL,
      phone_id TEXT NOT NULL,
      status TEXT NOT NULL
    );
    CREATE TABLE device_registration_replays (
      pairing_id TEXT PRIMARY KEY,
      expires_at TEXT NOT NULL
    );

    INSERT INTO pairing_requests VALUES
      ('pair_old', 'secret-old', '2026-08-12T11:59:59.999Z'),
      ('pair_boundary', 'secret-boundary', '2026-08-12T12:00:00.000Z'),
      ('pair_live', 'secret-live', '2026-08-12T12:00:00.001Z');
    INSERT INTO pairing_claim_rate_limits VALUES
      ('old', '2026-08-11T11:59:59.999Z'),
      ('boundary', '2026-08-11T12:00:00.000Z');
    INSERT INTO pairing_claim_attempts VALUES
      ('old', '2026-07-13T11:59:59.999Z'),
      ('boundary', '2026-07-13T12:00:00.000Z');
    INSERT INTO idempotency_records VALUES
      ('scope', 'expired', '2026-08-12T12:00:00.000Z'),
      ('scope', 'live', '2026-08-12T12:00:00.001Z');
    INSERT INTO phone_handoff_tickets VALUES
      ('handoff_expired', 'digest-expired', 'tenant_a', '2026-08-11T11:59:59.999Z', NULL, 'phone_expired', 'token-expired', 'route-expired'),
      ('handoff_consumed', 'digest-consumed', 'tenant_a', '2026-08-12T12:00:00.001Z', '2026-08-11T12:00:00.000Z', 'phone_consumed', 'token-consumed', 'route-consumed'),
      ('handoff_boundary', 'digest-boundary', 'tenant_a', '2026-08-11T12:00:00.000Z', NULL, 'phone_boundary', 'token-boundary', 'route-boundary'),
      ('handoff_active_old', 'digest-active', 'tenant_a', '2026-08-11T11:59:59.999Z', NULL, 'phone_active', 'token-active', 'route-active'),
      ('handoff_live', 'digest-live', 'tenant_a', '2026-08-11T12:00:00.001Z', NULL, 'phone_live', 'token-live', 'route-live');
    INSERT INTO relay_phone_principals VALUES
      ('phone_expired', 'tenant_a', 'token-expired', 'pending'),
      ('phone_consumed', 'tenant_a', 'token-consumed', 'pending'),
      ('phone_boundary', 'tenant_a', 'token-boundary', 'pending'),
      ('phone_active', 'tenant_a', 'token-active', 'active'),
      ('phone_live', 'tenant_a', 'token-live', 'pending');
    INSERT INTO phone_route_handles VALUES
      ('handle_expired', 'route-expired', 'tenant_a', 'phone_expired', 'pending'),
      ('handle_consumed', 'route-consumed', 'tenant_a', 'phone_consumed', 'pending'),
      ('handle_boundary', 'route-boundary', 'tenant_a', 'phone_boundary', 'pending'),
      ('handle_active', 'route-active', 'tenant_a', 'phone_active', 'active'),
      ('handle_live', 'route-live', 'tenant_a', 'phone_live', 'pending');
    INSERT INTO device_registration_replays VALUES
      ('replay_expired', '2026-08-12T12:00:00.000Z'),
      ('replay_live', '2026-08-12T12:00:00.001Z');
  `);

  const cutoffs = getRetentionCutoffs("2026-08-12T12:00:00.000Z");
  const changes = [
    db.prepare(RETIRE_EXPIRED_PAIRING_CODES_SQL).run(cutoffs.now).changes,
    db.prepare(DELETE_EXPIRED_CLAIM_RATE_WINDOWS_SQL).run(cutoffs.claimRateBefore).changes,
    db.prepare(DELETE_EXPIRED_CLAIM_ATTEMPTS_SQL).run(cutoffs.claimAttemptBefore).changes,
    db.prepare(DELETE_EXPIRED_IDEMPOTENCY_SQL).run(cutoffs.now).changes,
    db.prepare(DELETE_RETIRED_PENDING_PHONE_ROUTES_SQL)
      .run(cutoffs.handoffTicketBefore, cutoffs.handoffTicketBefore).changes,
    db.prepare(DELETE_RETIRED_PENDING_PHONE_PRINCIPALS_SQL)
      .run(cutoffs.handoffTicketBefore, cutoffs.handoffTicketBefore).changes,
    db.prepare(DELETE_RETIRED_PHONE_HANDOFF_TICKETS_SQL)
      .run(cutoffs.handoffTicketBefore, cutoffs.handoffTicketBefore).changes,
    db.prepare(DELETE_EXPIRED_DEVICE_REGISTRATION_REPLAYS_SQL).run(cutoffs.now).changes,
  ].map(Number);
  assert.deepEqual(changes, [2, 1, 1, 1, 3, 3, 4, 1]);
  assert.deepEqual(
    db.prepare("SELECT id, code_hash FROM pairing_requests ORDER BY id").all().map((row) => ({ ...row })),
    [
      { id: "pair_boundary", code_hash: "expired:pair_boundary" },
      { id: "pair_live", code_hash: "secret-live" },
      { id: "pair_old", code_hash: "expired:pair_old" },
    ],
  );
  assert.equal(db.prepare("SELECT COUNT(*) AS count FROM device_registration_replays").get().count, 1);
  assert.equal(db.prepare("SELECT COUNT(*) AS count FROM pairing_claim_rate_limits").get().count, 1);
  assert.equal(db.prepare("SELECT COUNT(*) AS count FROM pairing_claim_attempts").get().count, 1);
  assert.equal(db.prepare("SELECT COUNT(*) AS count FROM idempotency_records").get().count, 1);
  assert.deepEqual(
    db.prepare("SELECT id FROM phone_handoff_tickets ORDER BY id").all().map((row) => row.id),
    ["handoff_live"],
  );
  assert.deepEqual(
    db.prepare("SELECT phone_id, status FROM relay_phone_principals ORDER BY phone_id").all().map((row) => ({ ...row })),
    [
      { phone_id: "phone_active", status: "active" },
      { phone_id: "phone_live", status: "pending" },
    ],
  );
  assert.deepEqual(
    db.prepare("SELECT id, status FROM phone_route_handles ORDER BY id").all().map((row) => ({ ...row })),
    [
      { id: "handle_active", status: "active" },
      { id: "handle_live", status: "pending" },
    ],
  );

  const repeated = [
    db.prepare(RETIRE_EXPIRED_PAIRING_CODES_SQL).run(cutoffs.now).changes,
    db.prepare(DELETE_EXPIRED_CLAIM_RATE_WINDOWS_SQL).run(cutoffs.claimRateBefore).changes,
    db.prepare(DELETE_EXPIRED_CLAIM_ATTEMPTS_SQL).run(cutoffs.claimAttemptBefore).changes,
    db.prepare(DELETE_EXPIRED_IDEMPOTENCY_SQL).run(cutoffs.now).changes,
    db.prepare(DELETE_RETIRED_PENDING_PHONE_ROUTES_SQL)
      .run(cutoffs.handoffTicketBefore, cutoffs.handoffTicketBefore).changes,
    db.prepare(DELETE_RETIRED_PENDING_PHONE_PRINCIPALS_SQL)
      .run(cutoffs.handoffTicketBefore, cutoffs.handoffTicketBefore).changes,
    db.prepare(DELETE_RETIRED_PHONE_HANDOFF_TICKETS_SQL)
      .run(cutoffs.handoffTicketBefore, cutoffs.handoffTicketBefore).changes,
    db.prepare(DELETE_EXPIRED_DEVICE_REGISTRATION_REPLAYS_SQL).run(cutoffs.now).changes,
  ].map(Number);
  assert.deepEqual(repeated, [0, 0, 0, 0, 0, 0, 0, 0]);
  db.close();
});

test("derives healthy, overdue, running, stalled, failed, and invalid automatic maintenance states", () => {
  const record = {
    key: RETENTION_JOB_KEY,
    state: "succeeded",
    run_id: "maintenance_1",
    trigger: "scheduled",
    scheduled_at: "2026-08-12T00:00:00.000Z",
    started_at: "2026-08-12T00:00:01.000Z",
    completed_at: "2026-08-12T00:00:02.000Z",
    last_success_at: "2026-08-12T00:00:02.000Z",
    result_json: "{}",
    error_code: null,
    consecutive_failures: 0,
    run_count: 1,
    created_at: "2026-08-12T00:00:01.000Z",
    updated_at: "2026-08-12T00:00:02.000Z",
  };

  assert.equal(deriveRetentionJobHealth(null).state, "never_run");
  assert.equal(
    deriveRetentionJobHealth(record, "2026-08-13T12:00:02.000Z").state,
    "healthy",
  );
  assert.equal(
    deriveRetentionJobHealth(record, "2026-08-13T12:00:02.001Z").state,
    "overdue",
  );
  assert.equal(
    deriveRetentionJobHealth(
      { ...record, state: "running", completed_at: null, last_success_at: null },
      "2026-08-12T00:30:01.000Z",
    ).state,
    "running",
  );
  assert.equal(
    deriveRetentionJobHealth(
      { ...record, state: "running", completed_at: null, last_success_at: null },
      "2026-08-12T00:30:01.001Z",
    ).state,
    "stalled",
  );
  assert.equal(
    deriveRetentionJobHealth(
      { ...record, state: "failed", error_code: "retention_maintenance_failed", consecutive_failures: 1 },
      "2026-08-12T00:05:00.000Z",
    ).state,
    "failed",
  );
  assert.equal(
    deriveRetentionJobHealth({ ...record, trigger: "spoofed" }, "2026-08-12T00:05:00.000Z").state,
    "invalid",
  );
  assert.equal(
    deriveRetentionJobHealth({ ...record, error_code: "unsafe details" }, "2026-08-12T00:05:00.000Z").state,
    "invalid",
  );
  assert.equal(
    deriveRetentionJobHealth(
      { ...record, started_at: "2026-08-12T00:10:00.000Z" },
      "2026-08-12T00:05:00.000Z",
    ).state,
    "invalid",
  );
});

test("fences overlapping automatic maintenance and preserves the latest successful result", async () => {
  const migration = await readFile(
    new URL("../drizzle/0009_flat_robbie_robertson.sql", import.meta.url),
    "utf8",
  );
  const db = new DatabaseSync(":memory:");
  db.exec(migration);

  const acquire = (runId, scheduledAt, startedAt) => db
    .prepare(ACQUIRE_RETENTION_JOB_SQL)
    .run(
      RETENTION_JOB_KEY,
      runId,
      "scheduled",
      scheduledAt,
      startedAt,
      startedAt,
      startedAt,
      new Date(new Date(startedAt).getTime() - RETENTION_RUNNING_STALE_MS).toISOString(),
    );

  assert.equal(Number(acquire("run_1", "2026-08-12T00:00:00.000Z", "2026-08-12T00:00:01.000Z").changes), 1);
  assert.equal(Number(acquire("run_duplicate", "2026-08-12T00:01:00.000Z", "2026-08-12T00:01:01.000Z").changes), 0);
  assert.equal(Number(acquire("run_2", "2026-08-12T00:31:00.001Z", "2026-08-12T00:31:01.001Z").changes), 1);

  const complete = db.prepare(COMPLETE_RETENTION_JOB_SQL);
  assert.equal(Number(complete.run(
    "2026-08-12T00:31:02.000Z",
    "2026-08-12T00:31:02.000Z",
    "{}",
    "2026-08-12T00:31:02.000Z",
    RETENTION_JOB_KEY,
    "run_1",
  ).changes), 0);
  assert.equal(Number(complete.run(
    "2026-08-12T00:31:02.000Z",
    "2026-08-12T00:31:02.000Z",
    JSON.stringify({ deletedClaimAttempts: 2 }),
    "2026-08-12T00:31:02.000Z",
    RETENTION_JOB_KEY,
    "run_2",
  ).changes), 1);

  assert.equal(Number(acquire("run_3", "2026-08-13T00:00:00.000Z", "2026-08-13T00:00:01.000Z").changes), 1);
  assert.equal(Number(db.prepare(FAIL_RETENTION_JOB_SQL).run(
    "2026-08-13T00:00:02.000Z",
    "retention_maintenance_failed",
    "2026-08-13T00:00:02.000Z",
    RETENTION_JOB_KEY,
    "run_3",
  ).changes), 1);

  const row = db.prepare("SELECT * FROM maintenance_jobs WHERE key = ?").get(RETENTION_JOB_KEY);
  assert.equal(row.state, "failed");
  assert.equal(row.run_id, "run_3");
  assert.equal(Number(row.run_count), 3);
  assert.equal(Number(row.consecutive_failures), 1);
  assert.equal(row.last_success_at, "2026-08-12T00:31:02.000Z");
  assert.equal(db.prepare("SELECT COUNT(*) AS count FROM maintenance_jobs").get().count, 1);
  assert.match(
    db.prepare("EXPLAIN QUERY PLAN SELECT * FROM maintenance_jobs WHERE key = ?").get(RETENTION_JOB_KEY).detail,
    /USING INDEX sqlite_autoindex_maintenance_jobs_1/,
  );
  db.close();
});

test("keeps manual and scheduled retention bounded, observable, and auditable", async () => {
  const [route, repository, adminActions, adminPage, inventory, runner, worker, vite, bootstrap, runbook] = await Promise.all([
    readFile(new URL("../app/api/admin/retention/route.ts", import.meta.url), "utf8"),
    readFile(new URL("../db/repository.ts", import.meta.url), "utf8"),
    readFile(new URL("../app/admin/AdminActions.tsx", import.meta.url), "utf8"),
    readFile(new URL("../app/admin/page.tsx", import.meta.url), "utf8"),
    readFile(new URL("../docs/data-inventory.md", import.meta.url), "utf8"),
    readFile(new URL("../db/retention-runner.ts", import.meta.url), "utf8"),
    readFile(new URL("../worker/index.ts", import.meta.url), "utf8"),
    readFile(new URL("../vite.config.ts", import.meta.url), "utf8"),
    readFile(new URL("../db/bootstrap.ts", import.meta.url), "utf8"),
    readFile(new URL("../docs/retention-maintenance.md", import.meta.url), "utf8"),
  ]);

  assert.match(route, /getCloudViewer/);
  assert.match(route, /!viewer\.isAdmin/);
  assert.match(route, /readJsonMutation/);
  assert.match(route, /confirmed: payload\.confirmed === true/);
  assert.match(repository, /retention_confirmation_required/);
  assert.match(repository, /privacy\.retention_cleanup/);
  assert.match(repository, /db\.batch\(\[/);
  assert.match(repository, /DELETE_EXPIRED_IDEMPOTENCY_SQL/);
  assert.match(repository, /DELETE_RETIRED_PHONE_HANDOFF_TICKETS_SQL/);
  assert.match(repository, /DELETE_EXPIRED_DEVICE_REGISTRATION_REPLAYS_SQL/);
  assert.doesNotMatch(repository.slice(repository.indexOf("export async function runRetentionMaintenance"), repository.indexOf("export async function createFeedback")), /DELETE FROM (?:accounts|hosts|audit_events|beta_feedback|tenant_instances)/);
  assert.match(adminActions, /只处理已经到期的技术记录/);
  assert.match(adminActions, /不会删除账户、主机、反馈、审计、租户卷或备份/);
  assert.match(adminPage, /自动清理正常/);
  assert.match(adminPage, /连续失败/);
  assert.match(inventory, /成功认领会在同一原子写中烧毁原摘要/);
  assert.match(runner, /await ensureDatabase\(\)/);
  const runnerBody = runner.slice(runner.indexOf("export async function runScheduledRetentionMaintenance"));
  assert.ok(runnerBody.indexOf("await ensureDatabase()") < runnerBody.indexOf(".prepare(ACQUIRE_RETENTION_JOB_SQL)"));
  assert.match(runner, /system:scheduled-retention/);
  assert.match(runner, /FAIL_RETENTION_JOB_SQL/);
  assert.match(runner, /throw error/);
  assert.match(worker, /async scheduled\(controller: ScheduledController/);
  assert.match(worker, /runScheduledRetentionMaintenance\(env\.DB, controller\.scheduledTime\)/);
  assert.match(vite, /triggers: \{ crons: \[RETENTION_CRON\] \}/);
  assert.equal(RETENTION_CRON, "17 18 * * *");
  assert.match(bootstrap, /0009_flat_robbie_robertson/);
  assert.match(runbook, /不会删除账户、主机、设备凭据、反馈、故障公告、审计、租户、开通任务、卷或备份/);
});