tem_openssl_original_txt

CREATE VIEW tem_openssl_original_txt AS
WITH RECURSIVE cert_blocks AS (
    -- Anchor: start from the beginning of content with tenant info
    SELECT
        ur.uniform_resource_id,
        t.tenant_id,
        t.tenant_name,
        ts.ur_ingest_session_id,
        ur.content AS remaining_text,
        NULL AS metadata,
        NULL AS certificate,
        1 AS cert_index
    FROM uniform_resource ur
    INNER JOIN tem_tenant t ON t.device_id = ur.device_id
    INNER JOIN tem_session ts ON ur.device_id = ts.device_id
    WHERE ur.uri LIKE '%openssl%'  -- filter by uri
    
    UNION ALL

    -- Recursive step: extract the next certificate block
    SELECT
        uniform_resource_id,
        tenant_id,
        tenant_name,
        ur_ingest_session_id,
        substr(remaining_text, instr(remaining_text, '-----END CERTIFICATE-----') + length('-----END CERTIFICATE-----')) AS remaining_text,
        trim(substr(remaining_text, 1, instr(remaining_text, '-----BEGIN CERTIFICATE-----') - 1)) AS metadata,
        substr(
            remaining_text,
            instr(remaining_text, '-----BEGIN CERTIFICATE-----'),
            instr(remaining_text, '-----END CERTIFICATE-----') - instr(remaining_text, '-----BEGIN CERTIFICATE-----') + length('-----END CERTIFICATE-----')
        ) AS certificate,
        cert_index + 1
    FROM cert_blocks
    WHERE remaining_text LIKE '%-----BEGIN CERTIFICATE-----%'
)
SELECT
    uniform_resource_id,
    tenant_id,
    tenant_name,
    ur_ingest_session_id,
    cert_index,
    metadata,
    certificate
FROM cert_blocks
WHERE certificate IS NOT NULL
ORDER BY uniform_resource_id, cert_index