LagoESP
4c1931cdf4
- Removed the `upsert` option from the file upload in `uploadDoc` function to prevent unintended overwrites. - Enhanced German translations in `i18n.js` for better clarity and consistency in the admin interface. - Added new CSS styles for link interactions to improve user experience in `styles.css`. - Updated Supabase SQL migration to grant additional permissions for anonymous users to insert and update storage objects, ensuring proper functionality during the booking flow. Co-authored-by: Copilot <copilot@github.com>
420 lines
16 KiB
PL/PgSQL
420 lines
16 KiB
PL/PgSQL
-- 07-sales-orders.sql
|
|
-- Sales Orders, customer private notes, lead IP tracking, attachment relations
|
|
|
|
alter table public.leads add column if not exists ip_address text default '';
|
|
alter table public.leads add column if not exists ip_country text default '';
|
|
|
|
create table if not exists public.sales_orders (
|
|
id uuid primary key default gen_random_uuid(),
|
|
customer_id uuid not null references public.customers(id) on delete cascade,
|
|
lead_id uuid not null references public.leads(id) on delete restrict,
|
|
order_number text not null default '',
|
|
private_notes text not null default '',
|
|
kaution_paid boolean not null default false,
|
|
rental_paid boolean not null default false,
|
|
rental_complete boolean not null default false,
|
|
kaution_paid_at timestamptz,
|
|
rental_paid_at timestamptz,
|
|
rental_complete_at timestamptz,
|
|
daily_subtotal integer not null default 0,
|
|
weekend_subtotal integer not null default 0,
|
|
subtotal_eur integer not null default 0,
|
|
vat_eur integer not null default 0,
|
|
total_eur integer not null default 0,
|
|
deposit_eur integer not null default 0,
|
|
total_days integer not null default 0,
|
|
weekday_count integer not null default 0,
|
|
weekend_day_count integer not null default 0,
|
|
date_from date,
|
|
date_to date,
|
|
vehicle_label text not null default '',
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now()
|
|
);
|
|
|
|
create index if not exists sales_orders_customer_idx on public.sales_orders (customer_id);
|
|
create index if not exists sales_orders_lead_idx on public.sales_orders (lead_id);
|
|
create index if not exists sales_orders_order_number on public.sales_orders (order_number);
|
|
|
|
drop trigger if exists sales_orders_touch on public.sales_orders;
|
|
create trigger sales_orders_touch
|
|
before update on public.sales_orders
|
|
for each row execute function public.tg_touch_updated_at();
|
|
|
|
alter table public.sales_orders enable row level security;
|
|
|
|
drop policy if exists "sales_orders_admin_select" on public.sales_orders;
|
|
drop policy if exists "sales_orders_admin_insert" on public.sales_orders;
|
|
drop policy if exists "sales_orders_admin_update" on public.sales_orders;
|
|
|
|
create policy "sales_orders_admin_select"
|
|
on public.sales_orders for select to authenticated using (true);
|
|
create policy "sales_orders_admin_insert"
|
|
on public.sales_orders for insert to authenticated with check (true);
|
|
create policy "sales_orders_admin_update"
|
|
on public.sales_orders for update to authenticated using (true) with check (true);
|
|
|
|
grant select, insert, update on public.sales_orders to authenticated;
|
|
grant all on public.sales_orders to service_role;
|
|
|
|
create table if not exists public.sales_order_attachments (
|
|
id uuid primary key default gen_random_uuid(),
|
|
sales_order_id uuid not null references public.sales_orders(id) on delete cascade,
|
|
bucket text not null default 'customer-documents',
|
|
file_path text not null,
|
|
file_name text not null default '',
|
|
mime_type text not null default 'application/octet-stream',
|
|
kind text not null default 'other' check (kind in ('id_document', 'income_proof', 'other')),
|
|
created_at timestamptz not null default now()
|
|
);
|
|
|
|
create index if not exists sales_order_attachments_so_idx on public.sales_order_attachments (sales_order_id);
|
|
|
|
alter table public.sales_order_attachments enable row level security;
|
|
|
|
drop policy if exists "so_attach_admin_all" on public.sales_order_attachments;
|
|
create policy "so_attach_admin_all"
|
|
on public.sales_order_attachments for all to authenticated
|
|
using (true) with check (true);
|
|
|
|
grant all on public.sales_order_attachments to authenticated;
|
|
grant all on public.sales_order_attachments to service_role;
|
|
|
|
alter table public.customers add column if not exists private_notes text not null default '';
|
|
create unique index if not exists customers_email_lower_unique on public.customers ((lower(email)));
|
|
|
|
alter table public.customer_attachments add column if not exists sales_order_id uuid references public.sales_orders(id) on delete set null;
|
|
create index if not exists customer_attachments_so_idx on public.customer_attachments (sales_order_id);
|
|
|
|
create or replace function public.qualify_lead(p_lead_id uuid, p_notes text default '')
|
|
returns public.customers
|
|
language plpgsql
|
|
security invoker
|
|
as $$
|
|
declare
|
|
v_lead public.leads;
|
|
v_customer public.customers;
|
|
v_sales_order public.sales_orders;
|
|
v_user uuid := auth.uid();
|
|
v_order_num text;
|
|
v_year integer;
|
|
v_count integer;
|
|
begin
|
|
select * into v_lead from public.leads where id = p_lead_id for update;
|
|
if not found then
|
|
raise exception 'lead % not found', p_lead_id;
|
|
end if;
|
|
|
|
if v_lead.status = 'qualified' then
|
|
select * into v_customer from public.customers where lower(email) = lower(v_lead.email) limit 1;
|
|
return v_customer;
|
|
end if;
|
|
|
|
update public.leads
|
|
set status = 'qualified',
|
|
is_active = false,
|
|
qualified_at = now(),
|
|
qualified_by = v_user,
|
|
admin_notes = coalesce(nullif(p_notes, ''), admin_notes)
|
|
where id = v_lead.id;
|
|
|
|
insert into public.customers (lead_id, name, email, phone, notes, created_by)
|
|
values (v_lead.id, v_lead.name, v_lead.email, v_lead.phone, coalesce(p_notes,''), v_user)
|
|
on conflict ((lower(email))) do update
|
|
set name = excluded.name,
|
|
phone = excluded.phone,
|
|
notes = case when excluded.notes <> '' then excluded.notes else public.customers.notes end,
|
|
updated_at = now()
|
|
returning * into v_customer;
|
|
|
|
v_year := extract(year from now())::integer;
|
|
select coalesce(count(*), 0) + 1 into v_count
|
|
from public.sales_orders
|
|
where extract(year from created_at)::integer = v_year;
|
|
v_order_num := 'SO-' || v_year || '-' || lpad(v_count::text, 4, '0');
|
|
|
|
insert into public.sales_orders (
|
|
customer_id, lead_id, order_number, private_notes,
|
|
daily_subtotal, weekend_subtotal, subtotal_eur, vat_eur, total_eur, deposit_eur,
|
|
total_days, weekday_count, weekend_day_count,
|
|
date_from, date_to, vehicle_label
|
|
) values (
|
|
v_customer.id, v_lead.id, v_order_num, coalesce(v_lead.admin_notes, ''),
|
|
coalesce(v_lead.daily_subtotal, 0), coalesce(v_lead.weekend_subtotal, 0),
|
|
coalesce(v_lead.subtotal_eur, 0), coalesce(v_lead.vat_eur, 0),
|
|
coalesce(v_lead.total_eur, 0), coalesce(v_lead.deposit_eur, 0),
|
|
coalesce(v_lead.total_days, 0), coalesce(v_lead.weekday_count, 0),
|
|
coalesce(v_lead.weekend_day_count, 0),
|
|
v_lead.date_from, v_lead.date_to, v_lead.vehicle_label
|
|
) returning * into v_sales_order;
|
|
|
|
insert into public.customer_attachments (customer_id, lead_id, sales_order_id, bucket, file_path, file_name, mime_type, kind, created_at)
|
|
select v_customer.id, la.lead_id, v_sales_order.id, la.bucket, la.file_path, la.file_name, la.mime_type, la.kind, la.created_at
|
|
from public.lead_attachments la
|
|
where la.lead_id = v_lead.id
|
|
and not exists (
|
|
select 1 from public.customer_attachments ca
|
|
where ca.customer_id = v_customer.id
|
|
and ca.file_path = la.file_path
|
|
);
|
|
|
|
insert into public.sales_order_attachments (sales_order_id, bucket, file_path, file_name, mime_type, kind, created_at)
|
|
select v_sales_order.id, la.bucket, la.file_path, la.file_name, la.mime_type, la.kind, la.created_at
|
|
from public.lead_attachments la
|
|
where la.lead_id = v_lead.id;
|
|
|
|
return v_customer;
|
|
end;
|
|
$$;
|
|
|
|
create or replace function public.sales_order_toggle_kaution(p_so_id uuid)
|
|
returns public.sales_orders
|
|
language plpgsql
|
|
security invoker
|
|
as $$
|
|
declare
|
|
v_so public.sales_orders;
|
|
begin
|
|
select * into v_so from public.sales_orders where id = p_so_id for update;
|
|
if not found then
|
|
raise exception 'sales order % not found', p_so_id;
|
|
end if;
|
|
|
|
if v_so.kaution_paid then
|
|
update public.sales_orders
|
|
set kaution_paid = false, kaution_paid_at = null, updated_at = now()
|
|
where id = p_so_id
|
|
returning * into v_so;
|
|
else
|
|
if v_so.rental_complete then
|
|
raise exception 'cannot set kaution paid after rental is complete';
|
|
end if;
|
|
update public.sales_orders
|
|
set kaution_paid = true, kaution_paid_at = now(), updated_at = now()
|
|
where id = p_so_id
|
|
returning * into v_so;
|
|
end if;
|
|
return v_so;
|
|
end;
|
|
$$;
|
|
|
|
create or replace function public.sales_order_toggle_rental(p_so_id uuid)
|
|
returns public.sales_orders
|
|
language plpgsql
|
|
security invoker
|
|
as $$
|
|
declare
|
|
v_so public.sales_orders;
|
|
begin
|
|
select * into v_so from public.sales_orders where id = p_so_id for update;
|
|
if not found then
|
|
raise exception 'sales order % not found', p_so_id;
|
|
end if;
|
|
|
|
if v_so.rental_paid then
|
|
update public.sales_orders
|
|
set rental_paid = false, rental_paid_at = null, updated_at = now()
|
|
where id = p_so_id
|
|
returning * into v_so;
|
|
else
|
|
if v_so.kaution_paid = false or v_so.rental_complete then
|
|
raise exception 'can only set rental paid after kaution is paid and before rental is complete';
|
|
end if;
|
|
update public.sales_orders
|
|
set rental_paid = true, rental_paid_at = now(), updated_at = now()
|
|
where id = p_so_id
|
|
returning * into v_so;
|
|
end if;
|
|
return v_so;
|
|
end;
|
|
$$;
|
|
|
|
create or replace function public.sales_order_toggle_complete(p_so_id uuid)
|
|
returns public.sales_orders
|
|
language plpgsql
|
|
security invoker
|
|
as $$
|
|
declare
|
|
v_so public.sales_orders;
|
|
begin
|
|
select * into v_so from public.sales_orders where id = p_so_id for update;
|
|
if not found then
|
|
raise exception 'sales order % not found', p_so_id;
|
|
end if;
|
|
|
|
if v_so.rental_complete then
|
|
update public.sales_orders
|
|
set rental_complete = false, rental_complete_at = null, updated_at = now()
|
|
where id = p_so_id
|
|
returning * into v_so;
|
|
else
|
|
if v_so.kaution_paid = false or v_so.rental_paid = false then
|
|
raise exception 'can only set rental complete after kaution and rental are paid';
|
|
end if;
|
|
update public.sales_orders
|
|
set rental_complete = true, rental_complete_at = now(), updated_at = now()
|
|
where id = p_so_id
|
|
returning * into v_so;
|
|
end if;
|
|
return v_so;
|
|
end;
|
|
$$;
|
|
|
|
create or replace function public.customer_update_private_notes(p_customer_id uuid, p_notes text)
|
|
returns void
|
|
language plpgsql
|
|
security invoker
|
|
as $$
|
|
begin
|
|
update public.customers
|
|
set private_notes = coalesce(p_notes, ''), updated_at = now()
|
|
where id = p_customer_id;
|
|
end;
|
|
$$;
|
|
|
|
create or replace function public.sales_order_update_private_notes(p_so_id uuid, p_notes text)
|
|
returns void
|
|
language plpgsql
|
|
security invoker
|
|
as $$
|
|
begin
|
|
update public.sales_orders
|
|
set private_notes = coalesce(p_notes, ''), updated_at = now()
|
|
where id = p_so_id;
|
|
end;
|
|
$$;
|
|
|
|
create or replace function public.sales_order_upload_attachment(
|
|
p_sales_order_id uuid,
|
|
p_file_path text,
|
|
p_file_name text,
|
|
p_mime_type text,
|
|
p_kind text default 'other'
|
|
)
|
|
returns uuid
|
|
language plpgsql
|
|
security definer
|
|
as $$
|
|
declare
|
|
v_id uuid;
|
|
begin
|
|
insert into public.sales_order_attachments (sales_order_id, bucket, file_path, file_name, mime_type, kind)
|
|
values (p_sales_order_id, 'customer-documents', p_file_path, p_file_name, p_mime_type, p_kind)
|
|
returning id into v_id;
|
|
return v_id;
|
|
end;
|
|
$$;
|
|
|
|
create or replace function public.create_lead(
|
|
p_name text,
|
|
p_email text,
|
|
p_phone text default '',
|
|
p_vehicle_id uuid default null,
|
|
p_vehicle_label text default '',
|
|
p_date_from date default null,
|
|
p_date_to date default null,
|
|
p_message text default '',
|
|
p_source text default 'website',
|
|
p_daily_subtotal integer default 0,
|
|
p_weekend_subtotal integer default 0,
|
|
p_subtotal_eur integer default 0,
|
|
p_vat_eur integer default 0,
|
|
p_total_eur integer default 0,
|
|
p_deposit_eur integer default 0,
|
|
p_total_days integer default 0,
|
|
p_weekday_count integer default 0,
|
|
p_weekend_day_count integer default 0,
|
|
p_ip_address text default '',
|
|
p_ip_country text default ''
|
|
)
|
|
returns uuid
|
|
language plpgsql
|
|
security definer
|
|
as $$
|
|
declare
|
|
v_lead_id uuid;
|
|
begin
|
|
insert into public.leads (
|
|
name, email, phone, vehicle_id, vehicle_label, date_from, date_to,
|
|
message, source,
|
|
daily_subtotal, weekend_subtotal, subtotal_eur, vat_eur, total_eur, deposit_eur,
|
|
total_days, weekday_count, weekend_day_count, ip_address, ip_country
|
|
) values (
|
|
p_name, p_email, p_phone, p_vehicle_id, p_vehicle_label, p_date_from, p_date_to,
|
|
p_message, p_source,
|
|
p_daily_subtotal, p_weekend_subtotal, p_subtotal_eur, p_vat_eur, p_total_eur, p_deposit_eur,
|
|
p_total_days, p_weekday_count, p_weekend_day_count, p_ip_address, p_ip_country
|
|
)
|
|
returning id into v_lead_id;
|
|
return v_lead_id;
|
|
end;
|
|
$$;
|
|
|
|
drop function if exists public.create_lead(
|
|
text, text, text, uuid, text, date, date, text, text
|
|
);
|
|
|
|
drop function if exists public.create_lead(
|
|
text, text, text, uuid, text, date, date, text, text,
|
|
integer, integer, integer, integer, integer, integer, integer, integer, integer
|
|
);
|
|
|
|
create or replace function public.create_lead(
|
|
p_name text,
|
|
p_email text,
|
|
p_phone text default '',
|
|
p_vehicle_id uuid default null,
|
|
p_vehicle_label text default '',
|
|
p_date_from date default null,
|
|
p_date_to date default null,
|
|
p_message text default '',
|
|
p_source text default 'website',
|
|
p_daily_subtotal integer default 0,
|
|
p_weekend_subtotal integer default 0,
|
|
p_subtotal_eur integer default 0,
|
|
p_vat_eur integer default 0,
|
|
p_total_eur integer default 0,
|
|
p_deposit_eur integer default 0,
|
|
p_total_days integer default 0,
|
|
p_weekday_count integer default 0,
|
|
p_weekend_day_count integer default 0,
|
|
p_ip_address text default '',
|
|
p_ip_country text default ''
|
|
)
|
|
returns uuid
|
|
language plpgsql
|
|
security definer
|
|
as $$
|
|
declare
|
|
v_lead_id uuid;
|
|
begin
|
|
insert into public.leads (
|
|
name, email, phone, vehicle_id, vehicle_label, date_from, date_to,
|
|
message, source,
|
|
daily_subtotal, weekend_subtotal, subtotal_eur, vat_eur, total_eur, deposit_eur,
|
|
total_days, weekday_count, weekend_day_count, ip_address, ip_country
|
|
) values (
|
|
p_name, p_email, p_phone, p_vehicle_id, p_vehicle_label, p_date_from, p_date_to,
|
|
p_message, p_source,
|
|
p_daily_subtotal, p_weekend_subtotal, p_subtotal_eur, p_vat_eur, p_total_eur, p_deposit_eur,
|
|
p_total_days, p_weekday_count, p_weekend_day_count, p_ip_address, p_ip_country
|
|
)
|
|
returning id into v_lead_id;
|
|
return v_lead_id;
|
|
end;
|
|
$$;
|
|
|
|
grant execute on function public.sales_order_toggle_kaution(uuid) to authenticated;
|
|
grant execute on function public.sales_order_toggle_rental(uuid) to authenticated;
|
|
grant execute on function public.sales_order_toggle_complete(uuid) to authenticated;
|
|
grant execute on function public.customer_update_private_notes(uuid, text) to authenticated;
|
|
grant execute on function public.sales_order_update_private_notes(uuid, text) to authenticated;
|
|
grant execute on function public.sales_order_upload_attachment(uuid, text, text, text, text) to authenticated;
|
|
grant execute on function public.create_lead(
|
|
text, text, text, uuid, text, date, date, text, text,
|
|
integer, integer, integer, integer, integer, integer, integer, integer, integer,
|
|
text, text
|
|
) to anon, authenticated, service_role;
|
|
|
|
notify pgrst, 'reload schema';
|