"""
Early tracking date pre-dating March 1st of 2008 is unreliable and
needs to be deleted. This module safely deletes these records in a way
which does not lock tblRequestLog.
"""

import pymssql, time
from datetime import datetime, timedelta

queries = {
    'minDate': 'SELECT MIN(dtTimeStamp) FROM dbo.tblRequestLog WITH (NOLOCK);',

    # @@rowcount is selected because of a bug in pymssql that causes
    # row.rowcount to always be 0
    'deleteRange': """DELETE FROM dbo.tblRequestLog WHERE dtTimeStamp
    >= '%s' AND dtTimeStamp <= '%s'; SELECT @@rowcount;"""
}

def openDB():
    """ Returns an open connection to the database """
    conn = pymssql.connect(host="****.****.****.****",
                          user="******",
                          password="****",
                          database="*****")
    return conn

def formatDate(dt, maxMs=True):
    """ Formats datetime objects such that MSSQL doesn't percieve them
    to be syntax errors. """
    if maxMs:
        return datetime.strftime(dt, "%Y-%m-%d %H:%M:%S.999")
    else:
        return datetime.strftime(dt, "%Y-%m-%d %H:%M:%S.000")

def getStartDate():
    """ Retrieves earliest record we have in tblRequestLog """
    conn = openDB()
    cur = conn.cursor()
    cur.execute(queries["minDate"]);
    result = cur.fetchone()[0]
    conn.close()
    return result

# This is the day the data becomes reliable
endDate = datetime(2009, 2, 28, 23, 59, 59, 999999)
startDate = getStartDate()
currentDate = startDate

# currentDate will move forward 1 minute per iteration of the loop
timeIncrement = timedelta(minutes=1)
currentDate = startDate + timeIncrement

conn = openDB()
cur = conn.cursor()

try:
    while currentDate <= endDate:
        print(str(datetime.now()) + " ::: Deleting between " +
              formatDate(startDate, False) + " and " +
              formatDate(currentDate, True) + ".")

        cur.execute(queries["deleteRange"] % (formatDate(startDate, False), formatDate(currentDate, True)));

        print(str(datetime.now()) +
              " ::: Deleted " + str(cur.fetchone()[0]) + " records.")

        currentDate += timeIncrement
        time.sleep(5)
except Exception as ex:
    print(str(ex))

conn.close()