<?php
require_once '../../config/config.php';
require_once '../../config/database.php';
require_once '../../includes/header.php';
?>

<div class="row">
    <div class="col-md-12">
        <div class="card">
            <div class="card-header">
                <h4>Batch Operations</h4>
            </div>
            <div class="card-body">
                <!-- Bulk Invoice Actions -->
                <form id="bulkActionForm" class="mb-4">
                    <div class="row align-items-end">
                        <div class="col-md-4">
                            <div class="mb-3">
                                <label class="form-label">Select Action</label>
                                <select class="form-select" name="bulk_action" required>
                                    <option value="">Choose Action</option>
                                    <option value="mark_paid">Mark as Paid</option>
                                    <option value="mark_sent">Mark as Sent</option>
                                    <option value="delete">Delete Selected</option>
                                    <option value="export">Export Selected</option>
                                    <option value="email">Email Selected</option>
                                </select>
                            </div>
                        </div>
                        <div class="col-md-4">
                            <button type="submit" class="btn btn-primary">
                                Apply to Selected
                            </button>
                        </div>
                    </div>
                
                    <!-- Invoice Selection Table -->
                    <div class="table-responsive mt-4">
                        <table class="table table-striped" id="invoiceSelectionTable">
                            <thead>
                                <tr>
                                    <th>
                                        <input type="checkbox" id="selectAll">
                                    </th>
                                    <th>Invoice #</th>
                                    <th>Customer</th>
                                    <th>Date</th>
                                    <th>Amount</th>
                                    <th>Status</th>
                                </tr>
                            </thead>
                            <tbody>
                                <!-- Will be populated via AJAX -->
                            </tbody>
                        </table>
                    </div>
                </form>
            </div>
        </div>
    </div>
</div>

<!-- Add Recurring Invoice Feature -->
<div class="row mt-4">
    <div class="col-md-12">
        <div class="card">
            <div class="card-header">
                <h4>Recurring Invoices</h4>
            </div>
            <div class="card-body">
                <form id="recurringInvoiceForm">
                    <div class="row">
                        <div class="col-md-4">
                            <div class="mb-3">
                                <label class="form-label">Select Invoice Template</label>
                                <select class="form-select" name="template_id" required>
                                    <!-- Will be populated via AJAX -->
                                </select>
                            </div>
                        </div>
                        <div class="col-md-4">
                            <div class="mb-3">
                                <label class="form-label">Frequency</label>
                                <select class="form-select" name="frequency" required>
                                    <option value="monthly">Monthly</option>
                                    <option value="quarterly">Quarterly</option>
                                    <option value="yearly">Yearly</option>
                                </select>
                            </div>
                        </div>
                        <div class="col-md-4">
                            <div class="mb-3">
                                <label class="form-label">Next Generation Date</label>
                                <input type="date" class="form-control" name="next_date" required>
                            </div>
                        </div>
                    </div>
                    <button type="submit" class="btn btn-success">
                        Setup Recurring Invoice
                    </button>
                </form>

                <!-- Recurring Invoices List -->
                <div class="table-responsive mt-4">
                    <table class="table table-striped" id="recurringInvoicesTable">
                        <thead>
                            <tr>
                                <th>Template</th>
                                <th>Customer</th>
                                <th>Frequency</th>
                                <th>Next Date</th>
                                <th>Amount</th>
                                <th>Actions</th>
                            </tr>
                        </thead>
                        <tbody>
                            <!-- Will be populated via AJAX -->
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>

<?php
// Add necessary SQL procedures for batch operations

$sql = "
DELIMITER //

CREATE PROCEDURE sp_batch_update_invoice_status(
    IN p_invoice_ids TEXT,
    IN p_status VARCHAR(20)
)
BEGIN
    SET @sql = CONCAT(
        'UPDATE invoices SET status = ? WHERE id IN (', p_invoice_ids, ')'
    );
    PREPARE stmt FROM @sql;
    EXECUTE stmt USING p_status;
    DEALLOCATE PREPARE stmt;
END //

