# api_server.py
from flask import Flask, request, jsonify
import requests
import os
import json
import config
from urllib.parse import quote
from datetime import datetime

app = Flask(__name__)

# Tier ranking system (higher number = better tier)
TIER_RANK = {
    "HT1": 9,
    "HT2": 8,
    "HT3": 7,
    "HT4": 6,
    "LT1": 5,
    "LT2": 4,
    "LT3": 3,
    "LT4": 2,
    "-": 1,
}

def get_tier_rank(tier):
    """Get numerical rank for a tier (higher = better)"""
    return TIER_RANK.get(tier, 0)

# Backup directory
BACKUP_DIR = os.path.normpath(os.path.join(os.path.dirname(__file__), "misc"))

# Global log file
log_file = None

def init_logging():
    global log_file
    os.makedirs(BACKUP_DIR, exist_ok=True)
    log_path = os.path.join(BACKUP_DIR, "api_debug.log")
    log_file = open(log_path, "a", buffering=1)

# Get service role key from config or environment variable
SUPABASE_SERVICE_ROLE_KEY = os.getenv("SUPABASE_SERVICE_ROLE_KEY", config.SUPABASE_SERVICE_ROLE_KEY)
SUPABASE_URL = config.VITE_SUPABASE_URL  # https://liorcawnmqrbbjtwyeyh.supabase.co

# Headers for Supabase API requests
def get_headers():
    return {
        "apikey": SUPABASE_SERVICE_ROLE_KEY,
        "Authorization": f"Bearer {SUPABASE_SERVICE_ROLE_KEY}",
        "Content-Type": "application/json",
    }

