
# import matplotlib as mpl
# mpl.use('Agg') # necessary to be able to generate the reports even without X server
from matplotlib.pylab import plt
import numpy as np
from .log import Log, ERROR, WARNING, NOTICE
import importlib
log = Log()
from .import oivisquery;
try:
    from importlib import reload
except:
    pass
importlib.reload(oivisquery)
from .oivisquery import query, first, row
from datetime import datetime
import random
import glob
import os

# http://www.tutorialspoint.com/sql/sql-useful-functions.htm

_debug_ = False

SETUPSTRING = "[ESO INS SPEC RES]||'-'||[ESO INS POLA MODE]||'-'||[ESO INS TIM2 PERIOD]||'-'||[ESO DET2 SEQ1 DIT]"

# SETUPSTRING = "CONCAT_WS('-',[ESO INS SPEC RES],[ESO INS POLA MODE],[ESO INS TIM2 PERIOD],[ESO DET2 SEQ1 DIT])"

plot_defaults = {
    "directory":".",
    "file_ext":"png",
    "orientation":"portrait",
    "papertype":"a4",
    "timestamp":True, # put a little creation timestamp on figures
    "timestamp_pos":(0.01, 0.98),
    "save":True,   # save figure
    "close_figure":True # close the figure to avoid matplotlib stacking figures
}

####################################
# Macro to plot all data
####################################

#
# Init the various function aplied to column to convert them
# into colors, markers, indexes...
#
def init_dictionaries(db):

    # Convert the basename into a num 1,2,3,4...
    global base2int_dict
    q = db.query().query(x="ar1.STA_NAME||ar2.STA_NAME",tbls="vis")
    q.link_arrays(["ar1", "ar2"], "vis")
    base2int_dict = { base[0]:i for i,base in enumerate(q.execute(distinct=True).fetchall(), start=1) }

    def base2int(basename):
        global base2int_dict
        return base2int_dict[basename]

    db.db.create_function("base2int",1,base2int)

    # Convert the triplet into a num 1,2,3,4...
    global triplet2int_dict
    q = db.query().query(x="ar1.STA_NAME||ar2.STA_NAME||ar3.STA_NAME",tbls="t3")
    q.link_arrays(["ar1", "ar2","ar3"], "t3")
    triplet2int_dict = { base:i for i,base in enumerate(q.execute(distinct=True).fetchall(), start=1) }

    def triplet2int(basename):
        global triplet2int_dict
        return triplet2int_dict[basename]

    db.db.create_function("triplet2int",1,triplet2int)

    # Convert the column TARGET into a color 'b','g','r'...
    global target2colors_dict
    target2colors_dict = make_dictionary(db, list("bgrcmy"), "SELECT DISTiNCT TARGET FROM targets", lambda r:r[0])

    def target2colors(target):
        global target2colors_dict
        return target2colors_dict[target]

    db.db.create_function("target2colors",1,target2colors)

    # Convert the setupstring into a marker 'o','+','s'...
    global setup2markers_dict
    setup2markers_dict = make_dictionary(db, list("o+s^xvD"), "SELECT DISTiNCT "+SETUPSTRING+" FROM headers", lambda r:r[0])

    def setup2markers(setup):
        global setup2markers_dict
        return setup2markers_dict[setup]

    db.db.create_function("setup2markers",1,setup2markers)

    # Convert the couple TARGET,[ESO PRO CATG] into
    # color by target, but force black if a the PRO.CATG is TF
    def catg2colors(target,catg):
        global target2colors_dict
        return 'k' if 'TF' in catg else target2colors_dict[target]

    db.db.create_function("catg2colors",2,catg2colors)

    # Convert the catg into '' or 'k' for TF
    global catg2black_dict
    catg2black_dict = {
        'SINGLE_CAL_VIS':'','DUAL_CAL_VIS':'',
        'SINGLE_SCI_VIS':'','DUAL_SCI_VIS':'',
        'SINGLE_CAL_TF':'k','DUAL_CAL_TF':'k',
        'SINGLE_SCI_TF':'k','DUAL_SCI_TF':'k',
        'SINGLE_SCI_VIS_CALIBRATED':'',
        'DUAL_SCI_VIS_CALIBRATED':'' }

    def catg2black(catg):
        global catg2black_dict
        return catg2black_dict[catg]

    db.db.create_function("catg2black",1,catg2black)

    # Convert the catg into alpha
    global catg2alpha_dict
    catg2alpha_dict = {
        'SINGLE_CAL_VIS':0.1,'DUAL_CAL_VIS':0.1,
        'SINGLE_SCI_VIS':1.0,'DUAL_SCI_VIS':1.0,
        'SINGLE_CAL_TF':1.0,'DUAL_CAL_TF':1.0,
        'SINGLE_SCI_TF':0.1,'DUAL_SCI_TF':0.1,
        'SINGLE_SCI_VIS_CALIBRATED':1.0,
        'DUAL_SCI_VIS_CALIBRATED':1.0 }

    # Convert [ESO PRO CATG] into alpha
    # 1.0 by default, VIS on calib in 0.1, TF on science is 0.1
    def catg2alpha(catg):
        global catg2alpha_dict
        return catg2alpha_dict[catg]

    db.db.create_function("catg2alpha",1,catg2alpha)

    # Convert INSNAME into colors 'r','b' by detector name
    def insdet2colors(insname):
        if 'FT' in insname:
            return 'r'
        elif 'SC' in insname:
            return 'b'
        else:
            return 'k'

    db.db.create_function("insdet2colors",1,insdet2colors)

    # Convert INSNAME into markers '^','+' by detector name
    def insdet2markers(insname):
        if 'FT' in insname:
            return '+'
        elif 'SC' in insname:
            return '^'
        else:
            return 'o'

    db.db.create_function("insdet2markers",1,insdet2markers)

    # Convert INSNAME into colors 'y','g' by polarisation
    def inspol2colors(insname):
        if 'P1' in insname:
            return 'y'
        elif 'P2' in insname:
            return 'g'
        else:
            return 'k'

    db.db.create_function("inspol2colors",1,inspol2colors)

####################################
# Macro to plot all data
####################################

