-- =============================================================================
-- AI SEO Manager — 001_core
-- MySQL 8.0+ / MariaDB 10.6+   utf8mb4_0900_ai_ci falls back to utf8mb4_unicode_ci
-- Idempotent: every statement is CREATE TABLE IF NOT EXISTS.
-- =============================================================================


-- ---------------------------------------------------------------- registry ---

CREATE TABLE IF NOT EXISTS site (
  id                INT UNSIGNED     NOT NULL AUTO_INCREMENT,
  name              VARCHAR(120)     NOT NULL,
  primary_domain    VARCHAR(255)     NOT NULL,
  division          VARCHAR(60)      NULL,
  default_country   CHAR(2)          NOT NULL DEFAULT 'AE',
  default_language  CHAR(2)          NOT NULL DEFAULT 'en',
  cms               VARCHAR(40)      NULL,
  gsc_property      VARCHAR(255)     NULL,
  ga4_property_id   VARCHAR(40)      NULL,
  crawl_max_urls    INT UNSIGNED     NOT NULL DEFAULT 5000,
  crawl_concurrency TINYINT UNSIGNED NOT NULL DEFAULT 3,
  respect_crawl_delay TINYINT(1)     NOT NULL DEFAULT 1,
  is_active         TINYINT(1)       NOT NULL DEFAULT 1,
  created_at        TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_site_domain (primary_domain),
  KEY idx_site_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- www / non-www / m-dot / ccTLD variants. Needed by T091, T123.
CREATE TABLE IF NOT EXISTS site_host (
  id                INT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_id           INT UNSIGNED NOT NULL,
  host              VARCHAR(255) NOT NULL,
  scheme            ENUM('http','https') NOT NULL DEFAULT 'https',
  is_canonical_host TINYINT(1)   NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  UNIQUE KEY uq_host (site_id, scheme, host),
  CONSTRAINT fk_host_site FOREIGN KEY (site_id) REFERENCES site (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------------- runs ---

CREATE TABLE IF NOT EXISTS audit_run (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_id       INT UNSIGNED    NOT NULL,
  run_type      ENUM('full','canary','render_sample','ai_seo','offpage','site_analyze') NOT NULL DEFAULT 'full',
  status        ENUM('queued','running','completed','failed','cancelled') NOT NULL DEFAULT 'queued',
  started_at    DATETIME        NULL,
  finished_at   DATETIME        NULL,
  -- provenance: without these, every trend chart is a lie the first time a weight is tuned
  scoring_model_version VARCHAR(20) NOT NULL DEFAULT 'v1',
  crawler_version       VARCHAR(20) NOT NULL DEFAULT 'v1',
  urls_discovered INT UNSIGNED NOT NULL DEFAULT 0,
  urls_crawled    INT UNSIGNED NOT NULL DEFAULT 0,
  urls_rendered   INT UNSIGNED NOT NULL DEFAULT 0,
  api_cost_usd    DECIMAL(10,4) NOT NULL DEFAULT 0,
  config_json     JSON          NULL,
  error_message   TEXT          NULL,
  created_at      TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_run_site_created (site_id, created_at),
  KEY idx_run_status (status, run_type),
  CONSTRAINT fk_run_site FOREIGN KEY (site_id) REFERENCES site (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -------------------------------------------------------- crawl artefacts ---

CREATE TABLE IF NOT EXISTS crawled_url (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  run_id          BIGINT UNSIGNED NOT NULL,
  url             VARCHAR(2048)   NOT NULL,
  url_hash        BINARY(20)      NOT NULL,          -- sha1(normalised url), the join key
  status_code     SMALLINT        NULL,
  redirect_target VARCHAR(2048)   NULL,
  redirect_hops   TINYINT UNSIGNED NOT NULL DEFAULT 0,
  content_type    VARCHAR(120)    NULL,
  bytes           INT UNSIGNED    NULL,
  ttfb_ms         INT UNSIGNED    NULL,
  depth           SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  indexable       TINYINT(1)      NULL,
  robots_allowed  TINYINT(1)      NULL,
  canonical_url   VARCHAR(2048)   NULL,
  google_canonical VARCHAR(2048)  NULL,
  robots_directives VARCHAR(255)  NULL,
  title           VARCHAR(1024)   NULL,
  meta_description VARCHAR(1024)  NULL,
  h1              VARCHAR(1024)   NULL,
  word_count_main  MEDIUMINT UNSIGNED NULL,
  word_count_raw   MEDIUMINT UNSIGNED NULL,
  content_hash     BINARY(32)     NULL,              -- sha256 of normalised main content
  simhash          BIGINT UNSIGNED NULL,             -- near-duplicate bucketing (T119)
  template_fingerprint BINARY(20) NULL,              -- drives render sampling
  rendered         TINYINT(1)     NOT NULL DEFAULT 0,
  render_delta_pct DECIMAL(5,2)   NULL,              -- B01 retrievability gap
  raw_html_path    VARCHAR(500)   NULL,
  crawled_at       DATETIME       NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_run_url (run_id, url_hash),
  KEY idx_url_status (run_id, status_code),
  KEY idx_url_indexable (run_id, indexable),
  KEY idx_url_template (run_id, template_fingerprint),
  KEY idx_url_content_hash (run_id, content_hash),
  CONSTRAINT fk_url_run FOREIGN KEY (run_id) REFERENCES audit_run (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS url_link (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  run_id        BIGINT UNSIGNED NOT NULL,
  from_url_id   BIGINT UNSIGNED NOT NULL,
  to_url_hash   BINARY(20)      NOT NULL,            -- hash, so an edge can exist before the target is crawled
  to_url        VARCHAR(2048)   NOT NULL,
  anchor_text   VARCHAR(512)    NULL,
  rel           VARCHAR(120)    NULL,
  is_internal   TINYINT(1)      NOT NULL DEFAULT 1,
  is_boilerplate TINYINT(1)     NOT NULL DEFAULT 0,  -- appears on >70% of pages (T033)
  PRIMARY KEY (id),
  KEY idx_link_from (run_id, from_url_id),
  KEY idx_link_to (run_id, to_url_hash),
  CONSTRAINT fk_link_run FOREIGN KEY (run_id) REFERENCES audit_run (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS url_metric (
  run_id     BIGINT UNSIGNED NOT NULL,
  url_id     BIGINT UNSIGNED NOT NULL,
  metric_key VARCHAR(60)     NOT NULL,               -- lcp_p75, inp_p75, cls_p75, pagerank, entity_density…
  value      DOUBLE          NULL,
  source     VARCHAR(30)     NULL,                   -- crux | psi | lighthouse | computed
  PRIMARY KEY (run_id, url_id, metric_key),
  KEY idx_metric_key (run_id, metric_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Metric registry so a fourth Core Web Vital is a config row, not a migration.
CREATE TABLE IF NOT EXISTS metric_definition (
  metric_key     VARCHAR(60)  NOT NULL,
  label          VARCHAR(80)  NOT NULL,
  unit           VARCHAR(20)  NULL,
  threshold_good DOUBLE       NULL,
  threshold_poor DOUBLE       NULL,
  higher_is_better TINYINT(1) NOT NULL DEFAULT 0,
  is_core        TINYINT(1)   NOT NULL DEFAULT 0,
  effective_from DATE         NULL,
  PRIMARY KEY (metric_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------------- findings ---

-- check_definition is CONFIG, not code: severity, weight, confidence and
-- thresholds are tunable without a deploy.
CREATE TABLE IF NOT EXISTS check_definition (
  id            SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  code          VARCHAR(12)   NOT NULL,              -- T001, O031, F014, A17, B01, C06, D02, E01
  category      VARCHAR(40)   NOT NULL,              -- scoring category key
  subcategory   VARCHAR(60)   NULL,
  title         VARCHAR(200)  NOT NULL,
  description   TEXT          NULL,
  remediation   TEXT          NULL,
  severity      ENUM('critical','high','medium','low','info') NOT NULL DEFAULT 'medium',
  weight        DECIMAL(4,1)  NOT NULL DEFAULT 2.0,  -- critical 10 · high 5 · medium 2 · low 0.5
  confidence    DECIMAL(3,2)  NOT NULL DEFAULT 1.00, -- 1.0 deterministic · 0.5-0.8 heuristic
  plane         ENUM('crawl','render','api','log') NOT NULL DEFAULT 'crawl',
  eligible_unit VARCHAR(40)   NOT NULL DEFAULT 'indexable_url',
  evidence_tier ENUM('primary','third_party','weak') NOT NULL DEFAULT 'primary',
  doc_url       VARCHAR(500)  NULL,
  threshold_json JSON         NULL,
  is_active     TINYINT(1)    NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  UNIQUE KEY uq_check_code (code),
  KEY idx_check_cat (category, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- One row per check per affected unit.
CREATE TABLE IF NOT EXISTS finding (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  run_id        BIGINT UNSIGNED NOT NULL,
  site_id       INT UNSIGNED    NOT NULL,
  check_id      SMALLINT UNSIGNED NOT NULL,
  url_id        BIGINT UNSIGNED NULL,                -- null = site-level finding
  entity_ref    VARCHAR(255)    NULL,                -- crawler token, keyword, referring domain…
  severity_effective ENUM('critical','high','medium','low','info') NOT NULL,
  detail_json   JSON            NULL,
  evidence_json JSON            NULL,                -- field value + threshold crossed + provider
  affected_clicks INT UNSIGNED  NOT NULL DEFAULT 0,  -- GSC 28-day clicks, drives priority
  priority      DECIMAL(8,3)    NOT NULL DEFAULT 0,
  first_seen_run_id BIGINT UNSIGNED NULL,
  status        ENUM('new','persisting','fixed','ignored','wont_fix') NOT NULL DEFAULT 'new',
  assigned_to   VARCHAR(120)    NULL,
  resolution_note TEXT          NULL,
  resolved_at   DATETIME        NULL,
  created_at    TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_finding_run (run_id, severity_effective, priority),
  KEY idx_finding_site_status (site_id, status),
  KEY idx_finding_check (run_id, check_id),
  KEY idx_finding_url (url_id),
  CONSTRAINT fk_finding_run FOREIGN KEY (run_id) REFERENCES audit_run (id) ON DELETE CASCADE,
  CONSTRAINT fk_finding_check FOREIGN KEY (check_id) REFERENCES check_definition (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- What the scorer reads. Storing eligible/affected per run makes every
-- historical score reproducible and re-scorable.
CREATE TABLE IF NOT EXISTS check_result (
  run_id         BIGINT UNSIGNED NOT NULL,
  check_id       SMALLINT UNSIGNED NOT NULL,
  eligible_count INT UNSIGNED  NOT NULL DEFAULT 0,
  affected_count INT UNSIGNED  NOT NULL DEFAULT 0,
  prevalence     DECIMAL(6,5)  NOT NULL DEFAULT 0,
  penalty        DECIMAL(8,4)  NOT NULL DEFAULT 0,
  confidence     DECIMAL(3,2)  NOT NULL DEFAULT 1.00,
  PRIMARY KEY (run_id, check_id),
  CONSTRAINT fk_cr_run FOREIGN KEY (run_id) REFERENCES audit_run (id) ON DELETE CASCADE,
  CONSTRAINT fk_cr_check FOREIGN KEY (check_id) REFERENCES check_definition (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS score (
  run_id          BIGINT UNSIGNED NOT NULL,
  scope           ENUM('overall','category','risk','strength','opportunity') NOT NULL,
  scope_key       VARCHAR(40) NOT NULL DEFAULT '',
  value           DECIMAL(5,2) NOT NULL,
  ceiling_applied VARCHAR(120) NULL,                 -- which hard gate capped it
  model_version   VARCHAR(20)  NOT NULL DEFAULT 'v1',
  PRIMARY KEY (run_id, scope, scope_key),
  CONSTRAINT fk_score_run FOREIGN KEY (run_id) REFERENCES audit_run (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------- AI SEO ---

CREATE TABLE IF NOT EXISTS ai_crawler (
  id              SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  token           VARCHAR(60)  NOT NULL,             -- robots.txt product token
  vendor          VARCHAR(40)  NOT NULL,
  bot_class       ENUM('training','retrieval','agent','policy_token') NOT NULL,
  blocking_effect VARCHAR(255) NULL,
  ip_ranges_url   VARCHAR(255) NULL,
  doc_url         VARCHAR(255) NULL,
  is_verifiable   TINYINT(1)   NOT NULL DEFAULT 1,
  respects_robots ENUM('yes','partial','no','unknown') NOT NULL DEFAULT 'yes',
  severity_if_blocked ENUM('critical','high','medium','low','info') NOT NULL DEFAULT 'info',
  notes           TEXT         NULL,
  is_active       TINYINT(1)   NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  UNIQUE KEY uq_crawler_token (token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ai_access_result (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  run_id         BIGINT UNSIGNED NOT NULL,
  site_id        INT UNSIGNED    NOT NULL,
  crawler_id     SMALLINT UNSIGNED NOT NULL,
  path_pattern   VARCHAR(255)    NOT NULL,
  layer          ENUM('robots','meta','header','waf','cdn_402','content_signal','aipref') NOT NULL,
  robots_verdict ENUM('allowed','disallowed','not_applicable') NULL,
  probe_status   SMALLINT        NULL,
  probe_body_len INT UNSIGNED    NULL,
  baseline_status SMALLINT       NULL,
  baseline_body_len INT UNSIGNED NULL,
  verdict        ENUM('allowed','blocked','policy','paywall','unknown') NOT NULL DEFAULT 'unknown',
  detail_json    JSON            NULL,
  checked_at     TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_aar_run (run_id, crawler_id),
  CONSTRAINT fk_aar_run FOREIGN KEY (run_id) REFERENCES audit_run (id) ON DELETE CASCADE,
  CONSTRAINT fk_aar_crawler FOREIGN KEY (crawler_id) REFERENCES ai_crawler (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Share-of-voice harness. Prompt sets are versioned because comparing runs
-- across a changed prompt set is meaningless.
CREATE TABLE IF NOT EXISTS ai_prompt_set (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_id    INT UNSIGNED NOT NULL,
  version    SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  language   CHAR(2)      NOT NULL DEFAULT 'en',
  label      VARCHAR(120) NOT NULL,
  created_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_prompt_set (site_id, version, language),
  CONSTRAINT fk_ps_site FOREIGN KEY (site_id) REFERENCES site (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ai_prompt (
  id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  prompt_set_id INT UNSIGNED NOT NULL,
  archetype     ENUM('generic_best','budget','niche_usecase','head_to_head','enterprise_b2b') NOT NULL,
  prompt_text   TEXT NOT NULL,
  PRIMARY KEY (id),
  KEY idx_prompt_set (prompt_set_id),
  CONSTRAINT fk_prompt_set FOREIGN KEY (prompt_set_id) REFERENCES ai_prompt_set (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ai_visibility_run (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_id       INT UNSIGNED NOT NULL,
  prompt_set_id INT UNSIGNED NOT NULL,
  platform      VARCHAR(40)  NOT NULL,               -- never aggregate across platforms
  repetitions   TINYINT UNSIGNED NOT NULL DEFAULT 3,
  run_at        DATETIME     NOT NULL,
  cost_usd      DECIMAL(8,4) NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY idx_avr_site (site_id, platform, run_at),
  CONSTRAINT fk_avr_site FOREIGN KEY (site_id) REFERENCES site (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ai_visibility_obs (
  id                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  visibility_run_id BIGINT UNSIGNED NOT NULL,
  prompt_id         INT UNSIGNED    NOT NULL,
  repetition        TINYINT UNSIGNED NOT NULL,
  brand_mentioned   TINYINT(1)      NOT NULL DEFAULT 0,
  first_mention_rank SMALLINT       NULL,
  our_urls_cited    SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  cited_urls_json   JSON            NULL,            -- intermediary domains = the target list
  response_hash     BINARY(32)      NULL,
  PRIMARY KEY (id),
  KEY idx_avo_run (visibility_run_id, prompt_id),
  CONSTRAINT fk_avo_run FOREIGN KEY (visibility_run_id) REFERENCES ai_visibility_run (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Server-log rollup: the only ground truth for AI crawlers, because they do
-- not execute JS and client-side analytics never sees them.
CREATE TABLE IF NOT EXISTS bot_log_daily (
  site_id      INT UNSIGNED NOT NULL,
  log_date     DATE         NOT NULL,
  crawler_id   SMALLINT UNSIGNED NULL,
  ua_class     VARCHAR(40)  NOT NULL DEFAULT 'other',
  verified     TINYINT(1)   NOT NULL DEFAULT 0,      -- IP-verified, not UA-trusted
  requests     INT UNSIGNED NOT NULL DEFAULT 0,
  unique_urls  INT UNSIGNED NOT NULL DEFAULT 0,
  bytes        BIGINT UNSIGNED NOT NULL DEFAULT 0,
  status_2xx   INT UNSIGNED NOT NULL DEFAULT 0,
  status_3xx   INT UNSIGNED NOT NULL DEFAULT 0,
  status_4xx   INT UNSIGNED NOT NULL DEFAULT 0,
  status_402   INT UNSIGNED NOT NULL DEFAULT 0,
  status_5xx   INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (site_id, log_date, ua_class, verified),
  KEY idx_bld_crawler (site_id, crawler_id, log_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------- site analyze ---

CREATE TABLE IF NOT EXISTS country_config (
  country_code       CHAR(2)      NOT NULL,
  label              VARCHAR(80)  NOT NULL,
  default_language   CHAR(2)      NOT NULL DEFAULT 'en',
  secondary_language CHAR(2)      NULL,
  language_weights_json JSON      NULL,              -- {"en":0.72,"ar":0.28}
  location_code      INT UNSIGNED NULL,              -- DataForSEO location_code
  device_default     ENUM('mobile','desktop') NOT NULL DEFAULT 'mobile',
  currency           CHAR(3)      NULL,
  is_active          TINYINT(1)   NOT NULL DEFAULT 1,
  PRIMARY KEY (country_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS domain_blocklist (
  id       INT UNSIGNED NOT NULL AUTO_INCREMENT,
  domain   VARCHAR(255) NOT NULL,
  class    ENUM('marketplace','directory','aggregator_leadgen','publisher_media','oem_principal','gov_assoc') NOT NULL,
  region   VARCHAR(20)  NULL,
  vertical VARCHAR(40)  NULL,
  note     VARCHAR(255) NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_bl_domain (domain)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS competitor (
  id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_id          INT UNSIGNED NOT NULL,
  country_code     CHAR(2)      NOT NULL,
  language         CHAR(2)      NOT NULL DEFAULT 'en',
  domain           VARCHAR(255) NOT NULL,
  class            ENUM('direct_competitor','marketplace','directory','aggregator_leadgen','publisher_media','oem_principal','gov_assoc','own') NOT NULL DEFAULT 'direct_competitor',
  is_partner       TINYINT(1)   NOT NULL DEFAULT 0,  -- our own principal, not a rival
  competitor_score DECIMAL(6,4) NOT NULL DEFAULT 0,
  visibility       DECIMAL(12,2) NOT NULL DEFAULT 0,
  intersections    INT UNSIGNED NOT NULL DEFAULT 0,
  avg_position     DECIMAL(5,2) NULL,
  etv              DECIMAL(12,2) NULL,
  classified_by    ENUM('blocklist','classifier','manual') NOT NULL DEFAULT 'classifier',
  classifier_confidence DECIMAL(3,2) NULL,
  refreshed_at     DATETIME     NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_comp (site_id, country_code, language, domain),
  KEY idx_comp_rank (site_id, country_code, class, competitor_score),
  CONSTRAINT fk_comp_site FOREIGN KEY (site_id) REFERENCES site (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS keyword (
  id                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  country_code      CHAR(2)      NOT NULL,
  language          CHAR(2)      NOT NULL,
  keyword           VARCHAR(700) NOT NULL,
  keyword_hash      BINARY(20)   NOT NULL,
  volume_country    INT UNSIGNED NULL,                -- NULL means unknown, NOT zero
  volume_gcc        INT UNSIGNED NULL,
  volume_global_en  INT UNSIGNED NULL,
  volume_confidence ENUM('high','medium','low','unknown') NOT NULL DEFAULT 'unknown',
  cpc_usd           DECIMAL(8,2) NULL,
  kd_dfs            TINYINT UNSIGNED NULL,
  kd_semrush        TINYINT UNSIGNED NULL,
  achievability     DECIMAL(4,3) NULL,
  intent_primary    ENUM('informational','commercial','transactional','navigational') NULL,
  intent_probability DECIMAL(3,2) NULL,
  intent_source     ENUM('lexicon','model','serp') NULL,
  serp_features     INT UNSIGNED NOT NULL DEFAULT 0,  -- bitmask
  has_aio           TINYINT(1)   NOT NULL DEFAULT 0,
  directory_saturation DECIMAL(3,2) NULL,
  priority          DECIMAL(4,3) NULL,
  last_refreshed_at DATETIME     NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_kw (country_code, language, keyword_hash),
  KEY idx_kw_priority (country_code, language, priority),
  KEY idx_kw_intent (country_code, intent_primary)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS keyword_rank (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  keyword_id  BIGINT UNSIGNED NOT NULL,
  site_id     INT UNSIGNED    NOT NULL,
  url         VARCHAR(2048)   NULL,
  rank_date   DATE            NOT NULL,
  position    SMALLINT        NULL,
  is_aio_cited TINYINT(1)     NOT NULL DEFAULT 0,
  device      ENUM('mobile','desktop') NOT NULL DEFAULT 'mobile',
  PRIMARY KEY (id),
  UNIQUE KEY uq_kr (keyword_id, site_id, rank_date, device),
  KEY idx_kr_site_date (site_id, rank_date),
  CONSTRAINT fk_kr_kw FOREIGN KEY (keyword_id) REFERENCES keyword (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS keyword_cluster (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_id       INT UNSIGNED NOT NULL,
  country_code  CHAR(2)      NOT NULL,
  language      CHAR(2)      NOT NULL,
  label         VARCHAR(255) NOT NULL,
  page_type     ENUM('pillar','cluster_article','landing','comparison','glossary','tool_asset','brand','trust') NULL,
  cluster_method ENUM('embedding','serp_overlap','hybrid') NOT NULL DEFAULT 'hybrid',
  head_keyword_id BIGINT UNSIGNED NULL,
  PRIMARY KEY (id),
  KEY idx_cluster_site (site_id, country_code, language),
  CONSTRAINT fk_cluster_site FOREIGN KEY (site_id) REFERENCES site (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS keyword_cluster_member (
  cluster_id BIGINT UNSIGNED NOT NULL,
  keyword_id BIGINT UNSIGNED NOT NULL,
  is_head    TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (cluster_id, keyword_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS content_recommendation (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_id       INT UNSIGNED NOT NULL,
  cluster_id    BIGINT UNSIGNED NULL,
  country_code  CHAR(2)      NOT NULL,
  language      CHAR(2)      NOT NULL,
  action        ENUM('new','update','consolidate','children') NOT NULL,
  target_url    VARCHAR(2048) NULL,
  url_suggestion VARCHAR(500) NULL,
  page_type     VARCHAR(30)  NULL,
  brief_json    JSON         NULL,
  priority      DECIMAL(4,3) NOT NULL DEFAULT 0,
  effort_hours  DECIMAL(5,1) NULL,
  expected_clicks_12mo INT UNSIGNED NULL,
  publish_window VARCHAR(40) NULL,                    -- GCC cooling seasonality
  status        ENUM('proposed','accepted','in_progress','published','rejected') NOT NULL DEFAULT 'proposed',
  created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_rec_site (site_id, country_code, priority),
  CONSTRAINT fk_rec_site FOREIGN KEY (site_id) REFERENCES site (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Hard mapping that prevents two pages targeting the same (keyword, country,
-- language). Blocks cross-country cannibalisation by construction.
CREATE TABLE IF NOT EXISTS keyword_allocation (
  country_code CHAR(2)      NOT NULL,
  language     CHAR(2)      NOT NULL,
  keyword_id   BIGINT UNSIGNED NOT NULL,
  site_id      INT UNSIGNED NOT NULL,
  canonical_url VARCHAR(2048) NOT NULL,
  assigned_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (country_code, language, keyword_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------ infra: jobs, cache ---

CREATE TABLE IF NOT EXISTS job (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  queue         VARCHAR(40)  NOT NULL DEFAULT 'default',
  type          VARCHAR(60)  NOT NULL,
  payload_json  JSON         NULL,
  site_id       INT UNSIGNED NULL,
  run_id        BIGINT UNSIGNED NULL,
  status        ENUM('pending','reserved','done','failed') NOT NULL DEFAULT 'pending',
  attempts      TINYINT UNSIGNED NOT NULL DEFAULT 0,
  max_attempts  TINYINT UNSIGNED NOT NULL DEFAULT 3,
  available_at  DATETIME     NOT NULL,
  reserved_at   DATETIME     NULL,
  reserved_by   VARCHAR(64)  NULL,
  last_error    TEXT         NULL,
  created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_job_claim (queue, status, available_at),
  KEY idx_job_run (run_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Per-host politeness lives in the DB so concurrent workers share it.
CREATE TABLE IF NOT EXISTS host_state (
  host            VARCHAR(255) NOT NULL,
  next_allowed_at DATETIME(3)  NOT NULL,
  crawl_delay_ms  INT UNSIGNED NOT NULL DEFAULT 400,
  consecutive_5xx TINYINT UNSIGNED NOT NULL DEFAULT 0,
  paused_until    DATETIME     NULL,
  robots_body     MEDIUMTEXT   NULL,
  robots_status   SMALLINT     NULL,
  robots_hash     BINARY(20)   NULL,                 -- T138 drift detection
  robots_fetched_at DATETIME   NULL,
  PRIMARY KEY (host)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS api_cache (
  cache_key   BINARY(32)   NOT NULL,
  provider    VARCHAR(40)  NOT NULL,
  endpoint    VARCHAR(120) NOT NULL,
  response    LONGTEXT     NULL,
  cost_usd    DECIMAL(8,5) NOT NULL DEFAULT 0,
  expires_at  DATETIME     NOT NULL,
  created_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (cache_key),
  KEY idx_cache_expiry (expires_at),
  KEY idx_cache_provider (provider, endpoint)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS api_usage (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  provider   VARCHAR(40) NOT NULL,
  endpoint   VARCHAR(120) NOT NULL,
  site_id    INT UNSIGNED NULL,
  run_id     BIGINT UNSIGNED NULL,
  units      INT UNSIGNED NOT NULL DEFAULT 1,
  cost_usd   DECIMAL(8,5) NOT NULL DEFAULT 0,
  cache_hit  TINYINT(1)   NOT NULL DEFAULT 0,
  status     SMALLINT     NULL,
  duration_ms INT UNSIGNED NULL,
  called_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_usage_day (provider, called_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS oauth_token (
  id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  provider      VARCHAR(40)  NOT NULL,               -- google_search_console, ga4
  account_ref   VARCHAR(255) NOT NULL,
  access_token  TEXT         NULL,
  refresh_token TEXT         NULL,
  scope         TEXT         NULL,
  expires_at    DATETIME     NULL,
  updated_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_oauth (provider, account_ref)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS integration (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_id     INT UNSIGNED NULL,                     -- null = account-wide
  provider    VARCHAR(40)  NOT NULL,
  status      ENUM('connected','disconnected','error') NOT NULL DEFAULT 'disconnected',
  config_json JSON         NULL,                     -- never store secrets here; use env/oauth_token
  last_ok_at  DATETIME     NULL,
  last_error  TEXT         NULL,
  updated_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_integration (site_id, provider)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS migration (
  filename   VARCHAR(190) NOT NULL,
  applied_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (filename)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
