<?php
require_once __DIR__ . '/BaseModel.php';

/**
 * Financial Reporting & Bookkeeping Class
 * Handles financial reports, analytics, and bookkeeping
 */
class FinancialReport {
    private $db;
    
    public function __construct() {
        $this->db = Database::getInstance();
    }
    
    /**
     * Get revenue report
     */
    public function getRevenueReport($propertyId = null, $startDate = null, $endDate = null) {
        $sql = "SELECT 
                    DATE(transaction_date) as date,
                    SUM(amount) as total_revenue,
                    SUM(CASE WHEN payment_method = 'M-Pesa' THEN amount ELSE 0 END) as mpesa_revenue,
                    SUM(CASE WHEN payment_method = 'Bank Transfer' THEN amount ELSE 0 END) as bank_revenue,
                    SUM(CASE WHEN payment_method = 'Credit Card' THEN amount ELSE 0 END) as card_revenue,
                    SUM(CASE WHEN payment_method = 'Cash' THEN amount ELSE 0 END) as cash_revenue,
                    COUNT(*) as transaction_count
                FROM payments 
                WHERE status = 'verified'";
        
        $params = [];
        
        if ($propertyId) {
            $sql .= " AND invoice_id IN (SELECT id FROM invoices WHERE property_id = ?)";
            $params[] = $propertyId;
        }
        
        if ($startDate) {
            $sql .= " AND transaction_date >= ?";
            $params[] = $startDate;
        }
        
        if ($endDate) {
            $sql .= " AND transaction_date <= ?";
            $params[] = $endDate;
        }
        
        $sql .= " GROUP BY DATE(transaction_date) ORDER BY date DESC";
        
        $result = $this->db->query($sql, $params);
        $revenue = [];
        
        while ($row = $result->fetch_assoc()) {
            $revenue[] = $row;
        }
        
        return $revenue;
    }
    
    /**
     * Get expense report
     */
    public function getExpenseReport($propertyId = null, $startDate = null, $endDate = null) {
        $sql = "SELECT 
                    DATE(incurred_date) as date,
                    category,
                    SUM(amount) as total_expense,
                    COUNT(*) as expense_count
                FROM expenses 
                WHERE status = 'paid'";
        
        $params = [];
        
        if ($propertyId) {
            $sql .= " AND property_id = ?";
            $params[] = $propertyId;
        }
        
        if ($startDate) {
            $sql .= " AND incurred_date >= ?";
            $params[] = $startDate;
        }
        
        if ($endDate) {
            $sql .= " AND incurred_date <= ?";
            $params[] = $endDate;
        }
        
        $sql .= " GROUP BY DATE(incurred_date), category ORDER BY date DESC";
        
        $result = $this->db->query($sql, $params);
        $expenses = [];
        
        while ($row = $result->fetch_assoc()) {
            $expenses[] = $row;
        }
        
        return $expenses;
    }
    
    /**
     * Get profit and loss statement
     */
    public function getProfitLoss($propertyId = null, $startDate = null, $endDate = null) {
        $revenue = $this->getTotalRevenue($propertyId, $startDate, $endDate);
        $expenses = $this->getTotalExpenses($propertyId, $startDate, $endDate);
        
        return [
            'revenue' => $revenue,
            'expenses' => $expenses,
            'profit' => $revenue - $expenses,
            'profit_margin' => $revenue > 0 ? (($revenue - $expenses) / $revenue) * 100 : 0
        ];
    }
    
    /**
     * Get total revenue
     */
    public function getTotalRevenue($propertyId = null, $startDate = null, $endDate = null) {
        $sql = "SELECT SUM(amount) as total FROM payments WHERE status = 'verified'";
        $params = [];
        
        if ($propertyId) {
            $sql .= " AND invoice_id IN (SELECT id FROM invoices WHERE property_id = ?)";
            $params[] = $propertyId;
        }
        
        if ($startDate) {
            $sql .= " AND transaction_date >= ?";
            $params[] = $startDate;
        }
        
        if ($endDate) {
            $sql .= " AND transaction_date <= ?";
            $params[] = $endDate;
        }
        
        $result = $this->db->query($sql, $params);
        $row = $result->fetch_assoc();
        
        return (float)($row['total'] ?? 0.00);
    }
    
