数据库方案设计优惠

-- 创建优惠表
CREATE TABLE coupons (
    coupon_id INT PRIMARY KEY AUTO_INCREMENT,
    type ENUM('DISCOUNT', 'CASH_OFF', 'BUY_ONE_GET_ONE') NOT NULL,
    value DECIMAL(10, 2) NOT NULL,
    expiration_date DATE NOT NULL
);

-- 创建用户表
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    points INT DEFAULT 0,
    used_coupons TEXT -- 存储已使用的优惠券ID列表
);

-- 创建订单表
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    total_amount DECIMAL(10, 2),
    discount_amount DECIMAL(10, 2),
    final_amount DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- 示例：为用户添加优惠券
INSERT INTO coupons (type, value, expiration_date) VALUES ('DISCOUNT', 10.00, '2023-12-31');
SET @coupon_id = LAST_INSERT_ID();
UPDATE users SET used_coupons = CONCAT(used_coupons, ',', @coupon_id) WHERE user_id = 1;

-- 示例：创建订单时应用优惠券
INSERT INTO orders (user_id, total_amount, discount_amount, final_amount)
SELECT 1, 100.00, 10.00, 90.00;