SET DEFINE OFF
WHENEVER SQLERROR EXIT SQL.SQLCODE

-- 008 · IMMUTABILITY and PAYLOAD GUARDS — enforced in the database because the service is the thing being audited
-- (Architecture § 3.2; the SrdAi 002 pattern). Error codes -20010… are the platform's; -20001…-20009 are install errors.
--
--   -20010…-20029  append-only tables (UPDATE/DELETE refused)
--   -20030…-20039  the ledger chain
--   -20040…-20059  governance: published versions immutable; publish needs PASSED + approved; red eval set blocks
--   -20060…-20069  write shapes: approved template immutable; one extension of at most one day (D133)
--   -20070…-20079  data boundary

-- ------------------------------------------------------------------ append-only tables
CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."AUDIT_LEDGER_IMM_TRG"
BEFORE UPDATE OR DELETE ON "SRD_SUPPORT"."AUDIT_LEDGER"
BEGIN
    RAISE_APPLICATION_ERROR(-20010, 'AUDIT_LEDGER is append-only; correct with a CORRECTION record');
END;
/

CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."COST_LEDGER_IMM_TRG"
BEFORE UPDATE OR DELETE ON "SRD_SUPPORT"."COST_LEDGER"
BEGIN
    RAISE_APPLICATION_ERROR(-20011, 'COST_LEDGER is append-only');
END;
/

CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."LEDGER_SEALS_IMM_TRG"
BEFORE UPDATE OR DELETE ON "SRD_SUPPORT"."LEDGER_SEALS"
BEGIN
    RAISE_APPLICATION_ERROR(-20012, 'LEDGER_SEALS are immutable');
END;
/

CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."LEDGER_EXPORTS_IMM_TRG"
BEFORE UPDATE OR DELETE ON "SRD_SUPPORT"."LEDGER_EXPORTS"
BEGIN
    RAISE_APPLICATION_ERROR(-20013, 'LEDGER_EXPORTS is append-only; a failed export is retried as a new attempt row');
END;
/

CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."GATE_DECISIONS_IMM_TRG"
BEFORE UPDATE OR DELETE ON "SRD_SUPPORT"."GATE_DECISIONS"
BEGIN
    RAISE_APPLICATION_ERROR(-20014, 'GATE_DECISIONS are immutable');
END;
/

CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."WRITE_LOG_IMM_TRG"
BEFORE UPDATE OR DELETE ON "SRD_SUPPORT"."WRITE_LOG"
FOR EACH ROW
BEGIN
    -- the only permitted transition: an intended write becomes executed/refused/failed/reverted, once
    IF DELETING OR :OLD."OUTCOME" <> 'intended'
       OR :NEW."STATEMENT_HASH" <> :OLD."STATEMENT_HASH" OR :NEW."TARGET" <> :OLD."TARGET"
       OR :NEW."ENVIRONMENT" <> :OLD."ENVIRONMENT" OR :NEW."MODE" <> :OLD."MODE" THEN
        RAISE_APPLICATION_ERROR(-20015, 'WRITE_LOG rows are append-only; an intended write may only receive its outcome');
    END IF;
END;
/

CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."PLSQL_SNAPSHOTS_IMM_TRG"
BEFORE UPDATE OR DELETE ON "SRD_SUPPORT"."PLSQL_SNAPSHOTS"
BEGIN
    RAISE_APPLICATION_ERROR(-20016, 'PLSQL_SNAPSHOTS are append-only');
END;
/

CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."PLAN_REVISIONS_IMM_TRG"
BEFORE UPDATE OR DELETE ON "SRD_SUPPORT"."PLAN_REVISIONS"
BEGIN
    RAISE_APPLICATION_ERROR(-20017, 'PLAN_REVISIONS are immutable; a change is a new revision');
END;
/

CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."GOVERNANCE_EVENTS_IMM_TRG"
BEFORE UPDATE OR DELETE ON "SRD_SUPPORT"."GOVERNANCE_EVENTS"
BEGIN
    RAISE_APPLICATION_ERROR(-20018, 'GOVERNANCE_EVENTS are append-only');
END;
/

CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."EVAL_RUNS_IMM_TRG"
BEFORE UPDATE OR DELETE ON "SRD_SUPPORT"."EVAL_RUNS"
FOR EACH ROW
BEGIN
    IF DELETING OR :OLD."ENDED_ON_UTC" IS NOT NULL THEN
        RAISE_APPLICATION_ERROR(-20019, 'EVAL_RUNS are immutable once ended');
    END IF;
END;
/

CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."PAPERS_IMM_TRG"
BEFORE UPDATE OR DELETE ON "SRD_SUPPORT"."PAPERS"
BEGIN
    RAISE_APPLICATION_ERROR(-20020, 'PAPERS revision rows are immutable; a rewrite is a new REVISION_NO');
END;
/

CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."MEMORY_USES_IMM_TRG"
BEFORE DELETE ON "SRD_SUPPORT"."MEMORY_USES"
BEGIN
    RAISE_APPLICATION_ERROR(-20021, 'MEMORY_USES are never deleted; version pinning is the audit');
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."CASE_TYPE" <> :OLD."CASE_TYPE" OR :NEW."GATE_KIND" <> :OLD."GATE_KIND"
       OR :NEW."TARGET_CLASS" <> :OLD."TARGET_CLASS" OR :NEW."WRITE_CLASS" <> :OLD."WRITE_CLASS" THEN
        RAISE_APPLICATION_ERROR(-20022, 'RISK_ACCEPTANCES may only be withdrawn, never edited or deleted (N-12)');
    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."ROLE" <> :OLD."ROLE" OR :NEW."SUBJECT_ID" <> :OLD."SUBJECT_ID" THEN
        RAISE_APPLICATION_ERROR(-20023, 'ROLE_ASSIGNMENTS may only be revoked, never edited or deleted');
    END IF;
END;
/

-- ------------------------------------------------------------------ the ledger chain
-- PREV_HASH must be an existing RECORD_HASH — or the genesis constant for the first record only.
-- Together with UK_LEDGER_PREV_HASH this makes the chain strict: one successor per record, no orphans.
CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."AUDIT_LEDGER_CHAIN_TRG"
BEFORE INSERT ON "SRD_SUPPORT"."AUDIT_LEDGER"
FOR EACH ROW
DECLARE
    v_exists   INTEGER;
    v_genesis  CONSTANT NVARCHAR2(64) := '0000000000000000000000000000000000000000000000000000000000000000';
BEGIN
    IF :NEW."PREV_HASH" = v_genesis THEN
        SELECT COUNT(*) INTO v_exists FROM "SRD_SUPPORT"."AUDIT_LEDGER";
        IF v_exists > 0 THEN
            RAISE_APPLICATION_ERROR(-20030, 'the genesis PREV_HASH may only appear on the first ledger record');
        END IF;
    ELSE
        SELECT COUNT(*) INTO v_exists FROM "SRD_SUPPORT"."AUDIT_LEDGER" WHERE "RECORD_HASH" = :NEW."PREV_HASH";
        IF v_exists = 0 THEN
            RAISE_APPLICATION_ERROR(-20031, 'PREV_HASH does not name an existing RECORD_HASH — the chain would be broken');
        END IF;
    END IF;
    IF :NEW."KIND" = 'CORRECTION' THEN
        SELECT COUNT(*) INTO v_exists FROM "SRD_SUPPORT"."AUDIT_LEDGER" WHERE "RECORD_ID" = :NEW."SUPERSEDES_RECORD_ID";
        IF v_exists = 0 THEN
            RAISE_APPLICATION_ERROR(-20032, 'a CORRECTION must name an existing record it supersedes');
        END IF;
    END IF;
END;
/

-- ------------------------------------------------------------------ governance: published versions are immutable,
-- publishing needs a PASSED evaluation and an approval, and a red eval set blocks (Agent Framework § 6.1, § 6.5)
CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."PROMPTS_GOV_TRG"
BEFORE UPDATE ON "SRD_SUPPORT"."PROMPTS"
FOR EACH ROW
DECLARE
    v_passed INTEGER; v_approved INTEGER;
