based on this info database (-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: Oct 10, 2024 at 04:23 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,
  `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,
  `status` enum('Pending','Confirmed','Completed','Cancelled') DEFAULT 'Pending',
  `created_at` timestamp NULL DEFAULT current_timestamp()
) 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(11) NOT NULL,
  `gender` enum('Male','Female','Other') NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `medical_history` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `date_of_birth` date DEFAULT NULL,
  `registration_datetime` datetime DEFAULT current_timestamp(),
  `registration_date` date DEFAULT NULL,
  `status` enum('Initial Consultation','Diagnostic Phase','Treatment in Progress','Improving','Monitoring','Maintenance Phase','Awaiting Test Results','Follow-up Needed','Paused','Completed Program','Relapsed','Discontinued','Inactive') DEFAULT 'Initial Consultation',
  `doctor_id` int(11) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

--
-- Indexes for dumped tables
--

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

--
-- Indexes for table `appointments`
--
ALTER TABLE `appointments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `package_id` (`package_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_logins`
--
ALTER TABLE `patient_logins`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `patient_id` (`patient_id`),
  ADD UNIQUE KEY `username` (`username`);

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

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

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

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

--
-- AUTO_INCREMENT for dumped tables
--

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

--
-- AUTO_INCREMENT for table `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 `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;

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

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

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

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

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

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

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

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

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

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

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

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

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;), i want to make this codes repsoinive design for all devices (<?php
// Include database connection
require_once '../db_connect.php'; // Adjust the path if necessary

// Include PHPMailer classes
use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;

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

// Verify that $conn is defined
if (!isset($conn)) {
    die("Database connection not established. Please check db_connect.php.");
}

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

// Fetch packages for the form and JavaScript
$packages = [];

// Fetch packages from the database
$packageQuery = "SELECT id, package_name, description FROM packages";
$packageResult = $conn->query($packageQuery);

// Check for query error
if ($packageResult) {
    while ($row = $packageResult->fetch_assoc()) {
        $packages[] = $row;
    }
} else {
    $errors[] = "Failed to fetch packages: " . $conn->error;
}

// Handle form submission
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    // Collect input data
    $name = trim($_POST['name']);
    $email = trim($_POST['email']);
    $phone = trim($_POST['phone']);
    $age = intval($_POST['age']);
    $gender = $_POST['gender'];
    $address = trim($_POST['address']);
    $preferred_contact_method = $_POST['preferred_contact_method'];
    $package_id = $_POST['package_id'];
    $appointment_date = $_POST['appointment_date'];
    $additional_notes = trim($_POST['additional_notes']);

    // Validate inputs
    if (empty($name)) {
        $errors[] = "Please provide your full name.";
    }
    if (empty($email)) {
        $errors[] = "Please provide your email address.";
    }
    if (empty($phone)) {
        $errors[] = "Please provide your phone number.";
    }
    if (empty($age) || $age <= 0) {
        $errors[] = "Please provide a valid age.";
    }
    if (empty($gender)) {
        $errors[] = "Please select your gender.";
    }
    if (empty($address)) {
        $errors[] = "Please provide your address.";
    }
    if (empty($preferred_contact_method)) {
        $errors[] = "Please select your preferred contact method.";
    }
    if (empty($appointment_date)) {
        $errors[] = "Please select a preferred appointment date and time.";
    }
    if (empty($package_id)) {
        $errors[] = "Please select a package.";
    }

    // Ensure the appointment date is in the future
    $appointment_datetime = new DateTime($appointment_date);
    $current_datetime = new DateTime();
    if ($appointment_datetime <= $current_datetime) {
        $errors[] = "Please select a future date and time for your appointment.";
    }

    // Check for overlapping appointments (availability)
    if (empty($errors)) {
        // Appointment duration in minutes
        $duration = 60;
        $appointment_end = clone $appointment_datetime;
        $appointment_end->modify('+' . $duration . ' minutes');

        // Prepare and execute the availability query
        $availabilityQuery = "
            SELECT COUNT(*) as count FROM appointments
            WHERE appointment_date < ? AND DATE_ADD(appointment_date, INTERVAL ? MINUTE) > ?
        ";
        $stmt = $conn->prepare($availabilityQuery);
        if ($stmt) {
            $appointment_end_str = $appointment_end->format('Y-m-d H:i:s');
            $appointment_start_str = $appointment_datetime->format('Y-m-d H:i:s');
            $stmt->bind_param("sis", $appointment_end_str, $duration, $appointment_start_str);
            $stmt->execute();
            $stmt->bind_result($count);
            $stmt->fetch();
            $stmt->close();

            if ($count > 0) {
                $errors[] = "The selected time slot is not available. Please choose a different time.";
            }
        } else {
            $errors[] = "Database error during availability check: " . $conn->error;
        }
    }

    if (empty($errors)) {
        // Insert appointment
        $insertQuery = "INSERT INTO appointments
            (package_id, appointment_date, name, email, phone, age, gender, address, preferred_contact_method, additional_notes)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        $stmt = $conn->prepare($insertQuery);
        if ($stmt) {
            $appointment_date_str = $appointment_datetime->format('Y-m-d H:i:s');
            $stmt->bind_param(
                "isssisssss",
                $package_id,
                $appointment_date_str,
                $name,
                $email,
                $phone,
                $age,
                $gender,
                $address,
                $preferred_contact_method,
                $additional_notes
            );
            if ($stmt->execute()) {
                // Retrieve the last inserted appointment ID
                $appointment_id = $conn->insert_id;
                $success = "Appointment requested successfully! We will contact you soon to confirm the details.";

                // Fetch selected package name for email
                $stmt_pkg = $conn->prepare("SELECT package_name FROM packages WHERE id = ?");
                if ($stmt_pkg) {
                    $stmt_pkg->bind_param("i", $package_id);
                    $stmt_pkg->execute();
                    $stmt_pkg->bind_result($package_name);
                    $stmt_pkg->fetch();
                    $stmt_pkg->close();

                    // Send confirmation email using PHPMailer
                    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 (ensure you secure this)
                        $mail->SMTPSecure = PHPMailer::ENCRYPTION_SMTPS; // Use SSL encryption
                        $mail->Port       = 465;                       // TCP port

                        // Recipients
                        $mail->setFrom('info@longevacare.com', 'Longeva Health Center');
                        $mail->addAddress($email, $name); // Patient's email and name

                        // Content
                        $mail->isHTML(true);
                        $mail->Subject = 'Appointment Request Received';

                        // Compose the email body with enhanced design
                        $email_body = '
                        <!DOCTYPE html>
                        <html>
                        <head>
                            <title>Appointment Request Received</title>
                            <style type="text/css">
                                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;
                                }
                                .personal-details, .appointment-details, .additional-notes {
                                    margin-top: 20px;
                                    background-color: #f9f9f9;
                                    padding: 15px;
                                    border-radius: 5px;
                                }
                                .personal-details h3, .appointment-details h3, .additional-notes h3 {
                                    margin-top: 0;
                                    color: #4F3F02;
                                }
                                .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, .personal-details, .appointment-details, .additional-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>Appointment Request Received</h1>
                                </div>
                                <!-- Email Body -->
                                <div class="email-body">
                                    <p>Dear ' . htmlspecialchars($name, ENT_QUOTES, 'UTF-8') . ',</p>
                                    
                                    <p>Thank you for requesting an appointment with Longeva Health Center. We have received your request with the following details:</p>

                                    <!-- Personal Details -->
                                    <div class="personal-details">
                                        <h3>Your Details</h3>
                                        <p><strong>Name:</strong> ' . htmlspecialchars($name, ENT_QUOTES, 'UTF-8') . '</p>
                                        <p><strong>Email:</strong> ' . htmlspecialchars($email, ENT_QUOTES, 'UTF-8') . '</p>
                                        <p><strong>Phone:</strong> ' . htmlspecialchars($phone, ENT_QUOTES, 'UTF-8') . '</p>
                                        <p><strong>Age:</strong> ' . htmlspecialchars($age, ENT_QUOTES, 'UTF-8') . '</p>
                                        <p><strong>Gender:</strong> ' . htmlspecialchars($gender, ENT_QUOTES, 'UTF-8') . '</p>
                                        <p><strong>Address:</strong> ' . htmlspecialchars($address, ENT_QUOTES, 'UTF-8') . '</p>
                                        <p><strong>Preferred Contact Method:</strong> ' . htmlspecialchars($preferred_contact_method, ENT_QUOTES, 'UTF-8') . '</p>
                                    </div>

                                    <!-- Appointment Details -->
                                    <div class="appointment-details">
                                        <h3>Appointment Details</h3>
                                        <p><strong>Appointment ID:</strong> ' . htmlspecialchars($appointment_id, ENT_QUOTES, 'UTF-8') . '</p>
                                        <p><strong>Package Selected:</strong> ' . htmlspecialchars($package_name, ENT_QUOTES, 'UTF-8') . '</p>
                                        <p><strong>Preferred Date & Time:</strong> ' . date('l, F j, Y \a\t g:i A', strtotime($appointment_date_str)) . '</p>
                                    </div>

                                    <!-- Additional Notes -->
                                    ' . (!empty($additional_notes) ? '
                                    <div class="additional-notes">
                                        <h3>Additional Notes</h3>
                                        <p>' . nl2br(htmlspecialchars($additional_notes, ENT_QUOTES, 'UTF-8')) . '</p>
                                    </div>
                                    ' : '') . '

                                    <p>We will review your request and contact you soon to confirm the details.</p>

                                    <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 class="social-icons">
                                        <!-- Social Media Icons (Optional) -->
                                        <!-- Add your social media icons and links here -->
                                    </div>
                                </div>
                            </div>
                        </body>
                        </html>
                        ';

                        $mail->Body = $email_body;

                        // For non-HTML email clients
                        $mail->AltBody = 'Dear ' . $name . ',

Thank you for requesting an appointment with Longeva Health Center. We have received your request with the following details:

Your Details:
Name: ' . $name . '
Email: ' . $email . '
Phone: ' . $phone . '
Age: ' . $age . '
Gender: ' . $gender . '
Address: ' . $address . '
Preferred Contact Method: ' . $preferred_contact_method . '

Appointment Details:
Appointment ID: ' . $conn->insert_id . '
Package Selected: ' . $package_name . '
Preferred Date & Time: ' . date('l, F j, Y \a\t g:i A', strtotime($appointment_date_str)) . '

' . (!empty($additional_notes) ? '
Additional Notes:
' . $additional_notes . '
' : '') . '

We will review your request and contact you soon to confirm the details.

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->send();

                    } catch (Exception $e) {
                        $errors[] = 'Message could not be sent. Mailer Error: ' . $mail->ErrorInfo;
                    }

                } else {
                    $errors[] = "Failed to fetch package details for email: " . $conn->error;
                }
            } else {
                $errors[] = "Error scheduling appointment: " . $stmt->error;
            }
            $stmt->close();
        } else {
            $errors[] = "Database error during appointment scheduling: " . $conn->error;
        }
    }
}
?>