#
# Plot all trending versus time
#
def plot_all_trending(db):
    global target2colors_dict, setup2markers_dict, SETUPSTRING
    ## set the default query
    Q = db.query()
    Q.add_headers()
    Q.add_targets(linkto="headers")
    Q.add_arrays("ar1") # others will comme later

    Q.add_conds("ar1.STA_NAME NOT LIKE 'S%'")# remove the base starting with 'S'

    ###
    # update some general scalars for plot here
    Q.scalars.update(colors_dict = target2colors_dict,
                     markers_dict = setup2markers_dict,
                     fcolors_dict = catg2black_dict,
                     alpha_dict = catg2alpha_dict,
                     # axes_dict = base2int_dict,
                     colors_on = "targets",
                     markers_on = "setups",
                     xlabel="MJD",
                     grid=True,
                     clear_figure=True,  # clear figure before plotting
                     axes_orientation="vertical",
                     adjusts={"hspace":0.0} )

    ## v2
    q = Q.query(
                x="MJD",
                # y="median(VIS2DATA, WImin, WImax)",
                # yerr = "median(VIS2ERR, WImin, WImax)",
                y = "VIS2DATA_MED",
                yerr = "VIS2ERR_MED",
                colors_key = "TARGET",
                markers_key = SETUPSTRING,
                alpha_key = "[ESO PRO CATG]",
                fcolors_key = "[ESO PRO CATG]",
                # axes_key = "ar1.STA_NAME||ar2.STA_NAME",
                tbls= "vis2"
            )

    q.link_arrays(["ar1", "ar2"], "vis2")
    q.linkall("vis2", "headers,targets")

    q.scalars.update(ylabel="$v^2$",
                     ylim=(-.1,1.4), # can be (-.1,None)
                     ylim_max=4,
                     ptype = "VIS2",
                     det_query =  "det(vis2.INSNAME)" #key to loop figure
                    )
    q_vis2 = q

    ## visamp
    q = Q.query(
                x="MJD",
                # y="median(VISAMP, WImin, WImax)",
                # yerr = "median(VISAMPERR, WImin, WImax)",
                y="VISAMP_MED",
                yerr = "VISAMPERR_MED",
                colors_key = "TARGET",
                markers_key = SETUPSTRING,
                alpha_key = "[ESO PRO CATG]",
                fcolors_key = "[ESO PRO CATG]",
                # axes_key = "ar1.STA_NAME||ar2.STA_NAME",
                tbls= "vis"
            )
    q.link_arrays(["ar1", "ar2"], "vis")
    q.linkall("vis", "headers,targets")

    q.scalars.update(ylabel="$Vis_{amp}$",
                     ylim=(-.1,1.4),
                     ylim_max=4,
                     ptype = "VISAMP",
                     det_query =  "det(vis.INSNAME)"
                    )
    q_visamp = q

    ## vis phi
    q = q_visamp.query(
                #y="median(VISPHI, WImin, WImax)",
                #yerr = "median(VISPHIERR, WImin, WImax)"
                y="VISPHI_MED",
                yerr = "VISPHIERR_MED"
            )
    q.scalars.update(ylabel="$vis_{phi}$",
                     ylim=None,
                     ptype = "VISPHI",
                     det_query =  "det(vis.INSNAME)"
                    )
    q_visphi = q

    ## t3
    q = Q.query(
                x="MJD",
                #y="median(T3PHI, WImin, WImax)",
                #yerr = "median(T3PHIERR, WImin, WImax)",
                y="T3PHI_MED",
                yerr = "T3PHIERR_MED",
                colors_key = "TARGET",
                markers_key = SETUPSTRING,
                alpha_key = "[ESO PRO CATG]",
                fcolors_key = "[ESO PRO CATG]",
                # axes_key = "ar1.STA_NAME||ar2.STA_NAME||ar3.STA_NAME",
                tbls= "t3"
            )
    q.link_arrays(["ar1", "ar2", "ar3"], "t3")
    q.linkall("t3", "headers,targets")
    q.add_conds("[ESO PRO CATG] not like '%CAL_VIS'") # redundant with TF_SCI

    q.scalars.update(ylabel="$T3_{PHI}$",
                     ylim=(-190,190),
                     ptype = "T3PHI",
                     det_query =  "det(t3.INSNAME)"
                    )
    q_t3phi = q

    ## flux
    q = Q.query(
                x="MJD",
                #y="median(FLUX, WImin, WImax)",
                #yerr = "median(FLUXERR, WImin, WImax)",
                y="FLUX_MED",
                yerr = "FLUXERR_MED",
                colors_key = "TARGET",
                markers_key = SETUPSTRING,
                # axes_key = "ar1.STA_NAME",
                tbls= "flux"
            )
    q.link_arrays(["ar1"], "flux")
    q.linkall("flux", "headers,targets")
    q.add_conds("[ESO PRO CATG] not like 'TF%'")

    q.scalars.update(ylabel="FLUX",
                     ylim=None,
                     ptype = "FLUX",
                     det_query =  "det(flux.INSNAME)"
                    )
    q_flux = q


    # Plot the observables
    # don't plot visPhi since useless at this point
    queries_cal = [q_vis2, q_visamp, q_t3phi, q_flux]
    for q in queries_cal:
        log.notice("ploting %s, color by target"%q.scalars.get("ptype","unknown"))
        plot_figure_by_det(q,
                      plot_colors_and_markers_legend,
                      plot_axes_by_base,
                      plot_scatter)


#
# Plot all correlations
#
def plot_all_correlations(db):
    Q = db.query()

    Q.add_headers()
    Q.add_targets(linkto="headers")
    Q.add_arrays("ar1") # others will comme later
    Q.add_conds("[ESO PRO CATG] like '%_VIS'")

    Q.scalars.update(adjusts=dict(wspace=0.3,hspace=0.3), ptype="correlations", clear_figure=True)
    plot_by_night(Q, plot_figure, plot_correlations)


# Plot all uv
def plot_all_uv(db):
    global target2colors_dict, setup2markers_dict
    Q = db.query()

    Q.add_headers()
    Q.add_targets(linkto="headers")
    Q.add_arrays("ar1")

    Q.add_conds("ar1.STA_NAME NOT LIKE 'S%'")# remove the base starting with 'S'
    Q.add_conds("[ESO PRO CATG] like '%_VIS'")

    ###
    # update some general scalars for plot here
    Q.scalars.update(xlabel="MJD",
                     colors_dict=target2colors_dict,
                     ptype = "uv",
                     clear_figure = True,
                     adjusts={"hspace":0.2},
                     grid=True, #put grid on eaxh axes,
                     marker='o'
                    )

    q = Q.query(
                x = "UCOORD",
                y = "VCOORD",
                colors_key="TARGET",
                conds= "(polar(INSNAME)=1 or polar(INSNAME)=0)"
               )
    q.add_vis(linkto="headers")
    q.link_arrays("ar1", "vis")# need to remove the S% bases
    q.scalars.update(xlabel="$U$", ylabel="$V$",
                     det_query = "det(INSNAME)",
                     axis="scaled"
                     #axes_orientation="vertical"
                     )

    plot_by_night(q, plot_figure, plot_colors_and_markers_legend, plot_axes_by_insname, plot_scatter)

#
# Plot all flux ratio
#
def plot_all_flux_ratio(db):
    Q = db.query()
    Q.add_headers()
    Q.add_targets(linkto="headers")
    Q.add_arrays("ar1") # others will comme later

    Q.add_conds("ar1.STA_NAME NOT LIKE 'S%'")# remove the base starting with 'S'
    Q.add_conds("[ESO PRO CATG] like '%_VIS'")

    Q.scalars.update(
                     ptype="flux_ratio",
                     clear_figure=True,
                     axes_orientation="vertical"
                     )

    q = Q.query(
                # x="sc.MJD",
                #flux_sc="sum(sc.FLUX, wsc.WImin, wsc.WImax)",
                #flux_ft="sum(ft.FLUX, wft.WImin, wft.WImax)",
                flux_sc="sum(sc.FLUX, Null, Null)",
                flux_ft="sum(ft.FLUX, Null, Null)",
                y = lambda d: (d['flux_sc']-d['flux_ft'])/d['flux_sc']*100,
                x = lambda d: d['flux_sc']
                )
    q.add_flux("sc", "ft", linkall="headers")
    #q.add_conds("det(sc.INSNAME) = 'SC' AND det(ft.INSNAME) = 'FT' AND polar(sc.INSNAME) = polar(ft.INSNAME)")
    q.add_conds("polar(sc.INSNAME) = polar(ft.INSNAME)")
    q.add_conds(["det(sc.INSNAME) = 'SC'",
                 "det(ft.INSNAME) = 'FT'"
                ]
                )
    q.add_wavelengths("wsc", "wft", linkto=["sc", "ft"])
    q.link_arrays("ar1", ["sc", "ft"])

    q.scalars.update(ylim=(-1000,1000),
                     ylabel="$Flux: (sc-ft)/sc [\%] $",
                     xlabel="Flux sc",
                     marker='+',
                     polar_query="polar(sc.INSNAME)"
                     )

    plot_by_night(q, plot_figure, plot_axes_by_base, plot_color_by_polar, plot_colect_fig_legend, plot_scatter)

