backend/ingest/ingest_invoice_sqlite.py

"""
Invoice ingestion helper for SQLite.
Handles insert/update with computed fields.
"""

import sqlite3
from datetime import datetime
from pathlib import Path
from typing import Dict, Optional
from filelock import FileLock

DB_PATH = Path(__file__).parent.parent.parent / "data" / "invoices.db"
LOCK_PATH = Path(__file__).parent.parent.parent / "data" / "invoices.db.lock"

def parse_date(date_input) -> Optional[str]:
    """Convert various date formats to ISO string."""
    if not date_input:
        return None
    
    if isinstance(date_input, str):
        # Try parsing common formats
        for fmt in ["%Y-%m-%d %H:%M:%S", "%Y-%m-%d", "%Y%m%d"]:
            try:
                dt = datetime.strptime(date_input, fmt)
                return dt.strftime("%Y-%m-%d %H:%M:%S")
            except ValueError:
                continue
        return date_input  # Return as-is if parsing fails
    
    if isinstance(date_input, datetime):
        return date_input.strftime("%Y-%m-%d %H:%M:%S")
    
    return str(date_input)


def compute_days_diff(date1_str: Optional[str], date2_str: Optional[str]) -> Optional[int]:
    """Compute day difference between two ISO date strings."""
    if not date1_str or not date2_str:
        return None
    
    try:
        d1 = datetime.strptime(date1_str, "%Y-%m-%d %H:%M:%S")
        d2 = datetime.strptime(date2_str, "%Y-%m-%d %H:%M:%S")
        return (d1 - d2).days
    except:
        return None


def ingest_invoice(invoice_data: Dict) -> Dict:
    """
    Insert or update invoice in SQLite with computed fields.
    
    Args:
        invoice_data: Dict with invoice fields
        
    Returns:
        Dict with status and invoice_id
    """
    
    # Parse dates
    posting_date = parse_date(invoice_data.get("posting_date"))
    clear_date = parse_date(invoice_data.get("clear_date"))
    due_in_date = parse_date(invoice_data.get("due_in_date"))
    document_create_date = parse_date(invoice_data.get("document_create_date"))
    baseline_create_date = parse_date(invoice_data.get("baseline_create_date"))
    
    # Compute derived fields
    days_to_clear = compute_days_diff(clear_date, posting_date) if clear_date else None
    days_posting_to_due = compute_days_diff(due_in_date, posting_date)
    days_create_to_posting = compute_days_diff(posting_date, document_create_date)
    days_baseline_to_posting = compute_days_diff(posting_date, baseline_create_date)
    
    is_open = 0 if clear_date else 1
    is_overdue = 0
    if clear_date and due_in_date:
        try:
            cd = datetime.strptime(clear_date, "%Y-%m-%d %H:%M:%S")
            dd = datetime.strptime(due_in_date, "%Y-%m-%d %H:%M:%S")
            is_overdue = 1 if cd > dd else 0
        except:
            pass
    
    # Prepare data
    invoice_id = invoice_data.get("invoice_id")
    if not invoice_id:
        raise ValueError("invoice_id is required")
    
    # SQLite write with lock
    with FileLock(str(LOCK_PATH)):
        conn = sqlite3.connect(str(DB_PATH))
        cursor = conn.cursor()
        
        cursor.execute("""
            INSERT OR REPLACE INTO invoices_history (
                invoice_id, business_code, cust_number, name_customer,
                posting_date, document_create_date, document_create_date_alt,
                due_in_date, baseline_create_date, clear_date,
                total_open_amount, invoice_currency, document_type,
                cust_payment_terms, posting_id, business_year,
                days_to_clear, days_posting_to_due, days_create_to_posting,
                days_baseline_to_posting, is_overdue, is_open,
                updated_at
            ) VALUES (
                ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
                ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP
            )
        """, (
            invoice_id,
            invoice_data.get("business_code"),
            invoice_data.get("cust_number"),
            invoice_data.get("name_customer"),
            posting_date,
            document_create_date,
            invoice_data.get("document_create_date_alt"),
            due_in_date,
            baseline_create_date,
            clear_date,
            invoice_data.get("total_open_amount"),
            invoice_data.get("invoice_currency", "USD"),
            invoice_data.get("document_type"),
            invoice_data.get("cust_payment_terms"),
            invoice_data.get("posting_id"),
            invoice_data.get("business_year"),
            days_to_clear,
            days_posting_to_due,
            days_create_to_posting,
            days_baseline_to_posting,
            is_overdue,
            is_open
        ))
        
        conn.commit()
        conn.close()
    
    return {
        "status": "success",
        "invoice_id": invoice_id,
        "is_open": bool(is_open),
        "days_to_clear": days_to_clear
    }


if __name__ == "__main__":
    # Test
    test_invoice = {
        "invoice_id": 12345,
        "business_code": "U001",
        "cust_number": "0200769623",
        "name_customer": "Test Customer",
        "posting_date": "2024-01-15",
        "clear_date": "2024-02-01",
        "due_in_date": "2024-01-30",
        "total_open_amount": 50000.0,
        "cust_payment_terms": "NAH4"
    }
    
    result = ingest_invoice(test_invoice)
    print(result)