__author__ = 'liam'
"""
Manually convert the old OldGermanTrick database to the new schema.

    https://gist.github.com/esperlu/943776
    ./mysql2sqlite.sh -u XXXX -p XXXX | sqlite3 ogt.db
    export OGT_OLD_DB=sqllite:///ogt.db

"""

from oldgermantrick import db
from oldgermantrick.models import Category, Exam, Tag, Problem, Solution, User

from sqlalchemy import create_engine
import datetime
import re
import os
import sys

def process_latex(latex):
    # problem = problem.replace('\\par{}', '\n\n')
    latex = latex.replace('eqnarray*', 'align')
    latex = latex.replace('\\begin{problemlist}', '')
    latex = latex.replace('\\begin{enumerate}', '')
    latex = latex.replace('\\problem', '\n 1. ')
    latex = latex.replace('\\item', '\n A. ')
    latex = latex.replace('\\end{problemlist}', '')
    latex = latex.replace('\\end{enumerate}', '')
    latex = latex.replace('\\diff', '\\,\mathrm{d}')
    latex = latex.replace('\\mi', ' i')
    latex = latex.replace('\\me', ' e')
    latex = latex.replace('\\cross', '\\times')
    latex = latex.replace('``', '"')
    latex = latex.replace("''", '"')
    latex = latex.replace('{align*}', '{align}')
    latex = re.sub(r"\\braket{(.*?)}", r"\\langle \1 \\rangle", latex)
    latex = re.sub(r"\\Braket{(.*?)}", r"\\left\\langle \1 \\right\\rangle", latex)
    latex = re.sub(r"\\ket{(.*?)}", r"\lvert \1 \\rangle", latex)
    latex = re.sub(r"\\bra{(.*?)}", r"\langle \1 \\rvert", latex)
    latex = re.sub(r"\\deriv{(.*?)}{(.*?)}", r"\\frac{d \1}{d \2}", latex)
    latex = re.sub(r"\\pderiv{(.*?)}{(.*?)}", r"\\frac{\\partial \1}{\\partial \2}", latex)
    latex = re.sub(r"\\pderiv\[(.*?)\]{(.*?)}{(.*?)}", r"\\frac{\\partial^{\1} \2}{\\partial \3^{\1}}", latex)
    latex = re.sub(r"{\\it (.*?)}", r"_\1_", latex)
    latex = re.sub(r"\\underline{(.*?)}", r"**\1**", latex)
    latex = latex.replace('\\begin{align}', '$$\n\\begin{align}')
    latex = latex.replace('\\end{align}', '\\end{align}\n$$')
    return latex

if 'OGT_OLD_URI' not in os.environ:
    print('Set the environment variable OGT_OLD_URI.')
    sys.exit(1)
old_db_uri = os.environ['OGT_OLD_URI']
print('Loading old DB of preliminary exams from ' + old_db_uri + '.')
old_db = create_engine(old_db_uri)
print('New DB is ' + str(db.engine.url))

db.drop_all()
db.create_all()

user = User('0', '')

db.session.add(user)

ucd = Category(u'UC Davis Physics Preliminary Exams')
db.session.add(ucd)

tags = {
    'me': Tag(u'classical mechanics'),
    'em': Tag(u'electricity and magnetism'),
    'sm': Tag(u'statistical mechanics'),
    'ma': Tag(u'mathematical methods'),
    'qm': Tag(u'quantum mechanics'),
    'mo': Tag(u'Modern physics'),
}

for tag in tags.values():
    db.session.add(tag)

exams = {}

for (eid, year) in old_db.execute("""SELECT eid,year FROM exams""").fetchall():
    date = datetime.date.today()
    date.replace(year, 9, 25)
    exam = Exam(u'UC Davis Physics Prelim {}'.format(year), ucd, str(date))

    qu = "SELECT num,type,problem,solution FROM exam_data NATURAL JOIN problems WHERE eid=(?) ORDER BY num;"
    for (order, typ, problem, solution) in old_db.execute(qu, eid).fetchall():
        if problem is None or problem == '':
            continue
        problem = process_latex(problem)
        if solution is None:
            solution = ''
        solution = process_latex(solution)
        if typ is None or typ == '':
            problem = Problem(problem)
        else:
            problem = Problem(problem, [tags[typ]])
        problem.modifiedBy = user.id
        if solution is not '':
            print(solution)
            solution = Solution(solution)
            solution.modifiedBy = user.id
            problem.solutions.append(solution)
        exam.problems.insert(order, problem)

    db.session.add(exam)

db.session.commit()
