File size: 11,540 Bytes
ae77cb1
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
# src/storage.py
"""
Enhanced disk-based storage for the AR reconciliation system.
Supports manual matching, audit trails, and comprehensive analytics.
"""

from pathlib import Path
from typing import Tuple, List, Dict, Any
from datetime import datetime
import uuid
import pandas as pd

DATA_DIR = Path("data_store")
DATA_DIR.mkdir(exist_ok=True)

# Master tables
ALL_TRANSACTIONS_PATH = DATA_DIR / "all_transactions.parquet"
PAYMENT_NOTIFICATIONS_PATH = DATA_DIR / "payment_notifications.parquet"
MATCHES_PATH = DATA_DIR / "matches.parquet"


def load_all_transactions() -> pd.DataFrame:
    """Load All Transactions List - central database tab."""
    if ALL_TRANSACTIONS_PATH.exists():
        df = pd.read_parquet(ALL_TRANSACTIONS_PATH)
        return _ensure_all_transactions_schema(df)
    return _create_empty_all_transactions()


def load_payment_notifications() -> pd.DataFrame:
    """Load Payment Notifications for reconciliation."""
    if PAYMENT_NOTIFICATIONS_PATH.exists():
        df = pd.read_parquet(PAYMENT_NOTIFICATIONS_PATH)
        return _ensure_payment_notifications_schema(df)
    return _create_empty_payment_notifications()


def load_matches() -> pd.DataFrame:
    """Load reconciliation match history."""
    if MATCHES_PATH.exists():
        return pd.read_parquet(MATCHES_PATH)
    return _create_empty_matches_df()


def save_all_transactions(df: pd.DataFrame) -> None:
    """Save to All Transactions List."""
    df = _ensure_all_transactions_schema(df)
    df.to_parquet(ALL_TRANSACTIONS_PATH, index=False)


def save_payment_notifications(df: pd.DataFrame) -> None:
    """Save Payment Notifications."""
    df = _ensure_payment_notifications_schema(df)
    df.to_parquet(PAYMENT_NOTIFICATIONS_PATH, index=False)


def save_matches(df: pd.DataFrame) -> None:
    """Save reconciliation matches."""
    df = _ensure_matches_schema(df)
    df.to_parquet(MATCHES_PATH, index=False)


def load_all() -> Tuple[pd.DataFrame, pd.DataFrame]:
    """Return (invoices_df, payments_df)."""
    return load_all_transactions(), load_payment_notifications()


def save_all(invoices_df: pd.DataFrame, payments_df: pd.DataFrame) -> None:
    """Save both invoices and payments."""
    save_all_transactions(invoices_df)
    save_payment_notifications(payments_df)


def record_match(invoice_record_id: str, payment_record_id: str, matched_by: str = "auto") -> None:
    """Record a successful reconciliation match."""
    matches_df = load_matches()
    
    # Check if this match already exists
    existing_match = matches_df[
        (matches_df['invoice_record_id'] == invoice_record_id) & 
        (matches_df['payment_record_id'] == payment_record_id)
    ]
    
    if not existing_match.empty:
        return  # Match already recorded
    
    new_match = pd.DataFrame([{
        'match_id': str(uuid.uuid4()),
        'invoice_record_id': invoice_record_id,
        'payment_record_id': payment_record_id,
        'matched_at': datetime.utcnow(),
        'matched_by': matched_by
    }])
    
    matches_df = pd.concat([matches_df, new_match], ignore_index=True)
    save_matches(matches_df)


def record_manual_match(invoice_record_id: str, payment_record_id: str) -> None:
    """Record a manual reconciliation match."""
    record_match(invoice_record_id, payment_record_id, "manual")


# ---------------------------------------------------------------------
# Schema Management
# ---------------------------------------------------------------------

