i want to fix this accountant/create_invoice.php (<?php
// accountant/create_invoice.php

session_start();
require_once '../db_connect.php';

// Check if accountant is logged in
if (!isset($_SESSION['accountant_id'])) {
    header("Location: login.php");
    exit();
}

// Retrieve the list of services for the dropdown
try {
    $stmt_services = $pdo->prepare("SELECT service_id, service_name, service_price FROM services");
    $stmt_services->execute();
    $services = $stmt_services->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
    echo "Error fetching services: " . htmlspecialchars($e->getMessage());
    exit();
}

// Fetch patient details via AJAX
if (isset($_GET['search_patient'])) {
    $search_query = '%' . $_GET['search_patient'] . '%';
    $search_id = null;
    if (is_numeric($_GET['search_patient'])) {
        $search_id = intval($_GET['search_patient']);
    }

    $sql_patient = "SELECT id, name, phone, email, date_of_birth, registration_date 
                    FROM patients 
                    WHERE name LIKE :search_query";
    if ($search_id !== null) {
        $sql_patient .= " OR id = :search_id";
    }
    $stmt_patient = $pdo->prepare($sql_patient);
    $stmt_patient->bindParam(':search_query', $search_query, PDO::PARAM_STR);
    if ($search_id !== null) {
        $stmt_patient->bindParam(':search_id', $search_id, PDO::PARAM_INT);
    }
    $stmt_patient->execute();
    $patients = $stmt_patient->fetchAll(PDO::FETCH_ASSOC);

    echo json_encode($patients);
    exit();
}

// Handle invoice submission
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['submit_invoice'])) {
    // Input validation
    $patient_id = isset($_POST['patient_id']) ? intval($_POST['patient_id']) : null;
    $services_selected = isset($_POST['services']) ? $_POST['services'] : [];
    $notes = isset($_POST['notes']) ? trim($_POST['notes']) : '';
    $discount = isset($_POST['discount']) ? floatval($_POST['discount']) : 0;
    $tax_rate = isset($_POST['tax_rate']) ? floatval($_POST['tax_rate']) : 0;

    if (empty($patient_id)) {
        $error = "Please select a patient.";
    } elseif (empty($services_selected)) {
        $error = "Please select at least one service.";
    } else {
        try {
            $pdo->beginTransaction();

            // Calculate subtotal
            $subtotal = 0;
            $invoice_services_data = [];
            foreach ($services_selected as $service_id) {
                $service_id = intval($service_id);
                $sql_service = "SELECT service_name, service_price FROM services WHERE service_id = :service_id";
                $stmt_service = $pdo->prepare($sql_service);
                $stmt_service->bindParam(':service_id', $service_id, PDO::PARAM_INT);
                $stmt_service->execute();
                $service = $stmt_service->fetch(PDO::FETCH_ASSOC);

                if ($service) {
                    $subtotal += floatval($service['service_price']);
                    $invoice_services_data[] = [
                        'service_id' => $service_id,
                        'service_name' => $service['service_name'],
                        'cost' => $service['service_price']
                    ];
                } else {
                    $pdo->rollBack();
                    $error = "Selected service not found.";
                    break;
                }
            }

            if (!isset($error)) {
                // Apply discount
                $taxable_amount = max($subtotal - $discount, 0);

                // Calculate tax amount
                $tax_amount = $taxable_amount * ($tax_rate / 100);
                $tax_amount = round($tax_amount, 2); // Round to 2 decimal places

                // Calculate total amount
                $total_amount = $taxable_amount + $tax_amount;

                // Prepare service names for storing in the invoice
                $service_names = implode(", ", array_column($invoice_services_data, 'service_name'));

                // Insert invoice into invoices table
                $sql_invoice = "INSERT INTO invoices (patient_id, service_name, cost, tax_rate, discount, total_amount, invoice_date, notes)
                                VALUES (:patient_id, :service_name, :cost, :tax_rate, :discount, :total_amount, CURRENT_DATE, :notes)";
                $stmt_invoice = $pdo->prepare($sql_invoice);
                $stmt_invoice->bindParam(':patient_id', $patient_id, PDO::PARAM_INT);
                $stmt_invoice->bindParam(':service_name', $service_names);
                $stmt_invoice->bindParam(':cost', $subtotal);
                $stmt_invoice->bindParam(':tax_rate', $tax_rate);
                $stmt_invoice->bindParam(':discount', $discount);
                $stmt_invoice->bindParam(':total_amount', $total_amount);
                $stmt_invoice->bindParam(':notes', $notes);
                $stmt_invoice->execute();

                $invoice_id = $pdo->lastInsertId();

                // Insert services into invoice_services table
                $sql_invoice_service = "INSERT INTO invoice_services (invoice_id, service_id, service_name, cost)
                                        VALUES (:invoice_id, :service_id, :service_name, :cost)";
                $stmt_invoice_service = $pdo->prepare($sql_invoice_service);
                foreach ($invoice_services_data as $service_data) {
                    $stmt_invoice_service->bindParam(':invoice_id', $invoice_id, PDO::PARAM_INT);
                    $stmt_invoice_service->bindParam(':service_id', $service_data['service_id'], PDO::PARAM_INT);
                    $stmt_invoice_service->bindParam(':service_name', $service_data['service_name']);
                    $stmt_invoice_service->bindParam(':cost', $service_data['cost']);
                    $stmt_invoice_service->execute();
                }

                $pdo->commit();

                // Redirect to view invoice
                header('Location: view_invoice.php?id=' . $invoice_id);
                exit();
            }
        } catch (Exception $e) {
            $pdo->rollBack();
            $error = "Error creating invoice: " . htmlspecialchars($e->getMessage());
        }
    }
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Create Invoice</title>
    <!-- Meta tags for responsiveness -->
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <!-- Include Bootstrap CSS -->
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <!-- Custom Styles -->
    <style>
        /* Import Google Fonts */
        @import url('https://fonts.googleapis.com/css2?family=Poppins:wght@400;600;700&display=swap');

        :root {
            --primary-color: #4F3F02;
            --secondary-color: #D1C287;
            --background-color: #F8F9FA;
            --card-background: #FFFFFF;
            --text-color: #333333;
            --danger-color: #dc3545;
            --font-family: 'Poppins', sans-serif;
        }

        body {
            background-color: var(--background-color);
            font-family: var(--font-family);
            color: var(--text-color);
            margin: 0;
            padding: 0;
        }

        .dashboard-header {
            background-color: var(--primary-color);
            color: #fff;
            padding: 30px;
            text-align: center;
            border-radius: 0 0 25px 25px;
            margin-bottom: 40px;
        }

        .dashboard-header h1 {
            font-weight: 700;
        }

        .dashboard-header p a {
            color: #fff;
            margin: 0 10px;
            text-decoration: none;
        }

        .dashboard-header p a:hover {
            text-decoration: underline;
        }

        .container {
            max-width: 900px;
            margin: 0 auto;
            background-color: var(--card-background);
            padding: 30px;
            border-radius: 15px;
            box-shadow: 0 6px 10px rgba(0, 0, 0, 0.1);
        }

        h2 {
            color: var(--primary-color);
            font-weight: 700;
            margin-bottom: 25px;
        }

        .form-label {
            font-weight: 600;
            margin-bottom: 10px;
        }

        .form-control {
            margin-bottom: 20px;
            border-radius: 10px;
        }

        .btn-custom {
            background-color: var(--secondary-color);
            border-color: var(--secondary-color);
            color: var(--primary-color);
            font-weight: 600;
            padding: 12px 30px;
            border-radius: 10px;
            transition: all 0.3s ease;
        }

        .btn-custom:hover {
            background-color: var(--primary-color);
            color: #fff;
            border-color: var(--secondary-color);
        }

        .error {
            color: var(--danger-color);
            font-weight: 600;
            margin-bottom: 20px;
        }

        /* Patient Search Results */
        #patient_results {
            background: #fff;
            border: 1px solid #ddd;
            position: absolute;
            z-index: 1000;
            width: 100%;
            max-height: 200px;
            overflow-y: auto;
        }

        #patient_results div {
            padding: 10px;
            cursor: pointer;
        }

        #patient_results div:hover {
            background-color: #f1f1f1;
        }

        /* Service Rows */
        .service-row {
            display: flex;
            align-items: center;
            margin-bottom: 15px;
        }

        .service-row .form-select {
            flex: 1;
            margin-right: 10px;
        }

        /* Invoice Summary */
        .invoice-summary {
            margin-top: 30px;
            padding-top: 30px;
            border-top: 1px solid #ddd;
        }

        /* Responsive Adjustments */
        @media (max-width: 768px) {
            .service-row {
                flex-direction: column;
            }

            .service-row .form-select,
            .service-row button {
                width: 100%;
                margin-bottom: 10px;
            }
        }
    </style>
    <!-- Include jQuery (needed for AJAX patient search) -->
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
</head>
<body>

    <div class="dashboard-header">
        <h1>Create Invoice</h1>
        <p>
            <a href="manage_invoices.php">Manage Invoices</a> | 
            <a href="dashboard.php">Dashboard</a> | 
            <a href="accountant_profile.php">Profile</a> | 
            <a href="logout.php">Logout</a>
        </p>
    </div>

    <div class="container">
        <?php if (isset($error)): ?>
            <div class="error"><?= htmlspecialchars($error) ?></div>
        <?php endif; ?>
        <form method="POST" action="" id="invoiceForm">
            <!-- Patient Selection -->
            <div class="mb-4 position-relative">
                <label for="search_patient" class="form-label">Search Patient:</label>
                <input type="text" name="search_patient" id="search_patient" class="form-control" placeholder="Type patient name or ID">
                <div id="patient_results" style="display: none;"></div>
            </div>

            <div id="patient_details" style="display:none;">
                <h2>Patient Details</h2>
                <p><strong>Name:</strong> <span id="patient_name"></span></p>
                <p><strong>Phone:</strong> <span id="patient_phone"></span></p>
                <p><strong>Email:</strong> <span id="patient_email"></span></p>
                <p><strong>Date of Birth:</strong> <span id="patient_dob"></span></p>
                <p><strong>Registration Date:</strong> <span id="patient_registration"></span></p>
                <input type="hidden" name="patient_id" id="patient_id">
            </div>

            <!-- Services Selection -->
            <h2>Services</h2>
            <div id="services">
                <div class="service-row">
                    <select name="services[]" class="form-select" onchange="calculateSubtotal()">
                        <option value="">Select a Service</option>
                        <?php foreach ($services as $service): ?>
                            <option value="<?= htmlspecialchars($service['service_id']) ?>" data-price="<?= htmlspecialchars($service['service_price']) ?>">
                                <?= htmlspecialchars($service['service_name']) ?> - $<?= number_format($service['service_price'], 2) ?>
                            </option>
                        <?php endforeach; ?>
                    </select>
                    <button type="button" class="btn btn-danger" onclick="removeServiceRow(this)">Remove</button>
                </div>
            </div>
            <button type="button" class="btn btn-secondary mb-4" onclick="addServiceRow()">Add Another Service</button>

            <!-- Invoice Summary -->
            <div class="invoice-summary">
                <h2>Invoice Summary</h2>
                <div class="row">
                    <div class="col-md-6 mb-3">
                        <label for="subtotal" class="form-label">Subtotal:</label>
                        <input type="text" name="subtotal" id="subtotal" value="0.00" class="form-control" readonly>
                    </div>
                    <div class="col-md-6 mb-3">
                        <label for="discount" class="form-label">Discount (Amount):</label>
                        <input type="text" name="discount" id="discount" value="0" class="form-control" oninput="calculateTotal()">
                    </div>
                    <div class="col-md-6 mb-3">
                        <label for="tax_rate" class="form-label">Tax Rate (%):</label>
                        <input type="text" name="tax_rate" id="tax_rate" value="0" class="form-control" oninput="calculateTotal()">
                    </div>
                    <div class="col-md-6 mb-3">
                        <label for="total_amount" class="form-label">Total Amount:</label>
                        <input type="text" name="total_amount" id="total_amount" value="0.00" class="form-control" readonly>
                    </div>
                </div>
            </div>

            <!-- Notes -->
            <div class="mb-4">
                <label for="notes" class="form-label">Notes:</label>
                <textarea name="notes" id="notes" class="form-control" placeholder="Add any notes..."></textarea>
            </div>

            <!-- Submit Button -->
            <button type="submit" name="submit_invoice" class="btn btn-custom">Create Invoice</button>
        </form>
    </div>

    <!-- Bootstrap JS and dependencies -->
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
    <!-- JavaScript functions -->
    <script>
        $(document).ready(function() {
            // Fetch Patients via AJAX
            $('#search_patient').on('keyup', function() {
                const query = $(this).val();
                const resultsDiv = $('#patient_results');

                if (query.length > 2) {
                    $.ajax({
                        url: 'create_invoice.php',
                        method: 'GET',
                        data: { search_patient: query },
                        success: function(data) {
                            const patients = JSON.parse(data);
                            resultsDiv.empty();

                            if (patients.length > 0) {
                                patients.forEach(function(patient) {
                                    const patientDiv = $('<div>');
                                    patientDiv.html(`<strong>${patient.name}</strong><br>ID: ${patient.id}<br>
                                        Phone: ${patient.phone}<br>Email: ${patient.email}`);
                                    patientDiv.on('click', function() {
                                        selectPatient(patient);
                                    });
                                    resultsDiv.append(patientDiv);
                                });
                                resultsDiv.show();
                            } else {
                                resultsDiv.html('<div>No patients found</div>').show();
                            }
                        }
                    });
                } else {
                    resultsDiv.hide();
                }
            });

            // Hide patient results when clicking outside
            $(document).on('click', function(e) {
                if (!$(e.target).closest('#search_patient, #patient_results').length) {
                    $('#patient_results').hide();
                }
            });
        });

        function selectPatient(patient) {
            $('#patient_id').val(patient.id);
            $('#patient_name').text(patient.name);
            $('#patient_phone').text(patient.phone);
            $('#patient_email').text(patient.email);
            $('#patient_dob').text(patient.date_of_birth);
            $('#patient_registration').text(patient.registration_date);

            $('#patient_details').show();
            $('#patient_results').hide();
            $('#search_patient').val('');
        }

        function addServiceRow() {
            const servicesDiv = $('#services');
            const newServiceRow = $(`
                <div class="service-row">
                    <select name="services[]" class="form-select" onchange="calculateSubtotal()">
                        <option value="">Select a Service</option>
                        <?php foreach ($services as $service): ?>
                            <option value="<?= htmlspecialchars($service['service_id']) ?>" data-price="<?= htmlspecialchars($service['service_price']) ?>">
                                <?= htmlspecialchars($service['service_name']) ?> - $<?= number_format($service['service_price'], 2) ?>
                            </option>
                        <?php endforeach; ?>
                    </select>
                    <button type="button" class="btn btn-danger" onclick="removeServiceRow(this)">Remove</button>
                </div>
            `);
            servicesDiv.append(newServiceRow);
        }

        function removeServiceRow(button) {
            $(button).closest('.service-row').remove();
            calculateSubtotal();
        }

        function calculateSubtotal() {
            let subtotal = 0;
            $('select[name="services[]"]').each(function() {
                const selectedOption = $(this).find('option:selected');
                if (selectedOption.data('price')) {
                    subtotal += parseFloat(selectedOption.data('price'));
                }
            });
            $('#subtotal').val(subtotal.toFixed(2));
            calculateTotal();
        }

        function calculateTotal() {
            const subtotal = parseFloat($('#subtotal').val()) || 0;
            const discount = parseFloat($('#discount').val()) || 0;
            const tax_rate = parseFloat($('#tax_rate').val()) || 0;

            // Apply discount first
            let taxable_amount = subtotal - discount;
            if (taxable_amount < 0) {
                taxable_amount = 0;
            }

            // Calculate tax on taxable amount
            const tax_amount = taxable_amount * tax_rate / 100;

            // Calculate total amount
            const total_amount = taxable_amount + tax_amount;

            $('#total_amount').val(total_amount.toFixed(2));
        }
    </script>
