// PDF Report Generator - Team Progress & Compliance Reports
const db = require('../db');
const PDFDocument = require('pdfkit');

// ============================================
// GENERATE TEAM PROGRESS REPORT
// ============================================
const generateTeamReport = async (req, res) => {
    try {
        const { orgId } = req.params;
        const userId = Number(req.user.id);
        
        // Verify admin access
        const membership = await db.query(
            'SELECT role FROM organization_members WHERE organization_id = ? AND user_id = ? AND role IN ("owner", "admin")',
            [orgId, userId]
        );
        
        if (membership.rows.length === 0) {
            return res.status(403).json({ message: 'Admin access required' });
        }
        
        // Get organization info
        const org = await db.query('SELECT * FROM organizations WHERE id = ?', [orgId]);
        if (org.rows.length === 0) {
            return res.status(404).json({ message: 'Organization not found' });
        }
        
        // Get all members with stats
        const members = await db.query(
            `SELECT 
                u.id, u.name, u.email,
                om.role, om.joined_at,
                COALESCE(ux.total_xp, 0) as total_xp,
                COALESCE(ux.current_level, 1) as level,
                (SELECT COUNT(*) FROM investigations WHERE user_id = u.id AND status = 'graded') as investigations_completed,
                (SELECT AVG(grade) FROM investigations WHERE user_id = u.id AND status = 'graded') as avg_score,
                (SELECT COUNT(*) FROM ctf_progress WHERE user_id = u.id) as ctf_completed
             FROM organization_members om
             JOIN users u ON om.user_id = u.id
             LEFT JOIN user_xp ux ON u.id = ux.user_id
             WHERE om.organization_id = ?
             ORDER BY ux.total_xp DESC`,
            [orgId]
        );
        
        // Get learning path progress
        const pathProgress = await db.query(
            `SELECT 
                lp.title,
                COUNT(DISTINCT upp.user_id) as enrolled,
                AVG(upp.percentage_completed) as avg_completion
             FROM user_path_progress upp
             JOIN learning_paths lp ON upp.learning_path_id = lp.id
             JOIN organization_members om ON upp.user_id = om.user_id
             WHERE om.organization_id = ?
             GROUP BY lp.id, lp.title`,
            [orgId]
        );
        
        // Calculate team totals
        const teamStats = {
            totalMembers: members.rows.length,
            totalXP: members.rows.reduce((sum, m) => sum + (m.total_xp || 0), 0),
            avgXP: Math.round(members.rows.reduce((sum, m) => sum + (m.total_xp || 0), 0) / members.rows.length) || 0,
            totalInvestigations: members.rows.reduce((sum, m) => sum + (m.investigations_completed || 0), 0),
            avgScore: Math.round(members.rows.reduce((sum, m) => sum + (m.avg_score || 0), 0) / members.rows.length) || 0,
            totalCTF: members.rows.reduce((sum, m) => sum + (m.ctf_completed || 0), 0)
        };
        
        // Create PDF
        const doc = new PDFDocument({ margin: 50 });
        
        res.setHeader('Content-Type', 'application/pdf');
        res.setHeader('Content-Disposition', `attachment; filename="${org.rows[0].slug}-team-report.pdf"`);
        
        doc.pipe(res);
        
        // Header
        doc.fontSize(24).fillColor('#7c3aed').text('Team Progress Report', { align: 'center' });
        doc.moveDown();
        doc.fontSize(16).fillColor('#333').text(org.rows[0].name, { align: 'center' });
        doc.fontSize(10).fillColor('#666').text(`Generated: ${new Date().toLocaleDateString()}`, { align: 'center' });
        doc.moveDown(2);
        
        // Team Overview
        doc.fontSize(14).fillColor('#7c3aed').text('Team Overview');
        doc.moveDown(0.5);
        doc.fontSize(10).fillColor('#333');
        doc.text(`Total Members: ${teamStats.totalMembers}`);
        doc.text(`Total Team XP: ${teamStats.totalXP.toLocaleString()}`);
        doc.text(`Average XP per Member: ${teamStats.avgXP.toLocaleString()}`);
        doc.text(`Total Investigations Completed: ${teamStats.totalInvestigations}`);
        doc.text(`Average Investigation Score: ${teamStats.avgScore}%`);
        doc.text(`Total CTF Challenges Completed: ${teamStats.totalCTF}`);
        doc.moveDown(2);
        
        // Member Details Table
        doc.fontSize(14).fillColor('#7c3aed').text('Member Details');
        doc.moveDown(0.5);
        
        // Table header
        const tableTop = doc.y;
        const colWidths = [120, 60, 60, 80, 60, 60];
        doc.fontSize(8).fillColor('#666');
        doc.text('Name', 50, tableTop);
        doc.text('Role', 170, tableTop);
        doc.text('Level', 230, tableTop);
        doc.text('XP', 290, tableTop);
        doc.text('Investigations', 370, tableTop);
        doc.text('Avg Score', 450, tableTop);
        
        doc.moveTo(50, tableTop + 12).lineTo(520, tableTop + 12).stroke('#ddd');
        
        // Table rows
        let rowY = tableTop + 20;
        doc.fillColor('#333');
        
        for (const member of members.rows) {
            if (rowY > 700) {
                doc.addPage();
                rowY = 50;
            }
            
            doc.text(member.name || 'Unknown', 50, rowY, { width: 115 });
            doc.text(member.role, 170, rowY);
            doc.text(String(member.level), 230, rowY);
            doc.text(member.total_xp?.toLocaleString() || '0', 290, rowY);
            doc.text(String(member.investigations_completed || 0), 370, rowY);
            doc.text(`${Math.round(member.avg_score || 0)}%`, 450, rowY);
            
            rowY += 18;
        }
        
        // Learning Paths Section
        if (pathProgress.rows.length > 0) {
            doc.addPage();
            doc.fontSize(14).fillColor('#7c3aed').text('Learning Path Progress');
            doc.moveDown(0.5);
            
            for (const path of pathProgress.rows) {
                doc.fontSize(10).fillColor('#333').text(`${path.title}: ${path.enrolled} enrolled, ${Math.round(path.avg_completion || 0)}% avg completion`);
            }
        }
        
        // Footer
        doc.fontSize(8).fillColor('#999').text('InfoSecLabs Enterprise Report', 50, 750, { align: 'center' });
        
        doc.end();
    } catch (error) {
        console.error('Error generating team report:', error);
        res.status(500).json({ message: 'Failed to generate report' });
    }
};

