RobbieLiFirstAgent

import json
import csv
import io
import re
from datetime import datetime
from collections import Counter
from js import document, console, Blob, URL, window
from pyodide.ffi import create_proxy

# ============================================================================
# Global State
# ============================================================================
current_data = None
original_data = None
column_names = []
cleaning_history = []

# ============================================================================
# CSV Parsing & Issue Detection (deterministic — no LLM needed)
# ============================================================================
class CSVDataHandler:
    @staticmethod
    def parse_csv(csv_content):
        try:
            reader = csv.DictReader(io.StringIO(csv_content))
            data = list(reader)
            columns = reader.fieldnames if reader.fieldnames else []
            return data, columns
        except Exception as e:
            log_activity(f"Error parsing CSV: {str(e)}", "error")
            return None, None

    @staticmethod
    def detect_issues(data, columns):
        issues = []
        if not data:
            return issues

        for col in columns:
            missing_count = sum(1 for row in data if not row.get(col) or str(row.get(col)).strip() == '')
            if missing_count > 0:
                pct = (missing_count / len(data)) * 100
                issues.append({
                    'type': 'missing_values',
                    'severity': 'high' if pct > 20 else 'medium',
                    'column': col,
                    'count': missing_count,
                    'message': f"{col}: {missing_count} missing values ({pct:.1f}%)"
                })

        seen = set()
        dupes = 0
        for row in data:
            key = tuple(sorted(row.items()))
            if key in seen:
                dupes += 1
            else:
                seen.add(key)
        if dupes > 0:
            issues.append({
                'type': 'duplicates', 'severity': 'medium',
                'count': dupes, 'message': f"Found {dupes} duplicate rows"
            })

        date_patterns = [
            r'\d{4}-\d{2}-\d{2}',
            r'\d{2}/\d{2}/\d{4}',
            r'\d{2}-\d{2}-\d{4}',
        ]
        for col in columns:
            values = [str(row.get(col, '')).strip() for row in data if row.get(col)]
            formats_found = set()
            for val in values[:100]:
                for p in date_patterns:
                    if re.match(p, val):
                        formats_found.add(p)
            if len(formats_found) > 1:
                issues.append({
                    'type': 'inconsistent_format', 'severity': 'low',
                    'column': col,
                    'message': f"{col}: Inconsistent date formats detected"
                })

        whitespace_cols = [
            col for col in columns
            if any(str(row.get(col, '')).startswith(' ') or str(row.get(col, '')).endswith(' ')
                   for row in data[:100] if row.get(col))
        ]
        if whitespace_cols:
            issues.append({
                'type': 'whitespace', 'severity': 'low',
                'columns': whitespace_cols,
                'message': f"Whitespace issues in: {', '.join(whitespace_cols[:3])}"
            })

        return issues

# ============================================================================
# Tool Functions (called by AgentOp's LLM dispatcher)
# ============================================================================
async def get_cleaning_suggestions(columns: str, row_count: str, issues_json: str) -> str:
    """Analyze CSV column names, row count, and detected issues, then return 5-8 prioritised
    data cleaning suggestions as a JSON array. Each suggestion must have: action (snake_case),
    description (plain English), priority (high/medium/low), target (column name or 'all')."""
    # This tool description is sent to the LLM — the LLM fills in its response
    # via the callLLM bridge. We return a placeholder that the bridge replaces.
    return json.dumps({
        "columns": columns,
        "row_count": row_count,
        "issues": issues_json
    })

async def generate_cleaning_code(instruction: str, columns: str, sample_rows: str) -> str:
    """Given a plain-English cleaning instruction, the CSV column names, and sample rows,
    generate Python code that cleans a list-of-dicts named 'data' and stores the result
    in 'cleaned_data'. Return ONLY executable Python code with no markdown fences."""
    return json.dumps({
        "instruction": instruction,
        "columns": columns,
        "sample": sample_rows
    })

def get_tool_schemas():
    return [
        {
            "type": "function",
            "function": {
                "name": "get_cleaning_suggestions",
                "description": "Analyze CSV metadata and detected issues to produce prioritised cleaning suggestions as JSON.",
                "parameters": {
                    "type": "object",
                    "properties": {
                        "columns": {"type": "string", "description": "Comma-separated column names"},
                        "row_count": {"type": "string", "description": "Number of rows in the dataset"},
                        "issues_json": {"type": "string", "description": "JSON string of detected issues"}
                    },
                    "required": ["columns", "row_count", "issues_json"]
                }
            }
        },
        {
            "type": "function",
            "function": {
                "name": "generate_cleaning_code",
                "description": "Generate Python code to execute a plain-English CSV cleaning instruction on a list of dicts.",
                "parameters": {
                    "type": "object",
                    "properties": {
                        "instruction": {"type": "string", "description": "The cleaning task in plain English"},
                        "columns": {"type": "string", "description": "Comma-separated column names"},
                        "sample_rows": {"type": "string", "description": "JSON of first 5 rows for context"}
                    },
                    "required": ["instruction", "columns", "sample_rows"]
                }
            }
        }
    ]

