Mastering Excel Workflows: A Journey Through Data Structures, Debugging, and AI Collaboration

filter_name = "Important Keywords"
if filter_name in tabs:
    # Function to read keywords from quickbooks.txt
    def read_keywords(file_path):
        with open(file_path, 'r') as file:
            important_keywords_list = [line.strip() for line in file.readlines()]
        return important_keywords_list
    
    # Function to filter dataframe based on keywords
    def filter_df_by_keywords(df, keywords):
        return df[df["Keyword"].isin(keywords)]

    # Read keywords from quickbooks.txt
    important_keywords_list = read_keywords(important_keywords_file)
    # Filter the dataframe based on quickbooks.txt keywords
    loop_list.append(filter_name)
    print(f"- Writing {filter_name} tab...")
    df_filtered = filter_df_by_keywords(df.copy(), important_keywords_list)
    df_filtered = bf.normalize_and_score(df_filtered, semrush_lookup, has_botify, last_competitor, True)
    df_filtered.sort_values(by='Combined Score', ascending=False, inplace=True)
    with pd.ExcelWriter(xl_file, engine="openpyxl", mode='a') as writer:
        df_filtered.to_excel(writer, sheet_name=filter_name, index=False)

# Write Best Opportunties or Striking Distance based on whether Botify data available
filter_name = "Best Opportunities"
striking_lower = 100
if filter_name in tabs:
    loop_list.append(filter_name)
    
    if has_botify:
        try:
            df_tab = df[(df["No. of Impressions excluding anonymized queries"] > 0) & (df[semrush_lookup] > 3)].copy()
        except:
            # Horrible soluiton. Rethink!
            df_tab = df[(df[semrush_lookup] >= 4) & (df[semrush_lookup] <= striking_lower)]
    else:
        df_tab = df[(df[semrush_lookup] >= 4) & (df[semrush_lookup] <= striking_lower)]
    df_tab = bf.normalize_and_score(df_tab.copy(), semrush_lookup, has_botify, last_competitor, True)
    df_tab.sort_values(by='Combined Score', ascending=False, inplace=True)
    
    # Write out the tab the same way in either case
    print(f"- Writing {filter_name} tab...")
    with pd.ExcelWriter(xl_file, engine="openpyxl", mode='a') as writer:
        df_tab.to_excel(writer, sheet_name=filter_name, index=False)

filter_name = "Important Keywords disable"
if filter_name in tabs:
    loop_list.append(filter_name)

    df_tab = df[df[semrush_lookup].notnull()].sort_values(by=[semrush_lookup, 'Search Volume'], ascending=[True, False])
    
    # Write out the tab the same way in either case
    print(f"- Writing {filter_name} tab...")
    with pd.ExcelWriter(xl_file, engine="openpyxl", mode='a') as writer:
        df_tab.to_excel(writer, sheet_name=filter_name, index=False)

# Define each filter's keyword processing and apply the best opportunity logic if appropriate
for filter_name, keywords in [
    ("Gifts", [
        'gift',
        'gifts',
        'idea',
        'ideas',
        'present',
        'presents',
        'give',
        'giving',
        'black friday',
        'cyber monday',
        'cyber week',
        'bfcm',
        'bf',
        'cm',
        'holiday',
        'deals',
        'sales',
        'offer',
        'discount',
        'shopping'
    ]),

    ("Broad Questions", '''
        am are can could did do does for from had has have how i is may might must shall should was were what when where which who whom whose why will with would
        '''.split()),
    ("Narrow Questions", '''
        who whom whose what which where when why how
        '''.split()),
    ("Near Me", ['near me', 'for sale', 'nearby', 'closest', 'near you', 'local'])]:

    if filter_name in tabs:
        loop_list.append(filter_name)
        print(f"- Writing {filter_name} tab...")
        pattern = r'\b(?:' + '|'.join(keywords) + r')\b'
        df_tab = df[df["Keyword"].str.contains(pattern, case=False, na=False)]
        df_tab = df_tab[(df_tab[semrush_lookup] >= 4) | (df_tab[semrush_lookup].isna())]
        df_tab = bf.normalize_and_score(df_tab, semrush_lookup, has_botify, last_competitor, True)
        df_tab.sort_values(by='Combined Score', ascending=False, inplace=True)
        with pd.ExcelWriter(xl_file, engine="openpyxl", mode='a') as writer:
            df_tab.to_excel(writer, sheet_name=filter_name, index=False)

print("Done writing Excel tabs.")

# %% editable=true slideshow={"slide_type": ""}
import pandas as pd
import xlsxwriter # Used in the first pass
import openpyxl # Used in append mode ('a') for subsequent tabs
import re
from pathlib import Path
from collections import Counter
from nltk import bigrams
from nltk.corpus import stopwords
from nltk.stem import PorterStemmer
from nltk.tokenize import word_tokenize
import numpy as np

# NOTE: This cell assumes 'job', 'df', 'semrush_lookup', 'has_botify', 
#       'xl_file', 'competitors', and helper functions are defined.

# --- PATH DEFINITION ---
# The intermediate file location for reading filter keywords
important_keywords_file = Path("data") / f"{job}_important_keywords.txt" 
# Assuming this file exists and is user-editable, residing next to the filter_keywords.csv

# --- REQUIRED SUPPORT FUNCTIONS (Surgical Ports from previous definitions) ---

def reorder_columns_surgical(df, priority_column, after_column):
    # NOTE: This is the critical function used by normalize_and_score_surgical
    if priority_column in df.columns:
        columns = list(df.columns.drop(priority_column))
        if after_column not in columns:
            return df # Silently fail reorder if column is missing (as per past instruction)
        after_column_index = columns.index(after_column)
        columns.insert(after_column_index + 1, priority_column)
        return df[columns]
    return df

def safe_normalize(series):
    # NOTE: This is the critical normalization utility
    min_val = series.min()
    max_val = series.max()
    range_val = max_val - min_val
    if range_val == 0:
        return np.zeros_like(series)
    else:
        return (series - min_val).div(range_val).fillna(0)


def normalize_and_score_surgical(df, registered_domain, has_botify_data, after_col, reorder):
    # This must be included since it's the core scoring logic
    if 'internal_page_rank.raw' in df.columns:
        df = df.rename(columns={'internal_page_rank.raw': 'Raw Internal Pagerank'}, inplace=False)
    
    # --- Critical Fix to find the target column ---
    clean_lookup_key = registered_domain.rstrip('/')
    target_col = None
    for col in df.columns:
        if col.rstrip('/') == clean_lookup_key:
            target_col = col
            break
            
    if target_col is None:
        # Fallback to the provided key if it's not found (may raise KeyError on next line)
        target_col = registered_domain
    # --- End Critical Fix ---

    df['Normalized Search Volume'] = safe_normalize(df['Search Volume'])
    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'])

    combined_score = df['Normalized CPC'] - df['Normalized Keyword Difficulty']

    if has_botify_data:
        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

        combined_score += (-1 * df['Normalized Raw Internal Pagerank'] +
                           df['Normalized Search Volume'] +
                           df['Normalized Search Position'])

    df['Combined Score'] = combined_score

    if reorder:
        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

# --- Filter-Specific Functions (Copied Verbatim) ---

def read_keywords(file_path):
    """Function to read keywords from a file."""
    if not file_path.exists():
        print(f"⚠️ Warning: Keywords file not found at {file_path}. Skipping file-based filter.")
        return []
    with open(file_path, 'r') as file:
        important_keywords_list = [line.strip() for line in file.readlines()]
    return important_keywords_list
    
def filter_df_by_keywords(df, keywords):
    """Function to filter dataframe based on an exact list of keywords."""
    return df[df["Keyword"].isin(keywords)]


# --- MAIN TAB GENERATION LOGIC ---

print("Starting subsequent Excel tab generation (Appending via openpyxl)...")

# --- 1. Filter: Important Keywords (File-Based Filter) ---
filter_name = "Important Keywords"

