-- ensure-mqtt-schema.sql
-- Garantisce la colonna macchine.mqtt_device_id usata dal listener scanner
-- (Macchine::$mqttDeviceId, naming strategy `underscore`). Questo progetto NON
-- usa le Doctrine migrations (cartella migrations/ vuota): la colonna va creata
-- a mano sul DB online. Idempotente e portabile MySQL 8 / MariaDB.
--
--   docker compose exec -T php \
--     mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" < docker/mqtt/ensure-mqtt-schema.sql

SET @ddl := (
  SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE macchine ADD COLUMN mqtt_device_id VARCHAR(50) NULL',
    'DO 0'  -- colonna gia' presente: no-op
  )
  FROM information_schema.columns
  WHERE table_schema = DATABASE()
    AND table_name   = 'macchine'
    AND column_name  = 'mqtt_device_id'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Presenza del bridge (Macchine::$mqttOnline / $mqttPresenceAt). Indipendente dal
-- link: aggiornata dal worker sul topic retained c4rgo/<device>/status/bridge.
SET @ddl := (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE macchine ADD COLUMN mqtt_online TINYINT(1) NULL',
    'DO 0')
  FROM information_schema.columns
  WHERE table_schema = DATABASE() AND table_name = 'macchine' AND column_name = 'mqtt_online'
);
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @ddl := (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE macchine ADD COLUMN mqtt_presence_at DATETIME NULL',
    'DO 0')
  FROM information_schema.columns
  WHERE table_schema = DATABASE() AND table_name = 'macchine' AND column_name = 'mqtt_presence_at'
);
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Identita' del device sul relay c4rgo-dsc (Macchine::$dscDeviceId), per il
-- pulsante "Desktop remoto". Nullable: valorizzato solo per i device enrollati.
SET @ddl := (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE macchine ADD COLUMN dsc_device_id VARCHAR(100) NULL',
    'DO 0')
  FROM information_schema.columns
  WHERE table_schema = DATABASE() AND table_name = 'macchine' AND column_name = 'dsc_device_id'
);
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Storico delle transizioni di presenza (DevicePresenceLog). CREATE ... IF NOT EXISTS
-- e' gia' idempotente su MySQL 8 / MariaDB.
CREATE TABLE IF NOT EXISTS device_presence_log (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  macchina_id  INT         NOT NULL,
  device_id    VARCHAR(50) NOT NULL,
  online       TINYINT(1)  NOT NULL,
  changed_at   DATETIME    NOT NULL,
  INDEX idx_presence_macchina_changed (macchina_id, changed_at),
  CONSTRAINT fk_presence_macchina FOREIGN KEY (macchina_id) REFERENCES macchine (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Poi popola l'associazione device<->macchina per ogni scanner, es.:
--   UPDATE macchine SET mqtt_device_id = 'kiosk_36b94b28' WHERE id = 131;
