envelope_cli/services/
import.rs

1//! CSV Import service
2//!
3//! Provides functionality for importing transactions from CSV files,
4//! including column mapping, date parsing, duplicate detection, and batch import.
5
6use std::collections::HashMap;
7
8use chrono::NaiveDate;
9
10use crate::error::EnvelopeResult;
11use crate::models::{AccountId, CategoryId, Money, TransactionStatus};
12use crate::services::TransactionService;
13use crate::storage::Storage;
14use csv::{Reader, StringRecord};
15
16/// Column mapping configuration for CSV import
17#[derive(Debug, Clone)]
18pub struct ColumnMapping {
19    /// Index of the date column
20    pub date_column: usize,
21    /// Index of the amount column (or separate inflow/outflow columns)
22    pub amount_column: Option<usize>,
23    /// Index of the outflow column (if using separate columns)
24    pub outflow_column: Option<usize>,
25    /// Index of the inflow column (if using separate columns)
26    pub inflow_column: Option<usize>,
27    /// Index of the payee/description column
28    pub payee_column: Option<usize>,
29    /// Index of the memo/notes column
30    pub memo_column: Option<usize>,
31    /// Date format string (e.g., "%Y-%m-%d", "%m/%d/%Y")
32    pub date_format: String,
33    /// Whether the first row is a header
34    pub has_header: bool,
35    /// Delimiter character
36    pub delimiter: char,
37    /// Whether to invert amounts (some banks use positive for debits)
38    pub invert_amounts: bool,
39}
40
41impl Default for ColumnMapping {
42    fn default() -> Self {
43        Self {
44            date_column: 0,
45            amount_column: Some(1),
46            outflow_column: None,
47            inflow_column: None,
48            payee_column: Some(2),
49            memo_column: None,
50            date_format: "%Y-%m-%d".to_string(),
51            has_header: true,
52            delimiter: ',',
53            invert_amounts: false,
54        }
55    }
56}
57
58impl ColumnMapping {
59    /// Create a new column mapping
60    pub fn new() -> Self {
61        Self::default()
62    }
63
64    /// Common mapping for bank CSV exports (date, description, amount)
65    pub fn simple_bank() -> Self {
66        Self {
67            date_column: 0,
68            amount_column: Some(2),
69            outflow_column: None,
70            inflow_column: None,
71            payee_column: Some(1),
72            memo_column: None,
73            date_format: "%m/%d/%Y".to_string(),
74            has_header: true,
75            delimiter: ',',
76            invert_amounts: false,
77        }
78    }
79
80    /// Common mapping for credit card CSV exports
81    pub fn credit_card() -> Self {
82        Self {
83            date_column: 0,
84            amount_column: Some(2),
85            outflow_column: None,
86            inflow_column: None,
87            payee_column: Some(1),
88            memo_column: Some(3),
89            date_format: "%m/%d/%Y".to_string(),
90            has_header: true,
91            delimiter: ',',
92            invert_amounts: true, // Credit cards often show positive for purchases
93        }
94    }
95
96    /// Mapping for separate inflow/outflow columns
97    pub fn separate_inout(
98        date_col: usize,
99        outflow_col: usize,
100        inflow_col: usize,
101        payee_col: usize,
102    ) -> Self {
103        Self {
104            date_column: date_col,
105            amount_column: None,
106            outflow_column: Some(outflow_col),
107            inflow_column: Some(inflow_col),
108            payee_column: Some(payee_col),
109            memo_column: None,
110            date_format: "%Y-%m-%d".to_string(),
111            has_header: true,
112            delimiter: ',',
113            invert_amounts: false,
114        }
115    }
116
117    /// Set the date format
118    pub fn with_date_format(mut self, format: &str) -> Self {
119        self.date_format = format.to_string();
120        self
121    }
122
123    /// Set whether first row is header
124    pub fn with_header(mut self, has_header: bool) -> Self {
125        self.has_header = has_header;
126        self
127    }
128
129    /// Set the delimiter
130    pub fn with_delimiter(mut self, delimiter: char) -> Self {
131        self.delimiter = delimiter;
132        self
133    }
134}
135
136/// A parsed row from the CSV before import
137#[derive(Debug, Clone)]
138pub struct ParsedTransaction {
139    /// Transaction date
140    pub date: NaiveDate,
141    /// Amount (negative for outflow)
142    pub amount: Money,
143    /// Payee/description
144    pub payee: String,
145    /// Memo/notes
146    pub memo: String,
147    /// Original row number in CSV (0-indexed, excluding header)
148    pub row_number: usize,
149    /// Generated import ID for duplicate detection
150    pub import_id: String,
151}
152
153impl ParsedTransaction {
154    /// Generate an import ID based on the transaction data
155    pub fn generate_import_id(date: NaiveDate, amount: Money, payee: &str) -> String {
156        use std::hash::{Hash, Hasher};
157        let mut hasher = std::collections::hash_map::DefaultHasher::new();
158        date.hash(&mut hasher);
159        amount.cents().hash(&mut hasher);
160        payee.hash(&mut hasher);
161        format!("imp-{:016x}", hasher.finish())
162    }
163}
164
165/// Status of a transaction for import preview
166#[derive(Debug, Clone, PartialEq, Eq)]
167pub enum ImportStatus {
168    /// Transaction will be imported
169    New,
170    /// Transaction is a duplicate and will be skipped
171    Duplicate,
172    /// Transaction has an error and cannot be imported
173    Error(String),
174}
175
176/// Preview entry for import review
177#[derive(Debug, Clone)]
178pub struct ImportPreviewEntry {
179    /// The parsed transaction
180    pub transaction: ParsedTransaction,
181    /// Import status
182    pub status: ImportStatus,
183    /// Matching existing transaction ID (for duplicates)
184    pub existing_id: Option<String>,
185}
186
187/// Result of a completed import
188#[derive(Debug, Clone)]
189pub struct ImportResult {
190    /// Number of transactions imported
191    pub imported: usize,
192    /// Number of duplicates skipped
193    pub duplicates_skipped: usize,
194    /// Number of rows with errors
195    pub errors: usize,
196    /// IDs of imported transactions
197    pub imported_ids: Vec<String>,
198    /// Error messages by row
199    pub error_messages: HashMap<usize, String>,
200}
201
202/// Service for CSV import
203pub struct ImportService<'a> {
204    storage: &'a Storage,
205}
206
207impl<'a> ImportService<'a> {
208    /// Create a new import service
209    pub fn new(storage: &'a Storage) -> Self {
210        Self { storage }
211    }
212
213    /// Parse a CSV from a reader into transactions
214    pub fn parse_csv_from_reader<R: std::io::Read>(
215        &self,
216        reader: &mut Reader<R>,
217        mapping: &ColumnMapping,
218    ) -> EnvelopeResult<Vec<Result<ParsedTransaction, String>>> {
219        let mut results = Vec::new();
220        for (idx, result) in reader.records().enumerate() {
221            let record = match result {
222                Ok(record) => record,
223                Err(e) => {
224                    results.push(Err(format!("Error reading CSV record: {}", e)));
225                    continue;
226                }
227            };
228            let result = self.parse_record(&record, idx, mapping);
229            results.push(result);
230        }
231        Ok(results)
232    }
233
234    /// Parse a single CSV record
235    fn parse_record(
236        &self,
237        record: &StringRecord,
238        row_number: usize,
239        mapping: &ColumnMapping,
240    ) -> Result<ParsedTransaction, String> {
241        // Parse date
242        let date_str = record
243            .get(mapping.date_column)
244            .ok_or_else(|| "Missing date column".to_string())?
245            .trim();
246
247        let date = self.parse_date(date_str, &mapping.date_format)?;
248
249        // Parse amount
250        let amount = self.parse_amount_from_record(record, mapping)?;
251
252        // Parse payee
253        let payee = mapping
254            .payee_column
255            .and_then(|col| record.get(col))
256            .map(|s| s.trim().to_string())
257            .unwrap_or_default();
258
259        // Parse memo
260        let memo = mapping
261            .memo_column
262            .and_then(|col| record.get(col))
263            .map(|s| s.trim().to_string())
264            .unwrap_or_default();
265
266        // Generate import ID
267        let import_id = ParsedTransaction::generate_import_id(date, amount, &payee);
268
269        Ok(ParsedTransaction {
270            date,
271            amount,
272            payee,
273            memo,
274            row_number,
275            import_id,
276        })
277    }
278
279    /// Parse amount from a record
280    fn parse_amount_from_record(
281        &self,
282        record: &StringRecord,
283        mapping: &ColumnMapping,
284    ) -> Result<Money, String> {
285        let amount = if let Some(amount_col) = mapping.amount_column {
286            // Single amount column
287            let amount_str = record
288                .get(amount_col)
289                .ok_or_else(|| "Missing amount column".to_string())?
290                .trim();
291
292            self.parse_amount_string(amount_str)?
293        } else {
294            // Separate inflow/outflow columns
295            let outflow_col = mapping
296                .outflow_column
297                .ok_or_else(|| "Missing outflow column configuration".to_string())?;
298            let inflow_col = mapping
299                .inflow_column
300                .ok_or_else(|| "Missing inflow column configuration".to_string())?;
301
302            let outflow_str = record.get(outflow_col).map(|s| s.trim()).unwrap_or("");
303            let inflow_str = record.get(inflow_col).map(|s| s.trim()).unwrap_or("");
304
305            let outflow = if outflow_str.is_empty() {
306                Money::zero()
307            } else {
308                -self.parse_amount_string(outflow_str)?.abs()
309            };
310
311            let inflow = if inflow_str.is_empty() {
312                Money::zero()
313            } else {
314                self.parse_amount_string(inflow_str)?.abs()
315            };
316
317            outflow + inflow
318        };
319
320        if mapping.invert_amounts {
321            Ok(-amount)
322        } else {
323            Ok(amount)
324        }
325    }
326
327    /// Parse a date string using multiple format attempts
328    fn parse_date(&self, s: &str, primary_format: &str) -> Result<NaiveDate, String> {
329        // Try primary format first
330        if let Ok(date) = NaiveDate::parse_from_str(s, primary_format) {
331            return Ok(date);
332        }
333
334        // Try common alternative formats
335        let formats = [
336            "%Y-%m-%d", "%m/%d/%Y", "%m/%d/%y", "%d/%m/%Y", "%d/%m/%y", "%Y/%m/%d", "%m-%d-%Y",
337            "%d-%m-%Y",
338        ];
339
340        for format in formats {
341            if let Ok(date) = NaiveDate::parse_from_str(s, format) {
342                return Ok(date);
343            }
344        }
345
346        Err(format!("Could not parse date: '{}'", s))
347    }
348
349    /// Detect column mapping from CSV header record
350    pub fn detect_mapping_from_headers(&self, headers: &StringRecord) -> ColumnMapping {
351        let mut mapping = ColumnMapping::new();
352
353        for (idx, header) in headers.iter().enumerate() {
354            let h = header.to_lowercase();
355            let h = h.trim();
356
357            if h.contains("date") || h.contains("posted") || h.contains("trans") {
358                mapping.date_column = idx;
359            } else if h.contains("amount") && mapping.amount_column.is_none() {
360                mapping.amount_column = Some(idx);
361            } else if h.contains("debit") || h.contains("outflow") || h.contains("withdrawal") {
362                mapping.outflow_column = Some(idx);
363            } else if h.contains("credit") || h.contains("inflow") || h.contains("deposit") {
364                mapping.inflow_column = Some(idx);
365            } else if h.contains("description")
366                || h.contains("payee")
367                || h.contains("merchant")
368                || h.contains("name")
369            {
370                mapping.payee_column = Some(idx);
371            } else if h.contains("memo") || h.contains("note") {
372                mapping.memo_column = Some(idx);
373            }
374        }
375
376        // If we have separate inflow/outflow, clear the amount column
377        if mapping.outflow_column.is_some() && mapping.inflow_column.is_some() {
378            mapping.amount_column = None;
379        }
380
381        mapping
382    }
383
384    /// Parse an amount string, handling various formats
385    fn parse_amount_string(&self, s: &str) -> Result<Money, String> {
386        // Remove currency symbols, commas, spaces
387        let cleaned: String = s
388            .chars()
389            .filter(|c| c.is_ascii_digit() || *c == '.' || *c == '-' || *c == '(' || *c == ')')
390            .collect();
391
392        // Handle parentheses as negative (accounting format)
393        let (is_negative, value) = if cleaned.starts_with('(') && cleaned.ends_with(')') {
394            (true, &cleaned[1..cleaned.len() - 1])
395        } else if let Some(stripped) = cleaned.strip_prefix('-') {
396            (true, stripped)
397        } else {
398            (false, cleaned.as_str())
399        };
400
401        Money::parse(value)
402            .map(|m| if is_negative { -m } else { m })
403            .map_err(|e| format!("Could not parse amount '{}': {}", s, e))
404    }
405
406    /// Generate an import preview, checking for duplicates
407    pub fn generate_preview(
408        &self,
409        parsed: &[Result<ParsedTransaction, String>],
410        account_id: AccountId,
411    ) -> EnvelopeResult<Vec<ImportPreviewEntry>> {
412        let mut preview = Vec::with_capacity(parsed.len());
413
414        // Get existing transactions for duplicate checking
415        let existing_txns = self.storage.transactions.get_by_account(account_id)?;
416        let existing_import_ids: HashMap<_, _> = existing_txns
417            .iter()
418            .filter_map(|t| {
419                t.import_id
420                    .as_ref()
421                    .map(|id| (id.clone(), t.id.to_string()))
422            })
423            .collect();
424
425        for result in parsed {
426            match result {
427                Ok(txn) => {
428                    let status = if let Some(_existing_id) = existing_import_ids.get(&txn.import_id)
429                    {
430                        ImportStatus::Duplicate
431                    } else {
432                        ImportStatus::New
433                    };
434
435                    let existing_id = existing_import_ids.get(&txn.import_id).cloned();
436
437                    preview.push(ImportPreviewEntry {
438                        transaction: txn.clone(),
439                        status,
440                        existing_id,
441                    });
442                }
443                Err(e) => {
444                    preview.push(ImportPreviewEntry {
445                        transaction: ParsedTransaction {
446                            date: NaiveDate::from_ymd_opt(1970, 1, 1).unwrap(),
447                            amount: Money::zero(),
448                            payee: String::new(),
449                            memo: String::new(),
450                            row_number: 0,
451                            import_id: String::new(),
452                        },
453                        status: ImportStatus::Error(e.clone()),
454                        existing_id: None,
455                    });
456                }
457            }
458        }
459
460        Ok(preview)
461    }
462
463    /// Import transactions from a preview
464    pub fn import_from_preview(
465        &self,
466        preview: &[ImportPreviewEntry],
467        account_id: AccountId,
468        default_category_id: Option<CategoryId>,
469        mark_cleared: bool,
470    ) -> EnvelopeResult<ImportResult> {
471        let txn_service = TransactionService::new(self.storage);
472
473        let mut result = ImportResult {
474            imported: 0,
475            duplicates_skipped: 0,
476            errors: 0,
477            imported_ids: Vec::new(),
478            error_messages: HashMap::new(),
479        };
480
481        for entry in preview {
482            match &entry.status {
483                ImportStatus::New => {
484                    let input = crate::services::CreateTransactionInput {
485                        account_id,
486                        date: entry.transaction.date,
487                        amount: entry.transaction.amount,
488                        payee_name: Some(entry.transaction.payee.clone()),
489                        category_id: default_category_id,
490                        memo: Some(entry.transaction.memo.clone()),
491                        status: if mark_cleared {
492                            Some(TransactionStatus::Cleared)
493                        } else {
494                            None
495                        },
496                    };
497
498                    match txn_service.create(input) {
499                        Ok(mut txn) => {
500                            // Set the import ID for duplicate detection
501                            txn.import_id = Some(entry.transaction.import_id.clone());
502                            self.storage.transactions.upsert(txn.clone())?;
503                            result.imported += 1;
504                            result.imported_ids.push(txn.id.to_string());
505                        }
506                        Err(e) => {
507                            result.errors += 1;
508                            result
509                                .error_messages
510                                .insert(entry.transaction.row_number, e.to_string());
511                        }
512                    }
513                }
514                ImportStatus::Duplicate => {
515                    result.duplicates_skipped += 1;
516                }
517                ImportStatus::Error(e) => {
518                    result.errors += 1;
519                    result
520                        .error_messages
521                        .insert(entry.transaction.row_number, e.clone());
522                }
523            }
524        }
525
526        // Save all transactions
527        self.storage.transactions.save()?;
528
529        Ok(result)
530    }
531}
532
533#[cfg(test)]
534mod tests {
535    use super::*;
536    use crate::config::paths::EnvelopePaths;
537    use crate::models::{Account, AccountType};
538    use tempfile::TempDir;
539
540    fn create_test_storage() -> (TempDir, Storage) {
541        let temp_dir = TempDir::new().unwrap();
542        let paths = EnvelopePaths::with_base_dir(temp_dir.path().to_path_buf());
543        let mut storage = Storage::new(paths).unwrap();
544        storage.load_all().unwrap();
545        (temp_dir, storage)
546    }
547
548    fn setup_test_account(storage: &Storage) -> AccountId {
549        let account = Account::new("Test Account", AccountType::Checking);
550        let account_id = account.id;
551        storage.accounts.upsert(account).unwrap();
552        storage.accounts.save().unwrap();
553        account_id
554    }
555
556    #[test]
557    fn test_parse_simple_csv() {
558        let (_temp_dir, storage) = create_test_storage();
559        let service = ImportService::new(&storage);
560
561        let csv_data =
562            "Date,Amount,Description\n2025-01-15,-50.00,Test Store\n2025-01-16,100.00,Paycheck";
563        let mapping = ColumnMapping::new();
564        let mut reader = csv::Reader::from_reader(csv_data.as_bytes());
565
566        let results = service
567            .parse_csv_from_reader(&mut reader, &mapping)
568            .unwrap();
569        assert_eq!(results.len(), 2);
570
571        let txn1 = results[0].as_ref().unwrap();
572        assert_eq!(txn1.date, NaiveDate::from_ymd_opt(2025, 1, 15).unwrap());
573        assert_eq!(txn1.amount.cents(), -5000);
574        assert_eq!(txn1.payee, "Test Store");
575
576        let txn2 = results[1].as_ref().unwrap();
577        assert_eq!(txn2.date, NaiveDate::from_ymd_opt(2025, 1, 16).unwrap());
578        assert_eq!(txn2.amount.cents(), 10000);
579    }
580
581    #[test]
582    fn test_parse_separate_inflow_outflow() {
583        let (_temp_dir, storage) = create_test_storage();
584        let service = ImportService::new(&storage);
585
586        let csv_data = "Date,Outflow,Inflow,Description\n2025-01-15,50.00,,Groceries\n2025-01-16,,100.00,Paycheck";
587        let mapping = ColumnMapping::separate_inout(0, 1, 2, 3);
588        let mut reader = csv::Reader::from_reader(csv_data.as_bytes());
589
590        let results = service
591            .parse_csv_from_reader(&mut reader, &mapping)
592            .unwrap();
593        assert_eq!(results.len(), 2);
594
595        let txn1 = results[0].as_ref().unwrap();
596        assert_eq!(txn1.amount.cents(), -5000);
597
598        let txn2 = results[1].as_ref().unwrap();
599        assert_eq!(txn2.amount.cents(), 10000);
600    }
601
602    #[test]
603    fn test_parse_various_date_formats() {
604        let (_temp_dir, storage) = create_test_storage();
605        let service = ImportService::new(&storage);
606
607        // MM/DD/YYYY format
608        let csv_data = "Date,Amount,Description\n01/15/2025,-50.00,Test";
609        let mapping = ColumnMapping::new().with_date_format("%m/%d/%Y");
610        let mut reader = csv::Reader::from_reader(csv_data.as_bytes());
611        let results = service
612            .parse_csv_from_reader(&mut reader, &mapping)
613            .unwrap();
614        assert_eq!(
615            results[0].as_ref().unwrap().date,
616            NaiveDate::from_ymd_opt(2025, 1, 15).unwrap()
617        );
618    }
619
620    #[test]
621    fn test_parse_accounting_negative_format() {
622        let (_temp_dir, storage) = create_test_storage();
623        let service = ImportService::new(&storage);
624
625        let csv_data = "Date,Amount,Description\n2025-01-15,(50.00),Test";
626        let mapping = ColumnMapping::new();
627        let mut reader = csv::Reader::from_reader(csv_data.as_bytes());
628
629        let results = service
630            .parse_csv_from_reader(&mut reader, &mapping)
631            .unwrap();
632        let txn = results[0].as_ref().unwrap();
633        assert_eq!(txn.amount.cents(), -5000);
634    }
635
636    #[test]
637    fn test_duplicate_detection() {
638        let (_temp_dir, storage) = create_test_storage();
639        let account_id = setup_test_account(&storage);
640        let service = ImportService::new(&storage);
641
642        // First import
643        let csv_data = "Date,Amount,Description\n2025-01-15,-50.00,Test Store";
644        let mapping = ColumnMapping::new();
645        let mut reader = csv::Reader::from_reader(csv_data.as_bytes());
646        let parsed = service
647            .parse_csv_from_reader(&mut reader, &mapping)
648            .unwrap();
649
650        let preview1 = service.generate_preview(&parsed, account_id).unwrap();
651        assert_eq!(preview1[0].status, ImportStatus::New);
652
653        // Import it
654        service
655            .import_from_preview(&preview1, account_id, None, false)
656            .unwrap();
657
658        // Try to import the same transaction again
659        let preview2 = service.generate_preview(&parsed, account_id).unwrap();
660        assert_eq!(preview2[0].status, ImportStatus::Duplicate);
661    }
662
663    #[test]
664    fn test_detect_mapping() {
665        let (_temp_dir, storage) = create_test_storage();
666        let service = ImportService::new(&storage);
667
668        let header_str = "Transaction Date,Debit,Credit,Description,Notes";
669        let mut reader = csv::ReaderBuilder::new()
670            .has_headers(false)
671            .from_reader(header_str.as_bytes());
672        let headers = reader.headers().unwrap().clone();
673        let mapping = service.detect_mapping_from_headers(&headers);
674
675        assert_eq!(mapping.date_column, 0);
676        assert_eq!(mapping.outflow_column, Some(1));
677        assert_eq!(mapping.inflow_column, Some(2));
678        assert_eq!(mapping.payee_column, Some(3));
679        assert_eq!(mapping.memo_column, Some(4));
680        assert!(mapping.amount_column.is_none());
681    }
682
683    #[test]
684    fn test_import_result() {
685        let (_temp_dir, storage) = create_test_storage();
686        let account_id = setup_test_account(&storage);
687        let service = ImportService::new(&storage);
688
689        let csv_data =
690            "Date,Amount,Description\n2025-01-15,-50.00,Store 1\n2025-01-16,-25.00,Store 2";
691        let mapping = ColumnMapping::new();
692        let mut reader = csv::Reader::from_reader(csv_data.as_bytes());
693        let parsed = service
694            .parse_csv_from_reader(&mut reader, &mapping)
695            .unwrap();
696        let preview = service.generate_preview(&parsed, account_id).unwrap();
697
698        let result = service
699            .import_from_preview(&preview, account_id, None, false)
700            .unwrap();
701
702        assert_eq!(result.imported, 2);
703        assert_eq!(result.duplicates_skipped, 0);
704        assert_eq!(result.errors, 0);
705        assert_eq!(result.imported_ids.len(), 2);
706    }
707}
envelope_cli/services/import.rs

envelope_cli/services/
import.rs
