CREATE TABLE `messages` (
  `id` char(36) NOT NULL DEFAULT uuid(),
  `conversation_id` char(36) DEFAULT NULL,
  `sender_type` varchar(20) DEFAULT NULL CHECK (`sender_type` in ('customer','agent','system','ai','bot')),
  `sender_id` char(36) DEFAULT NULL,
  `message_text` text DEFAULT NULL,
  `message_type` varchar(50) DEFAULT NULL CHECK (`message_type` in ('text','image','video','audio','document','location','contact','sticker','template','interactive','reaction')),
  `media_url` varchar(500) DEFAULT NULL,
  `media_mime_type` varchar(100) DEFAULT NULL,
  `media_caption` text DEFAULT NULL,
  `media_size_bytes` bigint(20) DEFAULT NULL,
  `is_ai_generated` tinyint(1) DEFAULT 0,
  `ai_model` varchar(50) DEFAULT NULL,
  `ai_confidence` decimal(3,2) DEFAULT NULL,
  `ai_prompt_tokens` int(11) DEFAULT NULL,
  `ai_completion_tokens` int(11) DEFAULT NULL,
  `ai_cost` decimal(10,6) DEFAULT NULL,
  `status` varchar(20) DEFAULT NULL CHECK (`status` in ('queued','sent','delivered','read','failed','deleted')),
  `external_id` varchar(255) DEFAULT NULL,
  `error_code` varchar(50) DEFAULT NULL,
  `error_message` text DEFAULT NULL,
  `metadata` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`metadata`)),
  `channel` varchar(50) DEFAULT NULL,
  `sent_at` timestamp NULL DEFAULT NULL,
  `delivered_at` timestamp NULL DEFAULT NULL,
  `read_at` timestamp NULL DEFAULT NULL,
  `failed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `sender_id` (`sender_id`),
  KEY `idx_conversation_created` (`conversation_id`,`created_at` DESC),
  KEY `idx_status_created` (`status`,`created_at`),
  CONSTRAINT `messages_ibfk_1` FOREIGN KEY (`conversation_id`) REFERENCES `conversations` (`id`) ON DELETE CASCADE,
  CONSTRAINT `messages_ibfk_2` FOREIGN KEY (`sender_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci