CREATE TABLE CITIZENSHIP
(
	ID NUMBER(6,0) NOT NULL CONSTRAINT "CITIZENSHIP_PK" PRIMARY KEY,
	NAME VARCHAR2(50) NOT NULL,
	CONSTRAINT "CITIZENSHIP_UK" UNIQUE (NAME)
)
/

CREATE SEQUENCE CITIZENSHIP_SEQ INCREMENT BY 1 START WITH 1;

CREATE OR REPLACE TRIGGER CITIZENSHIP_BIR
	BEFORE INSERT ON CITIZENSHIP
	FOR EACH ROW
	BEGIN
		SELECT CITIZENSHIP_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
		:NEW.NAME := INITCAP(:NEW.NAME);
	END CITIZENSHIP_BIR;
/

CREATE TABLE HUMANS
(
	ID NUMBER(6,0) NOT NULL CONSTRAINT "HUMANS_PK" PRIMARY KEY,
	NAME VARCHAR2(50) NOT NULL,
	SURNAME VARCHAR2(50) NOT NULL,
	SECOND_NAME VARCHAR(50) NOT NULL,
	BIRTH_DATE DATE NOT NULL,
	SEX CHAR(1) NOT NULL CONSTRAINT "M/F"
						CHECK (SEX IN ('M','F')),
	CITIZENSHIP_ID NUMBER(6,0) NOT NULL CONSTRAINT "CITIZENSHIP_FK" 
										REFERENCES CITIZENSHIP(ID),
	CONSTRAINT "HUMANS_UK" UNIQUE (SURNAME,NAME,SECOND_NAME,BIRTH_DATE,SEX)
);


CREATE SEQUENCE HUMANS_SEQ INCREMENT BY 1 START WITH 1;

CREATE OR REPLACE TRIGGER HUMANS_BIR
	BEFORE INSERT ON HUMANS
	FOR EACH ROW
	BEGIN
		SELECT HUMANS_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
		:NEW.SURNAME := INITCAP(:NEW.SURNAME);
		:NEW.NAME := INITCAP(:NEW.NAME);
		:NEW.SECOND_NAME := INITCAP(:NEW.SECOND_NAME);
		IF :NEW.BIRTH_DATE IS NULL THEN
			:NEW.BIRTH_DATE := TO_DATE('11.11.1111','DD.MM.YYYY');
		END IF;
	END HUMANS_BIR;
/

CREATE TABLE ENTRANTS
(
	ID NUMBER(6,0) NOT NULL CONSTRAINT "ENTRANTS_PK" PRIMARY KEY,
	HUMAN_ID NUMBER(6,0) NOT NULL CONSTRAINT "ENTRANTS_FK"
									REFERENCES HUMANS(ID),
	ORIGINAL CHAR(1) NOT NULL CONSTRAINT "ORIGINAL IS Y/N"
						CHECK (ORIGINAL IN ('Y','N')),
	MEDAL CHAR(1) NOT NULL CONSTRAINT "MEDAL IS Y/N"
						CHECK (MEDAL IN ('Y','N')),
	ALLOWANCE CHAR(1) NOT NULL CONSTRAINT "ALLOWANCE IS Y/N"
						CHECK (ALLOWANCE IN ('Y','N')),
	ENTRY_YEAR NUMBER(4,0) NOT NULL,
	SCANNED_PROFILE BLOB,
	CONSTRAINT "ENTRANTS_UK" UNIQUE (HUMAN_ID,ENTRY_YEAR)
);


CREATE SEQUENCE ENTRANTS_SEQ INCREMENT BY 1 START WITH 1;

CREATE OR REPLACE TRIGGER ENTRANTS_BIR
	BEFORE INSERT ON ENTRANTS
	FOR EACH ROW
	BEGIN
		SELECT ENTRANTS_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
	END ENTRANTS_BIR;
/

CREATE TABLE BRANCHES
(
	ID NUMBER(3,0) NOT NULL CONSTRAINT "BRANCHES_PK" PRIMARY KEY,
	NAME VARCHAR2(50) NOT NULL,
	CONSTRAINT "BRANCHES_UK" UNIQUE(NAME)
);


CREATE SEQUENCE BRANCHES_SEQ INCREMENT BY 1 START WITH 1;

CREATE OR REPLACE TRIGGER BRANCHES_BIR
	BEFORE INSERT ON BRANCHES
	FOR EACH ROW
	BEGIN
		SELECT BRANCHES_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
		:NEW.NAME := INITCAP(:NEW.NAME);
	END BRANCHES_BIR;
