-- Tao database
create database BT2209
go

-- Kich hoat database
use BT2209
go

-- Tao tables
create table Room (
	id int not null,
	name nvarchar(20),
	max int
)
go

create table Animal (
	id int not null,
	name nvarchar(50),
	age int,
	buy_at datetime,
	room_id int
)
go

create table FoodType (
	id int not null,
	name nvarchar(50),
	price float,
	amount int
)
go

create table FoodAnimal (
	food_id int not null,
	animal_id int not null
)
go

-- primary key
alter table Room
add constraint pk_room primary key (id)
go

alter table Animal
add constraint pk_animal primary key (id)
go

alter table FoodType
add constraint pk_foodtype primary key (id)
go

alter table FoodAnimal
add constraint pk_food_animal primary key (food_id, animal_id)
go

-- foreign key
alter table Animal
add constraint fk_room_id foreign key (room_id) references Room (id)
go

alter table FoodAnimal
add constraint fk_fa_food_id foreign key (food_id) references FoodType (id)
go

alter table FoodAnimal
add constraint fk_fa_animal_id foreign key (animal_id) references Animal (id)
go

-- insert data: Thêm mỗi bảng 5 bản ghi dữ liệu
insert into Room(id, name, max)
values
(1, 'R01', 5),
(2, 'R02', 15),
(3, 'R03', 2),
(4, 'R04', 25),
(5, 'R05', 50)
go

insert into FoodType(id, name, price, amount)
values
(1, 'F01', 11, 100),
(2, 'F02', 10, 80),
(3, 'F03', 21, 30),
(4, 'F04', 100, 1000),
(5, 'F05', 101, 200)
go

insert into Animal(id, name, age, buy_at, room_id)
values
(1, 'A01', 1, '2021-05-19', 1),
(2, 'A02', 2, '2020-05-12', 1),
(3, 'A03', 5, '2021-11-16', 2),
(4, 'A04', 1, '2021-08-09', 2),
(5, 'A05', 2, '2021-06-29', 2)
go

insert into FoodAnimal (animal_id, food_id)
values
(1, 1),
(1, 2),
(2, 3),
(3, 4),
(4, 2),
(5, 1)
go

--  Xem thông tin động vật gồm các trường sau: tên chuồng, tên động vật, tuổi, ngày mua về
select Animal.name 'Ten Dong Vat', Animal.age 'Tuoi', Animal.buy_at 'Ngay Mua', Room.name 'Ten Chuong'
from Animal left join Room on Animal.room_id = Room.id
go

-- Xem thông tin những chuồng có số động vật đang lưu vượt quá max của chuồng đó
select Room.name 'Ten Chuong', Room.max 'So Luong Toi Da', count(Animal.id) 'So Dong Vat'
from Room left join Animal on Room.id = Animal.room_id
group by Room.name, Room.max
order by 'So Dong Vat' desc

update Room set max = 2 where id = 2

select Room.name 'Ten Chuong', Room.max 'So Luong Toi Da', count(Animal.id) 'So Dong Vat'
from Room left join Animal on Room.id = Animal.room_id
group by Room.name, Room.max
having count(Animal.id) > Room.max
order by 'So Dong Vat' desc

-- Xem thông tin những chuồng còn so khả năng lưu thêm động vật vào
select Room.name 'Ten Chuong', Room.max 'So Luong Toi Da', count(Animal.id) 'So Dong Vat'
from Room left join Animal on Room.id = Animal.room_id
group by Room.name, Room.max
having count(Animal.id) < Room.max
order by 'So Dong Vat' desc

-- Viết proc có tham số là @animalId -> cho phép xem được thông tin loại thức ăn của động vật này.
select Animal.name 'Ten Dong Vat', FoodType.name 'Ten Thuc An'
from Animal left join FoodAnimal on Animal.id = FoodAnimal.animal_id
	left join FoodType on FoodType.id = FoodAnimal.food_id

create proc proc_view_food_animal
	@animalId int
as
begin
	select Animal.name 'Ten Dong Vat', FoodType.name 'Ten Thuc An'
	from Animal left join FoodAnimal on Animal.id = FoodAnimal.animal_id
		left join FoodType on FoodType.id = FoodAnimal.food_id
	where Animal.id = @animalId
end

exec proc_view_food_animal 1
exec proc_view_food_animal 2

---- Index & Trigger ----
select * from Room
select * from Animal
select * from FoodType
select * from FoodAnimal

select * from Room
where name = 'R01'

-- danh index -> column -> thuong xuyen dung trong cau lenh where clustered index (duy nhat 1 column dc danh -> primary key) & nonclustered index
---- Can than khi chon column -> danh index: it bi thay doi noi dung
create index index_name on Room (name)
go

create clustered index index_animal_name on Animal (name)
go

create nonclustered index index_animal_name on Animal (name)
go

drop index index_animal_name on Animal
go

-- Trigger: insert, update, delete
select * from Room

insert into Room(id, name, max)
values
(7, 'R07', -5),
(8, 'R08', 15),
(9, 'R09', 25)
go

insert into Room(id, name, max)
values
(6, 'R01', 5)

update Room set max = -5 where id = 6

delete from Room where id >= 6

delete from Room where id = 6

-- Muon -> khi insert max <= 0 => bao error -> ko cho chen thanh cong
---- C1. Dat dieu kien check cho column: max -> nen dung (insert, update)
---- C2. Trigger
create trigger trigger_check_max on Room
for insert
as
begin
	if (select max from inserted) <= 0
	begin
		print N'Khong duoc phep chen max < 0'
		rollback transaction
	end
end

alter trigger trigger_check_max on Room
for insert
as
begin
	if (select count(*) from inserted where max <= 0) > 0
	begin
		print N'Khong duoc phep chen max < 0'
		rollback transaction --Huy lenh insert -> khong cho chen thanh cong
	end
end

drop trigger trigger_check_max

-- Trigger update
create trigger trigger_update_check_max on Room
for update
as
begin
	if (select count(*) from inserted where max <= 0) > 0
	begin
		print N'Khong duoc cap nhat max < 0'
		rollback transaction --Huy lenh insert -> khong cho chen thanh cong
	end
end

---- Khong cho phep nguoi dung sua noi dung coulmn:name trong bang Room -> check ko lam dc
create trigger trigger_update_no_update_name on Room
for update
as
begin
	if update(name)
	begin
		print N'Khong duoc phep thay doi noi dung column:name'
		rollback transaction --Huy lenh insert -> khong cho chen thanh cong
	end
end

select * from Room

update Room set name = '123' where id = 1

update Room set max = 12 where id = 1

-- trigger delete: ko cho phep xoa id = 1, 2, 3, 6 -> Ung dung user
delete from Room where id = 6
delete from Room where id = 5

create trigger trigger_no_delete_id_primary on Room
for delete
as
begin
	if(select count(*) from deleted where id in (1, 2, 3, 6)) > 0
	begin
		print N'Khong duoc phep xoa ban ghi co id = 1, 2, 3, 6'
		rollback transaction
	end
end

drop trigger trigger_no_delete_id_primary