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

/**
 * 动物模型类
 * 处理动物相关的数据库操作
 */
class Animal {
  /**
   * 根据ID查找动物
   * @param {number} id - 动物ID
   * @returns {Object|null} 动物信息
   */
  static async findById(id) {
    try {
      const [rows] = await query(
        'SELECT * FROM animals WHERE id = ?',
        [id]
      );
      return rows[0] || null;
    } catch (error) {
      console.error('查找动物失败:', error);
      throw error;
    }
  }

  /**
   * 获取动物列表（包含商家信息）
   * @param {Object} options - 查询选项
   * @returns {Array} 动物列表
   */
  static async getAnimalListWithMerchant(options = {}) {
    try {
      const {
        whereClause = '',
        params = [],
        sortBy = 'created_at',
        sortOrder = 'desc',
        limit = 10,
        offset = 0
      } = options;

      const query = `
        SELECT 
          a.*,
          type,
          m.name as merchant_name,
          m.contact_phone as merchant_phone
        FROM animals a
        LEFT JOIN merchants m ON a.merchant_id = m.id
        WHERE ${whereClause}
        ORDER BY a.created_at DESC
        LIMIT ? OFFSET ?
      `;

      const [rows] = await query(query, [...params, limit, offset]);
      return rows;
    } catch (error) {
      console.error('获取动物列表失败:', error);
      throw error;
    }
  }

  /**
   * 获取动物数量
   * @param {Object} options - 查询选项
   * @returns {number} 动物数量
   */
  static async getAnimalCount(options = {}) {
    try {
      const { whereClause = '', params = [] } = options;

      const query = `
        SELECT COUNT(*) as count
        FROM animals a
        WHERE 1=1 ${whereClause}
      `;

      const [rows] = await query(query, params);
      return rows[0].count;
    } catch (error) {
      console.error('获取动物数量失败:', error);
      throw error;
    }
  }

  /**
   * 获取动物详情（包含商家信息）
   * @param {number} id - 动物ID
   * @returns {Object|null} 动物详情
   */
  static async getAnimalDetailWithMerchant(id) {
    try {
      const query = `
        SELECT 
          a.*,
          a.type,
          m.name as merchant_name,
          m.contact_phone as merchant_phone,
          m.address as merchant_address
        FROM animals a
        LEFT JOIN merchants m ON a.merchant_id = m.id
        WHERE a.id = ?
      `;

      const [rows] = await query(query, [id]);
      return rows[0] || null;
    } catch (error) {
      console.error('获取动物详情失败:', error);
      throw error;
    }
  }

  /**
   * 更新动物状态
   * @param {number} id - 动物ID
   * @param {string} status - 新状态
   * @param {number} adminId - 管理员ID
   * @param {string} reason - 更新原因
   * @returns {Object} 更新结果
   */
  static async updateAnimalStatus(id, status, adminId, reason = null) {
    try {
      const query = `
        UPDATE animals 
        SET status = ?, updated_at = NOW()
        WHERE id = ?
      `;

      const [result] = await query(query, [status, id]);
      
      // 记录状态变更日志
      if (reason) {
        await query(
          `INSERT INTO animal_status_logs (animal_id, old_status, new_status, admin_id, reason, created_at)
           SELECT ?, status, ?, ?, ?, NOW() FROM animals WHERE id = ?`,
          [id, status, adminId, reason, id]
        );
      }

      return result;
    } catch (error) {
      console.error('更新动物状态失败:', error);
      throw error;
    }
  }

  /**
   * 批量更新动物状态
   * @param {Array} ids - 动物ID数组
   * @param {string} status - 新状态
   * @param {number} adminId - 管理员ID
   * @param {string} reason - 更新原因
   * @returns {Object} 更新结果
   */
  static async batchUpdateAnimalStatus(ids, status, adminId, reason = null) {
    try {
      const placeholders = ids.map(() => '?').join(',');
      const query = `
        UPDATE animals 
        SET status = ?, updated_at = NOW()
        WHERE id IN (${placeholders})
      `;

      const [result] = await query(query, [status, ...ids]);
      return result;
    } catch (error) {
      console.error('批量更新动物状态失败:', error);
      throw error;
    }
  }

  /**
   * 获取动物总体统计
   * @returns {Object} 统计信息
   */
  static async getAnimalTotalStats() {
    try {
      const query = `
        SELECT 
          COUNT(*) as total_animals,
          COUNT(CASE WHEN status = 'available' THEN 1 END) as available_count,
          COUNT(CASE WHEN status = 'claimed' THEN 1 END) as claimed_count,
          COUNT(CASE WHEN status = 'unavailable' THEN 1 END) as unavailable_count,
          AVG(price) as avg_price,
          MIN(price) as min_price,
          MAX(price) as max_price
        FROM animals
      `;

      const [rows] = await query(query);
      return rows[0];
    } catch (error) {
      console.error('获取动物总体统计失败:', error);
      throw error;
    }
  }

  /**
   * 获取动物统计信息（按类型分组）
   * @returns {Array} 统计信息
   */
  static async getAnimalStatsByType() {
    try {
      const query = `
        SELECT 
          type,
          COUNT(*) as count,
          AVG(price) as avg_price
        FROM animals
        WHERE status = 'available'
        GROUP BY type
        ORDER BY count DESC
      `;

      const [rows] = await query(query);
      return rows;
    } catch (error) {
      console.error('获取动物统计信息失败:', error);
      throw error;
    }
  }

