import sqlite3
def nevvtit(thing):
    print('Должность: ')
    dz = input()
    thing.execute("insert into titles (title) values (:dz)", locals())
    taboles.commit()
def nevvcough(thing):
    print('Кафедра: ')
    dz = input()
    thing.execute("insert into fractions (fraction) values (:dz)", locals())
    taboles.commit()
def nevvtch(thing):
    print('ФИО: ')
    fio = input()
    print('Должность: ', end = '')
    mousey.execute("SELECT * FROM titles")
    rows = mousey.fetchall()
    for row in rows:
        print(row[0], ' - ', row[1], end = ', ')
    print()
    dz = input()
    print('Кафедра: ', end = '')
    mousey.execute("SELECT * FROM fractions")
    rows = mousey.fetchall()
    for row in rows:
        print(row[0], ' - ', row[1], end = ', ')
    print()
    kf = input()
    thing.execute("insert into tchrs (tname, dolz, cough) values (:fio, :dz, :kf)", locals())
    taboles.commit()
def updtit(thing):
    print('ID изменяемой записи: ')
    aidi = input()
    print('Должность: ')
    dz = input()
    thing.execute("update titles set title=:dz where id=:aidi", locals())
    taboles.commit()
def updcough(thing):
    print('ID изменяемой записи: ')
    aidi = input()
    print('Кафедра: ')
    dz = input()
    thing.execute("update fractions set fraction=:dz where id=:aidi", locals())
    taboles.commit()
def updtch(thing):
    print('ID изменяемой записи:: ')
    aidi = input()
    print('ФИО: ')
    fio = input()
    print('Должность: ', end = '')
    mousey.execute("SELECT * FROM titles")
    rows = mousey.fetchall()
    for row in rows:
        print(row[0], ' - ', row[1], end = ', ')
    print()
    dz = input()
    print('Кафедра: ', end = '')
    mousey.execute("SELECT * FROM fractions")
    rows = mousey.fetchall()
    for row in rows:
        print(row[0], ' - ', row[1], end = ', ')
    print()
    kf = input()
    thing.execute("update tchrs set tname=:fio, dolz=:dz, cough=:kf where id=:aidi", locals())
    taboles.commit()
def deltit(thing):
    print('ID удаляемой записи: ')
    aidi = input()
    thing.execute("delete from tchrs where dolz=:aidi", locals())
    thing.execute("delete from titles where id=:aidi", locals())
    taboles.commit()
def delcough(thing):
    print('ID удаляемой записи: ')
    aidi = input()
    thing.execute("delete from tchrs where cough=:aidi", locals())
    thing.execute("delete from fractions where id=:aidi", locals())
    taboles.commit()
def deltch(thing):
    print('ID удаляемой записи: ')
    aidi = input()
    thing.execute("delete from tchrs where id=:aidi", locals())
    taboles.commit()
taboles = sqlite3.connect('teech.db')
mousey = taboles.cursor()
mousey.execute("create table if not exists titles ("
               "id integer primary key autoincrement unique not null, "
               "title text unique not null)")
mousey.execute("create table if not exists fractions ("
               "id integer primary key autoincrement unique not null, "
               "fraction text unique not null)")
mousey.execute("create table if not exists tchrs ("
               "id integer primary key autoincrement unique not null, "
               "tname text unique not null, "
               "dolz integer not null, "
               "cough integer not null, "
               "foreign key (dolz) references titles, "
               "foreign key (cough) references fractions)")
taboles.commit()

