#crear ambiente virtual
#instalar GOES PIP install GOES
#instalar requests pip install requests
#pip install --upgrade GOES 
### 220701 pip install pandas. y pip install geojsonls down

import GOES
from datetime import datetime, timedelta

import sys
##web params
webpath=sys.argv[1]
secs=int(sys.argv[2])
addtodb=""
try:
    addtodb=sys.argv[3]
except IndexError:
    pass


n = datetime.today()
d = n - timedelta(hours=0, seconds=secs)
ahora = n.strftime("%Y%m%d-%H%M%S")
desde = d.strftime("%Y%m%d-%H%M%S")	
print('Desde:',desde,'Hasta:',ahora)

##descargar
#flist=GOES.download('goes16',prod,DateTimeIni=desde,DateTimeFin=ahora,channel=['GLM'],path_out='./down/')

flist = GOES.download('goes16', 'GLM-L2-LCFA',
                      DateTimeIni=desde,DateTimeFin=ahora,
                      path_out='./down/')

print(flist)

##### 2021.06.10 para GLM
#import GOES
import pandas as pd

#flist=GOES.locate_files('/home/joao/Downloads/GOES-16/GLM/', 'OR_GLM*.nc','20210313-223500', '20210313-224000')
ds = GOES.open_mfdataset(flist)

flash_lon = ds.variable('flash_lon')
flash_lat = ds.variable('flash_lat')

ti = ds.variable('flash_time_offset_of_first_event')
tf = ds.variable('flash_time_offset_of_last_event')
flash_time = ti.data+(tf.data-ti.data)/2.0

data = {'lon':flash_lon.data, 'lat':flash_lat.data}

df = pd.DataFrame(data, index=flash_time)
df['fecha_hora']=df.index.strftime('%Y-%m-%d %H:%M:%S')


import geojson


def data2geojson(df):
    features = []
    insert_features = lambda X: features.append(
            geojson.Feature(geometry=geojson.Point((float(X["lon"]),
                                                    float(X["lat"]))),
                            properties=dict(fecha_hora=X['fecha_hora']
                                            )))
    df.apply(insert_features, axis=1)
    with open(webpath+'/rayos.geojson', 'w', encoding='utf8') as fp:
        geojson.dump(geojson.FeatureCollection(features), fp, sort_keys=True, ensure_ascii=False)
    print('archivo generado rayos.geojson en: ',webpath)


data2geojson(df)

if addtodb=='Y':
    ##pip install psycopg2-binary
    import psycopg2
    connection = None

    ###PGCNX
    try:
        # Connect to an existing database
        connection = psycopg2.connect(user="geodb",
                                    password="geoUniversal$%1",
                                    host="154.12.253.150",
                                    port="11014",
                                    database="geodb")

        # Create a cursor to perform database operations
        cursor = connection.cursor()
        # Print PostgreSQL details
        print("🟢 CONNECTION SUCCESFUL: PostgreSQL server information")
        print(connection.get_dsn_parameters(), "\n")
        # Executing a SQL query
        cursor.execute("SELECT version();")
        # Fetch result
        record = cursor.fetchone()
        print("You are connected to - ", record, "\n")

        #  show_tables(cursor)
        #  get_table_cols('station')

    except (Exception, Error) as error:
        print("❌ Error while connecting to PostgreSQL", error)
    ###PGCNX

    print('Agregando a la base de datos')
    i=0
    for index, row in df.iterrows():
        i=i+1
        ##print(i,'Processing: ',row['fecha_hora'],row['lat'],row['lon'])
        query="INSERT INTO goes_glm (the_geom , fecha_hora) VALUES (ST_GeomFromText('POINT("+str(round(row['lon'],5))+" "+str(round(row['lat'],5))+")', 4326), '"+row['fecha_hora']+"');" 
        ##print(i,query);
        cursor.execute(query)
    print('Processed: ',i)
    connection.commit()
    if (connection):
        cursor.close()
        connection.close()
        print("PostgreSQL connection is closed")

### como hacer el cluster de rayos. o densidad
 # 980  gdal_rasterize -burn 1.0 -tr 0.05 0.05 -a_nodata 0.0 -ot Byte -of GTiff -add /cloudclusters/default_site/data/test/rayos.geojson /cloudclusters/default_site/data/test/rayos.tif
 # 982  gdal_translate -projwin -120 35 -50 0 /cloudclusters/default_site/data/test/rayos.tif /cloudclusters/default_site/data/test/rayos_crop.tif
 #  986  gdalwarp -t_srs EPSG:3857 -r cubicspline /cloudclusters/default_site/data/test/rayos_crop.tif rayos_3857.tif -overwrite
 # 995  gdaldem color-relief rayos_3857.tif -alpha -nearest_color_entry ./colorsGLM.txt /cloudclusters/default_site/data/test/rayos.png