CREATE PROCEDURE sp_setup_recurring_invoice(
    IN p_template_id INT,
    IN p_frequency VARCHAR(20),
    IN p_next_date DATE
)
BEGIN
    INSERT INTO recurring_invoices (
        template_id,
        frequency,
        next_date
    ) VALUES (
        p_template_id,
        p_frequency,
        p_next_date
    );
END //

CREATE PROCEDURE sp_generate_recurring_invoices()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_template_id, v_customer_id INT;
    DECLARE v_frequency VARCHAR(20);
    DECLARE v_next_date DATE;
    
    DECLARE cur CURSOR FOR 
        SELECT template_id, frequency, next_date
        FROM recurring_invoices
        WHERE next_date <= CURRENT_DATE;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO v_template_id, v_frequency, v_next_date;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- Create new invoice from template
        CALL sp_create_invoice_from_template(v_template_id);
        
        -- Update next date
        UPDATE recurring_invoices
        SET next_date = 
            CASE v_frequency
                WHEN 'monthly' THEN DATE_ADD(v_next_date, INTERVAL 1 MONTH)
                WHEN 'quarterly' THEN DATE_ADD(v_next_date, INTERVAL 3 MONTH)
                WHEN 'yearly' THEN DATE_ADD(v_next_date, INTERVAL 1 YEAR)
            END
        WHERE template_id = v_template_id;
    END LOOP;
    
    CLOSE cur;
END //

DELIMITER ;
";

// Add JavaScript for batch operations and recurring invoices
?>

<script>
$(document).ready(function() {
    // Initialize DataTable for invoice selection
    const invoiceTable = $('#invoiceSelectionTable').DataTable({
        ajax: 'api.php?action=list_invoices',
        columns: [
            {
                data: 'id',
                render: function(data) {
                    return `<input type="checkbox" name="invoice_ids[]" value="${data}">`;
                }
            },
            { data: 'invoice_number' },
            { data: 'customer_name' },
            { data: 'invoice_date' },
            { 
                data: 'total_amount',
                render: function(data) {
                    return formatCurrency(data);
                }
            },
            {
                data: 'status',
                render: function(data) {
                    return `<span class="badge bg-${getStatusColor(data)}">${data}</span>`;
                }
            }
        ]
    });

    // Select All checkbox
    $('#selectAll').change(function() {
        $('input[name="invoice_ids[]"]').prop('checked', $(this).prop('checked'));
    });

    // Bulk Action Form
    $('#bulkActionForm').submit(function(e) {
        e.preventDefault();
        
        const selectedIds = $('input[name="invoice_ids[]"]:checked').map(function() {
            return $(this).val();
        }).get();

        if(selectedIds.length === 0) {
            alert('Please select at least one invoice');
            return;
        }

        const action = $('select[name="bulk_action"]').val();
        
        if(confirm(`Are you sure you want to ${action} the selected invoices?`)) {
            $.post('api.php', {
                action: 'bulk_action',
                bulk_action: action,
                invoice_ids: selectedIds
            }, function(response) {
                if(response.success) {
                    toastr.success(response.message);
                    invoiceTable.ajax.reload();
                } else {
                    toastr.error(response.message);
                }
            });
        }
    });

    // Initialize DataTable for recurring invoices
    const recurringTable = $('#recurringInvoicesTable').DataTable({
        ajax: 'api.php?action=list_recurring',
        columns: [
            { data: 'template_name' },
            { data: 'customer_name' },
            { data: 'frequency' },
            { data: 'next_date' },
            {
                data: 'amount',
                render: function(data) {
                    return formatCurrency(data);
                }
            },
            {
                data: 'id',
                render: function(data) {
                    return `
                        <button class="btn btn-sm btn-primary edit-recurring" data-id="${data}">
                            Edit
                        </button>
                        <button class="btn btn-sm btn-danger delete-recurring" data-id="${data}">
                            Delete
                        </button>
                    `;
                }
            }
        ]
    });

    // Recurring Invoice Form
    $('#recurringInvoiceForm').submit(function(e) {
        e.preventDefault();
        
        $.post('api.php', $(this).serialize() + '&action=setup_recurring', function(response) {
            if(response.success) {
                toastr.success(response.message);
                recurringTable.ajax.reload();
                $(this)[0].reset();
            } else {
                toastr.error(response.message);
            }
        });
    });
});

