CREATE SEQUENCE department_seq START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE employee_seq START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE project_seq START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE assignment_seq START WITH 1 INCREMENT BY 1;

CREATE TABLE department (
    ID INT PRIMARY KEY,
    Name VARCHAR2(50) NOT NULL,
    dept_manager_id INT UNIQUE
);

CREATE TABLE employee (
    id INT NOT NULL PRIMARY KEY,
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL,
    dob DATE DEFAULT NULL,
    is_contractor NUMBER(1,0) NOT NULL DEFAULT 0,
    manager_id INT DEFAULT NULL,
    FOREIGN KEY (manager_id) REFERENCES department (id)
);

ALTER TABLE department ADD CONSTRAINT dept_emp_fk FOREIGN KEY (dept_manager_id) REFERENCES employee (ID);


CREATE TABLE Contractor (
    id INT NOT NULL PRIMARY KEY,
    hour_rate DECIMAL(10,2) NOT NULL,
    company VARCHAR2(100) DEFAULT 'Self employed',
    specialty VARCHAR2(100) DEFAULT NULL,
    FOREIGN KEY (id) REFERENCES employee (id)
);

CREATE TABLE Salaried_employee (
    id INT NOT NULL PRIMARY KEY,
    hire_date DATE DEFAULT NULL,
    experience NUMBER(3) DEFAULT 0,
    salary DECIMAL(10,2) DEFAULT 2000,
    week_hours NUMBER(3) DEFAULT 40,
    works_for_dept_id INT NOT NULL,
    FOREIGN KEY (id) REFERENCES employee (id),
    FOREIGN KEY (works_for_dept_id) REFERENCES department (ID)
);

CREATE TABLE project (
    id INT PRIMARY KEY,
    name VARCHAR2(100) NOT NULL,
    description VARCHAR2(1000) DEFAULT '',
    Budget DECIMAL(10,0) DEFAULT NULL,
    start_date DATE DEFAULT NULL,
    end_date DATE DEFAULT NULL,
    deadline DATE DEFAULT NULL,
    status VARCHAR2(10) DEFAULT 'running' CHECK (status IN ('completed', 'running')),
    coordinated_by_emp_id INT NOT NULL,
    owned_by_dept_id INT NOT NULL,
    FOREIGN KEY (coordinated_by_emp_id) REFERENCES employee (id),
    FOREIGN KEY (owned_by_dept_id) REFERENCES department (ID)
);

CREATE TABLE assignment (
    id INT PRIMARY KEY,
    proj_id INT NOT NULL,
    emp_id INT NOT NULL,
    assign_date DATE DEFAULT NULL,
    hours_worked NUMBER(3),
    CONSTRAINT uniq_proj_emp_assign UNIQUE (proj_id, emp_id, assign_date),
    FOREIGN KEY (proj_id) REFERENCES project (id),
    FOREIGN KEY (emp_id) REFERENCES employee (id)
);