import time, logging
from os.path import basename

from pymonetdb.exceptions import Error as DBError

def loadcsv_drop_pkey(conn, csvfile, q):
    """
    """
    tabname = basename(csvfile)[:-4].lower()
    pkey = tabname + "_id_pkey"

    with open('loadcsv_drop_pkey.sql', 'r') as f:
        qcsv = f.read()
    params = {'tabname': tabname
             ,'pkey': pkey}
    query = qcsv % (params)
    #print "Query:\n%s" % (query)

    try:
        cursor = conn.cursor()
        t0 = time.time()
        cursor.execute(query)
        t = round(1000 * (time.time() - t0), 3)
        conn.commit()
        print "# AL: (Q%s: %s ms)  Altered table %s dropped pkey %s" % (q, t, tabname, pkey)
        logging.info("L;I?;Q%s;T%s;N0" % (q, t))
        cursor.close()
    except DBError, dbe:
        print "Failed for reason %s on query %s" % (dbe, query)
        raise

def alter_tabname_add_pkey(conn, csvfile, q):
    """Set a primary key for the table
    """
    tabname = basename(csvfile)[:-4].lower()
    if tabname.startswith("source_"):
        pkey = "id"
    elif tabname.startswith("forcedsource_"):
        pkey = "deepSourceId,scienceCcdExposureId"
    elif tabname.startswith("object_"):
        pkey = "deepSourceId"
    elif tabname.startswith("objectfulloverlap_"):
        return

    with open('sql/alter_tabname_add_pkey.sql', 'r') as f:
        qcsv = f.read()
    params = {'tabname': tabname
             ,'pkey': pkey}
    query = qcsv % (params)
    #print "query = %s" % (query)

    try:
        cursor = conn.cursor()
        t0 = time.time()
        cursor.execute(query)
        t = round(1000 * (time.time() - t0), 3)
        conn.commit()
        print "# AT: (Q%s: %s ms) Altered table %s added pkey" % (q, t, tabname)
        logging.info("L;I0;Q%s;T%s;N0" % (q, t))
        cursor.close()
    except DBError, dbe:
        print "Failed for reason %s on query %s" % (dbe, query)
        raise

def load_csv_copyinto(conn, csvfile, q):
    """Use copy into to load the csv file
    """
    tabname = basename(csvfile)[:-4].lower()
    with open('sql/loadcsv_copyinto.sql', 'r') as f:
        qcsv = f.read()
    params = {'tabname': tabname
             ,'csvfile': csvfile}
    query = qcsv % (params)
    #print "Query:\n%s" % (query)

    try:
        cursor = conn.cursor()
        t0 = time.time()
        n = cursor.execute(query)
        t = round(1000 * (time.time() - t0), 3)
        conn.commit()
        print "# CI: (Q%s: %s ms) Copied %s records into table %s" % (q, t, n, tabname)
        logging.info("L;I0;Q%s;T%s;N%s" % (q, t, n))
        cursor.close()
    except DBError, dbe:
        print "Failed for reason %s on query %s" % (dbe, query)
        raise

def load_csv(conn, csvfile, q):
    """Load the csvfile, then set the primary key
    """
    #loadcsv_drop_pkey(conn, csvfile, q+=?)
    load_csv_copyinto(conn, csvfile, q)
    alter_tabname_add_pkey(conn, csvfile, q+1)