#!/usr/bin/env python3
"""
SQLite Database Layer for Battle System
Python 표준 라이브러리만 사용 (sqlite3, json, time, uuid)
"""
import sqlite3
import json
import time
import uuid
from pathlib import Path
from typing import Optional

_db_path: Optional[str] = None
_connection: Optional[sqlite3.Connection] = None

def init_db(db_path: str):
    """
    데이터베이스 초기화
    - 스키마 파일 실행
    - WAL 모드 설정
    - foreign_keys, busy_timeout 설정
    - 마이그레이션 적용
    """
    global _db_path, _connection
    _db_path = db_path
    
    # 디렉토리 자동 생성
    db_file = Path(db_path)
    db_file.parent.mkdir(parents=True, exist_ok=True)
    
    # 스키마 파일 경로
    schema_path = Path(__file__).parent.parent / 'schemas' / 'battle-db-schema.sql'
    
    # 연결 생성 및 설정
    conn = sqlite3.connect(db_path, check_same_thread=False)
    conn.execute('PRAGMA journal_mode=WAL')
    conn.execute('PRAGMA foreign_keys=ON')
    conn.execute('PRAGMA busy_timeout=5000')
    
    # 스키마 적용
    if schema_path.exists():
        schema_sql = schema_path.read_text()
        conn.executescript(schema_sql)
    
    _connection = conn
    
    # 마이그레이션 적용
    _apply_migrations(conn)
    
    return conn

def _apply_migrations(conn: sqlite3.Connection):
    """마이그레이션 적용"""
    cursor = conn.cursor()
    
    # 현재 스키마 버전 확인
    cursor.execute('SELECT MAX(version) FROM schema_version')
    current_version = cursor.fetchone()[0] or 0
    
    # v2 마이그레이션: save_state 컬럼 추가
    if current_version < 2:
        try:
            cursor.execute('ALTER TABLE game_sessions ADD COLUMN save_state TEXT')
            print("✅ Migration v2: Added save_state column")
        except sqlite3.OperationalError as e:
            # 컬럼이 이미 존재하면 무시
            if 'duplicate column' in str(e).lower():
                print("⚠️  Migration v2: save_state column already exists")
            else:
                raise
        
        # 인덱스는 IF NOT EXISTS로 안전
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_sessions_user_game ON game_sessions(user_id, game_id, ended_at)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_battles_adapter_result ON battles(adapter, result)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_battles_created ON battles(created_at)')
        
        # 마이그레이션 기록
        cursor.execute("INSERT OR IGNORE INTO schema_version VALUES (2, ?, 'Add save_state + analytics indexes')", 
                      (time.time(),))
        conn.commit()
        print("✅ Migration v2: Analytics indexes created")

def get_connection() -> sqlite3.Connection:
    """단일 connection 반환 (thread-safe with WAL)"""
    if _connection is None:
        raise RuntimeError("Database not initialized. Call init_db() first.")
    return _connection

# ============================================================
# User Functions
# ============================================================

