from __future__ import annotations
import asyncpg
from config import settings


class DatabaseManager:
    def __init__(self):
        self.pool: asyncpg.Pool | None = None

    async def _get_pool(self):
        if not self.pool:
            self.pool = await asyncpg.create_pool(settings.DATABASE_URL)
        return self.pool

    # ---------------------------
    # Low-level helpers
    # ---------------------------
    async def execute(self, query: str, *args):
        pool = await self._get_pool()
        async with pool.acquire() as conn:
            return await conn.execute(query, *args)

    async def fetch_all(self, query: str, *args):
        pool = await self._get_pool()
        async with pool.acquire() as conn:
            return await conn.fetch(query, *args)

    async def fetch_one(self, query: str, *args):
        pool = await self._get_pool()
        async with pool.acquire() as conn:
            return await conn.fetchrow(query, *args)

    # ---------------------------
    # Matches
    # ---------------------------
    async def upsert_match(self, m: dict):
        query = """
        INSERT INTO matches (
            id, source, competition_url, match_url,
            kickoff_ts, status, home_team, away_team,
            home_score, away_score, minute
        )
        VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)
        ON CONFLICT (id) DO UPDATE SET
            status=EXCLUDED.status,
            home_score=EXCLUDED.home_score,
            away_score=EXCLUDED.away_score,
            minute=EXCLUDED.minute
        """
        await self.execute(
            query,
            m["id"],
            m.get("source", "flashscore"),
            m.get("competition_url"),
            m.get("match_url"),
            m.get("kickoff_ts"),
            m.get("status"),
            m.get("home_team"),
            m.get("away_team"),
            m.get("home_score"),
            m.get("away_score"),
            m.get("minute"),
        )

    async def list_matches(self, status: str, limit: int = 100):
        rows = await self.fetch_all(
            """
            SELECT * FROM matches
            WHERE status=$1
            ORDER BY kickoff_ts DESC
            LIMIT $2
            """,
            status,
            limit,
        )
        return rows

    async def last_matches_for_team(self, team: str, limit: int = 15):
        return await self.fetch_all(
            """
            SELECT *
            FROM matches
            WHERE status='finished'
              AND (home_team=$1 OR away_team=$1)
            ORDER BY kickoff_ts DESC
            LIMIT $2
            """,
            team,
            limit,
        )

    # ---------------------------
    # Team stats
    # ---------------------------
    async def get_team_stats(self, team: str):
        return await self.fetch_one(
            "SELECT * FROM team_stats WHERE team_id=$1",
            team,
        )

    # ---------------------------
    # Predictions
    # ---------------------------
    async def upsert_prediction(self, p: dict):
        query = """
        INSERT INTO predictions (
            match_id, model,
            p_home, p_draw, p_away,
            eg_home, eg_away,
            confidence, created_at
        )
        VALUES ($1,$2,$3,$4,$5,$6,$7,$8,now())
        ON CONFLICT (match_id, model) DO UPDATE SET
            p_home=EXCLUDED.p_home,
            p_draw=EXCLUDED.p_draw,
            p_away=EXCLUDED.p_away,
            eg_home=EXCLUDED.eg_home,
            eg_away=EXCLUDED.eg_away,
            confidence=EXCLUDED.confidence,
            created_at=now()
        """
        await self.execute(
            query,
            p["match_id"],
            p["model"],
            p["p_home"],
            p["p_draw"],
            p["p_away"],
            p["eg_home"],
            p["eg_away"],
            p["confidence"],
        )

    async def get_prediction_for_match(self, match_id: str, model: str):
        return await self.fetch_one(
            """
            SELECT * FROM predictions
            WHERE match_id=$1 AND model=$2
            """,
            match_id,
            model,
        )

    # ---------------------------
    # Metrics
    # ---------------------------
    async def save_metrics(self, model: str, n: int, acc: float, brier: float, logloss: float):
        await self.execute(
            """
            INSERT INTO metric_aggregates(model, n_matches, accuracy, brier, logloss, created_at)
            VALUES ($1,$2,$3,$4,$5,now())
            """,
            model,
            n,
            acc,
            brier,
            logloss,
        )