#TEST to check that all modifications in fusion table are modified on eurocae model:

'''
table geofences:
    sequential_id: it cant be modified
    identifier: varchar 10
    layer_name:if the layer is not in the list, back would create a new layer. the list available is {DenselyPopulatedRegion, power_plant_volume, protected_zone_volume, schools_volume, townhall_volume, natural_zone_volume, hospitals_volume, TemporartFlightRestriction, aerodromes_volume, Emergency, aerodromes_volume, ControlledAirspace, rail_station_volume, SpecialUseAirspace, military_volume, generator_volume}
    geom_buffer: multipolygon
    name_feature: 
    lower_limit
    upper_limit
    lower_vertical_reference: one between AGL AMSL WGS84
    upper_vertical_reference: one between AGL AMSL WGS84
    geom_center: GeometryCollection
    geom_radius: int if negative line buffer if positive, it is the radious of a circle
    contact: only for osm 
    url: only for osm
    oid: only for osm
    country_id: from ISO 3166-1 alpha 3 
    region
    restriction: json with this structure {"type": "code_restriction_type", "values": [{"restriction_type_name": "REQ_AUTHORISATION"}, {"restriction_type_name": "CONDITIONAL", "conditions": [{"condition_name": "Manual authorization required from XXX"}, {"condition_name": "Allowed Operators/drones", "operators_allowed": [{"operator_id": "1", "drones_allowed": [{"drone_id": "1"}]}]}]}]}
    code_zone_reason: json with this structure {"type": "code_zone_reason", "values": [{"code_zone_type_name": "Population"}, {"code_zone_type_name": "Nature"}, {"code_zone_type_name": "Noise"}]}
    code_uspace_class_type: json with this structure {"type": "code_uspace_class_type", "values": [{"uspace_class_type_name": "NDZ"}, {"uspace_class_type_name": "EDZp"}, {"uspace_class_type_name": "EDZm"}]}
    volume_limitations: json with this structure {"type": "volume_limitation", "values": [{"volume_limitation_name": "Certification Type 0"}, {"volume_limitation_name": "Certification Type 3"}, {"volume_limitation_name": "Operation_type Specific"}]}
    code_zone_type: one between this list {COMMON, CUSTOMIZED, MODIFIED}
    time_period: json with this structure {"type": "time_period", "values": [{"start_date": "01-Jan-2000 ", "end_date": "01-Dec-2050 ", "permanent": true, "daily_periods": [{"start_time": "01-Jan-1900 (09:00:00.000000)", "end_time": "01-Jan-1900 (15:00:00.000000)", "week_day": "Fryday"}]}]}
    message: 
    data_capture_prohibition: bool
    author_metadata
    intervalbefore: varchar 11
    remote_id: cant be modified directly, it has to be done by restriction property
    geoawareness_required: cant be modified directly, it has to be done by restriction property
    creation_datatime
    update_datetime
    authorityfrom: json with the structure   {"type": "authorityfrom", "name": "name of authority", 'service', "authority.service", 'contact_name', "authority.contact_name", 'site_url', "authority.site_url", 'email', "authority.email", 'phone', "authority.phone" }
    notificationto: json with this structure {"type": "notificationto", "name": "name of authority", 'service', "authority.service", 'contact_name', "authority.contact_name", 'site_url', "authority.site_url", 'email', "authority.email", 'phone', "authority.phone" }
'''
from pgConnection import PgConnection
import os
import datetime
import time

#0. CONECTION:
database="newgis"
pgconn = PgConnection(database)
execCase=2

