audit_payouts.py

#!/usr/bin/env python3
"""
Shopify Payout Auditor

Matches each order transaction against payout transaction records to ensure
every eligible order has been paid out. Lists any orders missing from payouts.

Transactions NOT expected in payouts:
- Gift cards
- Manual orders marked as paid
- Cash transactions
- Shop cash
- Failed transactions
"""

import argparse
import csv
from collections import defaultdict
from dataclasses import dataclass, field
from datetime import datetime
from decimal import Decimal, ROUND_HALF_UP
from pathlib import Path
from typing import Optional


@dataclass
class OrderTransaction:
    """Represents a Shopify order transaction."""
    order_id: str
    order_name: str
    kind: str
    gateway: str
    created_at: datetime
    status: str
    amount: Decimal
    currency: str
    card_type: str
    payment_method: str

    @property
    def date(self) -> str:
        return self.created_at.strftime('%Y-%m-%d')

    @property
    def is_payout_eligible(self) -> bool:
        non_payout_gateways = {'gift_card', 'cash', 'manual', 'shop_cash'}
        if self.gateway in non_payout_gateways:
            return False
        if self.status != 'success':
            return False
        return True

    @property
    def exclusion_reason(self) -> Optional[str]:
        if self.gateway == 'gift_card':
            return 'Gift card payment'
        if self.gateway == 'cash':
            return 'Cash payment'
        if self.gateway == 'manual':
            return 'Manual payment'
        if self.gateway == 'shop_cash':
            return 'Shop cash payment'
        if self.status != 'success':
            return f'Transaction status: {self.status}'
        return None


@dataclass
class PayoutTransaction:
    """Represents a Shopify payout transaction record."""
    transaction_date: datetime
    type: str  # charge, refund, chargeback, etc.
    order_name: str
    card_brand: str
    card_source: str
    payout_status: str
    payout_date: str
    available_on: str
    amount: Decimal
    fee: Decimal
    net: Decimal
    checkout: str
    payment_method: str
    currency: str


@dataclass
class OrderAuditResult:
    """Audit result for a single order."""
    order_name: str
    order_id: str
    transaction_date: str
    transaction_amount: Decimal
    payment_method: str
    gateway: str
    payout_found: bool
    payout_amount: Optional[Decimal] = None
    payout_fee: Optional[Decimal] = None
    payout_net: Optional[Decimal] = None
    payout_date: Optional[str] = None
    payout_status: Optional[str] = None
    amount_difference: Optional[Decimal] = None
    notes: str = ""


def parse_decimal(value: str) -> Decimal:
    if not value or value.strip() == '':
        return Decimal('0.00')
    clean_value = value.strip().strip('"').replace(',', '')
    return Decimal(clean_value).quantize(Decimal('0.01'), rounding=ROUND_HALF_UP)


def parse_datetime(value: str) -> datetime:
    try:
        return datetime.strptime(value.strip(), '%Y-%m-%d %H:%M:%S %z')
    except ValueError:
        return datetime.strptime(value.strip()[:19], '%Y-%m-%d %H:%M:%S')


def load_order_transactions(filepath: str) -> list[OrderTransaction]:
    """Load order transactions from CSV."""
    transactions = []
    with open(filepath, 'r', encoding='utf-8') as f:
        reader = csv.DictReader(f)
        for row in reader:
            txn = OrderTransaction(
                order_id=row['Order'],
                order_name=row['Name'],
                kind=row['Kind'],
                gateway=row['Gateway'],
                created_at=parse_datetime(row['Created At']),
                status=row['Status'],
                amount=parse_decimal(row['Amount']),
                currency=row['Currency'],
                card_type=row.get('Card Type', '').strip('"'),
                payment_method=row.get('Payment Method', '')
            )
            transactions.append(txn)
    return transactions


def load_payout_transactions(filepath: str) -> list[PayoutTransaction]:
    """Load payout transactions from CSV."""
    transactions = []
    with open(filepath, 'r', encoding='utf-8') as f:
        reader = csv.DictReader(f)
        for row in reader:
            txn = PayoutTransaction(
                transaction_date=parse_datetime(row['Transaction Date']),
                type=row['Type'],
                order_name=row['Order'],
                card_brand=row.get('Card Brand', ''),
                card_source=row.get('Card Source', ''),
                payout_status=row['Payout Status'],
                payout_date=row.get('Payout Date', ''),
                available_on=row.get('Available On', ''),
                amount=parse_decimal(row['Amount']),
                fee=parse_decimal(row['Fee']),
                net=parse_decimal(row['Net']),
                checkout=row.get('Checkout', ''),
                payment_method=row.get('Payment Method Name', ''),
                currency=row.get('Currency', 'USD')
            )
            transactions.append(txn)
    return transactions