// ============================================
// GENERATE INDIVIDUAL MEMBER REPORT
// ============================================
const generateMemberReport = async (req, res) => {
    try {
        const { orgId, memberId } = req.params;
        const userId = Number(req.user.id);
        
        // Verify admin access
        const membership = await db.query(
            'SELECT role FROM organization_members WHERE organization_id = ? AND user_id = ? AND role IN ("owner", "admin")',
            [orgId, userId]
        );
        
        if (membership.rows.length === 0) {
            return res.status(403).json({ message: 'Admin access required' });
        }
        
        // Get member info
        const member = await db.query(
            `SELECT u.*, om.role, om.joined_at,
                    COALESCE(ux.total_xp, 0) as total_xp,
                    COALESCE(ux.current_level, 1) as level
             FROM organization_members om
             JOIN users u ON om.user_id = u.id
             LEFT JOIN user_xp ux ON u.id = ux.user_id
             WHERE om.organization_id = ? AND om.user_id = ?`,
            [orgId, memberId]
        );
        
        if (member.rows.length === 0) {
            return res.status(404).json({ message: 'Member not found' });
        }
        
        const user = member.rows[0];
        
        // Get investigations
        const investigations = await db.query(
            `SELECT i.*, a.title as alert_title, a.severity
             FROM investigations i
             JOIN alerts a ON i.alert_id = a.id
             WHERE i.user_id = ? AND i.status = 'graded'
             ORDER BY i.graded_at DESC LIMIT 20`,
            [memberId]
        );
        
        // Get path progress
        const paths = await db.query(
            `SELECT lp.title, upp.percentage_completed, upp.started_at, upp.completed_at
             FROM user_path_progress upp
             JOIN learning_paths lp ON upp.learning_path_id = lp.id
             WHERE upp.user_id = ?`,
            [memberId]
        );
        
        // Get badges
        const badges = await db.query(
            `SELECT b.name, b.description, ub.earned_at
             FROM user_badges ub
             JOIN badges b ON ub.badge_id = b.id
             WHERE ub.user_id = ?
             ORDER BY ub.earned_at DESC`,
            [memberId]
        );
        
        // Create PDF
        const doc = new PDFDocument({ margin: 50 });
        
        res.setHeader('Content-Type', 'application/pdf');
        res.setHeader('Content-Disposition', `attachment; filename="${user.name?.replace(/\s+/g, '-') || 'member'}-report.pdf"`);
        
        doc.pipe(res);
        
        // Header
        doc.fontSize(24).fillColor('#7c3aed').text('Individual Progress Report', { align: 'center' });
        doc.moveDown();
        doc.fontSize(18).fillColor('#333').text(user.name || 'Team Member', { align: 'center' });
        doc.fontSize(10).fillColor('#666').text(user.email, { align: 'center' });
        doc.text(`Member since: ${new Date(user.joined_at).toLocaleDateString()}`, { align: 'center' });
        doc.moveDown(2);
        
        // Stats Overview
        doc.fontSize(14).fillColor('#7c3aed').text('Performance Overview');
        doc.moveDown(0.5);
        doc.fontSize(10).fillColor('#333');
        doc.text(`Level: ${user.level}`);
        doc.text(`Total XP: ${user.total_xp.toLocaleString()}`);
        doc.text(`Investigations Completed: ${investigations.rows.length}`);
        const avgScore = investigations.rows.length > 0 
            ? Math.round(investigations.rows.reduce((s, i) => s + (i.grade || 0), 0) / investigations.rows.length) 
            : 0;
        doc.text(`Average Score: ${avgScore}%`);
        doc.moveDown(2);
        
        // Recent Investigations
        if (investigations.rows.length > 0) {
            doc.fontSize(14).fillColor('#7c3aed').text('Recent Investigations');
            doc.moveDown(0.5);
            doc.fontSize(9).fillColor('#333');
            
            for (const inv of investigations.rows.slice(0, 10)) {
                doc.text(`• ${inv.alert_title} - Score: ${inv.grade}% (${inv.severity})`);
            }
            doc.moveDown();
        }
        
        // Learning Paths
        if (paths.rows.length > 0) {
            doc.fontSize(14).fillColor('#7c3aed').text('Learning Paths');
            doc.moveDown(0.5);
            doc.fontSize(9).fillColor('#333');
            
            for (const path of paths.rows) {
                const status = path.completed_at ? '✓ Completed' : `${Math.round(path.percentage_completed)}% complete`;
                doc.text(`• ${path.title} - ${status}`);
            }
            doc.moveDown();
        }
        
        // Badges
        if (badges.rows.length > 0) {
            doc.fontSize(14).fillColor('#7c3aed').text('Earned Badges');
            doc.moveDown(0.5);
            doc.fontSize(9).fillColor('#333');
            
            for (const badge of badges.rows) {
                doc.text(`• ${badge.name} - ${new Date(badge.earned_at).toLocaleDateString()}`);
            }
        }
        
        // Footer
        doc.fontSize(8).fillColor('#999').text('InfoSecLabs Enterprise Report', 50, 750, { align: 'center' });
        
        doc.end();
    } catch (error) {
        console.error('Error generating member report:', error);
        res.status(500).json({ message: 'Failed to generate report' });
    }
};