</body>
</html>) and this is database (-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: Oct 07, 2024 at 09:03 PM
-- Server version: 10.11.9-MariaDB
-- PHP Version: 7.2.34

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `u406552082_info`
--

-- --------------------------------------------------------

--
-- Table structure for table `admins`
--

CREATE TABLE `admins` (
  `id` int(11) NOT NULL,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `email` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `budgets`
--

CREATE TABLE `budgets` (
  `id` int(11) NOT NULL,
  `month` varchar(7) DEFAULT NULL,
  `budgeted_revenue` decimal(10,2) DEFAULT NULL,
  `budgeted_expenses` decimal(10,2) DEFAULT NULL,
  `notes` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `comments`
--

CREATE TABLE `comments` (
  `id` int(11) NOT NULL,
  `patient_id` int(11) NOT NULL,
  `sender` enum('patient','doctor','admin') NOT NULL,
  `message` text NOT NULL,
  `timestamp` datetime DEFAULT current_timestamp(),
  `parent_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `doctors`
--

CREATE TABLE `doctors` (
  `id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `username` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `specialization` varchar(255) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `registration_date` date DEFAULT curdate(),
  `profile_picture` varchar(255) DEFAULT NULL,
  `department` varchar(100) DEFAULT NULL,
  `date_of_joining` date DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `doctor_patient`
--

CREATE TABLE `doctor_patient` (
  `id` int(11) NOT NULL,
  `doctor_id` int(11) NOT NULL,
  `patient_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `employees`
--

CREATE TABLE `employees` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `role` enum('admin','doctor','staff','accountant') NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `department` varchar(100) DEFAULT NULL,
  `date_of_joining` date DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `username` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `profile_picture` varchar(255) DEFAULT NULL,
  `specialization` varchar(255) DEFAULT NULL,
  `is_online` tinyint(1) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `health_records`
--

CREATE TABLE `health_records` (
  `id` int(11) NOT NULL,
  `patient_id` int(11) NOT NULL,
  `blood_pressure` varchar(20) DEFAULT NULL,
  `weight` decimal(5,2) DEFAULT NULL,
  `bmi` decimal(4,2) DEFAULT NULL,
  `cholesterol` decimal(5,2) DEFAULT NULL,
  `prescription` text DEFAULT NULL,
  `treatment_progress` text DEFAULT NULL,
  `record_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `insurance_claims`
--

CREATE TABLE `insurance_claims` (
  `id` int(11) NOT NULL,
  `patient_id` int(11) DEFAULT NULL,
  `invoice_id` int(11) DEFAULT NULL,
  `claim_amount` decimal(10,2) DEFAULT NULL,
  `status` enum('Pending','Approved','Rejected') DEFAULT NULL,
  `submission_date` date DEFAULT NULL,
  `processing_date` date DEFAULT NULL,
  `notes` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `invoices`
--

CREATE TABLE `invoices` (
  `id` int(11) NOT NULL,
  `patient_id` int(11) NOT NULL,
  `service_name` varchar(255) NOT NULL,
  `service_description` text DEFAULT NULL,
  `cost` decimal(10,2) NOT NULL,
  `tax_rate` decimal(5,2) DEFAULT 0.00,
  `discount` decimal(5,2) DEFAULT 0.00,
  `total_amount` decimal(10,2) NOT NULL,
  `invoice_date` date DEFAULT curdate(),
  `due_date` date DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `tax` decimal(5,2) DEFAULT 0.00
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `invoice_services`
--

CREATE TABLE `invoice_services` (
  `id` int(11) NOT NULL,
  `invoice_id` int(11) NOT NULL,
  `service_id` int(11) NOT NULL,
  `service_name` varchar(255) NOT NULL,
  `cost` decimal(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `patients`
--

CREATE TABLE `patients` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `age` int(11) NOT NULL,
  `gender` enum('Male','Female','Other') NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `medical_history` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `date_of_birth` date DEFAULT NULL,
  `registration_datetime` datetime DEFAULT current_timestamp(),
  `registration_date` date DEFAULT NULL,
  `status` enum('Initial Consultation','Diagnostic Phase','Treatment in Progress','Improving','Monitoring','Maintenance Phase','Awaiting Test Results','Follow-up Needed','Paused','Completed Program','Relapsed','Discontinued','Inactive') DEFAULT 'Initial Consultation',
  `doctor_id` int(11) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `patient_logins`
--

CREATE TABLE `patient_logins` (
  `id` int(11) NOT NULL,
  `patient_id` int(11) NOT NULL,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `payments`
--

CREATE TABLE `payments` (
  `id` int(11) NOT NULL,
  `patient_id` int(11) NOT NULL,
  `invoice_id` int(11) NOT NULL,
  `amount_paid` decimal(10,2) NOT NULL,
  `payment_date` date NOT NULL,
  `payment_method` varchar(50) DEFAULT NULL,
  `notes` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `prescriptions`
--

CREATE TABLE `prescriptions` (
  `id` int(11) NOT NULL,
  `patient_id` int(11) NOT NULL,
  `doctor_id` int(11) DEFAULT NULL,
  `medications` text NOT NULL,
  `instructions` text DEFAULT NULL,
  `date_prescribed` datetime DEFAULT current_timestamp(),
  `status` enum('active','inactive') DEFAULT 'active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `salaries`
--

CREATE TABLE `salaries` (
  `id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `basic_salary` decimal(10,2) NOT NULL,
  `bonuses` decimal(10,2) DEFAULT 0.00,
  `deductions` decimal(10,2) DEFAULT 0.00,
  `overtime_hours` int(11) DEFAULT 0,
  `overtime_rate` decimal(10,2) DEFAULT 0.00,
  `tax` decimal(10,2) DEFAULT 0.00,
  `payment_date` date NOT NULL,
  `net_salary` decimal(10,2) NOT NULL,
  `status` enum('Pending','Paid') DEFAULT 'Pending',
  `notes` text DEFAULT NULL,
  `payslip_path` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `services`
--

CREATE TABLE `services` (
  `service_id` int(11) NOT NULL,
  `service_name` varchar(255) NOT NULL,
  `service_price` decimal(10,2) NOT NULL,
  `service_description` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `admins`
--
ALTER TABLE `admins`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `username` (`username`);

--
-- Indexes for table `budgets`
--
ALTER TABLE `budgets`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `comments`
--
ALTER TABLE `comments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `patient_id` (`patient_id`);

--
-- Indexes for table `doctors`
--
ALTER TABLE `doctors`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `username` (`username`),
  ADD UNIQUE KEY `email` (`email`),
  ADD KEY `employee_id` (`employee_id`);

--
-- Indexes for table `doctor_patient`
--
ALTER TABLE `doctor_patient`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `doctor_id` (`doctor_id`,`patient_id`),
  ADD KEY `fk_doctor_patient_patient_id` (`patient_id`);

--
-- Indexes for table `employees`
--
ALTER TABLE `employees`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email` (`email`),
  ADD UNIQUE KEY `username` (`username`);

--
-- Indexes for table `health_records`
--
ALTER TABLE `health_records`
  ADD PRIMARY KEY (`id`),
  ADD KEY `patient_id` (`patient_id`);

--
-- Indexes for table `insurance_claims`
--
ALTER TABLE `insurance_claims`
  ADD PRIMARY KEY (`id`),
  ADD KEY `patient_id` (`patient_id`),
  ADD KEY `invoice_id` (`invoice_id`);

--
-- Indexes for table `invoices`
--
ALTER TABLE `invoices`
  ADD PRIMARY KEY (`id`),
  ADD KEY `patient_id` (`patient_id`);

--
-- Indexes for table `invoice_services`
--
ALTER TABLE `invoice_services`
  ADD PRIMARY KEY (`id`),
  ADD KEY `invoice_id` (`invoice_id`),
  ADD KEY `service_id` (`service_id`);

--
-- Indexes for table `patients`
--
ALTER TABLE `patients`
  ADD PRIMARY KEY (`id`),
  ADD KEY `doctor_id` (`doctor_id`);

--
-- Indexes for table `patient_logins`
--
ALTER TABLE `patient_logins`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `patient_id` (`patient_id`),
  ADD UNIQUE KEY `username` (`username`);

--
-- Indexes for table `payments`
--
ALTER TABLE `payments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `patient_id` (`patient_id`),
  ADD KEY `invoice_id` (`invoice_id`);

--
-- Indexes for table `prescriptions`
--
ALTER TABLE `prescriptions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `patient_id` (`patient_id`),
  ADD KEY `doctor_id` (`doctor_id`);

--
-- Indexes for table `salaries`
--
ALTER TABLE `salaries`
  ADD PRIMARY KEY (`id`),
  ADD KEY `employee_id` (`employee_id`);

--
-- Indexes for table `services`
--
ALTER TABLE `services`
  ADD PRIMARY KEY (`service_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `admins`
--
ALTER TABLE `admins`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `budgets`
--
ALTER TABLE `budgets`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `comments`
--
ALTER TABLE `comments`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `doctors`
--
ALTER TABLE `doctors`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `doctor_patient`
--
ALTER TABLE `doctor_patient`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `employees`
--
ALTER TABLE `employees`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `health_records`
--
ALTER TABLE `health_records`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `insurance_claims`
--
ALTER TABLE `insurance_claims`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `invoices`
--
ALTER TABLE `invoices`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `invoice_services`
--
ALTER TABLE `invoice_services`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `patients`
--
ALTER TABLE `patients`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `patient_logins`
--
ALTER TABLE `patient_logins`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `payments`
--
ALTER TABLE `payments`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `prescriptions`
--
ALTER TABLE `prescriptions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `salaries`
--
ALTER TABLE `salaries`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `services`
--
ALTER TABLE `services`
  MODIFY `service_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `comments`
--
ALTER TABLE `comments`
  ADD CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`);

--
-- Constraints for table `doctors`
--
ALTER TABLE `doctors`
  ADD CONSTRAINT `doctors_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`);

--
-- Constraints for table `doctor_patient`
--
ALTER TABLE `doctor_patient`
  ADD CONSTRAINT `doctor_patient_fk_doctor_id` FOREIGN KEY (`doctor_id`) REFERENCES `doctors` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `doctor_patient_ibfk_2` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_doctor` FOREIGN KEY (`doctor_id`) REFERENCES `doctors` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_doctor_patient_doctor_id` FOREIGN KEY (`doctor_id`) REFERENCES `doctors` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_doctor_patient_patient_id` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `health_records`
--
ALTER TABLE `health_records`
  ADD CONSTRAINT `health_records_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `insurance_claims`
--
ALTER TABLE `insurance_claims`
  ADD CONSTRAINT `insurance_claims_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `insurance_claims_ibfk_2` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `invoices`
--
ALTER TABLE `invoices`
  ADD CONSTRAINT `invoices_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`);

--
-- Constraints for table `invoice_services`
--
ALTER TABLE `invoice_services`
  ADD CONSTRAINT `invoice_services_ibfk_1` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `invoice_services_ibfk_2` FOREIGN KEY (`service_id`) REFERENCES `services` (`service_id`) ON DELETE CASCADE;

--
-- Constraints for table `patients`
--
ALTER TABLE `patients`
  ADD CONSTRAINT `patients_ibfk_1` FOREIGN KEY (`doctor_id`) REFERENCES `doctors` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

--
-- Constraints for table `patient_logins`
--
ALTER TABLE `patient_logins`
  ADD CONSTRAINT `patient_logins_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `payments`
--
ALTER TABLE `payments`
  ADD CONSTRAINT `payments_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `payments_ibfk_2` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `prescriptions`
--
ALTER TABLE `prescriptions`
  ADD CONSTRAINT `prescriptions_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `prescriptions_ibfk_2` FOREIGN KEY (`doctor_id`) REFERENCES `doctors` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `salaries`
--
ALTER TABLE `salaries`
  ADD CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;) so i want this codes like this admin/create_invoce.php (<?php
//invoice/create_invoice.php

include('../db_connect.php');


// Retrieve the list of services for the dropdown
$services = [];
$sql_services = "SELECT service_id, service_name, service_price FROM services";
$stmt_services = $pdo->prepare($sql_services);
$stmt_services->execute();
$services = $stmt_services->fetchAll(PDO::FETCH_ASSOC);

// Fetch patient details via AJAX
if (isset($_GET['search_patient'])) {
    $search_query = '%' . $_GET['search_patient'] . '%';
    $search_id = null;
    if (is_numeric($_GET['search_patient'])) {
        $search_id = $_GET['search_patient'];
    }

    $sql_patient = "SELECT id, name, phone, email, date_of_birth, registration_date 
                    FROM patients 
                    WHERE name LIKE :search_query";
    if ($search_id !== null) {
        $sql_patient .= " OR id = :search_id";
    }
    $stmt_patient = $pdo->prepare($sql_patient);
    $stmt_patient->bindParam(':search_query', $search_query, PDO::PARAM_STR);
    if ($search_id !== null) {
        $stmt_patient->bindParam(':search_id', $search_id, PDO::PARAM_INT);
    }
    $stmt_patient->execute();
    $patients = $stmt_patient->fetchAll(PDO::FETCH_ASSOC);

    echo json_encode($patients);
    exit();
}

// Handle invoice submission
if (isset($_POST['submit_invoice'])) {
    if (!isset($_POST['patient_id']) || $_POST['patient_id'] == '') {
        $error = "Please select a patient.";
    } elseif (!isset($_POST['services']) || empty($_POST['services'])) {
        $error = "Please select at least one service.";
    } else {
        try {
            $pdo->beginTransaction();

            $patient_id = $_POST['patient_id'];
            $services_selected = $_POST['services'];
            $notes = $_POST['notes'];

            // Calculate subtotal
            $subtotal = 0;
            foreach ($services_selected as $service_id) {
                $sql_service = "SELECT service_name, service_price FROM services WHERE service_id = :service_id";
                $stmt_service = $pdo->prepare($sql_service);
                $stmt_service->bindParam(':service_id', $service_id, PDO::PARAM_INT);
                $stmt_service->execute();
                $service = $stmt_service->fetch(PDO::FETCH_ASSOC);

                if ($service) {
                    $subtotal += floatval($service['service_price']);
                }
            }

            // Get discount and tax from POST data
            $discount = isset($_POST['discount']) ? floatval($_POST['discount']) : 0; // Discount as amount
            $tax = isset($_POST['tax']) ? floatval($_POST['tax']) : 0; // Tax as percentage

            // Apply discount first
            $taxable_amount = $subtotal - $discount;
            if ($taxable_amount < 0) {
                $taxable_amount = 0; // Ensure it does not go negative
            }

            // Calculate tax amount
            $tax_amount = $taxable_amount * $tax / 100;

            // Calculate total amount
            $total = $taxable_amount + $tax_amount;

            // Insert invoice into invoices table
            $sql_invoice = "INSERT INTO invoices (patient_id, total_amount, invoice_date, notes, discount, tax)
                            VALUES (:patient_id, :total_amount, CURRENT_DATE, :notes, :discount, :tax)";
            $stmt_invoice = $pdo->prepare($sql_invoice);
            $stmt_invoice->bindParam(':patient_id', $patient_id, PDO::PARAM_INT);
            $stmt_invoice->bindParam(':total_amount', $total);
            $stmt_invoice->bindParam(':notes', $notes);
            $stmt_invoice->bindParam(':discount', $discount);
            $stmt_invoice->bindParam(':tax', $tax);
            $stmt_invoice->execute();

            $invoice_id = $pdo->lastInsertId();

            // Insert services into invoice_services table
            foreach ($services_selected as $service_id) {
                $sql_service = "SELECT service_name, service_price FROM services WHERE service_id = :service_id";
                $stmt_service = $pdo->prepare($sql_service);
                $stmt_service->bindParam(':service_id', $service_id, PDO::PARAM_INT);
                $stmt_service->execute();
                $service = $stmt_service->fetch(PDO::FETCH_ASSOC);

                if ($service) {
                    $sql_invoice_service = "INSERT INTO invoice_services (invoice_id, service_id, service_name, cost)
                                            VALUES (:invoice_id, :service_id, :service_name, :cost)";
                    $stmt_invoice_service = $pdo->prepare($sql_invoice_service);
                    $stmt_invoice_service->bindParam(':invoice_id', $invoice_id, PDO::PARAM_INT);
                    $stmt_invoice_service->bindParam(':service_id', $service_id, PDO::PARAM_INT);
                    $stmt_invoice_service->bindParam(':service_name', $service['service_name']);
                    $stmt_invoice_service->bindParam(':cost', $service['service_price']);
                    $stmt_invoice_service->execute();
                }
            }

            $pdo->commit();

            header('Location: view_invoice.php?invoice_id=' . $invoice_id);
            exit();
        } catch (Exception $e) {
            $pdo->rollBack();
            $error = "Error creating invoice: " . $e->getMessage();
        }
    }
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <!-- Your head content -->
    <meta charset="UTF-8">
    <title>Create Invoice</title>
    <!-- Include your CSS styles here -->
    <style>
        body {
            background-color: #f4f4f4;
            font-family: 'Poppins', sans-serif;
            color: #333;
        }
        .container {
            max-width: 1000px;
            margin: 40px auto;
            padding: 40px;
            background-color: #fff;
            box-shadow: 0 0 20px rgba(0, 0, 0, 0.1);
            border-radius: 12px;
        }
        h1, h2 {
            color: #4F3F02;
        }
        .form-group {
            margin-bottom: 25px;
        }
        .form-control {
            width: 100%;
            padding: 12px;
            border: 1px solid #ddd;
            border-radius: 6px;
        }
        .btn {
            background-color: #D1C287;
            padding: 12px 24px;
            border: none;
            border-radius: 6px;
            cursor: pointer;
            font-size: 16px;
        }
        .invoice-summary {
            margin-top: 30px;
        }
        .total-row {
            display: flex;
            justify-content: space-between;
            padding: 10px 0;
            border-top: 1px solid #ddd;
            font-weight: 600;
        }
        .error {
            color: #ff0033;
        }
        /* Styles for service rows */
        .service-row {
            display: flex;
            align-items: center;
            margin-bottom: 10px;
        }
        .service-row select {
            flex: 1;
            margin-right: 10px;
        }
        .service-row .btn {
            background-color: #ff4d4d;
            color: #fff;
        }
        .service-row .btn:hover {
            background-color: #ff1a1a;
        }
    </style>
</head>
<body>
    <div class="container">
        <h1>Create Invoice</h1>
        <?php if (isset($error)): ?>
            <div class="error"><?= $error ?></div>
        <?php endif; ?>
        <form method="POST" action="create_invoice.php" id="invoiceForm">
            <div class="form-group">
                <label for="search_patient">Search Patient:</label>
                <input type="text" name="search_patient" id="search_patient" class="form-control" placeholder="Type patient name or ID" onkeyup="fetchPatients()">
                <div id="patient_results" style="background: #fff; border: 1px solid #ddd; position: absolute; z-index: 1000; width: 100%; display: none;"></div>
            </div>

            <div id="patient_details" style="display:none;">
                <h2>Patient Details</h2>
                <p><strong>Name:</strong> <span id="patient_name"></span></p>
                <p><strong>Phone:</strong> <span id="patient_phone"></span></p>
                <p><strong>Email:</strong> <span id="patient_email"></span></p>
                <p><strong>Date of Birth:</strong> <span id="patient_dob"></span></p>
                <p><strong>Registration Date:</strong> <span id="patient_registration"></span></p>
                <input type="hidden" name="patient_id" id="patient_id">
            </div>

            <h2>Services</h2>
            <div id="services">
                <div class="service-row">
                    <select name="services[]" class="form-control" onchange="calculateSubtotal()">
                        <option value="">Select a Service</option>
                        <?php foreach ($services as $service): ?>
                            <option value="<?= $service['service_id'] ?>" data-price="<?= $service['service_price'] ?>">
                                <?= $service['service_name'] ?> - $<?= number_format($service['service_price'], 2) ?>
                            </option>
                        <?php endforeach; ?>
                    </select>
                    <button type="button" class="btn" onclick="removeServiceRow(this)">Remove</button>
                </div>
            </div>
            <button type="button" class="btn" onclick="addServiceRow()">Add Another Service</button>

            <div class="invoice-summary">
                <h3>Invoice Summary</h3>
                <div class="form-group">
                    <label for="subtotal">Subtotal:</label>
                    <input type="text" name="subtotal" id="subtotal" value="0.00" class="form-control" readonly>
                </div>
                <div class="form-group">
                    <label for="discount">Discount:</label>
                    <input type="text" name="discount" id="discount" value="0" class="form-control" oninput="calculateTotal()">
                </div>
                <div class="form-group">
                    <label for="tax">Tax (%):</label>
                    <input type="text" name="tax" id="tax" value="0" class="form-control" oninput="calculateTotal()">
                </div>
                <div class="form-group">
                    <label for="total">Total:</label>
                    <input type="text" name="total" id="total" value="0.00" class="form-control" readonly>
                </div>
            </div>

            <div class="form-group">
                <label for="notes">Notes:</label>
                <textarea name="notes" id="notes" class="form-control" placeholder="Add notes..."></textarea>
            </div>

            <button type="submit" name="submit_invoice" class="btn">Submit Invoice</button>
        </form>

        <a href="index.php" class="btn btn-primary m-5">Back to Patient Invoices</a>
    </div>

<script>
    function fetchPatients() {
        const query = document.getElementById('search_patient').value;
        const resultsDiv = document.getElementById('patient_results');

        if (query.length > 2) {
            const xhr = new XMLHttpRequest();
            xhr.open('GET', 'create_invoice.php?search_patient=' + encodeURIComponent(query), true);
            xhr.onreadystatechange = function () {
                if (this.readyState === 4 && this.status === 200) {
                    const patients = JSON.parse(this.responseText);
                    // Clear previous results
                    resultsDiv.innerHTML = '';

                    if (patients.length > 0) {
                        patients.forEach(patient => {
                            const patientDiv = document.createElement('div');
                            patientDiv.style.padding = '10px';
                            patientDiv.style.cursor = 'pointer';
                            patientDiv.innerHTML = `<strong>${patient.name}</strong><br>ID: ${patient.id}<br>
                                                    Phone: ${patient.phone}<br>Email: ${patient.email}<hr>`;
                            patientDiv.addEventListener('click', function() {
                                selectPatient(patient);
                            });
                            resultsDiv.appendChild(patientDiv);
                        });
                        resultsDiv.style.display = 'block';
                    } else {
                        resultsDiv.innerHTML = '<p>No patients found</p>';
                        resultsDiv.style.display = 'block';
                    }
                }
            };
            xhr.send();
        } else {
            resultsDiv.style.display = 'none';
        }
    }

    function selectPatient(patient) {
        document.getElementById('patient_id').value = patient.id;
        document.getElementById('patient_name').innerText = patient.name;
        document.getElementById('patient_phone').innerText = patient.phone;
        document.getElementById('patient_email').innerText = patient.email;
        document.getElementById('patient_dob').innerText = patient.date_of_birth;
        document.getElementById('patient_registration').innerText = patient.registration_date;

        document.getElementById('patient_details').style.display = 'block';
        document.getElementById('patient_results').style.display = 'none';
        document.getElementById('search_patient').value = '';
    }

    function addServiceRow() {
        const servicesDiv = document.getElementById('services');
        const newServiceRow = document.createElement('div');
        newServiceRow.classList.add('service-row');
        newServiceRow.innerHTML = `
            <select name="services[]" class="form-control" onchange="calculateSubtotal()">
                <option value="">Select a Service</option>
                <?php foreach ($services as $service): ?>
                    <option value="<?= $service['service_id'] ?>" data-price="<?= $service['service_price'] ?>">
                        <?= $service['service_name'] ?> - $<?= number_format($service['service_price'], 2) ?>
                    </option>
                <?php endforeach; ?>
            </select>
            <button type="button" class="btn" onclick="removeServiceRow(this)">Remove</button>
        `;
        servicesDiv.appendChild(newServiceRow);
    }

    function removeServiceRow(button) {
        button.parentElement.remove();
        calculateSubtotal();
    }

    function calculateSubtotal() {
        let subtotal = 0;
        document.querySelectorAll('select[name="services[]"]').forEach(select => {
            const selectedOption = select.options[select.selectedIndex];
            if (selectedOption && selectedOption.dataset.price) {
                subtotal += parseFloat(selectedOption.dataset.price);
            }
        });
        document.getElementById('subtotal').value = subtotal.toFixed(2);
        calculateTotal();
    }

    function calculateTotal() {
        const subtotal = parseFloat(document.getElementById('subtotal').value) || 0;
        const discount = parseFloat(document.getElementById('discount').value) || 0;
        const tax = parseFloat(document.getElementById('tax').value) || 0;

        // Apply discount first
        let taxable_amount = subtotal - discount;
        if (taxable_amount < 0) {
            taxable_amount = 0; // Ensure taxable amount is not negative
        }

        // Calculate tax on discounted subtotal
        const tax_amount = taxable_amount * tax / 100;

        // Calculate total
        const total = taxable_amount + tax_amount;

        document.getElementById('total').value = total.toFixed(2);
    }
</script>
</body>
</html>) and type full code