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"), } CREATE_TABLES = [ """ CREATE TABLE IF NOT EXISTS ghost_running_runs ( id VARCHAR(36) PRIMARY KEY, user_id VARCHAR(36) NOT NULL, source_run_id VARCHAR(36) NULL, live_session_id VARCHAR(36) NULL, title VARCHAR(120) NULL, mode ENUM('solo', 'history_ghost', 'friend_battle', 'live_battle') NOT NULL DEFAULT 'solo', status ENUM('draft', 'completed', 'aborted') NOT NULL DEFAULT 'completed', distance_m DECIMAL(10,2) NOT NULL DEFAULT 0, duration_sec INT NOT NULL DEFAULT 0, avg_pace_sec INT NULL, best_pace_sec INT NULL, calories INT NULL, started_at DATETIME NULL, ended_at DATETIME NULL, route_bounds_json JSON NULL, summary_json JSON NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_ghost_running_runs_user (user_id), INDEX idx_ghost_running_runs_live_session (live_session_id), INDEX idx_ghost_running_runs_mode (mode), INDEX idx_ghost_running_runs_started (started_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci """, """ CREATE TABLE IF NOT EXISTS ghost_running_run_points ( id BIGINT AUTO_INCREMENT PRIMARY KEY, run_id VARCHAR(36) NOT NULL, seq_no INT NOT NULL, elapsed_sec INT NOT NULL, latitude DECIMAL(10,7) NOT NULL, longitude DECIMAL(10,7) NOT NULL, altitude_m DECIMAL(8,2) NULL, pace_sec INT NULL, heart_rate INT NULL, cadence INT NULL, distance_m DECIMAL(10,2) NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_ghost_running_run_point_seq (run_id, seq_no), INDEX idx_ghost_running_run_points_run (run_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci """, """ CREATE TABLE IF NOT EXISTS ghost_running_ghosts ( id VARCHAR(36) PRIMARY KEY, user_id VARCHAR(36) NOT NULL, source_run_id VARCHAR(36) NOT NULL, name VARCHAR(120) NOT NULL, distance_m DECIMAL(10,2) NOT NULL DEFAULT 0, duration_sec INT NOT NULL DEFAULT 0, avg_pace_sec INT NULL, best_pace_sec INT NULL, visibility ENUM('private', 'friends') NOT NULL DEFAULT 'private', metadata_json JSON NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_ghost_running_ghosts_user (user_id), INDEX idx_ghost_running_ghosts_source (source_run_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci """, """ CREATE TABLE IF NOT EXISTS ghost_running_friend_invites ( id VARCHAR(36) PRIMARY KEY, inviter_user_id VARCHAR(36) NOT NULL, invitee_user_id VARCHAR(36) NULL, invite_code VARCHAR(24) NOT NULL, status ENUM('pending', 'accepted', 'declined', 'expired') NOT NULL DEFAULT 'pending', expires_at DATETIME NULL, accepted_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_ghost_running_friend_invites_code (invite_code), INDEX idx_ghost_running_friend_invites_inviter (inviter_user_id), INDEX idx_ghost_running_friend_invites_invitee (invitee_user_id), INDEX idx_ghost_running_friend_invites_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci """, """ CREATE TABLE IF NOT EXISTS ghost_running_friendships ( id VARCHAR(36) PRIMARY KEY, user_id VARCHAR(36) NOT NULL, friend_user_id VARCHAR(36) NOT NULL, status ENUM('active', 'blocked') NOT NULL DEFAULT 'active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_ghost_running_friendships_pair (user_id, friend_user_id), INDEX idx_ghost_running_friendships_friend (friend_user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci """, """ CREATE TABLE IF NOT EXISTS ghost_running_battles ( id VARCHAR(36) PRIMARY KEY, creator_user_id VARCHAR(36) NOT NULL, type ENUM('history_ghost', 'friend_async', 'friend_live') NOT NULL DEFAULT 'history_ghost', title VARCHAR(120) NOT NULL, target_distance_m DECIMAL(10,2) NULL, target_duration_sec INT NULL, status ENUM('pending', 'active', 'completed', 'cancelled') NOT NULL DEFAULT 'pending', ghost_id VARCHAR(36) NULL, invite_code VARCHAR(24) NULL, rules_json JSON NULL, started_at DATETIME NULL, ended_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_ghost_running_battles_creator (creator_user_id), INDEX idx_ghost_running_battles_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci """, """ CREATE TABLE IF NOT EXISTS ghost_running_battle_participants ( id VARCHAR(36) PRIMARY KEY, battle_id VARCHAR(36) NOT NULL, user_id VARCHAR(36) NOT NULL, ghost_id VARCHAR(36) NULL, run_id VARCHAR(36) NULL, role ENUM('creator', 'opponent', 'ghost') NOT NULL DEFAULT 'opponent', progress_distance_m DECIMAL(10,2) NOT NULL DEFAULT 0, progress_duration_sec INT NOT NULL DEFAULT 0, result_rank INT NULL, result_status ENUM('pending', 'won', 'lost', 'draw') NOT NULL DEFAULT 'pending', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_ghost_running_battle_participants (battle_id, user_id, role), INDEX idx_ghost_running_battle_participants_user (user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci """, """ CREATE TABLE IF NOT EXISTS ghost_running_live_sessions ( id VARCHAR(36) PRIMARY KEY, user_id VARCHAR(36) NOT NULL, ghost_run_id VARCHAR(36) NULL, run_id VARCHAR(36) NULL, status ENUM('queued', 'countdown', 'running', 'paused', 'completed', 'cancelled', 'failed') NOT NULL DEFAULT 'queued', device_platform VARCHAR(24) NULL, device_model VARCHAR(120) NULL, app_version VARCHAR(40) NULL, distance_m DECIMAL(10,2) NOT NULL DEFAULT 0, elapsed_sec INT NOT NULL DEFAULT 0, accuracy_m DECIMAL(8,2) NULL, speed_mps DECIMAL(8,2) NULL, started_at DATETIME NULL, last_ping_at DATETIME NULL, ended_at DATETIME NULL, payload_json JSON NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_ghost_running_live_sessions_user (user_id), INDEX idx_ghost_running_live_sessions_status (status), INDEX idx_ghost_running_live_sessions_last_ping (last_ping_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci """, ] ALTERS = [ ("ALTER TABLE ghost_running_runs ADD COLUMN live_session_id VARCHAR(36) NULL AFTER source_run_id", 1060), ("ALTER TABLE ghost_running_runs ADD INDEX idx_ghost_running_runs_live_session (live_session_id)", 1061), ("ALTER TABLE ghost_running_friend_invites ADD COLUMN accepted_at DATETIME NULL AFTER expires_at", 1060), ("ALTER TABLE ghost_running_friend_invites ADD INDEX idx_ghost_running_friend_invites_status (status)", 1061), ] def run(): conn = None cursor = None try: conn = mysql.connector.connect(**DB_CONFIG) cursor = conn.cursor() print("DB Connected") for statement in CREATE_TABLES: cursor.execute(statement) conn.commit() print("OK: create table") 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 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()