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.
SQL Update for Doctor-Patient Table
The SQL statement ALTER TABLE doctor_patient ADD CONSTRAINT fk_doctor FOREIGN KEY (doctor_id) REFERENCES doctors(id) ON DELETE CASCADE ON UPDATE CASCADE; ensures that any deletion or update of a doctor in the doctors table will automatically cascade to the doctor_patient table, maintaining referential integrity.
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.

CREATE TABLE IF NOT EXISTS salaries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    basic_salary DECIMAL(10,2) NOT NULL,
    bonuses DECIMAL(10,2) DEFAULT 0,
    deductions DECIMAL(10,2) DEFAULT 0,
    overtime_hours INT DEFAULT 0,
    overtime_rate DECIMAL(10,2) DEFAULT 0,
    tax DECIMAL(10,2) DEFAULT 0,
    payment_date DATE NOT NULL,
    net_salary DECIMAL(10,2) NOT NULL,
    status ENUM('Pending', 'Paid') DEFAULT 'Pending',
    notes TEXT,
    payslip_path VARCHAR(255),
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE ON UPDATE CASCADE
);

ALTER TABLE salaries ADD COLUMN basic_salary DECIMAL(10,2) NOT NULL AFTER employee_id;


SQL Updates for Doctors Table
The SQL statement ALTER TABLE doctors ADD COLUMN date_of_joining DATE DEFAULT NULL; adds a date_of_joining column to the doctors table, allowing the storage of the date when a doctor joined the organization.
The SQL statement ALTER TABLE doctors ADD COLUMN department VARCHAR(100) DEFAULT NULL; adds a department column to the doctors table, enabling the specification of the department where the doctor works.

) and this manage_salaries.php (<?php
// accountant/manage_salaries.php

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

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

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

// Generate CSRF token if not set
if (empty($_SESSION['csrf_token'])) {
    $_SESSION['csrf_token'] = bin2hex(random_bytes(32));
}

// Initialize feedback messages
$success = isset($_SESSION['success']) ? $_SESSION['success'] : '';
$error = isset($_SESSION['error']) ? $_SESSION['error'] : '';
unset($_SESSION['success'], $_SESSION['error']);

// Fetch all salaries with employee details
try {
    $stmt = $pdo->prepare("
        SELECT salaries.*, employees.name AS employee_name
        FROM salaries
        JOIN employees ON salaries.employee_id = employees.id
        ORDER BY payment_date DESC
    ");
    $stmt->execute();
    $salaries = $stmt->fetchAll();
} catch (PDOException $e) {
    die("Error fetching salaries: " . sanitize_output($e->getMessage()));
}

// Fetch all active employees for the 'Add Salary' form
try {
    $empStmt = $pdo->prepare("SELECT id, name FROM employees WHERE status = 'active' ORDER BY name ASC");
    $empStmt->execute();
    $employees = $empStmt->fetchAll();
} catch (PDOException $e) {
    die("Error fetching employees: " . sanitize_output($e->getMessage()));
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Manage Salaries</title>
    <!-- Meta tags for responsiveness -->
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <!-- Bootstrap CSS, DataTables CSS, Font Awesome -->
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <link href="https://cdn.datatables.net/1.13.5/css/dataTables.bootstrap5.min.css" rel="stylesheet">
    <link href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css" rel="stylesheet">
    <!-- Custom Styles -->
    <link href="../assets/css/styles.css" rel="stylesheet">
</head>
<body class="light-mode">
    <!-- Dashboard Header -->
    <header class="navbar navbar-expand-lg navbar-light bg-primary text-white px-4">
        <a class="navbar-brand text-white" href="#">Salary Management</a>
        <button class="navbar-toggler" type="button" data-bs-toggle="collapse" data-bs-target="#navbarSupportedContent" 
            aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation">
            <span class="navbar-toggler-icon"></span>
        </button>
        <div class="collapse navbar-collapse justify-content-end" id="navbarSupportedContent">
            <div class="navbar-nav">
                <span class="nav-link">Welcome, <?php echo sanitize_output($_SESSION['accountant_name']); ?></span>
                <a class="nav-link" href="manage_salaries.php">Manage Salaries</a>
                <a class="nav-link" href="../auth/logout.php">Logout</a>
                <!-- Dark Mode Toggle -->
                <button class="btn btn-secondary ms-3" id="toggleDarkMode"><i class="fas fa-moon"></i></button>
            </div>
        </div>
    </header>

    <!-- Main Container -->
    <div class="container my-4">
        <!-- Feedback Messages -->
        <?php if (!empty($success)): ?>
            <div class="alert alert-success alert-dismissible fade show" role="alert">
                <?php echo sanitize_output($success); ?>
                <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
            </div>
        <?php endif; ?>

        <?php if (!empty($error)): ?>
            <div class="alert alert-danger alert-dismissible fade show" role="alert">
                <?php echo sanitize_output($error); ?>
                <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
            </div>
        <?php endif; ?>

        <!-- Add Salary Button -->
        <button type="button" class="btn btn-success mb-3" data-bs-toggle="modal" data-bs-target="#addSalaryModal">
            <i class="fas fa-plus"></i> Add Salary
        </button>

        <!-- Salaries Table -->
        <div class="table-responsive">
            <table class="table table-striped table-hover" id="salariesTable">
                <thead>
                    <tr>
                        <th>Salary ID</th>
                        <th>Employee Name</th>
                        <th>Basic Salary ($)</th>
                        <th>Bonuses ($)</th>
                        <th>Deductions ($)</th>
                        <th>Overtime (Hours)</th>
                        <th>Overtime Rate ($)</th>
                        <th>Tax ($)</th>
                        <th>Net Salary ($)</th>
                        <th>Payment Date</th>
                        <th>Status</th>
                        <th>Actions</th>
                    </tr>
                </thead>
                <tbody>
                    <?php if (count($salaries) > 0): ?>
                        <?php foreach ($salaries as $salary): ?>
                            <tr>
                                <td><?php echo sanitize_output($salary['id']); ?></td>
                                <td><?php echo sanitize_output($salary['employee_name']); ?></td>
                                <td><?php echo number_format($salary['basic_salary'], 2); ?></td>
                                <td><?php echo number_format($salary['bonuses'], 2); ?></td>
                                <td><?php echo number_format($salary['deductions'], 2); ?></td>
                                <td><?php echo sanitize_output($salary['overtime_hours']); ?></td>
                                <td><?php echo number_format($salary['overtime_rate'], 2); ?></td>
                                <td><?php echo number_format($salary['tax'], 2); ?></td>
                                <td><?php echo number_format($salary['net_salary'], 2); ?></td>
                                <td><?php echo sanitize_output($salary['payment_date']); ?></td>
                                <td>
                                    <?php if ($salary['status'] === 'Paid'): ?>
                                        <span class="badge bg-success">Paid</span>
                                    <?php else: ?>
                                        <span class="badge bg-warning text-dark">Pending</span>
                                    <?php endif; ?>
                                </td>
                                <td>
                                    <a href="edit_salary.php?id=<?php echo urlencode($salary['id']); ?>" class="btn btn-sm btn-primary" title="Edit">
                                        <i class="fas fa-edit"></i>
                                    </a>
                                    <form action="delete_salary.php" method="POST" style="display:inline-block;">
                                        <input type="hidden" name="salary_id" value="<?php echo sanitize_output($salary['id']); ?>">
                                        <input type="hidden" name="csrf_token" value="<?php echo sanitize_output($_SESSION['csrf_token']); ?>">
                                        <button type="submit" name="delete_salary" class="btn btn-sm btn-danger" 
                                            onclick="return confirm('Are you sure you want to delete this salary record?');" 
                                            title="Delete">
                                            <i class="fas fa-trash-alt"></i>
                                        </button>
                                    </form>
                                    <?php if ($salary['status'] === 'Pending'): ?>
                                        <form action="mark_paid.php" method="POST" style="display:inline-block;">
                                            <input type="hidden" name="salary_id" value="<?php echo sanitize_output($salary['id']); ?>">
                                            <input type="hidden" name="csrf_token" value="<?php echo sanitize_output($_SESSION['csrf_token']); ?>">
                                            <button type="submit" name="mark_paid" class="btn btn-sm btn-success" 
                                                onclick="return confirm('Mark this salary as paid?');" 
                                                title="Mark as Paid">
                                                <i class="fas fa-dollar-sign"></i>
                                            </button>
                                        </form>
                                    <?php endif; ?>
                                    <a href="generate_payslip.php?id=<?php echo urlencode($salary['id']); ?>" class="btn btn-sm btn-info" title="Generate Payslip">
                                        <i class="fas fa-file-pdf"></i>
                                    </a>
                                </td>
                            </tr>
                        <?php endforeach; ?>
                    <?php else: ?>
                        <tr>
                            <td colspan="12" class="text-center">No salary records found.</td>
                        </tr>
                    <?php endif; ?>
                </tbody>
            </table>
        </div>
    </div>

    <!-- Add Salary Modal -->
    <div class="modal fade" id="addSalaryModal" tabindex="-1" aria-labelledby="addSalaryModalLabel" aria-hidden="true">
        <div class="modal-dialog">
            <div class="modal-content">
                <form action="add_salary.php" method="POST">
                    <div class="modal-header">
                        <h5 class="modal-title" id="addSalaryModalLabel">Add New Salary</h5>
                        <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
                    </div>
                    <div class="modal-body">
                        <!-- CSRF Token -->
                        <input type="hidden" name="csrf_token" value="<?php echo sanitize_output($_SESSION['csrf_token']); ?>">

                        <!-- Employee Selection -->
                        <div class="mb-3">
                            <label for="employee_id" class="form-label">Employee</label>
                            <select class="form-select" id="employee_id" name="employee_id" required>
                                <option value="" selected disabled>Select an employee</option>
                                <?php foreach ($employees as $employee): ?>
                                    <option value="<?php echo sanitize_output($employee['id']); ?>">
                                        <?php echo sanitize_output($employee['name']); ?>
                                    </option>
                                <?php endforeach; ?>
                            </select>
                        </div>

                        <!-- Basic Salary -->
                        <div class="mb-3">
                            <label for="basic_salary" class="form-label">Basic Salary ($)</label>
                            <input type="number" step="0.01" class="form-control" id="basic_salary" name="basic_salary" placeholder="Enter basic salary" required>
                        </div>

                        <!-- Bonuses -->
                        <div class="mb-3">
                            <label for="bonuses" class="form-label">Bonuses ($)</label>
                            <input type="number" step="0.01" class="form-control" id="bonuses" name="bonuses" placeholder="Enter bonuses" value="0">
                        </div>

                        <!-- Deductions -->
                        <div class="mb-3">
                            <label for="deductions" class="form-label">Deductions ($)</label>
                            <input type="number" step="0.01" class="form-control" id="deductions" name="deductions" placeholder="Enter deductions" value="0">
                        </div>

                        <!-- Overtime Hours -->
                        <div class="mb-3">
                            <label for="overtime_hours" class="form-label">Overtime Hours</label>
                            <input type="number" class="form-control" id="overtime_hours" name="overtime_hours" placeholder="Enter overtime hours" value="0">
                        </div>

                        <!-- Overtime Rate -->
                        <div class="mb-3">
                            <label for="overtime_rate" class="form-label">Overtime Rate ($/hr)</label>
                            <input type="number" step="0.01" class="form-control" id="overtime_rate" name="overtime_rate" placeholder="Enter overtime rate" value="0">
                        </div>

                        <!-- Tax -->
                        <div class="mb-3">
                            <label for="tax" class="form-label">Tax ($)</label>
                            <input type="number" step="0.01" class="form-control" id="tax" name="tax" placeholder="Enter tax deductions" value="0" required>
                        </div>

                        <!-- Payment Date -->
                        <div class="mb-3">
                            <label for="payment_date" class="form-label">Payment Date</label>
                            <input type="date" class="form-control" id="payment_date" name="payment_date" required>
                        </div>

                        <!-- Notes -->
                        <div class="mb-3">
                            <label for="notes" class="form-label">Notes</label>
                            <textarea class="form-control" id="notes" name="notes" rows="3" placeholder="Enter any additional notes"></textarea>
                        </div>
                    </div>
                    <div class="modal-footer">
                        <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Cancel</button>
                        <button type="submit" name="add_salary" class="btn btn-success">Add Salary</button>
                    </div>
                </form>
            </div>
        </div>
    </div>

    <!-- Bootstrap JS, DataTables JS, Font Awesome, Custom JS -->
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script src="https://cdn.datatables.net/1.13.5/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/1.13.5/js/dataTables.bootstrap5.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/js/all.min.js"></script>
    <script src="../assets/js/scripts.js"></script>
</body>
</html>) so fix it