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

import sys
import sqlite3
import csv
import argparse
import bcrypt
import os
import string
import re

# SIIUE names have alien strings like "(TE)" and are sometimes capitalized
# We remove them so that students dont keep asking what it means
def fixname(s):
    return string.capwords(re.sub('\(.*\)', '', s).strip())

def genstudent(reader, pw=''):
    for i, r in enumerate(reader):
        num = r['N.º']
        name = fixname(r['Nome'])
        yield (r['N.º'], fixname(r['Nome']), '')
    print('{} students inserted.'.format(i+1))

# ---- DATABASE SCHEMA ----
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)
    );'''

# --------- Parse command line options -----------
argparser = argparse.ArgumentParser(description='Create new database from a CSV file (SIIUE format)')
argparser.add_argument('--db', default='students.db', type=str, help='database filename')
argparser.add_argument('csvfile', nargs='?', type=str, default='', help='CSV filename')
args = argparser.parse_args()

db_exists = os.path.exists(args.db)

with sqlite3.connect(args.db) as c:
    # use existing or create new database schema
    if db_exists:
        print('-> Using previous database "{}"...'.format(args.db))
    else:
        print('-> Creating new database "{}"...'.format(args.db))
        c.executescript(sql_cmd)

    # get students
    if args.csvfile:
        csvfile = open(args.csvfile, encoding='iso-8859-1')
        print('-> Using students from CSV file "{}"...'.format(args.csvfile))
        students = genstudent(csv.DictReader(csvfile, delimiter=';', quotechar='"'))
    else:
        print('-> Creating fake students numbered 1 to 5...'.format(args.csvfile))
        students = [
            ('1', 'Student1', ''),
            ('2', 'Student2', ''),
            ('3', 'Student3', ''),
            ('4', 'Student4', ''),
            ('5', 'Student5', '')
        ]

    # insert students into database
    print('-> Inserting students into database... ')
    try:
        c.executemany('INSERT INTO students VALUES (?,?,?)', students)
    except sqlite3.IntegrityError:
        print('** ERROR ** Students already exist. Aborted!')
        sys.exit(1)

    # insert professor into database
    print('-> Inserting professor (id=0)...')
    try:
        c.execute('INSERT INTO students VALUES (?,?,?)', ('0', 'Professor', ''))
    except sqlite3.IntegrityError:
        print('** WARNING ** Professor already exists.')

print('Done.')