#! /usr/bin/env python
# -*- coding: cp866 -*-

import sqlite3

conn = sqlite3.connect('test.db', isolation_level=None)
cur = conn.cursor()

try:
    cur.execute('create table courses (cid integer primary key autoincrement, \
                                        name_course text, q_qwetions integer)')
    cur.execute('create table qwetions (cid integer, qid integer primary key autoincrement, \
                                        qwetion text, apearence integer, q_answers integer)')
    cur.execute('create table answers (cid integer, qid integer, aid integer primary key autoincrement, \
                                        answer text, grade integer)')
except sqlite3.Error, e:
    print e
    
x = 0
while x != 5:
    x = int(raw_input("""Выберите пункт меню:
    1. Создать базу
    2. Редактировать базу
    3. Вывести базу на экран
    4. Удалить базу
    5. Выход\n"""))
    
    if x == 1:
        y = 0
        while y != 4:
            y = int(raw_input("""
1. Создать базу
    Выберите пункт подменю:
    1 - курс,
    2 - вопрос,
    3 - ответ
    4 - вернутся в главное меню\n"""))
            if y == 1:
                name_course = raw_input('Введите название курса\n')
                q_qwetions = int(raw_input('Введите количество вопросов\n'))
                cur.execute('insert into courses values(NULL, "%s", "%d" )' % \
                            (name_course, q_qwetions))
           
            elif y == 2:
                cur.execute('select * from courses')    
                for row in cur:
                    print row
                
                cid = int(raw_input('Выберите номер курса\n')) 

                d = cur.execute('select q_qwetions from courses where cid = %d' %cid).fetchone()[0]
                for i in range(d):
                    qwetion = raw_input('Введите вопрос\n')
                    apearence = int(raw_input('Введите номер представления вопроса\n'))
                    q_answers = int(raw_input('Введите количество ответов\n'))
                    cur.execute('insert into qwetions values("%d", NULL, "%s", "%d", "%d" )' % \
                            (cid, qwetion, apearence, q_answers))
           
            elif y == 3:
                cur.execute('select * from courses')    
                for row in cur:
                    print row
                                
                cid = int(raw_input('Выберите номер курса\n')) 
                
                cur.execute('select * from qwetions where cid = %d' %cid)
                for row in cur:
                    print row
                    
                qid = int(raw_input('Выберите номер вопроса для работы\n'))
                
                d = cur.execute('select q_answers from qwetions where qid = %d' %qid).fetchone()[0]
                for i in range(d):
                    answer = raw_input('Введите ответ\n')
                    grade = int(raw_input('Введите балл за этот ответ\n'))
                    cur.execute('insert into answers values("%d", "%d", NULL, "%s", "%d")' % \
                            (cid, qid, answer, grade))

    elif x == 2:
        y = 0
        while y != 4:
            y = int(raw_input("""
1. Редактировать базу
    Выберите пункт подменю:
    1 - курс,
    2 - вопрос,
    3 - ответ
    4 - вернутся в главное меню\n"""))
                                        
            if y == 1:
                cur.execute('select * from courses')    
                for row in cur:
                    print row
                    
                cid = int(raw_input('Выберите номер курса\n'))
                cur.execute('select * from courses where cid = %d' %cid)
                print cur.execute('select * from courses where cid = %d' %cid).fetchone()
                
                a = int(raw_input("Какой столбец вы хотите исправить?\n"))
                if a == 2:
                    print cur.execute('select name_course from courses where cid = %d' %cid).fetchone()
                    b = raw_input("На что хотите заменить?\n")             
                    cur.execute('UPDATE courses SET name_course = "%s" where cid = %d' % (b, cid))

                elif a == 3:
                    print cur.execute('select q_qwetions from courses where cid = %d' %cid).fetchone()
                    b = int(raw_input("На что хотите заменить?\n"))             
                    cur.execute('UPDATE courses SET q_qwetions = "%d" where cid = %d' % (b, cid))

            elif y == 2:
                cur.execute('select * from courses')    
                for row in cur:
                    print row
                    
                cid = int(raw_input('Выберите номер курса\n'))

                cur.execute('select * from qwetions where cid = %d' %cid)
                for row in cur:
                    print row

                qid = int(raw_input('Выберите номер вопроса\n'))
                cur.execute('select * from qwetions where qid = %d' %qid)
                print cur.execute('select * from qwetions where qid = %d' %qid).fetchone()

                a = int(raw_input("Какой столбец вы хотите исправить?\n"))

                if a == 3:
                    print cur.execute('select qwetion from qwetions where qid = %d' %qid).fetchone()
                    b = raw_input("На что хотите заменить?\n")             
                    cur.execute('UPDATE qwetions SET qwetion = "%s" where qid = %d' % (b, qid))

                elif a == 4:
                    print cur.execute('select apearence from qwetions where qid = %d' %qid).fetchone()
                    b = int(raw_input("На что хотите заменить?\n"))
                    cur.execute('UPDATE qwetions SET apearence = "%d" where qid = %d' % (b, qid))

                elif a == 5:
                    print cur.execute('select q_answers from qwetions where qid = %d' %qid).fetchone()
                    b = int(raw_input("На что хотите заменить?\n"))
                    cur.execute('UPDATE qwetions SET q_answers = "%d" where qid = %d' % (b, qid))
                             
            elif y == 3:
                cur.execute('select * from courses')    
                for row in cur:
                    print row
                                
                cid = int(raw_input('Выберите номер курса\n')) 
                
                cur.execute('select * from qwetions where cid = %d' %cid)
                for row in cur:
                    print row
                    
                qid = int(raw_input('Выберите номер вопроса\n'))
                
                cur.execute('select * from answers where qid = %d' %qid)
                for row in cur:
                    print row

                aid = int(raw_input('Выберите номер ответа\n'))
                cur.execute('select * from answers where aid = %d' %aid)
                print cur.execute('select * from answers where qid = %d' %qid).fetchone()

                a = int(raw_input("Какой столбец вы хотите исправить?\n"))

                if a == 4:
                    print cur.execute('select answer from answers where aid = %d' %aid).fetchone()
                    b = raw_input("На что хотите заменить?\n")             
                    cur.execute('UPDATE answers SET answer = "%s" where aid = %d' % (b, aid))

                elif a == 5:
                    print cur.execute('select grade from answers where aid = %d' %aid).fetchone()
                    b = int(raw_input("На что хотите заменить?\n"))
                    cur.execute('UPDATE answers SET grade = "%d" where aid = %d' % (b, aid))
               
    elif x == 3:
        y = 0
        while y != 4:
            y = int(raw_input("""
1. Выводить базу на экран
    Выберите пункт подменю:
    1 - курс,
    2 - вопрос,
    3 - ответ
    4 - вернутся в главное меню\n"""))
            if y == 1:
                cur.execute('select * from courses')    
                for row in cur:
                    print row
                                          
            elif y == 2:
                cur.execute('select * from courses')    
                for row in cur:
                    print row
                    
                cid = int(raw_input('Выберите номер курса\n'))
                
                cur.execute('select * from qwetions where cid = %d' %cid)
                for row in cur:
                    print row
                
            elif y == 3:
                cur.execute('select * from courses')    
                for row in cur:
                    print row
                                
                cid = int(raw_input('Выберите номер курса\n')) 
                
                cur.execute('select * from qwetions where cid = %d' %cid)
                for row in cur:
                    print row
                    
                qid = int(raw_input('Выберите номер вопроса\n'))
                
                cur.execute('select * from answers where qid = %d' %qid)
                for row in cur:
                    print row
                
    elif x == 4:
        y = 0
        while y != 4:
            y = int(raw_input("""
1. Удалить базу
    Выберите пункт подменю:
    1 - курс,
    2 - вопрос,
    3 - ответ
    4 - вернутся в главное меню\n"""))
            if y == 1:
                cur.execute('select * from courses')    
                for row in cur:
                    print row
                cid = int(raw_input('Выберите номер курса\n'))    
                cur.execute('delete from courses where cid = %d' %cid)
                cur.execute('delete from qwetions where cid = %d' %cid)
                cur.execute('delete from answers where cid = %d' %cid)
           
            elif y == 2:
                cur.execute('select * from courses')    
                for row in cur:
                    print row
                    
                cid = int(raw_input('Выберите номер курса\n'))
                
                cur.execute('select * from qwetions where cid = %d' %cid)
                for row in cur:
                    print row
                    
                qid = int(raw_input('Выберите номер вопроса\n')) 
                
                cur.execute('delete from qwetions values where qid = %d' %qid)
                cur.execute('delete from answers values where qid = %d' %qid)
           
            elif y == 3:
                cur.execute('select * from courses')    
                for row in cur:
                    print row
                                
                cid = int(raw_input('Выберите номер курса\n')) 
                
                cur.execute('select * from qwetions where cid = %d' %cid)
                for row in cur:
                    print row
                    
                qid = int(raw_input('Выберите номер вопроса для дальнейшей работы\n'))
                
                cur.execute('select * from answers where qid = %d' %qid)
                for row in cur:
                    print row
                
                aid = int(raw_input('Выберите номер ответа для удаления\n'))
               
                cur.execute('delete from answers values where aid = %d' %aid)
                                   
cur.close()
conn.close()