const { Policy, InsuranceApplication, InsuranceType, User } = require('../models');
const responseFormat = require('../utils/response');
const { Op } = require('sequelize');
const ExcelExport = require('../utils/excelExport');

// 获取保单列表
const getPolicies = async (req, res) => {
  try {
    const {
      policy_number, // 前端发送的参数名
      policyholder_name, // 前端发送的参数名
      insurance_type_id, // 前端发送的参数名
      status, // 前端发送的参数名
      page = 1,
      pageSize = 10 // 前端发送的参数名
    } = req.query;
    
    const whereClause = {};
    
    // 保单编号筛选
    if (policy_number) {
      whereClause.policy_no = { [Op.like]: `%${policy_number}%` };
    }
    
    // 保单状态筛选
    if (status) {
      whereClause.policy_status = status;
    }
    
    // 保险类型筛选
    if (insurance_type_id) {
      whereClause.insurance_type_id = insurance_type_id;
    }
    
    const offset = (page - 1) * pageSize;
    
    const { count, rows } = await Policy.findAndCountAll({
      where: whereClause,
      include: [
        {
          model: InsuranceApplication,
          as: 'application',
          attributes: ['id', 'customer_name', 'customer_phone'],
          include: [
            {
              model: InsuranceType,
              as: 'insurance_type',
              attributes: ['id', 'name']
            }
          ]
        },
        {
          model: User,
          as: 'customer',
          attributes: ['id', 'real_name', 'username']
        },
        {
          model: InsuranceType,
          as: 'insurance_type',
          attributes: ['id', 'name']
        }
      ],
      order: [['created_at', 'DESC']],
      offset,
      limit: parseInt(pageSize)
    });
    
    // 处理返回数据，确保前端能够正确解析
    const processedRows = rows.map(row => {
      const policy = row.toJSON();
      return {
        id: policy.id,
        policy_number: policy.policy_no, // 映射字段名
        // 优先使用保单表中的姓名字段，如果为空则使用关联表中的数据
        policyholder_name: policy.policyholder_name || policy.application?.customer_name || policy.customer?.real_name || '',
        insured_name: policy.insured_name || policy.application?.customer_name || policy.customer?.real_name || '',
        insurance_type_id: policy.insurance_type_id,
        insurance_type_name: policy.insurance_type?.name || '',
        premium_amount: parseFloat(policy.premium_amount) || 0,
        coverage_amount: parseFloat(policy.coverage_amount) || 0,
        start_date: policy.start_date,
        end_date: policy.end_date,
        status: policy.policy_status, // 映射字段名
        // 优先使用保单表中的联系信息字段
        phone: policy.phone || policy.application?.customer_phone || '',
        email: policy.email || policy.customer?.email || '',
        address: policy.address || policy.application?.address || '',
        remarks: policy.terms_and_conditions || '',
        created_at: policy.created_at,
        updated_at: policy.updated_at
      };
    });
    
    res.json(responseFormat.pagination(processedRows, {
      page: parseInt(page),
      pageSize: parseInt(pageSize),
      total: count
    }, '获取保单列表成功'));
  } catch (error) {
    console.error('获取保单列表错误:', error);
    res.status(500).json(responseFormat.error('获取保单列表失败'));
  }
};

