const express = require("express");
const router = express.Router();
const db = require("../db");

// Helper function to generate unique log ID for project logs
const generateProjectLogId = async () => {
    try {
        const [result] = await db.execute(
            "SELECT MAX(log_id) as max_id FROM project_log"
        );
        const maxId = result[0].max_id || 0;
        return maxId + 1;
    } catch (error) {
        return parseInt(Date.now() + Math.floor(Math.random() * 1000));
    }
};

// Helper function to create project log entry
const createProjectLog = async (logData) => {
    try {
        // First, verify that the user exists
        const [userCheck] = await db.execute(
            "SELECT user_id FROM users WHERE user_id = ?",
            [logData.user_id]
        );

        if (userCheck.length === 0) {
            logData.user_id = 201;

            // Check if default user exists
            const [defaultUserCheck] = await db.execute(
                "SELECT user_id FROM users WHERE user_id = 201"
            );

            if (defaultUserCheck.length === 0) {
                throw new Error("No valid user found for logging");
            }
        }

        const [result] = await db.execute(
            "INSERT INTO project_log (log_id, user_id, project_name, type_name, status_name, invoice_status_name, priority_level_name, deadline, percent_completed, estimated_hours, actual_hours, remarks, action, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
            [
                logData.log_id,
                logData.user_id,
                logData.project_name,
                logData.type_name || null,
                logData.status_name || null,
                logData.invoice_status_name || null,
                logData.priority_level_name || null,
                logData.deadline || null,
                logData.percent_completed || null,
                logData.estimated_hours || null,
                logData.actual_hours || null,
                logData.remarks || null,
                logData.action,
                logData.created_at ||
                    new Date().toISOString().slice(0, 19).replace("T", " "),
            ]
        );

        return result;
    } catch (error) {
        throw error;
    }
};

router.post("/addEmployeeContribution", async (req, res) => {
    try {
        const { employee_id, project_id, contribution_percentage, contribution_description, user_id } = req.body;
        
        // Validate required fields
        if (!employee_id || !project_id || contribution_percentage === undefined || contribution_percentage === null) {
            return res.status(400).json({ error: "employee_id, project_id, and contribution_percentage are required" });
        }

        // Validate employee_id exists and get employee name
        const [employeeCheck] = await db.execute(
            "SELECT employee_id, employee_name FROM employees WHERE employee_id = ?",
            [employee_id]
        );
        if (employeeCheck.length === 0) {
            return res.status(404).json({ error: `Employee with ID ${employee_id} does not exist` });
        }
        const employeeName = employeeCheck[0].employee_name;

        // Validate project_id exists and get project name and details with JOINs
        const [projectCheck] = await db.execute(
            `SELECT 
                p.project_id, 
                p.project_name, 
                pt.type_name, 
                ps.status_name, 
                ins.invoice_status_name, 
                ppl.priority_level_name, 
                p.deadline, 
                p.percent_completed, 
                p.estimated_hours, 
                p.actual_hours, 
                p.remarks 
            FROM projects p
            LEFT JOIN project_type pt ON p.type_id = pt.type_id
            LEFT JOIN project_status ps ON p.status_id = ps.status_id
            LEFT JOIN invoice_status ins ON p.invoice_status_id = ins.invoice_status_id
            LEFT JOIN project_priority_level ppl ON p.priority_level_id = ppl.priority_level_id
            WHERE p.project_id = ?`,
            [project_id]
        );
        if (projectCheck.length === 0) {
            return res.status(404).json({ error: `Project with ID ${project_id} does not exist` });
        }
        const project = projectCheck[0];

        // Validate contribution_percentage is within valid range (0-100)
        if (contribution_percentage < 0 || contribution_percentage > 100) {
            return res.status(400).json({ error: "contribution_percentage must be between 0 and 100" });
        }

        const query = `INSERT INTO employee_contributions (employee_id, project_id, contribution_percentage, contribution_description) VALUES (?, ?, ?, ?)`;
        const [result] = await db.execute(query, [employee_id, project_id, contribution_percentage, contribution_description || null]);
        
        // Create project log entry
        try {
            const logId = await generateProjectLogId();
            const actionMessage = `Employee contribution added: ${employeeName} - ${contribution_percentage}%`;
            
            await createProjectLog({
                log_id: logId,
                user_id: user_id || 201,
                project_name: project.project_name,
                type_name: project.type_name,
                status_name: project.status_name,
                invoice_status_name: project.invoice_status_name,
                priority_level_name: project.priority_level_name,
                deadline: project.deadline,
                percent_completed: project.percent_completed,
                estimated_hours: project.estimated_hours,
                actual_hours: project.actual_hours,
                remarks: contribution_description || `Contribution: ${employeeName} - ${contribution_percentage}%`,
                action: actionMessage,
            });
        } catch (logError) {
            console.error("Error creating project log:", logError);
            // Don't fail the request if logging fails, just log the error
        }
        
        res.status(201).json({ 
            message: "Employee contribution added successfully",
            contribution_id: result.insertId
        });
    } catch (err) {
        // Handle foreign key constraint errors with a clearer message
        if (err.code === 'ER_NO_REFERENCED_ROW_2' || err.message.includes('foreign key constraint')) {
            return res.status(400).json({ 
                error: "Invalid employee_id or project_id. Please ensure both exist in the database." 
            });
        }
        res.status(500).json({ error: err.message });
    }
});



