游戏数据库存储优惠卷

CREATE TABLE Coupons (
    CouponID INT PRIMARY KEY AUTO_INCREMENT,
    Code VARCHAR(50) NOT NULL UNIQUE,
    DiscountType ENUM('Amount', 'Percentage', 'Threshold') NOT NULL,
    DiscountValue DECIMAL(10, 2) NOT NULL,
    MinPurchase DECIMAL(10, 2),
    ExpiryDate DATETIME,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt DATETIME ON UPDATE CURRENT_TIMESTAMP
);

import mysql.connector
from datetime import datetime, timedelta

# 假设已建立数据库连接
db = mysql.connector.connect(host="localhost", user="user", password="password", database="game_db")
cursor = db.cursor()

def issue_coupon(player_id, coupon_code, discount_type, discount_value, expiry_days):
    expiry_date = datetime.now() + timedelta(days=expiry_days)
    try:
        cursor.execute("""
            INSERT INTO Coupons (Code, DiscountType, DiscountValue, ExpiryDate)
            VALUES (%s, %s, %s, %s)
        """, (coupon_code, discount_type, discount_value, expiry_date))
        db.commit()
        
        # 假设有一个PlayerCoupons表来关联玩家和优惠券
        cursor.execute("""
            INSERT INTO PlayerCoupons (PlayerID, CouponID)
            VALUES (%s, LAST_INSERT_ID())
        """, (player_id,))
        db.commit()
    except mysql.connector.Error as err:
        print(f"Error issuing coupon: {err}")
        db.rollback()

# 示例调用
issue_coupon(player_id=123, coupon_code="SUMMER20", discount_type="Percentage", discount_value=20.0, expiry_days=30)