CREATE DATABASE IF NOT EXISTS onepage_apps CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE onepage_apps;

CREATE TABLE IF NOT EXISTS runs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  slug VARCHAR(100) NOT NULL,
  ip VARCHAR(45) NOT NULL,
  input_json JSON NOT NULL,
  output_json JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_runs_slug_ip_created_at (slug, ip, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS rate_limits (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  ip VARCHAR(45) NOT NULL,
  slug VARCHAR(100) NOT NULL,
  ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_rate_limits_ip_slug_ts (ip, slug, ts),
  KEY idx_rate_limits_ts (ts)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS trackers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  slug VARCHAR(100) NOT NULL,
  ip VARCHAR(45) NOT NULL,
  keyword VARCHAR(80) NOT NULL,
  city VARCHAR(80) NOT NULL,
  country ENUM('us','gb','ca','au','de','fr') NOT NULL,
  radius_km SMALLINT UNSIGNED NOT NULL DEFAULT 25,
  window_days SMALLINT UNSIGNED NOT NULL DEFAULT 90,
  compare_keyword VARCHAR(80) DEFAULT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_sampled_at TIMESTAMP NULL DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_trackers_slug_ip (slug, ip),
  KEY idx_trackers_lookup (country, city, keyword),
  KEY idx_trackers_is_active (is_active),
  KEY idx_trackers_last_sampled_at (last_sampled_at),
  KEY idx_trackers_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS samples (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tracker_id BIGINT UNSIGNED NOT NULL,
  sampled_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  sample_date DATE GENERATED ALWAYS AS (DATE(sampled_at)) STORED,
  job_count INT UNSIGNED NOT NULL,
  source VARCHAR(32) NOT NULL DEFAULT 'adzuna',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_samples_tracker_date (tracker_id, sample_date),
  KEY idx_samples_tracker_sampled_at (tracker_id, sampled_at),
  KEY idx_samples_sample_date (sample_date),
  CONSTRAINT fk_samples_tracker FOREIGN KEY (tracker_id) REFERENCES trackers (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS cache (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  slug VARCHAR(100) NOT NULL,
  cache_key VARCHAR(255) NOT NULL,
  value_json JSON NOT NULL,
  result_count INT UNSIGNED DEFAULT NULL,
  country ENUM('us','gb','ca','au','de','fr') DEFAULT NULL,
  keyword VARCHAR(80) DEFAULT NULL,
  city VARCHAR(80) DEFAULT NULL,
  radius_km SMALLINT UNSIGNED DEFAULT NULL,
  expires_at DATETIME NOT NULL,
  last_accessed_at DATETIME DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_cache_slug_key (slug, cache_key),
  KEY idx_cache_expires_at (expires_at),
  KEY idx_cache_slug_expires (slug, expires_at),
  KEY idx_cache_lookup (country, city, keyword)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;