-- 02_create_tables.sql
-- Crear nuevas tablas necesarias para la migración

USE asterisk;

-- Tabla para todas las extensiones del sistema
CREATE TABLE IF NOT EXISTS extensiones_sistema (
    id INT AUTO_INCREMENT PRIMARY KEY,
    extension VARCHAR(10) UNIQUE NOT NULL,
    tipo ENUM('COORDINADOR','USUARIO','RESCATE','TAROT','INFO','CALL_CENTER_EXTERNO') NOT NULL,
    nombre VARCHAR(100) NOT NULL,
    password VARCHAR(50) DEFAULT '5252',
    telefono_conexion VARCHAR(50),
    grupo VARCHAR(50) COMMENT 'principal, respaldo, etc',
    prioridad INT DEFAULT 999,
    activo BOOLEAN DEFAULT TRUE,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    notas TEXT,
    INDEX idx_tipo (tipo),
    INDEX idx_extension (extension),
    INDEX idx_activo (activo, tipo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Todas las extensiones del sistema';

-- Tabla para métricas de duración media (para ordenar tarotistas)
CREATE TABLE IF NOT EXISTS metricas_duracion (
    id INT AUTO_INCREMENT PRIMARY KEY,
    agent_code VARCHAR(10) NOT NULL,
    fecha DATE NOT NULL,
    total_llamadas INT DEFAULT 0,
    duracion_total_segundos INT DEFAULT 0,
    duracion_media_segundos DECIMAL(10,2) GENERATED ALWAYS AS 
        (CASE WHEN total_llamadas > 0 THEN duracion_total_segundos / total_llamadas ELSE 0 END) STORED,
    ultima_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_agent_fecha (agent_code, fecha),
    INDEX idx_duracion_media (duracion_media_segundos DESC),
    INDEX idx_fecha (fecha),
    INDEX idx_agent (agent_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Métricas diarias para cálculo de duración media';

-- Vista para duración media del último mes
CREATE OR REPLACE VIEW agent_duracion_media AS
SELECT 
    agent_code,
    COUNT(DISTINCT fecha) as dias_trabajados,
    SUM(total_llamadas) as total_llamadas_mes,
    SUM(duracion_total_segundos) as duracion_total_mes,
    AVG(duracion_media_segundos) as duracion_media_mes,
    MAX(fecha) as ultima_fecha,
    CASE 
        WHEN SUM(total_llamadas) > 0 
        THEN SUM(duracion_total_segundos) / SUM(total_llamadas)
        ELSE 0 
    END as duracion_media_real
FROM metricas_duracion
WHERE fecha >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY agent_code
ORDER BY duracion_media_real DESC;

-- Tabla para configuración de colas dinámicas
CREATE TABLE IF NOT EXISTS queue_config (
    id INT AUTO_INCREMENT PRIMARY KEY,
    queue_name VARCHAR(50) UNIQUE NOT NULL,
    strategy VARCHAR(50) DEFAULT 'rrmemory',
    timeout INT DEFAULT 30,
    retry INT DEFAULT 5,
    wrapuptime INT DEFAULT 5,
    maxlen INT DEFAULT 50,
    joinempty VARCHAR(50) DEFAULT 'yes',
    leavewhenempty VARCHAR(50) DEFAULT 'no',
    announce_frequency INT DEFAULT 0,
    activo BOOLEAN DEFAULT TRUE,
    UNIQUE KEY uk_queue (queue_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Configuración dinámica de colas';

-- Insertar configuración de colas
INSERT INTO queue_config (queue_name, strategy, timeout, retry, wrapuptime, maxlen) VALUES
('coordinadores', 'leastrecent', 30, 5, 5, 50),
('tarot', 'fewestcalls', 45, 5, 10, 100),
('info', 'rrmemory', 30, 5, 5, 50),
('erotico', 'ringall', 60, 10, 15, 20),
('callcenter-externo', 'rrmemory', 30, 5, 5, 50)
ON DUPLICATE KEY UPDATE strategy=VALUES(strategy);

-- Tabla para tracking de captura/rescate
CREATE TABLE IF NOT EXISTS rescue_permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    rescue_extension VARCHAR(10) NOT NULL,
    can_rescue_from VARCHAR(200) COMMENT 'Lista de extensiones o grupos',
    activo BOOLEAN DEFAULT TRUE,
    UNIQUE KEY uk_rescue (rescue_extension)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Permisos de rescate/captura';

-- Configurar permisos de rescate
INSERT INTO rescue_permissions (rescue_extension, can_rescue_from) VALUES
('269', 'ALL'), -- TATIANA puede rescatar todas
('271', 'FORMACION'),
('272', 'COORDINADORES'),
('273', 'MESA1'),
('274', 'MESA2'),
('275', 'MESA3'),
('276', 'FORMACION'),
('277', 'COORDINADORES')
ON DUPLICATE KEY UPDATE can_rescue_from=VALUES(can_rescue_from);

-- Agregar columna tipo a agentes si no existe
ALTER TABLE agentes 
ADD COLUMN IF NOT EXISTS tipo VARCHAR(20) DEFAULT 'TAROT' AFTER apellidos,
ADD INDEX IF NOT EXISTS idx_tipo (tipo);

-- Crear trigger para actualizar métricas
DELIMITER $$

DROP TRIGGER IF EXISTS update_metricas_after_call$$
CREATE TRIGGER update_metricas_after_call
AFTER INSERT ON cdr
FOR EACH ROW
BEGIN
    DECLARE agent_code VARCHAR(10);
    
    -- Obtener código de agente si es una extensión 3XX
    IF NEW.dst LIKE '3%' AND LENGTH(NEW.dst) = 3 THEN
        SET agent_code = NEW.dst;
    ELSEIF NEW.src LIKE '3%' AND LENGTH(NEW.src) = 3 THEN
        SET agent_code = NEW.src;
    END IF;
    
    -- Si encontramos un agente, actualizar métricas
    IF agent_code IS NOT NULL AND NEW.disposition = 'ANSWERED' THEN
        INSERT INTO metricas_duracion 
        (agent_code, fecha, total_llamadas, duracion_total_segundos)
        VALUES 
        (agent_code, DATE(NEW.calldate), 1, NEW.duration)
        ON DUPLICATE KEY UPDATE
        total_llamadas = total_llamadas + 1,
        duracion_total_segundos = duracion_total_segundos + NEW.duration;
    END IF;
END$$

DELIMITER ;

-- Mensaje de finalización
SELECT 'Tablas creadas correctamente' as mensaje;