def upsert_user(user_id: str, device_hash: Optional[str] = None, 
                display_name: Optional[str] = None, settings: Optional[dict] = None) -> dict:
    """유저 생성 또는 업데이트"""
    conn = get_connection()
    cursor = conn.cursor()
    
    now = time.time()
    
    # 기존 유저 확인
    cursor.execute('SELECT level, exp, settings FROM users WHERE id = ?', (user_id,))
    row = cursor.fetchone()
    
    if row:
        # 업데이트
        level, exp, existing_settings = row
        
        # settings 병합 (새 값으로 덮어쓰기)
        if settings:
            merged_settings = json.loads(existing_settings)
            merged_settings.update(settings)
        else:
            merged_settings = json.loads(existing_settings)
        
        cursor.execute('''
            UPDATE users 
            SET display_name = COALESCE(?, display_name),
                device_hash = COALESCE(?, device_hash),
                settings = ?,
                last_seen = ?,
                updated_at = ?
            WHERE id = ?
        ''', (display_name, device_hash, json.dumps(merged_settings), now, now, user_id))
    else:
        # 신규 생성
        level, exp = 1, 0
        merged_settings = settings or {}
        
        cursor.execute('''
            INSERT INTO users (id, display_name, level, exp, settings, device_hash, 
                             created_at, last_seen, updated_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', (user_id, display_name, level, exp, json.dumps(merged_settings), 
              device_hash, now, now, now))
    
    conn.commit()
    
    return {
        'userId': user_id,
        'level': level,
        'exp': exp,
        'displayName': display_name,
        'settings': merged_settings
    }

def get_user(user_id: str) -> Optional[dict]:
    """유저 조회"""
    conn = get_connection()
    cursor = conn.cursor()
    
    cursor.execute('''
        SELECT id, display_name, level, exp, preferred_locale, settings, 
               device_hash, created_at, last_seen, updated_at
        FROM users WHERE id = ?
    ''', (user_id,))
    
    row = cursor.fetchone()
    if not row:
        return None
    
    return {
        'userId': row[0],
        'displayName': row[1],
        'level': row[2],
        'exp': row[3],
        'preferredLocale': row[4],
        'settings': json.loads(row[5]),
        'deviceHash': row[6],
        'createdAt': row[7],
        'lastSeen': row[8],
        'updatedAt': row[9]
    }

def get_user_stats(user_id: str) -> dict:
    """유저 통계 (기존 API 응답 형태 유지)"""
    conn = get_connection()
    cursor = conn.cursor()
    
    # 유저 기본 정보
    user = get_user(user_id)
    if not user:
        return {
            'userId': user_id,
            'level': 1,
            'exp': 0,
            'totalBattles': 0,
            'wins': 0,
            'losses': 0,
            'winRate': 0,
            'adapterStats': {}
        }
    
    # 전투 통계
    cursor.execute('''
        SELECT result, adapter FROM battles WHERE user_id = ?
    ''', (user_id,))
    
    battles = cursor.fetchall()
    wins = sum(1 for b in battles if b[0] == 'win')
    losses = len(battles) - wins
    
    # 어댑터별 통계
    adapter_stats = {}
    for result, adapter in battles:
        if adapter not in adapter_stats:
            adapter_stats[adapter] = {'wins': 0, 'losses': 0, 'total': 0}
        
        adapter_stats[adapter]['total'] += 1
        if result == 'win':
            adapter_stats[adapter]['wins'] += 1
        else:
            adapter_stats[adapter]['losses'] += 1
    
    # 승률 계산
    for adapter in adapter_stats:
        total = adapter_stats[adapter]['total']
        adapter_stats[adapter]['winRate'] = (
            adapter_stats[adapter]['wins'] / total * 100 if total > 0 else 0
        )
    
    return {
        'userId': user_id,
        'level': user['level'],
        'exp': user['exp'],
        'totalBattles': len(battles),
        'wins': wins,
        'losses': losses,
        'winRate': (wins / len(battles) * 100) if battles else 0,
        'adapterStats': adapter_stats
    }

def get_user_history(user_id: str, limit: int = 50) -> list:
    """유저 전투 이력"""
    conn = get_connection()
    cursor = conn.cursor()
    
    cursor.execute('''
        SELECT battle_id, created_at, result, adapter, duration_ms, remaining_hp
        FROM battles 
        WHERE user_id = ?
        ORDER BY created_at DESC
        LIMIT ?
    ''', (user_id, limit))
    
    battles = []
    for row in cursor.fetchall():
        battles.append({
            'battleId': row[0],
            'timestamp': row[1],
            'victory': row[2] == 'win',
            'adapter': row[3],
            'duration': row[4],
            'remainingHP': row[5]
        })
    
    return battles

def update_user_exp(user_id: str, exp_gained: int) -> dict:
    """EXP 업데이트 및 레벨업 처리"""
    conn = get_connection()
    cursor = conn.cursor()
    
    # 현재 유저 정보
    cursor.execute('SELECT level, exp FROM users WHERE id = ?', (user_id,))
    row = cursor.fetchone()
    
    if not row:
        # 유저 없으면 생성
        upsert_user(user_id)
        level, exp = 1, 0
    else:
        level, exp = row
    
    # EXP 추가
    exp += exp_gained
    
    # 레벨업 처리 (100 EXP당 1레벨)
    while exp >= 100:
        exp -= 100
        level += 1
    
    # 업데이트
    cursor.execute('''
        UPDATE users 
        SET level = ?, exp = ?, updated_at = ?
        WHERE id = ?
    ''', (level, exp, time.time(), user_id))
    
    conn.commit()
    
    return {
        'userId': user_id,
        'level': level,
        'exp': exp,
        'expGained': exp_gained
    }

# ============================================================
# Battle Functions
# ============================================================

def insert_battle(battle_data: dict) -> dict:
    """전투 결과 저장"""
    conn = get_connection()
    cursor = conn.cursor()
    
    battle_id = battle_data.get('battleId', str(uuid.uuid4()))
    user_id = battle_data.get('userId')
    session_id = battle_data.get('sessionId')
    game_id = battle_data.get('gameId')
    adapter = battle_data.get('adapter', 'unknown')
    
    # result 결정 (result='win' 또는 victory=True)
    result = battle_data.get('result', 'unknown')
    if result == 'unknown' and battle_data.get('victory'):
        result = 'win'
    
    # 적 레벨 (EXP 계산용)
    enemy_level = battle_data.get('enemyLevel', 1)
    if 'enemyStats' in battle_data and isinstance(battle_data['enemyStats'], dict):
        enemy_level = battle_data['enemyStats'].get('level', enemy_level)
    
    # EXP 계산
    is_victory = (result == 'win')
    exp_gained = enemy_level * 15 if is_victory else enemy_level * 5
    
    # 유저 EXP 업데이트
    user_level = 1
    if user_id:
        user_result = update_user_exp(user_id, exp_gained)
        user_level = user_result['level']
    
    # 전투 저장
    cursor.execute('''
        INSERT INTO battles (
            battle_id, user_id, session_id, game_id, adapter,
            battle_slot_id, scenario_node_id, result,
            remaining_hp, turns_used, total_damage, enemy_level, exp_gained,
            duration_ms, player_stats, enemy_stats, special_conditions,
            input_count, is_autoplay, client_build, raw_data, created_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    ''', (
        battle_id,
        user_id,
        session_id,
        game_id,
        adapter,
        battle_data.get('battleSlotId'),
        battle_data.get('scenarioNodeId'),
        result,
        battle_data.get('remainingHP'),
        battle_data.get('turnsUsed'),
        battle_data.get('totalDamage'),
        enemy_level,
        exp_gained,
        battle_data.get('durationMs'),
        json.dumps(battle_data.get('playerStats')) if battle_data.get('playerStats') else None,
        json.dumps(battle_data.get('enemyStats')) if battle_data.get('enemyStats') else None,
        json.dumps(battle_data.get('specialConditions', [])),
        battle_data.get('inputCount'),
        1 if battle_data.get('isAutoplay') else 0,
        battle_data.get('clientBuild'),
        json.dumps(battle_data),
        battle_data.get('timestamp', time.time())
    ))
    
    # 세션 통계 업데이트
    if session_id:
        cursor.execute('''
            UPDATE game_sessions 
            SET battles_played = battles_played + 1,
                battles_won = battles_won + ?,
                last_node_id = COALESCE(?, last_node_id)
            WHERE session_id = ?
        ''', (1 if is_victory else 0, battle_data.get('scenarioNodeId'), session_id))
    
    conn.commit()
    
    # 응답 구성 (기존 API 호환)
    response = dict(battle_data)
    response.update({
        'battleId': battle_id,
        'expGained': exp_gained,
        'userLevel': user_level,
        'result': result
    })
    
    return response

def get_battle(battle_id: str) -> Optional[dict]:
    """전투 결과 조회"""
    conn = get_connection()
    cursor = conn.cursor()
    
    cursor.execute('''
        SELECT battle_id, user_id, session_id, game_id, adapter,
               battle_slot_id, scenario_node_id, result,
               remaining_hp, turns_used, total_damage, enemy_level, exp_gained,
               duration_ms, player_stats, enemy_stats, special_conditions,
               input_count, is_autoplay, client_build, raw_data, created_at
        FROM battles WHERE battle_id = ?
    ''', (battle_id,))
    
    row = cursor.fetchone()
    if not row:
        return None
    
    # raw_data에서 원본 필드 복원
    raw_data = json.loads(row[20]) if row[20] else {}
    
    return {
        'battleId': row[0],
        'userId': row[1],
        'sessionId': row[2],
        'gameId': row[3],
        'adapter': row[4],
        'battleSlotId': row[5],
        'scenarioNodeId': row[6],
        'result': row[7],
        'remainingHP': row[8],
        'turnsUsed': row[9],
        'totalDamage': row[10],
        'enemyLevel': row[11],
        'expGained': row[12],
        'durationMs': row[13],
        'playerStats': json.loads(row[14]) if row[14] else None,
        'enemyStats': json.loads(row[15]) if row[15] else None,
        'specialConditions': json.loads(row[16]) if row[16] else [],
        'inputCount': row[17],
        'isAutoplay': bool(row[18]),
        'clientBuild': row[19],
        'timestamp': row[21],
        **raw_data  # 원본 필드 병합
    }

# ============================================================
# Session Functions
# ============================================================

def create_session(user_id: str, game_id: str, client_build: Optional[str] = None) -> str:
    """게임 세션 생성"""
    conn = get_connection()
    cursor = conn.cursor()
    
    session_id = str(uuid.uuid4())
    now = time.time()
    
    # 게임 존재 확인 (없으면 생성)
    cursor.execute('SELECT game_id FROM games WHERE game_id = ?', (game_id,))
    if not cursor.fetchone():
        # 게임이 없으면 최소 정보로 생성
        cursor.execute('''
            INSERT INTO games (game_id, adapters_used, created_at)
            VALUES (?, '[]', ?)
        ''', (game_id, now))
    
    # 유저 존재 확인 (없으면 생성)
    cursor.execute('SELECT id FROM users WHERE id = ?', (user_id,))
    if not cursor.fetchone():
        cursor.execute('''
            INSERT INTO users (id, level, exp, created_at, last_seen, updated_at)
            VALUES (?, 1, 0, ?, ?, ?)
        ''', (user_id, now, now, now))
    
    cursor.execute('''
        INSERT INTO game_sessions (
            session_id, user_id, game_id, started_at, client_build
        ) VALUES (?, ?, ?, ?, ?)
    ''', (session_id, user_id, game_id, now, client_build))
    
    conn.commit()
    
    return session_id

def end_session(session_id: str, end_reason: str, 
                ending_id: Optional[str] = None, 
                drop_node_id: Optional[str] = None) -> dict:
    """세션 종료"""
    conn = get_connection()
    cursor = conn.cursor()
    
    now = time.time()
    
    cursor.execute('''
        UPDATE game_sessions 
        SET ended_at = ?,
            end_reason = ?,
            ending_id = ?,
            drop_node_id = ?
        WHERE session_id = ?
    ''', (now, end_reason, ending_id, drop_node_id, session_id))
    
    conn.commit()
    
    # 세션 정보 반환
    cursor.execute('''
        SELECT session_id, user_id, game_id, started_at, ended_at, 
               battles_played, battles_won, chapters_completed
        FROM game_sessions WHERE session_id = ?
    ''', (session_id,))
    
    row = cursor.fetchone()
    if not row:
        return {'status': 'not_found'}
    
    return {
        'sessionId': row[0],
        'userId': row[1],
        'gameId': row[2],
        'startedAt': row[3],
        'endedAt': row[4],
        'battlesPlayed': row[5],
        'battlesWon': row[6],
        'chaptersCompleted': row[7],
        'endReason': end_reason
    }

def update_session(session_id: str, **kwargs) -> dict:
    """세션 업데이트"""
    conn = get_connection()
    cursor = conn.cursor()
    
    # 허용된 필드만 업데이트 (camelCase → snake_case 매핑)
    camel_to_snake = {
        'lastNodeId': 'last_node_id',
        'battlesPlayed': 'battles_played',
        'battlesWon': 'battles_won',
        'chaptersCompleted': 'chapters_completed',
        'saveState': 'save_state',
    }
    allowed_fields = ['last_node_id', 'battles_played', 'battles_won', 'chapters_completed', 'save_state']
    updates = []
    values = []
    
    # camelCase 키를 snake_case로 변환
    normalized = {}
    for key, value in kwargs.items():
        normalized[camel_to_snake.get(key, key)] = value
    
    for key, value in normalized.items():
        if key in allowed_fields:
            updates.append(f"{key} = ?")
            # save_state는 JSON 직렬화
            if key == 'save_state' and value is not None:
                if isinstance(value, (dict, list)):
                    values.append(json.dumps(value))
                else:
                    values.append(value)
            else:
                values.append(value)
    
    if not updates:
        return {'status': 'no_updates'}
    
    values.append(session_id)
    sql = f"UPDATE game_sessions SET {', '.join(updates)} WHERE session_id = ?"
    
    cursor.execute(sql, values)
    conn.commit()
    
    return {'status': 'ok', 'sessionId': session_id}

def get_user_sessions(user_id: str, game_id: Optional[str] = None, 
                      status: Optional[str] = None, limit: int = 20) -> list:
    """
    유저의 세션 목록 조회
    status='active' → ended_at IS NULL
    status='completed' → end_reason='completed'
    """
    conn = get_connection()
    cursor = conn.cursor()
    
    conditions = ['user_id = ?']
    params = [user_id]
    
    if game_id:
        conditions.append('game_id = ?')
        params.append(game_id)
    
    if status == 'active':
        conditions.append('ended_at IS NULL')
    elif status == 'completed':
        conditions.append("end_reason = 'completed'")
    
    where_clause = ' AND '.join(conditions)
    params.append(limit)
    
    cursor.execute(f'''
        SELECT session_id, game_id, started_at, ended_at, end_reason,
               battles_played, battles_won, last_node_id, save_state
        FROM game_sessions
        WHERE {where_clause}
        ORDER BY started_at DESC
        LIMIT ?
    ''', params)
    
    sessions = []
    for row in cursor.fetchall():
        save_state = None
        if row[8]:
            try:
                save_state = json.loads(row[8])
            except:
                save_state = row[8]
        
        sessions.append({
            'sessionId': row[0],
            'gameId': row[1],
            'startedAt': row[2],
            'endedAt': row[3],
            'endReason': row[4],
            'battlesPlayed': row[5],
            'battlesWon': row[6],
            'lastNodeId': row[7],
            'saveState': save_state
        })
    
    return sessions

def get_session(session_id: str) -> Optional[dict]:
    """세션 상세 조회 (save_state 포함)"""
    conn = get_connection()
    cursor = conn.cursor()
    
    cursor.execute('''
        SELECT session_id, user_id, game_id, started_at, ended_at, end_reason,
               ending_id, chapters_completed, battles_played, battles_won,
               last_node_id, drop_node_id, client_build, save_state
        FROM game_sessions
        WHERE session_id = ?
    ''', (session_id,))
    
    row = cursor.fetchone()
    if not row:
        return None
    
    save_state = None
    if row[13]:
        try:
            save_state = json.loads(row[13])
        except:
            save_state = row[13]
    
    return {
        'sessionId': row[0],
        'userId': row[1],
        'gameId': row[2],
        'startedAt': row[3],
        'endedAt': row[4],
        'endReason': row[5],
        'endingId': row[6],
        'chaptersCompleted': row[7],
        'battlesPlayed': row[8],
        'battlesWon': row[9],
        'lastNodeId': row[10],
        'dropNodeId': row[11],
        'clientBuild': row[12],
        'saveState': save_state
    }

def get_analytics_overview() -> dict:
    """글로벌 통계"""
    conn = get_connection()
    cursor = conn.cursor()
    
    # 고유 유저 수
    cursor.execute('SELECT COUNT(DISTINCT user_id) FROM game_sessions')
    unique_users = cursor.fetchone()[0]
    
    # 총 세션 수
    cursor.execute('SELECT COUNT(*) FROM game_sessions')
    total_sessions = cursor.fetchone()[0]
    
    # 활성 세션 (ended_at IS NULL)
    cursor.execute('SELECT COUNT(*) FROM game_sessions WHERE ended_at IS NULL')
    active_sessions = cursor.fetchone()[0]
    
    # 완료된 세션
    cursor.execute("SELECT COUNT(*) FROM game_sessions WHERE end_reason = 'completed'")
    completed_sessions = cursor.fetchone()[0]
    
    # 총 전투 수
    cursor.execute('SELECT COUNT(*) FROM battles')
    total_battles = cursor.fetchone()[0]
    
    # 승리 수
    cursor.execute("SELECT COUNT(*) FROM battles WHERE result = 'win'")
    wins = cursor.fetchone()[0]
    
    win_rate = (wins / total_battles * 100) if total_battles > 0 else 0
    
    # 평균 세션 길이 (완료된 세션만)
    cursor.execute('''
        SELECT AVG((ended_at - started_at) * 1000)
        FROM game_sessions
        WHERE ended_at IS NOT NULL
    ''')
    avg_duration = cursor.fetchone()[0] or 0
    
    return {
        'uniqueUsers': unique_users,
        'totalSessions': total_sessions,
        'activeSessions': active_sessions,
        'completedSessions': completed_sessions,
        'totalBattles': total_battles,
        'wins': wins,
        'winRate': round(win_rate, 2),
        'avgSessionDurationMs': round(avg_duration, 2)
    }

def get_analytics_funnel(game_id: str) -> dict:
    """게임별 퍼널 — battleCount별 세션 수"""
    conn = get_connection()
    cursor = conn.cursor()
    
    cursor.execute('''
        SELECT battles_played, COUNT(*) as session_count
        FROM game_sessions
        WHERE game_id = ?
        GROUP BY battles_played
        ORDER BY battles_played
    ''', (game_id,))
    
    steps = []
    total_sessions = 0
    completed = 0
    
    for row in cursor.fetchall():
        battles_completed = row[0]
        session_count = row[1]
        total_sessions += session_count
        
        steps.append({
            'battlesCompleted': battles_completed,
            'sessionCount': session_count
        })
    
    # 완료율
    cursor.execute('''
        SELECT COUNT(*) FROM game_sessions
        WHERE game_id = ? AND end_reason = 'completed'
    ''', (game_id,))
    completed = cursor.fetchone()[0]
    
    completion_rate = (completed / total_sessions * 100) if total_sessions > 0 else 0
    
    return {
        'gameId': game_id,
        'steps': steps,
        'completionRate': round(completion_rate, 2)
    }

def get_analytics_adapters() -> dict:
    """어댑터별 통계"""
    conn = get_connection()
    cursor = conn.cursor()
    
    cursor.execute('''
        SELECT adapter, result, turns_used, duration_ms
        FROM battles
        WHERE adapter IS NOT NULL
    ''')
    
    adapters = {}
    
    for row in cursor.fetchall():
        adapter = row[0]
        result = row[1]
        turns = row[2] or 0
        duration = row[3] or 0
        
        if adapter not in adapters:
            adapters[adapter] = {
                'total': 0,
                'wins': 0,
                'turnsList': [],
                'durationList': []
            }
        
        adapters[adapter]['total'] += 1
        if result == 'win':
            adapters[adapter]['wins'] += 1
        
        if turns > 0:
            adapters[adapter]['turnsList'].append(turns)
        if duration > 0:
            adapters[adapter]['durationList'].append(duration)
    
    # 통계 계산
    result = {}
    for adapter, stats in adapters.items():
        total = stats['total']
        wins = stats['wins']
        win_rate = (wins / total * 100) if total > 0 else 0
        
        avg_turns = sum(stats['turnsList']) / len(stats['turnsList']) if stats['turnsList'] else 0
        avg_duration = sum(stats['durationList']) / len(stats['durationList']) if stats['durationList'] else 0
        
        result[adapter] = {
            'total': total,
            'wins': wins,
            'winRate': round(win_rate, 2),
            'avgTurns': round(avg_turns, 2),
            'avgDurationMs': round(avg_duration, 2)
        }
    
    return {'adapters': result}

def get_analytics_timeline(days: int = 30) -> dict:
    """일별 세션/전투 수"""
    conn = get_connection()
    cursor = conn.cursor()
    
    cutoff = time.time() - (days * 86400)
    
    # 일별 세션 수
    cursor.execute('''
        SELECT DATE(started_at, 'unixepoch') as date, COUNT(*) as count
        FROM game_sessions
        WHERE started_at >= ?
        GROUP BY date
        ORDER BY date
    ''', (cutoff,))
    
    sessions_by_date = {row[0]: row[1] for row in cursor.fetchall()}
    
    # 일별 전투 수
    cursor.execute('''
        SELECT DATE(created_at, 'unixepoch') as date, COUNT(*) as count
        FROM battles
        WHERE created_at >= ?
        GROUP BY date
        ORDER BY date
    ''', (cutoff,))
    
    battles_by_date = {row[0]: row[1] for row in cursor.fetchall()}
    
    # 모든 날짜 병합 (None 제외)
    all_dates = sorted(set([d for d in list(sessions_by_date.keys()) + list(battles_by_date.keys()) if d is not None]))
    
    timeline = []
    for date in all_dates:
        timeline.append({
            'date': date,
            'sessions': sessions_by_date.get(date, 0),
            'battles': battles_by_date.get(date, 0)
        })
    
    return {'days': timeline}

def cleanup_stale_sessions(timeout_minutes: int = 30):
    """오래된 진행 중 세션 자동 종료"""
    conn = get_connection()
    cursor = conn.cursor()
    
    cutoff = time.time() - (timeout_minutes * 60)
    
    cursor.execute('''
        UPDATE game_sessions 
        SET ended_at = ?,
            end_reason = 'timeout'
        WHERE ended_at IS NULL 
          AND started_at < ?
    ''', (time.time(), cutoff))
    
    updated = cursor.rowcount
    conn.commit()
    
    return {'cleaned': updated}

# ============================================================
# Event Functions
# ============================================================

def insert_event(user_id: Optional[str], session_id: Optional[str], 
                 game_id: Optional[str], event_type: str, event_data: dict) -> int:
    """이벤트 로그 저장"""
    conn = get_connection()
    cursor = conn.cursor()
    
    cursor.execute('''
        INSERT INTO events (user_id, session_id, game_id, event_type, event_data, created_at)
        VALUES (?, ?, ?, ?, ?, ?)
    ''', (user_id, session_id, game_id, event_type, json.dumps(event_data), time.time()))
    
    conn.commit()
    
    return cursor.lastrowid

# ============================================================
# Game Functions
# ============================================================

def upsert_game(game_id: str, request_id: Optional[str] = None, 
                name: Optional[str] = None, genre: Optional[str] = None,
                adapters_used: Optional[list] = None, battle_slot_count: int = 0,
                version: str = '1.0', serving_url: Optional[str] = None) -> dict:
    """게임 레지스트리 생성/업데이트"""
    conn = get_connection()
    cursor = conn.cursor()
    
    now = time.time()
    
    # 기존 게임 확인
    cursor.execute('SELECT game_id FROM games WHERE game_id = ?', (game_id,))
    exists = cursor.fetchone()
    
    if exists:
        # 업데이트
        cursor.execute('''
            UPDATE games 
            SET request_id = COALESCE(?, request_id),
                name = COALESCE(?, name),
                genre = COALESCE(?, genre),
                adapters_used = COALESCE(?, adapters_used),
                battle_slot_count = COALESCE(?, battle_slot_count),
                version = COALESCE(?, version),
                serving_url = COALESCE(?, serving_url)
            WHERE game_id = ?
        ''', (request_id, name, genre, 
              json.dumps(adapters_used) if adapters_used else None,
              battle_slot_count, version, serving_url, game_id))
    else:
        # 신규 생성
        cursor.execute('''
            INSERT INTO games (
                game_id, request_id, name, genre, adapters_used,
                battle_slot_count, version, serving_url, created_at
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', (game_id, request_id, name, genre, 
              json.dumps(adapters_used or []),
              battle_slot_count, version, serving_url, now))
    
    conn.commit()
    
    return {'gameId': game_id, 'status': 'ok'}

def get_game_stats(game_id: str) -> dict:
    """게임 통계"""
    conn = get_connection()
    cursor = conn.cursor()
    
    # 플레이 횟수
    cursor.execute('''
        SELECT COUNT(*) FROM game_sessions WHERE game_id = ?
    ''', (game_id,))
    play_count = cursor.fetchone()[0]
    
    # 완료율 (ended_at이 있고 end_reason='completed')
    cursor.execute('''
        SELECT COUNT(*) FROM game_sessions 
        WHERE game_id = ? AND end_reason = 'completed'
    ''', (game_id,))
    completed_count = cursor.fetchone()[0]
    
    avg_completion = (completed_count / play_count * 100) if play_count > 0 else 0
    
    # 어댑터별 승률
    cursor.execute('''
        SELECT adapter, result FROM battles WHERE game_id = ?
    ''', (game_id,))
    
    battles = cursor.fetchall()
    adapter_stats = {}
    
    for adapter, result in battles:
        if adapter not in adapter_stats:
            adapter_stats[adapter] = {'wins': 0, 'total': 0}
        
        adapter_stats[adapter]['total'] += 1
        if result == 'win':
            adapter_stats[adapter]['wins'] += 1
    
    # 승률 계산
    for adapter in adapter_stats:
        total = adapter_stats[adapter]['total']
        adapter_stats[adapter]['winRate'] = (
            adapter_stats[adapter]['wins'] / total * 100 if total > 0 else 0
        )
    
    return {
        'gameId': game_id,
        'playCount': play_count,
        'completedCount': completed_count,
        'avgCompletion': avg_completion,
        'adapterStats': adapter_stats
    }

# ============================================================
# Error Cases Functions
# ============================================================

def insert_error(error_data: dict) -> dict:
    """에러 케이스 기록"""
    conn = get_connection()
    cursor = conn.cursor()
    
    created_at = error_data.get('createdAt', time.time())
    
    cursor.execute('''
        INSERT INTO error_cases (
            source, task_label, adapter, phase, model,
            error_type, error_message, error_context,
            resolution, resolution_type, resolution_diff,
            resolved, iterations, time_to_resolve_ms,
            created_at, resolved_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    ''', (
        error_data.get('source', 'manual'),
        error_data.get('taskLabel'),
        error_data.get('adapter'),
        error_data.get('phase'),
        error_data.get('model'),
        error_data.get('errorType', 'unknown'),
        error_data.get('errorMessage', ''),
        error_data.get('errorContext'),
        error_data.get('resolution'),
        error_data.get('resolutionType'),
        error_data.get('resolutionDiff'),
        1 if error_data.get('resolved', False) else 0,
        error_data.get('iterations', 1),
        error_data.get('timeToResolveMs'),
        created_at,
        error_data.get('resolvedAt'),
    ))
    conn.commit()
    
    error_id = cursor.lastrowid
    return {'id': error_id, 'status': 'ok'}


def resolve_error(error_id: int, resolution_data: dict) -> dict:
    """에러 해결 기록 업데이트"""
    conn = get_connection()
    
    conn.execute('''
        UPDATE error_cases SET
            resolution = ?,
            resolution_type = ?,
            resolution_diff = ?,
            resolved = 1,
            resolved_at = ?,
            iterations = COALESCE(?, iterations)
        WHERE id = ?
    ''', (
        resolution_data.get('resolution'),
        resolution_data.get('resolutionType'),
        resolution_data.get('resolutionDiff'),
        time.time(),
        resolution_data.get('iterations'),
        error_id,
    ))
    conn.commit()
    
    return {'id': error_id, 'status': 'resolved'}


def search_errors(error_type: str = None, source: str = None, 
                  keyword: str = None, resolved_only: bool = True,
                  limit: int = 5) -> list:
    """유사 에러 검색 (Phase 1: 키워드 + 태그 매칭)"""
    conn = get_connection()
    conn.row_factory = sqlite3.Row
    
    conditions = []
    params = []
    
    if resolved_only:
        conditions.append('resolved = 1')
    
    if error_type:
        conditions.append('error_type = ?')
        params.append(error_type)
    
    if source:
        conditions.append('source = ?')
        params.append(source)
    
    if keyword:
        conditions.append('(error_message LIKE ? OR error_context LIKE ? OR resolution LIKE ?)')
        kw = f'%{keyword}%'
        params.extend([kw, kw, kw])
    
    where = f"WHERE {' AND '.join(conditions)}" if conditions else ''
    
    rows = conn.execute(f'''
        SELECT id, source, task_label, adapter, phase, model,
               error_type, error_message, error_context,
               resolution, resolution_type, resolution_diff,
               iterations, created_at
        FROM error_cases
        {where}
        ORDER BY created_at DESC
        LIMIT ?
    ''', params + [limit]).fetchall()
    
    conn.row_factory = None
    
    return [dict(r) for r in rows]


def get_error_stats() -> dict:
    """에러 통계"""
    conn = get_connection()
    
    total = conn.execute('SELECT COUNT(*) FROM error_cases').fetchone()[0]
    resolved = conn.execute('SELECT COUNT(*) FROM error_cases WHERE resolved = 1').fetchone()[0]
    
    # error_type별 빈도
    type_counts = conn.execute('''
        SELECT error_type, COUNT(*) as cnt 
        FROM error_cases 
        GROUP BY error_type 
        ORDER BY cnt DESC
    ''').fetchall()
    
    # resolution_type별 빈도
    res_counts = conn.execute('''
        SELECT resolution_type, COUNT(*) as cnt 
        FROM error_cases 
        WHERE resolved = 1 
        GROUP BY resolution_type 
        ORDER BY cnt DESC
    ''').fetchall()
    
    return {
        'total': total,
        'resolved': resolved,
        'unresolved': total - resolved,
        'byErrorType': {r[0]: r[1] for r in type_counts},
        'byResolutionType': {r[0]: r[1] for r in res_counts if r[0]},
    }