-- args save_data(0 datatypeid TEXT, 1 value TEXT, 2 id integer, 3 source text, 4 accountid integer, 
--5 language text, 6 time_start partialDate, 7 time_anchor partialDate, 8 time_end partialDate, 9 datareferenceid BIGINT, 10 precision,
--11 use source for duplicate check, 12 data origin id) 
DROP FUNCTION save_data(TEXT, TEXT, integer, text, integer, text)
CREATE OR REPLACE FUNCTION save_data(dtypeid TEXT, value TEXT, objectid integer, source text, accountid integer, language text) RETURNS BIGINT
AS $$
BEGIN
  RETURN save_data(dtypeid, value, objectid, source, accountid, language, NULL, NULL, NULL, NULL, NULL, TRUE, NULL)
END
$$ LANGUAGE plpgsql

DROP FUNCTION save_data(TEXT, TEXT, integer, text, integer, text, int, int, int, BIGINT)
CREATE OR REPLACE FUNCTION save_data(dtypeid TEXT, value TEXT, objectid INTEGER, source TEXT, accountid INTEGER, language TEXT, time_start INTEGER, time_anchor INTEGER, time_end INTEGER, data_ref_id BIGINT) RETURNS BIGINT
AS $$
BEGIN
  RETURN save_data(dtypeid, value, objectid, source, accountid, language, time_start, time_anchor, time_end, data_ref_id, NULL, TRUE, NULL)
END
$$ LANGUAGE plpgsql

DROP FUNCTION save_data(TEXT, TEXT, integer, text, INTEGER, text, INTEGER, INTEGER, INTEGER, BIGINT, double precision, BOOLEAN)
CREATE OR REPLACE FUNCTION save_data(dtypeid TEXT, value TEXT, objectid INTEGER, source TEXT, accountid INTEGER, language text, time_start INTEGER, time_anchor INTEGER, time_end INTEGER, data_ref_id BIGINT, accuracy DOUBLE PRECISION, sourcecheck BOOLEAN) RETURNS BIGINT
AS $$
BEGIN
  RETURN save_data(dtypeid, value, objectid, source, accountid, language, time_start, time_anchor, time_end, data_ref_id, accuracy, sourcecheck, 0)
END
$$ LANGUAGE plpgsql

