iloom-flatten/e2e/database-validation.sql

-- ============================================================================
-- 全流程模拟测试 - 数据库验证脚本
-- ============================================================================
-- 用途：验证采购商下单 → 分享链接 → 纺织厂确认 → 生产流程 → 分批次入库
--       的完整业务流程数据一致性
-- 
-- 执行方式：通过 meoo-cli cloud query --sql "..." 逐条执行
-- ============================================================================

-- ============================================================================
-- 阶段零：环境准备（清理旧数据，模拟首次合作）
-- ============================================================================

-- 0.1 清理旧的合作关系（模拟首次合作场景）
DELETE FROM company_relationships 
WHERE purchaser_company_id = '11111111-1111-1111-1111-111111111101' 
  AND factory_company_id = '11111111-1111-1111-1111-111111111102';

-- 0.2 清理旧的测试计划（避免重复）
DELETE FROM production_plans WHERE plan_code = 'TEST-2026-001';

-- 0.3 清理旧的测试产品
DELETE FROM products WHERE fabric_code = 'QM-XW-280-01' 
  AND company_id = '11111111-1111-1111-1111-111111111101';

-- ============================================================================
-- 阶段一：采购商创建产品和生产计划
-- ============================================================================

-- 1.1 创建产品信息
INSERT INTO products (
  company_id, product_name, weight, color, fabric_code, color_code,
  yarn_usage_per_meter, yarn_types, yarn_ratios, raw_fabric_rolls, 
  raw_fabric_meters, total_stock, production_price, warp_weight, 
  weft_weight, total_weight
) VALUES (
  '11111111-1111-1111-1111-111111111101',  -- 采购商公司ID
  '全棉斜纹布', 
  280, 
  '藏青', 
  'QM-XW-280-01', 
  '01',
  350, 
  ARRAY['精梳棉纱','涤纶纱'], 
  ARRAY[0.7,0.3], 
  0, 
  0, 
  0, 
  18.5, 
  160, 
  120, 
  280
);

-- 1.2 获取刚创建的产品ID（用于后续关联）
-- SELECT id FROM products WHERE fabric_code = 'QM-XW-280-01' 
--   AND company_id = '11111111-1111-1111-1111-111111111101';

-- 1.3 创建生产计划
INSERT INTO production_plans (
  plan_code, product_name, color, fabric_code, color_code,
  remark, purchaser_id, target_quantity, completed_quantity, 
  yarn_usage_per_meter, production_price, status, start_time, created_by
) VALUES (
  'TEST-2026-001', 
  '全棉斜纹布', 
  '藏青', 
  'QM-XW-280-01', 
  '01',
  '模拟测试订单', 
  '11111111-1111-1111-1111-111111111101',  -- 采购商公司ID
  10000,  -- 目标产量10000米
  0,      -- 初始完成量为0
  350,    -- 每米纱用量
  18.5,   -- 生产采购价
  'pending', 
  NOW(),
  'a0000001-0000-0000-0000-000000000001'  -- 采购商用户ID
);

-- 1.4 获取刚创建的计划ID（用于后续关联）
-- SELECT id FROM production_plans WHERE plan_code = 'TEST-2026-001';

-- 1.5 创建纱线配比（假设计划ID为 {plan_id}）
-- INSERT INTO yarn_ratios (plan_id, yarn_name, ratio, amount_per_meter, total_amount, company_id)
-- VALUES ('{plan_id}', '精梳棉纱', 0.7, 245, 2450000, '11111111-1111-1111-1111-111111111101'),
--        ('{plan_id}', '涤纶纱', 0.3, 105, 1050000, '11111111-1111-1111-1111-111111111101');

-- 1.6 创建流程节点（5个标准节点）
-- INSERT INTO plan_process_steps (plan_id, step_type, status, company_id)
-- VALUES ('{plan_id}', 'confirm', 'pending', '11111111-1111-1111-1111-111111111101'),
--        ('{plan_id}', 'yarn_purchase', 'pending', '11111111-1111-1111-1111-111111111101'),
--        ('{plan_id}', 'dyeing', 'pending', '11111111-1111-1111-1111-111111111101'),
--        ('{plan_id}', 'machine_start', 'pending', '11111111-1111-1111-1111-111111111101'),
--        ('{plan_id}', 'fabric_warehouse', 'pending', '11111111-1111-1111-1111-111111111101');

-- ============================================================================
-- 阶段二：生成分享链接（首次合作）
-- ============================================================================

-- 2.1 生成分享链接
-- INSERT INTO share_links (short_code, plan_id, factory_type, created_by, expires_at, status)
-- VALUES ('TEST-SIM-001', '{plan_id}', 'textile', 'a0000001-0000-0000-0000-000000000001',
--   NOW() + INTERVAL '30 minutes', 'pending');

-- ============================================================================
-- 阶段三：纺织厂点击链接并确认导入
-- ============================================================================

-- 3.1 纺织厂点击链接（更新状态为 clicked）
-- UPDATE share_links SET status = 'clicked', clicked_at = NOW(), click_count = 1
-- WHERE short_code = 'TEST-SIM-001';

