-- =============================================================================
-- FUNCTION: fn_get_user_session_context
-- PURPOSE:  Resolves the complete identity and roles matrix for an authenticated
--           Google user. Implements the Latent Superadmin step-up pattern.
--           Replaces the JS logic in authDbService.js → getUserRolesAndIdentity()
-- RETURNS:  JSONB {
--               user: { partner_id, name, surname, email, phone, country,
--                        zinzino_range, student_id, tutor_id,
--                        has_superadmin_clearance } | null,
--               allowed_roles: ['PROFESSOR'?, 'TUTOR'?, 'STUDENT'] | []
--           }
-- CALLED BY: POST /api/auth/google  (via authController.js)
-- =============================================================================

CREATE OR REPLACE FUNCTION public.fn_get_user_session_context(
    p_email valid_email
)
RETURNS JSONB
LANGUAGE plpgsql
STABLE
AS $$
DECLARE
    v_row   RECORD;
    v_roles TEXT[] := '{}';
    v_user  JSONB;
BEGIN
    -- [STEP 1] Resolve identity and role flags from the optimized view
    SELECT * INTO v_row
    FROM vw_user_identity_roles
    WHERE email = p_email;

    -- [STEP 2] User not found in the system → clean unregistered state
    IF NOT FOUND THEN
        RETURN jsonb_build_object(
            'user',          NULL::JSONB,
            'allowed_roles', '[]'::JSONB
        );
    END IF;

    -- [STEP 3] Build active roles array in priority order
    -- SUPERADMIN is intentionally EXCLUDED (latent step-up pattern)
    IF v_row.is_professor THEN
        v_roles := array_append(v_roles, 'PROFESSOR');
    END IF;

    IF v_row.is_tutor THEN
        v_roles := array_append(v_roles, 'TUTOR');
    END IF;

    -- STUDENT: granted only if the partner has an active student profile
    IF v_row.is_student THEN
        v_roles := array_append(v_roles, 'STUDENT');
    END IF;

    -- [STEP 4] Build the complete user identity context object
    v_user := jsonb_build_object(
        'partner_id',               v_row.partner_id,
        'name',                     v_row.name,
        'surname',                  v_row.surname,
        'email',                    v_row.email::TEXT,
        'phone',                    v_row.phone::TEXT,
        'country',                  v_row.country::TEXT,
        'zinzino_range',            v_row.zinzino_range,
        'student_id',               v_row.partner_id,
        'tutor_id',                 CASE WHEN v_row.is_tutor THEN v_row.partner_id ELSE NULL END,
        'has_superadmin_clearance', v_row.is_superadmin
    );

    -- [STEP 5] Return unified session context
    RETURN jsonb_build_object(
        'user',          v_user,
        'allowed_roles', to_jsonb(v_roles)
    );
END;
$$;

ALTER FUNCTION public.fn_get_user_session_context(valid_email)
    OWNER TO elfi1953;