/*
SQLyog Ultimate v11.24 (32 bit)
MySQL - 5.7.41 : Database - lh-afms
*********************************************************************
*/

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`lh-afms` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `lh-afms`;

/*Table structure for table `afms_basic_aquatic` */

DROP TABLE IF EXISTS `afms_basic_aquatic`;

CREATE TABLE `afms_basic_aquatic` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL COMMENT '动保名称',
  `category` varchar(255) NOT NULL COMMENT '类别',
  `brand` varchar(255) NOT NULL COMMENT '品牌',
  `measurement_unit_id` int(11) NOT NULL COMMENT '计量单位ID',
  `specification_unit` varchar(255) NOT NULL COMMENT '规格单位',
  `supplier` varchar(255) NOT NULL COMMENT '供应商',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `supplier_id` int(11) NOT NULL COMMENT '供应商ID',
  PRIMARY KEY (`id`),
  KEY `measurement_unit_id` (`measurement_unit_id`),
  KEY `FK_afms_basic_aquatic_supplier` (`supplier_id`),
  CONSTRAINT `FK_afms_basic_aquatic_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `afms_basic_supplier` (`id`),
  CONSTRAINT `afms_basic_aquatic_ibfk_1` FOREIGN KEY (`measurement_unit_id`) REFERENCES `afms_basic_measurement_unit` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='基础信息管理-动保信息管理';

/*Data for the table `afms_basic_aquatic` */

/*Table structure for table `afms_basic_brand` */

DROP TABLE IF EXISTS `afms_basic_brand`;

CREATE TABLE `afms_basic_brand` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(255) NOT NULL COMMENT '名称',
  `description` text NOT NULL COMMENT '描述',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='基础信息管理-品牌信息管理';

/*Data for the table `afms_basic_brand` */

/*Table structure for table `afms_basic_category` */

DROP TABLE IF EXISTS `afms_basic_category`;

CREATE TABLE `afms_basic_category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(255) NOT NULL COMMENT '名称',
  `description` text NOT NULL COMMENT '描述',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='基础信息管理-品类信息管理';

/*Data for the table `afms_basic_category` */

/*Table structure for table `afms_basic_customer` */

DROP TABLE IF EXISTS `afms_basic_customer`;

