import sqlite3
import decimal
import os
import csv
import win32com.client as win32
import fnmatch
import re
import datetime
import numpy as np

def csv_to_arr(csv_file, req_col=0):
	arr = []
	with open(csv_file, 'rU') as f:
		reader = csv.reader(f)
		arr = list(reader)

	if req_col > len(arr[0]):
		n_arr = np.array(arr)
		zeros_arr = np.empty((len(arr),req_col-len(arr[0])), dtype="int")
		n_arr_tmp = np.append(n_arr, zeros_arr, axis=1)
		arr = n_arr_tmp.tolist()

	header = ','.join(arr[0])
	arr = arr[1:]
	#print header
	return re.sub(r"[?\*\.#/\$%\"\(\)& \_-]", "", header), arr

def db_cur(source=":memory:"):
	conn = sqlite3.connect(source)
	conn.row_factory = sqlite3.Row
	cur = conn.cursor()

	return conn, cur

def question_marks(st):
    question_marks = '?'
    for i in range(0, len(st.split(','))-1):
        question_marks = question_marks + ",?"
    return question_marks

def arr_to_csv(file_name, header, data_arr):
    f1 = open(file_name, "w")
    f1.write(header+'\n')

    for data_row in data_arr:
        line = '' 
        for ele in data_row:
            line = line + str(ele) + ',' 

        line = line[:-1] + '\n'
        f1.write(line)    
    
    f1.close()
    return

def create_tbl(cur, tbl_name, header, arr = None, index_arr = None):
	cur.execute("""select count(*) FROM sqlite_master WHERE type='table' AND name = '%s' """ % (tbl_name))
	tbl_exists = cur.fetchone() 
	if tbl_exists[0] == 0:
		cur.execute("CREATE TABLE " + tbl_name + " (" + header.replace("id,", "id PRIMARY KEY,") + " );")
		if index_arr is not None:
			for index in index_arr:
				cur.execute("CREATE INDEX " + tbl_name + "_" + index + " ON " + tbl_name + " (" + index + ");")
		
	if arr is not None:
		cur.executemany("INSERT INTO " + tbl_name + " ("+ header +") VALUES ("+question_marks(header)+")", arr)
	return 

def main(fa_files):
	conn, cur = db_cur()
	fa_header = ""

	for fa_file in fa_files:
		fa_header, fa_arr = csv_to_arr(fa_file)
		print (fa_file)
		create_tbl(cur, "trd", fa_header, fa_arr)

	cur.execute("select distinct Counterparty from trd ")
	rows = cur.fetchall()
	for row in rows:
		print (row[0])

	return 

fa_files = []

script_dir = "U:\\PB_DeltaOne\\FA_Trade_Import"
for root, subdirs, files  in os.walk(script_dir):
	for file in files:
		if fnmatch.fnmatch(file, "ullink_to_fa_????????.csv"):
			fa_files.append(os.path.join(root, file))

fa_files.reverse()
main(fa_files)


