The Uncompressible Truth: When 'Dirty' Paths Become Canonical Keys in Data Pipelines

# Print the initial processing message for the Gap Analysis tab
print("- Writing Gap Analysis tab (takes some time)...")

# Initialize Loop List
loop_list = ["Gap Analysis"]
last_competitor = competitors[-1]

# Save Initial Excel Sheet with xlsxwriter for speed (openpyxl for appending)
arg_dict = {'options': {'strings_to_urls': False}}
df_tab = bf.normalize_and_score(df.copy(), semrush_lookup, has_botify, last_competitor, False)
with pd.ExcelWriter(xl_file, engine="xlsxwriter", engine_kwargs=arg_dict, mode='w') as writer:
    df_tab.to_excel(writer, sheet_name='Gap Analysis', index=False)

def normalize_and_score(df, registered_domain, has_botify_data, after_col, reorder):
    # Normalize metrics that are always included
    df['Normalized Search Volume'] = safe_normalize(df['Search Volume'])
    df['Normalized Search Position'] = safe_normalize(df[registered_domain])
    df['Normalized Keyword Difficulty'] = safe_normalize(df['Keyword Difficulty'])
    df['Normalized CPC'] = safe_normalize(df['CPC'])

    # Always include CPC and Keyword Difficulty in the combined score
    combined_score = df['Normalized CPC'] - df['Normalized Keyword Difficulty']

    if has_botify_data:
        # Normalize additional Botify metrics if available
        df['Normalized Raw Internal Pagerank'] = safe_normalize(df['Raw Internal Pagerank'])

        if "No. of Missed Clicks excluding anonymized queries" in df.columns:
            df['Normalized Missed Clicks'] = safe_normalize(df["No. of Missed Clicks excluding anonymized queries"])
            combined_score += df['Normalized Missed Clicks']
        else:
            df['Normalized Missed Clicks'] = 0  # or any other default value if necessary

        # Add Botify metrics to the combined score
        combined_score += (-1 * df['Normalized Raw Internal Pagerank'] +
                           df['Normalized Search Volume'] +
                           df['Normalized Search Position'])

    # Apply the combined score to the DataFrame
    df['Combined Score'] = combined_score

    if reorder:
        # Reorder columns if required
        df = reorder_columns(df, "CPC", after_col)
        df = reorder_columns(df, "Keyword Difficulty", "CPC")
        if has_botify_data:
            df = reorder_columns(df, "Internal Pagerank", "Keyword Difficulty")
            df = reorder_columns(df, "No. of Unique Inlinks", "Internal Pagerank")
            if "No. of Missed Clicks excluding anonymized queries" in df.columns:
                df = reorder_columns(df, "No. of Missed Clicks excluding anonymized queries", "No. of Unique Inlinks")

    return df

def reorder_columns(df, priority_column, after_column):
    if priority_column in df.columns:
        columns = list(df.columns.drop(priority_column))
        after_column_index = columns.index(after_column)
        columns.insert(after_column_index + 1, priority_column)
        df = df[columns]
    else:
        print(f"{priority_column} not found in DataFrame columns.")
    return df

def export_and_format_excel(job: str, df: pd.DataFrame):
    """
    Exports the DataFrame to a professionally formatted Excel file and a CSV file
    inside a dedicated 'output' folder. Displays a button to open the folder.
    """
    if df.empty:
        print("⚠️ DataFrame is empty, skipping file export.")
        return

    output_dir = Path("output")
    output_dir.mkdir(parents=True, exist_ok=True)

    csv_filename = output_dir / f"{job}_output.csv"
    excel_filename = output_dir / f"{job}_output.xlsx"
    
    print(f"📄 Saving CSV file: {csv_filename}")
    df.to_csv(csv_filename, index=False)
    
    print(f"🎨 Formatting and exporting data to Excel: {excel_filename}")
    with pd.ExcelWriter(excel_filename, engine='openpyxl') as writer:
        df.to_excel(writer, index=False, sheet_name='FAQ_Analysis')
        
        worksheet = writer.sheets['FAQ_Analysis']

        # 1. Create an Excel Table for high-contrast banded rows and filtering
        table_range = f"A1:{get_column_letter(worksheet.max_column)}{worksheet.max_row}"
        table = Table(displayName="FAQTable", ref=table_range)
        # Using a more visible style for alternate row shading
        style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False,
                               showLastColumn=False, showRowStripes=True, showColumnStripes=False)
        table.tableStyleInfo = style
        worksheet.add_table(table)

        # 2. Define consistent column widths
        width_map = {
            "url": 50,
            "title": 50,
            "priority": 10,
            "question": 60,
            "target_intent": 45,
            "justification": 45,
        }
        default_width = 18
        
        # 3. Apply formatting to all cells
        # Loop through headers (row 1)
        for col_idx, column_cell in enumerate(worksheet[1], 1):
            column_letter = get_column_letter(col_idx)
            header_text = str(column_cell.value)
            
            # A. Format header cell
            column_cell.font = Font(bold=True)
            column_cell.alignment = Alignment(horizontal='center', vertical='center')

            # B. Set column width based on header
            width = default_width
            for prefix, value in width_map.items():
                if header_text.lower().startswith(prefix):
                    width = value
                    break
            worksheet.column_dimensions[column_letter].width = width

        # Loop through data cells (rows 2 onwards) to apply text wrapping
        for row in worksheet.iter_rows(min_row=2):
            for cell in row:
                cell.alignment = Alignment(wrap_text=True, vertical='top')

    print(f"✅ Success! Files saved in the '{output_dir}' folder.")
    
    button = widgets.Button(
        description="📂 Open Output Folder",
        tooltip=f"Open {output_dir.resolve()}",
        button_style='success'
    )
    button.on_click(lambda b: _open_folder("output"))
    display(button)

# %% editable=true slideshow={"slide_type": ""}
# --- CORE SUPPORT FUNCTIONS ---
import pandas as pd
import numpy as np
from pathlib import Path
import os
import platform
import subprocess
import ipywidgets as widgets
from IPython.display import display