BEGIN
    IF :OLD."STATE" IN ('published','retired') AND (
         DBMS_LOB.COMPARE(:NEW."BODY", :OLD."BODY") <> 0 OR :NEW."CONTENT_HASH" <> :OLD."CONTENT_HASH"
         OR DBMS_LOB.COMPARE(:NEW."VARIABLES", :OLD."VARIABLES") <> 0 OR :NEW."PROMPT_KEY" <> :OLD."PROMPT_KEY" OR :NEW."VERSION" <> :OLD."VERSION") THEN
        RAISE_APPLICATION_ERROR(-20040, 'a published prompt version is immutable; a change is a new version');
    END IF;
    IF :OLD."STATE" = 'retired' AND :NEW."STATE" <> 'retired' THEN
        RAISE_APPLICATION_ERROR(-20041, 'a retired prompt version does not come back; rollback re-publishes a previous version');
    END IF;
    IF :NEW."STATE" = 'published' AND :OLD."STATE" <> 'published' THEN
        SELECT COUNT(*) INTO v_passed FROM "SRD_SUPPORT"."GOVERNANCE_EVENTS"
         WHERE "SUBJECT_TYPE" = 'prompt' AND "SUBJECT_KEY" = :NEW."PROMPT_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" = 'prompt' AND "SUBJECT_KEY" = :NEW."PROMPT_KEY" AND "SUBJECT_VERSION" = :NEW."VERSION" AND "EVENT" = 'approved';
        IF v_passed = 0 OR v_approved = 0 THEN
            RAISE_APPLICATION_ERROR(-20042, 'publishing a prompt needs a PASSED evaluation and an approval recorded in GOVERNANCE_EVENTS');
        END IF;
        :NEW."PUBLISHED_ON_UTC" := COALESCE(:NEW."PUBLISHED_ON_UTC", SYS_EXTRACT_UTC(SYSTIMESTAMP));
    END IF;
END;
/

CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."SKILLS_GOV_TRG"
BEFORE UPDATE ON "SRD_SUPPORT"."SKILLS"
FOR EACH ROW
DECLARE
    v_passed INTEGER; v_approved INTEGER;
BEGIN
    IF :OLD."STATE" IN ('published','retired') AND (:NEW."MANIFEST_HASH" <> :OLD."MANIFEST_HASH" OR DBMS_LOB.COMPARE(:NEW."MANIFEST", :OLD."MANIFEST") <> 0) THEN
        RAISE_APPLICATION_ERROR(-20043, 'a published skill version is immutable; a change is a new version');
    END IF;
    IF :NEW."STATE" = 'published' AND :OLD."STATE" <> 'published' THEN
        SELECT COUNT(*) INTO v_passed FROM "SRD_SUPPORT"."GOVERNANCE_EVENTS"
         WHERE "SUBJECT_TYPE" = 'skill' AND "SUBJECT_KEY" = :NEW."SKILL_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" = 'skill' AND "SUBJECT_KEY" = :NEW."SKILL_KEY" AND "SUBJECT_VERSION" = :NEW."VERSION" AND "EVENT" = 'approved';
        IF v_passed = 0 OR v_approved = 0 THEN
            RAISE_APPLICATION_ERROR(-20044, 'publishing a skill needs a PASSED evaluation and an approval recorded in GOVERNANCE_EVENTS');
        END IF;
        :NEW."PUBLISHED_ON_UTC" := COALESCE(:NEW."PUBLISHED_ON_UTC", SYS_EXTRACT_UTC(SYSTIMESTAMP));
    END IF;
END;
/

CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."PROFILES_GOV_TRG"
BEFORE UPDATE ON "SRD_SUPPORT"."PROFILES"
FOR EACH ROW
DECLARE
    v_passed INTEGER; v_approved INTEGER; v_red INTEGER; v_zone NVARCHAR2(16); v_dpa NUMBER(1);