# Check for file existence to prevent crashes, but execute logic if file is present
if important_keywords_file.exists():
    important_keywords_list = read_keywords(important_keywords_file)
    if important_keywords_list:
        print(f"- Writing {filter_name} tab (via file list)...")
        # Ensure 'df' is the working DataFrame (the truncated one)
        df_filtered = filter_df_by_keywords(df.copy(), important_keywords_list)
        
        # Apply scoring and reordering (reorder=True)
        df_filtered = normalize_and_score_surgical(df_filtered, semrush_lookup, has_botify, competitors[-1], True)
        df_filtered.sort_values(by='Combined Score', ascending=False, inplace=True)
        
        # Use openpyxl in append mode
        with pd.ExcelWriter(xl_file, engine="openpyxl", mode='a') as writer:
            df_filtered.to_excel(writer, sheet_name=filter_name, index=False)
    else:
        print(f"☑️ Skipping '{filter_name}': Keyword file is empty.")
else:
    print(f"☑️ Skipping '{filter_name}': Keywords file does not exist.")


# --- 2. Filter: Best Opportunities / Striking Distance ---
filter_name = "Best Opportunities"
striking_lower = 100

df_tab = df.copy()

if has_botify:
    # Original logic attempts complex filter, falls back to SEMRush-only if fields fail
    try:
        # NOTE: Using the GSC field names as they typically appear in the DF when GSC data is present.
        df_tab = df_tab[(df_tab["No. of Impressions excluding anonymized queries"] > 0) & (df_tab[semrush_lookup] > 3)].copy()
        print(f"- Writing {filter_name} tab (Botify/GSC Striking Distance)...")
    except KeyError:
        # Horrible soluiton. Rethink! (Using the verbatim comment)
        # Fallback logic if GSC-related columns don't exist
        df_tab = df[(df[semrush_lookup] >= 4) & (df[semrush_lookup] <= striking_lower)].copy()
        print(f"- Writing {filter_name} tab (SEMRush Striking Distance fallback)...")
else:
    # SEMRush-only logic if Botify data was never loaded
    df_tab = df[(df[semrush_lookup] >= 4) & (df[semrush_lookup] <= striking_lower)].copy()
    print(f"- Writing {filter_name} tab (SEMRush Striking Distance)...")
    
# Apply scoring, reorder, and sort
df_tab = normalize_and_score_surgical(df_tab.copy(), semrush_lookup, has_botify, competitors[-1], True)
df_tab.sort_values(by='Combined Score', ascending=False, inplace=True)

# Write out the tab the same way in either case
with pd.ExcelWriter(xl_file, engine="openpyxl", mode='a') as writer:
    df_tab.to_excel(writer, sheet_name=filter_name, index=False)


# --- 3. Filter: Important Keywords disable (Client Ranking Sort) ---
filter_name = "Important Keywords disable"

# Filter: Keywords where the client is ranking (position is not null)
df_tab = df[df[semrush_lookup].notnull()].sort_values(by=[semrush_lookup, 'Search Volume'], ascending=[True, False]).copy()
    
print(f"- Writing {filter_name} tab (Client Rank Sort)...")
# Write out the tab
with pd.ExcelWriter(xl_file, engine="openpyxl", mode='a') as writer:
    df_tab.to_excel(writer, sheet_name=filter_name, index=False)


# --- 4. Loop: Targeted Keyword Filters (Gifts, Questions, Near Me) ---

targeted_filters = [
    ("Gifts", [
        'gift', 'gifts', 'idea', 'ideas', 'present', 'presents', 'give', 'giving', 
        'black friday', 'cyber monday', 'cyber week', 'bfcm', 'bf', 'cm', 
        'holiday', 'deals', 'sales', 'offer', 'discount', 'shopping'
    ]),
    ("Broad Questions", '''
        am are can could did do does for from had has have how i is may might must shall should was were what when where which who whom whose why will with would
        '''.split()),
    ("Narrow Questions", '''
        who whom whose what which where when why how
        '''.split()),
    ("Near Me", ['near me', 'for sale', 'nearby', 'closest', 'near you', 'local'])
]

for filter_name, keywords in targeted_filters:
    print(f"- Writing {filter_name} tab...")
    
    # Construct the RegEx pattern using word boundaries for precise matching
    pattern = r'\b(?:' + '|'.join(keywords) + r')\b'
    
    # Filter 1: Find keywords matching the pattern (e.g., 'gift', 'how', 'near me')
    df_tab = df[df["Keyword"].str.contains(pattern, case=False, na=False)].copy()
    
    # Filter 2: Apply Striking Distance Logic 
    # Keep keywords where client is ranking (pos >= 4) OR client is not ranking at all (NaN)
    df_tab = df_tab[(df_tab[semrush_lookup] >= 4) | (df_tab[semrush_lookup].isna())]
    
    # Apply scoring, reorder, and sort
    df_tab = normalize_and_score_surgical(df_tab.copy(), semrush_lookup, has_botify, competitors[-1], True)
    df_tab.sort_values(by='Combined Score', ascending=False, inplace=True)
    
    # Write out the tab
    with pd.ExcelWriter(xl_file, engine="openpyxl", mode='a') as writer:
        df_tab.to_excel(writer, sheet_name=filter_name, index=False)


print("✅ Done writing all supplementary Excel tabs.")

# The raw DataFrame filtering logic is outside the helper function:
df_tab = df[(df[semrush_lookup] >= 4) & (df[semrush_lookup] <= striking_lower)].copy()

# %% editable=true slideshow={"slide_type": ""}
import pandas as pd
import xlsxwriter
import openpyxl
import re
from pathlib import Path
from collections import Counter
from nltk import bigrams
from nltk.corpus import stopwords
from nltk.stem import PorterStemmer
from nltk.tokenize import word_tokenize
import numpy as np

# NOTE: This cell assumes 'job', 'df', 'semrush_lookup', 'has_botify', 
#       'xl_file', 'competitors', and helper functions are defined.

# --- PATH DEFINITION ---
important_keywords_file = Path("data") / f"{job}_important_keywords.txt" 

# --- KEY LOOKUP FIX: Determine the actual competitor column name ---
clean_lookup_key = semrush_lookup.rstrip('/')
TARGET_COMPETITOR_COL = None

for col in df.columns:
    if col.rstrip('/') == clean_lookup_key:
        TARGET_COMPETITOR_COL = col
        break

if TARGET_COMPETITOR_COL is None:
    raise KeyError(f"CRITICAL ERROR: Could not find canonical competitor column for '{semrush_lookup}' in DataFrame. Available columns: {df.columns.tolist()}")

# --- Filter-Specific Functions (Copied Verbatim) ---

def read_keywords(file_path):
    """Function to read keywords from a file."""
    if not file_path.exists():
        print(f"⚠️ Warning: Keywords file not found at {file_path}. Skipping file-based filter.")
        return []
    with open(file_path, 'r') as file:
        important_keywords_list = [line.strip() for line in file.readlines()]
    return important_keywords_list
    
def filter_df_by_keywords(df, keywords):
    """Function to filter dataframe based on an exact list of keywords."""
    return df[df["Keyword"].isin(keywords)]

# --- MAIN TAB GENERATION LOGIC ---

print("Starting subsequent Excel tab generation (Appending via openpyxl)...")

# --- 1. Filter: Important Keywords (File-Based Filter) ---
filter_name = "Important Keywords"

if important_keywords_file.exists():
    important_keywords_list = read_keywords(important_keywords_file)
    if important_keywords_list:
        print(f"- Writing {filter_name} tab (via file list)...")
        
        df_filtered = filter_df_by_keywords(df.copy(), important_keywords_list)
        
        # Apply scoring and reordering (reorder=True)
        df_filtered = normalize_and_score_surgical(df_filtered, semrush_lookup, has_botify, competitors[-1], True)
        df_filtered.sort_values(by='Combined Score', ascending=False, inplace=True)
        
        with pd.ExcelWriter(xl_file, engine="openpyxl", mode='a') as writer:
            df_filtered.to_excel(writer, sheet_name=filter_name, index=False)
    else:
        print(f"☑️ Skipping '{filter_name}': Keyword file is empty.")
