//! Tests for CSV export functionality.

use domain_status::export::{export_csv, ExportFormat, ExportOptions};
use sqlx::SqlitePool;
use tempfile::TempDir;

#[path = "helpers.rs"]
mod helpers;

use helpers::{create_test_pool_with_path, create_test_run, create_test_url_status};

/// Creates test data: URL with technologies, `GeoIP`, WHOIS, etc.
async fn create_test_url_with_enrichment(
    pool: &SqlitePool,
    domain: &str,
    run_id: Option<&str>,
) -> i64 {
    let url_id = create_test_url_status(pool, domain, domain, 200, run_id, 1704067200000).await;

    // Add technologies
    sqlx::query("INSERT INTO url_technologies (url_status_id, technology_name) VALUES (?, ?)")
        .bind(url_id)
        .bind("nginx")
        .execute(pool)
        .await
        .expect("Failed to insert technology");
    sqlx::query("INSERT INTO url_technologies (url_status_id, technology_name) VALUES (?, ?)")
        .bind(url_id)
        .bind("PHP")
        .execute(pool)
        .await
        .expect("Failed to insert technology");

    // Add redirect chain
    sqlx::query(
        "INSERT INTO url_redirect_chain (url_status_id, sequence_order, redirect_url) VALUES (?, ?, ?)",
    )
    .bind(url_id)
    .bind(0)
    .bind(format!("https://{}", domain))
    .execute(pool)
    .await
    .expect("Failed to insert redirect");

    // Add GeoIP
    sqlx::query(
        "INSERT INTO url_geoip (
            url_status_id, country_code, country_name, city, latitude, longitude, asn, asn_org
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
    )
    .bind(url_id)
    .bind("US")
    .bind("United States")
    .bind("San Francisco")
    .bind(37.7749)
    .bind(-122.4194)
    .bind(15169)
    .bind("GOOGLE")
    .execute(pool)
    .await
    .expect("Failed to insert GeoIP");

    // Add WHOIS
    sqlx::query(
        "INSERT INTO url_whois (
            url_status_id, registrar, creation_date_ms, expiration_date_ms, registrant_country
        ) VALUES (?, ?, ?, ?, ?)",
    )
    .bind(url_id)
    .bind("Test Registrar")
    .bind(1609459200000i64) // 2021-01-01 in ms
    .bind(1735689600000i64) // 2025-01-01 in ms
    .bind("US")
    .execute(pool)
    .await
    .expect("Failed to insert WHOIS");

    // Add analytics IDs
    sqlx::query(
        "INSERT INTO url_analytics_ids (url_status_id, provider, tracking_id) VALUES (?, ?, ?)",
    )
    .bind(url_id)
    .bind("Google Analytics")
    .bind("UA-123456-1")
    .execute(pool)
    .await
    .expect("Failed to insert analytics ID");

    // Add social media links
    sqlx::query(
        "INSERT INTO url_social_media_links (url_status_id, platform, profile_url) VALUES (?, ?, ?)",
    )
    .bind(url_id)
    .bind("LinkedIn")
    .bind("https://linkedin.com/company/test")
    .execute(pool)
    .await
    .expect("Failed to insert social media link");

    // Add security warnings
    sqlx::query(
        "INSERT INTO url_security_warnings (url_status_id, warning_code, warning_description) VALUES (?, ?, ?)",
    )
    .bind(url_id)
    .bind("missing_csp")
    .bind("Content-Security-Policy header is missing")
    .execute(pool)
    .await
        .expect("Failed to insert security warning");

    // Add certificate SANs
    sqlx::query("INSERT INTO url_certificate_sans (url_status_id, san_value) VALUES (?, ?)")
        .bind(url_id)
        .bind("example.com")
        .execute(pool)
        .await
        .expect("Failed to insert certificate SAN");

    // Add OIDs
    sqlx::query("INSERT INTO url_certificate_oids (url_status_id, oid) VALUES (?, ?)")
        .bind(url_id)
        .bind("1.3.6.1.4.1.11129.2.4.2")
        .execute(pool)
        .await
        .expect("Failed to insert OID");

    url_id
}