  /**
   * 获取按状态分类的统计
   * @returns {Array} 统计信息
   */
  static async getAnimalStatsByStatus() {
    try {
      const query = `
        SELECT 
          status,
          COUNT(*) as count,
          ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM animals), 2) as percentage
        FROM animals
        GROUP BY status
        ORDER BY count DESC
      `;

      const [rows] = await query(query);
      return rows;
    } catch (error) {
      console.error('获取按状态分类的统计失败:', error);
      throw error;
    }
  }

  /**
   * 获取按商家分类的统计
   * @returns {Array} 统计信息
   */
  static async getAnimalStatsByMerchant() {
    try {
      const query = `
        SELECT 
          m.name as merchant_name,
          COUNT(a.id) as animal_count,
          COUNT(CASE WHEN a.status = 'available' THEN 1 END) as available_count,
          COUNT(CASE WHEN a.status = 'claimed' THEN 1 END) as claimed_count,
          AVG(a.price) as avg_price
        FROM merchants m
        LEFT JOIN animals a ON m.id = a.merchant_id
        GROUP BY m.id, m.name
        HAVING animal_count > 0
        ORDER BY animal_count DESC
      `;

      const [rows] = await query(query);
      return rows;
    } catch (error) {
      console.error('获取按商家分类的统计失败:', error);
      throw error;
    }
  }

  /**
   * 获取月度趋势数据
   * @returns {Array} 趋势数据
   */
  static async getAnimalMonthlyTrend() {
    try {
      const query = `
        SELECT 
          DATE_FORMAT(created_at, '%Y-%m') as month,
          COUNT(*) as count,
          COUNT(CASE WHEN status = 'available' THEN 1 END) as available_count,
          COUNT(CASE WHEN status = 'claimed' THEN 1 END) as claimed_count
        FROM animals
        WHERE created_at >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
        GROUP BY DATE_FORMAT(created_at, '%Y-%m')
        ORDER BY month ASC
      `;

      const [rows] = await query(query);
      return rows;
    } catch (error) {
      console.error('获取月度趋势数据失败:', error);
      throw error;
    }
  }

  /**
   * 获取导出数据
   * @param {Object} options - 查询选项
   * @returns {Array} 导出数据
   */
  static async getAnimalExportData(options = {}) {
    try {
      const { whereClause = '', params = [] } = options;

      const query = `
        SELECT 
          a.id,
          a.name,
          a.species,
          a.breed,
          a.age,
          a.gender,
          a.price,
          a.status,
          m.name as merchant_name,
          a.created_at
        FROM animals a
        LEFT JOIN merchants m ON a.merchant_id = m.id
        WHERE 1=1 ${whereClause}
        ORDER BY a.created_at DESC
      `;

      const [rows] = await query(query, params);
      return rows;
    } catch (error) {
      console.error('获取导出数据失败:', error);
      throw error;
    }
  }

  /**
   * 创建新动物
   * @param {Object} animalData - 动物数据
   * @returns {Object} 创建结果
   */
  static async create(animalData) {
    try {
      const {
        name,
        type,
        breed,
        age,
        gender,
        weight,
        price,
        description,
        health_status,
        vaccination_records,
        images,
        merchant_id,
        farm_location,
        contact_info,
        status = 'available'
      } = animalData;

      const query = `
        INSERT INTO animals (
          name, type, breed, age, gender, weight, price, 
          description, health_status, vaccination_records, images,
          merchant_id, farm_location, contact_info, status, created_at, updated_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())
      `;

      const [result] = await query(query, [
        name, type, breed, age, gender, weight, price,
        description, health_status, JSON.stringify(vaccination_records || []), 
        JSON.stringify(images || []), merchant_id, farm_location, 
        JSON.stringify(contact_info || {}), status
      ]);

      return { id: result.insertId, ...animalData };
    } catch (error) {
      console.error('创建动物失败:', error);
      throw error;
    }
  }

  /**
   * 更新动物信息
   * @param {number} id - 动物ID
   * @param {Object} animalData - 更新数据
   * @returns {Object} 更新结果
   */
  static async update(id, animalData) {
    try {
      const fields = [];
      const values = [];

      Object.keys(animalData).forEach(key => {
        if (animalData[key] !== undefined) {
          fields.push(`${key} = ?`);
          values.push(animalData[key]);
        }
      });

      if (fields.length === 0) {
        throw new Error('没有要更新的字段');
      }

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

      const query = `UPDATE animals SET ${fields.join(', ')} WHERE id = ?`;
      const [result] = await query(query, values);

      return result;
    } catch (error) {
      console.error('更新动物信息失败:', error);
      throw error;
    }
  }

  /**
   * 删除动物
   * @param {number} id - 动物ID
   * @returns {Object} 删除结果
   */
  static async delete(id) {
    try {
      const [result] = await query('DELETE FROM animals WHERE id = ?', [id]);
      return result;
    } catch (error) {
      console.error('删除动物失败:', error);
      throw error;
    }
  }
}

module.exports = Animal;