-- 007_analyze_content.sql
--
-- The Site Analyze pipeline, the editorial calendar, and the article workflow.
--
-- 001_core.sql already defined `competitor`, `keyword`, `keyword_cluster`,
-- `content_recommendation`, `keyword_allocation`, `country_config` and
-- `domain_blocklist` — and INTEGRATION-GUIDE.md §12 recorded that nothing
-- populated them: "the orchestration that turns them into competitors, keyword
-- scores and content briefs is the next phase." This migration adds the tables
-- that phase needs. It does not alter any of the existing ones.
--
-- New tables only, so this is safe against a populated database and safe to
-- re-run.

-- ---------------------------------------------------------------------------
-- Which countries each site is analysed for.
--
-- Not a column on `site`: a site can be analysed for several countries at once
-- (dolphinhvac.ae is a UAE business; dolphincatalogue.com sells worldwide), and
-- each country/language pair is a separate SERP with separate competitors. The
-- crawler is country-blind; everything downstream of it is not.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS site_country (
  site_id      INT UNSIGNED NOT NULL,
  country_code CHAR(2)      NOT NULL,
  language     CHAR(2)      NOT NULL DEFAULT 'en',
  is_primary   TINYINT(1)   NOT NULL DEFAULT 0 COMMENT 'the market this site is really for; used to break ties',
  is_active    TINYINT(1)   NOT NULL DEFAULT 1,
  added_at     TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (site_id, country_code, language),
  KEY idx_sc_active (site_id, is_active),
  CONSTRAINT fk_sc_site FOREIGN KEY (site_id) REFERENCES site (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------------
-- One analysis pass over one site in one country/language.
--
-- Separate from `audit_run` on purpose. An audit crawls YOUR site and needs no
-- money; an analysis buys SERP and keyword data and needs a lot of it. Mixing
-- them would make "run an audit" a billable action, and the first thing anyone
-- does with a new tool is press every button.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS analysis_run (
  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',
  status         ENUM('queued','running','completed','inconclusive','failed') NOT NULL DEFAULT 'queued',
  -- Same discipline as audit_run: a pass that bought no usable data is
  -- `inconclusive`, never `completed` with zero counts, because zero
  -- competitors reads as "you have no competitors" rather than "we found none".
  seed_keywords     INT UNSIGNED NOT NULL DEFAULT 0,
  competitors_found INT UNSIGNED NOT NULL DEFAULT 0,
  keywords_found    INT UNSIGNED NOT NULL DEFAULT 0,
  gaps_found        INT UNSIGNED NOT NULL DEFAULT 0,
  clusters_built    INT UNSIGNED NOT NULL DEFAULT 0,
  recommendations   INT UNSIGNED NOT NULL DEFAULT 0,
  api_cost_usd   DECIMAL(10,4) NOT NULL DEFAULT 0,
  error_message  TEXT         NULL,
  started_at     DATETIME     NULL,
  finished_at    DATETIME     NULL,
  created_at     TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_ar_site (site_id, country_code, id),
  CONSTRAINT fk_ar_site FOREIGN KEY (site_id) REFERENCES site (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Keyword gaps: a keyword a competitor ranks for and we do not, or rank badly
-- for. This is the raw material the recommendations are built from, kept as its
-- own table so "why was this suggested?" has an answer with evidence in it.
CREATE TABLE IF NOT EXISTS keyword_gap (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  analysis_run_id BIGINT UNSIGNED NOT NULL,
  site_id         INT UNSIGNED NOT NULL,
  keyword_id      BIGINT UNSIGNED NOT NULL,
  our_position    SMALLINT     NULL COMMENT 'NULL = we do not rank at all',
  best_competitor VARCHAR(255) NULL,
  best_competitor_position SMALLINT NULL,
  best_competitor_url VARCHAR(2048) NULL,
  competitors_ranking TINYINT UNSIGNED NOT NULL DEFAULT 0,
  opportunity     DECIMAL(6,3) NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  UNIQUE KEY uq_gap (analysis_run_id, keyword_id),
  KEY idx_gap_site (site_id, opportunity),
  CONSTRAINT fk_gap_run FOREIGN KEY (analysis_run_id) REFERENCES analysis_run (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------------
-- The editorial calendar. Two slots per week per site.
--
-- A slot exists before it has an article in it — that is the point. An empty
-- slot next Tuesday is a question ("what are we publishing?"); a list of
-- recommendations with no dates is a backlog nobody opens.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS content_calendar (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_id        INT UNSIGNED NOT NULL,
  week_start     DATE         NOT NULL COMMENT 'always a Monday, UTC',
  slot_no        TINYINT UNSIGNED NOT NULL COMMENT '1 or 2 — two per week per site',
  publish_on     DATE         NOT NULL COMMENT 'suggested date inside that week',
  recommendation_id BIGINT UNSIGNED NULL,
  article_id     BIGINT UNSIGNED NULL,
  state          ENUM('empty','planned','drafting','in_review','approved','published','skipped','failed')
                 NOT NULL DEFAULT 'empty',
  note           VARCHAR(255) NULL,
  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_slot (site_id, week_start, slot_no),
  KEY idx_cal_week (week_start, state),
  KEY idx_cal_site (site_id, week_start),
  CONSTRAINT fk_cal_site FOREIGN KEY (site_id) REFERENCES site (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------------
-- Articles.
--
-- body_md is the source of truth and body_html is derived, not the other way
-- round: every CMS wants a different flavour of HTML, and keeping Markdown
-- means the same article can be re-rendered for WordPress, Shopify and a static
-- bundle without a lossy round trip.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS article (
  id                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_id           INT UNSIGNED NOT NULL,
  recommendation_id BIGINT UNSIGNED NULL,
  calendar_id       BIGINT UNSIGNED NULL,
  country_code      CHAR(2)      NOT NULL,
  language          CHAR(2)      NOT NULL DEFAULT 'en',

  target_keyword    VARCHAR(700) NULL,
  title             VARCHAR(300) NOT NULL,
  slug              VARCHAR(300) NOT NULL,
  meta_description  VARCHAR(320) NULL,
  excerpt           VARCHAR(600) NULL,
  body_md           MEDIUMTEXT   NULL,
  body_html         MEDIUMTEXT   NULL,
  brief_json        JSON         NULL COMMENT 'the brief this was written from, frozen at draft time',
  schema_json       JSON         NULL COMMENT 'Article + FAQPage JSON-LD to publish with it',
  word_count        INT UNSIGNED NOT NULL DEFAULT 0,

  status            ENUM('planned','drafting','draft','in_review','approved','publishing','published','failed','rejected')
                    NOT NULL DEFAULT 'planned',

  -- Provenance. An article nobody can trace back to a model, a prompt and a
  -- price is an article nobody can defend in a meeting.
  writer_model      VARCHAR(60)  NULL,
  writer_tokens_in  INT UNSIGNED NOT NULL DEFAULT 0,
  writer_tokens_out INT UNSIGNED NOT NULL DEFAULT 0,
  writer_cost_usd   DECIMAL(10,5) NOT NULL DEFAULT 0,
  image_cost_usd    DECIMAL(10,5) NOT NULL DEFAULT 0,

  created_by        INT UNSIGNED NULL,
  reviewed_by       INT UNSIGNED NULL,
  reviewed_at       DATETIME     NULL,
  published_at      DATETIME     NULL,
  published_url     VARCHAR(2048) NULL,
  cms_post_id       VARCHAR(120) NULL,
  error_message     TEXT         NULL,
  -- Kept apart from error_message. An image that failed to generate does not
  -- make the article bad, and writing "OpenAI rate limit" into the field that
  -- explains why a draft failed would say the wrong thing about the text.
  image_error       VARCHAR(1000) 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_article_site (site_id, status, id),
  KEY idx_article_cal (calendar_id),
  CONSTRAINT fk_article_site FOREIGN KEY (site_id) REFERENCES site (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Every edit, so "who changed the opening paragraph" is answerable and an
-- accidental overwrite is recoverable. Append-only.
CREATE TABLE IF NOT EXISTS article_revision (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  article_id BIGINT UNSIGNED NOT NULL,
  user_id    INT UNSIGNED NULL,
  title      VARCHAR(300) NULL,
  body_md    MEDIUMTEXT   NULL,
  note       VARCHAR(255) NULL,
  created_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_rev_article (article_id, id),
  CONSTRAINT fk_rev_article FOREIGN KEY (article_id) REFERENCES article (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Generated illustrations. Stored on disk under var/media, not in the database:
-- a 1536x1024 PNG is megabytes and MySQL is the wrong place for it.
CREATE TABLE IF NOT EXISTS article_image (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  article_id  BIGINT UNSIGNED NOT NULL,
  role        ENUM('hero','inline') NOT NULL DEFAULT 'hero',
  prompt      TEXT         NULL,
  alt_text    VARCHAR(500) NULL,
  file_path   VARCHAR(500) NOT NULL COMMENT 'relative to var/media',
  mime        VARCHAR(60)  NOT NULL DEFAULT 'image/png',
  width       SMALLINT UNSIGNED NULL,
  height      SMALLINT UNSIGNED NULL,
  bytes       INT UNSIGNED NOT NULL DEFAULT 0,
  provider    VARCHAR(40)  NULL,
  model       VARCHAR(60)  NULL,
  cost_usd    DECIMAL(10,5) NOT NULL DEFAULT 0,
  remote_ref  VARCHAR(500) NULL COMMENT 'media id/url once uploaded to the CMS',
  created_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_img_article (article_id, role),
  CONSTRAINT fk_img_article FOREIGN KEY (article_id) REFERENCES article (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------------
-- Where each site publishes.
--
-- config_json is ENCRYPTED at rest with APP_KEY (see src/Core/Crypto.php). A
-- WordPress application password in a plaintext column is a password on a
-- backup, in a dump, and in every screenshot of phpMyAdmin anyone ever takes.
--
-- `driver` values map 1:1 to a class in src/Content/Cms. 'squarespace' is
-- present so the UI can explain WHY it cannot publish there rather than just
-- omitting the option — Squarespace publishes no content-authoring API at all.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS cms_connection (
  id             INT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_id        INT UNSIGNED NOT NULL,
  driver         ENUM('wordpress','shopify','wix','webhook','bundle','squarespace','none') NOT NULL DEFAULT 'none',
  label          VARCHAR(120) NULL,
  base_url       VARCHAR(500) NULL,
  config_enc     BLOB         NULL COMMENT 'AES-256-GCM over JSON, keyed from APP_KEY',
  default_status ENUM('draft','publish') NOT NULL DEFAULT 'draft'
                 COMMENT 'what a Publish click actually does on the far end',
  last_check_at  DATETIME     NULL,
  last_check_ok  TINYINT(1)   NULL,
  last_check_msg VARCHAR(500) NULL,
  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_cms_site (site_id),
  CONSTRAINT fk_cms_site FOREIGN KEY (site_id) REFERENCES site (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Append-only record of every publish attempt, successful or not. Publishing is
-- the one action in this app that changes something outside it.
CREATE TABLE IF NOT EXISTS publish_attempt (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  article_id   BIGINT UNSIGNED NOT NULL,
  user_id      INT UNSIGNED NULL,
  driver       VARCHAR(30)  NOT NULL,
  ok           TINYINT(1)   NOT NULL DEFAULT 0,
  -- Set when the connection died after the request was delivered but before an
  -- answer came back. The post may exist on the live site. Nothing may publish
  -- this article again automatically while such a row exists.
  unresolved   TINYINT(1)   NOT NULL DEFAULT 0,
  -- Set when an operator has checked the live site and confirmed the post is
  -- not there. The attempt itself is left intact — this is a third state, not
  -- a rewrite of what the CMS said.
  released     TINYINT(1)   NOT NULL DEFAULT 0,
  http_status  SMALLINT     NULL,
  remote_id    VARCHAR(120) NULL,
  remote_url   VARCHAR(2048) NULL,
  message      TEXT         NULL,
  duration_ms  INT UNSIGNED NOT NULL DEFAULT 0,
  created_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_pub_article (article_id, id),
  CONSTRAINT fk_pub_article FOREIGN KEY (article_id) REFERENCES article (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------------
-- Seed: point each existing site at its own market so the first analysis has
-- somewhere to run. AE for everything, because these are Dubai companies —
-- change it in the UI, which is the point of site_country existing.
--
-- The full country list is NOT hardcoded here. Country codes are only half the
-- story; DataForSEO needs a location_code, and inventing 200 of those from
-- memory would produce a table that looks complete and returns other people's
-- SERPs. The Analyze screen has a "Load every country from DataForSEO" button
-- that calls /v3/dataforseo_labs/locations_and_languages — which is free — and
-- fills country_config from the source of truth.
-- ---------------------------------------------------------------------------
INSERT INTO site_country (site_id, country_code, language, is_primary, is_active)
SELECT s.id, 'AE', 'en', 1, 1 FROM site s
ON DUPLICATE KEY UPDATE is_active = VALUES(is_active);

-- ---------------------------------------------------------------------------
-- Idempotency for queued work.
--
-- Analysis, drafting and image generation all cost real money on the first
-- click. Without a key to deduplicate on, a double-click, an impatient reload,
-- or two people opening the same screen buys the same data twice — and one
-- country/language pair costs roughly a dollar.
--
-- The column and its UNIQUE index are added by 008_publish_safety.sql, not
-- here: this file had already been applied on the live install by the time the
-- need for it was found, and the migrator never re-reads a file it has
-- recorded. Editing this one would have shipped a column that only ever
-- existed on a fresh database.
-- ---------------------------------------------------------------------------