import graphene
import logging
from db import get_dynamic_connection
from ..types import CreateTeamInput

logger = logging.getLogger("scheduler.team")

class CreateTeam(graphene.Mutation):
    class Arguments:
        input = CreateTeamInput(required=True)

    success = graphene.Boolean()
    message = graphene.String()
    team_id = graphene.Int()

    def mutate(self, info, input):
        db_name = info.context.get("db_name")
        if not db_name:
            return CreateTeam(success=False, message="Organisation database not configured.")

        try:
            with get_dynamic_connection(db_name) as conn:
                with conn.cursor() as cur:
                    # Verify division_id exists in level
                    cur.execute("SELECT id FROM level WHERE id = %s LIMIT 1", (input.division_id,))
                    if not cur.fetchone():
                        return CreateTeam(
                            success=False,
                            message=f"Division ID {input.division_id} not found."
                        )

                    # Verify home_field_id exists in resource
                    cur.execute("SELECT rsc_id FROM resource WHERE rsc_id = %s LIMIT 1", (input.home_field_id,))
                    if not cur.fetchone():
                        return CreateTeam(
                            success=False,
                            message=f"Field ID {input.home_field_id} not found."
                        )

                    # Verify season_id exists in season if provided
                    if input.season_id is not None:
                        cur.execute("SELECT season_id FROM season WHERE season_id = %s LIMIT 1", (input.season_id,))
                        if not cur.fetchone():
                            return CreateTeam(
                                success=False,
                                message=f"Season ID {input.season_id} not found."
                            )

                    # Check if team with same name already exists in this division (level)
                    cur.execute(
                        "SELECT id FROM team WHERE teamname = %s AND level = %s LIMIT 1",
                        (input.team_name, input.division_id)
                    )
                    if cur.fetchone():
                        return CreateTeam(
                            success=False,
                            message=f"Team '{input.team_name}' already exists in division ID {input.division_id}."
                        )

                    abbr_val = input.team_name[:14]
                    cur.execute(
                        """
                        INSERT INTO team (
                            teamname, abbr, level, homefield, affiliation,
                            year, League_ID, validated, paid_in_full
                        ) VALUES (%s, %s, %s, %s, %s, %s, 1, 1, 0)
                        """,
                        (
                            input.team_name,
                            abbr_val,
                            input.division_id,
                            input.home_field_id,
                            input.affiliation,
                            str(input.season_id) if input.season_id is not None else None
                        )
                    )
                    conn.commit()
                    team_id = cur.lastrowid

            return CreateTeam(
                success=True,
                message=f"Team '{input.team_name}' created successfully.",
                team_id=team_id
            )
        except Exception as e:
            logger.error(f"Unexpected error during team creation: {e}", exc_info=True)
            # Keep database errors internal, do not expose SQL details to users.
            return CreateTeam(
                success=False,
                message="An unexpected database error occurred during team creation."
            )

class TeamMutation:
    create_team = CreateTeam.Field()