main.py - master - Askoline/EnglishCard

main.py
1 003 строки24 KB
Ponomarev Viktor
rework main.py
27 июл 2026, 20:24
27 июл 2026, 20:240f973a4
"""

EnglishCard - Приложение для изучения английского языка

"""

import streamlit as st
import psycopg2
import pandas as pd
import random


st.set_page_config(
    page_title="EnglishCard - Изучение английского",
    page_icon="📚",
    layout="wide"
)


def get_db_connection():
    connection = psycopg2.connect(
        host="localhost",
        database="english_card",
        user="postgres",
        password="postgres"
    )

    return connection


def init_database():
    connection = get_db_connection()
    cursor = connection.cursor()

    cursor.execute(
        """
        CREATE TABLE IF NOT EXISTS users (
            id SERIAL PRIMARY KEY,
            username VARCHAR(100) NOT NULL UNIQUE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );
        """
    )

    cursor.execute(
        """
        CREATE TABLE IF NOT EXISTS common_words (
            id SERIAL PRIMARY KEY,
            russian_word VARCHAR(100) NOT NULL,
            english_word VARCHAR(100) NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE (russian_word, english_word)
        );
        """
    )

    cursor.execute(
        """
        CREATE TABLE IF NOT EXISTS user_words (
            id SERIAL PRIMARY KEY,
            user_id INTEGER NOT NULL,
            russian_word VARCHAR(100) NOT NULL,
            english_word VARCHAR(100) NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE (user_id, russian_word, english_word),
            FOREIGN KEY (user_id)
                REFERENCES users(id)
                ON DELETE CASCADE
        );
        """
    )

    cursor.execute(
        """
        CREATE TABLE IF NOT EXISTS learning_stats (
            id SERIAL PRIMARY KEY,
            user_id INTEGER NOT NULL,
            word_id INTEGER NOT NULL,
            word_type VARCHAR(20) NOT NULL,
            correct_answers INTEGER DEFAULT 0,
            total_attempts INTEGER DEFAULT 0,
            last_reviewed TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE (user_id, word_id, word_type),
            FOREIGN KEY (user_id)
                REFERENCES users(id)
                ON DELETE CASCADE
        );
        """
    )

    common_words = [
        ("красный", "red"),
        ("синий", "blue"),
        ("зелёный", "green"),
        ("жёлтый", "yellow"),
        ("чёрный", "black"),
        ("белый", "white"),
        ("я", "I"),
        ("ты", "you"),
        ("он", "he"),
        ("она", "she"),
        ("дом", "house"),
        ("книга", "book")
    ]

    for word in common_words:
        russian_word = word[0]
        english_word = word[1]

        cursor.execute(
            """
            SELECT id
            FROM common_words
            WHERE russian_word = %s
              AND english_word = %s;
            """,
            (russian_word, english_word)
        )

        existing_word = cursor.fetchone()

        if existing_word is None:
            cursor.execute(
                """
                INSERT INTO common_words (
                    russian_word,
                    english_word
                )
                VALUES (%s, %s);
                """,
                (russian_word, english_word)
            )

    connection.commit()
    cursor.close()
    connection.close()


def login_user(username):
    username = username.strip()

    if username == "":
        return None

    connection = get_db_connection()
    cursor = connection.cursor()

    cursor.execute(
        """
        SELECT id
        FROM users
        WHERE username = %s;
        """,
        (username,)
    )

    user = cursor.fetchone()

    if user is not None:
        user_id = user[0]
    else:
        cursor.execute(
            """
            INSERT INTO users (username)
            VALUES (%s)
            RETURNING id;
            """,
            (username,)
        )

        user = cursor.fetchone()
        user_id = user[0]
        connection.commit()

    cursor.close()
    connection.close()

    return user_id


