BEGIN;

SET ROLE capability_role;

CREATE TABLE IF NOT EXISTS capability.cognitive_model_qualification_observations (
  observation_id TEXT PRIMARY KEY,
  provider_instance_id TEXT NOT NULL,
  provider_model_id TEXT NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('REACHABLE','RESPONDED_WITHOUT_QUALIFICATION','UNAVAILABLE')),
  finish_reason TEXT NOT NULL,
  latency_millis BIGINT NOT NULL CHECK (latency_millis >= 0),
  output_observed BOOLEAN NOT NULL,
  quality_assessed BOOLEAN NOT NULL DEFAULT FALSE CHECK (quality_assessed = FALSE),
  category TEXT NOT NULL CHECK (category = 'UNRATED'),
  observed_at_unix_millis BIGINT NOT NULL CHECK (observed_at_unix_millis > 0)
);

CREATE INDEX IF NOT EXISTS cognitive_model_qualification_lookup
  ON capability.cognitive_model_qualification_observations(provider_instance_id, provider_model_id, observed_at_unix_millis DESC);

GRANT SELECT, INSERT ON capability.cognitive_model_qualification_observations TO capability_role;

RESET ROLE;
COMMIT;