CREATE OR REPLACE FUNCTION structural_form_channels() RETURNS trigger AS $$
 BEGIN
  DELETE FROM object_channel WHERE objectid = new.id AND channelid IN 
    (695777793,848282650,903236469, 688499811,839225522, 668152778, 182056052, 215110916, 687292558);

  -- tall buildings
  IF substr(NEW.structural_form,1,22) = 'structural_form.02.1.1' THEN
    INSERT INTO object_channel (objectid, channelid) VALUES (NEW.id, 695777793);
  END IF;

  -- skyscrapers
  IF (substr(NEW.structural_form,1,24) IN ('structural_form.02.1.1.2', 'structural_form.02.1.1.3')) THEN
    INSERT INTO object_channel (objectid, channelid) VALUES (NEW.id, 687292558);
  END IF;

  -- supertalls
  IF (substr(NEW.structural_form,1,24) = 'structural_form.02.1.1.3') THEN
    INSERT INTO object_channel (objectid, channelid) VALUES (NEW.id, 981622820);
  END IF;

  -- religious structures
  IF substr(NEW.structural_form,1,22) = 'structural_form.02.1.4' THEN
    INSERT INTO object_channel (objectid, channelid) VALUES (NEW.id, 848282650);
  END IF;

  -- churches
  IF substr(NEW.structural_form,1,24) = 'structural_form.02.1.4.1' THEN
    INSERT INTO object_channel (objectid, channelid) VALUES (NEW.id, 903236469);
  END IF;

  -- bridges
  IF substr(NEW.structural_form,1,24) = 'structural_form.02.2.2.1' THEN
    INSERT INTO object_channel (objectid, channelid) VALUES (NEW.id, 688499811);
  END IF;

  -- stadium
  IF substr(NEW.structural_form,1,22) = 'structural_form.02.1.5' THEN
    INSERT INTO object_channel (objectid, channelid) VALUES (NEW.id, 839225522);
  END IF;

  -- towers
  IF substr(NEW.structural_form,1,22) = 'structural_form.02.2.5' THEN
    INSERT INTO object_channel (objectid, channelid) VALUES (NEW.id, 668152778);
  END IF;

  -- statues and monuments
  IF substr(NEW.structural_form,1,22) = 'structural_form.02.2.3' THEN
    INSERT INTO object_channel (objectid, channelid) VALUES (NEW.id, 182056052);
  END IF;

  -- fountains
  IF substr(NEW.structural_form,1,24) = 'structural_form.02.2.3.2' THEN
    INSERT INTO object_channel (objectid, channelid) VALUES (NEW.id, 215110916);
  END IF;

  RETURN NULL;
 END
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION usage_channels() RETURNS trigger AS $$
 DECLARE
  ustring TEXT;
 BEGIN
  DELETE FROM object_channel WHERE objectid = new.id AND channelid IN (715902609,765915414,938316898,842530304,357188543);

  ustring = array_to_string(NEW.usage_main, ',');

  -- hotel channel
  IF strpos(ustring, 'usage.02.1')>0 THEN
    INSERT INTO object_channel (objectid, channelid) VALUES (NEW.id, 765915414);
  END IF;

  -- office channel
  IF strpos(ustring, 'usage.13')>0 THEN
    INSERT INTO object_channel (objectid, channelid) VALUES (NEW.id, 715902609);
  END IF;

  -- museum channel
  IF strpos(ustring, 'usage.07.2')>0 THEN
    INSERT INTO object_channel (objectid, channelid) VALUES (NEW.id, 938316898);
  END IF;

  -- library channel
  IF strpos(ustring, 'usage.09')>0 THEN
    INSERT INTO object_channel (objectid, channelid) VALUES (NEW.id, 842530304);
  END IF;  

  -- logistics channel
  IF ARRAY['usage.16.2.22','usage.16.2.5','usage.16.2.16','usage.16.2.6','usage.16.2.1','usage.16.2.4','usage.16.2.21'] && NEW.usage_main OR strpos(ustring, 'usage.18.3')>0 THEN
    INSERT INTO object_channel (objectid, channelid) VALUES (NEW.id, 357188543);
  END IF;  

  RETURN NULL;
 END
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION height_channels() RETURNS trigger AS $$
 BEGIN
  DELETE FROM object_channel WHERE objectid = new.id AND channelid IN (719139339);

  -- tall structures
  IF COALESCE(NEW.height_architectural, 0) > 100 OR COALESCE(NEW.height_tip,0) > 100 OR COALESCE(NEW.height_enclosure,0) > 100 OR COALESCE(NEW.height_mainroof,0) > 100 THEN
    INSERT INTO object_channel (objectid, channelid) VALUES (NEW.id, 719139339);
  END IF;

  RETURN NULL;
 END
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION style_channels() RETURNS trigger AS $$
 BEGIN
  DELETE FROM object_channel WHERE objectid = new.id AND channelid IN (0);

  RETURN NULL;
 END
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION mixed_channels() RETURNS trigger AS $$
 DECLARE
  feature_string TEXT;
  usagemain TEXT;
 BEGIN
  DELETE FROM object_channel WHERE objectid = new.id AND channelid IN (538709140, 576707705, 715332244);

  -- historic
  IF NEW.construction_end IS NULL OR NEW.construction_end < 19500000 THEN
    IF substr(NEW.style,1,7) = 'style.1' THEN
      INSERT INTO object_channel (objectid, channelid) VALUES (NEW.id, 538709140);
    ELSIF NEW.style IN ('style.2.1','style.2.2','style.2.3','style.2.4','style.2.6','style.2.9','style.2.13','style.3.1') 
      OR (substr(NEW.style,1,7) IN ('style.3') AND NEW.style NOT IN ('style.3.11','style.3.12')) THEN
        feature_string = array_to_string(NEW.feature,' ');
        IF (strpos(feature_string, 'feature.4.2') > 0)
          OR (strpos(feature_string, 'feature.4.4') > 0)
          OR (strpos(feature_string, 'feature.4.5') > 0) THEN
            INSERT INTO object_channel (objectid, channelid) VALUES (NEW.id, 538709140);
        END IF;
    ELSIF substr(NEW.structural_form,1,24) IN ('structural_form.02.2.3.3', 'structural_form.02.2.3.4') 
      OR NEW.structural_form IN ('structural_form.02.1.3.3', 'structural_form.02.1.3.5', 'structural_form.02.1.4.3', 'structural_form.02.2.5.3', 
        'structural_form.02.2.5.7', 'structural_form.02.2.5.8', 'structural_form.02.2.3.3', 'structural_form.02.2.3.4') THEN
          INSERT INTO object_channel (objectid, channelid) VALUES (NEW.id, 538709140);
    END IF;
  END IF;

  -- green buildings
  IF NEW.feature IS NOT NULL THEN
    feature_string = array_to_string(NEW.feature,' ');
    IF (strpos(feature_string, 'feature.2') > 0) THEN
      IF (array_length(regexp_split_to_array(feature_string, 'feature.2'),1) > 2) THEN
        INSERT INTO object_channel (objectid, channelid) VALUES (NEW.id, 576707705);
      END IF;
    END IF;
  END IF;

  -- airports
  IF NEW.structural_form IN ('structural_form.02.2.5.1', 'structural_form.02.3.3.1') THEN
    INSERT INTO object_channel (objectid, channelid) VALUES (NEW.id, 715332244);
  ELSE 
    usagemain = array_to_string(NEW.usage_main, ',');
    IF strpos(usagemain, 'usage.04.4.2.1')>0 OR strpos(usagemain, 'usage.04.4.2.2')>0 OR strpos(usagemain, 'usage.04.4.3.1')>0 THEN
      INSERT INTO object_channel (objectid, channelid) VALUES (NEW.id, 715332244);
    END IF;
  END IF;

  RETURN NULL;
 END
