import os
import mysql.connector
from mysql.connector import Error

DB_CONFIG = {
    "host": os.environ.get("DB_HOST", "officialsite.kr"),
    "port": int(os.environ.get("DB_PORT", "23306")),
    "user": os.environ.get("DB_USER", "admin"),
    "password": os.environ.get("DB_PASSWORD", "dlgks~123"),
    "database": os.environ.get("DB_NAME", "app_master"),
}

ALTERS = [
    ("ALTER TABLE ghost_running_battles ADD COLUMN started_by_user_id VARCHAR(36) NULL AFTER invite_code", 1060),
    ("ALTER TABLE ghost_running_battles ADD COLUMN ended_reason VARCHAR(48) NULL AFTER started_by_user_id", 1060),
    (
        "ALTER TABLE ghost_running_battle_participants "
        "ADD COLUMN participant_status ENUM('joined', 'running', 'finished', 'left') NOT NULL DEFAULT 'joined' AFTER role",
        1060,
    ),
    ("ALTER TABLE ghost_running_battle_participants ADD COLUMN last_latitude DECIMAL(10,7) NULL AFTER progress_duration_sec", 1060),
    ("ALTER TABLE ghost_running_battle_participants ADD COLUMN last_longitude DECIMAL(10,7) NULL AFTER last_latitude", 1060),
    ("ALTER TABLE ghost_running_battle_participants ADD COLUMN last_accuracy_m DECIMAL(8,2) NULL AFTER last_longitude", 1060),
    ("ALTER TABLE ghost_running_battle_participants ADD COLUMN last_speed_mps DECIMAL(8,2) NULL AFTER last_accuracy_m", 1060),
    ("ALTER TABLE ghost_running_battle_participants ADD COLUMN joined_at DATETIME NULL AFTER result_status", 1060),
    ("ALTER TABLE ghost_running_battle_participants ADD COLUMN last_ping_at DATETIME NULL AFTER joined_at", 1060),
    ("ALTER TABLE ghost_running_battle_participants ADD COLUMN left_at DATETIME NULL AFTER last_ping_at", 1060),
    ("ALTER TABLE ghost_running_battle_participants ADD COLUMN finished_at DATETIME NULL AFTER left_at", 1060),
    ("ALTER TABLE ghost_running_battle_participants ADD INDEX idx_ghost_running_battle_participants_status (participant_status)", 1061),
]


def run():
    conn = None
    cursor = None
    try:
        conn = mysql.connector.connect(**DB_CONFIG)
        cursor = conn.cursor()
        print("DB Connected")

        for statement, dup_errno in ALTERS:
            try:
                cursor.execute(statement)
                conn.commit()
                print("OK:", statement)
            except Error as error:
                if error.errno == dup_errno:
                    print("SKIP (already exists):", statement)
                else:
                    raise

        print("\n=== ghost running live battle lobby migration completed ===")
    finally:
        if cursor is not None:
            cursor.close()
        if conn is not None and conn.is_connected():
            conn.close()
            print("DB connection closed")


if __name__ == "__main__":
    run()