<?php

include_once "settings.php";

// Get and use an item record ID (ItemRecordID) to use for a data pull
$ItemID = htmlspecialchars($_GET["itemid"]);

// -------------------- BEGIN DATABASE QUERIES --------------------

// Establish atabase connection
$db = new SQLite3('metadata.sqlite');

// ---------- Pull data from books table ----------
$book = $db->query("SELECT id, title, date(timestamp), author_sort, strftime('%Y',pubdate) AS pubyear, date(last_modified) FROM books WHERE id = '$ItemID'");

while ($row = $book->fetchArray()) {
    $row_id = $row['id'];
    $row_title = $row['title'];
	$row_created = $row['date(timestamp)'];
	$row_author_sort = $row['author_sort'];
    $row_pubdate = $row['pubyear'];
	$row_modified = $row['date(last_modified)'];
}

// ---------- Pull data from authors table ----------
$author = $db->query("SELECT name FROM authors
INNER JOIN books_authors_link
ON books_authors_link.author = authors.id
WHERE books_authors_link.book = '$ItemID'");

while ($row = $author->fetchArray()) {
    $row_creator = $row['name'];
}

// ---------- Pull data from comments table ----------
$summary = $db->query("SELECT text FROM comments WHERE book = '$ItemID'");

while ($row = $summary->fetchArray()) {
	$row_summary = $row['text'];
}

// ---------- Pull data from publishers table ----------

$publisher = $db->query("SELECT name from publishers
INNER JOIN books_publishers_link
ON books_publishers_link.publisher = publishers.id
WHERE books_publishers_link.book = '$ItemID'");

while ($row = $publisher->fetchArray()) {
    $row_publisher = $row['name'];
}

// ---------- Pull data from tags table ----------

$tags = $db->query("SELECT name FROM tags
INNER JOIN books_tags_link
ON books_tags_link.tag = tags.id
WHERE tags.name NOT LIKE 'infopump%' 
AND books_tags_link.book = '$ItemID'");

// ---------- Pull data from tags table for Similar items ----------

// New similar tags query
$similartags = $db->query("SELECT name FROM tags
WHERE name IN (SELECT name FROM tags
INNER JOIN books_tags_link
ON books_tags_link.tag = tags.id 
AND books_tags_link.book = '$ItemID'
ORDER BY RANDOM() LIMIT 2)");

$simtag = array();

while($row = $similartags->fetchArray()) {
    $simtag[] = $row['name'];
}

$getsimilar = $db->query("SELECT books.id, title, author_sort
FROM books
INNER JOIN books_tags_link
ON books_tags_link.book = books.id
INNER JOIN tags
ON tags.id = books_tags_link.tag
WHERE (tags.name LIKE '$simtag[0]'
OR tags.name LIKE '$simtag[1]')
AND books.id != '$ItemID' LIMIT 4");


// ---------- Pull data from identifiers table ----------

$identifiers = $db->query("SELECT type, val FROM identifiers WHERE book = '$ItemID'");

// ---------- Pull data from languages table ----------

$languages = $db->query("SELECT languages.lang_code AS lang_code FROM languages
INNER JOIN books_languages_link
ON books_languages_link.lang_code = languages.id
WHERE book = '$ItemID'
ORDER BY books_languages_link.item_order");

// ---------- Pull data from tags table to dertermine type ----------

$type = $db->query("SELECT custom_column_1.value AS itemtype
FROM books_custom_column_1_link
INNER JOIN custom_column_1
ON custom_column_1.id = books_custom_column_1_link.value
WHERE books_custom_column_1_link.book = '$ItemID'");

while ($row = $type->fetchArray()) {
    $row_type = mb_convert_case($row['itemtype'], MB_CASE_TITLE, "UTF-8");
}

// ---------- Pull series information ----------

$series = $db->query("SELECT series.name AS series, books.series_index AS seriesindex
FROM series
INNER JOIN books_series_link
ON books_series_link.series = series.id
INNER JOIN books
ON books.id = books_series_link.book
WHERE books_series_link.book = '$ItemID'");

while ($row = $series->fetchArray()) {
    $row_series = $row['series'];
    $row_seriesindex = $row['seriesindex'];
}

// ---------- Pull publisher location information ----------

$publisher_location = $db->query("SELECT custom_column_2.value AS publoc
FROM books
INNER JOIN
books_custom_column_2_link
ON books_custom_column_2_link.book = books.id
INNER JOIN
custom_column_2
ON custom_column_2.id = books_custom_column_2_link.value
WHERE
books.id = '$ItemID'");

while ($row = $publisher_location->fetchArray()) {
    $row_publocation = $row['publoc'];
}

// ---------- Pull subtype information ----------

$subtype = $db->query("SELECT custom_column_3.value AS subtype
FROM books
INNER JOIN
books_custom_column_3_link
ON books_custom_column_3_link.book = books.id
INNER JOIN
custom_column_3
ON custom_column_3.id = books_custom_column_3_link.value
WHERE
books.id = '$ItemID'");

while ($row = $subtype->fetchArray()) {
    $row_subtype = $row['subtype'];
}


// -------------------- END DATABASE QUERIES --------------------

?>
<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="utf-8" />
        <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no" />
        <meta name="description" content=<?php echo '"Infopump - Item record - '.$row_title.'"/>'; ?> 
	    <meta name="keywords" content="infopump, bibliography, media, catalog" />
	    <meta name="author" content="Daniel Messer" />

        <!-- Social Media Integration -->
        <meta property="og:title" content=<?php echo '"'.$SiteName.' - Item Record" />';?>

	    <meta property="og:image" content=<?php echo '"'.$SiteURL.'/images/'.$row_id.'.jpg" />'; ?>

	    <meta property="og:url" content=<?php echo '"'.$SiteURL.'/itemrecord.php?itemid='.$row_id.'" />'; ?>

	    <meta property="og:site_name" content=<?php echo '"'.$SiteName.'" />'; ?>

	    <meta property="og:description" content=<?php echo '"'.$row_title.' - '.$row_creator.'" />'; ?>

	    <meta name="twitter:title" content=<?php echo '"'.$row_title.' - '.$row_creator.'" />'; ?>

	    <meta name="twitter:image" content=<?php echo '"'.$SiteURL.'/images/'.$row_id.'.jpg" />'; ?>

	    <meta name="twitter:url" content=<?php echo '"'.$SiteURL.'/itemrecord.php?itemid='.$row_id.'" />'; ?>

	    <meta name="twitter:card" content="summary" />

        <!-- Zotero and RDF Related Metadata -->

        <meta name="citation_title" content=<?php echo '"'.$row_title.'" />'; ?>

        <meta name="citation_authors" content=<?php echo '"'.$row_author_sort.'" />'; ?>

        <meta name="citation_publisher" content=<?php echo '"'.$row_publisher.'" />'; ?>

        <?php
            while ($row = $identifiers->fetchArray()) {
                $row_id_type = $row['type'];
                $row_id_val = $row['val'];  
                if ($row_id_type == 'isbn') {
                    echo '<meta name="citation_isbn" content="'.$row_id_val.'" />';
                }
            }
        ?>

        <?php echo '<title>Item Record: '.$row_title.'</title>'; ?>
        <!-- Favicon-->
        <link rel="icon" type="image/x-icon" href="item/assets/favicon.ico" />
        <!-- Bootstrap icons-->
        <link href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.5.0/font/bootstrap-icons.css" rel="stylesheet" />
        <!-- Core theme CSS (includes Bootstrap)-->
        <link href="item/css/styles.css" rel="stylesheet" />

    </head>

    <body>
        <!-- Navigation-->
        <nav class="navbar navbar-expand-lg navbar-light bg-light">
            <div class="container px-4 px-lg-5">
                <a class="navbar-brand" href="#!"><?php echo '<strong>'.$row_title.'</strong> | Item Record '.$ItemID; ?></a>
                <button class="navbar-toggler" type="button" data-bs-toggle="collapse" data-bs-target="#navbarSupportedContent" aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation"><span class="navbar-toggler-icon"></span></button>
                <div class="collapse navbar-collapse" id="navbarSupportedContent">
                    <ul class="navbar-nav me-auto mb-2 mb-lg-0 ms-lg-4">
                        <li class="nav-item"><a class="nav-link active" aria-current="page" href="index.php">Home</a></li>
                        <!-- <li class="nav-item"><a class="nav-link" href="#!">About</a></li>
                        <li class="nav-item dropdown">
                            <a class="nav-link dropdown-toggle" id="navbarDropdown" href="#" role="button" data-bs-toggle="dropdown" aria-expanded="false">Shop</a>
                            <ul class="dropdown-menu" aria-labelledby="navbarDropdown">
                                <li><a class="dropdown-item" href="#!">All Products</a></li>
                                <li><hr class="dropdown-divider" /></li>
                                <li><a class="dropdown-item" href="#!">Popular Items</a></li>
                                <li><a class="dropdown-item" href="#!">New Arrivals</a></li>
                            </ul>
                        </li> -->
                    </ul>
                    <!-- <form class="d-flex">
                        <button class="btn btn-outline-dark" type="submit">
                            <i class="bi-cart-fill me-1"></i>
                            Cart
                            <span class="badge bg-dark text-white ms-1 rounded-pill">0</span>
                        </button>
                    </form> -->
                </div>
            </div>
        </nav>
        <!-- Product section-->
        <section class="py-5">
            <div class="container px-4 px-lg-5 my-5">
                <div class="row gx-4 gx-lg-5 align-items-top">
                    <div class="col-md-6"><img class="card-img-top mb-5 mb-md-0" src="images/<?php echo $row_id.'.jpg' ?>" alt="<?php echo $row_title; ?>" title="<?php echo $row_title; ?>" /></div>
                    <div class="col-md-6">
                        <div class="small mb-1"><?php echo 'Item Record '.$ItemID; ?></div>
                        <h1 class="display-5 fw-bolder"><?php echo $row_title; ?></h1>
                        <div class="medium">
                            <?php
                                if ($row_series) {
                                    echo '<hr />';
                                    echo '<strong>Number '.$row_seriesindex.' in the <a href="results.php?se='.$row_series.'">'.$row_series.' </a>series</strong><br />';
                                    echo '<hr />';
                                } else {
                                    echo '<hr>';
                                }
                            ?>
                        </div>
                        <p class="lead"><?php echo $row_summary; ?></p>
                        <div class="d-flex">
                            <!-- <input class="form-control text-center me-3" id="inputQuantity" type="num" value="1" style="max-width: 3rem" />
                            <button class="btn btn-outline-dark flex-shrink-0" type="button">
                                <i class="bi-cart-fill me-1"></i>
                                Add to cart
                            </button> -->
                        </div>
                    </div>
                </div>
                <div class="row gx-4 gx-lg-5 align-items-top">
                    <div class="col-md-6"> <!-- BEGIN CITATIONS -->
                        <a name="citations"></a>
                        <h3 class="fw-bolder"><u>Bibliographic Citations</u></h3><br />
                        <p class = "small"><em>Note: You may need to slightly edit citations for final use. Citations are based upon available data. Refer to your format guides for proper bibliographic citations.</em></p>
                        
                        <!-- BEGIN CHICAGO CITATION -->
                        <p class="metadata"><strong>Chicago</strong></p>
                        <?php 
                            if ($row_type == 'Text') {
                                echo '<p class="metadata">'.$row_author_sort.'. <em>'.$row_title.'.</em> '.$row_publocation.': '.$row_publisher.', '.$row_pubdate.'.<br />';
                                    echo '<div id=chicago style="display: none;">';
                                echo '<p>'.$row_author_sort.'. <em>'.$row_title.'.</em> '.$row_publocation.': '.$row_publisher.', '.$row_pubdate.'</p>';
                                    echo '</div>';
                            } elseif ($row_type == 'Music') {
                                echo '<p class="metadata">'.$row_author_sort.', <em>'.$row_title.'.</em> '.$row_publisher.', '.$row_pubdate.'.<br />';
                                    echo '<div id=chicago style="display: none;">';
                                echo '<p>'.$row_author_sort.', "'.$row_title.'." '.$row_publisher.', '.$row_pubdate.'</p>';
                                    echo '</div>';
                            } else {
                                echo '<p class="metadata">'.$row_author_sort.', director. <em>'.$row_title.'.</em> '.$row_publisher.', '.$row_pubdate.'.<br />';
                                    echo '<div id=chicago style="display: none;">';
                                echo '<p><p class="metadata">'.$row_author_sort.', director. <em>'.$row_title.'.</em> '.$row_publisher.', '.$row_pubdate.'</p>';
                                    echo '</div>';
                            } 
                        ?>
                        <a href="#citations" onclick="copyToClip(document.getElementById('chicago').innerHTML)">Copy Chicago citation</a></p>
                        <script>
                            function copyToClip(str) {
                                function listener(e) {
                                    e.clipboardData.setData("text/html", str);
                                    e.clipboardData.setData("text/plain", str);
                                    e.preventDefault();
                                }
                                document.addEventListener("copy", listener);
                                document.execCommand("copy");
                                document.removeEventListener("copy", listener);
                            };
                        </script>
                        <!-- END CHICAGO CITATION -->

                        <hr>

                        <!-- BEGIN APA CITATION -->
                        <p class="metadata"><strong>APA</strong></p>
                        <?php
                            $APA_author = explode(', ' ,$row_author_sort);
                            $APA_first = mb_strimwidth($APA_author[1], 0, 1);
                            
                            if ($row_type == 'Text') {
                                echo '<p class="metadata">'.$APA_author[0].', '.$APA_first.'. ('.$row_pubdate.'). '.$row_title.'. '.$row_publisher.'.</p>';
                                echo '<div id=apa style="display: none;">';
                                echo '<p>'.$APA_author[0].', '.$APA_first.'. ('.$row_pubdate.'). '.$row_title.'. '.$row_publisher.'.</p>';
                                echo '</div>';  
                            } elseif ($row_type == 'Music') {
                                echo '<p class="metadata">'.$row_creator.'. ('.$row_pubdate.'). <em>'.$row_title.'</em> [Album]. '.$row_publisher.'.</p>';
                                echo '<div id=apa style="display: none;">';
                                echo '<p>'.$row_creator.'. ('.$row_pubdate.'). <em>'.$row_title.'</em>. '.$row_publisher.'.</p>';
                                echo '</div>';
                            } else {
                                echo '<p class="metadata">'.$APA_author[0].', '.$APA_first.'. (Director). ('.$row_pubdate.'). <em>'.$row_title.'</em> [Film]. '.$row_publisher.'.</p>';
                                echo '<div id=apa style="display: none;">';
                                echo '<p>'.$APA_author[0].', '.$APA_first.'. (Director). ('.$row_pubdate.'). <em>'.$row_title.'</em> [Film]. '.$row_publisher.'.</p>';
                                echo '</div>';
                            }                        
                        ?>
                        <a href="#citations" onclick="copyToClip(document.getElementById('apa').innerHTML)">Copy APA citation</a></p>
                        <script>
                            function copyToClip(str) {
                                function listener(e) {
                                    e.clipboardData.setData("text/html", str);
                                    e.clipboardData.setData("text/plain", str);
                                    e.preventDefault();
                                }
                                document.addEventListener("copy", listener);
                                document.execCommand("copy");
                                document.removeEventListener("copy", listener);
                            };
                        </script>
                        <!-- END APA CITATION -->

                        <hr>

                        <!-- MLA CITATION -->
                        <p class="metadata"><strong>MLA</strong></p>
                        <?php
                            if ($row_type == 'Text') {
                                echo '<p class="metadata">'.$row_author_sort.'. <em>'.$row_title.'.</em> '.$row_publisher.', '.$row_pubdate.'.<br />';
                                echo '<div id=mla style="display: none;">';
                                    echo '<p>'.$row_author_sort.'. <em>'.$row_title.'.</em> '.$row_publisher.', '.$row_pubdate.'.</p>';
                                echo '</div>';
                            } elseif ($row_type == 'Music') {
                                echo '<p class="metadata">'.$row_author_sort.'. <em>'.$row_title.'.</em> '.$row_publisher.', '.$row_pubdate.'.<br />';
                                echo '<div id=mla style="display: none;">';
                                    echo '<p>'.$row_author_sort.'. <em>'.$row_title.'.</em> '.$row_publisher.', '.$row_pubdate.'.</p>';
                                echo '</div>';
                            } else {
                                echo '<p class="metadata">'.$row_author_sort.', director. <em>'.$row_title.'.</em> '.$row_publisher.', '.$row_pubdate.'.<br />';
                                echo '<div id=mla style="display: none;">';
                                    echo '<p>'.$row_author_sort.', director. <em>'.$row_title.'.</em> '.$row_publisher.', '.$row_pubdate.'.</p>';
                                echo '</div>';
                            }

                        ?>
                        <a href="#citations" onclick="copyToClip(document.getElementById('mla').innerHTML)">Copy MLA citation</a></p>
                        <script>
                            function copyToClip(str) {
                                function listener(e) {
                                    e.clipboardData.setData("text/html", str);
                                    e.clipboardData.setData("text/plain", str);
                                    e.preventDefault();
                                }
                                document.addEventListener("copy", listener);
                                document.execCommand("copy");
                                document.removeEventListener("copy", listener);
                            };
                        </script>

                    </div> <!-- END CITATIONS -->

                    <!-- BEGIN METADATA -->                
                    <div class="col-md-6"> 
                        <h3 class="fw-bolder"><u>Metadata</u></h3>
                        <p class="metadata"><br />
                        <?php
                            echo '<strong>Item Control Number: </strong>'.$ItemID.'<br />';
                            echo '<strong>Title:</strong> '.$row_title.'<br />';
                            echo '<strong>Creator:</strong> <a href="results.php?au='.$row_author_sort.'">'.$row_author_sort.'</a><br />';
                            if ($row_series) {
                                echo '<strong>Series:</strong> <a href="results.php?se='.$row_series.'">'.$row_series.'</a><br />';
                                echo '<strong>Series Index:</strong> '.$row_seriesindex.'<br />';
                            }
                            echo '<strong>Publisher:</strong> '.$row_publisher.'<br />';
                            echo '<strong>Location:</strong> '.$row_publocation.'<br />';
                            echo '<strong>Date: </strong>'.$row_pubdate.'<br />';
                            echo '<strong>Type: </strong> <a href="results.php?ty='.$row_type.'">'.$row_type.'</a><br />';
                            echo '<strong>Subtype: </strong> <a href="results.php?st='.$row_subtype.'">'.$row_subtype.'</a><br />';
                            echo '<strong>Subjects: </strong>';
                            while ($row = $tags->fetchArray()) {
                                $row_tags = $row['name'];
                                //echo '['.$row_tags.'] ';
                                echo '[<a href="results.php?su='.$row_tags.'">'.$row_tags.'</a>]';
                            }
                            echo '<br/ ><strong>Identifiers: </strong><br />';
                            while ($row = $identifiers->fetchArray()) {   
                                $row_id_type = $row['type'];
                                $row_id_val = $row['val'];                                
                                //echo '<span style="margin-left: 10px;">'.$row_id_type.': ' .$row_id_val.'</span><br />';
                                if ($row_id_type == 'google') {
                                    $identifierURL = 'https://books.google.com/books?id=';
                                } elseif ($row_id_type == 'isbn') {
                                    $identifierURL = 'https://www.librarything.com/isbn/';
                                } elseif ($row_id_type == 'oclc') {
                                    $identifierURL = 'https://worldcat.org/title/';
                                } elseif ($row_id_type == 'tmdb') { 
                                    $identifierURL = 'https://www.themoviedb.org/movie/';
                                } else {
                                    $identifierURL = '#'; 
                                }
                                echo '<span style="margin-left: 10px;">'.$row_id_type.': <a href="'.$identifierURL.$row_id_val.'" target="_blank">'.$row_id_val.'</a></span><br />';
                            }
                            echo '<strong>Language(s): </strong>';
                            while ($row = $languages->fetchArray()) {
                                $row_lang_code = $row['lang_code'];
                                echo '[ '.$row_lang_code.' ] ';
                            }
                            echo '<br /><strong>Created: </strong>'.$row_created.'<br />';
                            echo '<strong>Last Modified: </strong>'.$row_modified;
                        ?>
                        </p>
                    </div> <!-- END METADATA -->

                </div>
            </div>
        </section>
        <!-- Related items section-->
        <section class="py-5 bg-light">
            <div class="container px-4 px-lg-5 mt-5">
                <h2 class="fw-bolder mb-4">Similar items</h2>
                <div class="row gx-4 gx-lg-5 row-cols-2 row-cols-md-3 row-cols-xl-4 justify-content-center"> <!-- Begin Similar Items display area -->
                    <?php
                    while ($row = $getsimilar->fetchArray()) {
                        $row_simbookid = $row['id'];
                        $row_simtitle = $row['title'];
                        $row_simauthorsort = $row['author_sort'];
                    echo '<div class="col mb-5"> <!-- Begin Similar Items card -->';
                        echo '<div class="card h-100">';
                        echo '<!-- Item image-->';
                            echo '<a href="itemrecord.php?itemid='.$row_simbookid.'"><img class="card-img-top" src="images/'.$row_simbookid.'.jpg" /></a>';
                            echo '<!-- Product details-->';
                            echo '<div class="card-body p-4">';
                                echo '<div class="text-center">';
                                    echo '<!-- Item name-->';
                                    echo '<h5 class="fw-bolder">'.$row_simtitle.'</h5>';
                                    echo '<!-- Item creator-->';
                                    echo $row_simauthorsort;
                                echo '</div>';
                            echo '</div>';
                            echo '<!-- Item actions-->';
                            echo '<div class="card-footer p-4 pt-0 border-top-0 bg-transparent">';
                                echo '<div class="text-center"><a class="btn btn-outline-dark mt-auto" href="itemrecord.php?itemid='.$row_simbookid.'">View Item</a></div>';
                            echo '</div>';
                        echo '</div>';
                    
                    
                    echo '</div> <!-- End Similar Items card -->';
                    }
                    ?>
                </div> <!-- End Similar Items display area -->
            </div>
        </section>
        <!-- Footer-->
        <footer class="py-5 bg-dark">
            <div class="container"><p class="m-0 text-center text-white">Copyright - Creative Commons By-NC-SA - Infopump</p></div>
        </footer>
        <!-- Bootstrap core JS-->
        <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/js/bootstrap.bundle.min.js"></script>
        <!-- Core theme JS-->
        <script src="item/js/scripts.js"></script>
    </body>
</html>