#from pgConnection import PgConnection

#pgconn = PgConnection()
#cursor = pgconn.cursor
'''
# Delete previoius rows
try:
    #query = "DELETE FROM administrative_regions where parent_id = '{spainId}';".format(spainId= spain_id)
    query = "DELETE FROM administrative_regions where parent_id = 'beef7024-020c-4dec-a6e0-e7fe5e9693f1';"
    
    cursor.execute(query)
    result = cursor.fetchall()
except Exception as ex :
    print("ERROR: ", type(ex), ex.args)
'''
from B01Common import CommonSql
class FillInCcaa(CommonSql):
    def __init__(self,pgconn):
        CommonSql.__init__(self,pgconn)

    def executeQuery(self):
        country_id = self.getAdminRegionIdLevel(34,0)
        '''
        # Look for the id for the country level in spain
        query = "SELECT * FROM administrative_region_levels where country_id = 34 and hierarchy_level = 0"
        try:
            self._cursor.execute(query)
            result = self._cursor.fetchall()
            if len(result) == 1:
                country_id = result[0][0]
            else:
                raise Exception("No country level found")

        except Exception as ex :
            print("ERROR: ", type(ex), ex.args)
        '''
        # Look for the id for the ccaa level in spain
        ccaa_id =  self.getAdminRegionIdLevel(34,1)
        '''
        query = "SELECT * FROM administrative_region_levels where country_id = 34 and hierarchy_level = 1"
        try:
            self._cursor.execute(query)
            result = self._cursor.fetchall()
            if len(result) == 1:
                ccaa_id = result[0][0]
            else:
                raise Exception("No CCAA level found")

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

        # Look for the id of Spain as a parent of CCAAs
        result = self.getRegionsByAdministrativeLevel(34,0) # Spain
        try:
            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)

        '''
        try:
            query = "SELECT * FROM administrative_regions where administrative_region_levels_region_level_id = '{levelId}';".format(levelId= country_id)
            self._cursor.execute(query)
            result = self._cursor.fetchall()
            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)
        '''
        '''
        # Delete previoius rows
        try:
            query = "DELETE FROM administrative_regions where parent_id = '{spainId}';".format(spainId= spain_id)
            
            self._cursor.execute(query)
            #result = cursor.fetchall()

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

        # Iterate for ccaa table and store it in administrative_regions
        try:
            query = "SELECT id, ST_AsEWKT(geom) as wkt, natcode,nameunit FROM spain.ccaa;"
            self._cursor.execute(query)
            result = self._cursor.fetchall()
            for row in result: # parent_id, natcode, name, geom,region_level_id
                self.insertIntoAdministrativeRegion(spain_id,row[2],row[3],row[1],ccaa_id)
                #query = '''INSERT INTO administrative_regions (parent_id, name, geom,administrative_region_levels_region_level_id)
                #VALUES
                #('{parent}', '{name}',ST_GeomFromText('{geom}'),'{ccaa_id}') ;'''.format(parent=spain_id,name=row[3][:25],geom=row[1],ccaa_id=ccaa_id)
                # 
                #self._cursor.execute(query)
                #self._pgconn.connection.commit()
                #count = self._cursor.rowcount
                #print (count, "Records inserted successfully into the table")

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