def _create_empty_all_transactions() -> pd.DataFrame:
    """Empty All Transactions List with exact specified columns."""
    return pd.DataFrame({
        'record_id': pd.Series([], dtype='str'),
        'upload_timestamp': pd.Series([], dtype='datetime64[ns]'),
        'document_number': pd.Series([], dtype='str'),
        'invoice_date': pd.Series([], dtype='datetime64[ns]'),
        'total_billed': pd.Series([], dtype='float64'),
        'vendor_name': pd.Series([], dtype='str'),
        'customer_name': pd.Series([], dtype='str'),
        # Added columns per specification
        'ar_status': pd.Series([], dtype='str'),  # 'Paid' / 'Unpaid'
        'amount_received': pd.Series([], dtype='float64'),
        'transaction_type': pd.Series([], dtype='str')  # 'invoice', 'credit_memo', etc.
    })


def _create_empty_payment_notifications() -> pd.DataFrame:
    """Empty Payment Notifications with exact specified columns."""
    return pd.DataFrame({
        'record_id': pd.Series([], dtype='str'),
        'upload_timestamp': pd.Series([], dtype='datetime64[ns]'),
        'payment_notification_id': pd.Series([], dtype='str'),  # For grouping
        'document_number': pd.Series([], dtype='str'),
        'invoice_date': pd.Series([], dtype='datetime64[ns]'),
        'payment_amount': pd.Series([], dtype='float64'),
        'vendor_name': pd.Series([], dtype='str'),
        # Status columns
        'applied_status': pd.Series([], dtype='str'),  # 'Applied' / 'Unapplied'
        'applied_to_invoice': pd.Series([], dtype='str')  # Links to invoice record_id
    })


def _create_empty_matches_df() -> pd.DataFrame:
    """Create empty matches DataFrame for audit trail."""
    return pd.DataFrame({
        'match_id': pd.Series([], dtype='str'),
        'invoice_record_id': pd.Series([], dtype='str'),
        'payment_record_id': pd.Series([], dtype='str'),
        'matched_at': pd.Series([], dtype='datetime64[ns]'),
        'matched_by': pd.Series([], dtype='str')  # 'auto' or 'manual'
    })


def _ensure_all_transactions_schema(df: pd.DataFrame) -> pd.DataFrame:
    """Ensure All Transactions List has exact specified columns."""
    if df.empty:
        return _create_empty_all_transactions()
    
    df = df.copy()
    
    # Required columns per specification
    if 'record_id' not in df.columns:
        df['record_id'] = [str(uuid.uuid4()) for _ in range(len(df))]
    if 'upload_timestamp' not in df.columns:
        df['upload_timestamp'] = datetime.utcnow()
    if 'ar_status' not in df.columns:
        df['ar_status'] = 'Unpaid'
    if 'amount_received' not in df.columns:
        df['amount_received'] = 0.0
    if 'transaction_type' not in df.columns:
        df['transaction_type'] = 'invoice'  # Default
    
    return df


def _ensure_payment_notifications_schema(df: pd.DataFrame) -> pd.DataFrame:
    """Ensure Payment Notifications have exact specified columns."""
    if df.empty:
        return _create_empty_payment_notifications()
    
    df = df.copy()
    
    # Required columns per specification
    if 'record_id' not in df.columns:
        df['record_id'] = [str(uuid.uuid4()) for _ in range(len(df))]
    if 'upload_timestamp' not in df.columns:
        df['upload_timestamp'] = datetime.utcnow()
    if 'payment_notification_id' not in df.columns:
        # Generate group IDs for Tab 4 summary
        df['payment_notification_id'] = df['upload_timestamp'].dt.strftime('PN-%Y%m%d-') + df.index.astype(str)
    if 'applied_status' not in df.columns:
        df['applied_status'] = 'Unapplied'
    if 'applied_to_invoice' not in df.columns:
        df['applied_to_invoice'] = ''
    
    return df


def _ensure_matches_schema(df: pd.DataFrame) -> pd.DataFrame:
    """Ensure matches DataFrame has correct schema."""
    expected_columns = ['match_id', 'invoice_record_id', 'payment_record_id', 'matched_at', 'matched_by']
    
    for col in expected_columns:
        if col not in df.columns:
            if col == 'match_id':
                df[col] = [str(uuid.uuid4()) for _ in range(len(df))]
            elif col == 'matched_at':
                df[col] = datetime.utcnow()
            elif col == 'matched_by':
                df[col] = 'auto'
            else:
                df[col] = None
    
    return df