BEGIN
    IF :OLD."STATE" IN ('published','retired') AND (:NEW."CONTENT_HASH" <> :OLD."CONTENT_HASH" OR :NEW."PROMPT_ID" <> :OLD."PROMPT_ID"
         OR :NEW."MODEL_ROUTE_ID" <> :OLD."MODEL_ROUTE_ID" 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 version is immutable; a change is a new version (Agents § 0.4)');
    END IF;
    IF :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';
        IF v_passed = 0 OR v_approved = 0 THEN
            RAISE_APPLICATION_ERROR(-20046, 'publishing a profile needs a PASSED evaluation and an approval recorded in GOVERNANCE_EVENTS');
        END IF;
        SELECT COUNT(*) INTO v_red FROM "SRD_SUPPORT"."EVAL_SETS" WHERE "ID" = :NEW."EVAL_SET_ID" AND "LAST_RESULT" = 'red';
        IF v_red > 0 THEN
            RAISE_APPLICATION_ERROR(-20047, 'nothing publishes with a red eval set (Agent Framework § 6.5)');
        END IF;
        -- the data boundary is code at the route selector (Agents § 0.3): a Support profile binds only an EU, DPA-covered route
        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 profile may only publish with an EU-resident, DPA-covered route (SG-9)');
        END IF;
        :NEW."PUBLISHED_ON_UTC" := COALESCE(:NEW."PUBLISHED_ON_UTC", SYS_EXTRACT_UTC(SYSTIMESTAMP));
    END IF;
END;
/

CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."MODEL_ROUTES_GOV_TRG"
BEFORE UPDATE ON "SRD_SUPPORT"."MODEL_ROUTES"
FOR EACH ROW
BEGIN
    IF :OLD."STATE" IN ('published','retired') AND (:NEW."PROVIDER_CODE" <> :OLD."PROVIDER_CODE" OR :NEW."DEPLOYMENT_NAME" <> :OLD."DEPLOYMENT_NAME"
         OR :NEW."MODEL_NAME" <> :OLD."MODEL_NAME" OR :NEW."DATA_ZONE" <> :OLD."DATA_ZONE" OR :NEW."DPA_COVERED" <> :OLD."DPA_COVERED"
         OR :NEW."ENDPOINT_REF" <> :OLD."ENDPOINT_REF") THEN
        RAISE_APPLICATION_ERROR(-20049, 'a published model route is immutable; a route change is a governed decision per profile (Software Architecture § 7.1)');
    END IF;
END;
/

CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."MEMORY_ARTICLES_GOV_TRG"
BEFORE UPDATE ON "SRD_SUPPORT"."MEMORY_ARTICLES"
FOR EACH ROW
BEGIN
    IF :OLD."STATE" IN ('active','retired') AND (:NEW."GIT_SHA" <> :OLD."GIT_SHA" OR :NEW."CONTENT_HASH" <> :OLD."CONTENT_HASH") THEN
        RAISE_APPLICATION_ERROR(-20050, 'an active article is immutable; a change is a new version (Agents Memory § 7.4b)');
    END IF;
    IF :OLD."STATE" = 'retired' AND :NEW."STATE" <> 'retired' THEN
        RAISE_APPLICATION_ERROR(-20051, 'a retired article does not come back');
    END IF;
END;
/

-- ------------------------------------------------------------------ write shapes (Gating § 4, § 7; D133)
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") 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" = 'draft' 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;
/

-- ------------------------------------------------------------------ data boundary
CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."CASE_HANDLES_IMM_TRG"
BEFORE UPDATE OR DELETE ON "SRD_SUPPORT"."CASE_HANDLES"
BEGIN
    RAISE_APPLICATION_ERROR(-20070, 'CASE_HANDLES are never rewritten; destruction is the key (CASE_KEYS.DESTROYED_ON_UTC), which shreds every value');
END;
/

CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."CASE_KEYS_GOV_TRG"
BEFORE UPDATE OR DELETE ON "SRD_SUPPORT"."CASE_KEYS"
FOR EACH ROW
BEGIN
    IF DELETING OR :OLD."DESTROYED_ON_UTC" IS NOT NULL OR :NEW."WRAPPED_DATA_KEY" <> :OLD."WRAPPED_DATA_KEY" THEN
        RAISE_APPLICATION_ERROR(-20071, 'a case data key may only be destroyed, once');
    END IF;
END;
/
