--------------------------------------------------------
--  DDL for View NLCORP_DATABASES_MIGVAL_VW
--------------------------------------------------------

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "OTHQ_OWN"."NLCORP_DATABASES_MIGVAL_VW" ("LAGGING_NSN_VERSION", "LEADING_NSN_VERSION", "LAGGING_NSX_VERSION", "LEADING_NSX_VERSION", "INTERNALID", "DBNAME", "DBNKEY", "DBHOST", "DBINSTANCE", "PRODUCT_TYPE", "UPGRADE_TYPE", "HIGHRISK", "DBSIZE", "DBTYPE", "VERSION", "PDB_CATEGORY", "PRODUCTION_PHASE", "DATACENTER", "DCID_FIXED", "REGION", "DESCRIPTION", "IS_ONLINE", "CREATED_DATE_UTC", "UPGRADE_DATE_UTC", "MAINTENANCE_WINDOW", "DCID", "DCID_BACKEND", "OCI", "DATACENTER_OLD", "INACTIVE", "PRE_MIGRATE_COLD_TEST_DONE", "PRE_MIGRATE_HOT_TEST_DONE", "MIGRATE_COLD_TEST_DONE", "MIGRATE_POST_TEST_DONE", "PRE_MIGRATE_COLD_TEST_DATE_UTC", "PRE_MIGRATE_HOT_TEST_DATE_UTC", "MIGRATE_COLD_TEST_DATE_UTC", "MIGRATE_POST_TEST_DATE_UTC", "LAST_MODIFIED_UTC", "METADATA_DOWNLOADED_TIMESTAMP_UTC", "VERSION_TESTED", "PRE_MIGRATE_COLD_TEST_DONE2", "PRE_MIGRATE_HOT_TEST_DONE2", "MIGRATE_COLD_TEST_DONE2", "MIGRATE_POST_TEST_DONE2") DEFAULT COLLATION "USING_NLS_COMP"  AS 
  SELECT
    t."LAGGING_NSN_VERSION",t."LEADING_NSN_VERSION",t."LAGGING_NSX_VERSION",t."LEADING_NSX_VERSION",t."INTERNALID",t."DBNAME",t."DBNKEY",t."DBHOST",t."DBINSTANCE",t."PRODUCT_TYPE",t."UPGRADE_TYPE",t."HIGHRISK",t."DBSIZE",t."DBTYPE",t."VERSION",t."PDB_CATEGORY",t."PRODUCTION_PHASE",t."DATACENTER",t."DCID_FIXED",t."REGION",t."DESCRIPTION",t."IS_ONLINE",t."CREATED_DATE_UTC",t."UPGRADE_DATE_UTC",t."MAINTENANCE_WINDOW",t."DCID",t."DCID_BACKEND",t."OCI",t."DATACENTER_OLD",t."INACTIVE",t."PRE_MIGRATE_COLD_TEST_DONE",t."PRE_MIGRATE_HOT_TEST_DONE",t."MIGRATE_COLD_TEST_DONE",t."MIGRATE_POST_TEST_DONE",t."PRE_MIGRATE_COLD_TEST_DATE_UTC",t."PRE_MIGRATE_HOT_TEST_DATE_UTC",t."MIGRATE_COLD_TEST_DATE_UTC",t."MIGRATE_POST_TEST_DATE_UTC",t."LAST_MODIFIED_UTC",t."METADATA_DOWNLOADED_TIMESTAMP_UTC",
    CASE
        WHEN product_type = 'NSX' THEN
            leading_nsx_version
        ELSE
            leading_nsn_version
    END AS version_tested,
    CASE
        WHEN version = lagging_nsn_version
             OR version = lagging_nsx_version THEN
                CASE
                    WHEN pre_migrate_cold_test_done = 'true' THEN
                        'Tested'
                    ELSE
                        'Not tested'
                END
        ELSE
            CASE
                WHEN pre_migrate_cold_test_done = 'true' THEN
                        'Tested'
                ELSE
                    'Skipped'
            END
    END AS pre_migrate_cold_test_done2,
    CASE
        WHEN version = lagging_nsn_version
             OR version = lagging_nsx_version THEN
                CASE
                    WHEN pre_migrate_hot_test_done = 'true' THEN
                        'Tested'
                    ELSE
                        'Not tested'
                END
        ELSE
            CASE
                WHEN pre_migrate_hot_test_done = 'true' THEN
                        'Tested'
                ELSE
                    'Skipped'
            END
    END AS pre_migrate_hot_test_done2,
    CASE
        WHEN version = lagging_nsn_version
             OR version = lagging_nsx_version THEN
                CASE
                    WHEN migrate_cold_test_done = 'true' THEN
                        'Tested'
                    ELSE
                        'Not tested'
                END
        ELSE
            CASE
                WHEN migrate_cold_test_done = 'true' THEN
                        'Tested'
                ELSE
                    'Skipped'
            END
    END AS migrate_cold_test_done2,
    CASE
        WHEN version = lagging_nsn_version
             OR version = lagging_nsx_version THEN
                CASE
                    WHEN migrate_post_test_done = 'true' THEN
                        'Tested'
                    ELSE
                        'Not tested'
                END
        ELSE
            CASE
                WHEN migrate_post_test_done = 'true' THEN
                        'Tested'
                ELSE
                    'Skipped'
            END
    END AS migrate_post_test_done2
