#! /usr/bin/env python
# -*- coding: iso-8859-15 -*-

import gravi_trend as gt
from optparse import OptionParser
import glob
import os
import numpy as np
import matplotlib as mpl
import timeit
# mpl.use('Agg') # necessary to be able to generate the reports even without X server
from matplotlib.pylab import plt

db = gt.oivisdb.OIVisDB('/Users/lebouquj/Datas/GVRAID/reduced/oivisdb.db')
db.delete_constrains("MJD<30000 or STA1_INDEX>99")

gt.plots.init_dictionaries(db);


db = gt.oivisdb.OIVisDB()
fls = glob.glob("/Users/lebouquj/Datas/GVRAID/reduced/GRAV*.fits")
for f in fls[0:10]:        
    db.feed(f)

cal = db.execute("SELECT VIS2DATA_MED,vis2.FILEID,FILENAME from vis2,files,headers where vis2.FILEID=headers.FILEID and vis2.FILEID=files.FILEID and [ESO PRO CATG]='SINGLE_CAL_VIS'").fetchall()
sci = db.execute("SELECT VIS2DATA_MED,vis2.FILEID,FILENAME from vis2,files,headers where vis2.FILEID=headers.FILEID and vis2.FILEID=files.FILEID and [ESO PRO CATG]='SINGLE_SCI_VIS'").fetchall()
tf = db.execute("SELECT VIS2DATA_MED,vis2.FILEID,FILENAME from vis2,files,headers where vis2.FILEID=headers.FILEID and vis2.FILEID=files.FILEID and [ESO PRO CATG]='SINGLE_CAL_TF'").fetchall()

diam = db.execute("SELECT vis2.VIS2DATA_MED/tf.VIS2DATA_MED from vis2,vis2 as tf,headers as h1,headers as h2 where vis2.MJD=tf.MJD and vis2.FILEID=h1.FILEID and tf.FILEID=h2.FILEID and h1.[ESO PRO CATG]='SINGLE_CAL_VIS' and h2.[ESO PRO CATG]='SINGLE_CAL_TF' and tf.STA1_INDEX=vis2.STA1_INDEX and tf.STA2_INDEX=vis2.STA2_INDEX").fetchall()
    
gt.old_plots.plot_all_trending_new(db)

db.execute('SELECT FILEID,[ESO PRO CATG] from headers where [ESO PRO CATG] like "%CALIBRATED" or  [ESO PRO CATG] like "TF_%_SCI"').fetchall()

fileid = db.execute('SELECT DISTINCT FILEID from headers where [ESO PRO CATG] like "%CALIBRATED" or  [ESO PRO CATG] like "TF_%_SCI"').fetchall()

# very long
db.executemany("DELETE from vis2 where FILEID=?",fileid)

c = db.execute('SELECT TARGET from vis2,targets where vis2.FILEID=targets.FILEID and vis2.TARGET_ID=targets.TARGET_ID').fetchall()

c = db.execute('SELECT catg2colors(TARGET,[ESO PRO CATG]),catg2alpha([ESO PRO CATG]) from vis2,targets,headers where vis2.FILEID=targets.FILEID and vis2.TARGET_ID=targets.TARGET_ID and vis2.FILEID=headers.FILEID and [ESO PRO CATG] like "TF%"').fetchall()

c = db.execute('SELECT catg2colors(TARGET,[ESO PRO CATG]),catg2alpha([ESO PRO CATG]),setup2markers('+gt.plots.setupString+') from vis2,targets,headers where vis2.FILEID=targets.FILEID and vis2.TARGET_ID=targets.TARGET_ID and vis2.FILEID=headers.FILEID and [ESO PRO CATG] like "TF%"').fetchall()


q = db.query().query(x="ar1.STA_NAME||ar2.STA_NAME",tbls="vis")
q.link_arrays(["ar1", "ar2"], "vis")
baselist = q.execute(distinct=True).fetchall()

