in this codes ../longeva_health_journey/pages/page8.php (<?php
// longeva_health_journey/pages/page8.php

// Enable error reporting for debugging (Remove in production)
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

// Start session and include database connection
session_start();
require '../db_connect.php';

// Check if patient_id is set; if not, redirect to page3.php to collect information
if (!isset($_SESSION['patient_id'])) {
    header("Location: page3.php");
    exit();
}

$errors = [];

// Initialize variables
$name = '';
$email = '';
$phone = '';
$gender = '';
$age = '';
$weight = '';
$height = '';
$address = '';
$scheduled_at = '';

// Function to safely escape output
function safe_htmlspecialchars($string) {
    return htmlspecialchars($string ?? '', ENT_QUOTES, 'UTF-8');
}

// Fetch existing patient_basic_info and consultation date/time to pre-fill the form
try {
    // Fetch patient basic info including address
    $stmt = $pdo->prepare("SELECT name, email, phone, gender, age, weight, height, address FROM patient_basic_info WHERE patient_id = ?");
    $stmt->execute([$_SESSION['patient_id']]);
    $basic_info = $stmt->fetch(PDO::FETCH_ASSOC);

    if ($basic_info) {
        $name = safe_htmlspecialchars($basic_info['name']);
        $email = safe_htmlspecialchars($basic_info['email']);
        $phone = safe_htmlspecialchars($basic_info['phone']);
        $gender = safe_htmlspecialchars($basic_info['gender']);
        $age = safe_htmlspecialchars($basic_info['age']);
        $weight = safe_htmlspecialchars($basic_info['weight']);
        $height = safe_htmlspecialchars($basic_info['height']);
        $address = safe_htmlspecialchars($basic_info['address']);
    }

    // Fetch existing consultation date and time if available
    $stmt = $pdo->prepare("SELECT scheduled_at FROM patient_consultation WHERE patient_id = ?");
    $stmt->execute([$_SESSION['patient_id']]);
    $consultation = $stmt->fetch();

    if ($consultation) {
        // Ensure the datetime format is compatible with input[type="datetime-local"]
        $scheduled_at = date('Y-m-d\TH:i', strtotime($consultation['scheduled_at']));
    }
} catch (PDOException $e) {
    $errors[] = "Database Error: " . safe_htmlspecialchars($e->getMessage());
}

// Handle form submission
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    // Collect and sanitize inputs
    $name = isset($_POST['name']) ? safe_htmlspecialchars(trim($_POST['name'])) : '';
    $email = isset($_POST['email']) ? filter_var(trim($_POST['email']), FILTER_SANITIZE_EMAIL) : '';
    $phone = isset($_POST['phone']) ? safe_htmlspecialchars(trim($_POST['phone'])) : '';
    $gender = isset($_POST['gender']) ? safe_htmlspecialchars(trim($_POST['gender'])) : '';
    $age = isset($_POST['age']) ? intval($_POST['age']) : '';
    $weight = isset($_POST['weight']) ? floatval($_POST['weight']) : '';
    $height = isset($_POST['height']) ? floatval($_POST['height']) : '';
    $address = isset($_POST['address']) ? safe_htmlspecialchars(trim($_POST['address'])) : '';
    $scheduled_at = isset($_POST['scheduled_at']) ? safe_htmlspecialchars(trim($_POST['scheduled_at'])) : '';

    // Validate inputs
    if (empty($name)) {
        $errors[] = "Name is required.";
    }
    if (empty($email) || !filter_var($email, FILTER_VALIDATE_EMAIL)) {
        $errors[] = "A valid Email is required.";
    }
    if (empty($phone)) {
        $errors[] = "Phone number is required.";
    } elseif (!preg_match('/^[0-9+\-\s()]{7,20}$/', $phone)) {
        $errors[] = "Please enter a valid phone number.";
    }
    if (empty($gender)) {
        $errors[] = "Gender is required.";
    }
    if (empty($age) || $age <= 0) {
        $errors[] = "Valid age is required.";
    }
    if (empty($weight) || $weight <= 0) {
        $errors[] = "Valid weight is required.";
    }
    if (empty($height) || $height <= 0) {
        $errors[] = "Valid height is required.";
    }
    if (empty($address)) {
        $errors[] = "Address is required.";
    }
    if (empty($scheduled_at)) {
        $errors[] = "Please select a consultation date and time.";
    } else {
        // Validate datetime format
        $date = DateTime::createFromFormat('Y-m-d\TH:i', $scheduled_at);
        if (!$date) {
            $errors[] = "Invalid date and time format.";
        }
    }

    if (empty($errors)) {
        try {
            // Begin transaction to ensure both inserts/updates are successful
            $pdo->beginTransaction();

            // Update or Insert into patient_basic_info
            // Check if patient_basic_info already exists
            $stmt = $pdo->prepare("SELECT patient_id FROM patient_basic_info WHERE patient_id = ?");
            $stmt->execute([$_SESSION['patient_id']]);
            if ($stmt->rowCount() > 0) {
                // Update existing record
                $stmt = $pdo->prepare("UPDATE patient_basic_info SET name = ?, email = ?, phone = ?, gender = ?, age = ?, weight = ?, height = ?, address = ? WHERE patient_id = ?");
                $stmt->execute([$name, $email, $phone, $gender, $age, $weight, $height, $address, $_SESSION['patient_id']]);
            } else {
                // Insert new record with patient_id
                $stmt = $pdo->prepare("INSERT INTO patient_basic_info (patient_id, name, email, phone, gender, age, weight, height, address) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
                $stmt->execute([$_SESSION['patient_id'], $name, $email, $phone, $gender, $age, $weight, $height, $address]);
            }

            // Update or Insert into patient_consultation
            $stmt = $pdo->prepare("SELECT patient_id FROM patient_consultation WHERE patient_id = ?");
            $stmt->execute([$_SESSION['patient_id']]);
            if ($stmt->rowCount() > 0) {
                // Update existing consultation
                $stmt = $pdo->prepare("UPDATE patient_consultation SET scheduled_at = ? WHERE patient_id = ?");
                $stmt->execute([$scheduled_at, $_SESSION['patient_id']]);
            } else {
                // Insert new consultation
                $stmt = $pdo->prepare("INSERT INTO patient_consultation (patient_id, scheduled_at) VALUES (?, ?)");
                $stmt->execute([$_SESSION['patient_id'], $scheduled_at]);
            }

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

            // Redirect to page10.php after successful submission
            header("Location: page10.php");
            exit();
        } catch (PDOException $e) {
            // Rollback transaction on error
            $pdo->rollBack();
            $errors[] = "Database Error: " . safe_htmlspecialchars($e->getMessage());
        }
    }
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Your Information</title>
    <link rel="stylesheet" href="../assets/css/styles.css">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
</head>
<body>
    <div class="container mt-5">
        <div class="header text-center mb-4">
            <img src="../assets/images/white-logo.png" alt="Longeva Logo" style="width:25%;">
            <h2>Your Information</h2>
        </div>

        <div class="section">
            <h3>Ensure Your Details Are Correct</h3>
            <p>Providing accurate information helps us serve you better.</p>

            <?php
            if (!empty($errors)) {
                echo '<div class="error alert alert-danger">';
                foreach($errors as $error){
                    echo "<p>" . safe_htmlspecialchars($error) . "</p>";
                }
                echo '</div>';
            }
            ?>

            <form method="POST" action="">
                <!-- Name -->
                <div class="form-group">
                    <label for="name">Name:<span class="text-danger">*</span></label>
                    <input type="text" id="name" name="name" class="form-control" value="<?php echo safe_htmlspecialchars($name); ?>" required>
                </div>

                <!-- Email -->
                <div class="form-group">
                    <label for="email">Email:<span class="text-danger">*</span></label>
                    <input type="email" id="email" name="email" class="form-control" value="<?php echo safe_htmlspecialchars($email); ?>" required>
                </div>

                <!-- Phone Number -->
                <div class="form-group">
                    <label for="phone">Phone Number:<span class="text-danger">*</span></label>
                    <input type="tel" id="phone" name="phone" class="form-control" value="<?php echo safe_htmlspecialchars($phone); ?>" required>
                </div>

                <!-- Gender -->
                <div class="form-group">
                    <label for="gender">Gender:<span class="text-danger">*</span></label>
                    <select id="gender" name="gender" class="form-control" required>
                        <option value="">--Select Gender--</option>
                        <option value="Male" <?php if($gender === 'Male') echo 'selected'; ?>>Male</option>
                        <option value="Female" <?php if($gender === 'Female') echo 'selected'; ?>>Female</option>
                        <option value="Other" <?php if($gender === 'Other') echo 'selected'; ?>>Other</option>
                    </select>
                </div>

                <!-- Age -->
                <div class="form-group">
                    <label for="age">Age:<span class="text-danger">*</span></label>
                    <input type="number" id="age" name="age" class="form-control" min="0" value="<?php echo safe_htmlspecialchars($age); ?>" required>
                </div>

                <!-- Weight -->
                <div class="form-group">
                    <label for="weight">Weight (kg):<span class="text-danger">*</span></label>
                    <input type="number" step="0.1" id="weight" name="weight" class="form-control" min="0" value="<?php echo safe_htmlspecialchars($weight); ?>" required>
                </div>

                <!-- Height -->
                <div class="form-group">
                    <label for="height">Height (cm):<span class="text-danger">*</span></label>
                    <input type="number" step="0.1" id="height" name="height" class="form-control" min="0" value="<?php echo safe_htmlspecialchars($height); ?>" required>
                </div>

                <!-- Address -->
                <div class="form-group">
                    <label for="address">Address:<span class="text-danger">*</span></label>
                    <textarea id="address" name="address" class="form-control" rows="3" required><?php echo safe_htmlspecialchars($address); ?></textarea>
                </div>

                <!-- Consultation Date and Time -->
                <div class="form-group">
                    <label for="scheduled_at">Select Consultation Date and Time:<span class="text-danger">*</span></label>
                    <input type="datetime-local" id="scheduled_at" name="scheduled_at" class="form-control" value="<?php echo safe_htmlspecialchars($scheduled_at); ?>" required>
                </div>

                <button type="submit" class="btn btn-primary">Save & Continue</button>
            </form>
        </div>
    </div>

    <!-- Optional: Include Bootstrap JS for better interactivity -->
    <script src="https://code.jquery.com/jquery-3.5.1.slim.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@4.5.2/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>) when user submit on the form, send email to his/her to tell them we reach you soon and this info of database (-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: Oct 19, 2024 at 08:09 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,
  `address` varchar(255) DEFAULT 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 */;) and use this codes to help you (<?php
session_start();
require_once '../db_connect.php';

// Include PHPMailer classes for sending emails
use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;

// Autoload PHPMailer classes (if using Composer)
require '../vendor/autoload.php'; // Adjust the path if necessary

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

// Get the appointment ID from GET parameters and validate it
if (!isset($_GET['id'])) {
    $_SESSION['error_message'] = "Appointment ID is missing.";
    header("Location: manage_appointments.php");
    exit();
}

$appointment_id = intval($_GET['id']); // Sanitize appointment ID

// Fetch appointment details including admin_notes and additional_notes
$stmt = $conn->prepare("
    SELECT a.*, p.package_name, p.description
    FROM appointments a
    LEFT JOIN packages p ON a.package_id = p.id
    WHERE a.id = ?
");
if ($stmt === false) {
    $_SESSION['error_message'] = "Failed to prepare the statement: " . htmlspecialchars($conn->error);
    header("Location: manage_appointments.php");
    exit();
}
$stmt->bind_param("i", $appointment_id);
$stmt->execute();
$result = $stmt->get_result();
if ($result->num_rows === 0) {
    $_SESSION['error_message'] = "Appointment not found.";
    header("Location: manage_appointments.php");
    exit();
}
$appointment = $result->fetch_assoc();
$stmt->close();

// Prepare variables for email
$name = $appointment['name'];
$email = $appointment['email'];
$package_name = $appointment['package_name'];
$appointment_date = $appointment['appointment_date']; // Patient's requested date
$status = $appointment['status'];
$admin_notes = $appointment['admin_notes']; // Admin's internal notes
$additional_notes = $appointment['additional_notes']; // Patient's additional notes
$proposed_appointment_date = $appointment['proposed_appointment_date']; // Admin's proposed date

// Determine the email subject and status message based on appointment status
$email_subject = '';
$status_message = '';

switch ($status) {
    case 'Pending':
        $email_subject = 'Appointment Pending Confirmation';
        $status_message = 'Your appointment request is currently pending. We will review it and get back to you shortly.';
        break;
    case 'Confirmed':
        $email_subject = 'Your Appointment is Confirmed';
        $status_message = 'Great news! Your appointment has been confirmed. We look forward to seeing you.';
        break;
    case 'Completed':
        $email_subject = 'Thank You for Your Appointment';
        $status_message = 'Thank you for attending your appointment. We hope you had a great experience.';
        break;
    case 'Cancelled':
        $email_subject = 'Your Appointment has been Cancelled';
        $status_message = 'We regret to inform you that your appointment has been cancelled. Please contact us for further assistance.';
        break;
    default:
        $email_subject = 'Appointment Update';
        $status_message = 'There is an update regarding your appointment. Please see the details below.';
        break;
}

try {
    // Create a new PHPMailer instance
    $mail = new PHPMailer(true);

    // SMTP configuration
    $mail->isSMTP();
    $mail->Host       = 'smtp.hostinger.com'; // Replace with your SMTP server
    $mail->SMTPAuth   = true;
    $mail->Username   = 'info@longevacare.com'; // SMTP username
    $mail->Password   = '50495049.Info';        // SMTP password
    $mail->SMTPSecure = PHPMailer::ENCRYPTION_SMTPS; // Use SSL encryption
    $mail->Port       = 465;                       // TCP port for SSL

    // Recipients
    $mail->setFrom('info@longevacare.com', 'Longeva Health Center');
    $mail->addAddress($email, $name); // Add a recipient

    // Content
    $mail->isHTML(true); // Set email format to HTML
    $mail->Subject = $email_subject;

    // Format the appointment dates
    $appointment_date_formatted = date('l, F j, Y \a\t g:i A', strtotime($appointment_date));
    $proposed_date_formatted = $proposed_appointment_date ? date('l, F j, Y \a\t g:i A', strtotime($proposed_appointment_date)) : null;

    // Construct the email body with enhanced styling
    $email_body = '
    <!DOCTYPE html>
    <html>
    <head>
        <title>' . htmlspecialchars($email_subject, ENT_QUOTES, 'UTF-8') . '</title>
        <style type="text/css">
            /* Email styles */
            body {
                margin: 0;
                padding: 0;
                font-family: Arial, sans-serif;
                background-color: #f7f7f7;
                color: #333333;
            }
            .email-container {
                background-color: #ffffff;
                margin: 0 auto;
                padding: 0;
                max-width: 600px;
            }
            .email-header {
                background-color: #4F3F02; /* Brown color */
                padding: 20px;
                text-align: center;
                color: #ffffff;
            }
            .email-header img {
                max-width: 150px;
                margin-bottom: 10px;
            }
            .email-header h1 {
                margin: 0;
                font-size: 24px;
                font-weight: normal;
            }
            .email-body {
                padding: 20px;
            }
            .email-body h2 {
                color: #4F3F02;
                margin-top: 0;
            }
            .email-body p {
                line-height: 1.6;
            }
            .appointment-details {
                margin-top: 20px;
                background-color: #f9f9f9;
                padding: 15px;
                border-radius: 5px;
            }
            .appointment-details h3 {
                margin-top: 0;
                color: #4F3F02;
            }
            .appointment-details p {
                margin: 5px 0;
            }
            .admin-notes {
                margin-top: 20px;
                padding: 15px;
                border-radius: 5px;
                background-color: #ffe6e6; /* Light red background */
            }
            .admin-notes h3 {
                margin-top: 0;
                color: #ff0000; /* Red color */
            }
            .admin-notes p {
                color: #ff0000; /* Red color */
                font-weight: bold;
            }
            .btn {
                display: inline-block;
                background-color: #D1C287; /* Light golden color */
                color: #4F3F02; /* Brown color */
                padding: 12px 20px;
                margin-top: 20px;
                text-decoration: none;
                border-radius: 5px;
                font-weight: bold;
            }
            .btn:hover {
                background-color: #4F3F02;
                color: #ffffff;
            }
            .email-footer {
                background-color: #f0f0f0;
                text-align: center;
                padding: 15px;
                font-size: 12px;
                color: #777777;
            }
            .email-footer p {
                margin: 5px 0;
            }
            .social-icons img {
                width: 24px;
                margin: 0 5px;
            }
            /* Responsive Design */
            @media only screen and (max-width: 600px) {
                .email-body, .appointment-details, .admin-notes {
                    padding: 15px;
                }
                .email-header h1 {
                    font-size: 20px;
                }
                .email-body h2 {
                    font-size: 18px;
                }
            }
        </style>
    </head>
    <body>
        <div class="email-container">
            <!-- Email Header -->
            <div class="email-header">
                <img src="https://longevacare.com/wp-content/uploads/2024/09/white-logo.png" alt="Longeva Logo">
                <h1>' . htmlspecialchars($email_subject, ENT_QUOTES, 'UTF-8') . '</h1>
            </div>
            <!-- Email Body -->
            <div class="email-body">
                <p>Dear ' . htmlspecialchars($name, ENT_QUOTES, 'UTF-8') . ',</p>
                
                <p>' . htmlspecialchars($status_message, ENT_QUOTES, 'UTF-8') . '</p>

                <!-- Appointment Details -->
                <div class="appointment-details">
                    <h3>Appointment Details</h3>
                    <p><strong>Appointment ID:</strong> ' . $appointment_id . '</p>
                    <p><strong>Package Selected:</strong> ' . htmlspecialchars($package_name, ENT_QUOTES, 'UTF-8') . '</p>
                    <p><strong>Your Requested Date & Time:</strong> ' . $appointment_date_formatted . '</p>';
    
    if ($proposed_date_formatted) {
        $email_body .= '<p><strong>Proposed Date & Time:</strong> ' . $proposed_date_formatted . '</p>';
    }
    
    $email_body .= '<p><strong>Status:</strong> ' . htmlspecialchars($status, ENT_QUOTES, 'UTF-8') . '</p>';
    
    if (!empty($additional_notes)) {
        $email_body .= '<p><strong>Additional Notes:</strong> ' . nl2br(htmlspecialchars($additional_notes, ENT_QUOTES, 'UTF-8')) . '</p>';
    }
    
    // Include admin_notes if available
    if (!empty($admin_notes)) {
        $email_body .= '
                <!-- Admin Notes -->
                <div class="admin-notes">
                    <h3>Notes:</h3>
                    <p>' . nl2br(htmlspecialchars($admin_notes, ENT_QUOTES, 'UTF-8')) . '</p>
                </div>';
    }
    
    $email_body .= '
                <!-- Additional Information -->
                <p>If you have any questions or need further assistance, feel free to contact us.</p>
    
                <p>Warm regards,<br>The Longeva Health Center Team</p>
    
                <a href="https://longevacare.com/contact" class="btn">Contact Us</a>
            </div>
            <!-- Email Footer -->
            <div class="email-footer">
                <p><strong>Longeva Health Center</strong></p>
                <p>Email: <a href="mailto:info@longevacare.com">info@longevacare.com</a></p>
                <p>Phone: +971 528 863 600</p>
                <p>WhatsApp: +1 (702) 526-2050</p>
                <p>Address: HDS Tower, Cluster M, JLT, Dubai</p>
            </div>
        </div>
    </body>
    </html>
    ';

    $mail->Body = $email_body;

    // For non-HTML email clients
    $plain_text_email = 'Dear ' . $name . ',
    
' . strip_tags($status_message) . '
    
Appointment Details:
Appointment ID: ' . $appointment_id . '
Package Selected: ' . $package_name . '
Your Requested Date & Time: ' . $appointment_date_formatted;
    
    if ($proposed_date_formatted) {
        $plain_text_email .= '
Proposed Date & Time: ' . $proposed_date_formatted;
    }
    
    $plain_text_email .= '
Status: ' . $status;
    
    if (!empty($additional_notes)) {
        $plain_text_email .= '
Additional Notes: ' . $additional_notes;
    }
    
    if (!empty($admin_notes)) {
        $plain_text_email .= '
Admin Notes: ' . $admin_notes;
    }
    
    $plain_text_email .= '
    
If you have any questions or need further assistance, feel free to contact us.

Warm regards,
The Longeva Health Center Team

Email: info@longevacare.com
Phone: +971 528 863 600
WhatsApp: +1 (702) 526-2050
Address: HDS Tower, Cluster M, JLT, Dubai
';

    $mail->AltBody = $plain_text_email;

    $mail->send();

    $_SESSION['success_message'] = 'Email sent successfully to ' . htmlspecialchars($email, ENT_QUOTES, 'UTF-8') . '.';
} catch (Exception $e) {
    $_SESSION['error_message'] = 'Message could not be sent. Mailer Error: ' . htmlspecialchars($mail->ErrorInfo);
}

// Redirect back to manage_appointments.php
header("Location: manage_appointments.php");
exit();
?>) and type full codes with guides