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

/**
 * Invoice Management Class
 * Handles invoice creation, updates, and invoice-related operations
 */
class Invoice extends BaseModel {
    protected $tableName = 'invoices';
    protected $tenantColumn = null; // Invoices are isolated by tenant_id or property_id
    
    public function __construct() {
        parent::__construct();
    }
    
    /**
     * Create a new invoice
     */
    public function createInvoice($data) {
        // Validate required fields
        $required = ['tenant_id', 'type', 'amount', 'total_amount', 'due_date'];
        foreach ($required as $field) {
            if (empty($data[$field])) {
                throw new Exception("Field {$field} is required");
            }
        }
        
        // Generate invoice number
        $invoiceNumber = $this->generateInvoiceNumber($data['type']);
        
        // Get property_id from lease if lease_id is provided
        $propertyId = $data['property_id'] ?? null;
        if (empty($propertyId) && !empty($data['lease_id'])) {
            $sql = "SELECT u.property_id FROM leases l 
                    INNER JOIN units u ON l.unit_id = u.id 
                    WHERE l.id = ?";
            $result = $this->db->query($sql, [$data['lease_id']]);
            $row = $result->fetch_assoc();
            $propertyId = $row['property_id'] ?? null;
        }
        
        $invoiceData = [
            'invoice_number' => $invoiceNumber,
            'lease_id' => $data['lease_id'] ?? null,
            'tenant_id' => $data['tenant_id'],
            'property_id' => $propertyId,
            'type' => $data['type'],
            'description' => $data['description'] ?? null,
            'amount' => $data['amount'],
            'tax_amount' => $data['tax_amount'] ?? 0.00,
            'discount_amount' => $data['discount_amount'] ?? 0.00,
            'total_amount' => $data['total_amount'],
            'paid_amount' => 0.00,
            'balance' => $data['balance'] ?? $data['total_amount'],
            'due_date' => $data['due_date'],
            'generated_date' => date('Y-m-d'),
            'status' => $data['status'] ?? 'unpaid',
            'is_recurring' => $data['is_recurring'] ?? false
        ];
        
        return $this->create($invoiceData);
    }
    
    /**
     * Update invoice
     */
    public function updateInvoice($id, $data) {
        return $this->update($id, $data);
    }
    
    /**
     * Get invoice with tenant and payment details
     */
    public function getInvoiceDetails($id) {
        $invoice = $this->getById($id);
        
        if (!$invoice) {
            return null;
        }
        
        // Get tenant details
        $sql = "SELECT id, first_name, last_name, email, phone FROM users WHERE id = ?";
        $result = $this->db->query($sql, [$invoice['tenant_id']]);
        $invoice['tenant'] = $result->fetch_assoc();
        
        // Get property details if available
        if ($invoice['property_id']) {
            $propertyModel = new Property();
            $invoice['property'] = $propertyModel->getById($invoice['property_id']);
        }
        
        // Get lease details if available
        if ($invoice['lease_id']) {
            $leaseModel = new Lease();
            $invoice['lease'] = $leaseModel->getById($invoice['lease_id']);
        }
        
        // Get payments for this invoice
        $paymentModel = new Payment();
        $invoice['payments'] = $paymentModel->getPaymentsByInvoice($id);
        
        return $invoice;
    }
    
    /**
     * Get invoices by tenant
     */
    public function getInvoicesByTenant($tenantId, $status = null) {
        $sql = "SELECT * FROM {$this->tableName} WHERE tenant_id = ?";
        $params = [$tenantId];
        
        if ($status) {
            $sql .= " AND status = ?";
            $params[] = $status;
        }
        
        $sql .= " ORDER BY due_date DESC, created_at DESC";
        
        $result = $this->db->query($sql, $params);
        $invoices = [];
        
        while ($row = $result->fetch_assoc()) {
            $invoices[] = $row;
        }
        
        return $invoices;
    }
    
    /**
     * Get invoices by lease
     */
    public function getInvoicesByLease($leaseId) {
        $sql = "SELECT * FROM {$this->tableName} WHERE lease_id = ? ORDER BY due_date DESC";
        $result = $this->db->query($sql, [$leaseId]);
        $invoices = [];
        
        while ($row = $result->fetch_assoc()) {
            $invoices[] = $row;
        }
        
        return $invoices;
    }
    
    /**
     * Get invoices by property
     */
    public function getInvoicesByProperty($propertyId, $status = null) {
        $sql = "SELECT i.*, u.first_name, u.last_name, u.email, u.phone, un.unit_number 
                FROM invoices i 
                INNER JOIN users u ON i.tenant_id = u.id 
                LEFT JOIN leases l ON i.lease_id = l.id 
                LEFT JOIN units un ON l.unit_id = un.id 
                WHERE i.property_id = ?";
        
        $params = [$propertyId];
        
        if ($status) {
            $sql .= " AND i.status = ?";
            $params[] = $status;
        }
        
        $sql .= " ORDER BY i.due_date DESC";
        
        $result = $this->db->query($sql, $params);
        $invoices = [];
        
        while ($row = $result->fetch_assoc()) {
            $invoices[] = $row;
        }
        
        return $invoices;
    }
    
