-- Mehrere Visitenkarten pro Person.
-- ====================================================================
-- Use-Case: Geschäftsführer Felix Zösch hat eine Karte als „GF
-- Stadtwerk", eine zweite als „BDEW-Ausschuss-Vertreter", eine dritte
-- als „VKU-Delegierter". Alle Karten haben eigenen Slug, eigene
-- Position, eigene Kontaktdaten — referenzieren aber dieselbe Person
-- (gleicher Name, gleiches Foto, gleicher akademischer Titel).
--
-- Migration:
-- 1. persons-Tabelle anlegen
-- 2. employees.person_id (nullable FK) ergänzen
-- 3. Backfill: pro existierendem Employee eine Person anlegen +
--    verknüpfen (id-mäßig 1:1, semantisch erstmal eindeutig)
-- 4. Spätere Mehrfach-Karten setzen denselben person_id auf weiteren
--    employees-Zeilen
--
-- Für die Rückwärtskompatibilität bleiben die name- und photo-Felder
-- in employees erhalten — sie sind die maßgeblichen Daten für die
-- jeweilige Karte. Person-Felder werden für „Person-Übersicht" im
-- Verzeichnis genutzt und können in v2 als Default-Quelle für neue
-- Karten dienen.

create table if not exists public.persons (
  id              uuid primary key default gen_random_uuid(),
  academic_title  text,
  first_name      text not null,
  last_name       text not null,
  photo_url       text,
  created_at      timestamptz not null default now(),
  updated_at      timestamptz not null default now()
);

create index if not exists persons_last_name_idx
  on public.persons (last_name, first_name);

drop trigger if exists trg_persons_updated_at on public.persons;
create trigger trg_persons_updated_at
  before update on public.persons
  for each row execute function public.set_updated_at();

alter table public.employees
  add column if not exists person_id uuid
    references public.persons(id) on delete set null;

create index if not exists employees_person_id_idx
  on public.employees (person_id);

-- Backfill: für jeden Employee ohne person_id eine Person anlegen
-- und verknüpfen.
do $$
declare
  e record;
  new_person_id uuid;
begin
  for e in
    select id, academic_title, first_name, last_name, photo_url
    from public.employees
    where person_id is null
  loop
    insert into public.persons (academic_title, first_name, last_name, photo_url)
      values (e.academic_title, e.first_name, e.last_name, e.photo_url)
      returning id into new_person_id;
    update public.employees
      set person_id = new_person_id
      where id = e.id;
  end loop;
end $$;

-- Anon-Zugriff: person_id darf gelesen werden, damit
-- "weitere Karten dieser Person"-Liste auf öffentlicher Seite
-- möglich ist.
grant select (person_id) on public.employees to anon;

-- persons-Tabelle: anon darf basics (name, foto) lesen — wird auf
-- der "weitere Karten"-Liste der öffentlichen Karte angezeigt.
alter table public.persons enable row level security;
create policy "public_read_persons"
  on public.persons for select
  to anon using (true);
revoke all on public.persons from anon;
grant select
  (id, academic_title, first_name, last_name, photo_url)
  on public.persons to anon;