<?php
/**
 * Client Debt Management Controller
 * 
 * This class handles all operations related to client debts including:
 * - Adding debt transactions
 * - Displaying debt history
 * - Adding debit/credit notes
 * - Generating reports
 */
class ClientDebtController {
    private $conn;
    
    /**
     * Constructor
     * 
     * @param mysqli $conn The database connection
     */
    public function __construct($conn) {
        $this->conn = $conn;
    }
    
    /**
     * Get total debt for a customer
     * 
     * @param int $customerId The customer ID
     * @param string $currency Optional currency filter
     * @return array The debt data
     */
    public function getCustomerDebt($customerId, $currency = null) {
        $debts = [];
        
        try {
            if ($currency) {
                $sql = "SELECT * FROM client_debts 
                        WHERE customer_id = ? AND currency = ?";
                $stmt = $this->conn->prepare($sql);
                $stmt->bind_param('is', $customerId, $currency);
            } else {
                $sql = "SELECT * FROM client_debts 
                        WHERE customer_id = ?";
                $stmt = $this->conn->prepare($sql);
                $stmt->bind_param('i', $customerId);
            }
            
            $stmt->execute();
            $result = $stmt->get_result();
            
            while ($row = $result->fetch_assoc()) {
                $debts[] = $row;
            }
            
            return $debts;
            
        } catch (Exception $e) {
            error_log("Error in getCustomerDebt: " . $e->getMessage());
            return [];
        }
    }
    
    /**
     * Get debt transactions for a customer
     * 
     * @param int $customerId The customer ID
     * @param string $currency Optional currency filter
     * @param string $startDate Optional start date filter
     * @param string $endDate Optional end date filter
     * @param int $limit Optional limit on number of transactions
     * @return array The transaction data
     */
    public function getDebtTransactions($customerId, $currency = null, $startDate = null, $endDate = null, $limit = null) {
        $transactions = [];
        
        try {
            $sql = "SELECT dt.*, 
                          u.username as created_by,
                          c.first_name,
                          c.last_name 
                    FROM debt_transactions dt
                    LEFT JOIN users u ON dt.user_id = u.user_id
                    LEFT JOIN customers c ON dt.customer_id = c.customer_id
                    WHERE dt.customer_id = ?";
            $params = [$customerId];
            $types = "i";
            
            if ($currency) {
                $sql .= " AND dt.currency = ?";
                $params[] = $currency;
                $types .= "s";
            }
            
            if ($startDate) {
                $sql .= " AND dt.transaction_date >= ?";
                $params[] = $startDate;
                $types .= "s";
            }
            
            if ($endDate) {
                $sql .= " AND dt.transaction_date <= ?";
                $params[] = $endDate;
                $types .= "s";
            }
            
            $sql .= " ORDER BY dt.transaction_date DESC, dt.created_at DESC";
            
            if ($limit) {
                $sql .= " LIMIT ?";
                $params[] = $limit;
                $types .= "i";
            }
            
            $stmt = $this->conn->prepare($sql);
            $stmt->bind_param($types, ...$params);
            $stmt->execute();
            $result = $stmt->get_result();
            
            while ($row = $result->fetch_assoc()) {
                $transactions[] = $row;
            }
            
            return $transactions;
            
        } catch (Exception $e) {
            error_log("Error in getDebtTransactions: " . $e->getMessage());
            return [];
        }
    }
    
    /**
     * Add a debt transaction and update client debt total
     * 
     * @param array $transactionData The transaction data
     * @return bool True if successful, false otherwise
     */
    public function addDebtTransaction($transactionData) {
        try {
            $this->conn->begin_transaction();
            
            // Insert transaction
            $sql = "INSERT INTO debt_transactions (
                        customer_id, transaction_date, amount, currency,
                        transaction_type, reference_id, notes, user_id
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
            
            $stmt = $this->conn->prepare($sql);
            $stmt->bind_param(
                'isdssssi',
                $transactionData['customer_id'],
                $transactionData['transaction_date'],
                $transactionData['amount'],
                $transactionData['currency'],
                $transactionData['transaction_type'],
                $transactionData['reference_id'],
                $transactionData['notes'],
                $transactionData['user_id']
            );
            
            if (!$stmt->execute()) {
                throw new Exception("Error adding debt transaction: " . $stmt->error);
            }
            
            // Update client debt total - create or update
            $sql = "INSERT INTO client_debts (customer_id, currency, total_debt)
                    VALUES (?, ?, ?)
                    ON DUPLICATE KEY UPDATE
                    total_debt = total_debt + ?";
            
            $stmt = $this->conn->prepare($sql);
            $stmt->bind_param(
                'isdd',
                $transactionData['customer_id'],
                $transactionData['currency'],
                $transactionData['amount'],
                $transactionData['amount']
            );
            
            if (!$stmt->execute()) {
                throw new Exception("Error updating client debt: " . $stmt->error);
            }
            
            $this->conn->commit();
            return true;
            
        } catch (Exception $e) {
            $this->conn->rollback();
            error_log("Error in addDebtTransaction: " . $e->getMessage());
            return false;
        }
    }
    
    /**
     * Add a manual debt adjustment
     * 
     * @param array $adjustmentData The adjustment data
     * @return bool True if successful, false otherwise
     */
    public function addDebtAdjustment($adjustmentData) {
        try {
            return $this->addDebtTransaction([
                'customer_id' => $adjustmentData['customer_id'],
                'transaction_date' => $adjustmentData['transaction_date'],
                'amount' => $adjustmentData['amount'],
                'currency' => $adjustmentData['currency'],
                'transaction_type' => 'adjustment',
                'reference_id' => $adjustmentData['reference_id'] ?? null,
                'notes' => $adjustmentData['notes'],
                'user_id' => $adjustmentData['user_id']
            ]);
            
        } catch (Exception $e) {
            error_log("Error in addDebtAdjustment: " . $e->getMessage());
            return false;
        }
    }
    
