# 1 - Import libraries
from osgeo import gdal, ogr
import os, sys
from datetime import datetime

#create datetime as string for folder
now = datetime.now() # current date and time
year = now.strftime("%Y")
month = now.strftime("%m")
day = now.strftime("%d")
time = now.strftime("%H%M")
date_time = now.strftime("%Y%m%d_%H%M")
#if merge.shp exists, delete it
if os.path.exists("merge.shp"):
  os.remove("merge.shp")
  os.remove("merge.dbf")
  os.remove("merge.shx")

ncfolder = os.getcwd()

# 2 - create folder for tiffs
folderToCreate = ncfolder + '/' + date_time + '_tiffs' 
try:
    os.mkdir(folderToCreate)
except OSError:
    print ("Step 1: Creation of the directory %s failed" % folderToCreate)
    sys.exit(1) # this is to exit code
else:
    print ("Step 1: Successfully created the directory %s " % folderToCreate)
    
# 3 - convert nc to tiffs
#gdal_translate -of GTiff TEST.nc TEST.tiff
    
for fn in os.listdir(ncfolder):
    #print(ncfolder)
    if fn.endswith(".nc"):
        #print(os.path.join(ncfolder, fn))
        #print(fn)
        try:
            os.system("gdal_translate -of GTiff " + fn + " " +  folderToCreate + "/" + fn + '.tiff')
            print ("Step 2: Conversion of the file %s successfully" % fn)
        except OSError:
            print ("Step 2: Conversion of the file %s failed" % fn)
        
    else:
        continue

#create production folder
folderProduction = ncfolder + '/' + date_time + '_prod'  
try:
    os.mkdir(folderProduction)
except OSError:
    print ("Step 3: Creation of the directory %s failed" % folderProduction)
    

#tiffFolder = folderToCreate + '/'
for tifffile in os.listdir(folderToCreate):
    try:
        #print("gdal_polygonize.py [-8] [-nomask] " + folderToCreate + '/' + tifffile + " " + folderProduction + "/" + tifffile + '.shp')
        os.system("gdal_polygonize.py -8 -nomask "+ folderToCreate + '/' + tifffile + " " + folderProduction + "/" + tifffile + '.shp')
        print ("Step 3: Conversion of the file %s successfully" % tifffile)
    except OSError:
        print ("Step 3: Conversion of the file %s failed" % fn)
        
# 5 - Create new fields to shapefiles

for shpfile in os.listdir(folderProduction):
    if shpfile.endswith(".shp"):
        #print(shpfile)
        yearfield =shpfile[14:18]
        monthfield=shpfile[18:20]
        dayfield = shpfile[20:22]
        hourfield = shpfile[23:25]
        minutesfield = shpfile[25:27]
        datefield = datetime.strptime(yearfield + "-" + monthfield + '-' + dayfield, '%Y-%m-%d')
        timefield = hourfield + ':' + minutesfield
        driver = ogr.GetDriverByName('ESRI Shapefile')
        fn = folderProduction +'/' + shpfile
        dataSource = driver.Open(fn, 1) # 1 for writing, 0 for reading
        fldDef = ogr.FieldDefn('coordx', ogr.OFTReal)
        fldDef2 = ogr.FieldDefn('coordy', ogr.OFTReal)
        fldDef3 = ogr.FieldDefn('ddate', ogr.OFTDate)
        fldDef4 = ogr.FieldDefn('dtime', ogr.OFTString)
        fldDef4.SetWidth(5) #5 char string width
        fldDef5 = ogr.FieldDefn('ID', ogr.OFTInteger)
        layer = dataSource.GetLayer()
        layer.CreateField(fldDef)
        layer.CreateField(fldDef2)
        layer.CreateField(fldDef3)
        layer.CreateField(fldDef4)
        layer.CreateField(fldDef5)
        print ("Step 4: Fields to file %s successfully added" % shpfile)
        #fill shapefiles with dates, times
        feature = layer.GetNextFeature()
        while feature:
            feature.SetField("ddate", yearfield + "-" + monthfield + '-' + dayfield)
            layer.SetFeature(feature)
            feature.SetField("dtime", timefield)
            layer.SetFeature(feature)
            feature = layer.GetNextFeature()
        dataSource.Destroy()
        print ("Step 5: Date and Time added to files successfully ")



#merge shapefiles to one

