--------------------------------------------------------
--  DDL for View OSEARCH_MIGRATETELEMETRY_MIGRATES_VW
--------------------------------------------------------

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "OTHQ_OWN"."OSEARCH_MIGRATETELEMETRY_MIGRATES_VW" ("RUN_TYPE", "ROW_NUMBER", "DOC_COUNT", "INDEX_", "TIMESTAMP", "DURATIONINSECONDS", "MIGRATIONTASKID", "MIGRATEVERSION", "DBID", "STATUS", "METADATA_REGION", "METADATA_DOWNLOADED_TIMESTAMP_UTC", "TOTAL_MIGRATES", "TOTAL_MIGRATES_STATUS_IS_ERROR", "TOTAL_MIGRATES_STATUS_IS_CANCELLED") DEFAULT COLLATION "USING_NLS_COMP"  AS 
  SELECT
    l."RUN_TYPE",l."ROW_NUMBER",l."DOC_COUNT",l."INDEX_",l."TIMESTAMP",l."DURATIONINSECONDS",l."MIGRATIONTASKID",l."MIGRATEVERSION",l."DBID",l."STATUS",l."METADATA_REGION",l."METADATA_DOWNLOADED_TIMESTAMP_UTC",
    CASE WHEN run_type = 'Last run' THEN COUNT(*) OVER(PARTITION BY dbid, migrateversion) ELSE NULL END AS total_migrates,
    CASE WHEN run_type = 'Last run' THEN SUM(CASE WHEN status IN('Error') THEN 1 ELSE 0 END) OVER(PARTITION BY dbid, migrateversion) ELSE NULL END AS total_migrates_status_is_error,
    CASE WHEN run_type = 'Last run' THEN SUM(CASE WHEN status IN('Cancelled') THEN 1 ELSE 0 END) OVER(PARTITION BY dbid, migrateversion) ELSE NULL END AS total_migrates_status_is_cancelled
FROM
    (
        SELECT
            CASE WHEN ROW_NUMBER() OVER(PARTITION BY dbid, migrateversion ORDER BY TIMESTAMP DESC) = 1 THEN 'Last run' ELSE 'Previous run' END AS run_type,
            t.*
        FROM
            (
                SELECT
                    ROW_NUMBER() OVER(PARTITION BY dbid, migrateversion, migrationtaskid ORDER BY TIMESTAMP DESC) AS row_number,
                    m.*
                FROM
                    src_osearch_migratetelemetry_migrates_tb m
                WHERE
                    status IS NOT NULL
            ) t
        WHERE
            row_number = 1
    ) l
;
