--------------------------------------------------------
--  DDL for View CSM_NETSUITE_RELEASE_VW
--------------------------------------------------------

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "OTHQ_OWN"."CSM_NETSUITE_RELEASE_VW" ("NKEY", "METADATA_REGION", "METADATA_ENDPOINT", "DATABASEID", "MIGRATEVERSION", "PMRID", "DATACENTER", "ACCOUNTNAME", "DATABASETYPE", "DATABASEHOST", "STAGE", "STATUS", "TARGETMAINSTARTTIME", "DATABASEOFFLINETIMESTAMPINUTC", "DATABASEONLINETIMESTAMPINUTC", "COLDMIGRATESTART", "COLDMIGRATEEND", "PLANNEDCUSTOMERDOWNTIME", "PREPSTEPDURATION", "MAINSTEPDURATION", "COLDMIGRATEDURATION", "POSTMIGRATEDURATION", "ERRORTEXT", "PREPSTAGEERRORMESSAGE", "MAINSTAGEERRORMESSAGE", "POSTSTAGEERRORMESSAGE", "ROLLBACKSTAGEERRORMESSAGE", "ROLLFORWARDSTAGEERRORMESSAGE", "CANCELSTAGEERRORMESSAGE", "ORA_CODES", "ERROR_CLASS", "ANALYZED_TEXT", "SOURCECDBUNIQUENAME", "TARGETCDBUNIQUENAME", "ISPMRSTATUSSYNCED", "ISVALIDANALYTICSROW", "HARDWARETYPE", "METADATA_SOURCE", "REGION", "ENDPOINT", "METADATA_DOWNLOADED_TIMESTAMP_UTC", "DOWNTIME_MINUTES", "UPGRADE_TYPE2", "UPGRADE_TYPE", "STATUS_ORDER", "TARGETMAINSTARTTIME_PT", "ERRORMESSAGES") DEFAULT COLLATION "USING_NLS_COMP"  AS 
  SELECT
    nkey,
    metadata_region,
    metadata_endpoint,
    databaseid,
    CASE
        WHEN migrateversion LIKE '____/__/_0' ESCAPE '/' THEN
            substr(replace(migrateversion, '_', '.'),
                   1,
                   6)
        ELSE
            substr(replace(migrateversion, '_', '.'),
                   1,
                   7)
    END                                           AS migrateversion,
    CASE
        WHEN REGEXP_LIKE ( pmrid,
                           '^[[:digit:]]+$' ) THEN
            pmrid
        ELSE
            NULL
    END                                           AS pmrid,
    datacenter,
    accountname,
    databasetype,
    databasehost,
    stage,
    CASE status
        WHEN 'AWAITING_PROCESSING' THEN 'PLANNED'
        WHEN 'COMPLETE' THEN 'COMPLETED'
        ELSE status
    END                                          AS status,
    targetmainstarttime,
    databaseofflinetimestampinutc,
    databaseonlinetimestampinutc,
    coldmigratestart,
    coldmigrateend,
    plannedcustomerdowntime,
    prepstepduration,
    mainstepduration,
    coldmigrateduration,
    postmigrateduration,
    errortext,
    prepstageerrormessage,
    mainstageerrormessage,
    poststageerrormessage,
    rollbackstageerrormessage,
    rollforwardstageerrormessage,
    cancelstageerrormessage,
    ora_codes,
    error_class,
    analyzed_text,
    sourcecdbuniquename,
    targetcdbuniquename,
    ispmrstatussynced,
    isvalidanalyticsrow,
    hardwaretype,
