/**
 * wechat-reader.js — 从 wechat_plain SQLite 数据库读取微信数据（纯读取，不写图谱）
 *
 * 使用 Node.js 内置 node:sqlite（DatabaseSync），无需额外依赖。
 *
 * 数据库路径：wechat_plain/
 *   - contact_contact.db  → 联系人
 *   - session_session.db  → 会话列表
 *   - message_message_*.db (0~10) → 消息
 */

const { DatabaseSync } = require('node:sqlite');
const crypto = require('crypto');
const path = require('path');
const fs = require('fs');

// wechat_plain 目录：相对于本文件 server/services/wechat/
const WECHAT_DIR = path.resolve(__dirname, '../../../wechat_plain');

function dbPath(name) {
    return path.join(WECHAT_DIR, name);
}

/**
 * 安全打开 SQLite 数据库（只读模式）
 * @param {string} filePath
 * @returns {DatabaseSync|null}
 */
function openDb(filePath) {
    if (!fs.existsSync(filePath)) return null;
    try {
        return new DatabaseSync(filePath, { open: true });
    } catch (e) {
        console.warn(`[WechatReader] 无法打开 ${filePath}:`, e.message);
        return null;
    }
}

/**
 * 计算 md5(username)，用于定位消息表名 Msg_<md5>
 * @param {string} username
 * @returns {string}
 */
function md5(username) {
    return crypto.createHash('md5').update(username).digest('hex');
}

/**
 * 解析 message_content 字段
 * 格式：`wxid_xxx:\n正文` 或 `nickname:\n正文` 或单纯正文
 * @param {string} content
 * @returns {{ senderUsername: string|null, content: string }}
 */
function parseMessageContent(content) {
    if (!content) return { senderUsername: null, content: '' };
    const newlineIdx = content.indexOf('\n');
    if (newlineIdx > 0) {
        const prefix = content.slice(0, newlineIdx).trim();
        const body = content.slice(newlineIdx + 1);
        // 前缀不含空格，判断为 sender 标识符
        if (prefix && !prefix.includes(' ')) {
            return { senderUsername: prefix, content: body };
        }
    }
    return { senderUsername: null, content };
}

/**
 * 每个 DB 文件中 real_sender_id 是独立编号的，
 * 需要按文件各自检测「自己」的 sender_id：
 * 在该文件的所有消息表中，跨表出现次数最多的 sender_id = 自己。
 *
 * 返回 Map<filePath, selfSenderId>，结果缓存。
 */
let _selfSenderIds = null; // Map<filePath, number>

function getSelfSenderIds() {
    if (_selfSenderIds !== null) return _selfSenderIds;

    const result = new Map();

    for (let i = 0; i <= 10; i++) {
        const filePath = dbPath(`message_message_${i}.db`);
        const db = openDb(filePath);
        if (!db) continue;
        try {
            const tables = db.prepare(
                "SELECT name FROM sqlite_master WHERE type='table' AND name LIKE 'Msg_%'"
            ).all().slice(0, 40);

            const freq = {};
            for (const { name } of tables) {
                try {
                    const rows = db.prepare(
                        `SELECT real_sender_id, count(*) c FROM ${name} WHERE local_type=1 GROUP BY real_sender_id`
                    ).all();
                    for (const r of rows) {
                        freq[r.real_sender_id] = (freq[r.real_sender_id] || 0) + 1;
                    }
                } catch (_) {}
            }

            let bestId = null, bestCount = 0;
            for (const [id, count] of Object.entries(freq)) {
                if (count > bestCount) { bestCount = count; bestId = Number(id); }
            }
            if (bestId !== null) result.set(filePath, bestId);
        } catch (_) {} finally {
            try { db.close(); } catch (_) {}
        }
    }

    _selfSenderIds = result;
    return result;
}

class WechatReader {
    /**
     * 获取微信联系人列表
     * @param {number} limit - 最大返回数量，默认 5000
     * @returns {Array<{username, nickName, remark, description, alias}>}
     */
    getContacts(limit = 5000) {
        const db = openDb(dbPath('contact_contact.db'));
        if (!db) {
            console.warn('[WechatReader] contact_contact.db 不存在');
            return [];
        }
        try {
            const rows = db.prepare(`
                SELECT username, nick_name, remark, description, alias,
                       pin_yin_initial, quan_pin,
                       remark_pin_yin_initial, remark_quan_pin
                FROM contact
                WHERE local_type = 1
                  AND delete_flag = 0
                  AND nick_name IS NOT NULL
                  AND nick_name != ''
                ORDER BY rowid
                LIMIT ?
            `).all(limit);

            return rows.map(r => ({
                username:       r.username,
                nickName:       r.nick_name,
                remark:         r.remark || '',
                description:    r.description || '',
                alias:          r.alias || '',
                // 排序用全拼：优先用备注名全拼，否则用昵称全拼
                quanPin:        r.remark_quan_pin || r.quan_pin || '',
                pinYinInitial:  r.remark_pin_yin_initial || r.pin_yin_initial || '',
            }));
        } catch (e) {
            console.error('[WechatReader] getContacts error:', e.message);
            return [];
        } finally {
            try { db.close(); } catch (_) {}
        }
    }