# Test for loop also on axes
def make_base2axes_dictionary(db, fig):
    q = db.query().query(x="ar1.STA_NAME||ar2.STA_NAME",tbls="vis")
    q.link_arrays(["ar1", "ar2"], "vis")
    q.add_conds("ar1.STA_NAME NOT LIKE 'S%'")
    baselist = q.execute(distinct=True).fetchall()
    return { base:fig.get_axes()[i] for i,base in enumerate(baselist, start=1) }

q = db.query().query(x="ar1.STA_NAME||ar2.STA_NAME",tbls="vis")
q.link_arrays(["ar1", "ar2"], "vis")
q.add_conds("ar1.STA_NAME NOT LIKE 'S%'")
baselist = q.execute().fetchall()

dic = make_base2axes_dictionary(db, fig)
an = np.array([ dic[base] for base in baselist ])
for s in set(an):
    print((an[ an==s ]))
    print((an[ an==s ]))
    print((an[ an==s ]))
    print((an[ an==s ]))
    print((an[ an==s ]))

# much faster
db.execute("select distinct [ESO PRO CATG] from vis,headers where vis.FILEID=headers.FILEID").fetchall()

db.execute("select distinct FILEID from headers").fetchall()
db.execute("select distinct STA_INDEX from arrays").fetchall()
db.execute("select distinct TARGET_ID from targets").fetchall()

db.execute("SELECT  ar1.STA_NAME||'-'||ar2.STA_NAME FROM vis AS vis, arrays AS ar2, arrays AS ar1  WHERE ar1.[STA_INDEX] = vis.[STA1_INDEX] AND ar2.[STA_INDEX] = vis.[STA2_INDEX] AND ar1.[FILEID] = vis.[FILEID] AND ar2.[FILEID] = vis.[FILEID] AND ar1.STA_NAME NOT LIKE 'S%'").fetchall()


    
#fls = glob.glob("./calibrated/tf_*.GRAV*.fits")
#for f in fls:        
#    db.feed(f)
    
# db.update_basename()

