import pypyodbc
mySQLServer = "DESKTOP-3F2B3B3\SQLEXPRESS"
myDataBase = "DfLessonDb"
itstrue = ('да', 'Да', 'ДА', 'уверен', 'Уверен', 'Верны', 'верны', 'ага', 'Ага', 'Верно', 'верно')
import telebot
from telebot import types
bot = telebot.TeleBot('909191584:AAFMYcMImK84IBchpY1NiGQ3bLeUlpYRUYg')
name = ''
surname = ''
nameadmin = ''
telegramid = 0
id = 0
course = ''
grade = 0
id_stud = 0
attendance = 0




@bot.message_handler(commands=['start'])
def send_welcome(message):
    user_id = str(message.from_user.id)
    bot.send_message(message.from_user.id, '''Привет, я бот-журнал для списка команд введи /help\nВаш telegram id:''' + user_id)


@bot.message_handler(commands=['help'])
def send_welcome(message):
    bot.send_message(message.from_user.id, "/reg - Начать регистрацию пользователя\n/infoaboutme - получить "
                                           "информацию о себе\n/leavethecours - покинуть курс\n/regadmin - стать администратором курса")


@bot.message_handler(commands=['reg'])
def start_register(message):
    bot.send_message(message.from_user.id, 'Введите свое имя')
    bot.register_next_step_handler(message, get_name)


@bot.message_handler(commands=['infoaboutme'])
def get_info(message):
    connection = pypyodbc.connect('Driver={SQL Server};'
                                  'Server=' + mySQLServer + ';'
                                                            'Database=' + myDataBase + ';')
    print(message)
    cursor = connection.cursor()
    telegramid = int(message.from_user.id)
    mySQLQuery = "/select namestud, surnamestud, studid from Students where telegramid={student[0]!r}".format(student=[telegramid])
    cursor.execute(mySQLQuery[1:])
    #
    #
    #
    results = str(cursor.fetchall())[2:-2].replace("'", '').replace(',', '', 1).replace(',', '\nНомер студенческого:',1).replace(')', '').split(',')
    print(results)
    if not results:
        bot.send_message(message.from_user.id, 'Вы не зарегистрированны в системе!')
    else:
        mySQLQuery2 = "/select course from Students where telegramid={student[0]!r}".format(student=[telegramid])
        cursor.execute(mySQLQuery2[1:])
        results2 ='Ваши курсы: ' + str(cursor.fetchall())[2:-2].replace("'", '').replace(',', '', 1).replace('(', '').replace(')', '')
        bot.send_message(message.from_user.id, results)
        bot.send_message(message.from_user.id, results2)
    connection.close()









@bot.message_handler(commands=['leavethecours'])
def leave_cours(message):
    bot.send_message(message.from_user.id, 'Какой курс вы хотите покинуть?')
    bot.register_next_step_handler(message, get_answer_leave1)

def get_answer_leave1(message):
    global course
    course = message.text
    bot.send_message(message.from_user.id, 'Вы уверены?')
    bot.register_next_step_handler(message, get_answer_leave2)
def get_answer_leave2(message):
    if str(message.text) in itstrue:
        bot.send_message(message.from_user.id, 'До скорых встреч!')
        connection = pypyodbc.connect('Driver={SQL Server};'
                                      'Server=' + mySQLServer + ';'
                                                                'Database=' + myDataBase + ';')

        telegramid = int(message.from_user.id)
        cursor = connection.cursor()
        mySQLQuery = "/delete from Students where telegramid={student[0]!r} and course ={student[1]!r}".format(student=[telegramid, course])
        cursor.execute(mySQLQuery[1:])
        connection.commit()
        connection.close()
    else:
        bot.send_message(message.from_user.id, 'Не играй с огнем)')









def get_name(message):
    global name
    name = message.text
    bot.send_message(message.from_user.id, 'Введите свою фамилию')
    bot.register_next_step_handler(message, get_surname)


def get_surname(message):
    global surname
    surname = message.text
    bot.send_message(message.from_user.id, 'Введите номер своего студенческого билета')
    bot.register_next_step_handler(message, get_course)

def get_course(message):
    global id
    id = message.text
    bot.send_message(message.from_user.id, 'Введите название курса\n*Матстат\n*Питон\n*Матанализ\n*БД')
    bot.register_next_step_handler(message, get_id)

def get_id(message):
    global course
    course = message.text
    bot.send_message(message.from_user.id,
                     'Данные верны?\nИмя: ' + name + '\n' + 'Фамилия: ' + surname + '\n' + 'Номер суденческого: ' + id + '\n' + 'Курс: ' + course)
    bot.register_next_step_handler(message, get_answer_reg)

