-- ============================================================
-- 电工 APP（VoltLink）订单池演示数据
--
-- 依赖：
--   1) db_init.sql          基础表结构
--   2) 20261003_worker_app_orders.sql  报价/距离/时长等展示字段
--
-- 用法：
--   psql -U postgres -d xintong -f database/seed_worker_app_orders.sql
--
-- 特性：
--   - 独立事务（BEGIN/COMMIT）
--   - 幂等可重复执行：全部使用「固定业务单号 + ON CONFLICT DO NOTHING」
--   - 不写死主键，id 均由序列自增
--   - 生成「未被任何电工接单」的订单池数据：报修 3 / 安装 2 / 维保 2 / 勘察 2
-- ============================================================
BEGIN;

-- ------------------------------------------------------------
-- 1. 演示客户（customer_no 唯一，作为全部演示订单的归属客户）
-- ------------------------------------------------------------
INSERT INTO customer (customer_no, customer_name, province, city, district, address, contact_person, contact_phone, customer_level, status)
VALUES ('KH-DEMO-0001', '示例智电科技园', '江苏省', '南京市', '江宁区', '江宁区智汇路 88 号', '张工', '13800000000', '企业', 3)
ON CONFLICT (customer_no) DO NOTHING;

-- ------------------------------------------------------------
-- 2. 演示方案（install_order.plan_id 为必填外键，需保证存在可引用的 project_plan）
--    优先保证存在一条固定的演示方案；install_order 插入时再兜底取「任意已存在的方案」
-- ------------------------------------------------------------
INSERT INTO project_plan (plan_no, customer_id, registrar, register_time, plan_desc, status)
SELECT 'FA-DEMO-0001', c.id, '演示业务员', CURRENT_DATE, '电工 APP 订单池演示方案（自动生成）', 1
FROM customer c
WHERE c.customer_no = 'KH-DEMO-0001'
ON CONFLICT (plan_no) DO NOTHING;

-- ------------------------------------------------------------
-- 3. 报修单（status=0 待处理，进入订单池）—— 3 条，金额各不相同
-- ------------------------------------------------------------
INSERT INTO repair_request
(repair_no, customer_id, requester, requester_phone, request_time, location, repair_content, photos, status, labor_fee, material_fee, distance, estimated_duration)
SELECT 'BX-DEMO-0001', c.id, '张工', '13800000001', NOW() - INTERVAL '2 hour', 'A 栋 3 层配电间',
       '配电箱空开频繁跳闸，怀疑线路过载，请尽快上门排查处理', '[]'::jsonb, 0, 150.00, 80.00, 5.20, '1 - 1.5 小时'
FROM customer c WHERE c.customer_no = 'KH-DEMO-0001'
ON CONFLICT (repair_no) DO NOTHING;

INSERT INTO repair_request
(repair_no, customer_id, requester, requester_phone, request_time, location, repair_content, photos, status, labor_fee, material_fee, distance, estimated_duration)
SELECT 'BX-DEMO-0002', c.id, '李主管', '13800000002', NOW() - INTERVAL '5 hour', 'B 栋 1 层强电井',
       '楼层照明回路部分失电，需检测并更换损坏断路器', '[]'::jsonb, 0, 260.00, 120.00, 12.40, '2 - 3 小时'
FROM customer c WHERE c.customer_no = 'KH-DEMO-0001'
ON CONFLICT (repair_no) DO NOTHING;

INSERT INTO repair_request
(repair_no, customer_id, requester, requester_phone, request_time, location, repair_content, photos, status, labor_fee, material_fee, distance, estimated_duration)
SELECT 'BX-DEMO-0003', c.id, '王工', '13800000003', NOW() - INTERVAL '30 minute', 'C 栋 2 层办公室',
       '插座无电，需排查线路并更换面板', '[]'::jsonb, 0, 90.00, 35.00, 3.10, '1 小时以内'
FROM customer c WHERE c.customer_no = 'KH-DEMO-0001'
ON CONFLICT (repair_no) DO NOTHING;

-- ------------------------------------------------------------
-- 4. 安装派单（worker_id 为空，status=0 已派发，进入订单池）—— 2 条
--    plan_id 优先取已存在的任意方案，其次回退到演示方案
-- ------------------------------------------------------------
INSERT INTO install_order
(install_no, customer_id, installer, worker_id, dispatch_date, start_date, end_date, plan_id, remark, status, labor_fee, material_fee, distance, estimated_duration)
SELECT 'AZ-DEMO-0001', c.id, NULL, NULL, CURRENT_DATE, CURRENT_DATE, CURRENT_DATE + 3,
       COALESCE((SELECT id FROM project_plan ORDER BY id LIMIT 1), (SELECT id FROM project_plan WHERE plan_no = 'FA-DEMO-0001')),
       '智能电表安装 12 台，含桥架布线', 0, 480.00, 260.00, 12.50, '4 - 6 小时'
