theatr/theatre_app.py

"""
Театр — Приложение для управления базой данных
PyQt6 + PostgreSQL (psycopg2)
Python 3.10+
"""

import sys
import csv
import hashlib
import os
from datetime import datetime
from typing import Optional

import psycopg2
import psycopg2.extras
from PyQt6.QtWidgets import (
    QApplication, QMainWindow, QWidget, QVBoxLayout, QHBoxLayout,
    QTabWidget, QTableWidget, QTableWidgetItem, QPushButton, QLabel,
    QLineEdit, QComboBox, QDateTimeEdit, QDoubleSpinBox, QSpinBox,
    QDialog, QFormLayout, QDialogButtonBox, QMessageBox, QFileDialog,
    QGroupBox, QTextEdit, QSplitter, QHeaderView, QFrame, QStatusBar,
    QInputDialog, QCheckBox, QListWidget
)
from PyQt6.QtCore import Qt, QDateTime, QThread, pyqtSignal, QTimer
from PyQt6.QtGui import QColor, QFont, QIcon, QPalette

# ── Настройки подключения ──────────────────────────────────────────────────
DB_CONFIG = {
    "host":     os.environ.get("DB_HOST", "edu-pg.itiscaf.ru"),
    "port":     int(os.environ.get("DB_PORT", 5432)),
    "dbname":   os.environ.get("DB_NAME", "dbwork"),
    "user":     os.environ.get("DB_USER", "sokolov_ta"),
    "password": os.environ.get("DB_PASS", "%4aH&Dz6a5#"),
    "connect_timeout": 5,
    "options": "-c search_path=sokolov_ta,public"
}

APP_ROLES = {
    "admin":    "Администратор",
    "cashier":  "Кассир",
    "buyer":    "Покупатель",
}

BLUE      = "#2d6eaa"
BLUE_DARK = "#1a4a7a"
LIGHT_BG  = "#f4f6f9"
ACCENT    = "#e8f4fd"

STYLE = f"""
QMainWindow, QDialog, QWidget {{ background: {LIGHT_BG}; color: #222; }}
QTabWidget::pane {{ border: 1px solid #c8d6e5; background: white; border-radius: 4px; }}
QTabBar::tab {{ background: #dce8f5; color: #333; padding: 8px 18px; border-radius: 4px 4px 0 0; margin-right: 2px; }}
QTabBar::tab:selected {{ background: {BLUE}; color: white; }}
QPushButton {{
    background: {BLUE}; color: white; border: none;
    padding: 7px 18px; border-radius: 5px; font-weight: bold;
}}
QPushButton:hover {{ background: {BLUE_DARK}; }}
QPushButton:pressed {{ background: #0f3358; }}
QPushButton:disabled {{ background: #aabdd4; color: #ddd; }}
QPushButton#danger {{ background: #c0392b; color: white; }}
QPushButton#danger:hover {{ background: #a93226; }}
QPushButton#success {{ background: #27ae60; color: white; }}
QPushButton#success:hover {{ background: #1e8449; }}
QPushButton#secondary {{ background: #6c8eaf; color: white; }}
QPushButton#secondary:hover {{ background: #526a87; }}
QLineEdit, QComboBox, QDateTimeEdit, QDoubleSpinBox, QSpinBox {{
    border: 1px solid #b0c4d8; border-radius: 4px;
    padding: 5px 8px; background: white; color: #222;
}}
QLineEdit:focus, QComboBox:focus {{ border-color: {BLUE}; }}
QComboBox QAbstractItemView {{ background: white; color: #222; selection-background-color: {ACCENT}; selection-color: #1a4a7a; }}
QTableWidget {{ border: 1px solid #c8d6e5; gridline-color: #e0e8f0; background: white; color: #222; }}
QTableWidget::item {{ color: #222; }}
QTableWidget::item:selected {{ background: {ACCENT}; color: #1a4a7a; }}
QHeaderView::section {{
    background: {BLUE}; color: white;
    padding: 6px; border: none; font-weight: bold;
}}
QGroupBox {{ border: 1px solid #c8d6e5; border-radius: 6px; margin-top: 10px; padding-top: 8px; color: #222; }}
QGroupBox::title {{ subcontrol-origin: margin; left: 10px; color: {BLUE}; font-weight: bold; }}
QLabel {{ color: #222; }}
QLabel#header {{ font-size: 18px; font-weight: bold; color: {BLUE_DARK}; }}
QLabel#hint {{ color: #666; font-size: 11px; }}
QStatusBar {{ background: {BLUE_DARK}; color: white; padding: 2px 8px; }}
QDialogButtonBox QPushButton {{ min-width: 80px; }}
QListWidget {{ background: white; color: #222; border: 1px solid #b0c4d8; border-radius: 4px; }}
QListWidget::item:selected {{ background: {ACCENT}; color: #1a4a7a; }}
"""

# ══════════════════════════════════════════════════════════════════════════════
# DATABASE LAYER
# ══════════════════════════════════════════════════════════════════════════════

