-- nn_referral_codes: one code per user (permanent) or guest (30-min TTL)
CREATE TABLE IF NOT EXISTS nn_referral_codes (
    id                INT UNSIGNED     AUTO_INCREMENT PRIMARY KEY,
    code              VARCHAR(32)      NOT NULL UNIQUE,
    user_id           INT UNSIGNED     DEFAULT NULL,   -- NULL = guest
    guest_session_id  VARCHAR(128)     DEFAULT NULL,   -- PHP session_id() for guests
    store_id          INT UNSIGNED     NOT NULL,
    domain            VARCHAR(120)     NOT NULL,
    total_clicks      INT UNSIGNED     DEFAULT 0,
    total_conversions INT UNSIGNED     DEFAULT 0,
    total_earned_cad  DECIMAL(8,2)    DEFAULT 0.00,
    is_active         TINYINT(1)       DEFAULT 1,
    expires_at        DATETIME         DEFAULT NULL,   -- NULL = permanent, 30min for guests
    created_at        DATETIME         DEFAULT current_timestamp(),
    INDEX idx_code    (code),
    INDEX idx_user    (user_id),
    INDEX idx_guest   (guest_session_id),
    FOREIGN KEY       (user_id) REFERENCES nn_customer_accounts(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- nn_referral_events: every click, register, purchase, and milestone
CREATE TABLE IF NOT EXISTS nn_referral_events (
    id                  INT UNSIGNED   AUTO_INCREMENT PRIMARY KEY,
    ref_code_id         INT UNSIGNED   NOT NULL,
    referred_session    VARCHAR(128)   DEFAULT NULL,  -- guest session that followed link
    referred_user_id    INT UNSIGNED   DEFAULT NULL,  -- if they registered
    order_id            INT UNSIGNED   DEFAULT NULL,
    event_type          ENUM('click','register','purchase','milestone_bonus') NOT NULL,
    credit_awarded_cad  DECIMAL(8,2)  DEFAULT 0.00,  -- to referrer (logged-in)
    discount_applied_cad DECIMAL(8,2) DEFAULT 0.00,  -- to referred cart (guest flow)
    milestone_bonus_cad DECIMAL(8,2)  DEFAULT 0.00,
    created_at          DATETIME       DEFAULT current_timestamp(),
    INDEX idx_code      (ref_code_id),
    INDEX idx_order     (order_id),
    FOREIGN KEY         (ref_code_id) REFERENCES nn_referral_codes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Track milestone progress per referrer
ALTER TABLE nn_referral_codes
    ADD COLUMN IF NOT EXISTS milestone_count INT UNSIGNED DEFAULT 0 AFTER total_conversions;
