Financial Reporting & Payment Management - User Story

Story Overview

Story ID: FIN-REP-001
Epic: Billing & Partners Management Enhancement
Story Points: 21
Priority: High
Type: Brownfield Enhancement

User Story

As a Financial Manager/Administrator
I want comprehensive financial reporting and payment management capabilities
So that I can track revenue, monitor outstanding balances, reconcile payments, analyze financial performance, and ensure accurate financial operations across both client invoices and partner commissions.

Acceptance Criteria

AC1: Financial Dashboard Implementation

• GIVEN I am a financial manager
• WHEN I access the financial dashboard
• THEN I should see:
  • Total revenue (HT/TTC) with current month vs previous month comparison
  • Outstanding balances by status (A_FACTURER, EN_ATTENTE, REGLEE)
  • Partner commission totals with payment status breakdown
  • Aging analysis (0-30, 31-60, 61-90, 90+ days)
  • Key financial KPIs and trends visualization

AC2: Comprehensive Financial Reports

• GIVEN I need detailed financial analysis
• WHEN I generate financial reports
• THEN I should be able to:
  • Generate revenue reports by period, client, partner, or reclamation type
  • Export aging reports showing overdue invoices and partner payments
  • Create commission reports showing partner performance and payment status
  • Generate reconciliation reports comparing invoice amounts vs payments
  • Filter all reports by date range, status, client, or partner

AC3: Payment Reconciliation Workflows

• GIVEN I need to reconcile payments
• WHEN I use the payment reconciliation system
• THEN I should be able to:
  • Match payments to invoices with automatic suggestions
  • Handle partial payments with remaining balance tracking
  • Process bulk payment imports with validation
  • Record payment methods and reference numbers
  • Generate reconciliation audit trails

AC4: Aging Analysis and Alerts

• GIVEN I need to monitor overdue accounts
• WHEN I access aging analysis
• THEN I should see:
  • Automated aging buckets (0-30, 31-60, 61-90, 90+ days)
  • Client-specific aging with contact information for follow-up
  • Partner payment aging with commission details
  • Alert notifications for invoices approaching or exceeding payment terms
  • Aging trend analysis showing improvement or deterioration

AC5: Financial Performance Analytics

• GIVEN I need business intelligence
• WHEN I analyze financial performance
• THEN I should have:
  • Revenue trends with forecasting capabilities
  • Partner profitability analysis showing top-performing partners
  • Client profitability analysis showing highest-value clients
  • Commission efficiency metrics and partner ROI analysis
  • Financial performance comparisons across time periods

Technical Requirements

Database Schema Enhancements

-- Add financial reporting fields to existing tables
ALTER TABLE factures ADD COLUMN payment_method VARCHAR(50);
ALTER TABLE factures ADD COLUMN payment_reference VARCHAR(100);
ALTER TABLE factures ADD COLUMN payment_notes TEXT;
ALTER TABLE factures ADD COLUMN aging_bucket VARCHAR(20);

ALTER TABLE factures_partenaires ADD COLUMN payment_method VARCHAR(50);
ALTER TABLE factures_partenaires ADD COLUMN payment_reference VARCHAR(100);
ALTER TABLE factures_partenaires ADD COLUMN commission_rate DECIMAL(5,4);
ALTER TABLE factures_partenaires ADD COLUMN aging_bucket VARCHAR(20);

-- Create financial reporting tables
CREATE TABLE payment_reconciliation (
  id SERIAL PRIMARY KEY,
  type VARCHAR(20) NOT NULL, -- 'FACTURE' or 'PARTNER'
  reference_id INTEGER NOT NULL,
  payment_amount DECIMAL(10,2) NOT NULL,
  payment_date DATE NOT NULL,
  payment_method VARCHAR(50),
  payment_reference VARCHAR(100),
  reconciled_by INTEGER REFERENCES users(id),
  reconciled_at TIMESTAMP DEFAULT NOW(),
  notes TEXT
);

CREATE TABLE financial_snapshots (
  id SERIAL PRIMARY KEY,
  snapshot_date DATE NOT NULL,
  total_revenue_ht DECIMAL(12,2),
  total_revenue_ttc DECIMAL(12,2),
  outstanding_invoices DECIMAL(12,2),
  partner_commissions_pending DECIMAL(12,2),
  partner_commissions_paid DECIMAL(12,2),
  created_at TIMESTAMP DEFAULT NOW()
);

API Endpoints

Financial Dashboard

GET /api/financial/dashboard
Response: {
  revenue: {
    currentMonth: { ht: number, ttc: number },
    previousMonth: { ht: number, ttc: number },
    growth: number
  },
  outstanding: {
    aFacturer: number,
    enAttente: number,
    total: number
  },
  partnerCommissions: {
    pending: number,
    paid: number,
    total: number
  },
  aging: {
    current: number,
    days30: number,
    days60: number,
    days90: number,
    over90: number
  }
}

Financial Reports

GET /api/financial/reports/revenue
Query: { startDate, endDate, clientId?, partnerId?, type? }

GET /api/financial/reports/aging
Query: { asOfDate?, clientId?, partnerId? }