class Database:
    def __init__(self):
        self.conn: Optional[psycopg2.extensions.connection] = None

    def connect(self, config: dict) -> bool:
        try:
            self.conn = psycopg2.connect(**config)
            self.conn.autocommit = False
            return True
        except Exception as e:
            print(f"DB connect error: {e}")
            return False

    def disconnect(self):
        if self.conn:
            self.conn.close()
            self.conn = None

    def execute(self, sql: str, params=None) -> list:
        """Выполнить запрос и вернуть строки."""
        with self.conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) as cur:
            cur.execute(sql, params)
            self.conn.commit()
            try:
                return cur.fetchall()
            except psycopg2.ProgrammingError:
                return []

    def execute_many(self, sql: str, data: list):
        with self.conn.cursor() as cur:
            psycopg2.extras.execute_batch(cur, sql, data)
        self.conn.commit()

    def verify_password(self, email: str, password: str):
        rows = self.execute(
            """
            SELECT user_id, last_name, name, mid_name, role, is_active
            FROM users
            WHERE email = %s AND password_hash = %s
            """,
            (email, password)
        )

        if rows and rows[0]["is_active"]:
            return dict(rows[0])

        return None

    # ── TICKETS ──
    def get_tickets(self, search: str = "", status: str = "", show_id: int = 0) -> list:
        sql = """
            SELECT t.ticket_id, s.title AS show_title, t.event_datetime,
                   h.name AS hall, se.row_number, se.seat_number, se.category,
                   t.price, t.status, a.manager_name
            FROM tickets t
            JOIN shows s    ON t.show_id   = s.show_id
            JOIN seats se   ON t.seat_id   = se.seat_id
            JOIN halls h    ON se.hall_id  = h.hall_id
            JOIN accounting a ON t.accounting_id = a.accounting_id
            WHERE 1=1
        """
        params = []
        if search:
            sql += " AND (s.title ILIKE %s OR se.category ILIKE %s)"
            params += [f"%{search}%", f"%{search}%"]
        if status:
            sql += " AND t.status = %s"
            params.append(status)
        if show_id:
            sql += " AND t.show_id = %s"
            params.append(show_id)
        sql += " ORDER BY t.event_datetime DESC, t.ticket_id"
        return self.execute(sql, params or None)

    def add_ticket(self, data: dict) -> bool:
        try:
            self.execute(
                """INSERT INTO tickets (accounting_id, show_id, seat_id, event_datetime, price, status)
                   VALUES (%(accounting_id)s, %(show_id)s, %(seat_id)s,
                           %(event_datetime)s, %(price)s, %(status)s)""",
                data
            )
            return True
        except Exception as e:
            self.conn.rollback()
            raise e

    def update_ticket(self, ticket_id: int, data: dict) -> bool:
        try:
            self.execute(
                """UPDATE tickets SET show_id=%(show_id)s, seat_id=%(seat_id)s,
                          event_datetime=%(event_datetime)s, price=%(price)s, status=%(status)s
                   WHERE ticket_id=%(ticket_id)s""",
                {**data, "ticket_id": ticket_id}
            )
            return True
        except Exception as e:
            self.conn.rollback()
            raise e

    def delete_ticket(self, ticket_id: int):
        # Нельзя удалить проданный билет
        rows = self.execute("SELECT status FROM tickets WHERE ticket_id=%s", (ticket_id,))
        if rows and rows[0]["status"] == "продан":
            raise ValueError("Нельзя удалить проданный билет!")
        self.execute("DELETE FROM tickets WHERE ticket_id=%s", (ticket_id,))

    # ── SALES ──
    def get_sales(self, search: str = "") -> list:
        sql = """
            SELECT sa.sale_id, u.last_name || ' ' || u.name || ' ' || u.mid_name AS buyer, u.email,
                   sh.title AS show_title, t.event_datetime,
                   t.price, sa.amount_paid, sa.payment_method, sa.sale_datetime
            FROM sales sa
            JOIN users u   ON sa.user_id   = u.user_id
            JOIN tickets t ON sa.ticket_id = t.ticket_id
            JOIN shows sh  ON t.show_id    = sh.show_id
        """
        params = []
        if search:
            sql += """WHERE
                last_name ILIKE %s OR name ILIKE %s OR mid_name ILIKE %s OR email ILIKE %s"""
            params = [    f"%{search}%", f"%{search}%", f"%{search}%", f"%{search}%"]
        sql += " ORDER BY sa.sale_datetime DESC"
        return self.execute(sql, params or None)

    def sell_ticket(self, ticket_id: int, user_id: int, method: str) -> bool:
        rows = self.execute("SELECT price, status FROM tickets WHERE ticket_id=%s", (ticket_id,))
        if not rows:
            raise ValueError("Билет не найден")
        t = rows[0]
        if t["status"] != "доступен":
            raise ValueError(f"Билет недоступен (статус: {t['status']})")
        try:
            self.execute(
                "UPDATE tickets SET status='продан' WHERE ticket_id=%s", (ticket_id,)
            )
            self.execute(
                """INSERT INTO sales (ticket_id, user_id, payment_method, amount_paid)
                   VALUES (%s, %s, %s, %s)""",
                (ticket_id, user_id, method, t["price"])
            )
            return True
        except Exception as e:
            self.conn.rollback()
            raise e

    # ── USERS ──
    def get_users(self, search: str = "") -> list:
        sql = """SELECT user_id, last_name, name, mid_name, birthdate, phone, email, role, is_active, created_at
                 FROM users"""
        params = []
        if search:
            sql += " WHERE last_name || ' ' || name || ' ' || mid_name ILIKE %s OR email ILIKE %s"
            params = [f"%{search}%", f"%{search}%"]
        sql += " ORDER BY last_name, name, mid_name"
        return self.execute(sql, params or None)

    def add_user(self, data: dict) -> bool:
        try:
            self.execute(
                """INSERT INTO users (last_name, name, mid_name, birthdate, phone, email, password_hash, role)
                   VALUES (%(last_name)s, %(name)s, %(mid_name)s, %(birthdate)s, %(phone)s, %(email)s,
                           %(password)s, %(role)s)""",
                data
            )
            return True
        except Exception as e:
            self.conn.rollback()
            raise e

    def toggle_user_active(self, user_id: int, active: bool):
        self.execute("UPDATE users SET is_active=%s WHERE user_id=%s", (active, user_id))

    # ── SHOWS ──
    def get_shows(self) -> list:
        return self.execute("SELECT * FROM shows ORDER BY title")

    # ── LOOKUPS ──
    def get_accounting_list(self) -> list:
        return self.execute("SELECT accounting_id, manager_name FROM accounting ORDER BY manager_name")

    def get_seats_by_hall(self, hall_id: int) -> list:
        return self.execute(
            "SELECT seat_id, row_number, seat_number, category FROM seats WHERE hall_id=%s ORDER BY row_number, seat_number",
            (hall_id,)
        )

    def get_halls(self) -> list:
        return self.execute("SELECT * FROM halls ORDER BY name")

    # ── REPORTS ──
    def revenue_by_show(self) -> list:
        return self.execute("""
            SELECT sh.title, COUNT(*) AS sold,
                   SUM(sa.amount_paid) AS revenue
            FROM sales sa
            JOIN tickets t ON sa.ticket_id = t.ticket_id
            JOIN shows sh  ON t.show_id    = sh.show_id
            GROUP BY sh.title ORDER BY revenue DESC
        """)

    def get_audit_log(self, limit=50) -> list:
        return self.execute(
            "SELECT * FROM audit_log ORDER BY changed_at DESC LIMIT %s", (limit,)
        )