// Helper functions
function formatCurrency(amount) {
    return new Intl.NumberFormat('en-US', {
        style: 'currency',
        currency: 'USD'
    }).format(amount);
}

function getStatusColor(status) {
    const colors = {
        'paid': 'success',
        'pending': 'warning',
        'cancelled': 'danger',
        'draft': 'secondary'
    };
    return colors[status] || 'primary';
}
</script>

<?php require_once '../../includes/footer.php'; ?>
Above is batch_operations php
<?php
require_once '../../config/config.php';
require_once '../../config/database.php';
require_once '../../includes/header.php';
?>

<div class="row">
    <div class="col-md-12">
        <div class="card">
            <div class="card-header">
                <h4>Create New Invoice</h4>
            </div>
            <div class="card-body">
                <form id="createInvoiceForm">
                    <!-- Invoice Header -->
                    <div class="row mb-4">
                        <div class="col-md-6">
                            <div class="mb-3">
                                <label class="form-label">Customer</label>
                                <select class="form-select" name="customer_id" required>
                                    <option value="">Select Customer</option>
                                </select>
                            </div>
                        </div>
                        <div class="col-md-3">
                            <div class="mb-3">
                                <label class="form-label">Invoice Date</label>
                                <input type="date" class="form-control" name="invoice_date" required>
                            </div>
                        </div>
                        <div class="col-md-3">
                            <div class="mb-3">
                                <label class="form-label">Due Date</label>
                                <input type="date" class="form-control" name="due_date" required>
                            </div>
                        </div>
                    </div>

                    <!-- Invoice Items -->
                    <div class="row mb-4">
                        <div class="col-md-12">
                            <h5>Invoice Items</h5>
                            <table class="table" id="invoiceItems">
                                <thead>
                                    <tr>
                                        <th>Product</th>
                                        <th>Quantity</th>
                                        <th>Unit Price</th>
                                        <th>Total</th>
                                        <th>Action</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <tr id="itemRow0">
                                        <td>
                                            <select class="form-select product-select" name="items[0][product_id]" required>
                                                <option value="">Select Product</option>
                                            </select>
                                        </td>
                                        <td>
                                            <input type="number" class="form-control quantity" name="items[0][quantity]" min="1" value="1">
                                        </td>
                                        <td>
                                            <input type="number" class="form-control unit-price" name="items[0][unit_price]" readonly>
                                        </td>
                                        <td>
                                            <input type="number" class="form-control item-total" readonly>
                                        </td>
                                        <td>
                                            <button type="button" class="btn btn-danger btn-sm remove-item">
                                                <i class="fas fa-trash"></i>
                                            </button>
                                        </td>
                                    </tr>
                                </tbody>
                            </table>
                            <button type="button" class="btn btn-success" id="addItem">
                                <i class="fas fa-plus"></i> Add Item
                            </button>
                        </div>
                    </div>

                    <!-- Invoice Totals -->
                    <div class="row">
                        <div class="col-md-6">
                            <div class="mb-3">
                                <label class="form-label">Notes</label>
                                <textarea class="form-control" name="notes" rows="3"></textarea>
                            </div>
                        </div>
                        <div class="col-md-6">
                            <div class="card">
                                <div class="card-body">
                                    <div class="row mb-2">
                                        <label class="col-sm-4 col-form-label">Subtotal:</label>
                                        <div class="col-sm-8">
                                            <input type="number" class="form-control" id="subtotal" readonly>
                                        </div>
                                    </div>
                                    <div class="row mb-2">
                                        <label class="col-sm-4 col-form-label">Tax Rate (%):</label>
                                        <div class="col-sm-8">
                                            <input type="number" class="form-control" name="tax_rate" value="0" min="0" max="100">
                                        </div>
                                    </div>
                                    <div class="row mb-2">
                                        <label class="col-sm-4 col-form-label">Tax Amount:</label>
                                        <div class="col-sm-8">
                                            <input type="number" class="form-control" id="taxAmount" readonly>
                                        </div>
                                    </div>
                                    <div class="row">
                                        <label class="col-sm-4 col-form-label">Total:</label>
                                        <div class="col-sm-8">
                                            <input type="number" class="form-control" id="total" readonly>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <div class="row mt-4">
                        <div class="col-md-12">
                            <button type="submit" class="btn btn-primary">
                                <i class="fas fa-save"></i> Save Invoice
                            </button>
                            <a href="index.php" class="btn btn-secondary">
                                <i class="fas fa-times"></i> Cancel
                            </a>
                        </div>
                    </div>
                </form>
            </div>
        </div>
    </div>
