Implementation Guide: Classify and code incoming transactions against chart of accounts automatically

# In QBO: Navigate to Settings > Chart of Accounts > Export to Excel (Run Report > Account List > Export)
# In Xero: Navigate to Accounting > Chart of Accounts > Export (CSV)
# Document in a spreadsheet: Account Number | Account Name | Account Type | Sub-type | Active Status | Last 90 Day Transaction Count
# Identify: Duplicate accounts, unused accounts (0 transactions in 12 months), inconsistently named accounts, accounts missing from standard GAAP template

# In QBO: Navigate to Banking > Link Account > Select bank > Authenticate with bank credentials
# In QBO: Verify feed status — green checkmark = active; yellow/red = needs reconnection
# In Xero: Navigate to Accounting > Bank Accounts > Add Bank Account > Select institution > Authenticate
# Test: Wait 24 hours after connecting, then verify transactions are importing with correct dates and amounts
# For banks not supporting direct feeds: Set up CSV import workflow or Dext bank statement upload as fallback

# CoA Mapping Validator Workflow
# Platform: Python script run locally or as a cloud function
# Input: Exported CSV of client's chart of accounts from QBO/Xero
# Output: Validation report with issues and recommendations

import csv
import json
from collections import Counter
from difflib import SequenceMatcher

# Standard GAAP account types that should exist
STANDARD_ACCOUNTS = {
    'Assets': ['Cash and Cash Equivalents', 'Accounts Receivable', 'Inventory', 'Prepaid Expenses', 'Fixed Assets', 'Accumulated Depreciation'],
    'Liabilities': ['Accounts Payable', 'Credit Card Payable', 'Accrued Liabilities', 'Payroll Liabilities', 'Notes Payable', 'Line of Credit'],
    'Equity': ['Owner Equity', 'Retained Earnings', 'Owner Draws'],
    'Revenue': ['Service Revenue', 'Product Revenue', 'Other Income', 'Interest Income'],
    'Expenses': ['Advertising', 'Auto Expense', 'Bank Charges', 'Contractor Expense', 'Depreciation', 'Dues and Subscriptions', 'Insurance', 'Interest Expense', 'Legal and Professional', 'Meals and Entertainment', 'Office Supplies', 'Payroll Expense', 'Rent Expense', 'Repairs and Maintenance', 'Software and Technology', 'Taxes and Licenses', 'Travel', 'Utilities']
}

def load_coa(filepath):
    """Load chart of accounts from CSV export."""
    accounts = []
    with open(filepath, 'r') as f:
        reader = csv.DictReader(f)
        for row in reader:
            accounts.append({
                'number': row.get('Account #', row.get('Code', '')).strip(),
                'name': row.get('Account', row.get('Name', '')).strip(),
                'type': row.get('Type', row.get('Account Type', '')).strip(),
                'detail_type': row.get('Detail Type', '').strip(),
                'active': row.get('Active', row.get('Status', 'Active')).strip()
            })
    return accounts

def check_duplicates(accounts):
    """Find accounts with similar names that may be duplicates."""
    issues = []
    names = [a['name'].lower() for a in accounts if a['active'].lower() in ('active', 'yes', 'true')]
    for i, name1 in enumerate(names):
        for j, name2 in enumerate(names[i+1:], i+1):
            similarity = SequenceMatcher(None, name1, name2).ratio()
            if similarity > 0.80 and name1 != name2:
                issues.append({
                    'type': 'POTENTIAL_DUPLICATE',
                    'severity': 'HIGH',
                    'accounts': [accounts[i]['name'], accounts[j]['name']],
                    'similarity': f'{similarity:.0%}',
                    'recommendation': f'Review and merge: "{accounts[i]["name"]}" and "{accounts[j]["name"]}"'
                })
    return issues

def check_missing_standard(accounts):
    """Check for standard GAAP accounts that are missing."""
    issues = []
    active_names = [a['name'].lower() for a in accounts if a['active'].lower() in ('active', 'yes', 'true')]
    for category, standard_list in STANDARD_ACCOUNTS.items():
        for std_account in standard_list:
            found = any(SequenceMatcher(None, std_account.lower(), name).ratio() > 0.70 for name in active_names)
            if not found:
                issues.append({
                    'type': 'MISSING_STANDARD_ACCOUNT',
                    'severity': 'MEDIUM',
                    'category': category,
                    'account': std_account,
                    'recommendation': f'Consider adding standard {category} account: "{std_account}"'
                })
    return issues

