-- Unified Memory MCP — schema additions
-- Applied after existing claude-life-mcp schema (metixrom_life database).
-- Safe to re-run: all statements are idempotent.

-- ----------------------------------------------------------------------------
-- memories — durable, deliberate saves (user / feedback / project / reference)
-- Content body stored as ciphertext; name/description/tags remain plaintext so
-- the MCP server can serve search + listing without needing the encryption key.
-- ----------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS memories (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    uuid            CHAR(36)        NOT NULL,
    type            ENUM('user','feedback','project','reference') NOT NULL,
    name            VARCHAR(255)    NOT NULL,
    description     VARCHAR(512)    NOT NULL DEFAULT '',
    tags            JSON            NULL,
    project         VARCHAR(128)    NULL,

    content_ciphertext  MEDIUMBLOB  NOT NULL,
    content_iv          VARBINARY(16) NOT NULL,
    content_alg         VARCHAR(32) NOT NULL DEFAULT 'AES-256-GCM',
    content_hash        CHAR(32)    NOT NULL,  -- sha256(plaintext)[:32 hex chars]

    source          ENUM('claude-code','claude-desktop','claude-web','manual','import') NOT NULL DEFAULT 'manual',

    created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at      DATETIME        NULL,

    PRIMARY KEY (id),
    UNIQUE KEY uq_memories_uuid (uuid),
    KEY ix_memories_type_project_created (type, project, created_at),
    KEY ix_memories_hash (content_hash),
    FULLTEXT KEY ft_memories_name_description (name, description)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------------------
-- observations — claude-mem-style auto-capture of tool use.
-- title is plaintext (so search works); narrative is ciphertext.
-- 30s dedup window enforced via (content_hash, created_at) check in handler.
-- ----------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS observations (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    uuid            CHAR(36)        NOT NULL,
    session_id      VARCHAR(64)     NOT NULL,
    project         VARCHAR(128)    NULL,
    tool_name       VARCHAR(64)     NOT NULL,
    title           VARCHAR(255)    NOT NULL,

    narrative_ciphertext MEDIUMBLOB NOT NULL,
    narrative_iv        VARBINARY(16) NOT NULL,
    narrative_alg       VARCHAR(32) NOT NULL DEFAULT 'AES-256-GCM',
    content_hash        CHAR(32)    NOT NULL,

    created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    UNIQUE KEY uq_observations_uuid (uuid),
    KEY ix_observations_session_created (session_id, created_at),
    KEY ix_observations_project_created (project, created_at),
    KEY ix_observations_hash_created (content_hash, created_at),
    FULLTEXT KEY ft_observations_title (title)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;