SET DEFINE OFF
SET SERVEROUTPUT ON
WHENEVER SQLERROR EXIT SQL.SQLCODE

-- verify-schema.sql — the structural acceptance of the SrdSupport script set: every table of the inventory exists,
-- every append-only table carries its immutability trigger, every JSON column is constrained, and the platform
-- policy seed is present. Fails on the first missing item.

DECLARE
    TYPE t_names IS TABLE OF VARCHAR2(64);
    v_tables  t_names := t_names(
        'CASES','SESSIONS','TASKS','TURN_CHECKPOINTS','CASE_IMPACT_KEYS','CASE_LINKS','ARRIVALS',
        'PLANS','PLAN_REVISIONS','PLAN_STEPS',
        'WRITE_SHAPES','GATES','GATE_DECISIONS','GRANTS','RISK_ACCEPTANCES','HELD_BATCHES','HELD_BATCH_ITEMS','WRITE_LEASES',
        'PAPERS','WRITE_LOG','BUILD_STATE','MISSING','PLSQL_SNAPSHOTS','CASE_KEYS','CASE_HANDLES',
        'AUDIT_LEDGER','LEDGER_SEALS','LEDGER_EXPORTS','COST_LEDGER',
        'PROMPTS','MODEL_ROUTES','PROFILES','SKILLS','EVAL_SETS','EVAL_RUNS','GOVERNANCE_EVENTS','CONNECTOR_SCOPES','CONNECTOR_HEALTH',
        'MEMORY_DOMAINS','MEMORY_ARTICLES','MEMORY_USES','EXPERIENCE_ENTRIES',
        'POLICY_VALUES','RUNTIME_CALLS','INBOX_MESSAGES','IMPACT_LOCKS','CASE_TYPES','ROLE_ASSIGNMENTS','OUTBOX_MESSAGES','RETENTION_ACTIONS');
    v_triggers t_names := t_names(
        'AUDIT_LEDGER_IMM_TRG','AUDIT_LEDGER_CHAIN_TRG','COST_LEDGER_IMM_TRG','LEDGER_SEALS_IMM_TRG','LEDGER_EXPORTS_IMM_TRG',
        'GATE_DECISIONS_IMM_TRG','WRITE_LOG_IMM_TRG','PLSQL_SNAPSHOTS_IMM_TRG','PLAN_REVISIONS_IMM_TRG','GOVERNANCE_EVENTS_IMM_TRG',
        'EVAL_RUNS_IMM_TRG','PAPERS_IMM_TRG','MEMORY_USES_IMM_TRG','RISK_ACCEPTANCES_IMM_TRG','ROLE_ASSIGNMENTS_IMM_TRG',
        'PROMPTS_GOV_TRG','SKILLS_GOV_TRG','PROFILES_GOV_TRG','MODEL_ROUTES_GOV_TRG','MEMORY_ARTICLES_GOV_TRG','WRITE_SHAPES_GOV_TRG',
        'CASE_HANDLES_IMM_TRG','RUNTIME_CALLS_GOV_TRG','INBOX_MESSAGES_IMM_TRG','TURN_CHECKPOINTS_IMM_TRG','CASE_KEYS_GOV_TRG');
    v_n INTEGER;
    v_missing VARCHAR2(4000) := '';
BEGIN
    FOR i IN 1 .. v_tables.COUNT LOOP
        SELECT COUNT(*) INTO v_n FROM USER_TABLES WHERE TABLE_NAME = v_tables(i);
        IF v_n = 0 THEN v_missing := v_missing || ' table:' || v_tables(i); END IF;
    END LOOP;
    FOR i IN 1 .. v_triggers.COUNT LOOP
        SELECT COUNT(*) INTO v_n FROM USER_TRIGGERS WHERE TRIGGER_NAME = v_triggers(i) AND STATUS = 'ENABLED';
        IF v_n = 0 THEN v_missing := v_missing || ' trigger:' || v_triggers(i); END IF;
    END LOOP;
    SELECT COUNT(*) INTO v_n FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TRIGGER' AND STATUS = 'INVALID';
    IF v_n > 0 THEN v_missing := v_missing || ' invalid_triggers:' || v_n; END IF;
    SELECT COUNT(*) INTO v_n FROM "SRD_SUPPORT"."POLICY_VALUES" WHERE "SCOPE" = 'platform' AND "STATE" = 'published' AND "KEY" IN ('budget.dimension','ledger.seal_interval','writes.always_human','writes.shape_expiry_default');
    IF v_n < 4 THEN v_missing := v_missing || ' seed:platform_policy(' || v_n || '/4)'; END IF;

    IF v_missing IS NOT NULL THEN
        RAISE_APPLICATION_ERROR(-20090, 'verify-schema FAILED:' || v_missing);
    END IF;
    DBMS_OUTPUT.PUT_LINE('verify-schema: OK — ' || v_tables.COUNT || ' tables, ' || v_triggers.COUNT || ' guards, platform policy seeded');
END;
/
