Аукцион в реальном времени

-- Аукционы (лоты)
CREATE TABLE auctions (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    image_url VARCHAR(500),
    start_price DECIMAL(12, 2) NOT NULL,
    current_price DECIMAL(12, 2) NOT NULL,
    min_step DECIMAL(12, 2) DEFAULT 100.00,
    starts_at TIMESTAMP NOT NULL,
    ends_at TIMESTAMP NOT NULL,
    status ENUM('pending', 'active', 'ended', 'cancelled') DEFAULT 'pending',
    winner_id BIGINT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Ставки
CREATE TABLE bids (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    auction_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    amount DECIMAL(12, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (auction_id) REFERENCES auctions(id),
    INDEX idx_auction_amount (auction_id, amount DESC)
);

composer require pushler/php-sdk

<?php
// place_bid.php — сделать ставку

require_once 'vendor/autoload.php';

use PushlerRu\PushlerClient;

$pushler = new PushlerClient('key_ваш_ключ', 'secret_ваш_секрет');

header('Content-Type: application/json');

$auctionId = (int) $_POST['auction_id'];
$amount = (float) $_POST['amount'];
$userId = getCurrentUserId();

$pdo = getDbConnection();

// Начинаем транзакцию (защита от race condition)
$pdo->beginTransaction();

try {
    // Получаем аукцион с блокировкой
    $stmt = $pdo->prepare('
        SELECT * FROM auctions 
        WHERE id = ? AND status = "active" AND ends_at > NOW()
        FOR UPDATE
    ');
    $stmt->execute([$auctionId]);
    $auction = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$auction) {
        throw new Exception('Аукцион недоступен');
    }

    // Проверяем минимальную ставку
    $minBid = $auction['current_price'] + $auction['min_step'];
    if ($amount < $minBid) {
        throw new Exception("Минимальная ставка: {$minBid} ₽");
    }

    // Получаем предыдущего лидера (для уведомления)
    $stmt = $pdo->prepare('
        SELECT user_id FROM bids 
        WHERE auction_id = ? 
        ORDER BY amount DESC LIMIT 1
    ');
    $stmt->execute([$auctionId]);
    $previousLeader = $stmt->fetchColumn();

    // Сохраняем ставку
    $stmt = $pdo->prepare('
        INSERT INTO bids (auction_id, user_id, amount) 
        VALUES (?, ?, ?)
    ');
    $stmt->execute([$auctionId, $userId, $amount]);
    $bidId = $pdo->lastInsertId();

    // Обновляем текущую цену
    $stmt = $pdo->prepare('
        UPDATE auctions SET current_price = ? WHERE id = ?
    ');
    $stmt->execute([$amount, $auctionId]);

    $pdo->commit();

    // Получаем данные пользователя
    $user = getUserById($userId);

    $bidData = [
        'id' => $bidId,
        'amount' => $amount,
        'user' => [
            'id' => $userId,
            'name' => $user['name'],
            'avatar' => $user['avatar']
        ],
        'created_at' => date('c')
    ];

    // Broadcast новой ставки ВСЕМ участникам аукциона
    $pushler->trigger(
        "presence-auction-{$auctionId}",
        'bid:new',
        [
            'auction_id' => $auctionId,
            'bid' => $bidData,
            'current_price' => $amount,
            'min_next_bid' => $amount + $auction['min_step']
        ]
    );

    // Уведомляем предыдущего лидера, что его перебили
    if ($previousLeader && $previousLeader != $userId) {
        $pushler->trigger(
            "private-user-{$previousLeader}",
            'bid:outbid',
            [
                'auction_id' => $auctionId,
                'auction_title' => $auction['title'],
                'new_amount' => $amount,
                'your_amount' => $auction['current_price']
            ]
        );
    }

    echo json_encode(['success' => true, 'bid' => $bidData]);

} catch (Exception $e) {
    $pdo->rollBack();
    http_response_code(400);
    echo json_encode(['error' => $e->getMessage()]);
}

<?php
// end_auctions.php — cron каждую минуту

require_once 'vendor/autoload.php';

use PushlerRu\PushlerClient;

$pushler = new PushlerClient('key_ваш_ключ', 'secret_ваш_секрет');
$pdo = getDbConnection();

// Находим аукционы, которые нужно завершить
$stmt = $pdo->query('
    SELECT a.*, 
           (SELECT user_id FROM bids WHERE auction_id = a.id ORDER BY amount DESC LIMIT 1) as winner_id
    FROM auctions a
    WHERE status = "active" AND ends_at <= NOW()
');

while ($auction = $stmt->fetch(PDO::FETCH_ASSOC)) {
    // Обновляем статус
    $update = $pdo->prepare('
        UPDATE auctions SET status = "ended", winner_id = ? WHERE id = ?
    ');
    $update->execute([$auction['winner_id'], $auction['id']]);

    // Уведомляем всех участников о завершении
    $pushler->trigger(
        "presence-auction-{$auction['id']}",
        'auction:ended',
        [
            'auction_id' => $auction['id'],
            'final_price' => $auction['current_price'],
            'winner_id' => $auction['winner_id']
        ]
    );

    // Уведомляем победителя
    if ($auction['winner_id']) {
        $pushler->trigger(
            "private-user-{$auction['winner_id']}",
            'auction:won',
            [
                'auction_id' => $auction['id'],
                'title' => $auction['title'],
                'amount' => $auction['current_price']
            ]
        );
    }
}

<?php
// auth.php

require_once 'vendor/autoload.php';

use PushlerRu\PushlerClient;

$pushler = new PushlerClient('key_ваш_ключ', 'secret_ваш_секрет');

header('Content-Type: application/json');

$channelName = $_POST['channel_name'];
$socketId = $_POST['socket_id'];
$userId = getCurrentUserId();

if (!$userId) {
    http_response_code(401);
    echo json_encode(['error' => 'Unauthorized']);
    exit;
}

// Presence-канал аукциона
if (preg_match('/^presence-auction-(\d+)$/', $channelName, $matches)) {
    $user = getUserById($userId);
    
    $auth = $pushler->authorizePresenceChannel($channelName, $socketId, [
        'user_id' => (string) $userId,
        'user_info' => [
            'name' => $user['name'],
            'avatar' => $user['avatar']
        ]
    ]);
    
    echo json_encode($auth);
    exit;
}

// Приватный канал пользователя
if (preg_match('/^private-user-(\d+)$/', $channelName, $matches)) {
    if ((int) $matches[1] !== $userId) {
        http_response_code(403);
        exit;
    }
    
    echo json_encode($pushler->authorizeChannel($channelName, $socketId));
}

class LiveAuction {
    constructor(auctionId, userId, appKey) {
        this.auctionId = auctionId;
        this.userId = userId;
        this.appKey = appKey;
        
        this.currentPrice = 0;
        this.minNextBid = 0;
        this.endsAt = null;
        this.bids = [];
        this.participants = new Map();
        
        this.pushler = null;
        this.auctionChannel = null;
        this.userChannel = null;
    }

    async connect() {
        this.pushler = new PushlerClient({
            appKey: this.appKey,
            authEndpoint: '/auth.php'
        });

        return new Promise((resolve) => {
            this.pushler.on('connected', () => {
                this.subscribeToAuction();
                this.subscribeToUser();
                this.startTimer();
                resolve();
            });
        });
    }

    subscribeToAuction() {
        this.auctionChannel = this.pushler.subscribe(`presence-auction-${this.auctionId}`);

        // Получили список участников
        this.auctionChannel.on('pushler:subscription_succeeded', (data) => {
            if (data.members) {
                Object.entries(data.members).forEach(([id, info]) => {
                    this.participants.set(id, info);
                });
            }
            this.updateParticipantsUI();
        });

        // Новый участник
        this.auctionChannel.on('pushler:member_added', (member) => {
            this.participants.set(member.id, member.info);
            this.updateParticipantsUI();
            this.showToast(`${member.info.name} присоединился`);
        });

        // Участник ушёл
        this.auctionChannel.on('pushler:member_removed', (member) => {
            this.participants.delete(member.id);
            this.updateParticipantsUI();
        });

        // Новая ставка!
        this.auctionChannel.on('bid:new', (data) => {
            this.handleNewBid(data);
        });

        // Аукцион завершён
        this.auctionChannel.on('auction:ended', (data) => {
            this.handleAuctionEnded(data);
        });
    }

    subscribeToUser() {
        this.userChannel = this.pushler.subscribe(`private-user-${this.userId}`);

        // Вашу ставку перебили!
        this.userChannel.on('bid:outbid', (data) => {
            if (data.auction_id === this.auctionId) {
                this.showOutbidAlert(data);
                this.playSound('outbid');
            }
        });

        // Вы победили!
        this.userChannel.on('auction:won', (data) => {
            if (data.auction_id === this.auctionId) {
                this.showWinModal(data);
                this.playSound('win');
            }
        });
    }

    handleNewBid(data) {
        this.currentPrice = data.current_price;
        this.minNextBid = data.min_next_bid;
        this.bids.unshift(data.bid);
        
        // Обновляем UI
        this.updatePriceUI();
        this.updateBidsListUI();
        
        // Анимация и звук
        this.animatePrice();
        if (data.bid.user.id !== this.userId) {
            this.playSound('bid');
        }
    }

    handleAuctionEnded(data) {
        clearInterval(this.timerInterval);
        
        document.getElementById('auction-status').textContent = 'Завершён';
        document.getElementById('bid-form').style.display = 'none';
        
        if (data.winner_id) {
            const winner = this.participants.get(String(data.winner_id));
            document.getElementById('winner-info').innerHTML = `
                🏆 Победитель: ${winner?.name || 'Пользователь'} — ${this.formatPrice(data.final_price)}
            `;
        }
    }

    async placeBid(amount) {
        const response = await fetch('/place_bid.php', {
            method: 'POST',
            headers: { 'Content-Type': 'application/x-www-form-urlencoded' },
            body: `auction_id=${this.auctionId}&amount=${amount}`
        });

        const data = await response.json();
        
        if (!response.ok) {
            this.showError(data.error);
            return false;
        }
        
        return true;
    }

    // UI методы
    updatePriceUI() {
        const priceEl = document.getElementById('current-price');
        priceEl.textContent = this.formatPrice(this.currentPrice);
        
        document.getElementById('min-bid').textContent = this.formatPrice(this.minNextBid);
        document.getElementById('bid-input').min = this.minNextBid;
        document.getElementById('bid-input').value = this.minNextBid;
    }

    animatePrice() {
        const el = document.getElementById('current-price');
        el.classList.add('pulse');
        setTimeout(() => el.classList.remove('pulse'), 500);
    }

    updateBidsListUI() {
        const container = document.getElementById('bids-list');
        container.innerHTML = this.bids.slice(0, 10).map(bid => `
            <div class="bid-item ${bid.user.id === this.userId ? 'own' : ''}">
                <img src="${bid.user.avatar}" alt="" class="avatar" />
                <span class="name">${bid.user.name}</span>
                <span class="amount">${this.formatPrice(bid.amount)}</span>
            </div>
        `).join('');
    }

    updateParticipantsUI() {
        document.getElementById('participants-count').textContent = this.participants.size;
    }

    startTimer() {
        this.timerInterval = setInterval(() => {
            const now = new Date();
            const end = new Date(this.endsAt);
            const diff = end - now;
            
            if (diff <= 0) {
                document.getElementById('timer').textContent = '00:00:00';
                return;
            }
            
            const hours = Math.floor(diff / 3600000);
            const mins = Math.floor((diff % 3600000) / 60000);
            const secs = Math.floor((diff % 60000) / 1000);
            
            document.getElementById('timer').textContent = 
                `${hours.toString().padStart(2, '0')}:${mins.toString().padStart(2, '0')}:${secs.toString().padStart(2, '0')}`;
            
            // Подсвечиваем последнюю минуту
            if (diff < 60000) {
                document.getElementById('timer').classList.add('urgent');
            }
        }, 1000);
    }

    showOutbidAlert(data) {
        const alert = document.createElement('div');
        alert.className = 'outbid-alert';
        alert.innerHTML = `
            ⚠️ Вашу ставку перебили! Новая цена: ${this.formatPrice(data.new_amount)}
            <button onclick="auction.scrollToBidForm()">Сделать ставку</button>
        `;
        document.body.appendChild(alert);
        setTimeout(() => alert.remove(), 10000);
    }

    formatPrice(amount) {
        return new Intl.NumberFormat('ru-RU', {
            style: 'currency',
            currency: 'RUB',
            maximumFractionDigits: 0
        }).format(amount);
    }

    playSound(type) {
        const sounds = {
            bid: '/sounds/bid.mp3',
            outbid: '/sounds/outbid.mp3',
            win: '/sounds/win.mp3'
        };
        const audio = new Audio(sounds[type]);
        audio.volume = 0.3;
        audio.play().catch(() => {});
    }
}

// Использование
const auction = new LiveAuction(123, currentUserId, 'key_ваш_ключ');
auction.endsAt = '2026-01-15T18:00:00Z';
auction.currentPrice = 15000;
auction.minNextBid = 15500;
auction.connect();

// ❌ Неправильно — два пользователя могут сделать ставку одновременно
$auction = getAuction($id);
if ($amount > $auction['current_price']) {
    saveBid($amount); // Race condition!
}

// ✅ Правильно — блокировка строки в транзакции
$pdo->beginTransaction();
$stmt = $pdo->prepare('SELECT * FROM auctions WHERE id = ? FOR UPDATE');
// Теперь строка заблокирована до commit

// ❌ Неправильно — локальное время клиента
const timeLeft = endTime - Date.now(); // Может отличаться!

// ✅ Правильно — серверное время или NTP
// Сервер отправляет ends_at в UTC, клиент конвертирует
const endsAt = new Date(serverEndsAt); // ISO 8601 с timezone