database/schema.py

# database/schema.py
import logging
from database.connection import get_conn

logger = logging.getLogger(__name__)


def init_db() -> None:
    conn = get_conn()
    c    = conn.cursor()
    try:
        # ────────────────────────────────────────────────────────
        # جدول المستخدمين
        # ────────────────────────────────────────────────────────
        c.execute("""
            CREATE TABLE IF NOT EXISTS users (
                user_id      INTEGER PRIMARY KEY,
                username     TEXT    DEFAULT '',
                first_name   TEXT    DEFAULT '',
                last_name    TEXT    DEFAULT '',
                joined_at    TEXT    DEFAULT (datetime('now','localtime')),
                search_count INTEGER DEFAULT 0,
                last_game    TEXT    DEFAULT '',
                referred_by  INTEGER DEFAULT NULL,
                is_banned    INTEGER DEFAULT 0
            )
        """)
        c.execute("CREATE INDEX IF NOT EXISTS idx_users_joined ON users(joined_at)")

        # ────────────────────────────────────────────────────────
        # جدول المنشورات المفهرسة
        # ────────────────────────────────────────────────────────
        c.execute("""
            CREATE TABLE IF NOT EXISTS posts (
                id                INTEGER PRIMARY KEY AUTOINCREMENT,
                message_id        INTEGER NOT NULL,
                channel_id        INTEGER NOT NULL,
                channel_username  TEXT    DEFAULT '',
                text_original     TEXT    DEFAULT '',
                text_normalized   TEXT    DEFAULT '',
                hashtags          TEXT    DEFAULT '',
                platform          TEXT    DEFAULT '',
                has_media         INTEGER DEFAULT 0,
                media_type        TEXT    DEFAULT '',
                posted_at         TEXT    NOT NULL,
                indexed_at        TEXT    DEFAULT (datetime('now','localtime')),
                UNIQUE(message_id, channel_id)
            )
        """)
        c.execute("CREATE INDEX IF NOT EXISTS idx_posts_platform ON posts(platform)")
        c.execute("CREATE INDEX IF NOT EXISTS idx_posts_indexed  ON posts(indexed_at)")

        # ── FTS5 للبحث السريع ────────────────────────────────────
        c.execute("""
            CREATE VIRTUAL TABLE IF NOT EXISTS posts_fts
            USING fts5(
                text_original,
                text_normalized,
                hashtags,
                platform,
                content='posts',
                content_rowid='id',
                tokenize='unicode61'
            )
        """)

        # ── Triggers مزامنة FTS ──────────────────────────────────
        c.executescript("""
            CREATE TRIGGER IF NOT EXISTS fts_ai
            AFTER INSERT ON posts BEGIN
                INSERT INTO posts_fts(rowid, text_original, text_normalized, hashtags, platform)
                VALUES (new.id, new.text_original, new.text_normalized, new.hashtags, new.platform);
            END;

            CREATE TRIGGER IF NOT EXISTS fts_ad
            AFTER DELETE ON posts BEGIN
                INSERT INTO posts_fts(posts_fts, rowid, text_original, text_normalized, hashtags, platform)
                VALUES ('delete', old.id, old.text_original, old.text_normalized, old.hashtags, old.platform);
            END;

            CREATE TRIGGER IF NOT EXISTS fts_au
            AFTER UPDATE ON posts BEGIN
                INSERT INTO posts_fts(posts_fts, rowid, text_original, text_normalized, hashtags, platform)
                VALUES ('delete', old.id, old.text_original, old.text_normalized, old.hashtags, old.platform);
                INSERT INTO posts_fts(rowid, text_original, text_normalized, hashtags, platform)
                VALUES (new.id, new.text_original, new.text_normalized, new.hashtags, new.platform);
            END;
        """)

        # ────────────────────────────────────────────────────────
        # جدول المشتركين في الإشعارات
        # ────────────────────────────────────────────────────────
        c.execute("""
            CREATE TABLE IF NOT EXISTS subscribers (
                user_id              INTEGER PRIMARY KEY,
                wants_free_games     INTEGER DEFAULT 0,
                wants_dlcs           INTEGER DEFAULT 0,
                wants_translations   INTEGER DEFAULT 0,
                updated_at           TEXT    DEFAULT (datetime('now','localtime'))
            )
        """)

        # ────────────────────────────────────────────────────────
        # جدول الألعاب المجانية المُرسلة (لكل مستخدم)
        # ────────────────────────────────────────────────────────
        c.execute("""
            CREATE TABLE IF NOT EXISTS free_games_sent (
                id        INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id   INTEGER NOT NULL,
                game_id   TEXT    NOT NULL,
                platform  TEXT    DEFAULT '',
                sent_at   TEXT    DEFAULT (datetime('now','localtime')),
                UNIQUE(user_id, game_id)
            )
        """)
        c.execute("CREATE INDEX IF NOT EXISTS idx_fgs_user   ON free_games_sent(user_id)")
        c.execute("CREATE INDEX IF NOT EXISTS idx_fgs_gameid ON free_games_sent(game_id)")

        conn.commit()
        logger.info("✅ قاعدة البيانات جاهزة بنجاح")

    except Exception as e:
        conn.rollback()
        logger.critical(f"❌ فشل تهيئة قاعدة البيانات: {e}", exc_info=True)
        raise
    finally:
        conn.close()


def rebuild_fts() -> bool:
    conn = get_conn()
    try:
        conn.execute("INSERT INTO posts_fts(posts_fts) VALUES('rebuild')")
        conn.commit()
        logger.info("✅ FTS أُعيد بناؤه")
        return True
    except Exception as e:
        logger.error(f"rebuild_fts: {e}", exc_info=True)
        return False
    finally:
        conn.close()