use clap::Subcommand;
use serde_json;
use std::collections::{HashMap, HashSet};

use theseus_core::{ImportNode, ImportEdge, ImportRequest, ImportResult, ApiResponse};

use crate::BoxErr;

#[derive(Subcommand)]
pub enum TrafficAction {
    /// Import Yakit Excel traffic into the attack graph
    Import {
        project_id: String,

        /// Yakit Excel traffic file (.xlsx)
        #[arg(long)]
        yakit_excel: String,

        /// Preview only — do not post to server
        #[arg(long)]
        dry_run: bool,

        /// Only import rows matching this Host column value (prefix match)
        #[arg(long)]
        host: Option<String>,

        /// Output raw JSON instead of summary
        #[arg(long)]
        json: bool,
    },
    /// Analyze Yakit Excel traffic — build deterministic attack graph for external Agents
    Analyze {
        project_id: String,

        /// Yakit Excel traffic file (.xlsx)
        #[arg(long)]
        yakit_excel: String,

        /// Preview only — show analysis graph without posting to server
        #[arg(long)]
        dry_run: bool,

        /// Only analyze rows matching this Host column value (prefix match)
        #[arg(long)]
        host: Option<String>,

        /// Output raw JSON instead of summary
        #[arg(long)]
        json: bool,
    },
}

pub async fn handle_traffic(
    client: &reqwest::Client,
    base: &str,
    action: TrafficAction,
) -> Result<(), BoxErr> {
    match action {
        TrafficAction::Import {
            project_id,
            yakit_excel,
            dry_run,
            host,
            json,
        } => {
            handle_traffic_import(
                client,
                base,
                &project_id,
                &yakit_excel,
                dry_run,
                host.as_deref(),
                json,
            )
            .await
        }
        TrafficAction::Analyze {
            project_id,
            yakit_excel,
            dry_run,
            host,
            json,
        } => {
            handle_traffic_analyze(
                client,
                base,
                &project_id,
                &yakit_excel,
                dry_run,
                host.as_deref(),
                json,
            )
            .await
        }
    }
}

// ── Traffic import ──

async fn handle_traffic_import(
    client: &reqwest::Client,
    base: &str,
    project_id: &str,
    yakit_excel: &str,
    dry_run: bool,
    host_filter: Option<&str>,
    json_output: bool,
) -> Result<(), BoxErr> {
    let parsed_rows = parse_yakit_rows(yakit_excel, host_filter)?;
    if parsed_rows.is_empty() {
        eprintln!("No matching rows found in XLSX.");
        return Ok(());
    }

    // Build deduplicated nodes and edges grouped by host
    let mut host_nodes: HashMap<String, ImportNode> = HashMap::new();
    let mut endpoint_nodes: Vec<ImportNode> = Vec::new();
    let mut edges: Vec<ImportEdge> = Vec::new();
    let mut seen_endpoints: HashSet<String> = HashSet::new();

    for row in &parsed_rows {
        host_nodes.entry(row.host.clone()).or_insert_with(|| {
            let mut props = serde_json::json!({});
            if let serde_json::Value::Object(ref mut m) = props {
                m.insert("ip".into(), serde_json::Value::String(row.ip.clone()));
            }
            ImportNode {
                label: row.host.clone(),
                node_type: Some("host".to_string()),
                content: Some(format!("Host: {}\nIP: {}", row.host, row.ip)),
                properties: Some(props),
            }
        });

        let endpoint_label = format!("{} {}", row.method, row.url);
        if seen_endpoints.insert(endpoint_label.clone()) {
            let content = build_endpoint_content(row);
            let props = build_endpoint_props(row);
            endpoint_nodes.push(ImportNode {
                label: endpoint_label.clone(),
                node_type: Some("finding".to_string()),
                content: Some(content),
                properties: Some(props),
            });
        }
        // Always add an edge (even if endpoint already seen, the edge is the finding instance)
        let edge_label = if row.status_code.is_empty() {
            row.method.clone()
        } else {
            format!("{} {}", row.method, row.status_code)
        };
        edges.push(ImportEdge {
            source_label: row.host.clone(),
            target_label: format!("{} {}", row.method, row.url),
            label: Some(edge_label),
        });
    }

    // Combine all nodes: hosts first, then endpoints
    let mut all_nodes: Vec<ImportNode> = host_nodes.into_values().collect();
    all_nodes.append(&mut endpoint_nodes);

    let import_request = ImportRequest {
        nodes: all_nodes,
        edges,
    };

    let total_nodes = import_request.nodes.len();
    let total_edges = import_request.edges.len();
    let total_rows = parsed_rows.len();

    if json_output {
        if dry_run {
            let summary = serde_json::json!({
                "source": "yakit_excel",
                "file": yakit_excel,
                "dry_run": dry_run,
                "total_rows": total_rows,
                "total_nodes": total_nodes,
                "total_edges": total_edges,
                "import": import_request,
            });
            println!("{}", serde_json::to_string_pretty(&summary)?);
        }
    } else {
        println!("=== Yakit Traffic Import ===");
        println!("File:    {yakit_excel}");
        println!("Rows:    {total_rows}");
        println!("Nodes:   {total_nodes} (hosts + endpoints)");
        println!("Edges:   {total_edges}");
        if let Some(hf) = host_filter {
            println!("Filter:  host starts with '{hf}'");
        }
        if dry_run {
            println!("\n[dry-run] Not posting to server.");
            println!();
            // Show preview: list hosts and their endpoints
            let mut hosts_seen: HashSet<String> = HashSet::new();
            for e in &import_request.edges {
                if hosts_seen.insert(e.source_label.clone()) {
                    println!("▎ {}", e.source_label);
                }
                let edge_lbl = e.label.as_deref().unwrap_or("-");
                println!("    {}  →  {}", edge_lbl, e.target_label);
            }
        }
    }

    if !dry_run {
        let project_id = super::resolve_project_id(client, base, project_id).await?;
        let resp = client
            .post(format!("{base}/api/projects/{project_id}/import"))
            .json(&import_request)
            .send()
            .await?;
        if json_output {
            super::print_json(resp).await?;
        } else {
            eprintln!();
            let body: ApiResponse<ImportResult> = resp.json().await?;
            if body.success {
                if let Some(result) = body.data {
                    eprintln!("Imported: {} nodes created, {} edges created",
                        result.nodes_created, result.edges_created);
                    if result.nodes_skipped > 0 || result.edges_skipped > 0 {
                        eprintln!("Skipped:  {} nodes, {} edges",
                            result.nodes_skipped, result.edges_skipped);
                    }
                    for err in &result.errors {
                        eprintln!("  ! {err}");
                    }
                }
            } else {
                return Err(body.error.unwrap_or_else(|| "import failed".into()).into());
            }
        }
    }

    Ok(())
}

