------------------------------- AUTO_INCREMENT------------------------------------
CREATE SEQUENCE AUTO_INCREMENT_PAPER;
CREATE SEQUENCE AUTO_INCREMENT_COMPLICATION;
CREATE SEQUENCE AUTO_INCREMENT_SIDE_EFFECT;
CREATE SEQUENCE AUTO_INCREMENT_BENEFIT;
CREATE SEQUENCE AUTO_INCREMENT_TREATMENT;
CREATE SEQUENCE AUTO_INCREMENT_RISK_FACTOR;
----------------------------------- SCA_USER -------------------------------------
CREATE TABLE SCA_USER
(
USER_NAME VARCHAR(60) NOT NULL,
USER_PASSWORD VARCHAR(15),
CONSTRAINT PK_USER PRIMARY KEY (USER_NAME)
);
------------------------------------ RISK_FACTOR-------------------------------------
CREATE TABLE RISK_FACTOR
(
RISK_ID INTEGER DEFAULT NEXTVAL('AUTO_INCREMENT_RISK_FACTOR') NOT NULL,
RISK_NAME VARCHAR(60) NOT NULL,
RISK_ACRONYM VARCHAR(10),
RISK_DESC VARCHAR(100),
RISK_CURATED_TERM BOOLEAN NOT NULL,
RISK_CURATED_NEW_TERM BOOLEAN NOT NULL,
RISK_CURATOR VARCHAR(60),
RISK_CURATED_DATE TIMESTAMP,
RISK_AUTOMATIC BOOLEAN NOT NULL,
RISK_EXTRACTION_TECHNIQUE VARCHAR(60),
RISK_EXTRACTION_DATE TIMESTAMP,
CONSTRAINT PK_RISK PRIMARY KEY (RISK_ID),
CONSTRAINT U_RISK_NAME UNIQUE (RISK_NAME)
);

CREATE TABLE RISK_FACTOR_VARIATION
(
RISK_ID INTEGER NOT NULL,
RISK_VARIATION VARCHAR(100) NOT NULL,
CONSTRAINT PK_RISK_VARIATION PRIMARY KEY (RISK_ID, RISK_VARIATION),
CONSTRAINT FK_RISK_VARIATION FOREIGN KEY (RISK_ID) 
	REFERENCES RISK_FACTOR (RISK_ID) 
	ON UPDATE CASCADE 
	ON DELETE CASCADE
);
----------------------------------- PAPER ----------------------------------------
CREATE TABLE PAPER
(
PAPER_ID INTEGER DEFAULT NEXTVAL('AUTO_INCREMENT_PAPER') NOT NULL,
TITLE VARCHAR(250) NOT NULL,
JOURNAL VARCHAR(150) NOT NULL,
YEAR CHAR(4) NOT NULL,
AUTHORS VARCHAR(350) NOT NULL,
PDF OID NOT NULL,
HTML OID NOT NULL,
XML OID,
TXT OID,
TOTAL_PATIENTS INTEGER,
SC_PATIENTS INTEGER,
UC_PATIENTS INTEGER,
PAPER_CURATED BOOLEAN NOT NULL,
PAPER_CURATED_NEW BOOLEAN NOT NULL,
PAPER_CURATOR VARCHAR(60),
PAPER_CURATED_DATE TIMESTAMP,
PAPER_AUTOMATIC BOOLEAN NOT NULL,
CONSTRAINT PK_PAPER PRIMARY KEY (PAPER_ID)
);
------------------------------------ COMPLICATION -------------------------------------
CREATE TABLE COMPLICATION
(
COMPLICATION_ID INTEGER DEFAULT NEXTVAL('AUTO_INCREMENT_COMPLICATION') NOT NULL,
COMPLICATION_NAME VARCHAR(60) NOT NULL,
COMPLICATION_ACRONYM VARCHAR(10),
COMPLICATION_DESC VARCHAR(100),
COMPLICATION_CURATED_TERM BOOLEAN NOT NULL,
COMPLICATION_CURATED_NEW_TERM BOOLEAN NOT NULL,
COMPLICATION_CURATOR VARCHAR(60),
COMPLICATION_CURATED_DATE TIMESTAMP,
COMPLICATION_AUTOMATIC BOOLEAN NOT NULL,
COMPLICATION_EXTRACTION_TECHNIQUE VARCHAR(60),
COMPLICATION_EXTRACTION_DATE TIMESTAMP,
CONSTRAINT PK_COMPLICATION PRIMARY KEY (COMPLICATION_ID),
CONSTRAINT U_COMPLICATION_NAME UNIQUE (COMPLICATION_NAME)
);

