SET NAMES utf8mb4;

-- 送养帖。平台只做信息与撮合，任何金额字段都不存在——不给"变相售卖"留位置。
CREATE TABLE IF NOT EXISTS pet_adoptions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  pet_id BIGINT UNSIGNED NOT NULL,
  owner_user_id BIGINT UNSIGNED NOT NULL,
  city_code VARCHAR(20) NOT NULL DEFAULT '',
  city_name VARCHAR(50) NOT NULL DEFAULT '',
  reason VARCHAR(500) NOT NULL DEFAULT '' COMMENT '送养原因',
  requirements VARCHAR(500) NOT NULL DEFAULT '' COMMENT '对领养人的要求',
  status TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '0 已下架 1 招领中 2 已送出',
  moderation_status TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0 待审核 1 通过 2 驳回',
  moderation_note VARCHAR(255) NOT NULL DEFAULT '',
  review_reason VARCHAR(255) NOT NULL DEFAULT '' COMMENT '转人工的原因，给审核员看',
  application_count INT UNSIGNED NOT NULL DEFAULT 0,
  adopter_user_id BIGINT UNSIGNED NULL,
  completed_at DATETIME(3) NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  deleted_at DATETIME(3) NULL,
  PRIMARY KEY (id),
  KEY idx_adoptions_listing (status, moderation_status, id),
  KEY idx_adoptions_owner (owner_user_id, id),
  KEY idx_adoptions_city (city_code, status, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 一个人对同一条送养只能申请一次：unique 让重复申请在数据库层面就不可能。
CREATE TABLE IF NOT EXISTS pet_adoption_applications (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  adoption_id BIGINT UNSIGNED NOT NULL,
  applicant_user_id BIGINT UNSIGNED NOT NULL,
  answers_json TEXT NOT NULL,
  message VARCHAR(500) NOT NULL DEFAULT '',
  status TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0 待处理 1 已选定 2 已婉拒 3 已撤回',
  decided_at DATETIME(3) NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_adoption_applicant (adoption_id, applicant_user_id),
  KEY idx_applications_applicant (applicant_user_id, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 回访：送出之后才是责任的开始，到期提醒领养人交一次近况。
CREATE TABLE IF NOT EXISTS pet_adoption_followups (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  adoption_id BIGINT UNSIGNED NOT NULL,
  adopter_user_id BIGINT UNSIGNED NOT NULL,
  due_at DATETIME(3) NOT NULL,
  submitted_at DATETIME(3) NULL,
  content VARCHAR(500) NOT NULL DEFAULT '',
  media_json TEXT NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  KEY idx_followups_due (submitted_at, due_at),
  KEY idx_followups_adopter (adopter_user_id, due_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 配种信息：只展示与联系，不参与交易，所以同样没有金额字段。
CREATE TABLE IF NOT EXISTS pet_mating_listings (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  pet_id BIGINT UNSIGNED NOT NULL,
  owner_user_id BIGINT UNSIGNED NOT NULL,
  city_code VARCHAR(20) NOT NULL DEFAULT '',
  city_name VARCHAR(50) NOT NULL DEFAULT '',
  expectation VARCHAR(500) NOT NULL DEFAULT '' COMMENT '对对方的期望',
  vaccine_media VARCHAR(500) NOT NULL DEFAULT '' COMMENT '免疫证明照片',
  status TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '0 已下架 1 展示中',
  moderation_status TINYINT UNSIGNED NOT NULL DEFAULT 0,
  moderation_note VARCHAR(255) NOT NULL DEFAULT '',
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  deleted_at DATETIME(3) NULL,
  PRIMARY KEY (id),
  KEY idx_mating_listing (status, moderation_status, id),
  KEY idx_mating_owner (owner_user_id, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO system_configs(config_key,config_value,value_type,description) VALUES
  ('pet.adoption_enabled','true','boolean','是否开放送养与领养'),
  ('pet.adoption_min_photos','3','integer','发布送养所需的最少照片数'),
  ('pet.adoption_review_words','元,钱,价,押金,费用,红包,转账,收费,出售,买,卖,微信,QQ,电话','string','送养文案命中这些词时转人工审核，逗号分隔'),
  ('pet.adoption_batch_days','90','integer','统计送养只数的时间窗口（天）'),
  ('pet.adoption_batch_limit','3','integer','窗口内送养超过这个只数即转人工审核'),
  ('pet.adoption_followup_days','30,90','string','送出后的回访节点，天数逗号分隔'),
  ('pet.mating_enabled','false','boolean','是否开放配种撮合；开启前请确认动物福利与舆情风险'),
  ('pet.mating_min_age_months','12','integer','可发布配种的最小月龄')
ON DUPLICATE KEY UPDATE description=VALUES(description);