//! Generic CSV → ImportPayload converter.
//!
//! Parses CSV data with a user-provided column mapping and produces an
//! `ImportPayload` containing subscribers and/or transactions depending
//! on which columns are mapped.

use chrono::{DateTime, NaiveDate, NaiveDateTime, Utc};

use super::{ColumnMapping, ImportPayload, ImportSubscriber, ImportTransaction};
use crate::error::{AppError, Result};

/// Parse CSV bytes into an `ImportPayload` using the given column mapping.
///
/// Rows with an email column produce `ImportSubscriber` entries.
/// Rows with an amount column produce `ImportTransaction` entries.
/// A single row can produce both if both columns are mapped.
#[tracing::instrument(skip_all, name = "import::parse_csv")]
pub fn parse_csv(bytes: &[u8], mapping: &ColumnMapping) -> Result<ImportPayload> {
    // Strip UTF-8 BOM if present
    let bytes = if bytes.starts_with(&[0xEF, 0xBB, 0xBF]) {
        &bytes[3..]
    } else {
        bytes
    };

    let mut reader = csv::ReaderBuilder::new()
        .flexible(true)
        .trim(csv::Trim::All)
        .from_reader(bytes);

    let mut payload = ImportPayload::default();
    let mut errors = Vec::new();

    const MAX_IMPORT_ROWS: usize = 100_000;

    for (row_idx, result) in reader.records().enumerate() {
        if row_idx >= MAX_IMPORT_ROWS {
            errors.push(format!("Import capped at {} rows", MAX_IMPORT_ROWS));
            break;
        }
        let record = match result {
            Ok(r) => r,
            Err(e) => {
                errors.push(format!("Row {}: parse error: {}", row_idx + 2, e));
                continue;
            }
        };

        // Extract email
        let email = mapping
            .email
            .and_then(|i| record.get(i))
            .map(|s| s.trim().to_lowercase())
            .filter(|s| {
                let parts: Vec<&str> = s.splitn(2, '@').collect();
                parts.len() == 2
                    && !parts[0].is_empty()
                    && parts[1].contains('.')
                    && !parts[1].starts_with('.')
                    && !parts[1].ends_with('.')
            });

        // Extract name
        let name = mapping
            .name
            .and_then(|i| record.get(i))
            .map(sanitize_field)
            .filter(|s| !s.is_empty());

        // Extract amount (always in cents)
        let amount_cents = mapping
            .amount
            .and_then(|i| record.get(i))
            .and_then(parse_amount_cents);

        // Extract date
        let date = mapping
            .date
            .and_then(|i| record.get(i))
            .and_then(parse_flexible_date);

        // Extract item title
        let item_title = mapping
            .item_title
            .and_then(|i| record.get(i))
            .map(sanitize_field)
            .filter(|s| !s.is_empty());

        // Extract tier
        let tier_name = mapping
            .tier
            .and_then(|i| record.get(i))
            .map(sanitize_field)
            .filter(|s| !s.is_empty());

        // Extract status
        let status = mapping
            .status
            .and_then(|i| record.get(i))
            .map(|s| sanitize_field(s).to_lowercase())
            .filter(|s| !s.is_empty());

        // Build subscriber if we have email
        if let Some(ref email) = email {
            payload.subscribers.push(ImportSubscriber {
                email: email.clone(),
                name: name.clone(),
                tier_name,
                status: status.clone(),
                joined_at: date,
                lifetime_amount_cents: amount_cents,
                stripe_customer_id: None,
            });
        }

        // Build transaction if we have amount and email
        if let (Some(cents), Some(email)) = (amount_cents, &email) {
            payload.transactions.push(ImportTransaction {
                buyer_email: email.clone(),
                buyer_name: name,
                item_title,
                amount_cents: cents,
                currency: "USD".into(),
                date: date.unwrap_or_else(Utc::now),
                status,
            });
        }
    }

    if payload.subscribers.is_empty() && payload.transactions.is_empty() {
        return Err(AppError::validation(
            "No valid rows found. Check your column mapping and CSV format.",
        ));
    }

    if !errors.is_empty() {
        tracing::warn!(error_count = errors.len(), "CSV parse had row-level errors");
    }

    Ok(payload)
}

