import os
import pandas as pd
from dotenv import load_dotenv
from sqlalchemy import create_engine

load_dotenv('../../.env.local')

class DataLoader:
    def __init__(self):
        db_url = os.getenv("SUPABASE_DB_URL")
        if not db_url:
            raise ValueError("SUPABASE_DB_URL not found in .env.local")
        # SQLAlchemy requires postgresql:// instead of postgres:// in some versions, 
        # but supabase usually provides postgresql://
        self.engine = create_engine(db_url)

    def get_rankings(self):
        query = """
            SELECT team_id, rating, rank 
            FROM worldcup_team_rankings
        """
        df = pd.read_sql(query, self.engine)
        return df.set_index('team_id')

    def get_team_form(self):
        query = """
            SELECT team_id, match_date, opponent_elo, goals_for, goals_against, is_home 
            FROM worldcup_team_form
            ORDER BY team_id, match_date DESC
        """
        return pd.read_sql(query, self.engine)

    def get_teams(self):
        query = """
            SELECT id as team_id, is_host 
            FROM worldcup_teams
        """
        df = pd.read_sql(query, self.engine)
        return df.set_index('team_id')

    def get_matches(self):
        query = """
            SELECT id, stage, round, home_team_id, away_team_id, 
                   home_score_90, away_score_90, status
            FROM worldcup_matches
            WHERE status != 'cancelled'
        """
        return pd.read_sql(query, self.engine)

    def get_manual_features(self):
        query = """
            SELECT match_id, odds_1x2_home, odds_1x2_draw, odds_1x2_away,
                   injury_impact_home, injury_impact_away,
                   lineup_strength_home, lineup_strength_away
            FROM worldcup_manual_features
        """
        df = pd.read_sql(query, self.engine)
        return df.set_index('match_id')

    def get_latest_odds_summary(self):
        query = """
            WITH ranked AS (
                SELECT
                    match_id,
                    bookmaker_key,
                    bookmaker_title,
                    market_key,
                    market_title,
                    home_odds,
                    draw_odds,
                    away_odds,
                    last_update,
                    snapshot_time,
                    ROW_NUMBER() OVER (
                        PARTITION BY match_id, bookmaker_key, market_key
                        ORDER BY COALESCE(last_update, snapshot_time) DESC, snapshot_time DESC
                    ) AS rn
                FROM worldcup_market_odds_snapshots
                WHERE market_key = 'h2h'
            )
            SELECT *
            FROM ranked
            WHERE rn = 1
            ORDER BY match_id, bookmaker_title
        """
        try:
            return pd.read_sql(query, self.engine)
        except Exception:
            return pd.DataFrame()

    def get_latest_weather_summary(self):
        query = """
            WITH ranked AS (
                SELECT
                    match_id,
                    forecast_time,
                    snapshot_time,
                    temperature_c,
                    apparent_temperature_c,
                    humidity_pct,
                    precipitation_probability_pct,
                    precipitation_mm,
                    wind_speed_kmh,
                    wind_gusts_kmh,
                    weather_code,
                    ROW_NUMBER() OVER (
                        PARTITION BY match_id
                        ORDER BY snapshot_time DESC
                    ) AS rn
                FROM worldcup_weather_snapshots
            )
            SELECT *
            FROM ranked
            WHERE rn = 1
        """
        try:
            df = pd.read_sql(query, self.engine)
            if df.empty:
                return df
            return df.set_index('match_id')
        except Exception:
            return pd.DataFrame()

    def close(self):
        self.engine.dispose()

if __name__ == "__main__":
    loader = DataLoader()
    print("Rankings loaded:", len(loader.get_rankings()))
    print("Matches loaded:", len(loader.get_matches()))
    loader.close()