#****************************************************************
# use "In Class Example RealEstate Queries.sql" 
#****************************************************************

Drop database if exists RealEstate;
Create database RealEstate;
use RealEstate;

#####################
# Create Tables
#####################

Create Table House 
(
	hid 		int,
	squareFeet 	double,
	address 	varchar(30),
	dateBuilt 	date,
	zip 		varchar(5), 
	price 		double,
   constraint house_PK Primary Key (hid)  
) engine = innodb;

Create Table roomType 
(
	rid varchar (2),
	roomDescription varchar(30),
   Constraint roomType_PK primary key (RID)
) engine = innodb;

Create Table Room 
(
	rid int auto_increment ,
	hid int,
	nameID varchar(2),
	roomWidth double,
	roomLength double,
	roomHeight double,
  constraint room_PK Primary Key (rid),
  constraint room_FK2 foreign key (nameID) references roomType(rid),
  constraint room_FK1 foreign key (hid) references House(hid) 
) engine = innodb;


#####################
# Populate Tables
#####################

--  This code inserts the data into the tables 

insert into roomType(rid, roomDescription) values  ('LR', 'Living Room');
insert into roomType(rid, roomDescription) values  ('K',  'Kitchen');
insert into roomType(rid, roomDescription) values  ('BR', 'Bedroom Room');
insert into roomType(rid, roomDescription) values  ('BT', 'Bath Room');
insert into roomType(rid, roomDescription) values  ('DN', 'Den');
insert into roomType(rid, roomDescription) values  ('BM', 'Basement');
insert into roomType(rid, roomDescription) values  ('DR', 'Dining Room');

insert into House values (1, 2500, '321 East A Street', '1960-07-21', '14321', 250000);
insert into House values (2, 1600, '1234 South Street', '1903-07-21', '15321', 185000);
insert into House values (3, 5000, '555 North Street', '1980-07-21', '14521',  359000); 
insert into House values (4, 5000, '321 West A Street', '1960-07-21', '14321', 458000);

insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (1, 'LR',15, 18, 20);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (1, 'K',12, 18, 20);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (1, 'DR',20, 18, 20);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (1, 'BR',10, 12, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (1, 'BR',10, 12, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (1, 'BR',18, 20, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (1, 'BT',10, 9, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (1, 'BT',8, 6, 20);

insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (2, 'LR',10, 10, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (2, 'K',12, 12, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (2, 'DR',12, 15, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (2, 'BR',10, 10, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (2, 'BR',10, 10, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (2, 'BR',12, 12, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (2, 'BT',10, 9, 15);

insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (3, 'LR',20, 30, 20);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (3, 'K',20, 20, 20);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (3, 'DR',20, 30, 20);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (3, 'BR',10, 15, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (3, 'BR',10, 15, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (3, 'BR',10, 15, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (3, 'BR',20, 20, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (3, 'BT',10, 9, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (3, 'BT',10, 9, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (3, 'BT',10, 9, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (3, 'BT',8, 6, 20);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (3, 'BM',20, 40, 12);

insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (4, 'LR',20, 30, 20);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (4, 'K',20, 20, 20);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (4, 'DR',20, 30, 20);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (4, 'BR',10, 15, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (4, 'BR',10, 15, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (4, 'BR',10, 15, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (4, 'BR',20, 20, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (4, 'BT',10, 9, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (4, 'BT',10, 9, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (4, 'BT',10, 9, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (4, 'BT',8, 6, 20);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (4, 'DN',8, 6, 20);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (4, 'BM',20, 60, 12);



-- Build a comma delimited flat file from the ROOM table
select * into outfile 'c:/room.txt'
  fields terminated by ',' from room;

-- Delete the Room table and rebuild it from the comma delimited file
truncate room;

load data infile 'c:/room.txt' 
  into table room
  fields terminated by ',';


-- Change the delimiter to create a stored function (counts rooms by house id)

delimiter |
create function f_rctr (inhid int)
      returns int
  begin
   declare ctr int;
   select count(*) into ctr 
     from room 
    where hid = inhid;
   return ctr;
  end |
delimiter ;

-- Use the function created above 
select f_rctr(1) from dual;


-- Create a stored procedure to return the count of rooms by House_Id

delimiter //
create procedure 
  countrooms( in hhid int, out ctr int)
begin
select count(*) 
  into ctr 
  from room 
 where hid = hhid;
end //
delimiter ;

-- run the stored procedure and store the results in variable @crooms
call countrooms (1,@crooms);
select @crooms;

-- Create a table to log changes to the Room table

Create Table Room_log
(
    logid int auto_increment primary key,
	rid int,
	hid int,
	nameID varchar(2),
	roomWidth double,
	roomLength double,
	roomHeight double,
    update_user varchar(20),
    Cdate timestamp

) engine = innodb;

-- Create a trigger to log the changes to Room
delimiter //
create trigger upd_room 
  before update on room
for each row
begin
  declare c_user varchar(20);
  select user() into c_user;
  insert into room_log 
   values (null,new.rid,new.hid,new.nameid,new.roomwidth,new.roomlength,new.roomheight,c_user,now());
end;//
delimiter ;


-- Test trigger
update room
set roomheight = 10
where rid = 5;

update room
set roomheight = 12
where hid = 4;

-- See the log

select * from room_log; 

