244 lines
11 KiB
PHP
244 lines
11 KiB
PHP
<?php
|
|
require_once 'db/config.php';
|
|
require_once 'layout_header.php';
|
|
|
|
$pdo = db();
|
|
|
|
// Create scheduled_posts table if it doesn't exist
|
|
$pdo->exec("CREATE TABLE IF NOT EXISTS scheduled_posts (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
channel_id INT NOT NULL,
|
|
message TEXT NOT NULL,
|
|
scheduled_at DATETIME NOT NULL,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'pending',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (channel_id) REFERENCES channels(id) ON DELETE CASCADE
|
|
);");
|
|
|
|
$message = '';
|
|
$type = '';
|
|
|
|
// Handle deletion of a scheduled post
|
|
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'delete_post') {
|
|
$post_id_to_delete = $_POST['post_id'] ?? 0;
|
|
if ($post_id_to_delete > 0) {
|
|
try {
|
|
$delete_stmt = $pdo->prepare("DELETE FROM scheduled_posts WHERE id = ?");
|
|
$delete_stmt->execute([$post_id_to_delete]);
|
|
$message = 'Scheduled post cancelled successfully.';
|
|
$type = 'success';
|
|
} catch (PDOException $e) {
|
|
$message = 'Error cancelling post: ' . $e->getMessage();
|
|
$type = 'danger';
|
|
}
|
|
}
|
|
}
|
|
|
|
// Handle form submission for bulk scheduling
|
|
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['schedule_post'])) {
|
|
$channel_id = $_POST['channel_id'];
|
|
$post_idea = $_POST['message'];
|
|
$schedule_date = $_POST['schedule_date'];
|
|
$post_count = isset($_POST['post_count']) ? (int)$_POST['post_count'] : 0;
|
|
|
|
if (empty($channel_id) || empty($post_idea) || empty($schedule_date) || $post_count <= 0) {
|
|
$message = 'Please fill out all fields and specify a valid number of posts.';
|
|
$type = 'danger';
|
|
} else {
|
|
try {
|
|
$pdo->beginTransaction();
|
|
|
|
$stmt = $pdo->prepare("INSERT INTO scheduled_posts (channel_id, message, scheduled_at) VALUES (?, ?, ?)");
|
|
|
|
// Fetch webhook URL and channel identifier once before the loop
|
|
$webhook_stmt = $pdo->query("SELECT setting_value FROM settings WHERE setting_key = 'webhook_new_post'");
|
|
$webhook_url = $webhook_stmt->fetchColumn();
|
|
$identifier_stmt = $pdo->prepare("SELECT channel_identifier FROM channels WHERE id = ?");
|
|
$identifier_stmt->execute([$channel_id]);
|
|
$channel_identifier = $identifier_stmt->fetchColumn();
|
|
|
|
$start_hour = 8; // 8 AM
|
|
$end_hour = 22; // 10 PM
|
|
$total_hours = $end_hour - $start_hour;
|
|
$interval_minutes = ($post_count > 1) ? floor(($total_hours * 60) / ($post_count - 1)) : 0;
|
|
|
|
$posts_scheduled = 0;
|
|
for ($i = 0; $i < $post_count; $i++) {
|
|
$minutes_to_add = $i * $interval_minutes;
|
|
$scheduled_datetime = new DateTime($schedule_date . ' ' . $start_hour . ':00');
|
|
$scheduled_datetime->add(new DateInterval('PT' . $minutes_to_add . 'M'));
|
|
$scheduled_at_str = $scheduled_datetime->format('Y-m-d H:i:s');
|
|
|
|
$stmt->execute([$channel_id, $post_idea, $scheduled_at_str]);
|
|
$last_post_id = $pdo->lastInsertId();
|
|
|
|
// Trigger webhook for each post
|
|
if ($webhook_url && $channel_identifier) {
|
|
$post_data = [
|
|
'event' => 'new_post_scheduled',
|
|
'post_id' => $last_post_id,
|
|
'channel_identifier' => $channel_identifier,
|
|
'post_text_idea' => $post_idea,
|
|
'scheduled_at_utc' => gmdate('Y-m-d H:i:s', strtotime($scheduled_at_str))
|
|
];
|
|
|
|
$ch = curl_init($webhook_url);
|
|
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
|
|
curl_setopt($ch, CURLOPT_POST, true);
|
|
curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($post_data));
|
|
curl_setopt($ch, CURLOPT_HTTPHEADER, ['Content-Type: application/json']);
|
|
curl_exec($ch);
|
|
curl_close($ch);
|
|
}
|
|
$posts_scheduled++;
|
|
}
|
|
|
|
$pdo->commit();
|
|
$message = "$posts_scheduled post(s) scheduled successfully!";
|
|
$type = 'success';
|
|
|
|
} catch (Exception $e) {
|
|
$pdo->rollBack();
|
|
$message = 'Error scheduling posts: ' . $e->getMessage();
|
|
$type = 'danger';
|
|
}
|
|
}
|
|
}
|
|
|
|
// Fetch channels for the dropdown
|
|
try {
|
|
$channels_stmt = $pdo->query("SELECT id, channel_identifier FROM channels ORDER BY channel_identifier ASC");
|
|
$channels = $channels_stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
} catch (PDOException $e) {
|
|
$channels = [];
|
|
$message = 'Error fetching channels: ' . $e->getMessage();
|
|
$type = 'danger';
|
|
}
|
|
|
|
// Fetch scheduled posts for the table
|
|
try {
|
|
$posts_stmt = $pdo->query("
|
|
SELECT sp.id, c.channel_identifier, sp.message, sp.scheduled_at, sp.status
|
|
FROM scheduled_posts sp
|
|
JOIN channels c ON sp.channel_id = c.id
|
|
ORDER BY sp.scheduled_at ASC
|
|
");
|
|
$scheduled_posts = $posts_stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
} catch (PDOException $e) {
|
|
$scheduled_posts = [];
|
|
$message = 'Error fetching scheduled posts: ' . $e->getMessage();
|
|
$type = 'danger';
|
|
}
|
|
|
|
?>
|
|
|
|
<div class="container-fluid p-4">
|
|
<h1 class="h3 mb-4 text-white">Scheduler</h1>
|
|
|
|
<?php if ($message): ?>
|
|
<div class="alert alert-<?php echo $type; ?> alert-dismissible fade show" role="alert">
|
|
<?php echo htmlspecialchars($message); ?>
|
|
<button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
|
|
</div>
|
|
<?php endif; ?>
|
|
|
|
<!-- Schedule Posts Form -->
|
|
<div class="card bg-dark-surface mb-4">
|
|
<div class="card-header">
|
|
<h5 class="card-title mb-0">Bulk Schedule Posts</h5>
|
|
</div>
|
|
<div class="card-body">
|
|
<form method="POST" action="scheduler.php">
|
|
<div class="row">
|
|
<div class="col-md-6 mb-3">
|
|
<label for="channel_id" class="form-label">Channel</label>
|
|
<select class="form-select" id="channel_id" name="channel_id" required>
|
|
<option value="" disabled selected>Select a channel</option>
|
|
<?php foreach ($channels as $channel): ?>
|
|
<option value="<?php echo $channel['id']; ?>">
|
|
<?php echo htmlspecialchars($channel['channel_identifier']); ?>
|
|
</option>
|
|
<?php endforeach; ?>
|
|
</select>
|
|
</div>
|
|
<div class="col-md-3 mb-3">
|
|
<label for="schedule_date" class="form-label">Date</label>
|
|
<input type="date" class="form-control" id="schedule_date" name="schedule_date" required>
|
|
</div>
|
|
<div class="col-md-3 mb-3">
|
|
<label for="post_count" class="form-label">Number of Posts</label>
|
|
<input type="number" class="form-control" id="post_count" name="post_count" min="1" max="100" value="1" required>
|
|
</div>
|
|
</div>
|
|
<div class="mb-3">
|
|
<label for="message" class="form-label">Post Idea / Prompt</label>
|
|
<textarea class="form-control" id="message" name="message" rows="3" required placeholder="e.g., A motivational quote about success"></textarea>
|
|
<div class="form-text">This text will be sent to your n8n workflow as a prompt for AI content generation.</div>
|
|
</div>
|
|
<button type="submit" name="schedule_post" class="btn btn-primary">Schedule Posts</button>
|
|
</form>
|
|
</div>
|
|
</div>
|
|
|
|
<!-- Scheduled Posts Table -->
|
|
<div class="card bg-dark-surface">
|
|
<div class="card-header">
|
|
<h5 class="card-title mb-0">Upcoming Posts</h5>
|
|
</div>
|
|
<div class="card-body">
|
|
<div class="table-responsive">
|
|
<table class="table table-dark table-hover">
|
|
<thead>
|
|
<tr>
|
|
<th>ID</th>
|
|
<th>Channel</th>
|
|
<th>Message</th>
|
|
<th>Scheduled Time</th>
|
|
<th>Status</th>
|
|
<th>Actions</th>
|
|
</tr>
|
|
</thead>
|
|
<tbody>
|
|
<?php if (empty($scheduled_posts)): ?>
|
|
<tr>
|
|
<td colspan="6" class="text-center text-muted">No posts scheduled yet.</td>
|
|
</tr>
|
|
<?php else: ?>
|
|
<?php
|
|
$current_date = null;
|
|
foreach ($scheduled_posts as $post):
|
|
$post_date = date("F j, Y", strtotime($post['scheduled_at']));
|
|
if ($post_date !== $current_date):
|
|
$current_date = $post_date;
|
|
?>
|
|
<tr class="table-group-divider">
|
|
<td colspan="6" class="bg-secondary text-white"><strong><?php echo $current_date; ?></strong></td>
|
|
</tr>
|
|
<?php endif; ?>
|
|
<tr>
|
|
<td><?php echo $post['id']; ?></td>
|
|
<td><?php echo htmlspecialchars($post['channel_identifier']); ?></td>
|
|
<td><?php echo nl2br(htmlspecialchars(substr($post['message'], 0, 80))) . (strlen($post['message']) > 80 ? '...' : ''); ?></td>
|
|
<td><?php echo date("g:i a", strtotime($post['scheduled_at'])); ?></td>
|
|
<td><span class="badge bg-warning text-dark"><?php echo htmlspecialchars(ucfirst($post['status'])); ?></span></td>
|
|
<td>
|
|
<form method="POST" action="scheduler.php" onsubmit="return confirm('Are you sure you want to cancel this post?');" style="display: inline;">
|
|
<input type="hidden" name="action" value="delete_post">
|
|
<input type="hidden" name="post_id" value="<?php echo $post['id']; ?>">
|
|
<button type="submit" class="btn btn-danger btn-sm">Cancel</button>
|
|
</form>
|
|
</td>
|
|
</tr>
|
|
<?php endforeach; ?>
|
|
<?php endif; ?>
|
|
</tbody>
|
|
</table>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<?php
|
|
require_once 'layout_footer.php';
|
|
?>
|