def check_naming_consistency(accounts):
    """Check for inconsistent naming patterns."""
    issues = []
    active = [a for a in accounts if a['active'].lower() in ('active', 'yes', 'true')]
    # Check for accounts without numbers
    no_numbers = [a for a in active if not a['number']]
    if len(no_numbers) > 0 and len(no_numbers) < len(active):
        issues.append({
            'type': 'INCONSISTENT_NUMBERING',
            'severity': 'MEDIUM',
            'count': len(no_numbers),
            'accounts': [a['name'] for a in no_numbers[:5]],
            'recommendation': 'Some accounts have numbers and some do not. Standardize by assigning numbers to all accounts.'
        })
    # Check for very short or ambiguous names
    for a in active:
        if len(a['name']) < 4:
            issues.append({
                'type': 'AMBIGUOUS_NAME',
                'severity': 'HIGH',
                'account': a['name'],
                'recommendation': f'Account name "{a["name"]}" is too short/ambiguous for AI classification. Rename to be descriptive.'
            })
    return issues

def check_account_count(accounts):
    """Check if total account count is in the optimal range for AI."""
    issues = []
    active = [a for a in accounts if a['active'].lower() in ('active', 'yes', 'true')]
    count = len(active)
    if count > 150:
        issues.append({
            'type': 'EXCESSIVE_ACCOUNTS',
            'severity': 'HIGH',
            'count': count,
            'recommendation': f'{count} active accounts detected. Optimal range for AI classification is 30-80. Consolidate sub-accounts and use class/location tracking for dimensional analysis instead.'
        })
    elif count > 80:
        issues.append({
            'type': 'HIGH_ACCOUNT_COUNT',
            'severity': 'MEDIUM',
            'count': count,
            'recommendation': f'{count} active accounts detected. Consider consolidating to 30-80 for optimal AI accuracy.'
        })
    elif count < 15:
        issues.append({
            'type': 'LOW_ACCOUNT_COUNT',
            'severity': 'LOW',
            'count': count,
            'recommendation': f'Only {count} active accounts. This may lack granularity for meaningful financial reporting.'
        })
    return issues

def generate_report(filepath):
    accounts = load_coa(filepath)
    all_issues = []
    all_issues.extend(check_duplicates(accounts))
    all_issues.extend(check_missing_standard(accounts))
    all_issues.extend(check_naming_consistency(accounts))
    all_issues.extend(check_account_count(accounts))
    
    report = {
        'total_accounts': len(accounts),
        'active_accounts': len([a for a in accounts if a['active'].lower() in ('active', 'yes', 'true')]),
        'issues_found': len(all_issues),
        'high_severity': len([i for i in all_issues if i['severity'] == 'HIGH']),
        'medium_severity': len([i for i in all_issues if i['severity'] == 'MEDIUM']),
        'low_severity': len([i for i in all_issues if i['severity'] == 'LOW']),
        'ai_readiness': 'READY' if len([i for i in all_issues if i['severity'] == 'HIGH']) == 0 else 'NEEDS_CLEANUP',
        'issues': all_issues
    }
    return json.dumps(report, indent=2)

if __name__ == '__main__':
    import sys
    if len(sys.argv) != 2:
        print('Usage: python coa_validator.py <path_to_coa_export.csv>')
        sys.exit(1)
    print(generate_report(sys.argv[1]))

# Transaction Classification Rule Engine
# Sits between bank feed ingestion and AI classification
# Rules in this engine take precedence over Booke AI suggestions

# --- rules_config.yaml (per client entity) ---
# File: /configs/clients/{client_id}/classification_rules.yaml

client_id: "acme-corp-001"
client_name: "ACME Corporation"
last_updated: "2025-01-15"
updated_by: "msp-tech@mspfirm.com"

global_settings:
  confidence_threshold: 0.90
  auto_post_enabled: true
  max_auto_post_amount: 5000.00
  require_approval_accounts:
    - "3000"  # Equity accounts
    - "2100"  # Notes Payable
    - "9000"  # Inter-company
  new_vendor_action: "flag_for_review"

