# L-26 MCS 275 Fri 10 Mar 2017 : normdb.py
"""
The database OurPyFiles has one table scripts
with fields type, number, date, and file.
There are two problems:
(1) only file is unique for every record
    and can be used as key.
(2) redundant data occurs as many scripts
    belong to the same date and type.
Using the data in scripts, we create two
new tables:
(1) typedate stores types and dates,
    fields are (id, type, number, date)
(2) filedata stores the file names,
    fields are (id, file, tnd),
where tnd is the id of the corresponding
record in the typedate table.
"""
# import MySQLdb
import pymysql

def make_type_date(crs):
    """
    The table typedate stores type and date,
    e.g.: L-26 and 2017-03-10, repesented by
    four fields: id, type, number, and date.
    The input parameter crs is the cursor.
    """
    try:
        qry = 'create table typedate ' + \
          '( i INT, t CHAR(1), n INT, d DATE )'
        crs.execute(qry)
        print(qry + ' succeeded')
    except:
        print(qry + ' went wrong...')

def make_file_data(crs):
    """
    The table filedata stores file names,
    represented by three fields: id, name,
    and id of the corresponding entry in
    the typedate table, crs is the cursor.
    """
    try:
        qry = 'create table filedata ' + \
          '( i INT, f CHAR(20), t INT )'
        crs.execute(qry)
        print(qry + ' succeeded')
    except:
        print(qry + ' went wrong...')

def make_tables(crs):
    """
    Executes the MySQL commands to create
    the tables typedate and filedata.
    The input parameter crs is the cursor.
    """
    make_type_date(crs)
    make_file_data(crs)

def split_records(R):
    """
    Returns two lists: records that go
    in typedate and in filedata.
    """
    L = []
    T = []
    F = []
    cnt = -1
    for i in range(len(R)):
        d = R[i]
        p = (d[0], d[1], d[2])
        if p in L:
            ind = L.index(p)
        else:
            cnt = cnt + 1
            L.append((d[0], d[1], d[2]))
            T.append((cnt, d[0], d[1], d[2]))
            ind = cnt
        F.append((i, d[3], ind))
    return (T, F)

def insert_type_date(crs, T, doit=False):
    """
    Given the cursor and a list of values for
    the table typedate, all records are added.
    """
    for i in range(len(T)):
        p = T[i]
        qry = 'insert into typedate values ' + \
            '(\"%s\", \"%s\", \"%s\", \"%s\") ' \
            % (str(p[0]), str(p[1]), str(p[2]), str(p[3]))
        if doit:
            crs.execute(qry)
        else:
            print('query = ', qry)

def insert_file_data(crs, F, doit=False):
    """
    Given the cursor and a list of values for
    the table filedata, all records are added.
    """
    for i in range(len(F)):
        n = F[i]
        qry = 'insert into filedata values ' + \
            '(\"%s\", \"%s\", \"%s\") ' \
          % (str(n[0]), n[1], str(n[2]))
        if doit:
            crs.execute(qry)
        else:
            print('query = ', qry)

def date_files(crs):
    """
    As a final check, selects file name
    and corresonding date from the newly
    created tables, crs is the cursor.
    """
    qry = 'select f,d from filedata, typedate' \
        + ' where filedata.t = typedate.i'
    crs.execute(qry)
    res = crs.fetchall()
    print(res)
    print('#records :', len(res))

def main():
    """
    Splits scripts in two tables.
    """
    # db = MySQLdb.connect(db="OurPyFiles")
    ourdb = pymysql.connect(db="OurPyFiles")
    crs = ourdb.cursor()
    qry = 'select * from scripts order by d'
    nbr = crs.execute(qry)
    print('found %d records' % int(nbr))
    ans = input('Make the tables ? (y/n) ')
    if ans == 'y':
        make_tables(crs)
    data = crs.fetchall()
    (types, files) = split_records(data)
    print('T = ', types)
    print('F = ', files)
    if ans == 'y':
        ans = input('Do the inserts ? (y/n) ')
        insert_type_date(crs, types, ans=='y')
        insert_file_data(crs, files, ans=='y')
        date_files(crs)
        ourdb.commit()
    else:
        print('showing the insert queries')
        insert_type_date(crs, types)
        insert_file_data(crs, files)

if __name__ == "__main__":
    main()