ALTER TABLE PROJECT_USERS DROP CONSTRAINT FK_PROJECT_USERS_PROJECTS
;
ALTER TABLE PROJECT_USERS DROP CONSTRAINT FK_PUSR_USERS
;
ALTER TABLE REQUIREMENT_SETS DROP CONSTRAINT FK_RQS_PROJECT
;
ALTER TABLE STEPS DROP CONSTRAINT FK_ST_TESTCASE
;
ALTER TABLE TESTCASE_REQUREMENTS DROP CONSTRAINT FK_TRQ_TESTCASE
;
ALTER TABLE TESTCASE_REQUREMENTS DROP CONSTRAINT FK_TRQ_REQUIREMENT
;
ALTER TABLE REQUIREMENTS DROP CONSTRAINT FK_RQ_ASSIGNEE
;
ALTER TABLE REQUIREMENTS DROP CONSTRAINT FK_RQ_AUTHOR
;
ALTER TABLE REQUIREMENTS DROP CONSTRAINT FK_RQ_REQUIREMENT_SET
;
ALTER TABLE TESTCASES DROP CONSTRAINT FK_TC_AUTHOR
;
ALTER TABLE TESTCASES DROP CONSTRAINT FK_TC_BLOCKED_BY
;
ALTER TABLE TESTCASES DROP CONSTRAINT FK_TC_TESTCASE_SET
;
ALTER TABLE USERS DROP CONSTRAINT FK_USR_GROUP
;
ALTER TABLE TESTCASE_SETS DROP CONSTRAINT FK_TCS_PROJECT
;

DROP TABLE PROJECT_USERS
;
DROP TABLE REQUIREMENT_SETS
;
DROP TABLE PROJECTS
;
DROP TABLE GROUPS
;
DROP TABLE STEPS
;
DROP TABLE TESTCASE_REQUREMENTS
;
DROP TABLE REQUIREMENTS
;
DROP TABLE TESTCASES
;
DROP TABLE USERS
;
DROP TABLE TESTCASE_SETS
;

CREATE TABLE PROJECT_USERS ( 
	PUSR_USER integer NOT NULL,
	PUSR_PROJECT integer NOT NULL
)
;

CREATE TABLE REQUIREMENT_SETS ( 
	RQS_ID integer NOT NULL,
	RQS_NAME varchar(50) NOT NULL,
	RQS_PROJECT integer NOT NULL
)
;

CREATE TABLE PROJECTS ( 
	PR_ID integer NOT NULL,
	PR_NAME varchar(20) NOT NULL
)
;

CREATE TABLE GROUPS ( 
	GR_ID integer NOT NULL,
	GR_NAME varchar(50) NOT NULL
)
;

CREATE TABLE STEPS ( 
	ST_ID integer NOT NULL,
	ST_NR integer NOT NULL,
	ST_ACTION text,
	ST_RESULT text,
	ST_TESTCASE integer NOT NULL,
	ST_EXPECTED_RESULT text,
	ST_JIRA_ISSUE text
)
;

CREATE TABLE TESTCASE_REQUREMENTS ( 
	TRQ_TESTCASE integer NOT NULL,
	TRQ_REQUIREMENT integer NOT NULL
)
;

CREATE TABLE REQUIREMENTS ( 
	RQ_ID integer NOT NULL,
	RQ_STATUS varchar(15) NOT NULL,
	RQ_CASES_NR integer NOT NULL,
	RQ_ASSIGNEE integer NOT NULL,
	RQ_CREATED timestamp NOT NULL,
	RQ_REQUIREMENT_SET integer NOT NULL,
	RQ_CONTENT text,
	RQ_NAME varchar(50) NOT NULL,
	RQ_AUTHOR integer NOT NULL,
	RQ_UPDATED timestamp
)
;

CREATE TABLE TESTCASES ( 
	TC_ID integer NOT NULL,
	TC_BLOCKED boolean NOT NULL,
	TC_UPDATED timestamp NOT NULL,
	TC_TESTCASE_SET integer NOT NULL,
	TC_BLOCKED_BY integer,
	TC_NAME varchar(50) NOT NULL,
	TC_PRIORITY varchar(15) NOT NULL,
	TC_PREREQUISITES text,
	TC_AUTHOR integer NOT NULL
)
;

CREATE TABLE USERS ( 
	USR_ID integer NOT NULL,
	USR_NAME varchar(20) NOT NULL,
	USR_SURNAME varchar(30) NOT NULL,
	USR_LOGIN varchar(30) NOT NULL,
	USR_EMAIL varchar(30) NOT NULL,
	USR_ROLE varchar(20) NOT NULL,
	USR_GROUP integer,
	USR_ACTIVE boolean NOT NULL,
	USR_PASSWORD text NOT NULL
)
;

