Distilling the "Doozy": Achieving Mechanical Advantage in Code Refactoring with AI

import pandas as pd
from pathlib import Path
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Font, Alignment, Border, Side
from openpyxl.formatting.rule import ColorScaleRule
from openpyxl.worksheet.table import Table, TableStyleInfo
from openpyxl.utils import get_column_letter
import re # Needed for is_safe_url
import validators # Need to import validators for URL check

print(f"🎨 Applying Excel Formatting to all data tabs in {xl_file.name} (third pass)...")

# NOTE: This cell assumes 'xl_file', 'competitors', 'semrush_lookup', 'has_botify'
#       'TARGET_COMPETITOR_COL' (the verified column name) are defined in previous cells.

# --- REQUIRED SUPPORT FUNCTIONS (Surgically Ported/Defined) ---

def create_column_mapping(sheet):
    """Creates a dictionary mapping header names to column letters."""
    mapping = {}
    for col_idx, column_cell in enumerate(sheet[1], 1): # Assumes headers are in row 1
        column_letter = get_column_letter(col_idx)
        mapping[str(column_cell.value)] = column_letter
    return mapping

def apply_fill_to_column_labels(sheet, column_mapping, columns_list, fill):
    """Applies a fill color to the header cells of specified columns."""
    for column_name in columns_list:
        column_letter = column_mapping.get(column_name)
        if column_letter:
            cell = sheet[f"{column_letter}1"]
            cell.fill = fill

def find_last_data_row(sheet, keyword_column_letter):
    """Finds the last row containing data in a specific column (e.g., 'Keyword')."""
    if not keyword_column_letter: # Handle case where keyword column might be missing
        return sheet.max_row

    last_row = sheet.max_row
    # Iterate backwards from the max row
    while last_row > 1 and sheet[f"{keyword_column_letter}{last_row}"].value in [None, "", " "]:
        last_row -= 1
    return last_row

def apply_conditional_formatting(sheet, column_mapping, last_row, conditionals_descending, conditionals_ascending, rule_desc, rule_asc):
    """Applies color scale conditional formatting to specified columns."""
    for label in conditionals_descending + conditionals_ascending:
        column_letter = column_mapping.get(label)
        if column_letter and last_row > 1: # Ensure there is data to format
            range_string = f'{column_letter}2:{column_letter}{last_row}'
            rule = rule_desc if label in conditionals_descending else rule_asc
            try:
                sheet.conditional_formatting.add(range_string, rule)
            except Exception as e:
                print(f"⚠️ Failed to apply conditional formatting for {label}: {e}")

def is_safe_url(url):
    """ Check if the given string is a valid URL using the validators library. """
    if not isinstance(url, str):
        return False
    # Use validators library for robust URL check
    return validators.url(url)

# Color schemes and patterns
green = '33FF33'
client_color = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid') # Yellow
competitor_color = PatternFill(start_color='EEECE2', end_color='EEECE2', fill_type='solid') # Light Gray
semrush_color = PatternFill(start_color='FAEADB', end_color='FAEADB', fill_type='solid') # Light Orange
semrush_opportunity_color = PatternFill(start_color='F1C196', end_color='F1C196', fill_type='solid') # Darker Orange
botify_color = PatternFill(start_color='EADFF2', end_color='EADFF2', fill_type='solid') # Light Purple
botify_opportunity_color = PatternFill(start_color='AEA1C4', end_color='AEA1C4', fill_type='solid') # Darker Purple
color_scale_rule_desc = ColorScaleRule(start_type='min', start_color='FFFFFF', end_type='max', end_color=green) # White to Green (Higher is Better)
color_scale_rule_asc = ColorScaleRule(start_type='min', start_color=green, end_type='max', end_color='FFFFFF') # Green to White (Lower is Better)

# Create a border style (Subtle hair lines, thin bottom for headers)
thin_border = Border(left=Side(style='hair'), right=Side(style='hair'), top=Side(style='hair'), bottom=Side(style='thin'))

# Commonly reused column widths
tiny_width = 11
small_width = 15
medium_width = 20
description_width = 50
url_width = 70 # Adjusted slightly down from 100 for better viewability

# Define column widths (Verbatim)
column_widths = {
    'Keyword': 40, 'Search Volume': small_width, 'Number of Words': tiny_width,
    'Keyword Group (Experimental)': small_width, 'Competitors Positioning': tiny_width,
    'CPC': tiny_width, 'Keyword Difficulty': tiny_width, 'Competition': tiny_width,

## Bug Hunt: Uncovering the Incomplete `loop_list`
    'Depth': tiny_width, 'No. of Keywords': tiny_width,
    'No. of Impressions excluding anonymized queries': small_width,
    'No. of Clicks excluding anonymized queries': small_width,
    'No. of Missed Clicks excluding anonymized queries': small_width,
    'Avg. URL CTR excluding anonymized queries': tiny_width,
    'Avg. URL Position excluding anonymized queries': tiny_width,
    'No. of Keywords for the URL To Achieve 90% Audience': tiny_width,
    'Raw Internal Pagerank': small_width, 'Internal Pagerank': tiny_width,
    'Internal Pagerank Position': tiny_width, 'No. of Unique Inlinks': tiny_width,
    'Title': description_width, 'Meta Description': description_width,
    'Timestamp': 12, 'SERP Features by Keyword': description_width,
    'Keyword Intents': medium_width, 'Position Type': small_width,
    'Number of Results': medium_width, 'Competitor URL': url_width,
    'Client URL': url_width, # This gets renamed later
    # Normalized/Score columns
    'Normalized CPC': tiny_width, 'Normalized Keyword Difficulty': tiny_width,
    'Normalized Raw Internal Pagerank': tiny_width, 'Normalized Search Volume': tiny_width,
    'Normalized Search Position': tiny_width, 'Normalized Missed Clicks': tiny_width,
    'Combined Score': tiny_width
}

# Commonly used number formats (Verbatim)
int_fmt = '0'
comma_fmt = '#,##0'
pct_fmt = '0.00'
date_fmt = 'yyyy-mm-dd' # Added for Timestamp clarity

# Define number formats (Added Timestamp)
number_formats = {
    'Search Volume': comma_fmt, 'Number of Words': int_fmt, 'CPC': pct_fmt,
    'Keyword Difficulty': int_fmt, 'Competition': pct_fmt, 'Depth': int_fmt,
    'No. of Keywords': comma_fmt, 'No. of Impressions excluding anonymized queries': comma_fmt,
    'No. of Clicks excluding anonymized queries': comma_fmt,
    'No. of Missed Clicks excluding anonymized queries': comma_fmt,
    'Avg. URL CTR excluding anonymized queries': pct_fmt,
    'Avg. URL Position excluding anonymized queries': '0.0',
    'No. of Keywords for the URL To Achieve 90% Audience': comma_fmt,
    'Raw Internal Pagerank': '0.0000000', 'Internal Pagerank': pct_fmt,
    'Internal Pagerank Position': int_fmt, 'No. of Unique Inlinks': comma_fmt,
    'Number of Results': comma_fmt, 'Timestamp': date_fmt,
    # Apply comma format to positioning and scores for consistency
    'Competitors Positioning': int_fmt, 'Normalized CPC': pct_fmt,
    'Normalized Keyword Difficulty': pct_fmt, 'Normalized Raw Internal Pagerank': pct_fmt,
    'Normalized Search Volume': pct_fmt, 'Normalized Search Position': pct_fmt,
    'Normalized Missed Clicks': pct_fmt, 'Combined Score': '0.00'
}

