-- PR 2 (roadmap #24): DB-backed conversation state layer

CREATE TABLE IF NOT EXISTS conversation_states (
  user_id UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  conversation_id UUID REFERENCES conversations(id) ON DELETE SET NULL,
  pending_intent_type TEXT,
  pending_intent_payload JSONB NOT NULL DEFAULT '{}'::jsonb,
  pending_action_source TEXT,
  last_question_asked TEXT,
  clarification_state JSONB NOT NULL DEFAULT '{}'::jsonb,
  active_topic TEXT,
  active_thread TEXT,
  expires_at TIMESTAMPTZ,
  cleared_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_conversation_states_conversation
  ON conversation_states(conversation_id)
  WHERE conversation_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_conversation_states_expires
  ON conversation_states(expires_at)
  WHERE expires_at IS NOT NULL AND cleared_at IS NULL;
