-- Migration: 003_add_relationships
-- Author: Descomplicar®
-- Date: 2026-02-01
-- Description: Tabelas de relacionamento entre componentes

-- UP

-- Agent ↔ MCP
CREATE TABLE IF NOT EXISTS cr_agent_mcps (
    id INT AUTO_INCREMENT PRIMARY KEY,
    agent_id INT NOT NULL,
    mcp_id INT NOT NULL,
    usage_type ENUM('primary', 'recommended', 'available') DEFAULT 'available',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (agent_id) REFERENCES cr_agents(id) ON DELETE CASCADE,
    FOREIGN KEY (mcp_id) REFERENCES cr_mcps(id) ON DELETE CASCADE,
    UNIQUE KEY unique_agent_mcp (agent_id, mcp_id)
);

-- Agent ↔ LSP
CREATE TABLE IF NOT EXISTS cr_agent_lsps (
    id INT AUTO_INCREMENT PRIMARY KEY,
    agent_id INT NOT NULL,
    lsp_id INT NOT NULL,
    usage_type ENUM('primary', 'recommended', 'available') DEFAULT 'available',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (agent_id) REFERENCES cr_agents(id) ON DELETE CASCADE,
    FOREIGN KEY (lsp_id) REFERENCES cr_lsps(id) ON DELETE CASCADE,
    UNIQUE KEY unique_agent_lsp (agent_id, lsp_id)
);

-- Agent ↔ Skill
CREATE TABLE IF NOT EXISTS cr_agent_skills (
    id INT AUTO_INCREMENT PRIMARY KEY,
    agent_id INT NOT NULL,
    skill_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (agent_id) REFERENCES cr_agents(id) ON DELETE CASCADE,
    FOREIGN KEY (skill_id) REFERENCES cr_skills(id) ON DELETE CASCADE,
    UNIQUE KEY unique_agent_skill (agent_id, skill_id)
);

-- Skill ↔ MCP
CREATE TABLE IF NOT EXISTS cr_skill_mcps (
    id INT AUTO_INCREMENT PRIMARY KEY,
    skill_id INT NOT NULL,
    mcp_id INT NOT NULL,
    usage_type ENUM('primary', 'recommended', 'available') DEFAULT 'available',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (skill_id) REFERENCES cr_skills(id) ON DELETE CASCADE,
    FOREIGN KEY (mcp_id) REFERENCES cr_mcps(id) ON DELETE CASCADE,
    UNIQUE KEY unique_skill_mcp (skill_id, mcp_id)
);

-- SDK ↔ Agent
CREATE TABLE IF NOT EXISTS cr_sdk_agents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sdk_id INT NOT NULL,
    agent_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (sdk_id) REFERENCES cr_sdks(id) ON DELETE CASCADE,
    FOREIGN KEY (agent_id) REFERENCES cr_agents(id) ON DELETE CASCADE,
    UNIQUE KEY unique_sdk_agent (sdk_id, agent_id)
);

-- SDK ↔ Skill
CREATE TABLE IF NOT EXISTS cr_sdk_skills (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sdk_id INT NOT NULL,
    skill_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (sdk_id) REFERENCES cr_sdks(id) ON DELETE CASCADE,
    FOREIGN KEY (skill_id) REFERENCES cr_skills(id) ON DELETE CASCADE,
    UNIQUE KEY unique_sdk_skill (sdk_id, skill_id)
);

-- SDK ↔ MCP
CREATE TABLE IF NOT EXISTS cr_sdk_mcps (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sdk_id INT NOT NULL,
    mcp_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (sdk_id) REFERENCES cr_sdks(id) ON DELETE CASCADE,
    FOREIGN KEY (mcp_id) REFERENCES cr_mcps(id) ON DELETE CASCADE,
    UNIQUE KEY unique_sdk_mcp (sdk_id, mcp_id)
);

-- Agent ↔ Agent (Colaborações)
CREATE TABLE IF NOT EXISTS cr_agent_collaborations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    agent_id INT NOT NULL,
    collaborator_id INT NOT NULL,
    collaboration_type VARCHAR(50) DEFAULT 'peer',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (agent_id) REFERENCES cr_agents(id) ON DELETE CASCADE,
    FOREIGN KEY (collaborator_id) REFERENCES cr_agents(id) ON DELETE CASCADE,
    UNIQUE KEY unique_collaboration (agent_id, collaborator_id)
);

-- Índices para performance
CREATE INDEX idx_agent_mcps_agent ON cr_agent_mcps(agent_id);
CREATE INDEX idx_agent_mcps_mcp ON cr_agent_mcps(mcp_id);
CREATE INDEX idx_agent_lsps_agent ON cr_agent_lsps(agent_id);
CREATE INDEX idx_agent_skills_agent ON cr_agent_skills(agent_id);
CREATE INDEX idx_skill_mcps_skill ON cr_skill_mcps(skill_id);
CREATE INDEX idx_sdk_agents_sdk ON cr_sdk_agents(sdk_id);

-- DOWN
DROP TABLE IF EXISTS cr_agent_collaborations;
DROP TABLE IF EXISTS cr_sdk_mcps;
DROP TABLE IF EXISTS cr_sdk_skills;
DROP TABLE IF EXISTS cr_sdk_agents;
DROP TABLE IF EXISTS cr_skill_mcps;
DROP TABLE IF EXISTS cr_agent_skills;
DROP TABLE IF EXISTS cr_agent_lsps;
DROP TABLE IF EXISTS cr_agent_mcps;