import { Database } from "bun:sqlite";

export const db = new Database("links.sqlite", { create: true });

db.run("PRAGMA journal_mode = WAL;");

db.run(`
  CREATE TABLE IF NOT EXISTS links (
    id TEXT PRIMARY KEY,
    target TEXT NOT NULL,
    short TEXT NOT NULL UNIQUE,
    ogTitle TEXT,
    ogDesc TEXT,
    ogImage TEXT
  );
`);

db.run(`
  CREATE TABLE IF NOT EXISTS stats (
    linkId TEXT PRIMARY KEY,
    clicks INTEGER NOT NULL DEFAULT 0
  );
`);

// Use positional placeholders ?
export const insertLinkStmt = db.query(`
  INSERT INTO links (id, target, short, ogTitle, ogDesc, ogImage)
  VALUES (?, ?, ?, ?, ?, ?)
`);

export const insertStatsStmt = db.query(`
  INSERT INTO stats (linkId, clicks) VALUES (?, 0)
`);

export const getAllLinksStmt = db.query(`
  SELECT l.*, s.clicks
  FROM links l
  LEFT JOIN stats s ON l.id = s.linkId
`);

export const getLinkByIdStmt = db.query(`
  SELECT l.*, s.clicks
  FROM links l
  LEFT JOIN stats s ON l.id = s.linkId
  WHERE l.id = ?
`);

export const getLinkByShortStmt = db.query(`
  SELECT * FROM links WHERE short = ?
`);

export const updateLinkStmt = db.query(`
  UPDATE links SET
    target = COALESCE(?, target),
    short = COALESCE(?, short),
    ogTitle = COALESCE(?, ogTitle),
    ogDesc = COALESCE(?, ogDesc),
    ogImage = COALESCE(?, ogImage)
  WHERE id = ?
`);

export const deleteLinkStmt = db.query(`DELETE FROM links WHERE id = ?`);
export const deleteStatsStmt = db.query(`DELETE FROM stats WHERE linkId = ?`);

export const incClickStmt = db.query(`
  UPDATE stats SET clicks = clicks + 1 WHERE linkId = ?
`);

export const getStatsByIdStmt = db.query(`
  SELECT clicks FROM stats WHERE linkId = ?
`);