CREATE TABLE IF NOT EXISTS settings (
  setting_key VARCHAR(120) PRIMARY KEY,
  setting_value MEDIUMTEXT NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS memories (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_ref VARCHAR(190) NOT NULL DEFAULT 'default',
  parent_id BIGINT UNSIGNED NULL,
  kind VARCHAR(60) NOT NULL DEFAULT 'general',
  title VARCHAR(255) NOT NULL,
  content MEDIUMTEXT NOT NULL,
  importance TINYINT UNSIGNED NOT NULL DEFAULT 5,
  source_ref VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_memory_user_updated (user_ref, updated_at),
  INDEX idx_memory_parent (parent_id),
  CONSTRAINT fk_memory_parent FOREIGN KEY (parent_id) REFERENCES memories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS agent_runs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  request_id CHAR(36) NOT NULL UNIQUE,
  user_ref VARCHAR(190) NOT NULL DEFAULT 'default',
  conversation_ref VARCHAR(190) NULL,
  goal MEDIUMTEXT NOT NULL,
  mode VARCHAR(30) NOT NULL DEFAULT 'fast',
  tool_mode VARCHAR(30) NOT NULL DEFAULT 'chat',
  status ENUM('running','completed','failed') NOT NULL DEFAULT 'running',
  web_used TINYINT(1) NOT NULL DEFAULT 0,
  memory_ids_json TEXT NULL,
  response_text MEDIUMTEXT NULL,
  error_text TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  finished_at TIMESTAMP NULL DEFAULT NULL,
  INDEX idx_agent_user_created (user_ref, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS artifacts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  request_id CHAR(36) NULL,
  user_ref VARCHAR(190) NOT NULL DEFAULT 'default',
  kind VARCHAR(30) NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  stored_name VARCHAR(255) NOT NULL UNIQUE,
  mime_type VARCHAR(120) NOT NULL,
  size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
  download_token CHAR(64) NOT NULL UNIQUE,
  metadata_json MEDIUMTEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_artifact_user_created (user_ref, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;