--------------------------------------------------------
--  DDL for View OSEARCH_MIGRATETELEMETRY_MIGVAL_SCOUT_VW
--------------------------------------------------------

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "OTHQ_OWN"."OSEARCH_MIGRATETELEMETRY_MIGVAL_SCOUT_VW" ("MAIN_VERSION", "MIGRATEVERSION", "DBTYPE", "PHASE", "UPGRADE_TYPE", "SCOUT_EXCEPTIONMESSAGE", "ISSUE_NUMBER", "ISSUE_URL", "SCOUT_EXITCODE", "DBID", "DBID_NONMIGVAL", "DBID_MIGVAL", "IS_MIGVAL_DB", "TIMESTAMP_UTC", "TIMESTAMP", "DCID", "OCI", "MOLECULE", "DBHOST", "ISSUE_HAS_ONLY_MIGVAL_DBS", "ISSUE_HAS_ONLY_PROD_DBS", "REGION", "MIGRATIONTASKID", "INDEX_", "DOC_COUNT", "METADATA_REGION", "METADATA_DOWNLOADED_TIMESTAMP_UTC", "MIGRATEFILE", "MIGRATEFILE_SHORT") DEFAULT COLLATION "USING_NLS_COMP"  AS 
  SELECT
    CAST(NULL AS VARCHAR2(4))        AS main_version,
    migrateversion,
    dbtype,
    CAST(NULL AS VARCHAR2(4))        AS phase,
    CAST(NULL AS VARCHAR2(4))        AS upgrade_type,
    CASE
        WHEN scout_exceptionmessage = 'Long-running' THEN
            'Long-Running'
        ELSE
            scout_exceptionmessage
    END         AS scout_exceptionmessage,
    issue_number,
    issue_url,
    scout_exitcode,
    CASE
        WHEN dbid >= 100000000
             AND dbid < 900000000 THEN
            dbid - 100000000
        ELSE
            dbid
    END         AS dbid,
    CASE
        WHEN dbid >= 100000000
             AND dbid < 900000000 THEN
            NULL
        ELSE
            dbid
    END         AS dbid_nonmigval,
    CASE
        WHEN dbid >= 100000000
             AND dbid < 900000000 THEN
            dbid
        ELSE
            NULL
    END         AS dbid_migval,
    CASE
        WHEN dbid >= 100000000
             AND dbid < 900000000 THEN
            'Yes'
        ELSE
            'No'
    END         AS is_migval_db,
    "TIMESTAMP" AS timestamp_utc,
    "TIMESTAMP",
    dcid,
    CASE
        WHEN dcid LIKE '___-%'
             OR dcid = 'field-not-implemented' THEN
            'Yes'
        ELSE
            'No'
    END         AS oci,
    molecule,
    dbhost,
    CASE
        WHEN COUNT(*)
             OVER(PARTITION BY issue_number) = SUM(
            CASE
                WHEN dbid >= 100000000
                     AND dbid < 900000000 THEN
                    1
                ELSE
                    0
            END
        )
                                               OVER(PARTITION BY issue_number) THEN
            'Migval-only'
        WHEN COUNT(*)
             OVER(PARTITION BY issue_number) = SUM(
            CASE
                WHEN dbid < 100000000
                     OR dbid >= 900000000 THEN
                    1
                ELSE
                    0
            END
        )
                                               OVER(PARTITION BY issue_number) THEN
            'non-Migval only'
        ELSE
            'Both'
    END         AS issue_has_only_migval_dbs,
    CASE
        WHEN COUNT(*)
             OVER(PARTITION BY issue_number) = SUM(
            CASE
                WHEN molecule = 'PRODUCTION' THEN
                    1
                ELSE
                    0
            END
        )
                                               OVER(PARTITION BY issue_number) THEN
            'PROD only'
        WHEN COUNT(*)
             OVER(PARTITION BY issue_number) = SUM(
            CASE
                WHEN molecule <> 'PRODUCTION' THEN
                    1
                ELSE
                    0
            END
        )
                                               OVER(PARTITION BY issue_number) THEN
            'QA only'
        ELSE
            'Both'
    END         AS issue_has_only_prod_dbs,
    region,
    migrationtaskid,
    index_,
    doc_count,
    metadata_region,
    metadata_downloaded_timestamp_utc,
    migratefile,
    REGEXP_SUBSTR(migratefile, '[^/]+$') as migratefile_short
FROM
    src_osearch_migratetelemetry_migval_scout_tb
WHERE
    molecule <> 'CPE'
;