/// Parse a currency string into cents.
///
/// All values are interpreted as cents. A decimal point is treated as
/// dollars.cents (e.g. "12.50" → 1250). Whole numbers are cents as-is
/// (e.g. "500" → 500). Currency symbols and commas are stripped.
fn parse_amount_cents(s: &str) -> Option<i64> {
    let cleaned: String = s
        .trim()
        .replace(['$', '€', '£', '¥'], "")
        .replace(',', "")
        .trim()
        .to_string();

    if cleaned.is_empty() {
        return None;
    }

    // Decimal point means dollars.cents
    if let Some((dollars, cents_str)) = cleaned.split_once('.') {
        let dollars: i64 = dollars.trim().parse().ok()?;
        // Pad or truncate to exactly 2 decimal places
        let cents_str = if cents_str.len() >= 2 {
            &cents_str[..2]
        } else {
            cents_str
        };
        let cents: i64 = if cents_str.len() == 1 {
            cents_str.parse::<i64>().ok()? * 10
        } else {
            cents_str.parse().ok()?
        };
        return Some(if dollars < 0 { dollars * 100 - cents } else { dollars * 100 + cents });
    }

    // Whole number — already in cents
    cleaned.parse().ok()
}

/// Parse dates flexibly. Supports:
/// - ISO 8601: "2024-01-15T10:30:00Z", "2024-01-15"
/// - US format: "01/15/2024", "1/15/2024"
/// - EU format: "15.01.2024", "15/01/2024" (day > 12 disambiguates)
/// - Epoch seconds: "1705312200"
fn parse_flexible_date(s: &str) -> Option<DateTime<Utc>> {
    let s = s.trim();
    if s.is_empty() {
        return None;
    }

    // ISO 8601 with time
    if let Ok(dt) = s.parse::<DateTime<Utc>>() {
        return Some(dt);
    }

    // ISO 8601 datetime without timezone
    if let Ok(ndt) = NaiveDateTime::parse_from_str(s, "%Y-%m-%dT%H:%M:%S") {
        return Some(ndt.and_utc());
    }

    // ISO 8601 date only
    if let Ok(nd) = NaiveDate::parse_from_str(s, "%Y-%m-%d") {
        return nd.and_hms_opt(0, 0, 0).map(|ndt| ndt.and_utc());
    }

    // US format: MM/DD/YYYY
    if let Ok(nd) = NaiveDate::parse_from_str(s, "%m/%d/%Y") {
        return nd.and_hms_opt(0, 0, 0).map(|ndt| ndt.and_utc());
    }

    // EU format with dots: DD.MM.YYYY
    if let Ok(nd) = NaiveDate::parse_from_str(s, "%d.%m.%Y") {
        return nd.and_hms_opt(0, 0, 0).map(|ndt| ndt.and_utc());
    }

    // EU format with slashes: DD/MM/YYYY (try if day > 12)
    if let Some((a, rest)) = s.split_once('/')
        && let Some((b, c)) = rest.split_once('/')
        && let (Ok(first), Ok(second), Ok(year)) =
            (a.parse::<u32>(), b.parse::<u32>(), c.parse::<i32>())
        && first > 12
        && second <= 12
        && let Some(nd) = NaiveDate::from_ymd_opt(year, second, first)
    {
        return nd.and_hms_opt(0, 0, 0).map(|ndt| ndt.and_utc());
    }

    // Epoch seconds
    if let Ok(ts) = s.parse::<i64>() {
        return DateTime::from_timestamp(ts, 0);
    }

    None
}

/// Sanitize a CSV field value: trim whitespace, strip formula-triggering chars.
fn sanitize_field(s: &str) -> String {
    let trimmed = s.trim();
    if trimmed.starts_with(['=', '+', '-', '@']) {
        format!("'{trimmed}")
    } else {
        trimmed.to_string()
    }
}

#[cfg(test)]
mod tests {
    use super::*;