#[tokio::test]
async fn test_export_csv_basic() {
    let temp_dir = TempDir::new().expect("Failed to create temp directory");
    let db_path = temp_dir.path().join("test_export.db");
    let output_path = temp_dir.path().join("output.csv");

    // Create test database with migrations
    // Ensure parent directory exists
    if let Some(parent) = db_path.parent() {
        std::fs::create_dir_all(parent).expect("Failed to create parent directory");
    }
    // Create test database with migrations
    let pool = create_test_pool_with_path(&db_path).await;

    // Create test run first (required for foreign key)
    create_test_run(&pool, "test_run_1", 1704067200000).await;

    // Create test data
    create_test_url_with_enrichment(&pool, "example.com", Some("test_run_1")).await;
    create_test_url_status(
        &pool,
        "test.com",
        "test.com",
        200,
        Some("test_run_1"),
        1704067200000,
    )
    .await;

    drop(pool); // Close connection before export

    // Export CSV
    let count = export_csv(&ExportOptions {
        db_path: db_path.clone(),
        output: Some(output_path.clone()),
        format: ExportFormat::Csv,
        run_id: None,
        domain: None,
        status: None,
        since: None,
    })
    .await
    .expect("Export should succeed");

    assert_eq!(count, 2, "Should export 2 records");

    // Verify CSV file exists and has content
    let csv_content = std::fs::read_to_string(&output_path).expect("Should read CSV file");
    let lines: Vec<&str> = csv_content.lines().collect();
    assert_eq!(lines.len(), 3, "Should have header + 2 data rows");

    // Verify header
    assert!(
        lines[0].contains("url") && lines[0].contains("technologies"),
        "Header should contain expected columns"
    );

    // Verify data rows contain expected data
    assert!(
        lines[1].contains("example.com") || lines[2].contains("example.com"),
        "CSV should contain example.com"
    );
    assert!(
        lines[1].contains("test.com") || lines[2].contains("test.com"),
        "CSV should contain test.com"
    );
}

#[tokio::test]
async fn test_export_csv_filter_by_run_id() {
    let temp_dir = TempDir::new().expect("Failed to create temp directory");
    let db_path = temp_dir.path().join("test_export.db");
    let output_path = temp_dir.path().join("output.csv");

    // Create test database with migrations
    let pool = create_test_pool_with_path(&db_path).await;

    // Create runs first (required for foreign key)
    create_test_run(&pool, "run_1", 1704067200000).await;
    create_test_run(&pool, "run_2", 1704067200000).await;

    // Create data with different run_ids
    create_test_url_status(
        &pool,
        "test1.com",
        "test1.com",
        200,
        Some("run_1"),
        1704067200000,
    )
    .await;
    create_test_url_status(
        &pool,
        "test2.com",
        "test2.com",
        200,
        Some("run_2"),
        1704067200000,
    )
    .await;

    drop(pool);

    // Export only run_1
    let count = export_csv(&ExportOptions {
        db_path: db_path.clone(),
        output: Some(output_path.clone()),
        format: ExportFormat::Csv,
        run_id: Some("run_1".to_string()),
        domain: None,
        status: None,
        since: None,
    })
    .await
    .expect("Export should succeed");

    assert_eq!(count, 1, "Should export only 1 record for run_1");

    let csv_content = std::fs::read_to_string(&output_path).expect("Should read CSV file");
    assert!(
        csv_content.contains("test1.com"),
        "CSV should contain test1.com"
    );
    assert!(
        !csv_content.contains("test2.com"),
        "CSV should not contain test2.com"
    );
}

#[tokio::test]
async fn test_export_csv_filter_by_domain() {
    let temp_dir = TempDir::new().expect("Failed to create temp directory");
    let db_path = temp_dir.path().join("test_export.db");
    let output_path = temp_dir.path().join("output.csv");

    // Create test database with migrations
    let pool = create_test_pool_with_path(&db_path).await;

    create_test_url_status(
        &pool,
        "example.com",
        "example.com",
        200,
        None,
        1704067200000,
    )
    .await;
    create_test_url_status(&pool, "test.com", "test.com", 200, None, 1704067200000).await;

    drop(pool);

    // Filter by domain
    let count = export_csv(&ExportOptions {
        db_path: db_path.clone(),
        output: Some(output_path.clone()),
        format: ExportFormat::Csv,
        run_id: None,
        domain: Some("example.com".to_string()),
        status: None,
        since: None,
    })
    .await
    .expect("Export should succeed");

    assert_eq!(count, 1, "Should export only 1 record for example.com");

    let csv_content = std::fs::read_to_string(&output_path).expect("Should read CSV file");
    assert!(
        csv_content.contains("example.com"),
        "CSV should contain example.com"
    );
    assert!(
        !csv_content.contains("test.com"),
        "CSV should not contain test.com"
    );
}