# --- DEFINE COLUMN GROUPS FOR COLORING (Verbatim, adapted for known columns) ---
# Higher Numbers More Green (Descending is better)
conditionals_descending = [
    'Search Volume', 'CPC', 'Competition', # Removed Traffic metrics as they were dropped
    'Avg. URL CTR excluding anonymized queries',
    'No. of Missed Clicks excluding anonymized queries', 'Combined Score',
    'No. of Unique Inlinks' # Added Inlinks (usually higher is better contextually)
]
# Lower Numbers More Green (Ascending is better)
conditionals_ascending = [
    'Keyword Difficulty', 'Raw Internal Pagerank', 'Internal Pagerank',
    'Internal Pagerank Position', 'Avg. URL Position excluding anonymized queries', 'Depth',
    TARGET_COMPETITOR_COL # Add the client's position column dynamically
] + [col for col in competitors if col != TARGET_COMPETITOR_COL] # Add other competitor position columns

# SEMRush Data Columns
semrush_columns = [
    'Keyword', 'Search Volume', 'CPC', 'Keyword Difficulty', 'Competition',
    'SERP Features by Keyword', 'Keyword Intents', 'Position Type',
    'Number of Results', 'Timestamp', 'Competitor URL', 'Client URL' # Includes Client URL before rename
]
# Botify Data Columns (Ensure these match final DataFrame after merge)
botify_columns = [
    'Depth', 'No. of Keywords', 'No. of Impressions excluding anonymized queries',
    'No. of Clicks excluding anonymized queries', 'No. of Missed Clicks excluding anonymized queries',
    'Avg. URL CTR excluding anonymized queries', 'Avg. URL Position excluding anonymized queries',
    'No. of Keywords for the URL To Achieve 90% Audience', 'Raw Internal Pagerank',
    'Internal Pagerank', 'Internal Pagerank Position', 'No. of Unique Inlinks',
    'Title', 'Meta Description' # Changed from API name
]
# Columns which get bigger header fonts
bigger_font_headers = [
    "Keyword", "Search Volume", "Title", "Meta Description",
    "Competitor URL", "Client URL", "SERP Features by Keyword"
]
# Columns which get darker Botify color
botify_opportunity_columns = [
    'Internal Pagerank', 'No. of Unique Inlinks',
    'No. of Missed Clicks excluding anonymized queries',
    'Normalized Raw Internal Pagerank', 'Normalized Missed Clicks'
]
# Columns which get darker SEMRush color
semrush_opportunity_columns = [
    'CPC', 'Keyword Difficulty', 'Normalized CPC', 'Normalized Keyword Difficulty',
    'Normalized Search Volume', 'Normalized Search Position', 'Combined Score' # Added Combined Score here
]


