-- Background job queue for SERP ranking runs
CREATE TABLE IF NOT EXISTS `serp_jobs` (
  `id`            INT           NOT NULL AUTO_INCREMENT,
  `job_id`        VARCHAR(80)   NOT NULL,
  `submission_id` VARCHAR(50)   NOT NULL,
  `status`        ENUM('queued','processing','done','failed') NOT NULL DEFAULT 'queued',
  `percent`       TINYINT       NOT NULL DEFAULT 0,
  `message`       TEXT,
  `device`        VARCHAR(20)   NOT NULL DEFAULT 'desktop',
  `search_type`   VARCHAR(20)   NOT NULL DEFAULT 'normal',
  `city_name`     VARCHAR(255)  NOT NULL DEFAULT '',
  `country_code`  VARCHAR(10)   NOT NULL DEFAULT 'IN',
  `created_at`    DATETIME      DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    DATETIME      DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_serp_job_id` (`job_id`),
  KEY `idx_serp_job_sub_id` (`submission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
