import mysql.connector
from mysql.connector.constants import ClientFlag
import sys
import utils

import datetime

class database(object):
    config = {
        'user': 'root',
        'password': '',
        'port': '3300',
        'host': '128.0.0.1',
        'database': 'normalisationprep'
    }

    lastFetch = None

    def __init__(self):
        try:
            self.connection = mysql.connector.connect(**self.config)
        except:

            print("An error connecting to the database occured")

    def query(self, q, fetch, autoCommit):
        # a database cursor is used to query records, scan and find matches etc.
        try:
            self.cursor = self.connection.cursor()
            self.cursor.execute(q)
            # returns all of the records that match the query.  If it was a SELECT query then we get results.

            if autoCommit:
                self.commit()

            if fetch:
                rows = self.cursor.fetchall()
                return rows

        except mysql.connector.Error as Error:
            print('Error running SQL %s %s'%(q,Error))
            print('Rolling back')
            self.connection.rollback()
            sys.exit(1)

        finally:
            self.cursor.close()

    def commit(self):
        self.connection.commit()

    def rollback(self):
        self.connection.rollback()

    # returns a list of Column names from the database from the table called tableName
    def getColumnNames(self, tableName):
        return self.query('SHOW columns FROM %s'%tableName, True, False)

    # returns all of the data from the table called TableName from the column columnName
    def getColumnData(self,tableName,columnName,filter=''):
        queryString = 'SELECT %s from %s'%(columnName,tableName)
        if filter != '':
            queryString = queryString + " WHERE %s like %s"%(columnName) + '%'

        return self.query(queryString, True, False)

    # this is the destructor of the class - something that should always happen when an instance is killed off
    def __del__(self):
        # make sure don't waste connections to the database so free our connection
        self.connection.close()

    # returns the list of customers in the database.  It is limited to maxRecords customers unless this is -1 in which      case it gets all customers.
    def getCustomerList(self, maxRecords=-1, filterColumn='', searchValue=''):
        sqlCustomers = "SELECT * FROM customers"
        if filterColumn != '':
            sqlCustomers = sqlCustomers + " WHERE %s=%s"%(filterColumn,searchValue)
        if maxRecords > 0:
            sqlCustomers = sqlCustomers + " LIMIT " + str(maxRecords)

        qResult = self.query(sqlCustomers, True, False)
        return qResult

    def deleteCustomerByEmail(self,emailAddress):
        deleteSQL = "DELETE from customers where email='%s'"%(emailAddress)
        qResult = self.query(deleteSQL, False, True)
        return qResult

    # updates/add the customer record in the database.
    def addCustomer(self, email,initial,surname,address1,town, county, postcode, allowUpdateOnDuplicate=True):
        addSQL = "INSERT INTO customers (email, initial, surname, address1, town, county, postcode) VALUES " \\
                 "('%s','%s','%s','%s','%s','%s','%s')"%(email,initial,surname,address1,town,county,postcode)
        if allowUpdateOnDuplicate:
            addSQL = addSQL + "ON DUPLICATE KEY UPDATE email='%s',initial='%s', surname='%s',address1='%s', town='%s'," \\
                              "county='%s',postcode='%s'"%(email,initial,surname,address1,town,county,postcode)
        self.query(addSQL,False,True)

    def getTableRowCount(self,table):
        numCustomers = self.query("SELECT COUNT(*) FROM %s"%(table),True, False)
        return numCustomers[0]

    def findCustomerByEmail(self,email):
        sql = "SELECT count(*) from customers where email='%s'"%(email)
        customersFound = self.query(sql,True,False)
        return int(customersFound[0][0])==1

if __name__=="__main__":
    print('testing database.py')
    myDatabase = database()
    while True:
        print("Number of customers in the database is : %i"%myDatabase.getTableRowCount('customers'))

        if utils.getYesNo('Do you want to add a new customer?'):
            emailAddress = input('Email Address: ')
            newCustomer = myDatabase.addCustomer(emailAddress,input('Initial: '),input('Surname: '),input('Address 1: '),input('Town: '),input('County: '),
                                   input('Postcode: '),True)
            print(newCustomer)
            input('Check your database - the customer should be there.  Press ENTER when ready')
            print('deleting customer from the database now ...')
            if utils.getYesNo('Do you wish to delete this?'):
                myDatabase.deleteCustomerByEmail(emailAddress)
        input('Hit any key')