CREATE TABLE `afms_basic_customer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL COMMENT '客户名称',
  `customer_type_id` int(11) NOT NULL COMMENT '客户类型ID',
  `contact_person` varchar(255) NOT NULL COMMENT '联系人',
  `contact_phone` varchar(255) NOT NULL COMMENT '联系电话',
  `contact_address` varchar(255) NOT NULL COMMENT '联系地址',
  `taxpayer_identification_number` varchar(255) NOT NULL COMMENT '纳税人识别号',
  `bank` varchar(255) NOT NULL COMMENT '开户行',
  `account_name` varchar(255) NOT NULL COMMENT '用户名',
  `account_number` varchar(255) NOT NULL COMMENT '账号',
  `business_license` varchar(255) NOT NULL COMMENT '营业执照',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `customer_type_id` (`customer_type_id`),
  CONSTRAINT `afms_basic_customer_ibfk_1` FOREIGN KEY (`customer_type_id`) REFERENCES `afms_basic_customer_type` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='基础信息管理-客户信息管理';

/*Data for the table `afms_basic_customer` */

/*Table structure for table `afms_basic_customer_type` */

DROP TABLE IF EXISTS `afms_basic_customer_type`;

CREATE TABLE `afms_basic_customer_type` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(255) NOT NULL COMMENT '名称',
  `description` text NOT NULL COMMENT '描述',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='基础信息管理-客户类型信息管理';

/*Data for the table `afms_basic_customer_type` */

/*Table structure for table `afms_basic_feed` */

DROP TABLE IF EXISTS `afms_basic_feed`;

CREATE TABLE `afms_basic_feed` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL COMMENT '饲料名称',
  `brand_id` int(11) NOT NULL COMMENT '品牌ID',
  `category_id` int(11) NOT NULL COMMENT '品类ID',
  `specification_unit` varchar(255) NOT NULL COMMENT '规格单位（kg/包）',
  `supplier` varchar(255) NOT NULL COMMENT '供应商',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `supplier_id` int(11) NOT NULL COMMENT '供应商ID',
  PRIMARY KEY (`id`),
  KEY `brand_id` (`brand_id`),
  KEY `category_id` (`category_id`),
  KEY `FK_afms_basic_feed_supplier` (`supplier_id`),
  CONSTRAINT `FK_afms_basic_feed_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `afms_basic_supplier` (`id`),
  CONSTRAINT `afms_basic_feed_ibfk_1` FOREIGN KEY (`brand_id`) REFERENCES `afms_basic_brand` (`id`),
  CONSTRAINT `afms_basic_feed_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `afms_basic_category` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='基础信息管理-饲料信息管理';

/*Data for the table `afms_basic_feed` */

/*Table structure for table `afms_basic_fry` */

DROP TABLE IF EXISTS `afms_basic_fry`;

CREATE TABLE `afms_basic_fry` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL COMMENT '苗种名称',
  `species_id` int(11) NOT NULL COMMENT '养殖品种ID',
  `category` enum('鱼苗','虾苗','螺苗','鱼卵') NOT NULL COMMENT '类别',
  `quantity_unit` enum('尾','万尾','粒') NOT NULL COMMENT '数量单位',
  `specification_unit` varchar(255) NOT NULL COMMENT '规格单位',
  `supplier` varchar(255) NOT NULL COMMENT '供应商',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `supplier_id` int(11) NOT NULL COMMENT '供应商ID',
  PRIMARY KEY (`id`),
  KEY `species_id` (`species_id`),
  KEY `FK_afms_basic_fry_supplier` (`supplier_id`),
  CONSTRAINT `FK_afms_basic_fry_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `afms_basic_supplier` (`id`),
  CONSTRAINT `afms_basic_fry_ibfk_1` FOREIGN KEY (`species_id`) REFERENCES `afms_basic_species` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='基础信息管理-苗种信息管理';

/*Data for the table `afms_basic_fry` */

/*Table structure for table `afms_basic_measurement_unit` */

DROP TABLE IF EXISTS `afms_basic_measurement_unit`;

CREATE TABLE `afms_basic_measurement_unit` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL COMMENT '名称',
  `description` text NOT NULL COMMENT '描述',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='基础信息管理-计量单位信息管理';

/*Data for the table `afms_basic_measurement_unit` */

/*Table structure for table `afms_basic_pond` */

DROP TABLE IF EXISTS `afms_basic_pond`;

CREATE TABLE `afms_basic_pond` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `name` varchar(255) NOT NULL COMMENT '塘口名称',
  `person_in_charge` varchar(255) NOT NULL COMMENT '负责人',
  `species_ids` varchar(255) NOT NULL COMMENT '养殖品种ID集合（以英文逗号分割）',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='基础信息管理-塘口信息管理';

/*Data for the table `afms_basic_pond` */

/*Table structure for table `afms_basic_species` */

DROP TABLE IF EXISTS `afms_basic_species`;

CREATE TABLE `afms_basic_species` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL COMMENT '品种名称',
  `water_type` enum('淡水','海水') NOT NULL COMMENT '水体类型',
  `weight_unit` enum('斤','kg','g') NOT NULL COMMENT '重量单位',
  `quantity_unit` enum('尾','粒') NOT NULL COMMENT '数量单位',
  `specification_unit` varchar(255) NOT NULL COMMENT '规格单位',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='基础信息管理-养殖品种管理';

/*Data for the table `afms_basic_species` */

/*Table structure for table `afms_basic_supplier` */

DROP TABLE IF EXISTS `afms_basic_supplier`;

