CrossBorder_OS/backend/sql/fix_data.sql

-- =============================================
-- Fix Database Encoding Issues
-- Fixes garbled Chinese characters caused by encoding issues during data insertion
-- =============================================

USE oms;

-- Fix sys_user table
UPDATE sys_user SET name = '系统管理员', role_label = '超级管理员', workspace = '全栈' WHERE username = 'admin';

-- Fix sys_role table
UPDATE sys_role SET name = 'ADMIN', description = '超级管理员' WHERE name = 'ADMIN';
UPDATE sys_role SET name = 'MANAGER', description = '运营经理' WHERE name = 'MANAGER';
UPDATE sys_role SET name = 'OPERATOR', description = '运营专员' WHERE name = 'OPERATOR';
UPDATE sys_role SET name = 'PROCUREMENT', description = '采购员' WHERE name = 'PROCUREMENT';
UPDATE sys_role SET name = 'WAREHOUSE', description = '仓库管理员' WHERE name = 'WAREHOUSE';
UPDATE sys_role SET name = 'FINANCE', description = '财务' WHERE name = 'FINANCE';
UPDATE sys_role SET name = 'CUSTOMER_SERVICE', description = '客服' WHERE name = 'CUSTOMER_SERVICE';

-- =============================================
-- Insert Sample Data for Testing
-- =============================================

-- Insert sample channels
INSERT INTO channel (channel_code, channel_name, shop_name, app_key, app_secret, token_status, sync_enabled, tenant_id) VALUES
('SHOPIFY_US', 'Shopify US', 'US Official Store', 'shopify_us_key', 'shopify_us_secret', 'VALID', true, 'DEFAULT'),
('SHOPIFY_JP', 'Shopify JP', 'Japan Official Store', 'shopify_jp_key', 'shopify_jp_secret', 'VALID', true, 'DEFAULT'),
('TIKTOK_UK', 'TikTok UK', 'UK Store', 'tiktok_uk_key', 'tiktok_uk_secret', 'VALID', true, 'DEFAULT'),
('AMAZON_US', 'Amazon US', 'US Marketplace', 'amazon_us_key', 'amazon_us_secret', 'VALID', true, 'DEFAULT')
ON DUPLICATE KEY UPDATE channel_name = VALUES(channel_name);

-- Insert sample warehouses
INSERT INTO warehouse (name, type, address, contact, phone, status, manager, tenant_id) VALUES
('深圳南山仓', '国内仓', '深圳市南山区科技园南路88号', '张经理', '13800138001', 'ACTIVE', '张三', 'DEFAULT'),
('义乌商贸仓', '国内仓', '义乌市稠城街道商贸城', '李经理', '13800138002', 'ACTIVE', '李四', 'DEFAULT'),
('洛杉矶海外仓', '海外仓', 'Los Angeles, CA 90001, USA', 'John Smith', '+1-555-0101', 'ACTIVE', 'John', 'DEFAULT')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- Insert sample products
INSERT INTO product (spu, title, subtitle, category_id, brand, tags, description, specs, channel_status, status, owner, sku_count, image, tenant_id) VALUES
('SPU-LUGG-001', 'TravelFlex Expandable Carry-On 20寸行李箱', '轻便耐用的ABS材质万向轮行李箱', 1, 'TravelFlex', '["行李箱","旅行","ABS"]', '高品质ABS材质，360度万向轮，TSA密码锁，20寸可扩展设计', '[]', 'ACTIVE', 'LISTED', '王运营', 3, 'https://example.com/images/lugg-001.jpg', 'DEFAULT'),
('SPU-TAG-001', 'Travel Tag Set 旅行标签牌套装', '防水耐磨的行李牌套装', 2, 'TravelEssentials', '["旅行配件","行李牌"]', '硅胶材质，防水耐磨，多色可选', '[]', 'ACTIVE', 'LISTED', '王运营', 2, 'https://example.com/images/tag-001.jpg', 'DEFAULT'),
('SPU-CUSH-001', 'Memory Foam Travel Neck Pillow 记忆棉护颈枕', 'U型设计记忆棉旅行护颈枕', 3, 'ComfortTravel', '["旅行枕","记忆棉"]', '高密度记忆棉，USB充电护眼罩，耳塞套装', '[]', 'INACTIVE', 'DRAFT', '张运营', 1, 'https://example.com/images/pillow-001.jpg', 'DEFAULT')
ON DUPLICATE KEY UPDATE title = VALUES(title);

