transaction_id,date,amount,fee,description
TXN001,2026-08-01,1250.00,0.00,Alipay Collection
TXN002,2026-08-01,890.50,0.00,WeChat Collection
TXN003,2026-08-02,2100.00,5.25,Alipay Collection
TXN004,2026-08-02,560.00,0.00,Bank Transfer
TXN005,2026-08-03,1780.25,0.00,Alipay Collection
TXN006,2026-08-03,920.00,2.30,WeChat Collection
TXN007,2026-08-04,3200.00,8.00,Alipay Collection
TXN008,2026-08-04,1450.50,0.00,Bank Transfer
TXN009,2026-08-05,680.00,0.00,WeChat Collection
TXN010,2026-08-05,2350.75,5.88,Alipay Collection

order_id,payment_date,payment_amount,platform_fee,settle_amount,channel
ORD20260801001,2026-08-01,1250.00,37.50,1212.50,alipay
ORD20260801002,2026-08-01,890.50,26.72,863.78,wechat
ORD20260802001,2026-08-02,2100.00,63.00,2037.00,alipay
ORD20260802002,2026-08-02,560.00,0.00,560.00,bank
ORD20260803001,2026-08-03,1780.25,53.41,1726.84,alipay
ORD20260803002,2026-08-03,920.00,27.60,892.40,wechat
ORD20260804001,2026-08-04,3200.00,96.00,3104.00,alipay
ORD20260804002,2026-08-04,1450.50,0.00,1450.50,bank
ORD20260805001,2026-08-05,680.00,20.40,659.60,wechat
ORD20260805002,2026-08-05,2350.75,70.52,2280.23,alipay

sale_id,order_id,sale_date,amount,tax_rate,status
S001,ORD20260801001,2026-08-01,1250.00,0.06,completed
S002,ORD20260801002,2026-08-01,890.50,0.06,completed
S003,ORD20260802001,2026-08-02,2100.00,0.06,completed
S004,ORD20260802002,2026-08-02,560.00,0.00,completed
S005,ORD20260803001,2026-08-03,1780.25,0.06,completed
S006,ORD20260803002,2026-08-03,920.00,0.06,completed
S007,ORD20260804001,2026-08-04,3200.00,0.06,completed
S008,ORD20260804002,2026-08-04,1450.50,0.00,completed
S009,ORD20260805001,2026-08-05,680.00,0.06,completed
S010,ORD20260805002,2026-08-05,2350.75,0.06,completed
S011,ORD20260805003,2026-08-05,450.00,0.06,pending

import duckdb

con = duckdb.connect("ecommerce_recon.db")

# Load three data sources in one line each
con.execute("CREATE TABLE bank AS SELECT * FROM read_csv_auto('bank_statement.csv')")
con.execute("CREATE TABLE platform AS SELECT * FROM read_csv_auto('platform_settlement.csv')")
con.execute("CREATE TABLE erp AS SELECT * FROM read_csv_auto('erp_sales.csv')")

# Check row counts
print(con.execute("""
    SELECT 'bank' AS source, COUNT(*) FROM bank 
    UNION ALL SELECT 'platform', COUNT(*) FROM platform 
    UNION ALL SELECT 'erp', COUNT(*) FROM erp
""").fetchall())
# [('bank', 10), ('platform', 10), ('erp', 11)]

