<?php
require_once('../config.php'); // Include config for PDO initialization
require_once('checklogin.php');
include_once('header.php');
include_once('editors-menu.php');

// ======================================================================
// === Define the getEditionName function directly in this file ===
// === Recommended: Move this to a central functions file later ===
// ======================================================================
if (!function_exists('getEditionName')) {
    /**
     * Gets the name of an edition from its ID.
     * (Defined locally in editors-othertypes.php)
     *
     * @param int|null $editionId The ID of the edition.
     * @return string The name of the edition or a default string.
     */
    function getEditionName($editionId) {
        global $pdo; // Use the global $pdo connection from config.php

        // If ID is invalid or PDO is not available, return default
        if ($editionId === null || !$pdo instanceof PDO) {
            return 'N/A'; // Or 'Unknown', or ''
        }

        try {
            // --- Using corrected column name ---
            $tableName = 'sm_editions';     // Your Table Name
            $columnName = 'edition';        // CORRECTED Column Name (Field)
            // ----------------------------------

            // Basic check for valid identifiers (optional but safer)
            if (!preg_match('/^[a-zA-Z0-9_]+$/', $tableName) || !preg_match('/^[a-zA-Z0-9_]+$/', $columnName)) {
                 error_log("Invalid table or column name provided to getEditionName.");
                 return 'Error';
            }

            // Assumes the ID column in sm_editions is named 'id'
            $sql = "SELECT `" . $columnName . "` FROM `" . $tableName . "` WHERE `id` = :id LIMIT 1";
            $stmt = $pdo->prepare($sql);
            $stmt->bindParam(':id', $editionId, PDO::PARAM_INT);
            $stmt->execute();
            $result = $stmt->fetch(PDO::FETCH_ASSOC);

            // Return the name if found, otherwise a default string
            // Check if the column actually exists in the fetched result
            if ($result && isset($result[$columnName])) {
                return $result[$columnName];
            } else {
                 // Log if the expected column wasn't in the result (might indicate other issues)
                 if ($result) error_log("Column '$columnName' not found in result for getEditionName ID $editionId");
                 return 'Unknown Edition ID'; // Or specific message
            }

        } catch (PDOException $e) {
            // Log the error for administrators
            error_log("Error in getEditionName for ID $editionId: " . $e->getMessage());
            return 'Error'; // Return a generic error indicator for display
        }
    }
}
// ======================================================================
// === End function definition ===
// ======================================================================

?>

<style>
    /* Add a container for centered content with limited width */
    .content-container {
        max-width: 900px;
        margin: 0 auto;
        padding: 20px;
        background-color: #f9f9f9;
        border-radius: 10px;
        box-shadow: 0px 4px 10px rgba(0, 0, 0, 0.1);
        font-family: Arial, sans-serif;
    }

    h2 {
        text-align: center;
        color: #333;
        font-size: 24px;
        margin-bottom: 20px;
    }

    table {
        width: 100%;
        border-collapse: collapse;
        margin-top: 20px;
    }

    table th, table td {
        border: 1px solid #ddd;
        text-align: center;
        padding: 10px;
        font-size: 14px;
    }

    table th {
        background-color: #4CAF50;
        color: white;
    }

    table tr:nth-child(even) {
        background-color: #f2f2f2;
    }

    table a {
        color: #4CAF50;
        text-decoration: none;
    }

    table a:hover {
        text-decoration: underline;
    }

    .msg {
        text-align: center;
        color: #4CAF50;
        font-weight: bold;
        margin-top: 10px;
    }
</style>

