CREATE TABLE IF NOT EXISTS booths (
  booth_id VARCHAR(80) NOT NULL PRIMARY KEY,
  label VARCHAR(160) NOT NULL,
  event_name VARCHAR(190) NULL,
  last_seen_at DATETIME NOT NULL,
  lumabooth_running TINYINT(1) NULL,
  printer_status VARCHAR(120) NULL,
  printer_name VARCHAR(160) NULL,
  network_status VARCHAR(120) NULL,
  app_version VARCHAR(40) NULL,
  agent_json LONGTEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_booths_last_seen (last_seen_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS booth_events (
  event_id VARCHAR(120) NOT NULL PRIMARY KEY,
  booth_id VARCHAR(80) NOT NULL,
  event_type VARCHAR(80) NOT NULL,
  param1 TEXT NULL,
  param2 TEXT NULL,
  param3 TEXT NULL,
  param4 TEXT NULL,
  payload_json LONGTEXT NULL,
  occurred_at DATETIME NOT NULL,
  received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_events_booth_time (booth_id, occurred_at),
  INDEX idx_events_type (event_type),
  CONSTRAINT fk_events_booth
    FOREIGN KEY (booth_id)
    REFERENCES booths (booth_id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS remote_commands (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  booth_id VARCHAR(80) NOT NULL,
  action VARCHAR(80) NOT NULL,
  payload_json LONGTEXT NULL,
  status ENUM('pending', 'done', 'error', 'cancelled') NOT NULL DEFAULT 'pending',
  result_json LONGTEXT NULL,
  error_message TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  picked_at DATETIME NULL,
  completed_at DATETIME NULL,
  INDEX idx_commands_booth_status (booth_id, status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;