const { sequelize } = require('../config/database');
const responseFormat = require('../utils/response');
const { Op } = require('sequelize');

/**
 * 监管任务结项控制器
 * 处理监管任务结项相关的业务逻辑
 */

// 获取监管任务结项列表
const getTaskCompletions = async (req, res) => {
  try {
    const {
      application_no,    // 申请编号
      policy_no,         // 保单号
      product_name,      // 产品名称
      customer_name,     // 客户名称
      completion_date,   // 结项日期
      status,           // 状态
      reviewer_name,    // 审核人员
      page = 1,
      limit = 10
    } = req.query;

    // 构建查询条件
    let whereClause = '';
    const params = [];
    
    if (application_no) {
      whereClause += ' AND rtc.application_no LIKE ?';
      params.push(`%${application_no}%`);
    }
    
    if (policy_no) {
      whereClause += ' AND rtc.policy_no LIKE ?';
      params.push(`%${policy_no}%`);
    }
    
    if (product_name) {
      whereClause += ' AND rtc.product_name LIKE ?';
      params.push(`%${product_name}%`);
    }
    
    if (customer_name) {
      whereClause += ' AND rtc.customer_name LIKE ?';
      params.push(`%${customer_name}%`);
    }
    
    if (completion_date) {
      whereClause += ' AND DATE(rtc.completion_date) = ?';
      params.push(completion_date);
    }
    
    if (status) {
      whereClause += ' AND rtc.status = ?';
      params.push(status);
    }
    
    if (reviewer_name) {
      whereClause += ' AND rtc.reviewer_name LIKE ?';
      params.push(`%${reviewer_name}%`);
    }

    // 计算偏移量
    const offset = (page - 1) * limit;

    // 查询总数
    const countQuery = `
      SELECT COUNT(*) as total
      FROM regulatory_task_completions rtc
      WHERE 1=1 ${whereClause}
    `;
    
    const [countResult] = await sequelize.query(countQuery, {
      replacements: params,
      type: sequelize.QueryTypes.SELECT
    });

    // 查询数据
    const dataQuery = `
      SELECT 
        rtc.id,
        rtc.application_no,
        rtc.policy_no,
        rtc.product_name,
        rtc.customer_name,
        rtc.customer_phone,
        rtc.insurance_amount,
        rtc.premium_amount,
        rtc.start_date,
        rtc.end_date,
        rtc.completion_date,
        rtc.status,
        rtc.reviewer_name,
        rtc.review_comments,
        rtc.created_at,
        rtc.updated_at
      FROM regulatory_task_completions rtc
      WHERE 1=1 ${whereClause}
      ORDER BY rtc.created_at DESC
      LIMIT ? OFFSET ?
    `;

    const results = await sequelize.query(dataQuery, {
      replacements: [...params, parseInt(limit), offset],
      type: sequelize.QueryTypes.SELECT
    });

    res.json(responseFormat.success({
      list: results,
      pagination: {
        current: parseInt(page),
        pageSize: parseInt(limit),
        total: countResult.total,
        totalPages: Math.ceil(countResult.total / limit)
      }
    }));

  } catch (error) {
    console.error('获取监管任务结项列表失败:', error);
    res.status(500).json(responseFormat.error('获取监管任务结项列表失败'));
  }
};

