DROP TABLE rel_prj_emp CASCADE CONSTRAINTS;
DROP TABLE projects CASCADE CONSTRAINTS;
DROP TABLE employees CASCADE CONSTRAINTS;
DROP TABLE departments CASCADE CONSTRAINTS;
DROP SEQUENCE seq_dep;
DROP SEQUENCE seq_emp;
DROP SEQUENCE seq_prj;

CREATE TABLE departments
(
	dep_id NUMBER,
	dep_name varchar2(50) NOT NULL,
	dep_chief_dep_id NUMBER,
	dep_manager_id NUMBER,
	CONSTRAINT departments_pk PRIMARY KEY (dep_id)
);

CREATE TABLE employees
(
	emp_id NUMBER,
	emp_first_name VARCHAR2(50) NOT NULL,
	emp_last_name VARCHAR2(50) NOT NULL,
	emp_position VARCHAR2(100),
	emp_salary NUMBER,
	emp_dep_id NUMBER,
	emp_join_date DATE,
	CONSTRAINT employees_pk PRIMARY KEY (emp_id),
	CONSTRAINT departments_fk2 FOREIGN KEY (emp_dep_id) REFERENCES departments(dep_id)
);

ALTER TABLE departments ADD CONSTRAINT departments_fk1 FOREIGN KEY (dep_chief_dep_id) REFERENCES departments(dep_id);

CREATE TABLE projects
(
	prj_id NUMBER,
	prj_name VARCHAR2(150) NOT NULL,
	prj_start_date DATE,
	prj_finish_date DATE,
	CONSTRAINT projects_pk PRIMARY KEY (prj_id)
);

CREATE TABLE rel_prj_emp
(
	rel_emp_id NUMBER,
	rel_prj_id NUMBER,
	PRIMARY KEY (rel_emp_id, rel_prj_id),
	CONSTRAINT employees_fk FOREIGN KEY (rel_emp_id) REFERENCES employees(emp_id),
	CONSTRAINT projects_fk FOREIGN KEY (rel_prj_id) REFERENCES projects(prj_id)
);

CREATE SEQUENCE seq_dep START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE seq_emp START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE seq_prj START WITH 1 INCREMENT BY 1;

INSERT INTO departments VALUES(seq_dep.NEXTVAL, 'Администрация', NULL, 1);
INSERT INTO departments VALUES(seq_dep.NEXTVAL, 'Отдел кадров', 1, 5);
INSERT INTO departments VALUES(seq_dep.NEXTVAL, 'Разработки', 1, 4);
INSERT INTO departments VALUES(seq_dep.NEXTVAL, 'Тестирования', 3, 3);

INSERT INTO employees VALUES(seq_emp.NEXTVAL, 'Иван', 'Иванов', 'директор', 60000, 1, TO_DATE('01.01.2000', 'DD.MM.YYYY'));
INSERT INTO employees VALUES(seq_emp.NEXTVAL, 'Петр', 'Петров', 'менеджер', 50000, 1, TO_DATE('01.02.2000', 'DD.MM.YYYY'));
INSERT INTO employees VALUES(seq_emp.NEXTVAL, 'Иван', 'Сидоров', 'менеджер', 15000, 4, TO_DATE('15.12.2003', 'DD.MM.YYYY'));
INSERT INTO employees VALUES(seq_emp.NEXTVAL, 'Юрий', 'Гурбис', 'менеджер', 30000, 3, TO_DATE('23.07.2004', 'DD.MM.YYYY'));
INSERT INTO employees VALUES(seq_emp.NEXTVAL, 'Элла', 'Иткис', 'менеджер', 30000, 2, TO_DATE('24.03.2002', 'DD.MM.YYYY'));
INSERT INTO employees VALUES(seq_emp.NEXTVAL, 'Борис', 'Иванов', 'программист', 10000, 3, TO_DATE('21.10.2006', 'DD.MM.YYYY'));
INSERT INTO employees VALUES(seq_emp.NEXTVAL, 'Елена', 'Иванова', 'программист', 8000, 4, TO_DATE('12.09.2006', 'DD.MM.YYYY'));
INSERT INTO employees VALUES(seq_emp.NEXTVAL, 'Павел', 'Суриков', 'программист', 12000, 3, TO_DATE('24.10.2007', 'DD.MM.YYYY'));
INSERT INTO employees VALUES(seq_emp.NEXTVAL, 'Ольга', 'Ландышева', 'программист', 8000, 4, TO_DATE('20.05.2007', 'DD.MM.YYYY'));
INSERT INTO employees VALUES(seq_emp.NEXTVAL, 'Ольга', 'Кун', 'программист', 15000, 3, TO_DATE('20.05.2005', 'DD.MM.YYYY'));

INSERT INTO projects VALUES(seq_prj.NEXTVAL, 'Автоматизация отдела кадров', TO_DATE('01.01.2007', 'DD.MM.YYYY'), TO_DATE('01.09.2007', 'DD.MM.YYYY'));
INSERT INTO projects VALUES(seq_prj.NEXTVAL, 'Интернет магазин Орион', TO_DATE('12.09.2006', 'DD.MM.YYYY'), NULL);
INSERT INTO projects VALUES(seq_prj.NEXTVAL, 'Интернет магазин Синтия', TO_DATE('12.05.2007', 'DD.MM.YYYY'), TO_DATE('30.01.2008', 'DD.MM.YYYY'));

INSERT INTO rel_prj_emp VALUES(10, 1);
INSERT INTO rel_prj_emp VALUES(10, 2);
INSERT INTO rel_prj_emp VALUES(10, 3);
INSERT INTO rel_prj_emp VALUES(2, 1);
INSERT INTO rel_prj_emp VALUES(2, 2);
INSERT INTO rel_prj_emp VALUES(2, 3);
INSERT INTO rel_prj_emp VALUES(3, 2);
INSERT INTO rel_prj_emp VALUES(4, 1);
INSERT INTO rel_prj_emp VALUES(4, 2);
INSERT INTO rel_prj_emp VALUES(4, 3);
INSERT INTO rel_prj_emp VALUES(6, 1);
INSERT INTO rel_prj_emp VALUES(6, 2);
INSERT INTO rel_prj_emp VALUES(7, 1);
INSERT INTO rel_prj_emp VALUES(7, 3);
INSERT INTO rel_prj_emp VALUES(8, 2);
INSERT INTO rel_prj_emp VALUES(8, 3);
INSERT INTO rel_prj_emp VALUES(9, 2);