#
# Plot all correlation
#
def plot_all_flux_correlations(db):
    Q = db.query()
    Q.add_headers()
    Q.add_targets(linkto="headers")
    Q.add_arrays("ar1") # others will comme later

    Q.add_conds("ar1.STA_NAME NOT LIKE 'S%'")# remove the base starting with 'S'
    Q.add_conds("[ESO PRO CATG] like '%_VIS'")
    Q.scalars.update(
                     ptype="flux_correletions",
                     clear_figure=True
                     #axes_orientation="vertical",
                     )

    q = Q.query(
                # x="sc.MJD",
                #flux_sc="sum(sc.FLUX, wsc.WImin, wsc.WImax)",
                #flux_ft="sum(ft.FLUX, wft.WImin, wft.WImax)",
                flux_sc="sum(sc.FLUX, Null, Null)",
                flux_ft="sum(ft.FLUX, Null, Null)",
                y = lambda d: d['flux_ft'],
                x = lambda d: d['flux_sc']
                )
    q.add_flux("sc", "ft", linkall="headers")
    #q.add_conds("det(sc.INSNAME) = 'SC' AND det(ft.INSNAME) = 'FT' AND polar(sc.INSNAME) = polar(ft.INSNAME)")
    q.add_conds("polar(sc.INSNAME) = polar(ft.INSNAME)")
    q.add_conds(["det(sc.INSNAME) = 'SC'",
                 "det(ft.INSNAME) = 'FT'"
                ]
                )
    q.add_wavelengths("wsc", "wft", linkto=["sc", "ft"])
    q.link_arrays("ar1", ["sc", "ft"])

    q.scalars.update(
                     ylabel="Flux FT",
                     xlabel="Flux SC",
                     marker='+',
                     polar_query="polar(sc.INSNAME)"
                     )

    plot_by_night(q, plot_figure, plot_axes_by_telescope, plot_color_by_polar, plot_colect_fig_legend, plot_scatter)

#
# Plot all targets
#
def plot_all_target(db):
    Q = db.query("TARGET","targets")
    Q.scalars.update(
                     ptype="target",
                     adjusts = {"wspace":0.25},
                     clear_figure = True
                     )
    Q.add_headers()
    Q.add_targets(linkto="headers")
    Q.add_conds("[ESO PRO CATG] like '%CALIBRATED'")
    plot_figure_by_target(Q, plot_target)


#######################################################################
# Add loops to dispatch the data into figures, axes, colors, markers...
#######################################################################

# Dispatch data in the 'correlation style', since this spetial
# dispatch of data into axes cannot be build from loops
def plot_correlations(db, *plots):
    global target2colors_dict, setup2markers_dict

    Q = db.query()

    Q.add_headers()
    Q.add_targets(linkto="headers")
    Q.add_arrays("ar1") # others will comme later

    Q.add_conds("ar1.STA_NAME NOT LIKE 'S%'")# remove the base starting with 'S'
    Q.add_conds("[ESO PRO CATG] like '%_VIS'")
    ###
    # update some general scalars for plot here
    Q.scalars.update(xlabel="MJD",
                     ptype = "correlations",
                     grid=True, #put grid on eaxh axes,
                     marker='o'
                    )


    ## visamp sc vs ft
    q = Q.query(
                # x = "median(sc.VISAMP, wsc.WImin,wsc.WImax)",
                # y = "median(ft.VISAMP, wft.WImin, wft.WImax)",
                # xerr = "median(sc.VISAMPERR, wsc.WImin, wsc.WImax)",
                # yerr = "median(ft.VISAMPERR, wft.WImin, wft.WImax)",
                x = "sc.VISAMP_MED",
                y = "ft.VISAMP_MED",
                xerr = "sc.VISAMPERR_MED",
                yerr = "ft.VISAMPERR_MED",

                conds = ["det(sc.INSNAME) = 'SC'", "det(ft.INSNAME) = 'FT'"]
                )
    q.add_vis("sc", "ft", linkall="headers")

    q.add_conds("polar(sc.INSNAME) = polar(ft.INSNAME)")#link polar together

    q.link_arrays(["ar1", "ar2"], ["sc", "ft"])
    q.link_wavelengths(["wsc", "wft"], ["sc", "ft"])
    q.scalars.update(xlabel="$VisAmp_{sc}$", ylabel="$VisAmp_{ft}$",
                     xlim=(-0.1,1.2), ylim=(-0.1,1.2),
                     polar_query = "polar(sc.INSNAME)"
                    )
    q_visamp_sc_ft = q

    ## visamp sc vs ft
    q = Q.query(
                #x = "median(sc.VIS2DATA, wsc.WImin,wsc.WImax)",
                #y = "median(ft.VIS2DATA, wft.WImin, wft.WImax)",
                #xerr = "median(sc.VIS2ERR, wsc.WImin, wsc.WImax)",
                #yerr = "median(ft.VIS2ERR, wft.WImin, wft.WImax)",
                x = "sc.VIS2DATA_MED",
                y = "ft.VIS2DATA_MED",
                xerr = "sc.VIS2ERR_MED",
                yerr = "ft.VIS2ERR_MED",
                conds = ["det(sc.INSNAME) = 'SC'", "det(ft.INSNAME) = 'FT'"]
                )
    q.add_vis2("sc", "ft", linkall="headers")
    q.add_conds("polar(sc.INSNAME) = polar(ft.INSNAME)")


    q.link_arrays(["ar1", "ar2"], ["sc", "ft"])
    q.link_wavelengths(["wsc", "wft"], ["sc", "ft"])
    q.scalars.update(xlabel="$Vis^2_{sc}$", ylabel="$Vis^2_{ft}$",
                     xlim=(-0.1,1.2), ylim=(-0.1,1.2),
                     polar_query = "polar(sc.INSNAME)"
                    )
    q_vis2_sc_ft = q

    ## visamp vs vis2
    q = Q.query(
                # x = "median(vis.VISAMP, w.WImin,w.WImax)",
                # y = "median(vis2.VIS2DATA, w2.WImin, w2.WImax)",
                # xerr = "median(vis.VISAMPERR, w.WImin, w.WImax)",
                # yerr = "median(vis2.VIS2ERR, w2.WImin, w2.WImax)"
                x = "vis.VISAMP_MED",
                y = "vis2.VIS2DATA_MED",
                xerr = "vis.VISAMPERR_MED",
                yerr = "vis2.VIS2ERR_MED"
               )
    q.add_vis2(linkall="headers")
    q.add_vis(linkall="headers")
    q.link("vis", "vis2", ["INSNAME"])
    q.link_arrays(["ar1", "ar2"], ["vis2", "vis"])
    q.add_wavelengths("w2", "w", linkto=["vis2", "vis"])
    q.scalars.update(xlabel="$VisAmp$", ylabel="$Vis^2$",
                     xlim=(-0.1,1.2), ylim=(-0.1,1.2),
                     det_query = "det(vis.INSNAME)"
                    )
    q_vis_vis2 = q

    ## vis2 p1 vs p2
    q = Q.query(
                # x = "median(p1.VIS2DATA, wp1.WImin, wp1.WImax)",
                # y = "median(p2.VIS2DATA, wp2.WImin, wp2.WImax)",
                # xerr = "median(p1.VIS2ERR, wp1.WImin, wp1.WImax)",
                # yerr = "median(p2.VIS2ERR, wp2.WImin, wp2.WImax)",
                x = "p1.VIS2DATA_MED",
                y = "p2.VIS2DATA_MED",
                xerr = "p1.VIS2ERR_MED",
                yerr = "p2.VIS2ERR_MED",
                conds = ["polar(p1.INSNAME) = 1", "polar(p2.INSNAME) = 2"]
                )
    q.add_vis2("p1", "p2", linkall="headers")
    q.add_conds("det(p1.INSNAME) = det(p2.INSNAME)")

    q.link_arrays(["ar1", "ar2"], ["p1", "p2"])
    q.link_wavelengths(["wp1", "wp2"], ["p1", "p2"])
    q.scalars.update(xlabel="$Vis^2_{p1}$", ylabel="$Vis^2_{p2}$",
                     xlim=(-0.1,1.2), ylim=(-0.1,1.2),
                     det_query = "det(p1.INSNAME)"
                    )
    q_vis2_p1_p2 = q

    ## visamp p1 vs p2
    q = Q.query(
                # x = "median(p1.VISAMP, wp1.WImin, wp1.WImax)",
                # y = "median(p2.VISAMP, wp2.WImin, wp2.WImax)",
                # xerr = "median(p1.VISAMPERR, wp1.WImin, wp1.WImax)",
                # yerr = "median(p2.VISAMPERR, wp2.WImin, wp2.WImax)",
                x = "p1.VISAMP_MED",
                y = "p2.VISAMP_MED",
                xerr = "p1.VISAMPERR_MED",
                yerr = "p2.VISAMPERR_MED",
                conds = ["polar(p1.INSNAME) = 1", "polar(p2.INSNAME) = 2"]
                )
    q.add_vis("p1", "p2", linkall="headers")
    q.add_conds("det(p1.INSNAME) = det(p2.INSNAME)")

    q.link_arrays(["ar1", "ar2"], ["p1", "p2"])
    q.link_wavelengths(["wp1", "wp2"], ["p1", "p2"])
    q.scalars.update(xlabel="$VisAmp_{p1}$", ylabel="$VisAmp_{p2}$",
                     xlim=(-0.1,1.2), ylim=(-0.1,1.2),
                     det_query = "det(p1.INSNAME)"
                    )
    q_visamp_p1_p2 = q


    q_visamp_sc_ft.scalars.update(axes=(3,2,1))
    plot_axes(q_visamp_sc_ft, *(plots+(plot_color_by_polar, plot_colect_fig_legend, plot_scatter)))

    q_vis2_sc_ft.scalars.update(axes=(3,2,2))
    plot_axes(q_vis2_sc_ft, *(plots+(plot_color_by_polar, plot_colect_fig_legend, plot_scatter)))

    q_vis_vis2.scalars.update(axes=(3,2,3))
    plot_axes(q_vis_vis2, *(plots+(plot_color_by_det,  plot_colect_fig_legend, plot_scatter)))

    q_vis2_p1_p2.scalars.update(axes=(3,2,4))
    plot_axes(q_vis2_p1_p2, *(plots+(plot_color_by_det,  plot_colect_fig_legend, plot_scatter)))

    q_visamp_p1_p2.scalars.update(axes=(3,2,5))
    plot_axes(q_visamp_p1_p2, *(plots+(plot_color_by_det,  plot_colect_fig_legend, plot_scatter)))

