please fix this errror in sql (Error
SQL query: Copy


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;
MySQL said: Documentation

#1452 - Cannot add or update a child row: a foreign key constraint fails (`u406552082_info`.`#sql-alter-37fa6-175aa74`, CONSTRAINT `fk_patient_basic_info_patient` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
Open new phpMyAdmin window) and alrealdy have manage_appointments.php (<?php
session_start();
require_once '../db_connect.php';

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

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

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


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



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

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

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

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

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

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

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

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

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

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

        h1, h2 {
            font-weight: bold;
        }

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

        .summary h3 {
            margin-top: 0;
        }

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

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

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

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

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

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

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

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

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

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

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

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

// includes/footer.php

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

?>) so i don't wnat to chnage it and i won't show errer like this (Database Error: SQLSTATE[42000]: Syntax error or access violation: 1110 Column &#039;status&#039; specified twice

) and in add_appointment Appointment Date & Time take it from table don't tell me add it, just tell me Proposed Appointment Date & Time: to add and in select Patient make it advanced and easy to use because if there are many more patients so fix all and type full codes with guides 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 17, 2024 at 03:42 AM
-- Server version: 10.11.9-MariaDB
-- PHP Version: 7.2.34

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `u406552082_info`
--

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

--
-- Table structure for table `admins`
--

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

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

--
-- Table structure for table `appointments`
--