def get_user_words(user_id):
    connection = get_db_connection()
    cursor = connection.cursor()

    cursor.execute(
        """
        SELECT
            id,
            russian_word,
            english_word
        FROM common_words
        ORDER BY id;
        """
    )

    common_words_from_database = cursor.fetchall()

    cursor.execute(
        """
        SELECT
            id,
            russian_word,
            english_word
        FROM user_words
        WHERE user_id = %s
        ORDER BY id;
        """,
        (user_id,)
    )

    personal_words_from_database = cursor.fetchall()

    cursor.close()
    connection.close()

    words = []

    for word in common_words_from_database:
        word_data = {
            "id": word[0],
            "russian_word": word[1],
            "english_word": word[2],
            "word_type": "common"
        }

        words.append(word_data)

    for word in personal_words_from_database:
        word_data = {
            "id": word[0],
            "russian_word": word[1],
            "english_word": word[2],
            "word_type": "personal"
        }

        words.append(word_data)

    return words


def add_personal_word(user_id, russian_word, english_word):
    russian_word = russian_word.strip().lower()
    english_word = english_word.strip().lower()

    if russian_word == "" or english_word == "":
        return False

    connection = get_db_connection()
    cursor = connection.cursor()

    cursor.execute(
        """
        SELECT id
        FROM user_words
        WHERE user_id = %s
          AND russian_word = %s
          AND english_word = %s;
        """,
        (user_id, russian_word, english_word)
    )

    existing_personal_word = cursor.fetchone()

    cursor.execute(
        """
        SELECT id
        FROM common_words
        WHERE LOWER(russian_word) = %s
          AND LOWER(english_word) = %s;
        """,
        (russian_word, english_word)
    )

    existing_common_word = cursor.fetchone()

    if existing_personal_word is not None:
        cursor.close()
        connection.close()
        return False

    if existing_common_word is not None:
        cursor.close()
        connection.close()
        return False

    cursor.execute(
        """
        INSERT INTO user_words (
            user_id,
            russian_word,
            english_word
        )
        VALUES (%s, %s, %s);
        """,
        (user_id, russian_word, english_word)
    )

    connection.commit()
    cursor.close()
    connection.close()

    return True


def delete_personal_word(user_id, word_id):
    connection = get_db_connection()
    cursor = connection.cursor()

    cursor.execute(
        """
        DELETE FROM learning_stats
        WHERE user_id = %s
          AND word_id = %s
          AND word_type = 'personal';
        """,
        (user_id, word_id)
    )

    cursor.execute(
        """
        DELETE FROM user_words
        WHERE id = %s
          AND user_id = %s;
        """,
        (word_id, user_id)
    )

    deleted_rows = cursor.rowcount

    connection.commit()
    cursor.close()
    connection.close()

    if deleted_rows > 0:
        return True

    return False


def update_stats(user_id, word_id, word_type, is_correct):
    connection = get_db_connection()
    cursor = connection.cursor()

    cursor.execute(
        """
        SELECT
            id,
            correct_answers,
            total_attempts
        FROM learning_stats
        WHERE user_id = %s
          AND word_id = %s
          AND word_type = %s;
        """,
        (user_id, word_id, word_type)
    )

    statistic = cursor.fetchone()

    if statistic is None:
        correct_answers = 0

        if is_correct:
            correct_answers = 1

        cursor.execute(
            """
            INSERT INTO learning_stats (
                user_id,
                word_id,
                word_type,
                correct_answers,
                total_attempts,
                last_reviewed
            )
            VALUES (%s, %s, %s, %s, 1, CURRENT_TIMESTAMP);
            """,
            (
                user_id,
                word_id,
                word_type,
                correct_answers
            )
        )
    else:
        statistic_id = statistic[0]
        correct_answers = statistic[1]
        total_attempts = statistic[2]

        total_attempts = total_attempts + 1

        if is_correct:
            correct_answers = correct_answers + 1

        cursor.execute(
            """
            UPDATE learning_stats
            SET correct_answers = %s,
                total_attempts = %s,
                last_reviewed = CURRENT_TIMESTAMP
            WHERE id = %s;
            """,
            (
                correct_answers,
                total_attempts,
                statistic_id
            )
        )

    connection.commit()
    cursor.close()
    connection.close()


