/**
 * Seeded upgrade proof: the point of the 2026 migration re-baseline.
 *
 * We build a genuine pre-squash "old user DB" from the preserved 11-migration
 * history, seed it with real rows across the core tables, then reconcile it
 * against the CURRENT baseline (once via the on-disk folder, once via the
 * embedded constants). The invariants:
 *
 *   1. Every seeded row is still present and byte-for-byte unchanged.
 *   2. Every table the desktop migrations create exists afterwards (derived via
 *      expectedTableCount() — currently 49) — including the ones the old history
 *      never had (e.g. email_log, waitlist_entries, git_commits).
 *   3. reconcile does not throw.
 *
 * If reconcile ever throws here — e.g. a baseline column is NOT NULL / UNIQUE
 * without a default and can't be ALTER-added onto a legacy table — that is a
 * real migration bug, not a test problem.
 */

import type { Database } from "bun:sqlite";
import { afterEach, beforeEach, describe, expect, test } from "bun:test";
import { mkdtempSync, rmSync } from "node:fs";
import { tmpdir } from "node:os";
import { join } from "node:path";
import {
  applyLegacyMigrations,
  expectedTableCount,
  MIGRATIONS_FOLDER,
} from "../fixtures";
import { EMBEDDED_MIGRATIONS } from "../migrations-embedded";
import { type MigrationSource, reconcile } from "../reconcile";

let tmpDir: string;
let dbPath: string;

beforeEach(() => {
  tmpDir = mkdtempSync(join(tmpdir(), "scribe-upgrade-"));
  dbPath = join(tmpDir, "scribe.db");
});

afterEach(() => {
  rmSync(tmpDir, { recursive: true, force: true });
});

type SeedRows = {
  user: { id: string; name: string; email: string };
  project: { id: string; name: string };
  invoice: {
    id: string;
    invoice_number: string;
    subtotal: number;
    total: number;
  };
  lineItems: Array<{
    id: string;
    description: string;
    quantity: number;
    amount: number;
  }>;
  events: Array<{ id: string; timestamp: number; source: string }>;
};

const SEED: SeedRows = {
  user: { id: "u_seed", name: "Ada Seed", email: "ada@example.com" },
  project: { id: "p_seed", name: "Seed Project" },
  invoice: {
    id: "inv_seed",
    invoice_number: "INV-2026-001",
    subtotal: 150000,
    total: 150000,
  },
  lineItems: [
    { id: "li_seed_1", description: "Design", quantity: 300, amount: 90000 },
    { id: "li_seed_2", description: "Build", quantity: 200, amount: 60000 },
  ],
  events: [
    { id: "ev_seed_1", timestamp: 1_700_000_000, source: "git" },
    { id: "ev_seed_2", timestamp: 1_700_000_100, source: "claude" },
    { id: "ev_seed_3", timestamp: 1_700_000_200, source: "manual" },
  ],
};

/** Seed the legacy-shape DB. Only inserts NOT-NULL columns that exist pre-squash. */
function seed(sqlite: Database): void {
  sqlite
    .prepare(
      `INSERT INTO "user" (id, name, email, email_verified, created_at, updated_at)
       VALUES (?, ?, ?, ?, ?, ?)`,
    )
    .run(SEED.user.id, SEED.user.name, SEED.user.email, 1, 1000, 2000);

  sqlite
    .prepare(`INSERT INTO projects (id, name) VALUES (?, ?)`)
    .run(SEED.project.id, SEED.project.name);

  sqlite
    .prepare(
      `INSERT INTO invoices (id, user_id, invoice_number, issue_date, subtotal, total)
       VALUES (?, ?, ?, ?, ?, ?)`,
    )
    .run(
      SEED.invoice.id,
      SEED.user.id,
      SEED.invoice.invoice_number,
      1_700_000_000,
      SEED.invoice.subtotal,
      SEED.invoice.total,
    );

  for (const li of SEED.lineItems) {
    // quantity_type 'units' so the embedded set's 0011 hours->minutes data
    // migration deliberately leaves these rows untouched (its semantics are
    // covered by invoice-hours-migration.test.ts); here we only prove the rows
    // survive the schema upgrade unchanged.
    sqlite
      .prepare(
        `INSERT INTO invoice_line_items (id, invoice_id, description, quantity, quantity_type, rate, amount)
         VALUES (?, ?, ?, ?, 'units', ?, ?)`,
      )
      .run(li.id, SEED.invoice.id, li.description, li.quantity, 300, li.amount);
  }

  for (const ev of SEED.events) {
    sqlite
      .prepare(
        `INSERT INTO timeline_events (id, timestamp, source) VALUES (?, ?, ?)`,
      )
      .run(ev.id, ev.timestamp, ev.source);
  }
}

function assertSeedIntact(sqlite: Database): void {
  expect(
    sqlite
      .prepare(`SELECT id, name, email FROM "user" WHERE id = ?`)
      .get(SEED.user.id),
  ).toEqual(SEED.user);

  expect(
    sqlite
      .prepare(`SELECT id, name FROM projects WHERE id = ?`)
      .get(SEED.project.id),
  ).toEqual(SEED.project);

  expect(
    sqlite
      .prepare(
        `SELECT id, invoice_number, subtotal, total FROM invoices WHERE id = ?`,
      )
      .get(SEED.invoice.id),
  ).toEqual(SEED.invoice);

  expect(
    sqlite
      .prepare(
        `SELECT id, description, quantity, amount FROM invoice_line_items ORDER BY id`,
      )
      .all(),
  ).toEqual(SEED.lineItems);

  expect(
    sqlite
      .prepare(`SELECT id, timestamp, source FROM timeline_events ORDER BY id`)
      .all(),
  ).toEqual(SEED.events);
}

