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

-- 清理现有数据（可选）
-- DELETE FROM user_roles;
-- DELETE FROM role_permissions;
-- DELETE FROM users WHERE id > 1;

-- ======================================
-- 1. 角色和权限初始化
-- ======================================

-- 插入角色数据
INSERT IGNORE INTO `roles` (`name`, `description`) VALUES
('admin', '系统管理员 - 拥有所有权限'),
('farmer', '养殖户 - 管理自己的牛只和交易'),
('banker', '银行职员 - 处理贷款申请'),
('insurer', '保险员 - 处理保险和理赔'),
('government_inspector', '政府检查员 - 进行合规检查'),
('government_admin', '政府管理员 - 查看监管数据'),
('trader', '交易员 - 处理交易业务'),
('merchant', '商户 - 管理商城商品');

-- 插入权限数据
INSERT IGNORE INTO `permissions` (`name`, `description`, `module`) VALUES
-- 用户管理权限
('user_view', '查看用户信息', 'user'),
('user_create', '创建用户', 'user'),
('user_edit', '编辑用户信息', 'user'),
('user_delete', '删除用户', 'user'),
('user_manage', '用户管理（包含所有用户操作）', 'user'),

-- 牛只管理权限
('cattle_view', '查看牛只信息', 'cattle'),
('cattle_create', '创建牛只档案', 'cattle'),
('cattle_edit', '编辑牛只信息', 'cattle'),
('cattle_delete', '删除牛只档案', 'cattle'),
('cattle_manage', '牛只管理（包含所有牛只操作）', 'cattle'),

-- 金融服务权限
('loan_view', '查看贷款信息', 'finance'),
('loan_create', '创建贷款申请', 'finance'),
('loan_review', '审核贷款申请', 'finance'),
('loan_manage', '贷款管理', 'finance'),
('insurance_view', '查看保险信息', 'finance'),
('insurance_create', '创建保险申请', 'finance'),
('insurance_review', '审核保险申请', 'finance'),
('insurance_manage', '保险管理', 'finance'),

-- 交易管理权限
('transaction_view', '查看交易信息', 'trading'),
('transaction_create', '创建交易记录', 'trading'),
('transaction_manage', '交易管理', 'trading'),
('contract_view', '查看合同信息', 'trading'),
('contract_create', '创建合同', 'trading'),
('contract_manage', '合同管理', 'trading'),

-- 政府监管权限
('government_supervision', '政府监管权限', 'government'),
('government_inspection', '政府检查权限', 'government'),
('government_statistics', '政府统计权限', 'government'),
('government_report', '政府报告权限', 'government'),
('quality_trace', '质量追溯权限', 'government'),
('policy_view', '查看政策法规', 'government'),
('policy_manage', '管理政策法规', 'government'),

-- 商城管理权限
('product_view', '查看商品信息', 'mall'),
('product_create', '创建商品', 'mall'),
('product_edit', '编辑商品信息', 'mall'),
('product_delete', '删除商品', 'mall'),
('product_manage', '商品管理', 'mall'),
('order_view', '查看订单信息', 'mall'),
('order_manage', '订单管理', 'mall'),
('mall_statistics', '商城统计', 'mall'),

-- 系统管理权限
('system_config', '系统配置', 'system'),
('data_view', '数据查看', 'system'),
('data_export', '数据导出', 'system'),
('log_view', '日志查看', 'system');

-- ======================================
-- 2. 角色权限分配
-- ======================================

-- 管理员角色 - 拥有所有权限
INSERT IGNORE INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id FROM `roles` r, `permissions` p WHERE r.name = 'admin';

-- 养殖户角色权限
INSERT IGNORE INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id FROM `roles` r, `permissions` p 
WHERE r.name = 'farmer' AND p.name IN (
  'cattle_view', 'cattle_create', 'cattle_edit', 'cattle_manage',
  'loan_view', 'loan_create', 'insurance_view', 'insurance_create',
  'transaction_view', 'transaction_create', 'contract_view',
  'product_view', 'order_view', 'data_view'
);

-- 银行职员角色权限
INSERT IGNORE INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id FROM `roles` r, `permissions` p 
WHERE r.name = 'banker' AND p.name IN (
  'loan_view', 'loan_review', 'loan_manage',
  'cattle_view', 'user_view', 'data_view'
);

-- 保险员角色权限
INSERT IGNORE INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id FROM `roles` r, `permissions` p 
WHERE r.name = 'insurer' AND p.name IN (
  'insurance_view', 'insurance_review', 'insurance_manage',
  'cattle_view', 'user_view', 'data_view'
);

