<?php

require_once('../common.php');
require_once('checklogin.php');
require_once('../functions.php'); // Include shared functions, including getEditionName()
include_once('header.php');
include_once('editors-menu.php');

// Database connection using mysqli for compatibility
$connect = mysqli_connect(DBHOST, DBUSER, DBPASS, DBNAME);

// Check if connection failed
if (!$connect) {
    die("Database connection failed: " . mysqli_connect_error());
}

// Retrieve 'finalcheck' value from the database
$finalcheck_query = "SELECT value FROM sm_settings WHERE var = 'finalcheck'";
$finalcheck_result = mysqli_query($connect, $finalcheck_query);

if ($finalcheck_result && mysqli_num_rows($finalcheck_result) > 0) {
    $finalcheck_row = mysqli_fetch_assoc($finalcheck_result);
    $finalcheck = $finalcheck_row['value'];
} else {
    // Default value if not found
    $finalcheck = "no";
}

// Message display
?>
<center><h2>Accepted</h2>
<span class="msg"><?php if (isset($msg)) { echo htmlspecialchars($msg, ENT_QUOTES, 'UTF-8'); } ?></span>
<table width="1000" align="center" cellpadding="15" border="5">
    <tr>
        <th>Date Submitted</th>
        <th>Poem</th>
        <th>Contributor</th>
        <th>Email</th>        
        <th>Edition</th>
        <?php if ($finalcheck == "yes"): ?><th>Print</th><?php endif ?>
        <th>&nbsp;</th>
    </tr>

<?php
// Secure SQL Query with Prepared Statement
$query = "SELECT *, a.id AS sid
          FROM sm_submissions AS a
          INNER JOIN sm_poets AS b ON a.poetid = b.id
          WHERE a.status = ?
          ORDER BY a.date_submitted DESC";

if ($stmt = mysqli_prepare($connect, $query)) {
    $status = 1;
    mysqli_stmt_bind_param($stmt, "i", $status);  // Bind parameter
    mysqli_stmt_execute($stmt);  // Execute statement
    $result = mysqli_stmt_get_result($stmt);  // Get result

    while ($row = mysqli_fetch_assoc($result)) {
        // Secure output by using htmlspecialchars
        $dateSubmitted = htmlspecialchars(date('M. d, Y h:ia', strtotime($row['date_submitted'])), ENT_QUOTES, 'UTF-8');
        $title = htmlspecialchars($row['title'], ENT_QUOTES, 'UTF-8');
        $authorName = htmlspecialchars($row['author_name'], ENT_QUOTES, 'UTF-8');
        $authorLastName = htmlspecialchars($row['author_lastname'], ENT_QUOTES, 'UTF-8');
        $email = htmlspecialchars($row['email'], ENT_QUOTES, 'UTF-8');
        $editionName = getEditionName($row['editionid']); // Use the shared function
        $sid = htmlspecialchars($row['sid'], ENT_QUOTES, 'UTF-8');
        $poetid = htmlspecialchars($row['poetid'], ENT_QUOTES, 'UTF-8');

        // Display submission data
        ?>
        <tr>
            <td><?= $dateSubmitted ?></td>
            <td><a href="view-submission.php?id=<?= $sid ?>"><?= $title ?></a></td>
            <td><a href="user_history.php?poetid=<?= $poetid ?>" target="_search"><?= $authorName . ' ' . $authorLastName ?></a></td>
            <td><a href="mailto:<?= $email ?>"><?= $email ?></a></td>        
            <td><?= $editionName ?></td>

            <?php if ($finalcheck == "yes"): ?>
            <td align="center">
            <?php
                if ($row['printconfirmation'] == 2) {
                    echo '<span style="color:red;">NO</span>';
                } elseif ($row['printconfirmation'] == 1) {
                    echo '<span style="color:green;">YES</span>';
                } else {
                    echo '<b>?</b>';
                }
            ?>
            </td>
            <?php endif; ?>
            <td>[<a href="editors-update-status.php?id=<?= $sid ?>">Update Status</a>]</td>
        </tr>    
        <?php
    }

    mysqli_stmt_close($stmt);  // Close statement
} else {
    echo "Error preparing query: " . mysqli_error($connect);
}
?>

</table></center>
<?php
include_once('footer.php');

// Close the database connection
mysqli_close($connect);
?>
