-- Explicit immutable OHIP JSON sources; existing XML and legacy deliveries remain valid.
BEGIN;
DO $$ BEGIN
 IF current_database() <> 'booking_test' THEN
  RAISE EXCEPTION 'ARR migration is allowed only in booking_test';
 END IF;
 IF to_regclass('ingestion.daily_review_cases') IS NULL THEN
  RAISE EXCEPTION 'ARR migration 018 must be applied first';
 END IF;
END $$;
ALTER TABLE ingestion.artifacts DROP CONSTRAINT artifacts_artifact_kind_check;
ALTER TABLE ingestion.artifacts ADD CONSTRAINT artifacts_artifact_kind_check CHECK (artifact_kind IN (
 'booking_source_md', 'booking_excel', 'opera_xml', 'ohip_json', 'daily_xlsx', 'monthly_xlsx',
 'channel_detail_xlsx', 'company_ten_day_xlsx', 'exception_xlsx', 'result_json',
 'structured_result_json', 'manual_override_json'
));
ALTER TABLE ingestion.processing_runs DROP CONSTRAINT processing_runs_terminal_shape;
ALTER TABLE ingestion.processing_runs
    ADD CONSTRAINT processing_runs_terminal_shape CHECK (
        (
            run_status = 'accepted'
            AND failure_code IS NULL
            AND validated_at IS NOT NULL
            AND finished_at IS NOT NULL
            AND (
                pipeline_type <> 'opera_daily'
                OR (
                    business_date IS NOT NULL
                    AND delivered_processor_version IS NOT NULL
                    AND delivered_rule_set_sha256 IS NOT NULL
                    AND result_schema_version IS NOT NULL
                    AND delivery_sha256 IS NOT NULL
                    AND (
                        (
                            result_delivery_mode = 'artifact_callback'
                            AND result_artifact_id IS NOT NULL
                        )
                        OR (
                            result_delivery_mode = 'direct_mcp'
                            AND result_artifact_id IS NULL
                        )
                    )
                )
            )
        )
        OR (
            run_status IN ('rejected', 'failed')
            AND failure_code IS NOT NULL
            AND finished_at IS NOT NULL
        )
        OR (run_status = 'cancelled' AND finished_at IS NOT NULL)
        OR (
            run_status = 'awaiting_review'
            AND pipeline_type = 'opera_daily'
            AND result_delivery_mode = 'artifact_callback'
            AND result_artifact_id IS NOT NULL
            AND business_date IS NOT NULL
            AND delivered_processor_version IS NOT NULL
            AND delivered_rule_set_sha256 IS NOT NULL
            AND result_schema_version IN ('4.0', '5.0')
            AND delivery_sha256 IS NOT NULL
            AND validated_at IS NOT NULL
            AND finished_at IS NULL
        )
        OR run_status IN ('received', 'queued', 'running', 'validating')
    );
ALTER TABLE ingestion.processing_runs DROP CONSTRAINT processing_runs_uploaded_filename_check;
ALTER TABLE ingestion.processing_runs ADD CONSTRAINT processing_runs_uploaded_filename_check CHECK (
 uploaded_filename IS NULL OR (
  char_length(uploaded_filename) BETWEEN 1 AND 255 AND uploaded_filename NOT IN ('.', '..')
  AND uploaded_filename !~ '[[:cntrl:]/\\]'
  AND (right(lower(uploaded_filename), 4) = '.xml' OR uploaded_filename = 'ARR.json')
 )
);
COMMIT;