db_init.py

INIT_SQLS = [
    # ── Archives ──
    """CREATE TABLE IF NOT EXISTS archives (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        archive_id TEXT,
        media_id INTEGER,
        media_type TEXT,
        file_name TEXT,
        dataset_name TEXT,
        account TEXT,
        account_key TEXT,
        title TEXT,
        year INTEGER,
        season_number INTEGER,
        episode_number INTEGER,
        quality TEXT,
        file_size INTEGER,
        status TEXT DEFAULT 'archived',
        telegram_file_unique_id TEXT,
        timestamp TEXT,
        genres TEXT,
        rating REAL,
        progress_status TEXT,
        progress_details TEXT,
        archive_space TEXT,
        source TEXT,
        audio TEXT,
        network TEXT,
        highlights TEXT,
        full_metadata TEXT,
        storage_channel_id INTEGER,
        storage_msg_id INTEGER,
        created_at TEXT DEFAULT (datetime('now'))
    )""",
    # ── User-related ──
    """CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER UNIQUE NOT NULL,
        username TEXT, first_name TEXT, last_name TEXT,
        photo_url TEXT, language_code TEXT DEFAULT 'en',
        stars_balance INTEGER DEFAULT 0, kernels_balance INTEGER DEFAULT 0,
        total_earned_kernels INTEGER DEFAULT 0, total_spent_kernels INTEGER DEFAULT 0,
        is_premium INTEGER DEFAULT 0, last_active_at TEXT,
        total_watch_time INTEGER DEFAULT 0, points INTEGER DEFAULT 0,
        created_at TEXT DEFAULT (datetime('now'))
    )""",
    # ── Watch history ──
    """CREATE TABLE IF NOT EXISTS watch_history (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL, tmdb_id INTEGER NOT NULL,
        media_type TEXT NOT NULL, season_number INTEGER, episode_number INTEGER,
        progress_seconds INTEGER DEFAULT 0, duration_seconds INTEGER DEFAULT 0,
        watch_count INTEGER DEFAULT 1, completed INTEGER DEFAULT 0,
        last_watched_at TEXT,
        UNIQUE(user_id, tmdb_id, media_type, COALESCE(season_number, 0), COALESCE(episode_number, 0))
    )""",
    # ── Watch list ──
    """CREATE TABLE IF NOT EXISTS user_list (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL, tmdb_id INTEGER NOT NULL,
        media_type TEXT NOT NULL CHECK(media_type IN ('movie', 'tv')),
        title TEXT NOT NULL DEFAULT '', poster_url TEXT,
        added_at TEXT DEFAULT (datetime('now')),
        UNIQUE(user_id, tmdb_id, media_type)
    )""",
    # ── Ratings ──
    """CREATE TABLE IF NOT EXISTS ratings (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL, tmdb_id INTEGER NOT NULL,
        media_type TEXT NOT NULL, rating INTEGER NOT NULL CHECK(rating >= 1 AND rating <= 10),
        review TEXT, created_at TEXT DEFAULT (datetime('now')),
        updated_at TEXT DEFAULT (datetime('now')),
        UNIQUE(user_id, tmdb_id, media_type)
    )""",
    # ── Friendships ──
    """CREATE TABLE IF NOT EXISTS friendships (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL, friend_id INTEGER NOT NULL,
        status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','accepted','rejected')),
        created_at TEXT DEFAULT (datetime('now')),
        UNIQUE(user_id, friend_id)
    )""",
    # ── Conversations ──
    """CREATE TABLE IF NOT EXISTS conversations (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL, participant_id INTEGER NOT NULL,
        last_message TEXT, last_message_at TEXT, unread_count INTEGER DEFAULT 0,
        created_at TEXT DEFAULT (datetime('now')),
        UNIQUE(user_id, participant_id)
    )""",
    # ── Messages ──
    """CREATE TABLE IF NOT EXISTS messages (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        conversation_id INTEGER NOT NULL, sender_id INTEGER NOT NULL,
        content TEXT NOT NULL, created_at TEXT DEFAULT (datetime('now'))
    )""",
    # ── Watch parties ──
    """CREATE TABLE IF NOT EXISTS watch_parties (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        room_code TEXT UNIQUE NOT NULL, host_id INTEGER NOT NULL,
        tmdb_id INTEGER, media_type TEXT, title TEXT,
        is_public INTEGER DEFAULT 1, password TEXT DEFAULT '',
        max_participants INTEGER DEFAULT 10,
        status TEXT DEFAULT 'waiting' CHECK(status IN ('waiting','playing','paused','ended')),
        created_at TEXT DEFAULT (datetime('now'))
    )""",
    """CREATE TABLE IF NOT EXISTS party_members (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        party_id INTEGER NOT NULL, user_id INTEGER NOT NULL,
        role TEXT DEFAULT 'member' CHECK(role IN ('host','member')),
        joined_at TEXT DEFAULT (datetime('now')),
        UNIQUE(party_id, user_id)
    )""",
    """CREATE TABLE IF NOT EXISTS party_chat (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        party_id INTEGER NOT NULL, user_id INTEGER NOT NULL,
        user_name TEXT NOT NULL DEFAULT '', content TEXT NOT NULL,
        created_at TEXT DEFAULT (datetime('now'))
    )""",
    # ── Store ──
    """CREATE TABLE IF NOT EXISTS products (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name_en TEXT NOT NULL, name_ar TEXT NOT NULL,
        description_en TEXT, description_ar TEXT,
        category TEXT, rarity TEXT,
        price_stars INTEGER DEFAULT 0, price_kernels INTEGER DEFAULT 0,
        image_url TEXT, sort_order INTEGER DEFAULT 0, active INTEGER DEFAULT 1,
        is_purchasable INTEGER DEFAULT 1, preview_images TEXT DEFAULT '[]',
        tags TEXT DEFAULT '', payment_method TEXT DEFAULT 'stars',
        created_at TEXT DEFAULT (datetime('now')), updated_at TEXT DEFAULT (datetime('now'))
    )""",
    """CREATE TABLE IF NOT EXISTS asset_items (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        product_id INTEGER, type TEXT, name_en TEXT,
        image_url TEXT, rarity TEXT, sort_order INTEGER DEFAULT 0,
        file_url TEXT DEFAULT '', preview_url TEXT DEFAULT '',
        metadata TEXT DEFAULT '{}'
    )""",
    """CREATE TABLE IF NOT EXISTS user_assets (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL, asset_type TEXT, asset_id INTEGER,
        is_active INTEGER DEFAULT 0, acquired_at TEXT DEFAULT (datetime('now'))
    )""",
    """CREATE TABLE IF NOT EXISTS purchases (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL, product_id INTEGER,
        stars_amount INTEGER DEFAULT 0, status TEXT DEFAULT 'completed',
        purchased_at TEXT DEFAULT (datetime('now'))
    )""",
    # ── Notifications ──
    """CREATE TABLE IF NOT EXISTS notifications (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL, title TEXT NOT NULL, body TEXT,
        type TEXT DEFAULT 'info', is_read INTEGER DEFAULT 0,
        created_at TEXT DEFAULT (datetime('now'))
    )""",
    # ── Achievements ──
    """CREATE TABLE IF NOT EXISTS achievements (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT UNIQUE NOT NULL, description TEXT, icon TEXT,
        category TEXT, total_required INTEGER DEFAULT 1
    )""",
    """CREATE TABLE IF NOT EXISTS user_achievements (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL, achievement_id INTEGER NOT NULL,
        progress INTEGER DEFAULT 0, total INTEGER DEFAULT 1,
        is_unlocked INTEGER DEFAULT 0, unlocked_at TEXT,
        UNIQUE(user_id, achievement_id)
    )""",
    # ── Daily streaks ──
    """CREATE TABLE IF NOT EXISTS daily_streaks (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL UNIQUE, streak_count INTEGER DEFAULT 0,
        last_claim_date TEXT
    )""",
    # ── Comments ──
    """CREATE TABLE IF NOT EXISTS comments (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL, tmdb_id INTEGER NOT NULL,
        media_type TEXT NOT NULL, content TEXT NOT NULL,
        rating INTEGER, created_at TEXT DEFAULT (datetime('now'))
    )""",
    """CREATE TABLE IF NOT EXISTS comment_likes (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        comment_id INTEGER NOT NULL, user_id INTEGER NOT NULL,
        created_at TEXT DEFAULT (datetime('now')),
        UNIQUE(comment_id, user_id)
    )""",
    """CREATE TABLE IF NOT EXISTS comment_reports (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        comment_id INTEGER NOT NULL, user_id INTEGER NOT NULL,
        reason TEXT NOT NULL, created_at TEXT DEFAULT (datetime('now'))
    )""",
    """CREATE TABLE IF NOT EXISTS rating_helpful (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        rating_id INTEGER NOT NULL, user_id INTEGER NOT NULL,
        created_at TEXT DEFAULT (datetime('now')),
        UNIQUE(rating_id, user_id)
    )""",
    # ── Archive progress (survives restarts) ──
    """CREATE TABLE IF NOT EXISTS archive_progress (
        archive_id TEXT PRIMARY KEY,
        status TEXT DEFAULT 'pending',
        file_name TEXT, account TEXT, account_key TEXT, quality TEXT,
        file_unique_id TEXT, step TEXT, progress_pct INTEGER DEFAULT 0,
        message TEXT, updated_at REAL
    )""",
]

