-- Run in Supabase SQL Editor. Uses existing customers, addresses, shipments and triggers.
-- No anonymous direct INSERT permissions are required for this workflow.
create or replace function public.create_postway_web_booking(
 p_sender jsonb,p_receiver jsonb,p_parcel jsonb,p_amount numeric
) returns jsonb language plpgsql security invoker set search_path=public as $$
declare c_id uuid; a_id uuid; b_id uuid; s_id uuid; tracking text;
begin
 if p_amount is null or p_amount <= 0 or p_amount > 100000 then raise exception 'Invalid amount'; end if;
 insert into customers(customer_type,full_name,phone,email)
 values ('individual',p_sender->>'name',p_sender->>'phone',nullif(p_sender->>'email','')) returning id into c_id;
 insert into addresses(customer_id,address_type,street,suburb,city,province,postal_code)
 values(c_id,'collection',p_sender->>'street',p_sender->>'suburb',p_sender->>'city',p_sender->>'province',p_sender->>'postalCode') returning id into a_id;
 insert into addresses(customer_id,address_type,street,suburb,city,province,postal_code)
 values(c_id,'delivery',p_receiver->>'street',p_receiver->>'suburb',p_receiver->>'city',p_receiver->>'province',p_receiver->>'postalCode') returning id into b_id;
 insert into shipments(customer_id,collection_address_id,delivery_address_id,service_level,status,weight_kg,length_cm,width_cm,height_cm,pieces,payment_status,quoted_amount,reference)
 values(c_id,a_id,b_id,p_parcel->>'serviceLevel','Awaiting Collection',(p_parcel->>'weightKg')::numeric,(p_parcel->>'lengthCm')::numeric,(p_parcel->>'widthCm')::numeric,(p_parcel->>'heightCm')::numeric,1,'Pending',p_amount,left(coalesce(p_parcel->>'parcelType','Website booking'),100))
 returning id,tracking_number into s_id,tracking;
 return jsonb_build_object('shipmentId',s_id,'trackingNumber',tracking,'status','Awaiting Collection','paymentStatus','Pending','amount',p_amount);
end $$;
revoke all on function public.create_postway_web_booking(jsonb,jsonb,jsonb,numeric) from public,anon,authenticated;
grant execute on function public.create_postway_web_booking(jsonb,jsonb,jsonb,numeric) to service_role;