[js] 自动渲染目录中包含数据库的页面以及被数据库记录关联的页面

作用：习惯将数据库关联的页面作为子页面，为了直观管理形成了这段代码
逻辑：
1、遍历笔记所有数据库，修改目录中包含数据库的页面的图标和标题颜色
2、遍历每个数据库关联的页面，修改页面的图标和标题颜色
限制：暂时没考虑新增数据库以及新关联页面

// 配置选项
const CONFIG = {
    styles: {
        database: {
            color: '#ff4757',
            fontWeight: 'bold',
            icon: 'data:image/svg+xml;base64,PHN2ZyB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciIHdpZHRoPSIyNCIgaGVpZ2h0PSIyNCIgdmlld0JveD0iMCAwIDI0IDI0IiBmaWxsPSJub25lIiBzdHJva2U9ImN1cnJlbnRDb2xvciIgc3Ryb2tlLXdpZHRoPSIyIiBzdHJva2UtbGluZWNhcD0icm91bmQiIHN0cm9rZS1saW5lam9pbj0icm91bmQiIGNsYXNzPSJsdWNpZGUgbHVjaWRlLWRhdGFiYXNlLWljb24gbHVjaWRlLWRhdGFiYXNlIj48ZWxsaXBzZSBjeD0iMTIiIGN5PSI1IiByeD0iOSIgcnk9IjMiLz48cGF0aCBkPSJNMyA1VjE5QTkgMyAwIDAgMCAyMSAxOVY1Ii8+PHBhdGggZD0iTTMgMTJBOSAzIDAgMCAwIDIxIDEyIi8+PC9zdmc+',
            iconType: 'svg'
        },
        referenced: {
            color: '#3742fa',
            icon: 'data:image/svg+xml;base64,PHN2ZyB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciIHdpZHRoPSIyNCIgaGVpZ2h0PSIyNCIgdmlld0JveD0iMCAwIDI0IDI0IiBmaWxsPSJub25lIiBzdHJva2U9ImN1cnJlbnRDb2xvciIgc3Ryb2tlLXdpZHRoPSIyIiBzdHJva2UtbGluZWNhcD0icm91bmQiIHN0cm9rZS1saW5lam9pbj0icm91bmQiIGNsYXNzPSJsdWNpZGUgbHVjaWRlLWxpbmstaWNvbiBsdWNpZGUtbGluayI+PHBhdGggZD0iTTEwIDEzYTUgNSAwIDAgMCA3LjU0LjU0bDMtM2E1IDUgMCAwIDAtNy4wNy03LjA3bC0xLjcyIDEuNzEiLz48cGF0aCBkPSJNMTQgMTFhNSA1IDAgMCAwLTcuNTQtLjU0bC0zIDNhNSA1IDAgMCAwIDcuMDcgNy4wN2wxLjcxLTEuNzEiLz48L3N2Zz4=',
            iconType: 'svg'
        }
    },
    showIcon: true,
    debug: true,
    enableReferencedPages: true,
    enableEventListeners: true, // 启用事件监听
    pagination: {
        pageSize: 64,
        maxRetries: 3,
        retryDelay: 100
    },
    rendering: {
        initialRetries: 3,
        retryInterval: 1000,
        batchSize: 100,
        eventDebounceDelay: 300 // 事件防抖延迟
    }
};

// SQL 常量
const SQL = {
    DB_COUNT: `SELECT COUNT(DISTINCT root_id) FROM blocks WHERE type = 'av'`,
    DB_PAGE: `SELECT DISTINCT root_id FROM blocks WHERE type = 'av'`,
    REF_COUNT: `SELECT count(DISTINCT root_id) FROM attributes WHERE box IN (SELECT box FROM blocks WHERE type = 'av') AND name = 'custom-avs'`,
    REF_PAGE: `SELECT DISTINCT root_id FROM attributes WHERE box IN (SELECT box FROM blocks WHERE type = 'av') AND name = 'custom-avs'`
};

