SET DEFINE OFF
WHENEVER SQLERROR EXIT SQL.SQLCODE

-- 006 · MEMORY — Agents Memory § 6 (the experience sub-index), § 7.4b (publication states), Software Architecture § Challenges
-- ("git is the store, Oracle the projection"): article bodies and AGENTS.md files live in the platform-owned git repository
-- aisa-memory; these rows are an index rebuildable from the repository (commit first, row second, reconciled at start).

DECLARE
    PROCEDURE ddl(p_sql IN VARCHAR2) IS
    BEGIN
        EXECUTE IMMEDIATE p_sql;
    EXCEPTION
        WHEN OTHERS THEN
            IF SQLCODE NOT IN (-955, -1408, -1430, -2260, -2261, -2264, -2275) THEN RAISE; END IF;
    END;
BEGIN
    -- ---------------------------------------------------------------- MEMORY_DOMAINS (the tree skeleton as rows — D67)
    ddl(q'~CREATE TABLE "SRD_SUPPORT"."MEMORY_DOMAINS" (
        "NODE_PATH"                 NVARCHAR2(256)  NOT NULL,
        "PARENT_PATH"               NVARCHAR2(256),
        "BRANCH"                    NVARCHAR2(16)   NOT NULL,
        "OWNER_PROFILE_KEY"         NVARCHAR2(128),
        "INDEX_COMMIT_SHA"          NVARCHAR2(64),
        "INDEX_LINES"               NUMBER(10),
        "ARTICLE_COUNT"             NUMBER(10)      DEFAULT 0 NOT NULL,
        "CREATED_BY"                NVARCHAR2(16)   NOT NULL,
        "CREATED_ON_UTC"            TIMESTAMP(6)    NOT NULL,
        "REFRESHED_ON_UTC"          TIMESTAMP(6),
        "RETIRED_ON_UTC"            TIMESTAMP(6),
        CONSTRAINT "PK_MEMORY_DOMAINS" PRIMARY KEY ("NODE_PATH"),
        CONSTRAINT "FK_MDOM_PARENT" FOREIGN KEY ("PARENT_PATH") REFERENCES "SRD_SUPPORT"."MEMORY_DOMAINS" ("NODE_PATH"),
        CONSTRAINT "CK_MDOM_BRANCH" CHECK ("BRANCH" IN ('platform','connectors','configuration','source','support')),
        CONSTRAINT "CK_MDOM_CREATED" CHECK ("CREATED_BY" IN ('seed','gate','summarizer','migration'))
    )~');
    ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."MEMORY_DOMAINS" IS 'One row per node of the memory tree (Agents Memory § 1); the AGENTS.md index of the node is at INDEX_COMMIT_SHA. OWNER_PROFILE_KEY = the single writing profile (Agents § 0.1). CREATED_BY summarizer = the auto-learning path (Agents § 0.6); gate = the manual node-creation gate.'~');

    -- ---------------------------------------------------------------- MEMORY_ARTICLES (index over git; immutable once active)
    ddl(q'~CREATE TABLE "SRD_SUPPORT"."MEMORY_ARTICLES" (
        "ID"                        NVARCHAR2(64)   NOT NULL,
        "NODE_PATH"                 NVARCHAR2(256)  NOT NULL,
        "ARTICLE_KEY"               NVARCHAR2(128)  NOT NULL,
        "VERSION"                   NUMBER(10)      NOT NULL,
        "KIND"                      NVARCHAR2(16)   NOT NULL,
        "TITLE"                     NVARCHAR2(512)  NOT NULL,
        "STATE"                     NVARCHAR2(16)   NOT NULL,
        "OWNER_PROFILE_KEY"         NVARCHAR2(128)  NOT NULL,
        "GIT_SHA"                   NVARCHAR2(64)   NOT NULL,
        "CONTENT_HASH"              NVARCHAR2(64)   NOT NULL,
        "PROVENANCE"                CLOB            NOT NULL,
        "REACHES_UNTRUSTED"         NUMBER(1)       NOT NULL,
        "HUMAN_REVIEWED"            NUMBER(1)       DEFAULT 0 NOT NULL,
        "CONFIRMATION_KIND"         NVARCHAR2(16),
        "CONFIRMED_BY"              NVARCHAR2(128),
        "CONFIRMED_ON_UTC"          TIMESTAMP(6),
        "OBSERVED_ENVIRONMENT"      NVARCHAR2(32),
        "OBSERVED_VERSION"          NVARCHAR2(128),
        "SUPERSEDED_BY_ID"          NVARCHAR2(64),
        "RETIRED_ON_UTC"            TIMESTAMP(6),
        "RETIRED_REASON"            NVARCHAR2(1000),
        "PROPOSED_BY_CASE_ID"       NVARCHAR2(64),
        "CREATED_ON_UTC"            TIMESTAMP(6)    NOT NULL,
        "ROW_VERSION"               NUMBER(19)      DEFAULT 0 NOT NULL,
        CONSTRAINT "PK_MEMORY_ARTICLES" PRIMARY KEY ("ID"),
        CONSTRAINT "UK_MEMORY_ARTICLES" UNIQUE ("NODE_PATH", "ARTICLE_KEY", "VERSION"),
        CONSTRAINT "FK_MART_NODE" FOREIGN KEY ("NODE_PATH") REFERENCES "SRD_SUPPORT"."MEMORY_DOMAINS" ("NODE_PATH"),
        CONSTRAINT "FK_MART_SUPERSEDED" FOREIGN KEY ("SUPERSEDED_BY_ID") REFERENCES "SRD_SUPPORT"."MEMORY_ARTICLES" ("ID"),
        CONSTRAINT "FK_MART_CASE" FOREIGN KEY ("PROPOSED_BY_CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
        CONSTRAINT "CK_MART_KIND" CHECK ("KIND" IN ('article','skill_source','index_line','checklist','experience')),
        CONSTRAINT "CK_MART_STATE" CHECK ("STATE" IN ('proposed','active','retired')),
        CONSTRAINT "CK_MART_FLAGS" CHECK ("REACHES_UNTRUSTED" IN (0,1) AND "HUMAN_REVIEWED" IN (0,1)),
        CONSTRAINT "CK_MART_PROV_JSON" CHECK ("PROVENANCE" IS JSON),
        CONSTRAINT "CK_MART_CONFIRM" CHECK ("CONFIRMATION_KIND" IS NULL OR "CONFIRMATION_KIND" IN ('human_review','two_uses','migration_measured')),
        CONSTRAINT "CK_MART_ACTIVE" CHECK ("STATE" <> 'active' OR ("CONFIRMATION_KIND" IS NOT NULL AND "CONFIRMED_ON_UTC" IS NOT NULL))
    )~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_MART_NODE_STATE" ON "SRD_SUPPORT"."MEMORY_ARTICLES" ("NODE_PATH", "STATE")~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_MART_GIT" ON "SRD_SUPPORT"."MEMORY_ARTICLES" ("GIT_SHA")~');
    ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."MEMORY_ARTICLES" IS 'Agents Memory § 7.4b. case-local entries are papers, not rows; the tree holds proposed/active/retired only. CK_MART_ACTIVE: nothing is active without a recorded confirmation (human review, or two independent uncontradicted uses — the initial rule, D56). PROVENANCE = {cases[], profile_versions[], sources[]}; REACHES_UNTRUSTED stays marked.'~');

    -- ---------------------------------------------------------------- MEMORY_USES (version pinning; the evidence for two-uses)
    ddl(q'~CREATE TABLE "SRD_SUPPORT"."MEMORY_USES" (
        "ARTICLE_ID"                NVARCHAR2(64)   NOT NULL,
        "CASE_ID"                   NVARCHAR2(64)   NOT NULL,
        "TASK_ID"                   NVARCHAR2(64)   NOT NULL,
        "USED_ON_UTC"               TIMESTAMP(6)    NOT NULL,
        "CONTRADICTED"              NUMBER(1)       DEFAULT 0 NOT NULL,
        "CONTRADICTION_PAPER_ID"    NVARCHAR2(64),
        CONSTRAINT "PK_MEMORY_USES" PRIMARY KEY ("ARTICLE_ID", "CASE_ID"),
        CONSTRAINT "FK_MUSE_ARTICLE" FOREIGN KEY ("ARTICLE_ID") REFERENCES "SRD_SUPPORT"."MEMORY_ARTICLES" ("ID"),
        CONSTRAINT "FK_MUSE_CASE" FOREIGN KEY ("CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
        CONSTRAINT "FK_MUSE_TASK" FOREIGN KEY ("TASK_ID") REFERENCES "SRD_SUPPORT"."TASKS" ("ID"),
        CONSTRAINT "FK_MUSE_PAPER" FOREIGN KEY ("CONTRADICTION_PAPER_ID") REFERENCES "SRD_SUPPORT"."PAPERS" ("ID"),
        CONSTRAINT "CK_MUSE_FLAG" CHECK ("CONTRADICTED" IN (0,1))
    )~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_MUSE_CASE" ON "SRD_SUPPORT"."MEMORY_USES" ("CASE_ID")~');
    ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."MEMORY_USES" IS 'Version pinning (Agents Memory § 7.4b): a case records the article versions it relied on, so retiring an article lists the cases that pinned it. Two rows from distinct cases with CONTRADICTED = 0 satisfy the two-uses confirmation; one CONTRADICTED = 1 blocks it.'~');

    -- ---------------------------------------------------------------- EXPERIENCE_ENTRIES (support/experience and platform/experience)
    ddl(q'~CREATE TABLE "SRD_SUPPORT"."EXPERIENCE_ENTRIES" (
        "ID"                        NVARCHAR2(64)   NOT NULL,
        "INDEX_NODE"                NVARCHAR2(64)   NOT NULL,
        "CASE_ID"                   NVARCHAR2(64)   NOT NULL,
        "STATE"                     NVARCHAR2(16)   NOT NULL,
        "SYMPTOM_SIGNATURE"         NVARCHAR2(1000) NOT NULL,
        "SYMPTOM_NORMALISED"        NVARCHAR2(1000) NOT NULL,
        "SYMPTOM_HASH"              NVARCHAR2(64)   NOT NULL,
        "SYSTEM_ERROR"              NVARCHAR2(512),
        "SYSTEMS"                   CLOB            NOT NULL,
        "ENVIRONMENT"               NVARCHAR2(32),
        "REQUEST_TYPE"              NVARCHAR2(32),
        "MECHANISM"                 NVARCHAR2(2000),
        "MECHANISM_LAYER"           NVARCHAR2(64),
        "FIX_SHAPE"                 NVARCHAR2(16),
        "SKILL_KEY"                 NVARCHAR2(128),
        "LINKS"                     CLOB,
        "ARTEFACTS"                 CLOB,
        "OBSERVED_VERSION"          NVARCHAR2(128),
        "ARTICLE_ID"                NVARCHAR2(64),
        "CREATED_BY_PROFILE"        NVARCHAR2(128)  NOT NULL,
        "CREATED_ON_UTC"            TIMESTAMP(6)    NOT NULL,
        "CONFIRMED_BY"              NVARCHAR2(128),
        "CONFIRMED_ON_UTC"          TIMESTAMP(6),
        "RETIRED_ON_UTC"            TIMESTAMP(6),
        "ROW_VERSION"               NUMBER(19)      DEFAULT 0 NOT NULL,
        CONSTRAINT "PK_EXPERIENCE_ENTRIES" PRIMARY KEY ("ID"),
        CONSTRAINT "FK_EXP_CASE" FOREIGN KEY ("CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
        CONSTRAINT "FK_EXP_ARTICLE" FOREIGN KEY ("ARTICLE_ID") REFERENCES "SRD_SUPPORT"."MEMORY_ARTICLES" ("ID"),
        CONSTRAINT "CK_EXP_NODE" CHECK ("INDEX_NODE" IN ('support/experience','platform/experience')),
        CONSTRAINT "CK_EXP_STATE" CHECK ("STATE" IN ('proposed','active','retired')),
        CONSTRAINT "CK_EXP_FIX" CHECK ("FIX_SHAPE" IS NULL OR "FIX_SHAPE" IN ('data_fix','config_cell','code','answer','combination','none')),
        CONSTRAINT "CK_EXP_JSON" CHECK ("SYSTEMS" IS JSON AND ("LINKS" IS NULL OR "LINKS" IS JSON) AND ("ARTEFACTS" IS NULL OR "ARTEFACTS" IS JSON))
    )~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_EXP_SYMPTOM" ON "SRD_SUPPORT"."EXPERIENCE_ENTRIES" ("INDEX_NODE", "STATE", "SYMPTOM_HASH")~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_EXP_CASE" ON "SRD_SUPPORT"."EXPERIENCE_ENTRIES" ("CASE_ID")~');
    ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."EXPERIENCE_ENTRIES" IS 'Agents Memory § 6 (contract: contracts/drafts/experience-entry.schema.json): symptom signature verbatim + normalised (lower-cased, identifiers → handles, digits collapsed — the initial normalisation; depth is a class-C measurement) + its hash for the exact lookup; a grep index over SYMPTOM_NORMALISED is the first retrieval, vector search only if it beats the measured baseline. No PII by construction: identifiers are already handles at the connector.'~');
END;
/