else:
    print(f"☑️ Skipping '{filter_name}': Keywords file does not exist.")


# --- 2. Filter: Best Opportunities / Striking Distance ---
filter_name = "Best Opportunities"
striking_lower = 100

df_tab = df.copy()

if has_botify:
    # Original logic attempts complex filter, falls back to SEMRush-only if fields fail
    try:
        # FIXED: Use TARGET_COMPETITOR_COL instead of semrush_lookup
        df_tab = df_tab[(df_tab["No. of Impressions excluding anonymized queries"] > 0) & (df_tab[TARGET_COMPETITOR_COL] > 3)].copy()
        print(f"- Writing {filter_name} tab (Botify/GSC Striking Distance)...")
    except KeyError:
        # Fallback uses TARGET_COMPETITOR_COL
        df_tab = df[(df[TARGET_COMPETITOR_COL] >= 4) & (df[TARGET_COMPETITOR_COL] <= striking_lower)].copy()
        print(f"- Writing {filter_name} tab (SEMRush Striking Distance fallback)...")
else:
    # SEMRush-only logic uses TARGET_COMPETITOR_COL
    df_tab = df[(df[TARGET_COMPETITOR_COL] >= 4) & (df[TARGET_COMPETITOR_COL] <= striking_lower)].copy()
    print(f"- Writing {filter_name} tab (SEMRush Striking Distance)...")
    
# Apply scoring, reorder, and sort
df_tab = normalize_and_score_surgical(df_tab.copy(), semrush_lookup, has_botify, competitors[-1], True)
df_tab.sort_values(by='Combined Score', ascending=False, inplace=True)

# Write out the tab the same way in either case
with pd.ExcelWriter(xl_file, engine="openpyxl", mode='a') as writer:
    df_tab.to_excel(writer, sheet_name=filter_name, index=False)


# --- 3. Filter: Important Keywords disable (Client Ranking Sort) ---
filter_name = "Important Keywords disable"

# Filter: Keywords where the client is ranking (position is not null)
# FIXED: Use TARGET_COMPETITOR_COL
df_tab = df[df[TARGET_COMPETITOR_COL].notnull()].sort_values(by=[TARGET_COMPETITOR_COL, 'Search Volume'], ascending=[True, False]).copy()
    
print(f"- Writing {filter_name} tab (Client Rank Sort)...")
# Write out the tab
with pd.ExcelWriter(xl_file, engine="openpyxl", mode='a') as writer:
    df_tab.to_excel(writer, sheet_name=filter_name, index=False)


# --- 4. Loop: Targeted Keyword Filters (Gifts, Questions, Near Me) ---

targeted_filters = [
    ("Gifts", [
        'gift', 'gifts', 'idea', 'ideas', 'present', 'presents', 'give', 'giving', 
        'black friday', 'cyber monday', 'cyber week', 'bfcm', 'bf', 'cm', 
        'holiday', 'deals', 'sales', 'offer', 'discount', 'shopping'
    ]),
    ("Broad Questions", '''
        am are can could did do does for from had has have how i is may might must shall should was were what when where which who whom whose why will with would
        '''.split()),
    ("Narrow Questions", '''
        who whom whose what which where when why how
        '''.split()),
    ("Near Me", ['near me', 'for sale', 'nearby', 'closest', 'near you', 'local'])
]

for filter_name, keywords in targeted_filters:
    print(f"- Writing {filter_name} tab...")
    
    # Construct the RegEx pattern using word boundaries for precise matching
    pattern = r'\b(?:' + '|'.join([re.escape(k) for k in keywords]) + r')\b'
    
    # Filter 1: Find keywords matching the pattern (e.g., 'gift', 'how', 'near me')
    df_tab = df[df["Keyword"].str.contains(pattern, case=False, na=False)].copy()
    
    # Filter 2: Apply Striking Distance Logic 
    # Keep keywords where client is ranking (pos >= 4) OR client is not ranking at all (NaN)
    # FIXED: Use TARGET_COMPETITOR_COL
    df_tab = df_tab[(df_tab[TARGET_COMPETITOR_COL] >= 4) | (df_tab[TARGET_COMPETITOR_COL].isna())]
    
    # Apply scoring, reorder, and sort
    df_tab = normalize_and_score_surgical(df_tab.copy(), semrush_lookup, has_botify, competitors[-1], True)
    df_tab.sort_values(by='Combined Score', ascending=False, inplace=True)
    
    # Write out the tab
    with pd.ExcelWriter(xl_file, engine="openpyxl", mode='a') as writer:
        df_tab.to_excel(writer, sheet_name=filter_name, index=False)


print("✅ Done writing all supplementary Excel tabs.")

# %% editable=true slideshow={"slide_type": ""}
import pandas as pd
import xlsxwriter
import openpyxl
import re
from pathlib import Path
from collections import Counter
from nltk import bigrams
from nltk.corpus import stopwords
from nltk.stem import PorterStemmer
from nltk.tokenize import word_tokenize
import numpy as np

# NOTE: This cell assumes 'job', 'df', 'semrush_lookup', 'has_botify', 
#       'xl_file', 'competitors', and helper functions are defined.

# --- PATH DEFINITION ---
# The intermediate file location for reading filter keywords
important_keywords_file = Path("data") / f"{job}_important_keywords.txt" 

# --- REQUIRED SUPPORT FUNCTIONS (Surgical Ports from previous definitions) ---

def reorder_columns_surgical(df, priority_column, after_column):
    # NOTE: This is the critical function used by normalize_and_score_surgical
    if priority_column in df.columns:
        columns = list(df.columns.drop(priority_column))
        if after_column not in columns:
            return df # Silently fail reorder if column is missing (as per past instruction)
        after_column_index = columns.index(after_column)
        columns.insert(after_column_index + 1, priority_column)
        return df[columns]
    return df

def safe_normalize(series):
    # NOTE: This is the critical normalization utility
    min_val = series.min()
    max_val = series.max()
    range_val = max_val - min_val
    if range_val == 0:
        return np.zeros_like(series)
    else:
        return (series - min_val).div(range_val).fillna(0)


def normalize_and_score_surgical(df, registered_domain, has_botify_data, after_col, reorder):
    # This must be included since it's the core scoring logic
    if 'internal_page_rank.raw' in df.columns:
        df = df.rename(columns={'internal_page_rank.raw': 'Raw Internal Pagerank'}, inplace=False)
    
    # --- Critical Fix to find the target column ---
    clean_lookup_key = registered_domain.rstrip('/')
    target_col = None
    for col in df.columns:
        if col.rstrip('/') == clean_lookup_key:
            target_col = col
            break
            
    if target_col is None:
        target_col = registered_domain # Fallback to original key if not found
    # --- End Critical Fix ---

    df['Normalized Search Volume'] = safe_normalize(df['Search Volume'])
    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'])

    combined_score = df['Normalized CPC'] - df['Normalized Keyword Difficulty']

    if has_botify_data:
        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

        combined_score += (-1 * df['Normalized Raw Internal Pagerank'] +
                           df['Normalized Search Volume'] +
                           df['Normalized Search Position'])

    df['Combined Score'] = combined_score

    if reorder:
        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

# --- Filter-Specific Functions (Copied Verbatim) ---

def read_keywords(file_path):
    """Function to read keywords from a file."""
    if not file_path.exists():
        print(f"⚠️ Warning: Keywords file not found at {file_path}. Skipping file-based filter.")
        return []
    with open(file_path, 'r') as file:
        important_keywords_list = [line.strip() for line in file.readlines()]
    return important_keywords_list
    