// GET employee contributions - supports querying by employee_id and/or project_id
router.get("/getEmployeeContribution", async (req, res) => {
    try {
        const { employee_id, project_id } = req.query;
        
        // Build query dynamically based on provided parameters
        let query = 'SELECT * FROM employee_contributions WHERE 1=1';
        const params = [];
        
        if (employee_id) {
            query += ' AND employee_id = ?';
            params.push(employee_id);
        }
        
        if (project_id) {
            query += ' AND project_id = ?';
            params.push(project_id);
        }
        
        // If neither parameter is provided, return error
        if (!employee_id && !project_id) {
            return res.status(400).json({ 
                error: "At least one of employee_id or project_id must be provided" 
            });
        }
        
        query += ' ORDER BY date_logged DESC';
        
        const [results] = await db.execute(query, params);
        
        if (results.length === 0) {
            return res.status(404).json({ 
                message: "No contributions found",
                data: [] 
            });
        }
        
        res.status(200).json({
            count: results.length,
            data: results
        });
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});

// UPDATE employee contribution
router.put("/updateEmployeeContribution", async (req, res) => {
    try {
        const { employee_id, project_id, contribution_percentage, contribution_description, user_id } = req.body;
        
        // Validate required fields
        if (!employee_id || !project_id || contribution_percentage === undefined || contribution_percentage === null) {
            return res.status(400).json({ error: "employee_id, project_id, and contribution_percentage are required" });
        }

        // Validate contribution_percentage is within valid range (0-100)
        if (contribution_percentage < 0 || contribution_percentage > 100) {
            return res.status(400).json({ error: "contribution_percentage must be between 0 and 100" });
        }

        // Check if contribution exists and get old percentage
        const [existing] = await db.execute(
            "SELECT * FROM employee_contributions WHERE employee_id = ? AND project_id = ?",
            [employee_id, project_id]
        );

        if (existing.length === 0) {
            return res.status(404).json({ error: "Contribution not found. Use addEmployeeContribution to create a new contribution." });
        }

        const oldPercentage = existing[0].contribution_percentage;

        // Get employee name
        const [employeeCheck] = await db.execute(
            "SELECT employee_name FROM employees WHERE employee_id = ?",
            [employee_id]
        );
        const employeeName = employeeCheck.length > 0 ? employeeCheck[0].employee_name : `Employee ID ${employee_id}`;

        // Get project details with JOINs
        const [projectCheck] = await db.execute(
            `SELECT 
                p.project_id, 
                p.project_name, 
                pt.type_name, 
                ps.status_name, 
                ins.invoice_status_name, 
                ppl.priority_level_name, 
                p.deadline, 
                p.percent_completed, 
                p.estimated_hours, 
                p.actual_hours, 
                p.remarks 
            FROM projects p
            LEFT JOIN project_type pt ON p.type_id = pt.type_id
            LEFT JOIN project_status ps ON p.status_id = ps.status_id
            LEFT JOIN invoice_status ins ON p.invoice_status_id = ins.invoice_status_id
            LEFT JOIN project_priority_level ppl ON p.priority_level_id = ppl.priority_level_id
            WHERE p.project_id = ?`,
            [project_id]
        );
        const project = projectCheck.length > 0 ? projectCheck[0] : null;

        // Update the contribution
        const query = `UPDATE employee_contributions 
                      SET contribution_percentage = ?, contribution_description = ?, date_logged = CURRENT_TIMESTAMP 
                      WHERE employee_id = ? AND project_id = ?`;
        const [result] = await db.execute(query, [
            contribution_percentage,
            contribution_description || null,
            employee_id,
            project_id
        ]);
        
        if (result.affectedRows === 0) {
            return res.status(404).json({ error: "Contribution not found or no changes made" });
        }
        
        // Create project log entry only if project exists
        if (project) {
            try {
                const logId = await generateProjectLogId();
                const actionMessage = `Employee contribution updated: ${employeeName} - ${oldPercentage}% → ${contribution_percentage}%`;
                
                await createProjectLog({
                    log_id: logId,
                    user_id: user_id || 201,
                    project_name: project.project_name,
                    type_name: project.type_name,
                    status_name: project.status_name,
                    invoice_status_name: project.invoice_status_name,
                    priority_level_name: project.priority_level_name,
                    deadline: project.deadline,
                    percent_completed: project.percent_completed,
                    estimated_hours: project.estimated_hours,
                    actual_hours: project.actual_hours,
                    remarks: contribution_description || `Contribution updated: ${employeeName} - ${oldPercentage}% → ${contribution_percentage}%`,
                    action: actionMessage,
                });
            } catch (logError) {
                console.error("Error creating project log:", logError);
                // Don't fail the request if logging fails, just log the error
            }
        }
        
        res.status(200).json({ 
            message: "Employee contribution updated successfully",
            contribution_id: existing[0].contribution_id
        });
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});

module.exports = router;