# --- APPLY FORMATTING TO EXCEL FILE ---
try:
    wb = load_workbook(xl_file)

    # --- UPDATED: Get all sheet names EXCEPT the diagnostics sheet ---
    sheets_to_format = [name for name in wb.sheetnames if name != "Filter Diagnostics"]
    # -----------------------------------------------------------------

    if not sheets_to_format:
         print("⚠️ No data sheets found in the Excel file to format. Skipping formatting.")

    for sheet_name in sheets_to_format:
        print(f"- Formatting '{sheet_name}' tab...")
        sheet = wb[sheet_name]
        column_mapping = create_column_mapping(sheet)

        # Determine the last row with data based on the 'Keyword' column
        keyword_col_letter = column_mapping.get("Keyword")
        # Add a check in case a sheet somehow doesn't have a Keyword column
        if not keyword_col_letter:
             print(f"  Skipping sheet '{sheet_name}': Cannot find 'Keyword' column for formatting reference.")
             continue
             
        last_row = find_last_data_row(sheet, keyword_col_letter)
        
        # --- Apply Formatting ---

        # 1. Fill client column (using TARGET_COMPETITOR_COL identified earlier)
        client_column_letter = column_mapping.get(TARGET_COMPETITOR_COL)
        if client_column_letter:
            for row in range(1, last_row + 1):
                cell = sheet[f"{client_column_letter}{row}"]
                cell.fill = client_color
                if row == 1: cell.font = Font(bold=True) # Bold header

        # 2. Fill Header Backgrounds
        apply_fill_to_column_labels(sheet, column_mapping, semrush_columns, semrush_color)
        apply_fill_to_column_labels(sheet, column_mapping, botify_columns, botify_color)
        # Apply competitor color only to competitor columns *present* in this sheet
        present_competitors = [c for c in competitors if c in column_mapping and c != TARGET_COMPETITOR_COL]
        apply_fill_to_column_labels(sheet, column_mapping, present_competitors, competitor_color)
        apply_fill_to_column_labels(sheet, column_mapping, botify_opportunity_columns, botify_opportunity_color)
        apply_fill_to_column_labels(sheet, column_mapping, semrush_opportunity_columns, semrush_opportunity_color)

        # 3. Header Styling (Alignment, Font, Border)
        header_font = Font(bold=True)
        header_align = Alignment(horizontal='center', vertical='center', wrap_text=True)
        for header, col_letter in column_mapping.items():
            cell = sheet[f"{col_letter}1"]
            cell.alignment = header_align
            cell.font = header_font
            cell.border = thin_border # Apply border to header
            if header in bigger_font_headers:
                 cell.font = Font(size=14, bold=True) # Slightly smaller than original for balance

        # 4. Hyperlinks (Competitor URL, Client URL)
        for col_label in ["Competitor URL", "Client URL"]:
            col_letter = column_mapping.get(col_label)
            if col_letter:
                for row in range(2, last_row + 1):
                    cell = sheet[f"{col_letter}{row}"]
                    url = cell.value
                    if url and is_safe_url(url) and not str(url).startswith('=HYPERLINK'):
                        # Truncate displayed URL if very long, keep full URL in link
                        display_text = url if len(url) <= 80 else url[:77] + "..."
                        cell.value = f'=HYPERLINK("{url}", "{display_text}")'
                        cell.font = Font(color="0000FF", underline="single")
                        cell.alignment = Alignment(vertical='top', wrap_text=False) # Prevent wrap for URLs


        # 5. Rotate Competitor Headers & Set Width
        competitor_header_align = Alignment(vertical='bottom', textRotation=90, horizontal='center')
        for competitor_col_name in competitors:
            col_letter = column_mapping.get(competitor_col_name)
            if col_letter:
                cell = sheet[f"{col_letter}1"]
                cell.alignment = competitor_header_align
                sheet.column_dimensions[col_letter].width = 4

        # 6. Apply Column Widths (with Global Adjustment)
        for label, width in column_widths.items():
            column_letter = column_mapping.get(label)
            if column_letter:
                # Apply the global width adjustment multiplier
                sheet.column_dimensions[column_letter].width = width * GLOBAL_WIDTH_ADJUSTMENT

        # 7. Apply Number Formats
        for label, format_code in number_formats.items():
            column_letter = column_mapping.get(label)
            if column_letter:
                for row in range(2, last_row + 1):
                    cell = sheet[f"{column_letter}{row}"]
                    # Apply only if cell is not empty, prevents formatting issues
                    if cell.value is not None:
                        cell.number_format = format_code

        # 8. Apply Conditional Formatting (Using the combined rules)
        apply_conditional_formatting(sheet, column_mapping, last_row, conditionals_descending, conditionals_ascending, color_scale_rule_desc, color_scale_rule_asc)

        # 9. Rename 'Client URL' Header Dynamically
        client_url_column_letter = column_mapping.get("Client URL")
        if client_url_column_letter:
            header_cell = sheet[f"{client_url_column_letter}1"]
            header_cell.value = f"{TARGET_COMPETITOR_COL} URL" # Use the canonical name

        # 10. Data Cell Alignment (Wrap text, top align)
        data_align = Alignment(wrap_text=False, vertical='top')
        url_columns = [column_mapping.get("Competitor URL"), column_mapping.get("Client URL")] # Get letters before loop
        for row_idx in range(2, last_row + 1):
            for col_idx in range(1, sheet.max_column + 1):
                cell = sheet.cell(row=row_idx, column=col_idx)
                col_letter = get_column_letter(col_idx)
                # Apply default alignment, skip URL columns handled earlier
                if col_letter not in url_columns:
                    cell.alignment = data_align


        # 11. Header Row Height & Freeze Panes
        # Use the explicit configuration variable for header height
        sheet.row_dimensions[1].height = locals().get('max_length', 15) * 9 if 'max_length' in locals() else 100
        sheet.freeze_panes = 'C2' # Freeze panes more appropriately after Keyword/Volume

        # 12. Apply AutoFilter
        max_col_letter = get_column_letter(sheet.max_column)
        if last_row > 0: # Ensure there are rows to filter
             sheet.auto_filter.ref = f"A1:{max_col_letter}{last_row}"

        # 13. (Optional but recommended) Add Table for banded rows (replaces manual banding)
        if last_row > 0: # Ensure there is data for the table
            table_range = f"A1:{max_col_letter}{last_row}"
            table_name = f"DataTable_{re.sub(r'[^A-Za-z0-9_]', '', sheet_name)}" # Sanitize sheet name for table name

            # --- CORRECTED TABLE CHECK ---
            # Defensively check if items in sheet._tables have a .name attribute
            existing_table_names = [t.name for t in sheet._tables if hasattr(t, 'name')]
            if table_name not in existing_table_names:
            # --- END CORRECTION ---
                 tab = Table(displayName=table_name, ref=table_range)
                 style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False,
                                       showLastColumn=False, showRowStripes=True, showColumnStripes=False)
                 tab.tableStyleInfo = style
                 try:
                      sheet.add_table(tab)
                 except ValueError as ve:
                      print(f"  Note: Could not add Excel Table '{table_name}' to sheet '{sheet_name}'. Maybe overlaps existing table? Error: {ve}")
            # Optional: Add an else here if you want to log that the table already exists
            # else:
            #    print(f"  Skipping table creation: Table '{table_name}' already exists in sheet '{sheet_name}'.")

    # Save the workbook with all formatting applied
    wb.save(xl_file)
    print(f"✅ Formatting applied to all data tabs and saved to {xl_file.name}")

except FileNotFoundError:
    print(f"❌ Error: Excel file not found at {xl_file}. Cannot apply formatting.")
except KeyError as e:
     print(f"❌ Error during formatting: A required column key was not found: {e}. Check DataFrame structure.")
     # Safely attempt to print mapping if it exists
     if 'column_mapping' in locals(): print(f"   Column Mapping: {column_mapping}")
except Exception as e:
    print(f"❌ An unexpected error occurred during Excel formatting: {e}")
# Use a lambda function to call the portable _open_folder function on click
button.on_click(lambda b: _open_folder(str(deliverables_dir)))
display(button)

# In Notebooks/gap_analyzer_sauce.py
import pandas as pd
from pathlib import Path
import re
import json
from pipulate import pip # Ensure pip is imported
import numpy as np # Ensure numpy is imported
from io import StringIO # Ensure StringIO is imported
import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Font, Alignment, Border, Side
from openpyxl.formatting.rule import ColorScaleRule
from openpyxl.worksheet.table import Table, TableStyleInfo
from openpyxl.utils import get_column_letter
import validators # For URL validation
import ipywidgets as widgets # For the button
from IPython.display import display # For the button

# (Keep all previously added functions)
# ...

# --- Private Helper Functions for Excel Formatting ---

def _create_column_mapping(sheet):
    """Creates a dictionary mapping header names to column letters."""
    mapping = {}
    for col_idx, column_cell in enumerate(sheet[1], 1): # Assumes headers are in row 1
        column_letter = get_column_letter(col_idx)
        mapping[str(column_cell.value)] = column_letter
    return mapping

def _apply_fill_to_column_labels(sheet, column_mapping, columns_list, fill):
    """Applies a fill color to the header cells of specified columns."""
    for column_name in columns_list:
        column_letter = column_mapping.get(column_name)
        if column_letter:
            cell = sheet[f"{column_letter}1"]
            cell.fill = fill

def _find_last_data_row(sheet, keyword_column_letter):
    """Finds the last row containing data in a specific column (e.g., 'Keyword')."""
    if not keyword_column_letter: # Handle case where keyword column might be missing
        return sheet.max_row
    last_row = sheet.max_row
    # Iterate backwards from the max row
    while last_row > 1 and sheet[f"{keyword_column_letter}{last_row}"].value in [None, "", " "]:
        last_row -= 1
    return last_row

def _apply_conditional_formatting(sheet, column_mapping, last_row, conditionals_descending, conditionals_ascending, rule_desc, rule_asc):
    """Applies color scale conditional formatting to specified columns."""
    for label in conditionals_descending + conditionals_ascending:
        column_letter = column_mapping.get(label)
        if column_letter and last_row > 1: # Ensure there is data to format
            range_string = f'{column_letter}2:{column_letter}{last_row}'
            rule = rule_desc if label in conditionals_descending else rule_asc
            try:
                sheet.conditional_formatting.add(range_string, rule)
            except Exception as e:
                print(f"  ⚠️ Failed to apply conditional formatting for {label}: {e}")

def _is_safe_url(url):
    """ Check if the given string is a valid URL using the validators library. """
    if not isinstance(url, str):
        return False
    return validators.url(url) is True # Explicitly check for True

