import pymysql
import random

db = pymysql.connect("localhost", "root", "Hero2of1war", "lib")
cursor = db.cursor()


class Library(object):
    def __init__(self):
        super(Library, self).__init__()

        self.number_of_library = ''  # library_number
        self.street = ''
        self.phone_number = 0
        self.books = []
        self.visitors = []
        self.history = []
        self.authors = []

    def add_book(self, book):
        # not_found = True
        # for i in self.books:
        #     if i.name == book.name and i.author == book.author:
        #         not_found = False
        #         break
        # if not_found:
        #     self.books.append(book)
        # else:
        #     print('Book exist!')

        for i in self.books:
            if i.name == book.name and i.author == book.author:
                print('Book exist!')
                break
        else:
            self.books.append(book)

    def delete_book(self, name, author):
        book = False
        for i in lib.books:
            if i.name == name and i.author == author:
                self.books.remove(i)
                return True
        if book == False:
                print('This book not exist')
                return False

    def search_book(self, book):
        for i in self.books:
            if i.name == book:
                return i

    def add_visitor(self, visitor):
        not_found = True
        for i in self.visitors:
            if i.name == visitor.name and i.last_name == visitor.last_name and i.number == visitor.number:
                not_found = False
        if not_found == True:
                self.visitors.append(visitor)
        else:
            print('Visitor exist!')
            return False

    def remove_visitor(self, name, last_name, number):
        remove = False
        for i in lib.visitors:
            if i.name == name and i.last_name == last_name and i.number == number:
                lib.visitors.remove(i)
                return True
        if remove == False:
                print('Can not delete!')
                return False

    def write_book_visitor(self, write, book, data):
        book_in_visitor = BookInVisitor(visitor_name, visitor_last_name, book_name, book_author, data)
        book_in_visitor.visitor_name = write.name
        book_in_visitor.visitor_last_name = write.last_name
        book_in_visitor.book_name = book.name
        book_in_visitor.book_author = book.author
        book_in_visitor.data_of_take = data

        self.history.append(book_in_visitor)

    def print_history(self):
        for i in self.history:
            print(i.visitor_name + ' take', i.book_name)

    def edit_book(self, old_name, new_name):
        for i in lib.books:
            if i.name == old_name:
                i.name = new_name

    def edit_visitors(self, old_name, old_last_name, old_number, new_name, new_last_name, new_number):
        for i in lib.visitors:
            if i.name == old_name and i.last_name == old_last_name and i.number == old_number:
                i.name = new_name
                i.last_name = new_last_name
                i.number = new_number

    def user_exist(self, old_name, old_last_name, old_number):
        not_exist = False
        for i in lib.visitors:
            if i.name == old_name and i.last_name == old_last_name and i.number == old_number:
                return True
        if not_exist == False:
                print('Not exist')
                return False

    def find_book_by_name(self, name, author):
        for i in lib.books:
            if i.name == name and i.author == author:
                return i
        # return None

    def find_visitor_by_name(self, name, last_name):
        for i in lib.visitors:
            if i.name == name and i.last_name == last_name:
                return i
        # return None

    # add_return_date
    def add_data_of_remove(self, name, last_name, book, author, remove_date):
        for i in lib.history:
            if i.visitor_name == name and i.visitor_last_name == last_name and i.book_name == book and i.book_author == author:
                i.remove_of_data = remove_date

    def initialize_database(self):
        cursor.execute("INSERT INTO gender (id, name) VALUES (NULL, 'male')")
        cursor.execute("INSERT INTO gender (id, name) VALUES (NULL, 'female')")
        db.commit()
        # cursor.execute("SELECT name FROM gender")
        # name_gender = cursor.fetchall()




class Book(object):
    def __init__(self,id, name, author, date, count = '', available_count = ''):
        super(Book, self).__init__()

        self.id = id
        self.name = name
        self.author = author
        self.date = date
        self.count = count
        self.available_count = available_count

    def save(self):
        cursor.execute("INSERT INTO book (id, name, date, count, available_count) VALUES (NULL, '{0}', '{1}', {2}, {3})".format(self.name, self.date, self.count, self.available_count))

