The Output Triad: Managing Data, Feedback, and Side Effects in AI-Driven Pipelines

print("Visiting competitor homepages for title tags for filters...\n")  # Debugging line

nest_asyncio.apply()  # To run in Jupyter

df_competitors['Matched Title'] = df_competitors['Matched Title'].str.lower()
needs_titles = df_competitors[df_competitors['Title'] == '']
# for adom in needs_titles['Domain'].tolist():
#     print(adom)

if not needs_titles.empty:
    # Get Titles
    print("Gathering Titles.")
    results = bf.test_domains(needs_titles['Domain'].tolist())
    
    # Prepare data to be added to DataFrame
    data_to_add = {
        'Domain': [],
        'Title': [],
        'Matched Title': []
    }
    
    for domain, info in results.items():
        data_to_add['Domain'].append(domain)
        data_to_add['Title'].append(info['title'] if info['title'] else '')
        data_to_add['Matched Title'].append(info['matched_title'] if info['matched_title'] else '')
    
    # Convert the prepared data to a DataFrame
    new_data_df = pd.DataFrame(data_to_add)
    df_competitors.set_index('Domain', inplace=True)

## Merging and Filtering: Forging the Core Intelligence Table
    new_data_df.set_index('Domain', inplace=True)
    df_competitors = new_data_df.combine_first(df_competitors)
    df_competitors.reset_index(inplace=True)
    df_competitors['Matched Title'] = df_competitors['Matched Title'].str.lower()
    df_competitors.to_csv(competitors_csv_file, index=False)

# Create Keyword Filters
extracted_domains = [bf.extract_registered_domain(domain).replace('.com', '') for domain in df_competitors['Domain']]
matched_titles = [title.replace('.com', '') for title in df_competitors['Matched Title'].tolist()]
split_domains = [bf.split_domain_name(domain) for domain in extracted_domains]
combined_list = [x.strip() for x in extracted_domains + matched_titles + split_domains if x]
combined_list = sorted(list(set(combined_list)))
if not filter_file.exists():
    df_filter = pd.DataFrame(combined_list, columns=['Filter'])
    df_filter.to_csv(filter_file, index=False)

# %% editable=true slideshow={"slide_type": ""}
import pandas as pd
import nest_asyncio
import asyncio
from pathlib import Path
from tldextract import extract
import wordninja
import httpx
import re
from collections import defaultdict # Already imported in a previous cell

# NOTE: This cell assumes 'job', 'semrush_lookup', 'df_competitors', 
#       and 'competitors_csv_file' are defined in prior cells.
# We also assume 'df_competitors' was loaded from 'competitors_csv_file' in the previous step.

# --- PATH DEFINITION FOR FILTER FILE ---

## Botify Utilities: Enabling Adaptive Data Retrieval
# Consolidating working files to the 'data' directory.
filter_file = Path("data") / f"{job}_filter_keywords.csv"


# --- REQUIRED SUPPORT FUNCTIONS (Surgically Ported from botifython.py) ---

# Headers and user_agent were defined globally in botifython.py, but are needed here for httpx
user_agent = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/125.0.0.0 Safari/537.36'
headers = {'User-Agent': user_agent}

def extract_registered_domain(url):
    """Extracts the registered domain (domain.suffix) from a URL/hostname."""
    extracted = extract(url)
    return f"{extracted.domain}.{extracted.suffix}"

def get_title_from_html(html_content):
    """Simple helper to extract the title from HTML content."""
    from bs4 import BeautifulSoup
    soup = BeautifulSoup(html_content, 'html.parser')
    title_tag = soup.find('title')
    return title_tag.text if title_tag else ''

def match_domain_in_title(domain, title):
    """Finds a stripped version of the domain in the title."""
    base_domain = domain.split('.')[0]
    pattern = ''.join([c + r'\s*' for c in base_domain])
    regex = re.compile(pattern, re.IGNORECASE)
    match = regex.search(title)
    if match:
        matched = match.group(0).strip()
        return matched
    return ''

async def async_check_url(url, domain, timeout):
    """Asynchronously checks a single domain and extracts title/matched title."""
    # Timeout is intentionally high (120s from the original context)
    try:
        async with httpx.AsyncClient(follow_redirects=True, headers=headers, timeout=timeout) as client:
            response = await client.get(url)
            if response.status_code == 200:
                if str(response.url) != url:
                    print(f"Redirected to {response.url} for {url}")
                title = get_title_from_html(response.text)
                matched_title = match_domain_in_title(domain, title)
                return str(response.url), title, matched_title, True
            else:
                print(f"Status Code {response.status_code} for {url}")
    except httpx.RequestError as e:
        print(f"Request failed for {url}: {str(e)}")
    except Exception as e:
        print(f"An unexpected error occurred for {url}: {str(e)}")
    return url, None, None, False

