-- COP Rating denorm triggers
--
-- Copyright (C) 2003 Jeff Davis
-- @author Jeff Davis <davis@xarg.net>
-- @creation-date 1/12/2003
--
-- @cvs-id $Id: ratings-triggers.sql,v 1.1 2004/03/28 21:17:14 jeffd Exp $
--
-- This is free software distributed under the terms of the GNU Public
-- License.  Full text of the license is available from the GNU Project:
-- http://www.fsf.org/copyleft/gpl.html

create function cop_ratings_ins_tr () returns opaque as '
begin
    if not exists (
        SELECT 1 
          FROM cop_rating_aggregates 
         WHERE dimension_id = new.dimension_id 
           and object_one = new.object_one 
           and object_two = new.object_two) then 

        INSERT  INTO cop_rating_aggregates (dimension_id, object_one, object_two, ratings, rating_sum, rating_ave, rated_on)
        VALUES (new.dimension_id, new.object_one, new.object_two, 1, new.rating, new.rating, now());

    else

        UPDATE cop_rating_aggregates 
           SET ratings = ratings + 1, rating_sum = rating_sum + new.rating, 
               rating_ave = (rating_sum + new.rating)/(ratings + 1), rated_on = now()
         WHERE dimension_id = new.dimension_id 
           and object_one = new.object_one 
           and object_two = new.object_two;

    end if;

    return new;
end;' language 'plpgsql';

create trigger cop_ratings_ins_tr
after insert on cop_ratings
for each row
execute procedure cop_ratings_ins_tr();

create function cop_ratings_upd_tr () returns opaque as '
begin
    UPDATE cop_rating_aggregates 
       SET rating_sum = rating_sum - coalesce(old.rating,1) + coalesce(new.rating,1), 
           rating_ave = (rating_sum - coalesce(old.rating,1) + coalesce(new.rating,1))/ratings, 
           rated_on = now()
     WHERE dimension_id = new.dimension_id 
       and object_one = new.object_one 
       and object_two = new.object_two;

    return new;

end;' language 'plpgsql';

create trigger cop_ratings_upd_tr
after update on cop_ratings
for each row
execute procedure cop_ratings_upd_tr();

-- drop function cop_ratings_del_tr() cascade;
create function cop_ratings_del_tr () returns opaque as '
begin
    UPDATE cop_rating_aggregates 
       SET ratings = (case when ratings > 0 then ratings - 1 else 0 end),
           rating_sum = (case when rating_sum - coalesce(old.rating,1) > 0 then rating_sum - coalesce(old.rating,1) else 0 end),
           rating_ave = (rating_sum - coalesce(old.rating,1))/(case when ratings > 1 then ratings - 1 else 1 end)
     WHERE dimension_id = old.dimension_id 
       and object_one = old.object_one
       and object_two = old.object_two;

    return old;
end;' language 'plpgsql';

create trigger cop_ratings_del_tr
after delete on cop_ratings
for each row
execute procedure cop_ratings_del_tr();