// 获取单个监管任务结项详情
const getTaskCompletionById = async (req, res) => {
  try {
    const { id } = req.params;

    const query = `
      SELECT 
        rtc.*,
        (
          SELECT JSON_ARRAYAGG(
            JSON_OBJECT(
              'id', rtca.id,
              'file_name', rtca.file_name,
              'file_path', rtca.file_path,
              'file_size', rtca.file_size,
              'file_type', rtca.file_type,
              'upload_time', rtca.upload_time
            )
          )
          FROM regulatory_task_completion_attachments rtca
          WHERE rtca.completion_id = rtc.id
        ) as attachments,
        (
          SELECT JSON_ARRAYAGG(
            JSON_OBJECT(
              'id', rtcl.id,
              'operation_type', rtcl.operation_type,
              'operation_description', rtcl.operation_description,
              'operator_name', rtcl.operator_name,
              'operation_time', rtcl.operation_time,
              'ip_address', rtcl.ip_address
            )
          )
          FROM regulatory_task_completion_logs rtcl
          WHERE rtcl.completion_id = rtc.id
          ORDER BY rtcl.operation_time DESC
        ) as operation_logs
      FROM regulatory_task_completions rtc
      WHERE rtc.id = ?
    `;

    const [result] = await sequelize.query(query, {
      replacements: [id],
      type: sequelize.QueryTypes.SELECT
    });

    if (!result) {
      return res.status(404).json(responseFormat.error('监管任务结项记录不存在'));
    }

    // 解析JSON字段
    if (result.attachments) {
      result.attachments = JSON.parse(result.attachments) || [];
    } else {
      result.attachments = [];
    }

    if (result.operation_logs) {
      result.operation_logs = JSON.parse(result.operation_logs) || [];
    } else {
      result.operation_logs = [];
    }

    res.json(responseFormat.success(result));

  } catch (error) {
    console.error('获取监管任务结项详情失败:', error);
    res.status(500).json(responseFormat.error('获取监管任务结项详情失败'));
  }
};

// 创建监管任务结项记录
const createTaskCompletion = async (req, res) => {
  const transaction = await sequelize.transaction();
  
  try {
    const {
      application_no,
      policy_no,
      product_name,
      customer_name,
      customer_phone,
      insurance_amount,
      premium_amount,
      start_date,
      end_date,
      completion_date,
      status = 'pending',
      review_comments,
      attachments = []
    } = req.body;

    // 验证必填字段
    if (!application_no || !policy_no || !product_name || !customer_name) {
      return res.status(400).json(responseFormat.error('申请编号、保单号、产品名称、客户名称为必填项'));
    }

    // 检查申请编号是否已存在
    const existingQuery = `
      SELECT id FROM regulatory_task_completions 
      WHERE application_no = ? OR policy_no = ?
    `;
    
    const [existing] = await sequelize.query(existingQuery, {
      replacements: [application_no, policy_no],
      type: sequelize.QueryTypes.SELECT,
      transaction
    });

    if (existing) {
      await transaction.rollback();
      return res.status(400).json(responseFormat.error('申请编号或保单号已存在'));
    }

    // 创建监管任务结项记录
    const insertQuery = `
      INSERT INTO regulatory_task_completions (
        application_no, policy_no, product_name, customer_name, customer_phone,
        insurance_amount, premium_amount, start_date, end_date, completion_date,
        status, review_comments, created_at, updated_at
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())
    `;

    const [insertResult] = await sequelize.query(insertQuery, {
      replacements: [
        application_no, policy_no, product_name, customer_name, customer_phone,
        insurance_amount, premium_amount, start_date, end_date, completion_date,
        status, review_comments
      ],
      type: sequelize.QueryTypes.INSERT,
      transaction
    });

    const completionId = insertResult;

    // 创建附件记录
    if (attachments && attachments.length > 0) {
      for (const attachment of attachments) {
        const attachmentQuery = `
          INSERT INTO regulatory_task_completion_attachments (
            completion_id, file_name, file_path, file_size, file_type, upload_time
          ) VALUES (?, ?, ?, ?, ?, NOW())
        `;
        
        await sequelize.query(attachmentQuery, {
          replacements: [
            completionId,
            attachment.file_name,
            attachment.file_path,
            attachment.file_size,
            attachment.file_type
          ],
          type: sequelize.QueryTypes.INSERT,
          transaction
        });
      }
    }

    // 记录操作日志
    const logQuery = `
      INSERT INTO regulatory_task_completion_logs (
        completion_id, operation_type, operation_description, 
        operator_name, operation_time, ip_address
      ) VALUES (?, ?, ?, ?, NOW(), ?)
    `;

    await sequelize.query(logQuery, {
      replacements: [
        completionId,
        'create',
        '创建监管任务结项记录',
        req.user?.real_name || '系统',
        req.ip
      ],
      type: sequelize.QueryTypes.INSERT,
      transaction
    });

    await transaction.commit();

    res.status(201).json(responseFormat.success({
      id: completionId,
      message: '监管任务结项记录创建成功'
    }));

  } catch (error) {
    await transaction.rollback();
    console.error('创建监管任务结项记录失败:', error);
    res.status(500).json(responseFormat.error('创建监管任务结项记录失败'));
  }
};

