-- +goose Up
-- IMMUTABLE clearance/poly ordinal functions for the Workforce Command Center aggregates. They map
-- the stored text codes to the SAME ordinals as the engine's enums (engine.ParseClearanceLevel /
-- ParsePolygraph), so WCC coverage math agrees byte-for-byte with screenMATCH. Pure (no table reads)
-- => RLS-neutral. Locked to the engine by TestOrdinalsMatchEngine + the integration test.

-- +goose StatementBegin
CREATE OR REPLACE FUNCTION mm_lvl_ord(code text) RETURNS int LANGUAGE sql IMMUTABLE PARALLEL SAFE AS $$
  SELECT CASE code
    WHEN 'ts_sci'       THEN 5
    WHEN 'top_secret'   THEN 4
    WHEN 'secret'       THEN 3
    WHEN 'confidential' THEN 2
    WHEN 'none'         THEN 1
    ELSE 0
  END
$$;
-- +goose StatementEnd

-- +goose StatementBegin
CREATE OR REPLACE FUNCTION mm_poly_ord(code text) RETURNS int LANGUAGE sql IMMUTABLE PARALLEL SAFE AS $$
  SELECT CASE code
    WHEN 'full_scope' THEN 2
    WHEN 'ci'         THEN 1
    ELSE 0
  END
$$;
-- +goose StatementEnd

GRANT EXECUTE ON FUNCTION mm_lvl_ord(text) TO missionmatch_app;
GRANT EXECUTE ON FUNCTION mm_poly_ord(text) TO missionmatch_app;

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