const express = require('express');
const router = express.Router();
const pool = require("../db");
const fetch = require('node-fetch');
require("dotenv").config();
const BASE_URL = process.env.BASE_URL;
const GIT_API_URL = process.env.GIT_API_URL;
const REPOSITORY_BASE_URL = process.env.REPOSITORY_BASE_URL;
// Helper function to append BASE_URL to a path
const appendBaseUrl = (path) => (path ? `${BASE_URL}${path}` : null);
// Fetch all projects
router.get("/projects", async (req, res) => {
try {
const categoriesAndTagsQuery = `SELECT
c.id AS category_id,
c.name AS category_name,
c.logo AS category_logo,
GROUP_CONCAT(t.name) AS tags
FROM
categories c
LEFT JOIN
tags t ON c.id = t.category_id
GROUP BY
c.id;`;
const projectsQuery = ` SELECT
p.id,
p.title,
p.hook,
c.name AS category,
p.thumbnail_path AS thumbnail,
-- Fetch tags as a JSON array
(SELECT JSON_ARRAYAGG(t.name)
FROM project_tags pt
INNER JOIN tags t ON pt.tag_id = t.id
WHERE pt.project_id = p.id) AS tags,
-- Fetch tools as a JSON array
(SELECT JSON_ARRAYAGG(t1.name)
FROM project_tools pt
INNER JOIN tools t1 ON pt.tool_id = t1.id
WHERE pt.project_id = p.id) AS tools,
-- Fetch tech as a JSON array
(SELECT JSON_ARRAYAGG(t2.name)
FROM project_tech pt2
INNER JOIN tech t2 ON pt2.tech_id = t2.id
WHERE pt2.project_id = p.id) AS tech,
-- Fetch programming languages as a JSON array
(SELECT JSON_ARRAYAGG(t3.name)
FROM project_languages pt3
INNER JOIN languages t3 ON pt3.language_id = t3.id
WHERE pt3.project_id = p.id) AS programming_languages,
-- Fetch tech logos as a JSON array
(SELECT JSON_ARRAYAGG(t2.logo)
FROM project_tech pt2
INNER JOIN tech t2 ON pt2.tech_id = t2.id
WHERE pt2.project_id = p.id) AS tech_logo,
-- Fetch programming language logos as a JSON array
(SELECT JSON_ARRAYAGG(t3.logo)
FROM project_languages pt3
INNER JOIN languages t3 ON pt3.language_id = t3.id
WHERE pt3.project_id = p.id) AS programming_languages_logo
FROM
projects p
LEFT JOIN
categories c ON p.category_id = c.id
GROUP BY
p.id, p.title, p.hook, p.thumbnail_path, c.name;`;
//Queries results
const projectsRaw = await pool.query(projectsQuery);
const categoriesAndTagsRaw = await pool.query(categoriesAndTagsQuery);
const categories = categoriesAndTagsRaw.map(category => ({
...category,
category_logo: category.category_logo ? `${BASE_URL}${category.category_logo}` : null,
tags: category.tags ? category.tags.split(',') : []
}
));
const projects = projectsRaw.map(project => ({
...project,
image_url: `${BASE_URL}${project.thumbnail}`, // Construct full URL for thumbnail
logos: [
...(project.tech_logo ? project.tech_logo.map(logo => `${BASE_URL}${logo}`) : []),
...(project.programming_languages_logo ? project.programming_languages_logo.map(logo => `${BASE_URL}${logo}`) : [])
] // Merge tech_logo and programming_languages_logo into a single array
}));
// Log the query results
//console.log("Query result with image URLs:", projectsWithImageURLs);
// Check if rows are returned
if (!projectsRaw || projectsRaw.length === 0) {
return res.status(404).send("No projects found.");
}
//console.log("Work result: ", {categories, projects});
res.json({categories, projects});
} catch (err) {
console.error("Error fetching projects:", err.message);
res.status(500).send("Server error");
}
});
router.get("/project_page/:project_id", async (req, res) => {
const { project_id } = req.params;
try {
// Query to fetch project context
const contextQuery = `
SELECT type AS project_context
FROM projects p
WHERE p.id = ?;
`;
const contextResult = await pool.query(contextQuery, [project_id]);
if (!contextResult || contextResult.length === 0) {
return res.status(404).send("Project not found");
}
const projectContext = contextResult[0].project_context;
// Determine the logo type based on the project context
let contextLogoType;
switch (projectContext) {
case "Personal":
contextLogoType = "Personal";
break;
case "Academic":
contextLogoType = "Academic";
break;
default:
contextLogoType = "Professional";
}
// Query to fetch the logo path
const logoQuery = `
SELECT l.logo_path AS context_logo
FROM logos l
WHERE l.type = ?;
`;
const logoResult = await pool.query(logoQuery, [contextLogoType]);
if (!logoResult || logoResult.length === 0) {
return res.status(404).send("Logo not found");
}
const logoPath = logoResult[0].context_logo;
// Construct the project context object
const projectContextData = {
project_context: projectContext,
context_logo: appendBaseUrl(logoPath),
};
// Query to fetch Git repository
const projectQuery = `
SELECT git_rep AS git_repository
FROM projects
WHERE id = ?;
`;
const projectDateQuery = `
SELECT
CONCAT(MONTHNAME(p.start_date), ' ', YEAR(p.start_date)) AS start_month_year,
CONCAT(MONTHNAME(p.end_date), ' ', YEAR(p.end_date)) AS end_month_year,
l.logo_path AS date_logo
FROM projects p
LEFT JOIN logos l ON l.type = 'date'
WHERE p.id = ?;
`;
const [projectResult, projectExtraInfoResult] = await Promise.all([
pool.query(projectQuery, [project_id]),
pool.query(projectDateQuery, [project_id]),
]);
if (!projectResult || projectResult.length === 0) {
return res.status(404).send("Project not found.");
}
const gitRepository = projectResult[0].git_repository;
let repository = {
url: null,
logo: null,
commits: null,
};
if (gitRepository) {
const repositoryapiUrl = `${GIT_API_URL}${gitRepository}/commits?per_page=5`;
const repositoryUrl = `${REPOSITORY_BASE_URL}${gitRepository}`;
const response = await fetch(repositoryapiUrl);
const commits = await response.json();
const logoQueryGit = "SELECT logo FROM tools WHERE name = 'GitBucket'";
const logoResultGit = await pool.query(logoQueryGit);
const logoUrl = logoResultGit[0]?.logo || '';
const fullLogoUrl = appendBaseUrl(logoUrl);
const commitsData = commits.map(commit => ({
sha: commit.sha,
author: commit.commit.author.name,
message: commit.commit.message,
date: commit.commit.author.date,
url: commit.html_url,
logo: fullLogoUrl,
}));
repository = {
url: repositoryUrl,
logo: fullLogoUrl,
commits: commitsData,
};
}
// Query to fetch distinct tool types
const toolTypesQuery = `
SELECT DISTINCT t.type
FROM tools t
INNER JOIN project_tools pt ON pt.tool_id = t.id
WHERE pt.project_id = ?;
`;
const toolTypesRows = await pool.query(toolTypesQuery, [project_id]);
if (!toolTypesRows || toolTypesRows.length === 0) {
return res.status(404).send("No tool types found.");
}
const toolTypes = toolTypesRows.map(row => row.type);
// Dynamically generate tool subqueries
const toolSubqueries = toolTypes.map(type => `
(SELECT JSON_ARRAYAGG(
JSON_OBJECT('name', t.name, 'logo', t.logo, 'type', t.type)
)
FROM project_tools pt
INNER JOIN tools t ON pt.tool_id = t.id
WHERE pt.project_id = p.id AND t.type = '${type}') AS ${type.toLowerCase()}_tools
`).join(',\n');
// Main query to fetch project page details
const query = `
SELECT
p.id AS project_id,
p.title AS project_title,
p.description AS project_description,
p.hook,
p.trailer,
p.thumbnail_path AS project_thumbnail,
${toolSubqueries},
(SELECT JSON_ARRAYAGG(JSON_OBJECT('name', t.name, 'logo', t.logo))
FROM project_tech pt
INNER JOIN tech t ON pt.tech_id = t.id
WHERE pt.project_id = p.id) AS tech,
(SELECT JSON_ARRAYAGG(JSON_OBJECT('name', t.name, 'logo', t.logo))
FROM project_languages pl
INNER JOIN languages t ON pl.language_id = t.id
WHERE pl.project_id = p.id) AS programming_languages,
pg.background_image AS background_path,
pg.project_logo AS logo
FROM
project_page pp
INNER JOIN
projects p ON pp.project_id = p.id
INNER JOIN
page pg ON pp.page_id = pg.id
WHERE
pp.project_id = ?;
`;
const rows = await pool.query(query, [project_id]);
if (!rows || rows.length === 0) {
return res.status(404).send("No page found for the given project ID.");
}
// Construct the final response
const pageData = rows.map(row => ({
...row,
background_image: appendBaseUrl(row.background_path),
project_logo: appendBaseUrl(row.logo),
project_thumbnail: appendBaseUrl(row.project_thumbnail),
repository,
tech: row.tech.map(item => ({
...item,
logo: appendBaseUrl(item.logo),
})),
programming_languages: row.programming_languages.map(item => ({
...item,
logo: appendBaseUrl(item.logo),
})),
tools: toolTypes.map(type => ({
type,
tools: (row[`${type.toLowerCase()}_tools`] || []).map(tool => ({
...tool,
logo: appendBaseUrl(tool.logo),
})),
})),
context: projectContextData, // Include the project context data
start_date: projectExtraInfoResult[0]?.start_month_year,
end_date: projectExtraInfoResult[0]?.end_month_year,
date_logo: appendBaseUrl(projectExtraInfoResult[0]?.date_logo),
}));
console.log(pageData);
res.json(pageData[0]);
} catch (err) {
console.error("Error fetching project page:", err.message);
res.status(500).send("Server error");
}
});
// Export the router
module.exports = router;
