<?php
/**
 * Note Controller (Credit & Debit Notes)
 * 
 * This class handles operations related to debit and credit notes
 */
class NoteController {
    private $conn;
    
    /**
     * Constructor
     * 
     * @param mysqli $conn The database connection
     */
    public function __construct($conn) {
        $this->conn = $conn;
    }
    
    /**
     * Generate a unique note number
     * 
     * @param string $type Either 'debit' or 'credit'
     * @return string The generated note number
     */
    public function generateNoteNumber($type) {
        $prefix = ($type == 'debit') ? 'DN' : 'CN';
        $year = date('y');
        
        // Get the last used number for this type and year
        $sql = "SELECT MAX(CAST(SUBSTRING_INDEX(note_number, '/', -1) AS UNSIGNED)) as last_number 
                FROM notes 
                WHERE note_number LIKE 'RF/$prefix/%/$year'";
        
        $result = $this->conn->query($sql);
        $row = $result->fetch_assoc();
        
        if ($row['last_number']) {
            $nextNumber = $row['last_number'] + 1;
        } else {
            $nextNumber = 1;
        }
        
        // Format with leading zeros (3 digits)
        $formattedNumber = sprintf("%03d", $nextNumber);
        
        // Return the final note number
        return "RF/$prefix/$formattedNumber/$year";
    }
    