import subprocess
#command = './mergeshapefiles2.sh'
#process = subprocess.Popen(command.split(), stdout=subprocess.PIPE)
#process=subprocess.Popen('../mergeshapefiles2.sh %s' % folderProduction, shell=True)
#process=subprocess.Popen(['./mergeshapefiles2.sh %s' % (str(folderProduction)], shell=True)
#os.system(' ./mergeshapefiles2.sh {} ' .format(str(folderProduction))) 
ffl = date_time + '_prod'
#process=subprocess.Popen(['./mergeshapefiles2.sh',str(ffl)],shell=True,stdin=subprocess.PIPE,stderr=subprocess.PIPE)
process=subprocess.Popen('./mergeshapefiles2.sh %s' % (str(ffl),), shell=True)
#print process.communicate() #now you should see your output
output, error = process.communicate()
print ("Step 6: Files merged successfully ")

#insert merged shapefile to database
#connection strings for database
import psycopg2
con = psycopg2.connect(database="smoke", user="postgres", password="2n3CsIHfP5Qw4sESsU0T", host="epidemics.space.noa.gr", port="5432")
#con = psycopg2.connect(database="smokedemo", user="postgres", password="root", host="192.168.60.60", port="5432")
con.autocommit = True
print("Database opened successfully")

#fill ID column of shapefile with nect increment
cur = con.cursor()
#cur.execute('''select max(id) from smoke_polygons;''')
#cur.execute('''select max(id) from smoke_polygons_case_studies;''')
#maxid = cur.fetchone()[0]
#open merge.shp and set id to column
driver = ogr.GetDriverByName('ESRI Shapefile')
fn = ncfolder + '/merge.shp'
dataSource = driver.Open(fn, 1) # 1 for writing, 0 for reading
layer = dataSource.GetLayer()
feature = layer.GetNextFeature()
#maxid=0
while feature:
    #maxid = maxid + 1
    #feature.SetField("ID", maxid)
    layer.SetFeature(feature)
    feature = layer.GetNextFeature()
    
dataSource.Destroy()
print ("Step 7: ID updated successfully ")


#ogr2ogr -f "PostgreSQL" PG:"host=epidemics.space.noa.gr port=5432 dbname=smoke user=postgres password=2n3CsIHfP5Qw4sESsU0T" merge.shp -nln bbb -nlt MULTIPOLYGON
os.system("ogr2ogr -f 'PostgreSQL' PG:'host=epidemics.space.noa.gr port=5432 dbname=smoke user=postgres password=2n3CsIHfP5Qw4sESsU0T' merge.shp -nln temp_data_alerts -nlt MULTIPOLYGON")
#os.system("ogr2ogr -f 'PostgreSQL' PG:'host=192.168.60.60 port=5432 dbname=smokedemo user=postgres password=root' merge.shp -nln temp_data -nlt MULTIPOLYGON")
print ("Step 8: Data update to DB successfully ")
cur.execute('''alter table temp_data_alerts RENAME COLUMN wkb_geometry TO geom;''')
cur.execute('''delete from temp_data_alerts where DN = 0;''')
cur.execute('''alter table temp_data_alerts RENAME COLUMN DN TO grid_code;''')
cur.execute('''SELECT updategeometrysrid('temp_data_alerts','geom',4326);''')
cur.execute('''Update temp_data_alerts set coordx = ST_X(st_centroid(geom));''')
cur.execute('''Update temp_data_alerts set coordy = ST_Y(st_centroid(geom));''')
cur.execute('''alter table temp_data_alerts drop column ogc_fid;''')
#cur.execute('''insert into smoke_polygons_case_studies (id, geom, grid_code, coordx, coordy, ddate, dtime) select id, geom, grid_code, coordx, coordy, ddate, dtime from temp_data_alerts;''')
cur.execute('''insert into smoke_polygons_case_studies (geom, grid_code, coordx, coordy, ddate, dtime) select geom, grid_code, coordx, coordy, ddate, dtime from temp_data_alerts;''')
cur.execute('''drop table temp_data_alerts;''')
con.close()
print ("Step 9: Table append successfully ")

#move nc files
import shutil
newncfolder = ncfolder + '/' + date_time + '_ncs'
os.mkdir(newncfolder)
for ncfile in os.listdir(ncfolder):
    if ncfile.endswith(".nc"):
        shutil.move(os.path.join(ncfolder, ncfile), newncfolder +'/')
shutil.copyfile('merge.shp',folderProduction + '/merge.shp')
shutil.copyfile('merge.dbf',folderProduction + '/merge.dbf')
shutil.copyfile('merge.shx',folderProduction + '/merge.shx')
os.remove("merge.shp")
os.remove("merge.dbf")
os.remove("merge.shx")