-- Core reconciliation: three-way match
WITH matched AS (
    SELECT 
        e.order_id,
        e.sale_id,
        e.amount AS erp_amount,
        p.payment_amount AS platform_amount,
        b.amount AS bank_amount,
        p.platform_fee,
        b.fee AS bank_fee,
        -- Amount discrepancy analysis
        ROUND(e.amount - p.payment_amount, 2) AS erp_vs_platform_diff,
        ROUND(p.settle_amount - b.amount, 2) AS platform_vs_bank_diff,
        ROUND(e.amount - b.amount, 2) AS erp_vs_bank_diff
    FROM erp e
    LEFT JOIN platform p ON e.order_id = p.order_id
    LEFT JOIN bank b ON CAST(SUBSTRING(b.transaction_id, 4) AS INTEGER) = 
                       CAST(SUBSTRING(p.order_id, 4) AS INTEGER)
    WHERE e.status = 'completed'
)
SELECT 
    order_id,
    sale_id,
    erp_amount,
    platform_amount,
    bank_amount,
    platform_fee,
    bank_fee,
    erp_vs_platform_diff,
    platform_vs_bank_diff,
    erp_vs_bank_diff,
    CASE 
        WHEN erp_vs_platform_diff != 0 THEN '⚠️ ERP vs Platform Mismatch'
        WHEN platform_vs_bank_diff != 0 THEN '⚠️ Platform vs Bank Mismatch'
        WHEN erp_vs_bank_diff != 0 THEN '⚠️ ERP vs Bank Mismatch'
        ELSE '✅ Fully Matched'
    END AS reconciliation_status
FROM matched
ORDER BY order_id;

-- Analysis 1: Find all amount mismatches
SELECT 
    'ERP vs Platform' AS comparison,
    order_id,
    ROUND(erp.amount - platform.payment_amount, 2) AS amount_diff,
    erp.amount AS erp_amount,
    platform.payment_amount AS platform_amount
FROM erp erp
JOIN platform platform ON erp.order_id = platform.order_id
WHERE ABS(erp.amount - platform.payment_amount) > 0.01

UNION ALL

SELECT 
    'Platform vs Bank' AS comparison,
    platform.order_id,
    ROUND(platform.settle_amount - bank.amount, 2) AS amount_diff,
    platform.settle_amount AS platform_amount,
    bank.amount AS bank_amount
FROM platform
JOIN bank ON CAST(SUBSTRING(bank.transaction_id, 4) AS INTEGER) = 
           CAST(SUBSTRING(platform.order_id, 4) AS INTEGER)
WHERE ABS(platform.settle_amount - bank.amount) > 0.01

UNION ALL

SELECT 
    'ERP vs Bank' AS comparison,
    erp.order_id,
    ROUND(erp.amount - bank.amount, 2) AS amount_diff,
    erp.amount AS erp_amount,
    bank.amount AS bank_amount
FROM erp erp
JOIN bank ON CAST(SUBSTRING(bank.transaction_id, 4) AS INTEGER) = 
           CAST(SUBSTRING(erp.order_id, 4) AS INTEGER)
WHERE ABS(erp.amount - bank.amount) > 0.01;

-- Analysis 2: Find missing records
SELECT 'ERP Missing' AS issue_type, order_id, amount AS amount_erp
FROM erp
WHERE order_id NOT IN (SELECT order_id FROM platform)

UNION ALL

SELECT 'Platform Missing' AS issue_type, order_id, payment_amount AS amount_platform
FROM platform
WHERE order_id NOT IN (SELECT order_id FROM erp)

UNION ALL

SELECT 'Bank Missing' AS issue_type, transaction_id, amount AS amount_bank
FROM bank
WHERE CAST(SUBSTRING(transaction_id, 4) AS INTEGER) NOT IN 
    (SELECT CAST(SUBSTRING(order_id, 4) AS INTEGER) FROM platform)

UNION ALL

SELECT 'Bank Missing (Reverse)' AS issue_type, order_id, settle_amount AS amount_platform
FROM platform
WHERE CAST(SUBSTRING(order_id, 4) AS INTEGER) NOT IN 
    (SELECT CAST(SUBSTRING(transaction_id, 4) AS INTEGER) FROM bank);

-- Analysis 3: Fee analysis (are processing fees reasonable?)
SELECT 
    order_id,
    platform_fee,
    bank_fee,
    ROUND(platform_fee + bank_fee, 2) AS total_fee,
    ROUND((platform_fee + bank_fee) / settle_amount * 100, 2) AS fee_rate_pct,
    CASE 
        WHEN (platform_fee + bank_fee) / NULLIF(settle_amount, 0) > 0.05 THEN '⚠️ High Fee Rate'
        WHEN (platform_fee + bank_fee) / NULLIF(settle_amount, 0) < 0.01 THEN '✅ Normal Fee'
        ELSE '📊 Normal Fee'
    END AS fee_status
