<?php
require_once "/var/www/forceutf8/src/ForceUTF8/Encoding.php";
require_once "/var/www/utils/tl_error_handling.php";

use \ForceUTF8\Encoding;

header('Content-Type: text/html; charset=utf-8');

// Configuratie
require_once '/var/www/html/v13/config.php';

// Databaseverbinding
$mysqli = new mysqli(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_NAME);
if ($mysqli->connect_errno) {
    die("Connectiefout: " . $mysqli->connect_error);
}

// Data ophalen met prepared statement
function fetchContent($mysqli) {
    $query = "
        SELECT pid, uid, l10n_source, sys_language_uid, pi_flexform, bodytext
        FROM tt_content
        WHERE CType = 'tx_codehighlight_codesnippet'
          AND deleted = 0
          AND hidden = 0
          AND starttime <= UNIX_TIMESTAMP()
          AND (endtime = 0 OR endtime > UNIX_TIMESTAMP())
        ORDER BY pid, sys_language_uid, uid
    ";

    $stmt = $mysqli->prepare($query);
    $rows = [];
    if ($stmt) {
        $stmt->execute();
        $result = $stmt->get_result();
        while ($row = $result->fetch_assoc()) {
            $rows[] = $row;
        }
        $stmt->close();
    } else {
        myErrorHandler($mysqli->errno, "Queryfout: " . $mysqli->error, __FILE__, __LINE__);    
    }
    return $rows;
}

// FlexForm uitlezen en filename extraheren
function getFilenameFromFlexForm($xmlString) {
    libxml_use_internal_errors(true);
    $xml = simplexml_load_string($xmlString);
    if (!$xml) {
        myErrorHandler(0,"Ongeldige XML in FlexForm", __FILE__, __LINE__); 
        return false;
    }

    foreach ($xml->data->sheet as $sheet) {
        if ((string)$sheet['index'] === 'sDEF') {
            foreach ($sheet->language->field as $field) {
                if ((string)$field['index'] === 'filename') {
                    return trim((string)$field->value);
                }
            }
        }
    }
    return null;
}

// Content ophalen en tonen
function parseFlexFormAndOutput($mysqli, $rows) {
    echo '<table id="myTable2" class="table table-bordered">';
    echo '<thead><tr><th>pid</th><th>uid</th><th>l10n_source</th><th>sys_language_uid</th><th>filename</th><th>content</th></tr></thead>';

    foreach ($rows as $row) {
        echo '<tr>';
        echo '<td>' . htmlspecialchars($row['pid']) . '</td>';
        echo '<td>' . htmlspecialchars($row['uid']) . '</td>';
        echo '<td>' . htmlspecialchars($row['l10n_source']) . '</td>';
        echo '<td>' . htmlspecialchars($row['sys_language_uid']) . '</td>';

        $filename = getFilenameFromFlexForm($row['pi_flexform']);
/*
Als filename leeg is, is dit niet fout maar met opzet gedaan.
Het is een optie om geen bestand te gebruiken, maar de content direct in de bodytext te zetten.
*/

        if (!$filename) {
            echo '<td>Bodytext niet uit file geladen maar direct ingevoerd in de backend</td>';
            $content = $row['bodytext']; // Directe content uit bodytext
        } else {

            echo '<td>';

            if (strpos($filename, 'FILE:') === 0) {

                $url = str_replace('FILE:', '', $filename);
                $absolutePath = realpath($url);

                // Veiligere padcontrole: alleen bestanden binnen baseDir zijn toegestaan
                if (
                    !$absolutePath ||
                    strncmp($absolutePath, BASEDIR, BASEDIR_LENGTH) !== 0 ||
                    !is_file($absolutePath) ||
                    !is_readable($absolutePath)
                ) {
                    myErrorHandler(1, "Ongeldig of onleesbaar pad: " . $url . " - ". $absolutePath, __FILE__, __LINE__);
                    echo "Bestand niet toegestaan of niet leesbaar.";
                    echo '<td></td>'; // Lege cel voor content
                } else {
                    $content = @file_get_contents($absolutePath);
                    if ($content === false) {
                        myErrorHandler(2, "Leesfout: " . ($url ?? "onbekend pad: " . $absolutePath), __FILE__, __LINE__);
                        echo "Bestand kon niet worden gelezen.";
                        echo '<td></td>'; // Lege cel voor content
                    } else {
                        echo htmlspecialchars($filename);
                        $encoding = mb_detect_encoding($content, 'UTF-8, ISO-8859-1', true);
                        if ($encoding !== 'UTF-8') {
                            $content = Encoding::toUTF8($content);
                        }
                        $escaped = htmlspecialchars($content);
                        echo '<td><pre class="codeInTable"><code>' . substr($escaped, 0, 100) . '<br>....</code></pre></td>';
                        // updateBodyText($mysqli, $row['uid'], $content);
                        updateBodyText($mysqli, $row['uid'], $content);
                    }
                }
            }

            echo '</td>';

        }
        
        echo '</tr>';
    
    }

    echo '</table>';
}

// Database bijwerken
function updateBodyText($mysqli, $uid, $content) {
    $stmt = $mysqli->prepare("UPDATE tt_content SET bodytext = ?, header = '' WHERE uid = ?");
    if ($stmt) {
        $stmt->bind_param("si", $content, $uid);
        $stmt->execute();
        $stmt->close();
    } else {
        myErrorHandler($mysqli->errno, "Updatefout: " . $mysqli->error, __FILE__, __LINE__);
    }
}

// Data ophalen met prepared statement
function alignContentForTranslations ($mysqli) {

    // Prepare the UPDATE statement
    $query = "
        UPDATE tt_content AS tt_content_dest
        JOIN tt_content AS tt_content_src 
            ON tt_content_src.uid = tt_content_dest.l10n_source
        SET 
            tt_content_dest.header = tt_content_src.header,
            tt_content_dest.bodytext = tt_content_src.bodytext,
            tt_content_dest.pi_flexform = tt_content_src.pi_flexform
        WHERE 
            tt_content_dest.sys_language_uid <> 0
            AND tt_content_dest.CType = 'tx_codehighlight_codesnippet'
            AND tt_content_dest.deleted = 0
            AND tt_content_dest.hidden = 0
            AND tt_content_dest.starttime <= UNIX_TIMESTAMP()
            AND (tt_content_dest.endtime = 0 OR tt_content_dest.endtime > UNIX_TIMESTAMP())
            AND tt_content_dest.l10n_source IS NOT NULL";

    $stmt = $mysqli->prepare($query);
    if ($stmt === false) {
        die("Error preparing statement: " . $mysqli->error);
    }

    // Execute the statement
    if ($stmt->execute()) {
        echo "<br>";
        echo "<p>Content tx_codehighlight_codesnippet elements translated successfully for language [1] and [2] from [0]!</p>";
        echo "<p>Fields header, bodytext and pi_flexform (Options) are made indentical to language [0]</p>";
        echo "<br>";
    } else {
        myErrorHandler($stmt->error, "Error translating Content tx_codehighlight_codesnippet elementsout", __FILE__, __LINE__);  
    }

    // Close the statement
    $stmt->close();
}

// Script starten
$data = fetchContent($mysqli);
parseFlexFormAndOutput($mysqli, $data);
alignContentForTranslations($mysqli);
$mysqli->close();