CREATE TABLE consult.address (
	address_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
	line1 VARCHAR(50) NOT NULL,
	line2 VARCHAR(50),
	city VARCHAR(50) NOT NULL,
	region VARCHAR(50) NOT NULL,
	country VARCHAR(50) NOT NULL,
	postal_code VARCHAR(50) NOT NULL,
	CONSTRAINT consult_address_pk PRIMARY KEY ( address_id )
);

CREATE TABLE consult.consultant_status (
	status_id CHAR NOT NULL,
	description VARCHAR(50) NOT NULL,
	CONSTRAINT consult_consultant_status_pk PRIMARY KEY ( status_id )
);

CREATE TABLE consult.consultant (
	consultant_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
	status_id CHAR NOT NULL,
	email VARCHAR(50) NOT NULL,
	hourly_rate DECIMAL(6,2) NOT NULL,
	billable_hourly_rate DECIMAL(6,2) NOT NULL,
	hire_date DATE,
	recruiter_id INTEGER,
	resume LONG VARCHAR,
	CONSTRAINT consult_consultant_pk PRIMARY KEY ( consultant_id )
);

CREATE TABLE consult.client (
	client_name VARCHAR(50) NOT NULL,
	client_department_number SMALLINT NOT NULL, 
	billing_address INTEGER NOT NULL,
	contact_email VARCHAR(50),
	CONSTRAINT consult_client_pk PRIMARY KEY ( client_name, client_department_number )
);

CREATE TABLE consult.recruiter (
	recruiter_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
	email VARCHAR(50) NOT NULL,
	client_name VARCHAR(50),
	client_department_number SMALLINT,
	CONSTRAINT consult_recruiter_pk PRIMARY KEY ( recruiter_id )
);

CREATE TABLE consult.project (
	client_name VARCHAR(50) NOT NULL,
	client_department_number SMALLINT NOT NULL,
	project_name VARCHAR(50) NOT NULL,
	contact_email VARCHAR(50),
	CONSTRAINT consult_project_pk PRIMARY KEY ( client_name, client_department_number, project_name )
);

CREATE TABLE consult.project_consultant (
	client_name VARCHAR(50) NOT NULL,
	client_department_number SMALLINT NOT NULL,
	project_name VARCHAR(50) NOT NULL,
	consultant_id INTEGER NOT NULL,
	CONSTRAINT consult_project_consultant_pk PRIMARY KEY ( client_name, client_department_number, project_name, consultant_id )
);

CREATE TABLE consult.billable (
	billable_id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
	consultant_id INTEGER NOT NULL,
	client_name VARCHAR(50) NOT NULL,
	client_department_number SMALLINT NOT NULL,
	project_name VARCHAR(50) NOT NULL,
	start_date TIMESTAMP,
	end_date TIMESTAMP,
	hours SMALLINT NOT NULL,
	hourly_rate DECIMAL(6,2) NOT NULL,
	billable_hourly_rate DECIMAL(6,2) NOT NULL,
	description VARCHAR(50),
	artifacts CLOB, 
	CONSTRAINT consult_billable_pk PRIMARY KEY ( billable_id )
);

ALTER TABLE consult.consultant ADD CONSTRAINT consult_consultant_fk_status_id FOREIGN KEY ( status_id ) REFERENCES consult.consultant_status ( status_id );
ALTER TABLE consult.consultant ADD CONSTRAINT consult_consultant_fk_recruiter_id FOREIGN KEY ( recruiter_id ) REFERENCES consult.recruiter ( recruiter_id );

ALTER TABLE consult.client ADD CONSTRAINT consult_client_fk_billing_address FOREIGN KEY ( billing_address ) REFERENCES consult.address ( address_id );
ALTER TABLE consult.client ADD CONSTRAINT consult_client_uq_billing_address UNIQUE ( billing_address );

ALTER TABLE consult.recruiter ADD CONSTRAINT consult_recruiter_fk_client_name FOREIGN KEY ( client_name, client_department_number ) REFERENCES consult.client ( client_name, client_department_number );

ALTER TABLE consult.project ADD CONSTRAINT consult_project_fk_client_name FOREIGN KEY ( client_name, client_department_number ) REFERENCES consult.client ( client_name, client_department_number );

ALTER TABLE consult.project_consultant ADD CONSTRAINT consult_project_consultant_fk_project_name FOREIGN KEY ( client_name, client_department_number, project_name ) REFERENCES consult.project ( client_name, client_department_number, project_name );
ALTER TABLE consult.project_consultant ADD CONSTRAINT consult_project_consultant_fk_consultant_id FOREIGN KEY ( consultant_id ) REFERENCES consult.consultant ( consultant_id );

ALTER TABLE consult.billable ADD CONSTRAINT consult_billable_fk_consultant_id FOREIGN KEY ( consultant_id ) REFERENCES consult.consultant ( consultant_id );
ALTER TABLE consult.billable ADD CONSTRAINT consult_billable_fk_project_name FOREIGN KEY ( client_name, client_department_number, project_name ) REFERENCES consult.project ( client_name, client_department_number, project_name );