def build_payout_lookup(payout_txns: list[PayoutTransaction]) -> dict:
    """
    Build lookup of payout transactions by order name.
    Returns dict: order_name -> list of payout transactions for that order
    """
    lookup = defaultdict(list)
    for txn in payout_txns:
        if txn.order_name:
            lookup[txn.order_name].append(txn)
    return dict(lookup)


def audit_orders(
    order_txns: list[OrderTransaction],
    payout_lookup: dict
) -> tuple[list[OrderAuditResult], list[OrderAuditResult], dict]:
    """
    Audit each order transaction against payout records.

    Returns:
        - paid_orders: Orders that were found in payouts
        - unpaid_orders: Eligible orders NOT found in payouts
        - exclusion_summary: Summary of excluded (non-payout-eligible) orders
    """
    paid_orders = []
    unpaid_orders = []
    exclusion_summary = defaultdict(lambda: {'count': 0, 'amount': Decimal('0.00'), 'orders': []})

    # Group order transactions by order name to handle multiple transactions per order
    orders_by_name = defaultdict(list)
    for txn in order_txns:
        orders_by_name[txn.order_name].append(txn)

    for order_name, txns in orders_by_name.items():
        # Check if any transaction for this order is payout-eligible
        eligible_txns = [t for t in txns if t.is_payout_eligible]
        excluded_txns = [t for t in txns if not t.is_payout_eligible]

        # Track exclusions
        for txn in excluded_txns:
            reason = txn.exclusion_reason
            exclusion_summary[reason]['count'] += 1
            exclusion_summary[reason]['amount'] += txn.amount
            exclusion_summary[reason]['orders'].append(order_name)

        if not eligible_txns:
            # All transactions for this order are excluded
            continue

        # Sum up eligible transaction amounts for this order
        # Refunds should be subtracted, not added
        total_eligible_amount = Decimal('0.00')
        for t in eligible_txns:
            if t.kind == 'refund':
                total_eligible_amount -= t.amount
            else:
                total_eligible_amount += t.amount

        first_txn = eligible_txns[0]

        # Look up payout records for this order
        payout_records = payout_lookup.get(order_name, [])

        # Check if there are charge records in payouts
        charge_records = [p for p in payout_records if p.type == 'charge']

        if charge_records:
            # Order was paid out - sum all charges and refunds
            # Note: refunds already have negative amounts in the payout export
            payment_records = [p for p in payout_records if p.type in ('charge', 'refund')]
            total_payout_amount = sum(p.amount for p in payment_records)
            total_payout_fee = sum(p.fee for p in payment_records)
            total_payout_net = sum(p.net for p in payment_records)

            # Use the latest payout record for date/status
            latest_payout = max(payment_records, key=lambda p: p.payout_date or '')

            amount_diff = total_eligible_amount - total_payout_amount

            result = OrderAuditResult(
                order_name=order_name,
                order_id=first_txn.order_id,
                transaction_date=first_txn.date,
                transaction_amount=total_eligible_amount,
                payment_method=first_txn.payment_method,
                gateway=first_txn.gateway,
                payout_found=True,
                payout_amount=total_payout_amount,
                payout_fee=total_payout_fee,
                payout_net=total_payout_net,
                payout_date=latest_payout.payout_date,
                payout_status=latest_payout.payout_status,
                amount_difference=amount_diff,
                notes="" if abs(amount_diff) < Decimal('0.01') else f"Amount difference: ${amount_diff:.2f}"
            )
            paid_orders.append(result)
        else:
            # Order NOT found in payouts
            result = OrderAuditResult(
                order_name=order_name,
                order_id=first_txn.order_id,
                transaction_date=first_txn.date,
                transaction_amount=total_eligible_amount,
                payment_method=first_txn.payment_method,
                gateway=first_txn.gateway,
                payout_found=False,
                notes="NOT PAID OUT - Missing from payout records"
            )
            unpaid_orders.append(result)

    return paid_orders, unpaid_orders, dict(exclusion_summary)


