-- ======================================
-- 锡林郭勒盟智慧养殖产业平台 - 数据库初始化脚本
-- ======================================

SET FOREIGN_KEY_CHECKS = 0;

-- ======================================
-- 1. 用户权限相关表
-- ======================================

-- 用户表
CREATE TABLE IF NOT EXISTS `users` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
    `username` VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名（用于登录）',
    `email` VARCHAR(100) UNIQUE COMMENT '邮箱（用于通知和找回密码）',
    `phone` VARCHAR(20) UNIQUE COMMENT '手机号（实名认证用）',
    `password_hash` VARCHAR(255) NOT NULL COMMENT '密码哈希值（BCrypt加密）',
    `real_name` VARCHAR(50) COMMENT '真实姓名（需与身份证一致）',
    `avatar_url` VARCHAR(255) COMMENT '头像URL（OSS存储路径）',
    `user_type` ENUM('farmer', 'banker', 'insurer', 'government', 'trader', 'admin') NOT NULL COMMENT '用户类型：牧民/银行职员/保险员/政府人员/交易员/管理员',
    `status` TINYINT DEFAULT 1 COMMENT '状态: 1-正常, 0-禁用（禁用用户无法登录）',
    `last_login` TIMESTAMP NULL COMMENT '最后登录时间（用于活跃度分析）',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间（不可修改）',
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间（自动维护）',
    INDEX `idx_username` (`username`),
    INDEX `idx_email` (`email`),
    INDEX `idx_phone` (`phone`),
    INDEX `idx_user_type` (`user_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- 角色表
CREATE TABLE IF NOT EXISTS `roles` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '角色ID',
    `name` VARCHAR(50) NOT NULL UNIQUE COMMENT '角色名称',
    `description` TEXT COMMENT '角色描述',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色表';

-- 用户角色关联表
CREATE TABLE IF NOT EXISTS `user_roles` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
    `role_id` INT UNSIGNED NOT NULL COMMENT '角色ID',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE CASCADE,
    UNIQUE KEY `uk_user_role` (`user_id`, `role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户角色关联表';

-- 权限表
CREATE TABLE IF NOT EXISTS `permissions` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '权限ID',
    `name` VARCHAR(100) NOT NULL UNIQUE COMMENT '权限名称',
    `description` TEXT COMMENT '权限描述',
    `module` VARCHAR(50) COMMENT '所属模块',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='权限表';

-- 角色权限关联表
CREATE TABLE IF NOT EXISTS `role_permissions` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    `role_id` INT UNSIGNED NOT NULL COMMENT '角色ID',
    `permission_id` INT UNSIGNED NOT NULL COMMENT '权限ID',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`permission_id`) REFERENCES `permissions`(`id`) ON DELETE CASCADE,
    UNIQUE KEY `uk_role_permission` (`role_id`, `permission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色权限关联表';

-- ======================================
-- 2. 牛只档案相关表
-- ======================================

-- 牛只档案表
CREATE TABLE IF NOT EXISTS `cattle` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '牛只ID',
    `ear_tag` VARCHAR(50) NOT NULL UNIQUE COMMENT '耳标号',
    `name` VARCHAR(50) COMMENT '名称',
    `breed` VARCHAR(50) COMMENT '品种',
    `gender` ENUM('male', 'female') COMMENT '性别',
    `birth_date` DATE COMMENT '出生日期',
    `color` VARCHAR(30) COMMENT '毛色',
    `weight` DECIMAL(5,2) COMMENT '体重(kg)',
    `health_status` ENUM('healthy', 'sick', 'quarantine', 'dead') DEFAULT 'healthy' COMMENT '健康状况',
    `owner_id` BIGINT UNSIGNED COMMENT '所有者ID（牧民）',
    `farm_location` VARCHAR(255) COMMENT '牧场位置',
    `status` ENUM('active', 'sold', 'dead', 'quarantine') DEFAULT 'active' COMMENT '状态',
    `image_url` VARCHAR(255) COMMENT '图片URL',
    `qr_code_url` VARCHAR(255) COMMENT '二维码URL',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    FOREIGN KEY (`owner_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_ear_tag` (`ear_tag`),
    INDEX `idx_owner` (`owner_id`),
    INDEX `idx_breed` (`breed`),
    INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='牛只档案表';

-- 饲养记录表
CREATE TABLE IF NOT EXISTS `feeding_records` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '记录ID',
    `cattle_id` BIGINT UNSIGNED NOT NULL COMMENT '牛只ID',
    `record_type` ENUM('feed', 'vaccine', 'treatment', 'checkup') NOT NULL COMMENT '记录类型: 饲料, 疫苗, 治疗, 检查',
    `feed_type` VARCHAR(100) COMMENT '饲料类型',
    `feed_amount` DECIMAL(6,2) COMMENT '饲料量(kg)',
    `vaccine_name` VARCHAR(100) COMMENT '疫苗名称',
    `treatment_desc` TEXT COMMENT '治疗描述',
    `medicine_name` VARCHAR(100) COMMENT '药品名称',
    `dosage` VARCHAR(100) COMMENT '用量',
    `veterinarian` VARCHAR(50) COMMENT '兽医',
    `cost` DECIMAL(10,2) COMMENT '费用',
    `record_date` DATE NOT NULL COMMENT '记录日期',
    `notes` TEXT COMMENT '备注',
    `operator_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 (`cattle_id`) REFERENCES `cattle`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`operator_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_cattle_date` (`cattle_id`, `record_date`),
    INDEX `idx_record_type` (`record_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='饲养记录表';

-- 繁殖记录表
CREATE TABLE IF NOT EXISTS `breeding_records` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '记录ID',
    `cattle_id` BIGINT UNSIGNED NOT NULL COMMENT '母牛ID',
    `breeding_method` ENUM('natural', 'artificial') NOT NULL COMMENT '配种方式',
    `breeding_date` DATE NOT NULL COMMENT '配种日期',
    `breeding_male_id` BIGINT UNSIGNED COMMENT '公牛ID',
    `semen_code` VARCHAR(50) COMMENT '冻精编号',
    `expected_delivery_date` DATE COMMENT '预产期',
    `actual_delivery_date` DATE COMMENT '实际产犊日期',
    `calf_count` TINYINT DEFAULT 1 COMMENT '产犊数',
    `calf_ids` JSON COMMENT '犊牛IDs',
    `breeding_result` ENUM('success', 'failed', 'pending') DEFAULT 'pending' COMMENT '配种结果',
    `notes` TEXT COMMENT '备注',
    `operator_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 (`cattle_id`) REFERENCES `cattle`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`breeding_male_id`) REFERENCES `cattle`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`operator_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_cattle_date` (`cattle_id`, `breeding_date`),
    INDEX `idx_result` (`breeding_result`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='繁殖记录表';

-- ======================================
-- 3. 金融业务相关表
-- ======================================

-- 贷款申请表
CREATE TABLE IF NOT EXISTS `loan_applications` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '贷款申请ID',
    `applicant_id` BIGINT UNSIGNED NOT NULL COMMENT '申请人ID',
    `loan_type` ENUM('cattle', 'farm', 'equipment', 'operating') NOT NULL COMMENT '贷款类型',
    `cattle_ids` JSON COMMENT '质押牛只IDs',
    `loan_amount` DECIMAL(15,2) NOT NULL COMMENT '贷款金额',
    `currency` VARCHAR(10) DEFAULT 'CNY' COMMENT '货币',
    `interest_rate` DECIMAL(5,4) COMMENT '利率',
    `term_months` INT COMMENT '期限(月)',
    `purpose` TEXT COMMENT '用途',
    `repayment_method` ENUM('equal_principal', 'equal_payment', 'bullet') COMMENT '还款方式',
    `guarantee_type` ENUM('cattle_pledge', 'guarantor', 'insurance', 'credit') COMMENT '担保方式',
    `status` ENUM('submitted', 'under_review', 'approved', 'rejected', 'disbursed', 'completed', 'overdue') DEFAULT 'submitted' COMMENT '状态',
    `reviewer_id` BIGINT UNSIGNED COMMENT '审核人ID',
    `review_notes` TEXT COMMENT '审核备注',
    `approved_amount` DECIMAL(15,2) COMMENT '批准金额',
    `approved_date` TIMESTAMP NULL COMMENT '批准日期',
    `disbursement_date` TIMESTAMP NULL COMMENT '放款日期',
    `repayment_schedule` JSON COMMENT '还款计划',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    FOREIGN KEY (`applicant_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`reviewer_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_applicant` (`applicant_id`),
    INDEX `idx_status` (`status`),
    INDEX `idx_type` (`loan_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='贷款申请表';

-- 保险申请表
CREATE TABLE IF NOT EXISTS `insurance_applications` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '保险申请ID',
    `applicant_id` BIGINT UNSIGNED NOT NULL COMMENT '申请人ID',
    `insurance_type` ENUM('cattle_death', 'cattle_health', 'cattle_theft', 'property') NOT NULL COMMENT '保险类型',
    `cattle_ids` JSON COMMENT '保险牛只IDs',
    `policy_number` VARCHAR(50) UNIQUE COMMENT '保单号',
    `insured_amount` DECIMAL(15,2) COMMENT '保险金额',
    `premium` DECIMAL(12,2) COMMENT '保费',
    `currency` VARCHAR(10) DEFAULT 'CNY' COMMENT '货币',
    `start_date` DATE COMMENT '起保日期',
    `end_date` DATE COMMENT '终保日期',
    `status` ENUM('applied', 'underwriting', 'issued', 'active', 'expired', 'cancelled', 'claiming', 'settled') DEFAULT 'applied' COMMENT '状态',
    `underwriter_id` BIGINT UNSIGNED COMMENT '核保人ID',
    `underwriting_notes` TEXT COMMENT '核保备注',
    `policy_file_url` VARCHAR(255) COMMENT '保单文件URL',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    FOREIGN KEY (`applicant_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`underwriter_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_applicant` (`applicant_id`),
    INDEX `idx_policy_number` (`policy_number`),
    INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='保险申请表';

-- 理赔申请表
CREATE TABLE IF NOT EXISTS `claims` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '理赔申请ID',
    `insurance_id` BIGINT UNSIGNED NOT NULL COMMENT '保险ID',
    `applicant_id` BIGINT UNSIGNED NOT NULL COMMENT '申请人ID',
    `claim_amount` DECIMAL(12,2) NOT NULL COMMENT '理赔金额',
    `currency` VARCHAR(10) DEFAULT 'CNY' COMMENT '货币',
    `incident_date` DATE NOT NULL COMMENT '事故日期',
    `incident_type` ENUM('death', 'illness', 'accident', 'theft') NOT NULL COMMENT '事故类型',
    `description` TEXT COMMENT '事故描述',
    `evidence_files` JSON COMMENT '证据文件URL列表',
    `status` ENUM('submitted', 'under_review', 'approved', 'rejected', 'paid') DEFAULT 'submitted' COMMENT '状态',
    `reviewer_id` BIGINT UNSIGNED COMMENT '审核人ID',
    `review_notes` TEXT COMMENT '审核备注',
    `approved_amount` DECIMAL(12,2) COMMENT '批准金额',
    `paid_amount` DECIMAL(12,2) COMMENT '赔付金额',
    `submitted_at` TIMESTAMP NULL COMMENT '提交时间',
    `reviewed_at` TIMESTAMP NULL COMMENT '审核时间',
    `approved_at` TIMESTAMP NULL COMMENT '批准时间',
    `paid_at` TIMESTAMP NULL COMMENT '赔付时间',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    FOREIGN KEY (`insurance_id`) REFERENCES `insurance_applications`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`applicant_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`reviewer_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_insurance` (`insurance_id`),
    INDEX `idx_applicant` (`applicant_id`),
    INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='理赔申请表';

-- ======================================
-- 4. 交易管理相关表
-- ======================================

-- 交易记录表
CREATE TABLE IF NOT EXISTS `transactions` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '交易ID',
    `transaction_number` VARCHAR(50) UNIQUE NOT NULL COMMENT '交易编号',
    `transaction_type` ENUM('cattle_sale', 'feed_purchase', 'equipment_sale', 'service') NOT NULL COMMENT '交易类型',
    `buyer_id` BIGINT UNSIGNED NOT NULL COMMENT '买方ID',
    `seller_id` BIGINT UNSIGNED NOT NULL COMMENT '卖方ID',
    `cattle_ids` JSON COMMENT '交易牛只IDs',
    `product_name` VARCHAR(200) COMMENT '商品名称',
    `quantity` DECIMAL(10,2) COMMENT '数量',
    `unit` VARCHAR(20) COMMENT '单位',
    `unit_price` DECIMAL(12,2) COMMENT '单价',
    `total_amount` DECIMAL(15,2) NOT NULL COMMENT '总金额',
    `currency` VARCHAR(10) DEFAULT 'CNY' COMMENT '货币',
    `payment_method` ENUM('cash', 'bank_transfer', 'installment', 'check') COMMENT '付款方式',
    `payment_status` ENUM('pending', 'paid', 'partial', 'overdue') DEFAULT 'pending' COMMENT '付款状态',
    `delivery_method` ENUM('pickup', 'delivery', 'installation') COMMENT '交付方式',
    `delivery_address` TEXT COMMENT '交付地址',
    `delivery_date` TIMESTAMP NULL COMMENT '交付日期',
    `delivery_status` ENUM('pending', 'in_transit', 'delivered', 'cancelled') DEFAULT 'pending' COMMENT '交付状态',
    `status` ENUM('pending', 'confirmed', 'in_progress', 'completed', 'cancelled', 'refunded') DEFAULT 'pending' COMMENT '交易状态',
    `contract_id` BIGINT UNSIGNED COMMENT '合同ID',
    `notes` TEXT COMMENT '备注',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    FOREIGN KEY (`buyer_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
    FOREIGN KEY (`seller_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
    INDEX `idx_transaction_number` (`transaction_number`),
    INDEX `idx_buyer` (`buyer_id`),
    INDEX `idx_seller` (`seller_id`),
    INDEX `idx_status` (`status`),
    INDEX `idx_type` (`transaction_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='交易记录表';

-- 合同表
CREATE TABLE IF NOT EXISTS `contracts` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '合同ID',
    `contract_number` VARCHAR(50) UNIQUE NOT NULL COMMENT '合同编号',
    `contract_type` ENUM('cattle_sale', 'feed_supply', 'equipment_purchase', 'service') NOT NULL COMMENT '合同类型',
    `party_a_id` BIGINT UNSIGNED NOT NULL COMMENT '甲方ID',
    `party_b_id` BIGINT UNSIGNED NOT NULL COMMENT '乙方ID',
    `contract_amount` DECIMAL(15,2) NOT NULL COMMENT '合同金额',
    `currency` VARCHAR(10) DEFAULT 'CNY' COMMENT '货币',
    `signing_date` DATE COMMENT '签订日期',
    `effective_date` DATE COMMENT '生效日期',
    `expiry_date` DATE COMMENT '到期日期',
    `payment_terms` TEXT COMMENT '付款条款',
    `delivery_terms` TEXT COMMENT '交付条款',
    `status` ENUM('draft', 'active', 'completed', 'cancelled', 'expired') DEFAULT 'draft' COMMENT '合同状态',
    `contract_content` LONGTEXT COMMENT '合同内容',
    `contract_url` VARCHAR(255) COMMENT '合同文件URL',
    `digital_signature_a` TEXT COMMENT '甲方数字签名',
    `digital_signature_b` TEXT COMMENT '乙方数字签名',
    `witness_id` BIGINT UNSIGNED COMMENT '见证人ID',
    `notes` TEXT COMMENT '备注',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    FOREIGN KEY (`party_a_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
    FOREIGN KEY (`party_b_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
    FOREIGN KEY (`witness_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_contract_number` (`contract_number`),
    INDEX `idx_party_a` (`party_a_id`),
    INDEX `idx_party_b` (`party_b_id`),
    INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='合同表';

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

-- 牧场注册表
CREATE TABLE IF NOT EXISTS `farms` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '牧场ID',
    `farm_name` VARCHAR(100) NOT NULL COMMENT '牧场名称',
    `registration_number` VARCHAR(50) UNIQUE NOT NULL COMMENT '注册编号',
    `owner_id` BIGINT UNSIGNED NOT NULL COMMENT '所有者ID',
    `legal_representative` VARCHAR(50) COMMENT '法定代表人',
    `contact_phone` VARCHAR(20) COMMENT '联系电话',
    `contact_email` VARCHAR(100) COMMENT '联系邮箱',
    `address` TEXT COMMENT '详细地址',
    `region` VARCHAR(50) COMMENT '所属区域',
    `coordinates` POINT COMMENT '经纬度坐标',
    `farm_area` DECIMAL(10,2) COMMENT '牧场面积(亩)',
    `cattle_capacity` INT COMMENT '牲畜容量',
    `current_cattle_count` INT DEFAULT 0 COMMENT '当前牲畜数量',
    `registration_date` DATE COMMENT '注册日期',
    `license_number` VARCHAR(50) COMMENT '许可证号',
    `license_expiry_date` DATE COMMENT '许可证到期日期',
    `status` ENUM('active', 'inactive', 'suspended', 'cancelled') DEFAULT 'active' COMMENT '状态',
    `compliance_status` ENUM('compliant', 'warning', 'violation', 'pending') DEFAULT 'pending' COMMENT '合规状态',
    `last_inspection_date` DATE COMMENT '最后检查日期',
    `next_inspection_date` DATE COMMENT '下次检查日期',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    FOREIGN KEY (`owner_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
    INDEX `idx_registration_number` (`registration_number`),
    INDEX `idx_owner` (`owner_id`),
    INDEX `idx_region` (`region`),
    INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='牧场注册表';

-- 政府检查记录表
CREATE TABLE IF NOT EXISTS `government_inspections` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '检查记录ID',
    `farm_id` BIGINT UNSIGNED NOT NULL COMMENT '牧场ID',
    `inspector_id` BIGINT UNSIGNED NOT NULL COMMENT '检查员ID',
    `inspection_type` ENUM('routine', 'follow_up', 'complaint', 'emergency') NOT NULL COMMENT '检查类型',
    `inspection_date` DATE NOT NULL COMMENT '检查日期',
    `inspection_scope` JSON COMMENT '检查范围',
    `checklist` JSON COMMENT '检查清单',
    `score` DECIMAL(5,2) COMMENT '检查评分',
    `result` ENUM('passed', 'conditional_pass', 'failed') COMMENT '检查结果',
    `violations` JSON COMMENT '违规事项',
    `improvements` JSON COMMENT '改进建议',
    `corrective_actions` JSON COMMENT '整改要求',
    `next_inspection_date` DATE COMMENT '下次检查日期',
    `report_file_url` VARCHAR(255) COMMENT '检查报告文件URL',
    `photos` JSON COMMENT '检查照片URLs',
    `inspector_notes` TEXT COMMENT '检查员备注',
    `farm_response` TEXT COMMENT '牧场回应',
    `status` ENUM('completed', 'pending_correction', 'follow_up_required') DEFAULT 'completed' COMMENT '状态',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    FOREIGN KEY (`farm_id`) REFERENCES `farms`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`inspector_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
    INDEX `idx_farm_date` (`farm_id`, `inspection_date`),
    INDEX `idx_inspector` (`inspector_id`),
    INDEX `idx_result` (`result`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='政府检查记录表';

-- 政策法规表
CREATE TABLE IF NOT EXISTS `policies` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '政策ID',
    `title` VARCHAR(200) NOT NULL COMMENT '政策标题',
    `policy_number` VARCHAR(50) UNIQUE COMMENT '政策编号',
    `category` ENUM('regulation', 'support_policy', 'subsidy', 'standard', 'guideline') NOT NULL COMMENT '政策类别',
    `authority` VARCHAR(100) COMMENT '发布机关',
    `content_summary` TEXT COMMENT '内容摘要',
    `content_detail` LONGTEXT COMMENT '详细内容',
    `document_url` VARCHAR(255) COMMENT '文档URL',
    `publish_date` DATE COMMENT '发布日期',
    `effective_date` DATE COMMENT '生效日期',
    `expiry_date` DATE COMMENT '失效日期',
    `status` ENUM('draft', 'active', 'expired', 'repealed') DEFAULT 'draft' COMMENT '状态',
    `target_audience` JSON COMMENT '适用对象',
    `keywords` VARCHAR(500) COMMENT '关键词',
    `created_by` BIGINT UNSIGNED COMMENT '创建人ID',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_policy_number` (`policy_number`),
    INDEX `idx_category` (`category`),
    INDEX `idx_status` (`status`),
    FULLTEXT `idx_keywords` (`title`, `content_summary`, `keywords`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='政策法规表';

-- ======================================
-- 6. 商城相关表
-- ======================================

-- 商品表
CREATE TABLE IF NOT EXISTS `products` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '商品ID',
    `name` VARCHAR(200) NOT NULL COMMENT '商品名称',
    `sku` VARCHAR(50) UNIQUE COMMENT '商品SKU',
    `category` ENUM('beef', 'dairy', 'snacks', 'processed', 'equipment', 'feed', 'other') NOT NULL COMMENT '商品类别',
    `subcategory` VARCHAR(50) COMMENT '子类别',
    `description` TEXT COMMENT '商品描述',
    `price` DECIMAL(10,2) NOT NULL COMMENT '价格',
    `original_price` DECIMAL(10,2) COMMENT '原价',
    `cost_price` DECIMAL(10,2) COMMENT '成本价',
    `currency` VARCHAR(10) DEFAULT 'CNY' COMMENT '货币',
    `stock` INT DEFAULT 0 COMMENT '库存数量',
    `sales_count` INT DEFAULT 0 COMMENT '销售数量',
    `weight` DECIMAL(8,3) COMMENT '重量(kg)',
    `dimensions` VARCHAR(100) COMMENT '尺寸',
    `shelf_life` INT COMMENT '保质期(天)',
    `storage_conditions` VARCHAR(200) COMMENT '储存条件',
    `origin` VARCHAR(100) COMMENT '产地',
    `brand` VARCHAR(100) COMMENT '品牌',
    `specifications` JSON COMMENT '规格参数',
    `images` JSON COMMENT '商品图片URLs',
    `video_url` VARCHAR(255) COMMENT '视频URL',
    `seller_id` BIGINT UNSIGNED NOT NULL COMMENT '卖家ID',
    `status` ENUM('active', 'inactive', 'out_of_stock', 'pending_review', 'rejected') DEFAULT 'pending_review' COMMENT '状态',
    `featured` TINYINT DEFAULT 0 COMMENT '是否推荐',
    `rating` DECIMAL(3,2) DEFAULT 0 COMMENT '评分',
    `review_count` INT DEFAULT 0 COMMENT '评价数量',
    `seo_title` VARCHAR(200) COMMENT 'SEO标题',
    `seo_description` TEXT COMMENT 'SEO描述',
    `seo_keywords` VARCHAR(500) COMMENT 'SEO关键词',
    `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 RESTRICT,
    INDEX `idx_sku` (`sku`),
    INDEX `idx_category` (`category`),
    INDEX `idx_seller` (`seller_id`),
    INDEX `idx_status` (`status`),
    INDEX `idx_featured` (`featured`),
    FULLTEXT `idx_search` (`name`, `description`, `seo_keywords`)
) 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) UNIQUE NOT NULL COMMENT '订单号',
    `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
    `order_type` ENUM('normal', 'group_buy', 'presale', 'custom') DEFAULT 'normal' COMMENT '订单类型',
    `total_amount` DECIMAL(12,2) NOT NULL COMMENT '商品总金额',
    `discount_amount` DECIMAL(12,2) DEFAULT 0 COMMENT '优惠金额',
    `shipping_fee` DECIMAL(10,2) DEFAULT 0 COMMENT '运费',
    `tax_amount` DECIMAL(10,2) DEFAULT 0 COMMENT '税费',
    `final_amount` DECIMAL(12,2) NOT NULL COMMENT '最终金额',
    `currency` VARCHAR(10) DEFAULT 'CNY' COMMENT '货币',
    `payment_method` ENUM('wechat_pay', 'alipay', 'bank_transfer', 'cash_on_delivery') COMMENT '支付方式',
    `payment_status` ENUM('pending', 'paid', 'partial', 'refunded', 'failed') DEFAULT 'pending' COMMENT '支付状态',
    `shipping_method` ENUM('express', 'self_pickup', 'same_city') COMMENT '配送方式',
    `shipping_address` TEXT COMMENT '收货地址',
    `shipping_name` VARCHAR(50) COMMENT '收货人姓名',
    `shipping_phone` VARCHAR(20) COMMENT '收货人电话',
    `tracking_number` VARCHAR(100) COMMENT '快递单号',
    `status` ENUM('pending_payment', 'paid', 'processing', 'shipping', 'delivered', 'completed', 'cancelled', 'refunded') DEFAULT 'pending_payment' COMMENT '订单状态',
    `coupon_code` VARCHAR(50) COMMENT '优惠券代码',
    `notes` TEXT COMMENT '订单备注',
    `order_date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
    `payment_date` TIMESTAMP NULL COMMENT '支付时间',
    `shipping_date` TIMESTAMP NULL COMMENT '发货时间',
    `delivery_date` TIMESTAMP NULL COMMENT '收货时间',
    `completion_date` 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 RESTRICT,
    INDEX `idx_order_number` (`order_number`),
    INDEX `idx_user` (`user_id`),
    INDEX `idx_status` (`status`),
    INDEX `idx_payment_status` (`payment_status`),
    INDEX `idx_order_date` (`order_date`)
) 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',
    `product_name` VARCHAR(200) NOT NULL COMMENT '商品名称快照',
    `product_sku` VARCHAR(50) COMMENT '商品SKU快照',
    `product_image` VARCHAR(255) COMMENT '商品图片快照',
    `unit_price` DECIMAL(10,2) NOT NULL COMMENT '单价',
    `quantity` INT NOT NULL COMMENT '数量',
    `total_price` DECIMAL(12,2) NOT NULL COMMENT '小计',
    `currency` VARCHAR(10) DEFAULT 'CNY' COMMENT '货币',
    `specifications` JSON COMMENT '规格参数快照',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE RESTRICT,
    INDEX `idx_order` (`order_id`),
    INDEX `idx_product` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单商品表';

-- 商品评价表
CREATE TABLE IF NOT EXISTS `product_reviews` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '评价ID',
    `product_id` BIGINT UNSIGNED NOT NULL COMMENT '商品ID',
    `order_id` BIGINT UNSIGNED NOT NULL COMMENT '订单ID',
    `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
    `rating` TINYINT NOT NULL COMMENT '评分(1-5)',
    `content` TEXT COMMENT '评价内容',
    `images` JSON COMMENT '评价图片URLs',
    `helpful_count` INT DEFAULT 0 COMMENT '有用数',
    `reply_content` TEXT COMMENT '商家回复',
    `reply_date` TIMESTAMP NULL COMMENT '回复时间',
    `status` ENUM('active', 'hidden', 'deleted') DEFAULT 'active' COMMENT '状态',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_product` (`product_id`),
    INDEX `idx_user` (`user_id`),
    INDEX `idx_rating` (`rating`),
    INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品评价表';

-- ======================================
-- 7. 质量追溯相关表
-- ======================================

-- 产品追溯表
CREATE TABLE IF NOT EXISTS `product_traceability` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '追溯ID',
    `product_id` BIGINT UNSIGNED COMMENT '商品ID',
    `batch_number` VARCHAR(50) UNIQUE NOT NULL COMMENT '批次号',
    `cattle_ids` JSON COMMENT '源头牛只IDs',
    `farm_id` BIGINT UNSIGNED COMMENT '来源牧场ID',
    `slaughter_date` DATE COMMENT '屠宰日期',
    `slaughterhouse` VARCHAR(100) COMMENT '屠宰场',
    `processing_date` DATE COMMENT '加工日期',
    `processor` VARCHAR(100) COMMENT '加工商',
    `packaging_date` DATE COMMENT '包装日期',
    `quality_certificates` JSON COMMENT '质量证书URLs',
    `inspection_reports` JSON COMMENT '检验报告URLs',
    `transportation_records` JSON COMMENT '运输记录',
    `storage_conditions` JSON COMMENT '储存条件记录',
    `chain_of_custody` JSON COMMENT '监管链记录',
    `qr_code` VARCHAR(255) COMMENT '二维码内容',
    `blockchain_hash` VARCHAR(255) COMMENT '区块链哈希值',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`farm_id`) REFERENCES `farms`(`id`) ON DELETE SET NULL,
    INDEX `idx_batch_number` (`batch_number`),
    INDEX `idx_product` (`product_id`),
    INDEX `idx_farm` (`farm_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='产品追溯表';

-- ======================================
-- 8. 系统日志相关表
-- ======================================

-- 操作日志表
CREATE TABLE IF NOT EXISTS `operation_logs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '日志ID',
    `user_id` BIGINT UNSIGNED COMMENT '操作用户ID',
    `operation_type` VARCHAR(50) NOT NULL COMMENT '操作类型',
    `operation_module` VARCHAR(50) NOT NULL COMMENT '操作模块',
    `operation_desc` TEXT COMMENT '操作描述',
    `request_method` VARCHAR(10) COMMENT '请求方法',
    `request_url` VARCHAR(500) COMMENT '请求URL',
    `request_params` JSON COMMENT '请求参数',
    `response_code` INT COMMENT '响应状态码',
    `response_message` TEXT COMMENT '响应消息',
    `ip_address` VARCHAR(45) COMMENT 'IP地址',
    `user_agent` TEXT COMMENT '用户代理',
    `execution_time` INT COMMENT '执行时间(毫秒)',
    `success` TINYINT DEFAULT 1 COMMENT '是否成功',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_user` (`user_id`),
    INDEX `idx_type` (`operation_type`),
    INDEX `idx_module` (`operation_module`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='操作日志表';

SET FOREIGN_KEY_CHECKS = 1;

-- ======================================
-- 插入初始数据
-- ======================================

-- 插入默认角色
INSERT IGNORE INTO `roles` (`name`, `description`) VALUES
('admin', '系统管理员'),
('farmer', '养殖户'),
('banker', '银行职员'),
('insurer', '保险员'),
('government', '政府监管人员'),
('trader', '交易员');

-- 插入默认权限
INSERT IGNORE INTO `permissions` (`name`, `description`, `module`) VALUES
('user_manage', '用户管理', 'user'),
('cattle_manage', '牛只管理', 'cattle'),
('loan_manage', '贷款管理', 'loan'),
('insurance_manage', '保险管理', 'insurance'),
('trade_manage', '交易管理', 'trade'),
('government_supervise', '政府监管', 'government'),
('data_view', '数据查看', 'data'),
('system_config', '系统配置', 'system');

-- 插入默认管理员用户 (密码: admin123)
INSERT IGNORE INTO `users` (`username`, `email`, `password_hash`, `real_name`, `user_type`, `status`) VALUES
('admin', 'admin@xlxumu.com', '$2b$10$8K1p/a0dFd2XeyGWm7S9me5qHEF1K/ZEGPmU0ISGwXc7hdsXkn8ZO', '系统管理员', 'admin', 1);

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