-- Organigramm-Modul + Modul-Toggle.
-- ====================================================================
-- Generisches Datenmodell, das verschiedene Compliance-Regelwerke
-- (TSM, ISO 9001, DSGVO, …) abbilden kann. Stellen werden von Personen
-- getrennt, damit Vakanzen ("N.N.") und Personalwechsel ohne Layout-
-- Bruch funktionieren.
--
-- Tabellen:
--   org_units             — Sparte/Bereich (Strom, Gas, Finanzen, …)
--   positions             — Stelle in der Hierarchie
--   position_assignments  — Person ⇄ Stelle, mit Zeitraum
--   deputies              — Stelle ⇄ Stelle (Vertretung), mit Zeitraum
--   external_parties      — Fremdfirma / Behörde / Dienstleister
--   service_relations     — Externe ⇄ Pflichtbereich
--
-- Erweiterung an site_settings:
--   enabled_modules       — text[]: aktivierte Produkt-Module
--   organigram_visibility — public | internal | trusted
--
-- RLS-Modell: Tabellen sind RLS-aktiv, KEINE anon-Policies. Reads
-- laufen ausschließlich über service_role aus der App. Die Sichtbarkeits-
-- Logik (public/internal/trusted) prüft der App-Code, nicht die DB.

-- --------------------------------------------------------------------
-- 1) Module-Toggle + Organigramm-Sichtbarkeit auf site_settings
-- --------------------------------------------------------------------

alter table public.site_settings
  add column if not exists enabled_modules text[] not null
    default array['business_cards']::text[];

alter table public.site_settings
  add column if not exists organigram_visibility text not null
    default 'internal';

-- Sichtbarkeitswerte einschränken (idempotent)
alter table public.site_settings
  drop constraint if exists site_settings_organigram_visibility_check;
alter table public.site_settings
  add constraint site_settings_organigram_visibility_check
    check (organigram_visibility in ('public', 'internal', 'trusted'));

-- --------------------------------------------------------------------
-- 2) org_units — Sparte / Bereich (Hierarchie auch innerhalb möglich)
-- --------------------------------------------------------------------

create table if not exists public.org_units (
  id              uuid primary key default gen_random_uuid(),
  parent_id       uuid references public.org_units(id) on delete restrict,
  slug            text not null unique,
  name            text not null,
  description     text,
  sort_order      integer not null default 0,
  created_at      timestamptz not null default now(),
  updated_at      timestamptz not null default now(),
  constraint org_units_no_self_parent check (parent_id is null or parent_id <> id)
);

create index if not exists org_units_parent_id_idx
  on public.org_units (parent_id);

-- --------------------------------------------------------------------
-- 3) positions — Stelle (entkoppelt von Person)
-- --------------------------------------------------------------------

create table if not exists public.positions (
  id              uuid primary key default gen_random_uuid(),
  parent_id       uuid references public.positions(id) on delete restrict,
  org_unit_id     uuid references public.org_units(id) on delete set null,
  name            text not null,
  short_name      text,
  description     text,
  -- Anzeigewert wenn keine Person zugewiesen ist (Default: 'N.N.').
  vacant_label    text not null default 'N.N.',
  sort_order      integer not null default 0,
  created_at      timestamptz not null default now(),
  updated_at      timestamptz not null default now(),
  constraint positions_no_self_parent check (parent_id is null or parent_id <> id)
);

create index if not exists positions_parent_id_idx
  on public.positions (parent_id);
create index if not exists positions_org_unit_id_idx
  on public.positions (org_unit_id);

-- --------------------------------------------------------------------
-- 4) position_assignments — Person sitzt auf Stelle (mit Zeitraum)
-- --------------------------------------------------------------------

create table if not exists public.position_assignments (
  id              uuid primary key default gen_random_uuid(),
  employee_id     uuid not null references public.employees(id) on delete cascade,
  position_id     uuid not null references public.positions(id) on delete cascade,
  -- Optionaler Zusatz, z.B. "kommissarisch", "i.V."
  role_label      text,
  is_primary      boolean not null default false,
  valid_from      date not null default current_date,
  valid_to        date,
  created_at      timestamptz not null default now(),
  updated_at      timestamptz not null default now(),
  constraint position_assignments_valid_range
    check (valid_to is null or valid_to >= valid_from)
);

