const { Transaction, Account, User, Report } = require('../models');
const { Op } = require('sequelize');
const ExcelJS = require('exceljs');
const PDFDocument = require('pdfkit');
const fs = require('fs');
const path = require('path');

class ReportController {
  /**
   * 生成报表
   */
  static async generateReport(req, res) {
    try {
      const errors = validationResult(req);
      if (!errors.isEmpty()) {
        return res.status(400).json({
          success: false,
          message: '参数验证失败',
          errors: errors.array()
        });
      }

      const { reportType, dateRange, transactionType, format } = req.body;
      const userId = req.user.id;
      const [startDate, endDate] = dateRange;

      // 创建报表记录
      const report = await Report.create({
        name: `${this.getReportTypeName(reportType)}_${new Date().toISOString().split('T')[0]}`,
        type: reportType,
        format: format,
        status: 'processing',
        createdBy: userId,
        parameters: {
          dateRange,
          transactionType,
          format
        }
      });

      // 异步生成报表
      setImmediate(async () => {
        try {
          const reportData = await this.generateReportData(reportType, startDate, endDate, transactionType);
          const filePath = await this.exportReport(reportData, format, report.id);
          
          await report.update({
            status: 'completed',
            filePath: filePath,
            data: reportData
          });
        } catch (error) {
          console.error('报表生成失败:', error);
          await report.update({
            status: 'failed',
            error: error.message
          });
        }
      });

      res.json({
        success: true,
        message: '报表生成中，请稍后查看',
        data: {
          reportId: report.id,
          status: 'processing'
        }
      });

    } catch (error) {
      console.error('生成报表失败:', error);
      res.status(500).json({
        success: false,
        message: '生成报表失败',
        error: error.message
      });
    }
  }

  /**
   * 获取报表历史
   */
  static async getReportHistory(req, res) {
    try {
      const { page = 1, pageSize = 10, type, status } = req.query;
      const offset = (page - 1) * pageSize;

      const where = {};
      if (type) where.type = type;
      if (status) where.status = status;

      const { count, rows: reports } = await Report.findAndCountAll({
        where,
        order: [['createdAt', 'DESC']],
        limit: parseInt(pageSize),
        offset: parseInt(offset),
        include: [{
          model: User,
          as: 'creator',
          attributes: ['username', 'real_name']
        }]
      });

      res.json({
        success: true,
        data: {
          reports: reports.map(report => ({
            id: report.id,
            name: report.name,
            type: report.type,
            format: report.format,
            status: report.status,
            createdAt: report.createdAt,
            createdBy: report.creator ? 
              `${report.creator.real_name || report.creator.username} (${report.creator.role})` : 
              '未知用户',
            filePath: report.filePath
          })),
          pagination: {
            current: parseInt(page),
            pageSize: parseInt(pageSize),
            total: count
          }
        }
      });

    } catch (error) {
      console.error('获取报表历史失败:', error);
      res.status(500).json({
        success: false,
        message: '获取报表历史失败',
        error: error.message
      });
    }
  }

  /**
   * 下载报表
   */
  static async downloadReport(req, res) {
    try {
      const { id } = req.params;
      const report = await Report.findByPk(id);

      if (!report) {
        return res.status(404).json({
          success: false,
          message: '报表不存在'
        });
      }

      if (report.status !== 'completed') {
        return res.status(400).json({
          success: false,
          message: '报表尚未生成完成'
        });
      }

      const filePath = path.join(__dirname, '..', 'uploads', 'reports', report.filePath);
      
      if (!fs.existsSync(filePath)) {
        return res.status(404).json({
          success: false,
          message: '报表文件不存在'
        });
      }

      res.download(filePath, `${report.name}.${report.format}`);

    } catch (error) {
      console.error('下载报表失败:', error);
      res.status(500).json({
        success: false,
        message: '下载报表失败',
        error: error.message
      });
    }
  }