FROM
    (
        SELECT
            '2025.2'                               AS lagging_nsn_version,
            '2026.1'                               AS leading_nsn_version,
            '2025.04'                              AS lagging_nsx_version,
            '2025.05'                              AS leading_nsx_version,
            internalid,
            dbname,
            dbnkey,
            dbhost,
            dbinstance,
            CASE
                WHEN dbinstance LIKE 'nsx%' THEN
                    'NSX'
                ELSE
                    'NSN'
            END                                    AS product_type,
            CASE
                WHEN dbinstance LIKE 'nsx%' THEN
                    'Monthly'
                ELSE
                    'Major|Pre-migrate'
            END                                    AS upgrade_type,
            lower(highrisk)                        AS highrisk,
            dbsize,
            dbtype,
            CASE
                WHEN dbinstance LIKE 'nsx%'
                     AND version LIKE '____._' THEN
                    concat(concat(substr(version, 1, 4),
                                  '.'),
                           lpad(substr(version, 6, 2),
                                2,
                                '0'))
                ELSE
                    version
            END                                    AS version,
            CASE
                WHEN dbinstance LIKE 'nsx%' THEN
                    'ATP_NSX'
                WHEN dbinstance LIKE '%.adb.%' THEN
                    'ATP_NSN'
                ELSE
                    'EXA_NSN'
            END                                    AS pdb_category,
            production_phase,
            datacenter,
            regexp_substr(datacenter, '*.[^ ]+*')  AS dcid_fixed,
            regexp_substr(datacenter, '*.[^ -]+*') AS region,
            description,
            lower("ONLINE")                        AS is_online,
            created_date_utc,
            upgrade_date_utc,
            maintenance_window,
            dcid,
            dcid_backend,
            CASE
                WHEN dcid_backend LIKE '___-%' THEN
                    'true'
                ELSE
                    'false'
            END                                    AS oci,
            datacenter_old,
            lower(inactive)                        AS inactive,
            lower(pre_migrate_cold_test_done)      AS pre_migrate_cold_test_done,
            lower(pre_migrate_hot_test_done)       AS pre_migrate_hot_test_done,
            lower(migrate_cold_test_done)          AS migrate_cold_test_done,
            lower(migrate_post_test_done)          AS migrate_post_test_done,
            pre_migrate_cold_test_date_utc,
            pre_migrate_hot_test_date_utc,
            migrate_cold_test_date_utc,
            migrate_post_test_date_utc,
            last_modified_utc,
            metadata_downloaded_timestamp_utc
        FROM
            src_nlcorp_databases_migval_tb
        WHERE
                lower("ONLINE") = 'true'
            AND lower(inactive) = 'false'
    ) t
;
