#!/usr/bin/python3

import sys
import pandas as pd
import numpy as np
import os
import sqlite3

# infile = 'gb.db'
infile = sys.argv[1]
con = sqlite3.connect(infile)

# dIn=pd.read_sql("Select * from 'GradesTable' where GroupName='student'", con)
dIn = pd.read_sql("Select * from 'GradesTable'", con)


dOut = dIn.copy()

# replacing user names in accordance with BlackBoard
dOut["UserName"] = dOut["UserName"].str.replace("@.*$", "", regex=True)
dOut.rename(columns={"UserName": "Username"}, inplace=True)

# exclude inforows
dOut = dOut.loc[dOut["GroupName"].isin(["student"])]

# remove unneeded info cols
infoCol = [
    "FirstName",
    "LastName",
    "GroupName",
    "UserHiddenColums",
    "UserHiddenGroups",
    "UserHiddenGradeCategories",
    "SectionNum",
    "IdNum",
]
dOut = dOut.drop(infoCol, axis=1)

# drop non gradable columns
cCat = dIn["UserName"] == "_Col_Category_"
colToDrop = []
for c in dOut.columns:
    if c == "Username":
        continue
    category = dIn[cCat][c][0]
    if category not in ["HomeWork", "MidTerm", "FinalExam", "weighted_column"]:
        colToDrop.append(c)
dOut = dOut.drop(colToDrop, axis=1)

dOut.to_csv("BlackBoard.csv", index=False)