class Visitor(object):
    def __init__(self, id, name, last_name, birth_date, number, gender):
        super(Visitor, self).__init__()

        self.id = id
        self.name = name
        self.last_name = last_name
        self.birth_date = birth_date
        self.number = number
        self.gender = gender

    def save(self):
        cursor.execute("INSERT INTO visitor (id, name, last_name, birth_date, number, gender_id) VALUES (NULL, '{0}','{1}','{2}','{3}','{4}')".format(self.name, self.last_name, self.birth_date, self.number, self.gender))

class BookInVisitor(object):
    def __init__(self, visitor_name, visitor_last_name, book_name, book_author, data_of_take, remove_of_data=''):
        super(BookInVisitor, self).__init__()

        self.visitor_name = visitor_name
        self.visitor_last_name = visitor_last_name
        self.book_name = book_name
        self.book_author = book_author
        self.data_of_take = data_of_take
        self.remove_of_data = remove_of_data

class Author(object):
    def __init__(self, id = '', name = '', last_name = ''):
        super(Author, self).__init__()

        self.id = id
        self.name = name
        self.last_name = last_name

    def save(self):
        cursor.execute("INSERT INTO author (id, name, last_name) VALUES (NULL, '{0}', '{1}')".format(self.name, self.last_name))

class BookPerAuthor(object):
    def __init__(self, book_id = '', author_id = ''):
        super(BookPerAuthor, self).__init__()

        self.book_id = book_id
        self.author_id = author_id

    def save(self):
        cursor.execute("INSERT INTO book_has_author (book_id, author_id) VALUES ({0},{1})".format(self.book_id, self.author_id))

# user = 'Mike'
# password = '123456'

lib = Library()

# user_input = input("Enter user: ")
# password_input = input("Enter password: ")

# if user == user_input and password == password_input:

cursor.execute("SELECT * FROM gender")
bk = cursor.fetchall()
if len(bk) == 0:
    lib.initialize_database()

cursor.execute("SELECT * FROM author")
au = cursor.fetchall()
for author in au:
    author = Author(author[0], author[1], author[2])
    lib.authors.append(author)

cursor.execute("SELECT * FROM book")
bk = cursor.fetchall()
for book in bk:
    # cursor.execute("SELECT id from book WHERE name = '{0}'".format(book[1]))
    # b = cursor.fetchall()
    b = book[0]
    # print(b)
    cursor.execute("SELECT author_id from book_has_author WHERE book_id = {}".format(b))
    v = cursor.fetchall()
    v = v[0][0]
    # print(v)
    cursor.execute("SELECT name FROM author WHERE id = {}".format(v))
    na = cursor.fetchall()
    na = na[0][0]
    # print(na)
    # print(book)
    book = Book(book[0], book[1], na, book[2], book[3], book[4])
    lib.books.append(book)

cursor.execute("SELECT * FROM visitor")
vs = cursor.fetchall()
for visitor in vs:
    gender_name = visitor[5]
    # print(gender_name)
    cursor.execute("SELECT name FROM gender  WHERE id = {}".format(gender_name))
    gender = cursor.fetchall()
    gender = gender[0][0]
    # print(gender)
    visitor = Visitor(visitor[0], visitor[1], visitor[2], visitor[3], visitor[4], visitor[5])
    lib.visitors.append(visitor)
    # print(visitor)