CREATE TABLE PAPER_COMPLICATION
(
PAPER_ID INTEGER NOT NULL,
COMPLICATION_ID INTEGER NOT NULL,
SECTION VARCHAR(22) NOT NULL,
PAGE INTEGER NOT NULL,
PARAGRAPH INTEGER NOT NULL,
SENTENCE INTEGER NOT NULL,
WHOLE_SENTENCE VARCHAR(560) NOT NULL,
CONCLUDED_PATIENTS INTEGER, 
NONCONCLUDED_PATIENTS INTEGER, 
PATIENTS_NUMBER INTEGER,
CONSTRAINT PK_PAPER_COMPLICATION PRIMARY KEY (PAPER_ID, COMPLICATION_ID, SECTION, PAGE, PARAGRAPH, SENTENCE),
CONSTRAINT FK_COMPLICATION_ID FOREIGN KEY (COMPLICATION_ID) 
	REFERENCES COMPLICATION (COMPLICATION_ID) 
	ON UPDATE CASCADE 
	ON DELETE CASCADE,
CONSTRAINT FK_PAPER_COMPLICATION FOREIGN KEY (PAPER_ID) 
	REFERENCES PAPER (PAPER_ID) 
	ON UPDATE CASCADE 
	ON DELETE CASCADE,
CONSTRAINT CK_PAPER_COMPLICATION_SECTION CHECK (SECTION IN (LOWER('ABSTRACT'), LOWER('RESULTS'), LOWER('DISCUSSION'), LOWER('RESULTS AND DISCUSSION')))
);

CREATE TABLE COMPLICATION_VARIATION
(
COMPLICATION_ID INTEGER NOT NULL,
COMPLICATION_VARIATION VARCHAR(100) NOT NULL,
CONSTRAINT PK_COMPLICATION_VARIATION PRIMARY KEY (COMPLICATION_ID, COMPLICATION_VARIATION),
CONSTRAINT FK_COMPLICATION_VARIATION FOREIGN KEY (COMPLICATION_ID) 
	REFERENCES COMPLICATION (COMPLICATION_ID) 
	ON UPDATE CASCADE 
	ON DELETE CASCADE
);
-------------------------------------- SIDE_EFFECT -----------------------------------------------
CREATE TABLE SIDE_EFFECT
(
SE_ID INTEGER DEFAULT NEXTVAL('AUTO_INCREMENT_SIDE_EFFECT') NOT NULL,
SE_NAME VARCHAR(60) NOT NULL,
SE_ACRONYM VARCHAR(10),
SE_DESC VARCHAR(100),
SE_CURATED_TERM BOOLEAN NOT NULL,
SE_CURATED_NEW_TERM BOOLEAN NOT NULL,
SE_CURATOR VARCHAR(60),
SE_CURATED_DATE TIMESTAMP,
SE_AUTOMATIC BOOLEAN NOT NULL,
SE_EXTRACTION_TECHNIQUE VARCHAR(60),
SE_EXTRACTION_DATE TIMESTAMP,
CONSTRAINT PK_SE PRIMARY KEY (SE_ID),
CONSTRAINT U_SE_NAME UNIQUE (SE_NAME)
);

