const db = require('../config/database');

class AnimalClaim {
  /**
   * 创建认领申请
   * @param {Object} claimData - 认领申请数据
   * @returns {Object} 创建的认领申请
   */
  static async create(claimData) {
    try {
      const {
        claim_no,
        animal_id,
        user_id,
        claim_reason,
        claim_duration,
        total_amount,
        contact_info,
        status = 'pending'
      } = claimData;

      const query = `
        INSERT INTO animal_claims (
          claim_no, animal_id, user_id, claim_reason, claim_duration,
          total_amount, contact_info, status, created_at, updated_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())
      `;

      const [result] = await db.execute(query, [
        claim_no,
        animal_id,
        user_id,
        claim_reason,
        claim_duration,
        total_amount,
        contact_info,
        status
      ]);

      return await this.findById(result.insertId);
    } catch (error) {
      console.error('创建认领申请数据库错误:', error);
      throw error;
    }
  }

  /**
   * 根据ID查找认领申请
   * @param {number} id - 认领申请ID
   * @returns {Object|null} 认领申请信息
   */
  static async findById(id) {
    try {
      const query = `
        SELECT 
          ac.*,
          a.name as animal_name,
          a.type as animal_type,
          a.image as animal_image,
          a.price as animal_price,
          u.username,
          u.phone as user_phone,
          reviewer.username as reviewer_name
        FROM animal_claims ac
        LEFT JOIN animals a ON ac.animal_id = a.id
        LEFT JOIN users u ON ac.user_id = u.id
        LEFT JOIN users reviewer ON ac.reviewed_by = reviewer.id
        WHERE ac.id = ? AND ac.deleted_at IS NULL
      `;

      const [rows] = await db.execute(query, [id]);
      return rows[0] || null;
    } catch (error) {
      console.error('查找认领申请数据库错误:', error);
      throw error;
    }
  }

  /**
   * 根据认领订单号查找
   * @param {string} claimNo - 认领订单号
   * @returns {Object|null} 认领申请信息
   */
  static async findByClaimNo(claimNo) {
    try {
      const query = `
        SELECT 
          ac.*,
          a.name as animal_name,
          a.type as animal_type,
          a.image as animal_image,
          u.username,
          u.phone as user_phone
        FROM animal_claims ac
        LEFT JOIN animals a ON ac.animal_id = a.id
        LEFT JOIN users u ON ac.user_id = u.id
        WHERE ac.claim_no = ? AND ac.deleted_at IS NULL
      `;

      const [rows] = await db.execute(query, [claimNo]);
      return rows[0] || null;
    } catch (error) {
      console.error('根据订单号查找认领申请数据库错误:', error);
      throw error;
    }
  }

  /**
   * 查找用户对特定动物的活跃认领申请
   * @param {number} userId - 用户ID
   * @param {number} animalId - 动物ID
   * @returns {Object|null} 认领申请信息
   */
  static async findActiveClaimByUserAndAnimal(userId, animalId) {
    try {
      const query = `
        SELECT * FROM animal_claims
        WHERE user_id = ? AND animal_id = ? 
        AND status IN ('pending', 'approved')
        AND deleted_at IS NULL
        ORDER BY created_at DESC
        LIMIT 1
      `;

      const [rows] = await db.execute(query, [userId, animalId]);
      return rows[0] || null;
    } catch (error) {
      console.error('查找活跃认领申请数据库错误:', error);
      throw error;
    }
  }

  /**
   * 更新认领申请状态
   * @param {number} id - 认领申请ID
   * @param {string} status - 新状态
   * @param {Object} updateData - 更新数据
   * @returns {Object} 更新后的认领申请
   */
  static async updateStatus(id, status, updateData = {}) {
    try {
      const fields = ['status = ?', 'updated_at = NOW()'];
      const values = [status];

      // 动态添加更新字段
      Object.keys(updateData).forEach(key => {
        if (updateData[key] !== undefined) {
          fields.push(`${key} = ?`);
          values.push(updateData[key]);
        }
      });

      values.push(id);

      const query = `
        UPDATE animal_claims 
        SET ${fields.join(', ')}
        WHERE id = ?
      `;

      await db.execute(query, values);
      return await this.findById(id);
    } catch (error) {
      console.error('更新认领申请状态数据库错误:', error);
      throw error;
    }
  }