    /**
     * Get total expenses
     */
    public function getTotalExpenses($propertyId = null, $startDate = null, $endDate = null) {
        $sql = "SELECT SUM(amount) as total FROM expenses WHERE status = 'paid'";
        $params = [];
        
        if ($propertyId) {
            $sql .= " AND property_id = ?";
            $params[] = $propertyId;
        }
        
        if ($startDate) {
            $sql .= " AND incurred_date >= ?";
            $params[] = $startDate;
        }
        
        if ($endDate) {
            $sql .= " AND incurred_date <= ?";
            $params[] = $endDate;
        }
        
        $result = $this->db->query($sql, $params);
        $row = $result->fetch_assoc();
        
        return (float)($row['total'] ?? 0.00);
    }
    
    /**
     * Get outstanding receivables
     */
    public function getOutstandingReceivables($propertyId = null) {
        $sql = "SELECT 
                    SUM(balance) as total_outstanding,
                    COUNT(*) as invoice_count,
                    SUM(CASE WHEN status = 'overdue' THEN balance ELSE 0 END) as overdue_amount,
                    SUM(CASE WHEN status = 'overdue' THEN 1 ELSE 0 END) as overdue_count
                FROM invoices 
                WHERE status IN ('unpaid', 'partial', 'overdue')";
        
        $params = [];
        
        if ($propertyId) {
            $sql .= " AND property_id = ?";
            $params[] = $propertyId;
        }
        
        $result = $this->db->query($sql, $params);
        return $result->fetch_assoc();
    }
    
    /**
     * Get collection rate
     */
    public function getCollectionRate($propertyId = null, $startDate = null, $endDate = null) {
        $sql = "SELECT 
                    SUM(total_amount) as total_invoiced,
                    SUM(paid_amount) as total_collected
                FROM invoices 
                WHERE 1=1";
        
        $params = [];
        
        if ($propertyId) {
            $sql .= " AND property_id = ?";
            $params[] = $propertyId;
        }
        
        if ($startDate) {
            $sql .= " AND generated_date >= ?";
            $params[] = $startDate;
        }
        
        if ($endDate) {
            $sql .= " AND generated_date <= ?";
            $params[] = $endDate;
        }
        
        $result = $this->db->query($sql, $params);
        $row = $result->fetch_assoc();
        
        $totalInvoiced = (float)($row['total_invoiced'] ?? 0.00);
        $totalCollected = (float)($row['total_collected'] ?? 0.00);
        
        $collectionRate = $totalInvoiced > 0 ? ($totalCollected / $totalInvoiced) * 100 : 0;
        
        return [
            'total_invoiced' => $totalInvoiced,
            'total_collected' => $totalCollected,
            'collection_rate' => round($collectionRate, 2)
        ];
    }
    
    /**
     * Get monthly summary
     */
    public function getMonthlySummary($propertyId = null, $year = null) {
        $year = $year ?? date('Y');
        
        $sql = "SELECT 
                    MONTH(transaction_date) as month,
                    SUM(amount) as revenue
                FROM payments 
                WHERE status = 'verified' AND YEAR(transaction_date) = ?";
        
        $params = [$year];
        
        if ($propertyId) {
            $sql .= " AND invoice_id IN (SELECT id FROM invoices WHERE property_id = ?)";
            $params[] = $propertyId;
        }
        
        $sql .= " GROUP BY MONTH(transaction_date) ORDER BY month";
        
        $result = $this->db->query($sql, $params);
        $monthly = [];
        
        while ($row = $result->fetch_assoc()) {
            $monthly[$row['month']] = $row['revenue'];
        }
        
        return $monthly;
    }
}
?>