</div>

<!-- Add JavaScript for invoice handling -->
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script src="<?php echo SITE_URL; ?>/assets/js/invoice-create.js"></script>
above is create.php
<?php
require_once '../../config/config.php';
require_once '../../config/database.php';
require_once '../../includes/InvoicePDF.php';

$invoice_id = isset($_GET['id']) ? intval($_GET['id']) : 0;

// Get invoice details
$query = "SELECT i.*, c.* 
          FROM invoices i 
          LEFT JOIN customers c ON i.customer_id = c.id 
          WHERE i.id = $invoice_id";
$invoice = $db->query($query)->fetch_assoc();

// Get invoice items
$query = "SELECT ii.*, p.product_name 
          FROM invoice_items ii 
          LEFT JOIN products p ON ii.product_id = p.id 
          WHERE ii.invoice_id = $invoice_id";
$items = $db->query($query)->fetch_all(MYSQLI_ASSOC);

// Generate PDF
$pdf = new InvoicePDF($invoice, $items);
$pdfContent = $pdf->generate();

// Output PDF
header('Content-Type: application/pdf');
header('Content-Disposition: attachment; filename="Invoice_' . $invoice['invoice_number'] . '.pdf"');
echo $pdfContent;
above is generate_pdf.php
<?php
require_once '../../config/config.php';
require_once '../../config/database.php';
require_once '../../includes/header.php';
?>

<div class="row">
    <div class="col-md-12 mb-3">
        <div class="card">
            <div class="card-header d-flex justify-content-between align-items-center">
                <h4>Invoices</h4>
                <a href="create.php" class="btn btn-primary">
                    <i class="fas fa-plus"></i> Create New Invoice
                </a>
            </div>
            <div class="card-body">
                <table id="invoicesTable" class="table table-striped">
                    <thead>
                        <tr>
                            <th>Invoice #</th>
                            <th>Customer</th>
                            <th>Date</th>
                            <th>Due Date</th>
                            <th>Amount</th>
                            <th>Status</th>
                            <th>Actions</th>
                        </tr>
                    </thead>
                </table>
            </div>
        </div>
    </div>
</div>

<?php require_once '../../includes/footer.php'; ?>
above is index.php
<?php
require_once '../../config/config.php';
require_once '../../config/database.php';
require_once '../../includes/header.php';

// Function to get status color
function getStatusColor($status) {
    switch ($status) {
        case 'paid':
            return 'success';
        case 'pending':
            return 'warning';
        case 'overdue':
            return 'danger';
        case 'cancelled':
            return 'secondary';
        default:
            return 'info';
    }
}

$invoice_id = isset($_GET['id']) ? intval($_GET['id']) : 0;

// Get invoice details
$query = "SELECT i.*, c.* 
          FROM invoices i 
          LEFT JOIN customers c ON i.customer_id = c.id 
          WHERE i.id = $invoice_id";
$result = $db->query($query);
$invoice = $result->fetch_assoc();

// Get invoice items
$query = "SELECT ii.*, p.product_name 
          FROM invoice_items ii 
          LEFT JOIN products p ON ii.product_id = p.id 
          WHERE ii.invoice_id = $invoice_id";
$items = $db->query($query)->fetch_all(MYSQLI_ASSOC);
?>

