from B01Common import CommonSql
import os
import re

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

    def executeQuery(self):
        counter = 0
        # get  a collection of all provinces by its foreing Key as ccaa_id
        provinces_col = self.getRegionsByAdministrativeLevel(34,2)
        MuniIdLevel = self.getAdminRegionIdLevel(34,3)

        # iterate for each province searching by its natcode in table municipios and then insert to administrative_regions 
        for row in provinces_col:
            print(row[0])
            id_muni = row[1][:6]
            query = "SELECT id, ST_AsEWKT(geom) as wkt, natcode,nameunit FROM spain.municipios WHERE natcode LIKE '{}%';".format(id_muni)
            self._cursor.execute(query)
            municipios = self._cursor.fetchall()
            for muni  in municipios: # Hay un total de 8124
                #print(muni[0])
                #os.system('clear')
                counter +=1
                if counter == 1661:
                    print("PARA")
                print(counter)
                # parent_id, natcode, name, geom,region_level_id
                muniStr = re.sub('[!@#$\n\t<>&*\']', '', muni[3].lstrip())[:250]
                self.insertIntoAdministrativeRegion(row[0],muni[2],muniStr,muni[1],MuniIdLevel)