-- Sponsorship system (referral codes).
--
-- One row per user is auto-provisioned at registration in `sponsorship`
-- (the user's own 6-char invite code) AND in `sponsorship_stats`
-- (counters starting at 0). The PHP side enforces the invariant.
--
-- `sponsorship_user` stores the directed edge "sponsor -> referred".
-- A given user can only be referred by ONE sponsor, ever — modeled by
-- making `referred_id` the primary key.
--
-- `code` is VARCHAR(6) for now; widening to 8 later only needs an
-- `ALTER TABLE` (no data migration since codes are uppercase alphanum).
-- Alphabet = Crockford Base32 (0-9 + A-Z without I, L, O, U) — chosen
-- to stay readable when spelt aloud (no 0/O or 1/I confusion).

CREATE TABLE `sponsorship` (
    `user_id`    BINARY(16)  NOT NULL,
    `code`       VARCHAR(6)  NOT NULL,
    `created_at` DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`user_id`),
    UNIQUE KEY `uniq_sponsorship_code` (`code`),
    CONSTRAINT `fk_sponsorship_user` FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `sponsorship_user` (
    -- A referred user can only have ONE sponsor, ever. Making this the PK
    -- enforces the invariant at the schema level (no app-side race needed).
    `referred_id` BINARY(16) NOT NULL,
    `sponsor_id`  BINARY(16) NOT NULL,
    `created_at`  DATETIME   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`referred_id`),
    -- Powers "list my referrals" lookups.
    KEY `idx_su_sponsor_created` (`sponsor_id`, `created_at`),
    CONSTRAINT `fk_su_sponsor`  FOREIGN KEY (`sponsor_id`)  REFERENCES `user`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_su_referred` FOREIGN KEY (`referred_id`) REFERENCES `user`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `sponsorship_stats` (
    `user_id`        BINARY(16)   NOT NULL,
    -- Total number of users referred (regardless of email confirmation).
    -- Bumped by SponsorshipService::attachSponsorById() at the moment the
    -- referred user submits the code (i.e. at filleul registration).
    `num_user_total` INT UNSIGNED NOT NULL DEFAULT 0,
    -- Number of referred users that have CONFIRMED their email. Bumped by
    -- EmailConfirmationService::confirm() — once per referred user, ever.
    -- Invariant: num_user_valid <= num_user_total.
    `num_user_valid` INT UNSIGNED NOT NULL DEFAULT 0,
    `created_at`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`user_id`),
    CONSTRAINT `fk_ss_user` FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