# Surgical port of _open_folder from FAQuilizer, necessary for the widget to work
def _open_folder(path_str: str = "."):
    """Opens the specified folder in the system's default file explorer."""
    folder_path = Path(path_str).resolve()
    
    if not folder_path.exists() or not folder_path.is_dir():
        print(f"❌ Error: Path is not a valid directory: {folder_path}")
        return

    system = platform.system()
    try:
        if system == "Windows":
            os.startfile(folder_path)
        elif system == "Darwin":  # macOS
            subprocess.run(["open", folder_path])
        else:  # Linux (xdg-open covers most desktop environments)
            subprocess.run(["xdg-open", folder_path])
    except Exception as e:
        print(f"❌ Failed to open folder. Error: {e}")

# This utility must be defined for normalize_and_score to work
def safe_normalize(series):
    """ Normalize the series safely to avoid divide by zero and handle NaN values. """
    min_val = series.min()
    max_val = series.max()
    range_val = max_val - min_val
    if range_val == 0:
        # Avoid division by zero by returning zero array if no range
        return np.zeros_like(series)
    else:
        # Normalize and fill NaN values that might result from empty/NaN series
        return (series - min_val).div(range_val).fillna(0)

# Surgical port of bf.reorder_columns
def reorder_columns_surgical(df, priority_column, after_column):
    if priority_column in df.columns:
        columns = list(df.columns.drop(priority_column))
        # Handle cases where the after_column may have been dropped earlier
        if after_column not in columns:
            print(f"⚠️ Reorder Error: Target column '{after_column}' not found. Skipping reorder of '{priority_column}'.")
            return df
            
        after_column_index = columns.index(after_column)
        columns.insert(after_column_index + 1, priority_column)
        df = df[columns]
    else:
        print(f"⚠️ Reorder Error: Column '{priority_column}' not found in DataFrame.")
    return df

# Surgical port of bf.normalize_and_score
def normalize_and_score_surgical(df, registered_domain, has_botify_data, after_col, reorder):
    # Rename original column fields to match expected names in the dataframe
    # This addresses the fact that the Botify fields were renamed to user-friendly names
    if 'internal_page_rank.raw' in df.columns:
        df = df.rename(columns={'internal_page_rank.raw': 'Raw Internal Pagerank'}, inplace=False)
    
    # Normalize metrics that are always included
    df['Normalized Search Volume'] = safe_normalize(df['Search Volume'])
    df['Normalized Search Position'] = safe_normalize(df[registered_domain])
    df['Normalized Keyword Difficulty'] = safe_normalize(df['Keyword Difficulty'])
    df['Normalized CPC'] = safe_normalize(df['CPC'])

    # Always include CPC and Keyword Difficulty in the combined score
    combined_score = df['Normalized CPC'] - df['Normalized Keyword Difficulty']

    if has_botify_data:
        # Normalize additional Botify metrics if available
        if 'Raw Internal Pagerank' in df.columns:
            df['Normalized Raw Internal Pagerank'] = safe_normalize(df['Raw Internal Pagerank'])
        else:
            df['Normalized Raw Internal Pagerank'] = 0

        if "No. of Missed Clicks excluding anonymized queries" in df.columns:
            df['Normalized Missed Clicks'] = safe_normalize(df["No. of Missed Clicks excluding anonymized queries"])
            combined_score += df['Normalized Missed Clicks']
        else:
            df['Normalized Missed Clicks'] = 0

        # Add Botify metrics to the combined score
        combined_score += (-1 * df['Normalized Raw Internal Pagerank'] +
                           df['Normalized Search Volume'] +
                           df['Normalized Search Position'])

    # Apply the combined score to the DataFrame
    df['Combined Score'] = combined_score

    if reorder:
        # Reorder columns if required (using the surgically ported reorder function)
        df = reorder_columns_surgical(df, "CPC", after_col)
        df = reorder_columns_surgical(df, "Keyword Difficulty", "CPC")
        if has_botify_data:
            df = reorder_columns_surgical(df, "Internal Pagerank", "Keyword Difficulty")
            df = reorder_columns_surgical(df, "No. of Unique Inlinks", "Internal Pagerank")
            if "No. of Missed Clicks excluding anonymized queries" in df.columns:
                df = reorder_columns_surgical(df, "No. of Missed Clicks excluding anonymized queries", "No. of Unique Inlinks")

    return df

# %% editable=true slideshow={"slide_type": ""}
import pandas as pd
import xlsxwriter
import itertools
from pathlib import Path
from IPython.display import display
import os
import platform
import subprocess
import ipywidgets as widgets
# NOTE: This cell assumes 'job', 'df', 'competitors', 'semrush_lookup', 'has_botify' are defined.

# --- 1. DEFINE SECURE OUTPUT PATHS ---
# Create the secure, client-specific deliverables folder: Notebooks/deliverables/{job}/
deliverables_dir = Path("deliverables") / job
deliverables_dir.mkdir(parents=True, exist_ok=True)

# Define the final Excel file path
xl_filename = f"{semrush_lookup.replace('.', '_')}_GAPalyzer_{job}_V1.xlsx"
xl_file = deliverables_dir / xl_filename

# --- 2. EXECUTE CORE LOGIC ---
print(f"- Writing Gap Analysis tab to {xl_file.name} (first pass)...")

# Initialize Loop List (verbatim)
loop_list = ["Gap Analysis"]
last_competitor = competitors[-1]

# Apply the normalization and scoring logic
df_tab = normalize_and_score_surgical(df.copy(), semrush_lookup, has_botify, last_competitor, False)

