DROP TABLE IF EXISTS VPX_PREUPGRADE_VERIFICATION;
 /
 
 CREATE TABLE VPX_PREUPGRADE_VERIFICATION (
    INCONSISTENCIE VARCHAR(2500)
 );
 /
 
  DO $$
  DECLARE l_verid int;
  BEGIN
 
  SELECT (VER_ID / 10) * 10
    INTO l_verid
    FROM VPX_VERSION;
 
  INSERT INTO VPX_PREUPGRADE_VERIFICATION
   SELECT 'DB ver:' || CAST(l_verid AS VARCHAR)||
          ' SOURCE:   ' || UPPER(C.TABLE_NAME) || '.' || UPPER(C.COLUMN_NAME) || ', DATA TYPE:' || C.DATA_TYPE || ', DATA SIZE:' || CAST(COALESCE ( C.CHARACTER_MAXIMUM_LENGTH, C.NUMERIC_PRECISION,0) AS VARCHAR) || ', NULLABLE:'|| C.IS_NULLABLE  || ', DEFAULT:' || COALESCE(C.COLUMN_DEFAULT,'NONE') ||
          ' ====> EXPECTED: ' || M.TABLE_NAME ||'.' || M.COLUMN_NAME || ', DATA TYPE:' || M.DATA_TYPE || ', DATA SIZE:' || CAST(COALESCE(M.DATA_SIZE,0) AS VARCHAR)  || ', NULLABLE:' || M.IS_NULLABLE || ', DEFAULT:' || COALESCE(M.COLUMN_DEFAULT,'NONE')
    FROM information_schema.columns  c
    LEFT JOIN MGRD_EXP_TABLES_COLUMNS m
       ON UPPER(C.TABLE_NAME) = M.TABLE_NAME
          AND UPPER (C.COLUMN_NAME) = M.COLUMN_NAME
    WHERE UPPER(C.TABLE_NAME) LIKE 'VPX%'
    AND UPPER(c.TABLE_NAME) NOT IN ('VPX_HIST_STAT1','VPX_HIST_STAT2','VPX_HIST_STAT3','VPX_HIST_STAT4','VPX_PREUPGRADE_VERIFICATION')
    AND M.TYPE = 'T'
    AND ( C.DATA_TYPE <> M.DATA_TYPE
          OR COALESCE ( C.CHARACTER_MAXIMUM_LENGTH, C.NUMERIC_PRECISION,-1) <> COALESCE(M.DATA_SIZE,-1)
          OR C.IS_NULLABLE <> M.IS_NULLABLE
          OR COALESCE(C.COLUMN_DEFAULT,'') <> COALESCE(M.COLUMN_DEFAULT,'')
  	);
  END$$;
 /
 
 DO $$
 DECLARE err_message text;
 DECLARE err_message_full text;
 DECLARE l_verid int;
 BEGIN
 
 RAISE NOTICE 'Verifying DB schema ...';
 
 
 
 SELECT (VER_ID / 10) * 10
   INTO l_verid
   FROM VPX_VERSION;
 
 
 err_message := null;
 SELECT ARRAY_TO_STRING(ARRAY
    (   SELECT M.TABLE_NAME || '.' || M.COLUMN_NAME
          FROM
             (SELECT *
                FROM INFORMATION_SCHEMA.COLUMNS C1
               WHERE C1.TABLE_SCHEMA IN ('vc','vpx')
              ) c
             RIGHT JOIN MGRD_EXP_TABLES_COLUMNS M
                ON UPPER(C.TABLE_NAME) = UPPER(M.TABLE_NAME)
               AND UPPER(C.COLUMN_NAME) = UPPER(M.COLUMN_NAME)
             WHERE C.TABLE_NAME IS NULL
               AND M.TYPE = 'T'
               AND UPPER(M.TABLE_NAME) <> (CASE WHEN l_verid = 550 THEN 'VPX_SAMPLE_TIME3' ELSE 'VPX_X' END)
               AND UPPER(M.COLUMN_NAME) <> (CASE WHEN l_verid = 550 THEN 'PARTITION_INDEX_HS4' ELSE 'VPX_X' END)
             ORDER BY C.TABLE_NAME,C.COLUMN_NAME
             LIMIT 100
    ),';')
 INTO err_message;
 
 IF char_length(COALESCE (err_message,'')) > 0 THEN
    err_message_full:= 'Missing tables or columns: ' || err_message || ';';
 END IF;
 
 
 
 err_message := null;
 SELECT ARRAY_TO_STRING(ARRAY
    (   SELECT M.TABLE_NAME || '.' || M.COLUMN_NAME
          FROM
             ( SELECT C2.RELNAME TABLE_NAME,
                      CASE
                         WHEN l_verid = 550
                            THEN
                               CASE
                                  WHEN UPPER(C1.RELNAME) LIKE 'IDX_VPX_HIST_STAT%'
                                     THEN UPPER( REPLACE (UPPER(C1.RELNAME),'IDX_VPX_HIST_STAT','IX_VPX_HIST_STAT'))
                                  WHEN UPPER(C1.RELNAME)='VPXI_ENTITY_TYPE_ID'
                                     THEN 'IX_VPX_ENTITY_TYPE_ID'
                                  ELSE UPPER(C1.RELNAME)
                               END
                         WHEN l_verid = 650
                            THEN
                               CASE
                                  WHEN UPPER(C1.RELNAME) = 'VPXI_EVENT_USERNAME'
                                     THEN UPPER( REPLACE (UPPER(C1.RELNAME),'VPXI_EVENT_USERNAME','VPXI_EVENT_USERNAME_1'))
                                  ELSE UPPER(C1.RELNAME)
                               END
                      ELSE UPPER(C1.RELNAME)
                      END AS INDEX_NAME
                 FROM PG_CLASS C1
                 JOIN PG_INDEX I
                   ON I.INDEXRELID = C1.OID
                 JOIN PG_CLASS C2
                   ON I.INDRELID = C2.OID
                 LEFT JOIN PG_NAMESPACE N
                   ON N.OID = C1.RELNAMESPACE
                WHERE C1.RELKIND IN ('i','')
                  AND N.NSPNAME IN ('vpx','vc')
                  AND PG_TABLE_IS_VISIBLE(C1.OID)
              ) C
             RIGHT JOIN MGRD_EXP_TABLES_COLUMNS M
                ON UPPER(C.TABLE_NAME) = UPPER(M.TABLE_NAME)
               AND UPPER(C.INDEX_NAME) = UPPER(M.COLUMN_NAME)
             WHERE C.TABLE_NAME IS NULL
               AND M.TYPE = 'I'
             ORDER BY C.TABLE_NAME,C.INDEX_NAME
             LIMIT 100
    ),';')
 INTO err_message;
 
 IF char_length(COALESCE(err_message,'')) > 0 THEN
    err_message_full:=COALESCE(err_message_full,'') || ' Missing indexes: ' || err_message || ';';
 END IF;
 
 
 err_message := null;
 SELECT ARRAY_TO_STRING(ARRAY
    (   SELECT M.TABLE_NAME || '.' || M.COLUMN_NAME
          FROM
             ( SELECT C1.RELNAME TABLE_NAME, CON.CONNAME CONSTRAINT_NAME
                 FROM PG_CONSTRAINT CON
                 JOIN PG_CLASS C1
                   ON CON.CONRELID = C1.OID
                 JOIN PG_NAMESPACE N
                   ON N.OID = C1.RELNAMESPACE
                WHERE N.NSPNAME IN ('vpx', 'vc')
              ) c
             RIGHT JOIN MGRD_EXP_TABLES_COLUMNS M
                ON UPPER(C.TABLE_NAME) = UPPER(M.TABLE_NAME)
               AND UPPER(C.CONSTRAINT_NAME) = UPPER(M.COLUMN_NAME)
             WHERE C.TABLE_NAME IS NULL
               AND M.TYPE = 'C'
             ORDER BY C.TABLE_NAME,C.CONSTRAINT_NAME
             LIMIT 100
    ),';')
 INTO err_message;
 
 IF char_length(COALESCE(err_message,'')) > 0 THEN
    err_message_full:=COALESCE(err_message_full,'') || ' Missing constraints: ' || err_message || ';';
 END IF;
 
 
 err_message := null;
 SELECT ARRAY_TO_STRING(ARRAY
    (   SELECT M.COLUMN_NAME
          FROM
             ( SELECT RELNAME SEQUENCE_NAME
                 FROM PG_CLASS
                WHERE RELKIND = 'S'
              ) C
             RIGHT JOIN MGRD_EXP_TABLES_COLUMNS M
                ON UPPER(C.SEQUENCE_NAME) = UPPER(M.COLUMN_NAME)
             WHERE C.SEQUENCE_NAME IS NULL
               AND M.TYPE='S'
             ORDER BY C.SEQUENCE_NAME
             LIMIT 100
    ),';')
 INTO err_message;
 
 IF char_length(COALESCE(err_message,'')) > 0 THEN
    err_message_full:=COALESCE(err_message_full,'') || ' Missing sequences: ' || err_message || ';';
 END IF;
 
 
 
 err_message:=null;
 SELECT ARRAY_TO_STRING(ARRAY
    (   SELECT C.TABLE_NAME || '.' || C.COLUMN_NAME
          FROM
             (SELECT C1.TABLE_NAME , C1.COLUMN_NAME
                FROM INFORMATION_SCHEMA.COLUMNS C1
                JOIN INFORMATION_SCHEMA.TABLES T
                  ON C1.TABLE_NAME = T.TABLE_NAME
               WHERE T.TABLE_SCHEMA IN ('vc', 'vpx')
                 AND TABLE_TYPE = 'BASE TABLE'
                 AND UPPER(C1.TABLE_NAME) LIKE 'VPX%'
 		AND UPPER(C1.TABLE_NAME) NOT IN ('VPX_PREUPGRADE_VERIFICATION', 'VPX_POPULATED_COUNTERS')
              ) c
             LEFT JOIN MGRD_EXP_TABLES_COLUMNS M
               ON UPPER(C.TABLE_NAME) = UPPER(M.TABLE_NAME)
              AND UPPER(C.COLUMN_NAME) = UPPER(M.COLUMN_NAME)
            WHERE M.TABLE_NAME IS NULL
             ORDER BY C.TABLE_NAME,C.COLUMN_NAME
             LIMIT 100
    ),';')
 INTO err_message;
 
 IF char_length(COALESCE(err_message,'')) > 0 THEN
    err_message_full:= COALESCE(err_message_full,'') || ' Extra tables or columns: ' || err_message || ';';
 END IF;
 
 
 err_message := null;
 SELECT ARRAY_TO_STRING(ARRAY
    (   SELECT C.TABLE_NAME || '.' || C.INDEX_NAME
          FROM
             ( SELECT C2.RELNAME TABLE_NAME,
                      CASE
                         WHEN l_verid = 550
                            THEN
                               CASE
                                  WHEN UPPER(CS.RELNAME) LIKE 'IDX_VPX_HIST_STAT%'
                                     THEN UPPER(REPLACE(UPPER(CS.RELNAME),'IDX_VPX_HIST_STAT','IX_VPX_HIST_STAT'))
                                  WHEN UPPER(CS.RELNAME)='VPXI_ENTITY_TYPE_ID'
                                     THEN 'IX_VPX_ENTITY_TYPE_ID'
                                  ELSE UPPER(CS.RELNAME)
                               END
                         WHEN l_verid = 650
                            THEN
                               CASE
                                  WHEN UPPER(CS.RELNAME) = 'VPXI_EVENT_USERNAME'
                                     THEN UPPER( REPLACE (UPPER(CS.RELNAME),'VPXI_EVENT_USERNAME','VPXI_EVENT_USERNAME_1'))
                                  ELSE UPPER(CS.RELNAME)
                               END
                      ELSE UPPER(CS.RELNAME)
 	                  END AS INDEX_NAME
                 FROM PG_CLASS CS
                 JOIN PG_INDEX I
                   ON I.INDEXRELID = CS.OID
                 JOIN PG_CLASS C2
                   ON I.INDRELID = C2.OID
                 LEFT JOIN PG_USER U
                   ON U.USESYSID = CS.RELOWNER
                 LEFT JOIN PG_NAMESPACE N
                   ON N.OID = CS.RELNAMESPACE
                WHERE CS.RELKIND IN ('i','')
                  AND N.NSPNAME IN ('vc','vpx')
                  AND PG_TABLE_IS_VISIBLE(CS.OID)
                  AND UPPER(C2.RELNAME) LIKE 'VPX%'
              ) C
             LEFT JOIN MGRD_EXP_TABLES_COLUMNS M
               ON UPPER(C.TABLE_NAME) = UPPER(M.TABLE_NAME)
              AND UPPER(C.INDEX_NAME) = UPPER(M.COLUMN_NAME)
            WHERE M.TABLE_NAME IS NULL
            ORDER BY C.TABLE_NAME,C.INDEX_NAME
            LIMIT 100
    ),';')
 INTO err_message;
 
 IF char_length(COALESCE(err_message,'')) > 0 THEN
    err_message_full:= COALESCE(err_message_full,'') || ' Extra indexes: ' || err_message || ';';
 END IF;
 
 
 err_message:=null;
 SELECT ARRAY_TO_STRING(ARRAY
    (   SELECT C.TABLE_NAME || '.' || C.CONSTRAINT_NAME
          FROM
             ( SELECT C1.RELNAME TABLE_NAME, CON.CONNAME CONSTRAINT_NAME
                 FROM PG_CONSTRAINT CON
                 JOIN PG_CLASS C1
                   ON CON.CONRELID = C1.OID
                 JOIN PG_NAMESPACE N
                   ON N.OID = C1.RELNAMESPACE
                WHERE N.NSPNAME IN ('vc', 'vpx')
 			   AND UPPER(C1.RELNAME) LIKE 'VPX%'
 		       AND UPPER(CONNAME) <> CASE WHEN l_verid = 550 THEN 'FK_VPX_STAT_DEF_TOPN_STATS' ELSE 'VPX_X' END
              ) c
             LEFT JOIN MGRD_EXP_TABLES_COLUMNS M
               ON UPPER(C.TABLE_NAME) = UPPER(M.TABLE_NAME)
              AND UPPER(C.CONSTRAINT_NAME) = UPPER(M.COLUMN_NAME)
            WHERE M.TABLE_NAME IS NULL
            ORDER BY C.TABLE_NAME,C.CONSTRAINT_NAME
            LIMIT 100
    ),';')
 INTO err_message;
 
 IF char_length(COALESCE(err_message,'')) > 0 THEN
    err_message_full:= COALESCE(err_message_full,'') || ' Extra constraints: ' || err_message || ';';
 END IF;
 
 
 err_message := null;
 SELECT ARRAY_TO_STRING(ARRAY
    (   SELECT C.SEQUENCE_NAME
          FROM
             ( SELECT RELNAME SEQUENCE_NAME
                 FROM PG_CLASS
                WHERE RELKIND = 'S'
              ) C
             LEFT JOIN MGRD_EXP_TABLES_COLUMNS M
               ON UPPER(C.SEQUENCE_NAME) = UPPER(M.COLUMN_NAME)
            WHERE M.TABLE_NAME IS NULL
 		   AND UPPER(C.SEQUENCE_NAME) LIKE 'VPX%'
            ORDER BY C.SEQUENCE_NAME
            LIMIT 100
    ),';')
 INTO err_message;
 
 
 IF char_length(COALESCE(err_message,'')) > 0 THEN
   err_message_full:= COALESCE(err_message_full,'') || ' Extra sequences: ' || err_message || ';';
 END IF;
 
 
 err_message := null;
 SELECT ARRAY_TO_STRING(ARRAY
    (   SELECT c.relname
        FROM pg_class c, pg_user u
        WHERE c.relowner = u.usesysid and c.relkind = 'S'
       AND UPPER(c.relname) like 'VPX%'
       AND u.usename <> 'vc'
            LIMIT 100
    ),';')
 INTO err_message;
 
 IF char_length(COALESCE(err_message,'')) > 0 THEN
   err_message_full:= COALESCE(err_message_full,'') || ' Sequences owner: ' || err_message || ';';
 END IF;
 
 
 DROP TABLE IF EXISTS MGRD_EXP_TABLES_COLUMNS;
 
 IF char_length(COALESCE(err_message_full,'')) > 0 THEN
    RAISE EXCEPTION 'ERROR !%',err_message_full;
 END IF;
 
 END$$;
 /
