import ExcelJS from 'exceljs';
import fs from 'fs/promises';
import path from 'path';

/**
 * Flattens the nested JSON data into separate arrays for projects, licenses, and buildings.
 * @param {Array<Object>} data - The nested data from merged_data.json.
 * @returns {{projects: Array<Object>, licenses: Array<Object>, buildings: Array<Object>}}
 */
function flattenData(data) {
    const projects = [];
    const licenses = [];
    const buildings = [];

    data.forEach(project => {
        // Create a shallow copy to avoid modifying the original object in memory
        const projectCopy = { ...project };
        const licensesData = projectCopy['预售许可证'];
        // The nested array will not be included in the flattened project data
        delete projectCopy['预售许可证'];
        projects.push(projectCopy);

        if (licensesData && Array.isArray(licensesData)) {
            licensesData.forEach(license => {
                const licenseCopy = { ...license };
                const buildingsData = licenseCopy['楼幢'];
                // The nested array will not be included in the flattened license data
                delete licenseCopy['楼幢'];

                // Add a key to link back to the parent project
                licenseCopy['项目名称_key'] = project['项目名称'];
                licenses.push(licenseCopy);

                if (buildingsData && Array.isArray(buildingsData)) {
                    buildingsData.forEach(building => {
                        // Add keys to link back to the parent license and project
                        const buildingCopy = {
                             ...building,
                            '许可证号_key': license['许可证号'],
                            '项目名称_key': project['项目名称'],
                        };
                        buildings.push(buildingCopy);
                    });
                }
            });
        }
    });

    return { projects, licenses, buildings };
}


/**
 * Adds a worksheet to the workbook with the given data, headers, and styling.
 * @param {ExcelJS.Workbook} workbook - The workbook instance.
 * @param {string} sheetName - The name for the new worksheet.
 * @param {Array<Object>} data - The array of data for the sheet.
 */
function addSheet(workbook, sheetName, data) {
    if (data.length === 0) {
        console.log(`- 注意: 没有数据可用于工作表 '${sheetName}'`);
        return;
    }
    const worksheet = workbook.addWorksheet(sheetName);
    // Get all unique keys from all objects to form a complete header
    const allKeys = data.reduce((keys, item) => {
        if (item) {
            Object.keys(item).forEach(key => {
                if (!keys.includes(key)) {
                    keys.push(key);
                }
            });
        }
        return keys;
    }, []);

    worksheet.columns = allKeys.map(key => ({
        header: key,
        key: key,
        width: key.includes('地址') || key.includes('链接') || key.includes('简介') ? 50 : 25
    }));

    worksheet.addRows(data);

    // Style header row
    worksheet.getRow(1).eachCell(cell => {
        cell.font = { bold: true };
        cell.fill = {
            type: 'pattern',
            pattern: 'solid',
            fgColor: { argb: 'FFD3D3D3' }
        };
        cell.alignment = { vertical: 'middle', horizontal: 'center', wrapText: true };
    });

    // Add autofilter to the header row
    worksheet.autoFilter = {
        from: 'A1',
        to: {
            row: 1,
            column: allKeys.length
        }
    };
}


/**
 * Generates a multi-sheet XLSX file from the nested real estate data.
 * @param {Array<Object>} mergedData - The nested data from merged_data.json.
 * @param {Array<Object>} companiesData - The data from companies.json.
 * @param {string} filePath - The path to save the XLSX file.
 */
async function generateXlsx(mergedData, companiesData, filePath) {
    if ((!mergedData || mergedData.length === 0) && (!companiesData || companiesData.length === 0)) {
        console.log(`没有数据可生成 XLSX 文件 (${filePath})。`);
        return;
    }

    const workbook = new ExcelJS.Workbook();
    workbook.creator = 'Gemini Assistant';
    workbook.created = new Date();
    workbook.modified = new Date();

    console.log('正在创建 Excel 工作表...');

    if (mergedData && mergedData.length > 0) {
        console.log('正在将项目和许可证数据处理成多个工作表...');
        const { projects, licenses, buildings } = flattenData(mergedData);
        console.log(`- 项目: ${projects.length} 条`);
        console.log(`- 许可证: ${licenses.length} 条`);
        console.log(`- 楼幢: ${buildings.length} 条`);

        addSheet(workbook, '项目', projects);
        addSheet(workbook, '预售许可证', licenses);
        addSheet(workbook, '楼幢', buildings);
    } else {
        console.log('- 注意: merged_data.json 没有数据，跳过项目和许可证工作表创建。');
    }

    if (companiesData && companiesData.length > 0) {
        console.log('正在添加公司数据工作表...');
        console.log(`- 企业: ${companiesData.length} 条`);
        addSheet(workbook, '企业', companiesData);
    } else {
        console.log('- 注意: companies.json 没有数据，跳过企业工作表创建。');
    }


    await workbook.xlsx.writeFile(filePath);
    console.log(`✅ 成功生成多工作表 Excel 文件: ${filePath}`);
}

/**
 * Main function to read data and trigger XLSX generation.
 */
async function run() {
    console.log('🚀 开始生成 Excel 分析文件...');
    const mergedDataPath = path.join(process.cwd(), 'data', 'merged_data.json');
    const companiesDataPath = path.join(process.cwd(), 'data', 'companies.json');
    const outputPath = path.join(process.cwd(), '普宁房产数据分析.xlsx');

    let mergedData = [];
    let companiesData = [];

    try {
        await fs.access(mergedDataPath);
        console.log(`读取数据源: ${mergedDataPath}`);
        mergedData = JSON.parse(await fs.readFile(mergedDataPath, 'utf-8'));
    } catch (error) {
        if (error.code === 'ENOENT') {
            console.log(`- 注意: 未找到数据文件 '${path.basename(mergedDataPath)}'。`);
        } else {
            console.error(`❌ 读取 ${path.basename(mergedDataPath)} 时发生错误:`, error.message);
            process.exit(1);
        }
    }

    try {
        await fs.access(companiesDataPath);
        console.log(`读取数据源: ${companiesDataPath}`);
        companiesData = JSON.parse(await fs.readFile(companiesDataPath, 'utf-8'));
    } catch (error) {
        if (error.code === 'ENOENT') {
            console.log(`- 注意: 未找到数据文件 '${path.basename(companiesDataPath)}'。`);
        } else {
            console.error(`❌ 读取 ${path.basename(companiesDataPath)} 时发生错误:`, error.message);
            process.exit(1);
        }
    }
    
    // Pass both datasets to generateXlsx
    await generateXlsx(mergedData, companiesData, outputPath);
}

run();