def get_statistics(user_id):
    connection = get_db_connection()
    cursor = connection.cursor()

    cursor.execute(
        """
        SELECT
            learning_stats.word_id,
            learning_stats.word_type,
            learning_stats.correct_answers,
            learning_stats.total_attempts,
            learning_stats.last_reviewed,
            common_words.russian_word,
            common_words.english_word,
            user_words.russian_word,
            user_words.english_word
        FROM learning_stats
        LEFT JOIN common_words
            ON learning_stats.word_type = 'common'
            AND learning_stats.word_id = common_words.id
        LEFT JOIN user_words
            ON learning_stats.word_type = 'personal'
            AND learning_stats.word_id = user_words.id
        WHERE learning_stats.user_id = %s
        ORDER BY learning_stats.last_reviewed DESC;
        """,
        (user_id,)
    )

    statistics_from_database = cursor.fetchall()

    cursor.close()
    connection.close()

    studied_words = len(statistics_from_database)
    correct_answers = 0
    total_attempts = 0
    history = []

    for statistic in statistics_from_database:
        word_type = statistic[1]
        word_correct_answers = statistic[2]
        word_total_attempts = statistic[3]
        last_reviewed = statistic[4]

        correct_answers = correct_answers + word_correct_answers
        total_attempts = total_attempts + word_total_attempts

        if word_type == "common":
            russian_word = statistic[5]
            english_word = statistic[6]
            displayed_word_type = "Общее"
        else:
            russian_word = statistic[7]
            english_word = statistic[8]
            displayed_word_type = "Персональное"

        if russian_word is None:
            russian_word = "Слово удалено"

        if english_word is None:
            english_word = "Слово удалено"

        history_item = {
            "Русское слово": russian_word,
            "Английское слово": english_word,
            "Тип слова": displayed_word_type,
            "Правильные ответы": word_correct_answers,
            "Всего попыток": word_total_attempts,
            "Последняя попытка": last_reviewed
        }

        history.append(history_item)

    accuracy = 0

    if total_attempts > 0:
        accuracy = correct_answers / total_attempts * 100

    statistics = {
        "studied_words": studied_words,
        "correct_answers": correct_answers,
        "total_attempts": total_attempts,
        "accuracy": accuracy,
        "history": history
    }

    return statistics


def generate_options(correct_word, all_words):
    wrong_words = []

    for word in all_words:
        english_word = word["english_word"]

        if english_word != correct_word:
            if english_word not in wrong_words:
                wrong_words.append(english_word)

    additional_words = [
        "table",
        "window",
        "water",
        "friend",
        "school",
        "family",
        "street",
        "morning"
    ]

    for word in additional_words:
        if word != correct_word:
            if word not in wrong_words:
                wrong_words.append(word)

    selected_wrong_words = random.sample(wrong_words, 3)

    options = []

    for word in selected_wrong_words:
        options.append(word)

    options.append(correct_word)
    random.shuffle(options)

    return options


def clear_quiz():
    if "current_word" in st.session_state:
        del st.session_state.current_word

    if "quiz_options" in st.session_state:
        del st.session_state.quiz_options

    if "answer_message" in st.session_state:
        del st.session_state.answer_message

    if "answer_is_correct" in st.session_state:
        del st.session_state.answer_is_correct


def render_sidebar():
    st.sidebar.header("Пользователь")

    if st.session_state.user_id is None:
        username = st.sidebar.text_input("Введите ваше имя")
        login_button = st.sidebar.button("Войти")

        if login_button:
            user_id = login_user(username)

            if user_id is None:
                st.sidebar.warning("Введите имя пользователя.")
            else:
                st.session_state.user_id = user_id
                st.session_state.username = username.strip()
                clear_quiz()
                st.rerun()
    else:
        st.sidebar.success(
            "Здравствуйте, "
            + st.session_state.username
            + "!"
        )

        logout_button = st.sidebar.button("Выйти")

        if logout_button:
            st.session_state.user_id = None
            st.session_state.username = None
            clear_quiz()
            st.rerun()


