-- Debug-only OTA manifest rows and telemetry (FTT capture app). Used by web/api/debug_update_api.php.

CREATE TABLE IF NOT EXISTS `ftt_debug_update_publish` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `package_name` VARCHAR(256) NOT NULL DEFAULT '',
  `target_version_code` INT NOT NULL DEFAULT 0,
  `target_version_name` VARCHAR(64) NOT NULL DEFAULT '',
  `apk_url` TEXT NOT NULL,
  `apk_sha256` CHAR(64) NOT NULL DEFAULT '',
  `apk_size_bytes` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `enabled` TINYINT(1) NOT NULL DEFAULT 0,
  `kill_switch` TINYINT(1) NOT NULL DEFAULT 0,
  `kill_reason` VARCHAR(512) NOT NULL DEFAULT '',
  `force_install` TINYINT(1) NOT NULL DEFAULT 0,
  `auto_resume` TINYINT(1) NOT NULL DEFAULT 1,
  `send_config_on_start` TINYINT(1) NOT NULL DEFAULT 1,
  `resume_action` VARCHAR(32) NOT NULL DEFAULT 'SEND_BOTH',
  `next_poll_seconds` INT NOT NULL DEFAULT 300,
  `max_install_attempts_per_version` INT NOT NULL DEFAULT 2,
  `max_resume_failures` INT NOT NULL DEFAULT 2,
  `stop_after_success` TINYINT(1) NOT NULL DEFAULT 0,
  `server_nonce` VARCHAR(64) NOT NULL DEFAULT '',
  `issued_at_utc` DATETIME NULL,
  `expires_at_utc` DATETIME NULL,
  `expected_min_sdk` INT NOT NULL DEFAULT 0,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_pkg_ver` (`package_name`, `target_version_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ftt_debug_update_events` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `device_id` VARCHAR(128) NOT NULL DEFAULT '',
  `correlation_id` VARCHAR(64) NOT NULL DEFAULT '',
  `install_id` VARCHAR(64) NOT NULL DEFAULT '',
  `event_type` VARCHAR(64) NOT NULL DEFAULT '',
  `package_name` VARCHAR(256) NOT NULL DEFAULT '',
  `version_code` INT NOT NULL DEFAULT 0,
  `payload_json` LONGTEXT NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_device_created` (`device_id`, `created_at`),
  KEY `idx_correlation` (`correlation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;