    /**
     * Create a new debit or credit note
     * 
     * @param array $noteData The note data
     * @param array $items The note items
     * @return int|bool The note ID if successful, false otherwise
     */
    public function createNote($noteData, $items) {
        try {
            $this->conn->begin_transaction();
            
            // Insert note
            $sql = "INSERT INTO notes (
                        note_number, note_type, file_number, invoice_id, customer_id,
                        user_id, total_amount, currency, issue_date, due_date,
                        status, reference, notes, reason
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            
            $stmt = $this->conn->prepare($sql);
            $stmt->bind_param(
                'sssiidssssssss',
                $noteData['note_number'],
                $noteData['note_type'],
                $noteData['file_number'],
                $noteData['invoice_id'],
                $noteData['customer_id'],
                $noteData['user_id'],
                $noteData['total_amount'],
                $noteData['currency'],
                $noteData['issue_date'],
                $noteData['due_date'],
                $noteData['status'],
                $noteData['reference'],
                $noteData['notes'],
                $noteData['reason']
            );
            
            if (!$stmt->execute()) {
                throw new Exception("Error creating note: " . $stmt->error);
            }
            
            $noteId = $stmt->insert_id;
            
            // Insert items
            foreach ($items as $item) {
                $sql = "INSERT INTO note_items (
                            note_id, description, quantity, unit_price, amount,
                            vat_included, vat_amount
                        ) VALUES (?, ?, ?, ?, ?, ?, ?)";
                
                $stmt = $this->conn->prepare($sql);
                $stmt->bind_param(
                    'isddidi',
                    $noteId,
                    $item['description'],
                    $item['quantity'],
                    $item['unit_price'],
                    $item['amount'],
                    $item['vat_included'],
                    $item['vat_amount']
                );
                
                if (!$stmt->execute()) {
                    throw new Exception("Error adding note item: " . $stmt->error);
                }
            }
            
            // If this is a credit note, create a debt transaction
            if ($noteData['note_type'] == 'credit' && $noteData['status'] == 'issued') {
                // Add a negative amount (credit reduces debt)
                $amount = -1 * abs($noteData['total_amount']);
                $this->addDebtTransaction([
                    'customer_id' => $noteData['customer_id'],
                    'transaction_date' => $noteData['issue_date'],
                    'amount' => $amount,
                    'currency' => $noteData['currency'],
                    'transaction_type' => 'credit_note',
                    'reference_id' => $noteData['note_number'],
                    'notes' => 'Credit note issued: ' . $noteData['reason'],
                    'user_id' => $noteData['user_id']
                ]);
            }
            
            // If this is a debit note, create a debt transaction
            if ($noteData['note_type'] == 'debit' && $noteData['status'] == 'issued') {
                // Add a positive amount (debit increases debt)
                $amount = abs($noteData['total_amount']);
                $this->addDebtTransaction([
                    'customer_id' => $noteData['customer_id'],
                    'transaction_date' => $noteData['issue_date'],
                    'amount' => $amount,
                    'currency' => $noteData['currency'],
                    'transaction_type' => 'debit_note',
                    'reference_id' => $noteData['note_number'],
                    'notes' => 'Debit note issued: ' . $noteData['reason'],
                    'user_id' => $noteData['user_id']
                ]);
            }
            
            $this->conn->commit();
            return $noteId;
            
        } catch (Exception $e) {
            $this->conn->rollback();
            error_log("Error in createNote: " . $e->getMessage());
            return false;
        }
    }
    
    /**
     * Update an existing debit or credit note
     * 
     * @param int $noteId The note ID
     * @param array $noteData The note data
     * @param array $items The note items
     * @return bool True if successful, false otherwise
     */
    public function updateNote($noteId, $noteData, $items) {
        try {
            $this->conn->begin_transaction();
            
            // Get the original note data for comparison
            $originalSql = "SELECT * FROM notes WHERE note_id = ?";
            $originalStmt = $this->conn->prepare($originalSql);
            $originalStmt->bind_param('i', $noteId);
            $originalStmt->execute();
            $originalResult = $originalStmt->get_result();
            $originalNote = $originalResult->fetch_assoc();
            
            // Update note
            $sql = "UPDATE notes SET
                        file_number = ?,
                        invoice_id = ?,
                        customer_id = ?,
                        total_amount = ?,
                        currency = ?,
                        issue_date = ?,
                        due_date = ?,
                        status = ?,
                        reference = ?,
                        notes = ?,
                        reason = ?
                    WHERE note_id = ?";
            
            $stmt = $this->conn->prepare($sql);
            $stmt->bind_param(
                'siidsssssssi',
                $noteData['file_number'],
                $noteData['invoice_id'],
                $noteData['customer_id'],
                $noteData['total_amount'],
                $noteData['currency'],
                $noteData['issue_date'],
                $noteData['due_date'],
                $noteData['status'],
                $noteData['reference'],
                $noteData['notes'],
                $noteData['reason'],
                $noteId
            );
            
            if (!$stmt->execute()) {
                throw new Exception("Error updating note: " . $stmt->error);
            }
            
            // Delete existing items
            $sql = "DELETE FROM note_items WHERE note_id = ?";
            $stmt = $this->conn->prepare($sql);
            $stmt->bind_param('i', $noteId);
            
            if (!$stmt->execute()) {
                throw new Exception("Error deleting note items: " . $stmt->error);
            }
            
            // Insert new items
            foreach ($items as $item) {
                $sql = "INSERT INTO note_items (
                            note_id, description, quantity, unit_price, amount,
                            vat_included, vat_amount
                        ) VALUES (?, ?, ?, ?, ?, ?, ?)";
                
                $stmt = $this->conn->prepare($sql);
                $stmt->bind_param(
                    'isddidi',
                    $noteId,
                    $item['description'],
                    $item['quantity'],
                    $item['unit_price'],
                    $item['amount'],
                    $item['vat_included'],
                    $item['vat_amount']
                );
                
                if (!$stmt->execute()) {
                    throw new Exception("Error adding note item: " . $stmt->error);
                }
            }
            
            // Handle debt transactions if status changes to/from issued
            // If original wasn't issued but now is, add a transaction
            if ($originalNote['status'] != 'issued' && $noteData['status'] == 'issued') {
                // For credit note
                if ($noteData['note_type'] == 'credit') {
                    // Add a negative amount (credit reduces debt)
                    $amount = -1 * abs($noteData['total_amount']);
                    $this->addDebtTransaction([
                        'customer_id' => $noteData['customer_id'],
                        'transaction_date' => $noteData['issue_date'],
                        'amount' => $amount,
                        'currency' => $noteData['currency'],
                        'transaction_type' => 'credit_note',
                        'reference_id' => $originalNote['note_number'],
                        'notes' => 'Credit note issued: ' . $noteData['reason'],
                        'user_id' => $noteData['user_id']
                    ]);
                }
                
                // For debit note
                if ($noteData['note_type'] == 'debit') {
                    // Add a positive amount (debit increases debt)
                    $amount = abs($noteData['total_amount']);
                    $this->addDebtTransaction([
                        'customer_id' => $noteData['customer_id'],
                        'transaction_date' => $noteData['issue_date'],
                        'amount' => $amount,
                        'currency' => $noteData['currency'],
                        'transaction_type' => 'debit_note',
                        'reference_id' => $originalNote['note_number'],
                        'notes' => 'Debit note issued: ' . $noteData['reason'],
                        'user_id' => $noteData['user_id']
                    ]);
                }
            }
            // If original was issued but now isn't, reverse the transaction
            else if ($originalNote['status'] == 'issued' && $noteData['status'] != 'issued') {
                // For credit note
                if ($noteData['note_type'] == 'credit') {
                    // Add a positive amount (reversal of credit)
                    $amount = abs($originalNote['total_amount']);
                    $this->addDebtTransaction([
                        'customer_id' => $noteData['customer_id'],
                        'transaction_date' => date('Y-m-d'),
                        'amount' => $amount,
                        'currency' => $noteData['currency'],
                        'transaction_type' => 'adjustment',
                        'reference_id' => $originalNote['note_number'],
                        'notes' => 'Credit note cancelled: ' . $originalNote['note_number'],
                        'user_id' => $noteData['user_id']
                    ]);
                }
                
                // For debit note
                if ($noteData['note_type'] == 'debit') {
                    // Add a negative amount (reversal of debit)
                    $amount = -1 * abs($originalNote['total_amount']);
                    $this->addDebtTransaction([
                        'customer_id' => $noteData['customer_id'],
                        'transaction_date' => date('Y-m-d'),
                        'amount' => $amount,
                        'currency' => $noteData['currency'],
                        'transaction_type' => 'adjustment',
                        'reference_id' => $originalNote['note_number'],
                        'notes' => 'Debit note cancelled: ' . $originalNote['note_number'],
                        'user_id' => $noteData['user_id']
                    ]);
                }
            }
            // If both were issued but amount changed, adjust the difference
            else if ($originalNote['status'] == 'issued' && $noteData['status'] == 'issued' && 
                     $originalNote['total_amount'] != $noteData['total_amount']) {
                
                $difference = $noteData['total_amount'] - $originalNote['total_amount'];
                
                // For credit note
                if ($noteData['note_type'] == 'credit') {
                    // If difference is positive, it means more credit was given
                    // So we need to reduce the debt further
                    $this->addDebtTransaction([
                        'customer_id' => $noteData['customer_id'],
                        'transaction_date' => date('Y-m-d'),
                        'amount' => -1 * abs($difference),
                        'currency' => $noteData['currency'],
                        'transaction_type' => 'adjustment',
                        'reference_id' => $originalNote['note_number'],
                        'notes' => 'Credit note adjusted: ' . $originalNote['note_number'],
                        'user_id' => $noteData['user_id']
                    ]);
                }
                
                // For debit note
                if ($noteData['note_type'] == 'debit') {
                    // If difference is positive, it means more debt was added
                    $this->addDebtTransaction([
                        'customer_id' => $noteData['customer_id'],
                        'transaction_date' => date('Y-m-d'),
                        'amount' => $difference,
                        'currency' => $noteData['currency'],
                        'transaction_type' => 'adjustment',
                        'reference_id' => $originalNote['note_number'],
                        'notes' => 'Debit note adjusted: ' . $originalNote['note_number'],
                        'user_id' => $noteData['user_id']
                    ]);
                }
            }
            
            $this->conn->commit();
            return true;
            
        } catch (Exception $e) {
            $this->conn->rollback();
            error_log("Error in updateNote: " . $e->getMessage());
            return false;
        }
    }
    
    /**
     * Get a note by ID
     * 
     * @param int $noteId The note ID
     * @return array|null The note data or null if not found
     */
    public function getNoteById($noteId) {
        try {
            $sql = "SELECT n.*, 
                           c.first_name, c.last_name, c.phone_number, c.email, 
                           c.address, c.city, c.country,
                           u.username as created_by, 
                           a.username as approved_by_name
                    FROM notes n
                    LEFT JOIN customers c ON n.customer_id = c.customer_id
                    LEFT JOIN users u ON n.user_id = u.user_id
                    LEFT JOIN users a ON n.approved_by = a.user_id
                    WHERE n.note_id = ?";
            
            $stmt = $this->conn->prepare($sql);
            $stmt->bind_param('i', $noteId);
            $stmt->execute();
            $result = $stmt->get_result();
            
            if ($result->num_rows == 0) {
                return null;
            }
            
            $note = $result->fetch_assoc();
            
            // Get note items
            $sql = "SELECT * FROM note_items WHERE note_id = ?";
            $stmt = $this->conn->prepare($sql);
            $stmt->bind_param('i', $noteId);
            $stmt->execute();
            $itemsResult = $stmt->get_result();
            
            $items = [];
            while ($row = $itemsResult->fetch_assoc()) {
                $items[] = $row;
            }
            
            $note['items'] = $items;
            
            return $note;
            
        } catch (Exception $e) {
            error_log("Error in getNoteById: " . $e->getMessage());
            return null;
        }
    }
    
    /**
     * Get all notes with optional filters
     * 
     * @param string $type Optional note type filter ('debit' or 'credit')
     * @param int $customerId Optional customer ID filter
     * @param string $status Optional status filter
     * @param string $startDate Optional start date filter
     * @param string $endDate Optional end date filter
     * @return array The notes data
     */
    public function getNotes($type = null, $customerId = null, $status = null, $startDate = null, $endDate = null) {
        try {
            $sql = "SELECT n.*, 
                           c.first_name, c.last_name, 
                           u.username as created_by, 
                           a.username as approved_by_name
                    FROM notes n
                    LEFT JOIN customers c ON n.customer_id = c.customer_id
                    LEFT JOIN users u ON n.user_id = u.user_id
                    LEFT JOIN users a ON n.approved_by = a.user_id
                    WHERE 1=1";
            
            $params = [];
            $types = "";
            
            if ($type) {
                $sql .= " AND n.note_type = ?";
                $params[] = $type;
                $types .= "s";
            }
            
            if ($customerId) {
                $sql .= " AND n.customer_id = ?";
                $params[] = $customerId;
                $types .= "i";
            }
            
            if ($status) {
                $sql .= " AND n.status = ?";
                $params[] = $status;
                $types .= "s";
            }
            
            if ($startDate) {
                $sql .= " AND n.issue_date >= ?";
                $params[] = $startDate;
                $types .= "s";
            }
            
            if ($endDate) {
                $sql .= " AND n.issue_date <= ?";
                $params[] = $endDate;
                $types .= "s";
            }
            
            $sql .= " ORDER BY n.issue_date DESC, n.created_at DESC";
            
            $stmt = $this->conn->prepare($sql);
            
            if (!empty($params)) {
                $stmt->bind_param($types, ...$params);
            }
            
            $stmt->execute();
            $result = $stmt->get_result();
            
            $notes = [];
            while ($row = $result->fetch_assoc()) {
                $notes[] = $row;
            }
            
            return $notes;
            
        } catch (Exception $e) {
            error_log("Error in getNotes: " . $e->getMessage());
            return [];
        }
    }
    
    /**
     * Approve or deny a note
     * 
     * @param int $noteId The note ID
     * @param string $action Either 'approve' or 'deny'
     * @param int $userId The ID of the user approving/denying
     * @return bool True if successful, false otherwise
     */
    public function processNoteApproval($noteId, $action, $userId) {
        try {
            $this->conn->begin_transaction();
            
            $status = ($action == 'approve') ? 'approved' : 'denied';
            
            // Update note status
            $sql = "UPDATE notes SET 
                    status = ?, 
                    approved_by = ?, 
                    approval_date = NOW() 
                    WHERE note_id = ?";
            
            $stmt = $this->conn->prepare($sql);
            $stmt->bind_param('sii', $status, $userId, $noteId);
            
            if (!$stmt->execute()) {
                throw new Exception("Error updating note status: " . $stmt->error);
            }
            
            // If approved, get note details to create debt transaction
            if ($action == 'approve') {
                $sql = "SELECT * FROM notes WHERE note_id = ?";
                $stmt = $this->conn->prepare($sql);
                $stmt->bind_param('i', $noteId);
                $stmt->execute();
                $result = $stmt->get_result();
                $note = $result->fetch_assoc();
                
                // For credit note
                if ($note['note_type'] == 'credit') {
                    // Add a negative amount (credit reduces debt)
                    $amount = -1 * abs($note['total_amount']);
                    $this->addDebtTransaction([
                        'customer_id' => $note['customer_id'],
                        'transaction_date' => date('Y-m-d'),
                        'amount' => $amount,
                        'currency' => $note['currency'],
                        'transaction_type' => 'credit_note',
                        'reference_id' => $note['note_number'],
                        'notes' => 'Credit note approved: ' . $note['reason'],
                        'user_id' => $userId
                    ]);
                }
                
                // For debit note
                if ($note['note_type'] == 'debit') {
                    // Add a positive amount (debit increases debt)
                    $amount = abs($note['total_amount']);
                    $this->addDebtTransaction([
                        'customer_id' => $note['customer_id'],
                        'transaction_date' => date('Y-m-d'),
                        'amount' => $amount,
                        'currency' => $note['currency'],
                        'transaction_type' => 'debit_note',
                        'reference_id' => $note['note_number'],
                        'notes' => 'Debit note approved: ' . $note['reason'],
                        'user_id' => $userId
                    ]);
                }
            }
            
            $this->conn->commit();
            return true;
            
        } catch (Exception $e) {
            $this->conn->rollback();
            error_log("Error in processNoteApproval: " . $e->getMessage());
            return false;
        }
    }
    
    /**
     * Add a debt transaction and update client debt total
     * Helper method - uses private version to avoid circular dependencies
     * 
     * @param array $transactionData The transaction data
     * @return bool True if successful, false otherwise
     */
    private 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
            $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;
        }
    }
    
    /**
     * Generate PDF for a debit or credit note
     * 
     * @param int $noteId The note ID
     * @return string|false The path to the generated PDF or false on failure
     */
    public function generateNotePDF($noteId) {
        try {
            // Get note with all details
            $note = $this->getNoteById($noteId);
            
            if (!$note) {
                throw new Exception("Note not found");
            }
            
            // Get company settings
            $sql = "SELECT * FROM settings WHERE setting_key IN ('system_name', 'address', 'tel_1', 'tel_2')";
            $settingsResult = $this->conn->query($sql);
            
            $settings = [];
            while ($row = $settingsResult->fetch_assoc()) {
                $settings[$row['setting_key']] = $row['setting_value'];
            }
            
            // Create PDF
            require_once('fpdf/fpdf.php');
            
            $pdf = new FPDF('P', 'mm', 'A4');
            $pdf->AddPage();
            
            // Set company info
            $pdf->SetFont('Arial', 'B', 16);
            $pdf->Cell(0, 10, $settings['system_name'] ?? 'Royal Freight', 0, 1, 'C');
            
            $pdf->SetFont('Arial', '', 10);
            $pdf->Cell(0, 5, $settings['address'] ?? 'P.O.Box-85636-80100, Mombasa', 0, 1, 'C');
            $pdf->Cell(0, 5, 'Tel: ' . ($settings['tel_1'] ?? '') . ' / ' . ($settings['tel_2'] ?? ''), 0, 1, 'C');
            
            // Add note title
            $pdf->Ln(10);
            $pdf->SetFont('Arial', 'B', 14);
            $noteTitle = ($note['note_type'] == 'credit') ? 'CREDIT NOTE' : 'DEBIT NOTE';
            $pdf->Cell(0, 10, $noteTitle, 0, 1, 'C');
            
            // Add note details
            $pdf->SetFont('Arial', 'B', 10);
            $pdf->Cell(40, 7, 'Note Number:', 0);
            $pdf->SetFont('Arial', '', 10);
            $pdf->Cell(60, 7, $note['note_number'], 0);
            
            $pdf->SetFont('Arial', 'B', 10);
            $pdf->Cell(40, 7, 'Date:', 0);
            $pdf->SetFont('Arial', '', 10);
            $pdf->Cell(0, 7, $note['issue_date'], 0, 1);
            
            if ($note['due_date']) {
                $pdf->SetFont('Arial', 'B', 10);
                $pdf->Cell(40, 7, 'Due Date:', 0);
                $pdf->SetFont('Arial', '', 10);
                $pdf->Cell(0, 7, $note['due_date'], 0, 1);
            }
            
            $pdf->SetFont('Arial', 'B', 10);
            $pdf->Cell(40, 7, 'Status:', 0);
            $pdf->SetFont('Arial', '', 10);
            $pdf->Cell(0, 7, ucfirst($note['status']), 0, 1);
            
            if ($note['file_number']) {
                $pdf->SetFont('Arial', 'B', 10);
                $pdf->Cell(40, 7, 'File Number:', 0);
                $pdf->SetFont('Arial', '', 10);
                $pdf->Cell(0, 7, $note['file_number'], 0, 1);
            }
            
            // Customer details
            $pdf->Ln(5);
            $pdf->SetFont('Arial', 'B', 11);
            $pdf->Cell(0, 7, 'Customer Details', 0, 1);
            
            $pdf->SetFont('Arial', 'B', 10);
            $pdf->Cell(40, 7, 'Customer:', 0);
            $pdf->SetFont('Arial', '', 10);
            $pdf->Cell(0, 7, $note['first_name'] . ' ' . $note['last_name'], 0, 1);
            
            $pdf->SetFont('Arial', 'B', 10);
            $pdf->Cell(40, 7, 'Address:', 0);
            $pdf->SetFont('Arial', '', 10);
            $pdf->Cell(0, 7, $note['address'], 0, 1);
            
            $pdf->SetFont('Arial', 'B', 10);
            $pdf->Cell(40, 7, 'Location:', 0);
            $pdf->SetFont('Arial', '', 10);
            $pdf->Cell(0, 7, $note['city'] . ', ' . $note['country'], 0, 1);
            
            $pdf->SetFont('Arial', 'B', 10);
            $pdf->Cell(40, 7, 'Contact:', 0);
            $pdf->SetFont('Arial', '', 10);
            $pdf->Cell(0, 7, $note['phone_number'] . ' / ' . $note['email'], 0, 1);
            
            // Reason for note
            $pdf->Ln(5);
            $pdf->SetFont('Arial', 'B', 11);
            $pdf->Cell(0, 7, 'Reason', 0, 1);
            $pdf->SetFont('Arial', '', 10);
            $pdf->MultiCell(0, 7, $note['reason'], 0, 'L');
            
            // Reference
            if ($note['reference']) {
                $pdf->Ln(2);
                $pdf->SetFont('Arial', 'B', 10);
                $pdf->Cell(40, 7, 'Reference:', 0);
                $pdf->SetFont('Arial', '', 10);
                $pdf->Cell(0, 7, $note['reference'], 0, 1);
            }
            
            // Items
            $pdf->Ln(5);
            $pdf->SetFont('Arial', 'B', 11);
            $pdf->Cell(0, 7, 'Items', 0, 1);
            
            // Table header
            $pdf->SetFont('Arial', 'B', 10);
            $pdf->Cell(80, 7, 'Description', 1);
            $pdf->Cell(25, 7, 'Quantity', 1, 0, 'R');
            $pdf->Cell(35, 7, 'Unit Price', 1, 0, 'R');
            $pdf->Cell(50, 7, 'Amount', 1, 1, 'R');
            
            // Table rows
            $pdf->SetFont('Arial', '', 10);
            $totalVat = 0;
            
            foreach ($note['items'] as $item) {
                $pdf->Cell(80, 7, $item['description'], 1);
                $pdf->Cell(25, 7, number_format($item['quantity'], 2), 1, 0, 'R');
                $pdf->Cell(35, 7, $note['currency'] . ' ' . number_format($item['unit_price'], 2), 1, 0, 'R');
                $pdf->Cell(50, 7, $note['currency'] . ' ' . number_format($item['amount'], 2), 1, 1, 'R');
                $totalVat += $item['vat_amount'];
            }
            
            // Totals
            $pdf->SetFont('Arial', 'B', 10);
            $pdf->Cell(140, 7, 'Subtotal:', 1, 0, 'R');
            $pdf->Cell(50, 7, $note['currency'] . ' ' . number_format($note['total_amount'] - $totalVat, 2), 1, 1, 'R');
            
            if ($totalVat > 0) {
                $pdf->Cell(140, 7, 'VAT:', 1, 0, 'R');
                $pdf->Cell(50, 7, $note['currency'] . ' ' . number_format($totalVat, 2), 1, 1, 'R');
            }
            
            $pdf->Cell(140, 7, 'Total:', 1, 0, 'R');
            $pdf->Cell(50, 7, $note['currency'] . ' ' . number_format($note['total_amount'], 2), 1, 1, 'R');
            
            // Notes
            if ($note['notes']) {
                $pdf->Ln(5);
                $pdf->SetFont('Arial', 'B', 11);
                $pdf->Cell(0, 7, 'Notes', 0, 1);
                $pdf->SetFont('Arial', '', 10);
                $pdf->MultiCell(0, 7, $note['notes'], 0, 'L');
            }
            
            // Signatures
            $pdf->Ln(15);
            $pdf->SetFont('Arial', '', 10);
            $pdf->Cell(95, 7, 'Prepared by: ' . $note['created_by'], 0, 0);
            if ($note['approved_by']) {
                $pdf->Cell(95, 7, 'Approved by: ' . $note['approved_by_name'], 0, 1);
            } else {
                $pdf->Cell(95, 7, '', 0, 1);
            }
            
            $pdf->Ln(15);
            $pdf->Cell(95, 7, '___________________________', 0, 0, 'C');
            $pdf->Cell(95, 7, '___________________________', 0, 1, 'C');
            $pdf->Cell(95, 7, 'Customer Signature', 0, 0, 'C');
            $pdf->Cell(95, 7, 'Company Representative', 0, 1, 'C');
            
            // Output PDF
            $pdfPath = 'uploads/notes/' . $note['note_number'] . '.pdf';
            $pdf->Output('F', $pdfPath);
            
            return $pdfPath;
            
        } catch (Exception $e) {
            error_log("Error in generateNotePDF: " . $e->getMessage());
            return false;
        }
    }
}
?>