-- new columns
    'CSM'                                         AS metadata_source,
    metadata_region                               AS region,
    metadata_endpoint                             AS endpoint,
    METADATA_DOWNLOADED_TIMESTAMP_UTC,
    CASE
        WHEN databaseofflinetimestampinutc IS NULL
             OR databaseonlinetimestampinutc IS NULL THEN
            coldmigrateduration
        ELSE
            round((databaseonlinetimestampinutc - databaseofflinetimestampinutc) * 24 * 60, 0)
    END                                           AS downtime_minutes,
    CASE
        WHEN metadata_endpoint LIKE '%PreMigrates%' THEN
            'NSN Pre-Migrate'
        WHEN metadata_endpoint LIKE '%MonthlyMigrate%' THEN
            'NSX Monthly Upgrade'
        WHEN metadata_endpoint LIKE '%MajorMigrate%' THEN
            'NSN MVU'
        WHEN metadata_endpoint LIKE '%isDryRun%' THEN
            'ATP Dry Run'
        WHEN metadata_endpoint LIKE '%AtpMove%' THEN
            'ATP'
        WHEN metadata_endpoint LIKE '%Maintenance%' THEN
            'TRANSPORTABLETABLESPACE'
        ELSE
            'SQL error: Unknown endpoint'
    END                                           upgrade_type2,
    CASE
        WHEN metadata_endpoint LIKE '%PreMigrates%' THEN
            'NSN Pre-Migrate'
        WHEN metadata_endpoint LIKE '%MonthlyMigrate%' THEN
            'NSX Monthly Upgrade'
        WHEN metadata_endpoint LIKE '%MajorMigrate%' THEN
            'NSN MVU'
        WHEN metadata_endpoint LIKE '%isDryRun%' THEN
            'ATP Dry Run'
        WHEN metadata_endpoint LIKE '%AtpMove%' THEN
            'ATP'
        WHEN metadata_endpoint LIKE '%Maintenance%' THEN
            'TRANSPORTABLETABLESPACE'
        ELSE
            'SQL error: Unknown endpoint'
    END                                           upgrade_type,
    CASE status
        WHEN 'AWAITING_PROCESSING' THEN '2_PLANNED'
        WHEN 'COMPLETE' THEN '3_COMPLETED'
        ELSE '3_' || status
    END                                           AS status_order,
    CAST(from_tz(CAST(targetmainstarttime AS TIMESTAMP),
                 'UTC') AT TIME ZONE 'US/Pacific' AS DATE) AS targetmainstarttime_pt,
    CASE
        WHEN prepstageerrormessage IS NOT NULL THEN
                'PREPSTAGEERRORMESSAGE: '
                || prepstageerrormessage
                || ' '
        ELSE
            NULL
    END
    ||
    CASE
        WHEN mainstageerrormessage IS NOT NULL THEN
                'MAINSTAGEERRORMESSAGE: '
                || mainstageerrormessage
                || ' '
        ELSE
            NULL
    END
    ||
    CASE
        WHEN poststageerrormessage IS NOT NULL THEN
                'POSTSTAGEERRORMESSAGE: '
                || poststageerrormessage
                || ' '
        ELSE
            NULL
    END
    ||
    CASE
        WHEN rollbackstageerrormessage IS NOT NULL THEN
                'ROLLBACKSTAGEERRORMESSAGE: '
                || rollbackstageerrormessage
                || ' '
        ELSE
            NULL
    END
    ||
    CASE
        WHEN rollforwardstageerrormessage IS NOT NULL THEN
                'ROLLFORWARDSTAGEERRORMESSAGE: '
                || rollforwardstageerrormessage
                || ' '
        ELSE
            NULL
    END
    ||
    CASE
        WHEN cancelstageerrormessage IS NOT NULL THEN
                'CANCELSTAGEERRORMESSAGE: '
                || cancelstageerrormessage
                || ' '
        ELSE
            NULL
    END
    ||
    CASE
        WHEN errortext IS NOT NULL THEN
                'ERRORTEXT: ' || errortext
        ELSE
            NULL
    END
    AS errormessages
FROM
    src_csm_netsuite_release_tb
WHERE
    ( isvalidanalyticsrow = 'true'
      OR isvalidanalyticsrow IS NULL )
    AND metadata_endpoint NOT LIKE '%getScheduled%'
;
