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

/**
 * 旅行计划数据模型
 * 处理旅行计划相关的数据库操作
 */
class Travel {
  /**
   * 创建旅行计划
   * @param {Object} travelData - 旅行计划数据
   * @returns {Promise<Object>} 创建的旅行计划
   */
  static async create(travelData) {
    const {
      title,
      destination,
      description,
      start_date,
      end_date,
      max_participants,
      price_per_person,
      includes,
      excludes,
      itinerary,
      images,
      requirements,
      created_by
    } = travelData;

    const query = `
      INSERT INTO travel_plans 
      (title, destination, description, start_date, end_date, max_participants, 
       current_participants, price_per_person, includes, excludes, itinerary, 
       images, requirements, created_by, status, created_at, updated_at)
      VALUES (?, ?, ?, ?, ?, ?, 0, ?, ?, ?, ?, ?, ?, ?, 'draft', NOW(), NOW())
    `;

    const [result] = await query(query, [
      title,
      destination,
      description || null,
      start_date,
      end_date,
      max_participants || 10,
      price_per_person,
      JSON.stringify(includes || []),
      JSON.stringify(excludes || []),
      JSON.stringify(itinerary || []),
      JSON.stringify(images || []),
      requirements || null,
      created_by
    ]);

    return this.findById(result.insertId);
  }

  /**
   * 根据ID查找旅行计划
   * @param {number} id - 旅行计划ID
   * @returns {Promise<Object|null>} 旅行计划信息
   */
  static async findById(id) {
    const query = `
      SELECT 
        tp.*,
        u.username as creator_name,
        u.avatar as creator_avatar,
        u.phone as creator_phone
      FROM travel_plans tp
      LEFT JOIN users u ON tp.created_by = u.id
      WHERE tp.id = ?
    `;

    const [rows] = await query(query, [id]);
    
    if (rows.length === 0) {
      return null;
    }

    const travel = rows[0];
    
    // 解析JSON字段
    if (travel.includes) {
      travel.includes = JSON.parse(travel.includes);
    }
    if (travel.excludes) {
      travel.excludes = JSON.parse(travel.excludes);
    }
    if (travel.itinerary) {
      travel.itinerary = JSON.parse(travel.itinerary);
    }
    if (travel.images) {
      travel.images = JSON.parse(travel.images);
    }

    return travel;
  }

  /**
   * 获取旅行计划列表
   * @param {Object} options - 查询选项
   * @returns {Promise<Array>} 旅行计划列表
   */
  static async findAll(options = {}) {
    const {
      page = 1,
      limit = 10,
      destination,
      status,
      created_by,
      start_date,
      end_date,
      sort_by = 'created_at',
      sort_order = 'DESC'
    } = options;

    let whereClause = 'WHERE 1=1';
    const params = [];

    if (destination) {
      whereClause += ' AND tp.destination LIKE ?';
      params.push(`%${destination}%`);
    }

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

    if (created_by) {
      whereClause += ' AND tp.created_by = ?';
      params.push(created_by);
    }

    if (start_date) {
      whereClause += ' AND tp.start_date >= ?';
      params.push(start_date);
    }

    if (end_date) {
      whereClause += ' AND tp.end_date <= ?';
      params.push(end_date);
    }

    const offset = (page - 1) * limit;

    const query = `
      SELECT 
        tp.*,
        u.username as creator_name,
        u.avatar as creator_avatar
      FROM travel_plans tp
      LEFT JOIN users u ON tp.created_by = u.id
      ${whereClause}
      ORDER BY tp.${sort_by} ${sort_order}
      LIMIT ? OFFSET ?
    `;

    const [rows] = await query(query, [...params, limit, offset]);
    
    // 解析JSON字段
    return rows.map(travel => {
      if (travel.includes) {
        travel.includes = JSON.parse(travel.includes);
      }
      if (travel.excludes) {
        travel.excludes = JSON.parse(travel.excludes);
      }
      if (travel.itinerary) {
        travel.itinerary = JSON.parse(travel.itinerary);
      }
      if (travel.images) {
        travel.images = JSON.parse(travel.images);
      }
      return travel;
    });
  }

  /**
   * 获取旅行计划总数
   * @param {Object} options - 查询选项
   * @returns {Promise<number>} 总数
   */
  static async count(options = {}) {
    const {
      destination,
      status,
      created_by,
      start_date,
      end_date
    } = options;

    let whereClause = 'WHERE 1=1';
    const params = [];

    if (destination) {
      whereClause += ' AND destination LIKE ?';
      params.push(`%${destination}%`);
    }

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

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

    if (start_date) {
      whereClause += ' AND start_date >= ?';
      params.push(start_date);
    }

    if (end_date) {
      whereClause += ' AND end_date <= ?';
      params.push(end_date);
    }

    const query = `SELECT COUNT(*) as count FROM travel_plans ${whereClause}`;
    const [rows] = await query(query, params);
    return rows[0].count;
  }

