in this (The error you're encountering indicates that the PHP script cannot locate the auth.php file due to an incorrect file path. Specifically, the BASE_PATH constant is undefined, which was introduced in the previous guidance but hasn't been set up in your environment. To resolve this, we'll eliminate the use of BASE_PATH and adjust the file inclusion paths to use correct relative paths based on your directory structure.

Below, you'll find fully corrected codes for all Ads module files within the public/workspace/ads/ directory, along with detailed guides to ensure seamless integration without modifying your existing database or authentication system.

1. Understanding the Directory Structure
Before proceeding, it's crucial to confirm your project's directory structure. Based on your error message and typical PHP project layouts, let's assume the following structure:


/home/u406552082/domains/longevacare.com/public_html/kaakalia/
├── includes/
│   ├── auth.php
│   ├── db_connect.php
│   └── functions.php
├── public/
│   ├── login.php
│   ├── logout.php
│   ├── workspace/
│   │   ├── index.php
│   │   ├── plans.php
│   │   ├── posts.php
│   │   ├── notes.php
│   │   └── ads/
│   │       ├── index.php
│   │       ├── create_ad.php
│   │       ├── edit_ad.php
│   │       ├── delete_ad.php
│   │       └── view_ad.php
│   └── [other public files]
└── [other root files]
Key Points:

The includes directory is located one level up from the public directory.
The Ads module resides within public/workspace/ads/.
Given this structure, to include files from the includes directory within any Ads module file, you need to navigate three levels up from public/workspace/ads/.

2. Correcting the File Paths
A. public/workspace/ads/index.php
Issue:

The original require_once statement uses an incorrect relative path, leading to the error:


require_once __DIR__ . '/../../includes/auth.php';
Correction:

Adjust the path to go three levels up to reach the includes directory:


require_once __DIR__ . '/../../../includes/auth.php';
Full Corrected Code:


<?php
// /public/workspace/ads/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
ob_start();

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

// No additional authentication check needed as it's handled in auth.php

$user_id = $_SESSION['user_id'];
$user_role = $_SESSION['role']; // 'admin' or 'staff'
$ads = [];
$errors = [];

// Fetch Ads
try {
    if ($user_role === 'admin') {
        $ads_sql = "SELECT a.*, wp.title AS post_title, u.username 
                    FROM ads a
                    JOIN workspace_posts wp ON a.related_id = wp.id
                    JOIN users u ON a.user_id = u.id
                    ORDER BY a.created_at DESC";
        $stmt = $conn->prepare($ads_sql);
    } else {
        $ads_sql = "SELECT a.*, wp.title AS post_title, u.username 
                    FROM ads a
                    JOIN workspace_posts wp ON a.related_id = wp.id
                    JOIN users u ON a.user_id = u.id
                    WHERE a.user_id = ?
                    ORDER BY a.created_at DESC";
        $stmt = $conn->prepare($ads_sql);
        $stmt->bind_param("i", $user_id);
    }

    if ($stmt) {
        $stmt->execute();
        $result = $stmt->get_result();
        while ($ad = $result->fetch_assoc()) {
            $ads[] = $ad;
        }
        $stmt->close();
    } else {
        throw new Exception("Failed to prepare statement: " . $conn->error);
    }
} catch (Exception $e) {
    $errors[] = $e->getMessage();
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Manage Ads - 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">
</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">Manage Ads</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; ?>

        <!-- Add New Ad Button -->
        <div class="mb-4">
            <a href="create_ad.php" class="bg-blue-500 hover:bg-blue-600 text-white px-4 py-2 rounded inline-flex items-center">
                <i class="bi bi-plus-lg mr-2"></i> Create New Ad
            </a>
        </div>

        <!-- Ads Table -->
        <div class="overflow-x-auto">
            <table class="min-w-full bg-white rounded-lg shadow">
                <thead>
                    <tr>
                        <th class="py-3 px-6 bg-gray-200 font-semibold text-left">Ad Name</th>
                        <th class="py-3 px-6 bg-gray-200 font-semibold text-left">Type</th>
                        <th class="py-3 px-6 bg-gray-200 font-semibold text-left">Related Post/Story</th>
                        <th class="py-3 px-6 bg-gray-200 font-semibold text-left">Budget ($)</th>
                        <th class="py-3 px-6 bg-gray-200 font-semibold text-left">Spent ($)</th>
                        <th class="py-3 px-6 bg-gray-200 font-semibold text-left">Status</th>
                        <th class="py-3 px-6 bg-gray-200 font-semibold text-left">Actions</th>
                    </tr>
                </thead>
                <tbody>
                    <?php if (empty($ads)): ?>
                        <tr>
                            <td colspan="7" class="text-center py-4">No ads found.</td>
                        </tr>
                    <?php else: ?>
                        <?php foreach ($ads as $ad): ?>
                            <tr class="border-t">
                                <td class="py-3 px-6"><?= htmlspecialchars($ad['name']) ?></td>
                                <td class="py-3 px-6"><?= htmlspecialchars($ad['ad_type']) ?></td>
                                <td class="py-3 px-6"><?= htmlspecialchars($ad['post_title']) ?></td>
                                <td class="py-3 px-6"><?= number_format($ad['budget'], 2) ?></td>
                                <td class="py-3 px-6"><?= number_format($ad['total_spent'], 2) ?></td>
                                <td class="py-3 px-6">
                                    <span class="px-2 inline-flex text-xs leading-5 font-semibold rounded-full <?php 
                                        if ($ad['status'] === 'Active') {
                                            echo 'bg-green-100 text-green-800';
                                        } elseif ($ad['status'] === 'Paused') {
                                            echo 'bg-yellow-100 text-yellow-800';
                                        } else {
                                            echo 'bg-gray-100 text-gray-800';
                                        }
                                    ?>">
                                        <?= htmlspecialchars($ad['status']) ?>
                                    </span>
                                </td>
                                <td class="py-3 px-6">
                                    <a href="view_ad.php?id=<?= $ad['id'] ?>" class="text-blue-500 hover:text-blue-700 mr-2">
                                        <i class="bi bi-eye"></i>
                                    </a>
                                    <a href="edit_ad.php?id=<?= $ad['id'] ?>" class="text-yellow-500 hover:text-yellow-700 mr-2">
                                        <i class="bi bi-pencil-square"></i>
                                    </a>
                                    <a href="delete_ad.php?id=<?= $ad['id'] ?>" class="text-red-500 hover:text-red-700" 
                                       onclick="return confirm('Are you sure you want to delete this ad?');">
                                        <i class="bi bi-trash"></i>
                                    </a>
                                </td>
                            </tr>
                        <?php endforeach; ?>
                    <?php endif; ?>
                </tbody>
            </table>
        </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>
</body>
</html>

<?php
// Flush the output buffer
ob_end_flush();
// No closing PHP tag to prevent unwanted output
?>
B. public/workspace/ads/create_ad.php
Issue:

Similarly, the original require_once statements use incorrect relative paths, causing inclusion failures.

Correction:

Adjust the paths to go three levels up to reach the includes directory.

Full Corrected Code:


<?php
// /public/workspace/ads/create_ad.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
ob_start();

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

// No additional authentication check needed as it's handled in auth.php

$user_id = $_SESSION['user_id'];
$user_role = $_SESSION['role']; // 'admin' or 'staff'
$errors = [];
$success = "";

// Fetch user's posts/stories to assign to the ad
$related_items = [];

try {
    if ($user_role === 'admin') {
        $items_sql = "SELECT id, title, platform FROM workspace_posts ORDER BY created_at DESC";
        $stmt = $conn->prepare($items_sql);
    } else {
        $items_sql = "SELECT id, title, platform FROM workspace_posts WHERE user_id = ? ORDER BY created_at DESC";
        $stmt = $conn->prepare($items_sql);
        $stmt->bind_param("i", $user_id);
    }

    if ($stmt) {
        $stmt->execute();
        $result = $stmt->get_result();
        while ($item = $result->fetch_assoc()) {
            $related_items[] = $item;
        }
        $stmt->close();
    } else {
        throw new Exception("Failed to prepare statement: " . $conn->error);
    }
} catch (Exception $e) {
    $errors[] = $e->getMessage();
}

// Handle form submission
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    // Sanitize and validate input
    $name = sanitize_input($_POST['name'] ?? '');
    $ad_type = sanitize_input($_POST['ad_type'] ?? '');
    $related_id = intval($_POST['related_id'] ?? 0);
    $budget = floatval($_POST['budget'] ?? 0.00);
    $start_date = sanitize_input($_POST['start_date'] ?? '');
    $end_date = sanitize_input($_POST['end_date'] ?? '');
    $status = sanitize_input($_POST['status'] ?? 'Active');

    // Validation
    if (empty($name)) {
        $errors[] = "Ad name is required.";
    }
    if (!in_array($ad_type, ['Post', 'Story'])) {
        $errors[] = "Invalid ad type selected.";
    }
    if ($related_id <= 0) {
        $errors[] = "Please select a valid related post/story.";
    }
    if ($budget <= 0) {
        $errors[] = "Budget must be greater than zero.";
    }
    if (empty($start_date) || empty($end_date)) {
        $errors[] = "Start date and end date are required.";
    }
    if (strtotime($start_date) > strtotime($end_date)) {
        $errors[] = "Start date cannot be later than end date.";
    }

    // If no errors, insert the ad
    if (empty($errors)) {
        try {
            $insert_sql = "INSERT INTO ads (user_id, name, ad_type, related_id, budget, start_date, end_date, status) 
                           VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
            $stmt = $conn->prepare($insert_sql);
            if ($stmt) {
                $stmt->bind_param("issidsss", $user_id, $name, $ad_type, $related_id, $budget, $start_date, $end_date, $status);
                if ($stmt->execute()) {
                    $success = "Ad created successfully.";
                    // Reset form fields
                    $name = $ad_type = $related_id = $budget = $start_date = $end_date = $status = "";
                } else {
                    throw new Exception("Failed to create ad: " . $stmt->error);
                }
                $stmt->close();
            } else {
                throw new Exception("Failed to prepare insert statement: " . $conn->error);
            }
        } catch (Exception $e) {
            $errors[] = $e->getMessage();
        }
    }
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Create New Ad - 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">
    <!-- Datepicker CSS (optional for better date selection) -->
    <link href="https://cdn.jsdelivr.net/npm/flatpickr/dist/flatpickr.min.css" rel="stylesheet">
</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">Create New Ad</h1>

        <!-- Display Success Message -->
        <?php if (!empty($success)): ?>
            <div class="mb-6 bg-green-100 border border-green-400 text-green-700 px-4 py-3 rounded">
                <?= htmlspecialchars($success) ?>
            </div>
        <?php endif; ?>

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

        <!-- Ad Creation Form -->
        <form method="POST" action="create_ad.php" class="bg-white p-6 rounded-lg shadow-md">
            <div class="mb-4">
                <label for="name" class="block text-gray-700">Ad Name</label>
                <input type="text" id="name" name="name" value="<?= htmlspecialchars($name ?? '') ?>" 
                       class="w-full mt-2 p-2 border rounded" required>
            </div>

            <div class="mb-4">
                <label for="ad_type" class="block text-gray-700">Ad Type</label>
                <select id="ad_type" name="ad_type" class="w-full mt-2 p-2 border rounded" required>
                    <option value="">Select Ad Type</option>
                    <option value="Post" <?= isset($ad_type) && $ad_type === 'Post' ? 'selected' : '' ?>>Post</option>
                    <option value="Story" <?= isset($ad_type) && $ad_type === 'Story' ? 'selected' : '' ?>>Story</option>
                </select>
            </div>

            <div class="mb-4">
                <label for="related_id" class="block text-gray-700">Select Related Post/Story</label>
                <select id="related_id" name="related_id" class="w-full mt-2 p-2 border rounded" required>
                    <option value="">Select Post/Story</option>
                    <?php foreach ($related_items as $item): ?>
                        <option value="<?= $item['id'] ?>" <?= isset($related_id) && $related_id == $item['id'] ? 'selected' : '' ?>>
                            <?= htmlspecialchars($item['title']) ?> (<?= htmlspecialchars($item['platform']) ?>)
                        </option>
                    <?php endforeach; ?>
                </select>
            </div>

            <div class="mb-4">
                <label for="budget" class="block text-gray-700">Budget ($)</label>
                <input type="number" step="0.01" id="budget" name="budget" value="<?= htmlspecialchars($budget ?? '') ?>" 
                       class="w-full mt-2 p-2 border rounded" required>
            </div>

            <div class="mb-4">
                <label for="start_date" class="block text-gray-700">Start Date</label>
                <input type="date" id="start_date" name="start_date" value="<?= htmlspecialchars($start_date ?? '') ?>" 
                       class="w-full mt-2 p-2 border rounded" required>
            </div>

            <div class="mb-4">
                <label for="end_date" class="block text-gray-700">End Date</label>
                <input type="date" id="end_date" name="end_date" value="<?= htmlspecialchars($end_date ?? '') ?>" 
                       class="w-full mt-2 p-2 border rounded" required>
            </div>

            <div class="mb-4">
                <label for="status" class="block text-gray-700">Status</label>
                <select id="status" name="status" class="w-full mt-2 p-2 border rounded" required>
                    <option value="Active" <?= isset($status) && $status === 'Active' ? 'selected' : '' ?>>Active</option>
                    <option value="Paused" <?= isset($status) && $status === 'Paused' ? 'selected' : '' ?>>Paused</option>
                    <option value="Completed" <?= isset($status) && $status === 'Completed' ? 'selected' : '' ?>>Completed</option>
                </select>
            </div>

            <div class="flex justify-between">
                <button type="submit" class="bg-green-500 hover:bg-green-600 text-white px-4 py-2 rounded">
                    <i class="bi bi-save mr-2"></i> Save Ad
                </button>
                <a href="index.php" class="text-gray-700 hover:text-gray-900"><i class="bi bi-arrow-left mr-2"></i> Back to Ads</a>
            </div>
        </form>
    </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>

    <!-- Optional: Flatpickr JS for enhanced date selection -->
    <script src="https://cdn.jsdelivr.net/npm/flatpickr"></script>
    <script>
        // Initialize Flatpickr
        flatpickr("#start_date", {
            dateFormat: "Y-m-d"
        });
        flatpickr("#end_date", {
            dateFormat: "Y-m-d"
        });
    </script>
</body>
</html>

<?php
// Flush the output buffer
ob_end_flush();
// No closing PHP tag to prevent unwanted output
?>
C. public/workspace/ads/edit_ad.php
Correction:

Ensure all require_once and include statements use the corrected relative path by going three levels up.

Full Corrected Code:


<?php
// /public/workspace/ads/edit_ad.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
ob_start();

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

// No additional authentication check needed as it's handled in auth.php

$user_id = $_SESSION['user_id'];
$user_role = $_SESSION['role']; // 'admin' or 'staff'
$errors = [];
$success = "";

// Get Ad ID from GET parameter
$ad_id = intval($_GET['id'] ?? 0);
if ($ad_id <= 0) {
    header("Location: index.php");
    exit();
}

// Fetch Ad Details
$ad = null;
$related_items = [];

try {
    if ($user_role === 'admin') {
        $ad_sql = "SELECT a.* FROM ads a WHERE a.id = ?";
        $stmt = $conn->prepare($ad_sql);
        $stmt->bind_param("i", $ad_id);
    } else {
        $ad_sql = "SELECT a.* FROM ads a WHERE a.id = ? AND a.user_id = ?";
        $stmt = $conn->prepare($ad_sql);
        $stmt->bind_param("ii", $ad_id, $user_id);
    }

    if ($stmt) {
        $stmt->execute();
        $result = $stmt->get_result();
        $ad = $result->fetch_assoc();
        $stmt->close();

        if (!$ad) {
            header("Location: index.php");
            exit();
        }
    } else {
        throw new Exception("Failed to prepare ad fetch statement: " . $conn->error);
    }

    // Fetch related posts/stories
    if ($user_role === 'admin') {
        $items_sql = "SELECT id, title, platform FROM workspace_posts ORDER BY created_at DESC";
        $stmt = $conn->prepare($items_sql);
    } else {
        $items_sql = "SELECT id, title, platform FROM workspace_posts WHERE user_id = ? ORDER BY created_at DESC";
        $stmt = $conn->prepare($items_sql);
        $stmt->bind_param("i", $user_id);
    }

    if ($stmt) {
        $stmt->execute();
        $result = $stmt->get_result();
        while ($item = $result->fetch_assoc()) {
            $related_items[] = $item;
        }
        $stmt->close();
    } else {
        throw new Exception("Failed to prepare related items fetch statement: " . $conn->error);
    }
} catch (Exception $e) {
    $errors[] = $e->getMessage();
}

// Handle form submission
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    // Sanitize and validate input
    $name = sanitize_input($_POST['name'] ?? '');
    $ad_type = sanitize_input($_POST['ad_type'] ?? '');
    $related_id = intval($_POST['related_id'] ?? 0);
    $budget = floatval($_POST['budget'] ?? 0.00);
    $start_date = sanitize_input($_POST['start_date'] ?? '');
    $end_date = sanitize_input($_POST['end_date'] ?? '');
    $status = sanitize_input($_POST['status'] ?? 'Active');

    // Validation
    if (empty($name)) {
        $errors[] = "Ad name is required.";
    }
    if (!in_array($ad_type, ['Post', 'Story'])) {
        $errors[] = "Invalid ad type selected.";
    }
    if ($related_id <= 0) {
        $errors[] = "Please select a valid related post/story.";
    }
    if ($budget <= 0) {
        $errors[] = "Budget must be greater than zero.";
    }
    if (empty($start_date) || empty($end_date)) {
        $errors[] = "Start date and end date are required.";
    }
    if (strtotime($start_date) > strtotime($end_date)) {
        $errors[] = "Start date cannot be later than end date.";
    }

    // If no errors, update the ad
    if (empty($errors)) {
        try {
            $update_sql = "UPDATE ads SET name = ?, ad_type = ?, related_id = ?, budget = ?, start_date = ?, end_date = ?, status = ?, updated_at = NOW() 
                           WHERE id = ?";
            $stmt = $conn->prepare($update_sql);
            if ($stmt) {
                $stmt->bind_param("ssidsssi", $name, $ad_type, $related_id, $budget, $start_date, $end_date, $status, $ad_id);
                if ($stmt->execute()) {
                    $success = "Ad updated successfully.";
                    // Refresh ad data
                    $ad['name'] = $name;
                    $ad['ad_type'] = $ad_type;
                    $ad['related_id'] = $related_id;
                    $ad['budget'] = $budget;
                    $ad['start_date'] = $start_date;
                    $ad['end_date'] = $end_date;
                    $ad['status'] = $status;
                } else {
                    throw new Exception("Failed to update ad: " . $stmt->error);
                }
                $stmt->close();
            } else {
                throw new Exception("Failed to prepare update statement: " . $conn->error);
            }
        } catch (Exception $e) {
            $errors[] = $e->getMessage();
        }
    }
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Edit Ad - 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">
    <!-- Datepicker CSS (optional for better date selection) -->
    <link href="https://cdn.jsdelivr.net/npm/flatpickr/dist/flatpickr.min.css" rel="stylesheet">
</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">Edit Ad</h1>

        <!-- Display Success Message -->
        <?php if (!empty($success)): ?>
            <div class="mb-6 bg-green-100 border border-green-400 text-green-700 px-4 py-3 rounded">
                <?= htmlspecialchars($success) ?>
            </div>
        <?php endif; ?>

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

        <!-- Ad Edit Form -->
        <?php if ($ad): ?>
            <form method="POST" action="edit_ad.php?id=<?= $ad_id ?>" class="bg-white p-6 rounded-lg shadow-md">
                <div class="mb-4">
                    <label for="name" class="block text-gray-700">Ad Name</label>
                    <input type="text" id="name" name="name" value="<?= htmlspecialchars($ad['name']) ?>" 
                           class="w-full mt-2 p-2 border rounded" required>
                </div>

                <div class="mb-4">
                    <label for="ad_type" class="block text-gray-700">Ad Type</label>
                    <select id="ad_type" name="ad_type" class="w-full mt-2 p-2 border rounded" required>
                        <option value="Post" <?= $ad['ad_type'] === 'Post' ? 'selected' : '' ?>>Post</option>
                        <option value="Story" <?= $ad['ad_type'] === 'Story' ? 'selected' : '' ?>>Story</option>
                    </select>
                </div>

                <div class="mb-4">
                    <label for="related_id" class="block text-gray-700">Select Related Post/Story</label>
                    <select id="related_id" name="related_id" class="w-full mt-2 p-2 border rounded" required>
                        <?php foreach ($related_items as $item): ?>
                            <option value="<?= $item['id'] ?>" <?= $ad['related_id'] == $item['id'] ? 'selected' : '' ?>>
                                <?= htmlspecialchars($item['title']) ?> (<?= htmlspecialchars($item['platform']) ?>)
                            </option>
                        <?php endforeach; ?>
                    </select>
                </div>

                <div class="mb-4">
                    <label for="budget" class="block text-gray-700">Budget ($)</label>
                    <input type="number" step="0.01" id="budget" name="budget" value="<?= htmlspecialchars($ad['budget']) ?>" 
                           class="w-full mt-2 p-2 border rounded" required>
                </div>

                <div class="mb-4">
                    <label for="start_date" class="block text-gray-700">Start Date</label>
                    <input type="date" id="start_date" name="start_date" value="<?= htmlspecialchars($ad['start_date']) ?>" 
                           class="w-full mt-2 p-2 border rounded" required>
                </div>

                <div class="mb-4">
                    <label for="end_date" class="block text-gray-700">End Date</label>
                    <input type="date" id="end_date" name="end_date" value="<?= htmlspecialchars($ad['end_date']) ?>" 
                           class="w-full mt-2 p-2 border rounded" required>
                </div>

                <div class="mb-4">
                    <label for="status" class="block text-gray-700">Status</label>
                    <select id="status" name="status" class="w-full mt-2 p-2 border rounded" required>
                        <option value="Active" <?= $ad['status'] === 'Active' ? 'selected' : '' ?>>Active</option>
                        <option value="Paused" <?= $ad['status'] === 'Paused' ? 'selected' : '' ?>>Paused</option>
                        <option value="Completed" <?= $ad['status'] === 'Completed' ? 'selected' : '' ?>>Completed</option>
                    </select>
                </div>

                <div class="flex justify-between">
                    <button type="submit" class="bg-green-500 hover:bg-green-600 text-white px-4 py-2 rounded">
                        <i class="bi bi-save mr-2"></i> Update Ad
                    </button>
                    <a href="index.php" class="text-gray-700 hover:text-gray-900"><i class="bi bi-arrow-left mr-2"></i> Back to Ads</a>
                </div>
            </form>
        <?php endif; ?>
    </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>

    <!-- Optional: Flatpickr JS for enhanced date selection -->
    <script src="https://cdn.jsdelivr.net/npm/flatpickr"></script>
    <script>
        // Initialize Flatpickr
        flatpickr("#start_date", {
            dateFormat: "Y-m-d"
        });
        flatpickr("#end_date", {
            dateFormat: "Y-m-d"
        });
    </script>
</body>
</html>

<?php
// Flush the output buffer
ob_end_flush();
// No closing PHP tag to prevent unwanted output
?>
D. public/workspace/ads/delete_ad.php
Correction:

Ensure all require_once and include statements use the corrected relative path by going three levels up.

Full Corrected Code:


<?php
// /public/workspace/ads/delete_ad.php

// Enable error reporting for debugging (disable in production)
ini_set('display_errors', 0); // Set to 0 in production
ini_set('display_startup_errors', 0); // Set to 0 in production
error_reporting(0); // Set to 0 in production

// Start output buffering
ob_start();

// Include authentication and database connection using correct relative paths
require_once __DIR__ . '/../../../includes/auth.php';
require_once __DIR__ . '/../../../includes/db_connect.php';

// No additional authentication check needed as it's handled in auth.php

$user_id = $_SESSION['user_id'];
$user_role = $_SESSION['role']; // 'admin' or 'staff'

// Get Ad ID from GET parameter
$ad_id = intval($_GET['id'] ?? 0);
if ($ad_id <= 0) {
    header("Location: index.php");
    exit();
}

$errors = [];

// Delete the Ad
try {
    if ($user_role === 'admin') {
        $delete_sql = "DELETE FROM ads WHERE id = ?";
        $stmt = $conn->prepare($delete_sql);
        $stmt->bind_param("i", $ad_id);
    } else {
        $delete_sql = "DELETE FROM ads WHERE id = ? AND user_id = ?";
        $stmt = $conn->prepare($delete_sql);
        $stmt->bind_param("ii", $ad_id, $user_id);
    }

    if ($stmt) {
        if ($stmt->execute()) {
            // Redirect back with success message
            header("Location: index.php?message=Ad+deleted+successfully.");
            exit();
        } else {
            throw new Exception("Failed to delete ad: " . $stmt->error);
        }
        $stmt->close();
    } else {
        throw new Exception("Failed to prepare delete statement: " . $conn->error);
    }
} catch (Exception $e) {
    // Log the error (optional) and redirect with error message
    // error_log($e->getMessage());
    header("Location: index.php?error=" . urlencode($e->getMessage()));
    exit();
}
?>
E. public/workspace/ads/view_ad.php
Correction:

Ensure all require_once and include statements use the corrected relative path by going three levels up.

Full Corrected Code:


<?php
// /public/workspace/ads/view_ad.php

// Enable error reporting for debugging (disable in production)
ini_set('display_errors', 0); // Set to 0 in production
ini_set('display_startup_errors', 0); // Set to 0 in production
error_reporting(0); // Set to 0 in production

// Start output buffering
ob_start();

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

// No additional authentication check needed as it's handled in auth.php

$user_id = $_SESSION['user_id'];
$user_role = $_SESSION['role']; // 'admin' or 'staff'
$ad = null;
$spend_logs = [];
$errors = [];

// Get Ad ID from GET parameter
$ad_id = intval($_GET['id'] ?? 0);
if ($ad_id <= 0) {
    header("Location: index.php");
    exit();
}

// Fetch Ad Details and Spend Logs
try {
    // Fetch Ad Details
    if ($user_role === 'admin') {
        $ad_sql = "SELECT a.*, wp.title AS post_title, u.username 
                   FROM ads a
                   JOIN workspace_posts wp ON a.related_id = wp.id
                   JOIN users u ON a.user_id = u.id
                   WHERE a.id = ?";
        $stmt = $conn->prepare($ad_sql);
        $stmt->bind_param("i", $ad_id);
    } else {
        $ad_sql = "SELECT a.*, wp.title AS post_title, u.username 
                   FROM ads a
                   JOIN workspace_posts wp ON a.related_id = wp.id
                   JOIN users u ON a.user_id = u.id
                   WHERE a.id = ? AND a.user_id = ?";
        $stmt = $conn->prepare($ad_sql);
        $stmt->bind_param("ii", $ad_id, $user_id);
    }

    if ($stmt) {
        $stmt->execute();
        $result = $stmt->get_result();
        $ad = $result->fetch_assoc();
        $stmt->close();

        if (!$ad) {
            header("Location: index.php");
            exit();
        }
    } else {
        throw new Exception("Failed to prepare ad fetch statement: " . $conn->error);
    }

    // Fetch Spend Logs
    $spend_sql = "SELECT * FROM ad_spend_logs WHERE ad_id = ? ORDER BY spend_date DESC";
    $stmt = $conn->prepare($spend_sql);
    if ($stmt) {
        $stmt->bind_param("i", $ad_id);
        $stmt->execute();
        $result = $stmt->get_result();
        while ($log = $result->fetch_assoc()) {
            $spend_logs[] = $log;
        }
        $stmt->close();
    } else {
        throw new Exception("Failed to prepare spend logs fetch statement: " . $conn->error);
    }
} catch (Exception $e) {
    $errors[] = $e->getMessage();
}

// Handle Add Spend Log Form Submission
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['add_spend'])) {
    $amount = floatval($_POST['amount'] ?? 0.00);
    $description = sanitize_input($_POST['description'] ?? '');

    if ($amount <= 0) {
        $errors[] = "Amount must be greater than zero.";
    }

    if (empty($description)) {
        $errors[] = "Description is required.";
    }

    if (empty($errors)) {
        try {
            $insert_spend_sql = "INSERT INTO ad_spend_logs (ad_id, amount, description) VALUES (?, ?, ?)";
            $stmt = $conn->prepare($insert_spend_sql);
            if ($stmt) {
                $stmt->bind_param("ids", $ad_id, $amount, $description);
                if ($stmt->execute()) {
                    // Update total_spent in ads table
                    $update_ad_sql = "UPDATE ads SET total_spent = total_spent + ? WHERE id = ?";
                    $update_stmt = $conn->prepare($update_ad_sql);
                    if ($update_stmt) {
                        $update_stmt->bind_param("di", $amount, $ad_id);
                        $update_stmt->execute();
                        $update_stmt->close();
                    }
                    // Redirect to refresh data
                    header("Location: view_ad.php?id=$ad_id");
                    exit();
                } else {
                    throw new Exception("Failed to add spend log: " . $stmt->error);
                }
                $stmt->close();
            } else {
                throw new Exception("Failed to prepare spend log insert statement: " . $conn->error);
            }
        } catch (Exception $e) {
            $errors[] = $e->getMessage();
        }
    }
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>View Ad - 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">
    <!-- Chart.js for visualizing spending (optional) -->
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
</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">Ad Details</h1>

        <!-- Back Button -->
        <div class="mb-4">
            <a href="index.php" class="text-gray-700 hover:text-gray-900"><i class="bi bi-arrow-left mr-2"></i> Back to Ads</a>
        </div>

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

        <!-- Ad Information -->
        <?php if ($ad): ?>
            <div class="bg-white p-6 rounded-lg shadow-md mb-6">
                <h2 class="text-2xl font-semibold mb-4"><?= htmlspecialchars($ad['name']) ?></h2>
                <p><strong>Type:</strong> <?= htmlspecialchars($ad['ad_type']) ?></p>
                <p><strong>Related Post/Story:</strong> <?= htmlspecialchars($ad['post_title']) ?> (<?= htmlspecialchars($ad['platform']) ?>)</p>
                <p><strong>Budget:</strong> $<?= number_format($ad['budget'], 2) ?></p>
                <p><strong>Total Spent:</strong> $<?= number_format($ad['total_spent'], 2) ?></p>
                <p><strong>Start Date:</strong> <?= htmlspecialchars(date("d M Y", strtotime($ad['start_date']))) ?></p>
                <p><strong>End Date:</strong> <?= htmlspecialchars(date("d M Y", strtotime($ad['end_date']))) ?></p>
                <p><strong>Status:</strong> <?= htmlspecialchars($ad['status']) ?></p>
                <p><strong>Created By:</strong> <?= htmlspecialchars($ad['username']) ?></p>
                <p><strong>Created At:</strong> <?= htmlspecialchars(date("d M Y, h:i A", strtotime($ad['created_at']))) ?></p>
            </div>

            <!-- Spending Visualization (Optional) -->
            <div class="bg-white p-6 rounded-lg shadow-md mb-6">
                <h3 class="text-xl font-semibold mb-4">Spending Overview</h3>
                <canvas id="spendingChart" width="400" height="200"></canvas>
            </div>

            <!-- Add Spend Log Form -->
            <div class="bg-white p-6 rounded-lg shadow-md mb-6">
                <h3 class="text-xl font-semibold mb-4">Add Spend Log</h3>
                <form method="POST" action="view_ad.php?id=<?= $ad_id ?>" class="grid grid-cols-1 md:grid-cols-2 gap-4">
                    <div>
                        <label for="amount" class="block text-gray-700">Amount ($)</label>
                        <input type="number" step="0.01" id="amount" name="amount" class="w-full mt-2 p-2 border rounded" required>
                    </div>
                    <div>
                        <label for="description" class="block text-gray-700">Description</label>
                        <input type="text" id="description" name="description" class="w-full mt-2 p-2 border rounded" required>
                    </div>
                    <div class="md:col-span-2 flex justify-end">
                        <button type="submit" name="add_spend" class="bg-blue-500 hover:bg-blue-600 text-white px-4 py-2 rounded">
                            <i class="bi bi-plus-lg mr-2"></i> Add Spend
                        </button>
                    </div>
                </form>
            </div>

            <!-- Spend Logs Table -->
            <div class="bg-white p-6 rounded-lg shadow-md mb-6">
                <h3 class="text-xl font-semibold mb-4">Spending Logs</h3>
                <div class="overflow-x-auto">
                    <table class="min-w-full bg-white rounded-lg shadow">
                        <thead>
                            <tr>
                                <th class="py-3 px-6 bg-gray-200 font-semibold text-left">Amount ($)</th>
                                <th class="py-3 px-6 bg-gray-200 font-semibold text-left">Description</th>
                                <th class="py-3 px-6 bg-gray-200 font-semibold text-left">Date</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if (empty($spend_logs)): ?>
                                <tr>
                                    <td colspan="3" class="text-center py-4">No spend logs found.</td>
                                </tr>
                            <?php else: ?>
                                <?php foreach ($spend_logs as $log): ?>
                                    <tr class="border-t">
                                        <td class="py-3 px-6"><?= number_format($log['amount'], 2) ?></td>
                                        <td class="py-3 px-6"><?= htmlspecialchars($log['description']) ?></td>
                                        <td class="py-3 px-6"><?= htmlspecialchars(date("d M Y, h:i A", strtotime($log['spend_date']))) ?></td>
                                    </tr>
                                <?php endforeach; ?>
                            <?php endif; ?>
                        </tbody>
                    </table>
                </div>
            </div>

            <!-- Chart.js Script -->
            <script>
                const ctx = document.getElementById('spendingChart').getContext('2d');
                const spendData = {
                    labels: <?= json_encode(array_map(function($log) {
                        return date("d M", strtotime($log['spend_date']));
                    }, $spend_logs)) ?>,
                    datasets: [{
                        label: 'Amount Spent ($)',
                        data: <?= json_encode(array_map(function($log) {
                            return floatval($log['amount']);
                        }, $spend_logs)) ?>,
                        backgroundColor: 'rgba(59, 130, 246, 0.6)', // Tailwind blue-500
                        borderColor: 'rgba(59, 130, 246, 1)',
                        borderWidth: 1
                    }]
                };

                const spendingChart = new Chart(ctx, {
                    type: 'bar',
                    data: spendData,
                    options: {
                        scales: {
                            y: {
                                beginAtZero: true
                            }
                        }
                    }
                });
            </script>
        <?php endif; ?>
    </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>
</body>
</html>

<?php
// Flush the output buffer
ob_end_flush();
// No closing PHP tag to prevent unwanted output
?>
3. Comprehensive List of Corrected Ads Module Files
To ensure consistency and prevent further inclusion errors, all Ads module PHP files within public/workspace/ads/ need to have their require_once and include statements corrected. Below are the fully corrected versions of these files.

A. public/workspace/ads/index.php
(Already provided above.)

B. public/workspace/ads/create_ad.php
(Already provided above.)

C. public/workspace/ads/edit_ad.php
(Already provided above.)

D. public/workspace/ads/delete_ad.php
(Already provided above.)

E. public/workspace/ads/view_ad.php
(Already provided above.)

4. Guide to Correcting Other Ads Module Files
For any additional or future Ads module files, follow these steps to correct the file paths:

Identify the Current Directory: Determine the location of the PHP file within the directory structure. All Ads module files are within public/workspace/ads/.
Set the Correct Relative Path: To include files from the includes directory, you need to go three levels up from ads/:
From public/workspace/ads/:
../ → public/workspace/
../../ → public/
../../../ → /home/u406552082/domains/longevacare.com/public_html/kaakalia/
Update require_once and include Statements: Replace all existing require_once or include statements with the corrected relative paths. Example:

require_once __DIR__ . '/../../../includes/auth.php';
require_once __DIR__ . '/../../../includes/db_connect.php';
require_once __DIR__ . '/../../../includes/functions.php';
Adjust Footer or Header Inclusions: If your Ads module files include common components like headers or footers, ensure their paths are also correctly set. Example:

<?php include __DIR__ . '/../../../includes/header.php'; ?>
Verify File Existence: Ensure that the included files (auth.php, db_connect.php, functions.php, header.php, etc.) exist in the specified includes directory.
Test the Corrections: After updating the paths, access each Ads module page to verify that the error has been resolved and that the inclusion works as expected.
5. Additional Recommendations
While the above corrections address the immediate error, here are some best practices and recommendations to enhance your project's robustness and maintainability:

A. Avoid Deeply Nested Relative Paths
Issue: Deeply nested ../../ paths can become confusing and error-prone as the project scales.
Solution: Consider using absolute paths or defining a base path constant if you plan to expand the project further. However, if you choose this route, ensure that the base path is consistently included across all files. Example:

// At the very top of your PHP files
define('BASE_PATH', '/home/u406552082/domains/longevacare.com/public_html/kaakalia');

require_once BASE_PATH . '/includes/auth.php';
require_once BASE_PATH . '/includes/db_connect.php';
require_once BASE_PATH . '/includes/functions.php';

include BASE_PATH . '/includes/header.php';
Note: This approach requires defining the BASE_PATH in every PHP file, which can be streamlined with configurations or autoloaders in more advanced setups.

B. Consistent Error Reporting
Development vs. Production:
Development: Enable error reporting to aid debugging.

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
Production: Disable error reporting to prevent sensitive information disclosure.

ini_set('display_errors', 0);
ini_set('display_startup_errors', 0);
error_reporting(0);
Best Practice: Use environment variables or configurations to switch between development and production settings seamlessly.

C. Security Enhancements
Validate and Sanitize All Inputs: Ensure that all user inputs are thoroughly validated and sanitized to prevent security vulnerabilities like SQL Injection and Cross-Site Scripting (XSS).
Use Prepared Statements: All database interactions already use prepared statements, which is excellent. Continue this practice to safeguard against SQL Injection.
Restrict File Access: Ensure that sensitive directories (like includes/) are not directly accessible via the web. Typically, placing them outside the public/ directory helps achieve this.
D. Modular Code Structure
Autoloaders: For larger projects, consider implementing autoloaders to manage class and file inclusions more efficiently.
MVC Frameworks: As the project grows, adopting a Model-View-Controller (MVC) framework (like Laravel, Symfony, or CodeIgniter) can help in maintaining clean and organized code.
E. Documentation and Comments
Maintain Clear Comments: Document the purpose of each section of your code with clear comments. This practice aids future maintenance and collaboration.
README Files: Include README files in your project directories to provide overviews, setup instructions, and other pertinent information.
6. Final Verification Steps
After applying the corrected file paths, perform the following steps to ensure everything functions correctly:

Clear Browser Cache: Sometimes, browsers cache PHP errors. Clearing the cache ensures you're viewing the latest version.
Access Ads Module Pages: Navigate to each Ads module page (index.php, create_ad.php, edit_ad.php, delete_ad.php, view_ad.php) to verify that:
The pages load without inclusion errors.
All functionalities (create, edit, delete, view) work as intended.
Check Included Components: Ensure that shared components like the header are displayed correctly and function (e.g., navigation links).
Monitor Logs: Check your server's error logs to identify any lingering issues or warnings that weren't immediately visible.
7. Conclusion
By meticulously adjusting the relative file paths in your Ads module files, you've resolved the inclusion errors and ensured that your Kaak Alia Management System functions seamlessly. Maintaining correct file paths is fundamental in PHP projects to ensure that dependencies are loaded correctly and to prevent runtime errors.

Key Takeaways:

Relative Paths: Ensure that your require_once and include statements correctly navigate the directory structure.
Avoid Undefined Constants: If you choose not to implement base path constants like BASE_PATH, rely on reliable relative paths.
Consistent Structure: Maintain a clear and consistent project structure to simplify navigation and file inclusions.
Security Practices: Implement security best practices to protect your application from common vulnerabilities.
Should you encounter further issues or need assistance with other parts of your application, feel free to reach out!

) fix thsi (Unknown column 'title' in 'field list' ) and and when create ad fix select from post from this info database (-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: Nov 10, 2024 at 11:22 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
) 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,
  `password` varchar(255) NOT NULL,
  `role` enum('admin','staff') NOT 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,
  `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 `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 `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 `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