<!DOCTYPE html>
<html>
<head>
    <title>Request Appointment - Longeva</title>
    <!-- Include Bootstrap CSS and optional theme -->
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
    <!-- Custom Styles -->
    <style>
        body {
            background-color: #121212; /* Dark background */
            color: #fff;
            font-family: 'Open Sans', sans-serif;
        }
        .container {
            margin-top: 50px;
            margin-bottom: 50px;
        }
        .card {
            background-color: #1e1e1e;
            border: none;
            border-radius: 8px;
        }
        .card-header {
            background-color: #1e1e1e;
            border-bottom: none;
            text-align: center;
            padding-top: 30px;
        }
        .card-header h2 {
            color: #D1C287; /* Gold color */
            font-weight: bold;
            text-transform: uppercase;
        }
        .form-control {
            background-color: #2e2e2e;
            border: 1px solid #444;
            color: #fff;
        }
        .form-control:focus {
            background-color: #2e2e2e;
            border-color: #D1C287;
            color: #fff;
        }
        .btn-gold {
            background-color: #D1C287;
            color: #000;
            border: none;
            font-weight: bold;
            text-transform: uppercase;
        }
        .btn-gold:hover {
            background-color: #b8a461;
            color: #000;
        }
        .package-details {
            padding: 15px;
            background-color: #2e2e2e;
            color: #fff;
            border-radius: 5px;
            margin-bottom: 20px;
            display: none;
        }
        .footer {
            background-color: #1e1e1e;
            color: #fff;
            padding: 20px;
            text-align: center;
        }
        .footer a {
            color: #D1C287;
            text-decoration: none;
        }
        .footer a:hover {
            text-decoration: underline;
        }
        .alert {
            background-color: #ff4d4d;
            color: #fff;
            border: none;
        }
        .modal-content {
            background-color: #1e1e1e;
            color: #fff;
            border: none;
        }
        .modal-header {
            border-bottom: none;
        }
        .modal-footer {
            border-top: none;
        }
        .close {
            color: #fff;
            opacity: 1;
        }
        .close:hover {
            color: #D1C287;
        }
        .contact-info {
            margin-top: 30px;
            text-align: center;
        }
        .contact-info p {
            margin-bottom: 5px;
        }
        .contact-info i {
            color: #D1C287;
            margin-right: 5px;
        }
    </style>