// ── Traffic analyze ──

async fn handle_traffic_analyze(
    client: &reqwest::Client,
    base: &str,
    project_id: &str,
    yakit_excel: &str,
    dry_run: bool,
    host_filter: Option<&str>,
    json_output: bool,
) -> Result<(), BoxErr> {
    let parsed_rows = parse_yakit_rows(yakit_excel, host_filter)?;
    if parsed_rows.is_empty() {
        eprintln!("No matching rows found in XLSX.");
        return Ok(());
    }

    // Sort by request time for sequence analysis
    let mut sorted_rows: Vec<&YakitRow> = parsed_rows.iter().collect();
    sorted_rows.sort_by(|a, b| a.request_time.cmp(&b.request_time));
    let total_rows = sorted_rows.len();

    // ── Step 1: Build endpoint identities + metadata ──
    let mut endpoint_identities: HashMap<String, String> = HashMap::new();
    let mut endpoint_meta: HashMap<String, (usize, Vec<String>, Vec<String>, Vec<String>)> = HashMap::new();
    // Stage 1 enrichment: per-endpoint aggregated data for semantic properties
    let mut endpoint_status_counts: HashMap<String, HashMap<String, usize>> = HashMap::new();
    let mut endpoint_request_body: HashMap<String, Option<String>> = HashMap::new();
    let mut endpoint_response_body: HashMap<String, Option<String>> = HashMap::new();
    let mut endpoint_first_raw_path: HashMap<String, String> = HashMap::new();
    let mut endpoint_first_url: HashMap<String, String> = HashMap::new();
    let mut time_ordered_ids: Vec<String> = Vec::new();
    let mut host_endpoint_count: HashMap<String, usize> = HashMap::new();
    let mut host_ips: HashMap<String, String> = HashMap::new();

    for row in &sorted_rows {
        let identity = endpoint_identity(&row.method, &row.host, &row.path);
        let display_label = format!("{} {}", row.method, row.url);
        endpoint_identities.entry(identity.clone()).or_insert_with(|| {
            time_ordered_ids.push(identity.clone());
            display_label
        });

        let entry = endpoint_meta.entry(identity.clone()).or_insert_with(|| (0, Vec::new(), Vec::new(), Vec::new()));
        entry.0 += 1;
        if !row.method.is_empty() && !entry.1.contains(&row.method) {
            entry.1.push(row.method.clone());
        }
        if !row.status_code.is_empty() && !entry.2.contains(&row.status_code) {
            entry.2.push(row.status_code.clone());
        }
        if !row.response_packet.is_empty() {
            let fields = extract_response_field_names(&row.response_packet);
            for f in fields {
                if !entry.3.contains(&f) {
                    entry.3.push(f);
                }
            }
        }

        // Stage 1 enrichment: aggregate per-endpoint data for semantic properties
        let sc = endpoint_status_counts.entry(identity.clone()).or_default();
        if !row.status_code.is_empty() {
            *sc.entry(row.status_code.clone()).or_default() += 1;
        }
        endpoint_request_body.entry(identity.clone()).or_insert_with(|| {
            if !row.request_packet.is_empty() {
                extract_http_body(&row.request_packet)
            } else {
                None
            }
        });
        endpoint_response_body.entry(identity.clone()).or_insert_with(|| {
            if !row.response_packet.is_empty() {
                extract_http_body(&row.response_packet)
            } else {
                None
            }
        });
        endpoint_first_raw_path.entry(identity.clone()).or_insert_with(|| row.path.clone());
        endpoint_first_url.entry(identity.clone()).or_insert_with(|| row.url.clone());

        *host_endpoint_count.entry(row.host.clone()).or_default() += 1;
        host_ips.entry(row.host.clone()).or_insert_with(|| row.ip.clone());
    }

    let unique_endpoints = endpoint_identities.len();

    // ── Step 2: Host nodes ──
    let mut host_nodes: HashMap<String, ImportNode> = HashMap::new();
    for (host, ip) in &host_ips {
        let ep_count = host_endpoint_count.get(host).copied().unwrap_or(0);
        let mut props = serde_json::json!({});
        if let serde_json::Value::Object(ref mut m) = props {
            m.insert("ip".into(), serde_json::Value::String(ip.clone()));
        }
        host_nodes.insert(host.clone(), ImportNode {
            label: host.clone(),
            node_type: Some("host".to_string()),
            content: Some(format!(
                "# Host: {host}\n\nIP: {ip}\n\nUnique endpoints observed: {ep_count}"
            )),
            properties: Some(props),
        });
    }

    // ── Step 3: Business function groups ──
    let mut bf_groups: Vec<(String, String, String, Vec<String>)> = Vec::new();
    // (host, bf_key, bf_label, [endpoint_identities])
    let mut bf_index: HashMap<String, usize> = HashMap::new();

    // Group endpoints by (host, first_meaningful_segment)
    for id in &time_ordered_ids {
        let (host, norm_path) = split_identity_host_path(id);
        let seg = first_meaningful_segment(&norm_path);
        let bf_key = if seg.is_empty() {
            host.clone()
        } else {
            format!("{host}/{seg}")
        };
        let bf_label = format!("{bf_key} [{host}]");

        if let Some(&idx) = bf_index.get(&bf_key) {
            bf_groups[idx].3.push(id.clone());
        } else {
            bf_index.insert(bf_key.clone(), bf_groups.len());
            bf_groups.push((host.clone(), bf_key, bf_label, vec![id.clone()]));
        }
    }

    // Build BF nodes
    let mut bf_endpoint_map: HashMap<String, Vec<String>> = HashMap::new();
    let mut bf_nodes: Vec<ImportNode> = Vec::new();
    for (host, bf_key, bf_label, ep_list) in &bf_groups {
        let ep_count = ep_list.len();
        let mut props = serde_json::json!({});
        if let serde_json::Value::Object(ref mut m) = props {
            m.insert("host".into(), serde_json::Value::String(host.clone()));
            m.insert("group_key".into(), serde_json::Value::String(bf_key.clone()));
        }
        bf_nodes.push(ImportNode {
            label: bf_label.clone(),
            node_type: Some("business_function".to_string()),
            content: Some(build_bf_content(bf_key, host, ep_count)),
            properties: Some(props),
        });
        bf_endpoint_map.insert(bf_label.clone(), ep_list.clone());
    }

    // ── Step 4: Endpoint nodes (finding type, no raw packets) ──
    let mut ep_nodes: Vec<ImportNode> = Vec::new();
    for id in &time_ordered_ids {
        let (host, norm_path) = split_identity_host_path(id);
        let meta = endpoint_meta.get(id).map(|m| m.clone()).unwrap_or((0, vec![], vec![], vec![]));
        let count = meta.0;
        let methods = &meta.1;
        let statuses = &meta.2;
        let resp_fields = &meta.3;
        let display_label = endpoint_identities.get(id).cloned().unwrap_or_else(|| id.clone());

        // Stage 1 enrichment data
        let status_counts = endpoint_status_counts.get(id);
        let raw_path = endpoint_first_raw_path.get(id).map(|s| s.as_str()).unwrap_or("");
        let req_body = endpoint_request_body.get(id).and_then(|o| o.as_deref());
        let res_body = endpoint_response_body.get(id).and_then(|o| o.as_deref());

        let content = build_analyze_endpoint_content(&display_label, &host, count, methods, statuses, resp_fields);
        let props = build_analyze_endpoint_props(
            &host, &norm_path, count, methods, statuses, resp_fields,
            &display_label, status_counts, raw_path,
            req_body, res_body,
        );

        ep_nodes.push(ImportNode {
            label: id.clone(),
            node_type: Some("finding".to_string()),
            content: Some(content),
            properties: Some(props),
        });
    }

    // ── Step 5: Edges ──
    let mut edges: Vec<ImportEdge> = Vec::new();

    // 5a: host → business_function (exposes)
    for (host, _bf_key, bf_label, _) in &bf_groups {
        edges.push(ImportEdge {
            source_label: host.clone(),
            target_label: bf_label.clone(),
            label: Some("exposes".to_string()),
        });
    }

    // 5b: business_function → endpoint (contains)
    for (bf_label, ep_list) in &bf_endpoint_map {
        for ep_id in ep_list {
            edges.push(ImportEdge {
                source_label: bf_label.clone(),
                target_label: ep_id.clone(),
                label: Some("contains".to_string()),
            });
        }
    }

    // 5c: calls_after (sequence edges between endpoints in time order)
    for w in time_ordered_ids.windows(2) {
        let prev_id = &w[0];
        let next_id = &w[1];
        if prev_id != next_id {
            edges.push(ImportEdge {
                source_label: prev_id.clone(),
                target_label: next_id.clone(),
                label: Some("calls_after".to_string()),
            });
        }
    }

    // 5d: data_dependency edges (response values reused in later requests)
    // Collect response candidates per endpoint (use the first response packet seen)
    let mut ep_candidates: HashMap<String, Vec<(String, String)>> = HashMap::new();
    for row in &parsed_rows {
        let identity = endpoint_identity(&row.method, &row.host, &row.path);
        if !ep_candidates.contains_key(&identity) && !row.response_packet.is_empty() {
            let candidates = extract_response_candidates(&row.response_packet);
            if !candidates.is_empty() {
                ep_candidates.insert(identity.clone(), candidates);
            }
        }
    }

    // For each endpoint, check if any earlier endpoint's response candidates appear in this request
    let mut dep_edges_added: HashSet<String> = HashSet::new();
    for (i, curr_id) in time_ordered_ids.iter().enumerate() {
        // Find the rows for this endpoint
        let curr_rows: Vec<&YakitRow> = parsed_rows.iter()
            .filter(|r| endpoint_identity(&r.method, &r.host, &r.path) == *curr_id)
            .collect();
        if curr_rows.is_empty() {
            continue;
        }
        let curr_url = &curr_rows[0].url;
        let curr_req = &curr_rows[0].request_packet;

        for j in 0..i {
            let prev_id = &time_ordered_ids[j];
            if let Some(candidates) = ep_candidates.get(prev_id) {
                let matches = filter_dependency_matches(candidates, curr_url, curr_req);
                for (key, _value) in matches {
                    let edge_label = format!("data_dependency:{key}");
                    let dedup_key = format!("{prev_id}->{curr_id}:{edge_label}");
                    if dep_edges_added.insert(dedup_key) {
                        edges.push(ImportEdge {
                            source_label: prev_id.clone(),
                            target_label: curr_id.clone(),
                            label: Some(edge_label),
                        });
                    }
                }
            }
        }
    }

    let dep_count = dep_edges_added.len();

    // ── Assemble ImportRequest ──
    let mut all_nodes: Vec<ImportNode> = host_nodes.into_values().collect();
    all_nodes.append(&mut bf_nodes);
    all_nodes.append(&mut ep_nodes);

    let total_nodes = all_nodes.len();
    let total_edges = edges.len();
    let bf_count = bf_groups.len();

    let import_request = ImportRequest { nodes: all_nodes, edges };

    // ── Output ──
    let host_list: Vec<&str> = host_ips.keys().map(|s| s.as_str()).collect();

    if json_output {
        if dry_run {
            let summary = serde_json::json!({
                "source": "yakit_excel",
                "mode": "analyze",
                "file": yakit_excel,
                "dry_run": dry_run,
                "total_rows": total_rows,
                "hosts": host_list,
                "business_functions": bf_count,
                "unique_endpoints": unique_endpoints,
                "total_nodes": total_nodes,
                "total_edges": total_edges,
                "dependency_edges": dep_count,
                "import": import_request,
            });
            println!("{}", serde_json::to_string_pretty(&summary)?);
        }
    } else {
        println!("=== Yakit Traffic Analyze ===");
        println!("File:    {yakit_excel}");
        println!("Rows:    {total_rows}");
        println!("Hosts:   {}", host_list.join(", "));
        println!("Business Functions: {bf_count}");
        println!("Endpoints (unique): {unique_endpoints}");
        println!("Nodes:   {total_nodes} (hosts + BFs + endpoints)");
        println!("Edges:   {total_edges} (exposes + contains + calls_after + data_dependency:*)");
        if let Some(hf) = host_filter {
            println!("Filter:  host starts with '{hf}'");
        }
        if dry_run {
            println!("\n[dry-run] Not posting to server.\n");

            // Show preview: hosts, BFs, endpoints, edges
            println!("── Hosts ──");
            for host in &host_list {
                let ep_count = host_endpoint_count.get(*host).copied().unwrap_or(0);
                println!("  {host} ({ep_count} endpoint occurrences)");
            }

            println!("\n── Business Functions ──");
            for (host, bf_key, _, ep_list) in &bf_groups {
                println!("  {bf_key} ({host}) — {ec} endpoints",
                    ec = ep_list.len());
            }

            println!("\n── Endpoints ──");
            for id in &time_ordered_ids {
                let meta = endpoint_meta.get(id).map(|m| m.clone()).unwrap_or((0, vec![], vec![], vec![]));
                let status_str = if meta.2.is_empty() {
                    String::new()
                } else {
                    format!(" [{}]", meta.2.join(", "))
                };
                println!("  {id}{status_str} (×{count})",
                    count = meta.0);
            }

            println!("\n── Sample Edges ──");
            let mut shown = 0usize;
            for e in &import_request.edges {
                if shown >= 20 {
                    println!("  ... (+{} more edges)", total_edges - 20);
                    break;
                }
                let lbl = e.label.as_deref().unwrap_or("-");
                // Truncate long labels for readability
                let lbl_disp = if lbl.len() > 50 {
                    format!("{}…", &lbl[..49])
                } else {
                    lbl.to_string()
                };
                let src: String = e.source_label.chars().take(50).collect();
                let tgt: String = e.target_label.chars().take(50).collect();
                println!("  {src}  ──{lbl_disp}──▶  {tgt}");
                shown += 1;
            }

            if dep_count > 0 {
                println!("\nData dependency edges inferred: {dep_count}");
            }
        }
    }

    if !dry_run {
        let project_id = super::resolve_project_id(client, base, project_id).await?;
        let resp = client
            .post(format!("{base}/api/projects/{project_id}/import"))
            .json(&import_request)
            .send()
            .await?;
        if json_output {
            super::print_json(resp).await?;
        } else {
            eprintln!();
            let body: ApiResponse<ImportResult> = resp.json().await?;
            if body.success {
                if let Some(result) = body.data {
                    eprintln!("Imported: {} nodes created, {} edges created",
                        result.nodes_created, result.edges_created);
                    if result.nodes_skipped > 0 || result.edges_skipped > 0 {
                        eprintln!("Skipped:  {} nodes, {} edges",
                            result.nodes_skipped, result.edges_skipped);
                    }
                    for err in &result.errors {
                        eprintln!("  ! {err}");
                    }
                }
            } else {
                return Err(body.error.unwrap_or_else(|| "import failed".into()).into());
            }
        }
    }

    Ok(())
}

