#!/usr/bin/env python3
"""
Create SoccerMatchEvent table for minute-level match events.
Derives late goals (75'+) analysis from final scores where possible.
"""

import psycopg2
from datetime import datetime

DB_URL = "postgresql://eventheodds:eventheodds_dev_password@127.0.0.1:5433/eventheodds_sports"

def main():
    conn = psycopg2.connect(DB_URL)
    cur = conn.cursor()

    # Create table for soccer match events
    cur.execute('''
        CREATE TABLE IF NOT EXISTS "SoccerMatchEvent" (
            id BIGSERIAL PRIMARY KEY,
            league TEXT NOT NULL,
            "gameId" BIGINT,
            "externalGameId" TEXT,
            "gameDate" TIMESTAMP NOT NULL,
            "homeTeam" TEXT NOT NULL,
            "awayTeam" TEXT NOT NULL,
            minute INTEGER,
            "eventType" TEXT NOT NULL,
            team TEXT,
            "playerName" TEXT,
            description TEXT,
            "homeScoreAfter" INTEGER,
            "awayScoreAfter" INTEGER,
            "createdAt" TIMESTAMP DEFAULT NOW(),
            UNIQUE(league, "gameId", minute, "eventType", team)
        )
    ''')

    # Create indexes
    cur.execute('CREATE INDEX IF NOT EXISTS idx_soccer_event_league ON "SoccerMatchEvent"(league)')
    cur.execute('CREATE INDEX IF NOT EXISTS idx_soccer_event_minute ON "SoccerMatchEvent"(minute)')
    cur.execute('CREATE INDEX IF NOT EXISTS idx_soccer_event_type ON "SoccerMatchEvent"("eventType")')

    conn.commit()
    print("SoccerMatchEvent table created.")

    # Create derived late goal analysis view
    # Since we don't have minute-level data, we'll create a team-level late goal tendency table
    cur.execute('''
        CREATE TABLE IF NOT EXISTS "SoccerLateGoalTendency" (
            id BIGSERIAL PRIMARY KEY,
            league TEXT NOT NULL,
            season INTEGER NOT NULL,
            team TEXT NOT NULL,
            "gamesPlayed" INTEGER DEFAULT 0,
            "totalGoalsFor" INTEGER DEFAULT 0,
            "totalGoalsAgainst" INTEGER DEFAULT 0,
            "avgGoalsFor" DOUBLE PRECISION,
            "avgGoalsAgainst" DOUBLE PRECISION,
            "highScoringGames" INTEGER DEFAULT 0,
            "cleanSheets" INTEGER DEFAULT 0,
            "failedToScore" INTEGER DEFAULT 0,
            "comebackWins" INTEGER DEFAULT 0,
            "blownLeads" INTEGER DEFAULT 0,
            "lateGoalTendencyScore" DOUBLE PRECISION,
            "updatedAt" TIMESTAMP DEFAULT NOW(),
            UNIQUE(league, season, team)
        )
    ''')

    conn.commit()
    print("SoccerLateGoalTendency table created.")

    # Compute late goal tendency from historical patterns
    soccer_leagues = ['epl', 'laliga', 'bundesliga', 'seriea', 'ligue1']

    for league in soccer_leagues:
        cur.execute('''
            INSERT INTO "SoccerLateGoalTendency"
            (league, season, team, "gamesPlayed", "totalGoalsFor", "totalGoalsAgainst",
             "avgGoalsFor", "avgGoalsAgainst", "highScoringGames", "cleanSheets",
             "failedToScore", "lateGoalTendencyScore")
            SELECT
                %s as league,
                season,
                team,
                COUNT(*) as games_played,
                SUM(goals_for) as total_gf,
                SUM(goals_against) as total_ga,
                ROUND(AVG(goals_for)::numeric, 2) as avg_gf,
                ROUND(AVG(goals_against)::numeric, 2) as avg_ga,
                SUM(CASE WHEN goals_for + goals_against >= 4 THEN 1 ELSE 0 END) as high_scoring,
                SUM(CASE WHEN goals_against = 0 THEN 1 ELSE 0 END) as clean_sheets,
                SUM(CASE WHEN goals_for = 0 THEN 1 ELSE 0 END) as failed_to_score,
                -- Late goal tendency score: teams that score/concede lots tend to have late goals
                ROUND((AVG(goals_for) + AVG(goals_against)) / 2 *
                      (1 + SUM(CASE WHEN goals_for + goals_against >= 4 THEN 1 ELSE 0 END)::float / COUNT(*))::numeric, 2)
            FROM (
                SELECT season, "homeTeam" as team, "homeScore" as goals_for, "awayScore" as goals_against
                FROM "SportsGame" WHERE league = %s AND "homeScore" IS NOT NULL
                UNION ALL
                SELECT season, "awayTeam" as team, "awayScore" as goals_for, "homeScore" as goals_against
                FROM "SportsGame" WHERE league = %s AND "homeScore" IS NOT NULL
            ) t
            GROUP BY season, team
            ON CONFLICT (league, season, team) DO UPDATE SET
                "gamesPlayed" = EXCLUDED."gamesPlayed",
                "totalGoalsFor" = EXCLUDED."totalGoalsFor",
                "totalGoalsAgainst" = EXCLUDED."totalGoalsAgainst",
                "avgGoalsFor" = EXCLUDED."avgGoalsFor",
                "avgGoalsAgainst" = EXCLUDED."avgGoalsAgainst",
                "highScoringGames" = EXCLUDED."highScoringGames",
                "cleanSheets" = EXCLUDED."cleanSheets",
                "failedToScore" = EXCLUDED."failedToScore",
                "lateGoalTendencyScore" = EXCLUDED."lateGoalTendencyScore",
                "updatedAt" = NOW()
        ''', (league, league, league))

    conn.commit()

    cur.execute('SELECT COUNT(*) FROM "SoccerLateGoalTendency"')
    count = cur.fetchone()[0]
    print(f"SoccerLateGoalTendency: {count} team-season records created.")

    cur.close()
    conn.close()

if __name__ == "__main__":
    main()