import sqlite3
import threading
import time
import queue
import logging
from flask import Blueprint, request, jsonify, current_app

socketio = None

# ---------- 游戏数据库连接池 (chat.db) ----------
GAME_DB_PATH = 'chat.db'
game_connection_pool = queue.Queue(maxsize=200)  # 最大连接数100

def init_game_db_pool():
    """初始化游戏数据库连接池，创建100个连接并放入队列"""
    for i in range(game_connection_pool.maxsize):
        conn = sqlite3.connect(GAME_DB_PATH, timeout=30, check_same_thread=False)
        conn.execute("PRAGMA journal_mode=WAL")
        conn.execute("PRAGMA synchronous=NORMAL")
        conn.execute("PRAGMA cache_size=10000")
        conn.execute("PRAGMA busy_timeout=10000")
        game_connection_pool.put(conn)
    logger.info(f"游戏数据库连接池初始化完成，共 {game_connection_pool.maxsize} 个连接")
    
    # 使用独立连接创建索引（避免占用池中连接）
    conn = sqlite3.connect(GAME_DB_PATH)
    try:
        c = conn.cursor()
        # characters 表已迁移，不再创建其索引
        # 保留对其他表的索引
        c.execute("CREATE INDEX IF NOT EXISTS idx_auction_status_end ON auction_items(status, end_time)")
        c.execute("CREATE INDEX IF NOT EXISTS idx_auction_seller ON auction_items(seller_character_name)")
        c.execute("CREATE INDEX IF NOT EXISTS idx_player_logs_name_time ON player_logs(player_name, created_at)")
        conn.commit()
        logger.info("数据库索引创建/检查完成")
    except Exception as e:
        logger.error(f"创建索引失败: {e}")
    finally:
        conn.close()

def get_game_db():
    """获取游戏数据库连接，带重试机制和超时"""
    max_retries = 3
    for attempt in range(max_retries):
        try:
            # 等待最多10秒获取连接
            conn = game_connection_pool.get(timeout=30)
            # 每次获取后重新应用PRAGMA（防止连接被其他线程修改）
            conn.execute("PRAGMA journal_mode=WAL")
            conn.execute("PRAGMA busy_timeout=10000")
            return conn
        except queue.Empty:
            if attempt == max_retries - 1:
                logger.error("游戏数据库连接池繁忙，重试3次后仍失败")
                raise Exception("游戏数据库连接池繁忙，请稍后重试")
            logger.warning(f"游戏数据库连接池繁忙，正在重试 ({attempt+1}/{max_retries})")
            time.sleep(1)  # 等待1秒后重试
    raise Exception("游戏数据库连接池繁忙，请稍后重试")

def close_game_connection(conn):
    """归还数据库连接到池中，并重置连接状态"""
    if conn is not None:
        try:
            # 可选：回滚未提交的事务，避免残留状态
            conn.rollback()
            # 重置连接的PRAGMA为默认值（下次获取时会重新设置）
            conn.execute("PRAGMA journal_mode=WAL")
            conn.execute("PRAGMA busy_timeout=10000")
            game_connection_pool.put(conn)
        except Exception as e:
            logger.error(f"归还游戏数据库连接失败: {e}")
            # 如果连接已损坏，丢弃它并创建新连接补充
            try:
                new_conn = sqlite3.connect(GAME_DB_PATH, timeout=30, check_same_thread=False)
                new_conn.execute("PRAGMA journal_mode=WAL")
                new_conn.execute("PRAGMA synchronous=NORMAL")
                new_conn.execute("PRAGMA cache_size=10000")
                new_conn.execute("PRAGMA busy_timeout=10000")
                game_connection_pool.put(new_conn)
                logger.info("已用新连接替换损坏连接")
            except Exception as ne:
                logger.error(f"创建新连接失败: {ne}")