def print_report(
    paid_orders: list[OrderAuditResult],
    unpaid_orders: list[OrderAuditResult],
    exclusion_summary: dict,
    order_txns: list[OrderTransaction],
    payout_txns: list[PayoutTransaction]
):
    """Print the audit report."""
    print("=" * 80)
    print("SHOPIFY PAYOUT AUDIT REPORT - Order by Order Analysis")
    print("=" * 80)
    print()

    # Summary stats
    total_orders = len(set(t.order_name for t in order_txns))
    eligible_orders = len(paid_orders) + len(unpaid_orders)

    print("-" * 80)
    print("SUMMARY")
    print("-" * 80)
    print(f"  Total Orders in Transaction Export:    {total_orders:,}")
    print(f"  Payout-Eligible Orders:                {eligible_orders:,}")
    print(f"  Orders Paid Out:                       {len(paid_orders):,}")
    print(f"  Orders NOT Paid Out:                   {len(unpaid_orders):,}")
    print()

    # Financial summary for paid orders
    total_paid_amount = sum(o.transaction_amount for o in paid_orders)
    total_payout_amount = sum(o.payout_amount or Decimal('0') for o in paid_orders)
    total_fees = sum(o.payout_fee or Decimal('0') for o in paid_orders)
    total_net = sum(o.payout_net or Decimal('0') for o in paid_orders)

    print("-" * 80)
    print("PAID ORDERS FINANCIAL SUMMARY")
    print("-" * 80)
    print(f"  Total Transaction Amount:   ${total_paid_amount:,.2f}")
    print(f"  Total Payout Amount:        ${total_payout_amount:,.2f}")
    print(f"  Total Fees:                 ${total_fees:,.2f}")
    print(f"  Total Net Received:         ${total_net:,.2f}")
    print()

    # Excluded orders summary
    if exclusion_summary:
        print("-" * 80)
        print("EXCLUDED ORDERS (Not expected in payouts)")
        print("-" * 80)
        for reason, data in sorted(exclusion_summary.items()):
            print(f"  {reason}:")
            print(f"    Count:  {data['count']:,}")
            print(f"    Amount: ${data['amount']:,.2f}")
        print()

    # UNPAID ORDERS - The main focus
    if unpaid_orders:
        unpaid_total = sum(o.transaction_amount for o in unpaid_orders)

        print("=" * 80)
        print(f"UNPAID ORDERS - {len(unpaid_orders)} orders totaling ${unpaid_total:,.2f}")
        print("=" * 80)
        print()
        print(f"{'Order':<12} {'Date':<12} {'Amount':>12} {'Payment Method':<25} {'Order ID'}")
        print("-" * 80)

        # Sort by date
        for order in sorted(unpaid_orders, key=lambda o: o.transaction_date):
            print(f"{order.order_name:<12} {order.transaction_date:<12} ${order.transaction_amount:>10,.2f} {order.payment_method:<25} {order.order_id}")

        print("-" * 80)
        print(f"{'TOTAL':<12} {'':<12} ${unpaid_total:>10,.2f}")
        print()
    else:
        print("=" * 80)
        print("ALL ELIGIBLE ORDERS HAVE BEEN PAID OUT")
        print("=" * 80)
        print()

    # Check for amount discrepancies in paid orders
    discrepancies = [o for o in paid_orders if o.amount_difference and abs(o.amount_difference) >= Decimal('0.01')]
    if discrepancies:
        print("-" * 80)
        print(f"AMOUNT DISCREPANCIES ({len(discrepancies)} orders)")
        print("-" * 80)
        print(f"{'Order':<12} {'Transaction':>12} {'Payout':>12} {'Difference':>12}")
        print("-" * 80)
        for order in discrepancies[:20]:  # Show first 20
            print(f"{order.order_name:<12} ${order.transaction_amount:>10,.2f} ${order.payout_amount:>10,.2f} ${order.amount_difference:>10,.2f}")
        if len(discrepancies) > 20:
            print(f"  ... and {len(discrepancies) - 20} more")
        print()

    print("=" * 80)
    print("AUDIT COMPLETE")
    print("=" * 80)