// 创建保单
const createPolicy = async (req, res) => {
  try {
    const policyData = req.body;
    
    // 生成保单编号（优先使用前端传递的policy_number，否则自动生成）
    const policyNo = policyData.policy_number || `POL${Date.now()}${Math.random().toString(36).substr(2, 6).toUpperCase()}`;
    
    // 构建保单数据，优先使用前端传递的数据
    const createData = {
      // 基本信息
      policy_no: policyNo,
      insurance_type_id: policyData.insurance_type_id || 1,
      
      // 客户信息 - 只有在前端没有传递时才使用默认值
      application_id: policyData.application_id || 4, // 默认关联存在的申请（id=4）
      customer_id: policyData.customer_id || 1, // 默认关联第一个客户（admin用户）
      
      // 保单持有人信息（直接保存到Policy表中）
      policyholder_name: policyData.policyholder_name || '',
      insured_name: policyData.insured_name || '',
      
      // 金额信息
      premium_amount: policyData.premium_amount,
      coverage_amount: policyData.coverage_amount,
      
      // 日期信息
      start_date: policyData.start_date,
      end_date: policyData.end_date,
      
      // 状态信息
      policy_status: policyData.status || 'active',
      payment_status: 'unpaid',
      
      // 联系信息（直接保存到Policy表中）
      phone: policyData.phone || '',
      email: policyData.email || '',
      address: policyData.address || '',
      
      // 备注
      terms_and_conditions: policyData.remarks,
      
      // 系统字段
      created_by: req.user?.id || 1,
      updated_by: req.user?.id || 1
    };
    
    const policy = await Policy.create(createData);
    
    res.status(201).json(responseFormat.created(policy, '保单创建成功'));
  } catch (error) {
    console.error('创建保单错误:', error);
    res.status(500).json(responseFormat.error('创建保单失败'));
  }
};

// 获取单个保单详情
const getPolicyById = async (req, res) => {
  try {
    const { id } = req.params;
    
    const policy = await Policy.findByPk(id, {
      include: [
        {
          model: InsuranceApplication,
          as: 'application',
          include: [
            {
              model: InsuranceType,
              as: 'insurance_type',
              attributes: ['id', 'name', 'description', 'premium_rate']
            }
          ]
        },
        {
          model: User,
          as: 'customer',
          attributes: ['id', 'real_name', 'username', 'phone', 'email']
        }
      ]
    });
    
    if (!policy) {
      return res.status(404).json(responseFormat.error('保单不存在'));
    }
    
    res.json(responseFormat.success(policy, '获取保单详情成功'));
  } catch (error) {
    console.error('获取保单详情错误:', error);
    res.status(500).json(responseFormat.error('获取保单详情失败'));
  }
};

// 更新保单
const updatePolicy = async (req, res) => {
  try {
    const { id } = req.params;
    const updateData = req.body;
    
    console.log('更新保单请求 - ID:', id);
    console.log('更新保单数据:', JSON.stringify(updateData, null, 2));
    
    const policy = await Policy.findByPk(id);
    if (!policy) {
      return res.status(404).json(responseFormat.error('保单不存在'));
    }
    
    // 过滤掉null值的必填关联字段，避免覆盖现有数据
    const filteredUpdateData = { ...updateData };
    
    // 处理日期字段格式
    if (filteredUpdateData.start_date) {
      // 确保日期格式正确
      const startDate = new Date(filteredUpdateData.start_date);
      if (isNaN(startDate.getTime())) {
        console.error('无效的开始日期格式:', filteredUpdateData.start_date);
        return res.status(400).json(responseFormat.error('开始日期格式无效'));
      }
      filteredUpdateData.start_date = startDate;
    }
    
    if (filteredUpdateData.end_date) {
      // 确保日期格式正确
      const endDate = new Date(filteredUpdateData.end_date);
      if (isNaN(endDate.getTime())) {
        console.error('无效的结束日期格式:', filteredUpdateData.end_date);
        return res.status(400).json(responseFormat.error('结束日期格式无效'));
      }
      filteredUpdateData.end_date = endDate;
    }
    
    // 如果这些关联字段为null或undefined，则不更新它们
    if (filteredUpdateData.customer_id === null || filteredUpdateData.customer_id === undefined) {
      delete filteredUpdateData.customer_id;
    }
    
    if (filteredUpdateData.application_id === null || filteredUpdateData.application_id === undefined) {
      delete filteredUpdateData.application_id;
    }
    
    if (filteredUpdateData.insurance_type_id === null || filteredUpdateData.insurance_type_id === undefined) {
      delete filteredUpdateData.insurance_type_id;
    }
    
    // 验证必填字段
    if (filteredUpdateData.hasOwnProperty('policy_no') && (!filteredUpdateData.policy_no || filteredUpdateData.policy_no.trim() === '')) {
      return res.status(400).json(responseFormat.error('保单编号不能为空'));
    }
    
    if (filteredUpdateData.hasOwnProperty('coverage_amount') && (filteredUpdateData.coverage_amount < 0)) {
      return res.status(400).json(responseFormat.error('保额不能小于0'));
    }
    
    if (filteredUpdateData.hasOwnProperty('premium_amount') && (filteredUpdateData.premium_amount < 0)) {
      return res.status(400).json(responseFormat.error('保费金额不能小于0'));
    }
    
    console.log('处理后的更新数据:', JSON.stringify(filteredUpdateData, null, 2));
    
    await policy.update(filteredUpdateData);
    
    console.log('保单更新成功:', policy.id);
    res.json(responseFormat.success(policy, '保单更新成功'));
  } catch (error) {
    console.error('更新保单错误详情:', {
      message: error.message,
      stack: error.stack,
      name: error.name,
      sql: error.sql || 'N/A',
      parameters: error.parameters || 'N/A'
    });
    
    // 根据错误类型返回更具体的错误信息
    if (error.name === 'SequelizeValidationError') {
      const validationErrors = error.errors.map(err => err.message).join(', ');
      return res.status(400).json(responseFormat.error(`数据验证失败: ${validationErrors}`));
    }
    
    if (error.name === 'SequelizeUniqueConstraintError') {
      return res.status(400).json(responseFormat.error('保单编号已存在'));
    }
    
    if (error.name === 'SequelizeForeignKeyConstraintError') {
      return res.status(400).json(responseFormat.error('关联数据不存在'));
    }
    
    res.status(500).json(responseFormat.error('更新保单失败'));
  }
};

