/* **********************************************************
 * Copyright 2020 VMware, Inc.  All rights reserved. -- VMware Confidential
 * **********************************************************/

BEGIN;

/*
 * Holds the master upgrade information. All other table depend on it.
 */
CREATE TABLE NduUpgrade (
  ID SERIAL, --Used to ensure that we can easily find the last Upgrade
  UUID UUID UNIQUE NOT NULL,
  SOURCE_NETWORKING Json,
  SOURCE_HOSTS TEXT[],
  REPLICATION_CONFIG Json,
  QUESTIONS Json,
  NOTIFICATIONS Json,
  PRIMARY KEY (ID)
);

/*
 * Holds information about the UpgradeParameters of the upgrade
 */
CREATE TABLE NduUpgradeParameters (
  UUID UUID REFERENCES NduUpgrade(UUID),
  USERNAME VARCHAR(128) NOT NULL,
  VERSION VARCHAR(128) NOT NULL,
  ANSWERS TEXT,
  PRIMARY KEY (UUID)
);

/*
 * Holds information about where is the B2B upgrade code.
 */
CREATE TABLE NduScriptsLocation (
  UUID UUID REFERENCES NduUpgrade(UUID),
  LocalPath TEXT NOT NULL,
  URL TEXT,
  USERNAME VARCHAR(128),
  PASSWORD VARCHAR(256),
  PRIMARY KEY (UUID)
);

/*
 * Holds information on how to deploy the new target machine
 */
CREATE TABLE NduApplianceDeployment (
  UUID UUID REFERENCES NduUpgrade(UUID),
  NAME VARCHAR(256) NOT NULL,
  SIZE VARCHAR(32) NOT NULL,
  STORAGE_SIZE VARCHAR(32) NOT NULL,
  PASSWORD VARCHAR(256) NOT NULL,
  CEIP bool NOT NULL,
  DISK_MODE bool NOT NULL,
  PRIMARY KEY (UUID)
);

/*
 * Holds information on what should be the network setup for the target machine.
 */
CREATE TABLE NduApplianceDeploymentNetwork (
  UUID UUID REFERENCES NduUpgrade(UUID),
  FAMILY VARCHAR(16) NOT NULL,
  MODE VARCHAR(32) NOT NULL,
  IP VARCHAR(32),
  PREFIX NUMERIC,
  GATEWAY  VARCHAR(32),
  HOSTNAME VARCHAR(256),
  DNS_SERVERS TEXT[],
  PRIMARY KEY (UUID)
);

/*
 * Holds information on where is the ova for the upgrade.
 */
CREATE TABLE NduOvaLocation (
  UUID UUID REFERENCES NduUpgrade(UUID),
  LOCATION TEXT NOT NULL,
  LOCAL_LOCATION TEXT,
  SSL_VERIFY bool NOT NULL,
  THUMBPRINT VARCHAR(256),
  THUMBPRINT_ALGO VARCHAR(128),
  CHECKSUM VARCHAR(256),
  CHECKSUM_ALGO VARCHAR(128),
  PRIMARY KEY (UUID)
);

/*
 * Holds information on the deployed and how to contanct it.
 */
CREATE TABLE NduDeployerConnection (
  UUID UUID REFERENCES NduUpgrade(UUID),
  HOSTNAME VARCHAR(256) NOT NULL,
  USERNAME VARCHAR(128) NOT NULL,
  PASSWORD VARCHAR(256) NOT NULL,
  SSL_VERIFY bool NOT NULL,
  PORT NUMERIC NOT NULL,
  THUMBPRINT VARCHAR(256),
  THUMBPRINT_ALGO VARCHAR(128),
  PRIMARY KEY (UUID)
);

/*
 * Holds information on where in the deployer to deploy the new machine.
 * Note this table can have no entry for the upgrade as there is also the ESX
 * option.
 */
CREATE TABLE NduVcInventory (
  UUID UUID REFERENCES NduUpgrade(UUID),
  VM_FOLDER_PATH TEXT,
  RESOURCE_POOL_PATH TEXT,
  CLUSTER_PATH TEXT,
  HOST_PATH TEXT,
  DATASTORE_NAME VARCHAR(256),
  DATASTORE_CLUSTER_NAME VARCHAR(256),
  NETWORK_NAME VARCHAR(256) NOT NULL,
  PRIMARY KEY (UUID)
);

/*
 * Holds information for how to reach once deployed target machine
 */
CREATE TABLE NduTargetDeployment (
  UUID UUID REFERENCES NduUpgrade(UUID),
  HOSTNAME VARCHAR(256) NOT NULL,
  USERNAME VARCHAR(128) NOT NULL,
  PASSWORD VARCHAR(256) NOT NULL,
  THUMBPRINT VARCHAR(256),
  PRIVATE_KEY TEXT,
  KNOWNHOSTS TEXT,
  PUBLIC_CERT BYTEA,
  PRIMARY KEY (UUID)
);

/*
 * NDU System information about progress of states.
 */
CREATE TABLE NduStateProgress (
  UUID UUID REFERENCES NduUpgrade(UUID),
  IDENTIFIER VARCHAR(32),
  PROGRESS NUMERIC NOT NULL,
  PROGRESS_MESSAGE Json,
  START_TIME timestamp,
  END_TIME timestamp,
  LAST_PROGRESS_UPDATE timestamp NOT NULL,
  ERROR Json,
  PRIMARY KEY (UUID, IDENTIFIER)
);

INSERT INTO UpgradeTracking VALUES(:ndu_id, :ndu_operation);
COMMIT;
