from __future__ import annotations

import asyncpg
from typing import Any, Optional
from config import settings


class DatabaseManager:
    def __init__(self):
        self.pool: Optional[asyncpg.Pool] = None

    async def connect(self):
        if self.pool is None:
            self.pool = await asyncpg.create_pool(
                dsn=settings.DATABASE_URL,
                min_size=1,
                max_size=10,
            )

    async def close(self):
        if self.pool:
            await self.pool.close()

    async def execute(self, query: str, *args):
        await self.connect()
        async with self.pool.acquire() as conn:
            await conn.execute(query, *args)

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

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

    async def init_db(self):
        # MATCHES
        await self.execute("""
        CREATE TABLE IF NOT EXISTS matches (
            match_id TEXT PRIMARY KEY,
            home_team TEXT,
            away_team TEXT,
            home_goals INTEGER,
            away_goals INTEGER,
            status TEXT,
            start_time TIMESTAMP,
            competition TEXT,
            url TEXT
        );
        """)

        # PREDICTIONS
        await self.execute("""
        CREATE TABLE IF NOT EXISTS predictions (
            match_id TEXT PRIMARY KEY,
            p1 REAL,
            px REAL,
            p2 REAL,
            lambda_home REAL,
            lambda_away REAL,
            confidence REAL,
            created_at TIMESTAMP DEFAULT now()
        );
        """)

        # TEAM STATS (🔥 ВАЖНО)
        await self.execute("""
        CREATE TABLE IF NOT EXISTS team_stats (
            team_id TEXT PRIMARY KEY,
            team_name TEXT,
            attack REAL,
            defense REAL,
            updated_at TIMESTAMP DEFAULT now()
        );
        """)

    async def get_team_stats(self, team_id: str):
        row = await self.fetch_one(
            "SELECT attack, defense FROM team_stats WHERE team_id=$1",
            team_id
        )
        return row