from B03Osm_conetion import OsmConnection
from B04Osm_ddbb import Osmddbb
from B01InsertfromOSMddbb import Feature
from B01Insert_feature import FeatureGjson
from C01Common import CommonSql
from random import randint

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

    def one_subtype_osm(self, doc, osmtype, osmsubtype, downloadtype, coords):
        if downloadtype== 'esp':
            c=OsmConnection()
            c.conectosm(osmtype, osmsubtype, doc)
            print('geojson created')

        elif downloadtype=='coords':
            c=OsmConnection()
            c.conectosm_coords(osmtype, osmsubtype, coords, doc)
            print('geojson created')

        elif downloadtype=='ddbb':
            c=Osmddbb()
            c.defineosmlayer(osmtype, osmsubtype, doc)
            print('docname defined')
        
        elif downloadtype=='cca':
            c=OsmConnection()
            c.conectosm_cca(osmtype, osmsubtype, doc, coords)
            print('docname defined')


    def one_group_osm(self, doc, osmtype, downloadtype, coords):
        query1= '''SELECT class_id FROM feature_class_codification WHERE name='{}'; '''.format(osmtype)
        self._cursor.execute(query1)
        typecode = self._cursor.fetchall()

        query2= ''' SELECT name FROM feature_subclass_codification WHERE feature_class_codification_class_id = '{}'; '''.format(typecode[0][0])
        self._cursor.execute(query2)
        osmsubtype= self._cursor.fetchall()

        for feature_e in osmsubtype:
            doc1=doc+'_'+osmtype+'_'+feature_e[0]
            self.one_subtype_osm(doc1, osmtype, feature_e[0], downloadtype, coords)

    def one_subtype_gjson(self, doc1, osmtype, osmsubtype, properties, pgconn,layer_id, geofence, geom_type_id):
        
        #insert features from geojson: 
        f=FeatureGjson(pgconn)
        doc=doc1
        f.insert_subtype(doc,osmtype, osmsubtype,properties,layer_id, geofence)
        print('features inserted')
        
        

    def one_group_gjson(self, doc, osmtype, properties, pgconn,layer_id, geofence, geom_type_id):
        #get names of each subclass
        query1= '''SELECT class_id FROM feature_class_codification WHERE name='{}'; '''.format(osmtype)
        self._cursor.execute(query1)
        typecode = self._cursor.fetchall()

        query2= ''' SELECT name FROM feature_subclass_codification WHERE feature_class_codification_class_id = '{}'; '''.format(typecode[0][0])
        self._cursor.execute(query2)
        osmsubtype= self._cursor.fetchall()

        for feature_e in osmsubtype:
            doc1=doc+'_'+osmtype+'_'+feature_e[0]
            self.one_subtype_gjson(doc1, osmtype, feature_e[0], properties, pgconn, layer_id,geofence,geom_type_id)
        

    def one_subtype(self, doc1, osmtype, osmsubtype, properties, pgconn,layer_id, geofence, geom_type_id):
        
        #insert features from geojson: 
        f=Feature(pgconn)
        doc=doc1
        if geom_type_id !=2:
            f.insert_subtype_P(doc,osmtype, osmsubtype,properties,layer_id,geofence,geom_type_id)
        else:
            f.insert_subtype_L(doc,osmtype, osmsubtype,properties,layer_id,geofence,geom_type_id)
        print('features inserted')
        
        

    def one_group(self, doc, osmtype, properties, pgconn,layer_id, geofence, geom_type_id):
        #get names of each subclass
        query1= '''SELECT class_id FROM feature_class_codification WHERE name='{}'; '''.format(osmtype)
        self._cursor.execute(query1)
        typecode = self._cursor.fetchall()

        query2= ''' SELECT name FROM feature_subclass_codification WHERE feature_class_codification_class_id = '{}'; '''.format(typecode[0][0])
        self._cursor.execute(query2)
        osmsubtype= self._cursor.fetchall()

        for feature_e in osmsubtype:
            doc1=doc+'_'+osmtype+'_'+feature_e[0]
            self.one_subtype(doc1, osmtype, feature_e[0], properties, pgconn, layer_id,geofence,geom_type_id)
           


    def some_subtype_1group(self, doc, osmtype, osmsubtype, properties,pgconn,layer_id):
        
        for feature_e in osmsubtype:
            doc1=doc+'_'+osmtype+'_'+feature_e[0]
            self.one_subtype(doc1, osmtype, feature_e, properties, pgconn, layer_id)
           
    

    def createlayer(self, name, geom_type,  description_layer):
        
        query_insert_layer='''INSERT INTO layers_cartography (name, geometry_codification_geometry_type, description)
                    VALUES
                    ('{}', {}, '{}') RETURNING layer_id;'''.format(name, geom_type, description_layer)
        self._cursor.execute(query_insert_layer)
        self._pgconn.connection.commit()        
        layer= self._cursor.fetchall()
        
        return layer[0][0]

    def createview(self,name, layerid, type_geom):
        if type_geom == 3: text_type='geom_polygon'
        if type_geom == 2: text_type='geom_linestring'
        if type_geom == 1: text_type='geom_point'

        
        query='''CREATE MATERIALIZED VIEW {} AS
            SELECT * FROM features WHERE layers_layer_id='{}' and {} IS NOT NULL;'''.format(name, layerid, text_type)

        self._cursor.execute(query)
        self._pgconn.connection.commit() 
    
    def get_layerid(self, name):
        query='''SELECT layer_id FROM layers_cartography 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 delete_layers_features(self, id_layer):
        query= '''DELETE FROM features WHERE layers_layer_id='{}';'''.format(id_layer)

        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 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 get_geom_type_id(self, geom_type):
        query_type_geom= ''' SELECT geometry_id FROM geometry_codification WHERE name='{}'; '''.format(geom_type)
        try:
            self._cursor.execute(query_type_geom)
            typegeom_id= self._cursor.fetchall()
            return typegeom_id[0][0]
        except Exception as ex :
            print("ERROR: ", type(ex), ex.args)
            return 0