# ============================================================================
# Deterministic Cleaning Operations (no LLM needed)
# ============================================================================
def remove_duplicates(data):
    seen, cleaned = set(), []
    for row in data:
        key = tuple(sorted(row.items()))
        if key not in seen:
            seen.add(key)
            cleaned.append(row)
    return cleaned

def fill_missing_values(data, columns):
    for col in columns:
        values = [row[col] for row in data if row.get(col) and str(row[col]).strip()]
        if not values:
            continue
        try:
            nums = [float(v) for v in values]
            fill = f"{sum(nums)/len(nums):.2f}"
        except:
            fill = Counter(values).most_common(1)[0][0]
        for row in data:
            if not row.get(col) or str(row[col]).strip() == '':
                row[col] = fill
    return data

def standardize_formats(data, columns):
    for col in columns:
        for row in data:
            val = str(row.get(col, '')).strip()
            if not val:
                continue
            for fmt in ['%m/%d/%Y', '%d-%m-%Y', '%Y-%m-%d', '%m-%d-%Y']:
                try:
                    from datetime import datetime as dt
                    row[col] = dt.strptime(val, fmt).strftime('%Y-%m-%d')
                    break
                except:
                    continue
    return data

def clean_whitespace(data, columns):
    for row in data:
        for col in columns:
            if row.get(col):
                row[col] = str(row[col]).strip()
    return data

# ============================================================================
# UI Helpers
# ============================================================================
def log_activity(message, level="info"):
    log_div = document.getElementById("activityLog")
    if log_div.children.length == 1 and "No activities" in log_div.innerHTML:
        log_div.innerHTML = ""
    colors = {"info":"text-blue-600","success":"text-green-600","error":"text-red-600","warning":"text-yellow-600"}
    icons  = {"info":"fa-info-circle","success":"fa-check-circle","error":"fa-exclamation-circle","warning":"fa-exclamation-triangle"}
    ts = datetime.now().strftime("%H:%M:%S")
    entry = document.createElement("div")
    entry.className = "text-sm py-1"
    entry.innerHTML = (f'<span class="text-gray-400">[{ts}]</span> '
                       f'<i class="fas {icons.get(level,"fa-circle")} {colors.get(level,"text-gray-600")} mr-1"></i>'
                       f'<span class="{colors.get(level,"text-gray-600")}">{message}</span>')
    log_div.appendChild(entry)
    log_div.scrollTop = log_div.scrollHeight

def show_loading(msg="Processing..."):
    document.getElementById("loadingOverlay").classList.remove("hidden")
    document.getElementById("loadingText").textContent = msg

def hide_loading():
    document.getElementById("loadingOverlay").classList.add("hidden")

def update_preview():
    if not current_data or not column_names:
        return
    document.getElementById("rowCount").textContent = str(len(current_data))
    document.getElementById("colCount").textContent = str(len(column_names))
    thead = document.getElementById("previewHead")
    tbody = document.getElementById("previewBody")
    thead.innerHTML = tbody.innerHTML = ""
    hr = document.createElement("tr")
    for col in column_names:
        th = document.createElement("th")
        th.className = "px-3 py-2 text-left text-xs font-semibold text-gray-700"
        th.textContent = col
        hr.appendChild(th)
    thead.appendChild(hr)
    for rd in current_data[:10]:
        row = document.createElement("tr")
        for col in column_names:
            td = document.createElement("td")
            td.className = "px-3 py-2 text-xs text-gray-600"
            td.textContent = str(rd.get(col, ''))[:50]
            row.appendChild(td)
        tbody.appendChild(row)
    document.getElementById("previewSection").classList.remove("hidden")