@app.route("/upload", methods=["POST"])
def upload_player_data():
    """API endpoint to upload/update player tier data"""
    try:
        if not SUPABASE_SERVICE_ROLE_KEY or "PASTE_YOUR_SERVICE_ROLE_KEY" in SUPABASE_SERVICE_ROLE_KEY:
            return jsonify({
                "success": False, 
                "error": "SUPABASE_SERVICE_ROLE_KEY not configured. Add your service role key to config.py"
            }), 500
        
        data = request.json
        print(f"[API] upload_player_data received: {data}")
        
        # Validate required fields
        if not data.get("username") or not data.get("region") or not data.get("gamemode") or not data.get("tier"):
            print(f"[API] Missing required fields")
            return jsonify({
                "success": False, 
                "error": "username, region, gamemode, and tier are required"
            }), 400
        
        original_username = data["username"]  # Keep original case
        username_lower = original_username.lower()  # For comparison
        region = data["region"]
        gamemode = data["gamemode"]
        tier = data["tier"]
        
        # Validate gamemode
        valid_gamemodes = ["vanilla", "smp", "pot", "sword", "uhc", "nethpot", "mace", "axe"]
        if gamemode not in valid_gamemodes:
            print(f"[API] [ERROR] Invalid gamemode: {gamemode}")
            return jsonify({
                "success": False, 
                "error": f"Invalid gamemode '{gamemode}'. Valid: {', '.join(valid_gamemodes)}"
            }), 400
        
        # Step 1: Fetch existing player by checking all users and matching case-insensitively
        url = f"{SUPABASE_URL}/rest/v1/players"
        headers = get_headers()
        
        # Fetch all players to find case-insensitive match
        fetch_url = f"{url}?select=*"
        print(f"[API] Fetching all players for case-insensitive match")
        fetch_response = requests.get(fetch_url, headers=headers)
        
        all_players = fetch_response.json() if fetch_response.status_code == 200 else []
        existing_data = [p for p in all_players if p.get("username", "").lower() == username_lower]
        
        print(f"[API] Found {len(existing_data)} matching player(s) (case-insensitive)")
        
        # Step 2: Build update data with tier + region
        tier_column = f"tier_{gamemode}"
        
        if existing_data and len(existing_data) > 0:
            # Player exists - use original stored username, update region and tier
            existing_player = existing_data[0]
            player_id = existing_player["id"]
            stored_username = existing_player.get("username")  # Use the original stored case
            old_region = existing_player.get("region")
            
            update_payload = {
                "region": region,
                tier_column: tier
            }
            
            print(f"[API] Updating existing player ID: {player_id}")
            print(f"[API] Stored username: {stored_username}")
            print(f"[API] Region: {old_region} -> {region}")
            print(f"[API] Tier column: {tier_column} = {tier}")
            print(f"[API] Payload: {update_payload}")
            
            # Use PATCH to update region and tier column
            patch_url = f"{url}?id=eq.{player_id}"
            print(f"[API] PATCH URL: {patch_url}")
            response = requests.patch(patch_url, json=update_payload, headers=headers)
            
            print(f"[API] PATCH response status: {response.status_code}")
            print(f"[API] PATCH response body: {response.text[:500]}")
            
            if response.status_code in [200, 204]:
                print(f"[API] [OK] Successfully updated player {player_id}")
                return jsonify({
                    "success": True,
                    "message": f"Player {stored_username} updated (Region: {region}, {gamemode} tier: {tier})"
                }), 200
            else:
                print(f"[API] [ERROR] Patch error - Status {response.status_code}: {response.text}")
                return jsonify({
                    "success": False,
                    "error": f"Supabase error: {response.status_code}",
                    "details": response.text[:500] if response.text else "No response body"
                }), response.status_code
        else:
            # New player - create entry with original case username
            create_payload = {
                "username": original_username,  # Store original case
                "region": region,
                tier_column: tier,
            }
            
            print(f"[API] Creating new player: {original_username}")
            print(f"[API] Tier column: {tier_column} = {tier}")
            print(f"[API] Payload: {create_payload}")
            
            # Use POST to create new player
            response = requests.post(url, json=create_payload, headers=headers)
            
            print(f"[API] POST response status: {response.status_code}")
            print(f"[API] Response body: {response.text[:200]}")
            
            if response.status_code in [200, 201]:
                print(f"[API] [OK] Successfully created new player {original_username}")
                return jsonify({
                    "success": True,
                    "message": f"Player {original_username} created (Region: {region}, {gamemode} tier: {tier})"
                }), 200
            else:
                print(f"[API] [ERROR] POST error - Status {response.status_code}: {response.text}")
                return jsonify({
                    "success": False,
                    "error": f"Supabase error: {response.status_code}",
                    "details": response.text[:500] if response.text else "No response body"
                }), response.status_code
    
    except Exception as e:
        print(f"[API] [ERROR] upload_player_data error: {str(e)}")
        import traceback
        traceback.print_exc()
        return jsonify({"success": False, "error": str(e), "type": type(e).__name__}), 500

@app.route("/health", methods=["GET"])
def health():
    """Health check endpoint"""
    key_status = "[OK] Loaded" if SUPABASE_SERVICE_ROLE_KEY and len(SUPABASE_SERVICE_ROLE_KEY) > 50 else "[ERROR] Too short or missing"
    return jsonify({
        "status": "ok",
        "supabase_url": SUPABASE_URL,
        "service_role_key_status": key_status,
        "key_length": len(SUPABASE_SERVICE_ROLE_KEY) if SUPABASE_SERVICE_ROLE_KEY else 0
    }), 200