rules:
  # Priority 1: Exact match rules (highest precedence)
  - rule_id: "R001"
    name: "Monthly Rent Payment"
    priority: 1
    conditions:
      vendor_contains: ["ABC Property Management", "ABC Prop Mgmt"]
      amount_range: {min: 4800, max: 5200}
      transaction_type: "debit"
    action:
      account: "6100 - Rent Expense"
      class: "General & Administrative"
      memo: "Monthly office rent"
      auto_post: true
    
  - rule_id: "R002"
    name: "Gusto Payroll"
    priority: 1
    conditions:
      vendor_contains: ["Gusto", "ZP Gusto"]
      transaction_type: "debit"
    action:
      account: "6500 - Payroll Expense"
      memo: "Payroll processing - Gusto"
      auto_post: true
      skip_ai: true  # Never send to AI; Gusto journal entries handle the detail

  - rule_id: "R003"
    name: "Stripe Revenue Deposits"
    priority: 1
    conditions:
      vendor_contains: ["Stripe", "STRIPE TRANSFER"]
      transaction_type: "credit"
    action:
      account: "4000 - Service Revenue"
      memo: "Stripe payout"
      auto_post: true

  # Priority 2: Pattern match rules
  - rule_id: "R010"
    name: "Software Subscriptions"
    priority: 2
    conditions:
      vendor_contains: ["Adobe", "Microsoft", "Google Workspace", "Zoom", "Slack", "Dropbox", "Notion", "Asana", "GitHub"]
      amount_range: {min: 0, max: 1000}
      transaction_type: "debit"
    action:
      account: "6210 - Software & Technology"
      auto_post: true

  - rule_id: "R011"
    name: "AWS/Cloud Infrastructure"
    priority: 2
    conditions:
      vendor_contains: ["Amazon Web Services", "AWS", "Google Cloud", "Microsoft Azure", "DigitalOcean", "Heroku"]
      transaction_type: "debit"
    action:
      account: "6215 - Cloud Infrastructure"
      auto_post: true

  - rule_id: "R012"
    name: "Business Insurance"
    priority: 2
    conditions:
      vendor_contains: ["Hartford", "State Farm", "GEICO Commercial", "Hiscox", "Next Insurance", "Progressive Commercial"]
      transaction_type: "debit"
    action:
      account: "6300 - Insurance Expense"
      auto_post: false  # Flag for review — may need allocation to specific insurance sub-accounts

  # Priority 3: Category-based fallback rules
  - rule_id: "R020"
    name: "Restaurant/Meal Transactions"
    priority: 3
    conditions:
      mcc_codes: [5812, 5813, 5814]  # Restaurants, Bars, Fast Food
      amount_range: {min: 0, max: 500}
      transaction_type: "debit"
    action:
      account: "6400 - Meals & Entertainment"
      auto_post: false  # Always review — may need to split business vs. personal

  - rule_id: "R021"
    name: "Gas Stations"
    priority: 3
    conditions:
      mcc_codes: [5541, 5542]
      transaction_type: "debit"
    action:
      account: "6150 - Auto & Fuel Expense"
      auto_post: true

# --- Python Rule Engine Implementation ---

import yaml
import re
from dataclasses import dataclass
from typing import Optional, List, Dict

@dataclass
class Transaction:
    id: str
    date: str
    vendor_name: str
    amount: float
    transaction_type: str  # 'credit' or 'debit'
    mcc_code: Optional[int] = None
    description: str = ''

@dataclass
class ClassificationResult:
    transaction_id: str
    account: str
    class_name: Optional[str] = None
    memo: Optional[str] = None
    auto_post: bool = False
    skip_ai: bool = False
    rule_id: Optional[str] = None
    confidence: float = 1.0  # Deterministic rules always 1.0
    source: str = 'rule_engine'

def load_rules(config_path: str) -> dict:
    with open(config_path, 'r') as f:
        return yaml.safe_load(f)

def match_vendor(vendor_name: str, patterns: List[str]) -> bool:
    vendor_lower = vendor_name.lower()
    return any(p.lower() in vendor_lower for p in patterns)

def match_amount(amount: float, amount_range: dict) -> bool:
    if not amount_range:
        return True
    return amount_range.get('min', 0) <= abs(amount) <= amount_range.get('max', float('inf'))