# Plot data in the 'target style', since this spetial
# dispatch of data into axes cannot be build from loops
def plot_target(db, *plots):

    Q = db.query()
    Q.add_targets(linkto="headers")

    # VIS2DATA versus baseline length
    q = Q.query(
                x = "baseline(UCOORD,VCOORD)",
                y = "VIS2DATA_MED",
                yerr= "VIS2ERR_MED",
                conds = "(polar(vis2.INSNAME) = 0 or polar(vis2.INSNAME) = 1)"
                )

    q.add_vis2(linkto="targets")
    q.add_wavelengths( linkto="vis2")
    q.scalars.update(det_query="det(vis2.INSNAME)",
                     xlabel = "$\sqrt{u^2+v^2}$",
                     ylabel = "$V^2$",
                     #xlim=(0,None),
                     ylim=(-0.2,1.4),
                     axes=(2,2,1)
                     )
    q_base = q

    # VIS2DATA versus baseline angle
    q = q_base.query(#this is a copy of q_base
                     x = "baselineangle(UCOORD,VCOORD)",
                     )
    q.scalars.update(xlabel = "$arctan(u/v) [degree]$",
                     xlim=None,
                     axes=(2,2,2)
                    )
    q_angle = q

    # U,V plane
    q = Q.query(
                x = "UCOORD",
                y = "VCOORD",
                conds = "(polar(vis.INSNAME) = 0 or polar(vis.INSNAME) = 1)"
                )

    q.add_vis(linkto="targets")
    q.scalars.update(det_query="det(vis.INSNAME)",
                     xlabel = "$u$",
                     ylabel = "$v$",
                     axes=(2,2,3),
                     axis="scaled"
                    )
    q_uv = q

    plots = plots+(plot_color_by_det, plot_scatter)
    plot_axes(q_base , *plots)
    plot_axes(q_angle, *plots)
    plot_axes(q_uv, *(plots+(plot_color_by_det, plot_colect_fig_legend, plot_scatter)))

# Add a loop to spread the NIGHT into different pages
def plot_by_night(query, *plots):
    if not plots: return None

    for qn in query.iter("NIGHT", night=first):
        if qn.scalars.get("colors_on")=="targets":
            # restrict the color dict TARGET for the ones
            # present in the night only
            colors_dict = qn.scalars.get("colors_dict", {})
            qt = qn.copy()
            qt.set_columns('TARGET')
            new_colors_dict = {r[0]:colors_dict.get(r[0],'b') for r in qt.execute(distinct=True).fetchall()}
            qn.scalars["colors_dict"] = new_colors_dict

        plots[0](qn, *plots[1:])

# Add a loop to colorize per detector SC/FT
def plot_color_by_det(query, *plots):
    det_query =  query.scalars.get("det_query")
    colors =  {"SC":"blue", "FT":"red"}
    markers = {"SC":"+",    "FT":  "o"}

    if det_query is None:
        it = (query,)
    else:
        it = query.iter(det_query,
                        det=first,
                        color=lambda  r:colors[r[0]],
                        marker=lambda r:markers[r[0]],
                        label=first
                       )
    for qc in it:
        if plots:
            plots[0](qc,*plots[1:])

# Add a loop separate the insname into different axes
def plot_axes_by_insname(query, *plots):
    fig = gpf(query)

    insname_query =  query.scalars.get("insname_query", "INSNAME")

    orientation = query.scalars.get("axes_orientation")
    if orientation=="vertical":
        axes_pos = lambda n,i,r: (n,1,i+1)
    elif orientation=="horizontal":
        axes_pos = lambda n,i,r: (1,n,i+1)
    else:
        def axes_pos(n,i,r):
            nx = int(np.sqrt(n))
            ny = int(np.ceil(n/float(nx)))
            return (nx,ny,i+1)

    if insname_query is None:
        it = (query,)
    else:
        it = query.niter(insname_query,
                         axes = axes_pos,
                         title   = lambda n,i,r:r[0],
                         insname = lambda n,i,r:r[0],
                        )
    for qa in it:
        axes = gpa(qa)
        qa.scalars.update(axes=axes)
        plot_axes(qa, *plots)


# Add a loop to colorize by polarisation
def plot_color_by_polar(query, *plots):
    polar_query =  query.scalars.get("polar_query")
    colors = {0:"red", 1:"blue", 2:"green"}

    if polar_query is None:
        it = (query,)
    else:
        it = query.iter(polar_query, polar=first,
                        color=lambda r:colors[r[0]])

    for qc in it:
        qc.scalars["label"] = qc.scalars.get("label", "")+" P%d"%(qc.scalars.get("polar",0))
        if plots:
            plots[0](qc,*plots[1:])