# --- Main Formatting Function ---

def apply_excel_formatting(
    job: str,
    xl_file: Path,
    competitors: list,
    semrush_lookup: str,
    TARGET_COMPETITOR_COL: str,
    has_botify: bool,
    GLOBAL_WIDTH_ADJUSTMENT: float,
    button: widgets.Button
):
    """
    Applies all "painterly" openpyxl formatting to the generated Excel file.
    This is the "third pass" that makes the deliverable client-ready.

    Args:
        job (str): The current Pipulate job ID.
        xl_file (Path): The Path object for the Excel file.

## The First Pass: Encapsulation and Pip State
        competitors (list): The list of competitor column names.
        semrush_lookup (str): The client's clean domain (e.g., 'example.com').
        TARGET_COMPETITOR_COL (str): The canonical client column name (e.g., 'example.com/').
        has_botify (bool): Flag indicating if Botify data is present.
        GLOBAL_WIDTH_ADJUSTMENT (float): Multiplier for column widths.
        button (widgets.Button): The "Open Folder" button object.

    Returns:
        widgets.Button: The same button object, for re-display.
    """
    if not xl_file or not Path(xl_file).exists():
        print(f"❌ Error: Excel file not found at '{xl_file}'. Cannot apply formatting.")
        return button # Return the original button

    print(f"🎨 Applying Excel Formatting to all data tabs in {xl_file.name} (third pass)...")
    
    # --- Formatting Definitions (Moved from Notebook) ---
    green = '33FF33'
    client_color = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid') # Yellow
    competitor_color = PatternFill(start_color='EEECE2', end_color='EEECE2', fill_type='solid') # Light Gray
    semrush_color = PatternFill(start_color='FAEADB', end_color='FAEADB', fill_type='solid') # Light Orange
    semrush_opportunity_color = PatternFill(start_color='F1C196', end_color='F1C196', fill_type='solid') # Darker Orange
    botify_color = PatternFill(start_color='EADFF2', end_color='EADFF2', fill_type='solid') # Light Purple
    botify_opportunity_color = PatternFill(start_color='AEA1C4', end_color='AEA1C4', fill_type='solid') # Darker Purple
    color_scale_rule_desc = ColorScaleRule(start_type='min', start_color='FFFFFF', end_type='max', end_color=green)
    color_scale_rule_asc = ColorScaleRule(start_type='min', start_color=green, end_type='max', end_color='FFFFFF')
    thin_border = Border(left=Side(style='hair'), right=Side(style='hair'), top=Side(style='hair'), bottom=Side(style='thin'))
    tiny_width, small_width, medium_width, description_width, url_width = 11, 15, 20, 50, 70

    column_widths = {
        'Keyword': 40, 'Search Volume': small_width, 'Number of Words': tiny_width,
        'Keyword Group (Experimental)': small_width, 'Competitors Positioning': tiny_width,
        'CPC': tiny_width, 'Keyword Difficulty': tiny_width, 'Competition': tiny_width,
        'Depth': tiny_width, 'No. of Keywords': tiny_width,
        'No. of Impressions excluding anonymized queries': small_width,
        'No. of Clicks excluding anonymized queries': small_width,
        'No. of Missed Clicks excluding anonymized queries': small_width,
        'Avg. URL CTR excluding anonymized queries': tiny_width,
        'Avg. URL Position excluding anonymized queries': tiny_width,
        'No. of Keywords for the URL To Achieve 90% Audience': tiny_width,
        'Raw Internal Pagerank': small_width, 'Internal Pagerank': tiny_width,
        'Internal Pagerank Position': tiny_width, 'No. of Unique Inlinks': tiny_width,
        'Title': description_width, 'Meta Description': description_width,
        'Timestamp': 12, 'SERP Features by Keyword': description_width,
        'Keyword Intents': medium_width, 'Position Type': small_width,
        'Number of Results': medium_width, 'Competitor URL': url_width,
        'Client URL': url_width, # Gets renamed
        'Normalized CPC': tiny_width, 'Normalized Keyword Difficulty': tiny_width,
        'Normalized Raw Internal Pagerank': tiny_width, 'Normalized Search Volume': tiny_width,
        'Normalized Search Position': tiny_width, 'Normalized Missed Clicks': tiny_width,
        'Combined Score': tiny_width
    }
    
    int_fmt, comma_fmt, pct_fmt, date_fmt = '0', '#,##0', '0.00', 'yyyy-mm-dd'
    number_formats = {
        'Search Volume': comma_fmt, 'Number of Words': int_fmt, 'CPC': pct_fmt,
        'Keyword Difficulty': int_fmt, 'Competition': pct_fmt, 'Depth': int_fmt,
        'No. of Keywords': comma_fmt, 'No. of Impressions excluding anonymized queries': comma_fmt,
        'No. of Clicks excluding anonymized queries': comma_fmt,
        'No. of Missed Clicks excluding anonymized queries': comma_fmt,
        'Avg. URL CTR excluding anonymized queries': pct_fmt,
        'Avg. URL Position excluding anonymized queries': '0.0',
        'No. of Keywords for the URL To Achieve 90% Audience': comma_fmt,
        'Raw Internal Pagerank': '0.0000000', 'Internal Pagerank': pct_fmt,
        'Internal Pagerank Position': int_fmt, 'No. of Unique Inlinks': comma_fmt,
        'Number of Results': comma_fmt, 'Timestamp': date_fmt,
        'Competitors Positioning': int_fmt, 'Normalized CPC': pct_fmt,
        'Normalized Keyword Difficulty': pct_fmt, 'Normalized Raw Internal Pagerank': pct_fmt,
        'Normalized Search Volume': pct_fmt, 'Normalized Search Position': pct_fmt,
        'Normalized Missed Clicks': pct_fmt, 'Combined Score': '0.00'
    }

    conditionals_descending = ['Search Volume', 'CPC', 'Competition', 'Avg. URL CTR excluding anonymized queries', 'No. of Missed Clicks excluding anonymized queries', 'Combined Score', 'No. of Unique Inlinks']
    conditionals_ascending = ['Keyword Difficulty', 'Raw Internal Pagerank', 'Internal Pagerank', 'Internal Pagerank Position', 'Avg. URL Position excluding anonymized queries', 'Depth', TARGET_COMPETITOR_COL] + [col for col in competitors if col != TARGET_COMPETITOR_COL]
    semrush_columns = ['Keyword', 'Search Volume', 'CPC', 'Keyword Difficulty', 'Competition', 'SERP Features by Keyword', 'Keyword Intents', 'Position Type', 'Number of Results', 'Timestamp', 'Competitor URL', 'Client URL']
    botify_columns = ['Depth', 'No. of Keywords', 'No. of Impressions excluding anonymized queries', 'No. of Clicks excluding anonymized queries', 'No. of Missed Clicks excluding anonymized queries', 'Avg. URL CTR excluding anonymized queries', 'Avg. URL Position excluding anonymized queries', 'No. of Keywords for the URL To Achieve 90% Audience', 'Raw Internal Pagerank', 'Internal Pagerank', 'Internal Pagerank Position', 'No. of Unique Inlinks', 'Title', 'Meta Description']
    bigger_font_headers = ["Keyword", "Search Volume", "Title", "Meta Description", "Competitor URL", "Client URL", "SERP Features by Keyword"]
    botify_opportunity_columns = ['Internal Pagerank', 'No. of Unique Inlinks', 'No. of Missed Clicks excluding anonymized queries', 'Normalized Raw Internal Pagerank', 'Normalized Missed Clicks']
    semrush_opportunity_columns = ['CPC', 'Keyword Difficulty', 'Normalized CPC', 'Normalized Keyword Difficulty', 'Normalized Search Volume', 'Normalized Search Position', 'Combined Score']
    # --- End Formatting Definitions ---


    # --- APPLY FORMATTING TO EXCEL FILE ---
    try:
        wb = load_workbook(xl_file)
        
        # Get sheet names from the loop_list *stored in pip state*
        loop_list_json = pip.get(job, 'loop_list', '[]')
        sheets_to_format = json.loads(loop_list_json)
        
        # Also load the other lists needed
        competitors_json = pip.get(job, 'competitors_list', '[]')
        competitors = json.loads(competitors_json)
        
        if not sheets_to_format:
             print("  ⚠️ No sheets found in 'loop_list' from pip state. Adding all sheets as fallback.")
             sheets_to_format = [name for name in wb.sheetnames if name != "Filter Diagnostics"]

        if not sheets_to_format:
            print("⚠️ No data sheets found in the Excel file to format. Skipping formatting.")
            return button # Return original button

        for sheet_name in sheets_to_format:
            if sheet_name not in wb.sheetnames:
                 print(f"  ⚠️ Skipping sheet '{sheet_name}': Not found in workbook.")
                 continue
                 
            print(f"  - Formatting '{sheet_name}' tab...")
            sheet = wb[sheet_name]
            column_mapping = _create_column_mapping(sheet)
            keyword_col_letter = column_mapping.get("Keyword")

            if not keyword_col_letter:
                print(f"  ⚠️ Skipping sheet '{sheet_name}': Cannot find 'Keyword' column.")
                continue
                
            last_row = _find_last_data_row(sheet, keyword_col_letter)
            
            # --- Apply All Formatting Steps ---
            
            # 1. Fill client column
            client_column_letter = column_mapping.get(TARGET_COMPETITOR_COL)
            if client_column_letter:
                for row in range(1, last_row + 1):
                    cell = sheet[f"{client_column_letter}{row}"]
                    cell.fill = client_color
                    if row == 1: cell.font = Font(bold=True)

            # 2. Fill Header Backgrounds
            _apply_fill_to_column_labels(sheet, column_mapping, semrush_columns, semrush_color)
            _apply_fill_to_column_labels(sheet, column_mapping, botify_columns, botify_color)
            present_competitors = [c for c in competitors if c in column_mapping and c != TARGET_COMPETITOR_COL]
            _apply_fill_to_column_labels(sheet, column_mapping, present_competitors, competitor_color)
            _apply_fill_to_column_labels(sheet, column_mapping, botify_opportunity_columns, botify_opportunity_color)
            _apply_fill_to_column_labels(sheet, column_mapping, semrush_opportunity_columns, semrush_opportunity_color)

            # 3. Header Styling
            header_font = Font(bold=True)
            header_align = Alignment(horizontal='center', vertical='center', wrap_text=True)
            for header, col_letter in column_mapping.items():
                cell = sheet[f"{col_letter}1"]
                cell.alignment = header_align
                cell.font = header_font
                cell.border = thin_border
                if header in bigger_font_headers:
                    cell.font = Font(size=14, bold=True)

            # 4. Hyperlinks
            for col_label in ["Competitor URL", "Client URL"]:
                col_letter = column_mapping.get(col_label)
                if col_letter:
                    for row in range(2, last_row + 1):
                        cell = sheet[f"{col_letter}{row}"]
                        url = cell.value
                        if url and _is_safe_url(url) and not str(url).startswith('=HYPERLINK'):
                            display_text = url if len(url) <= 80 else url[:77] + "..."
                            cell.value = f'=HYPERLINK("{url}", "{display_text}")'
                            cell.font = Font(color="0000FF", underline="single")
                            cell.alignment = Alignment(vertical='top', wrap_text=False)

            # 5. Rotate Competitor Headers
            competitor_header_align = Alignment(vertical='bottom', textRotation=90, horizontal='center')
            header_height = 100 # Default
            for competitor_col_name in competitors:
                col_letter = column_mapping.get(competitor_col_name)
                if col_letter:
                    cell = sheet[f"{col_letter}1"]
                    cell.alignment = competitor_header_align
                    sheet.column_dimensions[col_letter].width = 4
                    # Dynamically calculate required header height
                    header_height = max(header_height, len(competitor_col_name) * 6) # Simple heuristic
            
            # 11. Header Row Height & Freeze Panes
            sheet.row_dimensions[1].height = header_height # Apply calculated height
            sheet.freeze_panes = 'C2'


            # 6. Apply Column Widths
            for label, width in column_widths.items():
                column_letter = column_mapping.get(label)
                if column_letter:
                    sheet.column_dimensions[column_letter].width = width * GLOBAL_WIDTH_ADJUSTMENT

            # 7. Apply Number Formats
            for label, format_code in number_formats.items():
                column_letter = column_mapping.get(label)
                if column_letter:
                    for row in range(2, last_row + 1):
                        cell = sheet[f"{column_letter}{row}"]
                        if cell.value is not None:
                            cell.number_format = format_code

            # 8. Apply Conditional Formatting
            _apply_conditional_formatting(sheet, column_mapping, last_row, conditionals_descending, conditionals_ascending, color_scale_rule_desc, color_scale_rule_asc)

            # 9. Rename 'Client URL' Header
            client_url_column_letter = column_mapping.get("Client URL")
            if client_url_column_letter:
                sheet[f"{client_url_column_letter}1"].value = f"{TARGET_COMPETITOR_COL} URL"

            # 10. Data Cell Alignment
            data_align = Alignment(wrap_text=False, vertical='top')
            url_columns = [column_mapping.get("Competitor URL"), column_mapping.get("Client URL")]
            for row_idx in range(2, last_row + 1):
                for col_idx in range(1, sheet.max_column + 1):
                    cell = sheet.cell(row=row_idx, column=col_idx)
                    if get_column_letter(col_idx) not in url_columns:
                        cell.alignment = data_align

            # 12. Apply AutoFilter
            max_col_letter = get_column_letter(sheet.max_column)
            if last_row > 0:
                sheet.auto_filter.ref = f"A1:{max_col_letter}{last_row}"

            # 13. Add Table for banded rows
            if last_row > 0:
                table_range = f"A1:{max_col_letter}{last_row}"
                table_name = f"DataTable_{re.sub(r'[^A-Za-z0-9_]', '', sheet_name)}"
                existing_table_names = [t.name for t in sheet._tables if hasattr(t, 'name')]
                if table_name not in existing_table_names:
                    tab = Table(displayName=table_name, ref=table_range)
                    style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False,
                                           showLastColumn=False, showRowStripes=True, showColumnStripes=False)
                    tab.tableStyleInfo = style
                    try:
                        sheet.add_table(tab)
                    except ValueError as ve:
                        print(f"  Note: Could not add Excel Table '{table_name}'. Overlaps? {ve}")

        wb.save(xl_file)
        print(f"✅ Formatting applied to all data tabs and saved to {xl_file.name}")

    except FileNotFoundError:
        print(f"❌ Error: Excel file not found at {xl_file}. Cannot apply formatting.")
    except KeyError as e:
        print(f"❌ Error during formatting: A required column key was not found: {e}.")
        if 'column_mapping' in locals(): print(f"   Column Mapping: {column_mapping}")
    except Exception as e:
        print(f"❌ An unexpected error occurred during Excel formatting: {e}")
    
    # --- RETURN VALUE ---
    # Return the button so it can be re-displayed (handler is already attached)
    return button

