-- SPDX-License-Identifier: LicenseRef-ConusAI-Template-1.0
-- An exact lookup through a platform-only role, never SELECT on auth tables.
-- This is a read side, not a decision, action lease or App user declaration.
CREATE FUNCTION public.conusai_app_approval_session_v1(p_handle_hash text,p_app_tenant uuid)
RETURNS jsonb LANGUAGE plpgsql STABLE SECURITY DEFINER
SET search_path=pg_catalog SET row_security=on AS $$
DECLARE observed jsonb; matches integer;
BEGIN
    IF session_user::text<>current_database()||'_approval' OR p_handle_hash !~ '^[0-9a-f]{64}$' OR p_app_tenant IS NULL
       OR p_app_tenant='00000000-0000-0000-0000-000000000000'::uuid THEN
        RAISE EXCEPTION 'app approval session refused' USING ERRCODE='42501';
    END IF;
    IF NOT EXISTS(SELECT FROM pg_roles WHERE rolname=session_user
        AND NOT rolsuper AND NOT rolbypassrls AND NOT rolcreatedb AND NOT rolcreaterole AND NOT rolreplication AND rolcanlogin)
        OR EXISTS(SELECT FROM pg_auth_members WHERE member=session_user::regrole)
        OR EXISTS(SELECT FROM pg_class WHERE relnamespace='public'::regnamespace AND relowner=session_user::regrole)
        OR EXISTS(SELECT FROM pg_database WHERE datname=current_database() AND datdba=session_user::regrole)
    THEN RAISE EXCEPTION 'app approval session refused' USING ERRCODE='42501'; END IF;
    PERFORM pg_catalog.set_config('app.org_id',p_app_tenant::text,true);
    SELECT count(*),min(x.record::text)::jsonb INTO matches,observed FROM (
        SELECT jsonb_build_object(
            'schema_version',1,'session_id',s.id,'session_version',s.version,
            'member_id',u.id,'identity_id',a.id,'identity_version',a.version,
            'issuer',s.issuer,'provider_subject',s.subject,'sealed_tokens',s.tokens,
            'created_at',s.created_at,'last_seen_at',s.last_seen_at,
            'absolute_expires_at',s.absolute_expires_at,
            'access_token_expires_at',s.access_token_expires_at,
            'app_tenant',o.id,'membership_id',m.id,'membership_version',m.version,
            'membership_role',m.role,'membership_role_key',m.role_key,
            'organization_version',o.version
        ) AS record FROM public.auth_sessions s
        JOIN public.app_user_identity a ON a.issuer=s.issuer AND a.subject=s.subject AND a.user_id=s.user_id
        JOIN public.users u ON u.id=a.user_id
        JOIN public.org_memberships m ON m.user_id=u.id AND m.org_id=p_app_tenant
        JOIN public.organizations o ON o.id=m.org_id
        WHERE s.handle_hash=p_handle_hash AND s.revoked_at IS NULL
          AND o.status='active'
        LIMIT 2
    ) x;
    IF matches<>1 THEN RETURN NULL; END IF;
    RETURN observed;
END
$$;
REVOKE ALL ON FUNCTION public.conusai_app_approval_session_v1(text,uuid) FROM PUBLIC;

-- DS21 gives the ordinary runtime EXECUTE by owner default privileges. This
-- narrow private function must explicitly remove those inherited ACL entries.
DO $$
DECLARE role_name text;
BEGIN
    FOREACH role_name IN ARRAY ARRAY[current_database()||'_app',current_database()||'_reader'] LOOP
        IF octet_length(role_name)<=63 AND EXISTS(SELECT FROM pg_roles WHERE rolname=role_name) THEN
            EXECUTE format('REVOKE ALL ON FUNCTION public.conusai_app_approval_session_v1(text,uuid) FROM %I',role_name);
        END IF;
    END LOOP;
END
$$;

-- The managed provider creates this platform-only role. Local/self-managed
-- installations without it gain no authority and receive no table grant.
DO $$
DECLARE approval_role text:=current_database()||'_approval'; safe boolean;
BEGIN
    IF octet_length(approval_role)<=63 AND EXISTS(SELECT FROM pg_roles WHERE rolname=approval_role) THEN
        SELECT NOT rolsuper AND NOT rolbypassrls AND NOT rolcreatedb AND NOT rolcreaterole
            AND NOT rolreplication AND rolcanlogin INTO safe FROM pg_roles WHERE rolname=approval_role;
        IF NOT safe OR EXISTS(SELECT FROM pg_auth_members WHERE member=(SELECT oid FROM pg_roles WHERE rolname=approval_role)) THEN
            RAISE EXCEPTION 'app approval role refused' USING ERRCODE='42501';
        END IF;
        EXECUTE format('GRANT USAGE ON SCHEMA public TO %I',approval_role);
        EXECUTE format('GRANT EXECUTE ON FUNCTION public.conusai_app_approval_session_v1(text,uuid) TO %I',approval_role);
    END IF;
END
$$;