<?php
require_once('../common.php');
require_once('checklogin.php');

// "Last modified" for a submission in revision = the timestamp of the
// most recent revision message in sm_revisions, falling back to the
// original submission date if no messages have been exchanged yet.
$query = "SELECT s.id, s.title, s.date_submitted,
                 COALESCE(MAX(r.date_sent), s.date_submitted) AS last_modified,
                 p.author_name, p.author_lastname
          FROM sm_submissions AS s
          JOIN sm_poets AS p ON s.poetid = p.id
          LEFT JOIN sm_revisions AS r ON r.submission_id = s.id
          WHERE s.status = 3
          GROUP BY s.id, s.title, s.date_submitted, p.author_name, p.author_lastname
          ORDER BY last_modified DESC";

$stmt = $pdo->query($query);
$revisions = $stmt->fetchAll(PDO::FETCH_ASSOC);

include_once('header.php');
include_once('editors-menu.php');
?>

<div class="container" style="max-width: 800px; margin: 20px auto; padding: 20px;">
    <h2>Revisions in Progress</h2>
    <table>
        <tr>
            <th>Title</th>
            <th>Author</th>
            <th>Last Modified</th>
            <th>Actions</th>
        </tr>
        <?php foreach ($revisions as $revision): ?>
            <tr>
                <td><?= htmlspecialchars($revision['title'], ENT_QUOTES, 'UTF-8') ?></td>
                <td><?= htmlspecialchars($revision['author_name'] . ' ' . $revision['author_lastname'], ENT_QUOTES, 'UTF-8') ?></td>
                <td><?= htmlspecialchars(date('M. d, Y h:ia', strtotime($revision['last_modified'])), ENT_QUOTES, 'UTF-8') ?></td>
                <td><a href="collaborate-revision.php?id=<?= $revision['id'] ?>">View Revision</a></td>
            </tr>
        <?php endforeach; ?>
    </table>
</div>

<?php include_once('footer.php'); ?>
