from pgConnection import PgConnection
from A06DownloadBasicLayers import Basic_layers_Download
from A04AddBasicLayers import Basic_layers
from B02CreateTables import CreateTables

execCase=7
databaseGeoFabrik="spainprueba"
databaseUTM="utm"

#1. ADD NEW COLUMNS OF DOCNAME
if(execCase==1):
    pgconnosm = PgConnection(databaseGeoFabrik)

    query =''' ALTER TABLE planet_osm_polygon ADD docname varchar(500);'''
    pgconnosm.cursor.execute(query)
    pgconnosm.connection.commit()

    query =''' ALTER TABLE planet_osm_point ADD docname varchar(500);'''
    pgconnosm.cursor.execute(query)
    pgconnosm.connection.commit()

    query =''' ALTER TABLE planet_osm_line ADD docname varchar(500);'''
    pgconnosm.cursor.execute(query)
    pgconnosm.connection.commit()

    query =''' ALTER TABLE planet_osm_roads ADD docname varchar(500);'''
    pgconnosm.cursor.execute(query)
    pgconnosm.connection.commit()


#2. DEFINE DOCNAME COLUMNS
elif(execCase==2):
    download_type = 'ddbb'
    pgconn = PgConnection(databaseUTM)
    osmdownload= Basic_layers_Download(pgconn)
    osmdownload.query(download_type,0)
    print('all geojson downloaded')

#3. DELETE ONES WITH NOT DOCNAME DEFINITION
elif(execCase==3):
    pgconnosm = PgConnection(databaseGeoFabrik)
    query =''' DELETE FROM planet_osm_polygon WHERE docname is null;'''
    pgconnosm.cursor.execute(query)
    pgconnosm.connection.commit()

    query =''' DELETE FROM planet_osm_point WHERE docname is null;'''
    pgconnosm.cursor.execute(query)
    pgconnosm.connection.commit()

    query =''' DELETE FROM planet_osm_line WHERE docname is null;'''
    pgconnosm.cursor.execute(query)
    pgconnosm.connection.commit()

    query =''' DELETE FROM planet_osm_roads WHERE docname is null;'''
    pgconnosm.cursor.execute(query)
    pgconnosm.connection.commit()

#4. CREATE FEATURE CLASSES AND SUBCLASSES ON UTM MODEL 
elif(execCase==4):
    pgconn = PgConnection(databaseUTM)
    tables = CreateTables(pgconn)
    tables.Query_inserttypes()
    print("Inserted types in table")

#5. GET FEATURES FROM OSMddbb AND STORE THEM ON UTM MODEL:
elif(execCase==5):
    pgconn = PgConnection(databaseUTM)
    layersosm= Basic_layers(pgconn)
    layersosm.query(True,1)
    print('end')

#6. ADD FEATURES LOCATED ON CANARIAS
elif(execCase==6): #this is needed because in the spain ddbb downloaded from geofabric the canarian island is not included
    #download the data from osm to gjson:
    download_type = 'cca'
    coords=['CN']
    pgconn = PgConnection(databaseUTM)
    osmdownload= Basic_layers_Download(pgconn)
    osmdownload.query(download_type, coords)
    print('all geojson downloaded')

elif(execCase==7):    
    #inter the data from gjson to ddbb:
    pgconn = PgConnection(databaseUTM)
    layersosm= Basic_layers(pgconn)
    layersosm.query(False,2)
    print('end')