// ============================================
// GENERATE COMPLIANCE REPORT
// ============================================
const generateComplianceReport = async (req, res) => {
    try {
        const { orgId } = req.params;
        const { framework = 'general' } = req.query; // 'soc2', 'nist', 'iso27001', 'general'
        const userId = Number(req.user.id);
        
        // Verify admin access
        const membership = await db.query(
            'SELECT role FROM organization_members WHERE organization_id = ? AND user_id = ? AND role IN ("owner", "admin")',
            [orgId, userId]
        );
        
        if (membership.rows.length === 0) {
            return res.status(403).json({ message: 'Admin access required' });
        }
        
        // Get organization info
        const org = await db.query('SELECT * FROM organizations WHERE id = ?', [orgId]);
        
        // Get training completion stats
        const trainingStats = await db.query(
            `SELECT 
                COUNT(DISTINCT om.user_id) as total_members,
                COUNT(DISTINCT CASE WHEN u.training_completed = 1 THEN om.user_id END) as training_completed,
                COUNT(DISTINCT CASE WHEN ux.current_level >= 5 THEN om.user_id END) as proficient_members
             FROM organization_members om
             JOIN users u ON om.user_id = u.id
             LEFT JOIN user_xp ux ON u.id = ux.user_id
             WHERE om.organization_id = ?`,
            [orgId]
        );
        
        // Get path completion by category
        const pathCompletion = await db.query(
            `SELECT 
                lp.title,
                COUNT(DISTINCT upp.user_id) as enrolled,
                SUM(CASE WHEN upp.percentage_completed = 100 THEN 1 ELSE 0 END) as completed
             FROM learning_paths lp
             LEFT JOIN user_path_progress upp ON lp.id = upp.learning_path_id
             LEFT JOIN organization_members om ON upp.user_id = om.user_id AND om.organization_id = ?
             GROUP BY lp.id, lp.title`,
            [orgId]
        );
        
        // Framework-specific mappings
        const frameworks = {
            soc2: {
                name: 'SOC 2 Type II',
                controls: [
                    { id: 'CC1.1', name: 'Security Awareness Training', metric: 'training_completed' },
                    { id: 'CC6.1', name: 'Logical Access Controls', metric: 'investigations' },
                    { id: 'CC7.2', name: 'Incident Response', metric: 'avg_score' }
                ]
            },
            nist: {
                name: 'NIST Cybersecurity Framework',
                controls: [
                    { id: 'PR.AT-1', name: 'Awareness and Training', metric: 'training_completed' },
                    { id: 'DE.AE-2', name: 'Event Analysis', metric: 'investigations' },
                    { id: 'RS.AN-1', name: 'Incident Analysis', metric: 'avg_score' }
                ]
            },
            iso27001: {
                name: 'ISO 27001:2022',
                controls: [
                    { id: 'A.6.3', name: 'Information Security Awareness', metric: 'training_completed' },
                    { id: 'A.5.24', name: 'Incident Management', metric: 'investigations' },
                    { id: 'A.8.16', name: 'Monitoring Activities', metric: 'avg_score' }
                ]
            },
            general: {
                name: 'General Security Training',
                controls: [
                    { id: 'TRN-01', name: 'Security Awareness', metric: 'training_completed' },
                    { id: 'TRN-02', name: 'Hands-on Training', metric: 'investigations' },
                    { id: 'TRN-03', name: 'Skill Proficiency', metric: 'avg_score' }
                ]
            }
        };
        
        const fw = frameworks[framework] || frameworks.general;
        const stats = trainingStats.rows[0];
        
        // Create PDF
        const doc = new PDFDocument({ margin: 50 });
        
        res.setHeader('Content-Type', 'application/pdf');
        res.setHeader('Content-Disposition', `attachment; filename="${org.rows[0].slug}-compliance-${framework}.pdf"`);
        
        doc.pipe(res);
        
        // Header
        doc.fontSize(24).fillColor('#7c3aed').text('Compliance Training Report', { align: 'center' });
        doc.moveDown();
        doc.fontSize(14).fillColor('#333').text(org.rows[0].name, { align: 'center' });
        doc.fontSize(12).fillColor('#666').text(`Framework: ${fw.name}`, { align: 'center' });
        doc.fontSize(10).text(`Report Period: ${new Date().toLocaleDateString()}`, { align: 'center' });
        doc.moveDown(2);
        
        // Executive Summary
        doc.fontSize(14).fillColor('#7c3aed').text('Executive Summary');
        doc.moveDown(0.5);
        doc.fontSize(10).fillColor('#333');
        
        const completionRate = stats.total_members > 0 
            ? Math.round((stats.training_completed / stats.total_members) * 100) 
            : 0;
        
        doc.text(`Organization: ${org.rows[0].name}`);
        doc.text(`Total Team Members: ${stats.total_members}`);
        doc.text(`Training Completion Rate: ${completionRate}%`);
        doc.text(`Proficient Members (Level 5+): ${stats.proficient_members}`);
        doc.moveDown(2);
        
        // Control Mapping
        doc.fontSize(14).fillColor('#7c3aed').text(`${fw.name} Control Mapping`);
        doc.moveDown(0.5);
        
        for (const control of fw.controls) {
            doc.fontSize(11).fillColor('#333').text(`${control.id}: ${control.name}`);
            
            let status = '✓ Compliant';
            let statusColor = '#22c55e';
            
            if (control.metric === 'training_completed' && completionRate < 80) {
                status = '⚠ Partial';
                statusColor = '#f59e0b';
            }
            
            doc.fontSize(10).fillColor(statusColor).text(`   Status: ${status}`);
            doc.fillColor('#666').text(`   Evidence: InfoSecLabs Training Platform Records`);
            doc.moveDown(0.5);
        }
        
        // Training Paths Detail
        doc.addPage();
        doc.fontSize(14).fillColor('#7c3aed').text('Training Program Details');
        doc.moveDown(0.5);
        
        for (const path of pathCompletion.rows) {
            if (path.enrolled > 0) {
                doc.fontSize(10).fillColor('#333').text(`${path.title}: ${path.completed}/${path.enrolled} completed`);
            }
        }
        
        // Attestation
        doc.moveDown(2);
        doc.fontSize(12).fillColor('#7c3aed').text('Attestation');
        doc.moveDown(0.5);
        doc.fontSize(9).fillColor('#333').text(
            `This report certifies that the above-named organization has implemented a security awareness training program through InfoSecLabs. Training records are maintained digitally and available for audit upon request.`
        );
        doc.moveDown();
        doc.text(`Report Generated: ${new Date().toISOString()}`);
        doc.text(`Platform: InfoSecLabs Enterprise`);
        
        // Footer
        doc.fontSize(8).fillColor('#999').text('InfoSecLabs Compliance Report - Confidential', 50, 750, { align: 'center' });
        
        doc.end();
    } catch (error) {
        console.error('Error generating compliance report:', error);
        res.status(500).json({ message: 'Failed to generate report' });
    }
};

module.exports = {
    generateTeamReport,
    generateMemberReport,
    generateComplianceReport
};