def select_new_word(words):
    previous_word = None

    if "current_word" in st.session_state:
        previous_word = st.session_state.current_word

    available_words = []

    for word in words:
        add_word = True

        if previous_word is not None:
            same_id = word["id"] == previous_word["id"]
            same_type = (
                word["word_type"] == previous_word["word_type"]
            )

            if same_id and same_type:
                add_word = False

        if add_word:
            available_words.append(word)

    if len(available_words) == 0:
        available_words = words

    current_word = random.choice(available_words)

    options = generate_options(
        current_word["english_word"],
        words
    )

    st.session_state.current_word = current_word
    st.session_state.quiz_options = options
    st.session_state.answer_message = ""
    st.session_state.answer_is_correct = False


def render_study_tab(words):
    st.subheader("Изучение слов")

    if len(words) == 0:
        st.info("В словаре пока нет слов.")
        return

    if "current_word" not in st.session_state:
        select_new_word(words)

    current_word = st.session_state.current_word
    current_word_exists = False

    for word in words:
        same_id = word["id"] == current_word["id"]
        same_type = (
            word["word_type"] == current_word["word_type"]
        )

        if same_id and same_type:
            current_word_exists = True

    if not current_word_exists:
        select_new_word(words)

    current_word = st.session_state.current_word
    options = st.session_state.quiz_options

    st.write("Выберите правильный перевод слова:")
    st.header(current_word["russian_word"])

    column_1, column_2 = st.columns(2)

    for number in range(len(options)):
        option = options[number]

        if number % 2 == 0:
            selected_column = column_1
        else:
            selected_column = column_2

        with selected_column:
            answer_button = st.button(
                option,
                key=(
                    "answer_"
                    + str(current_word["id"])
                    + "_"
                    + current_word["word_type"]
                    + "_"
                    + str(number)
                ),
                disabled=st.session_state.answer_is_correct,
                use_container_width=True
            )

        if answer_button:
            if option == current_word["english_word"]:
                update_stats(
                    st.session_state.user_id,
                    current_word["id"],
                    current_word["word_type"],
                    True
                )

                st.session_state.answer_message = (
                    "Правильный ответ!"
                )
                st.session_state.answer_is_correct = True
            else:
                update_stats(
                    st.session_state.user_id,
                    current_word["id"],
                    current_word["word_type"],
                    False
                )

                st.session_state.answer_message = (
                    "Неправильный ответ. Попробуйте ещё раз."
                )
                st.session_state.answer_is_correct = False

            st.rerun()

    if st.session_state.answer_message != "":
        if st.session_state.answer_is_correct:
            st.success(st.session_state.answer_message)
        else:
            st.error(st.session_state.answer_message)

    if st.session_state.answer_is_correct:
        next_word_button = st.button(
            "Следующее слово",
            type="primary"
        )

        if next_word_button:
            select_new_word(words)
            st.rerun()


def render_add_word_tab():
    st.subheader("Добавление нового слова")

    russian_word = st.text_input(
        "Введите слово на русском языке",
        key="new_russian_word"
    )

    english_word = st.text_input(
        "Введите перевод на английском языке",
        key="new_english_word"
    )

    add_word_button = st.button("Добавить слово")

    if add_word_button:
        word_added = add_personal_word(
            st.session_state.user_id,
            russian_word,
            english_word
        )

        if word_added:
            clear_quiz()

            words = get_user_words(
                st.session_state.user_id
            )

            st.success(
                "Слово успешно добавлено. "
                "Количество изучаемых слов: "
                + str(len(words))
            )
        else:
            st.warning(
                "Заполните оба поля или проверьте, "
                "не было ли это слово добавлено ранее."
            )


