游戏数据库存储首购优惠

CREATE TABLE Players (
    PlayerID INT PRIMARY KEY,
    Username VARCHAR(255) NOT NULL,
    FirstPurchaseDate DATETIME,
    FirstPurchaseDiscountApplied BOOLEAN DEFAULT FALSE
);

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    Name VARCHAR(255) NOT NULL,
    RegularPrice DECIMAL(10, 2),
    FirstPurchaseDiscount DECIMAL(5, 2)
);

CREATE TABLE Transactions (
    TransactionID INT PRIMARY KEY,
    PlayerID INT,
    ProductID INT,
    PurchaseDate DATETIME,
    Price DECIMAL(10, 2),
    FOREIGN KEY (PlayerID) REFERENCES Players(PlayerID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

UPDATE Players
SET FirstPurchaseDate = NOW(), FirstPurchaseDiscountApplied = TRUE
WHERE PlayerID = ? AND NOT FirstPurchaseDiscountApplied;

CREATE INDEX idx_playerid ON Players(PlayerID);

BEGIN TRANSACTION;
SELECT FirstPurchaseDiscountApplied FROM Players WHERE PlayerID = ? FOR UPDATE;
-- 执行优惠应用逻辑
COMMIT;

import mysql.connector

def apply_first_purchase_discount(player_id, product_id):
    db = mysql.connector.connect(host="localhost", user="user", password="password", database="game_db")
    cursor = db.cursor()

    try:
        cursor.execute("START TRANSACTION;")
        cursor.execute("SELECT FirstPurchaseDiscountApplied FROM Players WHERE PlayerID = %s FOR UPDATE;", (player_id,))
        result = cursor.fetchone()
        if result and not result[0]:
            cursor.execute("UPDATE Players SET FirstPurchaseDate = NOW(), FirstPurchaseDiscountApplied = TRUE WHERE PlayerID = %s;", (player_id,))
            cursor.execute("INSERT INTO Transactions (PlayerID, ProductID, PurchaseDate, Price) VALUES (%s, %s, NOW(), (SELECT RegularPrice - FirstPurchaseDiscount FROM Products WHERE ProductID = %s));", (player_id, product_id, product_id))
            db.commit()
            print("First purchase discount applied successfully.")
        else:
            print("First purchase discount already applied or player not found.")
    except Exception as e:
        db.rollback()
        print(f"Error: {e}")
    finally:
        cursor.close()
        db.close()

# 示例调用
apply_first_purchase_discount(1, 101)