-- Ejecutar una sola vez en MariaDB/MySQL (sgpsphoenix_bd).
-- Conserva solamente el ultimo mensaje conocido para cada unidad.
-- El UPSERT sobrescribe la misma fila; esta tabla no guarda historial.

CREATE TABLE IF NOT EXISTS `tmp_cemo_geofence_last_message` (
    `origin_adm` INT NOT NULL,
    `cve_vehicle` INT NOT NULL,
    `msg_type` VARCHAR(20) NOT NULL,
    `folio_alert` BIGINT UNSIGNED NOT NULL,
    `alert_ticket` BIGINT UNSIGNED DEFAULT NULL,
    `alert_text` TEXT DEFAULT NULL,
    `date_insert` DATETIME(6) NOT NULL,
    `send_time` DATETIME(6) NOT NULL,
    `status` ENUM('pending', 'processed', 'invalid') NOT NULL DEFAULT 'pending',
    `destination_folio_alert` BIGINT UNSIGNED DEFAULT NULL,
    `processed_at` DATETIME DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`origin_adm`, `cve_vehicle`, `msg_type`),
    KEY `idx_cemo_geofence_last_status` (`status`, `date_insert`)
) ENGINE=InnoDB;