import pandas as pd
import MySQLdb as mdb
import pandas.io.sql as psql
import calendar
from datetime import datetime, timedelta
from matplotlib import dates


def get_rx_data(rxID, tstart, tstop):
    '''
    Query the GEONET database for data from a receiver in a certain time span.
    INPUTS:
        rxID - integer, receiver ID
        tstart - datetime, start of time window
        tstop  - datetime, end of time window
    OUTPUTS:
        frame - an object containing the desired GPS data:
                rxID, time [POSIX], latitude [deg], longitude [deg], and altitude [m]
    '''
    
    # Open a connection to the database
    con = mdb.connect(host='airglow.csl.illinois.edu',user='bduser',passwd='bdpass',db='gpsdatabase')
    
    # This is the UT POSIX time for the start/stop times of interest
    startUT = calendar.timegm(tstart.timetuple())
    stopUT =  calendar.timegm(tstop.timetuple())
    
    # First find out if the entry is in there (i.e., we are just updating the png and avi file)
    sql_cmd = 'SELECT rxID, UT, lat, lon, alt ' + \
              'FROM rxposition WHERE rxID = %s AND UT >= %d AND UT <= %d' % (rxID, startUT, stopUT)
    frame = psql.frame_query(sql_cmd, con=con)
    con.close()
    
    return frame

def get_geonet_data(tstart, tstop):
    '''
    Query the GEONET database for data for all receivers in a certain time span.
    INPUTS:
        tstart - datetime, start of time window
        tstop  - datetime, end of time window
    OUTPUTS:
        frame - an object containing the desired GPS data:
                rxID, time [POSIX], latitude [deg], longitude [deg], and altitude [m]
    '''
    # Open a connection to the database
    con = mdb.connect(host='airglow.csl.illinois.edu',user='bduser',passwd='bdpass',db='gpsdatabase')
    
    # This is the UT POSIX time for the start/stop times of interest
    startUT = calendar.timegm(tstart.timetuple())
    stopUT =  calendar.timegm(tstop.timetuple())
    
    # First find out if the entry is in there (i.e., we are just updating the png and avi file)
    sql_cmd = 'SELECT rxID, UT, lat, lon, alt FROM rxposition WHERE UT >= %d AND UT <= %d' \
              % (startUT, stopUT)
    frame = psql.frame_query(sql_cmd, con=con)
    con.close()
    
    return frame

def get_geonet_data_at_time(t):
    '''
    Query the GEONET database for data for all receivers at a certain time.
    INPUTS:
        t - datetime, desired sample time
    OUTPUTS:
        frame - an object containing the desired GPS data:
                rxID, time [POSIX], latitude [deg], longitude [deg], and altitude [m]
    '''
    # Open a connection to the database
    con = mdb.connect(host='airglow.csl.illinois.edu',user='bduser',passwd='bdpass',db='gpsdatabase')
    
    # This is the UT POSIX time for the time of interest
    UT = calendar.timegm(t.timetuple())
    
    # First find out if the entry is in there (i.e., we are just updating the png and avi file)
    sql_cmd = 'SELECT rxID, UT, lat, lon, alt FROM rxposition WHERE UT = %d' \
              % (UT)
    frame = psql.frame_query(sql_cmd, con=con)
    con.close()
    
    return frame