import { Database as BunDatabase } from "bun:sqlite";
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 UserRow = Selectable<UserTable>;
export type PasskeyRow = Selectable<PasskeyTable>;
export type SessionRow = Selectable<SessionTable>;
export type RepositoryRow = Selectable<RepositoryTable>;
export type IssueRow = Selectable<IssueTable>;
export type IssueCommentRow = Selectable<IssueCommentTable>;
export type IssueReactionRow = Selectable<IssueReactionTable>;
export type PatchRow = Selectable<PatchTable>;
export type PatchCommentRow = Selectable<PatchCommentTable>;
export type PatchReactionRow = Selectable<PatchReactionTable>;
export type SshKeyRow = Selectable<SshKeyTable>;
export type ReleaseRow = Selectable<ReleaseTable>;
export type ReleaseAssetRow = Selectable<ReleaseAssetTable>;
export type LabelRow = Selectable<LabelTable>;
export type CiRunRow = Selectable<CiRunTable>;
export type CiStepRow = Selectable<CiStepTable>;
export type CiArtifactRow = Selectable<CiArtifactTable>;
export type CiSecretRow = Selectable<CiSecretTable>;

let sqlite = new BunDatabase(paths.DB_PATH);
sqlite.run("PRAGMA journal_mode=WAL");
sqlite.run("PRAGMA foreign_keys=ON");

// Migration: add is_pending and register_application columns to users if missing
const userCols = sqlite
    .query<{ name: string }, []>("PRAGMA table_info(users)")
    .all();
if (!userCols.some((c) => c.name === "is_pending")) {
    sqlite.run(
        "ALTER TABLE users ADD COLUMN is_pending INTEGER NOT NULL DEFAULT 0",
    );
}
if (!userCols.some((c) => c.name === "register_application")) {
    sqlite.run("ALTER TABLE users ADD COLUMN register_application TEXT");
}

// Migration: add version column if missing, then populate any empty values
const patchCols = sqlite
    .query<{ name: string }, []>("PRAGMA table_info(patches)")
    .all();
if (!patchCols.some((c) => c.name === "version")) {
    sqlite.run(
        "ALTER TABLE patches ADD COLUMN version TEXT NOT NULL DEFAULT ''",
    );
}
sqlite.run(
    "UPDATE patches SET version = lower(hex(randomblob(16))) WHERE version = ''",
);

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

function runMigrations(s: InstanceType<typeof BunDatabase>) {
    const ciRunCols = s
        .query<{ name: string }, []>("PRAGMA table_info(ci_runs)")
        .all();
    if (!ciRunCols.some((c) => c.name === "repo_run_id")) {
        s.run("ALTER TABLE ci_runs ADD COLUMN repo_run_id INTEGER");
    }
}

// NOTE: runMigrations() alters ci_runs, so it must run *after* the
// `CREATE TABLE IF NOT EXISTS ci_runs` block below — otherwise upgrading a
// database created before the CI tables existed would ALTER a missing table
// and throw at import. The call is intentionally placed at the end of the
// migration section, not here.

/** Close the current DB and reopen from disk (used by tests after data wipe). */
export function resetDb() {
    try {
        sqlite.close();
    } catch {}
    sqlite = new BunDatabase(paths.DB_PATH);
    sqlite.run("PRAGMA journal_mode=WAL");
    sqlite.run("PRAGMA foreign_keys=ON");
    runMigrations(sqlite);
    db = new Kysely<Database>({
        dialect: new BunSqliteDialect({ database: sqlite }),
    });
}

