''' COMMENTS
09-12-23
        --create 3 tabs 1st tab input for pogo insertion
        --2nd tab view edit sapnumber and price
        --3rd tabs view history 
09-13-23
        --creat funtions on 2nd and 3rd tab
        --remove arrow on date navifation use pop calendar instead
        --error on tab 2 while saving same value on sapnumber
        --change the process of add delete and modify sapnumber and price
        --error is due to same atribute name
        --change same table in the database to be float or REAL datatype to save decimal values in prices
        --add login form for admin using hardcoded credentials ATM.
09-14-23
        --add other warnings
        --add filter by date from - to date:
        --dropdown/sugest cleanup unique value
09-15-23
        --4th tab generate graph BHW vs Quantity and SAP# vs Quantity filter by date 
        --make graph copyable to be use in presenation 
09-16-23
        --fix alignmet of label to graph and arange from high to low
09-18-23
        --add top fail count in graph generation set default 10
        --center in main app the message warnings and login form
09-9-23
        --add filter by BHW that uses sapnumber generate graph
        --add backgound image
        --add voice warnings
        --add copy hightes cells in history
'''




import sys
import sqlite3
import datetime
import matplotlib.pyplot as plt
from matplotlib.backends.backend_qt5agg import FigureCanvasQTAgg as FigureCanvas
import matplotlib.dates as mdates
import getpass
from collections import defaultdict
from PyQt5 import QtCore, QtWidgets
from PyQt5.QtCore import QDate, Qt
from PyQt5.QtTextToSpeech import QTextToSpeech
from PyQt5.QtGui import QIcon, QImage, QClipboard, QPainter, QKeySequence
from PyQt5.QtWidgets import QApplication, QMainWindow, QLabel, QLineEdit, QVBoxLayout, QWidget, QCompleter, QDateTimeEdit, QSpinBox, QComboBox, QPushButton,\
    QMessageBox, QHBoxLayout, QTabWidget, QTableWidget, QTableWidgetItem, QDateEdit, QAction, QAbstractItemView, QDialog, QFormLayout, QFrame, QMenu
from PyQt5.QtGui import QPixmap
from io import BytesIO

#must be set to false in default
Authorize = False 


#-------------------------------------------------------------allow copy of cells----------------------------------------------------------------------------------------------
class CustomTableWidget(QTableWidget):
    def __init__(self, parent=None):
        super().__init__(parent)

    def keyPressEvent(self, event):
        if event.matches(QKeySequence.Copy):
            selected_indexes = self.selectedIndexes()
            if selected_indexes:
                selected_rows = set()
                for index in selected_indexes:
                    selected_rows.add(index.row())

                copied_text = ""
                for row in sorted(selected_rows):
                    row_text = "\t".join([self.item(row, col).text() for col in range(self.columnCount())])
                    copied_text += row_text + "\n"

                clipboard = QApplication.clipboard()
                clipboard.setText(copied_text)
        else:
            super().keyPressEvent(event)

#---------------------------------------------------------------------set background image in tab---------------------------------------------------------------------------
class CustomTabWithBackground(QWidget):
    def __init__(self, background_image_path):
        super().__init__()
        self.background_image = QPixmap(background_image_path)
        
    def paintEvent(self, event):
        painter = QPainter(self)

        painter.drawPixmap(self.rect(), self.background_image)
        painter.end()