def filter_df_by_keywords(df, keywords):
    """Function to filter dataframe based on an exact list of keywords."""
    return df[df["Keyword"].isin(keywords)]


# --- MAIN TAB GENERATION LOGIC ---

print("Starting subsequent Excel tab generation (Appending via openpyxl)...")

# --- 1. Filter: Important Keywords (File-Based Filter) ---
filter_name = "Important Keywords"

if important_keywords_file.exists():
    important_keywords_list = read_keywords(important_keywords_file)
    if important_keywords_list:
        print(f"- Writing {filter_name} tab (via file list)...")
        
        df_filtered = filter_df_by_keywords(df.copy(), important_keywords_list)
        
        # Apply scoring and reordering (reorder=True)
        df_filtered = normalize_and_score_surgical(df_filtered, semrush_lookup, has_botify, competitors[-1], True)
        df_filtered.sort_values(by='Combined Score', ascending=False, inplace=True)
        
        # FIX 1: Add if_sheet_exists='replace'
        with pd.ExcelWriter(xl_file, engine="openpyxl", mode='a', if_sheet_exists='replace') as writer:
            df_filtered.to_excel(writer, sheet_name=filter_name, index=False)
    else:
        print(f"☑️ Skipping '{filter_name}': Keyword file is empty.")
else:
    print(f"☑️ Skipping '{filter_name}': Keywords file does not exist.")


# --- 2. Filter: Best Opportunities / Striking Distance ---
filter_name = "Best Opportunities"
striking_lower = 100

df_tab = df.copy()

if has_botify:
    # Original logic attempts complex filter, falls back to SEMRush-only if fields fail
    try:
        # FIXED: Use TARGET_COMPETITOR_COL instead of semrush_lookup (This is done inside normalize_and_score)
        df_tab = df_tab[(df_tab["No. of Impressions excluding anonymized queries"] > 0) & (df_tab[TARGET_COMPETITOR_COL] > 3)].copy()
        print(f"- Writing {filter_name} tab (Botify/GSC Striking Distance)...")
    except KeyError:
        # Fallback uses TARGET_COMPETITOR_COL
        df_tab = df[(df[TARGET_COMPETITOR_COL] >= 4) & (df[TARGET_COMPETITOR_COL] <= striking_lower)].copy()
        print(f"- Writing {filter_name} tab (SEMRush Striking Distance fallback)...")
else:
    # SEMRush-only logic uses TARGET_COMPETITOR_COL
    df_tab = df[(df[TARGET_COMPETITOR_COL] >= 4) & (df[TARGET_COMPETITOR_COL] <= striking_lower)].copy()
    print(f"- Writing {filter_name} tab (SEMRush Striking Distance)...")
    
# Apply scoring, reorder, and sort
df_tab = normalize_and_score_surgical(df_tab.copy(), semrush_lookup, has_botify, competitors[-1], True)
df_tab.sort_values(by='Combined Score', ascending=False, inplace=True)

# FIX 2: Add if_sheet_exists='replace'
with pd.ExcelWriter(xl_file, engine="openpyxl", mode='a', if_sheet_exists='replace') as writer:
    df_tab.to_excel(writer, sheet_name=filter_name, index=False)


# --- 3. Filter: Important Keywords disable (Client Ranking Sort) ---
filter_name = "Important Keywords disable"

# Filter: Keywords where the client is ranking (position is not null)
# FIXED: Use TARGET_COMPETITOR_COL
df_tab = df[df[TARGET_COMPETITOR_COL].notnull()].sort_values(by=[TARGET_COMPETITOR_COL, 'Search Volume'], ascending=[True, False]).copy()
    
print(f"- Writing {filter_name} tab (Client Rank Sort)...")
# FIX 3: Add if_sheet_exists='replace'
with pd.ExcelWriter(xl_file, engine="openpyxl", mode='a', if_sheet_exists='replace') as writer:
    df_tab.to_excel(writer, sheet_name=filter_name, index=False)


# --- 4. Loop: Targeted Keyword Filters (Gifts, Questions, Near Me) ---

targeted_filters = [
    ("Gifts", [
        'gift', 'gifts', 'idea', 'ideas', 'present', 'presents', 'give', 'giving', 
        'black friday', 'cyber monday', 'cyber week', 'bfcm', 'bf', 'cm', 
        'holiday', 'deals', 'sales', 'offer', 'discount', 'shopping'
    ]),
    ("Broad Questions", '''
        am are can could did do does for from had has have how i is may might must shall should was were what when where which who whom whose why will with would
        '''.split()),
    ("Narrow Questions", '''
        who whom whose what which where when why how
        '''.split()),
    ("Near Me", ['near me', 'for sale', 'nearby', 'closest', 'near you', 'local'])
]

for filter_name, keywords in targeted_filters:
    print(f"- Writing {filter_name} tab...")
    
    # Construct the RegEx pattern using word boundaries for precise matching
    pattern = r'\b(?:' + '|'.join([re.escape(k) for k in keywords]) + r')\b'
    
    # Filter 1: Find keywords matching the pattern (e.g., 'gift', 'how', 'near me')
    df_tab = df[df["Keyword"].str.contains(pattern, case=False, na=False)].copy()
    
    # Filter 2: Apply Striking Distance Logic 
    # Keep keywords where client is ranking (pos >= 4) OR client is not ranking at all (NaN)
    # FIXED: Use TARGET_COMPETITOR_COL
    df_tab = df_tab[(df_tab[TARGET_COMPETITOR_COL] >= 4) | (df_tab[TARGET_COMPETITOR_COL].isna())]
    
    # Apply scoring, reorder, and sort
    df_tab = normalize_and_score_surgical(df_tab.copy(), semrush_lookup, has_botify, competitors[-1], True)
    df_tab.sort_values(by='Combined Score', ascending=False, inplace=True)
    
    # FIX 4: Add if_sheet_exists='replace'
    with pd.ExcelWriter(xl_file, engine="openpyxl", mode='a', if_sheet_exists='replace') as writer:
        df_tab.to_excel(writer, sheet_name=filter_name, index=False)


print("✅ Done writing all supplementary Excel tabs.")

# %% editable=true slideshow={"slide_type": ""}
import pandas as pd
import xlsxwriter
import openpyxl
import re
from pathlib import Path
from collections import Counter
from nltk import bigrams
from nltk.corpus import stopwords
from nltk.stem import PorterStemmer
from nltk.tokenize import word_tokenize
import numpy as np

# NOTE: This cell assumes 'job', 'df', 'semrush_lookup', 'has_botify', 
#       'xl_file', 'competitors', and helper functions are defined.

# --- PATH DEFINITION ---
# The intermediate file location for reading filter keywords
important_keywords_file = Path("data") / f"{job}_important_keywords.txt" 

# --- REQUIRED SUPPORT FUNCTIONS (Surgical Ports from previous definitions) ---

def reorder_columns_surgical(df, priority_column, after_column):
    # NOTE: This is the critical function used by normalize_and_score_surgical
    if priority_column in df.columns:
        columns = list(df.columns.drop(priority_column))
        if after_column not in columns:
            return df # Silently fail reorder if column is missing (as per past instruction)
        after_column_index = columns.index(after_column)
        columns.insert(after_column_index + 1, priority_column)
        return df[columns]
    return df

def safe_normalize(series):
    # NOTE: This is the critical normalization utility
    min_val = series.min()
    max_val = series.max()
    range_val = max_val - min_val
    if range_val == 0:
        return np.zeros_like(series)
    else:
        return (series - min_val).div(range_val).fillna(0)