@app.route("/delete_gamemode", methods=["POST"])
def delete_gamemode():
    """Delete a specific gamemode tier for a player"""
    try:
        data = request.json
        print(f"[API] delete_gamemode received: {data}")
        
        if not data.get("username") or not data.get("region") or not data.get("gamemode"):
            print(f"[API] Missing required fields")
            return jsonify({
                "success": False,
                "error": "username, region, and gamemode are required"
            }), 400
        
        username_lower = data["username"].lower()  # For comparison
        gamemode = data["gamemode"]
        tier_column = f"tier_{gamemode}"
        
        url = f"{SUPABASE_URL}/rest/v1/players"
        headers = get_headers()
        
        # Fetch all players and find case-insensitive match
        fetch_url = f"{url}?select=id,username"
        fetch_response = requests.get(fetch_url, headers=headers)
        all_players = fetch_response.json() if fetch_response.status_code == 200 else []
        
        matching_players = [p for p in all_players if p.get("username", "").lower() == username_lower]
        
        if not matching_players:
            print(f"[API] Player not found")
            return jsonify({
                "success": False,
                "error": "Player not found"
            }), 404
        
        player_id = matching_players[0]["id"]
        stored_username = matching_players[0]["username"]
        print(f"[API] Found player ID: {player_id}, username: {stored_username}")
        
        # Reset the gamemode tier to '-' (enum default)
        update_payload = {tier_column: "-"}
        
        # Use PATCH to update the tier column
        patch_url = f"{url}?id=eq.{player_id}"
        response = requests.patch(patch_url, json=update_payload, headers=headers)
        print(f"[API] Patch response status: {response.status_code}")
        
        if response.status_code in [200, 204]:
            return jsonify({
                "success": True,
                "message": f"Gamemode {gamemode} tier removed for {stored_username}"
            }), 200
        else:
            print(f"[API] Patch error response: {response.text}")
            return jsonify({
                "success": False,
                "error": f"Supabase error: {response.status_code}",
                "details": response.text[:500] if response.text else "No response body"
            }), response.status_code
    
    except Exception as e:
        print(f"[API] delete_gamemode error: {str(e)}")
        return jsonify({"success": False, "error": str(e)}), 500

@app.route("/delete_player", methods=["POST"])
def delete_player():
    """Delete all data for a player"""
    try:
        data = request.json
        print(f"[API] delete_player received: {data}")
        
        if not data.get("username") or not data.get("region"):
            print(f"[API] Missing required fields")
            return jsonify({
                "success": False,
                "error": "username and region are required"
            }), 400
        
        username_lower = data["username"].lower()  # For comparison
        
        url = f"{SUPABASE_URL}/rest/v1/players"
        headers = get_headers()
        
        # Fetch all players and find case-insensitive match
        fetch_url = f"{url}?select=id,username"
        fetch_response = requests.get(fetch_url, headers=headers)
        all_players = fetch_response.json() if fetch_response.status_code == 200 else []
        
        matching_players = [p for p in all_players if p.get("username", "").lower() == username_lower]
        
        if not matching_players:
            return jsonify({
                "success": False,
                "error": "Player not found"
            }), 404
        
        player_id = matching_players[0]["id"]
        stored_username = matching_players[0]["username"]
        
        # Delete the player record by ID
        delete_url = f"{url}?id=eq.{player_id}"
        response = requests.delete(delete_url, headers=headers)
        print(f"[API] Delete response status: {response.status_code}")
        
        if response.status_code in [200, 204]:
            return jsonify({
                "success": True,
                "message": f"Player {stored_username} deleted"
            }), 200
        else:
            return jsonify({
                "success": False,
                "error": f"Supabase error: {response.status_code}"
            }), response.status_code
    
    except Exception as e:
        print(f"[API] delete_player error: {str(e)}")
        return jsonify({"success": False, "error": str(e)}), 500

