-- Empfangsbuch + GoBD-Audit-Trail.
-- ====================================================================
-- 1. card_leads.kiosk_id ergaenzen — welcher Kiosk hat den Eintrag erzeugt
--    (NULL = ueber /admin/empfang von einem Admin-User).
-- 2. reception_visit_audit als append-only Event-Log: pro Besuch wird
--    jedes Ereignis (created / checked_out / mail_sent / mail_failed /
--    deleted / updated) als eigene Zeile geschrieben.
--    Loeschen / Aendern von card_leads zerstoert das Audit-Log NICHT
--    (ON DELETE SET NULL auf lead_id; payload behaelt Snapshot).
-- 3. DB-Trigger verbietet UPDATE und DELETE auf der Audit-Tabelle —
--    GoBD-Anforderung "Unveraenderlichkeit nach Abschluss".

-- 1) Kiosk-Referenz auf card_leads
alter table public.card_leads
  add column if not exists kiosk_id uuid
  references public.reception_kiosks(id) on delete set null;

create index if not exists card_leads_kiosk_idx
  on public.card_leads (kiosk_id, created_at desc)
  where kiosk_id is not null;

-- 2) Audit-Tabelle
create table if not exists public.reception_visit_audit (
  id           uuid primary key default gen_random_uuid(),
  lead_id      uuid references public.card_leads(id) on delete set null,
  occurred_at  timestamptz not null default now(),
  event_kind   text not null check (event_kind in (
    'created', 'checked_out', 'mail_sent', 'mail_failed',
    'updated', 'deleted'
  )),
  actor_kind   text not null check (actor_kind in ('admin','kiosk','system')),
  actor_id     uuid,
  -- Vollstaendiger Snapshot der relevanten Daten, damit der Audit-Eintrag
  -- auch nach Loeschung des urspruenglichen Leads aussagekraeftig bleibt.
  -- Felder typischerweise: visitor_first_name, visitor_last_name,
  -- visitor_company, visitor_email, visitor_phone, employee_id,
  -- employee_name, reason, source, kiosk_id, mail_to, error.
  payload      jsonb not null default '{}'::jsonb
);

create index if not exists reception_visit_audit_occurred_idx
  on public.reception_visit_audit (occurred_at desc);

create index if not exists reception_visit_audit_lead_idx
  on public.reception_visit_audit (lead_id)
  where lead_id is not null;

create index if not exists reception_visit_audit_kind_idx
  on public.reception_visit_audit (event_kind, occurred_at desc);

-- 3) Unveraenderlichkeit: UPDATE und DELETE auf der Audit-Tabelle blocken.
create or replace function public.reject_reception_audit_modify()
returns trigger as $$
begin
  raise exception 'reception_visit_audit ist append-only — % nicht erlaubt', tg_op;
end;
$$ language plpgsql;

drop trigger if exists no_update_reception_audit on public.reception_visit_audit;
create trigger no_update_reception_audit
  before update on public.reception_visit_audit
  for each row execute function public.reject_reception_audit_modify();

drop trigger if exists no_delete_reception_audit on public.reception_visit_audit;
create trigger no_delete_reception_audit
  before delete on public.reception_visit_audit
  for each row execute function public.reject_reception_audit_modify();

alter table public.reception_visit_audit enable row level security;
-- Default deny — Lesezugriff nur ueber Service-Role (Admin-Pfade).