def match_mcc(mcc_code: Optional[int], mcc_codes: List[int]) -> bool:
    if not mcc_codes or mcc_code is None:
        return not mcc_codes  # True if no MCC filter, False if filter exists but no MCC on transaction
    return mcc_code in mcc_codes

def classify_transaction(txn: Transaction, config: dict) -> Optional[ClassificationResult]:
    rules = sorted(config.get('rules', []), key=lambda r: r.get('priority', 99))
    global_settings = config.get('global_settings', {})
    
    for rule in rules:
        conditions = rule.get('conditions', {})
        matched = True
        
        if 'vendor_contains' in conditions:
            if not match_vendor(txn.vendor_name, conditions['vendor_contains']):
                matched = False
        
        if 'amount_range' in conditions and matched:
            if not match_amount(txn.amount, conditions['amount_range']):
                matched = False
        
        if 'transaction_type' in conditions and matched:
            if txn.transaction_type != conditions['transaction_type']:
                matched = False
        
        if 'mcc_codes' in conditions and matched:
            if not match_mcc(txn.mcc_code, conditions['mcc_codes']):
                matched = False
        
        if matched:
            action = rule.get('action', {})
            auto_post = action.get('auto_post', False)
            
            # Global override: check max auto-post amount
            if auto_post and abs(txn.amount) > global_settings.get('max_auto_post_amount', 5000):
                auto_post = False
            
            # Global override: check if account requires approval
            acct_number = action['account'].split(' ')[0] if action.get('account') else ''
            if acct_number in global_settings.get('require_approval_accounts', []):
                auto_post = False
            
            return ClassificationResult(
                transaction_id=txn.id,
                account=action.get('account', 'UNCLASSIFIED'),
                class_name=action.get('class'),
                memo=action.get('memo'),
                auto_post=auto_post,
                skip_ai=action.get('skip_ai', False),
                rule_id=rule['rule_id'],
                confidence=1.0,
                source='rule_engine'
            )
    
    return None  # No rule matched — send to AI classification layer

# Usage:
# config = load_rules('/configs/clients/acme-corp-001/classification_rules.yaml')
# txn = Transaction(id='TXN-001', date='2025-01-15', vendor_name='GUSTO 01/15 PAYROLL', amount=-15234.56, transaction_type='debit')
# result = classify_transaction(txn, config)
# if result is None: send_to_booke_ai(txn)  # Fallback to AI
# elif result.auto_post: post_to_qbo(result)
# else: add_to_review_queue(result)

# Classification Accuracy Monitor Agent
# Runs weekly via cron/scheduled task
# Queries Booke AI API + QBO/Xero for classification metrics
# Generates alerts and reports

import json
import datetime
from dataclasses import dataclass, asdict
from typing import List, Dict, Optional
from collections import defaultdict

# --- Configuration ---
MONITOR_CONFIG = {
    'accuracy_threshold_warning': 0.85,
    'accuracy_threshold_critical': 0.80,
    'exception_rate_threshold': 0.15,  # 15% exception rate triggers alert
    'min_transactions_for_analysis': 50,
    'report_recipients': ['msp-team@mspfirm.com', 'admin@clientfirm.com'],
    'alert_recipients': ['msp-alerts@mspfirm.com'],
    'smtp_server': 'smtp.office365.com',
    'smtp_port': 587
}

@dataclass
class WeeklyMetrics:
    period_start: str
    period_end: str
    client_id: str
    total_transactions: int
    auto_classified: int
    manually_corrected: int
    flagged_for_review: int
    new_vendors_encountered: int
    accuracy_rate: float
    exception_rate: float
    top_misclassified_accounts: List[Dict]
    top_misclassified_vendors: List[Dict]
    accuracy_trend: str  # 'improving', 'stable', 'declining'
    alerts: List[str]

def calculate_accuracy(auto_correct: int, corrected: int, total: int) -> float:
    if total == 0:
        return 0.0
    return auto_correct / total

def detect_trend(current_accuracy: float, previous_weeks: List[float]) -> str:
    if len(previous_weeks) < 2:
        return 'insufficient_data'
    avg_previous = sum(previous_weeks[-3:]) / len(previous_weeks[-3:])
    if current_accuracy > avg_previous + 0.02:
        return 'improving'
    elif current_accuracy < avg_previous - 0.02:
        return 'declining'
    return 'stable'

