path: root/app/lib/db.server.ts

import Database from "better-sqlite3";
import path from "path";
import { fileURLToPath } from "url";

const __dirname = path.dirname(fileURLToPath(import.meta.url));
const DB_PATH = process.env.DB_PATH
  ? path.resolve(process.env.DB_PATH)
  : path.join(__dirname, "../../events.db");

let _db: Database.Database | null = null;

/** Directory holding the DB file — also used for other runtime state files. */
export const DATA_DIR = path.dirname(DB_PATH);

/** Normalization used for favorite matching: full/half-width and case insensitive. */
export function normalizeTerm(s: string): string {
  return s.normalize("NFKC").toLowerCase().trim();
}

export function getDb(): Database.Database {
  if (!_db) {
    _db = new Database(DB_PATH);
    _db.pragma("journal_mode = WAL");
    _db.pragma("foreign_keys = ON");
    _db.function("nfkc_lower", { deterministic: true }, (s: unknown) =>
      typeof s === "string" ? normalizeTerm(s) : null
    );
    initSchema(_db);
  }
  return _db;
}

function initSchema(db: Database.Database) {
  db.exec(`
    CREATE TABLE IF NOT EXISTS venues (
      id       TEXT PRIMARY KEY,
      name     TEXT NOT NULL,
      url      TEXT NOT NULL,
      area     TEXT,
      capacity INTEGER
    );

    CREATE TABLE IF NOT EXISTS events (
      id          INTEGER PRIMARY KEY AUTOINCREMENT,
      venue_id    TEXT    NOT NULL REFERENCES venues(id),
      title       TEXT    NOT NULL,
      artist      TEXT,
      date        TEXT    NOT NULL,
      start_time  TEXT,
      open_time   TEXT,
      ticket_url  TEXT,
      price       TEXT,
      image_url   TEXT,
      description TEXT,
      source_url  TEXT,
      fetched_at  TEXT    NOT NULL DEFAULT (datetime('now')),
      UNIQUE(venue_id, title, date)
    );

    CREATE INDEX IF NOT EXISTS idx_events_date     ON events(date);
    CREATE INDEX IF NOT EXISTS idx_events_venue_id ON events(venue_id);

    CREATE TABLE IF NOT EXISTS scrape_logs (
      id           INTEGER PRIMARY KEY AUTOINCREMENT,
      run_id       TEXT    NOT NULL,
      venue_id     TEXT    NOT NULL,
      venue_name   TEXT    NOT NULL,
      status       TEXT    NOT NULL DEFAULT 'running',  -- running | ok | error
      events_saved INTEGER NOT NULL DEFAULT 0,
      error        TEXT,
      started_at   TEXT    NOT NULL DEFAULT (datetime('now')),
      finished_at  TEXT
    );

    CREATE INDEX IF NOT EXISTS idx_scrape_logs_run_id   ON scrape_logs(run_id);
    CREATE INDEX IF NOT EXISTS idx_scrape_logs_venue_id ON scrape_logs(venue_id);

    -- Single-user auth: any registered passkey logs in as "the" owner
    CREATE TABLE IF NOT EXISTS passkeys (
      id           TEXT PRIMARY KEY,           -- credential ID (base64url)
      public_key   BLOB    NOT NULL,
      counter      INTEGER NOT NULL DEFAULT 0,
      transports   TEXT,                       -- JSON array
      label        TEXT,
      created_at   TEXT    NOT NULL DEFAULT (datetime('now')),
      last_used_at TEXT
    );

    CREATE TABLE IF NOT EXISTS sessions (
      id         TEXT PRIMARY KEY,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      expires_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS auth_challenges (
      id         TEXT PRIMARY KEY,
      challenge  TEXT NOT NULL,
      expires_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS favorites (
      id         INTEGER PRIMARY KEY AUTOINCREMENT,
      term       TEXT NOT NULL,
      term_norm  TEXT NOT NULL UNIQUE,
      created_at TEXT NOT NULL DEFAULT (datetime('now'))
    );
  `);

  // Migration: add capacity column to existing venues tables
  try {
    db.exec("ALTER TABLE venues ADD COLUMN capacity INTEGER");
  } catch {
    // Column already exists — ignore
  }
}

