import os
import uuid

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

    def executeQueryCreate(self):
        dir_path = os.path.dirname(os.path.realpath(__file__))
        dir_file = os.path.join(dir_path, "sql/Maps_2.sql")
        with open(dir_file, 'r') as myfile:
            data = myfile.read()

        self._cursor.execute(data)
        self._pgconn.connection.commit()
        count = self._cursor.rowcount
        print (count, "Record inserted successfully into the table")

    def Query_inserttypes(self):
        #we open the csv in order to get all the types and subtypes
        curPath= os.path.dirname(os.path.abspath(os.path.realpath(__file__)))
        csvFile= os.path.join(curPath, 'csv/LibroPrueba.csv')
            #insert all this values on the table Osm_codification
        with open(csvFile) as fp:
            previous=''
            for line in fp:
                lineArray = line.split(',')
                
                #we have to check which type of geometry it is:
                isarea=0
                ispoint=0
                isway=0
                n_area=lineArray[3].count('area')
                n_point=lineArray[3].count('node')
                n_way=lineArray[3].count('way')
                if n_area != 0: isarea=1
                if n_point != 0: ispoint=1
                if n_way != 0: isway=1
                
                descripticion=lineArray[4].split('\n')
                if lineArray[1]=='primary':
                    pass
                else:
                    #we check if the type is new or if it is created yet, and if it is not we insert it in the table
                    if lineArray[1] != previous:
                        typemodel= 2
                        self.Insert_types(lineArray[1],typemodel)
                        previous= lineArray[1]
                    #we insert the subtype in the table osm_subclass_codification
                    self.Insert_subtypes(lineArray[1],lineArray[2], ispoint,isway,isarea,descripticion)
                     

    def Insert_types(self, tipo, model):
        try:
            query = '''INSERT INTO feature_class_codification (name, data_origin_origin_id)
                    VALUES
                    ('{}', '{}') ;'''.format(tipo, model)

            self._cursor.execute(query)
            self._pgconn.connection.commit()
            #count = self._cursor.rowcount
        except Exception as ex :
            print("ERROR: ", type(ex), ex.args)
            return None

    def Insert_subtypes(self, typeosm, subtype, ispoint,isway,isarea,description):
        query1= '''SELECT class_id FROM feature_class_codification WHERE name='{}'; '''.format(typeosm)
        self._cursor.execute(query1)
        typecode = self._cursor.fetchall()
        typecode_uuid= uuid.UUID(typecode[0][0]).hex
        try:
            query = '''INSERT INTO feature_subclass_codification (feature_class_codification_class_id,name,is_point,is_linestring,is_polygon)
                    VALUES
                    ('{}','{}','{}','{}','{}') ;'''.format(typecode_uuid, subtype, ispoint, isway, isarea)
            
            self._cursor.execute(query)
            self._pgconn.connection.commit()
            #count = self._cursor.rowcount
        except Exception as ex :
            print("ERROR: ", type(ex), ex.args)
            return None
    
    
    