def export_unpaid_orders(unpaid_orders: list[OrderAuditResult], output_path: str):
    """Export unpaid orders to CSV."""
    with open(output_path, 'w', newline='', encoding='utf-8') as f:
        writer = csv.writer(f)
        writer.writerow([
            'Order Name',
            'Order ID',
            'Transaction Date',
            'Amount',
            'Payment Method',
            'Gateway',
            'Notes'
        ])
        for order in sorted(unpaid_orders, key=lambda o: o.transaction_date):
            writer.writerow([
                order.order_name,
                order.order_id,
                order.transaction_date,
                f'{order.transaction_amount:.2f}',
                order.payment_method,
                order.gateway,
                order.notes
            ])
    print(f"\nUnpaid orders exported to: {output_path}")


def export_full_audit(
    paid_orders: list[OrderAuditResult],
    unpaid_orders: list[OrderAuditResult],
    output_path: str
):
    """Export full audit results to CSV."""
    all_orders = paid_orders + unpaid_orders

    with open(output_path, 'w', newline='', encoding='utf-8') as f:
        writer = csv.writer(f)
        writer.writerow([
            'Order Name',
            'Order ID',
            'Transaction Date',
            'Transaction Amount',
            'Payment Method',
            'Gateway',
            'Payout Found',
            'Payout Amount',
            'Payout Fee',
            'Payout Net',
            'Payout Date',
            'Payout Status',
            'Amount Difference',
            'Notes'
        ])
        for order in sorted(all_orders, key=lambda o: o.transaction_date):
            writer.writerow([
                order.order_name,
                order.order_id,
                order.transaction_date,
                f'{order.transaction_amount:.2f}',
                order.payment_method,
                order.gateway,
                'Yes' if order.payout_found else 'No',
                f'{order.payout_amount:.2f}' if order.payout_amount else '',
                f'{order.payout_fee:.2f}' if order.payout_fee else '',
                f'{order.payout_net:.2f}' if order.payout_net else '',
                order.payout_date or '',
                order.payout_status or '',
                f'{order.amount_difference:.2f}' if order.amount_difference else '',
                order.notes
            ])
    print(f"Full audit exported to: {output_path}")


def main():
    parser = argparse.ArgumentParser(
        description='Audit Shopify payouts - verify each order has been paid out'
    )
    parser.add_argument('--transactions', '-t', help='Path to order transactions CSV')
    parser.add_argument('--payout-transactions', '-p', help='Path to payout transactions CSV')
    parser.add_argument('--export-unpaid', '-u', action='store_true',
                        help='Export unpaid orders to CSV')
    parser.add_argument('--export-full', '-f', action='store_true',
                        help='Export full audit results to CSV')
    parser.add_argument('--output-dir', '-o', default='.',
                        help='Output directory for exports')

    args = parser.parse_args()

    script_dir = Path(__file__).parent
    data_dir = script_dir / 'data'

    # Find transaction files
    if args.transactions:
        transactions_file = args.transactions
    else:
        files = list(data_dir.glob('transactions_export*.csv'))
        if not files:
            print("ERROR: No transactions export file found")
            return 1
        transactions_file = str(files[0])

    if args.payout_transactions:
        payout_file = args.payout_transactions
    else:
        files = list(data_dir.glob('payment_transactions_export*.csv'))
        if not files:
            print("ERROR: No payout transactions file found")
            print("Expected: data/payment_transactions_export*.csv")
            return 1
        payout_file = str(files[0])

    print(f"Loading order transactions from: {transactions_file}")
    print(f"Loading payout transactions from: {payout_file}")
    print()

    # Load data
    order_txns = load_order_transactions(transactions_file)
    payout_txns = load_payout_transactions(payout_file)

    print(f"Loaded {len(order_txns):,} order transactions")
    print(f"Loaded {len(payout_txns):,} payout transactions")
    print()

    # Build lookup and run audit
    payout_lookup = build_payout_lookup(payout_txns)
    paid_orders, unpaid_orders, exclusion_summary = audit_orders(order_txns, payout_lookup)

    # Print report
    print_report(paid_orders, unpaid_orders, exclusion_summary, order_txns, payout_txns)

    # Export if requested
    output_dir = Path(args.output_dir)
    timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')

    if args.export_unpaid and unpaid_orders:
        export_unpaid_orders(unpaid_orders, str(output_dir / f'unpaid_orders_{timestamp}.csv'))

    if args.export_full:
        export_full_audit(paid_orders, unpaid_orders, str(output_dir / f'full_audit_{timestamp}.csv'))

    return 0 if not unpaid_orders else 1


if __name__ == '__main__':
    exit(main())