in this codes (<?php
session_start();
require_once 'db_connect.php'; // Ensure this file connects to your database

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

// Include TCPDF library
require_once 'assets/libraries/tcpdf/tcpdf.php'; // Correct path to TCPDF library

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

try {
    // Initialize filter variables with default values
    $start_date = isset($_GET['start_date']) ? $_GET['start_date'] : date('Y-m-d', strtotime('-30 days'));
    $end_date = isset($_GET['end_date']) ? $_GET['end_date'] : date('Y-m-d');
    $gender_filter = isset($_GET['gender_filter']) ? $_GET['gender_filter'] : '';
    $age_group_filter = isset($_GET['age_group_filter']) ? $_GET['age_group_filter'] : '';
    $status_filter = isset($_GET['status_filter']) ? $_GET['status_filter'] : '';
    $service_filter = isset($_GET['service_filter']) ? $_GET['service_filter'] : '';

    // Validate dates
    $start_date = date('Y-m-d', strtotime($start_date));
    $end_date = date('Y-m-d', strtotime($end_date));

    // Build filters array
    $filters = [
        'start_date' => $start_date,
        'end_date' => $end_date,
        'gender' => $gender_filter,
        'age_group' => $age_group_filter,
        'status' => $status_filter,
        'service' => $service_filter
    ];

    // Fetch all the data
    $total_patients = getTotalPatients($pdo, $filters);
    $gender_distribution = getGenderDistribution($pdo, $filters);
    $age_distribution = getAgeDistribution($pdo, $filters);
    $status_distribution = getStatusDistribution($pdo, $filters);
    $services_distribution = getServicesDistribution($pdo, $filters);
    $invoice_trend = getInvoiceTrend($pdo, $filters);
    $registration_trend = getRegistrationTrend($pdo, $filters);

} catch (Exception $e) {
    // Handle exceptions
    die("Error: " . $e->getMessage());
}

// Functions to fetch data
function buildWhereConditions($filters, &$params, $prefix = 'p.') {
    $conditions = [];

    if (!empty($filters['start_date']) && !empty($filters['end_date'])) {
        $conditions[] = $prefix."registration_date BETWEEN :start_date AND :end_date";
        $params[':start_date'] = $filters['start_date'];
        $params[':end_date'] = $filters['end_date'];
    }
    if (!empty($filters['gender'])) {
        $conditions[] = $prefix."gender = :gender";
        $params[':gender'] = $filters['gender'];
    }
    if (!empty($filters['age_group'])) {
        switch ($filters['age_group']) {
            case 'under_18':
                $conditions[] = $prefix."age < 18";
                break;
            case '18_30':
                $conditions[] = $prefix."age BETWEEN 18 AND 30";
                break;
            case '31_50':
                $conditions[] = $prefix."age BETWEEN 31 AND 50";
                break;
            case '51_70':
                $conditions[] = $prefix."age BETWEEN 51 AND 70";
                break;
            case 'over_70':
                $conditions[] = $prefix."age > 70";
                break;
        }
    }
    if (!empty($filters['status'])) {
        $conditions[] = $prefix."status = :status";
        $params[':status'] = $filters['status'];
    }
    return $conditions;
}

function getTotalPatients($pdo, $filters) {
    $params = [];
    $conditions = buildWhereConditions($filters, $params);
    $where_clause = '';
    if (!empty($conditions)) {
        $where_clause = 'WHERE ' . implode(' AND ', $conditions);
    }
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM patients p $where_clause");
    $stmt->execute($params);
    return $stmt->fetchColumn();
}

function getGenderDistribution($pdo, $filters) {
    $params = [];
    $conditions = buildWhereConditions($filters, $params);
    $where_clause = '';
    if (!empty($conditions)) {
        $where_clause = 'WHERE ' . implode(' AND ', $conditions);
    }
    $stmt = $pdo->prepare("SELECT p.gender, COUNT(*) as count FROM patients p $where_clause GROUP BY p.gender");
    $stmt->execute($params);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getAgeDistribution($pdo, $filters) {
    $params = [];
    $conditions = buildWhereConditions($filters, $params);
    $where_clause = '';
    if (!empty($conditions)) {
        $where_clause = 'WHERE ' . implode(' AND ', $conditions);
    }
    $stmt = $pdo->prepare("SELECT p.age FROM patients p $where_clause");
    $stmt->execute($params);
    return $stmt->fetchAll(PDO::FETCH_COLUMN);
}

function getStatusDistribution($pdo, $filters) {
    $params = [];
    $conditions = buildWhereConditions($filters, $params);
    $where_clause = '';
    if (!empty($conditions)) {
        $where_clause = 'WHERE ' . implode(' AND ', $conditions);
    }
    $stmt = $pdo->prepare("SELECT p.status, COUNT(*) as count FROM patients p $where_clause GROUP BY p.status");
    $stmt->execute($params);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getServicesDistribution($pdo, $filters) {
    $params = [];
    $patient_conditions = buildWhereConditions($filters, $params, 'p.');

    // Date condition for invoices
    $params[':invoice_start_date'] = $filters['start_date'];
    $params[':invoice_end_date'] = $filters['end_date'];
    $invoice_date_condition = "i.invoice_date BETWEEN :invoice_start_date AND :invoice_end_date";

    $all_conditions = $patient_conditions;
    $all_conditions[] = $invoice_date_condition;

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

    $stmt = $pdo->prepare("
        SELECT s.service_name, COUNT(*) as count
        FROM invoice_services isv
        INNER JOIN invoices i ON isv.invoice_id = i.id
        INNER JOIN patients p ON i.patient_id = p.id
        INNER JOIN services s ON isv.service_id = s.service_id
        $where_clause
        GROUP BY s.service_name
    ");

    $stmt->execute($params);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getInvoiceTrend($pdo, $filters) {
    $params = [];
    $conditions = buildWhereConditions($filters, $params, 'p.');

    // Date condition for invoices
    $params[':invoice_start_date'] = $filters['start_date'];
    $params[':invoice_end_date'] = $filters['end_date'];
    $invoice_date_condition = "i.invoice_date BETWEEN :invoice_start_date AND :invoice_end_date";

    $all_conditions = $conditions;
    $all_conditions[] = $invoice_date_condition;

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

    $stmt = $pdo->prepare("
        SELECT DATE(i.invoice_date) as date, SUM(i.total_amount) as total_amount
        FROM invoices i
        INNER JOIN patients p ON i.patient_id = p.id
        $where_clause
        GROUP BY DATE(i.invoice_date)
        ORDER BY date
    ");
    $stmt->execute($params);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getRegistrationTrend($pdo, $filters) {
    $params = [];
    $conditions = buildWhereConditions($filters, $params, 'p.');
    $where_clause = '';
    if (!empty($conditions)) {
        $where_clause = 'WHERE ' . implode(' AND ', $conditions);
    }

    $stmt = $pdo->prepare("
        SELECT DATE(p.registration_date) as date, COUNT(*) as count
        FROM patients p
        $where_clause
        GROUP BY DATE(p.registration_date)
        ORDER BY date
    ");
    $stmt->execute($params);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Generate PDF Report if requested
if (isset($_GET['generate_pdf']) && $_GET['generate_pdf'] == '1') {
    // Include TCPDF library (already included at the top)
    // Create new PDF document
    $pdf = new TCPDF();

    // Set document information
    $pdf->SetCreator('Your Application Name');
    $pdf->SetAuthor('Admin');
    $pdf->SetTitle('Admin Report Dashboard');
    $pdf->SetSubject('Patient Data Analysis');
    $pdf->SetKeywords('TCPDF, PDF, report, dashboard');

    // Add a page
    $pdf->AddPage();

    // Set font
    $pdf->SetFont('helvetica', '', 12);

    // Generate HTML content for the PDF
    $html = '<h1>Admin Report Dashboard</h1>';
    $html .= '<h2>Total Patients: ' . htmlspecialchars($total_patients) . '</h2>';

    // Gender Distribution
    $html .= '<h3>Gender Distribution</h3>';
    $html .= '<table border="1" cellpadding="5">';
    $html .= '<tr><th>Gender</th><th>Count</th></tr>';
    foreach ($gender_distribution as $row) {
        $html .= '<tr><td>' . htmlspecialchars($row['gender']) . '</td><td>' . htmlspecialchars($row['count']) . '</td></tr>';
    }
    $html .= '</table>';

    // Age Distribution
    $html .= '<h3>Age Distribution</h3>';
    $age_counts = [];
    foreach ($age_distribution as $age) {
        $ageGroup = floor($age / 10) * 10;
        $label = $ageGroup . '-' . ($ageGroup + 9);
        if (!isset($age_counts[$label])) {
            $age_counts[$label] = 0;
        }
        $age_counts[$label]++;
    }
    $html .= '<table border="1" cellpadding="5">';
    $html .= '<tr><th>Age Group</th><th>Count</th></tr>';
    foreach ($age_counts as $label => $count) {
        $html .= '<tr><td>' . htmlspecialchars($label) . '</td><td>' . htmlspecialchars($count) . '</td></tr>';
    }
    $html .= '</table>';

    // Status Distribution
    $html .= '<h3>Status Distribution</h3>';
    $html .= '<table border="1" cellpadding="5">';
    $html .= '<tr><th>Status</th><th>Count</th></tr>';
    foreach ($status_distribution as $row) {
        $html .= '<tr><td>' . htmlspecialchars($row['status']) . '</td><td>' . htmlspecialchars($row['count']) . '</td></tr>';
    }
    $html .= '</table>';

    // Services Distribution
    $html .= '<h3>Services Rendered Distribution</h3>';
    $html .= '<table border="1" cellpadding="5">';
    $html .= '<tr><th>Service</th><th>Count</th></tr>';
    foreach ($services_distribution as $row) {
        $html .= '<tr><td>' . htmlspecialchars($row['service_name']) . '</td><td>' . htmlspecialchars($row['count']) . '</td></tr>';
    }
    $html .= '</table>';

    // Invoice Trend
    $html .= '<h3>Invoice Amount Over Time</h3>';
    $html .= '<table border="1" cellpadding="5">';
    $html .= '<tr><th>Date</th><th>Total Amount</th></tr>';
    foreach ($invoice_trend as $row) {
        $html .= '<tr><td>' . htmlspecialchars($row['date']) . '</td><td>' . htmlspecialchars(number_format($row['total_amount'], 2)) . '</td></tr>';
    }
    $html .= '</table>';

    // Registration Trend
    $html .= '<h3>Patient Registration Over Time</h3>';
    $html .= '<table border="1" cellpadding="5">';
    $html .= '<tr><th>Date</th><th>Registrations</th></tr>';
    foreach ($registration_trend as $row) {
        $html .= '<tr><td>' . htmlspecialchars($row['date']) . '</td><td>' . htmlspecialchars($row['count']) . '</td></tr>';
    }
    $html .= '</table>';

    // Output the HTML content
    $pdf->writeHTML($html, true, false, true, false, '');

    // Output PDF document
    $pdf->Output('admin_report.pdf', 'D'); // 'D' sets the download behavior
    exit;
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <!-- Meta tags for responsive design and character encoding -->
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <!-- Page title -->
    <title>Admin Report Dashboard</title>
    <!-- Bootstrap 5 CSS for responsive layout and components -->
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha3/dist/css/bootstrap.min.css" rel="stylesheet">
    <!-- Date Range Picker CSS -->
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/daterangepicker/daterangepicker.css">
    <!-- Chart.js Library -->
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <!-- Custom CSS for the design -->
    <style>
        /* Custom CSS for clean design */
        body {
            background-color: #f8f9fa;
            font-family: 'Roboto', Arial, sans-serif;
        }
        /* Header styling */
        .dashboard-header {
            background-color: #343a40; /* Dark theme */
            color: #fff;
            padding: 20px;
            text-align: center;
            border-radius: 10px;
            margin-bottom: 30px;
            box-shadow: 0 4px 10px rgba(0,0,0,0.1);
        }
        /* Card styling */
        .card {
            border-radius: 10px;
            box-shadow: 0 0 10px rgba(0,0,0,0.1);
            margin-bottom: 20px;
            background-color: #ffffff;
        }
        .card-header {
            background-color: #17a2b8; /* Info color */
            color: #fff;
            font-weight: bold;
            border-bottom: none;
            padding: 15px;
        }
        /* Button styling */
        .btn-primary {
            background-color: #28a745; /* Success color */
            border-color: #28a745;
            color: #fff;
            font-weight: bold;
            border-radius: 50px;
        }
        .btn-primary:hover {
            background-color: #218838; /* Dark green */
            border-color: #1e7e34;
            color: #fff;
        }
        .btn-secondary {
            background-color: #6c757d; /* Secondary color */
            border-color: #6c757d;
            color: #fff;
            font-weight: bold;
            border-radius: 50px;
        }
        .btn-secondary:hover {
            background-color: #5a6268;
            color: #fff;
        }
        /* Form control styling */
        .form-control, .form-select {
            border-radius: 8px;
        }
        /* DateRangePicker styling */
        .daterangepicker td.active, .daterangepicker td.active:hover {
            background-color: #28a745;
        }
        /* Generate PDF button styling */
        #generatePDF {
            background-color: #007bff; /* Primary color */
            border-color: #007bff;
            color: #fff;
            font-weight: bold;
            border-radius: 50px;
        }
        #generatePDF:hover {
            background-color: #0069d9;
            border-color: #0062cc;
            color: #fff;
        }
        /* Adjust chart sizes */
        canvas {
            max-height: 400px;
        }
    </style>
</head>
<body>
    <!-- Main Container -->
    <div class="container-fluid">
        <!-- Header -->
        <div class="dashboard-header">
            <h1>Admin Report Dashboard</h1>
            <p>Comprehensive Patient Data Analysis</p>
        </div>

        <!-- Filters and Generate PDF Button -->
        <div class="row mb-4">
            <div class="col-md-12">
                <!-- Filter Card -->
                <div class="card">
                    <div class="card-header">Filters</div>
                    <div class="card-body">
                        <form id="filterForm" method="GET" class="row g-3">
                            <!-- Date Range Picker -->
                            <div class="col-md-3">
                                <label for="daterange" class="form-label">Date Range:</label>
                                <input type="text" id="daterange" name="daterange" class="form-control" value="<?= htmlspecialchars($start_date) ?> - <?= htmlspecialchars($end_date) ?>">
                                <input type="hidden" id="start_date" name="start_date" value="<?= htmlspecialchars($start_date) ?>">
                                <input type="hidden" id="end_date" name="end_date" value="<?= htmlspecialchars($end_date) ?>">
                            </div>
                            <!-- Gender Filter -->
                            <div class="col-md-3">
                                <label for="gender_filter" class="form-label">Gender:</label>
                                <select id="gender_filter" name="gender_filter" class="form-select">
                                    <option value="">All</option>
                                    <option value="Male" <?= $gender_filter == 'Male' ? 'selected' : '' ?>>Male</option>
                                    <option value="Female" <?= $gender_filter == 'Female' ? 'selected' : '' ?>>Female</option>
                                    <option value="Other" <?= $gender_filter == 'Other' ? 'selected' : '' ?>>Other</option>
                                </select>
                            </div>
                            <!-- Age Group Filter -->
                            <div class="col-md-3">
                                <label for="age_group_filter" class="form-label">Age Group:</label>
                                <select id="age_group_filter" name="age_group_filter" class="form-select">
                                    <option value="">All</option>
                                    <option value="under_18" <?= $age_group_filter == 'under_18' ? 'selected' : '' ?>>Under 18</option>
                                    <option value="18_30" <?= $age_group_filter == '18_30' ? 'selected' : '' ?>>18-30</option>
                                    <option value="31_50" <?= $age_group_filter == '31_50' ? 'selected' : '' ?>>31-50</option>
                                    <option value="51_70" <?= $age_group_filter == '51_70' ? 'selected' : '' ?>>51-70</option>
                                    <option value="over_70" <?= $age_group_filter == 'over_70' ? 'selected' : '' ?>>Over 70</option>
                                </select>
                            </div>
                            <!-- Status Filter -->
                            <div class="col-md-3">
                                <label for="status_filter" class="form-label">Status:</label>
                                <select id="status_filter" name="status_filter" class="form-select">
                                    <option value="">All</option>
                                    <?php
                                    $status_options_display = ['Initial Consultation', 'Diagnostic Phase', 'Treatment in Progress', 'Improving', 'Monitoring', 'Maintenance Phase', 'Awaiting Test Results', 'Follow-up Needed', 'Paused', 'Completed Program', 'Relapsed', 'Discontinued', 'Inactive'];
                                    foreach ($status_options_display as $option) {
                                        echo "<option value=\"" . htmlspecialchars($option) . "\"" . ($status_filter == $option ? ' selected' : '') . ">" . htmlspecialchars($option) . "</option>";
                                    }
                                    ?>
                                </select>
                            </div>
                            <!-- Service Filter -->
                            <div class="col-md-3">
                                <label for="service_filter" class="form-label">Service:</label>
                                <select id="service_filter" name="service_filter" class="form-select">
                                    <option value="">All</option>
                                    <?php
                                    // Fetch all services from the database
                                    $stmt = $pdo->query("SELECT DISTINCT service_name FROM services ORDER BY service_name");
                                    $services = $stmt->fetchAll(PDO::FETCH_COLUMN);
                                    foreach ($services as $service) {
                                        echo "<option value=\"" . htmlspecialchars($service) . "\"" . ($service_filter == $service ? ' selected' : '') . ">" . htmlspecialchars($service) . "</option>";
                                    }
                                    ?>
                                </select>
                            </div>
                            <!-- Buttons -->
                            <div class="col-md-12 d-flex justify-content-end mt-3">
                                <button type="submit" class="btn btn-primary">Apply Filters</button>
                                <button type="button" id="resetFilters" class="btn btn-secondary ms-2">Reset Filters</button>
                                <button type="button" id="generatePDF" class="btn btn-info ms-2">Generate PDF</button>
                            </div>
                        </form>
                    </div>
                </div>
            </div>
        </div>

        <!-- Report Content -->
        <div id="reportContent">
            <!-- Total Patients Card -->
            <div class="row">
                <div class="col-md-12">
                    <div class="card text-center">
                        <div class="card-body">
                            <h5 class="card-title">Total Patients</h5>
                            <h2 class="card-text"><?= htmlspecialchars($total_patients) ?></h2>
                        </div>
                    </div>
                </div>
            </div>

            <!-- Charts Row -->
            <div class="row">
                <!-- Gender Distribution Chart -->
                <div class="col-lg-6 col-md-12">
                    <div class="card h-100">
                        <div class="card-header">Gender Distribution</div>
                        <div class="card-body">
                            <canvas id="genderChart"></canvas>
                        </div>
                    </div>
                </div>
                <!-- Age Distribution Chart -->
                <div class="col-lg-6 col-md-12">
                    <div class="card h-100">
                        <div class="card-header">Age Distribution</div>
                        <div class="card-body">
                            <canvas id="ageChart"></canvas>
                        </div>
                    </div>
                </div>
            </div>

            <!-- Additional Charts Row -->
            <div class="row">
                <!-- Status Distribution Chart -->
                <div class="col-lg-6 col-md-12">
                    <div class="card h-100">
                        <div class="card-header">Patient Status Distribution</div>
                        <div class="card-body">
                            <canvas id="statusChart"></canvas>
                        </div>
                    </div>
                </div>
                <!-- Services Rendered Distribution Chart -->
                <div class="col-lg-6 col-md-12">
                    <div class="card h-100">
                        <div class="card-header">Services Rendered Distribution</div>
                        <div class="card-body">
                            <canvas id="servicesChart"></canvas>
                        </div>
                    </div>
                </div>
            </div>

            <!-- Invoice Amount Trend Chart -->
            <div class="row">
                <div class="col-md-6">
                    <div class="card">
                        <div class="card-header">Invoice Amount Over Time</div>
                        <div class="card-body">
                            <canvas id="invoiceTrendChart"></canvas>
                        </div>
                    </div>
                </div>
                <!-- Patient Registration Trend Chart -->
                <div class="col-md-6">
                    <div class="card">
                        <div class="card-header">Patient Registration Over Time</div>
                        <div class="card-body">
                            <canvas id="registrationTrendChart"></canvas>
                        </div>
                    </div>
                </div>
            </div>
        </div>
        <!-- End of Report Content -->

        <!-- Back to Dashboard Button -->
        <a href="admin_dashboard.php" class="btn btn-secondary mt-3 mb-5">Back to Dashboard</a>
    </div>
    <!-- Include jQuery and Bootstrap JS for components -->
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <!-- Bootstrap 5 JS -->
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha3/dist/js/bootstrap.bundle.min.js"></script>
    <!-- Moment.js for date manipulation -->
    <script src="https://cdn.jsdelivr.net/momentjs/latest/moment.min.js"></script>
    <!-- Date Range Picker JS -->
    <script src="https://cdn.jsdelivr.net/npm/daterangepicker/daterangepicker.min.js"></script>
    <!-- Custom JS scripts -->
    <script>
        $(document).ready(function() {
            // Initialize Date Range Picker
            $('#daterange').daterangepicker({
                startDate: moment('<?= htmlspecialchars($start_date) ?>'),
                endDate: moment('<?= htmlspecialchars($end_date) ?>'),
                locale: {
                    format: 'YYYY-MM-DD'
                }
            }, function(start, end, label) {
                $('#start_date').val(start.format('YYYY-MM-DD'));
                $('#end_date').val(end.format('YYYY-MM-DD'));
            });

            // Reset filters by reloading the page without any GET parameters
            $('#resetFilters').click(function() {
                window.location.href = 'admin_report.php';
            });

            // Generate PDF Report functionality
            $('#generatePDF').click(function() {
                // Append a hidden input to indicate PDF generation
                var form = $('#filterForm');
                $('<input>').attr({
                    type: 'hidden',
                    name: 'generate_pdf',
                    value: '1'
                }).appendTo(form);

                // Submit the form
                form.submit();
            });

            // Gender Distribution Chart
            var genderCtx = document.getElementById('genderChart').getContext('2d');
            var genderChart = new Chart(genderCtx, {
                type: 'pie',
                data: {
                    labels: <?= json_encode(array_column($gender_distribution, 'gender')) ?>,
                    datasets: [{
                        data: <?= json_encode(array_column($gender_distribution, 'count')) ?>,
                        backgroundColor: ['#007bff', '#dc3545', '#28a745'] // Colors for Male, Female, Other
                    }]
                },
                options: {
                    responsive: true,
                    maintainAspectRatio: false,
                    plugins: {
                        legend: {
                            position: 'bottom'
                        }
                    }
                }
            });

            // Age Distribution Chart (Histogram)
            var ageCtx = document.getElementById('ageChart').getContext('2d');
            var ages = <?= json_encode($age_distribution) ?>;
            var ageCounts = {};
            ages.forEach(function(age) {
                var ageGroup = Math.floor(age / 10) * 10;
                var label = ageGroup + '-' + (ageGroup + 9);
                if (!ageCounts[label]) {
                    ageCounts[label] = 0;
                }
                ageCounts[label]++;
            });
            var ageLabels = Object.keys(ageCounts);
            var ageData = Object.values(ageCounts);

            var ageChart = new Chart(ageCtx, {
                type: 'bar',
                data: {
                    labels: ageLabels,
                    datasets: [{
                        label: 'Number of Patients',
                        data: ageData,
                        backgroundColor: '#ffc107'
                    }]
                },
                options: {
                    responsive: true,
                    maintainAspectRatio: false,
                    scales: {
                        y: {
                            beginAtZero: true,
                            stepSize: 1
                        }
                    },
                    plugins: {
                        legend: {
                            display: false
                        }
                    }
                }
            });

            // Status Distribution Chart
            var statusCtx = document.getElementById('statusChart').getContext('2d');
            var statusChart = new Chart(statusCtx, {
                type: 'pie',
                data: {
                    labels: <?= json_encode(array_column($status_distribution, 'status')) ?>,
                    datasets: [{
                        data: <?= json_encode(array_column($status_distribution, 'count')) ?>,
                        backgroundColor: [
                            '#FF6384', '#36A2EB', '#FFCE56',
                            '#4BC0C0', '#9966FF', '#FF9F40',
                            '#C9CBCF', '#8D6E63', '#7986CB',
                            '#81C784', '#DCE775', '#FFB74D', '#BA68C8'
                        ] // Extend colors as needed
                    }]
                },
                options: {
                    responsive: true,
                    maintainAspectRatio: false,
                    plugins: {
                        legend: {
                            position: 'bottom'
                        }
                    }
                }
            });

            // Services Rendered Distribution Chart
            var servicesCtx = document.getElementById('servicesChart').getContext('2d');
            var servicesLabels = <?= json_encode(array_column($services_distribution, 'service_name')) ?>;
            var servicesData = <?= json_encode(array_column($services_distribution, 'count')) ?>;
            var servicesChart = new Chart(servicesCtx, {
                type: 'pie',
                data: {
                    labels: servicesLabels,
                    datasets: [{
                        data: servicesData,
                        backgroundColor: [
                            '#007bff', '#dc3545', '#ffc107',
                            '#28a745', '#17a2b8', '#6f42c1',
                            '#fd7e14', '#20c997', '#6610f2',
                            '#e83e8c'
                        ] // Extend colors as needed
                    }]
                },
                options: {
                    responsive: true,
                    maintainAspectRatio: false,
                    plugins: {
                        legend: {
                            position: 'bottom'
                        }
                    }
                }
            });

            // Invoice Amount Trend Chart (Line)
            var invoiceTrendCtx = document.getElementById('invoiceTrendChart').getContext('2d');
            var invoiceTrendLabels = <?= json_encode(array_column($invoice_trend, 'date')) ?>;
            var invoiceTrendData = <?= json_encode(array_column($invoice_trend, 'total_amount')) ?>;
            var invoiceTrendChart = new Chart(invoiceTrendCtx, {
                type: 'line',
                data: {
                    labels: invoiceTrendLabels,
                    datasets: [{
                        label: 'Total Invoice Amount ($)',
                        data: invoiceTrendData,
                        backgroundColor: 'rgba(0, 123, 255, 0.2)',
                        borderColor: '#007bff',
                        fill: true,
                        tension: 0.2
                    }]
                },
                options: {
                    responsive: true,
                    maintainAspectRatio: false,
                    scales: {
                        x: {
                            type: 'time',
                            time: {
                                unit: 'day',
                                displayFormats: {
                                    day: 'YYYY-MM-DD'
                                }
                            },
                            title: {
                                display: true,
                                text: 'Date'
                            }
                        },
                        y: {
                            beginAtZero: true,
                            title: {
                                display: true,
                                text: 'Amount ($)'
                            }
                        }
                    },
                    plugins: {
                        legend: {
                            display: true,
                            position: 'top'
                        }
                    }
                }
            });

            // Patient Registration Trend Chart (Line)
            var registrationTrendCtx = document.getElementById('registrationTrendChart').getContext('2d');
            var registrationTrendLabels = <?= json_encode(array_column($registration_trend, 'date')) ?>;
            var registrationTrendData = <?= json_encode(array_column($registration_trend, 'count')) ?>;
            var registrationTrendChart = new Chart(registrationTrendCtx, {
                type: 'line',
                data: {
                    labels: registrationTrendLabels,
                    datasets: [{
                        label: 'New Registrations',
                        data: registrationTrendData,
                        backgroundColor: 'rgba(40, 167, 69, 0.2)',
                        borderColor: '#28a745',
                        fill: true,
                        tension: 0.2
                    }]
                },
                options: {
                    responsive: true,
                    maintainAspectRatio: false,
                    scales: {
                        x: {
                            type: 'time',
                            time: {
                                unit: 'day',
                                displayFormats: {
                                    day: 'YYYY-MM-DD'
                                }
                            },
                            title: {
                                display: true,
                                text: 'Date'
                            }
                        },
                        y: {
                            beginAtZero: true,
                            title: {
                                display: true,
                                text: 'Number of Registrations'
                            }
                        }
                    },
                    plugins: {
                        legend: {
                            display: true,
                            position: 'top'
                        }
                    }
                }
            });
        });
    </script>
</body>
</html>) i need t ochange all layout and make it cool design and i have folder called includes, this folder has header and footer php, add to this codes as header and footer and make sure everything is working well and use this style colors (/* Header Styles */
:root {
    --lgc-primary-color: #4F3F02;
    --lgc-secondary-color: #D1C287;
    --lgc-background-color: #f8f9fa;
    --lgc-text-color: #333333;
    --lgc-navbar-height: 60px;
}

body {
    font-family: 'Poppins', sans-serif !important;
    background-color: var(--lgc-background-color) !important;
    color: var(--lgc-text-color) !important;
    margin: 0 !important;
    padding: 0 !important;
    font-size: 14px !important; /* Reduced base font size */
}

.lgc-top-bar {
    background-color: var(--lgc-primary-color) !important;
    padding: 8px 0 !important;
    font-size: 0.8rem !important;
    color: #ffffff !important;
}

.lgc-top-bar a {
    color: var(--lgc-secondary-color) !important;
    margin-right: 15px !important;
    transition: color 0.3s ease !important;
    font-size: 0.75rem !important;
}

.lgc-top-bar a:hover {
    color: #ffffff !important;
    text-decoration: none !important;
}

.lgc-welcome-message {
    font-weight: 500 !important;
    color: #ffffff !important;
    font-size: 0.8rem !important;
}

.lgc-navbar-custom {
    background-color: var(--lgc-primary-color) !important;
    padding: 0.4rem 1rem !important;
    box-shadow: 0 2px 10px rgba(0,0,0,0.1) !important;
}

.lgc-navbar-brand {
    display: flex !important;
    align-items: center !important;
}

.lgc-navbar-brand img {
    max-height: 25px !important;
    margin-right: 8px !important;
    transition: transform 0.3s ease !important;
}

.lgc-navbar-brand:hover img {
    transform: scale(1.05) !important;
}

.lgc-nav-link {
    color: var(--lgc-secondary-color) !important;
    font-weight: 500 !important;
    padding: 0.4rem 0.8rem !important;
    border-radius: 5px !important;
    transition: all 0.3s ease !important;
    font-size: 0.85rem !important;
}

.lgc-nav-link:hover, .lgc-nav-link:focus {
    color: #ffffff !important;
    background-color: rgba(209, 194, 135, 0.1) !important;
}

.lgc-badge-position {
    position: absolute !important;
    top: 0 !important;
    right: 0 !important;
    transform: translate(50%, -50%) !important;
    font-size: 0.7rem !important;
}

.lgc-search-form {
    position: relative !important;
    margin-left: auto !important;
}

.lgc-search-form .lgc-form-control {
    background-color: rgba(255,255,255,0.1) !important;
    border: none !important;
    color: #ffffff !important;
    padding-left: 2.5rem !important;
    border-radius: 20px !important;
    transition: all 0.3s ease !important;
    font-size: 0.8rem !important;
}

.lgc-search-form .lgc-form-control::placeholder {
    color: rgba(255,255,255,0.7) !important;
}

.lgc-search-form .lgc-fa-search {
    position: absolute !important;
    top: 50% !important;
    left: 1rem !important;
    transform: translateY(-50%) !important;
    color: var(--lgc-secondary-color) !important;
    font-size: 0.8rem !important;
}

.lgc-search-form .lgc-form-control:focus {
    background-color: rgba(255,255,255,0.2) !important;
    box-shadow: 0 0 0 0.2rem rgba(209, 194, 135, 0.25) !important;
}

.lgc-dropdown-menu {
    background-color: var(--lgc-primary-color) !important;
    border: none !important;
    box-shadow: 0 0 10px rgba(0,0,0,0.1) !important;
}

.lgc-dropdown-item {
    color: var(--lgc-secondary-color) !important;
    transition: all 0.3s ease !important;
    font-size: 0.8rem !important;
}

.lgc-dropdown-item:hover {
    background-color: rgba(209, 194, 135, 0.1) !important;
    color: #ffffff !important;
}

@media (max-width: 991.98px) {
    .lgc-navbar-custom {
        padding: 0.4rem !important;
    }

    .lgc-navbar-brand img {
        max-height: 25px !important;
    }

    .lgc-nav-link {
        padding: 0.4rem !important;
    }

    .lgc-search-form {
        margin-top: 0.8rem !important;
        width: 100% !important;
    }

    .lgc-search-form .lgc-form-control {
        width: 100% !important;
    }
}

@media (max-width: 575.98px) {
    .lgc-top-bar {
        font-size: 0.7rem !important;
    }

    .lgc-welcome-message {
        display: none !important;
    }

    .lgc-navbar-brand img {
        max-height: 20px !important;
    }
}) and  don't use the classes names of this styles, just take from fonts and colors ans other things and add more charts 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 12, 2024 at 06:20 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 `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,
  `status` enum('Pending','Confirmed','Completed','Cancelled') DEFAULT 'Pending',
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

--
-- Indexes for dumped tables
--

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

--
-- AUTO_INCREMENT for dumped tables
--

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

--
-- Constraints for dumped tables
--

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

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

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

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

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

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

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

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

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

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

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

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

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

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;) and type full codes