CREATE OR REPLACE FUNCTION sync_filter_advertisement() RETURNS trigger AS
$BODY$
DECLARE
  flag INT := 0;
BEGIN
  IF (NEW.is_deleted) THEN
    -- deleting, only when no other ads exist
    IF (NEW.advertisement_type_sid = 1) THEN
      PERFORM
        NULL
      FROM
        advertisement
      WHERE
        profile_sid = NEW.profile_sid AND advertisement_type_sid=1 AND NOT is_deleted AND date >= CURRENT_DATE;

      IF NOT FOUND THEN
        UPDATE
          materialisation
        SET
          filter = filter & (~ 128)
        WHERE
          profile_sid = NEW.profile_sid;
      END IF;
    END IF;

    PERFORM
      NULL
    FROM
      advertisement
    WHERE
      profile_sid = NEW.profile_sid
    AND
      NOT is_deleted
    AND
      (date = CURRENT_DATE - EXTRACT(DOW FROM CURRENT_DATE)::int + (EXTRACT(DOW FROM CURRENT_DATE)!=0)::int * 7
        OR
      date = CURRENT_DATE - EXTRACT(DOW FROM CURRENT_DATE)::int + 6);

    IF NOT FOUND THEN
      UPDATE
        materialisation
      SET
        filter = filter & (~ 256)
      WHERE
        profile_sid = NEW.profile_sid;
    END IF;
  ELSE
    -- speed date open
    IF (NEW.advertisement_type_sid = 1 AND NEW.date >= CURRENT_DATE) THEN
      flag := 128;
    END IF;

    -- weekend date
    IF (NEW.date = CURRENT_DATE - EXTRACT(DOW FROM CURRENT_DATE)::int + (EXTRACT(DOW FROM CURRENT_DATE)!=0)::int * 7
          OR
        NEW.date = CURRENT_DATE - EXTRACT(DOW FROM CURRENT_DATE)::int + 6) THEN

      flag := flag | 256;
    END IF;

    IF flag > 0 THEN
      UPDATE materialisation SET filter = filter | flag WHERE profile_sid = NEW.profile_sid;
    END IF;
  END IF;

  RETURN NEW;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;


CREATE TRIGGER sync_filter
  AFTER INSERT OR UPDATE
  ON advertisement
  FOR EACH ROW
  EXECUTE PROCEDURE public.sync_filter_advertisement();