# ══════════════════════════════════════════════════════════════════════════════
# DIALOGS
# ══════════════════════════════════════════════════════════════════════════════

class LoginDialog(QDialog):
    def __init__(self):
        super().__init__()
        self.setWindowTitle("Вход в систему — Театр")
        self.setFixedWidth(380)
        self.user_data = None

        layout = QVBoxLayout(self)
        layout.setSpacing(14)

        title = QLabel("🎭 Театр — Вход в систему")
        title.setObjectName("header")
        title.setAlignment(Qt.AlignmentFlag.AlignCenter)
        layout.addWidget(title)

        form = QFormLayout()
        self.email_edit = QLineEdit()
        self.email_edit.setPlaceholderText("example@mail.ru")
        self.pass_edit  = QLineEdit()
        self.pass_edit.setEchoMode(QLineEdit.EchoMode.Password)
        form.addRow("Email:", self.email_edit)
        form.addRow("Пароль:", self.pass_edit)
        layout.addLayout(form)

        hint = QLabel("<i>Демо: admin@teatr.ru / AdminPass!1</i>")
        hint.setAlignment(Qt.AlignmentFlag.AlignCenter)
        hint.setStyleSheet("color: gray; font-size: 11px;")
        layout.addWidget(hint)

        btns = QDialogButtonBox(QDialogButtonBox.StandardButton.Ok | QDialogButtonBox.StandardButton.Cancel)
        btns.accepted.connect(self.try_login)
        btns.rejected.connect(self.reject)
        layout.addWidget(btns)

        self.db = Database()

    def set_db_config(self, config: dict):
        self.db.connect(config)

    def try_login(self):
        email    = self.email_edit.text().strip()
        password = self.pass_edit.text()
        if not email or not password:
            QMessageBox.warning(self, "Ошибка", "Заполните все поля.")
            return
        user = self.db.verify_password(email, password)
        if user:
            self.user_data = user
            self.accept()
        else:
            QMessageBox.critical(self, "Ошибка", "Неверный email или пароль.")


