import geojson
import os
from B02CreateTables import CreateTables
from shapely.geometry import LineString, shape, Point, MultiPolygon, Polygon
from shapely.wkt import loads

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


    def insert_subtype(self,doc,osmclass, osmsubclass,properties,layer,geofence):
        subclass_id= self.serchfather(osmclass,osmsubclass)
        documento= 'environments/osm/geojson2/'+doc+'_CN.geojson'
        print(documento)
        with open(documento) as geojson_file:
            data = geojson.load(geojson_file)
            for p in data['features']:
                geom= p['geometry']
                geometry_type=p['geometry']['type']
                geometry_coords=p['geometry']['coordinates']
                
                geom_text= self.convertgeom(geometry_type, geometry_coords, geom)
                is_in=False
                if geom_text=='':
                    is_in=True

                #Is the geometry in the database? 
                '''               
                is_in=self.isgoemetryinddbb(geom_text,layer)
                '''

                if is_in==False:
                    center= geom_text.centroid
                
                
                    #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:
                            property_element= p['properties'][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)
        
                    self.insert_properties(geom_text, geometry_type, properties_elements, properties_names,layer,center,geofence)                
                    print('1 feature inserted')

    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': text='geom_polygon'
        if geom_type == 'LineString': text='geom_linestring'
        if geom_type == 'Point': text='geom_point'
        if geom_type == '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))) RETURNING feature_id;;'''.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

    
        