  /**
   * 更新旅行计划
   * @param {number} id - 旅行计划ID
   * @param {Object} updateData - 更新数据
   * @returns {Promise<Object|null>} 更新后的旅行计划
   */
  static async update(id, updateData) {
    const {
      title,
      destination,
      description,
      start_date,
      end_date,
      max_participants,
      price_per_person,
      includes,
      excludes,
      itinerary,
      images,
      requirements,
      status
    } = updateData;

    const fields = [];
    const params = [];

    if (title !== undefined) {
      fields.push('title = ?');
      params.push(title);
    }
    if (destination !== undefined) {
      fields.push('destination = ?');
      params.push(destination);
    }
    if (description !== undefined) {
      fields.push('description = ?');
      params.push(description);
    }
    if (start_date !== undefined) {
      fields.push('start_date = ?');
      params.push(start_date);
    }
    if (end_date !== undefined) {
      fields.push('end_date = ?');
      params.push(end_date);
    }
    if (max_participants !== undefined) {
      fields.push('max_participants = ?');
      params.push(max_participants);
    }
    if (price_per_person !== undefined) {
      fields.push('price_per_person = ?');
      params.push(price_per_person);
    }
    if (includes !== undefined) {
      fields.push('includes = ?');
      params.push(JSON.stringify(includes));
    }
    if (excludes !== undefined) {
      fields.push('excludes = ?');
      params.push(JSON.stringify(excludes));
    }
    if (itinerary !== undefined) {
      fields.push('itinerary = ?');
      params.push(JSON.stringify(itinerary));
    }
    if (images !== undefined) {
      fields.push('images = ?');
      params.push(JSON.stringify(images));
    }
    if (requirements !== undefined) {
      fields.push('requirements = ?');
      params.push(requirements);
    }
    if (status !== undefined) {
      fields.push('status = ?');
      params.push(status);
    }

    if (fields.length === 0) {
      return this.findById(id);
    }

    fields.push('updated_at = NOW()');
    params.push(id);

    const query = `UPDATE travel_plans SET ${fields.join(', ')} WHERE id = ?`;
    await query(query, params);

    return this.findById(id);
  }

  /**
   * 删除旅行计划
   * @param {number} id - 旅行计划ID
   * @returns {Promise<boolean>} 是否删除成功
   */
  static async delete(id) {
    const query = 'DELETE FROM travel_plans WHERE id = ?';
    const [result] = await query(query, [id]);
    return result.affectedRows > 0;
  }

  /**
   * 增加参与人数
   * @param {number} id - 旅行计划ID
   * @param {number} count - 增加的人数
   * @returns {Promise<boolean>} 是否更新成功
   */
  static async incrementParticipants(id, count = 1) {
    const query = `
      UPDATE travel_plans 
      SET current_participants = current_participants + ?, updated_at = NOW()
      WHERE id = ? AND current_participants + ? <= max_participants
    `;
    const [result] = await query(query, [count, id, count]);
    return result.affectedRows > 0;
  }

  /**
   * 减少参与人数
   * @param {number} id - 旅行计划ID
   * @param {number} count - 减少的人数
   * @returns {Promise<boolean>} 是否更新成功
   */
  static async decrementParticipants(id, count = 1) {
    const query = `
      UPDATE travel_plans 
      SET current_participants = GREATEST(0, current_participants - ?), updated_at = NOW()
      WHERE id = ?
    `;
    const [result] = await query(query, [count, id]);
    return result.affectedRows > 0;
  }

  /**
   * 检查是否可以报名
   * @param {number} id - 旅行计划ID
   * @returns {Promise<boolean>} 是否可以报名
   */
  static async canRegister(id) {
    const query = `
      SELECT 
        current_participants < max_participants as can_register,
        status = 'published' as is_published,
        start_date > NOW() as not_started
      FROM travel_plans 
      WHERE id = ?
    `;
    const [rows] = await query(query, [id]);
    
    if (rows.length === 0) {
      return false;
    }

    const { can_register, is_published, not_started } = rows[0];
    return can_register && is_published && not_started;
  }

  /**
   * 获取热门旅行计划
   * @param {number} limit - 限制数量
   * @returns {Promise<Array>} 热门旅行计划列表
   */
  static async getPopular(limit = 10) {
    const query = `
      SELECT 
        tp.*,
        u.username as creator_name,
        u.avatar as creator_avatar,
        COUNT(tr.id) as registration_count
      FROM travel_plans tp
      LEFT JOIN users u ON tp.created_by = u.id
      LEFT JOIN travel_registrations tr ON tp.id = tr.travel_plan_id AND tr.status = 'approved'
      WHERE tp.status = 'published' AND tp.start_date > NOW()
      GROUP BY tp.id
      ORDER BY registration_count DESC, tp.created_at DESC
      LIMIT ?
    `;

    const [rows] = await query(query, [limit]);
    
    // 解析JSON字段
    return rows.map(travel => {
      if (travel.includes) {
        travel.includes = JSON.parse(travel.includes);
      }
      if (travel.excludes) {
        travel.excludes = JSON.parse(travel.excludes);
      }
      if (travel.itinerary) {
        travel.itinerary = JSON.parse(travel.itinerary);
      }
      if (travel.images) {
        travel.images = JSON.parse(travel.images);
      }
      return travel;
    });
  }
}

module.exports = Travel;