$$ LANGUAGE plpgsql;

DROP TRIGGER structural_form_channel_inserts ON structure;
CREATE TRIGGER structural_form_channel_inserts AFTER INSERT ON structure FOR EACH ROW EXECUTE PROCEDURE structural_form_channels();
DROP TRIGGER usage_channel_inserts ON structure;
CREATE TRIGGER usage_channel_inserts AFTER INSERT ON structure FOR EACH ROW EXECUTE PROCEDURE usage_channels();

DROP TRIGGER structural_form_channel_updates ON structure;
CREATE TRIGGER structural_form_channel_updates AFTER UPDATE OF structural_form ON structure FOR EACH ROW EXECUTE PROCEDURE structural_form_channels();
DROP TRIGGER usage_channel_updates ON structure;
CREATE TRIGGER usage_channel_updates AFTER UPDATE OF usage_main ON structure FOR EACH ROW EXECUTE PROCEDURE usage_channels();
DROP TRIGGER height_channel_updates ON structure;
CREATE TRIGGER height_channel_updates AFTER UPDATE OF height_architectural,height_tip,height_enclosure,height_mainroof ON structure FOR EACH ROW EXECUTE PROCEDURE height_channels();
DROP TRIGGER style_channel_updates ON structure;
--CREATE TRIGGER style_channel_updates AFTER UPDATE OF style ON structure FOR EACH ROW EXECUTE PROCEDURE style_channels();
DROP TRIGGER mixed_channel_updates ON structure;
CREATE TRIGGER mixed_channel_updates AFTER UPDATE OF construction_end,style,structural_form,feature ON structure FOR EACH ROW EXECUTE PROCEDURE mixed_channels();

