"""Database module for the World Cup 2026 Prediction Bot."""

import os
from pathlib import Path

import aiosqlite
from datetime import datetime
from typing import Optional

from config import settings


DB_PATH = settings.DATABASE_PATH
print("DB_PATH =", settings.DATABASE_PATH)


async def init_db() -> None:
    """Initialize database and create tables if not exist."""
    db_dir = os.path.dirname(DB_PATH)
    if db_dir:
        Path(db_dir).mkdir(parents=True, exist_ok=True)
    async with aiosqlite.connect(DB_PATH) as db:
        await db.execute("""
            CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                telegram_id INTEGER UNIQUE NOT NULL,
                full_name TEXT NOT NULL,
                phone TEXT NOT NULL,
                role TEXT NOT NULL,
                score INTEGER DEFAULT 0,
                referral_code TEXT UNIQUE NOT NULL,
                invited_by INTEGER,
                registered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)

        await db.execute("""
            CREATE TABLE IF NOT EXISTS matches (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                team1 TEXT NOT NULL,
                team2 TEXT NOT NULL,
                team1_flag TEXT,
                team2_flag TEXT,
                match_time TIMESTAMP NOT NULL,
                status TEXT NOT NULL DEFAULT 'upcoming',
                team1_score INTEGER,
                team2_score INTEGER,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)

        await db.execute("""
            CREATE TABLE IF NOT EXISTS predictions (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                match_id INTEGER NOT NULL,
                predict_team1 INTEGER Null,
                predict_team2 INTEGER Null,
                predict_result TEXT,
                earned_score INTEGER DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES users(id),
                FOREIGN KEY (match_id) REFERENCES matches(id),
                UNIQUE(user_id, match_id)
            )
        """)

        await db.execute("""
            CREATE TABLE IF NOT EXISTS referrals (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                inviter_id INTEGER NOT NULL,
                invited_user_id INTEGER NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (inviter_id) REFERENCES users(id),
                FOREIGN KEY (invited_user_id) REFERENCES users(id),
                UNIQUE(invited_user_id)
            )
        """)

        await db.commit()

        

# ==================== User Functions ====================

async def create_user(
    telegram_id: int,
    full_name: str,
    phone: str,
    role: str,
    referral_code: str,
    invited_by: Optional[int] = None
) -> int:
    """Create a new user and return the user ID."""
    async with aiosqlite.connect(DB_PATH) as db:
        cursor = await db.execute(
            """
            INSERT INTO users (telegram_id, full_name, phone, role, referral_code, invited_by)
            VALUES (?, ?, ?, ?, ?, ?)
            """,
            (telegram_id, full_name, phone, role, referral_code, invited_by)
        )
        await db.commit()
        return cursor.lastrowid


async def get_user_by_telegram_id(telegram_id: int) -> Optional[dict]:
    """Get user by Telegram ID."""
    async with aiosqlite.connect(DB_PATH) as db:
        db.row_factory = aiosqlite.Row
        cursor = await db.execute(
            "SELECT * FROM users WHERE telegram_id = ?",
            (telegram_id,)
        )
        row = await cursor.fetchone()
        return dict(row) if row else None


async def get_user_by_referral_code(referral_code: str) -> Optional[dict]:
    """Get user by referral code."""
    async with aiosqlite.connect(DB_PATH) as db:
        db.row_factory = aiosqlite.Row
        cursor = await db.execute(
            "SELECT * FROM users WHERE referral_code = ?",
            (referral_code,)
        )
        row = await cursor.fetchone()
        return dict(row) if row else None


async def update_user_score(user_id: int, score: int) -> None:
    """Update user score by adding the given value."""
    async with aiosqlite.connect(DB_PATH) as db:
        await db.execute(
            "UPDATE users SET score = score + ? WHERE id = ?",
            (score, user_id)
        )
        await db.commit()


async def get_all_users() -> list[dict]:
    """Get all users."""
    async with aiosqlite.connect(DB_PATH) as db:
        db.row_factory = aiosqlite.Row
        cursor = await db.execute("SELECT * FROM users")
        rows = await cursor.fetchall()
        return [dict(row) for row in rows]


async def get_user_count() -> int:
    """Get total number of users."""
    async with aiosqlite.connect(DB_PATH) as db:
        cursor = await db.execute("SELECT COUNT(*) FROM users")
        row = await cursor.fetchone()
        return row[0]


async def get_user_count_by_role(role: str) -> int:
    """Get number of users by role."""
    async with aiosqlite.connect(DB_PATH) as db:
        cursor = await db.execute(
            "SELECT COUNT(*) FROM users WHERE role = ?",
            (role,)
        )
        row = await cursor.fetchone()
        return row[0]


async def search_user(query: str) -> Optional[dict]:
    """Search user by telegram_id, phone, or name."""
    async with aiosqlite.connect(DB_PATH) as db:
        db.row_factory = aiosqlite.Row
        cursor = await db.execute(
            """
            SELECT * FROM users WHERE
                telegram_id = ? OR
                phone = ? OR
                full_name LIKE ?
            """,
            (query, query, f"%{query}%")
        )
        row = await cursor.fetchone()
        return dict(row) if row else None


# ==================== Match Functions ====================

async def create_match(
    team1: str,
    team2: str,
    team1_flag: str,
    team2_flag: str,
    match_time: datetime
) -> int:
    """Create a new match and return the match ID."""
    async with aiosqlite.connect(DB_PATH) as db:
        cursor = await db.execute(
            """
            INSERT INTO matches (team1, team2, team1_flag, team2_flag, match_time)
            VALUES (?, ?, ?, ?, ?)
            """,
            (team1, team2, team1_flag, team2_flag, match_time.isoformat())
        )
        await db.commit()
        return cursor.lastrowid


async def get_match(match_id: int) -> Optional[dict]:
    """Get match by ID."""
    async with aiosqlite.connect(DB_PATH) as db:
        db.row_factory = aiosqlite.Row
        cursor = await db.execute(
            "SELECT * FROM matches WHERE id = ?",
            (match_id,)
        )
        row = await cursor.fetchone()
        if row:
            match = dict(row)
            match["match_time"] = datetime.fromisoformat(match["match_time"])
            return match
        return None


async def get_upcoming_matches() -> list[dict]:
    """Get all upcoming matches."""
    async with aiosqlite.connect(DB_PATH) as db:
        db.row_factory = aiosqlite.Row
        cursor = await db.execute(
            "SELECT * FROM matches WHERE status = 'upcoming' ORDER BY match_time"
        )
        rows = await cursor.fetchall()
        matches = []
        for row in rows:
            match = dict(row)
            match["match_time"] = datetime.fromisoformat(match["match_time"])
            matches.append(match)
        return matches


async def get_all_matches() -> list[dict]:
    """Get all matches."""
    async with aiosqlite.connect(DB_PATH) as db:
        db.row_factory = aiosqlite.Row
        cursor = await db.execute(
            "SELECT * FROM matches ORDER BY match_time"
        )
        rows = await cursor.fetchall()
        matches = []
        for row in rows:
            match = dict(row)
            match["match_time"] = datetime.fromisoformat(match["match_time"])
            matches.append(match)
        return matches


async def update_match_result(
    match_id: int,
    team1_score: int,
    team2_score: int
) -> None:
    """Update match result and set status to finished."""
    async with aiosqlite.connect(DB_PATH) as db:
        await db.execute(
            """
            UPDATE matches
            SET team1_score = ?, team2_score = ?, status = 'finished'
            WHERE id = ?
            """,
            (team1_score, team2_score, match_id)
        )
        await db.commit()


async def delete_match(match_id: int) -> None:
    """Delete a match."""
    async with aiosqlite.connect(DB_PATH) as db:
        await db.execute("DELETE FROM matches WHERE id = ?", (match_id,))
        await db.execute("DELETE FROM predictions WHERE match_id = ?", (match_id,))
        await db.commit()


# ==================== Prediction Functions ====================

async def create_prediction(
    user_id: int,
    match_id: int,
    predict_team1: int = None,
    predict_team2: int = None,
    predict_result: str = None
) -> int:
    """Create a new prediction and return the prediction ID."""
    if not match_id:
        raise ValueError("match_id cannot be None")
    async with aiosqlite.connect(DB_PATH) as db:
        cursor = await db.execute(
            """
            INSERT INTO predictions (user_id, match_id, predict_team1, predict_team2, predict_result)
            VALUES (?, ?, ?, ?, ?)
            """,
            (user_id, match_id, predict_team1, predict_team2, predict_result)
        )
        await db.commit()
        return cursor.lastrowid


async def get_prediction(user_id: int, match_id: int) -> Optional[dict]:
    """Get prediction by user ID and match ID."""
    async with aiosqlite.connect(DB_PATH) as db:
        db.row_factory = aiosqlite.Row
        cursor = await db.execute(
            "SELECT * FROM predictions WHERE user_id = ? AND match_id = ?",
            (user_id, match_id)
        )
        row = await cursor.fetchone()
        return dict(row) if row else None


async def update_prediction(
    user_id: int,
    match_id: int,
    predict_team1: int = None,
    predict_team2: int = None,
    predict_result: str = None
) -> None:
    """Update an existing prediction."""
    async with aiosqlite.connect(DB_PATH) as db:
        if predict_result is not None:
            await db.execute(
                """
                UPDATE predictions
                SET predict_result = ?
                WHERE user_id = ? AND match_id = ?
                """,
                (predict_result, user_id, match_id)
            )
        else:
            await db.execute(
                """
                UPDATE predictions
                SET predict_team1 = ?, predict_team2 = ?
                WHERE user_id = ? AND match_id = ?
                """,
                (predict_team1, predict_team2, user_id, match_id)
            )
        await db.commit()


async def get_predictions_for_match(match_id: int) -> list[dict]:
    """Get all predictions for a specific match."""
    async with aiosqlite.connect(DB_PATH) as db:
        db.row_factory = aiosqlite.Row
        cursor = await db.execute(
            "SELECT * FROM predictions WHERE match_id = ?",
            (match_id,)
        )
        rows = await cursor.fetchall()
        return [dict(row) for row in rows]


async def update_prediction_score(
    prediction_id: int,
    earned_score: int
) -> None:
    """Update earned score for a prediction."""
    async with aiosqlite.connect(DB_PATH) as db:
        await db.execute(
            "UPDATE predictions SET earned_score = ? WHERE id = ?",
            (earned_score, prediction_id)
        )
        await db.commit()


async def get_prediction_count_for_user(user_id: int) -> int:
    """Get total predictions count for a user."""
    async with aiosqlite.connect(DB_PATH) as db:
        cursor = await db.execute(
            "SELECT COUNT(*) FROM predictions WHERE user_id = ?",
            (user_id,)
        )
        row = await cursor.fetchone()
        return row[0]


async def get_referral_count_for_user(user_id: int) -> int:
    """Get total successful referrals count for a user."""
    async with aiosqlite.connect(DB_PATH) as db:
        cursor = await db.execute(
            "SELECT COUNT(*) FROM referrals WHERE inviter_id = ?",
            (user_id,)
        )
        row = await cursor.fetchone()
        return row[0]


# ==================== Referral Functions ====================

async def create_referral(inviter_id: int, invited_user_id: int) -> int:
    """Create a new referral record and return the ID."""
    async with aiosqlite.connect(DB_PATH) as db:
        cursor = await db.execute(
            "INSERT INTO referrals (inviter_id, invited_user_id) VALUES (?, ?)",
            (inviter_id, invited_user_id)
        )
        await db.commit()
        return cursor.lastrowid


async def get_referral_by_invited(invited_user_id: int) -> Optional[dict]:
    """Get referral by invited user ID."""
    async with aiosqlite.connect(DB_PATH) as db:
        db.row_factory = aiosqlite.Row
        cursor = await db.execute(
            "SELECT * FROM referrals WHERE invited_user_id = ?",
            (invited_user_id,)
        )
        row = await cursor.fetchone()
        return dict(row) if row else None


# ==================== Ranking Functions ====================

async def get_top_users(limit: int = 20) -> list[dict]:
    """Get top users by score."""
    async with aiosqlite.connect(DB_PATH) as db:
        db.row_factory = aiosqlite.Row
        cursor = await db.execute(
            """
            SELECT full_name, score FROM users
            ORDER BY score DESC
            LIMIT ?
            """,
            (limit,)
        )
        rows = await cursor.fetchall()
        return [dict(row) for row in rows]


async def get_user_rank(user_id: int) -> int:
    """Get rank of a specific user."""
    async with aiosqlite.connect(DB_PATH) as db:
        cursor = await db.execute(
            """
            SELECT COUNT(*) + 1 FROM users WHERE score > (
                SELECT score FROM users WHERE id = ?
            )
            """,
            (user_id,)
        )
        row = await cursor.fetchone()
        return row[0]