BEGIN;
SET ROLE capability_role;

CREATE TABLE IF NOT EXISTS capability.cognitive_task_evaluations (
  evaluation_id TEXT PRIMARY KEY,
  task_profile_id TEXT NOT NULL,
  binding_id TEXT NOT NULL,
  binding_revision BIGINT NOT NULL,
  capability_id TEXT NOT NULL,
  result TEXT NOT NULL,
  quality_score_millis BIGINT NOT NULL,
  reliability_score_millis BIGINT NOT NULL,
  latency_score_millis BIGINT NOT NULL,
  overall_score_millis BIGINT NOT NULL,
  critical_failure_millis BIGINT NOT NULL,
  benchmark_version TEXT NOT NULL,
  artifact_id TEXT NOT NULL,
  observed_at_unix_millis BIGINT NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK (binding_revision > 0),
  CHECK (result IN ('UNKNOWN','SUPPORTED','UNSUPPORTED')),
  CHECK (quality_score_millis BETWEEN 0 AND 1000),
  CHECK (reliability_score_millis BETWEEN 0 AND 1000),
  CHECK (latency_score_millis BETWEEN 0 AND 1000),
  CHECK (overall_score_millis BETWEEN 0 AND 1000),
  CHECK (critical_failure_millis BETWEEN 0 AND 1000),
  CHECK (observed_at_unix_millis > 0)
);

CREATE INDEX IF NOT EXISTS cognitive_task_evaluations_lookup
  ON capability.cognitive_task_evaluations(task_profile_id, binding_id, binding_revision, observed_at_unix_millis DESC);

GRANT SELECT, INSERT ON capability.cognitive_task_evaluations TO capability_role;
RESET ROLE;
COMMIT;