// ── Analyze helpers ──

/// Normalize a path segment: replace numeric IDs, UUIDs, hashes with placeholders.
fn normalize_path_template(path: &str) -> String {
    let path_no_query = path.split('?').next().unwrap_or(path);
    let mut segments: Vec<String> = Vec::new();
    for seg in path_no_query.split('/').filter(|s| !s.is_empty()) {
        segments.push(normalize_path_segment(seg));
    }
    if segments.is_empty() {
        "/".to_string()
    } else {
        format!("/{}", segments.join("/"))
    }
}

fn normalize_path_segment(seg: &str) -> String {
    if let Some((stem, ext)) = split_segment_extension(seg) {
        if is_all_digits(stem) {
            return format!("{{id}}.{ext}");
        }
        if is_uuid_like(stem) {
            return format!("{{uuid}}.{ext}");
        }
        if is_hash_like(stem) {
            return format!("{{token}}.{ext}");
        }
    }

    if is_all_digits(seg) {
        "{id}".to_string()
    } else if is_uuid_like(seg) {
        "{uuid}".to_string()
    } else if is_hash_like(seg) {
        "{token}".to_string()
    } else {
        seg.to_string()
    }
}

fn split_segment_extension(seg: &str) -> Option<(&str, &str)> {
    let (stem, ext) = seg.rsplit_once('.')?;
    if stem.is_empty() || ext.is_empty() || ext.len() > 12 {
        return None;
    }
    if !ext.chars().all(|c| c.is_ascii_alphanumeric()) {
        return None;
    }
    Some((stem, ext))
}