# Save Initial Excel Sheet with xlsxwriter (Engine Kwargs included verbatim)
arg_dict = {'options': {'strings_to_urls': False}}
try:
    with pd.ExcelWriter(xl_file, engine="xlsxwriter", engine_kwargs=arg_dict, mode='w') as writer:
        # NOTE: Using df_tab here, which has the Normalized columns
        df_tab.to_excel(writer, sheet_name='Gap Analysis', index=False)
    
    print("✅ Gap Analysis tab written (Unformatted Pass 1).")
    
except Exception as e:
    print(f"❌ Error writing Excel file: {e}")
    
# --- 3. DISPLAY SECURE EGRESS BUTTON ---

# The button description clearly indicates the file and the security of the folder.
button = widgets.Button(
    description=f"📂 Open Deliverables Folder ({job})",
    tooltip=f"Open {deliverables_dir.resolve()}",
    button_style='success'
)
# 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)

# Store final file path in pipeline
pip.set(job, 'final_xl_file', str(xl_file))
pip.set(job, 'deliverables_folder', str(deliverables_dir))

# %%
# ... (contents of the cell that joins, filters, and truncates) ...

# NOTE: The DataFrame 'df' (which is set to 'final_df' in the housekeeping step
# before truncation) is the one that has the column names we need to clean.

# --- DATA CLEANUP FOR SCORING (CRITICAL FIX) ---
# The columns were created in the pivot table (pivot_df) using the domain list.
# We must remove any residual trailing slash '/' from the column headers to match the clean 'semrush_lookup'.

# 1. Store original columns
original_cols = df.columns.tolist()
new_cols = []
cleaned_lookup = semrush_lookup.rstrip('/') # Just for safety

for col in original_cols:
    # Check if the column is a domain that might have a trailing slash (like 'nixos.org/')
    # This also handles columns like 'nixos.org/path/' if they somehow survived.
    if col.endswith('/'):
        new_cols.append(col.rstrip('/'))
    else:
        new_cols.append(col)

# 2. Apply the cleaned column names
df.columns = new_cols

# 3. Re-run the normalization with the now-clean column name
# This line is where the original script called the problematic function:
# df_tab = normalize_and_score_surgical(df.copy(), semrush_lookup, has_botify, last_competitor, False)
df_tab = normalize_and_score_surgical(df.copy(), cleaned_lookup, has_botify, last_competitor, False)

# ... (rest of the code for the last cell that saves the Excel file) ...

# Cell In[33], normalize_and_score_surgical
def normalize_and_score_surgical(df, registered_domain, has_botify_data, after_col, reorder):
    # ... (rest of the code) ...
    
    # --- CRITICAL FIX START ---
    # Find the actual column name that matches the registered_domain, ignoring the trailing slash.
    # This searches the columns for the one that starts with the registered_domain name.
    
    clean_domain = registered_domain.rstrip('/')
    target_col = None
    
    for col in df.columns:
        if col.rstrip('/') == clean_domain:
            target_col = col
            break
            
    if target_col is None:
        # If still not found, raise the original KeyError for debug
        raise KeyError(f"Could not find column for registered domain '{registered_domain}'. Available columns: {df.columns.tolist()}")
    
    # --- CRITICAL FIX END ---

    # Normalize metrics that are always included
    df['Normalized Search Volume'] = safe_normalize(df['Search Volume'])
    # USE THE FOUND target_col instead of registered_domain
    df['Normalized Search Position'] = safe_normalize(df[target_col]) 
    df['Normalized Keyword Difficulty'] = safe_normalize(df['Keyword Difficulty'])
    df['Normalized CPC'] = safe_normalize(df['CPC'])
    
    # ... (rest of the code) ...
    # Note: If registered_domain is used later, it must also be replaced by target_col
    
    # The original scoring uses the registered domain name again. We assume it should use the target column.
    # Original: combined_score += (-1 * df['Normalized Raw Internal Pagerank'] + df['Normalized Search Volume'] + df['Normalized Search Position'])
    # The scoring formula in normalize_and_score_surgical does not use registered_domain for the score itself, only for normalization, so the fix is contained.

    # ... (rest of the code) ...
    
    return df

# Surgical port of bf.normalize_and_score
def normalize_and_score_surgical(df, registered_domain, has_botify_data, after_col, reorder):
    # Rename original column fields to match expected names in the dataframe
    if 'internal_page_rank.raw' in df.columns:
        df = df.rename(columns={'internal_page_rank.raw': 'Raw Internal Pagerank'}, inplace=False)
        
    # --- FIX FOR KEYERROR: Find the actual column name used in the DataFrame ---
    clean_domain = registered_domain.rstrip('/')
    target_col = None
    
    for col in df.columns:
        if col.rstrip('/') == clean_domain:
            target_col = col
            break
            
    if target_col is None:
        # Re-raise the error with better information if we can't find the column after cleaning
        raise KeyError(f"Could not find client domain column for '{registered_domain}' in DataFrame. Available columns: {df.columns.tolist()}")
    # --- END FIX ---
    
    # Normalize metrics that are always included
    df['Normalized Search Volume'] = safe_normalize(df['Search Volume'])
    df['Normalized Search Position'] = safe_normalize(df[target_col])  # <-- FIXED LINE
    df['Normalized Keyword Difficulty'] = safe_normalize(df['Keyword Difficulty'])
    df['Normalized CPC'] = safe_normalize(df['CPC'])

    # Always include CPC and Keyword Difficulty in the combined score
    combined_score = df['Normalized CPC'] - df['Normalized Keyword Difficulty']

    if has_botify_data:
        # Normalize additional Botify metrics if available
        if 'Raw Internal Pagerank' in df.columns:
            df['Normalized Raw Internal Pagerank'] = safe_normalize(df['Raw Internal Pagerank'])
        else:
            df['Normalized Raw Internal Pagerank'] = 0

        if "No. of Missed Clicks excluding anonymized queries" in df.columns:
            df['Normalized Missed Clicks'] = safe_normalize(df["No. of Missed Clicks excluding anonymized queries"])
            combined_score += df['Normalized Missed Clicks']
        else:
            df['Normalized Missed Clicks'] = 0

        # Add Botify metrics to the combined score
        combined_score += (-1 * df['Normalized Raw Internal Pagerank'] +
                           df['Normalized Search Volume'] +
                           df['Normalized Search Position'])

    # Apply the combined score to the DataFrame
    df['Combined Score'] = combined_score

    if reorder:
        # Reorder columns if required (using the surgically ported reorder function)
        df = reorder_columns_surgical(df, "CPC", after_col)
        df = reorder_columns_surgical(df, "Keyword Difficulty", "CPC")
        if has_botify_data:
            df = reorder_columns_surgical(df, "Internal Pagerank", "Keyword Difficulty")
            df = reorder_columns_surgical(df, "No. of Unique Inlinks", "Internal Pagerank")
            if "No. of Missed Clicks excluding anonymized queries" in df.columns:
                df = reorder_columns_surgical(df, "No. of Missed Clicks excluding anonymized queries", "No. of Unique Inlinks")

    return df

