plugin-financials/repositories/GeneralLedgerRepository.js

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

/**
 * General Ledger Repository
 * 
 * Manages general ledger functionality by aggregating transaction lines by account.
 * Provides running balances, account history, and ledger views.
 */
class GeneralLedgerRepository extends BaseFinancialRepository {
  constructor() {
    super('pg_fn_transaction_lines'); // Work with transaction_lines for ledger
    this.transactionRepository = null;
    this.transactionLineRepository = null;
    
    // Lazy load to avoid circular dependencies
    this.getRepositories();
  }

  /**
   * Get required repositories
   */
  getRepositories() {
    if (!this.transactionRepository) {
      const { transactionRepository, transactionLineRepository } = require('./index');
      this.transactionRepository = transactionRepository;
      this.transactionLineRepository = transactionLineRepository;
    }
  }

  /**
   * Get ledger entries for an account with running balance
   * @param {string} accountId - Account ID
   * @param {string} siteId - Site ID
   * @param {Object} options - Date range and filter options
   * @returns {Promise<Array>} Array of ledger entries with running balance
   */
  async getAccountLedger(accountId, siteId, options = {}) {
    try {
      const { startDate, endDate } = options;

      // Get all transaction lines for this account within the date range
      const query = {
        account_id: accountId
      };

      // If we have site_id in transaction_lines, we can filter by it
      // Otherwise, we need to join with transactions table
      const allLines = await this.transactionLineRepository.findAll(query);

      // Get related transactions to filter by site_id and date
      const linesWithTransactions = await Promise.all(
        allLines.map(async (line) => {
          const transaction = await this.transactionRepository.findById(line.transaction_id);
          return { ...line, transaction };
        })
      );

      // Filter by site_id and date range
      let filteredLines = linesWithTransactions.filter(line => {
        if (line.transaction?.site_id !== siteId) {
          return false;
        }
        if (startDate && line.transaction?.transaction_date < startDate) {
          return false;
        }
        if (endDate && line.transaction?.transaction_date > endDate) {
          return false;
        }
        return true;
      });

      // Calculate running balance
      let runningBalance = 0;
      const ledgerEntries = filteredLines.map(line => {
        const debitAmount = parseFloat(line.debit_amount || 0);
        const creditAmount = parseFloat(line.credit_amount || 0);
        
        // Calculate balance change (debits increase assets, credits decrease)
        // For equity/revenue/liabilities it's the opposite
        const balanceChange = debitAmount - creditAmount;
        runningBalance += balanceChange;

        return {
          id: line.id,
          transaction_id: line.transaction_id,
          transaction_date: line.transaction?.transaction_date,
          description: line.description || line.transaction?.description,
          debit_amount: debitAmount,
          credit_amount: creditAmount,
          balance_change: balanceChange,
          running_balance: runningBalance,
          reference_number: line.transaction?.reference_number,
          transaction_type: line.transaction?.transaction_type
        };
      });

      return ledgerEntries;
    } catch (error) {
      throw new Error(`Failed to get account ledger: ${error.message}`);
    }
  }

  /**
   * Get account balance summary
   * @param {string} accountId - Account ID
   * @param {string} siteId - Site ID
   * @param {Object} options - Date range options
   * @returns {Promise<Object>} Account balance summary
   */
  async getAccountBalance(accountId, siteId, options = {}) {
    try {
      const { startDate, endDate } = options;

      const ledger = await this.getAccountLedger(accountId, siteId, options);

      // Calculate opening balance (balance before start date)
      const openingBalanceQuery = {
        account_id: accountId
      };
      const allLines = await this.transactionLineRepository.findAll(openingBalanceQuery);
      
      const linesBeforePeriod = [];
      if (startDate) {
        for (const line of allLines) {
          const transaction = await this.transactionRepository.findById(line.transaction_id);
          if (transaction?.site_id === siteId && transaction?.transaction_date < startDate) {
            linesBeforePeriod.push({ line, transaction });
          }
        }
      }

      const openingBalance = linesBeforePeriod.reduce((balance, item) => {
        const debitAmount = parseFloat(item.line.debit_amount || 0);
        const creditAmount = parseFloat(item.line.credit_amount || 0);
        return balance + (debitAmount - creditAmount);
      }, 0);

      // Calculate totals for the period
      const totalDebits = ledger.reduce((sum, entry) => sum + entry.debit_amount, 0);
      const totalCredits = ledger.reduce((sum, entry) => sum + entry.credit_amount, 0);
      const closingBalance = openingBalance + (totalDebits - totalCredits);

      return {
        account_id: accountId,
        site_id: siteId,
        start_date: startDate || null,
        end_date: endDate || null,
        opening_balance: openingBalance,
        total_debits: totalDebits,
        total_credits: totalCredits,
        closing_balance: closingBalance,
        transaction_count: ledger.length
      };
    } catch (error) {
      throw new Error(`Failed to get account balance: ${error.message}`);
    }
  }

  /**
   * Get general ledger for all accounts with balances
   * @param {string} siteId - Site ID
   * @param {Object} options - Filter options
   * @returns {Promise<Array>} Array of account ledger summaries
   */
  async getGeneralLedger(siteId, options = {}) {
    try {
      const { ChartOfAccountsRepository } = require('./index');
      const chartOfAccountsRepository = new ChartOfAccountsRepository();

      // Get all active accounts for this site
      const accounts = await chartOfAccountsRepository.findAll({ 
        site_id: siteId, 
        is_active: true 
      });

      // Get balance for each account
      const ledgerAccounts = await Promise.all(
        accounts.map(async (account) => {
          const balance = await this.getAccountBalance(account.id, siteId, options);
          return {
            account_id: account.id,
            account_code: account.account_code,
            account_name: account.account_name,
            account_type: account.account_type,
            opening_balance: balance.opening_balance,
            total_debits: balance.total_debits,
            total_credits: balance.total_credits,
            closing_balance: balance.closing_balance,
            transaction_count: balance.transaction_count
          };
        })
      );

      return ledgerAccounts;
    } catch (error) {
      throw new Error(`Failed to get general ledger: ${error.message}`);
    }
  }

  /**
   * Get trial balance
   * @param {string} siteId - Site ID
   * @param {Object} options - Filter options
   * @returns {Promise<Object>} Trial balance report
   */
  async getTrialBalance(siteId, options = {}) {
    try {
      const generalLedger = await this.getGeneralLedger(siteId, options);

      const trialBalance = {
        site_id: siteId,
        report_date: options.endDate || new Date().toISOString().split('T')[0],
        accounts: generalLedger,
        totals: {
          total_debits: generalLedger.reduce((sum, account) => sum + account.total_debits, 0),
          total_credits: generalLedger.reduce((sum, account) => sum + account.total_credits, 0),
          net_balance: generalLedger.reduce((sum, account) => sum + account.closing_balance, 0)
        }
      };

      return trialBalance;
    } catch (error) {
      throw new Error(`Failed to get trial balance: ${error.message}`);
    }
  }
}

module.exports = GeneralLedgerRepository;