# %% editable=true slideshow={"slide_type": ""}
import pandas as pd
from pathlib import Path
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Font, Alignment, Border, Side
from openpyxl.formatting.rule import ColorScaleRule
from openpyxl.worksheet.table import Table, TableStyleInfo
from openpyxl.utils import get_column_letter
import re # Needed for is_safe_url
import validators # Need to import validators for URL check
import gap_analyzer_sauce # Ensure module is imported
from IPython.display import display # Ensure display is imported

# This is the final "painterly" step.
# This one function now handles:
# 1. Loading the Excel workbook from disk.
# 2. Reading the list of sheets-to-format from pip state.
# 3. Defining all helper functions for formatting (column mapping, cell finding, etc.).
# 4. Defining all formatting rules (colors, widths, number formats, column groups).
# 5. Iterating through each sheet and applying all 13+ formatting steps.
# 6. Saving the final, polished Excel file.
# 7. Returning the "Open Folder" button for re-display.

# It receives all necessary objects from the previous cells.
button = gap_analyzer_sauce.apply_excel_formatting(
    job,
    xl_file,
    competitors,
    semrush_lookup,
    TARGET_COMPETITOR_COL,
    has_botify,
    GLOBAL_WIDTH_ADJUSTMENT,
    button
)

# Re-display the button (its on_click handler is preserved)
display(button)