def test_domains(domains, timeout=120):
    """Orchestrates async checks for a list of domains."""
    print(f"Giving up to {timeout} seconds to visit all sites...")
    tasks = [async_check_url(f'https://{domain}', domain, timeout) for domain in domains]
    results = asyncio.run(async_test_domains(domains, tasks))
    
    domain_results = {}
    for domain, result in zip(domains, results):
        # Handle exceptions gracefully as in the original bf.test_domains (part of the transplant)
        if isinstance(result, Exception):
            domain_results[domain] = {'url': None, 'title': None, 'matched_title': None}
        else:
            domain_results[domain] = {'url': result[0], 'title': result[1], 'matched_title': result[2]}
    return domain_results

async def async_test_domains(domains, tasks):
    """Internal helper for asyncio.gather."""
    return await asyncio.gather(*tasks, return_exceptions=True)

def split_domain_name(domain):
    """Splits a concatenated domain name into human-readable words (requires wordninja)."""
    words = wordninja.split(domain)
    return ' '.join(words)

# --- MAIN WORKFLOW LOGIC ---

print("Visiting competitor homepages for title tags for filters...\n")

# Original logic required to run async in Jupyter
nest_asyncio.apply()

# Lowercase existing matched titles for comparison
df_competitors['Matched Title'] = df_competitors['Matched Title'].str.lower()

# Find domains where 'Title' column is empty ('') or NaN
# Using .isna() on a string column returns False for '', so we check explicitly for the empty string
needs_titles = df_competitors[
    (df_competitors['Title'].isna()) | (df_competitors['Title'] == '')
].copy()

if not needs_titles.empty:
    # 1. Scrape Titles
    print(f"Gathering Titles for {len(needs_titles)} domains.")
    results = test_domains(needs_titles['Domain'].tolist())
    
    # 2. Prepare and Merge Data
    data_to_add = {
        'Domain': [],
        'Title': [],
        'Matched Title': []
    }
    
    for domain, info in results.items():
        data_to_add['Domain'].append(domain)
        data_to_add['Title'].append(info['title'] if info['title'] else '')
        data_to_add['Matched Title'].append(info['matched_title'] if info['matched_title'] else '')
    
    new_data_df = pd.DataFrame(data_to_add)
    
    # Use original combine_first logic for non-destructive update
    df_competitors.set_index('Domain', inplace=True)
    new_data_df.set_index('Domain', inplace=True)
    df_competitors = new_data_df.combine_first(df_competitors)
    df_competitors.reset_index(inplace=True)
    
    # Lowercase and persist the updated data
    df_competitors['Matched Title'] = df_competitors['Matched Title'].str.lower()
    df_competitors.to_csv(competitors_csv_file, index=False)
    print(f"✅ Updated competitor titles and saved to '{competitors_csv_file}'.")


# --- Create Keyword Filters ---

# Remove '.com' from both lists to create more generic keyword filters
extracted_domains = [extract_registered_domain(domain).replace('.com', '') for domain in df_competitors['Domain']]
matched_titles = [title.replace('.com', '') for title in df_competitors['Matched Title'].tolist() if title]

# Split domain names using wordninja (e.g., 'fooexample' -> 'foo example')
split_domains = [split_domain_name(domain) for domain in extracted_domains]

# Combine all lists, strip whitespace, and deduplicate
combined_list = [x.strip() for x in extracted_domains + matched_titles + split_domains if x]
combined_list = sorted(list(set(combined_list)))

# Persist to external filter file (allows user editing)
if not filter_file.exists():
    df_filter = pd.DataFrame(combined_list, columns=['Filter'])
    df_filter.to_csv(filter_file, index=False)
    print(f"✅ Created initial keyword filter file at '{filter_file}' for user editing.")
else:
    print(f"☑️ Keyword filter file already exists at '{filter_file}'. Skipping creation.")

# Store the final competitors DF in the pipeline
pip.set(job, 'competitors_df', df_competitors.to_json(orient='records'))

print("Adjusting SEMRush columns that were not part of competitor-columns pivot...")

# Assign aggregating function to each metric
agg_funcs = {
    'Position': 'min',
    'Search Volume': 'max',
    'CPC': 'mean',
    'Traffic': 'sum',
    'Traffic (%)': 'mean',
    'Traffic Cost': 'sum',
    'Keyword Difficulty': 'mean',
    'Previous position': 'first',
    'Competition': 'mean',
    'Number of Results': 'max',
    'Timestamp': 'max',
    'SERP Features by Keyword': 'first',
    'Keyword Intents': 'first',
    'Position Type': 'first',
    'URL': 'first',
    'Competitor URL': 'first',
    'Client URL': 'first'
}

