-- 009_settings_content.sql
--
-- Everything the writer needs to know about YOUR business, plus the measurement
-- surfaces that were missing: backlink comparison and Search Console.
--
-- The reason this migration exists is a defect in the previous build rather
-- than a missing feature. The article writer was forbidden from saying anything
-- about the client company — no experience, no certifications, no history —
-- because it knew nothing about them and would otherwise invent it. That is the
-- right rule when the app holds no facts. It is the wrong rule once it does.
-- These tables are where the facts live, so the rule can become "state only
-- what the supplied documents say" instead of "say nothing".
--
-- New tables only, plus additive columns on `article` and `competitor`. Nothing
-- existing is altered or dropped.

-- ---------------------------------------------------------------------------
-- One settings row per site.
--
-- Deliberately a wide table rather than a key/value store. Every column here is
-- read on every article generation, a key/value store would mean a dozen joins
-- or a rehydration step on the hot path, and — more importantly — a typed
-- column is a schema somebody can read. `publish_frequency` being an ENUM is
-- what stops a typo becoming a site that publishes zero articles a week.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS site_settings (
  site_id             INT UNSIGNED NOT NULL,

  -- Publishing preferences -------------------------------------------------
  -- auto_publish is the one setting in this table that can put text on a live
  -- website without a person reading it first. It defaults OFF and the UI says
  -- what it means in plain words, because the entire review workflow exists to
  -- prevent exactly what turning this on allows.
  auto_publish        TINYINT(1) NOT NULL DEFAULT 0,
  hero_image          TINYINT(1) NOT NULL DEFAULT 1,
  youtube_embed       TINYINT(1) NOT NULL DEFAULT 0,
  infographic         TINYINT(1) NOT NULL DEFAULT 0,
  key_takeaways       TINYINT(1) NOT NULL DEFAULT 1,
  table_of_contents   TINYINT(1) NOT NULL DEFAULT 1,
  external_links      TINYINT(1) NOT NULL DEFAULT 1,
  links_new_tab       TINYINT(1) NOT NULL DEFAULT 0,

  -- Target length. Stored as the word target itself so the brief can use it
  -- directly; the UI offers the same four choices AutoSEO does.
  article_words       SMALLINT UNSIGNED NOT NULL DEFAULT 2000,

  -- How often this site publishes. Two a week (Tue/Thu) is the default because
  -- it is what was asked for; the others exist because volume is a per-site
  -- decision and a catalogue site can absorb more than a small brochure site.
  publish_frequency   ENUM('1','2','3','5','7') NOT NULL DEFAULT '2'
                      COMMENT 'articles per week: 1=Mon, 2=Tue/Thu, 3=Mon/Wed/Fri, 5=weekdays, 7=daily',

  -- What this business is ---------------------------------------------------
  website_title       VARCHAR(200)  NULL,
  website_description TEXT          NULL COMMENT 'what the company does, in its own words',
  content_language    CHAR(2)       NOT NULL DEFAULT 'en',

  -- Location targeting. 'country' writes for a whole national market;
  -- 'local' narrows every keyword and angle to one city, which is right for a
  -- service business and wrong for an exporter.
  location_mode       ENUM('country','local') NOT NULL DEFAULT 'country',
  location_city       VARCHAR(120)  NULL,

  -- Generation instructions -------------------------------------------------
  writing_instructions TEXT         NULL COMMENT 'appended to every generation prompt',
  cta_url             VARCHAR(2048) NULL,
  cta_label           VARCHAR(120)  NULL,
  cta_as_button       TINYINT(1) NOT NULL DEFAULT 0,

  -- Images ------------------------------------------------------------------
  image_prompt        TEXT          NULL COMMENT 'house style for generated images',
  logo_path           VARCHAR(500)  NULL COMMENT 'relative to var/media',

  -- Article furniture -------------------------------------------------------
  author_box          TINYINT(1) NOT NULL DEFAULT 0,
  author_name         VARCHAR(120)  NULL,
  author_bio          TEXT          NULL,
  disclaimer_on       TINYINT(1) NOT NULL DEFAULT 0,
  disclaimer_text     TEXT          NULL,

  updated_at          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (site_id),
  CONSTRAINT fk_settings_site FOREIGN KEY (site_id) REFERENCES site (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------------
-- Business documents: the brochure, the company profile, the service list.
--
-- The extracted text is stored, not the file. A 12 MB PDF is not something to
-- read on every generation, and the only part of it the writer can use is the
-- prose. The original is kept on disk so somebody can check what a claim came
-- from — which is the whole point of grounding.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS site_document (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_id       INT UNSIGNED NOT NULL,
  filename      VARCHAR(255)  NOT NULL,
  mime          VARCHAR(100)  NOT NULL,
  bytes         INT UNSIGNED  NOT NULL DEFAULT 0,
  file_path     VARCHAR(500)  NULL COMMENT 'relative to var/media; NULL once pruned',
  extracted_text MEDIUMTEXT   NULL,
  extract_error VARCHAR(500)  NULL COMMENT 'why the text could not be read, if it could not',
  word_count    INT UNSIGNED  NOT NULL DEFAULT 0,
  is_active     TINYINT(1)    NOT NULL DEFAULT 1,
  uploaded_by   INT UNSIGNED  NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_doc_site (site_id, is_active),
  CONSTRAINT fk_doc_site FOREIGN KEY (site_id) REFERENCES site (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------------
-- What you sell, and what you do not.
--
-- The second half is the one that earns its place. A radiator manufacturer that
-- does not do on-site vehicle repair gets articles recommending on-site vehicle
-- repair unless something says otherwise, and every one of those is a lead sent
-- to a competitor.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS site_offering (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_id    INT UNSIGNED NOT NULL,
  kind       ENUM('sell','dont_sell') NOT NULL DEFAULT 'sell',
  label      VARCHAR(200) NOT NULL,
  sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_offering (site_id, kind, label),
  KEY idx_offering_site (site_id, kind, sort_order),
  CONSTRAINT fk_offering_site FOREIGN KEY (site_id) REFERENCES site (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------------
-- Internal link targets.
--
-- The previous build asked the writer for internal links and had none to give
-- it, so the brief either demanded invented URLs or forbade links entirely.
-- This is the list it links to. Every URL here is one somebody has confirmed
-- exists, which is the only way an internal link is better than no link.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS site_internal_link (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_id     INT UNSIGNED NOT NULL,
  url         VARCHAR(2048) NOT NULL,
  label       VARCHAR(255)  NULL COMMENT 'anchor hint; the page title if we could fetch it',
  url_hash    BINARY(20) NOT NULL COMMENT 'sha1(url), because a 2048-char column cannot be unique',
  times_used  INT UNSIGNED NOT NULL DEFAULT 0,
  created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_ilink (site_id, url_hash),
  CONSTRAINT fk_ilink_site FOREIGN KEY (site_id) REFERENCES site (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------------
-- Reader feedback on a draft.
--
-- Three buttons, not a five-star scale. "It is fundamentally wrong" and "it
-- needs a tweak" call for completely different responses, and no useful
-- distinction exists between three stars and four.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS article_feedback (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  article_id BIGINT UNSIGNED NOT NULL,
  user_id    INT UNSIGNED NULL,
  rating     ENUM('love','needs_work','wrong') NOT NULL,
  note       TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_fb_article (article_id, id),
  CONSTRAINT fk_fb_article FOREIGN KEY (article_id) REFERENCES article (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------------
-- Backlink comparison.
--
-- A snapshot per refresh rather than a live table, because the numbers only
-- mean something next to the date they were taken and next to the competitor
-- set they were compared against. Overwriting in place would make "we closed
-- the gap" unprovable.
--
-- What this is NOT: a link exchange. AutoSEO can offer one because it has
-- thousands of paying members to swap links between. This measures.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS backlink_snapshot (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_id         INT UNSIGNED NOT NULL,
  our_domains     INT UNSIGNED NULL COMMENT 'referring domains pointing at us',
  our_backlinks   INT UNSIGNED NULL,
  our_rank        SMALLINT UNSIGNED NULL COMMENT 'DataForSEO Rank, 0-100 scale — NOT Moz DA',
  our_spam_score  SMALLINT UNSIGNED NULL,
  median_domains  INT UNSIGNED NULL COMMENT 'median referring domains across the direct competitors',
  competitors     SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  api_cost_usd    DECIMAL(10,4) NOT NULL DEFAULT 0,
  error_message   TEXT NULL,
  created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_bls_site (site_id, id),
  CONSTRAINT fk_bls_site FOREIGN KEY (site_id) REFERENCES site (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS backlink_domain (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  snapshot_id  BIGINT UNSIGNED NOT NULL,
  domain       VARCHAR(255) NOT NULL,
  is_ours      TINYINT(1) NOT NULL DEFAULT 0,
  referring_domains INT UNSIGNED NULL,
  backlinks    INT UNSIGNED NULL,
  rank_score   SMALLINT UNSIGNED NULL COMMENT 'DataForSEO Rank on the 0-100 scale',
  spam_score   SMALLINT UNSIGNED NULL,
  PRIMARY KEY (id),
  KEY idx_bld_snap (snapshot_id, referring_domains),
  CONSTRAINT fk_bld_snap FOREIGN KEY (snapshot_id) REFERENCES backlink_snapshot (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------------
-- Search Console performance, cached per page per pull.
--
-- Keyed on the URL rather than the article id, because a site has pages this
-- app did not write and their performance is the baseline the written ones are
-- judged against.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS gsc_performance (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_id     INT UNSIGNED NOT NULL,
  page_url    VARCHAR(2048) NOT NULL,
  url_hash    BINARY(20) NOT NULL,
  period_days SMALLINT UNSIGNED NOT NULL DEFAULT 28,
  clicks      INT UNSIGNED NOT NULL DEFAULT 0,
  impressions INT UNSIGNED NOT NULL DEFAULT 0,
  ctr         DECIMAL(6,4) NOT NULL DEFAULT 0,
  position    DECIMAL(6,2) NULL,
  top_query   VARCHAR(500) NULL,
  fetched_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_gsc (site_id, url_hash, period_days),
  KEY idx_gsc_site (site_id, clicks),
  CONSTRAINT fk_gsc_site FOREIGN KEY (site_id) REFERENCES site (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------------
-- Additive columns on existing tables.
--
-- Guarded, so this file is safe on a database that has already seen part of it,
-- and so it runs on MySQL as well as MariaDB ("ADD COLUMN IF NOT EXISTS" is
-- MariaDB-only).
-- ---------------------------------------------------------------------------

-- article.meta_keywords / cms_tags: the metadata card on the review screen.
-- Kept as JSON arrays rather than a comma string because a keyword can contain
-- a comma and splitting on one is how "radiators, heavy-duty" becomes two tags.
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'article' AND COLUMN_NAME = 'meta_keywords');
SET @d := IF(@c = 0, 'ALTER TABLE article ADD COLUMN meta_keywords JSON NULL AFTER meta_description', 'DO 0');
PREPARE s FROM @d; EXECUTE s; DEALLOCATE PREPARE s;

SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'article' AND COLUMN_NAME = 'cms_tags');
SET @d := IF(@c = 0, 'ALTER TABLE article ADD COLUMN cms_tags JSON NULL AFTER meta_keywords', 'DO 0');
PREPARE s FROM @d; EXECUTE s; DEALLOCATE PREPARE s;

-- Key takeaways are stored separately from the body so the review screen can
-- show them as a checklist and the CMS driver can place them where that
-- platform wants them, rather than hoping a heading match finds them again.
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'article' AND COLUMN_NAME = 'takeaways_json');
SET @d := IF(@c = 0, 'ALTER TABLE article ADD COLUMN takeaways_json JSON NULL AFTER schema_json', 'DO 0');
PREPARE s FROM @d; EXECUTE s; DEALLOCATE PREPARE s;

-- Which settings produced this article. An article written under a 1,500-word
-- setting and one written under 3,000 are not comparable, and six months later
-- nobody remembers which was in force.
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'article' AND COLUMN_NAME = 'settings_json');
SET @d := IF(@c = 0, 'ALTER TABLE article ADD COLUMN settings_json JSON NULL AFTER brief_json', 'DO 0');
PREPARE s FROM @d; EXECUTE s; DEALLOCATE PREPARE s;

-- competitor.note: the one-line reason this domain is on the list. The
-- classifier can write it ("ranks for 34 of your terms"), and a person who adds
-- one by hand can say why in their own words.
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'competitor' AND COLUMN_NAME = 'note');
SET @d := IF(@c = 0, 'ALTER TABLE competitor ADD COLUMN note VARCHAR(300) NULL AFTER classifier_confidence', 'DO 0');
PREPARE s FROM @d; EXECUTE s; DEALLOCATE PREPARE s;

-- content_calendar.slot_no is TINYINT and was capped at 2 by convention. Daily
-- publishing needs 7, and the unique key already covers (site, week, slot).
-- Nothing to alter — recorded here so the next reader knows it was considered.

-- ---------------------------------------------------------------------------
-- Seed: one settings row per existing site, so every site has defaults and the
-- settings screen never has to cope with a missing row.
-- ---------------------------------------------------------------------------
INSERT INTO site_settings (site_id, website_title, publish_frequency)
SELECT s.id, s.name, '2' FROM site s
ON DUPLICATE KEY UPDATE site_id = site_settings.site_id;