based on this info database (-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: Jan 03, 2025 at 01:22 PM
-- Server version: 10.11.10-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;

--
-- Dumping data for table `customers`
--

INSERT INTO `customers` (`id`, `name`, `contact`, `delivery_address`) VALUES
(1, 'Order_3121', '012345689', 'Qena'),
(2, 'order_3212', '51648963', 'Luxor'),
(3, 'Order_323', '01119235049', 'Qena, Egypt'),
(4, 'Order_3231', '0155648963', 'Luxor'),
(5, 'Order_3548464852', '0110215489', 'Qena'),
(6, 'Order_mahmoud', '50495049', 'Qena'),
(7, 'Ahmed', '01119235049', 'Assuit'),
(8, 'Mahmoud Tawfik', '01119235049', 'Qena, Center Qena'),
(9, 'Ahmed', '0101549463', 'Luxor'),
(10, 'Yossef', '0154949463', 'Aswan'),
(11, 'Mona', '0154984315', 'Qena'),
(12, 'Order_132323', '0119548463', 'CXA'),
(13, 'Mostafa', '154946123', 'Assuit'),
(14, 'Order_66960907', '+96566960907', 'kuwait'),
(15, 'Order_84569845', '+96584569845', 'Kuwait'),
(16, 'Order_84656222', '+96584656222', 'Mashrif'),
(17, 'Order_84549651', '+96584549651', 'Luxor'),
(18, 'Order_94531154', '+96594531154', 'Qena'),
(19, 'Order_56123548', '+96556123548', ''),
(20, 'Order_84562148', '+96584562148', NULL),
(21, 'Order_54632154', '+96554632154', 'Luxor'),
(22, 'Order_84561254', '+96584561254', 'Qena'),
(23, 'Order_54563154', '+96554563154', 'Qena'),
(24, 'Order_21312312', '+96521312312', 'qena'),
(25, 'Order_8485684', '+9658485684', ''),
(26, 'Order_54865215', '+96554865215', 'Meshrfi');

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

--
-- Table structure for table `deals`
--

CREATE TABLE `deals` (
  `id` int(11) NOT NULL,
  `influencer_id` int(11) NOT NULL,
  `deal_type` enum('Sponsorship','Booth','Other') NOT NULL,
  `description` text DEFAULT NULL,
  `compensation` decimal(10,2) DEFAULT 0.00,
  `status` enum('Pending','Confirmed','Completed','Cancelled') DEFAULT 'Pending',
  `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 `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;

--
-- Dumping data for table `drivers`
--

INSERT INTO `drivers` (`id`, `name`, `number`, `is_star`) VALUES
(1, 'Fox توصيل', '+965 9944 6116', 0),
(2, 'Perfect M توصيل 3kd', '+965 555 72147', 0),
(3, 'توصيل ٥', '+965 505 02769', 0),
(4, 'Shipping Company توصيل Delivery', '+965 9940 7010', 0),
(5, 'توصيلكم توصيل Delivery', '+965 517 66244', 0),
(6, 'توصيل الكويت ٢', '+965 557 25712', 0),
(7, 'توصيل الكويت 2.5kd', '+965 6049 4960', 0),
(8, 'كلاسك توصيل', '+965 6969 1459', 0),
(9, 'سايق On Call5 Delivery Freelance', '+965 6698 8976', 1),
(10, 'سايق On Call 3 Delivery Freelance', '+965 555 86442', 0),
(11, 'سايق On Call 2 Delivery Freelance', '+965 555 12101', 0),
(12, 'Delivery توصيل', '+965 9851 8182', 0),
(13, 'Kumar Driver', '+965 6673 8688', 1),
(14, 'Pawan Driver', '+965 9733 1565', 0),
(15, 'توصيل - صلاح', '+965 6576 2337', 0);

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

--
-- Table structure for table `influencers`
--

CREATE TABLE `influencers` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `contact_number` varchar(50) NOT NULL,
  `contact_info` varchar(255) NOT NULL,
  `address` text NOT NULL,
  `social_media` varchar(255) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `delivery_datetime` datetime DEFAULT 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;

--
-- Dumping data for table `influencers`
--

INSERT INTO `influencers` (`id`, `name`, `contact_number`, `contact_info`, `address`, `social_media`, `notes`, `delivery_datetime`, `created_at`, `updated_at`) VALUES
(3, 'Ahmed', '2112121', 'mahmoud@mail.com', 'Qena', 'instagram', 'qwq', '2024-12-09 12:00:00', '2024-12-02 09:41:07', '2024-12-02 09:41:07');

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

--
-- Table structure for table `interactions`
--

CREATE TABLE `interactions` (
  `id` int(11) NOT NULL,
  `influencer_id` int(11) NOT NULL,
  `interaction_date` datetime NOT NULL,
  `method` enum('Email','Phone','In-Person','Social Media','Other') NOT NULL,
  `notes` text DEFAULT 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 `notifications`
--

CREATE TABLE `notifications` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `type` enum('order','post','plan','note','ad') 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,
  `delivery_fee` decimal(10,2) NOT NULL DEFAULT 0.00,
  `free_shipping` tinyint(1) NOT NULL DEFAULT 0,
  `is_paid` tinyint(1) NOT NULL DEFAULT 0,
  `pickup` tinyint(1) NOT NULL DEFAULT 0
) 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;

--
-- Dumping data for table `products`
--

INSERT INTO `products` (`id`, `name`, `size`, `price`, `category`, `thumbnail`) VALUES
(1, 'Shabora', 'Half Kilo', 6.00, 'Cakes', NULL),
(2, 'Shabora', 'One Kilo', 8.00, 'Cakes', NULL),
(3, 'Shabora', 'Small Basket', 8.00, 'Cakes', NULL),
(4, 'Shabora', 'Large Basket', 10.00, 'Cakes', NULL),
(5, 'Shabora', 'Presentation Plate (Ivory)', 14.00, 'Cakes', NULL),
(6, 'Mahalbiya Crunch', '12 Pieces', 12.00, 'Desserts', NULL),
(7, 'Monkey Bread', 'Large', 12.00, 'Breads', NULL),
(8, 'Monkey Bread Mini', '12 Pieces', 6.00, 'Breads', NULL),
(9, 'Monkey Bread Mini', '9 Pieces', 9.00, 'Breads', NULL),
(11, 'Monkey Bread', 'Presentation Plate', 16.00, 'Breads', NULL);

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

--
-- 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;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `username`, `email`, `password`, `role`, `profile_image`) VALUES
(1, 'kaakalia', 'user@example.com', '$2y$10$tEdSPRwL8WTXljuAxNh.m.af/8sHd/ouDB21Bgs1bC7GkbRq/h0D6', 'admin', 'uploads/profile_images/profile_kaakalia_1731274341.png'),
(2, 'mahmoudtawfik', 'mahmoudtawfikh@gmail.com', '$2y$10$0yxg1R.kfbVV7GL4G9/Q3u5PK.cvv8p29R3ggkfUMC2.aRiAXb2cO', 'admin', 'uploads/profile_images/profile_mahmoudtawfik_1731274427.jpg'),
(3, 'haya', 'haya@gmail.com', '$2y$10$ri1wzOhcmT2YVGkoPYjwu.3fY8xayakQEWHjTaAFGkPfDwJ50PIh6', 'admin', 'uploads/profile_images/profile_haya_1731304053.jpg'),
(4, 'ali', NULL, '$2y$10$3Uky8m7Xhbo/740wX.7Fd.qIjboGtRsOvUdfEB0uDBUd1PQrj3yoW', 'staff', NULL);

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

--
-- 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;

--
-- Dumping data for table `workspace_notes`
--

INSERT INTO `workspace_notes` (`id`, `user_id`, `title`, `content`, `created_at`, `updated_at`) VALUES
(2, 2, 'This important notes', 'HaloHaloHaloHaloHaloHaloHaloHaloHaloHalo', '2024-11-11 09:37:10', '2024-11-11 09:37:10');

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

--
-- 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(),
  `priority` enum('Normal','Important','Critical') NOT NULL DEFAULT 'Normal'
) 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;

