#!/usr/bin/env python3
"""
Enhanced GUI for PHP migration tracking system.
Database-driven interface with advanced filtering and bulk operations.
"""

import tkinter as tk
from tkinter import ttk, messagebox, filedialog, simpledialog
import subprocess
import sys
import os
import json
from typing import List, Dict, Optional
from db_utils import MigrationDB

# OpenAI integration (optional)
try:
    import openai
    OPENAI_AVAILABLE = True
except ImportError:
    OPENAI_AVAILABLE = False

# Global log text widget
log_text = None

def add_log(message):
    """Add a log message to the log window"""
    global log_text
    if log_text:
        log_text.insert(tk.END, f"{message}\n")
        log_text.see(tk.END)  # Scroll to the end

class MigrationGUI:
    """Main GUI application for migration tracking."""
    
    def __init__(self, root):
        self.root = root
        self.db = MigrationDB()
        self.current_files = []
        self.current_index = 0
        self.filter_status = 'all'
        self.filter_flags = {'php8': 'all', 'tested': 'all', 'links': 'all'}
        self.quick_mode = tk.BooleanVar(value=False)
        self.logs_visible = tk.BooleanVar(value=False)

        # Sorting state
        self.sort_column = 'Path'
        self.sort_reverse = False
        self.deps_sort_column = 'Path'
        self.deps_sort_reverse = False

        # OpenAI API key
        self.openai_api_key = self.load_openai_api_key()

        # UI variables for file details
        self.php8_var = tk.BooleanVar()
        self.tested_var = tk.BooleanVar()
        self.links_var = tk.BooleanVar()
        self.isLive_var = tk.BooleanVar()
        self.current_file_var = tk.StringVar(value="No file selected")
        self.active_file_var = tk.StringVar(value="No file selected")
        self.description_var = tk.StringVar()

        self.setup_ui()
        self.refresh_file_list()
    
    def setup_ui(self):
        """Setup the user interface."""
        self.root.title("PHP Migration Tracking System")
        self.root.geometry("1400x900")
        
        # Setup menu bar
        self.setup_menu_bar()
        
        # Create main frame
        main_frame = ttk.Frame(self.root, padding="10")
        main_frame.grid(row=0, column=0, sticky=(tk.W, tk.E, tk.N, tk.S))
        
        # Configure grid weights
        self.root.columnconfigure(0, weight=1)
        self.root.rowconfigure(0, weight=1)
        main_frame.columnconfigure(0, weight=1)
        main_frame.rowconfigure(1, weight=1)
        main_frame.rowconfigure(3, weight=1)
        
        # Filter frame (moved to top)
        self.setup_filter_frame(main_frame)

        # File list frame (full width)
        self.setup_file_list_frame(main_frame)

        # File details frame (full width below file list)
        self.setup_file_details_frame(main_frame)

        # Simplified action buttons frame
        self.setup_action_buttons_frame(main_frame)

        # Status bar
        self.status_var = tk.StringVar()
        self.status_bar = ttk.Label(main_frame, textvariable=self.status_var,
                                    relief=tk.SUNKEN, anchor=tk.W)
        self.status_bar.grid(row=4, column=0, sticky=(tk.W, tk.E), pady=(10, 0))
        
        self.update_status("Ready")
    
    def setup_menu_bar(self):
        """Setup menu bar with organized functions."""
        menubar = tk.Menu(self.root)
        self.root.config(menu=menubar)
        
        # File menu
        file_menu = tk.Menu(menubar, tearoff=0)
        menubar.add_cascade(label="File", menu=file_menu)
        file_menu.add_command(label="Open Selected File", command=self.open_current_file,
                             accelerator="Ctrl+O")
        file_menu.add_command(label="Check Compatibility", command=self.check_compatibility)
        file_menu.add_separator()
        file_menu.add_command(label="Exit", command=self.root.quit, accelerator="Ctrl+Q")
        
        # System menu
        system_menu = tk.Menu(menubar, tearoff=0)
        menubar.add_cascade(label="System", menu=system_menu)
        
        system_menu.add_command(label="Rescan Files", command=self.rescan_inventory)
        system_menu.add_command(label="Verify File Existence", command=self.verify_all_files)
        system_menu.add_command(label="Generate Missing Files List", command=self.generate_missing_files_list)
        system_menu.add_separator()
        system_menu.add_command(label="Scan All Links", command=self.scan_all_links)
        system_menu.add_command(label="Import Data from Markdown", command=self.import_data_dialog)
        system_menu.add_separator()
        system_menu.add_command(label="Create Backup", command=self.create_backup)
        system_menu.add_command(label="Database Statistics", command=self.show_statistics)
        
        # Reports menu
        reports_menu = tk.Menu(menubar, tearoff=0)
        menubar.add_cascade(label="Reports", menu=reports_menu)
        reports_menu.add_command(label="Generate Summary Report", 
                               command=lambda: self.generate_specific_report('summary'))
        reports_menu.add_command(label="Export to CSV", 
                               command=lambda: self.generate_specific_report('csv'))
        reports_menu.add_command(label="Dependencies Report", 
                               command=lambda: self.generate_specific_report('dependencies'))
        reports_menu.add_separator()
        reports_menu.add_command(label="Custom Report...", command=self.generate_report_dialog)
        
        # Tools menu
        tools_menu = tk.Menu(menubar, tearoff=0)
        menubar.add_cascade(label="Tools", menu=tools_menu)
        tools_menu.add_command(label="Bulk Update Files", command=self.bulk_update_dialog)
        tools_menu.add_command(label="Cleanup Duplicates", command=self.cleanup_duplicates)
        
        # Help menu
        help_menu = tk.Menu(menubar, tearoff=0)
        menubar.add_cascade(label="Help", menu=help_menu)
        help_menu.add_command(label="Quick Start Guide", command=self.show_help)
        help_menu.add_command(label="About", command=self.show_about)
        
        # Bind keyboard shortcuts
        self.root.bind('<Control-o>', lambda e: self.open_current_file())
        self.root.bind('<Control-q>', lambda e: self.root.quit())
    
    def setup_filter_frame(self, parent):
        """Setup filter controls."""
        filter_frame = ttk.LabelFrame(parent, text="Filters", padding="5")
        filter_frame.grid(row=0, column=0, sticky=(tk.W, tk.E), pady=(0, 10))
        
        # Status filter
        ttk.Label(filter_frame, text="Status:").grid(row=0, column=0, padx=(0, 5))
        self.status_combo = ttk.Combobox(filter_frame, values=['all', 'new', 'keep', 'archive', 'remove', 'Done', 'Key Page', 'Done Key Page'],
                                        state='readonly', width=10)
        self.status_combo.set('all')
        self.status_combo.grid(row=0, column=1, padx=(0, 10))
        self.status_combo.bind('<<ComboboxSelected>>', self.on_filter_change)
        
        # PHP8 filter
        ttk.Label(filter_frame, text="PHP8:").grid(row=0, column=2, padx=(0, 5))
        self.php8_combo = ttk.Combobox(filter_frame, values=['all', 'done', 'pending'], 
                                      state='readonly', width=10)
        self.php8_combo.set('all')
        self.php8_combo.grid(row=0, column=3, padx=(0, 10))
        self.php8_combo.bind('<<ComboboxSelected>>', self.on_filter_change)
        
        # Tested filter
        ttk.Label(filter_frame, text="Tested:").grid(row=0, column=4, padx=(0, 5))
        self.tested_combo = ttk.Combobox(filter_frame, values=['all', 'done', 'pending'], 
                                        state='readonly', width=10)
        self.tested_combo.set('all')
        self.tested_combo.grid(row=0, column=5, padx=(0, 10))
        self.tested_combo.bind('<<ComboboxSelected>>', self.on_filter_change)
        
        # Links filter
        ttk.Label(filter_frame, text="Links:").grid(row=0, column=6, padx=(0, 5))
        self.links_combo = ttk.Combobox(filter_frame, values=['all', 'done', 'pending'], 
                                       state='readonly', width=10)
        self.links_combo.set('all')
        self.links_combo.grid(row=0, column=7, padx=(0, 10))
        self.links_combo.bind('<<ComboboxSelected>>', self.on_filter_change)
        
        # File exists filter
        ttk.Label(filter_frame, text="Exists:").grid(row=0, column=8, padx=(0, 5))
        self.exists_combo = ttk.Combobox(filter_frame, values=['all', 'exists', 'missing'], 
                                        state='readonly', width=10)
        self.exists_combo.set('all')
        self.exists_combo.grid(row=0, column=9, padx=(0, 10))
        self.exists_combo.bind('<<ComboboxSelected>>', self.on_filter_change)
        
        # Search
        ttk.Label(filter_frame, text="Search:").grid(row=0, column=10, padx=(0, 5))
        self.search_var = tk.StringVar()
        self.search_entry = ttk.Entry(filter_frame, textvariable=self.search_var, width=15)
        self.search_entry.grid(row=0, column=11, padx=(0, 10))
        self.search_entry.bind('<KeyRelease>', self.on_search_change)
        
        # Clear filters button
        ttk.Button(filter_frame, text="Clear", command=self.clear_filters).grid(row=0, column=12, padx=(0, 20))

        # Quick Mode toggle (moved from title area)
        ttk.Label(filter_frame, text="Quick Mode:", font=("Arial", 10)).grid(row=0, column=13, padx=(0, 5))
        quick_mode_check = ttk.Checkbutton(filter_frame, variable=self.quick_mode,
                                          command=self.on_quick_mode_toggle)
        quick_mode_check.grid(row=0, column=14, padx=(0, 5))

        self.quick_mode_status = ttk.Label(filter_frame, text="OFF",
                                          font=("Arial", 9), foreground="red")
        self.quick_mode_status.grid(row=0, column=15, padx=(0, 10))
    
    def setup_file_list_frame(self, parent):
        """Setup file list with treeview."""
        list_frame = ttk.LabelFrame(parent, text="Files", padding="5")
        list_frame.grid(row=1, column=0, sticky=(tk.W, tk.E, tk.N, tk.S))
        list_frame.columnconfigure(0, weight=1)
        list_frame.rowconfigure(0, weight=1)
        
        # Treeview for file list
        columns = ('Checked', 'Path', 'Status', 'PHP8', 'Tested', 'Links', 'IsLive', 'Exists', 'Last Updated')
        self.file_tree = ttk.Treeview(list_frame, columns=columns, show='headings', height=18)

        # Configure columns
        self.file_tree.heading('Checked', text='✓')
        self.file_tree.heading('Path', text='File Path')
        self.file_tree.heading('Status', text='Status')
        self.file_tree.heading('PHP8', text='PHP8')
        self.file_tree.heading('Tested', text='Tested')
        self.file_tree.heading('Links', text='Links')
        self.file_tree.heading('IsLive', text='IsLive')
        self.file_tree.heading('Exists', text='File Exists')
        self.file_tree.heading('Last Updated', text='Last Updated')

        self.file_tree.column('Checked', width=40, anchor='center')
        self.file_tree.column('Path', width=400)
        self.file_tree.column('Status', width=80)
        self.file_tree.column('PHP8', width=60)
        self.file_tree.column('Tested', width=60)
        self.file_tree.column('Links', width=60)
        self.file_tree.column('IsLive', width=60)
        self.file_tree.column('Exists', width=80)
        self.file_tree.column('Last Updated', width=120)
        
        # Scrollbar for treeview
        tree_scroll = ttk.Scrollbar(list_frame, orient=tk.VERTICAL, command=self.file_tree.yview)
        self.file_tree.configure(yscrollcommand=tree_scroll.set)
        
        self.file_tree.grid(row=0, column=0, sticky=(tk.W, tk.E, tk.N, tk.S))
        tree_scroll.grid(row=0, column=1, sticky=(tk.N, tk.S))
        
        # Bind selection event
        self.file_tree.bind('<<TreeviewSelect>>', self.on_file_select)
        
        # Bind double-click for quick mode
        self.file_tree.bind('<Double-1>', self.on_tree_double_click)
        
        # Bind column header clicks for sorting (except checkbox column)
        for col in columns:
            if col == 'Checked':
                # Bind checkbox column to toggle functionality
                self.file_tree.heading(col, command=self.toggle_checkbox_column)
            else:
                self.file_tree.heading(col, command=lambda c=col: self.sort_file_list(c))
        
        # Context menu for file list
        self.setup_context_menu()
    
    def setup_file_details_frame(self, parent):
        """Setup file details panel with tabs."""
        details_frame = ttk.LabelFrame(parent, text="File Details", padding="5")
        details_frame.grid(row=2, column=0, sticky=(tk.W, tk.E, tk.N, tk.S))
        details_frame.columnconfigure(0, weight=1)
        details_frame.rowconfigure(0, weight=1)

        # Create notebook for tabs
        self.details_notebook = ttk.Notebook(details_frame)
        self.details_notebook.grid(row=0, column=0, sticky=(tk.W, tk.E, tk.N, tk.S))
        self.details_notebook.bind('<<NotebookTabChanged>>', self.on_tab_changed)

        # File Details Tab
        self.setup_file_info_tab()

        # Dependencies Tab
        self.setup_dependencies_tab()

        # File Search Tab
        self.setup_file_search_tab()
    
    def setup_file_info_tab(self):
        """Setup the file information tab."""
        info_frame = ttk.Frame(self.details_notebook, padding="5")
        self.details_notebook.add(info_frame, text="File Info")
        info_frame.columnconfigure(1, weight=1)

        # File path
        ttk.Label(info_frame, text="Path:").grid(row=0, column=0, sticky=tk.W, padx=(0, 10))
        self.path_var = tk.StringVar()
        ttk.Label(info_frame, textvariable=self.path_var, font=("Courier", 10)).grid(
            row=0, column=1, sticky=(tk.W, tk.E), pady=(0, 5))

        # Status and controls in one horizontal line
        controls_frame = ttk.Frame(info_frame)
        controls_frame.grid(row=1, column=0, columnspan=2, sticky=(tk.W, tk.E), pady=(0, 10))

        ttk.Label(controls_frame, text="Status:").pack(side=tk.LEFT, padx=(0, 5))
        self.status_detail_combo = ttk.Combobox(controls_frame, values=['new', 'keep', 'archive', 'remove', 'Done', 'Key Page', 'Done Key Page'],
                                               state='readonly', width=15)
        self.status_detail_combo.pack(side=tk.LEFT, padx=(0, 10))

        self.key_file_var = tk.BooleanVar()
        ttk.Checkbutton(controls_frame, text="Key File", variable=self.key_file_var).pack(side=tk.LEFT, padx=(0, 10))

        ttk.Button(controls_frame, text="Save Changes", command=self.save_current_file).pack(side=tk.LEFT)

        # Notes
        ttk.Label(info_frame, text="Notes:").grid(row=2, column=0, sticky=(tk.W, tk.N), padx=(0, 10))
        self.notes_text = tk.Text(info_frame, height=6, width=50)
        self.notes_text.grid(row=2, column=1, sticky=(tk.W, tk.E, tk.N, tk.S), pady=(0, 10))

        # Notes scrollbar
        notes_scroll = ttk.Scrollbar(info_frame, orient=tk.VERTICAL, command=self.notes_text.yview)
        self.notes_text.configure(yscrollcommand=notes_scroll.set)
        notes_scroll.grid(row=2, column=2, sticky=(tk.N, tk.S), pady=(0, 10))

        # Task Instructions
        ttk.Label(info_frame, text="Task Instructions:").grid(row=3, column=0, sticky=(tk.W, tk.N), padx=(0, 10))
        self.task_text = tk.Text(info_frame, height=4, width=50, state='normal')
        self.task_text.grid(row=3, column=1, sticky=(tk.W, tk.E, tk.N, tk.S), pady=(0, 10))

        # Task instructions scrollbar
        task_scroll = ttk.Scrollbar(info_frame, orient=tk.VERTICAL, command=self.task_text.yview)
        self.task_text.configure(yscrollcommand=task_scroll.set)
        task_scroll.grid(row=3, column=2, sticky=(tk.N, tk.S), pady=(0, 10))

        info_frame.rowconfigure(3, weight=1)
    
    def setup_dependencies_tab(self):
        """Setup the dependencies tab with live status display."""
        deps_frame = ttk.Frame(self.details_notebook, padding="5")
        self.details_notebook.add(deps_frame, text="Dependencies")
        deps_frame.columnconfigure(0, weight=1)
        deps_frame.rowconfigure(1, weight=1)

        # Dependency filters
        filter_frame = ttk.Frame(deps_frame)
        filter_frame.grid(row=0, column=0, sticky=(tk.W, tk.E), pady=(0, 10))

        ttk.Label(filter_frame, text="Show:").grid(row=0, column=0, padx=(0, 5))
        self.dep_filter_var = tk.StringVar(value="all")
        dep_filter_combo = ttk.Combobox(filter_frame, textvariable=self.dep_filter_var,
                                       values=['all', 'new', 'keep', 'archive', 'remove', 'incomplete'],
                                       state='readonly', width=12)
        dep_filter_combo.grid(row=0, column=1, padx=(0, 10))
        dep_filter_combo.bind('<<ComboboxSelected>>', self.on_dependency_filter_change)

        ttk.Button(filter_frame, text="Refresh", command=self.refresh_dependencies).grid(row=0, column=2)

        # Dependencies list - now using clickable labels instead of treeview
        # Create scrollable frame for dependencies
        canvas_frame = ttk.Frame(deps_frame)
        canvas_frame.grid(row=1, column=0, sticky=(tk.W, tk.E, tk.N, tk.S))

        canvas = tk.Canvas(canvas_frame)
        scrollbar = tk.Scrollbar(canvas_frame, orient="vertical", command=canvas.yview)
        scrollable_frame = tk.Frame(canvas)

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

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

        canvas.pack(side=tk.LEFT, fill=tk.BOTH, expand=True)
        scrollbar.pack(side=tk.RIGHT, fill=tk.Y)

        # Store reference to scrollable frame for dependency display
        self.deps_scrollable_frame = scrollable_frame

        # Dependency summary
        self.dep_summary_var = tk.StringVar()
        ttk.Label(deps_frame, textvariable=self.dep_summary_var, font=("Arial", 10)).grid(
            row=2, column=0, sticky=tk.W, pady=(5, 0))

    def setup_file_search_tab(self):
        """Setup the file search tab."""
        search_frame = ttk.Frame(self.details_notebook, padding="5")
        self.details_notebook.add(search_frame, text="File Search")
        search_frame.columnconfigure(0, weight=1)
        search_frame.rowconfigure(2, weight=1)

        # Buttons
        button_frame = ttk.Frame(search_frame)
        button_frame.grid(row=0, column=0, pady=(0, 10))
        ttk.Button(button_frame, text="Quick Search", command=self.perform_quick_search).grid(row=0, column=0, padx=(0, 10))
        ttk.Button(button_frame, text="Deep Search", command=self.perform_deep_search).grid(row=0, column=1)

        # Results
        ttk.Label(search_frame, text="Files containing references to the active file:").grid(row=1, column=0, sticky=(tk.W, tk.N))
        self.search_results_text = tk.Text(search_frame, height=10, width=50, state='normal')
        self.search_results_text.grid(row=2, column=0, sticky=(tk.W, tk.E, tk.N, tk.S), pady=(5, 10))

        # Scrollbar
        search_scroll = ttk.Scrollbar(search_frame, orient=tk.VERTICAL, command=self.search_results_text.yview)
        self.search_results_text.configure(yscrollcommand=search_scroll.set)
        search_scroll.grid(row=2, column=1, sticky=(tk.N, tk.S), pady=(5, 10))

    def setup_action_buttons_frame(self, parent):
        """Setup simplified action buttons."""
        action_frame = ttk.Frame(parent)
        action_frame.grid(row=3, column=0, pady=(10, 0))
        
        # Essential file operations only
        ttk.Button(action_frame, text="Open File", command=self.open_current_file).grid(
            row=0, column=0, padx=(0, 10))
        ttk.Button(action_frame, text="Check Compatibility", command=self.check_compatibility).grid(
            row=0, column=1, padx=(0, 10))
        
        # GPT button (conditional on OpenAI availability and API key)
        if OPENAI_AVAILABLE and self.openai_api_key:
            ttk.Button(action_frame, text="GPT Check", command=self.gpt_compatibility_check,
                      style="Accent.TButton").grid(row=0, column=2, padx=(0, 10))
            gpt_col_offset = 1
        else:
            gpt_col_offset = 0
        
        ttk.Button(action_frame, text="Scan Links", command=self.scan_current_file_links).grid(
            row=0, column=2+gpt_col_offset, padx=(0, 10))
        ttk.Button(action_frame, text="Verify IsLive", command=self.verify_islive_status).grid(
            row=0, column=3+gpt_col_offset, padx=(0, 20))

        # Quick access to most used functions
        ttk.Button(action_frame, text="Bulk Update", command=self.bulk_update_dialog).grid(
            row=0, column=4+gpt_col_offset, padx=(0, 10))
        ttk.Button(action_frame, text="Tag Done", command=self.tag_done).grid(
            row=0, column=5+gpt_col_offset, padx=(0, 10))
        ttk.Button(action_frame, text="Rescan Files", command=self.rescan_inventory).grid(
            row=0, column=6+gpt_col_offset, padx=(0, 10))
        
        # Note about menu and API key setup
        if not OPENAI_AVAILABLE:
            note_text = "Install 'openai' package for GPT compatibility checking | More functions in System menu"
        elif not self.openai_api_key:
            note_text = "Set OpenAI API key in Tools menu for GPT checking | More functions in System menu"
        else:
            note_text = "More functions available in System menu"

        ttk.Label(action_frame, text=note_text,
                  font=("Arial", 9), foreground="gray").grid(row=0, column=6+gpt_col_offset, padx=(20, 0))
    
    def setup_menu_bar(self):
        """Setup menu bar with organized functions."""
        menubar = tk.Menu(self.root)
        self.root.config(menu=menubar)
        
        # File menu
        file_menu = tk.Menu(menubar, tearoff=0)
        menubar.add_cascade(label="File", menu=file_menu)
        file_menu.add_command(label="Open Selected File", command=self.open_current_file,
                             accelerator="Ctrl+O")
        file_menu.add_command(label="Check Compatibility", command=self.check_compatibility)
        file_menu.add_separator()
        file_menu.add_command(label="Exit", command=self.root.quit, accelerator="Ctrl+Q")
        
        # System menu
        system_menu = tk.Menu(menubar, tearoff=0)
        menubar.add_cascade(label="System", menu=system_menu)
        
        system_menu.add_command(label="Rescan Files", command=self.rescan_inventory)
        system_menu.add_command(label="Verify File Existence", command=self.verify_all_files)
        system_menu.add_command(label="Generate Missing Files List", command=self.generate_missing_files_list)
        system_menu.add_separator()
        system_menu.add_command(label="Scan All Links", command=self.scan_all_links)
        system_menu.add_command(label="Import Data from Markdown", command=self.import_data_dialog)
        system_menu.add_separator()
        system_menu.add_command(label="Create Backup", command=self.create_backup)
        system_menu.add_command(label="Database Statistics", command=self.show_statistics)
        
        # Reports menu
        reports_menu = tk.Menu(menubar, tearoff=0)
        menubar.add_cascade(label="Reports", menu=reports_menu)
        reports_menu.add_command(label="Generate Summary Report", 
                               command=lambda: self.generate_specific_report('summary'))
        reports_menu.add_command(label="Export to CSV", 
                               command=lambda: self.generate_specific_report('csv'))
        reports_menu.add_command(label="Dependencies Report", 
                               command=lambda: self.generate_specific_report('dependencies'))
        reports_menu.add_separator()
        reports_menu.add_command(label="Custom Report...", command=self.generate_report_dialog)
        
        # Tools menu
        tools_menu = tk.Menu(menubar, tearoff=0)
        menubar.add_cascade(label="Tools", menu=tools_menu)
        tools_menu.add_command(label="Bulk Update Files", command=self.bulk_update_dialog)
        tools_menu.add_command(label="Cleanup Duplicates", command=self.cleanup_duplicates)
        tools_menu.add_separator()
        tools_menu.add_command(label="Verify IsLive Status", command=self.verify_islive_status)
        tools_menu.add_separator()
        tools_menu.add_command(label="Configure OpenAI API Key", command=self.configure_openai_api_key)
        tools_menu.add_separator()
        tools_menu.add_command(label="Toggle Application Logs", command=self.toggle_logs)
        
        # Help menu
        help_menu = tk.Menu(menubar, tearoff=0)
        menubar.add_cascade(label="Help", menu=help_menu)
        help_menu.add_command(label="Quick Start Guide", command=self.show_help)
        help_menu.add_command(label="About", command=self.show_about)
        
        # Bind keyboard shortcuts
        self.root.bind('<Control-o>', lambda e: self.open_current_file())
        self.root.bind('<Control-q>', lambda e: self.root.quit())
    
    def setup_context_menu(self):
        """Setup context menu for file list."""
        self.context_menu = tk.Menu(self.root, tearoff=0)
        self.context_menu.add_command(label="Open File", command=self.open_current_file)
        self.context_menu.add_command(label="Check Compatibility", command=self.check_compatibility)
        self.context_menu.add_separator()
        self.context_menu.add_command(label="Mark as Keep", command=lambda: self.quick_status_change('keep'))
        self.context_menu.add_command(label="Mark as Archive", command=lambda: self.quick_status_change('archive'))
        self.context_menu.add_command(label="Mark as Remove", command=lambda: self.quick_status_change('remove'))
        self.context_menu.add_separator()
        self.context_menu.add_command(label="Mark PHP8 Done", command=lambda: self.quick_flag_change('php8', True))
        self.context_menu.add_command(label="Mark Tested", command=lambda: self.quick_flag_change('tested', True))
        
        # Bind right-click and left-click events
        self.file_tree.bind("<Button-3>", self.show_context_menu)
        self.file_tree.bind("<Button-1>", self.hide_context_menu)
        self.root.bind("<Button-1>", self.hide_context_menu)
    
    def show_context_menu(self, event):
        """Show context menu."""
        # First select the item that was right-clicked
        item = self.file_tree.identify_row(event.y)
        if item:
            self.file_tree.selection_set(item)
            self.file_tree.focus(item)
            # Trigger selection event to load file details
            self.on_file_select()
        
        try:
            self.context_menu.tk_popup(event.x_root, event.y_root)
        finally:
            self.context_menu.grab_release()
    
    def hide_context_menu(self, event=None):
        """Hide context menu when clicking elsewhere."""
        try:
            self.context_menu.unpost()
        except:
            pass
    
    def refresh_file_list(self):
        """Refresh the file list based on current filters."""
        # Apply filters
        files = self.apply_filters()
        self.current_files = files
        
        # Apply current sorting
        self.apply_file_list_sorting()
        
        # Refresh display
        self.refresh_file_list_display()
        
        self.update_status(f"Showing {len(files)} files")
    
    def apply_file_list_sorting(self):
        """Apply current sorting to the file list."""
        if self.sort_column == 'Checked':
            self.current_files.sort(key=lambda f: f.get('checked', 0), reverse=self.sort_reverse)
        elif self.sort_column == 'Path':
            self.current_files.sort(key=lambda f: self.db.get_file_path(f), reverse=self.sort_reverse)
        elif self.sort_column == 'Status':
            self.current_files.sort(key=lambda f: f['status'], reverse=self.sort_reverse)
        elif self.sort_column == 'PHP8':
            self.current_files.sort(key=lambda f: f['php8_rewritten'], reverse=self.sort_reverse)
        elif self.sort_column == 'Tested':
            self.current_files.sort(key=lambda f: f['tested'], reverse=self.sort_reverse)
        elif self.sort_column == 'Links':
            self.current_files.sort(key=lambda f: f['links_scanned'], reverse=self.sort_reverse)
        elif self.sort_column == 'IsLive':
            self.current_files.sort(key=lambda f: f.get('isLive', 0), reverse=self.sort_reverse)
        elif self.sort_column == 'Exists':
            self.current_files.sort(key=lambda f: f.get('file_exists', 1), reverse=self.sort_reverse)
        elif self.sort_column == 'Last Updated':
            self.current_files.sort(key=lambda f: f.get('last_updated', ''), reverse=self.sort_reverse)
    
    def apply_filters(self) -> List[Dict]:
        """Apply current filters and return filtered file list."""
        # Start with all files
        files = self.db.get_all_files()
        
        # Apply status filter
        status_filter = self.status_combo.get()
        if status_filter != 'all':
            files = [f for f in files if f['status'] == status_filter]
        else:
            # When showing 'all', exclude 'archive' and 'remove' statuses
            files = [f for f in files if f['status'] not in ['archive', 'remove']]
        
        # Apply PHP8 filter
        php8_filter = self.php8_combo.get()
        if php8_filter == 'done':
            files = [f for f in files if f['php8_rewritten']]
        elif php8_filter == 'pending':
            files = [f for f in files if not f['php8_rewritten']]
        
        # Apply tested filter
        tested_filter = self.tested_combo.get()
        if tested_filter == 'done':
            files = [f for f in files if f['tested']]
        elif tested_filter == 'pending':
            files = [f for f in files if not f['tested']]
        
        # Apply links filter
        links_filter = self.links_combo.get()
        if links_filter == 'done':
            files = [f for f in files if f['links_scanned']]
        elif links_filter == 'pending':
            files = [f for f in files if not f['links_scanned']]
        
        # Apply file exists filter
        exists_filter = self.exists_combo.get()
        if exists_filter == 'exists':
            files = [f for f in files if f.get('file_exists', 1)]
        elif exists_filter == 'missing':
            files = [f for f in files if not f.get('file_exists', 1)]
        
        # Apply search filter
        search_term = self.search_var.get().strip()
        if search_term:
            files = [f for f in files if search_term.lower() in self.db.get_file_path(f).lower()]
        
        return files
    
    def on_filter_change(self, event=None):
        """Handle filter change."""
        self.refresh_file_list()
    
    def on_search_change(self, event=None):
        """Handle search change with delay."""
        # Cancel previous timer if exists
        if hasattr(self, 'search_timer'):
            self.root.after_cancel(self.search_timer)
        
        # Set new timer
        self.search_timer = self.root.after(500, self.refresh_file_list)
    
    def clear_filters(self):
        """Clear all filters."""
        self.status_combo.set('all')
        self.php8_combo.set('all')
        self.tested_combo.set('all')
        self.links_combo.set('all')
        self.exists_combo.set('all')
        self.search_var.set('')
        self.refresh_file_list()
    
    def on_file_select(self, event=None):
        """Handle file selection with enhanced highlighting."""
        selection = self.file_tree.selection()
        if not selection:
            # Clear any existing highlighting when no selection
            self.clear_dependency_highlighting()
            return

        # Get selected item index
        item = selection[0]
        index = self.file_tree.index(item)

        if index < len(self.current_files):
            self.current_index = index
            selected_file = self.current_files[index]
            self.load_file_details(selected_file)

            # Apply enhanced highlighting based on links status
            self.apply_selection_highlighting(selected_file, item)

            # Scroll to make selected item visible at top
            self.file_tree.see(item)
    
    def load_file_details(self, file_record: Dict):
        """Load file details into the details panel."""
        path = self.db.get_file_path(file_record)
        self.path_var.set(path)
        
        # Update both the prominent current file display and the new active file display
        self.current_file_var.set(f"📁 {path}")
        self.active_file_var.set(path)
        
        self.status_detail_combo.set(file_record['status'])
        
        self.php8_var.set(bool(file_record['php8_rewritten']))
        self.tested_var.set(bool(file_record['tested']))
        self.links_var.set(bool(file_record['links_scanned']))
        self.key_file_var.set(bool(file_record.get('key_file', 0)))
        self.isLive_var.set(bool(file_record.get('isLive', 0)))
        
        # Load notes
        self.notes_text.delete(1.0, tk.END)
        if file_record['notes']:
            self.notes_text.insert(1.0, file_record['notes'])
        
        # Load dependencies
        self.refresh_dependencies()

        # Generate task instructions
        self.generate_task_instructions(file_record)

    def generate_task_instructions(self, file_record: Dict):
        """Generate copy-pastable task instructions for the selected file."""
        path = self.db.get_file_path(file_record)

        # Parse path to get filename and folder
        parts = path.split('/')
        if len(parts) >= 2:
            filename = parts[-1]
            folder = '/' + '/'.join(parts[:-1])
        else:
            filename = path
            folder = '/'

        instructions = f"Task instructions : read AI_info.md,\n\nthen upgrade the file {filename}\n\nlocated in the {folder} folder please"

        # Enable the widget temporarily to allow updates
        self.task_text.config(state='normal')
        self.task_text.delete(1.0, tk.END)
        self.task_text.insert(1.0, instructions)
        self.task_text.config(state='disabled')  # Make it read-only but selectable

    def refresh_dependencies(self):
        """Refresh the dependencies view for the current file."""
        if not self.current_files or self.current_index >= len(self.current_files):
            # Clear dependencies view
            for widget in self.deps_scrollable_frame.winfo_children():
                widget.destroy()
            self.dep_summary_var.set("No file selected")
            return

        file_record = self.current_files[self.current_index]
        file_id = file_record['id']

        # Get dependencies from links table
        dependencies = self.get_file_dependencies_with_status(file_id)

        # Clear existing items
        for widget in self.deps_scrollable_frame.winfo_children():
            widget.destroy()

        # Apply dependency filter
        filter_value = self.dep_filter_var.get()
        filtered_deps = self.filter_dependencies(dependencies, filter_value)

        # Apply sorting to dependencies
        self.apply_dependencies_sorting(filtered_deps)

        # Populate dependencies with clickable labels
        for dep in filtered_deps:
            path = dep['display_path']
            status = dep['status']
            php8 = "✅" if dep['php8_rewritten'] else "❌"
            tested = "✅" if dep['tested'] else "❌"
            exists = "✅" if dep['file_exists'] else "❌"

            # Add status emoji
            status_display = self.get_status_emoji(status) + " " + status

            # Create a frame for each dependency
            dep_frame = tk.Frame(self.deps_scrollable_frame, relief=tk.RAISED, borderwidth=1)
            dep_frame.pack(fill=tk.X, padx=5, pady=2)

            # Create clickable label for path
            path_label = tk.Label(
                dep_frame,
                text=path,
                font=("Courier", 10, "bold"),
                fg="blue",
                cursor="hand2",
                anchor="w",
                padx=5,
                pady=3
            )
            path_label.pack(side=tk.LEFT, fill=tk.X, expand=True)

            # Store the path in the label for the event handler
            path_label.dep_path = path

            # Bind click event to switch active file
            path_label.bind('<Button-1>', self.on_dependency_click)

            # Add hover effect
            path_label.bind('<Enter>', self.on_dependency_enter)
            path_label.bind('<Leave>', self.on_dependency_leave)

            # Create status labels
            status_label = tk.Label(dep_frame, text=status_display, font=("Arial", 9), padx=5)
            status_label.pack(side=tk.LEFT)

            php8_label = tk.Label(dep_frame, text=php8, font=("Arial", 9), padx=5)
            php8_label.pack(side=tk.LEFT)

            tested_label = tk.Label(dep_frame, text=tested, font=("Arial", 9), padx=5)
            tested_label.pack(side=tk.LEFT)

            exists_label = tk.Label(dep_frame, text=exists, font=("Arial", 9), padx=5)
            exists_label.pack(side=tk.LEFT)

        # Update summary
        total_deps = len(dependencies)
        filtered_count = len(filtered_deps)
        incomplete_count = len([d for d in dependencies if not d['php8_rewritten'] or not d['tested']])
        missing_count = len([d for d in dependencies if not d['file_exists']])

        summary = f"Dependencies: {filtered_count}/{total_deps}"
        if incomplete_count > 0:
            summary += f" | Incomplete: {incomplete_count}"
        if missing_count > 0:
            summary += f" | Missing: {missing_count}"

        self.dep_summary_var.set(summary)
    
    def get_file_dependencies_with_status(self, file_id: int) -> List[Dict]:
        """Get dependencies for a file with their current status."""
        links = self.db.get_links_for_file(file_id)
        dependencies = []
        
        for link in links:
            target_path = link['target_path']
            
            # Parse target path to find file in database
            if target_path.startswith('/intranet/'):
                rel_path = target_path[10:]  # Remove /intranet/
                parts = rel_path.split('/')
                
                if len(parts) == 1:
                    primary, sub, name = '', '', parts[0]
                elif len(parts) == 2:
                    primary, sub, name = parts[0], '', parts[1]
                else:
                    primary, sub, name = parts[0], parts[1], '/'.join(parts[2:])
                
                # Get target file from database
                target_file = self.db.get_file_by_path(primary, sub, name)
                
                if target_file:
                    dependencies.append({
                        'display_path': rel_path,
                        'status': target_file['status'],
                        'php8_rewritten': bool(target_file['php8_rewritten']),
                        'tested': bool(target_file['tested']),
                        'links_scanned': bool(target_file['links_scanned']),
                        'file_exists': bool(target_file.get('file_exists', 1)),
                        'target_file_id': target_file['id']
                    })
                else:
                    # File not in database
                    dependencies.append({
                        'display_path': rel_path,
                        'status': 'missing',
                        'php8_rewritten': False,
                        'tested': False,
                        'links_scanned': False,
                        'file_exists': False,
                        'target_file_id': None
                    })
        
        return dependencies
    
    def filter_dependencies(self, dependencies: List[Dict], filter_value: str) -> List[Dict]:
        """Filter dependencies based on filter value."""
        if filter_value == 'all':
            return dependencies
        elif filter_value == 'incomplete':
            return [d for d in dependencies if not d['php8_rewritten'] or not d['tested']]
        else:
            return [d for d in dependencies if d['status'] == filter_value]
    
    def get_status_emoji(self, status: str) -> str:
        """Get emoji for status."""
        emoji_map = {
            'new': '🆕',
            'keep': '✅',
            'archive': '📦',
            'remove': '❌',
            'Done': '🎯',
            'Key Page': '🔑',
            'Done Key Page': '🎯🔑',
            'missing': '❓'
        }
        return emoji_map.get(status, '❓')
    
    def on_dependency_filter_change(self, event=None):
        """Handle dependency filter change."""
        self.refresh_dependencies()
    
    def apply_dependencies_sorting(self, dependencies: List[Dict]):
        """Apply current sorting to the dependencies list."""
        if self.deps_sort_column == 'Path':
            dependencies.sort(key=lambda d: d['display_path'], reverse=self.deps_sort_reverse)
        elif self.deps_sort_column == 'Status':
            # Sort by status with custom order: missing, new, keep, archive, remove
            status_order = {'missing': 0, 'new': 1, 'keep': 2, 'archive': 3, 'remove': 4, 'Done': 5, 'Key Page': 6, 'Done Key Page': 7}
            dependencies.sort(key=lambda d: status_order.get(d['status'], 5), reverse=self.deps_sort_reverse)
        elif self.deps_sort_column == 'PHP8':
            dependencies.sort(key=lambda d: d['php8_rewritten'], reverse=self.deps_sort_reverse)
        elif self.deps_sort_column == 'Tested':
            dependencies.sort(key=lambda d: d['tested'], reverse=self.deps_sort_reverse)
        elif self.deps_sort_column == 'Exists':
            dependencies.sort(key=lambda d: d['file_exists'], reverse=self.deps_sort_reverse)
    
    def sort_file_list(self, column: str):
        """Sort the main file list by column."""
        # Toggle sort direction if same column
        if self.sort_column == column:
            self.sort_reverse = not self.sort_reverse
        else:
            self.sort_column = column
            self.sort_reverse = False
        
        # Update column header to show sort direction
        for col in ['Checked', 'Path', 'Status', 'PHP8', 'Tested', 'Links', 'IsLive', 'Exists', 'Last Updated']:
            if col == column:
                direction = " ↓" if self.sort_reverse else " ↑"
                if col == 'Checked':
                    self.file_tree.heading(col, text='✓' + direction)
                else:
                    self.file_tree.heading(col, text=col + direction)
            else:
                if col == 'Checked':
                    self.file_tree.heading(col, text='✓')
                else:
                    self.file_tree.heading(col, text=col)

        # Sort the current files list
        if column == 'Checked':
            self.current_files.sort(key=lambda f: f.get('checked', 0), reverse=self.sort_reverse)
        elif column == 'Path':
            self.current_files.sort(key=lambda f: self.db.get_file_path(f), reverse=self.sort_reverse)
        elif column == 'Status':
            self.current_files.sort(key=lambda f: f['status'], reverse=self.sort_reverse)
        elif column == 'PHP8':
            self.current_files.sort(key=lambda f: f['php8_rewritten'], reverse=self.sort_reverse)
        elif column == 'Tested':
            self.current_files.sort(key=lambda f: f['tested'], reverse=self.sort_reverse)
        elif column == 'Links':
            self.current_files.sort(key=lambda f: f['links_scanned'], reverse=self.sort_reverse)
        elif column == 'IsLive':
            self.current_files.sort(key=lambda f: f.get('isLive', 0), reverse=self.sort_reverse)
        elif column == 'Exists':
            self.current_files.sort(key=lambda f: f.get('file_exists', 1), reverse=self.sort_reverse)
        elif column == 'Last Updated':
            self.current_files.sort(key=lambda f: f.get('last_updated', ''), reverse=self.sort_reverse)
        
        # Refresh the display
        self.refresh_file_list_display()
    
    def sort_dependencies(self, column: str):
        """Sort the dependencies list by column."""
        # Toggle sort direction if same column
        if self.deps_sort_column == column:
            self.deps_sort_reverse = not self.deps_sort_reverse
        else:
            self.deps_sort_column = column
            self.deps_sort_reverse = False

        # Refresh dependencies with new sort order
        self.refresh_dependencies()

    def on_dependency_click(self, event):
        """Handle click on dependency label."""
        label = event.widget
        selected_dep = getattr(label, 'dep_path', None)
        if selected_dep:
            add_log(f"Dependency link clicked: {selected_dep}")
            self.switch_to_dependency_direct(selected_dep)

    def on_dependency_enter(self, event):
        """Handle mouse enter on dependency label."""
        label = event.widget
        label.config(bg="lightblue")

    def on_dependency_leave(self, event):
        """Handle mouse leave on dependency label."""
        label = event.widget
        label.config(bg="white")

    def switch_to_dependency_direct(self, selected_dep):
        """Switch active file to selected dependency (for direct click)"""
        add_log(f"Starting switch to dependency: {selected_dep}")

        # Find the dependency in the current files list and make it active
        for idx, file_record in enumerate(self.current_files):
            file_path = self.db.get_file_path(file_record)
            if file_path == selected_dep:
                add_log(f"Found dependency at index {idx}, making it active")

                # Set as active file (same as clicking in tree)
                self.current_index = idx
                self.load_file_details(file_record)

                # Update tree selection and scroll to make it visible at top
                for item in self.file_tree.get_children():
                    item_values = self.file_tree.item(item, 'values')
                    if len(item_values) > 1 and item_values[1] == selected_dep:
                        self.file_tree.selection_set(item)
                        self.file_tree.focus(item)
                        # Scroll to make the selected item visible at the top
                        self.file_tree.see(item)
                        break

                # Switch to File Info tab to show task instructions
                self.details_notebook.select(0)  # File Info tab is index 0

                self.update_status(f"Active file changed to: {selected_dep}")
                add_log(f"Successfully switched to dependency and opened File Info tab")
                return

        # If not found in current view, show error
        add_log(f"Dependency {selected_dep} not found in current file list")
        messagebox.showerror("Error", f"Dependency file not found in current view: {selected_dep}")
    
    def refresh_file_list_display(self):
        """Refresh only the file list display without re-filtering."""
        # Clear existing items
        for item in self.file_tree.get_children():
            self.file_tree.delete(item)
        
        # Populate treeview with current files (already sorted)
        for file_record in self.current_files:
            checked = "☑" if file_record.get('checked', 0) else "☐"
            path = self.db.get_file_path(file_record)
            status = file_record['status']
            php8 = "✅" if file_record['php8_rewritten'] else "❌"
            tested = "✅" if file_record['tested'] else "❌"
            links = "✅" if file_record['links_scanned'] else "❌"
            isLive = "✅" if file_record.get('isLive', 0) else "❌"
            exists = "✅" if file_record.get('file_exists', 1) else "❌"
            last_updated = file_record.get('last_updated', '')[:10] if file_record.get('last_updated') else ''

            item_id = self.file_tree.insert('', 'end', values=(checked, path, status, php8, tested, links, isLive, exists, last_updated))
            
            # Apply pastel yellow background for checked items
            if file_record.get('checked', 0):
                self.file_tree.set(item_id, 'Checked', '☑')
                # Configure tag for checked items
                self.file_tree.item(item_id, tags=('checked',))
        
        # Configure all highlighting tags
        self.file_tree.tag_configure('checked', background='#FFFACD')                    # Light yellow/pastel yellow for checked
        self.file_tree.tag_configure('selected_unscanned', background='#FFB6C1')        # Light red for unscanned dependencies
        self.file_tree.tag_configure('dependency_file_complete', background='#98FB98')  # Light green for fully complete dependency files
        self.file_tree.tag_configure('dependency_file_incomplete', background='#FFFFE0') # Light yellow for incomplete dependency files
        
        # Reapply selection highlighting if there's a current selection
        selection = self.file_tree.selection()
        if selection and self.current_files and self.current_index < len(self.current_files):
            selected_file = self.current_files[self.current_index]
            self.apply_selection_highlighting(selected_file, selection[0])
    
    def save_current_file(self):
        """Save changes to current file."""
        if not self.current_files or self.current_index >= len(self.current_files):
            return
        
        file_record = self.current_files[self.current_index]
        file_id = file_record['id']
        
        # Update status
        new_status = self.status_detail_combo.get()
        self.db.update_file_status(file_id, new_status)
        
        # Update flags
        self.db.update_file_flags(
            file_id,
            php8_rewritten=int(self.php8_var.get()),
            tested=int(self.tested_var.get()),
            links_scanned=int(self.links_var.get()),
            key_file=int(self.key_file_var.get()),
            isLive=int(self.isLive_var.get())
        )
        
        # Update notes
        notes = self.notes_text.get(1.0, tk.END).strip()
        self.db.update_file_notes(file_id, notes)
        
        # Refresh display
        self.refresh_file_list()
        self.update_status("File saved successfully")
        
        messagebox.showinfo("Success", "File updated successfully!")
    
    def quick_status_change(self, new_status: str):
        """Quick status change from context menu."""
        selection = self.file_tree.selection()
        if not selection:
            return
        
        item = selection[0]
        index = self.file_tree.index(item)
        
        if index < len(self.current_files):
            file_record = self.current_files[index]
            self.db.update_file_status(file_record['id'], new_status)
            self.refresh_file_list()
            self.update_status(f"Status changed to {new_status}")
    
    def quick_flag_change(self, flag_type: str, value: bool):
        """Quick flag change from context menu."""
        selection = self.file_tree.selection()
        if not selection:
            return
        
        item = selection[0]
        index = self.file_tree.index(item)
        
        if index < len(self.current_files):
            file_record = self.current_files[index]
            
            kwargs = {}
            if flag_type == 'php8':
                kwargs['php8_rewritten'] = int(value)
            elif flag_type == 'tested':
                kwargs['tested'] = int(value)
            elif flag_type == 'links':
                kwargs['links_scanned'] = int(value)
            
            self.db.update_file_flags(file_record['id'], **kwargs)
            self.refresh_file_list()
            self.update_status(f"Flag {flag_type} updated")
    
    def open_current_file(self):
        """Open current file in external editor."""
        if not self.current_files or self.current_index >= len(self.current_files):
            messagebox.showwarning("Warning", "No file selected")
            return
        
        file_record = self.current_files[self.current_index]
        file_path = self.db.get_full_filesystem_path(file_record)
        
        if not os.path.exists(file_path):
            messagebox.showerror("Error", f"File not found: {file_path}")
            return
        
        try:
            # Try to open with default editor
            if sys.platform.startswith('linux'):
                subprocess.run(['xdg-open', file_path])
            elif sys.platform.startswith('darwin'):
                subprocess.run(['open', file_path])
            elif sys.platform.startswith('win'):
                os.startfile(file_path)
        except Exception as e:
            messagebox.showerror("Error", f"Failed to open file: {e}")
    
    def check_compatibility(self):
        """Check PHP compatibility of current file."""
        if not self.current_files or self.current_index >= len(self.current_files):
            messagebox.showwarning("Warning", "No file selected")
            return
        
        file_record = self.current_files[self.current_index]
        file_path = self.db.get_full_filesystem_path(file_record)
        
        try:
            # Simple compatibility check
            issues = self.check_php_compatibility(file_path)
            
            if issues:
                message = "Compatibility Issues Found:\n\n" + "\n".join(issues)
                messagebox.showwarning("Compatibility Check", message)
            else:
                messagebox.showinfo("Compatibility Check", 
                                  "✅ File looks compatible with both PHP 5.6 and 8.x")
        except Exception as e:
            messagebox.showerror("Error", f"Failed to check compatibility: {e}")
    
    def check_php_compatibility(self, file_path: str) -> List[str]:
        """Basic PHP compatibility check."""
        if not os.path.exists(file_path):
            return [f"File not found: {file_path}"]
        
        try:
            with open(file_path, 'r', encoding='utf-8', errors='ignore') as f:
                content = f.read()
        except Exception as e:
            return [f"Error reading file: {str(e)}"]
        
        issues = []
        
        # Basic checks
        if '??' in content:
            issues.append("❌ Found null coalescing operator (??) — not PHP 5.6 compatible.")
        
        if 'declare(strict_types=1)' in content:
            issues.append("❌ Found strict types declaration — not PHP 5.6 compatible.")
        
        import re
        if re.search(r'\bmysql_', content):
            issues.append("❌ Found deprecated mysql_* functions — should use mysqli_* or PDO.")
        
        return issues
    
    def scan_current_file_links(self):
        """Scan current file for links."""
        if not self.current_files or self.current_index >= len(self.current_files):
            messagebox.showwarning("Warning", "No file selected")
            return
        
        try:
            from scan_links import LinkScanner
            scanner = LinkScanner()
            
            file_record = self.current_files[self.current_index]
            links_found = scanner.scan_file_by_id(file_record['id'])
            
            self.refresh_file_list()
            self.refresh_dependencies()  # Refresh dependencies view
            messagebox.showinfo("Link Scan", f"Found {links_found} dependencies")
        except Exception as e:
            messagebox.showerror("Error", f"Failed to scan links: {e}")
    
    def scan_all_links(self):
        """Scan all unscanned files for links."""
        try:
            from scan_links import LinkScanner
            scanner = LinkScanner()
            
            # Show progress dialog
            progress_window = tk.Toplevel(self.root)
            progress_window.title("Scanning Links")
            progress_window.geometry("400x100")
            progress_window.transient(self.root)
            progress_window.grab_set()
            
            progress_label = ttk.Label(progress_window, text="Scanning files for dependencies...")
            progress_label.pack(pady=20)
            
            progress_bar = ttk.Progressbar(progress_window, mode='indeterminate')
            progress_bar.pack(pady=10, padx=20, fill=tk.X)
            progress_bar.start()
            
            # Update display
            self.root.update()
            
            # Perform scan
            stats = scanner.scan_all_unscanned()
            
            # Close progress dialog
            progress_window.destroy()
            
            # Show results
            message = f"Scan complete!\n\nFiles scanned: {stats['files_scanned']}\nLinks found: {stats['total_links']}"
            if stats['errors'] > 0:
                message += f"\nErrors: {stats['errors']}"
            
            messagebox.showinfo("Link Scan Complete", message)
            self.refresh_file_list()
            
        except Exception as e:
            messagebox.showerror("Error", f"Failed to scan links: {e}")
    
    def bulk_update_dialog(self):
        """Show bulk update dialog."""
        dialog = BulkUpdateDialog(self.root, self.db, self.current_files)
        if dialog.result:
            self.refresh_file_list()
            self.update_status("Bulk update completed")

    def tag_done(self):
        """Tag files as Done if all required flags are set."""
        if not self.current_files:
            messagebox.showwarning("Warning", "No files in current view")
            return

        updated_count = 0
        for file_record in self.current_files:
            # Check if all four conditions are met: PHP8, Tested, Links, File Exists
            if (file_record['php8_rewritten'] and
                file_record['tested'] and
                file_record['links_scanned'] and
                file_record.get('file_exists', 1)):
                # Update status to Done
                self.db.update_file_status(file_record['id'], 'Done')
                updated_count += 1

        if updated_count > 0:
            self.refresh_file_list()
            messagebox.showinfo("Tag Done", f"Marked {updated_count} files as Done")
            self.update_status(f"Tagged {updated_count} files as Done")
        else:
            messagebox.showinfo("Tag Done", "No files met the criteria (PHP8 ✅, Tested ✅, Links ✅, File Exists ✅)")
    
    def generate_report_dialog(self):
        """Show report generation dialog."""
        dialog = ReportDialog(self.root)
        if dialog.result:
            try:
                from report import ReportGenerator
                generator = ReportGenerator()
                
                if dialog.result == 'summary':
                    output_file = generator.generate_summary_markdown()
                elif dialog.result == 'csv':
                    output_file = generator.generate_csv_report()
                elif dialog.result == 'dependencies':
                    output_file = generator.generate_dependency_report()
                
                messagebox.showinfo("Report Generated", f"Report saved to:\n{output_file}")
            except Exception as e:
                messagebox.showerror("Error", f"Failed to generate report: {e}")
    
    def create_backup(self):
        """Create system backup."""
        try:
            from backup import create_backup
            backup_file = create_backup()
            messagebox.showinfo("Backup Created", f"Backup saved to:\n{backup_file}")
        except Exception as e:
            messagebox.showerror("Error", f"Failed to create backup: {e}")
    
    def import_data_dialog(self):
        """Show data import dialog."""
        result = messagebox.askyesno("Import Data", 
                                   "Import data from existing progress.md and TO_DO.md files?\n\n" +
                                   "This will merge existing data with the database.")
        if result:
            try:
                from migrate_data import main as migrate_main
                # Run migration in background
                subprocess.run([sys.executable, 'migrate_data.py'], cwd='Tools')
                self.refresh_file_list()
                messagebox.showinfo("Import Complete", "Data imported successfully!")
            except Exception as e:
                messagebox.showerror("Error", f"Failed to import data: {e}")
    
    def verify_all_files(self):
        """Verify all files exist on filesystem."""
        try:
            # Show progress dialog
            progress_window = tk.Toplevel(self.root)
            progress_window.title("Verifying Files")
            progress_window.geometry("400x100")
            progress_window.transient(self.root)
            progress_window.grab_set()
            
            progress_label = ttk.Label(progress_window, text="Checking file existence...")
            progress_label.pack(pady=20)
            
            progress_bar = ttk.Progressbar(progress_window, mode='indeterminate')
            progress_bar.pack(pady=10, padx=20, fill=tk.X)
            progress_bar.start()
            
            # Update display
            self.root.update()
            
            # Perform verification
            stats = self.db.verify_all_files_exist()
            
            # Close progress dialog
            progress_window.destroy()
            
            # Show results
            message = f"File verification complete!\n\n"
            message += f"Total files: {stats['total']}\n"
            message += f"Files found: {stats['exists']}\n"
            message += f"Missing files: {stats['missing']}"
            
            if stats['missing'] > 0:
                message += f"\n\nUse the 'Exists: missing' filter to see missing files."
            
            messagebox.showinfo("File Verification Complete", message)
            self.refresh_file_list()
            
        except Exception as e:
            messagebox.showerror("Error", f"Failed to verify files: {e}")
    
    def generate_missing_files_list(self):
        """Generate missing files list for server collection."""
        try:
            from verify_files import generate_missing_files_list
            output_file = generate_missing_files_list()
            
            # Show the file content in a dialog
            missing_files = self.db.get_missing_files()
            
            if missing_files:
                message = f"Missing files list generated!\n\n"
                message += f"File saved to: {output_file}\n\n"
                message += f"Found {len(missing_files)} missing files:\n"
                
                # Show first few missing files
                for i, file_record in enumerate(missing_files[:5]):
                    path = self.db.get_file_path(file_record)
                    message += f"  • {path}\n"
                
                if len(missing_files) > 5:
                    message += f"  ... and {len(missing_files) - 5} more\n"
                
                message += f"\nUse 'Exists: missing' filter to see all missing files in the list."
                
                messagebox.showinfo("Missing Files List Generated", message)
            else:
                messagebox.showinfo("No Missing Files", "✅ All files are present on the filesystem!")
                
        except Exception as e:
            messagebox.showerror("Error", f"Failed to generate missing files list: {e}")
    
    def rescan_inventory(self):
        """Rescan filesystem for new files and update database."""
        try:
            # Show progress dialog
            progress_window = tk.Toplevel(self.root)
            progress_window.title("Rescanning Files")
            progress_window.geometry("400x120")
            progress_window.transient(self.root)
            progress_window.grab_set()
            
            progress_label = ttk.Label(progress_window, text="Scanning filesystem for new files...")
            progress_label.pack(pady=10)
            
            progress_bar = ttk.Progressbar(progress_window, mode='indeterminate')
            progress_bar.pack(pady=10, padx=20, fill=tk.X)
            progress_bar.start()
            
            status_label = ttk.Label(progress_window, text="Initializing scan...")
            status_label.pack(pady=5)
            
            # Update display
            self.root.update()
            
            # Get current file count
            old_stats = self.db.get_statistics()
            old_count = old_stats['total_files']
            
            # Run inventory scan
            status_label.config(text="Scanning vmserver10/intranet directory...")
            self.root.update()
            
            from inventory import scan_directory, update_database
            # Use absolute path to avoid path resolution issues
            base_dir = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
            vmserver_path = os.path.join(base_dir, 'vmserver10', 'intranet')
            
            php_files = scan_directory(vmserver_path)
            
            if php_files is None:
                raise Exception("scan_directory returned None - check if vmserver10/intranet directory exists")
            
            if not isinstance(php_files, list):
                raise Exception(f"scan_directory returned unexpected type: {type(php_files)}")
            
            status_label.config(text="Updating database...")
            self.root.update()
            
            update_database(php_files, preserve_existing=True)
            
            # Get new stats
            new_stats = self.db.get_statistics()
            new_count = new_stats['total_files']
            files_added = new_count - old_count
            
            # Close progress dialog
            progress_window.destroy()
            
            # Show results
            message = f"Inventory rescan complete!\n\n"
            message += f"Files before: {old_count}\n"
            message += f"Files after: {new_count}\n"
            message += f"New files added: {files_added}"
            
            if files_added > 0:
                message += f"\n\nNew files have been added with 'new' status."
                message += f"\nUse filters to find and classify them."
            
            messagebox.showinfo("Rescan Complete", message)
            
            # Refresh the file list
            self.refresh_file_list()
            self.update_status(f"Rescan complete - {files_added} new files added")
            
        except Exception as e:
            if 'progress_window' in locals():
                progress_window.destroy()
            
            # Enhanced error reporting
            import traceback
            error_details = traceback.format_exc()
            print(f"Rescan error details:\n{error_details}")
            
            # Show detailed error message
            error_msg = f"Failed to rescan inventory: {str(e)}\n\nDetails:\n{error_details}"
            messagebox.showerror("Rescan Error", error_msg)
    
    def show_statistics(self):
        """Show database statistics."""
        stats = self.db.get_statistics()
        
        stats_window = tk.Toplevel(self.root)
        stats_window.title("Database Statistics")
        stats_window.geometry("450x350")
        stats_window.transient(self.root)
        
        frame = ttk.Frame(stats_window, padding="20")
        frame.pack(fill=tk.BOTH, expand=True)
        
        ttk.Label(frame, text="Database Statistics", font=("Arial", 14, "bold")).pack(pady=(0, 20))
        
        # Overall stats
        ttk.Label(frame, text=f"Total Files: {stats['total_files']}", font=("Arial", 12)).pack(anchor=tk.W)
        ttk.Label(frame, text=f"PHP8 Completed: {stats['php8_completed']}", font=("Arial", 12)).pack(anchor=tk.W)
        ttk.Label(frame, text=f"Tested: {stats['tested_completed']}", font=("Arial", 12)).pack(anchor=tk.W)
        ttk.Label(frame, text=f"Links Scanned: {stats['links_scanned']}", font=("Arial", 12)).pack(anchor=tk.W)
        ttk.Label(frame, text=f"Total Links: {stats['total_links']}", font=("Arial", 12)).pack(anchor=tk.W)
        
        # File existence stats
        missing_files = self.db.get_missing_files()
        existing_files = self.db.get_files_by_existence(True)
        ttk.Label(frame, text=f"Files Found: {len(existing_files)}", font=("Arial", 12)).pack(anchor=tk.W)
        ttk.Label(frame, text=f"Missing Files: {len(missing_files)}", font=("Arial", 12)).pack(anchor=tk.W)
        
        # Status breakdown
        ttk.Label(frame, text="\nFiles by Status:", font=("Arial", 12, "bold")).pack(anchor=tk.W, pady=(10, 5))
        by_status = stats.get('by_status', {})
        for status, count in by_status.items():
            ttk.Label(frame, text=f"  {status.title()}: {count}", font=("Arial", 11)).pack(anchor=tk.W)
        
        ttk.Button(frame, text="Close", command=stats_window.destroy).pack(pady=(20, 0))
    
    def on_quick_mode_toggle(self):
        """Handle quick mode toggle."""
        if self.quick_mode.get():
            self.quick_mode_status.config(text="ON", foreground="green")
            self.update_status("Quick Mode ON - Double-click Status column to cycle status, ✅/❌ icons to toggle flags instantly")
        else:
            self.quick_mode_status.config(text="OFF", foreground="red")
            self.update_status("Quick Mode OFF - Use detail panel to edit files")
    
    def on_tree_double_click(self, event):
        """Handle double-click on tree items for quick mode."""
        # Get clicked item and column
        item = self.file_tree.identify('item', event.x, event.y)
        column = self.file_tree.identify('column', event.x, event.y)
        
        if not item or not column:
            return
        
        # Get file index
        try:
            index = self.file_tree.index(item)
            if index >= len(self.current_files):
                return
            
            file_record = self.current_files[index]
            file_id = file_record['id']
            
            # Determine which column was clicked
            column_names = ['Checked', 'Path', 'Status', 'PHP8', 'Tested', 'Links', 'IsLive', 'Exists', 'Last Updated']
            col_index = int(column.replace('#', '')) - 1
            
            if col_index < 0 or col_index >= len(column_names):
                return
            
            clicked_column = column_names[col_index]
            
            # Handle checkbox toggle (works regardless of quick mode)
            if clicked_column == 'Checked':
                new_value = 0 if file_record.get('checked', 0) else 1
                self.db.update_file_checked(file_id, bool(new_value))
                action = "checked" if new_value else "unchecked"
                self.update_status(f"File {action}: {self.db.get_file_path(file_record)}")
                
                # Update the current files list
                self.current_files[index]['checked'] = new_value
                
                # Refresh the display
                self.refresh_file_list_display()
                return
            
            # Quick mode functionality for other columns
            if not self.quick_mode.get():
                return
            
            # Handle status cycling
            if clicked_column == 'Status':
                # Cycle through statuses: new → keep → archive → remove → new
                current_status = file_record['status']
                status_cycle = ['new', 'keep', 'archive', 'remove', 'Done', 'Key Page', 'Done Key Page']
                current_index = status_cycle.index(current_status)
                next_index = (current_index + 1) % len(status_cycle)
                new_status = status_cycle[next_index]
                
                self.db.update_file_status(file_id, new_status)
                self.update_status(f"Status changed from '{current_status}' to '{new_status}' for {self.db.get_file_path(file_record)}")
                
            # Handle flag toggles
            elif clicked_column == 'PHP8':
                new_value = 0 if file_record['php8_rewritten'] else 1
                self.db.update_file_flags(file_id, php8_rewritten=new_value)
                action = "marked as complete" if new_value else "marked as incomplete"
                self.update_status(f"PHP8 {action} for {self.db.get_file_path(file_record)}")
                
            elif clicked_column == 'Tested':
                new_value = 0 if file_record['tested'] else 1
                self.db.update_file_flags(file_id, tested=new_value)
                action = "marked as complete" if new_value else "marked as incomplete"
                self.update_status(f"Testing {action} for {self.db.get_file_path(file_record)}")
                
            elif clicked_column == 'Links':
                new_value = 0 if file_record['links_scanned'] else 1
                self.db.update_file_flags(file_id, links_scanned=new_value)
                action = "marked as scanned" if new_value else "marked as unscanned"
                self.update_status(f"Links {action} for {self.db.get_file_path(file_record)}")

            elif clicked_column == 'IsLive':
                new_value = 0 if file_record.get('isLive', 0) else 1
                self.db.update_file_flags(file_id, isLive=new_value)
                action = "marked as live" if new_value else "marked as not live"
                self.update_status(f"IsLive {action} for {self.db.get_file_path(file_record)}")

            else:
                return  # Not a clickable column
            
            # Refresh the display
            self.refresh_file_list()
            
            # Update current file details if this file is selected
            if self.current_index == index:
                self.load_file_details(self.current_files[index])
                
        except Exception as e:
            messagebox.showerror("Error", f"Failed to update flag: {e}")
    
    def generate_specific_report(self, report_type: str):
        """Generate specific report type directly."""
        try:
            from report import ReportGenerator
            generator = ReportGenerator()
            
            self.update_description(f"Generating {report_type} report...")
            
            if report_type == 'summary':
                output_file = generator.generate_summary_markdown()
            elif report_type == 'csv':
                output_file = generator.generate_csv_report()
            elif report_type == 'dependencies':
                output_file = generator.generate_dependency_report()
            
            messagebox.showinfo("Report Generated", f"Report saved to:\n{output_file}")
            self.update_description("")
            
        except Exception as e:
            messagebox.showerror("Error", f"Failed to generate {report_type} report: {e}")
            self.update_description("")
    
    def cleanup_duplicates(self):
        """Run duplicate cleanup tool."""
        try:
            result = messagebox.askyesno("Cleanup Duplicates",
                                        "This will clean up any duplicate file entries with server paths.\n\n" +
                                        "Do you want to proceed?")
            if result:
                from cleanup_duplicates import remove_server_path_duplicates
                removed = remove_server_path_duplicates(dry_run=False)

                if removed > 0:
                    messagebox.showinfo("Cleanup Complete", f"Removed {removed} duplicate entries")
                    self.refresh_file_list()
                else:
                    messagebox.showinfo("Cleanup Complete", "No duplicates found to remove")
        except Exception as e:
            messagebox.showerror("Error", f"Failed to cleanup duplicates: {e}")

    def verify_islive_status(self):
        """Run IsLive verification tool."""
        try:
            result = messagebox.askyesno("Verify IsLive Status",
                                        "This will check files against the live system and automatically mark matching files as IsLive.\n\n" +
                                        "Files must:\n" +
                                        "• Have status other than 'archive' or 'remove'\n" +
                                        "• Exist in the filesystem\n" +
                                        "• Not already be marked as IsLive\n\n" +
                                        "The process compares MD5 hashes to ensure exact matches.\n\n" +
                                        "Do you want to proceed?")
            if result:
                # Show progress dialog
                progress_window = tk.Toplevel(self.root)
                progress_window.title("Verifying IsLive Status")
                progress_window.geometry("500x150")
                progress_window.transient(self.root)
                progress_window.grab_set()

                progress_label = ttk.Label(progress_window, text="Initializing IsLive verification...")
                progress_label.pack(pady=20)

                progress_bar = ttk.Progressbar(progress_window, mode='indeterminate')
                progress_bar.pack(pady=10, padx=20, fill=tk.X)
                progress_bar.start()

                status_label = ttk.Label(progress_window, text="")
                status_label.pack(pady=5)

                # Update display
                self.root.update()

                try:
                    # Import and run the verification
                    from verify_islive import IsLiveVerifier

                    status_label.config(text="Running verification...")
                    self.root.update()

                    verifier = IsLiveVerifier()
                    stats = verifier.run_verification(dry_run=False)

                    # Close progress dialog
                    progress_window.destroy()

                    # Show results
                    message = f"IsLive verification complete!\n\n"
                    message += f"Files checked: {stats['total_checked']}\n"
                    message += f"Marked as live: {stats['marked_live']}\n"
                    if stats.get('errors', 0) > 0:
                        message += f"Errors: {stats['errors']}\n\n"
                        message += "Check the console output for details."

                    messagebox.showinfo("IsLive Verification Complete", message)

                    # Refresh the file list to show updated IsLive status
                    self.refresh_file_list()
                    self.update_status(f"IsLive verification: {stats['marked_live']} files marked as live")

                except Exception as e:
                    progress_window.destroy()
                    messagebox.showerror("Error", f"Failed to run IsLive verification: {e}")

        except Exception as e:
            messagebox.showerror("Error", f"Failed to start IsLive verification: {e}")
    
    def show_help(self):
        """Show quick start guide."""
        help_window = tk.Toplevel(self.root)
        help_window.title("Quick Start Guide")
        help_window.geometry("700x600")
        help_window.transient(self.root)
        
        frame = ttk.Frame(help_window, padding="20")
        frame.pack(fill=tk.BOTH, expand=True)
        
        # Create scrollable text widget
        text_frame = ttk.Frame(frame)
        text_frame.pack(fill=tk.BOTH, expand=True)
        
        help_text = tk.Text(text_frame, wrap=tk.WORD, font=("Arial", 11))
        scrollbar = ttk.Scrollbar(text_frame, orient=tk.VERTICAL, command=help_text.yview)
        help_text.configure(yscrollcommand=scrollbar.set)
        
        help_content = """PHP Migration Tracking System - Quick Start

🎯 BASIC USAGE:
• Select files from the list to edit details on the right
• Use filters to find specific files (Status, PHP8, Tested, Links, Exists)
• Right-click files for quick actions

⚡ QUICK MODE:
• Toggle Quick Mode (top-right) for instant updates
• Double-click Status column to cycle: new → keep → archive → remove → new
• Double-click ✅/❌ icons in PHP8, Tested, Links columns
• Changes save automatically - no save button needed

🔍 FILE EXISTENCE:
• "File Exists" column shows ✅ for existing, ❌ for missing files
• Use "Exists: missing" filter to see files needing collection
• System → "Generate Missing Files List" creates collection list

📁 ADDING NEW FILES:
• After copying files to vmserver10/intranet/
• Use System → "Rescan Files" to add them to database
• New files appear with "new" status

🔗 DEPENDENCY SCANNING:
• System → "Scan All Links" finds file dependencies
• Auto-discovers referenced files and adds them to database
• Use System → "Verify File Existence" after scanning

📊 MENU ORGANIZATION:

FILE MENU:
• Open Selected File - Launch file in external editor
• Check Compatibility - PHP 5.6/8.x compatibility check
• Exit - Close application

SYSTEM MENU:
• Rescan Files - Find new PHP files added to filesystem
• Verify File Existence - Check which files actually exist
• Generate Missing Files List - Create collection list for server
• Scan All Links - Find all file dependencies
• Import Data from Markdown - Import from old progress files
• Create Backup - Backup entire system
• Database Statistics - Show current progress stats

REPORTS MENU:
• Generate Summary Report - Progress overview with statistics
• Export to CSV - Complete data export for spreadsheets
• Dependencies Report - File relationship analysis
• Custom Report - Choose specific report options

TOOLS MENU:
• Bulk Update Files - Update multiple files at once
• Cleanup Duplicates - Remove duplicate entries

💡 WORKFLOW TIPS:
• Use Quick Mode for fast batch updates
• Combine filters to focus on specific areas
• Regular backups recommended before major changes
• Missing files list helps collect files from server
• Right-click context menu for quick status changes

🚨 QUICK MODE USAGE:
• Enable Quick Mode toggle (top-right)
• Double-click Status column to cycle through: new → keep → archive → remove → new
• Double-click ✅ to change to ❌ (mark incomplete)
• Double-click ❌ to change to ✅ (mark complete)
• Works on Status, PHP8, Tested, and Links columns
• File Exists column cannot be toggled (reflects filesystem)"""
        
        help_text.insert(1.0, help_content)
        help_text.config(state=tk.DISABLED)
        
        help_text.pack(side=tk.LEFT, fill=tk.BOTH, expand=True)
        scrollbar.pack(side=tk.RIGHT, fill=tk.Y)
        
        ttk.Button(frame, text="Close", command=help_window.destroy).pack(pady=(10, 0))
    
    def show_about(self):
        """Show about dialog."""
        about_text = """PHP Migration Tracking System
Version 1.0

A comprehensive database-driven system for tracking 
PHP8 migration progress, managing file inventories, 
and coordinating migration workflows.

Features:
• Database-driven file tracking with SQLite
• Quick Mode for instant flag updates
• File existence verification and missing file detection
• Dependency scanning and analysis
• Comprehensive reporting (Summary, CSV, Dependencies)
• Automated backups with versioning
• Advanced filtering and search capabilities
• Bulk operations for efficient file management

Built for efficient PHP8 migration management.
Organizes all migration work in a single unified system."""
        
        messagebox.showinfo("About", about_text)
    
    def update_description(self, text: str):
        """Update description label."""
        self.description_var.set(text)
    
    def load_openai_api_key(self) -> Optional[str]:
        """Load OpenAI API key from config file."""
        config_file = os.path.join(os.path.dirname(__file__), 'openai_config.json')
        try:
            if os.path.exists(config_file):
                with open(config_file, 'r') as f:
                    config = json.load(f)
                    return config.get('api_key')
        except Exception:
            pass
        return None
    
    def save_openai_api_key(self, api_key: str):
        """Save OpenAI API key to config file."""
        config_file = os.path.join(os.path.dirname(__file__), 'openai_config.json')
        try:
            config = {'api_key': api_key}
            with open(config_file, 'w') as f:
                json.dump(config, f, indent=2)
            self.openai_api_key = api_key
        except Exception as e:
            messagebox.showerror("Error", f"Failed to save API key: {e}")
    
    def configure_openai_api_key(self):
        """Configure OpenAI API key."""
        current_key = self.openai_api_key or ""
        masked_key = current_key[:8] + "..." + current_key[-4:] if len(current_key) > 12 else current_key

        dialog_text = f"Current API Key: {masked_key if current_key else '(not set)'}\n\nEnter your OpenAI API Key:"

        new_key = simpledialog.askstring("OpenAI API Key", dialog_text, show='*')
        if new_key:
            self.save_openai_api_key(new_key.strip())
            messagebox.showinfo("Success", "OpenAI API key saved successfully!")
            # Refresh the UI to show/hide GPT button
            self.setup_action_buttons_frame(self.root.children['!frame'])

    def toggle_logs(self):
        """Toggle the visibility of the application logs section."""
        if self.logs_visible.get():
            # Hide logs
            self.log_label.grid_remove()
            self.log_frame.grid_remove()
            self.logs_visible.set(False)
            self.update_status("Application logs hidden")
        else:
            # Show logs
            self.log_label.grid()
            self.log_frame.grid()
            self.logs_visible.set(True)
            self.update_status("Application logs shown")
    
    def gpt_compatibility_check(self):
        """Check PHP compatibility using OpenAI GPT."""
        if not OPENAI_AVAILABLE:
            messagebox.showerror("Error", "OpenAI package not installed. Install with: pip install openai")
            return
        
        if not self.openai_api_key:
            messagebox.showerror("Error", "OpenAI API key not configured. Use Tools → Configure OpenAI API Key")
            return
        
        if not self.current_files or self.current_index >= len(self.current_files):
            messagebox.showwarning("Warning", "No file selected")
            return
        
        file_record = self.current_files[self.current_index]
        file_path = self.db.get_full_filesystem_path(file_record)
        
        if not os.path.exists(file_path):
            messagebox.showerror("Error", f"File not found: {file_path}")
            return
        
        try:
            # Read file content
            with open(file_path, 'r', encoding='utf-8', errors='ignore') as f:
                content = f.read()
            
            # Limit content size for API call
            if len(content) > 8000:
                content = content[:8000] + "\n\n... (file truncated for analysis)"
            
            # Show progress dialog
            progress_window = tk.Toplevel(self.root)
            progress_window.title("GPT Analysis")
            progress_window.geometry("300x100")
            progress_window.transient(self.root)
            progress_window.grab_set()
            
            progress_label = ttk.Label(progress_window, text="Analyzing with GPT...")
            progress_label.pack(pady=20)
            
            progress_bar = ttk.Progressbar(progress_window, mode='indeterminate')
            progress_bar.pack(pady=10, padx=20, fill=tk.X)
            progress_bar.start()
            
            self.root.update()
            
            # Call OpenAI API
            client = openai.OpenAI(api_key=self.openai_api_key)
            
            prompt = f"""Please analyze this PHP code for compatibility with both PHP 5.6 and PHP 8.x.

Respond with ONLY:
- "YES" if the code is compatible with both PHP 5.6 and PHP 8.x
- "NO" if the code has compatibility issues

Then provide a brief explanation of any issues found.

PHP Code:
```php
{content}
```"""
            
            response = client.chat.completions.create(
                model="gpt-3.5-turbo",
                messages=[{"role": "user", "content": prompt}],
                max_tokens=500,
                temperature=0.1
            )
            
            # Close progress dialog
            progress_window.destroy()
            
            # Parse response
            gpt_response = response.choices[0].message.content.strip()
            
            # Determine if compatible
            is_compatible = gpt_response.upper().startswith("YES")
            
            # Add GPT results to Notes field
            from datetime import datetime
            current_date = datetime.now().strftime("%d/%m/%y")
            gpt_section = f"*** GPT {current_date} ***\n{gpt_response}\n"
            
            # Get existing notes
            existing_notes = file_record.get('notes', '') or ''
            
            # Check if there's already a GPT section for today
            today_marker = f"*** GPT {current_date} ***"
            if today_marker in existing_notes:
                # Replace today's GPT section
                lines = existing_notes.split('\n')
                new_lines = []
                skip_section = False
                
                for line in lines:
                    if line.strip() == today_marker:
                        skip_section = True
                        continue
                    elif skip_section and (line.strip() == "" or line.startswith("***")):
                        skip_section = False
                        if line.startswith("***"):
                            new_lines.append(line)
                        continue
                    elif not skip_section:
                        new_lines.append(line)
                
                # Add new GPT section at the top
                updated_notes = gpt_section + "\n" + '\n'.join(new_lines).strip()
            else:
                # Add GPT section at the top, pushing existing notes down
                if existing_notes.strip():
                    updated_notes = gpt_section + "\n" + existing_notes.strip()
                else:
                    updated_notes = gpt_section.strip()
            
            # Update notes in database
            self.db.update_file_notes(file_record['id'], updated_notes)
            
            # Update the current file record with new notes
            file_record['notes'] = updated_notes
            self.current_files[self.current_index] = file_record
            
            # Update the Notes text widget directly
            self.notes_text.delete(1.0, tk.END)
            self.notes_text.insert(1.0, updated_notes)
            
            # Show results
            if is_compatible:
                title = "GPT Analysis: Compatible ✅"
                icon = messagebox.showinfo
            else:
                title = "GPT Analysis: Issues Found ⚠️"
                icon = messagebox.showwarning
            
            message = f"File: {self.db.get_file_path(file_record)}\n\nGPT Analysis:\n{gpt_response}\n\nResults have been saved to the Notes field."
            icon(title, message)
            
        except Exception as e:
            if 'progress_window' in locals():
                progress_window.destroy()
            messagebox.showerror("GPT Error", f"Failed to analyze with GPT: {e}")
    
    def toggle_checkbox_column(self):
        """Toggle all checkboxes in the current view."""
        if not self.current_files:
            return
        
        # Check if any files are currently checked
        any_checked = any(f.get('checked', 0) for f in self.current_files)
        
        # If any are checked, uncheck all; otherwise, check all
        new_state = not any_checked
        
        try:
            updated_count = 0
            for file_record in self.current_files:
                file_id = file_record['id']
                self.db.update_file_checked(file_id, new_state)
                file_record['checked'] = int(new_state)
                updated_count += 1
            
            action = "checked" if new_state else "unchecked"
            self.update_status(f"All visible files {action} ({updated_count} files)")
            
            # Refresh the display
            self.refresh_file_list_display()
            
        except Exception as e:
            messagebox.showerror("Error", f"Failed to toggle checkboxes: {e}")
    
    def clear_dependency_highlighting(self):
        """Clear all dependency-related highlighting."""
        for item in self.file_tree.get_children():
            # Get current tags
            current_tags = list(self.file_tree.item(item, 'tags'))
            # Remove dependency highlighting tags but keep 'checked' tag
            new_tags = [tag for tag in current_tags if tag not in ['selected_unscanned', 'dependency_file_complete', 'dependency_file_incomplete']]
            self.file_tree.item(item, tags=new_tags)
    
    def apply_selection_highlighting(self, selected_file, selected_item):
        """Apply highlighting based on selected file's dependency status."""
        # Clear any existing dependency highlighting
        self.clear_dependency_highlighting()
        
        # Check if selected file has scanned or unscanned dependencies
        links_scanned = selected_file.get('links_scanned', 0)
        
        if not links_scanned:
            # File has unscanned dependencies - highlight in red
            current_tags = list(self.file_tree.item(selected_item, 'tags'))
            if 'selected_unscanned' not in current_tags:
                current_tags.append('selected_unscanned')
                self.file_tree.item(selected_item, tags=current_tags)
        else:
            # File has scanned dependencies - highlight dependency files in green
            self.highlight_dependency_files(selected_file['id'])
    
    def highlight_dependency_files(self, file_id):
        """Highlight dependency files with completion status colors."""
        # Get dependencies for the selected file
        dependencies = self.get_file_dependencies_with_status(file_id)
        
        if not dependencies:
            return
        
        # Create a mapping of dependency paths to their completion status
        dependency_status = {}
        for dep in dependencies:
            path = dep['display_path']
            # Check if all three flags are complete (PHP8, Tested, Links)
            is_complete = (dep['php8_rewritten'] and dep['tested'] and dep['links_scanned'])
            dependency_status[path] = is_complete
        
        # Go through all visible files and highlight dependencies
        for item in self.file_tree.get_children():
            item_values = self.file_tree.item(item, 'values')
            if len(item_values) > 1:  # Make sure we have the path value
                file_path = item_values[1]  # Path is in column 1
                
                # Check if this file is a dependency
                if file_path in dependency_status:
                    current_tags = list(self.file_tree.item(item, 'tags'))
                    
                    # Determine the appropriate highlighting tag based on completion status
                    if dependency_status[file_path]:
                        # All flags complete - green highlighting
                        tag_to_add = 'dependency_file_complete'
                    else:
                        # Some flags incomplete - yellow highlighting
                        tag_to_add = 'dependency_file_incomplete'
                    
                    # Remove any existing dependency tags and add the new one
                    current_tags = [tag for tag in current_tags if tag not in ['dependency_file_complete', 'dependency_file_incomplete']]
                    current_tags.append(tag_to_add)
                    self.file_tree.item(item, tags=current_tags)

    def on_tab_changed(self, event):
        """Handle tab change event."""
        current_tab = self.details_notebook.tab(self.details_notebook.select(), "text")
        add_log(f"Switched to tab: {current_tab}")

    def update_status(self, message: str):
        """Update status bar."""
        self.status_var.set(message)

    def perform_quick_search(self):
        """Perform quick file search in dependencies only."""
        active_file_path = self.active_file_var.get().strip()
        if not active_file_path or active_file_path == "No file selected":
            messagebox.showwarning("Warning", "No active file selected")
            return

        # Extract filename from path
        import os
        search_term = os.path.basename(active_file_path)
        if not search_term:
            messagebox.showwarning("Warning", "Invalid file path")
            return

        # Get current file record
        if not self.current_files or self.current_index >= len(self.current_files):
            messagebox.showwarning("Warning", "No file selected")
            return

        file_record = self.current_files[self.current_index]
        file_id = file_record['id']

        # Clear previous results
        self.search_results_text.delete(1.0, tk.END)
        self.search_results_text.insert(1.0, f"Quick searching for references to '{search_term}' in dependencies...\n\n")

        # Get dependencies
        dependencies = self.get_file_dependencies_with_status(file_id)

        base_dir = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
        databases_dir = os.path.join(base_dir, 'vmserver10', 'intranet', 'databases')

        found_files = []
        for dep in dependencies:
            if dep['file_exists']:
                dep_path = dep['display_path']
                full_path = os.path.join(databases_dir, dep_path)
                try:
                    with open(full_path, 'r', encoding='utf-8', errors='ignore') as f:
                        content = f.read()
                        if search_term in content:
                            found_files.append(dep_path)
                except Exception as e:
                    print(f"Error reading {full_path}: {e}")

        # Display results
        self.search_results_text.delete(1.0, tk.END)
        if found_files:
            self.search_results_text.insert(1.0, f"Found {len(found_files)} dependency files containing '{search_term}':\n\n")
            for f in sorted(found_files):
                self.search_results_text.insert(tk.END, f"{f}\n")
        else:
            self.search_results_text.insert(1.0, f"No dependency files found containing '{search_term}'")

    def perform_deep_search(self):
        """Perform deep file search for references to the active file."""
        active_file_path = self.active_file_var.get().strip()
        if not active_file_path or active_file_path == "No file selected":
            messagebox.showwarning("Warning", "No active file selected")
            return

        # Extract filename from path
        import os
        search_term = os.path.basename(active_file_path)
        if not search_term:
            messagebox.showwarning("Warning", "Invalid file path")
            return

        # Clear previous results
        self.search_results_text.delete(1.0, tk.END)
        self.search_results_text.insert(1.0, f"Deep searching for references to '{search_term}'...\n\n")

        # Perform search
        base_dir = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
        databases_dir = os.path.join(base_dir, 'vmserver10', 'intranet', 'databases')

        # Show progress dialog
        progress_window = tk.Toplevel(self.root)
        progress_window.title("Deep Searching Files")
        progress_window.geometry("400x100")
        progress_window.transient(self.root)
        progress_window.grab_set()

        progress_label = ttk.Label(progress_window, text=f"Searching for references to '{search_term}'...")
        progress_label.pack(pady=20)

        progress_bar = ttk.Progressbar(progress_window, mode='indeterminate')
        progress_bar.pack(pady=10, padx=20, fill=tk.X)
        progress_bar.start()

        self.root.update()

        found_files = []
        try:
            for root_dir, dirs, files in os.walk(databases_dir):
                for file in files:
                    if file.endswith('.php'):
                        file_path = os.path.join(root_dir, file)
                        try:
                            with open(file_path, 'r', encoding='utf-8', errors='ignore') as f:
                                content = f.read()
                                if search_term in content:
                                    # Get relative path from databases/
                                    rel_path = os.path.relpath(file_path, databases_dir)
                                    found_files.append(rel_path)
                        except Exception as e:
                            print(f"Error reading {file_path}: {e}")
        except Exception as e:
            messagebox.showerror("Error", f"Search failed: {e}")
            progress_window.destroy()
            return

        # Close progress dialog
        progress_window.destroy()

        # Display results
        self.search_results_text.delete(1.0, tk.END)
        if found_files:
            self.search_results_text.insert(1.0, f"Found {len(found_files)} files containing '{search_term}':\n\n")
            for f in sorted(found_files):
                self.search_results_text.insert(tk.END, f"{f}\n")
        else:
            self.search_results_text.insert(1.0, f"No files found containing '{search_term}'")


