/**
 * 菜单数据模型
 *
 * 功能：封装菜单表和菜单-菜品关联表的数据库操作
 */

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

/**
 * 菜单模型类
 */
class Menu {
  /**
   * 根据ID查找菜单
   * @param {number} id - 菜单ID
   * @returns {Promise<object|null>} 菜单对象或null
   */
  static async findById(id) {
    try {
      const [rows] = await pool.query(
        'SELECT * FROM menus WHERE 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,
        type,
        is_active
      } = options;

      const offset = (page - 1) * pageSize;

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

      if (type) {
        conditions.push('type = ?');
        params.push(type);
      }
      if (is_active !== undefined) {
        conditions.push('is_active = ?');
        params.push(is_active);
      }

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

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

      // 查询列表
      const [rows] = await pool.query(
        `SELECT * FROM menus ${whereClause}
         ORDER BY 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} menuData - 菜单数据
   * @returns {Promise<object>} 创建的菜单
   */
  static async create(menuData) {
    try {
      const {
        name,
        type,
        start_date,
        end_date,
        is_active = 1
      } = menuData;

      const [result] = await pool.query(
        `INSERT INTO menus (name, type, start_date, end_date, is_active)
         VALUES (?, ?, ?, ?, ?)`,
        [name, type, start_date, end_date, is_active]
      );

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

  /**
   * 更新菜单
   * @param {number} id - 菜单ID
   * @param {object} menuData - 要更新的数据
   * @returns {Promise<object>} 更新后的菜单
   */
  static async update(id, menuData) {
    try {
      const { name, type, start_date, end_date, is_active } = menuData;

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

      if (name !== undefined) {
        updates.push('name = ?');
        values.push(name);
      }
      if (type !== undefined) {
        updates.push('type = ?');
        values.push(type);
      }
      if (start_date !== undefined) {
        updates.push('start_date = ?');
        values.push(start_date);
      }
      if (end_date !== undefined) {
        updates.push('end_date = ?');
        values.push(end_date);
      }
      if (is_active !== undefined) {
        updates.push('is_active = ?');
        values.push(is_active);
      }

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

      values.push(id);

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

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

  /**
   * 删除菜单
   * @param {number} id - 菜单ID
   * @returns {Promise<boolean>} 是否成功
   */
  static async delete(id) {
    try {
      // 删除菜单会自动删除关联的菜品（CASCADE）
      await pool.query('DELETE FROM menus WHERE id = ?', [id]);
      return true;
    } catch (error) {
      throw new Error('删除菜单失败: ' + error.message);
    }
  }

  /**
   * 获取菜单中的菜品列表
   * @param {number} menuId - 菜单ID
   * @returns {Promise<array>} 菜品列表
   */
  static async getMenuDishes(menuId) {
    try {
      const [rows] = await pool.query(
        `SELECT
          md.id as menu_dish_id,
          md.special_price,
          d.id as dish_id,
          d.name,
          d.price as original_price,
          d.image,
          d.description,
          d.taste,
          d.cooking_time,
          d.is_available,
          dc.name as category_name
         FROM menu_dishes md
         JOIN dishes d ON md.dish_id = d.id
         LEFT JOIN dish_categories dc ON d.category_id = dc.id
         WHERE md.menu_id = ?
         ORDER BY d.category_id, d.id`,
        [menuId]
      );

      // 计算实际价格
      return rows.map(row => ({
        ...row,
        effective_price: row.special_price || row.original_price
      }));
    } catch (error) {
      throw new Error('查询菜单菜品失败: ' + error.message);
    }
  }

  /**
   * 为菜单添加菜品
   * @param {number} menuId - 菜单ID
   * @param {number} dishId - 菜品ID
   * @param {number} specialPrice - 特价（可选）
   * @returns {Promise<object>} 添加的记录
   */
  static async addDish(menuId, dishId, specialPrice = null) {
    try {
      // 检查是否已存在
      const [existing] = await pool.query(
        'SELECT id FROM menu_dishes WHERE menu_id = ? AND dish_id = ?',
        [menuId, dishId]
      );

      if (existing.length > 0) {
        throw new Error('该菜品已在菜单中');
      }

      const [result] = await pool.query(
        'INSERT INTO menu_dishes (menu_id, dish_id, special_price) VALUES (?, ?, ?)',
        [menuId, dishId, specialPrice]
      );

      // 返回添加的菜品信息
      const [rows] = await pool.query(
        `SELECT
          md.id,
          md.special_price,
          d.id as dish_id,
          d.name,
          d.price as original_price
         FROM menu_dishes md
         JOIN dishes d ON md.dish_id = d.id
         WHERE md.id = ?`,
        [result.insertId]
      );

      return rows[0];
    } catch (error) {
      throw new Error('添加菜品到菜单失败: ' + error.message);
    }
  }

  /**
   * 从菜单移除菜品
   * @param {number} menuId - 菜单ID
   * @param {number} dishId - 菜品ID
   * @returns {Promise<boolean>} 是否成功
   */
  static async removeDish(menuId, dishId) {
    try {
      const [result] = await pool.query(
        'DELETE FROM menu_dishes WHERE menu_id = ? AND dish_id = ?',
        [menuId, dishId]
      );

      if (result.affectedRows === 0) {
        throw new Error('菜品不在该菜单中');
      }

      return true;
    } catch (error) {
      throw new Error('从菜单移除菜品失败: ' + error.message);
    }
  }

  /**
   * 更新菜单中菜品的特价
   * @param {number} menuId - 菜单ID
   * @param {number} dishId - 菜品ID
   * @param {number} specialPrice - 特价
   * @returns {Promise<boolean>} 是否成功
   */
  static async updateDishPrice(menuId, dishId, specialPrice) {
    try {
      const [result] = await pool.query(
        'UPDATE menu_dishes SET special_price = ? WHERE menu_id = ? AND dish_id = ?',
        [specialPrice, menuId, dishId]
      );

      if (result.affectedRows === 0) {
        throw new Error('菜品不在该菜单中');
      }

      return true;
    } catch (error) {
      throw new Error('更新菜品特价失败: ' + error.message);
    }
  }

  /**
   * 批量添加菜品到菜单
   * @param {number} menuId - 菜单ID
   * @param {array} dishes - 菜品数组 [{dish_id, special_price}]
   * @returns {Promise<number>} 成功添加的数量
   */
  static async addDishes(menuId, dishes) {
    const connection = await pool.getConnection();

    try {
      await connection.beginTransaction();

      let successCount = 0;

      for (const dish of dishes) {
        try {
          await connection.query(
            'INSERT INTO menu_dishes (menu_id, dish_id, special_price) VALUES (?, ?, ?)',
            [menuId, dish.dish_id, dish.special_price || null]
          );
          successCount++;
        } catch (err) {
          // 忽略已存在的记录
          if (err.code !== 'ER_DUP_ENTRY') {
            throw err;
          }
        }
      }

      await connection.commit();
      return successCount;
    } catch (error) {
      await connection.rollback();
      throw new Error('批量添加菜品失败: ' + error.message);
    } finally {
      connection.release();
    }
  }

  /**
   * 获取当前有效的菜单
   * @param {string} type - 菜单类型
   * @returns {Promise<array>} 菜单列表
   */
  static async getActiveMenus(type = null) {
    try {
      const today = new Date().toISOString().split('T')[0];

      let query = `
        SELECT * FROM menus
        WHERE is_active = 1
        AND (start_date IS NULL OR start_date <= ?)
        AND (end_date IS NULL OR end_date >= ?)
      `;

      const params = [today, today];

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

      query += ' ORDER BY created_at DESC';

      const [rows] = await pool.query(query, params);
      return rows;
    } catch (error) {
      throw new Error('查询有效菜单失败: ' + error.message);
    }
  }
}

module.exports = Menu;