#!/usr/bin/env python
"""
A script to import initial data from ProEco into Theros
"""

import logging
import argparse
import sys
import re

parser=argparse.ArgumentParser(description=__doc__, formatter_class=argparse.ArgumentDefaultsHelpFormatter)
parser.add_argument("worksFile", help="the csv file containing the raw works export from ProEco", metavar="CSV_FILE" , default="travaux.csv", nargs="?")
parser.add_argument("-v","--verbose", action="store_const", dest="logging", const=logging.DEBUG, default=logging.INFO, help="show debug logs")
parser.add_argument("--dsn", help="the dsn to use for db operations", action="store", dest="dsn", default="theros_dev")
parser.add_argument("--is", "--insert-student", help="insert (ignoring duplicates) students into database (requires --dsn)", action="store_true", dest="insertStudents")
parser.add_argument("--ic", "--insert-class", help="insert (ignoring duplicates) classes into database (requires --dsn)", action="store_true", dest="insertClasses")
args=parser.parse_args()
logging.basicConfig(level=args.logging, stream=sys.stdout)
logger=logging.getLogger()

if not args.dsn and (
        args.insertStudents
        or args.insertClasses
    ):
    parser.error("missing --dsn specification")

worksFile=args.worksFile
works=[]
classes=set()
students=set()
with open(worksFile) as fh:
    header=True
    for i,line in enumerate(fh):
        if header:
            header=False
            continue
        line=line.decode("utf8")
        klass,student, dummy, foo, desc, grp = map(lambda s:s.strip(), line.split("\t"))
        klass=klass.replace(" ","").upper()
        if not re.search(r"^\d[A-Z]+$", klass):
            raise ValueError, "line %i contains bad class: %s"%(i+1, klass)
        student=student.replace("  "," ")
        classes.add(klass)
        students.add(student)
        if desc:
            works.append((klass, student, desc))
            logger.debug("keep %s", works[-1])
        else:
            logger.debug("discarded line %s", line.strip())

logger.info("got %i works, %i students, %i classes", len(works), len(students), len(classes))

if args.insertStudents or args.insertClasses:
    logging.info("connecting to database")
    import pyodbc
    conn=pyodbc.connect(dsn=args.dsn)
    try:
        db=conn.cursor()
        if args.insertStudents:
            logging.info("inserting students")
            params=[(s,) for s in sorted(students)]
            db.executemany("INSERT IGNORE INTO student(st_name) VALUES (?)", params)

        if args.insertClasses:
            logging.info("inserting classes")
            params=[(c,) for c in sorted(classes)]
            db.executemany("INSERT IGNORE INTO class(cl_desc) VALUES (?)", params)
        conn.commit()
    finally:
        conn.close()

@@ -991,16 +991,176 @@
 asses%22)%0A
+parser.add_argument(%22--iw%22, %22--insert-work%22, help=%22insert (ignoring duplicates) works into database (requires --dsn)%22, action=%22store_true%22, dest=%22insertWorks%22)%0A
 args=par
@@ -1343,16 +1343,44 @@
 Classes%0A
+        or args.insertWorks%0A
     ):%0A 
@@ -1490,16 +1490,181 @@
 s=set()%0A
+class Work:%0A    def __init__(self, klass, student, desc, line):%0A        self.klass=klass%0A        self.student=student%0A        self.desc=desc%0A        self.line=line%0A%0A
 with ope
@@ -2228,16 +2228,20 @@
 .append(
+Work
 (klass, 
@@ -2253,16 +2253,21 @@
 nt, desc
+, i+1
 ))%0A     
@@ -2518,24 +2518,44 @@
 nsertClasses
+ or args.insertWorks
 :%0A    loggin
@@ -3018,24 +3018,24 @@
 d(classes)%5D%0A
-
             
@@ -3104,16 +3104,1125 @@
  params)
+%0A%0A        if args.insertWorks:%0A            logging.info(%22inserting works%22)%0A            classes=db.execute(%22SELECT * FROM class%22).fetchall()%0A            classes=dict(%5B(c.cl_desc, c.cl_id) for c in classes%5D)%0A            students=db.execute(%22SELECT * FROM student%22).fetchall()%0A            students=dict(%5B(s.st_name, s.st_id) for s in students%5D)%0A            query = %22INSERT IGNORE INTO raw_data(rd_st_id, rd_cl_id, rd_desc) VALUES (?,?,?)%22%0A            params=%5B%5D%0A            for w in works:%0A                if w.klass not in classes:%0A                    logger.error(%22no such class: %25s for work at line %25i%22,w.klass, w.line)%0A                    continue%0A%0A                if w.student not in students:%0A                    logger.error(%22no such student: %25s for work at line %25i%22,w.student, w.line)%0A                    continue%0A%0A                classId = classes%5Bw.klass%5D%0A                studentId = students%5Bw.student%5D%0A                params.append((studentId, classId, w.desc))%0A            if len(params):%0A                db.executemany(query, params)%0A            else:%0A                logger.warn(%22no work inserted%22)
 %0A       

add insertion of raw data