--------------------------------------------------------
--  DDL for View RO_NEXT_PREVIEW_VW --
--------------------------------------------------------

CREATE OR REPLACE FORCE EDITIONABLE VIEW "OTHQ_OWN"."RO_NEXT_PREVIEW_VW" (
    "NKEY", 
    "SERVICEENVIRONMENT", 
    "SOURCEACCOUNT", 
    "TARGETACCOUNT", 
    "WORKFLOWID", 
    "TARGETPDB", 
    "TARGETUNIQUECDB", 
    "STATUS", 
    "SNAPSHOTDATE", 
    "VERSION", 
    "CREATEDDATE", 
    "INSTANCESIZE", 
    "CLEANUPDBONFAILURE", 
    "SOURCEDCID", 
    "TARGETDCID", 
    "LOGINTERVENTIONCOUNT", 
    "TOTALREFRESHTIME", 
    "METADATA_DOWNLOADED_TIMESTAMP_UTC", 
    "HARDWARE_TYPE",
    "METADATA_ENDPOINT", 
    "METADATA_REGION", 
    "EMAIL", 
    "FORMATTED_CREATEDDATE", 
    "INSTANCE_SIZE_LABEL",
    "MOVED_TO_ATP",
    "ERROR_TEXT"
) DEFAULT COLLATION "USING_NLS_COMP"
AS 
SELECT 
    "N_KEY", 
    "SERVICE_ENVIRONMENT", 
    "SOURCE_ACCOUNT", 
    "TARGET_ACCOUNT", 
    "WORK_FLOW_ID", 
    "TARGET_PDB", 
    "TARGET_UNIQUE_CDB", 
    "STATUS", 
    "SNAP_SHOT_DATE", 
    "VERSION", 
    "CREATED_DATE", 
    "INSTANCE_SIZE", 
    "CLEAN_UP_DB_ON_FAILURE", 
    "SOURCE_DC_ID" AS SOURCEDCID,
    "TARGET_DC_ID" AS TARGETDCID,
    "LOG_INTERVENTION_COUNT" AS LOGINTERVENTIONCOUNT,
    "TOTAL_REFRESH_TIME" AS TOTALREFRESHTIME,
    "METADATA_DOWNLOADED_TIMESTAMP_UTC",
    "HARDWARE_TYPE", 
    "METADATA_ENDPOINT", 
    "METADATA_REGION", 
    "EMAIL", 
    -- Format CreatedDate
    CASE 
        WHEN "CREATED_DATE" IS NOT NULL THEN TO_CHAR("CREATED_DATE", 'DD-MON-YYYY HH24:MI') 
        ELSE NULL 
    END AS FORMATTED_CREATEDDATE,
    -- InstanceSize Label Logic with explicit casting
    CASE 
        WHEN COALESCE(TO_NUMBER("INSTANCE_SIZE", '9999999.99'), 0) = 0 THEN 'N/A'
        WHEN TO_NUMBER("INSTANCE_SIZE", '9999999.99') < 1000 THEN '<1000'
        WHEN TO_NUMBER("INSTANCE_SIZE", '9999999.99') <= 3000 THEN '1000-3000'
        WHEN TO_NUMBER("INSTANCE_SIZE", '9999999.99') <= 10000 THEN '3000-10000'
        WHEN TO_NUMBER("INSTANCE_SIZE", '9999999.99') > 10000 THEN '>10000'
        ELSE 'N/A'
    END AS INSTANCE_SIZE_LABEL,
    "MOVED_TO_ATP",
    "ERROR_TEXT"
FROM 
    "OTHQ_OWN"."SRC_RO_NEXT_PREVIEW_TB" t1
WHERE LOWER(TRIM(t1."DRY_RUN")) = 'false';