#[tokio::test]
async fn test_export_csv_filter_by_status() {
    let temp_dir = TempDir::new().expect("Failed to create temp directory");
    let db_path = temp_dir.path().join("test_export.db");
    let output_path = temp_dir.path().join("output.csv");

    // Create test database with migrations
    let pool = create_test_pool_with_path(&db_path).await;

    create_test_url_status(&pool, "ok.com", "ok.com", 200, None, 1704067200000).await;
    create_test_url_status(&pool, "error.com", "error.com", 404, None, 1704067200000).await;

    drop(pool);

    // Filter by status 200
    let count = export_csv(&ExportOptions {
        db_path: db_path.clone(),
        output: Some(output_path.clone()),
        format: ExportFormat::Csv,
        run_id: None,
        domain: None,
        status: Some(200),
        since: None,
    })
    .await
    .expect("Export should succeed");

    assert_eq!(count, 1, "Should export only 1 record with status 200");

    let csv_content = std::fs::read_to_string(&output_path).expect("Should read CSV file");
    assert!(csv_content.contains("ok.com"), "CSV should contain ok.com");
    assert!(
        !csv_content.contains("error.com"),
        "CSV should not contain error.com"
    );
}

#[tokio::test]
async fn test_export_csv_empty_database() {
    let temp_dir = TempDir::new().expect("Failed to create temp directory");
    let db_path = temp_dir.path().join("test_export.db");
    let output_path = temp_dir.path().join("output.csv");

    // Create empty database
    // Create test database with migrations
    let pool = create_test_pool_with_path(&db_path).await;
    drop(pool);

    // Export from empty database
    let count = export_csv(&ExportOptions {
        db_path: db_path.clone(),
        output: Some(output_path.clone()),
        format: ExportFormat::Csv,
        run_id: None,
        domain: None,
        status: None,
        since: None,
    })
    .await
    .expect("Export should succeed even with empty database");

    assert_eq!(count, 0, "Should export 0 records from empty database");

    // Verify CSV has only header
    let csv_content = std::fs::read_to_string(&output_path).expect("Should read CSV file");
    let lines: Vec<&str> = csv_content.lines().collect();
    assert_eq!(lines.len(), 1, "Should have only header row");
    assert!(lines[0].contains("url"), "Header should be present");
}

#[tokio::test]
async fn test_export_csv_missing_relationships() {
    let temp_dir = TempDir::new().expect("Failed to create temp directory");
    let db_path = temp_dir.path().join("test_export.db");
    let output_path = temp_dir.path().join("output.csv");

    // Create test database with migrations
    let pool = create_test_pool_with_path(&db_path).await;

    // Create URL with NO enrichment data (no GeoIP, no WHOIS, no technologies)
    create_test_url_status(&pool, "bare.com", "bare.com", 200, None, 1704067200000).await;

    drop(pool);

    // Export should handle missing relationships gracefully
    let count = export_csv(&ExportOptions {
        db_path: db_path.clone(),
        output: Some(output_path.clone()),
        format: ExportFormat::Csv,
        run_id: None,
        domain: None,
        status: None,
        since: None,
    })
    .await
    .expect("Export should succeed even with missing relationships");

    assert_eq!(count, 1, "Should export 1 record");

    let csv_content = std::fs::read_to_string(&output_path).expect("Should read CSV file");
    // Should have empty values for missing data, not crash
    assert!(
        csv_content.contains("bare.com"),
        "CSV should contain bare.com"
    );
}

#[tokio::test]
async fn test_export_csv_all_enrichment_data() {
    let temp_dir = TempDir::new().expect("Failed to create temp directory");
    let db_path = temp_dir.path().join("test_export.db");
    let output_path = temp_dir.path().join("output.csv");

    // Create test database with migrations
    let pool = create_test_pool_with_path(&db_path).await;

    // Create URL with all enrichment data
    create_test_url_with_enrichment(&pool, "full.com", None).await;

    drop(pool);

    let count = export_csv(&ExportOptions {
        db_path: db_path.clone(),
        output: Some(output_path.clone()),
        format: ExportFormat::Csv,
        run_id: None,
        domain: None,
        status: None,
        since: None,
    })
    .await
    .expect("Export should succeed");

    assert_eq!(count, 1, "Should export 1 record");

    let csv_content = std::fs::read_to_string(&output_path).expect("Should read CSV file");

    // Verify all enrichment data is present
    assert!(
        csv_content.contains("nginx") || csv_content.contains("PHP"),
        "CSV should contain technologies"
    );
    assert!(
        csv_content.contains("United States") || csv_content.contains("US"),
        "CSV should contain GeoIP data"
    );
    assert!(
        csv_content.contains("Test Registrar"),
        "CSV should contain WHOIS data"
    );
    assert!(
        csv_content.contains("Google Analytics") || csv_content.contains("UA-123456-1"),
        "CSV should contain analytics IDs"
    );
    assert!(
        csv_content.contains("LinkedIn"),
        "CSV should contain social media links"
    );
    assert!(
        csv_content.contains("missing_csp"),
        "CSV should contain security warnings"
    );
}