GET /api/financial/reports/commissions
Query: { startDate, endDate, partnerId? }

GET /api/financial/reports/reconciliation
Query: { startDate, endDate, status? }

Payment Reconciliation

POST /api/financial/payments/reconcile
Body: {
  type: 'FACTURE' | 'PARTNER',
  referenceId: number,
  amount: number,
  paymentDate: string,
  paymentMethod?: string,
  paymentReference?: string,
  notes?: string
}

GET /api/financial/payments/unreconciled
Query: { type?, olderThan? }

POST /api/financial/payments/bulk-import
Body: { payments: PaymentData[], validateOnly?: boolean }

Service Layer Implementation

FinancialReportingService

@Injectable()
export class FinancialReportingService {
  constructor(
    private prisma: PrismaService,
    private factureService: FactureService,
    private facturePartenaireService: FacturePartenaireService,
  ) {}

  async getDashboardData(): Promise<FinancialDashboard> {
    // Aggregate financial data from existing services
    // Calculate KPIs and trends
    // Return dashboard metrics
  }

  async generateRevenueReport(filters: RevenueReportFilters): Promise<RevenueReport> {
    // Use existing facture queries with financial aggregations
    // Group by specified dimensions (client, partner, period)
    // Calculate revenue metrics and trends
  }

  async generateAgingReport(filters: AgingReportFilters): Promise<AgingReport> {
    // Calculate aging buckets based on invoice/payment dates
    // Use existing relationship data for client/partner context
    // Generate aging analysis with contact information
  }

  async updateAgingBuckets(): Promise<void> {
    // Scheduled job to update aging classifications
    // Preserve existing status workflows
    // Maintain backward compatibility
  }
}

PaymentReconciliationService

@Injectable()
export class PaymentReconciliationService {
  constructor(
    private prisma: PrismaService,
    private factureService: FactureService,
    private facturePartenaireService: FacturePartenaireService,
  ) {}

  async reconcilePayment(data: ReconcilePaymentDto): Promise<PaymentReconciliation> {
    // Update invoice/partner payment status
    // Record reconciliation audit trail
    // Maintain existing status enum integrity
  }

  async suggestMatches(amount: number, date: string): Promise<PaymentMatch[]> {
    // Find potential invoice/partner payment matches
    // Use existing service queries for candidate selection
    // Score matches based on amount and timing
  }

  async processBulkPayments(payments: BulkPaymentDto[]): Promise<BulkReconciliationResult> {
    // Validate payment data against existing invoices
    // Process successful matches
    // Report validation errors and unmatched items
  }
}

Integration Requirements

Existing Service Integration

• FactureService Integration: Extend getFactureStats() with aging analysis
• FacturePartenaireService Integration: Enhance getPartnerInvoiceStats() with commission metrics
• Status Preservation: Maintain existing FactureStatus and FacturePartenaireStatus enum workflows
• Relationship Integrity: Leverage existing Reclamation → Client → ApporteurAffaire relationships

API Compatibility

• Backward Compatibility: All existing endpoints remain unchanged
• Response Extensions: Financial data added to existing responses via optional includes
• Query Enhancement: Existing queries extended with financial filters
• Status Workflow: Integration with existing status transition logic

Test Automation Requirements

Unit Tests

FinancialReportingService Tests

describe('FinancialReportingService', () => {
  describe('getDashboardData', () => {
    it('should aggregate revenue from existing facture data');
    it('should calculate aging buckets correctly');
    it('should include partner commission totals');
    it('should handle empty data sets gracefully');
    it('should cache dashboard calculations for performance');
  });

  describe('generateRevenueReport', () => {
    it('should filter by date range correctly');
    it('should group by client when specified');
    it('should group by partner when specified');
    it('should calculate HT/TTC totals accurately');
    it('should handle partial date ranges');
  });

  describe('generateAgingReport', () => {
    it('should categorize invoices into correct aging buckets');
    it('should include client contact information');
    it('should calculate aging from invoice date vs current date');
    it('should handle null payment dates correctly');
    it('should sort by aging priority (oldest first)');
  });
});

describe('PaymentReconciliationService', () => {
  describe('reconcilePayment', () => {
    it('should update facture status to REGLEE when fully paid');
    it('should maintain EN_ATTENTE status for partial payments');
    it('should update partner invoice status correctly');
    it('should create audit trail record');
    it('should validate payment amount against invoice total');
  });

  describe('suggestMatches', () => {
    it('should find exact amount matches first');
    it('should consider payment date proximity');
    it('should exclude already reconciled invoices');
    it('should return both client and partner invoice matches');
    it('should score matches by relevance');
  });
});

Integration Tests

Financial Dashboard Integration

describe('Financial Dashboard Integration', () => {
  it('should integrate with existing facture data');
  it('should aggregate partner commission data correctly');
  it('should maintain performance with large datasets');
  it('should handle concurrent user access');
  it('should refresh data in real-time');
});

describe('Payment Reconciliation Integration', () => {
  it('should preserve existing invoice workflow states');
  it('should maintain referential integrity');
  it('should handle concurrent payment processing');
  it('should integrate with existing status transitions');
  it('should validate against existing business rules');
});

