CREATE TYPE ARCHIVE_JOB_STATUS AS ENUM (
  'AJS_ToTransferForUser',
  'AJS_ToReportToUserForTransfer',
  'AJS_Complete',
  'AJS_ToReportToUserForFailure',
  'AJS_Failed',
  'AJS_Abandoned',
  'AJS_ToTransferForRepack',
  'AJS_ToReportToRepackForFailure' );
CREATE TYPE RETRIEVE_JOB_STATUS AS ENUM (
  'RJS_ToTransfer',
  'RJS_ToReportToUserForFailure',
  'RJS_Failed',
  'RJS_ToReportToRepackForSuccess',
  'RJS_ToReportToRepackForFailure' );
CREATE TYPE REPACK_REQ_STATUS AS ENUM (
  'RRS_Pending',
  'RRS_ToExpand',
  'RRS_Starting',
  'RRS_Running',
  'RRS_Complete',
  'RRS_Failed' );
CREATE TABLE CTA_SCHEDULER(
  SCHEMA_VERSION_MAJOR    NUMERIC(20, 0)	 CONSTRAINT CTA_SCHEDULER_SVM1_NN NOT NULL,
  SCHEMA_VERSION_MINOR    NUMERIC(20, 0)	 CONSTRAINT CTA_SCHEDULER_SVM2_NN NOT NULL,
  NEXT_SCHEMA_VERSION_MAJOR NUMERIC(20, 0),
  NEXT_SCHEMA_VERSION_MINOR NUMERIC(20, 0),
  STATUS                  VARCHAR(100),
  IS_PRODUCTION           CHAR(1)         DEFAULT '0' CONSTRAINT CTA_SCHEDULER_IP_NN NOT NULL,
  CONSTRAINT CTA_SCHEDULER_IP_BOOL_CK     CHECK(IS_PRODUCTION IN ('0','1'))
);
CREATE TABLE ARCHIVE_JOB_QUEUE(
  JOB_ID BIGSERIAL,
  ARCHIVE_REQID BIGSERIAL,
  MOUNT_ID BIGINT,
  STATUS ARCHIVE_JOB_STATUS CONSTRAINT AJQ_S_NN NOT NULL,
  TAPE_POOL VARCHAR(100) CONSTRAINT AJQ_TPN_NN NOT NULL,
  MOUNT_POLICY VARCHAR(100) CONSTRAINT AJQ_MPN_NN NOT NULL,
  PRIORITY SMALLINT CONSTRAINT AJQ_MPAP_NN NOT NULL,
  MIN_ARCHIVE_REQUEST_AGE INTEGER CONSTRAINT AJQ_MPAMR_NN NOT NULL,
  ARCHIVE_FILE_ID BIGINT,
  SIZE_IN_BYTES BIGINT,
  COPY_NB NUMERIC(3, 0),
  START_TIME BIGINT,
  CHECKSUMBLOB BYTEA,
  CREATION_TIME BIGINT,
  DISK_INSTANCE VARCHAR(100),
  DISK_FILE_ID VARCHAR(100),
  DISK_FILE_OWNER_UID INTEGER,
  DISK_FILE_GID INTEGER,
  DISK_FILE_PATH VARCHAR(2000),
  ARCHIVE_REPORT_URL VARCHAR(2000),
  ARCHIVE_ERROR_REPORT_URL VARCHAR(2000),
  REQUESTER_NAME VARCHAR(100),
  REQUESTER_GROUP VARCHAR(100),
  SRC_URL VARCHAR(2000),
  STORAGE_CLASS VARCHAR(100),
  RETRIES_WITHIN_MOUNT SMALLINT,
  TOTAL_RETRIES SMALLINT,
  LAST_MOUNT_WITH_FAILURE BIGINT,
  MAX_TOTAL_RETRIES SMALLINT,
  MAX_RETRIES_WITHIN_MOUNT SMALLINT,
  MAX_REPORT_RETRIES SMALLINT,
  TOTAL_REPORT_RETRIES SMALLINT,
  ARCHIVE_JOBFAILLOGS_PB BYTEA,
  IS_REPACK CHAR(1),
  REPACK_REQID BIGINT,
  REPACK_FILEBUF_URL VARCHAR(2000),
  REPACK_FSEQ NUMERIC(20, 0),
  REPACK_DEST_VID VARCHAR(20),
  IS_REPORTDECIDED CHAR(1),
  VID VARCHAR(20)
);
CREATE TABLE RETRIEVE_JOB_QUEUE(
  JOB_ID BIGSERIAL,
  RETRIEVE_REQID BIGSERIAL,
  MOUNT_ID BIGINT,
  STATUS RETRIEVE_JOB_STATUS CONSTRAINT RJQ_S_NN NOT NULL,

/* From RetrieveJobPointer/RetrieveQueue */
  VID VARCHAR(20),
  ACTIVE_COPY_NB NUMERIC(3, 0),
  PRIORITY SMALLINT,
  RETRIEVE_MIN_REQ_AGE BIGINT,
  STARTTIME BIGINT,
  ACTIVITY VARCHAR(100),

/* from RetrieveRequest : RetrieveJob */
  RETRIEVEJOB_PB BYTEA,

/* From RetrieveRequest->SchedulerRetrieveRequest->RequesterIdentity */
  REQUESTER_NAME VARCHAR(100),
  REQUESTER_GROUP VARCHAR(100),

/* From RetrieveRequest->SchedulerRetrieveRequest */
  ARCHIVE_FILE_ID BIGINT,
  DST_URL VARCHAR(2000),

/* From RetrieveRequest->SchedulerRetrieveRequest->DiskFileInfo */
  DISK_FILE_OWNER_UID INTEGER,
  DISK_FILE_GID INTEGER,
  DISK_FILE_PATH VARCHAR(2000),

/* From RetrieveRequest->SchedulerRetrieveRequest->EntryLog */
  SRR_USERNAME VARCHAR(100),
  SRR_HOST VARCHAR(100),
  SRR_TIME BIGINT,

/* From RetrieveRequest->SchedulerRetrieveRequest */
  RETRIEVE_ERROR_REPORT_URL VARCHAR(2000),
  IS_VERIFY CHAR(1),

/* From RetrieveRequest->MountPolicy */
  MOUNT_POLICY VARCHAR(100),

/* From RetrieveRequest */
  SRR_MOUNT_POLICY VARCHAR(100),
  SRR_ACTIVITY VARCHAR(100),

/* RetrieveRequest ArchiveFile */
  /* Use Archive_File_Id from SchedulerRetrieveRequest */
  SIZE_IN_BYTES BIGINT,
  DISK_FILE_ID VARCHAR(100),
  DISK_INSTANCE VARCHAR(100),
  /* Use DiskFileInfo from RetrieveRequest->SchedulerRetrieveRequest->DiskFileInfo */
  CHECKSUMBLOB BYTEA,
  CREATION_TIME BIGINT,
  STORAGE_CLASS VARCHAR(100),

  FAILURE_REPORT_URL VARCHAR(2000),
  FAILURE_REPORT_LOG VARCHAR(2000),

  IS_REPACK CHAR(1),
  REPACK_REQID BIGINT,

/* From RetrieveRequest->RetrieveRequestRepackInfo */
  RR_REPACKINFO_PB BYTEA,

/* From RetrieveRequest->LifeCycleTimings */
  LT_CREATE BIGINT,
  LT_FIRST_SELECTED BIGINT,
  LT_COMPLETED BIGINT,

/* From RetrieveRequest */
  DISK_SYSTEM_NAME VARCHAR(100),

/* User is_verify from schedulerretrieverequest */
  IS_FAILED CHAR(1)
);
CREATE TABLE REPACK_JOB_QUEUE(
  REPACK_REQID BIGSERIAL,
  VID VARCHAR(20),
  BUFFER_URL VARCHAR(2000),
  STATUS REPACK_REQ_STATUS,
  IS_ADD_COPIES CHAR(1),
  IS_MOVE CHAR(1),
  TOTAL_FILES_TO_RETRIEVE BIGINT,
  TOTAL_BYTES_TO_RETRIEVE BIGINT,
  TOTAL_FILES_TO_ARCHIVE BIGINT,
  TOTAL_BYTES_TO_ARCHIVE BIGINT,
  USER_PROVIDED_FILES BIGINT,
  USER_PROVIDED_BYTES BIGINT,
  RETRIEVED_FILES BIGINT,
  RETRIEVED_BYTES BIGINT,
  ARCHIVED_FILES BIGINT,
  ARCHIVED_BYTES BIGINT,
  FAILED_TO_RETRIEVE_FILES BIGINT,
  FAILED_TO_RETRIEVE_BYTES BIGINT,
  FAILED_TO_CREATE_ARCHIVE_REQ BIGINT,
  FAILED_TO_ARCHIVE_FILES BIGINT,
  FAILED_TO_ARCHIVE_BYTES BIGINT,
  LAST_EXPANDED_FSEQ BIGINT,
  IS_EXPAND_FINISHED CHAR(1),
  IS_EXPAND_STARTED  CHAR(1),
  MOUNT_POLICY VARCHAR(100),
  IS_COMPLETE CHAR(1),
  IS_NO_RECALL CHAR(1),
  SUBREQ_PB BYTEA,
  DESTINFO_PB BYTEA,
  CREATE_USERNAME VARCHAR(100),
  CREATE_HOST VARCHAR(100),
  CREATE_TIME BIGINT,
  REPACK_FINISHED_TIME BIGINT
);
CREATE TABLE TAPE_MOUNTS(
  MOUNT_ID BIGSERIAL,
  CREATION_TIME TIMESTAMPTZ DEFAULT NOW(),
  OWNER VARCHAR(100)
);
CREATE VIEW ARCHIVE_JOB_SUMMARY(
  MOUNT_ID,
  STATUS,
  TAPE_POOL,
  MOUNT_POLICY,
  JOBS_COUNT,
  JOBS_TOTAL_SIZE,
  OLDEST_JOB_START_TIME,
  ARCHIVE_PRIORITY,
  ARCHIVE_MIN_REQUEST_AGE) AS SELECT
    MOUNT_ID,
    STATUS,
    TAPE_POOL,
    MOUNT_POLICY,
    COUNT(*),
    SUM(SIZE_IN_BYTES),
    MIN(START_TIME),
    MAX(PRIORITY),
    MIN(MIN_ARCHIVE_REQUEST_AGE)
  FROM ARCHIVE_JOB_QUEUE GROUP BY
    MOUNT_ID,
    STATUS,
    TAPE_POOL,
    MOUNT_POLICY;
CREATE VIEW RETRIEVE_JOB_SUMMARY(
  MOUNT_ID,
  STATUS,
  VID,
  MOUNT_POLICY,
  JOBS_COUNT,
  JOBS_TOTAL_SIZE,
  OLDEST_JOB_START_TIME,
  RETRIEVE_PRIORITY,
  RETRIEVE_MIN_REQUEST_AGE) AS SELECT
    MOUNT_ID,
    STATUS,
    VID,
    MOUNT_POLICY,
    COUNT(*),
    SUM(SIZE_IN_BYTES),
    MIN(START_TIME),
    MAX(PRIORITY),
    MIN(MIN_ARCHIVE_REQUEST_AGE)
  FROM ARCHIVE_JOB_QUEUE GROUP BY
    MOUNT_ID,
    STATUS,
    VID,
    MOUNT_POLICY;