-- ======================================
-- 锡林郭勒盟智慧养殖产业平台 - 数据库初始化脚本（第二部分）
-- 交易系统、商城管理、政府监管相关表
-- ======================================

SET FOREIGN_KEY_CHECKS = 0;

-- ======================================
-- 4. 交易系统相关表
-- ======================================

-- 合同表
CREATE TABLE IF NOT EXISTS `contracts` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '合同ID',
    `contract_number` VARCHAR(50) NOT NULL UNIQUE COMMENT '合同编号',
    `seller_id` BIGINT UNSIGNED NOT NULL COMMENT '卖方ID',
    `buyer_id` BIGINT UNSIGNED NOT NULL COMMENT '买方ID',
    `cattle_details` JSON COMMENT '牛只详情',
    `total_price` DECIMAL(15,2) NOT NULL COMMENT '总价',
    `currency` VARCHAR(10) DEFAULT 'CNY' COMMENT '货币',
    `contract_date` DATE NOT NULL COMMENT '合同日期',
    `effective_date` DATE COMMENT '生效日期',
    `expiry_date` DATE COMMENT '到期日期',
    `payment_terms` TEXT COMMENT '付款条款',
    `delivery_terms` TEXT COMMENT '交付条款',
    `contract_status` ENUM('draft', 'signed', 'active', 'completed', 'cancelled') DEFAULT 'draft' COMMENT '合同状态',
    `contract_file_url` VARCHAR(255) COMMENT '合同文件URL',
    `notes` TEXT COMMENT '备注',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    FOREIGN KEY (`seller_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`buyer_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_contract_number` (`contract_number`),
    INDEX `idx_seller` (`seller_id`),
    INDEX `idx_buyer` (`buyer_id`),
    INDEX `idx_status` (`contract_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='合同表';

-- 交易记录表
CREATE TABLE IF NOT EXISTS `transactions` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '交易ID',
    `cattle_id` BIGINT UNSIGNED NOT NULL COMMENT '牛只ID',
    `seller_id` BIGINT UNSIGNED NOT NULL COMMENT '卖方ID',
    `buyer_id` BIGINT UNSIGNED NOT NULL COMMENT '买方ID',
    `transaction_type` ENUM('direct', 'auction', 'platform') NOT NULL COMMENT '交易类型',
    `price` DECIMAL(12,2) NOT NULL COMMENT '交易价格',
    `currency` VARCHAR(10) DEFAULT 'CNY' COMMENT '货币',
    `transaction_date` DATETIME NOT NULL COMMENT '交易时间',
    `contract_id` BIGINT UNSIGNED COMMENT '合同ID',
    `status` ENUM('pending', 'completed', 'cancelled') DEFAULT 'pending' COMMENT '状态',
    `payment_status` ENUM('unpaid', 'partial', 'paid') DEFAULT 'unpaid' COMMENT '付款状态',
    `delivery_status` ENUM('pending', 'in_transit', 'delivered') DEFAULT 'pending' COMMENT '交付状态',
    `notes` TEXT COMMENT '备注',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    FOREIGN KEY (`cattle_id`) REFERENCES `cattle`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`seller_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`buyer_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`contract_id`) REFERENCES `contracts`(`id`) ON DELETE SET NULL,
    INDEX `idx_cattle_date` (`cattle_id`, `transaction_date`),
    INDEX `idx_seller` (`seller_id`),
    INDEX `idx_buyer` (`buyer_id`),
    INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='交易记录表';

-- ======================================
-- 5. 商城管理相关表
-- ======================================

