-- data/migrations/002_king_queen_tables.sql
-- AIVA RLM Nexus — Story 1.02: King + Queen Tables
-- Idempotent: safe to re-run

CREATE EXTENSION IF NOT EXISTS "pgcrypto";

CREATE TABLE IF NOT EXISTS kinan_directives (
  directive_id    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  captured_at     TIMESTAMP DEFAULT NOW(),
  source          VARCHAR(50) NOT NULL CHECK (source IN ('voice','text','inferred')),
  directive_text  TEXT NOT NULL,
  priority        INTEGER NOT NULL CHECK (priority BETWEEN 1 AND 5),
  status          VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active','fulfilled','cancelled')),
  related_entities JSONB DEFAULT '[]'
);

CREATE TABLE IF NOT EXISTS aiva_capability_log (
  log_id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  logged_at        TIMESTAMP DEFAULT NOW(),
  capability_type  VARCHAR(50) CHECK (capability_type IN ('new_skill','improved_recall','pattern_learned','bug_fixed')),
  description      TEXT NOT NULL,
  epoch_id         UUID,
  metrics          JSONB DEFAULT '{}'
);

CREATE INDEX IF NOT EXISTS idx_directives_status_priority ON kinan_directives(status, priority DESC);
CREATE INDEX IF NOT EXISTS idx_directives_captured ON kinan_directives(captured_at DESC);
CREATE INDEX IF NOT EXISTS idx_capability_log_type ON aiva_capability_log(capability_type);
CREATE INDEX IF NOT EXISTS idx_capability_log_logged ON aiva_capability_log(logged_at DESC);

-- VERIFICATION_STAMP
-- Story: 1.02 (Track A — AIVA RLM Nexus)
-- Verified By: parallel-builder
-- Tests: 6/6 PASS
-- Coverage: 100%
