-- DROP SCHEMA tutorial;
ALTER TABLE tutorial.Department DROP FOREIGN KEY Department_FK_headId;
DROP TABLE IF EXISTS tutorial.Employee;
DROP TABLE IF EXISTS tutorial.PostalLocation;
DROP TABLE IF EXISTS tutorial.JobPosition;
DROP TABLE IF EXISTS tutorial.Department;
DROP TABLE IF EXISTS tutorial.Room;
DROP TABLE IF EXISTS tutorial.AcademicTitle;

CREATE SCHEMA IF NOT EXISTS tutorial;
USE tutorial;
CREATE TABLE  PostalLocation (
	id							SERIAL PRIMARY KEY, -- SERIAL: BIGINT UNSIGNED AUTO_INCREMENT NOT NULL UNIQUE
    code 						CHAR(5) NOT NULL,
    cityName					VARCHAR(20) NOT NULL,
    cityDistrictName			VARCHAR(100)
);


CREATE TABLE AcademicTitle (
	id					SERIAL PRIMARY KEY, -- SERIAL: BIGINT UNSIGNED AUTO_INCREMENT NOT NULL UNIQUE
    name 				CHAR(10) NOT NULL UNIQUE
);

CREATE TABLE JobPosition (
	id					SERIAL PRIMARY KEY, -- SERIAL: BIGINT UNSIGNED AUTO_INCREMENT NOT NULL UNIQUE
    name 				VARCHAR(40) NOT NULL UNIQUE,
    description			TEXT
);

CREATE TABLE Room (
	id					SERIAL PRIMARY KEY, -- SERIAL: BIGINT UNSIGNED AUTO_INCREMENT NOT NULL UNIQUE
    name				VARCHAR(15) NOT NULL UNIQUE,
    buildingName 		VARCHAR(40) NOT NULL,
    M2_area				NUMERIC(5,2) NOT NULL,
    seatCount			INTEGER NOT NULL,
    computerCount		INTEGER NOT NULL,
	monthlyRentEUR_Amount	NUMERIC(10,2)
);

CREATE TABLE Department (
	id							SERIAL PRIMARY KEY, -- SERIAL: BIGINT UNSIGNED AUTO_INCREMENT NOT NULL UNIQUE
    name 						VARCHAR(40) NOT NULL UNIQUE,
    headId						BIGINT UNSIGNED,
    responsibilityDescription	TEXT
);

CREATE TABLE Employee (
	id								SERIAL PRIMARY KEY, -- SERIAL: BIGINT UNSIGNED AUTO_INCREMENT NOT NULL UNIQUE
    academicTitleId 				BIGINT UNSIGNED NOT NULL,
	forename 						VARCHAR(40),
	surname							VARCHAR(50),
	jobPositionId					BIGINT UNSIGNED,
	departmentId					BIGINT UNSIGNED,
	roomId							BIGINT UNSIGNED,
	dateOfBirth						DATE NOT NULL,
	dateOfRecruitment				DATE NOT NULL,
    postalLocationId				BIGINT UNSIGNED,
	houseNumber						CHAR(10),
	streetName						VARCHAR(100),
	annualSalaryGrossEUR_Amount		NUMERIC(12,2),
	telephoneNumber					CHAR(20),
    FOREIGN KEY(AcademicTitleId)	REFERENCES AcademicTitle(id),
    FOREIGN KEY(jobPositionId) 		REFERENCES JobPosition(id),
    FOREIGN KEY(departmentId) 		REFERENCES Department(id),
    FOREIGN KEY(roomId) 			REFERENCES Room(id),
    FOREIGN KEY(postalLocationId) REFERENCES PostalLocation(id)
);

-- Achtung: schlechtes Design: Employee und Department sind wechselseitig abhängig
-- Der Fremdschlüssel headId auf Employee muss nachträglich ergänzt werden.
ALTER TABLE Department ADD
CONSTRAINT Department_FK_headId
FOREIGN KEY(headId) REFERENCES Employee(id);