const express = require('express');
const multer = require('multer');
const XLSX = require('xlsx');
const path = require('path');
const fs = require('fs');
const uploadRouter = require('./routes/upload');
const dotenv = require('dotenv');

// 确保在其他代码之前加载环境变量
const result = dotenv.config({
    path: path.resolve(__dirname, '.env')
});

if (result.error) {
    console.error('Error loading .env file:', result.error);
}

const app = express();
const upload = multer({ dest: 'uploads/' });

// 用于存储原始工作簿格式
let originalWorkbook = null;

// 配置中间件
app.use(express.json({limit: '50mb'}));
app.use(express.urlencoded({ extended: true, limit: '50mb' }));
app.use(express.static(path.join(__dirname, 'public')));

// 注册图片上传路由
app.use('/', uploadRouter);

// Excel文件上传处理
app.post('/upload', upload.single('excelFile'), (req, res) => {
    if (!req.file) {
        return res.status(400).send('No file uploaded.');
    }

    // 读取并保存原始工作簿
    originalWorkbook = XLSX.readFile(req.file.path, {
        cellStyles: true,
        cellNF: true,
        cellFormula: true
    });
    
    const firstSheet = originalWorkbook.Sheets[originalWorkbook.SheetNames[0]];
    
    // 读取标题 (A1 到 AR1)
    const headers = [];
    for (let i = 0; i < 44; i++) {
        const cellRef = XLSX.utils.encode_cell({c: i, r: 0});
        const cell = firstSheet[cellRef];
        headers.push(cell ? cell.v : '');
    }

    // 读取数据并保留单元格格式信息
    const range = XLSX.utils.decode_range(firstSheet['!ref']);
    const data = [];
    
    for(let R = 1; R <= range.e.r; ++R) {
        const row = {};
        for(let C = 0; C <= range.e.c; ++C) {
            const cellRef = XLSX.utils.encode_cell({c: C, r: R});
            const cell = firstSheet[cellRef];
            if(cell) {
                row[headers[C]] = cell.v;
            }
        }
        data.push(row);
    }

    // 保存原始文件路径
    originalWorkbook.filePath = req.file.path;

    res.json({headers, data});
});

// 保存编辑后的数据
app.post('/save', (req, res) => {
    try {
        const { headers, data } = req.body;
        
        // 检查超长内容
        const exceedingCells = [];
        data.forEach((row, rowIndex) => {
            headers.forEach((header, colIndex) => {
                const content = row[header];
                if (content && content.length > 32767) {
                    exceedingCells.push({
                        row: rowIndex + 2,
                        column: header,
                        length: content.length
                    });
                }
            });
        });

        if (exceedingCells.length > 0) {
            const errorMessage = exceedingCells.map(cell => 
                `第 ${cell.row} 行的 "${cell.column}" 列内容长度为 ${cell.length}，超出了Excel单元格32767个字符的限制`
            ).join('\n');
            
            return res.status(400).json({ 
                error: '存在超出Excel字符限制的单元格',
                details: exceedingCells,
                message: errorMessage
            });
        }

        if (!originalWorkbook) {
            return res.status(400).json({ error: 'No original workbook found. Please upload file first.' });
        }

        const originalSheet = originalWorkbook.Sheets[originalWorkbook.SheetNames[0]];
        
        data.forEach((row, rowIndex) => {
            headers.forEach((header, colIndex) => {
                const cellRef = XLSX.utils.encode_cell({ r: rowIndex + 1, c: colIndex });
                const value = row[header];
                
                if (value === undefined || value === null) {
                    // 如果单元格值为空，则删除该单元格
                    delete originalSheet[cellRef];
                } else {
                    // 创建或更新单元格
                    originalSheet[cellRef] = {
                        t: 's', // 设置类型为字符串
                        v: value.toString(), // 值
                        w: value.toString(), // 格式化显示值
                        h: value.toString(), // HTML显示值
                    };
                }
            });
        });

        // 创建downloads目录（如果不存在）
        const downloadsDir = path.join(__dirname, 'downloads');
        if (!fs.existsSync(downloadsDir)) {
            fs.mkdirSync(downloadsDir);
        }

        // 保存文件
        const fileName = `edited_${Date.now()}.xlsx`;
        const filePath = path.join(downloadsDir, fileName);
        
        XLSX.writeFile(originalWorkbook, filePath, {
            bookType: 'xlsx',
            bookSST: false,
            type: 'file',
            cellStyles: true,
            cellNF: true,
            cellFormula: true
        });

        res.json({ downloadUrl: `/download/${fileName}` });
    } catch (error) {
        console.error('Save error:', error);
        res.status(500).json({ error: error.message });
    }
});

// 下载文件
app.get('/download/:filename', (req, res) => {
    const filePath = path.join(__dirname, 'downloads', req.params.filename);
    res.download(filePath, req.params.filename, (err) => {
        if (!err) {
            // 下载完成后删除文件
            fs.unlinkSync(filePath);
        }
    });
});

// 错误处理中间件
app.use((err, req, res, next) => {
    console.error(err.stack);
    res.status(500).json({ error: err.message || '服务器内部错误' });
});

// 清理函数：当服务器关闭时删除临时文件
function cleanup() {
    if (originalWorkbook && originalWorkbook.filePath) {
        try {
            fs.unlinkSync(originalWorkbook.filePath);
        } catch (err) {
            console.error('Error cleaning up:', err);
        }
    }
}

// 监听进程退出事件
process.on('SIGINT', () => {
    cleanup();
    process.exit();
});

process.on('SIGTERM', () => {
    cleanup();
    process.exit();
});

const port = process.env.PORT || 3100;
app.listen(port, () => {
    console.log(`Server is running on port ${port}`);
});