"""
统一数据访问层 - 集中管理所有数据库连接

功能：
1. 统一数据库路径配置（按环境分离：prod/dev）
2. 连接池管理（上下文管理器）
3. WAL 模式 + 外键约束
4. 自动事务管理（commit/rollback）
5. 初始化所有表结构

数据库分离：
- feedback.db: 反馈系统（生产模式也使用）
- knowledge.db: 知识管理（生产模式也使用）
- session.db: 会话管理（仅开发模式）
- exam.db: 出题系统（仅开发模式）

使用方式：
    from data.db import get_connection, init_databases

    # 初始化数据库（首次运行时调用）
    init_databases()

    # 使用连接
    with get_connection("feedback") as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM feedbacks WHERE user_id = ?", (user_id,))
        rows = cursor.fetchall()
"""

import sqlite3
import os
import json
import logging
import threading
from contextlib import contextmanager
from typing import Generator, Dict, List, Optional

logger = logging.getLogger(__name__)

# ==================== 路径配置 ====================

DATA_DIR = os.path.dirname(os.path.abspath(__file__))

DB_PATHS = {
    # 生产模式数据库（开发模式也使用）
    "feedback":  os.path.join(DATA_DIR, "prod", "feedback.db"),
    "knowledge": os.path.join(DATA_DIR, "prod", "knowledge.db"),

    # 仅开发模式数据库
    "session":   os.path.join(DATA_DIR, "dev", "session.db"),
    "exam":      os.path.join(DATA_DIR, "dev", "exam.db"),
}

# 数据库名称映射（用于日志）
DB_NAMES = {
    "feedback":  "反馈系统数据库（反馈、FAQ、质量报告）",
    "knowledge": "知识管理数据库（同步、大纲、版本）",
    "session":   "会话管理数据库（会话、消息、审计日志）",
    "exam":      "出题系统数据库（题库、试卷、批卷）",
}

_init_lock = threading.Lock()
_initialized = False


# ==================== 连接管理 ====================

@contextmanager
def get_connection(db_name: str, row_factory: bool = True) -> Generator[sqlite3.Connection, None, None]:
    """
    获取数据库连接（上下文管理器）

    Args:
        db_name: 数据库名称 ("feedback", "knowledge", "session", "exam")
        row_factory: 是否启用行工厂（返回字典格式）

    Yields:
        sqlite3.Connection: 数据库连接

    Example:
        with get_connection("feedback") as conn:
            cursor = conn.cursor()
            cursor.execute("SELECT * FROM feedbacks")
            rows = cursor.fetchall()
    """
    if db_name not in DB_PATHS:
        raise ValueError(f"未知的数据库: {db_name}，可用: {list(DB_PATHS.keys())}")

    db_path = DB_PATHS[db_name]

    # fresh clone 中 prod/dev 目录尚不存在；连接前统一创建父目录。
    os.makedirs(os.path.dirname(db_path), exist_ok=True)

    # 关键：check_same_thread=False 支持多worker
    conn = sqlite3.connect(db_path, timeout=30, check_same_thread=False)

    # 启用 WAL 模式（提升并发性能）
    conn.execute("PRAGMA journal_mode=WAL")

    # 设置繁忙超时（5秒）
    conn.execute("PRAGMA busy_timeout=5000")

    # 启用外键约束
    conn.execute("PRAGMA foreign_keys=ON")

    # 设置行工厂（返回字典格式）
    if row_factory:
        conn.row_factory = sqlite3.Row

    try:
        yield conn
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()


def get_raw_connection(db_name: str) -> sqlite3.Connection:
    """
    获取原始数据库连接（不使用上下文管理器）

    注意：调用者需要手动关闭连接

    Args:
        db_name: 数据库名称

    Returns:
        sqlite3.Connection: 数据库连接
    """
    if db_name not in DB_PATHS:
        raise ValueError(f"未知的数据库: {db_name}")

    db_path = DB_PATHS[db_name]
    os.makedirs(os.path.dirname(db_path), exist_ok=True)
    conn = sqlite3.connect(db_path, timeout=30, check_same_thread=False)
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA busy_timeout=5000")
    conn.execute("PRAGMA foreign_keys=ON")
    conn.row_factory = sqlite3.Row
    return conn


