-- Diseño de sincronización entre tarotistas y agentes
-- Fecha: 2025-07-30

-- 1. Crear tabla de mapeo en asterisk (accesible por ambos sistemas)
CREATE TABLE IF NOT EXISTS asterisk.sync_tarotistas_agentes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tarotista_id INT NOT NULL,
    tarotista_extension VARCHAR(20) NOT NULL,
    agente_usuario VARCHAR(20) NOT NULL,
    last_sync_panel_to_asterisk TIMESTAMP NULL,
    last_sync_asterisk_to_panel TIMESTAMP NULL,
    sync_enabled TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_tarotista (tarotista_id),
    UNIQUE KEY uk_extension (tarotista_extension),
    UNIQUE KEY uk_agente (agente_usuario),
    INDEX idx_sync_enabled (sync_enabled)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Mapeo entre tarotistas del panel y agentes de asterisk';

-- 2. Crear tabla de logs de sincronización
CREATE TABLE IF NOT EXISTS asterisk.sync_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sync_type ENUM('panel_to_asterisk', 'asterisk_to_panel', 'bidirectional') NOT NULL,
    table_affected VARCHAR(50) NOT NULL,
    record_id VARCHAR(50) NOT NULL,
    field_name VARCHAR(50),
    old_value TEXT,
    new_value TEXT,
    sync_status ENUM('success', 'failed', 'skipped') NOT NULL,
    error_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by VARCHAR(50),
    INDEX idx_sync_type (sync_type),
    INDEX idx_created_at (created_at),
    INDEX idx_status (sync_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Logs de sincronización entre sistemas';

-- 3. Añadir campos faltantes en agentes para sincronización completa
ALTER TABLE asterisk.agentes 
ADD COLUMN IF NOT EXISTS apellidos VARCHAR(100) AFTER nombre,
ADD COLUMN IF NOT EXISTS descripcion TEXT AFTER apellidos,
ADD COLUMN IF NOT EXISTS activo TINYINT(1) DEFAULT 1 AFTER dnd_status,
ADD COLUMN IF NOT EXISTS orden_display INT DEFAULT 0 AFTER activo,
ADD COLUMN IF NOT EXISTS created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER orden_display,
ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at,
ADD COLUMN IF NOT EXISTS sync_with_panel TINYINT(1) DEFAULT 1 COMMENT 'Si debe sincronizarse con panel_tarot';

-- 4. Añadir campo en tarotistas para tracking
ALTER TABLE panel_tarot.tarotistas 
ADD COLUMN IF NOT EXISTS sync_with_asterisk TINYINT(1) DEFAULT 1 COMMENT 'Si debe sincronizarse con asterisk',
ADD COLUMN IF NOT EXISTS last_sync_from_asterisk TIMESTAMP NULL AFTER sync_with_asterisk,
ADD COLUMN IF NOT EXISTS agente_usuario VARCHAR(20) AFTER extension COMMENT 'Usuario correspondiente en tabla agentes';

-- 5. Función para mapear estados de tarotistas a agentes
DELIMITER $$
CREATE FUNCTION IF NOT EXISTS asterisk.map_tarotista_to_agente_state(
    estado_tarotista VARCHAR(20)
) RETURNS JSON
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE result JSON;
    
    CASE estado_tarotista
        WHEN 'offline' THEN
            SET result = JSON_OBJECT(
                'estado_login', 0,
                'estado_ocupacion', 0,
                'dnd_status', 0
            );
        WHEN 'disponible' THEN
            SET result = JSON_OBJECT(
                'estado_login', 1,
                'estado_ocupacion', 0,
                'dnd_status', 0
            );
        WHEN 'ocupado' THEN
            SET result = JSON_OBJECT(
                'estado_login', 1,
                'estado_ocupacion', 1,
                'dnd_status', 0
            );
        WHEN 'pausa', 'bloqueado' THEN
            SET result = JSON_OBJECT(
                'estado_login', 1,
                'estado_ocupacion', 0,
                'dnd_status', 1
            );
        WHEN 'esperando', 'reservado', 'solicitado', 'sonando' THEN
            SET result = JSON_OBJECT(
                'estado_login', 1,
                'estado_ocupacion', 0,
                'dnd_status', 0
            );
        ELSE
            SET result = JSON_OBJECT(
                'estado_login', 0,
                'estado_ocupacion', 0,
                'dnd_status', 0
            );
    END CASE;
    
    RETURN result;
END$$
DELIMITER ;

-- 6. Función para mapear estados de agentes a tarotistas
DELIMITER $$
CREATE FUNCTION IF NOT EXISTS asterisk.map_agente_to_tarotista_state(
    login_state TINYINT,
    busy_state TINYINT,
    dnd_state TINYINT
) RETURNS VARCHAR(20)
DETERMINISTIC
READS SQL DATA
BEGIN
    IF login_state = 0 THEN
        RETURN 'offline';
    ELSEIF busy_state = 1 THEN
        RETURN 'ocupado';
    ELSEIF dnd_state = 1 THEN
        RETURN 'pausa';
    ELSE
        RETURN 'disponible';
    END IF;
END$$
DELIMITER ;

-- 7. Inicializar mapeo con datos existentes
-- Esto asume que extension en tarotistas = usuario en agentes
INSERT INTO asterisk.sync_tarotistas_agentes (
    tarotista_id,
    tarotista_extension,
    agente_usuario,
    sync_enabled
)
SELECT 
    t.id,
    t.extension,
    a.usuario,
    1
FROM panel_tarot.tarotistas t
INNER JOIN asterisk.agentes a ON t.extension = a.usuario
WHERE t.activo = 1
ON DUPLICATE KEY UPDATE
    updated_at = CURRENT_TIMESTAMP;

-- 8. Vista unificada para facilitar consultas
CREATE OR REPLACE VIEW asterisk.v_tarotistas_agentes_sync AS
SELECT 
    s.id as sync_id,
    t.id as tarotista_id,
    t.extension as tarotista_extension,
    t.nombre as tarotista_nombre,
    t.apellidos as tarotista_apellidos,
    t.estado_actual as tarotista_estado,
    t.activo as tarotista_activo,
    t.ultima_actualizacion as tarotista_ultima_act,
    a.id as agente_id,
    a.usuario as agente_usuario,
    a.nombre as agente_nombre,
    a.estado_login as agente_login,
    a.estado_ocupacion as agente_ocupado,
    a.dnd_status as agente_dnd,
    a.activo as agente_activo,
    s.last_sync_panel_to_asterisk,
    s.last_sync_asterisk_to_panel,
    s.sync_enabled,
    -- Estados mapeados
    asterisk.map_agente_to_tarotista_state(
        a.estado_login, 
        a.estado_ocupacion, 
        a.dnd_status
    ) as agente_estado_como_tarotista,
    JSON_EXTRACT(
        asterisk.map_tarotista_to_agente_state(t.estado_actual),
        '$'
    ) as tarotista_estado_como_agente
FROM asterisk.sync_tarotistas_agentes s
INNER JOIN panel_tarot.tarotistas t ON s.tarotista_id = t.id
INNER JOIN asterisk.agentes a ON s.agente_usuario = a.usuario
WHERE s.sync_enabled = 1;

-- 9. Trigger para auto-sync cuando se actualiza un agente
DELIMITER $$
CREATE TRIGGER IF NOT EXISTS asterisk.agentes_after_update_sync
AFTER UPDATE ON asterisk.agentes
FOR EACH ROW
BEGIN
    DECLARE sync_id INT;
    
    -- Solo sincronizar si está habilitado y hay cambios relevantes
    IF NEW.sync_with_panel = 1 AND (
        OLD.estado_login != NEW.estado_login OR
        OLD.estado_ocupacion != NEW.estado_ocupacion OR
        OLD.dnd_status != NEW.dnd_status OR
        OLD.nombre != NEW.nombre OR
        OLD.activo != NEW.activo
    ) THEN
        -- Registrar en tabla temporal para proceso batch
        INSERT INTO asterisk.sync_pending_changes 
        (table_name, record_id, change_type, created_at)
        VALUES ('agentes', NEW.usuario, 'update', NOW())
        ON DUPLICATE KEY UPDATE created_at = NOW();
    END IF;
END$$
DELIMITER ;

-- 10. Tabla para cambios pendientes de sincronización
CREATE TABLE IF NOT EXISTS asterisk.sync_pending_changes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(50) NOT NULL,
    record_id VARCHAR(50) NOT NULL,
    change_type ENUM('insert', 'update', 'delete') NOT NULL,
    processed TINYINT(1) DEFAULT 0,
    processed_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_table_record (table_name, record_id),
    INDEX idx_processed (processed),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;