# %% editable=true slideshow={"slide_type": ""}
# --- CORE SUPPORT FUNCTIONS ---
import pandas as pd
import numpy as np
from pathlib import Path
import os
import platform
import subprocess
import ipywidgets as widgets
from IPython.display import display

# Surgical port of _open_folder from FAQuilizer, necessary for the widget to work
def _open_folder(path_str: str = "."):
    """Opens the specified folder in the system's default file explorer."""
    folder_path = Path(path_str).resolve()
    
    if not folder_path.exists() or not folder_path.is_dir():
        print(f"❌ Error: Path is not a valid directory: {folder_path}")
        return

    system = platform.system()
    try:
        if system == "Windows":
            os.startfile(folder_path)
        elif system == "Darwin":  # macOS
            subprocess.run(["open", folder_path])
        else:  # Linux (xdg-open covers most desktop environments)
            subprocess.run(["xdg-open", folder_path])
    except Exception as e:
        print(f"❌ Failed to open folder. Error: {e}")

# This utility must be defined for normalize_and_score to work
def safe_normalize(series):
    """ Normalize the series safely to avoid divide by zero and handle NaN values. """
    min_val = series.min()
    max_val = series.max()
    range_val = max_val - min_val
    if range_val == 0:
        # Avoid division by zero by returning zero array if no range
        return np.zeros_like(series)
    else:
        # Normalize and fill NaN values that might result from empty/NaN series
        return (series - min_val).div(range_val).fillna(0)

# Surgical port of bf.reorder_columns
def reorder_columns_surgical(df, priority_column, after_column):
    if priority_column in df.columns:
        columns = list(df.columns.drop(priority_column))
        # Handle cases where the after_column may have been dropped earlier
        if after_column not in columns:
            print(f"⚠️ Reorder Error: Target column '{after_column}' not found. Skipping reorder of '{priority_column}'.")
            return df
            
        after_column_index = columns.index(after_column)
        columns.insert(after_column_index + 1, priority_column)
        df = df[columns]
    else:
        print(f"⚠️ Reorder Error: Column '{priority_column}' not found in DataFrame.")
    return df

# Surgical port of bf.normalize_and_score
def normalize_and_score_surgical(df, registered_domain, has_botify_data, after_col, reorder):
    # Rename original column fields to match expected names in the dataframe
    if 'internal_page_rank.raw' in df.columns:
        df = df.rename(columns={'internal_page_rank.raw': 'Raw Internal Pagerank'}, inplace=False)
    
    # --- FIX FOR KEYERROR: Find the actual column name used in the DataFrame ---
    # The registered_domain (e.g., 'nixos.org') might be missing the trailing slash 
    # that is present in the DataFrame column name (e.g., 'nixos.org/'). 
    # We find the actual column name by comparing the clean domain to the cleaned headers.
    
    clean_domain = registered_domain.rstrip('/')
    target_col = None
    
    for col in df.columns:
        if col.rstrip('/') == clean_domain:
            target_col = col
            break
            
    if target_col is None:
        # Re-raise the error with better information if we can't find the column after cleaning
        raise KeyError(f"Could not find client domain column for '{registered_domain}' in DataFrame. Available columns: {df.columns.tolist()}")
    # --- END FIX ---
    
    # Normalize metrics that are always included
    df['Normalized Search Volume'] = safe_normalize(df['Search Volume'])
    df['Normalized Search Position'] = safe_normalize(df[target_col]) # <-- FIXED LINE
    df['Normalized Keyword Difficulty'] = safe_normalize(df['Keyword Difficulty'])
    df['Normalized CPC'] = safe_normalize(df['CPC'])

    # Always include CPC and Keyword Difficulty in the combined score
    combined_score = df['Normalized CPC'] - df['Normalized Keyword Difficulty']

    if has_botify_data:
        # Normalize additional Botify metrics if available
        if 'Raw Internal Pagerank' in df.columns:
            df['Normalized Raw Internal Pagerank'] = safe_normalize(df['Raw Internal Pagerank'])
        else:
            df['Normalized Raw Internal Pagerank'] = 0

        if "No. of Missed Clicks excluding anonymized queries" in df.columns:
            df['Normalized Missed Clicks'] = safe_normalize(df["No. of Missed Clicks excluding anonymized queries"])
            combined_score += df['Normalized Missed Clicks']
        else:
            df['Normalized Missed Clicks'] = 0

        # Add Botify metrics to the combined score
        combined_score += (-1 * df['Normalized Raw Internal Pagerank'] +
                           df['Normalized Search Volume'] +
                           df['Normalized Search Position'])

    # Apply the combined score to the DataFrame
    df['Combined Score'] = combined_score

    if reorder:
        # Reorder columns if required (using the surgically ported reorder function)
        df = reorder_columns_surgical(df, "CPC", after_col)
        df = reorder_columns_surgical(df, "Keyword Difficulty", "CPC")
        if has_botify_data:
            df = reorder_columns_surgical(df, "Internal Pagerank", "Keyword Difficulty")
            df = reorder_columns_surgical(df, "No. of Unique Inlinks", "Internal Pagerank")
            if "No. of Missed Clicks excluding anonymized queries" in df.columns:
                df = reorder_columns_surgical(df, "No. of Missed Clicks excluding anonymized queries", "No. of Unique Inlinks")

    return df

