const LivestockPolicy = require('../models/LivestockPolicy');
const LivestockType = require('../models/LivestockType');
const User = require('../models/User');
const responseFormat = require('../utils/response');
const { Op } = require('sequelize');
const ExcelExport = require('../utils/excelExport');

// 获取生资保单列表
const getLivestockPolicies = async (req, res) => {
  try {
    const {
      policy_no,
      farmer_name,
      farmer_phone,
      policy_status,
      payment_status,
      livestock_type_id,
      start_date,
      end_date,
      page = 1,
      limit = 10
    } = req.query;
    
    const whereClause = {};
    
    // 保单编号筛选
    if (policy_no) {
      whereClause.policy_no = { [Op.like]: `%${policy_no}%` };
    }
    
    // 农户姓名筛选
    if (farmer_name) {
      whereClause.farmer_name = { [Op.like]: `%${farmer_name}%` };
    }
    
    // 农户电话筛选
    if (farmer_phone) {
      whereClause.farmer_phone = { [Op.like]: `%${farmer_phone}%` };
    }
    
    // 保单状态筛选
    if (policy_status) {
      whereClause.policy_status = policy_status;
    }
    
    // 支付状态筛选
    if (payment_status) {
      whereClause.payment_status = payment_status;
    }
    
    // 牲畜类型筛选
    if (livestock_type_id) {
      whereClause.livestock_type_id = livestock_type_id;
    }
    
    // 日期范围筛选
    if (start_date && end_date) {
      whereClause.start_date = {
        [Op.between]: [new Date(start_date), new Date(end_date)]
      };
    }
    
    const offset = (page - 1) * limit;
    
    const { count, rows } = await LivestockPolicy.findAndCountAll({
      where: whereClause,
      include: [
        {
          model: LivestockType,
          as: 'livestock_type',
          attributes: ['id', 'name', 'description', 'unit_price_min', 'unit_price_max', 'premium_rate']
        },
        {
          model: User,
          as: 'creator',
          attributes: ['id', 'real_name', 'username']
        }
      ],
      order: [['created_at', 'DESC']],
      offset,
      limit: parseInt(limit)
    });
    
    res.json(responseFormat.pagination(rows, {
      page: parseInt(page),
      limit: parseInt(limit),
      total: count
    }, '获取生资保单列表成功'));
  } catch (error) {
    console.error('获取生资保单列表错误:', error);
    res.status(500).json(responseFormat.error('获取生资保单列表失败'));
  }
};

// 创建生资保单
const createLivestockPolicy = async (req, res) => {
  try {
    const policyData = req.body;
    
    // 生成保单编号
    const policyNo = `LP${Date.now()}${Math.random().toString(36).substr(2, 6).toUpperCase()}`;
    
    // 计算总保额和保费
    const totalValue = policyData.livestock_count * policyData.unit_value;
    const premiumAmount = totalValue * policyData.premium_rate;
    
    const policy = await LivestockPolicy.create({
      ...policyData,
      policy_no: policyNo,
      total_value: totalValue,
      premium_amount: premiumAmount,
      created_by: req.user?.id
    });
    
    // 获取完整的保单信息（包含关联数据）
    const fullPolicy = await LivestockPolicy.findByPk(policy.id, {
      include: [
        {
          model: LivestockType,
          as: 'livestock_type',
          attributes: ['id', 'name', 'description', 'unit_price_min', 'unit_price_max', 'premium_rate']
        }
      ]
    });
    
    res.status(201).json(responseFormat.created(fullPolicy, '生资保单创建成功'));
  } catch (error) {
    console.error('创建生资保单错误:', error);
    res.status(500).json(responseFormat.error('创建生资保单失败'));
  }
};