  /**
   * 获取用户的认领申请列表
   * @param {number} userId - 用户ID
   * @param {Object} options - 查询选项
   * @returns {Object} 分页结果
   */
  static async getUserClaims(userId, options = {}) {
    try {
      const {
        page = 1,
        limit = 10,
        status,
        animal_type,
        start_date,
        end_date
      } = options;

      const offset = (page - 1) * limit;
      let whereConditions = ['ac.user_id = ?', 'ac.deleted_at IS NULL'];
      let queryParams = [userId];

      // 添加筛选条件
      if (status) {
        whereConditions.push('ac.status = ?');
        queryParams.push(status);
      }

      if (animal_type) {
        whereConditions.push('a.type = ?');
        queryParams.push(animal_type);
      }

      if (start_date) {
        whereConditions.push('ac.created_at >= ?');
        queryParams.push(start_date);
      }

      if (end_date) {
        whereConditions.push('ac.created_at <= ?');
        queryParams.push(end_date);
      }

      const whereClause = whereConditions.join(' AND ');

      // 查询数据
      const dataQuery = `
        SELECT 
          ac.*,
          a.name as animal_name,
          a.type as animal_type,
          a.image as animal_image,
          a.price as animal_price
        FROM animal_claims ac
        LEFT JOIN animals a ON ac.animal_id = a.id
        WHERE ${whereClause}
        ORDER BY ac.created_at DESC
        LIMIT ? OFFSET ?
      `;

      const [dataRows] = await db.execute(dataQuery, [...queryParams, limit, offset]);

      // 查询总数
      const countQuery = `
        SELECT COUNT(*) as total
        FROM animal_claims ac
        LEFT JOIN animals a ON ac.animal_id = a.id
        WHERE ${whereClause}
      `;

      const [countRows] = await db.execute(countQuery, queryParams);
      const total = countRows[0].total;

      return {
        data: dataRows,
        pagination: {
          page: parseInt(page),
          limit: parseInt(limit),
          total,
          pages: Math.ceil(total / limit)
        }
      };
    } catch (error) {
      console.error('获取用户认领申请列表数据库错误:', error);
      throw error;
    }
  }

  /**
   * 获取动物的认领申请列表
   * @param {number} animalId - 动物ID
   * @param {Object} options - 查询选项
   * @returns {Object} 分页结果
   */
  static async getAnimalClaims(animalId, options = {}) {
    try {
      const {
        page = 1,
        limit = 10,
        status
      } = options;

      const offset = (page - 1) * limit;
      let whereConditions = ['ac.animal_id = ?', 'ac.deleted_at IS NULL'];
      let queryParams = [animalId];

      if (status) {
        whereConditions.push('ac.status = ?');
        queryParams.push(status);
      }

      const whereClause = whereConditions.join(' AND ');

      // 查询数据
      const dataQuery = `
        SELECT 
          ac.*,
          u.username,
          u.phone as user_phone,
          u.email as user_email
        FROM animal_claims ac
        LEFT JOIN users u ON ac.user_id = u.id
        WHERE ${whereClause}
        ORDER BY ac.created_at DESC
        LIMIT ? OFFSET ?
      `;

      const [dataRows] = await db.execute(dataQuery, [...queryParams, limit, offset]);

      // 查询总数
      const countQuery = `
        SELECT COUNT(*) as total
        FROM animal_claims ac
        WHERE ${whereClause}
      `;

      const [countRows] = await db.execute(countQuery, queryParams);
      const total = countRows[0].total;

      return {
        data: dataRows,
        pagination: {
          page: parseInt(page),
          limit: parseInt(limit),
          total,
          pages: Math.ceil(total / limit)
        }
      };
    } catch (error) {
      console.error('获取动物认领申请列表数据库错误:', error);
      throw error;
    }
  }