// 更新监管任务结项记录
const updateTaskCompletion = async (req, res) => {
  const transaction = await sequelize.transaction();
  
  try {
    const { id } = req.params;
    const {
      application_no,
      policy_no,
      product_name,
      customer_name,
      customer_phone,
      insurance_amount,
      premium_amount,
      start_date,
      end_date,
      completion_date,
      status,
      review_comments
    } = req.body;

    // 检查记录是否存在
    const checkQuery = `SELECT id FROM regulatory_task_completions WHERE id = ?`;
    const [existing] = await sequelize.query(checkQuery, {
      replacements: [id],
      type: sequelize.QueryTypes.SELECT,
      transaction
    });

    if (!existing) {
      await transaction.rollback();
      return res.status(404).json(responseFormat.error('监管任务结项记录不存在'));
    }

    // 更新记录
    const updateQuery = `
      UPDATE regulatory_task_completions SET
        application_no = ?, policy_no = ?, product_name = ?, customer_name = ?,
        customer_phone = ?, insurance_amount = ?, premium_amount = ?,
        start_date = ?, end_date = ?, completion_date = ?, status = ?,
        review_comments = ?, updated_at = NOW()
      WHERE id = ?
    `;

    await sequelize.query(updateQuery, {
      replacements: [
        application_no, policy_no, product_name, customer_name, customer_phone,
        insurance_amount, premium_amount, start_date, end_date, completion_date,
        status, review_comments, id
      ],
      type: sequelize.QueryTypes.UPDATE,
      transaction
    });

    // 记录操作日志
    const logQuery = `
      INSERT INTO regulatory_task_completion_logs (
        completion_id, operation_type, operation_description, 
        operator_name, operation_time, ip_address
      ) VALUES (?, ?, ?, ?, NOW(), ?)
    `;

    await sequelize.query(logQuery, {
      replacements: [
        id,
        'update',
        '更新监管任务结项记录',
        req.user?.real_name || '系统',
        req.ip
      ],
      type: sequelize.QueryTypes.INSERT,
      transaction
    });

    await transaction.commit();

    res.json(responseFormat.success({ message: '监管任务结项记录更新成功' }));

  } catch (error) {
    await transaction.rollback();
    console.error('更新监管任务结项记录失败:', error);
    res.status(500).json(responseFormat.error('更新监管任务结项记录失败'));
  }
};

// 删除监管任务结项记录
const deleteTaskCompletion = async (req, res) => {
  const transaction = await sequelize.transaction();
  
  try {
    const { id } = req.params;

    // 检查记录是否存在
    const checkQuery = `SELECT id FROM regulatory_task_completions WHERE id = ?`;
    const [existing] = await sequelize.query(checkQuery, {
      replacements: [id],
      type: sequelize.QueryTypes.SELECT,
      transaction
    });

    if (!existing) {
      await transaction.rollback();
      return res.status(404).json(responseFormat.error('监管任务结项记录不存在'));
    }

    // 删除相关附件记录
    await sequelize.query(
      'DELETE FROM regulatory_task_completion_attachments WHERE completion_id = ?',
      {
        replacements: [id],
        type: sequelize.QueryTypes.DELETE,
        transaction
      }
    );

    // 删除相关日志记录
    await sequelize.query(
      'DELETE FROM regulatory_task_completion_logs WHERE completion_id = ?',
      {
        replacements: [id],
        type: sequelize.QueryTypes.DELETE,
        transaction
      }
    );

    // 删除主记录
    await sequelize.query(
      'DELETE FROM regulatory_task_completions WHERE id = ?',
      {
        replacements: [id],
        type: sequelize.QueryTypes.DELETE,
        transaction
      }
    );

    await transaction.commit();

    res.json(responseFormat.success({ message: '监管任务结项记录删除成功' }));

  } catch (error) {
    await transaction.rollback();
    console.error('删除监管任务结项记录失败:', error);
    res.status(500).json(responseFormat.error('删除监管任务结项记录失败'));
  }
};

