-- Create database
CREATE DATABASE IF NOT EXISTS FinancialApp;
USE FinancialApp;

-- --------------------
-- Table Creation
-- --------------------

-- Users Table
CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Customers Table
CREATE TABLE IF NOT EXISTS customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20),
    address TEXT,
    city VARCHAR(50),
    state VARCHAR(50),
    zip_code VARCHAR(10),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Products Table
CREATE TABLE IF NOT EXISTS products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    description TEXT,
    unit_price DECIMAL(10, 2) NOT NULL,
    stock_quantity INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Invoices Table
CREATE TABLE IF NOT EXISTS invoices (
    id INT PRIMARY KEY AUTO_INCREMENT,
    invoice_number VARCHAR(20) NOT NULL UNIQUE,
    customer_id INT NOT NULL,
    invoice_date DATE NOT NULL,
    due_date DATE NOT NULL,
    subtotal DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    tax_rate DECIMAL(5, 2) DEFAULT 0.00,
    tax_amount DECIMAL(10, 2) DEFAULT 0.00,
    total_amount DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    notes TEXT,
    status ENUM('pending', 'paid', 'cancelled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- Invoice Items Table
CREATE TABLE IF NOT EXISTS invoice_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    invoice_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    total_price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Payments Table
CREATE TABLE IF NOT EXISTS payments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    invoice_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    payment_date DATE NOT NULL,
    payment_method VARCHAR(50) NOT NULL,
    reference_number VARCHAR(100),
    notes TEXT,
    status ENUM('pending', 'completed', 'failed') DEFAULT 'completed',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id)
);

