#!/usr/bin/env jython
"""
ccsv2sql
Utility to convert a CSV file to a SQL dump.

Copyright 2015 Sam Saint-Pettersen.
Licensed under the MIT/X11 License.

Tweaked for Jython.
"""
import sys
import csv
import os
import re
import datetime
import getopt

signature = 'ccsv2sql 1.0.6 [Jython] (https://github.com/stpettersens/ccsv2sql)'

def displayVersion():
	print('\n' + signature)

def displayInfo():
	print(__doc__)

def ccsv2sql(file, out, separator, db, comments, verbose, version, info):
	
	if len(sys.argv) == 1:
		displayInfo()
		sys.exit(0)

	if file == None and out == None:
		if verbose == False and version == True and info == False:
			displayVersion()

		elif verbose == False and version == False and info == True:
			displayInfo()

		sys.exit(0)

	if out == None: out = re.sub('.csv', '.sql', file)

	if file.endswith('.csv') == False:
		print('Input file is not a CSV file.')
		sys.exit(1)

	if out.endswith('.sql') == False:
		print('Output file is not a SQL file.')
		sys.exit(1)

	basename = os.path.basename(out)
	table = re.sub('.sql', '', basename)

	if separator == None: separator = ','

	if comments == None: comments = True

	fields = []
	rows = []
	csvfile = open(file, 'r')
	f = csv.reader(csvfile, delimiter=separator)
	headers = True
	for row in f:
		if headers:
			fields = separator.join(row).split(separator)
			headers = False
		else:
			rows.append(row)

	csvfile.close()

	dtable = 'DROP TABLE IF EXISTS `%s`;' % table
	ctable = 'CREATE TABLE IF NOT EXISTS `%s` (\n' % table
	insert = 'INSERT INTO `%s` VALUES (\n' % table 
	inserts = []
	x = 0
	for value in rows[0]:

		key = fields[x]

		fvalue = re.sub('\'|\"', '', value)
		tvalue = re.sub('\.', '', fvalue)

		if value.startswith('ObjectId('):
			ctable += '`%s` VARCHAR(24),\n' % key

		elif tvalue.isdigit() == False:
			pattern = re.compile('\d{4}\-\d{2}\-\d{2}')
			if pattern.match(value):
				ctable += '`%s` TIMESTAMP,\n' % key

			pattern = re.compile('true|false', re.IGNORECASE)
			if pattern.match(value):
				ctable += '`%s` BOOLEAN,\n' % key

			else:
				length = 50
				if key == 'description': length = 100
				ctable += '`%s` VARCHAR(%d),\n' % (key, length)

		else: ctable += '`%s` NUMERIC(15, 2),\n' % key

		x = x + 1

	x = 0
	for row in rows:

		ii = ''
		for value in rows[x]:

			fvalue = re.sub('ObjectId|\(|\)|\'|\"', '', value)
			tvalue = re.sub('\.', '', value)

			if tvalue.isdigit() == False:
				pattern = re.compile('\d{4}\-\d{2}\-\d{2}')
				if pattern.match(value):
					fvalue = re.sub('\T', ' ', fvalue)
					fvalue = re.sub('\.\d{3}Z', '', fvalue)
					fvalue = re.sub('\.\d{3}\+\d{4}', '', fvalue)

				pattern = re.compile('true|false|null', re.IGNORECASE)
				if pattern.match(value):
					ii += '%s,\n' % fvalue.upper()
					continue

				ii += '\'%s\',\n' % fvalue

			else: ii += '%s,\n' % fvalue

		ii = ii[:-2]
		inserts.append(insert + ii + ');\n\n')
		ii = ''
		x = x + 1

	ctable = ctable[:-2]
	ctable += ');'

	if verbose:
		print('\nGenerating SQL dump file: \'%s\' from\nCSV file: \'%s\'\n'
		% (out, file))

	f = open(out, 'w')
	f.write('-- !\n')
	if comments:
		f.write('-- SQL table dump from CSV file: %s (%s -> %s)\n' 
		% table, file, out))
		f.write('-- Generated by: %s\n' % signature)
		f.write('-- Generated at: %s\n\n' % datetime.datetime.now())
	if db != None: f.write('USE `%s`;\n' % db)
	f.write('%s\n' % dtable)
	f.write('%s\n\n' % ctable)

	for insert in inserts:
		f.write(insert)

	f.close()


# Handle any command line arguments.
try:
	opts, args = getopt.getopt(sys.argv[1:], "f:o:s:d:nlvi")
except:
	print('Invalid option or argument.')
	displayInfo()
	sys.exit(2)

file = None
out = None
separator = None
db = None
comments = None
verbose = False
version = False
info = False
for o, a in opts:
	if o == '-f':
		file = a
	elif o == '-o':
		out = a
	elif o == '-s':
		separator = a
	elif o == '-d':
		db = a
	elif o == 'n':
		comments = False
	elif o == '-l':
		verbose = True
	elif o == '-v':
		version = True
	elif o == '-i':
		info = True
	else:
		assert False, 'unhandled option'

ccsv2sql(file, out, separator, db, comments, verbose, version, info)
