init_db.sql

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE DATABASE IF NOT EXISTS lmeterx;
USE lmeterx;

-- ----------------------------
-- Table structure for llm_tasks
-- ----------------------------
DROP TABLE IF EXISTS `llm_tasks`;
CREATE TABLE `llm_tasks` (
  `id` varchar(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT 'idle',
  `created_by` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Creator username',
  `target_host` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `api_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '/chat/completions' COMMENT 'API path',
  `request_payload` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Custom request payload for non-chat completions APIs',
  `field_mapping` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Field mapping configuration for custom APIs (JSON string)',
  `api_type` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT 'openai-chat' COMMENT 'API type: openai-chat, claude-chat, embeddings, custom-chat',
  `model` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `test_data` longtext COLLATE utf8mb4_unicode_ci,
  `stream_mode` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT 'True',
  `concurrent_users` int(11) DEFAULT '1',
  `spawn_rate` int(11) DEFAULT '0',
  `duration` int(11) DEFAULT '60',
  `load_mode` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'fixed' COMMENT 'Load test mode: fixed or stepped',
  `step_start_users` int(11) DEFAULT NULL COMMENT 'Stepped mode: initial number of users',
  `step_increment` int(11) DEFAULT NULL COMMENT 'Stepped mode: users added per step',
  `step_duration` int(11) DEFAULT NULL COMMENT 'Stepped mode: duration of each step (seconds)',
  `step_max_users` int(11) DEFAULT NULL COMMENT 'Stepped mode: maximum number of users',
  `step_sustain_duration` int(11) DEFAULT NULL COMMENT 'Stepped mode: sustain duration at max users (seconds)',
  `warmup_enabled` tinyint(1) DEFAULT '1' COMMENT 'Warmup mode: 0=disabled, 1=enabled',
  `warmup_duration` int(11) DEFAULT '120' COMMENT 'Warmup duration in seconds (10-1800)',
  `cert_file` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `key_file` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `headers` json DEFAULT NULL,
  `cookies` json DEFAULT NULL,
  `error_message` text COLLATE utf8mb4_unicode_ci,
  `engine_id` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Engine instance ID that executed this task',
  `cluster_id` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Soft delete flag: 0=active, 1=deleted',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_status_created` (`status`,`created_at`),
  KEY `idx_created_by` (`created_by`),
  KEY `idx_name` (`name`),
  KEY `idx_status` (`status`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_model` (`model`),
  KEY `idx_model_concurrent_status` (`model`, `concurrent_users`, `status`, `created_at`),
  KEY `idx_is_deleted` (`is_deleted`),
  KEY `idx_engine_id` (`engine_id`),
  KEY `idx_cluster_status` (`cluster_id`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Table structure for llm_task_results
-- ----------------------------
DROP TABLE IF EXISTS `llm_task_results`;
CREATE TABLE `llm_task_results` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `task_id` varchar(36) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'task id',
  `metric_type` varchar(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'metric type',
  `num_requests` int(11) DEFAULT '0' COMMENT 'request total',
  `num_failures` int(11) DEFAULT '0' COMMENT 'request failure total',
  `avg_latency` float DEFAULT '0' COMMENT 'request average response time',
  `min_latency` float DEFAULT '0' COMMENT 'request minimum response time',
  `max_latency` float DEFAULT '0' COMMENT 'request maximum response time',
  `median_latency` float DEFAULT '0' COMMENT 'request median response time',
  `p95_latency` float DEFAULT '0' COMMENT 'request 95% response time',
  `rps` float DEFAULT '0' COMMENT 'request per second',
  `avg_content_length` float DEFAULT '0' COMMENT 'average output character length',
  `completion_tps` float DEFAULT '0' COMMENT 'completion tokens per second',
  `total_tps` float DEFAULT '0' COMMENT 'total tokens per second',
  `avg_total_tokens_per_req` float DEFAULT '0' COMMENT 'average total tokens per request',
  `avg_completion_tokens_per_req` float DEFAULT '0' COMMENT 'average completion tokens per request',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'create time',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'updated time',
  PRIMARY KEY (`id`),
  KEY `idx_task_id` (`task_id`),
  KEY `idx_task_metric_created` (`task_id`, `metric_type`, `created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=262 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Table structure for http_tasks
-- ----------------------------
DROP TABLE IF EXISTS `http_tasks`;
CREATE TABLE `http_tasks` (
  `id` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_by` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Creator username',
  `method` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL,
  `target_url` varchar(2000) COLLATE utf8mb4_unicode_ci NOT NULL,
  `target_host` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `api_path` varchar(1024) COLLATE utf8mb4_unicode_ci NOT NULL,
  `headers` json DEFAULT NULL,
  `cookies` json DEFAULT NULL,
  `request_body` longtext COLLATE utf8mb4_unicode_ci,
  `dataset_file` longtext COLLATE utf8mb4_unicode_ci,
  `curl_command` longtext COLLATE utf8mb4_unicode_ci,
  `success_assert` text COLLATE utf8mb4_unicode_ci,
  `concurrent_users` int(11) NOT NULL,
  `spawn_rate` int(11) NOT NULL,
  `duration` int(11) NOT NULL,
  `load_mode` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'fixed' COMMENT 'Load test mode: fixed or stepped',
  `step_start_users` int(11) DEFAULT NULL COMMENT 'Stepped mode: initial number of users',
  `step_increment` int(11) DEFAULT NULL COMMENT 'Stepped mode: users added per step',
  `step_duration` int(11) DEFAULT NULL COMMENT 'Stepped mode: duration of each step (seconds)',
  `step_max_users` int(11) DEFAULT NULL COMMENT 'Stepped mode: maximum number of users',
  `step_sustain_duration` int(11) DEFAULT NULL COMMENT 'Stepped mode: sustain duration at max users (seconds)',
  `error_message` text COLLATE utf8mb4_unicode_ci,
  `engine_id` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Engine instance ID that executed this task',
  `cluster_id` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Soft delete flag: 0=active, 1=deleted',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_common_status_created` (`status`,`created_at`),
  KEY `idx_common_created_by` (`created_by`),
  KEY `idx_common_name` (`name`),
  KEY `idx_common_created` (`created_at`),
  KEY `idx_is_deleted` (`is_deleted`),
  KEY `idx_common_engine_id` (`engine_id`),
  KEY `idx_cluster_status` (`cluster_id`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Table structure for http_task_results
-- ----------------------------
DROP TABLE IF EXISTS `http_task_results`;
CREATE TABLE `http_task_results` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `task_id` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'task id',
  `metric_type` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `num_requests` int(11) NOT NULL DEFAULT '0',
  `num_failures` int(11) NOT NULL DEFAULT '0',
  `avg_latency` float NOT NULL DEFAULT '0',
  `min_latency` float NOT NULL DEFAULT '0',
  `max_latency` float NOT NULL DEFAULT '0',
  `median_latency` float NOT NULL DEFAULT '0',
  `p95_latency` float NOT NULL DEFAULT '0',
  `rps` float NOT NULL DEFAULT '0',
  `avg_content_length` float NOT NULL DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'create time',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'updated time',
  PRIMARY KEY (`id`),
  KEY `idx_common_task_id` (`task_id`),
  KEY `idx_common_task_metric_created` (`task_id`, `metric_type`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Standards-based A2A / MCP protocol load tests
-- ----------------------------
DROP TABLE IF EXISTS `agent_tasks`;
CREATE TABLE `agent_tasks` (
  `id` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_by` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `protocol` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'a2a or mcp',
  `protocol_version` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `target_url` varchar(2000) COLLATE utf8mb4_unicode_ci NOT NULL,
  `target_host` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `api_path` varchar(1024) COLLATE utf8mb4_unicode_ci NOT NULL,
  `headers` json DEFAULT NULL,
  `protocol_config` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `concurrent_users` int(11) NOT NULL,
  `spawn_rate` int(11) NOT NULL,
  `duration` int(11) NOT NULL,
  `error_message` text COLLATE utf8mb4_unicode_ci,
  `engine_id` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `cluster_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'local',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_agent_status_created` (`status`,`created_at`),
  KEY `idx_agent_protocol_created` (`protocol`,`created_at`),
  KEY `idx_agent_cluster_status` (`cluster_id`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `agent_task_results`;
CREATE TABLE `agent_task_results` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `task_id` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `metric_type` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  `num_requests` int(11) NOT NULL DEFAULT '0',
  `num_failures` int(11) NOT NULL DEFAULT '0',
  `avg_latency` float NOT NULL DEFAULT '0',
  `min_latency` float NOT NULL DEFAULT '0',
  `max_latency` float NOT NULL DEFAULT '0',
  `median_latency` float NOT NULL DEFAULT '0',
  `p95_latency` float NOT NULL DEFAULT '0',
  `rps` float NOT NULL DEFAULT '0',
  `avg_content_length` float NOT NULL DEFAULT '0',
  `metric_data` longtext COLLATE utf8mb4_unicode_ci,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_agent_results_task_metric` (`task_id`,`metric_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Unified FIFO dispatch queue
-- ----------------------------
DROP TABLE IF EXISTS `task_dispatch_queue`;
CREATE TABLE `task_dispatch_queue` (
  `queue_seq` bigint NOT NULL AUTO_INCREMENT,
  `task_type` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL,
  `task_id` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `cluster_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'local',
  `status` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'created',
  `engine_id` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `claimed_at` datetime(6) DEFAULT NULL,
  PRIMARY KEY (`queue_seq`),
  UNIQUE KEY `uk_dispatch_task` (`task_type`, `task_id`),
  KEY `idx_dispatch_cluster_status_seq` (`cluster_id`, `status`, `queue_seq`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Table structure for test_insights
-- ----------------------------
DROP TABLE IF EXISTS `test_insights`;
CREATE TABLE `test_insights` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `task_id` varchar(36) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'task id',
  `eval_prompt` longtext COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'AI analysis prompt',
  `analysis_report` longtext COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'AI analysis content',
  `status` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT 'completed' COMMENT 'analysis status',
  `error_message` text COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'error message if analysis failed',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'create time',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'updated time',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_task_id` (`task_id`),
  KEY `idx_task_id` (`task_id`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Table structure for system_config
-- ----------------------------
DROP TABLE IF EXISTS `system_config`;
CREATE TABLE `system_config` (
  `id` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'config id',
  `config_key` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'config key',
  `config_value` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'config value',
  `description` text COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'config description',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'create time',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'updated time',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_config_key` (`config_key`),
  KEY `idx_config_key` (`config_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Table structure for clusters
-- ----------------------------
DROP TABLE IF EXISTS `clusters`;
CREATE TABLE `clusters` (
  `id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `status` enum('active','inactive','draining') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'active',
  `desired_replicas` int(11) NOT NULL DEFAULT '1',
  `min_replicas` int(11) NOT NULL DEFAULT '1',
  `max_replicas` int(11) NOT NULL DEFAULT '10',
  `current_replicas` int(11) NOT NULL DEFAULT '0',
  `ready_replicas` int(11) NOT NULL DEFAULT '0',
  `api_token` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert a default 'local' cluster for docker-compose compatibility
INSERT IGNORE INTO `clusters` (`id`, `name`, `description`, `status`, `desired_replicas`)
VALUES ('local', 'Local', 'Default local cluster for docker-compose deployment', 'active', 1);

-- ----------------------------
-- Table structure for engine_heartbeats
-- ----------------------------
DROP TABLE IF EXISTS `engine_heartbeats`;
CREATE TABLE `engine_heartbeats` (
  `engine_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `deployment_name` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `pod_name` varchar(253) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `cluster_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'local',
  `status` enum('online','busy','offline') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'online',
  `last_heartbeat` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `running_tasks` json DEFAULT NULL,
  `cpu_usage` float NOT NULL DEFAULT 0,
  `memory_usage` float NOT NULL DEFAULT 0,
  `available_slots` int(11) NOT NULL DEFAULT 1,
  `version` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `registered_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`engine_id`),
  KEY `idx_cluster_status` (`cluster_id`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Table structure for probe_tasks
-- ----------------------------
DROP TABLE IF EXISTS `probe_tasks`;
CREATE TABLE `probe_tasks` (
  `id` varchar(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `cluster_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `probe_type` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '"llm" | "http"',
  `status` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `engine_id` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `request_config` json NOT NULL,
  `result` json DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `completed_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_probe_cluster_status` (`cluster_id`, `status`),
  KEY `idx_probe_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Table structure for collections
-- ----------------------------
DROP TABLE IF EXISTS `collections`;
CREATE TABLE `collections` (
  `id` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `rich_content` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Rich text and chart config',
  `created_by` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Creator username',
  `is_public` tinyint(1) NOT NULL DEFAULT '1' COMMENT '0=private, 1=public',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_created_by` (`created_by`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Table structure for collection_tasks
-- ----------------------------
DROP TABLE IF EXISTS `collection_tasks`;
CREATE TABLE `collection_tasks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `collection_id` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `task_id` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `task_type` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'http or llm',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_collection_task` (`collection_id`, `task_id`),
  KEY `idx_collection_id` (`collection_id`),
  KEY `idx_task_id` (`task_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Table structure for reusable datasets
-- ----------------------------
DROP TABLE IF EXISTS `datasets`;
CREATE TABLE `datasets` (
  `id` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `file_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `file_path` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `file_size` bigint NOT NULL DEFAULT '0',
  `record_count` int NOT NULL DEFAULT '0',
  `dataset_types` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `tags` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_by` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_public` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_dataset_created_by` (`created_by`),
  KEY `idx_dataset_visibility_created` (`is_public`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;