CREATE TABLE `afms_basic_supplier` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL COMMENT '供应商名称',
  `supplier_type_id` int(11) NOT NULL COMMENT '供应商类型ID',
  `contact_person` varchar(255) NOT NULL COMMENT '联系人',
  `contact_phone` varchar(255) NOT NULL COMMENT '联系电话',
  `contact_address` varchar(255) NOT NULL COMMENT '联系地址',
  `taxpayer_identification_number` varchar(255) NOT NULL COMMENT '纳税人识别号',
  `bank` varchar(255) NOT NULL COMMENT '开户行',
  `account_name` varchar(255) NOT NULL COMMENT '用户名',
  `account_number` varchar(255) NOT NULL COMMENT '账号',
  `business_license` varchar(255) NOT NULL COMMENT '营业执照',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `supplier_type_id` (`supplier_type_id`),
  CONSTRAINT `afms_basic_supplier_ibfk_1` FOREIGN KEY (`supplier_type_id`) REFERENCES `afms_basic_supplier_type` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='基础信息管理-供应商信息管理';

/*Data for the table `afms_basic_supplier` */

/*Table structure for table `afms_basic_supplier_type` */

DROP TABLE IF EXISTS `afms_basic_supplier_type`;

CREATE TABLE `afms_basic_supplier_type` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(255) NOT NULL COMMENT '名称',
  `description` text NOT NULL COMMENT '描述',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='基础信息管理-供应商类型信息管理';

/*Data for the table `afms_basic_supplier_type` */

/*Table structure for table `afms_cost_expense` */

DROP TABLE IF EXISTS `afms_cost_expense`;

CREATE TABLE `afms_cost_expense` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL COMMENT '时间',
  `category` varchar(255) NOT NULL COMMENT '类别',
  `cost` decimal(10,2) NOT NULL COMMENT '费用',
  `remarks` text COMMENT '备注',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='成本与收益- 成本模块';

/*Data for the table `afms_cost_expense` */

/*Table structure for table `afms_data_loss` */

DROP TABLE IF EXISTS `afms_data_loss`;

CREATE TABLE `afms_data_loss` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `loss_record_time` datetime NOT NULL COMMENT '损失记录时间',
  `pond_id` int(11) NOT NULL COMMENT '塘口ID',
  `species` varchar(255) NOT NULL COMMENT '养殖品种',
  `loss_count` int(11) NOT NULL COMMENT '损失数量',
  `loss_weight` decimal(10,2) NOT NULL COMMENT '损失重量',
  `loss_specification` decimal(6,2) NOT NULL COMMENT '损失规格',
  `estimated_reason` varchar(255) NOT NULL COMMENT '预估原因',
  `image_path` varchar(255) DEFAULT NULL COMMENT '图片',
  PRIMARY KEY (`id`),
  KEY `pond_id` (`pond_id`),
  CONSTRAINT `afms_data_loss_ibfk_1` FOREIGN KEY (`pond_id`) REFERENCES `afms_basic_pond` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `afms_data_loss` */

/*Table structure for table `afms_data_medicine` */

DROP TABLE IF EXISTS `afms_data_medicine`;

CREATE TABLE `afms_data_medicine` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `feeding_start_time` datetime NOT NULL COMMENT '投喂开始时间',
  `pond_id` int(11) NOT NULL COMMENT '塘口ID',
  `medicine_name` varchar(255) NOT NULL COMMENT '动保',
  `medicine_amount` decimal(10,2) NOT NULL COMMENT '动保使用量',
  `unit` varchar(255) NOT NULL COMMENT '计量单位',
  `reason` varchar(255) NOT NULL COMMENT '用药原因',
  PRIMARY KEY (`id`),
  KEY `pond_id` (`pond_id`),
  CONSTRAINT `afms_data_medicine_ibfk_1` FOREIGN KEY (`pond_id`) REFERENCES `afms_basic_pond` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `afms_data_medicine` */

/*Table structure for table `afms_data_sampling` */

DROP TABLE IF EXISTS `afms_data_sampling`;