prop=[
    'identifier', 
    'layer_name', 
    'geom_buffer',
    'name_feature',
    'lower_limit',
    'upper_limit',
    'lower_vertical_reference',
    'upper_vertical_reference',
    'geom_center',
    'geom_radius',
    'contact',
    'url',
    'oid',
    'country_id',
    'region',
    'restriction',
    'code_zone_reason',
    'code_uspace_class_type',
    'volume_limitations',
    'code_zone_type',
    'message',
    'data_capture_prohibition',
    'author_metadata',
    'intervalbefore',
    'creation_datetime',
    'authorityfrom',
    'notificationto',
    'time_period',
    'active',
]
values=[
    'example', 
    'sensitive_volume', 
    'MULTIPOLYGON(((-2.8001205514147 37.8923252965836,-2.67564686446385 37.9656428983705,-2.72231818391457 37.8916144355108,-2.72428122429462 37.8879845103478, -2.70226559709314 37.8605123144506,-2.8001205514147 37.8923252965836)))',
    'example change name',
    100,
    100,
    'AMSL',
    'AMSL',
    'GEOMETRYCOLLECTION(POINT(-1.15020589261845 38.4472871307411))',
    30,
    'example',
    'example',
    '66666555',
    'FRA',
    2,
    '{"type": "code_restriction_type", "values": [{"restriction_type_name": "REQ_AUTHORISATION"}, {"restriction_type_name": "CONDITIONAL", "conditions": [{"condition_name": "Remote ID equipment requiered"}, {"condition_name": "Allowed Operators/drones"}]}]}',
    '{"type": "code_zone_reason", "values": [{"code_zone_type_name": "Population"}, {"code_zone_type_name": "Nature"}, {"code_zone_type_name": "Noise"}]}',
    '{"type": "code_uspace_class_type", "values": [{"uspace_class_type_name": "NDZ"}, {"uspace_class_type_name": "EDZp"}, {"uspace_class_type_name": "EDZm"}]}',
    '{"type": "volume_limitation", "values": [{"volume_limitation_name": "Certification Type 0"}, {"volume_limitation_name": "Certification Type 3"}, {"volume_limitation_name": "Operation_type Specific"}]}',
    'CUSTOMIZED',
    'example change message',
    'True',
    'author changed',
    'ljgh',
    '2021-06-26 00:00:00+01',
    '{"type": "authorityfrom", "name": "name of authority", "service": "exampleservice", "contact_name": "examplecontact_name", "site_url": "https://es.wikipedia.org/wiki/ISO_3166-1", "email": "email@email.com", "phone": "789456123" }',
    '{"type": "notificationto", "name": "name of authority", "service": "exampleservice", "contact_name": "examplecontact_name", "site_url": "https://es.wikipedia.org/wiki/ISO_3166-1", "email": "email@email.com", "phone": "789456123" }',
    '{"type":"every day","time_periods":[{"start_date":"2021-06-01T00:05:00+00:00","end_date":"2021-06-02T00:05:00+00:00","days":[],"slots":[]}]}',
    'True'
]
if(execCase==1):
    query='''
    UPDATE geofences
    SET 
    {} = '{}',
    {} = '{}',
    {} = ST_GeomFromText(ST_AsEWKT(ST_GeomFromText('{}',4326)::geography)),
    {} = '{}',
    {} = {},
    {} = {},
    {} = '{}',
    {} = '{}',
    {} = ST_GeomFromText(ST_AsEWKT(ST_GeomFromText('{}',4326)::geography)),
    {} = {},
    {} = '{}',
    {} = '{}',
    {} = '{}',
    {} = '{}',
    {} = {},
    {} = encode(convert_to('{}', 'UTF-8'),'base64'),
    {} = encode(convert_to('{}', 'UTF-8'),'base64'),
    {} = encode(convert_to('{}', 'UTF-8'),'base64'),
    {} = encode(convert_to('{}', 'UTF-8'),'base64'),
    {} = '{}',
    {} = '{}',
    {} = {},
    {} = '{}',
    {} = '{}',
    {} = '{}',
    {} =  encode(convert_to('{}', 'UTF-8'),'base64'),
    {} =  encode(convert_to('{}', 'UTF-8'),'base64'),
    {} =  encode(convert_to('{}', 'UTF-8'),'base64'),
    {} = {}
    WHERE sequential_id=66538;
    '''.format(
        prop[0], values[0],
        prop[1], values[1],
        prop[2], values[2],
        prop[3], values[3],
        prop[4], values[4],
        prop[5], values[5],
        prop[6], values[6],
        prop[7], values[7],
        prop[8], values[8],
        prop[9], values[9],
        prop[10], values[10],
        prop[11], values[11],
        prop[12], values[12],
        prop[13], values[13],
        prop[14], values[14],
        prop[15], values[15],
        prop[16], values[16],
        prop[17], values[17],
        prop[18], values[18],
        prop[19], values[19],
        prop[20], values[20],
        prop[21], values[21],
        prop[22], values[22],
        prop[23], values[23],
        prop[24], values[24],
        prop[25], values[25],
        prop[26], values[26],
        prop[27], values[27],
        prop[28], values[28]
    )
    
    try:
        ts = time.time()
        pgconn.cursor.execute(query)
        pgconn.connection.commit()
        te = time.time()
        timeexec=round(te - ts, 5)
        print("global update done in {} ms".format(timeexec*1000))
    except Exception as ex :
        print("ERROR: ", type(ex), ex.args)
