import { Database } from "bun:sqlite";
import { describe, expect, test } from "bun:test";
import { readFileSync } from "node:fs";
import path from "node:path";

const SCHEMA = readFileSync(
    path.resolve(import.meta.dir, "../src/db/schema.sql"),
    "utf-8",
);

function open() {
    const db = new Database(":memory:");
    db.run("PRAGMA foreign_keys=ON");
    return db;
}

function tables(db: Database): string[] {
    return db
        .query<{ name: string }, []>(
            "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name",
        )
        .all()
        .map((r) => r.name);
}

function columns(db: Database, table: string): string[] {
    return db
        .query<{ name: string }, []>(`PRAGMA table_info(${table})`)
        .all()
        .map((r) => r.name)
        .sort();
}

describe("schema.sql", () => {
    test("creates every table the Kysely Database interface declares", () => {
        const db = open();
        db.run(SCHEMA);
        expect(tables(db)).toEqual([
            "ci_artifacts",
            "ci_run_counters",
            "ci_runs",
            "ci_secrets",
            "ci_steps",
            "issue_comments",
            "issue_labels",
            "issue_reactions",
            "issues",
            "labels",
            "passkeys",
            "patch_comments",
            "patch_labels",
            "patch_reactions",
            "patches",
            "release_assets",
            "releases",
            "repositories",
            "sessions",
            "ssh_keys",
            "users",
        ]);
        db.close();
    });

    test("includes the columns that used to be added by ALTER migrations", () => {
        const db = open();
        db.run(SCHEMA);
        expect(columns(db, "users")).toContain("is_pending");
        expect(columns(db, "users")).toContain("register_application");
        expect(columns(db, "patches")).toContain("version");
        expect(columns(db, "repositories")).toContain("allow_user_labels");
        expect(columns(db, "ci_runs")).toContain("repo_run_id");
        db.close();
    });

    test("creates the query indexes", () => {
        const db = open();
        db.run(SCHEMA);
        const idx = db
            .query<{ name: string }, []>(
                "SELECT name FROM sqlite_master WHERE type='index' AND name LIKE 'idx_%'",
            )
            .all()
            .map((r) => r.name);
        expect(idx).toContain("idx_issues_repo_status");
        expect(idx).toContain("idx_patches_repo_status");
        expect(idx).toContain("idx_sessions_expires_at");
        expect(idx).toContain("idx_ci_runs_repo_id");
        db.close();
    });

    test("is idempotent — re-running changes nothing", () => {
        const db = open();
        db.run(SCHEMA);
        const before = tables(db);
        const userCols = columns(db, "users");
        db.run(SCHEMA);
        db.run(SCHEMA);
        expect(tables(db)).toEqual(before);
        expect(columns(db, "users")).toEqual(userCols);
        db.close();
    });

    test("applies cleanly over a pre-CI database, as an upgrade would", () => {
        const db = open();
        // Only the oldest core tables, mimicking a database created before
        // the CI and label features existed.
        db.run(`CREATE TABLE users (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          username TEXT NOT NULL UNIQUE,
          password_hash TEXT,
          created_at TEXT NOT NULL,
          avatar_version INTEGER NOT NULL DEFAULT 0
        )`);
        db.run(SCHEMA);
        expect(tables(db)).toContain("ci_runs");
        expect(tables(db)).toContain("labels");
        // The pre-existing table keeps its old shape; index.ts ALTERs the
        // missing columns in.
        expect(columns(db, "users")).not.toContain("is_pending");
        db.close();
    });
});