-- Migration: 005_add_archive_tables
-- Author: Descomplicar®
-- Date: 2026-02-04
-- Description: Tabelas de arquivo para telemetria antiga

-- UP

-- Archive de uso de agentes
CREATE TABLE IF NOT EXISTS cr_agent_usage_archive (
    id INT PRIMARY KEY,
    agent_id INT NOT NULL,
    session_id VARCHAR(100),
    invocations INT DEFAULT 1,
    success_count INT DEFAULT 0,
    error_count INT DEFAULT 0,
    total_duration_ms INT DEFAULT 0,
    created_at TIMESTAMP,
    archived_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Archive de uso de skills
CREATE TABLE IF NOT EXISTS cr_skill_usage_archive (
    id INT PRIMARY KEY,
    skill_id INT NOT NULL,
    session_id VARCHAR(100),
    invocations INT DEFAULT 1,
    success_count INT DEFAULT 0,
    error_count INT DEFAULT 0,
    created_at TIMESTAMP,
    archived_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Archive de uso de ferramentas MCP
CREATE TABLE IF NOT EXISTS cr_mcp_tool_usage_archive (
    id INT PRIMARY KEY,
    mcp_id INT NOT NULL,
    tool_name VARCHAR(100) NOT NULL,
    session_id VARCHAR(100),
    invocations INT DEFAULT 1,
    success_count INT DEFAULT 0,
    error_count INT DEFAULT 0,
    avg_latency_ms INT DEFAULT 0,
    created_at TIMESTAMP,
    archived_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Archive de uso de LSPs
CREATE TABLE IF NOT EXISTS cr_lsp_usage_archive (
    id INT PRIMARY KEY,
    lsp_id INT NOT NULL,
    session_id VARCHAR(100),
    invocations INT DEFAULT 1,
    completions INT DEFAULT 0,
    diagnostics INT DEFAULT 0,
    created_at TIMESTAMP,
    archived_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Índices para consultas históricas
CREATE INDEX idx_agent_archive_date ON cr_agent_usage_archive(created_at);
CREATE INDEX idx_agent_archive_archived ON cr_agent_usage_archive(archived_at);
CREATE INDEX idx_skill_archive_date ON cr_skill_usage_archive(created_at);
CREATE INDEX idx_mcp_archive_date ON cr_mcp_tool_usage_archive(created_at);
CREATE INDEX idx_lsp_archive_date ON cr_lsp_usage_archive(created_at);

-- DOWN
DROP TABLE IF EXISTS cr_lsp_usage_archive;
DROP TABLE IF EXISTS cr_mcp_tool_usage_archive;
DROP TABLE IF EXISTS cr_skill_usage_archive;
DROP TABLE IF EXISTS cr_agent_usage_archive;