/* **************************************************************************** *
 * Copyright (c) 2021 VMware, Inc.  All rights reserved. -- VMware Confidential *
 * **************************************************************************** */

/*
 * This script is for applying the database and schema expand for vstats_coredb present in VCDB
 */

CREATE OR REPLACE FUNCTION raise_exception(text)
RETURNS void AS $$
BEGIN
  RAISE EXCEPTION '%', $1;
END;
$$ LANGUAGE plpgsql;

SELECT COUNT(*)=0 as not_vcdb, 'vstats_coredb' as dbname, 'vstats_coredb_schema' as db_schema  FROM pg_database WHERE datname = 'VCDB'
\gset
\if :not_vcdb
   \echo 'vstats_coredb is not present.'
   SELECT raise_exception('vstats_coredb is not present.');
\else
   \echo 'vstats_coredb is present'
   \set dbname 'VCDB'
   \connect :dbname

   SET SEARCH_PATH = :db_schema;

   /*========================================================================*/
   /*  Rename TYPE vstats_status to acq_spec_status & Add new value INVALID  */
   /*========================================================================*/

   SELECT vc.vcdb_ndu_cln_processor(
   $$INSERT INTO vc.VPX_VCDB_NDU_EXP (CLN_ID, RELEASE, CLN_STATUS) VALUES (49,700,'') ON CONFLICT (CLN_ID) DO NOTHING;$$,
   $func$
   DO $$
   BEGIN
      IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'acq_spec_status') THEN
         CREATE TYPE acq_spec_status AS ENUM ('ENABLED', 'DISABLED', 'INVALID'); -- Placeholder for future extension
      END IF;
   END $$;
   $func$
   );

   /*========================================================================*/
   /*  Create Endpoints & reset id_sequence to 101                           */
   /*  & related cross-table acq_spec_X_endpoint & its indexes               */
   /*========================================================================*/

   SELECT vc.vcdb_ndu_cln_processor(
   $$INSERT INTO vc.VPX_VCDB_NDU_EXP (CLN_ID, RELEASE, CLN_STATUS) VALUES (50,700,'') ON CONFLICT (CLN_ID) DO NOTHING;$$,
   $$CALL vc.vcdb_ndu_add_table(50,'vstats', 700,
   'CREATE TABLE IF NOT EXISTS vstats_coredb_schema.endpoints (
      id BIGSERIAL PRIMARY KEY,
      config_spec JSONB NOT NULL,
      userid TEXT NOT NULL,
      UNIQUE(config_spec, userid)
      );'
   );$$,
   $func$
   DO
   $$
   DECLARE l_cur_val INT;
   BEGIN
      SELECT LAST_VALUE INTO l_cur_val from endpoints_id_seq;
      IF l_cur_val < 101
      THEN
         EXECUTE format('ALTER SEQUENCE endpoints_id_seq START WITH %s RESTART', 101);
      END IF;
   END;
   $$;
   $func$,
   $$CALL vc.vcdb_ndu_add_table(50,'vstats', 700,
   'CREATE TABLE IF NOT EXISTS vstats_coredb_schema.acq_spec_X_endpoint(
      acq_spec_id BIGSERIAL,
      endpoint_id BIGSERIAL REFERENCES vstats_coredb_schema.endpoints(id) ON DELETE CASCADE NOT NULL
      );'
   );$$,
   $$CALL vc.vcdb_ndu_crt_idx(50,'vstats', 700, 'vstats_coredb_schema', 'idx_acq_spec_X_endpoint_acq_spec_id', 'acq_spec_X_endpoint', 'acq_spec_id');$$,
   $$CALL vc.vcdb_ndu_crt_idx(50,'vstats', 700, 'vstats_coredb_schema', 'idx_acq_spec_X_endpoint_endpoint_id', 'acq_spec_X_endpoint', 'endpoint_id');$$
   );

   /*========================================================================*/
   /*  Add new column userid, column status_reason to acq_spec table         */
   /*  & rename column vs_status to status & vs_status_ts to status_ts       */
   /*========================================================================*/

   SELECT vc.vcdb_ndu_cln_processor(
   $$INSERT INTO vc.VPX_VCDB_NDU_EXP (CLN_ID, RELEASE, CLN_STATUS) VALUES (51,700,'') ON CONFLICT (CLN_ID) DO NOTHING;$$,
   $$CALL vc.vcdb_ndu_add_column(51,'vstats', 700, 'vstats_coredb_schema', 'acq_spec', 'userid', 'TEXT', 'NOT NULL DEFAULT ''''');$$,
   $$CALL vc.vcdb_ndu_add_column(51,'vstats', 700, 'vstats_coredb_schema', 'acq_spec', 'status_reason', 'TEXT');$$,
   $$CALL vc.vcdb_ndu_rename_col(51,'vstats', 700, 'vstats_coredb_schema', 'acq_spec', 'vs_status', 'status');$$,
   $$CALL vc.vcdb_ndu_rename_col(51,'vstats', 700, 'vstats_coredb_schema', 'acq_spec', 'vs_status_ts', 'status_ts');$$
   );

   COMMIT;
\endif