-- 牦牛资产档案（主表 + 子表）
SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `biz_yak_asset` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `external_id` bigint(20) DEFAULT NULL COMMENT '第三方牛只唯一ID OpenYakEntryDto.cattleId',
  `yak_no` varchar(64) NOT NULL COMMENT '牦牛编号（优先耳标 earTagNumber）',
  `pasture_id` bigint(20) DEFAULT NULL COMMENT '牧场ID',
  `pasture_name` varchar(128) DEFAULT NULL COMMENT '所属牧场名称',
  `batch_no` varchar(64) DEFAULT NULL COMMENT '批次编号',
  `gender` varchar(16) DEFAULT NULL COMMENT '性别',
  `birth_date` date DEFAULT NULL COMMENT '出生日期',
  `age_months` int(11) DEFAULT NULL COMMENT '月龄',
  `entry_date` date DEFAULT NULL COMMENT '入栏日期',
  `entry_weight_kg` decimal(10,2) DEFAULT NULL COMMENT '入栏体重kg',
  `source` varchar(64) DEFAULT NULL COMMENT '来源',
  `breeding_method` varchar(64) DEFAULT NULL COMMENT '养殖方式',
  `asset_status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '资产状态1-4',
  `status_change_date` date DEFAULT NULL COMMENT '变更日期',
  `status_change_reason` varchar(256) DEFAULT NULL COMMENT '状态变更原因',
  `pen_location` varchar(128) DEFAULT NULL COMMENT '圈舍位置',
  `expected_out_date` date DEFAULT NULL COMMENT '预计出栏日期',
  `supplement_plan` varchar(512) DEFAULT NULL COMMENT '补饲方案',
  `realtime_temp` decimal(6,2) DEFAULT NULL COMMENT '实时体温',
  `realtime_steps` int(11) DEFAULT NULL COMMENT '实时运动量',
  `env_temp` decimal(6,2) DEFAULT NULL COMMENT '环境温度',
  `location` varchar(256) DEFAULT NULL COMMENT '位置',
  `physio_collect_time` datetime DEFAULT NULL COMMENT '生理采集时间',
  `father_yak_no` varchar(64) DEFAULT NULL COMMENT '父亲编号',
  `mother_yak_no` varchar(64) DEFAULT NULL COMMENT '母亲编号',
  `last_sync_time` datetime DEFAULT NULL COMMENT '最近同步时间',
  `del_flag` char(1) NOT NULL DEFAULT '0' COMMENT '0存在2删除',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_external_id` (`external_id`),
  UNIQUE KEY `uk_yak_no` (`yak_no`),
  KEY `idx_asset_status` (`asset_status`),
  KEY `idx_pasture_id` (`pasture_id`),
  KEY `idx_status_change_date` (`status_change_date`),
  KEY `idx_last_sync_time` (`last_sync_time`),
  KEY `idx_del_flag` (`del_flag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='牦牛资产档案';

CREATE TABLE IF NOT EXISTS `biz_yak_physio_series` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `yak_asset_id` bigint(20) NOT NULL,
  `collect_time` datetime NOT NULL,
  `body_temp` decimal(6,2) DEFAULT NULL,
  `steps` int(11) DEFAULT NULL,
  `env_temp` decimal(6,2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_yak_collect` (`yak_asset_id`,`collect_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='牦牛生理时序';

CREATE TABLE IF NOT EXISTS `biz_yak_growth_record` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `yak_asset_id` bigint(20) NOT NULL,
  `day_age` int(11) DEFAULT NULL,
  `weight_kg` decimal(10,2) DEFAULT NULL,
  `height_cm` decimal(10,2) DEFAULT NULL,
  `chest_cm` decimal(10,2) DEFAULT NULL,
  `length_cm` decimal(10,2) DEFAULT NULL,
  `collect_time` datetime DEFAULT NULL,
  `data_kind` tinyint(4) NOT NULL DEFAULT '1' COMMENT '1实测2预测',
  PRIMARY KEY (`id`),
  KEY `idx_yak_growth` (`yak_asset_id`,`collect_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='牦牛生长性状';

CREATE TABLE IF NOT EXISTS `biz_yak_reproduction_record` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `yak_asset_id` bigint(20) NOT NULL,
  `calving_date` date DEFAULT NULL COMMENT '分娩日期',
  `day_age` int(11) DEFAULT NULL,
  `parity` int(11) DEFAULT NULL,
  `calving_interval_days` int(11) DEFAULT NULL,
  `calf_count` int(11) DEFAULT NULL,
  `live_calf_count` int(11) DEFAULT NULL COMMENT '成活牦牛数量',
  `calf_survival_rate` decimal(6,4) DEFAULT NULL,
  `calf_birth_weight_kg` decimal(10,2) DEFAULT NULL,
  `dystocia_flag` char(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_yak_repro` (`yak_asset_id`,`parity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='牦牛繁殖性能';

CREATE TABLE IF NOT EXISTS `biz_yak_feeding_record` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `yak_asset_id` bigint(20) NOT NULL,
  `feed_target` varchar(32) DEFAULT NULL COMMENT '补饲对象',
  `start_day_age` int(11) DEFAULT NULL,
  `end_day_age` int(11) DEFAULT NULL,
  `feed_type` varchar(64) DEFAULT NULL,
  `total_supplement_kg` decimal(10,2) DEFAULT NULL COMMENT '总补饲量kg',
  `daily_supplement_kg` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_yak_feeding` (`yak_asset_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='牦牛饲喂数据';

CREATE TABLE IF NOT EXISTS `biz_yak_pen_rel` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `yak_asset_id` bigint(20) NOT NULL,
  `pen_name` varchar(128) NOT NULL COMMENT '圈舍名称',
  PRIMARY KEY (`id`),
  KEY `idx_yak_pen` (`yak_asset_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='牦牛圈舍关联';

CREATE TABLE IF NOT EXISTS `biz_yak_batch_rel` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `yak_asset_id` bigint(20) NOT NULL,
  `batch_no` varchar(64) NOT NULL COMMENT '批次编号',
  PRIMARY KEY (`id`),
  KEY `idx_yak_batch` (`yak_asset_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='牦牛批次关联';