-- AI Workspace module schema
-- Generated from the actual CodeIgniter migration:
-- app/Database/Migrations/2026-08-06-000001_CreateAiWorkspaceTables.php
-- (dumped here via SHOW CREATE TABLE against a real run, not hand-written)

CREATE TABLE `ai_providers` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `provider_key` varchar(40) NOT NULL,
  `label` varchar(100) NOT NULL,
  `driver` varchar(30) NOT NULL,
  `api_base_url` varchar(255) DEFAULT NULL,
  `api_key_encrypted` text DEFAULT NULL,
  `default_model` varchar(100) DEFAULT NULL,
  `extra_headers` text DEFAULT NULL,
  `is_enabled` tinyint(1) NOT NULL DEFAULT 0,
  `is_default` tinyint(1) NOT NULL DEFAULT 0,
  `priority` int(11) NOT NULL DEFAULT 100,
  `last_test_status` varchar(20) DEFAULT NULL,
  `last_test_message` text DEFAULT NULL,
  `last_tested_at` datetime DEFAULT NULL,
  `updated_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `provider_key` (`provider_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `ai_conversations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `mode` enum('chat','assistant') NOT NULL DEFAULT 'chat',
  `title` varchar(180) NOT NULL DEFAULT 'New conversation',
  `provider_key` varchar(40) DEFAULT NULL,
  `model` varchar(100) DEFAULT NULL,
  `erp_context_enabled` tinyint(1) NOT NULL DEFAULT 1,
  `is_archived` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `ai_messages` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `conversation_id` bigint(20) unsigned NOT NULL,
  `role` enum('user','assistant','system') NOT NULL,
  `content` longtext NOT NULL,
  `provider_key` varchar(40) DEFAULT NULL,
  `model` varchar(100) DEFAULT NULL,
  `tokens_input` int(10) unsigned DEFAULT NULL,
  `tokens_output` int(10) unsigned DEFAULT NULL,
  `is_error` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `conversation_id` (`conversation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `ai_tool_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `tool_key` varchar(60) NOT NULL,
  `input_summary` text DEFAULT NULL,
  `output_summary` text DEFAULT NULL,
  `status` enum('success','failed') NOT NULL,
  `error_message` text DEFAULT NULL,
  `duration_ms` int(10) unsigned DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `tool_key` (`tool_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