-- 商品分类表
CREATE TABLE IF NOT EXISTS `product_categories` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '分类ID',
    `name` VARCHAR(50) NOT NULL COMMENT '分类名称',
    `parent_id` INT UNSIGNED DEFAULT 0 COMMENT '父分类ID',
    `level` TINYINT DEFAULT 1 COMMENT '层级',
    `sort_order` INT DEFAULT 0 COMMENT '排序',
    `image_url` VARCHAR(255) COMMENT '图片URL',
    `status` TINYINT DEFAULT 1 COMMENT '状态: 1-正常, 0-禁用',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX `idx_parent` (`parent_id`),
    INDEX `idx_level` (`level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品分类表';

-- 商品表
CREATE TABLE IF NOT EXISTS `products` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '商品ID',
    `name` VARCHAR(100) NOT NULL COMMENT '商品名称',
    `description` TEXT COMMENT '商品描述',
    `category_id` INT UNSIGNED COMMENT '分类ID',
    `sku` VARCHAR(50) UNIQUE COMMENT 'SKU',
    `price` DECIMAL(10,2) NOT NULL COMMENT '价格',
    `original_price` DECIMAL(10,2) COMMENT '原价',
    `currency` VARCHAR(10) DEFAULT 'CNY' COMMENT '货币',
    `stock_quantity` INT DEFAULT 0 COMMENT '库存数量',
    `min_stock` INT DEFAULT 0 COMMENT '最低库存',
    `unit` VARCHAR(20) COMMENT '单位',
    `weight` DECIMAL(8,3) COMMENT '重量(kg)',
    `origin` VARCHAR(100) COMMENT '产地',
    `production_date` DATE COMMENT '生产日期',
    `expiration_date` DATE COMMENT '保质期',
    `cattle_id` BIGINT UNSIGNED COMMENT '关联牛只ID',
    `status` ENUM('active', 'inactive', 'discontinued') DEFAULT 'active' COMMENT '状态',
    `image_urls` JSON COMMENT '图片URL列表',
    `tags` JSON COMMENT '标签',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    FOREIGN KEY (`cattle_id`) REFERENCES `cattle`(`id`) ON DELETE SET NULL,
    INDEX `idx_name` (`name`),
    INDEX `idx_category` (`category_id`),
    INDEX `idx_sku` (`sku`),
    INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';

-- 订单表
CREATE TABLE IF NOT EXISTS `orders` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '订单ID',
    `order_number` VARCHAR(50) NOT NULL UNIQUE COMMENT '订单编号',
    `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
    `total_amount` DECIMAL(12,2) NOT NULL COMMENT '订单总额',
    `currency` VARCHAR(10) DEFAULT 'CNY' COMMENT '货币',
    `order_status` ENUM('pending', 'confirmed', 'processing', 'shipped', 'delivered', 'cancelled', 'refunded') DEFAULT 'pending' COMMENT '订单状态',
    `payment_status` ENUM('unpaid', 'partial', 'paid') DEFAULT 'unpaid' COMMENT '付款状态',
    `shipping_status` ENUM('unshipped', 'shipped', 'delivered') DEFAULT 'unshipped' COMMENT '发货状态',
    `receiver_name` VARCHAR(50) COMMENT '收货人姓名',
    `receiver_phone` VARCHAR(20) COMMENT '收货人电话',
    `receiver_address` TEXT COMMENT '收货地址',
    `shipping_method` VARCHAR(50) COMMENT '配送方式',
    `shipping_fee` DECIMAL(10,2) DEFAULT 0.00 COMMENT '运费',
    `notes` TEXT COMMENT '备注',
    `payment_method` VARCHAR(50) COMMENT '付款方式',
    `paid_at` TIMESTAMP NULL COMMENT '付款时间',
    `shipped_at` TIMESTAMP NULL COMMENT '发货时间',
    `delivered_at` TIMESTAMP NULL COMMENT '送达时间',
    `cancelled_at` TIMESTAMP NULL COMMENT '取消时间',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_order_number` (`order_number`),
    INDEX `idx_user` (`user_id`),
    INDEX `idx_status` (`order_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

-- 订单项表
CREATE TABLE IF NOT EXISTS `order_items` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '订单项ID',
    `order_id` BIGINT UNSIGNED NOT NULL COMMENT '订单ID',
    `product_id` BIGINT UNSIGNED NOT NULL COMMENT '商品ID',
    `quantity` INT NOT NULL COMMENT '数量',
    `unit_price` DECIMAL(10,2) NOT NULL COMMENT '单价',
    `total_price` DECIMAL(12,2) NOT NULL COMMENT '总价',
    `cattle_id` BIGINT UNSIGNED COMMENT '关联牛只ID',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`cattle_id`) REFERENCES `cattle`(`id`) ON DELETE SET NULL,
    INDEX `idx_order` (`order_id`),
    INDEX `idx_product` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单项表';

-- 物流跟踪表
CREATE TABLE IF NOT EXISTS `logistics_tracking` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '物流ID',
    `order_id` BIGINT UNSIGNED NOT NULL COMMENT '订单ID',
    `tracking_number` VARCHAR(100) UNIQUE COMMENT '物流单号',
    `carrier` VARCHAR(50) COMMENT '承运商',
    `status` ENUM('pending', 'in_transit', 'delivered', 'exception') DEFAULT 'pending' COMMENT '物流状态',
    `origin` VARCHAR(255) COMMENT '起始地',
    `destination` VARCHAR(255) COMMENT '目的地',
    `estimated_delivery_date` DATE COMMENT '预计送达日期',
    `actual_delivery_date` DATE COMMENT '实际送达日期',
    `current_location` VARCHAR(255) COMMENT '当前位置',
    `tracking_info` JSON COMMENT '物流跟踪信息',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
    INDEX `idx_tracking_number` (`tracking_number`),
    INDEX `idx_order` (`order_id`),
    INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='物流跟踪表';

-- ======================================
-- 6. 政府监管相关表
-- ======================================