-- Insert sample product SKUs
INSERT INTO product_sku (product_id, sku, barcode, spec_combo, cost_price, suggest_price, weight, length, width, height, tenant_id) VALUES
(1, 'SKU-LUGG-20-BLK', 'BC001001', '{"颜色":"黑色","尺寸":"20寸"}', 45.00, 89.00, 3500, 55, 35, 25, 'DEFAULT'),
(1, 'SKU-LUGG-20-SLV', 'BC001002', '{"颜色":"银色","尺寸":"20寸"}', 45.00, 89.00, 3500, 55, 35, 25, 'DEFAULT'),
(1, 'SKU-LUGG-24-BLK', 'BC001003', '{"颜色":"黑色","尺寸":"24寸"}', 55.00, 109.00, 4200, 65, 42, 28, 'DEFAULT'),
(2, 'SKU-TAG-SET-GRY', 'BC002001', '{"颜色":"灰色"}', 8.00, 19.50, 200, 15, 10, 2, 'DEFAULT'),
(2, 'SKU-TAG-SET-BLU', 'BC002002', '{"颜色":"蓝色"}', 8.00, 19.50, 200, 15, 10, 2, 'DEFAULT'),
(3, 'SKU-PILLOW-GRY', 'BC003001', '{"颜色":"灰色"}', 25.00, 49.00, 300, 28, 28, 12, 'DEFAULT')
ON DUPLICATE KEY UPDATE sku = VALUES(sku);

-- Insert sample orders
INSERT INTO orders (order_no, channel_order_no, channel_id, order_status, shipping_status, payment_status, refund_status, exception_tag, priority, buyer, buyer_id, buyer_email, buyer_phone, buyer_country, buyer_level, buyer_order_count, receiver_name, receiver_phone, receiver_country, receiver_state, receiver_city, receiver_postal_code, receiver_address, currency, exchange_rate, payment_method, order_amount, tax_amount, shipping_fee, actual_paid, warehouse_id, warehouse_location, item_count, tenant_id, created_at) VALUES
('OMS-20260421-001', 'CH-20260421-001', 1, 'CREATED', 'UNSHIPPED', 'UNPAID', 'NONE', NULL, 'NORMAL', 'Olivia Zhang', 'buyer-001', 'olivia.zhang@mail.com', '+1-213-555-4401', 'US', 'VIP', 12, 'Olivia Zhang', '+1-213-555-4401', 'US', 'California', 'Los Angeles', '90001', '1234 Main St, Apt 5B, Los Angeles, CA 90001', 'USD', 1.0, 'PayPal', 89.00, 7.12, 5.00, 101.12, 3, '洛杉矶海外仓', 1, 'DEFAULT', NOW()),
('OMS-20260421-002', 'CH-20260421-002', 2, 'PAID', 'UNSHIPPED', 'PAID', 'NONE', NULL, 'NORMAL', 'Noah Smith', 'buyer-002', 'noah.smith@mail.com', '+1-212-555-8802', 'UK', '金牌', 8, 'Noah Smith', '+1-212-555-8802', 'UK', 'London', 'London', 'SW1A1AA', '10 Downing Street, London, UK', 'USD', 0.79, 'Credit Card', 158.50, 12.68, 8.50, 179.68, 2, '深圳南山仓', 2, 'DEFAULT', NOW()),
('OMS-20260421-003', 'CH-20260421-003', 1, 'ALLOCATED', 'PROCESSING', 'PAID', 'NONE', '地址需复核', 'URGENT', 'Emma Wilson', 'buyer-003', 'emma.wilson@mail.com', '+1-310-555-3303', 'US', '普通', 3, 'Emma Wilson', '+1-310-555-3303', 'US', 'New York', 'New York City', '10001', '456 Broadway, New York, NY 10001', 'USD', 1.0, 'PayPal', 245.00, 19.60, 12.00, 276.60, 1, '义乌商贸仓', 3, 'DEFAULT', NOW())
ON DUPLICATE KEY UPDATE order_no = VALUES(order_no);