--
-- Dumping data for table `workspace_posts`
--

INSERT INTO `workspace_posts` (`id`, `user_id`, `platform`, `content`, `media_url`, `scheduled_datetime`, `status`, `created_at`, `updated_at`, `type`) VALUES
(2, 1, 'Instagram', 'Hello', NULL, '2024-11-11 01:19:00', 'Scheduled', '2024-11-10 11:16:10', '2024-11-10 11:16:10', 'Post');

--
-- 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 `deals`
--
ALTER TABLE `deals`
  ADD PRIMARY KEY (`id`),
  ADD KEY `influencer_id` (`influencer_id`);

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

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

--
-- Indexes for table `interactions`
--
ALTER TABLE `interactions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `influencer_id` (`influencer_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=5;

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

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

--
-- AUTO_INCREMENT for table `deals`
--
ALTER TABLE `deals`
  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=16;

--
-- AUTO_INCREMENT for table `influencers`
--
ALTER TABLE `influencers`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `interactions`
--
ALTER TABLE `interactions`
  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=27;

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

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

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

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

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

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

--
-- 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 `deals`
--
ALTER TABLE `deals`
  ADD CONSTRAINT `deals_ibfk_1` FOREIGN KEY (`influencer_id`) REFERENCES `influencers` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `interactions`
