ALTER TABLE services ADD COLUMN google_index_status VARCHAR(32) NULL;
ALTER TABLE services ADD COLUMN google_index_checked_at DATETIME NULL;
ALTER TABLE services ADD COLUMN google_index_reason VARCHAR(255) NULL;
ALTER TABLE job_posts ADD COLUMN google_index_status VARCHAR(32) NULL;
ALTER TABLE job_posts ADD COLUMN google_index_checked_at DATETIME NULL;
ALTER TABLE job_posts ADD COLUMN google_index_reason VARCHAR(255) NULL;

CREATE TABLE google_index_check_runs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  run_date DATE NOT NULL,
  attempted INT UNSIGNED NOT NULL DEFAULT 0,
  indexed_count INT UNSIGNED NOT NULL DEFAULT 0,
  not_indexed_count INT UNSIGNED NOT NULL DEFAULT 0,
  failed_count INT UNSIGNED NOT NULL DEFAULT 0,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY google_index_check_runs_date_unique (run_date)
);

CREATE TABLE inspected_urls (
  url VARCHAR(2048) NOT NULL PRIMARY KEY,
  kind VARCHAR(16) NOT NULL,
  listing_id BIGINT NOT NULL,
  checked_at DATETIME NOT NULL,
  status VARCHAR(32) NOT NULL,
  reason VARCHAR(255) NULL,
  canonical VARCHAR(2048) NULL,
  attempts INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;