import os, sys, psycopg2
from psycopg2.extras import DictCursor
from PyQt6.QtWidgets import (QApplication, QMainWindow, QWidget, QVBoxLayout, QHBoxLayout, QLabel, QLineEdit,
                             QPushButton, QStackedWidget, QListWidget, QListWidgetItem, QComboBox, QMessageBox, QDialog,
                             QFormLayout, QDateEdit, QSpinBox, QDoubleSpinBox)
from PyQt6.QtCore import QDate, Qt
from PyQt6.QtGui import QPixmap, QColor

DB_CFG = {"host": "localhost", "port": 5432, "user": "postgres", "password": "12072005", "dbname": "381KashutinDE"}
IMG_DIR = r"D:\Загрузки\17 июня\17 июня\Прил_В4_КОД 09.02.07-2-2026-ПУ\Модуль 1\import"
CSS = "* { font-family: 'Arial'; font-size: 14px; background-color: #FFFFFF; } QPushButton { background-color: #F5DEB3; font-weight: bold; }"

class DB:
    @staticmethod
    def run(q, p=(), f=None):
        try:
            c = psycopg2.connect(**DB_CFG, cursor_factory=DictCursor)
            cur = c.cursor()
            cur.execute(q, p)
            res = cur.fetchall() if f == 'all' else cur.fetchone() if f == 'one' else c.commit() or True
            c.close()
            return res
        except Exception as e:
            QMessageBox.critical(None, "Ошибка БД", str(e))
            return None

class ProdWidget(QWidget):
    def __init__(self, i, p=None):
        super().__init__(p)
        lay = QHBoxLayout(self)
        img = QLabel()
        pix = QPixmap(os.path.join(IMG_DIR, str(i.get('photo') or '11.jpg').strip() or '11.jpg')).scaled(100, 100)
        img.setPixmap(pix)
        txt = QLabel(
            f"<b>{i['product_name']}</b> (Артикул: {i['sku']})<br>{i['description'] or '-'}<br>{i['manufacturer']} | <b>{i['price']} руб.</b><br>Скидка: {i['discount']}% | В наличии: {i['stock_quantity']}")
        txt.setWordWrap(True)
        lay.addWidget(img)
        lay.addWidget(txt)

class ProdDlg(QDialog):
    def __init__(self, sku=None, p=None):
        super().__init__(p)
        self.sku = sku
        self.setWindowTitle("Редактирование товара")
        self.ui = {
            'sku': QLineEdit(),
            'name': QLineEdit(),
            'desc': QLineEdit(),
            'mfg': QLineEdit(),
            'price': QDoubleSpinBox(),
            'disc': QSpinBox(),
            'stock': QSpinBox()
        }
        self.ui['sku'].setEnabled(not sku)
        self.ui['price'].setRange(0, 999999999)
        self.ui['disc'].setRange(0, 100)
        self.ui['stock'].setRange(0, 99999999)

        lay = QFormLayout(self)
        labels = ["Артикул:", "Название:", "Описание:", "ПОставшик:", "Цена:", "Скидка:", "Кол-во:"]
        for l, k in zip(labels, self.ui.keys()):
            lay.addRow(l, self.ui[k])

        b1, b2 = QPushButton("Сохранить"), QPushButton("Отмена")
        b1.clicked.connect(self.save)
        b2.clicked.connect(self.reject)
        lay.addRow(b1, b2)

        if sku and (r := DB.run("SELECT * FROM products WHERE sku = %s;", (sku,), 'one')):
            self.ui['sku'].setText(r['sku'])
            self.ui['name'].setText(r['product_name'])
            self.ui['desc'].setText(r['description'] or '')
            self.ui['mfg'].setText(r['manufacturer'] or '')
            self.ui['price'].setValue(float(r['price']))
            self.ui['disc'].setValue(r['discount'] or 0)
            self.ui['stock'].setValue(r['stock_quantity'] or 0)

    def save(self):
        v = {k: w.text() if isinstance(w, QLineEdit) else w.value() for k, w in self.ui.items()}
        if not v['sku'] or not v['name']:
            return QMessageBox.warning(self, "!", "Заполните Артикул и Название")

        if self.sku:
            DB.run(
                "UPDATE products SET product_name=%s, description=%s, manufacturer=%s, price=%s, discount=%s, stock_quantity=%s WHERE sku=%s;",
                (v['name'], v['desc'], v['mfg'], v['price'], v['disc'], v['stock'], self.sku))
        else:
            if DB.run("SELECT 1 FROM products WHERE sku=%s;", (v['sku'],), 'one'):
                return QMessageBox.warning(self, "!", "Товар с таким артикулом уже есть")
            DB.run(
                "INSERT INTO products (sku, product_name, description, manufacturer, price, discount, stock_quantity) VALUES (%s, %s, %s, %s, %s, %s, %s);",
                (v['sku'], v['name'], v['desc'], v['mfg'], v['price'], v['disc'], v['stock']))
        self.accept()

