import logging from typing import Optional, List import pandas as pd from invoice_schema import invoice_pipeline_schema from schemas import DatasetSchema from validator import ColumnValidator logger = logging.getLogger(__name__) CANONICAL_COLUMNS: List[str] = [ "id", "document_type", "document_number", "reference_number", "po_number", "invoice_date", "due_date", "total_billed", "amount_outstanding", "discount", "credit_advances_applied", "payment_amount", "ar_status", "open_balance", ] def _parse_date(series: pd.Series, col_name: str, dataset_name: str) -> pd.Series: parsed = pd.to_datetime(series, errors="coerce") invalid_count = parsed.isna().sum() if invalid_count > 0: logger.warning( "Found %d invalid dates in column '%s' for dataset '%s'.", invalid_count, col_name, dataset_name ) return parsed def _parse_currency(series: pd.Series, col_name: str, dataset_name: str) -> pd.Series: cleaned = ( series.astype("string") .str.replace(r"[\$,]", "", regex=True) .str.strip() ) parsed = pd.to_numeric(cleaned, errors="coerce") invalid_count = parsed.isna().sum() if invalid_count > 0: logger.warning( "Found %d invalid currency values in column '%s' for dataset '%s'.", invalid_count, col_name, dataset_name ) return parsed def normalize_open_invoices( df: pd.DataFrame, *, schema: Optional[DatasetSchema] = None, dataset_name: str = "Open Invoices", validate_positive_amounts: bool = True, ) -> pd.DataFrame: logger.info("Normalizing dataset '%s' (open invoices).", dataset_name) before = len(df) df = df.dropna(how="all").copy() dropped = before - len(df) if dropped > 0: logger.debug("Dropped %d fully empty rows from '%s'.", dropped, dataset_name) df["id"] = range(1, len(df) + 1) invoice_date = _parse_date(df["Date"], "Date", dataset_name) due_date = _parse_date(df["Due date"], "Due date", dataset_name) total_billed = pd.to_numeric(df["Amount"], errors="coerce") open_balance = pd.to_numeric(df["Open balance"], errors="coerce") if validate_positive_amounts: if (total_billed < 0).any(): logger.warning("Negative 'Amount' values found in '%s'.", dataset_name) if (open_balance < 0).any(): logger.warning("Negative 'Open balance' values found in '%s'.", dataset_name) out = pd.DataFrame({ "id": df["id"], "document_type": df["Transaction type"].astype("string"), "document_number": df["Num"].astype("string"), "reference_number": pd.NA, "po_number": df["P.O. Number"].astype("string"), "invoice_date": invoice_date, "due_date": due_date, "total_billed": total_billed, "amount_outstanding": open_balance, "discount": pd.NA, "credit_advances_applied": pd.NA, "payment_amount": pd.NA, "ar_status": df["A/R paid"].astype("string"), "open_balance": open_balance, }) out = out[CANONICAL_COLUMNS] schema_to_use = schema or invoice_pipeline_schema validator = ColumnValidator(schema_to_use) validator.validate(out, dataset_name=f"{dataset_name} (normalized)") return out def normalize_payment_notification( df: pd.DataFrame, *, schema: Optional[DatasetSchema] = None, dataset_name: str = "Payment Notification", validate_positive_amounts: bool = True, ) -> pd.DataFrame: logger.info("Normalizing dataset '%s' (payment notification).", dataset_name) df = df.dropna(how="all").copy() df["id"] = range(1, len(df) + 1) invoice_date = _parse_date(df["Date"], "Date", dataset_name) total_billed = _parse_currency(df["Total billed"], "Total billed", dataset_name) amount_outstanding = _parse_currency(df["Amount outstanding"], "Amount outstanding", dataset_name) discount = _parse_currency(df["Discount"], "Discount", dataset_name) credit_adv = _parse_currency(df["Credit/advances applied"], "Credit/advances applied", dataset_name) payment_amount = _parse_currency(df["Payment amount"], "Payment amount", dataset_name) if validate_positive_amounts: for col_name, series in [ ("Total billed", total_billed), ("Amount outstanding", amount_outstanding), ("Discount", discount), ("Credit/advances applied", credit_adv), ("Payment amount", payment_amount), ]: if (series < 0).any(): logger.warning("Negative values found in '%s' for dataset '%s'.", col_name, dataset_name) out = pd.DataFrame({ "id": df["id"], "document_type": "Invoice", "document_number": df["Bill number"].astype("string"), "reference_number": df["Reference"].astype("string"), "po_number": pd.NA, "invoice_date": invoice_date, "due_date": pd.NA, "total_billed": total_billed, "amount_outstanding": amount_outstanding, "discount": discount, "credit_advances_applied": credit_adv, "payment_amount": payment_amount, "ar_status": pd.NA, "open_balance": amount_outstanding, }) out = out[CANONICAL_COLUMNS] schema_to_use = schema or invoice_pipeline_schema validator = ColumnValidator(schema_to_use) validator.validate(out, dataset_name=f"{dataset_name} (normalized)") return out