# ---------- 社交数据库连接池 (chatroom.db) ----------
CHAT_DB_PATH = 'chatroom.db'
chat_connection_pool = queue.Queue(maxsize=50)

def init_chat_db_pool():
    for i in range(chat_connection_pool.maxsize):
        conn = sqlite3.connect(CHAT_DB_PATH, timeout=20, check_same_thread=False)
        conn.execute("PRAGMA journal_mode=WAL")
        conn.execute("PRAGMA synchronous=NORMAL")
        conn.execute("PRAGMA cache_size=10000")
        conn.execute("PRAGMA busy_timeout=5000")
        chat_connection_pool.put(conn)
    logger.info(f"社交数据库连接池初始化完成，共 {chat_connection_pool.maxsize} 个连接")

def get_chat_db():
    max_retries = 3
    for attempt in range(max_retries):
        try:
            conn = chat_connection_pool.get(timeout=10)
            conn.execute("PRAGMA journal_mode=WAL")
            conn.execute("PRAGMA busy_timeout=5000")
            return conn
        except queue.Empty:
            if attempt == max_retries - 1:
                logger.error("社交数据库连接池繁忙，重试3次后仍失败")
                raise Exception("社交数据库连接池繁忙，请稍后重试")
            logger.warning(f"社交数据库连接池繁忙，正在重试 ({attempt+1}/{max_retries})")
            time.sleep(1)
    raise Exception("社交数据库连接池繁忙，请稍后重试")

def close_chat_connection(conn):
    if conn is not None:
        try:
            conn.rollback()
            conn.execute("PRAGMA journal_mode=WAL")
            conn.execute("PRAGMA busy_timeout=5000")
            chat_connection_pool.put(conn)
        except Exception as e:
            logger.error(f"归还社交数据库连接失败: {e}")
            try:
                new_conn = sqlite3.connect(CHAT_DB_PATH, timeout=20, check_same_thread=False)
                new_conn.execute("PRAGMA journal_mode=WAL")
                new_conn.execute("PRAGMA synchronous=NORMAL")
                new_conn.execute("PRAGMA cache_size=10000")
                new_conn.execute("PRAGMA busy_timeout=5000")
                chat_connection_pool.put(new_conn)
                logger.info("已用新连接替换损坏社交连接")
            except Exception as ne:
                logger.error(f"创建新社交连接失败: {ne}")

# 为了兼容 main.py 中原有的 get_db 调用，保留别名指向游戏数据库
get_db = get_game_db
close_connection = close_game_connection

# ---------- 排行榜缓存（优化：增量更新）----------
rankings_cache = []
rankings_cache_lock = threading.Lock()
last_rankings_update = 0
rankings_update_in_progress = False

logging.basicConfig(level=logging.ERROR)
logger = logging.getLogger(__name__)

def update_rankings_cache():
    global rankings_cache, last_rankings_update, rankings_update_in_progress
    compute_rankings_once()
    while True:
        time.sleep(60)
        if rankings_update_in_progress:
            continue
        compute_rankings_once()

def compute_rankings_once():
    global rankings_cache, last_rankings_update, rankings_update_in_progress
    if rankings_update_in_progress:
        return
    rankings_update_in_progress = True
    conn = None
    try:
        conn = get_game_db()
        conn.row_factory = sqlite3.Row
        c = conn.cursor()
        # 从 players 表获取等级/转生，从 tasks 表获取积分
        c.execute('''
            SELECT p.name, p.level, p.rebirth, COALESCE(t.points, 0) as points
            FROM players p
            LEFT JOIN tasks t ON p.id = t.player_id
            ORDER BY p.rebirth DESC, p.level DESC, p.name ASC
            LIMIT 50
        ''')
        rows = c.fetchall()
        rankings = [{'name': row['name'], 'level': row['level'], 'rebirth': row['rebirth'], 'points': row['points']} for row in rows]
        with rankings_cache_lock:
            rankings_cache = rankings
            last_rankings_update = time.time()
        logger.info(f"排行榜更新完成，共 {len(rankings)} 条记录")
    except Exception as e:
        logger.error(f"更新排行榜缓存失败: {e}")
    finally:
        rankings_update_in_progress = False
        if conn:
            close_game_connection(conn)