fn is_all_digits(s: &str) -> bool {
    !s.is_empty() && s.chars().all(|c| c.is_ascii_digit())
}

fn is_uuid_like(s: &str) -> bool {
    let hex: String = s.chars().filter(|c| *c != '-').collect();
    hex.len() >= 32 && hex.len() <= 36 && hex.chars().all(|c| c.is_ascii_hexdigit())
}

fn is_hash_like(s: &str) -> bool {
    if s.len() < 8 {
        return false;
    }
    // Pure hex 8+ chars (md5 fragment, etc.)
    if s.chars().all(|c| c.is_ascii_hexdigit()) {
        return true;
    }
    // Mixed alphanumeric 16+ chars (base64 tokens, session IDs, etc.)
    if s.len() >= 16 && s.chars().all(|c| c.is_ascii_alphanumeric() || c == '_' || c == '-') {
        let has_upper = s.chars().any(|c| c.is_ascii_uppercase());
        let has_lower = s.chars().any(|c| c.is_ascii_lowercase());
        let has_digit = s.chars().any(|c| c.is_ascii_digit());
        if (has_upper && has_lower) || (has_lower && has_digit) || (has_upper && has_digit) {
            return true;
        }
    }
    false
}

/// Build endpoint identity: METHOD host://path_template (query stripped, IDs normalized).
fn endpoint_identity(method: &str, host: &str, path: &str) -> String {
    let norm = normalize_path_template(path);
    format!("{method} {host}{norm}")
}

/// Split identity back into (host, normalized_path).
fn split_identity_host_path(identity: &str) -> (String, String) {
    let without_method = identity
        .find(' ')
        .map(|p| &identity[p + 1..])
        .unwrap_or(identity);
    // without_method looks like "host.example.com/path"
    if let Some(slash_pos) = without_method.find('/') {
        let host = without_method[..slash_pos].to_string();
        let path = without_method[slash_pos..].to_string();
        (host, path)
    } else {
        (without_method.to_string(), "/".to_string())
    }
}

/// Find the first path segment that is NOT a placeholder (i.e., contains alphabetic chars).
fn first_meaningful_segment(normalized_path: &str) -> String {
    for seg in normalized_path.split('/').filter(|s| !s.is_empty()) {
        if seg.chars().any(|c| c.is_alphabetic()) {
            return seg.to_string();
        }
    }
    String::new()
}

/// Extract response field names from a response packet (JSON keys, query-like keys).
/// Does NOT store values — only structural field names.
fn extract_response_field_names(response_packet: &str) -> Vec<String> {
    let mut fields = Vec::new();
    // Try JSON
    if let Ok(val) = serde_json::from_str::<serde_json::Value>(response_packet) {
        if let Some(obj) = val.as_object() {
            for k in obj.keys() {
                if !k.is_empty() {
                    fields.push(k.chars().take(60).collect());
                }
            }
            return fields;
        }
        // If it's an array, note it but no individual keys
        if val.as_array().is_some() {
            fields.push("[array]".to_string());
            return fields;
        }
    }
    // Fallback: look for key=value or key:value patterns in text
    for line in response_packet.lines() {
        for part in line.split('&') {
            if let Some((k, _)) = part.split_once('=') {
                let k = k.trim();
                if !k.is_empty() && k.len() < 80 {
                    fields.push(k.to_string());
                }
            }
        }
    }
    fields
}