class BulkUpdateDialog:
    """Dialog for bulk operations."""
    
    def __init__(self, parent, db, files):
        self.parent = parent
        self.db = db
        self.files = files
        self.result = None
        
        self.dialog = tk.Toplevel(parent)
        self.dialog.title("Bulk Update")
        self.dialog.geometry("400x300")
        self.dialog.transient(parent)
        self.dialog.grab_set()
        
        self.setup_ui()
    
    def setup_ui(self):
        """Setup dialog UI."""
        frame = ttk.Frame(self.dialog, padding="20")
        frame.pack(fill=tk.BOTH, expand=True)
        
        ttk.Label(frame, text="Bulk Update Operations", font=("Arial", 14, "bold")).pack(pady=(0, 20))
        
        ttk.Label(frame, text=f"Selected files: {len(self.files)}").pack(anchor=tk.W)
        
        # Status update
        status_frame = ttk.LabelFrame(frame, text="Update Status", padding="10")
        status_frame.pack(fill=tk.X, pady=(10, 0))
        
        self.status_var = tk.StringVar(value="keep")
        ttk.Radiobutton(status_frame, text="Keep", variable=self.status_var, value="keep").pack(anchor=tk.W)
        ttk.Radiobutton(status_frame, text="Archive", variable=self.status_var, value="archive").pack(anchor=tk.W)
        ttk.Radiobutton(status_frame, text="Remove", variable=self.status_var, value="remove").pack(anchor=tk.W)
        
        # Flags update
        flags_frame = ttk.LabelFrame(frame, text="Update Flags", padding="10")
        flags_frame.pack(fill=tk.X, pady=(10, 0))
        
        self.php8_var = tk.BooleanVar()
        self.tested_var = tk.BooleanVar()
        self.links_var = tk.BooleanVar()
        
        ttk.Checkbutton(flags_frame, text="Mark PHP8 Complete", variable=self.php8_var).pack(anchor=tk.W)
        ttk.Checkbutton(flags_frame, text="Mark Tested", variable=self.tested_var).pack(anchor=tk.W)
        ttk.Checkbutton(flags_frame, text="Mark Links Scanned", variable=self.links_var).pack(anchor=tk.W)
        
        # Buttons
        button_frame = ttk.Frame(frame)
        button_frame.pack(fill=tk.X, pady=(20, 0))
        
        ttk.Button(button_frame, text="Apply", command=self.apply_updates).pack(side=tk.RIGHT, padx=(5, 0))
        ttk.Button(button_frame, text="Cancel", command=self.cancel).pack(side=tk.RIGHT)
    
    def apply_updates(self):
        """Apply bulk updates."""
        try:
            updated_count = 0
            
            for file_record in self.files:
                file_id = file_record['id']
                
                # Update status
                self.db.update_file_status(file_id, self.status_var.get())
                
                # Update flags if checked
                kwargs = {}
                if self.php8_var.get():
                    kwargs['php8_rewritten'] = 1
                if self.tested_var.get():
                    kwargs['tested'] = 1
                if self.links_var.get():
                    kwargs['links_scanned'] = 1
                
                if kwargs:
                    self.db.update_file_flags(file_id, **kwargs)
                
                updated_count += 1
            
            self.result = True
            messagebox.showinfo("Success", f"Updated {updated_count} files")
            self.dialog.destroy()
            
        except Exception as e:
            messagebox.showerror("Error", f"Failed to update files: {e}")
    
    def cancel(self):
        """Cancel dialog."""
        self.result = False
        self.dialog.destroy()


