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 = []
        pixel_array_alpha = []
        results = cursor.fetchall()
        for j in range(0, 36):
            row_array = []
            row_array_alpha = []
            for i, station_id in enumerate(results):
                if i % 36 == 0:
                    value = results[i + j][1]
                    row_array.append(value)
                    row_array_alpha.append(value)
                    np_row_array = np.array(row_array)
                    np_row_array_alpha = np.array(row_array_alpha)
                    np_row_array_alpha = np.where(np_row_array_alpha < 8, 0, 255)
                    # print(row_array)
            pixel_array.append(np_row_array)
            pixel_array_alpha.append(np_row_array_alpha)
        np_pixel_array = np.array(pixel_array)
        np_pixel_array_alpha = np.array(pixel_array_alpha)

        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('D:/Uni/Master/01_SS2021/Automatisierte_Geodatenprozessierung/temperaturverteilung/dataacquisition/output/myraster.tif', ncols, nrows, 4, 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.GetRasterBand(2).WriteArray(np_pixel_array)
        # output_raster.GetRasterBand(3).WriteArray(np_pixel_array)
        output_raster.GetRasterBand(4).WriteArray(np_pixel_array_alpha)

        output_raster.FlushCache()