# ---------------------------------------------------------------------
# Utility functions for the enhanced system
# ---------------------------------------------------------------------

def get_payment_notification_summary() -> pd.DataFrame:
    """Generate summary for Tab 4 - grouped by payment_notification_id."""
    payments_df = load_payment_notifications()
    
    if payments_df.empty:
        return _create_empty_pn_summary()
    
    summary = payments_df.groupby('payment_notification_id').agg({
        'upload_timestamp': 'min',
        'record_id': 'count',
        'payment_amount': 'sum',
        'applied_status': lambda x: (x == 'Applied').sum()
    }).reset_index()
    
    summary.columns = ['payment_notification_id', 'upload_timestamp', 'total_count', 'total_value', 'applied_count']
    summary['unapplied_count'] = summary['total_count'] - summary['applied_count']
    
    return summary


def get_pn_details(payment_notification_id: str) -> pd.DataFrame:
    """Get details for a specific Payment Notification (drill-down)."""
    payments_df = load_payment_notifications()
    return payments_df[payments_df['payment_notification_id'] == payment_notification_id]


def get_reconciliation_audit_trail() -> pd.DataFrame:
    """Get complete audit trail of all matches."""
    matches_df = load_matches()
    invoices_df = load_all_transactions()
    payments_df = load_payment_notifications()
    
    if matches_df.empty:
        return pd.DataFrame()
    
    # Enrich match data with invoice and payment details
    audit_trail = matches_df.merge(
        invoices_df[['record_id', 'document_number', 'total_billed', 'vendor_name']],
        left_on='invoice_record_id',
        right_on='record_id',
        how='left',
        suffixes=('', '_invoice')
    ).merge(
        payments_df[['record_id', 'document_number', 'payment_amount', 'vendor_name']],
        left_on='payment_record_id',
        right_on='record_id',
        how='left',
        suffixes=('_invoice', '_payment')
    )
    
    # Select and rename columns for clarity
    audit_trail = audit_trail[[
        'matched_at', 'matched_by', 
        'document_number_invoice', 'total_billed', 'vendor_name_invoice',
        'document_number_payment', 'payment_amount', 'vendor_name_payment'
    ]]
    
    audit_trail.columns = [
        'matched_at', 'matched_by',
        'invoice_number', 'invoice_amount', 'invoice_vendor',
        'payment_number', 'payment_amount', 'payment_vendor'
    ]
    
    return audit_trail


def _create_empty_pn_summary() -> pd.DataFrame:
    """Empty Payment Notification Summary."""
    return pd.DataFrame({
        'payment_notification_id': pd.Series([], dtype='str'),
        'upload_timestamp': pd.Series([], dtype='datetime64[ns]'),
        'total_count': pd.Series([], dtype='int64'),
        'total_value': pd.Series([], dtype='float64'),
        'applied_count': pd.Series([], dtype='int64'),
        'unapplied_count': pd.Series([], dtype='int64')
    })


def get_vendor_summary() -> pd.DataFrame:
    """Get vendor-level summary for analytics."""
    payments_df = load_payment_notifications()
    
    if payments_df.empty:
        return pd.DataFrame()
    
    vendor_summary = payments_df.groupby('vendor_name').agg({
        'payment_amount': ['count', 'sum'],
        'applied_status': lambda x: (x == 'Applied').sum()
    }).reset_index()
    
    vendor_summary.columns = ['vendor_name', 'payment_count', 'total_payment_amount', 'applied_count']
    vendor_summary['unapplied_count'] = vendor_summary['payment_count'] - vendor_summary['applied_count']
    vendor_summary['reconciliation_rate'] = (vendor_summary['applied_count'] / vendor_summary['payment_count'] * 100).round(1)
    
    return vendor_summary