  /**
   * 获取所有认领申请列表（管理员）
   * @param {Object} options - 查询选项
   * @returns {Object} 分页结果
   */
  static async getAllClaims(options = {}) {
    try {
      const {
        page = 1,
        limit = 10,
        status,
        animal_type,
        user_id,
        start_date,
        end_date,
        keyword
      } = options;

      const offset = (page - 1) * limit;
      let whereConditions = ['ac.deleted_at IS NULL'];
      let queryParams = [];

      // 添加筛选条件
      if (status) {
        whereConditions.push('ac.status = ?');
        queryParams.push(status);
      }

      if (animal_type) {
        whereConditions.push('a.type = ?');
        queryParams.push(animal_type);
      }

      if (user_id) {
        whereConditions.push('ac.user_id = ?');
        queryParams.push(user_id);
      }

      if (start_date) {
        whereConditions.push('ac.created_at >= ?');
        queryParams.push(start_date);
      }

      if (end_date) {
        whereConditions.push('ac.created_at <= ?');
        queryParams.push(end_date);
      }

      if (keyword) {
        whereConditions.push('(ac.claim_no LIKE ? OR a.name LIKE ? OR u.username LIKE ?)');
        const keywordPattern = `%${keyword}%`;
        queryParams.push(keywordPattern, keywordPattern, keywordPattern);
      }

      const whereClause = whereConditions.join(' AND ');

      // 查询数据
      const dataQuery = `
        SELECT 
          ac.*,
          a.name as animal_name,
          a.type as animal_type,
          a.image as animal_image,
          a.price as animal_price,
          u.username,
          u.phone as user_phone,
          u.email as user_email,
          reviewer.username as reviewer_name
        FROM animal_claims ac
        LEFT JOIN animals a ON ac.animal_id = a.id
        LEFT JOIN users u ON ac.user_id = u.id
        LEFT JOIN users reviewer ON ac.reviewed_by = reviewer.id
        WHERE ${whereClause}
        ORDER BY ac.created_at DESC
        LIMIT ? OFFSET ?
      `;

      const [dataRows] = await db.execute(dataQuery, [...queryParams, limit, offset]);

      // 查询总数
      const countQuery = `
        SELECT COUNT(*) as total
        FROM animal_claims ac
        LEFT JOIN animals a ON ac.animal_id = a.id
        LEFT JOIN users u ON ac.user_id = u.id
        WHERE ${whereClause}
      `;

      const [countRows] = await db.execute(countQuery, queryParams);
      const total = countRows[0].total;

      return {
        data: dataRows,
        pagination: {
          page: parseInt(page),
          limit: parseInt(limit),
          total,
          pages: Math.ceil(total / limit)
        }
      };
    } catch (error) {
      console.error('获取所有认领申请列表数据库错误:', error);
      throw error;
    }
  }

  /**
   * 创建续期记录
   * @param {Object} renewalData - 续期数据
   * @returns {Object} 续期记录
   */
  static async createRenewal(renewalData) {
    try {
      const {
        claim_id,
        duration,
        amount,
        payment_method,
        status = 'pending'
      } = renewalData;

      const query = `
        INSERT INTO animal_claim_renewals (
          claim_id, duration, amount, payment_method, status, created_at, updated_at
        ) VALUES (?, ?, ?, ?, ?, NOW(), NOW())
      `;

      const [result] = await db.execute(query, [
        claim_id,
        duration,
        amount,
        payment_method,
        status
      ]);

      return {
        id: result.insertId,
        claim_id,
        duration,
        amount,
        payment_method,
        status
      };
    } catch (error) {
      console.error('创建续期记录数据库错误:', error);
      throw error;
    }
  }