class OrderDlg(QDialog):
    def __init__(self, oid=None, p=None):
        super().__init__(p)
        self.oid = oid
        self.ui = {'id': QSpinBox(), 'd1': QDateEdit(QDate.currentDate()),
                   'd2': QDateEdit(QDate.currentDate().addDays(3)), 'pt': QComboBox(), 'cl': QComboBox(),
                   'cd': QSpinBox(), 'st': QComboBox()}
        self.ui['id'].setRange(1, 999999)
        self.ui['id'].setValue(oid or 1)
        self.ui['id'].setEnabled(not oid)
        self.ui['cd'].setRange(100, 999)
        self.ui['st'].addItems(["Новый", "Завершен"])

        lay = QFormLayout(self)
        for l, k in zip(["№:", "Дата:", "Доставка:", "Пункт:", "Клиент:", "Код получения:", "Статус:"],
                        self.ui.keys()): lay.addRow(l, self.ui[k])

        b1, b2 = QPushButton("Сохранить"), QPushButton("Отмена")
        b1.clicked.connect(self.save)
        b2.clicked.connect(self.reject)
        lay.addRow(b1, b2)

        for r in DB.run("SELECT point_id, address FROM pickup_points;", f='all') or []: self.ui['pt'].addItem(
            r['address'], r['point_id'])
        for r in DB.run("SELECT user_id, fio FROM users WHERE role_id = 3;", f='all') or []: self.ui['cl'].addItem(
            r['fio'], r['user_id'])

        if oid and (r := DB.run("SELECT * FROM orders WHERE order_id = %s;", (oid,), 'one')):
            self.ui['d1'].setDate(r['order_date'])
            self.ui['d2'].setDate(r['delivery_date'])
            self.ui['pt'].setCurrentIndex(self.ui['pt'].findData(r['pickup_point_id']))
            self.ui['cl'].setCurrentIndex(self.ui['cl'].findData(r['client_id']))
            self.ui['cd'].setValue(r['pickup_code'])
            self.ui['st'].setCurrentText(r['order_status'])

    def save(self):
        v = {k: w.currentData() if k in ('pt', 'cl') else
        w.currentText() if k == 'st' else
        w.date().toPyDate() if k in ('d1', 'd2') else
        w.value() for k, w in self.ui.items()}

        if not v['pt'] or not v['cl']:
            return QMessageBox.warning(self, "!", "Заполните списки")
        if self.oid:
            DB.run(
                "UPDATE orders SET order_date=%s, delivery_date=%s, pickup_point_id=%s, client_id=%s, pickup_code=%s, order_status=%s WHERE order_id=%s;",
                (v['d1'], v['d2'], v['pt'], v['cl'], v['cd'], v['st'], self.oid))
        elif DB.run("SELECT 1 FROM orders WHERE order_id=%s;", (v['id'],), 'one'):
            return QMessageBox.warning(self, "!", "Такой заказ уже есть")
        else:
            DB.run(
                "INSERT INTO orders (order_id, order_date, delivery_date, pickup_point_id, client_id, pickup_code, order_status) VALUES (%s, %s, %s, %s, %s, %s, %s);",
                (v['id'], v['d1'], v['d2'], v['pt'], v['cl'], v['cd'], v['st']))
        self.accept()