cursor.execute("SELECT * FROM book_in_visitor")
bookinvisitor = cursor.fetchall()
for bkinvs in bookinvisitor:
    """айді списка"""
    idfirst = bkinvs[0]
    # cursor.execute("SELECT taking_date FROM book_in_visitor WHERE id = {}".format(idfirst))
    # takingdate = cursor.fetchall()  # takingDate, TakingDate, taking_date
    # takingdate = takingdate[0][0]
    takingdate = str(bkinvs[1])
    # print(takingdate)
    # cursor.execute("SELECT returning_date FROM book_in_visitor WHERE id = {}".format(idfirst))
    # returningdate = cursor.fetchall()
    # returningdate = returningdate[0][0]
    returningdate = str(bkinvs[2])
    # print(returningdate)
    # cursor.execute("SELECT visitor_id FROM book_in_visitor WHERE id = {}".format(idfirst))
    # """берем число в вісітора_айді"""
    # visid = cursor.fetchall()
    # visid = visid[0][0]
    visid = str(bkinvs[3])
    cursor.execute("SELECT name FROM visitor WHERE id = {}".format(visid))
    """дістаєм імя"""
    visname = cursor.fetchall()
    visname = visname[0][0]
    # print(visname)
    cursor.execute("SELECT last_name FROM visitor WHERE id = {}".format(visid))
    """дістаєм прізвище"""
    vislastname = cursor.fetchall()
    vislastname = vislastname[0][0]
    # print(vislastname)
    # cursor.execute("SELECT book_id FROM book_in_visitor WHERE id = {}".format(idfirst))
    # """берем число бук_айді"""
    # bookid = cursor.fetchall()
    # bookid = bookid[0][0]
    bookid = str(bkinvs[4])
    # print(bookid)
    cursor.execute("SELECT name FROM book WHERE id = {}".format(bookid))
    bookname = cursor.fetchall()
    bookname = bookname[0][0]
    # print(bookname)
    cursor.execute("SELECT author_id FROM book_has_author WHERE book_id = {}".format(bookid))
    authorid = cursor.fetchall()
    authorid = authorid[0][0]
    # print(authorid)
    cursor.execute("SELECT name FROM author WHERE id = {}".format(authorid))
    authorname = cursor.fetchall()
    authorname = authorname[0][0]
    # print(authorname)
    # cursor.execute("SELECT last_name FROM author WHERE id = {}".format(authorid))
    # authorlastname = cursor.fetchall()
    # authorlastname = authorlastname[0][0]
    # print(authorlastname)
    bookvisitor = BookInVisitor(visname, vislastname, bookname, authorname, takingdate, returningdate)
    lib.history.append(bookvisitor)


# book1 = Book(None, '300 spartans', 'Zak Plahin', '2001')
# lib.add_book(book1)

# visitor1 = Visitor(None, 'Oleg', 'See', '12-12-2001', '+380635887884', 'male')
# lib.add_visitor(visitor1)
#
# visitor2 = Visitor(None, 'Chak', 'Broflovski', '09-11-2001', '+38073911112', 'male')
# lib.add_visitor(visitor2)



