argument.es/db.ts

import { Database } from "bun:sqlite";
import type { RoundState } from "./game.ts";

const dbPath = process.env.DATABASE_PATH ?? "argumentes.sqlite";
export const db = new Database(dbPath, { create: true });

db.exec(`
  CREATE TABLE IF NOT EXISTS rounds (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    num INTEGER,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    data TEXT
  );
`);

export function saveRound(round: RoundState) {
  const insert = db.prepare("INSERT INTO rounds (num, data) VALUES ($num, $data)");
  insert.run({ $num: round.num, $data: JSON.stringify(round) });
}

export function getRounds(page: number = 1, limit: number = 10) {
  const offset = (page - 1) * limit;
  const countQuery = db.query("SELECT COUNT(*) as count FROM rounds").get() as { count: number };
  const rows = db.query("SELECT data FROM rounds ORDER BY num DESC, id DESC LIMIT $limit OFFSET $offset")
    .all({ $limit: limit, $offset: offset }) as { data: string }[];
  return {
    rounds: rows.map(r => JSON.parse(r.data) as RoundState),
    total: countQuery.count,
    page,
    limit,
    totalPages: Math.ceil(countQuery.count / limit)
  };
}

export function getAllRounds() {
  const rows = db.query("SELECT data FROM rounds ORDER BY num ASC, id ASC").all() as { data: string }[];
  return rows.map(r => JSON.parse(r.data) as RoundState);
}

export function clearAllRounds() {
  db.exec("DELETE FROM rounds;");
  db.exec("DELETE FROM sqlite_sequence WHERE name = 'rounds';");
}

// ── Questions (user-submitted) ───────────────────────────────────────────────

db.exec(`
  CREATE TABLE IF NOT EXISTS questions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    text TEXT NOT NULL,
    order_id TEXT NOT NULL UNIQUE,
    status TEXT NOT NULL DEFAULT 'pending',
    username TEXT NOT NULL DEFAULT '',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  );
`);

// Migration: add username column to pre-existing questions tables
try {
  db.exec("ALTER TABLE questions ADD COLUMN username TEXT NOT NULL DEFAULT ''");
} catch {
  // Column already exists — no-op
}

export function createPendingQuestion(text: string, orderId: string, username = ""): number {
  const stmt = db.prepare(
    "INSERT INTO questions (text, order_id, username) VALUES ($text, $orderId, $username)"
  );
  const result = stmt.run({ $text: text, $orderId: orderId, $username: username });
  return result.lastInsertRowid as number;
}

/** Creates a question that is immediately ready (used for credit-based submissions). */
export function createPaidQuestion(text: string, username: string): void {
  const orderId = crypto.randomUUID();
  db.prepare(
    "INSERT INTO questions (text, order_id, username, status) VALUES ($text, $orderId, $username, 'paid')"
  ).run({ $text: text, $orderId: orderId, $username: username });
}

export function markQuestionPaid(orderId: string): boolean {
  const result = db
    .prepare("UPDATE questions SET status = 'paid' WHERE order_id = $orderId AND status = 'pending'")
    .run({ $orderId: orderId });
  return result.changes > 0;
}

export function getNextPendingQuestion(): { id: number; text: string; order_id: string } | null {
  return db
    .query("SELECT id, text, order_id FROM questions WHERE status = 'paid' ORDER BY id ASC LIMIT 1")
    .get() as { id: number; text: string; order_id: string } | null;
}

export function markQuestionUsed(id: number): void {
  db.prepare("UPDATE questions SET status = 'used' WHERE id = $id").run({ $id: id });
}

/** Top 7 players by total votes received on their answers, excluding anonymous. */
export function getPlayerScores(): Record<string, number> {
  const rows = db
    .query(
      "SELECT username, SUM(votes) as score FROM user_answers WHERE username != '' GROUP BY username ORDER BY score DESC LIMIT 7"
    )
    .all() as { username: string; score: number }[];
  return Object.fromEntries(rows.map(r => [r.username, r.score]));
}

/** Persist accumulated vote counts for user answers in a given round. */
export function persistUserAnswerVotes(roundNum: number, votes: Record<string, number>): void {
  const stmt = db.prepare(
    "UPDATE user_answers SET votes = $votes WHERE round_num = $roundNum AND username = $username"
  );
  db.transaction(() => {
    for (const [username, voteCount] of Object.entries(votes)) {
      stmt.run({ $votes: voteCount, $roundNum: roundNum, $username: username });
    }
  })();
}