class ReportDialog:
    """Dialog for report generation."""
    
    def __init__(self, parent):
        self.parent = parent
        self.result = None
        
        self.dialog = tk.Toplevel(parent)
        self.dialog.title("Generate Report")
        self.dialog.geometry("300x200")
        self.dialog.transient(parent)
        self.dialog.grab_set()
        
        self.setup_ui()
    
    def setup_ui(self):
        """Setup dialog UI."""
        frame = ttk.Frame(self.dialog, padding="20")
        frame.pack(fill=tk.BOTH, expand=True)
        
        ttk.Label(frame, text="Select Report Type", font=("Arial", 14, "bold")).pack(pady=(0, 20))
        
        self.report_var = tk.StringVar(value="summary")
        
        ttk.Radiobutton(frame, text="Summary Report", variable=self.report_var, value="summary").pack(anchor=tk.W, pady=5)
        ttk.Radiobutton(frame, text="CSV Export", variable=self.report_var, value="csv").pack(anchor=tk.W, pady=5)
        ttk.Radiobutton(frame, text="Dependencies Report", variable=self.report_var, value="dependencies").pack(anchor=tk.W, pady=5)
        
        # Buttons
        button_frame = ttk.Frame(frame)
        button_frame.pack(fill=tk.X, pady=(20, 0))
        
        ttk.Button(button_frame, text="Generate", command=self.generate).pack(side=tk.RIGHT, padx=(5, 0))
        ttk.Button(button_frame, text="Cancel", command=self.cancel).pack(side=tk.RIGHT)
    
    def generate(self):
        """Generate report."""
        self.result = self.report_var.get()
        self.dialog.destroy()
    
    def cancel(self):
        """Cancel dialog."""
        self.result = None
        self.dialog.destroy()