class TicketDialog(QDialog):
    """Диалог добавления / редактирования билета."""
    def __init__(self, db: Database, ticket=None, parent=None):
        super().__init__(parent)
        self.db = db
        self.ticket = ticket
        self.setWindowTitle("Редактировать билет" if ticket else "Добавить билет")
        self.setMinimumWidth(420)

        layout = QFormLayout(self)

        # Спектакль
        self.show_cb = QComboBox()
        shows = db.get_shows()
        for s in shows:
            self.show_cb.addItem(s["title"], s["show_id"])
        layout.addRow("Спектакль:", self.show_cb)

        # Касса
        self.acc_cb = QComboBox()
        accs = db.get_accounting_list()
        for a in accs:
            self.acc_cb.addItem(a["manager_name"], a["accounting_id"])
        layout.addRow("Кассир:", self.acc_cb)

        # Зал → место
        self.hall_cb = QComboBox()
        halls = db.get_halls()
        for h in halls:
            self.hall_cb.addItem(h["name"], h["hall_id"])
        self.hall_cb.currentIndexChanged.connect(self._load_seats)
        layout.addRow("Зал:", self.hall_cb)

        self.seat_cb = QComboBox()
        layout.addRow("Место:", self.seat_cb)
        self._load_seats()

        # Дата и время
        self.dt_edit = QDateTimeEdit(QDateTime.currentDateTime())
        self.dt_edit.setDisplayFormat("dd.MM.yyyy HH:mm")
        layout.addRow("Дата/время:", self.dt_edit)

        # Цена
        self.price_spin = QDoubleSpinBox()
        self.price_spin.setRange(0, 99999)
        self.price_spin.setSuffix(" ₽")
        layout.addRow("Цена:", self.price_spin)

        # Статус
        self.status_cb = QComboBox()
        self.status_cb.addItems(["доступен", "бронь", "продан", "возврат"])
        layout.addRow("Статус:", self.status_cb)

        btns = QDialogButtonBox(QDialogButtonBox.StandardButton.Save | QDialogButtonBox.StandardButton.Cancel)
        btns.accepted.connect(self.accept)
        btns.rejected.connect(self.reject)
        layout.addRow(btns)

        if ticket:
            self._fill(ticket)

    def _load_seats(self):
        hall_id = self.hall_cb.currentData()
        self.seat_cb.clear()
        if hall_id:
            for s in self.db.get_seats_by_hall(hall_id):
                label = f"Ряд {s['row_number']}, место {s['seat_number']} ({s['category']})"
                self.seat_cb.addItem(label, s["seat_id"])

    def _fill(self, t):
        idx = self.show_cb.findData(t["show_id"]) if "show_id" in t else -1
        if idx >= 0:
            self.show_cb.setCurrentIndex(idx)
        self.price_spin.setValue(float(t.get("price", 0)))
        status_idx = self.status_cb.findText(t.get("status", "доступен"))
        if status_idx >= 0:
            self.status_cb.setCurrentIndex(status_idx)

    def get_data(self) -> dict:
        return {
            "accounting_id":  self.acc_cb.currentData(),
            "show_id":        self.show_cb.currentData(),
            "seat_id":        self.seat_cb.currentData(),
            "event_datetime": self.dt_edit.dateTime().toString("yyyy-MM-dd HH:mm"),
            "price":          self.price_spin.value(),
            "status":         self.status_cb.currentText(),
        }


class SellDialog(QDialog):
    """Диалог продажи билета."""
    def __init__(self, db: Database, ticket_id: int, parent=None):
        super().__init__(parent)
        self.db = db
        self.ticket_id = ticket_id
        self.setWindowTitle("Продажа билета")
        self.setFixedWidth(380)

        layout = QFormLayout(self)

        self.user_cb = QComboBox()
        users = db.get_users()
        fio = f"{u['last_name']} {u['name']} {u['mid_name'] or ''}".strip()
        for u in users:
            self.user_cb.addItem(f"{u['fio']} ({u['email']})", u["user_id"])
        layout.addRow("Покупатель:", self.user_cb)

        self.method_cb = QComboBox()
        self.method_cb.addItems(["карта", "наличные", "онлайн"])
        layout.addRow("Способ оплаты:", self.method_cb)

        btns = QDialogButtonBox(QDialogButtonBox.StandardButton.Ok | QDialogButtonBox.StandardButton.Cancel)
        btns.accepted.connect(self.accept)
        btns.rejected.connect(self.reject)
        layout.addRow(btns)


class UserDialog(QDialog):
    def __init__(self, parent=None):
        super().__init__(parent)
        self.setWindowTitle("Добавить пользователя")
        self.setFixedWidth(400)

        layout = QFormLayout(self)
        self.last_name_edit = QLineEdit()
        self.name_edit = QLineEdit()
        self.mid_name_edit = QLineEdit()
        self.email_edit = QLineEdit()
        self.phone_edit = QLineEdit()
        self.birth_edit = QLineEdit()
        self.birth_edit.setPlaceholderText("ГГГГ-ММ-ДД")
        self.pass_edit  = QLineEdit()
        self.pass_edit.setEchoMode(QLineEdit.EchoMode.Password)
        self.role_cb    = QComboBox()
        self.role_cb.addItems(["buyer", "cashier", "admin"])

        layout.addRow("Фамилия:", self.last_name_edit)
        layout.addRow("Имя:", self.name_edit)
        layout.addRow("Отчество:", self.mid_name_edit)
        layout.addRow("Email:", self.email_edit)
        layout.addRow("Телефон:", self.phone_edit)
        layout.addRow("Дата рождения:", self.birth_edit)
        layout.addRow("Пароль:", self.pass_edit)
        layout.addRow("Роль:", self.role_cb)

        btns = QDialogButtonBox(QDialogButtonBox.StandardButton.Save | QDialogButtonBox.StandardButton.Cancel)
        btns.accepted.connect(self.accept)
        btns.rejected.connect(self.reject)
        layout.addRow(btns)

    def get_data(self) -> dict:
        return {
            "last_name": self.last_name_edit.text().strip(),
            "name": self.name_edit.text().strip(),
            "mid_name": self.mid_name_edit.text().strip(),
            "email":     self.email_edit.text().strip(),
            "phone":     self.phone_edit.text().strip(),
            "birthdate": self.birth_edit.text().strip() or None,
            "password":  self.pass_edit.text(),
            "role":      self.role_cb.currentText(),
        }


def make_table(headers: list) -> QTableWidget:
    t = QTableWidget(0, len(headers))
    t.setHorizontalHeaderLabels(headers)
    t.setEditTriggers(QTableWidget.EditTrigger.NoEditTriggers)
    t.setSelectionBehavior(QTableWidget.SelectionBehavior.SelectRows)
    t.horizontalHeader().setStretchLastSection(True)
    t.verticalHeader().setVisible(False)
    t.setAlternatingRowColors(False)
    return t