create index if not exists position_assignments_employee_id_idx
  on public.position_assignments (employee_id);
create index if not exists position_assignments_position_id_idx
  on public.position_assignments (position_id);
-- Schnelle "wer ist gerade aktiv auf Stelle X"-Queries
create index if not exists position_assignments_active_idx
  on public.position_assignments (position_id, valid_from, valid_to);

-- --------------------------------------------------------------------
-- 5) deputies — Stelle wird vertreten von Stelle (mit Zeitraum)
-- --------------------------------------------------------------------

create table if not exists public.deputies (
  id                uuid primary key default gen_random_uuid(),
  position_id       uuid not null references public.positions(id) on delete cascade,
  deputy_position_id uuid not null references public.positions(id) on delete cascade,
  -- Reihenfolge bei mehreren Vertretungen (1. Vertretung, 2. Vertretung, …)
  sort_order        integer not null default 0,
  valid_from        date not null default current_date,
  valid_to          date,
  created_at        timestamptz not null default now(),
  constraint deputies_no_self check (position_id <> deputy_position_id),
  constraint deputies_valid_range
    check (valid_to is null or valid_to >= valid_from)
);

create index if not exists deputies_position_id_idx
  on public.deputies (position_id);
create index if not exists deputies_deputy_position_id_idx
  on public.deputies (deputy_position_id);

-- --------------------------------------------------------------------
-- 6) external_parties — Fremdfirma / Behörde / Dienstleister
-- --------------------------------------------------------------------

create table if not exists public.external_parties (
  id              uuid primary key default gen_random_uuid(),
  name            text not null,
  -- Art der Partei: dienstleister, behoerde, kooperationspartner, sonstige
  kind            text not null default 'dienstleister',
  contact_name    text,
  contact_email   text,
  contact_phone   text,
  website         text,
  address         text,
  notes           text,
  sort_order      integer not null default 0,
  created_at      timestamptz not null default now(),
  updated_at      timestamptz not null default now(),
  constraint external_parties_kind_check
    check (kind in ('dienstleister', 'behoerde', 'kooperationspartner', 'sonstige'))
);

-- --------------------------------------------------------------------
-- 7) service_relations — Externe deckt Pflichtbereich ab
-- --------------------------------------------------------------------

create table if not exists public.service_relations (
  id                uuid primary key default gen_random_uuid(),
  external_party_id uuid not null references public.external_parties(id) on delete cascade,
  -- Polymorphe Referenz: scope_kind = 'org_unit' | 'position' | 'global'
  scope_kind        text not null,
  scope_id          uuid,
  -- Klartext, z.B. "Brandschutzbeauftragter", "Betriebsarzt", "Personalwesen"
  service_label     text not null,
  description       text,
  valid_from        date not null default current_date,
  valid_to          date,
  created_at        timestamptz not null default now(),
  constraint service_relations_scope_kind_check
    check (scope_kind in ('org_unit', 'position', 'global')),
  constraint service_relations_scope_id_consistency
    check (
      (scope_kind = 'global' and scope_id is null)
      or (scope_kind in ('org_unit', 'position') and scope_id is not null)
    ),
  constraint service_relations_valid_range
    check (valid_to is null or valid_to >= valid_from)
);

create index if not exists service_relations_external_party_id_idx
  on public.service_relations (external_party_id);
create index if not exists service_relations_scope_idx
  on public.service_relations (scope_kind, scope_id);

-- --------------------------------------------------------------------
-- 8) Trigger: updated_at automatisch aktualisieren
-- --------------------------------------------------------------------

create or replace function public.set_updated_at()
returns trigger as $$
begin
  new.updated_at = now();
  return new;
end;
$$ language plpgsql;

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

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

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

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

-- --------------------------------------------------------------------
-- 9) RLS — alle neuen Tabellen sind defensive
-- --------------------------------------------------------------------
-- Reads laufen ausschließlich über service_role (Admin + öffentliche
-- Org-Page checken Visibility selbst). KEINE anon-Policies.

alter table public.org_units             enable row level security;
alter table public.positions             enable row level security;
alter table public.position_assignments  enable row level security;
alter table public.deputies              enable row level security;
alter table public.external_parties      enable row level security;
alter table public.service_relations     enable row level security;