def normalize_and_score_surgical(df, registered_domain, has_botify_data, after_col, reorder):
    # This must be included since it's the core scoring logic
    if 'internal_page_rank.raw' in df.columns:
        df = df.rename(columns={'internal_page_rank.raw': 'Raw Internal Pagerank'}, inplace=False)
    
    # --- Critical Fix to find the target column ---
    clean_lookup_key = registered_domain.rstrip('/')
    target_col = None
    for col in df.columns:
        if col.rstrip('/') == clean_lookup_key:
            target_col = col
            break
            
    if target_col is None:
        target_col = registered_domain # Fallback to original key if not found
    # --- End Critical Fix ---

    df['Normalized Search Volume'] = safe_normalize(df['Search Volume'])
    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'])

    combined_score = df['Normalized CPC'] - df['Normalized Keyword Difficulty']

    if has_botify_data:
        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

        combined_score += (-1 * df['Normalized Raw Internal Pagerank'] +
                           df['Normalized Search Volume'] +
                           df['Normalized Search Position'])

    df['Combined Score'] = combined_score

    if reorder:
        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

# --- Filter-Specific Functions (Copied Verbatim) ---

def read_keywords(file_path):
    """Function to read keywords from a file."""
    if not file_path.exists():
        print(f"⚠️ Warning: Keywords file not found at {file_path}. Skipping file-based filter.")
        return []
    with open(file_path, 'r') as file:
        important_keywords_list = [line.strip() for line in file.readlines()]
    return important_keywords_list
    
def filter_df_by_keywords(df, keywords):
    """Function to filter dataframe based on an exact list of keywords."""
    return df[df["Keyword"].isin(keywords)]


# --- MAIN TAB GENERATION LOGIC ---

print("Starting subsequent Excel tab generation (Appending via openpyxl)...")

# --- 1. Filter: Important Keywords (File-Based Filter) ---
filter_name = "Important Keywords"

if important_keywords_file.exists():
    important_keywords_list = read_keywords(important_keywords_file)
    if important_keywords_list:
        print(f"- Writing {filter_name} tab (via file list)...")
        
        df_filtered = filter_df_by_keywords(df.copy(), important_keywords_list)
        
        # Apply scoring and reordering (reorder=True)
        df_filtered = normalize_and_score_surgical(df_filtered, semrush_lookup, has_botify, competitors[-1], True)
        df_filtered.sort_values(by='Combined Score', ascending=False, inplace=True)
        
        # FIX 1: Add if_sheet_exists='replace'
        with pd.ExcelWriter(xl_file, engine="openpyxl", mode='a', if_sheet_exists='replace') as writer:
            df_filtered.to_excel(writer, sheet_name=filter_name, index=False)
    else:
        print(f"☑️ Skipping '{filter_name}': Keyword file is empty.")
else:
    print(f"☑️ Skipping '{filter_name}': Keywords file does not exist.")


# --- 2. Filter: Best Opportunities / Striking Distance ---
filter_name = "Best Opportunities"
striking_lower = 100

df_tab = df.copy()

if has_botify:
    # Original logic attempts complex filter, falls back to SEMRush-only if fields fail
    try:
        # FIXED: Use TARGET_COMPETITOR_COL instead of semrush_lookup (This is done inside normalize_and_score)
        df_tab = df_tab[(df_tab["No. of Impressions excluding anonymized queries"] > 0) & (df_tab[TARGET_COMPETITOR_COL] > 3)].copy()
        print(f"- Writing {filter_name} tab (Botify/GSC Striking Distance)...")
    except KeyError:
        # Fallback uses TARGET_COMPETITOR_COL
        df_tab = df[(df[TARGET_COMPETITOR_COL] >= 4) & (df[TARGET_COMPETITOR_COL] <= striking_lower)].copy()
        print(f"- Writing {filter_name} tab (SEMRush Striking Distance fallback)...")
else:
    # SEMRush-only logic uses TARGET_COMPETITOR_COL
    df_tab = df[(df[TARGET_COMPETITOR_COL] >= 4) & (df[TARGET_COMPETITOR_COL] <= striking_lower)].copy()
    print(f"- Writing {filter_name} tab (SEMRush Striking Distance)...")
    
# Apply scoring, reorder, and sort
df_tab = normalize_and_score_surgical(df_tab.copy(), semrush_lookup, has_botify, competitors[-1], True)
df_tab.sort_values(by='Combined Score', ascending=False, inplace=True)

# FIX 2: Add if_sheet_exists='replace'
with pd.ExcelWriter(xl_file, engine="openpyxl", mode='a', if_sheet_exists='replace') as writer:
    df_tab.to_excel(writer, sheet_name=filter_name, index=False)


# --- 3. Filter: Important Keywords disable (Client Ranking Sort) ---
filter_name = "Important Keywords disable"

# Filter: Keywords where the client is ranking (position is not null)
# FIXED: Use TARGET_COMPETITOR_COL
df_tab = df[df[TARGET_COMPETITOR_COL].notnull()].sort_values(by=[TARGET_COMPETITOR_COL, 'Search Volume'], ascending=[True, False]).copy()
    
print(f"- Writing {filter_name} tab (Client Rank Sort)...")
# FIX 3: Add if_sheet_exists='replace'
with pd.ExcelWriter(xl_file, engine="openpyxl", mode='a', if_sheet_exists='replace') as writer:
    df_tab.to_excel(writer, sheet_name=filter_name, index=False)


# --- 4. Loop: Targeted Keyword Filters (Gifts, Questions, Near Me) ---

targeted_filters = [
    ("Gifts", [
        'gift', 'gifts', 'idea', 'ideas', 'present', 'presents', 'give', 'giving', 
        'black friday', 'cyber monday', 'cyber week', 'bfcm', 'bf', 'cm', 
        'holiday', 'deals', 'sales', 'offer', 'discount', 'shopping'
    ]),
    ("Broad Questions", '''
        am are can could did do does for from had has have how i is may might must shall should was were what when where which who whom whose why will with would
        '''.split()),
    ("Narrow Questions", '''
        who whom whose what which where when why how
        '''.split()),
    ("Near Me", ['near me', 'for sale', 'nearby', 'closest', 'near you', 'local'])
]

for filter_name, keywords in targeted_filters:
    print(f"- Writing {filter_name} tab...")
    
    # Construct the RegEx pattern using word boundaries for precise matching
    pattern = r'\b(?:' + '|'.join([re.escape(k) for k in keywords]) + r')\b'
    
    # Filter 1: Find keywords matching the pattern (e.g., 'gift', 'how', 'near me')
    df_tab = df[df["Keyword"].str.contains(pattern, case=False, na=False)].copy()
    
    # Filter 2: Apply Striking Distance Logic 
    # Keep keywords where client is ranking (pos >= 4) OR client is not ranking at all (NaN)
    # FIXED: Use TARGET_COMPETITOR_COL
    df_tab = df_tab[(df_tab[TARGET_COMPETITOR_COL] >= 4) | (df_tab[TARGET_COMPETITOR_COL].isna())]
    
    # Apply scoring, reorder, and sort
    df_tab = normalize_and_score_surgical(df_tab.copy(), semrush_lookup, has_botify, competitors[-1], True)
    df_tab.sort_values(by='Combined Score', ascending=False, inplace=True)
    
    # FIX 4: Add if_sheet_exists='replace'
    with pd.ExcelWriter(xl_file, engine="openpyxl", mode='a', if_sheet_exists='replace') as writer:
        df_tab.to_excel(writer, sheet_name=filter_name, index=False)
# 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)

print("✅ Done writing all supplementary Excel tabs.")

# TEMPORARY DIAGNOSTIC CELL: Find the real column names
print("--- ACTUAL COLUMN HEADERS ---")
for col in final_df.columns:
    if 'description' in col.lower() or 'title' in col.lower():
        print(f"FOUND: {col}")
print("-----------------------------")
# Display all columns if necessary:
# print(final_df.columns.tolist())

# %% editable=true slideshow={"slide_type": "skip"}
# --- START DIAGNOSTIC BLOCK ---
print("--- DIAGNOSTICS: Pre-Filter DataFrame ---")
print(f"Input df shape: {df.shape}")
print(f"Input df columns: {df.columns.tolist()}")
print(f"Using TARGET_COMPETITOR_COL: '{TARGET_COMPETITOR_COL}'")
print(f"Has Botify Data: {has_botify}")
print("\n--- DIAGNOSTICS: 'Best Opportunities' Filter Breakdown ---")