// 工具延迟
class DelayUtil {
    static async delay(ms) {
        return new Promise(resolve => setTimeout(resolve, ms));
    }
}

class EnhancedDatabaseDocumentChecker {
    constructor(config = CONFIG) {
        this.config = config;
        this.databaseDocumentIds = new Set();
        this.referencedDocumentIds = new Set();
        this.renderedElements = new WeakSet();
        this.renderQueue = new Set();
        this.isRendering = false;
        this.renderTimeout = null;
        this.eventListeners = []; // 存储事件监听器
        this.eventDebounceTimer = null; // 事件防抖定时器
    }
  
    log(...args) {
        if (this.config.debug) console.log('[DB检查器]', ...args);
    }

    // SQL 查询
    async executeSingleSQL(sql, description = '') {
        try {
            this.log(`执行SQL查询${description}:`, sql);
            const response = await fetch('/api/query/sql', {
                method: 'POST',
                headers: { 'Content-Type': 'application/json' },
                body: JSON.stringify({ stmt: sql })
            });
            if (!response.ok) throw new Error(`HTTP错误: ${response.status}`);
            const result = await response.json();
            if (result.code !== 0) throw new Error(`查询失败: ${result.msg || '未知错误'}`);
            return result.data || [];
        } catch (error) {
            this.log(`SQL查询失败${description}:`, error);
            throw error;
        }
    }

    async getTotalCount(countSql, description = '') {
        const result = await this.executeSingleSQL(countSql, `${description}-计数`);
        return result.length ? (parseInt(Object.values(result[0])[0]) || 0) : 0;
    }

    // 精确分页查询
    async executePrecisePaginatedSQL(baseSql, countSql, description = '') {
        const { pageSize, maxRetries, retryDelay } = this.config.pagination;
        const totalCount = await this.getTotalCount(countSql, description);
        if (!totalCount) return [];
        const totalPages = Math.ceil(totalCount / pageSize);
        let allResults = [];
        for (let page = 0; page < totalPages; page++) {
            const offset = page * pageSize;
            const paginatedSql = `${baseSql} LIMIT ${pageSize} OFFSET ${offset}`;
            let batchResults = [];
            for (let retry = 0; retry < maxRetries; retry++) {
                try {
                    batchResults = await this.executeSingleSQL(paginatedSql, `${description}-第${page + 1}/${totalPages}页`);
                    break;
                } catch (error) {
                    if (retry === maxRetries - 1) this.log(`第${page + 1}页查询失败:`, error.message);
                    await DelayUtil.delay(retryDelay);
                }
            }
            if (batchResults.length) allResults.push(...batchResults);
            if (page < totalPages - 1) await DelayUtil.delay(retryDelay / 2);
        }
        if (allResults.length < totalCount) this.log(`${description} 数据可能不完整: 预期${totalCount}条，实际${allResults.length}条`);
        return allResults;
    }

    // 传统分页（降级）
    async executeLegacyPaginatedSQL(baseSql, description = '') {
        const { pageSize, maxRetries, retryDelay } = this.config.pagination;
        let allResults = [], offset = 0, batch = 0;
        while (true) {
            const paginatedSql = `${baseSql} LIMIT ${pageSize} OFFSET ${offset}`;
            let batchResults = [];
            for (let retry = 0; retry < maxRetries; retry++) {
                try {
                    batchResults = await this.executeSingleSQL(paginatedSql, `${description}-第${batch + 1}批`);
                    break;
                } catch (error) {
                    if (retry === maxRetries - 1) throw error;
                    await DelayUtil.delay(retryDelay);
                }
            }
            if (batchResults.length < pageSize) break;
            allResults.push(...batchResults);
            offset += pageSize;
            batch++;
            if (batch > 100) break;
            await DelayUtil.delay(retryDelay / 2);
        }
        return allResults;
    }