    #[test]
    fn parse_amount_decimal_as_dollars_cents() {
        assert_eq!(parse_amount_cents("$12.50"), Some(1250));
        assert_eq!(parse_amount_cents("12.50"), Some(1250));
        assert_eq!(parse_amount_cents("€10.00"), Some(1000));
        assert_eq!(parse_amount_cents("£5.5"), Some(550));
        assert_eq!(parse_amount_cents("$1,234.56"), Some(123456));
    }

    #[test]
    fn parse_amount_whole_numbers_are_cents() {
        assert_eq!(parse_amount_cents("500"), Some(500));
        assert_eq!(parse_amount_cents("15000"), Some(15000));
        assert_eq!(parse_amount_cents("0"), Some(0));
        assert_eq!(parse_amount_cents("10"), Some(10));
    }

    #[test]
    fn parse_amount_empty() {
        assert_eq!(parse_amount_cents(""), None);
        assert_eq!(parse_amount_cents("   "), None);
        assert_eq!(parse_amount_cents("abc"), None);
    }

    #[test]
    fn parse_date_iso8601() {
        let dt = parse_flexible_date("2024-01-15").unwrap();
        assert_eq!(dt.date_naive().to_string(), "2024-01-15");
    }

    #[test]
    fn parse_date_iso8601_with_time() {
        let dt = parse_flexible_date("2024-01-15T10:30:00Z").unwrap();
        assert_eq!(dt.date_naive().to_string(), "2024-01-15");
    }

    #[test]
    fn parse_date_us_format() {
        let dt = parse_flexible_date("01/15/2024").unwrap();
        assert_eq!(dt.date_naive().to_string(), "2024-01-15");
    }

    #[test]
    fn parse_date_eu_format_dots() {
        let dt = parse_flexible_date("15.01.2024").unwrap();
        assert_eq!(dt.date_naive().to_string(), "2024-01-15");
    }

    #[test]
    fn parse_date_eu_format_slashes_day_over_12() {
        let dt = parse_flexible_date("25/01/2024").unwrap();
        assert_eq!(dt.date_naive().to_string(), "2024-01-25");
    }

    #[test]
    fn parse_date_epoch() {
        let dt = parse_flexible_date("1705312200").unwrap();
        assert_eq!(dt.date_naive().to_string(), "2024-01-15");
    }

    #[test]
    fn parse_date_empty() {
        assert!(parse_flexible_date("").is_none());
        assert!(parse_flexible_date("   ").is_none());
        assert!(parse_flexible_date("not-a-date").is_none());
    }

    #[test]
    fn parse_csv_basic_subscribers() {
        let csv = b"email,name\nalice@test.com,Alice\nbob@test.com,Bob\n";
        let mapping = ColumnMapping {
            email: Some(0),
            name: Some(1),
            ..Default::default()
        };
        let payload = parse_csv(csv, &mapping).unwrap();
        assert_eq!(payload.subscribers.len(), 2);
        assert_eq!(payload.subscribers[0].email, "alice@test.com");
        assert_eq!(payload.subscribers[0].name.as_deref(), Some("Alice"));
        assert_eq!(payload.subscribers[1].email, "bob@test.com");
    }

    #[test]
    fn parse_csv_with_bom() {
        let mut csv = vec![0xEF, 0xBB, 0xBF]; // UTF-8 BOM
        csv.extend_from_slice(b"email\nalice@test.com\n");
        let mapping = ColumnMapping {
            email: Some(0),
            ..Default::default()
        };
        let payload = parse_csv(&csv, &mapping).unwrap();
        assert_eq!(payload.subscribers.len(), 1);
    }

    #[test]
    fn parse_csv_transactions() {
        let csv = b"email,amount,date\nbuyer@test.com,$25.00,2024-01-15\n";
        let mapping = ColumnMapping {
            email: Some(0),
            amount: Some(1),
            date: Some(2),
            ..Default::default()
        };
        let payload = parse_csv(csv, &mapping).unwrap();
        assert_eq!(payload.subscribers.len(), 1);
        assert_eq!(payload.transactions.len(), 1);
        assert_eq!(payload.transactions[0].amount_cents, 2500);
    }