#[tokio::test]
async fn test_export_csv_filter_combinations() {
    let temp_dir = TempDir::new().expect("Failed to create temp directory");
    let db_path = temp_dir.path().join("test_export.db");
    let output_path = temp_dir.path().join("output.csv");

    // Create test database with migrations
    let pool = create_test_pool_with_path(&db_path).await;

    // Create run first (required for foreign key)
    create_test_run(&pool, "run_1", 1704067200000).await;
    create_test_run(&pool, "run_2", 1704067200000).await;

    // Create data with different attributes
    create_test_url_status(
        &pool,
        "match.com",
        "match.com",
        200,
        Some("run_1"),
        1704067200000,
    )
    .await;
    create_test_url_status(
        &pool,
        "nomatch.com",
        "nomatch.com",
        404,
        Some("run_1"),
        1704067200000,
    )
    .await;
    create_test_url_status(
        &pool,
        "other.com",
        "other.com",
        200,
        Some("run_2"),
        1704067200000,
    )
    .await;

    drop(pool);

    // Filter by run_id AND status
    let count = export_csv(&ExportOptions {
        db_path: db_path.clone(),
        output: Some(output_path.clone()),
        format: ExportFormat::Csv,
        run_id: Some("run_1".to_string()),
        domain: None,
        status: Some(200),
        since: None,
    })
    .await
    .expect("Export should succeed");

    assert_eq!(
        count, 1,
        "Should export only 1 record matching both filters"
    );

    let csv_content = std::fs::read_to_string(&output_path).expect("Should read CSV file");
    assert!(
        csv_content.contains("match.com"),
        "CSV should contain match.com"
    );
    assert!(
        !csv_content.contains("nomatch.com"),
        "CSV should not contain nomatch.com (wrong status)"
    );
    assert!(
        !csv_content.contains("other.com"),
        "CSV should not contain other.com (wrong run_id)"
    );
}

#[tokio::test]
async fn test_export_csv_filter_by_since() {
    let temp_dir = TempDir::new().expect("Failed to create temp directory");
    let db_path = temp_dir.path().join("test_export.db");
    let output_path = temp_dir.path().join("output.csv");

    // Create test database with migrations
    let pool = create_test_pool_with_path(&db_path).await;

    // Create data with different timestamps
    create_test_url_status(&pool, "old.com", "old.com", 200, None, 1609459200000).await; // 2021-01-01
    create_test_url_status(&pool, "new.com", "new.com", 200, None, 1704067200000).await; // 2024-01-01

    drop(pool);

    // Filter by since (after 2022-01-01)
    let since_timestamp = 1640995200000i64; // 2022-01-01
    let count = export_csv(&ExportOptions {
        db_path: db_path.clone(),
        output: Some(output_path.clone()),
        format: ExportFormat::Csv,
        run_id: None,
        domain: None,
        status: None,
        since: Some(since_timestamp),
    })
    .await
    .expect("Export should succeed");

    assert_eq!(count, 1, "Should export only 1 record after timestamp");

    let csv_content = std::fs::read_to_string(&output_path).expect("Should read CSV file");
    assert!(
        csv_content.contains("new.com"),
        "CSV should contain new.com"
    );
    assert!(
        !csv_content.contains("old.com"),
        "CSV should not contain old.com (too old)"
    );
}

#[tokio::test]
async fn test_export_csv_stdout() {
    let temp_dir = TempDir::new().expect("Failed to create temp directory");
    let db_path = temp_dir.path().join("test_export.db");

    // Create test database with migrations
    let pool = create_test_pool_with_path(&db_path).await;

    create_test_url_status(&pool, "stdout.com", "stdout.com", 200, None, 1704067200000).await;

    drop(pool);

    // Use a temporary file instead of stdout to avoid polluting test output
    // This tests the same code path (writing to a file) without stdout pollution
    let stdout_test_path = temp_dir.path().join("stdout_test.csv");
    let count = export_csv(&ExportOptions {
        db_path: db_path.clone(),
        output: Some(stdout_test_path.clone()),
        format: ExportFormat::Csv,
        run_id: None,
        domain: None,
        status: None,
        since: None,
    })
    .await
    .expect("Export should succeed");

    assert_eq!(count, 1, "Should export 1 record");

    // Verify the output contains expected data (simulates stdout behavior)
    let content = std::fs::read_to_string(&stdout_test_path).expect("Should read output file");
    assert!(
        content.contains("stdout.com"),
        "Output should contain domain"
    );
    assert!(
        content.contains("url,initial_domain"),
        "Output should contain CSV header"
    );
}