@app.route("/clear_all", methods=["POST"])
def clear_all():
    """Clear all player data from database and backup to JSON"""
    try:
        print(f"[API] clear_all called")
        
        url = f"{SUPABASE_URL}/rest/v1/players"
        headers = get_headers()
        
        # Step 1: Fetch all data before deletion (for backup)
        fetch_url = f"{url}?select=*"
        fetch_response = requests.get(fetch_url, headers=headers)
        all_data = fetch_response.json() if fetch_response.status_code == 200 else []
        
        print(f"[API] Fetched {len(all_data)} records for backup")
        
        # Step 2: Save to backup JSON file with timestamp
        if all_data:
            os.makedirs(BACKUP_DIR, exist_ok=True)
            timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
            backup_filename = f"backup_{timestamp}.json"
            backup_filepath = os.path.join(BACKUP_DIR, backup_filename)
            with open(backup_filepath, "w") as f:
                json.dump(all_data, f, indent=2)
            print(f"[API] Backup saved to {backup_filepath}")
        
        # Step 3: Delete all records
        headers["Prefer"] = "return=minimal"  # Don't return deleted rows
        delete_url = f"{url}?id=not.is.null"
        response = requests.delete(delete_url, headers=headers)
        print(f"[API] Delete response status: {response.status_code}")
        
        if response.status_code in [200, 204]:
            backup_file = f"backup_{timestamp}.json" if all_data else "No data to backup"
            return jsonify({
                "success": True,
                "message": "All player data cleared successfully",
                "backup_file": backup_file,
                "records_deleted": len(all_data)
            }), 200
        else:
            print(f"[API] Delete error response: {response.text}")
            return jsonify({
                "success": False,
                "error": f"Supabase error: {response.status_code}",
                "details": response.text[:500] if response.text else "No response body"
            }), response.status_code
    
    except Exception as e:
        print(f"[API] clear_all error: {str(e)}")
        return jsonify({"success": False, "error": str(e)}), 500

@app.route("/restore", methods=["POST"])
def restore_data():
    """Restore player data with one of three merge strategies"""
    try:
        data = request.json
        print(f"[API] restore called with strategy: {data.get('strategy')}")
        
        backup_file = data.get("backup_file")
        strategy = data.get("strategy")  # merge, overwrite, or clear_new
        
        if not backup_file or not strategy:
            return jsonify({
                "success": False,
                "error": "backup_file and strategy are required"
            }), 400
        
        if strategy not in ["merge", "overwrite", "clear_new"]:
            return jsonify({
                "success": False,
                "error": "strategy must be 'merge', 'overwrite', or 'clear_new'"
            }), 400
        
        # Load backup data
        try:
            backup_filepath = os.path.join(BACKUP_DIR, backup_file)
            with open(backup_filepath, "r") as f:
                backup_data = json.load(f)
            print(f"[API] Loaded {len(backup_data)} records from backup")
        except FileNotFoundError:
            return jsonify({
                "success": False,
                "error": f"Backup file not found: {backup_file}"
            }), 404
        
        url = f"{SUPABASE_URL}/rest/v1/players"
        headers = get_headers()
        
        # Fetch current data in database
        fetch_url = f"{url}?select=*"
        fetch_response = requests.get(fetch_url, headers=headers)
        current_data = fetch_response.json() if fetch_response.status_code == 200 else []
        
        print(f"[API] Current database has {len(current_data)} records")
        
        if strategy == "merge":
            # Merge: Keep higher tiers from both old and new
            merged_data = merge_strategy(backup_data, current_data)
        elif strategy == "overwrite":
            # Overwrite: Keep old as priority, fill gaps from new
            merged_data = overwrite_strategy(backup_data, current_data)
        elif strategy == "clear_new":
            # Clear new: Keep only backup data, mark as restored
            merged_data = backup_data
        
        # Clear database and re-upload merged data
        delete_url = f"{url}?id=not.is.null"
        delete_headers = get_headers()
        delete_headers["Prefer"] = "return=minimal"
        requests.delete(delete_url, headers=delete_headers)
        
        # Upload merged data
        uploaded_count = 0
        for record in merged_data:
            # Remove id field if it exists (let database generate new ones)
            record_copy = {k: v for k, v in record.items() if k != "id"}
            response = requests.post(url, json=record_copy, headers=headers)
            if response.status_code in [200, 201]:
                uploaded_count += 1
        
        print(f"[API] Restored {uploaded_count} records using '{strategy}' strategy")
        
        return jsonify({
            "success": True,
            "message": f"Data restored successfully using '{strategy}' strategy",
            "records_restored": uploaded_count
        }), 200
    
    except Exception as e:
        print(f"[API] restore error: {str(e)}")
        return jsonify({"success": False, "error": str(e)}), 500

