import psycopg2
import sys


class DatabaseClass(object):
    def __init__(self):
        pass

    def execute(self, query):
        try:
            cursor = self.conn.cursor()
            cursor.execute(query)
            res = cursor.fetchall()
            cursor.close()
            return res
        except psycopg2.Error as e:
            print "psycopg2 error ({0}): {1}".format(e.pgcode, e.pgerror)
            self.conn.rollback()


class Adapter(DatabaseClass):
    def __init__(self, db, user, pw):
        try:
            self.conn = psycopg2.connect(
            "dbname={0} user={1} password={2}".format(db, user, pw))
        except psycopg2.Error as e:
            print "psycopg2 error ({0}): {1}".format(e.pgcode, e.pgerror)
            raise AdapterException("init", "failed to establish connection")
        self.tables = self.get_tables()

    def get_tables(self):
        query = """SELECT table_name FROM information_schema.tables
                        WHERE table_schema = 'public'"""
        results = self.execute(query)
        tables = []
        for table in results:
            tbl = Table(table, self.conn)
            tables.append(tbl)
        return tables

    def close(self, save=True):
        if save:
            for tbl in tables:
                table.save()
        try:
            self.conn.close()
        except psycopg2.Error as e:
            print "psycopg2 error ({0}): {1}".format(e.pgcode, e.pgerror)
            raise AdapterException("logout", "unable to logout")


class Table(DatabaseClass):
    def __init__(self, name, db, conn):
        self.conn = conn
        self.name = name
        self.cols = self.get_cols()
        # this list structure stores all rows we currently have loaded into
        # "memory"
        self.rows = []

    def __str__(self):
        return self.name

    def __repr__(self):
        return "Table Name: {0}, Columns: {1}".format(self.name, self.cols)

    def get_cols(self):
        query = "SELECT * FROM {0} LIMIT 0".format(self.name)
        row = self.execute(query)
        return [desc[0] for desc in row.description]

    # THIS NEEDS TO BE THOUGHT ABOUT. We do not want to potentially return
    # 1 million rows. How should we 'paginate' this? Perhaps a supplied arg
    # to specify range? Say, start = 0, end = 25 would return the first
    # 25 rows?
    #
    # What about filtering? ... WHERE?
    def get_rows(self, start, end):
        pass

    # eg. format should follow format of:
    # self.edit("colName", "SET DEFAULT expression")
    def edit(self, col, query):
        res = self.execute("ALTER TABLE {0} {1}".format(self.name, query))
        return res

    def add_row(self, data):
        query = "INSERT INTO {0} ({1}) VALUES ({2})".format(
            self.name, ", ".join(self.cols), " ".join([data[c] for c in self.cols]))
        msg = self.execute(query)
        new_row = Row(self.conn, self.name, data)
        self.rows.append(new_row)
        return new_row

    def edit_row(self, row, field, val):
        setattr(row, field, val)
        row.save()

    def delete_row(self, row):
        self.rows.remove(row)
        row.delete()

    def save(self):
        for row in self.rows:
            row.save()

    def _remove_row_from_mem(self, row):
        self.rows.remove(row)

class Row(DatabaseClass):
    def __init__(self, conn, tbl, data):
        self.conn = conn
        self.table = tbl
        self.fields = []
        for (field, val) in data.items():
            self.fields.append(field)
            setattr(self, field, val)

    def edit(self, field, val):
        setattr(self, field, val)
        self.save()

    def delete(self):
        query = "DELETE FROM {0} WHERE ID={1}".format(self.table, self.id)
        return self.execute(query)

    def save(self):
        query = "UPDATE {0} SET ({1})".format(
            self.table, ", ".join(
                [f + " = " + getattr(self, f) for f in self.fields]
                )
            )
        return self.execute(query)

    def __str__(self):
        return ", ".join([f + ": " + getattr(self, f) for f in self.fields])

    def __repr__(self):
        string = "Table Name: {0}, Fields: {1}".format(
            self.table, ", ".join(
                [f + ": " + getattr(self, f) for f in self.fields]))
        return string


class AdapterException(Exception):
    def __init__(self, act, msg):
        self.act = act
        self.msg = msg