-- 政府检查员角色权限
INSERT IGNORE INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id FROM `roles` r, `permissions` p 
WHERE r.name = 'government_inspector' AND p.name IN (
  'government_supervision', 'government_inspection', 'quality_trace',
  'cattle_view', 'user_view', 'policy_view', 'data_view'
);

-- 政府管理员角色权限
INSERT IGNORE INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id FROM `roles` r, `permissions` p 
WHERE r.name = 'government_admin' AND p.name IN (
  'government_supervision', 'government_statistics', 'government_report',
  'policy_view', 'policy_manage', 'quality_trace',
  'cattle_view', 'user_view', 'data_view', 'data_export'
);

-- 交易员角色权限
INSERT IGNORE INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id FROM `roles` r, `permissions` p 
WHERE r.name = 'trader' AND p.name IN (
  'transaction_view', 'transaction_create', 'transaction_manage',
  'contract_view', 'contract_create', 'contract_manage',
  'cattle_view', 'user_view', 'data_view'
);

-- 商户角色权限
INSERT IGNORE INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id FROM `roles` r, `permissions` p 
WHERE r.name = 'merchant' AND p.name IN (
  'product_view', 'product_create', 'product_edit', 'product_manage',
  'order_view', 'order_manage', 'mall_statistics',
  'data_view'
);

-- ======================================
-- 3. 测试用户数据
-- ======================================

-- 创建测试用户（密码都是：123456）
INSERT IGNORE INTO `users` (`username`, `email`, `phone`, `password_hash`, `real_name`, `user_type`, `status`) VALUES
('admin', 'admin@xlxumu.com', '13900000001', '$2b$10$N9qo8uLOickgx2ZMRZoMye1VrVCjhAhLOEI8aSqJZmL4Q9DQKqWV.', '系统管理员', 'admin', 1),
('farmer001', 'farmer001@example.com', '13900000002', '$2b$10$N9qo8uLOickgx2ZMRZoMye1VrVCjhAhLOEI8aSqJZmL4Q9DQKqWV.', '张三', 'farmer', 1),
('farmer002', 'farmer002@example.com', '13900000003', '$2b$10$N9qo8uLOickgx2ZMRZoMye1VrVCjhAhLOEI8aSqJZmL4Q9DQKqWV.', '李四', 'farmer', 1),
('banker001', 'banker001@example.com', '13900000004', '$2b$10$N9qo8uLOickgx2ZMRZoMye1VrVCjhAhLOEI8aSqJZmL4Q9DQKqWV.', '王五', 'banker', 1),
('insurer001', 'insurer001@example.com', '13900000005', '$2b$10$N9qo8uLOickgx2ZMRZoMye1VrVCjhAhLOEI8aSqJZmL4Q9DQKqWV.', '赵六', 'insurer', 1),
('inspector001', 'inspector001@example.com', '13900000006', '$2b$10$N9qo8uLOickgx2ZMRZoMye1VrVCjhAhLOEI8aSqJZmL4Q9DQKqWV.', '钱七', 'government', 1),
('trader001', 'trader001@example.com', '13900000007', '$2b$10$N9qo8uLOickgx2ZMRZoMye1VrVCjhAhLOEI8aSqJZmL4Q9DQKqWV.', '孙八', 'trader', 1),
('merchant001', 'merchant001@example.com', '13900000008', '$2b$10$N9qo8uLOickgx2ZMRZoMye1VrVCjhAhLOEI8aSqJZmL4Q9DQKqWV.', '周九', 'trader', 1);

-- 分配用户角色
INSERT IGNORE INTO `user_roles` (`user_id`, `role_id`)
SELECT u.id, r.id FROM `users` u, `roles` r 
WHERE u.username = 'admin' AND r.name = 'admin';

INSERT IGNORE INTO `user_roles` (`user_id`, `role_id`)
SELECT u.id, r.id FROM `users` u, `roles` r 
WHERE u.username = 'farmer001' AND r.name = 'farmer';

INSERT IGNORE INTO `user_roles` (`user_id`, `role_id`)
SELECT u.id, r.id FROM `users` u, `roles` r 
WHERE u.username = 'farmer002' AND r.name = 'farmer';

INSERT IGNORE INTO `user_roles` (`user_id`, `role_id`)
SELECT u.id, r.id FROM `users` u, `roles` r 
WHERE u.username = 'banker001' AND r.name = 'banker';

INSERT IGNORE INTO `user_roles` (`user_id`, `role_id`)
SELECT u.id, r.id FROM `users` u, `roles` r 
WHERE u.username = 'insurer001' AND r.name = 'insurer';

INSERT IGNORE INTO `user_roles` (`user_id`, `role_id`)
SELECT u.id, r.id FROM `users` u, `roles` r 
WHERE u.username = 'inspector001' AND r.name = 'government_inspector';