  /**
   * 预览报表
   */
  static async previewReport(req, res) {
    try {
      const { id } = req.params;
      const report = await Report.findByPk(id);

      if (!report) {
        return res.status(404).json({
          success: false,
          message: '报表不存在'
        });
      }

      if (report.status !== 'completed') {
        return res.status(400).json({
          success: false,
          message: '报表尚未生成完成'
        });
      }

      // 返回报表数据的前100条用于预览
      const previewData = report.data ? report.data.slice(0, 100) : [];

      res.json({
        success: true,
        data: {
          name: report.name,
          type: report.type,
          format: report.format,
          createdAt: report.createdAt,
          data: previewData
        }
      });

    } catch (error) {
      console.error('预览报表失败:', error);
      res.status(500).json({
        success: false,
        message: '预览报表失败',
        error: error.message
      });
    }
  }

  /**
   * 删除报表
   */
  static async deleteReport(req, res) {
    try {
      const { id } = req.params;
      const report = await Report.findByPk(id);

      if (!report) {
        return res.status(404).json({
          success: false,
          message: '报表不存在'
        });
      }

      // 删除文件
      if (report.filePath) {
        const filePath = path.join(__dirname, '..', 'uploads', 'reports', report.filePath);
        if (fs.existsSync(filePath)) {
          fs.unlinkSync(filePath);
        }
      }

      await report.destroy();

      res.json({
        success: true,
        message: '报表删除成功'
      });

    } catch (error) {
      console.error('删除报表失败:', error);
      res.status(500).json({
        success: false,
        message: '删除报表失败',
        error: error.message
      });
    }
  }

  /**
   * 获取报表统计
   */
  static async getReportStats(req, res) {
    try {
      const stats = await Report.findAll({
        attributes: [
          'type',
          'status',
          [Report.sequelize.fn('COUNT', Report.sequelize.col('id')), 'count']
        ],
        group: ['type', 'status'],
        raw: true
      });

      const formattedStats = {
        total: 0,
        byType: {},
        byStatus: {}
      };

      stats.forEach(stat => {
        const count = parseInt(stat.count);
        formattedStats.total += count;
        
        if (!formattedStats.byType[stat.type]) {
          formattedStats.byType[stat.type] = 0;
        }
        formattedStats.byType[stat.type] += count;
        
        if (!formattedStats.byStatus[stat.status]) {
          formattedStats.byStatus[stat.status] = 0;
        }
        formattedStats.byStatus[stat.status] += count;
      });

      res.json({
        success: true,
        data: formattedStats
      });

    } catch (error) {
      console.error('获取报表统计失败:', error);
      res.status(500).json({
        success: false,
        message: '获取报表统计失败',
        error: error.message
      });
    }
  }

  /**
   * 生成报表数据
   */
  static async generateReportData(reportType, startDate, endDate, transactionType) {
    const where = {
      createdAt: {
        [Op.between]: [startDate, endDate]
      }
    };

    if (transactionType) {
      where.type = transactionType;
    }

    switch (reportType) {
      case 'transaction':
        return await this.generateTransactionReport(where);
      case 'account':
        return await this.generateAccountReport(where);
      case 'user':
        return await this.generateUserReport(where);
      default:
        throw new Error('不支持的报表类型');
    }
  }

  /**
   * 生成交易报表数据
   */
  static async generateTransactionReport(where) {
    const transactions = await Transaction.findAll({
      where,
      include: [{
        model: Account,
        as: 'account',
        attributes: ['account_number', 'account_type']
      }],
      order: [['createdAt', 'DESC']]
    });

    return transactions.map(t => ({
      id: t.id,
      date: t.createdAt.toISOString().split('T')[0],
      type: t.type,
      amount: t.amount,
      account: t.account ? t.account.account_number : 'N/A',
      description: t.description,
      status: t.status
    }));
  }

  /**
   * 生成账户报表数据
   */
  static async generateAccountReport(where) {
    const accounts = await Account.findAll({
      where: {
        createdAt: {
          [Op.between]: [where.createdAt[Op.between][0], where.createdAt[Op.between][1]]
        }
      },
      include: [{
        model: User,
        as: 'user',
        attributes: ['username', 'real_name']
      }],
      order: [['createdAt', 'DESC']]
    });

    return accounts.map(a => ({
      id: a.id,
      accountNumber: a.account_number,
      accountType: a.account_type,
      balance: a.balance,
      owner: a.user ? a.user.real_name || a.user.username : 'N/A',
      createdAt: a.createdAt.toISOString().split('T')[0],
      status: a.status
    }));
  }