def fill_table(table: QTableWidget, rows: list, keys: list):
    table.setRowCount(0)
    for row in rows:
        r = table.rowCount()
        table.insertRow(r)
        for c, k in enumerate(keys):
            val = row.get(k, "")
            item = QTableWidgetItem(str(val) if val is not None else "")
            item.setData(Qt.ItemDataRole.UserRole, row)
            table.setItem(r, c, item)


def export_to_csv(table: QTableWidget, parent=None):
    path, _ = QFileDialog.getSaveFileName(parent, "Сохранить CSV", "", "CSV (*.csv)")
    if not path:
        return
    with open(path, "w", newline="", encoding="utf-8-sig") as f:
        writer = csv.writer(f, delimiter=";")
        headers = [table.horizontalHeaderItem(i).text()
                   for i in range(table.columnCount())]
        writer.writerow(headers)
        for row in range(table.rowCount()):
            writer.writerow([
                table.item(row, col).text() if table.item(row, col) else ""
                for col in range(table.columnCount())
            ])
    QMessageBox.information(parent, "Экспорт", f"Файл сохранён:\n{path}")


# ── Вкладка: Билеты ──────────────────────────────────────────────────────────
class TicketsTab(QWidget):
    def __init__(self, db: Database, user: dict):
        super().__init__()
        self.db   = db
        self.user = user
        self._build_ui()
        self.refresh()

    def _build_ui(self):
        layout = QVBoxLayout(self)

        # Панель поиска
        search_grp = QGroupBox("Поиск и фильтрация")
        sg = QHBoxLayout(search_grp)
        self.search_edit   = QLineEdit()
        self.search_edit.setPlaceholderText("Название спектакля, категория...")
        self.status_filter = QComboBox()
        self.status_filter.addItems(["", "доступен", "продан", "бронь", "возврат"])
        sg.addWidget(QLabel("Поиск:"))
        sg.addWidget(self.search_edit)
        sg.addWidget(QLabel("Статус:"))
        sg.addWidget(self.status_filter)
        btn_search = QPushButton("🔍 Найти")
        btn_search.clicked.connect(self.refresh)
        sg.addWidget(btn_search)
        layout.addWidget(search_grp)

        # Таблица
        self.table = make_table([
            "ID", "Спектакль", "Дата/время", "Зал",
            "Ряд", "Место", "Категория", "Цена ₽", "Статус"
        ])
        layout.addWidget(self.table)

        # Кнопки
        btn_row = QHBoxLayout()
        btn_add  = QPushButton("➕ Добавить")
        btn_edit = QPushButton("✏️ Изменить")
        btn_del  = QPushButton("🗑️ Удалить")
        btn_sell = QPushButton("💳 Продать")
        btn_exp  = QPushButton("📤 Экспорт CSV")
        btn_ref  = QPushButton("🔄 Обновить")
        btn_del.setObjectName("danger")
        btn_sell.setObjectName("success")

        btn_add.clicked.connect(self.add_ticket)
        btn_edit.clicked.connect(self.edit_ticket)
        btn_del.clicked.connect(self.delete_ticket)
        btn_sell.clicked.connect(self.sell_ticket)
        btn_exp.clicked.connect(lambda: export_to_csv(self.table, self))
        btn_ref.clicked.connect(self.refresh)

        for b in [btn_add, btn_edit, btn_del, btn_sell, btn_exp, btn_ref]:
            btn_row.addWidget(b)

        # Права доступа — скрываем кнопки управления для покупателя
        if self.user["role"] not in ("admin", "cashier"):
            for b in (btn_add, btn_edit, btn_del, btn_sell):
                b.setVisible(False)

        layout.addLayout(btn_row)

    def refresh(self):
        rows = self.db.get_tickets(
            self.search_edit.text(),
            self.status_filter.currentText()
        )
        keys = ["ticket_id","show_title","event_datetime","hall",
                "row_number","seat_number","category","price","status"]
        fill_table(self.table, rows, keys)

    def _selected_id(self) -> Optional[int]:
        row = self.table.currentRow()
        if row < 0:
            QMessageBox.warning(self, "Выбор", "Выберите строку.")
            return None
        item = self.table.item(row, 0)
        return int(item.text())

    def add_ticket(self):
        dlg = TicketDialog(self.db, parent=self)
        if dlg.exec():
            try:
                self.db.add_ticket(dlg.get_data())
                self.refresh()
            except Exception as e:
                QMessageBox.critical(self, "Ошибка", str(e))

    def edit_ticket(self):
        tid = self._selected_id()
        if tid is None:
            return
        row = self.table.currentRow()
        ticket = {"show_id": None, "price": float(self.table.item(row, 7).text()),
                  "status": self.table.item(row, 8).text()}
        dlg = TicketDialog(self.db, ticket, parent=self)
        if dlg.exec():
            try:
                self.db.update_ticket(tid, dlg.get_data())
                self.refresh()
            except Exception as e:
                QMessageBox.critical(self, "Ошибка", str(e))

    def delete_ticket(self):
        tid = self._selected_id()
        if tid is None:
            return
        if QMessageBox.question(self, "Удаление", f"Удалить билет #{tid}?") == QMessageBox.StandardButton.Yes:
            try:
                self.db.delete_ticket(tid)
                self.refresh()
            except Exception as e:
                QMessageBox.critical(self, "Ошибка", str(e))

    def sell_ticket(self):
        tid = self._selected_id()
        if tid is None:
            return
        dlg = SellDialog(self.db, tid, parent=self)
        if dlg.exec():
            try:
                self.db.sell_ticket(
                    tid,
                    dlg.user_cb.currentData(),
                    dlg.method_cb.currentText()
                )
                QMessageBox.information(self, "Успех", "Билет продан!")
                self.refresh()
            except Exception as e:
                QMessageBox.critical(self, "Ошибка", str(e))


