/**
 * 自然写互动课堂PC端应用软件 V1.0
 * 数据库管理模块 - 基于better-sqlite3实现SQLite本地数据持久化
 * 
 * 功能说明：
 * 1. 数据库初始化与版本迁移（Schema Migration）
 * 2. 学生笔迹数据的存储与检索（支持按学生/作业/时间维度查询）
 * 3. 作业批改记录管理（AI批改 + 人工标注）
 * 4. 班级/学生信息本地缓存（减少网络请求）
 * 5. 点阵码映射关系维护（课件页面与点阵码对应）
 * 6. 课件元数据索引（本地课件文件的管理信息）
 * 7. 数据库文件加密（SQLCipher集成，防止本地数据泄露）
 * 8. 自动备份与数据清理策略
 */

import path from 'path';
import fs from 'fs';
import { app } from 'electron';
import crypto from 'crypto';

/* ========== 类型定义 ========== */

/** 数据库配置接口 */
interface DatabaseConfig {
    dbPath: string;             // 数据库文件路径
    encryptionKey: string;      // 加密密钥（SQLCipher）
    maxBackups: number;         // 最大备份数量
    autoVacuumInterval: number; // 自动整理间隔（毫秒）
    walMode: boolean;           // 是否启用WAL模式
}

/** 学生笔迹记录 */
interface StrokeRecord {
    id: string;
    studentId: string;
    studentName: string;
    assignmentId: string;
    pageIndex: number;
    strokeData: string;        // JSON序列化的笔迹坐标数据
    thumbnailPath: string;     // 缩略图文件路径
    collectTime: number;       // 采集时间戳
    syncStatus: number;        // 同步状态: 0=未同步, 1=已同步, 2=同步失败
    fileSize: number;          // 数据大小（字节）
}

/** 批改记录 */
interface GradeRecord {
    id: string;
    assignmentId: string;
    studentId: string;
    aiScore: number;           // AI评分（0-100）
    teacherScore: number;      // 教师评分（-1表示未批改）
    aiAnnotation: string;      // AI批改标注JSON
    teacherAnnotation: string; // 教师手动标注JSON
    gradeTime: number;
    status: number;            // 0=待批改, 1=AI已批, 2=教师已批
}

/** 班级信息 */
interface ClassInfo {
    classId: string;
    className: string;
    grade: string;
    teacherId: string;
    studentCount: number;
    lastSyncTime: number;
}

/** 学生信息 */
interface StudentInfo {
    studentId: string;
    studentName: string;
    classId: string;
    seatNumber: number;
    penDeviceId: string;       // 绑定的点阵笔设备ID
    avatarPath: string;
}

/** 点阵码映射 */
interface DotCodeMapping {
    dotCodeId: string;         // 点阵码唯一标识
    coursewareId: string;      // 课件ID
    pageIndex: number;         // 对应页面索引
    regionType: string;        // 区域类型: 'answer'/'writing'/'drawing'
    coordinates: string;       // 区域坐标JSON
}

/** 课件元数据 */
interface CoursewareMeta {
    coursewareId: string;
    title: string;
    type: string;              // 'ppt'/'pdf'/'custom'
    filePath: string;          // 本地文件路径
    pageCount: number;
    fileSize: number;
    createTime: number;
    lastOpenTime: number;
    cloudUrl: string;          // 云端地址
    syncStatus: number;
}

/** 迁移脚本定义 */
interface Migration {
    version: number;
    description: string;
    sql: string;
}

/* ========== 数据库管理器 ========== */

// 数据库Schema版本号，每次表结构变更递增
const CURRENT_SCHEMA_VERSION = 5;

/**
 * 数据库管理器 - 统一管理SQLite数据库的生命周期
 * 采用单例模式确保全局唯一数据库连接
 */
class DatabaseManager {
    private db: any = null;                   // better-sqlite3 数据库实例
    private config: DatabaseConfig;            // 数据库配置
    private backupTimer: ReturnType<typeof setInterval> | null = null;
    private vacuumTimer: ReturnType<typeof setInterval> | null = null;
    private initialized: boolean = false;

    constructor() {
        // 默认配置：数据库存储在应用数据目录
        const userDataPath = app.getPath('userData');
        this.config = {
            dbPath: path.join(userDataPath, 'writech_data.db'),
            encryptionKey: this.loadOrCreateEncryptionKey(),
            maxBackups: 5,
            autoVacuumInterval: 24 * 60 * 60 * 1000, // 每24小时整理一次
            walMode: true
        };
    }