DROP FUNCTION save_data(TEXT, TEXT, INTEGER, TEXT, INTEGER, TEXT, INTEGER, INTEGER, INTEGER, BIGINT, DOUBLE PRECISION, BOOLEAN, BIGINT);
CREATE OR REPLACE FUNCTION save_data(dtypeid TEXT, invalue TEXT, oid INTEGER, source TEXT, accountid INTEGER, lng TEXT, timestart INTEGER, timeanchor INTEGER, timeend INTEGER, data_ref_id BIGINT, accuracy DOUBLE PRECISION, sourcecheck BOOLEAN, data_org_id BIGINT) RETURNS bigint
AS $$
  dtypeid = args[0]
  import sys
  sys.path.append('/var/www/dev/www.phorio.com/misc/tables')

  # functions
  # datarank calculation
  def getDatarank(datatype, objectid, value, language):
    if datatype["datatypeid"] == "status":
      drank_query = plpy.prepare("SELECT max(drank) AS drank FROM data WHERE objectid = $1 AND datatypeid = 'status'", ["int"])
      dranks = plpy.execute(drank_query, [objectid])
      if dranks:
        return dranks[0]["drank"]      
    
    if datatype["multivalue"]:
      if datatype["language_dependent"]:
        drank_query = plpy.prepare("SELECT drank FROM data WHERE objectid = $1 AND datatypeid = $2 AND language = $4 AND value = $3 AND visible = TRUE", ["int", "text", "text", "text"])
        dranks = plpy.execute(drank_query, [objectid, datatype["datatypeid"], value, language])
        if dranks:
          return dranks[0]["drank"]
      else:
        drank_query = plpy.prepare("SELECT drank FROM data WHERE objectid = $1 AND datatypeid = $2 AND value = $3 AND visible = TRUE", ["int", "text", "text"])
        dranks = plpy.execute(drank_query, [objectid, datatype["datatypeid"], value])
        if dranks:
          return dranks[0]["drank"]
    else:
      if datatype["language_dependent"]:
        drank_query = plpy.prepare("SELECT drank FROM data WHERE objectid = $1 AND datatypeid = $2 AND visible = TRUE AND language = $3", ["int", "text", "text"])
        dranks = plpy.execute(drank_query, [objectid, datatype["datatypeid"], language])
        if dranks:
          return dranks[0]["drank"]
      else:
        drank_query = plpy.prepare("SELECT drank FROM data WHERE objectid = $1 AND datatypeid = $2 AND visible = TRUE", ["int", "text"])
        dranks = plpy.execute(drank_query, [objectid, datatype["datatypeid"]])
        if dranks:
          return dranks[0]["drank"]
    return 0

  if dtypeid == 'cityid':
    dtypeid = 'regionid'

  from PartialDate import PartialDate

  # finding objecttype
  objecttype_query = plpy.prepare("SELECT * FROM object WHERE objectid = $1", ["int"])
  objecttypes = plpy.execute(objecttype_query, [oid])
  objecttype = objecttypes[0]["objecttypeid"]

  try:
    # taking valuetype
    subquery = "position($2 IN array_to_string(objecttypeid, ' ')) > 0"
    datatype_query = plpy.prepare("SELECT * FROM datatype NATURAL JOIN valuetype WHERE datatypeid = $1 AND (("+subquery+") OR objecttypeid = array[]::text[])", ["text", "text"])
    datatypes = plpy.execute(datatype_query, [dtypeid, objecttype])
    if datatypes.nrows() > 0:
      datatype = datatypes[0]
    else:
      return 0

    # testing the subtype if necessary
    if datatype["objecttypeid"]:
      subtype = datatype["objecttypeid"][0].partition(".")[2]
      if subtype:
        subtype_query = plpy.prepare("SELECT dataid FROM data WHERE objectid = $1 AND position( $2 IN value) > 0 AND visible = TRUE", ["int", "text"])
        subtypes = plpy.execute(subtype_query, [oid, subtype])
        if not (subtypes.nrows() > 0):
          plpy.notice("no subtype correctly set")
          plpy.notice("query:SELECT dataid FROM data WHERE objectid = "+str(oid)+" AND position( "+str(subtype)+" IN value) > 0 AND visible = TRUE")
          return 0

  except (IndexError, NameError) as e:
    plpy.notice("SELECT * FROM datatype NATURAL JOIN valuetype WHERE datatypeid = '"+dtypeid+"' AND (("+subquery+") OR objecttypeid = array[]::text[])")
    plpy.notice(e)
    return 0

  # finding source id if necessary
  try:
    sourceid = int(source)
  except TypeError:
    # source is None, so.. setting default
    sourceid = 914
  except ValueError:
    source_find = plpy.prepare("SELECT sourceid FROM sources WHERE sourcetext = $1", ["text"])
    sourceids = plpy.execute(source_find, [source.lower().strip()])
    if sourceids.nrows() > 0:
      sourceid = sourceids[0]["sourceid"]
    else:
      sourceid = None

  # calculating datarank
  if sourceid > 0:
    drank_prep = plpy.prepare("SELECT least(accountcrank, srank) AS rank FROM account, source WHERE account.accountid = $1 AND sourceid = $2",["int", "int"])
    dranks = plpy.execute(drank_prep,[accountid, sourceid])
  else:
    drank_prep = plpy.prepare("SELECT accountcrank AS rank FROM account WHERE accountid = $1",["int"])
    dranks = plpy.execute(drank_prep,[accountid])
  drank = dranks[0]["rank"]
  if drank == None:
    drank = 3
  elif timeend and (drank > 4) and (PartialDate(timeend) > PartialDate(None)):
    drank = 4

  # checking the visibility flag and replacing boolean
  replace = True
  # TODO with multivalue, language usw
  if invalue:
    visible = True
  else:
    visible = False


  # checking if given row is identical to already existing one and returning drank, language dependent change
  if datatype["language_dependent"]:
    placecounter = 5
    query = "SELECT dataid FROM data WHERE objectid = $1 AND language = $2 AND value = $3 AND datatypeid = $4 AND visible = TRUE"
    queryparams = [oid,lng,invalue,dtypeid]
    querytypes = ["int", "char(2)", "text", "text"]
  else:
    placecounter = 4
    query = "SELECT dataid FROM data WHERE objectid = $1 AND value = $2 AND datatypeid = $3 AND visible = TRUE"
    queryparams = [oid,invalue,dtypeid]
    querytypes = ["int", "text", "text"]

  if source and sourcecheck:
    query += " AND source = $"+str(placecounter)
    placecounter += 1
    queryparams = queryparams + [source]
    querytypes = querytypes + ["text"]

  if timestart:
    query += " AND time_start = $"+str(placecounter)
    placecounter += 1
    queryparams = queryparams + [timestart]
    querytypes = querytypes + ["int"]

  if timeanchor and sourcecheck:
    query += " AND time_anchor = $"+str(placecounter)
    placecounter += 1
    queryparams = queryparams + [timeanchor]
    querytypes = querytypes + ["int"]

  if timeend and sourcecheck:
    query += " AND time_end = $"+str(placecounter)
    placecounter += 1
    queryparams = queryparams + [timeend]
    querytypes = querytypes + ["int"]

  check_duplicate = plpy.prepare(query, querytypes)
  duplicates = plpy.execute(check_duplicate, queryparams)
  if duplicates.nrows() > 0:
    # identical row(s) found, returning one and exiting
    return duplicates[0]["dataid"]


  # checking timeline overlap and determining replace boolean
  placecounter = 3
  query = "SELECT dataid FROM data WHERE objectid = $1 AND datatypeid = $2 AND visible = TRUE "
  queryparams = [oid,lng,invalue,dtypeid]
  querytypes = ["int", "char(2)", "text", "text"]

  if datatype["language_dependent"]:
    query += " AND language = $"+str(placecounter)
    placecounter += 1
    queryparams = queryparams + [lng]

  if datatype["multivalue"]:
    query += " AND value = $"+str(placecounter)
    placecounter += 1
    queryparams = queryparams + [invalue]

  # subquery for the interval min/max

  # TODO: the big beef, interval overlap checking
  if timestart:
    query += " AND (time_end > $"+str(placecounter)+" OR time_anchor > $"+str(placecounter)+")"
    placecounter += 1
    queryparams = queryparams + [timestart]
    querytypes = querytypes + ["int"]

  if timeanchor:
    query += " AND time_anchor > $"+str(placecounter)
    placecounter += 1
    queryparams = queryparams + [timeanchor]
    querytypes = querytypes + ["int"]

  if timeend:
    query += " AND time_end = $"+str(placecounter)
    placecounter += 1
    queryparams = queryparams + [timeend]
    querytypes = querytypes + ["int"]

  # todo test
  #overlapping_timelines = plpy.prepare(query, querytypes)
  #overlaps = plpy.execute(overlapping_timelines, queryparams)
  #if overlaps.nrows() > 0:
    # identical row(s) found, returning one and exiting
  #  overlapids = [x["dataid"] for x in oo]

  # drank calculation
  oldrank = getDatarank(datatype, oid, dtypeid, lng)


  # logic for dataranks
  if oldrank and oldrank > drank:
    if datatype["datatypeid"] == "status":
      check_old_status = plpy.prepare("SELECT value FROM data WHERE visible = TRUE AND objectid = $1 AND datatypeid = 'status'", ["int"])
      oldstatuses = plpy.execute(check_old_status, [oid])
      oldstatus = oldstatuses[0]["value"]
      if invalue < oldstatus and ((oldstatus != 'status.3.4' or invalue < 'status.3') and (oldstatus != 'status.4.2' or invalue != 'status.4.1')):
        replace = False
        visible = False
    else:
      replace = False
      visible = False

  # special handling of special fields
  if datatype["datatypeid"] == "event":
    # changing all same event other entries to invisible, except multievents
    event_visible_change = plpy.prepare("UPDATE data SET visible = FALSE WHERE objectid = $1 AND datatypeid = 'event' AND value = $2 AND NOT (value > 'event.2' AND value < 'event.3' AND value != 'event.2.02')", ["int", "text"])
    plpy.execute(event_visible_change, [oid, invalue])

  # if replacing value and type is not multivalue and timeline is ok, we set others non-visible
  elif replace:
    dataref = None
    if datatype["multivalue"]:
      # setting same reference line values to non-visible
      if datatype["language_dependent"]:
        # same reference line and same language
        if data_ref_id and data_ref_id > 0:
          visible_update = plpy.prepare("UPDATE data SET visible = FALSE WHERE (datareferenceid = $1 OR dataid = $2) AND language = $3 AND objectid = $4 RETURNING datareferenceid", ["bigint", "bigint", "char(2)", "int"])
          dataref = plpy.execute(visible_update, [data_ref_id, data_ref_id, lng, oid])
        else:
          # multivalue within language, only setting invisible with same value.. this should have been caught on duplicates part also
          visible_update = plpy.prepare("UPDATE data SET visible = FALSE WHERE language = $1 AND objectid = $2 AND datatypeid = $3 AND value = $4 RETURNING datareferenceid", ["char(2)", "int", "text", "text"])
          dataref = plpy.execute(visible_update, [lng, oid, dtypeid, invalue])
      else:
        if data_ref_id and data_ref_id > 0:
          visible_update = plpy.prepare("UPDATE data SET visible = FALSE WHERE (datareferenceid = $1 OR dataid = $2) AND objectid = $3 RETURNING datareferenceid", ["bigint", "bigint", "int"])
          dataref = plpy.execute(visible_update, [data_ref_id, data_ref_id, oid])
        else:
          visible_update = plpy.prepare("UPDATE data SET visible = FALSE WHERE objectid = $1 AND datatypeid = $2 AND value = $3 RETURNING datareferenceid", ["int", "text", "text"])
          dataref = plpy.execute(visible_update, [oid, dtypeid, invalue])
    else:
      if datatype["language_dependent"]:
        # setting others as non-visible
        if lng == None:
          visible_update = plpy.prepare("UPDATE data SET visible = FALSE WHERE objectid = $1 AND datatypeid = $2 AND language IS NULL RETURNING datareferenceid", ["int", "text"])
          dataref = plpy.execute(visible_update, [oid, dtypeid])
        else:
          visible_update = plpy.prepare("UPDATE data SET visible = FALSE WHERE objectid = $1 AND datatypeid = $2 AND language = $3 RETURNING datareferenceid", ["int", "text", "char(2)"])
          dataref = plpy.execute(visible_update, [oid, dtypeid, lng])
      else:
        # setting others as non-visible
        visible_update = plpy.prepare("UPDATE data SET visible = FALSE WHERE objectid = $1 AND datatypeid = $2 RETURNING datareferenceid", ["int", "text"])
        dataref = plpy.execute(visible_update, [oid, dtypeid])

    # if found datareference, we check if referred data row is also not visible.. 
    if dataref and dataref[0]["datareferenceid"]:
      ref_update = plpy.prepare("UPDATE data SET visible = FALSE WHERE dataid = $1", ["bigint"])
      plpy.execute(ref_update, [dataref[0]["datareferenceid"]])

  # insert to data
  # plpy.notice("drank:"+drank)
  if data_org_id and data_org_id > 0:
    dataref = data_org_id
  else:
    dataref = data_ref_id

  data_insert = plpy.prepare("INSERT INTO data (objectid, datatypeid, value, source, sourceid, accountid, language, time_start, time_anchor, time_end, drank, visible, datareferenceid) VALUES($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13) RETURNING dataid", ["int", "text", "text", "text", "int", "int", "char(2)", "int", "int", "int", "int", "boolean", "bigint"])
  id_rows = plpy.execute(data_insert, [oid, dtypeid, invalue, source, sourceid, accountid, lng, timestart, timeanchor, timeend, drank, visible, dataref])
  id = id_rows[0]["dataid"]

  # post-insert triggers for some datatypes
  if datatype["datatypeid"] == 'name_other':
    name_update = plpy.prepare("SELECT set_object_vectors($1)", ["int"])
    plpy.execute(name_update, [oid])
  #elif datatype["datatypeid"] in set(['address_side','address_main','address_virtual']):
    # copying address
    #address_copy = plpy.prepare("SELECT address_copy($1)", ["bigint"])
    #plpy.execute(address_copy, [id])

    


  # if no copy to flat table, stopping here
  if not datatype["datacopy"] or not replace:
    return id

  # #################################################################################### #
  # saving logic to region,company,structure,company_role,street_connection,module table #
  # #################################################################################### #

  # done in another copy prpcedure
  # copy_query = plpy.prepare("SELECT data_copy($1, $2)", ["int", "text"])
  # plpy.execute(copy_query, [oid, dtypeid])

  # # # # # # # # # # # # #
  # function definitions  #
  # # # # # # # # # # # # #

  def saveRegions(regions, objectid, objecttypeid):
    # temporary variable to keep track if edition was found among parents
    #is_edition_among_parents = False

    # copying to connection table
    for parent_region in regions:
      insert_query = plpy.prepare("INSERT INTO object_region (regionid, objectid) SELECT $1 AS regionid, $2 AS objectid WHERE NOT EXISTS (SELECT 1 FROM object_region WHERE regionid = $3 AND objectid = $4)", ["int", "int", "int", "int"])
      plpy.execute(insert_query, [parent_region["id"], objectid, parent_region["id"], objectid])

      # edition id must be currently copied also to structure table for indexing -- temporary solution until materialized views are in place
      #if parent_region["regiontypeid"] == 17 and objecttypeid in set(['structure', 'company']):
      #  insert_query = plpy.prepare("UPDATE "+objecttype+" SET editionid = $1 WHERE id = $2", ["int", "int"])
      #  plpy.execute(insert_query, [parent_region["id"], objectid])
      #  is_edition_among_parents = True

      # city id must be currently copied also to company table for indexing -- temporary solution until materialized views are in place
      # better is regiongroupid == 6, but currently regiontypeid is only returned
      if parent_region["regiontypeid"] == 7 and objecttypeid in set(['company']):
        insert_query = plpy.prepare("UPDATE "+objecttype+" SET cityid = $1 WHERE id = $2", ["int", "int"])
        plpy.execute(insert_query, [parent_region["id"], objectid])

    # if edition was not among parents, we better set editionid in structure to NULL
    #if not is_edition_among_parents and objecttypeid in set(['structure', 'company']):
    #  insert_query = plpy.prepare("UPDATE "+objecttype+" SET editionid = NULL WHERE id = $1", ["int"])
    #  plpy.execute(insert_query, [objectid])
    
    return None

  # NoneType query results to int = 0
  def to_int(value, default=0):
    try:
      return int(value)
    except TypeError:
      return default

  # column name
  if datatype["language_dependent"] == 1 and lng:
    column_name = datatype["datatypeid"]+"_"+lng
  elif datatype["language_dependent"] == 2 and lng:
    try:
      # taking valuetype
      lng_query = plpy.prepare("SELECT script_equivalent AS lng FROM language WHERE isocode = $1", ["text"])
      lngs = plpy.execute(lng_query, [lng])
      script_lng = lngs[0]
    except (IndexError, NameError) as e:
      plpy.notice("SELECT script_equivalence FROM language WHERE isocode = "+lng)
      plpy.notice(e)
      return 0
    column_name = datatype["datatypeid"]+"_"+script_lng['lng']
  else:
    column_name = datatype["datatypeid"]

  # updating structure/region/company tables
  if datatype["datatypeid"] == 'pinpoint':
    # converting pinpoint value to geom binary for updating to structure table
    point = "CAST(ST_PointFromText('POINT("+invalue+")',4326) AS TEXT)"
    insert_query = plpy.prepare("UPDATE "+objecttype+" SET "+column_name+" = "+point+" WHERE id = $1", ["int"])
    plpy.execute(insert_query, [oid])

    # finding boundaries
    boundary_query = plpy.prepare("WITH RECURSIVE parents (id, parent_id, regiontypeid, regiongroupid) AS (SELECT id, parent_id, regiontypeid, regiongroupid FROM region WHERE id IN (SELECT id FROM region WHERE ST_Contains(boundary,(SELECT pinpoint FROM structure WHERE id = $1))) UNION ALL SELECT b.id, b.parent_id, b.regiontypeid, b.regiongroupid FROM parents a, region b WHERE b.id = a.parent_id OR a.id = ANY(b.defining_regions)) SELECT DISTINCT id, regiontypeid, regiongroupid FROM parents", ["int"])
    regions = plpy.execute(boundary_query, [oid])

    # saving regions in function
    saveRegions(regions, oid, objecttype)

  elif datatype["datatypeid"] == 'parent_id' and objecttype == 'street':
    # saving parent also to object_region table, but without recursive parents
    delete_query = plpy.prepare("DELETE FROM object_region WHERE objectid = $1", ["int"])
    plpy.execute(delete_query, [oid])
    parents_query = plpy.prepare("WITH RECURSIVE parents (id, parent_id, regiontypeid, regiongroupid) AS (SELECT id, parent_id, regiontypeid, regiongroupid FROM region WHERE id = $1 UNION ALL SELECT b.id, b.parent_id, b.regiontypeid, b.regiongroupid FROM parents a, region b WHERE (a.parent_id = b.id OR a.id = ANY(b.defining_regions)) AND a.regiontypeid <> 7) SELECT DISTINCT id, regiontypeid, regiongroupid FROM parents", ["int"])
    parents = plpy.execute(parents_query, [invalue])

    # saving regions in function
    saveRegions(parents, oid, objecttype)

    # updating parent
    insert_query = plpy.prepare("UPDATE street SET parent_id = $1 WHERE id = $2", ["int", "int"])
    plpy.execute(insert_query, [invalue, oid])

  elif datatype["datatypeid"] == 'regionid': # region special copy
    # finding all parental links including defining regions and direct parents
    parent_query = plpy.prepare("WITH RECURSIVE parents (id, parent_id, regiontypeid, regiongroupid) AS (SELECT id, parent_id, regiontypeid, regiongroupid FROM region WHERE id = $1 UNION ALL SELECT b.id, b.parent_id, b.regiontypeid, b.regiongroupid FROM parents a, region b WHERE a.parent_id = b.id OR a.id = ANY(b.defining_regions)) SELECT DISTINCT id, regiontypeid, regiongroupid FROM parents", ["int"])
    parents = plpy.execute(parent_query, [invalue])

    # setting old region links to not visible using the received regiontype list
    region_visible_set = plpy.prepare("UPDATE data SET visible = FALSE WHERE objectid = $1 AND datatypeid = 'regionid' AND value != $2", ["int", "text"])
    plpy.execute(region_visible_set, [oid, invalue])

    # deleting old connection table links
    region_remove_query = plpy.prepare("DELETE FROM object_region WHERE objectid = $1", ["int"])
    plpy.execute(region_remove_query, [oid])

    saveRegions(parents, oid, objecttype)

  elif datatype["datatypeid"] == 'event': # event special copy
    # updating all event things
    status_update = plpy.prepare("SELECT data_copy($1, 'event')", ["int"])
    res = plpy.execute(status_update, [oid])

  # boundary data
  elif datatype["datatypeid"] == 'boundary' and objecttype == 'region':
    # copying boundary to region table
    copy_query = plpy.prepare("UPDATE region SET boundary = '$1' WHERE id = $2", ["int", "int"])
    plpy.execute(copy_query, [invalue, oid])

    # assigning links to structures
    

  # special company role connection
  elif datatype["datatypeid"] == 'company_role_connection':
    set_query = plpy.prepare("SELECT data_copy($1, $2, $3)", ["int", "text", "text"])
    plpy.execute(set_query, [oid, 'company_role_connection', invalue])

  # special company relation
  elif datatype["datatypeid"] == 'company_relation':
    set_query = plpy.prepare("SELECT data_copy($1, $2)", ["int", "text"])
    plpy.execute(set_query, [oid, dtypeid])

  # special street connection
  elif datatype["datatypeid"] == 'street_connection':
    set_query = plpy.prepare("SELECT data_copy($1, $2)", ["int", "text"])
    plpy.execute(set_query, [oid, dtypeid])

  # special recognition connection
  elif datatype["datatypeid"] == 'recognition_connection':
    set_query = plpy.prepare("SELECT data_copy($1, $2)", ["int", "text"])
    plpy.execute(set_query, [oid, dtypeid])

  # multivalue copy to array columns
  elif datatype["multivalue"]:
    # using data_copy
    if invalue == None:
      set_query = plpy.prepare("SELECT data_copy($1, $2)", ["int", "text"])
      plpy.execute(set_query, [oid, dtypeid])
    else:
      # normal multivalue copy
      add_query = plpy.prepare("UPDATE "+objecttype+" SET "+column_name+" = "+column_name+" || array[$1] WHERE id = $2", [datatype["valuetype_pg"], "int"])
      plpy.execute(add_query, [invalue,oid])

  # name copy
  elif datatype["datatypeid"] == 'name':
    set_query = plpy.prepare("SELECT data_copy($1, $2, $3, $4)", ["int", "text", "text", "text"])
    plpy.execute(set_query, [oid, dtypeid, invalue, lng])

  else:
    # normal data copy
    insert_query = plpy.prepare("UPDATE "+objecttype+" SET "+column_name+" = $1 WHERE id = $2", [datatype["valuetype_pg"], "int"])
    if invalue == None:
      value = None
    elif datatype["valuetype_pg"] in ['FLOAT', 'INTEGER'] and len(invalue) == 0:
      value = None
    else:
      value = invalue
    plpy.execute(insert_query, [value,oid])

    # check if flat table has the english column filled and if not, filling it
    #if datatype["datatypeid"] == "name" and lng != "en":
    #  english_check = plpy.prepare("SELECT name_en FROM "+objecttype+" WHERE id = $1", ["int"])
    #  english_column = plpy.execute(english_check, [oid])
    #  if english_column[0]["name_en"] == None:
    #    insert_query = plpy.prepare("UPDATE "+objecttype+" SET name_en = $1 WHERE id = $2", ["text", "int"])
    #    plpy.execute(insert_query, [value,oid])

  # doing postupdate triggers
  
  if visible and datatype["datatypeid"] in set(["structural_form","height_enclosure","height_architectural","floors_og"]):
    # checking structural_form for skyscraper,high-rise,usw
    structural_form_query = plpy.prepare("SELECT structural_form,height_enclosure,height_architectural,floors_og FROM structure WHERE id = $1",["int"])
    values_result = plpy.execute(structural_form_query, [oid])
    values = values_result[0]
    if values["structural_form"] and (values["structural_form"][0:22] == "structural_form.02.1.1" or values["structural_form"][0:22] == "structural_form.02.1.2"):

      # common function to save the structural form, returning the new dataid
      def saveStructuralForm(structuralForm):
        old_struct_hidden = plpy.prepare("UPDATE data SET visible = FALSE WHERE objectid = $1 AND datatypeid = 'structural_form'", ["int"])
        plpy.execute(old_struct_hidden, [oid])

        data_insert = plpy.prepare("INSERT INTO data (objectid, datatypeid, value, source, sourceid, accountid, language, time_start, time_anchor, time_end, drank, visible, datareferenceid) VALUES($1, 'structural_form', $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) RETURNING dataid", ["int", "text", "text", "int", "int", "char(2)", "int", "int", "int", "int", "boolean", "bigint"])
        id_rows = plpy.execute(data_insert, [oid, structuralForm, source, sourceid, accountid, lng, timestart, timeanchor, timeend, drank, visible, data_ref_id])

        data_update = plpy.prepare("UPDATE structure SET structural_form = $1 WHERE id = $2", ["text", "int"])
        plpy.execute(data_update, [structuralForm,oid])
        return id_rows[0]["dataid"]

      if to_int(values.get("height_enclosure", 0)) >= 300 or to_int(values.get("height_architectural", 0)) >= 300:
        if values["structural_form"] != "structural_form.02.1.1.3":
          saveStructuralForm("structural_form.02.1.1.3")
      elif to_int(values.get("height_enclosure", 0)) >= 100 or to_int(values.get("height_architectural", 0)) >= 100:
        if values["structural_form"] != "structural_form.02.1.1.2":
          saveStructuralForm("structural_form.02.1.1.2")
      elif to_int(values.get("floors_og", 0)) >= 40:
        if values["structural_form"] != "structural_form.02.1.1.2":
          saveStructuralForm("structural_form.02.1.1.2")
      elif to_int(values.get("floors_og", 0)) >= 10:
        if values["structural_form"] != "structural_form.02.1.1.1":
          saveStructuralForm("structural_form.02.1.1.1")
      elif values["structural_form"][0:22] == "structural_form.02.1.2":
        if to_int(values.get("floors_og", 0)) > 6:
          if values["structural_form"] != "structural_form.02.1.2.1":
            saveStructuralForm("structural_form.02.1.2.1")
        elif values["structural_form"] in set(["structural_form.02.1.2.7", "structural_form.02.1.2.5", "structural_form.02.1.2.10"]) and values["floors_og"] > 3:
          saveStructuralForm("structural_form.02.1.2.1")
      else:
        saveStructuralForm("structural_form.02.1.2.1")

  #elif datatype["datatypeid"] == "name" and objecttype in set(['structure', 'company']):
  #  copy_name_query = plpy.prepare("UPDATE "+objecttype+" SET name = $1 WHERE id = $2", ["text", "int"])
  #  plpy.execute(copy_name_query, [value, oid])

  elif datatype["datatypeid"] == "name" and objecttype in set(['region']):
    check_english_name = plpy.prepare("SELECT name_en AS name FROM "+objecttype+" WHERE id = $1", ["int"])
    names = plpy.execute(check_english_name, [oid])
    if not names[0]["name"]:
      copy_name_query = plpy.prepare("UPDATE "+objecttype+" SET name_en = $1 WHERE id = $2", ["text", "int"])
      plpy.execute(copy_name_query, [value, oid])

  return id
$$ LANGUAGE plpython3u