sql greensauna

USE greensauna
;


# Drop Tables, Stored Procedures and Views 
DROP TABLE IF EXISTS designs
;
DROP TABLE IF EXISTS control_units
;
DROP TABLE IF EXISTS facings
;
DROP TABLE IF EXISTS heaters
;
DROP TABLE IF EXISTS customers
;
DROP TABLE IF EXISTS cabins
;
DROP TABLE IF EXISTS employees
;
DROP TABLE IF EXISTS assembly_teams
;
DROP TABLE IF EXISTS assemblies
;
DROP TABLE IF EXISTS cars
;

# Create Tables 
CREATE TABLE designs
(
  id INTEGER NOT NULL AUTO_INCREMENT,
  name VARCHAR(30) NOT NULL,
  active BOOL NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  UNIQUE UQ_designs_id(id),
  UNIQUE UQ_designs_name(name)
) 
;


CREATE TABLE control_units
(
  id INTEGER NOT NULL AUTO_INCREMENT,
  name VARCHAR(30) NOT NULL,
  active BOOL NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  UNIQUE UQ_control_units_id(id),
  UNIQUE UQ_control_units_name(name)
) 
;


CREATE TABLE facings
(
  id INTEGER NOT NULL AUTO_INCREMENT,
  name VARCHAR(30) NOT NULL,
  active BOOL NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  UNIQUE UQ_facings_id(id),
  UNIQUE UQ_facings_name(name)
) 
;


CREATE TABLE heaters
(
  id INTEGER NOT NULL AUTO_INCREMENT,
  name VARCHAR(30) NOT NULL,
  active BOOL NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  UNIQUE UQ_heaters_id(id),
  UNIQUE UQ_heaters_name(name)
) 
;


CREATE TABLE customers
(
  id INTEGER NOT NULL AUTO_INCREMENT,
  name VARCHAR(60) NOT NULL,
  contact VARCHAR(255) NULL,
  cabins_count INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  UNIQUE UQ_customers_id(id),
  UNIQUE UQ_customers_name(name)
) 
;


CREATE TABLE cabins
(
  id INTEGER NOT NULL AUTO_INCREMENT,
  address VARCHAR(255) NULL,
  note TEXT NULL,
  control_unit_id INTEGER NULL,
  design_id INTEGER NULL,
  facing_in_id INTEGER NULL,
  facing_out_id INTEGER NULL,
  customer_id INTEGER NULL,
  heater_id INTEGER NULL,
  assembly_id INTEGER NULL,
  PRIMARY KEY (id),
  UNIQUE UQ_cabins_id(id),
  KEY (assembly_id),
  KEY (facing_in_id),
  KEY (facing_out_id),
  KEY (control_unit_id),
  KEY (design_id),
  KEY (customer_id),
  KEY (heater_id)
) 
;


CREATE TABLE employees
(
  id INTEGER NOT NULL AUTO_INCREMENT,
  name VARCHAR(60) NOT NULL,
  assembly_team_id INTEGER NULL,
  PRIMARY KEY (id),
  UNIQUE UQ_employees_id(id),
  UNIQUE UQ_employees_name(name),
  KEY (assembly_team_id)
) 
;


CREATE TABLE assembly_teams
(
  id INTEGER NOT NULL AUTO_INCREMENT,
  name VARCHAR(60) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE UQ_assembly_teams_id(id),
  UNIQUE UQ_assembly_teams_name(name)
) 
;


CREATE TABLE assemblies
(
  id INTEGER NOT NULL AUTO_INCREMENT,
  start_at DATE NOT NULL,
  end_at DATE NULL,
  assembly_team_id INTEGER NULL,
  car_id INTEGER NULL,
  PRIMARY KEY (id),
  UNIQUE UQ_assemblies_id(id),
  KEY (assembly_team_id),
  KEY (car_id)
) 
;


CREATE TABLE cars
(
  id INTEGER NOT NULL AUTO_INCREMENT,
  license_plate VARCHAR(7) NOT NULL,
  note VARCHAR(255) NULL,
  PRIMARY KEY (id),
  UNIQUE UQ_cars_id(id),
  UNIQUE UQ_cars_license_plate(license_plate)
) 
;





# Create Foreign Key Constraints 
ALTER TABLE cabins ADD CONSTRAINT FK_cabins_assemblies 
  FOREIGN KEY (assembly_id) REFERENCES assemblies (id)
;

ALTER TABLE cabins ADD CONSTRAINT FK_cabins_facings 
  FOREIGN KEY (facing_in_id) REFERENCES facings (id)
;

ALTER TABLE cabins ADD CONSTRAINT FK_cabins_facings 
  FOREIGN KEY (facing_out_id) REFERENCES facings (id)
;

ALTER TABLE cabins ADD CONSTRAINT FK_cabins_control_units 
  FOREIGN KEY (control_unit_id) REFERENCES control_units (id)
;

ALTER TABLE cabins ADD CONSTRAINT FK_cabins_designs 
  FOREIGN KEY (design_id) REFERENCES designs (id)
;

ALTER TABLE cabins ADD CONSTRAINT FK_cabins_customers 
  FOREIGN KEY (customer_id) REFERENCES customers (id)
;

ALTER TABLE cabins ADD CONSTRAINT FK_cabins_heaters 
  FOREIGN KEY (heater_id) REFERENCES heaters (id)
;

ALTER TABLE employees ADD CONSTRAINT FK_employees_assembly_teams 
  FOREIGN KEY (assembly_team_id) REFERENCES assembly_teams (id)
;

ALTER TABLE assemblies ADD CONSTRAINT FK_assemblies_assembly_teams 
  FOREIGN KEY (assembly_team_id) REFERENCES assembly_teams (id)
;

ALTER TABLE assemblies ADD CONSTRAINT FK_assemblies_cars 
  FOREIGN KEY (car_id) REFERENCES cars (id)
;