threading.Thread(target=update_rankings_cache, daemon=True).start()

# ---------- 社交数据库初始化 ----------
def init_chat_db():
    conn = get_chat_db()
    try:
        c = conn.cursor()
        c.execute('''
            CREATE TABLE IF NOT EXISTS chat_messages (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                sender TEXT,
                target TEXT,
                content TEXT,
                type TEXT,
                timestamp REAL
            )
        ''')
        c.execute('''
            CREATE TABLE IF NOT EXISTS board_messages (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                sender TEXT NOT NULL,
                content TEXT NOT NULL,
                timestamp REAL NOT NULL
            )
        ''')
        c.execute("CREATE INDEX IF NOT EXISTS idx_chat_type_id ON chat_messages(type, id)")
        c.execute("CREATE INDEX IF NOT EXISTS idx_chat_private ON chat_messages(type, sender, target, id)")
        c.execute("CREATE INDEX IF NOT EXISTS idx_board_id ON board_messages(id)")
        conn.commit()
        logger.info("社交数据库初始化完成")
    except Exception as e:
        logger.error(f"社交数据库初始化失败: {e}")
    finally:
        close_chat_connection(conn)

# ---------- 后台清理线程：删除7天前的聊天消息 ----------
def cleanup_old_chat_messages():
    while True:
        time.sleep(600)  # 10分钟
        conn = None
        try:
            conn = get_chat_db()
            c = conn.cursor()
            week_ago = time.time() - 7 * 24 * 3600
            c.execute('DELETE FROM chat_messages WHERE timestamp < ?', (week_ago,))
            deleted = c.rowcount
            conn.commit()
            if deleted > 0:
                logger.info(f"清理聊天消息完成，删除 {deleted} 条旧记录")
        except Exception as e:
            logger.error(f"清理旧聊天消息失败: {e}")
        finally:
            if conn:
                close_chat_connection(conn)

threading.Thread(target=cleanup_old_chat_messages, daemon=True).start()

# ---------- HTTP API 蓝图 ----------
chat_bp = Blueprint('chat', __name__, url_prefix='/api/chat')

@chat_bp.route('/online', methods=['GET'])
def get_online():
    try:
        from online_state import online_cache, cache_lock
        with cache_lock:
            users = [{
                'name': name,
                'level': info['level'],
                'rebirth': info['rebirth'],
                'map': info['map']
            } for name, info in online_cache.items()]
        return jsonify(users)
    except ImportError:
        return jsonify([])

@chat_bp.route('/messages', methods=['GET'])
def get_messages():
    msg_type = request.args.get('type', 'system')
    target = request.args.get('target')
    since_id = request.args.get('since_id', type=int, default=0)
    limit = request.args.get('limit', type=int, default=20)
    current_user = request.args.get('current_user')
    conn = None
    try:
        conn = get_chat_db()
        conn.row_factory = sqlite3.Row
        c = conn.cursor()
        if msg_type == 'public':
            time_window = time.time() - 300
            c.execute('''
                SELECT id, sender, content, timestamp FROM chat_messages
                WHERE type='public' AND timestamp > ? AND id > ?
                ORDER BY id ASC LIMIT ?
            ''', (time_window, since_id, limit))
        else:
            if not target or not current_user:
                return jsonify({'error': '缺少目标用户或当前用户'}), 400
            c.execute('''
                SELECT id, sender, target, content, timestamp FROM chat_messages
                WHERE type='private' AND (
                    (sender=? AND target=?) OR (sender=? AND target=?)
                ) AND id > ? ORDER BY id ASC LIMIT ?
            ''', (current_user, target, target, current_user, since_id, limit))
        rows = c.fetchall()
        messages = [dict(row) for row in rows]
        return jsonify(messages)
    except Exception as e:
        logger.error(f"获取消息失败: {e}")
        return jsonify({'error': str(e)}), 500
    finally:
        if conn:
            close_chat_connection(conn)

