in this codes (<?php
// /public/workspace/index.php

// Enable error reporting for debugging (disable in production)
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

// Start output buffering to prevent "headers already sent" errors
ob_start();

// Include authentication, database connection, and utility functions
require_once __DIR__ . '/../../includes/auth.php';
require_once __DIR__ . '/../../includes/db_connect.php';
require_once __DIR__ . '/../../includes/functions.php'; // Contains sanitize_input()

// Ensure the user is authenticated
if (!isset($_SESSION['user_id'])) {
    header("Location: /public/login.php");
    exit();
}

$user_id = $_SESSION['user_id'];
$user_role = $_SESSION['role']; // 'admin' or 'staff'
$calendar_events = []; // Initialize calendar events array
$errors = [];

// Initialize counts
$plans_count = $posts_count = $notes_count = $ads_count = 0;

// Fetch summary data: Total Number of Plans, Posts, Notes, Ads
try {
    // Total Plans
    if ($user_role === 'admin') {
        $plans_count_sql = "SELECT COUNT(*) AS total FROM workspace_plans";
    } else {
        $plans_count_sql = "SELECT COUNT(*) AS total FROM workspace_plans WHERE user_id = ?";
    }
    if ($stmt = $conn->prepare($plans_count_sql)) {
        if ($user_role !== 'admin') {
            $stmt->bind_param("i", $user_id);
        }
        $stmt->execute();
        $result = $stmt->get_result();
        $plans_count = $result->fetch_assoc()['total'] ?? 0;
        $stmt->close();
    } else {
        throw new Exception("Prepare statement failed for plans count: " . $conn->error);
    }

    // Total Posts
    if ($user_role === 'admin') {
        $posts_count_sql = "SELECT COUNT(*) AS total FROM workspace_posts";
    } else {
        $posts_count_sql = "SELECT COUNT(*) AS total FROM workspace_posts WHERE user_id = ?";
    }
    if ($stmt = $conn->prepare($posts_count_sql)) {
        if ($user_role !== 'admin') {
            $stmt->bind_param("i", $user_id);
        }
        $stmt->execute();
        $result = $stmt->get_result();
        $posts_count = $result->fetch_assoc()['total'] ?? 0;
        $stmt->close();
    } else {
        throw new Exception("Prepare statement failed for posts count: " . $conn->error);
    }

    // Total Notes
    if ($user_role === 'admin') {
        $notes_count_sql = "SELECT COUNT(*) AS total FROM workspace_notes";
    } else {
        $notes_count_sql = "SELECT COUNT(*) AS total FROM workspace_notes WHERE user_id = ?";
    }
    if ($stmt = $conn->prepare($notes_count_sql)) {
        if ($user_role !== 'admin') {
            $stmt->bind_param("i", $user_id);
        }
        $stmt->execute();
        $result = $stmt->get_result();
        $notes_count = $result->fetch_assoc()['total'] ?? 0;
        $stmt->close();
    } else {
        throw new Exception("Prepare statement failed for notes count: " . $conn->error);
    }

    // Total Ads
    if ($user_role === 'admin') {
        $ads_count_sql = "SELECT COUNT(*) AS total FROM ads";
    } else {
        $ads_count_sql = "SELECT COUNT(*) AS total FROM ads WHERE user_id = ?";
    }
    if ($stmt = $conn->prepare($ads_count_sql)) {
        if ($user_role !== 'admin') {
            $stmt->bind_param("i", $user_id);
        }
        $stmt->execute();
        $result = $stmt->get_result();
        $ads_count = $result->fetch_assoc()['total'] ?? 0;
        $stmt->close();
    } else {
        throw new Exception("Prepare statement failed for ads count: " . $conn->error);
    }

} catch (Exception $e) {
    $errors[] = $e->getMessage();
    $plans_count = $posts_count = $notes_count = $ads_count = 0; // Default values in case of error
}

// Fetch upcoming plans and posts for the calendar along with creator's username
try {
    // Fetch Plans
    if ($user_role === 'admin') {
        $calendar_plans_sql = "SELECT wp.id, wp.title, wp.due_date, u.username 
                               FROM workspace_plans wp
                               JOIN users u ON wp.user_id = u.id
                               WHERE wp.due_date >= NOW()
                               ORDER BY wp.due_date ASC";
    } else {
        $calendar_plans_sql = "SELECT wp.id, wp.title, wp.due_date, u.username 
                               FROM workspace_plans wp
                               JOIN users u ON wp.user_id = u.id
                               WHERE wp.due_date >= NOW() AND wp.user_id = ?
                               ORDER BY wp.due_date ASC";
    }
    $stmt = $conn->prepare($calendar_plans_sql);
    if ($stmt) {
        if ($user_role !== 'admin') {
            $stmt->bind_param("i", $user_id);
        }
        $stmt->execute();
        $result = $stmt->get_result();

        while ($plan = $result->fetch_assoc()) {
            $calendar_events[] = [
                'id'    => 'plan_' . $plan['id'], // Prefix to distinguish type
                'title' => sanitize_input($plan['title']) . " (by " . sanitize_input($plan['username']) . ")",
                'start' => $plan['due_date'],
                'type'  => 'plan', // Custom property to identify type
                'color' => '#3B82F6' // Tailwind Blue-500
            ];
        }
        $stmt->close();
    } else {
        throw new Exception("Prepare statement failed for calendar plans: " . $conn->error);
    }

    // Fetch Posts
    if ($user_role === 'admin') {
        $calendar_posts_sql = "SELECT wp.id, CONCAT(wp.platform, ': ', wp.content) AS content, wp.scheduled_datetime, u.username 
                               FROM workspace_posts wp
                               JOIN users u ON wp.user_id = u.id
                               WHERE wp.scheduled_datetime >= NOW()
                               ORDER BY wp.scheduled_datetime ASC";
    } else {
        $calendar_posts_sql = "SELECT wp.id, CONCAT(wp.platform, ': ', wp.content) AS content, wp.scheduled_datetime, u.username 
                               FROM workspace_posts wp
                               JOIN users u ON wp.user_id = u.id
                               WHERE wp.scheduled_datetime >= NOW() AND wp.user_id = ?
                               ORDER BY wp.scheduled_datetime ASC";
    }
    $stmt = $conn->prepare($calendar_posts_sql);
    if ($stmt) {
        if ($user_role !== 'admin') {
            $stmt->bind_param("i", $user_id);
        }
        $stmt->execute();
        $result = $stmt->get_result();

        while ($post = $result->fetch_assoc()) {
            $calendar_events[] = [
                'id'    => 'post_' . $post['id'], // Prefix to distinguish type
                'title' => sanitize_input($post['content']) . " (by " . sanitize_input($post['username']) . ")",
                'start' => $post['scheduled_datetime'],
                'type'  => 'post', // Custom property to identify type
                'color' => '#10B981' // Tailwind Green-500
            ];
        }
        $stmt->close();
    } else {
        throw new Exception("Prepare statement failed for calendar posts: " . $conn->error);
    }
} catch (Exception $e) {
    $errors[] = $e->getMessage();
}

// (Optional) Fetch recent activities: Plans, Posts, Notes, Ads
$recent_plans = [];
$recent_posts = [];
$recent_notes = [];
$recent_ads = [];
$items_per_section = 5; // Number of items to display in each recent section

try {
    // Recent Plans
    if ($user_role === 'admin') {
        $recent_plans_sql = "SELECT wp.*, u.username 
                             FROM workspace_plans wp
                             JOIN users u ON wp.user_id = u.id
                             ORDER BY wp.created_at DESC
                             LIMIT ?";
        $stmt = $conn->prepare($recent_plans_sql);
        $stmt->bind_param("i", $items_per_section);
    } else {
        $recent_plans_sql = "SELECT wp.*, u.username 
                             FROM workspace_plans wp
                             JOIN users u ON wp.user_id = u.id
                             WHERE wp.user_id = ?
                             ORDER BY wp.created_at DESC
                             LIMIT ?";
        $stmt = $conn->prepare($recent_plans_sql);
        $stmt->bind_param("ii", $user_id, $items_per_section);
    }

    if ($stmt) {
        $stmt->execute();
        $result = $stmt->get_result();
        while ($plan = $result->fetch_assoc()) {
            $recent_plans[] = $plan;
        }
        $stmt->close();
    }

    // Recent Posts
    if ($user_role === 'admin') {
        $recent_posts_sql = "SELECT wp.*, u.username 
                             FROM workspace_posts wp
                             JOIN users u ON wp.user_id = u.id
                             ORDER BY wp.created_at DESC
                             LIMIT ?";
        $stmt = $conn->prepare($recent_posts_sql);
        $stmt->bind_param("i", $items_per_section);
    } else {
        $recent_posts_sql = "SELECT wp.*, u.username 
                             FROM workspace_posts wp
                             JOIN users u ON wp.user_id = u.id
                             WHERE wp.user_id = ?
                             ORDER BY wp.created_at DESC
                             LIMIT ?";
        $stmt = $conn->prepare($recent_posts_sql);
        $stmt->bind_param("ii", $user_id, $items_per_section);
    }

    if ($stmt) {
        $stmt->execute();
        $result = $stmt->get_result();
        while ($post = $result->fetch_assoc()) {
            $recent_posts[] = $post;
        }
        $stmt->close();
    }

    // Recent Notes
    if ($user_role === 'admin') {
        $recent_notes_sql = "SELECT wn.*, u.username 
                             FROM workspace_notes wn
                             JOIN users u ON wn.user_id = u.id
                             ORDER BY wn.created_at DESC
                             LIMIT ?";
        $stmt = $conn->prepare($recent_notes_sql);
        $stmt->bind_param("i", $items_per_section);
    } else {
        $recent_notes_sql = "SELECT wn.*, u.username 
                             FROM workspace_notes wn
                             JOIN users u ON wn.user_id = u.id
                             WHERE wn.user_id = ?
                             ORDER BY wn.created_at DESC
                             LIMIT ?";
        $stmt = $conn->prepare($recent_notes_sql);
        $stmt->bind_param("ii", $user_id, $items_per_section);
    }

    if ($stmt) {
        $stmt->execute();
        $result = $stmt->get_result();
        while ($note = $result->fetch_assoc()) {
            $recent_notes[] = $note;
        }
        $stmt->close();
    }

    // Recent Ads
    if ($user_role === 'admin') {
        $recent_ads_sql = "SELECT a.*, u.username 
                           FROM ads a
                           JOIN users u ON a.user_id = u.id
                           ORDER BY a.created_at DESC
                           LIMIT ?";
        $stmt = $conn->prepare($recent_ads_sql);
        $stmt->bind_param("i", $items_per_section);
    } else {
        $recent_ads_sql = "SELECT a.*, u.username 
                           FROM ads a
                           JOIN users u ON a.user_id = u.id
                           WHERE a.user_id = ?
                           ORDER BY a.created_at DESC
                           LIMIT ?";
        $stmt = $conn->prepare($recent_ads_sql);
        $stmt->bind_param("ii", $user_id, $items_per_section);
    }

    if ($stmt) {
        $stmt->execute();
        $result = $stmt->get_result();
        while ($ad = $result->fetch_assoc()) {
            $recent_ads[] = $ad;
        }
        $stmt->close();
    }

} catch (Exception $e) {
    $errors[] = $e->getMessage();
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Workspace Dashboard - Kaak Alia Management System</title>
    <!-- Tailwind CSS -->
    <link href="https://cdn.jsdelivr.net/npm/tailwindcss@2.2.19/dist/tailwind.min.css" rel="stylesheet">
    <!-- Bootstrap Icons -->
    <link href="https://cdn.jsdelivr.net/npm/bootstrap-icons/font/bootstrap-icons.css" rel="stylesheet">
    <!-- FullCalendar CSS -->
    <link href='https://cdn.jsdelivr.net/npm/fullcalendar@5.11.3/main.min.css' rel='stylesheet' />
    <!-- Custom Styles -->
    <style>
        body {
            font-family: 'Cairo', sans-serif;
        }
        #calendar {
            max-width: 100%;
            margin: 0 auto;
            background-color: #ffffff;
            border-radius: 8px;
            box-shadow: 0 4px 6px rgba(0,0,0,0.1);
            padding: 20px;
        }
    </style>
</head>
<body class="bg-gray-100">
    <!-- Header -->
    <?php include __DIR__ . '/../../includes/header.php'; ?>

    <!-- Main Content -->
    <main class="container mx-auto p-6 mt-24 mb-20">
        <h1 class="text-3xl font-bold mb-6">Workspace Dashboard</h1>

        <!-- Display Errors -->
        <?php if (!empty($errors)): ?>
            <div class="mb-6">
                <?php foreach ($errors as $error): ?>
                    <div class="bg-red-100 border border-red-400 text-red-700 px-4 py-3 rounded mb-2">
                        <?= htmlspecialchars($error) ?>
                    </div>
                <?php endforeach; ?>
            </div>
        <?php endif; ?>

        <!-- Summary Cards -->
        <div class="grid grid-cols-1 md:grid-cols-4 gap-6 mb-6">
            <!-- Plans -->
            <div class="flex items-center p-4 bg-blue-500 text-white rounded-lg shadow hover:bg-blue-600 transition">
                <i class="bi bi-list-task text-4xl mr-4"></i>
                <div>
                    <h2 class="text-xl font-semibold">Total Plans</h2>
                    <p class="text-2xl"><?= htmlspecialchars(number_format($plans_count)) ?></p>
                </div>
            </div>
            <!-- Posts -->
            <div class="flex items-center p-4 bg-green-500 text-white rounded-lg shadow hover:bg-green-600 transition">
                <i class="bi bi-calendar-plus text-4xl mr-4"></i>
                <div>
                    <h2 class="text-xl font-semibold">Total Posts</h2>
                    <p class="text-2xl"><?= htmlspecialchars(number_format($posts_count)) ?></p>
                </div>
            </div>
            <!-- Notes -->
            <div class="flex items-center p-4 bg-purple-500 text-white rounded-lg shadow hover:bg-purple-600 transition">
                <i class="bi bi-stickies text-4xl mr-4"></i>
                <div>
                    <h2 class="text-xl font-semibold">Total Notes</h2>
                    <p class="text-2xl"><?= htmlspecialchars(number_format($notes_count)) ?></p>
                </div>
            </div>
            <!-- Ads -->
            <div class="flex items-center p-4 bg-red-500 text-white rounded-lg shadow hover:bg-red-600 transition">
                <i class="bi bi-megaphone text-4xl mr-4"></i>
                <div>
                    <h2 class="text-xl font-semibold">Total Ads</h2>
                    <p class="text-2xl"><?= htmlspecialchars(number_format($ads_count)) ?></p>
                </div>
            </div>
        </div>

        <!-- Quick Links -->
        <div class="grid grid-cols-1 md:grid-cols-4 gap-6 mb-6">
            <a href="plans.php" class="block p-6 bg-blue-400 text-white rounded-lg shadow hover:bg-blue-500 transition">
                <i class="bi bi-list-task text-3xl"></i>
                <h2 class="mt-2 text-xl font-semibold">Manage Plans</h2>
            </a>
            <a href="posts.php" class="block p-6 bg-green-400 text-white rounded-lg shadow hover:bg-green-500 transition">
                <i class="bi bi-calendar-plus text-3xl"></i>
                <h2 class="mt-2 text-xl font-semibold">Manage Posts</h2>
            </a>
            <a href="notes.php" class="block p-6 bg-purple-400 text-white rounded-lg shadow hover:bg-purple-500 transition">
                <i class="bi bi-stickies text-3xl"></i>
                <h2 class="mt-2 text-xl font-semibold">Manage Notes</h2>
            </a>
            <a href="ads/index.php" class="block p-6 bg-red-400 text-white rounded-lg shadow hover:bg-red-500 transition">
                <i class="bi bi-megaphone text-3xl"></i>
                <h2 class="mt-2 text-xl font-semibold">Manage Ads</h2>
            </a>
        </div>

        <!-- Calendar -->
        <div class="mb-6">
            <h2 class="text-2xl font-bold mb-4">Upcoming Plans & Posts</h2>
            <div id='calendar'></div>
        </div>

        <!-- (Optional) Recent Activities -->
        <div class="grid grid-cols-1 md:grid-cols-4 gap-6 mb-6">
            <!-- Recent Plans -->
            <div>
                <h3 class="text-xl font-semibold mb-2">Recent Plans</h3>
                <?php if (!empty($recent_plans)): ?>
                    <ul>
                        <?php foreach ($recent_plans as $plan): ?>
                            <li class="mb-2">
                                <span class="font-semibold"><?= htmlspecialchars($plan['title']) ?></span> 
                                by <span class="text-gray-700"><?= htmlspecialchars($plan['username']) ?></span>
                                <br>
                                <span class="text-sm text-gray-500"><?= htmlspecialchars(date("d M Y, h:i A", strtotime($plan['created_at']))) ?></span>
                            </li>
                        <?php endforeach; ?>
                    </ul>
                <?php else: ?>
                    <p class="text-gray-500">No recent plans found.</p>
                <?php endif; ?>
            </div>
            <!-- Recent Posts -->
            <div>
                <h3 class="text-xl font-semibold mb-2">Recent Posts</h3>
                <?php if (!empty($recent_posts)): ?>
                    <ul>
                        <?php foreach ($recent_posts as $post): ?>
                            <li class="mb-2">
                                <span class="font-semibold"><?= htmlspecialchars($post['platform']) ?></span>: 
                                <?= htmlspecialchars(substr($post['content'], 0, 50)) ?>...
                                by <span class="text-gray-700"><?= htmlspecialchars($post['username']) ?></span>
                                <br>
                                <span class="text-sm text-gray-500"><?= htmlspecialchars(date("d M Y, h:i A", strtotime($post['created_at']))) ?></span>
                            </li>
                        <?php endforeach; ?>
                    </ul>
                <?php else: ?>
                    <p class="text-gray-500">No recent posts found.</p>
                <?php endif; ?>
            </div>
            <!-- Recent Notes -->
            <div>
                <h3 class="text-xl font-semibold mb-2">Recent Notes</h3>
                <?php if (!empty($recent_notes)): ?>
                    <ul>
                        <?php foreach ($recent_notes as $note): ?>
                            <li class="mb-2">
                                <span class="font-semibold"><?= htmlspecialchars($note['title']) ?></span> 
                                by <span class="text-gray-700"><?= htmlspecialchars($note['username']) ?></span>
                                <br>
                                <span class="text-sm text-gray-500"><?= htmlspecialchars(date("d M Y, h:i A", strtotime($note['created_at']))) ?></span>
                            </li>
                        <?php endforeach; ?>
                    </ul>
                <?php else: ?>
                    <p class="text-gray-500">No recent notes found.</p>
                <?php endif; ?>
            </div>
            <!-- Recent Ads -->
            <div>
                <h3 class="text-xl font-semibold mb-2">Recent Ads</h3>
                <?php if (!empty($recent_ads)): ?>
                    <ul>
                        <?php foreach ($recent_ads as $ad_item): ?>
                            <li class="mb-2">
                                <span class="font-semibold"><?= htmlspecialchars($ad_item['name']) ?></span> 
                                by <span class="text-gray-700"><?= htmlspecialchars($ad_item['username']) ?></span>
                                <br>
                                <span class="text-sm text-gray-500"><?= htmlspecialchars(date("d M Y, h:i A", strtotime($ad_item['created_at']))) ?></span>
                            </li>
                        <?php endforeach; ?>
                    </ul>
                <?php else: ?>
                    <p class="text-gray-500">No recent ads found.</p>
                <?php endif; ?>
            </div>
        </div>
    </main>

    <!-- Footer -->
    <footer class="fixed bottom-0 left-0 w-full bg-gray-800 text-white text-center p-4">
        &copy; 2024 Kaak Alia Bakery. All rights reserved.
    </footer>

    <!-- FullCalendar JS -->
    <script src='https://cdn.jsdelivr.net/npm/fullcalendar@5.11.3/main.min.js'></script>
    <script>
        document.addEventListener('DOMContentLoaded', function() {
            var calendarEl = document.getElementById('calendar');
            var calendarData = <?= json_encode($calendar_events) ?>;
            var calendar = new FullCalendar.Calendar(calendarEl, {
                initialView: 'dayGridMonth',
                events: calendarData,
                headerToolbar: {
                    left: 'prev,next today',
                    center: 'title',
                    right: 'dayGridMonth,timeGridWeek,timeGridDay'
                },
                eventClick: function(info) {
                    // Determine the type of the event and redirect accordingly
                    const eventType = info.event.extendedProps.type;
                    const eventId = info.event.id.split('_')[1]; // Extract the numeric ID

                    if(eventType === 'plan'){
                        window.location.href = 'view_plan.php?id=' + eventId;
                    } else if(eventType === 'post'){
                        window.location.href = 'view_post.php?id=' + eventId;
                    } else {
                        // Default action or handle other types if necessary
                        alert('Unknown event type.');
                    }
                },
                eventColor: '#06D6A0', // Default event color (can be overridden by individual event colors)
                navLinks: true, // Can click day/week names to navigate views
                selectable: true,
                selectMirror: true,
                select: function(arg) {
                    // Handle date selection for creating new plans
                    if(confirm('Would you like to add a new plan on ' + arg.startStr + '?')) {
                        window.location.href = 'create_plan.php?due_date=' + encodeURIComponent(arg.startStr);
                    }
                    calendar.unselect()
                },
                // Responsive Design Enhancements
                height: 'auto',
                windowResize: function(view) {
                    calendar.render();
                }
            });
            calendar.render();
        });
    </script>
</body>
</html>

<?php
// Flush the output buffer
ob_end_flush();
// No closing PHP tag to prevent unwanted output
?>) i wnat (To improve the "Workspace Dashboard" page and make it look more organized and visually appealing, here are some detailed suggestions:

1. Redesign Dashboard Cards:
Colors: Use a more consistent color scheme that aligns with the brand colors or introduces a gradient for each card to make them more vibrant.
Icons: Add more recognizable icons on each card to represent the sections (plans, posts, notes, ads) more visually.
Animation: Add a hover effect to the cards (e.g., slight scaling or shadow effect) to give a sense of interactivity.
2. Enhance Overview Sections:
Plans Overview: Replace the empty space with a bar or line graph showing the number of plans created per month. Add labels, tooltips, and a legend for better data understanding.
Posts Overview: Currently, there's a single pie chart for Instagram. Consider expanding this to include different social media platforms (Instagram, Facebook, TikTok) to provide a better overview.
Notes Overview: Include a stacked bar chart or line chart that shows notes by categories (e.g., feedback, tasks, reminders) to give more context.
Ads Budget vs Spent: Improve the visualization with a dual-line graph (one line for the budget and one for the actual spend). Add percentage indicators for the spent amount relative to the budget.
3. Add More Detailed Widgets:
Recent Activities Section:
Expand this section with a table format showing recent actions (e.g., created post, updated plan, added note) with clickable links to view the related item.
Include filters for different types of activities (e.g., 'All', 'Posts', 'Plans') to make it easier to find specific actions.
Summary Widgets:
Add small summary boxes at the top with key statistics (e.g., "Total Plans This Month," "Total Posts Today," "Budget Utilization").
Use dynamic badges or counters to indicate trends (e.g., green for growth, red for decline).
4. Improve Calendar Section:
Interactive Calendar: Enhance the calendar with hover details for each event. When the user hovers over a date with an upcoming plan or post, show a tooltip with a brief summary.
Add Filters: Allow users to filter the calendar by type (plans, posts, notes).
Integrate Actions: Add a "Quick Add" button on each date for users to easily create a new plan or post directly from the calendar.
5. Include a Notifications Widget:
Notifications Panel: Add a notifications section or a pop-up panel that shows pending tasks, upcoming posts, or reminders for ads.
6. UI Enhancements:
Spacing and Alignment: Increase the padding around cards and charts for a cleaner look. Align the charts and graphs for consistency.
7. Detailed Insights Section:
Add a Performance Insight Widget: Provide a small section with insights like "Top Performing Post of the Week," "Most Active Plan," or "Best Note Category."
8. Loading Animations:
Add subtle loading animations or placeholders while data is being fetched, especially for charts and recent activities.
9. Better Typography:
Use a modern, clean font style with different weights to make headings stand out. Consider using a mix of bold for titles and regular for content.
10. Customizable Dashboard:
Allow users to customize their dashboard layout by dragging and dropping widgets or rearranging sections based on their preferences.
By implementing these suggestions, the page will not only look more visually appealing but also become more functional and user-friendly. Let me know if you want detailed code or design mockups for any of these ideas!

) don't add toggle dark mode, and add recent activites (activities.php), and this info of database (-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: Nov 11, 2024 at 06:48 AM
-- Server version: 10.11.9-MariaDB
-- PHP Version: 7.2.34

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


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

