#!/usr/bin/env python

import csv, re
from subprocess import call

infile = input("File to load:")
db = input("DB to load into:")
table = input("Table to load into:")

fh = csv.reader(open(infile, 'r'), delimiter=';', quotechar='#')
headers = next(fh)

def variablize(text, prefix=''):
    if not prefix:
        # if no prefix, move any digits or non-word chars to the end
        parts = re.match('(^[\W\d]*)(.*$)', text).groups()
        text = "%s %s" % (parts[1], parts[0])
    text = ("%s %s" % (prefix, text)).strip().lower()
    text =  re.sub('[\W]', '_', text)
    return re.sub('_*$', '', text)

columns = map(variablize, open(infile).readline().split(';'))
columns = map(lambda v: '%s varchar(128)' % v, columns)
queries = [
    'drop table %s;' % table,
    'create table %s (%s);' % (table, ','.join(columns)),
    "copy %s from '%s' with csv header;" % (table, infile),
    'alter table %s add column id serial;' % table,
    'alter table %s add primary key (id);' % table,
]
for q in queries:
    call(['psql','-a','-d',db,'-c',q])