-- AI Ticket Analyzer — provider column for per-run LLM provider tracking.
--
-- Adds a `provider` field so multiple analyses can coexist for the same
-- ticket from different LLM providers (Claude vs DeepSeek-via-OpenRouter)
-- without colliding on the (ticket_number, analysis_version) uniqueness.
--
-- analysis_version is now monotonic *within (ticket_number, provider)*, not
-- globally per ticket. This means:
--   - The first Anthropic run is v1, first OpenRouter run is also v1.
--   - Re-runs of Anthropic produce v2, v3, …; OpenRouter likewise.

ALTER TABLE analyzer_analyses
  ADD COLUMN IF NOT EXISTS provider TEXT NOT NULL DEFAULT 'anthropic'
    CHECK (provider IN ('anthropic', 'openrouter'));

ALTER TABLE analyzer_jobs
  ADD COLUMN IF NOT EXISTS provider TEXT NOT NULL DEFAULT 'anthropic'
    CHECK (provider IN ('anthropic', 'openrouter'));

-- Replace the old unique constraint with a provider-scoped one.
ALTER TABLE analyzer_analyses
  DROP CONSTRAINT IF EXISTS analyzer_analyses_version_unique;
ALTER TABLE analyzer_analyses
  ADD CONSTRAINT analyzer_analyses_version_unique
  UNIQUE (ticket_number, provider, analysis_version);

-- Useful covering index for the listing path.
DROP INDEX IF EXISTS idx_analyzer_analyses_ticket_version;
CREATE INDEX IF NOT EXISTS idx_analyzer_analyses_ticket_provider_version
  ON analyzer_analyses (ticket_number, provider, analysis_version DESC);

COMMENT ON COLUMN analyzer_analyses.provider IS
  'Which LLM provider produced this analysis. anthropic=Claude direct; '
  'openrouter=DeepSeek (V4 Pro/Flash + R1) via OpenRouter.';

COMMENT ON COLUMN analyzer_jobs.provider IS
  'Provider the worker should use when running this job. Set by the analyze '
  'endpoint based on the user''s pick.';