@chat_bp.route('/messages', methods=['POST'])
def send_message():
    data = request.json
    sender = data.get('sender')
    content = data.get('content')
    msg_type = data.get('type', 'public')
    target = data.get('target') if msg_type == 'private' else None
    if not sender or not content:
        return jsonify({'error': '缺少参数'}), 400
    if len(content) > 200:
        content = content[:200]
    conn = None
    try:
        conn = get_chat_db()
        c = conn.cursor()
        timestamp = time.time()
        c.execute(
            'INSERT INTO chat_messages (sender, target, content, type, timestamp) VALUES (?, ?, ?, ?, ?)',
            (sender, target, content, msg_type, timestamp)
        )
        conn.commit()
        return jsonify({'status': 'ok', 'timestamp': timestamp, 'id': c.lastrowid})
    except Exception as e:
        logger.error(f"发送消息失败: {e}")
        return jsonify({'error': str(e)}), 500
    finally:
        if conn:
            close_chat_connection(conn)

@chat_bp.route('/board', methods=['GET'])
def get_board():
    since_id = request.args.get('since_id', type=int, default=0)
    conn = None
    try:
        conn = get_chat_db()
        conn.row_factory = sqlite3.Row
        c = conn.cursor()
        if since_id == 0:
            c.execute('SELECT id, sender, content, timestamp FROM board_messages ORDER BY id DESC LIMIT 50')
            rows = c.fetchall()
            messages = [dict(row) for row in rows][::-1]
        else:
            c.execute('SELECT id, sender, content, timestamp FROM board_messages WHERE id > ? ORDER BY id ASC LIMIT 50', (since_id,))
            rows = c.fetchall()
            messages = [dict(row) for row in rows]
        return jsonify(messages)
    except Exception as e:
        logger.error(f"获取留言板失败: {e}")
        return jsonify({'error': str(e)}), 500
    finally:
        if conn:
            close_chat_connection(conn)

@chat_bp.route('/board', methods=['POST'])
def post_board():
    data = request.json
    sender = data.get('sender')
    content = data.get('content')
    if not sender or not content:
        return jsonify({'error': '缺少参数'}), 400
    if len(content) > 200:
        content = content[:200]
    result = save_board_message(sender, content)
    if result['success']:
        return jsonify({'status': 'ok', 'timestamp': result['timestamp'], 'id': result['id']})
    else:
        return jsonify({'error': result.get('error', '发送失败')}), 500

@chat_bp.route('/rankings', methods=['GET'])
def get_rankings():
    with rankings_cache_lock:
        return jsonify(rankings_cache)

def add_system_message(content, msg_type='system', target=None):
    global socketio
    # 1. 立即构造消息对象（使用临时负数ID，避免与真实ID冲突）
    import time
    temp_id = -int(time.time() * 1_000_000)
    timestamp = time.time()
    message = {
        'id': temp_id,
        'sender': '【系统】',
        'content': content,
        'type': msg_type,
        'timestamp': timestamp
    }
    if msg_type == 'private':
        message['target'] = target

    # 2. 立即广播（不依赖数据库）
    if socketio is not None:
        try:
            if msg_type == 'public':
                socketio.emit('chat_message', message)
            elif msg_type == 'private':
                socketio.emit('chat_message', message, room=target)
            else:
                socketio.emit('chat_message', message)
        except Exception as e:
            logger.exception(f"广播系统消息失败: {e}")

    # 3. 异步保存到数据库（不影响广播）
    def save_to_db():
        conn = None
        try:
            conn = get_chat_db()
            c = conn.cursor()
            c.execute(
                'INSERT INTO chat_messages (sender, target, content, type, timestamp) VALUES (?, ?, ?, ?, ?)',
                ('【系统】', target, content, msg_type, timestamp)
            )
            conn.commit()
            # 可选：将真实ID推送给客户端，替换临时ID（非必须，客户端可忽略）
        except Exception as e:
            logger.exception(f"系统消息入库失败: {e}")
        finally:
            if conn:
                close_chat_connection(conn)

    threading.Thread(target=save_to_db, daemon=True).start()