CREATE TABLE `afms_data_sampling` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sampling_time` datetime NOT NULL COMMENT '采样时间',
  `pond_id` int(11) NOT NULL COMMENT '塘口ID',
  `species` varchar(255) NOT NULL COMMENT '养殖品种',
  `sample_count` int(11) NOT NULL COMMENT '样本数量',
  `sample_weight` decimal(10,2) NOT NULL COMMENT '样本重量',
  `average_length` decimal(6,2) NOT NULL COMMENT '平均体长',
  `average_specification` decimal(6,2) NOT NULL COMMENT '平均规格',
  `estimated_pond_weight` decimal(10,2) NOT NULL COMMENT '预估存塘重量',
  `image_path` varchar(255) DEFAULT NULL COMMENT '图片',
  PRIMARY KEY (`id`),
  KEY `pond_id` (`pond_id`),
  CONSTRAINT `afms_data_sampling_ibfk_1` FOREIGN KEY (`pond_id`) REFERENCES `afms_basic_pond` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `afms_data_sampling` */

/*Table structure for table `afms_data_water_quality` */

DROP TABLE IF EXISTS `afms_data_water_quality`;

CREATE TABLE `afms_data_water_quality` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `test_time` datetime NOT NULL COMMENT '检测时间',
  `pond_id` int(11) NOT NULL COMMENT '塘口ID',
  `weather` varchar(255) NOT NULL COMMENT '天气',
  `water_temperature` decimal(5,2) NOT NULL COMMENT '水温（℃）',
  `dissolved_oxygen` decimal(5,2) NOT NULL COMMENT '溶氧（mg/L）',
  `pH` decimal(5,2) NOT NULL COMMENT '酸碱度（pH）',
  `ammonia_nitrogen` decimal(5,2) NOT NULL COMMENT '氨氮（ppm）',
  `nitrite` decimal(5,2) NOT NULL COMMENT '亚硝酸盐（ppm）',
  `salinity` decimal(5,2) NOT NULL COMMENT '盐度',
  `total_alkalinity` decimal(5,2) NOT NULL COMMENT '总碱度',
  `total_hardness` decimal(5,2) NOT NULL COMMENT '总硬度',
  PRIMARY KEY (`id`),
  KEY `pond_id` (`pond_id`),
  CONSTRAINT `afms_data_water_quality_ibfk_1` FOREIGN KEY (`pond_id`) REFERENCES `afms_basic_pond` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `afms_data_water_quality` */

/*Table structure for table `afms_inventory_feed_purchase` */

DROP TABLE IF EXISTS `afms_inventory_feed_purchase`;

CREATE TABLE `afms_inventory_feed_purchase` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `feed_id` int(11) NOT NULL COMMENT '饲料ID（关联饲料表）',
  `purchase_time` datetime NOT NULL COMMENT '进货时间',
  `weight` decimal(10,2) NOT NULL COMMENT '重量（kg）',
  `unit_price` decimal(10,2) NOT NULL COMMENT '单价（元）',
  `total_price` decimal(10,2) NOT NULL COMMENT '总价（元）',
  PRIMARY KEY (`id`),
  KEY `feed_id` (`feed_id`),
  CONSTRAINT `afms_inventory_feed_purchase_ibfk_1` FOREIGN KEY (`feed_id`) REFERENCES `afms_basic_feed` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='进货模块-饲料进货';

/*Data for the table `afms_inventory_feed_purchase` */

/*Table structure for table `afms_inventory_feed_stock` */

DROP TABLE IF EXISTS `afms_inventory_feed_stock`;

CREATE TABLE `afms_inventory_feed_stock` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `feed_id` int(11) NOT NULL COMMENT '饲料ID（关联饲料信息表）',
  `stock` int(11) NOT NULL COMMENT '库存',
  `weight` decimal(10,2) NOT NULL COMMENT '重量（kg）',
  `unit_price` decimal(10,2) NOT NULL COMMENT '单价（元）',
  `total_price` decimal(10,2) NOT NULL COMMENT '总价（元）',
  PRIMARY KEY (`id`),
  KEY `feed_id` (`feed_id`),
  CONSTRAINT `afms_inventory_feed_stock_ibfk_1` FOREIGN KEY (`feed_id`) REFERENCES `afms_basic_feed` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='库存模块- 饲料库存';

/*Data for the table `afms_inventory_feed_stock` */

/*Table structure for table `afms_inventory_medicine_purchase` */

DROP TABLE IF EXISTS `afms_inventory_medicine_purchase`;

CREATE TABLE `afms_inventory_medicine_purchase` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `medicine_id` int(11) NOT NULL COMMENT '动保ID（关联动保表）',
  `purchase_time` datetime NOT NULL COMMENT '进货时间',
  `quantity` int(11) NOT NULL COMMENT '数量',
  `unit_price` decimal(10,2) NOT NULL COMMENT '单价（元）',
  `total_price` decimal(10,2) NOT NULL COMMENT '总价（元）',
  PRIMARY KEY (`id`),
  KEY `medicine_id` (`medicine_id`),
  CONSTRAINT `afms_inventory_medicine_purchase_ibfk_1` FOREIGN KEY (`medicine_id`) REFERENCES `afms_basic_aquatic` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='进货模块-动保进货';

/*Data for the table `afms_inventory_medicine_purchase` */

/*Table structure for table `afms_inventory_medicine_stock` */

DROP TABLE IF EXISTS `afms_inventory_medicine_stock`;

CREATE TABLE `afms_inventory_medicine_stock` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `medicine_id` int(11) NOT NULL COMMENT '动保ID（关联动保信息表）',
  `stock` int(11) NOT NULL COMMENT '库存',
  `weight` decimal(10,2) NOT NULL COMMENT '重量（kg）',
  `unit_price` decimal(10,2) NOT NULL COMMENT '单价（元）',
  `total_price` decimal(10,2) NOT NULL COMMENT '总价（元）',
  PRIMARY KEY (`id`),
  KEY `medicine_id` (`medicine_id`),
  CONSTRAINT `afms_inventory_medicine_stock_ibfk_1` FOREIGN KEY (`medicine_id`) REFERENCES `afms_basic_aquatic` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='库存模块-动保库存';

/*Data for the table `afms_inventory_medicine_stock` */

/*Table structure for table `afms_inventory_seed_purchase` */

DROP TABLE IF EXISTS `afms_inventory_seed_purchase`;

CREATE TABLE `afms_inventory_seed_purchase` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `seed_id` int(11) NOT NULL COMMENT '苗种ID（关联苗种表）',
  `purchase_time` datetime NOT NULL COMMENT '进货时间',
  `quantity` int(11) NOT NULL COMMENT '数量',
  `unit_price` decimal(10,2) NOT NULL COMMENT '单价（元）',
  `total_price` decimal(10,2) NOT NULL COMMENT '总价（元）',
  PRIMARY KEY (`id`),
  KEY `seed_id` (`seed_id`),
  CONSTRAINT `afms_inventory_seed_purchase_ibfk_1` FOREIGN KEY (`seed_id`) REFERENCES `afms_basic_fry` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='进货模块- 苗种进货';

/*Data for the table `afms_inventory_seed_purchase` */

/*Table structure for table `afms_inventory_seed_stock` */

DROP TABLE IF EXISTS `afms_inventory_seed_stock`;

CREATE TABLE `afms_inventory_seed_stock` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `seed_id` int(11) NOT NULL COMMENT '苗种ID（关联苗种信息表）',
  `stock` int(11) NOT NULL COMMENT '库存',
  `weight` decimal(10,2) NOT NULL COMMENT '重量（kg）',
  `unit_price` decimal(10,2) NOT NULL COMMENT '单价（元）',
  `total_price` decimal(10,2) NOT NULL COMMENT '总价（元）',
  PRIMARY KEY (`id`),
  KEY `seed_id` (`seed_id`),
  CONSTRAINT `afms_inventory_seed_stock_ibfk_1` FOREIGN KEY (`seed_id`) REFERENCES `afms_basic_fry` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='库存模块-苗种库存';

/*Data for the table `afms_inventory_seed_stock` */

/*Table structure for table `afms_operation_draining` */

DROP TABLE IF EXISTS `afms_operation_draining`;

CREATE TABLE `afms_operation_draining` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `draining_date` datetime NOT NULL COMMENT '清塘时间',
  `pond_id` int(11) NOT NULL COMMENT '塘口ID',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `pond_id` (`pond_id`),
  CONSTRAINT `afms_operation_draining_ibfk_1` FOREIGN KEY (`pond_id`) REFERENCES `afms_basic_pond` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='养殖操作-清塘模块';

/*Data for the table `afms_operation_draining` */

/*Table structure for table `afms_operation_feed` */

DROP TABLE IF EXISTS `afms_operation_feed`;

CREATE TABLE `afms_operation_feed` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `start_time` datetime NOT NULL COMMENT '开始时间',
  `end_time` datetime NOT NULL COMMENT '结束时间',
  `meal_count` int(11) NOT NULL COMMENT '餐数',
  `pond_id` int(11) NOT NULL COMMENT '塘口ID',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `pond_id` (`pond_id`),
  CONSTRAINT `afms_operation_feed_ibfk_1` FOREIGN KEY (`pond_id`) REFERENCES `afms_basic_pond` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='养殖操作-饲料投喂模块';

/*Data for the table `afms_operation_feed` */

/*Table structure for table `afms_operation_feed_details` */

DROP TABLE IF EXISTS `afms_operation_feed_details`;

CREATE TABLE `afms_operation_feed_details` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `feed_record_id` int(11) NOT NULL COMMENT '饲料投喂记录ID',
  `feed_id` int(11) NOT NULL COMMENT '饲料ID',
  `feed_amount` float NOT NULL COMMENT '投放量/Kg',
  PRIMARY KEY (`id`),
  KEY `feed_record_id` (`feed_record_id`),
  KEY `feed_id` (`feed_id`),
  CONSTRAINT `afms_operation_feed_details_ibfk_1` FOREIGN KEY (`feed_record_id`) REFERENCES `afms_operation_feed` (`id`),
  CONSTRAINT `afms_operation_feed_details_ibfk_2` FOREIGN KEY (`feed_id`) REFERENCES `afms_basic_feed` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='养殖操作-饲料投喂模块明细';

/*Data for the table `afms_operation_feed_details` */

/*Table structure for table `afms_operation_seed_stock` */

DROP TABLE IF EXISTS `afms_operation_seed_stock`;

CREATE TABLE `afms_operation_seed_stock` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stocking_date` datetime NOT NULL COMMENT '投放时间',
  `pond_id` int(11) NOT NULL COMMENT '塘口ID',
  `fry_id` int(11) NOT NULL COMMENT '投放苗种ID',
  `quantity` float NOT NULL COMMENT '数量',
  `specification` varchar(255) NOT NULL COMMENT '规格',
  `unit_price` float NOT NULL COMMENT '单价（元）',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `pond_id` (`pond_id`),
  KEY `fry_id` (`fry_id`),
  CONSTRAINT `afms_operation_seed_stock_ibfk_1` FOREIGN KEY (`pond_id`) REFERENCES `afms_basic_pond` (`id`),
  CONSTRAINT `afms_operation_seed_stock_ibfk_2` FOREIGN KEY (`fry_id`) REFERENCES `afms_basic_fry` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='养殖操作-投放模块';