export interface Venue {
  id: string;
  name: string;
  url: string;
  area: string | null;
  capacity: number | null;
  event_count?: number;
}

export interface Event {
  id: number;
  venue_id: string;
  venue_name: string;
  venue_area: string | null;
  venue_url?: string;
  title: string;
  artist: string | null;
  date: string;
  start_time: string | null;
  open_time: string | null;
  ticket_url: string | null;
  price: string | null;
  image_url: string | null;
  description: string | null;
  source_url: string | null;
  fetched_at: string;
  /** 1 if title/artist matches a favorite term. Only computed when with_favorites is set. */
  is_favorite?: number;
}

export interface EventInput {
  venue_id: string;
  title: string;
  artist?: string | null;
  date: string;
  start_time?: string | null;
  open_time?: string | null;
  ticket_url?: string | null;
  price?: string | null;
  image_url?: string | null;
  description?: string | null;
  source_url?: string | null;
}

export function upsertVenue(
  id: string,
  name: string,
  url: string,
  area?: string,
  capacity?: number
) {
  getDb()
    .prepare(
      "INSERT OR REPLACE INTO venues (id, name, url, area, capacity) VALUES (?, ?, ?, ?, ?)"
    )
    .run(id, name, url, area ?? null, capacity ?? null);
}

export function upsertEvent(raw: EventInput) {
  // Ensure all named parameters exist (better-sqlite3 requires them all)
  const event = {
    artist: null,
    start_time: null,
    open_time: null,
    ticket_url: null,
    price: null,
    image_url: null,
    description: null,
    source_url: null,
    ...raw,
  };
  getDb()
    .prepare(
      `INSERT INTO events
        (venue_id, title, artist, date, start_time, open_time,
         ticket_url, price, image_url, description, source_url, fetched_at)
       VALUES
        (@venue_id, @title, @artist, @date, @start_time, @open_time,
         @ticket_url, @price, @image_url, @description, @source_url, datetime('now'))
       ON CONFLICT(venue_id, title, date) DO UPDATE SET
         artist      = excluded.artist,
         start_time  = excluded.start_time,
         open_time   = excluded.open_time,
         ticket_url  = excluded.ticket_url,
         price       = excluded.price,
         image_url   = excluded.image_url,
         description = excluded.description,
         source_url  = excluded.source_url,
         fetched_at  = excluded.fetched_at`
    )
    .run(event);
}

export type CapacityRange = "small" | "medium" | "large";

export interface QueryEventsParams {
  date_from?: string;
  date_to?: string;
  venue_id?: string;
  keyword?: string;
  capacity_range?: CapacityRange;
  area?: string;
  /** Compute is_favorite for each row */
  with_favorites?: boolean;
  /** Only events matching a favorite term */
  favorites_only?: boolean;
  limit?: number;
  offset?: number;
}

const FAVORITE_MATCH_SQL = `EXISTS (
  SELECT 1 FROM favorites f
  WHERE instr(nfkc_lower(e.title || ' ' || COALESCE(e.artist, '')), f.term_norm) > 0
)`;