def analyze_misclassifications(corrections: List[Dict]) -> Dict:
    """Analyze correction patterns to identify systematic issues."""
    account_errors = defaultdict(int)
    vendor_errors = defaultdict(int)
    common_swaps = defaultdict(int)
    
    for c in corrections:
        ai_account = c.get('ai_suggested_account', 'Unknown')
        correct_account = c.get('corrected_to_account', 'Unknown')
        vendor = c.get('vendor_name', 'Unknown')
        
        account_errors[ai_account] += 1
        vendor_errors[vendor] += 1
        swap_key = f'{ai_account} -> {correct_account}'
        common_swaps[swap_key] += 1
    
    return {
        'top_misclassified_accounts': sorted(
            [{'account': k, 'error_count': v} for k, v in account_errors.items()],
            key=lambda x: x['error_count'], reverse=True
        )[:5],
        'top_misclassified_vendors': sorted(
            [{'vendor': k, 'error_count': v} for k, v in vendor_errors.items()],
            key=lambda x: x['error_count'], reverse=True
        )[:5],
        'common_swaps': sorted(
            [{'swap': k, 'count': v} for k, v in common_swaps.items()],
            key=lambda x: x['count'], reverse=True
        )[:5]
    }

def generate_weekly_report(client_id: str, transactions: List[Dict], corrections: List[Dict], previous_accuracy: List[float]) -> WeeklyMetrics:
    today = datetime.date.today()
    week_start = today - datetime.timedelta(days=7)
    
    total = len(transactions)
    corrected = len(corrections)
    flagged = len([t for t in transactions if t.get('status') == 'flagged'])
    auto_correct = total - corrected - flagged
    new_vendors = len(set(t.get('vendor_name') for t in transactions if t.get('is_new_vendor', False)))
    
    accuracy = calculate_accuracy(auto_correct, corrected, total)
    exception_rate = (corrected + flagged) / total if total > 0 else 0
    trend = detect_trend(accuracy, previous_accuracy)
    
    misclass = analyze_misclassifications(corrections)
    
    alerts = []
    if accuracy < MONITOR_CONFIG['accuracy_threshold_critical']:
        alerts.append(f'CRITICAL: Classification accuracy {accuracy:.1%} is below {MONITOR_CONFIG["accuracy_threshold_critical"]:.0%} threshold')
    elif accuracy < MONITOR_CONFIG['accuracy_threshold_warning']:
        alerts.append(f'WARNING: Classification accuracy {accuracy:.1%} is below {MONITOR_CONFIG["accuracy_threshold_warning"]:.0%} threshold')
    if exception_rate > MONITOR_CONFIG['exception_rate_threshold']:
        alerts.append(f'WARNING: Exception rate {exception_rate:.1%} exceeds {MONITOR_CONFIG["exception_rate_threshold"]:.0%} threshold')
    if trend == 'declining':
        alerts.append('WARNING: Accuracy trend is declining over the past 3 weeks')
    
    return WeeklyMetrics(
        period_start=week_start.isoformat(),
        period_end=today.isoformat(),
        client_id=client_id,
        total_transactions=total,
        auto_classified=auto_correct,
        manually_corrected=corrected,
        flagged_for_review=flagged,
        new_vendors_encountered=new_vendors,
        accuracy_rate=accuracy,
        exception_rate=exception_rate,
        top_misclassified_accounts=misclass['top_misclassified_accounts'],
        top_misclassified_vendors=misclass['top_misclassified_vendors'],
        accuracy_trend=trend,
        alerts=alerts
    )

