CREATE TABLE IF NOT EXISTS desktop_clients (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  public_id CHAR(32) NOT NULL UNIQUE,
  token_hash CHAR(64) NOT NULL,
  machine_id VARCHAR(128) DEFAULT NULL,
  display_name VARCHAR(160) DEFAULT NULL,
  app_version VARCHAR(40) DEFAULT NULL,
  last_ip VARCHAR(64) DEFAULT NULL,
  last_user_agent VARCHAR(255) DEFAULT NULL,
  last_seen_at DATETIME DEFAULT NULL,
  revoked_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_token_hash (token_hash),
  INDEX idx_machine_id (machine_id),
  INDEX idx_revoked_at (revoked_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS mobile_devices (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  desktop_id BIGINT UNSIGNED NOT NULL,
  public_id CHAR(32) NOT NULL UNIQUE,
  token_hash CHAR(64) NOT NULL,
  device_name VARCHAR(160) DEFAULT NULL,
  android_version VARCHAR(40) DEFAULT NULL,
  app_version VARCHAR(40) DEFAULT NULL,
  status ENUM('paired','online','offline','revoked') NOT NULL DEFAULT 'paired',
  last_ip VARCHAR(64) DEFAULT NULL,
  last_seen_at DATETIME DEFAULT NULL,
  paired_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  revoked_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_desktop_id (desktop_id),
  INDEX idx_token_hash (token_hash),
  INDEX idx_status (status),
  CONSTRAINT fk_mobile_devices_desktop
    FOREIGN KEY (desktop_id) REFERENCES desktop_clients(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pairing_codes (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  desktop_id BIGINT UNSIGNED NOT NULL,
  code_hash CHAR(64) NOT NULL UNIQUE,
  code_preview VARCHAR(16) NOT NULL,
  expires_at DATETIME NOT NULL,
  used_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_desktop_expires (desktop_id, expires_at),
  CONSTRAINT fk_pairing_codes_desktop
    FOREIGN KEY (desktop_id) REFERENCES desktop_clients(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS mobile_commands (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  public_id CHAR(32) NOT NULL UNIQUE,
  desktop_id BIGINT UNSIGNED NOT NULL,
  device_id BIGINT UNSIGNED NOT NULL,
  command_type VARCHAR(80) NOT NULL,
  payload_json JSON DEFAULT NULL,
  status ENUM('queued','delivered','completed','failed','expired') NOT NULL DEFAULT 'queued',
  result_json JSON DEFAULT NULL,
  expires_at DATETIME NOT NULL,
  delivered_at DATETIME DEFAULT NULL,
  completed_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_device_status (device_id, status),
  INDEX idx_desktop_device (desktop_id, device_id),
  INDEX idx_expires_at (expires_at),
  CONSTRAINT fk_mobile_commands_desktop
    FOREIGN KEY (desktop_id) REFERENCES desktop_clients(id)
    ON DELETE CASCADE,
  CONSTRAINT fk_mobile_commands_device
    FOREIGN KEY (device_id) REFERENCES mobile_devices(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS mobile_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  public_id CHAR(32) NOT NULL UNIQUE,
  desktop_id BIGINT UNSIGNED NOT NULL,
  device_id BIGINT UNSIGNED NOT NULL,
  event_type VARCHAR(80) NOT NULL,
  payload_json JSON DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  consumed_at DATETIME DEFAULT NULL,
  INDEX idx_desktop_created (desktop_id, created_at),
  INDEX idx_device_created (device_id, created_at),
  CONSTRAINT fk_mobile_events_desktop
    FOREIGN KEY (desktop_id) REFERENCES desktop_clients(id)
    ON DELETE CASCADE,
  CONSTRAINT fk_mobile_events_device
    FOREIGN KEY (device_id) REFERENCES mobile_devices(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS latest_screen_frames (
  device_id BIGINT UNSIGNED PRIMARY KEY,
  desktop_id BIGINT UNSIGNED NOT NULL,
  mime_type VARCHAR(80) NOT NULL DEFAULT 'image/jpeg',
  frame_base64 MEDIUMTEXT NOT NULL,
  captured_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_desktop_id (desktop_id),
  CONSTRAINT fk_latest_frames_desktop
    FOREIGN KEY (desktop_id) REFERENCES desktop_clients(id)
    ON DELETE CASCADE,
  CONSTRAINT fk_latest_frames_device
    FOREIGN KEY (device_id) REFERENCES mobile_devices(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;