const express = require('express');
const router = express.Router();
const pool = require("../db");
require("dotenv").config();


const BASE_URL = process.env.BASE_URL;

router.get("/skills", async (req, res) => {
    try {
        // Queries
        const techQuery = `
            SELECT 
                t.id,
                t.name,
                t.logo,
                MAX(spr.level) AS level,
                MAX(spr.description) AS description
            FROM 
                tech t
            JOIN 
                tech_skill_proficiency tsp ON t.id = tsp.tech_id
            JOIN 
                skill_proficiency_ranking spr ON tsp.skill_proficiency_id = spr.id
            GROUP BY 
                t.id, t.name, t.logo
            ORDER BY 
                t.name;
        `;

        const toolQuery = `
            SELECT 
                tl.id,
                tl.name,
                tl.type,
                tl.logo,
                MAX(spr.level) AS level,
                MAX(spr.description) AS description
            FROM 
                tools tl
            JOIN 
                tools_skill_proficiency tsp ON tl.id = tsp.tool_id
            JOIN 
                skill_proficiency_ranking spr ON tsp.skill_proficiency_id = spr.id
            GROUP BY 
                tl.id, tl.name, tl.type, tl.logo
            ORDER BY 
                tl.name;
        `;

        const languagesQuery = `
            SELECT 
                l.id,
                l.name,
                l.logo,
                MAX(spr.level) AS level,
                MAX(spr.description) AS description
            FROM 
                languages l
            JOIN 
                languages_skill_proficiency lsp ON l.id = lsp.language_id
            JOIN 
                skill_proficiency_ranking spr ON lsp.skill_proficiency_id = spr.id
            GROUP BY 
                l.id, l.name, l.logo
            ORDER BY 
                l.name;
        `;


        const databasesQuery = `
        SELECT 
            d.id,
            d.name,
            d.logo,
            MAX(spr.level) AS level,
            MAX(spr.description) AS description
        FROM 
            \`databases\` d
        JOIN
            databases_skill_proficiency dsp ON d.id = dsp.database_id
        JOIN 
            skill_proficiency_ranking spr ON dsp.skill_proficiency_id = spr.id
        GROUP BY 
            d.id, d.name, d.logo
        ORDER BY 
            d.name;
    `;
    

        const experienceQuery = `
            SELECT 
                e.id,
                e.title AS name,
                e.org,
                e.start_date,
                e.end_date,
                e.description,
                e.context,
                e.logo,
                GROUP_CONCAT(DISTINCT t.name ORDER BY t.name SEPARATOR ', ') AS techs,
                GROUP_CONCAT(DISTINCT tl.name ORDER BY tl.name SEPARATOR ', ') AS tools,
                GROUP_CONCAT(DISTINCT l.name ORDER BY l.name SEPARATOR ', ') AS languages
            FROM 
                experience e
                LEFT JOIN experience_tech et ON e.id = et.experience_id
                LEFT JOIN tech t ON et.tech_id = t.id
                LEFT JOIN experience_tools etl ON e.id = etl.experience_id
                LEFT JOIN tools tl ON etl.tool_id = tl.id
                LEFT JOIN experience_languages el ON e.id = el.experience_id
                LEFT JOIN languages l ON el.language_id = l.id
            GROUP BY 
                e.id, e.title, e.context;
        `;

        const logosQuery = `SELECT * FROM logos WHERE type IN ('Academic', 'Professional');`;

        // Perform queries
        const techsRaw = await pool.query(techQuery);
        const toolsRaw = await pool.query(toolQuery);
        const languagesRaw = await pool.query(languagesQuery);
        const databasesRaw = await pool.query(databasesQuery);
        const experienceRaw = await pool.query(experienceQuery);
        const logosRaw = await pool.query(logosQuery);

        // Map logos to include the URL
        const updatedLogos = logosRaw.map(l => ({
            [l.type.toLowerCase()]: l.logo_path ? BASE_URL + l.logo_path : null
        }));

        // Map techs, tools, and languages to include the URL for logos and format the skill level
        const updatedTechs = techsRaw.map(tech => ({
            id: tech.id,
            name: tech.name,
            logo: tech.logo ? BASE_URL + tech.logo : null,
            skill: `${tech.description}: ${tech.level}/5`
        }));

        const updatedTools = toolsRaw.map(tool => ({
            id: tool.id,
            name: tool.name,
            type: tool.type,
            logo: tool.logo ? BASE_URL + tool.logo : null,
            skill: `${tool.description}: ${tool.level}/5`
        }));

        const updatedLanguages = languagesRaw.map(language => ({
            id: language.id,
            name: language.name,
            logo: language.logo ? BASE_URL + language.logo : null,
            skill: `${language.description}: ${language.level}/5`
        }));

        const updatedDatabases = databasesRaw.map(database => ({
            id: database.id,
            name: database.name,
            logo: database.logo ? BASE_URL + database.logo : null,
            skill: `${database.description}: ${database.level}/5`
        }));

        // Group techs, tools, and languages into a single skills object
        const skills = {
            techs: updatedTechs,
            tools: updatedTools,
            languages: updatedLanguages,
            databases: updatedDatabases
        };

        // Format experience data
        const academicExperience = [];
        const professionalExperience = [];

        experienceRaw.forEach(exp => {
            const formattedExp = {
                id: exp.id,
                name: exp.name,
                org: exp.org,
                start_date: exp.start_date,
                end_date: exp.end_date,
                description: exp.description ? exp.description.replace(/\\n/g, "<br>") : "",
                context: exp.context,
                skills: {
                    techs: exp.techs ? exp.techs.split(', ') : [],
                    tools: exp.tools ? exp.tools.split(', ') : [],
                    languages: exp.languages ? exp.languages.split(', ') : []
                },
                logo: exp.logo ? BASE_URL + exp.logo : null
            };

            if (exp.context === "Academic") {
                academicExperience.push(formattedExp);
            } else if (exp.context === "Professional") {
                professionalExperience.push(formattedExp);
            }
        });

        //console.log("My SKILLS: ", skills);

        // Send response
        res.json({
            skills,
            experience: {
                academic: academicExperience,
                professional: professionalExperience
            },
            logos: updatedLogos
        });

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

module.exports = router;