alpenwerk-hr/supabase/migrations/20260811100000_personnel_number_is_entered.sql

-- Die Personalnummer wird eingegeben, nicht vergeben.
--
-- Sie muss mit Loga und Interflex übereinstimmen. Eine von dieser Anwendung
-- selbst gezogene Nummer ist dort unbekannt, und die Person hätte in drei
-- Systemen zwei Nummern.
--
-- Zwei Dinge ändern sich dadurch:
--
-- 1. Die Identität fällt weg. `GENERATED ALWAYS` weist eigene Werte
--    ausdrücklich ab — deshalb brauchte der Import bisher OVERRIDING SYSTEM
--    VALUE.
-- 2. Die Eindeutigkeit muss ausdrücklich her. Bisher gab es **keine**: die
--    Identität verhinderte Doppelte nur als Nebenwirkung. Sobald der Wert von
--    aussen kommt, ist das die eigentliche Zusicherung — und sie fehlte.

-- Gegenprobe vor dem Umbau: was jetzt doppelt ist, liesse sich danach nicht
-- mehr eindeutig machen.
do $$
declare v_doppelt int;
begin
  select count(*) into v_doppelt from (
    select personnel_number from employees group by 1 having count(*) > 1
  ) x;
  if v_doppelt > 0 then
    raise exception '% Personalnummern kommen mehrfach vor — vor der Umstellung bereinigen.', v_doppelt;
  end if;
end;
$$;

alter table employees alter column personnel_number drop identity if exists;

alter table employees add constraint employees_personnel_number_key unique (personnel_number);

comment on column employees.personnel_number is
  'Wird eingegeben und muss mit Loga/Interflex übereinstimmen. Nicht automatisch vergeben.';

-- ═══ hire_employee nimmt die Nummer entgegen ═════════════════════
do $$
declare
  v_def text;
  v_neu text;
begin
  select pg_get_functiondef(p.oid) into v_def
    from pg_proc p join pg_namespace n on n.oid = p.pronamespace
   where n.nspname = 'public' and p.proname = 'hire_employee' limit 1;

  -- Pflichtprüfung direkt nach der Planstellenprüfung einhängen.
  v_neu := regexp_replace(
    v_def,
    '(if\s+v_position_id\s+is\s+null\s+then\s+raise\s+exception\s+''Es muss eine Planstelle angegeben werden\.'';\s+end\s+if;)',
$neu$\1

  if payload->>'personnel_number' is null or btrim(payload->>'personnel_number') = '' then
    raise exception 'Es muss eine Personalnummer angegeben werden.';
  end if;
  if exists (select 1 from employees where personnel_number = (payload->>'personnel_number')::int) then
    raise exception 'Die Personalnummer % ist bereits vergeben.', payload->>'personnel_number';
  end if;$neu$,
    'g'
  );
  if v_neu = v_def then
    raise exception 'Die Pflichtprüfung liess sich nicht einhängen — hire_employee blieb unverändert.';
  end if;
  v_def := v_neu;

  -- Und in die Einfügung aufnehmen. Die Spaltenliste beginnt mit
  -- first_name; davor kommt personnel_number, in beiden Listen an gleicher
  -- Stelle.
  v_neu := regexp_replace(v_def, 'insert\s+into\s+employees\s*\(\s*first_name,', 'insert into employees (' || chr(10) || '    personnel_number, first_name,', 'g');
  if v_neu = v_def then
    raise exception 'Die Spaltenliste liess sich nicht ergänzen.';
  end if;
  v_def := v_neu;

  v_neu := regexp_replace(v_def, 'values\s*\(\s*payload->>''first_name'',', 'values (' || chr(10) || '    (payload->>''personnel_number'')::int, payload->>''first_name'',', 'g');
  if v_neu = v_def then
    raise exception 'Die Werteliste liess sich nicht ergänzen.';
  end if;

  -- OVERRIDING SYSTEM VALUE gibt es nur für Identitätsspalten; nach dem
  -- Wegfall wäre es ein Fehler. Der Import setzt es selbst nicht mehr, hier
  -- steht es vorsorglich, falls eine ältere Fassung es doch trägt.
  v_neu := regexp_replace(v_neu, '\s+overriding\s+system\s+value', '', 'gi');

  execute v_neu;
end;
$$;

-- ═══ Gegenprobe ══════════════════════════════════════════════════
do $$
declare
  v_ist_identitaet text;
  v_def text;
begin
  select is_identity into v_ist_identitaet
    from information_schema.columns
   where table_name = 'employees' and column_name = 'personnel_number';
  if v_ist_identitaet <> 'NO' then
    raise exception 'personnel_number ist weiterhin eine Identitätsspalte.';
  end if;

  if not exists (
    select 1 from pg_constraint
     where conrelid = 'employees'::regclass and contype = 'u'
       and pg_get_constraintdef(oid) = 'UNIQUE (personnel_number)'
  ) then
    raise exception 'Die Eindeutigkeit auf personnel_number fehlt.';
  end if;

  select pg_get_functiondef(p.oid) into v_def
    from pg_proc p join pg_namespace n on n.oid = p.pronamespace
   where n.nspname = 'public' and p.proname = 'hire_employee' limit 1;
  if v_def not like '%bereits vergeben%' then
    raise exception 'hire_employee() prüft die Personalnummer nicht.';
  end if;
  if v_def ilike '%overriding system value%' then
    raise exception 'hire_employee() enthält weiterhin OVERRIDING SYSTEM VALUE.';
  end if;
end;
$$;