268 lines
14 KiB
PL/PgSQL
268 lines
14 KiB
PL/PgSQL
-- ============================================
|
||
-- 诊断脚本：排查 delivery RPC 超时问题
|
||
-- 请在 Supabase SQL Editor 中逐条执行
|
||
-- ============================================
|
||
|
||
-- 诊断1：确认 delivery_build_order_json 当前是 baseline 还是 patched 版本
|
||
SELECT
|
||
proname,
|
||
CASE
|
||
WHEN prosrc LIKE '%p_raw -> ''address_snapshot''%' THEN 'PATCHED (含 address_snapshot fallback)'
|
||
WHEN prosrc LIKE '%''elderGender'', COALESCE(NULLIF(p_raw ->> ''elder_gender''%' THEN 'PATCHED (动态读取 gender/age)'
|
||
ELSE 'BASELINE (原始版本)'
|
||
END AS function_status,
|
||
md5(prosrc) AS function_md5,
|
||
LENGTH(prosrc) AS src_length
|
||
FROM pg_proc
|
||
WHERE proname = 'delivery_build_order_json';
|
||
|
||
|
||
-- 诊断2：直接测试 delivery_build_order_json 是否能正常执行（不依赖任何表数据）
|
||
-- 如果这条语句能立即返回 JSONB 结果，说明函数本身没有问题
|
||
SELECT delivery_build_order_json(
|
||
'{"id": "test123", "service_name": "测试服务", "status": "assigned", "address_snapshot_json": {"fullAddress": "测试地址"}}'::jsonb,
|
||
'[]'::jsonb,
|
||
'[]'::jsonb,
|
||
'[]'::jsonb,
|
||
NULL,
|
||
'legacy'
|
||
) AS test_result;
|
||
|
||
|
||
-- 诊断3：查看 hss_service_orders 有多少条记录、有多少条已删除、有多少条分配给当前登录用户
|
||
-- 注意：auth.uid() 在 SQL Editor 中为 NULL，所以 current_staff_id 匹配会返回空结果
|
||
-- 这条只是确认表结构和数据量
|
||
SELECT
|
||
COUNT(*) AS total_orders,
|
||
COUNT(*) FILTER (WHERE deleted_at IS NOT NULL) AS deleted_orders,
|
||
COUNT(*) FILTER (WHERE deleted_at IS NULL) AS active_orders,
|
||
COUNT(*) FILTER (WHERE current_staff_id IS NOT NULL) AS assigned_orders
|
||
FROM public.hss_service_orders;
|
||
|
||
|
||
-- 诊断4：查看 ec_care_tasks 有多少条记录（如果存在）
|
||
DO $$
|
||
BEGIN
|
||
IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'ec_care_tasks') THEN
|
||
RAISE NOTICE 'ec_care_tasks exists, row count: %', (SELECT COUNT(*) FROM public.ec_care_tasks);
|
||
ELSE
|
||
RAISE NOTICE 'ec_care_tasks does NOT exist';
|
||
END IF;
|
||
END $$;
|
||
|
||
|
||
-- 诊断5：强制刷新 PostgREST schema cache（如果怀疑是 schema 缓存问题）
|
||
-- 执行后等待 5-10 秒再测试前端
|
||
NOTIFY pgrst, 'reload schema';
|
||
|
||
|
||
-- 诊断6：如果诊断1显示函数是 PATCHED 版本，执行以下恢复语句（取消注释后运行）
|
||
-- 或者如果诊断2卡住/超时，说明 patched 版本有问题，也需要恢复
|
||
/*
|
||
CREATE OR REPLACE FUNCTION public.delivery_build_order_json(
|
||
p_raw JSONB,
|
||
p_logs JSONB DEFAULT '[]'::jsonb,
|
||
p_records JSONB DEFAULT '[]'::jsonb,
|
||
p_evidence JSONB DEFAULT '[]'::jsonb,
|
||
p_exception JSONB DEFAULT NULL,
|
||
p_source TEXT DEFAULT 'legacy'
|
||
)
|
||
RETURNS JSONB
|
||
LANGUAGE plpgsql
|
||
IMMUTABLE
|
||
AS $$
|
||
DECLARE
|
||
v_service JSONB := COALESCE(p_raw -> 'service_snapshot_json', jsonb_build_object('category', COALESCE(p_raw ->> 'service_category', ''), 'price', COALESCE((p_raw ->> 'service_price')::NUMERIC, 0)));
|
||
v_address JSONB := COALESCE(p_raw -> 'address_snapshot_json', '{}'::jsonb);
|
||
v_raw_status TEXT := COALESCE(p_raw ->> 'status', 'assigned');
|
||
v_normalized_status TEXT;
|
||
v_front_status TEXT;
|
||
v_checkin_record JSONB;
|
||
v_service_record JSONB;
|
||
v_service_items JSONB;
|
||
v_record_json JSONB;
|
||
v_timeline JSONB;
|
||
v_status_logs JSONB;
|
||
v_evidence_list JSONB;
|
||
BEGIN
|
||
IF p_source = 'care' THEN
|
||
IF COALESCE(p_raw ->> 'accepted_by_family_at', '') <> '' THEN
|
||
v_normalized_status := 'completed';
|
||
ELSIF COALESCE(p_raw ->> 'acceptance_pending_at', '') <> '' THEN
|
||
v_normalized_status := 'pending_acceptance';
|
||
ELSIF COALESCE(p_raw ->> 'service_started_at', '') <> '' THEN
|
||
v_normalized_status := 'in_service';
|
||
ELSIF COALESCE(p_raw ->> 'checked_in_at', '') <> '' THEN
|
||
v_normalized_status := 'arrived';
|
||
ELSIF COALESCE(p_raw ->> 'departed_at', '') <> '' THEN
|
||
v_normalized_status := 'departed';
|
||
ELSIF COALESCE(p_raw ->> 'accepted_at', '') <> '' THEN
|
||
v_normalized_status := 'accepted';
|
||
ELSE
|
||
v_normalized_status := CASE v_raw_status
|
||
WHEN 'ORDER_ACCEPTED' THEN 'accepted'
|
||
WHEN 'ORDER_CHECKED_IN' THEN 'arrived'
|
||
WHEN 'ORDER_IN_SERVICE' THEN 'in_service'
|
||
WHEN 'ACCEPTANCE_PENDING' THEN 'pending_acceptance'
|
||
WHEN 'ORDER_EXCEPTION' THEN 'exception'
|
||
WHEN 'ORDER_REJECTED' THEN 'rejected'
|
||
WHEN 'ORDER_CANCELLED' THEN 'cancelled'
|
||
WHEN 'ORDER_COMPLETED' THEN 'completed'
|
||
ELSE 'assigned'
|
||
END;
|
||
END IF;
|
||
ELSE
|
||
v_normalized_status := lower(v_raw_status);
|
||
END IF;
|
||
|
||
v_front_status := public.delivery_front_status(v_normalized_status, p_raw);
|
||
v_timeline := public.delivery_build_timeline(p_logs);
|
||
v_status_logs := public.delivery_build_status_logs(p_logs, COALESCE(p_raw ->> 'id', ''));
|
||
v_evidence_list := public.delivery_build_evidence(p_evidence, COALESCE(p_raw ->> 'id', ''));
|
||
|
||
SELECT item INTO v_checkin_record
|
||
FROM jsonb_array_elements(COALESCE(p_records, '[]'::jsonb)) item
|
||
WHERE COALESCE(item ->> 'record_type', item ->> 'care_record_type', '') = 'checkin'
|
||
ORDER BY COALESCE(item ->> 'created_at', '') DESC
|
||
LIMIT 1;
|
||
|
||
SELECT item INTO v_service_record
|
||
FROM jsonb_array_elements(COALESCE(p_records, '[]'::jsonb)) item
|
||
WHERE COALESCE(item ->> 'record_type', item ->> 'care_record_type', '') <> 'checkin'
|
||
AND COALESCE(item ->> 'record_type', item ->> 'care_record_type', '') <> 'review'
|
||
ORDER BY COALESCE(item ->> 'created_at', '') DESC
|
||
LIMIT 1;
|
||
|
||
IF v_service_record IS NULL THEN
|
||
v_service_record := v_checkin_record;
|
||
END IF;
|
||
|
||
v_service_items := COALESCE(v_service_record -> 'service_items_json', public.delivery_default_service_items(COALESCE(p_raw ->> 'id', ''), COALESCE(p_raw ->> 'service_name', '')));
|
||
|
||
IF v_service_record IS NULL THEN
|
||
v_record_json := NULL;
|
||
ELSE
|
||
v_record_json := jsonb_build_object(
|
||
'id', COALESCE(v_service_record ->> 'id', ''),
|
||
'orderId', COALESCE(p_raw ->> 'id', ''),
|
||
'startTime', COALESCE(v_service_record ->> 'started_at', v_service_record ->> 'service_started_at', ''),
|
||
'endTime', COALESCE(v_service_record ->> 'finished_at', v_service_record ->> 'service_finished_at', ''),
|
||
'actualDurationMinutes', COALESCE((v_service_record ->> 'duration_minutes')::INTEGER, (v_service_record ->> 'actual_duration_minutes')::INTEGER, 0),
|
||
'serviceItems', COALESCE(v_service_items, '[]'::jsonb),
|
||
'serviceContent', COALESCE(
|
||
(
|
||
SELECT jsonb_agg(elem ->> 'name')
|
||
FROM jsonb_array_elements(COALESCE(v_service_items, '[]'::jsonb)) elem
|
||
WHERE COALESCE((elem ->> 'completed')::BOOLEAN, false)
|
||
),
|
||
'[]'::jsonb
|
||
),
|
||
'processNote', COALESCE(v_service_record ->> 'summary', v_service_record ->> 'content', ''),
|
||
'elderStatus', '',
|
||
'healthMetrics', jsonb_build_object('bloodPressure', '', 'heartRate', '', 'bloodSugar', '', 'bloodOxygen', ''),
|
||
'materialsUsed', '',
|
||
'abnormalNote', '',
|
||
'photos', COALESCE(
|
||
(
|
||
SELECT jsonb_agg(item ->> 'file_url')
|
||
FROM jsonb_array_elements(COALESCE(p_evidence, '[]'::jsonb)) item
|
||
WHERE COALESCE(item ->> 'phase', '') = 'service'
|
||
),
|
||
'[]'::jsonb
|
||
),
|
||
'staffRemark', COALESCE(v_service_record ->> 'remark', ''),
|
||
'familyConfirmation', jsonb_build_object('method', 'none', 'code', '', 'signatureName', '', 'signatureUrl', '', 'confirmedAt', ''),
|
||
'createdAt', COALESCE(v_service_record ->> 'created_at', ''),
|
||
'updatedAt', COALESCE(v_service_record ->> 'updated_at', '')
|
||
);
|
||
END IF;
|
||
|
||
RETURN jsonb_build_object(
|
||
'id', COALESCE(p_raw ->> 'id', ''),
|
||
'orderNo', COALESCE(NULLIF(p_raw ->> 'task_no', ''), p_raw ->> 'order_no', ''),
|
||
'serviceType', COALESCE(NULLIF(v_service ->> 'category', ''), '居家服务'),
|
||
'serviceName', COALESCE(p_raw ->> 'service_name', ''),
|
||
'serviceCategory', COALESCE(v_service ->> 'category', ''),
|
||
'serviceItems', COALESCE(v_service_items, '[]'::jsonb),
|
||
'elderId', COALESCE(p_raw ->> 'elder_id', p_raw ->> 'user_id', ''),
|
||
'elderName', COALESCE(NULLIF(p_raw ->> 'recipient_name', ''), p_raw ->> 'elder_name', ''),
|
||
'elderNameMasked', COALESCE(NULLIF(p_raw ->> 'recipient_name', ''), p_raw ->> 'elder_name', ''),
|
||
'elderGender', '',
|
||
'elderAge', 0,
|
||
'elderPhone', COALESCE(NULLIF(p_raw ->> 'recipient_phone', ''), p_raw ->> 'elder_phone', ''),
|
||
'elderPhoneMasked', COALESCE(NULLIF(p_raw ->> 'recipient_phone', ''), p_raw ->> 'elder_phone', ''),
|
||
'fullElderName', COALESCE(NULLIF(p_raw ->> 'recipient_name', ''), p_raw ->> 'elder_name', ''),
|
||
'fullPhone', COALESCE(NULLIF(p_raw ->> 'recipient_phone', ''), p_raw ->> 'elder_phone', ''),
|
||
'contactRelation', '家属',
|
||
'addressSummary', COALESCE(NULLIF(v_address ->> 'fullAddress', ''), NULLIF(v_address ->> 'full_address', ''), NULLIF(v_address ->> 'address', ''), ''),
|
||
'address', COALESCE(NULLIF(v_address ->> 'fullAddress', ''), NULLIF(v_address ->> 'full_address', ''), NULLIF(v_address ->> 'address', ''), ''),
|
||
'addressDetail', COALESCE(NULLIF(v_address ->> 'detailAddress', ''), NULLIF(v_address ->> 'detail_address', ''), ''),
|
||
'fullAddress', COALESCE(NULLIF(v_address ->> 'fullAddress', ''), NULLIF(v_address ->> 'full_address', ''), NULLIF(v_address ->> 'address', ''), ''),
|
||
'latitude', COALESCE((v_address ->> 'latitude')::NUMERIC, 0),
|
||
'longitude', COALESCE((v_address ->> 'longitude')::NUMERIC, 0),
|
||
'appointmentTime', COALESCE(NULLIF(p_raw ->> 'appointment_time', ''), p_raw ->> 'scheduled_at', ''),
|
||
'appointmentStartTime', COALESCE(NULLIF(p_raw ->> 'appointment_time', ''), p_raw ->> 'scheduled_at', ''),
|
||
'appointmentEndTime', COALESCE(NULLIF(p_raw ->> 'appointment_time', ''), p_raw ->> 'scheduled_at', ''),
|
||
'duration', COALESCE((p_raw ->> 'duration_minutes')::INTEGER, 90),
|
||
'estimatedDuration', COALESCE((p_raw ->> 'duration_minutes')::INTEGER, 90),
|
||
'price', COALESCE((v_service ->> 'price')::NUMERIC, 0),
|
||
'staffIncome', COALESCE((v_service ->> 'price')::NUMERIC, 0),
|
||
'distance', '',
|
||
'actualStartTime', COALESCE(p_raw ->> 'service_started_at', ''),
|
||
'actualEndTime', COALESCE(NULLIF(p_raw ->> 'completed_at', ''), p_raw ->> 'service_completed_at', ''),
|
||
'status', v_front_status,
|
||
'statusText', public.delivery_status_text(v_front_status),
|
||
'statusTone', public.delivery_status_tone(v_front_status),
|
||
'riskTags', '[]'::jsonb,
|
||
'healthTags', '[]'::jsonb,
|
||
'careLevel', COALESCE(v_service ->> 'category', ''),
|
||
'needFamilyPresent', false,
|
||
'needMaterials', false,
|
||
'remark', COALESCE(p_raw ->> 'remark', ''),
|
||
'merchantId', COALESCE(p_raw ->> 'merchant_id', ''),
|
||
'merchantName', COALESCE(p_raw ->> 'merchant_name', ''),
|
||
'deliveryStaffId', COALESCE(NULLIF(p_raw ->> 'current_staff_id', ''), p_raw ->> 'assigned_to', ''),
|
||
'deliveryStaffName', COALESCE(p_raw ->> 'delivery_staff_name', ''),
|
||
'acceptTime', COALESCE(p_raw ->> 'accepted_at', ''),
|
||
'departTime', COALESCE(p_raw ->> 'departed_at', ''),
|
||
'arriveTime', COALESCE(NULLIF(p_raw ->> 'arrived_at', ''), p_raw ->> 'checked_in_at', ''),
|
||
'checkinTime', COALESCE(NULLIF(p_raw ->> 'checked_in_at', ''), p_raw ->> 'arrived_at', ''),
|
||
'startServiceTime', COALESCE(p_raw ->> 'service_started_at', ''),
|
||
'finishTime', COALESCE(NULLIF(p_raw ->> 'completed_at', ''), p_raw ->> 'service_completed_at', ''),
|
||
'cancelReason', COALESCE(p_raw ->> 'cancel_reason', ''),
|
||
'exceptionType', COALESCE(p_exception ->> 'exception_type', ''),
|
||
'exceptionDesc', COALESCE(p_exception ->> 'description', p_exception ->> 'remark', ''),
|
||
'evidenceList', COALESCE(v_evidence_list, '[]'::jsonb),
|
||
'signatureUrl', '',
|
||
'signatureName', '',
|
||
'satisfactionStatus', CASE WHEN v_front_status = 'pending_acceptance' THEN '待验收' WHEN v_front_status = 'completed' THEN '已验收' ELSE '待评价' END,
|
||
'settlementStatus', CASE WHEN v_front_status = 'completed' THEN '待结算' ELSE '待确认' END,
|
||
'archiveStatus', '未归档',
|
||
'createdAt', COALESCE(p_raw ->> 'created_at', ''),
|
||
'updatedAt', COALESCE(p_raw ->> 'updated_at', ''),
|
||
'contactName', COALESCE(p_raw ->> 'contact_name', ''),
|
||
'contactPhone', COALESCE(p_raw ->> 'contact_phone', ''),
|
||
'notices', '[]'::jsonb,
|
||
'timeline', COALESCE(v_timeline, '[]'::jsonb),
|
||
'statusLog', COALESCE(v_status_logs, '[]'::jsonb),
|
||
'serviceSummary', COALESCE(v_service_record ->> 'summary', v_service_record ->> 'content', ''),
|
||
'progressNote', COALESCE(v_service_record ->> 'remark', ''),
|
||
'distanceKm', '',
|
||
'allowCheckinRadiusMeters', 100,
|
||
'lastLocation', CASE
|
||
WHEN v_checkin_record IS NULL THEN NULL
|
||
ELSE jsonb_build_object(
|
||
'latitude', COALESCE((v_checkin_record ->> 'latitude')::NUMERIC, (v_checkin_record ->> 'checkin_latitude')::NUMERIC, 0),
|
||
'longitude', COALESCE((v_checkin_record ->> 'longitude')::NUMERIC, (v_checkin_record ->> 'checkin_longitude')::NUMERIC, 0),
|
||
'address', COALESCE(v_checkin_record ->> 'location_text', v_checkin_record ->> 'checkin_address', ''),
|
||
'time', COALESCE(v_checkin_record ->> 'checked_in_at', v_checkin_record ->> 'checkin_time', '')
|
||
)
|
||
END,
|
||
'trackPoints', COALESCE(v_service_record -> 'track_points_json', '[]'::jsonb),
|
||
'serviceRecord', v_record_json,
|
||
'abnormalReport', public.delivery_build_abnormal(p_exception, COALESCE(p_raw ->> 'id', ''))
|
||
);
|
||
END;
|
||
$$;
|
||
*/
|