defined('_JEXEC') or die;
 
use Joomla\CMS\Factory;
use Joomla\CMS\Table\Table;
 
try {
    // Log that the script was triggered
    Factory::getApplication()->enqueueMessage('Submission update script triggered!', 'info');
 
    $db = Factory::getContainer()->get('DatabaseDriver');
 
    // Extract SubmissionId
    $submissionId = (int) $submission->SubmissionId;
 
    // Fetch the latest submission values from josrh_rsform_submission_values
    $query = $db->getQuery(true)
        ->select($db->quoteName(['FieldName', 'FieldValue']))
        ->from($db->quoteName('josrh_rsform_submission_values'))
        ->where($db->quoteName('SubmissionId') . ' = ' . $submissionId);
    $db->setQuery($query);
    $rawValues = $db->loadObjectList('FieldName');
 
    if (!$rawValues) {
        throw new Exception('Failed to fetch submission data for ID: ' . $submissionId);
    }
 
    // Map submission values to an array for easy access
    $submissionValues = [];
    foreach ($rawValues as $fieldName => $fieldObject) {
        $submissionValues[$fieldName] = $fieldObject->FieldValue;
    }
 
    // Extract ArticleId
    $articleId = isset($submissionValues['ArticleId']) ? (int) $submissionValues['ArticleId'] : 0;
 
    // Check if the ArticleId is valid
    if (!$articleId) {
        Factory::getApplication()->enqueueMessage('No ArticleId found in the submission.', 'error');
        return;
    }
 
    // Map fields to josrh_content
    $articleData = [
        'id' => $articleId, // Primary key
        'title' => $submissionValues['Title'], // Title of the article
        'introtext' => $submissionValues['Description'], // Intro text
        'catid' => (int) $submissionValues['Category'], // Category ID
        'modified' => Factory::getDate()->toSql(), // Update modified date
        'modified_by' => Factory::getUser()->id, // User making the change
    ];
 
    // Load and update the article
    $article = Table::getInstance('Content', 'JTable');
    if (!$article->load($articleId)) {
        Factory::getApplication()->enqueueMessage('No article found with ID: ' . $articleId, 'error');
        return;
    }
 
    $article->bind($articleData);
 
    // Validate and save the article
    if (!$article->check() || !$article->store()) {
        throw new Exception('Error updating article: ' . $article->getError());
    }
 
    Factory::getApplication()->enqueueMessage('Article updated successfully! Article ID: ' . $articleId, 'success');
 
    // Map fields to josrh_fields_values
    $customFields = [
        'email' => $submissionValues['Email'],
        'phone' => $submissionValues['Phone'],
        'locationaddress' => $submissionValues['Address'],
        'google-map-coordinates' => $submissionValues['Google_Map_Coordinates'],
        'website-booking-link' => $submissionValues['Booking_Link'],
        'eircode' => $submissionValues['Eircode']
    ];
 
    foreach ($customFields as $fieldName => $fieldValue) {
        // Get the field ID for the custom field
        $fieldQuery = $db->getQuery(true)
            ->select($db->quoteName('id'))
            ->from($db->quoteName('josrh_fields'))
            ->where($db->quoteName('name') . ' = ' . $db->quote($fieldName));
        $db->setQuery($fieldQuery);
        $fieldId = $db->loadResult();
 
        if (!$fieldId) {
            Factory::getApplication()->enqueueMessage('Custom field not found: ' . $fieldName, 'warning');
            continue;
        }
 
        // Check if a value exists for this field and article
        $valueQuery = $db->getQuery(true)
            ->select($db->quoteName('field_id'))
            ->from($db->quoteName('josrh_fields_values'))
            ->where($db->quoteName('field_id') . ' = ' . (int) $fieldId)
            ->where($db->quoteName('item_id') . ' = ' . (int) $articleId);
        $db->setQuery($valueQuery);
        $fieldValueExists = $db->loadResult();
 
        if ($fieldValueExists) {
            // Update the existing field value
            $updateQuery = $db->getQuery(true)
                ->update($db->quoteName('josrh_fields_values'))
                ->set($db->quoteName('value') . ' = ' . $db->quote($fieldValue))
                ->where($db->quoteName('field_id') . ' = ' . (int) $fieldId)
                ->where($db->quoteName('item_id') . ' = ' . (int) $articleId);
            $db->setQuery($updateQuery);
            $db->execute();
        } else {
            // Insert a new field value
            $insertQuery = $db->getQuery(true)
                ->insert($db->quoteName('josrh_fields_values'))
                ->columns(['field_id', 'item_id', 'value'])
                ->values((int) $fieldId . ', ' . (int) $articleId . ', ' . $db->quote($fieldValue));
            $db->setQuery($insertQuery);
            $db->execute();
        }
    }
 
    Factory::getApplication()->enqueueMessage('Custom fields updated successfully!', 'success');
} catch (Exception $e) {
    // Log errors
    Factory::getApplication()->enqueueMessage('Error in submission update script: ' . $e->getMessage(), 'error');
}