BEGIN;

SET ROLE capability_role;

ALTER TABLE capability.skill_profiles
  ADD COLUMN IF NOT EXISTS applicability JSONB NOT NULL DEFAULT '{}'::jsonb,
  ADD COLUMN IF NOT EXISTS dependency_refs TEXT[] NOT NULL DEFAULT '{}',
  ADD COLUMN IF NOT EXISTS validation_evidence_refs TEXT[] NOT NULL DEFAULT '{}',
  ADD COLUMN IF NOT EXISTS independent_evaluation_refs TEXT[] NOT NULL DEFAULT '{}',
  ADD COLUMN IF NOT EXISTS known_failure_conditions TEXT[] NOT NULL DEFAULT '{}',
  ADD COLUMN IF NOT EXISTS verified_outcome_count BIGINT NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS must_not_worsen_satisfied BOOLEAN NOT NULL DEFAULT FALSE;

ALTER TABLE capability.skill_profile_heads DROP CONSTRAINT IF EXISTS skill_profile_heads_status_check;
ALTER TABLE capability.skill_profile_heads
  ADD CONSTRAINT skill_profile_heads_status_check CHECK (status IN ('CANDIDATE','EVALUATING','VALIDATED','ACTIVE','DEGRADED','SUSPENDED','RETIRED'));

CREATE TABLE IF NOT EXISTS capability.reflex_revisions (
  reflex_id TEXT NOT NULL,
  revision BIGINT NOT NULL CHECK (revision > 0),
  principal_id TEXT NOT NULL,
  source_skill_id TEXT NOT NULL,
  source_skill_revision BIGINT NOT NULL CHECK (source_skill_revision > 0),
  kind TEXT NOT NULL,
  applicability JSONB NOT NULL,
  implementation_ref TEXT NOT NULL,
  implementation_version TEXT NOT NULL DEFAULT '',
  artifact_digest TEXT NOT NULL DEFAULT '',
  dependency_refs TEXT[] NOT NULL DEFAULT '{}',
  validation_evidence_refs TEXT[] NOT NULL DEFAULT '{}',
  known_failure_experience_refs TEXT[] NOT NULL DEFAULT '{}',
  shadow_policy JSONB NOT NULL DEFAULT '{}'::jsonb,
  revalidation_policy JSONB NOT NULL DEFAULT '{}'::jsonb,
  last_validated_at BIGINT NOT NULL DEFAULT 0,
  lifecycle TEXT NOT NULL,
  supersedes_revision BIGINT NOT NULL DEFAULT 0,
  execution_count BIGINT NOT NULL DEFAULT 0,
  failure_count BIGINT NOT NULL DEFAULT 0,
  divergence_count BIGINT NOT NULL DEFAULT 0,
  recorded_at BIGINT NOT NULL CHECK (recorded_at > 0),
  PRIMARY KEY (reflex_id, revision),
  CHECK (lifecycle IN ('CANDIDATE','VALIDATING','ACTIVE','DEGRADED','STALE','REVALIDATING','SUSPENDED','RETIRED'))
);
CREATE TABLE IF NOT EXISTS capability.reflex_heads (
  reflex_id TEXT PRIMARY KEY,
  revision BIGINT NOT NULL,
  lifecycle TEXT NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
GRANT SELECT, INSERT, UPDATE ON capability.reflex_revisions, capability.reflex_heads TO capability_role;

RESET ROLE;
SET ROLE self_role;

CREATE TABLE IF NOT EXISTS self.improvement_needs (
  improvement_need_id TEXT PRIMARY KEY,
  principal_id TEXT NOT NULL,
  subject_ref JSONB NOT NULL,
  problem_summary TEXT NOT NULL,
  evidence_refs JSONB NOT NULL,
  expected_benefit TEXT NOT NULL,
  priority INTEGER NOT NULL,
  state TEXT NOT NULL,
  created_at_unix_millis BIGINT NOT NULL
);
ALTER TABLE self.learning_ledger
  ADD COLUMN IF NOT EXISTS improvement_need_ref JSONB,
  ADD COLUMN IF NOT EXISTS proposed_change_ref JSONB,
  ADD COLUMN IF NOT EXISTS expected_improvement TEXT NOT NULL DEFAULT '',
  ADD COLUMN IF NOT EXISTS evaluation_refs JSONB NOT NULL DEFAULT '[]'::jsonb,
  ADD COLUMN IF NOT EXISTS development_gate_ref JSONB,
  ADD COLUMN IF NOT EXISTS before_version_refs JSONB NOT NULL DEFAULT '[]'::jsonb,
  ADD COLUMN IF NOT EXISTS after_version_refs JSONB NOT NULL DEFAULT '[]'::jsonb,
  ADD COLUMN IF NOT EXISTS side_effects JSONB NOT NULL DEFAULT '[]'::jsonb,
  ADD COLUMN IF NOT EXISTS rollback_ref JSONB;
GRANT SELECT, INSERT, UPDATE ON self.improvement_needs, self.learning_ledger TO self_role;

RESET ROLE;
COMMIT;