-- Roles are created by scripts/db-roles.ts with generated passwords; this migration only grants privileges if the roles exist.
DO $$ BEGIN
  IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname='tools_reader') THEN
    EXECUTE 'GRANT USAGE ON SCHEMA public TO tools_reader';
    EXECUTE 'REVOKE ALL ON ALL TABLES IN SCHEMA public FROM tools_reader';
    EXECUTE 'GRANT SELECT ON public_tools, public_collections, public_link_checks, vendors, categories, tags, tool_categories, tool_tags, tool_alternatives, sources, tool_sources, collection_items TO tools_reader';
    EXECUTE 'GRANT INSERT ON error_reports, search_events TO tools_reader';
    EXECUTE 'GRANT USAGE, SELECT ON SEQUENCE search_events_id_seq TO tools_reader';
  END IF;
  IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname='tools_app') THEN
    EXECUTE 'GRANT USAGE ON SCHEMA public TO tools_app';
    EXECUTE 'GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO tools_app';
    EXECUTE 'GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO tools_app';
    EXECUTE 'REVOKE UPDATE, DELETE ON audit_log, tool_revisions FROM tools_app';
  END IF;
END $$;