-- SELF_V2 base schema

CREATE TABLE IF NOT EXISTS selfv2_settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  setting_key VARCHAR(100) NOT NULL UNIQUE,
  setting_value TEXT,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS selfv2_llm_providers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  provider_key VARCHAR(50) NOT NULL UNIQUE,
  label VARCHAR(100) NOT NULL,
  endpoint TEXT,
  enabled TINYINT(1) DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS selfv2_llm_models (
  id INT AUTO_INCREMENT PRIMARY KEY,
  provider_key VARCHAR(50) NOT NULL,
  model_key VARCHAR(100) NOT NULL,
  label VARCHAR(150) DEFAULT NULL,
  enabled TINYINT(1) DEFAULT 1,
  sort_order INT DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_provider_model (provider_key, model_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS selfv2_llm_agents (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE,
  provider_key VARCHAR(50) NOT NULL,
  default_model VARCHAR(100) DEFAULT NULL,
  endpoint TEXT,
  secret_ref VARCHAR(150) DEFAULT NULL,
  enabled TINYINT(1) DEFAULT 1,
  is_default TINYINT(1) DEFAULT 0,
  sort_order INT DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS selfv2_conversations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS selfv2_messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  conversation_id INT NOT NULL,
  role VARCHAR(20) NOT NULL,
  agent_name VARCHAR(50) DEFAULT NULL,
  model_key VARCHAR(100) DEFAULT NULL,
  content LONGTEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  KEY idx_conversation (conversation_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS selfv2_chat_actions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  label VARCHAR(255) NOT NULL,
  action_type VARCHAR(50) NOT NULL DEFAULT 'prompt',
  command_text TEXT,
  enabled TINYINT(1) DEFAULT 1,
  sort_order INT DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS selfv2_workspace_tabs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  url TEXT,
  is_fixed TINYINT(1) DEFAULT 0,
  is_closable TINYINT(1) DEFAULT 1,
  sort_order INT DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS selfv2_user_ui_state (
  id INT AUTO_INCREMENT PRIMARY KEY,
  state_key VARCHAR(100) NOT NULL UNIQUE,
  state_value LONGTEXT,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO selfv2_llm_providers (provider_key, label, endpoint, enabled) VALUES
('openai', 'OpenAI', 'https://api.openai.com/v1/chat/completions', 1),
('gemini', 'Google Gemini', 'https://generativelanguage.googleapis.com/v1beta/models', 1),
('ollama', 'Ollama (Local)', 'http://localhost:11434/api/generate', 1),
('custom', 'Custom / Internal LLM', '', 1);

INSERT IGNORE INTO selfv2_llm_models (provider_key, model_key, label, enabled, sort_order) VALUES
('openai', 'gpt-4o', 'gpt-4o', 1, 10),
('openai', 'gpt-4o-mini', 'gpt-4o-mini', 1, 20),
('openai', 'o3', 'o3', 1, 30),
('openai', 'o3-mini', 'o3-mini', 1, 40),
('gemini', 'gemini-1.5-pro', 'gemini-1.5-pro', 1, 10),
('gemini', 'gemini-1.5-flash', 'gemini-1.5-flash', 1, 20),
('ollama', 'llama3', 'llama3', 1, 10),
('ollama', 'mistral', 'mistral', 1, 20),
('ollama', 'phi3', 'phi3', 1, 30),
('ollama', 'mixtral', 'mixtral', 1, 40);

INSERT IGNORE INTO selfv2_llm_agents (name, provider_key, default_model, enabled, is_default, sort_order) VALUES
('Toze', 'openai', 'gpt-4o', 1, 1, 10),
('Sara', 'gemini', 'gemini-1.5-pro', 1, 0, 20);

INSERT IGNORE INTO selfv2_settings (setting_key, setting_value) VALUES
('default_agent', 'Toze'),
('mcp_enabled', '0'),
('theme_mode', 'dark');

INSERT IGNORE INTO selfv2_chat_actions (label, action_type, command_text, enabled, sort_order) VALUES
('Paste + Send', 'memory_paste_send', '{paste_send}', 1, 10),
('List Tools', 'prompt', 'lista tools', 1, 20),
('Projects', 'prompt', 'lista projects', 1, 30),
('System Status', 'prompt', 'system status', 1, 40),
('Agents', 'prompt', 'lista agents', 1, 50),
('Help', 'prompt', 'help', 1, 60);

INSERT IGNORE INTO selfv2_workspace_tabs (title, url, is_fixed, is_closable, sort_order) VALUES
('Admin', '/admin/', 1, 0, 10);