-- Script para crear tabla unificada de estados
-- Esta tabla será la fuente única de verdad para estados de agentes

USE asterisk;

-- Crear tabla de estados unificados si no existe
CREATE TABLE IF NOT EXISTS agent_unified_state (
    agent_code VARCHAR(10) PRIMARY KEY,
    agent_name VARCHAR(100),
    login_state TINYINT DEFAULT 0,  -- 0=logout, 1=login
    occupation_state TINYINT DEFAULT 0, -- 0=libre, 1=ocupado
    dnd_state TINYINT DEFAULT 0, -- 0=disponible, 1=DND
    current_call_id VARCHAR(50),
    last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    update_source VARCHAR(50), -- 'AGI', 'PANEL', 'PBX', etc
    INDEX idx_last_update (last_update),
    INDEX idx_states (login_state, occupation_state, dnd_state)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Poblar con datos actuales de agentes
INSERT IGNORE INTO agent_unified_state (agent_code, agent_name, login_state, occupation_state, dnd_state, update_source)
SELECT 
    usuario as agent_code,
    nombre as agent_name,
    estado_login as login_state,
    estado_ocupacion as occupation_state,
    dnd_status as dnd_state,
    'INITIAL' as update_source
FROM agentes
WHERE activo = 1;

-- Crear trigger para sincronizar cambios desde agentes
DELIMITER $$

DROP TRIGGER IF EXISTS sync_agent_state_update$$
CREATE TRIGGER sync_agent_state_update
AFTER UPDATE ON agentes
FOR EACH ROW
BEGIN
    IF NEW.activo = 1 THEN
        INSERT INTO agent_unified_state (
            agent_code, agent_name, login_state, occupation_state, dnd_state, update_source
        ) VALUES (
            NEW.usuario, NEW.nombre, NEW.estado_login, NEW.estado_ocupacion, NEW.dnd_status, 'AGENTES_TABLE'
        )
        ON DUPLICATE KEY UPDATE
            agent_name = NEW.nombre,
            login_state = NEW.estado_login,
            occupation_state = NEW.estado_ocupacion,
            dnd_state = NEW.dnd_status,
            update_source = 'AGENTES_TABLE';
    END IF;
END$$

DELIMITER ;

-- Vista para facilitar consultas
CREATE OR REPLACE VIEW v_agent_current_state AS
SELECT 
    aus.agent_code,
    aus.agent_name,
    aus.login_state,
    aus.occupation_state,
    aus.dnd_state,
    CASE 
        WHEN aus.login_state = 0 THEN 'Offline'
        WHEN aus.occupation_state = 1 THEN 'Ocupado'
        WHEN aus.dnd_state = 1 THEN 'No Molestar'
        ELSE 'Disponible'
    END as estado_texto,
    aus.current_call_id,
    aus.last_update,
    aus.update_source,
    TIMESTAMPDIFF(SECOND, aus.last_update, NOW()) as seconds_since_update
FROM agent_unified_state aus
ORDER BY aus.agent_code;