Initial commit

1 files changed, 65 insertions, 0 deletions

diff --git a/init_db.py b/init_db.py
new file mode 100644
index 0000000..a401a91
--- /dev/null
+++ b/init_db.py
@@ -0,0 +1,65 @@
+#!/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()