# ── Вкладка: Продажи ─────────────────────────────────────────────────────────
class SalesTab(QWidget):
    def __init__(self, db: Database, user: dict):
        super().__init__()
        self.db   = db
        self.user = user
        self._build_ui()
        self.refresh()

    def _build_ui(self):
        layout = QVBoxLayout(self)

        sh = QHBoxLayout()
        self.search_edit = QLineEdit()
        self.search_edit.setPlaceholderText("Имя покупателя, спектакль, email...")
        btn_s = QPushButton("🔍 Найти")
        btn_s.clicked.connect(self.refresh)
        btn_e = QPushButton("📤 Экспорт CSV")
        btn_e.clicked.connect(lambda: export_to_csv(self.table, self))
        sh.addWidget(QLabel("Поиск:"))
        sh.addWidget(self.search_edit)
        sh.addWidget(btn_s)
        sh.addWidget(btn_e)
        layout.addLayout(sh)

        self.table = make_table([
            "ID продажи", "Покупатель", "Email", "Спектакль",
            "Дата показа", "Цена ₽", "Оплачено ₽", "Способ", "Дата продажи"
        ])
        layout.addWidget(self.table)

        # Итог
        self.total_lbl = QLabel()
        self.total_lbl.setStyleSheet(f"color: {BLUE_DARK}; font-weight: bold; padding: 4px;")
        layout.addWidget(self.total_lbl)

    def refresh(self):
        rows = self.db.get_sales(self.search_edit.text())
        keys = ["sale_id","buyer","email","show_title",
                "event_datetime","price","amount_paid","payment_method","sale_datetime"]
        fill_table(self.table, rows, keys)
        total = sum(float(r["amount_paid"] or 0) for r in rows)
        self.total_lbl.setText(f"Итого: {total:,.2f} ₽  |  Записей: {len(rows)}")


# ── Вкладка: Пользователи ────────────────────────────────────────────────────
class UsersTab(QWidget):
    def __init__(self, db: Database, user: dict):
        super().__init__()
        self.db   = db
        self.user = user
        self._build_ui()
        self.refresh()

    def _build_ui(self):
        layout = QVBoxLayout(self)

        sh = QHBoxLayout()
        self.search_edit = QLineEdit()
        self.search_edit.setPlaceholderText("ФИО, email...")
        btn_s = QPushButton("🔍 Найти")
        btn_s.clicked.connect(self.refresh)
        sh.addWidget(QLabel("Поиск:"))
        sh.addWidget(self.search_edit)
        sh.addWidget(btn_s)
        layout.addLayout(sh)

        self.table = make_table([
            "ID", "Фaмилия", "Имя", "Отчество", "Дата рожд.", "Телефон", "Email", "Роль", "Активен", "Дата регистр."
        ])
        layout.addWidget(self.table)

        btn_row = QHBoxLayout()
        btn_add    = QPushButton("➕ Добавить")
        btn_toggle = QPushButton("🔒 Активировать/Блок.")
        btn_exp    = QPushButton("📤 Экспорт CSV")
        btn_ref    = QPushButton("🔄 Обновить")

        btn_add.clicked.connect(self.add_user)
        btn_toggle.clicked.connect(self.toggle_user)
        btn_exp.clicked.connect(lambda: export_to_csv(self.table, self))
        btn_ref.clicked.connect(self.refresh)

        for b in [btn_add, btn_toggle, btn_exp, btn_ref]:
            btn_row.addWidget(b)

        if self.user["role"] != "admin":
            btn_add.setVisible(False)
            btn_toggle.setVisible(False)

        layout.addLayout(btn_row)

    def refresh(self):
        rows = self.db.get_users(self.search_edit.text())
        keys = [
            "user_id",
            "last_name",
            "name",
            "mid_name",
            "birthdate",
            "phone",
            "email",
            "role",
            "is_active",
            "created_at"
        ]
        fill_table(self.table, rows, keys)

    def add_user(self):
        dlg = UserDialog(self)
        if dlg.exec():
            try:
                self.db.add_user(dlg.get_data())
                self.refresh()
            except Exception as e:
                QMessageBox.critical(self, "Ошибка", str(e))

    def toggle_user(self):
        row = self.table.currentRow()
        if row < 0:
            QMessageBox.warning(self, "Выбор", "Выберите пользователя.")
            return
        uid    = int(self.table.item(row, 0).text())
        active = self.table.item(row, 6).text() == "True"
        self.db.toggle_user_active(uid, not active)
        self.refresh()