def merge_strategy(old_data, new_data):
    """Merge strategy: Keep higher tier for each gamemode"""
    result = {}
    
    # Add all players from both datasets
    for player in old_data + new_data:
        username_lower = player.get("username", "").lower()
        
        if username_lower not in result:
            result[username_lower] = {
                "username": player.get("username"),
                "region": player.get("region")
            }
        
        # Merge gamemode tiers - keep higher tier
        for key, value in player.items():
            if key.startswith("tier_"):
                existing_tier = result[username_lower].get(key, "-")
                new_tier = value
                
                if get_tier_rank(new_tier) > get_tier_rank(existing_tier):
                    result[username_lower][key] = new_tier
                else:
                    result[username_lower][key] = existing_tier
    
    return list(result.values())

def overwrite_strategy(old_data, new_data):
    """Overwrite strategy: Old data as priority, fill gaps from new"""
    result = {}
    
    # First add all old data
    for player in old_data:
        username_lower = player.get("username", "").lower()
        result[username_lower] = {k: v for k, v in player.items() if k != "id"}
    
    # Then fill gaps from new data
    for player in new_data:
        username_lower = player.get("username", "").lower()
        
        if username_lower not in result:
            result[username_lower] = {k: v for k, v in player.items() if k != "id"}
        else:
            # Only add fields that don't exist in old data
            for key, value in player.items():
                if key != "id" and not result[username_lower].get(key):
                    result[username_lower][key] = value
    
    return list(result.values())

@app.route("/merge", methods=["POST"])
def merge_players():
    """Merge two players, keeping higher tiers for each gamemode"""
    try:
        data = request.json
        print(f"[API] merge_players received: {data}")
        
        if not data.get("username1") or not data.get("username2"):
            return jsonify({
                "success": False,
                "error": "username1 and username2 are required"
            }), 400
        
        username1_lower = data["username1"].lower()  # For comparison
        username2_lower = data["username2"].lower()  # For comparison
        
        if username1_lower == username2_lower:
            return jsonify({
                "success": False,
                "error": "Cannot merge a player with themselves"
            }), 400
        
        url = f"{SUPABASE_URL}/rest/v1/players"
        headers = get_headers()
        
        # Fetch all players to find case-insensitive matches
        fetch_url = f"{url}?select=*"
        print(f"[API] Fetching all players for merge")
        fetch_response = requests.get(fetch_url, headers=headers)
        all_players = fetch_response.json() if fetch_response.status_code == 200 else []
        
        # Find matching players (case-insensitive)
        player1_data = [p for p in all_players if p.get("username", "").lower() == username1_lower]
        player2_data = [p for p in all_players if p.get("username", "").lower() == username2_lower]
        
        if not player1_data or not player2_data:
            return jsonify({
                "success": False,
                "error": f"One or both players not found"
            }), 404
        
        player1 = player1_data[0]
        player2 = player2_data[0]
        
        print(f"[API] Player 1: {player1}")
        print(f"[API] Player 2: {player2}")
        
        # Get all gamemode columns (tier_*, excluding tier)
        gamemode_columns = [col for col in player1.keys() if col.startswith("tier_")]
        
        # Build merged update for player1 with higher tiers from both players
        merged_update = {}
        for gamemode_col in gamemode_columns:
            tier1 = player1.get(gamemode_col, "-")
            tier2 = player2.get(gamemode_col, "-")
            
            rank1 = get_tier_rank(tier1)
            rank2 = get_tier_rank(tier2)
            
            # Keep the higher tier
            if rank2 > rank1:
                merged_update[gamemode_col] = tier2
            else:
                merged_update[gamemode_col] = tier1
        
        print(f"[API] Merged update: {merged_update}")
        
        # Update player1 with merged data
        patch_url = f"{url}?id=eq.{player1['id']}"
        response = requests.patch(patch_url, json=merged_update, headers=headers)
        
        print(f"[API] PATCH response status: {response.status_code}")
        
        if response.status_code not in [200, 204]:
            return jsonify({
                "success": False,
                "error": f"Failed to update player 1: {response.status_code}"
            }), 500
        
        # Delete player2
        delete_url = f"{url}?id=eq.{player2['id']}"
        delete_response = requests.delete(delete_url, headers=headers)
        
        print(f"[API] DELETE response status: {delete_response.status_code}")
        
        if delete_response.status_code not in [200, 204]:
            return jsonify({
                "success": False,
                "error": f"Failed to delete player 2: {delete_response.status_code}"
            }), 500
        
        return jsonify({
            "success": True,
            "message": f"Successfully merged {data['username2']} into {data['username1']}",
            "merged_tiers": merged_update
        }), 200
        
    except Exception as e:
        print(f"[API] merge_players error: {str(e)}")
        return jsonify({"success": False, "error": str(e)}), 500

