DROP FUNCTION update_file_keywords(INTEGER) CASCADE;
CREATE OR REPLACE FUNCTION update_file_keywords(fileid INTEGER) RETURNS BOOLEAN AS $$
 DECLARE
  oid INTEGER;
  cat TEXT[];
  otype TEXT;
 BEGIN
  SELECT file.objectid, filecategoryid, objecttypeid INTO STRICT oid, cat, otype FROM file LEFT JOIN object ON object.objectid = file.objectid WHERE id = fileid;
  IF oid > 0 AND otype IN ('structure') THEN
   IF cat IS NOT NULL THEN
    UPDATE file SET keywords_search = setweight(to_tsvector('english', COALESCE(description,'')), 'C') ||
     setweight(to_tsvector('english', COALESCE(umlaut_flatten(keywords),'')), 'A') ||
     setweight(to_tsvector('english', COALESCE(categ,'')), 'B') ||
     setweight(to_tsvector('english', COALESCE(umlaut_flatten(city.name_en), '')), 'C') ||
     setweight(to_tsvector('english', COALESCE(umlaut_flatten(country.name_en),'')), 'D') 
     || COALESCE(object.name_vector,'') || COALESCE(properties_vector,'')
     FROM object LEFT JOIN object_region AS city_conn ON (city_conn.objectid = object.objectid) 
     INNER JOIN region AS city ON (city.id = city_conn.regionid AND city.regiontypeid = 7) 
     LEFT JOIN object_region AS country_conn ON (country_conn.objectid = object.objectid) 
     INNER JOIN region AS country ON (country.id = country_conn.regionid AND country.regiontypeid = 3), 
     (SELECT array_to_string(array_agg(key_en),' ') AS categ FROM  file 
       INNER JOIN filecategory ON filecategory.id=ANY(file.filecategoryid) WHERE file.id =fileid GROUP BY file.id ) AS fcat 
     WHERE object.objectid = file.objectid AND file.id = fileid;
   ELSE
    UPDATE file SET keywords_search = setweight(to_tsvector('english', COALESCE(description,'')), 'B') ||
     setweight(to_tsvector('english', COALESCE(umlaut_flatten(keywords),'')), 'A') ||
     setweight(to_tsvector('english', COALESCE(umlaut_flatten(city.name_en), '')), 'C') ||
     setweight(to_tsvector('english', COALESCE(umlaut_flatten(country.name_en),'')), 'D') 
     || COALESCE(object.name_vector,'') || COALESCE(properties_vector,'')
     FROM object LEFT JOIN object_region AS city_conn ON (city_conn.objectid = object.objectid) 
     INNER JOIN region AS city ON (city.id = city_conn.regionid AND city.regiontypeid = 7) 
     LEFT JOIN object_region AS country_conn ON (country_conn.objectid = object.objectid) 
     INNER JOIN region AS country ON (country.id = country_conn.regionid AND country.regiontypeid = 3)
    WHERE object.objectid = file.objectid AND file.id = fileid;
   END IF;
  ELSE
   IF cat IS NOT NULL THEN
    UPDATE file SET keywords_search = setweight(to_tsvector('english', COALESCE(description,'')), 'B') ||
     setweight(to_tsvector('english', COALESCE(umlaut_flatten(keywords),'')), 'A') ||
     setweight(to_tsvector('english', COALESCE(categ,'')), 'B') ||
     setweight(to_tsvector('english', COALESCE(umlaut_flatten(location),'')), 'C') 
    FROM (SELECT array_to_string(array_agg(key_en),' ') AS categ FROM  file
       INNER JOIN filecategory ON filecategory.id=ANY(file.filecategoryid) WHERE file.id =fileid GROUP BY file.id ) AS fcat
    WHERE file.id = fileid;
   ELSE
    UPDATE file SET keywords_search = setweight(to_tsvector('english', COALESCE(description,'')), 'B') ||
     setweight(to_tsvector('english', COALESCE(umlaut_flatten(keywords),'')), 'A') ||
     setweight(to_tsvector('english', COALESCE(umlaut_flatten(location),'')), 'C') 
    WHERE file.id = fileid;
   END IF;
  END IF;
  RETURN TRUE;
 END
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION update_file_keywords_insert() RETURNS trigger AS '
 BEGIN
  PERFORM update_file_keywords(NEW.id);
  RETURN NULL;
 END
' LANGUAGE plpgsql;

CREATE TRIGGER update_file_keywords_insert AFTER INSERT ON file FOR EACH ROW EXECUTE PROCEDURE update_file_keywords_insert();

CREATE OR REPLACE FUNCTION update_file_keywords_update() RETURNS trigger AS '
 BEGIN
  PERFORM update_file_keywords(NEW.id);
  RETURN NULL;
 END
' LANGUAGE plpgsql;

DROP TRIGGER update_file_keywords_update ON file;
CREATE TRIGGER update_file_keywords_update AFTER UPDATE OF keywords,description,objectid,location ON file FOR EACH ROW EXECUTE PROCEDURE update_file_keywords_update();

