huimv-baqing/sql/biz_grass_livestock_imbalance_warning.sql

-- 草畜失衡预警（与牦牛疾病预警分离；物理删除）
-- 数据来源：家庭牧场 / 农牧户 / 养殖大户填报触发
-- 唯一键：uk_source_key (source_key)，如 FR-{id} / FH-{id} / LLF-{id}
DROP TABLE IF EXISTS `biz_grass_livestock_imbalance_warning`;
CREATE TABLE `biz_grass_livestock_imbalance_warning` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `source_key` varchar(64) NOT NULL COMMENT '业务键，如 FR-123 / FH-123 / LLF-123',
  `data_source` varchar(32) NOT NULL COMMENT 'family_ranch / farmer_household / large_livestock_farmer',
  `source_record_id` bigint(20) NOT NULL COMMENT '源填报记录ID',
  `stat_year` int(11) DEFAULT NULL COMMENT '所属年份',
  `stat_month` int(11) DEFAULT NULL COMMENT '所属月份(1-12)',
  `town_dept_id` bigint(20) DEFAULT NULL COMMENT '乡镇dept_id',
  `town_name` varchar(64) DEFAULT NULL COMMENT '乡镇名称',
  `village_dept_id` bigint(20) DEFAULT NULL COMMENT '村dept_id',
  `village_name` varchar(128) DEFAULT NULL COMMENT '村名称',
  `subject_name` varchar(200) DEFAULT NULL COMMENT '主体名称（企业名或村名等）',
  `approved_carrying_capacity` decimal(18,4) DEFAULT NULL COMMENT '核定年末草畜平衡载畜量',
  `year_end_stock_sheep_unit` decimal(18,4) DEFAULT NULL COMMENT '年底牲畜存栏(绵羊单位)',
  `alert_time` datetime NOT NULL COMMENT '预警时间',
  `alert_message` varchar(1024) NOT NULL COMMENT '预警信息',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_source_key` (`source_key`),
  KEY `idx_data_source_record` (`data_source`, `source_record_id`),
  KEY `idx_alert_time` (`alert_time`),
  KEY `idx_stat_year_month` (`stat_year`, `stat_month`),
  KEY `idx_town_dept_id` (`town_dept_id`),
  KEY `idx_village_dept_id` (`village_dept_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='草畜失衡预警';