---

import tkinter as tk
from tkinter import ttk, messagebox
import mysql.connector

DB_CONFIG = dict(host='localhost', user='root', password='', database='resort_db')

class ResortApp:
    def __init__(self, root):
        self.root = root
        self.root.title('ООО КУРОРТ - Управление обращениями и бронированиями')
        self.root.geometry('1320x760')
        self.conn = None
        self.cur = None
        self.selected_request_id = None
        self.create_ui()
        self.connect_db()
        self.load_requests()

    def connect_db(self):
        try:
            self.conn = mysql.connector.connect(**DB_CONFIG)
            self.cur = self.conn.cursor()
            messagebox.showinfo('Успех', 'Подключение к БД выполнено')
        except Exception as e:
            messagebox.showerror('Ошибка подключения', str(e))

    def create_ui(self):
        main = ttk.Frame(self.root, padding=10)
        main.pack(fill='both', expand=True)

        left = ttk.Frame(main, padding=10)
        left.pack(side='left', fill='y')

        right = ttk.Frame(main, padding=10)
        right.pack(side='right', fill='both', expand=True)

        row = 0
        ttk.Label(left, text='Гость').grid(row=row, column=0, sticky='w')
        self.guest_name = ttk.Entry(left, width=35)
        self.guest_name.grid(row=row+1, column=0, pady=3)

        row += 2
        ttk.Label(left, text='Телефон').grid(row=row, column=0, sticky='w')
        self.phone = ttk.Entry(left, width=35)
        self.phone.grid(row=row+1, column=0, pady=3)

        row += 2
        ttk.Label(left, text='Email').grid(row=row, column=0, sticky='w')
        self.email = ttk.Entry(left, width=35)
        self.email.grid(row=row+1, column=0, pady=3)

        row += 2
        ttk.Label(left, text='Паспорт').grid(row=row, column=0, sticky='w')
        self.passport = ttk.Entry(left, width=35)
        self.passport.grid(row=row+1, column=0, pady=3)

        row += 2
        ttk.Label(left, text='Тип обращения').grid(row=row, column=0, sticky='w')
        self.req_type = ttk.Combobox(left, values=['Жалоба', 'Заявка', 'Вопрос'], width=32, state='readonly')
        self.req_type.grid(row=row+1, column=0, pady=3)
        self.req_type.set('Заявка')

        row += 2
        ttk.Label(left, text='Текст обращения').grid(row=row, column=0, sticky='w')
        self.req_text = tk.Text(left, width=28, height=6)
        self.req_text.grid(row=row+1, column=0, pady=3)

        row += 2
        ttk.Label(left, text='Статус').grid(row=row, column=0, sticky='w')
        self.status = ttk.Combobox(left, values=['Новое', 'В работе', 'Закрыто'], width=32, state='readonly')
        self.status.grid(row=row+1, column=0, pady=3)
        self.status.set('Новое')

        row += 2
        ttk.Label(left, text='Сотрудник').grid(row=row, column=0, sticky='w')
        self.employee = ttk.Combobox(left, values=self.get_employee_list(), width=32, state='readonly')
        self.employee.grid(row=row+1, column=0, pady=3)
        if self.employee['values']:
            self.employee.set(self.employee['values'][0])

        row += 2
        ttk.Label(left, text='Поиск по гостю').grid(row=row, column=0, sticky='w')
        self.search = ttk.Entry(left, width=35)
        self.search.grid(row=row+1, column=0, pady=3)

        row += 2
        ttk.Button(left, text='Добавить', command=self.add_request).grid(row=row, column=0, pady=3, sticky='ew')
        ttk.Button(left, text='Обновить', command=self.load_requests).grid(row=row+1, column=0, pady=3, sticky='ew')
        ttk.Button(left, text='Показать активные', command=self.load_active_requests).grid(row=row+2, column=0, pady=3, sticky='ew')
        ttk.Button(left, text='Найти', command=self.search_requests).grid(row=row+3, column=0, pady=3, sticky='ew')
        ttk.Button(left, text='Изменить выбранное', command=self.update_request).grid(row=row+4, column=0, pady=3, sticky='ew')
        ttk.Button(left, text='Удалить выбранное', command=self.delete_request).grid(row=row+5, column=0, pady=3, sticky='ew')
        ttk.Button(left, text='Сбросить форму', command=self.clear_form).grid(row=row+6, column=0, pady=3, sticky='ew')

        cols = ('request_id', 'guest_name', 'request_type', 'request_status', 'employee_name', 'created_at')
        self.tree = ttk.Treeview(right, columns=cols, show='headings', height=24)
        headings = ['ID', 'Гость', 'Тип', 'Статус', 'Сотрудник', 'Дата']
        widths = [60, 250, 110, 110, 180, 170]
        for c, h, w in zip(cols, headings, widths):
            self.tree.heading(c, text=h)
            self.tree.column(c, width=w, anchor='w')
        self.tree.pack(fill='both', expand=True)
        self.tree.bind('<<TreeviewSelect>>', self.on_select)

    def get_employee_list(self):
        try:
            self.cur.execute('SELECT full_name FROM employees ORDER BY full_name')
            return [r[0] for r in self.cur.fetchall()]
        except:
            return []

    def _employee_id_by_name(self, name):
        if not name:
            return None
        self.cur.execute('SELECT employee_id FROM employees WHERE full_name=%s', (name,))
        row = self.cur.fetchone()
        return row[0] if row else None

    def _query(self, sql, params=()):
        self.cur.execute(sql, params)
        if sql.strip().lower().startswith('select'):
            return self.cur.fetchall()
        self.conn.commit()

    def refresh_tree(self, sql, params=()):
        for i in self.tree.get_children():
            self.tree.delete(i)
        rows = self._query(sql, params) or []
        for r in rows:
            self.tree.insert('', 'end', values=r)

    def load_requests(self):
        self.refresh_tree("""
            SELECT gr.request_id, g.full_name, gr.request_type, gr.request_status, IFNULL(e.full_name,''), gr.created_at
            FROM guest_requests gr
            JOIN guests g ON gr.guest_id = g.guest_id
            LEFT JOIN employees e ON gr.employee_id = e.employee_id
            ORDER BY gr.request_id DESC
        """)

    def load_active_requests(self):
        self.refresh_tree("""
            SELECT request_id, guest_name, request_type, request_status, employee_name, created_at
            FROM active_requests_view
        """)

    def search_requests(self):
        q = self.search.get().strip()
        self.refresh_tree("""
            SELECT gr.request_id, g.full_name, gr.request_type, gr.request_status, IFNULL(e.full_name,''), gr.created_at
            FROM guest_requests gr
            JOIN guests g ON gr.guest_id = g.guest_id
            LEFT JOIN employees e ON gr.employee_id = e.employee_id
            WHERE g.full_name LIKE %s
            ORDER BY gr.request_id DESC
        """, (f'%{q}%',))

    def clear_form(self):
        self.selected_request_id = None
        for w in (self.guest_name, self.phone, self.email, self.passport, self.search):
            w.delete(0, 'end')
        self.req_text.delete('1.0', 'end')
        self.req_type.set('Заявка')
        self.status.set('Новое')
        if self.employee['values']:
            self.employee.set(self.employee['values'][0])
        self.tree.selection_remove(self.tree.selection())

    def add_request(self):
        try:
            guest = self.guest_name.get().strip()
            phone = self.phone.get().strip()
            email = self.email.get().strip()
            passport = self.passport.get().strip()
            req_type = self.req_type.get().strip()
            text = self.req_text.get('1.0', 'end').strip()
            status = self.status.get().strip()
            employee_name = self.employee.get().strip()

            if not guest or not text:
                messagebox.showwarning('Проверка', 'Заполни имя гостя и текст обращения')
                return

            self._query(
                'INSERT INTO guests (full_name, phone, email, passport_number) VALUES (%s,%s,%s,%s)',
                (guest, phone, email, passport)
            )
            guest_id = self.cur.lastrowid
            employee_id = self._employee_id_by_name(employee_name)

            self._query(
                'INSERT INTO guest_requests (guest_id, request_type, request_text, request_status, employee_id) VALUES (%s,%s,%s,%s,%s)',
                (guest_id, req_type, text, status, employee_id)
            )
            messagebox.showinfo('Успех', 'Запись добавлена')
            self.load_requests()
        except Exception as e:
            messagebox.showerror('Ошибка', str(e))

    def on_select(self, event=None):
        sel = self.tree.selection()
        if not sel:
            return
        vals = self.tree.item(sel[0], 'values')
        self.selected_request_id = vals[0]
        self.guest_name.delete(0, 'end')
        self.guest_name.insert(0, vals[1])
        self.req_type.set(vals[2])
        self.status.set(vals[3])
        self.req_text.delete('1.0', 'end')
        self.req_text.insert('1.0', '')

    def update_request(self):
        try:
            if not self.selected_request_id:
                messagebox.showwarning('Проверка', 'Сначала выбери запись в таблице')
                return
            status = self.status.get().strip()
            text = self.req_text.get('1.0', 'end').strip()
            employee_name = self.employee.get().strip()
            employee_id = self._employee_id_by_name(employee_name)

            self._query(
                'UPDATE guest_requests SET request_status=%s, request_text=IF(%s=\"\", request_text, %s), employee_id=%s WHERE request_id=%s',
                (status, text, text, employee_id, self.selected_request_id)
            )
            messagebox.showinfo('Успех', 'Запись обновлена')
            self.load_requests()
        except Exception as e:
            messagebox.showerror('Ошибка', str(e))

    def delete_request(self):
        try:
            if not self.selected_request_id:
                messagebox.showwarning('Проверка', 'Сначала выбери запись в таблице')
                return
            if messagebox.askyesno('Подтверждение', 'Удалить выбранную запись?'):
                self._query('DELETE FROM guest_requests WHERE request_id=%s', (self.selected_request_id,))
                self.selected_request_id = None
                self.load_requests()
        except Exception as e:
            messagebox.showerror('Ошибка', str(e))

if __name__ == '__main__':
    root = tk.Tk()
    app = ResortApp(root)
    root.mainloop()