function baselineTables(sqlite: Database): Set<string> {
  return new Set(
    (
      sqlite
        .prepare(
          "SELECT name FROM sqlite_master WHERE type = 'table' " +
            "AND name NOT LIKE 'sqlite_%' AND name != '__drizzle_migrations'",
        )
        .all() as Array<{ name: string }>
    ).map((r) => r.name),
  );
}

// Tables the old 11-migration history never created; the baseline adds them.
const NEWLY_ADDED_TABLES = [
  "commit_provenance",
  "email_log",
  "email_preferences",
  "git_commits",
  "github_installations",
  "github_repositories",
  "jwks",
  "oauth_access_token",
  "oauth_client",
  "oauth_consent",
  "oauth_refresh_token",
  "waitlist_entries",
];

describe("reconcile dedups a drifted table so a new unique index can be created", () => {
  // The desktop's evidence_messages had a NON-unique index on
  // (provider_id, external_message_id); schema.ts later made it UNIQUE. Real
  // desktop DBs accumulated duplicates that the baseline's CREATE UNIQUE INDEX
  // cannot apply — reconcile must dedup and continue instead of rolling back
  // the entire baseline (which stranded the DB missing 12 tables). Regression
  // for the v0.0.92-internal.1 breakage.
  test("duplicate evidence_messages are deduped; unique index + all baseline tables land", () => {
    const sqlite = applyLegacyMigrations(dbPath);
    sqlite.exec("PRAGMA foreign_keys = OFF");
    const insMsg = sqlite.prepare(
      `INSERT INTO evidence_messages (id, thread_id, provider_id, external_message_id, role, timestamp)
       VALUES (?, 't1', 'claude', ?, 'user', ?)`,
    );
    // 3 duplicates of (claude, m1), 2 rows with NULL external (never conflict),
    // 1 distinct message.
    insMsg.run("a1", "m1", 10);
    insMsg.run("a2", "m1", 11);
    insMsg.run("a3", "m1", 12);
    insMsg.run("n1", null, 13);
    insMsg.run("n2", null, 14);
    insMsg.run("d1", "m2", 15);

    expect(() =>
      reconcile(sqlite, { folder: MIGRATIONS_FOLDER }),
    ).not.toThrow();

    // Unique index now exists.
    expect(
      sqlite
        .prepare(
          "SELECT COUNT(*) c FROM sqlite_master WHERE type='index' AND name='evidence_messages_external_uidx'",
        )
        .get(),
    ).toEqual({ c: 1 });
    // Exactly one row survives the (claude, m1) group; NULLs and the distinct
    // row are untouched. 6 seeded -> 4 (removed 2 of the 3 m1 dups).
    expect(
      sqlite
        .prepare(
          "SELECT COUNT(*) c FROM evidence_messages WHERE provider_id='claude' AND external_message_id='m1'",
        )
        .get(),
    ).toEqual({ c: 1 });
    expect(
      sqlite
        .prepare(
          "SELECT COUNT(*) c FROM evidence_messages WHERE external_message_id IS NULL",
        )
        .get(),
    ).toEqual({ c: 2 });
    expect(
      sqlite.prepare("SELECT COUNT(*) c FROM evidence_messages").get(),
    ).toEqual({ c: 4 });
    // And the rest of the baseline still landed.
    expect(
      sqlite
        .prepare(
          "SELECT COUNT(*) c FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' AND name != '__drizzle_migrations'",
        )
        .get(),
    ).toEqual({ c: expectedTableCount() });
    sqlite.close();
  });
});

describe("pre-squash legacy DB upgrades to the current baseline without data loss", () => {
  const variants: Array<[string, () => MigrationSource]> = [
    ["folder (desktop bundle)", () => ({ folder: MIGRATIONS_FOLDER })],
    ["embedded (published CLI)", () => ({ embedded: EMBEDDED_MIGRATIONS })],
  ];

  for (const [label, makeSource] of variants) {
    test(`via ${label}: seeded rows survive, all baseline tables present`, () => {
      const sqlite = applyLegacyMigrations(dbPath);
      seed(sqlite);

      // Sanity: legacy shape lacks the tables the baseline later added.
      const legacyTables = baselineTables(sqlite);
      for (const t of NEWLY_ADDED_TABLES) {
        expect(legacyTables.has(t)).toBe(false);
      }

      // The upgrade. A throw here is a real migration bug — do not swallow it.
      expect(() => reconcile(sqlite, makeSource())).not.toThrow();

      // (1) Every seeded row is present and unchanged.
      assertSeedIntact(sqlite);

      // (2) Every baseline table exists, including the newly added ones.
      const after = baselineTables(sqlite);
      expect(after.size).toBe(expectedTableCount());
      for (const t of NEWLY_ADDED_TABLES) {
        expect(after.has(t)).toBe(true);
      }

      sqlite.close();
    });
  }
});