import os
import geopandas
import psycopg2
import pandas
from sqlalchemy import create_engine 

#database name
db = 'db_name'
#database user
db_user = 'db_user'
#database hostname
db_host = 'db_host'
#database use password
db_password = 'db_password'
engine = create_engine('postgresql+psycopg2://'+db_user+':'+db_password+'@'+db_host+'/'+db)
#path to location of indicadores_seccion_censal csvs
path_to_csv_folder = '/indicadores_seccion_censal_csv/'
#path to folder containing shapefiles
path_to_muni_shp_folder = '/shapefiles/'

#Prep CSV to a pandas dataframe
csv_names = os.listdir(path_to_csv_folder)
csv_paths = [path_to_csv_folder+csv_file for csv_file in csv_names]
df_list = [pandas.read_csv(f, dtype = {'cpro':str,'cmun':str}) for f in csv_paths]
for entry in df_list:
    entry['id_ine']=entry['cpro']+entry['cmun']
full_df = pandas.concat(df_list)
#Write pandas dataframe to db table
full_df.to_sql('indicadores_seccion_censal', con=engine)

#This section reads the municipality shape as preparation to send to postGIS
shp_names = os.listdir(path_to_muni_shp_folder)
shp_paths = [path_to_muni_shp_folder+shp_file for shp_file in shp_names if shp_file[-3:] == 'shp']
gdf_list = [geopandas.read_file(path) for path in shp_paths]

#This section writes the municipality geometry file to postGIS table
try:
    conn = psycopg2.connect(f"dbname={db} user={db_user} host={db_host} password={db_password}")
except:
    print ("Can't connect to database")
cur = conn.cursor()
#Create tables
cur.execute("CREATE TABLE muni_poly (id_ine text, name text, geom geometry);")
cur.execute("ALTER TABLE muni_poly ALTER COLUMN geom type geometry(Geometry, 4326);")
#Populate rows with geometries
for shapefile in gdf_list:
    for index, row in shapefile.iterrows():
        in_geom = 'SRID=4326;'+row.geometry.wkb_hex
        in_name = str(row.rotulo)
        in_id = str(row.id_ine)
        SQL = "INSERT INTO muni_poly(geom,name,id_ine) VALUES ((%s::geometry), (%s), (%s));"
        data = (in_geom, in_name, in_id)
        cur.execute(SQL,data)
conn.commit()
cur.close()
conn.close()

SELECT 
	p.name, 
	SUM(t1_1), 
	isc.id_ine, 
	st_area(st_geogfromwkb(p.geom))/1000000 AS area_sq_km, 
	ROUND(CAST(SUM(t1_1)/(st_area(st_geogfromwkb(p.geom))/1000000) AS NUMERIC),2) AS pop_per_sq_km
FROM 
	indicadores_seccion_censal isc
INNER JOIN 
	muni_poly p ON isc.id_ine = p.id_ine
WHERE 
	isc.cpro = '28'
GROUP BY
	isc.id_ine, 
	p.name, 
	p.geom
ORDER BY
	pop_per_sq_km DESC;

SELECT 
	SUM(t1_1) AS province_pop, 
	isc.cpro AS province_code, 
	SUM(t12_5) AS tot_degree, 
	ROUND(CAST(SUM(t12_5)/SUM(t1_1)*100 as numeric),2) AS perc_degree
FROM 
	indicadores_seccion_censal isc
INNER JOIN 
	muni_poly p ON isc.id_ine = p.id_ine
WHERE 
	t12_5 IS NOT NULL
GROUP BY 
	isc.cpro
ORDER BY 
	perc_degree DESC
LIMIT 10;