#[tokio::test]
async fn test_export_csv_date_formatting() {
    let temp_dir = TempDir::new().expect("Failed to create temp directory");
    let db_path = temp_dir.path().join("test_export.db");
    let output_path = temp_dir.path().join("output.csv");

    // Create test database with migrations
    let pool = create_test_pool_with_path(&db_path).await;

    let url_id =
        create_test_url_status(&pool, "date.com", "date.com", 200, None, 1704067200000).await;

    // Add SSL cert with valid_to date
    sqlx::query("UPDATE url_status SET ssl_cert_valid_to_ms = ? WHERE id = ?")
        .bind(1735689600000i64) // 2025-01-01 in milliseconds
        .bind(url_id)
        .execute(&pool)
        .await
        .expect("Failed to update SSL cert date");

    // Add WHOIS with dates
    sqlx::query(
        "INSERT INTO url_whois (
            url_status_id, creation_date_ms, expiration_date_ms
        ) VALUES (?, ?, ?)",
    )
    .bind(url_id)
    .bind(1609459200000i64) // 2021-01-01 in milliseconds
    .bind(1735689600000i64) // 2025-01-01 in milliseconds
    .execute(&pool)
    .await
    .expect("Failed to insert WHOIS");

    drop(pool);

    let count = export_csv(&ExportOptions {
        db_path: db_path.clone(),
        output: Some(output_path.clone()),
        format: ExportFormat::Csv,
        run_id: None,
        domain: None,
        status: None,
        since: None,
    })
    .await
    .expect("Export should succeed");

    assert_eq!(count, 1, "Should export 1 record");

    let csv_content = std::fs::read_to_string(&output_path).expect("Should read CSV file");

    // Verify dates are formatted correctly (YYYY-MM-DD format)
    assert!(
        csv_content.contains("2025-01-01"),
        "CSV should contain formatted SSL cert date"
    );
    assert!(
        csv_content.contains("2021-01-01"),
        "CSV should contain formatted WHOIS creation date"
    );
}

#[tokio::test]
async fn test_export_csv_comma_separated_lists() {
    let temp_dir = TempDir::new().expect("Failed to create temp directory");
    let db_path = temp_dir.path().join("test_export.db");
    let output_path = temp_dir.path().join("output.csv");

    // Create test database with migrations
    let pool = create_test_pool_with_path(&db_path).await;

    let url_id =
        create_test_url_status(&pool, "list.com", "list.com", 200, None, 1704067200000).await;

    // Add multiple technologies
    for tech in ["nginx", "PHP", "WordPress", "MySQL"] {
        sqlx::query("INSERT INTO url_technologies (url_status_id, technology_name) VALUES (?, ?)")
            .bind(url_id)
            .bind(tech)
            .execute(&pool)
            .await
            .expect("Failed to insert technology");
    }

    // Add multiple analytics IDs
    sqlx::query(
        "INSERT INTO url_analytics_ids (url_status_id, provider, tracking_id) VALUES (?, ?, ?)",
    )
    .bind(url_id)
    .bind("Google Analytics")
    .bind("UA-111-1")
    .execute(&pool)
    .await
    .expect("Failed to insert analytics ID");
    sqlx::query(
        "INSERT INTO url_analytics_ids (url_status_id, provider, tracking_id) VALUES (?, ?, ?)",
    )
    .bind(url_id)
    .bind("Google Tag Manager")
    .bind("GTM-XXXXX")
    .execute(&pool)
    .await
    .expect("Failed to insert analytics ID");

    drop(pool);

    let count = export_csv(&ExportOptions {
        db_path: db_path.clone(),
        output: Some(output_path.clone()),
        format: ExportFormat::Csv,
        run_id: None,
        domain: None,
        status: None,
        since: None,
    })
    .await
    .expect("Export should succeed");

    assert_eq!(count, 1, "Should export 1 record");

    let csv_content = std::fs::read_to_string(&output_path).expect("Should read CSV file");

    // Verify comma-separated lists are formatted correctly
    // Technologies should be comma-separated
    let tech_line = csv_content
        .lines()
        .find(|line| line.contains("list.com"))
        .expect("Should find data row");

    // Should contain technologies (order may vary)
    assert!(
        tech_line.contains("nginx") || tech_line.contains("PHP"),
        "CSV should contain technologies"
    );

    // Should contain analytics IDs
    assert!(
        tech_line.contains("Google Analytics") || tech_line.contains("UA-111-1"),
        "CSV should contain analytics IDs"
    );
}