--
-- Database: `u406552082_kaakalia`
--

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

--
-- Table structure for table `ads`
--

CREATE TABLE `ads` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `ad_type` enum('Post','Story') NOT NULL,
  `related_id` int(11) NOT NULL,
  `budget` decimal(10,2) NOT NULL,
  `total_spent` decimal(10,2) DEFAULT 0.00,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `status` enum('Active','Paused','Completed') DEFAULT 'Active',
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `ad_spend_logs`
--

CREATE TABLE `ad_spend_logs` (
  `id` int(11) NOT NULL,
  `ad_id` int(11) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `spend_date` datetime DEFAULT current_timestamp(),
  `description` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `customers`
--

CREATE TABLE `customers` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `contact` varchar(50) DEFAULT NULL,
  `delivery_address` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `drivers`
--

CREATE TABLE `drivers` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `number` varchar(15) NOT NULL,
  `is_star` tinyint(1) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `notifications`
--

CREATE TABLE `notifications` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `type` enum('order','post','plan','note') NOT NULL,
  `message` varchar(255) NOT NULL,
  `link` varchar(255) NOT NULL,
  `created_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `orders`
--

CREATE TABLE `orders` (
  `id` int(11) NOT NULL,
  `customer_id` int(11) NOT NULL,
  `status` enum('Pending','In Progress','Completed','Delivered') DEFAULT 'Pending',
  `priority` enum('Normal','Important','Quick') DEFAULT 'Normal',
  `order_date` datetime DEFAULT current_timestamp(),
  `delivery_datetime` datetime DEFAULT NULL,
  `delivery_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `order_items`
--

CREATE TABLE `order_items` (
  `id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `quantity` int(11) NOT NULL DEFAULT 1,
  `packaging_preferences` varchar(255) DEFAULT NULL,
  `discount_percentage` decimal(5,2) DEFAULT 0.00,
  `discount_reason` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `products`
--

CREATE TABLE `products` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `size` varchar(50) DEFAULT NULL,
  `price` decimal(10,2) NOT NULL,
  `category` varchar(50) DEFAULT NULL,
  `thumbnail` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `username` varchar(50) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  `password` varchar(255) NOT NULL,
  `role` enum('admin','staff') NOT NULL,
  `profile_image` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `workspace_notes`
--

CREATE TABLE `workspace_notes` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `workspace_plans`
--

CREATE TABLE `workspace_plans` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `audio_path` varchar(255) DEFAULT NULL,
  `due_date` datetime DEFAULT NULL,
  `status` enum('Not Started','In Progress','Completed') DEFAULT 'Not Started',
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `workspace_posts`
--

CREATE TABLE `workspace_posts` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `platform` enum('Instagram','Facebook','Twitter','Other') NOT NULL,
  `content` text NOT NULL,
  `media_url` varchar(255) DEFAULT NULL,
  `scheduled_datetime` datetime DEFAULT NULL,
  `status` enum('Scheduled','Posted','Failed') DEFAULT 'Scheduled',
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `type` enum('Post','Story') DEFAULT 'Post'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `ads`
--
ALTER TABLE `ads`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `related_id` (`related_id`);

--
-- Indexes for table `ad_spend_logs`
--
ALTER TABLE `ad_spend_logs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `ad_id` (`ad_id`);

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

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

--
-- Indexes for table `notifications`
--
ALTER TABLE `notifications`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `created_at` (`created_at`);

--
-- Indexes for table `orders`
--
ALTER TABLE `orders`
  ADD PRIMARY KEY (`id`),
  ADD KEY `customer_id` (`customer_id`),
  ADD KEY `idx_orders_customer_id` (`customer_id`);

--
-- Indexes for table `order_items`
--
ALTER TABLE `order_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `order_id` (`order_id`),
  ADD KEY `product_id` (`product_id`),
  ADD KEY `idx_order_items_order_id` (`order_id`),
  ADD KEY `idx_order_items_product_id` (`product_id`);

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

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

--
-- Indexes for table `workspace_notes`
--
ALTER TABLE `workspace_notes`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `workspace_plans`
--
ALTER TABLE `workspace_plans`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `workspace_posts`
--
ALTER TABLE `workspace_posts`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`);

--
-- AUTO_INCREMENT for dumped tables
--

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

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

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

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

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

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

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

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

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

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

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

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

--
-- Constraints for dumped tables
--

--
-- Constraints for table `ads`
--
ALTER TABLE `ads`
  ADD CONSTRAINT `fk_ads_related_id` FOREIGN KEY (`related_id`) REFERENCES `workspace_posts` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_ads_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `ad_spend_logs`
--
ALTER TABLE `ad_spend_logs`
  ADD CONSTRAINT `fk_ad_spend_logs_ad_id` FOREIGN KEY (`ad_id`) REFERENCES `ads` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `notifications`
--
ALTER TABLE `notifications`
  ADD CONSTRAINT `notifications_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `orders`
--
ALTER TABLE `orders`
  ADD CONSTRAINT `fk_orders_customer_id` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `order_items`
--
ALTER TABLE `order_items`
  ADD CONSTRAINT `fk_order_items_order_id` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_order_items_product_id` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `order_items_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `order_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `workspace_notes`
--
ALTER TABLE `workspace_notes`
  ADD CONSTRAINT `fk_workspace_notes_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `workspace_plans`
--
ALTER TABLE `workspace_plans`
  ADD CONSTRAINT `fk_workspace_plans_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `workspace_posts`
--
ALTER TABLE `workspace_posts`
  ADD CONSTRAINT `fk_workspace_posts_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE 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