import sqlite3
import os
import tkinter as tk
from tkinter import ttk
import openpyxl  # You'd still need this if you're integrating directly

# Re-using the grouping function from the previous response for context
def group_rows_by_color(excel_file_path):
    """
    Groups rows from an Excel file where each new group starts at a color-filled row.
    (This is a simplified version; refer to the full previous code for details)
    """
    try:
        workbook = openpyxl.load_workbook(excel_file_path)
        sheet = workbook.active

        groups = []
        current_group = []
        
        for row_index, row in enumerate(sheet.iter_rows()):
            first_cell = row[0] 
            is_color_filled_row = False
            if first_cell.fill and first_cell.fill.patternType:
                is_color_filled_row = True

            row_values = [cell.value for cell in row]

            if is_color_filled_row:
                if current_group:
                    groups.append(current_group)
                current_group = [row_values]
            else:
                current_group.append(row_values)

        if current_group:
            groups.append(current_group)

        return groups

    except FileNotFoundError:
        print(f"Error: The file '{excel_file_path}' was not found.")
        return []
    except Exception as e:
        print(f"An error occurred during grouping: {e}")
        return []


def create_and_insert_data_to_db(grouped_data, db_name='my_grouped_data.db'):
    """
    Creates a SQLite database table and inserts the grouped data.

    Args:
        grouped_data (list): A list of lists, where each inner list is a group
                             and contains rows (lists of cell values).
        db_name (str): The name of the SQLite database file.
    """
    conn = None
    try:
        conn = sqlite3.connect(db_name)
        cursor = conn.cursor()

        # --- 1. Create Table ---
        # Define your table schema.
        # You'll need to adapt 'Column1', 'Column2', etc., based on your actual data columns.
        # For simplicity, let's assume a fixed number of columns for each row
        # and add a 'group_id' to link rows to their respective groups.
        
        # Example schema: (adjust based on your actual data structure)
        # You might have a variable number of columns or specific names.
        # This example assumes up to 10 columns per row, and adds a group_id.
        # You should inspect your data to determine the actual number and type of columns.
        
        # To make it flexible, let's assume the first row of your data (e.g., headers) 
        # defines the column count.
        
        # Determine the maximum number of columns across all groups/rows to create a flexible table
        max_cols = 0
        for group in grouped_data:
            for row in group:
                if len(row) > max_cols:
                    max_cols = len(row)
        
        # Create column definitions
        columns_sql = [f"Column{i+1} TEXT" for i in range(max_cols)]
        table_columns = "group_id INTEGER, row_in_group_id INTEGER, " + ", ".join(columns_sql)
        
        cursor.execute(f'''
            CREATE TABLE IF NOT EXISTS grouped_data (
                {table_columns}
            )
        ''')
        conn.commit()
        print(f"Table 'grouped_data' created or already exists in '{db_name}'.")

        # --- 2. Insert Data ---
        for group_id, group in enumerate(grouped_data):
            for row_in_group_id, row_values in enumerate(group):
                # Prepare data for insertion
                # Fill missing columns with None to match the table's max_cols
                padded_row_values = row_values + [None] * (max_cols - len(row_values))
                
                # The values to insert will be group_id, row_in_group_id, then the row_values
                values_to_insert = [group_id, row_in_group_id] + padded_row_values
                
                # Create placeholders for the SQL query
                placeholders = ', '.join(['?'] * len(values_to_insert))
                
                cursor.execute(f'''
                    INSERT INTO grouped_data VALUES ({placeholders})
                ''', values_to_insert)
        
        conn.commit()
        print(f"Successfully inserted {len(grouped_data)} groups into '{db_name}'.")

    except sqlite3.Error as e:
        print(f"SQLite error: {e}")
    except Exception as e:
        print(f"An unexpected error occurred: {e}")
    finally:
        if conn:
            conn.close()

# --- How to use it ---
# 1. First, get your grouped data using the function from earlier:
excel_file_path = 'c:/Users/Todd.SFC/CascadeProjects/data_importer/Buff.xlsx' # Make sure this is the path to your actual .xlsx file
my_grouped_data = group_rows_by_color(excel_file_path)

