import psycopg2
import subprocess
import os

path = "D:\\"
resolution = 231.65

for raster_lc in os.listdir(path):
    if raster_lc.endswith(".tif"):

        raster_lc = os.path.join(path, raster_lc)
        name = raster_lc.split('\\')[7]
        file_name = name.split('.tif')[0]

        from Global import parameters
        db, user, password, resolution = parameters()

        os.environ['PATH'] = r';C:\Program Files\PostgreSQL\9.4\bin'
        os.environ['PGHOST'] = 'localhost'
        os.environ['PGPORT'] = '5432'
        os.environ['PGUSER'] = user
        os.environ['PGPASSWORD'] = password
        os.environ['PGDATABASE'] = db

        rastername = str(file_name)
        rasterlayer = rastername.lower()

        connection = psycopg2.connect(database=db, user=user, host="localhost", password=password)

        # Check if raster table already exists
        cursor_check = connection.cursor()
        cursor_check.execute("SELECT COUNT(*) FROM information_schema.tables WHERE table_name = '" + rasterlayer + "' ;")
        check = cursor_check.fetchone()[0]

        if check > 0:
            connection.close()
            pass

        else:

            cmds = 'raster2pgsql -s 4326 -I "' + raster_lc + '" |psql'
            subprocess.call(cmds, shell=True)

            """
            # Project raster Albers, resample to 250m and transform to WGS84
            rql = "UPDATE " + rasterlayer + " SET rast = ST_Transform(ST_SetSRID(rast,4326),102025); \
                   UPDATE " + rasterlayer + " SET rast = ST_Rescale(rast, 250, 'Near'); \
                   UPDATE " + rasterlayer + " SET rast = ST_Transform(ST_SetSRID(rast,102025),4326); "
            cursor.execute(rql)
            connection.commit()
            """

            connection.close()

            ########################################################################################################

            calculation = "((" + resolution + " * " + resolution + ") / 10000)"

            table_list = ["gadm_0","gadm_1","gadm_2","gadm_3"]

            for table in table_list:

                connection = psycopg2.connect(database=db, user=user, host="localhost", password=password)
                cursor = connection.cursor()

                geom = table + "_geom"
                year = file_name[-9:].split("_")[0]
                clid = file_name[-9:].split("_")[1]

                cid = int(clid)
                rasteryear = int(year)

                temp = "ca_aggregation"

                ffl = "CREATE TABLE " + temp + " AS TABLE " + table + "; \
                       DELETE FROM " + temp + "; \
                       ALTER SEQUENCE seq_aggregation RESTART WITH 1; \
                       ALTER TABLE " + temp + " ALTER COLUMN idx SET DEFAULT NEXTVAL('seq_aggregation');"
                cursor.execute(ffl)
                connection.commit()

                # Crop statistics: calculate per aggregation unit
                bql = "INSERT INTO " + temp + "(gid, year) SELECT \
                        gid, " + year + " \
                        FROM " + geom + ";"
                cursor.execute(bql)
                connection.commit()

                if cid == 1:
                    ha = "cereals"
                if cid == 2:
                    ha = "maize"
                if cid == 3:
                    ha = "grassland"
                if cid == 4:
                    ha = "forest"
                if cid == 5:
                    ha = "shrubs"
                if cid == 6:
                    ha = "urban"
                if cid == 7:
                    ha = "industrial"
                if cid == 8:
                    ha = "bare_soil"
                if cid == 9:
                    ha = "other"

                crop = ha
                years = str(year)

                sql = "UPDATE " + temp + " SET " + crop + " = zonal_stat." + crop + " \
                        FROM (SELECT gid, (sum(sum)/100) * " + calculation + " AS " + crop + " \
                        FROM " + rasterlayer + ", " + geom + ", ST_SummaryStats(ST_Clip(rast,1,geom,true), 1) \
                        GROUP BY gid) AS zonal_stat WHERE " + temp + ".gid = zonal_stat.gid AND year = " + years + "; "
                cursor.execute(sql)
                connection.commit()

                oql = "UPDATE " + temp + " SET " + crop + " = 0 WHERE " + crop + " IS NULL;"
                cursor.execute(oql)
                connection.commit()

                # Check year
                check_id = connection.cursor()
                check_id.execute("SELECT COUNT(*) FROM " + table + " WHERE year = " + years + " ;")
                check_code = check_id.fetchone()[0]
                checker = int(check_code)

                if checker == 0:

                    bql = "INSERT INTO " + table + "(gid, year) SELECT \
                            gid, " + year + " \
                            FROM " + geom + ";"
                    cursor.execute(bql)
                    connection.commit()

                    # Pass data
                    www = "UPDATE " + table + " SET " + crop + " = " + temp + "." + crop + " FROM " + temp + " \
                           #WHERE " + table + ".gid = " + temp + ".gid \
                           #AND " + table + ".year = " + temp + ".year ; \
                           #DROP TABLE " + temp + "; "
                    cursor.execute(www)
                    connection.commit()

                else:

                    # Pass data and drop temporary table
                    www = "DROP TABLE " + temp + "; "
                    cursor.execute(www)
                    connection.commit()

                connection.close()