import sys
import sqlite3
from datetime import datetime, date, timedelta
from PyQt6.QtWidgets import (QApplication, QMainWindow, QWidget, QVBoxLayout, QHBoxLayout, 
                             QListWidget, QPushButton, QLabel, QLineEdit, QFormLayout, 
                             QDateEdit, QFileDialog, QMessageBox, QTabWidget, QComboBox, QDialog, QDialogButtonBox, QSpinBox, QMenu, QSystemTrayIcon, QTextEdit)
from PyQt6.QtCore import Qt, QDate, QBuffer, QTimer
from PyQt6.QtGui import QPixmap, QIcon
from reportlab.lib.pagesizes import letter
from reportlab.pdfgen import canvas
import pandas as pd
import csv
from openpyxl import Workbook

def adapt_date(val):
    return val.isoformat()

def convert_date(val):
    return date.fromisoformat(val.decode())

sqlite3.register_adapter(date, adapt_date)
sqlite3.register_converter("DATE", convert_date)

class NotificationDialog(QDialog):
    def __init__(self, parent=None):
        super().__init__(parent)
        self.setWindowTitle("Impostazioni Notifiche")
        layout = QFormLayout(self)
        self.days_spinbox = QSpinBox()
        self.days_spinbox.setRange(1, 365)
        self.days_spinbox.setValue(30)
        layout.addRow("Giorni di anticipo per le notifiche:", self.days_spinbox)
        buttons = QDialogButtonBox(QDialogButtonBox.StandardButton.Ok | QDialogButtonBox.StandardButton.Cancel)
        buttons.accepted.connect(self.accept)
        buttons.rejected.connect(self.reject)
        layout.addWidget(buttons)

class ExportDialog(QDialog):
    def __init__(self, parent=None):
        super().__init__(parent)
        self.setWindowTitle("Esporta Report")
        layout = QVBoxLayout(self)
        self.format_combo = QComboBox()
        self.format_combo.addItems(["PDF", "CSV", "Excel"])
        layout.addWidget(self.format_combo)
        buttons = QDialogButtonBox(QDialogButtonBox.StandardButton.Ok | QDialogButtonBox.StandardButton.Cancel)
        buttons.accepted.connect(self.accept)
        buttons.rejected.connect(self.reject)
        layout.addWidget(buttons)

    def get_format(self):
        return self.format_combo.currentText()