-- 政府监管报告表
CREATE TABLE IF NOT EXISTS `government_reports` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '报告ID',
    `report_type` ENUM('production', 'sales', 'disease', 'environment', 'finance') NOT NULL COMMENT '报告类型',
    `reporter_id` BIGINT UNSIGNED NOT NULL COMMENT '报告人ID',
    `reporting_period_start` DATE NOT NULL COMMENT '报告期开始日期',
    `reporting_period_end` DATE NOT NULL COMMENT '报告期结束日期',
    `data_content` JSON COMMENT '报告数据内容',
    `summary` TEXT COMMENT '摘要',
    `status` ENUM('draft', 'submitted', 'approved', 'rejected') DEFAULT 'draft' COMMENT '状态',
    `approver_id` BIGINT UNSIGNED COMMENT '审批人ID',
    `approval_notes` TEXT COMMENT '审批备注',
    `submitted_at` TIMESTAMP NULL COMMENT '提交时间',
    `approved_at` TIMESTAMP NULL COMMENT '审批时间',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    FOREIGN KEY (`reporter_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`approver_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_reporter` (`reporter_id`),
    INDEX `idx_type` (`report_type`),
    INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='政府监管报告表';

-- 新闻资讯表
CREATE TABLE IF NOT EXISTS `news_articles` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '新闻ID',
    `title` VARCHAR(150) NOT NULL COMMENT '标题',
    `subtitle` VARCHAR(200) COMMENT '副标题',
    `content` TEXT NOT NULL COMMENT '内容',
    `author` VARCHAR(50) COMMENT '作者',
    `source` VARCHAR(100) COMMENT '来源',
    `cover_image` VARCHAR(255) COMMENT '封面图片URL',
    `is_featured` BOOLEAN DEFAULT FALSE COMMENT '是否推荐',
    `status` ENUM('draft', 'published', 'archived') DEFAULT 'draft' COMMENT '状态',
    `publish_date` TIMESTAMP NULL COMMENT '发布时间',
    `category` VARCHAR(50) COMMENT '分类',
    `views` INT UNSIGNED DEFAULT 0 COMMENT '浏览量',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX `idx_status` (`status`),
    INDEX `idx_category` (`category`),
    INDEX `idx_publish_date` (`publish_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='新闻资讯表';

-- 政策公告表
CREATE TABLE IF NOT EXISTS `policy_announcements` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '公告ID',
    `title` VARCHAR(150) NOT NULL COMMENT '标题',
    `content` TEXT NOT NULL COMMENT '内容',
    `issuer` VARCHAR(100) NOT NULL COMMENT '发布机构',
    `issue_date` DATE NOT NULL COMMENT '发布日期',
    `effective_date` DATE COMMENT '生效日期',
    `document_number` VARCHAR(50) COMMENT '文号',
    `attachment_url` VARCHAR(255) COMMENT '附件URL',
    `is_important` BOOLEAN DEFAULT FALSE COMMENT '是否重要公告',
    `status` ENUM('draft', 'published', 'expired') DEFAULT 'draft' COMMENT '状态',
    `views` INT UNSIGNED DEFAULT 0 COMMENT '浏览量',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX `idx_issue_date` (`issue_date`),
    INDEX `idx_status` (`status`),
    INDEX `idx_issuer` (`issuer`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='政策公告表';

-- 环境监测表
CREATE TABLE IF NOT EXISTS `environment_monitoring` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '记录ID',
    `location` VARCHAR(255) NOT NULL COMMENT '监测位置',
    `temperature` DECIMAL(5,2) COMMENT '温度(℃)',
    `humidity` DECIMAL(5,2) COMMENT '湿度(%)',
    `air_quality` VARCHAR(50) COMMENT '空气质量',
    `ammonia_concentration` DECIMAL(6,3) COMMENT '氨气浓度(ppm)',
    `carbon_dioxide` DECIMAL(6,2) COMMENT '二氧化碳浓度(ppm)',
    `noise_level` DECIMAL(5,2) COMMENT '噪音(dB)',
    `light_intensity` DECIMAL(7,2) COMMENT '光照强度(lux)',
    `monitoring_date` DATETIME NOT NULL COMMENT '监测时间',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX `idx_location_date` (`location`, `monitoring_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='环境监测表';

SET FOREIGN_KEY_CHECKS = 1;

-- ======================================
-- 插入测试数据
-- ======================================

-- 插入商品分类
INSERT IGNORE INTO `product_categories` (`name`, `parent_id`, `level`, `sort_order`) VALUES
('牛肉制品', 0, 1, 1),
('新鲜牛肉', 1, 2, 1),
('牛肉干', 1, 2, 2),
('牛肉罐头', 1, 2, 3);

-- 插入测试牛只数据
INSERT IGNORE INTO `cattle` (`ear_tag`, `name`, `breed`, `gender`, `birth_date`, `color`, `weight`, `farm_location`, `owner_id`) VALUES
('XL001', '小花', '西门塔尔牛', 'female', '2022-03-15', '黄白花', 450.50, '锡林浩特市第一牧场', 1),
('XL002', '壮壮', '安格斯牛', 'male', '2021-08-20', '黑色', 580.75, '锡林浩特市第一牧场', 1),
('XL003', '美美', '夏洛莱牛', 'female', '2022-05-10', '白色', 420.30, '东乌旗牧场A', 1);

SELECT '数据库扩展表结构创建完成！' AS message;