import { Database as BunDatabase } from "bun:sqlite";
import { mkdirSync, readFileSync } from "node:fs";
import path from "node:path";
import { type Generated, Kysely, type Selectable } from "kysely";
import { BunSqliteDialect } from "kysely-bun-sqlite";

import { paths } from "../constants.ts";

interface UserTable {
    id: Generated<number>;
    username: string;
    password_hash: string | null;
    created_at: string;
    avatar_version: Generated<number>;
    is_pending: Generated<number>;
    register_application: string | null;
}

interface PasskeyTable {
    id: Generated<number>;
    user_id: number;
    credential_id: string;
    public_key: string;
    counter: number;
    created_at: string;
}

interface SessionTable {
    id: string;
    user_id: number;
    expires_at: string;
    created_at: string;
}

interface RepositoryTable {
    id: Generated<number>;
    name: string;
    description: string | null;
    is_private: number;
    is_pinned: Generated<number>;
    default_branch: string;
    created_at: string;
    issue_seq: Generated<number>;
    patch_seq: Generated<number>;
    issue_template: string | null;
    patch_template: string | null;
    allow_user_labels: Generated<number>;
}

interface IssueTable {
    id: Generated<number>;
    repo_id: number;
    author_id: number | null;
    number: number;
    title: string;
    body: string;
    status: string;
    created_at: string;
    updated_at: string;
    edited_at: string | null;
}

interface IssueCommentTable {
    id: Generated<number>;
    issue_id: number;
    author_id: number | null;
    body: string;
    created_at: string;
    edited_at: string | null;
}

interface IssueReactionTable {
    id: Generated<number>;
    issue_id: number;
    comment_id: number | null;
    user_id: number;
    emoji: string;
}

interface PatchTable {
    id: Generated<number>;
    repo_id: number;
    author_id: number | null;
    number: number;
    title: string;
    description: string;
    patch_content: string;
    status: string;
    author_name: string;
    author_email: string;
    created_at: string;
    updated_at: string;
    edited_at: string | null;
    version: string;
}

interface PatchCommentTable {
    id: Generated<number>;
    patch_id: number;
    author_id: number | null;
    body: string;
    created_at: string;
    edited_at: string | null;
}

interface PatchReactionTable {
    id: Generated<number>;
    patch_id: number;
    comment_id: number | null;
    user_id: number;
    emoji: string;
}

interface SshKeyTable {
    id: Generated<number>;
    user_id: number;
    name: string;
    public_key: string;
    fingerprint: string;
    created_at: string;
}

interface ReleaseTable {
    id: Generated<number>;
    repo_id: number;
    tag_name: string | null;
    name: string;
    notes: string | null;
    include_source_code: number;
    created_at: string;
}

interface ReleaseAssetTable {
    id: Generated<number>;
    release_id: number;
    filename: string;
    size: number;
    created_at: string;
}

interface LabelTable {
    id: Generated<number>;
    repo_id: number;
    name: string;
    color: string;
    created_at: string;
}

interface IssueLabelTable {
    issue_id: number;
    label_id: number;
}

interface PatchLabelTable {
    patch_id: number;
    label_id: number;
}

interface CiRunTable {
    id: Generated<number>;
    repo_id: number;
    triggered_by: number | null;
    trigger_source: string;
    commit_sha: string | null;
    commit_branch: string | null;
    commit_tag: string | null;
    status: string;
    variable_overrides: string | null;
    started_at: string | null;
    finished_at: string | null;
    created_at: Generated<string>;
    repo_run_id: number | null;
}

interface CiStepTable {
    id: Generated<number>;
    run_id: number;
    name: string;
    status: string;
    exit_code: number | null;
    started_at: string | null;
    finished_at: string | null;
    log: Generated<string>;
}

interface CiArtifactTable {
    id: Generated<number>;
    run_id: number;
    filename: string;
    size: number;
    created_at: Generated<string>;
}

interface CiSecretTable {
    id: Generated<number>;
    repo_id: number;
    name: string;
    value: string;
    description: string | null;
    created_at: Generated<string>;
}

// Per-repo monotonic counter for the human-facing run number (#1, #2, …).
// Incremented atomically on each trigger so numbers never collide or repeat
// after history pruning — unlike deriving the number from a live row count.
interface CiRunCounterTable {
    repo_id: number;
    last_run_id: number;
}

