-- LocalJobs migration: multi-provider AI models
--
-- Run this against an existing installation. It is safe to run twice: every
-- statement is IF NOT EXISTS or INSERT IGNORE, and nothing existing is altered
-- or dropped.
--
--   mysql -u youruser -p yourdb < migrations/001_ai_models.sql
--
-- Afterwards: Settings → AI providers to paste your keys, then Setup → AI
-- models to pick a default. If you already had a key in config.php as
-- ANTHROPIC_API_KEY, it keeps working — the settings value simply overrides it
-- when you fill it in.

CREATE TABLE IF NOT EXISTS ai_models (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  provider    ENUM('anthropic','openai','gemini') NOT NULL,
  label       VARCHAR(120) NOT NULL,
  model_id    VARCHAR(160) NOT NULL,
  tier        ENUM('fast','balanced','deep') NOT NULL DEFAULT 'balanced',
  max_tokens  SMALLINT UNSIGNED DEFAULT 2000,
  notes       VARCHAR(500),
  api_base    VARCHAR(255),
  is_default  TINYINT(1) NOT NULL DEFAULT 0,
  active      TINYINT(1) NOT NULL DEFAULT 1,
  sort_order  INT DEFAULT 100,
  use_count   INT NOT NULL DEFAULT 0,
  last_used_at DATETIME,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_provider_model (provider, model_id),
  INDEX idx_active (active, sort_order)
) ENGINE=InnoDB CHARSET=utf8mb4;

-- Older copies of this migration created the table without api_base. MySQL has
-- no portable "ADD COLUMN IF NOT EXISTS", so this is wrapped in a procedure
-- that checks first and is safe to run repeatedly.
DROP PROCEDURE IF EXISTS lj_add_api_base;
DELIMITER //
CREATE PROCEDURE lj_add_api_base()
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.COLUMNS
                 WHERE TABLE_SCHEMA = DATABASE()
                   AND TABLE_NAME = 'ai_models'
                   AND COLUMN_NAME = 'api_base') THEN
    ALTER TABLE ai_models ADD COLUMN api_base VARCHAR(255) AFTER notes;
  END IF;
END //
DELIMITER ;
CALL lj_add_api_base();
DROP PROCEDURE lj_add_api_base;

INSERT IGNORE INTO ai_models (provider, label, model_id, tier, max_tokens, notes, is_default, sort_order) VALUES
  ('anthropic','Claude Sonnet 4.6','claude-sonnet-4-6','balanced',3000,
   'Good default for localised copy: follows the "do not invent facts" instruction reliably.',1,10),
  ('anthropic','Claude Haiku 4.5','claude-haiku-4-5','fast',2000,
   'Cheapest and quickest. Fine for keyword clusters and nearby-town lists.',0,20),
  ('anthropic','Claude Opus 4.7','claude-opus-4-7','deep',4000,
   'Most expensive. Worth it only for the flagship site''s about and mission copy.',0,30),
  ('openai','GPT-4o','gpt-4o','balanced',3000,
   'Strong all-rounder. Tends to write more marketing-flavoured copy than Claude — edit accordingly.',0,40),
  ('openai','GPT-4o mini','gpt-4o-mini','fast',2000,
   'Cheap and quick. Good for keyword and locality lists.',0,50),
  ('gemini','Gemini 2.0 Flash','gemini-2.0-flash','fast',2000,
   'Very cheap and fast. Solid UK geography, which suits the nearby-towns prompt.',0,60),
  ('gemini','Gemini 1.5 Pro','gemini-1.5-pro','deep',3000,
   'Slower but more considered. Useful when Flash gives thin copy.',0,70);

INSERT IGNORE INTO settings (`key`, `value`) VALUES
  ('anthropic_api_key', ''),
  ('openai_api_key',    ''),
  ('gemini_api_key',    '');
