in add appointment (<?php
// appointment/add_appointment.php

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

// Check if the user is logged in as admin
if (!isset($_SESSION['admin_id'])) {
    header("Location: ../login.php"); // Redirect to login page
    exit();
}

// Initialize variables
$errors = [];
$success = '';
$patient = [];

// Function to fetch patients from patient_basic_info
function fetch_all_patients_basic_info($pdo) {
    $sql = "SELECT pbi.patient_id, pbi.name, pbi.email FROM patient_basic_info pbi ORDER BY pbi.name ASC";
    $stmt = $pdo->prepare($sql);
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

try {
    // If patient_id is provided via GET, fetch their information
    if (isset($_GET['patient_id']) && intval($_GET['patient_id']) > 0) {
        $patient_id = intval($_GET['patient_id']);

        $sql = "
            SELECT 
                p.id AS patient_id,
                p.name AS patient_name,
                p.email AS patient_email,
                p.phone AS patient_phone,
                p.gender AS patient_gender,
                p.age AS patient_age,
                p.address AS patient_address,
                pbi.weight AS patient_weight,
                pbi.height AS patient_height,
                pc.scheduled_at
            FROM 
                patients p
            LEFT JOIN 
                patient_basic_info pbi ON p.id = pbi.patient_id
            LEFT JOIN 
                patient_consultation pc ON p.id = pc.patient_id
            WHERE 
                p.id = ?
        ";

        $stmt = $pdo->prepare($sql);
        $stmt->execute([$patient_id]);
        $patient = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$patient) {
            $errors[] = "Patient not found.";
        }
    }
} catch (PDOException $e) {
    $errors[] = "Database Error: " . htmlspecialchars($e->getMessage());
}

// Handle form submission
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    // Determine if patient is selected via dropdown or pre-filled
    if (isset($_POST['patient_id']) && $_POST['patient_id'] > 0) {
        $patient_id = intval($_POST['patient_id']);
    } elseif (isset($_POST['hidden_patient_id']) && $_POST['hidden_patient_id'] > 0) {
        $patient_id = intval($_POST['hidden_patient_id']);
    } else {
        $patient_id = 0;
    }

    // Collect and sanitize inputs
    $appointment_date = isset($_POST['appointment_date']) ? trim($_POST['appointment_date']) : '';
    $proposed_appointment_date = isset($_POST['proposed_appointment_date']) && !empty($_POST['proposed_appointment_date']) ? trim($_POST['proposed_appointment_date']) : null;
    $status = isset($_POST['status']) ? trim($_POST['status']) : '';
    $preferred_contact_method = isset($_POST['preferred_contact_method']) ? trim($_POST['preferred_contact_method']) : '';
    $additional_notes = isset($_POST['additional_notes']) ? trim($_POST['additional_notes']) : '';
    $admin_notes = isset($_POST['admin_notes']) ? trim($_POST['admin_notes']) : '';

    // Validation
    if ($patient_id <= 0) {
        $errors[] = "Please select a valid patient.";
    }

    if (empty($appointment_date)) {
        $errors[] = "Appointment date and time are required.";
    } else {
        // Validate datetime format
        $date = DateTime::createFromFormat('Y-m-d\TH:i', $appointment_date);
        if (!$date) {
            $errors[] = "Invalid appointment date and time format.";
        } else {
            $appointment_date_formatted = $date->format('Y-m-d H:i:s');
        }
    }

    if (!empty($proposed_appointment_date)) {
        $proposed_date = DateTime::createFromFormat('Y-m-d\TH:i', $proposed_appointment_date);
        if (!$proposed_date) {
            $errors[] = "Invalid proposed appointment date and time format.";
        } else {
            $proposed_appointment_date_formatted = $proposed_date->format('Y-m-d H:i:s');
        }
    }

    if (empty($status)) {
        $errors[] = "Appointment status is required.";
    } elseif (!in_array($status, ['Pending', 'Confirmed', 'Completed', 'Cancelled'])) {
        $errors[] = "Invalid appointment status.";
    }

    if (empty($preferred_contact_method)) {
        $errors[] = "Preferred contact method is required.";
    } elseif (!in_array($preferred_contact_method, ['Email','Phone','WhatsApp'])) {
        $errors[] = "Invalid contact method.";
    }

    if (empty($errors)) {
        try {
            // Begin transaction
            $pdo->beginTransaction();

            // Optionally, set package_id. Adjust as needed.
            // Assuming a default package exists with id=1
            $package_id = 1; 

            // Insert into appointments table
            $stmt = $pdo->prepare("
                INSERT INTO appointments 
                (package_id, appointment_date, proposed_appointment_date, status, preferred_contact_method, additional_notes, admin_notes, patient_id)
                VALUES 
                (:package_id, :appointment_date, :proposed_appointment_date, :status, :preferred_contact_method, :additional_notes, :admin_notes, :patient_id)
            ");

            $stmt->execute([
                ':package_id' => $package_id,
                ':appointment_date' => $appointment_date_formatted,
                ':proposed_appointment_date' => isset($proposed_appointment_date_formatted) ? $proposed_appointment_date_formatted : null,
                ':status' => $status,
                ':preferred_contact_method' => $preferred_contact_method,
                ':additional_notes' => $additional_notes,
                ':admin_notes' => $admin_notes,
                ':patient_id' => $patient_id
            ]);

            // Commit transaction
            $pdo->commit();

            // Redirect to manage appointments with success message
            $_SESSION['success_message'] = "Appointment added successfully.";
            header("Location: manage_appointments.php");
            exit();

        } catch (PDOException $e) {
            // Rollback transaction on error
            $pdo->rollBack();
            $errors[] = "Database Error: " . htmlspecialchars($e->getMessage());
        }
    }
}

// Fetch all patients from patient_basic_info for the dropdown if no patient is pre-selected
$all_patients = [];
if (!isset($patient_id) || $patient_id <= 0) {
    $all_patients = fetch_all_patients_basic_info($pdo);
}

// Include the header
include '../includes/header.php';
?>
<!DOCTYPE html>
<html>
<head>
    <title>Add Appointment - Longeva</title>
    <!-- Include Bootstrap CSS and Select2 CSS -->
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css"/>
    <link href="https://cdn.jsdelivr.net/npm/select2@4.1.0-rc.0/dist/css/select2.min.css" rel="stylesheet" />

    <!-- Custom CSS -->
    <style>
        /* Custom styles */
        body {
            background-color: #f8f9fa;
            font-family: 'Roboto', sans-serif;
        }
        .container {
            max-width: 800px;
            margin-top: 50px;
            background-color: #ffffff;
            padding: 30px 40px;
            border-radius: 10px;
            box-shadow: 0 4px 15px rgba(0, 0, 0, 0.1);
        }
        h2 {
            text-align: center;
            margin-bottom: 30px;
            color: #4F3F02;
        }
        .btn-primary, .btn-secondary {
            border-radius: 50px;
            padding: 10px 20px;
            margin: 5px;
        }
        .btn-primary {
            background-color: #D1C287;
            border-color: #D1C287;
            color: #4F3F02;
        }
        .btn-primary:hover {
            background-color: #4F3F02;
            color: #ffffff;
        }
        .btn-secondary {
            background-color: #4F3F02;
            border-color: #4F3F02;
            color: #ffffff;
        }
        .btn-secondary:hover {
            background-color: #D1C287;
            color: #4F3F02;
        }
        .form-section {
            margin-bottom: 30px;
        }
        .form-section h4 {
            color: #4F3F02;
            margin-bottom: 20px;
            border-bottom: 2px solid #D1C287;
            padding-bottom: 10px;
        }
        .alert {
            margin-top: 20px;
            border-radius: 8px;
        }
    </style>
</head>
<body>
    <div class="container">
        <h2>Add New Appointment</h2>
        <?php
        if (!empty($errors)) {
            echo '<div class="alert alert-danger">';
            foreach ($errors as $error) {
                echo "<p>" . htmlspecialchars($error) . "</p>";
            }
            echo '</div>';
        }

        if (!empty($success)) {
            echo '<div class="alert alert-success">' . htmlspecialchars($success) . '</div>';
        }
        ?>
        <form method="POST" action="">
            <!-- Patient Selection (if patient_id not pre-selected) -->
            <?php if (!isset($patient_id) || $patient_id <= 0): ?>
                <div class="form-section">
                    <h4>Select Patient</h4>
                    <div class="form-group">
                        <label for="patient_id">Patient:<span class="text-danger">*</span></label>
                        <select id="patient_id" name="patient_id" class="form-control" required>
                            <option value="">-- Select Patient --</option>
                            <?php foreach ($all_patients as $pat): ?>
                                <option value="<?= htmlspecialchars($pat['patient_id']) ?>" <?= (isset($_POST['patient_id']) && $_POST['patient_id'] == $pat['patient_id']) ? 'selected' : '' ?>>
                                    <?= htmlspecialchars($pat['name']) ?> - <?= htmlspecialchars($pat['email']) ?>
                                </option>
                            <?php endforeach; ?>
                        </select>
                    </div>
                </div>
            <?php endif; ?>

            <!-- Hidden input to retain patient_id if pre-selected -->
            <?php if (isset($patient_id) && $patient_id > 0): ?>
                <input type="hidden" name="hidden_patient_id" value="<?= htmlspecialchars($patient_id) ?>">
            <?php endif; ?>

            <!-- Display Patient Information (Read-Only if patient_id is provided) -->
            <?php if (isset($patient_id) && $patient_id > 0 && empty($errors)): ?>
                <div class="form-section">
                    <h4>Patient Information</h4>
                    <div class="form-group">
                        <label>Full Name:</label>
                        <input type="text" class="form-control" value="<?= htmlspecialchars($patient['patient_name']) ?>" readonly>
                    </div>
                    <div class="form-group">
                        <label>Email Address:</label>
                        <input type="email" class="form-control" value="<?= htmlspecialchars($patient['patient_email']) ?>" readonly>
                    </div>
                    <div class="form-group">
                        <label>Phone Number:</label>
                        <input type="text" class="form-control" value="<?= htmlspecialchars($patient['patient_phone']) ?>" readonly>
                    </div>
                    <div class="form-group">
                        <label>Gender:</label>
                        <input type="text" class="form-control" value="<?= htmlspecialchars($patient['patient_gender']) ?>" readonly>
                    </div>
                    <div class="form-group">
                        <label>Age:</label>
                        <input type="number" class="form-control" value="<?= htmlspecialchars($patient['patient_age']) ?>" readonly>
                    </div>
                    <div class="form-group">
                        <label>Address:</label>
                        <textarea class="form-control" rows="2" readonly><?= htmlspecialchars($patient['patient_address']) ?></textarea>
                    </div>
                </div>
            <?php endif; ?>

            <!-- Appointment Details -->
            <div class="form-section">
                <h4>Appointment Details</h4>
                <div class="form-group">
                    <label>Appointment Date & Time:<span class="text-danger">*</span></label>
                    <input type="datetime-local" name="appointment_date" class="form-control" value="<?= isset($_POST['appointment_date']) ? htmlspecialchars($_POST['appointment_date']) : '' ?>" required>
                </div>
                <div class="form-group">
                    <label>Proposed Appointment Date & Time:</label>
                    <input type="datetime-local" name="proposed_appointment_date" class="form-control" value="<?= isset($_POST['proposed_appointment_date']) ? htmlspecialchars($_POST['proposed_appointment_date']) : '' ?>">
                    <small class="form-text text-muted">Use this field to propose a new appointment date/time if needed.</small>
                </div>
                <div class="form-group">
                    <label>Status:<span class="text-danger">*</span></label>
                    <select name="status" class="form-control" required>
                        <option value="">--Select Status--</option>
                        <option value="Pending" <?= (isset($_POST['status']) && $_POST['status'] === 'Pending') ? 'selected' : '' ?>>Pending</option>
                        <option value="Confirmed" <?= (isset($_POST['status']) && $_POST['status'] === 'Confirmed') ? 'selected' : '' ?>>Confirmed</option>
                        <option value="Completed" <?= (isset($_POST['status']) && $_POST['status'] === 'Completed') ? 'selected' : '' ?>>Completed</option>
                        <option value="Cancelled" <?= (isset($_POST['status']) && $_POST['status'] === 'Cancelled') ? 'selected' : '' ?>>Cancelled</option>
                    </select>
                </div>
                <div class="form-group">
                    <label>Preferred Contact Method:<span class="text-danger">*</span></label>
                    <select name="preferred_contact_method" class="form-control" required>
                        <option value="">--Select Contact Method--</option>
                        <option value="Email" <?= (isset($_POST['preferred_contact_method']) && $_POST['preferred_contact_method'] === 'Email') ? 'selected' : '' ?>>Email</option>
                        <option value="Phone" <?= (isset($_POST['preferred_contact_method']) && $_POST['preferred_contact_method'] === 'Phone') ? 'selected' : '' ?>>Phone</option>
                        <option value="WhatsApp" <?= (isset($_POST['preferred_contact_method']) && $_POST['preferred_contact_method'] === 'WhatsApp') ? 'selected' : '' ?>>WhatsApp</option>
                    </select>
                </div>
                <div class="form-group">
                    <label>Additional Notes:</label>
                    <textarea name="additional_notes" class="form-control" rows="3"><?= isset($_POST['additional_notes']) ? htmlspecialchars($_POST['additional_notes']) : '' ?></textarea>
                </div>
                <div class="form-group">
                    <label>Admin Notes:</label>
                    <textarea name="admin_notes" class="form-control" rows="3"><?= isset($_POST['admin_notes']) ? htmlspecialchars($_POST['admin_notes']) : '' ?></textarea>
                    <small class="form-text text-muted">Internal notes for administrative purposes.</small>
                </div>
            </div>

            <!-- Action Buttons -->
            <div class="form-group text-center">
                <button type="submit" class="btn btn-primary">Add Appointment</button>
                <a href="manage_appointments.php" class="btn btn-secondary">Cancel</a>
            </div>
        </form>
    </div>

    <!-- Include jQuery, Popper.js, Bootstrap JS, and Select2 JS -->
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.1/dist/umd/popper.min.js"></script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/select2@4.1.0-rc.0/dist/js/select2.min.js"></script>

    <!-- Initialize Select2 for Patient Selection -->
    <script>
        $(document).ready(function() {
            $('#patient_id').select2({
                placeholder: 'Select a patient',
                allowClear: true,
                width: '100%',
                // You can enable AJAX here if you have a large number of patients
                /*
                ajax: {
                    url: 'path_to_your_patient_search_endpoint.php',
                    dataType: 'json',
                    delay: 250,
                    data: function (params) {
                        return {
                            search: params.term, // search term
                            page: params.page
                        };
                    },
                    processResults: function (data, params) {
                        // parse the results into the format expected by Select2
                        // since we are using custom formatting functions we do not need to
                        // alter the remote JSON data
                        params.page = params.page || 1;

                        return {
                            results: data.items,
                            pagination: {
                                more: (params.page * 30) < data.total_count
                            }
                        };
                    },
                    cache: true
                },
                minimumInputLength: 1,
                templateResult: formatPatient, // optional
                templateSelection: formatPatientSelection // optional
                */
            });
        });

        /*
        function formatPatient(patient) {
            if (patient.loading) {
                return patient.text;
            }

            var markup = "<div>" + patient.name + " - " + patient.email + "</div>";
            return $(markup);
        }

        function formatPatientSelection(patient) {
            return patient.name || patient.text;
        }
        */
    </script>
</body>
</html>
<?php
// Include the footer
include '../includes/footer.php';
?>) it show this errror (Oops, looks like the page is lost.
This is not a fault, just an accident that was not intentional.) after i add appointment from this page (<?php
// longeva_health_journey_answers/index.php

// Start session and include necessary files
session_start();
require '../db_connect.php';

// Check if the user is logged in as admin
if (!isset($_SESSION['admin_id'])) {
    header("Location: ../login.php"); // Redirect to login page
    exit();
}

// Include the header
include '../includes/header.php';

// Initialize variables
$patients = [];

try {
    // Fetch all patients who completed the page 8 survey
    // Including additional tables via LEFT JOIN
    $sql = "
        SELECT 
            pbi.patient_id,
            pbi.name AS patient_name,
            pbi.email AS patient_email,
            pbi.phone AS patient_phone,
            pbi.gender AS patient_gender,
            pbi.age AS patient_age,
            pbi.weight AS patient_weight,
            pbi.height AS patient_height,
            pbi.created_at,
            pbi.updated_at,
            pc.scheduled_at,
            cons.consent,
            goals.primary_health_goals,
            goals.health_concerns,
            metrics.exercise_frequency,
            metrics.diet,
            metrics.diet_specify,
            metrics.sleep_average,
            metrics.smoking_status,
            history.chronic_conditions,
            history.chronic_conditions_specify,
            history.medications,
            history.medications_list,
            history.major_surgeries,
            history.major_surgeries_specify
        FROM 
            patient_basic_info pbi
        INNER JOIN 
            patient_consultation pc ON pbi.patient_id = pc.patient_id
        LEFT JOIN 
            patient_consent cons ON pbi.patient_id = cons.patient_id
        LEFT JOIN 
            patient_goals goals ON pbi.patient_id = goals.patient_id
        LEFT JOIN 
            patient_health_metrics metrics ON pbi.patient_id = metrics.patient_id
        LEFT JOIN 
            patient_medical_history history ON pbi.patient_id = history.patient_id
    ";

    $stmt = $pdo->query($sql);
    $patients = $stmt->fetchAll(PDO::FETCH_ASSOC);

} catch (PDOException $e) {
    echo "<div class='alert alert-danger'>Database Error: " . htmlspecialchars($e->getMessage()) . "</div>";
}
?>

<!-- Custom Styles -->
<style>
    @import url('https://fonts.googleapis.com/css2?family=Roboto:wght@300;400;700&display=swap');

    :root {
        --primary-color: #4F3F02;
        --secondary-color: #D1C287;
        --background-color: #f9f9f9;
        --text-color: #333;
    }

    body {
        font-family: 'Roboto', sans-serif;
        line-height: 1.6;
        background-color: var(--background-color);
        color: var(--text-color);
        margin: 0;
        padding: 0;
    }

    .container-survey {
        max-width: 1200px;
        margin: 40px auto;
        padding: 20px;
        background-color: white;
        border-radius: 20px;
        box-shadow: 0 15px 30px rgba(0, 0, 0, 0.1);
        overflow: hidden;
    }

    .header {
        background: linear-gradient(135deg, var(--primary-color) 0%, var(--secondary-color) 100%);
        color: white;
        padding: 20px;
        text-align: center;
        border-radius: 20px 20px 0 0;
        position: relative;
        overflow: hidden;
    }

    .header h2 {
        margin: 0;
        font-size: 32px;
        font-weight: 700;
    }

    .header::before {
        content: "";
        position: absolute;
        top: -50%;
        left: -50%;
        width: 200%;
        height: 200%;
        background: radial-gradient(circle, rgba(255,255,255,0.2) 0%, rgba(255,255,255,0) 70%);
        transform: rotate(30deg);
    }

    .section {
        margin-top: 20px;
        padding: 20px;
        background-color: var(--background-color);
        border-radius: 10px;
        box-shadow: 0 4px 6px rgba(0, 0, 0, 0.05);
        transition: transform 0.3s ease, box-shadow 0.3s ease;
    }

    .section:hover {
        transform: translateY(-5px);
        box-shadow: 0 6px 12px rgba(0, 0, 0, 0.1);
    }

    .section h3 {
        font-size: 24px;
        margin-top: 0;
        color: var(--primary-color);
        border-bottom: 2px solid var(--secondary-color);
        padding-bottom: 10px;
    }

    .card {
        background-color: white;
        border-radius: 10px;
        box-shadow: 0 4px 6px rgba(0,0,0,0.1);
        transition: transform 0.3s ease, box-shadow 0.3s ease;
    }

    .card:hover {
        transform: translateY(-5px);
        box-shadow: 0 6px 12px rgba(0,0,0,0.15);
    }

    .card-body {
        padding: 20px;
    }

    .table {
        width: 100%;
        border-collapse: collapse;
        margin-bottom: 20px;
    }

    .table th, .table td {
        padding: 12px 15px;
        border: 1px solid #ddd;
        text-align: left;
    }

    .table thead {
        background-color: var(--primary-color);
        color: white;
    }

    .table tbody tr:nth-child(even) {
        background-color: #f2f2f2;
    }

    .btn {
        padding: 6px 12px;
        border: none;
        border-radius: 4px;
        cursor: pointer;
        font-size: 14px;
        text-decoration: none;
    }

    .btn-primary {
        background-color: var(--primary-color);
        color: white;
    }

    .btn-secondary {
        background-color: var(--secondary-color);
        color: white;
    }

    .btn-info {
        background-color: #17a2b8;
        color: white;
    }

    .btn-warning {
        background-color: #ffc107;
        color: white;
    }

    .btn-danger {
        background-color: #dc3545;
        color: white;
    }

    .btn:hover {
        opacity: 0.9;
    }

    /* Responsive Table */
    @media (max-width: 768px) {
        .table thead {
            display: none;
        }

        .table, .table tbody, .table tr, .table td {
            display: block;
            width: 100%;
        }

        .table tr {
            margin-bottom: 15px;
            border: 1px solid #ddd;
            border-radius: 10px;
            padding: 10px;
        }

        .table td {
            text-align: right;
            padding-left: 50%;
            position: relative;
        }

        .table td::before {
            content: attr(data-label);
            position: absolute;
            left: 15px;
            width: calc(50% - 30px);
            padding-right: 10px;
            white-space: nowrap;
            text-align: left;
            font-weight: bold;
        }
    }
</style>

<div class="container-survey">
    <div class="header">
        <h2>Survey Information - Longeva Health Journey</h2>
    </div>

    <!-- New Card Related to Page Index -->
    <div class="section">
        <div class="card">
            <div class="card-body">
                <h3>Welcome to the Longeva Health Journey Dashboard</h3>
                <p>Manage patient surveys, view detailed information, and perform administrative tasks related to the health journey program all in one place.</p>
            </div>
        </div>
    </div>

    <!-- Patient Data Table -->
    <div class="section">
        <h3>Patient Data</h3>
        <div class="table-responsive">
            <table class="table" id="patientTable">
                <thead>
                    <tr>
                        <th></th> <!-- For toggle button -->
                        <th>Patient ID</th>
                        <th>Name</th>
                        <th>Email</th>
                        <th>Phone</th>
                        <th>Gender</th>
                        <th>Age</th>
                        <th>Consultation Date</th>
                        <th>Actions</th>
                    </tr>
                </thead>
                <tbody>
                    <?php if ($patients): ?>
                        <?php foreach ($patients as $patient): ?>
                            <tr>
                                <td>
                                    <button class="btn btn-primary toggle-details" data-bs-toggle="collapse" data-bs-target="#patient-<?= htmlspecialchars($patient['patient_id']) ?>" aria-expanded="false" aria-controls="patient-<?= htmlspecialchars($patient['patient_id']) ?>">
                                        +
                                    </button>
                                </td>
                                <td data-label="Patient ID"><?= htmlspecialchars($patient['patient_id']) ?></td>
                                <td data-label="Name"><?= htmlspecialchars($patient['patient_name']) ?></td>
                                <td data-label="Email"><?= htmlspecialchars($patient['patient_email']) ?></td>
                                <td data-label="Phone"><?= htmlspecialchars($patient['patient_phone']) ?></td>
                                <td data-label="Gender"><?= htmlspecialchars($patient['patient_gender']) ?></td>
                                <td data-label="Age"><?= htmlspecialchars($patient['patient_age']) ?></td>
                                <td data-label="Consultation Date"><?= htmlspecialchars(date('Y-m-d H:i', strtotime($patient['scheduled_at']))) ?></td>
                                <td data-label="Actions">
                                    <a href="view_patient.php?id=<?= htmlspecialchars($patient['patient_id']) ?>" class="btn btn-info">View</a>
                                    <a href="add_appointment.php?id=<?= htmlspecialchars($patient['patient_id']) ?>" class="btn btn-warning">Add Appointment</a>
                                    <a href="delete_patient.php?id=<?= htmlspecialchars($patient['patient_id']) ?>" class="btn btn-danger" onclick="return confirm('Are you sure you want to delete this patient?')">Delete</a>
                                </td>
                            </tr>
                            <tr>
                                <td colspan="9" class="p-0 border-0">
                                    <div class="collapse" id="patient-<?= htmlspecialchars($patient['patient_id']) ?>">
                                        <div class="card card-body">
                                            <h5>Additional Information</h5>
                                            <div class="row">
                                                <div class="col-md-4">
                                                    <p><strong>Weight:</strong> <?= htmlspecialchars($patient['patient_weight']) ?> kg</p>
                                                    <p><strong>Height:</strong> <?= htmlspecialchars($patient['patient_height']) ?> cm</p>
                                                    <p><strong>Consent:</strong> <?= htmlspecialchars($patient['consent']) ?></p>
                                                </div>
                                                <div class="col-md-4">
                                                    <p><strong>Primary Health Goals:</strong> <?= nl2br(htmlspecialchars($patient['primary_health_goals'])) ?></p>
                                                    <p><strong>Health Concerns:</strong> <?= nl2br(htmlspecialchars($patient['health_concerns'])) ?></p>
                                                    <p><strong>Exercise Frequency:</strong> <?= htmlspecialchars($patient['exercise_frequency']) ?></p>
                                                </div>
                                                <div class="col-md-4">
                                                    <p><strong>Diet:</strong> <?= htmlspecialchars($patient['diet']) ?>
                                                        <?php if ($patient['diet'] === 'Yes'): ?>
                                                            (<?= htmlspecialchars($patient['diet_specify']) ?>)
                                                        <?php endif; ?>
                                                    </p>
                                                    <p><strong>Sleep Average:</strong> <?= htmlspecialchars($patient['sleep_average']) ?></p>
                                                    <p><strong>Smoking Status:</strong> <?= htmlspecialchars($patient['smoking_status']) ?></p>
                                                    <p><strong>Chronic Conditions:</strong> <?= htmlspecialchars($patient['chronic_conditions']) ?>
                                                        <?php if ($patient['chronic_conditions'] === 'Yes'): ?>
                                                            (<?= htmlspecialchars($patient['chronic_conditions_specify']) ?>)
                                                        <?php endif; ?>
                                                    </p>
                                                    <p><strong>Medications:</strong> <?= htmlspecialchars($patient['medications']) ?>
                                                        <?php if ($patient['medications'] === 'Yes'): ?>
                                                            (<?= htmlspecialchars($patient['medications_list']) ?>)
                                                        <?php endif; ?>
                                                    </p>
                                                    <p><strong>Major Surgeries:</strong> <?= htmlspecialchars($patient['major_surgeries']) ?>
                                                        <?php if ($patient['major_surgeries'] === 'Yes'): ?>
                                                            (<?= htmlspecialchars($patient['major_surgeries_specify']) ?>)
                                                        <?php endif; ?>
                                                    </p>
                                                </div>
                                            </div>
                                        </div>
                                    </div>
                                </td>
                            </tr>
                        <?php endforeach; ?>
                    <?php else: ?>
                        <tr>
                            <td colspan="9" class="text-center">No survey answers found.</td>
                        </tr>
                    <?php endif; ?>
                </tbody>
            </table>
        </div>
    </div>
</div>

<!-- Include necessary scripts for DataTables and Bootstrap (if not already included in header.php) -->
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<!-- Include Bootstrap JS if necessary -->
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
<!-- Include DataTables JS -->
<script src="https://cdn.datatables.net/1.13.6/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.13.6/js/dataTables.bootstrap5.min.js"></script>

<!-- Initialize DataTables and Toggle Functionality -->
<script>
$(document).ready(function() {
    var table = $('#patientTable').DataTable({
        "paging": true,
        "searching": true,
        "ordering": true,
        "info": true,
        "lengthChange": true,
        "columnDefs": [
            { "orderable": false, "targets": 0 } // Make the first column (toggle button) not orderable
        ],
        "language": {
            "search": "Filter records:",
            "lengthMenu": "Show _MENU_ entries",
            "info": "Showing _START_ to _END_ of _TOTAL_ patients",
            "paginate": {
                "previous": "Prev",
                "next": "Next"
            }
        },
        "responsive": true
    });

    // Change '+' to '-' when row is expanded and vice versa
    $('#patientTable tbody').on('click', 'button.toggle-details', function () {
        var button = $(this);
        var target = button.data('bs-target');
        var isExpanded = $(target).hasClass('show');
        if (isExpanded) {
            button.text('+');
        } else {
            button.text('-');
        }
    });
});
</script>

<?php
// Include the footer
include '../includes/footer.php';
?>) so fix and when i add appoinmtnet, it automatically show in the page manage appointment (<?php
session_start();
require_once '../db_connect.php';

// includes/header.php
include '../includes/header.php';

// Enable error reporting for debugging (remove or comment out in production)
ini_set('display_errors', 1);
error_reporting(E_ALL);

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


// Display success or error messages
if (isset($_SESSION['success_message'])) {
    echo '<div class="alert alert-success">' . $_SESSION['success_message'] . '</div>';
    unset($_SESSION['success_message']);
}
if (isset($_SESSION['error_message'])) {
    echo '<div class="alert alert-danger">' . $_SESSION['error_message'] . '</div>';
    unset($_SESSION['error_message']);
}



// Handle search and filtering
$search_query = isset($_GET['search_query']) ? trim($_GET['search_query']) : '';
$status_filter = isset($_GET['status_filter']) ? $_GET['status_filter'] : '';

$where_clauses = [];
$params = [];
$types = '';

if ($search_query !== '') {
    $where_clauses[] = "(a.name LIKE CONCAT('%', ?, '%') OR a.email LIKE CONCAT('%', ?, '%') OR a.phone LIKE CONCAT('%', ?, '%'))";
    $params[] = $search_query;
    $params[] = $search_query;
    $params[] = $search_query;
    $types .= 'sss';
}

if ($status_filter !== '') {
    $where_clauses[] = "a.status = ?";
    $params[] = $status_filter;
    $types .= 's';
}

$where_sql = '';
if (!empty($where_clauses)) {
    $where_sql = 'WHERE ' . implode(' AND ', $where_clauses);
}

// Prepare SQL statement
$sql = "
    SELECT a.*, p.package_name
    FROM appointments a
    LEFT JOIN packages p ON a.package_id = p.id
    $where_sql
    ORDER BY a.appointment_date ASC
";

$stmt = $conn->prepare($sql);
if ($stmt === false) {
    die('Prepare() failed: ' . htmlspecialchars($conn->error));
}

if (!empty($params)) {
    // Prepare parameters for bind_param
    $bind_params = [];
    $bind_params[] = &$types;
    for ($i = 0; $i < count($params); $i++) {
        $bind_params[] = &$params[$i];
    }
    // Bind parameters
    call_user_func_array(array($stmt, 'bind_param'), $bind_params);
}

$stmt->execute();
$result = $stmt->get_result();
?>
<!DOCTYPE html>
<html>
<head>
    <title>Manage Appointments - Longeva</title>
    <!-- Include Bootstrap CSS and DataTables CSS -->
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css"/>
    <link rel="stylesheet" href="https://cdn.datatables.net/1.13.5/css/dataTables.bootstrap4.min.css"/>
    <!-- Custom CSS -->
    <style>
        /* Custom CSS for modern design */
        body {
            background-color: #f8f9fa;
            font-family: Arial, sans-serif;
        }

        .dashboard-header {
            background-color: #4F3F02; /* Brown color */
            color: #fff;
            padding: 20px;
            text-align: center;
            border-radius: 8px;
            margin-bottom: 30px;
        }

        h1, h2 {
            font-weight: bold;
        }

        .btn-primary {
            background-color: #D1C287; /* Light golden color */
            border-color: #D1C287;
            color: #4F3F02; /* Brown color */
            font-weight: bold;
            text-decoration: none;
            margin-right: 5px;
        }

        .btn-primary:hover {
            background-color: #4F3F02; /* Brown color */
            border-color: #D1C287;
            color: #fff;
            text-decoration: none;
        }

        .btn-secondary {
            background-color: #4F3F02; /* Brown color */
            border-color: #4F3F02;
            color: #fff;
            font-weight: bold;
            text-decoration: none;
            margin-right: 5px;
        }

        .btn-secondary:hover {
            background-color: #D1C287; /* Light golden color */
            color: #4F3F02; /* Brown color */
            text-decoration: none;
        }

        .filters {
            background-color: #fff;
            padding: 20px;
            border-radius: 8px;
            margin-bottom: 20px;
            box-shadow: 0 0 10px rgba(0, 0, 0, 0.1);
        }

        .filters .form-control, .filters select {
            margin-bottom: 15px;
            border: 1px solid #D1C287;
            border-radius: 5px;
        }

        .filters .form-control:focus, .filters select:focus {
            border-color: #4F3F02;
            box-shadow: none;
        }

        .table-responsive {
            margin-top: 20px;
        }

        .table {
            background-color: #fff;
            border-radius: 8px;
            overflow: hidden;
            width: 100%;
            border-collapse: collapse;
        }

        th, td {
            vertical-align: middle !important;
            border: 1px solid #ccc;
            padding: 10px;
            text-align: left;
        }

        .card {
            border: none;
            border-radius: 10px;
        }

        .card .card-title {
            color: #4F3F02;
        }

        /* DataTables customizations */
        .dataTables_wrapper .dataTables_filter input {
            border-radius: 5px;
            border: 1px solid #D1C287;
        }

        .dataTables_wrapper .dataTables_length select {
            border-radius: 5px;
            border: 1px solid #D1C287;
        }

        /* Responsive adjustments */
        @media (max-width: 768px) {
            .filters .form-control, .filters select {
                margin-bottom: 10px;
            }
        }

        .actions a {
            text-decoration: none;
            color: #4F3F02;
            margin-right: 10px;
        }

        .actions a:hover {
            text-decoration: underline;
        }

        .summary {
            margin-bottom: 20px;
            padding: 20px;
            background-color: #fff;
            border-radius: 8px;
            box-shadow: 0 0 10px rgba(0, 0, 0, 0.1);
        }

        .summary h3 {
            margin-top: 0;
        }

        /* Status Label Styles */
        .status-label {
            padding: 5px 10px;
            border-radius: 5px;
            color: #fff;
            font-weight: bold;
            text-align: center;
            display: inline-block;
        }

        .status-pending {
            background-color: #ffc107; /* Yellow */
            color: #fff;
        }

        .status-confirmed {
            background-color: #17a2b8; /* Blue */
            color: #fff;
        }

        .status-completed {
            background-color: #28a745; /* Green */
            color: #fff;
        }

        .status-cancelled {
            background-color: #dc3545; /* Red */
            color: #fff;
        }
    </style>
    <!-- Include jQuery and DataTables JS -->
    <!--<script src="https://code.jquery.com/jquery-3.5.1.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.bootstrap4.min.js"></script>
</head>
<body>
    <div class="dashboard-header">
        <h2>Manage Appointments</h2>
    </div>

    <div class="container">
        <!-- Back to Dashboard Button -->
        <div class="mb-2">
            <a href="../admin_dashboard.php" class="btn btn-secondary">Back to Dashboard</a>
        </div>

        <!-- Summary Section -->
        <div class="summary">
            <?php
            // Fetch appointment statistics
            $stats_sql = "SELECT status, COUNT(*) as count FROM appointments GROUP BY status";
            $stats_result = $conn->query($stats_sql);
            $stats = [];
            while ($row_stats = $stats_result->fetch_assoc()) {
                $stats[$row_stats['status']] = $row_stats['count'];
            }
            ?>
            <h3>Appointment Summary</h3>
            <p>Total Appointments: <?php echo array_sum($stats); ?></p>
            <ul>
                <li>Pending: <?php echo isset($stats['Pending']) ? $stats['Pending'] : 0; ?></li>
                <li>Confirmed: <?php echo isset($stats['Confirmed']) ? $stats['Confirmed'] : 0; ?></li>
                <li>Completed: <?php echo isset($stats['Completed']) ? $stats['Completed'] : 0; ?></li>
                <li>Cancelled: <?php echo isset($stats['Cancelled']) ? $stats['Cancelled'] : 0; ?></li>
            </ul>
        </div>

        <!-- Search and filter form -->
        <div class="filters">
            <form class="search-form" method="GET" action="">
                <div class="form-row">
                    <div class="col-md-4">
                        <input type="text" name="search_query" class="form-control" placeholder="Search by name, email, or phone" value="<?php echo htmlspecialchars($search_query); ?>"/>
                    </div>
                    <div class="col-md-3">
                        <select name="status_filter" class="form-control">
                            <option value="">-- Status --</option>
                            <option value="Pending" <?php if ($status_filter == 'Pending') echo 'selected'; ?>>Pending</option>
                            <option value="Confirmed" <?php if ($status_filter == 'Confirmed') echo 'selected'; ?>>Confirmed</option>
                            <option value="Completed" <?php if ($status_filter == 'Completed') echo 'selected'; ?>>Completed</option>
                            <option value="Cancelled" <?php if ($status_filter == 'Cancelled') echo 'selected'; ?>>Cancelled</option>
                        </select>
                    </div>
                    <div class="col-md-5">
                        <button type="submit" class="btn btn-primary">Search</button>
                        <a href="manage_appointments.php" class="btn btn-secondary">Reset</a>
                    </div>
                </div>
            </form>
        </div>

        <!-- Appointments Table -->
        <div class="table-responsive">
            <table class="table" id="appointmentsTable">
                <thead>
                    <tr>
                        <th>ID</th>
                        <th>Name</th>
                        <th>Package</th>
                        <th>Appointment Date</th>
                        <th>Status</th>
                        <th>Preferred Contact Method</th>
                        <th>Actions</th>
                    </tr>
                </thead>
                <tbody>
                    <?php
                    while ($row = $result->fetch_assoc()) {
                        // Format date
                        $appointment_date = date('d-m-Y H:i', strtotime($row['appointment_date']));

                        // Determine status label class
                        $status_class = '';
                        switch ($row['status']) {
                            case 'Pending':
                                $status_class = 'status-pending';
                                break;
                            case 'Confirmed':
                                $status_class = 'status-confirmed';
                                break;
                            case 'Completed':
                                $status_class = 'status-completed';
                                break;
                            case 'Cancelled':
                                $status_class = 'status-cancelled';
                                break;
                            default:
                                $status_class = '';
                                break;
                        }

   echo "<tr>
       <td>".$row['id']."</td>
       <td>".$row['name']."</td>
       <td>".$row['package_name']."</td>
       <td>".$appointment_date."</td>
       <td><span class='status-label $status_class'>".$row['status']."</span></td>
       <td>".$row['preferred_contact_method']."</td>
       <td class='actions'>
           <a href='appointment_details.php?id=".$row['id']."'>View</a> |
           <a href='edit_appointment.php?id=".$row['id']."'>Edit</a> |
           <a href='delete_appointment.php?id=".$row['id']."' onclick=\"return confirm('Are you sure you want to delete this appointment?');\">Delete</a> |
           <a href='send_email.php?id=".$row['id']."'>Send Email</a> |
           <a href='../create_patient.php?appointment_id=".$row['id']."'>Add as Patient</a>
       </td>
   </tr>";                    }
                    ?>
                </tbody>
            </table>
        </div>
    </div>

    <!-- Activate DataTables -->
    <script>
        $(document).ready(function() {
            $('#appointmentsTable').DataTable({
                // Customize DataTables options here
                "pageLength": 10,
                "order": [[3, "asc"]],
                "lengthMenu": [ [10, 25, 50, -1], [10, 25, 50, "All"] ]
            });
        });
    </script>
</body>
</html>
<?php
$stmt->close();

// includes/footer.php

include '../includes/footer.php';

?>) but i don't want to change the mange page codes and thsi info databse (-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: Oct 17, 2024 at 05:30 AM
-- 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 `appointments`
--

CREATE TABLE `appointments` (
  `id` int(11) NOT NULL,
  `package_id` int(11) NOT NULL,
  `appointment_date` datetime NOT NULL,
  `proposed_appointment_date` datetime DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `age` int(3) DEFAULT NULL,
  `gender` enum('Male','Female','Other') DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `preferred_contact_method` enum('Email','Phone','WhatsApp') DEFAULT NULL,
  `additional_notes` text DEFAULT NULL,
  `admin_notes` text DEFAULT NULL,
  `status` enum('Pending','Confirmed','Completed','Cancelled') DEFAULT 'Pending',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `patient_id` int(11) 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 `expenses`
--

CREATE TABLE `expenses` (
  `id` int(11) NOT NULL,
  `expense_date` date NOT NULL,
  `category` varchar(100) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `amount` decimal(10,2) NOT 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;

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

--
-- 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 `packages`
--

CREATE TABLE `packages` (
  `id` int(11) NOT NULL,
  `package_name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `details` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) 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(3) DEFAULT NULL,
  `gender` enum('Male','Female','Other') DEFAULT 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_basic_info`
--

CREATE TABLE `patient_basic_info` (
  `patient_id` int(11) NOT NULL,
  `gender` enum('Male','Female','Other') NOT NULL,
  `age` int(3) NOT NULL,
  `weight` decimal(5,2) NOT NULL,
  `height` decimal(5,2) NOT NULL,
  `name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `phone` varchar(20) NOT 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;

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

--
-- Table structure for table `patient_consent`
--

CREATE TABLE `patient_consent` (
  `patient_id` int(11) NOT NULL,
  `consent` enum('accepted','declined') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `patient_consultation`
--

CREATE TABLE `patient_consultation` (
  `patient_id` int(11) NOT NULL,
  `scheduled_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `patient_goals`
--

CREATE TABLE `patient_goals` (
  `patient_id` int(11) NOT NULL,
  `primary_health_goals` text NOT NULL,
  `health_concerns` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `patient_health_metrics`
--

CREATE TABLE `patient_health_metrics` (
  `patient_id` int(11) NOT NULL,
  `exercise_frequency` enum('Daily','Weekly','Rarely','Never') NOT NULL,
  `diet` enum('Yes','No') NOT NULL,
  `diet_specify` varchar(255) DEFAULT NULL,
  `sleep_average` enum('<5 Hours','5-7 Hours','7-9 Hours','>9 Hours') NOT NULL,
  `smoking_status` enum('Yes','No','Occasionally') NOT 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 `patient_medical_history`
--

CREATE TABLE `patient_medical_history` (
  `patient_id` int(11) NOT NULL,
  `chronic_conditions` enum('Yes','No') NOT NULL,
  `chronic_conditions_specify` varchar(255) DEFAULT NULL,
  `medications` enum('Yes','No') NOT NULL,
  `medications_list` text DEFAULT NULL,
  `major_surgeries` enum('Yes','No') NOT NULL,
  `major_surgeries_specify` varchar(255) DEFAULT 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 `questionnaire_responses`
--

CREATE TABLE `questionnaire_responses` (
  `id` int(11) NOT NULL,
  `session_id` varchar(255) NOT NULL,
  `page_number` int(2) NOT NULL,
  `question` varchar(255) NOT NULL,
  `answer` text NOT NULL,
  `submitted_at` timestamp NULL DEFAULT current_timestamp(),
  `consent` enum('Yes','No') DEFAULT 'No'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_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 `appointments`
--
ALTER TABLE `appointments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `package_id` (`package_id`),
  ADD KEY `appointments_ibfk_2` (`patient_id`);

--
-- 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 `expenses`
--
ALTER TABLE `expenses`
  ADD PRIMARY KEY (`id`);

--
-- 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 `packages`
--
ALTER TABLE `packages`
  ADD PRIMARY KEY (`id`);

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

--
-- Indexes for table `patient_basic_info`
--
ALTER TABLE `patient_basic_info`
  ADD PRIMARY KEY (`patient_id`);

--
-- Indexes for table `patient_consent`
--
ALTER TABLE `patient_consent`
  ADD PRIMARY KEY (`patient_id`);

--
-- Indexes for table `patient_consultation`
--
ALTER TABLE `patient_consultation`
  ADD PRIMARY KEY (`patient_id`);

--
-- Indexes for table `patient_goals`
--
ALTER TABLE `patient_goals`
  ADD PRIMARY KEY (`patient_id`);

--
-- Indexes for table `patient_health_metrics`
--
ALTER TABLE `patient_health_metrics`
  ADD PRIMARY KEY (`patient_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 `patient_medical_history`
--
ALTER TABLE `patient_medical_history`
  ADD PRIMARY KEY (`patient_id`);

--
-- 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 `questionnaire_responses`
--
ALTER TABLE `questionnaire_responses`
  ADD PRIMARY KEY (`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 `appointments`
--
ALTER TABLE `appointments`
  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 `expenses`
--
ALTER TABLE `expenses`
  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 `packages`
--
ALTER TABLE `packages`
  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 `questionnaire_responses`
--
ALTER TABLE `questionnaire_responses`
  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 `appointments`
--
ALTER TABLE `appointments`
  ADD CONSTRAINT `appointments_ibfk_1` FOREIGN KEY (`package_id`) REFERENCES `packages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `appointments_ibfk_2` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- 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_basic_info`
--
ALTER TABLE `patient_basic_info`
  ADD CONSTRAINT `fk_patient_basic_info_patient` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`) ON DELETE CASCADE 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 please fix it