CREATE TABLE PAPER_SIDE_EFFECT
(
PAPER_ID INTEGER NOT NULL,
SE_ID INTEGER NOT NULL,
SECTION VARCHAR(22) NOT NULL,
PAGE INTEGER NOT NULL,
PARAGRAPH INTEGER NOT NULL,
SENTENCE INTEGER NOT NULL,
WHOLE_SENTENCE VARCHAR(560) NOT NULL,
CONCLUDED_PATIENTS INTEGER, 
NONCONCLUDED_PATIENTS INTEGER, 
PATIENTS_NUMBER INTEGER,
CONSTRAINT PK_PAPER_SIDE_EFFECT PRIMARY KEY (PAPER_ID, SE_ID, SECTION, PAGE, PARAGRAPH, SENTENCE),
CONSTRAINT FK_PAPER_SIDE_EFFECT FOREIGN KEY (PAPER_ID) 
	REFERENCES PAPER (PAPER_ID) 
	ON UPDATE CASCADE 
	ON DELETE CASCADE,
CONSTRAINT FK_SE_ID FOREIGN KEY (SE_ID) 	
	REFERENCES SIDE_EFFECT (SE_ID) 
	ON UPDATE CASCADE 
	ON DELETE CASCADE,
CONSTRAINT CK_PAPER_SIDE_EFFECT_SECTION CHECK (SECTION IN (LOWER('ABSTRACT'), LOWER('RESULTS'), LOWER('DISCUSSION'), LOWER('RESULTS AND DISCUSSION')))
);

CREATE TABLE SIDE_EFFECT_VARIATION
(
SE_ID INTEGER NOT NULL,
SE_VARIATION VARCHAR(100) NOT NULL,
CONSTRAINT PK_SE_VARIATION PRIMARY KEY (SE_ID, SE_VARIATION),
CONSTRAINT FK_SE_VARIATION FOREIGN KEY (SE_ID) 
	REFERENCES SIDE_EFFECT (SE_ID) 
	ON UPDATE CASCADE 
	ON DELETE CASCADE
);
-------------------------------------- BENEFIT -----------------------------------------------
CREATE TABLE BENEFIT
(
BENEFIT_ID INTEGER DEFAULT NEXTVAL('AUTO_INCREMENT_BENEFIT') NOT NULL,
BENEFIT_NAME VARCHAR(80) NOT NULL,
BENEFIT_ACRONYM VARCHAR(10),
BENEFIT_DESC VARCHAR(100),
BENEFIT_CURATED_TERM BOOLEAN NOT NULL,
BENEFIT_CURATED_NEW_TERM BOOLEAN NOT NULL,
BENEFIT_CURATOR VARCHAR(60),
BENEFIT_CURATED_DATE TIMESTAMP,
BENEFIT_AUTOMATIC BOOLEAN NOT NULL,
BENEFIT_EXTRACTION_TECHNIQUE VARCHAR(60),
BENEFIT_EXTRACTION_DATE TIMESTAMP,
CONSTRAINT PK_B PRIMARY KEY (BENEFIT_ID),
CONSTRAINT U_BENEFIT_NAME UNIQUE (BENEFIT_NAME)
);

CREATE TABLE PAPER_BENEFIT
(
PAPER_ID INTEGER NOT NULL,
BENEFIT_ID INTEGER NOT NULL,
SECTION VARCHAR(22) NOT NULL,
PAGE INTEGER NOT NULL,
PARAGRAPH INTEGER NOT NULL,
SENTENCE INTEGER NOT NULL,
WHOLE_SENTENCE VARCHAR(560) NOT NULL,
CONCLUDED_PATIENTS INTEGER, 
NONCONCLUDED_PATIENTS INTEGER, 
PATIENTS_NUMBER INTEGER,
CONSTRAINT PK_PAPER_BENEFIT PRIMARY KEY (PAPER_ID, BENEFIT_ID, SECTION, PAGE, PARAGRAPH, SENTENCE),
CONSTRAINT FK_BENEFIT_ID FOREIGN KEY (BENEFIT_ID) 	
	REFERENCES BENEFIT (BENEFIT_ID) 
	ON UPDATE CASCADE 
	ON DELETE CASCADE,
CONSTRAINT FK_PAPER_BENEFIT FOREIGN KEY (PAPER_ID) 
	REFERENCES PAPER (PAPER_ID) 
	ON UPDATE CASCADE 
	ON DELETE CASCADE,
CONSTRAINT CK_PAPER_BENEFIT_SECTION CHECK (SECTION IN (LOWER('ABSTRACT'), LOWER('RESULTS'), LOWER('DISCUSSION'), LOWER('RESULTS AND DISCUSSION')))
);