FROM platform
JOIN bank ON CAST(SUBSTRING(bank.transaction_id, 4) AS INTEGER) = 
           CAST(SUBSTRING(platform.order_id, 4) AS INTEGER);

import duckdb
from datetime import datetime
from pathlib import Path

def generate_reconciliation_report(db_name="reconciliation.db"):
    con = duckdb.connect(db_name)
    
    # Load data
    con.execute("CREATE TABLE bank AS SELECT * FROM read_csv_auto('bank_statement.csv')")
    con.execute("CREATE TABLE platform AS SELECT * FROM read_csv_auto('platform_settlement.csv')")
    con.execute("CREATE TABLE erp AS SELECT * FROM read_csv_auto('erp_sales.csv')")
    
    reconcile_date = datetime.now().strftime('%Y-%m-%d')
    
    # Get statistics
    stats = con.execute("""
        WITH matched AS (
            SELECT 
                e.order_id,
                e.amount AS erp_amount,
                p.payment_amount AS platform_amount,
                b.amount AS bank_amount,
                p.platform_fee,
                b.fee AS bank_fee
            FROM erp e
            LEFT JOIN platform p ON e.order_id = p.order_id
            LEFT JOIN bank b ON CAST(SUBSTRING(b.transaction_id, 4) AS INTEGER) = 
                               CAST(SUBSTRING(p.order_id, 4) AS INTEGER)
            WHERE e.status = 'completed'
        )
        SELECT 
            COUNT(*) AS total_orders,
            SUM(CASE WHEN platform_amount IS NOT NULL AND bank_amount IS NOT NULL THEN 1 ELSE 0 END) AS fully_matched,
            SUM(CASE WHEN platform_amount IS NULL THEN 1 ELSE 0 END) AS missing_platform,
            SUM(CASE WHEN bank_amount IS NULL AND platform_amount IS NOT NULL THEN 1 ELSE 0 END) AS missing_bank,
            ROUND(SUM(erp_amount), 2) AS total_erp_amount,
            ROUND(SUM(platform_amount), 2) AS total_platform_amount,
            ROUND(SUM(bank_amount), 2) AS total_bank_amount,
            ROUND(SUM(platform_fee), 2) AS total_platform_fee,
            ROUND(SUM(bank_fee), 2) AS total_bank_fee
        FROM matched
    """).fetchone()
    
    total, matched, missing_platform, missing_bank, erp_total, platform_total, bank_total, p_fee, b_fee = stats
    
    # Get discrepancy details
    diffs = con.execute("""
        WITH matched AS (
            SELECT 
                e.order_id, e.sale_id, e.amount AS erp_amount,
                p.payment_amount, p.settle_amount,
                b.amount AS bank_amount,
                p.platform_fee, b.fee AS bank_fee
            FROM erp e
            LEFT JOIN platform p ON e.order_id = p.order_id
            LEFT JOIN bank b ON CAST(SUBSTRING(b.transaction_id, 4) AS INTEGER) = 
                               CAST(SUBSTRING(p.order_id, 4) AS INTEGER)
            WHERE e.status = 'completed'
        )
        SELECT 
            order_id, sale_id, erp_amount,
            COALESCE(payment_amount, 0) AS platform_amount,
            COALESCE(bank_amount, 0) AS bank_amount,
            COALESCE(platform_fee, 0) AS platform_fee,
            COALESCE(bank_fee, 0) AS bank_fee,
            CASE 
                WHEN payment_amount IS NULL THEN '⚠️ Missing Platform'
                WHEN bank_amount IS NULL THEN '⚠️ Missing Bank'
                WHEN ABS(erp_amount - COALESCE(payment_amount, 0)) > 0.01 THEN '⚠️ Amount Mismatch'
                ELSE '✅ Matched'
            END AS status
        FROM matched
        ORDER BY order_id
    """).fetchall()
    
    # Generate report
    report = f"""# 📊 Automated Reconciliation Report

**Reconciliation Date**: {reconcile_date}
**Generated At**: {datetime.now().strftime('%Y-%m-%d %H:%M')}
**Data Sources**: Bank Statement · Payment Platform · ERP Sales Record

---

## 1. Summary

| Metric | Value |
|--------|-------|
| Total Orders | {total} |
| Fully Matched | {matched} |
| Missing Platform | {missing_platform} |
| Missing Bank | {missing_bank} |
| ERP Total Amount | ${erp_total:,.2f} |
| Platform Settlement Total | ${platform_total:,.2f} |
| Bank Received Total | ${bank_total:,.2f} |
| Platform Fees Total | ${p_fee:,.2f} |
| Bank Fees Total | ${b_fee:,.2f} |

---

## 2. Discrepancy Details

| Order ID | ERP Amount | Platform Amount | Bank Amount | Status |
|----------|-----------|----------------|------------|--------|
"""
    
    for row in diffs:
        order_id, sale_id, erp_amt, plat_amt, bank_amt, p_fee, b_fee, status = row
        report += f"| {order_id} | ${erp_amt:,.2f} | ${plat_amt:,.2f} | ${bank_amt:,.2f} | {status} |\n"
    
    report += f"\n---\n*Report generated by DuckDB*\n"
    
    # Save report
    output_dir = Path("./reports")
    output_dir.mkdir(exist_ok=True)
    report_path = output_dir / f"reconciliation_{reconcile_date}.md"
    report_path.write_text(report, encoding="utf-8")
    
    print(f"✅ Report generated: {report_path}")
    print(f"   Total: {total} | Matched: {matched} | Discrepancies: {missing_platform + missing_bank}")
    
    con.close()
    return report_path

