Untitled

@using WebMatrix.Data;

@{
    var db = Database.Open("AmazonOrders");

    // Get the item ID from the query string
    var itemId = Request.QueryString["id"];

    // Join the Items table with Categories and Reviews to get the Category name and review data for the item
    var sql = "SELECT Items.*, itemCategories.CategoryName, " +
              "COUNT(Reviews.itemID) as TotalReviews, AVG(Reviews.rating) as AverageRating " +
              "FROM Items " +
              "JOIN itemCategories ON Items.CategoryId = itemCategories.categoryID " +
              "LEFT JOIN Reviews ON Items.itemID = Reviews.itemID " +
              "WHERE Items.itemID = @0 " +
              "GROUP BY Items.itemID, itemCategories.CategoryName, Items.itemName, Items.itemDescription, Items.itemCost, Items.itemImage, Items.categoryID";

    var item = db.QuerySingle(sql, itemId);
}

<div class="container">
    <div class="row justify-content-center">
        <div class="col-10">
            <div class="text-center">
                <h2 style="background-color: #f5f5f5; padding: 10px; margin: 20px 0;">Product Detail</h2>
            </div>

            @if (item != null)
            {
                <div class="card">
                    <div class="card-body">
                        <h5 class="card-title">@item.itemName</h5>
                        <p class="card-text">@item.itemDescription</p>
                        <p><strong>Category:</strong> @item.CategoryName</p>
                        <p><strong>Total Reviews:</strong> @item.TotalReviews</p>
                        <p><strong>Average Rating:</strong> @(item.AverageRating != null ? Math.Round((double)item.AverageRating, 2).ToString() : "No reviews yet")</p>
                        <p class="font-italic">@item.itemCost.ToString("C")</p>
                        <img src="@item.itemImage" class="card-img-top" alt="@item.itemName image" />
                    </div>
                </div>
            }
            else
            {
                <h2 class="text-danger text-center">Ooops - That product was not found!</h2>
            }

        </div>
    </div>
</div>