USE cattletrade;

-- 创建屠宰场管理相关表
-- 1. 屠宰场表（slaughter_house）
-- 2. 进场表（slaughter_entry）

-- ============================================
-- 1. 屠宰场表（slaughter_house）
-- ============================================
CREATE TABLE IF NOT EXISTS `slaughter_house` (
    `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `house_name` VARCHAR(100) NOT NULL COMMENT '屠宰场名称',
    `slaughter_code` VARCHAR(50) NOT NULL COMMENT '屠宰场编码，格式：SLyyyyMMddXXXX',
    `capacity` INT(11) DEFAULT NULL COMMENT '容量（头数）',
    `address` VARCHAR(255) DEFAULT NULL COMMENT '地址',
    `longitude` VARCHAR(50) DEFAULT NULL COMMENT '经度',
    `latitude` VARCHAR(50) DEFAULT NULL COMMENT '纬度',
    `status` TINYINT(1) DEFAULT 1 COMMENT '状态：1-启用，0-禁用',
    `remark` TEXT COMMENT '备注',
    `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `created_by` INT(11) DEFAULT NULL COMMENT '创建人ID',
    `updated_by` INT(11) DEFAULT NULL COMMENT '更新人ID',
    `is_delete` TINYINT(1) DEFAULT 0 COMMENT '逻辑删除：0-未删除，1-已删除',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_slaughter_code` (`slaughter_code`),
    KEY `idx_house_name` (`house_name`),
    KEY `idx_status` (`status`),
    KEY `idx_is_delete` (`is_delete`),
    KEY `idx_create_time` (`create_time`),
    KEY `idx_status_is_delete` (`status`, `is_delete`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='屠宰场表';

-- ============================================
-- 2. 进场表（slaughter_entry）
-- ============================================
CREATE TABLE IF NOT EXISTS `slaughter_entry` (
    `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `slaughter_house_id` INT(11) NOT NULL COMMENT '屠宰场ID',
    `delivery_id` INT(11) NOT NULL COMMENT '运送清单ID（关联delivery表）',
    `order_id` INT(11) DEFAULT NULL COMMENT '订单ID（关联order表）',
    `start_location` VARCHAR(255) DEFAULT NULL COMMENT '起始地',
    `start_lon` VARCHAR(50) DEFAULT NULL COMMENT '起始经度',
    `start_lat` VARCHAR(50) DEFAULT NULL COMMENT '起始纬度',
    `end_location` VARCHAR(255) DEFAULT NULL COMMENT '目的地',
    `end_lon` VARCHAR(50) DEFAULT NULL COMMENT '目的地经度',
    `end_lat` VARCHAR(50) DEFAULT NULL COMMENT '目的地纬度',
    `driver_name` VARCHAR(50) DEFAULT NULL COMMENT '司机姓名',
    `driver_mobile` VARCHAR(20) DEFAULT NULL COMMENT '联系方式',
    `license_plate` VARCHAR(20) DEFAULT NULL COMMENT '车牌号',
    `yield_rate` DECIMAL(5,2) DEFAULT NULL COMMENT '出肉率（%）',
    `remark` TEXT COMMENT '备注',
    `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `created_by` INT(11) DEFAULT NULL COMMENT '创建人ID',
    `updated_by` INT(11) DEFAULT NULL COMMENT '更新人ID',
    `is_delete` TINYINT(1) DEFAULT 0 COMMENT '逻辑删除：0-未删除，1-已删除',
    PRIMARY KEY (`id`),
    KEY `idx_slaughter_house_id` (`slaughter_house_id`),
    KEY `idx_delivery_id` (`delivery_id`),
    KEY `idx_order_id` (`order_id`),
    KEY `idx_is_delete` (`is_delete`),
    KEY `idx_create_time` (`create_time`),
    CONSTRAINT `fk_slaughter_entry_house` FOREIGN KEY (`slaughter_house_id`) REFERENCES `slaughter_house` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT `fk_slaughter_entry_delivery` FOREIGN KEY (`delivery_id`) REFERENCES `delivery` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT `fk_slaughter_entry_order` FOREIGN KEY (`order_id`) REFERENCES `order` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='屠宰场进场表';

