-- LocalJobs migration 002: job search guides
--
-- Adds per-site guides, saved AI topic suggestions, and provider-agnostic
-- generation batches. Safe to run more than once: every table is
-- IF NOT EXISTS, and the one data change is guarded so it only touches values
-- nobody has customised.
--
--   mysql -u youruser -p yourdb < migrations/002_guides.sql
--
-- Requires 001_ai_models.sql to have been run first.

-- A published article on one site. Read live by the site, so publishing does
-- not need a rebuild.
CREATE TABLE IF NOT EXISTS guides (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  site_id       INT NOT NULL,
  sector_id     INT NULL,                        -- optional: what the guide is about
  suggestion_id INT NULL,                        -- the topic it was written from, if any
  batch_id      INT NULL,                        -- the batch that wrote it, if any
  title         VARCHAR(255) NOT NULL,
  slug          VARCHAR(190) NOT NULL,
  summary       VARCHAR(500),                    -- one or two sentences for listings
  body_html     MEDIUMTEXT,                      -- sanitised; internal links validated at render
  faq           JSON,                            -- [{q,a}]
  meta_title    VARCHAR(255),
  meta_desc     VARCHAR(500),
  word_count    INT DEFAULT 0,
  status        ENUM('draft','published') NOT NULL DEFAULT 'draft',
  ai_model_id   INT NULL,                        -- which model wrote it
  published_at  DATETIME,
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_site_slug (site_id, slug),
  INDEX idx_site_status (site_id, status, published_at),
  INDEX idx_sector (sector_id)
) ENGINE=InnoDB CHARSET=utf8mb4;

-- A topic idea. Unused ones stay here indefinitely, so a batch of twelve ideas
-- can be written three at a time over a month.
CREATE TABLE IF NOT EXISTS guide_suggestions (
  id             INT AUTO_INCREMENT PRIMARY KEY,
  site_id        INT NOT NULL,
  sector_id      INT NULL,
  title          VARCHAR(255) NOT NULL,
  angle          TEXT,                           -- what makes it local and worth reading
  summary        TEXT,
  search_intent  VARCHAR(255),                   -- what the reader is trying to do
  target_keyword VARCHAR(190),
  brief          VARCHAR(500),                   -- the brief it was generated from, if any
  status         ENUM('pending','queued','written','dismissed') NOT NULL DEFAULT 'pending',
  guide_id       INT NULL,
  batch_id       INT NULL,
  ai_model_id    INT NULL,
  created_at     DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at     DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_site_status (site_id, status),
  INDEX idx_batch (batch_id)
) ENGINE=InnoDB CHARSET=utf8mb4;

-- One generation run. Provider-agnostic: 'native' uses the provider's own
-- batch API (cheaper, asynchronous); 'queued' works through the items one at a
-- time from our side, which works with any provider or gateway.
CREATE TABLE IF NOT EXISTS ai_batches (
  id                INT AUTO_INCREMENT PRIMARY KEY,
  site_id           INT NULL,
  kind              VARCHAR(40) NOT NULL DEFAULT 'guides',
  ai_model_id       INT NULL,
  provider          VARCHAR(20) NOT NULL,
  mode              ENUM('native','queued') NOT NULL DEFAULT 'native',
  status            ENUM('queued','submitted','in_progress','ended','processed','failed','canceled')
                    NOT NULL DEFAULT 'queued',
  provider_batch_id VARCHAR(120),                -- msgbatch_… / batch_…
  provider_file_id  VARCHAR(120),                -- OpenAI input file
  item_count        INT NOT NULL DEFAULT 0,
  done_count        INT NOT NULL DEFAULT 0,
  failed_count      INT NOT NULL DEFAULT 0,
  error             TEXT,
  created_at        DATETIME DEFAULT CURRENT_TIMESTAMP,
  submitted_at      DATETIME,
  ended_at          DATETIME,
  processed_at      DATETIME,
  checked_at        DATETIME,
  INDEX idx_status (status)
) ENGINE=InnoDB CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ai_batch_items (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  batch_id      INT NOT NULL,
  custom_id     VARCHAR(64) NOT NULL,            -- matches the provider's result back to us
  suggestion_id INT NULL,
  planned_slug  VARCHAR(190),                    -- reserved at submission so siblings can link to it
  planned_title VARCHAR(255),
  request       MEDIUMTEXT,                      -- the prompt, JSON
  response_text MEDIUMTEXT,
  status        ENUM('pending','submitted','succeeded','failed','processed') NOT NULL DEFAULT 'pending',
  error         TEXT,
  guide_id      INT NULL,
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_batch_custom (batch_id, custom_id),
  INDEX idx_batch_status (batch_id, status)
) ENGINE=InnoDB CHARSET=utf8mb4;

-- The ceilings seeded by 001 were sized for short copy. A 1,500-word guide in
-- HTML inside JSON needs roughly 6,000–7,000 output tokens, and because the
-- ceiling is a hard cap, every guide would be cut off mid-sentence. Raise only
-- the rows still carrying the exact value 001 seeded — anything you changed
-- yourself is left alone.
UPDATE ai_models SET max_tokens = 8000 WHERE model_id = 'claude-sonnet-4-6' AND max_tokens = 3000;
UPDATE ai_models SET max_tokens = 8000 WHERE model_id = 'claude-haiku-4-5'  AND max_tokens = 2000;
UPDATE ai_models SET max_tokens = 8000 WHERE model_id = 'claude-opus-4-7'   AND max_tokens = 4000;
UPDATE ai_models SET max_tokens = 8000 WHERE model_id = 'gpt-4o'            AND max_tokens = 3000;
UPDATE ai_models SET max_tokens = 8000 WHERE model_id = 'gpt-4o-mini'       AND max_tokens = 2000;
UPDATE ai_models SET max_tokens = 8000 WHERE model_id = 'gemini-2.0-flash'  AND max_tokens = 2000;
UPDATE ai_models SET max_tokens = 8000 WHERE model_id = 'gemini-1.5-pro'    AND max_tokens = 3000;
