数据库存储首购优惠

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(255),
    email VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    total_amount DECIMAL(10, 2),
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE coupons (
    coupon_id INT PRIMARY KEY,
    code VARCHAR(50),
    discount_type ENUM('fixed', 'percentage'),
    discount_value DECIMAL(10, 2),
    expiration_date TIMESTAMP,
    used BOOLEAN DEFAULT FALSE
);

CREATE TABLE order_coupons (
    order_id INT,
    coupon_id INT,
    PRIMARY KEY (order_id, coupon_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (coupon_id) REFERENCES coupons(coupon_id)
);

ALTER TABLE coupons ADD COLUMN user_id INT;

-- 应用优惠券时的检查逻辑
SELECT * FROM coupons WHERE code = 'FIRST_PURCHASE' AND used = FALSE AND user_id IS NULL;
-- 如果查询结果为空，则表示该优惠券已被使用或不符合条件