-- =============================================================================
-- FUNCTION: fn_validate_partner_id
-- PURPOSE:  Validates a self-declared Zinzino PartnerID against the partners
--           table. Checks for identity collisions (same ID, different email).
-- RETURNS:  JSONB { viable: boolean, message: text }
-- CALLED BY: POST /api/auth/validate-partner-id
-- =============================================================================

CREATE OR REPLACE FUNCTION public.fn_validate_partner_id(
    p_partner_id    BIGINT,
    p_email         valid_email
)
RETURNS JSONB
LANGUAGE plpgsql
STABLE
AS $$
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;
$$;

ALTER FUNCTION public.fn_validate_partner_id(INT, valid_email)
    OWNER TO elfi1953;