#!/usr/bin/env python3
"""
Create GameBroadcast table for TV network data.
Infers national TV games from schedule patterns (primetime, day of week).
"""

import psycopg2
from datetime import datetime

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

# Known national TV patterns
NATIONAL_NETWORKS = {
    'nba': ['ESPN', 'TNT', 'ABC', 'NBATV'],
    'nfl': ['ESPN', 'NBC', 'CBS', 'FOX', 'NFLN', 'PRIME', 'ABC'],
    'nhl': ['ESPN', 'TNT', 'ABC', 'NHLN'],
    'mlb': ['ESPN', 'FOX', 'TBS', 'MLBN', 'PEACOCK', 'APPLE']
}

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

    # Create GameBroadcast table
    cur.execute('''
        CREATE TABLE IF NOT EXISTS "GameBroadcast" (
            id BIGSERIAL PRIMARY KEY,
            league TEXT NOT NULL,
            "gameId" BIGINT,
            "gameDate" TIMESTAMP NOT NULL,
            "homeTeam" TEXT NOT NULL,
            "awayTeam" TEXT NOT NULL,
            network TEXT,
            "isNationalTV" BOOLEAN DEFAULT FALSE,
            "isPrimetime" BOOLEAN DEFAULT FALSE,
            "dayOfWeek" INTEGER,
            "startHour" INTEGER,
            "viewershipEstimate" TEXT,
            "createdAt" TIMESTAMP DEFAULT NOW(),
            UNIQUE(league, "gameId")
        )
    ''')

    cur.execute('CREATE INDEX IF NOT EXISTS idx_broadcast_league ON "GameBroadcast"(league)')
    cur.execute('CREATE INDEX IF NOT EXISTS idx_broadcast_national ON "GameBroadcast"("isNationalTV")')
    cur.execute('CREATE INDEX IF NOT EXISTS idx_broadcast_network ON "GameBroadcast"(network)')

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

    # Infer national TV games from schedule patterns
    # NFL: Sunday Night (NBC), Monday Night (ESPN/ABC), Thursday Night (PRIME/NFLN)
    # NBA: Tuesday/Thursday TNT, Saturday ABC, Sunday ESPN
    # NHL: Wednesday ESPN, Saturday ABC

    print("Inferring NFL national TV games...")
    cur.execute('''
        INSERT INTO "GameBroadcast"
        (league, "gameId", "gameDate", "homeTeam", "awayTeam", network, "isNationalTV", "isPrimetime", "dayOfWeek", "startHour")
        SELECT
            'nfl',
            id,
            "gameDate",
            "homeTeam",
            "awayTeam",
            CASE
                WHEN EXTRACT(DOW FROM "gameDate") = 0 AND EXTRACT(HOUR FROM "gameDate") >= 20 THEN 'NBC'
                WHEN EXTRACT(DOW FROM "gameDate") = 1 THEN 'ESPN'
                WHEN EXTRACT(DOW FROM "gameDate") = 4 THEN 'PRIME'
                WHEN EXTRACT(DOW FROM "gameDate") = 0 AND EXTRACT(HOUR FROM "gameDate") >= 16 THEN 'CBS/FOX'
                ELSE 'REGIONAL'
            END as network,
            CASE
                WHEN EXTRACT(DOW FROM "gameDate") IN (0, 1, 4) AND EXTRACT(HOUR FROM "gameDate") >= 19 THEN TRUE
                ELSE FALSE
            END as is_national,
            EXTRACT(HOUR FROM "gameDate") >= 20 as is_primetime,
            EXTRACT(DOW FROM "gameDate")::int,
            EXTRACT(HOUR FROM "gameDate")::int
        FROM "SportsGame"
        WHERE league = 'nfl' AND "homeScore" IS NOT NULL
        ON CONFLICT (league, "gameId") DO UPDATE SET
            network = EXCLUDED.network,
            "isNationalTV" = EXCLUDED."isNationalTV",
            "isPrimetime" = EXCLUDED."isPrimetime",
            "dayOfWeek" = EXCLUDED."dayOfWeek",
            "startHour" = EXCLUDED."startHour"
    ''')

    print("Inferring NBA national TV games...")
    cur.execute('''
        INSERT INTO "GameBroadcast"
        (league, "gameId", "gameDate", "homeTeam", "awayTeam", network, "isNationalTV", "isPrimetime", "dayOfWeek", "startHour")
        SELECT
            'nba',
            id,
            "gameDate",
            "homeTeam",
            "awayTeam",
            CASE
                WHEN EXTRACT(DOW FROM "gameDate") IN (2, 4) AND EXTRACT(HOUR FROM "gameDate") >= 19 THEN 'TNT'
                WHEN EXTRACT(DOW FROM "gameDate") = 6 AND EXTRACT(HOUR FROM "gameDate") >= 20 THEN 'ABC'
                WHEN EXTRACT(DOW FROM "gameDate") = 0 THEN 'ESPN'
                ELSE 'REGIONAL'
            END as network,
            CASE
                WHEN EXTRACT(DOW FROM "gameDate") IN (2, 4, 6, 0) AND EXTRACT(HOUR FROM "gameDate") >= 19 THEN TRUE
                ELSE FALSE
            END as is_national,
            EXTRACT(HOUR FROM "gameDate") >= 20 as is_primetime,
            EXTRACT(DOW FROM "gameDate")::int,
            EXTRACT(HOUR FROM "gameDate")::int
        FROM "SportsGame"
        WHERE league = 'nba' AND "homeScore" IS NOT NULL
        ON CONFLICT (league, "gameId") DO UPDATE SET
            network = EXCLUDED.network,
            "isNationalTV" = EXCLUDED."isNationalTV",
            "isPrimetime" = EXCLUDED."isPrimetime",
            "dayOfWeek" = EXCLUDED."dayOfWeek",
            "startHour" = EXCLUDED."startHour"
    ''')

    print("Inferring NHL national TV games...")
    cur.execute('''
        INSERT INTO "GameBroadcast"
        (league, "gameId", "gameDate", "homeTeam", "awayTeam", network, "isNationalTV", "isPrimetime", "dayOfWeek", "startHour")
        SELECT
            'nhl',
            id,
            "gameDate",
            "homeTeam",
            "awayTeam",
            CASE
                WHEN EXTRACT(DOW FROM "gameDate") = 3 AND EXTRACT(HOUR FROM "gameDate") >= 19 THEN 'ESPN'
                WHEN EXTRACT(DOW FROM "gameDate") = 6 THEN 'ABC'
                ELSE 'REGIONAL'
            END as network,
            CASE
                WHEN EXTRACT(DOW FROM "gameDate") IN (3, 6) AND EXTRACT(HOUR FROM "gameDate") >= 18 THEN TRUE
                ELSE FALSE
            END as is_national,
            EXTRACT(HOUR FROM "gameDate") >= 20 as is_primetime,
            EXTRACT(DOW FROM "gameDate")::int,
            EXTRACT(HOUR FROM "gameDate")::int
        FROM "SportsGame"
        WHERE league = 'nhl' AND "homeScore" IS NOT NULL
        ON CONFLICT (league, "gameId") DO UPDATE SET
            network = EXCLUDED.network,
            "isNationalTV" = EXCLUDED."isNationalTV",
            "isPrimetime" = EXCLUDED."isPrimetime",
            "dayOfWeek" = EXCLUDED."dayOfWeek",
            "startHour" = EXCLUDED."startHour"
    ''')

    print("Inferring MLB national TV games...")
    cur.execute('''
        INSERT INTO "GameBroadcast"
        (league, "gameId", "gameDate", "homeTeam", "awayTeam", network, "isNationalTV", "isPrimetime", "dayOfWeek", "startHour")
        SELECT
            'mlb',
            id,
            "gameDate",
            "homeTeam",
            "awayTeam",
            CASE
                WHEN EXTRACT(DOW FROM "gameDate") = 0 AND EXTRACT(HOUR FROM "gameDate") >= 19 THEN 'ESPN'
                WHEN EXTRACT(DOW FROM "gameDate") = 6 AND EXTRACT(HOUR FROM "gameDate") >= 19 THEN 'FOX'
                ELSE 'REGIONAL'
            END as network,
            CASE
                WHEN EXTRACT(DOW FROM "gameDate") IN (0, 6) AND EXTRACT(HOUR FROM "gameDate") >= 19 THEN TRUE
                ELSE FALSE
            END as is_national,
            EXTRACT(HOUR FROM "gameDate") >= 20 as is_primetime,
            EXTRACT(DOW FROM "gameDate")::int,
            EXTRACT(HOUR FROM "gameDate")::int
        FROM "SportsGame"
        WHERE league = 'mlb' AND "homeScore" IS NOT NULL
        ON CONFLICT (league, "gameId") DO UPDATE SET
            network = EXCLUDED.network,
            "isNationalTV" = EXCLUDED."isNationalTV",
            "isPrimetime" = EXCLUDED."isPrimetime",
            "dayOfWeek" = EXCLUDED."dayOfWeek",
            "startHour" = EXCLUDED."startHour"
    ''')

    conn.commit()

    cur.execute('SELECT COUNT(*) FROM "GameBroadcast"')
    total = cur.fetchone()[0]

    cur.execute('SELECT COUNT(*) FROM "GameBroadcast" WHERE "isNationalTV" = TRUE')
    national = cur.fetchone()[0]

    print(f"GameBroadcast: {total} games tagged, {national} national TV games identified.")

    # Summary by league and network
    cur.execute('''
        SELECT league, network, COUNT(*) as games, SUM(CASE WHEN "isNationalTV" THEN 1 ELSE 0 END) as national
        FROM "GameBroadcast"
        GROUP BY league, network
        ORDER BY league, games DESC
    ''')
    print("\nBroadcast summary:")
    for row in cur.fetchall():
        print(f"  {row[0]} {row[1]}: {row[2]} games ({row[3]} national)")

    cur.close()
    conn.close()

if __name__ == "__main__":
    main()