CREATE TABLE `appointments` (
  `id` int(11) NOT NULL,
  `package_id` int(11) NOT NULL,
  `appointment_date` datetime NOT NULL,
  `proposed_appointment_date` datetime DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `age` int(3) DEFAULT NULL,
  `gender` enum('Male','Female','Other') DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `preferred_contact_method` enum('Email','Phone','WhatsApp') DEFAULT NULL,
  `additional_notes` text DEFAULT NULL,
  `admin_notes` text DEFAULT NULL,
  `status` enum('Pending','Confirmed','Completed','Cancelled') DEFAULT 'Pending',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `patient_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `budgets`
--

CREATE TABLE `budgets` (
  `id` int(11) NOT NULL,
  `month` varchar(7) DEFAULT NULL,
  `budgeted_revenue` decimal(10,2) DEFAULT NULL,
  `budgeted_expenses` decimal(10,2) DEFAULT NULL,
  `notes` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `comments`
--

CREATE TABLE `comments` (
  `id` int(11) NOT NULL,
  `patient_id` int(11) NOT NULL,
  `sender` enum('patient','doctor','admin') NOT NULL,
  `message` text NOT NULL,
  `timestamp` datetime DEFAULT current_timestamp(),
  `parent_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `doctors`
--

CREATE TABLE `doctors` (
  `id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `username` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `specialization` varchar(255) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `registration_date` date DEFAULT curdate(),
  `profile_picture` varchar(255) DEFAULT NULL,
  `department` varchar(100) DEFAULT NULL,
  `date_of_joining` date DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `doctor_patient`
--

CREATE TABLE `doctor_patient` (
  `id` int(11) NOT NULL,
  `doctor_id` int(11) NOT NULL,
  `patient_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `employees`
--

CREATE TABLE `employees` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `role` enum('admin','doctor','staff','accountant') NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `department` varchar(100) DEFAULT NULL,
  `date_of_joining` date DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `username` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `profile_picture` varchar(255) DEFAULT NULL,
  `specialization` varchar(255) DEFAULT NULL,
  `is_online` tinyint(1) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `expenses`
--

CREATE TABLE `expenses` (
  `id` int(11) NOT NULL,
  `expense_date` date NOT NULL,
  `category` varchar(100) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `amount` decimal(10,2) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `health_records`
--

CREATE TABLE `health_records` (
  `id` int(11) NOT NULL,
  `patient_id` int(11) NOT NULL,
  `blood_pressure` varchar(20) DEFAULT NULL,
  `weight` decimal(5,2) DEFAULT NULL,
  `bmi` decimal(4,2) DEFAULT NULL,
  `cholesterol` decimal(5,2) DEFAULT NULL,
  `prescription` text DEFAULT NULL,
  `treatment_progress` text DEFAULT NULL,
  `record_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `insurance_claims`
--

CREATE TABLE `insurance_claims` (
  `id` int(11) NOT NULL,
  `patient_id` int(11) DEFAULT NULL,
  `invoice_id` int(11) DEFAULT NULL,
  `claim_amount` decimal(10,2) DEFAULT NULL,
  `status` enum('Pending','Approved','Rejected') DEFAULT NULL,
  `submission_date` date DEFAULT NULL,
  `processing_date` date DEFAULT NULL,
  `notes` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `invoices`
--

CREATE TABLE `invoices` (
  `id` int(11) NOT NULL,
  `patient_id` int(11) NOT NULL,
  `service_name` varchar(255) NOT NULL,
  `service_description` text DEFAULT NULL,
  `cost` decimal(10,2) NOT NULL,
  `tax_rate` decimal(5,2) DEFAULT 0.00,
  `discount` decimal(5,2) DEFAULT 0.00,
  `total_amount` decimal(10,2) NOT NULL,
  `invoice_date` date DEFAULT curdate(),
  `due_date` date DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `tax` decimal(5,2) DEFAULT 0.00
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `invoice_services`
--

CREATE TABLE `invoice_services` (
  `id` int(11) NOT NULL,
  `invoice_id` int(11) NOT NULL,
  `service_id` int(11) NOT NULL,
  `service_name` varchar(255) NOT NULL,
  `cost` decimal(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `packages`
--

CREATE TABLE `packages` (
  `id` int(11) NOT NULL,
  `package_name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `details` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `patients`
--

CREATE TABLE `patients` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `age` int(3) DEFAULT NULL,
  `gender` enum('Male','Female','Other') DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `medical_history` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `date_of_birth` date DEFAULT NULL,
  `registration_datetime` datetime DEFAULT current_timestamp(),
  `registration_date` date DEFAULT NULL,
  `status` enum('Initial Consultation','Diagnostic Phase','Treatment in Progress','Improving','Monitoring','Maintenance Phase','Awaiting Test Results','Follow-up Needed','Paused','Completed Program','Relapsed','Discontinued','Inactive') DEFAULT 'Initial Consultation',
  `doctor_id` int(11) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `patient_basic_info`
--

CREATE TABLE `patient_basic_info` (
  `patient_id` int(11) NOT NULL,
  `gender` enum('Male','Female','Other') NOT NULL,
  `age` int(3) NOT NULL,
  `weight` decimal(5,2) NOT NULL,
  `height` decimal(5,2) NOT NULL,
  `name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

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

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

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

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

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

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

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

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

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

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

CREATE TABLE `patient_health_metrics` (
  `patient_id` int(11) NOT NULL,
  `exercise_frequency` enum('Daily','Weekly','Rarely','Never') NOT NULL,
  `diet` enum('Yes','No') NOT NULL,
  `diet_specify` varchar(255) DEFAULT NULL,
  `sleep_average` enum('<5 Hours','5-7 Hours','7-9 Hours','>9 Hours') NOT NULL,
  `smoking_status` enum('Yes','No','Occasionally') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

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

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

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

--
-- Table structure for table `patient_medical_history`
--

CREATE TABLE `patient_medical_history` (
  `patient_id` int(11) NOT NULL,
  `chronic_conditions` enum('Yes','No') NOT NULL,
  `chronic_conditions_specify` varchar(255) DEFAULT NULL,
  `medications` enum('Yes','No') NOT NULL,
  `medications_list` text DEFAULT NULL,
  `major_surgeries` enum('Yes','No') NOT NULL,
  `major_surgeries_specify` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

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

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

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

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

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

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

--
-- Table structure for table `questionnaire_responses`
--

CREATE TABLE `questionnaire_responses` (
  `id` int(11) NOT NULL,
  `session_id` varchar(255) NOT NULL,
  `page_number` int(2) NOT NULL,
  `question` varchar(255) NOT NULL,
  `answer` text NOT NULL,
  `submitted_at` timestamp NULL DEFAULT current_timestamp(),
  `consent` enum('Yes','No') DEFAULT 'No'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

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

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

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

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

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

--
-- Indexes for dumped tables
--

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

--
-- Indexes for table `appointments`
--
ALTER TABLE `appointments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `package_id` (`package_id`),
  ADD KEY `appointments_ibfk_2` (`patient_id`);

--
-- Indexes for table `budgets`
--
ALTER TABLE `budgets`
  ADD PRIMARY KEY (`id`);

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

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

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

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

--
-- Indexes for table `expenses`
--
ALTER TABLE `expenses`
  ADD PRIMARY KEY (`id`);

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

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

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

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

--
-- Indexes for table `packages`
--
ALTER TABLE `packages`
  ADD PRIMARY KEY (`id`);

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

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

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

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

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

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

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

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

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

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

--
-- Indexes for table `questionnaire_responses`
--
ALTER TABLE `questionnaire_responses`
  ADD PRIMARY KEY (`id`);

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

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

--
-- AUTO_INCREMENT for dumped tables
--

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

--
-- AUTO_INCREMENT for table `services`
--
ALTER TABLE `services`
  MODIFY `service_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `appointments`
--
ALTER TABLE `appointments`
  ADD CONSTRAINT `appointments_ibfk_1` FOREIGN KEY (`package_id`) REFERENCES `packages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `appointments_ibfk_2` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

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

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

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

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

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

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

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

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

--
-- Constraints for table `patient_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 this page add appoinmtent (<?php
// appointment/add_appointment.php

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

// Include the header
include '../includes/header.php';
?>
<!DOCTYPE html>
<html>
<head>
    <title>Add Appointment - Longeva</title>
    <!-- Include Bootstrap CSS and any other required CSS -->
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css"/>
    <!-- Custom CSS -->
    <style>
        /* Custom styles */
        body {
            background-color: #f8f9fa;
            font-family: 'Roboto', sans-serif;
        }
        .container {
            max-width: 800px;
            margin-top: 50px;
            background-color: #ffffff;
            padding: 30px 40px;
            border-radius: 10px;
            box-shadow: 0 4px 15px rgba(0, 0, 0, 0.1);
        }
        h2 {
            text-align: center;
            margin-bottom: 30px;
            color: #4F3F02;
        }
        .btn-primary, .btn-secondary {
            border-radius: 50px;
            padding: 10px 20px;
            margin: 5px;
        }
        .btn-primary {
            background-color: #D1C287;
            border-color: #D1C287;
            color: #4F3F02;
        }
        .btn-primary:hover {
            background-color: #4F3F02;
            color: #ffffff;
        }
        .btn-secondary {
            background-color: #4F3F02;
            border-color: #4F3F02;
            color: #ffffff;
        }
        .btn-secondary:hover {
            background-color: #D1C287;
            color: #4F3F02;
        }
        .form-section {
            margin-bottom: 30px;
        }
        .form-section h4 {
            color: #4F3F02;
            margin-bottom: 20px;
            border-bottom: 2px solid #D1C287;
            padding-bottom: 10px;
        }
        .alert {
            margin-top: 20px;
            border-radius: 8px;
        }
    </style>
</head>
<body>
    <div class="container">
        <h2>Add New Appointment</h2>
        <?php
        if (!empty($errors)) {
            echo '<div class="alert alert-danger">';
            foreach ($errors as $error) {
                echo "<p>" . htmlspecialchars($error) . "</p>";
            }
            echo '</div>';
        }

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

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

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

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

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

    <!-- Include Bootstrap JS and dependencies -->
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.1/dist/umd/popper.min.js"></script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>
</body>
</html>
<?php
// Include the footer
include '../includes/footer.php';
?>)