    #[test]
    fn parse_csv_empty_returns_error() {
        let csv = b"email,name\n";
        let mapping = ColumnMapping {
            email: Some(0),
            name: Some(1),
            ..Default::default()
        };
        assert!(parse_csv(csv, &mapping).is_err());
    }

    #[test]
    fn parse_csv_skips_invalid_emails() {
        let csv = b"email\nnot-an-email\nalice@test.com\n";
        let mapping = ColumnMapping {
            email: Some(0),
            ..Default::default()
        };
        let payload = parse_csv(csv, &mapping).unwrap();
        assert_eq!(payload.subscribers.len(), 1);
        assert_eq!(payload.subscribers[0].email, "alice@test.com");
    }

    #[test]
    fn parse_csv_mixed_currencies() {
        let csv = b"email,amount\na@b.com,$10.00\nc@d.com,EUR20.50\ne@f.com,500\n";
        let mapping = ColumnMapping {
            email: Some(0),
            amount: Some(1),
            ..Default::default()
        };
        let payload = parse_csv(csv, &mapping).unwrap();
        assert_eq!(payload.transactions.len(), 2); // EUR20.50 won't parse (no € symbol)
        assert_eq!(payload.transactions[0].amount_cents, 1000);
    }

    #[test]
    fn sanitize_field_strips_formula_chars() {
        assert_eq!(sanitize_field("=SUM(A1)"), "'=SUM(A1)");
        assert_eq!(sanitize_field("+cmd"), "'+cmd");
        assert_eq!(sanitize_field("normal"), "normal");
    }

    // ── Edge cases: empty CSV / headers only ──

    #[test]
    fn parse_csv_completely_empty() {
        let csv = b"";
        let mapping = ColumnMapping {
            email: Some(0),
            ..Default::default()
        };
        assert!(parse_csv(csv, &mapping).is_err());
    }

    #[test]
    fn parse_csv_only_headers_no_data_rows() {
        let csv = b"email,name,amount\n";
        let mapping = ColumnMapping {
            email: Some(0),
            name: Some(1),
            amount: Some(2),
            ..Default::default()
        };
        assert!(parse_csv(csv, &mapping).is_err());
    }

    #[test]
    fn parse_csv_special_characters_in_fields() {
        let csv = b"email,name\nalice@test.com,\"O'Brien, Jr.\"\nbob@test.com,\"Name with \"\"quotes\"\"\"\n";
        let mapping = ColumnMapping {
            email: Some(0),
            name: Some(1),
            ..Default::default()
        };
        let payload = parse_csv(csv, &mapping).unwrap();
        assert_eq!(payload.subscribers.len(), 2);
        assert_eq!(payload.subscribers[0].name.as_deref(), Some("O'Brien, Jr."));
    }

    // ── Column mapping edge cases ──

    #[test]
    fn parse_csv_mapped_column_index_beyond_row_length() {
        let csv = b"email\nalice@test.com\n";
        let mapping = ColumnMapping {
            email: Some(0),
            name: Some(5), // column 5 does not exist
            ..Default::default()
        };
        let payload = parse_csv(csv, &mapping).unwrap();
        assert_eq!(payload.subscribers.len(), 1);
        assert!(payload.subscribers[0].name.is_none());
    }

    #[test]
    fn parse_csv_extra_unmapped_columns_ignored() {
        let csv = b"email,name,phone,address,zip\nalice@test.com,Alice,555-1234,123 Main,90210\n";
        let mapping = ColumnMapping {
            email: Some(0),
            name: Some(1),
            ..Default::default()
        };
        let payload = parse_csv(csv, &mapping).unwrap();
        assert_eq!(payload.subscribers.len(), 1);
        assert_eq!(payload.subscribers[0].email, "alice@test.com");
        assert_eq!(payload.subscribers[0].name.as_deref(), Some("Alice"));
    }

    #[test]
    fn parse_csv_no_email_mapping_no_subscribers() {
        let csv = b"name,amount\nAlice,$10.00\n";
        let mapping = ColumnMapping {
            name: Some(0),
            amount: Some(1),
            ..Default::default()
        };
        // No email mapped means no subscribers or transactions
        assert!(parse_csv(csv, &mapping).is_err());
    }