def main():
    """Main function."""
    root = tk.Tk()
    app = MigrationGUI(root)

    # Add log window under File Details section
    # We need to add it to the main frame, not directly to root since root uses grid
    main_frame = root.children['!frame']  # Get the main frame that uses grid

    # Add log section to the bottom of the main frame (hidden by default)
    app.log_label = ttk.Label(main_frame, text="Application Logs", font=("Arial", 12, "bold"))
    # Don't grid initially since logs_visible defaults to False

    # Create frame for log text with scrollbar
    app.log_frame = ttk.Frame(main_frame, relief=tk.SUNKEN, borderwidth=2)
    # Don't grid initially since logs_visible defaults to False

    # Configure the log frame to expand
    app.log_frame.columnconfigure(0, weight=1)
    app.log_frame.rowconfigure(0, weight=1)

    global log_text
    log_text = tk.Text(app.log_frame, height=10, wrap=tk.WORD, font=("Courier", 9), bg="black", fg="green")
    scrollbar = tk.Scrollbar(app.log_frame, command=log_text.yview)
    log_text.config(yscrollcommand=scrollbar.set)

    log_text.grid(row=0, column=0, sticky=(tk.W, tk.E, tk.N, tk.S))
    scrollbar.grid(row=0, column=1, sticky=(tk.N, tk.S))

    # Add initial log message
    add_log("Application started - logging enabled")

    root.mainloop()


if __name__ == '__main__':
    main()