-- Recurring Invoices Table
CREATE TABLE IF NOT EXISTS recurring_invoices (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    frequency ENUM('weekly', 'monthly', 'quarterly', 'yearly') NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE,
    next_date DATE NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    notes TEXT,
    status ENUM('active', 'paused', 'completed') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- Recurring Items Table
CREATE TABLE IF NOT EXISTS recurring_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    recurring_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (recurring_id) REFERENCES recurring_invoices(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Invoice Templates Table
CREATE TABLE IF NOT EXISTS invoice_templates (
    id INT PRIMARY KEY AUTO_INCREMENT,
    template_name VARCHAR(100) NOT NULL,
    customer_type ENUM('all', 'corporate', 'individual') NOT NULL DEFAULT 'all',
    description TEXT,
    payment_terms INT DEFAULT 30,
    tax_rate DECIMAL(5, 2) DEFAULT 0.00,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Template Items Table
CREATE TABLE IF NOT EXISTS template_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    template_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (template_id) REFERENCES invoice_templates(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- --------------------
-- Views Creation
-- --------------------

CREATE VIEW vw_sales_summary AS
SELECT 
    COUNT(i.id) as total_invoices,
    SUM(i.total_amount) as total_revenue,
    AVG(i.total_amount) as avg_invoice_value
FROM invoices i
WHERE i.status = 'paid';

CREATE VIEW vw_customer_summary AS
SELECT 
    c.id,
    c.customer_name,
    COUNT(i.id) as invoice_count,
    SUM(i.total_amount) as total_sales,
    AVG(i.total_amount) as avg_invoice_value,
    MAX(i.invoice_date) as last_invoice_date
FROM customers c
LEFT JOIN invoices i ON c.id = i.customer_id
GROUP BY c.id;

CREATE VIEW vw_product_performance AS
SELECT 
    p.id,
    p.product_name,
    SUM(ii.quantity) as units_sold,
    SUM(ii.unit_price * ii.quantity) as total_sold,
    COUNT(DISTINCT i.id) as invoice_count
FROM products p
LEFT JOIN invoice_items ii ON p.id = ii.product_id
LEFT JOIN invoices i ON ii.invoice_id = i.id
GROUP BY p.id;

-- --------------------
-- Procedures Creation
-- --------------------

DELIMITER //

CREATE PROCEDURE sp_create_invoice_from_recurring (
    IN p_recurring_id INT
)
BEGIN
    -- Logic to generate an invoice from a recurring invoice process.
    DECLARE v_customer_id INT;
    DECLARE v_total_amount DECIMAL(10,2);
    
    SELECT customer_id, total_amount INTO v_customer_id, v_total_amount 
    FROM recurring_invoices WHERE id = p_recurring_id;
    
    INSERT INTO invoices (
        invoice_number, customer_id, invoice_date, due_date,
        subtotal, total_amount
    ) VALUES (
        CONCAT('INV', DATE_FORMAT(NOW(), '%Y%m%d'), '-', p_recurring_id),
        v_customer_id,
        CURDATE(),
        DATE_ADD(CURDATE(), INTERVAL 30 DAY),
        v_total_amount,
        v_total_amount
    );
END //

CREATE PROCEDURE sp_update_recurring_invoices()
BEGIN
    -- Logic to update the next date for active recurring invoices.
    UPDATE recurring_invoices 
    SET next_date = CASE frequency
        WHEN 'weekly' THEN DATE_ADD(next_date, INTERVAL 7 DAY)
        WHEN 'monthly' THEN DATE_ADD(next_date, INTERVAL 1 MONTH)
        WHEN 'quarterly' THEN DATE_ADD(next_date, INTERVAL 3 MONTH)
        WHEN 'yearly' THEN DATE_ADD(next_date, INTERVAL 1 YEAR)
    END
    WHERE status = 'active' AND next_date <= CURDATE();
END //

DELIMITER ;

-- ====================
-- Initial Setup Data (Optional)
-- ====================

INSERT INTO users (username, password, email) VALUES
('admin', '$2y$10$hashedpassword', 'admin@example.com'); -- Change this password hash to a real hash if above is the database below customers index.php not showing any data <?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 d-flex justify-content-between align-items-center">
                <h4>Customer Management</h4>
                <button type="button" class="btn btn-primary" data-bs-toggle="modal" data-bs-target="#addCustomerModal">
                    <i class="fas fa-plus"></i> Add New Customer
                </button>
            </div>
            <div class="card-body">
                <!-- Search and Filter Section -->
                <div class="row mb-3">
                    <div class="col-md-3">
                        <input type="text" id="customerSearch" class="form-control" placeholder="Search customers...">
                    </div>
                    <div class="col-md-2">
                        <select id="customerFilter" class="form-select">
                            <option value="">All Customers</option>
                            <option value="active">Active</option>
                            <option value="inactive">Inactive</option>
                        </select>
                    </div>
                </div>

                <!-- Customers Table -->
                <div class="table-responsive">
                    <table id="customersTable" class="table table-striped">
                        <thead>
                            <tr>
                                <th>ID</th>
                                <th>Name</th>
                                <th>Email</th>
                                <th>Phone</th>
                                <th>Total Sales</th>
                                <th>Status</th>
                                <th>Actions</th>
                            </tr>
                        </thead>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>

<!-- Add Customer Modal -->
<div class="modal fade" id="addCustomerModal">
    <div class="modal-dialog modal-lg">
        <div class="modal-content">
            <div class="modal-header">
                <h5 class="modal-title">Add New Customer</h5>
                <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
            </div>
            <form id="addCustomerForm">
                <div class="modal-body">
                    <div class="row">
                        <div class="col-md-6">
                            <div class="mb-3">
                                <label class="form-label">Customer Name *</label>
                                <input type="text" class="form-control" name="customer_name" required>
                            </div>
                            <div class="mb-3">
                                <label class="form-label">Email *</label>
                                <input type="email" class="form-control" name="email" required>
                            </div>
                            <div class="mb-3">
                                <label class="form-label">Phone</label>
                                <input type="tel" class="form-control" name="phone">
                            </div>
                            <div class="mb-3">
                                <label class="form-label">Company</label>
                                <input type="text" class="form-control" name="company">
                            </div>
                        </div>
                        <div class="col-md-6">
                            <div class="mb-3">
                                <label class="form-label">Address</label>
                                <textarea class="form-control" name="address" rows="2"></textarea>
                            </div>
                            <div class="mb-3">
                                <label class="form-label">City</label>
                                <input type="text" class="form-control" name="city">
                            </div>
                            <div class="mb-3">
                                <label class="form-label">State</label>
                                <input type="text" class="form-control" name="state">
                            </div>
                            <div class="mb-3">
                                <label class="form-label">ZIP Code</label>
                                <input type="text" class="form-control" name="zip_code">
                            </div>
                        </div>
                    </div>
                    <div class="mb-3">
                        <label class="form-label">Notes</label>
                        <textarea class="form-control" name="notes" rows="3"></textarea>
                    </div>
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
                    <button type="submit" class="btn btn-primary">Save Customer</button>
                </div>
            </form>
        </div>
    </div>
</div>

<!-- Edit Customer Modal -->
<div class="modal fade" id="editCustomerModal">
    <div class="modal-dialog modal-lg">
        <div class="modal-content">
            <div class="modal-header">
                <h5 class="modal-title">Edit Customer</h5>
                <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
            </div>
            <form id="editCustomerForm">
                <input type="hidden" name="customer_id">
                <div class="modal-body">
                    <div class="row">
                        <div class="col-md-6">
                            <div class="mb-3">
                                <label class="form-label">Customer Name *</label>
                                <input type="text" class="form-control" name="customer_name" required>
                            </div>
                            <div class="mb-3">
                                <label class="form-label">Email *</label>
                                <input type="email" class="form-control" name="email" required>
                            </div>
                            <div class="mb-3">
                                <label class="form-label">Phone</label>
                                <input type="tel" class="form-control" name="phone">
                            </div>
                            <div class="mb-3">
                                <label class="form-label">Company</label>
                                <input type="text" class="form-control" name="company">
                            </div>
                        </div>
                        <div class="col-md-6">
                            <div class="mb-3">
                                <label class="form-label">Address</label>
                                <textarea class="form-control" name="address" rows="2"></textarea>
                            </div>
                            <div class="mb-3">
                                <label class="form-label">City</label>
                                <input type="text" class="form-control" name="city">
                            </div>
                            <div class="mb-3">
                                <label class="form-label">State</label>
                                <input type="text" class="form-control" name="state">
                            </div>
                            <div class="mb-3">
                                <label class="form-label">ZIP Code</label>
                                <input type="text" class="form-control" name="zip_code">
                            </div>
                        </div>
                    </div>
                    <div class="mb-3">
                        <label class="form-label">Notes</label>
                        <textarea class="form-control" name="notes" rows="3"></textarea>
                    </div>
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
                    <button type="submit" class="btn btn-primary">Update Customer</button>
                </div>
            </form>
        </div>
    </div>
</div>

<!-- View Customer Details Modal -->
<div class="modal fade" id="viewCustomerModal">
    <div class="modal-dialog modal-lg">
        <div class="modal-content">
            <div class="modal-header">
                <h5 class="modal-title">Customer Details</h5>
                <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
            </div>
            <div class="modal-body">
                <div class="row">
                    <div class="col-md-6">
                        <h6>Basic Information</h6>
                        <table class="table table-borderless">
                            <tr>
                                <th>Name:</th>
                                <td id="view_customer_name"></td>
                            </tr>
                            <tr>
                                <th>Email:</th>
                                <td id="view_email"></td>
                            </tr>
                            <tr>
                                <th>Phone:</th>
                                <td id="view_phone"></td>
                            </tr>
                            <tr>
                                <th>Company:</th>
                                <td id="view_company"></td>
                            </tr>
                        </table>
                    </div>
                    <div class="col-md-6">
                        <h6>Address Information</h6>
                        <table class="table table-borderless">
                            <tr>
                                <th>Address:</th>
                                <td id="view_address"></td>
                            </tr>
                            <tr>
                                <th>City:</th>
                                <td id="view_city"></td>
                            </tr>
                            <tr>
                                <th>State:</th>
                                <td id="view_state"></td>
                            </tr>
                            <tr>
                                <th>ZIP Code:</th>
                                <td id="view_zip_code"></td>
                            </tr>
                        </table>
                    </div>
                </div>
                <div class="row mt-4">
                    <div class="col-md-12">
                        <h6>Customer Statistics</h6>
                        <div class="row">
                            <div class="col-md-3">
                                <div class="card bg-primary text-white">
                                    <div class="card-body">
                                        <h6>Total Sales</h6>
                                        <h4 id="view_total_sales">$0.00</h4>
                                    </div>
                                </div>
                            </div>
                            <div class="col-md-3">
                                <div class="card bg-success text-white">
                                    <div class="card-body">
                                        <h6>Total Invoices</h6>
                                        <h4 id="view_total_invoices">0</h4>
                                    </div>
                                </div>
                            </div>
                            <div class="col-md-3">
                                <div class="card bg-info text-white">
                                    <div class="card-body">
                                        <h6>Average Invoice</h6>
                                        <h4 id="view_avg_invoice">$0.00</h4>
                                    </div>
                                </div>
                            </div>
                            <div class="col-md-3">
                                <div class="card bg-warning text-white">
                                    <div class="card-body">
                                        <h6>Outstanding</h6>
                                        <h4 id="view_outstanding">$0.00</h4>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

<!-- Customer JavaScript -->
<script src="<?php echo SITE_URL; ?>/assets/js/customers.js"></script>

<script>
$(document).ready(function() {
    loadCustomers();

    // Search and Filter
    $("#customerSearch").on("keyup", function() {
        loadCustomers($("#customerSearch").val(), $("#customerFilter").val());
    });
    
    $("#customerFilter").on("change", function() {
        loadCustomers($("#customerSearch").val(), $("#customerFilter").val());
    });

    // Add customer form submission
    $('#addCustomerForm').submit(function(e) {
        e.preventDefault();
        $.post('api.php', $(this).serialize() + '&action=addCustomer', function(response) {
            if(response.success) {
                $('#addCustomerModal').modal('hide');
                loadCustomers();
                toastr.success('Customer added successfully');
            } else {
                toastr.error(response.message);
            }
        });
    });

    // Edit customer form submission
    $('#editCustomerForm').submit(function(e) {
        e.preventDefault();
        $.post('api.php', $(this).serialize() + '&action=editCustomer', function(response) {
            if(response.success) {
                $('#editCustomerModal').modal('hide');
                loadCustomers();
                toastr.success('Customer updated successfully');
            } else {
                toastr.error(response.message);
            }
        });
    });
});

function loadCustomers(searchKeyword = '', filterStatus = '') {
    $.get('api.php', {
        action: 'listCustomers',
        search: searchKeyword,
        status: filterStatus
    }, function(response) {
        if(response.success) {
            const customersTable = $('#customersTable').DataTable();
            customersTable.clear().rows.add(response.data).draw();
        } else {
            toastr.error(response.message);
        }
    });
}

function viewCustomerDetails(customerId) {
    $.get('api.php', { action: 'getCustomer', id: customerId }, function(response) {
        if(response.success) {
            const customer = response.data;
            $('#view_customer_name').text(customer.customer_name);
            $('#view_email').text(customer.email);
            $('#view_phone').text(customer.phone);
            $('#view_company').text(customer.company);
            $('#view_address').text(customer.address);
            $('#view_city').text(customer.city);
            $('#view_state').text(customer.state);
            $('#view_zip_code').text(customer.zip_code);
            
            // You can include calculations for total sales, avg invoice, and outstanding amount as needed
            $('#view_total_sales').text(formatCurrency(customer.total_sales));
            $('#view_total_invoices').text(customer.invoice_count);
            $('#view_avg_invoice').text(formatCurrency(customer.avg_invoice_value));
            $('#view_outstanding').text(formatCurrency(customer.outstanding_amount));

            $('#viewCustomerModal').modal('show');
        } else {
            toastr.error(response.message);
        }
    });
}

function editCustomer(customerId) {
    $.get('api.php', { action: 'getCustomer', id: customerId }, function(response) {
        if(response.success) {
            const customer = response.data;
            $('[name="customer_id"]').val(customer.id);
            $('[name="customer_name"]').val(customer.customer_name);
            $('[name="email"]').val(customer.email);
            $('[name="phone"]').val(customer.phone);
            $('[name="company"]').val(customer.company);
            $('[name="address"]').val(customer.address);
            $('[name="city"]').val(customer.city);
            $('[name="state"]').val(customer.state);
            $('[name="zip_code"]').val(customer.zip_code);
            $('#editCustomerModal').modal('show');
        } else {
            toastr.error(response.message);
        }
    });
}
</script>

<?php require_once '../../includes/footer.php'; ?>  when api.php as below <?php
require_once '../../config/config.php';
require_once '../../config/database.php';

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

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

switch ($action) {
    case 'listCustomers':
        listCustomers($db);
        break;
    case 'addCustomer':
        addCustomer($db);
        break;
    case 'editCustomer':
        editCustomer($db);
        break;
    case 'deleteCustomer':
        deleteCustomer($db);
        break;
    case 'getCustomer':
        getCustomerDetails($db);
        break;
    default:
        echo json_encode(['success' => false, 'message' => 'Invalid action']);
        break;
}

// Function to list customers
function listCustomers($db) {
    $search = $_GET['search'] ?? '';
    $filter = $_GET['status'] ?? '';

    $query = "SELECT c.*, 
              (SELECT COALESCE(SUM(i.total_amount), 0) FROM invoices i WHERE i.customer_id = c.id) AS total_sales,
              (SELECT COUNT(i.id) FROM invoices i WHERE i.customer_id = c.id) AS invoice_count
              FROM customers c 
              WHERE 1=1";

    if (!empty($search)) {
        $query .= " AND (c.customer_name LIKE '%$search%' OR c.email LIKE '%$search%' OR c.phone LIKE '%$search%')";
    }
    
    if ($filter === 'active') {
        // Add filter for active customers, if applicable
        // If you have a status column, uncomment the following line
        // $query .= " AND c.status = 'active'";
    } elseif ($filter === 'inactive') {
        // Add filter for inactive customers, if applicable
        // If you have a status column, uncomment the following line
        // $query .= " AND c.status = 'inactive'";
    }

    $result = $db->query($query);
    $customers = $result->fetch_all(MYSQLI_ASSOC);
    
    echo json_encode(['success' => true, 'data' => $customers]);
}

// Function to add a new customer
function addCustomer($db) {
    $data = json_decode(file_get_contents("php://input"), true);
    
    $customerName = $db->escape($data['customer_name']);
    $email = $db->escape($data['email']);
    $phone = $db->escape($data['phone']);
    $company = $db->escape($data['company']);
    $address = $db->escape($data['address']);
    $city = $db->escape($data['city']);
    $state = $db->escape($data['state']);
    $zipCode = $db->escape($data['zip_code']);
    $notes = $db->escape($data['notes']);

    $query = "INSERT INTO customers (customer_name, email, phone, company, address, city, state, zip_code, notes) 
              VALUES ('$customerName', '$email', '$phone', '$company', '$address', '$city', '$state', '$zipCode', '$notes')";
              
    if ($db->query($query)) {
        echo json_encode(['success' => true, 'message' => 'Customer added successfully']);
    } else {
        echo json_encode(['success' => false, 'message' => 'Failed to add customer']);
    }
}

// Function to edit a customer
function editCustomer($db) {
    $data = json_decode(file_get_contents("php://input"), true);
    
    $customerId = intval($data['customer_id']);
    $customerName = $db->escape($data['customer_name']);
    $email = $db->escape($data['email']);
    $phone = $db->escape($data['phone']);
    $company = $db->escape($data['company']);
    $address = $db->escape($data['address']);
    $city = $db->escape($data['city']);
    $state = $db->escape($data['state']);
    $zipCode = $db->escape($data['zip_code']);
    $notes = $db->escape($data['notes']);

    $query = "UPDATE customers SET 
              customer_name = '$customerName', 
              email = '$email', 
              phone = '$phone', 
              company = '$company', 
              address = '$address', 
              city = '$city', 
              state = '$state', 
              zip_code = '$zipCode', 
              notes = '$notes' 
              WHERE id = $customerId";
    
    if ($db->query($query)) {
        echo json_encode(['success' => true, 'message' => 'Customer updated successfully']);
    } else {
        echo json_encode(['success' => false, 'message' => 'Failed to update customer']);
    }
}

// Function to delete a customer
function deleteCustomer($db) {
    $data = json_decode(file_get_contents("php://input"), true);
    
    $customerId = intval($data['customer_id']);
    
    $query = "DELETE FROM customers WHERE id = $customerId";

    if ($db->query($query)) {
        echo json_encode(['success' => true, 'message' => 'Customer deleted successfully']);
    } else {
        echo json_encode(['success' => false, 'message' => 'Failed to delete customer']);
    }
}

// Function to get customer details
function getCustomerDetails($db) {
    $customerId = intval($_GET['id']);
    
    $query = "SELECT c.*, 
              (SELECT COALESCE(SUM(i.total_amount), 0) FROM invoices i WHERE i.customer_id = c.id) AS total_sales,
              (SELECT COUNT(i.id) FROM invoices i WHERE i.customer_id = c.id) AS invoice_count,
              (SELECT AVG(i.total_amount) FROM invoices i WHERE i.customer_id = c.id) AS avg_invoice_value,
              (SELECT COALESCE(SUM(i.total_amount), 0) FROM invoices i WHERE i.customer_id = c.id AND i.status != 'paid') AS outstanding_amount
              FROM customers c 
              WHERE c.id = $customerId";

    $result = $db->query($query);
    
    if ($customer = $result->fetch_assoc()) {
        echo json_encode(['success' => true, 'data' => $customer]);
    } else {
        echo json_encode(['success' => false, 'message' => 'Failed to fetch customer details']);
    }
}
?>