---

import os.path
import shutil
import sys
import mysql.connector
from item import Ui_Form as ItemUI
from products_main import Ui_mainWindow as MainUI
from redakt_products import Ui_MainWindow as EditItem
from PyQt6 import QtWidgets, QtCore, QtGui
db_config = {'host':'localhost','user':'root','password':'root','database':'trade_db'}
class ProductItem(QtWidgets.QWidget, ItemUI):
    def __init__(self, data, role):
        super().__init__()
        self.setupUi(self)
        self.data = data
        self.name_product.setText(data['name_product'])
        self.category_product.setText(data['name_category'])
        self.desc_product.setText(data['description_product'])
        self.manufacturer_product.setText(data['name_manufacturer'])
        self.delievery_product.setText(data['name_provider'])
        self.old_price_product.setText(f'<s style = "color: red"> {data["price_product"]} руб </s>')
        new_price = float(data["price_product"]) * (1 - float(data["sale_product"] / 100))
        self.new_price_product.setText(f'{new_price} руб')
        self.unit_product.setText(data['name_unit'])
        self.col_sklad_product.setText(str(data['count_in_stock_product']))
        sale = data.get('sale_product')
        if sale and int(sale) > 0:
            self.sale_product.setText(f'Скидка - {sale}%')
            if int(sale) > 15:
                self.setStyleSheet('background-color: green')
        else:
            self.sale_product.setText(f'Скидка - {sale}%')
        photo = data.get('photo_product')
        pix = QtGui.QPixmap(f'resources/icons/{photo}')
        pix2 = QtGui.QPixmap(f'resources/icons/picture.jpg')
        if photo and not pix.isNull():
            self.photo_product.setPixmap(pix.scaled(150,150, QtCore.Qt.AspectRatioMode.KeepAspectRatio))
        else:
            self.photo_product.setPixmap(pix2.scaled(150, 150, QtCore.Qt.AspectRatioMode.KeepAspectRatio))
        if role == "admin":
            self.btn_to_redakt_item.clicked.connect(self.edit_info)
            self.btn_to_delete_product.clicked.connect(self.delete_info)

        else:
            self.btn_to_redakt_item.hide()
            self.btn_to_delete_product.hide()
            self.btn_to_insert_product.hide()
    def edit_info(self):
        main_window = self.window()
        main_window.edit_win = EditMain(self.data, main_window)
        main_window.edit_win.show()
    def delete_info(self):
        reply = QtWidgets.QMessageBox.question(self,'Удалить',"Вы уверены что хотите удалить товар?", QtWidgets.QMessageBox.StandardButton.Yes | QtWidgets.QMessageBox.StandardButton.No)
        if reply == QtWidgets.QMessageBox.StandardButton.No:
            return
        try:
            conn = mysql.connector.connect(**db_config)
            cursor = conn.cursor(dictionary=True)
            try:
                join = "DELETE FROM product WHERE id_product = %s"
                cursor.execute(join,(self.data['id_product'],))
                conn.commit()
            except Exception as e:
                QtWidgets.QMessageBox.warning(self,'Ошибка',f'Нельзя удалить товар, который находится в заказе! - {e}')
            main_window = self.window()
            if hasattr(main_window, 'load_products'):
                main_window.load_products()
        except Exception as e:
            print(f'Не удалось удалить товары - {e}')


