plugin-financials/repositories/AnalyticsRepository.js

const BaseFinancialRepository = require('./BaseFinancialRepository');

/**
 * Analytics Repository
 * 
 * Manages financial analytics and metrics.
 */
class AnalyticsRepository extends BaseFinancialRepository {
  constructor() {
    super('pg_fn_analytics');
  }

  async findBySiteId(siteId) {
    return await this.findAll({ site_id: siteId }, { orderBy: 'metric_date', orderDirection: 'desc' });
  }

  async findByMetric(siteId, metricName) {
    return await this.findAll({ site_id: siteId, metric_name: metricName }, {
      orderBy: 'metric_date',
      orderDirection: 'desc'
    });
  }

  async createAnalytic(analyticData) {
    return await this.create({
      ...analyticData,
      created_at: new Date().toISOString()
    });
  }

  /**
   * Get comprehensive financial analytics
   * @param {string} siteId - Site ID
   * @param {Object} options - Date range options
   * @returns {Promise<Object>} Analytics data
   */
  async getFinancialAnalytics(siteId, options = {}) {
    try {
      const { revenueRepository, expenseRepository } = require('./index');
      const { budgetRepository } = require('./index');
      const { transactionRepository, transactionLineRepository } = require('./index');
      const { chartOfAccountsRepository } = require('./index');

      // Get date range
      const startDate = options.start_date || new Date(new Date().getFullYear(), 0, 1).toISOString().split('T')[0];
      const endDate = options.end_date || new Date().toISOString().split('T')[0];

      // Calculate total revenue
      const revenues = await revenueRepository.findAll({ site_id: siteId });
      let totalRevenue = 0;
      for (const rev of revenues) {
        if (rev.revenue_date >= startDate && rev.revenue_date <= endDate) {
          totalRevenue += parseFloat(rev.amount || 0);
        }
      }

      // Calculate total expenses
      const expenses = await expenseRepository.findAll({ site_id: siteId });
      let totalExpenses = 0;
      for (const exp of expenses) {
        if (exp.expense_date >= startDate && exp.expense_date <= endDate) {
          totalExpenses += parseFloat(exp.amount || 0);
        }
      }

      // Calculate net income
      const netIncome = totalRevenue - totalExpenses;

      // Get budget for variance calculation
      let budgetVariance = null;
      const currentYear = new Date().getFullYear();
      const budgets = await budgetRepository.findAll({ site_id: siteId, fiscal_year: currentYear });
      if (budgets.length > 0) {
        const budget = budgets[0];
        const budgetedAmount = parseFloat(budget.total_amount || 0);
        const actualAmount = totalExpenses;
        budgetVariance = budgetedAmount > 0 ? ((actualAmount - budgetedAmount) / budgetedAmount * 100) : 0;
      }

      // Calculate cash flow (simplified - from transactions)
      const transactions = await transactionRepository.findAll({ site_id: siteId });
      let cashFlow = 0;
      for (const trans of transactions) {
        if (trans.transaction_date >= startDate && trans.transaction_date <= endDate) {
          if (trans.transaction_type === 'income') {
            cashFlow += parseFloat(trans.amount || 0);
          } else if (trans.transaction_type === 'expense') {
            cashFlow -= parseFloat(trans.amount || 0);
          }
        }
      }

      return {
        site_id: siteId,
        period: {
          start_date: startDate,
          end_date: endDate
        },
        total_revenue: totalRevenue,
        total_expenses: totalExpenses,
        net_income: netIncome,
        budget_variance: budgetVariance,
        cash_flow: cashFlow
      };
    } catch (error) {
      throw new Error(`Failed to calculate financial analytics: ${error.message}`);
    }
  }

  /**
   * Get HOA-specific metrics
   * @param {string} siteId - Site ID
   * @returns {Promise<Object>} HOA metrics
   */
  async getHOAMetrics(siteId) {
    try {
      const { unitBalanceRepository, invoiceRepository, paymentRepository } = require('./index');

      // Get all unit balances
      const unitBalances = await unitBalanceRepository.findAll({ site_id: siteId });
      
      let totalOutstanding = 0;
      let totalPaid = 0;
      let unitCount = 0;

      for (const unit of unitBalances) {
        const balance = await unitBalanceRepository.calculateUnitBalance(unit.unit_id, siteId);
        totalOutstanding += parseFloat(balance.current_balance || 0);
        unitCount++;
      }

      // Get all invoices
      const invoices = await invoiceRepository.findAll({ site_id: siteId });
      let paidCount = 0;
      let overdueCount = 0;
      
      for (const invoice of invoices) {
        const amount = parseFloat(invoice.amount || 0);
        if (invoice.status === 'paid') {
          totalPaid += amount;
          paidCount++;
        } else if (invoice.status === 'overdue') {
          overdueCount++;
        }
      }

      // Get recent payments
      const payments = await paymentRepository.findAll({ site_id: siteId });
      const recentPayments = payments
        .filter(p => p.payment_date >= new Date(new Date().setMonth(new Date().getMonth() - 1)).toISOString().split('T')[0])
        .reduce((sum, p) => sum + parseFloat(p.payment_amount || 0), 0);

      return {
        site_id: siteId,
        unit_metrics: {
          total_units: unitCount,
          total_outstanding: totalOutstanding,
          average_outstanding: unitCount > 0 ? totalOutstanding / unitCount : 0
        },
        invoice_metrics: {
          total_invoices: invoices.length,
          paid_invoices: paidCount,
          overdue_invoices: overdueCount,
          total_paid: totalPaid
        },
        recent_activity: {
          payments_last_month: recentPayments
        }
      };
    } catch (error) {
      throw new Error(`Failed to calculate HOA metrics: ${error.message}`);
    }
  }
}

module.exports = AnalyticsRepository;