class MainWin(QMainWindow):
    def __init__(self):
        super().__init__()
        self.stk = QStackedWidget()
        self.setCentralWidget(self.stk)

        lw, mw = QWidget(), QWidget()
        ll, ml = QVBoxLayout(lw), QVBoxLayout(mw)

        self.l_in, self.p_in = QLineEdit(placeholderText="Логин"), QLineEdit(placeholderText="Пароль",
                                                                             echoMode=QLineEdit.EchoMode.Password)
        b_in, b_g = QPushButton("Войти"), QPushButton("Войти как гость")
        b_in.clicked.connect(self.log)
        b_g.clicked.connect(lambda: self.set_u({"role_id": 0, "fio": "Гость"}))
        for w in (QLabel("ВХОД"), self.l_in, self.p_in, b_in, b_g): ll.addWidget(w)

        self.info, self.b_vw, b_out = QLabel(), QPushButton("Заказы"), QPushButton("Выйти")
        self.b_vw.clicked.connect(self.tg)
        b_out.clicked.connect(self.logout)
        hl = QHBoxLayout()
        hl.addWidget(self.info)
        hl.addWidget(self.b_vw)
        hl.addWidget(b_out)
        ml.addLayout(hl)

        self.sub = QStackedWidget()
        pw, ow = QWidget(), QWidget()
        pl, ol = QVBoxLayout(pw), QVBoxLayout(ow)

        self.srch, self.srt, self.mfg = QLineEdit(placeholderText="Поиск"), QComboBox(), QComboBox()
        self.srt.addItems(["Без сортировки", "Цена (возр.)", "Цена (убыв.)"])
        for w in (self.srch, self.srt, self.mfg):
            pl.addWidget(w)
            if w != self.mfg: w.textChanged.connect(self.l_pr) if w == self.srch else w.currentIndexChanged.connect(
                self.l_pr)
        self.mfg.currentIndexChanged.connect(self.l_pr)

        self.p_lst = QListWidget()
        pl.addWidget(self.p_lst)

        self.b_edit_prod = QPushButton("Редактировать товар")
        self.b_edit_prod.clicked.connect(self.ed_prod)
        pl.addWidget(self.b_edit_prod)

        self.btns = [QPushButton(t) for t in ("Добавить", "Изменить", "Удалить")]
        self.btns[0].clicked.connect(lambda: self.ed(0))
        self.btns[1].clicked.connect(lambda: self.ed(1))
        self.btns[2].clicked.connect(self.dl)
        for b in self.btns: ol.addWidget(b)
        self.o_lst = QListWidget()
        ol.addWidget(self.o_lst)

        self.sub.addWidget(pw)
        self.sub.addWidget(ow)
        ml.addWidget(self.sub)
        self.stk.addWidget(lw)
        self.stk.addWidget(mw)

    def log(self):
        if r := DB.run("SELECT * FROM users WHERE login=%s AND password=%s;", (self.l_in.text(), self.p_in.text()),
                       'one'):
            self.set_u(dict(r))
        else:
            QMessageBox.warning(self, "!", "Ошибка входа")

    def set_u(self, d):
        self.u = d
        self.info.setText(d['fio'])
        for w in (self.srch, self.srt, self.mfg, self.b_vw): w.setVisible(d.get('role_id') in [1, 2])
        for b in self.btns: b.setVisible(d.get('role_id') == 1)
        self.b_edit_prod.setVisible(d.get('role_id') == 1)

        self.mfg.blockSignals(True)
        self.mfg.clear()
        self.mfg.addItem("Все поставщики")
        for m in DB.run("SELECT DISTINCT manufacturer FROM products WHERE manufacturer IS NOT NULL;",
                        f='all') or []: self.mfg.addItem(m[0])
        self.mfg.blockSignals(False)

        self.sub.setCurrentIndex(0)
        self.b_vw.setText("Заказы")
        self.l_pr()
        self.stk.setCurrentIndex(1)
        self.showMaximized()

    def logout(self):
        self.stk.setCurrentIndex(0)
        self.showNormal()
        self.resize(400, 300)

    def tg(self):
        p = self.sub.currentIndex() == 0
        self.sub.setCurrentIndex(int(p))
        self.b_vw.setText("Товары" if p else "Заказы")
        self.l_ord() if p else self.l_pr()

    def l_pr(self):
        self.p_lst.clear()
        q, p = "SELECT * FROM products WHERE 1=1", []
        if self.u.get('role_id') in [1, 2]:
            if t := self.srch.text(): q += " AND product_name ILIKE %s"; p.append(f"%{t}%")
            if (m := self.mfg.currentText()) != "Все поставщики": q += " AND manufacturer=%s"; p.append(m)
            q += ["", " ORDER BY price ASC", " ORDER BY price DESC"][self.srt.currentIndex()]

        for i in DB.run(q, p, 'all') or []:
            li = QListWidgetItem(self.p_lst)
            li.setData(Qt.ItemDataRole.UserRole, i['sku'])
            w = ProdWidget(i)
            li.setSizeHint(w.sizeHint())
            if i['discount'] > 17: li.setBackground(QColor("#FFDEAD"))
            self.p_lst.setItemWidget(li, w)

    def ed_prod(self):
        if not self.p_lst.currentItem():
            return QMessageBox.warning(self, "!", "Выберите товар для редактирования!")
        sku = self.p_lst.currentItem().data(Qt.ItemDataRole.UserRole)
        dlg = ProdDlg(sku, self)
        if dlg.exec():
            self.l_pr()

    def l_ord(self):
        self.o_lst.clear()
        q = "SELECT o.*, p.address, u.fio, COALESCE(string_agg(oi.sku || ' (' || oi.quantity || ' шт)', ', '), '-') as items FROM orders o LEFT JOIN pickup_points p ON o.pickup_point_id=p.point_id LEFT JOIN users u ON o.client_id=u.user_id LEFT JOIN order_items oi ON o.order_id=oi.order_id GROUP BY o.order_id, p.address, u.fio ORDER BY o.order_id DESC;"
        for o in DB.run(q, f='all') or []:
            i = QListWidgetItem(
                f"№ {o['order_id']} | Статус: {o['order_status']} | Доставка: {o['delivery_date']}\nПункт: {o['address']}\nКлиент: {o['fio']}\nТовары: {o['items']}")
            i.setData(Qt.ItemDataRole.UserRole, o['order_id'])
            self.o_lst.addItem(i)

    def ed(self, e=0):
        if e and not self.o_lst.currentItem(): return QMessageBox.warning(self, "!", "Выберите заказ")
        oid = self.o_lst.currentItem().data(Qt.ItemDataRole.UserRole) if e else None

        dlg = OrderDlg(oid, self)
        if dlg.exec(): self.l_ord()

    def dl(self):
        if (i := self.o_lst.currentItem()) and QMessageBox.question(self, "?",
                                                                    "Удалить?") == QMessageBox.StandardButton.Yes:
            if DB.run("DELETE FROM orders WHERE order_id=%s;", (i.data(Qt.ItemDataRole.UserRole),)): self.l_ord()

if __name__ == "__main__":
    app = QApplication(sys.argv)
    app.setStyleSheet(CSS)
    w = MainWin()
    w.resize(400, 300)
    w.show()
    sys.exit(app.exec())