#!/usr/bin/python
import pg
import sys
import re

db_host = 'twix'
db_user = 'yuchen'
db_pass = 'yuchen'
db_name = 'pvsadmin'
local_file_name = 'rating4878/4878.txt'
table_name = 'scratch.import_4878'
rating_id = '4878'
state_id = 'NH'

def main():
    conn = pg.connect(db_name, db_host, -1, None, None, db_user, db_pass)
    #rating_create_table(conn)
    #rating_import(conn)
    #rating_match(conn)
    rating_update(conn)
    #trunkate_rating_candidate(conn)
    conn.close()

def trunkate_rating_candidate(conn):
    sqlquery = '''UPDATE scratch.rating_candidate SET sig_rating = btrim(sig_rating), our_rating = btrim(our_rating)'''
    do_query(conn, sqlquery, 1)

def rating_update(conn):
    # pvs_convert_ratingstring(rating)
    sqlquery = '''INSERT INTO scratch.rating_candidate (rating_id, candidate_id, sig_rating, our_rating) SELECT
    rating_id, candidate_id, sig_rating, pvs_convert_ratingstring(sig_rating)
FROM ''' + table_name + ' WHERE candidate_id IS NOT NULL;'
    do_query(conn, sqlquery, 1)

def rating_match(conn):
    # "'" + state_id + "'" +
    sqlquery = 'update ' + table_name + """ p set candidate_id = c.candidate_id
FROM candidate c JOIN office_candidate oc USING (candidate_id) JOIN office o USING (office_id)
WHERE
oc.state_id = '""" + state_id + """'
AND btrim(p.last_name) = c.lastname
AND p.first_name = c.firstname
AND btrim(p.office) = o.name
and p.candidate_id IS NULL
AND NOT EXISTS (SELECT 1 FROM candidate c2 JOIN office_candidate oc2 USING (candidate_id)
WHERE oc2.office_id = oc.office_id
AND btrim(p.last_name) = c2.lastname
AND p.first_name = c2.firstname
AND oc2.candidate_id != oc.candidate_id AND oc2.state_id = oc.state_id)"""
    do_query(conn, sqlquery, 1)


def rating_create_table(conn):
    # create the table
    sqlquery = 'CREATE TABLE ' + table_name + ''' ("candidate_id" integer,
                             "office" text,
                             "district" text,
                             "first_name" text,
                             "last_name" text,
                             "party" text,
                             "our_rating" text,
                             "sig_rating" text,
                             "rating_id" integer,
                             "state_id" text);'''
    #sqlquery = "SELECT * FROM " + table_name
    do_query(conn, sqlquery, 1)


def rating_import(conn):
    # try opening the local file
    fin = open(local_file_name)

    # import from the local file
    pa = re.compile('\t')
    for line in fin.readlines():
        tokens = pa.split(line[:-1])
        if (len(tokens) != 6):
            print 'Error with line ' + line
        sqlquery = 'INSERT INTO ' + table_name + ' (office, district, first_name, last_name, party, sig_rating, rating_id)' + " VALUES ('" + tokens[0] + "','" + tokens[1] + "','" + tokens[2] + "','" + tokens[3] + "','" + tokens[4] + "','" + tokens[5] + "'," + rating_id + ");"
        if (not do_query(conn, sqlquery, 0)):
            print 'Halt.'
            break;

    fin.close()


def do_query(conn, sqlquery, prompt):
    print conn.query('BEGIN;')
    print sqlquery
    if (prompt):
        while (1):
            ch = str(raw_input('Proceed? Y/N:'))
            if (ch == 'Y' or ch == 'y'):
                try:
                    print conn.query(sqlquery)
                    print conn.query('COMMIT;')
                    return 1
                except:
                    print conn.query('ROLLBACK;')
                    print 'Error. Rolled back.'
                    return 0

            elif (ch == 'N' or ch == 'n'):
                print conn.query('ROLLBACK;');
                return 0
            else:
                print 'Invalid input.'
    else:
        try:
            print conn.query(sqlquery)
            print conn.query('COMMIT;')
            return 1
        except:
            print conn.query('ROLLBACK;')
            print 'Error. Rolled back.'
            return 0


if __name__ == "__main__":
    sys.exit(main())