agg_df = df2.groupby('Keyword').agg(agg_funcs).reset_index()
agg_df['Number of Words'] = agg_df["Keyword"].apply(lambda x: len(x.split()))
agg_df.drop(columns=['Position'], inplace=True)
print("Table of aggregates prepared.")

display(agg_df)

## Data Aggregation: Distilling Keyword Metrics with Pandas

# %% editable=true slideshow={"slide_type": ""}
import pandas as pd
from IPython.display import display

# NOTE: This cell assumes 'df2' (the result of the aggregation step) is available.

print("Adjusting SEMRush columns that were not part of competitor-columns pivot...")

# Assign aggregating function to each metric
# The chosen functions are critical for creating a single, best-case summary per keyword:
# - 'min' for Position: Gives the *best* rank achieved across all competitors who rank.
# - 'max' for Search Volume/Number of Results/Timestamp: Captures the highest value reported.
# - 'sum' for Traffic/Traffic Cost: Aggregates the total value across all competitor results.
# - 'mean' for Difficulty/CPC/Competition: Averages the difficulty/cost across all reporting sources.
# - 'first' for categorical data (URLs, Intents, SERP Features): Chooses the first encountered value.
agg_funcs = {
    'Position': 'min',
    'Search Volume': 'max',
    'CPC': 'mean',
    'Traffic': 'sum',
    'Traffic (%)': 'mean',
    'Traffic Cost': 'sum',
    'Keyword Difficulty': 'mean',
    'Previous position': 'first',
    'Competition': 'mean',
    'Number of Results': 'max',
    'Timestamp': 'max',
    'SERP Features by Keyword': 'first',
    'Keyword Intents': 'first',
    'Position Type': 'first',
    'URL': 'first',
    'Competitor URL': 'first',
    'Client URL': 'first'
}

# Apply the aggregation across the combined dataset (df2)
agg_df = df2.groupby('Keyword').agg(agg_funcs).reset_index()

# Add a derived metric: Keyword word count
agg_df['Number of Words'] = agg_df["Keyword"].apply(lambda x: len(x.split()))

# Drop the 'Position' column: It was only used for the pivot/min operation,
# but it's redundant/misleading now that the competitor position data is in pivot_df.
agg_df.drop(columns=['Position'], inplace=True)

print("Table of aggregates prepared.")

display(agg_df)

# Store the aggregated metrics in the pipeline
pip.set(job, 'keyword_aggregate_df_json', agg_df.to_json(orient='records'))

print("Merging Pivot Data with Aggregate Data...")

pivotmerge_df = pd.merge(pivot_df.reset_index(), agg_df, on='Keyword', how='left')

print("Pivot and Aggregate Data Joined.\n")
rows, columns = pivotmerge_df.shape
print(f"Rows: {rows:,}")
print(f"Cols: {columns:,}")

print("\nBrand and Negative Filters being applied...")
# Optionally Filiter Brand & Negative Keywords
if filter_file.exists():
    df_filter = pd.read_csv(filter_file, header=0)
    kw_filter = df_filter["Filter"].tolist()
    pattern = '|'.join([f'{keyword}' for keyword in kw_filter])
    filtered_df = pivotmerge_df[~pivotmerge_df["Keyword"].str.contains(pattern)]
else:
    filtered_df = pivotmerge_df

rows, columns = filtered_df.shape
print(f"Rows: {rows:,}")
# print(f"Cols: {columns:,}")

# Reorder Columns
filtered_df = bf.reorder_columns(filtered_df, "Search Volume", after_column="Keyword")
filtered_df = bf.reorder_columns(filtered_df, "Number of Words", after_column="CPC")
filtered_df = bf.reorder_columns(filtered_df, "CPC", after_column="Number of Words")
filtered_df = bf.reorder_columns(filtered_df, "Number of Results", after_column="Position Type")
filtered_df = bf.reorder_columns(filtered_df, "Timestamp", after_column="Number of Results")
filtered_df = bf.reorder_columns(filtered_df, "Competitor URL", after_column="Client URL")

# Arrange Columns
rest_of_columns = [col for col in filtered_df.columns if col not in ['Keyword', 'Search Volume']]
new_column_order = ['Keyword', 'Search Volume'] + rest_of_columns
if 'Keyword' in filtered_df.columns:
    filtered_df = filtered_df[['Keyword'] + ['Search Volume'] + [col for col in filtered_df.columns if col not in ['Keyword', 'Search Volume']]]