    /**
     * 加载或创建数据库加密密钥
     * 密钥存储在操作系统安全凭据管理器中（通过keytar）
     * 首次运行时生成随机256位密钥
     */
    private loadOrCreateEncryptionKey(): string {
        const keyFilePath = path.join(app.getPath('userData'), '.db_key');
        try {
            if (fs.existsSync(keyFilePath)) {
                return fs.readFileSync(keyFilePath, 'utf-8').trim();
            }
            // 生成256位随机密钥并保存
            const newKey = crypto.randomBytes(32).toString('hex');
            fs.writeFileSync(keyFilePath, newKey, { mode: 0o600 });
            console.log('[DatabaseManager] 已生成新的数据库加密密钥');
            return newKey;
        } catch (error) {
            console.error('[DatabaseManager] 密钥管理失败，使用默认密钥:', error);
            return 'writech_default_key_2024';
        }
    }

    /**
     * 初始化数据库连接并执行迁移
     * 启用WAL模式提高并发读写性能
     * 设置SQLCipher加密密钥
     */
    async initialize(): Promise<void> {
        if (this.initialized) return;

        try {
            const Database = require('better-sqlite3');
            const dbDir = path.dirname(this.config.dbPath);
            if (!fs.existsSync(dbDir)) {
                fs.mkdirSync(dbDir, { recursive: true });
            }

            // 创建数据库连接（启用verbose日志用于调试）
            this.db = new Database(this.config.dbPath, { verbose: undefined });

            // 设置SQLCipher加密密钥
            this.db.pragma(`key='${this.config.encryptionKey}'`);

            // 启用WAL模式提高并发性能
            if (this.config.walMode) {
                this.db.pragma('journal_mode=WAL');
                this.db.pragma('synchronous=NORMAL');
            }

            // 启用外键约束
            this.db.pragma('foreign_keys=ON');

            // 执行数据库迁移
            this.runMigrations();

            // 启动定时任务（备份 + 整理）
            this.startAutoBackup();
            this.startAutoVacuum();

            this.initialized = true;
            console.log('[DatabaseManager] 数据库初始化完成，版本:', CURRENT_SCHEMA_VERSION);
        } catch (error) {
            console.error('[DatabaseManager] 数据库初始化失败:', error);
            throw error;
        }
    }

