-- Coupon redemption ledger.
--
-- The composite PK (coupon_id, user_id) enforces the "one redemption per user
-- per coupon, ever" invariant at the schema level — a second INSERT raises a
-- 23000 duplicate-key error which the service translates to
-- `coupon.alreadyRedeemed`.
--
-- `consumed_at` is purely an audit column; no per-user reward replay is
-- supported.

CREATE TABLE `coupon_user` (
    `coupon_id`   VARCHAR(32) NOT NULL,
    `user_id`     BINARY(16)  NOT NULL,
    `consumed_at` DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`coupon_id`, `user_id`),
    -- Reverse lookup: "all coupons consumed by this user".
    KEY `idx_cu_user`        (`user_id`),
    CONSTRAINT `fk_cu_coupon` FOREIGN KEY (`coupon_id`) REFERENCES `coupon`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_cu_user`   FOREIGN KEY (`user_id`)   REFERENCES `user`(`id`)   ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