filtered_df = filtered_df[new_column_order]
arranged_df = filtered_df.sort_values(by='Search Volume', ascending=False)
arranged_df.drop(columns=["Previous position", "Traffic", "Traffic (%)", "Traffic Cost"], inplace=True)

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

# NOTE: This cell assumes 'job', 'pivot_df', 'agg_df', and 'filter_file' are defined in prior cells.

# --- PATH DEFINITION ---
# The filter file path is already defined in a previous step, but included here for clarity
filter_file = Path("data") / f"{job}_filter_keywords.csv"

# --- REQUIRED SUPPORT FUNCTION (Surgically Ported from botifython.py) ---

def reorder_columns_surgical(df, priority_column, after_column):
    """
    Surgical port of bf.reorder_columns: Moves a column immediately after a specified column.
    """
    if priority_column in df.columns and after_column in df.columns:
        columns = df.columns.drop(priority_column).tolist()
        after_column_index = columns.index(after_column)
        columns.insert(after_column_index + 1, priority_column)
        return df[columns]
    elif priority_column not in df.columns:
        print(f"⚠️ Warning: Priority column '{priority_column}' not found for reorder.")
    elif after_column not in df.columns:
        print(f"⚠️ Warning: After column '{after_column}' not found for reorder.")
    return df


print("Merging Pivot Data with Aggregate Data...")

# 1. Merge Pivot Data (Keyword as index) with Aggregate Data (Keyword as index/column)
pivotmerge_df = pd.merge(pivot_df.reset_index(), agg_df, on='Keyword', how='left')

print("Pivot and Aggregate Data Joined.\n")
rows, columns = pivotmerge_df.shape
print(f"Rows: {rows:,}")
print(f"Cols: {columns:,}")

# --- FILTERING LOGIC ---

print("\nBrand and Negative Filters being applied...")
# 2. Optionally Filter Brand & Negative Keywords
if filter_file.exists():
    df_filter = pd.read_csv(filter_file, header=0)
    
    # Ensure all list items are strings before joining into a regex pattern
    kw_filter = [str(f) for f in df_filter["Filter"].dropna().tolist()]
    
    if kw_filter:
        # Use re.escape to handle special characters in keywords and then join with '|' (OR)
        pattern = '|'.join([re.escape(keyword) for keyword in kw_filter])
        
        # Apply the filter: keep rows where Keyword DOES NOT contain the pattern
        filtered_df = pivotmerge_df[~pivotmerge_df["Keyword"].str.contains(pattern, case=False, na=False)]
        print(f"✅ Filter applied using {len(kw_filter)} terms from '{filter_file}'.")
    else:
        filtered_df = pivotmerge_df
        print("⚠️ Filter file exists but contains no terms. Skipping filter application.")
else:
    filtered_df = pivotmerge_df
    print(f"☑️ No filter file found at '{filter_file}'. Skipping negative filtering.")

rows_filtered, columns_filtered = filtered_df.shape
print(f"Rows: {rows_filtered:,} ({rows:,} - {rows_filtered:,} = {rows - rows_filtered:,} rows removed)")


# --- REORDERING AND FINAL POLISH ---

# 3. Apply Reordering Logic (Using the surgically defined function)
# NOTE: The original logic chains reorders based on previously moved columns.
temp_df = filtered_df.copy() # Use a temporary variable for clarity during chained operations

temp_df = reorder_columns_surgical(temp_df, "Search Volume", after_column="Keyword")
temp_df = reorder_columns_surgical(temp_df, "Number of Words", after_column="CPC")
temp_df = reorder_columns_surgical(temp_df, "CPC", after_column="Number of Words")
temp_df = reorder_columns_surgical(temp_df, "Number of Results", after_column="Position Type")
temp_df = reorder_columns_surgical(temp_df, "Timestamp", after_column="Number of Results")
temp_df = reorder_columns_surgical(temp_df, "Competitor URL", after_column="Client URL")

# 4. Final Arrange (Verbatim column ordering and sorting)
# The manual reorder logic below overrides the custom function, but we include it verbatim:
rest_of_columns = [col for col in temp_df.columns if col not in ['Keyword', 'Search Volume']]
new_column_order = ['Keyword', 'Search Volume'] + rest_of_columns

# The conditional block from the original (verbatim)
if 'Keyword' in temp_df.columns:
    temp_df = temp_df[['Keyword'] + ['Search Volume'] + [col for col in temp_df.columns if col not in ['Keyword', 'Search Volume']]]

