CREATE TABLE user_referrals
(
    user_id     bigint                                 NOT NULL
        CONSTRAINT user_referrals_pk
            PRIMARY KEY
        CONSTRAINT user_referrals_users_id_fk
            REFERENCES users
            ON DELETE CASCADE,
    referrer_id bigint                                 NOT NULL
        CONSTRAINT user_referrals_users_id_fk_2
            REFERENCES users
            ON DELETE SET NULL,
    balance     numeric(38, 9)           DEFAULT 0.00  NOT NULL,
    created_at  timestamp with time zone DEFAULT NOW() NOT NULL,
    updated_at  timestamp with time zone DEFAULT NOW() NOT NULL
);

-- name: GetReferralChain :many
WITH RECURSIVE referral_chain AS (
    SELECT user_id,
           referrer_id,
           1 AS level
    FROM user_referrals r
    WHERE r.user_id = $1

    UNION ALL

    SELECT r.user_id,
           r.referrer_id,
           rc.level + 1
    FROM user_referrals r
             JOIN referral_chain rc ON r.user_id = rc.referrer_id
    WHERE rc.level < @level
)
SELECT *
FROM referral_chain
;