CREATE TABLE jobs (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  token         CHAR(48)     NOT NULL UNIQUE,
  contact_email VARCHAR(190) NULL,
  old_ip        VARCHAR(45)  NOT NULL,
  old_host      VARCHAR(253) NULL,
  old_port      SMALLINT UNSIGNED NOT NULL DEFAULT 22,
  old_user      VARCHAR(32)  NOT NULL,
  old_pass_enc  TEXT NULL,
  new_ip        VARCHAR(45)  NOT NULL,
  new_port      SMALLINT UNSIGNED NOT NULL DEFAULT 22,
  new_user      VARCHAR(32)  NOT NULL,
  new_pass_enc  TEXT NULL,
  status        ENUM('queued','running','done','failed','cancelled') NOT NULL DEFAULT 'queued',
  progress      TINYINT UNSIGNED NOT NULL DEFAULT 0,
  step          VARCHAR(160) NOT NULL DEFAULT 'Waiting in queue',
  result_enc    MEDIUMTEXT NULL,
  error         TEXT NULL,
  client_ip     VARCHAR(45)  NOT NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  started_at    DATETIME NULL,
  finished_at   DATETIME NULL,
  KEY idx_status (status, id),
  KEY idx_client (client_ip, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE job_logs (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  job_id     BIGINT UNSIGNED NOT NULL,
  level      ENUM('info','warn','error') NOT NULL DEFAULT 'info',
  message    VARCHAR(1000) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_job (job_id, id),
  CONSTRAINT fk_log_job FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;