/// Extract candidate (key, value) pairs from a response packet for dependency tracking.
/// Values are truncated to avoid storing secrets at full length.
fn extract_response_candidates(response_packet: &str) -> Vec<(String, String)> {
    let mut candidates = Vec::new();
    // Try JSON
    if let Ok(val) = serde_json::from_str::<serde_json::Value>(response_packet) {
        if let Some(obj) = val.as_object() {
            for (k, v) in obj {
                if let Some(s) = v.as_str() {
                    if s.len() >= 3 && !s.contains(char::is_whitespace) {
                        candidates.push((k.clone(), truncate_text(s, 200)));
                    }
                }
            }
        }
        return candidates;
    }
    // Fallback: key=value patterns
    for line in response_packet.lines() {
        for part in line.split('&') {
            if let Some((k, v)) = part.split_once('=') {
                let k = k.trim();
                let v = v.trim();
                if !k.is_empty() && v.len() >= 3 && !v.contains(char::is_whitespace) {
                    candidates.push((k.to_string(), truncate_text(v, 200)));
                }
            }
        }
    }
    candidates
}

/// Find which candidate values appear in the target URL or request body.
fn filter_dependency_matches(
    candidates: &[(String, String)],
    target_url: &str,
    target_request: &str,
) -> Vec<(String, String)> {
    let mut matches = Vec::new();
    for (key, value) in candidates {
        if value.len() < 3 {
            continue;
        }
        if target_url.contains(value) || target_request.contains(value) {
            matches.push((key.clone(), value.clone()));
        }
    }
    matches
}

fn build_analyze_endpoint_content(
    display_label: &str,
    host: &str,
    count: usize,
    methods: &[String],
    statuses: &[String],
    resp_fields: &[String],
) -> String {
    let mut parts = Vec::new();
    parts.push(format!("# {display_label}\n"));
    parts.push(format!("**Host**: {host}"));
    parts.push(format!("**Observed**: {count} time(s)"));
    if !methods.is_empty() {
        parts.push(format!("**Methods**: {}", methods.join(", ")));
    }
    if !statuses.is_empty() {
        parts.push(format!("**Status codes**: {}", statuses.join(", ")));
    }
    if !resp_fields.is_empty() {
        let field_list: Vec<String> = resp_fields.iter().take(20).cloned().collect();
        let suffix = if resp_fields.len() > 20 {
            format!(" (+{} more)", resp_fields.len() - 20)
        } else {
            String::new()
        };
        parts.push(format!("**Response fields**: {}{suffix}", field_list.join(", ")));
    }
    parts.push(String::new());
    parts.push("## What an Agent should inspect next".to_string());
    parts.push(String::new());
    parts.push("- **Parameter injection**: Test all query/body parameters for SQL injection, XSS, SSRF, and command injection.".to_string());
    parts.push("- **Authorization bypass**: Attempt to access this endpoint without authentication or with lower-privilege credentials.".to_string());
    parts.push("- **IDOR**: If path segments contain resource IDs, test for insecure direct object references.".to_string());
    parts.push("- **Response analysis**: Review response data for sensitive information (PII, tokens, internal IPs, stack traces).".to_string());
    parts.push("- **Rate limiting**: Test for brute-force susceptibility and denial-of-service conditions.".to_string());
    parts.push("- **Data flow**: Trace whether data from this endpoint feeds into later requests (check data_dependency edges).".to_string());
    parts.join("\n")
}

// ── Stage 1 semantic enrichment helpers ──

/// Extract HTTP message body — strips request/status line + headers.
fn extract_http_body(packet: &str) -> Option<String> {
    if let Some(pos) = packet.find("\r\n\r\n") {
        let body = packet[pos + 4..].trim().to_string();
        if !body.is_empty() { return Some(body); }
    }
    if let Some(pos) = packet.find("\n\n") {
        let body = packet[pos + 2..].trim().to_string();
        if !body.is_empty() { return Some(body); }
    }
    // No header/body separation — try as raw JSON
    let trimmed = packet.trim();
    if !trimmed.is_empty() && (trimmed.starts_with('{') || trimmed.starts_with('[')) {
        Some(trimmed.to_string())
    } else {
        None
    }
}

/// Extract JSON schema shape: keys mapped to primitive type names.
/// Objects and arrays are expanded recursively (max depth 3). No values stored.
fn extract_schema_shape(body: &str) -> serde_json::Value {
    match serde_json::from_str::<serde_json::Value>(body) {
        Ok(val) => infer_json_shape(&val, 0),
        Err(_) => serde_json::Value::Null,
    }
}

fn infer_json_shape(value: &serde_json::Value, depth: usize) -> serde_json::Value {
    if depth > 3 { return serde_json::Value::String("...".into()); }
    match value {
        serde_json::Value::Null => serde_json::Value::String("null".into()),
        serde_json::Value::Bool(_) => serde_json::Value::String("boolean".into()),
        serde_json::Value::Number(n) => {
            if n.is_i64() || n.is_u64() { serde_json::Value::String("integer".into()) }
            else { serde_json::Value::String("number".into()) }
        }
        serde_json::Value::String(_) => serde_json::Value::String("string".into()),
        serde_json::Value::Array(arr) => {
            let items = arr.first().map(|v| infer_json_shape(v, depth + 1));
            serde_json::json!({"type": "array", "items": items})
        }
        serde_json::Value::Object(obj) => {
            let mut props = serde_json::Map::new();
            let mut keys: Vec<&String> = obj.keys().collect();
            keys.sort();
            for k in keys {
                props.insert(k.clone(), infer_json_shape(&obj[k], depth + 1));
            }
            serde_json::json!({"type": "object", "properties": serde_json::Value::Object(props)})
        }
    }
}

