/**
 * 库存数据模型
 *
 * 功能：封装库存表和库存日志表的数据库操作
 */

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

/**
 * 库存模型类
 */
class Inventory {
  /**
   * 根据ID查找库存项
   * @param {number} id - 库存ID
   * @returns {Promise<object|null>} 库存对象或null
   */
  static async findById(id) {
    try {
      const [rows] = await pool.query(
        'SELECT * FROM inventory WHERE id = ?',
        [id]
      );
      return rows[0] || null;
    } catch (error) {
      throw new Error('查询库存失败: ' + error.message);
    }
  }

  /**
   * 根据原材料名称查找库存项
   * @param {string} name - 原材料名称
   * @returns {Promise<object|null>} 库存对象或null
   */
  static async findByName(name) {
    try {
      const [rows] = await pool.query(
        'SELECT * FROM inventory WHERE name = ?',
        [name]
      );
      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,
        category,
        search,
        low_stock_only = false
      } = options;

      const offset = (page - 1) * pageSize;

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

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

      if (search) {
        conditions.push('(name LIKE ? OR supplier LIKE ?)');
        params.push(`%${search}%`, `%${search}%`);
      }

      // 低库存筛选
      if (low_stock_only) {
        conditions.push('quantity <= alert_quantity');
      }

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

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

      // 查询列表
      const [rows] = await pool.query(
        `SELECT * FROM inventory ${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} inventoryData - 库存数据
   * @returns {Promise<object>} 创建的库存项
   */
  static async create(inventoryData) {
    try {
      const {
        name,
        category,
        quantity,
        unit,
        purchase_price,
        supplier,
        expiry_date,
        alert_quantity
      } = inventoryData;

      const [result] = await pool.query(
        `INSERT INTO inventory
         (name, category, quantity, unit, purchase_price, supplier, expiry_date, alert_quantity)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
        [name, category, quantity, unit, purchase_price, supplier, expiry_date || null, alert_quantity]
      );

      // 记录库存日志
      await this.addLog({
        inventory_id: result.insertId,
        change_type: 'in',
        quantity_change: quantity,
        quantity_after: quantity,
        operator_id: inventoryData.operator_id,
        remark: '初始入库'
      });

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

  /**
   * 更新库存项
   * @param {number} id - 库存ID
   * @param {object} inventoryData - 要更新的数据
   * @returns {Promise<object>} 更新后的库存项
   */
  static async update(id, inventoryData) {
    try {
      const {
        name,
        category,
        unit,
        purchase_price,
        supplier,
        expiry_date,
        alert_quantity
      } = inventoryData;

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

      if (name !== undefined) {
        updates.push('name = ?');
        values.push(name);
      }
      if (category !== undefined) {
        updates.push('category = ?');
        values.push(category);
      }
      if (unit !== undefined) {
        updates.push('unit = ?');
        values.push(unit);
      }
      if (purchase_price !== undefined) {
        updates.push('purchase_price = ?');
        values.push(purchase_price);
      }
      if (supplier !== undefined) {
        updates.push('supplier = ?');
        values.push(supplier);
      }
      if (expiry_date !== undefined) {
        updates.push('expiry_date = ?');
        values.push(expiry_date);
      }
      if (alert_quantity !== undefined) {
        updates.push('alert_quantity = ?');
        values.push(alert_quantity);
      }

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

      values.push(id);

      await pool.query(
        `UPDATE inventory 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 {
      await pool.query('DELETE FROM inventory WHERE id = ?', [id]);
      return true;
    } catch (error) {
      throw new Error('删除库存失败: ' + error.message);
    }
  }

  /**
   * 调整库存数量（入库/出库）
   * @param {number} id - 库存ID
   * @param {string} changeType - 变更类型 (in/out/adjust)
   * @param {number} quantityChange - 数量变化（正数表示增加，负数表示减少）
   * @param {number} operatorId - 操作人ID
   * @param {string} remark - 备注
   * @returns {Promise<object>} 更新后的库存项
   */
  static async adjustQuantity(id, changeType, quantityChange, operatorId, remark = null) {
    const connection = await pool.getConnection();

    try {
      await connection.beginTransaction();

      // 获取当前库存
      const [rows] = await connection.query(
        'SELECT * FROM inventory WHERE id = ?',
        [id]
      );

      if (rows.length === 0) {
        throw new Error('库存项不存在');
      }

      const currentItem = rows[0];
      let newQuantity;

      // 计算新数量
      if (changeType === 'in') {
        newQuantity = parseFloat(currentItem.quantity) + parseFloat(quantityChange);
      } else if (changeType === 'out') {
        newQuantity = parseFloat(currentItem.quantity) - parseFloat(quantityChange);
        if (newQuantity < 0) {
          throw new Error('库存不足，无法出库');
        }
      } else if (changeType === 'adjust') {
        // 直接调整到目标数量
        newQuantity = parseFloat(quantityChange);
      } else {
        throw new Error('无效的变更类型');
      }

      // 更新库存数量
      await connection.query(
        'UPDATE inventory SET quantity = ?, updated_at = NOW() WHERE id = ?',
        [newQuantity, id]
      );

      // 记录库存日志
      const actualChange = changeType === 'adjust'
        ? (newQuantity - parseFloat(currentItem.quantity))
        : (changeType === 'in' ? quantityChange : -quantityChange);

      await connection.query(
        `INSERT INTO inventory_logs
         (inventory_id, change_type, quantity_change, quantity_after, operator_id, remark)
         VALUES (?, ?, ?, ?, ?, ?)`,
        [id, changeType, actualChange, newQuantity, operatorId, remark]
      );

      await connection.commit();

      return await this.findById(id);
    } catch (error) {
      await connection.rollback();
      throw new Error('调整库存失败: ' + error.message);
    } finally {
      connection.release();
    }
  }

  /**
   * 获取低库存预警列表
   * @returns {Promise<array>} 低库存项列表
   */
  static async getLowStockItems() {
    try {
      const [rows] = await pool.query(
        `SELECT * FROM inventory
         WHERE quantity <= alert_quantity
         ORDER BY (quantity / alert_quantity) ASC`
      );

      return rows;
    } catch (error) {
      throw new Error('查询低库存项失败: ' + error.message);
    }
  }

  /**
   * 获取即将过期的库存列表
   * @param {number} days - 预警天数（默认7天）
   * @returns {Promise<array>} 即将过期的库存列表
   */
  static async getExpiringItems(days = 7) {
    try {
      const [rows] = await pool.query(
        `SELECT * FROM inventory
         WHERE expiry_date IS NOT NULL
         AND expiry_date <= DATE_ADD(CURDATE(), INTERVAL ? DAY)
         AND expiry_date >= CURDATE()
         ORDER BY expiry_date ASC`,
        [days]
      );

      return rows;
    } catch (error) {
      throw new Error('查询即将过期库存失败: ' + error.message);
    }
  }

  /**
   * 获取已过期的库存列表
   * @returns {Promise<array>} 已过期的库存列表
   */
  static async getExpiredItems() {
    try {
      const [rows] = await pool.query(
        `SELECT * FROM inventory
         WHERE expiry_date IS NOT NULL
         AND expiry_date < CURDATE()
         ORDER BY expiry_date ASC`
      );

      return rows;
    } catch (error) {
      throw new Error('查询已过期库存失败: ' + error.message);
    }
  }

  /**
   * 获取库存日志
   * @param {number} inventoryId - 库存ID（可选）
   * @param {object} options - 查询选项
   * @returns {Promise<object>} 日志列表和总数
   */
  static async getLogs(inventoryId = null, options = {}) {
    try {
      const {
        page = 1,
        pageSize = 20,
        change_type
      } = options;

      const offset = (page - 1) * pageSize;

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

      if (inventoryId) {
        conditions.push('il.inventory_id = ?');
        params.push(inventoryId);
      }

      if (change_type) {
        conditions.push('il.change_type = ?');
        params.push(change_type);
      }

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

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

      // 查询日志列表
      const [rows] = await pool.query(
        `SELECT
          il.*,
          i.name as inventory_name,
          i.unit,
          u.username as operator_name
         FROM inventory_logs il
         LEFT JOIN inventory i ON il.inventory_id = i.id
         LEFT JOIN users u ON il.operator_id = u.id
         ${whereClause}
         ORDER BY il.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} logData - 日志数据
   * @returns {Promise<number>} 插入的日志ID
   */
  static async addLog(logData) {
    try {
      const {
        inventory_id,
        change_type,
        quantity_change,
        quantity_after,
        operator_id,
        remark
      } = logData;

      const [result] = await pool.query(
        `INSERT INTO inventory_logs
         (inventory_id, change_type, quantity_change, quantity_after, operator_id, remark)
         VALUES (?, ?, ?, ?, ?, ?)`,
        [inventory_id, change_type, quantity_change, quantity_after, operator_id, remark || null]
      );

      return result.insertId;
    } catch (error) {
      throw new Error('添加库存日志失败: ' + error.message);
    }
  }

  /**
   * 检查名称是否已存在
   * @param {string} name - 原材料名称
   * @param {number} excludeId - 排除的ID（用于更新时）
   * @returns {Promise<boolean>} 是否存在
   */
  static async isNameExists(name, excludeId = null) {
    try {
      const query = excludeId
        ? 'SELECT id FROM inventory WHERE name = ? AND id != ?'
        : 'SELECT id FROM inventory WHERE name = ?';

      const params = excludeId ? [name, excludeId] : [name];

      const [rows] = await pool.query(query, params);
      return rows.length > 0;
    } catch (error) {
      throw new Error('检查名称失败: ' + error.message);
    }
  }

  /**
   * 获取库存分类列表
   * @returns {Promise<array>} 分类列表
   */
  static async getCategories() {
    try {
      const [rows] = await pool.query(
        'SELECT DISTINCT category FROM inventory ORDER BY category'
      );
      return rows.map(row => row.category);
    } catch (error) {
      throw new Error('查询分类列表失败: ' + error.message);
    }
  }

  /**
   * 获取库存统计信息
   * @returns {Promise<object>} 统计信息
   */
  static async getStatistics() {
    try {
      const [totalResult] = await pool.query(
        'SELECT COUNT(*) as total_items, SUM(quantity * purchase_price) as total_value FROM inventory'
      );

      const [lowStockResult] = await pool.query(
        'SELECT COUNT(*) as low_stock_count FROM inventory WHERE quantity <= alert_quantity'
      );

      const [expiringResult] = await pool.query(
        `SELECT COUNT(*) as expiring_count FROM inventory
         WHERE expiry_date IS NOT NULL
         AND expiry_date <= DATE_ADD(CURDATE(), INTERVAL 7 DAY)
         AND expiry_date >= CURDATE()`
      );

      const [expiredResult] = await pool.query(
        `SELECT COUNT(*) as expired_count FROM inventory
         WHERE expiry_date IS NOT NULL AND expiry_date < CURDATE()`
      );

      return {
        total_items: totalResult[0].total_items || 0,
        total_value: parseFloat(totalResult[0].total_value || 0),
        low_stock_count: lowStockResult[0].low_stock_count || 0,
        expiring_count: expiringResult[0].expiring_count || 0,
        expired_count: expiredResult[0].expired_count || 0
      };
    } catch (error) {
      throw new Error('查询库存统计失败: ' + error.message);
    }
  }
}

module.exports = Inventory;