import geojson
import os
from B02CreateTables import CreateTables
from shapely.wkt import loads
from shapely.geometry import mapping, Polygon, LineString, Point
from pgConnection import PgConnection

class Feature:
    def __init__(self,pgconn):
        self._pgconn = pgconn
        self._cursor = pgconn.cursor


    def insert_subtype_P(self,doc,osmclass, osmsubclass,properties,layer,geofence, geom_type_id):
        subclass_id= self.serchfather(osmclass,osmsubclass)
        documento= doc
        print(documento)
        
        tables=['planet_osm_polygon','planet_osm_point', 'planet_osm_line','planet_osm_roads']


        for table in tables:
            pgconnosm = PgConnection("spainOSM")
            query='''SELECT ST_AsText(ST_Transform(way,4326)), ST_AsText(ST_Transform(ST_Centroid(way),4326)), ST_GeometryType(way), to_json(tags), name, ref FROM {} WHERE docname='{}';'''.format(table,documento)
            pgconnosm.cursor.execute(query)
            geoms = pgconnosm.cursor.fetchall()
            inddbb = True

            for geomprops in geoms:
                geom_text=geomprops[0]
                center= geomprops[1]
                geometry_type=geomprops[2]
                tags = geomprops[3]
                name = geomprops[4]
                ref= geomprops[5]
        
                    
                #we have to find the properties selected:
                properties_elements=[]
                properties_names=[]
                for prop in properties:
                    id_column=self.check_property(prop,subclass_id)
                    #print(id_column, 'printing id')
                    try:
                        if prop=='name':
                            property_element = name
                        elif prop == 'ref':
                            property_element = ref
                        else:
                            property_element= tags[prop]
                        property_element=self.normalize(property_element)
                        properties_elements.append(property_element)
                        properties_names.append(id_column)
                    except Exception as ex :
                        print("ERROR: ", type(ex), ex.args)
                        properties_elements.append('')
                        properties_names.append(id_column)

                if(table=='planet_osm_line' or table=='planet_osm_roads'):
                    #check if the lines are doing a polygon
                    geom = self.checkifPolygon(geom_text)
                    geom_text = geom
                    geometry_type=geom.geom_type
                    if(geometry_type =='LineString'):
                        inddbb=False

                if (inddbb):
                    self.insert_properties(geom_text, geometry_type, properties_elements, properties_names,layer,center,geofence)               
                    print('1 feature inserted')

    def insert_subtype_L(self,doc,osmclass, osmsubclass,properties,layer,geofence, geom_type_id):
        subclass_id= self.serchfather(osmclass,osmsubclass)
        documento= doc
        print(documento)
        
        tables=['planet_osm_line']


        for table in tables:
            pgconnosm = PgConnection("spainOSM")
            query='''SELECT ST_AsText(ST_Transform(way,4326)), ST_AsText(ST_Transform(ST_Centroid(way),4326)), ST_GeometryType(way), to_json(tags), name, ref FROM {} WHERE docname='{}';'''.format(table,documento)
            pgconnosm.cursor.execute(query)
            geoms = pgconnosm.cursor.fetchall()
            inddbb = True
            ref_list=[]
            name_list=[]
            feature_list = []

            #first we get a list with all the features and properties
            for geomprops in geoms:
                geom_text=geomprops[0]
                center= geomprops[1]
                geometry_type=geomprops[2]
                tags = geomprops[3]
                name = geomprops[4]
                ref= geomprops[5]
        
                    
                #we have to find the properties selected:
                properties_elements=[]
                properties_names=[]
                for prop in properties:
                    id_column=self.check_property(prop,subclass_id)
                    #print(id_column, 'printing id')
                    try:
                        if prop=='name':
                            property_element = name
                        elif prop == 'ref':
                            property_element = ref
                        else:
                            property_element= tags[prop]
                        property_element=self.normalize(property_element)
                        properties_elements.append(property_element)
                        properties_names.append(id_column)
                    except Exception as ex :
                        print("ERROR: ", type(ex), ex.args)
                        properties_elements.append('')
                        properties_names.append(id_column)

                if ref==None and name==None:
                    self.insert_properties(geom_text, geometry_type, properties_elements, properties_names,layer,center,geofence)               
                    print('1 feature inserted')
                else: 
                    #append the feature to the list
                    props= [geom_text, geometry_type, properties_elements, properties_names,layer,center,geofence]
                    if ref==None:
                        ref='None'
                    if name==None:
                        name='None'
                    feature = [ref, geom_text, name,props]
                    feature_list.append(feature)
                    ref_list.append(ref)
                    name_list.append(name)

            #create a list with all the reference
            
            ref_list.sort()
            name_list.sort()
            feature_list.sort(key = lambda x:x[0])
            self.setLines(ref_list,name_list,feature_list)

            

    def setLines(self, ref_list, name_list, features):
        ref_list2=list(dict.fromkeys(ref_list))
        name_list2=list(dict.fromkeys(name_list))
        ids=[]
        idsn=[]
        final_lines= []

        #first we try to group them by ref
        for ref in ref_list2:
            ids.append(ref_list.index(ref))

        for ref in ref_list2:
            ij=ref_list2.index(ref)
            ways_eval = []

            if ij!=(len(ref_list2)-1):
                lines=features[ids[ij]:ids[ij+1]]
            else:
                lines=features[ids[ij]:]

            if ref!='None':
                for line in lines:
                    geom= mapping(loads(line[1]))
                    ways_eval.append(geom)

                while ways_eval:
                    print('new line')
                    coord1=ways_eval[0]['coordinates'][0]
                    coord2=ways_eval[0]['coordinates'][-1]
                    ways_ordenadas=[]
                    ways_ordenadas.append(ways_eval[0]['coordinates'])
                    ways_eval.pop(0)
                    len1=range(len(ways_eval))
                
                    for j in len1:
                        if any(e['coordinates'][-1]==coord1 for e in ways_eval)==True:
                            x=next(ways_eval.index(e) for e in ways_eval if e['coordinates'][-1]==coord1)
                            coord1=ways_eval[x]['coordinates'][0]
                            ways_ordenadas.insert(0,ways_eval[x]['coordinates']) 
                            ways_eval.pop(x)
                        elif any(e['coordinates'][0]==coord1 for e in ways_eval)==True:
                            x=next(ways_eval.index(e) for e in ways_eval if e['coordinates'][0]==coord1)
                            coord1=ways_eval[x]['coordinates'][-1]
                            ways_ordenadas.insert(0,ways_eval[x]['coordinates'][::-1]) 
                            ways_eval.pop(x)
                        else:
                            print(len(ways_eval))
                            break



                    len2=range(len(ways_eval))
                    for j in len2:
                        if any(e['coordinates'][0]==coord2 for e in ways_eval)==True:
                            x=next(ways_eval.index(e) for e in ways_eval if e['coordinates'][0]==coord2)
                            coord2=ways_eval[x]['coordinates'][-1]
                            ways_ordenadas.append(ways_eval[x]['coordinates']) 
                            ways_eval.pop(x)

                        elif any(e['coordinates'][-1]==coord2 for e in ways_eval)==True:
                            x=next(ways_eval.index(e) for e in ways_eval if e['coordinates'][-1]==coord2)
                            coord2=ways_eval[x]['coordinates'][0]
                            ways_ordenadas.append(ways_eval[x]['coordinates'][::-1]) 
                            ways_eval.pop(x)
                        else:
                            print(len(ways_eval))
                            break 

                    print(len(ways_eval))

                    coordinates_ways_ord=[]
                    for small_way in ways_ordenadas:
                        [coordinates_ways_ord.append(coord) for coord in small_way]
                    
                    thisline = LineString(coordinates_ways_ord)
                    props = features[ids[ij]][3]
                    final_lines.append([ref, thisline, features[ids[ij]][2], [thisline, props[1], props[2], props[3], props[4], props[5], props[6]]])
                    #self.insert_properties(thisline, props[1], props[2], props[3], props[4], props[5], props[6])               
                    #print('1 feature inserted')
            else:
                for line in lines:
                    final_lines.append([line[0], loads(line[1]), line[2], line[3]])
                    


        #second we try to group them by name:
        final_lines.sort(key = lambda x:x[2])

        for name in name_list2:
            idsn.append(name_list.index(name))

        for name in name_list2:
            ij=name_list2.index(name)
            ways_eval = []

            if ij!=(len(name_list2)-1):
                lines=final_lines[idsn[ij]:idsn[ij+1]]
            else:
                lines=final_lines[idsn[ij]:]

            if name!='None':
                for line in lines:
                    ways_eval.append(mapping(line[1]))

                while ways_eval:
                    print('new line')
                    coord1=ways_eval[0]['coordinates'][0]
                    coord2=ways_eval[0]['coordinates'][-1]
                    ways_ordenadas=[]
                    ways_ordenadas.append(ways_eval[0]['coordinates'])
                    ways_eval.pop(0)
                    len1=range(len(ways_eval))
                
                    for j in len1:
                        if any(e['coordinates'][-1]==coord1 for e in ways_eval)==True:
                            x=next(ways_eval.index(e) for e in ways_eval if e['coordinates'][-1]==coord1)
                            coord1=ways_eval[x]['coordinates'][0]
                            ways_ordenadas.insert(0,ways_eval[x]['coordinates']) 
                            ways_eval.pop(x)
                        elif any(e['coordinates'][0]==coord1 for e in ways_eval)==True:
                            x=next(ways_eval.index(e) for e in ways_eval if e['coordinates'][0]==coord1)
                            coord1=ways_eval[x]['coordinates'][-1]
                            ways_ordenadas.insert(0,ways_eval[x]['coordinates'][::-1]) 
                            ways_eval.pop(x)
                        else:
                            print(len(ways_eval))
                            break



                    len2=range(len(ways_eval))
                    for j in len2:
                        if any(e['coordinates'][0]==coord2 for e in ways_eval)==True:
                            x=next(ways_eval.index(e) for e in ways_eval if e['coordinates'][0]==coord2)
                            coord2=ways_eval[x]['coordinates'][-1]
                            ways_ordenadas.append(ways_eval[x]['coordinates']) 
                            ways_eval.pop(x)

                        elif any(e['coordinates'][-1]==coord2 for e in ways_eval)==True:
                            x=next(ways_eval.index(e) for e in ways_eval if e['coordinates'][-1]==coord2)
                            coord2=ways_eval[x]['coordinates'][0]
                            ways_ordenadas.append(ways_eval[x]['coordinates'][::-1]) 
                            ways_eval.pop(x)
                        else:
                            print(len(ways_eval))
                            break 

                    print(len(ways_eval))

                    coordinates_ways_ord=[]
                    for small_way in ways_ordenadas:
                        [coordinates_ways_ord.append(coord) for coord in small_way]
                    
                    thisline = LineString(coordinates_ways_ord)
                    props = final_lines[idsn[ij]][3]
                    self.insert_properties(thisline, props[1], props[2], props[3], props[4], props[5], props[6])               
                    print('1 feature inserted')       
            else:
                for line in lines:
                    props= line[3]
                    self.insert_properties(props[0], props[1], props[2], props[3], props[4], props[5], props[6])               
                    print('1 feature inserted') 
        print("end name")

    def checkifPolygon(self, geom_text):
        geom= mapping(loads(geom_text))
        if(geom['coordinates'][0]==geom['coordinates'][-1]):
            geom= Polygon(geom['coordinates'])
        else:
            geom=LineString(geom['coordinates'])
        return geom   

    def convertgeom(self,geom_type,coordinates,geom):
        if geom_type == 'LineString':
           geom_text= LineString(coordinates)
        elif geom_type == 'Point':
            geom_text= Point(coordinates)
        elif geom_type == 'Polygon':
            geom_text = Polygon(coordinates) 
            
        elif geom_type == 'MultiPolygon':
            geom_pol=[]
            i=0
            for x in coordinates:
                if i==0:
                    geom_text=Polygon(x)
                i=i+1
       
        return geom_text

    def serchfather(self, typeosm, subtypeosm):
        query1='''SELECT class_id FROM feature_class_codification WHERE name= '{}';'''.format(typeosm)

        self._cursor.execute(query1)
        class_id=self._cursor.fetchall()

        query = "SELECT subclass_id FROM feature_subclass_codification WHERE (name= '{}' AND feature_class_codification_class_id='{}');".format(subtypeosm, class_id[0][0])
       
        self._cursor.execute(query)
        parent = self._cursor.fetchall()

        return parent[0][0]

        
    def check_property(self, property_evaluated, subclass_id):
        query= '''
             SELECT column_id FROM feature_columns_codification 
             WHERE (name = '{}' AND feature_subclass_codification_subclass_id = '{}'); '''.format(property_evaluated, subclass_id)
        print(property_evaluated)
        try:
            self._cursor.execute(query)
            column_id = self._cursor.fetchall()
            print(column_id, ' column')
        except:
            print('error')
            column_id=[]
        
        if column_id==[]:
            query2= '''INSERT INTO feature_columns_codification (name, feature_subclass_codification_subclass_id,name_code)
                    VALUES
                    ('{}','{}','{}') ;'''.format(property_evaluated, subclass_id, property_evaluated)
            self._cursor.execute(query2)
            self._pgconn.connection.commit()
            query1= "SELECT column_id FROM feature_columns_codification WHERE name = '{}' and feature_subclass_codification_subclass_id= '{}'".format(property_evaluated, subclass_id)
            self._cursor.execute(query1)
            column_id = self._cursor.fetchall()
            print('Created new column')
            return column_id[0][0]
        else:
            return column_id[0][0]

    def insert_properties(self, geom_text, geom_type, properties_elements, properties_names,layer,center,geofence):
        if geom_type == 'Polygon' or geom_type== "ST_Polygon": text='geom_polygon'
        if geom_type == 'LineString' or geom_type== "ST_LineString": text='geom_linestring'
        if geom_type == 'Point' or geom_type== "ST_Point": text='geom_point'
        if geom_type == 'MultiPolygon' or geom_type== "ST_MultiPolygon": text='geom_polygon'

        numberfeatures= len(properties_elements)
        if numberfeatures == 0:
             query = '''INSERT INTO features (layers_layer_id,{},geom_center)
                VALUES
                ('{}',ST_GeomFromText(ST_AsEWKT(ST_GeomFromText('{}',4326)::geography)), ST_GeomFromText(ST_AsEWKT(ST_GeomFromText('{}',4326)::geography))) ;'''.format(text,layer,geom_text,center)
        if numberfeatures == 1:
            query = '''INSERT INTO features (layers_layer_id,{}, prop1_value, prop1_code,geom_center)
                VALUES
                ('{}',ST_GeomFromText(ST_AsEWKT(ST_GeomFromText('{}',4326)::geography)), '{}','{}', ST_GeomFromText(ST_AsEWKT(ST_GeomFromText('{}',4326)::geography))) RETURNING feature_id;;'''.format(text,layer,geom_text,
                self.normalize(properties_elements[0]), properties_names[0], center)
        if numberfeatures == 2: 
            query = '''INSERT INTO features (layers_layer_id,{}, prop1_value, prop1_code, prop2_value, prop2_code, geom_center)
                VALUES
                ('{}',ST_GeomFromText(ST_AsEWKT(ST_GeomFromText('{}',4326)::geography)), '{}','{}','{}','{}', ST_GeomFromText(ST_AsEWKT(ST_GeomFromText('{}',4326)::geography))) RETURNING feature_id;;'''.format(text,layer,geom_text,
                self.normalize(properties_elements[0]), properties_names[0],self.normalize(properties_elements[1]),properties_names[1],center)
        if numberfeatures == 3:
            query = '''INSERT INTO features (layers_layer_id,{}, prop1_value, prop1_code, prop2_value, prop2_code, prop3_value, prop3_code, geom_center)
                VALUES
                ('{}',ST_GeomFromText(ST_AsEWKT(ST_GeomFromText('{}',4326)::geography)), '{}','{}','{}','{}','{}','{}', ST_GeomFromText(ST_AsEWKT(ST_GeomFromText('{}',4326)::geography))) RETURNING feature_id;;'''.format(text,layer,geom_text,
                self.normalize(properties_elements[0]), properties_names[0],self.normalize(properties_elements[1]),properties_names[1],self.normalize(properties_elements[2]),
                properties_names[2], center)
        if numberfeatures == 4:
            query = '''INSERT INTO features (layers_layer_id,{}, prop1_value, prop1_code, prop2_value, prop2_code, prop3_value, prop3_code, prop4_value, prop4_code, geom_center )
                    VALUES
                    ('{}',ST_GeomFromText(ST_AsEWKT(ST_GeomFromText('{}',4326)::geography)), '{}','{}','{}','{}','{}','{}','{}','{}', ST_GeomFromText(ST_AsEWKT(ST_GeomFromText('{}',4326)::geography))) RETURNING feature_id;;'''.format(text,layer,geom_text,
                    self.normalize(properties_elements[0]), properties_names[0],self.normalize(properties_elements[1]),properties_names[1],self.normalize(properties_elements[2]),
                    properties_names[2],self.normalize(properties_elements[3]), properties_names[3], center)

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

            #count = self._cursor.rowcount
        except Exception as ex :
            print("ERROR: ", type(ex), ex.args)
            exit
            return None

        if geofence==False:
            try:
                queryproced= '''CALL create_fcartography('{}'); '''.format(f_id[0][0])
                self._cursor.execute(queryproced)
                self._pgconn.connection.commit()
            except Exception as ex :
                print("ERROR: ", type(ex), ex.args)
                exit
                return None
        

  
    def isgoemetryinddbb(self, geom, layer_id):
        Pol1=geom
        query_f = '''SELECT ST_AsEWKT(geom_polygon), feature_id FROM features WHERE layers_layer_id='{}' AND geom_polygon IS NOT NULL; ;'''.format(layer_id)
        try:
            self._cursor.execute(query_f)
            feature_bbdd = self._cursor.fetchall()
            is_in=False
        
            if feature_bbdd !=[]:
                for f in feature_bbdd:
                    query= "SELECT ST_AsText(ST_GeomFromText('{}'))".format(f[0])
                    try:
                        self._cursor.execute(query)
                        result = self._cursor.fetchall()
                        if len(result) == 1:
                            Geom = result[0][0]
                        else:
                            raise Exception("no geometry buffered converted")
                    except Exception as ex :
                        print("ERROR: ", type(ex), ex.args)
                    
                    i= loads(Geom)
                    if i.contains(Pol1)==True:
                        is_in=True
                        break
                    elif Pol1.contains(i)==True:
                        query ='''DELETE FROM features WHERE feature_id='{}';'''.format(f[1]) 
                        self._cursor.execute(query)
                        self._pgconn.connection.commit() 
                        is_in=False
                    else:
                        is_in=False
            return is_in
        except Exception as ex :
            print("ERROR: ", type(ex), ex.args)
            is_in=False
            return is_in
 

    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

    
        