RAPPORT/supabase/migrations/0001_initial.sql

-- ============================================================================
-- RAPPORT — Initial Migration (Draft v1)
-- ============================================================================
-- Zweck: Übersetzt das localStorage-Schema (studio_data_v1) nach Postgres,
--        Multi-Tenant von Anfang an (jede Tabelle hat studio_id),
--        Auth via Supabase (auth.users), Files via Supabase Storage.
--
-- Stand: Draft auf Basis von constants.js + View-Audit (Projects, Quotes,
--        Invoices, Expenses, Employees, Persons, Protocols, Time).
--        Noch NICHT auf eine echte Supabase-Instanz angewendet.
--
-- Konventionen:
--   - snake_case für Spalten, camelCase-Felder aus dem Frontend werden
--     beim Adapter-Mapping übersetzt (z.B. ferienWochen → ferien_wochen).
--   - status/kind/mode als CHECK-Constraints (statt Postgres-ENUM), weil
--     leichter zu erweitern.
--   - Volatile / formverteilte Strukturen als JSONB (z.B. quote.sia,
--     protocol.participants), klassisch normalisiert wo joinable.
--   - RLS-Policies am Ende; bis dahin sind Tabellen offen — für Self-Hosted
--     Supabase muss `alter table … enable row level security` aktiv sein.
-- ============================================================================

-- ─── EXTENSIONS ────────────────────────────────────────────────────────────
create extension if not exists "pgcrypto";   -- gen_random_uuid()
create extension if not exists "citext";     -- case-insensitive email

-- ════════════════════════════════════════════════════════════════════════════
-- TENANT-LAYER: Studios + Membership
-- ════════════════════════════════════════════════════════════════════════════

create table studios (
  id          uuid primary key default gen_random_uuid(),
  name        text not null,
  slug        text unique not null,
  created_at  timestamptz not null default now()
);

-- Verlinkung User (Supabase auth.users) ↔ Studio mit Rolle pro Studio
create table studio_members (
  studio_id      uuid not null references studios(id) on delete cascade,
  user_id        uuid not null references auth.users(id) on delete cascade,
  app_role_id    text,                                -- FK später, app_roles ist studio-spezifisch
  active         boolean not null default true,
  joined_at      timestamptz not null default now(),
  primary key (studio_id, user_id)
);

-- Profil-Erweiterung zu auth.users (Anzeigename etc.) — global, nicht studio-spezifisch
create table profiles (
  id            uuid primary key references auth.users(id) on delete cascade,
  username      citext unique not null,
  display_name  text not null,
  created_at    timestamptz not null default now()
);

-- ════════════════════════════════════════════════════════════════════════════
-- STUDIO-WEITE STAMMDATEN (Settings, Rollen, Templates)
-- ════════════════════════════════════════════════════════════════════════════

-- Eine Zeile pro Studio (ersetzt das Singleton-`settings`-Objekt)
create table studio_settings (
  studio_id              uuid primary key references studios(id) on delete cascade,
  name                   text not null default 'Mein Studio',
  address                text,
  street                 text, zip text, city text, country text default 'CH',
  email                  citext, phone text,
  iban                   text,
  iban_type              text default 'qr' check (iban_type in ('qr','normal')),
  mwst_nr                text,
  mwst_rate              numeric(5,2) default 8.1,
  default_hourly_rate    numeric(10,2) default 120,
  default_wochenstunden  numeric(5,2) default 35,
  default_ferien_wochen  numeric(4,1) default 5,

  -- Volatile / formverteilte Konfig als JSONB:
  formats                jsonb not null default '{}'::jsonb,  -- projectNumberFormat, invoiceNumberFormat, …
  page_margins           jsonb not null default '{}'::jsonb,  -- pageMarginTop/Bottom/Left/Right
  ui                     jsonb not null default '{}'::jsonb,  -- autoPrint, logoSize, qrNewPage, pdfNameFormat
  protokoll_type_abbr    jsonb not null default '{}'::jsonb,  -- {"Bausitzung":"BS", …}

  closed_months          int[] default '{}',
  block_mai_tag          boolean default true,
  setup_completed        boolean default false,
  logo_url               text,                                -- Supabase Storage Pfad
  updated_at             timestamptz not null default now()
);