</head>
<body>
    <div class="container">
        <!-- Card -->
        <div class="card shadow">
            <div class="card-header">
                <h2>Request an Appointment</h2>
            </div>
            <div class="card-body">
                <?php
                if (!empty($success)) {
                    // Success Modal
                    echo "
                    <div class='modal' tabindex='-1' role='dialog' id='successModal'>
                      <div class='modal-dialog' role='document'>
                        <div class='modal-content'>
                          <div class='modal-header'>
                            <h5 class='modal-title'>Appointment Requested</h5>
                            <button type='button' class='close' data-dismiss='modal' aria-label='Close' onclick=\"window.location.href='public_appointment.php';\">
                              <span aria-hidden='true'>&times;</span>
                            </button>
                          </div>
                          <div class='modal-body'>
                            <p>$success</p>
                          </div>
                          <div class='modal-footer'>
                            <button type='button' class='btn btn-gold' onclick=\"window.location.href='public_appointment.php';\">Close</button>
                          </div>
                        </div>
                      </div>
                    </div>
                    ";
                }
                if (!empty($errors)) {
                    foreach ($errors as $error) {
                        echo "<div class='alert alert-danger'>$error</div>";
                    }
                }
                ?>
                <form method="POST" action="">
                    <div class="form-group">
                        <label for="name">Full Name</label>
                        <input type="text" name="name" class="form-control" placeholder="Enter your full name" required/>
                    </div>
                    <div class="form-group">
                        <label for="email">Email Address</label>
                        <input type="email" name="email" class="form-control" placeholder="Enter your email address" required/>
                    </div>
                    <div class="form-group">
                        <label for="phone">Phone Number</label>
                        <input type="text" name="phone" class="form-control" placeholder="Enter your phone number" required/>
                    </div>
                    <div class="form-group">
                        <label for="age">Age</label>
                        <input type="number" name="age" min="0" class="form-control" placeholder="Enter your age" required/>
                    </div>
                    <div class="form-group">
                        <label for="gender">Gender</label>
                        <select name="gender" class="form-control" required>
                            <option value="">Select Gender</option>
                            <option value="Male">Male</option>
                            <option value="Female">Female</option>
                            <option value="Other">Other</option>
                        </select>
                    </div>
                    <div class="form-group">
                        <label for="address">Address</label>
                        <input type="text" name="address" class="form-control" placeholder="Enter your address" required/>
                    </div>
                    <div class="form-group">
                        <label for="preferred_contact_method">Preferred Contact Method</label>
                        <select name="preferred_contact_method" class="form-control" required>
                            <option value="">Select Preferred Contact Method</option>
                            <option value="Email">Email</option>
                            <option value="Phone">Phone</option>
                            <option value="WhatsApp">WhatsApp</option>
                        </select>
                    </div>
                    <div class="form-group">
                        <label for="package_id">Select Package</label>
                        <select name="package_id" id="package_id" class="form-control" onchange="showPackageDetails()" required>
                            <option value="">Select Package</option>
                            <?php
                            foreach ($packages as $pkg) {
                                echo "<option value='" . $pkg['id'] . "'>" . htmlspecialchars($pkg['package_name']) . "</option>";
                            }
                            ?>
                        </select>
                    </div>
                    <div id="package_details" class="package-details">
                        <h5>Package Details</h5>
                        <p id="package_description"></p>
                    </div>
                    <div class="form-group">
                        <label for="appointment_date">Preferred Appointment Date & Time</label>
                        <input type="datetime-local" name="appointment_date" class="form-control" required/>
                    </div>
                    <div class="form-group">
                        <label for="additional_notes">Additional Notes</label>
                        <textarea name="additional_notes" class="form-control" rows="4" placeholder="Any additional information or special requests"></textarea>
                    </div>
                    <button type="submit" class="btn btn-gold btn-block">Request Appointment</button>
                </form>
            </div>
        </div>

        <!-- Contact Info -->
        <div class="contact-info">
            <p><i class="fas fa-envelope"></i> Email: info@longevacare.com</p>
            <p><i class="fas fa-phone"></i> Phone: +971 528 863 600</p>
            <p><i class="fab fa-whatsapp"></i> WhatsApp: +1 (702) 526-2050</p>
            <p><i class="fas fa-map-marker-alt"></i> Location: HDS Tower, Cluster M, JLT, Dubai</p>
        </div>
    </div>

    <!-- Footer -->
    <div class="footer">
        &copy; <?php echo date('Y'); ?> LONGEVA. All rights reserved.
    </div>

    <!-- Include Bootstrap JS and jQuery -->
    <script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@4.5.2/dist/js/bootstrap.bundle.min.js"></script>

    <script>
        function showPackageDetails() {
            var packageSelect = document.getElementById('package_id');
            var packageId = packageSelect.value;
            var packageDetailsDiv = document.getElementById('package_details');
            var packageDescriptionP = document.getElementById('package_description');

            if (packageId) {
                var packages = <?php echo json_encode($packages); ?>;
                var selectedPackage = packages.find(function(pkg) { return pkg.id == packageId; });
                if (selectedPackage) {
                    packageDescriptionP.innerText = selectedPackage.description;
                    packageDetailsDiv.style.display = 'block';
                } else {
                    packageDetailsDiv.style.display = 'none';
                }
            } else {
                packageDetailsDiv.style.display = 'none';
            }
        }

        $(document).ready(function() {
            // Show the success modal if success message is set
            <?php if (!empty($success)) { ?>
                $('#successModal').modal('show');
            <?php } ?>

            // Initialize tooltips (optional)
            $('[data-toggle="tooltip"]').tooltip();
        });
    </script>
</body>
</html>) and type full codes