# Apply the intended final order
filtered_df = temp_df[new_column_order]

# Final sorting and column drops
arranged_df = filtered_df.sort_values(by='Search Volume', ascending=False)
arranged_df.drop(columns=["Previous position", "Traffic", "Traffic (%)", "Traffic Cost"], inplace=True)

print("\nFinal Keyword Table Prepared.")

# Store the final result in the pipeline
pip.set(job, 'filtered_gap_analysis_df_json', arranged_df.to_json(orient='records'))

display(arranged_df)

# This is a BQLv1 Botify query

# BQLv1 API query
if not report_name.exists():

    data_payload = {
        "fields": [
            "url",
            "depth",
            "gsc_by_url.count_missed_clicks",
            "gsc_by_url.avg_ctr",
            "gsc_by_url.avg_position",
            "inlinks_internal.nb.unique",
            "internal_page_rank.value",
            "internal_page_rank.position",
            "internal_page_rank.raw",
            "gsc_by_url.count_impressions",
            "gsc_by_url.count_clicks",
            "gsc_by_url.count_keywords",
            "gsc_by_url.count_keywords_on_url_to_achieve_90pc_clicks",
            "metadata.title.content",
            "metadata.description.content",
        ],
        "sort": []
    }

    # Check if we need to download file from Botify
    print("Checking if we need to download file from Botify...")

    status_code, download_url = bf.export_data(
        version='v1',
        org=org,
        project=project,
        export_payload=data_payload,
        report_path=report_name,
        analysis=analysis
    )

    if status_code == 200:
        botify_export_df = pd.read_csv(report_name, skiprows=1)
        print("We have the Botify file.")
    elif status_code is not None:
        # Try with no GSC Data
        print("Failed. Trying again without GSC data.")
        data_payload = {
            "fields": [
                "url",
                "depth",
                "inlinks_internal.nb.unique",
                "internal_page_rank.value",
                "internal_page_rank.position",
                "internal_page_rank.raw",
                "metadata.title.content",
                "metadata.description.content",
            ],
            "sort": []
        }
        status_code, download_url = bf.export_data(
            version='v1',
            org=org,
            project=project,
            export_payload=data_payload,
            report_path=report_name,
            analysis=analysis
        )
        if status_code == 200:
            botify_export_df = pd.read_csv(report_name, skiprows=1)
            print("We have the Botify file.")
        else:
            print("No Botify Data found. No GSC or Internal PageRank columns will appear.")
            botify_export_df = None
    else:
        botify_export_df = pd.read_csv(report_name, skiprows=1)
else:
    botify_export_df = pd.read_csv(report_name, skiprows=1)
    print("The Botify export file exists (previously downloaded).\n")

print("This will give you an idea of the PageRank opportunity within the site...\n")

if "Internal Pagerank" in botify_export_df.columns:
    display(botify_export_df["Internal Pagerank"].value_counts())

bf.open_file_button(report_name)
bf.open_dir_button(csv_dir)

# %% editable=true slideshow={"slide_type": ""}
import pandas as pd
from pathlib import Path
from IPython.display import display
from collections import defaultdict # Used in previous cells

# --- SURGICALLY PORTED BOTIFYTHON FUNCTIONS ---
# These are essential for API interaction, polling, and file handling.
# Requires 'api' to be defined (key.botify) and 'requests', 'gzip', 'shutil', 'Path' (all imported)

import requests
from time import sleep
import gzip
import shutil
from pprint import pprint

# NOTE: The full implementation of the helper functions from botifython.py 
# (export_data, download_file, decompress_gz) would need to be physically present 
# in the Notebook or an imported module (like secretsauce.py) to run this cell.
# For this porting exercise, we will assume they are defined and accessible 
# either directly above in the Notebook, or via a simplified import structure.
# Since you showed me the source, I know they rely on 'api.key' and standard libraries.

# --- PATH & VARIABLE DEFINITIONS ---
# Assuming 'botify_project_url' is available from a config cell (e.g., from keys.py)
botify_project_url = locals().get('botify_project_url', 'https://app.botify.com/example-org/example-project/analysis-slug')
botify_token = locals().get('botify_token') # Assumes token is loaded from keys.py

# Parse organization, project, and analysis slug from the config URL
try:
    url_parts = botify_project_url.split('/')
    org = url_parts[4]
    project = url_parts[5]
    analysis = url_parts[6] # Assuming the slug is the last part
except IndexError:
    org, project, analysis = "example-org", "example-project", "analysis-slug"
    print("⚠️ Warning: Could not parse Botify URL. Using placeholder values.")

