CREATE DATABASE IF NOT EXISTS `speedots_messaging` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE `speedots_messaging`;

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;

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;

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;