/**
 * 预订数据模型
 *
 * 功能：封装预订表的数据库操作
 */

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

/**
 * 预订模型类
 */
class Reservation {
  /**
   * 根据ID查找预订
   * @param {number} id - 预订ID
   * @returns {Promise<object|null>} 预订对象或null
   */
  static async findById(id) {
    try {
      const [rows] = await pool.query(
        `SELECT
          r.*,
          u.username as customer_name,
          u.phone as customer_phone
         FROM reservations r
         LEFT JOIN users u ON r.customer_id = u.id
         WHERE r.id = ?`,
        [id]
      );
      return rows[0] || null;
    } catch (error) {
      throw new Error('查询预订失败: ' + error.message);
    }
  }

  /**
   * 获取预订列表（分页、筛选）
   * @param {object} options - 查询选项
   * @returns {Promise<object>} 预订列表和总数
   */
  static async findAll(options = {}) {
    try {
      const {
        page = 1,
        pageSize = 10,
        customer_id,
        type,
        status,
        date,
        table_number
      } = options;

      const offset = (page - 1) * pageSize;

      // 构建WHERE条件
      const conditions = [];
      const params = [];

      if (customer_id) {
        conditions.push('r.customer_id = ?');
        params.push(customer_id);
      }

      if (type) {
        conditions.push('r.type = ?');
        params.push(type);
      }

      if (status) {
        conditions.push('r.status = ?');
        params.push(status);
      }

      if (date) {
        conditions.push('DATE(r.reservation_time) = ?');
        params.push(date);
      }

      if (table_number) {
        conditions.push('r.table_number = ?');
        params.push(table_number);
      }

      const whereClause = conditions.length > 0
        ? 'WHERE ' + conditions.join(' AND ')
        : '';

      // 查询总数
      const [countResult] = await pool.query(
        `SELECT COUNT(*) as total FROM reservations r ${whereClause}`,
        params
      );
      const total = countResult[0].total;

      // 查询列表
      const [rows] = await pool.query(
        `SELECT
          r.*,
          u.username as customer_name,
          u.phone as customer_phone
         FROM reservations r
         LEFT JOIN users u ON r.customer_id = u.id
         ${whereClause}
         ORDER BY r.reservation_time DESC
         LIMIT ? OFFSET ?`,
        [...params, pageSize, offset]
      );

      return {
        list: rows,
        total,
        page: parseInt(page),
        pageSize: parseInt(pageSize)
      };
    } catch (error) {
      throw new Error('查询预订列表失败: ' + error.message);
    }
  }

  /**
   * 创建新预订
   * @param {object} reservationData - 预订数据
   * @returns {Promise<object>} 创建的预订
   */
  static async create(reservationData) {
    try {
      const {
        customer_id,
        type,
        reservation_time,
        guest_count,
        table_number,
        contact_name,
        contact_phone,
        special_request
      } = reservationData;

      const [result] = await pool.query(
        `INSERT INTO reservations
         (customer_id, type, reservation_time, guest_count, table_number,
          contact_name, contact_phone, special_request, status)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'pending')`,
        [
          customer_id,
          type,
          reservation_time,
          guest_count,
          table_number || null,
          contact_name,
          contact_phone,
          special_request || null
        ]
      );

      return await this.findById(result.insertId);
    } catch (error) {
      throw new Error('创建预订失败: ' + error.message);
    }
  }

  /**
   * 更新预订
   * @param {number} id - 预订ID
   * @param {object} reservationData - 要更新的数据
   * @returns {Promise<object>} 更新后的预订
   */
  static async update(id, reservationData) {
    try {
      const {
        reservation_time,
        guest_count,
        table_number,
        contact_name,
        contact_phone,
        special_request
      } = reservationData;

      const updates = [];
      const values = [];

      if (reservation_time !== undefined) {
        updates.push('reservation_time = ?');
        values.push(reservation_time);
      }
      if (guest_count !== undefined) {
        updates.push('guest_count = ?');
        values.push(guest_count);
      }
      if (table_number !== undefined) {
        updates.push('table_number = ?');
        values.push(table_number);
      }
      if (contact_name !== undefined) {
        updates.push('contact_name = ?');
        values.push(contact_name);
      }
      if (contact_phone !== undefined) {
        updates.push('contact_phone = ?');
        values.push(contact_phone);
      }
      if (special_request !== undefined) {
        updates.push('special_request = ?');
        values.push(special_request);
      }

      if (updates.length === 0) {
        throw new Error('没有要更新的数据');
      }

      values.push(id);

      await pool.query(
        `UPDATE reservations SET ${updates.join(', ')} WHERE id = ?`,
        values
      );

      return await this.findById(id);
    } catch (error) {
      throw new Error('更新预订失败: ' + error.message);
    }
  }

