based on this database (-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: Oct 06, 2024 at 07:47 PM
-- 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 `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
) 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(),
  `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 `patients`
--

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

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

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

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

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

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

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

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

--
-- Table structure for table `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 `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 `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 `patients`
--
ALTER TABLE `patients`
  ADD PRIMARY KEY (`id`),
  ADD KEY `doctor_id` (`doctor_id`);

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

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

--
-- Indexes for table `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 `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 `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 `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 `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 `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 `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 these path (./index.php
./admin_dashboard.php
./admin_logout.php
./admin_login.php
./update_patient_status.php
./add_health_record.php
./create_patient.php
./manage_comments.php
./admin_report.php
./view_patient.php
./delete_patient.php
./edit_patient.php
./db_connect.php
./accountant/dashboard.php
./accountant/manage_invoices.php
./accountant/view_invoice.php
./accountant/patient_financial_summary.php
./accountant/create_insurance_claim.php
./accountant/financial_forecasting.php
./accountant/mark_paid.php
./accountant/login.php
./accountant/important_text
./accountant/edit_invoice.php
./accountant/generate_report.php
./accountant/view_patient_financial_summary.php
./accountant/manage_salaries.php
./accountant/manage_payments.php
./accountant/accountant_profile.php
./accountant/delete_payment.php
./accountant/generate_payslip.php
./accountant/budget_management.php
./accountant/edit_salary.php
./accountant/insurance_claim_tracker.php
./accountant/delete_invoice.php
./accountant/logout.php
./accountant/trend_analysis.php
./accountant/create_payment.php
./accountant/financial_dashboard.php
./accountant/add_salary.php
./accountant/edit_payment.php
./accountant/create_invoice.php
./accountant/delete_salary.php
./accountant/upload/payslips/payslip_1.pdf
./accountant/upload/payslips/payslip_5.pdf
./accountant/upload/payslips/payslip_6.pdf
./accountant/edit_salary_form.php
./accountant/view_salaries.php
./accountant/bulk_actions.php
./invoice/view_invoice.php
./invoice/edit_invoice.php
./invoice/print_invoice.php
./invoice/index.php
./invoice/services.php
./invoice/create_invoice.php
./team/add_accountant.php
./team/delete_employee.php
./team/add_doctor.php
./team/add_admin.php
./team/add_employee.php
./team/edit_employee.php
./team/manage_employees.php
./team/select_employee.php
./team/view_employee.php
./patients/delete_comment.php
./patients/patient_dashboard.php
./patients/patient_logout.php
./patients/patient_login.php
./patients/send_comment.php
./doctors/dashboard.php
./doctors/login.php
./doctors/add_health_record.php
./doctors/add_doctor.php
./doctors/view_patient.php
./doctors/delete_health_record.php
./doctors/logout.php
./doctors/edit_health_record.php
./doctors/manage_doctors.php
./chat/index.php
./chat/send_message.php
./chat/get_messages.php
./uploads/doaa123_1727919500.jpg
./uploads/payslips/payslip_6.pdf
./uploads/mahmoudtawfik_19.png
./uploads/drengy_18.jpg
./uploads/ali123_1727918034.jpg
./uploads/mahmoudtawfik_1727916558.png
./uploads/engy_14.jpg
./images/gold-logo.png
./images/background-image-admin.jpg
./images/white-logo.png
./images/logo.png
./css/styles.css
), i need to fix this (<?php
// accountant/financial_forecasting.php

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

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

// Function to sanitize output
function sanitize_output($data) {
    return htmlspecialchars($data, ENT_QUOTES, 'UTF-8');
}

// Enable error reporting during development (disable in production)
//ini_set('display_errors', 1);
//error_reporting(E_ALL);

// Define date range for historical data (last 12 months)
$start_date = date('Y-m-01', strtotime('-11 months'));
$end_date = date('Y-m-t');

try {
    // Fetch monthly revenue
    $stmt = $pdo->prepare("
        SELECT DATE_FORMAT(invoice_date, '%Y-%m') AS month, SUM(total_amount) AS monthly_revenue
        FROM invoices
        WHERE invoice_date BETWEEN ? AND ?
        GROUP BY month
        ORDER BY month ASC
    ");
    $stmt->execute([$start_date, $end_date]);
    $revenue_data = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Prepare data for forecasting
    $months = [];
    $revenues = [];

    foreach ($revenue_data as $data) {
        $months[] = $data['month'];
        $revenues[] = $data['monthly_revenue'];
    }

    // Simple forecasting using linear regression (least squares method)
    // Calculate the coefficients a0 and a1
    $n = count($revenues);
    $sum_x = 0;
    $sum_y = 0;
    $sum_xy = 0;
    $sum_xx = 0;

    for ($i = 0; $i < $n; $i++) {
        $x = $i + 1; // Time periods
        $y = $revenues[$i];

        $sum_x += $x;
        $sum_y += $y;
        $sum_xy += $x * $y;
        $sum_xx += $x * $x;
    }

    $a1 = ($n * $sum_xy - $sum_x * $sum_y) / ($n * $sum_xx - $sum_x * $sum_x);
    $a0 = ($sum_y - $a1 * $sum_x) / $n;

    // Forecast for the next 6 months using Linear Regression
    $forecast_months = [];
    $forecast_revenues_lr = [];

    for ($i = 1; $i <= 6; $i++) {
        $future_time = $n + $i;
        $forecast = $a0 + $a1 * $future_time;
        $forecast_revenues_lr[] = max(0, $forecast); // Ensure forecast is not negative
        $forecast_months[] = date('Y-m', strtotime("+$i month", strtotime(end($months) . '-01')));
    }

    // Forecast using Simple Moving Average (3-month window)
    $forecast_revenues_sma = [];
    $window_size = 3;

    for ($i = $n; $i < $n + 6; $i++) {
        $window_start = max(0, $i - $window_size);
        $window_end = $i;
        $window_data = array_slice(array_merge($revenues, $forecast_revenues_sma), $window_start, $window_size);
        $forecast = array_sum($window_data) / count($window_data);
        $forecast_revenues_sma[] = max(0, $forecast);
    }

} catch (PDOException $e) {
    echo "An error occurred: " . sanitize_output($e->getMessage());
    exit();
}
?>

<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>Financial Forecasting</title>
    <!-- Meta tags for responsiveness -->
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <!-- Chart.js Library -->
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <!-- Include Bootstrap CSS -->
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <!-- Styles -->
    <style>
        /* Import Google Fonts */
        @import url('https://fonts.googleapis.com/css2?family=Poppins:wght@400;600;700&display=swap');

        /* Root Variables */
        :root {
            --primary-color: #4F3F02;
            --secondary-color: #D1C287;
            --background-color: #F8F9FA;
            --card-background: #FFFFFF;
            --text-color: #333333;
            --success-color: #28a745;
            --danger-color: #dc3545;
            --info-color: #17a2b8;
            --font-family: 'Poppins', sans-serif;
        }

        body {
            background-color: var(--background-color);
            font-family: var(--font-family);
            color: var(--text-color);
        }

        /* Header */
        .dashboard-header {
            background-color: var(--primary-color);
            color: #fff;
            padding: 20px;
            text-align: center;
            border-radius: 0 0 20px 20px;
            margin-bottom: 30px;
            position: relative;
        }

        .dashboard-header h1 {
            font-weight: 700;
            margin-bottom: 10px;
        }

        .dashboard-header p a {
            color: #fff;
            margin: 0 10px;
            text-decoration: none;
        }

        .dashboard-header p a:hover {
            text-decoration: underline;
        }

        /* Cards */
        .card {
            border: none;
            border-radius: 15px;
            box-shadow: 0 4px 6px rgba(0, 0, 0, 0.1);
            margin-bottom: 25px;
            background-color: var(--card-background);
        }

        .card .card-body {
            padding: 20px;
        }

        .card .card-title {
            color: var(--primary-color);
            font-weight: 600;
            margin-bottom: 10px;
            font-size: 1.1em;
        }

        .card .card-text {
            font-size: 1.0em;
            font-weight: 400;
        }

        /* Buttons */
        .btn-custom {
            background-color: var(--secondary-color);
            border-color: var(--secondary-color);
            color: var(--primary-color);
            font-weight: 600;
            width: 100%;
            padding: 12px;
            border-radius: 8px;
            transition: all 0.3s ease;
            text-decoration: none;
            text-align: center;
            display: inline-block;
        }

        .btn-custom:hover {
            background-color: var(--primary-color);
            color: #fff;
            border-color: var(--secondary-color);
            transform: translateY(-2px);
            box-shadow: 0 6px 12px rgba(0, 0, 0, 0.15);
        }

        /* Filters */
        .filters {
            background-color: var(--card-background);
            padding: 20px;
            border-radius: 15px;
            box-shadow: 0 4px 6px rgba(0, 0, 0, 0.1);
            margin-bottom: 30px;
        }

        /* Charts */
        .chart-container {
            position: relative;
            height: 400px;
            margin-bottom: 30px;
        }

        /* Responsive Adjustments */
        @media (max-width: 768px) {
            .dashboard-header h1 {
                font-size: 22px;
            }

            .btn-custom {
                margin-bottom: 15px;
            }
        }

        @media (max-width: 576px) {
            .dashboard-header h1 {
                font-size: 18px;
            }

            .card .card-body {
                padding: 15px;
            }

            .card .card-text {
                font-size: 0.9em;
            }

            .filters {
                padding: 15px;
            }

            .chart-container {
                height: 250px;
            }
        }
        /* Exclude dark mode styles */
    </style>
</head>
<body>

    <div class="dashboard-header">
        <h1>Financial Forecasting</h1>
        <p>
            <a href="dashboard.php">Back to Dashboard</a> | <a href="logout.php">Logout</a>
        </p>
    </div>

    <div class="container">

        <!-- Forecasting Explanation -->
        <div class="card">
            <div class="card-body">
                <h5 class="card-title">Understanding the Forecast</h5>
                <p class="card-text">
                    This financial forecasting tool uses historical revenue data to predict future revenues over the next six months. Two forecasting methods are employed:
                </p>
                <ul>
                    <li><strong>Linear Regression:</strong> This method fits a straight line to the historical data and projects future values based on the line's slope and intercept.</li>
                    <li><strong>Simple Moving Average:</strong> This method calculates the average of the last few data points and uses it as the forecast for the next period.</li>
                </ul>
                <p class="card-text">
                    The forecasts are intended to help in planning and decision-making to support business growth.
                </p>
            </div>
        </div>

        <!-- Chart Section -->
        <div class="chart-container">
            <canvas id="forecastChart"></canvas>
        </div>

        <!-- Actionable Insights -->
        <div class="card">
            <div class="card-body">
                <h5 class="card-title">Actionable Insights</h5>
                <?php
                // Analyze forecasted revenues
                $average_forecast = array_sum($forecast_revenues_lr) / count($forecast_revenues_lr);
                $last_actual = end($revenues);
                if ($average_forecast > $last_actual) {
                    echo '<p class="card-text">The forecast predicts an upward trend in revenue. Consider scaling up operations, investing in marketing, and preparing for increased demand.</p>';
                } else {
                    echo '<p class="card-text">The forecast predicts a downward or stable trend in revenue. Review current strategies, optimize costs, and explore new revenue streams.</p>';
                }
                ?>
            </div>
        </div>

        <!-- Guides for Growth -->
        <div class="card">
            <div class="card-body">
                <h5 class="card-title">Guides for Business Growth</h5>
                <p class="card-text">
                    Based on the forecasts, here are some strategies to consider:
                </p>
                <ul>
                    <li><strong>Marketing Campaigns:</strong> Targeted advertising can attract new patients and retain existing ones.</li>
                    <li><strong>Service Expansion:</strong> Introduce new services that meet patient needs and increase revenue streams.</li>
                    <li><strong>Cost Optimization:</strong> Review operational costs to improve profitability without compromising service quality.</li>
                    <li><strong>Patient Engagement:</strong> Enhance patient experience to build loyalty and encourage referrals.</li>
                    <li><strong>Technology Integration:</strong> Adopt new technologies for efficiency and better patient care.</li>
                </ul>
            </div>
        </div>

    </div> <!-- End of container -->

    <!-- Scripts -->
    <!-- Include Bootstrap JS -->
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
    <!-- Chart.js -->
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>

    <script>
        var ctx = document.getElementById('forecastChart').getContext('2d');
        var forecastChart = new Chart(ctx, {
            type: 'line',
            data: {
                labels: <?php echo json_encode(array_merge($months, $forecast_months)); ?>,
                datasets: [
                    {
                        label: 'Actual Revenue',
                        data: <?php echo json_encode($revenues); ?>,
                        borderColor: 'rgba(40, 167, 69, 1)', // Success color
                        backgroundColor: 'rgba(40, 167, 69, 0.2)',
                        fill: false,
                        tension: 0.1,
                        pointRadius: 5,
                    },
                    {
                        label: 'Forecasted Revenue (Linear Regression)',
                        data: <?php
                            $combined_forecast_lr = array_merge(
                                array_fill(0, count($revenues), null),
                                $forecast_revenues_lr
                            );
                            echo json_encode($combined_forecast_lr);
                        ?>,
                        borderColor: 'rgba(255, 165, 0, 1)', // Orange color
                        backgroundColor: 'rgba(255, 165, 0, 0.2)',
                        fill: false,
                        borderDash: [5, 5],
                        tension: 0.1,
                    },
                    {
                        label: 'Forecasted Revenue (Moving Average)',
                        data: <?php
                            $combined_forecast_sma = array_merge(
                                array_fill(0, count($revenues), null),
                                $forecast_revenues_sma
                            );
                            echo json_encode($combined_forecast_sma);
                        ?>,
                        borderColor: 'rgba(23, 162, 184, 1)', // Info color
                        backgroundColor: 'rgba(23, 162, 184, 0.2)',
                        fill: false,
                        borderDash: [10, 5],
                        tension: 0.1,
                    }
                ]
            },
            options: {
                responsive: true,
                maintainAspectRatio: false,
                interaction: {
                    mode: 'index',
                    intersect: false,
                },
                stacked: false,
                plugins: {
                    title: {
                        display: true,
                        text: 'Revenue Forecasting Based on Historical Data',
                        font: {
                            size: 18
                        }
                    },
                    tooltip: {
                        enabled: true
                    },
                    legend: {
                        position: 'bottom'
                    }
                },
                scales: {
                    x: {
                        display: true,
                        title: {
                            display: true,
                            text: 'Month',
                            font: {
                                size: 14
                            }
                        }
                    },
                    y: {
                        display: true,
                        title: {
                            display: true,
                            text: 'Revenue ($)',
                            font: {
                                size: 14
                            }
                        },
                        beginAtZero: true
                    }
                }
            }
        });
    </script>
</body>
</html>) and type full codes