// Large test function handling comprehensive CSV export validation with all column presence checks.
// Consider refactoring into smaller focused test functions in Phase 4.
#[allow(clippy::too_many_lines)]
#[tokio::test]
async fn test_export_csv_all_columns_present() {
    let temp_dir = TempDir::new().expect("Failed to create temp directory");
    let db_path = temp_dir.path().join("test_export.db");
    let output_path = temp_dir.path().join("output.csv");

    let pool = create_test_pool_with_path(&db_path).await;
    create_test_url_with_enrichment(&pool, "full.com", None).await;
    drop(pool);

    let count = export_csv(&ExportOptions {
        db_path: db_path.clone(),
        output: Some(output_path.clone()),
        format: ExportFormat::Csv,
        run_id: None,
        domain: None,
        status: None,
        since: None,
    })
    .await
    .expect("Export should succeed");

    assert_eq!(count, 1, "Should export 1 record");

    let csv_content = std::fs::read_to_string(&output_path).expect("Should read CSV file");
    let lines: Vec<&str> = csv_content.lines().collect();
    assert!(lines.len() >= 2, "Should have header + at least 1 data row");

    // Verify all 58 expected columns are present in header
    let header = lines[0];
    let expected_columns = [
        "url",
        "initial_domain",
        "final_domain",
        "ip_address",
        "reverse_dns",
        "status",
        "status_description",
        "response_time_ms",
        "title",
        "keywords",
        "description",
        "is_mobile_friendly",
        "redirect_count",
        "final_redirect_url",
        "technologies",
        "technology_count",
        "tls_version",
        "ssl_cert_subject",
        "ssl_cert_issuer",
        "ssl_cert_valid_to",
        "cipher_suite",
        "key_algorithm",
        "certificate_sans",
        "certificate_san_count",
        "oids",
        "oid_count",
        "nameserver_count",
        "txt_record_count",
        "mx_record_count",
        "spf_record",
        "dmarc_record",
        "analytics_ids",
        "analytics_count",
        "social_media_links",
        "social_media_count",
        "security_warnings",
        "security_warning_count",
        "structured_data_types",
        "structured_data_count",
        "http_headers",
        "http_header_count",
        "security_headers",
        "security_header_count",
        "geoip_country_code",
        "geoip_country_name",
        "geoip_region",
        "geoip_city",
        "geoip_latitude",
        "geoip_longitude",
        "geoip_asn",
        "geoip_asn_org",
        "whois_registrar",
        "whois_creation_date",
        "whois_expiration_date",
        "whois_registrant_country",
        "timestamp",
        "run_id",
    ];

    for column in &expected_columns {
        assert!(
            header.contains(column),
            "Header should contain column: {}",
            column
        );
    }

    // Verify data row has correct number of fields (should match header)
    // Use CSV parser to properly handle quoted fields with commas
    use csv::ReaderBuilder;
    let mut header_reader = ReaderBuilder::new()
        .has_headers(false)
        .from_reader(header.as_bytes());
    let header_record = header_reader
        .records()
        .next()
        .expect("Should read header")
        .expect("Should parse header");
    let header_field_count = header_record.len();

    let mut data_reader = ReaderBuilder::new()
        .has_headers(false)
        .from_reader(lines[1].as_bytes());
    let data_record = data_reader
        .records()
        .next()
        .expect("Should read data row")
        .expect("Should parse data row");
    let data_field_count = data_record.len();

    assert_eq!(
        header_field_count, data_field_count,
        "Data row should have same number of fields as header ({} vs {})",
        header_field_count, data_field_count
    );
}

#[tokio::test]
async fn test_export_csv_null_handling() {
    let temp_dir = TempDir::new().expect("Failed to create temp directory");
    let db_path = temp_dir.path().join("test_export.db");
    let output_path = temp_dir.path().join("output.csv");

    let pool = create_test_pool_with_path(&db_path).await;

    // Create URL with many NULL/empty fields
    let url_id = create_test_url_status(
        &pool,
        "nulltest.com",
        "nulltest.com",
        200,
        None,
        1704067200000,
    )
    .await;

    // Explicitly set some fields to NULL
    sqlx::query("UPDATE url_status SET reverse_dns_name = NULL, keywords = NULL, description = NULL, tls_version = NULL WHERE id = ?")
        .bind(url_id)
        .execute(&pool)
        .await
        .expect("Failed to update with NULLs");

    drop(pool);

    let count = export_csv(&ExportOptions {
        db_path: db_path.clone(),
        output: Some(output_path.clone()),
        format: ExportFormat::Csv,
        run_id: None,
        domain: None,
        status: None,
        since: None,
    })
    .await
    .expect("Export should handle NULL values");

    assert_eq!(count, 1, "Should export 1 record");

    let csv_content = std::fs::read_to_string(&output_path).expect("Should read CSV file");
    let lines: Vec<&str> = csv_content.lines().collect();
    let data_row = lines[1];

    // NULL values should be exported as empty strings, not crash
    assert!(
        data_row.contains("nulltest.com"),
        "CSV should contain domain even with NULL fields"
    );
    // Verify row is valid CSV (has correct number of fields)
    // Use CSV parser to properly handle quoted fields
    use csv::ReaderBuilder;
    let mut reader = ReaderBuilder::new()
        .has_headers(false)
        .from_reader(data_row.as_bytes());
    let record = reader
        .records()
        .next()
        .expect("Should read data row")
        .expect("Should parse data row");
    assert!(
        record.len() >= 50,
        "Data row should have all fields even with NULLs (got {})",
        record.len()
    );
}

