-- +goose Up
-- THE PRE TENANT RESOLVE. Turning a public slug into an organization id before any tenant context exists.
--
-- The careers surface in migration 0091 needed one thing before it could do anything else: name the tenant.
-- It did that with a plain query against organizations from the application pool, which returns ZERO ROWS,
-- correctly and by design. organizations carries FORCE ROW LEVEL SECURITY, the application role is
-- NOBYPASSRLS, and a public request has no app.current_org_id to satisfy the policy with. The whole tenancy
-- contract is that no context yields no rows, and that contract does not make an exception for a feature
-- that finds it inconvenient.
--
-- So the resolve crosses the line the SAME way every other pre tenant lookup in this schema does: a SECURITY
-- DEFINER function, narrow enough to be safe.
--
-- IT RETURNS AN IDENTIFIER AND NOTHING ELSE. Not a name, not a status, not a count, not a list. A caller
-- supplies a slug they already knew and learns only whether it corresponds to an active organization, which
-- is the same thing they learn from whether the careers page renders. It cannot be used to enumerate
-- customers, because it answers only about the exact string it was handed.
-- +goose StatementBegin
CREATE OR REPLACE FUNCTION public_org_id_by_slug(p_slug text) RETURNS uuid AS $$
  SELECT id FROM organizations
  WHERE lower(slug) = lower(btrim(p_slug))
    AND status = 'active'
  LIMIT 1;
$$ LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public, pg_temp;
-- +goose StatementEnd
REVOKE ALL ON FUNCTION public_org_id_by_slug(text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public_org_id_by_slug(text) TO missionmatch_app;

SELECT mm_mirror_staging_role_privileges();

-- +goose Down
DROP FUNCTION IF EXISTS public_org_id_by_slug(text);