from B01Common import CommonSql
from E01CommonVolumes import CommonVolumes
from B02ExpAeronautics import export_aeronautics
import datetime
import json
import base64
import os

class CreateProceduresPy(CommonSql):
    def __init__(self,pgconn):
        CommonSql.__init__(self,pgconn)

    def query_create(self):
        cm= CommonVolumes(self._pgconn)

        #1. get the information from a geofence
       
        n_layers= cm.query_numberlayers()
        
        for id_f in range(2,n_layers+1):
            geofence_id=id_f
            layer_geofence_info=cm.getinfogeofence(geofence_id)
            
            if layer_geofence_info[1]== True:
              
                    #get layer_volume_id
                layer_name=layer_geofence_info[0]+'_volume'
                layer_vol_id=cm.get_layerid(layer_name)
                            #layer_vol_id='19fe6347-1423-482c-8433-c885a6c9edb8'
                #2. create a json with the information of a column with multiple values
                #select all the uas_zone_ids:
                
                vol_ids=self.get_airspace_from_layer(layer_vol_id)
                for vol in vol_ids:
                    #2.1. get all the information related with the restriction type for the geofence(uas_code_restriction_type, uas_code_zone_reason, uas_code_uspace_class_type, volume_limitation)
                        #uas_code_uspace_class_type:
                    jsons=self.get_jsons(vol[0])
                    uspace_json=json.dumps(jsons[0])
                    zone_reason_json=json.dumps(jsons[1])
                    restriction_json=json.dumps(jsons[2])
                    volume_limitation_json=json.dumps(jsons[3])
                    time_period_json=json.dumps(jsons[4])
                        
                    #2.2.codified this json
                    uspace_base64=base64.encodestring(uspace_json.encode('utf-8'))
                    uspace_base64=str(uspace_base64, "utf-8")
                    zone_reason_base64=base64.b64encode(zone_reason_json.encode('utf-8'))
                    zone_reason_base64=str(zone_reason_base64, "utf-8")
                    restriction_base64=base64.b64encode(restriction_json.encode('utf-8'))
                    restriction_base64=str(restriction_base64, "utf-8")
                    volume_limitation_base64=base64.b64encode(volume_limitation_json.encode('utf-8'))
                    volume_limitation_base64=str(volume_limitation_base64, "utf-8")
                    time_period_base64=base64.b64encode(time_period_json.encode('utf-8'))
                    time_period_base64=str(time_period_base64, "utf-8")


                    #2.3. insert the information in uas_zone_version
                    
                    query_insert_restrictions=''' UPDATE uas_zone_version SET json_restriction_type='{}', json_uspace_class_type='{}', json_zone_reason='{}', json_volume_limitation='{}',
                                                    json_time_period='{}' WHERE airspace_volume_vol_id='{}';'''.format(restriction_base64,uspace_base64,zone_reason_base64,volume_limitation_base64,time_period_base64,vol[0])
                    try:
                        self._cursor.execute(query_insert_restrictions)
                        self._pgconn.connection.commit()
                    except Exception as ex :
                        print("ERROR: ", type(ex), ex.args)
               

    def get_airspace_from_layer(self, layer_volume):
        query='''SELECT vol_id FROM airspace_volume WHERE layers_layer_id='{}';'''.format(layer_volume)
        try:
            self._cursor.execute(query)
            vol_id=self._cursor.fetchall()
            return vol_id
        except Exception as ex :
            print("ERROR: ", type(ex), ex.args)
            return 'error'

    def get_jsons(self, airspace_volume):
        uas_zone_id=self.get_uas_zone_id_airspace_vol(airspace_volume)
        data_uspace=self.get_uspace(uas_zone_id[0][0])   
        data_zone_reason=self.get_zone_reason(uas_zone_id[0][0])  
        data_restriction=self.get_restriction(uas_zone_id[0][0])   
        data_vol_limitation=self.get_volume_limitations(uas_zone_id[0][0])   
        data_time_period=self.get_time_period(uas_zone_id[0][0]) 

        return [data_uspace,data_zone_reason,data_restriction,data_vol_limitation,data_time_period]

    def get_uas_zone_id_airspace_vol(self,airspace_volume):
        query=''' SELECT uas_zone_id FROM uas_zone_version WHERE airspace_volume_vol_id ='{}';'''.format(airspace_volume)
        try:
            self._cursor.execute(query)
            uas_zone_id=self._cursor.fetchall()
            return uas_zone_id
        except Exception as ex :
            print("ERROR: ", type(ex), ex.args)
            return 'error'

    def get_uspace(self,uas_zone_id):
        json_uspace={}
        json_uspace['type']='code_uspace_class_type'
        data_uspace=[]
        #JSON USPACE CLASS TYPE
        query_u='''SELECT code_uspace_class_type.name FROM code_uspace_class_type, uas_code_uspace_class_type 
                WHERE uas_code_uspace_class_type.uas_zone_version_uas_zone_id='{}' AND code_uspace_class_type.u_space_code=uas_code_uspace_class_type.code_uspace_class_type_u_space_code;; '''.format(uas_zone_id)
        try:
            self._cursor.execute(query_u)
            u_restriction=self._cursor.fetchall()
            for u_r in u_restriction:
                u_json={}
                u_json['uspace_class_type_name']=u_r[0]
                data_uspace.append(u_json)
                json_uspace['values']=data_uspace
        except Exception as ex :
            print("ERROR: ", type(ex), ex.args)

        return json_uspace

    def get_zone_reason(self,uas_zone_id):  
        json_zone_reason={}
        json_zone_reason['type']='code_zone_reason' 
        data_zone_reason=[]   
        #JSON CODE ZONE REASON 
        query_reason='''SELECT code_zone_reason.name FROM code_zone_reason, uas_code_zone_reason
                WHERE uas_code_zone_reason.uas_zone_version_uas_zone_id='{}' AND code_zone_reason.code_zone_reason=uas_code_zone_reason.code_zone_reason_code_zone_reason;; '''.format(uas_zone_id)
        try:
            self._cursor.execute(query_reason)
            reason_restrictions=self._cursor.fetchall()
            for reason_r in reason_restrictions:
                reason_json={}
                reason_json['code_zone_type_name']=reason_r[0]
                data_zone_reason.append(reason_json)
                json_zone_reason['values']=data_zone_reason
        except Exception as ex :
            print("ERROR: ", type(ex), ex.args)

        return json_zone_reason

    def get_volume_limitations(self,uas_zone_id): 
        json_volume_limitation={}
        json_volume_limitation['type']='volume_limitation'
        data_vol_lim=[]
        #JSON VOLUME LIMITATION
        query_volume_lim='''SELECT volume_limitation_definitions.name FROM volume_limitation_definitions, volume_limitation
                WHERE volume_limitation.uas_zone_version_uas_zone_id='{}' AND volume_limitation_definitions.limitation_definition_id=volume_limitation.volume_limitation_definitions_limitation_definition_id;; '''.format(uas_zone_id)
        try:
            self._cursor.execute(query_volume_lim)
            limitation_restrictions=self._cursor.fetchall()
            for lim_r in limitation_restrictions:
                limitation_json={}
                limitation_json['volume_limitation_name']=lim_r[0]
                data_vol_lim.append(limitation_json)
                json_volume_limitation['values']=data_vol_lim
        except Exception as ex :
            print("ERROR: ", type(ex), ex.args)

        return json_volume_limitation

    def get_restriction(self,uas_zone_id):  
        json_restriction={}   
        json_restriction['type']='code_restriction_type'
        data_restriction=[]
        #JSON RESTRICTION TYPE
        query_restriction=''' SELECT code_restriction_type.name, uas_code_restriction_type.uas_restriction_code FROM code_restriction_type, uas_code_restriction_type
                WHERE uas_code_restriction_type.uas_zone_version_uas_zone_id='{}' AND code_restriction_type.restriction_code=uas_code_restriction_type.code_restriction_type_restriction_code;; '''.format(uas_zone_id)
        try:
            self._cursor.execute(query_restriction)
            restrictions_types=self._cursor.fetchall()
            for restrict in restrictions_types:
                restriction_json={}
                restriction_json['restriction_type_name']=restrict[0]
                if restrict[0]=='CONDITIONAL':
                    conditional_id=restrict[1]
                    conditions_data=[]
                    query_condition=''' SELECT condition_expression_type.name, uas_condition_expression_type.uas_restriction_code
                    FROM condition_expression_type, uas_condition_expression_type
                    WHERE uas_condition_expression_type.uas_code_restriction_type_uas_restriction_code='{}' 
                    AND condition_expression_type.restriction_code=uas_condition_expression_type.condition_expression_type_restriction_code;; '''.format(conditional_id)
                    try:
                        self._cursor.execute(query_condition)
                        condition_expressions=self._cursor.fetchall()
                        for cond in condition_expressions:
                            condition_json={}
                            condition_json['condition_name']=cond[0]
                            if cond[0]=='Allowed Operators/drones':
                                allowed_d_id=cond[1]
                                allowed_drones_data=[]
                                query_allowed_d=''' SELECT list_allowed_operators_drones.operator_id, list_allowed_operators_drones.drone_id
                                        FROM list_allowed_operators_drones
                                        WHERE list_allowed_operators_drones.uas_condition_expression_type_uas_restriction_code='{}' ; '''.format(allowed_d_id)
                                try:
                                    self._cursor.execute(query_allowed_d)
                                    allowed_operators=self._cursor.fetchall()
                                    for op in allowed_operators:
                                        operators=[]
                                        if op[0] not in operators:
                                            operators.append(op[0])
                                    for operator in operators:
                                        indexs= [allowed_operators.index(all_op) for all_op in allowed_operators if all_op[0]==operator]
                                        drones_data=[]
                                        operator_json={}
                                        for ind in indexs:
                                            drone_json={}
                                            drone_json['drone_id']=allowed_operators[ind][1]
                                            drones_data.append(drone_json)
                                        operator_json['operator_id']=op[0]
                                        operator_json['drones_allowed']=drones_data  
                                        allowed_drones_data.append(operator_json)
                                    condition_json['operators_allowed']=allowed_drones_data      
                                except Exception as ex :
                                    print("ERROR: ", type(ex), ex.args)
                            conditions_data.append(condition_json) 
                    except Exception as ex :
                        print("ERROR: ", type(ex), ex.args)
                    restriction_json['conditions']=conditions_data
                data_restriction.append(restriction_json)
                json_restriction['values']=data_restriction

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

    def get_time_daily(self, time_period_id):
        data_time_period=[]
        #JSON TIME PERIOD
        query_time='''SELECT permanent, start_date_time, end_date_time, time_period_id
            FROM time_period
            WHERE time_period_id='{}'; '''.format(time_period_id)

        try:
            self._cursor.execute(query_time)
            time_inf=self._cursor.fetchall()
            time_json={}
            time_json['start_date']=time_inf[0][1].strftime("%d-%b-%Y ")
            time_json['end_date']=time_inf[0][2].strftime("%d-%b-%Y ")
            time_json['permanent']=time_inf[0][0]
            time_id=time_inf[0][3]
        except Exception as ex :
            print("ERROR: ", type(ex), ex.args)

        query_daily='''SELECT daily_period.start_time, daily_period.end_time, code_week_day.day_name
            FROM  daily_period, code_week_day
            WHERE daily_period.time_period_time_period_id='{}' AND daily_period.code_week_day_day_number=code_week_day.day_number; '''.format(time_id)
        try:
            self._cursor.execute(query_daily)
            daily_inf=self._cursor.fetchall()
            daily_data=[]
            daily_json={}
            for daily in daily_inf:
                daily_json['start_time']=daily[0].strftime("%d-%b-%Y (%H:%M:%S.%f)")
                daily_json['end_time']=daily[1].strftime("%d-%b-%Y (%H:%M:%S.%f)")
                daily_json['week_day']=daily[2]
                daily_data.append(daily_json)
            time_json['daily_periods']=daily_data
        except Exception as ex :
            print("ERROR: ", type(ex), ex.args)
        
        data_time_period.append(time_json)  

        return data_time_period           
            
    def get_time_period(self,uas_zone_id): 
        json_time_period={}
        json_time_period['type']='time_period'
        #JSON TIME PERIOD
        query_time='''SELECT time_period_time_period_id
            FROM uas_zone_version
            WHERE uas_zone_id='{}'; '''.format(uas_zone_id)

        try:
            self._cursor.execute(query_time)
            time_inf=self._cursor.fetchall()
            time_id=time_inf[0][0]
        except Exception as ex :
            print("ERROR: ", type(ex), ex.args)

        data_time_period=self.get_time_daily(time_id)
        
        json_time_period['values']=data_time_period

        return json_time_period 