#len( db.execute("SELECT * from vis2,targets,arrays as ar1, arrays as ar2 WHERE ar1.[FILEID]=vis2.[FILEID] and ar2.[FILEID]=vis2.[FILEID] and ar1.STA_INDEX=vis2.STA1_INDEX and ar2.STA_INDEX=vis2.STA2_INDEX and ar1.STA_NAME='' and ar2.STA_NAME='' and targets.[FILEID] = vis2.[FILEID] and TARGET='R_For' and ").fetchall() )    
#
#len( db.execute("SELECT * from vis2,targets where vis2.[FILEID]=targets.[FILEID] and vis2.[TARGET_ID]=targets.[TARGET_ID] and TARGET='R_For'").fetchall() )
#
#len( db.execute("SELECT * from vis2").fetchall() )
#
#len( db.execute("SELECT * from vis2,arrays as ar1,arrays as ar2 where ar1.[FILEID]=vis2.[FILEID] and ar1.STA_INDEX=vis2.STA1_INDEX and ar2.[FILEID]=vis2.[FILEID] and ar2.STA_INDEX=vis2.STA2_INDEX and ar1.STA_NAME='B2' and ar2.STA_NAME='A0'").fetchall() )
#
#len( db.execute("SELECT TARGET,ar1.STA_NAME,ar2.STA_NAME from vis2,targets,arrays as ar1,arrays as ar2 where ar1.[FILEID]=vis2.[FILEID] and ar1.STA_INDEX=vis2.STA1_INDEX and ar2.[FILEID]=vis2.[FILEID] and ar2.STA_INDEX=vis2.STA2_INDEX and ar1.STA_NAME='B2' and ar2.STA_NAME='A0' and vis2.[FILEID]=targets.[FILEID] and vis2.[TARGET_ID]=targets.[TARGET_ID] and TARGET='R_For'").fetchall() )
#
#db.execute("SELECT MJD,TIME,TARGET,ar1.STA_NAME,ar2.STA_NAME,DET_NAME,POLAR_ID from vis2,targets,arrays as ar1,arrays as ar2 where ar1.[FILEID]=vis2.[FILEID] and ar1.STA_INDEX=vis2.STA1_INDEX and ar2.[FILEID]=vis2.[FILEID] and ar2.STA_INDEX=vis2.STA2_INDEX and ar1.STA_NAME='B2' and ar2.STA_NAME='A0' and vis2.[FILEID]=targets.[FILEID] and vis2.[TARGET_ID]=targets.[TARGET_ID] and TARGET='R_For'").fetchall()
#
#cc = db.execute("SELECT ar1.STA_NAME, from vis2,arrays as ar1,arrays as ar2 where ar1.[FILEID]=vis2.[FILEID] and ar1.STA_INDEX=vis2.STA1_INDEX and ar2.[FILEID]=vis2.[FILEID] and ar2.STA_INDEX=vis2.STA2_INDEX and ar1.STA_NAME='B2' and ar2.STA_NAME='A0'")
#
#t1 = cc.execute("SELECT TARGET,STA1_INDEX,STA2_INDEX,DET_NAME,POLAR_ID from vis2,targets where vis2.[FILEID]=targets.[FILEID] and vis2.[TARGET_ID]=targets.[TARGET_ID] and TARGET='R_For'")
#
#len( db.execute("SELECT MJD,TARGET,VIS2DATA,VIS2ERR,ar1.STA_NAME,ar2.STA_NAME,DET_NAME,POLAR_ID from vis2,targets,arrays as ar1,arrays as ar2 where ar1.[FILEID]=vis2.[FILEID] and ar1.STA_INDEX=vis2.STA1_INDEX and ar2.[FILEID]=vis2.[FILEID] and ar2.STA_INDEX=vis2.STA2_INDEX and vis2.[FILEID]=targets.[FILEID] and vis2.[TARGET_ID]=targets.[TARGET_ID]").fetchall() )
#
#toto = db.execute("SELECT MJD,TARGET,VIS2DATA,VIS2ERR,EFF_WAVE,ar1.STA_NAME,ar2.STA_NAME from vis2,targets,wavelengths,arrays as ar1,arrays as ar2 where ar1.[FILEID]=vis2.[FILEID] and ar1.STA_INDEX=vis2.STA1_INDEX and ar2.[FILEID]=vis2.[FILEID] and ar2.STA_INDEX=vis2.STA2_INDEX and vis2.[FILEID]=targets.[FILEID] and vis2.[TARGET_ID]=targets.[TARGET_ID] and wavelengths.[FILEID]=vis2.[FILEID] and wavelengths.[POLAR_ID]=vis2.[POLAR_ID] and wavelengths.[DET_NAME]=vis2.[DET_NAME]").fetchall() 
#
#
#toto = db.execute("SELECT MJD,TARGET,VIS2DATA,VIS2ERR,EFF_WAVE,UCOORD,VCOORD,ar1.STA_NAME,ar2.STA_NAME,[ESO INS POLA MODE],[ESO INS SPEC RES],[ESO INS TIM2 PERIOD],[ESO DET2 SEQ1 DIT] from vis2,targets,wavelengths,headers,arrays as ar1,arrays as ar2 where ar1.[FILEID]=vis2.[FILEID] and ar1.STA_INDEX=vis2.STA1_INDEX and ar2.[FILEID]=vis2.[FILEID] and ar2.STA_INDEX=vis2.STA2_INDEX and vis2.[FILEID]=targets.[FILEID] and vis2.[TARGET_ID]=targets.[TARGET_ID] and wavelengths.[FILEID]=vis2.[FILEID] and wavelengths.[POLAR_ID]=vis2.[POLAR_ID] and wavelengths.[DET_NAME]=vis2.[DET_NAME] and headers.[FILEID]=vis2.[FILEID]  and TARGET='R_For' and ar1.STA_NAME='D0' and ar2.STA_NAME='A0'").fetchall() 
#
#toto = db.execute("SELECT MJD,TARGET,VISAMP,VISAMPERR,EFF_WAVE,UCOORD,VCOORD,ar1.STA_NAME,ar2.STA_NAME,[ESO INS POLA MODE],[ESO INS SPEC RES],[ESO INS TIM2 PERIOD],[ESO DET2 SEQ1 DIT] from vis,targets,wavelengths,headers,arrays as ar1,arrays as ar2 where ar1.[FILEID]=vis.[FILEID] and ar1.STA_INDEX=vis.STA1_INDEX and ar2.[FILEID]=vis.[FILEID] and ar2.STA_INDEX=vis.STA2_INDEX and vis.[FILEID]=targets.[FILEID] and vis.[TARGET_ID]=targets.[TARGET_ID] and wavelengths.[FILEID]=vis.[FILEID] and wavelengths.[POLAR_ID]=vis.[POLAR_ID] and wavelengths.[DET_NAME]=vis.[DET_NAME] and headers.[FILEID]=vis.[FILEID]").fetchall() 
#
#toto = db.execute("SELECT MJD,TARGET,VISAMP,VISAMPERR,EFF_WAVE,UCOORD,VCOORD,DET_NAME,POLAR_ID,ar1.STA_NAME,ar2.STA_NAME,[ESO INS POLA MODE],[ESO INS SPEC RES],[ESO INS TIM2 PERIOD],[ESO DET2 SEQ1 DIT] from vis,targets,wavelengths,headers,arrays as ar1,arrays as ar2 where ar1.[FILEID]=vis.[FILEID] and ar1.STA_INDEX=vis.STA1_INDEX and ar2.[FILEID]=vis.[FILEID] and ar2.STA_INDEX=vis.STA2_INDEX and vis.[FILEID]=targets.[FILEID] and vis.[TARGET_ID]=targets.[TARGET_ID] and wavelengths.[FILEID]=vis.[FILEID] and wavelengths.[INSNAME]=vis.[INSNAME] and headers.[FILEID]=vis.[FILEID]").fetchall()
#