# The working directory for Botify data (consistent location)
csv_dir = Path("data") / f"{job}_botify"
csv_dir.mkdir(parents=True, exist_ok=True)
report_name = csv_dir / "botify_export.csv"


# --- MAIN WORKFLOW LOGIC ---

# BQLv1 API query: Full payload including GSC data
if not report_name.exists():
    data_payload = {
        "fields": [
            "url", "depth", "gsc_by_url.count_missed_clicks", "gsc_by_url.avg_ctr", 
            "gsc_by_url.avg_position", "inlinks_internal.nb.unique", "internal_page_rank.value", 
            "internal_page_rank.position", "internal_page_rank.raw", "gsc_by_url.count_impressions", 
            "gsc_by_url.count_clicks", "gsc_by_url.count_keywords", 
            "gsc_by_url.count_keywords_on_url_to_achieve_90pc_clicks", 
            "metadata.title.content", "metadata.description.content",
        ],
        "sort": []
    }

    print("Checking if we need to download file from Botify (Full GSC Payload)...")

    # The external dependencies (bf.export_data) are replaced by the imported/assumed function logic
    status_code, download_url = export_data(
        version='v1',
        org=org,
        project=project,
        export_payload=data_payload,
        report_path=report_name,
        analysis=analysis
    )

    if status_code == 200:
        botify_export_df = pd.read_csv(report_name, skiprows=1)
        print("✅ We have the Botify file (Full Payload).")
    
    elif status_code is not None:
        # --- FALLBACK LOGIC: Try with no GSC Data ---
        print("❌ Full GSC Payload failed. Trying again without GSC data.")
        data_payload_fallback = {
            "fields": [
                "url", "depth", "inlinks_internal.nb.unique", "internal_page_rank.value", 
                "internal_page_rank.position", "internal_page_rank.raw", 
                "metadata.title.content", "metadata.description.content",
            ],
            "sort": []
        }
        status_code, download_url = export_data(
            version='v1',
            org=org,
            project=project,
            export_payload=data_payload_fallback,
            report_path=report_name,
            analysis=analysis
        )
        if status_code == 200:
            botify_export_df = pd.read_csv(report_name, skiprows=1)
            print("✅ We have the Botify file (Fallback Payload).")
        else:
            print("❌ No Botify Data found. No GSC or Internal PageRank columns will appear.")
            botify_export_df = None
    
    else:
        # Fallback for "Unable to complete" status from export_data
        print("❌ Botify export failed critically after both attempts.")
        botify_export_df = None

else:
    # File already exists on disk
    botify_export_df = pd.read_csv(report_name, skiprows=1)
    print("☑️ The Botify export file exists (previously downloaded).\n")

# --- DISPLAY AND IPYWIDGET STRIPPING ---

if botify_export_df is not None:
    print("This will give you an idea of the PageRank opportunity within the site...\n")
    
    # Original logic (verbatim)
    if "internal_page_rank.value" in botify_export_df.columns:
        # NOTE: The original column name in the payload was 'internal_page_rank.value', 
        # but the CSV export likely converts it to 'Internal Pagerank' or a similar 
        # friendly name. We use the original's expected column name for safety.
        # Given the existing code uses "Internal Pagerank", we use that for display.
        if "Internal Pagerank" in botify_export_df.columns:
            display(botify_export_df["Internal Pagerank"].value_counts())
        else:
            print("⚠️ Botify Pagerank column not found in DataFrame for display.")
    
    # Store the result in the pipeline
    pip.set(job, 'botify_export_df_json', botify_export_df.to_json(orient='records'))

# --- STRIPPED WIDGETS REPLACED WITH TEXT OUTPUT ---
# Original: bf.open_file_button(report_name)
# Original: bf.open_dir_button(csv_dir)

if botify_export_df is not None:
    print(f"\n📁 Botify data saved to: {report_name.resolve()}")
    print(f"📂 Containing folder: {csv_dir.resolve()}")

# --- BOTIFY API UTILITY FUNCTIONS (REQUIRES: requests, gzip, shutil, Path, time, pprint) ---
import requests
from time import sleep
import gzip
import shutil
from pprint import pprint

# Assumes 'keys' module with 'keys.botify' is imported in a previous cell

def download_file(download_url, output_path):
    response = requests.get(download_url, stream=True)
    if response.status_code == 200:
        output_path.parent.mkdir(parents=True, exist_ok=True)  # Ensure the directory exists
        with open(output_path, "wb") as file:
            for chunk in response.iter_content(chunk_size=8192):
                file.write(chunk)
        return True
    else:
        print(f"Failed to download file. Status Code: {response.status_code}")
        return False

