import Database from "better-sqlite3";
import path from "path";

let db: Database.Database | null = null;

export function getDb(): Database.Database {
  if (!db) {
    const dbPath = process.env.DB_PATH ?? path.resolve("microblog.db");
    db = new Database(dbPath);
    db.pragma("journal_mode = WAL");
    db.pragma("foreign_keys = ON");
    initSchema(db);
  }
  return db;
}

function initSchema(db: Database.Database) {
  db.exec(`
    CREATE TABLE IF NOT EXISTS posts (
      id         TEXT PRIMARY KEY,
      content    TEXT NOT NULL,
      created_at TEXT NOT NULL DEFAULT (datetime('now'))
    );
    CREATE INDEX IF NOT EXISTS idx_posts_created_at ON posts(created_at DESC);
  `);
}

export interface Post {
  id: string;
  content: string;
  created_at: string;
}

export interface CreatePostInput {
  id: string;
  content: string;
}

export function listPosts(): Post[] {
  return getDb().prepare("SELECT * FROM posts ORDER BY created_at DESC").all() as Post[];
}

export function getPostById(id: string): Post | null {
  return getDb().prepare("SELECT * FROM posts WHERE id = ?").get(id) as Post | null;
}

export function createPost(input: CreatePostInput): Post {
  getDb().prepare("INSERT INTO posts (id, content) VALUES (?, ?)").run(input.id, input.content);
  return getPostById(input.id)!;
}