// 更新保单状态
const updatePolicyStatus = async (req, res) => {
  try {
    const { id } = req.params;
    const { policy_status } = req.body;
    
    const policy = await Policy.findByPk(id);
    if (!policy) {
      return res.status(404).json(responseFormat.error('保单不存在'));
    }
    
    if (!['active', 'expired', 'cancelled', 'suspended'].includes(policy_status)) {
      return res.status(400).json(responseFormat.error('无效的保单状态'));
    }
    
    await policy.update({ policy_status });
    
    res.json(responseFormat.success(policy, '保单状态更新成功'));
  } catch (error) {
    console.error('更新保单状态错误:', error);
    res.status(500).json(responseFormat.error('更新保单状态失败'));
  }
};

// 获取保单统计
const getPolicyStats = async (req, res) => {
  try {
    const stats = await Policy.findAll({
      attributes: [
        'policy_status',
        [Policy.sequelize.fn('COUNT', Policy.sequelize.col('id')), 'count'],
        [Policy.sequelize.fn('SUM', Policy.sequelize.col('coverage_amount')), 'total_coverage'],
        [Policy.sequelize.fn('SUM', Policy.sequelize.col('premium_amount')), 'total_premium']
      ],
      group: ['policy_status']
    });
    
    const total = await Policy.count();
    const totalCoverage = await Policy.sum('coverage_amount');
    const totalPremium = await Policy.sum('premium_amount');
    
    res.json(responseFormat.success({
      stats,
      total,
      total_coverage: totalCoverage || 0,
      total_premium: totalPremium || 0
    }, '获取保单统计成功'));
  } catch (error) {
    console.error('获取保单统计错误:', error);
    res.status(500).json(responseFormat.error('获取保单统计失败'));
  }
};

// 删除保单
const deletePolicy = async (req, res) => {
  try {
    const { id } = req.params;
    
    const policy = await Policy.findByPk(id);
    if (!policy) {
      return res.status(404).json(responseFormat.error('保单不存在'));
    }
    
    await policy.destroy();
    
    res.json(responseFormat.success(null, '保单删除成功'));
  } catch (error) {
    console.error('删除保单错误:', error);
    res.status(500).json(responseFormat.error('删除保单失败'));
  }
};

