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

class AnimalService {
  // 获取动物列表
  static async getAnimals(searchParams) {
    try {
      const { merchantId, type, status, page = 1, pageSize = 10 } = searchParams;
      const offset = (page - 1) * pageSize;

      let sql = `
        SELECT a.*, m.business_name as merchant_name, m.merchant_type, u.nickname as username, u.nickname as real_name
        FROM animals a
        INNER JOIN merchants m ON a.merchant_id = m.id
        INNER JOIN users u ON m.user_id = u.id
        WHERE 1=1
      `;
      const params = [];

      if (merchantId) {
        sql += ' AND a.merchant_id = ?';
        params.push(merchantId);
      }

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

      if (status) {
        sql += ' AND a.status = ?';
        params.push(status);
      }

      // 获取总数
      const countSql = `SELECT COUNT(*) as total FROM (${sql}) as count_query`;
      const countResult = await query(countSql, params);
      const total = countResult[0].total;

      // 添加分页和排序
      sql += ' ORDER BY a.created_at DESC LIMIT ? OFFSET ?';
      params.push(pageSize, offset);

      const animals = await query(sql, params);

      return {
        animals,
        pagination: {
          page,
          pageSize,
          total,
          totalPages: Math.ceil(total / pageSize)
        }
      };
    } catch (error) {
      throw error;
    }
  }

  // 获取单个动物详情
  static async getAnimalById(animalId) {
    try {
      const sql = `
        SELECT a.*, m.business_name as merchant_name, m.merchant_type, m.contact_person as contact_info, u.nickname as username, u.nickname as real_name, u.avatar
        FROM animals a
        INNER JOIN merchants m ON a.merchant_id = m.id
        INNER JOIN users u ON m.user_id = u.id
        WHERE a.id = ?
      `;
      const params = [animalId];
      const result = await query(sql, params);
      
      if (result.length === 0) {
        throw new AppError('动物不存在', 404);
      }
      
      return result[0];
    } catch (error) {
      throw error;
    }
  }

  // 创建动物
  static async createAnimal(animalData) {
    try {
      const sql = `
        INSERT INTO animals (
          merchant_id, name, type, breed, age, gender, weight, price, 
          description, health_status, vaccination_records, images,
          farm_location, contact_info, status, created_at, updated_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())
      `;
      const params = [
        animalData.merchant_id,
        animalData.name,
        animalData.type,
        animalData.breed,
        animalData.age,
        animalData.gender,
        animalData.weight,
        animalData.price,
        animalData.description,
        animalData.health_status,
        JSON.stringify(animalData.vaccination_records || []),
        JSON.stringify(animalData.images || []),
        animalData.farm_location,
        JSON.stringify(animalData.contact_info || {}),
        animalData.status || 'available'
      ];

      const result = await query(sql, params);
      const animalId = result.insertId;
      
      return await this.getAnimalById(animalId);
    } catch (error) {
      throw error;
    }
  }

  // 更新动物信息
  static async updateAnimal(animalId, updateData) {
    try {
      // 构建动态更新语句
      const fields = [];
      const params = [];
      
      Object.keys(updateData).forEach(key => {
        // 只允许更新特定字段
        const allowedFields = ['name', 'species', 'breed', 'birth_date', 'personality', 'farm_location', 'price', 'status'];
        if (allowedFields.includes(key)) {
          fields.push(`${key} = ?`);
          params.push(updateData[key]);
        }
      });
      
      if (fields.length === 0) {
        throw new AppError('没有提供可更新的字段', 400);
      }
      
      params.push(animalId); // 为WHERE条件添加ID
      
      const sql = `UPDATE animals SET ${fields.join(', ')} WHERE id = ?`;
      await query(sql, params);
      
      return await this.getAnimalById(animalId);
    } catch (error) {
      throw error;
    }
  }

  // 删除动物
  static async deleteAnimal(animalId) {
    try {
      const sql = 'DELETE FROM animals WHERE id = ?';
      const params = [animalId];
      await query(sql, params);
    } catch (error) {
      throw error;
    }
  }

  // 获取动物统计信息
  static async getAnimalStatistics() {
    try {
      // 获取动物总数
      const totalSql = 'SELECT COUNT(*) as total FROM animals';
      const [totalResult] = await query(totalSql);
      const totalAnimals = totalResult.total;

      // 按物种统计
      const speciesSql = `
        SELECT species, COUNT(*) as count 
        FROM animals 
        GROUP BY species 
        ORDER BY count DESC
      `;
      const speciesStats = await query(speciesSql);

      // 按状态统计
      const statusSql = `
        SELECT status, COUNT(*) as count 
        FROM animals 
        GROUP BY status
      `;
      const statusStats = await query(statusSql);

      // 按商家统计（前5名）
      const merchantSql = `
        SELECT m.business_name as merchant_name, COUNT(a.id) as animal_count
        FROM merchants m
        LEFT JOIN animals a ON m.id = a.merchant_id
        GROUP BY m.id, m.business_name
        ORDER BY animal_count DESC
        LIMIT 5
      `;
      const merchantStats = await query(merchantSql);

      return {
        total: totalAnimals,
        bySpecies: speciesStats,
        byStatus: statusStats,
        topMerchants: merchantStats
      };
    } catch (error) {
      throw error;
    }
  }

  // 搜索动物
  static async searchAnimals(searchParams) {
    try {
      const { keyword, species, minPrice, maxPrice, page = 1, pageSize = 10 } = searchParams;
      const offset = (page - 1) * pageSize;

      let sql = `
        SELECT a.*, m.business_name as merchant_name, m.merchant_type, u.nickname as username, u.nickname as real_name
        FROM animals a
        INNER JOIN merchants m ON a.merchant_id = m.id
        INNER JOIN users u ON m.user_id = u.id
        WHERE a.status = 'available'
      `;
      const params = [];

      if (keyword) {
        sql += ' AND (a.name LIKE ? OR a.personality LIKE ? OR a.species LIKE ?)';
        params.push(`%${keyword}%`, `%${keyword}%`, `%${keyword}%`);
      }

      if (species) {
        sql += ' AND a.species = ?';
        params.push(species);
      }

      if (minPrice !== null) {
        sql += ' AND a.price >= ?';
        params.push(minPrice);
      }

      if (maxPrice !== null) {
        sql += ' AND a.price <= ?';
        params.push(maxPrice);
      }

      // 获取总数
      const countSql = `SELECT COUNT(*) as total FROM (${sql}) as count_query`;
      const countResult = await query(countSql, params);
      const total = countResult[0].total;

      // 添加分页和排序
      sql += ' ORDER BY a.created_at DESC LIMIT ? OFFSET ?';
      params.push(pageSize, offset);

      const animals = await query(sql, params);

      return {
        animals,
        pagination: {
          page,
          pageSize,
          total,
          totalPages: Math.ceil(total / pageSize)
        }
      };
    } catch (error) {
      throw error;
    }
  }
}

module.exports = AnimalService;