# ── Вкладка: Отчёты ──────────────────────────────────────────────────────────
class ReportsTab(QWidget):
    def __init__(self, db: Database):
        super().__init__()
        self.db = db
        self._build_ui()

    def _build_ui(self):
        layout = QVBoxLayout(self)

        btn_rev = QPushButton("📊 Выручка по спектаклям")
        btn_rev.clicked.connect(self.show_revenue)
        btn_aud = QPushButton("📋 Журнал аудита")
        btn_aud.clicked.connect(self.show_audit)

        row = QHBoxLayout()
        row.addWidget(btn_rev)
        row.addWidget(btn_aud)
        row.addStretch()
        layout.addLayout(row)

        self.report_table = make_table(["Спектакль", "Продано билетов", "Выручка ₽"])
        layout.addWidget(self.report_table)

        self.summary_lbl = QLabel()
        self.summary_lbl.setStyleSheet(f"color: {BLUE_DARK}; font-weight: bold;")
        layout.addWidget(self.summary_lbl)

        btn_exp = QPushButton("📤 Экспорт CSV")
        btn_exp.clicked.connect(lambda: export_to_csv(self.report_table, self))
        layout.addWidget(btn_exp)

    def show_revenue(self):
        rows = self.db.revenue_by_show()
        self.report_table.setColumnCount(3)
        self.report_table.setHorizontalHeaderLabels(["Спектакль", "Продано", "Выручка ₽"])
        fill_table(self.report_table, rows, ["title", "sold", "revenue"])
        total = sum(float(r["revenue"] or 0) for r in rows)
        self.summary_lbl.setText(f"Общая выручка: {total:,.2f} ₽")

    def show_audit(self):
        rows = self.db.get_audit_log()
        self.report_table.setColumnCount(5)
        self.report_table.setHorizontalHeaderLabels(
            ["ID", "Таблица", "Операция", "Пользователь", "Дата/время"]
        )
        fill_table(self.report_table, rows,
                   ["log_id","table_name","operation","changed_by","changed_at"])
        self.summary_lbl.setText(f"Записей в журнале: {len(rows)}")


# ══════════════════════════════════════════════════════════════════════════════
# MAIN WINDOW
# ══════════════════════════════════════════════════════════════════════════════

class MainWindow(QMainWindow):
    def __init__(self, db: Database, user: dict):
        super().__init__()
        self.db   = db
        self.user = user
        fio = f"{user['last_name']} {user['name']}"
        if user['mid_name']:
            fio += f" {user['mid_name']}"
        self.setWindowTitle(f"Театр — {fio} ({APP_ROLES.get(user['role'], user['role'])})")
        self.resize(1100, 680)

        # Статус-бар
        sb = QStatusBar()
        self.setStatusBar(sb)
        sb.showMessage(f"Подключено  |  Пользователь: {fio}  |  Роль: {APP_ROLES.get(user['role'])}")

        # Вкладки
        tabs = QTabWidget()

        role = user["role"]

        # Вкладка «Билеты» — доступна всем
        tabs.addTab(TicketsTab(db, user), "🎫 Билеты")

        # Вкладка «Продажи» — только admin и cashier
        if role in ("admin", "cashier"):
            tabs.addTab(SalesTab(db, user), "💳 Продажи")

        # Вкладка «Пользователи» — только admin
        if role == "admin":
            tabs.addTab(UsersTab(db, user), "👤 Пользователи")

        # Вкладка «Отчёты» — только admin и cashier
        if role in ("admin", "cashier"):
            tabs.addTab(ReportsTab(db), "📊 Отчёты")

        self.setCentralWidget(tabs)


# ══════════════════════════════════════════════════════════════════════════════
# ENTRY POINT
# ══════════════════════════════════════════════════════════════════════════════

import json

# ── Хранилище профилей подключения ──────────────────────────────────────────
PROFILES_FILE = os.path.join(os.path.expanduser("~"), ".theatre_db_profiles.json")

def load_profiles() -> list:
    """Загрузить сохранённые профили подключения."""
    try:
        if os.path.exists(PROFILES_FILE):
            with open(PROFILES_FILE, "r", encoding="utf-8") as f:
                return json.load(f)
    except Exception:
        pass
    return []

def save_profiles(profiles: list):
    """Сохранить профили подключения."""
    try:
        with open(PROFILES_FILE, "w", encoding="utf-8") as f:
            json.dump(profiles, f, ensure_ascii=False, indent=2)
    except Exception as e:
        print(f"Ошибка сохранения профилей: {e}")


