--CREATE TABLES
CREATE TABLE lamps (lamp_id INT NOT NULL,
area_id INT NOT NULL)

CREATE TABLE movements (lamp_id INT NOT NULL,
time datetime NOT NULL)

CREATE TABLE intensitySetting (area_id INT NOT NULL,
time datetime NOT NULL,
intensity float NOT NULL,
saveDate datetime NOT NULL)

CREATE TABLE areas(area_id INT NOT NULL,
area_name varchar (50) NOT NULL)

-- PRIMARY KEYS
ALTER TABLE lamps 
   ADD CONSTRAINT PK_lampsID PRIMARY KEY (lamp_id);
   
ALTER TABLE areas
   ADD CONSTRAINT PK_areaID PRIMARY KEY (area_id);

--FOREIGN KEYS
ALTER TABLE movements
ADD CONSTRAINT FK_movements_lampsID FOREIGN KEY (lamp_id) REFERENCES lamps (lamp_id) ;

ALTER TABLE lamps
ADD CONSTRAINT FK_lamps_areaID FOREIGN KEY (area_id) REFERENCES areas (area_id) ;

ALTER TABLE intensitySetting
ADD CONSTRAINT FK_intensitySetting_areaID FOREIGN KEY (area_id) REFERENCES areas (area_id) ;

-- UNIQUE CONSTRAINTS
ALTER TABLE movements 
ADD CONSTRAINT C_MOVEMENT UNIQUE (lamp_id, time); 

ALTER TABLE intensitySetting
ADD CONSTRAINT C_INTENSITY UNIQUE (area_id, time, saveDate); 

ALTER TABLE lamps 
   ADD CONSTRAINT PK_lampsID PRIMARY KEY (lamp_id);

ALTER TABLE movements
ADD CONSTRAINT FK_movements_lampsID FOREIGN KEY (lamp_id) REFERENCES lamps (lamp_id) ;

ALTER TABLE areas
   ADD CONSTRAINT PK_areaID PRIMARY KEY (area_id);

ALTER TABLE lamps
ADD CONSTRAINT FK_lamps_areaID FOREIGN KEY (area_id) REFERENCES areas (area_id) ;

ALTER TABLE intensitySetting
ADD CONSTRAINT FK_intensitySetting_areaID FOREIGN KEY (area_id) REFERENCES areas (area_id) ;