-- DROP TABLE IF EXISTS disconnection_logs;
-- DROP TABLE IF EXISTS disconnection_stats;
-- DROP TABLE IF EXISTS connection_logs;
-- DROP TABLE IF EXISTS device_events;

-- =====================================
-- TABLA PRINCIPAL DE EVENTOS
-- =====================================
CREATE TABLE IF NOT EXISTS device_events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    device_id BIGINT UNSIGNED NOT NULL,
    event_type ENUM('connection', 'disconnection') NOT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    -- Datos de conexión
    ssid VARCHAR(64) NULL,
    wifi_channel TINYINT UNSIGNED NULL,
    ip_address VARCHAR(45) NULL,
    rssi SMALLINT NULL,

    -- Datos de desconexión
    close_code INT NULL,
    close_reason VARCHAR(255) NULL,
    last_pong_ms BIGINT UNSIGNED NULL,
    ping_count INT NULL,
    last_heartbeat_ms BIGINT UNSIGNED NULL,
    total_disconnections INT DEFAULT NULL,

    INDEX idx_device_event (device_id, event_type),
    INDEX idx_device_time (device_id, created_at),
    FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
);

-- =====================================
-- VISTAS LÓGICAS
-- =====================================
CREATE OR REPLACE VIEW connection_logs AS
SELECT 
    id,
    device_id,
    ssid,
    wifi_channel,
    ip_address,
    rssi,
    created_at AS connected_at
FROM device_events
WHERE event_type = 'connection';

CREATE OR REPLACE VIEW disconnection_logs AS
SELECT 
    id,
    device_id,
    created_at AS disconnected_at,
    close_code,
    close_reason,
    last_pong_ms,
    ping_count,
    last_heartbeat_ms,
    total_disconnections
FROM device_events
WHERE event_type = 'disconnection';

-- =====================================
-- TABLA DE ESTADÍSTICAS
-- =====================================
CREATE TABLE IF NOT EXISTS disconnection_stats (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    device_id BIGINT UNSIGNED NOT NULL UNIQUE,
    total_disconnections INT DEFAULT 0,
    first_disconnection TIMESTAMP NULL,
    last_disconnection TIMESTAMP NULL,
    avg_disconnections_per_day DECIMAL(10,2) DEFAULT 0.00,
    max_disconnections_per_day INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
);