    // 查询数据库页面
    async queryDatabaseDocuments() {
        const data = await this.executeSingleSQL(SQL.DB_PAGE, '(数据库页面)');
        this.databaseDocumentIds = new Set(data.map(row => row.root_id));
        this.log('数据库页面查询完成:', data.length);
        return !!data.length;
    }

    // 查询被引用页面
    async queryReferencedDocuments() {
        if (!this.config.enableReferencedPages) return true;
        try {
            const data = await this.executePrecisePaginatedSQL(SQL.REF_PAGE, SQL.REF_COUNT, '(关联页面)');
            this.referencedDocumentIds = new Set(data.map(row => row.root_id));
            this.log('关联页面查询完成:', data.length);
            return !!data.length;
        } catch (error) {
            this.log('精确分页失败，降级到传统分页:', error);
            return await this.queryReferencedDocumentsFallback();
        }
    }

    async queryReferencedDocumentsFallback() {
        try {
            const data = await this.executeLegacyPaginatedSQL(SQL.REF_PAGE, '(关联页面-降级)');
            this.referencedDocumentIds = new Set(data.map(row => row.root_id));
            this.log('关联页面降级查询完成:', data.length);
            return !!data.length;
        } catch (error) {
            this.log('降级查询也失败了:', error);
            return false;
        }
    }

    // 验证查询结果
    async validateResults() {
        try {
            const dbCount = await this.getTotalCount(SQL.DB_COUNT, '(验证-数据库页面)');
            const refCount = await this.getTotalCount(SQL.REF_COUNT, '(验证-关联页面)');
            this.log(`验证结果: 数据库页面 预期${dbCount}, 实际${this.databaseDocumentIds.size}`);
            this.log(`验证结果: 关联页面 预期${refCount}, 实际${this.referencedDocumentIds.size}`);
            return this.databaseDocumentIds.size === dbCount && this.referencedDocumentIds.size === refCount;
        } catch (error) {
            this.log('验证查询失败:', error);
            return false;
        }
    }

    // 测试分页查询
    async testPaginatedQuery() {
        this.log('开始测试分页查询');
        try {
            const totalCount = await this.getTotalCount(SQL.REF_COUNT, '(测试)');
            const results = await this.executePrecisePaginatedSQL(SQL.REF_PAGE, SQL.REF_COUNT, '(测试分页)');
            const isValid = results.length === totalCount;
            this.log(`测试分页查询 - 总数: ${totalCount}, 实际获取: ${results.length}, 结果验证: ${isValid ? '通过' : '失败'}`);
            return { totalCount, actualCount: results.length, isValid };
        } catch (error) {
            this.log('测试分页查询失败:', error);
            return null;
        }
    }

    // 查找DOM元素
    findFileTreeElements() {
        const selectors = [
            '.file-tree [data-node-id]',
            '.sy__file [data-node-id]',
            '.b3-list-item[data-node-id]',
            '.protyle-breadcrumb [data-node-id]',
            '.layout-tab-bar [data-node-id]',
            '.layout-tab-bar__text[data-node-id]',
            '.sy__outline [data-node-id]',
            '.outline [data-node-id]',
            '.backlink [data-node-id]',
            '.protyle-attr [data-node-id]',
            '.search__item [data-node-id]',
            '.b3-list [data-node-id]',
            '[data-node-id]' // 兜底
        ];
        const allElements = new Set();
        selectors.forEach(selector => {
            document.querySelectorAll(selector).forEach(el => {
                if (el.getAttribute('data-node-id')) allElements.add(el);
            });
        });
        this.log(`找到 ${allElements.size} 个DOM元素`);
        return Array.from(allElements);
    }