class ValveManager(QMainWindow):
    def __init__(self):
        super().__init__()
        self.setWindowTitle("Gestione Valvole di Sicurezza")
        self.setGeometry(100, 100, 1000, 600)
        self.setWindowIcon(QIcon('valve_icon.png'))

        self.init_db()
        self.init_ui()
        self.init_tray()

        self.notification_days = 30
        self.check_revisions()

        self.timer = QTimer(self)
        self.timer.timeout.connect(self.check_revisions)
        self.timer.start(86400000)  # Controlla ogni 24 ore

    def init_db(self):
        self.conn = sqlite3.connect('valves.db', detect_types=sqlite3.PARSE_DECLTYPES)
        self.cursor = self.conn.cursor()
        self.cursor.execute('''CREATE TABLE IF NOT EXISTS valves
                            (id TEXT PRIMARY KEY,
                             name TEXT,
                             nominal_pressure TEXT,
                             inlet_diameter TEXT,
                             outlet_diameter TEXT,
                             last_revision_date DATE,
                             next_revision_date DATE,
                             image BLOB)''')
        self.conn.commit()

    def init_ui(self):
        main_layout = QHBoxLayout()

        list_layout = QVBoxLayout()
        self.valve_list = QListWidget()
        self.valve_list.itemClicked.connect(self.show_valve_details)
        list_layout.addWidget(self.valve_list)

        add_button = QPushButton("Inserisci Valvola")
        add_button.clicked.connect(self.insert_valve)
        list_layout.addWidget(add_button)

        delete_button = QPushButton("Elimina Valvola")
        delete_button.clicked.connect(self.delete_valve)
        list_layout.addWidget(delete_button)

        main_layout.addLayout(list_layout, 1)

        tab_widget = QTabWidget()
        
        details_widget = QWidget()
        form_layout = QFormLayout(details_widget)
        self.id_input = QLineEdit()
        self.name_input = QLineEdit()
        self.nominal_pressure_input = QLineEdit()
        self.inlet_diameter_input = QLineEdit()
        self.outlet_diameter_input = QLineEdit()
        self.last_revision_date_input = QDateEdit()
        self.last_revision_date_input.setCalendarPopup(True)
        self.next_revision_date_input = QComboBox()
        
        # Aggiungi opzioni al menu a tendina per la scadenza della revisione
        for i in range(1, 11):
            label = f"{i} anni"
            self.next_revision_date_input.addItem(label, i * 12)

        self.image_label = QLabel()
        self.image_label.setFixedSize(200, 200)
        self.image_label.setScaledContents(True)

        form_layout.addRow("Codice seriale:", self.id_input)
        form_layout.addRow("Nome:", self.name_input)
        form_layout.addRow("Pressione nominale:", self.nominal_pressure_input)
        form_layout.addRow("Diametro ingresso:", self.inlet_diameter_input)
        form_layout.addRow("Diametro uscita:", self.outlet_diameter_input)
        form_layout.addRow("Ultima revisione:", self.last_revision_date_input)
        form_layout.addRow("Scadenza revisione:", self.next_revision_date_input)
        form_layout.addRow("Immagine:", self.image_label)

        button_layout = QHBoxLayout()
        save_button = QPushButton("Salva modifiche")
        save_button.clicked.connect(self.save_valve)
        delete_card_button = QPushButton("Cancella Scheda")
        delete_card_button.clicked.connect(self.prepare_new_valve)
        add_image_button = QPushButton("Aggiungi Immagine")
        add_image_button.clicked.connect(self.add_image)
        button_layout.addWidget(save_button)
        button_layout.addWidget(delete_card_button)
        button_layout.addWidget(add_image_button)

        form_layout.addRow(button_layout)

        tab_widget.addTab(details_widget, "Dettagli Valvola")

        report_widget = QWidget()
        report_layout = QVBoxLayout(report_widget)
        self.report_text = QTextEdit()
        self.report_text.setReadOnly(True)
        report_layout.addWidget(self.report_text)
        
        generate_report_button = QPushButton("Genera Report")
        generate_report_button.clicked.connect(self.generate_report)
        report_layout.addWidget(generate_report_button)
        
        export_report_button = QPushButton("Esporta Report")
        export_report_button.clicked.connect(self.export_report)
        report_layout.addWidget(export_report_button)

        tab_widget.addTab(report_widget, "Report")

        main_layout.addWidget(tab_widget, 2)

        central_widget = QWidget()
        central_widget.setLayout(main_layout)
        self.setCentralWidget(central_widget)

        self.load_valves()

    def init_tray(self):
        self.tray_icon = QSystemTrayIcon(self)
        self.tray_icon.setIcon(QIcon('valve_icon.png'))
        
        tray_menu = QMenu()
        show_action = tray_menu.addAction("Mostra")
        show_action.triggered.connect(self.show)
        quit_action = tray_menu.addAction("Esci")
        quit_action.triggered.connect(QApplication.quit)
        
        self.tray_icon.setContextMenu(tray_menu)
        self.tray_icon.show()

    def load_valves(self):
        self.valve_list.clear()
        self.cursor.execute("SELECT id, name FROM valves")
        for row in self.cursor.fetchall():
            self.valve_list.addItem(f"{row[0]}: {row[1]}")

    def show_valve_details(self, item):
        valve_id = item.text().split(':')[0]
        self.cursor.execute("SELECT * FROM valves WHERE id=?", (valve_id,))
        valve = self.cursor.fetchone()
        if valve:
            self.id_input.setText(valve[0])
            self.name_input.setText(valve[1])
            self.nominal_pressure_input.setText(valve[2])
            self.inlet_diameter_input.setText(valve[3])
            self.outlet_diameter_input.setText(valve[4])
            self.last_revision_date_input.setDate(QDate(valve[5]))
            revision_months = (valve[6] - valve[5]).days // 30
            self.next_revision_date_input.setCurrentIndex((revision_months // 12) - 1)
            if valve[7]:
                pixmap = QPixmap()
                pixmap.loadFromData(valve[7])
                self.image_label.setPixmap(pixmap)
            else:
                self.image_label.clear()

    def save_valve(self):
        current_item = self.valve_list.currentItem()
        if not current_item:
            QMessageBox.warning(self, "Nessuna valvola selezionata", "Seleziona una valvola da aggiornare.")
            return

        valve_id = self.id_input.text()
        name = self.name_input.text()
        nominal_pressure = self.nominal_pressure_input.text()
        inlet_diameter = self.inlet_diameter_input.text()
        outlet_diameter = self.outlet_diameter_input.text()
        last_revision_date = self.last_revision_date_input.date().toPyDate()
        next_revision_months = self.next_revision_date_input.currentData()
        next_revision_date = last_revision_date + timedelta(days=next_revision_months * 30)

        buffer = QBuffer()
        buffer.open(QBuffer.OpenModeFlag.ReadWrite)
        image = self.image_label.pixmap()
        if image:
            image.save(buffer, "PNG")
            image_data = buffer.data()
        else:
            image_data = None

        self.cursor.execute('''UPDATE valves
                               SET name=?, nominal_pressure=?, inlet_diameter=?, outlet_diameter=?, 
                                   last_revision_date=?, next_revision_date=?, image=?
                               WHERE id=?''',
                            (name, nominal_pressure, inlet_diameter, outlet_diameter, 
                             last_revision_date, next_revision_date, image_data, valve_id))
        self.conn.commit()
        QMessageBox.information(self, "Successo", "Valvola aggiornata con successo.")
        self.load_valves()

    def prepare_new_valve(self):
        self.id_input.clear()
        self.name_input.clear()
        self.nominal_pressure_input.clear()
        self.inlet_diameter_input.clear()
        self.outlet_diameter_input.clear()
        self.last_revision_date_input.setDate(QDate.currentDate())
        self.next_revision_date_input.setCurrentIndex(0)
        self.image_label.clear()

    def insert_valve(self):
        valve_id = self.id_input.text()
        name = self.name_input.text()
        nominal_pressure = self.nominal_pressure_input.text()
        inlet_diameter = self.inlet_diameter_input.text()
        outlet_diameter = self.outlet_diameter_input.text()
        last_revision_date = self.last_revision_date_input.date().toPyDate()
        next_revision_months = self.next_revision_date_input.currentData()
        next_revision_date = last_revision_date + timedelta(days=next_revision_months * 30)

        self.cursor.execute("SELECT id FROM valves WHERE id=?", (valve_id,))
        if self.cursor.fetchone():
            QMessageBox.warning(self, "Errore", "L'ID della valvola esiste già. Scegli un altro ID.")
            return

        buffer = QBuffer()
        buffer.open(QBuffer.OpenModeFlag.ReadWrite)
        image = self.image_label.pixmap()
        if image:
            image.save(buffer, "PNG")
            image_data = buffer.data()
        else:
            image_data = None

        self.cursor.execute('''INSERT INTO valves (id, name, nominal_pressure, inlet_diameter, outlet_diameter, last_revision_date, next_revision_date, image)
                               VALUES (?, ?, ?, ?, ?, ?, ?, ?)''',
                            (valve_id, name, nominal_pressure, inlet_diameter, outlet_diameter, last_revision_date, next_revision_date, image_data))
        self.conn.commit()
        QMessageBox.information(self, "Successo", "Valvola aggiunta con successo.")
        self.load_valves()

    def delete_valve(self):
        current_item = self.valve_list.currentItem()
        if not current_item:
            QMessageBox.warning(self, "Nessuna valvola selezionata", "Seleziona una valvola da eliminare.")
            return

        valve_id = current_item.text().split(':')[0]
        reply = QMessageBox.question(self, 'Conferma eliminazione', f'Sei sicuro di voler eliminare la valvola {valve_id}?',
                                     QMessageBox.StandardButton.Yes | QMessageBox.StandardButton.No, QMessageBox.StandardButton.No)

        if reply == QMessageBox.StandardButton.Yes:
            self.cursor.execute("DELETE FROM valves WHERE id=?", (valve_id,))
            self.conn.commit()
            QMessageBox.information(self, "Successo", "Valvola eliminata con successo.")
            self.load_valves()

    def add_image(self):
        options = QFileDialog.Options()
        file_name, _ = QFileDialog.getOpenFileName(self, "Seleziona immagine", "", "Immagini (*.png *.xpm *.jpg)", options=options)
        if file_name:
            pixmap = QPixmap(file_name)
            self.image_label.setPixmap(pixmap)

    def generate_report(self):
        self.cursor.execute("SELECT * FROM valves")
        valves = self.cursor.fetchall()
        report = "ID\tNome\tPressione Nominale\tDiametro Ingresso\tDiametro Uscita\tUltima Revisione\tProssima Revisione\n"
        for valve in valves:
            report += f"{valve[0]}\t{valve[1]}\t{valve[2]}\t{valve[3]}\t{valve[4]}\t{valve[5]}\t{valve[6]}\n"
        self.report_text.setText(report)

    def export_report(self):
        dialog = ExportDialog(self)
        if dialog.exec():
            export_format = dialog.get_format()
            file_name, _ = QFileDialog.getSaveFileName(self, "Salva Report", "", f"{export_format} Files (*.{export_format.lower()})")
            if file_name:
                self.cursor.execute("SELECT * FROM valves")
                valves = self.cursor.fetchall()
                if export_format == "PDF":
                    self.export_pdf(file_name, valves)
                elif export_format == "CSV":
                    self.export_csv(file_name, valves)
                elif export_format == "Excel":
                    self.export_excel(file_name, valves)
                QMessageBox.information(self, "Successo", f"Report esportato con successo come {export_format}.")

    def export_pdf(self, file_name, valves):
        c = canvas.Canvas(file_name, pagesize=letter)
        width, height = letter
        c.drawString(100, height - 100, "Report Valvole di Sicurezza")
        y = height - 150
        for valve in valves:
            valve_details = f"ID: {valve[0]}, Nome: {valve[1]}, Pressione Nominale: {valve[2]}, Diametro Ingresso: {valve[3]}, Diametro Uscita: {valve[4]}, Ultima Revisione: {valve[5]}, Prossima Revisione: {valve[6]}"
            c.drawString(100, y, valve_details)
            y -= 30
            if y < 100:
                c.showPage()
                y = height - 100
        c.save()

    def export_csv(self, file_name, valves):
        with open(file_name, mode='w', newline='') as file:
            writer = csv.writer(file)
            writer.writerow(["ID", "Nome", "Pressione Nominale", "Diametro Ingresso", "Diametro Uscita", "Ultima Revisione", "Prossima Revisione"])
            for valve in valves:
                writer.writerow(valve[:7])

    def export_excel(self, file_name, valves):
        wb = Workbook()
        ws = wb.active
        ws.append(["ID", "Nome", "Pressione Nominale", "Diametro Ingresso", "Diametro Uscita", "Ultima Revisione", "Prossima Revisione"])
        for valve in valves:
            ws.append(valve[:7])
        wb.save(file_name)

    def check_revisions(self):
        self.cursor.execute("SELECT id, name, next_revision_date FROM valves")
        valves = self.cursor.fetchall()
        today = date.today()
        for valve in valves:
            days_until_revision = (valve[2] - today).days
            if days_until_revision <= self.notification_days:
                self.tray_icon.showMessage(
                    "Promemoria Revisione",
                    f"La valvola {valve[1]} (ID: {valve[0]}) deve essere revisionata entro {days_until_revision} giorni.",
                    QSystemTrayIcon.MessageIcon.Warning
                )

if __name__ == "__main__":
    app = QApplication(sys.argv)
    manager = ValveManager()
    manager.show()
    sys.exit(app.exec())