# %% editable=true slideshow={"slide_type": ""}
# --- CORE SUPPORT FUNCTIONS ---
import pandas as pd
import numpy as np
from pathlib import Path
import os
import platform
import subprocess
import ipywidgets as widgets
from IPython.display import display

# Surgical port of _open_folder from FAQuilizer, necessary for the widget to work
def _open_folder(path_str: str = "."):
    """Opens the specified folder in the system's default file explorer."""
    folder_path = Path(path_str).resolve()
    
    if not folder_path.exists() or not folder_path.is_dir():
        print(f"❌ Error: Path is not a valid directory: {folder_path}")
        return

    system = platform.system()
    try:
        if system == "Windows":
            os.startfile(folder_path)
        elif system == "Darwin":  # macOS
            subprocess.run(["open", folder_path])
        else:  # Linux (xdg-open covers most desktop environments)
            subprocess.run(["xdg-open", folder_path])
    except Exception as e:
        print(f"❌ Failed to open folder. Error: {e}")

# This utility must be defined for normalize_and_score to work
def safe_normalize(series):
    """ Normalize the series safely to avoid divide by zero and handle NaN values. """
    min_val = series.min()
    max_val = series.max()
    range_val = max_val - min_val
    if range_val == 0:
        # Avoid division by zero by returning zero array if no range
        return np.zeros_like(series)
    else:
        # Normalize and fill NaN values that might result from empty/NaN series
        return (series - min_val).div(range_val).fillna(0)

# Surgical port of bf.reorder_columns
def reorder_columns_surgical(df, priority_column, after_column):
    if priority_column in df.columns:
        columns = list(df.columns.drop(priority_column))
        # Handle cases where the after_column may have been dropped earlier
        if after_column not in columns:
            print(f"⚠️ Reorder Error: Target column '{after_column}' not found. Skipping reorder of '{priority_column}'.")
            return df
            
        after_column_index = columns.index(after_column)
        columns.insert(after_column_index + 1, priority_column)
        df = df[columns]
    else:
        print(f"⚠️ Reorder Error: Column '{priority_column}' not found in DataFrame.")
    return df

# Surgical port of bf.normalize_and_score - WITH KEY FIX
def normalize_and_score_surgical(df, registered_domain, has_botify_data, after_col, reorder):
    
    # Rename original column fields to match expected names in the dataframe
    if 'internal_page_rank.raw' in df.columns:
        df = df.rename(columns={'internal_page_rank.raw': 'Raw Internal Pagerank'}, inplace=False)
    
    # --- CRITICAL FIX FOR KEYERROR / TRAILING SLASHES ---
    # We find the actual column name used in the DataFrame, allowing for the column to retain the slash.
    # This supports dirty keys like 'nixos.org/' even if the lookup key is clean ('nixos.org').
    
    clean_domain_key = registered_domain.rstrip('/')
    target_col = None
    
    for col in df.columns:
        # We check if the column name, when stripped, matches the clean registered domain key.
        # This is the most reliable way to handle path/subdomain variations.
        if col.rstrip('/') == clean_domain_key:
            target_col = col
            break
            
    if target_col is None:
        raise KeyError(f"Could not find client domain column for '{registered_domain}' in DataFrame. Available columns: {df.columns.tolist()}")
    # --- END CRITICAL FIX ---
    
    # Normalize metrics that are always included
    df['Normalized Search Volume'] = safe_normalize(df['Search Volume'])
    df['Normalized Search Position'] = safe_normalize(df[target_col]) # <-- USES THE FOUND COLUMN
    df['Normalized Keyword Difficulty'] = safe_normalize(df['Keyword Difficulty'])
    df['Normalized CPC'] = safe_normalize(df['CPC'])

    # Always include CPC and Keyword Difficulty in the combined score
    combined_score = df['Normalized CPC'] - df['Normalized Keyword Difficulty']

    if has_botify_data:
        # Normalize additional Botify metrics if available
        if 'Raw Internal Pagerank' in df.columns:
            df['Normalized Raw Internal Pagerank'] = safe_normalize(df['Raw Internal Pagerank'])
        else:
            df['Normalized Raw Internal Pagerank'] = 0

        if "No. of Missed Clicks excluding anonymized queries" in df.columns:
            df['Normalized Missed Clicks'] = safe_normalize(df["No. of Missed Clicks excluding anonymized queries"])
            combined_score += df['Normalized Missed Clicks']
        else:
            df['Normalized Missed Clicks'] = 0

        # Add Botify metrics to the combined score
        combined_score += (-1 * df['Normalized Raw Internal Pagerank'] +
                           df['Normalized Search Volume'] +
                           df['Normalized Search Position'])

    # Apply the combined score to the DataFrame
    df['Combined Score'] = combined_score

    if reorder:
        # Reorder columns if required (using the surgically ported reorder function)
        df = reorder_columns_surgical(df, "CPC", after_col)
        df = reorder_columns_surgical(df, "Keyword Difficulty", "CPC")
        if has_botify_data:
            df = reorder_columns_surgical(df, "Internal Pagerank", "Keyword Difficulty")
            df = reorder_columns_surgical(df, "No. of Unique Inlinks", "Internal Pagerank")
            if "No. of Missed Clicks excluding anonymized queries" in df.columns:
                df = reorder_columns_surgical(df, "No. of Missed Clicks excluding anonymized queries", "No. of Unique Inlinks")

    return df

# %% editable=true slideshow={"slide_type": ""}
import pandas as pd
import xlsxwriter
import itertools
from pathlib import Path
from IPython.display import display
import os
import platform
import subprocess
import ipywidgets as widgets
# NOTE: This cell assumes 'job', 'df', 'competitors', 'semrush_lookup', 'has_botify' are defined.