#[tokio::test]
async fn test_export_csv_redirect_chain_edge_cases() {
    let temp_dir = TempDir::new().expect("Failed to create temp directory");
    let db_path = temp_dir.path().join("test_export.db");
    let output_path = temp_dir.path().join("output.csv");

    let pool = create_test_pool_with_path(&db_path).await;
    let _url_id = create_test_url_status(
        &pool,
        "redirect.com",
        "final.com",
        200,
        None,
        1704067200000i64,
    )
    .await;

    // No redirects (should use final_domain as final_redirect_url)
    drop(pool);

    let count = export_csv(&ExportOptions {
        db_path: db_path.clone(),
        output: Some(output_path.clone()),
        format: ExportFormat::Csv,
        run_id: None,
        domain: None,
        status: None,
        since: None,
    })
    .await
    .expect("Export should handle no redirects");

    assert_eq!(count, 1, "Should export 1 record");

    let csv_content = std::fs::read_to_string(&output_path).expect("Should read CSV file");
    assert!(
        csv_content.contains("final.com"),
        "CSV should contain final_domain when no redirects"
    );

    // Test with multiple redirects (reuse same database)
    let pool2 = create_test_pool_with_path(&db_path).await;
    let url_id2 =
        create_test_url_status(&pool2, "start.com", "end.com", 200, None, 1704067300000i64).await;

    for (i, url) in ["https://start.com", "https://middle.com", "https://end.com"]
        .iter()
        .enumerate()
    {
        sqlx::query(
            "INSERT INTO url_redirect_chain (url_status_id, sequence_order, redirect_url) VALUES (?, ?, ?)",
        )
        .bind(url_id2)
        .bind(i as i64)
        .bind(*url)
        .execute(&pool2)
        .await
        .expect("Failed to insert redirect");
    }

    drop(pool2);

    let output_path2 = temp_dir.path().join("output2.csv");
    let count = export_csv(&ExportOptions {
        db_path: db_path.clone(),
        output: Some(output_path2.clone()),
        format: ExportFormat::Csv,
        run_id: None,
        domain: None,
        status: None,
        since: None,
    })
    .await
    .expect("Export should handle multiple redirects");

    assert_eq!(count, 2, "Should export 2 records");

    let csv_content = std::fs::read_to_string(&output_path2).expect("Should read CSV file");
    // Should contain final redirect URL
    assert!(
        csv_content.contains("https://end.com"),
        "CSV should contain final redirect URL"
    );
    // Redirect count should be 3
    // Use CSV parser to properly extract the redirect_count field
    use csv::ReaderBuilder;
    let mut reader = ReaderBuilder::new()
        .has_headers(true)
        .from_reader(csv_content.as_bytes());
    let mut found_start = false;
    for result in reader.records() {
        let record = result.expect("Should parse CSV record");
        // Check if this row is for start.com (could be in url, initial_domain, or final_domain fields)
        let url = record.get(0).unwrap_or("");
        let initial_domain = record.get(1).unwrap_or("");
        let final_domain = record.get(2).unwrap_or("");
        if url.contains("start.com")
            || initial_domain.contains("start.com")
            || final_domain.contains("start.com")
        {
            // redirect_count is at index 12 (after url, initial_domain, final_domain, etc.)
            let redirect_count = record.get(12).expect("Should have redirect_count field");
            assert_eq!(
                redirect_count, "3",
                "CSV should show redirect count of 3 for start.com"
            );
            found_start = true;
        }
    }
    assert!(found_start, "Should find start.com in CSV");
}

