-- Directive memory system: optional key-value storage scoped to a directive.
-- Allows directives to persist learnings, decisions, and context across steps.

-- Add memory_enabled flag to directives
ALTER TABLE directives ADD COLUMN memory_enabled BOOLEAN NOT NULL DEFAULT false;

-- Key-value memory entries scoped to a directive
CREATE TABLE directive_memories (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    directive_id UUID NOT NULL REFERENCES directives(id) ON DELETE CASCADE,
    key VARCHAR(255) NOT NULL,
    value TEXT NOT NULL,
    category VARCHAR(100),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(directive_id, key)
);

CREATE INDEX idx_directive_memories_directive_id ON directive_memories(directive_id);
CREATE INDEX idx_directive_memories_category ON directive_memories(directive_id, category);
