<?php
// Include database connection
//include 'layouts/dbconn.php';

/**
 * Generate a unique note number
 * 
 * @param string $type Either 'DN' for debit note or 'CN' for credit note
 * @return string The generated note number
 */
function generateNoteNumber($conn, $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 = $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
 */
function createNote($conn, $noteData, $items) {
    try {
        $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 = $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 = $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']);
            addDebtTransaction($conn, [
                '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']);
            addDebtTransaction($conn, [
                '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']
            ]);
        }
        
        $conn->commit();
        return $noteId;
        
    } catch (Exception $e) {
        $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
 */
function updateNote($conn, $noteId, $noteData, $items) {
    try {
        $conn->begin_transaction();
        
        // Get the original note data for comparison
        $originalSql = "SELECT * FROM notes WHERE note_id = ?";
        $originalStmt = $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 = $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 = $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 = $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']);
                addDebtTransaction($conn, [
                    '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']
                ]);
            }
            
            // For debit note
            if ($noteData['note_type'] == 'debit') {
                // Add a positive amount (debit increases debt)
                $amount = abs($noteData['total_amount']);
                addDebtTransaction($conn, [
                    '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']
                ]);
            }
        }
        // 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']);
                addDebtTransaction($conn, [
                    'customer_id' => $noteData['customer_id'],
                    'transaction_date' => date('Y-m-d'),
                    'amount' => $amount,
                    'currency' => $noteData['currency'],
                    'transaction_type' => 'adjustment',
                    'reference_id' => $noteData['note_number'],
                    'notes' => 'Credit note cancelled: ' . $noteData['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']);
                addDebtTransaction($conn, [
                    'customer_id' => $noteData['customer_id'],
                    'transaction_date' => date('Y-m-d'),
                    'amount' => $amount,
                    'currency' => $noteData['currency'],
                    'transaction_type' => 'adjustment',
                    'reference_id' => $noteData['note_number'],
                    'notes' => 'Debit note cancelled: ' . $noteData['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
                addDebtTransaction($conn, [
                    'customer_id' => $noteData['customer_id'],
                    'transaction_date' => date('Y-m-d'),
                    'amount' => -1 * abs($difference),
                    'currency' => $noteData['currency'],
                    'transaction_type' => 'adjustment',
                    'reference_id' => $noteData['note_number'],
                    'notes' => 'Credit note adjusted: ' . $noteData['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
                addDebtTransaction($conn, [
                    'customer_id' => $noteData['customer_id'],
                    'transaction_date' => date('Y-m-d'),
                    'amount' => $difference,
                    'currency' => $noteData['currency'],
                    'transaction_type' => 'adjustment',
                    'reference_id' => $noteData['note_number'],
                    'notes' => 'Debit note adjusted: ' . $noteData['note_number'],
                    'user_id' => $noteData['user_id']
                ]);
            }
        }
        
        $conn->commit();
        return true;
        
    } catch (Exception $e) {
        $conn->rollback();
        error_log("Error in updateNote: " . $e->getMessage());
        return false;
    }
}

/**
 * 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
 */
function processNoteApproval($conn, $noteId, $action, $userId) {
    try {
        $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 = $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 = $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']);
                addDebtTransaction($conn, [
                    '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']);
                addDebtTransaction($conn, [
                    '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
                ]);
            }
        }
        
        $conn->commit();
        return true;
        
    } catch (Exception $e) {
        $conn->rollback();
        error_log("Error in processNoteApproval: " . $e->getMessage());
        return false;
    }
}

/**
 * Add a debt transaction and update client debt total
 * 
 * @param array $transactionData The transaction data
 * @return bool True if successful, false otherwise
 */
function addDebtTransaction($conn, $transactionData) {
    try {
        $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 = $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 = $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);
        }
        
        $conn->commit();
        return true;
        
    } catch (Exception $e) {
        $conn->rollback();
        error_log("Error in addDebtTransaction: " . $e->getMessage());
        return false;
    }
}

/**
 * Get total debt for a customer
 * 
 * @param int $customerId The customer ID
 * @param string $currency Optional currency filter
 * @return array The debt data
 */
function getCustomerDebt($conn, $customerId, $currency = null) {
    $debts = [];
    
    try {
        if ($currency) {
            $sql = "SELECT * FROM client_debts 
                    WHERE customer_id = ? AND currency = ?";
            $stmt = $conn->prepare($sql);
            $stmt->bind_param('is', $customerId, $currency);
        } else {
            $sql = "SELECT * FROM client_debts 
                    WHERE customer_id = ?";
            $stmt = $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
 * @return array The transaction data
 */
function getDebtTransactions($conn, $customerId, $currency = null, $startDate = null, $endDate = null) {
    $transactions = [];
    
    try {
        $sql = "SELECT dt.*, u.username as created_by 
                FROM debt_transactions dt
                LEFT JOIN users u ON dt.user_id = u.user_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";
        
        $stmt = $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 manual debt adjustment
 * 
 * @param array $adjustmentData The adjustment data
 * @return bool True if successful, false otherwise
 */
function addDebtAdjustment($conn, $adjustmentData) {
    try {
        return addDebtTransaction($conn, [
            '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;
    }
}

/**
 * Generate PDF for a debit or credit note
 * 
 * @param int $noteId The note ID
 * @return string The path to the generated PDF
 */
function generateNotePDF($conn, $noteId) {
    try {
        // Get note details
        $sql = "SELECT n.*, c.first_name, c.last_name, c.address, c.city, c.country, c.phone_number, c.email
                FROM notes n
                LEFT JOIN customers c ON n.customer_id = c.customer_id
                WHERE n.note_id = ?";
        
        $stmt = $conn->prepare($sql);
        $stmt->bind_param('i', $noteId);
        $stmt->execute();
        $noteResult = $stmt->get_result();
        
        if ($noteResult->num_rows == 0) {
            throw new Exception("Note not found");
        }
        
        $note = $noteResult->fetch_assoc();
        
        // Get note items
        $sql = "SELECT * FROM note_items WHERE note_id = ?";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param('i', $noteId);
        $stmt->execute();
        $itemsResult = $stmt->get_result();
        
        $items = [];
        while ($row = $itemsResult->fetch_assoc()) {
            $items[] = $row;
        }
        
        // Get company settings
        $sql = "SELECT * FROM settings WHERE setting_key IN ('system_name', 'address', 'tel_1', 'tel_2')";
        $settingsResult = $conn->query($sql);
        
        $settings = [];
        while ($row = $settingsResult->fetch_assoc()) {
            $settings[$row['setting_key']] = $row['setting_value'];
        }
    }
}
?>
        