def view_database_records(db_name='my_project_data.db', max_rows=50):
    """
    View all records from the database in a more readable format.
    
    Args:
        db_name (str): Name of the SQLite database file
        max_rows (int): Maximum number of rows to display per group
    """
    try:
        conn = sqlite3.connect(db_name)
        conn.row_factory = sqlite3.Row  # This allows column access by name
        cursor = conn.cursor()
        
        # Get all table names
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
        tables = cursor.fetchall()
        
        for table in tables:
            table_name = table[0]
            print(f"\n{'='*80}")
            print(f"TABLE: {table_name.upper()}")
            print(f"{'='*80}")
            
            # Get column info
            cursor.execute(f"PRAGMA table_info({table_name})")
            columns = [col[1] for col in cursor.fetchall()]
            
            # Get all rows
            cursor.execute(f"SELECT * FROM {table_name}")
            rows = cursor.fetchall()
            
            if not rows:
                print("No records found.")
                continue
                
            # Group data by group_id if it exists
            if 'group_id' in columns:
                groups = {}
                for row in rows:
                    group_id = row['group_id']
                    if group_id not in groups:
                        groups[group_id] = []
                    groups[group_id].append(dict(row))
                
                # Display each group separately
                for group_id, group_rows in groups.items():
                    print(f"\n{'*' * 30} Group {group_id} {'*' * 30}")
                    
                    # Display column headers
                    header = " | ".join(f"{col:15}" for col in columns if col not in ['group_id', 'row_in_group_id'])
                    print(f"\n{' '*3}{header}")
                    print("-" * len(header) * 2)
                    
                    # Display rows
                    for i, row in enumerate(group_rows[:max_rows]):
                        if i > 0 and i % 5 == 0 and i < len(group_rows) - 1:
                            print(f"{' '*3}... {len(group_rows) - i} more rows in group ...")
                            break
                        row_values = [str(row.get(col, ''))[:15].ljust(15) for col in columns 
                                   if col not in ['group_id', 'row_in_group_id']]
                        print(f"{row['row_in_group_id']:2} | {' | '.join(row_values)}")
                    
                    if len(group_rows) > max_rows:
                        print(f"\nShowing first {max_rows} of {len(group_rows)} rows in group")
                    
            else:
                # Display as a simple table if no group_id
                print(f"\n{' | '.join(columns)}\n{'-' * 50}")
                for i, row in enumerate(rows[:max_rows]):
                    if i > 0 and i % 10 == 0 and i < len(rows) - 1:
                        print(f"... {len(rows) - i} more rows ...")
                        break
                    print(" | ".join(str(value)[:30].ljust(30) for value in row))
                
                if len(rows) > max_rows:
                    print(f"\nShowing first {max_rows} of {len(rows)} rows")
                    
            print(f"\nTotal rows in {table_name}: {len(rows)}")
            
    except sqlite3.Error as e:
        print(f"Database error: {e}")
    except Exception as e:
        print(f"An error occurred: {e}")
    finally:
        if conn:
            conn.close()
                



# --- How to use the script ---
if __name__ == "__main__":
    # First, process the Excel file and create the database
    excel_file_path = 'c:/Users/Todd.SFC/CascadeProjects/data_importer/Buff.xlsx'  # Update this to your Excel file path
    my_grouped_data = group_rows_by_color(excel_file_path)
    
    if my_grouped_data:
        db_name = 'my_project_data.db'
        create_and_insert_data_to_db(my_grouped_data, db_name)
        
        # Then view the records
        print("\nViewing database records:")
        view_database_records(db_name)
    else:
        print("No data to process. Please check your Excel file path and contents.")
#     print(row)
# conn.close()
import tkinter as tk
from tkinter import ttk
import sqlite3

