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

    def getAdminRegionIdLevel(self,countryCode,level):
        query = "SELECT * FROM administrative_region_levels where country_id = {countryCode} and hierarchy_level = {level}\
            ".format(countryCode=countryCode, level=level)
        try:
            self._cursor.execute(query)
            result = self._cursor.fetchall()
            if len(result) == 1:
                country_id = result[0][0]
                return country_id
            else:
                raise Exception("No country level found")
                #return None

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

    def getRegionsByAdministrativeLevel(self,countryCode,level):
        try:
            adminRegion = self.getAdminRegionIdLevel(countryCode,level)
            if adminRegion == None:
                raise Exception("No admin region ID found")

            query = "SELECT * FROM administrative_regions where administrative_region_levels_region_level_id = '{}';".format(adminRegion)
            self._cursor.execute(query)
            result = self._cursor.fetchall()
            return result
            #if len(result) == 1:
            #    spain_id = result[0][0]
            #else:
            #    raise Exception("No spain feature found")

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

    def insertIntoAdministrativeRegion(self,parent_id, natcode, name, geom,region_level_id):
        try:
            query = '''INSERT INTO administrative_regions (parent_id, eurocae_region, name, geometry, administrative_region_levels_region_level_id)
                    VALUES
                    ('{}', '{}', '{}',ST_GeomFromText('{}'),'{}') ;'''.format(parent_id, natcode, name, geom,region_level_id)

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