/// Build sorted status profile from per-endpoint status code counts.
fn build_status_profile(counts: Option<&HashMap<String, usize>>) -> serde_json::Value {
    let counts = match counts {
        Some(c) => c,
        None => return serde_json::json!({"by_code": {}, "by_family": {}, "total_observations": 0}),
    };
    let mut by_code: Vec<(String, usize)> = counts.iter().map(|(k, v)| (k.clone(), *v)).collect();
    by_code.sort_by(|a, b| a.0.cmp(&b.0));

    let mut families: Vec<(&str, usize)> = vec![("2xx", 0), ("3xx", 0), ("4xx", 0), ("5xx", 0)];
    for (code, cnt) in &by_code {
        let idx = match code.chars().next() {
            Some('2') => 0, Some('3') => 1, Some('4') => 2, Some('5') => 3, _ => continue,
        };
        families[idx].1 += cnt;
    }

    let by_code_map: serde_json::Map<String, serde_json::Value> =
        by_code.into_iter().map(|(k, v)| (k, serde_json::Value::Number(v.into()))).collect();
    let by_family_map: serde_json::Map<String, serde_json::Value> =
        families.into_iter().map(|(fam, v)| (fam.into(), serde_json::Value::Number(v.into()))).collect();
    let total: usize = counts.values().sum();

    let mut m = serde_json::Map::new();
    m.insert("by_code".into(), serde_json::Value::Object(by_code_map));
    m.insert("by_family".into(), serde_json::Value::Object(by_family_map));
    m.insert("total_observations".into(), serde_json::Value::Number(total.into()));
    serde_json::Value::Object(m)
}

/// Extract parameter inventory from URL query string, path template placeholders,
/// and optional JSON request body keys. Stores names, locations, inferred kinds, count.
fn extract_parameter_inventory(
    display_label: &str,
    norm_path: &str,
    req_body: Option<&str>,
) -> serde_json::Value {
    use std::collections::BTreeMap;

    struct ParamEntry { location: String, inferred_kind: String, count: usize }
    let mut params: BTreeMap<String, ParamEntry> = BTreeMap::new();

    // From URL query string
    if let Some(query_start) = display_label.find('?') {
        let query = &display_label[query_start + 1..];
        let query = query.split('#').next().unwrap_or(query);
        for part in query.split('&') {
            let (name, value) = part.split_once('=').unwrap_or((part, ""));
            if name.is_empty() { continue; }
            let kind = infer_value_kind(value);
            let entry = params.entry(name.to_string()).or_insert_with(|| ParamEntry {
                location: "query".into(), inferred_kind: kind.clone(), count: 0,
            });
            entry.count += 1;
            if entry.inferred_kind != kind && matches!(kind.as_str(), "uuid" | "jwt" | "hex") {
                entry.inferred_kind = kind;
            }
        }
    }

    // From path template placeholders
    for seg in norm_path.split('/').filter(|s| !s.is_empty()) {
        if let Some(pi) = extract_placeholder_info(seg) {
            let entry = params.entry(pi.name.clone()).or_insert_with(|| ParamEntry {
                location: "path".into(), inferred_kind: pi.kind.clone(), count: 0,
            });
            entry.count += 1;
        }
    }

    // From JSON request body top-level keys
    if let Some(body) = req_body {
        if let Ok(val) = serde_json::from_str::<serde_json::Value>(body) {
            if let Some(obj) = val.as_object() {
                for k in obj.keys() {
                    params.entry(k.clone()).or_insert_with(|| ParamEntry {
                        location: "body".into(), inferred_kind: "string".into(), count: 1,
                    });
                }
            }
        }
    }

    let result: Vec<serde_json::Value> = params.into_iter().map(|(name, pe)| {
        serde_json::json!({
            "name": name, "location": pe.location,
            "inferred_kind": pe.inferred_kind, "occurrence_count": pe.count,
        })
    }).collect();
    serde_json::Value::Array(result)
}

struct PlaceholderInfo { name: String, kind: String }

fn extract_placeholder_info(segment: &str) -> Option<PlaceholderInfo> {
    if !segment.starts_with('{') { return None; }
    let close = segment.find('}')?;
    let placeholder = &segment[..=close];
    let suffix = &segment[close + 1..];
    let name = if suffix.is_empty() {
        placeholder.to_string()
    } else if suffix.starts_with('.') && suffix[1..].chars().all(|c| c.is_ascii_alphanumeric()) {
        format!("{placeholder}{suffix}")
    } else {
        return None;
    };
    let kind = match placeholder {
        "{id}" => "numeric_id".into(),
        "{uuid}" => "uuid".into(),
        "{token}" => "token".into(),
        _ => "dynamic_segment".into(),
    };
    Some(PlaceholderInfo { name, kind })
}

fn infer_value_kind(value: &str) -> String {
    if value.is_empty() { return "empty".into(); }
    if value.chars().all(|c| c.is_ascii_digit()) { return "integer".into(); }
    let hex: String = value.chars().filter(|c| *c != '-').collect();
    if hex.len() >= 32 && hex.len() <= 36 && hex.chars().all(|c| c.is_ascii_hexdigit()) {
        return "uuid".into();
    }
    if value.len() >= 16 && value.chars().all(|c| c.is_ascii_hexdigit()) {
        return "hex".into();
    }
    if value.matches('.').count() == 2 {
        let parts: Vec<&str> = value.split('.').collect();
        if parts.iter().all(|p| p.chars().all(|c| c.is_ascii_alphanumeric() || c == '-' || c == '_'))
            && parts.iter().all(|p| !p.is_empty())
        {
            return "jwt".into();
        }
    }
    "string".into()
}

/// Build per-aspect confidence scores with deterministic rationale.
fn assess_endpoint_confidence(
    observation_count: usize,
    unique_status_count: usize,
    has_req_schema: bool,
    has_resp_schema: bool,
) -> serde_json::Value {
    fn level(n: usize) -> &'static str {
        if n > 5 { "high" } else if n > 1 { "medium" } else { "low" }
    }
    serde_json::json!({
        "path_template": {
            "confidence": level(observation_count),
            "rationale": "Path segments matching numeric IDs, UUIDs, or hash-like tokens are replaced with {id}/{uuid}/{token} placeholders to group identical semantic endpoints.",
        },
        "parameters": {
            "confidence": level(observation_count),
            "rationale": "Parameter names extracted from URL query strings and path template placeholders. Inferred types come from value character classes (integer, uuid, hex, jwt) — no sample values are stored.",
        },
        "request_schema": {
            "confidence": if has_req_schema { level(observation_count) } else { "not_applicable" },
            "rationale": if has_req_schema {
                "JSON request body schema extracted: property keys mapped to type names (string/integer/boolean/null/array/object). Nested objects recurse to depth 3. All actual values are discarded."
            } else {
                "No JSON request body detected — body may be urlencoded, multipart, or absent."
            },
        },
        "response_schema": {
            "confidence": if has_resp_schema { level(observation_count) } else { "not_applicable" },
            "rationale": if has_resp_schema {
                "JSON response body schema extracted: property keys mapped to type names. Nested structures summarized at depth 3. No response values retained."
            } else {
                "No JSON response body detected — response may be HTML, binary, or text."
            },
        },
        "status_profile": {
            "confidence": level(observation_count),
            "rationale": format!("Status code distribution aggregated from {} observation(s) with {} unique status code(s). Family aggregates (2xx/3xx/4xx/5xx) computed from individual code counts.", observation_count, unique_status_count),
        },
    })
}