while True:
    number = int(input('1 - Show all books\n2 - Show all visitors\n3 - Add visitor\n4 - Add book\n5 - Show author\n6 - Change visitor\n7 - Write book on visitor\n8 - Add remove date\n9 - Show history\n10 - Serch book by name\n11 - Search book by author\n12 - Show books in visitor\n:'))
    if number == 1:
        if len(lib.books) == 0:
            print('Our library has not book')
        for i in lib.books:
            print(str(i.id) + ' Book: ' + i.name +', author: ' + i.author + ', date: ' + str(i.date))
    elif number == 2:
        if len(lib.visitors) == 0:
            print('Our library has not visitors')
        for i in lib.visitors:
            cursor.execute("SELECT name FROM gender WHERE id = '{}'".format(i.gender)) # дістав назву статі із бази данних(id)
            gender = cursor.fetchall()
            gender = gender[0][0] # дістав з кортежу стать в правильному вигляді
            print('ID: ' + str(i.id) + ', Name: ' + i.name + ', last name: ' + i.last_name + ', birth date: ' + str(i.birth_date) + ', number: ' + str(i.number) + ', gender: ' + gender)
    elif number == 3:
        first_name = input('Enter name: ')
        first_name = first_name.capitalize()
        second_name = input('Enter last_name: ')
        second_name = second_name.capitalize()
        birth_date = input('Enter birth date(yyyy-mm-dd): ')
        number = int(input('Enter your number(380** *** *** *): '))
        gender = input('Enter: 1-male / 2-female: ')
        if gender == str(1):
            cursor.execute("select name from gender where id = '{}'".format(1))
            sex = cursor.fetchall()
            sex = sex[0][0]
            # print(sex)
        elif gender == str(2):
            cursor.execute("select name from gender where id = '{}'".format(2))
            sex = cursor.fetchall()
            sex = sex[0][0]
            # print(sex)
        else:
            print('Error: gender_id!')
            break
        visit = Visitor(None, first_name, second_name, birth_date, number, gender)
        if lib.add_visitor(visit) == False:
            break
        visit.save()
        cursor.execute("SELECT last_insert_id()")
        vid = cursor.fetchall()
        db.commit()
        # print(vid)
        visit.id = vid[0][0]
        # lib.add_visitor(visit)
    elif number == 4:
        name_book = input('Enter book name: ')
        name_book = name_book.capitalize()
        author_name = input('Enter author name: ')
        author_name = author_name.capitalize()
        author_last_name = input('Enter author last name: ')
        author_last_name = author_last_name.capitalize()
        found = False
        for author in lib.authors:
            if author.name == author_name and author.last_name == author_last_name:
                found = True
                aid = author.id
                db.commit()
        if not found:
            author = Author(None, author_name, author_last_name)
            author.save()
            cursor.execute("Select last_insert_id()")
            aid = cursor.fetchall()
            aid = aid[0][0]
            # print(aid)
            author.id = aid
            lib.authors.append(author)
            db.commit()
        for book in lib.books:
            if book.name == name_book:
                found = True
                print('This book exist')
        if not found:
            date = input('Enter date(yyyy-mm-dd): ')
            count = int(input('Enter count:'))
            available_count = count
            # print(available_count)
            # print(count)
            bok = Book(None, name_book, author_name, date, count, available_count)
            bok.save()
            cursor.execute("SELECT last_insert_id()")
            bid = cursor.fetchall()
            bid = bid[0][0]
            db.commit()
            book_per_author = BookPerAuthor(bid, aid)
            book_per_author.save()
            db.commit()
            bok.id = bid
            lib.add_book(bok)
    elif number == 5:
        if len(lib.authors) == 0:
            print("Author do not exist")
        for i in lib.authors:
            print('ID:', i.id, ',' + 'name author: ' + i.name + ', last name: ' + i.last_name)
    elif number == 6:
        old_name = input('Enter old name of visitor: ')
        old_name = old_name.capitalize()
        old_last_name = input('Enter old last name of visitor: ')
        old_last_name = old_last_name.capitalize()
        old_number = int(input('Enter old number of visitor: '))
        if lib.user_exist(old_name, old_last_name, old_number) == False:
            break
        else:
            new_name = input('Enter new name visitor: ')
            new_name = new_name.capitalize()
            new_last_name = input('Enter new last name visitor: ')
            new_last_name = new_last_name.capitalize()
            new_number = input('Enter new number visitor: ')
            if new_number != int():
                print('Error int number!')
                break
            cursor.execute("UPDATE visitor SET name = '{0}', last_name = '{1}', number = '{2}' WHERE number = '{3}'". format(new_name, new_last_name, new_number, old_number))
            db.commit()
            if new_last_name == '':
                lib.edit_visitors(old_name, old_last_name, old_number, new_name, old_last_name, new_number)
            elif new_name == '':
                lib.edit_visitors(old_name, old_last_name, old_number, old_name, new_last_name, new_number)
            elif new_number == '':
                lib.edit_visitors(old_name, old_last_name, old_number, new_name, new_last_name, old_number)
            else:
                lib.edit_visitors(old_name, old_last_name, old_number, new_name, new_last_name, new_number)
    elif number == 7:
        visitor_name = input('Enter visitor name: ')
        visitor_name = visitor_name.capitalize()
        visitor_last_name = input('Enter visitor last name: ')
        visitor_last_name = visitor_last_name.capitalize()
        vis = lib.find_visitor_by_name(visitor_name, visitor_last_name)
        data_take = input('Enter data take(yyyy-mm-dd): ')
        if vis == None:
            print('Enter again visitor')
            break
        else:
            book_name = input('Enter book name: ')
            book_name = book_name.capitalize()
            book_author = input('Enter author name: ')
            book_author = book_author.capitalize()
            bo = lib.find_book_by_name(book_name, book_author)
            if bo == None:
                print('Enter again book')
                break
            else:
                cursor.execute("SELECT available_count FROM book WHERE name = '{}'".format(book_name))
                avcoif = cursor.fetchall()
                avcoif = avcoif[0][0]
                if avcoif == 0:
                    print("This book is not available!")
                else:
                    lib.write_book_visitor(vis, bo, data_take)
                    cursor.execute("SELECT id FROM visitor WHERE name = '{0}' AND last_name = '{1}'".format(visitor_name, visitor_last_name))
                    idvisitor = cursor.fetchall()
                    idvisitor = idvisitor[0][0]
                    # print(idvisitor)
                    cursor.execute("SELECT id FROM book WHERE name = '{}'".format(book_name))
                    idbook = cursor.fetchall()
                    idbook = idbook[0][0]
                    # print(idbook)
                    cursor.execute("INSERT INTO book_in_visitor (id, taking_date, returning_date, visitor_id, book_id) VALUES (NULL, '{0}', {1}, {2}, {3})".format(data_take, 'NULL', idvisitor, idbook))
                    cursor.execute("SELECT available_count FROM book WHERE id = {}".format(idbook))
                    avco = cursor.fetchall()
                    # print(avco)
                    avco = avco[0][0]
                    # print(avco)
                    result = int(avco) - 1
                    cursor.execute("UPDATE book SET available_count = '{0}' WHERE id = {1}".format(result, idbook))
                    db.commit()
    elif number == 8:
        name1 = input('Enter visitor name: ')
        name1 = name1.capitalize()
        last_na = input('Enter last_name: ')
        last_na = last_na.capitalize()
        if lib.find_visitor_by_name(name1, last_na) == None:
            print("This visitor not exist")
            break
        else:
            book = input('Enter book: ')
            book = book.capitalize()
            author = input('Enter author: ')
            author = author.capitalize()
            if lib.find_book_by_name(book, author) == None:
                print("This book not exist")
                break
            else:
                data = input('Enter data remove(yyyy-mm-dd): ')
                cursor.execute("SELECT name FROM book")
                bookname = [row[0] for row in cursor.fetchall()]
                # print(bookname)
                cursor.execute("SELECT id FROM visitor WHERE name = '{0}' AND last_name = '{1}'".format(name1, last_na))
                mid = cursor.fetchall()
                mid = mid[0][0]
                # print(mid)
                for i in bookname:
                    if i == book:
                        cursor.execute("UPDATE book_in_visitor SET returning_date = '{0}' WHERE visitor_id = {1}".format(data, mid))
                        cursor.execute("SELECT available_count FROM book WHERE name = '{}'".format(book))
                        avbo = cursor.fetchall()
                        avbo = avbo[0][0]
                        result = int(avbo) + 1
                        cursor.execute("UPDATE book SET available_count = '{0}' WHERE name = '{1}'".format(result, book))
                        db.commit()
                        lib.add_data_of_remove(name1, last_na, book, author, data)
    elif number == 9:
        for i in lib.history:
            print(i.visitor_name + ' ' + i.visitor_last_name + ' take: ' + i.book_name + ', author: ' + i.book_author + ', date: ' + str(i.data_of_take) + ', return: ' + str(i.remove_of_data))
    elif number == 10:
        i = input("Enter book name: ")
        i = i.capitalize()
        found = False
        for book in lib.books:
            if book.name == i:
                print('ID: ' + str(book.id) + ', Book: ' + book.name + ', Author: ' + book.author)
                found = True
        if not found:
            print("Not found")
    elif number == 11:
        i = input("Enter author name: ")
        i = i.capitalize()
        found = False
        for book in lib.books:
            if book.author == i:
                print('ID: ' + str(book.id) + ', Book: ' + book.name + ', Author: ' + book.author)
                found = True
        if not found:
            print("Not found")
    elif number == 12:
        name = input("Enter visitor name: ")
        name = name.capitalize()
        last_name = input("Enter visitor last name: ")
        last_name = last_name.capitalize()
        for i in lib.history:
            if i.visitor_name == name and i.visitor_last_name == last_name and i.remove_of_data == 'None':
                print('Name: ' + i.visitor_name + ', last name: ' + i.visitor_last_name + ', book: ' + i.book_name + ', author: ' + i.book_author + ', date:' + i.data_of_take)

# lib = Library()
#
# book1 = Book()
# book1.name = '300 spartans'
# book1.author = 'Zak Plahin'
#
# book2 = Book()
# book2.name = 'Hello'
# book2.author = 'Jack Frans'
#
# book3 = Book()
# book3.name = 'Madame Bovary'
# book3.author = ''
#
# visitor1 = Visitor()
# visitor1.name = 'Oleg'
# visitor1.last_name = 'See'
#
# visitor2 = Visitor()
# visitor2.name = 'Victor'
# visitor2.last_name = 'Selo'
#
# visitor3 = Visitor()
# visitor3.name = 'Victor'
# visitor3.last_name = 'Selo'
#
# lib.add_visitor(visitor1)
# lib.add_visitor(visitor2)
# lib.add_visitor(visitor3)
# lib.add_book(book2)
#
# lib.write_book_visitor(visitor2, book3)
# lib.write_book_visitor(visitor1, book1)
# lib.print_history()