class EditMain(QtWidgets.QMainWindow, EditItem):
    def __init__(self, data, main_window):
        super().__init__()
        self.setupUi(self)
        self.data = data
        self.main_window = main_window
        self.input_name.setText(str(data['name_product']))
        self.input_category.setText(str(data['name_category']))
        self.input_desc.setText(str(data['description_product']))
        self.input_mafucaturer.setText(str(data['name_manufacturer']))
        self.input_delivery.setText(str(data['name_provider']))
        self.input_price.setText(str(data['price_product']))
        self.input_unit.setText(str(data['name_unit']))
        self.input_sale.setText(str(data['sale_product']))
        self.input_col_in_sklad.setText(str(data['count_in_stock_product']))
        photo = data.get('photo_product')
        self.current_photo = photo
        pix = QtGui.QPixmap(f'resources/icons/{photo}')
        pix2 = QtGui.QPixmap(f'resources/icons/picture.jpg')
        if photo and not pix.isNull():
            self.choosen_redakt_photo.setPixmap(pix.scaled(120, 120, QtCore.Qt.AspectRatioMode.KeepAspectRatio))
        else:
            self.choosen_redakt_photo.setPixmap(pix2.scaled(120, 120, QtCore.Qt.AspectRatioMode.KeepAspectRatio))
        self.go_to_main_btn.clicked.connect(self.back_to_main)
        self.save_edit_item.clicked.connect(self.save_changed)
        self.btn_choose_photo_redakt.clicked.connect(self.open_photo)
    def back_to_main(self):
        self.close()
    def save_changed(self):
        name = self.input_name.text()
        category = self.input_category.text()
        desc = self.input_desc.text()
        manufacturer = self.input_mafucaturer.text()
        delivery = self.input_delivery.text()
        price = self.input_price.text()
        unit = self.input_unit.text()
        col_sklad = self.input_col_in_sklad.text()
        sale = self.input_sale.text()
        photo = self.current_photo
        try:
            conn = mysql.connector.connect(**db_config)
            cursor = conn.cursor(dictionary=True)
            join = """
                UPDATE product SET 
                    name_product = %s, 
                    id_category = (SELECT id_category FROM category WHERE name_category = %s LIMIT 1), 
                    description_product = %s, 
                    id_manufacturer = (SELECT id_manufacturer FROM manufacturer WHERE name_manufacturer = %s LIMIT 1), 
                    id_provider = (SELECT id_provider FROM provider WHERE name_provider = %s LIMIT 1), 
                    price_product = %s, 
                    id_unit = (SELECT id_unit FROM unit WHERE name_unit = %s LIMIT 1), 
                    count_in_stock_product = %s, 
                    sale_product = %s, 
                    photo_product = %s 
                WHERE id_product = %s
            """
            cursor.execute(join,(name,category,desc,manufacturer,delivery,price,unit,col_sklad,sale,photo, self.data['id_product']))
            conn.commit()
            self.main_window.load_products()
            self.close()
        except Exception as e:
            print(f'Не удалось сохранить записи в БД - {e}')
    def open_photo(self):
        filepath, _ = QtWidgets.QFileDialog.getOpenFileName(self, 'Выбор','','Images: *.png *.jpg *.jpeg')
        if filepath:
            filename = os.path.basename(filepath)
            shutil.copy(filepath, f'resources/icons/{filename}')
            self.current_photo = filename
            pix = QtGui.QPixmap(f'resources/icons/{filename}')
            self.choosen_redakt_photo.setPixmap(pix.scaled(120,120, QtCore.Qt.AspectRatioMode.KeepAspectRatio))