# Run reconciliation
generate_reconciliation_report()

#!/bin/bash
# daily_reconciliation.sh — Auto-reconcile at 2 AM every day

cd ~/reconciliation-system

# 1. Fetch latest data
python3 fetch_latest_data.py

# 2. Run reconciliation
python3 run_reconciliation.py

# 3. Send notification
report=$(ls -t reports/reconciliation_*.md | head -1)
echo "Reconciliation complete: $report" | mail -s "[Recon Report] $(date +%Y-%m-%d)" [email protected]

-- Read all platform files at once
CREATE TABLE all_platforms AS 
SELECT * FROM read_csv_auto('platforms/*.csv');

-- Cross-currency: convert everything to USD
SELECT 
    order_id,
    amount * get_fx_rate(currency, 'USD') AS amount_usd,
    bank_amount * get_fx_rate(bank_currency, 'USD') AS bank_amount_usd
FROM reconciliation
WHERE DATE(ts) = '2026-08-11';

SELECT 
    STRFTIME(date, '%Y-%m') AS month,
    COUNT(*) AS total_transactions,
    SUM(CASE WHEN amount_diff != 0 THEN 1 ELSE 0 END) AS discrepancy_count,
    ROUND(SUM(CASE WHEN amount_diff != 0 THEN ABS(amount_diff) ELSE 0 END), 2) AS total_discrepancy
FROM reconciliation_history
GROUP BY month
ORDER BY month;

import pandas as pd

df = con.execute("SELECT * FROM reconciliation_result").fetchdf()
html = df.to_html(index=False, classes='table table-striped')

report_html = f"""
<!DOCTYPE html>
<html>
<head>
    <title>Reconciliation Dashboard - {reconcile_date}</title>
    <style>
        body {{ font-family: -apple-system, sans-serif; padding: 20px; background: #f5f5f5; }}
        .card {{ background: white; border-radius: 8px; padding: 20px; margin: 10px 0; box-shadow: 0 2px 8px rgba(0,0,0,0.1); }}
        .match {{ color: #22c55e; font-weight: bold; }}
        .diff {{ color: #ef4444; font-weight: bold; }}
    </style>
</head>
<body>
    <h1>📊 Reconciliation Dashboard</h1>
    <p>Date: {reconcile_date}</p>
    <div class="card">{html}</div>
</body>
</html>
"""

with open(f"reconciliation_dashboard_{reconcile_date}.html", "w") as f:
    f.write(report_html)