<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

$servername = "localhost";
$username   = "nmskipton_cinema";
$password   = "tp271vb8tp271vb8";
$dbname     = "nmskipton_skipton";

try {
    $conn = new mysqli($servername, $username, $password, $dbname);
    $conn->set_charset("utf8mb4");

    $string = file_get_contents('programme.xml');
    if ($string === false) {
        throw new Exception("Could not read programme.xml");
    }

    $xml = new SimpleXMLElement($string);

    $sql = "INSERT INTO jacro_films
        (
            `ShortFilmTitle`,
            `FilmTitle`,
            `Code`,
            `Certificate`,
            `Is3d`,
            `Rentrak`,
            `ReleaseDate`,
            `RunningTime`,
            `Synopsis`,
            `Certificate_desc`,
            `Img_1s`,
            `Img_bd`,
            `Digital`,
            `StartDate`,
            `ComingSoon`,
            `IMDBCode`,
            `Genre`,
            `YouTube`,
            `GenreCode`
        )
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ON DUPLICATE KEY UPDATE
            ShortFilmTitle   = VALUES(ShortFilmTitle),
            FilmTitle        = VALUES(FilmTitle),
            Code             = VALUES(Code),
            Certificate      = VALUES(Certificate),
            Is3d             = VALUES(Is3d),
            Rentrak          = VALUES(Rentrak),
            ReleaseDate      = VALUES(ReleaseDate),
            RunningTime      = VALUES(RunningTime),
            Synopsis         = VALUES(Synopsis),
            Certificate_desc = VALUES(Certificate_desc),
            Img_1s           = VALUES(Img_1s),
            Img_bd           = VALUES(Img_bd),
            Digital          = VALUES(Digital),
            StartDate        = VALUES(StartDate),
            ComingSoon       = VALUES(ComingSoon),
            IMDBCode         = VALUES(IMDBCode),
            Genre            = VALUES(Genre),
            YouTube          = VALUES(YouTube),
            GenreCode        = VALUES(GenreCode)";

    $stmt = $conn->prepare($sql);

    foreach ($xml->Films->Film as $item) {
        $shortFilmTitle   = (string)$item->ShortFilmTitle;
        $filmTitle        = (string)$item->FilmTitle;
        $code             = (string)$item->Code;
        $certificate      = (string)$item->Certificate;
        $is3d             = (string)$item->Is3d;
        $rentrak          = (string)$item->Rentrak;
        $releaseDate      = (string)$item->ReleaseDate;
        $runningTime      = (string)$item->RunningTime;
        $synopsis         = (string)$item->Synopsis;
        $certificateDesc  = (string)$item->Certificate_desc;
        $img1s            = (string)$item->Img_1s;
        $imgBd            = (string)$item->img_bd;
        $digital          = (string)$item->Digital;
        $startDate        = (string)$item->StartDate;
        $comingSoon       = (string)$item->ComingSoon;
        $imdbCode         = (string)$item->IMDBCode;
        $genre            = (string)$item->Genre;
        $youtube          = (string)$item->Youtube;
        $genreCode        = (string)$item->GenreCode;

        $fixedimage = str_replace(
            "https://theroxy.org/tmdb1s/COTLEE/11.jpg",
            "https://theroxy.org/appimages/us/custom/COTLEE/andre%20rieu%202019%20maastricht%20concert%20shall%20we%20dance.jpg",
            $img1s
        );

        echo htmlspecialchars($shortFilmTitle, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8') . ' - ';
        echo htmlspecialchars($filmTitle, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8') . ' - ';
        echo htmlspecialchars($code, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8') . ' - ';
        echo htmlspecialchars($certificate, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8') . ' - ';
        echo htmlspecialchars($is3d, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8') . ' - ';
        echo htmlspecialchars($rentrak, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8') . ' - ';
        echo htmlspecialchars($releaseDate, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8') . ' - ';
        echo htmlspecialchars($runningTime, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8') . ' - ';
        echo htmlspecialchars($synopsis, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8') . ' - ';
        echo htmlspecialchars($certificateDesc, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8') . ' - ';
        echo htmlspecialchars($fixedimage, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8') . ' - ';
        echo htmlspecialchars($imgBd, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8') . ' - ';
        echo htmlspecialchars((string)$item->Img_title, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8') . ' - ';
        echo htmlspecialchars($digital, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8') . ' - ';
        echo htmlspecialchars($startDate, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8') . ' - ';
        echo htmlspecialchars($comingSoon, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8') . ' - ';
        echo htmlspecialchars($imdbCode, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8') . ' - ';
        echo htmlspecialchars($genre, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8') . ' - ';
        echo htmlspecialchars($youtube, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8') . ' - ';
        echo htmlspecialchars($genreCode, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');

        echo '<br/>&nbsp;<br />&nbsp;<br />';

        $stmt->bind_param(
            "sssssssssssssssssss",
            $shortFilmTitle,
            $filmTitle,
            $code,
            $certificate,
            $is3d,
            $rentrak,
            $releaseDate,
            $runningTime,
            $synopsis,
            $certificateDesc,
            $fixedimage,
            $imgBd,
            $digital,
            $startDate,
            $comingSoon,
            $imdbCode,
            $genre,
            $youtube,
            $genreCode
        );

        $stmt->execute();

        echo "Record inserted/updated successfully<br />&nbsp;<br />";
    }

    $stmt->close();
    $conn->close();

} catch (Throwable $e) {
    echo "Error: " . htmlspecialchars($e->getMessage(), ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
}
?>