const { query } = require('../config/database');

/**
 * 旅行报名数据模型
 * 处理旅行活动报名相关的数据库操作
 */
class TravelRegistration {
  /**
   * 创建报名记录
   * @param {Object} registrationData - 报名数据
   * @returns {Promise<Object>} 创建的报名记录
   */
  static async create(registrationData) {
    const {
      travel_plan_id,
      user_id,
      message,
      emergency_contact,
      emergency_phone
    } = registrationData;

    const query = `
      INSERT INTO travel_registrations 
      (travel_plan_id, user_id, message, emergency_contact, emergency_phone, status, applied_at)
      VALUES (?, ?, ?, ?, ?, 'pending', NOW())
    `;

    const [result] = await query(query, [
      travel_plan_id,
      user_id,
      message || null,
      emergency_contact || null,
      emergency_phone || null
    ]);

    return this.findById(result.insertId);
  }

  /**
   * 根据ID查找报名记录
   * @param {number} id - 报名记录ID
   * @returns {Promise<Object|null>} 报名记录
   */
  static async findById(id) {
    const query = `
      SELECT 
        tr.*,
        u.username,
        u.real_name,
        u.avatar_url,
        tp.title as travel_title,
        tp.destination,
        tp.start_date,
        tp.end_date
      FROM travel_registrations tr
      LEFT JOIN users u ON tr.user_id = u.id
      LEFT JOIN travel_plans tp ON tr.travel_plan_id = tp.id
      WHERE tr.id = ?
    `;

    const [rows] = await query(query, [id]);
    return rows[0] || null;
  }

  /**
   * 检查用户是否已报名某个旅行活动
   * @param {number} userId - 用户ID
   * @param {number} travelPlanId - 旅行活动ID
   * @returns {Promise<Object|null>} 报名记录
   */
  static async findByUserAndTravel(userId, travelPlanId) {
    const query = `
      SELECT * FROM travel_registrations 
      WHERE user_id = ? AND travel_plan_id = ? AND status != 'cancelled'
    `;

    const [rows] = await query(query, [userId, travelPlanId]);
    return rows[0] || null;
  }

  /**
   * 获取用户的报名记录列表
   * @param {number} userId - 用户ID
   * @param {Object} options - 查询选项
   * @returns {Promise<Object>} 报名记录列表和分页信息
   */
  static async findByUser(userId, options = {}) {
    const {
      page = 1,
      pageSize = 10,
      status
    } = options;

    const offset = (page - 1) * pageSize;
    let whereClause = 'WHERE tr.user_id = ?';
    const params = [userId];

    if (status) {
      whereClause += ' AND tr.status = ?';
      params.push(status);
    }

    // 获取总数
    const countQuery = `
      SELECT COUNT(*) as total 
      FROM travel_registrations tr 
      ${whereClause}
    `;
    const [countResult] = await query(countQuery, params);
    const total = countResult[0].total;

    // 获取数据
    const query = `
      SELECT 
        tr.*,
        tp.title as travel_title,
        tp.destination,
        tp.start_date,
        tp.end_date,
        tp.max_participants,
        tp.current_participants
      FROM travel_registrations tr
      LEFT JOIN travel_plans tp ON tr.travel_plan_id = tp.id
      ${whereClause}
      ORDER BY tr.applied_at DESC
      LIMIT ? OFFSET ?
    `;

    params.push(pageSize, offset);
    const [rows] = await query(query, params);

    return {
      registrations: rows,
      pagination: {
        page: parseInt(page),
        pageSize: parseInt(pageSize),
        total,
        totalPages: Math.ceil(total / pageSize)
      }
    };
  }