/

CREATE TABLE SPECIALTIES
(
	ID NUMBER(3,0) NOT NULL CONSTRAINT "SPECIALTIES_PK" PRIMARY KEY,
	NAME VARCHAR2(50) NOT NULL,
	CODE VARCHAR2(10) NOT NULL,
	CONSTRAINT "SPECIALTIES_UK" UNIQUE (CODE)
);


CREATE SEQUENCE SPECIALTIES_SEQ INCREMENT BY 1 START WITH 1;

CREATE OR REPLACE TRIGGER SPECIALTIES_BIR
	BEFORE INSERT ON SPECIALTIES
	FOR EACH ROW
	BEGIN
		SELECT SPECIALTIES_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
		:NEW.NAME := UPPER(:NEW.NAME);
	END SPECIALTIES_BIR;
/

CREATE TABLE PROGRAMS
(
	ID NUMBER(3,0) NOT NULL CONSTRAINT "PROGRAMS_PK" PRIMARY KEY,
	SPEC_ID NUMBER(3,0) NOT NULL CONSTRAINT "SPECIALTIES_FK"
								REFERENCES SPECIALTIES(ID),
	BRANCH_ID NUMBER(3,0) NOT NULL CONSTRAINT "BRANCHES_FK"
								REFERENCES BRANCHES(ID),
	CONSTRAINT "PROGRAMS_UK" UNIQUE (SPEC_ID,BRANCH_ID)
);


CREATE SEQUENCE PROGRAMS_SEQ INCREMENT BY 1 START WITH 1;

CREATE OR REPLACE TRIGGER PROGRAMS_BIR
	BEFORE INSERT ON PROGRAMS
	FOR EACH ROW
	BEGIN
		SELECT PROGRAMS_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
	END PROGRAMS_BIR;
/

CREATE TABLE DEPARTMENT_TYPES
(
	ID NUMBER(3,0) NOT NULL CONSTRAINT "DEPARTMENT_TYPES_PK" PRIMARY KEY,
	NAME VARCHAR2(50) NOT NULL,
	CONSTRAINT "DEPARTMENT_TYPES_UK" UNIQUE(NAME)
);


CREATE SEQUENCE DEPARTMENT_TYPES_SEQ INCREMENT BY 1 START WITH 1;

CREATE OR REPLACE TRIGGER DEPARTMENT_TYPES_BIR
	BEFORE INSERT ON DEPARTMENT_TYPES
	FOR EACH ROW
	BEGIN
		SELECT DEPARTMENT_TYPES_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
		:NEW.NAME := INITCAP(:NEW.NAME);
	END DEPARTMENT_TYPES_BIR;
/

CREATE TABLE DEPARTMENTS
(
	ID NUMBER (3,0) NOT NULL CONSTRAINT "DEPARTMENTS_PK" PRIMARY KEY,
	NAME VARCHAR2(100) NOT NULL,
	DEPARTMENT_ID NUMBER (3,0) CONSTRAINT "DEPARTMENTS_FK"
								REFERENCES DEPARTMENTS(ID),
	DEPARTMENT_TYPE_ID NUMBER (3,0) NOT NULL CONSTRAINT "DEPARTMENT_TYPES_FK" REFERENCES DEPARTMENT_TYPES(ID),
	CODE NUMBER(3,0) NOT NULL,
	ABBR VARCHAR2(15) NOT NULL,
	CONSTRAINT "DEPARTMENTS_UK" UNIQUE(CODE)
);


CREATE SEQUENCE DEPARTMENTS_SEQ INCREMENT BY 1 START WITH 1;

CREATE OR REPLACE TRIGGER DEPARTMENTS_BIR
	BEFORE INSERT ON DEPARTMENTS
	FOR EACH ROW
	BEGIN
		SELECT DEPARTMENTS_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
		:NEW.NAME := UPPER(:NEW.NAME);
	END DEPARTMENTS_BIR;
/

CREATE TABLE ACCEPTANCE_PLANS
(
	ID NUMBER(3,0) NOT NULL CONSTRAINT "ACCEPTANCE_PLANS_PK" PRIMARY KEY,
	PROGRAM_ID NUMBER(3,0) NOT NULL CONSTRAINT "PLANS_PROGRAMS_FK"
									REFERENCES PROGRAMS(ID),
	DEPARTMENT_ID NUMBER(3,0) NOT NULL CONSTRAINT "PLANS_DEPARTMENTS_FK"
									REFERENCES DEPARTMENTS(ID),
	AMOUNT NUMBER(3,0) NOT NULL,
	PLAN_YEAR NUMBER (4,0) NOT NULL,
	CONSTRAINT "ACCEPTANCE_PLANS_UK" UNIQUE(PROGRAM_ID,DEPARTMENT_ID)
);


