CREATE OR REPLACE FUNCTION public.fn_validate_partner_id(p_partner_id bigint, p_email valid_email)
 RETURNS jsonb
 LANGUAGE plpgsql
 STABLE
AS $function$
DECLARE
    v_existing_email valid_email;
BEGIN
    -- [STEP 1] Basic numeric sanity check
    IF p_partner_id <= 0 THEN
        RETURN jsonb_build_object(
            'viable',  false,
            'message', 'PartnerID must be a positive integer greater than 0.'
        );
    END IF;

    -- [STEP 2] Lookup the partner_id in the identity table
    SELECT email INTO v_existing_email
    FROM partners
    WHERE partner_id = p_partner_id;

    -- [STEP 3] Collision detection
    IF FOUND THEN
        IF v_existing_email != p_email THEN
            -- PartnerID already linked to a different email → REJECTED
            RETURN jsonb_build_object(
                'viable',  false,
                'message', 'This PartnerID is already linked to a different account.'
            );
        ELSE
            -- Same ID, same email → already registered, allow re-validation
            RETURN jsonb_build_object(
                'viable',  true,
                'message', 'PartnerID confirmed. You are already registered in the system.'
            );
        END IF;
    END IF;

    -- [STEP 4] No collision → PartnerID is free and valid
    RETURN jsonb_build_object(
        'viable',  true,
        'message', 'PartnerID is available and valid.'
    );
END;
$function$
;