SET DEFINE OFF
WHENEVER SQLERROR EXIT SQL.SQLCODE

-- 001 · WORK and PLANNING — Architecture § 3.1 groups "Work" and "Planning".
-- Every statement is twice-runnable: ORA-00955 (name in use) and the constraint/index
-- "already exists" codes are swallowed, anything else stops the script (SrdAi pattern).
--
-- Conventions of the whole SrdSupport script set
--   ids           NVARCHAR2(64) opaque strings (ULID/GUID text) issued by the service — never sequences,
--                 so a case id is the same in the ledger, the SignalR ticket and the papers volume
--   time          TIMESTAMP(6) UTC, column suffix _UTC
--   hashes        NVARCHAR2(64) lower-case hex SHA-256 over the SERDICA-JCS-1 canonical form (AI.Contracts)
--   json          CLOB with CHECK (… IS JSON); the schema of each payload is a contract under 10 Architecture/contracts
--   budget        minutes of predicted delivery time — the only budget dimension (D135)
--   states        closed CHECK lists; a module extends a vocabulary by adding a row to POLICY_VALUES, not a value here

DECLARE
    v_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_count FROM ALL_USERS WHERE USERNAME = 'SRD_SUPPORT';
    IF v_count = 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'SRD_SUPPORT user does not exist — created once by the DBA (D47) before any script runs');
    END IF;