CREATE SEQUENCE ACCEPTANCE_PLANS_SEQ INCREMENT BY 1 START WITH 1;

CREATE OR REPLACE TRIGGER ACCEPTANCE_PLANS_BIR
	BEFORE INSERT ON ACCEPTANCE_PLANS
	FOR EACH ROW
	BEGIN
		SELECT ACCEPTANCE_PLANS_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
	END ACCEPTANCE_PLANS_BIR;
/


CREATE TABLE DEPARTMENTPROGRAM
(
	ID NUMBER(3,0) NOT NULL CONSTRAINT "DEPARTMENTPROGRAM_PK" PRIMARY KEY,
	PROGRAM_ID NUMBER(3,0) NOT NULL CONSTRAINT "DP_PROGRAMS_FK" REFERENCES PROGRAMS(ID),
	DEPARTMENT_ID NUMBER(3,0) NOT NULL CONSTRAINT "DP_DEPARTMENT_FK" REFERENCES DEPARTMENTS(ID),
	CONSTRAINT "DEPARTMENTPROGRAM_UK" UNIQUE(PROGRAM_ID,DEPARTMENT_ID)
);


CREATE SEQUENCE DEPARTMENTPROGRAM_SEQ INCREMENT BY 1 START WITH 5;

CREATE OR REPLACE TRIGGER DEPARTMENTPROGRAM_BIR
	BEFORE INSERT ON DEPARTMENTPROGRAM
	FOR EACH ROW
	BEGIN
		SELECT DEPARTMENTPROGRAM_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
	END DEPARTMENTPROGRAM_BIR;
/

CREATE TABLE SUBJECTS
(
	ID NUMBER(3,0) NOT NULL CONSTRAINT "SUBJECTS_PK" PRIMARY KEY,
	NAME VARCHAR2(50) NOT NULL,
	CONSTRAINT "SUBJECTS_UK" UNIQUE(NAME)
);


CREATE SEQUENCE SUBJECTS_SEQ INCREMENT BY 1 START WITH 1;

CREATE OR REPLACE TRIGGER SUBJECTS_BIR
	BEFORE INSERT ON SUBJECTS
	FOR EACH ROW
	BEGIN
		SELECT SUBJECTS_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
	END SUBJECTS_BIR;
/

CREATE TABLE ORDERS_ITEMS
(
	ID NUMBER(3,0) NOT NULL CONSTRAINT "ORDERS_ITEMS" PRIMARY KEY,
	NAME VARCHAR2(50) NOT NULL,
	FULL_TEXT CLOB,
	ITEM_NUMBER NUMBER(2,0) NOT NULL,
	CONSTRAINT "ORDERS_ITEMS_UK" UNIQUE(NAME)
);


CREATE SEQUENCE ORDERS_ITEMS_SEQ INCREMENT BY 1 START WITH 1;

CREATE OR REPLACE TRIGGER ORDERS_ITEMS_BIR
	BEFORE INSERT ON ORDERS_ITEMS
	FOR EACH ROW
	BEGIN
		SELECT ORDERS_ITEMS_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
		:NEW.NAME := INITCAP(:NEW.NAME);
	END ORDERS_ITEMS_BIR;
/

CREATE TABLE PRIORITIES
(
	ID NUMBER(6,0) NOT NULL CONSTRAINT "PRIORITIES_PK" PRIMARY KEY,
	ENTRANT_ID NUMBER(6,0) NOT NULL CONSTRAINT "PR_ENTRANTS_FK" REFERENCES ENTRANTS(ID),
	PROGRAM_ID NUMBER(6,0) NOT NULL CONSTRAINT "PR_PROGRAMS_FK" REFERENCES DEPARTMENTPROGRAM(ID),
	PRIORITY NUMBER(1,0) NOT NULL CONSTRAINT "Приоритет не больше 3" CHECK(PRIORITY IN (1,2,3)),
	CONSTRAINT "PRIORITIES_UK" UNIQUE(ENTRANT_ID,PRIORITY)
);