class ProductMain(QtWidgets.QMainWindow, MainUI):
    def __init__(self, role = "admin", guest = "Администратор"):
        super().__init__()
        self.setupUi(self)
        self.user_role = role
        self.user_name_lbl.setText(f'Пользователь - {guest}')
        self.scrollArea.setWidgetResizable(True)
        self.vbox = QtWidgets.QVBoxLayout(self.scrollAreaWidgetContents)
        self.vbox.setAlignment(QtCore.Qt.AlignmentFlag.AlignTop)
        self.search_input.textEdited.connect(self.load_products)
        self.sort_combo.currentIndexChanged.connect(self.load_products)
        self.back_to_auth_btn.clicked.connect(self.go_to_auth)
        if role == "client" or role == "guest" or role == "admin":
            self.search_input.hide()
            self.sort_combo.hide()
        self.load_products()
    def go_to_auth(self):
        from logic_auth import AuthApp
        self.open_auth = AuthApp()
        self.open_auth.show()
        self.close()
    def clear_vbox(self):
        while self.vbox.count():
            item = self.vbox.takeAt(0)
            widget = item.widget()
            if widget:
                widget.deleteLater()
    def load_products(self):
        self.clear_vbox()
        search_text = self.search_input.text()
        sort_combo = self.sort_combo.currentIndex()
        join = """SELECT p.*, c.name_category, m.name_manufacturer, pr.name_provider, u.name_unit FROM product p
        JOIN category c ON c.id_category = p.id_category
        JOIN manufacturer m ON m.id_manufacturer = p.id_manufacturer
        JOIN provider pr ON pr.id_provider = p.id_provider
        JOIN unit u ON u.id_unit = p.id_unit
        WHERE p.name_product LIKE %s OR p.description_product LIKE %s"""
        try:
            conn = mysql.connector.connect(**db_config)
            cursor = conn.cursor(dictionary=True)
            pattern = f'%{search_text}%'
            if sort_combo == 1:
                join += " ORDER BY price_product ASC"
            elif sort_combo == 2:
                join += " ORDER BY price_product DESC"
            cursor.execute(join,(pattern,pattern))
            product = cursor.fetchall()
            for p_data in product:
                card = ProductItem(p_data, self.user_role)
                self.vbox.addWidget(card)
            cursor.close()
            conn.close()
        except Exception as e:
            print(f'Критическая ошибка - {e}')
if __name__ == "__main__":
    app = QtWidgets.QApplication(sys.argv)
    window = ProductMain()
    window.show()
    sys.exit(app.exec())












import sys
from PyQt6 import QtWidgets
import mysql.connector
from auth import Ui_MainWindow
from logic_products import ProductMain
db_config = {'host':'localhost','user':'root','password':'root','database':'trade_db'}
class AuthApp(QtWidgets.QMainWindow, Ui_MainWindow):
    def __init__(self):
        super().__init__()
        self.setupUi(self)
        self.login_btn.clicked.connect(self.handle_auth)
        self.btn_to_guest.clicked.connect(self.guest_mode)
    def guest_mode(self):
        self.open_guest = ProductMain(role = "guest", guest = "Гость")
        self.open_guest.show()
        self.close()
    def handle_auth(self):
        login = self.login_input.text()
        password = self.pass_input.text()
        if not all([login, password]):
            self.msg_label.setText("Заполните все обязательные поля!")
            self.msg_label.setStyleSheet('color: red')
            return
        try:
            conn = mysql.connector.connect(**db_config)
            cursor = conn.cursor(dictionary=True)
            join = "SELECT * FROM user WHERE login_user = %s AND password_user = %s"
            cursor.execute(join,(login, password))
            user = cursor.fetchall()
            if len(user) > 0:
                user_data = user[0]
                if user_data['id_role'] == 1:
                    current_role = "admin"
                elif user_data['id_role'] == 2:
                    current_role = "manager"
                else:
                    current_role = "client"
                fio = f'{user_data.get("surname_user")} {user_data.get("name_user")}'
                self.msg_label.setText('Успешная авторизация!')
                self.msg_label.setStyleSheet('color: green')
                self.open_main = ProductMain(role = current_role,guest = fio)
                self.open_main.show()
                self.close()
            else:
                self.msg_label.setText('Проверьте правильность данных!')
                self.msg_label.setStyleSheet('color: red')
        except Exception as e:
            print(f'Не удалось войти - {e}')
if __name__ == "__main__":
    app = QtWidgets.QApplication(sys.argv)
    window = AuthApp()
    window.show()
    sys.exit(app.exec())











-- Создаем базу данных и переключаемся на нее
CREATE DATABASE IF NOT EXISTS trade_db;
USE trade_db;

-- ==========================================
-- 1. СПРАВОЧНИКИ (независимые таблицы)
-- ==========================================

CREATE TABLE role (
    id_role INT PRIMARY KEY AUTO_INCREMENT,
    name_role VARCHAR(100) NOT NULL
);

