SET DEFINE OFF
WHENEVER SQLERROR EXIT SQL.SQLCODE

-- 010: proposed runtime storage contract; never executed by the documentation build.
-- Agent Runtime sections 2, 4, 5 and 7; Model Execution; Modules.
-- Existing installations with hash-only checkpoints need an explicit state migration:
-- this upgrade refuses to manufacture missing checkpoint bytes.
DECLARE
    PROCEDURE ddl(p_sql IN VARCHAR2) IS
    BEGIN
        EXECUTE IMMEDIATE p_sql;
    EXCEPTION
        WHEN OTHERS THEN
            IF SQLCODE NOT IN (-955, -1408, -1430, -1442, -1451, -2260, -2261, -2264, -2275) THEN RAISE; END IF;
    END;
    PROCEDURE drop_constraint(p_table IN VARCHAR2, p_name IN VARCHAR2) IS
    BEGIN
        EXECUTE IMMEDIATE 'ALTER TABLE "SRD_SUPPORT"."' || p_table || '" DROP CONSTRAINT "' || p_name || '"';
    EXCEPTION WHEN OTHERS THEN IF SQLCODE <> -2443 THEN RAISE; END IF;
    END;
BEGIN
    ddl('ALTER TABLE "SRD_SUPPORT"."TASKS" ADD ("LEASE_OWNER" NVARCHAR2(128), "LEASE_UNTIL_UTC" TIMESTAMP(6))');
    ddl('ALTER TABLE "SRD_SUPPORT"."TURN_CHECKPOINTS" ADD ("STATE_PAPER_ID" NVARCHAR2(64))');
    ddl('ALTER TABLE "SRD_SUPPORT"."TURN_CHECKPOINTS" MODIFY ("STATE_PAPER_ID" NOT NULL)');
    ddl('ALTER TABLE "SRD_SUPPORT"."TURN_CHECKPOINTS" ADD CONSTRAINT "FK_TURN_STATE_PAPER" FOREIGN KEY ("STATE_PAPER_ID") REFERENCES "SRD_SUPPORT"."PAPERS" ("ID")');

    ddl(q'~CREATE TABLE "SRD_SUPPORT"."RUNTIME_CALLS" (
        "ID"                    NVARCHAR2(64) NOT NULL,
        "CASE_ID"               NVARCHAR2(64) NOT NULL,
        "TASK_ID"               NVARCHAR2(64) NOT NULL,
        "TURN_NO"               NUMBER(10) NOT NULL,
        "CALL_ORDINAL"          NUMBER(10) NOT NULL,
        "KIND"                  NVARCHAR2(8) NOT NULL,
        "OPERATION_KEY"         NVARCHAR2(256) NOT NULL,
        "OPERATION_VERSION"     NVARCHAR2(128) NOT NULL,
        "IDEMPOTENCY_KEY"       NVARCHAR2(256) NOT NULL,
        "REQUEST_HASH"          NVARCHAR2(64) NOT NULL,
        "REQUEST_PAPER_ID"      NVARCHAR2(64) NOT NULL,
        "RESULT_PAPER_ID"       NVARCHAR2(64),
        "EFFECT_CLASS"          NVARCHAR2(16) NOT NULL,
        "STATE"                 NVARCHAR2(16) NOT NULL,
        "LANDED_STATE"          NVARCHAR2(24),
        "GRANT_ID"              NVARCHAR2(64),
        "FENCE_EPOCH"           NUMBER(19) NOT NULL,
        "ATTEMPT_NO"            NUMBER(10) DEFAULT 0 NOT NULL,
        "CREATED_ON_UTC"        TIMESTAMP(6) NOT NULL,
        "DISPATCHED_ON_UTC"     TIMESTAMP(6),
        "ENDED_ON_UTC"          TIMESTAMP(6),
        "ROW_VERSION"           NUMBER(19) DEFAULT 0 NOT NULL,
        CONSTRAINT "PK_RUNTIME_CALLS" PRIMARY KEY ("ID"),
        CONSTRAINT "UK_RUNTIME_CALL_SLOT" UNIQUE ("TASK_ID", "TURN_NO", "CALL_ORDINAL"),
        CONSTRAINT "UK_RUNTIME_CALL_KEY" UNIQUE ("IDEMPOTENCY_KEY"),
        CONSTRAINT "FK_RCALL_CASE" FOREIGN KEY ("CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
        CONSTRAINT "FK_RCALL_TASK" FOREIGN KEY ("TASK_ID") REFERENCES "SRD_SUPPORT"."TASKS" ("ID"),
        CONSTRAINT "FK_RCALL_REQUEST" FOREIGN KEY ("REQUEST_PAPER_ID") REFERENCES "SRD_SUPPORT"."PAPERS" ("ID"),
        CONSTRAINT "FK_RCALL_RESULT" FOREIGN KEY ("RESULT_PAPER_ID") REFERENCES "SRD_SUPPORT"."PAPERS" ("ID"),
        CONSTRAINT "FK_RCALL_GRANT" FOREIGN KEY ("GRANT_ID") REFERENCES "SRD_SUPPORT"."GRANTS" ("ID"),
        CONSTRAINT "CK_RCALL_GRANTED" CHECK ("EFFECT_CLASS" IN ('none','read') OR "STATE" IN ('prepared','refused') OR "GRANT_ID" IS NOT NULL),
        CONSTRAINT "CK_RCALL_KIND" CHECK ("KIND" IN ('model','tool')),
        CONSTRAINT "CK_RCALL_EFFECT" CHECK ("EFFECT_CLASS" IN ('none','read','transactional','compensable','irreversible','ddl')),
        CONSTRAINT "CK_RCALL_STATE" CHECK ("STATE" IN ('prepared','dispatched','succeeded','refused','failed','unknown')),
        CONSTRAINT "CK_RCALL_LANDED" CHECK ("LANDED_STATE" IS NULL OR "LANDED_STATE" IN ('applied','not_applied','partly_applied','unknown')),
        CONSTRAINT "CK_RCALL_SLOT" CHECK ("TURN_NO" > 0 AND "CALL_ORDINAL" >= 0 AND "ATTEMPT_NO" >= 0),
        CONSTRAINT "CK_RCALL_SUCCESS" CHECK ("STATE" <> 'succeeded' OR ("RESULT_PAPER_ID" IS NOT NULL AND "ENDED_ON_UTC" IS NOT NULL)),
        CONSTRAINT "CK_RCALL_DISPATCH" CHECK ("STATE" NOT IN ('dispatched','unknown','succeeded') OR "DISPATCHED_ON_UTC" IS NOT NULL)
    )~');
    ddl('CREATE INDEX "SRD_SUPPORT"."IX_RCALL_RECOVER" ON "SRD_SUPPORT"."RUNTIME_CALLS" ("TASK_ID", "STATE")');
    ddl('ALTER TABLE "SRD_SUPPORT"."GRANTS" ADD ("CONSUMED_BY_CALL_ID" NVARCHAR2(64))');
    ddl('ALTER TABLE "SRD_SUPPORT"."GRANTS" ADD CONSTRAINT "FK_GRANT_CONSUMED_CALL" FOREIGN KEY ("CONSUMED_BY_CALL_ID") REFERENCES "SRD_SUPPORT"."RUNTIME_CALLS" ("ID")');

    ddl(q'~CREATE TABLE "SRD_SUPPORT"."INBOX_MESSAGES" (
        "MESSAGE_ID"          NVARCHAR2(64) NOT NULL,
        "PAYLOAD_HASH"        NVARCHAR2(64) NOT NULL,
        "RECIPIENT_TASK_ID"   NVARCHAR2(64),
        "CONSUMER_KEY"        NVARCHAR2(256) NOT NULL,
        "RESPONSE_JSON"       CLOB,
        "DISPOSITION"         NVARCHAR2(16) NOT NULL,
        "RESULT_PAPER_ID"     NVARCHAR2(64),
        "APPLIED_ON_UTC"      TIMESTAMP(6) NOT NULL,
        CONSTRAINT "PK_INBOX_MESSAGES" PRIMARY KEY ("MESSAGE_ID"),
        CONSTRAINT "FK_INBOX_TASK" FOREIGN KEY ("RECIPIENT_TASK_ID") REFERENCES "SRD_SUPPORT"."TASKS" ("ID"),
        CONSTRAINT "FK_INBOX_RESULT" FOREIGN KEY ("RESULT_PAPER_ID") REFERENCES "SRD_SUPPORT"."PAPERS" ("ID"),
        CONSTRAINT "CK_INBOX_RESPONSE_JSON" CHECK ("RESPONSE_JSON" IS NULL OR "RESPONSE_JSON" IS JSON),
        CONSTRAINT "CK_INBOX_DISPOSITION" CHECK ("DISPOSITION" IN ('applied','refused'))
    )~');

    ddl(q'~CREATE TABLE "SRD_SUPPORT"."IMPACT_LOCKS" (
        "IMPACT_KEY"          NVARCHAR2(512) NOT NULL,
        "CALL_ID"             NVARCHAR2(64) NOT NULL,
        "CASE_ID"             NVARCHAR2(64) NOT NULL,
        "FENCE_EPOCH"         NUMBER(19) NOT NULL,
        "ACQUIRED_ON_UTC"     TIMESTAMP(6) NOT NULL,
        CONSTRAINT "PK_IMPACT_LOCKS" PRIMARY KEY ("IMPACT_KEY"),
        CONSTRAINT "FK_ILOCK_CALL" FOREIGN KEY ("CALL_ID") REFERENCES "SRD_SUPPORT"."RUNTIME_CALLS" ("ID"),
        CONSTRAINT "FK_ILOCK_CASE" FOREIGN KEY ("CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID")
    )~');

    drop_constraint('CASES', 'CK_CASES_PARK');
    ddl(q'~ALTER TABLE "SRD_SUPPORT"."CASES" ADD CONSTRAINT "CK_CASES_PARK_V2" CHECK ("PARK_REASON" IS NULL OR "PARK_REASON" IN ('question','gate','sub_case','connector_down','provider_down','budget','capability_gap','external_job','effect_unknown'))~');
    drop_constraint('TASKS', 'CK_TASKS_PARK');
    ddl(q'~ALTER TABLE "SRD_SUPPORT"."TASKS" ADD CONSTRAINT "CK_TASKS_PARK_V2" CHECK ("PARK_REASON" IS NULL OR "PARK_REASON" IN ('question','gate','sub_case','connector_down','provider_down','budget','capability_gap','external_job','effect_unknown'))~');

    -- A mechanical profile has a registered code handler, not a fictitious LLM route.
    ddl(q'~ALTER TABLE "SRD_SUPPORT"."PROFILES" ADD ("EXECUTION_KIND" NVARCHAR2(16) DEFAULT 'llm' NOT NULL, "HANDLER_KEY" NVARCHAR2(128))~');
    ddl('ALTER TABLE "SRD_SUPPORT"."PROFILES" MODIFY ("PROMPT_ID" NULL, "MODEL_ROUTE_ID" NULL)');
    ddl(q'~ALTER TABLE "SRD_SUPPORT"."PROFILES" ADD CONSTRAINT "CK_PROFILE_EXEC_KIND" CHECK (
      ("EXECUTION_KIND" = 'llm' AND "PROMPT_ID" IS NOT NULL AND "MODEL_ROUTE_ID" IS NOT NULL AND "HANDLER_KEY" IS NULL)
      OR ("EXECUTION_KIND" = 'deterministic' AND "PROMPT_ID" IS NULL AND "MODEL_ROUTE_ID" IS NULL AND "HANDLER_KEY" IS NOT NULL))~');

    ddl('ALTER TABLE "SRD_SUPPORT"."CONNECTOR_SCOPES" ADD ("EXECUTION_FORM" NVARCHAR2(1))');
    ddl(q'~ALTER TABLE "SRD_SUPPORT"."CONNECTOR_SCOPES" ADD CONSTRAINT "CK_SCOPE_EXEC_FORM" CHECK ("EXECUTION_FORM" IS NULL OR "EXECUTION_FORM" IN ('A','B','E'))~');

    drop_constraint('CONNECTOR_SCOPES', 'CK_SCOPES_CONNECTOR');
    ddl(q'~ALTER TABLE "SRD_SUPPORT"."CONNECTOR_SCOPES" ADD CONSTRAINT "CK_SCOPE_CONNECTOR_KEY" CHECK (TRIM("CONNECTOR") IS NOT NULL)~');

    -- Eligibility is representable for every class; no risk acceptance is seeded.
    drop_constraint('RISK_ACCEPTANCES', 'CK_RISK_WCLASS');
    ddl(q'~ALTER TABLE "SRD_SUPPORT"."RISK_ACCEPTANCES" ADD CONSTRAINT "CK_RISK_WCLASS_V2" CHECK ("WRITE_CLASS" IN ('W1','W2','W3','W4','W5','W6','W7'))~');
    ddl('ALTER TABLE "SRD_SUPPORT"."GATE_DECISIONS" ADD ("DECISION_POLICY_REF" NVARCHAR2(256))');
    drop_constraint('GATE_DECISIONS', 'CK_GDEC_POLICY_SHAPE');
    ddl(q'~ALTER TABLE "SRD_SUPPORT"."GATE_DECISIONS" ADD CONSTRAINT "CK_GDEC_POLICY_REF_V2" CHECK ("ACTOR_KIND" <> 'policy' OR "DECISION_POLICY_REF" IS NOT NULL)~');

    drop_constraint('AUDIT_LEDGER', 'CK_LEDGER_WRITE_COUNT');
    ddl(q'~ALTER TABLE "SRD_SUPPORT"."AUDIT_LEDGER" ADD CONSTRAINT "CK_LEDGER_WRITE_COUNT_V2" CHECK ("KIND" <> 'WRITE_EXECUTED' OR "ROW_COUNT" IS NOT NULL OR "MODE" IN ('send','ddl'))~');
    ddl('CREATE UNIQUE INDEX "SRD_SUPPORT"."UX_COST_PROVIDER_RECORD" ON "SRD_SUPPORT"."COST_LEDGER" ("LEDGER_RECORD_ID")');

    -- Missing provider measurements remain unknown, never fabricated as zero.
    ddl('ALTER TABLE "SRD_SUPPORT"."COST_LEDGER" MODIFY ("INPUT_TOKENS" NULL, "OUTPUT_TOKENS" NULL, "CACHED_TOKENS" NULL)');
    ddl(q'~ALTER TABLE "SRD_SUPPORT"."COST_LEDGER" ADD ("USAGE_STATUS" NVARCHAR2(16) DEFAULT 'measured' NOT NULL)~');
    ddl(q'~ALTER TABLE "SRD_SUPPORT"."COST_LEDGER" ADD CONSTRAINT "CK_COST_USAGE_STATUS" CHECK ("USAGE_STATUS" IN ('measured','partial','unavailable'))~');

    ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."RUNTIME_CALLS" IS 'Durable model/tool intent and outcome. Request identity is immutable; an uncertain effect is reconciled before retry. Result bytes are immutable PAPERS revisions. Attempt evidence is appended to AUDIT_LEDGER.'~');
    ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."INBOX_MESSAGES" IS 'Deduplication for messages and HTTP commands, scoped by consumer. Commit key/hash, response reference and state transition together; unequal replay bytes are refused.'~');
    ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."IMPACT_LOCKS" IS 'Current platform execution claims, acquired in sorted order. Not released merely because a worker lease expires; unknown target outcomes remain held. External authors are outside this lock.'~');
END;
/

CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."RUNTIME_CALLS_GOV_TRG"
BEFORE UPDATE OR DELETE ON "SRD_SUPPORT"."RUNTIME_CALLS"
FOR EACH ROW
BEGIN
    IF DELETING OR :NEW."ID" <> :OLD."ID" OR :NEW."TASK_ID" <> :OLD."TASK_ID"
       OR :NEW."CASE_ID" <> :OLD."CASE_ID" OR :NEW."TURN_NO" <> :OLD."TURN_NO"
       OR :NEW."CALL_ORDINAL" <> :OLD."CALL_ORDINAL" OR :NEW."KIND" <> :OLD."KIND"
       OR :NEW."REQUEST_HASH" <> :OLD."REQUEST_HASH" OR :NEW."REQUEST_PAPER_ID" <> :OLD."REQUEST_PAPER_ID"
       OR :NEW."IDEMPOTENCY_KEY" <> :OLD."IDEMPOTENCY_KEY" OR :NEW."OPERATION_KEY" <> :OLD."OPERATION_KEY"
       OR :NEW."OPERATION_VERSION" <> :OLD."OPERATION_VERSION" OR :NEW."EFFECT_CLASS" <> :OLD."EFFECT_CLASS" THEN
        RAISE_APPLICATION_ERROR(-20080, 'runtime call request identity is immutable');
    END IF;
    IF :OLD."STATE" IN ('succeeded','refused') THEN
        RAISE_APPLICATION_ERROR(-20081, 'terminal runtime call evidence is immutable; record a new corrective action');
    END IF;
END;
/

CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."INBOX_MESSAGES_IMM_TRG"
BEFORE UPDATE OR DELETE ON "SRD_SUPPORT"."INBOX_MESSAGES"
BEGIN
    RAISE_APPLICATION_ERROR(-20082, 'inbox message dispositions are immutable');
END;
/

CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."TURN_CHECKPOINTS_IMM_TRG"
BEFORE UPDATE OR DELETE ON "SRD_SUPPORT"."TURN_CHECKPOINTS"
BEGIN
    RAISE_APPLICATION_ERROR(-20083, 'checkpoint revisions are immutable');
END;
/

-- Replace the 008 profile guard to support deterministic profiles without model rows.
CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."PROFILES_GOV_TRG"
BEFORE INSERT OR UPDATE OR DELETE ON "SRD_SUPPORT"."PROFILES"
FOR EACH ROW
DECLARE
    v_passed INTEGER; v_approved INTEGER; v_green INTEGER;
    v_zone NVARCHAR2(16); v_dpa NUMBER(1);
BEGIN
    IF DELETING THEN
        RAISE_APPLICATION_ERROR(-20084, 'retire a profile version; do not delete it');
    END IF;
    IF INSERTING AND :NEW."STATE" <> 'draft' THEN
        RAISE_APPLICATION_ERROR(-20085, 'a profile begins as draft and publishes through governance');
    END IF;
    IF UPDATING AND :OLD."STATE" IN ('published','retired') AND (
       :NEW."CONTENT_HASH" <> :OLD."CONTENT_HASH"
       OR COALESCE(:NEW."PROMPT_ID", '-') <> COALESCE(:OLD."PROMPT_ID", '-')
       OR COALESCE(:NEW."MODEL_ROUTE_ID", '-') <> COALESCE(:OLD."MODEL_ROUTE_ID", '-')
       OR :NEW."EXECUTION_KIND" <> :OLD."EXECUTION_KIND"
       OR COALESCE(:NEW."HANDLER_KEY", '-') <> COALESCE(:OLD."HANDLER_KEY", '-')
       OR DBMS_LOB.COMPARE(:NEW."TOOLS", :OLD."TOOLS") <> 0
       OR DBMS_LOB.COMPARE(:NEW."DENIED_CONTEXT", :OLD."DENIED_CONTEXT") <> 0
       OR DBMS_LOB.COMPARE(:NEW."SKILLS", :OLD."SKILLS") <> 0
       OR COALESCE(:NEW."MEMORY_WRITE_NODE", '-') <> COALESCE(:OLD."MEMORY_WRITE_NODE", '-')) THEN
        RAISE_APPLICATION_ERROR(-20045, 'a published profile definition is immutable');
    END IF;
    IF UPDATING AND :NEW."STATE" = 'published' AND :OLD."STATE" <> 'published' THEN
        SELECT COUNT(*) INTO v_passed FROM "SRD_SUPPORT"."GOVERNANCE_EVENTS"
         WHERE "SUBJECT_TYPE" = 'profile' AND "SUBJECT_KEY" = :NEW."PROFILE_KEY"
           AND "SUBJECT_VERSION" = :NEW."VERSION" AND "EVENT" = 'evaluated' AND "RESULT" = 'PASSED';
        SELECT COUNT(*) INTO v_approved FROM "SRD_SUPPORT"."GOVERNANCE_EVENTS"
         WHERE "SUBJECT_TYPE" = 'profile' AND "SUBJECT_KEY" = :NEW."PROFILE_KEY"
           AND "SUBJECT_VERSION" = :NEW."VERSION" AND "EVENT" = 'approved';
        SELECT COUNT(*) INTO v_green FROM "SRD_SUPPORT"."EVAL_SETS"
         WHERE "ID" = :NEW."EVAL_SET_ID" AND "LAST_RESULT" = 'green';
        IF v_passed = 0 OR v_approved = 0 OR v_green = 0 THEN
            RAISE_APPLICATION_ERROR(-20046, 'publication requires passed evaluation, approval and a green eval set');
        END IF;
        IF :NEW."EXECUTION_KIND" = 'llm' THEN
            SELECT "DATA_ZONE", "DPA_COVERED" INTO v_zone, v_dpa
              FROM "SRD_SUPPORT"."MODEL_ROUTES" WHERE "ID" = :NEW."MODEL_ROUTE_ID";
            IF :NEW."MODULE" = 'support' AND (v_zone <> 'EU' OR v_dpa <> 1) THEN
                RAISE_APPLICATION_ERROR(-20048, 'a Support model profile requires an EU DPA-covered route');
            END IF;
        END IF;
        :NEW."PUBLISHED_ON_UTC" := COALESCE(:NEW."PUBLISHED_ON_UTC", SYS_EXTRACT_UTC(SYSTIMESTAMP));
    END IF;
END;
/

CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."RISK_ACCEPTANCES_IMM_TRG"
BEFORE UPDATE OR DELETE ON "SRD_SUPPORT"."RISK_ACCEPTANCES"
FOR EACH ROW
BEGIN
    IF DELETING OR :OLD."WITHDRAWN_ON_UTC" IS NOT NULL OR :NEW."WITHDRAWN_ON_UTC" IS NULL OR :NEW."WITHDRAWN_BY" IS NULL
       OR (:NEW."ID" <> :OLD."ID" OR (:NEW."ID" IS NULL AND :OLD."ID" IS NOT NULL) OR (:NEW."ID" IS NOT NULL AND :OLD."ID" IS NULL))
       OR (:NEW."CUSTOMER_CODE" <> :OLD."CUSTOMER_CODE" OR (:NEW."CUSTOMER_CODE" IS NULL AND :OLD."CUSTOMER_CODE" IS NOT NULL) OR (:NEW."CUSTOMER_CODE" IS NOT NULL AND :OLD."CUSTOMER_CODE" IS NULL))
       OR (:NEW."MODULE" <> :OLD."MODULE" OR (:NEW."MODULE" IS NULL AND :OLD."MODULE" IS NOT NULL) OR (:NEW."MODULE" IS NOT NULL AND :OLD."MODULE" IS NULL))
       OR (:NEW."CASE_TYPE" <> :OLD."CASE_TYPE" OR (:NEW."CASE_TYPE" IS NULL AND :OLD."CASE_TYPE" IS NOT NULL) OR (:NEW."CASE_TYPE" IS NOT NULL AND :OLD."CASE_TYPE" IS NULL))
       OR (:NEW."GATE_KIND" <> :OLD."GATE_KIND" OR (:NEW."GATE_KIND" IS NULL AND :OLD."GATE_KIND" IS NOT NULL) OR (:NEW."GATE_KIND" IS NOT NULL AND :OLD."GATE_KIND" IS NULL))
       OR (:NEW."TARGET_CLASS" <> :OLD."TARGET_CLASS" OR (:NEW."TARGET_CLASS" IS NULL AND :OLD."TARGET_CLASS" IS NOT NULL) OR (:NEW."TARGET_CLASS" IS NOT NULL AND :OLD."TARGET_CLASS" IS NULL))
       OR (:NEW."WRITE_CLASS" <> :OLD."WRITE_CLASS" OR (:NEW."WRITE_CLASS" IS NULL AND :OLD."WRITE_CLASS" IS NOT NULL) OR (:NEW."WRITE_CLASS" IS NOT NULL AND :OLD."WRITE_CLASS" IS NULL))
       OR (:NEW."ACCEPTED_BY" <> :OLD."ACCEPTED_BY" OR (:NEW."ACCEPTED_BY" IS NULL AND :OLD."ACCEPTED_BY" IS NOT NULL) OR (:NEW."ACCEPTED_BY" IS NOT NULL AND :OLD."ACCEPTED_BY" IS NULL))
       OR (:NEW."ACCEPTED_ROLE" <> :OLD."ACCEPTED_ROLE" OR (:NEW."ACCEPTED_ROLE" IS NULL AND :OLD."ACCEPTED_ROLE" IS NOT NULL) OR (:NEW."ACCEPTED_ROLE" IS NOT NULL AND :OLD."ACCEPTED_ROLE" IS NULL))
       OR (:NEW."DECISION_REF" <> :OLD."DECISION_REF" OR (:NEW."DECISION_REF" IS NULL AND :OLD."DECISION_REF" IS NOT NULL) OR (:NEW."DECISION_REF" IS NOT NULL AND :OLD."DECISION_REF" IS NULL))
       OR (:NEW."ACCEPTED_ON_UTC" <> :OLD."ACCEPTED_ON_UTC" OR (:NEW."ACCEPTED_ON_UTC" IS NULL AND :OLD."ACCEPTED_ON_UTC" IS NOT NULL) OR (:NEW."ACCEPTED_ON_UTC" IS NOT NULL AND :OLD."ACCEPTED_ON_UTC" IS NULL))
       OR (:NEW."LEDGER_RECORD_ID" <> :OLD."LEDGER_RECORD_ID" OR (:NEW."LEDGER_RECORD_ID" IS NULL AND :OLD."LEDGER_RECORD_ID" IS NOT NULL) OR (:NEW."LEDGER_RECORD_ID" IS NOT NULL AND :OLD."LEDGER_RECORD_ID" IS NULL)) THEN
        RAISE_APPLICATION_ERROR(-20022, 'RISK_ACCEPTANCES may only be withdrawn/revoked once; its authority scope is immutable');
    END IF;
END;
/

CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."ROLE_ASSIGNMENTS_IMM_TRG"
BEFORE UPDATE OR DELETE ON "SRD_SUPPORT"."ROLE_ASSIGNMENTS"
FOR EACH ROW
BEGIN
    IF DELETING OR :OLD."REVOKED_ON_UTC" IS NOT NULL OR :NEW."REVOKED_ON_UTC" IS NULL OR :NEW."REVOKED_BY" IS NULL
       OR (:NEW."ID" <> :OLD."ID" OR (:NEW."ID" IS NULL AND :OLD."ID" IS NOT NULL) OR (:NEW."ID" IS NOT NULL AND :OLD."ID" IS NULL))
       OR (:NEW."SUBJECT_ID" <> :OLD."SUBJECT_ID" OR (:NEW."SUBJECT_ID" IS NULL AND :OLD."SUBJECT_ID" IS NOT NULL) OR (:NEW."SUBJECT_ID" IS NOT NULL AND :OLD."SUBJECT_ID" IS NULL))
       OR (:NEW."SUBJECT_KIND" <> :OLD."SUBJECT_KIND" OR (:NEW."SUBJECT_KIND" IS NULL AND :OLD."SUBJECT_KIND" IS NOT NULL) OR (:NEW."SUBJECT_KIND" IS NOT NULL AND :OLD."SUBJECT_KIND" IS NULL))
       OR (:NEW."CUSTOMER_CODE" <> :OLD."CUSTOMER_CODE" OR (:NEW."CUSTOMER_CODE" IS NULL AND :OLD."CUSTOMER_CODE" IS NOT NULL) OR (:NEW."CUSTOMER_CODE" IS NOT NULL AND :OLD."CUSTOMER_CODE" IS NULL))
       OR (:NEW."ROLE" <> :OLD."ROLE" OR (:NEW."ROLE" IS NULL AND :OLD."ROLE" IS NOT NULL) OR (:NEW."ROLE" IS NOT NULL AND :OLD."ROLE" IS NULL))
       OR (:NEW."SCOPE_MODULE" <> :OLD."SCOPE_MODULE" OR (:NEW."SCOPE_MODULE" IS NULL AND :OLD."SCOPE_MODULE" IS NOT NULL) OR (:NEW."SCOPE_MODULE" IS NOT NULL AND :OLD."SCOPE_MODULE" IS NULL))
       OR (:NEW."SCOPE_STAGE" <> :OLD."SCOPE_STAGE" OR (:NEW."SCOPE_STAGE" IS NULL AND :OLD."SCOPE_STAGE" IS NOT NULL) OR (:NEW."SCOPE_STAGE" IS NOT NULL AND :OLD."SCOPE_STAGE" IS NULL))
       OR (:NEW."SCOPE_TARGET_CLASS" <> :OLD."SCOPE_TARGET_CLASS" OR (:NEW."SCOPE_TARGET_CLASS" IS NULL AND :OLD."SCOPE_TARGET_CLASS" IS NOT NULL) OR (:NEW."SCOPE_TARGET_CLASS" IS NOT NULL AND :OLD."SCOPE_TARGET_CLASS" IS NULL))
       OR (:NEW."SCOPE_SYSTEM" <> :OLD."SCOPE_SYSTEM" OR (:NEW."SCOPE_SYSTEM" IS NULL AND :OLD."SCOPE_SYSTEM" IS NOT NULL) OR (:NEW."SCOPE_SYSTEM" IS NOT NULL AND :OLD."SCOPE_SYSTEM" IS NULL))
       OR (:NEW."SOURCE" <> :OLD."SOURCE" OR (:NEW."SOURCE" IS NULL AND :OLD."SOURCE" IS NOT NULL) OR (:NEW."SOURCE" IS NOT NULL AND :OLD."SOURCE" IS NULL))
       OR (:NEW."SOURCE_REF" <> :OLD."SOURCE_REF" OR (:NEW."SOURCE_REF" IS NULL AND :OLD."SOURCE_REF" IS NOT NULL) OR (:NEW."SOURCE_REF" IS NOT NULL AND :OLD."SOURCE_REF" IS NULL))
       OR (:NEW."GRANTED_BY" <> :OLD."GRANTED_BY" OR (:NEW."GRANTED_BY" IS NULL AND :OLD."GRANTED_BY" IS NOT NULL) OR (:NEW."GRANTED_BY" IS NOT NULL AND :OLD."GRANTED_BY" IS NULL))
       OR (:NEW."GRANTED_ON_UTC" <> :OLD."GRANTED_ON_UTC" OR (:NEW."GRANTED_ON_UTC" IS NULL AND :OLD."GRANTED_ON_UTC" IS NOT NULL) OR (:NEW."GRANTED_ON_UTC" IS NOT NULL AND :OLD."GRANTED_ON_UTC" IS NULL))
       OR (:NEW."LEDGER_RECORD_ID" <> :OLD."LEDGER_RECORD_ID" OR (:NEW."LEDGER_RECORD_ID" IS NULL AND :OLD."LEDGER_RECORD_ID" IS NOT NULL) OR (:NEW."LEDGER_RECORD_ID" IS NOT NULL AND :OLD."LEDGER_RECORD_ID" IS NULL)) THEN
        RAISE_APPLICATION_ERROR(-20023, 'ROLE_ASSIGNMENTS may only be withdrawn/revoked once; its authority scope is immutable');
    END IF;
END;
/

-- Preserve scope, parameter and expiry immutability on re-approval as well.
CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."WRITE_SHAPES_GOV_TRG"
BEFORE UPDATE ON "SRD_SUPPORT"."WRITE_SHAPES"
FOR EACH ROW
BEGIN
    IF :OLD."STATE" IN ('approved','suspended','retired') AND (
         :NEW."TEMPLATE_HASH" <> :OLD."TEMPLATE_HASH" OR DBMS_LOB.COMPARE(:NEW."TEMPLATE", :OLD."TEMPLATE") <> 0
         OR DBMS_LOB.COMPARE(:NEW."TEARDOWN_TEMPLATE", :OLD."TEARDOWN_TEMPLATE") <> 0 OR DBMS_LOB.COMPARE(:NEW."ASSERTIONS", :OLD."ASSERTIONS") <> 0
         OR DBMS_LOB.COMPARE(:NEW."CASE_TYPES", :OLD."CASE_TYPES") <> 0 OR :NEW."EXPECTED_COUNTS_EXPR" <> :OLD."EXPECTED_COUNTS_EXPR"
         OR :NEW."TARGET_CLASS" <> :OLD."TARGET_CLASS" OR :NEW."EFFECT_CLASS" <> :OLD."EFFECT_CLASS" OR :NEW."WRITE_CLASS" <> :OLD."WRITE_CLASS"
         OR :NEW."OPERATION_ID" <> :OLD."OPERATION_ID"
         OR DBMS_LOB.COMPARE(:NEW."PARAMETERS", :OLD."PARAMETERS") <> 0
         OR :NEW."SHAPE_KEY" <> :OLD."SHAPE_KEY" OR :NEW."VERSION" <> :OLD."VERSION"
         OR :NEW."CUSTOMER_CODE" <> :OLD."CUSTOMER_CODE" OR :NEW."MODULE" <> :OLD."MODULE"
         OR :NEW."CONNECTOR" <> :OLD."CONNECTOR"
         OR (:NEW."EXPIRES_ON_UTC" <> :OLD."EXPIRES_ON_UTC"
             OR (:NEW."EXPIRES_ON_UTC" IS NULL AND :OLD."EXPIRES_ON_UTC" IS NOT NULL)
             OR (:NEW."EXPIRES_ON_UTC" IS NOT NULL AND :OLD."EXPIRES_ON_UTC" IS NULL))) THEN
        RAISE_APPLICATION_ERROR(-20060, 'an approved write shape is immutable; a changed template is a new shape needing its own approval');
    END IF;
    IF :NEW."STATE" = 'approved' AND :OLD."STATE" <> 'approved' THEN
        IF :NEW."APPROVED_BY_DECISION_ID" IS NULL THEN
            RAISE_APPLICATION_ERROR(-20061, 'a shape is approved only by an HW-approve GATE_DECISION');
        END IF;
        IF :NEW."CLEAN_INSTANCES" < 3 THEN
            RAISE_APPLICATION_ERROR(-20062, 'a shape needs three consecutive clean, person-confirmed instances before approval (Gating § 9.3)');
        END IF;
        :NEW."APPROVED_ON_UTC" := COALESCE(:NEW."APPROVED_ON_UTC", SYS_EXTRACT_UTC(SYSTIMESTAMP));
    END IF;
    IF :OLD."STATE" = 'suspended' AND :NEW."STATE" = 'approved' AND :NEW."APPROVED_BY_DECISION_ID" = :OLD."APPROVED_BY_DECISION_ID" THEN
        RAISE_APPLICATION_ERROR(-20063, 'a suspended shape returns only through a new HW-approve decision (Gating § 7)');
    END IF;
    -- the expiry default is a policy value (writes.shape_expiry_default); the approver may extend ONCE, by at most one day
    IF :NEW."EXTENDED_UNTIL_UTC" IS NOT NULL AND :OLD."EXTENDED_UNTIL_UTC" IS NOT NULL AND :NEW."EXTENDED_UNTIL_UTC" <> :OLD."EXTENDED_UNTIL_UTC" THEN
        RAISE_APPLICATION_ERROR(-20064, 'a write shape expiry may be extended once; a longer life is a re-approval (D133)');
    END IF;
    IF :NEW."EXTENDED_UNTIL_UTC" IS NOT NULL AND :OLD."EXTENDED_UNTIL_UTC" IS NULL AND (:NEW."EXTENDED_BY" IS NULL OR :NEW."EXTENDED_ON_UTC" IS NULL) THEN
        RAISE_APPLICATION_ERROR(-20065, 'an extension names the approver and the time');
    END IF;
END;
/
