import Database from 'better-sqlite3';
import { mkdirSync } from 'node:fs';
import { dirname } from 'node:path';

export type Db = Database.Database;

const CREATE_TABLE = `
  CREATE TABLE IF NOT EXISTS messages (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    message_id  INTEGER NOT NULL,
    chat_id     INTEGER NOT NULL,
    user_id     INTEGER,
    username    TEXT,
    first_name  TEXT,
    text        TEXT NOT NULL,
    date        INTEGER NOT NULL
  )
`;

const CREATE_DATE_INDEX = `
  CREATE INDEX IF NOT EXISTS idx_messages_date ON messages(date)
`;

const CREATE_USER_INDEX = `
  CREATE INDEX IF NOT EXISTS idx_messages_user ON messages(username, first_name)
`;

export function openDb(dbPath: string): Db {
  mkdirSync(dirname(dbPath), { recursive: true });
  const db = new Database(dbPath);
  db.pragma('journal_mode = WAL');
  db.prepare(CREATE_TABLE).run();
  db.prepare(CREATE_DATE_INDEX).run();
  db.prepare(CREATE_USER_INDEX).run();
  return db;
}