cfc/tests/e2e/seed-data.sql

USE cfc;

-- ============================================================
-- E2E Test Seed Data
-- ============================================================

-- Disable FK checks temporarily for clean deletion
SET FOREIGN_KEY_CHECKS = 0;

-- ============================================================
-- DELETE from all relevant tables in FK dependency order
-- ============================================================

-- Delete child tables first (referencing other tables)
DELETE FROM points_log;
DELETE FROM task_reviews;
DELETE FROM media_records;
DELETE FROM task_plan_items;
DELETE FROM task_plan_instances;
DELETE FROM task_template_items;
DELETE FROM task_template_packages;
DELETE FROM guide_families;
DELETE FROM teacher_messages;
DELETE FROM growth_records;
DELETE FROM dan_assessment_results;
DELETE FROM dan_assessments;
DELETE FROM assessment_appointments;
DELETE FROM focus_sessions;
DELETE FROM game_records;
DELETE FROM child_badge;
DELETE FROM energy_log;
DELETE FROM energy_balance;
DELETE FROM streak_milestones;
DELETE FROM points_reset_log;
DELETE FROM reward_wishlists;
DELETE FROM wishes;
DELETE FROM rewards;
DELETE FROM reward_categories;
DELETE FROM tasks;
DELETE FROM invite_card;
DELETE FROM activity_registrations;
DELETE FROM guide_invite_codes;
DELETE FROM guide_commissions;
DELETE FROM guide_service_logs;
DELETE FROM guide_orders;
DELETE FROM guide_application_records;
DELETE FROM guide_applications;
DELETE FROM package_orders;
DELETE FROM assessment_orders;
DELETE FROM assessment_records;
DELETE FROM product_orders;
DELETE FROM product_skus;
DELETE FROM user_coupon;
DELETE FROM user_address;
DELETE FROM family_relationships;
DELETE FROM family_member_attributes;

-- Delete tables with FK to users/families
DELETE FROM family_members;
DELETE FROM guides;
DELETE FROM admins;
DELETE FROM users;
DELETE FROM families;

-- Delete content tables
DELETE FROM articles;
DELETE FROM activities;
DELETE FROM products;

-- Re-enable FK checks
SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- INSERT test family
-- ============================================================

INSERT IGNORE INTO families (id, name, invite_code, created_at, updated_at) VALUES
(2001, '测试幸福家庭', 'TEST2001', NOW(), NOW());

-- ============================================================
-- INSERT test users (IDs 1001-1009)
-- ============================================================

-- 1001: admin
INSERT IGNORE INTO users (id, openid, unionid, family_id, role, nickname, phone, created_at, updated_at) VALUES
(1001, 'openid_admin_1001', NULL, 0, 'admin', '管理员', '13800138000', NOW(), NOW());

-- 1002: parent, family_id 2001
INSERT IGNORE INTO users (id, openid, unionid, family_id, role, nickname, phone, created_at, updated_at) VALUES
(1002, 'openid_parent_1002', NULL, 2001, 'parent', '家长用户', '13701366188', NOW(), NOW());

-- 1003: child, family_id 2001
INSERT IGNORE INTO users (id, openid, unionid, family_id, role, nickname, phone, created_at, updated_at) VALUES
(1003, 'openid_child_1003', NULL, 2001, 'child', '孩子用户A', '13701366189', NOW(), NOW());

-- 1004: child, family_id 2001
INSERT IGNORE INTO users (id, openid, unionid, family_id, role, nickname, phone, created_at, updated_at) VALUES
(1004, 'openid_child_1004', NULL, 2001, 'child', '孩子用户B', '13701366190', NOW(), NOW());

-- 1005: teacher, roles '["teacher","parent"]', teacher_status 'active'
INSERT IGNORE INTO users (id, openid, unionid, family_id, role, nickname, phone, teacher_status, roles, created_at, updated_at) VALUES
(1005, 'openid_teacher_1005', NULL, 0, 'teacher', '成长规划师', '13800000001', 'active', '["teacher","parent"]', NOW(), NOW());

-- 1006: nutritionist (teacher role with nutritionist vendor_type placeholder)
INSERT IGNORE INTO users (id, openid, unionid, family_id, role, nickname, phone, teacher_status, roles, created_at, updated_at) VALUES
(1006, 'openid_nutritionist_1006', NULL, 0, 'teacher', '营养师', '13800000010', 'active', '["teacher"]', NOW(), NOW());

-- 1007: butler (teacher role)
INSERT IGNORE INTO users (id, openid, unionid, family_id, role, nickname, phone, teacher_status, roles, created_at, updated_at) VALUES
(1007, 'openid_butler_1007', NULL, 0, 'teacher', '管家', '13800000020', 'active', '["teacher"]', NOW(), NOW());

-- 1008: assessor (teacher role)
INSERT IGNORE INTO users (id, openid, unionid, family_id, role, nickname, phone, teacher_status, roles, created_at, updated_at) VALUES
(1008, 'openid_assessor_1008', NULL, 0, 'teacher', '测评师', '13800000030', 'active', '["teacher"]', NOW(), NOW());

-- 1009: vendor
INSERT IGNORE INTO users (id, openid, unionid, family_id, role, nickname, phone, created_at, updated_at) VALUES
(1009, 'openid_vendor_1009', NULL, 0, 'vendor', '供应商', '13800138001', NOW(), NOW());

