DO $BODY$
DECLARE
-- INPUTS: Safe handling of n8n data using $val$ wrappers
p_shop_phone text := $val$+441234567890$val$;
p_cust_phone text := $val$+447700900000$val$;
p_tool_name text := $val$reorder_usual$val$;
p_args jsonb := $val${}$val$::jsonb;
-- SYSTEM VARIABLES
v_schema text; v_radius numeric; v_shop_pc text; v_result json; v_order_id int;
v_clean_input_pc text; v_clean_shop_pc text;
-- CALCULATION VARIABLES
v_cust_id int; v_saved_pc text;
cust_lat numeric; cust_lon numeric; shop_lat numeric; shop_lon numeric; dist_miles numeric;
BEGIN
-- 1. IDENTIFY TENANT (Fast Registry Lookup)
SELECT db_schema, delivery_radius_miles, shop_postcode
INTO v_schema, v_radius, v_shop_pc
FROM public.client_registry
WHERE twilio_number = p_shop_phone;
IF v_schema IS NULL THEN
v_result := json_build_object('error', 'Shop not found');
ELSE
CASE p_tool_name
-- A. SEARCH MENU
WHEN 'search_menu' THEN
EXECUTE format(
'SELECT json_agg(t) FROM (
SELECT i.name, i.description, v.price
FROM %I.items i
JOIN %I.item_variants v ON i.id = v.item_id
WHERE i.name ILIKE $1 OR i.description ILIKE $1
LIMIT 5
) t', v_schema, v_schema
) INTO v_result USING '%' || (p_args->>'query') || '%';
-- B. CHECK DELIVERY ZONE
WHEN 'check_delivery_zone' THEN
v_clean_input_pc := UPPER(REPLACE(p_args->>'address', ' ', ''));
v_clean_shop_pc := UPPER(REPLACE(v_shop_pc, ' ', ''));
EXECUTE format('SELECT id, postcode FROM %I.customers WHERE phone = $1', v_schema)
INTO v_cust_id, v_saved_pc USING p_cust_phone;
IF v_cust_id IS NOT NULL AND v_saved_pc = v_clean_input_pc THEN
v_result := json_build_object('in_range', true, 'message', 'Yes, that is your registered address.');
ELSE
SELECT latitude, longitude INTO cust_lat, cust_lon FROM public.uk_postcodes WHERE postcode = v_clean_input_pc;
SELECT latitude, longitude INTO shop_lat, shop_lon FROM public.uk_postcodes WHERE postcode = v_clean_shop_pc;
IF cust_lat IS NULL OR shop_lat IS NULL THEN
v_result := json_build_object(
'in_range', false,
'error', 'Postcode not found',
'details', json_build_object('shop_pc', v_clean_shop_pc, 'cust_pc', v_clean_input_pc)
);
ELSE
dist_miles := 3963 * acos(
cos(radians(shop_lat)) * cos(radians(cust_lat)) *
cos(radians(cust_lon) - radians(shop_lon)) +
sin(radians(shop_lat)) * sin(radians(cust_lat))
);
IF dist_miles <= v_radius THEN
v_result := json_build_object('in_range', true, 'distance', round(dist_miles, 2), 'message', 'Yes, we deliver.');
IF v_cust_id IS NULL THEN
EXECUTE format('INSERT INTO %I.customers (phone, postcode, first_name) VALUES ($1, $2, ''Guest'')', v_schema)
USING p_cust_phone, v_clean_input_pc;
END IF;
ELSE
v_result := json_build_object('in_range', false, 'distance', round(dist_miles, 2), 'message', 'Too far.');
END IF;
END IF;
END IF;
-- C. PLACE ORDER
WHEN 'place_order' THEN
EXECUTE format('INSERT INTO %I.customers (phone, first_name) VALUES ($1, ''Guest'') ON CONFLICT (phone) DO NOTHING', v_schema) USING p_cust_phone;
EXECUTE format(
'INSERT INTO %I.orders (customer_id, order_type, payment_method, total_amount, delivery_address, status)
SELECT id, $1, $2, $3, $4, ''pending'' FROM %I.customers WHERE phone = $5 RETURNING id',
v_schema, v_schema
) INTO v_order_id USING p_args->>'order_type', p_args->>'payment_method', (p_args->>'total')::numeric, p_args->>'address', p_cust_phone;
EXECUTE format(
'INSERT INTO %I.order_items (order_id, item_name, quantity, unit_price, total_price)
SELECT $1, name, quantity, price, (quantity * price)
FROM jsonb_to_recordset($2) AS x(name text, quantity int, price numeric)',
v_schema
) USING v_order_id, (p_args->'items');
v_result := json_build_object('order_id', v_order_id, 'message', 'Order placed successfully');
-- D. RE-ORDER USUAL (One-sentence ordering)
-- Positioned BEFORE the ELSE catch-all to ensure reachability
WHEN 'reorder_usual' THEN
EXECUTE format(
'WITH last_order AS (
SELECT id, delivery_address, total_amount, order_type, payment_method
FROM %I.orders
WHERE customer_id = (SELECT id FROM %I.customers WHERE phone = $1)
ORDER BY created_at DESC LIMIT 1
)
SELECT json_build_object(
''items'', (SELECT json_agg(json_build_object(''name'', item_name, ''quantity'', quantity, ''price'', unit_price))
FROM %I.order_items WHERE order_id = last_order.id),
''address'', delivery_address,
''total'', total_amount,
''order_type'', order_type,
''payment_method'', payment_method
) FROM last_order', v_schema, v_schema, v_schema
) INTO v_result USING p_cust_phone;
-- Validation: Ensure items exist before duplicating
IF v_result IS NOT NULL AND (v_result->>'items') IS NOT NULL THEN
EXECUTE format(
'INSERT INTO %I.orders (customer_id, order_type, payment_method, total_amount, delivery_address, status)
SELECT id, $1, $2, $3, $4, ''pending'' FROM %I.customers WHERE phone = $5 RETURNING id',
v_schema, v_schema
) INTO v_order_id USING v_result->>'order_type', v_result->>'payment_method', (v_result->>'total')::numeric, v_result->>'address', p_cust_phone;
EXECUTE format(
'INSERT INTO %I.order_items (order_id, item_name, quantity, unit_price, total_price)
SELECT $1, (x->>''name''), (x->>''quantity'')::int, (x->>''price'')::numeric, ((x->>''quantity'')::int * (x->>''price'')::numeric)
FROM json_array_elements($2) AS x',
v_schema
) USING v_order_id, (v_result->'items');
v_result := json_build_object('order_id', v_order_id, 'message', 'Your usual order has been placed successfully.');
ELSE
v_result := json_build_object('error', 'No previous order found to replicate.');
END IF;
-- Catch-all for undefined tools
ELSE
v_result := json_build_object('error', 'Tool not implemented');
END CASE;
END IF;
PERFORM set_config('n8n.response', COALESCE(v_result, '[]'::json)::text, true);
END $BODY$
|