def create_gui(db_name='my_project_data.db'):
    """
    Creates a tkinter GUI to view SQLite database records with group selection.
    
    Features:
    - Dropdown to select a specific group
    - Button to show all groups
    - Treeview to display records
    - Auto-refresh capability
    
    Args:
        db_name (str): Name of the SQLite database file
    """
    def refresh_data():
        """Refresh the data from the database and update the UI"""
        # Clear existing items in the tree
        for item in tree.get_children():
            tree.delete(item)
        
        try:
            # Check if database file exists
            db_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), '..', db_name)
            if not os.path.exists(db_path):
                error_msg = f"Database file not found: {db_path}"
                print(error_msg)
                status_label.config(text=error_msg, fg='red')
                return
                
            print(f"Connecting to database: {db_path}")
            conn = sqlite3.connect(db_path)
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            
            # Check if table exists
            table_name = 'grouped_data'
            print(f"Checking for table: {table_name}")
            cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
            all_tables = cursor.fetchall()
            print(f"All tables in database: {all_tables}")
            
            cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name=?", (table_name,))
            if not cursor.fetchone():
                error_msg = f"Table '{table_name}' not found in database. Available tables: {[t[0] for t in all_tables]}"
                print(error_msg)
                status_label.config(text=error_msg, fg='red')
                conn.close()
                return
            
            print(f"Getting table info for: {table_name}")
            cursor.execute(f"PRAGMA table_info({table_name})")
            columns_info = cursor.fetchall()
            print(f"Table columns: {columns_info}")
            columns = [col[1] for col in columns_info]
            print(f"Extracted column names: {columns}")
            
            # Configure tree columns if not already set
            if not tree['columns'] or set(tree['columns']) != set(columns):
                # Clear existing columns
                for col in tree['columns']:
                    tree.heading(col, text='')
                    tree.column(col, width=0, stretch=tk.NO)
                
                # Set new columns
                tree['columns'] = columns
                tree.heading('#0', text='Group')
                tree.column('#0', width=150, minwidth=100, stretch=tk.NO)
                
                for col in columns:
                    tree.heading(col, text=col)
                    tree.column(col, width=100, minwidth=50, stretch=tk.YES)
            
            # Get all rows
            print("Fetching all rows from the table...")
            cursor.execute(f"SELECT * FROM {table_name} ORDER BY group_id, row_in_group_id")
            rows = cursor.fetchall()
            print(f"Fetched {len(rows)} rows from the database")
            
            if not rows:
                print(f"No data found in table '{table_name}'.")
                status_label.config(text=f"No data found in table '{table_name}'", fg='orange')
                conn.close()
                return
            
            # Group rows by group_id
            global all_groups
            all_groups = {}
            for row in rows:
                row_dict = dict(row)  # Convert to dict for easier access
                group_id = row_dict.get('group_id')
                if group_id is not None:
                    if group_id not in all_groups:
                        all_groups[group_id] = []
                    all_groups[group_id].append(row_dict)
            
            print(f"Grouped data into {len(all_groups)} groups")
            
            # Update dropdown with all group IDs
            group_ids = sorted(all_groups.keys(), key=lambda x: int(x) if str(x).isdigit() else float('inf'))
            current_selection = group_selector.get()
            group_selector['values'] = group_ids
            
            # Try to maintain the current selection if it still exists
            if current_selection in group_ids:
                group_selector.set(current_selection)
                show_only_group(current_selection)
            elif group_ids:
                group_selector.set(str(group_ids[0]))
                show_only_group(group_ids[0])
            
            status_label.config(text=f"Loaded {len(rows)} records in {len(group_ids)} groups", fg='green')
            conn.close()
            
        except sqlite3.Error as e:
            error_msg = f"Database error: {e}"
            print(error_msg)
            status_label.config(text=error_msg, fg='red')
        except Exception as e:
            error_msg = f"Unexpected error: {str(e)}"
            print(error_msg)
            status_label.config(text=error_msg, fg='red')
    
    def on_group_selected(event):
        """Handler for group selection change"""
        selected_group = group_selector.get()
        if selected_group:
            # Convert to int if it's a digit, otherwise keep as string
            try:
                group_id = int(selected_group) if selected_group.isdigit() else selected_group
                show_only_group(group_id)
            except Exception as e:
                error_msg = f"Error selecting group: {str(e)}"
                print(error_msg)
                status_label.config(text=error_msg, fg='red')
    
    def show_only_group(group_id):
        """Display only the records from the selected group"""
        # Clear existing items in the tree
        for item in tree.get_children():
            tree.delete(item)
        
        print(f"Attempting to show group: {group_id} (type: {type(group_id)})")
        print(f"Available groups: {list(all_groups.keys())}")
        
        # Try to find a matching group ID (handling both string and int types)
        matching_group_id = None
        for gid in all_groups.keys():
            if str(gid) == str(group_id):
                matching_group_id = gid
                break
        
        if matching_group_id is not None:
            print(f"Found matching group: {matching_group_id} (type: {type(matching_group_id)})")
            # Insert the selected group's data
            group_node = tree.insert('', tk.END, text=f"Group {matching_group_id}", values=[''] * len(tree['columns']))
            for row in all_groups[matching_group_id]:
                # Get values in the correct order based on tree columns
                values = [str(row.get(col, '')) for col in tree['columns']]
                tree.insert(group_node, tk.END, values=values)
            
            # Expand the group
            tree.item(group_node, open=True)
            status_label.config(text=f"Showing group: {matching_group_id}", fg='green')
        else:
            error_msg = f"Group {group_id} not found in available groups"
            print(error_msg)
            status_label.config(text=error_msg, fg='red')
    
    def show_all_groups():
        """Display all groups in the treeview"""
        # Clear existing items in the tree
        for item in tree.get_children():
            tree.delete(item)
        
        if not all_groups:
            status_label.config(text="No groups to display", fg='orange')
            return
        
        # Insert all groups
        for group_id, group_rows in all_groups.items():
            group_node = tree.insert('', tk.END, text=f"Group {group_id}", values=[''] * len(tree['columns']))
            for row in group_rows:
                # Get values in the correct order based on tree columns and ensure they're strings
                values = [str(row.get(col, '')) for col in tree['columns']]
                tree.insert(group_node, tk.END, values=values)
            # Expand all groups
            tree.item(group_node, open=True)
        
        status_label.config(text=f"Showing all {len(all_groups)} groups", fg='green')
    
    # Create main window
    root = tk.Tk()
    root.title("Database Group Viewer")
    root.geometry("1200x800")
    
    # Configure grid weights
    root.grid_rowconfigure(1, weight=1)
    root.grid_columnconfigure(0, weight=1)
    
    # Create controls frame
    controls_frame = tk.Frame(root, padx=5, pady=5)
    controls_frame.grid(row=0, column=0, sticky='ew')
    controls_frame.grid_columnconfigure(1, weight=1)
    
    # Database file label
    db_label = tk.Label(controls_frame, text=f"Database:")
    db_label.grid(row=0, column=0, padx=5, pady=5, sticky='w')
    
    db_path_label = tk.Label(controls_frame, text=db_name, relief='sunken', padx=5, pady=2, width=50, anchor='w')
    db_path_label.grid(row=0, column=1, padx=5, pady=5, sticky='ew')
    
    # Group selection
    group_label = tk.Label(controls_frame, text="Select Group:")
    group_label.grid(row=0, column=2, padx=5, pady=5, sticky='e')
    
    group_selector = ttk.Combobox(controls_frame, state="readonly", width=20)
    group_selector.grid(row=0, column=3, padx=5, pady=5, sticky='w')
    group_selector.bind("<<ComboboxSelected>>", on_group_selected)
    
    # Buttons frame
    button_frame = tk.Frame(controls_frame)
    button_frame.grid(row=0, column=4, padx=10, pady=5, sticky='e')
    
    # Refresh button
    refresh_icon = tk.PhotoImage(width=1, height=1)  # Placeholder for an actual icon
    refresh_button = tk.Button(button_frame, text="🔄 Refresh", command=refresh_data,
                             padx=10, pady=2, compound='left')
    refresh_button.pack(side=tk.LEFT, padx=2)
    
    # Show all groups button
    show_all_button = tk.Button(button_frame, text="Show All Groups", command=show_all_groups,
                              padx=10, pady=2)
    show_all_button.pack(side=tk.LEFT, padx=2)
    
    # Create treeview with scrollbars
    tree_frame = tk.Frame(root)
    tree_frame.grid(row=1, column=0, sticky='nsew', padx=5, pady=(0, 5))
    
    # Vertical scrollbar
    vsb = ttk.Scrollbar(tree_frame, orient="vertical")
    vsb.pack(side='right', fill='y')
    
    # Horizontal scrollbar
    hsb = ttk.Scrollbar(tree_frame, orient="horizontal")
    hsb.pack(side='bottom', fill='x')
    
    # Create treeview
    tree = ttk.Treeview(tree_frame, yscrollcommand=vsb.set, xscrollcommand=hsb.set, selectmode='browse')
    tree.pack(expand=True, fill='both')
    
    # Configure scrollbars
    vsb.config(command=tree.yview)
    hsb.config(command=tree.xview)
    
    # Status bar
    status_frame = tk.Frame(root, height=20, bd=1, relief=tk.SUNKEN)
    status_frame.grid(row=2, column=0, sticky='ew')
    status_frame.grid_columnconfigure(0, weight=1)
    
    status_label = tk.Label(status_frame, text="Ready", anchor='w')
    status_label.pack(side=tk.LEFT, padx=5)
    
    # Initial data load
    global all_groups
    all_groups = {}
    refresh_data()
    
    # Make the window resizable
    root.minsize(800, 600)
    
    root.mainloop()

if __name__ == "__main__":
    create_gui('my_project_data.db')