  /**
   * 生成用户报表数据
   */
  static async generateUserReport(where) {
    const users = await User.findAll({
      where: {
        createdAt: {
          [Op.between]: [where.createdAt[Op.between][0], where.createdAt[Op.between][1]]
        }
      },
      order: [['createdAt', 'DESC']]
    });

    return users.map(u => ({
      id: u.id,
      username: u.username,
      realName: u.real_name,
      email: u.email,
      phone: u.phone,
      role: u.role,
      status: u.status,
      createdAt: u.createdAt.toISOString().split('T')[0]
    }));
  }

  /**
   * 导出报表文件
   */
  static async exportReport(data, format, reportId) {
    const fileName = `report_${reportId}_${Date.now()}`;
    const uploadsDir = path.join(__dirname, '..', 'uploads', 'reports');
    
    // 确保目录存在
    if (!fs.existsSync(uploadsDir)) {
      fs.mkdirSync(uploadsDir, { recursive: true });
    }

    switch (format) {
      case 'excel':
        return await this.exportToExcel(data, uploadsDir, fileName);
      case 'pdf':
        return await this.exportToPDF(data, uploadsDir, fileName);
      case 'csv':
        return await this.exportToCSV(data, uploadsDir, fileName);
      default:
        throw new Error('不支持的导出格式');
    }
  }

  /**
   * 导出为Excel
   */
  static async exportToExcel(data, uploadsDir, fileName) {
    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet('报表数据');

    if (data.length > 0) {
      const headers = Object.keys(data[0]);
      worksheet.addRow(headers);
      
      data.forEach(row => {
        worksheet.addRow(Object.values(row));
      });
    }

    const filePath = path.join(uploadsDir, `${fileName}.xlsx`);
    await workbook.xlsx.writeFile(filePath);
    return `${fileName}.xlsx`;
  }

  /**
   * 导出为PDF
   */
  static async exportToPDF(data, uploadsDir, fileName) {
    const doc = new PDFDocument();
    const filePath = path.join(uploadsDir, `${fileName}.pdf`);
    const stream = fs.createWriteStream(filePath);
    doc.pipe(stream);

    doc.fontSize(16).text('银行报表', { align: 'center' });
    doc.moveDown();

    if (data.length > 0) {
      const headers = Object.keys(data[0]);
      let tableData = [headers];
      
      data.forEach(row => {
        tableData.push(Object.values(row));
      });

      // 简单的表格实现
      tableData.forEach((row, index) => {
        if (index === 0) {
          doc.fontSize(12).font('Helvetica-Bold');
        } else {
          doc.fontSize(10).font('Helvetica');
        }
        
        doc.text(row.join(' | '));
        doc.moveDown(0.5);
      });
    }

    doc.end();
    
    return new Promise((resolve, reject) => {
      stream.on('finish', () => resolve(`${fileName}.pdf`));
      stream.on('error', reject);
    });
  }

  /**
   * 导出为CSV
   */
  static async exportToCSV(data, uploadsDir, fileName) {
    const filePath = path.join(uploadsDir, `${fileName}.csv`);
    
    if (data.length === 0) {
      fs.writeFileSync(filePath, '');
      return `${fileName}.csv`;
    }

    const headers = Object.keys(data[0]);
    const csvContent = [
      headers.join(','),
      ...data.map(row => 
        Object.values(row).map(value => 
          typeof value === 'string' && value.includes(',') ? `"${value}"` : value
        ).join(',')
      )
    ].join('\n');

    fs.writeFileSync(filePath, csvContent, 'utf8');
    return `${fileName}.csv`;
  }

  /**
   * 获取报表类型名称
   */
  static getReportTypeName(type) {
    const names = {
      transaction: '交易报表',
      account: '账户报表',
      user: '用户报表'
    };
    return names[type] || type;
  }
}

module.exports = ReportController;