  /**
   * 更新预订状态
   * @param {number} id - 预订ID
   * @param {string} status - 新状态
   * @returns {Promise<object>} 更新后的预订
   */
  static async updateStatus(id, status) {
    try {
      await pool.query(
        'UPDATE reservations SET status = ?, updated_at = NOW() WHERE id = ?',
        [status, id]
      );

      return await this.findById(id);
    } catch (error) {
      throw new Error('更新预订状态失败: ' + error.message);
    }
  }

  /**
   * 取消预订
   * @param {number} id - 预订ID
   * @param {string} cancelReason - 取消原因
   * @returns {Promise<object>} 更新后的预订
   */
  static async cancel(id, cancelReason = null) {
    try {
      await pool.query(
        `UPDATE reservations
         SET status = 'cancelled', cancel_reason = ?, updated_at = NOW()
         WHERE id = ?`,
        [cancelReason, id]
      );

      return await this.findById(id);
    } catch (error) {
      throw new Error('取消预订失败: ' + error.message);
    }
  }

  /**
   * 删除预订
   * @param {number} id - 预订ID
   * @returns {Promise<boolean>} 是否成功
   */
  static async delete(id) {
    try {
      await pool.query('DELETE FROM reservations WHERE id = ?', [id]);
      return true;
    } catch (error) {
      throw new Error('删除预订失败: ' + error.message);
    }
  }

  /**
   * 检查桌位是否可用
   * @param {number} tableNumber - 桌号
   * @param {string} reservationTime - 预订时间
   * @param {number} excludeId - 排除的预订ID（用于更新时）
   * @returns {Promise<boolean>} 是否可用
   */
  static async isTableAvailable(tableNumber, reservationTime, excludeId = null) {
    try {
      // 检查前后2小时内是否有冲突的预订
      const query = excludeId
        ? `SELECT id FROM reservations
           WHERE table_number = ?
           AND type = 'dine_in'
           AND status IN ('pending', 'confirmed')
           AND id != ?
           AND ABS(TIMESTAMPDIFF(MINUTE, reservation_time, ?)) < 120`
        : `SELECT id FROM reservations
           WHERE table_number = ?
           AND type = 'dine_in'
           AND status IN ('pending', 'confirmed')
           AND ABS(TIMESTAMPDIFF(MINUTE, reservation_time, ?)) < 120`;

      const params = excludeId
        ? [tableNumber, excludeId, reservationTime]
        : [tableNumber, reservationTime];

      const [rows] = await pool.query(query, params);

      return rows.length === 0;
    } catch (error) {
      throw new Error('检查桌位可用性失败: ' + error.message);
    }
  }

  /**
   * 获取指定时间段内的预订列表
   * @param {string} startTime - 开始时间
   * @param {string} endTime - 结束时间
   * @param {string} type - 预订类型（可选）
   * @returns {Promise<array>} 预订列表
   */
  static async findByTimeRange(startTime, endTime, type = null) {
    try {
      let query = `
        SELECT
          r.*,
          u.username as customer_name,
          u.phone as customer_phone
        FROM reservations r
        LEFT JOIN users u ON r.customer_id = u.id
        WHERE r.reservation_time >= ?
        AND r.reservation_time <= ?
        AND r.status IN ('pending', 'confirmed')
      `;

      const params = [startTime, endTime];

      if (type) {
        query += ' AND r.type = ?';
        params.push(type);
      }

      query += ' ORDER BY r.reservation_time ASC';

      const [rows] = await pool.query(query, params);
      return rows;
    } catch (error) {
      throw new Error('查询时间段预订失败: ' + error.message);
    }
  }

  /**
   * 获取今日预订列表
   * @param {string} type - 预订类型（可选）
   * @returns {Promise<array>} 今日预订列表
   */
  static async getTodayReservations(type = null) {
    try {
      const today = new Date().toISOString().split('T')[0];

      let query = `
        SELECT
          r.*,
          u.username as customer_name,
          u.phone as customer_phone
        FROM reservations r
        LEFT JOIN users u ON r.customer_id = u.id
        WHERE DATE(r.reservation_time) = ?
        AND r.status IN ('pending', 'confirmed')
      `;

      const params = [today];

      if (type) {
        query += ' AND r.type = ?';
        params.push(type);
      }

      query += ' ORDER BY r.reservation_time ASC';

      const [rows] = await pool.query(query, params);
      return rows;
    } catch (error) {
      throw new Error('查询今日预订失败: ' + error.message);
    }
  }

