import psycopg2
import sys

class pgDatabase:

    dbname = '***'
    user = '***'
    host = '***'
    password = '***'

    def __init__(self):
        try:
            self.conn = psycopg2.connect("dbname='eomf' user='***' host='***' password='***'")
        except:
            print "Unnable to connect to database"
            sys.exit()

        self.cur = self.conn.cursor()

    def exists(self, f):
        query = "select count(*) from inventory_file where name = '%s'" % (f.filename)
        try:
            self.cur.execute(query)
            rows = self.cur.fetchall()
            return rows[0][0] > 0
        except:
            print "Error with query:" + query

    def add(self, f):
        select = "SELECT count(*) from inventory_file WHERE tile_id = '%s' AND year = %s\
                    AND day = %s AND dataset_id = '%s'" % (f.info('tile'), f.info('year'),
                    f.info('day'), f.info('dataset').lower())

        update = "UPDATE inventory_file SET name = '%s', timestamp = %s \
                  WHERE tile_id = '%s' and year = %s and day = %s and dataset_id = '%s'" % \
                  (f.filename, f.info('stamp'), f.info('tile'), f.info('year'),
                    f.info('day'), f.info('dataset').lower())

        insert = "INSERT INTO inventory_file (tile_id,name,year,day,timestamp,dataset_id) " + \
               "VALUES ('%s','%s',%s,%s,%s,'%s')" % \
                (f.info('tile'), f.filename, f.info('year'), \
                 f.info('day'), f.info('stamp'), f.info('dataset').lower())

        self.execute(select)
        rows = self.cur.fetchall()
        if rows[0][0] > 0:
            self.execute(update)
        else:
            self.execute(insert)

    #Does not work, pseudo-sql
    def update(self, update, insert):
        query = "BEGIN; %s; if (!affected_rows){ %s; if (error) { ROLLBACK; %s ; } } COMMIT;" % (update, insert, update)
        self.execute(query)

    def execute(self, query):
        try:
            #print query
            self.cur.execute(query)
        except:
            print "Error with query:" + query
            sys.exit()

        self.conn.commit()

if __name__ == '__main__':
    #test
    db = pgDatabase()