# Add a loop to make a figure for each target
def plot_figure_by_target(query,*plots):
    target_query = query.scalars.get("target_query", "TARGET")

    figures = []

    for qf in query.iter(target_query,
                         target=first,
                         suptitle=first,
                         fig=lambda r:plt.figure(r[0])
                         ):
        figures.append(plot_figure(qf, *plots))

    return figures


# Add a loop to make a figure for each detector SC/FT
def plot_figure_by_det(query, *plots):
    """ make a figure by number of detector name ('FT','SC') """

    det_query =  query.scalars.get("det_query")
    if det_query is None:
        it = (query,)
    else:
        it = query.iter(det_query, det=first)

    ptype =  query.scalars.get("ptype", "")
    figures = []
    for qf in it:
        #fig, ax = plt.subplots(Nbase, 1, sharex=True)
        fig = plt.figure();
        qf.scalars.update(fig=fig)
        plot_figure(qf, *plots)
        figures.append(fig)

    return figures

# Add a loop to separate the base into the axes
def plot_axes_by_base_vect(query, *plots):
    """ plot a triplet,base or telescope axes """

    fig = gpf(query)

    orientation = query.scalars.get("axes_orientation")
    if orientation=="vertical":
        axes_pos = lambda n,i,r: (n,1,i+1)
    elif orientation=="horizontal":
        axes_pos = lambda n,i,r: (1,n,i+1)
    else:
        def axes_pos(n,i,r):
            nx = int(np.sqrt(n))
            ny = int(np.ceil(n/float(nx)))
            return (nx,ny,i+1)

    if query.has_table("ar3"): #this is a triplet
        iterator =  query.niter("ar1.STA_NAME, ar2.STA_NAME, ar3.STA_NAME",
                                base=lambda n,i,r:"".join(r), axes=axes_pos)
    elif query.has_table("ar2"): # this is a base
        iterator =  query.niter("ar1.STA_NAME, ar2.STA_NAME",
                                base=lambda n,i,r:"".join(r), axes=axes_pos)
    elif query.has_table("ar1"):
        iterator =  query.niter("ar1.STA_NAME",
                                base=lambda n,i,r:"".join(r), axes=axes_pos)
    else:
        raise ValueError("query must have at least the ar1 table")


    all_axes = []
    first_axes = None

    # Prepare the axes
    for i,qa in enumerate(iterator, start=1):
        nx,ny,i = qa.scalars.get("axes", (1,1,1))
        if first_axes:
            axes = fig.add_subplot(nx,ny,i, sharex=first_axes)
        else:
            axes = fig.add_subplot(nx,ny,i)
            first_axes = axes
        axes.label_outer()

        axes.text(0.01, 0.95, qa.scalars["base"],
                  verticalalignment='top',
                  transform=axes.transAxes)

        all_axes.append(axes)

    # Plot in these axes
    plot_axes(query, *plots)

    return all_axes

# Add a loop to separate the base into the axes
def plot_axes_by_base(query, *plots):
    """ plot a triplet,base or telescope axes """

    fig = gpf(query)

    orientation = query.scalars.get("axes_orientation")
    if orientation=="vertical":
        axes_pos = lambda n,i,r: (n,1,i+1)
    elif orientation=="horizontal":
        axes_pos = lambda n,i,r: (1,n,i+1)
    else:
        def axes_pos(n,i,r):
            nx = int(np.sqrt(n))
            ny = int(np.ceil(n/float(nx)))
            return (nx,ny,i+1)

    if query.has_table("ar3"): #this is a triplet
        iterator =  query.niter("ar1.STA_NAME, ar2.STA_NAME, ar3.STA_NAME",
                                base=lambda n,i,r:"".join(r), axes=axes_pos)
    elif query.has_table("ar2"): # this is a base
        iterator =  query.niter("ar1.STA_NAME, ar2.STA_NAME",
                                base=lambda n,i,r:"".join(r), axes=axes_pos)
    elif query.has_table("ar1"):
        iterator =  query.niter("ar1.STA_NAME",
                                base=lambda n,i,r:"".join(r), axes=axes_pos)
    else:
        raise ValueError("query must have at least the ar1 table")


    all_axes = []
    first_axes = None


    for i,qa in enumerate(iterator, start=1):
        nx,ny,i = qa.scalars.get("axes", (1,1,1))
        if first_axes:
            axes = fig.add_subplot(nx,ny,i, sharex=first_axes)
        else:
            axes = fig.add_subplot(nx,ny,i)
            first_axes = axes
        axes.label_outer()
        qa.scalars.update(axes=axes)

        plot_axes(qa, *plots)

        axes.text(0.01, 0.95, qa.scalars["base"],
                  verticalalignment='top',
                  transform=axes.transAxes)

        all_axes.append(axes)
    return all_axes


# Add a loop to separate the telescope into the axes
def plot_axes_by_telescope(query, *plots):
    """ plot a triplet,base or telescope axes """

    fig = gpf(query)

    orientation = query.scalars.get("axes_orientation")
    if orientation=="vertical":
        axes_pos = lambda n,i,r: (n,1,i+1)
    elif orientation=="horizontal":
        axes_pos = lambda n,i,r: (1,n,i+1)
    else:
        def axes_pos(n,i,r):
            nx = int(np.sqrt(n))
            ny = int(np.ceil(n/float(nx)))
            return (nx,ny,i+1)

    if query.has_table("ar3"): #this is a triplet
        iterator =  query.niter("ar1.STA_NAME, ar2.STA_NAME, ar3.STA_NAME",
                                base=lambda n,i,r:"".join(r), axes=axes_pos)
    elif query.has_table("ar2"): # this is a base
        iterator =  query.niter("ar1.STA_NAME, ar2.STA_NAME",
                                base=lambda n,i,r:"".join(r), axes=axes_pos)
    elif query.has_table("ar1"):
        iterator =  query.niter("ar1.STA_NAME",
                                base=lambda n,i,r:"".join(r), axes=axes_pos)
    else:
        raise ValueError("query must have at least the ar1 table")


    all_axes = []
    first_axes = None


    for i,qa in enumerate(iterator, start=1):
        nx,ny,i = qa.scalars.get("axes", (1,1,1))

        axes = fig.add_subplot(nx,ny,i)

        qa.scalars.update(axes=axes)

        plot_axes(qa, *plots)

        axes.text(0.01, 0.95, qa.scalars["base"],
                  verticalalignment='top',
                  transform=axes.transAxes)

        all_axes.append(axes)
    return all_axes

# Add a loop to have a different marker for each setup
def plot_marker_by_setup(query, *plots):
#    axes = gpa(query)

    hasdata = False

    for qs in query.iter("[ESO INS SPEC RES],[ESO INS POLA MODE],[ESO INS TIM2 PERIOD],[ESO DET2 SEQ1 DIT]",
                         setup=lambda r:"_".join(str(v) for v in r),
                         label=lambda r:"_".join(str(v) for v in r),
                         markers_key=lambda r:r
                         ):
        if plots:
            plots[0](qs, *plots[1:])

#    if not qs.scalars.get("hasdata", True):
#        axes.text(0.5, 0.5, "NO DATA",
#                  verticalalignment='center',
#                  horizontalalignment='center',
#                  transform=axes.transAxes)

