descomplicar-meta-plugin/sql/create-lsp-tables.sql

-- ============================================================================
-- LSP Tables for Descomplicar Meta-Plugin
-- Database: ealmeida_desk24
-- Version: 1.1.0
-- ============================================================================

-- -----------------------------------------------------------------------------
-- Core LSP Table
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS cr_lsps (
  id INT AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(100) NOT NULL UNIQUE,
  name VARCHAR(200) NOT NULL,
  language VARCHAR(50) NOT NULL,
  package_manager ENUM('npm', 'pip', 'cargo', 'go', 'composer', 'gem', 'native') NOT NULL,
  package_name VARCHAR(200) NOT NULL,
  binary_path VARCHAR(500),
  config_file VARCHAR(200),
  capabilities JSON,
  status ENUM('active', 'inactive', 'deprecated') DEFAULT 'active',
  version VARCHAR(20),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_language (language),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Agent-LSP Relationship Table
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS cr_agent_lsps (
  id INT AUTO_INCREMENT PRIMARY KEY,
  agent_id INT NOT NULL,
  lsp_id INT NOT NULL,
  relationship_type ENUM('primary', 'recommended', 'available') DEFAULT 'recommended',
  priority INT DEFAULT 1,
  auto_start BOOLEAN DEFAULT FALSE,
  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),
  INDEX idx_agent (agent_id),
  INDEX idx_lsp (lsp_id),
  INDEX idx_relationship (relationship_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- LSP Usage Telemetry Table
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS cr_lsp_usage (
  id INT AUTO_INCREMENT PRIMARY KEY,
  lsp_id INT NOT NULL,
  agent_id INT,
  operation VARCHAR(50),
  response_time_ms INT,
  success BOOLEAN DEFAULT TRUE,
  error_message TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (lsp_id) REFERENCES cr_lsps(id) ON DELETE CASCADE,
  FOREIGN KEY (agent_id) REFERENCES cr_agents(id) ON DELETE SET NULL,
  INDEX idx_lsp (lsp_id),
  INDEX idx_created (created_at),
  INDEX idx_success (success)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Insert Default LSPs
-- -----------------------------------------------------------------------------
INSERT INTO cr_lsps (slug, name, language, package_manager, package_name, capabilities, status) VALUES
('typescript-language-server', 'TypeScript Language Server', 'TypeScript', 'npm', 'typescript-language-server',
 '{"completionProvider": true, "hoverProvider": true, "definitionProvider": true, "referencesProvider": true, "documentSymbolProvider": true, "codeActionProvider": true, "renameProvider": true}', 'active'),

('intelephense', 'Intelephense PHP', 'PHP', 'npm', 'intelephense',
 '{"completionProvider": true, "hoverProvider": true, "definitionProvider": true, "referencesProvider": true, "documentSymbolProvider": true, "workspaceSymbolProvider": true, "codeActionProvider": true, "renameProvider": true}', 'active'),

('pyright', 'Pyright Python', 'Python', 'pip', 'pyright',
 '{"completionProvider": true, "hoverProvider": true, "definitionProvider": true, "referencesProvider": true, "documentSymbolProvider": true, "renameProvider": true}', 'active'),

('gopls', 'Go Language Server', 'Go', 'go', 'golang.org/x/tools/gopls',
 '{"completionProvider": true, "hoverProvider": true, "definitionProvider": true, "referencesProvider": true, "documentSymbolProvider": true, "codeActionProvider": true, "renameProvider": true, "foldingRangeProvider": true}', 'active'),

('rust-analyzer', 'Rust Analyzer', 'Rust', 'cargo', 'rust-analyzer',
 '{"completionProvider": true, "hoverProvider": true, "definitionProvider": true, "referencesProvider": true, "documentSymbolProvider": true, "codeActionProvider": true, "renameProvider": true, "inlayHintProvider": true}', 'active'),

('yaml-language-server', 'YAML Language Server', 'YAML', 'npm', 'yaml-language-server',
 '{"completionProvider": true, "hoverProvider": true, "documentSymbolProvider": true, "validationProvider": true}', 'active'),

('bash-language-server', 'Bash Language Server', 'Bash', 'npm', 'bash-language-server',
 '{"completionProvider": true, "hoverProvider": true, "definitionProvider": true, "referencesProvider": true, "documentSymbolProvider": true}', 'active'),

('sql-language-server', 'SQL Language Server', 'SQL', 'npm', 'sql-language-server',
 '{"completionProvider": true, "hoverProvider": true, "documentSymbolProvider": true}', 'active'),

('vscode-css-languageserver', 'CSS Language Server', 'CSS', 'npm', 'vscode-css-languageserver-bin',
 '{"completionProvider": true, "hoverProvider": true, "documentSymbolProvider": true, "colorProvider": true}', 'active'),

('vscode-html-languageserver', 'HTML Language Server', 'HTML', 'npm', 'vscode-html-languageserver-bin',
 '{"completionProvider": true, "hoverProvider": true, "documentSymbolProvider": true, "foldingRangeProvider": true}', 'active'),

('vscode-json-languageserver', 'JSON Language Server', 'JSON', 'npm', 'vscode-json-languageserver-bin',
 '{"completionProvider": true, "hoverProvider": true, "documentSymbolProvider": true, "validationProvider": true}', 'active')

ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  capabilities = VALUES(capabilities),
  updated_at = NOW();

-- -----------------------------------------------------------------------------
-- Default Agent-LSP Mappings for Development Agents
-- -----------------------------------------------------------------------------

-- PHP Agents → intelephense
INSERT INTO cr_agent_lsps (agent_id, lsp_id, relationship_type, priority, auto_start)
SELECT a.id, l.id, 'primary', 1, TRUE
FROM cr_agents a, cr_lsps l
WHERE a.slug IN ('php-fullstack-engineer', 'wordpress-plugin-developer', 'perfex-crm-module-developer')
AND l.slug = 'intelephense'
ON DUPLICATE KEY UPDATE relationship_type = 'primary', auto_start = TRUE;

-- WordPress Agents → typescript-language-server (for Gutenberg/React)
INSERT INTO cr_agent_lsps (agent_id, lsp_id, relationship_type, priority)
SELECT a.id, l.id, 'recommended', 2
FROM cr_agents a, cr_lsps l
WHERE a.slug IN ('wordpress-plugin-developer', 'elementor-specialist')
AND l.slug = 'typescript-language-server'
ON DUPLICATE KEY UPDATE relationship_type = 'recommended';

-- JavaScript Agent → typescript-language-server
INSERT INTO cr_agent_lsps (agent_id, lsp_id, relationship_type, priority, auto_start)
SELECT a.id, l.id, 'primary', 1, TRUE
FROM cr_agents a, cr_lsps l
WHERE a.slug = 'javascript-fullstack-specialist'
AND l.slug = 'typescript-language-server'
ON DUPLICATE KEY UPDATE relationship_type = 'primary', auto_start = TRUE;

-- Database Agent → sql-language-server
INSERT INTO cr_agent_lsps (agent_id, lsp_id, relationship_type, priority, auto_start)
SELECT a.id, l.id, 'primary', 1, TRUE
FROM cr_agents a, cr_lsps l
WHERE a.slug = 'database-design-specialist'
AND l.slug = 'sql-language-server'
ON DUPLICATE KEY UPDATE relationship_type = 'primary', auto_start = TRUE;

-- Infrastructure Agents → yaml-language-server, bash-language-server
INSERT INTO cr_agent_lsps (agent_id, lsp_id, relationship_type, priority)
SELECT a.id, l.id, 'primary', 1
FROM cr_agents a, cr_lsps l
WHERE a.slug IN ('easypanel-specialist', 'cwp-server-manager', 'backup-specialist')
AND l.slug IN ('yaml-language-server', 'bash-language-server')
ON DUPLICATE KEY UPDATE relationship_type = 'primary';

-- Web Design Agents → CSS, HTML
INSERT INTO cr_agent_lsps (agent_id, lsp_id, relationship_type, priority)
SELECT a.id, l.id, 'primary', 1
FROM cr_agents a, cr_lsps l
WHERE a.slug IN ('web-designer', 'ui-designer', 'elementor-specialist')
AND l.slug IN ('vscode-css-languageserver', 'vscode-html-languageserver')
ON DUPLICATE KEY UPDATE relationship_type = 'primary';

-- N8N Automation → yaml-language-server, json-language-server
INSERT INTO cr_agent_lsps (agent_id, lsp_id, relationship_type, priority)
SELECT a.id, l.id, 'recommended', 1
FROM cr_agents a, cr_lsps l
WHERE a.slug = 'n8n-automation-expert'
AND l.slug IN ('yaml-language-server', 'vscode-json-languageserver')
ON DUPLICATE KEY UPDATE relationship_type = 'recommended';

-- Dev Helper → Multiple LSPs available
INSERT INTO cr_agent_lsps (agent_id, lsp_id, relationship_type, priority)
SELECT a.id, l.id, 'available', 3
FROM cr_agents a, cr_lsps l
WHERE a.slug = 'dev-helper'
AND l.status = 'active'
ON DUPLICATE KEY UPDATE priority = 3;

-- ============================================================================
-- Verification Query
-- ============================================================================
-- SELECT
--   l.slug as lsp,
--   l.language,
--   COUNT(DISTINCT al.agent_id) as agents_mapped
-- FROM cr_lsps l
-- LEFT JOIN cr_agent_lsps al ON l.id = al.lsp_id
-- GROUP BY l.id
-- ORDER BY agents_mapped DESC;