-- Rebuilt SQL schema for the `speedots_messaging` database

--
-- Table structure for table `online_clients`
--

CREATE TABLE IF NOT EXISTS `online_clients` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `client_id` varchar(255) NOT NULL,
  `username` varchar(255) NOT NULL,
  `user_email` varchar(255) DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `connected_at` datetime DEFAULT current_timestamp(),
  `last_seen` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `status` enum('online','offline') DEFAULT 'online',
  PRIMARY KEY (`id`),
  UNIQUE KEY `client_id` (`client_id`),
  KEY `idx_client_id` (`client_id`),
  KEY `idx_status` (`status`),
  KEY `idx_last_seen` (`last_seen`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `signaling_queue`
--

CREATE TABLE IF NOT EXISTS `signaling_queue` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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` tinyint(1) DEFAULT 0,
  `created_at` datetime DEFAULT current_timestamp(),
  `delivered_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_to_client` (`to_client_id`,`is_delivered`),
  KEY `idx_created` (`created_at`),
  KEY `idx_delivered` (`is_delivered`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `message_history`
--

CREATE TABLE IF NOT EXISTS `message_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `from_client_id` varchar(255) NOT NULL,
  `to_client_id` varchar(255) DEFAULT NULL,
  `message` text NOT NULL,
  `is_broadcast` tinyint(1) DEFAULT 0,
  `timestamp` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_from_client` (`from_client_id`),
  KEY `idx_to_client` (`to_client_id`),
  KEY `idx_timestamp` (`timestamp`),
  KEY `idx_broadcast` (`is_broadcast`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Event for cleaning 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
--

SET GLOBAL event_scheduler = ON;