# --- 1. DEFINE SECURE OUTPUT PATHS ---
# Create the secure, client-specific deliverables folder: Notebooks/deliverables/{job}/
deliverables_dir = Path("deliverables") / job
deliverables_dir.mkdir(parents=True, exist_ok=True)

# Define the final Excel file path (using a standardized, clean name for the file)
xl_filename = f"{semrush_lookup.replace('.', '_').rstrip('_')}_GAPalyzer_{job}_V1.xlsx"
xl_file = deliverables_dir / xl_filename

# --- 2. EXECUTE CORE LOGIC ---
print(f"- Writing Gap Analysis tab to {xl_file.name} (first pass)...")

# Initialize Loop List (verbatim)
loop_list = ["Gap Analysis"]
last_competitor = competitors[-1]

# Apply the normalization and scoring logic
df_tab = normalize_and_score_surgical(df.copy(), semrush_lookup, has_botify, last_competitor, False)

# Save Initial Excel Sheet with xlsxwriter (Engine Kwargs included verbatim)
arg_dict = {'options': {'strings_to_urls': False}}
try:
    with pd.ExcelWriter(xl_file, engine="xlsxwriter", engine_kwargs=arg_dict, mode='w') as writer:
        # NOTE: Using df_tab here, which has the Normalized columns
        df_tab.to_excel(writer, sheet_name='Gap Analysis', index=False)
    
    print("✅ Gap Analysis tab written (Unformatted Pass 1).")
    
except Exception as e:
    print(f"❌ Error writing Excel file: {e}")
    
# --- 3. DISPLAY SECURE EGRESS BUTTON ---

# The button description clearly indicates the file and the security of the folder.
button = widgets.Button(
    description=f"📂 Open Deliverables Folder ({job})",
    tooltip=f"Open {deliverables_dir.resolve()}",
    button_style='success'
)
# 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)

# Store final file path in pipeline
pip.set(job, 'final_xl_file', str(xl_file))
pip.set(job, 'deliverables_folder', str(deliverables_dir))

# %% editable=true slideshow={"slide_type": ""}
# --- CORE SUPPORT FUNCTIONS ---
import pandas as pd
import numpy as np
from pathlib import Path
import os
import platform
import subprocess
import ipywidgets as widgets
from IPython.display import display

# Surgical port of _open_folder from FAQuilizer, necessary for the widget to work
def _open_folder(path_str: str = "."):
    """Opens the specified folder in the system's default file explorer."""
    folder_path = Path(path_str).resolve()
    
    if not folder_path.exists() or not folder_path.is_dir():
        print(f"❌ Error: Path is not a valid directory: {folder_path}")
        return

    system = platform.system()
    try:
        if system == "Windows":
            os.startfile(folder_path)
        elif system == "Darwin":  # macOS
            subprocess.run(["open", folder_path])
        else:  # Linux (xdg-open covers most desktop environments)
            subprocess.run(["xdg-open", folder_path])
    except Exception as e:
        print(f"❌ Failed to open folder. Error: {e}")

# This utility must be defined for normalize_and_score to work
def safe_normalize(series):
    """ Normalize the series safely to avoid divide by zero and handle NaN values. """
    min_val = series.min()
    max_val = series.max()
    range_val = max_val - min_val
    if range_val == 0:
        # Avoid division by zero by returning zero array if no range
        return np.zeros_like(series)
    else:
        # Normalize and fill NaN values that might result from empty/NaN series
        return (series - min_val).div(range_val).fillna(0)

# Surgical port of bf.reorder_columns
def reorder_columns_surgical(df, priority_column, after_column):
    if priority_column in df.columns:
        columns = list(df.columns.drop(priority_column))
        # Handle cases where the after_column may have been dropped earlier
        if after_column not in columns:
            print(f"⚠️ Reorder Error: Target column '{after_column}' not found. Skipping reorder of '{priority_column}'.")
            return df
            
        after_column_index = columns.index(after_column)
        columns.insert(after_column_index + 1, priority_column)
        df = df[columns]
    else:
        print(f"⚠️ Reorder Error: Column '{priority_column}' not found in DataFrame.")
    return df

# Surgical port of bf.normalize_and_score - WITH KEY FIX
def normalize_and_score_surgical(df, registered_domain, has_botify_data, after_col, reorder):
    
    # Rename original column fields to match expected names in the dataframe
    if 'internal_page_rank.raw' in df.columns:
        df = df.rename(columns={'internal_page_rank.raw': 'Raw Internal Pagerank'}, inplace=False)
    
    # --- CRITICAL FIX FOR KEYERROR / TRAILING SLASHES ---
    # We find the actual column name used in the DataFrame, allowing for the column to retain the slash.
    # This supports dirty keys like 'nixos.org/' even if the lookup key is clean ('nixos.org').
    
    clean_domain_key = registered_domain.rstrip('/')
    target_col = None
    
    for col in df.columns:
        # We check if the column name, when stripped, matches the clean registered domain key.
        # This is the most reliable way to handle path/subdomain variations.
        if col.rstrip('/') == clean_domain_key:
            target_col = col
            break
            
    if target_col is None:
        raise KeyError(f"Could not find client domain column for '{registered_domain}' in DataFrame. Available columns: {df.columns.tolist()}")
    # --- END CRITICAL FIX ---
    
    # Normalize metrics that are always included
    df['Normalized Search Volume'] = safe_normalize(df['Search Volume'])
    df['Normalized Search Position'] = safe_normalize(df[target_col]) # <-- USES THE FOUND COLUMN
    df['Normalized Keyword Difficulty'] = safe_normalize(df['Keyword Difficulty'])
    df['Normalized CPC'] = safe_normalize(df['CPC'])

    # Always include CPC and Keyword Difficulty in the combined score
    combined_score = df['Normalized CPC'] - df['Normalized Keyword Difficulty']

    if has_botify_data:
        # Normalize additional Botify metrics if available
        if 'Raw Internal Pagerank' in df.columns:
            df['Normalized Raw Internal Pagerank'] = safe_normalize(df['Raw Internal Pagerank'])
        else:
            df['Normalized Raw Internal Pagerank'] = 0

        if "No. of Missed Clicks excluding anonymized queries" in df.columns:
            df['Normalized Missed Clicks'] = safe_normalize(df["No. of Missed Clicks excluding anonymized queries"])
            combined_score += df['Normalized Missed Clicks']
        else:
            df['Normalized Missed Clicks'] = 0

        # Add Botify metrics to the combined score
        combined_score += (-1 * df['Normalized Raw Internal Pagerank'] +
                           df['Normalized Search Volume'] +
                           df['Normalized Search Position'])

    # Apply the combined score to the DataFrame
    df['Combined Score'] = combined_score

    if reorder:
        # Reorder columns if required (using the surgically ported reorder function)
        df = reorder_columns_surgical(df, "CPC", after_col)
        df = reorder_columns_surgical(df, "Keyword Difficulty", "CPC")
        if has_botify_data:
            df = reorder_columns_surgical(df, "Internal Pagerank", "Keyword Difficulty")
            df = reorder_columns_surgical(df, "No. of Unique Inlinks", "Internal Pagerank")
            if "No. of Missed Clicks excluding anonymized queries" in df.columns:
                df = reorder_columns_surgical(df, "No. of Missed Clicks excluding anonymized queries", "No. of Unique Inlinks")

    return df

