1 files changed, 172 insertions, 0 deletions
diff --git a/src/lib/db/schema.sql b/src/lib/db/schema.sql
new file mode 100644
index 0000000..1b678c5
--- /dev/null
+++ b/src/lib/db/schema.sql
@@ -0,0 +1,172 @@
+-- Enable foreign key support
+PRAGMA foreign_keys = ON;
+PRAGMA journal_mode = WAL;
+PRAGMA cache_size = -2000;
+PRAGMA mmap_size = 30000000000;
+
+
+CREATE TABLE IF NOT EXISTS cookies(
+    user INTEGER NOT NULL,
+    cookie TEXT NOT NULL,
+    expiry INTEGER NOT NULL,
+    PRIMARY KEY(user, cookie),
+    FOREIGN KEY (user) REFERENCES users(id) ON DELETE CASCADE
+);
+CREATE TABLE IF NOT EXISTS languages (
+    code TEXT PRIMARY KEY,
+    name TEXT NOT NULL,
+    native_name TEXT
+);
+-- type is "word" or other
+--
+
+-- an orthographic (and likely phonetic too entity)
+-- lang is 2char code ISO 6393-1 I gues
+CREATE TABLE IF NOT EXISTS expressions(
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    spelling TEXT NOT NULL,
+    lang TEXT NOT NULL,
+    frequency INTEGER,
+    type TEXT NOT NULL,
+    syllables INTEGER,
+    ipa    JSONB,
+    prosody JSONB,
+    confidence INTEGER NOT NULL DEFAULT 0,
+    FOREIGN KEY (lang) REFERENCES languages(code),
+    CONSTRAINT spell_unique UNIQUE (spelling, lang)
+);
+CREATE INDEX IF NOT EXISTS idx_words_spelling ON expressions(spelling);
+CREATE INDEX IF NOT EXISTS idx_words_type ON expressions(type);
+CREATE INDEX IF NOT EXISTS idx_words_lang ON expressions(lang);
+-- a semantic entity
+CREATE TABLE IF NOT EXISTS senses(
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    parent_id INTEGER NOT NULL,
+    spelling TEXT NOT NULL,
+    pos TEXT,
+    etymology TEXT,
+    ipa    JSONB,
+    prosody JSONB,
+    senses JSONB,
+    forms JSONB,
+    related JSONB,
+    confidence INTEGER NOT NULL DEFAULT 0,
+    FOREIGN KEY (parent_id) REFERENCES expressions(id)
+);
+CREATE INDEX IF NOT EXISTS idx_words_pos ON senses(pos);
+CREATE INDEX IF NOT EXISTS idx_senses_parent ON senses(parent_id);
+
+
+
+-- Categories table (for noun and verb categories)
+CREATE TABLE IF NOT EXISTS categories (
+    name TEXT PRIMARY KEY
+);
+
+-- Word Categories junction table
+CREATE TABLE IF NOT EXISTS word_categories (
+    word_id INTEGER NOT NULL,
+    category INTEGER NOT NULL,
+    PRIMARY KEY (word_id, category),
+    FOREIGN KEY (word_id) REFERENCES expressions(id),
+    FOREIGN KEY (category) REFERENCES categories(name)
+);
+CREATE INDEX IF NOT EXISTS idx_word_categories_category_id ON word_categories(category);
+
+
+-- Progress
+CREATE TABLE IF NOT EXISTS users(
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    name TEXT NOT NULL,
+    creds TEXT NOT NULL,
+    CONSTRAINT name_unique UNIQUE (name)
+);
+CREATE TABLE IF NOT EXISTS user_progress (
+      id INTEGER PRIMARY KEY AUTOINCREMENT,
+      user_id INTEGER NOT NULL,
+      card_id INTEGER NOT NULL,
+      repetition_count INTEGER DEFAULT 0,
+      ease_factor REAL DEFAULT 2.5,
+      interval INTEGER DEFAULT 1,
+      next_review_date INTEGER,
+      last_reviewed INTEGER,
+      is_mastered BOOLEAN DEFAULT FALSE,
+      FOREIGN KEY (user_id) REFERENCES users(id),
+      FOREIGN KEY (card_id) REFERENCES cards(id),
+      CONSTRAINT progress_unique UNIQUE (user_id, card_id)
+);
+-- CREATE TABLE IF NOT EXISTS user_progress (
+--       id INTEGER PRIMARY KEY AUTOINCREMENT,
+--       user_id INTEGER NOT NULL,
+--       card_id INTEGER NOT NULL,
+--       repetition_count INTEGER DEFAULT 0,
+--       ease_factor REAL DEFAULT 2.5,
+--       interval INTEGER DEFAULT 1,
+--       next_review_date DATETIME,
+--       last_reviewed DATETIME,
+--       is_mastered BOOLEAN DEFAULT FALSE,
+--       CONSTRAINT progress_unique UNIQUE (user_id, card_id)
+--       FOREIGN KEY (user_id) REFERENCES users(id),
+--       FOREIGN KEY (card_id) REFERENCES cards(id)
+-- );
+-- Lessons
+CREATE TABLE IF NOT EXISTS lessons(
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    name TEXT NOT NULL,
+    position INTEGER NOT NULL DEFAULT 0,
+    description TEXT,
+    lang TEXT,
+    FOREIGN KEY (lang) REFERENCES languages(code)
+);
+CREATE TABLE IF NOT EXISTS cards(
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    text TEXT NOT NULL,
+    note TEXT
+);
+CREATE TABLE IF NOT EXISTS cards_expressions(
+  expression_id INTEGER NOT NULL,
+  card_id INTEGER NOT NULL,
+  PRIMARY KEY (card_id, expression_id),
+  FOREIGN KEY (card_id) REFERENCES cards(id),
+  FOREIGN KEY (expression_id) REFERENCES expressions(id)
+);
+CREATE TABLE IF NOT EXISTS cards_lessons(
+  lesson_id INTEGER,
+  card_id INTEGER NOT NULL,
+  PRIMARY KEY (card_id, lesson_id),
+  FOREIGN KEY (card_id) REFERENCES cards(id),
+  FOREIGN KEY (lesson_id) REFERENCES lessons(id)
+);
+
+CREATE TABLE IF NOT EXISTS attempts(
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    user_id INTEGER NOT NULL,
+    timestamp INTEGER NOT NULL,
+    card_id INTEGER NOT NULL,
+    good INTEGER NOT NULL, -- 0 or 1
+    FOREIGN KEY (user_id) REFERENCES users(id)
+    FOREIGN KEY (card_id) REFERENCES cards(id)
+);
+
+-- Index to query attempts on a specific card
+CREATE INDEX IF NOT EXISTS idx_attempts_card ON attempts(card_id);
+
+-- Index to query attempts for a specific user
+CREATE INDEX IF NOT EXISTS idx_attempts_user ON attempts(user_id);
+
+-- (Optional) Index to query attempts by user and resource (useful if you often query by both)
+CREATE INDEX IF NOT EXISTS idx_attempts_user_resource ON attempts(user_id, card_id);
+CREATE INDEX IF NOT EXISTS idx_cards_resources
+ON cards_expressions(expression_id, card_id);
+
+-- CREATE TRIGGER IF NOT EXISTS populate_cards_resources
+-- AFTER INSERT ON cards
+-- FOR EACH ROW
+-- BEGIN
+--     -- Insert matching words into cards_resources
+--     INSERT INTO cards_expressions(card_id, expression_id)
+--     SELECT NEW.id, w.id 
+--     FROM expressions w 
+--     WHERE NEW.text LIKE '%' || w.spelling || '%';
+-- END;
+--