# Add a loop to colorize by PRO.CATG
def plot_colors_by_state(query, *plots):
#    axes = gpa(query)

    hasdata = False
    obj = []

    for qs in query.iter(["[ESO PRO CATG]"],
                         procatg=first):

        procatg = qs.scalars['procatg']

        if 'TF' in procatg:
            if 'SCI' in procatg:
                # TF SCI as alpha black, alpha
                # log.notice("Plot the TFSCI as alpha black, alpha")
                qs.scalars['colors'] = 'k'
                qs.scalars['alpha'] = 0.2
            else:
                # TF CAL as full black
                # log.notice("Plot the TFCAL as full black")
                qs.scalars['colors'] = 'k'
                qs.scalars['alpha'] = 1
                pass

            qs.scalars.pop('label',None)

        elif 'CALIBRATED' in procatg:
            # do not plot the calibrated
            # log.notice("Skipp the CALIBRATED")
            continue

        else:
            if 'CAL_VIS' in procatg:
                # *_CAL_VIS as alpha colored
                # log.notice("Plot the *_CAL_VIS with alpha")
                qs.scalars['alpha'] = 0.1
            else:
                pass
                # *_SCI_VIS as full colored
                # log.notice("Plot the *_SCI_VIS as normal")

        if plots:
            plots[0](qs, *plots[1:])


###################################################
# Macro to conclude the figures and plot the data
###################################################

# Setup and save the figure
def plot_figure(query, *plots):
    if _debug_:
        if plots: plots[0](query, *plots[1:])
        return

    fig = gpf(query)
    sc = query.scalars

    if sc.get("clear_figure"):
        fig.clear()
        try:
            del fig.my_legends
        except:
            pass

    fig.set_size_inches(8.267,11.692) # a4 paper

    name_elements = gps(query, "ptype", "det",
                        "polar", "target", "night")
    name = "_".join(s.strip() for s in name_elements if s)

    (suptitle, save,
     adjusts, timestamp,
     timestamp_pos, close_figure) = gps(query,"suptitle", "save", "adjusts",
                                "timestamp", "timestamp_pos", "close_figure"
                          )
    if suptitle is None:
        suptitle = name
    if suptitle:
        fig.suptitle(suptitle)

    if plots:
        plots[0](query, *plots[1:])

    if hasattr(fig, "my_legends"):
        kw = query.scalars.get("fig_legend_params", {})
        loc = kw.pop("loc", 'lower right')
        fig.legend(fig.my_legends[0],fig.my_legends[1], loc=loc, **kw)

    if adjusts:
        fig.subplots_adjust(**adjusts)

    if timestamp:
        text = datetime.strftime(datetime.now(), "%Y-%m-%dT%H:%M:%S")
        x, y = timestamp_pos or (0.0,0.0)
        fig.text( x, y, text, size="x-small")

    if save:
            (fname, directory, ext,
             orientation,
             papertype) = gps(query,"file_name", "directory", "file_ext",
                              "orientation", "papertype",
                              file_name = None
                            )
            if not fname:
                fname = name
            if not fname:
                fname = "tmp_fig"

            fig.savefig("%s/%s.%s"%(directory,fname,ext),
                        orientation=orientation,
                        #papertype=papertype
                        )
            log.notice("Figure '%s/%s.%s' saved "%(directory,fname,ext))
    if close_figure:
        plt.close(fig)


# Collect the legend
def plot_colect_fig_legend(query, *plots):
    if _debug_:
        if plots: plots[0](query,*plots[1:])
        return

    marker, color,label, linestyle = gps(query,"marker", "color","label",
                                        "linestyle",
                                        color="k", linestyle="none")

    if (marker is None) or (label is None):
        if plots:
            log.notice("plot_colect_fig_legend has nothing to plot")
            return plots[0](query,*plots[1:])
        return

    fig = gpf(query)
    line = plt.Line2D([], [], color=color, marker=marker,
                      markersize=12, label=label, linestyle=linestyle)
    my_legends = getattr(fig,"my_legends", [[],[]])
    if label not in my_legends[1]:
        my_legends[0].append(line)
        my_legends[1].append(label)
    setattr(fig,"my_legends", my_legends)
    if plots:
        plots[0](query,*plots[1:])

# Add the color and markers legend
def plot_colors_and_markers_legend(query, *plots):
    fig = gpf(query)

    dic = query.scalars.get("colors_dict", {})
    lines = []
    labels = []
    for k,v in list(dic.items()):
        if k not in labels:
            lines.append(plt.Line2D([], [], color=v, marker='o',
                          markersize=4, label=k, linestyle='none'))
            labels.append(k[:12]) # truncate the name

    if lines:
        fig.legend( lines, labels, bbox_to_anchor=(0.01,0.92),
                   prop ={"size":6},
                   numpoints=1,
                   ncol = 8,
                   mode="extand"
                   )

    dic = query.scalars.get("markers_dict", {})
    lines = []
    labels = []
    for k,v in list(dic.items()):
        if k not in labels:
            lines.append(plt.Line2D([], [], color='k', marker=v,
                          markersize=4, label=k, linestyle='none'))
            labels.append(k)

    if lines:
        fig.legend( lines, labels, bbox_to_anchor=(0.01,0.01),
                   prop ={"size":6},
                   numpoints=1,
                   ncol = 5,
                   mode="extand"
                   )

    if plots:
        plots[0](query, *plots[1:])


# Plot data into a defined axes
def plot_axes(query, *plots):
    if _debug_:
        if plots: plots[0](query, *plots[1:])
        return
    axes = gpa(query)
    (xlabel, ylabel, title,
     xlim, ylim,ylim_max, ylim_min,
     grid, axis,
     xlog,ylog) = gps(query,"xlabel", "ylabel", "title",
                      "xlim","ylim","ylim_max", "ylim_min", "grid", "axis", "xlog", "ylog")


    if xlabel:
        axes.set_xlabel(xlabel)
    if ylabel:
        axes.set_ylabel(ylabel)


    if grid:
        axes.grid()
    if title:
        axes.set_title(title)

    if plots:
        plots[0](query,*plots[1:])


    if ylim_max is not None:
        ylim1, ylim2 = axes.get_ylim()
        if ylim2>ylim_max:
            axes.set_ylim( ylim1,ylim_max)
    if ylim_min is not None:
        ylim1, ylim2 = axes.get_ylim()
        if ylim1>ylim_min:
            axes.set_ylim(ylim_min,ylim2)

    if ylim is not None:
        bottom, top = ylim
        axes.set_ylim(bottom=bottom,top=top)
    if xlim is not None:
        left, right = xlim
        axes.set_xlim(left=left, right=right)


    if xlog:
        try:
            axes.set_xscale('log')
        except ValueError:
            pass
    if ylog:
        try:
            axes.set_yscale('log')
        except ValueError:
            pass
    if axis:
        axes.axis(axis)

    return axes

# Plot a data as scatter
def plot_scatter(query, *plots):
    #if _debug_:
    #    if plots:
    #        plots[0](query, *plots[1:])
    #    print("skiped")
    #    return

    # print(str(query))

    query.execute()
    data = query.fetchdata()

    if not len(data):
        return
    query.scalars["hasdata"] = True
    scatter(data, **query.scalars)

    if plots:
        plots[0](query, *plots[1:])

# Get the scalars info and options
def get_plot_scalars(kwargs, *names, **defaults):
    global plot_defaults
    if isinstance(kwargs, query):
        kwargs = kwargs.scalars

    output = []
    for name in names:
        if name in kwargs:
            output.append(kwargs.get(name))
            continue

        if name+"_key" in kwargs:
            if not name+"_dict" in kwargs:
                raise TypeError("found keyword '%s' bot no '%s"%( name+"_key", name+"_dict"))
            d = kwargs.get(name+"_dict")
            k = kwargs.get(name+"_key")
            output.append(d.get(k, defaults.get(name)))
            continue
        output.append(defaults.get(name, plot_defaults.get(name)))
    return output
gps = get_plot_scalars