def save_chat_message(sender, content, msg_type='public', target=None):
    if len(content) > 200:
        content = content[:200]
    conn = None
    try:
        conn = get_chat_db()
        c = conn.cursor()
        timestamp = time.time()
        c.execute(
            'INSERT INTO chat_messages (sender, target, content, type, timestamp) VALUES (?, ?, ?, ?, ?)',
            (sender, target, content, msg_type, timestamp)
        )
        conn.commit()
        return {'success': True, 'id': c.lastrowid, 'timestamp': timestamp}
    except Exception as e:
        logger.error(f"保存消息失败: {e}")
        return {'success': False, 'error': str(e)}
    finally:
        if conn:
            close_chat_connection(conn)

def save_board_message(sender, content):
    if len(content) > 200:
        content = content[:200]
    conn = None
    try:
        conn = get_chat_db()
        c = conn.cursor()
        timestamp = time.time()
        c.execute(
            'INSERT INTO board_messages (sender, content, timestamp) VALUES (?, ?, ?)',
            (sender, content, timestamp)
        )
        conn.commit()
        message = {'id': c.lastrowid, 'sender': sender, 'content': content, 'timestamp': timestamp}
        socketio = current_app.extensions.get('socketio')
        if socketio:
            socketio.emit('board_message', message)
        return {'success': True, 'id': c.lastrowid, 'timestamp': timestamp}
    except Exception as e:
        logger.error(f"保存留言板失败: {e}")
        return {'success': False, 'error': str(e)}
    finally:
        if conn:
            close_chat_connection(conn)

def get_private_messages(current_user, target, since_id=0):
    conn = None
    try:
        conn = get_chat_db()
        conn.row_factory = sqlite3.Row
        c = conn.cursor()
        c.execute('''
            SELECT id, sender, target, content, timestamp FROM chat_messages
            WHERE type='private' AND (
                (sender=? AND target=?) OR (sender=? AND target=?)
            ) AND id > ? ORDER BY id ASC LIMIT 50
        ''', (current_user, target, target, current_user, since_id))
        rows = c.fetchall()
        return [dict(row) for row in rows]
    except Exception as e:
        logger.error(f"获取私聊历史失败: {e}")
        return []
    finally:
        if conn:
            close_chat_connection(conn)

def get_board_messages(limit=50):
    conn = None
    try:
        conn = get_chat_db()
        conn.row_factory = sqlite3.Row
        c = conn.cursor()
        c.execute('SELECT id, sender, content, timestamp FROM board_messages ORDER BY id DESC LIMIT ?', (limit,))
        rows = c.fetchall()
        return [dict(row) for row in rows]
    except Exception as e:
        logger.error(f"获取留言板失败: {e}")
        return []
    finally:
        if conn:
            close_chat_connection(conn)
            
def get_public_messages(limit=50):
    conn = None
    try:
        conn = get_chat_db()
        conn.row_factory = sqlite3.Row
        c = conn.cursor()
        c.execute('''
            SELECT id, sender, content, type, timestamp
            FROM chat_messages
            WHERE type = 'public'
            ORDER BY id DESC LIMIT ?
        ''', (limit,))
        rows = c.fetchall()
        return [dict(row) for row in reversed(rows)]
    except Exception as e:
        logger.error(f"获取公共消息失败: {e}")
        return []
    finally:
        if conn:
            close_chat_connection(conn)