    /**
     * 获取所有迁移脚本列表
     * 每个版本对应一个迁移脚本，按版本号顺序执行
     */
    private getMigrations(): Migration[] {
        return [
            {
                version: 1,
                description: '创建基础表结构',
                sql: `
                    -- 学生笔迹数据表
                    CREATE TABLE IF NOT EXISTS stroke_records (
                        id TEXT PRIMARY KEY,
                        student_id TEXT NOT NULL,
                        student_name TEXT NOT NULL,
                        assignment_id TEXT NOT NULL,
                        page_index INTEGER DEFAULT 0,
                        stroke_data TEXT NOT NULL,
                        thumbnail_path TEXT DEFAULT '',
                        collect_time INTEGER NOT NULL,
                        sync_status INTEGER DEFAULT 0,
                        file_size INTEGER DEFAULT 0,
                        created_at INTEGER DEFAULT (strftime('%s','now'))
                    );
                    CREATE INDEX IF NOT EXISTS idx_stroke_student ON stroke_records(student_id);
                    CREATE INDEX IF NOT EXISTS idx_stroke_assignment ON stroke_records(assignment_id);
                    CREATE INDEX IF NOT EXISTS idx_stroke_time ON stroke_records(collect_time);

                    -- 批改记录表
                    CREATE TABLE IF NOT EXISTS grade_records (
                        id TEXT PRIMARY KEY,
                        assignment_id TEXT NOT NULL,
                        student_id TEXT NOT NULL,
                        ai_score REAL DEFAULT -1,
                        teacher_score REAL DEFAULT -1,
                        ai_annotation TEXT DEFAULT '{}',
                        teacher_annotation TEXT DEFAULT '{}',
                        grade_time INTEGER NOT NULL,
                        status INTEGER DEFAULT 0,
                        created_at INTEGER DEFAULT (strftime('%s','now'))
                    );
                    CREATE INDEX IF NOT EXISTS idx_grade_assignment ON grade_records(assignment_id);
                    CREATE INDEX IF NOT EXISTS idx_grade_student ON grade_records(student_id);
                `
            },
            {
                version: 2,
                description: '添加班级和学生信息表',
                sql: `
                    -- 班级信息缓存表
                    CREATE TABLE IF NOT EXISTS class_info (
                        class_id TEXT PRIMARY KEY,
                        class_name TEXT NOT NULL,
                        grade TEXT DEFAULT '',
                        teacher_id TEXT NOT NULL,
                        student_count INTEGER DEFAULT 0,
                        last_sync_time INTEGER DEFAULT 0
                    );

                    -- 学生信息缓存表
                    CREATE TABLE IF NOT EXISTS student_info (
                        student_id TEXT PRIMARY KEY,
                        student_name TEXT NOT NULL,
                        class_id TEXT NOT NULL,
                        seat_number INTEGER DEFAULT 0,
                        pen_device_id TEXT DEFAULT '',
                        avatar_path TEXT DEFAULT '',
                        FOREIGN KEY (class_id) REFERENCES class_info(class_id)
                    );
                    CREATE INDEX IF NOT EXISTS idx_student_class ON student_info(class_id);
                    CREATE INDEX IF NOT EXISTS idx_student_pen ON student_info(pen_device_id);
                `
            },
            {
                version: 3,
                description: '添加点阵码映射表',
                sql: `
                    -- 点阵码映射关系表（课件页面与点阵码ID对应）
                    CREATE TABLE IF NOT EXISTS dot_code_mapping (
                        dot_code_id TEXT PRIMARY KEY,
                        courseware_id TEXT NOT NULL,
                        page_index INTEGER NOT NULL,
                        region_type TEXT DEFAULT 'answer',
                        coordinates TEXT DEFAULT '{}',
                        created_at INTEGER DEFAULT (strftime('%s','now'))
                    );
                    CREATE INDEX IF NOT EXISTS idx_dotcode_courseware ON dot_code_mapping(courseware_id);
                `
            },
            {
                version: 4,
                description: '添加课件元数据表',
                sql: `
                    -- 课件元数据索引表
                    CREATE TABLE IF NOT EXISTS courseware_meta (
                        courseware_id TEXT PRIMARY KEY,
                        title TEXT NOT NULL,
                        type TEXT DEFAULT 'custom',
                        file_path TEXT NOT NULL,
                        page_count INTEGER DEFAULT 0,
                        file_size INTEGER DEFAULT 0,
                        create_time INTEGER NOT NULL,
                        last_open_time INTEGER DEFAULT 0,
                        cloud_url TEXT DEFAULT '',
                        sync_status INTEGER DEFAULT 0
                    );
                    CREATE INDEX IF NOT EXISTS idx_courseware_type ON courseware_meta(type);
                    CREATE INDEX IF NOT EXISTS idx_courseware_time ON courseware_meta(last_open_time);
                `
            },
            {
                version: 5,
                description: '添加同步日志表用于离线数据追踪',
                sql: `
                    -- 数据同步日志表（记录所有待同步操作）
                    CREATE TABLE IF NOT EXISTS sync_log (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        table_name TEXT NOT NULL,
                        record_id TEXT NOT NULL,
                        operation TEXT NOT NULL,
                        payload TEXT DEFAULT '{}',
                        sync_status INTEGER DEFAULT 0,
                        retry_count INTEGER DEFAULT 0,
                        created_at INTEGER DEFAULT (strftime('%s','now')),
                        synced_at INTEGER DEFAULT 0
                    );
                    CREATE INDEX IF NOT EXISTS idx_sync_status ON sync_log(sync_status);
                `
            }
        ];
    }

    /**
     * 执行数据库迁移
     * 检查当前版本号，依次执行未执行的迁移脚本
     * 使用事务确保迁移的原子性
     */
    private runMigrations(): void {
        // 创建版本跟踪表
        this.db.exec(`
            CREATE TABLE IF NOT EXISTS schema_version (
                version INTEGER PRIMARY KEY,
                description TEXT,
                applied_at INTEGER DEFAULT (strftime('%s','now'))
            );
        `);

        // 获取当前数据库版本
        const row = this.db.prepare('SELECT MAX(version) as ver FROM schema_version').get();
        const currentVersion = row?.ver || 0;

        if (currentVersion >= CURRENT_SCHEMA_VERSION) {
            console.log('[DatabaseManager] 数据库已是最新版本:', currentVersion);
            return;
        }

        // 获取待执行的迁移脚本并按版本排序执行
        const migrations = this.getMigrations().filter(m => m.version > currentVersion);
        const runAll = this.db.transaction(() => {
            for (const migration of migrations) {
                console.log(`[DatabaseManager] 执行迁移 v${migration.version}: ${migration.description}`);
                this.db.exec(migration.sql);
                this.db.prepare('INSERT INTO schema_version (version, description) VALUES (?, ?)')
                    .run(migration.version, migration.description);
            }
        });

        runAll();
        console.log(`[DatabaseManager] 迁移完成: v${currentVersion} -> v${CURRENT_SCHEMA_VERSION}`);
    }