// ── User answers (submitted during live rounds) ──────────────────────────────

db.exec(`
  CREATE TABLE IF NOT EXISTS user_answers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    round_num INTEGER NOT NULL,
    text TEXT NOT NULL,
    username TEXT NOT NULL,
    token TEXT NOT NULL,
    votes INTEGER NOT NULL DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  );
`);

// Migration: add votes column to pre-existing user_answers tables
try {
  db.exec("ALTER TABLE user_answers ADD COLUMN votes INTEGER NOT NULL DEFAULT 0");
} catch {
  // Column already exists — no-op
}

// ── Credits (answer-count-based access) ──────────────────────────────────────

db.exec(`
  CREATE TABLE IF NOT EXISTS credits (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL,
    token TEXT NOT NULL UNIQUE,
    tier TEXT NOT NULL,
    order_id TEXT NOT NULL UNIQUE,
    status TEXT NOT NULL DEFAULT 'pending',
    expires_at INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  );
`);

// Migrations for question-tracking columns
try {
  db.exec("ALTER TABLE credits ADD COLUMN max_questions INTEGER");
} catch {
  // Column already exists — no-op
}
try {
  db.exec("ALTER TABLE credits ADD COLUMN questions_used INTEGER NOT NULL DEFAULT 0");
} catch {
  // Column already exists — no-op
}

export function createPendingCredit(username: string, orderId: string, tier: string, maxQuestions: number | null): string {
  const token = crypto.randomUUID();
  db.prepare(
    "INSERT INTO credits (username, token, tier, order_id, max_questions) VALUES ($username, $token, $tier, $orderId, $maxQuestions)"
  ).run({ $username: username, $token: token, $tier: tier, $orderId: orderId, $maxQuestions: maxQuestions });
  return token;
}

export function activateCredit(
  orderId: string,
  expiresAt: number,
): { token: string; username: string } | null {
  db.prepare(
    "UPDATE credits SET status = 'active', expires_at = $expiresAt WHERE order_id = $orderId AND status = 'pending'"
  ).run({ $expiresAt: expiresAt, $orderId: orderId });
  return db
    .query("SELECT token, username FROM credits WHERE order_id = $orderId AND status = 'active'")
    .get({ $orderId: orderId }) as { token: string; username: string } | null;
}

export function getCreditByOrder(orderId: string): {
  status: string;
  token: string;
  username: string;
  tier: string;
  expiresAt: number | null;
  maxQuestions: number | null;
  questionsUsed: number;
} | null {
  return db
    .query(
      "SELECT status, token, username, tier, expires_at as expiresAt, max_questions as maxQuestions, questions_used as questionsUsed FROM credits WHERE order_id = $orderId"
    )
    .get({ $orderId: orderId }) as {
    status: string;
    token: string;
    username: string;
    tier: string;
    expiresAt: number | null;
    maxQuestions: number | null;
    questionsUsed: number;
  } | null;
}

/** Insert a user answer directly, bypassing credit checks (admin use). */
export function insertAdminAnswer(roundNum: number, text: string, username: string): void {
  db.prepare(
    "INSERT INTO user_answers (round_num, text, username, token) VALUES ($roundNum, $text, $username, 'admin')"
  ).run({ $roundNum: roundNum, $text: text, $username: username });
}

/**
 * Atomically validates a credit token, records a user answer for the given
 * round, and decrements the answer budget. Returns null if the token is
 * invalid or exhausted.
 */
export function submitUserAnswer(
  token: string,
  roundNum: number,
  text: string,
): { username: string; answersLeft: number } | null {
  const row = db
    .query(
      "SELECT username, max_questions, questions_used FROM credits WHERE token = $token AND status = 'active'"
    )
    .get({ $token: token }) as {
    username: string;
    max_questions: number;
    questions_used: number;
  } | null;
  if (!row) return null;
  if (row.questions_used >= row.max_questions) return null;

  db.transaction(() => {
    db.prepare(
      "INSERT INTO user_answers (round_num, text, username, token) VALUES ($roundNum, $text, $username, $token)"
    ).run({ $roundNum: roundNum, $text: text, $username: row.username, $token: token });
    db.prepare(
      "UPDATE credits SET questions_used = questions_used + 1 WHERE token = $token"
    ).run({ $token: token });
  })();

  return { username: row.username, answersLeft: row.max_questions - row.questions_used - 1 };
}