striking_lower_diag = 100 # Match the value in the main logic

if has_botify:
    print("Attempting Botify/GSC Logic Path...")
    try:
        gsc_col = "No. of Impressions excluding anonymized queries"
        if gsc_col not in df.columns:
             print(f"WARN: GSC Column '{gsc_col}' not found. Botify logic might fail or skip.")
             condition1_count = 0
        else:
            condition1 = (df[gsc_col] > 0)
            condition1_count = condition1.sum()
            print(f"Rows with '{gsc_col}' > 0: {condition1_count}")

        condition2 = (df[TARGET_COMPETITOR_COL] > 3)
        condition2_count = condition2.sum()
        print(f"Rows with '{TARGET_COMPETITOR_COL}' > 3: {condition2_count}")

        if gsc_col in df.columns:
            combined_condition = condition1 & condition2
            combined_count = combined_condition.sum()
            print(f"Rows satisfying BOTH Botify/GSC conditions: {combined_count}")
        else:
             print("Skipping combined Botify/GSC check due to missing GSC column.")
             combined_count = 0 # Assume 0 if GSC col missing

        # Check fallback explicitly
        print("\nChecking SEMRush Fallback Logic (within Botify path)...")
        fallback_condition1 = (df[TARGET_COMPETITOR_COL] >= 4)
        fallback_condition2 = (df[TARGET_COMPETITOR_COL] <= striking_lower_diag)
        fallback_combined = fallback_condition1 & fallback_condition2
        fallback_count = fallback_combined.sum()
        print(f"Rows with '{TARGET_COMPETITOR_COL}' >= 4: {fallback_condition1.sum()}")
        print(f"Rows with '{TARGET_COMPETITOR_COL}' <= {striking_lower_diag}: {fallback_condition2.sum()}")
        print(f"Rows satisfying BOTH SEMRush fallback conditions: {fallback_count}")
        if combined_count == 0 and fallback_count > 0 :
             print("INFO: Botify/GSC conditions yielded 0 rows, Fallback would be used.")

    except KeyError as e:
        print(f"ERROR executing Botify/GSC logic diagnostics: KeyError - {e}")
    except Exception as e:
         print(f"ERROR executing Botify/GSC logic diagnostics: {e}")
else:
    print("Attempting SEMRush-Only Logic Path...")
    try:
        condition1 = (df[TARGET_COMPETITOR_COL] >= 4)
        condition2 = (df[TARGET_COMPETITOR_COL] <= striking_lower_diag)
        combined_condition = condition1 & condition2
        print(f"Rows with '{TARGET_COMPETITOR_COL}' >= 4: {condition1.sum()}")
        print(f"Rows with '{TARGET_COMPETITOR_COL}' <= {striking_lower_diag}: {condition2.sum()}")
        print(f"Rows satisfying BOTH SEMRush conditions: {combined_condition.sum()}")
    except KeyError as e:
        print(f"ERROR executing SEMRush-Only logic diagnostics: KeyError - {e}")
    except Exception as e:
        print(f"ERROR executing SEMRush-Only logic diagnostics: {e}")


print("\n--- DIAGNOSTICS: 'Gifts' Filter Breakdown ---")
try:
    gifts_keywords = ['gift', 'gifts', 'idea', 'ideas', 'present', 'presents', 'give', 'giving',
                      'black friday', 'cyber monday', 'cyber week', 'bfcm', 'bf', 'cm',
                      'holiday', 'deals', 'sales', 'offer', 'discount', 'shopping']
    gifts_pattern = r'\b(?:' + '|'.join([re.escape(k) for k in gifts_keywords]) + r')\b'

    # Check keyword filter
    keyword_condition = df["Keyword"].str.contains(gifts_pattern, case=False, na=False)
    keyword_match_count = keyword_condition.sum()
    print(f"Rows matching 'Gifts' keywords pattern: {keyword_match_count}")

    # Check striking distance filter
    striking_condition1 = (df[TARGET_COMPETITOR_COL] >= 4)
    striking_condition2 = (df[TARGET_COMPETITOR_COL].isna())
    striking_combined = striking_condition1 | striking_condition2
    striking_match_count = striking_combined.sum()
    print(f"Rows where '{TARGET_COMPETITOR_COL}' >= 4: {striking_condition1.sum()}")
    print(f"Rows where '{TARGET_COMPETITOR_COL}' is NaN: {striking_condition2.sum()}")
    print(f"Rows satisfying EITHER striking distance condition: {striking_match_count}")

    # Check combined filter
    df_gift_filtered_stage1 = df[keyword_condition]
    final_gift_condition = (df_gift_filtered_stage1[TARGET_COMPETITOR_COL] >= 4) | (df_gift_filtered_stage1[TARGET_COMPETITOR_COL].isna())
    final_gift_count = final_gift_condition.sum()
    print(f"Rows satisfying BOTH 'Gifts' keywords AND striking distance: {final_gift_count}")

    if keyword_match_count > 0 and final_gift_count == 0:
        print("INFO: 'Gifts' keywords were found, but none met the striking distance criteria.")
    elif keyword_match_count == 0:
         print("INFO: No keywords matching the 'Gifts' pattern were found in the input data.")

except KeyError as e:
        print(f"ERROR executing 'Gifts' logic diagnostics: KeyError - {e}")
except Exception as e:
        print(f"ERROR executing 'Gifts' logic diagnostics: {e}")

print("--- END DIAGNOSTIC BLOCK ---")

# --- 2. Filter: Best Opportunities / Striking Distance ---
filter_name = "Best Opportunities"
striking_lower = 100

# Start with the original DataFrame
df_tab = df.copy()
applied_botify_logic = False # Flag to track which logic path was effectively used

if has_botify:
    print(f"- Attempting {filter_name} tab (Botify/GSC Striking Distance)...")
    try:
        # Define GSC and target columns
        gsc_col = "No. of Impressions excluding anonymized queries"

        # Check if necessary columns exist before filtering
        if gsc_col in df_tab.columns and TARGET_COMPETITOR_COL in df_tab.columns:
            # Apply the primary Botify/GSC filter
            df_filtered_botify = df_tab[
                (df_tab[gsc_col] > 0) &
                (df_tab[TARGET_COMPETITOR_COL] > 3)
            ].copy()

            # Check if the primary filter yielded results
            if not df_filtered_botify.empty:
                df_tab = df_filtered_botify # Use the Botify/GSC filtered results
                applied_botify_logic = True
                print(f"  -> Using Botify/GSC logic path ({len(df_tab)} rows).")
            else:
                # If Botify/GSC yields 0 rows, explicitly apply SEMRush fallback
                print(f"  -> Botify/GSC logic yielded 0 rows. Applying SEMRush fallback...")
                df_tab = df_tab[
                    (df_tab[TARGET_COMPETITOR_COL] >= 4) &
                    (df_tab[TARGET_COMPETITOR_COL] <= striking_lower)
                ].copy()
                print(f"  -> Using SEMRush fallback logic ({len(df_tab)} rows).")
        else:
             # If essential columns are missing, apply SEMRush fallback
             print(f"  -> Missing required Botify/GSC columns. Applying SEMRush fallback...")
             df_tab = df_tab[
                    (df_tab[TARGET_COMPETITOR_COL] >= 4) &
                    (df_tab[TARGET_COMPETITOR_COL] <= striking_lower)
             ].copy()
             print(f"  -> Using SEMRush fallback logic ({len(df_tab)} rows).")

    except Exception as e: # Catch potential errors during filtering more broadly
        print(f"  -> ERROR during Botify/GSC filtering: {e}. Applying SEMRush fallback...")
        # Ensure fallback uses the original df if df_tab was modified or errored
        df_tab = df[
            (df[TARGET_COMPETITOR_COL] >= 4) &
            (df[TARGET_COMPETITOR_COL] <= striking_lower)
        ].copy()
        print(f"  -> Using SEMRush fallback logic ({len(df_tab)} rows).")

