import configparser
import numpy as np
import psycopg2
import pandas as pd
from osgeo import gdal, osr

cfg = configparser.ConfigParser()
cfg.read('../config.ini')
assert "POSTGRES" in cfg, "missing POSTGRES in config.ini"
param_postgres = cfg["POSTGRES"]
param_interpol = cfg["INTERPOLATION"]

db_name, user, pw, host, port = param_postgres['dbName'], param_postgres["user"], param_postgres["password"], param_postgres["host"], param_postgres["port"]

with psycopg2.connect(database=db_name, user=user, password=pw, host=param_postgres["host"], port=port) as connection:
    with connection.cursor() as cursor:
        cursor.execute('SELECT station_id, "2018" FROM stations WHERE file IS NULL ORDER BY station_id ASC;')
        pixel_array = []
        results = cursor.fetchall()
        for j in range(0, 36):
            row_array = []
            for i, station_id in enumerate(results):
                if i % 36 == 0:
                    row_array.append(results[i + j][1])                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
                    # print(row_array)
            pixel_array.append(row_array)
        np_pixel_array = np.array(pixel_array)
        print(np_pixel_array)
        
        xmin, ymin, xmax, ymax = [5.01, 47.15, 14.81, 55.33]
        nrows, ncols = np.shape(np_pixel_array)
        xres = (xmax - xmin) / float(ncols)
        yres = (ymax - ymin) / float(nrows)
        geotransform = (xmin, xres, 0, ymax, 0, -yres)

        output_raster = gdal.GetDriverByName('GTiff').Create('./output/myraster.tif', ncols, nrows, 1, gdal.GDT_Float32)  # Open the file
        output_raster.SetGeoTransform(geotransform)
        srs = osr.SpatialReference()
        srs.ImportFromEPSG(4326)
        output_raster.SetProjection(srs.ExportToWkt())
        output_raster.GetRasterBand(1).WriteArray(np_pixel_array)

        output_raster.FlushCache()