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;