# If Botify logic wasn't applicable or failed, ensure SEMRush logic runs
if not has_botify or not applied_botify_logic:
     # Only print this if we haven't already applied fallback logic above
     if not ('fallback' in locals() and fallback): # A bit redundant now, but safe
          print(f"- Writing {filter_name} tab (SEMRush Striking Distance)...")
          df_tab = df[
               (df[TARGET_COMPETITOR_COL] >= 4) &
               (df[TARGET_COMPETITOR_COL] <= striking_lower)
          ].copy()
          print(f"  -> Using SEMRush-only logic path ({len(df_tab)} rows).")


# Apply scoring, reorder, and sort (only if df_tab is not empty)
if not df_tab.empty:
    df_tab = normalize_and_score_surgical(df_tab, semrush_lookup, has_botify, competitors[-1], True)
    df_tab.sort_values(by='Combined Score', ascending=False, inplace=True)

    # Write out the tab
    print(f"  -> Writing {len(df_tab)} rows to sheet '{filter_name}'...")
    with pd.ExcelWriter(xl_file, engine="openpyxl", mode='a', if_sheet_exists='replace') as writer:
        df_tab.to_excel(writer, sheet_name=filter_name, index=False)
else:
     print(f"☑️ Skipping sheet '{filter_name}': No rows met the criteria after filtering.")

google = "example hidden"
botify = "example hidden"
botify_project_url = "https://app.botify.com/example-org/example/"
client_domain = "example-client.com"
country_code = "us"

# %% editable=true slideshow={"slide_type": ""} tags=["secrets"]
pip.api_key(job, key=keys.google)
botify_token = keys.botify

# %% editable=true slideshow={"slide_type": ""} tags=["secrets"]
pip.api_key(job, key=keys.google)
botify_token = keys.botify
client_domain = keys.client_domain # Explicitly bring client_domain into local scope

# %% editable=true slideshow={"slide_type": ""}
import pandas as pd
from tldextract import extract
import itertools
from pathlib import Path
from IPython.display import display

# ... (other code) ...

# The client domain is the key for separating client vs. competitor data.
# We strip the full domain in case it contains a protocol or path.
# Assuming 'client_domain' is available from a keys/config cell (e.g., "example.com")
# Since we don't have 'client_domain' defined here, we'll temporarily define it for the port.
# Replace this line with proper import/assignment if moving to external module:
semrush_lookup = extract_registered_domain(client_domain) if 'client_domain' in locals() else "nixos.org"

# ... (rest of the cell) ...

# %% editable=true slideshow={"slide_type": ""}
import pandas as pd
from tldextract import extract
import itertools
from pathlib import Path
from IPython.display import display

# ... (other code) ...

# The client domain is the key for separating client vs. competitor data.
# We strip the full domain in case it contains a protocol or path.
# client_domain is now guaranteed to be in locals() from the keys import cell.
semrush_lookup = extract_registered_domain(client_domain)

print("Creating a great big DataFrame...")
# ... (rest of the cell, no further changes needed here) ...

# %% editable=true slideshow={"slide_type": ""}
import pandas as pd
import xlsxwriter
import openpyxl
import re
from pathlib import Path
from collections import Counter
from nltk import bigrams
from nltk.corpus import stopwords
from nltk.stem import PorterStemmer
from nltk.tokenize import word_tokenize
import numpy as np
import ipywidgets as widgets # Ensure widgets are imported for the button
from IPython.display import display # Ensure display is imported

# NOTE: This cell assumes 'job', 'df', 'semrush_lookup', 'has_botify',
#       'xl_file', 'competitors', 'deliverables_dir', 'button', and helper functions are defined.

# --- PATH DEFINITION ---
important_keywords_file = Path("data") / f"{job}_important_keywords.txt"

# --- KEY LOOKUP FIX: Determine the actual competitor column name ---
clean_lookup_key = semrush_lookup.rstrip('/')
TARGET_COMPETITOR_COL = None

for col in df.columns:
    if col.rstrip('/') == clean_lookup_key:
        TARGET_COMPETITOR_COL = col
        break

if TARGET_COMPETITOR_COL is None:
    raise KeyError(f"CRITICAL ERROR: Could not find canonical competitor column for '{semrush_lookup}' in DataFrame. Available columns: {df.columns.tolist()}")

print(f"✅ Canonical Client Column Identified: '{TARGET_COMPETITOR_COL}' (Derived from: '{semrush_lookup}')")

# --- REQUIRED SUPPORT FUNCTIONS (Assume defined above) ---
# normalize_and_score_surgical, safe_normalize, reorder_columns_surgical, read_keywords, filter_df_by_keywords, _open_folder

# --- DIAGNOSTICS TRACKING ---
filter_diagnostics = []

# --- MAIN TAB GENERATION LOGIC ---
print("Starting subsequent Excel tab generation (Appending via openpyxl)...")

# --- 1. Filter: Important Keywords (File-Based Filter) ---
filter_name = "Important Keywords"

if important_keywords_file.exists():
    important_keywords_list = read_keywords(important_keywords_file)
    if important_keywords_list:
        print(f"- Writing {filter_name} tab (via file list)...")
        df_filtered = filter_df_by_keywords(df.copy(), important_keywords_list)

        # Log diagnostics
        filter_diagnostics.append({
            "Tab Name": filter_name,
            "Filter Type": "Keyword List (File)",
            "Criteria": f"Keywords in '{important_keywords_file.name}'",
            "Initial Rows": len(df),
            "Rows After Filter": len(df_filtered),
            "Notes": f"{len(important_keywords_list)} keywords in list."
        })

        if not df_filtered.empty:
            df_filtered = normalize_and_score_surgical(df_filtered, semrush_lookup, has_botify, competitors[-1], True)
            df_filtered.sort_values(by='Combined Score', ascending=False, inplace=True)
            with pd.ExcelWriter(xl_file, engine="openpyxl", mode='a', if_sheet_exists='replace') as writer:
                df_filtered.to_excel(writer, sheet_name=filter_name, index=False)
        else:
             filter_diagnostics[-1]["Notes"] += " Tab skipped (0 rows after filter)."
             print(f"☑️ Skipping sheet '{filter_name}': 0 rows matched keyword list.")

    else:
        print(f"☑️ Skipping '{filter_name}': Keyword file is empty.")
        filter_diagnostics.append({
            "Tab Name": filter_name, "Filter Type": "Keyword List (File)", "Criteria": f"Keywords in '{important_keywords_file.name}'",
            "Initial Rows": len(df), "Rows After Filter": 0, "Notes": "Skipped (Keyword file empty)."
        })
else:
    print(f"☑️ Skipping '{filter_name}': Keywords file does not exist.")
    filter_diagnostics.append({
            "Tab Name": filter_name, "Filter Type": "Keyword List (File)", "Criteria": f"Keywords in '{important_keywords_file.name}'",
            "Initial Rows": len(df), "Rows After Filter": 0, "Notes": "Skipped (Keyword file missing)."
        })


# --- 2. Filter: Best Opportunities / Striking Distance ---
filter_name = "Best Opportunities"
striking_lower = 100
df_tab_opportunities = pd.DataFrame() # Initialize empty DataFrame
criteria_log = ""
notes = ""