FROM customer c WHERE c.customer_no = 'KH-DEMO-0001'
ON CONFLICT (install_no) DO NOTHING;

INSERT INTO install_order
(install_no, customer_id, installer, worker_id, dispatch_date, start_date, end_date, plan_id, remark, status, labor_fee, material_fee, distance, estimated_duration)
SELECT 'AZ-DEMO-0002', c.id, NULL, NULL, CURRENT_DATE + 1, CURRENT_DATE + 1, CURRENT_DATE + 2,
       COALESCE((SELECT id FROM project_plan ORDER BY id LIMIT 1), (SELECT id FROM project_plan WHERE plan_no = 'FA-DEMO-0001')),
       '智能网关与采集器安装调试 3 套', 0, 320.00, 180.00, 6.80, '3 - 4 小时'
FROM customer c WHERE c.customer_no = 'KH-DEMO-0001'
ON CONFLICT (install_no) DO NOTHING;

-- ------------------------------------------------------------
-- 5. 维保派单（worker_id 为空，status=0 已派发，进入订单池）—— 2 条
-- ------------------------------------------------------------
INSERT INTO maintenance_order
(maintenance_no, customer_id, maintainer, worker_id, maintenance_date, remark, status, labor_fee, material_fee, distance, estimated_duration)
SELECT 'WB-DEMO-0001', c.id, NULL, NULL, CURRENT_DATE + 2,
       '季度维保：配电柜除尘、紧固、绝缘检测', 0, 200.00, 60.00, 8.30, '2 - 3 小时'
FROM customer c WHERE c.customer_no = 'KH-DEMO-0001'
ON CONFLICT (maintenance_no) DO NOTHING;

INSERT INTO maintenance_order
(maintenance_no, customer_id, maintainer, worker_id, maintenance_date, remark, status, labor_fee, material_fee, distance, estimated_duration)
SELECT 'WB-DEMO-0002', c.id, NULL, NULL, CURRENT_DATE + 5,
       '年度维保：变压器巡检与接地电阻测试', 0, 180.00, 40.00, 15.60, '2 小时'
FROM customer c WHERE c.customer_no = 'KH-DEMO-0001'
ON CONFLICT (maintenance_no) DO NOTHING;

-- ------------------------------------------------------------
-- 6. 勘察派单（worker_id 为空，status=0 待指派，is_draft=0，进入订单池）—— 2 条
--    金额复用 estimated_fee / actual_fee，不新增费用列
-- ------------------------------------------------------------
INSERT INTO survey_order
(survey_no, customer_id, surveyor, worker_id, survey_time, priority, task_title, site_location, source, tech_contact,
 plan_start, plan_end, is_draft, estimated_fee, actual_fee, photos, remark, status, distance, estimated_duration)
SELECT 'KC-DEMO-0001', c.id, NULL, NULL, NOW(), 3, '新增大功率充电桩勘察', 'C 栋地下车库', 'ledger', '13800000002',
       NOW() + INTERVAL '1 day', NOW() + INTERVAL '1 day 2 hour', 0, 260.00, NULL, '[]', '需评估配电容量与布线路径', 0, 8.30, '2 - 3 小时'
FROM customer c WHERE c.customer_no = 'KH-DEMO-0001'
ON CONFLICT (survey_no) DO NOTHING;

INSERT INTO survey_order
(survey_no, customer_id, surveyor, worker_id, survey_time, priority, task_title, site_location, source, tech_contact,
 plan_start, plan_end, is_draft, estimated_fee, actual_fee, photos, remark, status, distance, estimated_duration)
SELECT 'KC-DEMO-0002', c.id, NULL, NULL, NOW(), 3, '配电房扩容勘察', 'A 栋 1 层配电房', 'ledger', '13800000003',
       NOW() + INTERVAL '2 day', NOW() + INTERVAL '2 day 2 hour', 0, 180.00, NULL, '[]', '需确认扩容后回路数与母线规格', 0, 4.60, '1.5 - 2 小时'
FROM customer c WHERE c.customer_no = 'KH-DEMO-0001'
ON CONFLICT (survey_no) DO NOTHING;

COMMIT;