import sys, time, logging, subprocess

from os.path import basename

from pymonetdb.sql.connections import Connection as connect

from pymonetdb.exceptions import Error as DBError

def create_table(conn, csvfile, q):

    """General function to create either a source, forcedsource,

    object or objectfulloverlap table. The corresponding sql is

    executed.

    """

    tabname = basename(csvfile)[:-4].lower()

    fname = tabname.split("_")[0]

    sqlfile = "sql/create_" + fname + ".sql"

    with open(sqlfile, 'r') as f:

        qu = f.read()

    params = {'tabname': tabname}

    query = qu % (params)

    try:

        cursor = conn.cursor()

        t0 = time.time()

        cursor.execute(query)

        t = round(1000 * (time.time() - t0), 3)

        conn.commit()

        cursor.close()

        print "# CT: (Q%s: %s ms) Created table: %s" % (q, t, tabname)

    except DBError, dbe:

        print "Failed for reason %s on query %s" % (dbe, query)

        raise

def create_merge_table(conn, csvfile, q):

    """General function to create a merge table

    """

    tabname = basename(csvfile)[:-4].lower()

    merge_tabname = tabname.split("_")[0]

    with open('sql/create_merge_tabname.sql', 'r') as f:

        qu = f.read()

    params = {'merge_tabname': merge_tabname

             ,'tabname': tabname}

    query = qu % (params)

    try:

        cursor = conn.cursor()

        t0 = time.time()

        cursor.execute(query)

        t = round(1000 * (time.time() - t0), 3)

        conn.commit()

        cursor.close()

        print "# CM: (Q%s: %s ms) Created merge table %s based upon: %s" \

            % (q, t, merge_tabname, tabname)

    except DBError, dbe:

        print "Failed for reason %s on query %s" % (dbe, query)

        raise

def alter_merge_table_add_table(conn, csvfile, q):

    """Add a (sub)table to the merge table

    """

    tabname = basename(csvfile)[:-4].lower()

    merge_tabname = tabname.split("_")[0]

    with open('sql/alter_merge_table_add_table.sql', 'r') as f:

        qu = f.read()

    params = {'merge_tabname': merge_tabname

             ,'tabname': tabname}

    query = qu % (params)

    try:

        cursor = conn.cursor()

        t0 = time.time()

        cursor.execute(query)

        t = round(1000 * (time.time() - t0), 3)

        conn.commit()

        cursor.close()

        print "# AM: (Q%s: %s ms) Alter merge table %s add table %s" \

            % (q, t, merge_tabname, tabname)

    except DBError, dbe:

        print "Failed for reason %s on query %s" % (dbe, query)

        raise

def alter_merge_table_add_pkey(conn, csvfile, q):

    """Add a primary key to the merge table

    """

    tabname = basename(csvfile)[:-4].lower()

    merge_tabname = tabname.split("_")[0]

    if tabname.startswith("source_"):

        pkey = "id"

    elif tabname.startswith("forcedsource_"):

        pkey = "deepSourceId,scienceCcdExposureId"

    elif tabname.startswith("object_"):

        pkey = "deepSourceId"

    elif tabname.startswith("objectfulloverlap_"):

        return

    # NOTE: this sql is being used in load csv part as well (lsst_queries.py)

    with open('sql/alter_tabname_add_pkey.sql', 'r') as f:

        qu = f.read()

    params = {'tabname': merge_tabname

             ,'pkey': pkey}

    query = qu % (params)

    try:

        cursor = conn.cursor()

        t0 = time.time()

        cursor.execute(query)

        t = round(1000 * (time.time() - t0), 3)

        conn.commit()

        cursor.close()

        print "# AM: (Q%s: %s ms) Alter merge table %s add primary key (%s)" \

            % (q, t, merge_tabname, pkey)

    except DBError, dbe:

        print "Failed for reason %s on query %s" % (dbe, query)

        raise