---

CREATE DATABASE IF NOT EXISTS exam_shop;
USE exam_shop;

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

CREATE TABLE user (
    id_user INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(45) NOT NULL,
    login VARCHAR(45) NOT NULL UNIQUE,
    password VARCHAR(45) NOT NULL,
    role_id INT,
    FOREIGN KEY (role_id) REFERENCES role(id_role)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

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

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

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

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

CREATE TABLE product (
    id_product INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT,
    name VARCHAR(45) NOT NULL,
    description VARCHAR(70),
    manufacturer_id INT,
    provider_id INT,
    price DECIMAL(10,2) NOT NULL,
    unit_id INT,
    quantity INT NOT NULL,
    discount INT DEFAULT 0,
    photo VARCHAR(70),

    FOREIGN KEY (category_id) REFERENCES category(id_category)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,

    FOREIGN KEY (manufacturer_id) REFERENCES manufacturer(id_manufacturer)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,

    FOREIGN KEY (provider_id) REFERENCES provider(id_provider)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,

    FOREIGN KEY (unit_id) REFERENCES unit(id_unit)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

CREATE TABLE status_order (
    id_status_order INT AUTO_INCREMENT PRIMARY KEY,
    status_order VARCHAR(45) NOT NULL
);

CREATE TABLE orders (
    id_orders INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    product_id INT,
    status_order_id INT,
    adress VARCHAR(45),
    date_order DATE,
    date_deliver DATE,

    FOREIGN KEY (user_id) REFERENCES user(id_user)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    FOREIGN KEY (product_id) REFERENCES product(id_product)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    FOREIGN KEY (status_order_id) REFERENCES status_order(id_status_order)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

import sys
import os
import pymysql
from PyQt6 import QtWidgets, QtGui, QtCore
from resources.ui.main_ui import Ui_MainWindow
from resources.ui.auth_ui import Ui_Form


def get_conn():
    conn = pymysql.connect(
        host='localhost',
        user='root',
        password='',
        database='boots',
        charset='utf8',
    )
    cursor = conn.cursor()
    return conn, cursor


class AuthWindow(QtWidgets.QWidget, Ui_Form):
    def __init__(self):
        super().__init__()
        self.setupUi(self)
        self.setWindowTitle("Авторизация")
        self.setWindowIcon(QtGui.QIcon('resources/icons/iconn.ico'))
        self.setStyleSheet("background-color:#FFFFFF")

        self.btn_exit.clicked.connect(self.close)
        self.btn_enter.clicked.connect(self.enter)
        self.btn_guest.clicked.connect(self.guest)

    def enter(self):
        if self.le_login.text() == "" and self.le_password.text() == "":
            QtWidgets.QMessageBox.information(self, "Введите данные", "Введите логин и пароль")
        else:
            try:
                conn, cursor = get_conn()
                cursor.execute(
                    "SELECT id_user, username, role_id FROM user WHERE login = %s AND password = %s",
                    (self.le_login.text(), self.le_password.text())
                )
                user = cursor.fetchone()
                cursor.close()
                conn.close()

                if user:
                    id_user, username, role_id = user
                    self.open_main_window(id_user, username, role_id)
                else:
                    QtWidgets.QMessageBox.warning(self, "Ошибка", "Введите правильные логин и пароль")
            except Exception as e:
                QtWidgets.QMessageBox.critical(self, "Критическая ошибка", f"ошибка: {e}")

    def guest(self):
        self.open_main_window(0, "Гость", 1)

    def open_main_window(self, id_user, username, role_id):
        self.main = MainWindow(id_user, username, role_id)
        self.main.show()
        self.close()


class MainWindow(QtWidgets.QMainWindow, Ui_MainWindow):
    def __init__(self, id_user, username, role_id):
        super().__init__()
        self.setupUi(self)
        self.setWindowTitle("Основное окно")
        self.setWindowIcon(QtGui.QIcon('resources/icons/iconn.ico'))
        self.btn_exit.clicked.connect(self.exit)
        self.lbl_username.setText(username)

        self.btn_add.setStyleSheet("background-color: #00FA9A")
        self.btn_exit.setStyleSheet("background-color: #00FA9A")
        self.setStyleSheet("background-color:#FFFFFF")
        self.scrollArea.setStyleSheet("background-color:#7FFF00")
        self.scrollArea_2.setStyleSheet("background-color:#7FFF00")

        self.id_user = id_user
        self.role_id = role_id
        order_tab = self.tabWidget.indexOf(self.tabOrders)

        if role_id in (1, 2):
            self.le_search.setVisible(False)
            self.cmbx_filtr.setVisible(False)
            self.cmbx_sort.setVisible(False)
            self.btn_add.setVisible(False)
            self.tabWidget.setTabVisible(order_tab, False)
        elif role_id == 3:
            self.btn_add.setVisible(False)
            self.tabWidget.setTabVisible(order_tab, True)
        elif role_id == 4:
            self.tabWidget.setTabVisible(order_tab, True)

        self.le_search.textChanged.connect(self.load_data)
        self.cmbx_filtr.currentIndexChanged.connect(self.load_data)
        self.cmbx_sort.currentIndexChanged.connect(self.load_data)

        self.load_data()
        if role_id in (3, 4):
            self.load_orders()

    def exit(self):
        self.auth = AuthWindow()
        self.auth.show()
        self.close()

    def clear_data(self):
        layout = self.scrollAreaWidgetProducts.layout()
        while layout.count():
            item = layout.takeAt(0)
            widget = item.widget()
            if widget:
                widget.deleteLater()

    def load_data(self):
        self.clear_data()
        search = self.le_search.text().strip()

        query = """
        SELECT p.id_product, c.category, p.name, p.description, m.manufacturer,
               pr.provider, p.price, u.unit, p.quantity, p.discount, p.photo
        FROM product p
        JOIN category c ON p.category_id = c.id_category
        JOIN manufacturer m ON p.manufacturer_id = m.id_manufacturer
        JOIN provider pr ON p.provider_id = pr.id_provider
        JOIN unit u ON p.unit_id = u.id_unit
        WHERE p.name LIKE %s
        """
        params = [f"%{search}%"]

        if self.cmbx_filtr.currentIndex() == 1:
            query += " AND pr.provider = %s"
            params.append("Биг")
        elif self.cmbx_filtr.currentIndex() == 2:
            query += " AND pr.provider = %s"
            params.append("Мини")

        if self.cmbx_sort.currentIndex() == 1:
            query += " ORDER BY p.quantity DESC"
        elif self.cmbx_sort.currentIndex() == 2:
            query += " ORDER BY p.quantity ASC"

        conn, cursor = get_conn()
        cursor.execute(query, params)
        data = cursor.fetchall()
        cursor.close()
        conn.close()

        for row in data:
            self.add_card(row)

        self.scrollAreaWidgetProducts.layout().addStretch()

    def delete_product(self, product_id):
        if self.role_id != 4:
            return

        msg = QtWidgets.QMessageBox(self)
        msg.setWindowTitle("Удаление")
        msg.setText("Удалить данную запись?")
        yes_btn = msg.addButton("Да", QtWidgets.QMessageBox.ButtonRole.YesRole)
        msg.addButton("Нет", QtWidgets.QMessageBox.ButtonRole.NoRole)
        msg.exec()

        if msg.clickedButton() == yes_btn:
            conn, cursor = get_conn()

            cursor.execute("SELECT COUNT(*) FROM orders WHERE product_id = %s", (product_id,))
            count = cursor.fetchone()[0]

            if count > 0:
                QtWidgets.QMessageBox.warning(
                    self,
                    "Ошибка",
                    "Товар присутствует в заказах и не может быть удалён"
                )
                cursor.close()
                conn.close()
                return

            cursor.execute("DELETE FROM product WHERE id_product = %s", (product_id,))
            conn.commit()
            cursor.close()
            conn.close()
            self.load_data()

    def add_card(self, data):
        frame = QtWidgets.QFrame()
        frame.setFrameShape(QtWidgets.QFrame.Shape.Box)
        frame.setFixedSize(700, 150)

        frame.mouseDoubleClickEvent = lambda event, pid=data[0]: self.delete_product(pid)

        if data[8] == 0:
            frame.setStyleSheet("background-color: blue")
        elif data[9] > 15:
            frame.setStyleSheet("background-color: #2E8B57")

        layout = QtWidgets.QHBoxLayout(frame)

        img_lbl = QtWidgets.QLabel()
        img_lbl.setScaledContents(True)
        img_lbl.setFixedSize(100, 100)

        image_path = os.path.join("resources", "images", data[10] if data[10] else "")
        placeholder_path = os.path.join("resources", "images", "placeholder.jpg")

        if os.path.exists(image_path) and data[10]:
            img_lbl.setPixmap(QtGui.QPixmap(image_path))
        else:
            img_lbl.setPixmap(QtGui.QPixmap(placeholder_path))

        layout.addWidget(img_lbl)

        info_layout = QtWidgets.QVBoxLayout()
        info_layout.addWidget(QtWidgets.QLabel(f"Категория товара: {data[1]} | Наименование товара: {data[2]}"))
        info_layout.addWidget(QtWidgets.QLabel(f"Описание товара: {data[3]}"))
        info_layout.addWidget(QtWidgets.QLabel(f"Производитель: {data[4]}"))
        info_layout.addWidget(QtWidgets.QLabel(f"Поставщик: {data[5]}"))

        old_price = float(data[6])
        discount = int(data[9])
        price_layout = QtWidgets.QHBoxLayout()

        if discount > 0:
            new_price = old_price * (1 - discount / 100)
            old_lbl = QtWidgets.QLabel(f"Цена: {old_price:.2f} р")
            font = old_lbl.font()
            font.setStrikeOut(True)
            old_lbl.setFont(font)
            old_lbl.setStyleSheet("color:red")
            new_lbl = QtWidgets.QLabel(f"{new_price:.2f} р")
            price_layout.addWidget(old_lbl)
            price_layout.addWidget(new_lbl)
        else:
            price_layout.addWidget(QtWidgets.QLabel(f"Цена: {old_price:.2f} р"))

        info_layout.addLayout(price_layout)
        info_layout.addWidget(QtWidgets.QLabel(f"Единица измерения: {data[7]}"))
        info_layout.addWidget(QtWidgets.QLabel(f"Количество на складе: {data[8]}"))

        layout.addLayout(info_layout)
        layout.addStretch()
        layout.addWidget(QtWidgets.QLabel(f"{data[9]}%"))

        self.scrollAreaWidgetProducts.layout().addWidget(frame)

    def clear_orders(self):
        layout = self.scrollAreaWidgetOrders.layout()
        while layout.count():
            item = layout.takeAt(0)
            widget = item.widget()
            if widget:
                widget.deleteLater()

    def load_orders(self):
        self.clear_orders()
        conn, cursor = get_conn()
        cursor.execute("""
            SELECT o.id_orders, u.username, p.name, s.status_order,
                   o.adress, o.date_order, o.date_deliver
            FROM orders o
            JOIN user u ON o.user_id = u.id_user
            JOIN product p ON o.product_id = p.id_product
            JOIN status_order s ON o.status_order_id = s.id_status_order
        """)
        data = cursor.fetchall()
        cursor.close()
        conn.close()

        for row in data:
            self.add_order(row)

        self.scrollAreaWidgetOrders.layout().addStretch()

    def add_order(self, data):
        frame = QtWidgets.QFrame()
        frame.setFrameShape(QtWidgets.QFrame.Shape.Box)
        frame.setFixedSize(700, 150)

        layout = QtWidgets.QHBoxLayout(frame)
        info_layout = QtWidgets.QVBoxLayout()

        info_layout.addWidget(QtWidgets.QLabel(f"Артикул заказа: {data[0]}"))
        info_layout.addWidget(QtWidgets.QLabel(f"Статус заказа: {data[3]}"))
        info_layout.addWidget(QtWidgets.QLabel(f"Адрес пункта выдачи: {data[4]}"))
        info_layout.addWidget(QtWidgets.QLabel(f"Дата заказа: {data[5]}"))

        layout.addLayout(info_layout)
        layout.addStretch()
        layout.addWidget(QtWidgets.QLabel(f"Дата доставки: {data[6]}"))

        self.scrollAreaWidgetOrders.layout().addWidget(frame)


if __name__ == "__main__":
    app = QtWidgets.QApplication(sys.argv)
    window = AuthWindow()
    window.show()
    sys.exit(app.exec())