# Optional verification
# print("\nVerification: Final formatting applied.")

# %% editable=true slideshow={"slide_type": ""}
import pandas as pd
from pathlib import Path
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Font, Alignment, Border, Side
from openpyxl.formatting.rule import ColorScaleRule
from openpyxl.worksheet.table import Table, TableStyleInfo
from openpyxl.utils import get_column_letter
import re # Needed for is_safe_url
import validators # Need to import validators for URL check
import gap_analyzer_sauce # Ensure module is imported
from IPython.display import display # Ensure display is imported

# This is the final "painterly" step.
# This one function now handles:
# 1. Loading the Excel workbook from disk.
# 2. Reading the list of sheets-to-format from pip state.
# 3. Defining all helper functions for formatting (column mapping, cell finding, etc.).
# 4. Defining all formatting rules (colors, widths, number formats, column groups).
# 5. Iterating through each sheet and applying all 13+ formatting steps.
# 6. Saving the final, polished Excel file.
# 7. Returning the "Open Folder" button for re-display.

# It receives all necessary objects from the previous cells.
button = gap_analyzer_sauce.apply_excel_formatting(
    job,
    xl_file,
    competitors,
    semrush_lookup,
    TARGET_COMPETITOR_COL,
    has_botify,
    GLOBAL_WIDTH_ADJUSTMENT, # This is from the config cell
    button
)

# Re-display the button (its on_click handler is preserved)
display(button)

# Optional verification
# print("\nVerification: Final formatting applied.")

# In Notebooks/gap_analyzer_sauce.py
import pandas as pd
from pathlib import Path
import re
import json
from pipulate import pip
import numpy as np
from io import StringIO
import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Font, Alignment, Border, Side
from openpyxl.formatting.rule import ColorScaleRule
from openpyxl.worksheet.table import Table, TableStyleInfo
from openpyxl.utils import get_column_letter
import validators
import ipywidgets as widgets
from IPython.display import display

# (All previously added functions remain)
# ...

# --- Main Formatting Function (CORRECTED) ---