<div class="content-container">
    <h2>Submissions with Other Statuses</h2>
    <span class="msg"><?php if (isset($msg)) { echo htmlspecialchars($msg, ENT_QUOTES, 'UTF-8'); } ?></span>

    <?php
        $rowCount = 0;
        $errorInfo = null;
        $stmt = null; // Initialize $stmt

        try {
            // Query to get submissions with status > 3, joining poets and status tables
            $query = "SELECT a.date_submitted, a.title, b.author_name, b.author_lastname, b.email, a.editionid, a.id AS sid,
                            s.status AS status_name
                        FROM sm_submissions AS a
                        INNER JOIN sm_poets AS b ON a.poetid = b.id
                        INNER JOIN sm_status AS s ON a.status = s.id
                        WHERE a.status > 3
                        ORDER BY a.date_submitted DESC";

            $stmt = $pdo->prepare($query);
            $stmt->execute();
            $rowCount = $stmt->rowCount();

        } catch (PDOException $e) {
            // In a production environment, log this error instead of displaying it directly
            error_log("Database error on editors-othertypes.php: " . $e->getMessage());
            $errorInfo = "A database error occurred. Please try again later."; // User-friendly message
        }
    ?>

    <?php if ($errorInfo): ?>
        <div style="color: red; text-align: center; padding: 10px; border: 1px solid red; background-color: #ffe0e0;">
            <?php echo htmlspecialchars($errorInfo, ENT_QUOTES, 'UTF-8'); ?>
        </div>
    <?php endif; ?>

    <table>
        <tr>
            <th>Date Submitted</th>
            <th>Title</th>
            <th>Author</th>
            <th>Email</th>
            <th>Edition</th> <th>Status</th>
            <th>&nbsp;</th>
        </tr>
        <?php
        // Proceed only if the statement executed without PDO errors
        if ($stmt && $errorInfo === null) {
            if ($rowCount > 0) {
                while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                    // Sanitize data for display
                    $safe_sid = htmlspecialchars($row['sid'] ?? '', ENT_QUOTES, 'UTF-8');
                    $safe_title = htmlspecialchars(stripslashes($row['title'] ?? ''), ENT_QUOTES, 'UTF-8');
                    $safe_author = htmlspecialchars(stripslashes($row['author_name'] ?? ''), ENT_QUOTES, 'UTF-8') . ' ' . htmlspecialchars(stripslashes($row['author_lastname'] ?? ''), ENT_QUOTES, 'UTF-8');
                    $safe_email = htmlspecialchars(stripslashes($row['email'] ?? ''), ENT_QUOTES, 'UTF-8');
                    $safe_status = htmlspecialchars(stripslashes($row['status_name'] ?? ''), ENT_QUOTES, 'UTF-8');

                    // Call the function (defined above) and sanitize its output
                    $edition_name = getEditionName($row['editionid'] ?? null);
                    $safe_edition_name = htmlspecialchars($edition_name ?? 'N/A', ENT_QUOTES, 'UTF-8');

                    ?>
                    <tr>
                        <td><?= date('M. d, Y h:ia', strtotime($row['date_submitted'])) ?></td>
                        <td><a href="view-submission.php?id=<?= $safe_sid ?>"><?= $safe_title ?></a></td>
                        <td><?= $safe_author ?></td>
                        <td><a href="mailto:<?= $safe_email ?>"><?= $safe_email ?></a></td>
                        <td><?= $safe_edition_name ?></td> <td><?= $safe_status ?></td>
                        <td>[<a href="editors-update-status.php?id=<?= $safe_sid ?>">Update Status</a>]</td>
                    </tr>
                    <?php
                } // End while loop
            } else {
                // Display a message if no rows were found
                ?>
                <tr>
                    <td colspan="7" style="text-align: center; padding: 20px;">No submissions found with a status ID greater than 3.</td>
                </tr>
                <?php
            } // End if ($rowCount > 0)
        } elseif (!$errorInfo) { // Only show this if no PDO error occurred but $stmt is somehow null
             ?>
             <tr>
                 <td colspan="7" style="text-align: center; padding: 20px; color: orange;">Could not prepare database query (unexpected error).</td>
             </tr>
             <?php
        }
        ?>
    </table>
</div>

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