CREATE TABLE IF NOT EXISTS gi_connector_links (
  source VARCHAR(40) NOT NULL,
  external_id VARCHAR(190) NOT NULL,
  activity_id BIGINT UNSIGNED NOT NULL,
  fingerprint CHAR(64) NOT NULL,
  last_seen_at DATETIME NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  raw_json JSON NULL,
  PRIMARY KEY(source,external_id),
  KEY gi_connector_activity(activity_id),
  KEY gi_connector_active(source,active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS gi_activity_commercial (
  activity_id BIGINT UNSIGNED NOT NULL,
  source VARCHAR(40) NOT NULL,
  booking_url TEXT NULL,
  affiliate_url TEXT NULL,
  merchant_deep_link TEXT NULL,
  price DECIMAL(10,2) NULL,
  currency CHAR(3) NOT NULL DEFAULT 'EUR',
  updated_at DATETIME NOT NULL,
  PRIMARY KEY(activity_id,source)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS gi_connector_runs (
  run_id VARCHAR(40) PRIMARY KEY,
  source VARCHAR(40) NOT NULL,
  status VARCHAR(20) NOT NULL,
  seen_count INT UNSIGNED NOT NULL DEFAULT 0,
  created_count INT UNSIGNED NOT NULL DEFAULT 0,
  merged_count INT UNSIGNED NOT NULL DEFAULT 0,
  error_count INT UNSIGNED NOT NULL DEFAULT 0,
  started_at DATETIME NOT NULL,
  finished_at DATETIME NULL,
  KEY gi_runs_source(source,started_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS gi_geocode_cache (
  coord_key VARCHAR(64) PRIMARY KEY,
  latitude DECIMAL(10,7) NOT NULL,
  longitude DECIMAL(10,7) NOT NULL,
  city VARCHAR(120) NOT NULL DEFAULT '',
  zipcode VARCHAR(20) NOT NULL DEFAULT '',
  department VARCHAR(120) NOT NULL DEFAULT '',
  country VARCHAR(120) NOT NULL DEFAULT '',
  updated_at DATETIME NOT NULL,
  KEY gi_geocode_coords(latitude,longitude)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
