-- Telegram Queue Bot Database Schema
-- Compatible with MySQL 5.7+ and MariaDB 10.2+

-- Settings table
CREATE TABLE IF NOT EXISTS settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    avg_service_minutes INT NOT NULL DEFAULT 5,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default setting
INSERT INTO settings (id, avg_service_minutes) VALUES (1, 5) ON DUPLICATE KEY UPDATE avg_service_minutes=5;

-- Queue entries table
CREATE TABLE IF NOT EXISTS queue_entries (
    id INT PRIMARY KEY AUTO_INCREMENT,
    telegram_id BIGINT NOT NULL,
    first_name VARCHAR(255) DEFAULT NULL,
    last_name VARCHAR(255) DEFAULT NULL,
    username VARCHAR(255) DEFAULT NULL,
    phone VARCHAR(20) NOT NULL,
    status ENUM('waiting', 'called', 'done', 'canceled') NOT NULL DEFAULT 'waiting',
    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    called_at TIMESTAMP NULL DEFAULT NULL,
    INDEX idx_telegram_id (telegram_id),
    INDEX idx_status (status),
    INDEX idx_joined_at (joined_at),
    INDEX idx_telegram_status (telegram_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

