import os, sys, time, logging

import glob, socket, subprocess

from os.path import basename

from pymonetdb.sql.connections import Connection as connect

from pymonetdb.exceptions import Error as DBError

import lsst_schema_queries as sch

import lsst_queries as q

#######################################

def main():

    dbport = 53000

    dbrelease = "Dec2016"

    dbversion = "SP5"

    dbname = "lsst_napels"

    node = socket.gethostname().split('.')[0]

    logfile = 'log/' + node + "_lsst_" + dbrelease + '_' + dbversion + "_" + time.strftime("%Y%m%d-%H%M%S") + ".log"

    logging.basicConfig(filename=logfile, filemode='w', format='%(message)s', level=logging.INFO)

    logging.info('Started')

    print "DB (%s) logging in file %s" % (dbname, logfile)

    csvdir = "/export/scratch2/bscheers/lsst/csvs"

    csvfiles = sorted(glob.glob(csvdir + '/S*_*.csv'))

    print "len(csvfiles) = %s" % (len(csvfiles))

    p = subprocess.Popen(["./init_db.sh", dbname], stdout=subprocess.PIPE)

    output, err = p.communicate()

    print "O:",output

    if err is not None:

        print "E:",err

    hname = 'localhost'

    uname = 'monetdb'

    pword = 'monetdb'

    tstart = time.time()

    conn = connect(hostname=hname, port=dbport, username=uname, password=pword, database=dbname)

    fcnt = 0

    scnt, fscnt, ocnt, ofocnt = 0, 0, 0, 0

    for f in csvfiles:

        fcnt += 1

        t0 = time.time()

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

        print "====>\n#### Loading CSV file nr %s: %s ####" % (fcnt, basename(f))

        if tabname.startswith("source_"):

            scnt += 1

            sch.create_table(conn, basename(f), q=1)

            q.load_csv(conn, f, q=2) # executes queries 2 & 3

            if scnt == 1:

                sch.create_merge_table(conn, basename(f), q=4)

                sch.alter_merge_table_add_pkey(conn, f, q=5)

            sch.alter_merge_table_add_table(conn, basename(f), q=6)

        elif tabname.startswith("forcedsource_"):

            fscnt += 1

            sch.create_table(conn, basename(f), q=7)

            q.load_csv(conn, f, q=8)

            if fscnt == 1:

                sch.create_merge_table(conn, basename(f), q=10)

                sch.alter_merge_table_add_pkey(conn, f, q=11)

            sch.alter_merge_table_add_table(conn, basename(f), q=12)

        elif tabname.startswith("object_"):

            ocnt += 1

            sch.create_table(conn, basename(f), q=13)

            q.load_csv(conn, f, q=14)

            if ocnt == 1:

                sch.create_merge_table(conn, basename(f), q=16)

                sch.alter_merge_table_add_pkey(conn, f, q=17)

            sch.alter_merge_table_add_table(conn, basename(f), q=18)

        elif tabname.startswith("objectfulloverlap_"):

            ofocnt += 1

            sch.create_table(conn, basename(f), q=19)

            q.load_csv(conn, f, q=20)

            if ofocnt == 1:

                sch.create_merge_table(conn, basename(f), q=22)

                sch.alter_merge_table_add_pkey(conn, f, q=23)

            sch.alter_merge_table_add_table(conn, basename(f), q=24)

        else:

            sys.exit("No valid tabname: %s" % (tabname))

        print "# L : Total copy-into time for this file: %s seconds" % (str(round(time.time() - t0, 3)))

        print "# L : Total processing time: %s seconds" % (str(round(time.time() - tstart, 3)))

    conn.close()

    print "# DB: Connection closed"

    print "\n#### Total elapsed time: %s seconds ####\n" % (str(round(time.time() - tstart, 3)))

    logging.info('Ended')

    print "Check log file: %s" % (logfile)

if __name__ == '__main__':

    main()