    // 初始渲染
    async renderOnce() {
        const { initialRetries, retryInterval, batchSize } = this.config.rendering;
        let attempt = 0, bestResult = 0;
        this.log('开始渲染');
        while (attempt < initialRetries) {
            attempt++;
            const elements = this.findFileTreeElements();
            if (!elements.length) {
                this.log(`第${attempt}次尝试: 未找到DOM元素，${retryInterval}ms后重试`);
                await DelayUtil.delay(retryInterval);
                continue;
            }
            let updatedCount = 0;
            for (let i = 0; i < elements.length; i += batchSize) {
                elements.slice(i, i + batchSize).forEach(element => {
                    if (this.updateItemStyle(element)) updatedCount++;
                });
                await DelayUtil.delay(10);
            }
            bestResult = Math.max(bestResult, updatedCount);
            this.log(`第${attempt}次尝试完成: 更新了${updatedCount}个元素`);
            const expectedCount = this.databaseDocumentIds.size + this.referencedDocumentIds.size;
            if (updatedCount >= expectedCount * 0.8 || updatedCount >= 10) break;
            await DelayUtil.delay(retryInterval);
        }
        this.log(`渲染完成: 最终更新了${bestResult}个元素`);
        return bestResult;
    }

    // 更新样式
    updateItemStyle(item) {
        if (!item || !item.getAttribute) return false;
        const nodeId = item.getAttribute('data-node-id');
        if (!nodeId) return false;
        // 查找标题元素
        const textSelectors = [
            '.b3-list-item__text', '.item__text', '.layout-tab-bar__text',
            '.file-tree__text', '.sy__file-text', '.outline__text',
            '.search__text', 'span:not([class])', '.ariaLabel'
        ];
        let titleElement = null;
        for (const selector of textSelectors) {
            titleElement = item.querySelector(selector);
            if (titleElement) break;
        }
        if (!titleElement && item.textContent && item.textContent.trim()) titleElement = item;
        if (!titleElement) return false;
        let applied = false;
        if (this.databaseDocumentIds.has(nodeId)) {
            this.applyDatabaseStyles(titleElement);
            applied = true;
        } else if (this.referencedDocumentIds.has(nodeId)) {
            this.applyReferencedStyles(titleElement);
            applied = true;
        }
        if (applied) this.renderedElements.add(item);
        return applied;
    }

    // 数据库样式
    applyDatabaseStyles(element) {
        const { styles, showIcon } = this.config;
        const dbStyles = styles.database;
        element.style.color = dbStyles.color;
        element.style.fontWeight = dbStyles.fontWeight;
        element.classList.add('database-document');
  
        if (showIcon) {
            const existingIcon = element.parentElement?.querySelector('.b3-list-item__icon, .file-tree__icon, .sy__file-icon, .item__icon, [data-type="icon"]');
            if (existingIcon && dbStyles.iconType === 'svg') {
                existingIcon.innerHTML = '';
                const img = document.createElement('img');
                img.src = dbStyles.icon;
                img.style.width = '16px';
                img.style.height = '16px';
                img.style.display = 'block';
                img.title = '数据库页面';
                existingIcon.appendChild(img);
                existingIcon.classList.add('db-icon-replaced');
            }
        }
    }

    // 引用样式
    applyReferencedStyles(element) {
        const { styles, showIcon } = this.config;
        const refStyles = styles.referenced;
        element.style.color = refStyles.color;
        element.style.fontWeight = refStyles.fontWeight;
        element.classList.add('referenced-document');
  
        if (showIcon) {
            const existingIcon = element.parentElement?.querySelector('.b3-list-item__icon, .file-tree__icon, .sy__file-icon, .item__icon, [data-type="icon"]');
            if (existingIcon && refStyles.iconType === 'svg') {
                existingIcon.innerHTML = '';
                const img = document.createElement('img');
                img.src = refStyles.icon;
                img.style.width = '16px';
                img.style.height = '16px';
                img.style.display = 'block';
                img.title = '数据库关联页面';
                existingIcon.appendChild(img);
                existingIcon.classList.add('ref-icon-replaced');
            }
        }
    }