    /* ========== 笔迹数据操作 ========== */

    /** 保存学生笔迹记录（批量插入，提高写入性能） */
    saveStrokeRecords(records: StrokeRecord[]): number {
        const insertStmt = this.db.prepare(`
            INSERT OR REPLACE INTO stroke_records 
            (id, student_id, student_name, assignment_id, page_index, 
             stroke_data, thumbnail_path, collect_time, sync_status, file_size)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        `);

        // 使用事务批量插入，避免逐条写入导致的性能问题
        const insertMany = this.db.transaction((items: StrokeRecord[]) => {
            let count = 0;
            for (const r of items) {
                insertStmt.run(
                    r.id, r.studentId, r.studentName, r.assignmentId,
                    r.pageIndex, r.strokeData, r.thumbnailPath,
                    r.collectTime, r.syncStatus, r.fileSize
                );
                count++;
            }
            // 同时记录同步日志
            const logStmt = this.db.prepare(`
                INSERT INTO sync_log (table_name, record_id, operation, payload)
                VALUES ('stroke_records', ?, 'INSERT', ?)
            `);
            for (const r of items) {
                logStmt.run(r.id, JSON.stringify({ assignmentId: r.assignmentId }));
            }
            return count;
        });

        return insertMany(records);
    }

    /** 按作业ID查询笔迹（支持分页） */
    getStrokesByAssignment(assignmentId: string, page: number = 0, pageSize: number = 50): StrokeRecord[] {
        const offset = page * pageSize;
        return this.db.prepare(`
            SELECT id, student_id as studentId, student_name as studentName,
                   assignment_id as assignmentId, page_index as pageIndex,
                   stroke_data as strokeData, thumbnail_path as thumbnailPath,
                   collect_time as collectTime, sync_status as syncStatus,
                   file_size as fileSize
            FROM stroke_records
            WHERE assignment_id = ?
            ORDER BY collect_time DESC
            LIMIT ? OFFSET ?
        `).all(assignmentId, pageSize, offset);
    }

    /** 查询某学生的所有笔迹（用于学情分析） */
    getStrokesByStudent(studentId: string, startTime?: number, endTime?: number): StrokeRecord[] {
        let sql = `SELECT * FROM stroke_records WHERE student_id = ?`;
        const params: any[] = [studentId];
        if (startTime) {
            sql += ' AND collect_time >= ?';
            params.push(startTime);
        }
        if (endTime) {
            sql += ' AND collect_time <= ?';
            params.push(endTime);
        }
        sql += ' ORDER BY collect_time DESC';
        return this.db.prepare(sql).all(...params);
    }

    /** 获取未同步的笔迹记录（用于断网重连后批量上传） */
    getUnsyncedStrokes(limit: number = 100): StrokeRecord[] {
        return this.db.prepare(`
            SELECT * FROM stroke_records 
            WHERE sync_status = 0 
            ORDER BY collect_time ASC 
            LIMIT ?
        `).all(limit);
    }

    /** 批量更新笔迹同步状态 */
    updateStrokeSyncStatus(ids: string[], status: number): void {
        const placeholders = ids.map(() => '?').join(',');
        this.db.prepare(`
            UPDATE stroke_records SET sync_status = ? 
            WHERE id IN (${placeholders})
        `).run(status, ...ids);
    }

    /* ========== 批改记录操作 ========== */

    /** 保存或更新批改记录 */
    saveGradeRecord(record: GradeRecord): void {
        this.db.prepare(`
            INSERT OR REPLACE INTO grade_records
            (id, assignment_id, student_id, ai_score, teacher_score,
             ai_annotation, teacher_annotation, grade_time, status)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        `).run(
            record.id, record.assignmentId, record.studentId,
            record.aiScore, record.teacherScore,
            record.aiAnnotation, record.teacherAnnotation,
            record.gradeTime, record.status
        );
    }

