PHP 개발자몰 기능 이용방법 : 멤버십 포인트 및 레벨 시스템 구축

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    description TEXT,
    price DECIMAL(10, 2),
    stock INT
);

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    product_id INT,
    quantity INT,
    amount DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255),
    password VARCHAR(255),
    points INT,
    level TINYINT
);

// 更新用户积分
function updatePoints($userId, $points) {
    // 查询当前用户积分
    $currentPoints = selectPointsByUserId($userId);
    
    // 计算新的积分
    $newPoints = $currentPoints + $points;
    
    // 更新积分
    updatePointsByUserId($userId, $newPoints);
}

// 更新用户等级
function updateLevel($userId, $points) {
    // 查询当前用户等级
    $currentLevel = selectLevelByUserId($userId);
    
    // 根据积分数判断新的等级
    if ($points >= 100 && $points < 200) {
        $newLevel = 'Bronze';
    } elseif ($points >= 200 && $points < 500) {
        $newLevel = 'Silver';
    } elseif ($points >= 500 && $points < 1000) {
        $newLevel = 'Gold';
    } elseif ($points >= 1000) {
        $newLevel = 'Platinum';
    } else {
        $newLevel = 'Regular';
    }
    
    // 更新等级
    updateLevelByUserId($userId, $newLevel);
}

// 根据用户等级获取折扣
function getDiscountByLevel($level) {
    switch ($level) {
        case 'Bronze':
            return 0.95; // 95%折扣
        case 'Silver':
            return 0.9; // 90%折扣
        case 'Gold':
            return 0.85; // 85%折扣
        case 'Platinum':
            return 0.8; // 80%折扣
        default:
            return 1; // 无折扣
    }
}