-- "Angehörige" — dependents/family members tracked per employee (spouse,
-- children, ...), relevant for payroll/insurance. One employee can have
-- several. Editing is add/remove only — fix a mistaken entry by deleting
-- and re-adding it, same as "Position löschen" — no in-place edit RPC.

create table employee_dependents (
  id uuid primary key default gen_random_uuid(),
  employee_id uuid not null references employees(id) on delete cascade,
  first_name text not null,
  last_name text not null,
  relationship text not null check (relationship in ('Ehepartner:in', 'Lebenspartner:in', 'Kind', 'Sonstige')),
  sv_nummer text,
  birth_date date not null,
  created_at timestamptz not null default now()
);
create index on employee_dependents (employee_id);

-- RLS only narrows what an already-GRANTed role may do; the "grant all ...
-- to anon, authenticated, service_role" default privilege (see
-- 20260714120500_default_grants.sql) already covers this new table.
alter table employee_dependents enable row level security;
create policy "employee_dependents_hr_all" on employee_dependents for all
  using (is_hr_user()) with check (is_hr_user());

-- ── Angehörige:n hinzufügen ───────────────────────────────────────────
create or replace function add_employee_dependent(payload jsonb)
returns uuid language plpgsql as $$
declare
  v_id uuid;
  v_employee_id uuid := (payload->>'employee_id')::uuid;
  v_employee_name text;
begin
  perform require_hr_admin();
  select first_name || ' ' || last_name into v_employee_name from employees where id = v_employee_id;
  if not found then
    raise exception 'Mitarbeiter:in nicht gefunden.';
  end if;

  insert into employee_dependents (employee_id, first_name, last_name, relationship, sv_nummer, birth_date)
    values (
      v_employee_id, payload->>'first_name', payload->>'last_name', payload->>'relationship',
      nullif(payload->>'sv_nummer', ''), (payload->>'birth_date')::date
    )
    returning id into v_id;

  insert into audit_log (actor_user_id, actor_name, action, target_label, target_employee_id, details)
    values (
      auth.uid(), current_actor_name(), 'Angehörige:r hinzugefügt', v_employee_name, v_employee_id,
      (payload->>'first_name') || ' ' || (payload->>'last_name') || ' (' || (payload->>'relationship') || ')'
    );

  return v_id;
end;
$$;

-- ── Angehörige:n entfernen ────────────────────────────────────────────
create or replace function delete_employee_dependent(payload jsonb)
returns void language plpgsql as $$
declare
  v_dep employee_dependents%rowtype;
  v_employee_name text;
begin
  perform require_hr_admin();
  select * into v_dep from employee_dependents where id = (payload->>'dependent_id')::uuid;
  if not found then
    raise exception 'Angehörige:r nicht gefunden.';
  end if;
  select first_name || ' ' || last_name into v_employee_name from employees where id = v_dep.employee_id;

  delete from employee_dependents where id = v_dep.id;

  insert into audit_log (actor_user_id, actor_name, action, target_label, target_employee_id, details)
    values (
      auth.uid(), current_actor_name(), 'Angehörige:r entfernt', v_employee_name, v_dep.employee_id,
      v_dep.first_name || ' ' || v_dep.last_name || ' (' || v_dep.relationship || ')'
    );
end;
$$;