[DroneBL] Realtime stats

CREATE OR REPLACE FUNCTION blacklist() RETURNS TRIGGER AS $BODY$
BEGIN
  IF (TG_OP='UPDATE' OR TG_OP='DELETE') THEN
    IF NOT EXISTS (SELECT 1 FROM blacklist WHERE ip=OLD.ip AND type=OLD.type AND listed=1 LIMIT 1) THEN
      UPDATE classes SET count=count-1 WHERE classid=OLD.type AND count > 0;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM blacklist WHERE ip=OLD.ip AND country_code=OLD.country_code AND listed=1 LIMIT 1) THEN
      UPDATE country_stats SET amount=amount-1 WHERE country_code=OLD.country_code AND amount > 0;
    END IF;
    IF OLD.uid_add IS NOT NULL THEN
      IF (OLD.listed = 1) THEN
        UPDATE rpckey SET entries_active=entries_active-1 WHERE id=OLD.uid_add AND entries_active > 0;
      END IF;
      UPDATE rpckey SET entries=entries-1 WHERE id=OLD.uid_add AND entries > 0;
    END IF;
  END IF;

  IF (TG_OP='UPDATE' OR TG_OP='INSERT') THEN
    IF NOT EXISTS (SELECT 1 FROM blacklist WHERE ip=NEW.ip AND type=NEW.type AND listed=1 LIMIT 1) THEN
      UPDATE classes SET count=count+1 WHERE classid=NEW.type;
    END IF;

    IF NOT EXISTS (SELECT 1 FROM blacklist WHERE ip=NEW.ip AND country_code=NEW.country_code AND listed=1 LIMIT 1) THEN
      IF NOT EXISTS (SELECT 1 FROM country_stats WHERE country_code = NEW.country_code LIMIT 1) THEN
        INSERT INTO country_stats (country_code, amount) VALUES (NEW.country_code, 1);
      ELSE
        UPDATE country_stats SET amount=amount+1 WHERE country_code=NEW.country_code;
      END IF;
    END IF;

    IF NEW.uid_add IS NOT NULL THEN
      IF (NEW.listed = 1) THEN
        UPDATE rpckey SET entries_active=entries_active+1 WHERE id=NEW.uid_add;
      END IF;
      UPDATE rpckey SET entries=entries+1 WHERE id=NEW.uid_add;
    END IF;
    RETURN NEW;
  END IF;

  RETURN OLD;
END;
$BODY$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS blacklist_update on blacklist;
CREATE TRIGGER blacklist_update AFTER UPDATE ON blacklist FOR EACH ROW EXECUTE PROCEDURE blacklist();
DROP TRIGGER IF EXISTS blacklist_insert on blacklist;
CREATE TRIGGER blacklist_insert AFTER INSERT ON blacklist FOR EACH ROW EXECUTE PROCEDURE blacklist();
DROP TRIGGER IF EXISTS blacklist_delete on blacklist;
CREATE TRIGGER blacklist_delete AFTER DELETE ON blacklist FOR EACH ROW EXECUTE PROCEDURE blacklist();