-- Rate-Profile (PL/TS/BL/AS) — pro Studio, weil Stundensätze unterschiedlich
create table studio_roles (
  studio_id  uuid not null references studios(id) on delete cascade,
  id         text not null,                          -- "PL", "TS", … (innerhalb Studio eindeutig)
  label      text not null,
  rate       numeric(10,2) not null default 0,
  sort       int default 0,
  primary key (studio_id, id)
);

-- App-Rollen (Permissions/Dashboard-Zuordnung) — pro Studio
create table app_roles (
  studio_id              uuid not null references studios(id) on delete cascade,
  id                     text not null,              -- "r-admin", "r-projektleiter", …
  name                   text not null,
  permissions            text[],                     -- null = alle; sonst ["dashboard","projects",…]
  dashboard_template_id  text,
  primary key (studio_id, id)
);

create table dashboard_templates (
  studio_id  uuid not null references studios(id) on delete cascade,
  id         text not null,                          -- "tpl-admin", …
  name       text not null,
  is_public  boolean default true,
  layout     jsonb not null,                         -- Row-/Widget-Struktur
  primary key (studio_id, id)
);

alter table app_roles
  add constraint app_roles_dashboard_fk
  foreign key (studio_id, dashboard_template_id)
  references dashboard_templates(studio_id, id) on delete set null;

alter table studio_members
  add constraint studio_members_role_fk
  foreign key (studio_id, app_role_id)
  references app_roles(studio_id, id) on delete set null;

-- Absenz-Typen (Krankheit/Unfall/…) — pro Studio
create table absence_types (
  studio_id  uuid not null references studios(id) on delete cascade,
  id         text not null,                          -- "krankheit", "unfall", …
  label      text not null,
  color      text,
  primary key (studio_id, id)
);

-- Brieftemplates ("Offerte", "Zahlungserinnerung") — pro Studio
create table letter_templates (
  studio_id  uuid not null references studios(id) on delete cascade,
  id         text not null,                          -- "offer", "reminder"
  name       text not null,
  body       text not null,
  primary key (studio_id, id)
);

-- Feiertage — pro Studio (kantonal-spezifisch)
create table holidays (
  studio_id  uuid not null references studios(id) on delete cascade,
  date       date not null,
  label      text not null,
  half_day   boolean default false,
  primary key (studio_id, date)
);

-- ════════════════════════════════════════════════════════════════════════════
-- PERSONEN (Kunden + Partner vereint, seit v0.5)
-- ────────────────────────────────────────────────────────────────────────────
-- Zwei Modi pro Person:
--   a) Studio-lokal: studio_id IS NOT NULL  → klassisch pro Studio
--   b) Geteilt:      studio_id IS NULL      → lebt in person_studio_links,
--                                             sichtbar in allen verlinkten Studios
-- Default ist (a). Umstellung auf (b) ist eine User-Aktion in Stammdaten.
-- ════════════════════════════════════════════════════════════════════════════

create table persons (
  id                uuid primary key default gen_random_uuid(),
  studio_id         uuid references studios(id) on delete cascade,  -- NULL = geteilt
  name              text not null,
  person_type       text,                            -- Planer-Typ: "Elektroplaner", "HLKSE-Planer", …
  is_auftraggeber   boolean not null default false,
  is_partner        boolean not null default false,
  street            text, zip text, city text, country text default 'CH',
  email             citext, phone text, website text,
  note              text,
  contacts          jsonb not null default '[]'::jsonb,  -- [{id,name,position,phone,email}]
  honorar_offers    jsonb not null default '[]'::jsonb,
  created_at        timestamptz not null default now()
  -- updated_at + created_by/updated_by werden im Audit-Block am Ende ergänzt
);
create index on persons (studio_id);

-- Sichtbarkeit für geteilte Personen (studio_id IS NULL).
-- Nur relevant, wenn ein User die Person später "globalisiert".
create table person_studio_links (
  person_id  uuid not null references persons(id) on delete cascade,
  studio_id  uuid not null references studios(id) on delete cascade,
  primary_studio boolean default false,             -- in welchem Studio wurde sie ursprünglich angelegt
  linked_at  timestamptz not null default now(),
  primary key (person_id, studio_id)
);
create index on person_studio_links (studio_id);

-- ════════════════════════════════════════════════════════════════════════════
-- PROJEKTE
-- ════════════════════════════════════════════════════════════════════════════

