#!/usr/bin/env python3.4
# -*- coding: utf-8 -*-

import sqlite3
import csv
from optparse import OptionParser
from hashlib import sha256
import os.path
import sys
import string
import re

# SIIUE names have alien strings like "(TE)" and are sometimes capitalized
def fixname(s):
    return string.capwords(re.sub('\(.*\)', '', s).strip())


# --------- Parse command line options -----------
parser = OptionParser('usage: %prog [options] inputfile.csv')

parser.add_option('--db', dest='db_filename', default='students.db',
                  help='database filename to create [default: %default]')

parser.add_option('--pw', dest='password', default='',
                  help='initial password [default: %default]')

(options, args) = parser.parse_args()

if len(args) != 1:
    print('You must specify a CSV file to import.\nUse option -h for help.')
    sys.exit()

# terminate if db_filename exist
if os.path.exists(options.db_filename):
    print('Database already exists. Please use a different name.')
    sys.exit()

# -------- Create database ------------
conn = sqlite3.connect(options.db_filename)
c = conn.cursor()

sql_cmd = '''PRAGMA foreign_keys = ON;
    CREATE TABLE students (
        number TEXT PRIMARY KEY,
        name TEXT,
        password TEXT
    );
    CREATE TABLE tests (
        test_id TEXT NOT NULL,
        student_id TEXT NOT NULL,
        grade REAL,
        start_time TEXT,
        finish_time TEXT,
        FOREIGN KEY(student_id) REFERENCES students(number)
    );
    CREATE TABLE questions (
        test_id TEXT NOT NULL,
        question_id TEXT NOT NULL,
        student_id TEXT NOT NULL,
        grade REAL,
        time TEXT,
        FOREIGN KEY(student_id) REFERENCES students(number)
    );'''
c.executescript(sql_cmd)

# -------- Parse CSV and insert into database --------

password = options.password  # initial common password for all students
if password != '':
    password = sha256(password.encode('utf-8')).hexdigest()

with open(args[0], encoding='iso-8859-1') as csvfile:   # SIIUE format
    csvreader = csv.reader(csvfile, delimiter=';', quotechar='"')
    next(csvreader)  # ignore header

    c.executemany('INSERT INTO students VALUES (?,?,?)',
                  [(row[0], fixname(row[1]), password) for row in csvreader])
    c.execute('INSERT INTO students VALUES ("0", "Professor", "")')
    conn.commit()   # commit DB changes
    c.close()       # close DB cursor