# ==================== 数据库初始化 ====================

def init_databases():
    """
    初始化所有数据库表结构

    按环境分离为：
    - 生产模式数据库（开发模式也使用）：feedback.db, knowledge.db
    - 仅开发模式数据库：session.db, exam.db
    """
    global _initialized
    if _initialized:
        return

    from config import IS_DEV

    with _init_lock:
        if _initialized:
            return

        # 始终初始化生产模式数据库
        _init_feedback_db()
        _init_knowledge_db()

        # 仅开发模式初始化会话和出题数据库
        if IS_DEV:
            _init_session_db()
            _init_exam_db()
            logger.info("所有数据库初始化完成（开发模式）")
        else:
            logger.info("生产模式数据库初始化完成")

        _initialized = True


def _init_feedback_db():
    """初始化 feedback.db（反馈系统数据库 - 生产模式也使用）"""
    with get_connection("feedback", row_factory=False) as conn:
        cursor = conn.cursor()

        # ========== 反馈表 ==========
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS feedbacks (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                session_id TEXT NOT NULL,
                query TEXT NOT NULL,
                answer TEXT,
                sources TEXT,
                rating INTEGER NOT NULL,
                reason TEXT,
                user_id TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')

        cursor.execute('''
            CREATE INDEX IF NOT EXISTS idx_feedback_session
            ON feedbacks(session_id)
        ''')

        cursor.execute('''
            CREATE INDEX IF NOT EXISTS idx_feedback_rating
            ON feedbacks(rating)
        ''')

        cursor.execute('''
            CREATE INDEX IF NOT EXISTS idx_feedback_created
            ON feedbacks(created_at)
        ''')

        # ========== FAQ 表 ==========
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS faqs (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                question TEXT NOT NULL,
                answer TEXT NOT NULL,
                source_documents TEXT,
                frequency INTEGER DEFAULT 1,
                avg_rating REAL DEFAULT 0,
                status TEXT DEFAULT 'draft',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')

        cursor.execute('''
            CREATE INDEX IF NOT EXISTS idx_faq_status
            ON faqs(status)
        ''')

        # ========== FAQ 问题变体表（Multi-Query Indexing）==========
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS faq_variants (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                faq_id INTEGER NOT NULL,
                variant_question TEXT NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (faq_id) REFERENCES faqs(id) ON DELETE CASCADE
            )
        ''')

        cursor.execute('''
            CREATE INDEX IF NOT EXISTS idx_faq_variant_faq
            ON faq_variants(faq_id)
        ''')

        # ========== 质量报告表 ==========
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS quality_reports (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                report_type TEXT NOT NULL,
                start_date DATE NOT NULL,
                end_date DATE NOT NULL,
                total_queries INTEGER DEFAULT 0,
                total_feedback INTEGER DEFAULT 0,
                positive_count INTEGER DEFAULT 0,
                negative_count INTEGER DEFAULT 0,
                avg_rating REAL DEFAULT 0,
                satisfaction_rate REAL DEFAULT 0,
                high_freq_queries TEXT,
                low_rating_queries TEXT,
                improvement_suggestions TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')

        # ========== FAQ 建议表 ==========
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS faq_suggestions (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                query TEXT NOT NULL,
                answer TEXT,
                frequency INTEGER DEFAULT 1,
                avg_rating REAL DEFAULT 0,
                status TEXT DEFAULT 'pending',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')

        cursor.execute('''
            CREATE INDEX IF NOT EXISTS idx_faq_suggestion_status
            ON faq_suggestions(status)
        ''')

    logger.info(f"初始化数据库: {DB_PATHS['feedback']} (feedback.db)")


def _init_session_db():
    """初始化 session.db（会话管理数据库 - 仅开发模式使用）"""
    with get_connection("session", row_factory=False) as conn:
        cursor = conn.cursor()

        # ========== 会话表 ==========
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS sessions (
                session_id TEXT PRIMARY KEY,
                user_id TEXT NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                last_active TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                metadata TEXT
            )
        ''')

        cursor.execute('''
            CREATE INDEX IF NOT EXISTS idx_sessions_user
            ON sessions(user_id)
        ''')

        # ========== 消息表 ==========
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS messages (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                session_id TEXT NOT NULL,
                role TEXT NOT NULL,
                content TEXT NOT NULL,
                metadata TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (session_id) REFERENCES sessions(session_id) ON DELETE CASCADE
            )
        ''')

        cursor.execute('''
            CREATE INDEX IF NOT EXISTS idx_messages_session
            ON messages(session_id, created_at)
        ''')

        # ========== 审计日志表 ==========
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS audit_logs (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id TEXT NOT NULL,
                username TEXT DEFAULT '',
                action TEXT NOT NULL,
                query TEXT,
                result_summary TEXT,
                sources TEXT,
                role TEXT,
                department TEXT,
                ip_address TEXT,
                duration_ms INTEGER,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')

        cursor.execute('''
            CREATE INDEX IF NOT EXISTS idx_audit_user
            ON audit_logs(user_id, created_at)
        ''')

        cursor.execute('''
            CREATE INDEX IF NOT EXISTS idx_audit_action
            ON audit_logs(action, created_at)
        ''')

        cursor.execute('''
            CREATE INDEX IF NOT EXISTS idx_audit_created
            ON audit_logs(created_at)
        ''')

    logger.info(f"初始化数据库: {DB_PATHS['session']} (session.db)")


def _init_knowledge_db():
    """初始化 knowledge.db（知识管理数据库）"""
    with get_connection("knowledge", row_factory=False) as conn:
        cursor = conn.cursor()

        # ========== 文档哈希表 ==========
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS document_hashes (
                document_id TEXT PRIMARY KEY,
                document_name TEXT,
                content_hash TEXT,
                file_size INTEGER,
                last_modified TIMESTAMP,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')

        # ========== 变更日志表 ==========
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS change_logs (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                document_id TEXT,
                document_name TEXT,
                change_type TEXT,
                old_hash TEXT,
                new_hash TEXT,
                change_time TIMESTAMP,
                processed INTEGER DEFAULT 0,
                error_message TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')

        cursor.execute('''
            CREATE INDEX IF NOT EXISTS idx_change_logs_time
            ON change_logs(change_time)
        ''')

        cursor.execute('''
            CREATE INDEX IF NOT EXISTS idx_change_logs_processed
            ON change_logs(processed)
        ''')

        # ========== 同步状态表 ==========
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS sync_status (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                sync_type TEXT,
                status TEXT,
                start_time TIMESTAMP,
                end_time TIMESTAMP,
                documents_processed INTEGER,
                documents_added INTEGER,
                documents_modified INTEGER,
                documents_deleted INTEGER,
                error_message TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')

        # ========== 纲要缓存表 ==========
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS outline_cache (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                document_id TEXT NOT NULL UNIQUE,
                document_name TEXT,
                total_pages INTEGER DEFAULT 0,
                content_hash TEXT NOT NULL,
                outline_json TEXT NOT NULL,
                generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')

        cursor.execute('''
            CREATE INDEX IF NOT EXISTS idx_outline_doc
            ON outline_cache(document_id)
        ''')

        # ========== 文档向量缓存表 ==========
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS document_vectors (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                document_id TEXT NOT NULL UNIQUE,
                document_name TEXT,
                vector_hash TEXT,
                vector_json TEXT NOT NULL,
                tags_json TEXT,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')

        cursor.execute('''
            CREATE INDEX IF NOT EXISTS idx_vector_doc
            ON document_vectors(document_id)
        ''')

        # ========== 推荐缓存表 ==========
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS recommendation_cache (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                document_id TEXT NOT NULL,
                recommendations_json TEXT NOT NULL,
                generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')

        # ========== 文档版本表（统一结构） ==========
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS document_versions (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                document_id TEXT NOT NULL,
                collection TEXT,
                version TEXT NOT NULL DEFAULT 'v1',
                content_hash TEXT,
                status TEXT NOT NULL DEFAULT 'active',
                effective_date DATE,
                expiry_date DATE,
                deprecated_date DATETIME,
                deprecated_reason TEXT,
                deprecated_by TEXT,
                change_summary TEXT,
                changed_sections TEXT,
                supersedes TEXT,
                chunk_count INTEGER DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                created_by TEXT,
                UNIQUE(document_id, collection, version)
            )
        ''')

        # ========== 版本变更日志表 ==========
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS version_change_logs (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                document_id TEXT NOT NULL,
                collection TEXT,
                old_version TEXT,
                new_version TEXT,
                old_status TEXT,
                new_status TEXT,
                change_type TEXT NOT NULL,
                reason TEXT,
                changed_by TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')

        # 添加性能优化索引
        cursor.execute('''
            CREATE INDEX IF NOT EXISTS idx_document_versions_status
            ON document_versions(document_id, collection, status)
        ''')

        cursor.execute('''
            CREATE INDEX IF NOT EXISTS idx_version_change_logs_document
            ON version_change_logs(document_id, collection, created_at DESC)
        ''')

    logger.info(f"初始化数据库: {DB_PATHS['knowledge']} (knowledge.db)")


def _init_exam_db():
    """初始化 exam.db（出题系统数据库）"""
    with get_connection("exam", row_factory=False) as conn:
        cursor = conn.cursor()

        # ========== 题目表 ==========
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS questions (
                id TEXT PRIMARY KEY,
                question_type TEXT NOT NULL,
                content TEXT NOT NULL,
                options TEXT,
                correct_answer TEXT NOT NULL,
                analysis TEXT,
                knowledge_points TEXT,
                difficulty INTEGER DEFAULT 3,
                score INTEGER NOT NULL,
                source_file TEXT NOT NULL,
                source_collection TEXT NOT NULL,
                source_snippet TEXT,
                source_hash TEXT,
                status TEXT DEFAULT 'approved',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                created_by TEXT,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')

        cursor.execute('''
            CREATE INDEX IF NOT EXISTS idx_questions_source
            ON questions(source_file)
        ''')

        # ========== 试卷表 ==========
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS exams (
                id TEXT PRIMARY KEY,
                name TEXT NOT NULL,
                description TEXT,
                total_score INTEGER NOT NULL,
                total_count INTEGER NOT NULL,
                duration INTEGER DEFAULT 60,
                status TEXT DEFAULT 'published',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                created_by TEXT
            )
        ''')

        # ========== 试卷题目关联表 ==========
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS exam_questions (
                exam_id TEXT NOT NULL,
                question_id TEXT NOT NULL,
                question_order INTEGER NOT NULL,
                PRIMARY KEY (exam_id, question_id),
                FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE,
                FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE
            )
        ''')

        # ========== 学生答卷表（只存 student_id） ==========
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS student_answers (
                id TEXT PRIMARY KEY,
                exam_id TEXT NOT NULL,
                student_id TEXT NOT NULL,
                question_id TEXT NOT NULL,
                question_type TEXT NOT NULL,
                student_answer TEXT NOT NULL,
                score REAL DEFAULT 0,
                max_score INTEGER NOT NULL,
                feedback TEXT,
                score_details TEXT,
                submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                graded_at TIMESTAMP,
                FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE,
                FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE
            )
        ''')

        cursor.execute('''
            CREATE INDEX IF NOT EXISTS idx_student_answers_exam
            ON student_answers(exam_id, student_id)
        ''')

        # ========== 批阅报告表（只存 student_id） ==========
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS grade_reports (
                id TEXT PRIMARY KEY,
                exam_id TEXT NOT NULL,
                student_id TEXT NOT NULL,
                total_score REAL NOT NULL,
                max_score REAL NOT NULL,
                score_rate REAL,
                analysis TEXT,
                graded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE
            )
        ''')

        # ========== 题目-制度关联表 ==========
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS question_document_links (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                question_id TEXT NOT NULL,
                question_type TEXT NOT NULL,
                exam_id TEXT NOT NULL,
                document_id TEXT NOT NULL,
                document_name TEXT,
                chapter TEXT,
                key_points TEXT,
                relevance_score REAL DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')

        cursor.execute('''
            CREATE INDEX IF NOT EXISTS idx_qdl_question
            ON question_document_links(question_id)
        ''')

        cursor.execute('''
            CREATE INDEX IF NOT EXISTS idx_qdl_document
            ON question_document_links(document_id)
        ''')

        # ========== 知识点表 ==========
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS knowledge_points (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL UNIQUE,
                category TEXT,
                description TEXT,
                parent_id INTEGER,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (parent_id) REFERENCES knowledge_points(id)
            )
        ''')

        # ========== 题目-知识点关联表 ==========
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS question_knowledge_links (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                question_id TEXT NOT NULL,
                question_type TEXT NOT NULL,
                exam_id TEXT NOT NULL,
                knowledge_point_id INTEGER NOT NULL,
                knowledge_point_name TEXT,
                weight REAL DEFAULT 1.0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (knowledge_point_id) REFERENCES knowledge_points(id)
            )
        ''')

        cursor.execute('''
            CREATE INDEX IF NOT EXISTS idx_qkl_question
            ON question_knowledge_links(question_id)
        ''')

        cursor.execute('''
            CREATE INDEX IF NOT EXISTS idx_qkl_knowledge
            ON question_knowledge_links(knowledge_point_id)
        ''')

        # ========== 题目状态表 ==========
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS question_status (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                question_id TEXT NOT NULL UNIQUE,
                question_type TEXT NOT NULL,
                exam_id TEXT NOT NULL,
                status TEXT DEFAULT 'approved',
                affected_by TEXT,
                affect_reason TEXT,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')

        cursor.execute('''
            CREATE INDEX IF NOT EXISTS idx_qs_status
            ON question_status(status)
        ''')

        # ========== 整卷分析报告表 ==========
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS exam_analysis_reports (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                report_id TEXT NOT NULL UNIQUE,
                exam_id TEXT,
                exam_name TEXT,
                student_id TEXT,
                total_score REAL,
                max_score REAL,
                score_rate REAL,
                type_scores TEXT,
                knowledge_analysis TEXT,
                weak_points TEXT,
                strong_points TEXT,
                ai_comment TEXT,
                study_suggestions TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')

        # ========== 新题建议表 ==========
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS question_suggestions (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                document_id TEXT NOT NULL,
                suggestion TEXT,
                status TEXT DEFAULT 'pending',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')

    logger.info(f"初始化数据库: {DB_PATHS['exam']} (exam.db)")


# ==================== 工具函数 ====================

def row_to_dict(row: sqlite3.Row) -> Dict:
    """将 sqlite3.Row 转换为字典"""
    if row is None:
        return {}
    return dict(row)


def rows_to_list(rows: List[sqlite3.Row]) -> List[Dict]:
    """将 sqlite3.Row 列表转换为字典列表"""
    return [row_to_dict(row) for row in rows]


def json_parse(value: str, default=None):
    """安全解析 JSON 字符串"""
    if not value:
        return default
    try:
        return json.loads(value)
    except (json.JSONDecodeError, TypeError):
        return default


def json_stringify(value, ensure_ascii: bool = False) -> str:
    """将对象转换为 JSON 字符串"""
    if value is None:
        return None
    return json.dumps(value, ensure_ascii=ensure_ascii)


def get_db_stats() -> Dict:
    """获取所有数据库的统计信息"""
    stats = {}

    for db_name, db_path in DB_PATHS.items():
        if os.path.exists(db_path):
            file_size = os.path.getsize(db_path)

            with get_connection(db_name, row_factory=False) as conn:
                cursor = conn.cursor()

                # 获取所有表名
                cursor.execute(
                    "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"
                )
                tables = [row[0] for row in cursor.fetchall()]

                # 统计每个表的行数
                table_counts = {}
                for table in tables:
                    cursor.execute(f"SELECT COUNT(*) FROM {table}")
                    table_counts[table] = cursor.fetchone()[0]

            stats[db_name] = {
                "path": db_path,
                "name": DB_NAMES.get(db_name, db_name),
                "file_size": file_size,
                "tables": table_counts,
                "total_rows": sum(table_counts.values())
            }
        else:
            stats[db_name] = {
                "path": db_path,
                "name": DB_NAMES.get(db_name, db_name),
                "exists": False
            }

    return stats


# ==================== 自动初始化 ====================

# 模块加载时不自动初始化，由调用方决定何时初始化
# 这样可以避免在 import 时创建数据库文件