from hashlib import sha512
from uuid import uuid4
from sqlite3 import connect
from random import choice, random, randint
from csv import reader, Sniffer

def dict_factory(cursor, row):
	d = {}
	for idx, col in enumerate(cursor.description):
		d[col[0]] = row[idx]
	return d

def get_table(category):
	conn = connect('data.db')
	conn.row_factory = dict_factory
	c = conn.cursor()
	c.execute('SELECT * FROM %s ORDER BY id + 0' % category )
	results = c.fetchall()
	conn.close()
	return results

def update_entry(
	category, idnum, name, email, dob, address,
	phone, level, cursched, pastscheds
	):
	conn = connect('data.db')
	c = conn.cursor()
	header = get_header(
		True, name, level, email, dob, address, phone, hasNewline=False
		)
	q = 'INSERT OR REPLACE INTO %s (id, name, email, dob, address, ' % category + \
		'phone, level, cursched, pastscheds) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)'
	print q
	print tuple(value if value else None for value in [
		idnum, name, email, dob, address, phone, level, cursched, pastscheds
		])
	c.execute(q, tuple(value if value else None for value in [
		idnum, name, email, dob, address, phone, level, cursched, pastscheds
		]))
	conn.commit()
	conn.close()

def delete_entry(category, idnum):
	conn = connect('data.db')
	c = conn.cursor()
	q = 'DELETE FROM %s WHERE id = ?' % category
	c.execute(q, (idnum, ))
	conn.commit()
	conn.close()

def get_header(
	hasID, hasName, hasLevel, hasEmail, hasDOB,
	hasAddress, hasPhone, hasNewline=True
	):
	'''Creates a header for the randomly-generated csv file
	'''
	s = ''
	if hasID:
		s += 'id, '
	if hasName:
		s += 'name, '
	if hasLevel:
		s += 'level, '
	if hasEmail:
		s += 'email, '
	if hasDOB:
		s += 'dob, '
	if hasAddress:
		s += 'address, '
	if hasPhone:
		s += 'phone, '
	s = s[:-2]
	if hasNewline:
		s += '\n'
	return s

def randLists():
	'''Returns a series of lists that are used to generate random data
	'''
	f = open('./static/random/firstNames.txt', 'r')
	firstNames = f.read().splitlines()
	f.close()
	f = open('./static/random/lastNames.txt', 'r')
	lastNames = f.read().splitlines()
	f.close()
	f = open('./static/random/streetSuffixes.txt', 'r')
	streetSuffixes = f.read().splitlines()
	f.close()
	f = open('./static/random/cities.txt', 'r')
	cities = f.read().splitlines()
	f.close()
	f = open('./static/random/states.txt', 'r')
	states = f.read().splitlines()
	f.close()
	domains = ['aol.com', 'gmail.com', 'hotmail.com',
	'mail.com' , 'mail.kz', 'yahoo.com']
	return firstNames, lastNames, streetSuffixes, cities, states, domains

def randCSV(
	category, numPeople, hasName, hasLevel, hasEmail, hasDOB,
	hasAddress, hasPhone, deleteEntries, probDeletion
	):
	# ID is generated by default.
	s = get_header(True, hasName, hasLevel, hasEmail, hasDOB, hasAddress, hasPhone)
	firstNames, lastNames, streetSuffixes, cities, states, domains = randLists()
	for i in range(numPeople):
		s += str(i + 1) + ', '
		if hasName:
			name = [choice(firstNames), choice(lastNames)]
			s += ' '.join(name) + ', '
		if hasLevel:
			level = False
			if not deleteEntries or random() > probDeletion:
				if category == 'students':
					level = randint(9, 12)
				else:
					r = random()
					if r > 0.95:
						level = 'Assistant Principal'
					else:
						level = 'Teacher'
				s += str(level)
			s += ', '
		if hasEmail:
			if not deleteEntries or random() > probDeletion:
				# first letter of first name and part of last name,
				# followed by random email domain
				s += name[0][0].lower() + name[1][:randint(5, 7)].lower() + \
					'@' + choice(domains)
			s += ', '
		if hasDOB:
			if not deleteEntries or random() > probDeletion:
				# random date between 1/1/1998 and 12/28/2001
				s += str(randint(1, 12)) + '/' + str(randint(1, 28)) + \
					'/' + (str(2010 - level)
						if (hasLevel and level and category == 'students')
						else str(randint(1998, 2001)))
			s += ', '
		if hasAddress:
			if not deleteEntries or random() > probDeletion:
				# street address, city, state, zip
				s += str(randint(1, 9999)) + ' ' + choice(lastNames) + ' ' + \
					choice(streetSuffixes) + ' ' + choice(cities) + ' ' + \
					choice(states) + ' ' + str(randint(3000, 99999))
			s += ', '
		if hasPhone:
			if not deleteEntries or random() > probDeletion:
				# random phone number such that area code doesn't start
				# with a zero, none of the middle three digits are a 9, the
				# middle three digits aren't 000, and the last 4 digits
				# aren't identical
				n = '0000000000'
				while '9' in n[3:6] or n[3:6]=='000' or n[6]==n[7]==n[8]==n[9]:
					n = str(randint(10**9, 10**10-1))
				s += n[:3] + '-' + n[3:6] + '-' + n[6:]
			s += ', '
		s = s[:-2] + '\n'
	return s[:-1]

def upload_data(
	csv, category, numColumns, idCol, levelCol, emailCol,
	phoneCol, addressCol, dobCol, nameCol, fnameCol, lnameCol
	):
	has_header = Sniffer().has_header(csv.read(1024))
	csv.seek(0)
	# Find the delimiters and quote characters in the csv.
	dialect = Sniffer().sniff(csv.read(1024))
	csv.seek(0)
	csvreader = reader(csv, dialect)
	# Skip the header in the csv, if there is one.
	if has_header:
		next(csvreader)
	conn = connect('data.db')
	c = conn.cursor()
	for row in csvreader:
		# If there are more columns than the user said there would be, return
		# False to prevent index out of bounds errors.
		if int(numColumns) > len(row):
			return False
		# n is an attempt to reconstruct the full name.
		n = row[int(nameCol) - 1] if nameCol else \
			' '.join(row[int(i) - 1] for i in [fnameCol, lnameCol] if i)
		# header contains the names of all optional columns (everything other
		# than id, salt, and hash_value).
		header = get_header(
			False, n, levelCol, emailCol, dobCol, addressCol, phoneCol, hasNewline=False
			)
		if header:
			header = ', ' + header
		header = 'id, salt, hash_value' + header
		q = 'INSERT OR REPLACE INTO %s (%s) VALUES (%s?)' % (
			category, header, header.count(',') * '?, '
			)
		salt = uuid4().hex
		arguments = (
			row[int(idCol) - 1],
			salt,
			sha512((row[int(idCol) - 1] + salt) * 10000).hexdigest()
			)
		if n:
			arguments += (n, )
		arguments += tuple(row[int(i) - 1] for i in [
			levelCol, emailCol, dobCol, addressCol, phoneCol
			] if i)
		c.execute(q, arguments)
	csv.close()
	conn.commit()
	conn.close()
	return True