# import pymysql.cursors
class hw:
    def __init__(self):
        print("""============================================================
1. print all buildings
2. print all performances
3. print all audiences
4. insert a new building
5. remove a building
6. insert a new performance
7. remove a performance
8. insert a new audience
9. remove an audience
10. assign a performance to a building
11. book a performance
12. print all performances assigned to a building
13. print all audiences who booked for a performance
14. print ticket booking status of a performance
15. exit
============================================================""")

    def f1(self): #건믈
        connection = pymysql.connect(
            host = 'astronaut.snu.ac.kr',
            user = 'BDE-2018-26',
            password = '42ee45d01b57',
            db = 'BDE-2018-26',
            charset = 'utf8',
            cursorclass = pymysql.cursors.DictCursor)
        Autocommit = True
        try:
            with connection.cursor() as cursor:
                sql = 'select * from Building;'
                cursor.execute(sql)
                result = cursor.fetchall()
                print('-'*100)
                print('ID\t\t','name\t\t\t\t','type\t\t','price\t\t','booked')
                print('-'*100)
                for i in range(len(result)):
                    for k in result[i].values():
                        print(k,'        \t',end ='')
                    print('')
            connection.commit()
        finally:
            connection.close()

    def f2(self): #공연
        connection = pymysql.connect(
        host = 'astronaut.snu.ac.kr',
        user = 'BDE-2018-26',
        password = '42ee45d01b57',
        db = 'BDE-2018-26',
        charset = 'utf8',
        cursorclass = pymysql.cursors.DictCursor)
        Autocommit = True
        try:
            with connection.cursor() as cursor:
                sql = 'select * from Performance;'
                cursor.execute(sql)
                result = cursor.fetchall()
                print("-"*100)
                print('ID\t\t','name\t\t','type\t\t','price\t\t','booked\t\t')
                print('',end = '')
                print("-"*100)
                for i in range(len(result)):
                    for k in result[i].values():
                        print(k,'        \t',end ='')
                    print('')
        finally:
            connection.close()

    def f3(self): #관객
            connection = pymysql.connect(
            host = 'astronaut.snu.ac.kr',
            user = 'BDE-2018-26',
            password = '42ee45d01b57',
            db = 'BDE-2018-26',
            charset = 'utf8',
            cursorclass = pymysql.cursors.DictCursor)
            Autocommit = True
            try:
                with connection.cursor() as cursor:
                    sql = 'select * from Audience;'
                    cursor.execute(sql)
                    result = cursor.fetchall()
                print("-"*100)
                print('ID\t\t','name\t\t\t','gender\t\t','age')
                print('',end = '')
                print("-"*100)
                for i in range(len(result)):
                    for k in result[i].values():
                        print(k,'      \t',end ='')
                    print('')
            finally:
                connection.close()

    def f4(self): ##insert building
            import pymysql.cursors
            try:
                a = str(input('Building name:'))
                b = input('Building location:')
                c = int(input('Building capacity:'))
            except ValueError as e:
                return e
            else:
                return 'else'
            print('A building is successfuly inserted')
            connection = pymysql.connect(
                host = 'astronaut.snu.ac.kr',
                user = 'BDE-2018-26',
                password = '42ee45d01b57',
                db = 'BDE-2018-26',
                charset = 'utf8',
                cursorclass = pymysql.cursors.DictCursor)
            Autocommit = True
            try:
                with connection.cursor() as cursor:
                    sql = "insert into Building (name, location, capacity, assigned) values (%s, %s, %s, %s)"
                    cursor.execute(sql,(a,b,c,'0'))
                    result = cursor.fetchall()
                connection.commit()


            finally:
                connection.close()

    def f5(self): ## building delete
            connection = pymysql.connect(
                host = 'astronaut.snu.ac.kr',
                user = 'BDE-2018-26',
                password = '42ee45d01b57',
                db = 'BDE-2018-26',
                charset = 'utf8',
                cursorclass = pymysql.cursors.DictCursor)
            Autocommit = False

            try:
                with connection.cursor() as cursor:
                    sql1 = 'ALTER TABLE Building auto_increment = 1'
                    sql = "delete from Building;"
                    cursor.execute(sql)
                    cursor.execute(sql1)
                    result = cursor.fetchall()
                connection.commit()
            finally:
                connection.close()

    def f6(self): #insert a new performance
            try:
                a = input('Performance name:')
                b= input('Performance type:')
                c = int(input('Performance Price:'))
            except ValueError as e:
                return e
            else:
                return 'else'
            print('A building is successfuly inserted')
            connection = pymysql.connect(
            host = 'astronaut.snu.ac.kr',
            user = 'BDE-2018-26',
            password = '42ee45d01b57',
            db = 'BDE-2018-26',
            charset = 'utf8',
            cursorclass = pymysql.cursors.DictCursor)
            Autocommit = True
            try:
                with connection.cursor() as cursor:
                    sql = "insert into Performance (name, type, price, booked) values (%s, %s, %s, %s)"
                    cursor.execute(sql,(a,b,c,'0'))
                    result = cursor.fetchall()
                connection.commit()
            finally:
                connection.close()

    def f7(self): ## performance delete
            connection = pymysql.connect(
                host = 'astronaut.snu.ac.kr',
                user = 'BDE-2018-26',
                password = '42ee45d01b57',
                db = 'BDE-2018-26',
                charset = 'utf8',
                cursorclass = pymysql.cursors.DictCursor)
            Autocommit = False

            try:
                with connection.cursor() as cursor:
                    sql1 = 'ALTER TABLE Performance auto_increment = 1'
                    sql = "delete from Performance;"
                    cursor.execute(sql)
                    cursor.execute(sql1)
                    result = cursor.fetchall()
                connection.commit()
            finally:
                connection.close()

    def f8(self): #insert a new audience
            try:
                a = input('Performance name:')
                b= input('Performance type:')
                c = int(input('Performance Price:'))
            except ValueError as e:
                return e
            else:
                return 'else'
            print('A building is successfuly inserted')
            connection = pymysql.connect(
                host = 'astronaut.snu.ac.kr',
                user = 'BDE-2018-26',
                password = '42ee45d01b57',
                db = 'BDE-2018-26',
                charset = 'utf8',
                cursorclass = pymysql.cursors.DictCursor)
            Autocommit = True
            try:
                with connection.cursor() as cursor:
                    sql = "insert into Audience (name, gendeer, age ) values (%s, %s, %s)"
                    cursor.execute(sql,(a,b,c))
                    result = cursor.fetchall()
                connection.commit()
            finally:
                connection.close()

    def f9(self): ## audience delete
            connection = pymysql.connect(
                host = 'astronaut.snu.ac.kr',
                user = 'BDE-2018-26',
                password = '42ee45d01b57',
                db = 'BDE-2018-26',
                charset = 'utf8',
                cursorclass = pymysql.cursors.DictCursor)
            Autocommit = False

            try:
                with connection.cursor() as cursor:
                    sql1 = 'ALTER TABLE Audience auto_increment = 1'
                    sql = "delete from Audience;"
                    cursor.execute(sql)
                    cursor.execute(sql1)
                    result = cursor.fetchall()
            finally:
                connection.close()

    def f10(self): ## assigned
            try:
                a = input('Building ID : ')
                b = input('Performance ID : ')
            except ValueError as e:
                return e
            else:
                return 'else'
            connection = pymysql.connect(
                host = 'astronaut.snu.ac.kr',
                user = 'BDE-2018-26',
                password = '42ee45d01b57',
                db = 'BDE-2018-26',
                charset = 'utf8',
                cursorclass = pymysql.cursors.DictCursor)
            Autocommit = True

            try:
                with connection.cursor() as cursor:
                    sql = "update Building set assigned = assigned + 1 where Build_ID = %s"
                    #sql1 = 'select '
                    cursor.execute(sql,a)
                    #cursor.execute(sql1,b)
                    result = cursor.fetchall()
                connection.commit()
            except:
                print('error')
            finally:
                connection.close()


    def f11(self): #book
        try:
            a = input('Performance ID:')
            b = input('Audience ID:')
            c = input('Seat Nnumber:')
            e = str(c)
            f = e.split(',')
        except ValueError as e:
                return e
        else:
            return 'else'
        connection = pymysql.connect(
            host = 'astronaut.snu.ac.kr',
            user = 'BDE-2018-26',
            password = '42ee45d01b57',
            db = 'BDE-2018-26',
            charset = 'utf8',
            cursorclass = pymysql.cursors.DictCursor)
        Autocommit = True
        try:
            with connection.cursor() as cursor:
                sql = "select Performance.price from Performance where Performance_ID = %s"
                cursor.execute(sql,a)
                result = cursor.fetchall()
                for i in range(len(result)):
                    for k in result[i].values():
                        d = k
                print("Total ticket price is {0}".format(d*len(f)))
            connection.commit()
        finally:
            connection.close()


    def f12(self): ## build
        try:
            a = int(input('Performance ID : '))
        except ValueError as e:
                return e
        else:
            return 'else'
        connection = pymysql.connect(
            host = 'astronaut.snu.ac.kr',
            user = 'BDE-2018-26',
            password = '42ee45d01b57',
            db = 'BDE-2018-26',
            charset = 'utf8',
            cursorclass = pymysql.cursors.DictCursor)
        Autocommit = True
        try:
            with connection.cursor() as cursor:
                sql = """select *
                        from Performance
                        where Performance_ID = %s"""
                cursor.execute(sql,a)
                result = cursor.fetchall()
                print('-'*80)
                print('ID\t\t','name\t\t\t','type\t\t','price\t\t','booked')
                print('-'*80)
                for i in range(len(result)):
                    for k in result[i].values():
                        print(k,'       \t',end ='')
                    print('')
            connection.commit()

        finally:
            connection.close()


    def f13(self): ## assigned
        try:
            a = input('Audience ID : ')
        except ValueError as e:
                return e
        else:
            return 'else'
        connection = pymysql.connect(
            host = 'astronaut.snu.ac.kr',
            user = 'BDE-2018-26',
            password = '42ee45d01b57',
            db = 'BDE-2018-26',
            charset = 'utf8',
            cursorclass = pymysql.cursors.DictCursor)
        Autocommit = True

        try:
            with connection.cursor() as cursor:
                sql = """select *
                        from Audience
                        where Audience_ID = %s"""
                cursor.execute(sql,a)
                result = cursor.fetchall()
                print('-'*80)
                print('ID','\t\t','name','\t\t\t','gender','\t\t','age','\t\t')
                print('-'*80)
                for i in range(len(result)):
                    for k in result[i].values():
                        print(k,'       \t',end ='')
                    print('')
            connection.commit()
        finally:
            connection.close()



    def f14(self): ## book
        try:
            a = input('Performance ID : ')
        except ValueError as e:
                return e
        else:
            return 'else'
        connection = pymysql.connect(
            host = 'astronaut.snu.ac.kr',
            user = 'BDE-2018-26',
            password = '42ee45d01b57',
            db = 'BDE-2018-26',
            charset = 'utf8',
            cursorclass = pymysql.cursors.DictCursor)
        Autocommit = True

        try:
            with connection.cursor() as cursor:
                sql = """select *
                         from book
                         where seat;"""
                cursor.execute(sql,a)
                result = cursor.fetchall()
                print('-'*100)
                print('Seat_number','\t\t\t\t\t\t','audience_id')
                print('-'*100)
                for i in range(len(result)):
                    for k in result[i].values():
                        print(k,'       \t',end ='')
                    print('')
            connection.commit()
        except:
            print('error')
        finally:
            connection.close()

    def f15(self):
        print('Never Say Good BYE~')




def programing():
        num = int(input('select youraction:'))
        a=hw()
        if num == 1:
            a.f1()
        elif num == 2:
            a.f2()
        elif num == 3:
            a.f3()
        elif num == 4:
            a.f4()
        elif num == 5:
            a.f5()
        elif num == 6:
            a.f6()
        elif num == 7:
            a.f7()
        elif num == 8:
            a.f8()
        elif num == 9:
            a.f9()
        elif num == 10:
            a.f10()
        elif num == 11:
            a.f11()
        elif num == 12:
            a.f12()
        elif num == 13:
            a.f13()
        elif num == 14:
            a.f4()
        elif num == 15:
            a.f15()
        else:
            print('try again')

programing()