#!/usr/bin/env python
# coding: utf-8

# In[1]:


# imports
from sqlalchemy import create_engine, Column, String, Integer, Float, Date, MetaData, Table, type_coerce
from sqlalchemy.orm import mapper, create_session
import csv
from sqlalchemy import case
import numpy as np
from sqlalchemy.ext.automap import automap_base
import matplotlib.pyplot as plt
import sqlalchemy.types as types
import numbers
from sqlalchemy.sql import and_, or_, not_, func
from sqlalchemy.sql import select
import datetime as dt
import os
import re
from mpl_toolkits.basemap import Basemap
import pandas as pd
get_ipython().run_line_magic('matplotlib', 'inline')


# In[2]:


# definitions
basepath='/ocean/eolson/MEOPAR/obs/'
hdir='ECBuoy'
dbname='ECBuoy'
dbpath=os.path.join(basepath,hdir,dbname)
print(dbpath)


# In[3]:


engine = create_engine('sqlite:///'+dbpath+'.sqlite')


# In[4]:


connection=engine.connect()
if engine.dialect.has_table(connection,'FlowTBL'):
    # delete existing 
    connection.execute('DROP TABLE FlowTBL')
connection.close()


# In[5]:


Base = automap_base()
# reflect the tables in salish.sqlite:
Base.prepare(engine, reflect=True)
# mapped classes have been created
FBuoyTBL=Base.classes.FBuoyTBL


# In[6]:


session = create_session(bind = engine, autocommit = False, autoflush = True)


# In[7]:


# create JDFLocsTBL to identify rows in Strait of Juan de Fuca
class Flow(Base):
        __table__=Table('FlowTBL', Base.metadata,
                    Column('ID', Integer, primary_key=True),
                    Column('Year', Integer),
                    Column('Month', Integer),
                    Column('Day', Integer),
                    Column('DecDay',Integer),
                    Column('RateHope', Float),# m^3/s
                    Column('MeanTurb', Float))
Base.metadata.create_all(engine)
session = create_session(bind = engine, autocommit = False, autoflush = True)


# Add flow data to table

# In[8]:


df=pd.read_csv('/ocean/eolson/MEOPAR/obs/ECRivers/Flow/FraserHopeDaily__Apr-12-2018_11_07_27PM.csv',
              sep=',',header=1)
dts=[dt.datetime(yr,1,1)+dt.timedelta(days=(float(dd)-1)) for yr,dd in df[['YEAR','DD']].values]
df['Month']=[idt.month for idt in dts]
df['Day']=[idt.day for idt in dts]
df['RateHope']=df['Value']
df['Year']=df['YEAR']
df2=pd.DataFrame(df.loc[:,['Year','Month','Day','RateHope']][df.PARAM == 1])# select flow data (not height data)


# In[9]:


for ind, row in df2.iterrows():
    #print(dict(row))
    #idict={'Year':row['Year'],'Month':row['Month']}
    idict=dict(row)
    idict['DecDay']=(dt.date(int(row['Year']),int(row['Month']),int(row['Day']))-dt.date(1900,1,1)).days
    idict['MeanTurb']=np.nanmean([i for i in session.query(FBuoyTBL.Turb_NTU).filter(
        func.round(FBuoyTBL.DecDay-.5,0)==idict['DecDay'])]) if session.query(FBuoyTBL.Turb_NTU).filter(
        func.round(FBuoyTBL.DecDay-.5,0)==idict['DecDay']).count()>0 else np.nan
    if ind<20 or ind%365==0:
        print(idict)
    session.execute(Flow.__table__.insert().values(idict))
#    print(row['Year'], row['Month'])


# In[10]:


session.commit()


# In[11]:


session.close()
engine.dispose()


# In[ ]: