import graphene import io import logging import pandas as pd from db import get_dynamic_connection from ..types import ( Upload, Scheduler, DeleteResult, ImportLeagueSetupResult, CreateSchedulerInput, UpdateSchedulerInput ) from ..helpers import ( _resolve_game, _find_or_create_team, _find_or_create_level, _find_or_create_resource, _find_or_create_timeslot ) logger = logging.getLogger("scheduler.import") # 1. CreateScheduler class CreateScheduler(graphene.Mutation): class Arguments: input = CreateSchedulerInput(required=True) # Returns the created Scheduler id = graphene.Int() date = graphene.String() time = graphene.String() home = graphene.String() away = graphene.String() location = graphene.String() division = graphene.String() created_at = graphene.String() updated_at = graphene.String() def mutate(self, info, input): db_name = info.context["db_name"] with get_dynamic_connection(db_name) as conn: with conn.cursor() as cur: home_team_id = _find_or_create_team(cur, conn, input.home) away_team_id = _find_or_create_team(cur, conn, input.away) level_id = _find_or_create_level(cur, conn, input.division) resource_id = _find_or_create_resource(cur, conn, input.location) timeslot_id = _find_or_create_timeslot(cur, conn, input.time) # Insert game cur.execute( "INSERT INTO game (home_team, away_team, idlevel, status, game_type) " "VALUES (%s, %s, %s, %s, %s)", (home_team_id, away_team_id, level_id, 0, "season"), ) conn.commit() game_id = cur.lastrowid # Insert schedule cur.execute( "INSERT INTO schedule (relationshipid, rscs_date, rsc_id, ts_id, sched_status) " "VALUES (%s, %s, %s, %s, %s)", (game_id, input.date, resource_id, timeslot_id, 0), ) conn.commit() game_obj = _resolve_game(conn, game_id) return CreateScheduler( id=game_obj.id, date=game_obj.date, time=game_obj.time, home=game_obj.home, away=game_obj.away, location=game_obj.location, division=game_obj.division, created_at=game_obj.created_at, updated_at=game_obj.updated_at ) # 2. UpdateScheduler class UpdateScheduler(graphene.Mutation): class Arguments: input = UpdateSchedulerInput(required=True) # Returns the updated Scheduler id = graphene.Int() date = graphene.String() time = graphene.String() home = graphene.String() away = graphene.String() location = graphene.String() division = graphene.String() created_at = graphene.String() updated_at = graphene.String() def mutate(self, info, input): db_name = info.context["db_name"] with get_dynamic_connection(db_name) as conn: with conn.cursor() as cur: game_id = input.id # Verify game exists cur.execute("SELECT * FROM game WHERE id = %s", (game_id,)) game = cur.fetchone() if not game: raise Exception(f"Scheduler entry not found with ID {game_id}") # Build game update dict game_update = {} if input.home is not None: game_update["home_team"] = _find_or_create_team(cur, conn, input.home) if input.away is not None: game_update["away_team"] = _find_or_create_team(cur, conn, input.away) if input.division is not None: game_update["idlevel"] = _find_or_create_level(cur, conn, input.division) if game_update: set_clause = ", ".join(f"{k} = %s" for k in game_update) cur.execute( f"UPDATE game SET {set_clause} WHERE id = %s", (*game_update.values(), game_id), ) conn.commit() # Build schedule update dict cur.execute( "SELECT * FROM schedule WHERE relationshipid = %s LIMIT 1", (game_id,), ) schedule = cur.fetchone() schedule_update = {} if input.date is not None: schedule_update["rscs_date"] = input.date if input.location is not None: schedule_update["rsc_id"] = _find_or_create_resource( cur, conn, input.location ) if input.time is not None: schedule_update["ts_id"] = _find_or_create_timeslot( cur, conn, input.time ) if schedule_update: if schedule: set_clause = ", ".join(f"{k} = %s" for k in schedule_update) cur.execute( f"UPDATE schedule SET {set_clause} WHERE relationshipid = %s", (*schedule_update.values(), game_id), ) else: schedule_update["relationshipid"] = game_id schedule_update["sched_status"] = 0 cols = ", ".join(schedule_update.keys()) placeholders = ", ".join(["%s"] * len(schedule_update)) cur.execute( f"INSERT INTO schedule ({cols}) VALUES ({placeholders})", tuple(schedule_update.values()), ) conn.commit() game_obj = _resolve_game(conn, game_id) return UpdateScheduler( id=game_obj.id, date=game_obj.date, time=game_obj.time, home=game_obj.home, away=game_obj.away, location=game_obj.location, division=game_obj.division, created_at=game_obj.created_at, updated_at=game_obj.updated_at ) # 3. DeleteScheduler class DeleteScheduler(graphene.Mutation): class Arguments: id = graphene.Int(required=True) success = graphene.Boolean() message = graphene.String() def mutate(self, info, id): db_name = info.context["db_name"] with get_dynamic_connection(db_name) as conn: with conn.cursor() as cur: cur.execute( "DELETE FROM schedule WHERE relationshipid = %s", (id,) ) cur.execute("DELETE FROM game WHERE id = %s", (id,)) conn.commit() return DeleteScheduler(success=True, message=f"Scheduler entry {id} deleted.") # 4. ImportLeagueSetup class ImportLeagueSetup(graphene.Mutation): class Arguments: file = Upload(required=True) success = graphene.Boolean() message = graphene.String() async def mutate(self, info, file): # Check size: 10MB limit file_bytes = await file.read() if len(file_bytes) > 10 * 1024 * 1024: return ImportLeagueSetup(success=False, message="File size exceeds the 10MB limit.") if not file.filename.endswith(".xlsx"): return ImportLeagueSetup(success=False, message="Only .xlsx Excel files are supported.") db_name = info.context.get("db_name") if not db_name: return ImportLeagueSetup(success=False, message="Organisation database not configured.") # Re-usable loader function inside the resolver def load_sheet(excel_file, sheet_name, required_cols): try: df = pd.read_excel(excel_file, sheet_name=sheet_name, header=3) except Exception as e: raise ValueError(f"Failed to read sheet '{sheet_name}': {str(e)}") df = df.dropna(how="all") # strip trailing "*" markers and whitespace df.columns = [str(c).rstrip("*").strip() for c in df.columns] missing_required = [] for col in required_cols: if col not in df.columns: raise ValueError(f"[{sheet_name}] missing expected column: {col}") blank_rows = df[df[col].isna() | (df[col].astype(str).str.strip() == "")] if not blank_rows.empty: # 1-based index (header is row 4, first data is row 5) row_numbers = [idx + 5 for idx in blank_rows.index.tolist()] missing_required.append((col, row_numbers)) if missing_required: details = ", ".join(f"'{col}' blank at row(s) {rows}" for col, rows in missing_required) raise ValueError(f"[{sheet_name}] required fields blank: {details}") return df.reset_index(drop=True) try: excel_file = io.BytesIO(file_bytes) # ---- 1. Season (expect exactly one row) ---- season_df = load_sheet(excel_file, "Season", ["Season Name", "Start Date", "End Date", "Status", "Season Year"]) if len(season_df) != 1: raise ValueError(f"Expected exactly 1 season row, found {len(season_df)}") season = season_df.iloc[0] def opt_date(val): if pd.isna(val) or str(val).strip() == "": return None try: return pd.to_datetime(val).date() except Exception: return None with get_dynamic_connection(db_name) as conn: with conn.cursor() as cur: # Map status hide_val = 0 if str(season["Status"]).strip().lower() in ["inactive", "hidden", "hide"]: hide_val = 1 # Insert Season cur.execute( """ INSERT INTO season ( season_desc, season_start, season_end, year_id, reg_start_date, reg_end_date, early_bird_1, late_fee_date, season_order, season_type, hide, League_ID ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, 1.00, 1, %s, 1) """, ( season["Season Name"], pd.to_datetime(season["Start Date"]).date(), pd.to_datetime(season["End Date"]).date(), int(season["Season Year"]), opt_date(season.get("Registration Start")), opt_date(season.get("Registration End")), opt_date(season.get("Early Bird Date")), opt_date(season.get("Late Fee Date")), hide_val ) ) season_id = cur.lastrowid # ---- 2. Divisions ---- excel_file.seek(0) div_df = load_sheet(excel_file, "Divisions", ["Division Name", "Gender"]) division_id_by_name = {} for _, row in div_df.iterrows(): gender_str = str(row["Gender"]).strip().lower() gender_val = 0 if "girl" in gender_str or "female" in gender_str or "women" in gender_str: gender_val = 2 elif "boy" in gender_str or "male" in gender_str or "men" in gender_str: gender_val = 1 def parse_float(val): if pd.isna(val) or str(val).strip() == "": return 0.00 try: return float(val) except Exception: return 0.00 max_teams = row.get("Max Teams in Division") max_teams_val = int(max_teams) if (pd.notna(max_teams) and str(max_teams).strip() != "") else 0 cur.execute( """ INSERT INTO level ( description, gender, start, end, max_teams, League_ID, sp_id, div_code ) VALUES (%s, %s, %s, %s, %s, 1, 1, "") """, ( row["Division Name"], gender_val, parse_float(row.get("Start (age or grade)")), parse_float(row.get("End (age or grade)")), max_teams_val ) ) division_id_by_name[row["Division Name"]] = cur.lastrowid # ---- 3. Fields ---- excel_file.seek(0) field_df = load_sheet(excel_file, "Fields", ["Field Name"]) field_id_by_name = {} for _, row in field_df.iterrows(): address = row.get("Address") site_id = 0 if address and str(address).strip() != "": addr_str = str(address).strip() cur.execute("SELECT site_id FROM site WHERE site_name = %s LIMIT 1", (addr_str,)) site_row = cur.fetchone() if site_row: site_id = site_row["site_id"] else: cur.execute( "INSERT INTO site (site_name, site_address1, leagueID) VALUES (%s, %s, 1)", (addr_str, addr_str) ) site_id = cur.lastrowid # Look up resource by name (to avoid duplicates if already present) cur.execute("SELECT rsc_id FROM resource WHERE rsc_name = %s LIMIT 1", (row["Field Name"],)) rsc_row = cur.fetchone() if rsc_row: field_id = rsc_row["rsc_id"] else: cur.execute( """ INSERT INTO resource ( rsc_name, site_id, active, rsc_capacity, rsct_id, prim_tsg_id ) VALUES (%s, %s, 1, 1, 0, 0) """, (row["Field Name"], site_id) ) field_id = cur.lastrowid field_id_by_name[row["Field Name"]] = field_id # ---- 4. Teams (validate reference checks, then insert) ---- excel_file.seek(0) team_df = load_sheet(excel_file, "Teams", ["Team Name", "Division", "Home Field"]) errors = [] for i, row in team_df.iterrows(): if row["Division"] not in division_id_by_name: errors.append(f"Row {i + 5}: unknown Division '{row['Division']}'") if row["Home Field"] not in field_id_by_name: errors.append(f"Row {i + 5}: unknown Home Field '{row['Home Field']}'") if errors: raise ValueError("Teams tab has broken references:\n" + "\n".join(errors)) for _, row in team_df.iterrows(): aff_str = str(row.get("Affiliation/Club")).strip() if (pd.notna(row.get("Affiliation/Club")) and str(row.get("Affiliation/Club")).strip() != "") else None cur.execute( """ INSERT INTO team ( teamname, abbr, level, homefield, affiliation, League_ID, validated, paid_in_full ) VALUES (%s, %s, %s, %s, %s, 1, 1, 0) """, ( row["Team Name"], row["Team Name"][:14], division_id_by_name[row["Division"]], field_id_by_name[row["Home Field"]], aff_str ) ) conn.commit() return ImportLeagueSetup( success=True, message=f"Imported: 1 season, {len(div_df)} divisions, {len(field_df)} fields, {len(team_df)} teams." ) except ValueError as ve: logger.warning(f"Validation error during league setup import: {ve}") return ImportLeagueSetup(success=False, message=str(ve)) except Exception as e: logger.error(f"Unexpected error during league setup import: {e}", exc_info=True) return ImportLeagueSetup( success=False, message="An unexpected database error occurred during import. Please check your data format and try again." ) # Group mutations into class class SchedulerMutation: create_scheduler = CreateScheduler.Field() update_scheduler = UpdateScheduler.Field() delete_scheduler = DeleteScheduler.Field() import_league_setup = ImportLeagueSetup.Field()