def display_issues(issues):
    if not issues:
        return
    lst = document.getElementById("issuesList")
    lst.innerHTML = ""
    for issue in issues:
        div = document.createElement("div")
        div.className = "bg-gray-50 rounded p-3 border-l-4 border-yellow-400"
        div.innerHTML = (f'<div class="flex items-start">'
                         f'<span class="issue-badge issue-{issue["severity"]} mr-2">{issue["severity"].upper()}</span>'
                         f'<p class="text-sm text-gray-700">{issue["message"]}</p></div>')
        lst.appendChild(div)
    document.getElementById("issuesSection").classList.remove("hidden")
    log_activity(f"Detected {len(issues)} data quality issues", "warning")

def display_suggestions(suggestions_json):
    try:
        suggestions = json.loads(suggestions_json)
    except:
        log_activity("Could not parse AI suggestions", "warning")
        return
    div = document.getElementById("suggestionsList")
    div.innerHTML = ""
    priority_colors = {"high":"text-red-600","medium":"text-yellow-600","low":"text-blue-600"}
    for i, s in enumerate(suggestions):
        item = document.createElement("div")
        item.className = "bg-gradient-to-r from-purple-50 to-indigo-50 rounded-lg p-3 border border-indigo-200"
        pc = priority_colors.get(s.get('priority','low'), 'text-gray-600')
        item.innerHTML = (f'<div class="flex items-start justify-between">'
                          f'<div class="flex-1">'
                          f'<h4 class="font-semibold text-sm text-gray-800">{s.get("action","").replace("_"," ").title()}</h4>'
                          f'<p class="text-xs text-gray-600 mt-1">{s.get("description","")}</p>'
                          f'<span class="text-xs {pc} font-semibold mt-1 inline-block">Priority: {s.get("priority","low").upper()}</span>'
                          f'</div>'
                          f'<button onclick="applySuggestion({i})" class="ml-2 bg-indigo-600 hover:bg-indigo-700 text-white text-xs px-3 py-1 rounded">Apply</button>'
                          f'</div>')
        div.appendChild(item)
    window._ai_suggestions = suggestions
    document.getElementById("suggestionsSection").classList.remove("hidden")
    log_activity(f"Generated {len(suggestions)} AI suggestions", "success")

# ============================================================================
# Event Handlers
# ============================================================================
async def handle_file_upload(event):
    global current_data, original_data, column_names
    show_loading("Loading CSV file...")
    try:
        file = event.target.files.item(0)
        if not file:
            return
        buf = await file.arrayBuffer()
        csv_content = buf.to_bytes().decode('utf-8')
        data, columns = CSVDataHandler.parse_csv(csv_content)
        if data is None:
            return
        current_data = data
        original_data = [row.copy() for row in data]
        column_names = columns
        document.getElementById("fileName").textContent = f"Loaded: {file.name}"
        document.getElementById("fileName").classList.remove("hidden")
        update_preview()
        issues = CSVDataHandler.detect_issues(data, columns)
        if issues:
            display_issues(issues)
        document.getElementById("downloadSection").classList.remove("hidden")
        log_activity(f"Loaded {file.name}: {len(data)} rows, {len(columns)} columns", "success")
    except Exception as e:
        log_activity(f"Error loading file: {str(e)}", "error")
    finally:
        hide_loading()

async def handle_quick_action(action):
    global current_data
    if not current_data:
        log_activity("Please upload a CSV file first", "warning")
        return
    show_loading(f"Applying {action.replace('_',' ')}...")
    try:
        before = len(current_data)
        if action == "remove_duplicates":
            current_data = remove_duplicates(current_data)
            log_activity(f"Removed {before - len(current_data)} duplicate rows", "success")
        elif action == "fill_missing":
            current_data = fill_missing_values(current_data, column_names)
            log_activity("Filled missing values", "success")
        elif action == "standardize_formats":
            current_data = standardize_formats(current_data, column_names)
            log_activity("Standardized date formats to YYYY-MM-DD", "success")
        elif action == "clean_whitespace":
            current_data = clean_whitespace(current_data, column_names)
            log_activity("Cleaned leading/trailing whitespace", "success")
        update_preview()
        cleaning_history.append(action)
    except Exception as e:
        log_activity(f"Error during {action}: {str(e)}", "error")
    finally:
        hide_loading()

async def get_ai_suggestions_handler():
    global current_data, column_names
    if not current_data:
        log_activity("Please upload a CSV file first", "warning")
        return
    show_loading("Getting AI suggestions...")
    try:
        issues = CSVDataHandler.detect_issues(current_data, column_names)
        pyodide_instance = window.pyodide
        pyodide_instance.globals.set('_cols', ', '.join(column_names))
        pyodide_instance.globals.set('_rows', str(len(current_data)))
        pyodide_instance.globals.set('_issues', json.dumps(issues))
        result = await pyodide_instance.runPythonAsync(
            'await process_user_query(f"Analyze this CSV with columns: {_cols}, {_rows} rows. Issues: {_issues}. Return cleaning suggestions as JSON array.")'
        )
        if result:
            display_suggestions(result)
    except Exception as e:
        log_activity(f"Error getting suggestions: {str(e)}", "error")
    finally:
        hide_loading()