    /** 查询作业的批改结果列表 */
    getGradesByAssignment(assignmentId: string): GradeRecord[] {
        return this.db.prepare(`
            SELECT g.*, s.student_name as studentName
            FROM grade_records g
            LEFT JOIN student_info s ON g.student_id = s.student_id
            WHERE g.assignment_id = ?
            ORDER BY g.grade_time DESC
        `).all(assignmentId);
    }

    /** 获取待教师批改的记录数 */
    getPendingGradeCount(): number {
        const row = this.db.prepare(`
            SELECT COUNT(*) as cnt FROM grade_records WHERE status < 2
        `).get();
        return row?.cnt || 0;
    }

    /* ========== 班级/学生信息操作 ========== */

    /** 批量同步班级信息（从云端拉取后缓存到本地） */
    syncClassInfo(classes: ClassInfo[]): void {
        const upsert = this.db.prepare(`
            INSERT OR REPLACE INTO class_info 
            (class_id, class_name, grade, teacher_id, student_count, last_sync_time)
            VALUES (?, ?, ?, ?, ?, ?)
        `);
        const syncAll = this.db.transaction((items: ClassInfo[]) => {
            for (const c of items) {
                upsert.run(c.classId, c.className, c.grade, c.teacherId, c.studentCount, Date.now());
            }
        });
        syncAll(classes);
    }

    /** 批量同步学生信息 */
    syncStudentInfo(students: StudentInfo[]): void {
        const upsert = this.db.prepare(`
            INSERT OR REPLACE INTO student_info
            (student_id, student_name, class_id, seat_number, pen_device_id, avatar_path)
            VALUES (?, ?, ?, ?, ?, ?)
        `);
        const syncAll = this.db.transaction((items: StudentInfo[]) => {
            for (const s of items) {
                upsert.run(s.studentId, s.studentName, s.classId, s.seatNumber, s.penDeviceId, s.avatarPath);
            }
        });
        syncAll(students);
    }

    /** 按班级查询学生列表 */
    getStudentsByClass(classId: string): StudentInfo[] {
        return this.db.prepare(`
            SELECT * FROM student_info WHERE class_id = ? ORDER BY seat_number
        `).all(classId);
    }

    /** 通过点阵笔设备ID查找学生（用于实时笔迹识别） */
    findStudentByPenDevice(penDeviceId: string): StudentInfo | undefined {
        return this.db.prepare(`
            SELECT * FROM student_info WHERE pen_device_id = ?
        `).get(penDeviceId);
    }

    /* ========== 点阵码映射操作 ========== */

    /** 保存点阵码映射关系 */
    saveDotCodeMappings(mappings: DotCodeMapping[]): void {
        const upsert = this.db.prepare(`
            INSERT OR REPLACE INTO dot_code_mapping
            (dot_code_id, courseware_id, page_index, region_type, coordinates)
            VALUES (?, ?, ?, ?, ?)
        `);
        const saveAll = this.db.transaction((items: DotCodeMapping[]) => {
            for (const m of items) {
                upsert.run(m.dotCodeId, m.coursewareId, m.pageIndex, m.regionType, m.coordinates);
            }
        });
        saveAll(mappings);
    }

    /** 根据点阵码ID查找对应的课件页面（笔迹数据落点定位） */
    findPageByDotCode(dotCodeId: string): DotCodeMapping | undefined {
        return this.db.prepare(`
            SELECT * FROM dot_code_mapping WHERE dot_code_id = ?
        `).get(dotCodeId);
    }

    /* ========== 课件元数据操作 ========== */

    /** 保存课件元数据 */
    saveCoursewareMeta(meta: CoursewareMeta): void {
        this.db.prepare(`
            INSERT OR REPLACE INTO courseware_meta
            (courseware_id, title, type, file_path, page_count, file_size,
             create_time, last_open_time, cloud_url, sync_status)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        `).run(
            meta.coursewareId, meta.title, meta.type, meta.filePath,
            meta.pageCount, meta.fileSize, meta.createTime,
            meta.lastOpenTime, meta.cloudUrl, meta.syncStatus
        );
    }

    /** 获取最近打开的课件列表 */
    getRecentCoursewares(limit: number = 20): CoursewareMeta[] {
        return this.db.prepare(`
            SELECT * FROM courseware_meta ORDER BY last_open_time DESC LIMIT ?
        `).all(limit);
    }

    /* ========== 数据库维护操作 ========== */