    // 样式插入（只插入一次）
    addStyles() {
        if (document.querySelector('#database-doc-checker-styles')) return;
        const { styles } = this.config;
        const dbStyles = styles.database;
        const refStyles = styles.referenced;
        const css = `
            .database-document {
                color: ${dbStyles.color} !important;
                font-weight: ${dbStyles.fontWeight} !important;
            }
            .referenced-document {
                color: ${refStyles.color} !important;
                font-weight: ${refStyles.fontWeight} !important;
            }
            .db-icon-replaced img {
                display: block !important;
                width: 16px !important;
                height: 16px !important;
                filter: brightness(0) saturate(100%) invert(27%) sepia(51%) saturate(2878%) hue-rotate(346deg) brightness(104%) contrast(97%);
            }
            .ref-icon-replaced img {
                display: block !important;
                width: 16px !important;
                height: 16px !important;
                filter: brightness(0) saturate(100%) invert(27%) sepia(93%) saturate(2878%) hue-rotate(226deg) brightness(104%) contrast(97%);
            }
            .b3-theme-dark .db-icon-replaced img {
                filter: brightness(0) saturate(100%) invert(42%) sepia(93%) saturate(1352%) hue-rotate(87deg) brightness(119%) contrast(119%);
            }
            .b3-theme-dark .ref-icon-replaced img {
                filter: brightness(0) saturate(100%) invert(42%) sepia(93%) saturate(1352%) hue-rotate(200deg) brightness(119%) contrast(119%);
            }
        `;
        const styleElement = document.createElement('style');
        styleElement.id = 'database-doc-checker-styles';
        styleElement.textContent = css;
        document.head.appendChild(styleElement);
        this.log('CSS样式已添加（图标替换模式）');
    }

    // 事件防抖处理
    handleEventDebounce() {
        clearTimeout(this.eventDebounceTimer);
        this.eventDebounceTimer = setTimeout(async () => {
            this.log('事件触发，开始重新渲染');
            await this.forceRerender();
        }, this.config.rendering.eventDebounceDelay);
    }

    // 设置事件监听器
    setupEventListeners() {
        if (!this.config.enableEventListeners) return;
  
        // 清除旧的监听器
        this.removeEventListeners();
  
        // 点击事件（目录展开/收缩）
        const clickHandler = (event) => {
            const target = event.target;
            // 检查是否是文件树相关的点击
            if (target.closest('.file-tree') || 
                target.closest('.sy__file') || 
                target.closest('.b3-list-item') ||
                target.classList.contains('b3-list-item__arrow') ||
                target.classList.contains('file-tree__arrow')) {
                this.log('检测到文件树点击事件');
                this.handleEventDebounce();
            }
        };
  
        // 键盘事件（方向键导航等）
        const keyHandler = (event) => {
            if (['ArrowUp', 'ArrowDown', 'ArrowLeft', 'ArrowRight', 'Enter', 'Space'].includes(event.key)) {
                const target = event.target;
                if (target.closest('.file-tree') || target.closest('.sy__file')) {
                    this.log('检测到文件树键盘事件');
                    this.handleEventDebounce();
                }
            }
        };
  
        // 添加事件监听器
        document.addEventListener('click', clickHandler, true);
        document.addEventListener('keydown', keyHandler, true);
  
        // 存储监听器引用以便后续清理
        this.eventListeners = [
            { element: document, event: 'click', handler: clickHandler, options: true },
            { element: document, event: 'keydown', handler: keyHandler, options: true }
        ];
  
        this.log('事件监听器已设置');
    }

    // 移除事件监听器
    removeEventListeners() {
        this.eventListeners.forEach(({ element, event, handler, options }) => {
            element.removeEventListener(event, handler, options);
        });
        this.eventListeners = [];
        clearTimeout(this.eventDebounceTimer);
        this.log('事件监听器已移除');
    }