if(execCase==2):
    querycreate='''
    INSERT INTO geofences(
    {},{},{},{},{},{},{},{},{},{},
    {},{},{},{},{},{},{},{},{},{},
    {},{},{},{},{},{},{},{}, active, remote_id, geoawareness_required)
    VALUES
    (
        '{}',
        '{}',
        ST_GeomFromText(ST_AsEWKT(ST_GeomFromText('{}',4326)::geography)),
        '{}',
        {},
        {},
        '{}',
        '{}',
        ST_GeomFromText(ST_AsEWKT(ST_GeomFromText('{}',4326)::geography)),
        {},
        '{}',
        '{}',
        '{}',
        '{}',
        {},
        encode(convert_to('{}', 'UTF-8'),'base64'),
        encode(convert_to('{}', 'UTF-8'),'base64'),
        encode(convert_to('{}', 'UTF-8'),'base64'),
        encode(convert_to('{}', 'UTF-8'),'base64'),
        '{}',
        '{}',
        {},
        '{}',
        '{}',
        '{}',
        encode(convert_to('{}', 'UTF-8'),'base64'),
        encode(convert_to('{}', 'UTF-8'),'base64'),
        encode(convert_to('{}', 'UTF-8'),'base64'),
        null,
        null,
        null
    ) REtURNING sequential_id;
    '''.format(
        prop[0],
        prop[1],
        prop[2], 
        prop[3],
        prop[4], 
        prop[5],
        prop[6], 
        prop[7], 
        prop[8], 
        prop[9], 
        prop[10],
        prop[11], 
        prop[12],
        prop[13],
        prop[14],
        prop[15],
        prop[16],
        prop[17],
        prop[18],
        prop[19],
        prop[20],
        prop[21],
        prop[22],
        prop[23],
        prop[24],
        prop[25],
        prop[26],
        prop[27],
        values[0],
        values[1],
        values[2],
        values[3],
        values[4],
        values[5],
        values[6],
        values[7],
        values[8],
        values[9],
        values[10],
        values[11],
        values[12],
        values[13],
        values[14],
        values[15],
        values[16],
        values[17],
        values[18],
        values[19],
        values[20],
        values[21],
        values[22],
        values[23],
        values[24],
        values[25],
        values[26],
        values[27],
    )

    try:
        ts = time.time()
        pgconn.cursor.execute(querycreate)
        pgconn.connection.commit()
        s = pgconn.cursor.fetchall()
        te = time.time()
        timeexec=round(te - ts, 5)
        print("create done in {} ms. sequentialid={}".format(timeexec*1000, s[0][0]))
    except Exception as ex :
        print("ERROR: ", type(ex), ex.args)



if(execCase==3):
    querydel=''' DELETE FROM geofences WHERE sequential_id=66537'''
    try:
        ts = time.time()
        pgconn.cursor.execute(querydel)
        pgconn.connection.commit()
        te = time.time()
        timeexec=round(te - ts, 5)
        print("delete done in {} ms".format(timeexec*1000))
    except Exception as ex :
        print("ERROR: ", type(ex), ex.args)