ChefTronic/backend/models/Order.js

/**
 * 订单数据模型
 *
 * 功能：封装订单表和订单明细表的数据库操作
 */

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

/**
 * 订单模型类
 */
class Order {
  /**
   * 生成订单编号
   * @returns {string} 订单编号
   */
  static generateOrderNo() {
    // 格式: ORD + 年月日 + 时分秒 + 随机数
    const now = new Date();
    const dateStr = now.toISOString().replace(/[-:T]/g, '').slice(0, 14);
    const random = Math.floor(Math.random() * 1000).toString().padStart(3, '0');
    return `ORD${dateStr}${random}`;
  }

  /**
   * 根据ID查找订单
   * @param {number} id - 订单ID
   * @returns {Promise<object|null>} 订单对象或null
   */
  static async findById(id) {
    try {
      const [rows] = await pool.query(
        `SELECT o.*,
          u1.username as customer_name,
          u2.username as waiter_name
         FROM orders o
         LEFT JOIN users u1 ON o.customer_id = u1.id
         LEFT JOIN users u2 ON o.waiter_id = u2.id
         WHERE o.id = ?`,
        [id]
      );
      return rows[0] || null;
    } catch (error) {
      throw new Error('查询订单失败: ' + error.message);
    }
  }

  /**
   * 根据订单编号查找订单
   * @param {string} orderNo - 订单编号
   * @returns {Promise<object|null>} 订单对象或null
   */
  static async findByOrderNo(orderNo) {
    try {
      const [rows] = await pool.query(
        `SELECT o.*,
          u1.username as customer_name,
          u2.username as waiter_name
         FROM orders o
         LEFT JOIN users u1 ON o.customer_id = u1.id
         LEFT JOIN users u2 ON o.waiter_id = u2.id
         WHERE o.order_no = ?`,
        [orderNo]
      );
      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,
        waiter_id,
        status,
        order_type,
        start_date,
        end_date
      } = options;

      const offset = (page - 1) * pageSize;

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

      if (customer_id) {
        conditions.push('o.customer_id = ?');
        params.push(customer_id);
      }
      if (waiter_id) {
        conditions.push('o.waiter_id = ?');
        params.push(waiter_id);
      }
      if (status) {
        conditions.push('o.status = ?');
        params.push(status);
      }
      if (order_type) {
        conditions.push('o.order_type = ?');
        params.push(order_type);
      }
      if (start_date) {
        conditions.push('DATE(o.created_at) >= ?');
        params.push(start_date);
      }
      if (end_date) {
        conditions.push('DATE(o.created_at) <= ?');
        params.push(end_date);
      }

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

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

      // 查询列表
      const [rows] = await pool.query(
        `SELECT o.*,
          u1.username as customer_name,
          u2.username as waiter_name
         FROM orders o
         LEFT JOIN users u1 ON o.customer_id = u1.id
         LEFT JOIN users u2 ON o.waiter_id = u2.id
         ${whereClause}
         ORDER BY o.created_at 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} orderData - 订单数据
   * @param {array} items - 订单明细数组
   * @returns {Promise<object>} 创建的订单
   */
  static async create(orderData, items) {
    const connection = await pool.getConnection();

    try {
      await connection.beginTransaction();

      const {
        customer_id,
        waiter_id,
        table_number,
        order_type = 'dine_in',
        special_request
      } = orderData;

      // 生成订单编号
      const order_no = this.generateOrderNo();

      // 计算订单金额
      let total_amount = 0;
      for (const item of items) {
        total_amount += item.price * item.quantity;
      }

      const discount_amount = orderData.discount_amount || 0;
      const final_amount = total_amount - discount_amount;

      // 插入订单主表
      const [orderResult] = await connection.query(
        `INSERT INTO orders (order_no, customer_id, waiter_id, table_number,
          total_amount, discount_amount, final_amount, order_type, special_request, status)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending')`,
        [order_no, customer_id, waiter_id, table_number,
         total_amount, discount_amount, final_amount, order_type, special_request]
      );

      const orderId = orderResult.insertId;

      // 插入订单明细
      for (const item of items) {
        const subtotal = item.price * item.quantity;
        await connection.query(
          `INSERT INTO order_items (order_id, dish_id, dish_name, price, quantity, subtotal, special_request)
           VALUES (?, ?, ?, ?, ?, ?, ?)`,
          [orderId, item.dish_id, item.dish_name, item.price, item.quantity, subtotal, item.special_request || null]
        );
      }

      await connection.commit();

      // 返回创建的订单
      return await this.findById(orderId);
    } catch (error) {
      await connection.rollback();
      throw new Error('创建订单失败: ' + error.message);
    } finally {
      connection.release();
    }
  }

  /**
   * 更新订单状态
   * @param {number} id - 订单ID
   * @param {string} status - 新状态
   * @returns {Promise<object>} 更新后的订单
   */
  static async updateStatus(id, status) {
    try {
      await pool.query(
        'UPDATE orders SET status = ? WHERE id = ?',
        [status, id]
      );
      return await this.findById(id);
    } catch (error) {
      throw new Error('更新订单状态失败: ' + error.message);
    }
  }

  /**
   * 取消订单
   * @param {number} id - 订单ID
   * @returns {Promise<object>} 更新后的订单
   */
  static async cancel(id) {
    try {
      await pool.query(
        'UPDATE orders SET status = ? WHERE id = ?',
        ['cancelled', id]
      );
      return await this.findById(id);
    } catch (error) {
      throw new Error('取消订单失败: ' + error.message);
    }
  }

  /**
   * 获取订单明细
   * @param {number} orderId - 订单ID
   * @returns {Promise<array>} 订单明细列表
   */
  static async getOrderItems(orderId) {
    try {
      const [rows] = await pool.query(
        `SELECT oi.*, d.image, d.category_id
         FROM order_items oi
         LEFT JOIN dishes d ON oi.dish_id = d.id
         WHERE oi.order_id = ?
         ORDER BY oi.id`,
        [orderId]
      );
      return rows;
    } catch (error) {
      throw new Error('查询订单明细失败: ' + error.message);
    }
  }

  /**
   * 获取订单完整信息（包含明细）
   * @param {number} id - 订单ID
   * @returns {Promise<object>} 订单完整信息
   */
  static async getOrderWithItems(id) {
    try {
      const order = await this.findById(id);
      if (!order) {
        return null;
      }

      const items = await this.getOrderItems(id);

      return {
        ...order,
        items
      };
    } catch (error) {
      throw new Error('查询订单详情失败: ' + error.message);
    }
  }

  /**
   * 更新订单金额（添加折扣等）
   * @param {number} id - 订单ID
   * @param {number} discountAmount - 折扣金额
   * @returns {Promise<object>} 更新后的订单
   */
  static async updateAmount(id, discountAmount) {
    try {
      const order = await this.findById(id);
      if (!order) {
        throw new Error('订单不存在');
      }

      const final_amount = order.total_amount - discountAmount;

      await pool.query(
        'UPDATE orders SET discount_amount = ?, final_amount = ? WHERE id = ?',
        [discountAmount, final_amount, id]
      );

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

  /**
   * 统计订单数据
   * @param {object} conditions - 统计条件
   * @returns {Promise<object>} 统计结果
   */
  static async getStatistics(conditions = {}) {
    try {
      const { start_date, end_date, status } = conditions;

      const whereConditions = [];
      const params = [];

      if (start_date) {
        whereConditions.push('DATE(created_at) >= ?');
        params.push(start_date);
      }
      if (end_date) {
        whereConditions.push('DATE(created_at) <= ?');
        params.push(end_date);
      }
      if (status) {
        whereConditions.push('status = ?');
        params.push(status);
      }

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

      const [rows] = await pool.query(
        `SELECT
          COUNT(*) as total_orders,
          SUM(final_amount) as total_revenue,
          AVG(final_amount) as avg_order_value,
          MAX(final_amount) as max_order_value,
          MIN(final_amount) as min_order_value
         FROM orders ${whereClause}`,
        params
      );

      return rows[0];
    } catch (error) {
      throw new Error('统计订单数据失败: ' + error.message);
    }
  }

  /**
   * 按状态统计订单数量
   * @returns {Promise<array>} 各状态的订单数量
   */
  static async getStatusStatistics() {
    try {
      const [rows] = await pool.query(
        `SELECT status, COUNT(*) as count
         FROM orders
         GROUP BY status`
      );
      return rows;
    } catch (error) {
      throw new Error('统计订单状态失败: ' + error.message);
    }
  }
}

module.exports = Order;