use lab1;

create table Votes (cityname varchar(50) PRIMARY KEY, votecount int);
insert into Votes (cityname , votecount) values ('Mars City One',34);
insert into Votes (cityname , votecount) values ('New Gothenburg',11);
insert into Votes (cityname , votecount) values ('Picardia',1);
update Votes set votecount = votecount+3 where cityname='New Gothenburg';

select * from Votes;

set @remaining_votes = 1337;
select @remaining_votes;

drop procedure if exists updateVotes;

delimiter //
create procedure updateVotes()
begin
    if not exists (select * from Votes where cityname = 'cityname') then
        insert into Votes (cityname,votecount) select 'cityname',1337 - sum(votecount) from Votes;
    else
        update Votes set votecount = @remaining_votes where cityname = 'cityname';
    end if;
end//
delimiter ;

call updateVotes;

select * from Votes;

drop trigger if exists setRemainingVotes;

delimiter //
create trigger setRemainingVotes after insert on Votes
for each row
    begin
        set @remaining_votes = 1337 - (select sum(votecount) from Votes where cityname <> 'cityname');
    end//
delimiter ;

insert into votes values ('Ricardo',10);
insert into votes values ('Picardo',21);
call updateVotes;

select * from Votes;