// 获取单个生资保单详情
const getLivestockPolicyById = async (req, res) => {
  try {
    const { id } = req.params;
    
    const policy = await LivestockPolicy.findByPk(id, {
      include: [
        {
          model: LivestockType,
          as: 'livestock_type',
          attributes: ['id', 'name', 'description', 'unit_price_min', 'unit_price_max', 'premium_rate']
        },
        {
          model: User,
          as: 'creator',
          attributes: ['id', 'real_name', 'username']
        },
        {
          model: User,
          as: 'updater',
          attributes: ['id', 'real_name', 'username']
        }
      ]
    });
    
    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 updateLivestockPolicy = async (req, res) => {
  try {
    const { id } = req.params;
    const updateData = req.body;
    
    const policy = await LivestockPolicy.findByPk(id);
    if (!policy) {
      return res.status(404).json(responseFormat.error('生资保单不存在'));
    }
    
    // 如果更新了数量或单价，重新计算总保额和保费
    if (updateData.livestock_count || updateData.unit_value || updateData.premium_rate) {
      const livestockCount = updateData.livestock_count || policy.livestock_count;
      const unitValue = updateData.unit_value || policy.unit_value;
      const premiumRate = updateData.premium_rate || policy.premium_rate;
      
      updateData.total_value = livestockCount * unitValue;
      updateData.premium_amount = updateData.total_value * premiumRate;
    }
    
    updateData.updated_by = req.user?.id;
    
    await policy.update(updateData);
    
    // 获取更新后的完整信息
    const updatedPolicy = await LivestockPolicy.findByPk(id, {
      include: [
        {
          model: LivestockType,
          as: 'livestock_type',
          attributes: ['id', 'name', 'description', 'unit_price_min', 'unit_price_max', 'premium_rate']
        }
      ]
    });
    
    res.json(responseFormat.success(updatedPolicy, '生资保单更新成功'));
  } catch (error) {
    console.error('更新生资保单错误:', error);
    res.status(500).json(responseFormat.error('更新生资保单失败'));
  }
};

// 更新生资保单状态
const updateLivestockPolicyStatus = async (req, res) => {
  try {
    const { id } = req.params;
    const { policy_status, payment_status, payment_date } = req.body;
    
    const policy = await LivestockPolicy.findByPk(id);
    if (!policy) {
      return res.status(404).json(responseFormat.error('生资保单不存在'));
    }
    
    const updateData = {
      updated_by: req.user?.id
    };
    
    if (policy_status) updateData.policy_status = policy_status;
    if (payment_status) updateData.payment_status = payment_status;
    if (payment_date) updateData.payment_date = payment_date;
    
    await policy.update(updateData);
    
    res.json(responseFormat.success(policy, '生资保单状态更新成功'));
  } catch (error) {
    console.error('更新生资保单状态错误:', error);
    res.status(500).json(responseFormat.error('更新生资保单状态失败'));
  }
};

// 获取生资保单统计
const getLivestockPolicyStats = async (req, res) => {
  try {
    const { sequelize } = require('../config/database');
    
    // 总保单数
    const totalPolicies = await LivestockPolicy.count();
    
    // 有效保单数
    const activePolicies = await LivestockPolicy.count({
      where: { policy_status: 'active' }
    });
    
    // 已支付保单数
    const paidPolicies = await LivestockPolicy.count({
      where: { payment_status: 'paid' }
    });
    
    // 总保费收入
    const totalPremium = await LivestockPolicy.sum('premium_amount', {
      where: { payment_status: 'paid' }
    }) || 0;
    
    // 总保额
    const totalCoverage = await LivestockPolicy.sum('total_value', {
      where: { policy_status: 'active' }
    }) || 0;
    
    // 按牲畜类型统计
    const typeStats = await sequelize.query(`
      SELECT 
        lt.name as livestock_type,
        COUNT(lp.id) as policy_count,
        SUM(lp.livestock_count) as total_livestock,
        SUM(lp.total_value) as total_coverage,
        SUM(lp.premium_amount) as total_premium
      FROM livestock_policies lp
      LEFT JOIN livestock_types lt ON lp.livestock_type_id = lt.id
      WHERE lp.policy_status = 'active'
      GROUP BY lt.id, lt.name
      ORDER BY policy_count DESC
    `, { type: sequelize.QueryTypes.SELECT });
    
    // 按月份统计（最近12个月）
    const monthlyStats = await sequelize.query(`
      SELECT 
        DATE_FORMAT(created_at, '%Y-%m') as month,
        COUNT(*) as policy_count,
        SUM(premium_amount) as premium_amount,
        SUM(total_value) as total_coverage
      FROM livestock_policies
      WHERE created_at >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
      GROUP BY DATE_FORMAT(created_at, '%Y-%m')
      ORDER BY month DESC
    `, { type: sequelize.QueryTypes.SELECT });
    
    const stats = {
      overview: {
        total_policies: totalPolicies,
        active_policies: activePolicies,
        paid_policies: paidPolicies,
        total_premium: parseFloat(totalPremium),
        total_coverage: parseFloat(totalCoverage),
        payment_rate: totalPolicies > 0 ? (paidPolicies / totalPolicies * 100).toFixed(2) : 0
      },
      by_type: typeStats,
      monthly: monthlyStats
    };
    
    res.json(responseFormat.success(stats, '获取生资保单统计成功'));
  } catch (error) {
    console.error('获取生资保单统计错误:', error);
    res.status(500).json(responseFormat.error('获取生资保单统计失败'));
  }
};

// 删除生资保单
const deleteLivestockPolicy = async (req, res) => {
  try {
    const { id } = req.params;
    
    const policy = await LivestockPolicy.findByPk(id);
    if (!policy) {
      return res.status(404).json(responseFormat.error('生资保单不存在'));
    }
    
    // 检查保单状态，只有草稿状态的保单可以删除
    if (policy.policy_status === 'active') {
      return res.status(400).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 { policy_no, farmer_name, policy_status } = req.query;
    
    const where = {};
    if (policy_no) where.policy_no = { [Op.like]: `%${policy_no}%` };
    if (farmer_name) where.farmer_name = { [Op.like]: `%${farmer_name}%` };
    if (policy_status) where.policy_status = policy_status;
    
    const policies = await LivestockPolicy.findAll({
      where,
      include: [{
        model: LivestockType,
        as: 'livestockType',
        attributes: ['name']
      }],
      order: [['created_at', 'DESC']],
      raw: true,
      nest: true
    });
    
    const statusMap = { active: '生效中', pending: '待生效', expired: '已过期', cancelled: '已取消' };
    const paymentMap = { unpaid: '未支付', paid: '已支付', partial: '部分支付' };
    
    const exportData = policies.map(policy => ({
      policy_no: policy.policy_no || '',
      farmer_name: policy.farmer_name || '',
      farmer_phone: policy.farmer_phone || '',
      livestock_type: policy.livestockType?.name || '',
      insured_amount: policy.insured_amount || 0,
      premium_amount: policy.premium_amount || 0,
      policy_status: ExcelExport.formatStatus(policy.policy_status, statusMap),
      payment_status: ExcelExport.formatStatus(policy.payment_status, paymentMap),
      start_date: ExcelExport.formatDate(policy.start_date),
      end_date: ExcelExport.formatDate(policy.end_date),
      created_at: ExcelExport.formatDate(policy.created_at)
    }));
    
    const columns = [
      { header: '保单号', key: 'policy_no', width: 20 },
      { header: '农户姓名', key: 'farmer_name', width: 15 },
      { header: '联系电话', key: 'farmer_phone', width: 15 },
      { header: '牲畜类型', key: 'livestock_type', width: 15 },
      { header: '保额', key: 'insured_amount', width: 15 },
      { header: '保费', key: 'premium_amount', width: 15 },
      { header: '保单状态', key: 'policy_status', width: 12 },
      { header: '支付状态', key: 'payment_status', width: 12 },
      { header: '起始日期', key: 'start_date', width: 15 },
      { header: '结束日期', key: 'end_date', width: 15 },
      { header: '创建时间', key: 'created_at', width: 20 }
    ];
    
    const buffer = await ExcelExport.exportToExcel(exportData, columns, '生资保单列表');
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader('Content-Disposition', `attachment; filename=livestock_policies_${Date.now()}.xlsx`);
    res.send(buffer);
  } catch (error) {
    console.error('导出生资保单失败:', error);
    res.status(500).json(responseFormat.error('导出失败'));
  }
};

module.exports = {
  getLivestockPolicies,
  createLivestockPolicy,
  getLivestockPolicyById,
  updateLivestockPolicy,
  updateLivestockPolicyStatus,
  deleteLivestockPolicy,
  getLivestockPolicyStats,
  exportToExcel
};