// Migration: create CI tables if missing
sqlite.run(`CREATE TABLE IF NOT EXISTS ci_runs (
  id               INTEGER PRIMARY KEY AUTOINCREMENT,
  repo_id          INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
  triggered_by     INTEGER REFERENCES users(id) ON DELETE SET NULL,
  trigger_source   TEXT NOT NULL,
  commit_sha       TEXT,
  commit_branch    TEXT,
  commit_tag       TEXT,
  status           TEXT NOT NULL DEFAULT 'pending',
  variable_overrides TEXT,
  started_at       TEXT,
  finished_at      TEXT,
  created_at       TEXT NOT NULL DEFAULT (datetime('now'))
)`);
sqlite.run(`CREATE TABLE IF NOT EXISTS ci_steps (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  run_id      INTEGER NOT NULL REFERENCES ci_runs(id) ON DELETE CASCADE,
  name        TEXT NOT NULL,
  status      TEXT NOT NULL DEFAULT 'pending',
  exit_code   INTEGER,
  started_at  TEXT,
  finished_at TEXT,
  log         TEXT NOT NULL DEFAULT ''
)`);
sqlite.run(`CREATE TABLE IF NOT EXISTS ci_artifacts (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  run_id     INTEGER NOT NULL REFERENCES ci_runs(id) ON DELETE CASCADE,
  filename   TEXT NOT NULL,
  size       INTEGER NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
)`);
sqlite.run(`CREATE TABLE IF NOT EXISTS ci_secrets (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  repo_id     INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
  name        TEXT NOT NULL,
  value       TEXT NOT NULL,
  description TEXT,
  created_at  TEXT NOT NULL DEFAULT (datetime('now')),
  UNIQUE(repo_id, name)
)`);
sqlite.run(`CREATE TABLE IF NOT EXISTS ci_run_counters (
  repo_id     INTEGER PRIMARY KEY REFERENCES repositories(id) ON DELETE CASCADE,
  last_run_id INTEGER NOT NULL DEFAULT 0
)`);

// Run column-level migrations now that the CI tables are guaranteed to exist
// (see the note above runMigrations).
runMigrations(sqlite);

// Migration: add allow_user_labels column to repositories if missing
const repoCols = sqlite
    .query<{ name: string }, []>("PRAGMA table_info(repositories)")
    .all();
if (!repoCols.some((c) => c.name === "allow_user_labels")) {
    sqlite.run(
        "ALTER TABLE repositories ADD COLUMN allow_user_labels INTEGER NOT NULL DEFAULT 0",
    );
}

// Migration: create labels tables if missing
sqlite.run(`CREATE TABLE IF NOT EXISTS labels (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  repo_id    INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
  name       TEXT NOT NULL,
  color      TEXT NOT NULL DEFAULT '#808080',
  created_at TEXT NOT NULL,
  UNIQUE(repo_id, name)
)`);
sqlite.run(`CREATE TABLE IF NOT EXISTS issue_labels (
  issue_id INTEGER NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
  label_id INTEGER NOT NULL REFERENCES labels(id) ON DELETE CASCADE,
  PRIMARY KEY (issue_id, label_id)
)`);
sqlite.run(`CREATE TABLE IF NOT EXISTS patch_labels (
  patch_id INTEGER NOT NULL REFERENCES patches(id) ON DELETE CASCADE,
  label_id INTEGER NOT NULL REFERENCES labels(id) ON DELETE CASCADE,
  PRIMARY KEY (patch_id, label_id)
)`);

// Indexes for common query patterns (safe to run repeatedly)
sqlite.run("CREATE INDEX IF NOT EXISTS idx_issues_repo_id ON issues(repo_id)");
sqlite.run(
    "CREATE INDEX IF NOT EXISTS idx_issues_author_id ON issues(author_id)",
);
sqlite.run(
    "CREATE INDEX IF NOT EXISTS idx_patches_repo_id ON patches(repo_id)",
);
sqlite.run(
    "CREATE INDEX IF NOT EXISTS idx_patches_author_id ON patches(author_id)",
);
sqlite.run(
    "CREATE INDEX IF NOT EXISTS idx_ci_runs_repo_id ON ci_runs(repo_id)",
);
sqlite.run(
    "CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON sessions(user_id)",
);
sqlite.run(
    "CREATE INDEX IF NOT EXISTS idx_ssh_keys_user_id ON ssh_keys(user_id)",
);
sqlite.run(
    "CREATE INDEX IF NOT EXISTS idx_issue_labels_label_id ON issue_labels(label_id)",
);
sqlite.run(
    "CREATE INDEX IF NOT EXISTS idx_patch_labels_label_id ON patch_labels(label_id)",
);
sqlite.run("CREATE INDEX IF NOT EXISTS idx_labels_repo_id ON labels(repo_id)");
sqlite.run(
    "CREATE INDEX IF NOT EXISTS idx_sessions_expires_at ON sessions(expires_at)",
);
sqlite.run(
    "CREATE INDEX IF NOT EXISTS idx_issues_repo_status ON issues(repo_id, status)",
);
sqlite.run(
    "CREATE INDEX IF NOT EXISTS idx_patches_repo_status ON patches(repo_id, status)",
);

// Clean up expired sessions on startup
sqlite.run("DELETE FROM sessions WHERE expires_at < datetime('now')");

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;
}