/* Detect the available blog table and column names. */
$tables = [];
$tableResult = $conn->query("SHOW TABLES");

if ($tableResult) {
    while ($row = $tableResult->fetch_array(MYSQLI_NUM)) {
        $tables[] = $row[0];
    }
}

$blogTable = null;

foreach (["blogs", "blog", "posts", "articles"] as $candidate) {
    if (in_array($candidate, $tables, true)) {
        $blogTable = $candidate;
        break;
    }
}

$columns = [];

if ($blogTable) {
    $columnResult = $conn->query("SHOW COLUMNS FROM `" . $blogTable . "`");

    if ($columnResult) {
        while ($column = $columnResult->fetch_assoc()) {
            $columns[] = strtolower($column["Field"]);
        }
    }
}

$idCol = findColumn($columns, ["id", "blog_id", "post_id"]);
$titleCol = findColumn($columns, ["title", "blog_title", "post_title", "name"]);
$slugCol = findColumn($columns, ["slug", "post_slug", "blog_slug"]);
$imageCol = findColumn($columns, ["featured_image", "image", "thumbnail", "image_url", "cover_image"]);
$excerptCol = findColumn($columns, ["excerpt", "short_description", "summary", "description"]);
$contentCol = findColumn($columns, ["content", "body", "post_content", "blog_content"]);
$categoryCol = findColumn($columns, ["category", "category_name", "blog_category"]);
$statusCol = findColumn($columns, ["status", "post_status", "blog_status"]);
$dateCol = findColumn($columns, ["published_at", "created_at", "publish_date", "date", "updated_at"]);

$blogs = [];
$categories = [];
$totalBlogs = 0;
$pageSize = 9;

$page = isset($_GET["page"]) ? max(1, (int)$_GET["page"]) : 1;
$search = trim($_GET["q"] ?? "");
$selectedCategory = trim($_GET["category"] ?? "");

if ($blogTable && $titleCol && $idCol) {
    $selectColumns = [
        "`$idCol` AS blog_id",
        "`$titleCol` AS blog_title"
    ];

    $selectColumns[] = $slugCol ? "`$slugCol` AS blog_slug" : "'' AS blog_slug";
    $selectColumns[] = $imageCol ? "`$imageCol` AS blog_image" : "'' AS blog_image";
    $selectColumns[] = $excerptCol ? "`$excerptCol` AS blog_excerpt" : "'' AS blog_excerpt";
    $selectColumns[] = $contentCol ? "`$contentCol` AS blog_content" : "'' AS blog_content";
    $selectColumns[] = $categoryCol ? "`$categoryCol` AS blog_category" : "'' AS blog_category";
    $selectColumns[] = $dateCol ? "`$dateCol` AS blog_date" : "NULL AS blog_date";

    $from = " FROM `$blogTable` ";
    $whereParts = [];
    $params = [];
    $types = "";

    if ($statusCol) {
        $whereParts[] = "LOWER(`$statusCol`) IN ('published','active','1')";
    }

    if ($search !== "") {
        $searchParts = ["`$titleCol` LIKE ?"];
        $params[] = "%" . $search . "%";
        $types .= "s";

        if ($excerptCol) {
            $searchParts[] = "`$excerptCol` LIKE ?";
            $params[] = "%" . $search . "%";
            $types .= "s";
        }

        if ($contentCol) {
            $searchParts[] = "`$contentCol` LIKE ?";
            $params[] = "%" . $search . "%";
            $types .= "s";
        }

        $whereParts[] = "(" . implode(" OR ", $searchParts) . ")";
    }

    if ($selectedCategory !== "" && $categoryCol) {
        $whereParts[] = "`$categoryCol` = ?";
        $params[] = $selectedCategory;
        $types .= "s";
    }

    $whereSql = count($whereParts) ? " WHERE " . implode(" AND ", $whereParts) : "";

    if ($categoryCol) {
        $categorySql = "SELECT DISTINCT `$categoryCol` AS category_name"
            . $from
            . ($statusCol ? " WHERE LOWER(`$statusCol`) IN ('published','active','1')" : "")
            . " AND `$categoryCol` <> ''";

        // Build a valid category query with or without a status column.
        $categorySql = "SELECT DISTINCT `$categoryCol` AS category_name FROM `$blogTable`"
            . ($statusCol
                ? " WHERE LOWER(`$statusCol`) IN ('published','active','1') AND `$categoryCol` <> ''"
                : " WHERE `$categoryCol` <> ''")
            . " ORDER BY `$categoryCol` ASC";

        $categoryResult = $conn->query($categorySql);

        if ($categoryResult) {
            while ($cat = $categoryResult->fetch_assoc()) {
                if (!empty($cat["category_name"])) {
                    $categories[] = $cat["category_name"];
                }
            }
        }
    }

    $countSql = "SELECT COUNT(*) AS total " . $from . $whereSql;
    $countStmt = $conn->prepare($countSql);

    if ($countStmt) {
        $countParams = $params;
        bindDynamic($countStmt, $types, $countParams);
        $countStmt->execute();
        $countResult = $countStmt->get_result();

        if ($countResult) {
            $countRow = $countResult->fetch_assoc();
            $totalBlogs = (int)($countRow["total"] ?? 0);
        }

        $countStmt->close();
    }

    $totalPages = max(1, (int)ceil($totalBlogs / $pageSize));
    $page = min($page, $totalPages);
    $offset = ($page - 1) * $pageSize;

    $orderBy = $dateCol ? "`$dateCol` DESC" : "`$idCol` DESC";

    $sql = "SELECT " . implode(", ", $selectColumns)
        . $from . $whereSql
        . " ORDER BY $orderBy LIMIT ? OFFSET ?";

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

    if ($stmt) {
        $queryParams = $params;
        $queryParams[] = $pageSize;
        $queryParams[] = $offset;

        $queryTypes = $types . "ii";
        bindDynamic($stmt, $queryTypes, $queryParams);

        if ($stmt->execute()) {
            $result = $stmt->get_result();

            while ($row = $result->fetch_assoc()) {
                $blogs[] = $row;
            }
        }

        $stmt->close();
    }
}

$totalPages = max(1, (int)ceil($totalBlogs / $pageSize));

function blogUrl($blog) {
    if (!empty($blog["blog_slug"])) {
        return "blog-detail.php?slug=" . rawurlencode($blog["blog_slug"]);
    }

    return "blog-detail.php?id=" . (int)$blog["blog_id"];
}

function paginationUrl($pageNumber) {
    $query = $_GET;
    $query["page"] = $pageNumber;
    return "blog.php?" . http_build_query($query);
}

$siteLogo = "https://redrecommend.com/uploads/Redrecommend_Cart-R_Logo-removebg-preview.png";
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">

    <title>Blog | RedRecommend</title>
    <meta name="description" content="Explore product recommendations, shopping guides, reviews, comparisons and useful tips on the RedRecommend blog.">
    <meta name="robots" content="index,follow">
    <link rel="canonical" href="https://redrecommend.com/blog.php">

    <meta property="og:title" content="RedRecommend Blog">
    <meta property="og:description" content="Explore useful shopping guides, product recommendations and reviews.">
    <meta property="og:type" content="website">
    <meta property="og:url" content="https://redrecommend.com/blog.php">
    <meta property="og:image" content="