--------------------------------------------------------
--  DDL for View OSEARCH_MIGRATETELEMETRY_MIGVAL_MIGRATES_VW
--------------------------------------------------------

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "OTHQ_OWN"."OSEARCH_MIGRATETELEMETRY_MIGVAL_MIGRATES_VW" ("RUN_TYPE", "ROW_NUMBER", "DURATIONINMINUTES", "PDB_CATEGORY", "DOC_COUNT", "INDEX_", "DBINSTANCE", "DURATIONINSECONDS", "MIGRATIONTASKID", "MIGRATEFILE", "CONTROLSTATE", "NETSUITEPRODUCT", "TIMESTAMP", "MIGRATEVERSION", "DBID", "MIGRATECONTROLFILE", "CONTROLSECTION", "STARTTIME", "MOLECULE", "REGION", "EXCEPTIONMESSAGE", "METADATA_REGION", "METADATA_DOWNLOADED_TIMESTAMP_UTC", "ENDTIME", "EXCEPTIONCLASS", "DBENDSIZEINGB", "EXCEPTIONMESSAGE_HASH", "MIGRATESTATE", "COUNT_SCOUT_ISSUES", "MIGRATEFILESPACKAGEVERSION") DEFAULT COLLATION "USING_NLS_COMP"  AS 
  SELECT
    CASE WHEN ROW_NUMBER() OVER(PARTITION BY t.dbid, t.migrateversion ORDER BY t.TIMESTAMP DESC) = 1 THEN 'Last run' ELSE 'Previous run' END AS run_type,
    t."ROW_NUMBER",t."DURATIONINMINUTES",t."PDB_CATEGORY",t."DOC_COUNT",t."INDEX_",t."DBINSTANCE",t."DURATIONINSECONDS",t."MIGRATIONTASKID",t."MIGRATEFILE",t."CONTROLSTATE",t."NETSUITEPRODUCT",t."TIMESTAMP",t."MIGRATEVERSION",t."DBID",t."MIGRATECONTROLFILE",t."CONTROLSECTION",t."STARTTIME",t."MOLECULE",t."REGION",t."EXCEPTIONMESSAGE",t."METADATA_REGION",t."METADATA_DOWNLOADED_TIMESTAMP_UTC",t."ENDTIME",t."EXCEPTIONCLASS",t."DBENDSIZEINGB",t."EXCEPTIONMESSAGE_HASH",t."MIGRATESTATE",t."MIGRATEFILESPACKAGEVERSION"
    --,count(distinct s.issue_number) over (partition by t.migrateversion, t.dbid, t.migrationtaskid) as COUNT_SCOUT_ISSUES
    , NVL(COUNT_SCOUT_ISSUES, 0) AS COUNT_SCOUT_ISSUES
FROM
    (
        SELECT ROW_NUMBER() OVER(PARTITION BY dbid, migrateversion, migrationtaskid ORDER BY TIMESTAMP DESC) AS row_number,
            durationinseconds / 60 AS durationinminutes,
            CASE WHEN dbinstance LIKE '%.adb.%' THEN 'ATP' ELSE 'EXA' END AS pdb_category,
            m.*
        FROM
            src_osearch_migratetelemetry_migval_tb m
        WHERE
            controlstate IS NOT NULL
    ) t

LEFT JOIN (SELECT migrateversion, dbid_migval, migrationtaskid, COUNT(DISTINCT(CASE WHEN SCOUT_EXCEPTIONMESSAGE <> 'Skipped' THEN ISSUE_NUMBER END)) as COUNT_SCOUT_ISSUES FROM OSEARCH_MIGRATETELEMETRY_MIGVAL_SCOUT_VW GROUP BY migrateversion, dbid_migval, migrationtaskid) s ON t.migrateversion = s.migrateversion and t.dbid = s.dbid_migval and t.migrationtaskid = s.migrationtaskid
WHERE
    row_number = 1
;
