PM4/PM4.py

import os
import csv
import tkinter as tk
from tkinter import ttk, messagebox
import psycopg2

DB_CONFIG = {
    "dbname": "dbwork",
    "user": "tokarev_pi",
    "password": "#8&3u&52#&D",
    "host": "edu-pg.itiscaf.ru",
    "port": "5432"
}

class BookWarehouseApp(tk.Tk):
    def __init__(self):
        super().__init__()
        self.title("Информационная система «Склад книг»")
        self.geometry("1150x700")
        
        # Настройка визуального стиля (UI/UX)
        self.setup_styles()
        
        self.current_user = None
        self.current_role = None 
        
        self.show_login_window()

    def setup_styles(self):
        """Настраивает цветовую схему, шрифты и стили компонентов интерфейса."""
        self.style = ttk.Style()
        self.style.theme_use('clam')
        
        bg_main = "#F4F6F9"      
        fg_main = "#333333"      
        primary_color = "#1A73E8" 
        btn_active = "#1557B0"    
        
        self.style.configure('.', background=bg_main, foreground=fg_main, font=('Segoe UI', 10))
        
        self.style.configure('TNotebook', background=bg_main, borderwidth=0)
        self.style.configure('TNotebook.Tab', font=('Segoe UI', 10, 'bold'), padding=[15, 6], background='#E0E4EC')
        self.style.map('TNotebook.Tab', background=[('selected', primary_color)], foreground=[('selected', 'white')])
        
        self.style.configure('TButton', font=('Segoe UI', 10, 'bold'), background=primary_color, foreground='white', borderwidth=0, padding=6)
        self.style.map('TButton', background=[('active', btn_active), ('disabled', '#D2D6DE')], foreground=[('disabled', '#959DA5')])
        
        self.style.configure('Treeview', font=('Segoe UI', 10), rowheight=28, background='white', fieldbackground='white', borderwidth=1)
        self.style.configure('Treeview.Heading', font=('Segoe UI', 10, 'bold'), background='#E6E9ED', foreground=fg_main, padding=5)
        self.style.map('Treeview', background=[('selected', primary_color)], foreground=[('selected', 'white')])
        
        self.style.configure('TLabel', background=bg_main, font=('Segoe UI', 10))
        self.style.configure('TLabelframe', background=bg_main, borderwidth=1, relief="solid")
        self.style.configure('TLabelframe.Label', font=('Segoe UI', 10, 'bold'), foreground=primary_color, background=bg_main)

    def connect_db(self):
        """Устанавливает соединение с БД PostgreSQL."""
        conn = psycopg2.connect(**DB_CONFIG)
        conn.set_client_encoding('UTF8')
        return conn

    def safe_decode(self, value):
        """Безопасно преобразует любые типы данных из БД в строку."""
        if value is None:
            return ""
        if isinstance(value, bytes):
            return value.decode('utf-8', errors='ignore')
        return str(value)

    def show_login_window(self):
        """Отрисовывает центрированное окно авторизации и регистрации."""
        self.configure(background="#F4F6F9")
        self.login_frame = ttk.Labelframe(self, text=" Авторизация в системе ", padding=25)
        self.login_frame.place(relx=0.5, rely=0.5, anchor="center")

        ttk.Label(self.login_frame, text="Добро пожаловать!", font=("Segoe UI", 16, "bold"), foreground="#1A73E8").grid(row=0, column=0, columnspan=2, pady=15)
        
        ttk.Label(self.login_frame, text="Логин:").grid(row=1, column=0, sticky="e", padx=5, pady=8)
        self.ent_login = ttk.Entry(self.login_frame, font=('Segoe UI', 11), width=25)
        self.ent_login.grid(row=1, column=1, padx=5, pady=8)
        
        ttk.Label(self.login_frame, text="Пароль:").grid(row=2, column=0, sticky="e", padx=5, pady=8)
        self.ent_password = ttk.Entry(self.login_frame, show="*", font=('Segoe UI', 11), width=25)
        self.ent_password.grid(row=2, column=1, padx=5, pady=8)
        
        btn_frame = ttk.Frame(self.login_frame)
        btn_frame.grid(row=3, column=0, columnspan=2, pady=20)
        
        ttk.Button(btn_frame, text="Войти", command=self.handle_login).pack(side="left", padx=5, ipadx=10)
        ttk.Button(btn_frame, text="Регистрация", command=self.show_register_dialog).pack(side="left", padx=5, ipadx=10)

    def show_register_dialog(self):
        """Окно создания нового аккаунта (Регистрация)."""
        dialog = tk.Toplevel(self)
        dialog.title("Регистрация нового пользователя")
        dialog.geometry("400x360")
        dialog.grab_set()
        dialog.configure(background="#F4F6F9")

        ttk.Label(dialog, text="Регистрация аккаунта", font=("Segoe UI", 14, "bold"), foreground="#1A73E8").pack(pady=15)

        ttk.Label(dialog, text="Придумайте логин:").pack(pady=2, anchor="w", padx=40)
        ent_reg_login = ttk.Entry(dialog, font=('Segoe UI', 11), width=30)
        ent_reg_login.pack(padx=40, pady=4, fill="x")

        ttk.Label(dialog, text="Придумайте пароль:").pack(pady=2, anchor="w", padx=40)
        ent_reg_password = ttk.Entry(dialog, show="*", font=('Segoe UI', 11), width=30)
        ent_reg_password.pack(padx=40, pady=4, fill="x")

        ttk.Label(dialog, text="Повторите пароль:").pack(pady=2, anchor="w", padx=40)
        ent_reg_confirm = ttk.Entry(dialog, show="*", font=('Segoe UI', 11), width=30)
        ent_reg_confirm.pack(padx=40, pady=4, fill="x")

        def execute_registration():
            login = ent_reg_login.get().strip()
            password = ent_reg_password.get().strip()
            confirm = ent_reg_confirm.get().strip()

            if not login or not password or not confirm:
                messagebox.showerror("Ошибка", "Заполните все доступные поля!", parent=dialog)
                return

            if password != confirm:
                messagebox.showerror("Ошибка", "Введенные пароли не совпадают!", parent=dialog)
                return

            try:
                conn = self.connect_db()
                with conn:
                    with conn.cursor() as cur:
                        # Точное соответствие колонкам: login, password_hash, role_name
                        cur.execute("""
                            INSERT INTO Users (login, password_hash, role_name, employee_id) 
                            VALUES (%s, %s, 'читатель', NULL);
                        """, (login, password))
                conn.close()
                dialog.destroy()
                messagebox.showinfo("Успех", "Регистрация завершена! Роль по умолчанию: читатель. Теперь вы можете войти.")
            except psycopg2.errors.UniqueViolation:
                messagebox.showerror("Ошибка", "Пользователь с таким логином уже существует!", parent=dialog)
            except Exception as e:
                messagebox.showerror("Ошибка СУБД", f"Не удалось выполнить регистрацию:\n{e}", parent=dialog)

        ttk.Button(dialog, text="✨ Зарегистрироваться", command=execute_registration).pack(pady=22, ipadx=10)

    def handle_login(self):
        """Проверяет данные через хранимую функцию getrole."""
        login = self.ent_login.get().strip()
        password = self.ent_password.get().strip()
        
        if not login or not password:
            messagebox.showerror("Ошибка", "Заполните все поля!")
            return
        
        try:
            conn = self.connect_db()
            with conn:
                with conn.cursor() as cur:
                    cur.execute('SELECT getRole(%s::varchar, %s::varchar);', (login, password))
                    row = cur.fetchone()
            conn.close()
            
            if row and row[0] is not None:
                role_raw = self.safe_decode(row[0]).strip().upper()
                self.current_user = login
                # Если роль содержит ADMIN или АДМИН — даем полные права, иначе — обычные
                self.current_role = "ADMIN" if any(x in role_raw for x in ["АДМИН", "ADMIN"]) else "EMPLOYEE"
                
                self.login_frame.destroy()
                self.show_main_window()
            else:
                messagebox.showerror("Ошибка", "Неверный логин или пароль")
                
        except Exception as e:
            messagebox.showerror("Ошибка подключения", f"Ошибка базы данных:\n{e}")

    def show_main_window(self):
        """Инициализирует главное рабочее пространство системы с вкладками."""
        status_frame = ttk.Frame(self, padding=10, relief="groove")
        status_frame.pack(side="top", fill="x")
        
        ttk.Label(status_frame, text=f"👤 Пользователь: {self.current_user}  |  💼 Уровень прав: {self.current_role}", font=("Segoe UI", 10, "bold"), foreground="#555555").pack(side="left")
        ttk.Button(status_frame, text="🚪 Выйти из аккаунта", command=self.logout).pack(side="right")

        self.notebook = ttk.Notebook(self, padding=10)
        self.notebook.pack(expand=True, fill="both")
        
        self.create_books_tab()
        self.create_warehouse_tab()
        self.create_reservations_tab()
        self.create_personnel_tab()

    def logout(self):
        """Уничтожает текущий интерфейс и возвращает пользователя на форму логина."""
        for widget in self.winfo_children():
            widget.destroy()
        self.show_login_window()

    # --- ВСПОМОГАТЕЛЬНЫЕ МЕТОДЫ СВЯЗЕЙ СУБД ---

    def get_authors_list(self):
        return self._fetch_lookup('SELECT author_id, author_name FROM Authors ORDER BY author_name;')

    def get_categories_list(self):
        return self._fetch_lookup('SELECT category_id, category_name FROM Categories ORDER BY category_name;')

    def get_books_list(self):
        return self._fetch_lookup('SELECT book_id, title FROM Books ORDER BY title;')

    def get_personnel_list(self):
        return self._fetch_lookup("SELECT employee_id, surname || ' ' || name FROM Personnel ORDER BY surname;")

    def get_positions_list(self):
        """Загружает список доступных должностей для Personnel constraint."""
        return self._fetch_lookup("SELECT position_id, position_name FROM Positions ORDER BY position_name;")

    def _fetch_lookup(self, sql):
        res = []
        try:
            conn = self.connect_db()
            with conn:
                with conn.cursor() as cur:
                    cur.execute(sql)
                    res = [(row[0], self.safe_decode(row[1])) for row in cur.fetchall()]
            conn.close()
        except Exception as e:
            print(f"Ошибка lookup: {e}")
        return res

    # ==========================================
    # ВКЛАДКА 1: КНИГИ (ПРОСМОТР + CRUD)
    # ==========================================

    def create_books_tab(self):
        tab = ttk.Frame(self.notebook, padding=10)
        self.notebook.add(tab, text="📚 Книги")
        
        search_frame = ttk.LabelFrame(tab, text=" Поиск книги по названию ", padding=10)
        search_frame.pack(fill="x", padx=5, pady=5)
        
        ttk.Label(search_frame, text="Название:").pack(side="left", padx=5)
        self.book_search_var = tk.StringVar()
        self.book_search_var.trace_add("write", lambda *args: self.load_books_data())
        ttk.Entry(search_frame, textvariable=self.book_search_var, font=('Segoe UI', 10)).pack(side="left", fill="x", expand=True, padx=5, pady=5)
        
        ttk.Button(search_frame, text="📥 Экспорт в CSV", command=lambda: self.export_to_csv(self.books_tree, "books_export.csv")).pack(side="right", padx=5)

        columns = ("id", "title", "author", "category", "price")
        self.books_tree = ttk.Treeview(tab, columns=columns, show="headings")
        self.books_tree.heading("id", text="ID")
        self.books_tree.heading("title", text="Название книги")
        self.books_tree.heading("author", text="Автор")
        self.books_tree.heading("category", text="Категория")
        self.books_tree.heading("price", text="Цена (руб.)")
        
        self.books_tree.column("id", width=60, anchor="center", stretch=tk.NO)
        self.books_tree.column("title", width=300, anchor="w", stretch=tk.YES)
        self.books_tree.column("author", width=200, anchor="w", stretch=tk.YES)
        self.books_tree.column("category", width=150, anchor="w", stretch=tk.YES)
        self.books_tree.column("price", width=100, anchor="e", stretch=tk.NO)
        self.books_tree.pack(expand=True, fill="both", padx=5, pady=5)
        
        btn_frame = ttk.Frame(tab, padding=5)
        btn_frame.pack(fill="x", padx=5, pady=5)
        ttk.Button(btn_frame, text="➕ Добавить книгу", command=self.add_book_dialog).pack(side="left", padx=5)
        ttk.Button(btn_frame, text="✏️ Редактировать", command=self.edit_book_dialog).pack(side="left", padx=5)
        ttk.Button(btn_frame, text="❌ Удалить книгу", command=self.delete_book).pack(side="left", padx=5)
            
        self.load_books_data()

    def load_books_data(self):
        for i in self.books_tree.get_children():
            self.books_tree.delete(i)
        
        search_query = self.book_search_var.get()
        try:
            conn = self.connect_db()
            with conn:
                with conn.cursor() as cur:
                    query = """
                        SELECT b.book_id, b.title, a.author_name, c.category_name, b.price 
                        FROM Books b
                        JOIN Authors a ON b.author_id = a.author_id
                        JOIN Categories c ON b.category_id = c.category_id
                        WHERE b.title ILIKE %s ORDER BY b.book_id DESC;
                    """
                    cur.execute(query, (f"%{search_query}%",))
                    for row in cur.fetchall():
                        self.books_tree.insert("", "end", values=[self.safe_decode(cell) for cell in row])
            conn.close()
        except Exception as e:
            print(f"Ошибка загрузки книг: {e}")

    def add_book_dialog(self):
        authors_list = self.get_authors_list()
        categories_list = self.get_categories_list()

        if not authors_list or not categories_list:
            messagebox.showerror("Ошибка", "Списки авторов или категорий пусты в БД!")
            return

        dialog = tk.Toplevel(self)
        dialog.title("Добавление книги")
        dialog.geometry("400x320")
        dialog.grab_set()
        
        ttk.Label(dialog, text="Название книги:").pack(pady=2, anchor="w", padx=20)
        ent_title = ttk.Entry(dialog, width=40)
        ent_title.pack(padx=20, pady=2, fill="x")
        
        ttk.Label(dialog, text="Автор:").pack(pady=2, anchor="w", padx=20)
        cb_author = ttk.Combobox(dialog, values=[a[1] for a in authors_list], state="readonly")
        cb_author.pack(padx=20, pady=2, fill="x")
        cb_author.current(0)

        ttk.Label(dialog, text="Категория:").pack(pady=2, anchor="w", padx=20)
        cb_category = ttk.Combobox(dialog, values=[c[1] for c in categories_list], state="readonly")
        cb_category.pack(padx=20, pady=2, fill="x")
        cb_category.current(0)
        
        ttk.Label(dialog, text="Цена:").pack(pady=2, anchor="w", padx=20)
        ent_price = ttk.Entry(dialog, width=40)
        ent_price.pack(padx=20, pady=2, fill="x")
        
        def save():
            title = ent_title.get().strip()
            price_str = ent_price.get().strip()
            
            if not title or not price_str:
                messagebox.showerror("Ошибка", "Заполните все поля!", parent=dialog)
                return
            try:
                price = float(price_str)
            except ValueError:
                messagebox.showerror("Ошибка", "Цена должна быть числом!", parent=dialog)
                return

            author_id = authors_list[cb_author.current()][0]
            category_id = categories_list[cb_category.current()][0]

            try:
                conn = self.connect_db()
                with conn:
                    with conn.cursor() as cur:
                        cur.execute('INSERT INTO Books (title, price, author_id, category_id) VALUES (%s, %s, %s, %s)', 
                                    (title, price, author_id, category_id))
                conn.close()
                self.load_books_data()
                dialog.destroy()
                messagebox.showinfo("Успех", "Книга успешно добавлена!")
            except Exception as e:
                messagebox.showerror("Ошибка", f"Не удалось добавить книгу: {e}", parent=dialog)

        ttk.Button(dialog, text="💾 Сохранить", command=save).pack(pady=20)

    def edit_book_dialog(self):
        selected = self.books_tree.selection()
        if not selected:
            messagebox.showwarning("Внимание", "Выберите книгу для редактирования!")
            return
            
        book_values = self.books_tree.item(selected[0])['values']
        book_id, current_title, current_author_name, current_category_name, current_price = book_values

        authors_list = self.get_authors_list()
        categories_list = self.get_categories_list()

        dialog = tk.Toplevel(self)
        dialog.title(f"Редактирование книги (ID: {book_id})")
        dialog.geometry("400x320")
        dialog.grab_set()
        
        ttk.Label(dialog, text="Название книги:").pack(pady=2, anchor="w", padx=20)
        ent_title = ttk.Entry(dialog, width=40)
        ent_title.pack(padx=20, pady=2, fill="x")
        ent_title.insert(0, current_title)
        
        ttk.Label(dialog, text="Автор:").pack(pady=2, anchor="w", padx=20)
        author_names = [a[1] for a in authors_list]
        cb_author = ttk.Combobox(dialog, values=author_names, state="readonly")
        cb_author.pack(padx=20, pady=2, fill="x")
        if current_author_name in author_names:
            cb_author.current(author_names.index(current_author_name))
        
        ttk.Label(dialog, text="Категория:").pack(pady=2, anchor="w", padx=20)
        category_names = [c[1] for c in categories_list]
        cb_category = ttk.Combobox(dialog, values=category_names, state="readonly")
        cb_category.pack(padx=20, pady=2, fill="x")
        if current_category_name in category_names:
            cb_category.current(category_names.index(current_category_name))
        
        ttk.Label(dialog, text="Цена:").pack(pady=2, anchor="w", padx=20)
        ent_price = ttk.Entry(dialog, width=40)
        ent_price.pack(padx=20, pady=2, fill="x")
        ent_price.insert(0, current_price)
        
        def update_save():
            title = ent_title.get().strip()
            price_str = ent_price.get().strip()
            
            if not title or not price_str:
                messagebox.showerror("Ошибка", "Заполните все поля!", parent=dialog)
                return
            try:
                price = float(price_str)
            except ValueError:
                messagebox.showerror("Ошибка", "Цена должна быть числом!", parent=dialog)
                return

            author_id = authors_list[cb_author.current()][0]
            category_id = categories_list[cb_category.current()][0]

            try:
                conn = self.connect_db()
                with conn:
                    with conn.cursor() as cur:
                        cur.execute('''
                            UPDATE Books 
                            SET title = %s, price = %s, author_id = %s, category_id = %s 
                            WHERE book_id = %s;
                        ''', (title, price, author_id, category_id, book_id))
                conn.close()
                self.load_books_data()
                dialog.destroy()
                messagebox.showinfo("Успех", "Данные книги успешно обновлены!")
            except Exception as e:
                messagebox.showerror("Ошибка", f"Не удалось обновить данные: {e}", parent=dialog)

        ttk.Button(dialog, text="🔄 Обновить", command=update_save).pack(pady=20)

    def delete_book(self):
        selected = self.books_tree.selection()
        if not selected:
            messagebox.showwarning("Внимание", "Выберите книгу для удаления!")
            return
        book_id = self.books_tree.item(selected[0])['values'][0]
        book_title = self.books_tree.item(selected[0])['values'][1]
        
        if messagebox.askyesno("Подтверждение", f"Вы уверены, что хотите удалить '{book_title}'?\nЭто каскадно очистит связанные остатки склада!"):
            try:
                conn = self.connect_db()
                with conn:
                    with conn.cursor() as cur:
                        cur.execute('DELETE FROM Warehouse WHERE book_id = %s;', (book_id,))
                        cur.execute('DELETE FROM Reservation_Books WHERE book_id = %s;', (book_id,))
                        cur.execute('DELETE FROM Books WHERE book_id = %s;', (book_id,))
                conn.close()
                self.load_books_data()
                self.load_warehouse_data()
                messagebox.showinfo("Успех", f"Книга '{book_title}' успешно удалена!")
            except Exception as e:
                messagebox.showerror("Ошибка удаления", f"Критический сбой СУБД:\n{e}")

    # ==========================================
    # ВКЛАДКА 2: СКЛАД (ПРОСМОТР + CRUD)
    # ==========================================

    def create_warehouse_tab(self):
        tab = ttk.Frame(self.notebook, padding=10)
        self.notebook.add(tab, text="🏢 Склад")
        
        action_frame = ttk.Frame(tab, padding=5)
        action_frame.pack(fill="x", side="top", pady=5)
        ttk.Button(action_frame, text="📥 Экспорт остатков (CSV)", command=lambda: self.export_to_csv(self.wh_tree, "warehouse_export.csv")).pack(side="right", padx=5)

        columns = ("book_id", "title", "author", "quantity", "price", "total_cost")
        self.wh_tree = ttk.Treeview(tab, columns=columns, show="headings")
        self.wh_tree.heading("book_id", text="ID Книги")
        self.wh_tree.heading("title", text="Название книги")
        self.wh_tree.heading("author", text="Автор")
        self.wh_tree.heading("quantity", text="Количество на складе")
        self.wh_tree.heading("price", text="Цена за ед.")
        self.wh_tree.heading("total_cost", text="Общая стоимость")
        
        self.wh_tree.column("book_id", width=80, anchor="center")
        self.wh_tree.column("title", width=250, anchor="w")
        self.wh_tree.column("author", width=180, anchor="w")
        self.wh_tree.column("quantity", width=130, anchor="center")
        self.wh_tree.column("price", width=100, anchor="e")
        self.wh_tree.column("total_cost", width=120, anchor="e")
        self.wh_tree.pack(expand=True, fill="both", padx=5, pady=5)
        
        btn_frame = ttk.Frame(tab, padding=5)
        btn_frame.pack(fill="x", padx=5, pady=5)
        ttk.Button(btn_frame, text="➕ Принять на склад", command=self.add_warehouse_dialog).pack(side="left", padx=5)
        ttk.Button(btn_frame, text="✏️ Изменить количество", command=self.edit_warehouse_dialog).pack(side="left", padx=5)
        ttk.Button(btn_frame, text="❌ Списать со склада", command=self.delete_warehouse).pack(side="left", padx=5)
        
        self.load_warehouse_data()

    def load_warehouse_data(self):
        """Загрузка данных из системного представления BookStockInfo."""
        for i in self.wh_tree.get_children():
            self.wh_tree.delete(i)
            
        try:
            conn = self.connect_db()
            with conn:
                with conn.cursor() as cur:
                    cur.execute('SELECT * FROM BookStockInfo;')
                    for row in cur.fetchall():
                        self.wh_tree.insert("", "end", values=[self.safe_decode(cell) for cell in row])
            conn.close()
        except Exception as e:
            print(f"Ошибка загрузки представления склада: {e}")

    def add_warehouse_dialog(self):
        books_list = self.get_books_list()
        if not books_list:
            messagebox.showerror("Ошибка", "Список книг пуст!")
            return

        dialog = tk.Toplevel(self)
        dialog.title("Приемка на склад")
        dialog.geometry("400x220")
        dialog.grab_set()

        ttk.Label(dialog, text="Выберите книгу:").pack(pady=5, anchor="w", padx=20)
        cb_book = ttk.Combobox(dialog, values=[b[1] for b in books_list], state="readonly")
        cb_book.pack(padx=20, pady=2, fill="x")
        cb_book.current(0)

        ttk.Label(dialog, text="Количество:").pack(pady=5, anchor="w", padx=20)
        ent_qty = ttk.Entry(dialog, width=40)
        ent_qty.pack(padx=20, pady=2, fill="x")

        def save():
            qty_str = ent_qty.get().strip()
            if not qty_str:
                messagebox.showerror("Ошибка", "Укажите количество!", parent=dialog)
                return
            try:
                qty = int(qty_str)
                if qty < 0: raise ValueError
            except ValueError:
                messagebox.showerror("Ошибка", "Должно быть целым положительным числом!", parent=dialog)
                return

            book_id = books_list[cb_book.current()][0]

            try:
                conn = self.connect_db()
                with conn:
                    with conn.cursor() as cur:
                        cur.execute('SELECT quantity FROM Warehouse WHERE book_id = %s;', (book_id,))
                        row = cur.fetchone()
                        if row:
                            cur.execute('UPDATE Warehouse SET quantity = quantity + %s WHERE book_id = %s;', (qty, book_id))
                        else:
                            cur.execute('INSERT INTO Warehouse (book_id, quantity) VALUES (%s, %s);', (book_id, qty))
                conn.close()
                self.load_warehouse_data()
                dialog.destroy()
                messagebox.showinfo("Успех", "Товар оприходован!")
            except Exception as e:
                messagebox.showerror("Ошибка", f"Не удалось выполнить операцию: {e}", parent=dialog)

        ttk.Button(dialog, text="💾 Оприходовать", command=save).pack(pady=20)

    def edit_warehouse_dialog(self):
        selected = self.wh_tree.selection()
        if not selected:
            messagebox.showwarning("Внимание", "Выберите строку для корректировки остатка!")
            return

        values = self.wh_tree.item(selected[0])['values']
        book_id, book_title, _, current_qty, _, _ = values

        dialog = tk.Toplevel(self)
        dialog.title("Корректировка остатков")
        dialog.geometry("400x180")
        dialog.grab_set()

        ttk.Label(dialog, text=f"Книга: {book_title}", font=("Segoe UI", 10, "bold"), wraplength=350).pack(pady=10, anchor="w", padx=20)
        ttk.Label(dialog, text="Новое количество на складе:").pack(pady=2, anchor="w", padx=20)
        ent_qty = ttk.Entry(dialog, width=40)
        ent_qty.pack(padx=20, pady=2, fill="x")
        ent_qty.insert(0, str(current_qty))

        def update():
            qty_str = ent_qty.get().strip()
            if not qty_str:
                messagebox.showerror("Ошибка", "Заполните поле!", parent=dialog)
                return
            try:
                qty = int(qty_str)
                if qty < 0: raise ValueError
            except ValueError:
                messagebox.showerror("Ошибка", "Введите целое положительное число!", parent=dialog)
                return

            try:
                conn = self.connect_db()
                with conn:
                    with conn.cursor() as cur:
                        cur.execute('UPDATE Warehouse SET quantity = %s WHERE book_id = %s;', (qty, book_id))
                conn.close()
                self.load_warehouse_data()
                dialog.destroy()
                messagebox.showinfo("Успех", "Количество обновлено!")
            except Exception as e:
                messagebox.showerror("Ошибка", f"Не удалось обновить: {e}", parent=dialog)

        ttk.Button(dialog, text="🔄 Обновить остаток", command=update).pack(pady=15)

    def delete_warehouse(self):
        selected = self.wh_tree.selection()
        if not selected:
            messagebox.showwarning("Внимание", "Выберите позицию для списания!")
            return
        
        values = self.wh_tree.item(selected[0])['values']
        book_id, book_title = values[0], values[1]

        if messagebox.askyesno("Подтверждение", f"Удалить '{book_title}' со склада? Строка остатков будет стерта."):
            try:
                conn = self.connect_db()
                with conn:
                    with conn.cursor() as cur:
                        cur.execute('DELETE FROM Warehouse WHERE book_id = %s;', (book_id,))
                conn.close()
                self.load_warehouse_data()
                messagebox.showinfo("Успех", "Позиция списана.")
            except Exception as e:
                messagebox.showerror("Ошибка списания", f"Не удалось удалить: {e}")

    # ==========================================
    # ВКЛАДКА 3: БРОНИРОВАНИЕ (ЖУРНАЛ + CRUD)
    # ==========================================

    def create_reservations_tab(self):
        tab = ttk.Frame(self.notebook, padding=10)
        self.notebook.add(tab, text="📋 Бронирование")
        
        columns = ("res_id", "date", "employee")
        self.res_tree = ttk.Treeview(tab, columns=columns, show="headings")
        self.res_tree.heading("res_id", text="ID Брони")
        self.res_tree.heading("date", text="Дата оформления")
        self.res_tree.heading("employee", text="Ответственный сотрудник")
        
        self.res_tree.column("res_id", width=100, anchor="center")
        self.res_tree.column("date", width=250, anchor="center")
        self.res_tree.column("employee", width=400, anchor="w")
        self.res_tree.pack(expand=True, fill="both", padx=5, pady=5)
        
        btn_frame = ttk.Frame(tab, padding=5)
        btn_frame.pack(fill="x", padx=5, pady=5)
        ttk.Button(btn_frame, text="➕ Создать новую бронь", command=self.add_reservation_dialog).pack(side="left", padx=5)
        ttk.Button(btn_frame, text="✏️ Изменить сотрудника", command=self.edit_reservation_dialog).pack(side="left", padx=5)
        ttk.Button(btn_frame, text="❌ Аннулировать бронь", command=self.delete_reservation).pack(side="left", padx=5)
            
        self.load_reservations_data()

    def load_reservations_data(self):
        for i in self.res_tree.get_children():
            self.res_tree.delete(i)
            
        try:
            conn = self.connect_db()
            with conn:
                with conn.cursor() as cur:
                    query = """
                        SELECT r.reservation_id, r.reservation_date, p.surname || ' ' || p.name || ' ' || COALESCE(p.patronymic, '')
                        FROM Reservations r
                        JOIN Personnel p ON r.employee_id = p.employee_id
                        ORDER BY r.reservation_id DESC;
                    """
                    cur.execute(query)
                    for row in cur.fetchall():
                        self.res_tree.insert("", "end", values=[self.safe_decode(cell) for cell in row])
            conn.close()
        except Exception as e:
            print(f"Ошибка загрузки броней: {e}")

    def add_reservation_dialog(self):
        pers_list = self.get_personnel_list()
        if not pers_list:
            messagebox.showerror("Ошибка", "Штат персонала пуст!")
            return

        dialog = tk.Toplevel(self)
        dialog.title("Новое бронирование")
        dialog.geometry("400x180")
        dialog.grab_set()

        ttk.Label(dialog, text="Выберите ответственного сотрудника:").pack(pady=10, anchor="w", padx=20)
        cb_pers = ttk.Combobox(dialog, values=[p[1] for p in pers_list], state="readonly")
        cb_pers.pack(padx=20, pady=5, fill="x")
        cb_pers.current(0)

        def make():
            emp_id = pers_list[cb_pers.current()][0]
            try:
                conn = self.connect_db()
                with conn:
                    with conn.cursor() as cur:
                        cur.execute('INSERT INTO Reservations (employee_id) VALUES (%s) RETURNING reservation_id;', (emp_id,))
                        new_id = cur.fetchone()[0]
                conn.close()
                self.load_reservations_data()
                dialog.destroy()
                messagebox.showinfo("Успех", f"Успешно оформлена бронь №{new_id}!")
            except Exception as e:
                messagebox.showerror("Ошибка", f"Не удалось создать бронь: {e}", parent=dialog)

        ttk.Button(dialog, text="💾 Зарегистрировать бронь", command=make).pack(pady=20)

    def edit_reservation_dialog(self):
        selected = self.res_tree.selection()
        if not selected:
            messagebox.showwarning("Внимание", "Выберите бронь для изменения!")
            return

        res_id, _, current_emp = self.res_tree.item(selected[0])['values']
        pers_list = self.get_personnel_list()

        dialog = tk.Toplevel(self)
        dialog.title(f"Редактирование брони №{res_id}")
        dialog.geometry("400x180")
        dialog.grab_set()

        ttk.Label(dialog, text="Изменить сотрудника:").pack(pady=10, anchor="w", padx=20)
        names = [p[1] for p in pers_list]
        cb_pers = ttk.Combobox(dialog, values=names, state="readonly")
        cb_pers.pack(padx=20, pady=5, fill="x")
        
        for i, n in enumerate(names):
            if current_emp.startswith(n) or n.startswith(current_emp):
                cb_pers.current(i)
                break
        if not cb_pers.get() and names:
            cb_pers.current(0)

        def update():
            emp_id = pers_list[cb_pers.current()][0]
            try:
                conn = self.connect_db()
                with conn:
                    with conn.cursor() as cur:
                        cur.execute('UPDATE Reservations SET employee_id = %s WHERE reservation_id = %s;', (emp_id, res_id))
                conn.close()
                self.load_reservations_data()
                dialog.destroy()
                messagebox.showinfo("Успех", "Ответственный успешно изменен!")
            except Exception as e:
                messagebox.showerror("Ошибка", f"Не удалось изменить: {e}", parent=dialog)

        ttk.Button(dialog, text="🔄 Обновить бронь", command=update).pack(pady=20)

    def delete_reservation(self):
        selected = self.res_tree.selection()
        if not selected:
            messagebox.showwarning("Внимание", "Выберите бронь для аннулирования!")
            return

        res_id = self.res_tree.item(selected[0])['values'][0]
        if messagebox.askyesno("Подтверждение", f"Вы уверены, что хотите полностью удалить бронь №{res_id}?"):
            try:
                conn = self.connect_db()
                with conn:
                    with conn.cursor() as cur:
                        cur.execute('DELETE FROM Reservation_Books WHERE reservation_id = %s;', (res_id,))
                        cur.execute('DELETE FROM Reservations WHERE reservation_id = %s;', (res_id,))
                conn.close()
                self.load_reservations_data()
                messagebox.showinfo("Успех", f"Бронь №{res_id} удалена.")
            except Exception as e:
                messagebox.showerror("Ошибка удаления", f"Не удалось удалить:\n{e}")

    # ==========================================
    # ВКЛАДКА 4: УПРАВЛЕНИЕ ПЕРСОНАЛОМ (CRUD)
    # ==========================================

    def create_personnel_tab(self):
        tab = ttk.Frame(self.notebook, padding=10)
        self.notebook.add(tab, text="👥 Управление Персоналом")
        
        columns = ("id", "fio", "position", "birth", "passport", "phone", "salary")
        self.pers_tree = ttk.Treeview(tab, columns=columns, show="headings")
        self.pers_tree.heading("id", text="ID")
        self.pers_tree.heading("fio", text="ФИО Сотрудника")
        self.pers_tree.heading("position", text="Должность")
        self.pers_tree.heading("birth", text="Дата рожд.")
        self.pers_tree.heading("passport", text="Паспортные данные")
        self.pers_tree.heading("phone", text="Телефон")
        self.pers_tree.heading("salary", text="Оклад (руб.)")
        
        self.pers_tree.column("id", width=50, anchor="center", stretch=tk.NO)
        self.pers_tree.column("fio", width=220, stretch=tk.YES)
        self.pers_tree.column("position", width=140, stretch=tk.YES)
        self.pers_tree.column("birth", width=100, anchor="center", stretch=tk.NO)
        self.pers_tree.column("passport", width=140, anchor="center", stretch=tk.NO)
        self.pers_tree.column("phone", width=130, anchor="center", stretch=tk.NO)
        self.pers_tree.column("salary", width=100, anchor="e", stretch=tk.NO)
        self.pers_tree.pack(expand=True, fill="both", padx=5, pady=5)
        
        btn_frame = ttk.Frame(tab, padding=5)
        btn_frame.pack(fill="x", padx=5, pady=5)
        ttk.Button(btn_frame, text="➕ Нанять сотрудника", command=self.add_personnel_dialog).pack(side="left", padx=5)
        ttk.Button(btn_frame, text="✏️ Редактировать анкету", command=self.edit_personnel_dialog).pack(side="left", padx=5)
        ttk.Button(btn_frame, text="❌ Уволить сотрудника", command=self.delete_personnel).pack(side="left", padx=5)
        
        self.load_personnel_data()

    def load_personnel_data(self):
        for i in self.pers_tree.get_children():
            self.pers_tree.delete(i)
            
        try:
            conn = self.connect_db()
            with conn:
                with conn.cursor() as cur:
                    # Добавлен JOIN таблицы Positions для корректного отображения
                    query = """
                        SELECT p.employee_id, p.surname || ' ' || p.name || ' ' || COALESCE(p.patronymic, ''), 
                               pos.position_name, p.birth_date, p.passport_series || ' ' || p.passport_number, p.phone, p.salary 
                        FROM Personnel p
                        JOIN Positions pos ON p.position_id = pos.position_id
                        ORDER BY p.employee_id DESC;
                    """
                    cur.execute(query)
                    for row in cur.fetchall():
                        self.pers_tree.insert("", "end", values=[self.safe_decode(cell) for cell in row])
            conn.close()
        except Exception as e:
            print(f"Ошибка загрузки персонала: {e}")

    def add_personnel_dialog(self):
        positions_list = self.get_positions_list()
        if not positions_list:
            messagebox.showerror("Ошибка", "Таблица должностей Positions пуста в БД!")
            return

        dialog = tk.Toplevel(self)
        dialog.title("Регистрация сотрудника")
        dialog.geometry("460x520")
        dialog.grab_set()

        ttk.Label(dialog, text="Фамилия:").pack(anchor="w", padx=20, pady=2)
        ent_surname = ttk.Entry(dialog)
        ent_surname.pack(fill="x", padx=20, pady=2)

        ttk.Label(dialog, text="Имя:").pack(anchor="w", padx=20, pady=2)
        ent_name = ttk.Entry(dialog)
        ent_name.pack(fill="x", padx=20, pady=2)

        ttk.Label(dialog, text="Отчество:").pack(anchor="w", padx=20, pady=2)
        ent_patr = ttk.Entry(dialog)
        ent_patr.pack(fill="x", padx=20, pady=2)

        ttk.Label(dialog, text="Выбрать должность:").pack(anchor="w", padx=20, pady=2)
        cb_pos = ttk.Combobox(dialog, values=[p[1] for p in positions_list], state="readonly")
        cb_pos.pack(fill="x", padx=20, pady=2)
        cb_pos.current(0)

        ttk.Label(dialog, text="Дата рождения (ГГГГ-ММ-ДД):").pack(anchor="w", padx=20, pady=2)
        ent_birth = ttk.Entry(dialog)
        ent_birth.pack(fill="x", padx=20, pady=2)
        ent_birth.insert(0, "1995-01-01")

        ttk.Label(dialog, text="Серия паспорта (4 цифры):").pack(anchor="w", padx=20, pady=2)
        ent_pass_ser = ttk.Entry(dialog)
        ent_pass_ser.pack(fill="x", padx=20, pady=2)

        ttk.Label(dialog, text="Номер паспорта (6 цифр):").pack(anchor="w", padx=20, pady=2)
        ent_pass_num = ttk.Entry(dialog)
        ent_pass_num.pack(fill="x", padx=20, pady=2)

        ttk.Label(dialog, text="Телефон:").pack(anchor="w", padx=20, pady=2)
        ent_phone = ttk.Entry(dialog)
        ent_phone.pack(fill="x", padx=20, pady=2)

        ttk.Label(dialog, text="Оклад:").pack(anchor="w", padx=20, pady=2)
        ent_salary = ttk.Entry(dialog)
        ent_salary.pack(fill="x", padx=20, pady=2)

        def save():
            s = ent_surname.get().strip()
            n = ent_name.get().strip()
            p = ent_patr.get().strip()
            b = ent_birth.get().strip()
            ps = ent_pass_ser.get().strip()
            pn = ent_pass_num.get().strip()
            ph = ent_phone.get().strip()
            sal_str = ent_salary.get().strip()

            if not s or not n or not b or not ps or not pn or not sal_str:
                messagebox.showerror("Ошибка", "Заполните обязательные поля!", parent=dialog)
                return
            try:
                salary = float(sal_str)
            except ValueError:
                messagebox.showerror("Ошибка", "Оклад должен быть числом!", parent=dialog)
                return

            # Получаем корректный position_id из СУБД для внешнего ключа
            position_id = positions_list[cb_pos.current()][0]

            try:
                conn = self.connect_db()
                with conn:
                    with conn.cursor() as cur:
                        cur.execute("""
                            INSERT INTO Personnel (surname, name, patronymic, birth_date, passport_series, passport_number, phone, salary, position_id)
                            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s);
                        """, (s, n, p if p else None, b, ps, pn, ph if ph else None, salary, position_id))
                conn.close()
                self.load_personnel_data()
                dialog.destroy()
                messagebox.showinfo("Успех", "Сотрудник принят в штат!")
            except Exception as e:
                messagebox.showerror("Ошибка СУБД", f"Не удалось добавить: {e}", parent=dialog)

        ttk.Button(dialog, text="💾 Сохранить анкету", command=save).pack(pady=15)

    def edit_personnel_dialog(self):
        selected = self.pers_tree.selection()
        if not selected:
            messagebox.showwarning("Внимание", "Выберите сотрудника для изменения!")
            return

        values = self.pers_tree.item(selected[0])['values']
        emp_id, full_fio, current_pos_name, birth, passport, phone, salary = values

        fio_parts = full_fio.split()
        s_val = fio_parts[0] if len(fio_parts) > 0 else ""
        n_val = fio_parts[1] if len(fio_parts) > 1 else ""
        p_val = fio_parts[2] if len(fio_parts) > 2 else ""

        pass_parts = str(passport).split()
        ps_val = pass_parts[0] if len(pass_parts) > 0 else ""
        pn_val = pass_parts[1] if len(pass_parts) > 1 else ""

        positions_list = self.get_positions_list()

        dialog = tk.Toplevel(self)
        dialog.title(f"Анкета сотрудника (ID: {emp_id})")
        dialog.geometry("460x520")
        dialog.grab_set()

        ttk.Label(dialog, text="Фамилия:").pack(anchor="w", padx=20, pady=2)
        ent_surname = ttk.Entry(dialog)
        ent_surname.pack(fill="x", padx=20, pady=2)
        ent_surname.insert(0, s_val)

        ttk.Label(dialog, text="Имя:").pack(anchor="w", padx=20, pady=2)
        ent_name = ttk.Entry(dialog)
        ent_name.pack(fill="x", padx=20, pady=2)
        ent_name.insert(0, n_val)

        ttk.Label(dialog, text="Отчество:").pack(anchor="w", padx=20, pady=2)
        ent_patr = ttk.Entry(dialog)
        ent_patr.pack(fill="x", padx=20, pady=2)
        ent_patr.insert(0, p_val)

        ttk.Label(dialog, text="Изменить должность:").pack(anchor="w", padx=20, pady=2)
        pos_names = [p[1] for p in positions_list]
        cb_pos = ttk.Combobox(dialog, values=pos_names, state="readonly")
        cb_pos.pack(fill="x", padx=20, pady=2)
        if current_pos_name in pos_names:
            cb_pos.current(pos_names.index(current_pos_name))

        ttk.Label(dialog, text="Дата рождения (ГГГГ-ММ-ДД):").pack(anchor="w", padx=20, pady=2)
        ent_birth = ttk.Entry(dialog)
        ent_birth.pack(fill="x", padx=20, pady=2)
        ent_birth.insert(0, birth)

        ttk.Label(dialog, text="Серия паспорта:").pack(anchor="w", padx=20, pady=2)
        ent_pass_ser = ttk.Entry(dialog)
        ent_pass_ser.pack(fill="x", padx=20, pady=2)
        ent_pass_ser.insert(0, ps_val)

        ttk.Label(dialog, text="Номер паспорта:").pack(anchor="w", padx=20, pady=2)
        ent_pass_num = ttk.Entry(dialog)
        ent_pass_num.pack(fill="x", padx=20, pady=2)
        ent_pass_num.insert(0, pn_val)

        ttk.Label(dialog, text="Телефон:").pack(anchor="w", padx=20, pady=2)
        ent_phone = ttk.Entry(dialog)
        ent_phone.pack(fill="x", padx=20, pady=2)
        ent_phone.insert(0, phone)

        ttk.Label(dialog, text="Оклад:").pack(anchor="w", padx=20, pady=2)
        ent_salary = ttk.Entry(dialog)
        ent_salary.pack(fill="x", padx=20, pady=2)
        ent_salary.insert(0, str(salary))

        def update():
            s = ent_surname.get().strip()
            n = ent_name.get().strip()
            p = ent_patr.get().strip()
            b = ent_birth.get().strip()
            ps = ent_pass_ser.get().strip()
            pn = ent_pass_num.get().strip()
            ph = ent_phone.get().strip()
            sal_str = ent_salary.get().strip()

            if not s or not n or not b or not ps or not pn or not sal_str:
                messagebox.showerror("Ошибка", "Заполните обязательные поля!", parent=dialog)
                return
            try:
                sal = float(sal_str)
            except ValueError:
                messagebox.showerror("Ошибка", "Оклад должен быть числом!", parent=dialog)
                return

            position_id = positions_list[cb_pos.current()][0]

            try:
                conn = self.connect_db()
                with conn:
                    with conn.cursor() as cur:
                        cur.execute("""
                            UPDATE Personnel 
                            SET surname=%s, name=%s, patronymic=%s, birth_date=%s, passport_series=%s, passport_number=%s, phone=%s, salary=%s, position_id=%s
                            WHERE employee_id=%s;
                        """, (s, n, p if p else None, b, ps, pn, ph if ph else None, sal, position_id, emp_id))
                conn.close()
                self.load_personnel_data()
                self.load_reservations_data() 
                dialog.destroy()
                messagebox.showinfo("Успех", "Данные обновлены!")
            except Exception as e:
                messagebox.showerror("Ошибка СУБД", f"Не удалось обновить: {e}", parent=dialog)

        ttk.Button(dialog, text="🔄 Обновить анкету", command=update).pack(pady=15)

    def delete_personnel(self):
        selected = self.pers_tree.selection()
        if not selected:
            messagebox.showwarning("Внимание", "Выберите сотрудника для увольнения!")
            return

        emp_id, fio = self.pers_tree.item(selected[0])['values'][0], self.pers_tree.item(selected[0])['values'][1]

        if messagebox.askyesno("Подтверждение", f"Удалить сотрудника '{fio}' из системы?\nЭто очистит связанные записи!"):
            try:
                conn = self.connect_db()
                with conn:
                    with conn.cursor() as cur:
                        cur.execute('DELETE FROM Reservations WHERE employee_id = %s;', (emp_id,))
                        cur.execute('DELETE FROM Personnel WHERE employee_id = %s;', (emp_id,))
                conn.close()
                self.load_personnel_data()
                self.load_reservations_data()
                messagebox.showinfo("Успех", f"Сотрудник '{fio}' удален.")
            except Exception as e:
                messagebox.showerror("Ошибка СУБД", f"Не удалось удалить:\n{e}")

    # ==========================================
    # УНИВЕРСАЛЬНЫЙ МОДУЛЬ ЭКСПОРТА
    # ==========================================

    def export_to_csv(self, tree, filename):
        headers = [tree.heading(col)["text"] for col in tree["columns"]]
        rows = [tree.item(item)["values"] for item in tree.get_children()]
            
        try:
            with open(filename, mode="w", newline="", encoding="utf-8-sig") as f:
                writer = csv.writer(f, delimiter=";")
                writer.writerow(headers)
                writer.writerows(rows)
            messagebox.showinfo("Успех", f"Данные экспортированы в:\n{os.path.abspath(filename)}")
        except Exception as e:
            messagebox.showerror("Ошибка экспорта", f"Не удалось сохранить: {e}")

if __name__ == "__main__":
    app = BookWarehouseApp()
    app.mainloop()