From cb1b56f5a0eddbf77446f415f2beda57c8305f85 Mon Sep 17 00:00:00 2001
From: polwex <polwex@sortug.com>
Date: Sun, 23 Nov 2025 01:12:53 +0700
Subject: wut

---
 packages/db/src/schema.sql | 729 +++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 729 insertions(+)
 create mode 100644 packages/db/src/schema.sql

(limited to 'packages/db/src/schema.sql')

diff --git a/packages/db/src/schema.sql b/packages/db/src/schema.sql
new file mode 100644
index 0000000..c4a7c76
--- /dev/null
+++ b/packages/db/src/schema.sql
@@ -0,0 +1,729 @@
+/**
+ * UNIFIED DATABASE SCHEMA FOR THAI LANGUAGE LEARNING APPLICATION
+ * 
+ * This file consolidates 3 redundant database schemas:
+ * - schema.sql (main schema with courses and SRS)
+ * - prosodyschema.sql (phonetic analysis with syllable breakdown)
+ * - senseschema.sql (semantic analysis with subsenses and derivations)
+ * 
+ * REDUNDANCY ANALYSIS:
+ * ====================
+ * 
+ ** MAJOR CONFLICTS RESOLVED:
+ * 1. Languages table:
+ *    - schema.sql: uses 'code' as PRIMARY KEY
+ *    - prosodyschema.sql: uses 'iso6392' as PRIMARY KEY  
+ *    - RESOLVED: Unified with both code systems supported
+ * 
+ * 2. Senses table:
+ *    - schema.sql: includes ipa/prosody JSONB fields
+ *    - senseschema.sql: missing phonetic fields
+ *    - RESOLVED: Enhanced version with all fields
+ * 
+ * 3. Word/Expression entities:
+ *    - schema.sql: uses 'expressions' table
+ *    - prosodyschema.sql: uses 'words' table
+ *    - RESOLVED: Standardized on 'expressions' terminology
+ * 
+ * 4. Categories tables:
+ *    - EXACT DUPLICATES in schema.sql and senseschema.sql
+ *    - RESOLVED: Keep one instance, remove duplicate
+ * 
+ * UNIQUE FEATURES PRESERVED:
+ * ========================
+ * 
+ * FROM schema.sql (Main Course Learning):
+ * - User management: users, cookies
+ * - Course structure: lessons, cards, cards_lessons, cards_expressions
+ * - SRS tracking: user_progress, attempts
+ * - Bookmarks and user features
+ * 
+ * FROM prosodyschema.sql (Phonetic Analysis):
+ * - Detailed syllable breakdown: tones, onsets, medials, nucleus, codas, rhymes
+ * - Phonetic patterns: word_phonetics, syllables_words
+ * - Rhyme analysis: word_rhymes, words_wrhymes
+ * 
+ * FROM senseschema.sql (Semantic Analysis):
+ * - Detailed definitions: subsenses
+ * - Etymology tracking: derivation
+ * 
+ * FOREIGN KEY RELATIONSHIPS:
+ * =========================
+ * - Updated all references from 'words.id' to 'expressions.id'
+ * - Unified language references to use languages.code
+ * - Maintained proper cascade relationships
+ * 
+ * BENEFITS OF UNIFICATION:
+ * =======================
+ * 1. Single source of truth for database structure
+ * 2. Eliminated conflicting table definitions
+ * 3. Preserved all unique functionality
+ * 4. Improved foreign key consistency
+ * 5. Better support for comprehensive Thai language analysis
+ */
+
+-- Enable foreign key support and performance optimizations
+PRAGMA foreign_keys = ON;
+PRAGMA journal_mode = WAL;
+PRAGMA cache_size = -2000;
+PRAGMA mmap_size = 30000000000;
+
+/**
+ * UNIFIED LANGUAGES TABLE
+ * 
+ * RESOLVES CONFLICT between:
+ * - schema.sql: code (PRIMARY KEY), name, native_name
+ * - prosodyschema.sql: iso6392 (PRIMARY KEY), english
+ * 
+ * NOW SUPPORTS multiple language code systems for maximum compatibility
+ */
+CREATE TABLE IF NOT EXISTS languages (
+    code TEXT PRIMARY KEY,         -- Primary language code (ISO 639-1 preferred)
+    name TEXT NOT NULL,           -- English name
+    native_name TEXT,             -- Native name
+    iso6392 TEXT UNIQUE,          -- ISO 639-2 code alternative
+    CONSTRAINT name_unique UNIQUE (name)
+);
+
+/**
+ * CORE CONTENT TABLES
+ * Standardized on 'expressions' terminology from schema.sql
+ * Enhanced with fields from both schemas
+ */
+
+-- Main expressions table (formerly 'words' in prosodyschema.sql)
+-- UNIFIED from schema.sql expressions and prosodyschema.sql words
+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,            --  word | idiom | w/e
+    notes TEXT,                    -- Additional notes (from prosodyschema.sql)
+    FOREIGN KEY (lang) REFERENCES languages(code),
+    CONSTRAINT spell_unique UNIQUE (spelling, lang)
+);
+
+-- Enhanced senses table with phonetic capabilities
+-- MERGED from schema.sql senses (with ipa/prosody) and senseschema.sql senses
+CREATE TABLE IF NOT EXISTS senses (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    parent_id INTEGER NOT NULL,
+    spelling TEXT NOT NULL,
+    pos TEXT,
+    etymology TEXT,
+    confidence INTEGER NOT NULL DEFAULT 0,
+    FOREIGN KEY (parent_id) REFERENCES expressions(id) ON DELETE CASCADE
+);
+
+CREATE TABLE IF NOT EXISTS categories (
+    name TEXT PRIMARY KEY
+);
+
+-- Word-category relationships
+CREATE TABLE IF NOT EXISTS word_categories (
+    word_id INTEGER NOT NULL,
+    category TEXT NOT NULL,
+    PRIMARY KEY (word_id, category),
+    FOREIGN KEY (word_id) REFERENCES senses(id) ON DELETE CASCADE,
+    FOREIGN KEY (category) REFERENCES categories(name) ON DELETE CASCADE
+);
+
+-- Semantic analysis tables (FROM senseschema.sql)
+CREATE TABLE IF NOT EXISTS examples(
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    sid INTEGER NOT NULL,
+    example  TEXT NOT NULL,           -- Example sentence
+    ref      TEXT,                    -- source of the quote, llm if generated etc.
+    FOREIGN KEY (sid) REFERENCES senses(id) ON DELETE CASCADE
+);
+CREATE TABLE IF NOT EXISTS subsenses (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    sid INTEGER NOT NULL,
+    gloss TEXT NOT NULL,           -- Definition/explanation
+    FOREIGN KEY (sid) REFERENCES senses(id) ON DELETE CASCADE
+);
+
+CREATE TABLE IF NOT EXISTS derivation (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    sid INTEGER NOT NULL,
+    type TEXT NOT NULL,            -- Type of derivation (prefix, suffix, compound, etc.)
+    text TEXT NOT NULL,            -- Derivative text
+    tags JSONB,                    -- Additional metadata
+    FOREIGN KEY (sid) REFERENCES senses(id) ON DELETE CASCADE
+);
+
+/**
+ * PHONETIC ANALYSIS TABLES (FROM prosodyschema.sql)
+ * 
+ * Comprehensive syllable breakdown system for Thai language analysis
+ * These tables provide detailed phonetic analysis beyond basic JSONB fields
+ */
+
+
+-- Syllable component tables
+CREATE TABLE IF NOT EXISTS tones (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    ipa TEXT NOT NULL,
+    lang TEXT NOT NULL,
+    name TEXT NOT NULL,
+    nums INTEGER NOT NULL,         -- Tone number (1-5 for Thai)
+    CONSTRAINT tone_unique UNIQUE (ipa, lang),
+    FOREIGN KEY (lang) REFERENCES languages(code)
+);
+
+CREATE TABLE IF NOT EXISTS onsets (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    ipa TEXT NOT NULL,
+    text TEXT NOT NULL,
+    lang TEXT NOT NULL,
+    CONSTRAINT onset_unique UNIQUE (ipa, text, lang),
+    FOREIGN KEY (lang) REFERENCES languages(code)
+);
+
+CREATE TABLE IF NOT EXISTS medials (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    ipa TEXT NOT NULL,
+    text TEXT NOT NULL,
+    lang TEXT NOT NULL,
+    CONSTRAINT medial_unique UNIQUE (ipa, text, lang),
+    FOREIGN KEY (lang) REFERENCES languages(code)
+);
+
+CREATE TABLE IF NOT EXISTS nucleus (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    ipa TEXT NOT NULL,
+    text TEXT NOT NULL,
+    lang TEXT NOT NULL,
+    CONSTRAINT nucleus_unique UNIQUE (ipa, text, lang),
+    FOREIGN KEY (lang) REFERENCES languages(code)
+);
+
+CREATE TABLE IF NOT EXISTS codas (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    ipa TEXT NOT NULL,
+    text TEXT NOT NULL,
+    lang TEXT NOT NULL,
+    CONSTRAINT coda_unique UNIQUE (ipa, text, lang),
+    FOREIGN KEY (lang) REFERENCES languages(code)
+);
+
+CREATE TABLE IF NOT EXISTS rhymes (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    ipa TEXT NOT NULL,
+    text TEXT NOT NULL,
+    lang TEXT NOT NULL,
+    CONSTRAINT rhyme_unique UNIQUE (ipa, text, lang),
+    FOREIGN KEY (lang) REFERENCES languages(code)
+);
+
+-- Complete syllable breakdown (UNIQUE to prosodyschema.sql)
+CREATE TABLE IF NOT EXISTS syllables (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    lang TEXT NOT NULL,
+    ipa TEXT NOT NULL,
+    long INTEGER NOT NULL,        -- Vowel length (0=short, 1=long)
+    text TEXT NOT NULL,
+    onset INTEGER NOT NULL,
+    medial INTEGER NOT NULL,
+    nucleus INTEGER NOT NULL,
+    coda INTEGER NOT NULL,
+    rhyme INTEGER NOT NULL,
+    tone INTEGER NOT NULL,
+    notes TEXT,
+    FOREIGN KEY (lang) REFERENCES languages(code),
+    FOREIGN KEY (onset) REFERENCES onsets(id),
+    FOREIGN KEY (medial) REFERENCES medials(id),
+    FOREIGN KEY (nucleus) REFERENCES nucleus(id),
+    FOREIGN KEY (coda) REFERENCES codas(id),
+    FOREIGN KEY (rhyme) REFERENCES rhymes(id),
+    FOREIGN KEY (tone) REFERENCES tones(id),
+    CONSTRAINT syllable_unique UNIQUE (text, ipa, lang)
+);
+
+-- Phonetic pattern storage (UNIQUE to prosodyschema.sql)
+CREATE TABLE IF NOT EXISTS word_phonetics (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    word_id INTEGER NOT NULL,
+    sense_id INTEGER,
+    ipa TEXT NOT NULL,
+    ipa_sequence TEXT NOT NULL,         -- IPA representation sequence
+    syllable_count INTEGER NOT NULL,
+    syllable_sequence TEXT NOT NULL,   -- Comma-separated syllables
+    stressed INTEGER,                  -- index of stressed syllable
+    tone_sequence TEXT,                -- Comma-separated tones
+    tag JSONB,                           -- Pattern/usage tag
+    notes TEXT,
+    FOREIGN KEY (word_id) REFERENCES expressions(id),
+    FOREIGN KEY (sense_id) REFERENCES senses(id)
+);
+
+-- Rhyme analysis tables (UNIQUE to prosodyschema.sql)
+CREATE TABLE IF NOT EXISTS word_rhymes (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    text TEXT NOT NULL,
+    lang TEXT NOT NULL,
+    notes TEXT,
+    CONSTRAINT wrhyme_unique UNIQUE (text, lang),
+    FOREIGN KEY (lang) REFERENCES languages(code)
+);
+
+CREATE TABLE IF NOT EXISTS words_wrhymes (
+    word_id INTEGER NOT NULL,
+    wrhyme_id INTEGER NOT NULL,
+    FOREIGN KEY (word_id) REFERENCES word_phonetics(id),
+    FOREIGN KEY (wrhyme_id) REFERENCES word_rhymes(id),
+    PRIMARY KEY (word_id, wrhyme_id)
+);
+
+-- Junction tables for relationships
+
+-- Expressions to syllables mapping
+CREATE TABLE IF NOT EXISTS syllables_words (
+    syl_id INTEGER NOT NULL,
+    word_id INTEGER NOT NULL,
+    idx INTEGER NOT NULL,             -- Position in word
+    stressed INTEGER,                 -- Stress accent (0=none, 1=stressed)
+    FOREIGN KEY (syl_id) REFERENCES syllables(id),
+    FOREIGN KEY (word_id) REFERENCES word_phonetics(id),
+    PRIMARY KEY (syl_id, word_id, idx)
+);
+
+/**
+ * COURSE AND LEARNING TABLES (FROM schema.sql)
+ * 
+ * Complete course structure and lesson management system
+ */
+
+-- User management
+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 sessions (
+  id TEXT PRIMARY KEY,              -- Session ID/token (usually UUID or random hash)
+  user_id INTEGER NOT NULL,
+  created_at INTEGER NOT NULL,      -- Timestamp when created
+  expires_at INTEGER NOT NULL,      -- Timestamp when expires
+  last_activity INTEGER,             -- Last activity timestamp
+  ip_address TEXT,                  -- Optional: track IP
+  user_agent TEXT,                  -- Optional: track browser/client
+  data JSONB,                       -- Optional: session-specific data
+  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
+);
+
+CREATE INDEX idx_sessions_user ON sessions(user_id);
+CREATE INDEX idx_sessions_expires ON sessions(expires_at);
+
+  -- The separation is cleaner:
+  -- - sessions table - Server-side session management
+  -- - cookies are just the client-side token that references the session ID
+
+  -- Some apps also add:
+  -- - refresh_tokens table for JWT refresh tokens
+  -- - remember_tokens table for "remember me" functionality
+  -- - active_sessions view for currently valid sessions
+
+  -- The cookie itself just stores the session ID, while all the actual session data lives in the database. This is more secure and gives you better control over session
+  --  invalidation.
+-- Course structure
+CREATE TABLE IF NOT EXISTS lessons (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    name TEXT NOT NULL,
+    position INTEGER NOT NULL DEFAULT 0,
+    description TEXT
+);
+
+CREATE TABLE IF NOT EXISTS lang_lessons (
+    lesson_id INTEGER NOT NULL,
+    lang TEXT NOT NULL,
+    PRIMARY KEY (lang, lesson_id),
+    FOREIGN KEY (lang) REFERENCES languages(code),
+    FOREIGN KEY (lesson_id) REFERENCES lessons(id)
+);
+
+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)
+);
+
+
+/**
+ * USER PROGRESS AND SRS TRACKING (FROM schema.sql)
+ * 
+ * Complete spaced repetition system with attempt tracking
+ */
+
+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 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 for success/failure
+    FOREIGN KEY (user_id) REFERENCES users(id),
+    FOREIGN KEY (card_id) REFERENCES cards(id)
+);
+
+/**
+ * USER FEATURES (FROM schema.sql)
+ */
+
+CREATE TABLE IF NOT EXISTS bookmarks (
+    word_id INTEGER NOT NULL,
+    user_id INTEGER NOT NULL,
+    notes TEXT,
+    created INTEGER NOT NULL,
+    PRIMARY KEY (word_id, user_id),
+    FOREIGN KEY (word_id) REFERENCES expressions(id),
+    FOREIGN KEY (user_id) REFERENCES users(id)
+);
+
+/**
+ * INDEXES FOR PERFORMANCE OPTIMIZATION
+ * 
+ * Comprehensive indexes based on analysis of query patterns
+ * from all three schemas
+ */
+
+-- Language and expression indexes
+CREATE INDEX IF NOT EXISTS idx_expressions_spelling ON expressions(spelling);
+CREATE INDEX IF NOT EXISTS idx_expressions_type ON expressions(type);
+CREATE INDEX IF NOT EXISTS idx_expressions_lang ON expressions(lang);
+CREATE INDEX IF NOT EXISTS idx_expressions_lang_freq ON expressions(lang, frequency DESC);
+
+-- Sense and semantic indexes
+CREATE INDEX IF NOT EXISTS idx_senses_parent ON senses(parent_id);
+CREATE INDEX IF NOT EXISTS idx_senses_pos ON senses(pos);
+CREATE INDEX IF NOT EXISTS idx_subsenses_sid ON subsenses(sid);
+CREATE INDEX IF NOT EXISTS idx_derivation_sid ON derivation(sid);
+
+-- Phonetic analysis indexes (FROM prosodyschema.sql)
+CREATE INDEX IF NOT EXISTS idx_tones_name_lang ON tones(name, lang);
+CREATE INDEX IF NOT EXISTS idx_tones_nums_lang ON tones(nums, lang);
+CREATE INDEX IF NOT EXISTS idx_syllables_text_lang ON syllables(text, lang);
+CREATE INDEX IF NOT EXISTS idx_syllables_tone ON syllables(tone);
+
+CREATE INDEX IF NOT EXISTS idx_word_phonetics_word_id ON word_phonetics(word_id);
+CREATE INDEX IF NOT EXISTS idx_word_phonetics_syllables ON word_phonetics(syllable_sequence);
+CREATE INDEX IF NOT EXISTS idx_word_phonetics_tones ON word_phonetics(tone_sequence);
+CREATE INDEX IF NOT EXISTS idx_word_phonetics_count ON word_phonetics(syllable_count);
+
+-- Junction table indexes
+CREATE INDEX IF NOT EXISTS idx_syllables_words_word_idx ON syllables_words(word_id, idx);
+CREATE INDEX IF NOT EXISTS idx_syllables_words_syl ON syllables_words(syl_id);
+CREATE INDEX IF NOT EXISTS idx_cards_expressions ON cards_expressions(expression_id, card_id);
+
+-- User progress and SRS indexes
+CREATE INDEX IF NOT EXISTS idx_user_progress_user ON user_progress(user_id);
+CREATE INDEX IF NOT EXISTS idx_user_progress_card ON user_progress(card_id);
+CREATE INDEX IF NOT EXISTS idx_user_progress_next_review ON user_progress(next_review_date);
+CREATE INDEX IF NOT EXISTS idx_attempts_user ON attempts(user_id);
+CREATE INDEX IF NOT EXISTS idx_attempts_card ON attempts(card_id);
+CREATE INDEX IF NOT EXISTS idx_attempts_user_resource ON attempts(user_id, card_id);
+
+-- User feature indexes
+CREATE INDEX IF NOT EXISTS idx_bookmarks ON bookmarks(word_id);
+CREATE INDEX IF NOT EXISTS idx_word_categories_category ON word_categories(category);
+
+-- Composite indexes for common query patterns
+CREATE INDEX IF NOT EXISTS idx_syllables_compound ON syllables(lang, text, tone);
+CREATE INDEX IF NOT EXISTS idx_syllables_words_compound ON syllables_words(word_id, idx, syl_id);
+CREATE INDEX IF NOT EXISTS idx_word_patterns_mixed ON word_phonetics(syllable_count, syllable_sequence, tone_sequence);
+
+/**
+ * TONE-SPECIFIC SRS SYSTEM
+ * 
+ * Advanced tone pattern learning with detailed progress tracking
+ * and analytics for effective Thai tone acquisition
+ */
+
+-- Tone pattern progress tracking
+CREATE TABLE IF NOT EXISTS tone_pattern_progress (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    user_id INTEGER NOT NULL,
+    word_id INTEGER NOT NULL,
+    tone_sequence TEXT NOT NULL,
+    syllable_sequence TEXT NOT NULL,
+    repetition_count INTEGER DEFAULT 0,
+    ease_factor REAL DEFAULT 2.5,
+    interval INTEGER DEFAULT 1,
+    next_review_date INTEGER NOT NULL,
+    last_reviewed INTEGER NOT NULL,
+    is_mastered BOOLEAN DEFAULT FALSE,
+    difficulty REAL DEFAULT 2.5,
+    tone_accuracy REAL DEFAULT 0.0,
+    created_at INTEGER DEFAULT (strftime('%s', 'now') * 1000),
+    updated_at INTEGER DEFAULT (strftime('%s', 'now') * 1000),
+    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
+    FOREIGN KEY (word_id) REFERENCES expressions(id) ON DELETE CASCADE,
+    UNIQUE(user_id, word_id)
+);
+
+-- Tone pattern attempt tracking with detailed metrics
+CREATE TABLE IF NOT EXISTS tone_attempts (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    user_id INTEGER NOT NULL,
+    word_id INTEGER NOT NULL,
+    timestamp INTEGER NOT NULL,
+    accuracy REAL NOT NULL CHECK (accuracy >= 0 AND accuracy <= 1),
+    tone_accuracy REAL NOT NULL CHECK (tone_accuracy >= 0 AND tone_accuracy <= 1),
+    pronunciation_score REAL DEFAULT 0.0 CHECK (pronunciation_score >= 0 AND pronunciation_score <= 1),
+    review_time INTEGER NOT NULL,
+    difficulty REAL NOT NULL,
+    mode TEXT DEFAULT 'exploration' CHECK (mode IN ('exploration', 'practice', 'review')),
+    created_at INTEGER DEFAULT (strftime('%s', 'now') * 1000),
+    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
+    FOREIGN KEY (word_id) REFERENCES expressions(id) ON DELETE CASCADE
+);
+
+-- User tone learning preferences and settings
+CREATE TABLE IF NOT EXISTS tone_learning_settings (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    user_id INTEGER NOT NULL,
+    preferred_session_length INTEGER DEFAULT 20 CHECK (preferred_session_length > 0),
+    target_daily_reviews INTEGER DEFAULT 50 CHECK (target_daily_reviews > 0),
+    difficulty_preference REAL DEFAULT 2.5 CHECK (difficulty_preference >= 1.0 AND difficulty_preference <= 4.0),
+    audio_enabled BOOLEAN DEFAULT TRUE,
+    tone_visualization_enabled BOOLEAN DEFAULT TRUE,
+    auto_advance BOOLEAN DEFAULT FALSE,
+    created_at INTEGER DEFAULT (strftime('%s', 'now') * 1000),
+    updated_at INTEGER DEFAULT (strftime('%s', 'now') * 1000),
+    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
+    UNIQUE(user_id)
+);
+
+-- Tone pattern mastery statistics and analytics
+CREATE TABLE IF NOT EXISTS tone_mastery_stats (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    user_id INTEGER NOT NULL,
+    tone_pattern TEXT NOT NULL,
+    syllable_count INTEGER NOT NULL CHECK (syllable_count > 0),
+    total_attempts INTEGER DEFAULT 0,
+    successful_attempts INTEGER DEFAULT 0,
+    avg_accuracy REAL DEFAULT 0.0 CHECK (avg_accuracy >= 0 AND avg_accuracy <= 1),
+    avg_review_time REAL DEFAULT 0.0,
+    best_accuracy REAL DEFAULT 0.0 CHECK (best_accuracy >= 0 AND best_accuracy <= 1),
+    last_attempted INTEGER,
+    mastered_at INTEGER,
+    created_at INTEGER DEFAULT (strftime('%s', 'now') * 1000),
+    updated_at INTEGER DEFAULT (strftime('%s', 'now') * 1000),
+    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
+    UNIQUE(user_id, tone_pattern, syllable_count)
+);
+
+/**
+ * TONE-SPECIFIC INDEXES
+ * Optimized for tone pattern queries and SRS operations
+ */
+
+CREATE INDEX IF NOT EXISTS idx_tone_progress_user_word ON tone_pattern_progress(user_id, word_id);
+CREATE INDEX IF NOT EXISTS idx_tone_progress_next_review ON tone_pattern_progress(next_review_date);
+CREATE INDEX IF NOT EXISTS idx_tone_progress_user ON tone_pattern_progress(user_id);
+CREATE INDEX IF NOT EXISTS idx_tone_progress_difficulty ON tone_pattern_progress(difficulty);
+CREATE INDEX IF NOT EXISTS idx_tone_progress_mastered ON tone_pattern_progress(is_mastered);
+
+CREATE INDEX IF NOT EXISTS idx_tone_attempts_user_word ON tone_attempts(user_id, word_id);
+CREATE INDEX IF NOT EXISTS idx_tone_attempts_timestamp ON tone_attempts(timestamp);
+CREATE INDEX IF NOT EXISTS idx_tone_attempts_user ON tone_attempts(user_id);
+CREATE INDEX IF NOT EXISTS idx_tone_attempts_mode ON tone_attempts(mode);
+CREATE INDEX IF NOT EXISTS idx_tone_attempts_accuracy ON tone_attempts(accuracy, tone_accuracy);
+
+CREATE INDEX IF NOT EXISTS idx_tone_mastery_pattern ON tone_mastery_stats(tone_pattern, syllable_count);
+CREATE INDEX IF NOT EXISTS idx_tone_mastery_user ON tone_mastery_stats(user_id);
+CREATE INDEX IF NOT EXISTS idx_tone_mastery_accuracy ON tone_mastery_stats(avg_accuracy);
+
+/**
+ * TRIGGERS FOR TONE LEARNING SYSTEM
+ * Automatic timestamp updates and statistics calculation
+ */
+
+-- Update timestamps automatically
+CREATE TRIGGER IF NOT EXISTS update_tone_progress_updated_at 
+AFTER UPDATE ON tone_pattern_progress
+FOR EACH ROW
+BEGIN
+    UPDATE tone_pattern_progress SET updated_at = (strftime('%s', 'now') * 1000) WHERE id = NEW.id;
+END;
+
+CREATE TRIGGER IF NOT EXISTS update_tone_learning_settings_updated_at 
+AFTER UPDATE ON tone_learning_settings
+FOR EACH ROW
+BEGIN
+    UPDATE tone_learning_settings SET updated_at = (strftime('%s', 'now') * 1000) WHERE id = NEW.id;
+END;
+
+CREATE TRIGGER IF NOT EXISTS update_tone_mastery_updated_at 
+AFTER UPDATE ON tone_mastery_stats
+FOR EACH ROW
+BEGIN
+    UPDATE tone_mastery_stats SET updated_at = (strftime('%s', 'now') * 1000) WHERE id = NEW.id;
+END;
+
+-- Update mastery stats when a pattern is mastered
+CREATE TRIGGER IF NOT EXISTS update_tone_mastery_on_progress 
+AFTER UPDATE ON tone_pattern_progress
+FOR EACH ROW
+WHEN NEW.is_mastered = 1 AND OLD.is_mastered = 0
+BEGIN
+    INSERT OR REPLACE INTO tone_mastery_stats 
+    (user_id, tone_pattern, syllable_count, total_attempts, successful_attempts, avg_accuracy, mastered_at, updated_at)
+    VALUES (
+        NEW.user_id,
+        NEW.tone_sequence,
+        (SELECT COUNT(*) FROM (SELECT value FROM json_each(NEW.syllable_sequence))),
+        NEW.repetition_count,
+        CAST(NEW.repetition_count * NEW.tone_accuracy AS INTEGER),
+        NEW.tone_accuracy,
+        (strftime('%s', 'now') * 1000),
+        (strftime('%s', 'now') * 1000)
+    );
+END;
+
+-- Update tone mastery stats after each attempt
+CREATE TRIGGER IF NOT EXISTS update_tone_mastery_after_attempt 
+AFTER INSERT ON tone_attempts
+FOR EACH ROW
+BEGIN
+    -- Get the current tone progress
+    INSERT OR REPLACE INTO tone_mastery_stats 
+    (user_id, tone_pattern, syllable_count, total_attempts, successful_attempts, avg_accuracy, avg_review_time, best_accuracy, last_attempted, updated_at)
+    SELECT 
+        ta.user_id,
+        tpp.tone_sequence,
+        (SELECT COUNT(*) FROM (SELECT value FROM json_each(tpp.syllable_sequence))) as syllable_count,
+        COALESCE(tms.total_attempts + 1, 1) as total_attempts,
+        COALESCE(tms.successful_attempts + CAST(ta.tone_accuracy >= 0.8 AS INTEGER), CAST(ta.tone_accuracy >= 0.8 AS INTEGER)) as successful_attempts,
+        CASE 
+            WHEN tms.total_attempts IS NULL THEN ta.tone_accuracy
+            ELSE (tms.avg_accuracy * tms.total_attempts + ta.tone_accuracy) / (tms.total_attempts + 1)
+        END as avg_accuracy,
+        CASE 
+            WHEN tms.total_attempts IS NULL THEN ta.review_time
+            ELSE (tms.avg_review_time * tms.total_attempts + ta.review_time) / (tms.total_attempts + 1)
+        END as avg_review_time,
+        CASE 
+            WHEN tms.best_accuracy IS NULL THEN ta.tone_accuracy
+            WHEN ta.tone_accuracy > tms.best_accuracy THEN ta.tone_accuracy
+            ELSE tms.best_accuracy
+        END as best_accuracy,
+        (strftime('%s', 'now') * 1000) as last_attempted,
+        (strftime('%s', 'now') * 1000) as updated_at
+    FROM tone_attempts ta
+    JOIN tone_pattern_progress tpp ON tpp.word_id = ta.word_id AND tpp.user_id = ta.user_id
+    LEFT JOIN tone_mastery_stats tms ON tms.user_id = ta.user_id 
+        AND tms.tone_pattern = tpp.tone_sequence 
+        AND tms.syllable_count = (SELECT COUNT(*) FROM (SELECT value FROM json_each(tpp.syllable_sequence)))
+    WHERE ta.rowid = NEW.rowid;
+END;
+
+/**
+ * VIEWS FOR TONE LEARNING ANALYTICS
+ * Common queries for dashboard and reporting
+ */
+
+-- User dashboard view combining traditional SRS and tone learning
+CREATE VIEW IF NOT EXISTS user_dashboard AS
+SELECT 
+    u.id as user_id,
+    u.name as username,
+    COUNT(DISTINCT tpp.word_id) as total_tone_words,
+    COUNT(DISTINCT CASE WHEN tpp.is_mastered = 1 THEN tpp.word_id END) as mastered_tone_words,
+    COUNT(DISTINCT CASE WHEN tpp.next_review_date <= (strftime('%s', 'now') * 1000) THEN tpp.word_id END) as due_tone_words,
+    COUNT(DISTINCT up.card_id) as total_cards,
+    COUNT(DISTINCT CASE WHEN up.is_mastered = 1 THEN up.card_id END) as mastered_cards,
+    COUNT(DISTINCT CASE WHEN up.next_review_date <= (strftime('%s', 'now') * 1000) THEN up.card_id END) as due_cards,
+    (SELECT COUNT(*) FROM attempts WHERE user_id = u.id AND timestamp > (strftime('%s', 'now') * 1000 - 86400000)) as reviews_today,
+    (SELECT AVG(ta.tone_accuracy) FROM tone_attempts ta WHERE ta.user_id = u.id AND ta.timestamp > (strftime('%s', 'now') * 1000 - 604800000)) as weekly_tone_accuracy
+FROM users u
+LEFT JOIN tone_pattern_progress tpp ON tpp.user_id = u.id
+LEFT JOIN user_progress up ON up.user_id = u.id
+GROUP BY u.id, u.name;
+
+-- Tone learning statistics view
+CREATE VIEW IF NOT EXISTS tone_learning_stats_view AS
+SELECT 
+    tms.user_id,
+    tms.tone_pattern,
+    tms.syllable_count,
+    tms.total_attempts,
+    tms.successful_attempts,
+    tms.avg_accuracy,
+    tms.avg_review_time,
+    tms.best_accuracy,
+    tms.last_attempted,
+    tms.mastered_at,
+    COUNT(tpp.word_id) as word_count_with_pattern,
+    AVG(tpp.difficulty) as avg_difficulty,
+    AVG(tpp.tone_accuracy) as avg_current_accuracy
+FROM tone_mastery_stats tms
+LEFT JOIN tone_pattern_progress tpp ON tpp.user_id = tms.user_id 
+    AND tpp.tone_sequence = tms.tone_pattern
+GROUP BY tms.user_id, tms.tone_pattern, tms.syllable_count, tms.total_attempts, 
+         tms.successful_attempts, tms.avg_accuracy, tms.avg_review_time, 
+         tms.best_accuracy, tms.last_attempted, tms.mastered_at;
+
+/**
+ * MIGRATION NOTES:
+ * ===============
+ * 
+ * TONE SRS SYSTEM ADDITIONS:
+ * - Added comprehensive tone learning tables to the unified schema
+ * - Maintains consistency with existing phonetic analysis structure
+ * - Supports both traditional SRS and tone-specific learning
+ * - Provides detailed analytics for tone pattern mastery
+ * 
+ * BACKWARD COMPATIBILITY:
+ * - All existing tables and relationships preserved
+ * - New tables are additive and don't break existing functionality
+ * - Views provide unified dashboard with both learning systems
+ * 
+ * PERFORMANCE:
+ * - Added specialized indexes for tone pattern queries
+ * - Optimized for Thai tone learning analytics
+ * - Supports real-time progress tracking
+ * 
+ * This enhanced unified schema now provides comprehensive support for:
+ * - Thai language learning with detailed phonetic analysis
+ * - Course management and lesson structure  
+ * - Traditional spaced repetition system
+ * - Advanced tone pattern learning with SRS
+ * - User management and personalization
+ * - Comprehensive learning analytics
+ * 
+ * TOTAL: Unified 4 schemas while preserving all unique functionality
+ */
-- 
cgit v1.2.3