/*Data for the table `afms_operation_seed_stock` */

/*Table structure for table `afms_operation_transfer` */

DROP TABLE IF EXISTS `afms_operation_transfer`;

CREATE TABLE `afms_operation_transfer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `transfer_date` datetime NOT NULL COMMENT '搬塘时间',
  `pond_id` int(11) NOT NULL COMMENT '塘口ID',
  `species_id` int(11) NOT NULL COMMENT '养殖品种ID',
  `quantity` float NOT NULL COMMENT '搬出数量',
  `weight` float NOT NULL COMMENT '搬出重量',
  `unit_price` float NOT NULL COMMENT '单价',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `pond_id` (`pond_id`),
  KEY `species_id` (`species_id`),
  CONSTRAINT `afms_operation_transfer_ibfk_1` FOREIGN KEY (`pond_id`) REFERENCES `afms_basic_pond` (`id`),
  CONSTRAINT `afms_operation_transfer_ibfk_2` FOREIGN KEY (`species_id`) REFERENCES `afms_basic_species` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='养殖操作-搬塘模块';

/*Data for the table `afms_operation_transfer` */

/*Table structure for table `afms_operation_transfer_details` */

DROP TABLE IF EXISTS `afms_operation_transfer_details`;

CREATE TABLE `afms_operation_transfer_details` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `transfer_id` int(11) NOT NULL COMMENT '搬塘记录ID',
  `target_pond_id` int(11) NOT NULL COMMENT '转入塘口ID',
  `quantity` float NOT NULL COMMENT '转入数量',
  `weight` float NOT NULL COMMENT '转入重量',
  PRIMARY KEY (`id`),
  KEY `transfer_id` (`transfer_id`),
  KEY `target_pond_id` (`target_pond_id`),
  CONSTRAINT `afms_operation_transfer_details_ibfk_1` FOREIGN KEY (`transfer_id`) REFERENCES `afms_operation_transfer` (`id`),
  CONSTRAINT `afms_operation_transfer_details_ibfk_2` FOREIGN KEY (`target_pond_id`) REFERENCES `afms_basic_pond` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='养殖操作-搬塘模块明细';

/*Data for the table `afms_operation_transfer_details` */

/*Table structure for table `afms_operation_water_change` */

DROP TABLE IF EXISTS `afms_operation_water_change`;

CREATE TABLE `afms_operation_water_change` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `record_date` datetime NOT NULL COMMENT '换水记录时间',
  `pond_id` int(11) NOT NULL COMMENT '塘口ID',
  `inflow` float NOT NULL COMMENT '进水量（立方米）',
  `outflow` float NOT NULL COMMENT '出水量（立方米）',
  `photo` varchar(255) NOT NULL COMMENT '照片',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `pond_id` (`pond_id`),
  CONSTRAINT `afms_operation_water_change_ibfk_1` FOREIGN KEY (`pond_id`) REFERENCES `afms_basic_pond` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='养殖操作-换水模块';

/*Data for the table `afms_operation_water_change` */

/*Table structure for table `afms_revenue_harvest` */

DROP TABLE IF EXISTS `afms_revenue_harvest`;

CREATE TABLE `afms_revenue_harvest` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `harvest_date` date NOT NULL COMMENT '收获时间',
  `pond_id` int(11) NOT NULL COMMENT '塘口（关联塘口信息表）',
  `species_id` int(11) NOT NULL COMMENT '养殖品种',
  `customer` varchar(255) NOT NULL COMMENT '客户',
  `total_weight` decimal(10,2) NOT NULL COMMENT '收货总重量/斤',
  `total_price` decimal(10,2) NOT NULL COMMENT '收获总价格/元',
  `labor_cost` decimal(10,2) NOT NULL COMMENT '人工成本/元',
  `transportation_cost` decimal(10,2) NOT NULL COMMENT '运输成本/元',
  PRIMARY KEY (`id`),
  KEY `pond_id` (`pond_id`),
  KEY `species_id` (`species_id`),
  CONSTRAINT `afms_revenue_harvest_ibfk_1` FOREIGN KEY (`pond_id`) REFERENCES `afms_basic_pond` (`id`),
  CONSTRAINT `afms_revenue_harvest_ibfk_2` FOREIGN KEY (`species_id`) REFERENCES `afms_basic_species` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='成本与收益- 收获模块';

/*Data for the table `afms_revenue_harvest` */

/*Table structure for table `afms_revenue_harvest_detail` */

DROP TABLE IF EXISTS `afms_revenue_harvest_detail`;

CREATE TABLE `afms_revenue_harvest_detail` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `harvest_id` int(11) NOT NULL COMMENT '收获（关联收获表）',
  `specification_range` varchar(255) NOT NULL COMMENT '规格范围（xx~xx）',
  `unit_price` decimal(10,2) NOT NULL COMMENT '单价/元',
  `weight` decimal(10,2) NOT NULL COMMENT '重量/斤',
  `quantity` int(11) NOT NULL COMMENT '收获数量/尾',
  `harvest_value` decimal(10,2) NOT NULL COMMENT '收获产值/元',
  PRIMARY KEY (`id`),
  KEY `harvest_id` (`harvest_id`),
  CONSTRAINT `afms_revenue_harvest_detail_ibfk_1` FOREIGN KEY (`harvest_id`) REFERENCES `afms_revenue_harvest` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='成本与收益- 收获模块明细';

/*Data for the table `afms_revenue_harvest_detail` */

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;