tap_test_results

CREATE VIEW tap_test_results AS
SELECT
    ur.uniform_resource_id,
    ur.uri AS source_file_uri,
    ur.created_at AS tap_file_created_at,
    ur.size_bytes AS tap_file_size_bytes,
    -- Extract filename without path and extension (dynamic extraction using simple string operations)
    CASE
        -- Remove .tap extension if present
        WHEN (
            -- Get filename part after last '/' (or full URI if no '/')
            CASE
                WHEN INSTR(ur.uri, '/') > 0
                THEN REPLACE(ur.uri, RTRIM(ur.uri, REPLACE(ur.uri, '/', '')), '')
                ELSE ur.uri
            END
        ) LIKE '%.tap'
        THEN SUBSTR(
            CASE
                WHEN INSTR(ur.uri, '/') > 0
                THEN REPLACE(ur.uri, RTRIM(ur.uri, REPLACE(ur.uri, '/', '')), '')
                ELSE ur.uri
            END,
            1,
            LENGTH(
                CASE
                    WHEN INSTR(ur.uri, '/') > 0
                    THEN REPLACE(ur.uri, RTRIM(ur.uri, REPLACE(ur.uri, '/', '')), '')
                    ELSE ur.uri
                END
            ) - 4
        )
        ELSE
            CASE
                WHEN INSTR(ur.uri, '/') > 0
                THEN REPLACE(ur.uri, RTRIM(ur.uri, REPLACE(ur.uri, '/', '')), '')
                ELSE ur.uri
            END
    END AS name,
    -- Extract test plan information dynamically (e.g., "1..N" where N can be any number)
    CASE
        WHEN CAST(ur.content AS TEXT) LIKE '%1..%' THEN
            CAST(
                TRIM(
                    SUBSTR(
                        SUBSTR(CAST(ur.content AS TEXT), INSTR(CAST(ur.content AS TEXT), '1..') + 3),
                        1,
                        CASE
                            WHEN INSTR(SUBSTR(CAST(ur.content AS TEXT), INSTR(CAST(ur.content AS TEXT), '1..') + 3), CHAR(10)) > 0
                            THEN INSTR(SUBSTR(CAST(ur.content AS TEXT), INSTR(CAST(ur.content AS TEXT), '1..') + 3), CHAR(10)) - 1
                            WHEN INSTR(SUBSTR(CAST(ur.content AS TEXT), INSTR(CAST(ur.content AS TEXT), '1..') + 3), ' ') > 0
                            THEN INSTR(SUBSTR(CAST(ur.content AS TEXT), INSTR(CAST(ur.content AS TEXT), '1..') + 3), ' ') - 1
                            ELSE 10
                        END
                    )
                ) AS INTEGER
            )
        ELSE NULL
    END AS total_planned_tests,
    -- Count actual test results
    (LENGTH(CAST(ur.content AS TEXT)) - LENGTH(REPLACE(CAST(ur.content AS TEXT), 'ok ', ''))) / 3 AS total_test_lines,
    (LENGTH(CAST(ur.content AS TEXT)) - LENGTH(REPLACE(CAST(ur.content AS TEXT), 'not ok', ''))) / 6 AS failed_tests,
    -- Calculate passed tests
    ((LENGTH(CAST(ur.content AS TEXT)) - LENGTH(REPLACE(CAST(ur.content AS TEXT), 'ok ', ''))) / 3) -
    ((LENGTH(CAST(ur.content AS TEXT)) - LENGTH(REPLACE(CAST(ur.content AS TEXT), 'not ok', ''))) / 6) AS passed_tests,
    -- Overall test status
    CASE
        WHEN (LENGTH(CAST(ur.content AS TEXT)) - LENGTH(REPLACE(CAST(ur.content AS TEXT), 'not ok', ''))) / 6 > 0 THEN 'mixed'
        WHEN (LENGTH(CAST(ur.content AS TEXT)) - LENGTH(REPLACE(CAST(ur.content AS TEXT), 'ok ', ''))) / 3 > 0 THEN 'passed'
        ELSE 'unknown'
    END AS overall_status,
    -- Calculate pass rate
    ur.content AS tap_result_content
FROM uniform_resource ur
WHERE ur.uri LIKE '%.tap'
   OR ur.uri LIKE '%/tap/%'
   OR (ur.content IS NOT NULL AND CAST(ur.content AS TEXT) LIKE '%ok %' AND CAST(ur.content AS TEXT) LIKE '%..%')