fin = ''
witch = ''
while fin != '0':
    print('1 - Добавить запись, 2 - Изменить запись, 3 - Удалить запись, 4 - Вывести таблицу, 5 - Вывести количество преподавателей кафедры, 0 - Закончить работу.')
    fin = input()
    if fin == '1':
        print('Таблица: 1 - Должности, 2 - Кафедры, 3 - Преподаватели')
        witch = input()
        if witch == '1':
            mousey.execute("SELECT * FROM titles")
            rows = mousey.fetchall()
            for row in rows:
                print(row[0], ' | ', row[1])
            print('Добавить запись в таблицу "Должности"')
            nevvtit(mousey)
        elif witch == '2':
            mousey.execute("SELECT * FROM fractions")
            rows = mousey.fetchall()
            for row in rows:
                print(row[0], ' | ', row[1])
            print('Добавить запись в таблицу "Кафедры"')
            nevvcough(mousey)
        elif witch == '3':
            mousey.execute("SELECT tchrs.id as id, tchrs.tname as tname, titles.title as titie, fractions.fraction as frick FROM tchrs join titles on tchrs.dolz = titles.id join fractions on tchrs.cough = fractions.id")
            rows = mousey.fetchall()
            for row in rows:
                print(row[0], ' | ', row[1], ' | ', row[2], ' | ', row[3])
            print('Добавить запись в таблицу "Должности"')
            nevvtch(mousey)
        else:
            print('bruh')
    elif fin == '2':
        print('Таблица: 1 - Должности, 2 - Кафедры, 3 - Преподаватели')
        witch = input()
        if witch == '1':
            mousey.execute("SELECT * FROM titles")
            rows = mousey.fetchall()
            for row in rows:
                print(row[0], ' | ', row[1])
            print('Изменить запись в таблице "Должности"')
            updtit(mousey)
        elif witch == '2':
            mousey.execute("SELECT * FROM fractions")
            rows = mousey.fetchall()
            for row in rows:
                print(row[0], ' | ', row[1])
            print('Изменить запись в таблице "Кафедры"')
            updcough(mousey)
        elif witch == '3':
            mousey.execute("SELECT tchrs.id as id, tchrs.tname as tname, titles.title as titie, fractions.fraction as frick FROM tchrs join titles on tchrs.dolz = titles.id join fractions on tchrs.cough = fractions.id")
            rows = mousey.fetchall()
            for row in rows:
                print(row[0], ' | ', row[1], ' | ', row[2], ' | ', row[3])
            print('Изменить запись в таблице "Должности"')
            updtch(mousey)
        else:
            print('bruh')
    elif fin == '3':
        print('Таблица: 1 - Должности, 2 - Кафедры, 3 - Преподаватели')
        witch = input()
        if witch == '1':
            mousey.execute("SELECT * FROM titles")
            rows = mousey.fetchall()
            for row in rows:
                print(row[0], ' | ', row[1])
            print('Удалить запись из таблицы "Должности"')
            deltit(mousey)
        elif witch == '2':
            mousey.execute("SELECT * FROM fractions")
            rows = mousey.fetchall()
            for row in rows:
                print(row[0], ' | ', row[1])
            print('Удалить запись из таблицы "Кафедры"')
            delcough(mousey)
        elif witch == '3':
            mousey.execute("SELECT tchrs.id as id, tchrs.tname as tname, titles.title as titie, fractions.fraction as frick FROM tchrs join titles on tchrs.dolz = titles.id join fractions on tchrs.cough = fractions.id")
            rows = mousey.fetchall()
            for row in rows:
                print(row[0], ' | ', row[1], ' | ', row[2], ' | ', row[3])
            print('Удалить запись из таблицы "Должности"')
            deltch(mousey)
        else:
            print('bruh')
    elif fin == '4':
        print('Таблица: 1 - Должности, 2 - Кафедры, 3 - Преподаватели')
        witch = input()
        if witch == '1':
            print('Таблица "Должности"')
            mousey.execute("SELECT * FROM titles")
            rows = mousey.fetchall()
            for row in rows:
                print(row[0], ' | ', row[1])
        elif witch == '2':
            print('Таблица "Кафедры"')
            mousey.execute("SELECT * FROM fractions")
            rows = mousey.fetchall()
            for row in rows:
                print(row[0], ' | ', row[1])
        elif witch == '3':
            print('Таблица "Преподаватели"')
            mousey.execute("SELECT tchrs.id as id, tchrs.tname as tname, titles.title as titie, fractions.fraction as frick FROM tchrs join titles on tchrs.dolz = titles.id join fractions on tchrs.cough = fractions.id")
            rows = mousey.fetchall()
            for row in rows:
                print(row[0], ' | ', row[1], ' | ', row[2], ' | ', row[3])
        else:
            print('bruh')
    elif fin == '5':
        print('Кафедра: ', end = '')
        mousey.execute("SELECT * FROM fractions")
        rows = mousey.fetchall()
        for row in rows:
            print(row[0], ' - ', row[1], end = ', ')
        print()
        kf = input()
        mousey.execute("select count(*) from tchrs where cough = :kf", locals())
        print(str(mousey.fetchall())[2], ' преподавателей')
    else:
        print('bruh')
    print()