def apply_excel_formatting(
    job: str,
    xl_file: Path,
    competitors: list,
    semrush_lookup: str,
    TARGET_COMPETITOR_COL: str,
    has_botify: bool,
    GLOBAL_WIDTH_ADJUSTMENT: float,
    button: widgets.Button
):
    """
    Applies all "painterly" openpyxl formatting to the generated Excel file.
    This is the "third pass" that makes the deliverable client-ready.
    (This version formats ALL sheets found in the workbook.)

    Args:
        job (str): The current Pipulate job ID.
        xl_file (Path): The Path object for the Excel file.
        competitors (list): The list of competitor column names.
        semrush_lookup (str): The client's clean domain (e.g., 'example.com').
        TARGET_COMPETITOR_COL (str): The canonical client column name (e.g., 'example.com/').
        has_botify (bool): Flag indicating if Botify data is present.
        GLOBAL_WIDTH_ADJUSTMENT (float): Multiplier for column widths.
        button (widgets.Button): The "Open Folder" button object.

    Returns:
        widgets.Button: The same button object, for re-display.
    """
    if not xl_file or not Path(xl_file).exists():
        print(f"❌ Error: Excel file not found at '{xl_file}'. Cannot apply formatting.")
        return button # Return the original button

    print(f"🎨 Applying Excel Formatting to all data tabs in {xl_file.name} (third pass)...")
    
    # --- Formatting Definitions (Moved from Notebook) ---
    green = '33FF33'
    client_color = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid') # Yellow
    competitor_color = PatternFill(start_color='EEECE2', end_color='EEECE2', fill_type='solid') # Light Gray
    semrush_color = PatternFill(start_color='FAEADB', end_color='FAEADB', fill_type='solid') # Light Orange
    semrush_opportunity_color = PatternFill(start_color='F1C196', end_color='F1C196', fill_type='solid') # Darker Orange
    botify_color = PatternFill(start_color='EADFF2', end_color='EADFF2', fill_type='solid') # Light Purple
    botify_opportunity_color = PatternFill(start_color='AEA1C4', end_color='AEA1C4', fill_type='solid') # Darker Purple
    color_scale_rule_desc = ColorScaleRule(start_type='min', start_color='FFFFFF', end_type='max', end_color=green)
    color_scale_rule_asc = ColorScaleRule(start_type='min', start_color=green, end_type='max', end_color='FFFFFF')
    thin_border = Border(left=Side(style='hair'), right=Side(style='hair'), top=Side(style='hair'), bottom=Side(style='thin'))
    tiny_width, small_width, medium_width, description_width, url_width = 11, 15, 20, 50, 70

    column_widths = {
        'Keyword': 40, 'Search Volume': small_width, 'Number of Words': tiny_width,
        'Keyword Group (Experimental)': small_width, 'Competitors Positioning': tiny_width,
        'CPC': tiny_width, 'Keyword Difficulty': tiny_width, 'Competition': tiny_width,
        'Depth': tiny_width, 'No. of Keywords': tiny_width,
        'No. of Impressions excluding anonymized queries': small_width,
        'No. of Clicks excluding anonymized queries': small_width,
        'No. of Missed Clicks excluding anonymized queries': small_width,
        'Avg. URL CTR excluding anonymized queries': tiny_width,
        'Avg. URL Position excluding anonymized queries': tiny_width,
        'No. of Keywords for the URL To Achieve 90% Audience': tiny_width,
        'Raw Internal Pagerank': small_width, 'Internal Pagerank': tiny_width,
        'Internal Pagerank Position': tiny_width, 'No. of Unique Inlinks': tiny_width,
        'Title': description_width, 'Meta Description': description_width,
        'Timestamp': 12, 'SERP Features by Keyword': description_width,
        'Keyword Intents': medium_width, 'Position Type': small_width,
        'Number of Results': medium_width, 'Competitor URL': url_width,
        'Client URL': url_width, # Gets renamed
        'Normalized CPC': tiny_width, 'Normalized Keyword Difficulty': tiny_width,
        'Normalized Raw Internal Pagerank': tiny_width, 'Normalized Search Volume': tiny_width,
        'Normalized Search Position': tiny_width, 'Normalized Missed Clicks': tiny_width,
        'Combined Score': tiny_width
    }
    
    int_fmt, comma_fmt, pct_fmt, date_fmt = '0', '#,##0', '0.00', 'yyyy-mm-dd'
    number_formats = {
        'Search Volume': comma_fmt, 'Number of Words': int_fmt, 'CPC': pct_fmt,
        'Keyword Difficulty': int_fmt, 'Competition': pct_fmt, 'Depth': int_fmt,
        'No. of Keywords': comma_fmt, 'No. of Impressions excluding anonymized queries': comma_fmt,
        'No. of Clicks excluding anonymized queries': comma_fmt,
        'No. of Missed Clicks excluding anonymized queries': comma_fmt,
        'Avg. URL CTR excluding anonymized queries': pct_fmt,
        'Avg. URL Position excluding anonymized queries': '0.0',
        'No. of Keywords for the URL To Achieve 90% Audience': comma_fmt,
        'Raw Internal Pagerank': '0.0000000', 'Internal Pagerank': pct_fmt,
        'Internal Pagerank Position': int_fmt, 'No. of Unique Inlinks': comma_fmt,
        'Number of Results': comma_fmt, 'Timestamp': date_fmt,
        'Competitors Positioning': int_fmt, 'Normalized CPC': pct_fmt,
        'Normalized Keyword Difficulty': pct_fmt, 'Normalized Raw Internal Pagerank': pct_fmt,
        'Normalized Search Volume': pct_fmt, 'Normalized Search Position': pct_fmt,
        'Normalized Missed Clicks': pct_fmt, 'Combined Score': '0.00'
    }

    conditionals_descending = ['Search Volume', 'CPC', 'Competition', 'Avg. URL CTR excluding anonymized queries', 'No. of Missed Clicks excluding anonymized queries', 'Combined Score', 'No. of Unique Inlinks']
    conditionals_ascending = ['Keyword Difficulty', 'Raw Internal Pagerank', 'Internal Pagerank', 'Internal Pagerank Position', 'Avg. URL Position excluding anonymized queries', 'Depth', TARGET_COMPETITOR_COL] + [col for col in competitors if col != TARGET_COMPETITOR_COL]
    semrush_columns = ['Keyword', 'Search Volume', 'CPC', 'Keyword Difficulty', 'Competition', 'SERP Features by Keyword', 'Keyword Intents', 'Position Type', 'Number of Results', 'Timestamp', 'Competitor URL', 'Client URL']
    botify_columns = ['Depth', 'No. of Keywords', 'No. of Impressions excluding anonymized queries', 'No. of Clicks excluding anonymized queries', 'No. of Missed Clicks excluding anonymized queries', 'Avg. URL CTR excluding anonymized queries', 'Avg. URL Position excluding anonymized queries', 'No. of Keywords for the URL To Achieve 90% Audience', 'Raw Internal Pagerank', 'Internal Pagerank', 'Internal Pagerank Position', 'No. of Unique Inlinks', 'Title', 'Meta Description']
    bigger_font_headers = ["Keyword", "Search Volume", "Title", "Meta Description", "Competitor URL", "Client URL", "SERP Features by Keyword"]
    botify_opportunity_columns = ['Internal Pagerank', 'No. of Unique Inlinks', 'No. of Missed Clicks excluding anonymized queries', 'Normalized Raw Internal Pagerank', 'Normalized Missed Clicks']
    semrush_opportunity_columns = ['CPC', 'Keyword Difficulty', 'Normalized CPC', 'Normalized Keyword Difficulty', 'Normalized Search Volume', 'Normalized Search Position', 'Combined Score']
    # --- End Formatting Definitions ---


    # --- APPLY FORMATTING TO EXCEL FILE ---
    try:
        wb = load_workbook(xl_file)
        
        # --- FIX: Get all sheet names directly from the workbook ---
        # We ignore the faulty 'loop_list' from pip state
        sheets_to_format = [name for name in wb.sheetnames if name != "Filter Diagnostics"]
        # --- END FIX ---
        
        if not sheets_to_format:
            print("⚠️ No data sheets found in the Excel file to format. Skipping formatting.")
            return button # Return original button

        for sheet_name in sheets_to_format:
            if sheet_name not in wb.sheetnames:
                 print(f"  ⚠️ Skipping sheet '{sheet_name}': Not found in workbook.")
                 continue
                 
            print(f"  - Formatting '{sheet_name}' tab...") # This will now print for every sheet
            sheet = wb[sheet_name]
            column_mapping = _create_column_mapping(sheet)
            keyword_col_letter = column_mapping.get("Keyword")

            if not keyword_col_letter:
                print(f"  ⚠️ Skipping sheet '{sheet_name}': Cannot find 'Keyword' column.")
                continue
                
            last_row = _find_last_data_row(sheet, keyword_col_letter)
            
            # --- Apply All Formatting Steps ---
            
            # 1. Fill client column
            client_column_letter = column_mapping.get(TARGET_COMPETITOR_COL)
            if client_column_letter:
                for row in range(1, last_row + 1):
                    cell = sheet[f"{client_column_letter}{row}"]
                    cell.fill = client_color
                    if row == 1: cell.font = Font(bold=True)

            # 2. Fill Header Backgrounds
            _apply_fill_to_column_labels(sheet, column_mapping, semrush_columns, semrush_color)
            _apply_fill_to_column_labels(sheet, column_mapping, botify_columns, botify_color)
            present_competitors = [c for c in competitors if c in column_mapping and c != TARGET_COMPETITOR_COL]
            _apply_fill_to_column_labels(sheet, column_mapping, present_competitors, competitor_color)
            _apply_fill_to_column_labels(sheet, column_mapping, botify_opportunity_columns, botify_opportunity_color)
            _apply_fill_to_column_labels(sheet, column_mapping, semrush_opportunity_columns, semrush_opportunity_color)

            # 3. Header Styling
            header_font = Font(bold=True)
            header_align = Alignment(horizontal='center', vertical='center', wrap_text=True)
            header_height = 100 # Default header height
            for header, col_letter in column_mapping.items():
                cell = sheet[f"{col_letter}1"]
                cell.alignment = header_align
                cell.font = header_font
                cell.border = thin_border
                if header in bigger_font_headers:
                    cell.font = Font(size=14, bold=True)
                # Calculate max header height needed for rotated labels
                if header in competitors:
                     header_height = max(header_height, len(header) * 6 + 15) # Simple heuristic

            # 4. Hyperlinks
            for col_label in ["Competitor URL", "Client URL"]:
                col_letter = column_mapping.get(col_label)
                if col_letter:
                    for row in range(2, last_row + 1):
                        cell = sheet[f"{col_letter}{row}"]
                        url = cell.value
                        if url and _is_safe_url(url) and not str(url).startswith('=HYPERLINK'):
                            display_text = url if len(url) <= 80 else url[:77] + "..."
                            cell.value = f'=HYPERLINK("{url}", "{display_text}")'
                            cell.font = Font(color="0000FF", underline="single")
                            cell.alignment = Alignment(vertical='top', wrap_text=False)

            # 5. Rotate Competitor Headers
            competitor_header_align = Alignment(vertical='bottom', textRotation=90, horizontal='center')
            for competitor_col_name in competitors:
                col_letter = column_mapping.get(competitor_col_name)
                if col_letter:
                    cell = sheet[f"{col_letter}1"]
                    cell.alignment = competitor_header_align
                    sheet.column_dimensions[col_letter].width = 4
            
            # 11. Header Row Height & Freeze Panes
            sheet.row_dimensions[1].height = header_height # Apply calculated height
            sheet.freeze_panes = 'C2'


            # 6. Apply Column Widths
            for label, width in column_widths.items():
                column_letter = column_mapping.get(label)
                # Don't override competitor widths
                if column_letter and sheet.column_dimensions[column_letter].width != 4:
                    sheet.column_dimensions[column_letter].width = width * GLOBAL_WIDTH_ADJUSTMENT

            # 7. Apply Number Formats
            for label, format_code in number_formats.items():
                column_letter = column_mapping.get(label)
                if column_letter:
                    for row in range(2, last_row + 1):
                        cell = sheet[f"{column_letter}{row}"]
                        if cell.value is not None and not isinstance(cell.value, str):
                            cell.number_format = format_code

            # 8. Apply Conditional Formatting
            _apply_conditional_formatting(sheet, column_mapping, last_row, conditionals_descending, conditionals_ascending, color_scale_rule_desc, color_scale_rule_asc)

            # 9. Rename 'Client URL' Header
            client_url_column_letter = column_mapping.get("Client URL")
            if client_url_column_letter:
                sheet[f"{client_url_column_letter}1"].value = f"{TARGET_COMPETITOR_COL} URL"

            # 10. Data Cell Alignment
            data_align = Alignment(wrap_text=False, vertical='top')
            url_columns = [column_mapping.get("Competitor URL"), client_url_column_letter]
            for row_idx in range(2, last_row + 1):
                for col_idx in range(1, sheet.max_column + 1):
                    cell = sheet.cell(row=row_idx, column=col_idx)
                    if get_column_letter(col_idx) not in url_columns:
                        cell.alignment = data_align

            # 12. Apply AutoFilter
            max_col_letter = get_column_letter(sheet.max_column)
            if last_row > 0:
                sheet.auto_filter.ref = f"A1:{max_col_letter}{last_row}"

            # 13. Add Table for banded rows
            if last_row > 0:
                table_range = f"A1:{max_col_letter}{last_row}"
                table_name = f"DataTable_{re.sub(r'[^A-Za-z0-9_]', '', sheet_name)}"
                existing_table_names = [t.name for t in sheet._tables if hasattr(t, 'name')]
                if table_name not in existing_table_names:
                    tab = Table(displayName=table_name, ref=table_range)
                    style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False,
                                           showLastColumn=False, showRowStripes=True, showColumnStripes=False)
                    tab.tableStyleInfo = style
                    try:
                        sheet.add_table(tab)
                    except ValueError as ve:
                        print(f"  Note: Could not add Excel Table '{table_name}'. Overlaps? {ve}")

        wb.save(xl_file)
        print(f"✅ Formatting applied to all data tabs and saved to {xl_file.name}")

    except FileNotFoundError:
        print(f"❌ Error: Excel file not found at {xl_file}. Cannot apply formatting.")
    except KeyError as e:
        print(f"❌ Error during formatting: A required column key was not found: {e}.")
        if 'column_mapping' in locals(): print(f"   Column Mapping: {column_mapping}")
    except Exception as e:
        print(f"❌ An unexpected error occurred during Excel formatting: {e}")
    
    # --- RETURN VALUE ---
    # Return the button so it can be re-displayed (handler is already attached)
    return button