CREATE SEQUENCE PRIORITIES_SEQ INCREMENT BY 1 START WITH 1;

CREATE OR REPLACE TRIGGER PRIORITIES_BIR
	BEFORE INSERT ON PRIORITIES
	FOR EACH ROW
	BEGIN
		SELECT PRIORITIES_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
	END PRIORITIES_BIR;
/

CREATE TABLE EXAMS
(
	ID NUMBER(6,0) NOT NULL CONSTRAINT "EXAMS_PK" PRIMARY KEY,
	ENTRANT_ID NUMBER (6,0) NOT NULL CONSTRAINT "EXAMS_ENTRANTS_FK" REFERENCES ENTRANTS(ID),
	SUBJECT_ID NUMBER(3,0) NOT NULL CONSTRAINT "EXAMS_SUBJECTS_FK" REFERENCES SUBJECTS(ID),
	MARK NUMBER(3,0) NOT NULL,
	CODE NUMBER(9,0) NOT NULL,
	CONSTRAINT "EXAMS_UK" UNIQUE(CODE)
);


CREATE SEQUENCE EXAMS_SEQ INCREMENT BY 1 START WITH 1;

CREATE OR REPLACE TRIGGER EXAMS_BIR
	BEFORE INSERT ON EXAMS
	FOR EACH ROW
	BEGIN
		SELECT EXAMS_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
	END EXAMS_BIR;
/

CREATE TABLE PROGRAMSUBJECTS
(
	ID NUMBER(6,0) NOT NULL CONSTRAINT "PROGRAMSUBJECTS_PK" PRIMARY KEY,
	PROGRAM_ID NUMBER(3,0) NOT NULL CONSTRAINT "PS_PROGRAMS_FK" REFERENCES PROGRAMS(ID),
	SUBJECT_ID NUMBER(3,0) NOT NULL CONSTRAINT "PS_SUBJECTS_FK" REFERENCES SUBJECTS(ID),
	THRESHOLD_MARK NUMBER(3,0) NOT NULL,
	PS_YEAR NUMBER(4,0) NOT NULL,
	CONSTRAINT "PROGRAMSUBJECTS_UK" UNIQUE(PROGRAM_ID,SUBJECT_ID,PS_YEAR)
);


CREATE SEQUENCE PROGRAMSUBJECTS_SEQ INCREMENT BY 1 START WITH 1;

CREATE OR REPLACE TRIGGER PROGRAMSUBJECTS_BIR
	BEFORE INSERT ON PROGRAMSUBJECTS
	FOR EACH ROW
	BEGIN
		SELECT PROGRAMSUBJECTS_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
	END PROGRAMSUBJECTS_BIR;
/

CREATE TABLE COMMANDMENTS
(
	ID NUMBER(6,0) NOT NULL CONSTRAINT "COMMANDMENTS_PK" PRIMARY KEY,
	ENTRANT_ID NUMBER(6,0) NOT NULL CONSTRAINT "COMM_ENTRANTS_FK" REFERENCES ENTRANTS(ID),
	PROGRAM_ID NUMBER(3,0) NOT NULL CONSTRAINT "COMM_PROGRAMS_FK" REFERENCES PROGRAMS(ID),
	ORDER_ID NUMBER(3,0) NOT NULL CONSTRAINT "COMM_ORDERS_ITEMS_FK" REFERENCES ORDERS_ITEMS,
	PRE_COMMANDMENT CHAR(1) NOT NULL CONSTRAINT "PRE_COMMANDMENT IS Y/N"
						CHECK (PRE_COMMANDMENT IN ('Y','N')),
	COMMANDMENT_YEAR NUMBER(4,0) NOT NULL,
	CODE NUMBER(9,0) NOT NULL,
	DEPARTMENT_ID NUMBER(3,0) NOT NULL CONSTRAINT "COMM_DEPARTMENTS_FK" REFERENCES DEPARTMENTS(ID),
	COMMIT_DATE DATE,
	CREATE_DATE DATE,
	CONSTRAINT "COMMANDMENTS_UK" UNIQUE(CODE)
);


CREATE SEQUENCE COMMANDMENTS_SEQ INCREMENT BY 1 START WITH 1;

CREATE OR REPLACE TRIGGER COMMANDMENTS_BIR
	BEFORE INSERT ON COMMANDMENTS
	FOR EACH ROW
	BEGIN
		SELECT COMMANDMENTS_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
		:NEW.CREATE_DATE := SYSDATE;
	END COMMANDMENTS_BIR;
/