-- SPEEDots Messaging System Database Setup
-- Run this script to set up the MySQL database for the messaging system

-- Create the database
CREATE DATABASE IF NOT EXISTS speedots_messaging
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

-- Use the database
USE speedots_messaging;


GRANT ALL PRIVILEGES ON speedots_messaging.* TO 'speedots_user'@'localhost';
FLUSH PRIVILEGES;

-- Table: online_clients
-- Stores all connected clients with their connection status
CREATE TABLE IF NOT EXISTS online_clients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(255) NOT NULL,
    user_email VARCHAR(255),
    ip_address VARCHAR(45),
    connected_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_seen DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    status ENUM('online', 'offline') DEFAULT 'online',
    INDEX idx_client_id (client_id),
    INDEX idx_status (status),
    INDEX idx_last_seen (last_seen)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: signaling_queue
-- WebRTC signaling queue for exchanging SDP offers/answers and messages
CREATE TABLE IF NOT EXISTS signaling_queue (
    id INT AUTO_INCREMENT PRIMARY KEY,
    from_client_id VARCHAR(255) NOT NULL,
    to_client_id VARCHAR(255) NOT NULL,
    signal_type ENUM('offer', 'answer', 'ice_candidate', 'message') NOT NULL,
    signal_data TEXT NOT NULL,
    is_delivered BOOLEAN DEFAULT FALSE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    delivered_at DATETIME,
    INDEX idx_to_client (to_client_id, is_delivered),
    INDEX idx_created (created_at),
    INDEX idx_delivered (is_delivered)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: message_history
-- Stores message history for auditing and retrieval
CREATE TABLE IF NOT EXISTS message_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    from_client_id VARCHAR(255) NOT NULL,
    to_client_id VARCHAR(255),
    message TEXT NOT NULL,
    is_broadcast BOOLEAN DEFAULT FALSE,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_from_client (from_client_id),
    INDEX idx_to_client (to_client_id),
    INDEX idx_timestamp (timestamp),
    INDEX idx_broadcast (is_broadcast)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Optional: Create a cleanup event (requires EVENT scheduler to be enabled)
-- This automatically cleans up old data
DELIMITER $$

CREATE EVENT IF NOT EXISTS cleanup_messaging_data
ON SCHEDULE EVERY 1 HOUR
DO
BEGIN
    -- Mark clients offline if not seen in 2 minutes
    UPDATE online_clients
    SET status = 'offline'
    WHERE last_seen < DATE_SUB(NOW(), INTERVAL 2 MINUTE)
    AND status = 'online';

    -- Delete offline clients after 5 minutes
    DELETE FROM online_clients
    WHERE last_seen < DATE_SUB(NOW(), INTERVAL 5 MINUTE)
    AND status = 'offline';

    -- Delete old delivered signals (older than 1 hour)
    DELETE FROM signaling_queue
    WHERE is_delivered = TRUE
    AND delivered_at < DATE_SUB(NOW(), INTERVAL 1 HOUR);

    -- Delete old message history (older than 7 days)
    DELETE FROM message_history
    WHERE timestamp < DATE_SUB(NOW(), INTERVAL 7 DAY);
END$$

DELIMITER ;

-- Enable EVENT scheduler (if not already enabled)
SET GLOBAL event_scheduler = ON;

-- Display setup completion message
SELECT 'Database setup complete! Update messaging.php with your database credentials.' AS Status;

-- Show tables
SHOW TABLES;
