SET DEFINE OFF
WHENEVER SQLERROR EXIT SQL.SQLCODE

-- 007 · POLICY and OPERATIONS — Whitelabel Catalogue (every value with a default, as rows — CR-10, D51, D67);
-- Whitelabel Catalogue § 6 (case types); Trust and Data § 3 (roles held as rows — Software Architecture § 10.7 row 1);
-- Software Architecture § 4 (RabbitMQ.Outbox: "our SRD_SUPPORT supplies the rows"); Delivery § 4 (retention evaluator, D109).

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
    -- ---------------------------------------------------------------- POLICY_VALUES (the Whitelabel Catalogue as rows)
    ddl(q'~CREATE TABLE "SRD_SUPPORT"."POLICY_VALUES" (
        "ID"                        NVARCHAR2(64)   NOT NULL,
        "SCOPE"                     NVARCHAR2(16)   NOT NULL,
        "CUSTOMER_CODE"             NVARCHAR2(32)   DEFAULT '*' NOT NULL,
        "MODULE"                    NVARCHAR2(16)   DEFAULT '*' NOT NULL,
        "CASE_TYPE"                 NVARCHAR2(64)   DEFAULT '*' NOT NULL,
        "PROFILE_KEY"               NVARCHAR2(128)  DEFAULT '*' NOT NULL,
        "KEY"                       NVARCHAR2(128)  NOT NULL,
        "VERSION"                   NUMBER(10)      NOT NULL,
        "VALUE"                     CLOB            NOT NULL,
        "VALUE_TYPE"                NVARCHAR2(16)   NOT NULL,
        "CATALOGUE_SECTION"         NVARCHAR2(64),
        "STATE"                     NVARCHAR2(16)   NOT NULL,
        "SET_BY"                    NVARCHAR2(128)  NOT NULL,
        "PUBLISHED_ON_UTC"          TIMESTAMP(6),
        "RETIRED_ON_UTC"            TIMESTAMP(6),
        "CREATED_ON_UTC"            TIMESTAMP(6)    NOT NULL,
        CONSTRAINT "PK_POLICY_VALUES" PRIMARY KEY ("ID"),
        CONSTRAINT "UK_POLICY_VALUES" UNIQUE ("SCOPE", "CUSTOMER_CODE", "MODULE", "CASE_TYPE", "PROFILE_KEY", "KEY", "VERSION"),
        CONSTRAINT "CK_POLICY_SCOPE" CHECK ("SCOPE" IN ('platform','customer','case_type','profile')),
        CONSTRAINT "CK_POLICY_TYPE" CHECK ("VALUE_TYPE" IN ('number','string','boolean','duration','json')),
        CONSTRAINT "CK_POLICY_STATE" CHECK ("STATE" IN ('draft','published','retired')),
        CONSTRAINT "CK_POLICY_VALUE_JSON" CHECK ("VALUE" IS JSON),
        CONSTRAINT "CK_POLICY_PLATFORM_STAR" CHECK ("SCOPE" <> 'platform' OR ("CUSTOMER_CODE" = '*' AND "MODULE" = '*' AND "CASE_TYPE" = '*' AND "PROFILE_KEY" = '*'))
    )~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_POLICY_KEY" ON "SRD_SUPPORT"."POLICY_VALUES" ("KEY", "STATE", "SCOPE")~');
    ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."POLICY_VALUES" IS 'One row per Whitelabel Catalogue key, version and scope. Resolution order at case open: profile > case_type > customer > platform (the most specific published row wins; ''*'' = any). VALUE is always JSON (a scalar is a JSON scalar). A customer value is a governed change of the plug-in, not a code release (Catalogue § 7); the seed of the platform defaults is 009.'~');

    -- ---------------------------------------------------------------- CASE_TYPES
    ddl(q'~CREATE TABLE "SRD_SUPPORT"."CASE_TYPES" (
        "ID"                        NVARCHAR2(64)   NOT NULL,
        "CUSTOMER_CODE"             NVARCHAR2(32)   NOT NULL,
        "MODULE"                    NVARCHAR2(16)   NOT NULL,
        "CASE_TYPE"                 NVARCHAR2(64)   NOT NULL,
        "VERSION"                   NUMBER(10)      NOT NULL,
        "INITIAL_GRANTS"            CLOB            NOT NULL,
        "BUDGET_MINUTES"            NUMBER(10)      NOT NULL,
        "SLA_CLASS"                 NVARCHAR2(16),
        "STAGE_SET"                 CLOB            NOT NULL,
        "GATE_PLAN"                 CLOB            NOT NULL,
        "DEFAULT_ENVIRONMENT"       NVARCHAR2(32)   NOT NULL,
        "WORKING_ENVIRONMENT"       NVARCHAR2(32),
        "DEPLOYMENT_PRESET"         NVARCHAR2(32),
        "START_FLOW"                NVARCHAR2(32),
        "STATE"                     NVARCHAR2(16)   NOT NULL,
        "SET_BY"                    NVARCHAR2(128)  NOT NULL,
        "PUBLISHED_ON_UTC"          TIMESTAMP(6),
        "CREATED_ON_UTC"            TIMESTAMP(6)    NOT NULL,
        CONSTRAINT "PK_CASE_TYPES" PRIMARY KEY ("ID"),
        CONSTRAINT "UK_CASE_TYPES" UNIQUE ("CUSTOMER_CODE", "MODULE", "CASE_TYPE", "VERSION"),
        CONSTRAINT "CK_CTYPE_MODULE" CHECK ("MODULE" IN ('configuration','support','source')),
        CONSTRAINT "CK_CTYPE_STATE" CHECK ("STATE" IN ('draft','published','retired')),
        CONSTRAINT "CK_CTYPE_JSON" CHECK ("INITIAL_GRANTS" IS JSON AND "STAGE_SET" IS JSON AND "GATE_PLAN" IS JSON),
        CONSTRAINT "CK_CTYPE_BUDGET" CHECK ("BUDGET_MINUTES" > 0)
    )~');
    ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."CASE_TYPES" IS 'Whitelabel Catalogue § 6: each type carries its initial grants, its predicted delivery time in minutes (the case budget — D135), SLA class, stage set and gate plan (graphs.stage_sets, D67), and per module the working environment / deployment preset / start flow. A case copies these at open; a later change never rewrites a running case.'~');

    -- ---------------------------------------------------------------- ROLE_ASSIGNMENTS (roles held as rows)
    ddl(q'~CREATE TABLE "SRD_SUPPORT"."ROLE_ASSIGNMENTS" (
        "ID"                        NVARCHAR2(64)   NOT NULL,
        "SUBJECT_ID"                NVARCHAR2(256)  NOT NULL,
        "SUBJECT_KIND"              NVARCHAR2(8)    NOT NULL,
        "CUSTOMER_CODE"             NVARCHAR2(32)   NOT NULL,
        "ROLE"                      NVARCHAR2(32)   NOT NULL,
        "SCOPE_MODULE"              NVARCHAR2(16)   DEFAULT '*' NOT NULL,
        "SCOPE_STAGE"               NVARCHAR2(16)   DEFAULT '*' NOT NULL,
        "SCOPE_TARGET_CLASS"        NVARCHAR2(24)   DEFAULT '*' NOT NULL,
        "SCOPE_SYSTEM"              NVARCHAR2(32)   DEFAULT '*' NOT NULL,
        "SOURCE"                    NVARCHAR2(16)   NOT NULL,
        "SOURCE_REF"                NVARCHAR2(256),
        "GRANTED_BY"                NVARCHAR2(128)  NOT NULL,
        "GRANTED_ON_UTC"            TIMESTAMP(6)    NOT NULL,
        "REVOKED_ON_UTC"            TIMESTAMP(6),
        "REVOKED_BY"                NVARCHAR2(128),
        "LEDGER_RECORD_ID"          NVARCHAR2(64)   NOT NULL,
        CONSTRAINT "PK_ROLE_ASSIGNMENTS" PRIMARY KEY ("ID"),
        CONSTRAINT "CK_ROLE_SUBJECT" CHECK ("SUBJECT_KIND" IN ('user','group')),
        CONSTRAINT "CK_ROLE_ROLE" CHECK ("ROLE" IN ('viewer','operator','approver','prompt_publisher','administrator','customer_representative')),
        CONSTRAINT "CK_ROLE_SOURCE" CHECK ("SOURCE" IN ('token_claim','directory_group','platform_row'))
    )~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_ROLE_SUBJECT" ON "SRD_SUPPORT"."ROLE_ASSIGNMENTS" ("SUBJECT_ID", "REVOKED_ON_UTC")~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_ROLE_ROLE" ON "SRD_SUPPORT"."ROLE_ASSIGNMENTS" ("CUSTOMER_CODE", "ROLE", "REVOKED_ON_UTC")~');
    ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."ROLE_ASSIGNMENTS" IS 'Trust and Data § 3 (D134): the five platform roles + customer representative, held as rows. SOURCE says where a row came from — token_claim (a role claim in the Authority JWT), directory_group (roles.group_mapping resolved at login) or platform_row (granted by an Administrator here). Prompt publisher is scoped per module/stage, approver per target class/system. The gate check reads the union of live rows for the subject.'~');

    -- ---------------------------------------------------------------- OUTBOX_MESSAGES (RabbitMQ.Outbox rows)
    ddl(q'~CREATE TABLE "SRD_SUPPORT"."OUTBOX_MESSAGES" (
        "ID"                        NVARCHAR2(64)   NOT NULL,
        "EXCHANGE"                  NVARCHAR2(128)  NOT NULL,
        "ROUTING_KEY"               NVARCHAR2(256)  NOT NULL,
        "MESSAGE_TYPE"              NVARCHAR2(128)  NOT NULL,
        "MESSAGE_VERSION"           NUMBER(5)       NOT NULL,
        "PAYLOAD"                   CLOB            NOT NULL,
        "HEADERS"                   CLOB,
        "CORRELATION_ID"            NVARCHAR2(64),
        "CASE_ID"                   NVARCHAR2(64),
        "STATE"                     NVARCHAR2(16)   NOT NULL,
        "LEASE_OWNER"               NVARCHAR2(128),
        "LEASE_UNTIL_UTC"           TIMESTAMP(6),
        "ATTEMPTS"                  NUMBER(5)       DEFAULT 0 NOT NULL,
        "LAST_ERROR"                NVARCHAR2(2000),
        "CREATED_ON_UTC"            TIMESTAMP(6)    NOT NULL,
        "PUBLISHED_ON_UTC"          TIMESTAMP(6),
        CONSTRAINT "PK_OUTBOX_MESSAGES" PRIMARY KEY ("ID"),
        CONSTRAINT "CK_OUTBOX_STATE" CHECK ("STATE" IN ('pending','leased','published','failed','parked')),
        CONSTRAINT "CK_OUTBOX_JSON" CHECK ("PAYLOAD" IS JSON AND ("HEADERS" IS NULL OR "HEADERS" IS JSON))
    )~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_OUTBOX_PENDING" ON "SRD_SUPPORT"."OUTBOX_MESSAGES" ("STATE", "CREATED_ON_UTC")~');
    ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."OUTBOX_MESSAGES" IS 'Transactional outbox for the Rabbit lanes and the SignalR fan-out (Software Architecture § 7 Realtime/Messaging): a row is written in the same transaction as the state change it announces; the copied RabbitMQ.Outbox library leases and publishes it.'~');

    -- ---------------------------------------------------------------- RETENTION_ACTIONS (D109 — manual actions are gated writes)
    ddl(q'~CREATE TABLE "SRD_SUPPORT"."RETENTION_ACTIONS" (
        "ID"                        NVARCHAR2(64)   NOT NULL,
        "ARTEFACT_CLASS"            NVARCHAR2(32)   NOT NULL,
        "SUBJECT_TABLE"             NVARCHAR2(64)   NOT NULL,
        "SUBJECT_ID"                NVARCHAR2(64)   NOT NULL,
        "CASE_ID"                   NVARCHAR2(64),
        "ACTION"                    NVARCHAR2(16)   NOT NULL,
        "TRIGGER"                   NVARCHAR2(16)   NOT NULL,
        "POLICY_VALUE_ID"           NVARCHAR2(64),
        "GATE_ID"                   NVARCHAR2(64),
        "STATE"                     NVARCHAR2(16)   NOT NULL,
        "REQUESTED_BY"              NVARCHAR2(128)  NOT NULL,
        "REQUESTED_ON_UTC"          TIMESTAMP(6)    NOT NULL,
        "EXECUTED_ON_UTC"           TIMESTAMP(6),
        "LEDGER_RECORD_ID"          NVARCHAR2(64),
        CONSTRAINT "PK_RETENTION_ACTIONS" PRIMARY KEY ("ID"),
        CONSTRAINT "FK_RET_CASE" FOREIGN KEY ("CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
        CONSTRAINT "FK_RET_GATE" FOREIGN KEY ("GATE_ID") REFERENCES "SRD_SUPPORT"."GATES" ("ID"),
        CONSTRAINT "FK_RET_POLICY" FOREIGN KEY ("POLICY_VALUE_ID") REFERENCES "SRD_SUPPORT"."POLICY_VALUES" ("ID"),
        CONSTRAINT "CK_RET_CLASS" CHECK ("ARTEFACT_CLASS" IN ('case_papers','ledger','handle_map','special_category_evidence','memory_article','eval_corpus','tool_result')),
        CONSTRAINT "CK_RET_ACTION" CHECK ("ACTION" IN ('erase','anonymise','extend','export','destroy_key')),
        CONSTRAINT "CK_RET_TRIGGER" CHECK ("TRIGGER" IN ('policy_expiry','case_closure','manual','legal_request')),
        CONSTRAINT "CK_RET_STATE" CHECK ("STATE" IN ('proposed','at_gate','approved','executed','refused')),
        CONSTRAINT "CK_RET_MANUAL_GATED" CHECK ("TRIGGER" <> 'manual' OR "STATE" = 'proposed' OR "GATE_ID" IS NOT NULL)
    )~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_RET_STATE" ON "SRD_SUPPORT"."RETENTION_ACTIONS" ("STATE", "REQUESTED_ON_UTC")~');
    ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."RETENTION_ACTIONS" IS 'PG-13 / D109: retention per artefact class is configuration (retention.per_class); cases and the ledger never expire; handle maps die at case closure (destroy_key = crypto-shredding); manual erase/anonymise/extend/export are gated writes (CK_RET_MANUAL_GATED). The evaluator exists from day one and is idle until a class has an expiry.'~');
END;
/