#-----------------------------------------------------------------------loginForm Admin use to change add sap# and price------------------------------------------------------------
class LoginForm(QDialog):
    def __init__(self):
        super().__init__()
        self.setWindowTitle("Login")
        layout = QVBoxLayout()

        self.username_input = QLineEdit()
        self.password_input = QLineEdit()
        self.password_input.setEchoMode(QLineEdit.Password)

        login_button = QPushButton("Login")
        login_button.clicked.connect(self.login)
        login_button.setStyleSheet("background-color: red; color: white;")
        self.setWindowIcon(QIcon(r'C:\Users\ofer\Documents\pythons\Boss_eduard\icon.ico'))
        layout.addWidget(QLabel("Username:"))
        layout.addWidget(self.username_input)
        layout.addWidget(QLabel("Password:"))
        layout.addWidget(self.password_input)
        layout.addWidget(login_button)
        self.setLayout(layout)


    def login(self):
        global Authorize
        # Implement your login logic here
        username = self.username_input.text()
        password = self.password_input.text()

        if username and password:
            if username == "admin" and password == "LBAdmin":
                self.accept()  # Accept the dialog and close it
                Authorize = True
            else:
                self.show_message_box("Wrong Username or Password")
                print("Invalid credentials")
        else:
            self.show_message_box("Fillup Username and Password")
          
    def show_message_box(self, message):
        msg_box = QMessageBox()
        main_window_geometry = self.geometry()
        login_form_geometry = msg_box.geometry()
        x = (main_window_geometry.x() + (main_window_geometry.width() - login_form_geometry.width()) // 2) + 250
        y = (main_window_geometry.y() + (main_window_geometry.height() - login_form_geometry.height()) // 2)  + 150
        msg_box.setIcon(QMessageBox.Warning)
        msg_box.setWindowTitle("Warning")
        msg_box.setText(message)
        msg_box.move(x, y)
        msg_box.exec_()
#------------------------------------------------------------------------mainWindow-------------------------------------------------------       
class MainWindow(QMainWindow):
    def __init__(self):
        super().__init__()
        self.setWindowTitle("Pogopin Monitoring -v1.2")
        self.setWindowIcon(QIcon(r'C:\Users\ofer\Documents\pythons\Boss_eduard\icon.ico'))
        self.setGeometry(100, 100, 700, 400)
        self.tts = QTextToSpeech() 
        self.initUI()

    def initUI(self):
        # Create a menu bar
        menu_bar = self.menuBar()                                   # Add a "File" menu to the menu bar
        file_menu = menu_bar.addMenu("File")                        # Add an "Exit" action to the "File" menu
        exit_action = QAction("Exit", self)
        exit_action.triggered.connect(self.close)                   # Set the menu bar to the main window
        file_menu.addAction(exit_action)
        self.setMenuBar(menu_bar)

        admin_menu = menu_bar.addMenu("Admin")                      # Add an "Admin" menu to the menu bar
        self.admin_login = QAction("LogIn", self)                        # Add actions under the "Admin" menu
        self.admin_login.triggered.connect(self.open_login_form)         # Connect this action to a function that handles user management
        self.admin_login.setEnabled(True)
        admin_menu.addAction(self.admin_login)
        
        self.admin_logoff = QAction("LogOff", self)
        self.admin_logoff.triggered.connect(self.logout_admin)
        self.admin_logoff.setEnabled(False)                         # Connect this action to a function that handles system settings
        admin_menu.addAction(self.admin_logoff)

        # Set the menu bar to the main window
        self.setMenuBar(menu_bar)
        self.user = getpass.getuser()
        self.status_label = QLabel(f"AMS-OSRAM PHI: Standard User {self.user}")
        self.status_label.setStyleSheet("background-color: orange; color: white;")
        self.statusBar().addWidget(self.status_label, 1)
        
        self.status_label1 = QLabel("Author: [email protected]")
        self.status_label1.setStyleSheet("background-color: orange; color: white;")
        self.status_label1.setLayoutDirection(Qt.RightToLeft)  # Set layout direction to right-to-left
        self.status_label1.setAlignment(Qt.AlignRight)  # Set text alignment to right
        self.statusBar().addWidget(self.status_label1, 1)

        
        self.tab_widget = QTabWidget()  # Create a tab widget
        self.tab_widget.setGeometry(QtCore.QRect(30, 0, 961, 611))
        self.tab_widget.setTabShape(QtWidgets.QTabWidget.Triangular)
        '''            
        #create tabs 
        self.tab_1 = QWidget()
        self.create_data_entry_tab(self.tab_1)
        self.tab_widget.addTab(self.tab_1, "Pogopin Inputs")  # Add first tab
        '''
        # Create tabs
        self.tab_1 = CustomTabWithBackground("ams_OSRAM2.jpg")  # Replace with the actual image path
        self.create_data_entry_tab(self.tab_1)
        self.tab_widget.addTab(self.tab_1, "Pogopin Inputs")
        
        tab_2 = QWidget()
        self.SapNumberViewEdit(tab_2)
        self.tab_widget.addTab(tab_2, "SAP Number")  # Add second tab

        
        tab_3 = QWidget()
        self.viewhistory(tab_3)
        self.tab_widget.addTab(tab_3, "History")
        
        tab_4 = CustomTabWithBackground("ams_OSRAM.jpg")
        self.extract_data(tab_4)
        self.tab_widget.addTab(tab_4, "Extract Data")
        
        
        self.setCentralWidget(self.tab_widget)  # Set the tab widget as the central widget
        self.tab_widget.currentChanged.connect(self.tab_changed)
        self.current_graph_canvas = None  # To keep track of the current graph canvas
        self.add_sap = False
        
#--------------------------------------------------------------------------------------------------    #1st tab
    def update_sap_number_combo_box(self):
        sap_numbers = self.get_column_values('SAPNUMBER', 'SAPNUMBER')
        self.sap_number_input = QComboBox()
        self.sap_number_input.clear()
        self.sap_number_input.addItems(sap_numbers)
        self.sap_number_input.setFixedWidth(200)

    def create_data_entry_tab(self, tab_widget):
        #layout = QVBoxLayout()
        layout = QFormLayout()

        self.conn = sqlite3.connect('POGOINSERTION.db')     # Initialize database connection
        self.cursor = self.conn.cursor()

        # Fetch data from LOADBOARDS table for BHWName auto-suggest
        bhw_names = self.get_column_values('LOADBOARDS', 'LOADBOARDS')
        self.bhw_name_input = QLineEdit()
        bhw_name_completer = QCompleter(bhw_names)
        self.bhw_name_input.setCompleter(bhw_name_completer)
        self.bhw_name_input.setFixedWidth(200)
        
        self.update_sap_number_combo_box()

        def clean_and_normalize(value):
            return value.strip().lower()

        # Fetch data from POGOINSERTION table for ReplaceBy column
        query = 'SELECT ReplaceBy FROM POGOINSERTION'
        self.cursor.execute(query)
        replace_by_values = [clean_and_normalize(row[0]) for row in self.cursor.fetchall() if row[0] is not None]

        unique_replace_by_values = list(set(replace_by_values))

        self.replace_by_input = QLineEdit()
        user_name_completer = QCompleter(unique_replace_by_values)
        self.replace_by_input.setCompleter(user_name_completer)
        self.replace_by_input.setText(self.user)
        self.replace_by_input.setFixedWidth(200)

        # Other input fields
        self.run_count_input = QSpinBox()
        self.run_count_input.setMaximum(999999999)  # Set maximum value
        self.run_count_input.setFixedWidth(200)
        
        self.qty_of_pogo_input = QSpinBox()
        self.qty_of_pogo_input.setMaximum(999999999)  # Set maximum value
        self.qty_of_pogo_input.setFixedWidth(200)
        
        self.pogo_price_input = QSpinBox()
        self.pogo_price_input.setMaximum(999999999)  # Set maximum value
        self.pogo_price_input.setFixedWidth(200)
        
        self.site_input = QLineEdit()
        self.site_input.setFixedWidth(200)
        
        
        self.remarks_input = QLineEdit()
        self.remarks_input.setFixedWidth(300)

        # Add widgets to layout
        layout.addWidget(QLabel("BHW Name:"))
        layout.addWidget(self.bhw_name_input)

        layout.addWidget(QLabel("Date Replaced:"))
        current_date = QDate.currentDate()
        self.date_edit = QDateTimeEdit(current_date)
        self.date_edit.setDisplayFormat('yyyy-MM-dd')
        self.date_edit.setCalendarPopup(True)
        self.date_edit.setFixedWidth(200)
        
        #self.date_edit.setReadOnly(True)
        self.date_edit.setMaximumDate(current_date)
        layout.addWidget(self.date_edit)
        
        '''
        button_layout = QHBoxLayout()  # Horizontal layout for buttons
        move_up_button = QPushButton("← Date")
        move_down_button = QPushButton("Date →")
        move_up_button.clicked.connect(self.DateBackward)
        move_down_button.clicked.connect(self.DateForward)

        button_layout.addWidget(move_up_button)
        button_layout.addWidget(move_down_button)

        layout.addLayout(button_layout)  # Add the button layout to the main layout
        '''        
        
        layout.addWidget(QLabel("Sap Number:"))
        layout.addWidget(self.sap_number_input)


        layout.addWidget(QLabel("Runcount:"))
        layout.addWidget(self.run_count_input)
        
        layout.addWidget(QLabel("Quantity of Pogopin:"))
        layout.addWidget(self.qty_of_pogo_input)

        # Connect signals to calculate and update PogoPrice
        self.sap_number_input.currentIndexChanged.connect(self.update_pogo_price)
        self.qty_of_pogo_input.valueChanged.connect(self.update_pogo_price)

        layout.addWidget(QLabel("Pogopin Price in UERO:"))
        layout.addWidget(self.pogo_price_input)

        layout.addWidget(QLabel("Site's:"))
        layout.addWidget(self.site_input)

        layout.addWidget(QLabel("Replace By:"))
        layout.addWidget(self.replace_by_input)

        layout.addWidget(QLabel("Remarks:"))
        layout.addWidget(self.remarks_input)

        self.save_button = QPushButton("Save")
        self.save_button.clicked.connect(self.save_data)
        self.save_button.setFixedWidth(100)
        self.save_button.setStyleSheet("background-color: green; color: white;")  # Set background color and text color
        layout.addWidget(self.save_button)

        tab_widget.setLayout(layout)


#------------------------------------------------------------------------------------------------------    #2nd tab
    def SapNumberViewEdit(self, tab_widget):
        layout = QFormLayout()

        #self.table_widget_SAP = QTableWidget()
        self.table_widget_SAP = CustomTableWidget()
        self.load_data_to_table()
        self.table_widget_SAP.setEditTriggers(QAbstractItemView.DoubleClicked)
        self.table_widget_SAP.setSelectionBehavior(QAbstractItemView.SelectRows)
        layout.addRow(self.table_widget_SAP)  # Add the table to the form layout

        button_layout = QHBoxLayout()  # Create a horizontal layout for the buttons

        self.add_button = QPushButton("Add New")
        self.add_button.clicked.connect(self.add_new_row)
        self.add_button.setFixedWidth(200)
        self.add_button.setStyleSheet("background-color: blue; color: white;")
        button_layout.addWidget(self.add_button)  # Add the button to the horizontal layout

        self.save_button = QPushButton("Save Changes")
        self.save_button.clicked.connect(self.save_changes)
        self.save_button.setFixedWidth(200)
        self.save_button.setStyleSheet("background-color: green; color: white;")
        button_layout.addWidget(self.save_button)  # Add the button to the horizontal layout

        self.delete_button = QPushButton("Delete Selected")
        self.delete_button.clicked.connect(self.delete_selected_rows)
        self.delete_button.setFixedWidth(200)
        self.delete_button.setStyleSheet("background-color: red; color: white;")
        button_layout.addWidget(self.delete_button)  # Add the button to the horizontal layout

        button_layout.setAlignment(Qt.AlignCenter)  # Center-align the buttons horizontally
        layout.addRow(button_layout)  # Add the button layout to the form layout

        tab_widget.setLayout(layout)



#-------------------------------------------------------------------------------------------------------   #3rd tab 
    def viewhistory(self, tab_widget):
        layout = QVBoxLayout()
        
        #self.table_widget = QTableWidget()
        self.table_widget = CustomTableWidget()
        self.load_data_to_table_history()
        layout.addWidget(self.table_widget)

        # Inside the create_view_edit_tab() method
        filter_layout = QHBoxLayout()

        # Fetch data from LOADBOARDS table for BHWName dropdown
        bhw_names = self.get_column_values('LOADBOARDS', 'LOADBOARDS')
        self.filter_bhw_input = QComboBox()
        self.filter_bhw_input.addItems(bhw_names)

        # Add filter by BHW Name input to filter layout
        filter_layout.addWidget(QLabel("Filter by BHW Name:"))
        filter_layout.addWidget(self.filter_bhw_input)
        self.filter_bhw_button = QPushButton("Filter BHW")
        self.filter_bhw_button.clicked.connect(self.filter_by_bhw)
        self.filter_bhw_button.setStyleSheet("background-color: green; color: white;")  # Set background color and text color)
        filter_layout.addWidget(self.filter_bhw_button)

        # Add the filter layout to the main layout
        #layout.addLayout(filter_layout)
        '''
        # Filter by Date
        filter_layout.addWidget(QLabel("Filter by Date:"))
        self.filter_date_input = QDateEdit()
        self.filter_date_input.setDisplayFormat('yyyy-MM-dd')
        self.filter_date_input.setDate(QDate.currentDate())  # Set default date to current date
        self.filter_date_input.setCalendarPopup(True)
        self.filter_date_input.setMaximumDate(QDate.currentDate())  # Set maximum selectable date to current date
        filter_layout.addWidget(self.filter_date_input)

        '''

        filter_layout.addWidget(QLabel("Filter by Date (From):"))
        self.filter_date_input = QDateEdit()
        self.filter_date_input.setDisplayFormat('yyyy-MM-dd')
        self.filter_date_input.setDate(QDate.currentDate().addDays(-1))
        self.filter_date_input.setCalendarPopup(True)
        self.filter_date_input.setMaximumDate(QDate.currentDate())
        filter_layout.addWidget(self.filter_date_input)

        filter_layout.addWidget(QLabel("Filter by Date (To):"))
        self.filter_date_to_input = QDateEdit()
        self.filter_date_to_input.setDisplayFormat('yyyy-MM-dd')
        self.filter_date_to_input.setDate(QDate.currentDate())
        self.filter_date_to_input.setCalendarPopup(True)
        self.filter_date_to_input.setMaximumDate(QDate.currentDate())
        filter_layout.addWidget(self.filter_date_to_input)


        self.filter_date_button = QPushButton("Filter Date")
        self.filter_date_button.clicked.connect(self.filter_by_date)
        self.filter_date_button.setStyleSheet("background-color: green; color: white;")  # Set background color and text color)
        filter_layout.addWidget(self.filter_date_button)
        
        # Add Remove Filter button
        self.remove_filter_button = QPushButton("Filter Date and BHW")
        self.remove_filter_button.clicked.connect(self.apply_filters_date_bhw)
        self.remove_filter_button.setStyleSheet("background-color: green; color: white;")  # Set background color and text color)
        filter_layout.addWidget(self.remove_filter_button)


        self.remove_filter_button = QPushButton("Remove Filter")
        self.remove_filter_button.clicked.connect(self.remove_filter)
        self.remove_filter_button.setStyleSheet("background-color: red; color: white;")  # Set background color and text color)
        filter_layout.addWidget(self.remove_filter_button)

        layout.addLayout(filter_layout)
        tab_widget.setLayout(layout)

#--------------------------------------------------------------------------------------------------------------------- #4th tab
    def extract_data(self, tab_widget):
        layout = QVBoxLayout()

        self.graph_frame = QFrame()  # Frame to hold the graph
        self.graph_layout = QVBoxLayout()  # Layout inside the frame

        self.date_from = QDateEdit()
        self.date_to = QDateEdit()

        # Set default values
        default_date_to = datetime.datetime.now().date()
        default_date_from = default_date_to - datetime.timedelta(days=1)
        self.date_to.setDate(default_date_to)
        self.date_from.setDate(default_date_from)

        self.date_from.setCalendarPopup(True)  # Enable popup calendar
        self.date_to.setCalendarPopup(True)    # Enable popup calendar
        
        self.filter_combo = QComboBox()
        #self.filter_combo.addItem("No Filter")
        self.filter_combo.addItem("BHW Name")
        self.filter_combo.addItem("SAP Number")
        self.filter_combo.addItem("SAP Contrinutor")
        self.generate_graph_button = QPushButton("Analize Data")
        self.generate_graph_button.setStyleSheet("background-color: green; color: white;")  # Set background color and text color)
        self.remove_graph_button = QPushButton("Remove Graph")
        self.remove_graph_button.setStyleSheet("background-color: red; color: white;")  # Set background color and text color)
        layout.addWidget(self.graph_frame)
        filter_layout = QHBoxLayout()  # Horizontal layout for filter elements
        '''
        #add date limit to max date as now
        filter_layout.addWidget(QLabel("Date From:"))
        filter_layout.addWidget(self.date_from)
        filter_layout.addWidget(QLabel("Date To:"))
        filter_layout.addWidget(self.date_to)
        
        '''
        # Other input fields        
        filter_layout.addWidget(QLabel("Filter by Date (From):"))
        self.date_from = QDateEdit()
        self.date_from.setDisplayFormat('yyyy-MM-dd')
        self.date_from.setDate(QDate.currentDate().addDays(-1))
        self.date_from.setCalendarPopup(True)
        self.date_from.setMaximumDate(QDate.currentDate())
        filter_layout.addWidget(self.date_from)

        filter_layout.addWidget(QLabel("Filter by Date (To):"))
        self.date_to = QDateEdit()
        self.date_to.setDisplayFormat('yyyy-MM-dd')
        self.date_to.setDate(QDate.currentDate())
        self.date_to.setCalendarPopup(True)
        self.date_to.setMaximumDate(QDate.currentDate())
        filter_layout.addWidget(self.date_to)

        filter_layout.addWidget(QLabel("Sap Number: "))
        sap_numbers = self.get_column_values('SAPNUMBER', 'SAPNUMBER')
        self.sap_number_input = QComboBox()
        self.sap_number_input.clear()
        self.sap_number_input.addItems(sap_numbers)
        self.sap_number_input.setFixedWidth(100)
        filter_layout.addWidget(self.sap_number_input)

        top_layout = QHBoxLayout()
        top_label = QLabel("Top:")
        top_layout.addWidget(top_label)
        self.top = QSpinBox()
        self.top.setValue(10)
        self.top.setMaximum(100)  # Set maximum value
        self.top.setMinimum(1)
        self.top.setFixedWidth(70)
        top_layout.addWidget(self.top)

        filter_layout.addLayout(top_layout)
        
        filter_layout.addWidget(QLabel("Filter By:"))
        filter_layout.addWidget(self.filter_combo)
        filter_layout.addWidget(self.generate_graph_button)
        filter_layout.addWidget(self.remove_graph_button)
        layout.addLayout(filter_layout)

        tab_widget.setLayout(layout)
        
        self.generate_graph_button.clicked.connect(self.generate_graph)
        self.remove_graph_button.clicked.connect(self.remove_graph)
#####################################################################OUTSIDE THE TABS GENERATION / 1st tab~#############################################################################################

    def get_unique_column_values(self, table, column):
        self.cursor.execute(f'SELECT DISTINCT {column} FROM {table}')
        values = self.cursor.fetchall()
        return [value[0] for value in values]

    def get_column_values(self, table, column):
        self.cursor.execute(f'SELECT {column} FROM {table}')
        values = self.cursor.fetchall()
        return [value[0] for value in values]
    '''
    def DateBackward(self):
        new_date = self.date_edit.date().addDays(-1)
        self.date_edit.setDate(new_date)

    def DateForward(self):
        new_date = self.date_edit.date().addDays(1)
        if new_date <= QDate.currentDate():
            self.date_edit.setDate(new_date)
        else:
            self.show_message_box("Cannot select a future date.")
    '''
    '''
    def update_pogo_price(self):
        sap_number = self.sap_number_input.currentText()
        qty_of_pogo = self.qty_of_pogo_input.value()
        if sap_number and qty_of_pogo > 0:
            self.cursor.execute('SELECT PRICE FROM SAPNUMBER WHERE SAPNumber = ?', (sap_number,))
            fetched_price = self.cursor.fetchone()
            if fetched_price:
                sap_price = fetched_price[0]
                calculated_price = sap_price * qty_of_pogo
                self.pogo_price_input.setValue(calculated_price)
                self.pogo_price_input.setReadOnly(True)  # Set the input field as readonly
    '''
    
    def update_pogo_price(self):
        sap_number = self.sap_number_input.currentText()
        qty_of_pogo = self.qty_of_pogo_input.value()

        if qty_of_pogo == 0:
            self.pogo_price_input.lineEdit().setText("0.00")
        else:
            if sap_number and qty_of_pogo > 0:
                self.cursor.execute('SELECT PRICE FROM SAPNUMBER WHERE SAPNumber = ?', (sap_number,))
                fetched_price = self.cursor.fetchone()

                if fetched_price and fetched_price[0] is not None:
                    sap_price = float(fetched_price[0])  # Convert the fetched price to float
                    calculated_price = sap_price * qty_of_pogo
                    self.pogo_price_input.setReadOnly(True)  # Set the input field as readonly
                    displayed_value = f"{calculated_price:.2f}"  # Display float value with two decimal places and unit
                    self.price = displayed_value.replace(" uero", "")
                    self.pogo_price_input.lineEdit().setText(displayed_value)
                else:
                    self.pogo_price_input.clear()  # Clear the input field if price is not found or is None
                    self.pogo_price_input.setReadOnly(False)  # Allow editing since price is not available

    
    def save_data(self):
        bhw_name = self.bhw_name_input.text()
        date_replaced = QDate.currentDate().toString('yyyy-MM-dd')
        sap_number = self.sap_number_input.currentText()
        run_count = self.run_count_input.value()
        qty_of_pogo = self.qty_of_pogo_input.value()
        pogo_price = self.pogo_price_input.value()
        site = self.site_input.text()
        replace_by = self.replace_by_input.text()
        remarks = self.remarks_input.text()
        length = len(bhw_name)
                
        bhw_names_list = self.get_column_values('LOADBOARDS', 'LOADBOARDS')
        #check id BWH is empty or in list else alarm or add to list
        if not bhw_name:
            self.show_message_box("BHW Name field is empty")
            return 
        
        if bhw_name.startswith('HSDI'):
            max_len = 7
            min_len = 6
        elif bhw_name.startswith('TOWER'):
            max_len = 8
            min_len = 7
        else:
            max_len = 18
            min_len = 16
        print(f"{max_len}, {min_len}")
        if length < min_len or length > max_len:
            self.show_message_box(f"Posible Invalid New Hardware To Add\n{bhw_name}")
            return
        
        if bhw_name not in bhw_names_list:
            print(bhw_name)
            if bhw_name not in bhw_names_list:
                reply = QMessageBox.question(self, 'Confirmation', f'BHWName "{bhw_name}" is not in the list. Do you want to add it?', QMessageBox.Yes | QMessageBox.No)
                if reply == QMessageBox.Yes:
                    # Insert BHW name into LOADBOARDS table
                    self.cursor.execute('INSERT INTO LOADBOARDS (LOADBOARDS) VALUES (?)', (bhw_name,))
                    self.conn.commit()
                    bhw_names_list = self.get_column_values('LOADBOARDS', 'LOADBOARDS')
                    print(f'BHWName "{bhw_name}" added to LOADBOARDS table.')
                else:
                    return

        #check other input if filled
        if not site:
            self.show_message_box("site field is empty")
            return
        if not replace_by:
            self.show_message_box("replace by field is empty")
            return
        if not remarks:
            self.show_message_box("remarks by field is empty")
            return 
        if run_count == 0:
            self.show_message_box("run count must be greater than 0")
            return        
        if qty_of_pogo == 0:
            self.show_message_box("qty of pogo must be greater than 0")
            return  
        '''        
        #remove due to it was auto calculated according to sap and quantity 
        if pogo_price == 0:
            self.show_message_box("pogo price must be greater than 0")
            return    
        '''
                
        # Insert data into POGOINSERTION table
        self.cursor.execute('''
            INSERT INTO POGOINSERTION (BHWName, DateReplaced, RunCount, SapNumber, QtyOfPogo, PogoPrice, Site, ReplaceBy, Remarks)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', (bhw_name, date_replaced, run_count, sap_number, qty_of_pogo, self.price, site, replace_by, remarks))

        # Commit the changes and close the connection
        self.conn.commit()
        print("Data saved to POGOINSERTION table.")
        self.show_message_box("Data Saved")
        # Clear input fields
        self.bhw_name_input.clear()
        self.run_count_input.setValue(0)
        self.qty_of_pogo_input.setValue(0)
        self.pogo_price_input.setValue(0)
        self.site_input.clear()
        self.replace_by_input.clear()
        self.remarks_input.clear()
        self.load_data_to_table_history()
        
################################################################## TAB 2 Function#####################################################################
    def load_data_to_table(self):
        self.conn = sqlite3.connect('POGOINSERTION.db')
        self.cursor = self.conn.cursor()

        self.table_widget_SAP.setRowCount(0)
        self.table_widget_SAP.setColumnCount(2)
        self.table_widget_SAP.setHorizontalHeaderLabels(["SAP Number", "Price"])

        self.cursor.execute("SELECT SAPNumber, PRICE FROM SAPNUMBER")
        data = self.cursor.fetchall()
        for row_number, row_data in enumerate(data):
            self.table_widget_SAP.insertRow(row_number)
            for column_number, item in enumerate(row_data):
                table_item = QTableWidgetItem(str(item))
                table_item.setFlags(Qt.ItemIsSelectable | Qt.ItemIsEditable | Qt.ItemIsEnabled)  # Allow editing
                self.table_widget_SAP.setItem(row_number, column_number, table_item)
        # Add grid lines between the labels and the first line
        self.table_widget_SAP.setShowGrid(True)
        self.table_widget_SAP.setStyleSheet("gridline-color: gray;")



    def save_changes(self):
        if Authorize:
            # Save changes made to the table widget
            for row in range(self.table_widget_SAP.rowCount()):
                sap_number_item = self.table_widget_SAP.item(row, 0)
                price_item = self.table_widget_SAP.item(row, 1)
                if sap_number_item and price_item:
                    sap_number = sap_number_item.text()
                    price = price_item.text()
                    if sap_number and price:
                        self.cursor.execute('INSERT OR REPLACE INTO SAPNUMBER (SAPNumber, PRICE) VALUES (?, ?)', (sap_number, price))
            self.conn.commit()
            self.load_data_to_table()
            print("Changes saved.")
            if self.add_sap:
                QMessageBox.question(self, "Success", f"SAP:{sap_number}, PRICE:{price}\nData saved!!", QMessageBox.Ok)
            else:
                QMessageBox.question(self, "Success","Data saved!!", QMessageBox.Ok)
        else:
            print("denied")
            self.speak_text("Please Login As Admin!")
            self.open_login_form()
            #self.show_message_box("Please Login As Admin!")


    def add_new_row(self):
        if Authorize:
            # Add a new row to the table widget
            row_position = self.table_widget_SAP.rowCount()
            self.table_widget_SAP.insertRow(row_position)
            self.add_sap = True
        else:
            print("denied")
            self.speak_text("Please Login As Admin!")
            self.open_login_form()
            #self.show_message_box("Please Login As Admin!")

    '''    
    def delete_selected_rows(self):
        if Authorize:
            # Delete selected rows from the table widget
            selected_rows = self.table_widget_SAP.selectionModel().selectedRows()
            for row in selected_rows:
                self.table_widget_SAP.removeRow(row.row())
        else:
            print("denied")
            self.show_message_box("Please Login As Admin!")
        '''
     
    def delete_selected_rows(self):
        if Authorize:
            selected_rows = self.table_widget_SAP.selectionModel().selectedRows()
            if len(selected_rows) == 0:
                return
            sap_items = [self.table_widget_SAP.item(row.row(), 0) for row in selected_rows]
            sap_numbers = [sap_item.text() for sap_item in sap_items if sap_item is not None]
            if not sap_numbers:
                return
            confirmation_text = f'Delete {", ".join(sap_numbers)}?'
            reply = QMessageBox.question(self, 'Confirmation', confirmation_text, QMessageBox.Yes | QMessageBox.No)
            
            if reply == QMessageBox.Yes:
                # Delete selected rows from the table widget
                for row in selected_rows:
                    sap_item = self.table_widget_SAP.item(row.row(), 0)  # Get the SAP item of the row
                    if sap_item is not None:
                        sap_number = sap_item.text()
                        self.cursor.execute('DELETE FROM SAPNUMBER WHERE SAPNumber = ?', (sap_number,))
                        self.table_widget_SAP.removeRow(row.row())  # Remove the row from the table widget

                self.conn.commit()  # Commit the changes to the database
                self.update_sap_number_combo_box()  # Update the combo box
        else:
            print("denied")
            self.speak_text("Please Login As Admin!")
            self.open_login_form()
            #self.show_message_box("Please Login As Admin!")


     
       
#######################################################3rd Tab Function###########################################################
    def load_data_to_table_history(self):
        self.table_widget.setRowCount(0)
        self.table_widget.setColumnCount(9)
        self.table_widget.setHorizontalHeaderLabels([
            "BHW Name", "Date Replaced", "Last run Count", "SAP# of Pogo Pin Used",
            "Qty. of Pogo Pins Replaced per Board", "Pogo Pin Total Price in Euro",
            "Site/s", "Replaced by", "Remarks"
        ])

        self.cursor.execute('SELECT * FROM POGOINSERTION')
        data = self.cursor.fetchall()

        # Reverse the order of fetched data
        data.reverse()

        for row_number, row_data in enumerate(data):
            self.table_widget.insertRow(row_number)
            for column_number, cell_data in enumerate(row_data):
                item = QTableWidgetItem(str(cell_data))
                item.setFlags(item.flags() | Qt.ItemIsSelectable | Qt.ItemIsEnabled)  # Allow selection
                self.table_widget.setItem(row_number, column_number, item)

        # Add horizontal lines between the rows
        self.table_widget.setShowGrid(False)
        self.table_widget.setAlternatingRowColors(True)
        self.table_widget.setStyleSheet(
            "alternate-background-color: #f0f0f0; selection-background-color: #a6e1fa;")

        # Add grid lines between the labels and the first line
        self.table_widget.setShowGrid(True)
        self.table_widget.setStyleSheet("gridline-color: gray;")

        # Resize columns to content
        self.table_widget.resizeColumnsToContents()

        # Show all rows before populating the table
        for row in range(self.table_widget.rowCount()):
            self.table_widget.showRow(row)

        # Set selection mode to allow copying entire rows
        self.table_widget.setSelectionMode(QAbstractItemView.ContiguousSelection)



    def filter_by_bhw(self):
        filter_text = self.filter_bhw_input.currentText()  # Use currentText() to get the selected BHW Name
        if not filter_text:
            self.show_message_box("Can't filter empty BHW or Date")
            return
        for row in range(self.table_widget.rowCount()):
            item = self.table_widget.item(row, 0)  # Assuming BHW Name is in the first column
            if filter_text:
                if item.text() == filter_text:
                    self.table_widget.setRowHidden(row, False)  # Show the row
                else:
                    self.table_widget.setRowHidden(row, True)  # Hide the row
            else:
                self.table_widget.setRowHidden(row, False)  # Show all rows if no filter selected


    def remove_filter(self):
        self.filter_bhw_input.setCurrentIndex(-1)  # Clear the selected BHW Name
        self.filter_date_input.setDate(QDate.currentDate())  # Reset the date filter
        self.load_data_to_table_history()  # Load all data
    '''
    def filter_by_date(self):
        selected_date = self.filter_date_input.date().toString('yyyy-MM-dd')
        for row in range(self.table_widget.rowCount()):
            date_item = self.table_widget.item(row, 1)  # Assuming Date Replaced is in the second column
            if selected_date:
                if date_item.text() == selected_date:
                    self.table_widget.setRowHidden(row, False)
                else:
                    self.table_widget.setRowHidden(row, True)
            else:
                self.table_widget.setRowHidden(row, False)
    '''

    def filter_by_date(self):
        selected_from_date = self.filter_date_input.date().toString('yyyy-MM-dd')
        selected_to_date = self.filter_date_to_input.date().toString('yyyy-MM-dd')
        
        if selected_from_date > selected_to_date:
            self.speak_text(f"Error: 'From' date should be less than or equal to 'To' date")
            print("Error: 'From' date should be less than or equal to 'To' date")
            self.show_message_box(f"Error: 'From' date should be less than or equal to 'To' date")
            return
        
        for row in range(self.table_widget.rowCount()):
            date_item = self.table_widget.item(row, 1)  # Assuming Date Replaced is in the second column
            if selected_from_date <= date_item.text() <= selected_to_date:
                self.table_widget.setRowHidden(row, False)
            else:
                self.table_widget.setRowHidden(row, True)


    def apply_filters_date_bhw(self):
        selected_from_date = self.filter_date_input.date().toString('yyyy-MM-dd')
        selected_to_date = self.filter_date_to_input.date().toString('yyyy-MM-dd')
        filter_text = self.filter_bhw_input.currentText()  # Use currentText() to get the selected BHW Name
        if not filter_text:
            self.show_message_box("Can't filter empty BHW or Date")
            return
        if selected_from_date > selected_to_date:
            print("Error: 'From' date should be less than or equal to 'To' date")
            self.speak_text(f"Error: 'From' date should be less than or equal to 'To' date")
            self.show_message_box(f"Error: 'From' date should be less than or equal to 'To' date")
            return

        for row in range(self.table_widget.rowCount()):
            date_item = self.table_widget.item(row, 1)  # Assuming Date Replaced is in the second column
            bhw_item = self.table_widget.item(row, 0)   # Assuming BHW Name is in the first column

            date_condition = selected_from_date <= date_item.text() <= selected_to_date
            bhw_condition = filter_text == "" or bhw_item.text() == filter_text

            if date_condition and bhw_condition:
                self.table_widget.setRowHidden(row, False)
            else:
                self.table_widget.setRowHidden(row, True)




    # Add the filter_by_bhw method as you have previously defined
    
###################################################################4th tab Ectract Data Function#############################################################################
        
    def generate_graph(self):
        #graph count filter
        print(self.top.value())
        top_fail = self.top.value()

        if top_fail == 0:
            top_fail = 10

        self.conn = sqlite3.connect("POGOINSERTION.db")
        cursor = self.conn.cursor()

        date_from = self.date_from.date().toPyDate()
        date_to = self.date_to.date().toPyDate()

        # Define fig before using it
        fig, ax = plt.subplots()

        if date_from > date_to:
            print("Error: 'From' date should be less than or equal to 'To' date")
            self.speak_text(f"Error: 'From' date should be less than or equal to 'To' date")
            self.show_message_box(f"Error: 'From' date should be less than or equal to 'To' date")
            return

        filter_index = self.filter_combo.currentIndex()
        filter_label = ""
        if filter_index == 0:
            filter_label = "Hardware"
        if filter_index == 1:
            filter_label = "SAP Number"
        elif filter_index == 2:
            selected_sap_number = self.sap_number_input.currentText()
            self.generate_bhw_graph_by_sap_number(date_from, date_to, selected_sap_number, top_fail)
            return
        '''
        if  self.current_graph_canvas is not None:
            self.graph_layout.removeWidget(self.current_graph_canvas)
            self.current_graph_canvas.deleteLater()
            self.current_graph_canvas = None
        '''
        query = "SELECT BHWName, SapNumber, QtyOfPogo FROM POGOINSERTION WHERE DateReplaced BETWEEN ? AND ?"
        cursor.execute(query, (date_from, date_to))

        data = cursor.fetchall()
        #self.conn.close()
        
        bhw_sap_quantities = defaultdict(int)
        x_labels = []

        for row in data:
            if filter_index == 1:
                x_label = str(row[1])  # Convert SAP Number to string
            else:
                x_label = row[0]  # Use BHW Name as x_label

            if x_label is not None:
                x_labels.append(x_label)
                bhw_sap_quantities[x_label] += row[2]

        sorted_quantities_dict = dict(sorted(bhw_sap_quantities.items(), key=lambda item: item[1], reverse=True),)
        if not sorted_quantities_dict:
            self.speak_text("Cant Graph Empty Data!\nPlease set Date's with available Data")
            self.show_message_box("Cant Graph Empty Data!\nPlease set Date's with available Data")
            return
        else:
            self.remove_graph()

        # Get the first 5 items from the sorted dictionary
        sorted_labels = list(sorted_quantities_dict.keys())[:top_fail]
        sorted_quantities = list(sorted_quantities_dict.values())[:top_fail]
        print(f"{sorted_labels}, {sorted_quantities}")
        x_positions = range(len(sorted_labels))

        bars = ax.bar(x_positions, sorted_quantities, label='QtyOfPogo')

        for i, bar in enumerate(bars):
            yval = bar.get_height()
            plt.text(bar.get_x() + bar.get_width()/2.0, yval, int(yval), va='bottom', ha='center')

        ax.set_xlabel(filter_label)
        ax.set_ylabel('Qty of Pogo')
        ax.set_title(f'{filter_label} vs Quantity from {date_from} to {date_to}')
        ax.set_xticks(x_positions)  # Use the original positions
        ax.set_xticklabels(sorted_labels, rotation=45, ha='right')
        ax.legend()

        plt.tight_layout()

        # Re-assign the current_graph_canvas
        self.current_graph_canvas = FigureCanvas(fig)
        self.graph_layout.addWidget(self.current_graph_canvas)
        self.graph_frame.setLayout(self.graph_layout)

        # Add context menu for copying the graph
        self.current_graph_canvas.setContextMenuPolicy(Qt.CustomContextMenu)
        self.current_graph_canvas.customContextMenuRequested.connect(self.show_context_menu)

    def generate_bhw_graph_by_sap_number(self, date_from, date_to, selected_sap_number, top_fail):
        query = "SELECT BHWName, SapNumber, QtyOfPogo FROM POGOINSERTION WHERE DateReplaced BETWEEN ? AND ? AND SapNumber = ?"
        cursor = self.conn.cursor()  # Reuse the existing cursor
        cursor.execute(query, (date_from, date_to, selected_sap_number))
        data = cursor.fetchall()

        bhw_sap_quantities = defaultdict(int)
        x_labels = []

        for row in data:
            x_label = row[0]  # Use BHW Name as x_label
            x_labels.append(x_label)
            bhw_sap_quantities[x_label] += row[2]

        sorted_quantities_dict = dict(sorted(bhw_sap_quantities.items(), key=lambda item: item[1], reverse=True))
        if not sorted_quantities_dict:
            self.speak_text(f"Cant Graph Empty Data!\nPlease set Date's and Sap Number with available Data")
            self.show_message_box(f"Cant Graph Empty Data!\nPlease set Date's and Sap Number with available Data")
            return
        else:
            self.remove_graph()

        sorted_labels = list(sorted_quantities_dict.keys())[:top_fail]
        sorted_quantities = [sorted_quantities_dict[label] for label in sorted_labels]  # Only select corresponding quantities

        print(f"{sorted_labels}, {sorted_quantities}")

        fig, ax = plt.subplots()
        x_positions = range(len(sorted_labels))
        bars = ax.bar(x_positions, sorted_quantities, label='QtyOfPogo')

        for i, bar in enumerate(bars):
            yval = bar.get_height()
            plt.text(bar.get_x() + bar.get_width()/2.0, yval, int(yval), va='bottom', ha='center')

        ax.set_xlabel("BHW Name")
        ax.set_ylabel('Qty of Pogo')
        ax.set_title(f'Hardware using SAP Number {selected_sap_number} from {date_from} to {date_to}')
        ax.set_xticks(x_positions)
        ax.set_xticklabels(sorted_labels, rotation=45, ha='right')
        ax.legend()

        plt.tight_layout()

        # Re-assign the current_graph_canvas
        self.current_graph_canvas = FigureCanvas(fig)
        self.graph_layout.addWidget(self.current_graph_canvas)
        self.graph_frame.setLayout(self.graph_layout)

        # Add context menu for copying the graph
        self.current_graph_canvas.setContextMenuPolicy(Qt.CustomContextMenu)
        self.current_graph_canvas.customContextMenuRequested.connect(self.show_context_menu)


    def show_context_menu(self, point):
        context_menu = QMenu(self)
        copy_action = QAction("Copy Graph", self)
        copy_action.triggered.connect(self.copy_graph)
        context_menu.addAction(copy_action)
        context_menu.exec_(self.current_graph_canvas.mapToGlobal(point))

    def copy_graph(self):
        buffer = BytesIO()
        self.current_graph_canvas.figure.savefig(buffer, format="png")
        img = QImage.fromData(buffer.getvalue())
        QApplication.clipboard().setImage(img, mode=QClipboard.Clipboard)

    def remove_graph(self):
        if  self.current_graph_canvas is not None:
            self.graph_layout.removeWidget(self.current_graph_canvas)
            self.current_graph_canvas.deleteLater()
            self.current_graph_canvas = None


###########################################################Closing Events and general Function#######################################################################
    def show_message_box(self, message):
        msg_box = QMessageBox()
        main_window_geometry = self.geometry()
        login_form_geometry = msg_box.geometry()
        x = (main_window_geometry.x() + (main_window_geometry.width() - login_form_geometry.width()) // 2) + 250
        y = (main_window_geometry.y() + (main_window_geometry.height() - login_form_geometry.height()) // 2)  + 150
        msg_box.setIcon(QMessageBox.Warning)
        msg_box.setWindowTitle("Warning")
        msg_box.setText(message)
        msg_box.move(x, y)
        msg_box.exec_()
        

    def change_status_label(self, new_text):
        self.status_label.setText(new_text)
        
    def open_login_form(self):
        login_form = LoginForm()
        # Center the login form on the main window
        main_window_geometry = self.geometry()
        login_form_geometry = login_form.geometry()
        x = (main_window_geometry.x() + (main_window_geometry.width() - login_form_geometry.width()) // 2) + 250
        y = (main_window_geometry.y() + (main_window_geometry.height() - login_form_geometry.height()) // 2)  + 150
        login_form.move(x, y)
        if login_form.exec_() == QDialog.Accepted:
            self.admin_logoff.setEnabled(True)
            self.admin_login.setEnabled(False)
            ex.change_status_label(f"AMS-OSRAM PHI: Admin User {self.user}")
            self.status_label.setStyleSheet("background-color: green; color: white;")
            print("Login successful")



    def logout_admin(self):
        global Authorize
        Authorize = False
        '''
        self.add_button.setEnabled(False)
        self.save_button.setEnabled(False)
        self.delete_button.setEnabled(False)
        '''
        self.admin_logoff.setEnabled(False)
        self.admin_login.setEnabled(True)
        ex.change_status_label(f"AMS-OSRAM PHI: Standard User {self.user}")
        self.status_label.setStyleSheet("background-color: orange; color: white;")


    def set_tts_voice(self, voice_name):
        available_voices = self.tts.availableVoices()
        for voice in available_voices:
            if voice.name() == voice_name:
                print(voice.name())
                self.tts.setVoice(voice)
                break

    def speak_text(self, text):
        self.tts.setVolume(.5)
        self.set_tts_voice("Microsoft Zira Desktop - English (United States)")
        if self.tts.state() == QTextToSpeech.State.Ready:
            self.tts.say(text)
        else:
            print("Text-to-speech engine is not ready.")



    #disable this test only
    def tab_changed(self, index):
        if not self.isFullScreen():  # Check if the window is not in full-screen mode
            if index == 0:
                #self.resize(700, 400)  # Adjust size for Tab 1
                pass
            elif index == 1:
                #self.resize(700, 400)  # Adjust size for Tab 2
                pass
            elif index == 2:
                #self.resize(1600, 600)  # Adjust size for Tab 3
                pass
            elif index == 3:
                pass

    def closeEvent(self, event):
        print(f"closing ")
        self.conn.close() #close the db file when closing the app

if __name__ == '__main__':
    app = QApplication(sys.argv)
    ex = MainWindow()
    ex.show()
    sys.exit(app.exec_())