  /**
   * 获取即将到来的预订（未来24小时内）
   * @param {number} customerId - 顾客ID（可选）
   * @returns {Promise<array>} 预订列表
   */
  static async getUpcomingReservations(customerId = null) {
    try {
      let query = `
        SELECT
          r.*,
          u.username as customer_name,
          u.phone as customer_phone
        FROM reservations r
        LEFT JOIN users u ON r.customer_id = u.id
        WHERE r.reservation_time >= NOW()
        AND r.reservation_time <= DATE_ADD(NOW(), INTERVAL 24 HOUR)
        AND r.status IN ('pending', 'confirmed')
      `;

      const params = [];

      if (customerId) {
        query += ' AND r.customer_id = ?';
        params.push(customerId);
      }

      query += ' ORDER BY r.reservation_time ASC';

      const [rows] = await pool.query(query, params);
      return rows;
    } catch (error) {
      throw new Error('查询即将到来的预订失败: ' + error.message);
    }
  }

  /**
   * 获取预订统计信息
   * @param {string} startDate - 开始日期（可选）
   * @param {string} endDate - 结束日期（可选）
   * @returns {Promise<object>} 统计信息
   */
  static async getStatistics(startDate = null, endDate = null) {
    try {
      let whereClause = '';
      const params = [];

      if (startDate && endDate) {
        whereClause = 'WHERE DATE(reservation_time) BETWEEN ? AND ?';
        params.push(startDate, endDate);
      }

      // 总预订数和各状态数量
      const [statusStats] = await pool.query(
        `SELECT
          COUNT(*) as total_reservations,
          SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending_count,
          SUM(CASE WHEN status = 'confirmed' THEN 1 ELSE 0 END) as confirmed_count,
          SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed_count,
          SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) as cancelled_count,
          SUM(CASE WHEN status = 'no_show' THEN 1 ELSE 0 END) as no_show_count
         FROM reservations ${whereClause}`,
        params
      );

      // 按类型统计
      const [typeStats] = await pool.query(
        `SELECT
          type,
          COUNT(*) as count,
          SUM(guest_count) as total_guests
         FROM reservations ${whereClause}
         GROUP BY type`,
        params
      );

      return {
        ...statusStats[0],
        by_type: typeStats
      };
    } catch (error) {
      throw new Error('查询预订统计失败: ' + error.message);
    }
  }

  /**
   * 获取可用桌位列表
   * @param {string} reservationTime - 预订时间
   * @param {number} guestCount - 客人数量（可选，用于推荐合适的桌位）
   * @returns {Promise<array>} 可用桌位列表
   */
  static async getAvailableTables(reservationTime, guestCount = null) {
    try {
      // 获取所有已被预订的桌位（前后2小时内）
      const [bookedTables] = await pool.query(
        `SELECT DISTINCT table_number
         FROM reservations
         WHERE table_number IS NOT NULL
         AND type = 'dine_in'
         AND status IN ('pending', 'confirmed')
         AND ABS(TIMESTAMPDIFF(MINUTE, reservation_time, ?)) < 120`,
        [reservationTime]
      );

      const bookedTableNumbers = bookedTables.map(t => t.table_number);

      // 简化版：返回1-20号桌中未被预订的桌位
      const allTables = Array.from({ length: 20 }, (_, i) => ({
        table_number: i + 1,
        capacity: i < 10 ? 4 : 8, // 前10桌4人桌，后10桌8人桌
        is_available: !bookedTableNumbers.includes(i + 1)
      }));

      // 如果提供了客人数量，优先推荐合适容量的桌位
      if (guestCount) {
        return allTables
          .filter(t => t.is_available)
          .sort((a, b) => {
            const aDiff = Math.abs(a.capacity - guestCount);
            const bDiff = Math.abs(b.capacity - guestCount);
            return aDiff - bDiff;
          });
      }

      return allTables.filter(t => t.is_available);
    } catch (error) {
      throw new Error('查询可用桌位失败: ' + error.message);
    }
  }
}

module.exports = Reservation;