mc_cars_gmbh_infraestructure/supabase/migrations/08-backend-pricing-and-security.sql

-- 08-backend-pricing-and-security.sql
-- 1. Server-side price calculation RPC (read-only, callable by anon for display)
-- 2. Refactored create_lead RPC that computes prices internally (no price params from frontend)
-- 3. Unique constraint on lead_attachments to enforce max 1 id_document + 1 income_proof per lead

-- =============================================================================
-- 1. calculate_price RPC
-- =============================================================================
create or replace function public.calculate_price(
    p_vehicle_id uuid,
    p_date_from date,
    p_date_to date
)
returns jsonb
language plpgsql
stable
security definer
as $$
declare
    v_vehicle record;
    v_total_days integer;
    v_weekend_days integer;
    v_weekdays integer;
    v_daily_subtotal integer;
    v_weekend_subtotal integer;
    v_subtotal_eur integer;
    v_vat_eur integer;
    v_total_eur integer;
    v_deposit_eur integer;
    v_cur date;
    v_dow integer;
begin
    if p_vehicle_id is null or p_date_from is null or p_date_to is null then
        raise exception 'vehicle_id, date_from and date_to are required';
    end if;

    if p_date_to <= p_date_from then
        raise exception 'date_to must be after date_from';
    end if;

    select daily_price_eur, weekend_price_eur, kaution_eur, max_daily_km, max_km_weekend
      into v_vehicle
      from public.vehicles
     where id = p_vehicle_id;

    if not found then
        raise exception 'Vehicle not found';
    end if;

    -- Count days
    v_total_days := (p_date_to - p_date_from);
    v_weekend_days := 0;
    v_cur := p_date_from;
    while v_cur < p_date_to loop
        v_dow := extract(isodow from v_cur); -- 6=Sat, 7=Sun
        if v_dow in (6, 7) then
            v_weekend_days := v_weekend_days + 1;
        end if;
        v_cur := v_cur + 1;
    end loop;
    v_weekdays := v_total_days - v_weekend_days;

    -- Calculate prices
    v_daily_subtotal := v_weekdays * v_vehicle.daily_price_eur;
    v_weekend_subtotal := v_weekend_days * (case when v_vehicle.weekend_price_eur > 0 then v_vehicle.weekend_price_eur else v_vehicle.daily_price_eur end);
    v_subtotal_eur := v_daily_subtotal + v_weekend_subtotal;
    v_vat_eur := round(v_subtotal_eur * 0.20);
    v_total_eur := v_subtotal_eur + v_vat_eur;
    v_deposit_eur := coalesce(nullif(v_vehicle.kaution_eur, 0), 5000);

    return jsonb_build_object(
        'total_days', v_total_days,
        'weekday_count', v_weekdays,
        'weekend_day_count', v_weekend_days,
        'daily_subtotal', v_daily_subtotal,
        'weekend_subtotal', v_weekend_subtotal,
        'subtotal_eur', v_subtotal_eur,
        'vat_eur', v_vat_eur,
        'total_eur', v_total_eur,
        'deposit_eur', v_deposit_eur,
        'daily_price_eur', v_vehicle.daily_price_eur,
        'weekend_price_eur', (case when v_vehicle.weekend_price_eur > 0 then v_vehicle.weekend_price_eur else v_vehicle.daily_price_eur end),
        'max_daily_km', coalesce(v_vehicle.max_daily_km, 150),
        'max_km_weekend', coalesce(v_vehicle.max_km_weekend, v_vehicle.max_daily_km, 150)
    );
end;
$$;

grant execute on function public.calculate_price(uuid, date, date) to anon, authenticated, service_role;

-- =============================================================================
-- 2. Refactored create_lead – computes prices server-side, no price params
-- =============================================================================

-- Drop old overloaded signatures
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
);
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,
    text, text
);

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_ip_address text default '',
    p_ip_country text default ''
)
returns uuid
language plpgsql
security definer
as $$
declare
    v_lead_id uuid;
    v_vehicle record;
    v_total_days integer := 0;
    v_weekend_days integer := 0;
    v_weekdays integer := 0;
    v_daily_subtotal integer := 0;
    v_weekend_subtotal integer := 0;
    v_subtotal_eur integer := 0;
    v_vat_eur integer := 0;
    v_total_eur integer := 0;
    v_deposit_eur integer := 0;
    v_cur date;
    v_dow integer;
begin
    -- Compute prices server-side if vehicle and dates are provided
    if p_vehicle_id is not null and p_date_from is not null and p_date_to is not null and p_date_to > p_date_from then
        select daily_price_eur, weekend_price_eur, kaution_eur
          into v_vehicle
          from public.vehicles
         where id = p_vehicle_id;

        if found then
            v_total_days := (p_date_to - p_date_from);
            v_cur := p_date_from;
            while v_cur < p_date_to loop
                v_dow := extract(isodow from v_cur);
                if v_dow in (6, 7) then
                    v_weekend_days := v_weekend_days + 1;
                end if;
                v_cur := v_cur + 1;
            end loop;
            v_weekdays := v_total_days - v_weekend_days;

            v_daily_subtotal := v_weekdays * v_vehicle.daily_price_eur;
            v_weekend_subtotal := v_weekend_days * (case when v_vehicle.weekend_price_eur > 0 then v_vehicle.weekend_price_eur else v_vehicle.daily_price_eur end);
            v_subtotal_eur := v_daily_subtotal + v_weekend_subtotal;
            v_vat_eur := round(v_subtotal_eur * 0.20);
            v_total_eur := v_subtotal_eur + v_vat_eur;
            v_deposit_eur := coalesce(nullif(v_vehicle.kaution_eur, 0), 5000);
        end if;
    end if;

    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,
        v_daily_subtotal, v_weekend_subtotal, v_subtotal_eur, v_vat_eur, v_total_eur, v_deposit_eur,
        v_total_days, v_weekdays, v_weekend_days, p_ip_address, p_ip_country
    )
    returning id into v_lead_id;
    return v_lead_id;
end;
$$;

grant execute on function public.create_lead(
    text, text, text, uuid, text, date, date, text, text, text, text
) to anon, authenticated, service_role;

-- =============================================================================
-- 3. Enforce max 1 id_document + 1 income_proof per lead
-- =============================================================================

-- Unique partial index: only one 'id_document' per lead
drop index if exists lead_attachments_unique_id_document;
create unique index lead_attachments_unique_id_document
    on public.lead_attachments (lead_id)
    where kind = 'id_document';

-- Unique partial index: only one 'income_proof' per lead
drop index if exists lead_attachments_unique_income_proof;
create unique index lead_attachments_unique_income_proof
    on public.lead_attachments (lead_id)
    where kind = 'income_proof';

notify pgrst, 'reload schema';