create table projects (
  id                 uuid primary key default gen_random_uuid(),
  studio_id          uuid not null references studios(id) on delete cascade,
  number             text,                            -- "2025/03" via Format-Template
  name               text not null,
  client_id          uuid references persons(id) on delete set null,
  category           text,                            -- "Wettbewerb", "Direktauftrag", … (siehe constants.PROJECT_TYPES)
  billing_type       text check (billing_type in ('stundensatz','pauschal')),
  hourly_rate        numeric(10,2),
  budget             numeric(14,2),
  budget_hours       numeric(10,2),
  status             text default 'aktiv' check (status in ('aktiv','pausiert','abgeschlossen')),
  description        text,
  start_date         date,

  -- Aktivierte SIA-Phasen als Array (z.B. ["31","32","41","51","52","53"])
  enabled_phases     text[] not null default '{}',

  -- Komplexe / formverteilte Strukturen als JSONB (siehe View-Audit):
  positions          jsonb not null default '[]'::jsonb,    -- [{phaseId, …}]
  custom_phases      jsonb not null default '[]'::jsonb,    -- [{id, label}]
  project_contacts   jsonb not null default '[]'::jsonb,    -- [{contactId, personIds[]}]
  internal_members   jsonb not null default '[]'::jsonb,    -- [{userId, role, …}]

  created_at         timestamptz not null default now(),
  unique (studio_id, number)
);
create index on projects (studio_id, status);
create index on projects (client_id);

-- Projekt ↔ Offerten (mit Rolle aus linkedQuotes-Eintrag)
create table project_quote_links (
  project_id  uuid not null references projects(id) on delete cascade,
  quote_id    uuid not null,                          -- FK nach quotes (siehe unten)
  role        text,
  primary key (project_id, quote_id)
);

-- ════════════════════════════════════════════════════════════════════════════
-- OFFERTEN
-- ════════════════════════════════════════════════════════════════════════════

create table quotes (
  id              uuid primary key default gen_random_uuid(),
  studio_id       uuid not null references studios(id) on delete cascade,
  number          text,
  client_id       uuid references persons(id) on delete set null,
  project_id      uuid references projects(id) on delete set null,
  project_name    text,                               -- snapshot wenn Projekt nicht (mehr) verlinkt
  date            date,
  valid_until     date,
  mode            text check (mode in ('sia','manual','free')),
  mwst            boolean default true,
  notes           text,
  status          text default 'entwurf' check (status in
                  ('entwurf','gesendet','angenommen','abgelehnt','abgelaufen')),

  -- Drei Kalkulations-Pfade — je nach mode wird einer befüllt:
  sia_config      jsonb,                              -- {baukosten, schwierigkeit, stundenansatz, phases[]}
  manual_phases   jsonb,                              -- [{phaseId, …}]
  free_items      jsonb,                              -- [{id, desc, qty, price}]
  quote_roles     jsonb,                              -- [{id, label, rate}] — Rate-Overrides pro Offerte

  created_at      timestamptz not null default now(),
  unique (studio_id, number)
);
create index on quotes (studio_id, status);
create index on quotes (project_id);

alter table project_quote_links
  add constraint project_quote_links_quote_fk
  foreign key (quote_id) references quotes(id) on delete cascade;

-- ════════════════════════════════════════════════════════════════════════════
-- RECHNUNGEN
-- ════════════════════════════════════════════════════════════════════════════

create table invoices (
  id               uuid primary key default gen_random_uuid(),
  studio_id        uuid not null references studios(id) on delete cascade,
  number           text,
  client_id        uuid references persons(id) on delete set null,
  contact_id       uuid,                              -- optional: Kontaktperson aus persons.contacts (JSONB)
  project_id       uuid references projects(id) on delete set null,
  quote_id         uuid references quotes(id) on delete set null,

  date             date,
  due_date         date,
  sent_date        date,
  paid_date        date,                              -- gesetzt wenn status = 'bezahlt'

  items            jsonb not null default '[]'::jsonb,  -- [{id,desc,qty,price,discount}]
  mwst             boolean default true,
  mwst_rate        numeric(5,2),
  notes            text,

  status           text default 'entwurf' check (status in
                   ('entwurf','gesendet','bezahlt','überfällig')),

  invoice_kind     text check (invoice_kind in ('akonto','teilrechnung','schluss','voll')),

  discount_type    text default 'none' check (discount_type in ('none','percent','amount')),
  discount_value   numeric(14,2) default 0,
  discount_label   text,

  -- Welche Zeit-/Spesen-Einträge in diese Rechnung gehen
  entry_selections jsonb not null default '{}'::jsonb,

  qr_reference     text,                              -- 27-stellige Schweizer QR-Ref

  created_at       timestamptz not null default now(),
  unique (studio_id, number)
);
create index on invoices (studio_id, status);
create index on invoices (project_id);
create index on invoices (client_id);