/// Build normalization notes explaining what deterministic inferences were applied.
fn build_normalization_notes(norm_path: &str, raw_path: &str) -> serde_json::Value {
    let mut notes: Vec<String> = Vec::new();

    if norm_path != raw_path && raw_path != norm_path.replace('{', "") {
        notes.push(format!(
            "Path normalized to '{}'. Numeric IDs, UUIDs, and hash-like segments were replaced with typed placeholders for endpoint deduplication. Raw path values are intentionally omitted from this note.",
            norm_path
        ));
    } else {
        notes.push(format!(
            "Path: '{}' — no placeholder normalization was necessary for this endpoint.",
            norm_path
        ));
    }

    notes.push("Request/response schemas are extracted solely from JSON bodies. Non-JSON content (HTML, urlencoded, multipart, binary) yields a null schema. All property values are replaced with inferred type names — zero raw data stored.".into());
    notes.push("Parameter inventory captures names, locations (query/path/body), inferred kinds, and occurrence counts. No actual parameter values are stored — only structural metadata.".into());
    notes.push("Status profiles aggregate HTTP response codes by code and by family (2xx, 3xx, 4xx, 5xx). Counts are derived from all observed traffic rows for this endpoint.".into());
    notes.push("All enrichment inference is deterministic: path normalization uses pattern matching on segment character classes; JSON schema uses serde_json parse + recursive type mapping; parameter types are inferred from value character classes. No AI/LLM involved.".into());

    serde_json::Value::Array(notes.into_iter().map(|n| serde_json::Value::String(n)).collect())
}

fn build_analyze_endpoint_props(
    host: &str,
    norm_path: &str,
    count: usize,
    methods: &[String],
    statuses: &[String],
    resp_fields: &[String],
    display_label: &str,
    status_counts: Option<&HashMap<String, usize>>,
    raw_path: &str,
    req_body_sample: Option<&str>,
    res_body_sample: Option<&str>,
) -> serde_json::Value {
    let mut m = serde_json::Map::new();
    m.insert("host".into(), host.into());
    m.insert("path_template".into(), norm_path.into());
    m.insert("display_url".into(), display_label.into());
    m.insert("observation_count".into(), serde_json::Value::Number(count.into()));
    if !methods.is_empty() {
        let mut sorted: Vec<&String> = methods.iter().collect();
        sorted.sort();
        m.insert("methods".into(), sorted.iter().map(|s| s.as_str()).collect::<Vec<_>>().join(", ").into());
    }
    if !statuses.is_empty() {
        let mut sorted: Vec<&String> = statuses.iter().collect();
        sorted.sort();
        m.insert("status_codes".into(), sorted.iter().map(|s| s.as_str()).collect::<Vec<_>>().join(", ").into());
    }
    if !resp_fields.is_empty() {
        let mut sorted: Vec<&String> = resp_fields.iter().collect();
        sorted.sort();
        let field_list: Vec<serde_json::Value> = sorted.iter().take(50).map(|f| serde_json::Value::String((*f).clone())).collect();
        m.insert("response_field_names".into(), serde_json::Value::Array(field_list));
    }

    // Stage 1 semantic enrichment
    let status_profile = build_status_profile(status_counts);
    let request_schema = req_body_sample.map_or(serde_json::Value::Null, |s| extract_schema_shape(s));
    let response_schema = res_body_sample.map_or(serde_json::Value::Null, |s| extract_schema_shape(s));
    let parameters = extract_parameter_inventory(display_label, norm_path, req_body_sample);
    let unique_status_count = status_counts.map(|s| s.len()).unwrap_or(0);
    let confidence = assess_endpoint_confidence(count, unique_status_count, !request_schema.is_null(), !response_schema.is_null());
    let normalization_notes = build_normalization_notes(norm_path, raw_path);

    m.insert("status_profiles".into(), status_profile);
    m.insert("request_schema".into(), request_schema);
    m.insert("response_schema".into(), response_schema);
    m.insert("parameters".into(), parameters);
    m.insert("confidence".into(), confidence);
    m.insert("normalization_notes".into(), normalization_notes);

    serde_json::Value::Object(m)
}

fn build_bf_content(bf_key: &str, host: &str, ep_count: usize) -> String {
    let mut parts = Vec::new();
    parts.push(format!("# Business Function: {bf_key}\n"));
    parts.push(format!("**Host**: {host}"));
    parts.push(format!("**Endpoints in group**: {ep_count}"));
    parts.push(String::new());
    parts.push("## Why this group exists".to_string());
    parts.push(String::new());
    let display_prefix = bf_key
        .strip_prefix(host)
        .and_then(|rest| rest.strip_prefix('/'))
        .filter(|rest| !rest.is_empty())
        .map(|rest| format!("/{rest}"))
        .unwrap_or_else(|| "/".to_string());
    parts.push(format!(
        "This group represents endpoints under `{display_prefix}` on host `{host}`. \
        These endpoints share a common path prefix, suggesting they belong to the same \
        logical business function (e.g., user management, order processing, admin panel)."
    ));
    parts.push(String::new());
    parts.push("## What an Agent should inspect next".to_string());
    parts.push(String::new());
    parts.push("- **Authentication**: Is this entire function group protected? Are there unauthenticated entry points?".to_string());
    parts.push("- **Authorization model**: Check for role-based access — can a regular user access admin endpoints?".to_string());
    parts.push("- **Privilege escalation**: Map the privilege levels across endpoints. Can low-privilege responses be replayed to high-privilege endpoints?".to_string());
    parts.push("- **Cross-endpoint attacks**: Look for parameter pollution, IDOR across related endpoints, or data leakage between endpoints.".to_string());
    parts.push("- **Data flow**: Trace `data_dependency` edges to see how data moves between business functions.".to_string());
    parts.join("\n")
}

fn get_cell(row: &[calamine::Data], col: &HashMap<String, usize>, key: &str) -> String {
    col.get(key)
        .and_then(|&i| row.get(i))
        .map(data_to_str)
        .unwrap_or_default()
        .trim()
        .to_string()
}

fn data_to_str(d: &calamine::Data) -> String {
    match d {
        calamine::Data::String(s) => s.clone(),
        calamine::Data::Float(f) => f.to_string(),
        calamine::Data::Int(i) => i.to_string(),
        calamine::Data::Bool(b) => b.to_string(),
        calamine::Data::DateTime(f) => format!("{f}"),
        calamine::Data::DateTimeIso(s) => s.clone(),
        calamine::Data::DurationIso(s) => s.clone(),
        calamine::Data::Empty => String::new(),
        calamine::Data::Error(_) => String::new(),
    }
}

