CREATE TABLE IF NOT EXISTS local_gateways (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  event_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(190) NOT NULL,
  gateway_code VARCHAR(80) NOT NULL,
  token_hash CHAR(64) NOT NULL,
  status ENUM('pending','active','suspended','revoked') NOT NULL DEFAULT 'pending',
  last_seen_at DATETIME NULL,
  last_sync_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY local_gateways_gateway_code_unique (gateway_code),
  KEY local_gateways_event_id_idx (event_id),
  KEY local_gateways_status_idx (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS scanner_devices (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  event_id BIGINT UNSIGNED NOT NULL,
  gateway_id BIGINT UNSIGNED NULL,
  device_name VARCHAR(190) NOT NULL,
  device_code VARCHAR(80) NOT NULL,
  role ENUM('entry_scanner','exit_scanner','mixed_scanner','vendor_pos','nfc_writer') NOT NULL DEFAULT 'mixed_scanner',
  assigned_gate_id BIGINT UNSIGNED NULL,
  assigned_zone_id BIGINT UNSIGNED NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'active',
  last_seen_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY scanner_devices_device_code_unique (device_code),
  KEY scanner_devices_event_id_idx (event_id),
  KEY scanner_devices_gateway_id_idx (gateway_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sync_batches (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  event_id BIGINT UNSIGNED NOT NULL,
  gateway_id BIGINT UNSIGNED NOT NULL,
  direction ENUM('pull','push') NOT NULL,
  status ENUM('pending','running','success','failed','conflict') NOT NULL DEFAULT 'pending',
  started_at DATETIME NULL,
  finished_at DATETIME NULL,
  summary_json JSON NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY sync_batches_gateway_id_idx (gateway_id),
  KEY sync_batches_event_id_idx (event_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sync_outbox (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  event_id BIGINT UNSIGNED NOT NULL,
  gateway_id BIGINT UNSIGNED NULL,
  entity_type VARCHAR(80) NOT NULL,
  entity_id VARCHAR(80) NOT NULL,
  action VARCHAR(80) NOT NULL,
  payload_json JSON NOT NULL,
  idempotency_key VARCHAR(190) NOT NULL,
  status ENUM('pending','synced','failed','conflict') NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  synced_at DATETIME NULL,
  UNIQUE KEY sync_outbox_idempotency_unique (idempotency_key),
  KEY sync_outbox_event_gateway_idx (event_id, gateway_id),
  KEY sync_outbox_status_idx (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;