<div class="row">
    <div class="col-md-12 mb-3">
        <div class="card">
            <div class="card-header d-flex justify-content-between align-items-center">
                <h4>Invoice #<?php echo $invoice['invoice_number']; ?></h4>
                <div class="btn-group">
                    <button type="button" class="btn btn-primary" onclick="printInvoice()">
                        <i class="fas fa-print"></i> Print
                    </button>
                    <button type="button" class="btn btn-success" onclick="downloadPDF()">
                        <i class="fas fa-file-pdf"></i> Download PDF
                    </button>
                    <button type="button" class="btn btn-info" onclick="emailInvoice()">
                        <i class="fas fa-envelope"></i> Email
                    </button>
                </div>
            </div>
            <div class="card-body" id="invoicePrint">
                <!-- Company Information -->
                <div class="row mb-4">
                    <div class="col-md-6">
                        <h2><?php echo COMPANY_NAME; ?></h2>
                        <p><?php echo COMPANY_ADDRESS; ?><br>
                           Phone: <?php echo COMPANY_PHONE; ?><br>
                           Email: <?php echo COMPANY_EMAIL; ?></p>
                    </div>
                    <div class="col-md-6 text-end">
                        <h1 class="text-primary">INVOICE</h1>
                        <p><strong>Invoice #:</strong> <?php echo $invoice['invoice_number']; ?><br>
                           <strong>Date:</strong> <?php echo date('d/m/Y', strtotime($invoice['invoice_date'])); ?><br>
                           <strong>Due Date:</strong> <?php echo date('d/m/Y', strtotime($invoice['due_date'])); ?></p>
                    </div>
                </div>

                <!-- Customer Information -->
                <div class="row mb-4">
                    <div class="col-md-6">
                        <h5>Bill To:</h5>
                        <p><strong><?php echo $invoice['customer_name']; ?></strong><br>
                           <?php echo $invoice['address']; ?><br>
                           <?php echo $invoice['city'] . ', ' . $invoice['state'] . ' ' . $invoice['zip_code']; ?><br>
                           Phone: <?php echo $invoice['phone']; ?><br>
                           Email: <?php echo $invoice['email']; ?></p>
                    </div>
                    <div class="col-md-6 text-end">
                        <h5>Status:</h5>
                        <span class="badge bg-<?php echo getStatusColor($invoice['status']); ?>">
                            <?php echo ucfirst($invoice['status']); ?>
                        </span>
                    </div>
                </div>

                <!-- Invoice Items -->
                <div class="table-responsive mb-4">
                    <table class="table table-striped">
                        <thead>
                            <tr>
                                <th>Item</th>
                                <th>Quantity</th>
                                <th>Unit Price</th>
                                <th class="text-end">Total</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach($items as $item): ?>
                            <tr>
                                <td><?php echo $item['product_name']; ?></td>
                                <td><?php echo $item['quantity']; ?></td>
                                <td><?php echo number_format($item['unit_price'], 2); ?></td>
                                <td class="text-end"><?php echo number_format($item['total_price'], 2); ?></td>
                            </tr>
                            <?php endforeach; ?>
                        </tbody>
                        <tfoot>
                            <tr>
                                <td colspan="3" class="text-end"><strong>Subtotal:</strong></td>
                                <td class="text-end"><?php echo number_format($invoice['subtotal'], 2); ?></td>
                            </tr>
                            <tr>
                                <td colspan="3" class="text-end"><strong>Tax (<?php echo isset($invoice['tax_rate']) ? $invoice['tax_rate'] : 0; ?>%):</strong></td>
                                <td class="text-end"><?php echo number_format($invoice['tax_amount'], 2); ?></td>
                            </tr>
                            <tr>
                                <td colspan="3" class="text-end"><strong>Total:</strong></td>
                                <td class="text-end"><strong><?php echo number_format($invoice['total_amount'], 2); ?></strong></td>
                            </tr>
                        </tfoot>
                    </table>
                </div>

                <!-- Notes -->
                <?php if(!empty($invoice['notes'])): ?>
                <div class="row mb-4">
                    <div class="col-md-12">
                        <h5>Notes:</h5>
                        <p><?php echo nl2br($invoice['notes']); ?></p>
                    </div>
                </div>
                <?php endif; ?>
            </div>
        </div>
    </div>
</div>

<?php
// Add PDF Generation Class
require_once '../../includes/InvoicePDF.php';
?>