    /**
     * Get overdue invoices
     */
    public function getOverdueInvoices($propertyId = null) {
        $sql = "SELECT i.*, u.first_name, u.last_name, u.email, u.phone 
                FROM invoices i 
                INNER JOIN users u ON i.tenant_id = u.id 
                WHERE i.status IN ('unpaid', 'partial') 
                AND i.due_date < CURDATE()";
        
        $params = [];
        if ($propertyId) {
            $sql .= " AND i.property_id = ?";
            $params[] = $propertyId;
        }
        
        $sql .= " ORDER BY i.due_date ASC";
        
        $result = $this->db->query($sql, $params);
        $invoices = [];
        
        while ($row = $result->fetch_assoc()) {
            $invoices[] = $row;
        }
        
        return $invoices;
    }
    
    /**
     * Generate monthly rent invoices for all active leases
     */
    public function generateMonthlyRentInvoices() {
        // Call stored procedure if exists, otherwise use manual generation
        $sql = "CALL sp_generate_monthly_invoices()";
        
        try {
            $this->db->execute($sql);
            return true;
        } catch (Exception $e) {
            // Fallback to manual generation if procedure doesn't exist
            return $this->generateMonthlyRentInvoicesManual();
        }
    }
    
    /**
     * Manual monthly rent invoice generation
     */
    private function generateMonthlyRentInvoicesManual() {
        $sql = "SELECT l.id as lease_id, l.tenant_id, l.rent_amount, l.due_day, u.property_id 
                FROM leases l 
                INNER JOIN units u ON l.unit_id = u.id 
                WHERE l.status = 'active'";
        
        $result = $this->db->query($sql);
        $invoiceMonth = date('Ym');
        $generated = 0;
        
        while ($lease = $result->fetch_assoc()) {
            // Check if invoice already exists for this month
            $checkSql = "SELECT id FROM invoices 
                        WHERE lease_id = ? AND description LIKE ?";
            $checkResult = $this->db->query($checkSql, [$lease['lease_id'], "%{$invoiceMonth}%"]);
            
            if ($checkResult->num_rows == 0) {
                $dueDate = date('Y-m-' . str_pad($lease['due_day'], 2, '0', STR_PAD_LEFT));
                
                $this->createInvoice([
                    'lease_id' => $lease['lease_id'],
                    'tenant_id' => $lease['tenant_id'],
                    'property_id' => $lease['property_id'],
                    'type' => 'Rent',
                    'description' => 'Rent - ' . date('F Y'),
                    'amount' => $lease['rent_amount'],
                    'total_amount' => $lease['rent_amount'],
                    'balance' => $lease['rent_amount'],
                    'due_date' => $dueDate,
                    'status' => 'unpaid',
                    'is_recurring' => true
                ]);
                
                $generated++;
            }
        }
        
        return $generated;
    }
    
    /**
     * Generate invoice number
     */
    private function generateInvoiceNumber($type) {
        $prefix = strtoupper(substr($type, 0, 3));
        $year = date('Y');
        $month = date('m');
        
        // Get last invoice number for this type
        $sql = "SELECT invoice_number FROM invoices 
                WHERE invoice_number LIKE ? 
                ORDER BY id DESC LIMIT 1";
        
        $pattern = "{$prefix}-{$year}{$month}-%";
        $result = $this->db->query($sql, [$pattern]);
        
        if ($result->num_rows > 0) {
            $lastInvoice = $result->fetch_assoc();
            $lastNumber = (int)substr($lastInvoice['invoice_number'], -4);
            $newNumber = $lastNumber + 1;
        } else {
            $newNumber = 1;
        }
        
        return sprintf("%s-%s%s-%04d", $prefix, $year, $month, $newNumber);
    }
    
    /**
     * Mark invoice as paid
     */
    public function markAsPaid($id) {
        return $this->update($id, [
            'status' => 'paid',
            'balance' => 0.00,
            'paid_amount' => $this->getById($id)['total_amount']
        ]);
    }
    
    /**
     * Update invoice balance after payment
     */
    public function updateBalance($id, $paymentAmount) {
        $invoice = $this->getById($id);
        
        if (!$invoice) {
            throw new Exception("Invoice not found");
        }
        
        $newBalance = $invoice['balance'] - $paymentAmount;
        $newPaidAmount = $invoice['paid_amount'] + $paymentAmount;
        
        $status = 'partial';
        if ($newBalance <= 0) {
            $status = 'paid';
            $newBalance = 0.00;
        }
        
        return $this->update($id, [
            'balance' => $newBalance,
            'paid_amount' => $newPaidAmount,
            'status' => $status
        ]);
    }
}
?>
