-- Lightning Categories Table
CREATE TABLE IF NOT EXISTS lightning_categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    name TEXT NOT NULL,
    color TEXT NOT NULL DEFAULT '#1976d2',
    sort_order INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Lightning Sentences Table  
CREATE TABLE IF NOT EXISTS lightning_sentences (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    category_id INTEGER NOT NULL,
    english_text TEXT NOT NULL,
    persian_translation TEXT NOT NULL,
    stage INTEGER DEFAULT 1,
    next_review_date DATE DEFAULT (date('now')),
    is_archived BOOLEAN DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES lightning_categories(id) ON DELETE CASCADE
);

-- Lightning Practice Sessions Table (for future analytics)
CREATE TABLE IF NOT EXISTS lightning_practice_sessions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    category_id INTEGER NOT NULL,
    sentences_count INTEGER NOT NULL,
    completed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES lightning_categories(id) ON DELETE CASCADE
);

-- Lightning Category Stage Cooldowns Table
CREATE TABLE IF NOT EXISTS lightning_category_cooldowns (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    category_id INTEGER NOT NULL,
    stage INTEGER NOT NULL,
    failed_attempts INTEGER DEFAULT 0,
    cooldown_until DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES lightning_categories(id) ON DELETE CASCADE,
    UNIQUE(category_id, stage)
);