    /**
     * Get all customers with their debt totals
     * 
     * @param string $currency Optional currency filter
     * @return array The customer debt data
     */
    public function getAllCustomerDebts($currency = null) {
        try {
            $sql = "SELECT c.customer_id, c.first_name, c.last_name, c.phone_number, 
                           c.email, cd.currency, cd.total_debt, cd.credit_limit,
                           cd.last_updated
                    FROM customers c
                    LEFT JOIN client_debts cd ON c.customer_id = cd.customer_id";
            
            if ($currency) {
                $sql .= " WHERE cd.currency = ?";
                $stmt = $this->conn->prepare($sql);
                $stmt->bind_param('s', $currency);
            } else {
                $stmt = $this->conn->prepare($sql);
            }
            
            $stmt->execute();
            $result = $stmt->get_result();
            
            $customers = [];
            while ($row = $result->fetch_assoc()) {
                $customers[] = $row;
            }
            
            return $customers;
            
        } catch (Exception $e) {
            error_log("Error in getAllCustomerDebts: " . $e->getMessage());
            return [];
        }
    }
    
    /**
     * Update a customer's credit limit
     * 
     * @param int $customerId The customer ID
     * @param string $currency The currency
     * @param float $creditLimit The new credit limit
     * @return bool True if successful, false otherwise
     */
    public function updateCreditLimit($customerId, $currency, $creditLimit) {
        try {
            $sql = "INSERT INTO client_debts (customer_id, currency, credit_limit)
                    VALUES (?, ?, ?)
                    ON DUPLICATE KEY UPDATE
                    credit_limit = ?";
            
            $stmt = $this->conn->prepare($sql);
            $stmt->bind_param(
                'isdd',
                $customerId,
                $currency,
                $creditLimit,
                $creditLimit
            );
            
            return $stmt->execute();
            
        } catch (Exception $e) {
            error_log("Error in updateCreditLimit: " . $e->getMessage());
            return false;
        }
    }
    
    /**
     * Check if a customer has exceeded their credit limit
     * 
     * @param int $customerId The customer ID
     * @param string $currency The currency
     * @return bool|null True if exceeded, false if not, null if no limit set
     */
    public function hasCreditLimitExceeded($customerId, $currency) {
        try {
            $sql = "SELECT total_debt, credit_limit 
                    FROM client_debts
                    WHERE customer_id = ? AND currency = ?";
            
            $stmt = $this->conn->prepare($sql);
            $stmt->bind_param('is', $customerId, $currency);
            $stmt->execute();
            $result = $stmt->get_result();
            
            if ($result->num_rows > 0) {
                $row = $result->fetch_assoc();
                if ($row['credit_limit'] !== null) {
                    return $row['total_debt'] > $row['credit_limit'];
                }
            }
            
            return null; // No limit set
            
        } catch (Exception $e) {
            error_log("Error in hasCreditLimitExceeded: " . $e->getMessage());
            return null;
        }
    }
    
    /**
     * Generate debt statement for a customer
     * 
     * @param int $customerId The customer ID
     * @param string $currency The currency
     * @param string $startDate The start date for the statement
     * @param string $endDate The end date for the statement
     * @return array The statement data
     */
    public function generateDebtStatement($customerId, $currency, $startDate, $endDate) {
        try {
            // Get opening balance (sum of all transactions before start date)
            $openingBalanceSql = "SELECT SUM(amount) as opening_balance
                                  FROM debt_transactions
                                  WHERE customer_id = ?
                                  AND currency = ?
                                  AND transaction_date < ?";
            
            $stmt = $this->conn->prepare($openingBalanceSql);
            $stmt->bind_param('iss', $customerId, $currency, $startDate);
            $stmt->execute();
            $openingResult = $stmt->get_result();
            $openingBalance = $openingResult->fetch_assoc()['opening_balance'] ?? 0;
            
            // Get transactions within date range
            $transactions = $this->getDebtTransactions($customerId, $currency, $startDate, $endDate);
            
            // Calculate running balance
            $runningBalance = $openingBalance;
            foreach ($transactions as &$transaction) {
                $runningBalance += $transaction['amount'];
                $transaction['balance'] = $runningBalance;
            }
            
            return [
                'opening_balance' => $openingBalance,
                'closing_balance' => $runningBalance,
                'transactions' => $transactions,
                'start_date' => $startDate,
                'end_date' => $endDate,
                'currency' => $currency
            ];
            
        } catch (Exception $e) {
            error_log("Error in generateDebtStatement: " . $e->getMessage());
            return [
                'opening_balance' => 0,
                'closing_balance' => 0,
                'transactions' => [],
                'start_date' => $startDate,
                'end_date' => $endDate,
                'currency' => $currency,
                'error' => $e->getMessage()
            ];
        }
    }
    
    /**
     * Get customer details by ID
     * 
     * @param int $customerId The customer ID
     * @return array|null The customer data or null if not found
     */
    public function getCustomerDetails($customerId) {
        try {
            $sql = "SELECT * FROM customers WHERE customer_id = ?";
            $stmt = $this->conn->prepare($sql);
            $stmt->bind_param('i', $customerId);
            $stmt->execute();
            $result = $stmt->get_result();
            
            if ($result->num_rows > 0) {
                return $result->fetch_assoc();
            }
            
            return null;
            
        } catch (Exception $e) {
            error_log("Error in getCustomerDetails: " . $e->getMessage());
            return null;
        }
    }
}
?>