CREATE TABLE IF NOT EXISTS department_catalog (
  id BIGSERIAL PRIMARY KEY,
  code VARCHAR(80) NOT NULL UNIQUE,
  label VARCHAR(120) NOT NULL,
  description TEXT,
  sort_order INTEGER NOT NULL DEFAULT 1000,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_by BIGINT REFERENCES app_user(id),
  updated_by BIGINT REFERENCES app_user(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_department_catalog_updated_at') THEN
    CREATE TRIGGER trg_department_catalog_updated_at
      BEFORE UPDATE ON department_catalog
      FOR EACH ROW EXECUTE FUNCTION set_updated_at();
  END IF;
END $$;

INSERT INTO department_catalog(code, label, sort_order)
VALUES
  ('it', 'IT', 10),
  ('personal', 'Personal', 20),
  ('fertigung', 'Fertigung', 30),
  ('qualitaet', 'Qualität', 40),
  ('logistik', 'Logistik', 50),
  ('einkauf', 'Einkauf', 60),
  ('verwaltung', 'Verwaltung', 70),
  ('sonstiges', 'Sonstiges', 999)
ON CONFLICT (code) DO UPDATE
  SET label = EXCLUDED.label,
      sort_order = EXCLUDED.sort_order,
      is_active = TRUE;

INSERT INTO department_catalog(code, label, sort_order)
SELECT DISTINCT
       lower(regexp_replace(regexp_replace(trim(department), '[^[:alnum:]]+', '_', 'g'), '^_+|_+$', '', 'g')) AS code,
       trim(department) AS label,
       500
  FROM br_case
 WHERE department IS NOT NULL
   AND trim(department) <> ''
   AND NOT EXISTS (
     SELECT 1
       FROM department_catalog d
      WHERE d.code = lower(regexp_replace(regexp_replace(trim(br_case.department), '[^[:alnum:]]+', '_', 'g'), '^_+|_+$', '', 'g'))
         OR d.code = trim(br_case.department)
   )
ON CONFLICT (code) DO NOTHING;

INSERT INTO app_page(page_key, path, label, nav_group, sort_order, description)
VALUES ('departments', '/departments', 'Abteilungen', 'admin', 215, 'Abteilungen für Vorgänge pflegen')
ON CONFLICT (page_key) DO UPDATE
  SET path = EXCLUDED.path,
      label = EXCLUDED.label,
      nav_group = EXCLUDED.nav_group,
      sort_order = EXCLUDED.sort_order,
      description = EXCLUDED.description;

INSERT INTO app_permission(permission_key, label, category, description)
VALUES ('departments.manage', 'Abteilungen verwalten', 'Administration', 'Abteilungen anlegen, bearbeiten und deaktivieren/löschen')
ON CONFLICT (permission_key) DO UPDATE
  SET label = EXCLUDED.label,
      category = EXCLUDED.category,
      description = EXCLUDED.description;

INSERT INTO app_role_page(role_profile_id, page_key, can_view)
SELECT id, 'departments', TRUE
  FROM app_role_profile
 WHERE code IN ('admin', 'chair')
ON CONFLICT (role_profile_id, page_key) DO UPDATE SET can_view = TRUE;

INSERT INTO app_role_permission(role_profile_id, permission_key, granted)
SELECT id, 'departments.manage', TRUE
  FROM app_role_profile
 WHERE code IN ('admin', 'chair')
ON CONFLICT (role_profile_id, permission_key) DO UPDATE SET granted = TRUE;

ALTER TABLE department_catalog ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS department_catalog_select ON department_catalog;
DROP POLICY IF EXISTS department_catalog_modify ON department_catalog;
CREATE POLICY department_catalog_select ON department_catalog FOR SELECT USING (
  app_can_view_page('departments')
  OR app_has_permission('departments.manage')
  OR app_has_permission('cases.write')
  OR app_can_view_page('cases')
);
CREATE POLICY department_catalog_modify ON department_catalog FOR ALL USING (app_has_permission('departments.manage')) WITH CHECK (app_has_permission('departments.manage'));

GRANT SELECT, INSERT, UPDATE, DELETE ON department_catalog TO br_app;
GRANT USAGE, SELECT ON SEQUENCE department_catalog_id_seq TO br_app;