class DBConnectDialog(QDialog):
    """
    Диалог выбора/создания профиля подключения к БД.
    Показывает список сохранённых пар (логин, пароль).
    Позволяет выбрать, удалить или добавить новый профиль.
    """
    def __init__(self):
        super().__init__()
        self.setWindowTitle("Подключение к базе данных")
        self.setFixedWidth(440)
        self.selected_config: dict | None = None
        self._profiles = load_profiles()
        self._build_ui()

    def _build_ui(self):
        from PyQt6.QtWidgets import QListWidget, QListWidgetItem
        layout = QVBoxLayout(self)
        layout.setSpacing(12)

        title = QLabel("🎭 Театр — Подключение к БД")
        title.setObjectName("header")
        title.setAlignment(Qt.AlignmentFlag.AlignCenter)
        layout.addWidget(title)

        # Группа сохранённых профилей
        saved_grp = QGroupBox("Сохранённые подключения")
        sg = QVBoxLayout(saved_grp)

        self.list_widget = QListWidget()
        self.list_widget.setMinimumHeight(120)
        self._populate_list()
        self.list_widget.itemDoubleClicked.connect(self._accept_selected)
        sg.addWidget(self.list_widget)

        btn_row = QHBoxLayout()
        self.btn_connect = QPushButton("✅ Подключиться")
        self.btn_connect.setObjectName("success")
        self.btn_connect.clicked.connect(self._accept_selected)
        btn_delete = QPushButton("🗑️ Удалить")
        btn_delete.setObjectName("danger")
        btn_delete.clicked.connect(self._delete_selected)
        btn_row.addWidget(self.btn_connect)
        btn_row.addWidget(btn_delete)
        sg.addLayout(btn_row)
        layout.addWidget(saved_grp)

        # Разделитель
        line = QFrame()
        line.setFrameShape(QFrame.Shape.HLine)
        line.setFrameShadow(QFrame.Shadow.Sunken)
        layout.addWidget(line)

        # Группа нового подключения
        new_grp = QGroupBox("Новое подключение")
        ng = QFormLayout(new_grp)

        self.user_edit = QLineEdit()
        self.user_edit.setPlaceholderText("Пользователь PostgreSQL")
        self.pass_edit = QLineEdit()
        self.pass_edit.setEchoMode(QLineEdit.EchoMode.Password)
        self.pass_edit.setPlaceholderText("Пароль")

        ng.addRow("Логин:", self.user_edit)
        ng.addRow("Пароль:", self.pass_edit)

        btn_new = QPushButton("🔌 Подключиться с новым аккаунтом")
        btn_new.setObjectName("secondary")
        btn_new.clicked.connect(self._connect_new)
        ng.addRow(btn_new)
        layout.addWidget(new_grp)

        # Кнопка отмены
        btn_cancel = QPushButton("Отмена")
        btn_cancel.setObjectName("secondary")
        btn_cancel.clicked.connect(self.reject)
        layout.addWidget(btn_cancel)

        self._update_connect_btn_state()
        self.list_widget.currentRowChanged.connect(lambda _: self._update_connect_btn_state())

    def _populate_list(self):
        self.list_widget.clear()
        for p in self._profiles:
            self.list_widget.addItem(f"👤  {p['user']}")
        if self._profiles:
            self.list_widget.setCurrentRow(0)

    def _update_connect_btn_state(self):
        self.btn_connect.setEnabled(self.list_widget.currentRow() >= 0)

    def _build_config(self, user: str, password: str) -> dict:
        base = DB_CONFIG.copy()
        base["user"] = user
        base["password"] = password
        return base

    def _accept_selected(self):
        idx = self.list_widget.currentRow()
        if idx < 0:
            QMessageBox.warning(self, "Выбор", "Выберите профиль из списка.")
            return
        p = self._profiles[idx]
        config = self._build_config(p["user"], p["password"])
        db_test = Database()
        if not db_test.connect(config):
            QMessageBox.critical(self, "Ошибка подключения",
                                 f"Не удалось подключиться с логином «{p['user']}».\n"
                                 "Возможно, данные устарели — удалите профиль и добавьте заново.")
            return
        db_test.disconnect()
        self.selected_config = config
        self.accept()

    def _connect_new(self):
        user = self.user_edit.text().strip()
        password = self.pass_edit.text()
        if not user:
            QMessageBox.warning(self, "Ввод", "Введите логин.")
            return

        # Проверяем дубликат по логину
        existing_users = [p["user"] for p in self._profiles]
        if user in existing_users:
            QMessageBox.information(self, "Уже есть",
                                    f"Профиль с логином «{user}» уже сохранён.\nВыберите его из списка.")
            # Выбрать соответствующий элемент в списке
            idx = existing_users.index(user)
            self.list_widget.setCurrentRow(idx)
            return

        config = self._build_config(user, password)
        db_test = Database()
        if not db_test.connect(config):
            QMessageBox.critical(self, "Ошибка подключения",
                                 "Не удалось подключиться.\nПроверьте логин и пароль.")
            return
        db_test.disconnect()

        # Сохраняем новый профиль
        self._profiles.append({"user": user, "password": password})
        save_profiles(self._profiles)
        self._populate_list()
        self.list_widget.setCurrentRow(len(self._profiles) - 1)

        self.selected_config = config
        self.accept()

    def _delete_selected(self):
        idx = self.list_widget.currentRow()
        if idx < 0:
            return
        user = self._profiles[idx]["user"]
        reply = QMessageBox.question(
            self, "Удаление профиля",
            f"Удалить профиль «{user}»?",
            QMessageBox.StandardButton.Yes | QMessageBox.StandardButton.No
        )
        if reply == QMessageBox.StandardButton.Yes:
            self._profiles.pop(idx)
            save_profiles(self._profiles)
            self._populate_list()
            self._update_connect_btn_state()


def main():
    app = QApplication(sys.argv)
    app.setStyleSheet(STYLE)

    # Диалог выбора подключения к БД (только логин + пароль)
    conn_dlg = DBConnectDialog()
    if conn_dlg.exec() != QDialog.DialogCode.Accepted:
        sys.exit(0)

    config = conn_dlg.selected_config

    db = Database()
    if not db.connect(config):
        QMessageBox.critical(None, "Ошибка", "Не удалось подключиться к базе данных.")
        sys.exit(1)

    # Логин в приложение
    login = LoginDialog()
    login.set_db_config(config)
    if login.exec() != QDialog.DialogCode.Accepted:
        sys.exit(0)

    window = MainWindow(db, login.user_data)
    window.show()
    sys.exit(app.exec())


if __name__ == "__main__":
    main()