SET DEFINE OFF
WHENEVER SQLERROR EXIT SQL.SQLCODE

-- 002 · DECISIONS and GRANTS — Architecture § 3.1 group "Decisions" + "Queues"; Gating § 4, § 9.2; Trust and Data § 2.
-- A gate is a row, not a call (Gating § 9.2). A grant is bound to the artefact hash it was issued for and is
-- checked by the connector before contact. A write shape expires on a default schedule and an approver may
-- extend it by at most one day per extension (A-9 → D133).

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
    -- ---------------------------------------------------------------- WRITE_SHAPES (before GATES: GATES/PLAN_STEPS reference it)
    ddl(q'~CREATE TABLE "SRD_SUPPORT"."WRITE_SHAPES" (
        "ID"                        NVARCHAR2(64)   NOT NULL,
        "SHAPE_KEY"                 NVARCHAR2(128)  NOT NULL,
        "VERSION"                   NUMBER(10)      NOT NULL,
        "MODULE"                    NVARCHAR2(16)   NOT NULL,
        "CUSTOMER_CODE"             NVARCHAR2(32)   NOT NULL,
        "OPERATION_ID"              NVARCHAR2(128)  NOT NULL,
        "CONNECTOR"                 NVARCHAR2(32)   NOT NULL,
        "TEMPLATE"                  CLOB            NOT NULL,
        "TEMPLATE_HASH"             NVARCHAR2(64)   NOT NULL,
        "PARAMETERS"                CLOB            NOT NULL,
        "TARGET_CLASS"              NVARCHAR2(24)   NOT NULL,
        "EFFECT_CLASS"              NVARCHAR2(16)   NOT NULL,
        "WRITE_CLASS"               NVARCHAR2(2)    NOT NULL,
        "EXPECTED_COUNTS_EXPR"      NVARCHAR2(1000) NOT NULL,
        "ASSERTIONS"                CLOB            NOT NULL,
        "TEARDOWN_TEMPLATE"         CLOB            NOT NULL,
        "CASE_TYPES"                CLOB            NOT NULL,
        "STATE"                     NVARCHAR2(16)   NOT NULL,
        "CLEAN_INSTANCES"           NUMBER(5)       DEFAULT 0 NOT NULL,
        "APPROVED_BY_DECISION_ID"   NVARCHAR2(64),
        "APPROVED_ON_UTC"           TIMESTAMP(6),
        "EXPIRES_ON_UTC"            TIMESTAMP(6),
        "EXTENDED_UNTIL_UTC"        TIMESTAMP(6),
        "EXTENDED_BY"               NVARCHAR2(128),
        "EXTENDED_ON_UTC"           TIMESTAMP(6),
        "SUSPENDED_ON_UTC"          TIMESTAMP(6),
        "SUSPENSION_REASON"         NVARCHAR2(1000),
        "CREATED_BY"                NVARCHAR2(128)  NOT NULL,
        "CREATED_ON_UTC"            TIMESTAMP(6)    NOT NULL,
        "ROW_VERSION"               NUMBER(19)      DEFAULT 0 NOT NULL,
        CONSTRAINT "PK_WRITE_SHAPES" PRIMARY KEY ("ID"),
        CONSTRAINT "UK_WRITE_SHAPES" UNIQUE ("CUSTOMER_CODE", "SHAPE_KEY", "VERSION"),
        CONSTRAINT "CK_SHAPES_MODULE" CHECK ("MODULE" IN ('configuration','support','source')),
        CONSTRAINT "CK_SHAPES_STATE" CHECK ("STATE" IN ('draft','approved','suspended','retired')),
        CONSTRAINT "CK_SHAPES_TCLASS" CHECK ("TARGET_CLASS" IN ('working','shared','customer_facing','external','customer_visible')),
        CONSTRAINT "CK_SHAPES_EFFECT" CHECK ("EFFECT_CLASS" IN ('transactional','compensable','irreversible','ddl')),
        CONSTRAINT "CK_SHAPES_WCLASS" CHECK ("WRITE_CLASS" IN ('W1','W2','W3','W4','W5','W6','W7')),
        CONSTRAINT "CK_SHAPES_PARAMS_JSON" CHECK ("PARAMETERS" IS JSON),
        CONSTRAINT "CK_SHAPES_ASSERT_JSON" CHECK ("ASSERTIONS" IS JSON),
        CONSTRAINT "CK_SHAPES_TYPES_JSON" CHECK ("CASE_TYPES" IS JSON),
        CONSTRAINT "CK_SHAPES_CLEAN" CHECK ("CLEAN_INSTANCES" >= 0),
        CONSTRAINT "CK_SHAPES_APPROVED" CHECK ("STATE" <> 'approved' OR ("APPROVED_ON_UTC" IS NOT NULL AND "EXPIRES_ON_UTC" IS NOT NULL)),
        CONSTRAINT "CK_SHAPES_EXTENSION" CHECK ("EXTENDED_UNTIL_UTC" IS NULL OR ("EXPIRES_ON_UTC" IS NOT NULL AND "EXTENDED_UNTIL_UTC" > "EXPIRES_ON_UTC" AND "EXTENDED_UNTIL_UTC" <= "EXPIRES_ON_UTC" + INTERVAL '1' DAY))
    )~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_SHAPES_STATE" ON "SRD_SUPPORT"."WRITE_SHAPES" ("STATE", "MODULE", "CUSTOMER_CODE")~');
    ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."WRITE_SHAPES" IS 'Gating § 4. The approved template a policy may later instantiate (contract: contracts/write-shape.schema.json). Immutable once approved except STATE, the suspension columns and one extension (CK_SHAPES_EXTENSION: at most EXPIRES_ON_UTC + 1 day — D133). EXPIRES_ON_UTC defaults from policy writes.shape_expiry_default at approval.'~');

    -- ---------------------------------------------------------------- GATES
    ddl(q'~CREATE TABLE "SRD_SUPPORT"."GATES" (
        "ID"                        NVARCHAR2(64)   NOT NULL,
        "CASE_ID"                   NVARCHAR2(64)   NOT NULL,
        "TASK_ID"                   NVARCHAR2(64),
        "KIND"                      NVARCHAR2(16)   NOT NULL,
        "TARGET_CLASS"              NVARCHAR2(24),
        "ENVIRONMENT"               NVARCHAR2(32),
        "SYSTEM"                    NVARCHAR2(32),
        "ARTEFACT_HASH"             NVARCHAR2(64)   NOT NULL,
        "PACKET_PAPER_ID"           NVARCHAR2(64),
        "REQUIRED_ROLE"             NVARCHAR2(32)   NOT NULL,
        "SLA_CLASS"                 NVARCHAR2(16),
        "STATE"                     NVARCHAR2(16)   NOT NULL,
        "SHAPE_ID"                  NVARCHAR2(64),
        "AUTO_CONFIRM_FAILED_CHECK" NUMBER(2),
        "AUDITOR_VERDICT"           NVARCHAR2(8),
        "AUDITOR_VERDICT_PAPER_ID"  NVARCHAR2(64),
        "OPENED_ON_UTC"             TIMESTAMP(6)    NOT NULL,
        "ESCALATION_DUE_UTC"        TIMESTAMP(6),
        "ESCALATION_LEVEL"          NUMBER(1)       DEFAULT 0 NOT NULL,
        "DECIDED_ON_UTC"            TIMESTAMP(6),
        "ROW_VERSION"               NUMBER(19)      DEFAULT 0 NOT NULL,
        CONSTRAINT "PK_GATES" PRIMARY KEY ("ID"),
        CONSTRAINT "FK_GATES_CASE" FOREIGN KEY ("CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
        CONSTRAINT "FK_GATES_TASK" FOREIGN KEY ("TASK_ID") REFERENCES "SRD_SUPPORT"."TASKS" ("ID"),
        CONSTRAINT "FK_GATES_SHAPE" FOREIGN KEY ("SHAPE_ID") REFERENCES "SRD_SUPPORT"."WRITE_SHAPES" ("ID"),
        CONSTRAINT "CK_GATES_KIND" CHECK ("KIND" IN ('H1','H2','H3','H4','H5','H6','H7','Hd','HW-approve','HW-instance','HW-shared','HW-irreversible','HW-ddl','H5-SIM','H5-send','CG-11','publish')),
        CONSTRAINT "CK_GATES_STATE" CHECK ("STATE" IN ('open','decided','expired','withdrawn')),
        CONSTRAINT "CK_GATES_ROLE" CHECK ("REQUIRED_ROLE" IN ('operator','approver','prompt_publisher','administrator','customer_representative','approver_pair')),
        CONSTRAINT "CK_GATES_TCLASS" CHECK ("TARGET_CLASS" IS NULL OR "TARGET_CLASS" IN ('working','shared','customer_facing','external','customer_visible')),
        CONSTRAINT "CK_GATES_CHECK" CHECK ("AUTO_CONFIRM_FAILED_CHECK" IS NULL OR "AUTO_CONFIRM_FAILED_CHECK" BETWEEN 1 AND 9),
        CONSTRAINT "CK_GATES_AUDITOR" CHECK ("AUDITOR_VERDICT" IS NULL OR "AUDITOR_VERDICT" IN ('pass','refuse')),
        CONSTRAINT "CK_GATES_ESC" CHECK ("ESCALATION_LEVEL" BETWEEN 0 AND 2)
    )~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_GATES_OPEN" ON "SRD_SUPPORT"."GATES" ("STATE", "REQUIRED_ROLE", "OPENED_ON_UTC")~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_GATES_CASE" ON "SRD_SUPPORT"."GATES" ("CASE_ID", "STATE")~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_GATES_ESCALATION" ON "SRD_SUPPORT"."GATES" ("STATE", "ESCALATION_DUE_UTC")~');
    ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."GATES" IS 'Gating § 1 and § 9.2. The Decisions workspace is a view over STATE = open. AUTO_CONFIRM_FAILED_CHECK names the first failing condition of Gating § 9.4 so the operator sees why they are asked. Escalation is a timer on the row (PG-12): level 0 holders, 1 all holders, 2 administrator. REQUIRED_ROLE approver_pair = H5-SIM (two roles, D107).'~');

    -- ---------------------------------------------------------------- GATE_DECISIONS (immutable)
    ddl(q'~CREATE TABLE "SRD_SUPPORT"."GATE_DECISIONS" (
        "ID"                        NVARCHAR2(64)   NOT NULL,
        "GATE_ID"                   NVARCHAR2(64)   NOT NULL,
        "ACTOR_KIND"                NVARCHAR2(8)    NOT NULL,
        "ACTOR_ID"                  NVARCHAR2(128)  NOT NULL,
        "ACTOR_ROLE"                NVARCHAR2(32)   NOT NULL,
        "DECISION"                  NVARCHAR2(16)   NOT NULL,
        "SCOPE_APPROVED"            CLOB,
        "PLAN_REVISION_ID"          NVARCHAR2(64),
        "ARTEFACT_HASH"             NVARCHAR2(64)   NOT NULL,
        "SHAPE_ID"                  NVARCHAR2(64),
        "SHAPE_VERSION"             NUMBER(10),
        "REASON"                    NVARCHAR2(2000),
        "DECIDED_ON_UTC"            TIMESTAMP(6)    NOT NULL,
        "LEDGER_RECORD_ID"          NVARCHAR2(64)   NOT NULL,
        CONSTRAINT "PK_GATE_DECISIONS" PRIMARY KEY ("ID"),
        CONSTRAINT "FK_GDEC_GATE" FOREIGN KEY ("GATE_ID") REFERENCES "SRD_SUPPORT"."GATES" ("ID"),
        CONSTRAINT "FK_GDEC_PLANREV" FOREIGN KEY ("PLAN_REVISION_ID") REFERENCES "SRD_SUPPORT"."PLAN_REVISIONS" ("ID"),
        CONSTRAINT "FK_GDEC_SHAPE" FOREIGN KEY ("SHAPE_ID") REFERENCES "SRD_SUPPORT"."WRITE_SHAPES" ("ID"),
        CONSTRAINT "CK_GDEC_ACTOR" CHECK ("ACTOR_KIND" IN ('person','policy')),
        CONSTRAINT "CK_GDEC_DECISION" CHECK ("DECISION" IN ('approve','reject','amend','defer','accept','withdraw')),
        CONSTRAINT "CK_GDEC_SCOPE_JSON" CHECK ("SCOPE_APPROVED" IS NULL OR "SCOPE_APPROVED" IS JSON),
        CONSTRAINT "CK_GDEC_POLICY_SHAPE" CHECK ("ACTOR_KIND" <> 'policy' OR ("SHAPE_ID" IS NOT NULL AND "SHAPE_VERSION" IS NOT NULL))
    )~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_GDEC_GATE" ON "SRD_SUPPORT"."GATE_DECISIONS" ("GATE_ID")~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_GDEC_SHAPE" ON "SRD_SUPPORT"."GATE_DECISIONS" ("SHAPE_ID", "SHAPE_VERSION", "DECIDED_ON_UTC")~');
    ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."GATE_DECISIONS" IS 'Append-only. ACTOR_KIND policy = an auto-confirmed instance (Gating § 5, D70) — the same row, the policy id where a name would be; CK_GDEC_POLICY_SHAPE makes the shape mandatory for it. Every row is chained into AUDIT_LEDGER via LEDGER_RECORD_ID.'~');

    -- ---------------------------------------------------------------- GRANTS
    ddl(q'~CREATE TABLE "SRD_SUPPORT"."GRANTS" (
        "ID"                        NVARCHAR2(64)   NOT NULL,
        "CASE_ID"                   NVARCHAR2(64)   NOT NULL,
        "ENVIRONMENT"               NVARCHAR2(32)   NOT NULL,
        "SYSTEM"                    NVARCHAR2(32)   NOT NULL,
        "TARGET"                    NVARCHAR2(512)  NOT NULL,
        "MODE"                      NVARCHAR2(8)    NOT NULL,
        "BOUND_ARTEFACT_HASH"       NVARCHAR2(64),
        "EXPECTED_ROWS"             NUMBER(10),
        "PRECONDITIONS_HASH"        NVARCHAR2(64),
        "ISSUED_BY_KIND"            NVARCHAR2(16)   NOT NULL,
        "ISSUED_BY_REF"             NVARCHAR2(64)   NOT NULL,
        "ISSUED_ON_UTC"             TIMESTAMP(6)    NOT NULL,
        "EXPIRES_ON_UTC"            TIMESTAMP(6)    NOT NULL,
        "STATE"                     NVARCHAR2(16)   NOT NULL,
        "REVOKED_ON_UTC"            TIMESTAMP(6),
        "REVOKED_REASON"            NVARCHAR2(1000),
        "ROW_VERSION"               NUMBER(19)      DEFAULT 0 NOT NULL,
        CONSTRAINT "PK_GRANTS" PRIMARY KEY ("ID"),
        CONSTRAINT "FK_GRANTS_CASE" FOREIGN KEY ("CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
        CONSTRAINT "CK_GRANTS_MODE" CHECK ("MODE" IN ('read','dry_run','write','send','ddl')),
        CONSTRAINT "CK_GRANTS_STATE" CHECK ("STATE" IN ('active','consumed','expired','revoked')),
        CONSTRAINT "CK_GRANTS_ISSUER" CHECK ("ISSUED_BY_KIND" IN ('case_type','gate_decision','administrator')),
        CONSTRAINT "CK_GRANTS_BOUND" CHECK ("MODE" IN ('read','dry_run') OR "BOUND_ARTEFACT_HASH" IS NOT NULL),
        CONSTRAINT "CK_GRANTS_WINDOW" CHECK ("EXPIRES_ON_UTC" > "ISSUED_ON_UTC")
    )~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_GRANTS_CASE" ON "SRD_SUPPORT"."GRANTS" ("CASE_ID", "STATE")~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_GRANTS_SCOPE" ON "SRD_SUPPORT"."GRANTS" ("ENVIRONMENT", "SYSTEM", "MODE", "STATE")~');
    ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."GRANTS" IS 'Trust and Data § 2. Scope = environment × system × target × mode; a write grant is bound to the artefact it was approved for (CK_GRANTS_BOUND). Checked by the connector before contact. Expires at case close or EXPIRES_ON_UTC, whichever first (grants.expiry).'~');

    -- ---------------------------------------------------------------- RISK_ACCEPTANCES (N-12 register)
    ddl(q'~CREATE TABLE "SRD_SUPPORT"."RISK_ACCEPTANCES" (
        "ID"                        NVARCHAR2(64)   NOT NULL,
        "CUSTOMER_CODE"             NVARCHAR2(32)   NOT NULL,
        "MODULE"                    NVARCHAR2(16)   NOT NULL,
        "CASE_TYPE"                 NVARCHAR2(64)   NOT NULL,
        "GATE_KIND"                 NVARCHAR2(16)   NOT NULL,
        "TARGET_CLASS"              NVARCHAR2(24)   NOT NULL,
        "WRITE_CLASS"               NVARCHAR2(2)    NOT NULL,
        "ACCEPTED_BY"               NVARCHAR2(128)  NOT NULL,
        "ACCEPTED_ROLE"             NVARCHAR2(64)   NOT NULL,
        "DECISION_REF"              NVARCHAR2(256)  NOT NULL,
        "ACCEPTED_ON_UTC"           TIMESTAMP(6)    NOT NULL,
        "WITHDRAWN_ON_UTC"          TIMESTAMP(6),
        "WITHDRAWN_BY"              NVARCHAR2(128),
        "LEDGER_RECORD_ID"          NVARCHAR2(64)   NOT NULL,
        CONSTRAINT "PK_RISK_ACCEPTANCES" PRIMARY KEY ("ID"),
        CONSTRAINT "CK_RISK_MODULE" CHECK ("MODULE" IN ('configuration','support','source')),
        CONSTRAINT "CK_RISK_WCLASS" CHECK ("WRITE_CLASS" IN ('W1','W2'))
    )~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_RISK_LOOKUP" ON "SRD_SUPPORT"."RISK_ACCEPTANCES" ("CUSTOMER_CODE", "CASE_TYPE", "GATE_KIND", "TARGET_CLASS", "WITHDRAWN_ON_UTC")~');
    ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."RISK_ACCEPTANCES" IS 'The register N-12 names: a write class leaves the always-human set only by a row here, written by a person with DECISION_REF to the recorded organisational decision. Gating § 9.4 check 1 reads it. CK_RISK_WCLASS: only W1/W2 can ever be accepted (Gating § 3); a plug-in may narrow, never widen.'~');

    -- ---------------------------------------------------------------- HELD_BATCHES · HELD_BATCH_ITEMS
    ddl(q'~CREATE TABLE "SRD_SUPPORT"."HELD_BATCHES" (
        "ID"                        NVARCHAR2(64)   NOT NULL,
        "CASE_ID"                   NVARCHAR2(64)   NOT NULL,
        "GATE_DECISION_ID"          NVARCHAR2(64)   NOT NULL,
        "GRANT_ID"                  NVARCHAR2(64)   NOT NULL,
        "ITEMS_TOTAL"               NUMBER(10)      NOT NULL,
        "ITEMS_APPLIED"             NUMBER(10)      DEFAULT 0 NOT NULL,
        "PREFLIGHT_MODE"            NVARCHAR2(16)   NOT NULL,
        "STATE"                     NVARCHAR2(16)   NOT NULL,
        "HELD_SINCE_UTC"            TIMESTAMP(6)    NOT NULL,
        "LAST_PREFLIGHT_UTC"        TIMESTAMP(6),
        "COMPLETED_ON_UTC"          TIMESTAMP(6),
        "ROW_VERSION"               NUMBER(19)      DEFAULT 0 NOT NULL,
        CONSTRAINT "PK_HELD_BATCHES" PRIMARY KEY ("ID"),
        CONSTRAINT "FK_HELD_CASE" FOREIGN KEY ("CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
        CONSTRAINT "FK_HELD_DECISION" FOREIGN KEY ("GATE_DECISION_ID") REFERENCES "SRD_SUPPORT"."GATE_DECISIONS" ("ID"),
        CONSTRAINT "FK_HELD_GRANT" FOREIGN KEY ("GRANT_ID") REFERENCES "SRD_SUPPORT"."GRANTS" ("ID"),
        CONSTRAINT "CK_HELD_MODE" CHECK ("PREFLIGHT_MODE" IN ('ordinal','per_item')),
        CONSTRAINT "CK_HELD_STATE" CHECK ("STATE" IN ('held','applying','applied','abandoned')),
        CONSTRAINT "CK_HELD_COUNTS" CHECK ("ITEMS_TOTAL" > 0 AND "ITEMS_APPLIED" BETWEEN 0 AND "ITEMS_TOTAL")
    )~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_HELD_STATE" ON "SRD_SUPPORT"."HELD_BATCHES" ("STATE", "HELD_SINCE_UTC")~');
    ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."HELD_BATCHES" IS 'Approved-but-unapplied work with its age (Gating § 9.2) — the fix for the 12-week HDesk plateau. A batch older than held_batches.repreflight_after is re-preflighted from the first unapplied item (D66).'~');

    ddl(q'~CREATE TABLE "SRD_SUPPORT"."HELD_BATCH_ITEMS" (
        "ID"                        NVARCHAR2(64)   NOT NULL,
        "BATCH_ID"                  NVARCHAR2(64)   NOT NULL,
        "SEQ_NO"                    NUMBER(10)      NOT NULL,
        "PLAN_STEP_ID"              NVARCHAR2(64)   NOT NULL,
        "STATEMENT_HASH"            NVARCHAR2(64)   NOT NULL,
        "STATE"                     NVARCHAR2(16)   NOT NULL,
        "PREFLIGHTED_ON_UTC"        TIMESTAMP(6),
        "APPLIED_ON_UTC"            TIMESTAMP(6),
        "WRITE_LOG_ID"              NVARCHAR2(64),
        CONSTRAINT "PK_HELD_BATCH_ITEMS" PRIMARY KEY ("ID"),
        CONSTRAINT "UK_HELD_BATCH_ITEMS" UNIQUE ("BATCH_ID", "SEQ_NO"),
        CONSTRAINT "FK_HITEM_BATCH" FOREIGN KEY ("BATCH_ID") REFERENCES "SRD_SUPPORT"."HELD_BATCHES" ("ID"),
        CONSTRAINT "FK_HITEM_STEP" FOREIGN KEY ("PLAN_STEP_ID") REFERENCES "SRD_SUPPORT"."PLAN_STEPS" ("ID"),
        CONSTRAINT "CK_HITEM_STATE" CHECK ("STATE" IN ('pending','preflighted','applied','failed','skipped'))
    )~');

    -- ---------------------------------------------------------------- WRITE_LEASES (shape B)
    ddl(q'~CREATE TABLE "SRD_SUPPORT"."WRITE_LEASES" (
        "ID"                        NVARCHAR2(64)   NOT NULL,
        "CASE_ID"                   NVARCHAR2(64)   NOT NULL,
        "GRANT_ID"                  NVARCHAR2(64)   NOT NULL,
        "CONNECTOR"                 NVARCHAR2(32)   NOT NULL,
        "ENVIRONMENT"               NVARCHAR2(32)   NOT NULL,
        "SESSION_REF"               NVARCHAR2(256)  NOT NULL,
        "EPOCH"                     NUMBER(19)      NOT NULL,
        "STATE"                     NVARCHAR2(16)   NOT NULL,
        "LEASED_ON_UTC"             TIMESTAMP(6)    NOT NULL,
        "LEASED_UNTIL_UTC"          TIMESTAMP(6)    NOT NULL,
        "ENDED_ON_UTC"              TIMESTAMP(6),
        "ROW_VERSION"               NUMBER(19)      DEFAULT 0 NOT NULL,
        CONSTRAINT "PK_WRITE_LEASES" PRIMARY KEY ("ID"),
        CONSTRAINT "FK_LEASE_CASE" FOREIGN KEY ("CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
        CONSTRAINT "FK_LEASE_GRANT" FOREIGN KEY ("GRANT_ID") REFERENCES "SRD_SUPPORT"."GRANTS" ("ID"),
        CONSTRAINT "CK_LEASE_STATE" CHECK ("STATE" IN ('open','committed','rolled_back','expired')),
        CONSTRAINT "CK_LEASE_WINDOW" CHECK ("LEASED_UNTIL_UTC" > "LEASED_ON_UTC")
    )~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_LEASES_OPEN" ON "SRD_SUPPORT"."WRITE_LEASES" ("STATE", "LEASED_UNTIL_UTC")~');
    ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."WRITE_LEASES" IS 'Failure and Recovery § 1 write shape B: a connector-owned transaction under a lease that rolls back on expiry. No agent turn spans an uncommitted write (CR-9); the lease is what makes the rule checkable.'~');

    -- deferred FK: PLAN_STEPS.SHAPE_ID → WRITE_SHAPES (WRITE_SHAPES is created in this script)
    ddl(q'~ALTER TABLE "SRD_SUPPORT"."PLAN_STEPS" ADD CONSTRAINT "FK_STEPS_SHAPE" FOREIGN KEY ("SHAPE_ID") REFERENCES "SRD_SUPPORT"."WRITE_SHAPES" ("ID")~');
END;
/