# Get the data
def get_plot_data(data, kwargs, *names, **defaults):
    if isinstance(kwargs, query):
        kwargs = kwargs.scalars
    output = []

    for name in names:
        # extract this data from the input dictionary
        if name in data:
            output.append(np.array(data.get(name)))
            continue

        # if this data is associated to a convertion dictionary
        if name+"_key" in data:
            if not name+"_dict" in kwargs:
                raise TypeError("found keyword '%s' bot no '%s"%( name+"_key", name+"_dict"))
            d = kwargs.get(name+"_dict")
            keys = data.get(name+"_key")
            output.append( np.array([d[k] for k in keys]) )
            continue

        # if this data is not in the input, use default, which shall be scalar
        output.append(defaults.get(name))

    return output

gpd = get_plot_data

# Plot data as scatter
def scatter(data, **kwargs):
    if _debug_:
        print(("debug", "scatters with %d points ..."%(len(data['y']) if 'y' in data else 0)))

    # axes = gpa(kwargs)
    # if axes is None:
    #    log.notice("Get current axes")
    #    axes = plt.gca()  #get curent axes

    # Get scalar parameters
    color, marker, colors, alpha = get_plot_scalars(kwargs, "color", "marker", "colors",
                                                    "alpha", color="k", marker="+",
                                                    alpha=1.0)

    # Get parameters passed in data (arrays)
    x,y, xerr, yerr, dcolors, dmarkers, dalpha, fcolors = get_plot_data(data, kwargs,
                                                   "x", "y", "xerr", "yerr", "colors",
                                                   "markers", "alpha", "fcolors",
                                                   markers=marker, colors=color,
                                                   alpha=alpha, fcolors='')

    # Give priority to scalar or data
    colors = colors if colors is not None else dcolors
    alpha = dalpha if dalpha is not None else alpha
    markers = dmarkers

    # Vectorialise quantities
    n = len(data['y'])
    colors = colors if len(np.array(colors,ndmin=1)) > 1 or n<2 else np.array( [colors] * n )
    markers = markers if len(np.array(markers,ndmin=1)) > 1 or n<2 else np.array( [markers] * n )
    alpha = alpha if len(np.array(alpha,ndmin=1)) > 1 or n<2 else np.array( [alpha] * n )

    # Loop to force the colors to fcolors if fcolors == 'k'
    # This is a trick to allow color from different conditions
    if isinstance(fcolors, str):
        if fcolors != '':
            fcolors = fcolors if len(np.array(fcolors,ndmin=1)) > 1 or n<2 else np.array( [fcolors] * n )
            for i,f in enumerate(fcolors):
                if (f == 'k'):
                    colors[i] = 'k'
    else:
        if fcolors.any() != '':
            fcolors = fcolors if len(np.array(fcolors,ndmin=1)) > 1 or n<2 else np.array( [fcolors] * n )
            for i,f in enumerate(fcolors):
                if (f == 'k'):
                    colors[i] = 'k'

    # Get scalar axes
    axes, fig = gps(kwargs,"axes", "fig")
    axes = get_axes(axes, fig)
    if axes is None:
       log.notice("Get current axes")
       axes = plt.gca()  #get curent axes

    # Get list of axes
    daxes, = gpd(data, kwargs, "axes", axes=None)
    axes = daxes if daxes is not None else np.array( [axes] * n )

    # Loop on axes
    for ax in set(axes):
        axe = get_axes(ax,fig)

        # Scatter: we need to loop on each requested marker and alpha
        for m in set(markers):
            for a in set(alpha):
                ids = (markers == m) * (alpha == a) * (axes == ax)
                axe.scatter(x[ids], y[ids], c=colors[ids], marker=m, zorder=100, alpha=a,
                            edgecolors='face')

        # Errorbars: we put them on fixed gray, alpha to not have to loop
        if xerr is not None or yerr is not None:
            ids = (axes == ax)
            try:
                i,j,k = axe.errorbar(x[ids], y[ids],
                                    xerr=xerr[ids] if xerr is not None else None,
                                    yerr=np.abs(yerr[ids]) if yerr is not None else None,
                                    color="gray", fmt='.', markersize=0, linestyle='none', alpha=0.3,
                                    ecolor="gray")
                if yerr[ids] is not None and (yerr[ids] < 0).any():
                    log.warning('Negative error bars found')
                    tag_neg = yerr[ids]<0
                    axe.errorbar(x[ids][tag_neg], y[ids][tag_neg],
                                        xerr=xerr[ids][tag_neg] if xerr is not None else None,
                                        yerr=np.abs(yerr[ids][tag_neg]) if yerr is not None else None,
                                        color="gray", fmt='.', markersize=0, linestyle='none', alpha=0.3,
                                        ecolor="red")
            except TypeError:
                pass
            # log.warning("errorbar failed... maybe NaN")
#            else:
#                k[0].set_alpha(0.3)
#                j[0].set_alpha(0.3)
#                j[1].set_alpha(0.3)

    if _debug_:
        print(("debug", "...done"))
    return axes

####################################

def make_dictionary(db, values, query, row2key=lambda r:r[0]):
    """ values can be an iterable or a function with signature (i,N,r)
    where i is the row index 0,1,2,3 ,....,N ;  N the number of row and
    r is the value of that row
    """
    c = db.execute(query)
    if hasattr(values, "__call__"):
        func = values
        rows = [(row2key(r),r) for r in c]
        Nkeys = len(rows)
        return {k:func(i,Nkeys,r) for i,(k,r) in enumerate(rows)}
    else:
        N = len(values)
        return {row2key(r):values[i%N] for i,r in enumerate(c)}

###############################################
def get_plot_axes(kwargs):
    """ return the axes of a query or dictionary """
    axes, fig = gps(kwargs,"axes", "fig")
    return get_axes(axes, fig)
gpa = get_plot_axes

def get_plot_fig(kwargs):
    """ return the figure of a query or dictionary """
    axes, fig = gps(kwargs,"axes", "fig")
    return get_figure( fig, axes)
gpf = get_plot_fig


plot_axes_classes = []
def get_axes(axes, fig=None):
    """
     axesdef can be:
        None   : curent axes is used
        string : look for axes with that name raise ValueError is not found
        (ncol,nrow,i) : axes ith in a ncol by nrow grid (see matplotlib.figure.add_subplot)
        (N,i)         : ncol, nrow are created so ncol*nrow<N and the grid is
                        as square as possible. e.i N=10 is a 4x3 grid
        matplotlib.Axes object : use that axes
        axes instance : use axes defined there
        int : look for the i'th axes in the plot raise ValueError if not found

    """
    if axes is None:
        figure = get_figure(fig)
        return figure.gca()

    if isinstance(axes, tuple(plot_axes_classes)):
        return get_axes(axes.get("axes",None), axes.get("figure",fig))


    if isinstance(axes, str):
        name = axes
        figure = get_figure(fig)
        for ax in (figure.axes or []):
            if getattr(ax, "id", None) == name:
                axes = ax
                break
        else:
            # raise an error if not found
            # however user as still possibility to send
            # a tuple of (name, axes) see below
            raise ValueError("Cannot find axes of name '%s' in this figure"%name)
        return axes

    if isinstance(axes, (tuple,list)):
        figure = get_figure(fig)
        coord = axes

        if len(coord)==2:
            if isinstance(coord[1], str):
                # if string the second is a figure
                axes, fig = coord
                # "3" must be 3
                try:
                    intfig = int(fig)
                except:
                    pass
                else:
                    fig = intfig
                return get_axes(axes, fig)

            if isinstance(coord[0], str):
                # this is a (name, axes) tuple
                # try to find the name if not found
                # find the axes and rename it
                name, axes = coord
                try:
                    axes = get_axes(name, fig)
                except TypeError:
                    axes = get_axes(axes, fig)
                    axes.id = name
                finally:
                    return axes
            if isinstance(coord[0], int):
                #########
                # If coord is a (N,i) tuple, build the (nx,ny,i) tuple automaticaly
                #########
                nx = int(plt.np.sqrt(coord[0]))
                ny = int(plt.np.ceil(coord[0]/float(nx)))
                coord = (nx,ny,coord[1])
            else:
                raise TypeError("invalid 2 tuple axes '%s'"%coord)
        elif len(coord)==1:
            #this is explicitaly a number (probably superior to 100)
            axes, = coord
            if not isinstance(axes,int):
                return get_axes(axes, fig)

            figure = get_figure(fig)
            num = axes
            ##
            # conserve the supid 1 has the 1st axes e.g. 0th
            # to avoide confusion
            num -= 1

            if num>=len(figure.axes):
                raise ValueError("cannot find the %dth axes, figure has only %d axes"%(num+1,len(figure.axes)))
            return figure.axes[num]


        if len(coord)!=3:
            raise TypeError("coord tuple must be of len 2 or 3 got %d"%len(coord))

        axes = figure.add_subplot(*coord)
        return axes

    if isinstance(axes, int):
        figure = get_figure(fig)
        num = axes
        ##
        # it is prety much unlikely that more than 100 axes is intanded
        # to be plot. so better to put the short cut 121 as (1,2,1)
        # user can still force axes number with (456,) which is the axes
        # number #456
        if num>100:
            if num>9981:
                raise ValueError("int acceed 9981 use (num,) to force to the num'th axes")
            coord = tuple(int(n) for n in str(num))
            return get_axes(coord, fig, params)
        ##
        # conserve the supid 1 has the 1st axes e.g. 0th
        # to avoide confusion
        num -= 1
        if num>=len(figure.axes):
            raise ValueError("cannot find the %dth axes, figure has only %d axes"%(num+1,len(figure.axes)))
        axes = figure.axes[num]

        return axes

    if isinstance(axes,(plt.Axes, plt.Subplot)):
        return axes

    raise TypeError("axes keyword must be a tuple, integer, None, string or Axes instance got a '%s'"%(type(axes)))