-- 3.2 纺织厂确认导入 - 创建工厂关联
-- INSERT INTO plan_factories (plan_id, factory_id, factory_type)
-- VALUES ('{plan_id}', '11111111-1111-1111-1111-111111111102', 'textile');

-- 3.3 更新分享链接状态为 confirmed
-- UPDATE share_links SET status = 'confirmed', responded_at = NOW(),
--   response_result = 'confirmed', used_at = NOW()
-- WHERE short_code = 'TEST-SIM-001';

-- 3.4 建立合作关系
-- INSERT INTO company_relationships (purchaser_company_id, factory_company_id, factory_type, status)
-- VALUES ('11111111-1111-1111-1111-111111111101', '11111111-1111-1111-1111-111111111102', 'textile', 'active');

-- 3.5 更新计划状态为 producing
-- UPDATE production_plans SET status = 'producing' WHERE id = '{plan_id}';

-- ============================================================================
-- 阶段四：纺织厂确认生产流程节点
-- ============================================================================

-- 4.1 确认所有流程节点（5个节点全部完成）
-- UPDATE plan_process_steps SET status = 'completed', timestamp = NOW(),
--   operator_id = 'a0000002-0000-0000-0000-000000000002'  -- 纺织厂用户ID
-- WHERE plan_id = '{plan_id}';

-- ============================================================================
-- 阶段五：分批次坯布入库（3批）
-- ============================================================================

-- 5.1 第1批入库：3000米，30匹
-- INSERT INTO inventory_records (plan_id, warehouse_id, quantity, rolls, company_id, operator_id, price_per_meter)
-- VALUES ('{plan_id}', '22222222-2222-2222-2222-222222222202', 3000, 30, 
--   '11111111-1111-1111-1111-111111111102', 'a0000002-0000-0000-0000-000000000002', 18.5);

-- 5.2 第2批入库：4000米，40匹
-- INSERT INTO inventory_records (plan_id, warehouse_id, quantity, rolls, company_id, operator_id, price_per_meter)
-- VALUES ('{plan_id}', '22222222-2222-2222-2222-222222222202', 4000, 40, 
--   '11111111-1111-1111-1111-111111111102', 'a0000002-0000-0000-0000-000000000002', 18.5);

-- 5.3 第3批入库：3000米，28匹
-- INSERT INTO inventory_records (plan_id, warehouse_id, quantity, rolls, company_id, operator_id, price_per_meter)
-- VALUES ('{plan_id}', '22222222-2222-2222-2222-222222222202', 3000, 28, 
--   '11111111-1111-1111-1111-111111111102', 'a0000002-0000-0000-0000-000000000002', 18.5);

-- 5.4 更新计划完成状态
-- UPDATE production_plans SET completed_quantity = 10000, status = 'completed'
-- WHERE id = '{plan_id}';

-- ============================================================================
-- 阶段六：数据完整性验证查询
-- ============================================================================

-- 6.1 验证计划基本信息
SELECT 
  p.plan_code,
  p.status,
  p.target_quantity,
  p.completed_quantity,
  p.product_name,
  p.color,
  p.fabric_code,
  c.name as purchaser_name
FROM production_plans p
LEFT JOIN companies c ON c.id = p.purchaser_id
WHERE p.plan_code = 'TEST-2026-001';

-- 预期结果：
-- plan_code: TEST-2026-001
-- status: completed
-- target_quantity: 10000
-- completed_quantity: 10000
-- product_name: 全棉斜纹布
-- color: 藏青
-- fabric_code: QM-XW-280-01

-- 6.2 验证流程节点完成情况
SELECT 
  p.plan_code,
  COUNT(*) as total_steps,
  SUM(CASE WHEN ps.status = 'completed' THEN 1 ELSE 0 END) as completed_steps,
  ARRAY_AGG(ps.step_type || ':' || ps.status) as step_details
FROM production_plans p
LEFT JOIN plan_process_steps ps ON ps.plan_id = p.id
WHERE p.plan_code = 'TEST-2026-001'
GROUP BY p.plan_code;

-- 预期结果：
-- total_steps: 5
-- completed_steps: 5
-- step_details: {confirm:completed, yarn_purchase:completed, dyeing:completed, machine_start:completed, fabric_warehouse:completed}

-- 6.3 验证入库记录汇总
SELECT 
  p.plan_code,
  COUNT(ir.id) as inventory_batches,
  COALESCE(SUM(ir.quantity), 0) as total_meters,
  COALESCE(SUM(ir.rolls), 0) as total_rolls,
  AVG(ir.price_per_meter) as avg_price
FROM production_plans p
LEFT JOIN inventory_records ir ON ir.plan_id = p.id
WHERE p.plan_code = 'TEST-2026-001'
GROUP BY p.plan_code;

-- 预期结果：
-- inventory_batches: 3
-- total_meters: 10000
-- total_rolls: 98
-- avg_price: 18.5