def format_report_email(metrics: WeeklyMetrics) -> str:
    status = 'HEALTHY' if not metrics.alerts else 'ATTENTION NEEDED'
    body = f"""
    AI Transaction Classification — Weekly Report
    ================================================
    Client: {metrics.client_id}
    Period: {metrics.period_start} to {metrics.period_end}
    Status: {status}
    
    METRICS:
    - Total transactions processed: {metrics.total_transactions}
    - Auto-classified correctly: {metrics.auto_classified} ({metrics.accuracy_rate:.1%})
    - Manually corrected: {metrics.manually_corrected}
    - Flagged for review: {metrics.flagged_for_review}
    - New vendors encountered: {metrics.new_vendors_encountered}
    - Exception rate: {metrics.exception_rate:.1%}
    - Accuracy trend: {metrics.accuracy_trend}
    
    TOP MISCLASSIFIED ACCOUNTS:
    {json.dumps(metrics.top_misclassified_accounts, indent=2)}
    
    TOP MISCLASSIFIED VENDORS:
    {json.dumps(metrics.top_misclassified_vendors, indent=2)}
    
    ALERTS:
    {'No alerts — system performing within expected parameters.' if not metrics.alerts else chr(10).join('  ⚠ ' + a for a in metrics.alerts)}
    
    RECOMMENDED ACTIONS:
    {'None required.' if not metrics.alerts else 'Review alerts above. Consider: updating classification rules for problematic vendors, reviewing CoA naming for ambiguous accounts, or scheduling a model retraining session with the bookkeeper.'}
    """
    return body

# Entry point: Run weekly via cron
# 0 8 * * 1 python3 /opt/msp/classification_monitor.py
# For each client entity, pull data from Booke AI API + QBO/Xero, generate report, send email alerts

# New Vendor Classification Prompt Template
# Used when AI encounters a vendor not previously seen in the client's transaction history
# Variables in {brackets} are populated dynamically from the transaction and client context

SYSTEM_PROMPT = """
You are an expert bookkeeper classifying financial transactions for a {client_business_type} business.
Your task is to assign each transaction to the most appropriate account in the client's chart of accounts.

RULES:
1. You must select from ONLY the accounts listed below — never invent new accounts.
2. Consider the vendor name, transaction amount, and description to determine the correct account.
3. If the transaction could map to multiple accounts, choose the most specific one.
4. For ambiguous transactions, indicate low confidence so a human can review.
5. Never classify a transaction to an equity or liability account without high confidence.
6. Payroll-related transactions from known payroll providers (Gusto, ADP, Paychex) should map to Payroll Expense.
7. Revenue/income deposits should only map to revenue accounts if the source is clearly customer payments.
8. Bank transfers between the client's own accounts should be classified as 'Transfer' not as expense or income.

CLIENT'S CHART OF ACCOUNTS:
{chart_of_accounts_json}

RESPOND IN THIS EXACT JSON FORMAT:
{{
  "account_code": "the account number",
  "account_name": "the full account name",
  "confidence": 0.0 to 1.0,
  "reasoning": "brief explanation of why this account was selected",
  "alternative_account": "second-best account if confidence < 0.85, otherwise null",
  "flags": ["list of any concerns, e.g., 'possible_personal_expense', 'may_need_split', 'new_vendor'"]
}}
"""

USER_PROMPT = """
Classify this transaction:

- Vendor/Payee: {vendor_name}
- Amount: {amount} ({transaction_type})
- Date: {transaction_date}
- Description from bank: {bank_description}
- MCC Code (if available): {mcc_code}
- Bank Account: {bank_account_name}

Additional context:
- Client business type: {client_business_type}
- Client industry: {client_industry}
- Similar vendors previously classified:
{similar_vendor_classifications}

What account should this transaction be classified to?
"""

# Example populated prompt:
# SYSTEM: ... (with client's actual CoA)
# USER:
# Classify this transaction:
# - Vendor/Payee: CANVA PTY LTD
# - Amount: $155.88 (debit)
# - Date: 2025-01-10
# - Description from bank: CANVA PTY LTD SYDNEY AU
# - MCC Code: 5734 (Computer Software Stores)
# - Bank Account: Chase Business Checking
# Additional context:
# - Client business type: Marketing Agency
# - Client industry: Professional Services
# - Similar vendors previously classified:
#   Adobe Inc -> 6210 Software & Technology (confidence: 0.98)
#   Figma Inc -> 6210 Software & Technology (confidence: 0.95)
#
# Expected response:
# {
#   "account_code": "6210",
#   "account_name": "6210 - Software & Technology",
#   "confidence": 0.95,
#   "reasoning": "Canva is a graphic design SaaS platform. Similar to Adobe and Figma which are classified to Software & Technology. MCC code 5734 confirms software category.",
#   "alternative_account": null,
#   "flags": ["new_vendor"]
# }