<script>
function printInvoice() {
    const printContent = document.getElementById('invoicePrint').innerHTML;
    const originalContent = document.body.innerHTML;
    
    document.body.innerHTML = printContent;
    window.print();
    document.body.innerHTML = originalContent;
}

function downloadPDF() {
    window.location.href = 'generate_pdf.php?id=<?php echo $invoice_id; ?>';
}

function emailInvoice() {
    if(confirm('Send invoice to <?php echo $invoice['email']; ?>?')) {
        $.post('api.php', {
            action: 'email_invoice',
            invoice_id: <?php echo $invoice_id; ?>
        }, function(response) {
            alert(response.message);
        });
    }
}
</script>

<?php require_once '../../includes/footer.php'; ?>
Above is view.php
<?php
require_once '../../config/config.php';
require_once '../../config/database.php';

header('Content-Type: application/json');

$action = $_POST['action'] ?? $_GET['action'] ?? '';

switch($action) {
    case 'get_customers':
        $query = "SELECT id, customer_name FROM customers ORDER BY customer_name";
        $result = $db->query($query);
        $customers = [];
        
        while($row = $result->fetch_assoc()) {
            $customers[] = $row;
        }
        
        echo json_encode(['success' => true, 'data' => $customers]);
        break;

    case 'get_products':
        $query = "SELECT id, product_name, unit_price FROM products ORDER BY product_name";
        $result = $db->query($query);
        $products = [];
        
        while($row = $result->fetch_assoc()) {
            $products[] = $row;
        }
        
        echo json_encode(['success' => true, 'data' => $products]);
        break;

    case 'create':
        try {
            $db->query("START TRANSACTION");
            
            // Create invoice header
            $customerId = intval($_POST['customer_id']);
            $invoiceDate = $db->escape($_POST['invoice_date']);
            $dueDate = $db->escape($_POST['due_date']);
            $notes = $db->escape($_POST['notes']);
            $taxRate = floatval($_POST['tax_rate']);
            
            // Generate invoice number
            $year = date('Y');
            $month = date('m');
            $query = "SELECT COALESCE(MAX(CAST(SUBSTRING(invoice_number, 9) AS UNSIGNED)), 0) + 1 AS next_number 
                     FROM invoices 
                     WHERE invoice_number LIKE 'INV$year$month%'";
            $result = $db->query($query);
            $row = $result->fetch_assoc();
            $nextNumber = str_pad($row['next_number'], 4, '0', STR_PAD_LEFT);
            $invoiceNumber = "INV$year$month$nextNumber";
            
            // Calculate totals
            $subtotal = 0;
            foreach($_POST['items'] as $item) {
                $subtotal += floatval($item['quantity']) * floatval($item['unit_price']);
            }
            
            $taxAmount = $subtotal * ($taxRate / 100);
            $total = $subtotal + $taxAmount;
            
            // Insert invoice
            $query = "INSERT INTO invoices (
                        invoice_number, customer_id, invoice_date, due_date,
                        subtotal, tax_rate, tax_amount, total_amount, notes, status
                     ) VALUES (
                        '$invoiceNumber', $customerId, '$invoiceDate', '$dueDate',
                        $subtotal, $taxRate, $taxAmount, $total, '$notes', 'draft'
                     )";
            
            $db->query($query);
            $invoiceId = $db->getLastId();
            
            // Insert invoice items
            foreach($_POST['items'] as $item) {
                $productId = intval($item['product_id']);
                $quantity = intval($item['quantity']);
                $unitPrice = floatval($item['unit_price']);
                $total = $quantity * $unitPrice;
                
                $query = "INSERT INTO invoice_items (
                            invoice_id, product_id, quantity, unit_price, total_price
                         ) VALUES (
                            $invoiceId, $productId, $quantity, $unitPrice, $total
                         )";
                
                $db->query($query);
                
                // Update product stock
                $query = "UPDATE products 
                         SET stock_quantity = stock_quantity - $quantity 
                         WHERE id = $productId";
                $db->query($query);
            }
            
            $db->query("COMMIT");
            echo json_encode(['success' => true, 'message' => 'Invoice created successfully', 'invoice_id' => $invoiceId]);
            
        } catch (Exception $e) {
            $db->query("ROLLBACK");
            echo json_encode(['success' => false, 'message' => 'Failed to create invoice: ' . $e->getMessage()]);
        }
        break;

    case 'bulk_action':
        // ... (existing bulk action code)
        break;

    case 'setup_recurring':
        // ... (existing recurring setup code)
        break;
}
?>
above is api.php
// assets/js/invoice-create.js
$(document).ready(function() {
    let itemCount = 1;
    
    // Load customers
    $.get('api.php', { action: 'get_customers' }, function(response) {
        if(response.success) {
            const select = $('select[name="customer_id"]');
            response.data.forEach(customer => {
                select.append(`<option value="${customer.id}">${customer.customer_name}</option>`);
            });
        }
    });
    
    // Load products
    function loadProducts(select) {
        $.get('api.php', { action: 'get_products' }, function(response) {
            if(response.success) {
                response.data.forEach(product => {
                    select.append(`<option value="${product.id}" 
                        data-price="${product.unit_price}">${product.product_name}</option>`);
                });
            }
        });
    }
    
    loadProducts($('.product-select').first());
    
    // Add new item row
    $('#addItem').click(function() {
        const newRow = `
            <tr id="itemRow${itemCount}">
                <td>
                    <select class="form-select product-select" name="items[${itemCount}][product_id]" required>
                        <option value="">Select Product</option>
                    </select>
                </td>
                <td>
                    <input type="number" class="form-control quantity" 
                        name="items[${itemCount}][quantity]" min="1" value="1">
                </td>
                <td>
                    <input type="number" class="form-control unit-price" 
                        name="items[${itemCount}][unit_price]" readonly>
                </td>
                <td>
                    <input type="number" class="form-control item-total" readonly>
                </td>
                <td>
                    <button type="button" class="btn btn-danger btn-sm remove-item">
                        <i class="fas fa-trash"></i>
                    </button>
                </td>
            </tr>
        `;
        
        $('#invoiceItems tbody').append(newRow);
        loadProducts($(`#itemRow${itemCount} .product-select`));
        itemCount++;
    });
    
    // Remove item row
    $(document).on('click', '.remove-item', function() {
        $(this).closest('tr').remove();
        calculateTotals();
    });
    
    // Product selection change
    $(document).on('change', '.product-select', function() {
        const row = $(this).closest('tr');
        const selectedOption = $(this).find('option:selected');
        const price = selectedOption.data('price');
        
        row.find('.unit-price').val(price);
        calculateRowTotal(row);
    });
    
    // Quantity change
    $(document).on('change', '.quantity', function() {
        calculateRowTotal($(this).closest('tr'));
    });
    
    // Calculate row total
    function calculateRowTotal(row) {
        const quantity = parseFloat(row.find('.quantity').val()) || 0;
        const unitPrice = parseFloat(row.find('.unit-price').val()) || 0;
        const total = quantity * unitPrice;
        
        row.find('.item-total').val(total.toFixed(2));
        calculateTotals();
    }
    
    // Calculate invoice totals
    function calculateTotals() {
        let subtotal = 0;
        $('.item-total').each(function() {
            subtotal += parseFloat($(this).val()) || 0;
        });
        
        const taxRate = parseFloat($('input[name="tax_rate"]').val()) || 0;
        const taxAmount = subtotal * (taxRate / 100);
        const total = subtotal + taxAmount;
        
        $('#subtotal').val(subtotal.toFixed(2));
        $('#taxAmount').val(taxAmount.toFixed(2));
        $('#total').val(total.toFixed(2));
    }
    
    // Form submission
    $('#createInvoiceForm').submit(function(e) {
        e.preventDefault();
        
        $.ajax({
            url: 'api.php',
            type: 'POST',
            data: $(this).serialize() + '&action=create',
            success: function(response) {
                if(response.success) {
                    alert(response.message);
                    window.location.href = 'view.php?id=' + response.invoice_id;
                } else {
                    alert(response.message);
                }
            }
        });
    });
});
above is invoice-create.js from above get me proper sql