based on this accountant/dasbaoard.php (<?php
// accountant/dashboard.php

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

// Disable error reporting in production
// ini_set('display_errors', 0);
// error_reporting(0);

// Enable error reporting during development (comment out in production)
ini_set('display_errors', 1);
error_reporting(E_ALL);

// Function to sanitize output
function sanitize_output($data) {
    return htmlspecialchars($data, ENT_QUOTES, 'UTF-8');
}

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

// Retrieve and validate date range from GET parameters
$start_date = isset($_GET['start_date']) ? $_GET['start_date'] : date('Y-m-d', strtotime('-30 days'));
$end_date = isset($_GET['end_date']) ? $_GET['end_date'] : date('Y-m-d');

// Validate date formats
function validate_date($date, $format = 'Y-m-d') {
    $d = DateTime::createFromFormat($format, $date);
    return $d && $d->format($format) === $date;
}

if (!validate_date($start_date) || !validate_date($end_date)) {
    // Invalid date format, set to default last 30 days
    $start_date = date('Y-m-d', strtotime('-30 days'));
    $end_date = date('Y-m-d');
}

// Ensure start_date is not after end_date
if ($start_date > $end_date) {
    // Swap dates
    $temp = $start_date;
    $start_date = $end_date;
    $end_date = $temp;
}

try {
    // Prepare statements with date filters

    // Total number of invoices
    $stmt = $pdo->prepare("SELECT COUNT(*) AS total_invoices FROM invoices WHERE invoice_date BETWEEN ? AND ?");
    $stmt->execute([$start_date, $end_date]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    $total_invoices = $row['total_invoices'];

    // Total revenue from invoices
    $stmt = $pdo->prepare("SELECT SUM(total_amount) AS total_revenue FROM invoices WHERE invoice_date BETWEEN ? AND ?");
    $stmt->execute([$start_date, $end_date]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    $total_revenue = $row['total_revenue'] ?? 0;

    // Average revenue per invoice
    $average_revenue = $total_invoices > 0 ? $total_revenue / $total_invoices : 0;

    // Total salaries paid (sum of net_salary where status is 'Paid')
    $stmt = $pdo->prepare("SELECT SUM(net_salary) AS total_salaries FROM salaries WHERE payment_date BETWEEN ? AND ? AND status = 'Paid'");
    $stmt->execute([$start_date, $end_date]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    $total_salaries = $row['total_salaries'] ?? 0;

    // Total expenses (assuming salaries are the primary expenses)
    $total_expenses = $total_salaries;

    // Profit (Revenue - Expenses)
    $profit = $total_revenue - $total_expenses;

    // Outstanding payments (invoices not fully paid)
    // Improved logic to account for partial payments
    $stmt = $pdo->prepare("
        SELECT i.id, i.total_amount, IFNULL(SUM(p.amount_paid), 0) AS total_paid
        FROM invoices i
        LEFT JOIN payments p ON i.id = p.invoice_id
        WHERE i.invoice_date BETWEEN ? AND ?
        GROUP BY i.id
        HAVING total_paid < i.total_amount
    ");
    $stmt->execute([$start_date, $end_date]);
    $outstanding_rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $outstanding_payments = 0;
    foreach ($outstanding_rows as $out) {
        $outstanding_payments += ($out['total_amount'] - $out['total_paid']);
    }

    // Total payments received
    $stmt = $pdo->prepare("SELECT SUM(amount_paid) AS total_payments FROM payments WHERE payment_date BETWEEN ? AND ?");
    $stmt->execute([$start_date, $end_date]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    $total_payments = $row['total_payments'] ?? 0;

    // Fetch data for charts
    // Revenue over time (grouped by date)
    $stmt = $pdo->prepare("SELECT invoice_date, SUM(total_amount) AS daily_revenue FROM invoices WHERE invoice_date BETWEEN ? AND ? GROUP BY invoice_date ORDER BY invoice_date ASC");
    $stmt->execute([$start_date, $end_date]);
    $revenue_data = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Expenses over time (grouped by date)
    $stmt = $pdo->prepare("SELECT payment_date, SUM(net_salary) AS daily_expenses FROM salaries WHERE payment_date BETWEEN ? AND ? AND status = 'Paid' GROUP BY payment_date ORDER BY payment_date ASC");
    $stmt->execute([$start_date, $end_date]);
    $expenses_data = $stmt->fetchAll(PDO::FETCH_ASSOC);

} catch (PDOException $e) {
    // Log the error to a file (for production)
    // error_log($e->getMessage(), 3, '../logs/error.log');
    
    // Display a user-friendly message
    echo "An error occurred while fetching dashboard data. Please try again later.";
    exit();
}

// Prepare data for charts
$dates = [];
$revenues = [];
$expenses = [];
$start = new DateTime($start_date);
$end = new DateTime($end_date);
$end->modify('+1 day');
$period = new DatePeriod($start, new DateInterval('P1D'), $end);

foreach ($period as $date) {
    $current_date = $date->format('Y-m-d');
    $dates[] = $current_date;
    $revenues[$current_date] = 0; // Initialize with 0
    $expenses[$current_date] = 0; // Initialize with 0
}

// Map revenue data
foreach ($revenue_data as $data) {
    $invoice_date = $data['invoice_date'];
    if (isset($revenues[$invoice_date])) {
        $revenues[$invoice_date] = $data['daily_revenue'];
    }
}

// Map expenses data
foreach ($expenses_data as $data) {
    $payment_date = $data['payment_date'];
    if (isset($expenses[$payment_date])) {
        $expenses[$payment_date] = $data['daily_expenses'];
    }
}

// Prepare data arrays for charts
$chart_dates = array_values($dates);
$chart_revenues = array_values($revenues);
$chart_expenses = array_values($expenses);
?>
<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>Accountant Dashboard</title>
    <!-- Meta tags for responsiveness -->
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <!-- Bootstrap CSS -->
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <!-- Chart.js Library -->
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <!-- Custom Styles -->
    <style>
        /* Import Google Fonts */
        @import url('https://fonts.googleapis.com/css2?family=Poppins:wght@400;600;700&display=swap');

        /* Root Variables */
        :root {
            --primary-color: #4F3F02;
            --secondary-color: #D1C287;
            --background-color: #F8F9FA;
            --card-background: #FFFFFF;
            --text-color: #333333;
            --success-color: #28a745;
            --danger-color: #dc3545;
            --info-color: #17a2b8;
            --font-family: 'Poppins', sans-serif;
        }

        /* Reset and Base Styles */
        * {
            margin: 0;
            padding: 0;
            box-sizing: border-box;
        }

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

        /* Header */
        .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;
        }

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

        /* Cards */
        .card {
            border: none;
            border-radius: 15px;
            box-shadow: 0 6px 10px rgba(0, 0, 0, 0.1);
            margin-bottom: 30px;
            background-color: var(--card-background);
        }

        .card .card-body {
            padding: 25px;
        }

        .card .card-title {
            color: var(--primary-color);
            font-weight: 600;
            margin-bottom: 15px;
        }

        .card .card-text {
            font-size: 2em;
            font-weight: 700;
        }

        /* Buttons */
        .btn-custom {
            background-color: var(--secondary-color);
            border-color: var(--secondary-color);
            color: var(--primary-color);
            font-weight: 600;
            width: 100%;
            padding: 15px;
            border-radius: 10px;
            transition: all 0.3s ease;
        }

        .btn-custom:hover {
            background-color: var(--primary-color);
            color: #fff;
            border-color: var(--secondary-color);
            transform: translateY(-3px);
            box-shadow: 0 8px 20px rgba(0, 0, 0, 0.15);
        }

        /* Filters */
        .filters {
            background-color: var(--card-background);
            padding: 25px;
            border-radius: 15px;
            box-shadow: 0 6px 10px rgba(0, 0, 0, 0.1);
            margin-bottom: 40px;
        }

        /* Charts */
        .chart-container {
            position: relative;
            height: 400px;
        }

        /* Responsive Adjustments */
        @media (max-width: 768px) {
            .dashboard-header h1 {
                font-size: 24px;
            }

            .btn-custom {
                margin-bottom: 15px;
            }
        }

        @media (max-width: 576px) {
            .dashboard-header h1 {
                font-size: 20px;
            }

            .card .card-body {
                padding: 20px;
            }

            .card .card-text {
                font-size: 1.5em;
            }

            .filters {
                padding: 20px;
            }
        }

        /* Dark Mode Styles */
        body.dark-mode {
            background-color: #121212;
            color: #e0e0e0;
        }

        body.dark-mode .dashboard-header {
            background-color: #1f1f1f;
            color: #e0e0e0;
        }

        body.dark-mode .card {
            background-color: #1f1f1f;
            box-shadow: 0 6px 10px rgba(255, 255, 255, 0.05);
        }

        body.dark-mode .btn-custom {
            background-color: #333;
            border-color: #333;
            color: #fff;
        }

        body.dark-mode .btn-custom:hover {
            background-color: #555;
            border-color: #555;
            color: #fff;
        }

        body.dark-mode .filters {
            background-color: #1f1f1f;
            box-shadow: 0 6px 10px rgba(255, 255, 255, 0.05);
        }

        body.dark-mode .card-title {
            color: #d1c287;
        }
    </style>
</head>
<body>
    <div class="dashboard-header">
        <h1>Welcome, <?php echo sanitize_output($_SESSION['accountant_username']); ?></h1>
        <p>
            <a href="accountant_profile.php">Profile</a> | <a href="logout.php">Logout</a>
        </p>
    </div>

    <div class="container">

        <!-- Date Range Filters -->
        <div class="filters">
            <form method="GET" action="" class="row g-3">
                <div class="col-md-5">
                    <label for="start_date" class="form-label">Start Date:</label>
                    <input type="date" id="start_date" name="start_date" class="form-control" value="<?php echo sanitize_output($start_date); ?>" required>
                </div>
                <div class="col-md-5">
                    <label for="end_date" class="form-label">End Date:</label>
                    <input type="date" id="end_date" name="end_date" class="form-control" value="<?php echo sanitize_output($end_date); ?>" required>
                </div>
                <div class="col-md-2 d-flex align-items-end">
                    <button type="submit" class="btn btn-custom">Apply Filters</button>
                </div>
            </form>
        </div>

        <!-- Financial Metrics -->
        <div class="row">
            <!-- Total Invoices -->
            <div class="col-lg-3 col-md-6">
                <div class="card text-center">
                    <div class="card-body">
                        <h5 class="card-title">Total Invoices</h5>
                        <p class="card-text"><?php echo number_format($total_invoices); ?></p>
                    </div>
                </div>
            </div>
            <!-- Total Revenue -->
            <div class="col-lg-3 col-md-6">
                <div class="card text-center">
                    <div class="card-body">
                        <h5 class="card-title">Total Revenue</h5>
                        <p class="card-text">$<?php echo number_format($total_revenue, 2); ?></p>
                    </div>
                </div>
            </div>
            <!-- Total Payments Received -->
            <div class="col-lg-3 col-md-6">
                <div class="card text-center">
                    <div class="card-body">
                        <h5 class="card-title">Total Payments Received</h5>
                        <p class="card-text">$<?php echo number_format($total_payments, 2); ?></p>
                    </div>
                </div>
            </div>
            <!-- Total Expenses -->
            <div class="col-lg-3 col-md-6">
                <div class="card text-center">
                    <div class="card-body">
                        <h5 class="card-title">Total Expenses</h5>
                        <p class="card-text">$<?php echo number_format($total_expenses, 2); ?></p>
                    </div>
                </div>
            </div>
            <!-- Profit -->
            <div class="col-lg-4 col-md-6">
                <div class="card text-center">
                    <div class="card-body">
                        <h5 class="card-title">Profit</h5>
                        <p class="card-text" style="color: <?php echo $profit >= 0 ? 'var(--success-color)' : 'var(--danger-color)'; ?>">
                            $<?php echo number_format($profit, 2); ?>
                        </p>
                    </div>
                </div>
            </div>
            <!-- Average Revenue per Invoice -->
            <div class="col-lg-4 col-md-6">
                <div class="card text-center">
                    <div class="card-body">
                        <h5 class="card-title">Avg. Revenue per Invoice</h5>
                        <p class="card-text">$<?php echo number_format($average_revenue, 2); ?></p>
                    </div>
                </div>
            </div>
            <!-- Outstanding Payments -->
            <div class="col-lg-4 col-md-6">
                <div class="card text-center">
                    <div class="card-body">
                        <h5 class="card-title">Outstanding Payments</h5>
                        <p class="card-text" style="color: var(--danger-color);">
                            $<?php echo number_format($outstanding_payments, 2); ?>
                        </p>
                    </div>
                </div>
            </div>
        </div>

        <!-- Charts -->
        <div class="row">
            <!-- Revenue vs. Expenses Chart -->
            <div class="col-md-12">
                <div class="card">
                    <div class="card-body">
                        <h5 class="card-title">Revenue vs. Expenses Over Time</h5>
                        <div class="chart-container">
                            <canvas id="revenueExpensesChart"></canvas>
                        </div>
                    </div>
                </div>
            </div>
        </div>

        <!-- Actions -->
        <h3 class="mt-5 mb-4" style="color: var(--primary-color); font-weight: 700;">Actions</h3>
        <div class="row">
            <div class="col-md-3 mb-3">
                <a href="manage_invoices.php" class="btn btn-custom">Manage Invoices</a>
            </div>
            <div class="col-md-3 mb-3">
                <a href="manage_payments.php" class="btn btn-custom">Manage Payments</a>
            </div>
            <div class="col-md-3 mb-3">
                <a href="manage_salaries.php" class="btn btn-custom">Manage Salaries</a>
            </div>
            <div class="col-md-3 mb-3">
                <a href="generate_report.php" class="btn btn-custom">Generate Reports</a>
            </div>
        </div>

    </div>

    <!-- Bootstrap JS and dependencies (optional but recommended) -->
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <!-- Popper.js (for Bootstrap 5) -->
    <script src="https://cdn.jsdelivr.net/npm/@popperjs/core@2.9.3/dist/umd/popper.min.js"></script>
    <!-- Bootstrap JS -->
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>

    <!-- Chart.js Script -->
    <script>
        (function() {
            const ctx = document.getElementById('revenueExpensesChart').getContext('2d');
            const revenueExpensesChart = new Chart(ctx, {
                type: 'line',
                data: {
                    labels: <?php echo json_encode($chart_dates); ?>,
                    datasets: [
                        {
                            label: 'Revenue',
                            data: <?php echo json_encode(array_values($chart_revenues)); ?>,
                            borderColor: 'rgba(40, 167, 69, 1)', // Bootstrap success color
                            backgroundColor: 'rgba(40, 167, 69, 0.2)',
                            fill: true,
                            tension: 0.3,
                            pointRadius: 0,
                        },
                        {
                            label: 'Expenses',
                            data: <?php echo json_encode(array_values($chart_expenses)); ?>,
                            borderColor: 'rgba(220, 53, 69, 1)', // Bootstrap danger color
                            backgroundColor: 'rgba(220, 53, 69, 0.2)',
                            fill: true,
                            tension: 0.3,
                            pointRadius: 0,
                        }
                    ]
                },
                options: {
                    responsive: true,
                    maintainAspectRatio: false,
                    interaction: {
                        mode: 'index',
                        intersect: false
                    },
                    plugins: {
                        legend: {
                            labels: {
                                font: {
                                    family: 'Poppins',
                                    size: 14,
                                    weight: '600'
                                }
                            }
                        },
                        tooltip: {
                            enabled: true,
                            callbacks: {
                                label: function(context) {
                                    let label = context.dataset.label || '';
                                    if (label) {
                                        label += ': ';
                                    }
                                    if (context.parsed.y !== null) {
                                        label += '$' + context.parsed.y.toLocaleString();
                                    }
                                    return label;
                                }
                            }
                        }
                    },
                    scales: {
                        x: {
                            title: {
                                display: true,
                                text: 'Date',
                                font: {
                                    family: 'Poppins',
                                    size: 14,
                                    weight: '600'
                                }
                            },
                            ticks: {
                                autoSkip: true,
                                maxTicksLimit: 20,
                                font: {
                                    family: 'Poppins',
                                }
                            },
                            grid: {
                                display: false
                            }
                        },
                        y: {
                            title: {
                                display: true,
                                text: 'Amount ($)',
                                font: {
                                    family: 'Poppins',
                                    size: 14,
                                    weight: '600'
                                }
                            },
                            ticks: {
                                beginAtZero: true,
                                font: {
                                    family: 'Poppins',
                                },
                                callback: function(value) {
                                    return '$' + value.toLocaleString();
                                }
                            },
                            grid: {
                                color: '#e9ecef'
                            }
                        }
                    }
                }
            });
        })();
    </script>

    <!-- Dark Mode Toggle Script -->
    <script>
        document.addEventListener('DOMContentLoaded', function() {
            const darkModeToggle = document.getElementById('toggleDarkMode');

            // Toggle dark mode on button click
            darkModeToggle.addEventListener('click', function() {
                document.body.classList.toggle('dark-mode');
                // Save preference in localStorage
                if(document.body.classList.contains('dark-mode')) {
                    localStorage.setItem('theme', 'dark');
                    this.innerHTML = '<i class="fas fa-sun"></i>';
                } else {
                    localStorage.setItem('theme', 'light');
                    this.innerHTML = '<i class="fas fa-moon"></i>';
                }
            });

            // Load theme preference on page load
            const savedTheme = localStorage.getItem('theme');
            if(savedTheme === 'dark') {
                document.body.classList.add('dark-mode');
                darkModeToggle.innerHTML = '<i class="fas fa-sun"></i>';
            } else {
                darkModeToggle.innerHTML = '<i class="fas fa-moon"></i>';
            }
        });
    </script>
</body>
</html>), i created budget_management.php (<?php
// accountant/budget_management.php

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

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

// Function to sanitize output
function sanitize_output($data) {
    return htmlspecialchars($data, ENT_QUOTES, 'UTF-8');
}

// Handle form submission for adding a new budget
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $month = $_POST['month'];
    $budgeted_revenue = $_POST['budgeted_revenue'];
    $budgeted_expenses = $_POST['budgeted_expenses'];
    $notes = $_POST['notes'];

    try {
        $stmt = $pdo->prepare("INSERT INTO budgets (month, budgeted_revenue, budgeted_expenses, notes) VALUES (?, ?, ?, ?)");
        $stmt->execute([$month, $budgeted_revenue, $budgeted_expenses, $notes]);
        echo "Budget added successfully.";
    } catch (PDOException $e) {
        echo "An error occurred: " . sanitize_output($e->getMessage());
        exit();
    }
}

// Fetch budgets
try {
    $stmt = $pdo->prepare("SELECT * FROM budgets ORDER BY month DESC");
    $stmt->execute();
    $budgets = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Fetch actual revenues and expenses for variance analysis
    foreach ($budgets as &$budget) {
        $month_start = $budget['month'] . '-01';
        $month_end = date('Y-m-t', strtotime($month_start));

        // Actual revenue
        $stmt = $pdo->prepare("
            SELECT SUM(total_amount) AS actual_revenue
            FROM invoices
            WHERE invoice_date BETWEEN ? AND ?
        ");
        $stmt->execute([$month_start, $month_end]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        $budget['actual_revenue'] = $row['actual_revenue'] ?? 0;

        // Actual expenses
        $stmt = $pdo->prepare("
            SELECT SUM(net_salary) AS actual_expenses
            FROM salaries
            WHERE payment_date BETWEEN ? AND ?
            AND status = 'Paid'
        ");
        $stmt->execute([$month_start, $month_end]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        $budget['actual_expenses'] = $row['actual_expenses'] ?? 0;

        // Variance calculation
        $budget['revenue_variance'] = $budget['actual_revenue'] - $budget['budgeted_revenue'];
        $budget['expenses_variance'] = $budget['actual_expenses'] - $budget['budgeted_expenses'];
    }

} catch (PDOException $e) {
    echo "An error occurred: " . sanitize_output($e->getMessage());
    exit();
}
?>

<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>Budget Management</title>
</head>
<body>
    <h1>Budget Management</h1>
    <p>
        <a href="dashboard.php">Back to Dashboard</a> | <a href="logout.php">Logout</a>
    </p>

    <h2>Add New Budget</h2>
    <form method="POST" action="">
        <label for="month">Month (YYYY-MM):</label><br>
        <input type="text" id="month" name="month" required><br><br>

        <label for="budgeted_revenue">Budgeted Revenue:</label><br>
        <input type="number" step="0.01" id="budgeted_revenue" name="budgeted_revenue" required><br><br>

        <label for="budgeted_expenses">Budgeted Expenses:</label><br>
        <input type="number" step="0.01" id="budgeted_expenses" name="budgeted_expenses" required><br><br>

        <label for="notes">Notes:</label><br>
        <textarea id="notes" name="notes"></textarea><br><br>

        <input type="submit" value="Add Budget">
    </form>

    <h2>Budget Variance Analysis</h2>
    <table border="1" cellpadding="5">
        <tr>
            <th>Month</th>
            <th>Budgeted Revenue</th>
            <th>Actual Revenue</th>
            <th>Revenue Variance</th>
            <th>Budgeted Expenses</th>
            <th>Actual Expenses</th>
            <th>Expenses Variance</th>
            <th>Notes</th>
        </tr>
        <?php foreach ($budgets as $budget): ?>
        <tr>
            <td><?php echo sanitize_output($budget['month']); ?></td>
            <td>$<?php echo number_format($budget['budgeted_revenue'], 2); ?></td>
            <td>$<?php echo number_format($budget['actual_revenue'], 2); ?></td>
            <td>$<?php echo number_format($budget['revenue_variance'], 2); ?></td>
            <td>$<?php echo number_format($budget['budgeted_expenses'], 2); ?></td>
            <td>$<?php echo number_format($budget['actual_expenses'], 2); ?></td>
            <td>$<?php echo number_format($budget['expenses_variance'], 2); ?></td>
            <td><?php echo sanitize_output($budget['notes']); ?></td>
        </tr>
        <?php endforeach; ?>
    </table>
</body>
</html>), so i need to change the dasboard.php to make suitable with the budget and change the budget if you need to mkae better and this info of database (Updated Database Structure
The database for the admin dashboard and patient management system consists of the following tables:
a. Patients
id (INT, AUTO_INCREMENT, PRIMARY KEY): Unique identifier for each patient.
name (VARCHAR): Full name of the patient.
age (INT): Age of the patient.
gender (ENUM('Male', 'Female', 'Other')): Gender of the patient.
phone (VARCHAR): Phone number of the patient.
email (VARCHAR): Email address of the patient.
medical_history (TEXT): Medical history of the patient.
date_of_birth (DATE): Date of birth of the patient.
registration_date (DATE): Date when the patient was registered.
registration_datetime (DATETIME): Date and time when the patient was registered.
status (ENUM): Current status of the patient (e.g., 'Initial Consultation', 'Treatment in Progress').
b. Health Records
id (INT, AUTO_INCREMENT, PRIMARY KEY): Unique identifier for each health record.
patient_id (INT, FOREIGN KEY REFERENCES patients(id)): ID of the patient associated with this health record.
blood_pressure (VARCHAR): Blood pressure reading.
weight (DECIMAL): Weight of the patient.
bmi (DECIMAL): Body Mass Index of the patient.
cholesterol (DECIMAL): Cholesterol level of the patient.
prescription (TEXT): Prescriptions given to the patient.
treatment_progress (TEXT): Notes on the treatment progress.
record_date (DATE): Date of the health record.
c. Admins
id (INT, AUTO_INCREMENT, PRIMARY KEY): Unique identifier for each admin.
username (VARCHAR): Admin's username.
password (VARCHAR): Hashed password for the admin.
email (VARCHAR): Email address of the admin.
d. Patient Logins
id (INT, AUTO_INCREMENT, PRIMARY KEY): Unique identifier for each patient login.
patient_id (INT, FOREIGN KEY REFERENCES patients(id)): ID of the patient associated with this login.
username (VARCHAR): Username for patient login.
password (VARCHAR): Hashed password for patient login.
e. Invoices
id (INT, AUTO_INCREMENT, PRIMARY KEY): Unique identifier for each invoice.
patient_id (INT NOT NULL, FOREIGN KEY REFERENCES patients(id)): ID of the patient associated with this invoice.
service_name (VARCHAR(255) NOT NULL): Name of the service rendered.
service_description (TEXT): Description of the service.
cost (DECIMAL(10, 2) NOT NULL): Cost of the service.
tax_rate (DECIMAL(5, 2) DEFAULT 0): Tax rate applied to the service.
discount (DECIMAL(5, 2) DEFAULT 0): Discount applied to the service.
total_amount (DECIMAL(10, 2) NOT NULL): Total amount due after tax and discount.
invoice_date (DATE DEFAULT CURRENT_DATE): Date of the invoice.
notes (TEXT): Additional notes for the invoice.
f. Services
service_id (INT, AUTO_INCREMENT, PRIMARY KEY): Unique identifier for each service.
service_name (VARCHAR(255) NOT NULL): Name of the service.
service_price (DECIMAL(10, 2) NOT NULL): Price of the service.
service_description (TEXT): Description of the service.
created_at (TIMESTAMP DEFAULT CURRENT_TIMESTAMP): Timestamp when the service was created.
updated_at (TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP): Timestamp when the service was last updated.
g. Invoice Services
id (INT, AUTO_INCREMENT, PRIMARY KEY): Unique identifier for each entry in the invoice services table.
invoice_id (INT NOT NULL, FOREIGN KEY REFERENCES invoices(id)): ID of the associated invoice.
service_id (INT NOT NULL, FOREIGN KEY REFERENCES services(service_id)): ID of the associated service.
service_name (VARCHAR(255) NOT NULL): Name of the service rendered.
cost (DECIMAL(10, 2) NOT NULL): Cost of the service rendered.
h. Comments
id (INT, AUTO_INCREMENT, PRIMARY KEY): Unique identifier for each comment.
patient_id (INT NOT NULL, FOREIGN KEY REFERENCES patients(id)): ID of the patient associated with the comment.
sender (ENUM('patient', 'doctor', 'admin') NOT NULL): Sender of the comment, which can be 'patient', 'doctor', or 'admin'.
message (TEXT NOT NULL): The message or comment content.
timestamp (DATETIME DEFAULT CURRENT_TIMESTAMP): Date and time the comment was made.
parent_id (INT NULL DEFAULT NULL): ID of the parent comment (for threaded conversations), if applicable.
i. Doctor-Patient
id (INT, AUTO_INCREMENT, PRIMARY KEY): Unique identifier for each doctor-patient relationship.
doctor_id (INT NOT NULL, FOREIGN KEY REFERENCES doctors(id)): ID of the associated doctor.
patient_id (INT NOT NULL, FOREIGN KEY REFERENCES patients(id)): ID of the associated patient.
UNIQUE (doctor_id, patient_id): Ensures a unique doctor-patient pair.
j. Employees
id (INT, AUTO_INCREMENT, PRIMARY KEY): Unique identifier for each employee.
name (VARCHAR(255) NOT NULL): Full name of the employee.
role (ENUM('admin', 'doctor', 'staff') NOT NULL): Role of the employee, which can be 'admin', 'doctor', or 'staff'.
email (VARCHAR(255) NOT NULL UNIQUE): Email address of the employee, ensuring it is unique.
phone (VARCHAR(20)): Phone number of the employee.
department (VARCHAR(100)): Department where the employee works.
date_of_joining (DATE): The date when the employee joined the organization.
status (ENUM('active', 'inactive') DEFAULT 'active'): Employment status, either 'active' or 'inactive', with 'active' as the default.
username (VARCHAR(100) NOT NULL UNIQUE): Username for employee login, ensuring it is unique.
password (VARCHAR(255) NOT NULL): Hashed password for the employee.
created_at (TIMESTAMP DEFAULT CURRENT_TIMESTAMP): Timestamp when the employee record was created.
updated_at (TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP): Timestamp when the employee record was last updated.
profile_picture (VARCHAR(255) DEFAULT 'default_profile.png'): Profile picture of the employee.
specialization (VARCHAR(255) DEFAULT NULL): Specialization of the employee.
k. Doctors
id (INT, AUTO_INCREMENT, PRIMARY KEY): Unique identifier for each doctor.
employee_id (INT NOT NULL, FOREIGN KEY REFERENCES employees(id)): ID of the associated employee record.
department (VARCHAR(100) DEFAULT NULL): Department where the doctor works.
date_of_joining (DATE DEFAULT NULL): The date when the doctor joined the organization.
status (ENUM('active', 'inactive') DEFAULT 'active'): Employment status of the doctor.
profile_picture (VARCHAR(255) DEFAULT 'default_profile.png'): Profile picture of the doctor.
l. Payments
id (INT AUTO_INCREMENT, PRIMARY KEY): Unique identifier for each payment.
patient_id (INT NOT NULL, FOREIGN KEY REFERENCES patients(id)): ID of the patient associated with this payment.
invoice_id (INT NOT NULL, FOREIGN KEY REFERENCES invoices(id)): ID of the invoice associated with this payment.
amount_paid (DECIMAL(10,2) NOT NULL): Amount paid by the patient.
payment_date (DATE NOT NULL): Date of the payment.
payment_method (VARCHAR(50)): Method of payment (e.g., cash, card, insurance).
notes (TEXT): Additional notes regarding the payment.
m. Salaries
id (INT AUTO_INCREMENT, PRIMARY KEY): Unique identifier for each salary record.
employee_id (INT NOT NULL, FOREIGN KEY REFERENCES employees(id)): ID of the employee receiving the salary.
basic_salary (DECIMAL(10,2) NOT NULL): Basic salary of the employee.
bonuses (DECIMAL(10,2) DEFAULT 0): Bonuses added to the salary.
deductions (DECIMAL(10,2) DEFAULT 0): Deductions from the salary.
overtime_hours (INT DEFAULT 0): Number of overtime hours worked by the employee.
overtime_rate (DECIMAL(10,2) DEFAULT 0): Rate per overtime hour.
tax (DECIMAL(10,2) DEFAULT 0): Tax applied to the salary.
payment_date (DATE NOT NULL): Date of the salary payment.
net_salary (DECIMAL(10,2) NOT NULL): Net salary after adjustments.
status (ENUM('Pending', 'Paid') DEFAULT 'Pending'): Status of the salary payment.
notes (TEXT): Additional notes for the salary record.
 and this new tables (CREATE TABLE insurance_claims (
    id INT AUTO_INCREMENT PRIMARY KEY,                -- Unique ID for each claim
    patient_id INT,                                   -- References the patient making the claim
    invoice_id INT,                                   -- References the associated invoice
    claim_amount DECIMAL(10, 2),                      -- Amount of the insurance claim
    status ENUM('Pending', 'Approved', 'Rejected'),   -- Status of the claim
    submission_date DATE,                             -- Date when the claim was submitted
    processing_date DATE,                             -- Date when the claim was processed
    notes TEXT,                                       -- Additional notes or comments on the claim

    -- Foreign key constraints
    FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE,  -- Links to the patients table
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE   -- Links to the invoices table
);



CREATE TABLE budgets (
    id INT AUTO_INCREMENT PRIMARY KEY,               -- Unique ID for each budget entry
    month VARCHAR(7),                                -- The month for the budget in 'YYYY-MM' format
    budgeted_revenue DECIMAL(10, 2),                 -- The projected revenue for the month
    budgeted_expenses DECIMAL(10, 2),                -- The projected expenses for the month
    notes TEXT                                       -- Additional notes or comments regarding the budget
);
) ) so type full codes for update two files budget_management.php and dashboard.php