@app.route("/retire", methods=["POST"])
def retire_gamemode():
    """Retire a player in a specific gamemode"""
    try:
        data = request.json
        print(f"[API] retire received: {data}")
        
        if not data.get("username") or not data.get("gamemode"):
            return jsonify({
                "success": False,
                "error": "username and gamemode are required"
            }), 400
        
        username_lower = data["username"].lower()
        gamemode = data["gamemode"].lower()
        retired_column = f"retired_{gamemode}"
        
        url = f"{SUPABASE_URL}/rest/v1/players"
        headers = get_headers()
        
        # Fetch all players and find case-insensitive match
        fetch_url = f"{url}?select=id,username"
        fetch_response = requests.get(fetch_url, headers=headers)
        all_players = fetch_response.json() if fetch_response.status_code == 200 else []
        
        matching_players = [p for p in all_players if p.get("username", "").lower() == username_lower]
        
        if not matching_players:
            return jsonify({
                "success": False,
                "error": "Player not found"
            }), 404
        
        player_id = matching_players[0]["id"]
        stored_username = matching_players[0]["username"]
        
        # Set the retired_{gamemode} column to 1
        update_payload = {retired_column: 1}
        
        patch_url = f"{url}?id=eq.{player_id}"
        response = requests.patch(patch_url, json=update_payload, headers=headers)
        print(f"[API] Retire patch response status: {response.status_code}")
        
        if response.status_code in [200, 204]:
            return jsonify({
                "success": True,
                "message": f"Player {stored_username} retired from {gamemode} gamemode"
            }), 200
        else:
            print(f"[API] Patch error response: {response.text}")
            return jsonify({
                "success": False,
                "error": f"Supabase error: {response.status_code}",
                "details": response.text[:500] if response.text else "No response body"
            }), response.status_code
    
    except Exception as e:
        print(f"[API] retire error: {str(e)}")
        return jsonify({"success": False, "error": str(e)}), 500