def render_delete_word_tab(words):
    st.subheader("Удаление слова")

    personal_words = []

    for word in words:
        if word["word_type"] == "personal":
            personal_words.append(word)

    if len(personal_words) == 0:
        st.info("У вас пока нет персональных слов.")
        return

    word_names = []

    for word in personal_words:
        word_name = (
            word["russian_word"]
            + " — "
            + word["english_word"]
        )

        word_names.append(word_name)

    selected_word_name = st.selectbox(
        "Выберите слово для удаления",
        word_names
    )

    selected_word = None

    for word in personal_words:
        word_name = (
            word["russian_word"]
            + " — "
            + word["english_word"]
        )

        if word_name == selected_word_name:
            selected_word = word

    delete_button = st.button("Удалить слово")

    if delete_button:
        word_deleted = delete_personal_word(
            st.session_state.user_id,
            selected_word["id"]
        )

        if word_deleted:
            clear_quiz()
            st.success("Слово успешно удалено.")
            st.rerun()
        else:
            st.error("Не удалось удалить слово.")


def render_statistics_tab(user_id):
    st.subheader("Статистика изучения")

    statistics = get_statistics(user_id)

    column_1, column_2, column_3, column_4 = st.columns(4)

    column_1.metric(
        "Изучено слов",
        statistics["studied_words"]
    )

    column_2.metric(
        "Всего попыток",
        statistics["total_attempts"]
    )

    column_3.metric(
        "Правильных ответов",
        statistics["correct_answers"]
    )

    accuracy_text = (
        str(round(statistics["accuracy"], 1))
        + "%"
    )

    column_4.metric(
        "Точность",
        accuracy_text
    )

    if len(statistics["history"]) > 0:
        dataframe = pd.DataFrame(
            statistics["history"]
        )

        st.dataframe(
            dataframe,
            use_container_width=True,
            hide_index=True
        )
    else:
        st.info("Статистика пока отсутствует.")


def render_schema():
    st.subheader("Схема базы данных")

    schema = """
    digraph {
        rankdir=LR;
        node [shape=record];

        users [
            label="{users|id PK|username|created_at}"
        ];

        common_words [
            label="{common_words|id PK|russian_word|english_word|created_at}"
        ];

        user_words [
            label="{user_words|id PK|user_id FK|russian_word|english_word|created_at}"
        ];

        learning_stats [
            label="{learning_stats|id PK|user_id FK|word_id|word_type|correct_answers|total_attempts|last_reviewed}"
        ];

        users -> user_words [
            label="один ко многим"
        ];

        users -> learning_stats [
            label="один ко многим"
        ];

        common_words -> learning_stats [
            style=dashed
        ];

        user_words -> learning_stats [
            style=dashed
        ];
    }
    """

    st.graphviz_chart(
        schema,
        use_container_width=True
    )


def main():
    st.title(
        "📚 EnglishCard - Изучай английский "
        "с удовольствием!"
    )

    if "user_id" not in st.session_state:
        st.session_state.user_id = None

    if "username" not in st.session_state:
        st.session_state.username = None

    if "answer_message" not in st.session_state:
        st.session_state.answer_message = ""

    if "answer_is_correct" not in st.session_state:
        st.session_state.answer_is_correct = False

    try:
        init_database()
    except psycopg2.Error as error:
        st.error(
            "Не удалось подключиться к базе данных. "
            "Проверьте название базы, имя пользователя "
            "и пароль PostgreSQL."
        )

        st.code(str(error))
        st.stop()

    render_sidebar()

    if st.session_state.user_id is None:
        st.info(
            "Добро пожаловать в EnglishCard! "
            "Введите имя в боковой панели, "
            "чтобы начать изучение английских слов."
        )

        st.write(
            "В приложении можно изучать слова, "
            "добавлять собственные слова, удалять их "
            "и просматривать статистику."
        )

        return

    words = get_user_words(
        st.session_state.user_id
    )

    tab_1, tab_2, tab_3, tab_4, tab_5 = st.tabs(
        [
            "📖 Изучение",
            "➕ Добавить слово",
            "🗑️ Удалить слово",
            "📊 Статистика",
            "🗄️ Схема БД"
        ]
    )

    with tab_1:
        render_study_tab(words)

    with tab_2:
        render_add_word_tab()

    with tab_3:
        render_delete_word_tab(words)

    with tab_4:
        render_statistics_tab(
            st.session_state.user_id
        )

    with tab_5:
        render_schema()


if __name__ == "__main__":
    main()