-- SERP Tracker — submissions and keyword rank history tables

CREATE TABLE IF NOT EXISTS `serp_submissions` (
  `id`            INT(11)       NOT NULL AUTO_INCREMENT,
  `submission_id` VARCHAR(50)   NOT NULL,
  `project_id`    INT(11)       NOT NULL,
  `keywords`      TEXT,
  `weburl`        VARCHAR(2048),
  `search_engine` VARCHAR(100)  DEFAULT 'google.com',
  `country_code`  VARCHAR(10)   DEFAULT 'IN',
  `created_at`    DATETIME      DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_serp_submission_id` (`submission_id`),
  KEY `idx_serp_project_id` (`project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `serp_keyword_ranks` (
  `id`            INT(11)       NOT NULL AUTO_INCREMENT,
  `submission_id` VARCHAR(50)   NOT NULL,
  `keyword`       TEXT,
  `rank`          TEXT,
  `url`           TEXT,
  `device`        VARCHAR(20)   DEFAULT 'desktop',
  `location_name` VARCHAR(255),
  `country_code`  VARCHAR(50)   DEFAULT 'IN',
  `checked_at`    DATETIME      DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_serp_rank_sub_id` (`submission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