-- Mahnungen-Historie. Verifiziert: Frontend speichert inv.reminders[] mit
-- {nr, date, sentDate, daysPast}. Wird hier 1:1 abgebildet, damit jeder mit
-- Buchhaltungs-Zugriff den letzten Mahnungs-Stand sieht (z.B. "3× Mahnung,
-- zuletzt 15.03.2025"). UI-Hinweis-Box ("schick eine Mahnung") bleibt
-- localStorage — wie Dark Mode, per-Device-Setting.
create table invoice_reminders (
  id          uuid primary key default gen_random_uuid(),
  invoice_id  uuid not null references invoices(id) on delete cascade,
  nr          int not null check (nr between 1 and 9),  -- 1. Erinnerung, 2./3. Mahnung
  date        date not null,                            -- Erstell-/Druckdatum
  sent_date   date,                                     -- editierbar im Mahnungs-Modal
  days_past   int,                                      -- Snapshot Tage überfällig zum Zeitpunkt
  note        text,
  created_at  timestamptz not null default now()
);
create index on invoice_reminders (invoice_id, nr);

-- ════════════════════════════════════════════════════════════════════════════
-- ZEITERFASSUNG
-- ════════════════════════════════════════════════════════════════════════════

create table time_entries (
  id           uuid primary key default gen_random_uuid(),
  studio_id    uuid not null references studios(id) on delete cascade,
  employee_id  uuid,                                  -- FK nach employees (siehe unten)
  project_id   uuid references projects(id) on delete set null,
  phase_id     text,                                  -- SIA-Phase z.B. "32"
  position_id  text,                                  -- optional, sub-position
  date         date not null,
  minutes      int not null,
  start_time   time,
  end_time     time,
  description  text,
  created_at   timestamptz not null default now()
);
create index on time_entries (studio_id, date);
create index on time_entries (employee_id, date);
create index on time_entries (project_id, date);

-- ════════════════════════════════════════════════════════════════════════════
-- SPESEN & AUSGABEN
-- ════════════════════════════════════════════════════════════════════════════

create table expenses (              -- Mitarbeiterspesen (zur Rückerstattung)
  id              uuid primary key default gen_random_uuid(),
  studio_id       uuid not null references studios(id) on delete cascade,
  employee_id     uuid,                              -- FK nach employees
  project_id      uuid references projects(id) on delete set null,
  date            date not null,
  category        text,                              -- aus studio_settings.expense_categories
  description     text,
  amount          numeric(14,2) not null,
  mwst_rate       numeric(5,2),
  incl_mwst       boolean default true,
  status          text default 'offen' check (status in
                  ('offen','genehmigt','auf nächsten Lohn','ausbezahlt')),
  receipt_url     text,                              -- Supabase Storage Pfad (statt Base64)
  receipt_name    text,
  lohn_entry_id   uuid,                              -- FK nach payroll_entries (gesetzt bei ausbezahlt)
  created_at      timestamptz not null default now()
);
create index on expenses (studio_id, status);
create index on expenses (employee_id, date);

create table internal_expenses (     -- Studio-Ausgaben (Miete, Software, …)
  id                  uuid primary key default gen_random_uuid(),
  studio_id           uuid not null references studios(id) on delete cascade,
  date                date not null,
  category            text,
  description         text,
  amount              numeric(14,2) not null,
  mwst_rate           numeric(5,2),
  incl_mwst           boolean default true,
  recurring           boolean default false,
  recurring_interval  text check (recurring_interval in ('monatlich','quartalsweise','jährlich')),
  receipt_url         text,
  created_at          timestamptz not null default now()
);
create index on internal_expenses (studio_id, date);

-- ════════════════════════════════════════════════════════════════════════════
-- MITARBEITER (HR)
-- ════════════════════════════════════════════════════════════════════════════

create table employees (
  id                        uuid primary key default gen_random_uuid(),
  studio_id                 uuid not null references studios(id) on delete cascade,
  name                      text not null,
  personal_nr               text,
  pensum                    int check (pensum between 0 and 100),  -- in Prozent
  wochenstunden             numeric(5,2),
  ferien_wochen             numeric(4,1),
  pk_ag_satz                numeric(5,2),                          -- Pensionskasse-AG-Satz
  ferien_uebertrag_vorjahr  jsonb not null default '{}'::jsonb,    -- {year: days}

  -- Brücke zu App-Login (optional — nicht jeder Mitarbeiter braucht Cloud-Account)
  app_user_id               uuid references profiles(id) on delete set null,

  active                    boolean default true,
  created_at                timestamptz not null default now()
);
create index on employees (studio_id, active);

-- Jetzt die zirkulären FKs nachreichen:
alter table time_entries
  add constraint time_entries_employee_fk
  foreign key (employee_id) references employees(id) on delete set null;

alter table expenses
  add constraint expenses_employee_fk
  foreign key (employee_id) references employees(id) on delete set null;

create table absences (
  id           uuid primary key default gen_random_uuid(),
  studio_id    uuid not null references studios(id) on delete cascade,
  employee_id  uuid not null references employees(id) on delete cascade,
  type_id      text,                                  -- FK auf absence_types(studio_id, id)
  date         date,                                  -- Einzeltag-Variante
  date_from    date,                                  -- Mehrtages-Variante
  date_to      date,
  start_time   time,
  end_time     time,
  hours        int,
  minutes      int,
  note         text,
  status       text default 'pending' check (status in ('pending','approved','rejected')),
  created_at   timestamptz not null default now(),
  constraint absences_type_fk foreign key (studio_id, type_id)
    references absence_types(studio_id, id) on delete set null
);
create index on absences (employee_id, date);

create table vacation_entries (             -- ferienEntries
  id            uuid primary key default gen_random_uuid(),
  studio_id     uuid not null references studios(id) on delete cascade,
  employee_id   uuid not null references employees(id) on delete cascade,
  date_from     date not null,
  date_to       date not null,
  note          text,
  status        text default 'pending' check (status in ('pending','approved','rejected')),
  original_data jsonb,                                -- für pending-Anträge: Snapshot der Eingabe
  created_at    timestamptz not null default now()
);
create index on vacation_entries (employee_id, date_from);

create table payroll_entries (              -- lohnEntries
  id            uuid primary key default gen_random_uuid(),
  studio_id     uuid not null references studios(id) on delete cascade,
  employee_id   uuid not null references employees(id) on delete cascade,
  year          int not null,
  month         int not null check (month between 1 and 12),
  brutto        numeric(14,2),
  ahv           numeric(14,2),
  alv           numeric(14,2),
  bvg           numeric(14,2),
  nbu           numeric(14,2),
  ktg           numeric(14,2),
  quellensteuer numeric(14,2),
  spesen        numeric(14,2),
  bonus         numeric(14,2),
  netto         numeric(14,2),
  status        text default 'entwurf',
  paid_at       date,
  created_at    timestamptz not null default now(),
  unique (employee_id, year, month)
);

-- expenses.lohn_entry_id zeigt jetzt auf existierende Tabelle:
alter table expenses
  add constraint expenses_lohn_entry_fk
  foreign key (lohn_entry_id) references payroll_entries(id) on delete set null;

create table overtime_closings (            -- uberstundenAbschluss
  id           uuid primary key default gen_random_uuid(),
  studio_id    uuid not null references studios(id) on delete cascade,
  employee_id  uuid not null references employees(id) on delete cascade,
  date         date not null,
  saldo_hours  numeric(8,2),
  created_at   timestamptz not null default now()
);

-- ════════════════════════════════════════════════════════════════════════════
-- DOKUMENTE: Protokolle, Lieferscheine
-- ════════════════════════════════════════════════════════════════════════════

create table protocols (
  id              uuid primary key default gen_random_uuid(),
  studio_id       uuid not null references studios(id) on delete cascade,
  number          text,                                -- "2025-BS-01"
  type            text check (type in (
                    'Bausitzung','Planungssitzung','Baubesprechung','Jour fixe',
                    'Interne Sitzung','Kundensitzung','Abnahme','Sonstiges')),
  location        text,
  project_id      uuid references projects(id) on delete set null,
  project_manual  text,                                -- freie Eingabe falls kein verlinktes Projekt
  participants    jsonb not null default '[]'::jsonb,  -- [{id,name,role,source,status}]
  traktanden      jsonb not null default '[]'::jsonb,  -- [{id,nr,title,items:[{kind,text,…}]}]
  next_date       date,
  verteiler       text,
  created_at      timestamptz not null default now(),
  unique (studio_id, number)
);
create index on protocols (studio_id, type);
create index on protocols (project_id);

create table delivery_notes (
  id          uuid primary key default gen_random_uuid(),
  studio_id   uuid not null references studios(id) on delete cascade,
  number      text,
  date        date,
  client_id   uuid references persons(id) on delete set null,
  project_id  uuid references projects(id) on delete set null,
  notes       text,
  created_at  timestamptz not null default now(),
  unique (studio_id, number)
);

create table delivery_note_items (
  id                uuid primary key default gen_random_uuid(),
  delivery_note_id  uuid not null references delivery_notes(id) on delete cascade,
  sort              int default 0,
  description       text,
  qty               numeric(12,3) default 1,
  unit              text default 'Stk.',
  note              text
);
create index on delivery_note_items (delivery_note_id);

-- ════════════════════════════════════════════════════════════════════════════
-- PINNWAND (Blog-Posts)
-- ════════════════════════════════════════════════════════════════════════════

create table blog_posts (
  id          uuid primary key default gen_random_uuid(),
  studio_id   uuid not null references studios(id) on delete cascade,
  author_id   uuid references profiles(id) on delete set null,
  category    text,
  title       text,
  body        text,
  pinned      boolean default false,
  created_at  timestamptz not null default now()
);
create index on blog_posts (studio_id, created_at desc);

-- ════════════════════════════════════════════════════════════════════════════
-- AUDIT-SPALTEN: created_by / updated_by + Auto-Update via Trigger
-- ----------------------------------------------------------------------------
-- Bewusst nur auf "Daten"-Tabellen, nicht auf Stammdaten-Konfig (studio_roles,
-- app_roles, absence_types, holidays, letter_templates, dashboard_templates) —
-- die ändern sich selten, der Audit-Overhead lohnt nicht. Für Stammdaten
-- reicht `updated_at`.
--
-- Beim Insert/Update wird updated_at = now(), updated_by = auth.uid() gesetzt.
-- created_by wird beim Insert einmalig gesetzt und nie überschrieben.
-- ════════════════════════════════════════════════════════════════════════════

do $$
declare t text;
begin
  for t in
    select unnest(array[
      'studios',
      'persons','projects','quotes','invoices','invoice_reminders',
      'time_entries','expenses','internal_expenses',
      'employees','absences','vacation_entries','payroll_entries','overtime_closings',
      'protocols','delivery_notes','delivery_note_items','blog_posts'
    ])
  loop
    execute format('alter table %I add column created_by uuid references auth.users(id);', t);
    execute format('alter table %I add column updated_by uuid references auth.users(id);', t);
    execute format('alter table %I add column updated_at timestamptz not null default now();', t);
  end loop;
end$$;

create or replace function set_audit_fields() returns trigger
language plpgsql as $$
begin
  if tg_op = 'INSERT' then
    new.created_by := coalesce(new.created_by, auth.uid());
    new.updated_by := coalesce(new.updated_by, auth.uid());
    new.updated_at := now();
  elsif tg_op = 'UPDATE' then
    new.created_by := old.created_by;   -- nie ändern
    new.updated_by := auth.uid();
    new.updated_at := now();
  end if;
  return new;
end$$;

do $$
declare t text;
begin
  for t in
    select unnest(array[
      'studios',
      'persons','projects','quotes','invoices','invoice_reminders',
      'time_entries','expenses','internal_expenses',
      'employees','absences','vacation_entries','payroll_entries','overtime_closings',
      'protocols','delivery_notes','delivery_note_items','blog_posts'
    ])
  loop
    execute format(
      'create trigger %I_audit before insert or update on %I
         for each row execute function set_audit_fields();',
      t, t
    );
  end loop;
end$$;

-- ════════════════════════════════════════════════════════════════════════════
-- ROW-LEVEL-SECURITY (RLS)
-- ============================================================================
-- Globale Policy: "User darf Zeile lesen/schreiben, wenn er Mitglied im
-- Studio (studio_id) der Zeile ist." Funktioniert für alle Tabellen mit
-- studio_id-Spalte. studios/studio_members/persons brauchen eigene Policies.
-- ════════════════════════════════════════════════════════════════════════════

-- Helper: prüft Mitgliedschaft des aktuellen Users in einem Studio
create or replace function is_studio_member(s_id uuid) returns boolean
language sql stable security definer as $$
  select exists (
    select 1 from studio_members
    where studio_id = s_id
      and user_id = auth.uid()
      and active = true
  );
$$;

-- Alle studio-bezogenen Tabellen
do $$
declare t text;
begin
  for t in
    select unnest(array[
      'studio_settings','studio_roles','app_roles','dashboard_templates',
      'absence_types','letter_templates','holidays',
      'persons','projects','project_quote_links','quotes',
      'invoices','invoice_reminders','time_entries',
      'expenses','internal_expenses',
      'employees','absences','vacation_entries','payroll_entries','overtime_closings',
      'protocols','delivery_notes','delivery_note_items','blog_posts'
    ])
  loop
    execute format('alter table %I enable row level security;', t);
  end loop;
end$$;

-- Tabellen mit direkter studio_id-Spalte: einheitliche Policy
-- (persons ist hier ausgenommen, weil studio_id NULL sein darf — siehe unten)
do $$
declare t text;
begin
  for t in
    select unnest(array[
      'studio_settings','studio_roles','app_roles','dashboard_templates',
      'absence_types','letter_templates','holidays',
      'projects','quotes',
      'invoices','time_entries',
      'expenses','internal_expenses',
      'employees','absences','vacation_entries','payroll_entries','overtime_closings',
      'protocols','delivery_notes','blog_posts'
    ])
  loop
    execute format($f$
      create policy %I_member_access on %I
        for all
        using (is_studio_member(studio_id))
        with check (is_studio_member(studio_id));
    $f$, t, t);
  end loop;
end$$;

-- persons: zwei Sichtbarkeitspfade (studio-lokal ODER via person_studio_links)
alter table person_studio_links enable row level security;
create policy persons_member_access on persons
  for all
  using (
    (studio_id is not null and is_studio_member(studio_id))
    or exists (
      select 1 from person_studio_links psl
      where psl.person_id = persons.id
        and is_studio_member(psl.studio_id)
    )
  )
  with check (
    (studio_id is not null and is_studio_member(studio_id))
    or exists (
      select 1 from person_studio_links psl
      where psl.person_id = persons.id
        and is_studio_member(psl.studio_id)
    )
  );

create policy person_studio_links_member_access on person_studio_links
  for all
  using (is_studio_member(studio_id))
  with check (is_studio_member(studio_id));

-- Sub-Tabellen ohne eigene studio_id: Zugriff via Parent
create policy project_quote_links_member_access on project_quote_links
  for all
  using (
    exists (select 1 from projects p
            where p.id = project_quote_links.project_id
              and is_studio_member(p.studio_id))
  );

create policy invoice_reminders_member_access on invoice_reminders
  for all
  using (
    exists (select 1 from invoices i
            where i.id = invoice_reminders.invoice_id
              and is_studio_member(i.studio_id))
  );

create policy delivery_note_items_member_access on delivery_note_items
  for all
  using (
    exists (select 1 from delivery_notes dn
            where dn.id = delivery_note_items.delivery_note_id
              and is_studio_member(dn.studio_id))
  );

-- studios: User sieht nur Studios, in denen er Mitglied ist
alter table studios enable row level security;
create policy studios_member_access on studios
  for select
  using (is_studio_member(id));

-- studio_members: User sieht eigene Mitgliedschaften
alter table studio_members enable row level security;
create policy studio_members_self_access on studio_members
  for select
  using (user_id = auth.uid() or is_studio_member(studio_id));

-- profiles: Jeder authentifizierte User sieht alle Profile (Anzeigenamen)
alter table profiles enable row level security;
create policy profiles_authenticated_read on profiles
  for select
  using (auth.role() = 'authenticated');
create policy profiles_self_write on profiles
  for update using (id = auth.uid());

-- ════════════════════════════════════════════════════════════════════════════
-- Nächste Migrations:
--   0002_storage.sql        — Supabase Storage Buckets (receipts, logos) + Policies
--   0003_seed_defaults.sql  — Pro neues Studio die Default-Rollen, Absenz-Typen,
--                              Letter-Templates, Dashboard-Templates anlegen
-- ════════════════════════════════════════════════════════════════════════════