-- 6.4 验证数据一致性（completed_quantity = SUM(inventory_records.quantity)）
SELECT 
  p.plan_code,
  p.completed_quantity,
  COALESCE(SUM(ir.quantity), 0) as inventory_total,
  CASE 
    WHEN p.completed_quantity = COALESCE(SUM(ir.quantity), 0) THEN '✓ 一致'
    ELSE '✗ 不一致'
  END as consistency_check
FROM production_plans p
LEFT JOIN inventory_records ir ON ir.plan_id = p.id
WHERE p.plan_code = 'TEST-2026-001'
GROUP BY p.plan_code, p.completed_quantity;

-- 预期结果：consistency_check = '✓ 一致'

-- 6.5 验证分享链接状态
SELECT 
  sl.short_code,
  sl.status,
  sl.factory_type,
  sl.click_count,
  sl.used_at IS NOT NULL as is_used,
  sl.expires_at > NOW() as is_valid,
  c.name as creator_company
FROM share_links sl
LEFT JOIN profiles pr ON pr.id = sl.created_by
LEFT JOIN companies c ON c.id = pr.company_id
WHERE sl.short_code = 'TEST-SIM-001';

-- 预期结果：
-- status: confirmed
-- is_used: true
-- is_valid: false (已使用)

-- 6.6 验证合作关系建立
SELECT 
  cr.status,
  cr.factory_type,
  pc.name as purchaser_company,
  fc.name as factory_company,
  cr.created_at
FROM company_relationships cr
LEFT JOIN companies pc ON pc.id = cr.purchaser_company_id
LEFT JOIN companies fc ON fc.id = cr.factory_company_id
WHERE cr.purchaser_company_id = '11111111-1111-1111-1111-111111111101'
  AND cr.factory_company_id = '11111111-1111-1111-1111-111111111102';

-- 预期结果：
-- status: active
-- factory_type: textile

-- 6.7 验证工厂关联
SELECT 
  pf.factory_type,
  c.name as factory_name,
  pf.created_at
FROM plan_factories pf
LEFT JOIN companies c ON c.id = pf.factory_id
LEFT JOIN production_plans p ON p.id = pf.plan_id
WHERE p.plan_code = 'TEST-2026-001';

-- 预期结果：
-- factory_type: textile
-- factory_name: 示例纺织厂

-- 6.8 综合验证报告
SELECT 
  '=== 全流程模拟测试验证报告 ===' as section,
  '' as detail
UNION ALL
SELECT 
  '计划编号: ' || p.plan_code,
  '状态: ' || p.status
FROM production_plans p WHERE p.plan_code = 'TEST-2026-001'
UNION ALL
SELECT 
  '目标产量: ' || p.target_quantity || ' 米',
  '完成产量: ' || p.completed_quantity || ' 米'
FROM production_plans p WHERE p.plan_code = 'TEST-2026-001'
UNION ALL
SELECT 
  '入库批次: ' || COUNT(ir.id),
  '总入库量: ' || COALESCE(SUM(ir.quantity), 0) || ' 米'
FROM production_plans p
LEFT JOIN inventory_records ir ON ir.plan_id = p.id
WHERE p.plan_code = 'TEST-2026-001'
UNION ALL
SELECT 
  '流程节点: ' || COUNT(ps.id),
  '已完成: ' || SUM(CASE WHEN ps.status = 'completed' THEN 1 ELSE 0 END)
FROM production_plans p
LEFT JOIN plan_process_steps ps ON ps.plan_id = p.id
WHERE p.plan_code = 'TEST-2026-001'
UNION ALL
SELECT 
  '数据一致性: ' || 
  CASE 
    WHEN p.completed_quantity = COALESCE((SELECT SUM(quantity) FROM inventory_records WHERE plan_id = p.id), 0) 
    THEN '✓ 通过'
    ELSE '✗ 失败'
  END,
  ''
FROM production_plans p WHERE p.plan_code = 'TEST-2026-001';

-- ============================================================================
-- 阶段七：清理测试数据（可选）
-- ============================================================================

-- 7.1 清理测试计划及相关数据
-- DELETE FROM inventory_records WHERE plan_id IN (SELECT id FROM production_plans WHERE plan_code = 'TEST-2026-001');
-- DELETE FROM plan_process_steps WHERE plan_id IN (SELECT id FROM production_plans WHERE plan_code = 'TEST-2026-001');
-- DELETE FROM yarn_ratios WHERE plan_id IN (SELECT id FROM production_plans WHERE plan_code = 'TEST-2026-001');
-- DELETE FROM plan_factories WHERE plan_id IN (SELECT id FROM production_plans WHERE plan_code = 'TEST-2026-001');
-- DELETE FROM share_links WHERE short_code = 'TEST-SIM-001';
-- DELETE FROM production_plans WHERE plan_code = 'TEST-2026-001';
-- DELETE FROM products WHERE fabric_code = 'QM-XW-280-01' AND company_id = '11111111-1111-1111-1111-111111111101';
-- DELETE FROM company_relationships WHERE purchaser_company_id = '11111111-1111-1111-1111-111111111101' AND factory_company_id = '11111111-1111-1111-1111-111111111102';

-- ============================================================================
-- 结束
-- ============================================================================