INSERT IGNORE INTO `user_roles` (`user_id`, `role_id`)
SELECT u.id, r.id FROM `users` u, `roles` r 
WHERE u.username = 'trader001' AND r.name = 'trader';

INSERT IGNORE INTO `user_roles` (`user_id`, `role_id`)
SELECT u.id, r.id FROM `users` u, `roles` r 
WHERE u.username = 'merchant001' AND r.name = 'merchant';

-- ======================================
-- 4. 示例牧场数据
-- ======================================

INSERT IGNORE INTO `farms` (`farm_name`, `registration_number`, `owner_id`, `legal_representative`, `contact_phone`, `contact_email`, `address`, `region`, `farm_area`, `cattle_capacity`, `current_cattle_count`, `registration_date`, `license_number`, `license_expiry_date`, `status`, `compliance_status`) VALUES
('锡林浩特市第一牧场', 'REG2024001', 2, '张三', '13900000002', 'farm001@example.com', '锡林浩特市郊区草原路123号', '锡林浩特市', 150.5, 300, 240, '2023-06-15', 'LIC2023001', '2025-06-15', 'active', 'compliant'),
('东乌旗生态牧场', 'REG2024002', 3, '李四', '13900000003', 'farm002@example.com', '东乌旗珠恩嘎达布其镇', '东乌旗', 200.8, 400, 320, '2023-08-20', 'LIC2023002', '2025-08-20', 'active', 'compliant'),
('西乌旗示范牧场', 'REG2024003', 2, '张三', '13900000002', 'farm003@example.com', '西乌旗巴拉嘎尔高勒镇', '西乌旗', 300.2, 500, 450, '2023-05-10', 'LIC2023003', '2025-05-10', 'active', 'compliant');

-- ======================================
-- 5. 示例牛只数据
-- ======================================

INSERT IGNORE INTO `cattle` (`ear_tag`, `name`, `breed`, `gender`, `birth_date`, `color`, `weight`, `health_status`, `owner_id`, `farm_location`, `status`) VALUES
('C001', '小黄', '西门塔尔牛', 'female', '2022-03-15', '黄色', 450.5, 'healthy', 2, '锡林浩特市第一牧场', 'active'),
('C002', '大力', '安格斯牛', 'male', '2021-11-20', '黑色', 620.8, 'healthy', 2, '锡林浩特市第一牧场', 'active'),
('C003', '花花', '夏洛莱牛', 'female', '2022-07-08', '白色', 380.2, 'healthy', 3, '东乌旗生态牧场', 'active'),
('C004', '壮壮', '利木赞牛', 'male', '2021-09-12', '金黄色', 580.0, 'healthy', 3, '东乌旗生态牧场', 'active'),
('C005', '美美', '西门塔尔牛', 'female', '2022-12-25', '棕色', 420.3, 'healthy', 2, '西乌旗示范牧场', 'active');

-- ======================================
-- 6. 示例商品数据
-- ======================================

INSERT IGNORE INTO `products` (`name`, `sku`, `category`, `description`, `price`, `original_price`, `stock`, `weight`, `origin`, `brand`, `seller_id`, `status`, `featured`, `rating`, `review_count`) VALUES
('优质牛肉礼盒装', 'BEEF001', 'beef', '来自锡林浩特优质牧场的新鲜牛肉，肉质鲜美，营养丰富', 268.00, 298.00, 45, 2.000, '锡林浩特市第一牧场', '草原优品', 2, 'active', 1, 4.8, 56),
('有机牛奶', 'DAIRY001', 'dairy', '纯天然有机牛奶，无添加剂，营养价值高', 35.00, 35.00, 120, 1.000, '东乌旗生态牧场', '草原乳业', 3, 'active', 1, 4.6, 32),
('草原牛肉干', 'SNACK001', 'snacks', '传统工艺制作的牛肉干，口感醇香，营养丰富', 68.00, 78.00, 88, 0.500, '西乌旗牧场', '草原食品', 8, 'active', 0, 4.9, 78),
('精选牛排', 'BEEF002', 'beef', '精选优质牛排，适合煎烤，肉质鲜嫩', 158.00, 168.00, 25, 1.500, '锡林浩特市第一牧场', '草原优品', 2, 'active', 1, 4.7, 43),
('酸奶', 'DAIRY002', 'dairy', '传统发酵工艺制作的酸奶，口感醇厚', 25.00, 25.00, 200, 0.500, '东乌旗生态牧场', '草原乳业', 3, 'active', 0, 4.5, 67);

SELECT '初始数据插入完成！' AS message;