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

logger = logging.getLogger(__name__)


def get_subscriber(user_id: int) -> dict | None:
    conn = get_conn()
    try:
        c = conn.cursor()
        c.execute("SELECT * FROM subscribers WHERE user_id=?", (user_id,))
        row = c.fetchone()
        return dict(row) if row else None
    except Exception as e:
        logger.error(f"get_subscriber({user_id}): {e}", exc_info=True)
        return None
    finally:
        conn.close()


def upsert_subscriber(
    user_id: int,
    wants_free_games: bool,
    wants_dlcs: bool,
    wants_translations: bool,
) -> bool:
    conn = get_conn()
    try:
        conn.execute("""
            INSERT INTO subscribers
                (user_id, wants_free_games, wants_dlcs, wants_translations, updated_at)
            VALUES (?, ?, ?, ?, datetime('now','localtime'))
            ON CONFLICT(user_id) DO UPDATE SET
                wants_free_games   = excluded.wants_free_games,
                wants_dlcs         = excluded.wants_dlcs,
                wants_translations = excluded.wants_translations,
                updated_at         = datetime('now','localtime')
        """, (user_id, int(wants_free_games), int(wants_dlcs), int(wants_translations)))
        conn.commit()
        return True
    except Exception as e:
        logger.error(f"upsert_subscriber({user_id}): {e}", exc_info=True)
        return False
    finally:
        conn.close()


def remove_subscriber(user_id: int) -> bool:
    conn = get_conn()
    try:
        c = conn.cursor()
        c.execute("DELETE FROM subscribers WHERE user_id=?", (user_id,))
        conn.commit()
        return c.rowcount > 0
    except Exception as e:
        logger.error(f"remove_subscriber({user_id}): {e}", exc_info=True)
        return False
    finally:
        conn.close()


def get_free_game_subscribers() -> list[dict]:
    """كل المشتركين الذين يريدون إشعارات الألعاب المجانية."""
    conn = get_conn()
    try:
        c = conn.cursor()
        c.execute("""
            SELECT * FROM subscribers
            WHERE wants_free_games=1 OR wants_dlcs=1
        """)
        return [dict(r) for r in c.fetchall()]
    except Exception:
        return []
    finally:
        conn.close()


def get_translation_subscribers() -> list[dict]:
    """كل المشتركين الذين يريدون إشعارات التعريبات الجديدة."""
    conn = get_conn()
    try:
        c = conn.cursor()
        c.execute("SELECT * FROM subscribers WHERE wants_translations=1")
        return [dict(r) for r in c.fetchall()]
    except Exception:
        return []
    finally:
        conn.close()


def get_total_subscribers() -> int:
    conn = get_conn()
    try:
        return conn.execute("""
            SELECT COUNT(*) FROM subscribers
            WHERE wants_free_games=1 OR wants_dlcs=1 OR wants_translations=1
        """).fetchone()[0]
    except Exception:
        return 0
    finally:
        conn.close()