    /** 启动自动备份定时器（每6小时备份一次） */
    private startAutoBackup(): void {
        const BACKUP_INTERVAL = 6 * 60 * 60 * 1000; // 6小时
        this.backupTimer = setInterval(() => {
            this.createBackup();
        }, BACKUP_INTERVAL);
    }

    /** 创建数据库备份文件 */
    createBackup(): string {
        const backupDir = path.join(path.dirname(this.config.dbPath), 'backups');
        if (!fs.existsSync(backupDir)) {
            fs.mkdirSync(backupDir, { recursive: true });
        }

        // 生成备份文件名（包含时间戳）
        const timestamp = new Date().toISOString().replace(/[:.]/g, '-');
        const backupPath = path.join(backupDir, `writech_backup_${timestamp}.db`);

        // 使用SQLite的backup API执行在线备份（不阻塞读写）
        this.db.backup(backupPath);
        console.log('[DatabaseManager] 数据库备份完成:', backupPath);

        // 清理过期备份（保留最近N个）
        this.cleanOldBackups(backupDir);
        return backupPath;
    }

    /** 清理过期的备份文件 */
    private cleanOldBackups(backupDir: string): void {
        const files = fs.readdirSync(backupDir)
            .filter(f => f.startsWith('writech_backup_'))
            .sort()
            .reverse();

        // 删除超出最大数量的旧备份
        for (let i = this.config.maxBackups; i < files.length; i++) {
            const filePath = path.join(backupDir, files[i]);
            fs.unlinkSync(filePath);
            console.log('[DatabaseManager] 已清理过期备份:', files[i]);
        }
    }

    /** 启动自动数据库整理（VACUUM） */
    private startAutoVacuum(): void {
        this.vacuumTimer = setInterval(() => {
            try {
                // 清理30天前已同步的笔迹原始数据（缩略图保留）
                const threshold = Date.now() - 30 * 24 * 60 * 60 * 1000;
                const result = this.db.prepare(`
                    DELETE FROM stroke_records 
                    WHERE sync_status = 1 AND collect_time < ?
                `).run(threshold);
                if (result.changes > 0) {
                    console.log(`[DatabaseManager] 清理过期笔迹记录: ${result.changes}条`);
                }

                // 清理已同步的同步日志
                this.db.prepare(`
                    DELETE FROM sync_log WHERE sync_status = 1 AND synced_at < ?
                `).run(threshold);

                // 执行VACUUM整理磁盘空间
                this.db.exec('VACUUM');
                console.log('[DatabaseManager] 数据库整理完成');
            } catch (error) {
                console.error('[DatabaseManager] 数据库整理失败:', error);
            }
        }, this.config.autoVacuumInterval);
    }

    /** 获取数据库统计信息（用于状态显示） */
    getStatistics(): Record<string, number> {
        const stats: Record<string, number> = {};
        stats.strokeCount = this.db.prepare('SELECT COUNT(*) as c FROM stroke_records').get().c;
        stats.gradeCount = this.db.prepare('SELECT COUNT(*) as c FROM grade_records').get().c;
        stats.studentCount = this.db.prepare('SELECT COUNT(*) as c FROM student_info').get().c;
        stats.coursewareCount = this.db.prepare('SELECT COUNT(*) as c FROM courseware_meta').get().c;
        stats.unsyncedCount = this.db.prepare('SELECT COUNT(*) as c FROM sync_log WHERE sync_status=0').get().c;

        // 计算数据库文件大小
        try {
            const stat = fs.statSync(this.config.dbPath);
            stats.dbSizeBytes = stat.size;
        } catch {
            stats.dbSizeBytes = 0;
        }
        return stats;
    }

    /** 关闭数据库连接并清理资源 */
    close(): void {
        if (this.backupTimer) {
            clearInterval(this.backupTimer);
            this.backupTimer = null;
        }
        if (this.vacuumTimer) {
            clearInterval(this.vacuumTimer);
            this.vacuumTimer = null;
        }
        if (this.db) {
            // 关闭前执行一次checkpoint确保WAL数据写入
            try { this.db.pragma('wal_checkpoint(TRUNCATE)'); } catch {}
            this.db.close();
            this.db = null;
        }
        this.initialized = false;
        console.log('[DatabaseManager] 数据库连接已关闭');
    }
}

/* ========== 单例导出 ========== */

/** 全局数据库管理器实例 */
const dbManager = new DatabaseManager();
export default dbManager;