# QBO Bank Rule Sync Automation
# Reads classification rules from YAML config and creates/updates matching Bank Rules in QBO
# Uses QuickBooks Online API v3
# Requires: intuitlib, requests, pyyaml

import yaml
import requests
import json
from datetime import datetime

class QBOBankRuleSync:
    BASE_URL = 'https://quickbooks.api.intuit.com/v3/company'
    
    def __init__(self, realm_id: str, access_token: str, config_path: str):
        self.realm_id = realm_id
        self.access_token = access_token
        self.headers = {
            'Authorization': f'Bearer {access_token}',
            'Accept': 'application/json',
            'Content-Type': 'application/json'
        }
        with open(config_path, 'r') as f:
            self.config = yaml.safe_load(f)
    
    def get_accounts(self) -> dict:
        """Fetch chart of accounts to map names to QBO IDs."""
        url = f'{self.BASE_URL}/{self.realm_id}/query'
        query = "SELECT * FROM Account WHERE Active = true MAXRESULTS 200"
        resp = requests.get(url, headers=self.headers, params={'query': query})
        resp.raise_for_status()
        accounts = resp.json().get('QueryResponse', {}).get('Account', [])
        # Build lookup: account_name -> account_id
        return {a['FullyQualifiedName']: a['Id'] for a in accounts}
    
    def get_existing_rules(self) -> list:
        """Fetch existing bank rules from QBO."""
        # Note: QBO API doesn't have a direct Bank Rules endpoint as of 2025.
        # Bank rules are managed through the UI. This method is a placeholder
        # for when API support is added, or can be replaced with Selenium automation.
        # Current workaround: maintain a local mapping file of created rules.
        try:
            with open(f'/configs/clients/{self.config["client_id"]}/qbo_rules_map.json', 'r') as f:
                return json.load(f)
        except FileNotFoundError:
            return []
    
    def generate_rule_instructions(self) -> str:
        """Generate human-readable instructions for manual Bank Rule creation in QBO UI.
        
        Since QBO API doesn't support Bank Rules programmatically, this generates
        step-by-step instructions for the MSP technician to create rules manually.
        """
        accounts = self.get_accounts()
        instructions = []
        instructions.append('=' * 60)
        instructions.append('QBO BANK RULE CREATION INSTRUCTIONS')
        instructions.append(f'Client: {self.config["client_name"]}')
        instructions.append(f'Generated: {datetime.now().isoformat()}')
        instructions.append('=' * 60)
        instructions.append('')
        instructions.append('Navigation: QBO > Banking > Rules > Add Rule')
        instructions.append('')
        
        for rule in self.config.get('rules', []):
            conditions = rule.get('conditions', {})
            action = rule.get('action', {})
            
            if 'vendor_contains' not in conditions:
                continue  # Skip MCC-only rules (QBO doesn't support MCC in rules)
            
            instructions.append(f'--- Rule: {rule["name"]} (ID: {rule["rule_id"]}) ---')
            instructions.append(f'  Apply to: {"Money Out" if conditions.get("transaction_type") == "debit" else "Money In"}')
            
            for i, vendor_pattern in enumerate(conditions['vendor_contains']):
                instructions.append(f'  Condition {i+1}: Description CONTAINS "{vendor_pattern}"')
            
            if 'amount_range' in conditions:
                amt = conditions['amount_range']
                if amt.get('min') and amt.get('max'):
                    instructions.append(f'  Amount: Between ${amt["min"]} and ${amt["max"]}')
            
            instructions.append(f'  Assign to: Category = "{action.get("account", "UNSET")}"')
            
            if action.get('class'):
                instructions.append(f'  Class: "{action["class"]}"')
            
            if action.get('memo'):
                instructions.append(f'  Memo: "{action["memo"]}"')
            
            auto_add = 'Yes' if action.get('auto_post', False) else 'No'
            instructions.append(f'  Automatically add: {auto_add}')
            instructions.append('')
        
        return '\n'.join(instructions)

# Usage:
# sync = QBOBankRuleSync(
#     realm_id='1234567890',
#     access_token='eyJ...',
#     config_path='/configs/clients/acme-corp-001/classification_rules.yaml'
# )
# print(sync.generate_rule_instructions())
# Output: Step-by-step instructions for technician to create each rule in QBO UI