INDEX_SQLS = [
    "CREATE INDEX IF NOT EXISTS idx_archives_media_id ON archives(media_id)",
    "CREATE INDEX IF NOT EXISTS idx_archives_media_type ON archives(media_type)",
    "CREATE INDEX IF NOT EXISTS idx_archives_status ON archives(status)",
    "CREATE INDEX IF NOT EXISTS idx_archives_type_status ON archives(media_type, status)",
    "CREATE INDEX IF NOT EXISTS idx_archives_account ON archives(account_key)",
    "CREATE INDEX IF NOT EXISTS idx_archives_unique_id ON archives(telegram_file_unique_id)",
    "CREATE INDEX IF NOT EXISTS idx_watch_history_user ON watch_history(user_id)",
    "CREATE INDEX IF NOT EXISTS idx_watch_history_tmdb ON watch_history(tmdb_id, media_type)",
    "CREATE INDEX IF NOT EXISTS idx_friendships_user ON friendships(user_id, status)",
    "CREATE INDEX IF NOT EXISTS idx_friendships_friend ON friendships(friend_id, status)",
    "CREATE INDEX IF NOT EXISTS idx_messages_conv ON messages(conversation_id)",
    "CREATE INDEX IF NOT EXISTS idx_notifications_user ON notifications(user_id, is_read)",
    "CREATE INDEX IF NOT EXISTS idx_user_assets_user ON user_assets(user_id)",
    "CREATE INDEX IF NOT EXISTS idx_purchases_user ON purchases(user_id)",
    "CREATE INDEX IF NOT EXISTS idx_party_members_party ON party_members(party_id)",
    "CREATE INDEX IF NOT EXISTS idx_party_chat_party ON party_chat(party_id)",
    "CREATE INDEX IF NOT EXISTS idx_conversations_user ON conversations(user_id)",
]

