-- ALTER TABLE rating
-- ADD time_stamp timestamp NOT NULL
-- AFTER rating_val;

-- alter table customer
-- add column time_stamp timestamp;

-- alter table rating
-- add customer_id int;


-- alter table customer
-- drop column time_stamp;

-- insert into customer (id,name) values (1,'a');

-- alter table employee
-- add  e_name varchar(100);

-- insert into employee values (3,'seller');

-- insert into invoice  values (4,1,3,current_timestamp());

-- alter table sale
-- add categoryid int;

-- ALTER TABLE sale
-- ADD FOREIGN KEY (categoryid) REFERENCES category(id);


-- alter table sale
-- add sellerid int;

-- ALTER TABLE sale
-- ADD FOREIGN KEY (sellerid) REFERENCES sale(id);

-- alter table category
-- add average_rating float;

-- alter table category
-- add total_sale int;

-- alter table invoice
-- add total_price float;

-- alter table rating modify id int auto_increment;
-- ALTER TABLE rating
-- ALTER time_stamp SET DEFAULT current_timestamp();




-- DROP PROCEDURE avg_rating;
-- delimiter //
-- create PROCEDURE avg_rating(IN product_id int)
-- 	begin
--     declare avg_rat float;
--     select avg(rating_val) into avg_rat from rating
--     where rating.product_id=product_id;
--     select avg_rat;
--     end; //


-- call avg_rating(1);


-- DROP PROCEDURE update_rating;
-- delimiter //
-- create procedure update_rating (IN product_id integer, IN rating_value integer,IN customer_id integer)
--                BEGIN
--                insert into rating (product_id,rating_val,customer_id) values(product_id,rating_value, customer_id);
--                UPDATE product SET average_rating = (select avg_rating(product_id)) where product.id=product_id;
--                END //

-- call update_rating(2, 5,1);