#!/usr/bin/env python3
from __future__ import annotations

import sqlite3
from pathlib import Path

DB_PATH = Path("cheat.db")


def main() -> None:
    DB_PATH.parent.mkdir(parents=True, exist_ok=True)

    conn = sqlite3.connect(DB_PATH)
    try:
        conn.execute("PRAGMA journal_mode=WAL;")
        conn.execute("PRAGMA foreign_keys=ON;")

        conn.execute("""
        CREATE TABLE IF NOT EXISTS cards (
            id TEXT PRIMARY KEY,
            command TEXT NOT NULL,
            explanation TEXT NOT NULL,
            intent_json TEXT NOT NULL,
            alternatives_json TEXT NOT NULL,
            requires_json TEXT NOT NULL,
            packages_json TEXT NOT NULL,
            tags_json TEXT NOT NULL,
            platform_json TEXT NOT NULL,
            shell_json TEXT NOT NULL,
            safety TEXT NOT NULL,
            search_text TEXT NOT NULL,
            created_at TEXT DEFAULT CURRENT_TIMESTAMP,
            updated_at TEXT DEFAULT CURRENT_TIMESTAMP
        );
        """)

        conn.execute("""
        CREATE TABLE IF NOT EXISTS card_embeddings (
            card_id TEXT PRIMARY KEY,
            model_name TEXT NOT NULL,
            embedding_blob BLOB NOT NULL,
            embedding_dim INTEGER NOT NULL,
            created_at TEXT DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY(card_id) REFERENCES cards(id) ON DELETE CASCADE
        );
        """)

        conn.execute("""
        CREATE INDEX IF NOT EXISTS idx_cards_command
        ON cards(command);
        """)

        conn.execute("""
        CREATE INDEX IF NOT EXISTS idx_cards_safety
        ON cards(safety);
        """)

        conn.commit()
        print(f"Initialized database at {DB_PATH}")
    finally:
        conn.close()


if __name__ == "__main__":
    main()