#[tokio::test]
async fn test_export_csv_header_filtering() {
    let temp_dir = TempDir::new().expect("Failed to create temp directory");
    let db_path = temp_dir.path().join("test_export.db");
    let output_path = temp_dir.path().join("output.csv");

    let pool = create_test_pool_with_path(&db_path).await;
    let url_id = create_test_url_status(
        &pool,
        "headers.com",
        "headers.com",
        200,
        None,
        1704067200000,
    )
    .await;

    // Insert both filtered and unfiltered HTTP headers
    sqlx::query(
        "INSERT INTO url_http_headers (url_status_id, header_name, header_value) VALUES (?, ?, ?)",
    )
    .bind(url_id)
    .bind("Content-Type")
    .bind("text/html; charset=utf-8")
    .execute(&pool)
    .await
    .expect("Failed to insert header");

    sqlx::query(
        "INSERT INTO url_http_headers (url_status_id, header_name, header_value) VALUES (?, ?, ?)",
    )
    .bind(url_id)
    .bind("Server")
    .bind("nginx/1.18.0")
    .execute(&pool)
    .await
    .expect("Failed to insert header");

    sqlx::query(
        "INSERT INTO url_http_headers (url_status_id, header_name, header_value) VALUES (?, ?, ?)",
    )
    .bind(url_id)
    .bind("X-Custom-Header")
    .bind("should-not-appear")
    .execute(&pool)
    .await
    .expect("Failed to insert header");

    // Insert security headers
    sqlx::query("INSERT INTO url_security_headers (url_status_id, header_name, header_value) VALUES (?, ?, ?)")
        .bind(url_id)
        .bind("Content-Security-Policy")
        .bind("default-src 'self'")
        .execute(&pool)
        .await
        .expect("Failed to insert security header");

    sqlx::query("INSERT INTO url_security_headers (url_status_id, header_name, header_value) VALUES (?, ?, ?)")
        .bind(url_id)
        .bind("X-Frame-Options")
        .bind("DENY")
        .execute(&pool)
        .await
        .expect("Failed to insert security header");

    sqlx::query("INSERT INTO url_security_headers (url_status_id, header_name, header_value) VALUES (?, ?, ?)")
        .bind(url_id)
        .bind("X-Other-Header")
        .bind("should-not-appear")
        .execute(&pool)
        .await
        .expect("Failed to insert security header");

    drop(pool);

    let count = export_csv(&ExportOptions {
        db_path: db_path.clone(),
        output: Some(output_path.clone()),
        format: ExportFormat::Csv,
        run_id: None,
        domain: None,
        status: None,
        since: None,
    })
    .await
    .expect("Export should filter headers");

    assert_eq!(count, 1, "Should export 1 record");

    let csv_content = std::fs::read_to_string(&output_path).expect("Should read CSV file");
    let data_line = csv_content.lines().nth(1).expect("Should have data row");

    // Should contain filtered headers
    assert!(
        data_line.contains("Content-Type") || data_line.contains("text/html"),
        "CSV should contain filtered HTTP header"
    );
    assert!(
        data_line.contains("Server") || data_line.contains("nginx"),
        "CSV should contain filtered HTTP header"
    );
    assert!(
        data_line.contains("Content-Security-Policy") || data_line.contains("default-src"),
        "CSV should contain filtered security header"
    );
    assert!(
        data_line.contains("X-Frame-Options") || data_line.contains("DENY"),
        "CSV should contain filtered security header"
    );

    // Should NOT contain unfiltered headers (but this is hard to verify without parsing CSV properly)
    // The header_count should reflect total count, not filtered count
}

#[tokio::test]
async fn test_export_csv_unicode_and_special_chars() {
    let temp_dir = TempDir::new().expect("Failed to create temp directory");
    let db_path = temp_dir.path().join("test_export.db");
    let output_path = temp_dir.path().join("output.csv");

    let pool = create_test_pool_with_path(&db_path).await;
    let url_id = create_test_url_status(
        &pool,
        "unicode.com",
        "unicode.com",
        200,
        None,
        1704067200000,
    )
    .await;

    // Insert data with unicode and special characters
    sqlx::query("UPDATE url_status SET title = ? WHERE id = ?")
        .bind("Test Title with émojis 🚀 and \"quotes\"")
        .bind(url_id)
        .execute(&pool)
        .await
        .expect("Failed to update title");

    sqlx::query("INSERT INTO url_technologies (url_status_id, technology_name) VALUES (?, ?)")
        .bind(url_id)
        .bind("Tech with, commas & \"quotes\"")
        .execute(&pool)
        .await
        .expect("Failed to insert technology");

    drop(pool);

    let count = export_csv(&ExportOptions {
        db_path: db_path.clone(),
        output: Some(output_path.clone()),
        format: ExportFormat::Csv,
        run_id: None,
        domain: None,
        status: None,
        since: None,
    })
    .await
    .expect("Export should handle unicode and special chars");

    assert_eq!(count, 1, "Should export 1 record");

    let csv_content = std::fs::read_to_string(&output_path).expect("Should read CSV file");

    // CSV library should properly escape special characters
    assert!(
        csv_content.contains("unicode.com"),
        "CSV should contain domain"
    );
    // Note: CSV library handles escaping, so we just verify it doesn't crash
    // The exact format depends on csv crate's escaping rules
}