    /**
     * 获取最近活跃会话列表
     * @param {number} limit - 最大返回数量，默认 200
     * @returns {Array<{username, lastTimestamp, summary}>}
     */
    getSessions(limit = 200) {
        const db = openDb(dbPath('session_session.db'));
        if (!db) {
            console.warn('[WechatReader] session_session.db 不存在');
            return [];
        }
        try {
            // 使用 CAST 避免 Node.js 大整数溢出（last_timestamp 若为毫秒时间戳会溢出）
            const rows = db.prepare(`
                SELECT username,
                       CAST(last_timestamp AS TEXT) AS last_ts,
                       summary
                FROM SessionTable
                ORDER BY last_timestamp DESC
                LIMIT ?
            `).all(limit);

            return rows.map(r => ({
                username:      r.username,
                lastTimestamp: parseInt(r.last_ts, 10) || 0,
                summary:       r.summary || '',
            }));
        } catch (e) {
            console.error('[WechatReader] getSessions error:', e.message);
            return [];
        } finally {
            try { db.close(); } catch (_) {}
        }
    }

    /**
     * 获取指定联系人的最近消息（仅文本消息，local_type=1）
     * 遍历 message_message_0.db ~ message_message_10.db 查找对应表
     * @param {string} username - 微信 ID
     * @param {number} limit - 最大返回数量，默认 50
     * @returns {Array<{localId, createTime, senderUsername, content, localType}>}
     */
    getMessages(username, limit = 99999) {
        const tableName = `Msg_${md5(username)}`;
        const allRows = [];

        for (let i = 0; i <= 10; i++) {
            const filePath = dbPath(`message_message_${i}.db`);
            const db = openDb(filePath);
            if (!db) continue;
            try {
                const tableExists = db.prepare(
                    "SELECT name FROM sqlite_master WHERE type='table' AND name=?"
                ).get(tableName);
                if (!tableExists) { db.close(); continue; }

                const rows = db.prepare(`
                    SELECT local_id, create_time, message_content
                    FROM ${tableName}
                    WHERE local_type = 1
                    ORDER BY create_time DESC
                    LIMIT ?
                `).all(limit);
                db.close();

                for (const r of rows) {
                    if (typeof r.message_content !== 'string') continue;
                    const parsed = parseMessageContent(r.message_content);
                    allRows.push({
                        localId:        r.local_id,
                        createTime:     r.create_time,
                        senderUsername: parsed.senderUsername,
                        content:        parsed.content,
                    });
                }
            } catch (e) {
                console.warn(`[WechatReader] getMessages error in db ${i}:`, e.message);
                try { db.close(); } catch (_) {}
            }
        }

        allRows.sort((a, b) => b.createTime - a.createTime);
        return allRows.slice(0, limit).reverse();
    }

    /**
     * 分页查询消息（直接从 SQLite 实时读取，不写图谱）
     * 支持关键词全文搜索（跨所有 DB 文件）
     *
     * @param {string} username  - 微信 ID
     * @param {number} page      - 页码（1-based），默认 1
     * @param {number} pageSize  - 每页条数，默认 200
     * @param {string} query     - 搜索关键词，空字符串表示不过滤
     * @returns {{ messages, total, page, pages, pageSize }}
     */
    getMessagesPaged(username, page = 1, pageSize = 200, query = '') {
        const tableName = `Msg_${md5(username)}`;
        const allRows = [];
        const likeQ = query ? `%${query}%` : null;

        for (let i = 0; i <= 10; i++) {
            const filePath = dbPath(`message_message_${i}.db`);
            const db = openDb(filePath);
            if (!db) continue;
            try {
                const tableExists = db.prepare(
                    "SELECT name FROM sqlite_master WHERE type='table' AND name=?"
                ).get(tableName);
                if (!tableExists) { db.close(); continue; }

                const sql = likeQ
                    ? `SELECT local_id, create_time, real_sender_id, message_content FROM ${tableName} WHERE local_type=1 AND message_content LIKE ? ORDER BY create_time DESC`
                    : `SELECT local_id, create_time, real_sender_id, message_content FROM ${tableName} WHERE local_type=1 ORDER BY create_time DESC`;

                const rows = likeQ ? db.prepare(sql).all(likeQ) : db.prepare(sql).all();
                db.close();

                const selfId = getSelfSenderIds().get(filePath);
                for (const r of rows) {
                    if (typeof r.message_content !== 'string') continue;
                    const parsed = parseMessageContent(r.message_content);
                    allRows.push({
                        localId:    r.local_id,
                        createTime: r.create_time,
                        isSelf:     selfId !== undefined && r.real_sender_id === selfId,
                        sender:     parsed.senderUsername,
                        content:    parsed.content,
                    });
                }
            } catch (e) {
                console.warn(`[WechatReader] getMessagesPaged error in db ${i}:`, e.message);
                try { db.close(); } catch (_) {}
            }
        }

        // 按时间倒序排序（最新在前），分页后恢复正序（页内时间从早到晚）
        allRows.sort((a, b) => b.createTime - a.createTime);
        const total = allRows.length;
        const offset = (page - 1) * pageSize;
        const messages = allRows.slice(offset, offset + pageSize).reverse();

        return {
            messages,
            total,
            page,
            pages: Math.ceil(total / pageSize) || 1,
            pageSize,
        };
    }
}

module.exports = { WechatReader };