import sqlite3
import tkinter as tk
from tkinter import messagebox, ttk

# --- Datenbank initialisieren (lokale SQLite-Datei) ---
def init_db():
    conn = sqlite3.connect("hotel_handover.db")
    cursor = conn.cursor()
    
    # Tabelle für Tägliche Übergabe
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS daily (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            datum TEXT,
            prio TEXT,
            mitarbeiter TEXT,
            kategorie TEXT,
            beschreibung TEXT,
            status TEXT
        )
    """)
    
    # Tabelle für Jährliche Übergabe (Wissensdatenbank)
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS annual (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            kategorie TEXT,
            titel TEXT,
            beschreibung TEXT,
            letztes_update TEXT
        )
    """)
    
    # Beispiel-Daten einfügen, falls Tabellen leer sind
    cursor.execute("SELECT COUNT(*) FROM daily")
    if cursor.fetchone()[0] == 0:
        cursor.execute("INSERT INTO daily (datum, prio, mitarbeiter, kategorie, beschreibung, status) VALUES ('2026-06-06', 'Hoch', 'Anna M.', 'Technik', 'Aufzug 2 defekt, Wartung gerufen', 'Offen')")
        cursor.execute("INSERT INTO daily (datum, prio, mitarbeiter, kategorie, beschreibung, status) VALUES ('2026-06-06', 'Normal', 'Stefan R.', 'VIP', 'Zimmer 204 benötigt Babybett', 'Erledigt')")
    
    cursor.execute("SELECT COUNT(*) FROM annual")
    if cursor.fetchone()[0] == 0:
        cursor.execute("INSERT INTO annual (kategorie, titel, beschreibung, letztes_update) VALUES ('Technik', 'Lüftungsfilter', 'Filterwechsel alle 6 Monate (Firma Müller)', '2026-01-10')")
        cursor.execute("INSERT INTO annual (kategorie, titel, beschreibung, letztes_update) VALUES ('Notfall', 'Wasserschaden', 'Hauptventil im Keller hinter Tür B3', '2025-11-14')")
        
    conn.commit()
    conn.close()

class HotelHandoverApp:
    def __init__(self, root):
        self.root = root
        self.root.title("HotelHandover Pro (Offline-Modus)")
        self.root.geometry("900x550")
        
        # Stil für Excel-ähnliches Tabellen-Layout
        style = ttk.Style()
        style.theme_use("clam")
        
        # Haupt-Notebook (Tabs)
        self.notebook = ttk.Notebook(root)
        self.notebook.pack(fill="both", expand=True, padx=10, pady=10)
        
        # --- TAB 1: Tägliche Übergabe ---
        self.tab_daily = ttk.Frame(self.notebook)
        self.notebook.add(self.tab_daily, text="Tägliche Übergabe (Schicht-Logbuch)")
        self.setup_daily_tab()
        
        # --- TAB 2: Jährliche Übergabe ---
        self.tab_annual = ttk.Frame(self.notebook)
        self.notebook.add(self.tab_annual, text="Jährliche Übergabe (Wissensdatenbank)")
        self.setup_annual_tab()

    def setup_daily_tab(self):
        # Such- und Aktionsleiste oben
        top_frame = ttk.Frame(self.tab_daily)
        top_frame.pack(fill="x", padx=5, pady=5)
        
        ttk.Label(top_frame, text="Suche:").pack(side="left", padx=5)
        self.search_entry_daily = ttk.Entry(top_frame, width=25)
        self.search_entry_daily.pack(side="left", padx=5)
        self.search_entry_daily.bind("<KeyRelease>", self.filter_daily)
        
        btn_export = ttk.Button(top_frame, text="📄 Tagesbericht als PDF senden (1-Klick)", command=self.export_pdf)
        btn_export.pack(side="right", padx=5)

        # Excel-ähnliche Tabelle (Treeview)
        columns = ("ID", "Datum", "Prio", "Mitarbeiter", "Kategorie", "Beschreibung", "Status")
        self.tree_daily = ttk.Treeview(self.tab_daily, columns=columns, show="headings", selectmode="browse")
        
        for col in columns:
            self.tree_daily.heading(col, text=col)
            self.tree_daily.column(col, width=100 if col != "Beschreibung" else 250)
            
        self.tree_daily.pack(fill="both", expand=True, padx=5, pady=5)
        self.load_daily_data()

    def setup_annual_tab(self):
        top_frame = ttk.Frame(self.tab_annual)
        top_frame.pack(fill="x", padx=5, pady=5)
        
        ttk.Label(top_frame, text="Suche:").pack(side="left", padx=5)
        self.search_entry_annual = ttk.Entry(top_frame, width=25)
        self.search_entry_annual.pack(side="left", padx=5)
        self.search_entry_annual.bind("<KeyRelease>", self.filter_annual)

        columns = ("ID", "Kategorie", "Titel", "Beschreibung", "Letztes Update")
        self.tree_annual = ttk.Treeview(self.tab_annual, columns=columns, show="headings", selectmode="browse")
        
        for col in columns:
            self.tree_annual.heading(col, text=col)
            self.tree_annual.column(col, width=120 if col != "Beschreibung" else 300)
            
        self.tree_annual.pack(fill="both", expand=True, padx=5, pady=5)
        self.load_annual_data()

    def load_daily_data(self):
        for row in self.tree_daily.get_children():
            self.tree_daily.delete(row)
        conn = sqlite3.connect("hotel_handover.db")
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM daily")
        for row in cursor.fetchall():
            self.tree_daily.insert("", "end", values=row)
        conn.close()

    def load_annual_data(self):
        for row in self.tree_annual.get_children():
            self.tree_annual.delete(row)
        conn = sqlite3.connect("hotel_handover.db")
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM annual")
        for row in cursor.fetchall():
            self.tree_annual.insert("", "end", values=row)
        conn.close()

    def filter_daily(self, event):
        query = self.search_entry_daily.get().lower()
        for row in self.tree_daily.get_children():
            self.tree_daily.delete(row)
        conn = sqlite3.connect("hotel_handover.db")
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM daily WHERE beschreibung LIKE ? OR mitarbeiter LIKE ? OR kategorie LIKE ?", 
                       (f"%{query}%", f"%{query}%", f"%{query}%"))
        for row in cursor.fetchall():
            self.tree_daily.insert("", "end", values=row)
        conn.close()

    def filter_annual(self, event):
        query = self.search_entry_annual.get().lower()
        for row in self.tree_annual.get_children():
            self.tree_annual.delete(row)
        conn = sqlite3.connect("hotel_handover.db")
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM annual WHERE titel LIKE ? OR beschreibung LIKE ? OR kategorie LIKE ?", 
                       (f"%{query}%", f"%{query}%", f"%{query}%"))
        for row in cursor.fetchall():
            self.tree_annual.insert("", "end", values=row)
        conn.close()

    def export_pdf(self):
        # Platzhalter für die PDF-Generierung & den E-Mail-Versand per Knopfdruck
        messagebox.showinfo("Erfolgreich", "Tagesbericht wurde als PDF generiert und per E-Mail an den Abteilungsleiter gesendet!")

if __name__ == "__main__":
    init_db()
    root = tk.Tk()
    app = HotelHandoverApp(root)
    root.mainloop()print('Hello world!')