# toto = db.execute("SELECT VIS2DATA,VISAMP from vis,vis2,targets,wavelengths,headers,arrays as ar1,arrays as ar2 where ar1.[FILEID]=vis.[FILEID] and ar1.STA_INDEX=vis.STA1_INDEX and ar2.[FILEID]=vis.[FILEID] and ar2.STA_INDEX=vis.STA2_INDEX and vis.[FILEID]=targets.[FILEID] and vis.[TARGET_ID]=targets.[TARGET_ID] and wavelengths.[FILEID]=vis.[FILEID] and wavelengths.[INSNAME]=vis.[INSNAME] and headers.[FILEID]=vis.[FILEID] and vis.[MJD]=vis2.[MJD] and vis.[STA1_INDEX]=vis2.[STA1_INDEX] and vis.[STA2_INDEX]=vis2.[STA2_INDEX] and vis.[INSNAME]=vis2.[INSNAME]").fetchall()
# 
# toto = db.execute("SELECT VISAMP from vis,targets,wavelengths,headers,arrays as ar1,arrays as ar2 where ar1.[FILEID]=vis.[FILEID] and ar1.STA_INDEX=vis.STA1_INDEX and ar2.[FILEID]=vis.[FILEID] and ar2.STA_INDEX=vis.STA2_INDEX and vis.[FILEID]=targets.[FILEID] and vis.[TARGET_ID]=targets.[TARGET_ID] and wavelengths.[FILEID]=vis.[FILEID] and wavelengths.[INSNAME]=vis.[INSNAME] and headers.[FILEID]=vis.[FILEID]").fetchall()






#
# db.execute("alter table vis2 add column BASENAME TEXT")
#