medical-mall/mall_sql/tests/dev_home_service_package_pricing_seed.sql

-- ================================================================
-- DEV / TEST SEED DATA ONLY
-- 居家服务套餐价格联调测试数据，禁止用于生产真实支付。
-- 上线前必须执行对应 cleanup SQL 或确认所有测试记录已停用。
-- Seed Batch: DEV_HOME_SERVICE_PACKAGE_V1
-- ================================================================

BEGIN;

DO $$
DECLARE
  missing_services TEXT;
BEGIN
  SELECT string_agg(required.service_id, ', ' ORDER BY required.service_id)
  INTO missing_services
  FROM (
    VALUES ('svc-001'), ('svc-002'), ('svc-003')
  ) AS required(service_id)
  WHERE NOT EXISTS (
    SELECT 1
    FROM public.hss_service_catalog c
    WHERE c.id = required.service_id
      AND c.deleted_at IS NULL
  );

  IF missing_services IS NOT NULL THEN
    RAISE EXCEPTION '缺少 hss_service_catalog 服务目录数据: %', missing_services;
  END IF;
END;
$$;

INSERT INTO public.hss_service_packages (
  id,
  service_id,
  package_name,
  package_desc,
  duration_minutes,
  duration_text,
  price,
  list_price,
  is_default,
  sort_no,
  status,
  effective_at,
  expires_at,
  data_source,
  seed_batch_no,
  remark
) VALUES
  (
    'hspkg-dev-svc001-60',
    'svc-001',
    '【测试】基础上门护理 60分钟',
    '开发联调用基础套餐，验证默认推荐、待付款金额和订单快照。',
    60,
    '60分钟',
    138.00,
    168.00,
    true,
    10,
    1,
    TIMESTAMPTZ '2026-01-01 00:00:00+08',
    NULL,
    'dev_seed',
    'DEV_HOME_SERVICE_PACKAGE_V1',
    '仅用于开发联调，禁止生产真实支付；上线前必须停用或删除。'
  ),
  (
    'hspkg-dev-svc001-120',
    'svc-001',
    '【测试】基础上门护理 120分钟',
    '开发联调用延长套餐，验证多套餐展示、排序和金额差异。',
    120,
    '120分钟',
    188.00,
    228.00,
    false,
    20,
    1,
    TIMESTAMPTZ '2026-01-01 00:00:00+08',
    NULL,
    'dev_seed',
    'DEV_HOME_SERVICE_PACKAGE_V1',
    '仅用于开发联调，禁止生产真实支付；上线前必须停用或删除。'
  ),
  (
    'hspkg-dev-svc001-150',
    'svc-001',
    '【测试】基础上门护理 150分钟',
    '开发联调用加强套餐，验证高价区间和时长展示。',
    150,
    '150分钟',
    236.00,
    268.00,
    false,
    30,
    1,
    TIMESTAMPTZ '2026-01-01 00:00:00+08',
    NULL,
    'dev_seed',
    'DEV_HOME_SERVICE_PACKAGE_V1',
    '仅用于开发联调，禁止生产真实支付；上线前必须停用或删除。'
  ),
  (
    'hspkg-dev-svc002-90',
    'svc-002',
    '【测试】康复训练指导 90分钟',
    '开发联调用单一可售套餐，验证单服务单套餐场景。',
    90,
    '90分钟',
    260.00,
    320.00,
    true,
    10,
    1,
    TIMESTAMPTZ '2026-01-01 00:00:00+08',
    NULL,
    'dev_seed',
    'DEV_HOME_SERVICE_PACKAGE_V1',
    '仅用于开发联调，禁止生产真实支付；上线前必须停用或删除。'
  ),
  (
    'hspkg-dev-svc002-120-off',
    'svc-002',
    '【测试】康复训练指导 120分钟 已停用',
    '开发联调停用套餐，验证消费者查询不会返回下架数据。',
    120,
    '120分钟',
    318.00,
    360.00,
    false,
    20,
    0,
    TIMESTAMPTZ '2026-01-01 00:00:00+08',
    NULL,
    'dev_seed',
    'DEV_HOME_SERVICE_PACKAGE_V1',
    '停用测试套餐，消费者页面不应展示。'
  ),
  (
    'hspkg-dev-svc002-120-expired',
    'svc-002',
    '【测试】康复训练指导 120分钟 已过期',
    '开发联调过期套餐，验证有效期过滤。',
    120,
    '120分钟',
    299.00,
    350.00,
    false,
    30,
    1,
    TIMESTAMPTZ '2026-01-01 00:00:00+08',
    TIMESTAMPTZ '2026-05-01 00:00:00+08',
    'dev_seed',
    'DEV_HOME_SERVICE_PACKAGE_V1',
    '已过期测试套餐，消费者页面不应展示。'
  ),
  (
    'hspkg-dev-svc003-60',
    'svc-003',
    '【测试】慢病健康随访 60分钟',
    '开发联调用基础随访套餐，验证低价区间。',
    60,
    '60分钟',
    108.00,
    128.00,
    true,
    10,
    1,
    TIMESTAMPTZ '2026-01-01 00:00:00+08',
    NULL,
    'dev_seed',
    'DEV_HOME_SERVICE_PACKAGE_V1',
    '仅用于开发联调，禁止生产真实支付；上线前必须停用或删除。'
  ),
  (
    'hspkg-dev-svc003-90',
    'svc-003',
    '【测试】慢病健康随访 90分钟',
    '开发联调用标准随访套餐，验证中间价档位。',
    90,
    '90分钟',
    128.00,
    158.00,
    false,
    20,
    1,
    TIMESTAMPTZ '2026-01-01 00:00:00+08',
    NULL,
    'dev_seed',
    'DEV_HOME_SERVICE_PACKAGE_V1',
    '仅用于开发联调，禁止生产真实支付；上线前必须停用或删除。'
  ),
  (
    'hspkg-dev-svc003-120',
    'svc-003',
    '【测试】慢病健康随访 120分钟',
    '开发联调用加长随访套餐，验证高价区间和长时长展示。',
    120,
    '120分钟',
    166.00,
    198.00,
    false,
    30,
    1,
    TIMESTAMPTZ '2026-01-01 00:00:00+08',
    NULL,
    'dev_seed',
    'DEV_HOME_SERVICE_PACKAGE_V1',
    '仅用于开发联调，禁止生产真实支付；上线前必须停用或删除。'
  )
ON CONFLICT (id) DO UPDATE SET
  service_id = EXCLUDED.service_id,
  package_name = EXCLUDED.package_name,
  package_desc = EXCLUDED.package_desc,
  duration_minutes = EXCLUDED.duration_minutes,
  duration_text = EXCLUDED.duration_text,
  price = EXCLUDED.price,
  list_price = EXCLUDED.list_price,
  is_default = EXCLUDED.is_default,
  sort_no = EXCLUDED.sort_no,
  status = EXCLUDED.status,
  effective_at = EXCLUDED.effective_at,
  expires_at = EXCLUDED.expires_at,
  data_source = EXCLUDED.data_source,
  seed_batch_no = EXCLUDED.seed_batch_no,
  remark = EXCLUDED.remark,
  deleted_at = NULL,
  updated_at = now();

COMMIT;