fn build_endpoint_content(row: &YakitRow) -> String {
    let mut parts: Vec<String> = Vec::new();
    parts.push(format!("URL: {}", row.url));
    parts.push(format!("Host: {}", row.host));
    parts.push(format!("Method: {}", row.method));
    parts.push(format!("Path: {}", row.path));
    if !row.status_code.is_empty() {
        parts.push(format!("Status: {}", row.status_code));
    }
    if !row.ip.is_empty() {
        parts.push(format!("IP: {}", row.ip));
    }
    if !row.response_length.is_empty() {
        parts.push(format!("Response Length: {}", row.response_length));
    }
    if !row.title.is_empty() {
        parts.push(format!("Title: {}", row.title));
    }
    if !row.response_type.is_empty() {
        parts.push(format!("Response Type: {}", row.response_type));
    }
    if !row.latency_ms.is_empty() {
        parts.push(format!("Latency: {}ms", row.latency_ms));
    }
    if !row.request_time.is_empty() {
        parts.push(format!("Time: {}", row.request_time));
    }
    if !row.request_size.is_empty() {
        parts.push(format!("Request Size: {}", row.request_size));
    }
    parts.join("\n")
}

fn build_endpoint_props(row: &YakitRow) -> serde_json::Value {
    let mut m = serde_json::Map::new();
    if !row.method.is_empty() { m.insert("method".into(), row.method.clone().into()); }
    if !row.status_code.is_empty() { m.insert("status_code".into(), row.status_code.clone().into()); }
    if !row.ip.is_empty() { m.insert("ip".into(), row.ip.clone().into()); }
    if !row.response_length.is_empty() { m.insert("response_length".into(), row.response_length.clone().into()); }
    if !row.title.is_empty() { m.insert("title".into(), row.title.clone().into()); }
    if !row.response_type.is_empty() { m.insert("response_type".into(), row.response_type.clone().into()); }
    if !row.extension.is_empty() { m.insert("extension".into(), row.extension.clone().into()); }
    if !row.latency_ms.is_empty() { m.insert("latency_ms".into(), row.latency_ms.clone().into()); }
    if !row.request_time.is_empty() { m.insert("request_time".into(), row.request_time.clone().into()); }
    if !row.request_size.is_empty() { m.insert("request_size".into(), row.request_size.clone().into()); }
    if !row.params_count.is_empty() { m.insert("params_count".into(), row.params_count.clone().into()); }
    if !row.request_packet.is_empty() { m.insert("request_packet".into(), truncate_text(&row.request_packet, 4096).into()); }
    if !row.response_packet.is_empty() { m.insert("response_packet".into(), truncate_text(&row.response_packet, 4096).into()); }
    serde_json::Value::Object(m)
}

fn truncate_text(value: &str, max_chars: usize) -> String {
    let mut truncated: String = value.chars().take(max_chars).collect();
    if value.chars().count() > max_chars {
        truncated.push('…');
        truncated.push_str("[truncated]");
    }
    truncated
}

struct YakitRow {
    method: String,
    status_code: String,
    url: String,
    host: String,
    path: String,
    ip: String,
    response_length: String,
    title: String,
    response_type: String,
    extension: String,
    latency_ms: String,
    request_time: String,
    request_size: String,
    request_packet: String,
    response_packet: String,
    params_count: String,
}

fn parse_yakit_rows(
    yakit_excel: &str,
    host_filter: Option<&str>,
) -> Result<Vec<YakitRow>, BoxErr> {
    use calamine::{open_workbook_auto, Reader};

    let mut workbook = open_workbook_auto(yakit_excel)
        .map_err(|e| format!("failed to open XLSX: {e}"))?;
    let sheets = workbook.sheet_names();
    let sheet_name = sheets.first().cloned().ok_or("XLSX has no sheets")?;
    let range = workbook
        .worksheet_range(&sheet_name)
        .map_err(|e| format!("failed to read sheet '{sheet_name}': {e}"))?;

    let mut rows_iter = range.rows();

    // Parse header row
    let header_row = rows_iter.next().ok_or("XLSX has no rows (missing header)")?;
    let col = build_column_map(header_row)?;

    fn build_column_map(row: &[calamine::Data]) -> Result<HashMap<String, usize>, BoxErr> {
        let mut map = HashMap::new();
        for (i, cell) in row.iter().enumerate() {
            let s = data_to_str(cell).trim().to_string();
            if !s.is_empty() {
                map.insert(s, i);
            }
        }
        Ok(map)
    }

    let required_headers = [
        "方法", "URL", "Host", "Path", "IP", "状态码", "响应长度", "Title", "响应类型",
        "扩展名", "延迟（ms）", "请求时间", "请求大小", "请求包", "响应包", "参数",
    ];
    let mut missing = Vec::new();
    for &h in &required_headers {
        if !col.contains_key(h) {
            missing.push(h);
        }
    }
    if !missing.is_empty() {
        return Err(format!("missing Yakit headers: {}", missing.join(", ")).into());
    }

    // Collect rows
    let mut parsed_rows: Vec<YakitRow> = Vec::new();
    for row in rows_iter {
        if row.len() < col.len() {
            continue;
        }
        let method = get_cell(row, &col, "方法");
        let url = get_cell(row, &col, "URL");
        let host_val = get_cell(row, &col, "Host");
        if method.is_empty() || url.is_empty() || host_val.is_empty() {
            continue;
        }
        if let Some(hf) = host_filter {
            if !host_val.starts_with(hf) {
                continue;
            }
        }
        let path = get_cell(row, &col, "Path");
        parsed_rows.push(YakitRow {
            method,
            status_code: get_cell(row, &col, "状态码"),
            url,
            host: host_val,
            path,
            ip: get_cell(row, &col, "IP"),
            response_length: get_cell(row, &col, "响应长度"),
            title: get_cell(row, &col, "Title"),
            response_type: get_cell(row, &col, "响应类型"),
            extension: get_cell(row, &col, "扩展名"),
            latency_ms: get_cell(row, &col, "延迟（ms）"),
            request_time: get_cell(row, &col, "请求时间"),
            request_size: get_cell(row, &col, "请求大小"),
            request_packet: get_cell(row, &col, "请求包"),
            response_packet: get_cell(row, &col, "响应包"),
            params_count: get_cell(row, &col, "参数"),
        });
    }

    Ok(parsed_rows)
}