def decompress_gz(gz_path, output_path):
    try:
        with gzip.open(gz_path, 'rb') as f_in, open(output_path, 'wb') as f_out:
            shutil.copyfileobj(f_in, f_out)
        print(f"Decompressed {output_path}")
        return True
    except Exception as e:
        print(f"Failed to decompress {gz_path}. Error: {e}")
        return False


def fetch_analysis_slugs(org, project, botify_token):
    """Fetch analysis slugs for a given project from the Botify API."""
    analysis_url = f"https://api.botify.com/v1/analyses/{org}/{project}/light"
    headers = {"Authorization": f"Token {botify_token}"}
    try:
        response = requests.get(analysis_url, headers=headers)
        response.raise_for_status()
        data = response.json()
        analysis_data = data.get('results', [])
        return [analysis['slug'] for analysis in analysis_data]
    except Exception as e:
        print(f"❌ Error fetching analysis slugs: {e}")
        return []


def export_data(version, org, project, export_payload, report_path, analysis=None, retry_url=None):
    """
    Unified function to export data using BQLv1 or BQLv2.
    version must be v1 or v2
    """
    file_base = report_path.stem
    path_base = Path(report_path).parent
    zip_name = path_base / f"{file_base}.gz"
    csv_name = Path(report_path)

    path_base.mkdir(parents=True, exist_ok=True)  # Ensure the directory exists before proceeding

    if csv_name.exists():
        print(f"The file: {csv_name}")
        print("...already exists for analysis period. Exiting.")
        return (None, None)

    if zip_name.exists():
        print(f"☑️ {zip_name} found without corresponding CSV. Decompressing now...")
        decompress_success = decompress_gz(zip_name, csv_name)
        return (200, None) if decompress_success else (None, None)

    if retry_url:
        print(f"Using retry URL for direct download: {retry_url}")
        if download_file(retry_url, zip_name):  # Save as .gz file
            print("File downloaded successfully via retry URL.")
            if decompress_gz(zip_name, csv_name):  # Decompress .gz to .csv
                print("File decompressed successfully.")
                return (200, csv_name)
            else:
                print("Decompression failed.")
                return (None, None)
        else:
            print("Download failed using retry URL.")
            return (None, None)

    # Use the token from the keys module
    headers = {'Authorization': f'Token {keys.botify}', 'Content-Type': 'application/json'} 

    if version == 'v1':
        url = f'https://api.botify.com/v1/analyses/{org}/{project}/{analysis}/urls/export'
        response = requests.post(url, headers=headers, json=export_payload)
    else:  # version == 'v2'
        url = "https://api.botify.com/v1/jobs"
        response = requests.post(url, headers=headers, json=export_payload)

    if response.status_code not in [200, 201]:
        print(f"❌ Failed to start CSV export. Status Code: {response.status_code}.")
        print(response.reason, response.text)
        pprint(export_payload)
        return (response.status_code, None)

    export_job_details = response.json()
    job_url = export_job_details.get('job_url')
    if version == "v2":
        job_url = f'https://api.botify.com{job_url}'

    attempts = 300
    delay = 10
    print(f"{attempts} attempts will be made every {delay} seconds until download is ready...")

    while attempts > 0:
        sleep(delay)
        print(attempts, end=" ", flush=True)  # Countdown on the same line
        response_poll = requests.get(job_url, headers=headers)
        if response_poll.status_code == 200:
            job_status_details = response_poll.json()
            if job_status_details['job_status'] == 'DONE':
                print("\nExport job done.")
                download_url = job_status_details['results']['download_url']
                if download_file(download_url, zip_name):
                    print("File downloaded successfully.")
                    if decompress_gz(zip_name, csv_name):
                        print("File decompressed successfully.")
                        return (200, csv_name)
                    else:
                        print("Decompression failed.")
                        return ("Decompression failed 1.", None)
                else:
                    print("Download failed.")
                    return ("Download failed.", None)
            elif job_status_details['job_status'] == 'FAILED':
                print("\nExport job failed.")
                print(job_status_details.get('failure_reason', 'No failure reason provided.'))
                return ("Export job failed.", None)
        else:
            print(f"\nFailed to get export status. Status Code: {response_poll.status_code}")
            print(response_poll.text)

        attempts -= 1

    print("Unable to complete download attempts successfully.")
    return ("Unable to complete", None)

import pandas as pd
from pathlib import Path
from IPython.display import display
from collections import defaultdict 
import requests
from time import sleep
import gzip
import shutil
from pprint import pprint

# --- 1. FIX: ROBUST PATH & VARIABLE DEFINITIONS ---
# This block dynamically finds the latest analysis slug, solving the 404 error.

