-- Rename remaining German names on paliad.departments / paliad.department_members
-- (t-paliad-062 — F-03 fix).
--
-- Migration 020 renamed the *tables* paliad.dezernate → departments and
-- paliad.dezernat_mitglieder → department_members, but missed:
--   * the dezernat_id column on the link table (still German on prod youpc),
--   * the indexes/constraints/policies that postgres did not auto-rename
--     when their owning table was renamed.
--
-- Application code (services.DepartmentService.ListWithMembers,
-- ListMembers, AddMember, RemoveMember) selects/inserts on
-- department_members.department_id, so the missing column rename surfaces
-- as a 500 on /api/departments?include=members and on every /admin/team
-- write that touches Dezernat membership.
--
-- Idempotent: every rename is wrapped in a guard that swallows
-- "undefined_column" / "undefined_object" so the migration is a no-op on
-- DBs that were already provisioned with English names by migration 018.

-- ---------------------------------------------------------------------------
-- Column
-- ---------------------------------------------------------------------------
DO $$ BEGIN ALTER TABLE paliad.department_members RENAME COLUMN dezernat_id TO department_id; EXCEPTION WHEN undefined_column OR undefined_table THEN NULL; END $$;

-- ---------------------------------------------------------------------------
-- Constraints (primary key + foreign keys + check). Renaming a pkey
-- constraint also renames the underlying index of the same name.
-- ---------------------------------------------------------------------------
DO $$ BEGIN ALTER TABLE paliad.departments        RENAME CONSTRAINT dezernate_pkey                          TO departments_pkey;                       EXCEPTION WHEN undefined_object OR undefined_table OR duplicate_object THEN NULL; END $$;
DO $$ BEGIN ALTER TABLE paliad.departments        RENAME CONSTRAINT dezernate_lead_user_id_fkey             TO departments_lead_user_id_fkey;          EXCEPTION WHEN undefined_object OR undefined_table OR duplicate_object THEN NULL; END $$;
DO $$ BEGIN ALTER TABLE paliad.departments        RENAME CONSTRAINT dezernate_office_check                  TO departments_office_check;               EXCEPTION WHEN undefined_object OR undefined_table OR duplicate_object THEN NULL; END $$;
DO $$ BEGIN ALTER TABLE paliad.department_members RENAME CONSTRAINT dezernat_mitglieder_pkey                 TO department_members_pkey;                EXCEPTION WHEN undefined_object OR undefined_table OR duplicate_object THEN NULL; END $$;
DO $$ BEGIN ALTER TABLE paliad.department_members RENAME CONSTRAINT dezernat_mitglieder_dezernat_id_fkey     TO department_members_department_id_fkey;  EXCEPTION WHEN undefined_object OR undefined_table OR duplicate_object THEN NULL; END $$;
DO $$ BEGIN ALTER TABLE paliad.department_members RENAME CONSTRAINT dezernat_mitglieder_user_id_fkey         TO department_members_user_id_fkey;        EXCEPTION WHEN undefined_object OR undefined_table OR duplicate_object THEN NULL; END $$;

-- ---------------------------------------------------------------------------
-- Standalone indexes (non-pkey).
-- ---------------------------------------------------------------------------
DO $$ BEGIN ALTER INDEX paliad.dezernate_office_idx           RENAME TO departments_office_idx;        EXCEPTION WHEN undefined_object OR undefined_table OR duplicate_object THEN NULL; END $$;
DO $$ BEGIN ALTER INDEX paliad.dezernate_lead_idx             RENAME TO departments_lead_idx;          EXCEPTION WHEN undefined_object OR undefined_table OR duplicate_object THEN NULL; END $$;
DO $$ BEGIN ALTER INDEX paliad.dezernat_mitglieder_user_idx   RENAME TO department_members_user_idx;   EXCEPTION WHEN undefined_object OR undefined_table OR duplicate_object THEN NULL; END $$;

-- ---------------------------------------------------------------------------
-- RLS policies
-- ---------------------------------------------------------------------------
DO $$ BEGIN ALTER POLICY dezernate_select           ON paliad.departments        RENAME TO departments_select;        EXCEPTION WHEN undefined_object OR undefined_table OR duplicate_object THEN NULL; END $$;
DO $$ BEGIN ALTER POLICY dezernate_write            ON paliad.departments        RENAME TO departments_write;         EXCEPTION WHEN undefined_object OR undefined_table OR duplicate_object THEN NULL; END $$;
DO $$ BEGIN ALTER POLICY dezernat_mitglieder_select ON paliad.department_members RENAME TO department_members_select; EXCEPTION WHEN undefined_object OR undefined_table OR duplicate_object THEN NULL; END $$;
DO $$ BEGIN ALTER POLICY dezernat_mitglieder_write  ON paliad.department_members RENAME TO department_members_write;  EXCEPTION WHEN undefined_object OR undefined_table OR duplicate_object THEN NULL; END $$;
