--CREATE SCHEMA IF NOT EXISTS CDR;
--CREATE SCHEMA IF NOT EXISTS CPR;

create table CDR.DOCUMENTS_FROM_CBS(
    ID int not null,
    DOCUMENT_FROM_CBS_ID varchar(35) not null,
    DOCUMENT_TYPE varchar(30) not null,
    CONTENT blob(150) not null,
    RECEIVED_DATE timestamp,
    DOCUMENT_FROM_CBS_STATUS varchar (15) not null,
    RELATED_DOCUMENT_FROM_CBS_ID varchar(30) not null,
    PRIMARY KEY(ID),
    FOREIGN KEY (RELATED_DOCUMENT_FROM_CBS_ID) REFERENCES CDR.DOCUMENTS_FROM_CBS(DOCUMENT_FROM_CBS_ID)
);

create table CDR.DOCUMENTS_FROM_CBS_HISTORY(
    ID int not null,
    DOCUMENTS_FROM_CBS_ID varchar(35) not null,
    CHANGE_DATE timestamp,
    OLD_DOCUMENT_FROM_CBS_STATUS varchar (15) not null,
    NEW_DOCUMENT_FROM_CBS_STATUS varchar (15) not null,
    PRIMARY KEY(ID),
    FOREIGN KEY (DOCUMENTS_FROM_CBS_ID) REFERENCES CDR.DOCUMENTS_FROM_CBS(DOCUMENT_FROM_CBS_ID)
);

create table CPR.UNSIGNED_MESSAGES_FROM_CBS(
    ID int not null PRIMARY KEY,
    MESSAGE_FROM_CBS_ID varchar(35) not null,
    CONTENT blob(150) not null,
    MESSAGE_FROM_CBS_STATUS varchar (15) not null,
    RECEIVED_DATE timestamp
);

create table CPR.SIGNED_MESSAGES_FROM_CBS(
    ID int not null PRIMARY KEY,
    CONTENT blob(150) not null,
    RECEIVED_DATE timestamp,
    MESSAGE_FROM_CBS_ID varchar(35) not null,
   FOREIGN KEY (MESSAGE_FROM_CBS_ID) REFERENCES CPR.UNSIGNED_MESSAGES_FROM_CBS(MESSAGE_FROM_CBS_ID)
);

create table CDR.DOCUMENTS_FROM_CBS_UNSIGNED_MESSAGES_FROM_CBS(
    ID int not null,
    DOCUMENT_FROM_CBS_ID varchar(35) not null,
    MESSAGE_FROM_CBS_ID varchar(35) not null,
    CHANGE_DATE timestamp,
    DOCUMENT_FROM_CBS_STATUS varchar (15) not null,
    PRIMARY KEY(ID),
    FOREIGN KEY (DOCUMENT_FROM_CBS_ID) REFERENCES CDR.DOCUMENTS_FROM_CBS(DOCUMENT_FROM_CBS_ID),
    FOREIGN KEY (MESSAGE_FROM_CBS_ID) REFERENCES CPR.UNSIGNED_MESSAGES_FROM_CBS(MESSAGE_FROM_CBS_ID)
);





create table CPR.MESSAGES_FROM_CBS_HISTORY(
    ID int not null,
    MESSAGE_FROM_CBS_ID varchar(35) not null,
    OLD_MESSAGE_FROM_CBS_STATUS varchar (15) not null,
    NEW_MESSAGE_FROM_CBS_STATUS varchar (15) not null,
    CHANGE_DATE timestamp,
    SIGNED_MESSAGE_FROM_CBS_ID varchar(35),
    PRIMARY KEY(ID),
    FOREIGN KEY (MESSAGE_FROM_CBS_ID) REFERENCES CPR.UNSIGNED_MESSAGES_FROM_CBS(MESSAGE_FROM_CBS_ID),
    FOREIGN KEY (SIGNED_MESSAGE_FROM_CBS_ID) REFERENCES CPR.SIGNED_MESSAGES_FROM_CBS(ID)
);

create table CPR.MESSAGES_FROM_PAS(
    ID int not null PRIMARY KEY,
    CONTENT blob(150) not null,
    RECEIVED_DATE timestamp,
    MESSAGE_TYPE varchar(30) not null,
    DOCUMENT_FROM_PAS_ID varchar(35),
    DOCUMENT_ID varchar(35),
    MESSAGE_FROM_PAS_STATUS varchar (15) not null,
    MESSAGE_FROM_PAS_ID varchar(35) not null,
    PRIMARY KEY(ID),
    FOREIGN KEY (DOCUMENT_ID) REFERENCES CDR.DOCUMENTS_FROM_CBS(DOCUMENT_FROM_CBS_ID),
);

create table CPR.MESSAGES_FROM_PAS_HISTORY(
    ID int not null,
    MESSAGE_FROM_PAS_ID varchar(35) not null,
    NEW_MESSAGE_FROM_PAS_STATUS varchar (15) not null,
    OLD_MESSAGE_FROM_PAS_STATUS varchar (15) not null,
    CHANGE_DATE timestamp,
    PRIMARY KEY(ID),
    FOREIGN KEY (MESSAGE_FROM_PAS_ID) REFERENCES CPR.MESSAGES_FROM_PAS(MESSAGE_FROM_PAS_ID)
);

create table CDR.DOCUMENTS_FROM_PAS(
    ID int not null PRIMARY KEY,
    CONTENT blob(150) not null,
    DOCUMENT_FROM_PAS_TYPE varchar(30) not null,
    MESSAGE_FROM_PAS_STATUS varchar (15) not null,
    DOCUMENT_FROM_PAS_ID varchar(35),
    RECEIVED_DATE timestamp,
    RELATED_DOCUMENT_FROM_PAS_ID varchar(30) not null,
    PRIMARY KEY(ID),
    FOREIGN KEY (RELATED_DOCUMENT_FROM_PAS_ID) REFERENCES CDR.DOCUMENTS_FROM_PAS(DOCUMENT_FROM_PAS_ID),
    FOREIGN KEY (DOCUMENT_FROM_PAS_ID) REFERENCES CPR.MESSAGES_FROM_PAS(DOCUMENT_FROM_PAS_ID)
);

create table CPR.DOCUMENTS_FROM_PAS_HISTORY(
    ID int not null,
    DOCUMENT_FROM_PAS_ID varchar(35) not null,
    OLD_DOCUMENT_FROM_PAS_STATUS varchar (15) not null,
    NEW_DOCUMENT_FROM_PAS_STATUS varchar (15) not null,
    CHANGE_DATE timestamp,
    PRIMARY KEY(ID),
    FOREIGN KEY (DOCUMENT_FROM_PAS_ID) REFERENCES CDR.DOCUMENTS_FROM_PAS(DOCUMENT_FROM_PAS_ID)
);

create table CDR.RESPONSE_FROM_PAS(
    ID int not null PRIMARY KEY,
    CONTENT blob(150) not null,
    DOCUMENT_FROM_PAS_ID varchar(35) not null,
    DOCUMENT_FROM_CBS_ID varchar(35) not null,
    RESPONSE_STATUS varchar (15) not null,
    PRIMARY KEY(ID),
    FOREIGN KEY (DOCUMENT_FROM_PAS_ID) REFERENCES CDR.DOCUMENTS_FROM_PAS(DOCUMENT_FROM_PAS_ID),
    FOREIGN KEY (DOCUMENT_FROM_CBS_ID) REFERENCES CDR.DOCUMENTS_FROM_CBS(DOCUMENT_FROM_CBS_ID)
);