CREATE TABLE affiliate_partners (
    id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
    partner_name VARCHAR(128) NOT NULL,
    partner_code VARCHAR(128) NOT NULL,
    partner_user_id VARCHAR(64) NOT NULL,
    user_email VARCHAR(255),
    referral_user_count INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX affiliate_partners_code_idx ON affiliate_partners(partner_code);
CREATE INDEX affiliate_partners_user_idx ON affiliate_partners(partner_user_id);

ALTER TABLE users ADD COLUMN affiliate_referral_id VARCHAR(64) REFERENCES affiliate_partners(id);