DROP FUNCTION data_copy(INT)
CREATE OR REPLACE FUNCTION data_copy(objectid INT) RETURNS boolean
AS $$
  BEGIN
    RETURN data_copy(objectid, NULL, NULL, NULL);
  END
$$ LANGUAGE plpgsql

DROP FUNCTION data_copy(INT, TEXT)
CREATE OR REPLACE FUNCTION data_copy(objectid INT, datatypeid TEXT) RETURNS boolean
AS $$
  BEGIN
    RETURN data_copy(objectid, datatypeid, NULL, NULL);
  END
$$ LANGUAGE plpgsql

DROP FUNCTION data_copy(INT, TEXT, TEXT)
CREATE OR REPLACE FUNCTION data_copy(objectid INT, datatypeid TEXT, val TEXT) RETURNS boolean
AS $$
  BEGIN
    RETURN data_copy(objectid, datatypeid, val, NULL);
  END
$$ LANGUAGE plpgsql

DROP FUNCTION data_copy(INT, TEXT, TEXT, TEXT)
CREATE OR REPLACE FUNCTION data_copy(objectid INT, dtype TEXT, val TEXT, language TEXT) RETURNS boolean
AS $$
  # if value NULL, loading values from data table
  # if datatypeid is NULL then copying all datatypes of given oid

  datatypeid = dtype
  value = val

  language = args[3]

  import sys
  sys.path.append('/var/www/dev/www.phorio.com/misc/tables')

  from datatype import getDatatype

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

  def saveRegions(regions, objectid, objecttypeid):
    # 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])

      # 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])

    return None

  def saveStatus(oid):
    from PartialDate import PartialDate

    # getting the current status
    old_status_query = plpy.prepare("SELECT status FROM structure WHERE id = $1", ["int"])
    old_statuses = plpy.execute(old_status_query, [oid])
    old_status = old_statuses[0]["status"]

    # calculating new status and comparing
    new_status_query = plpy.prepare("SELECT calculate_status($1) AS status", ["int"])
    new_statuses = plpy.execute(new_status_query, [oid])
    new_status = new_statuses[0]["status"]

    if new_status and old_status != new_status:
      status_update_query = plpy.prepare("UPDATE structure SET status = $1 WHERE id = $2", ["text", "int"])
      plpy.execute(status_update_query, [new_status, oid])

  def saveConstructionStart(oid):
    time_query = plpy.prepare("SELECT time_end FROM data WHERE objectid = $1 AND datatypeid = 'event' AND visible AND value = 'event.2.01'", ["int"])
    times = plpy.execute(time_query, [oid])
    if times:
      time = times[0]["time_end"]
      if time:
        update_query = plpy.prepare("UPDATE structure SET construction_start = $1 WHERE id = $2", ["int", "int"])
        plpy.execute(update_query, [time, oid])

  def saveConstructionEnd(oid):
    time_query = plpy.prepare("SELECT time_end FROM data WHERE objectid = $1 AND datatypeid = 'event' AND visible AND value = 'event.2.02'", ["int"])
    times = plpy.execute(time_query, [oid])
    if times:
      time = times[0]["time_end"]
      if time:
        update_query = plpy.prepare("UPDATE structure SET construction_end = $1 WHERE id = $2", ["int", "int"])
        plpy.execute(update_query, [time, oid])

  def saveDemolitionEnd(oid):
    time_query = plpy.prepare("SELECT time_end FROM data WHERE objectid = $1 AND datatypeid = 'event' AND visible AND value = 'event.6.02'", ["int"])
    times = plpy.execute(time_query, [oid])
    if times:
      time = times[0]["time_end"]
      if time:
        update_query = plpy.prepare("UPDATE structure SET demolition_end = $1 WHERE id = $2", ["int", "int"])
        plpy.execute(update_query, [time, oid])

  def saveLastRenovation(oid):
    time_query = plpy.prepare("SELECT time_end FROM data WHERE objectid = $1 AND datatypeid = 'event' AND visible AND value = 'event.3.04' ORDER BY time_end DESC", ["int"])
    times = plpy.execute(time_query, [oid])
    if times:
      time = times[0]["time_end"]
      if time:
        update_query = plpy.prepare("UPDATE structure SET renovation_last = $1 WHERE id = $2", ["int", "int"])
        plpy.execute(update_query, [time, oid])

  # return value of True means the deleted bit can be set to FALSE, otherwise NULL
  def checkDeleted(datatypeid, objecttypeid, oid):
    dl = False
    if objecttypeid == "structure" and datatypeid in ["status", "structural_form", "name"]:
      del_query = plpy.prepare("SELECT status IS NOT NULL AND structural_form IS NOT NULL AND name IS NOT NULL AS dl FROM structure WHERE id = $1", ["int"])
      deleted = plpy.execute(del_query, [oid])
      dl = bool(deleted) and bool(deleted[0]["dl"])
    elif objecttypeid == "region" and datatypeid in ["name"]:
      dl = True

    if dl:
      delupdate_query = plpy.prepare("UPDATE "+objecttypeid+" SET deleted = FALSE WHERE id = $1", ["int"])
      plpy.execute(delupdate_query, [oid])
    return dl

  # if no datatype given, loading all
  all_types = None
  if not datatypeid:
    datatypes_query = plpy.prepare("SELECT DISTINCT data.datatypeid FROM data INNER JOIN datatype on datatype.datatypeid = data.datatypeid WHERE visible AND datacopy AND data.objectid = $1", ["int"])
    all_types = plpy.execute(datatypes_query, [objectid])
    datatypeid = all_types[0]["datatypeid"]

  # main loop for all datatypes
  while datatypeid:
    datatype = getDatatype(plpy, datatypeid, objectid)

    objecttype = datatype["objecttype"]

    if not value and not datatype['multivalue'] and datatypeid not in set(["event"]):
      data_query = plpy.prepare("SELECT value, language FROM data WHERE visible AND objectid = $1 AND datatypeid = $2", ["int", "text"])
      datas = plpy.execute(data_query, [objectid, datatypeid])
      if not datas:
        return 0
      value = datas[0]['value']
      language = datas[0]['language']

    # column name
    if datatype["language_dependent"] and datatype["language_dependent"] == 1 and language:
      column_name = datatype["datatypeid"]+"_"+language
    elif datatype["language_dependent"] and datatype["language_dependent"] > 1 and language:
      try:
        # taking valuetype
        lng_query = plpy.prepare("SELECT script_equivalent AS lng FROM language WHERE isocode = $1", ["text"])
        lngs = plpy.execute(lng_query, [language])
        script_lng = lngs[0]
      except (IndexError, NameError) as e:
        plpy.notice("SELECT script_equivalence FROM language WHERE isocode = "+language)
        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("+value+")',4326) AS TEXT)"
      insert_query = plpy.prepare("UPDATE "+objecttype+" SET "+column_name+" = "+point+" WHERE id = $1", ["int"])
      plpy.execute(insert_query, [objectid])

      # 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, [objectid])

      # saving regions in function
      saveRegions(regions, objectid, 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, [objectid])
      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, [value])

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

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

    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, [value])

      #parenttypes = []
      #for parent_region in parents:
      #  parenttypes.append(parent_region["id"])

      # 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, [objectid, value])

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

      saveRegions(parents, objectid, objecttype)

    #elif datatype["datatypeid"] == 'status': # status special copy
    #  saveStatus(objectid)

    elif datatype["datatypeid"] == 'event': # event special copy
      #saveStatus(objectid)

      # copying possibly, demolition_end, construction_end, construction_start, renovation_last event
      saveConstructionStart(objectid)
      saveConstructionEnd(objectid)
      saveDemolitionEnd(objectid)
      saveLastRenovation(objectid)

    # 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, [value, objectid])

      # assigning links to structures
    

    # special company role connection
    elif datatype["datatypeid"] == 'company_role_connection':
      dataid = None
      if not value:
        data_query = plpy.prepare("SELECT value, dataid, time_start, time_anchor, time_end FROM data WHERE visible AND objectid = $1 AND datatypeid = $2", ["int", "text"])
        datas = plpy.execute(data_query, [objectid, datatypeid])
      else:
        data_query = plpy.prepare("SELECT value, dataid, time_start, time_anchor, time_end FROM data WHERE visible AND objectid = $1 AND datatypeid = $2 AND value = $3", ["int", "text", "text"])
        datas = plpy.execute(data_query, [objectid, datatypeid, value])
      
      for datarow in datas:
        value = datarow['value']
        dataid = datarow['dataid']

        data_query = plpy.prepare("SELECT targetid FROM company_role WHERE dataid = $1", ["int"])
        rolerow = plpy.execute(data_query, [dataid])

        if value == None or rolerow:
          role_del = plpy.prepare("DELETE FROM company_role WHERE dataid = $1", ["bigint"])
          role_id = plpy.execute(role_del, [dataid])
          if value == None:
            continue

        # value format: "[target_id]-[company_role]-[renovation boolean smallint]"
        values = value.rsplit("-")

        # check values
        if len(values) == 2:
          values[2] = False
        elif values[2] == "1":
          values[2] = True
        else:
          values[2] = False

        role_copy = plpy.prepare("INSERT INTO company_role (companyid, targetid, company_role, renovation, time_start, time_anchor, time_end, dataid) VALUES ($1,$2,$3,$4,$5,$6,$7,$8)", ["int","int","text","boolean","int","int","int","bigint"])
        plpy.execute(role_copy, [objectid, values[0], values[1], values[2], datarow['time_start'], datarow['time_anchor'], datarow['time_end'], dataid])

      return dataid


    # company relation type
    elif datatype["datatypeid"] == 'company_relation':
      dataid = None
      if not value:
        data_query = plpy.prepare("SELECT value, dataid, time_start, time_anchor, time_end FROM data WHERE visible AND objectid = $1 AND datatypeid = $2", ["int", "text"])
        datas = plpy.execute(data_query, [objectid, datatypeid])
      else:
        data_query = plpy.prepare("SELECT value, dataid, time_start, time_anchor, time_end FROM data WHERE visible AND objectid = $1 AND datatypeid = $2 AND value = $3", ["int", "text", "text"])
        datas = plpy.execute(data_query, [objectid, datatypeid, value])
      
      for datarow in datas:
        value = datarow['value']
        dataid = datarow['dataid']

        data_query = plpy.prepare("SELECT targetid FROM company_relation WHERE dataid = $1", ["int"])
        relrow = plpy.execute(data_query, [dataid])

        if value == None or relrow:
          rel_del = plpy.prepare("DELETE FROM company_relation WHERE dataid = $1", ["bigint"])
          rel_id = plpy.execute(rel_del, [dataid])
          if value == None:
            continue

        # value format: "[target_id]-[company_relation_type]"
        values = value.rsplit("-")

        rel_copy = plpy.prepare("INSERT INTO company_relation (sourceid, targetid, company_relation_type, dataid) VALUES ($1,$2,$3,$4)", ["int","int","text","bigint"])
        plpy.execute(rel_copy, [objectid, values[0], values[1], dataid])

      return dataid


    # special street connection
    elif datatype["datatypeid"] == 'street_connection':
      data_query = plpy.prepare("SELECT dataid, value FROM data WHERE visible AND objectid = $1 AND datatypeid = 'street_connection' AND dataid NOT IN (SELECT dataid FROM street_connection WHERE targetid = $2)", ["int", "int"])
      datas = plpy.execute(data_query, [objectid, objectid])

      for row in datas:
        value = row["value"]
        dataid = row["dataid"]

        # value format: "[street_id]:[address_type]:[start_number(+ext)]:[end_number(+ext)]:[extension]"  -- normal - sign is used inside start_number fields
        values = value.rsplit(":")

        # padding needed list values to the end
        if len(values) < 5:
          for i in range(len(values),5):
            values.append(None);

        street_copy = plpy.prepare("INSERT INTO street_connection (streetid, targetid, address_start, address_end, address_type, address_extension, dataid) VALUES ($1, $2, $3, $4, $5, $6, $7)", ["int", "int", "text", "text", "text", "text", "bigint"])
        plpy.execute(street_copy, [values[0], objectid, values[2], values[3], values[1], values[4], dataid])

    # special recognition connection
    elif datatype["datatypeid"] == 'recognition_connection':
      data_query = plpy.prepare("SELECT dataid, value FROM data WHERE visible AND objectid = $1 AND datatypeid = 'recognition_connection' AND dataid NOT IN (SELECT dataid FROM recognition_connection WHERE objectid = $2)", ["int", "int"])
      datas = plpy.execute(data_query, [objectid, objectid])

      for row in datas:
        value = row["value"]
        dataid = row["dataid"]

        # value format: "[recognition_id]:[date]:[points]:[category]:[version]"
        values = value.rsplit(":")

        # padding needed list values to the end
        if len(values) < 5:
          for i in range(len(values),5):
            values.append(None);

        if values[1] == "":
          values[1] = None

        if values[2] == "":
          values[2] = None

        if values[3] == "":
          values[3] = None

        if values[4] == "":
          values[4] = None

        award_copy = plpy.prepare("INSERT INTO recognition_connection (recognitionid, objectid, dataid, given_date, points, recognitioncategoryid, recognitionversionid) VALUES ($1, $2, $3, $4, $5, $6, $7)", ["int", "int", "bigint", "int", "real", "int", "int"])
        plpy.execute(award_copy, [values[0], objectid, dataid, values[1], values[2], values[3], values[4]])

    # multivalue copy to array columns
    elif datatype["multivalue"]:
      # copying from data table
      if value == None:
        subquery = "SELECT array_agg(value) FROM data WHERE visible AND objectid = $1 AND datatypeid = $2";
        set_query = plpy.prepare("UPDATE "+objecttype+" SET "+column_name+" = ("+subquery+") WHERE id = $3", ["int", "text", "int"])
        plpy.execute(set_query, [objectid, datatypeid,objectid])
      else:
        # normal multivalue add
        add_query = plpy.prepare("UPDATE "+objecttype+" SET "+column_name+" = "+column_name+" || array[$1] WHERE id = $2", [datatype["valuetype_pg"], "int"])
        plpy.execute(add_query, [value,objectid])

    else:
      # normal data copy
      #plpy.notice("got language "+str(language)+" and ld "+str(datatype["language_dependent"])+" for dtype "+datatypeid+" and objectid "+str(objectid)+" cn "+column_name)
      insert_query = plpy.prepare("UPDATE "+objecttype+" SET "+column_name+" = $1 WHERE id = $2", [datatype["valuetype_pg"], "int"])
      if value == None:
        value = None
      elif datatype["valuetype_pg"] in ['FLOAT', 'INTEGER'] and len(value) == 0:
        value = None
      else:
        value = value
      plpy.execute(insert_query, [value,objectid])

      # check if flat table has the english column filled and if not, filling it
      #if datatype["datatypeid"] == "name" and language != "en":
      #  english_check = plpy.prepare("SELECT name_en FROM "+objecttype+" WHERE id = $1", ["int"])
      #  english_column = plpy.execute(english_check, [objectid])
      #  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,objectid])

    # doing postupdate triggers
    #plpy.notice("otype:"+objecttype)  
    if datatype["datatypeid"] == "name" and objecttype in set(['structure', 'company', 'street']):
      copy_name_query = plpy.prepare("UPDATE "+objecttype+" SET name = $1 WHERE id = $2", ["text", "int"])
      plpy.execute(copy_name_query, [value, objectid])

    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, [objectid])
      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, objectid])

    # deleted bit check and update
    checkDeleted(datatypeid, objecttype, objectid)

    datatypeid = None
    if all_types:
      datatypeid = all_types.pop()

  # main loop with all datatypes over

  return True
$$ LANGUAGE plpython3u