export function queryEvents(params: QueryEventsParams = {}): Event[] {
  const {
    date_from, date_to, venue_id, keyword, capacity_range, area,
    with_favorites, favorites_only, limit = 60, offset = 0,
  } = params;

  const clauses: string[] = [];
  const args: unknown[] = [];

  if (date_from) {
    clauses.push("e.date >= ?");
    args.push(date_from);
  }
  if (date_to) {
    clauses.push("e.date <= ?");
    args.push(date_to);
  }
  if (venue_id) {
    clauses.push("e.venue_id = ?");
    args.push(venue_id);
  }
  if (keyword) {
    clauses.push("(e.title LIKE ? OR e.artist LIKE ?)");
    args.push(`%${keyword}%`, `%${keyword}%`);
  }
  if (capacity_range === "small") {
    clauses.push("v.capacity <= 100");
  } else if (capacity_range === "medium") {
    clauses.push("v.capacity > 100 AND v.capacity < 300");
  } else if (capacity_range === "large") {
    clauses.push("v.capacity >= 300");
  }
  if (area) {
    clauses.push("v.area = ?");
    args.push(area);
  }
  if (favorites_only) {
    clauses.push(FAVORITE_MATCH_SQL);
  }

  const where = clauses.length ? `WHERE ${clauses.join(" AND ")}` : "";

  return getDb()
    .prepare(
      `SELECT e.*, v.name AS venue_name, v.area AS venue_area
              ${with_favorites ? `, ${FAVORITE_MATCH_SQL} AS is_favorite` : ""}
       FROM events e JOIN venues v ON e.venue_id = v.id
       ${where}
       ORDER BY e.date ASC, e.start_time ASC
       LIMIT ? OFFSET ?`
    )
    .all(...args, limit, offset) as Event[];
}

export function getEvent(id: number): Event | undefined {
  return getDb()
    .prepare(
      `SELECT e.*, v.name AS venue_name, v.url AS venue_url, v.area AS venue_area
       FROM events e JOIN venues v ON e.venue_id = v.id
       WHERE e.id = ?`
    )
    .get(id) as Event | undefined;
}

export function getVenues(): Venue[] {
  return getDb()
    .prepare(
      `SELECT v.*, COUNT(e.id) AS event_count
       FROM venues v LEFT JOIN events e ON v.id = e.venue_id
       GROUP BY v.id ORDER BY v.name`
    )
    .all() as Venue[];
}

// ---------- Scrape logs ----------

export interface ScrapeLog {
  id: number;
  run_id: string;
  venue_id: string;
  venue_name: string;
  status: "running" | "ok" | "error";
  events_saved: number;
  error: string | null;
  started_at: string;
  finished_at: string | null;
}

export function insertScrapeLog(
  run_id: string,
  venue_id: string,
  venue_name: string
): number {
  const result = getDb()
    .prepare(
      `INSERT INTO scrape_logs (run_id, venue_id, venue_name, status)
       VALUES (?, ?, ?, 'running')`
    )
    .run(run_id, venue_id, venue_name);
  return result.lastInsertRowid as number;
}

export function updateScrapeLog(
  id: number,
  status: "ok" | "error",
  events_saved: number,
  error?: string
) {
  getDb()
    .prepare(
      `UPDATE scrape_logs
       SET status = ?, events_saved = ?, error = ?, finished_at = datetime('now')
       WHERE id = ?`
    )
    .run(status, events_saved, error ?? null, id);
}

export function getLatestScrapeRun(): ScrapeLog[] {
  return getDb()
    .prepare(
      `SELECT * FROM scrape_logs
       WHERE run_id = (SELECT run_id FROM scrape_logs ORDER BY started_at DESC LIMIT 1)
       ORDER BY id ASC`
    )
    .all() as ScrapeLog[];
}

export function getScrapeRunById(run_id: string): ScrapeLog[] {
  return getDb()
    .prepare(
      "SELECT * FROM scrape_logs WHERE run_id = ? ORDER BY id ASC"
    )
    .all(run_id) as ScrapeLog[];
}

export function getLastScrapePerVenue(): ScrapeLog[] {
  return getDb()
    .prepare(
      `SELECT s.* FROM scrape_logs s
       INNER JOIN (
         SELECT venue_id, MAX(started_at) AS latest FROM scrape_logs GROUP BY venue_id
       ) t ON s.venue_id = t.venue_id AND s.started_at = t.latest
       ORDER BY s.venue_name ASC`
    )
    .all() as ScrapeLog[];
}