from datetime import datetime
import re
from shapely.wkt import loads
from shapely.geometry import shape, mapping
from B01Common import CommonSql
import datetime 
import time

class CommonVolumes(CommonSql):
    def __init__(self,pgconn):
        self._pgconn = pgconn
        self._cursor = pgconn.cursor
        
    '''
    @param str $name nombre del volumen
    @param int $lowerLimit limite inferior expresado en pies
    @param int $upperLimit limite superior expresado en pies
    @param geom $geometry geometría
    @param int $ buffer buffer en radio o de lado expresado en metros 
    @param uuid $ layer_id identificador de la capa
    '''
    def get_layer(self,layer_name):
        query=''' SELECT layer_id, geometry_codification_geometry_type FROM layers_cartography WHERE name='{}'; '''.format(layer_name)
        self._cursor.execute(query)
        layer_prop = self._cursor.fetchall()
        layer_name_geom=[]
        layer_name_geom.append(layer_prop[0][0])
        layer_name_geom.append(layer_prop[0][1])
        name=layer_name+'_volume'
        layer_name_geom.append(name)
        return layer_name_geom

    def get_volumelayer(self,layer_name):
        query=''' SELECT layer_id FROM layers_volume WHERE name='{}'; '''.format(layer_name)
        self._cursor.execute(query)
        layer_prop = self._cursor.fetchall()
        return layer_prop[0][0]

    def get_layerid(self, name):
        query='''SELECT layer_id FROM layers_volume WHERE name='{}'; '''.format(name)

        try:
            self._cursor.execute(query)
            id_l=self._cursor.fetchall()
            return id_l[0][0]
        except Exception as ex :
            print("ERROR: ", type(ex), ex.args)
            return None

    def get_features(self, layer_id, geometry_priority):
        #we have to know which type of geometry it is : 1-Point, 2-LineString, 3-Polygon
        geometry_type=''
        if geometry_priority==1: 
            print('point')
            geometry_type='geom_point'
        elif geometry_priority==2: 
            geometry_type='geom_linestring'
        elif geometry_priority==3: 
            print('geom')
            geometry_type='geom_polygon'
        
        #we have to obtain all the id_features from the view
        query_id= ''' SELECT feature_id  FROM features WHERE {} is not NULL AND layers_layer_id='{}' '''.format(geometry_type, layer_id)
        self._cursor.execute(query_id)
        features_id = self._cursor.fetchall()
        features_vec=[]
        for feature in features_id:
            query_property=''' SELECT prop1_code, prop2_code, prop3_code, prop4_code FROM features WHERE feature_id='{}' '''.format(feature[0])
            self._cursor.execute(query_property)
            properties_codes = self._cursor.fetchall()
            n=0
            for properties in properties_codes:
                n=n+1
                query_name= '''SELECT name FROM feature_columns_codification WHERE column_id ='{}'  '''.format(properties[0])
                self._cursor.execute(query_name)
                isname = self._cursor.fetchall()
                f=[]
                if isname[0][0] == 'name': 
                    query_prop=''' SELECT prop{}_value FROM features WHERE feature_id='{}';   '''.format(n,feature[0])
                    self._cursor.execute(query_prop)
                    name= self._cursor.fetchall()
                    query_geom='''SELECT ST_AsEWKT({}) as wkt FROM features WHERE feature_id='{}'; '''.format(geometry_type,feature[0])
                    self._cursor.execute(query_geom)
                    geom= self._cursor.fetchall()
                    f.append(geom[0][0])
                    f.append(name[0][0])
                    f.append(feature[0])
                    features_vec.append(f)

        print('number of geometries to insert ', len(features_vec))
        return features_vec

    def create_layer(self, name, layer_geom_type):
        query=''' SELECT layer_id FROM layers_volume WHERE name='{}'; '''.format(name)
        try:
            self._cursor.execute(query)
            layer_id = self._cursor.fetchall()
        except Exception as ex :
            print("ERROR: ", type(ex), ex.args)
        if layer_id==[]:
            query_insert= ''' INSERT INTO layers_volume(name, geometry_codification_geometry_type)
                            VALUES('{}','{}') RETURNING layer_id; '''.format(name,layer_geom_type)#layer geometry id in table cartography_layers do not have specification
            self._cursor.execute(query_insert)
            self._pgconn.connection.commit()
            new_layer_id = self._cursor.fetchall()
            return new_layer_id[0][0]
        else:
            return layer_id[0][0]

    def delete_geofencelayer(self, namelayer):
        query= '''DELETE FROM geofences WHERE layer_name='{}';'''.format(namelayer)

        try:
            self._cursor.execute(query)
            self._pgconn.connection.commit() 
            count = self._cursor.rowcount
            print(count, 'have been delete')
            
        except Exception as ex :
            print("ERROR: ", type(ex), ex.args)

    def createview(self,name, layerid):
        
        query='''CREATE MATERIALIZED VIEW {} AS
            SELECT 
            layers_volume.name layer_name,
            airspace_volume.vol_id volume_id,
            airspace_volume.geom_buffer geom_buffer,
            uas_zone_version.name name_feature,
            airspace_volume.lower_limit lower_limit, 
            airspace_volume.upper_limit upper_limit, 
            code_vertical_reference.name vertical_reference,
            features.prop2_value contact,
            features.prop3_value url,
            features.prop4_value oid,
            uas_zone_version.json_restriction_type restriction,
            uas_zone_version.json_zone_reason code_zone_reason,
            uas_zone_version.json_uspace_class_type code_uspace_class_type,
            uas_zone_version.json_volume_limitation volume_limitations,
            code_zone_type.name code_zone_type,
            uas_zone_version.code_period_type_period_type_id period_type,
            uas_zone_version.json_time_period time_period,
            uas_zone_version.message message,
            uas_zone_version.data_capture_prohibition data_capture_prohibition,
            metadata.author author_metadata,
            authority.name authority_name
            FROM 
            airspace_volume,
            features,
            layers_volume,
            uas_zone_version,
            code_zone_type,
            authority,
            metadata,
            code_vertical_reference
            WHERE 
            airspace_volume.vol_id= uas_zone_version.airspace_volume_vol_id AND
            airspace_volume.layers_layer_id = layers_volume.layer_id AND
            code_zone_type.type_code = uas_zone_version.code_zone_type_type_code AND
            authority.authority_oid = uas_zone_version.authority_authority_oid AND
            metadata.metada_id = uas_zone_version.metadata_metada_id AND
            code_vertical_reference.vertical_reference_code = airspace_volume.upper_vertical_reference AND
            layers_volume.layer_id='{}' AND
            CASE
            WHEN airspace_volume.geom_origin is NULL THEN
            features.feature_id='3ad9db77-6e92-45fb-a9d0-d520759a9712'
            ELSE 
            features.feature_id=airspace_volume.geom_origin
            END
            ;'''.format(name, layerid)
        self._cursor.execute(query)
        self._pgconn.connection.commit()

    def refresh_view(self, name_view):
        query= '''REFRESH MATERIALIZED VIEW {};'''.format(name_view)

        try: 
            self._cursor.execute(query)
            self._pgconn.connection.commit() 

        except Exception as ex :
            print("ERROR: ", type(ex), ex.args)

    def delete_view(self, name_view):
        pass

    def create_view_controlledairspace(self, name, layerid):
        query='''CREATE MATERIALIZED VIEW geoawareness_required AS
            SELECT 
            layers_volume.name layer_name,
            airspace_volume.vol_id volume_id,
            airspace_volume.geom_buffer geom_buffer,
            uas_zone_version.name name_feature,
            airspace_volume.lower_limit lower_limit, 
            airspace_volume.upper_limit upper_limit, 
            code_vertical_reference.name vertical_reference,
            features.prop2_value contact,
            features.prop3_value url,
            features.prop4_value oid,
            uas_zone_version.json_restriction_type restriction,
            uas_zone_version.json_zone_reason code_zone_reason,
            uas_zone_version.json_uspace_class_type code_uspace_class_type,
            uas_zone_version.json_volume_limitation volume_limitations,
            code_zone_type.name code_zone_type,
            uas_zone_version.code_period_type_period_type_id period_type,
            uas_zone_version.json_time_period time_period,
            uas_zone_version.message message,
            uas_zone_version.data_capture_prohibition data_capture_prohibition,
            metadata.author author_metadata,
            authority.name authority_name
            FROM 
            airspace_volume,
            features,
            layers_volume,
            uas_zone_version,
            code_zone_type,
            authority,
            metadata,
            uas_condition_expression_type,
            code_vertical_reference
            WHERE 
            airspace_volume.vol_id= uas_zone_version.airspace_volume_vol_id AND
            airspace_volume.layers_layer_id = layers_volume.layer_id AND
            code_zone_type.type_code = uas_zone_version.code_zone_type_type_code AND
            authority.authority_oid = uas_zone_version.authority_authority_oid AND
            metadata.metada_id = uas_zone_version.metadata_metada_id AND
            code_vertical_reference.vertical_reference_code = airspace_volume.upper_vertical_reference AND
            uas_condition_expression_type.uas_zone_version_uas_zone_id=uas_zone_version.uas_zone_id AND
            uas_condition_expression_type.condition_expression_type_restriction_code=1 AND
            CASE
            WHEN airspace_volume.geom_origin is NULL THEN
            features.feature_id='3ad9db77-6e92-45fb-a9d0-d520759a9712'
            ELSE 
            features.feature_id=airspace_volume.geom_origin
            END'''.format(name, layerid)
        self._cursor.execute(query)
        self._pgconn.connection.commit()

        query='''CREATE MATERIALIZED VIEW remote_id AS
            SELECT 
            layers_volume.name layer_name,
            airspace_volume.vol_id volume_id,
            airspace_volume.geom_buffer geom_buffer,
            uas_zone_version.name name_feature,
            airspace_volume.lower_limit lower_limit, 
            airspace_volume.upper_limit upper_limit, 
            code_vertical_reference.name vertical_reference,
            features.prop2_value contact,
            features.prop3_value url,
            features.prop4_value oid,
            uas_zone_version.json_restriction_type restriction,
            uas_zone_version.json_zone_reason code_zone_reason,
            uas_zone_version.json_uspace_class_type code_uspace_class_type,
            uas_zone_version.json_volume_limitation volume_limitations,
            code_zone_type.name code_zone_type,
            uas_zone_version.code_period_type_period_type_id period_type,
            uas_zone_version.json_time_period time_period,
            uas_zone_version.message message,
            uas_zone_version.data_capture_prohibition data_capture_prohibition,
            metadata.author author_metadata,
            authority.name authority_name
            FROM 
            airspace_volume,
            features,
            layers_volume,
            uas_zone_version,
            code_zone_type,
            authority,
            metadata,
            uas_condition_expression_type,
            code_vertical_reference
            WHERE 
            airspace_volume.vol_id= uas_zone_version.airspace_volume_vol_id AND
            airspace_volume.layers_layer_id = layers_volume.layer_id AND
            code_zone_type.type_code = uas_zone_version.code_zone_type_type_code AND
            authority.authority_oid = uas_zone_version.authority_authority_oid AND
            metadata.metada_id = uas_zone_version.metadata_metada_id AND
            code_vertical_reference.vertical_reference_code = airspace_volume.upper_vertical_reference AND
            uas_condition_expression_type.uas_zone_version_uas_zone_id=uas_zone_version.uas_zone_id AND
            uas_condition_expression_type.condition_expression_type_restriction_code=2 AND
            CASE
            WHEN airspace_volume.geom_origin is NULL THEN
            features.feature_id='3ad9db77-6e92-45fb-a9d0-d520759a9712'
            ELSE 
            features.feature_id=airspace_volume.geom_origin
            END'''.format(name, layerid)
        self._cursor.execute(query)
        self._pgconn.connection.commit()

    def query_numberlayers(self):

        query=''' SELECT feature_id FROM geofence_basic_layers;'''

        try:
            self._cursor.execute(query)
            layers= self._cursor.fetchall()
            return len(layers)
        except:
            return 0
        
    def getinfogeofence(self, id_layer):
        query=''' SELECT name, geofence, buffer, code_zone_reason_code_zone_reason, code_uspace_class_type_u_space_code FROM geofence_basic_layers WHERE feature_id={}; '''.format(id_layer)

        try:
            self._cursor.execute(query)
            layer_info= self._cursor.fetchall()
            return layer_info[0]
        except:
            return 'error'

    def createview_conditions(self ):
        query='''SELECT layers_volume.name AS layer_name,
                airspace_volume.vol_id AS volume_id,
                airspace_volume.geom_buffer,
                uas_zone_version.name AS name_feature,
                airspace_volume.lower_limit,
                airspace_volume.upper_limit,
                code_vertical_reference.name AS vertical_reference,
                features.prop2_value AS contact,
                features.prop3_value AS url,
                features.prop4_value AS oid,
                uas_zone_version.json_restriction_type AS restriction,
                uas_zone_version.json_zone_reason AS code_zone_reason,
                uas_zone_version.json_uspace_class_type AS code_uspace_class_type,
                uas_zone_version.json_volume_limitation AS volume_limitations,
                code_zone_type.name AS code_zone_type,
                uas_zone_version.json_time_period AS time_period,
                uas_zone_version.message,
                uas_zone_version.data_capture_prohibition,
                metadata.author AS author_metadata,
                authority.name AS authority_name
                FROM airspace_volume,
                features,
                layers_volume,
                uas_zone_version,
                code_zone_type,
                authority,
                metadata,
                code_vertical_reference,
                uas_condition_expression_type,
                uas_code_restriction_type
                WHERE airspace_volume.vol_id = uas_zone_version.airspace_volume_vol_id AND airspace_volume.layers_layer_id = layers_volume.layer_id AND code_zone_type.type_code = uas_zone_version.code_zone_type_type_code AND authority.authority_oid = uas_zone_version.authority_authority_oid AND metadata.metada_id = uas_zone_version.metadata_metada_id AND code_vertical_reference.vertical_reference_code = airspace_volume.upper_vertical_reference AND uas_condition_expression_type.condition_expression_type_restriction_code = 2 AND uas_code_restriction_type.uas_restriction_code = uas_condition_expression_type.uas_code_restriction_type_uas_restriction_code AND uas_zone_version.uas_zone_id = uas_code_restriction_type.uas_zone_version_uas_zone_id AND
                    CASE
                        WHEN airspace_volume.geom_origin IS NULL THEN features.feature_id = '3ad9db77-6e92-45fb-a9d0-d520759a9712'::uuid
                        ELSE features.feature_id = airspace_volume.geom_origin
                    END;
                        '''


    def insertIntoVolumes(self, name, lowerLimit, upperLimit, geometry, buffer,layer_id,
    f_id, message, author, country, code_zone, code_restriction, code_uspace, 
    code_zone_reason, prohibition, region,authority_authority_id, authority_notification_id,
    metadata_metadata_id,time_period_time_period_id,
    volume_limitation_def, condition_expression,upperlimitnorestriccion,period_type_id, identifier, intervalbefore, active):

        
        #INSERT IN AIRSPACE_VOLUME:
        list_indexs=[False]
        lower_limit = lowerLimit #int  NOT NULL, 
        upper_limit = upperLimit #int  NOT NULL,
        if(upper_limit>1E15):
            upper_limit='Null'
        
        query=""" SELECT ST_AsText('{}') """.format(geometry)
        try: 
            self._cursor.execute(query)
            results=self._cursor.fetchall()
            geomf=results[0][0]
        except Exception as ex :
            print("ERROR: ", type(ex), ex.args)
            return list_indexs

        #Define Radious and Center:
        [point,radious, geom_type]=self.add_PR(f_id, geomf, buffer)

        #if we use ST_MULTI, we will always obtain a multipoligon and not a poligon
        if geom_type=='Polygon':
            queryBuffer = "SELECT ST_AsEWKT(ST_Multi(ST_AsEWKT(ST_Buffer(ST_MakePolygon(ST_ExteriorRing(ST_GeomFromText('{}')))::geography, {}))))".format(geometry,buffer)
        else:
           queryBuffer = "SELECT ST_AsEWKT(ST_Multi(ST_AsEWKT(ST_Buffer(ST_GeomFromText('{}')::geography, {}))))".format(geometry,buffer)

        #buffer is in meters!
        
        try:
            self._cursor.execute(queryBuffer)
            result = self._cursor.fetchall()
            bufferGeom = result[0][0]

        except Exception as ex :
            print("ERROR: ", type(ex), ex.args)
            return list_indexs
    
        if f_id==0:
            query_insert_aerospace_v = """INSERT into airspace_volume (lower_limit,lower_vertical_reference,
            upper_limit,upper_vertical_reference, geom_buffer,layers_layer_id,active, geom_center, geom_radius) VALUES
            ({},{},{},{},ST_GeomFromText('{}'),'{}',True, ST_GeomFromText(ST_AsEWKT(ST_GeomFromText('{}',4326)::geography)), {}) RETURNING vol_id;
            """.format(lower_limit,1,upper_limit,1,bufferGeom,layer_id, point, radious)
            
            try:
                self._cursor.execute(query_insert_aerospace_v)
                self._pgconn.connection.commit()
                vol_id = self._cursor.fetchall()
                list_indexs.append(vol_id[0][0])
            except Exception as ex :
                print("ERROR: ", type(ex), ex.args)
                return list_indexs

        else:  
            query_insert_aerospace_v = """INSERT into airspace_volume (lower_limit,lower_vertical_reference,
                upper_limit,upper_vertical_reference,geom_buffer,layers_layer_id,geom_origin, active, geom_center, geom_radius) VALUES
                ({},{},{},{},ST_GeomFromText('{}'),'{}','{}','{}',  ST_GeomFromText(ST_AsEWKT(ST_GeomFromText('{}',4326)::geography)), {}) RETURNING vol_id;
                """.format(lower_limit,1,upper_limit,1,bufferGeom,layer_id, f_id, active, point, radious)
            try:
                self._cursor.execute(query_insert_aerospace_v)
                self._pgconn.connection.commit()
                vol_id = self._cursor.fetchall()
                list_indexs.append(vol_id[0][0])
            except Exception as ex :
                print("ERROR: ", type(ex), ex.args)
                return list_indexs
        
       
        #INSERT RESTRICTIONS IN UAS_ZONE_VERSION:

        country_id = country #34 # int  NOT NULL,
        name = name # varchar(100)  NOT NULL,
        code_zone_type_type_code = code_zone #1 #int  NOT NULL, 'Common', 'The Zone is provided with its default definition'
        code_restriction_type_restriction_code = code_restriction#1 #int  NOT NULL, 1 prohibido, 2 request, 3 consitional, 4 NO restrictin
        condition_expression_type_restriction_code=condition_expression
        region= region #1
        data_capture_prohibition = prohibition #'FALSE'  # boolean  NOT NULL,
        code_zone_reason_code_zone_reason = code_zone_reason #8 #int  NOT NULL, 1,'Air Trafic',2,'Sensitive',3,'Privacy',4,'Population',5,'Nature',6,'Noise',7,'Foreign Territory',8,'Other'
        code_uspace_class_type_u_space_code = code_uspace #2 #int  NOT NULL, 1,'NDZ',2,'LDZ',3,'EDZu',4,'EDZp',5,'EDZm',6,'X',7,'Y',8,'Z',
        message = self.normalize(message) #varchar(100)  NOT NULL,

        #metadata_metadata_ide uuid
        metadata_metadata_id=metadata_metadata_id

        #authority_authority_id uuid
        authority_authority_id=authority_authority_id

        #time_period_time_period_id uuid
        time_period_time_period_id= time_period_time_period_id
        code_period_type_period_type_id=period_type_id
        limitation_value_vol_limitation=1

        
        airspace_volume_vol_id=vol_id[0][0]     
        


        query_uas_zone = '''INSERT INTO uas_zone_version (country_id, name, airspace_volume_vol_id,code_zone_type_type_code,
                    region,data_capture_prohibition,message,metadata_metada_id,code_period_type_period_type_id,authority_authorityfrom_oid, authority_notificationto_oid,
                    identifier, intervalbefore)
                    VALUES
                    ('{country_id}','{name}','{airspace_volume_vol_id}','{code_zone_type_type_code}','{region}',
                    '{data_capture_prohibition}','{message}','{metadata_metadata_id}','{code_period_type_period_type_id}','{authority_authority_id}', '{authority_notificationto_id}',
                    '{identifier}','{intervalbefore}') RETURNING  uas_zone_id;'''.format(
                        country_id=country_id,
                        name=name,
                        airspace_volume_vol_id=airspace_volume_vol_id,
                        code_zone_type_type_code=code_zone_type_type_code,
                        region=region,
                        data_capture_prohibition=data_capture_prohibition,
                        message=message,
                        metadata_metadata_id=metadata_metadata_id,
                        code_period_type_period_type_id=code_period_type_period_type_id,
                        authority_authority_id=authority_authority_id,
                        authority_notificationto_id = authority_notification_id,
                        identifier=identifier,
                        intervalbefore=intervalbefore
                        )
        query_uas_zone = re.sub('[\n\t]', '', query_uas_zone)
        try:
            self._cursor.execute(query_uas_zone)
            self._pgconn.connection.commit()
            uas_id=self._cursor.fetchall()
            uas_id=uas_id[0][0]
            list_indexs.append(uas_id)
        except Exception as ex :
            print("ERROR: ", type(ex), ex.args)
            return list_indexs
        
       
        #Insert volume limitations:
        for i in range(0,len(volume_limitation_def)):
            query_volume_limitations=""" INSERT INTO volume_limitation(limitation_value,volume_limitation_definitions_limitation_definition_id, uas_zone_version_uas_zone_id)
                                        VALUES({},{},'{}')
                                    """.format(limitation_value_vol_limitation, volume_limitation_def[i], uas_id)
            try:
                self._cursor.execute(query_volume_limitations)
                self._pgconn.connection.commit()

            except Exception as ex :
                print("ERROR: ", type(ex), ex.args)
                return list_indexs

        #Insert restriction type:
        for i in range(0,len(code_restriction_type_restriction_code)):
            query_restriction=""" INSERT INTO uas_code_restriction_type(code_restriction_type_restriction_code, uas_zone_version_uas_zone_id)
                                            VALUES({},'{}') RETURNING uas_restriction_code;
                                        """.format(code_restriction_type_restriction_code[i], uas_id)
            try:
                self._cursor.execute(query_restriction)
                self._pgconn.connection.commit()
                id_uas_restriction_type= self._cursor.fetchall()
                if code_restriction_type_restriction_code[i]==3:
                    id_condition=id_uas_restriction_type[0][0]

            except Exception as ex :
                print("ERROR: ", type(ex), ex.args)
                return list_indexs

        #Insert conditions:
        for i in range(0,len(condition_expression_type_restriction_code)):
            query_cond_restriction=""" INSERT INTO uas_condition_expression_type(condition_expression_type_restriction_code, uas_code_restriction_type_uas_restriction_code,uas_zone_version_uas_zone_id)
                                            VALUES({},'{}','{}') RETURNING uas_restriction_code;
                                        """.format(condition_expression_type_restriction_code[i], id_condition,uas_id)
            try:
                self._cursor.execute(query_cond_restriction)
                self._pgconn.connection.commit()
                id_uas_condition_type= self._cursor.fetchall()
                if condition_expression_type_restriction_code[i]==3:
                    id_condition2=id_uas_condition_type[0][0]

            except Exception as ex :
                print("ERROR: ", type(ex), ex.args)
                return list_indexs


            if condition_expression_type_restriction_code[i]==3:
                query_uppermm= '''UPDATE uas_condition_expression_type SET upperlimitnorestriction='{}' WHERE uas_restriction_code='{}';'''.format(upperlimitnorestriccion,id_condition2)
                try:
                    self._cursor.execute(query_uppermm)
                    self._pgconn.connection.commit()
                except Exception as ex :
                    print("ERROR: ", type(ex), ex.args)
                    return list_indexs
            
        
        #Insert uspace_class:
        for i in range(0,len(code_uspace_class_type_u_space_code)):
            query_uspace=""" INSERT INTO uas_code_uspace_class_type(code_uspace_class_type_u_space_code, uas_zone_version_uas_zone_id)
                                        VALUES({},'{}')
                                    """.format(code_uspace_class_type_u_space_code[i], uas_id)
            try:
                self._cursor.execute(query_uspace)
                self._pgconn.connection.commit()

            except Exception as ex :
                print("ERROR: ", type(ex), ex.args)
                return list_indexs

        #Insert zone reason:
        for i in range(0,len(code_zone_reason_code_zone_reason)):
            query_zone_reason=""" INSERT INTO uas_code_zone_reason(code_zone_reason_code_zone_reason, uas_zone_version_uas_zone_id)
                                        VALUES({},'{}')
                                    """.format(code_zone_reason_code_zone_reason[i], uas_id)
            try:
                self._cursor.execute(query_zone_reason)
                self._pgconn.connection.commit()

            except Exception as ex :
                print("ERROR: ", type(ex), ex.args)
                return list_indexs

        #Insert time period:
        for i in range(0,len(time_period_time_period_id)):
            query_zone_reason=""" INSERT INTO uas_time_period(time_period_time_period_id, uas_zone_version_uas_zone_id)
                                        VALUES('{}','{}')
                                    """.format(time_period_time_period_id[i], uas_id)
            try:
                self._cursor.execute(query_zone_reason)
                self._pgconn.connection.commit()

            except Exception as ex :
                print("ERROR: ", type(ex), ex.args)
                return list_indexs
        
        query_procedure=""" CALL create_fgeofence('{}')""".format(airspace_volume_vol_id)
        try:
            self._cursor.execute(query_procedure)
            self._pgconn.connection.commit()

        except Exception as ex :
            print("ERROR: ", type(ex), ex.args)
            return list_indexs

        return [True, airspace_volume_vol_id,uas_id]


    def specification_time(self,start_date_time, end_date_time, slots, days):
        #we specificate all the characteristics of the time restriction
        #in this case we are going to insert the values diretly but all this values have to be insert by the function
        #timeperiod table:
        Monday=1
        Tuesday=1
        Wednesday=1
        Thursday=1
        Friday=1
        Saturday=1
        Sunday=1

        
        for d in ['M','T','W','Th','F','Sa','Su']:
            if d in days:
                pass
            else: 
                if d=='M':
                    Monday=0
                elif d=='T':
                    Tuesday=0
                elif d=='W':
                    Wednesday=0
                elif d=='Th':
                    Thursday=0
                elif d=='F':
                    Friday=0
                elif d=='Sa':
                    Saturday=0
                elif d=='Su':
                    Sunday=0

        if end_date_time=='':
            end_date_time='NULL'
            query_time= '''INSERT INTO time_period(start_date_time,end_date_time,monday,tuesday,wednesday,thursday,friday,saturday,sunday)
                    VALUES('{start_date_time}',{end_date_time},'{monday}','{tuesday}','{wednesday}','{thursday}','{friday}','{saturday}','{sunday}') RETURNING time_period_id'''.format(
                        start_date_time=start_date_time,
                        end_date_time=end_date_time,
                        monday=Monday,
                        tuesday=Tuesday,
                        wednesday=Wednesday,
                        thursday=Thursday,
                        friday=Friday,
                        saturday=Saturday,
                        sunday=Sunday
                    )
        else:
            if start_date_time=='':
                start_date_time=datetime.date.today()
            query_time= '''INSERT INTO time_period(start_date_time,end_date_time,monday,tuesday,wednesday,thursday,friday,saturday,sunday)
                    VALUES('{start_date_time}','{end_date_time}','{monday}','{tuesday}','{wednesday}','{thursday}','{friday}','{saturday}','{sunday}') RETURNING time_period_id'''.format(
                        start_date_time=start_date_time,
                        end_date_time=end_date_time,
                        monday=Monday,
                        tuesday=Tuesday,
                        wednesday=Wednesday,
                        thursday=Thursday,
                        friday=Friday,
                        saturday=Saturday,
                        sunday=Sunday
                    )

        

        
        try:
            self._cursor.execute(query_time)
            self._pgconn.connection.commit()
            time_id = self._cursor.fetchall()
        except Exception as ex :
            print("ERROR: ", type(ex), ex.args)
            return 0
            #we will have a problem if it can insert the values in the table and the time_id will not be sent
       
        
        #slot time:
        for s in slots:
            start_time=s[0]
            end_time=s[1]

            query_slots='''INSERT INTO slot_time(start_time,end_time,time_period_time_period_id)
                        VALUES('{start_time}','{end_time}','{time_period_time_period_id}'); '''.format(
                            start_time=start_time,
                            end_time=end_time,
                            time_period_time_period_id=time_id[0][0]
                        )
            query_slots = re.sub('[\n\t]', '', query_slots)
            try:
                dato = self.insertQuery(query_slots)
                print('Se ha insertado {} registro'.format(dato))
            except:
                return 0

        return time_id[0][0]
    
    def specification_metadata(self,creationdate, lastupdate,author):
        query=''' INSERT INTO metadata(creation_date_time, update_date_time, author)
                VALUES('{creation_data_time}','{update_data_time}','{author}') RETURNING metada_id;
                '''.format(
                    creation_data_time=creationdate,
                    update_data_time=lastupdate,
                    author=author
                )
        try:
            self._cursor.execute(query)
            self._pgconn.connection.commit()
            metadata_id = self._cursor.fetchall()
            return metadata_id[0][0]
        except:
            return 0
    
    def add_PR(self,geom_origin_id, geomf, buffer):
        collection={}
        collection['type']='GeometryCollection'
        geometries=[]
        geom_buffer=loads(geomf)
        geom_type=geom_buffer.type

        if geom_origin_id==0:
            geom=mapping(geom_buffer.centroid)
            geometries.append(geom)
            radious='Null'
        else:

            query_feature='''SELECT ST_AsText(geom_point), ST_AsText(geom_polygon), ST_AsText(geom_center), ST_AsText(geom_linestring) FROM features WHERE feature_id='{}';'''.format(geom_origin_id)
            try: 
                self._cursor.execute(query_feature)
                results=self._cursor.fetchall()
                geom_point=results[0][0]
                geom_polygon=results[0][1]
                geom_center=results[0][2]
                geom_linestring=results[0][3]
            except Exception as ex :
                print("ERROR: ", type(ex), ex.args)
            
            if geom_point!=None or geom_linestring!=None:
                query='''SELECT geofence_basic_layers.buffer 
                        FROM features, layers_cartography, geofence_basic_layers
                        WHERE
                        features.feature_id='{}' AND
                        geofence_basic_layers.name=layers_cartography.name AND
                        layers_cartography.layer_id=features.layers_layer_id;'''.format(geom_origin_id)
                try:
                    self._cursor.execute(query)
                    results=self._cursor.fetchall()
                    radious=results[0][0]
                except Exception as ex :
                    print('ERROR IN ORIGIN')
                    print("ERROR: ", type(ex), ex.args)
            
                if geom_point!=None:
                    geom= mapping(loads(geom_point))
                    geometries.append(geom)
                    
                elif geom_linestring!=None:
                    geom= mapping(loads(geom_linestring))
                    geometries.append(geom)
                    radious=-radious
                
            elif geom_polygon!=None:
                geom= mapping(loads(geom_center))
                geometries.append(geom)
                radious='Null'
            else:
                print('ERROR NO GEOMETRY')
            
        collection['geometries']=geometries 
        point=shape(collection)   

        return [point,radious,geom_type]


      
    def normalize(self,s):
        replacements = (
            ("á", "a"),
            ("é", "e"),
            ("í", "i"),
            ("ó", "o"),
            ("ú", "u"),
            ("à", "a"),
            ("è", "e"),
            ("ì", "i"),
            ("ò", "o"),
            ("ù", "u"),
            ("ä", "a"),
            ("ë", "e"),
            ("ï", "i"),
            ("ö", "o"),
            ("ü", "u"),
            ("â", "a"),
            ("ê", "e"),
            ("î", "i"),
            ("ô", "o"),
            ("û", "u"),
            ("'"," "),
            ("-"," ",)
        )
        for a, b in replacements:
            s = s.replace(a, b).replace(a.upper(), b.upper())
        return s

    def authority(self,name, service, contact_name, site_url,email,phone):

        query=''' INSERT INTO authority (name, service, contact_name, site_url,email,phone)
                VALUES('{name}', '{service}','{contact_name}', '{site_url}', '{email}','{phone}') RETURNING authority_oid;
                '''.format(
                    name=name, 
                    service=service,
                    contact_name=contact_name,
                    site_url=site_url,
                    email=email,
                    phone=phone
                )
        try:
            self._cursor.execute(query)
            self._pgconn.connection.commit()
            au_id = self._cursor.fetchall()
            return au_id[0][0]
        except Exception as ex :
            print("ERROR: ", type(ex), ex.args)
            return 0

    def delete_geom(self, vol_id):

        query_ids='''SELECT  uas_zone_version.uas_zone_id,  uas_zone_version.metadata_metada_id, 
                    uas_zone_version.authority_authorityfrom_oid, uas_zone_version.authority_notificationto_oid
                    FROM uas_zone_version,airspace_volume
                    WHERE 
                    uas_zone_version.airspace_volume_vol_id=airspace_volume.vol_id AND
                    airspace_volume.vol_id='{}'; '''.format(vol_id)

        try:
            self._cursor.execute(query_ids)
            geom_inf=self._cursor.fetchall()
            uas_id=geom_inf[0][0]
            metadata_id=geom_inf[0][0]
            authfrom=geom_inf[0][0]
            notifto=geom_inf[0][0]

        except Exception as ex :
            print("ERROR: ", type(ex), ex.args)

        query=''' DELETE FROM metadata WHERE metada_id='{}'; '''.format(metadata_id)
        try:
            self._cursor.execute(query)
            self._pgconn.connection.commit()

        except Exception as ex :
            print("ERROR: ", type(ex), ex.args)    

        query=''' DELETE FROM authority WHERE authority_oid='{}'; '''.format(authfrom)
        try:
            self._cursor.execute(query)
            self._pgconn.connection.commit()

        except Exception as ex :
            print("ERROR: ", type(ex), ex.args)    

        query=''' DELETE FROM authority WHERE authority_oid='{}'; '''.format(notifto)
        try:
            self._cursor.execute(query)
            self._pgconn.connection.commit()

        except Exception as ex :
            print("ERROR: ", type(ex), ex.args)    

        query='''DELETE FROM uas_code_zone_reason WHERE uas_zone_version_uas_zone_id='{}';'''.format(uas_id)
        try:
            self._cursor.execute(query)
            self._pgconn.connection.commit()

        except Exception as ex :
            print("ERROR: ", type(ex), ex.args)
        
        query='''DELETE FROM uas_condition_expression_type WHERE uas_zone_version_uas_zone_id='{}'; '''.format(uas_id)
        try:
            self._cursor.execute(query)
            self._pgconn.connection.commit()

        except Exception as ex :
            print("ERROR: ", type(ex), ex.args)
        query='''DELETE FROM uas_code_restriction_type WHERE uas_zone_version_uas_zone_id='{}'; '''.format(uas_id)
        try:
            self._cursor.execute(query)
            self._pgconn.connection.commit()

        except Exception as ex :
            print("ERROR: ", type(ex), ex.args)
        query='''DELETE FROM uas_code_uspace_class_type WHERE uas_zone_version_uas_zone_id='{}'; '''.format(uas_id)
        try:
            self._cursor.execute(query)
            self._pgconn.connection.commit()

        except Exception as ex :
            print("ERROR: ", type(ex), ex.args)

        query='''DELETE FROM volume_limitation WHERE uas_zone_version_uas_zone_id='{}'; '''.format(uas_id)
        try:
            self._cursor.execute(query)
            self._pgconn.connection.commit()

        except Exception as ex :
            print("ERROR: ", type(ex), ex.args)


        query='''DELETE FROM uas_time_period WHERE uas_zone_version_uas_zone_id='{}' RETURNING time_period_time_period_id; '''.format(uas_id)
        try:
            self._cursor.execute(query)
            self._pgconn.connection.commit()
            time_id= self._cursor.fetchall()

        except Exception as ex :
            print("ERROR: ", type(ex), ex.args)

        if len(time_id)>0:
            query='''DELETE FROM slot_time WHERE time_period_time_period_id='{}'; '''.format(time_id[0][0])
            try:
                self._cursor.execute(query)
                self._pgconn.connection.commit()

            except Exception as ex :
                print("ERROR: ", type(ex), ex.args)

        
            query='''DELETE FROM time_period WHERE uas_zone_version_uas_zone_id='{}'; '''.format(time_id[0][0])
            try:
                self._cursor.execute(query)
                self._pgconn.connection.commit()

            except Exception as ex :
                print("ERROR: ", type(ex), ex.args)


        query='''DELETE FROM uas_zone_version WHERE airspace_volume_vol_id='{}';'''.format(vol_id)
        try:
            self._cursor.execute(query)
            self._pgconn.connection.commit()

        except Exception as ex :
            print("ERROR: ", type(ex), ex.args)

        query='''DELETE FROM airspace_volume WHERE vol_id='{}';'''.format(vol_id)
        try:
            self._cursor.execute(query)
            self._pgconn.connection.commit()

        except Exception as ex :
            print("ERROR: ", type(ex), ex.args)

    def call_procedures(self, airspace_volume_vol_id):
        #execute procedure to add geofence to fusion table:
        query_procedure="""CALL create_fgeofence('{}')""".format(airspace_volume_vol_id)
        try:
            self._cursor.execute(query_procedure)
            self._pgconn.connection.commit()

        except Exception as ex :
            print("ERROR: ", type(ex), ex.args)

        print("No errors, geofence inserted")