End-to-End Tests

Financial Reporting E2E

describe('Financial Reporting E2E', () => {
  it('should generate complete revenue report from invoice creation to payment');
  it('should track aging progression over time');
  it('should handle full invoice-to-payment workflow');
  it('should maintain data consistency across services');
  it('should support multi-user financial operations');
});

describe('Payment Reconciliation E2E', () => {
  it('should reconcile payment and update all related entities');
  it('should handle partial payment scenarios correctly');
  it('should maintain audit trail throughout process');
  it('should integrate with existing client/partner relationships');
  it('should preserve backward compatibility');
});

Performance Tests

• Dashboard Load Time: < 2 seconds with 10,000+ invoices
• Report Generation: < 5 seconds for complex reports
• Payment Reconciliation: < 1 second per transaction
• Aging Calculation: Batch process < 10 seconds for full database
• Concurrent Users: Support 20+ simultaneous financial operations

Evolution Tracking

Version 1.0 - Foundation

• Basic financial dashboard with KPIs
• Revenue and aging reports
• Simple payment reconciliation
• Integration with existing invoice systems

Version 1.1 - Enhanced Analytics

• Advanced aging analysis with trends
• Partner profitability analytics
• Automated reconciliation suggestions
• Financial forecasting capabilities

Version 1.2 - Advanced Features

• Bulk payment processing
• Multi-currency support (if needed)
• Financial alert system
• Advanced reporting with visualizations

Audit Trail Requirements

Financial Operation Logging

interface FinancialAuditEvent {
  eventType: 'PAYMENT_RECONCILED' | 'REPORT_GENERATED' | 'AGING_UPDATED';
  userId: number;
  timestamp: Date;
  entityType: 'FACTURE' | 'PARTNER_INVOICE';
  entityId: number;
  oldStatus?: FactureStatus | FacturePartenaireStatus;
  newStatus?: FactureStatus | FacturePartenaireStatus;
  financialAmount?: number;
  metadata: Record<string, any>;
}

Audit Trail Features

• Payment Reconciliation: Track all payment applications and modifications
• Status Changes: Log all financial status transitions with user attribution
• Report Generation: Record report parameters and generation timestamps
• Data Modifications: Track changes to financial amounts and payment data
• User Activities: Maintain comprehensive user action logs for financial operations

Data Integrity & Validation

Financial Data Validation

• Amount Consistency: Validate totals across related invoices and payments
• Status Integrity: Ensure status transitions follow business rules
• Relationship Validation: Verify client/partner/reclamation relationships
• Date Validation: Ensure logical date sequences (invoice → payment)
• Currency Precision: Maintain decimal precision for financial calculations

Error Handling

• Graceful Degradation: Continue operations when non-critical services fail
• Data Recovery: Implement mechanisms to recover from calculation errors
• User Feedback: Provide clear error messages for financial operations
• Rollback Capabilities: Support transaction rollback for failed operations

Monitoring & Alerting

Financial Performance Monitoring

• Revenue Tracking: Monitor daily/weekly/monthly revenue trends
• Outstanding Balance Alerts: Alert when balances exceed thresholds
• Payment Delays: Notify when payments are overdue
• Reconciliation Gaps: Alert on unreconciled payments
• System Performance: Monitor report generation and dashboard load times

Business Intelligence Metrics

• Average Payment Time: Track payment collection efficiency
• Partner Performance: Monitor partner commission and payment patterns
• Client Payment Behavior: Analyze client payment reliability
• Revenue Predictability: Track revenue recognition patterns
• Financial Health Indicators: Monitor overall system financial health

Definition of Done

Functional Requirements

• Financial dashboard displays accurate real-time financial metrics
• Revenue reports generate correctly with filtering and grouping options
• Aging analysis accurately categorizes outstanding invoices and partner payments
• Payment reconciliation updates invoice status and maintains audit trail
• All financial calculations maintain precision and consistency
• Integration with existing invoice and partner systems works seamlessly

Technical Requirements

• All API endpoints documented with Swagger annotations
• Database migrations are backward compatible and reversible
• Service layer maintains existing patterns and interfaces
• Error handling covers all edge cases and provides meaningful feedback
• Performance requirements met under expected load conditions

Quality Assurance

• Unit test coverage > 90% for all financial services
• Integration tests validate end-to-end financial workflows
• Performance tests confirm response time requirements
• Security review completed for financial data access
• Code review approved by senior developers

Documentation & Training

• API documentation updated with financial endpoints
• User guide created for financial reporting features
• Administrator guide includes payment reconciliation procedures
• Database schema changes documented
• Migration procedures documented and tested

---

Story Dependencies:

• Depends On: Story 1 (Automated Invoice Generation) - requires invoice numbering and calculation logic
• Depends On: Story 2 (Partner Commission) - requires partner invoice generation and commission calculation
• Enables: Advanced financial analytics and business intelligence features
• Integrates With: All existing client, site, reclamation, and partner management systems

Estimated Implementation Time: 4-5 sprints (8-10 weeks) Risk Level: Medium - Complex financial calculations and reporting requirements Business Value: High - Enables comprehensive financial management and business intelligence