-- ============================================================
-- INSERT family_members records linking family 2001 to users 1002, 1003, 1004
-- ============================================================

INSERT IGNORE INTO family_members (id, family_id, user_id, nickname, phone, gender, birthday, generation, is_spouse, show_to_family, total_points, system_points, streak_days, created_at, updated_at) VALUES
(2001, 2001, 1002, '家长用户', '13701366188', 'male', '1985-01-01', 0, 0, 1, 0, 0, NOW(), NOW());

INSERT IGNORE INTO family_members (id, family_id, user_id, nickname, phone, gender, birthday, generation, is_spouse, show_to_family, total_points, system_points, streak_days, created_at, updated_at) VALUES
(2002, 2001, 1003, '孩子用户A', '13701366189', 'male', '2015-06-15', -1, 0, 1, 0, 0, NOW(), NOW());

INSERT IGNORE INTO family_members (id, family_id, user_id, nickname, phone, gender, birthday, generation, is_spouse, show_to_family, total_points, system_points, streak_days, created_at, updated_at) VALUES
(2003, 2001, 1004, '孩子用户B', '13701366190', 'female', '2018-03-20', -1, 0, 1, 0, 0, NOW(), NOW());

-- ============================================================
-- INSERT 2 test articles (draft + published)
-- ============================================================

INSERT IGNORE INTO articles (id, category_id, title, summary, cover_image, content, tags, author, read_time, article_type, word_count, related_dimensions, visibility, status, is_featured, published_at, view_count, created_by, created_at, updated_at) VALUES
(1001, 1, 'E2E测试文章-草稿', '这是一篇草稿状态的文章，用于E2E测试', '', '<p>草稿文章内容</p>', '["测试"]', '浠艾福', 5, 'normal', 100, '["mind"]', 'public', 'draft', 0, NULL, 0, 1001, NOW(), NOW());

INSERT IGNORE INTO articles (id, category_id, title, summary, cover_image, content, tags, author, read_time, article_type, word_count, related_dimensions, visibility, status, is_featured, published_at, view_count, created_by, created_at, updated_at) VALUES
(1002, 1, 'E2E测试文章-已发布', '这是一篇已发布状态的文章，用于E2E测试', '', '<p>已发布文章内容</p>', '["测试"]', '浠艾福', 10, 'original', 200, '["body","mind"]', 'public', 'published', 1, NOW(), 100, 1001, NOW(), NOW());

-- ============================================================
-- INSERT 2 test activities (pending + published)
-- ============================================================

INSERT IGNORE INTO activities (id, title, description, cover_image, dimension_code, dimension_weights, activity_type, status, start_time, end_time, location, max_participants, current_participants, price, vendor_id, vendor_name, created_at, updated_at) VALUES
(1001, 'E2E测试活动-待审核', '这是一个待审核状态的活动，用于E2E测试', '', 'mind', '{"body":0,"mind":50,"wisdom":50,"action":0,"wealth":0}', 'offline', 'pending', DATE_ADD(NOW(), INTERVAL 7 DAY), DATE_ADD(NOW(), INTERVAL 14 DAY), '测试地点', 20, 0, 10000, 1005, '成长规划师', NOW(), NOW());

INSERT IGNORE INTO activities (id, title, description, cover_image, dimension_code, dimension_weights, activity_type, status, start_time, end_time, location, max_participants, current_participants, price, vendor_id, vendor_name, created_at, updated_at) VALUES
(1002, 'E2E测试活动-已发布', '这是一个已发布状态的活动，用于E2E测试', '', 'body', '{"body":60,"mind":20,"wisdom":20,"action":0,"wealth":0}', 'offline', 'published', DATE_ADD(NOW(), INTERVAL 3 DAY), DATE_ADD(NOW(), INTERVAL 10 DAY), '测试地点2', 30, 5, 5000, 1005, '成长规划师', NOW(), NOW());

-- ============================================================
-- INSERT 2 test products
-- ============================================================

INSERT IGNORE INTO products (id, name, description, intro, cover_image, images, price, stock, sales_count, product_type, vendor_id, vendor_name, status, member_price, member_eligible, domain, dimension_weights, profit_rate, delivery_method, category_id, created_at, updated_at) VALUES
(1001, 'E2E测试商品-草稿', '这是一个草稿状态的商品，用于E2E测试', '详细介绍', '', NULL, 9999, 100, 0, 'service', 1009, '供应商', 'draft', 7999, 'all', 'mind', '{"body":0,"mind":100,"wisdom":0,"action":0,"wealth":0}', 100, 1, NULL, NOW(), NOW());

INSERT IGNORE INTO products (id, name, description, intro, cover_image, images, price, stock, sales_count, product_type, vendor_id, vendor_name, status, member_price, member_eligible, domain, dimension_weights, profit_rate, delivery_method, category_id, created_at, updated_at) VALUES
(1002, 'E2E测试商品-已上架', '这是一个已上架状态的商品，用于E2E测试', '详细介绍', '', NULL, 19999, 50, 10, 'course', 1009, '供应商', 'on_shelf', 15999, 'budget', 'wisdom', '{"body":0,"mind":0,"wisdom":100,"action":0,"wealth":0}', 150, 3, NULL, NOW(), NOW());