#master_update_tab.py

import os
import tkinter as tk
from tkinter import ttk, messagebox
import tksheet
from datetime import datetime
from config_full import *
from db_connection import get_db_connection
from utils import *
from excel_utils import *
from custom_widgets import LoadingWindow, TreeviewTooltip
from master_updater import process_file

class MasterUpdateApp(tk.Tk):
    def __init__(self):
        super().__init__()
        self.title("BSNL Outstanding Master Update")
        self.geometry("1200x800")
        
        # Initialize variables
        self.mode = "old"
        self.folder = self.initialize_folder()
        self.fuzzy_fields = get_fuzzy_fields_from_db()
        self.tables = self.get_tables()
        self.status_var = tk.StringVar(value="Ready")
        self.loading_window = None
        
        # Initialize UI
        self.init_ui()
        self.load_dropdowns()
        self.refresh_data()

    def initialize_folder(self):
        """Get Excel folder from DB or create default."""
        folder = get_default_excel_folder()
        if folder and os.path.exists(folder):
            return folder
            
        # If no folder in DB, create default
        default_folder = os.path.join(os.getcwd(), "dafile")
        if not os.path.exists(default_folder):
            try:
                os.makedirs(default_folder)
                messagebox.showinfo("Folder Created", 
                    f"Created new folder: {default_folder}\nPlease copy your Excel files here.")
            except Exception as e:
                print(f"Could not create default folder: {e}")
                default_folder = self.select_folder()
                
        set_default_excel_folder(default_folder)
        return default_folder

    def select_folder(self):
        """Prompt user to select Excel folder."""
        folder = tk.filedialog.askdirectory(
            title="Select Excel Files Folder",
            initialdir=os.getcwd()
        )
        if not folder:
            messagebox.showerror("Error", "Must select a folder to continue!")
            self.quit()
        return folder

    def get_tables(self):
        """Get table names based on current mode."""
        return {
            "log": get_config_value(TABLE_OLD_SOURCE_CONVERT_LOG, self.mode),
            "status": get_config_value(TABLE_OLD_OUTSTANDING_STATUS, self.mode)
        }

    def show_loading(self, message="Loading..."):
        """Show loading window with message."""
        if not self.loading_window:
            self.loading_window = LoadingWindow(self)
        self.loading_window.set_message(message)
        self.loading_window.show()

    def update_loading(self, message, progress=None, data_line=None):
        """Update loading window progress."""
        if self.loading_window:
            if message:
                self.loading_window.update_progress(message, progress)
            if data_line:
                self.loading_window.update_data(data_line)

    def hide_loading(self):
        """Hide and destroy loading window."""
        if self.loading_window:
            self.loading_window.close()
            self.loading_window = None
    def init_ui(self):
        """Initialize all UI components."""
        # Mode Selection
        mode_frame = ttk.LabelFrame(self, text="Mode Selection")
        mode_frame.pack(fill="x", padx=5, pady=5)
        
        self.mode_var = tk.StringVar(value=self.mode)
        ttk.Radiobutton(mode_frame, text="Old Outstanding", variable=self.mode_var, 
                       value="old", command=self.switch_mode).pack(side="left", padx=10)
        ttk.Radiobutton(mode_frame, text="New Outstanding", variable=self.mode_var,
                       value="new", command=self.switch_mode).pack(side="left", padx=10)

        # Folder Frame
        folder_frame = ttk.LabelFrame(self, text="Excel Folder")
        folder_frame.pack(fill="x", padx=5, pady=5)
        
        self.folder_label = ttk.Label(folder_frame, text=f"Current: {self.folder}")
        self.folder_label.pack(side="left", padx=5)
        ttk.Button(folder_frame, text="Change Folder", 
                  command=self.change_folder).pack(side="left", padx=5)
        ttk.Button(folder_frame, text="Refresh", 
                  command=self.refresh_data).pack(side="left", padx=5)

        # Filter Frame
        filter_frame = ttk.LabelFrame(self, text="Filters")
        filter_frame.pack(fill="x", padx=5, pady=5)

        # Area Filter
        area_frame = ttk.Frame(filter_frame)
        area_frame.pack(side="left", padx=5)
        ttk.Label(area_frame, text="Area:").pack(side="left")
        self.area_var = tk.StringVar(value="All Areas")
        self.area_combo = ttk.Combobox(area_frame, textvariable=self.area_var, 
                                     width=25, state="readonly")
        self.area_combo.pack(side="left")
        self.area_combo.bind("<<ComboboxSelected>>", self.on_area_selected)

        # SDCA Filter
        sdca_frame = ttk.Frame(filter_frame)
        sdca_frame.pack(side="left", padx=5)
        ttk.Label(sdca_frame, text="SDCA:").pack(side="left")
        self.sdca_var = tk.StringVar(value="All SDCAs")
        self.sdca_combo = ttk.Combobox(sdca_frame, textvariable=self.sdca_var,
                                     width=25, state="readonly")
        self.sdca_combo.pack(side="left")
        self.sdca_combo.bind("<<ComboboxSelected>>", self.on_sdca_selected)

        # Exchange Filter
        exchange_frame = ttk.Frame(filter_frame)
        exchange_frame.pack(side="left", padx=5)
        ttk.Label(exchange_frame, text="Exchange:").pack(side="left")
        self.exchange_var = tk.StringVar(value="All Exchanges")
        self.exchange_combo = ttk.Combobox(exchange_frame, textvariable=self.exchange_var,
                                         width=25, state="readonly")
        self.exchange_combo.pack(side="left")
        self.exchange_combo.bind("<<ComboboxSelected>>", self.refresh_data)

        # Excel Files TreeView
        file_frame = ttk.LabelFrame(self, text="Excel Files (Double-click to process)")
        file_frame.pack(fill="both", expand=True, padx=5, pady=5)
        
        self.file_sheet = tksheet.Sheet(file_frame, height=250)
        self.file_sheet.pack(fill="both", expand=True)
        self.file_sheet.enable_bindings(("single_select", "row_select", "double_click_row"))
        self.file_sheet.extra_bindings([("double_click_row", self.on_file_double_click)])

        # Log Summary TreeView
        log_frame = ttk.LabelFrame(self, text="Processing Log")
        log_frame.pack(fill="both", expand=True, padx=5, pady=5)
        
        self.log_sheet = tksheet.Sheet(log_frame, height=250)
        self.log_sheet.pack(fill="both", expand=True)
        self.log_sheet.enable_bindings("all")

        # Status Bar
        status_frame = ttk.Frame(self)
        status_frame.pack(fill="x", padx=5, pady=5)
        ttk.Label(status_frame, textvariable=self.status_var).pack(side="left")

    def switch_mode(self):
        """Handle mode switch between old/new."""
        self.mode = self.mode_var.get()
        self.tables = self.get_tables()
        self.refresh_data()

    def change_folder(self):
        """Change Excel folder and update display."""
        new_folder = tk.filedialog.askdirectory(
            title="Select Excel Files Folder",
            initialdir=self.folder
        )
        if new_folder:
            self.folder = new_folder
            self.folder_label.config(text=f"Current: {self.folder}")
            set_default_excel_folder(new_folder)
            self.refresh_data()

    def load_dropdowns(self):
        """Load all dropdown values."""
        # Load Area dropdown
        areas = get_area_options()
        self.area_map = {name: code for code, name in areas}
        self.area_combo["values"] = ["All Areas"] + list(self.area_map.keys())
        
        # Load dependent dropdowns
        self.load_sdca_dropdown()
        self.load_exchange_dropdown()

    def load_sdca_dropdown(self):
        """Load SDCA dropdown based on selected area."""
        area_code = self.area_map.get(self.area_var.get())
        sdcas = get_sdca_options(area_code)
        self.sdca_map = {name: code for code, name in sdcas}
        self.sdca_combo["values"] = ["All SDCAs"] + list(self.sdca_map.keys())

    def load_exchange_dropdown(self):
        """Load Exchange dropdown based on selected SDCA/Area."""
        area_code = self.area_map.get(self.area_var.get())
        sdca_code = self.sdca_map.get(self.sdca_var.get())
        exchanges = get_exchange_options(sdca_code, area_code)
        self.exchange_map = {name: code for code, name in exchanges}
        self.exchange_combo["values"] = ["All Exchanges"] + list(self.exchange_map.keys())

    def on_area_selected(self, event=None):
        """Handle area selection."""
        self.load_sdca_dropdown()
        self.load_exchange_dropdown()
        self.refresh_data()

    def on_sdca_selected(self, event=None):
        """Handle SDCA selection."""
        self.load_exchange_dropdown()
        self.refresh_data()
    def refresh_data(self):
        """Refresh both file list and log data with loading indicator."""
        self.show_loading("Scanning Excel Files...")
        self.update_idletasks()
        
        try:
            if not os.path.exists(self.folder):
                raise FileNotFoundError(f"Folder not found: {self.folder}")

            # Get all Excel files
            files = get_valid_excel_files(self.folder)
            
            if not files:
                self.status_var.set(f"No Excel files found in: {self.folder}")
                self.file_sheet.set_sheet_data([])
                self.hide_loading()
                return
                
            total_files = len(files)
            self.update_loading(f"Found {total_files} Excel files")
            
            valid_files_data = []
            log_table = self.tables["log"]

            # Process each file
            for idx, filename in enumerate(files, 1):
                progress = int((idx / total_files) * 100)
                self.update_loading(f"Processing file {idx} of {total_files}", progress)
                self.update_loading("", None, f"Checking: {filename}")
                
                full_path = os.path.join(self.folder, filename)
                
                # Check if file matches current mode (old/new)
                if self.mode == "old" and not is_old_outstanding_file(filename):
                    continue
                if self.mode == "new" and not is_current_outstanding_file(filename):
                    continue

                # Validate Excel structure
                valid, missing, header_row = validate_excel_file(full_path, self.fuzzy_fields)
                if not valid:
                    self.update_loading("", None, f"Invalid headers. Missing: {', '.join(missing)}")
                    continue

                # Extract and validate date
                data_date = extract_data_date_from_filename(filename)
                if not data_date:
                    self.update_loading("", None, "Could not extract date from filename")
                    continue
                
                display_date = format_ddmmyy_to_ddmmyyyy(data_date)

                # Count records
                try:
                    record_count = count_data_rows_after_header(full_path, self.fuzzy_fields)
                    self.update_loading("", None, f"Found {record_count} records")
                except Exception as e:
                    self.update_loading("", None, f"Error counting records: {str(e)}")
                    record_count = "Error"

                # Check log table status
                conn = get_db_connection()
                try:
                    cursor = conn.cursor()
                    source_updated_col = get_config_value(COL_SOURCE_UPDATED, self.mode)
                    master_updated_col = get_config_value(COL_MASTER_UPDATED, self.mode)
                    
                    cursor.execute(f"""
                        SELECT {q(source_updated_col)}, {q(master_updated_col)}
                        FROM {q(log_table)}
                        WHERE {q(COL_EXCEL_FILE_NAME)} = ? 
                        AND {q(COL_DATA_DATE)} = ?
                    """, (filename, data_date))
                    row = cursor.fetchone()
                    
                    if row:
                        src_upd = "Yes" if row[0] else "No"
                        mst_upd = "Yes" if row[1] else "No"
                    else:
                        src_upd = "No"
                        mst_upd = "No"
                        
                    self.update_loading("", None, f"Log status: Source={src_upd}, Master={mst_upd}")
                    
                    valid_files_data.append([
                        filename, display_date, record_count,
                        src_upd, mst_upd
                    ])
                        
                finally:
                    conn.close()

            # Update file sheet
            self.update_loading(f"Displaying {len(valid_files_data)} files", 100)
            
            self.file_sheet.headers([
                "File Name", "Date", "Records",
                "Source Updated", "Master Updated"
            ])
            self.file_sheet.set_sheet_data(valid_files_data)
            
            # Add tooltips
            TreeviewTooltip(self.file_sheet)
            
            # Refresh log summary
            self.refresh_log_sheet()
            
            self.status_var.set(f"Ready - Found {len(valid_files_data)} valid files")
            
        except Exception as e:
            self.status_var.set(f"Error: {str(e)}")
            messagebox.showerror("Error", f"Failed to refresh data: {str(e)}")
        finally:
            self.hide_loading()

    def refresh_log_sheet(self):
        """Refresh the log summary sheet."""
        try:
            self.update_loading("Loading log data...")
            log_table = self.tables["log"]
            conn = get_db_connection()
            try:
                cursor = conn.cursor()
                master_updated_col = get_config_value(COL_MASTER_UPDATED, self.mode)
                
                cursor.execute(f"""
                    SELECT 
                        {q(COL_EXCEL_FILE_NAME)},
                        {q(COL_DATA_DATE)},
                        COALESCE({q("TOTAL_NEW")}, 0) as TOTAL_NEW,
                        COALESCE({q("TOTAL_REPEATED")}, 0) as TOTAL_REPEATED,
                        COALESCE({q("TOTAL_PAID")}, 0) as TOTAL_PAID
                    FROM {q(log_table)}
                    WHERE {q(master_updated_col)} = 1
                    ORDER BY {q(COL_DATA_DATE)} DESC
                """)
                
                log_data = []
                for row in cursor.fetchall():
                    log_data.append([
                        row[0],  # File name
                        format_ddmmyy_to_ddmmyyyy(row[1]),  # Date
                        int(row[2] or 0),  # New
                        int(row[3] or 0),  # Repeated
                        int(row[4] or 0)   # Paid
                    ])
                
                self.log_sheet.headers([
                    "File Name", "Date", "New", "Repeated", "Paid"
                ])
                self.log_sheet.set_sheet_data(log_data)
                
                # Add tooltips to log sheet
                TreeviewTooltip(self.log_sheet)
                
            finally:
                conn.close()
                
        except Exception as e:
            print(f"Error refreshing log: {str(e)}")
            self.status_var.set("Error refreshing log data")
    def on_file_double_click(self, event):
        """Handle double-click on file in tree view."""
        try:
            selected = self.file_sheet.get_currently_selected()
            if not selected or selected[0] != "row":
                return
                
            row = int(selected[1])
            file_name = self.file_sheet.get_cell_data(row, 0)
            date_str = self.file_sheet.get_cell_data(row, 1)
            source_updated = self.file_sheet.get_cell_data(row, 3)
            master_updated = self.file_sheet.get_cell_data(row, 4)
            
            # Check if already processed
            if master_updated == "Yes":
                messagebox.showwarning("Already Processed",
                    f"This file has already been processed:\n{file_name}")
                return
                
            # Validate source status
            if source_updated not in ["Yes", "No"]:
                messagebox.showerror("Invalid Status",
                    f"Invalid source updated status: {source_updated}")
                return

            # Extract data_date for validation
            data_date = extract_data_date_from_filename(file_name)
            if not data_date:
                messagebox.showerror("Error", 
                    "Could not extract date from filename")
                return

            # Check if date is allowed
            if not is_new_data_date_allowed(data_date, self.tables["log"]):
                latest_date = get_latest_master_updated_date(self.tables["log"])
                messagebox.showerror("Invalid Date",
                    f"Cannot process file with date {date_str}.\n"
                    f"Must be newer than {format_ddmmyy_to_ddmmyyyy(latest_date)}")
                return
            
            # Confirm processing
            if not messagebox.askyesno("Confirm Process",
                f"Process this file?\n\n"
                f"File: {file_name}\n"
                f"Date: {date_str}\n"
                f"Source Updated: {source_updated}"):
                return
                
            # Verify file exists
            file_path = os.path.join(self.folder, file_name)
            if not os.path.exists(file_path):
                messagebox.showerror("Error", f"File not found: {file_path}")
                return
                
            # Show processing status
            self.status_var.set(f"Processing {file_name}...")
            self.show_loading(f"Processing {file_name}")
            self.update_idletasks()
            
            try:
                # Process file
                result, msg = process_file(file_path, self.mode)
                if result:
                    messagebox.showinfo("Success", msg)
                    self.refresh_data()
                else:
                    messagebox.showerror("Failed", msg)
            except Exception as e:
                messagebox.showerror("Error", f"Processing failed: {str(e)}")
            finally:
                self.hide_loading()
                self.status_var.set("Ready")
                
        except Exception as e:
            messagebox.showerror("Error", f"Double-click handling failed: {str(e)}")
            self.status_var.set("Ready")

if __name__ == "__main__":
    try:
        # Setup logging
        import logging
        logging.basicConfig(
            level=logging.INFO,
            format='%(asctime)s - %(levelname)s - %(message)s',
            handlers=[
                logging.FileHandler('master_update.log'),
                logging.StreamHandler()
            ]
        )
        
        # Create data folder if needed
        if not os.path.exists("dafile"):
            os.makedirs("dafile")
            logging.info("Created dafile folder")
            
        # Start application
        app = MasterUpdateApp()
        app.mainloop()
        
    except Exception as e:
        logging.error(f"Application failed to start: {str(e)}")
        messagebox.showerror("Fatal Error", 
            f"Application failed to start: {str(e)}\n\n"
            f"Please check master_update.log for details.")

WHY NOT SHOWING TREE VIEW FILE LIST AND LOG DATA