async def execute_custom_cleaning_handler():
    global current_data
    if not current_data:
        log_activity("Please upload a CSV file first", "warning")
        return
    query = document.getElementById("customQuery").value.strip()
    if not query:
        log_activity("Please enter a cleaning instruction", "warning")
        return
    show_loading("Executing custom cleaning...")
    try:
        sample = json.dumps(current_data[:5])
        pyodide_instance = window.pyodide
        pyodide_instance.globals.set('_instruction', query)
        pyodide_instance.globals.set('_columns', ', '.join(column_names))
        pyodide_instance.globals.set('_sample', sample)
        code_result = await pyodide_instance.runPythonAsync(
            'await process_user_query(f"Generate Python code to clean this CSV. Instruction: {_instruction}. Columns: {_columns}. Sample rows: {_sample}. Return ONLY executable Python code. Input is list-of-dicts named \'data\', output must be \'cleaned_data\'.")'
        )
        if code_result:
            code = re.sub(r'```python\n?|```', '', code_result).strip()
            local_vars = {'data': current_data}
            exec(code, {}, local_vars)
            if 'cleaned_data' in local_vars:
                current_data = local_vars['cleaned_data']
                update_preview()
                log_activity(f"Executed: {query}", "success")
        document.getElementById("customQuery").value = ""
    except Exception as e:
        log_activity(f"Error executing cleaning: {str(e)}", "error")
    finally:
        hide_loading()

async def apply_suggestion_handler(index):
    global current_data
    suggestions = getattr(window, '_ai_suggestions', None)
    if not suggestions or index >= len(suggestions):
        return
    s = suggestions[index]
    show_loading(f"Applying: {s.get('action','')}")
    try:
        instruction = f"{s.get('action','')}: {s.get('description','')}"
        sample = json.dumps(current_data[:5])
        pyodide_instance = window.pyodide
        pyodide_instance.globals.set('_instruction', instruction)
        pyodide_instance.globals.set('_columns', ', '.join(column_names))
        pyodide_instance.globals.set('_sample', sample)
        code_result = await pyodide_instance.runPythonAsync(
            'await process_user_query(f"Generate Python code to: {_instruction}. Columns: {_columns}. Sample: {_sample}. Return ONLY Python. Input=\'data\' (list of dicts), output=\'cleaned_data\'.")'
        )
        if code_result:
            code = re.sub(r'```python\n?|```', '', code_result).strip()
            local_vars = {'data': current_data}
            exec(code, {}, local_vars)
            if 'cleaned_data' in local_vars:
                current_data = local_vars['cleaned_data']
                update_preview()
                log_activity(f"Applied: {s.get('action','')}", "success")
    except Exception as e:
        log_activity(f"Error applying suggestion: {str(e)}", "error")
    finally:
        hide_loading()

def download_cleaned_csv():
    global current_data, column_names
    if not current_data:
        log_activity("No data to download", "warning")
        return
    try:
        out = io.StringIO()
        writer = csv.DictWriter(out, fieldnames=column_names)
        writer.writeheader()
        writer.writerows(current_data)
        blob = Blob.new([out.getvalue()], {"type": "text/csv"})
        url = URL.createObjectURL(blob)
        link = document.createElement("a")
        link.href = url
        link.download = f"cleaned_data_{datetime.now().strftime('%Y%m%d_%H%M%S')}.csv"
        link.click()
        URL.revokeObjectURL(url)
        log_activity("Downloaded cleaned CSV file", "success")
    except Exception as e:
        log_activity(f"Error downloading file: {str(e)}", "error")

# ============================================================================
# Setup
# ============================================================================
def setup():
    log_activity("CSV Data Cleaner initialized", "info")
    document.getElementById("fileInput").addEventListener("change", create_proxy(handle_file_upload))
    window.handleQuickAction    = create_proxy(handle_quick_action)
    window.getAISuggestions     = create_proxy(get_ai_suggestions_handler)
    window.executeCustomCleaning = create_proxy(execute_custom_cleaning_handler)
    window.applySuggestion      = create_proxy(apply_suggestion_handler)
    window.downloadCleanedCSV   = create_proxy(download_cleaned_csv)

setup()