import tkinter as tk
from tkinter import ttk, messagebox, filedialog, scrolledtext
import pandas as pd
import openpyxl
from openpyxl.utils.dataframe import dataframe_to_rows
from datetime import datetime
from tkcalendar import DateEntry

class WorkOrderSystem:
    def __init__(self, root):
        self.root = root
        self.root.title("Work Order System")

        # Make the window maximize by default
        self.root.state('zoomed')

        # Add scrollbar for the entire window
        self.canvas = tk.Canvas(root)
        self.scrollbar = ttk.Scrollbar(root, orient="vertical", command=self.canvas.yview)
        self.scrollable_frame = ttk.Frame(self.canvas)

        self.scrollable_frame.bind(
            "<Configure>",
            lambda e: self.canvas.configure(scrollregion=self.canvas.bbox("all"))
        )

        self.canvas.create_window((0, 0), window=self.scrollable_frame, anchor="nw")
        self.canvas.configure(yscrollcommand=self.scrollbar.set)

        # Pack the canvas and scrollbar
        self.canvas.pack(side="left", fill="both", expand=True)
        self.scrollbar.pack(side="right", fill="y")

        # Bind mouse wheel
        self.canvas.bind_all("<MouseWheel>", self._on_mousewheel)

        # Main frame
        self.main_frame = ttk.Frame(self.scrollable_frame, padding="10")
        self.main_frame.grid(row=0, column=0, sticky=(tk.W, tk.E, tk.N, tk.S))

        # Required columns definition with skip columns
        self.required_columns = [
            'Item', 'Work Order', 'Report Date', 'Report Time', 'Location',
            'Car no.', 'Faulty System', 'Service Impact CO/PU/CL/WD',
            'Init.', 'Acc.', 'Desc', 'System', 'Component/Symptom',
            'Fault Code', 'Start Work', 'End Work', 'Related Contract',
            'Contact Person', 'Finding', 'SKIP1', 'SKIP2',  # Skip columns
            'Replaced Equipment', 'Out S/N', 'In S/N', 'Status',
            'Target Completion Date', 'Cat. Of Systematic Failure',
            'Pending for Spare', 'Consequence Code',
            'Reporting in Stop Car List', 'Passenger complaint'
        ]

        # Excel file path and sheet name
        self.excel_file = "SIL EMU work order TEST1.xlsx"
        self.sheet_name = "Date"

        # Initialize variables
        self.initialize_variables()

        # Load Excel data
        try:
            # Read Excel file with object dtype first
            self.df = pd.read_excel(
                self.excel_file,
                sheet_name=self.sheet_name,
                dtype=str  # Read all columns as strings initially
            ).fillna('')

            # Convert date columns after reading
            date_columns = ['Report Date', 'Start Work', 'End Work']
            for col in date_columns:
                if col in self.df.columns:
                    try:
                        self.df[col] = pd.to_datetime(
                            self.df[col], 
                            format='%d/%m/%Y', 
                            dayfirst=True,
                            errors='coerce'
                        )
                    except Exception as e:
                        print(f"Error converting {col}: {str(e)}")
                        self.df[col] = pd.to_datetime(
                            self.df[col], 
                            dayfirst=True,
                            errors='coerce'
                        )

            # Format dates after loading
            if 'Report Date' in self.df.columns:
                self.df['Report Date'] = pd.to_datetime(self.df['Report Date']).dt.strftime('%d/%m/%Y')
            if 'Start Work' in self.df.columns:
                self.df['Start Work'] = pd.to_datetime(self.df['Start Work']).dt.strftime('%d/%m/%Y %H:%M:%S')
            if 'End Work' in self.df.columns:
                self.df['End Work'] = pd.to_datetime(self.df['End Work']).dt.strftime('%d/%m/%Y %H:%M:%S')

            self.all_system_options = self.load_system_options(self.df)

            # Check and add missing columns
            for col in self.required_columns:
                if col not in self.df.columns:
                    self.df[col] = ''

            # Ensure column order
            self.df = self.df[self.required_columns]

        except FileNotFoundError:
            self.df = pd.DataFrame(columns=self.required_columns)
            with pd.ExcelWriter(self.excel_file, engine='openpyxl') as writer:
                self.df.to_excel(writer, sheet_name=self.sheet_name, index=False)
            self.all_system_options = []
        except Exception as e:
            messagebox.showerror("Error", f"Error loading Excel file: {str(e)}")
            self.df = pd.DataFrame(columns=self.required_columns)
            self.all_system_options = []

        # Create interface elements
        self.create_basic_info_frame()
        self.create_system_info_frame()
        self.create_work_info_frame()
        self.create_description_frame()
        self.create_equipment_frame()
        self.create_status_frame()
        self.create_buttons()

        # Initialize record window and tree
        self.record_window = None
        self.tree = None

        # Bind events
        self.bind_events()

    def _on_mousewheel(self, event):
        self.canvas.yview_scroll(int(-1*(event.delta/120)), "units")

    def initialize_variables(self):
        # Basic information variables
        self.search_var = tk.StringVar()
        self.item_var = tk.StringVar()
        self.work_order_var = tk.StringVar()
        self.date_var = tk.StringVar(value=datetime.now().strftime('%d/%m/%Y'))
        self.time_var = tk.StringVar(value=datetime.now().strftime('%H:%M'))
        self.location_var = tk.StringVar(value="WCD")
        self.car_no_var = tk.StringVar()
        self.faulty_system_var = tk.StringVar()
        self.service_impact_var = tk.StringVar()
        self.init_var = tk.StringVar()
        self.acc_var = tk.StringVar()

        # System information variables
        self.system_var = tk.StringVar()
        self.component_var = tk.StringVar()
        self.fault_code_var = tk.StringVar()

        # Work information variables
        self.start_work_time_var = tk.StringVar()
        self.end_work_time_var = tk.StringVar()
        self.related_contract_var = tk.StringVar()
        self.contact_person_var = tk.StringVar()

        # Equipment variables
        self.replaced_equipment_var = tk.StringVar()
        self.out_sn_var = tk.StringVar()
        self.in_sn_var = tk.StringVar()

        # Status variables
        self.status_var = tk.StringVar()
        self.target_completion_var = tk.StringVar()
        self.systematic_failure_var = tk.StringVar()
        self.pending_spare_var = tk.StringVar()
        self.consequence_code_var = tk.StringVar()
        
        # Changed to BooleanVar for checkboxes
        self.stop_car_var = tk.BooleanVar(value=False)
        self.passenger_complaint_var = tk.BooleanVar(value=False)

    @staticmethod
    def load_system_options(df):
        """Load unique system options from the DataFrame"""
        if 'System' in df.columns:
            systems = df['System'].fillna('').astype(str)
            unique_systems = sorted(set(s.strip() for s in systems if s.strip()))
            return unique_systems
        return []
        
    def create_basic_info_frame(self):
        frame = ttk.LabelFrame(self.main_frame, text="Basic Information", padding="10")
        frame.grid(row=0, column=0, columnspan=2, sticky=(tk.W, tk.E), padx=10, pady=5)
        frame.grid_columnconfigure(1, weight=1)
        frame.grid_columnconfigure(3, weight=1)
        frame.grid_columnconfigure(5, weight=1)

        # Work Order
        ttk.Label(frame, text="Work Order:").grid(row=0, column=0, sticky=tk.W, padx=5, pady=5)
        ttk.Entry(frame, textvariable=self.work_order_var, width=20).grid(row=0, column=1, sticky=(tk.W, tk.E), padx=5)

        # Report Date
        ttk.Label(frame, text="Report Date:").grid(row=0, column=2, sticky=tk.W, padx=5)
        self.report_date = DateEntry(frame, width=15, background='darkblue',
                                   foreground='white', borderwidth=2,
                                   textvariable=self.date_var,
                                   date_pattern='dd/mm/yyyy')
        self.report_date.grid(row=0, column=3, sticky=(tk.W, tk.E), padx=5)

        # Report Time
        ttk.Label(frame, text="Report Time:").grid(row=0, column=4, sticky=tk.W, padx=5)
        ttk.Entry(frame, textvariable=self.time_var, width=15).grid(row=0, column=5, sticky=(tk.W, tk.E), padx=5)

        # Location
        ttk.Label(frame, text="Location:").grid(row=1, column=0, sticky=tk.W, padx=5, pady=5)
        ttk.Entry(frame, textvariable=self.location_var, width=20).grid(row=1, column=1, sticky=(tk.W, tk.E), padx=5)

        # Car No.
        ttk.Label(frame, text="Car No.:").grid(row=1, column=2, sticky=tk.W, padx=5)
        ttk.Entry(frame, textvariable=self.car_no_var, width=15).grid(row=1, column=3, sticky=(tk.W, tk.E), padx=5)

        # Init.
        ttk.Label(frame, text="Init.:").grid(row=1, column=4, sticky=tk.W, padx=5)
        ttk.Entry(frame, textvariable=self.init_var, width=15).grid(row=1, column=5, sticky=(tk.W, tk.E), padx=5)

        # Faulty System
        ttk.Label(frame, text="Faulty System:").grid(row=2, column=0, sticky=tk.W, padx=5, pady=5)
        ttk.Entry(frame, textvariable=self.faulty_system_var, width=20).grid(row=2, column=1, sticky=(tk.W, tk.E), padx=5)

        # Service Impact
        ttk.Label(frame, text="Service Impact CO/PU/CL/WD:").grid(row=2, column=2, sticky=tk.W, padx=5)
        ttk.Entry(frame, textvariable=self.service_impact_var, width=15).grid(row=2, column=3, sticky=(tk.W, tk.E), padx=5)

        # Acc.
        ttk.Label(frame, text="Acc.:").grid(row=2, column=4, sticky=tk.W, padx=5)
        ttk.Entry(frame, textvariable=self.acc_var, width=15).grid(row=2, column=5, sticky=(tk.W, tk.E), padx=5)

    def create_system_info_frame(self):
        frame = ttk.LabelFrame(self.main_frame, text="System Information", padding="10")
        frame.grid(row=1, column=0, columnspan=2, sticky=(tk.W, tk.E), padx=10, pady=5)
        frame.grid_columnconfigure(1, weight=1)
        frame.grid_columnconfigure(3, weight=1)

        # System with autocomplete
        ttk.Label(frame, text="System:").grid(row=0, column=0, sticky=tk.W, padx=5, pady=5)
        self.system_entry = ttk.Entry(frame, textvariable=self.system_var, width=40)
        self.system_entry.grid(row=0, column=1, sticky=(tk.W, tk.E), padx=5)

        # System Listbox for suggestions (hidden by default)
        self.system_listbox = tk.Listbox(frame, height=5)
        self.system_listbox.grid(row=1, column=1, sticky=(tk.W, tk.E), padx=5)
        self.system_listbox.grid_remove()

        # Component/Symptom with dropdown
        ttk.Label(frame, text="Component/Symptom:").grid(row=0, column=2, sticky=tk.W, padx=5)
        self.component_combobox = ttk.Combobox(frame, textvariable=self.component_var, width=40)
        self.component_combobox.grid(row=0, column=3, sticky=(tk.W, tk.E), padx=5)

        # Fault Code with dropdown
        ttk.Label(frame, text="Fault Code:").grid(row=1, column=2, sticky=tk.W, padx=5, pady=5)
        self.fault_code_combobox = ttk.Combobox(frame, textvariable=self.fault_code_var, width=40)
        self.fault_code_combobox.grid(row=1, column=3, sticky=(tk.W, tk.E), padx=5)

    def create_work_info_frame(self):
        frame = ttk.LabelFrame(self.main_frame, text="Work Information", padding="10")
        frame.grid(row=2, column=0, columnspan=2, sticky=(tk.W, tk.E), padx=10, pady=5)
        frame.grid_columnconfigure(1, weight=1)
        frame.grid_columnconfigure(3, weight=1)
        frame.grid_columnconfigure(5, weight=1)
        frame.grid_columnconfigure(7, weight=1)

        # Start Work
        ttk.Label(frame, text="Start Work:").grid(row=0, column=0, sticky=tk.W, padx=5, pady=5)
        self.start_work_date = DateEntry(frame, width=15, background='darkblue',
                                       foreground='white', borderwidth=2,
                                       date_pattern='dd/mm/yyyy')
        self.start_work_date.grid(row=0, column=1, sticky=(tk.W), padx=5)
        
        ttk.Label(frame, text="Time:").grid(row=0, column=2, sticky=tk.W, padx=5)
        ttk.Entry(frame, textvariable=self.start_work_time_var, width=10).grid(row=0, column=3, sticky=(tk.W), padx=5)

        # End Work
        ttk.Label(frame, text="End Work:").grid(row=0, column=4, sticky=tk.W, padx=5)
        self.end_work_date = DateEntry(frame, width=15, background='darkblue',
                                     foreground='white', borderwidth=2,
                                     date_pattern='dd/mm/yyyy')
        self.end_work_date.grid(row=0, column=5, sticky=(tk.W), padx=5)
        
        ttk.Label(frame, text="Time:").grid(row=0, column=6, sticky=tk.W, padx=5)
        ttk.Entry(frame, textvariable=self.end_work_time_var, width=10).grid(row=0, column=7, sticky=(tk.W), padx=5)

        # Related Contract
        ttk.Label(frame, text="Related Contract:").grid(row=1, column=0, sticky=tk.W, padx=5, pady=5)
        ttk.Entry(frame, textvariable=self.related_contract_var, width=30).grid(row=1, column=1, columnspan=3, sticky=(tk.W, tk.E), padx=5)

        # Contact Person
        ttk.Label(frame, text="Contact Person:").grid(row=1, column=4, sticky=tk.W, padx=5)
        ttk.Entry(frame, textvariable=self.contact_person_var, width=30).grid(row=1, column=5, columnspan=3, sticky=(tk.W, tk.E), padx=5)

    def create_description_frame(self):
        frame = ttk.LabelFrame(self.main_frame, text="Description Information", padding="10")
        frame.grid(row=3, column=0, columnspan=2, sticky=(tk.W, tk.E), padx=10, pady=5)
        frame.grid_columnconfigure(1, weight=1)

        # Description
        ttk.Label(frame, text="Description:").grid(row=0, column=0, sticky=tk.W, padx=5, pady=5)
        self.desc_text = scrolledtext.ScrolledText(frame, width=100, height=8)
        self.desc_text.grid(row=0, column=1, sticky=(tk.W, tk.E), padx=5)

        # Finding
        ttk.Label(frame, text="Finding:").grid(row=1, column=0, sticky=tk.W, padx=5, pady=5)
        self.finding_text = scrolledtext.ScrolledText(frame, width=100, height=8)
        self.finding_text.grid(row=1, column=1, sticky=(tk.W, tk.E), padx=5)
        
        
    def create_equipment_frame(self):
        frame = ttk.LabelFrame(self.main_frame, text="Equipment Information", padding="10")
        frame.grid(row=4, column=0, columnspan=2, sticky=(tk.W, tk.E), padx=10, pady=5)
        frame.grid_columnconfigure(1, weight=1)
        frame.grid_columnconfigure(3, weight=1)
        frame.grid_columnconfigure(5, weight=1)

        # Replaced Equipment
        ttk.Label(frame, text="Replaced Equipment:").grid(row=0, column=0, sticky=tk.W, padx=5, pady=5)
        ttk.Entry(frame, textvariable=self.replaced_equipment_var, width=40).grid(row=0, column=1, sticky=(tk.W, tk.E), padx=5)

        # Out S/N
        ttk.Label(frame, text="Out S/N:").grid(row=0, column=2, sticky=tk.W, padx=5)
        ttk.Entry(frame, textvariable=self.out_sn_var, width=30).grid(row=0, column=3, sticky=(tk.W, tk.E), padx=5)

        # In S/N
        ttk.Label(frame, text="In S/N:").grid(row=0, column=4, sticky=tk.W, padx=5)
        ttk.Entry(frame, textvariable=self.in_sn_var, width=30).grid(row=0, column=5, sticky=(tk.W, tk.E), padx=5)

    def create_status_frame(self):
        frame = ttk.LabelFrame(self.main_frame, text="Status Information", padding="10")
        frame.grid(row=5, column=0, columnspan=2, sticky=(tk.W, tk.E), padx=10, pady=5)
        frame.grid_columnconfigure(1, weight=1)
        frame.grid_columnconfigure(3, weight=1)
        frame.grid_columnconfigure(5, weight=1)

        # Status
        ttk.Label(frame, text="Status:").grid(row=0, column=0, sticky=tk.W, padx=5, pady=5)
        status_choices = ['Open', 'Closed', 'System', 'Cancel']
        self.status_combobox = ttk.Combobox(frame, textvariable=self.status_var,
                                           values=status_choices, width=15)
        self.status_combobox.grid(row=0, column=1, sticky=(tk.W), padx=5)

        # Target Completion Date (changed to Entry)
        ttk.Label(frame, text="Target Completion Date:").grid(row=0, column=2, sticky=tk.W, padx=5)
        ttk.Entry(frame, textvariable=self.target_completion_var, width=15).grid(row=0, column=3, sticky=(tk.W), padx=5)

        # Systematic Failure
        ttk.Label(frame, text="Cat. Of Systematic Failure:").grid(row=1, column=0, sticky=tk.W, padx=5, pady=5)
        ttk.Entry(frame, textvariable=self.systematic_failure_var, width=20).grid(row=1, column=1, sticky=(tk.W, tk.E), padx=5)

        # Pending for Spare
        ttk.Label(frame, text="Pending for Spare:").grid(row=1, column=2, sticky=tk.W, padx=5)
        ttk.Entry(frame, textvariable=self.pending_spare_var, width=20).grid(row=1, column=3, sticky=(tk.W, tk.E), padx=5)

        # Consequence Code
        ttk.Label(frame, text="Consequence Code:").grid(row=2, column=0, sticky=tk.W, padx=5, pady=5)
        ttk.Entry(frame, textvariable=self.consequence_code_var, width=20).grid(row=2, column=1, sticky=(tk.W, tk.E), padx=5)

        # Checkboxes moved to bottom right
        ttk.Label(frame, text="Stop Car List:").grid(row=2, column=2, sticky=tk.W, padx=5)
        ttk.Checkbutton(frame, variable=self.stop_car_var).grid(row=2, column=3, sticky=tk.W, padx=5)

        ttk.Label(frame, text="Passenger Complaint:").grid(row=3, column=2, sticky=tk.W, padx=5)
        ttk.Checkbutton(frame, variable=self.passenger_complaint_var).grid(row=3, column=3, sticky=tk.W, padx=5)

    def create_buttons(self):
        button_frame = ttk.Frame(self.main_frame)
        button_frame.grid(row=6, column=0, columnspan=2, sticky=(tk.W, tk.E), padx=10, pady=10)
        button_frame.grid_columnconfigure(1, weight=1)

        # Search box
        ttk.Label(button_frame, text="Search:").grid(row=0, column=0, sticky=tk.W, padx=5)
        self.search_entry = ttk.Entry(button_frame, textvariable=self.search_var, width=40)
        self.search_entry.grid(row=0, column=1, sticky=(tk.W, tk.E), padx=5)

        # Buttons
        ttk.Button(button_frame, text="Save", command=self.save_record, width=15).grid(row=0, column=2, padx=5)
        ttk.Button(button_frame, text="Clear", command=self.clear_fields, width=15).grid(row=0, column=3, padx=5)
        ttk.Button(button_frame, text="Delete", command=self.delete_record, width=15).grid(row=0, column=4, padx=5)
        ttk.Button(button_frame, text="Records", command=self.show_records, width=15).grid(row=0, column=5, padx=5)

    def bind_events(self):
        # System-related bindings
        self.system_entry.bind('<KeyRelease>', self.update_system_suggestions)
        self.system_listbox.bind('<<ListboxSelect>>', self.select_system)
        self.system_var.trace('w', lambda *args: self.after_system_change())

        # Search binding
        self.search_var.trace('w', self.search_records)

    def update_system_suggestions(self, event=None):
        """Update system suggestions in the listbox based on current input"""
        search_term = self.system_var.get().lower()
        if search_term:
            suggestions = [system for system in self.all_system_options 
                         if search_term in system.lower()]
            
            self.system_listbox.delete(0, tk.END)
            for item in suggestions:
                self.system_listbox.insert(tk.END, item)
            
            if suggestions:
                self.system_listbox.grid()
            else:
                self.system_listbox.grid_remove()
        else:
            self.system_listbox.grid_remove()

    def select_system(self, event=None):
        """Handle system selection from the suggestion listbox"""
        if self.system_listbox.curselection():
            selected = self.system_listbox.get(self.system_listbox.curselection())
            self.system_var.set(selected)
            self.system_listbox.grid_remove()
            self.update_component_options(selected)

    def after_system_change(self, *args):
        """Handle system changes and update related fields"""
        selected_system = self.system_var.get()
        if selected_system in self.all_system_options:
            self.update_component_options(selected_system)

    def update_component_options(self, selected_system):
        """Update component and fault code options based on selected system"""
        system_df = self.df[self.df['System'] == selected_system]
        
        components = system_df['Component/Symptom'].unique()
        valid_components = sorted([comp for comp in components 
                                if isinstance(comp, str) and comp.strip()])
        self.component_combobox['values'] = valid_components
        
        fault_codes = system_df['Fault Code'].unique()
        valid_fault_codes = sorted([code for code in fault_codes 
                                  if isinstance(code, str) and code.strip()])
        self.fault_code_combobox['values'] = valid_fault_codes
        
    def show_records(self):
        """Show records in a separate window"""
        if self.record_window is None or not tk.Toplevel.winfo_exists(self.record_window):
            self.record_window = tk.Toplevel(self.root)
            self.record_window.title("Records")
            
            # Set window size to 80% of screen size
            screen_width = self.record_window.winfo_screenwidth()
            screen_height = self.record_window.winfo_screenheight()
            window_width = int(screen_width * 0.8)
            window_height = int(screen_height * 0.8)
            center_x = int(screen_width/2 - window_width/2)
            center_y = int(screen_height/2 - window_height/2)
            self.record_window.geometry(f'{window_width}x{window_height}+{center_x}+{center_y}')

            # Create Treeview in the new window with improved scrolling
            self.create_treeview(self.record_window)
            self.refresh_treeview()
            
            # Add window close handler
            self.record_window.protocol("WM_DELETE_WINDOW", self.on_record_window_close)
        else:
            self.record_window.lift()

    def create_treeview(self, parent):
        # Create frame for treeview and scrollbars
        container = ttk.Frame(parent)
        container.pack(fill=tk.BOTH, expand=True, padx=5, pady=5)

        # Create canvas and scrollbar for horizontal scrolling
        canvas = tk.Canvas(container)
        scrollbar_y = ttk.Scrollbar(container, orient="vertical", command=canvas.yview)
        scrollbar_x = ttk.Scrollbar(container, orient="horizontal", command=canvas.xview)

        # Create frame inside canvas for treeview
        tree_frame = ttk.Frame(canvas)
        
        # Create Treeview
        self.tree = ttk.Treeview(tree_frame, columns=self.required_columns,
                                show='headings', height=20)

        # Set column widths and headers
        for col in self.required_columns:
            if col in ['SKIP1', 'SKIP2']:  # Hide skip columns
                self.tree.column(col, width=0, stretch=False)
            else:
                self.tree.heading(col, text=col,
                                command=lambda c=col: self.sort_treeview(c))
                # Set specific widths for certain columns
                if col in ['Report Date', 'Report Time']:
                    self.tree.column(col, width=100)
                elif col in ['Description', 'Finding']:
                    self.tree.column(col, width=200)
                else:
                    self.tree.column(col, width=120)

        # Configure scrollbars
        self.tree.pack(side=tk.LEFT, fill=tk.BOTH, expand=True)
        
        # Add tree to canvas
        canvas.create_window((0, 0), window=tree_frame, anchor="nw")

        # Configure canvas scrolling
        def on_configure(event):
            canvas.configure(scrollregion=canvas.bbox("all"))
            canvas.itemconfig(canvas.find_all()[0], width=event.width)

        tree_frame.bind("<Configure>", on_configure)
        canvas.configure(yscrollcommand=scrollbar_y.set,
                        xscrollcommand=scrollbar_x.set)

        # Pack everything
        scrollbar_y.pack(side=tk.RIGHT, fill=tk.Y)
        scrollbar_x.pack(side=tk.BOTTOM, fill=tk.X)
        canvas.pack(side=tk.LEFT, fill=tk.BOTH, expand=True)

        # Bind mousewheel
        def on_mousewheel(event):
            if event.state == 0:  # No modifier keys
                canvas.yview_scroll(int(-1*(event.delta/120)), "units")
            elif event.state & 0x1:  # Shift is pressed
                canvas.xview_scroll(int(-1*(event.delta/120)), "units")

        canvas.bind_all("<MouseWheel>", on_mousewheel)
        canvas.bind_all("<Shift-MouseWheel>", on_mousewheel)

        # Bind selection event
        self.tree.bind('<<TreeviewSelect>>', self.item_selected)

    def refresh_treeview(self):
        """Refresh the treeview with current data"""
        if self.tree:
            # Clear existing items
            for item in self.tree.get_children():
                self.tree.delete(item)
                
            # Insert all records
            for index, row in self.df.iterrows():
                values = list(row)
                # Format dates before display
                if values[2]:  # Report Date
                    try:
                        date_obj = pd.to_datetime(values[2])
                        values[2] = date_obj.strftime('%d/%m/%Y')
                    except:
                        pass
                        
                if values[14]:  # Start Work
                    try:
                        datetime_obj = pd.to_datetime(values[14])
                        values[14] = datetime_obj.strftime('%d/%m/%Y %H:%M:%S')
                    except:
                        pass
                        
                if values[15]:  # End Work
                    try:
                        datetime_obj = pd.to_datetime(values[15])
                        values[15] = datetime_obj.strftime('%d/%m/%Y %H:%M:%S')
                    except:
                        pass
                
                self.tree.insert('', tk.END, values=values)

    def sort_treeview(self, col):
        """Sort treeview by column"""
        if not self.tree:
            return
            
        # Get all items in the treeview
        data = [(self.tree.set(item, col), item) for item in self.tree.get_children('')]
        
        # Sort items
        data.sort(key=lambda x: (x[0] == '', x[0]))  # Empty strings at the end
        
        # Rearrange items in sorted positions
        for index, (val, item) in enumerate(data):
            self.tree.move(item, '', index)

    def item_selected(self, event):
        """Handle the selection of an item in the treeview"""
        selected_items = self.tree.selection()
        if selected_items:
            item = selected_items[0]
            values = self.tree.item(item)['values']
            self.populate_fields(values)

    def on_record_window_close(self):
        """Handle record window closing"""
        self.tree = None
        self.record_window.destroy()
        self.record_window = None
        
    def save_record(self):
        try:
            # Load workbook and maintain formatting
            wb = openpyxl.load_workbook(self.excel_file)
            ws = wb[self.sheet_name]

            # Get next item number
            next_item = int(pd.to_numeric(self.df['Item'], errors='coerce').max()) + 1 if not self.df.empty else 1

            # Format dates
            report_date = self.report_date.get_date().strftime('%d/%m/%Y')
            start_work = f"{self.start_work_date.get_date().strftime('%d/%m/%Y')} {self.start_work_time_var.get()}"
            end_work = f"{self.end_work_date.get_date().strftime('%d/%m/%Y')} {self.end_work_time_var.get()}"
            target_date = self.target_completion_var.get()  # Use the value directly from Entry

            values = {
                'Item': next_item,
                'Work Order': self.work_order_var.get(),
                'Report Date': report_date,
                'Report Time': self.time_var.get(),
                'Location': self.location_var.get(),
                'Car no.': self.car_no_var.get(),
                'Faulty System': self.faulty_system_var.get(),
                'Service Impact CO/PU/CL/WD': self.service_impact_var.get(),
                'Init.': self.init_var.get(),
                'Acc.': self.acc_var.get(),
                'Desc': self.desc_text.get('1.0', tk.END).strip(),
                'System': self.system_var.get(),
                'Component/Symptom': self.component_var.get(),
                'Fault Code': self.fault_code_var.get(),
                'Start Work': start_work,
                'End Work': end_work,
                'Related Contract': self.related_contract_var.get(),
                'Contact Person': self.contact_person_var.get(),
                'Finding': self.finding_text.get('1.0', tk.END).strip(),
                'SKIP1': '',  # Skip columns
                'SKIP2': '',  # Skip columns
                'Replaced Equipment': self.replaced_equipment_var.get(),
                'Out S/N': self.out_sn_var.get(),
                'In S/N': self.in_sn_var.get(),
                'Status': self.status_var.get(),
                'Target Completion Date': target_date,
                'Cat. Of Systematic Failure': self.systematic_failure_var.get(),
                'Pending for Spare': self.pending_spare_var.get(),
                'Consequence Code': self.consequence_code_var.get(),
                'Reporting in Stop Car List': 'Yes' if self.stop_car_var.get() else '',
                'Passenger complaint': 'Yes' if self.passenger_complaint_var.get() else ''
            }

            # Validate required fields
            required_fields = ['Work Order', 'Report Date', 'Report Time']
            for field in required_fields:
                if not values[field]:
                    messagebox.showerror("Error", f"{field} is required!")
                    return

            try:
                # Check if treeview exists and has selection
                selected_items = []
                if self.tree and self.tree.winfo_exists():
                    selected_items = self.tree.selection()
            except (tk.TclError, AttributeError):
                selected_items = []

            if selected_items:
                # Update existing record
                item_id = selected_items[0]
                item_values = self.tree.item(item_id)['values']
                matching_indices = self.df.index[self.df['Work Order'] == item_values[1]]
                if len(matching_indices) > 0:
                    index = matching_indices[0]
                    self.df.loc[index] = values
                    row_num = index + 2  # +2 because Excel is 1-based and has header
                else:
                    self.df = pd.concat([self.df, pd.DataFrame([values])], ignore_index=True)
                    row_num = len(self.df) + 1
            else:
                # Add new record
                self.df = pd.concat([self.df, pd.DataFrame([values])], ignore_index=True)
                row_num = len(self.df) + 1

            # Update specific row in Excel while maintaining formatting
            for col_num, (column, value) in enumerate(values.items(), start=1):
                cell = ws.cell(row=row_num, column=col_num)
                cell.value = value
                
                # Copy format from previous row if exists
                if row_num > 2:
                    prev_cell = ws.cell(row=row_num-1, column=col_num)
                    if prev_cell.has_style:
                        cell._style = prev_cell._style

            # Save workbook
            wb.save(self.excel_file)

            # Refresh system options and treeview
            self.refresh_system_options()
            if self.tree and self.tree.winfo_exists():
                self.refresh_treeview()

            # Clear the form fields
            self.clear_fields()
            messagebox.showinfo("Success", "Record saved successfully!")

        except Exception as e:
            messagebox.showerror("Error", f"Error saving record: {str(e)}")
            raise

    def delete_record(self):
        if not self.tree:
            messagebox.showwarning("Warning", "Please open the Records window first.")
            return

        selected_items = self.tree.selection()
        if not selected_items:
            messagebox.showwarning("Warning", "Please select a record to delete.")
            return

        if messagebox.askyesno("Confirm Delete", "Are you sure you want to delete the selected record?"):
            item = selected_items[0]
            values = self.tree.item(item)['values']

            # Find and delete the record from the dataframe
            self.df = self.df[self.df['Work Order'] != values[1]]

            try:
                # Save the updated dataframe to Excel
                with pd.ExcelWriter(self.excel_file, engine='openpyxl', mode='a', 
                                  if_sheet_exists='replace') as writer:
                    self.df.to_excel(writer, sheet_name=self.sheet_name, index=False)
                
                # Remove from treeview
                self.tree.delete(item)
                # Clear the form fields
                self.clear_fields()
                # Refresh system options
                self.refresh_system_options()
                
                messagebox.showinfo("Success", "Record deleted successfully!")
            except Exception as e:
                messagebox.showerror("Error", f"Error deleting record: {str(e)}")

    def clear_fields(self):
        # Clear all entry fields and text boxes
        self.work_order_var.set('')
        self.date_var.set(datetime.now().strftime('%d/%m/%Y'))
        self.time_var.set(datetime.now().strftime('%H:%M'))
        self.location_var.set('WCD')  # Set default value
        self.car_no_var.set('')
        self.faulty_system_var.set('')
        self.service_impact_var.set('')
        self.init_var.set('')
        self.acc_var.set('')
        self.system_var.set('')
        self.component_var.set('')
        self.fault_code_var.set('')
        self.start_work_time_var.set('')
        self.end_work_time_var.set('')
        self.related_contract_var.set('')
        self.contact_person_var.set('')
        self.replaced_equipment_var.set('')
        self.out_sn_var.set('')
        self.in_sn_var.set('')
        self.status_var.set('')
        self.target_completion_var.set('')  # Just clear the Entry value
        self.systematic_failure_var.set('')
        self.pending_spare_var.set('')
        self.consequence_code_var.set('')
        self.stop_car_var.set(False)
        self.passenger_complaint_var.set(False)

        # Clear text widgets
        self.desc_text.delete('1.0', tk.END)
        self.finding_text.delete('1.0', tk.END)

        # Reset dates to current date
        current_date = datetime.now().date()
        self.report_date.set_date(current_date)
        self.start_work_date.set_date(current_date)
        self.end_work_date.set_date(current_date)

    def search_records(self, *args):
        if not self.tree:
            return
            
        search_term = self.search_var.get().lower()
        # Clear existing items
        for item in self.tree.get_children():
            self.tree.delete(item)

        # Filter and insert matching records
        for index, row in self.df.iterrows():
            if any(str(value).lower().find(search_term) >= 0 for value in row):
                values = list(row)
                # Format dates before display
                if values[2]:  # Report Date
                    try:
                        date_obj = pd.to_datetime(values[2], format='%d/%m/%Y', dayfirst=True)
                        values[2] = date_obj.strftime('%d/%m/%Y')
                    except:
                        pass
                if values[14]:  # Start Work
                    try:
                        datetime_obj = pd.to_datetime(values[14], format='%d/%m/%Y %H:%M:%S', dayfirst=True)
                        values[14] = datetime_obj.strftime('%d/%m/%Y %H:%M:%S')
                    except:
                        pass
                if values[15]:  # End Work
                    try:
                        datetime_obj = pd.to_datetime(values[15], format='%d/%m/%Y %H:%M:%S', dayfirst=True)
                        values[15] = datetime_obj.strftime('%d/%m/%Y %H:%M:%S')
                    except:
                        pass
                self.tree.insert('', tk.END, values=values)

    def refresh_system_options(self):
        """Refresh the system options from the current DataFrame"""
        self.all_system_options = self.load_system_options(self.df)

    def populate_fields(self, values):
        """Populate form fields with the selected record's values"""
        try:
            self.work_order_var.set(values[1] if len(values) > 1 else '')

            # Handle Report Date
            try:
                if len(values) > 2 and values[2]:
                    date_obj = pd.to_datetime(values[2], format='%d/%m/%Y', dayfirst=True)
                    self.report_date.set_date(date_obj.date())
            except:
                self.report_date.set_date(datetime.now().date())

            self.time_var.set(values[3] if len(values) > 3 else '')
            self.location_var.set(values[4] if len(values) > 4 else 'WCD')
            self.car_no_var.set(values[5] if len(values) > 5 else '')
            self.faulty_system_var.set(values[6] if len(values) > 6 else '')
            self.service_impact_var.set(values[7] if len(values) > 7 else '')
            self.init_var.set(values[8] if len(values) > 8 else '')
            self.acc_var.set(values[9] if len(values) > 9 else '')

            # Handle text widgets
            self.desc_text.delete('1.0', tk.END)
            self.desc_text.insert('1.0', values[10] if len(values) > 10 and values[10] else '')

            self.system_var.set(values[11] if len(values) > 11 else '')
            self.component_var.set(values[12] if len(values) > 12 else '')
            self.fault_code_var.set(values[13] if len(values) > 13 else '')

            # Handle Start Work date/time
            if len(values) > 14 and values[14]:
                try:
                    datetime_obj = pd.to_datetime(values[14])
                    self.start_work_date.set_date(datetime_obj.date())
                    self.start_work_time_var.set(datetime_obj.strftime('%H:%M:%S'))
                except:
                    self.start_work_date.set_date(datetime.now().date())
                    self.start_work_time_var.set('')

            # Handle End Work date/time
            if len(values) > 15 and values[15]:
                try:
                    datetime_obj = pd.to_datetime(values[15])
                    self.end_work_date.set_date(datetime_obj.date())
                    self.end_work_time_var.set(datetime_obj.strftime('%H:%M:%S'))
                except:
                    self.end_work_date.set_date(datetime.now().date())
                    self.end_work_time_var.set('')

            self.related_contract_var.set(values[16] if len(values) > 16 else '')
            self.contact_person_var.set(values[17] if len(values) > 17 else '')
            
            self.finding_text.delete('1.0', tk.END)
            self.finding_text.insert('1.0', values[18] if len(values) > 18 and values[18] else '')

            # Skip SKIP1 and SKIP2 columns (index 19 and 20)
            self.replaced_equipment_var.set(values[21] if len(values) > 21 else '')
            self.out_sn_var.set(values[22] if len(values) > 22 else '')
            self.in_sn_var.set(values[23] if len(values) > 23 else '')
            self.status_var.set(values[24] if len(values) > 24 else '')

            # Handle Target Completion Date (changed to direct string assignment)
            if len(values) > 25:
                self.target_completion_var.set(values[25] if values[25] else '')

            self.systematic_failure_var.set(values[26] if len(values) > 26 else '')
            self.pending_spare_var.set(values[27] if len(values) > 27 else '')
            self.consequence_code_var.set(values[28] if len(values) > 28 else '')
            self.stop_car_var.set(str(values[29]).strip().upper() == 'YES' if len(values) > 29 else False)
            self.passenger_complaint_var.set(str(values[30]).strip().upper() == 'YES' if len(values) > 30 else False)

        except Exception as e:
            print(f"Error in populate_fields: {str(e)}")
            self.clear_fields()

if __name__ == "__main__":
    root = tk.Tk()
    app = WorkOrderSystem(root)
    root.mainloop()