    // ── Price parsing edge cases ──

    #[test]
    fn parse_amount_dollar_sign_with_cents() {
        assert_eq!(parse_amount_cents("$10.99"), Some(1099));
    }

    #[test]
    fn parse_amount_free_or_zero() {
        assert_eq!(parse_amount_cents("Free"), None);
        assert_eq!(parse_amount_cents("$0.00"), Some(0));
        assert_eq!(parse_amount_cents("0"), Some(0));
    }

    #[test]
    fn parse_amount_yen_symbol() {
        // ¥1500 whole number = 1500 cents
        assert_eq!(parse_amount_cents("¥1500"), Some(1500));
    }

    #[test]
    fn parse_amount_pound_with_pence() {
        assert_eq!(parse_amount_cents("£9.99"), Some(999));
    }

    #[test]
    fn parse_amount_trailing_whitespace() {
        assert_eq!(parse_amount_cents("  $12.50  "), Some(1250));
    }

    #[test]
    fn parse_amount_commas_in_thousands() {
        assert_eq!(parse_amount_cents("$1,000.00"), Some(100000));
        assert_eq!(parse_amount_cents("1,234,567.89"), Some(123456789));
    }

    #[test]
    fn parse_amount_single_decimal_digit() {
        assert_eq!(parse_amount_cents("5.5"), Some(550));
    }

    #[test]
    fn parse_amount_three_decimal_digits_truncates() {
        assert_eq!(parse_amount_cents("12.999"), Some(1299));
    }

    #[test]
    fn parse_amount_negative_parses_as_negative_cents() {
        assert_eq!(parse_amount_cents("-10.00"), Some(-1000));
        assert_eq!(parse_amount_cents("-"), None);
    }

    // ── Date parsing edge cases ──

    #[test]
    fn parse_date_iso8601_datetime_no_timezone() {
        let dt = parse_flexible_date("2024-06-15T14:30:00").unwrap();
        assert_eq!(dt.date_naive().to_string(), "2024-06-15");
    }

    #[test]
    fn parse_date_whitespace_padded() {
        let dt = parse_flexible_date("  2024-01-15  ").unwrap();
        assert_eq!(dt.date_naive().to_string(), "2024-01-15");
    }

    #[test]
    fn parse_date_garbage_returns_none() {
        assert!(parse_flexible_date("yesterday").is_none());
        assert!(parse_flexible_date("2024/13/01").is_none()); // month 13
        assert!(parse_flexible_date("foo bar baz").is_none());
    }

    #[test]
    fn parse_date_ambiguous_slash_format_defaults_to_us() {
        // 01/05/2024 - day <= 12, so it parses as US (Jan 5)
        let dt = parse_flexible_date("01/05/2024").unwrap();
        assert_eq!(dt.date_naive().to_string(), "2024-01-05");
    }

    #[test]
    fn parse_date_epoch_zero() {
        let dt = parse_flexible_date("0").unwrap();
        assert_eq!(dt.date_naive().to_string(), "1970-01-01");
    }

    // ── Unicode in fields ──

    #[test]
    fn parse_csv_unicode_names_and_titles() {
        let csv = "email,name,item_title\nalice@test.com,Alícia Müller,Über Spëcial Tïtle\nbob@test.com,\u{1F600} Bob,日本語タイトル\n";
        let mapping = ColumnMapping {
            email: Some(0),
            name: Some(1),
            item_title: Some(2),
            ..Default::default()
        };
        let payload = parse_csv(csv.as_bytes(), &mapping).unwrap();
        assert_eq!(payload.subscribers.len(), 2);
        assert_eq!(
            payload.subscribers[0].name.as_deref(),
            Some("Alícia Müller")
        );
        assert_eq!(
            payload.subscribers[1].name.as_deref(),
            Some("\u{1F600} Bob")
        );
    }

    #[test]
    fn parse_csv_cjk_characters() {
        let csv = "email,name\ntest@example.com,田中太郎\n";
        let mapping = ColumnMapping {
            email: Some(0),
            name: Some(1),
            ..Default::default()
        };
        let payload = parse_csv(csv.as_bytes(), &mapping).unwrap();
        assert_eq!(payload.subscribers[0].name.as_deref(), Some("田中太郎"));
    }

