from db import get_dynamic_connection
from .types import Scheduler

def _resolve_game(conn, game_id: int) -> Scheduler:
    """
    Build a full Scheduler object for a game row, joining schedule,
    timeslot, team, resource, site, and level tables.
    Mirrors PHP SchedulerType field resolvers.
    """
    with conn.cursor() as cur:
        # Game row
        cur.execute("SELECT * FROM game WHERE id = %s", (game_id,))
        game = cur.fetchone()
        if not game:
            raise Exception("Scheduler entry not found")

        # Schedule row
        cur.execute(
            "SELECT * FROM schedule WHERE relationshipid = %s LIMIT 1",
            (game_id,),
        )
        schedule = cur.fetchone()

        # Date
        date_val = schedule.get("rscs_date") if schedule else None
        if date_val is not None:
            date_val = str(date_val)

        # Time (via timeslot)
        time_val = None
        if schedule and schedule.get("ts_id"):
            cur.execute(
                "SELECT ts_starttime FROM timeslot WHERE ts_id = %s",
                (schedule["ts_id"],),
            )
            ts = cur.fetchone()
            if ts and ts.get("ts_starttime") is not None:
                time_val = str(ts["ts_starttime"])

        # Home team name
        home_name = None
        if game.get("home_team"):
            cur.execute(
                "SELECT teamname FROM team WHERE id = %s",
                (game["home_team"],),
            )
            t = cur.fetchone()
            if t:
                home_name = t.get("teamname")

        # Away team name
        away_name = None
        if game.get("away_team"):
            cur.execute(
                "SELECT teamname FROM team WHERE id = %s",
                (game["away_team"],),
            )
            t = cur.fetchone()
            if t:
                away_name = t.get("teamname")

        # Location (resource + site)
        location_val = ""
        if schedule and schedule.get("rsc_id"):
            cur.execute(
                "SELECT * FROM resource WHERE rsc_id = %s",
                (schedule["rsc_id"],),
            )
            resource = cur.fetchone()
            rsc_name = resource.get("rsc_name", "") if resource else ""
            site_name = ""
            if resource and resource.get("site_id"):
                cur.execute(
                    "SELECT site_name FROM site WHERE site_id = %s",
                    (resource["site_id"],),
                )
                site = cur.fetchone()
                site_name = site.get("site_name", "") if site else ""
            if rsc_name and site_name:
                location_val = f"{rsc_name} @ {site_name}"
            else:
                location_val = rsc_name or site_name or ""

        # Division (level)
        division_val = ""
        if game.get("idlevel"):
            cur.execute(
                "SELECT description FROM level WHERE id = %s",
                (game["idlevel"],),
            )
            lvl = cur.fetchone()
            if lvl:
                division_val = lvl.get("description", "")

        return Scheduler(
            id=game["id"],
            date=date_val,
            time=time_val,
            home=home_name,
            away=away_name,
            location=location_val,
            division=division_val,
            created_at=str(game["created_at"]) if game.get("created_at") else None,
            updated_at=str(game["updated_at"]) if game.get("updated_at") else None,
        )


def _find_or_create_team(cur, conn, team_name: str) -> int:
    """Find a team by name or create it.  Returns the team id."""
    cur.execute("SELECT id FROM team WHERE teamname = %s", (team_name,))
    row = cur.fetchone()
    if row:
        return row["id"]
    cur.execute(
        "INSERT INTO team (teamname, abbr) VALUES (%s, %s)",
        (team_name, team_name[:3]),
    )
    conn.commit()
    return cur.lastrowid


def _find_or_create_level(cur, conn, description: str) -> int:
    """Find a level/division by description or create it."""
    cur.execute("SELECT id FROM level WHERE description = %s", (description,))
    row = cur.fetchone()
    if row:
        return row["id"]
    cur.execute("INSERT INTO level (description) VALUES (%s)", (description,))
    conn.commit()
    return cur.lastrowid


def _find_or_create_resource(cur, conn, rsc_name: str) -> int:
    """Find a resource/location by name or create it."""
    cur.execute("SELECT rsc_id FROM resource WHERE rsc_name = %s", (rsc_name,))
    row = cur.fetchone()
    if row:
        return row["rsc_id"]
    cur.execute("INSERT INTO resource (rsc_name) VALUES (%s)", (rsc_name,))
    conn.commit()
    return cur.lastrowid


def _find_or_create_timeslot(cur, conn, start_time: str) -> int:
    """Find a timeslot by start time or create it."""
    cur.execute("SELECT ts_id FROM timeslot WHERE ts_starttime = %s", (start_time,))
    row = cur.fetchone()
    if row:
        return row["ts_id"]
    cur.execute(
        "INSERT INTO timeslot (ts_starttime, ts_endtime) VALUES (%s, %s)",
        (start_time, start_time),
    )
    conn.commit()
    return cur.lastrowid