import cx_Oracle

OK = (0, "")
NO_CITIZEN = (1, "No citizen")
PLATE_EXISTS = (2, "Plate exists")
NOTHING_TO_DO = (3, "Nothing to do")

class Vehicle():

    id = 0
    plate = ''
    model = ''
    citizen_id = 0

    def __init__(self, id, plate, model, citizen_id):
        self.id = id
        self.plate = plate
        self.model = model
        self.citizen_id = citizen_id

    def __str__(self):
        return "%d\t%d\t%s\t%s" % (self.id, self.citizen_id, self.plate, self.model)

class Citizen():

    id = 0
    name = ''
    bdate = ''

    def __init__(self, id, name, bdate):
        self.id = id
        self.name = name
        self.bdate = bdate

    def __str__(self):
        return "%d\t%s\t%s" % (self.id, self.bdate, self.name)


class Db():

    __db = None

    def __init__(self):
        self.__db = cx_Oracle.connect('usr', 'passwd', 'localhost:1521/XE')
        self.__db.autocommit = True

    def get_id_by_plate(self, plate):
        c = self.__db.cursor()
        c.execute()

    def get_max_vehicle_id(self):
        c = self.__db.cursor()
        c.execute('select max(id) from vehicle')
        mx = 0
        for row in c:
            mx = row[0]
        c.close()
        return mx

    def get_max_citizen_id(self):
        c = self.__db.cursor()
        c.execute('select max(id) from citizen')
        mx = 0
        for row in c:
            mx = row[0]
        c.close()
        return mx

    def exist_vehicle(self, plate):
        c = self.__db.cursor()
        c.execute('select * from vehicle where to_char(plate) = \'%s\'' % plate)
        c.fetchall()
        result = c.rowcount > 0
        c.close()
        return result

    def exist_citizen(self, citizen_id):
        c = self.__db.cursor()
        c.execute('select * from citizen where id = %d' % citizen_id)
        c.fetchall()
        result = c.rowcount > 0
        c.close()
        return result

    def get_vehicles(self):
        c = self.__db.cursor()
        c.execute('select * from vehicle')
        vehs = []
        for row in c:
            vehs.append(Vehicle(row[0], row[1], row[2], row[3]))
        c.close()
        return vehs

    def get_citizens(self):
        c = self.__db.cursor()
        c.execute('select * from citizen')
        cits = []
        for row in c:
            cits.append(Citizen(row[0], row[1], row[2]))
        c.close()
        return cits

    def add_vehicle(self, vehicle):
        plate_unique = not self.exist_vehicle(vehicle.plate)
        if plate_unique:
            citizen_exist = self.exist_citizen(vehicle.citizen_id)
            if citizen_exist:
                v_id = self.get_max_vehicle_id() + 1
                c = self.__db.cursor()
                query = 'insert into vehicle (id, plate, model, citizen_id) values (%d, \'%s\', \'%s\', %d)'
                query = query % (v_id, vehicle.plate, vehicle.model, vehicle.citizen_id)
                print(query)
                c.execute(query)
                c.close()
                return OK
            else:
                return NO_CITIZEN
        else:
            return PLATE_EXISTS


    def add_citizen(self, citizen):
        c_id = self.get_max_citizen_id() + 1
        c = self.__db.cursor()
        to_date = "to_date('%s', 'dd.mm.yyyy')" % citizen.bdate
        query = 'insert into citizen (id, name, naissance) values (%d, \'%s\', %s)' % (c_id, citizen.name, to_date)
        c.execute(query)
        c.close()
        return OK

    def update_vehicle(self, vehicle):
        plate_exists = self.exist_vehicle(vehicle.plate)
        if plate_exists:
            citizen_exists = self.exist_citizen(vehicle.citizen_id)
            if citizen_exists:
                c = self.__db.cursor()
                query = 'update vehicle set model = \'%s\', citizen_id = %d where to_char(plate) = \'%s\''
                query = query % (vehicle.model, vehicle.citizen_id, vehicle.plate)
                print(query)
                c.execute(query)
                c.close()
                return OK
            else:
                return NO_CITIZEN
        else:
            return NOTHING_TO_DO

    def update_citizen(self, citizen):
        citizen_exists = self.exist_citizen(citizen.id)
        if citizen_exists:
            c = self.__db.cursor()
            query = 'update citizen set name = \'%s\', naissance = %s where id = %d'
            to_date = "to_date('%s', 'dd.mm.yyyy')" % citizen.bdate
            query = query % (citizen.name, to_date, citizen.id)
            print(query)
            c.execute(query)
            c.close()
            return OK
        else:
            return NO_CITIZEN

    def delete_vehicle(self, vehicle):
        plate_exists = self.exist_vehicle(vehicle.plate)
        if plate_exists:
            c = self.__db.cursor()
            query = 'delete from vehicle where to_char(plate) = \'%s\'' % vehicle.plate
            print(query)
            c.execute(query)
            c.close()
            return OK
        else:
            return NOTHING_TO_DO

    def delete_citizen(self, citizen):
        citizen_exists = self.exist_citizen(citizen.id)
        if citizen_exists:
            c = self.__db.cursor()
            c.execute('delete from vehicle where citizen_id = %d' % citizen.id)
            c.close()
            c = self.__db.cursor()
            c.execute('delete from citizen where id = %d' % citizen.id)
            c.close()
            return OK
        else:
            return NOTHING_TO_DO




if __name__ == "__main__":
    db = Db()

    for v in db.get_vehicles():
        print(v)

    for c in db.get_citizens():
        print(c)
    print(db.exist_vehicle("289FKDPLC"))
    print(db.exist_citizen(45))

    print(db.get_max_vehicle_id())

    print(db.add_vehicle(Vehicle(0, "FHUMANITY", "KamAZ 51324", 4)))

    #print(db.add_citizen(Citizen(0, 'Robert Trappe', '13.02.1995')))

    #print(db.update_vehicle(Vehicle(0, "FHUMANITY", 'Renault Logan', 4)))

    #print(db.update_citizen(Citizen(7, 'Steve Poubelle', '18.01.1991')))

    #print(db.delete_vehicle(Vehicle(0, "ACIDK1NGG", '', 0)))

    print(db.delete_citizen(Citizen(4, '', '')))