608 lines
18 KiB
PL/PgSQL
608 lines
18 KiB
PL/PgSQL
-- Homecare location-distance RPCs (precheck + submit) — Fixed for uuid types
|
|
-- Date: 2026-06-03 (original), 2026-06-08 (fixed for uuid alignment)
|
|
-- Purpose: move checkin distance flow to RPC-only for phase1 tables
|
|
-- Fixed: p_org_id, p_team_id, p_operator_id changed from text to uuid
|
|
-- to match hc_dispatch_assignments table schema
|
|
|
|
create or replace function public.rpc_homecare_dispatch_assignment_upsert(
|
|
p_work_order_id uuid,
|
|
p_org_id uuid, -- ✅ fixed: was text, now uuid
|
|
p_team_id uuid, -- ✅ fixed: was text, now uuid
|
|
p_worker_id uuid,
|
|
p_service_latitude numeric,
|
|
p_service_longitude numeric,
|
|
p_service_coordinate_type text default 'gcj02',
|
|
p_operator_id uuid default null, -- ✅ fixed: was text, now uuid
|
|
p_dispatch_reason text default null
|
|
)
|
|
returns jsonb
|
|
language plpgsql
|
|
security definer
|
|
set search_path = public, app
|
|
as $$
|
|
declare
|
|
v_prev_version integer := 0;
|
|
v_prev_id text;
|
|
begin
|
|
if p_work_order_id is null then
|
|
raise exception 'p_work_order_id is required';
|
|
end if;
|
|
|
|
if p_org_id is null then
|
|
raise exception 'p_org_id is required';
|
|
end if;
|
|
|
|
if p_service_latitude is null or p_service_longitude is null then
|
|
raise exception 'service coordinates are required';
|
|
end if;
|
|
|
|
if to_regclass('public.hc_dispatch_assignments') is null then
|
|
raise exception 'hc_dispatch_assignments table is required';
|
|
end if;
|
|
|
|
select id, assign_version
|
|
into v_prev_id, v_prev_version
|
|
from public.hc_dispatch_assignments
|
|
where work_order_id = p_work_order_id
|
|
and is_current = true
|
|
order by created_at desc
|
|
limit 1;
|
|
|
|
if found then
|
|
update public.hc_dispatch_assignments
|
|
set is_current = false,
|
|
updated_at = now(),
|
|
updated_by = coalesce(p_operator_id, updated_by)
|
|
where id = v_prev_id;
|
|
end if;
|
|
|
|
insert into public.hc_dispatch_assignments(
|
|
id,
|
|
work_order_id,
|
|
org_id,
|
|
team_id,
|
|
assign_version,
|
|
worker_id,
|
|
service_latitude,
|
|
service_longitude,
|
|
service_coordinate_type,
|
|
dispatch_status,
|
|
dispatch_reason,
|
|
is_current,
|
|
dispatched_at,
|
|
created_by,
|
|
updated_by,
|
|
created_at,
|
|
updated_at
|
|
) values (
|
|
'dsp_' || gen_random_uuid()::text,
|
|
p_work_order_id,
|
|
p_org_id,
|
|
p_team_id,
|
|
v_prev_version + 1,
|
|
p_worker_id,
|
|
p_service_latitude,
|
|
p_service_longitude,
|
|
lower(coalesce(p_service_coordinate_type, 'gcj02')),
|
|
'PENDING',
|
|
p_dispatch_reason,
|
|
true,
|
|
now(),
|
|
p_operator_id,
|
|
p_operator_id,
|
|
now(),
|
|
now()
|
|
);
|
|
|
|
return jsonb_build_object('ok', true, 'assignVersion', v_prev_version + 1);
|
|
end;
|
|
$$;
|
|
|
|
create or replace function public.rpc_homecare_checkin_precheck(
|
|
p_work_order_id uuid,
|
|
p_worker_id uuid,
|
|
p_latitude numeric,
|
|
p_longitude numeric,
|
|
p_coordinate_type text default 'gcj02',
|
|
p_accuracy numeric default null,
|
|
p_reported_at timestamptz default null,
|
|
p_location_scene text default 'CHECKIN_PRECHECK'
|
|
)
|
|
returns jsonb
|
|
language plpgsql
|
|
security definer
|
|
set search_path = public, app
|
|
as $$
|
|
declare
|
|
v_assignment public.hc_dispatch_assignments%rowtype;
|
|
v_radius_meters numeric := 200;
|
|
v_distance_meters numeric;
|
|
v_can_checkin boolean := false;
|
|
v_reason_code text := 'WORK_ORDER_NOT_ASSIGNABLE';
|
|
v_scene text := upper(coalesce(p_location_scene, 'CHECKIN_PRECHECK'));
|
|
v_reported_at timestamptz := coalesce(p_reported_at, now());
|
|
v_worker_location_accepted boolean := false;
|
|
v_service_location_ready boolean := false;
|
|
v_insert_location boolean := false;
|
|
v_lat1 double precision;
|
|
v_lng1 double precision;
|
|
v_lat2 double precision;
|
|
v_lng2 double precision;
|
|
v_a double precision;
|
|
v_c double precision;
|
|
begin
|
|
if p_work_order_id is null then
|
|
raise exception 'p_work_order_id is required';
|
|
end if;
|
|
|
|
if p_worker_id is null then
|
|
raise exception 'p_worker_id is required';
|
|
end if;
|
|
|
|
if p_latitude is null or p_longitude is null then
|
|
raise exception 'p_latitude and p_longitude are required';
|
|
end if;
|
|
|
|
if v_scene not in ('CHECKIN_PRECHECK', 'CHECKIN') then
|
|
raise exception 'invalid p_location_scene: %', v_scene;
|
|
end if;
|
|
|
|
if to_regclass('public.hc_dispatch_assignments') is null then
|
|
raise exception 'hc_dispatch_assignments table is required';
|
|
end if;
|
|
|
|
if to_regclass('public.hc_worker_locations') is null then
|
|
raise exception 'hc_worker_locations table is required';
|
|
end if;
|
|
|
|
if to_regclass('public.sys_sla_config') is null then
|
|
raise exception 'sys_sla_config table is required';
|
|
end if;
|
|
|
|
select *
|
|
into v_assignment
|
|
from public.hc_dispatch_assignments
|
|
where work_order_id = p_work_order_id
|
|
and is_current = true
|
|
order by created_at desc
|
|
limit 1;
|
|
|
|
if not found then
|
|
v_reason_code := 'SERVICE_LOCATION_MISSING';
|
|
return jsonb_build_object(
|
|
'distanceMeters', null,
|
|
'allowedRadiusMeters', 0,
|
|
'canCheckin', false,
|
|
'reasonCode', v_reason_code,
|
|
'workerLocationAccepted', false,
|
|
'serviceLocationReady', false
|
|
);
|
|
end if;
|
|
|
|
if coalesce(v_assignment.dispatch_status, '') not in ('PENDING', 'ACCEPTED') then
|
|
v_reason_code := 'WORK_ORDER_NOT_ASSIGNABLE';
|
|
return jsonb_build_object(
|
|
'distanceMeters', null,
|
|
'allowedRadiusMeters', 0,
|
|
'canCheckin', false,
|
|
'reasonCode', v_reason_code,
|
|
'workerLocationAccepted', false,
|
|
'serviceLocationReady', false
|
|
);
|
|
end if;
|
|
|
|
if v_assignment.worker_id is not null and v_assignment.worker_id <> p_worker_id then
|
|
v_reason_code := 'WORKER_NOT_MATCHED';
|
|
return jsonb_build_object(
|
|
'distanceMeters', null,
|
|
'allowedRadiusMeters', 0,
|
|
'canCheckin', false,
|
|
'reasonCode', v_reason_code,
|
|
'workerLocationAccepted', false,
|
|
'serviceLocationReady', true
|
|
);
|
|
end if;
|
|
|
|
if v_assignment.service_latitude is null or v_assignment.service_longitude is null then
|
|
v_reason_code := 'SERVICE_LOCATION_MISSING';
|
|
return jsonb_build_object(
|
|
'distanceMeters', null,
|
|
'allowedRadiusMeters', 0,
|
|
'canCheckin', false,
|
|
'reasonCode', v_reason_code,
|
|
'workerLocationAccepted', false,
|
|
'serviceLocationReady', false
|
|
);
|
|
end if;
|
|
|
|
v_service_location_ready := true;
|
|
|
|
-- SLA lookup priority: WORK_ORDER > ORG > TEAM > GLOBAL
|
|
select config_value::numeric
|
|
into v_radius_meters
|
|
from public.sys_sla_config
|
|
where config_key = 'HOMECARE_CHECKIN_RADIUS_METERS'
|
|
and scope_type = 'WORK_ORDER'
|
|
and scope_id = p_work_order_id::text
|
|
and is_active = true
|
|
limit 1;
|
|
|
|
if v_radius_meters is null and v_assignment.org_id is not null then
|
|
select config_value::numeric
|
|
into v_radius_meters
|
|
from public.sys_sla_config
|
|
where config_key = 'HOMECARE_CHECKIN_RADIUS_METERS'
|
|
and scope_type = 'ORG'
|
|
and scope_id = v_assignment.org_id::text
|
|
and is_active = true
|
|
limit 1;
|
|
end if;
|
|
|
|
if v_radius_meters is null and v_assignment.team_id is not null then
|
|
select config_value::numeric
|
|
into v_radius_meters
|
|
from public.sys_sla_config
|
|
where config_key = 'HOMECARE_CHECKIN_RADIUS_METERS'
|
|
and scope_type = 'TEAM'
|
|
and scope_id = v_assignment.team_id::text
|
|
and is_active = true
|
|
limit 1;
|
|
end if;
|
|
|
|
if v_radius_meters is null then
|
|
select config_value::numeric
|
|
into v_radius_meters
|
|
from public.sys_sla_config
|
|
where config_key = 'HOMECARE_CHECKIN_RADIUS_METERS'
|
|
and scope_type = 'GLOBAL'
|
|
and scope_id is null
|
|
and is_active = true
|
|
limit 1;
|
|
end if;
|
|
|
|
v_radius_meters := coalesce(v_radius_meters, 200);
|
|
|
|
-- Haversine distance
|
|
v_lat1 := radians(p_latitude::double precision);
|
|
v_lng1 := radians(p_longitude::double precision);
|
|
v_lat2 := radians(v_assignment.service_latitude::double precision);
|
|
v_lng2 := radians(v_assignment.service_longitude::double precision);
|
|
|
|
v_a := power(sin((v_lat2 - v_lat1) / 2), 2)
|
|
+ cos(v_lat1) * cos(v_lat2) * power(sin((v_lng2 - v_lng1) / 2), 2);
|
|
v_c := 2 * atan2(sqrt(v_a), sqrt(1 - v_a));
|
|
v_distance_meters := round((6371000 * v_c)::numeric, 2);
|
|
|
|
v_can_checkin := (v_distance_meters <= v_radius_meters);
|
|
v_reason_code := case when v_can_checkin then 'OK' else 'OUT_OF_RADIUS' end;
|
|
v_insert_location := (v_scene = 'CHECKIN_PRECHECK') or (v_scene = 'CHECKIN');
|
|
|
|
if v_insert_location then
|
|
insert into public.hc_worker_locations(
|
|
id,
|
|
work_order_id,
|
|
worker_id,
|
|
latitude,
|
|
longitude,
|
|
coordinate_type,
|
|
accuracy,
|
|
location_scene,
|
|
reported_at,
|
|
distance_meters,
|
|
created_at
|
|
) values (
|
|
'loc_' || gen_random_uuid()::text,
|
|
p_work_order_id,
|
|
p_worker_id,
|
|
p_latitude,
|
|
p_longitude,
|
|
lower(coalesce(p_coordinate_type, 'gcj02')),
|
|
p_accuracy,
|
|
v_scene,
|
|
v_reported_at,
|
|
v_distance_meters,
|
|
now()
|
|
);
|
|
|
|
v_worker_location_accepted := true;
|
|
end if;
|
|
|
|
return jsonb_build_object(
|
|
'distanceMeters', v_distance_meters,
|
|
'allowedRadiusMeters', v_radius_meters,
|
|
'canCheckin', v_can_checkin,
|
|
'reasonCode', v_reason_code,
|
|
'workerLocationAccepted', v_worker_location_accepted,
|
|
'serviceLocationReady', v_service_location_ready
|
|
);
|
|
end;
|
|
$$;
|
|
|
|
create or replace function public.rpc_homecare_checkin_submit(
|
|
p_work_order_id uuid,
|
|
p_worker_id uuid,
|
|
p_latitude numeric,
|
|
p_longitude numeric,
|
|
p_coordinate_type text default 'gcj02',
|
|
p_accuracy numeric default null,
|
|
p_reported_at timestamptz default null,
|
|
p_evidence_file_ids jsonb default '[]'::jsonb,
|
|
p_signature_payload text default null,
|
|
p_reason text default null
|
|
)
|
|
returns jsonb
|
|
language plpgsql
|
|
security definer
|
|
set search_path = public, app
|
|
as $$
|
|
declare
|
|
v_precheck jsonb;
|
|
v_can_checkin boolean := false;
|
|
v_assignment public.hc_dispatch_assignments%rowtype;
|
|
v_assignment_found boolean := false;
|
|
v_max_photo_count numeric := 3;
|
|
v_requested_count integer := 0;
|
|
v_existing_count integer := 0;
|
|
v_mismatch_count integer := 0;
|
|
v_not_owned_count integer := 0;
|
|
v_not_ready_count integer := 0;
|
|
begin
|
|
if to_regclass('public.hc_work_order_confirmations') is null then
|
|
raise exception 'hc_work_order_confirmations table is required';
|
|
end if;
|
|
|
|
if p_signature_payload is null or length(trim(p_signature_payload)) = 0 then
|
|
return jsonb_build_object(
|
|
'distanceMeters', null,
|
|
'allowedRadiusMeters', 0,
|
|
'canCheckin', false,
|
|
'reasonCode', 'SIGNATURE_REQUIRED',
|
|
'workerLocationAccepted', false,
|
|
'serviceLocationReady', false
|
|
);
|
|
end if;
|
|
|
|
if length(trim(p_signature_payload)) < 8 then
|
|
return jsonb_build_object(
|
|
'distanceMeters', null,
|
|
'allowedRadiusMeters', 0,
|
|
'canCheckin', false,
|
|
'reasonCode', 'SIGNATURE_INVALID',
|
|
'workerLocationAccepted', false,
|
|
'serviceLocationReady', false
|
|
);
|
|
end if;
|
|
|
|
if p_evidence_file_ids is null or jsonb_typeof(p_evidence_file_ids) <> 'array' then
|
|
return jsonb_build_object(
|
|
'distanceMeters', null,
|
|
'allowedRadiusMeters', 0,
|
|
'canCheckin', false,
|
|
'reasonCode', 'EVIDENCE_FILE_NOT_EXIST',
|
|
'workerLocationAccepted', false,
|
|
'serviceLocationReady', false
|
|
);
|
|
end if;
|
|
|
|
v_requested_count := jsonb_array_length(p_evidence_file_ids);
|
|
if v_requested_count < 1 then
|
|
return jsonb_build_object(
|
|
'distanceMeters', null,
|
|
'allowedRadiusMeters', 0,
|
|
'canCheckin', false,
|
|
'reasonCode', 'EVIDENCE_FILE_NOT_EXIST',
|
|
'workerLocationAccepted', false,
|
|
'serviceLocationReady', false
|
|
);
|
|
end if;
|
|
|
|
select *
|
|
into v_assignment
|
|
from public.hc_dispatch_assignments
|
|
where work_order_id = p_work_order_id
|
|
and is_current = true
|
|
order by created_at desc
|
|
limit 1;
|
|
|
|
v_assignment_found := found;
|
|
|
|
select config_value::numeric
|
|
into v_max_photo_count
|
|
from public.sys_sla_config
|
|
where config_key = 'HOMECARE_CHECKIN_MAX_PHOTO_COUNT'
|
|
and scope_type = 'WORK_ORDER'
|
|
and scope_id = p_work_order_id::text
|
|
and is_active = true
|
|
limit 1;
|
|
|
|
if v_max_photo_count is null and v_assignment_found and v_assignment.org_id is not null then
|
|
select config_value::numeric
|
|
into v_max_photo_count
|
|
from public.sys_sla_config
|
|
where config_key = 'HOMECARE_CHECKIN_MAX_PHOTO_COUNT'
|
|
and scope_type = 'ORG'
|
|
and scope_id = v_assignment.org_id::text
|
|
and is_active = true
|
|
limit 1;
|
|
end if;
|
|
|
|
if v_max_photo_count is null and v_assignment_found and v_assignment.team_id is not null then
|
|
select config_value::numeric
|
|
into v_max_photo_count
|
|
from public.sys_sla_config
|
|
where config_key = 'HOMECARE_CHECKIN_MAX_PHOTO_COUNT'
|
|
and scope_type = 'TEAM'
|
|
and scope_id = v_assignment.team_id::text
|
|
and is_active = true
|
|
limit 1;
|
|
end if;
|
|
|
|
if v_max_photo_count is null then
|
|
select config_value::numeric
|
|
into v_max_photo_count
|
|
from public.sys_sla_config
|
|
where config_key = 'HOMECARE_CHECKIN_MAX_PHOTO_COUNT'
|
|
and scope_type = 'GLOBAL'
|
|
and scope_id is null
|
|
and is_active = true
|
|
limit 1;
|
|
end if;
|
|
|
|
v_max_photo_count := coalesce(v_max_photo_count, 3);
|
|
if v_requested_count > v_max_photo_count then
|
|
return jsonb_build_object(
|
|
'distanceMeters', null,
|
|
'allowedRadiusMeters', 0,
|
|
'canCheckin', false,
|
|
'reasonCode', 'PHOTO_COUNT_EXCEED_LIMIT',
|
|
'workerLocationAccepted', false,
|
|
'serviceLocationReady', false
|
|
);
|
|
end if;
|
|
|
|
select count(*)
|
|
into v_existing_count
|
|
from public.hc_evidence_files e
|
|
where e.id in (select jsonb_array_elements_text(p_evidence_file_ids));
|
|
|
|
if v_existing_count <> v_requested_count then
|
|
return jsonb_build_object(
|
|
'distanceMeters', null,
|
|
'allowedRadiusMeters', 0,
|
|
'canCheckin', false,
|
|
'reasonCode', 'EVIDENCE_FILE_NOT_EXIST',
|
|
'workerLocationAccepted', false,
|
|
'serviceLocationReady', false
|
|
);
|
|
end if;
|
|
|
|
select count(*)
|
|
into v_mismatch_count
|
|
from public.hc_evidence_files e
|
|
where e.id in (select jsonb_array_elements_text(p_evidence_file_ids))
|
|
and e.work_order_id <> p_work_order_id;
|
|
|
|
if v_mismatch_count > 0 then
|
|
return jsonb_build_object(
|
|
'distanceMeters', null,
|
|
'allowedRadiusMeters', 0,
|
|
'canCheckin', false,
|
|
'reasonCode', 'EVIDENCE_FILE_WORK_ORDER_MISMATCH',
|
|
'workerLocationAccepted', false,
|
|
'serviceLocationReady', false
|
|
);
|
|
end if;
|
|
|
|
select count(*)
|
|
into v_not_owned_count
|
|
from public.hc_evidence_files e
|
|
where e.id in (select jsonb_array_elements_text(p_evidence_file_ids))
|
|
and e.work_order_id = p_work_order_id
|
|
and coalesce(e.uploader_id, '') <> p_worker_id::text;
|
|
|
|
if v_not_owned_count > 0 then
|
|
return jsonb_build_object(
|
|
'distanceMeters', null,
|
|
'allowedRadiusMeters', 0,
|
|
'canCheckin', false,
|
|
'reasonCode', 'EVIDENCE_FILE_NOT_OWNED',
|
|
'workerLocationAccepted', false,
|
|
'serviceLocationReady', false
|
|
);
|
|
end if;
|
|
|
|
select count(*)
|
|
into v_not_ready_count
|
|
from public.hc_evidence_files e
|
|
where e.id in (select jsonb_array_elements_text(p_evidence_file_ids))
|
|
and e.work_order_id = p_work_order_id
|
|
and (e.file_url is null or length(trim(e.file_url)) = 0);
|
|
|
|
if v_not_ready_count > 0 then
|
|
return jsonb_build_object(
|
|
'distanceMeters', null,
|
|
'allowedRadiusMeters', 0,
|
|
'canCheckin', false,
|
|
'reasonCode', 'EVIDENCE_FILE_NOT_READY',
|
|
'workerLocationAccepted', false,
|
|
'serviceLocationReady', false
|
|
);
|
|
end if;
|
|
|
|
v_precheck := public.rpc_homecare_checkin_precheck(
|
|
p_work_order_id,
|
|
p_worker_id,
|
|
p_latitude,
|
|
p_longitude,
|
|
p_coordinate_type,
|
|
p_accuracy,
|
|
p_reported_at,
|
|
'CHECKIN'
|
|
);
|
|
|
|
v_can_checkin := coalesce((v_precheck ->> 'canCheckin')::boolean, false);
|
|
if not v_can_checkin then
|
|
return v_precheck;
|
|
end if;
|
|
|
|
insert into public.hc_work_order_confirmations(
|
|
id,
|
|
work_order_id,
|
|
confirmation_type,
|
|
status,
|
|
confirmed_by,
|
|
confirmed_at,
|
|
reason,
|
|
payload,
|
|
created_at,
|
|
updated_at
|
|
) values (
|
|
'woc_' || gen_random_uuid()::text,
|
|
p_work_order_id,
|
|
'ARRIVAL',
|
|
'PENDING',
|
|
p_worker_id,
|
|
null,
|
|
coalesce(p_reason, 'worker_checkin_submitted'),
|
|
jsonb_build_object(
|
|
'distanceMeters', (v_precheck ->> 'distanceMeters')::numeric,
|
|
'allowedRadiusMeters', (v_precheck ->> 'allowedRadiusMeters')::numeric,
|
|
'coordinateType', coalesce(lower(p_coordinate_type), 'gcj02'),
|
|
'reportedAt', coalesce(p_reported_at, now()),
|
|
'signatureProvided', (p_signature_payload is not null and length(trim(p_signature_payload)) > 0),
|
|
'evidenceFileIds', coalesce(p_evidence_file_ids, '[]'::jsonb)
|
|
),
|
|
now(),
|
|
now()
|
|
);
|
|
|
|
return v_precheck || jsonb_build_object('confirmationInserted', true);
|
|
end;
|
|
$$;
|
|
|
|
-- ============================================================
|
|
-- Grants (updated for uuid types)
|
|
-- ============================================================
|
|
|
|
revoke all on function public.rpc_homecare_checkin_precheck(uuid, uuid, numeric, numeric, text, numeric, timestamptz, text) from public;
|
|
grant execute on function public.rpc_homecare_checkin_precheck(uuid, uuid, numeric, numeric, text, numeric, timestamptz, text) to service_role;
|
|
|
|
revoke all on function public.rpc_homecare_dispatch_assignment_upsert(uuid, uuid, uuid, uuid, numeric, numeric, text, uuid, text) from public;
|
|
grant execute on function public.rpc_homecare_dispatch_assignment_upsert(uuid, uuid, uuid, uuid, numeric, numeric, text, uuid, text) to service_role;
|
|
|
|
revoke all on function public.rpc_homecare_checkin_submit(uuid, uuid, numeric, numeric, text, numeric, timestamptz, jsonb, text, text) from public;
|
|
grant execute on function public.rpc_homecare_checkin_submit(uuid, uuid, numeric, numeric, text, numeric, timestamptz, jsonb, text, text) to service_role;
|
|
|
|
-- ============================================================
|
|
-- Comments
|
|
-- ============================================================
|
|
|
|
comment on function public.rpc_homecare_checkin_precheck(uuid, uuid, numeric, numeric, text, numeric, timestamptz, text)
|
|
is 'Homecare checkin distance precheck RPC with SLA lookup and optional location snapshot insert';
|
|
|
|
comment on function public.rpc_homecare_dispatch_assignment_upsert(uuid, uuid, uuid, uuid, numeric, numeric, text, uuid, text)
|
|
is 'Homecare dispatch assignment upsert RPC; rotates current assignment and snapshots service location for distance checkin (uuid types)';
|
|
|
|
comment on function public.rpc_homecare_checkin_submit(uuid, uuid, numeric, numeric, text, numeric, timestamptz, jsonb, text, text)
|
|
is 'Homecare checkin submit RPC; re-runs distance precheck and writes confirmation snapshot when checkin is allowed';
|