-- Insert order items
INSERT INTO order_item (order_id, product_id, sku_id, sku, product_title, product_image, category_id, category_name, specs, barcode, qty, price, cost_price, subtotal, weight, available, locked, gift_flag, tenant_id, created_at) VALUES
(1, 1, 1, 'SKU-LUGG-20-BLK', 'TravelFlex Expandable Carry-On 20寸行李箱', 'https://example.com/images/lugg-001.jpg', 1, '行李箱', '{"颜色":"黑色","尺寸":"20寸"}', 'BC001001', 1, 89.00, 45.00, 89.00, 3.5, 95, 5, false, 'DEFAULT', NOW()),
(2, 1, 2, 'SKU-LUGG-20-SLV', 'TravelFlex Expandable Carry-On 20寸行李箱', 'https://example.com/images/lugg-001.jpg', 1, '行李箱', '{"颜色":"银色","尺寸":"20寸"}', 'BC001002', 1, 89.00, 45.00, 89.00, 3.5, 75, 5, false, 'DEFAULT', NOW()),
(2, 2, 4, 'SKU-TAG-SET-GRY', 'Travel Tag Set 旅行标签牌套装', 'https://example.com/images/tag-001.jpg', 2, '旅行配件', '{"颜色":"灰色"}', 'BC002001', 2, 19.50, 8.00, 39.00, 0.2, 180, 20, false, 'DEFAULT', NOW()),
(3, 1, 3, 'SKU-LUGG-24-BLK', 'TravelFlex Expandable Carry-On 20寸行李箱', 'https://example.com/images/lugg-001.jpg', 1, '行李箱', '{"颜色":"黑色","尺寸":"24寸"}', 'BC001003', 3, 245.00, 55.00, 735.00, 4.2, 55, 5, false, 'DEFAULT', NOW())
ON DUPLICATE KEY UPDATE sku = VALUES(sku);

-- Insert sample after-sales
INSERT INTO after_sale (after_sale_no, order_no, order_id, buyer, type, amount, audit_status, refund_status, reason, audit_remark, created_at, tenant_id) VALUES
('AS-20260421-001', 'OMS-20260421-001', 1, 'Olivia Zhang', '退款', 89.00, 'PENDING', 'NONE', '商品破损', NULL, NOW(), 'DEFAULT'),
('AS-20260421-002', 'OMS-20260421-002', 2, 'Noah Smith', '退货退款', 39.00, 'APPROVED', 'REFUNDED', '尺寸不合适', '同意退款', NOW(), 'DEFAULT')
ON DUPLICATE KEY UPDATE after_sale_no = VALUES(after_sale_no);

-- Insert inventory data
INSERT INTO inventory (sku_id, product_title, warehouse_id, available, locked, inbound, safe_stock, tenant_id) VALUES
(1, 'TravelFlex Expandable Carry-On 20寸行李箱', 3, 95, 5, 20, 10, 'DEFAULT'),
(2, 'TravelFlex Expandable Carry-On 20寸行李箱', 3, 75, 5, 15, 10, 'DEFAULT'),
(3, 'TravelFlex Expandable Carry-On 20寸行李箱', 2, 55, 5, 10, 10, 'DEFAULT'),
(4, 'Travel Tag Set 旅行标签牌套装', 1, 180, 20, 50, 20, 'DEFAULT'),
(5, 'Travel Tag Set 旅行标签牌套装', 1, 130, 20, 40, 20, 'DEFAULT'),
(6, 'Memory Foam Travel Neck Pillow 记忆棉护颈枕', 1, 45, 5, 20, 10, 'DEFAULT')
ON DUPLICATE KEY UPDATE available = VALUES(available);

SELECT 'Data fix and sample data insertion completed!' AS status;