swag-shop/shop.sql

create database shop;

use shop;

create table user(
	id int primary key auto_increment,
	username varchar(64) not null unique,
	displayname varchar(64) not null,
	email varchar(64) not null unique,
	password varchar(60) not null,
	role_id int not null default 1,
	creation_date timestamp default current_timestamp,
	foreign key (role_id) references user_role(id)
) auto_increment = 23001;

create table user_role(
	id int primary key auto_increment,
	role varchar(32) not null unique
);

insert into user_role(id, role) values (1, 'normal');
insert into user_role(id, role) values (2, 'admin');

create table user_statistics(
	id int primary key,
	total_gains decimal(64,2) not null default 0.00,
	sell_count int not null default 0,
	total_spent decimal(64,2) not null default 0.00,
	purchase_count int not null default 0,
	foreign key (id) references user(id)
);

create table product(
	id int primary key auto_increment,
	seller_id int not null,
	name varchar(32) not null,
	price_pc decimal(12,2) not null,
	image blob,
	image_name varchar(64),
	foreign key (seller_id) references user(id)
) auto_increment = 13001;

create table cart(
	id int primary key,
	price_total decimal(24,2) not null default 0.00,
	item_count int not null default 0,
	foreign key (id) references user(id)
);

create table cart_item(
	id int primary key auto_increment,
	cart_id int not null,
	product_id int not null,
	count int not null,
	price_subtotal decimal(12,2) not null,
	date_added datetime default current_timestamp,
	foreign key (cart_id) references cart(id),
	foreign key (product_id) references product(id)
);

create table purchase(
	id int primary key auto_increment,
	user_id int not null,
	purchase_time datetime default current_timestamp,
	price_total decimal(24,2) not null default 0.00,
	item_count_total int not null default 0,
	was_successful tinyint(1) not null default 0,
	foreign key (user_id) references user(id)
);

create table purchase_item(
	id int primary key auto_increment,
	purchase_id int not null,
	product_id int not null,
	count int not null,
	price_subtotal decimal(12,2) not null,
	foreign key (purchase_id) references purchase(id),
	foreign key (product_id) references product(id)
);

-- Create / Delete statistics with user creation / deletion

create trigger after_user_insert
after insert on user
for each row
begin
	insert into user_statistics(id) values (new.id);
	insert into cart(id) values (new.id);
end;

create trigger after_user_delete
after delete on user
for each row
begin
	delete from user_statistics where id = old.id;
	delete from cart where id = old.id;
end;

-- Calculate subtotal of a cart item automatically

create trigger calculate_price_subtotal
before insert on cart_item
for each row
begin
	set new.price_subtotal = (
		select new.count * p.price_pc
		from product p
		where p.id = new.product_id
	);
end;

create trigger calculate_price_subtotal_on_update
before update on cart_item
for each row
begin
	set new.price_subtotal = (
		select new.count * p.price_pc
		from product p
		where p.id = new.product_id
	);
end;

-- Check for buying from self

create trigger check_for_buying_from_self
before insert on cart_item
for each row
begin
	declare seller_id_check int;

	select seller_id into seller_id_check
	from product
	where id = new.product_id;

	if new.cart_id = seller_id_check then
		signal sqlstate '45000'
		set message_text = 'Cannot insert into cart_item. Seller_id and cart_id must be different.';
	end if;
end;

-- Cart price updates

create trigger after_cart_insert
after insert on cart_item
for each row
begin
	update cart
	set price_total = (
		select sum(price_subtotal)
		from cart_item
		where cart_id = new.cart_id
	),
	item_count = (
		select sum(count)
		from cart_item
		where cart_id = new.cart_id
	)
	where id = new.cart_id;
end;

create trigger after_cart_update
after update on cart_item
for each row
begin
	update cart
	set price_total = (
		select sum(price_subtotal)
		from cart_item
		where cart_id = new.cart_id
	),
	item_count = (
		select sum(count)
		from cart_item
		where cart_id = new.cart_id
	)
	where id = new.cart_id;
end;

create trigger after_cart_delete
after delete on cart_item
for each row
begin
	update cart
	set price_total = ifnull(
		(select sum(price_subtotal)
		from cart_item
		where cart_id = old.cart_id),
		0.00
	),
	item_count = ifnull(
	(select sum(count)
		from cart_item
		where cart_id = old.cart_id),
		0
	)
	where id = old.cart_id;
end;

-- Update statistics on purchase
-- TODO Finish me

create trigger on_purchase_update_stats
after insert on purchase_item
for each row
begin
	declare seller_id int;
	declare buyer_id int;

	select product.seller_id
	into seller_id
	from product
	inner join purchase_item
	on new.product_id = product.id
	limit 1;

	select purchase.user_id
	into buyer_id
	from purchase
	inner join purchase_item
	on new.purchase_id = purchase.id
	limit 1;

	-- Update buyers statistics
	update user_statistics
	set total_spent = total_spent + new.price_subtotal,
	purchase_count = purchase_count + new.count
	where user_statistics.id = buyer_id;
	
	-- Update sellers statistics
	update user_statistics
	set total_gains = total_gains + new.price_subtotal,
	sell_count = sell_count + new.count
	where user_statistics.id = seller_id;

	-- Update purchase
	update purchase
	set item_count_total = item_count_total + new.count,
	price_total = price_total + new.price_subtotal
	where id = new.purchase_id;
end;