-- запросы для проверки триггеров
-- select * from judge_statistics;

-- insert into judge (name, category, region, federation) 
-- values ('Иван Иванов', '1 категория', 'Санкт-Петербург', 'Федерация');
-- select * from judge where name = 'Иван Иванов';
-- select * from judge_statistics where id_judge = 154;

-- insert into judge_request (name, category, region, federation, is_registered, id_competition, id_judge) 
-- values ('Иван Иванов', '1 категория', 'Санкт-Петербург', 'Федерация', true, 401, 1);
-- update judge_request set id_judge = 2 where id_judge = 1;
-- delete from judge_request where name = 'Иван Иванов';

-- delete from judge where name = 'Иван Иванов';

-- таблица для ведения статистики
create table judge_statistics (
    id_judge integer primary key,
    competition_count integer not null default 0,
    foreign key (id_judge) references judge(id_judge)
);


-- добавление судьи
create or replace function update_statistics_on_judge_insert()
returns trigger as $$
begin
    insert into judge_statistics (id_judge, competition_count)
    values (new.id_judge, 0);
    return new;
end;
$$ language plpgsql;

create trigger after_judge_insert
after insert on judge
for each row
execute function update_statistics_on_judge_insert();

-- удаление судьи
create or replace function update_statistics_on_judge_delete()
returns trigger as $$
begin
    delete from judge_statistics where id_judge = old.id_judge;
    return old;
end;
$$ language plpgsql;

create trigger after_judge_delete
after delete on judge
for each row
execute function update_statistics_on_judge_delete();

-- добавление заявки судьи
create or replace function update_statistics_on_judge_request_insert()
returns trigger as $$
begin
    update judge_statistics
    set competition_count = competition_count + 1
    where id_judge = new.id_judge;
    return new;
end;
$$ language plpgsql;

create trigger after_judge_request_insert
after insert on judge_request
for each row
execute function update_statistics_on_judge_request_insert();

-- удаление заявки судьи
create or replace function update_statistics_on_judge_request_delete()
returns trigger as $$
begin
    update judge_statistics
    set competition_count = competition_count - 1
    where id_judge = old.id_judge;
    return old;
end;
$$ language plpgsql;

create trigger after_judge_request_delete
after delete on judge_request
for each row
execute function update_statistics_on_judge_request_delete();


-- обновление заявки судьи
create or replace function update_statistics_on_judge_request_update()
returns trigger as $$
begin
    -- уменьшение счётчика у старого судьи
    update judge_statistics
    set competition_count = competition_count - 1
    where id_judge = old.id_judge;

    -- увеличение счётчика у нового судьи
    update judge_statistics
    set competition_count = competition_count + 1
    where id_judge = new.id_judge;

    return new;
end;
$$ language plpgsql;

create trigger after_judge_request_update
after update on judge_request
for each row
execute function update_statistics_on_judge_request_update();