-- backend/memory/create_oracle_memories_table.sql
-- Run once on the Oracle instance (Oracle 12c+ required for FETCH NEXT syntax).
--
-- Changes vs prototype:
--   + INDEX on user_id to avoid full-scan on every list_memories() call.
--   + NVARCHAR2 / NCLOB commented out as alternatives for multi-byte content.

CREATE TABLE memories (
    user_id    VARCHAR2(255)  NOT NULL,
    memory_id  VARCHAR2(36)   NOT NULL,   -- UUID format (36 chars)
    data       CLOB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    CONSTRAINT pk_memories PRIMARY KEY (user_id, memory_id)
);

-- Speeds up list_memories() WHERE user_id = :x (avoids PK full-scan)
CREATE INDEX idx_memories_user ON memories (user_id);

-- Auto-update updated_at on every UPDATE
CREATE OR REPLACE TRIGGER trg_memories_updated_at
BEFORE UPDATE ON memories
FOR EACH ROW
BEGIN
    :NEW.updated_at := CURRENT_TIMESTAMP;
END;
/