END;
/

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
    -- ---------------------------------------------------------------- CASES
    ddl(q'~CREATE TABLE "SRD_SUPPORT"."CASES" (
        "ID"                        NVARCHAR2(64)   NOT NULL,
        "CASE_NO"                   NVARCHAR2(32)   NOT NULL,
        "MODULE"                    NVARCHAR2(16)   NOT NULL,
        "CASE_TYPE"                 NVARCHAR2(64)   NOT NULL,
        "CUSTOMER_CODE"             NVARCHAR2(32)   NOT NULL,
        "OBJECTIVE"                 NVARCHAR2(2000) NOT NULL,
        "STATE"                     NVARCHAR2(16)   NOT NULL,
        "PARK_REASON"               NVARCHAR2(32),
        "PARENT_CASE_ID"            NVARCHAR2(64),
        "ORIGIN_CHANNEL"            NVARCHAR2(32)   NOT NULL,
        "ORIGIN_KEY"                NVARCHAR2(256)  NOT NULL,
        "AUTHORITATIVE_CHANNEL"     NVARCHAR2(32)   NOT NULL,
        "DEFAULT_ENVIRONMENT"       NVARCHAR2(32)   NOT NULL,
        "WORKING_ENVIRONMENT"       NVARCHAR2(32),
        "SLA_CLASS"                 NVARCHAR2(16),
        "CONTRACT_SECTION"          NVARCHAR2(16),
        "SEVERITY"                  NVARCHAR2(16),
        "RESPONSE_DUE_UTC"          TIMESTAMP(6),
        "RESOLUTION_DUE_UTC"        TIMESTAMP(6),
        "CLOCK_PAUSED_FROM_UTC"     TIMESTAMP(6),
        "BUDGET_MINUTES"            NUMBER(10)      NOT NULL,
        "BUDGET_USED_MINUTES"       NUMBER(10,2)    DEFAULT 0 NOT NULL,
        "CONTROLLER_USER"           NVARCHAR2(128),
        "ROUTE_CONFIDENCE"          NVARCHAR2(8)    DEFAULT 'high' NOT NULL,
        "OPENED_BY"                 NVARCHAR2(128)  NOT NULL,
        "OPENED_ON_UTC"             TIMESTAMP(6)    NOT NULL,
        "RESOLVED_ON_UTC"           TIMESTAMP(6),
        "CLOSED_ON_UTC"             TIMESTAMP(6),
        "ROW_VERSION"               NUMBER(19)      DEFAULT 0 NOT NULL,
        CONSTRAINT "PK_CASES" PRIMARY KEY ("ID"),
        CONSTRAINT "UK_CASES_NO" UNIQUE ("CASE_NO"),
        CONSTRAINT "UK_CASES_ORIGIN" UNIQUE ("ORIGIN_CHANNEL", "ORIGIN_KEY"),
        CONSTRAINT "FK_CASES_PARENT" FOREIGN KEY ("PARENT_CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
        CONSTRAINT "CK_CASES_MODULE" CHECK ("MODULE" IN ('configuration','support','source')),
        CONSTRAINT "CK_CASES_STATE" CHECK ("STATE" IN ('opened','running','parked','at_gate','held','resolved','closed','cancelled')),
        CONSTRAINT "CK_CASES_PARK" CHECK ("PARK_REASON" IS NULL OR "PARK_REASON" IN ('question','gate','sub_case','connector_down','provider_down','budget','capability_gap')),
        CONSTRAINT "CK_CASES_ROUTE" CHECK ("ROUTE_CONFIDENCE" IN ('high','low')),
        CONSTRAINT "CK_CASES_BUDGET" CHECK ("BUDGET_MINUTES" > 0 AND "BUDGET_USED_MINUTES" >= 0)
    )~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_CASES_STATE" ON "SRD_SUPPORT"."CASES" ("STATE", "MODULE")~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_CASES_PARENT" ON "SRD_SUPPORT"."CASES" ("PARENT_CASE_ID")~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_CASES_CLOCKS" ON "SRD_SUPPORT"."CASES" ("RESPONSE_DUE_UTC", "RESOLUTION_DUE_UTC")~');
    ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."CASES" IS 'The durable work object (Agent Runtime § 11.1). PARENT_CASE_ID = an Hd sub-case inside the same case (Agents § 5). Clocks: two per case (Stage Classification § 3), paused while CLOCK_PAUSED_FROM_UTC is set. BUDGET_MINUTES = predicted delivery time (D135).'~');

    -- ---------------------------------------------------------------- SESSIONS
    ddl(q'~CREATE TABLE "SRD_SUPPORT"."SESSIONS" (
        "ID"                        NVARCHAR2(64)   NOT NULL,
        "CASE_ID"                   NVARCHAR2(64)   NOT NULL,
        "STATE"                     NVARCHAR2(16)   NOT NULL,
        "CONTROLLER_USER"           NVARCHAR2(128),
        "WORKER_ID"                 NVARCHAR2(128),
        "RUNTIME_VERSION"           NVARCHAR2(64)   NOT NULL,
        "FRAMEWORK_VERSION"         NVARCHAR2(128)  NOT NULL,
        "STARTED_ON_UTC"            TIMESTAMP(6)    NOT NULL,
        "STOPPED_ON_UTC"            TIMESTAMP(6),
        "LAST_CHECKPOINT_UTC"       TIMESTAMP(6),
        "ROW_VERSION"               NUMBER(19)      DEFAULT 0 NOT NULL,
        CONSTRAINT "PK_SESSIONS" PRIMARY KEY ("ID"),
        CONSTRAINT "FK_SESSIONS_CASE" FOREIGN KEY ("CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
        CONSTRAINT "CK_SESSIONS_STATE" CHECK ("STATE" IN ('live','stopped','resumed','deleted'))
    )~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_SESSIONS_CASE" ON "SRD_SUPPORT"."SESSIONS" ("CASE_ID", "STATE")~');
    ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."SESSIONS" IS 'The live execution of a case: stoppable, resumable, deletable; many viewers, one CONTROLLER_USER (CR-5). A worker is only a cache of the papers; the session row says which worker holds it.'~');

    -- ---------------------------------------------------------------- TASKS (the graph is data)
    ddl(q'~CREATE TABLE "SRD_SUPPORT"."TASKS" (
        "ID"                        NVARCHAR2(64)   NOT NULL,
        "CASE_ID"                   NVARCHAR2(64)   NOT NULL,
        "SESSION_ID"                NVARCHAR2(64),
        "PARENT_TASK_ID"            NVARCHAR2(64),
        "TASK_PATH"                 NVARCHAR2(1000) NOT NULL,
        "KIND"                      NVARCHAR2(16)   NOT NULL,
        "PROFILE_KEY"               NVARCHAR2(128)  NOT NULL,
        "PROFILE_VERSION"           NUMBER(10)      NOT NULL,
        "STAGE_ID"                  NVARCHAR2(16),
        "STATE"                     NVARCHAR2(16)   NOT NULL,
        "PARK_REASON"               NVARCHAR2(32),
        "TASK_TEXT"                 CLOB            NOT NULL,
        "DENIED_CONTEXT"            CLOB,
        "GRANT_SUBSET"              CLOB,
        "MEMORY_NODE"               NVARCHAR2(256)  NOT NULL,
        "BUDGET_MINUTES"            NUMBER(10)      NOT NULL,
        "BUDGET_USED_MINUTES"       NUMBER(10,2)    DEFAULT 0 NOT NULL,
        "DEADLINE_UTC"              TIMESTAMP(6),
        "PRIORITY"                  NUMBER(3),
        "RUNTIME_VERSION"           NVARCHAR2(64)   NOT NULL,
        "FENCE_EPOCH"               NUMBER(19)      DEFAULT 0 NOT NULL,
        "CREATED_ON_UTC"            TIMESTAMP(6)    NOT NULL,
        "ENDED_ON_UTC"              TIMESTAMP(6),
        "ROW_VERSION"               NUMBER(19)      DEFAULT 0 NOT NULL,
        CONSTRAINT "PK_TASKS" PRIMARY KEY ("ID"),
        CONSTRAINT "FK_TASKS_CASE" FOREIGN KEY ("CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
        CONSTRAINT "FK_TASKS_SESSION" FOREIGN KEY ("SESSION_ID") REFERENCES "SRD_SUPPORT"."SESSIONS" ("ID"),
        CONSTRAINT "FK_TASKS_PARENT" FOREIGN KEY ("PARENT_TASK_ID") REFERENCES "SRD_SUPPORT"."TASKS" ("ID"),
        CONSTRAINT "CK_TASKS_KIND" CHECK ("KIND" IN ('root','stage','sub_agent','consult','write_executor','connector','handover')),
        CONSTRAINT "CK_TASKS_STATE" CHECK ("STATE" IN ('open','running','parked','done','failed','cancelled')),
        CONSTRAINT "CK_TASKS_PARK" CHECK ("PARK_REASON" IS NULL OR "PARK_REASON" IN ('question','gate','sub_case','connector_down','provider_down','budget','capability_gap')),
        CONSTRAINT "CK_TASKS_DENIED_JSON" CHECK ("DENIED_CONTEXT" IS NULL OR "DENIED_CONTEXT" IS JSON),
        CONSTRAINT "CK_TASKS_GRANTS_JSON" CHECK ("GRANT_SUBSET" IS NULL OR "GRANT_SUBSET" IS JSON),
        CONSTRAINT "CK_TASKS_BUDGET" CHECK ("BUDGET_MINUTES" >= 0 AND "BUDGET_USED_MINUTES" >= 0)
    )~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_TASKS_CASE" ON "SRD_SUPPORT"."TASKS" ("CASE_ID", "STATE")~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_TASKS_PARENT" ON "SRD_SUPPORT"."TASKS" ("PARENT_TASK_ID")~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_TASKS_CONSULT_QUEUE" ON "SRD_SUPPORT"."TASKS" ("KIND", "STATE", "MEMORY_NODE", "PRIORITY", "CREATED_ON_UTC")~');
    ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."TASKS" IS 'One agent instance in one case (Agent Runtime § 1). The tree of PARENT_TASK_ID rows IS the module graph. KIND consult = a read-only domain consult (Agents § 5, D92); KIND handover = the Hd request that opened a sub-case. TASK_PATH = materialised ancestry for ledger correlation.'~');

    -- ---------------------------------------------------------------- TURN_CHECKPOINTS
    ddl(q'~CREATE TABLE "SRD_SUPPORT"."TURN_CHECKPOINTS" (
        "ID"                        NVARCHAR2(64)   NOT NULL,
        "TASK_ID"                   NVARCHAR2(64)   NOT NULL,
        "TURN_NO"                   NUMBER(10)      NOT NULL,
        "CONTEXT_HASH"              NVARCHAR2(64)   NOT NULL,
        "STATE_HASH"                NVARCHAR2(64)   NOT NULL,
        "COMPACTION_ARTEFACT_HASH"  NVARCHAR2(64),
        "PROFILE_VERSION"           NUMBER(10)      NOT NULL,
        "PROMPT_VERSION"            NUMBER(10)      NOT NULL,
        "ROUTE_VERSION"             NUMBER(10)      NOT NULL,
        "FRAMEWORK_VERSION"         NVARCHAR2(128)  NOT NULL,
        "DURATION_MS"               NUMBER(19)      NOT NULL,
        "MINUTES_CHARGED"           NUMBER(10,2)    NOT NULL,
        "LEDGER_FIRST_SEQ"          NUMBER(19),
        "LEDGER_LAST_SEQ"           NUMBER(19),
        "CREATED_ON_UTC"            TIMESTAMP(6)    NOT NULL,
        CONSTRAINT "PK_TURN_CHECKPOINTS" PRIMARY KEY ("ID"),
        CONSTRAINT "UK_TURN_CHECKPOINTS" UNIQUE ("TASK_ID", "TURN_NO"),
        CONSTRAINT "FK_TURN_TASK" FOREIGN KEY ("TASK_ID") REFERENCES "SRD_SUPPORT"."TASKS" ("ID"),
        CONSTRAINT "CK_TURN_NO" CHECK ("TURN_NO" > 0)
    )~');
    ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."TURN_CHECKPOINTS" IS 'One row per completed turn (Agent Runtime § 2 rule 4): the checkpoint a resumed worker continues from; the events of the turn are the ledger rows LEDGER_FIRST_SEQ..LEDGER_LAST_SEQ. FRAMEWORK_VERSION stamped per Agent Framework § Challenges.'~');

    -- ---------------------------------------------------------------- CASE_IMPACT_KEYS (PG-20)
    ddl(q'~CREATE TABLE "SRD_SUPPORT"."CASE_IMPACT_KEYS" (
        "CASE_ID"                   NVARCHAR2(64)   NOT NULL,
        "IMPACT_KEY"                NVARCHAR2(512)  NOT NULL,
        "MODE"                      NVARCHAR2(8)    NOT NULL,
        "DECLARED_ON_UTC"           TIMESTAMP(6)    NOT NULL,
        CONSTRAINT "PK_CASE_IMPACT_KEYS" PRIMARY KEY ("CASE_ID", "IMPACT_KEY", "MODE"),
        CONSTRAINT "FK_IMPACT_CASE" FOREIGN KEY ("CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
        CONSTRAINT "CK_IMPACT_MODE" CHECK ("MODE" IN ('read','write'))
    )~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_IMPACT_KEY" ON "SRD_SUPPORT"."CASE_IMPACT_KEYS" ("IMPACT_KEY", "MODE")~');
    ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."CASE_IMPACT_KEYS" IS 'environment|system|product-or-version|account|route keys a case intends to write (Agent Runtime § 11.5). Two open cases sharing a write key raise a coordination decision; reads never block.'~');

    -- ---------------------------------------------------------------- CASE_LINKS
    ddl(q'~CREATE TABLE "SRD_SUPPORT"."CASE_LINKS" (
        "ID"                        NVARCHAR2(64)   NOT NULL,
        "CASE_ID"                   NVARCHAR2(64)   NOT NULL,
        "LINK_KIND"                 NVARCHAR2(16)   NOT NULL,
        "EXTERNAL_SYSTEM"           NVARCHAR2(32)   NOT NULL,
        "EXTERNAL_REF"              NVARCHAR2(512)  NOT NULL,
        "IS_AUTHORITATIVE"          NUMBER(1)       DEFAULT 0 NOT NULL,
        "LINKED_ON_UTC"             TIMESTAMP(6)    NOT NULL,
        CONSTRAINT "PK_CASE_LINKS" PRIMARY KEY ("ID"),
        CONSTRAINT "UK_CASE_LINKS" UNIQUE ("CASE_ID", "EXTERNAL_SYSTEM", "EXTERNAL_REF"),
        CONSTRAINT "FK_LINKS_CASE" FOREIGN KEY ("CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
        CONSTRAINT "CK_LINKS_KIND" CHECK ("LINK_KIND" IN ('ticket','mail_thread','hdesk_ticket','merge_request','issue','pipeline','duplicate_of','mirror'))
    )~');
    ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."CASE_LINKS" IS 'The ticket system stays the system of record (N-11); the case links to it. Exactly one link per case carries IS_AUTHORITATIVE = 1 — the channel the customer speaks on; mirrors are projections (Stage Classification § 4).'~');

    -- ---------------------------------------------------------------- ARRIVALS (the Inbox, before a case exists)
    ddl(q'~CREATE TABLE "SRD_SUPPORT"."ARRIVALS" (
        "ID"                        NVARCHAR2(64)   NOT NULL,
        "CHANNEL"                   NVARCHAR2(32)   NOT NULL,
        "ORIGIN_KEY"                NVARCHAR2(256)  NOT NULL,
        "CUSTOMER_CODE"             NVARCHAR2(32),
        "REPORTER_HANDLE"           NVARCHAR2(64),
        "SUBJECT"                   NVARCHAR2(1000),
        "BODY_HASH"                 NVARCHAR2(64)   NOT NULL,
        "ARTEFACT_REF"              NVARCHAR2(512)  NOT NULL,
        "PROPOSED_MODULE"           NVARCHAR2(16),
        "PROPOSED_CASE_TYPE"        NVARCHAR2(64),
        "ROUTE_CONFIDENCE"          NVARCHAR2(8),
        "DUPLICATE_OF_CASE_ID"      NVARCHAR2(64),
        "INITIATION_OUTCOME"        NVARCHAR2(16),
        "STATE"                     NVARCHAR2(24)   NOT NULL,
        "CASE_ID"                   NVARCHAR2(64),
        "RECEIVED_ON_UTC"           TIMESTAMP(6)    NOT NULL,
        "DECIDED_ON_UTC"            TIMESTAMP(6),
        "DECIDED_BY"                NVARCHAR2(128),
        CONSTRAINT "PK_ARRIVALS" PRIMARY KEY ("ID"),
        CONSTRAINT "UK_ARRIVALS_ORIGIN" UNIQUE ("CHANNEL", "ORIGIN_KEY"),
        CONSTRAINT "FK_ARRIVALS_CASE" FOREIGN KEY ("CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
        CONSTRAINT "FK_ARRIVALS_DUP" FOREIGN KEY ("DUPLICATE_OF_CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
        CONSTRAINT "CK_ARRIVALS_STATE" CHECK ("STATE" IN ('new','opened','merged','dismissed','awaiting_authorisation','refused')),
        CONSTRAINT "CK_ARRIVALS_INIT" CHECK ("INITIATION_OUTCOME" IS NULL OR "INITIATION_OUTCOME" IN ('granted','may_request','not_granted')),
        CONSTRAINT "CK_ARRIVALS_MODULE" CHECK ("PROPOSED_MODULE" IS NULL OR "PROPOSED_MODULE" IN ('configuration','support','source'))
    )~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_ARRIVALS_STATE" ON "SRD_SUPPORT"."ARRIVALS" ("STATE", "RECEIVED_ON_UTC")~');
    ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."ARRIVALS" IS 'A normalised arrival (contract: drafts/normalised-arrival) as platform.intake re-briefs it (Agents § 5c). Dedup checks (CHANNEL, ORIGIN_KEY) first. The body itself is a paper; only its hash is here. REPORTER_HANDLE is a handle, never the identifier (Trust and Data § 4).'~');

    -- ---------------------------------------------------------------- PLANS · PLAN_REVISIONS · PLAN_STEPS
    ddl(q'~CREATE TABLE "SRD_SUPPORT"."PLANS" (
        "ID"                        NVARCHAR2(64)   NOT NULL,
        "CASE_ID"                   NVARCHAR2(64)   NOT NULL,
        "TASK_ID"                   NVARCHAR2(64)   NOT NULL,
        "CURRENT_REVISION_NO"       NUMBER(10)      DEFAULT 0 NOT NULL,
        "STATE"                     NVARCHAR2(16)   NOT NULL,
        "CREATED_ON_UTC"            TIMESTAMP(6)    NOT NULL,
        "ROW_VERSION"               NUMBER(19)      DEFAULT 0 NOT NULL,
        CONSTRAINT "PK_PLANS" PRIMARY KEY ("ID"),
        CONSTRAINT "FK_PLANS_CASE" FOREIGN KEY ("CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
        CONSTRAINT "FK_PLANS_TASK" FOREIGN KEY ("TASK_ID") REFERENCES "SRD_SUPPORT"."TASKS" ("ID"),
        CONSTRAINT "CK_PLANS_STATE" CHECK ("STATE" IN ('draft','confirmed','superseded','reconciled'))
    )~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_PLANS_CASE" ON "SRD_SUPPORT"."PLANS" ("CASE_ID")~');

    ddl(q'~CREATE TABLE "SRD_SUPPORT"."PLAN_REVISIONS" (
        "ID"                        NVARCHAR2(64)   NOT NULL,
        "PLAN_ID"                   NVARCHAR2(64)   NOT NULL,
        "REVISION_NO"               NUMBER(10)      NOT NULL,
        "ARTEFACT_HASH"             NVARCHAR2(64)   NOT NULL,
        "CONFIRMED_BY_KIND"         NVARCHAR2(16),
        "CONFIRMED_BY_REF"          NVARCHAR2(64),
        "REASON"                    NVARCHAR2(1000),
        "CREATED_ON_UTC"            TIMESTAMP(6)    NOT NULL,
        CONSTRAINT "PK_PLAN_REVISIONS" PRIMARY KEY ("ID"),
        CONSTRAINT "UK_PLAN_REVISIONS" UNIQUE ("PLAN_ID", "REVISION_NO"),
        CONSTRAINT "FK_PLANREV_PLAN" FOREIGN KEY ("PLAN_ID") REFERENCES "SRD_SUPPORT"."PLANS" ("ID"),
        CONSTRAINT "CK_PLANREV_NO" CHECK ("REVISION_NO" > 0),
        CONSTRAINT "CK_PLANREV_BY" CHECK ("CONFIRMED_BY_KIND" IS NULL OR "CONFIRMED_BY_KIND" IN ('parent_task','gate_decision'))
    )~');
    ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."PLAN_REVISIONS" IS 'Immutable. A child plan is confirmed by its immediate parent; only the root asks the human (Agent Runtime § 11.3). Parent confirmation never substitutes for a gate (CR-2).'~');

    ddl(q'~CREATE TABLE "SRD_SUPPORT"."PLAN_STEPS" (
        "ID"                        NVARCHAR2(64)   NOT NULL,
        "PLAN_REVISION_ID"          NVARCHAR2(64)   NOT NULL,
        "STEP_ID"                   NVARCHAR2(64)   NOT NULL,
        "SEQ_NO"                    NUMBER(10)      NOT NULL,
        "DESCRIPTION"               NVARCHAR2(2000) NOT NULL,
        "ASSERTION"                 NVARCHAR2(1000) NOT NULL,
        "EFFECT_CLASS"              NVARCHAR2(16)   NOT NULL,
        "WRITE_CLASS"               NVARCHAR2(2),
        "TARGET_ENVIRONMENT"        NVARCHAR2(32),
        "TARGET_SYSTEM"             NVARCHAR2(32),
        "TARGET_OBJECT"             NVARCHAR2(256),
        "SHAPE_ID"                  NVARCHAR2(64),
        "STATUS"                    NVARCHAR2(16)   DEFAULT 'planned' NOT NULL,
        CONSTRAINT "PK_PLAN_STEPS" PRIMARY KEY ("ID"),
        CONSTRAINT "UK_PLAN_STEPS" UNIQUE ("PLAN_REVISION_ID", "STEP_ID"),
        CONSTRAINT "FK_STEPS_REV" FOREIGN KEY ("PLAN_REVISION_ID") REFERENCES "SRD_SUPPORT"."PLAN_REVISIONS" ("ID"),
        CONSTRAINT "CK_STEPS_EFFECT" CHECK ("EFFECT_CLASS" IN ('none','transactional','compensable','irreversible','ddl')),
        CONSTRAINT "CK_STEPS_WCLASS" CHECK ("WRITE_CLASS" IS NULL OR "WRITE_CLASS" IN ('W1','W2','W3','W4','W5','W6','W7')),
        CONSTRAINT "CK_STEPS_STATUS" CHECK ("STATUS" IN ('planned','done','skipped','failed','reverted'))
    )~');
    ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."PLAN_STEPS" IS 'STEP_ID is stable across revisions so BUILD_STATE and WRITE_LOG can be reconciled against the plan at close (Agent Runtime § 11.3 rule 3). Every step declares its effect class (CR-9) and, for a write, its class W1–W7 (Gating § 3).'~');
END;
/
