"""
Урок 81. Практикум 20: Данные о сотрудниках. Часть 2 — весь код примеров одним файлом.

Источник: subjects/python-fundamentals/course/lessons/81-practice-20/examples.html
Файл собран автоматически (tools/build_lesson_examples.py): правьте страницу урока.
Запуск: python lesson-81.py
"""



# ====================================================================
# 💻 Рабочие примеры
# ====================================================================
import pymysql


def print_employees(rows):
    for i, (first, last, title, salary) in enumerate(rows, start=1):
        print(f"{i}. {first} {last} — {title} — {salary}")


def main():
    config = {
        "host": "ich-db.edu.itcareerhub.de",
        "user": "ich1",
        "password": "password",
        "database": "hr",
        "charset": "utf8mb4",
    }

    with pymysql.connect(**config) as connection:
        with connection.cursor() as cursor:
            cursor.execute("SELECT department_name FROM departments")
            departments = [row[0] for row in cursor]
            for i, name in enumerate(departments, start=1):
                print(f"{i}. {name}")

            selected_department = input("Enter department: ")

            cursor.execute("""
                SELECT first_name, last_name, job_title, salary
                FROM employees
                JOIN departments ON employees.department_id = departments.department_id
                JOIN jobs ON employees.job_id = jobs.job_id
                WHERE departments.department_name = %s
                ORDER BY salary DESC
            """, (selected_department,))

            results = cursor.fetchall()
            print_employees(results)


if __name__ == "__main__":
    main()


# ====================================================================
# 💻 Рабочие примеры
# ====================================================================
import pymysql


def print_employees(rows):
    for i, (first, last, title, salary) in enumerate(rows, start=1):
        print(f"{i}. {first} {last} — {title} — {salary}")


def main():
    config = {
        "host": "ich-db.edu.itcareerhub.de",
        "user": "ich1",
        "password": "password",
        "database": "hr",
        "charset": "utf8mb4",
    }

    with pymysql.connect(**config) as connection:
        with connection.cursor() as cursor:
            cursor.execute("SELECT department_name FROM departments")
            departments = [row[0] for row in cursor]
            for i, name in enumerate(departments, start=1):
                print(f"{i}. {name}")

            selected_index = int(input("Enter department number: "))
            selected_department = departments[selected_index - 1]
            print("You choose:", selected_department)

            cursor.execute("""
                SELECT first_name, last_name, job_title, salary
                FROM employees
                JOIN departments ON employees.department_id = departments.department_id
                JOIN jobs ON employees.job_id = jobs.job_id
                WHERE departments.department_name = %s
                ORDER BY salary DESC
            """, (selected_department,))

            results = cursor.fetchall()
            print_employees(results)


if __name__ == "__main__":
    main()


# ====================================================================
# 💻 Рабочие примеры
# ====================================================================
import pymysql


def print_employees(rows):
    for i, (first, last, title, salary) in enumerate(rows, start=1):
        print(f"{i}. {first} {last} — {title} — {salary}")


def main():
    config = {
        "host": "ich-db.edu.itcareerhub.de",
        "user": "ich1",
        "password": "password",
        "database": "hr",
        "charset": "utf8mb4",
    }

    with pymysql.connect(**config) as connection:
        with connection.cursor() as cursor:
            cursor.execute("SELECT department_name FROM departments")
            departments = [row[0] for row in cursor]
            for i, name in enumerate(departments, start=1):
                print(f"{i}. {name}")

            selected_index = int(input("Enter department number: "))
            selected_department = departments[selected_index - 1]
            print(f"You selected: {selected_department}")

            cursor.execute("""
                SELECT first_name, last_name, job_title, salary
                FROM employees
                JOIN departments ON employees.department_id = departments.department_id
                JOIN jobs ON employees.job_id = jobs.job_id
                WHERE departments.department_name = %s
                ORDER BY salary DESC
            """, (selected_department,))

            results = cursor.fetchall()

            if not results:
                print(f"No employees found in {selected_department} department.")
            else:
                print_employees(results)


if __name__ == "__main__":
    main()