# %% editable=true slideshow={"slide_type": ""}
import pandas as pd
import xlsxwriter
import itertools
from pathlib import Path
from IPython.display import display
import os
import platform
import subprocess
import ipywidgets as widgets
# NOTE: This cell assumes 'job', 'df', 'competitors', 'semrush_lookup', 'has_botify' are defined.

# --- 1. DEFINE SECURE OUTPUT PATHS ---
# Create the secure, client-specific deliverables folder: Notebooks/deliverables/{job}/
deliverables_dir = Path("deliverables") / job
deliverables_dir.mkdir(parents=True, exist_ok=True)

# Define the final Excel file path (using a standardized, clean name for the file)
xl_filename = f"{semrush_lookup.replace('.', '_').rstrip('_')}_GAPalyzer_{job}_V1.xlsx"
xl_file = deliverables_dir / xl_filename

# --- 2. EXECUTE CORE LOGIC ---
print(f"- Writing Gap Analysis tab to {xl_file.name} (first pass)...")

# Initialize Loop List (verbatim)
loop_list = ["Gap Analysis"]
last_competitor = competitors[-1]

# Apply the normalization and scoring logic
df_tab = normalize_and_score_surgical(df.copy(), semrush_lookup, has_botify, last_competitor, False)

# Save Initial Excel Sheet with xlsxwriter (Engine Kwargs included verbatim)
arg_dict = {'options': {'strings_to_urls': False}}
try:
    with pd.ExcelWriter(xl_file, engine="xlsxwriter", engine_kwargs=arg_dict, mode='w') as writer:
        # NOTE: Using df_tab here, which has the Normalized columns
        df_tab.to_excel(writer, sheet_name='Gap Analysis', index=False)
    
    print("✅ Gap Analysis tab written (Unformatted Pass 1).")
    
except Exception as e:
    print(f"❌ Error writing Excel file: {e}")
    
# --- 3. DISPLAY SECURE EGRESS BUTTON ---

# The button description clearly indicates the file and the security of the folder.
button = widgets.Button(
    description=f"📂 Open Deliverables Folder ({job})",
    tooltip=f"Open {deliverables_dir.resolve()}",
    button_style='success'
)
# 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)

# Store final file path in pipeline
pip.set(job, 'final_xl_file', str(xl_file))
pip.set(job, 'deliverables_folder', str(deliverables_dir))

# %% editable=true slideshow={"slide_type": ""}
# --- CORE SUPPORT FUNCTIONS ---
import pandas as pd
import numpy as np
from pathlib import Path
import os
import platform
import subprocess
import ipywidgets as widgets
from IPython.display import display

# Surgical port of _open_folder from FAQuilizer, necessary for the widget to work
def _open_folder(path_str: str = "."):
    """Opens the specified folder in the system's default file explorer."""
    folder_path = Path(path_str).resolve()
    
    if not folder_path.exists() or not folder_path.is_dir():
        print(f"❌ Error: Path is not a valid directory: {folder_path}")
        return

    system = platform.system()
    try:
        if system == "Windows":
            os.startfile(folder_path)
        elif system == "Darwin":  # macOS
            subprocess.run(["open", folder_path])
        else:  # Linux (xdg-open covers most desktop environments)
            subprocess.run(["xdg-open", folder_path])
    except Exception as e:
        print(f"❌ Failed to open folder. Error: {e}")

# This utility must be defined for normalize_and_score to work
def safe_normalize(series):
    """ Normalize the series safely to avoid divide by zero and handle NaN values. """
    min_val = series.min()
    max_val = series.max()
    range_val = max_val - min_val
    if range_val == 0:
        # Avoid division by zero by returning zero array if no range
        return np.zeros_like(series)
    else:
        # Normalize and fill NaN values that might result from empty/NaN series
        return (series - min_val).div(range_val).fillna(0)

# Surgical port of bf.reorder_columns
def reorder_columns_surgical(df, priority_column, after_column):
    if priority_column in df.columns:
        columns = list(df.columns.drop(priority_column))
        # Handle cases where the after_column may have been dropped earlier
        if after_column not in columns:
            print(f"⚠️ Reorder Error: Target column '{after_column}' not found. Skipping reorder of '{priority_column}'.")
            return df
            
        after_column_index = columns.index(after_column)
        columns.insert(after_column_index + 1, priority_column)
        df = df[columns]
    else:
        print(f"⚠️ Reorder Error: Column '{priority_column}' not found in DataFrame.")
    return df