CREATE TABLE BENEFIT_VARIATION
(
BENEFIT_ID INTEGER NOT NULL,
BENEFIT_VARIATION VARCHAR(100) NOT NULL,
CONSTRAINT PK_BENEFIT_VARIATION PRIMARY KEY (BENEFIT_ID, BENEFIT_VARIATION),
CONSTRAINT FK_BENEFIT_VARIATION FOREIGN KEY (BENEFIT_ID) 
	REFERENCES BENEFIT (BENEFIT_ID) 
	ON UPDATE CASCADE 
	ON DELETE CASCADE
);
-------------------------------------- TREATMENT -----------------------------------------------
CREATE TABLE TREATMENT
(
TREATMENT_ID INTEGER DEFAULT NEXTVAL('AUTO_INCREMENT_TREATMENT') NOT NULL,
TREATMENT_NAME VARCHAR(60) NOT NULL,
TREATMENT_ACRONYM VARCHAR(10),
TREATMENT_DESC VARCHAR(100),
TREATMENT_TYPE VARCHAR(7) NOT NULL,
TREATMENT_CURATED_TERM BOOLEAN NOT NULL,
TREATMENT_CURATED_NEW_TERM BOOLEAN NOT NULL,
TREATMENT_CURATOR VARCHAR(60),
TREATMENT_CURATED_DATE TIMESTAMP,
TREATMENT_AUTOMATIC BOOLEAN NOT NULL,
TREATMENT_EXTRACTION_TECHNIQUE VARCHAR(60),
TREATMENT_EXTRACTION_DATE TIMESTAMP,
CONSTRAINT PK_TREATMENT PRIMARY KEY (TREATMENT_ID),
CONSTRAINT CK_TREATMENT_TP CHECK (TREATMENT_TYPE IN (LOWER('DRUG'), LOWER('THERAPY'))),
CONSTRAINT U_TREATMENT_NAME UNIQUE (TREATMENT_NAME)
);

CREATE TABLE PAPER_TREATMENT
(
PAPER_ID INTEGER NOT NULL,
TREATMENT_ID INTEGER NOT NULL,
SECTION VARCHAR(22) NOT NULL,
PAGE INTEGER NOT NULL,
PARAGRAPH INTEGER NOT NULL,
SENTENCE INTEGER NOT NULL,
WHOLE_SENTENCE VARCHAR(560) NOT NULL,
CONCLUDED_PATIENTS INTEGER, 
NONCONCLUDED_PATIENTS INTEGER, 
PATIENTS_NUMBER INTEGER,
CONSTRAINT PK_PAPER_TREATMENT PRIMARY KEY (PAPER_ID, TREATMENT_ID, SECTION, PAGE, PARAGRAPH, SENTENCE),
CONSTRAINT FK_TREATMENT_ID FOREIGN KEY (TREATMENT_ID)
	REFERENCES TREATMENT (TREATMENT_ID) 
	ON UPDATE CASCADE 
	ON DELETE CASCADE,
CONSTRAINT FK_PAPER_TREATMENT FOREIGN KEY (PAPER_ID) 
	REFERENCES PAPER (PAPER_ID) 
	ON UPDATE CASCADE 
	ON DELETE CASCADE,
CONSTRAINT CK_PAPER_TREATMENT_SECTION CHECK (SECTION IN (LOWER('ABSTRACT'), LOWER('RESULTS'), LOWER('DISCUSSION'), LOWER('RESULTS AND DISCUSSION')))
);