// 审核监管任务结项记录
const reviewTaskCompletion = async (req, res) => {
  const transaction = await sequelize.transaction();
  
  try {
    const { id } = req.params;
    const { status, review_comments, reviewer_name } = req.body;

    // 验证状态值
    const validStatuses = ['pending', 'approved', 'rejected', 'completed'];
    if (!validStatuses.includes(status)) {
      return res.status(400).json(responseFormat.error('无效的状态值'));
    }

    // 检查记录是否存在
    const checkQuery = `SELECT id, status FROM regulatory_task_completions WHERE id = ?`;
    const [existing] = await sequelize.query(checkQuery, {
      replacements: [id],
      type: sequelize.QueryTypes.SELECT,
      transaction
    });

    if (!existing) {
      await transaction.rollback();
      return res.status(404).json(responseFormat.error('监管任务结项记录不存在'));
    }

    // 更新审核状态
    const updateQuery = `
      UPDATE regulatory_task_completions SET
        status = ?, review_comments = ?, reviewer_name = ?, updated_at = NOW()
      WHERE id = ?
    `;

    await sequelize.query(updateQuery, {
      replacements: [status, review_comments, reviewer_name, id],
      type: sequelize.QueryTypes.UPDATE,
      transaction
    });

    // 记录操作日志
    const logQuery = `
      INSERT INTO regulatory_task_completion_logs (
        completion_id, operation_type, operation_description, 
        operator_name, operation_time, ip_address
      ) VALUES (?, ?, ?, ?, NOW(), ?)
    `;

    const operationDesc = `审核监管任务结项记录，状态变更为：${status}`;
    await sequelize.query(logQuery, {
      replacements: [
        id,
        'review',
        operationDesc,
        reviewer_name || req.user?.real_name || '系统',
        req.ip
      ],
      type: sequelize.QueryTypes.INSERT,
      transaction
    });

    await transaction.commit();

    res.json(responseFormat.success({ message: '监管任务结项记录审核成功' }));

  } catch (error) {
    await transaction.rollback();
    console.error('审核监管任务结项记录失败:', error);
    res.status(500).json(responseFormat.error('审核监管任务结项记录失败'));
  }
};

// 获取监管任务结项统计数据
const getTaskCompletionStats = async (req, res) => {
  try {
    // 状态统计
    const statusStatsQuery = `
      SELECT 
        status,
        COUNT(*) as count
      FROM regulatory_task_completions
      GROUP BY status
    `;

    const statusStats = await sequelize.query(statusStatsQuery, {
      type: sequelize.QueryTypes.SELECT
    });

    // 月度统计
    const monthlyStatsQuery = `
      SELECT 
        DATE_FORMAT(completion_date, '%Y-%m') as month,
        COUNT(*) as count,
        SUM(insurance_amount) as total_amount
      FROM regulatory_task_completions
      WHERE completion_date >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
      GROUP BY DATE_FORMAT(completion_date, '%Y-%m')
      ORDER BY month
    `;

    const monthlyStats = await sequelize.query(monthlyStatsQuery, {
      type: sequelize.QueryTypes.SELECT
    });

    // 产品统计
    const productStatsQuery = `
      SELECT 
        product_name,
        COUNT(*) as count,
        SUM(insurance_amount) as total_amount
      FROM regulatory_task_completions
      GROUP BY product_name
      ORDER BY count DESC
      LIMIT 10
    `;

    const productStats = await sequelize.query(productStatsQuery, {
      type: sequelize.QueryTypes.SELECT
    });

    res.json(responseFormat.success({
      statusStats,
      monthlyStats,
      productStats
    }));

  } catch (error) {
    console.error('获取监管任务结项统计数据失败:', error);
    res.status(500).json(responseFormat.error('获取监管任务结项统计数据失败'));
  }
};

module.exports = {
  getTaskCompletions,
  getTaskCompletionById,
  createTaskCompletion,
  updateTaskCompletion,
  deleteTaskCompletion,
  reviewTaskCompletion,
  getTaskCompletionStats
};