// 导出保单列表到Excel
const exportToExcel = async (req, res) => {
  try {
    const ExcelJS = require('exceljs');
    const { policy_number, policyholder_name, status } = req.query;
    
    const where = {};
    if (policy_number) where.policy_number = { [Op.like]: `%${policy_number}%` };
    if (policyholder_name) where.policyholder_name = { [Op.like]: `%${policyholder_name}%` };
    if (status) where.status = status;
    
    const policies = await Policy.findAll({
      where,
      include: [{
        model: InsuranceType,
        as: 'insurance_type',  // 修正：使用正确的关联别名
        attributes: ['id', 'name', 'description'],
        required: false
      }],
      order: [['created_at', 'DESC']],
      limit: 10000
    });
    
    // 将Sequelize实例转换为纯对象
    const plainPolicies = policies.map(policy => policy.toJSON());
    
    // 状态映射
    const statusMap = { 
      'active': '生效中', 
      'pending': '待生效', 
      'expired': '已过期', 
      'cancelled': '已取消' 
    };
    
    // 创建Excel工作簿
    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet('保单列表');
    
    // 定义列（包含所有字段）
    worksheet.columns = [
      { header: 'ID', key: 'id', width: 8 },
      { header: '保单编号', key: 'policy_number', width: 18 },
      { header: '投保人姓名', key: 'policyholder_name', width: 15 },
      { header: '被保人姓名', key: 'insured_name', width: 15 },
      { header: '保险类型ID', key: 'insurance_type_id', width: 12 },
      { header: '保险类型名称', key: 'insurance_type_name', width: 18 },
      { header: '保险类型说明', key: 'insurance_type_description', width: 25 },
      { header: '保障金额', key: 'coverage_amount', width: 15 },
      { header: '保费金额', key: 'premium_amount', width: 15 },
      { header: '开始日期', key: 'start_date', width: 20 },
      { header: '结束日期', key: 'end_date', width: 20 },
      { header: '状态', key: 'status', width: 12 },
      { header: '联系电话', key: 'phone', width: 15 },
      { header: '电子邮箱', key: 'email', width: 25 },
      { header: '地址', key: 'address', width: 30 },
      { header: '备注', key: 'remarks', width: 30 },
      { header: '创建时间', key: 'created_at', width: 20 },
      { header: '更新时间', key: 'updated_at', width: 20 }
    ];
    
    // 设置表头样式
    worksheet.getRow(1).font = { bold: true, size: 12 };
    worksheet.getRow(1).fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFE0E0E0' }
    };
    worksheet.getRow(1).alignment = { vertical: 'middle', horizontal: 'center' };
    
    // 添加数据行
    plainPolicies.forEach(policy => {
      worksheet.addRow({
        id: policy.id || '',
        policy_number: policy.policy_no || '',  // 修正：使用 policy_no
        policyholder_name: policy.policyholder_name || '',
        insured_name: policy.insured_name || '',
        insurance_type_id: policy.insurance_type_id || '',
        insurance_type_name: policy.insurance_type?.name || '',
        insurance_type_description: policy.insurance_type?.description || '',
        coverage_amount: policy.coverage_amount || '',
        premium_amount: policy.premium_amount || '',
        start_date: policy.start_date || '',
        end_date: policy.end_date || '',
        status: statusMap[policy.policy_status] || policy.policy_status || '',  // 修正：使用 policy_status
        phone: policy.phone || '',
        email: policy.email || '',
        address: policy.address || '',
        remarks: policy.remarks || '',
        created_at: policy.created_at || '',
        updated_at: policy.updated_at || ''
      });
    });
    
    // 设置数据行样式
    worksheet.eachRow((row, rowNumber) => {
      if (rowNumber > 1) {
        row.alignment = { vertical: 'middle', horizontal: 'left' };
      }
    });
    
    // 生成Excel文件
    const buffer = await workbook.xlsx.writeBuffer();
    
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader('Content-Disposition', `attachment; filename=policies_${Date.now()}.xlsx`);
    res.send(buffer);
  } catch (error) {
    console.error('导出保单失败:', error);
    res.status(500).json(responseFormat.error('导出失败'));
  }
};

module.exports = {
  getPolicies,
  createPolicy,
  getPolicyById,
  updatePolicy,
  updatePolicyStatus,
  deletePolicy,
  getPolicyStats,
  exportToExcel
};