def get_answer_reg(message):
    connection = pypyodbc.connect('Driver={SQL Server};'
                                  'Server=' + mySQLServer + ';'
                                                            'Database=' + myDataBase + ';')
    cursor = connection.cursor()
    telegramid = int(message.from_user.id)
    mySQLQuery2 = "/select course from Students where telegramid={student[0]!r}".format(
        student=[telegramid])
    cursor.execute(mySQLQuery2[1:])
    results2 = str(cursor.fetchall())[2:-2].replace("'", '').replace(',', '').replace('(', '').replace(')', '')
    Courses = results2.split()
    if course in Courses:
        boolean = 0
    else:
        boolean = 1
    if boolean:
        if str(message.text) in itstrue:
            bot.send_message(message.from_user.id, 'Успех!')
            connection = pypyodbc.connect('Driver={SQL Server};'
                                          'Server=' + mySQLServer + ';'
                                                                    'Database=' + myDataBase + ';')
            cursor = connection.cursor()
            mySQLQuery = "/insert into Students (namestud, surnamestud, studid, telegramid, course) values ({student[0]!r}, {student[1]!r}, " \
                         "{student[2]!r}, {student[3]!r}, {student[4]!r})".format(
                student=[name, surname, id, telegramid, course])
            cursor.execute(mySQLQuery[1:])
            connection.commit()
            connection.close()
        else:
            bot.send_message(message.from_user.id, 'Попробуйте снова')
    else:
        bot.send_message(message.from_user.id, 'Вы уже зарегистрированны!')




@bot.message_handler(commands=['regadmin'])
def reg_admin(message):
    bot.send_message(message.from_user.id, 'Введите пароль от курса?')
    bot.register_next_step_handler(message, reg_admin_2)

def reg_admin_2(message):
    password = str(message.text)
    print(message.text)
    connection = pypyodbc.connect('Driver={SQL Server};'
                                  'Server=' + mySQLServer + ';'
                                                            'Database=' + myDataBase + ';')
    cursor = connection.cursor()
    telegramid = int(message.from_user.id)
    mySQLQuery = "/select course_c from Courses where pass={student[0]!r}".format(student=[password])
    cursor.execute(mySQLQuery[1:])
    results = str(cursor.fetchall())[2:-2].replace("'", '').replace(',','')
    if not results:
        bot.send_message(message.from_user.id, 'Вы ввели неверный пароль!')
    else:
        bot.send_message(message.from_user.id, 'Успех! Вы администратор курса ' + results)
        connection = pypyodbc.connect('Driver={SQL Server};'
                                      'Server=' + mySQLServer + ';'
                                                                'Database=' + myDataBase + ';')
        cursor = connection.cursor()
        mySQLQuery2 = "/insert into Teachers (telegramid_t,course_t) values({student[0]!r}, {student[1]!r})".format(
            student=[telegramid, results])
        cursor.execute(mySQLQuery2[1:])
        connection.commit()
        connection.close()





@bot.message_handler(commands=['torate'])
def to_rate(message):
    bot.send_message(message.from_user.id, 'Кому поставить оценку (выберите номер)?')
    connection = pypyodbc.connect('Driver={SQL Server};'
                                  'Server=' + mySQLServer + ';'
                                                            'Database=' + myDataBase + ';')
    cursor = connection.cursor()
    mySQLQuery = "/select id, surnamestud from Students"
    cursor.execute(mySQLQuery[1:])
    results = str(cursor.fetchall())[1:-1].replace('(', '\n').replace(')','').replace("'", '').replace(',','')
    connection.close()
    bot.send_message(message.from_user.id, results)
    bot.register_next_step_handler(message, to_rate_2)


def to_rate_2(message):
    bot.send_message(message.from_user.id, 'Какую оценку ставим?')
    global id_stud
    id_stud = int(message.text)
    bot.register_next_step_handler(message, to_rate_3)



def to_rate_3(message):
    bot.send_message(message.from_user.id, 'Присутствовал на паре (1/0)?')
    global grade
    grade = int(message.text)
    bot.register_next_step_handler(message, to_rate_4)


def to_rate_4(message):
    bot.send_message(message.from_user.id, 'Выставили!')
    global attendance
    attendance = int(message.text)
    telegramid = int(message.from_user.id)


    connection = pypyodbc.connect('Driver={SQL Server};'
                                  'Server=' + mySQLServer + ';'
                                                            'Database=' + myDataBase + ';')
    cursor = connection.cursor()
    telegramid = int(message.from_user.id)
    mySQLQuery = "/select course_t from Teachers where telegramid_t={student[0]!r}".format(
        student=[telegramid])
    cursor.execute(mySQLQuery[1:])
    results = str(cursor.fetchall())[2:-2].replace("'", '').replace(',', '')
    if not results:
        bot.send_message(message.from_user.id, 'Вы не администратор!')
    else:
        mySQLQuery2 = "/insert into Grades (id_g, course_g, grade, attendance)  values({student[0]!r}, {student[" \
                      "1]!r}, {student[2]!r}, {student[3]!r})".format(
            student=[id_stud, results, grade, attendance])
        cursor.execute(mySQLQuery2[1:])
        connection.commit()
    connection.close()




bot.polling()