# %% editable=true slideshow={"slide_type": ""}
import pandas as pd
from pathlib import Path
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Font, Alignment, Border, Side
from openpyxl.formatting.rule import ColorScaleRule
from openpyxl.worksheet.table import Table, TableStyleInfo
from openpyxl.utils import get_column_letter
import re # Needed for is_safe_url
import validators # Need to import validators for URL check
import gap_analyzer_sauce # Ensure module is imported
from IPython.display import display # Ensure display is imported

# This is the final "painterly" step.
# This one function now handles:
# 1. Loading the Excel workbook from disk.
# 2. Getting ALL sheet names from the workbook.
# 3. Defining all helper functions for formatting (column mapping, cell finding, etc.).
# 4. Defining all formatting rules (colors, widths, number formats, column groups).
# 5. Iterating through each sheet and applying all 13+ formatting steps.
# 6. Saving the final, polished Excel file.
# 7. Returning the "Open Folder" button for re-display.

# It receives all necessary objects from the previous cells.
button = gap_analyzer_sauce.apply_excel_formatting(
    job,
    xl_file,
    competitors,
    semrush_lookup,
    TARGET_COMPETITOR_COL,
    has_botify,
    GLOBAL_WIDTH_ADJUSTMENT, # This is from the config cell
    button
)

# Re-display the button (its on_click handler is preserved)
display(button)

# Optional verification
# print("\nVerification: Final formatting applied.")