import numpy as np
import matplotlib.pyplot as plot
import pandas as pd
import datetime as dt
from salishsea_tools import evaltools as et

%matplotlib inline

# path to model files:
PATH= '/results/SalishSea/nowcast-green.201812/'

# start and end dates for analysis:
start_date = dt.datetime(2015,1,1)
end_date = dt.datetime(2015,3,1)

# number of days per model file:
flen=1

# dictionary mapping desired model variables to the file types where they are found
filemap={'diatoms':'ptrc_T','ciliates':'ptrc_T',
         'flagellates':'ptrc_T','vosaline':'grid_T','votemper':'grid_T'}

# dictionary mapping model file types to their time resolution in hours (1 is hourly files, 24 is daily)
fdict={'ptrc_T':1,'grid_T':1}

# results format
# -- nowcast: files like 01jan15/SalishSea_1h_20150101_20150101_ptrc_T.nc
# -- long: files like SalishSea_1h_20150206_20150804_ptrc_T_20150427-20150506.nc, all in one directory
namfmt='nowcast'

        from sqlalchemy import create_engine, case
        from sqlalchemy.orm import create_session
        from sqlalchemy.ext.automap import automap_base
        from sqlalchemy.sql import and_, or_, not_, func

basedir='/ocean/shared/SalishSeaCastData/DFO/CTD/'
dbname='DFO_CTD.sqlite'
datelims=(start_date,end_date)

    engine = create_engine('sqlite:///' + basedir + dbname, echo = False)
    Base = automap_base()
    # reflect the tables in salish.sqlite:
    Base.prepare(engine, reflect=True)
    # mapped classes have been created
    # existing tables:
    StationTBL=Base.classes.StationTBL
    ObsTBL=Base.classes.ObsTBL
    CalcsTBL=Base.classes.CalcsTBL
    session = create_session(bind = engine, autocommit = False, autoflush = True)
    SA=case([(CalcsTBL.Salinity_T0_C0_SA!=None, CalcsTBL.Salinity_T0_C0_SA)], else_=
             case([(CalcsTBL.Salinity_T1_C1_SA!=None, CalcsTBL.Salinity_T1_C1_SA)], else_=
             case([(CalcsTBL.Salinity_SA!=None, CalcsTBL.Salinity_SA)], else_= None)))
    CT=case([(CalcsTBL.Temperature_Primary_CT!=None, CalcsTBL.Temperature_Primary_CT)], else_=
             case([(CalcsTBL.Temperature_Secondary_CT!=None, CalcsTBL.Temperature_Secondary_CT)], else_=CalcsTBL.Temperature_CT))
    ZD=case([(ObsTBL.Depth!=None,ObsTBL.Depth)], else_= CalcsTBL.Z)

Fl=case([(ObsTBL.Fluorescence_URU_Wetlabs!=None, ObsTBL.Fluorescence_URU_Wetlabs)], else_= ObsTBL.Fluorescence_URU_Seapoint)
Fl_units=case([(ObsTBL.Fluorescence_URU_Wetlabs!=None, ObsTBL.Fluorescence_URU_Wetlabs_units)], else_= ObsTBL.Fluorescence_URU_Seapoint)

start_y=datelims[0].year
start_m=datelims[0].month
start_d=datelims[0].day
end_y=datelims[1].year
end_m=datelims[1].month
end_d=datelims[1].day

qry=session.query(StationTBL.StartYear.label('Year'),StationTBL.StartMonth.label('Month'),
              StationTBL.StartDay.label('Day'),StationTBL.StartHour.label('Hour'),
              StationTBL.Lat,StationTBL.Lon,ZD.label('Z'),SA.label('SA'),CT.label('CT'),
                  Fl.label('Fl'),Fl_units.label('Fl_units')).\
        select_from(StationTBL).join(ObsTBL,ObsTBL.StationTBLID==StationTBL.ID).\
        join(CalcsTBL,CalcsTBL.ObsTBLID==ObsTBL.ID).filter(and_(Fl>=0,
                                                                or_(StationTBL.StartYear>start_y,
                                                                 and_(StationTBL.StartYear==start_y, StationTBL.StartMonth>start_m),
                                                                 and_(StationTBL.StartYear==start_y, StationTBL.StartMonth==start_m, StationTBL.StartDay>=start_d)),
                                                                or_(StationTBL.StartYear<end_y,
                                                                 and_(StationTBL.StartYear==end_y,StationTBL.StartMonth<end_m),
                                                                 and_(StationTBL.StartYear==end_y,StationTBL.StartMonth==end_m, StationTBL.StartDay<=end_d)),
                                                            StationTBL.Lat>47-3/2.5*(StationTBL.Lon+123.5),
                                                            StationTBL.Lat<47-3/2.5*(StationTBL.Lon+121),
                                                            StationTBL.Include==True,ObsTBL.Include==True,CalcsTBL.Include==True))
df1=pd.DataFrame(qry.all())
df1['dtUTC']=[dt.datetime(int(y),int(m),int(d))+dt.timedelta(hours=h) for y,m,d,h in zip(df1['Year'],df1['Month'],df1['Day'],df1['Hour'])]

data=et.matchData(data=df1,filemap=filemap, fdict=fdict, mod_start=start_date, mod_end=end_date, 
                  mod_nam_fmt=namfmt, mod_basedir=PATH, mod_flen=flen)