CREATE TABLE TREATMENT_VARIATION
(
TREATMENT_ID INTEGER NOT NULL,
TREATMENT_VARIATION VARCHAR(100) NOT NULL,
CONSTRAINT PK_TREATMENT_VARIATION PRIMARY KEY (TREATMENT_ID, TREATMENT_VARIATION),
CONSTRAINT FK_TREATMENT_VARIATION FOREIGN KEY (TREATMENT_ID) 
	REFERENCES TREATMENT (TREATMENT_ID) 
	ON UPDATE CASCADE 
	ON DELETE CASCADE
);
--------------------------- RELACIONAMENTOS TERNÁRIOS ----------------------------------
CREATE TABLE PAPER_TREATMENT_SIDE_EFFECT
(
PAPER_ID INTEGER NOT NULL,
TREATMENT_ID INTEGER NOT NULL,
SE_ID INTEGER NOT NULL,
SECTION VARCHAR(22) NOT NULL,
PAGE INTEGER NOT NULL,
PARAGRAPH INTEGER NOT NULL,
SENTENCE INTEGER NOT NULL,
WHOLE_SENTENCE VARCHAR(560) NOT NULL,
CONCLUDED_PATIENTS INTEGER, 
NONCONCLUDED_PATIENTS INTEGER, 
PATIENTS_NUMBER INTEGER,
CONSTRAINT PK_PAPER_TREATMENT_SIDE_EFFECT PRIMARY KEY (PAPER_ID, TREATMENT_ID, SE_ID, SECTION, PAGE, PARAGRAPH, SENTENCE),
CONSTRAINT FK_PAPER_ID FOREIGN KEY (PAPER_ID) 
	REFERENCES PAPER (PAPER_ID) 
	ON UPDATE CASCADE 
	ON DELETE CASCADE,
CONSTRAINT FK_TREATMENT_ID FOREIGN KEY (TREATMENT_ID)
	REFERENCES TREATMENT (TREATMENT_ID) 
	ON UPDATE CASCADE 
	ON DELETE CASCADE,
CONSTRAINT FK_SE_ID FOREIGN KEY (SE_ID) 
	REFERENCES SIDE_EFFECT (SE_ID) 
	ON UPDATE CASCADE 
	ON DELETE CASCADE,
CONSTRAINT CK_PAPER_TREATMENT_SIDE_EFFECT_SECTION CHECK (SECTION IN (LOWER('ABSTRACT'), LOWER('RESULTS'), LOWER('DISCUSSION'), LOWER('RESULTS AND DISCUSSION')))
);

CREATE TABLE PAPER_TREATMENT_BENEFIT
(
PAPER_ID INTEGER NOT NULL,
TREATMENT_ID INTEGER NOT NULL,
BENEFIT_ID INTEGER NOT NULL,
SECTION VARCHAR(22) NOT NULL,
PAGE INTEGER NOT NULL,
PARAGRAPH INTEGER NOT NULL,
SENTENCE INTEGER NOT NULL,
WHOLE_SENTENCE VARCHAR(560) NOT NULL,
CONCLUDED_PATIENTS INTEGER, 
NONCONCLUDED_PATIENTS INTEGER, 
PATIENTS_NUMBER INTEGER,
CONSTRAINT PK_PAPER_TREATMENT_BENEFIT PRIMARY KEY (PAPER_ID, TREATMENT_ID, BENEFIT_ID, SECTION, PAGE, PARAGRAPH, SENTENCE),
CONSTRAINT FK_PAPER_ID FOREIGN KEY (PAPER_ID) 
	REFERENCES PAPER (PAPER_ID) 
	ON UPDATE CASCADE 
	ON DELETE CASCADE,
CONSTRAINT FK_TREATMENT_ID FOREIGN KEY (TREATMENT_ID)
	REFERENCES TREATMENT (TREATMENT_ID) 
	ON UPDATE CASCADE 
	ON DELETE CASCADE,
CONSTRAINT FK_BENEFIT_ID FOREIGN KEY (BENEFIT_ID) 
	REFERENCES BENEFIT (BENEFIT_ID) 
	ON UPDATE CASCADE 
	ON DELETE CASCADE,
CONSTRAINT CK_PAPER_TREATMENT_BENEFIT_SECTION CHECK (SECTION IN (LOWER('ABSTRACT'), LOWER('RESULTS'), LOWER('DISCUSSION'), LOWER('RESULTS AND DISCUSSION')))
);