    // 统计信息
    getStats() {
        const overlappingPages = Array.from(this.databaseDocumentIds).filter(id => this.referencedDocumentIds.has(id));
        return {
            databaseDocumentCount: this.databaseDocumentIds.size,
            referencedDocumentCount: this.referencedDocumentIds.size,
            overlappingCount: overlappingPages.length,
            uniqueTotal: new Set([...this.databaseDocumentIds, ...this.referencedDocumentIds]).size,
            renderQueueSize: this.renderQueue.size,
            eventListenersActive: this.eventListeners.length > 0
        };
    }

    // 刷新
    async refresh() {
        this.log('手动刷新开始');
        this.removeEventListeners();
        this.databaseDocumentIds.clear();
        this.referencedDocumentIds.clear();
        this.renderQueue.clear();
        this.renderedElements = new WeakSet();
        this.isRendering = false;
        clearTimeout(this.renderTimeout);
        await this.init();
        this.log('手动刷新完成');
    }

    // 强制重新渲染
    async forceRerender() {
        this.log('开始强制重新渲染');
        this.renderedElements = new WeakSet();
        const updated = await this.renderOnce();
        this.log(`强制重新渲染完成: ${updated} 个元素`);
        return updated;
    }

    // 清理资源
    cleanup() {
        this.removeEventListeners();
        clearTimeout(this.renderTimeout);
        clearTimeout(this.eventDebounceTimer);
        this.log('资源已清理');
    }

    // 初始化
    async init() {
        this.log('初始化开始');
        this.addStyles();
        try {
            await this.queryDatabaseDocuments();
            await this.queryReferencedDocuments();
            const isValid = await this.validateResults();
            if (!isValid) this.log('查询结果验证失败，但继续执行渲染');
            const updated = await this.renderOnce();
  
            // 设置事件监听器
            this.setupEventListeners();
  
            const stats = this.getStats();
            console.log(`数据库文档检查完成:`);
            console.log(`- ${stats.databaseDocumentCount} 个数据库页面`);
            console.log(`- ${stats.referencedDocumentCount} 个关联页面`);
            console.log(`- ${stats.overlappingCount} 个重叠页面`);
            console.log(`- 初始渲染 ${updated} 个元素`);
            console.log(`- 事件监听器: ${stats.eventListenersActive ? '已启动' : '未启动'}`);
            this.log('初始化完成');
            return true;
        } catch (error) {
            this.log('初始化失败:', error);
            return false;
        }
    }
}

// 全局实例
let dbDocChecker = null;

// 初始化函数
async function initEnhancedDatabaseDocumentChecker() {
    if (dbDocChecker) {
        await dbDocChecker.refresh();
        return;
    }
    dbDocChecker = new EnhancedDatabaseDocumentChecker(CONFIG);
    await dbDocChecker.init();
    window.dbDocChecker = dbDocChecker;
    console.log('增强数据库文档检查器已启动（事件驱动模式）');
    console.log('调试命令:');
    console.log('- window.dbDocChecker.refresh() - 手动刷新');
    console.log('- window.dbDocChecker.forceRerender() - 强制重新渲染');
    console.log('- window.dbDocChecker.getStats() - 获取统计信息');
    console.log('- window.dbDocChecker.validateResults() - 验证查询结果');
    console.log('- window.dbDocChecker.testPaginatedQuery() - 测试分页查询');
    console.log('- window.dbDocChecker.cleanup() - 清理资源');
}

// 等待页面加载完成后初始化
function waitForPageLoad() {
    return new Promise((resolve) => {
        if (document.readyState === 'complete') {
            setTimeout(resolve, 2000);
        } else {
            window.addEventListener('load', () => {
                setTimeout(resolve, 2000);
            });
        }
    });
}

// 页面卸载时清理资源
window.addEventListener('beforeunload', () => {
    if (dbDocChecker) {
        dbDocChecker.cleanup();
    }
});

// 启动
waitForPageLoad().then(() => {
    initEnhancedDatabaseDocumentChecker();
});

window.initEnhancedDatabaseDocumentChecker = initEnhancedDatabaseDocumentChecker;