import "server-only";
import { mkdirSync } from "node:fs";
import path from "node:path";
import { DatabaseSync } from "node:sqlite";
import { DATA_ROOT } from "./paths";

const SCHEMA = `
CREATE TABLE IF NOT EXISTS users (
  id            INTEGER PRIMARY KEY,
  username      TEXT NOT NULL UNIQUE COLLATE NOCASE,
  name          TEXT NOT NULL DEFAULT '',
  email         TEXT NOT NULL UNIQUE COLLATE NOCASE,
  password_hash TEXT NOT NULL,
  bio           TEXT NOT NULL DEFAULT '',
  created_at    INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS sessions (
  id         TEXT PRIMARY KEY,           -- sha256 of the cookie token
  user_id    INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  created_at INTEGER NOT NULL,
  expires_at INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS ssh_keys (
  id           INTEGER PRIMARY KEY,
  user_id      INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title        TEXT NOT NULL,
  key_type     TEXT NOT NULL,
  public_key   TEXT NOT NULL,            -- "ssh-ed25519 AAAA..." without comment
  fingerprint  TEXT NOT NULL UNIQUE,     -- SHA256:...
  created_at   INTEGER NOT NULL,
  last_used_at INTEGER
);

CREATE TABLE IF NOT EXISTS api_tokens (
  id           INTEGER PRIMARY KEY,
  user_id      INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  name         TEXT NOT NULL,
  token_hash   TEXT NOT NULL UNIQUE,     -- sha256 of the token
  hint         TEXT NOT NULL,            -- last 4 chars, shown in the UI
  permission   TEXT NOT NULL CHECK (permission IN ('read', 'write')),
  all_repos    INTEGER NOT NULL DEFAULT 1,
  expires_at   INTEGER,
  created_at   INTEGER NOT NULL,
  last_used_at INTEGER
);

CREATE TABLE IF NOT EXISTS repos (
  id          INTEGER PRIMARY KEY,
  owner_id    INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  name        TEXT NOT NULL COLLATE NOCASE,
  description TEXT NOT NULL DEFAULT '',
  visibility  TEXT NOT NULL DEFAULT 'public' CHECK (visibility IN ('public', 'private')),
  created_at  INTEGER NOT NULL,
  pushed_at   INTEGER,
  UNIQUE (owner_id, name)
);

-- An uploaded repository image. Without a row, the owner's avatar is shown.
CREATE TABLE IF NOT EXISTS repo_images (
  repo_id      INTEGER PRIMARY KEY REFERENCES repos(id) ON DELETE CASCADE,
  content_type TEXT NOT NULL,
  data         BLOB NOT NULL,
  updated_at   INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS api_token_repos (
  token_id INTEGER NOT NULL REFERENCES api_tokens(id) ON DELETE CASCADE,
  repo_id  INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  PRIMARY KEY (token_id, repo_id)
);

CREATE TABLE IF NOT EXISTS collaborators (
  repo_id    INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  user_id    INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  created_at INTEGER NOT NULL,
  PRIMARY KEY (repo_id, user_id)
);

CREATE TABLE IF NOT EXISTS issues (
  id         INTEGER PRIMARY KEY,
  repo_id    INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  number     INTEGER NOT NULL,
  title      TEXT NOT NULL,
  body       TEXT NOT NULL DEFAULT '',
  author_id  INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  state      TEXT NOT NULL DEFAULT 'open' CHECK (state IN ('open', 'closed')),
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  UNIQUE (repo_id, number)
);

CREATE TABLE IF NOT EXISTS labels (
  id      INTEGER PRIMARY KEY,
  repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  name    TEXT NOT NULL COLLATE NOCASE,
  color   TEXT NOT NULL,             -- #rrggbb
  UNIQUE (repo_id, name)
);

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

-- Property changes, shown in an issue's activity feed. Values are stored as
-- they read at the time (status ids, priority numbers, usernames, label names).
CREATE TABLE IF NOT EXISTS issue_events (
  id         INTEGER PRIMARY KEY,
  issue_id   INTEGER NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
  actor_id   INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  field      TEXT NOT NULL,
  old_value  TEXT,
  new_value  TEXT,
  created_at INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS issue_comments (
  id         INTEGER PRIMARY KEY,
  issue_id   INTEGER NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
  author_id  INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  body       TEXT NOT NULL,
  created_at INTEGER NOT NULL
);
`;

function open() {
  mkdirSync(DATA_ROOT, { recursive: true });
  const db = new DatabaseSync(path.join(DATA_ROOT, "app.db"));
  db.exec("PRAGMA journal_mode = WAL; PRAGMA foreign_keys = ON; PRAGMA busy_timeout = 5000;");
  return db;
}

// Reuse one connection across hot reloads in development. The schema is
// re-applied on every load (it's idempotent) so new tables reach a reused connection.
const g = globalThis as unknown as { __db?: DatabaseSync };
export const db = (g.__db ??= open());
db.exec(SCHEMA);

// Columns added to existing tables after their first release. CREATE TABLE IF NOT
// EXISTS leaves old tables alone, so missing columns are added here.
const ADDED_COLUMNS: { table: string; column: string; definition: string; backfill?: string }[] = [
  {
    table: "issues",
    column: "status",
    definition: `TEXT NOT NULL DEFAULT 'todo'
      CHECK (status IN ('backlog', 'todo', 'in_progress', 'in_review', 'done', 'canceled'))`,
    // `state` stays in sync with `status`: done and canceled issues are closed.
    backfill: "UPDATE issues SET status = CASE state WHEN 'closed' THEN 'done' ELSE 'todo' END",
  },
  // 0 = no priority, 1 = urgent … 4 = low
  { table: "issues", column: "priority", definition: "INTEGER NOT NULL DEFAULT 0 CHECK (priority BETWEEN 0 AND 4)" },
  { table: "issues", column: "assignee_id", definition: "INTEGER REFERENCES users(id) ON DELETE SET NULL" },
  { table: "issues", column: "estimate", definition: "INTEGER" },
  { table: "issues", column: "due_date", definition: "TEXT" }, // YYYY-MM-DD
  // Manual order on the board and in lists; new issues go to the top.
  { table: "issues", column: "sort_order", definition: "REAL NOT NULL DEFAULT 0", backfill: "UPDATE issues SET sort_order = -created_at" },
];

for (const { table, column, definition, backfill } of ADDED_COLUMNS) {
  const columns = db.prepare(`PRAGMA table_info(${table})`).all() as { name: string }[];
  if (columns.some((c) => c.name === column)) continue;
  db.exec(`ALTER TABLE ${table} ADD COLUMN ${column} ${definition}`);
  if (backfill) db.exec(backfill);
}

export function transaction<T>(fn: () => T): T {
  db.exec("BEGIN IMMEDIATE");
  try {
    const result = fn();
    db.exec("COMMIT");
    return result;
  } catch (e) {
    db.exec("ROLLBACK");
    throw e;
  }
}