-- 动物认领申请表
CREATE TABLE IF NOT EXISTS animal_claims (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '认领申请ID',
  claim_no VARCHAR(32) NOT NULL UNIQUE COMMENT '认领订单号',
  animal_id INT NOT NULL COMMENT '动物ID',
  user_id INT NOT NULL COMMENT '用户ID',
  claim_reason TEXT COMMENT '认领理由',
  claim_duration INT NOT NULL DEFAULT 12 COMMENT '认领时长（月）',
  total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '总金额',
  contact_info VARCHAR(500) NOT NULL COMMENT '联系方式',
  status ENUM('pending', 'approved', 'rejected', 'cancelled') NOT NULL DEFAULT 'pending' COMMENT '申请状态',
  start_date DATETIME NULL COMMENT '开始日期',
  end_date DATETIME NULL COMMENT '结束日期',
  reviewed_by INT NULL COMMENT '审核人ID',
  review_remark TEXT COMMENT '审核备注',
  reviewed_at DATETIME NULL COMMENT '审核时间',
  approved_at DATETIME NULL COMMENT '通过时间',
  cancelled_at DATETIME NULL COMMENT '取消时间',
  cancel_reason VARCHAR(500) NULL COMMENT '取消原因',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  deleted_at DATETIME NULL COMMENT '删除时间',
  
  -- 外键约束
  FOREIGN KEY (animal_id) REFERENCES animals(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL,
  
  -- 索引
  INDEX idx_animal_id (animal_id),
  INDEX idx_user_id (user_id),
  INDEX idx_status (status),
  INDEX idx_created_at (created_at),
  INDEX idx_claim_no (claim_no),
  INDEX idx_deleted_at (deleted_at),
  
  -- 唯一约束：同一用户对同一动物在同一时间只能有一个有效申请
  UNIQUE KEY uk_user_animal_active (user_id, animal_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='动物认领申请表';

-- 动物认领续期记录表
CREATE TABLE IF NOT EXISTS animal_claim_renewals (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '续期记录ID',
  claim_id INT NOT NULL COMMENT '认领申请ID',
  duration INT NOT NULL COMMENT '续期时长（月）',
  amount DECIMAL(10,2) NOT NULL COMMENT '续期金额',
  payment_method ENUM('wechat', 'alipay', 'bank_transfer') NOT NULL COMMENT '支付方式',
  status ENUM('pending', 'paid', 'cancelled') NOT NULL DEFAULT 'pending' COMMENT '续期状态',
  payment_no VARCHAR(64) NULL COMMENT '支付订单号',
  paid_at DATETIME NULL COMMENT '支付时间',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  
  -- 外键约束
  FOREIGN KEY (claim_id) REFERENCES animal_claims(id) ON DELETE CASCADE,
  
  -- 索引
  INDEX idx_claim_id (claim_id),
  INDEX idx_status (status),
  INDEX idx_created_at (created_at),
  INDEX idx_payment_no (payment_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='动物认领续期记录表';

-- 插入测试数据
INSERT INTO animal_claims (
  claim_no, animal_id, user_id, claim_reason, claim_duration, 
  total_amount, contact_info, status, created_at
) VALUES 
(
  'CLAIM20241201001', 1, 2, '我很喜欢这只小狗，希望能够认领它', 12, 
  1200.00, '手机：13800138001，微信：user001', 'pending', '2024-12-01 10:00:00'
),
(
  'CLAIM20241201002', 2, 3, '想要认领这只小猫，会好好照顾它', 6, 
  600.00, '手机：13800138002，QQ：123456789', 'approved', '2024-12-01 11:00:00'
),
(
  'CLAIM20241201003', 3, 4, '希望认领这只兔子，家里有足够的空间', 24, 
  2400.00, '手机：13800138003，邮箱：user003@example.com', 'rejected', '2024-12-01 12:00:00'
);

-- 更新已通过的认领申请的时间信息
UPDATE animal_claims 
SET 
  start_date = '2024-12-01 11:30:00',
  end_date = '2025-06-01 11:30:00',
  reviewed_by = 1,
  review_remark = '申请材料完整，同意认领',
  reviewed_at = '2024-12-01 11:30:00',
  approved_at = '2024-12-01 11:30:00'
WHERE claim_no = 'CLAIM20241201002';

-- 更新被拒绝的认领申请的审核信息
UPDATE animal_claims 
SET 
  reviewed_by = 1,
  review_remark = '认领时长过长，建议缩短认领期限后重新申请',
  reviewed_at = '2024-12-01 12:30:00'
WHERE claim_no = 'CLAIM20241201003';

-- 插入续期记录测试数据
INSERT INTO animal_claim_renewals (
  claim_id, duration, amount, payment_method, status, created_at
) VALUES 
(
  2, 6, 600.00, 'wechat', 'pending', '2024-12-01 15:00:00'
);

-- 创建视图：认领申请详情视图
CREATE OR REPLACE VIEW v_animal_claim_details AS
SELECT 
  ac.id,
  ac.claim_no,
  ac.animal_id,
  a.name as animal_name,
  a.type as animal_type,
  a.breed as animal_breed,
  a.age as animal_age,
  a.gender as animal_gender,
  a.image as animal_image,
  a.price as animal_price,
  ac.user_id,
  u.username,
  u.phone as user_phone,
  u.email as user_email,
  ac.claim_reason,
  ac.claim_duration,
  ac.total_amount,
  ac.contact_info,
  ac.status,
  ac.start_date,
  ac.end_date,
  ac.reviewed_by,
  reviewer.username as reviewer_name,
  ac.review_remark,
  ac.reviewed_at,
  ac.approved_at,
  ac.cancelled_at,
  ac.cancel_reason,
  ac.created_at,
  ac.updated_at,
  -- 计算剩余天数
  CASE 
    WHEN ac.status = 'approved' AND ac.end_date > NOW() 
    THEN DATEDIFF(ac.end_date, NOW())
    ELSE 0 
  END as remaining_days,
  -- 是否即将到期（30天内）
  CASE 
    WHEN ac.status = 'approved' AND ac.end_date > NOW() AND DATEDIFF(ac.end_date, NOW()) <= 30
    THEN 1
    ELSE 0 
  END as is_expiring_soon
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.deleted_at IS NULL;

-- 创建触发器：认领申请通过时更新动物状态
DELIMITER //
CREATE TRIGGER tr_animal_claim_approved 
AFTER UPDATE ON animal_claims
FOR EACH ROW
BEGIN
  -- 如果认领申请从其他状态变为已通过
  IF OLD.status != 'approved' AND NEW.status = 'approved' THEN
    UPDATE animals SET status = 'claimed', claim_count = claim_count + 1 WHERE id = NEW.animal_id;
  END IF;
  
  -- 如果认领申请从已通过变为其他状态
  IF OLD.status = 'approved' AND NEW.status != 'approved' THEN
    UPDATE animals SET status = 'available' WHERE id = NEW.animal_id;
  END IF;
END//
DELIMITER ;

-- 创建存储过程：批量处理过期的认领申请
DELIMITER //
CREATE PROCEDURE sp_handle_expired_claims()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE claim_id INT;
  DECLARE animal_id INT;
  
  -- 声明游标
  DECLARE expired_cursor CURSOR FOR 
    SELECT id, animal_id 
    FROM animal_claims 
    WHERE status = 'approved' 
    AND end_date < NOW() 
    AND deleted_at IS NULL;
  
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  
  -- 开始事务
  START TRANSACTION;
  
  -- 打开游标
  OPEN expired_cursor;
  
  read_loop: LOOP
    FETCH expired_cursor INTO claim_id, animal_id;
    IF done THEN
      LEAVE read_loop;
    END IF;
    
    -- 更新认领申请状态为已过期
    UPDATE animal_claims 
    SET status = 'expired', updated_at = NOW() 
    WHERE id = claim_id;
    
    -- 更新动物状态为可认领
    UPDATE animals 
    SET status = 'available', updated_at = NOW() 
    WHERE id = animal_id;
    
  END LOOP;
  
  -- 关闭游标
  CLOSE expired_cursor;
  
  -- 提交事务
  COMMIT;
  
  -- 返回处理的记录数
  SELECT ROW_COUNT() as processed_count;
END//
DELIMITER ;

-- 创建事件：每天自动处理过期的认领申请
CREATE EVENT IF NOT EXISTS ev_handle_expired_claims
ON SCHEDULE EVERY 1 DAY
STARTS '2024-12-01 02:00:00'
DO
  CALL sp_handle_expired_claims();