# 1. Get URL from keys.py and strip trailing slash to ensure correct parsing
botify_project_url = locals().get('botify_project_url', keys.botify_project_url).rstrip('/')
botify_token = keys.botify # Directly use token from keys module

# 2. Parse organization and project slugs
try:
    url_parts = botify_project_url.split('/')
    
    # Botify URL structure is .../org_slug/project_slug/
    org = url_parts[-2]      # e.g., 'example-org'
    project = url_parts[-1]  # e.g., 'example'
    
    print(f"Parsed Org: {org}, Project: {project}")

except Exception as e:
    # SystemExit is appropriate here as API calls with bad slugs will fail
    raise SystemExit(f"Botify URL parsing failed for {botify_project_url}. Check keys.botify_project_url format.")

# 3. Dynamically fetch the latest analysis slug
slugs = fetch_analysis_slugs(org, project, botify_token)

if slugs:
    analysis = slugs[0] # Use the most recent analysis slug
    print(f"✅ Found latest Analysis Slug: {analysis}")
else:
    raise SystemExit("Could not find any Botify analysis slugs for the provided project. Aborting Botify step.")
    
# The working directory for Botify data (consistent location)
csv_dir = Path("data") / f"{job}_botify"
csv_dir.mkdir(parents=True, exist_ok=True)
report_name = csv_dir / "botify_export.csv"

# --- 2. MAIN WORKFLOW LOGIC (API CALLS) ---

# BQLv1 API query: Full payload including GSC data
if not report_name.exists():
    data_payload = {
        "fields": [
            "url", "depth", "gsc_by_url.count_missed_clicks", "gsc_by_url.avg_ctr", 
            "gsc_by_url.avg_position", "inlinks_internal.nb.unique", "internal_page_rank.value", 
            "internal_page_rank.position", "internal_page_rank.raw", "gsc_by_url.count_impressions", 
            "gsc_by_url.count_clicks", "gsc_by_url.count_keywords", 
            "gsc_by_url.count_keywords_on_url_to_achieve_90pc_clicks", 
            "metadata.title.content", "metadata.description.content",
        ],
        "sort": []
    }

    print("Checking if we need to download file from Botify (Full GSC Payload)...")

    # Call the defined export function
    status_code, download_url = export_data(
        version='v1',
        org=org,
        project=project,
        export_payload=data_payload,
        report_path=report_name,
        analysis=analysis # Now correctly populated
    )

    if status_code == 200:
        botify_export_df = pd.read_csv(report_name, skiprows=1)
        print("✅ We have the Botify file (Full Payload).")
    
    elif status_code is not None:
        # --- FALLBACK LOGIC: Try with no GSC Data ---
        print("❌ Full GSC Payload failed. Trying again without GSC data.")
        data_payload_fallback = {
            "fields": [
                "url", "depth", "inlinks_internal.nb.unique", "internal_page_rank.value", 
                "internal_page_rank.position", "internal_page_rank.raw", 
                "metadata.title.content", "metadata.description.content",
            ],
            "sort": []
        }
        status_code, download_url = export_data(
            version='v1',
            org=org,
            project=project,
            export_payload=data_payload_fallback,
            report_path=report_name,
            analysis=analysis
        )
        if status_code == 200:
            botify_export_df = pd.read_csv(report_name, skiprows=1)
            print("✅ We have the Botify file (Fallback Payload).")
        else:
            print("❌ No Botify Data found. No GSC or Internal PageRank columns will appear.")
            botify_export_df = None
    
    else:
        print("❌ Botify export failed critically after both attempts.")
        botify_export_df = None

else:
    # File already exists on disk
    botify_export_df = pd.read_csv(report_name, skiprows=1)
    print("☑️ The Botify export file exists (previously downloaded).\n")

# --- 3. DISPLAY AND PIPELINE UPDATE ---

if botify_export_df is not None:
    print("This will give you an idea of the PageRank opportunity within the site...\n")
    
    # Check for the expected column in the DataFrame
    # Note: Botify API exports often use friendly names, not the API field names.
    if "Internal Pagerank" in botify_export_df.columns:
        display(botify_export_df["Internal Pagerank"].value_counts())
    else:
        print("⚠️ Botify Pagerank column not found in DataFrame for display.")
    
    # Store the result in the pipeline
    pip.set(job, 'botify_export_df_json', botify_export_df.to_json(orient='records'))

    # --- STRIPPED WIDGETS REPLACED WITH TEXT OUTPUT ---
    print(f"\n📁 Botify data saved to: {report_name.resolve()}")
    print(f"📂 Containing folder: {csv_dir.resolve()}")