@app.route("/unretire", methods=["POST"])
def unretire_gamemode():
    """Unretire a player in a specific gamemode"""
    try:
        data = request.json
        print(f"[API] unretire received: {data}")
        
        if not data.get("username") or not data.get("gamemode"):
            return jsonify({
                "success": False,
                "error": "username and gamemode are required"
            }), 400
        
        username_lower = data["username"].lower()
        gamemode = data["gamemode"].lower()
        retired_column = f"retired_{gamemode}"
        
        url = f"{SUPABASE_URL}/rest/v1/players"
        headers = get_headers()
        
        # Fetch all players and find case-insensitive match
        fetch_url = f"{url}?select=id,username"
        fetch_response = requests.get(fetch_url, headers=headers)
        all_players = fetch_response.json() if fetch_response.status_code == 200 else []
        
        matching_players = [p for p in all_players if p.get("username", "").lower() == username_lower]
        
        if not matching_players:
            return jsonify({
                "success": False,
                "error": "Player not found"
            }), 404
        
        player_id = matching_players[0]["id"]
        stored_username = matching_players[0]["username"]
        
        # Set the retired_{gamemode} column to 0
        update_payload = {retired_column: 0}
        
        patch_url = f"{url}?id=eq.{player_id}"
        response = requests.patch(patch_url, json=update_payload, headers=headers)
        print(f"[API] Unretire patch response status: {response.status_code}")
        
        if response.status_code in [200, 204]:
            return jsonify({
                "success": True,
                "message": f"Player {stored_username} unretired from {gamemode} gamemode"
            }), 200
        else:
            print(f"[API] Patch error response: {response.text}")
            return jsonify({
                "success": False,
                "error": f"Supabase error: {response.status_code}",
                "details": response.text[:500] if response.text else "No response body"
            }), response.status_code
    
    except Exception as e:
        print(f"[API] unretire error: {str(e)}")
        return jsonify({"success": False, "error": str(e)}), 500

@app.route("/clear_gamemode", methods=["POST"])
def clear_gamemode():
    """Clear player data for specific gamemode(s)"""
    try:
        if not SUPABASE_SERVICE_ROLE_KEY or "PASTE_YOUR_SERVICE_ROLE_KEY" in SUPABASE_SERVICE_ROLE_KEY:
            return jsonify({
                "success": False, 
                "error": "SUPABASE_SERVICE_ROLE_KEY not configured"
            }), 500
        
        data = request.json
        gamemodes = data.get("gamemodes", [])
        backup_prefix = data.get("backup_prefix", "")
        
        if not gamemodes:
            return jsonify({
                "success": False,
                "error": "No gamemodes specified"
            }), 400
        
        print(f"[API] clear_gamemode: Clearing gamemodes: {gamemodes}")
        
        # Create backup with timestamp
        import os
        import json
        from datetime import datetime
        
        backup_dir = os.path.normpath(os.path.join(os.path.dirname(__file__), "misc", "backups", "auto_clear"))
        os.makedirs(backup_dir, exist_ok=True)
        
        backup_file = os.path.join(backup_dir, f"{backup_prefix}_supabase.json")
        
        # Backup all current players
        url = f"{SUPABASE_URL}/rest/v1/players"
        headers = get_headers()
        
        fetch_url = f"{url}?select=*"
        fetch_response = requests.get(fetch_url, headers=headers)
        all_players = fetch_response.json() if fetch_response.status_code == 200 else []
        
        # Save backup
        with open(backup_file, "w", encoding="utf-8") as f:
            json.dump(all_players, f, indent=4)
        
        print(f"[API] [OK] Backed up {len(all_players)} players to {backup_file}")
        
        # Delete players with specific gamemode tiers
        total_deleted = 0
        
        for gamemode in gamemodes:
            tier_column = f"tier_{gamemode}"
            
            # Get players with this gamemode's tier
            for player in all_players:
                if player.get(tier_column):  # Has this gamemode's tier
                    player_id = player["id"]
                    
                    # Clear the tier column
                    patch_url = f"{url}?id=eq.{player_id}"
                    update_payload = {tier_column: None}  # Set to NULL
                    
                    response = requests.patch(patch_url, json=update_payload, headers=headers)
                    
                    if response.status_code in [200, 204]:
                        total_deleted += 1
                        print(f"[API] [OK] Cleared {tier_column} for player {player_id}")
                    else:
                        print(f"[API] [!] Failed to clear {tier_column} for player {player_id}")
        
        print(f"[API] [OK] Successfully cleared {total_deleted} player records for gamemodes: {gamemodes}")
        
        return jsonify({
            "success": True,
            "backup_file": os.path.basename(backup_file),
            "records_cleared": total_deleted,
            "gamemodes_cleared": gamemodes
        }), 200
    
    except Exception as e:
        print(f"[API] clear_gamemode error: {str(e)}")
        import traceback
        traceback.print_exc()
        return jsonify({"success": False, "error": str(e)}), 500