  /**
   * 获取旅行活动的报名记录列表
   * @param {number} travelPlanId - 旅行活动ID
   * @param {Object} options - 查询选项
   * @returns {Promise<Object>} 报名记录列表和分页信息
   */
  static async findByTravelPlan(travelPlanId, options = {}) {
    const {
      page = 1,
      pageSize = 10,
      status
    } = options;

    const offset = (page - 1) * pageSize;
    let whereClause = 'WHERE tr.travel_plan_id = ?';
    const params = [travelPlanId];

    if (status) {
      whereClause += ' AND tr.status = ?';
      params.push(status);
    }

    // 获取总数
    const countQuery = `
      SELECT COUNT(*) as total 
      FROM travel_registrations tr 
      ${whereClause}
    `;
    const [countResult] = await query(countQuery, params);
    const total = countResult[0].total;

    // 获取数据
    const query = `
      SELECT 
        tr.*,
        u.username,
        u.real_name,
        u.avatar_url,
        u.phone,
        u.email
      FROM travel_registrations tr
      LEFT JOIN users u ON tr.user_id = u.id
      ${whereClause}
      ORDER BY tr.applied_at DESC
      LIMIT ? OFFSET ?
    `;

    params.push(pageSize, offset);
    const [rows] = await query(query, params);

    return {
      registrations: rows,
      pagination: {
        page: parseInt(page),
        pageSize: parseInt(pageSize),
        total,
        totalPages: Math.ceil(total / pageSize)
      }
    };
  }

  /**
   * 更新报名状态
   * @param {number} id - 报名记录ID
   * @param {string} status - 新状态
   * @param {string} rejectReason - 拒绝原因（可选）
   * @returns {Promise<Object>} 更新后的报名记录
   */
  static async updateStatus(id, status, rejectReason = null) {
    const query = `
      UPDATE travel_registrations 
      SET status = ?, reject_reason = ?, responded_at = NOW()
      WHERE id = ?
    `;

    await query(query, [status, rejectReason, id]);
    return this.findById(id);
  }

  /**
   * 取消报名
   * @param {number} id - 报名记录ID
   * @returns {Promise<Object>} 更新后的报名记录
   */
  static async cancel(id) {
    return this.updateStatus(id, 'cancelled');
  }

  /**
   * 获取报名统计信息
   * @param {number} travelPlanId - 旅行活动ID
   * @returns {Promise<Object>} 统计信息
   */
  static async getStats(travelPlanId) {
    const query = `
      SELECT 
        COUNT(*) as total_applications,
        SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending_count,
        SUM(CASE WHEN status = 'approved' THEN 1 ELSE 0 END) as approved_count,
        SUM(CASE WHEN status = 'rejected' THEN 1 ELSE 0 END) as rejected_count,
        SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) as cancelled_count
      FROM travel_registrations 
      WHERE travel_plan_id = ?
    `;

    const [rows] = await query(query, [travelPlanId]);
    return rows[0];
  }

  /**
   * 检查用户是否有权限查看旅行活动的报名列表
   * @param {number} userId - 用户ID
   * @param {number} travelPlanId - 旅行活动ID
   * @returns {Promise<boolean>} 是否有权限
   */
  static async canViewRegistrations(userId, travelPlanId) {
    const query = `
      SELECT id FROM travel_plans 
      WHERE id = ? AND created_by = ?
    `;

    const [rows] = await query(query, [travelPlanId, userId]);
    return rows.length > 0;
  }

  /**
   * 检查用户是否有权限审核报名
   * @param {number} userId - 用户ID
   * @param {number} registrationId - 报名记录ID
   * @returns {Promise<boolean>} 是否有权限
   */
  static async canReviewRegistration(userId, registrationId) {
    const query = `
      SELECT tr.id 
      FROM travel_registrations tr
      JOIN travel_plans tp ON tr.travel_plan_id = tp.id
      WHERE tr.id = ? AND tp.created_by = ?
    `;

    const [rows] = await query(query, [registrationId, userId]);
    return rows.length > 0;
  }

  /**
   * 获取旅行活动的已通过报名数量
   * @param {number} travelPlanId - 旅行活动ID
   * @returns {Promise<number>} 已通过报名数量
   */
  static async getApprovedCount(travelPlanId) {
    const query = `
      SELECT COUNT(*) as count 
      FROM travel_registrations 
      WHERE travel_plan_id = ? AND status = 'approved'
    `;

    const [rows] = await query(query, [travelPlanId]);
    return rows[0].count;
  }

  /**
   * 数据清理方法 - 移除敏感信息
   * @param {Object} registration - 报名记录
   * @returns {Object} 清理后的报名记录
   */
  static sanitize(registration) {
    if (!registration) return null;

    const sanitized = { ...registration };
    
    // 移除敏感信息
    delete sanitized.emergency_phone;
    
    return sanitized;
  }
}

module.exports = TravelRegistration;