export interface Database {
    users: UserTable;
    passkeys: PasskeyTable;
    sessions: SessionTable;
    repositories: RepositoryTable;
    issues: IssueTable;
    issue_comments: IssueCommentTable;
    issue_reactions: IssueReactionTable;
    patches: PatchTable;
    patch_comments: PatchCommentTable;
    patch_reactions: PatchReactionTable;
    ssh_keys: SshKeyTable;
    releases: ReleaseTable;
    release_assets: ReleaseAssetTable;
    labels: LabelTable;
    issue_labels: IssueLabelTable;
    patch_labels: PatchLabelTable;
    ci_runs: CiRunTable;
    ci_steps: CiStepTable;
    ci_artifacts: CiArtifactTable;
    ci_secrets: CiSecretTable;
    ci_run_counters: CiRunCounterTable;
}

// Selectable row types (id is plain number, as returned by queries)
export type RepositoryRow = Selectable<RepositoryTable>;
export type IssueRow = Selectable<IssueTable>;
export type IssueCommentRow = Selectable<IssueCommentTable>;
export type PatchRow = Selectable<PatchTable>;
export type PatchCommentRow = Selectable<PatchCommentTable>;
export type ReleaseRow = Selectable<ReleaseTable>;
export type ReleaseAssetRow = Selectable<ReleaseAssetTable>;
export type LabelRow = Selectable<LabelTable>;
export type CiStepRow = Selectable<CiStepTable>;
export type CiArtifactRow = Selectable<CiArtifactTable>;

const SCHEMA = readFileSync(path.join(import.meta.dir, "schema.sql"), "utf-8");

/**
 * Bring a database file up to date.
 *
 * `schema.sql` is the single source of truth: every statement in it is
 * `IF NOT EXISTS`, so running it on an existing database only fills in
 * whatever is missing. Column-level changes cannot be expressed that way,
 * so they follow as explicit `ALTER TABLE` steps — and they run after the
 * schema, which guarantees the table they alter exists.
 */
function migrate(s: InstanceType<typeof BunDatabase>) {
    s.run("PRAGMA journal_mode=WAL");
    s.run("PRAGMA foreign_keys=ON");
    s.run(SCHEMA);

    const hasColumn = (table: string, column: string) =>
        s
            .query<{ name: string }, []>(`PRAGMA table_info(${table})`)
            .all()
            .some((c) => c.name === column);

    if (!hasColumn("users", "is_pending")) {
        s.run(
            "ALTER TABLE users ADD COLUMN is_pending INTEGER NOT NULL DEFAULT 0",
        );
    }
    if (!hasColumn("users", "register_application")) {
        s.run("ALTER TABLE users ADD COLUMN register_application TEXT");
    }
    if (!hasColumn("patches", "version")) {
        s.run(
            "ALTER TABLE patches ADD COLUMN version TEXT NOT NULL DEFAULT ''",
        );
    }
    if (!hasColumn("repositories", "allow_user_labels")) {
        s.run(
            "ALTER TABLE repositories ADD COLUMN allow_user_labels INTEGER NOT NULL DEFAULT 0",
        );
    }
    if (!hasColumn("ci_runs", "repo_run_id")) {
        s.run("ALTER TABLE ci_runs ADD COLUMN repo_run_id INTEGER");
    }

    // Backfill patch versions left empty by the column migration above.
    s.run(
        "UPDATE patches SET version = lower(hex(randomblob(16))) WHERE version = ''",
    );
    // Expired sessions serve no purpose after a restart.
    s.run("DELETE FROM sessions WHERE expires_at < datetime('now')");
}

/**
 * Open the database, creating its directory first — SQLite reports a bare
 * SQLITE_CANTOPEN if the parent directory is missing, which is what a fresh
 * DATA_DIR looks like before db:init has ever run.
 */
function openDb(): InstanceType<typeof BunDatabase> {
    mkdirSync(path.dirname(paths.DB_PATH), { recursive: true });
    const s = new BunDatabase(paths.DB_PATH);
    migrate(s);
    return s;
}

let sqlite = openDb();

export let db = new Kysely<Database>({
    dialect: new BunSqliteDialect({ database: sqlite }),
});

/** Close the current DB and reopen from disk (used by tests after data wipe). */
export function resetDb() {
    try {
        sqlite.close();
    } catch {}
    sqlite = openDb();
    db = new Kysely<Database>({
        dialect: new BunSqliteDialect({ database: sqlite }),
    });
}

export async function getRepo(name: string, isAdmin: boolean) {
    const repo = await db
        .selectFrom("repositories")
        .selectAll()
        .where("name", "=", name)
        .executeTakeFirst();
    if (!repo) return null;
    if (repo.is_private && !isAdmin) return null;
    return repo;
}