--
ALTER TABLE `interactions`
  ADD CONSTRAINT `interactions_ibfk_1` FOREIGN KEY (`influencer_id`) REFERENCES `influencers` (`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 */;) please fix this header (<?php
// includes/header.php

// 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);

// Start the session if not already started
if (session_status() == PHP_SESSION_NONE) {
    session_start();
}

// Include authentication and database connection
require_once __DIR__ . '/auth.php';
require_once __DIR__ . '/db_connect.php';

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

$user_id = $_SESSION['user_id'];
$last_order_id = 0;

// Fetch the last pending order ID to initialize polling
$initial_pending_sql = "
    SELECT o.id 
    FROM orders o
    WHERE o.status = 'Pending' 
    ORDER BY o.id DESC 
    LIMIT 1
";
$initial_result = $conn->query($initial_pending_sql);
if ($initial_result && $initial_result->num_rows > 0) {
    $row = $initial_result->fetch_assoc();
    $last_order_id = intval($row['id']);
}

// Fetch the logged-in user's profile information
$username = 'User'; // Default username
$profile_image = '/assets/images/user-placeholder.png'; // Default profile image

$stmt = $conn->prepare("SELECT username, profile_image FROM users WHERE id = ?");
if ($stmt) {
    $stmt->bind_param("i", $user_id);
    $stmt->execute();
    $result_user = $stmt->get_result();
    if ($result_user && $result_user->num_rows === 1) {
        $user = $result_user->fetch_assoc();
        $username = htmlspecialchars($user['username'], ENT_QUOTES, 'UTF-8');
        if (!empty($user['profile_image']) && file_exists(__DIR__ . '/../' . $user['profile_image'])) {
            // Append file modification time to prevent caching
            $mod_time = filemtime(__DIR__ . '/../' . $user['profile_image']);
            $profile_image = '/' . htmlspecialchars($user['profile_image'], ENT_QUOTES, 'UTF-8') . '?v=' . $mod_time;
        }
    }
    $stmt->close();
} else {
    // Log error or handle accordingly
    error_log("Failed to prepare statement for fetching user data.");
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <!-- Meta Tags for Responsiveness and Accessibility -->
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Admin Dashboard</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">
    <!-- Alpine.js for Interactivity -->
    <script src="https://cdn.jsdelivr.net/npm/alpinejs@3.12.3/dist/cdn.min.js" defer></script>
    
    <!-- Custom Styles for Notification Links and Dropdowns -->
    <style>
        .notification-link {
            display: block;
            color: inherit;
            text-decoration: none;
        }
        .notification-link:hover {
            background-color: #4a5568; /* Tailwind's gray-700 */
        }
        .notification-badge {
            position: absolute;
            top: 0;
            right: 0;
            width: 0.75rem;
            height: 0.75rem;
            background-color: #f56565; /* Tailwind's red-500 */
            border-radius: 9999px;
            animation: pulse 2s infinite;
        }
        @keyframes pulse {
            0%, 100% { transform: scale(0.75); opacity: 1; }
            50% { transform: scale(1.25); opacity: 0.5; }
        }
    </style>
</head>
<body class="bg-gray-100">

    <!-- Audio Element for New Order Alert -->
    <audio id="newOrderAudio" src="/assets/audio/you_have_a_new_order.mp3" preload="auto"></audio>

    <!-- Header -->
    <header class="bg-gray-800 text-white py-4 border-b-4 border-yellow-400 shadow-lg fixed top-0 left-0 right-0 z-50">
        <div class="container mx-auto px-4 flex justify-between items-center">
            <!-- Logo -->
            <div class="flex items-center">
                <a href="/public/dashboard.php" class="flex items-center">
                    <img src="/assets/img/logo.png" alt="Kaak Alia Logo" class="h-12 w-auto mr-2">
                    <span class="text-xl font-bold">Admin Panel</span>
                </a>
            </div>

            <!-- Navigation Links (Desktop) -->
            <nav class="hidden md:flex space-x-4">
                <a href="/public/orders/add_order.php" class="flex items-center bg-yellow-400 text-gray-800 px-4 py-2 rounded-lg hover:bg-yellow-300 shadow transition-all duration-300">
                    <i class="bi bi-plus-circle-fill mr-2"></i> Add Order
                </a>
                <a href="/public/orders/view_orders.php" class="flex items-center bg-blue-500 text-white px-4 py-2 rounded-lg hover:bg-blue-400 shadow transition-all duration-300">
                    <i class="bi bi-eye-fill mr-2"></i> View Orders
                </a>
                <a href="/public/products/manage_products.php" class="flex items-center bg-green-400 text-white px-4 py-2 rounded-lg hover:bg-green-300 shadow transition-all duration-300">
                    <i class="bi bi-box-seam-fill mr-2"></i> Manage Products
                </a>
                <a href="/public/users/manage_users.php" class="flex items-center bg-orange-500 text-white px-4 py-2 rounded-lg hover:bg-orange-400 shadow transition-all duration-300">
                    <i class="bi bi-people-fill mr-2"></i> Manage Users
                </a>
                <a href="/public/reports/generate_reports.php" class="flex items-center bg-pink-500 text-white px-4 py-2 rounded-lg hover:bg-pink-400 shadow transition-all duration-300">
                    <i class="bi bi-bar-chart-fill mr-2"></i> Reports
                </a>
            </nav>

            <!-- Right Side Icons -->
            <div class="flex items-center space-x-4">
                <!-- Notifications Dropdown -->
                <div class="relative" x-data="{ open: false }">
                    <button @click="open = !open" class="relative text-white hover:text-yellow-400 focus:outline-none" aria-haspopup="true" aria-expanded="false" aria-controls="notification-menu" aria-label="Notifications">
                        <i class="bi bi-bell-fill text-2xl"></i>
                        <?php if ($last_order_id > 0): ?>
                            <!-- Notification Badge -->
                            <span class="absolute top-0 right-0 inline-block w-3 h-3 bg-red-500 rounded-full"></span>
                        <?php endif; ?>
                    </button>
                    <!-- Dropdown Menu -->
                    <div 
                        x-show="open" 
                        @click.away="open = false" 
                        id="notification-menu" 
                        class="absolute right-0 mt-2 w-80 bg-gray-800 text-white rounded-lg shadow-lg z-50"
                        x-transition:enter="transition ease-out duration-200"
                        x-transition:enter-start="opacity-0 translate-y-1"
                        x-transition:enter-end="opacity-100 translate-y-0"
                        x-transition:leave="transition ease-in duration-150"
                        x-transition:leave-start="opacity-100 translate-y-0"
                        x-transition:leave-end="opacity-0 translate-y-1"
                    >
                        <div class="py-2 px-4 border-b border-yellow-400">
                            <h3 class="text-lg font-semibold">Notifications</h3>
                        </div>
                        <ul class="max-h-64 overflow-y-auto" id="notificationsList">
                            <?php
                            // Fetch the latest 5 pending orders with their items
                            $notifications_sql = "
                                SELECT DISTINCT o.id, o.order_date, c.name AS customer_name
                                FROM orders o
                                JOIN customers c ON o.customer_id = c.id
                                JOIN order_items oi ON oi.order_id = o.id
                                WHERE o.status = 'Pending'
                                ORDER BY o.id DESC
                                LIMIT 5
                            ";
                            $notifications_result = $conn->query($notifications_sql);
                            if ($notifications_result && $notifications_result->num_rows > 0):
                                while($notification = $notifications_result->fetch_assoc()):
                            ?>
                                    <li class="px-4 py-2 hover:bg-gray-700 cursor-pointer">
                                        <a href="/public/orders/view_order_customer.php?order_id=<?= htmlspecialchars($notification['id'], ENT_QUOTES, 'UTF-8') ?>" class="notification-link flex items-center">
                                            <span class="inline-block w-2 h-2 bg-red-500 rounded-full mr-2"></span>
                                            New Order: <?= htmlspecialchars($notification['customer_name'], ENT_QUOTES, 'UTF-8'); ?> placed on <?= date("d M Y, h:i A", strtotime($notification['order_date'])) ?>
                                        </a>
                                    </li>
                            <?php
                                endwhile;
                            else:
                            ?>
                                <li class="px-4 py-2 text-gray-400">No new notifications</li>
                            <?php endif; ?>
                        </ul>
                        <div class="py-2 px-4 border-t border-yellow-400 text-center">
                            <a href="/public/notifications.php" class="text-yellow-400 hover:underline">View All</a>
                        </div>
                    </div>
                </div>

                <!-- User Profile Dropdown with Subcategories -->
                <div class="relative" x-data="{ open: false }">
                    <button @click="open = !open" class="flex items-center focus:outline-none" aria-haspopup="true" aria-expanded="false" aria-controls="profile-menu">
                        <img src="<?= $profile_image ?>" alt="User Avatar" class="h-10 w-10 rounded-full border-2 border-white hover:border-yellow-400 transition-colors duration-300">
                        <span class="ml-2 hidden lg:inline-block">
                            <?= $username ?>
                        </span>
                        <i class="bi bi-caret-down-fill ml-1 hidden lg:inline-block"></i>
                    </button>
                    <!-- Dropdown Menu -->
                    <div 
                        x-show="open" 
                        @click.away="open = false" 
                        id="profile-menu" 
                        class="absolute right-0 mt-2 w-56 bg-gray-800 text-white rounded-lg shadow-lg z-50"
                        x-transition:enter="transition ease-out duration-200"
                        x-transition:enter-start="opacity-0 translate-y-1"
                        x-transition:enter-end="opacity-100 translate-y-0"
                        x-transition:leave="transition ease-in duration-150"
                        x-transition:leave-start="opacity-100 translate-y-0"
                        x-transition:leave-end="opacity-0 translate-y-1"
                    >
                        <!-- Workspace Dropdown -->
                        <div class="relative" x-data="{ workspaceOpen: false }">
                            <button @click="workspaceOpen = !workspaceOpen" class="w-full flex items-center justify-between px-4 py-2 hover:bg-yellow-400 hover:text-gray-800 rounded-t-lg focus:outline-none">
                                <div class="flex items-center">
                                    <i class="bi bi-briefcase-fill mr-2"></i> WorkSpace
                                </div>
                                <i :class="workspaceOpen ? 'bi bi-caret-up-fill' : 'bi bi-caret-down-fill'"></i>
                            </button>
                            <!-- Submenu for Workspace -->
                            <div 
                                x-show="workspaceOpen" 
                                @click.away="workspaceOpen = false" 
                                class="bg-yellow-400 text-gray-800 rounded-b-lg shadow-lg mt-0 w-full z-50"
                                x-transition:enter="transition ease-out duration-200"
                                x-transition:enter-start="opacity-0 translate-y-1"
                                x-transition:enter-end="opacity-100 translate-y-0"
                                x-transition:leave="transition ease-in duration-150"
                                x-transition:leave-start="opacity-100 translate-y-0"
                                x-transition:leave-end="opacity-0 translate-y-1"
                            >
                                <a href="/public/workspace/business_development/index.php" class="block px-4 py-2 hover:bg-yellow-300 flex items-center">
                                    <i class="bi bi-graph-up mr-2"></i> Business Development
                                </a>
                                <!-- Future Subcategories can be added here -->
                            </div>
                        </div>

                        <a href="/public/drivers/drivers_list.php" class="block px-4 py-2 hover:bg-yellow-400 hover:text-gray-800 flex items-center">
                            <i class="bi bi-car-front mr-2"></i> Drivers
                        </a>

                        <a href="/includes/user_settings.php" class="block px-4 py-2 hover:bg-yellow-400 hover:text-gray-800 flex items-center">
                            <i class="bi bi-gear-fill mr-2"></i> Settings
                        </a>
                        <a href="/public/about_kaakalia/index_en.php" class="block px-4 py-2 hover:bg-yellow-400 hover:text-gray-800 flex items-center">
                            <i class="bi bi-info-circle mr-2"></i> About Kaak Alia
                        </a>
                        
                        <a href="/public/logout.php" class="block px-4 py-2 hover:bg-yellow-400 hover:text-gray-800 flex items-center">
                            <i class="bi bi-box-arrow-right mr-2"></i> Logout
                        </a>
                    </div>
                </div>

                <!-- Mobile Menu Button -->
                <div class="md:hidden">
                    <button id="mobile-menu-button" class="text-white focus:outline-none" aria-label="Toggle Mobile Menu">
                        <i class="bi bi-list text-2xl"></i>
                    </button>
                </div>
            </div>
        </header>

        <!-- Mobile Navigation Menu -->
        <nav id="mobile-menu" class="hidden md:hidden mt-16">
            <ul class="flex flex-col space-y-2 p-4 bg-gray-800">
                <li>
                    <a href="/public/orders/add_order.php" class="flex items-center bg-yellow-400 text-gray-800 px-4 py-2 rounded-lg hover:bg-yellow-300 transition-all duration-300">
                        <i class="bi bi-plus-circle-fill mr-2"></i> Add Order
                    </a>
                </li>
                <li>
                    <a href="/public/orders/view_orders.php" class="flex items-center bg-blue-500 text-white px-4 py-2 rounded-lg hover:bg-blue-400 transition-all duration-300">
                        <i class="bi bi-eye-fill mr-2"></i> View Orders
                    </a>
                </li>
                <li>
                    <a href="/public/products/manage_products.php" class="flex items-center bg-green-400 text-white px-4 py-2 rounded-lg hover:bg-green-300 transition-all duration-300">
                        <i class="bi bi-box-seam-fill mr-2"></i> Manage Products
                    </a>
                </li>
                <li>
                    <a href="/public/users/manage_users.php" class="flex items-center bg-orange-500 text-white px-4 py-2 rounded-lg hover:bg-orange-400 transition-all duration-300">
                        <i class="bi bi-people-fill mr-2"></i> Manage Users
                    </a>
                </li>
                <li>
                    <a href="/public/reports/generate_reports.php" class="flex items-center bg-pink-500 text-white px-4 py-2 rounded-lg hover:bg-pink-400 transition-all duration-300">
                        <i class="bi bi-bar-chart-fill mr-2"></i> Reports
                    </a>
                </li>
                
                <!-- Mobile Workspace Dropdown -->
                <li class="relative" x-data="{ open: false }">
                    <button @click="open = !open" class="w-full flex items-center justify-between bg-gray-700 text-white px-4 py-2 rounded-lg hover:bg-gray-600 transition-all duration-300 focus:outline-none">
                        <span class="flex items-center">
                            <i class="bi bi-briefcase-fill mr-2"></i> WorkSpace
                        </span>
                        <i :class="open ? 'bi-chevron-up' : 'bi-chevron-down'"></i>
                    </button>
                    <!-- Submenu -->
                    <ul 
                        x-show="open" 
                        class="flex flex-col pl-8 mt-2 space-y-2 transition-all duration-300"
                        x-transition:enter="transition ease-out duration-200"
                        x-transition:enter-start="opacity-0 max-h-0"
                        x-transition:enter-end="opacity-100 max-h-screen"
                        x-transition:leave="transition ease-in duration-200"
                        x-transition:leave-start="opacity-100 max-h-screen"
                        x-transition:leave-end="opacity-0 max-h-0"
                    >
                        <li>
                            <a href="/public/workspace/business_development/index.php" class="flex items-center bg-gray-700 text-white px-4 py-2 rounded-lg hover:bg-gray-600 transition-all duration-300">
                                <i class="bi bi-graph-up mr-2"></i> Business Development
                            </a>
                        </li>
                        <!-- Future Subcategories can be added here -->
                    </ul>
                </li>

                <!-- Additional Mobile Links -->
                <li>
                    <a href="/public/notifications.php" class="flex items-center bg-gray-700 text-white px-4 py-2 rounded-lg hover:bg-gray-600 transition-all duration-300">
                        <i class="bi bi-bell-fill mr-2"></i> Notifications
                    </a>
                </li>
                <li>
                    <a href="/includes/user_settings.php" class="flex items-center bg-gray-700 text-white px-4 py-2 rounded-lg hover:bg-gray-600 transition-all duration-300">
                        <i class="bi bi-gear-fill mr-2"></i> Settings
                    </a>
                </li>
                <li>
                    <a href="/public/about_kaakalia/index_en.php" class="flex items-center bg-gray-700 text-white px-4 py-2 rounded-lg hover:bg-gray-600 transition-all duration-300">
                        <i class="bi bi-info-circle mr-2"></i> About Kaak Alia
                    </a>
                </li>
            </ul>
        </nav>

    <!-- JavaScript for Real-Time Notifications and Mobile Menu Toggle -->
    <script>
        document.addEventListener('DOMContentLoaded', function() {
            // Mobile Menu Toggle
            const mobileMenuButton = document.getElementById('mobile-menu-button');
            const mobileMenu = document.getElementById('mobile-menu');

            mobileMenuButton.addEventListener('click', function() {
                mobileMenu.classList.toggle('hidden');
            });

            let lastOrderId = <?= $last_order_id ?>;
            const audio = document.getElementById('newOrderAudio');

            // Function to fetch new orders
            function fetchNewOrders() {
                fetch(`/includes/get_new_orders.php?last_order_id=${lastOrderId}`)
                    .then(response => response.json())
                    .then(data => {
                        if (data.orders && data.orders.length > 0) {
                            data.orders.forEach(order => {
                                // Update lastOrderId if needed
                                if (order.id > lastOrderId) {
                                    lastOrderId = order.id;
                                }

                                // Create a new notification item
                                const notificationsList = document.getElementById('notificationsList');
                                const li = document.createElement('li');
                                li.className = 'px-4 py-2 hover:bg-gray-700 cursor-pointer';

                                // Create a link to the order details page
                                const a = document.createElement('a');
                                a.href = `/public/orders/view_order_customer.php?order_id=${encodeURIComponent(order.id)}`;
                                a.className = 'notification-link flex items-center';
                                a.innerHTML = `
                                    <span class="inline-block w-2 h-2 bg-red-500 rounded-full mr-2"></span>
                                    New Order: ${sanitizeHTML(order.customer_name)} placed on ${sanitizeHTML(order.order_date)}
                                `;

                                li.appendChild(a);
                                // Insert the new notification at the top
                                notificationsList.insertBefore(li, notificationsList.firstChild);

                                // Play the audio alert
                                if (audio) {
                                    audio.play().catch(function(error) {
                                        console.log('Audio playback failed:', error);
                                    });
                                }

                                // Optionally, update the notification badge
                                const bellIcon = document.querySelector('header .bi-bell-fill');
                                const existingBadge = bellIcon.parentNode.querySelector('.notification-badge');
                                if (!existingBadge) {
                                    const badge = document.createElement('span');
                                    badge.className = 'notification-badge';
                                    bellIcon.parentNode.appendChild(badge);
                                }
                            });
                        }
                    })
                    .catch(error => {
                        console.error('Error fetching new orders:', error);
                    });
            }

            // Function to sanitize HTML to prevent XSS
            function sanitizeHTML(str) {
                var temp = document.createElement('div');
                temp.textContent = str;
                return temp.innerHTML;
            }

            // Poll every 10 seconds for new orders
            setInterval(fetchNewOrders, 10000); // 10000 ms = 10 seconds

            // Fetch immediately on page load
            fetchNewOrders();
        });
    </script>
</body>
</html>) and amake workspace clickable to (public/workspace/index.php) and mkae it header flexbliabe for all pages, and amke it not confilcti with other elements on page and make it cool