1use 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#[derive(Debug, Clone)]
18pub struct ColumnMapping {
19 pub date_column: usize,
21 pub amount_column: Option<usize>,
23 pub outflow_column: Option<usize>,
25 pub inflow_column: Option<usize>,
27 pub payee_column: Option<usize>,
29 pub memo_column: Option<usize>,
31 pub date_format: String,
33 pub has_header: bool,
35 pub delimiter: char,
37 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 pub fn new() -> Self {
61 Self::default()
62 }
63
64 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 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, }
94 }
95
96 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 pub fn td_bank() -> Self {
119 Self {
120 date_column: 0,
121 amount_column: None,
122 outflow_column: Some(2),
123 inflow_column: Some(3),
124 payee_column: Some(1),
125 memo_column: None,
126 date_format: "%Y-%m-%d".to_string(),
127 has_header: false,
128 delimiter: ',',
129 invert_amounts: false,
130 }
131 }
132
133 pub fn with_date_format(mut self, format: &str) -> Self {
135 self.date_format = format.to_string();
136 self
137 }
138
139 pub fn with_header(mut self, has_header: bool) -> Self {
141 self.has_header = has_header;
142 self
143 }
144
145 pub fn with_delimiter(mut self, delimiter: char) -> Self {
147 self.delimiter = delimiter;
148 self
149 }
150}
151
152#[derive(Debug, Clone)]
154pub struct ParsedTransaction {
155 pub date: NaiveDate,
157 pub amount: Money,
159 pub payee: String,
161 pub memo: String,
163 pub row_number: usize,
165 pub import_id: String,
167}
168
169impl ParsedTransaction {
170 pub fn generate_import_id(date: NaiveDate, amount: Money, payee: &str) -> String {
172 use std::hash::{Hash, Hasher};
173 let mut hasher = std::collections::hash_map::DefaultHasher::new();
174 date.hash(&mut hasher);
175 amount.cents().hash(&mut hasher);
176 payee.hash(&mut hasher);
177 format!("imp-{:016x}", hasher.finish())
178 }
179}
180
181#[derive(Debug, Clone, PartialEq, Eq)]
183pub enum ImportStatus {
184 New,
186 Duplicate,
188 Error(String),
190}
191
192#[derive(Debug, Clone)]
194pub struct ImportPreviewEntry {
195 pub transaction: ParsedTransaction,
197 pub status: ImportStatus,
199 pub existing_id: Option<String>,
201}
202
203#[derive(Debug, Clone)]
205pub struct ImportResult {
206 pub imported: usize,
208 pub duplicates_skipped: usize,
210 pub errors: usize,
212 pub imported_ids: Vec<String>,
214 pub error_messages: HashMap<usize, String>,
216}
217
218pub struct ImportService<'a> {
220 storage: &'a Storage,
221}
222
223impl<'a> ImportService<'a> {
224 pub fn new(storage: &'a Storage) -> Self {
226 Self { storage }
227 }
228
229 pub fn parse_csv_from_reader<R: std::io::Read>(
231 &self,
232 reader: &mut Reader<R>,
233 mapping: &ColumnMapping,
234 ) -> EnvelopeResult<Vec<Result<ParsedTransaction, String>>> {
235 let mut results = Vec::new();
236 for (idx, result) in reader.records().enumerate() {
237 let record = match result {
238 Ok(record) => record,
239 Err(e) => {
240 results.push(Err(format!("Error reading CSV record: {}", e)));
241 continue;
242 }
243 };
244 let result = self.parse_record(&record, idx, mapping);
245 results.push(result);
246 }
247 Ok(results)
248 }
249
250 fn parse_record(
252 &self,
253 record: &StringRecord,
254 row_number: usize,
255 mapping: &ColumnMapping,
256 ) -> Result<ParsedTransaction, String> {
257 let date_str = record
259 .get(mapping.date_column)
260 .ok_or_else(|| "Missing date column".to_string())?
261 .trim();
262
263 let date = self.parse_date(date_str, &mapping.date_format)?;
264
265 let amount = self.parse_amount_from_record(record, mapping)?;
267
268 let payee = mapping
270 .payee_column
271 .and_then(|col| record.get(col))
272 .map(|s| s.trim().to_string())
273 .unwrap_or_default();
274
275 let memo = mapping
277 .memo_column
278 .and_then(|col| record.get(col))
279 .map(|s| s.trim().to_string())
280 .unwrap_or_default();
281
282 let import_id = ParsedTransaction::generate_import_id(date, amount, &payee);
284
285 Ok(ParsedTransaction {
286 date,
287 amount,
288 payee,
289 memo,
290 row_number,
291 import_id,
292 })
293 }
294
295 fn parse_amount_from_record(
297 &self,
298 record: &StringRecord,
299 mapping: &ColumnMapping,
300 ) -> Result<Money, String> {
301 let amount = if let Some(amount_col) = mapping.amount_column {
302 let amount_str = record
304 .get(amount_col)
305 .ok_or_else(|| "Missing amount column".to_string())?
306 .trim();
307
308 self.parse_amount_string(amount_str)?
309 } else {
310 let outflow_col = mapping
312 .outflow_column
313 .ok_or_else(|| "Missing outflow column configuration".to_string())?;
314 let inflow_col = mapping
315 .inflow_column
316 .ok_or_else(|| "Missing inflow column configuration".to_string())?;
317
318 let outflow_str = record.get(outflow_col).map(|s| s.trim()).unwrap_or("");
319 let inflow_str = record.get(inflow_col).map(|s| s.trim()).unwrap_or("");
320
321 let outflow = if outflow_str.is_empty() {
322 Money::zero()
323 } else {
324 -self.parse_amount_string(outflow_str)?.abs()
325 };
326
327 let inflow = if inflow_str.is_empty() {
328 Money::zero()
329 } else {
330 self.parse_amount_string(inflow_str)?.abs()
331 };
332
333 outflow + inflow
334 };
335
336 if mapping.invert_amounts {
337 Ok(-amount)
338 } else {
339 Ok(amount)
340 }
341 }
342
343 fn parse_date(&self, s: &str, primary_format: &str) -> Result<NaiveDate, String> {
345 if let Ok(date) = NaiveDate::parse_from_str(s, primary_format) {
347 return Ok(date);
348 }
349
350 let formats = [
352 "%Y-%m-%d", "%m/%d/%Y", "%m/%d/%y", "%d/%m/%Y", "%d/%m/%y", "%Y/%m/%d", "%m-%d-%Y",
353 "%d-%m-%Y",
354 ];
355
356 for format in formats {
357 if let Ok(date) = NaiveDate::parse_from_str(s, format) {
358 return Ok(date);
359 }
360 }
361
362 Err(format!("Could not parse date: '{}'", s))
363 }
364
365 fn looks_like_data_row(&self, record: &StringRecord) -> bool {
368 if let Some(first) = record.get(0) {
369 let first = first.trim();
370 let date_formats = ["%Y-%m-%d", "%m/%d/%Y", "%m/%d/%y", "%d/%m/%Y", "%d/%m/%y"];
372 for format in date_formats {
373 if NaiveDate::parse_from_str(first, format).is_ok() {
374 return true;
375 }
376 }
377 }
378 false
379 }
380
381 pub fn detect_mapping_from_headers(&self, headers: &StringRecord) -> ColumnMapping {
383 if self.looks_like_data_row(headers) {
385 if headers.len() >= 4 {
388 let col2 = headers.get(2).map(|s| s.trim()).unwrap_or("");
390 let col3 = headers.get(3).map(|s| s.trim()).unwrap_or("");
391 let col2_is_num = col2.is_empty() || col2.parse::<f64>().is_ok();
392 let col3_is_num = col3.is_empty() || col3.parse::<f64>().is_ok();
393
394 if col2_is_num && col3_is_num {
395 return ColumnMapping::td_bank();
396 }
397 }
398 }
399
400 let mut mapping = ColumnMapping::new();
401
402 for (idx, header) in headers.iter().enumerate() {
403 let h = header.to_lowercase();
404 let h = h.trim();
405
406 if h.contains("date") || h.contains("posted") || h.contains("trans") {
407 mapping.date_column = idx;
408 } else if h.contains("amount") && mapping.amount_column.is_none() {
409 mapping.amount_column = Some(idx);
410 } else if h.contains("debit") || h.contains("outflow") || h.contains("withdrawal") {
411 mapping.outflow_column = Some(idx);
412 } else if h.contains("credit") || h.contains("inflow") || h.contains("deposit") {
413 mapping.inflow_column = Some(idx);
414 } else if h.contains("description")
415 || h.contains("payee")
416 || h.contains("merchant")
417 || h.contains("name")
418 {
419 mapping.payee_column = Some(idx);
420 } else if h.contains("memo") || h.contains("note") {
421 mapping.memo_column = Some(idx);
422 }
423 }
424
425 if mapping.outflow_column.is_some() && mapping.inflow_column.is_some() {
427 mapping.amount_column = None;
428 }
429
430 mapping
431 }
432
433 fn parse_amount_string(&self, s: &str) -> Result<Money, String> {
435 let cleaned: String = s
437 .chars()
438 .filter(|c| c.is_ascii_digit() || *c == '.' || *c == '-' || *c == '(' || *c == ')')
439 .collect();
440
441 let (is_negative, value) = if cleaned.starts_with('(') && cleaned.ends_with(')') {
443 (true, &cleaned[1..cleaned.len() - 1])
444 } else if let Some(stripped) = cleaned.strip_prefix('-') {
445 (true, stripped)
446 } else {
447 (false, cleaned.as_str())
448 };
449
450 Money::parse(value)
451 .map(|m| if is_negative { -m } else { m })
452 .map_err(|e| format!("Could not parse amount '{}': {}", s, e))
453 }
454
455 pub fn generate_preview(
457 &self,
458 parsed: &[Result<ParsedTransaction, String>],
459 account_id: AccountId,
460 ) -> EnvelopeResult<Vec<ImportPreviewEntry>> {
461 let mut preview = Vec::with_capacity(parsed.len());
462
463 let existing_txns = self.storage.transactions.get_by_account(account_id)?;
465 let existing_import_ids: HashMap<_, _> = existing_txns
466 .iter()
467 .filter_map(|t| {
468 t.import_id
469 .as_ref()
470 .map(|id| (id.clone(), t.id.to_string()))
471 })
472 .collect();
473
474 for result in parsed {
475 match result {
476 Ok(txn) => {
477 let status = if let Some(_existing_id) = existing_import_ids.get(&txn.import_id)
478 {
479 ImportStatus::Duplicate
480 } else {
481 ImportStatus::New
482 };
483
484 let existing_id = existing_import_ids.get(&txn.import_id).cloned();
485
486 preview.push(ImportPreviewEntry {
487 transaction: txn.clone(),
488 status,
489 existing_id,
490 });
491 }
492 Err(e) => {
493 preview.push(ImportPreviewEntry {
494 transaction: ParsedTransaction {
495 date: NaiveDate::from_ymd_opt(1970, 1, 1).unwrap(),
496 amount: Money::zero(),
497 payee: String::new(),
498 memo: String::new(),
499 row_number: 0,
500 import_id: String::new(),
501 },
502 status: ImportStatus::Error(e.clone()),
503 existing_id: None,
504 });
505 }
506 }
507 }
508
509 Ok(preview)
510 }
511
512 pub fn import_from_preview(
514 &self,
515 preview: &[ImportPreviewEntry],
516 account_id: AccountId,
517 default_category_id: Option<CategoryId>,
518 mark_cleared: bool,
519 ) -> EnvelopeResult<ImportResult> {
520 let txn_service = TransactionService::new(self.storage);
521
522 let mut result = ImportResult {
523 imported: 0,
524 duplicates_skipped: 0,
525 errors: 0,
526 imported_ids: Vec::new(),
527 error_messages: HashMap::new(),
528 };
529
530 for entry in preview {
531 match &entry.status {
532 ImportStatus::New => {
533 let input = crate::services::CreateTransactionInput {
534 account_id,
535 date: entry.transaction.date,
536 amount: entry.transaction.amount,
537 payee_name: Some(entry.transaction.payee.clone()),
538 category_id: default_category_id,
539 memo: Some(entry.transaction.memo.clone()),
540 status: if mark_cleared {
541 Some(TransactionStatus::Cleared)
542 } else {
543 None
544 },
545 };
546
547 match txn_service.create(input) {
548 Ok(mut txn) => {
549 txn.import_id = Some(entry.transaction.import_id.clone());
551 self.storage.transactions.upsert(txn.clone())?;
552 result.imported += 1;
553 result.imported_ids.push(txn.id.to_string());
554 }
555 Err(e) => {
556 result.errors += 1;
557 result
558 .error_messages
559 .insert(entry.transaction.row_number, e.to_string());
560 }
561 }
562 }
563 ImportStatus::Duplicate => {
564 result.duplicates_skipped += 1;
565 }
566 ImportStatus::Error(e) => {
567 result.errors += 1;
568 result
569 .error_messages
570 .insert(entry.transaction.row_number, e.clone());
571 }
572 }
573 }
574
575 self.storage.transactions.save()?;
577
578 Ok(result)
579 }
580}
581
582#[cfg(test)]
583mod tests {
584 use super::*;
585 use crate::config::paths::EnvelopePaths;
586 use crate::models::{Account, AccountType};
587 use tempfile::TempDir;
588
589 fn create_test_storage() -> (TempDir, Storage) {
590 let temp_dir = TempDir::new().unwrap();
591 let paths = EnvelopePaths::with_base_dir(temp_dir.path().to_path_buf());
592 let mut storage = Storage::new(paths).unwrap();
593 storage.load_all().unwrap();
594 (temp_dir, storage)
595 }
596
597 fn setup_test_account(storage: &Storage) -> AccountId {
598 let account = Account::new("Test Account", AccountType::Checking);
599 let account_id = account.id;
600 storage.accounts.upsert(account).unwrap();
601 storage.accounts.save().unwrap();
602 account_id
603 }
604
605 #[test]
606 fn test_parse_simple_csv() {
607 let (_temp_dir, storage) = create_test_storage();
608 let service = ImportService::new(&storage);
609
610 let csv_data =
611 "Date,Amount,Description\n2025-01-15,-50.00,Test Store\n2025-01-16,100.00,Paycheck";
612 let mapping = ColumnMapping::new();
613 let mut reader = csv::Reader::from_reader(csv_data.as_bytes());
614
615 let results = service
616 .parse_csv_from_reader(&mut reader, &mapping)
617 .unwrap();
618 assert_eq!(results.len(), 2);
619
620 let txn1 = results[0].as_ref().unwrap();
621 assert_eq!(txn1.date, NaiveDate::from_ymd_opt(2025, 1, 15).unwrap());
622 assert_eq!(txn1.amount.cents(), -5000);
623 assert_eq!(txn1.payee, "Test Store");
624
625 let txn2 = results[1].as_ref().unwrap();
626 assert_eq!(txn2.date, NaiveDate::from_ymd_opt(2025, 1, 16).unwrap());
627 assert_eq!(txn2.amount.cents(), 10000);
628 }
629
630 #[test]
631 fn test_parse_separate_inflow_outflow() {
632 let (_temp_dir, storage) = create_test_storage();
633 let service = ImportService::new(&storage);
634
635 let csv_data = "Date,Outflow,Inflow,Description\n2025-01-15,50.00,,Groceries\n2025-01-16,,100.00,Paycheck";
636 let mapping = ColumnMapping::separate_inout(0, 1, 2, 3);
637 let mut reader = csv::Reader::from_reader(csv_data.as_bytes());
638
639 let results = service
640 .parse_csv_from_reader(&mut reader, &mapping)
641 .unwrap();
642 assert_eq!(results.len(), 2);
643
644 let txn1 = results[0].as_ref().unwrap();
645 assert_eq!(txn1.amount.cents(), -5000);
646
647 let txn2 = results[1].as_ref().unwrap();
648 assert_eq!(txn2.amount.cents(), 10000);
649 }
650
651 #[test]
652 fn test_parse_various_date_formats() {
653 let (_temp_dir, storage) = create_test_storage();
654 let service = ImportService::new(&storage);
655
656 let csv_data = "Date,Amount,Description\n01/15/2025,-50.00,Test";
658 let mapping = ColumnMapping::new().with_date_format("%m/%d/%Y");
659 let mut reader = csv::Reader::from_reader(csv_data.as_bytes());
660 let results = service
661 .parse_csv_from_reader(&mut reader, &mapping)
662 .unwrap();
663 assert_eq!(
664 results[0].as_ref().unwrap().date,
665 NaiveDate::from_ymd_opt(2025, 1, 15).unwrap()
666 );
667 }
668
669 #[test]
670 fn test_parse_accounting_negative_format() {
671 let (_temp_dir, storage) = create_test_storage();
672 let service = ImportService::new(&storage);
673
674 let csv_data = "Date,Amount,Description\n2025-01-15,(50.00),Test";
675 let mapping = ColumnMapping::new();
676 let mut reader = csv::Reader::from_reader(csv_data.as_bytes());
677
678 let results = service
679 .parse_csv_from_reader(&mut reader, &mapping)
680 .unwrap();
681 let txn = results[0].as_ref().unwrap();
682 assert_eq!(txn.amount.cents(), -5000);
683 }
684
685 #[test]
686 fn test_duplicate_detection() {
687 let (_temp_dir, storage) = create_test_storage();
688 let account_id = setup_test_account(&storage);
689 let service = ImportService::new(&storage);
690
691 let csv_data = "Date,Amount,Description\n2025-01-15,-50.00,Test Store";
693 let mapping = ColumnMapping::new();
694 let mut reader = csv::Reader::from_reader(csv_data.as_bytes());
695 let parsed = service
696 .parse_csv_from_reader(&mut reader, &mapping)
697 .unwrap();
698
699 let preview1 = service.generate_preview(&parsed, account_id).unwrap();
700 assert_eq!(preview1[0].status, ImportStatus::New);
701
702 service
704 .import_from_preview(&preview1, account_id, None, false)
705 .unwrap();
706
707 let preview2 = service.generate_preview(&parsed, account_id).unwrap();
709 assert_eq!(preview2[0].status, ImportStatus::Duplicate);
710 }
711
712 #[test]
713 fn test_detect_mapping() {
714 let (_temp_dir, storage) = create_test_storage();
715 let service = ImportService::new(&storage);
716
717 let header_str = "Transaction Date,Debit,Credit,Description,Notes";
718 let mut reader = csv::ReaderBuilder::new()
719 .has_headers(false)
720 .from_reader(header_str.as_bytes());
721 let headers = reader.headers().unwrap().clone();
722 let mapping = service.detect_mapping_from_headers(&headers);
723
724 assert_eq!(mapping.date_column, 0);
725 assert_eq!(mapping.outflow_column, Some(1));
726 assert_eq!(mapping.inflow_column, Some(2));
727 assert_eq!(mapping.payee_column, Some(3));
728 assert_eq!(mapping.memo_column, Some(4));
729 assert!(mapping.amount_column.is_none());
730 }
731
732 #[test]
733 fn test_import_result() {
734 let (_temp_dir, storage) = create_test_storage();
735 let account_id = setup_test_account(&storage);
736 let service = ImportService::new(&storage);
737
738 let csv_data =
739 "Date,Amount,Description\n2025-01-15,-50.00,Store 1\n2025-01-16,-25.00,Store 2";
740 let mapping = ColumnMapping::new();
741 let mut reader = csv::Reader::from_reader(csv_data.as_bytes());
742 let parsed = service
743 .parse_csv_from_reader(&mut reader, &mapping)
744 .unwrap();
745 let preview = service.generate_preview(&parsed, account_id).unwrap();
746
747 let result = service
748 .import_from_preview(&preview, account_id, None, false)
749 .unwrap();
750
751 assert_eq!(result.imported, 2);
752 assert_eq!(result.duplicates_skipped, 0);
753 assert_eq!(result.errors, 0);
754 assert_eq!(result.imported_ids.len(), 2);
755 }
756}