#!/usr/bin/env python
"""
    Run various queries against the CIPRES production database.  
    Unless otherwise noted, results are written to stdout in csv format.
    Usage:
        usage.py -flag
    where flag is one of:
        -a  Joins job_stats and tgusage tables to print tgusage and user info for all jobs in the db
        -u  Joins job_stats and tgusage tables and prints SUs per user, per month.  
            Sorted by year and month, then user.
"""
import sys
import os
import re
import string
import subprocess
import tempfile
import getopt
import pymysql
from pymysql.err import IntegrityError, Error
import csv
import mailer

passwordFile = os.path.join(os.path.expanduser("~"), "scripts", "db_password.txt")

def getColumnNames(cur):
    return [ t[0] for t in cur.description ]

def asCSV(cur):
    header = ",".join(getColumnNames(cur))
    sys.stdout.write("%s\n" %  header)
    for row in cur:
        line = ",".join([str(field) for field in row])
        sys.stdout.write("%s\n"% line)

def allUsage():
    query = """
        select
        job_stats.statistic_id,
        job_stats.t_tool_id,
        job_stats.t_jobhandle,
        job_stats.t_username,
        job_stats.t_email,
        tgusage.*
        from job_stats, tgusage where
        job_stats.resource = tgusage.resource and
        job_stats.remote_job_id = tgusage.jobid and
        abs(datediff(job_stats.job_start_time, tgusage.submit_time)) < 2
        order by (tgusage.submit_time)
        """
    # DictCursor isn't implemented in this version of pymysql
    # cur = conn.cursor(MySQLdb.cursors.DictCursor)
    cur = conn.cursor()
    cur.execute(query)
    asCSV(cur)
    cur.close()

def suPerUserTotal():
    query = """
        select
        job_stats.t_username,
        job_stats.t_email,
        sum(tgusage.su)
        from job_stats, tgusage where
        job_stats.resource = tgusage.resource and
        job_stats.remote_job_id = tgusage.jobid and
        abs(datediff(job_stats.job_start_time, tgusage.submit_time)) < 2
        group by job_stats.t_username, job_stats.t_email
    """
    cur = conn.cursor()
    cur.execute(query)
    asCSV(cur)
    cur.close()

def suPerUser():
    query = """
        select
        year(tgusage.end_time),
        monthname(tgusage.end_time),
        job_stats.t_username,
        job_stats.t_email,
        sum(tgusage.su)
        from job_stats, tgusage where
        job_stats.resource = tgusage.resource and
        job_stats.remote_job_id = tgusage.jobid and
        abs(datediff(job_stats.job_start_time, tgusage.submit_time)) < 2
        group by year(tgusage.end_time), month(tgusage.end_time), job_stats.t_username, job_stats.t_email
        """
    cur = conn.cursor()
    cur.execute(query)
    asCSV(cur)
    cur.close()

def overLimitWarning():
    startOfPeriod = "2011-07-01"
    maxSusLevel0 = 10000
    maxSusLevel1 = 20000
    maxSusLevel2 = 30000

    # Find everyone who's used more than the lowest limit of sus.
    query = """
    select job_stats.t_user_id, users.username, users.email, sum(tgusage.su)
    from job_stats, tgusage, users where
    job_stats.resource = tgusage.resource and
    job_stats.remote_job_id = tgusage.jobid and
    abs(datediff(job_stats.job_start_time, tgusage.submit_time)) < 2 and
    job_stats.t_user_id = users.user_id and
    job_stats.job_start_time >= '%s'
    group by job_stats.t_user_id
    having sum(tgusage.su) > %d
    """ % (startOfPeriod, maxSusLevel0)

    # print "Executing query:\n%s" % query

    cur = conn.cursor()
    cur.execute(query)

    # Depending on the "level" of overusage we'll add a different type of 
    # SUWARNED preference to their user preferences.
    # print "The following users are over the lowest limit of: %d" % maxSusLevel0
    for row in cur:

        # print row

        sus = long(row[3])
        if sus > maxSusLevel2:
            level = 2
            key = "SUWARNED2"
        elif sus > maxSusLevel1:
            level = 1
            key = "SUWARNED1"
        else:
            level = 0
            key = "SUWARNED0"

        # If the insert suceedes we send the email.  Else if we get an IntegrityError
        # we know the record already existed and the email was already sent.
        query2 = """
            insert into user_preferences (user_id, value, preference)
            values(%d, now(), "%s")
        """ % (long(row[0]), key)

        cur2 = conn.cursor()
        try:
            cur2.execute(query2)
            conn.commit()
            print("Sending warning email to %s, uid=%d,  for level %d" % (row[2], row[0], level))

            mailer.overLimitWarning(row[2], level);
            # mailer.overLimitWarning("terri@sdsc.edu", level);

        except IntegrityError, ie:
            conn.rollback()
            # print str(ie)
            print "%s already issued for %d" % (key, long(row[0]))
        except Error, e:
            conn.rollback()
            print str(e)
            print "insert row for %d FAILED unexpectedly." % long(row[0])
        cur2.close()

    cur.close()


conn = None
def main(argv=None):
    if argv is None:
        argv = sys.argv

    global conn

    # Get the database name and password
    properties = {} 
    pf = open(passwordFile, "r");
    for line in iter(pf):
        s = line.split('=')
        properties[s[0].strip()] = s[1].strip()
    
    conn = pymysql.connect(host="mysql.sdsc.edu", port=3312, user="ngbw", 
        passwd=properties["password"], db=properties["db"])

    options, remainder = getopt.getopt(argv[1:], "hauwt")
    for opt, arg in options:
        # either the name of the ref file or the directory that contains the ref files.
        if opt in ("-a"):
            allUsage()
            return 0
        elif opt in ("-u"):
            suPerUser()
            return 0
        elif opt in ("-w"):
            overLimitWarning()
            return 0
        elif opt in ("-t"):
            suPerUserTotal()
            return 0
        elif opt in ("-h"):
            print __doc__
            return 0
    print __doc__
    return 0

if __name__ == "__main__":
    sys.exit(main())