def get_figure(fig, axes=None):
    if isinstance(fig,plt.Figure):
        return fig
    if fig is None:
        if axes is None:
            return plt.gcf()
        return get_axes(axes).figure

    if isinstance(fig, (tuple,list)):
        tpl = fig
        if len(tpl)!=2:
            TypeError("Expecting a 2 tuple for figure got '%s'"%(tpl,))

        if isinstance(tpl[0], str):
            name, fig = tpl
            fig = plt.figure(name)
        else:
            fig = get_figure(None, axes)

        if len(tpl)!=2:
            TypeError("Expecting a 2 tuple for figure got '%s'"%(tpl,))

        nrows, ncols = tpl
        gs = GridSpec(nrows, ncols)
        for i in range(nrows*ncols):
            fig.add_subplot(gs[i // ncols, i % ncols])
        return fig

    return plt.figure(fig)



def make_html(glb=None, directory=None):
    directory = directory or plot_defaults.get("directory", ".")
    if glb is None:
        glb = "*."+plot_defaults.get("file_ext", "png")
    files = glob.glob(directory+"/"+glb)
    g = open("%s/index.html"%directory, "w")
    random.seed()
    #links = [fig_html_template.format(file_path=os.path.split(f)[1], fig_name=os.path.splitext(os.path.split(f)[1])[0], rand=random.randint(10000,100000)) for f in files]
    links = [fig_html_template.format(num=i,
                                      fig_name=os.path.splitext(os.path.split(f)[1])[0],
                                      rand=random.randint(10000,100000)) for i,f in enumerate(files)
            ]
    data = ["'%s?%d'"%(os.path.split(f)[1],random.randint(10000,100000)) for f in files]
    data_list = ",\n".join(data)
    figures_links = "\n".join(links)
    timestamp = datetime.strftime(datetime.now(), "%Y-%m-%dT%H:%M:%S")
    g.write(index_html_template.format(figures_links=figures_links,
            timestamp=timestamp, data_list=data_list,
            first=(os.path.split(files[0])[1] if files else ""),
            rand=random.randint(10000,100000)))
    g.close()
    log.notice("%s/index.html updated"%directory)





index_html_template = """
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html>

    <head>
        <title>Gravi Trend</title>

    <script type="text/javascript">
     data = [{data_list}]
     curent = 0
     curent_size = 600
     function next(){{
        if (curent<(data.length-1)) {{
            curent++
            shownum(curent)
        }}
      }}
      function prev(){{
        if (curent>0) {{
            curent--
            shownum(curent)
        }}
       }}
       function show(l) {{
           figure.src = l
       }}
       function bigger() {{
           curent_size *= 2
           resize(curent_size)
       }}
       function smaller() {{
           curent_size /= 2
           resize(curent_size)
       }}
       function resize(size) {{
           figure.height =  size
       }}
       function shownum(n) {{
           if (n>=0 & n<data.length) {{
               curent = n
               show(data[n])
               els = document.getElementsByClassName('figlink')
               for (i=0; i<els.length; i++) {{
                 el = els[i]
                 if (el.getAttribute("id") == 'l'+n) {{
                     el.style.color = '#aa0'
                 }} else {{
                     el.style.color = '#aaa'
                 }}

               }}
           }}
       }}
       function code(e) {{
            e = e || window.event;
         return(e.keyCode || e.which);
        }}
        function mykeydown(e) {{
          var key = code(e);
          if (key==37 || key==38) {{
              prev();
          }};
          if (key==39 || key==40) {{
              next();
          }};
        }}
        document.addEventListener("keydown", mykeydown, false);
    window.onload = function (){{shownum(0);}}
    </script>

    <style type="text/css">
    body {{
        background: #222;
        margin: 0;
        color:#aaa;
    }}
    .links td {{
        width: 120px;
        border: 4px solid #555;
        padding: 1px;
        margin: 10px 10px 10px 10px;
        color:#aaa;
    }}
    .figlink {{
        color:#aaa;
        margin-top:  15px;
        padding-top: 15px;
    }}
    .sizelink {{
        color:#aaa;
        margin-top:  15px;
        padding-top: 15px;
    }}
    .fl {{
        margin-top:  5px;
        #padding-top: 15px;

    }}

    .thumbnails img:hover {{
        border: 4px solid #00ccff;
        cursor:pointer;
    }}
    .container td {{
        width: 802px;
    }}
    .container div {{
        padding: 1px;
        width: 800px;
    }}

    .figure img {{
        border: 4px solid #444;
        padding: 1px;
        height: 600px;
    }}
    div.sizes {{
        margin-left: 10px;
    }}
    div.timestamp {{
        margin-left: 10px;
        color: #494;
        font-size:12px;
    }}
</style>
    </head>
    <body>
<div class='timestamp'>Last update: {timestamp}</div>
<div class='sizes'>
<a onclick="bigger()" class='sizelink'>bigger</a> /
<a onclick="smaller()" class='sizelink'>smaller</a>

</div>
<div class="gallery" align="center">
<table><tr><td></td><td></td>
<div style='width:400px'>
<span style='float:left'><a onclick='prev()'>Prev</a></span>
<span style='float:right'><a onclick='next()'>Next</a></span>
</div>
</td>
</tr>
<tr><td class='links'><div class="thumbnails">
    {figures_links}
</div>
</td><td class="container">
 <div class="container" align="center">
    <img height=600 name="figure" class="figure" src="{first}?r={rand}" alt="click on image"/>
</div>
</tr>
</table>

</div>
</body>
</html>
"""
#fig_html_template ="""<li class='fl'><a class='figlink' onclick="figure.src='{file_path}?r={rand}'">{fig_name}</a></li>"""
fig_html_template ="""<li class='fl'><a class='figlink' onclick="shownum({num})" id="l{num}">{fig_name}</a></li>"""