FTS_SQLS = [
    "CREATE VIRTUAL TABLE IF NOT EXISTS archives_fts USING fts5(title, overview, content='archives', content_rowid='id')",
]

SEED_SQLS = [
    """INSERT OR IGNORE INTO products (id, name_en, name_ar, category, rarity, price_stars, price_kernels, image_url, sort_order)
    VALUES
    (1, 'Gold Frame', 'إطار ذهبي', 'frames', 'legendary', 500, 50, '/assets/badges/gold-frame.svg', 1),
    (2, 'Silver Frame', 'إطار فضي', 'frames', 'epic', 300, 30, '/assets/badges/silver-frame.svg', 2),
    (3, 'Neon Theme', 'سمة نيون', 'themes', 'epic', 400, 40, '/assets/badges/neon-theme.svg', 3),
    (4, 'Dark Mode Pro', 'الوضع الداكن برو', 'themes', 'rare', 200, 20, '/assets/badges/dark-pro.svg', 4),
    (5, 'Rainbow Badge', 'شارة قوس قزح', 'badges', 'legendary', 1000, 100, '/assets/badges/rainbow.svg', 5),
    (6, 'PopCorn Crown', 'تاج بوبكورن', 'badges', 'legendary', 2000, 200, '/assets/badges/crown.svg', 6)
    """,
    """INSERT OR IGNORE INTO asset_items (product_id, type, name_en, image_url, rarity, sort_order)
    VALUES
    (1, 'frame', 'Gold Frame', '/assets/badges/gold-frame.svg', 'legendary', 1),
    (2, 'frame', 'Silver Frame', '/assets/badges/silver-frame.svg', 'epic', 2),
    (3, 'theme', 'Neon Theme', '/assets/badges/neon-theme.svg', 'epic', 3),
    (4, 'theme', 'Dark Mode Pro', '/assets/badges/dark-pro.svg', 'rare', 4),
    (5, 'badge', 'Rainbow Badge', '/assets/badges/rainbow.svg', 'legendary', 5),
    (6, 'badge', 'PopCorn Crown', '/assets/badges/crown.svg', 'legendary', 6)
    """,
    """INSERT OR IGNORE INTO achievements (id, name, description, icon, category, total_required) VALUES
    (1, 'First Watch', 'Watch your first movie', '\U0001f3ac', 'watching', 1),
    (2, 'Movie Marathon', 'Watch 10 movies', '\U0001f4fa', 'watching', 10),
    (3, 'PopCorn Lover', 'Watch 50 movies', '\U0001f37f', 'watching', 50),
    (4, 'Binge Watcher', 'Watch 100 movies', '\U0001f525', 'watching', 100),
    (5, 'Social Butterfly', 'Make 5 friends', '\U0001f98b', 'social', 5),
    (6, 'Party Starter', 'Create 3 watch parties', '\U0001f389', 'social', 3),
    (7, 'Collector', 'Buy 5 items from the store', '\U0001f48e', 'store', 5),
    (8, 'High Roller', 'Earn 1000 kernels', '\U0001f4b0', 'earning', 1000)
    """,
]