DROP FUNCTION update_location_strings(INTEGER, TEXT);

CREATE OR REPLACE FUNCTION update_location_strings(oid INTEGER, lng TEXT) RETURNS BOOLEAN AS $$

  check_query = plpy.prepare("SELECT objectid FROM location_strings WHERE objectid = $1", ["int"])
  objcheck = plpy.execute(check_query, [oid])

  if not objcheck:
    rowins = plpy.prepare("INSERT INTO location_strings(objectid) VALUES ($1)", ["int"])
    plpy.execute(rowins, [oid])

  # fetching usages
  usage_query = plpy.prepare("SELECT categoryid AS usageid, category.name_"+lng+" AS name FROM structure INNER JOIN category ON (categoryid = ANY(structure.usage_main)) WHERE structure.id = $1", ["int"])
  usages = plpy.execute(usage_query, [oid])
  usestrings = []
  for userow in usages:
    usestrings.append("{\"usageid\":\""+userow['usageid']+"\", \"usage\":\""+userow['name'].replace("'","''")+"\"}")

  if usestrings:
    usestring = ",".join(usestrings)

  query = "SELECT '{\"region\": [{\"regionid\": '"
  query += "  ||cast(city.id AS text)||', \"name\": \"'||city.name_"+lng+"||'\", \"regiongroupid\": 6, \"regiontypeid\": '||cast(city.regiontypeid AS text)||'}, "
  query += " {\"regionid\": '||cast(state.id AS text)||', \"name\": \"'||state.name_"+lng+"||'\", \"regiongroupid\": 4, \"short_name\": \"'||"
  query += "  COALESCE(state.short_name,'')||'\", \"regiontypeid\": '||cast(state.regiontypeid as text)||'}, "
  query += " {\"regionid\": '||cast(country.id AS text)||', \"name\": \"'||country.name_"+lng+"||'\", \"regiongroupid\": 3, \"isocode2digits\": \"'"
  query += "  ||country.isocode2digits||'\", \"regiontypeid\": '||cast(country.regiontypeid AS text)||'}],"
  query += "  \"style\": \"'||stylecat.name_"+lng+"||'\""
  if usestrings:
    query += ", \"usage\": ["+usestring+"]"
  query += "}' AS loc "
  query += "FROM structure INNER JOIN object_region AS citycon ON (citycon.objectid = structure.id) INNER JOIN region AS city ON (city.id = citycon.regionid AND city.regiongroupid = 6) "
  query += " INNER JOIN object_region AS statecon ON (statecon.objectid = structure.id) INNER JOIN region AS state ON (state.id = statecon.regionid AND state.regiongroupid = 4) "
  query += " INNER JOIN object_region AS countrycon ON (countrycon.objectid = structure.id) INNER JOIN region AS country ON (country.id = countrycon.regionid AND country.regiongroupid = 3) "
  query += " LEFT JOIN category AS stylecat ON (stylecat.categoryid = structure.style) "
  query += "WHERE structure.id = $1"

  sel_query = plpy.prepare(query, ["int"])
  all = plpy.execute(sel_query, [oid])
  if all:
    jsn = all[0]["loc"]
  else:
    plpy.notice("object "+str(oid)+" location_strings failed for language "+lng)
    return False

  update_query = plpy.prepare("UPDATE location_strings SET location_"+lng+" = $1 WHERE objectid = $2", ["text", "int"])
  plpy.execute(update_query, [jsn, oid])

  return True
$$ LANGUAGE plpythonu;