# Surgical port of bf.normalize_and_score - WITH CRITICAL FIX
def normalize_and_score_surgical(df, registered_domain, has_botify_data, after_col, reorder):
    
    # Rename original column fields to match expected names in the dataframe
    if 'internal_page_rank.raw' in df.columns:
        df = df.rename(columns={'internal_page_rank.raw': 'Raw Internal Pagerank'}, inplace=False)
    
    # --- CRITICAL FIX FOR KEYERROR / TRAILING SLASHES ---
    # The lookup key (e.g., 'nixos.org') must be matched against the DataFrame column (e.g., 'nixos.org/').
    # We clean both for comparison to find the unique canonical key, but use the original column name.
    
    # Clean the lookup domain (assuming the input `registered_domain` might be missing the slash)
    clean_lookup_key = registered_domain.rstrip('/')
    target_col = None
    
    for col in df.columns:
        # Find the column whose stripped name matches the stripped lookup key.
        if col.rstrip('/') == clean_lookup_key:
            target_col = col
            break
            
    if target_col is None:
        raise KeyError(f"Could not find client domain column for '{registered_domain}' in DataFrame. Available columns: {df.columns.tolist()}")
    # --- END CRITICAL FIX ---
    
    # Normalize metrics that are always included
    df['Normalized Search Volume'] = safe_normalize(df['Search Volume'])
    df['Normalized Search Position'] = safe_normalize(df[target_col]) # <-- USES THE FOUND, CANONICAL COLUMN NAME
    df['Normalized Keyword Difficulty'] = safe_normalize(df['Keyword Difficulty'])
    df['Normalized CPC'] = safe_normalize(df['CPC'])

    # Always include CPC and Keyword Difficulty in the combined score
    combined_score = df['Normalized CPC'] - df['Normalized Keyword Difficulty']

    if has_botify_data:
        # Normalize additional Botify metrics if available
        if 'Raw Internal Pagerank' in df.columns:
            df['Normalized Raw Internal Pagerank'] = safe_normalize(df['Raw Internal Pagerank'])
        else:
            df['Normalized Raw Internal Pagerank'] = 0

        if "No. of Missed Clicks excluding anonymized queries" in df.columns:
            df['Normalized Missed Clicks'] = safe_normalize(df["No. of Missed Clicks excluding anonymized queries"])
            combined_score += df['Normalized Missed Clicks']
        else:
            df['Normalized Missed Clicks'] = 0

        # Add Botify metrics to the combined score
        combined_score += (-1 * df['Normalized Raw Internal Pagerank'] +
                           df['Normalized Search Volume'] +
                           df['Normalized Search Position'])

    # Apply the combined score to the DataFrame
    df['Combined Score'] = combined_score

    if reorder:
        # Reorder columns if required (using the surgically ported reorder function)
        df = reorder_columns_surgical(df, "CPC", after_col)
        df = reorder_columns_surgical(df, "Keyword Difficulty", "CPC")
        if has_botify_data:
            df = reorder_columns_surgical(df, "Internal Pagerank", "Keyword Difficulty")
            df = reorder_columns_surgical(df, "No. of Unique Inlinks", "Internal Pagerank")
            if "No. of Missed Clicks excluding anonymized queries" in df.columns:
                df = reorder_columns_surgical(df, "No. of Missed Clicks excluding anonymized queries", "No. of Unique Inlinks")

    return df

# %% editable=true slideshow={"slide_type": ""}
import pandas as pd
import xlsxwriter
import itertools
from pathlib import Path
from IPython.display import display
import os
import platform
import subprocess
import ipywidgets as widgets
# NOTE: This cell assumes 'job', 'df', 'competitors', 'semrush_lookup', 'has_botify' are defined.

# --- 1. DEFINE SECURE OUTPUT PATHS ---
# Create the secure, client-specific deliverables folder: Notebooks/deliverables/{job}/
deliverables_dir = Path("deliverables") / job
deliverables_dir.mkdir(parents=True, exist_ok=True)

# Define the final Excel file path (using a standardized, clean name for the file)
# We ensure the filename is clean by stripping the trailing slash/underscore that might be left by SEMRush input.
xl_filename = f"{semrush_lookup.replace('.', '_').rstrip('_')}_GAPalyzer_{job}_V1.xlsx"
xl_file = deliverables_dir / xl_filename

# --- 2. EXECUTE CORE LOGIC ---
print(f"- Writing Gap Analysis tab to {xl_file.name} (first pass)...")

# Initialize Loop List (verbatim)
loop_list = ["Gap Analysis"]
last_competitor = competitors[-1]

# Apply the normalization and scoring logic
# The target_col lookup inside this function will now succeed.
df_tab = normalize_and_score_surgical(df.copy(), semrush_lookup, has_botify, last_competitor, False)

# Save Initial Excel Sheet with xlsxwriter (Engine Kwargs included verbatim)
arg_dict = {'options': {'strings_to_urls': False}}
try:
    with pd.ExcelWriter(xl_file, engine="xlsxwriter", engine_kwargs=arg_dict, mode='w') as writer:
        # NOTE: Using df_tab here, which has the Normalized columns
        df_tab.to_excel(writer, sheet_name='Gap Analysis', index=False)
    
    print("✅ Gap Analysis tab written (Unformatted Pass 1).")
    
except Exception as e:
    print(f"❌ Error writing Excel file: {e}")
    
# --- 3. DISPLAY SECURE EGRESS BUTTON ---

# The button description clearly indicates the file and the security of the folder.
button = widgets.Button(
    description=f"📂 Open Deliverables Folder ({job})",
    tooltip=f"Open {deliverables_dir.resolve()}",
    button_style='success'
)
# 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)

# Store final file path in pipeline
pip.set(job, 'final_xl_file', str(xl_file))
pip.set(job, 'deliverables_folder', str(deliverables_dir))