# Script to create the tables and schema for the TCF database from the raw data
import psycopg2
import os
import sys


# we need to know the directory that all of the input files live in
directory = os.getcwd()
#connect to the TCF database
try:
    connection = psycopg2.connect("dbname='TCF' user='postgres' password='postgres'")
    print "Connection established to PostgreSQL Server..."
except:
    print "Unable to connect to the database..."

if connection:
    cursor = connection.cursor()

    directory_files = os.listdir(directory)

    cursor.execute('SELECT tablename FROM pg_tables')
    table_list = cursor.fetchall()
    import pdb;pdb.set_trace()

    for file_name in directory_files:
        if '.txt' in file_name:
            print "Found file: %s" % file_name
            "parse name of data file for table name"
            table_name = file_name.replace('.txt', '')
            table_name = table_name.replace('TCF.dbo.', '')
            "parse first row of input data file for column names"
            file_object = open((directory + "/" + file_name))
            column_names = file_object.readline()
            "drop table if it exists"
            if table_name in table_list:
                cursor.execute('Drop Table %s' % table_name)
                connection.commit()
            "create table in database if it does not exist based on file name"
            "create columns based on names if they do not yet exist"
            cursor.execute('Create Table %s ()'% table_name)
            connection.commit()
            "import data from input file using COPY"
            cursor.copy_from(file_object, table_name)
            file_object.close()
            connection.commit()
            sys.stdout.flush()