based on this database info (-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: Oct 26, 2024 at 01:03 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 `medical_notes`
--

CREATE TABLE `medical_notes` (
  `id` int(11) NOT NULL,
  `patient_id` int(11) NOT NULL,
  `doctor_id` int(11) NOT NULL,
  `note` text 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 `packages`
--

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

--
-- Indexes for dumped tables
--

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

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

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

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

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

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

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

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

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

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

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

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

--
-- Indexes for table `medical_notes`
--
ALTER TABLE `medical_notes`
  ADD PRIMARY KEY (`id`),
  ADD KEY `patient_id` (`patient_id`),
  ADD KEY `doctor_id` (`doctor_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 `medical_notes`
--
ALTER TABLE `medical_notes`
  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 `medical_notes`
--
ALTER TABLE `medical_notes`
  ADD CONSTRAINT `medical_notes_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `medical_notes_ibfk_2` FOREIGN KEY (`doctor_id`) REFERENCES `doctors` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

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

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

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

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

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

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

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;) i want you to create report folder, this folder has php files, this do anaylsis everytihing about reports and anaylsis patients and make the advances anaylusis and create file this file upload file like xlxss or csv to analysis this data and this report/admin_report.php (<?php
// admin_report.php

// Start the session and include necessary files
session_start();

// Include the header (should contain HTML head elements and navigation)
include '../includes/header.php';

// Check if the admin is logged in
if (!isset($_SESSION['admin_id'])) {
    // Not logged in, redirect to login page
    header('Location: admin_login.php');
    exit();
}

// Include the database connection
require_once '../db_connect.php';

// Functions to fetch data for reports

/**
 * Get total number of patients.
 *
 * @param PDO $pdo
 * @return int
 */
function getTotalPatients($pdo) {
    $stmt = $pdo->query("SELECT COUNT(*) as count FROM patients");
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    return $row['count'] ?? 0;
}

/**
 * Get total number of active doctors.
 *
 * @param PDO $pdo
 * @return int
 */
function getTotalDoctors($pdo) {
    $stmt = $pdo->query("SELECT COUNT(*) as count FROM doctors WHERE status = 'active'");
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    return $row['count'] ?? 0;
}

/**
 * Get total revenue from invoices.
 *
 * @param PDO $pdo
 * @return float
 */
function getTotalRevenue($pdo) {
    $stmt = $pdo->query("SELECT SUM(total_amount) as total FROM invoices");
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    return $row['total'] !== null ? (float)$row['total'] : 0.0;
}

/**
 * Get total expenses.
 *
 * @param PDO $pdo
 * @return float
 */
function getTotalExpenses($pdo) {
    $stmt = $pdo->query("SELECT SUM(amount) as total FROM expenses");
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    return $row['total'] !== null ? (float)$row['total'] : 0.0;
}

/**
 * Get total number of appointments.
 *
 * @param PDO $pdo
 * @return int
 */
function getTotalAppointments($pdo) {
    $stmt = $pdo->query("SELECT COUNT(*) as count FROM appointments");
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    return $row['count'] ?? 0;
}

/**
 * Get total number of prescriptions.
 *
 * @param PDO $pdo
 * @return int
 */
function getTotalPrescriptions($pdo) {
    $stmt = $pdo->query("SELECT COUNT(*) as count FROM prescriptions");
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    return $row['count'] ?? 0;
}

/**
 * Get gender distribution of patients.
 *
 * @param PDO $pdo
 * @return array
 */
function getGenderDistribution($pdo) {
    $stmt = $pdo->query("SELECT gender, COUNT(*) as count FROM patients GROUP BY gender");
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

/**
 * Get age distribution of patients.
 *
 * @param PDO $pdo
 * @return array
 */
function getAgeDistribution($pdo) {
    $stmt = $pdo->query("SELECT age FROM patients WHERE age IS NOT NULL");
    $ages = $stmt->fetchAll(PDO::FETCH_COLUMN, 0);
    return $ages;
}

/**
 * Get status distribution of patients.
 *
 * @param PDO $pdo
 * @return array
 */
function getStatusDistribution($pdo) {
    $stmt = $pdo->query("SELECT status, COUNT(*) as count FROM patients GROUP BY status");
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

/**
 * Get services rendered distribution.
 *
 * @param PDO $pdo
 * @return array
 */
function getServicesDistribution($pdo) {
    $stmt = $pdo->query("
        SELECT s.service_name, COUNT(*) as count
        FROM invoice_services isv
        INNER JOIN services s ON isv.service_id = s.service_id
        GROUP BY s.service_name
    ");
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

/**
 * Get revenue trend over time (monthly).
 *
 * @param PDO $pdo
 * @return array
 */
function getRevenueTrend($pdo) {
    $stmt = $pdo->query("
        SELECT DATE_FORMAT(invoice_date, '%Y-%m') as month, SUM(total_amount) as total
        FROM invoices
        GROUP BY month
        ORDER BY month ASC
    ");
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

/**
 * Get expenses trend over time (monthly).
 *
 * @param PDO $pdo
 * @return array
 */
function getExpensesTrend($pdo) {
    $stmt = $pdo->query("
        SELECT DATE_FORMAT(expense_date, '%Y-%m') as month, SUM(amount) as total
        FROM expenses
        GROUP BY month
        ORDER BY month ASC
    ");
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

/**
 * Get appointments trend over time (monthly).
 *
 * @param PDO $pdo
 * @return array
 */
function getAppointmentsTrend($pdo) {
    $stmt = $pdo->query("
        SELECT DATE_FORMAT(appointment_date, '%Y-%m') as month, COUNT(*) as count
        FROM appointments
        GROUP BY month
        ORDER BY month ASC
    ");
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

/**
 * Get top 5 doctors by number of patients.
 *
 * @param PDO $pdo
 * @return array
 */
function getTopDoctors($pdo) {
    $stmt = $pdo->query("
        SELECT d.name, COUNT(dp.patient_id) as patient_count
        FROM doctors d
        INNER JOIN doctor_patient dp ON d.id = dp.doctor_id
        GROUP BY d.id
        ORDER BY patient_count DESC
        LIMIT 5
    ");
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

/**
 * Get insurance claims status distribution.
 *
 * @param PDO $pdo
 * @return array
 */
function getInsuranceClaimsStatus($pdo) {
    $stmt = $pdo->query("
        SELECT status, COUNT(*) as count
        FROM insurance_claims
        GROUP BY status
    ");
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Fetch data for reports
$total_patients = getTotalPatients($pdo);
$total_doctors = getTotalDoctors($pdo);
$total_revenue = getTotalRevenue($pdo);
$total_expenses = getTotalExpenses($pdo);
$total_appointments = getTotalAppointments($pdo);
$total_prescriptions = getTotalPrescriptions($pdo);
$gender_distribution = getGenderDistribution($pdo);
$age_distribution = getAgeDistribution($pdo);
$status_distribution = getStatusDistribution($pdo);
$services_distribution = getServicesDistribution($pdo);
$revenue_trend = getRevenueTrend($pdo);
$expenses_trend = getExpensesTrend($pdo);
$appointments_trend = getAppointmentsTrend($pdo);
$top_doctors = getTopDoctors($pdo);
$insurance_claims_status = getInsuranceClaimsStatus($pdo);

?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Admin Reports</title>
    <!-- Include Bootstrap CSS -->
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css">
    <!-- Include Chart.js CDN (Version 3.x) -->
    <script src="https://cdn.jsdelivr.net/npm/chart.js@3.9.1/dist/chart.min.js"></script>
    <!-- Custom CSS -->
    <style>
        /* Custom styles */
        .container-report {
            margin-top: 30px;
            margin-bottom: 30px;
        }
        .chart-container {
            width: 100%;
            height: 400px;
        }
        .card {
            margin-bottom: 30px;
        }
        h1 {
            text-align: center;
            margin-bottom: 40px;
        }
        .card-deck .card {
            min-width: 180px;
        }
        .card-title {
            font-size: 1.2rem;
        }
        .nav-link.active {
            font-weight: bold;
            color: #007bff !important;
        }
        .nav-tabs .nav-link {
            color: #495057;
        }
        .table td, .table th {
            vertical-align: middle;
        }
    </style>
</head>
<body>
    <!-- Navigation bar -->
    <?php include 'includes/navbar.php'; ?> <!-- Ensure you have a navbar.php -->

    <!-- Main container -->
    <div class="container-report">
        <h1>Admin Reports</h1>

        <!-- Summary Cards -->
        <div class="row">
            <!-- Total Patients -->
            <div class="col-md-4">
                <div class="card text-center">
                    <div class="card-body bg-primary text-white">
                        <h5 class="card-title">Total Patients</h5>
                        <p class="display-4"><?php echo htmlspecialchars($total_patients); ?></p>
                    </div>
                </div>
            </div>
            <!-- Total Doctors -->
            <div class="col-md-4">
                <div class="card text-center">
                    <div class="card-body bg-success text-white">
                        <h5 class="card-title">Total Doctors</h5>
                        <p class="display-4"><?php echo htmlspecialchars($total_doctors); ?></p>
                    </div>
                </div>
            </div>
            <!-- Total Revenue -->
            <div class="col-md-4">
                <div class="card text-center">
                    <div class="card-body bg-info text-white">
                        <h5 class="card-title">Total Revenue</h5>
                        <p class="display-4">$<?php echo number_format($total_revenue, 2); ?></p>
                    </div>
                </div>
            </div>
        </div>

        <div class="row">
            <!-- Total Expenses -->
            <div class="col-md-4">
                <div class="card text-center">
                    <div class="card-body bg-warning text-white">
                        <h5 class="card-title">Total Expenses</h5>
                        <p class="display-4">$<?php echo number_format($total_expenses, 2); ?></p>
                    </div>
                </div>
            </div>
            <!-- Total Appointments -->
            <div class="col-md-4">
                <div class="card text-center">
                    <div class="card-body bg-danger text-white">
                        <h5 class="card-title">Total Appointments</h5>
                        <p class="display-4"><?php echo htmlspecialchars($total_appointments); ?></p>
                    </div>
                </div>
            </div>
            <!-- Total Prescriptions -->
            <div class="col-md-4">
                <div class="card text-center">
                    <div class="card-body bg-secondary text-white">
                        <h5 class="card-title">Total Prescriptions</h5>
                        <p class="display-4"><?php echo htmlspecialchars($total_prescriptions); ?></p>
                    </div>
                </div>
            </div>
        </div>

        <!-- Tabs for Charts -->
        <ul class="nav nav-tabs mt-4" id="reportTabs" role="tablist">
            <li class="nav-item">
                <a class="nav-link active" id="patient-tab" data-bs-toggle="tab" href="#patient" role="tab" aria-controls="patient" aria-selected="true">Patient Data</a>
            </li>
            <li class="nav-item">
                <a class="nav-link" id="financial-tab" data-bs-toggle="tab" href="#financial" role="tab" aria-controls="financial" aria-selected="false">Financial Data</a>
            </li>
            <li class="nav-item">
                <a class="nav-link" id="appointments-tab" data-bs-toggle="tab" href="#appointments" role="tab" aria-controls="appointments" aria-selected="false">Appointments</a>
            </li>
            <li class="nav-item">
                <a class="nav-link" id="doctors-tab" data-bs-toggle="tab" href="#doctors" role="tab" aria-controls="doctors" aria-selected="false">Doctors</a>
            </li>
            <li class="nav-item">
                <a class="nav-link" id="insurance-tab" data-bs-toggle="tab" href="#insurance" role="tab" aria-controls="insurance" aria-selected="false">Insurance Claims</a>
            </li>
        </ul>

        <div class="tab-content" id="reportTabsContent">
            <!-- Patient Data Tab -->
            <div class="tab-pane fade show active" id="patient" role="tabpanel" aria-labelledby="patient-tab">
                <!-- Gender Distribution -->
                <div class="card">
                    <div class="card-header">
                        Gender Distribution
                    </div>
                    <div class="card-body">
                        <canvas id="genderChart"></canvas>
                    </div>
                </div>

                <!-- Age Distribution -->
                <div class="card">
                    <div class="card-header">
                        Age Distribution
                    </div>
                    <div class="card-body">
                        <canvas id="ageChart"></canvas>
                    </div>
                </div>

                <!-- Status Distribution -->
                <div class="card">
                    <div class="card-header">
                        Patient Status Distribution
                    </div>
                    <div class="card-body">
                        <canvas id="statusChart"></canvas>
                    </div>
                </div>
            </div>

            <!-- Financial Data Tab -->
            <div class="tab-pane fade" id="financial" role="tabpanel" aria-labelledby="financial-tab">
                <!-- Revenue Trend -->
                <div class="card">
                    <div class="card-header">
                        Revenue Over Time
                    </div>
                    <div class="card-body">
                        <canvas id="revenueChart"></canvas>
                    </div>
                </div>

                <!-- Expenses Trend -->
                <div class="card">
                    <div class="card-header">
                        Expenses Over Time
                    </div>
                    <div class="card-body">
                        <canvas id="expensesChart"></canvas>
                    </div>
                </div>

                <!-- Services Rendered Distribution -->
                <div class="card">
                    <div class="card-header">
                        Services Rendered Distribution
                    </div>
                    <div class="card-body">
                        <canvas id="servicesChart"></canvas>
                    </div>
                </div>
            </div>

            <!-- Appointments Tab -->
            <div class="tab-pane fade" id="appointments" role="tabpanel" aria-labelledby="appointments-tab">
                <!-- Appointments Trend -->
                <div class="card">
                    <div class="card-header">
                        Appointments Over Time
                    </div>
                    <div class="card-body">
                        <canvas id="appointmentsChart"></canvas>
                    </div>
                </div>
            </div>

            <!-- Doctors Tab -->
            <div class="tab-pane fade" id="doctors" role="tabpanel" aria-labelledby="doctors-tab">
                <!-- Top Doctors by Number of Patients -->
                <div class="card">
                    <div class="card-header">
                        Top Doctors by Number of Patients
                    </div>
                    <div class="card-body">
                        <canvas id="topDoctorsChart"></canvas>
                    </div>
                </div>
            </div>

            <!-- Insurance Claims Tab -->
            <div class="tab-pane fade" id="insurance" role="tabpanel" aria-labelledby="insurance-tab">
                <!-- Insurance Claims Status Distribution -->
                <div class="card">
                    <div class="card-header">
                        Insurance Claims Status Distribution
                    </div>
                    <div class="card-body">
                        <canvas id="insuranceClaimsChart"></canvas>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <!-- Include Bootstrap JS Bundle (includes Popper) -->
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/js/bootstrap.bundle.min.js"></script>
    <!-- Include Moment.js for date parsing (optional, Chart.js 3 can handle ISO dates) -->
    <script src="https://cdn.jsdelivr.net/momentjs/latest/moment.min.js"></script>

    <!-- JavaScript code to render charts -->
    <script>
        // Helper function to generate random colors
        function getRandomColors(num) {
            const colors = [];
            for(let i = 0; i < num; i++) {
                const r = Math.floor(Math.random() * 255);
                const g = Math.floor(Math.random() * 255);
                const b = Math.floor(Math.random() * 255);
                colors.push(`rgba(${r}, ${g}, ${b}, 0.7)`);
            }
            return colors;
        }

        // Gender Distribution Chart
        const genderCtx = document.getElementById('genderChart').getContext('2d');
        const genderData = <?php echo json_encode($gender_distribution); ?>;
        const genderLabels = genderData.map(row => row.gender);
        const genderCounts = genderData.map(row => row.count);
        const genderColors = getRandomColors(genderLabels.length);

        const genderChart = new Chart(genderCtx, {
            type: 'pie',
            data: {
                labels: genderLabels,
                datasets: [{
                    data: genderCounts,
                    backgroundColor: genderColors,
                    borderColor: '#fff',
                    borderWidth: 1
                }]
            },
            options: {
                responsive: true,
                plugins: {
                    legend: {
                        position: 'bottom',
                    },
                    title: {
                        display: false,
                        text: 'Gender Distribution'
                    }
                }
            }
        });

        // Age Distribution Chart
        const ageCtx = document.getElementById('ageChart').getContext('2d');
        const ages = <?php echo json_encode($age_distribution); ?>;

        // Define age groups
        const ageGroups = {
            '0-9': 0,
            '10-19': 0,
            '20-29': 0,
            '30-39': 0,
            '40-49': 0,
            '50-59': 0,
            '60-69': 0,
            '70-79': 0,
            '80-89': 0,
            '90+': 0
        };

        // Categorize ages into groups
        ages.forEach(age => {
            age = parseInt(age);
            if (age >= 0 && age <= 9) {
                ageGroups['0-9']++;
            } else if (age >= 10 && age <= 19) {
                ageGroups['10-19']++;
            } else if (age >= 20 && age <= 29) {
                ageGroups['20-29']++;
            } else if (age >= 30 && age <= 39) {
                ageGroups['30-39']++;
            } else if (age >= 40 && age <= 49) {
                ageGroups['40-49']++;
            } else if (age >= 50 && age <= 59) {
                ageGroups['50-59']++;
            } else if (age >= 60 && age <= 69) {
                ageGroups['60-69']++;
            } else if (age >= 70 && age <= 79) {
                ageGroups['70-79']++;
            } else if (age >= 80 && age <= 89) {
                ageGroups['80-89']++;
            } else {
                ageGroups['90+']++;
            }
        });

        const ageLabels = Object.keys(ageGroups);
        const ageCounts = Object.values(ageGroups);
        const ageColors = getRandomColors(ageLabels.length);

        const ageChart = new Chart(ageCtx, {
            type: 'bar',
            data: {
                labels: ageLabels,
                datasets: [{
                    label: 'Number of Patients',
                    data: ageCounts,
                    backgroundColor: ageColors,
                    borderColor: '#fff',
                    borderWidth: 1
                }]
            },
            options: {
                responsive: true,
                plugins: {
                    legend: {
                        display: false,
                    },
                    title: {
                        display: false,
                        text: 'Age Distribution'
                    }
                },
                scales: {
                    y: {
                        beginAtZero: true,
                        precision: 0
                    }
                }
            }
        });

        // Status Distribution Chart
        const statusCtx = document.getElementById('statusChart').getContext('2d');
        const statusData = <?php echo json_encode($status_distribution); ?>;
        const statusLabels = statusData.map(row => row.status);
        const statusCounts = statusData.map(row => row.count);
        const statusColors = getRandomColors(statusLabels.length);

        const statusChart = new Chart(statusCtx, {
            type: 'doughnut',
            data: {
                labels: statusLabels,
                datasets: [{
                    data: statusCounts,
                    backgroundColor: statusColors,
                    borderColor: '#fff',
                    borderWidth: 1
                }]
            },
            options: {
                responsive: true,
                plugins: {
                    legend: {
                        position: 'bottom',
                    },
                    title: {
                        display: false,
                        text: 'Patient Status Distribution'
                    }
                }
            }
        });

        // Services Rendered Distribution Chart
        const servicesCtx = document.getElementById('servicesChart').getContext('2d');
        const servicesData = <?php echo json_encode($services_distribution); ?>;
        const servicesLabels = servicesData.map(row => row.service_name);
        const servicesCounts = servicesData.map(row => row.count);
        const servicesColors = getRandomColors(servicesLabels.length);

        const servicesChart = new Chart(servicesCtx, {
            type: 'horizontalBar', // Note: 'horizontalBar' is deprecated in Chart.js v3
            data: {
                labels: servicesLabels,
                datasets: [{
                    label: 'Number of Services Rendered',
                    data: servicesCounts,
                    backgroundColor: servicesColors,
                    borderColor: '#fff',
                    borderWidth: 1
                }]
            },
            options: {
                indexAxis: 'y',
                responsive: true,
                plugins: {
                    legend: {
                        display: false
                    },
                    title: {
                        display: false,
                        text: 'Services Rendered Distribution'
                    }
                },
                scales: {
                    x: {
                        beginAtZero: true,
                        precision: 0
                    }
                }
            }
        });

        // Revenue Trend Chart
        const revenueCtx = document.getElementById('revenueChart').getContext('2d');
        const revenueData = <?php echo json_encode($revenue_trend); ?>;
        const revenueLabels = revenueData.map(row => row.month);
        const revenueTotals = revenueData.map(row => parseFloat(row.total));
        const revenueColors = 'rgba(40, 167, 69, 0.7)';

        const revenueChart = new Chart(revenueCtx, {
            type: 'line',
            data: {
                labels: revenueLabels,
                datasets: [{
                    label: 'Total Revenue ($)',
                    data: revenueTotals,
                    backgroundColor: 'rgba(40, 167, 69, 0.2)',
                    borderColor: 'rgba(40, 167, 69, 1)',
                    borderWidth: 2,
                    fill: true,
                    tension: 0.1
                }]
            },
            options: {
                responsive: true,
                plugins: {
                    legend: {
                        position: 'top',
                    },
                    title: {
                        display: false,
                        text: 'Revenue Over Time'
                    }
                },
                scales: {
                    x: {
                        type: 'time',
                        time: {
                            unit: 'month',
                            tooltipFormat: 'YYYY-MM'
                        },
                        title: {
                            display: true,
                            text: 'Month'
                        }
                    },
                    y: {
                        beginAtZero: true,
                        title: {
                            display: true,
                            text: 'Revenue ($)'
                        },
                        ticks: {
                            callback: function(value) {
                                return '$' + value;
                            }
                        }
                    }
                }
            }
        });

        // Expenses Trend Chart
        const expensesCtx = document.getElementById('expensesChart').getContext('2d');
        const expensesData = <?php echo json_encode($expenses_trend); ?>;
        const expensesLabels = expensesData.map(row => row.month);
        const expensesTotals = expensesData.map(row => parseFloat(row.total));
        const expensesColors = 'rgba(255, 193, 7, 0.7)';

        const expensesChart = new Chart(expensesCtx, {
            type: 'line',
            data: {
                labels: expensesLabels,
                datasets: [{
                    label: 'Total Expenses ($)',
                    data: expensesTotals,
                    backgroundColor: 'rgba(255, 193, 7, 0.2)',
                    borderColor: 'rgba(255, 193, 7, 1)',
                    borderWidth: 2,
                    fill: true,
                    tension: 0.1
                }]
            },
            options: {
                responsive: true,
                plugins: {
                    legend: {
                        position: 'top',
                    },
                    title: {
                        display: false,
                        text: 'Expenses Over Time'
                    }
                },
                scales: {
                    x: {
                        type: 'time',
                        time: {
                            unit: 'month',
                            tooltipFormat: 'YYYY-MM'
                        },
                        title: {
                            display: true,
                            text: 'Month'
                        }
                    },
                    y: {
                        beginAtZero: true,
                        title: {
                            display: true,
                            text: 'Expenses ($)'
                        },
                        ticks: {
                            callback: function(value) {
                                return '$' + value;
                            }
                        }
                    }
                }
            }
        });

        // Appointments Trend Chart
        const appointmentsCtx = document.getElementById('appointmentsChart').getContext('2d');
        const appointmentsData = <?php echo json_encode($appointments_trend); ?>;
        const appointmentsLabels = appointmentsData.map(row => row.month);
        const appointmentsCounts = appointmentsData.map(row => row.count);
        const appointmentsColors = 'rgba(0, 123, 255, 0.7)';

        const appointmentsChart = new Chart(appointmentsCtx, {
            type: 'bar',
            data: {
                labels: appointmentsLabels,
                datasets: [{
                    label: 'Number of Appointments',
                    data: appointmentsCounts,
                    backgroundColor: 'rgba(0, 123, 255, 0.7)',
                    borderColor: 'rgba(0, 123, 255, 1)',
                    borderWidth: 1
                }]
            },
            options: {
                responsive: true,
                plugins: {
                    legend: {
                        position: 'top',
                    },
                    title: {
                        display: false,
                        text: 'Appointments Over Time'
                    }
                },
                scales: {
                    x: {
                        type: 'time',
                        time: {
                            unit: 'month',
                            tooltipFormat: 'YYYY-MM'
                        },
                        title: {
                            display: true,
                            text: 'Month'
                        }
                    },
                    y: {
                        beginAtZero: true,
                        title: {
                            display: true,
                            text: 'Appointments'
                        },
                        ticks: {
                            precision: 0
                        }
                    }
                }
            }
        });

        // Top Doctors by Number of Patients Chart
        const topDoctorsCtx = document.getElementById('topDoctorsChart').getContext('2d');
        const topDoctorsData = <?php echo json_encode($top_doctors); ?>;
        const doctorNames = topDoctorsData.map(row => row.name);
        const patientCounts = topDoctorsData.map(row => row.patient_count);
        const topDoctorsColors = getRandomColors(doctorNames.length);

        const topDoctorsChart = new Chart(topDoctorsCtx, {
            type: 'bar',
            data: {
                labels: doctorNames,
                datasets: [{
                    label: 'Number of Patients',
                    data: patientCounts,
                    backgroundColor: topDoctorsColors,
                    borderColor: '#fff',
                    borderWidth: 1
                }]
            },
            options: {
                indexAxis: 'y',
                responsive: true,
                plugins: {
                    legend: {
                        display: false
                    },
                    title: {
                        display: false,
                        text: 'Top Doctors by Number of Patients'
                    }
                },
                scales: {
                    x: {
                        beginAtZero: true,
                        precision: 0,
                        title: {
                            display: true,
                            text: 'Number of Patients'
                        }
                    },
                    y: {
                        title: {
                            display: true,
                            text: 'Doctor'
                        }
                    }
                }
            }
        });

        // Insurance Claims Status Distribution Chart
        const insuranceCtx = document.getElementById('insuranceClaimsChart').getContext('2d');
        const insuranceData = <?php echo json_encode($insurance_claims_status); ?>;
        const insuranceStatuses = insuranceData.map(row => row.status);
        const insuranceCounts = insuranceData.map(row => row.count);
        const insuranceColors = getRandomColors(insuranceStatuses.length);

        const insuranceClaimsChart = new Chart(insuranceCtx, {
            type: 'pie',
            data: {
                labels: insuranceStatuses,
                datasets: [{
                    data: insuranceCounts,
                    backgroundColor: insuranceColors,
                    borderColor: '#fff',
                    borderWidth: 1
                }]
            },
            options: {
                responsive: true,
                plugins: {
                    legend: {
                        position: 'bottom',
                    },
                    title: {
                        display: false,
                        text: 'Insurance Claims Status Distribution'
                    }
                }
            }
        });

        // Revenue and Expenses Trend Charts on Financial Tab
        // Already handled above

        // Activate Bootstrap Tabs
        document.addEventListener('DOMContentLoaded', function () {
            var triggerTabList = [].slice.call(document.querySelectorAll('#reportTabs a'))
            triggerTabList.forEach(function (triggerEl) {
                var tabTrigger = new bootstrap.Tab(triggerEl)

                triggerEl.addEventListener('click', function (event) {
                    event.preventDefault()
                    tabTrigger.show()
                })
            })
        });
    </script>
</body>
</html>) so update and make it conisstent with other files and make it advanced and type full codes with guides