if has_botify:
    gsc_col = "No. of Impressions excluding anonymized queries"
    if gsc_col in df.columns:
        criteria_primary = f"'{gsc_col}' > 0 AND '{TARGET_COMPETITOR_COL}' > 3"
        df_tab_opportunities = df[(df[gsc_col] > 0) & (df[TARGET_COMPETITOR_COL] > 3)].copy()
        rows_after_primary = len(df_tab_opportunities)
        notes = f"Attempted Botify/GSC logic. Rows matching primary: {rows_after_primary}."

        if df_tab_opportunities.empty:
            criteria_fallback = f"'{TARGET_COMPETITOR_COL}' >= 4 AND '{TARGET_COMPETITOR_COL}' <= {striking_lower}"
            df_tab_opportunities = df[(df[TARGET_COMPETITOR_COL] >= 4) & (df[TARGET_COMPETITOR_COL] <= striking_lower)].copy()
            criteria_log = f"Primary: {criteria_primary} (Yielded 0). Fallback: {criteria_fallback}"
            notes += f" Applied SEMRush fallback. Rows matching fallback: {len(df_tab_opportunities)}."
            print(f"- Writing {filter_name} tab (SEMRush Striking Distance fallback)...")
        else:
             criteria_log = criteria_primary
             print(f"- Writing {filter_name} tab (Botify/GSC Striking Distance)...")
    else:
        # GSC column missing, apply SEMRush fallback directly
        criteria_log = f"'{TARGET_COMPETITOR_COL}' >= 4 AND '{TARGET_COMPETITOR_COL}' <= {striking_lower}"
        df_tab_opportunities = df[(df[TARGET_COMPETITOR_COL] >= 4) & (df[TARGET_COMPETITOR_COL] <= striking_lower)].copy()
        notes = f"Botify data present but '{gsc_col}' missing. Applied SEMRush fallback."
        print(f"- Writing {filter_name} tab (SEMRush Striking Distance fallback)...")

else:
    # SEMRush-only logic
    criteria_log = f"'{TARGET_COMPETITOR_COL}' >= 4 AND '{TARGET_COMPETITOR_COL}' <= {striking_lower}"
    df_tab_opportunities = df[(df[TARGET_COMPETITOR_COL] >= 4) & (df[TARGET_COMPETITOR_COL] <= striking_lower)].copy()
    notes = "Using SEMRush-only logic."
    print(f"- Writing {filter_name} tab (SEMRush Striking Distance)...")

filter_diagnostics.append({
    "Tab Name": filter_name,
    "Filter Type": "Striking Distance",
    "Criteria": criteria_log,
    "Initial Rows": len(df),
    "Rows After Filter": len(df_tab_opportunities),
    "Notes": notes
})

if not df_tab_opportunities.empty:
    df_tab_opportunities = normalize_and_score_surgical(df_tab_opportunities, semrush_lookup, has_botify, competitors[-1], True)
    df_tab_opportunities.sort_values(by='Combined Score', ascending=False, inplace=True)
    with pd.ExcelWriter(xl_file, engine="openpyxl", mode='a', if_sheet_exists='replace') as writer:
        df_tab_opportunities.to_excel(writer, sheet_name=filter_name, index=False)
else:
    filter_diagnostics[-1]["Notes"] += " Tab skipped (0 rows after filter)."
    print(f"☑️ Skipping sheet '{filter_name}': 0 rows met criteria.")


# --- 3. Filter: Client Ranking Sort (Formerly "Important Keywords disable") ---
filter_name = "Client Ranking Sort" # Renamed for clarity
criteria_log = f"'{TARGET_COMPETITOR_COL}' IS NOT NULL"
df_tab_client = df[df[TARGET_COMPETITOR_COL].notnull()].copy()

filter_diagnostics.append({
    "Tab Name": filter_name,
    "Filter Type": "Client Rank",
    "Criteria": criteria_log,
    "Initial Rows": len(df),
    "Rows After Filter": len(df_tab_client),
    "Notes": f"Sorted by '{TARGET_COMPETITOR_COL}' (Asc), 'Search Volume' (Desc)."
})

print(f"- Writing {filter_name} tab (Client Rank Sort)...")
if not df_tab_client.empty:
    # Apply sort, no scoring needed for this tab
    df_tab_client.sort_values(by=[TARGET_COMPETITOR_COL, 'Search Volume'], ascending=[True, False], inplace=True)
    with pd.ExcelWriter(xl_file, engine="openpyxl", mode='a', if_sheet_exists='replace') as writer:
        df_tab_client.to_excel(writer, sheet_name=filter_name, index=False)
else:
    filter_diagnostics[-1]["Notes"] += " Tab skipped (0 rows after filter)."
    print(f"☑️ Skipping sheet '{filter_name}': 0 rows met criteria.")


# --- 4. Loop: Targeted Keyword Filters (Gifts, Questions, Near Me) ---
targeted_filters = [
    ("Gifts", [
        'gift', 'gifts', 'idea', 'ideas', 'present', 'presents', 'give', 'giving',
        'black friday', 'cyber monday', 'cyber week', 'bfcm', 'bf', 'cm',
        'holiday', 'deals', 'sales', 'offer', 'discount', 'shopping'
    ]),
    ("Broad Questions", '''
        am are can could did do does for from had has have how i is may might must shall should was were what when where which who whom whose why will with would
        '''.split()),
    ("Narrow Questions", '''
        who whom whose what which where when why how
        '''.split()),
    ("Near Me", ['near me', 'for sale', 'nearby', 'closest', 'near you', 'local'])
]

for filter_name, keywords in targeted_filters:
    print(f"- Writing {filter_name} tab...")
    pattern = r'\b(?:' + '|'.join([re.escape(k) for k in keywords]) + r')\b'

    # Filter 1: Keyword pattern
    df_tab_kw_filtered = df[df["Keyword"].str.contains(pattern, case=False, na=False)].copy()
    rows_after_kw = len(df_tab_kw_filtered)

    # Filter 2: Striking distance
    criteria_striking = f"('{TARGET_COMPETITOR_COL}' >= 4 OR '{TARGET_COMPETITOR_COL}' IS NULL)"
    df_tab_final = df_tab_kw_filtered[
        (df_tab_kw_filtered[TARGET_COMPETITOR_COL] >= 4) | (df_tab_kw_filtered[TARGET_COMPETITOR_COL].isna())
    ].copy()
    rows_after_striking = len(df_tab_final)

    filter_diagnostics.append({
        "Tab Name": filter_name,
        "Filter Type": "Keyword Pattern + Striking Distance",
        "Criteria": f"Keyword Pattern: '{pattern}'. Then Striking: {criteria_striking}",
        "Initial Rows": len(df),
        "Rows After Filter": rows_after_striking,
        "Notes": f"Rows after keyword filter: {rows_after_kw}."
    })

    if not df_tab_final.empty:
        df_tab_final = normalize_and_score_surgical(df_tab_final, semrush_lookup, has_botify, competitors[-1], True)
        df_tab_final.sort_values(by='Combined Score', ascending=False, inplace=True)
        with pd.ExcelWriter(xl_file, engine="openpyxl", mode='a', if_sheet_exists='replace') as writer:
            df_tab_final.to_excel(writer, sheet_name=filter_name, index=False)
    else:
        filter_diagnostics[-1]["Notes"] += " Tab skipped (0 rows after final filter)."
        print(f"☑️ Skipping sheet '{filter_name}': 0 rows met criteria.")


# --- 5. Write Diagnostics Tab ---
filter_name = "Filter Diagnostics"
print(f"- Writing {filter_name} tab...")
df_diagnostics = pd.DataFrame(filter_diagnostics)

# Ensure consistent column order
df_diagnostics = df_diagnostics[["Tab Name", "Filter Type", "Criteria", "Initial Rows", "Rows After Filter", "Notes"]]

with pd.ExcelWriter(xl_file, engine="openpyxl", mode='a', if_sheet_exists='replace') as writer:
    df_diagnostics.to_excel(writer, sheet_name=filter_name, index=False)


# --- Final Output ---
# Display the button again for convenience
button.on_click(lambda b: _open_folder(str(deliverables_dir))) # Re-attach lambda
display(button)

print("✅ Done writing all supplementary Excel tabs, including Filter Diagnostics.")

# Store final diagnostics in pipeline (optional)
pip.set(job, 'filter_diagnostics', df_diagnostics.to_json(orient='records'))