@app.route("/restore_gamemode", methods=["POST"])
def restore_gamemode():
    """Restore player data from gamemode backup"""
    try:
        if not SUPABASE_SERVICE_ROLE_KEY or "PASTE_YOUR_SERVICE_ROLE_KEY" in SUPABASE_SERVICE_ROLE_KEY:
            return jsonify({
                "success": False,
                "error": "SUPABASE_SERVICE_ROLE_KEY not configured"
            }), 500
        
        data = request.json
        backup_prefix = data.get("backup_prefix", "")
        
        if not backup_prefix:
            return jsonify({
                "success": False,
                "error": "No backup_prefix specified"
            }), 400
        
        print(f"[API] restore_gamemode: Restoring from backup {backup_prefix}")
        
        import os
        import json
        
        backup_dir = os.path.normpath(os.path.join(os.path.dirname(__file__), "misc", "backups", "auto_clear"))
        backup_file = os.path.join(backup_dir, f"{backup_prefix}_supabase.json")
        
        if not os.path.exists(backup_file):
            print(f"[API] [ERROR] Backup file not found: {backup_file}")
            return jsonify({
                "success": False,
                "error": f"Backup file not found"
            }), 404
        
        # Load backup
        with open(backup_file, "r", encoding="utf-8") as f:
            backup_players = json.load(f)
        
        print(f"[API] Loaded {len(backup_players)} players from backup")
        
        url = f"{SUPABASE_URL}/rest/v1/players"
        headers = get_headers()
        
        total_restored = 0
        
        # Restore each player
        for backup_player in backup_players:
            player_id = backup_player["id"]
            
            # Build update payload with all tier columns
            update_payload = {}
            for key, value in backup_player.items():
                if key.startswith("tier_"):
                    update_payload[key] = value
            
            if update_payload:
                patch_url = f"{url}?id=eq.{player_id}"
                response = requests.patch(patch_url, json=update_payload, headers=headers)
                
                if response.status_code in [200, 204]:
                    total_restored += 1
                    print(f"[API] [OK] Restored player {player_id}")
                else:
                    print(f"[API] [!] Failed to restore player {player_id}: {response.text[:200]}")
        
        print(f"[API] [OK] Successfully restored {total_restored} players")
        
        return jsonify({
            "success": True,
            "records_restored": total_restored,
            "backup_prefix": backup_prefix
        }), 200
    
    except Exception as e:
        print(f"[API] restore_gamemode error: {str(e)}")
        import traceback
        traceback.print_exc()
        return jsonify({"success": False, "error": str(e)}), 500

if __name__ == "__main__":
    init_logging()
    
    print(f"[KEY] Service Role Key loaded: {len(SUPABASE_SERVICE_ROLE_KEY) if SUPABASE_SERVICE_ROLE_KEY else 0} characters")
    print(f"[URL] Supabase URL: {SUPABASE_URL}")
    print(f"[API] Starting on localhost:5000")
    
    if log_file:
        log_file.write(f"[API] Starting server\n")
        log_file.write(f"[KEY] Service Role Key loaded: {len(SUPABASE_SERVICE_ROLE_KEY) if SUPABASE_SERVICE_ROLE_KEY else 0} characters\n")
        log_file.write(f"[URL] Supabase URL: {SUPABASE_URL}\n")
        log_file.write(f"[API] Listening on localhost:5000\n")
    
    app.run(host="localhost", port=5000, debug=False)