CREATE TABLE category (
    id_category INT PRIMARY KEY AUTO_INCREMENT,
    name_category VARCHAR(100) NOT NULL
);

CREATE TABLE manufacturer (
    id_manufacturer INT PRIMARY KEY AUTO_INCREMENT,
    name_manufacturer VARCHAR(100) NOT NULL
);

CREATE TABLE provider (
    id_provider INT PRIMARY KEY AUTO_INCREMENT,
    name_provider VARCHAR(100) NOT NULL
);

CREATE TABLE unit (
    id_unit INT PRIMARY KEY AUTO_INCREMENT,
    name_unit VARCHAR(50) NOT NULL
);

CREATE TABLE pickup_point (
    id_pickup_point INT PRIMARY KEY AUTO_INCREMENT,
    address VARCHAR(255) NOT NULL
);

-- ==========================================
-- 2. ПОЛЬЗОВАТЕЛИ
-- ==========================================

CREATE TABLE user (
    id_user INT PRIMARY KEY AUTO_INCREMENT,
    surname_user VARCHAR(100) NOT NULL,
    name_user VARCHAR(100) NOT NULL,
    patronymic_user VARCHAR(100),
    login_user VARCHAR(100) NOT NULL UNIQUE,
    password_user VARCHAR(100) NOT NULL,
    id_role INT NOT NULL,
    
    -- Связь с таблицей ролей
    FOREIGN KEY (id_role) REFERENCES role(id_role) ON DELETE RESTRICT ON UPDATE CASCADE
);

-- ==========================================
-- 3. ТОВАРЫ
-- ==========================================

CREATE TABLE product (
    id_product INT PRIMARY KEY AUTO_INCREMENT,
    article_product VARCHAR(100) NOT NULL UNIQUE,
    name_product VARCHAR(255) NOT NULL,
    id_category INT NOT NULL,
    description_product TEXT,
    id_manufacturer INT NOT NULL,
    id_provider INT NOT NULL,
    price_product DECIMAL(10, 2) NOT NULL,
    id_unit INT NOT NULL,
    count_in_stock_product INT NOT NULL,
    sale_product INT DEFAULT 0,
    photo_product VARCHAR(255),
    
    -- Внешние ключи для связи со справочниками
    FOREIGN KEY (id_category) REFERENCES category(id_category) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (id_manufacturer) REFERENCES manufacturer(id_manufacturer) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (id_provider) REFERENCES provider(id_provider) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (id_unit) REFERENCES unit(id_unit) ON DELETE RESTRICT ON UPDATE CASCADE
);

-- ==========================================
-- 4. ЗАКАЗЫ И ИХ СОСТАВ
-- ==========================================

CREATE TABLE `order` (
    id_order INT PRIMARY KEY AUTO_INCREMENT,
    order_date DATE NOT NULL,
    order_delivery_date DATE NOT NULL,
    id_pickup_point INT NOT NULL,
    id_user INT, -- Может быть NULL, если заказ сделал гость (зависит от ТЗ)
    order_code INT NOT NULL,
    order_status VARCHAR(50) DEFAULT 'Новый',
    
    -- Внешние связи
    FOREIGN KEY (id_pickup_point) REFERENCES pickup_point(id_pickup_point) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (id_user) REFERENCES user(id_user) ON DELETE SET NULL ON UPDATE CASCADE
);

-- Таблица связи многие-ко-многим (какие товары в каком заказе)
CREATE TABLE order_product (
    id_order INT NOT NULL,
    id_product INT NOT NULL,
    count INT NOT NULL DEFAULT 1,
    
    -- Составной первичный ключ
    PRIMARY KEY (id_order, id_product),
    
    -- Связи
    FOREIGN KEY (id_order) REFERENCES `order`(id_order) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (id_product) REFERENCES product(id_product) ON DELETE RESTRICT ON UPDATE CASCADE
);