  /**
   * 获取认领统计信息
   * @param {Object} filters - 筛选条件
   * @returns {Object} 统计信息
   */
  static async getClaimStatistics(filters = {}) {
    try {
      const { start_date, end_date, animal_type } = filters;
      let whereConditions = ['ac.deleted_at IS NULL'];
      let queryParams = [];

      if (start_date) {
        whereConditions.push('ac.created_at >= ?');
        queryParams.push(start_date);
      }

      if (end_date) {
        whereConditions.push('ac.created_at <= ?');
        queryParams.push(end_date);
      }

      if (animal_type) {
        whereConditions.push('a.type = ?');
        queryParams.push(animal_type);
      }

      const whereClause = whereConditions.join(' AND ');

      // 基础统计
      const basicStatsQuery = `
        SELECT 
          COUNT(*) as total_claims,
          COUNT(CASE WHEN ac.status = 'pending' THEN 1 END) as pending_claims,
          COUNT(CASE WHEN ac.status = 'approved' THEN 1 END) as approved_claims,
          COUNT(CASE WHEN ac.status = 'rejected' THEN 1 END) as rejected_claims,
          COUNT(CASE WHEN ac.status = 'cancelled' THEN 1 END) as cancelled_claims,
          SUM(CASE WHEN ac.status = 'approved' THEN ac.total_amount ELSE 0 END) as total_amount,
          AVG(CASE WHEN ac.status = 'approved' THEN ac.claim_duration ELSE NULL END) as avg_duration
        FROM animal_claims ac
        LEFT JOIN animals a ON ac.animal_id = a.id
        WHERE ${whereClause}
      `;

      const [basicStats] = await db.execute(basicStatsQuery, queryParams);

      // 按动物类型统计
      const typeStatsQuery = `
        SELECT 
          a.type,
          COUNT(*) as claim_count,
          COUNT(CASE WHEN ac.status = 'approved' THEN 1 END) as approved_count,
          SUM(CASE WHEN ac.status = 'approved' THEN ac.total_amount ELSE 0 END) as total_amount
        FROM animal_claims ac
        LEFT JOIN animals a ON ac.animal_id = a.id
        WHERE ${whereClause}
        GROUP BY a.type
        ORDER BY claim_count DESC
      `;

      const [typeStats] = await db.execute(typeStatsQuery, queryParams);

      // 按月份统计
      const monthlyStatsQuery = `
        SELECT 
          DATE_FORMAT(ac.created_at, '%Y-%m') as month,
          COUNT(*) as claim_count,
          COUNT(CASE WHEN ac.status = 'approved' THEN 1 END) as approved_count,
          SUM(CASE WHEN ac.status = 'approved' THEN ac.total_amount ELSE 0 END) as total_amount
        FROM animal_claims ac
        LEFT JOIN animals a ON ac.animal_id = a.id
        WHERE ${whereClause}
        GROUP BY DATE_FORMAT(ac.created_at, '%Y-%m')
        ORDER BY month DESC
        LIMIT 12
      `;

      const [monthlyStats] = await db.execute(monthlyStatsQuery, queryParams);

      return {
        basic: basicStats[0],
        by_type: typeStats,
        by_month: monthlyStats
      };
    } catch (error) {
      console.error('获取认领统计信息数据库错误:', error);
      throw error;
    }
  }

  /**
   * 软删除认领申请
   * @param {number} id - 认领申请ID
   * @returns {boolean} 删除结果
   */
  static async softDelete(id) {
    try {
      const query = `
        UPDATE animal_claims 
        SET deleted_at = NOW(), updated_at = NOW()
        WHERE id = ?
      `;

      const [result] = await db.execute(query, [id]);
      return result.affectedRows > 0;
    } catch (error) {
      console.error('软删除认领申请数据库错误:', error);
      throw error;
    }
  }
}

module.exports = AnimalClaim;