CREATE TABLE TESTCASE_SETS ( 
	TCS_ID integer NOT NULL,
	TCS_NAME varchar(50) NOT NULL,
	TCS_PROJECT integer
)
;


ALTER TABLE PROJECT_USERS ADD CONSTRAINT PK_PROJECT_USERS 
	PRIMARY KEY (PUSR_USER, PUSR_PROJECT)
;


ALTER TABLE REQUIREMENT_SETS ADD CONSTRAINT PK_REQUIREMENT_SETS 
	PRIMARY KEY (RQS_ID)
;


ALTER TABLE PROJECTS ADD CONSTRAINT PK_PROJECTS 
	PRIMARY KEY (PR_ID)
;


ALTER TABLE GROUPS ADD CONSTRAINT PK_GROUPS 
	PRIMARY KEY (GR_ID)
;


ALTER TABLE STEPS ADD CONSTRAINT PK_STEPS 
	PRIMARY KEY (ST_ID)
;


ALTER TABLE TESTCASE_REQUREMENTS ADD CONSTRAINT PK_TESTCASE_REQUREMENTS 
	PRIMARY KEY (TRQ_TESTCASE, TRQ_REQUIREMENT)
;


ALTER TABLE REQUIREMENTS ADD CONSTRAINT PK_REQUIREMENTS 
	PRIMARY KEY (RQ_ID)
;


ALTER TABLE TESTCASES ADD CONSTRAINT PK_TESTCASES 
	PRIMARY KEY (TC_ID)
;


ALTER TABLE USERS ADD CONSTRAINT PK_USERS 
	PRIMARY KEY (USR_ID)
;


ALTER TABLE TESTCASE_SETS ADD CONSTRAINT PK_TESTCASE_SETS 
	PRIMARY KEY (TCS_ID)
;




ALTER TABLE PROJECT_USERS ADD CONSTRAINT FK_PROJECT_USERS_PROJECTS 
	FOREIGN KEY (PUSR_PROJECT) REFERENCES PROJECTS (PR_ID)
;

ALTER TABLE PROJECT_USERS ADD CONSTRAINT FK_PUSR_USERS 
	FOREIGN KEY (PUSR_USER) REFERENCES USERS (USR_ID)
;

ALTER TABLE REQUIREMENT_SETS ADD CONSTRAINT FK_RQS_PROJECT 
	FOREIGN KEY (RQS_PROJECT) REFERENCES PROJECTS (PR_ID)
;

ALTER TABLE STEPS ADD CONSTRAINT FK_ST_TESTCASE 
	FOREIGN KEY (ST_TESTCASE) REFERENCES TESTCASES (TC_ID)
;

ALTER TABLE TESTCASE_REQUREMENTS ADD CONSTRAINT FK_TRQ_TESTCASE 
	FOREIGN KEY (TRQ_TESTCASE) REFERENCES TESTCASES (TC_ID)
;

ALTER TABLE TESTCASE_REQUREMENTS ADD CONSTRAINT FK_TRQ_REQUIREMENT 
	FOREIGN KEY (TRQ_REQUIREMENT) REFERENCES REQUIREMENTS (RQ_ID)
;

ALTER TABLE REQUIREMENTS ADD CONSTRAINT FK_RQ_ASSIGNEE 
	FOREIGN KEY (RQ_ASSIGNEE) REFERENCES USERS (USR_ID)
;

ALTER TABLE REQUIREMENTS ADD CONSTRAINT FK_RQ_AUTHOR 
	FOREIGN KEY (RQ_AUTHOR) REFERENCES USERS (USR_ID)
;

ALTER TABLE REQUIREMENTS ADD CONSTRAINT FK_RQ_REQUIREMENT_SET 
	FOREIGN KEY (RQ_REQUIREMENT_SET) REFERENCES REQUIREMENT_SETS (RQS_ID)
;

ALTER TABLE TESTCASES ADD CONSTRAINT FK_TC_AUTHOR 
	FOREIGN KEY (TC_AUTHOR) REFERENCES USERS (USR_ID)
;

ALTER TABLE TESTCASES ADD CONSTRAINT FK_TC_BLOCKED_BY 
	FOREIGN KEY (TC_BLOCKED_BY) REFERENCES USERS (USR_ID)
;

ALTER TABLE TESTCASES ADD CONSTRAINT FK_TC_TESTCASE_SET 
	FOREIGN KEY (TC_TESTCASE_SET) REFERENCES TESTCASE_SETS (TCS_ID)
;

ALTER TABLE USERS ADD CONSTRAINT FK_USR_GROUP 
	FOREIGN KEY (USR_GROUP) REFERENCES GROUPS (GR_ID)
;

ALTER TABLE TESTCASE_SETS ADD CONSTRAINT FK_TCS_PROJECT 
	FOREIGN KEY (TCS_PROJECT) REFERENCES PROJECTS (PR_ID)
;