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);

router.get("/my_projects", async (req, res) => {    
    try {

        //Query
        const projectsQuery = `SELECT * FROM projects;`;
        
        //Queries results
        const projects= await pool.query(projectsQuery);

        //Send response
        res.json(projects);

    } catch (err) {
    console.error("Error fetching project names: ", err.message);
    res.status(500).send("Server error");
    }
});

// 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), ' ', DAY(p.start_date), ', ', YEAR(p.start_date)) AS start_full_date,
                CONCAT(MONTHNAME(p.end_date), ' ', DAY(p.end_date), ', ', YEAR(p.end_date)) AS end_full_date,
                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,
            };
        }

        // 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,
            p.screenshots,
    
            -- Fetch tags as a JSON array
            (SELECT JSON_ARRAYAGG(JSON_OBJECT('name', t.name, 'logo', t.logo)) 
             FROM project_tags pt
             INNER JOIN tags t ON pt.tag_id = t.id 
             WHERE pt.project_id = p.id) AS tags,    
    
            -- Fetch tech as a JSON array
            (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,
    
            -- Fetch programming languages as a JSON array
            (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,

            -- Fetch development tools as a JSON array
            (SELECT JSON_ARRAYAGG(JSON_OBJECT('name', dt.name, 'logo', dt.logo)) 
             FROM project_dev_tools pdt
             INNER JOIN dev_tools dt ON pdt.dev_tool_id = dt.id 
             WHERE pdt.project_id = p.id) AS dev_tools,

            -- Fetch management tools as a JSON array
            (SELECT JSON_ARRAYAGG(JSON_OBJECT('name', mt.name, 'logo', mt.logo)) 
             FROM project_manage_tools pmt
             INNER JOIN manage_tools mt ON pmt.manage_tool_id = mt.id 
             WHERE pmt.project_id = p.id) AS management_tools,

            -- Fetch design tools as a JSON array
            (SELECT JSON_ARRAYAGG(JSON_OBJECT('name', dt.name, 'logo', dt.logo)) 
             FROM project_design_tools pdt
             INNER JOIN design_tools dt ON pdt.design_tool_id = dt.id 
             WHERE pdt.project_id = p.id) AS des_tools,
    
            -- Fetch databases as a JSON array
            (SELECT JSON_ARRAYAGG(JSON_OBJECT('name', d.name, 'logo', d.logo))
             FROM project_databases pd
             INNER JOIN \`databases\` d ON pd.database_id = d.id
             WHERE pd.project_id = p.id) AS project_databases,  -- Changed alias to avoid reserved keyword
    
            -- Fetch category as a JSON object
            JSON_OBJECT('name', c.name, 'logo', c.logo) AS category,
    
            -- Fetch project links as a JSON array
            (SELECT JSON_ARRAYAGG(JSON_OBJECT('name', l.name, 'url', l.url, 'logo', l.logo))
            FROM links l
            WHERE l.project_id = p.id) AS links,
    
            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
        LEFT JOIN 
            categories c ON p.category_id = c.id
        WHERE 
            pp.project_id = ?;
    `;

        const rows = await pool.query(query, [project_id]);

        const project_skills = rows.map(row => {
            let skills = {}; // Initialize as empty object
        
            if (row.tech && row.tech.length > 0) {
                skills.tech = row.tech.map(t => ({
                    skill_name: "Tech",
                    ...t,
                    logo: appendBaseUrl(t.logo),
                }));
            }
        
            if (row.programming_languages && row.programming_languages.length > 0) {
                skills.programming_languages = row.programming_languages.map(l => ({
                    skill_name: "Programming Languages",
                    ...l,
                    logo: appendBaseUrl(l.logo),
                }));
            }
        
            if (row.development_tools && row.development_tools.length > 0) {                
                skills.development_tools = row.development_tools.map(dev => ({
                    skill_name: "Development Tools",
                    ...dev,
                    logo: appendBaseUrl(dev.logo),
                }));
            }
        
            if (row.management_tools && row.management_tools.length > 0) {                
                skills.management_tools = row.management_tools.map(mgt => ({
                    skill_name: "Management Tools",
                    ...mgt,
                    logo: appendBaseUrl(mgt.logo),
                }));
            }
        
            if (row.des_tools && row.des_tools.length > 0) {                
                skills.design_tools = row.des_tools.map(dt => ({
                    skill_name: "Design Tools",
                    ...dt,
                    logo: appendBaseUrl(dt.logo),
                }));
            }
        
            if (row.project_databases && row.project_databases.length > 0) {                
                skills.project_databases = row.project_databases.map(db => ({
                    skill_name: "Databases",
                    ...db,
                    logo: appendBaseUrl(db.logo),
                }));
            }
        
            return skills; // Return object with all accumulated skills
        });
        

        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,

            category: row.category ? {
                ...row.category,
                logo: appendBaseUrl(row.category.logo),
            } : null,

            skills: project_skills,

            links: row.links ? row.links.map(link => ({
                ...link,
                logo: appendBaseUrl(link.logo),
            })) : null,

            tags: (row.tags || []).map(item => ({
                ...item,
                logo: appendBaseUrl(item.logo),
            })),
            background_image: appendBaseUrl(row.background_path),
            project_logo: appendBaseUrl(row.logo),
            project_thumbnail: appendBaseUrl(row.project_thumbnail),
            repository,
            context: projectContextData, // Include the project context data
            
            // Update date extraction using the new format
            start_date: projectExtraInfoResult[0]?.start_full_date, 
            end_date: projectExtraInfoResult[0]?.end_full_date || "Present", // Ensure "Present" if null
            date_logo: appendBaseUrl(projectExtraInfoResult[0]?.date_logo),

            // Split and concatenate screenshots with the base URL
            screenshots: row.screenshots 
            ? row.screenshots.split(",").map(path => `${BASE_URL}${path.trim()}`) 
            : [],
        }));

        //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;