#!/usr/bin/env python
# ------------------------------------------------------------------------------------
# Name: lhatpro.py
# Version: 2020-09-17 !NOW WITH A CONE SEARCH, AND POINTING TO THE OPERATIONAL SERVER!
# Author: A.Micol, Archive Science Group, ESO
# Purpose: Python 3 example on how to query the asm.lhatpros_paranal_profiles
# Dependency: pyvo must be installed, see: https://pypi.org/project/pyvo/
# -------
# Executing the script you might get a WARNING from astropy (depending on how old your astropy is):
# WARNING: W35: None:6:6: W35: 'value' attribute required for INFO elements [astropy.io.votable.tree]
# That is due to a astropy bug, that does not affect the execution of the script, and which should be
# solved soon, see: https://github.com/astropy/astropy/issues/9646
# ------------------------------------------------------------------------------------
import os
import sys
import math
import pyvo
from pyvo.dal import tap

# --- instantiate the ESO TAP service for ambient, raw, and processed data:

ESO_TAP_OBS = "http://archive.eso.org/tap_obs"
tapobs = tap.TAPService(ESO_TAP_OBS)

# --- prepare a TAP query to search the lhatpro profiles within a time range and a (ra, dec) box:
print()
print("Looking for LHATPRO profiles data within a certain timespan, in a box around a certain (ra,dec).")
print("Querying the ESO TAP service at %s" %(ESO_TAP_OBS))

# -- cone search:
ra = 332.2
dec = -25.5
radius = 2.5

# -- midpoint around:
date = '2019-06-06T09:55:27' 
within = 45 * 60 # within plus or minus 45 minutes, expressed in seconds

# The ADQL cone search query around those coordinates,
# and with a midpoint between + or - 45 minutes (i.e. 2700 seconds) from the defined date
# is: :
# SELECT start_date, midpoint_date, ra, dec, alt, az, *
# FROM asm.lhatpros_paranal_profiles
# WHERE midpoint_date between eso_dateadd_sec(-2700, '2019-06-06T09:55:27') and eso_dateadd_sec(2700, '2019-06-06T09:55:27')
#   AND CONTAINS(POINT('',ra, dec), CIRCLE('', 332.200000, -25.500000, 2.500000))=1
#   AND valid=1

# That query can be built from the given date, ra, dec, and radius, knowing that:
#    - the eso_dateadd_sec(arg1, arg2) adds a number of seconds (arg1) to the provided date (arg2)
#    - the cone search is obtained by the query constraint:
#          CONTAINS(contained, container)=1
#      where contained is the point of interest (POINT('',ra,dec)
#      and where the container is a circle: CIRCLE('', ra, dec, radius)

query="""SELECT start_date, midpoint_date, ra, dec, alt, az, *
FROM asm.lhatpros_paranal_profiles
WHERE midpoint_date between eso_dateadd_sec(-%f, '%s') and eso_dateadd_sec(%f, '%s')
  AND CONTAINS(POINT('',ra, dec), CIRCLE('', %f, %f, %f))=1
  AND valid=1""" % (within,date, within,date, ra, dec, radius)

print("")
print(query)
print("")

results = tapobs.search(query=query)

# few examples on how to access the astropy table:
print("results.to_table() shows the table of results:")
print(results.to_table())
print("")

print("results['ahum38'] is an array containing the ahum38 measurements:")
print(results["ahum38"])
print("")

print("results['ahum38'][0] is the first value in the column ahum38:")
print(results["ahum38"][0])