DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'br_app') THEN
    CREATE ROLE br_app LOGIN PASSWORD 'change_me';
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'br_migration') THEN
    CREATE ROLE br_migration LOGIN PASSWORD 'change_me';
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'br_reporting') THEN
    CREATE ROLE br_reporting LOGIN PASSWORD 'change_me';
  END IF;
END$$;

GRANT USAGE ON SCHEMA public TO br_app, br_reporting;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO br_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO br_app;
GRANT SELECT ON v_case_masked TO br_reporting;
REVOKE ALL ON audit_log FROM PUBLIC;
GRANT SELECT ON audit_log TO br_app;

INSERT INTO committee(name, committee_type)
VALUES ('Gesamtbetriebsrat', 'general')
ON CONFLICT DO NOTHING;