    // ── Sanitization edge cases ──

    #[test]
    fn sanitize_field_all_formula_prefixes() {
        assert_eq!(sanitize_field("@import"), "'@import");
        assert_eq!(sanitize_field("-command"), "'-command");
        // Tab and CR are whitespace, so trim() strips them before starts_with
        // Only non-whitespace formula chars are caught after trimming
        assert_eq!(sanitize_field("\tcmd"), "cmd");
    }

    #[test]
    fn sanitize_field_trims_whitespace() {
        assert_eq!(sanitize_field("  hello  "), "hello");
        assert_eq!(sanitize_field("\n\t  data  \n"), "data");
    }

    // ── Full CSV integration ──

    #[test]
    fn parse_csv_all_columns_mapped() {
        let csv = b"email,name,amount,date,item,tier,status\n\
                     alice@test.com,Alice,$25.00,2024-01-15,My Album,gold,active\n";
        let mapping = ColumnMapping {
            email: Some(0),
            name: Some(1),
            amount: Some(2),
            date: Some(3),
            item_title: Some(4),
            tier: Some(5),
            status: Some(6),
        };
        let payload = parse_csv(csv, &mapping).unwrap();
        assert_eq!(payload.subscribers.len(), 1);
        assert_eq!(payload.transactions.len(), 1);

        let sub = &payload.subscribers[0];
        assert_eq!(sub.email, "alice@test.com");
        assert_eq!(sub.name.as_deref(), Some("Alice"));
        assert_eq!(sub.tier_name.as_deref(), Some("gold"));
        assert_eq!(sub.status.as_deref(), Some("active"));
        assert_eq!(sub.lifetime_amount_cents, Some(2500));

        let txn = &payload.transactions[0];
        assert_eq!(txn.buyer_email, "alice@test.com");
        assert_eq!(txn.amount_cents, 2500);
        assert_eq!(txn.item_title.as_deref(), Some("My Album"));
        assert_eq!(txn.currency, "USD");
    }

    #[test]
    fn parse_csv_email_lowercased() {
        let csv = b"email\nALICE@TEST.COM\nBob@Example.Com\n";
        let mapping = ColumnMapping {
            email: Some(0),
            ..Default::default()
        };
        let payload = parse_csv(csv, &mapping).unwrap();
        assert_eq!(payload.subscribers[0].email, "alice@test.com");
        assert_eq!(payload.subscribers[1].email, "bob@example.com");
    }

    #[test]
    fn parse_csv_whitespace_only_name_treated_as_empty() {
        let csv = b"email,name\nalice@test.com,   \n";
        let mapping = ColumnMapping {
            email: Some(0),
            name: Some(1),
            ..Default::default()
        };
        let payload = parse_csv(csv, &mapping).unwrap();
        assert!(payload.subscribers[0].name.is_none());
    }

    #[test]
    fn parse_csv_transaction_requires_email() {
        // Amount present but no email -> no transaction
        let csv = b"amount\n$10.00\n";
        let mapping = ColumnMapping {
            amount: Some(0),
            ..Default::default()
        };
        assert!(parse_csv(csv, &mapping).is_err());
    }

    #[test]
    fn parse_csv_status_lowercased() {
        let csv = b"email,status\nalice@test.com,ACTIVE\n";
        let mapping = ColumnMapping {
            email: Some(0),
            status: Some(1),
            ..Default::default()
        };
        let payload = parse_csv(csv, &mapping).unwrap();
        assert_eq!(payload.subscribers[0].status.as_deref(), Some("active"));
    }

    #[test]
    fn parse_csv_flexible_row_lengths() {
        // Rows with different column counts (flexible mode)
        let csv = b"email,name,extra\nalice@test.com,Alice\nbob@test.com,Bob,stuff,bonus\n";
        let mapping = ColumnMapping {
            email: Some(0),
            name: Some(1),
            ..Default::default()
        };
        let payload = parse_csv(csv, &mapping).unwrap();
        assert_eq!(payload.subscribers.len(), 2);
    }
}