const db = require('../db');
const crypto = require('crypto');
const { generateCustomAlert } = require('../services/alertGenerator');
const { sendOrganizationInviteEmail } = require('../utils/emailService');
const { generateProgressReport, generateComplianceReport } = require('../services/pdfReportService');

// ============================================
// CREATE ORGANIZATION
// ============================================
const createOrganization = async (req, res) => {
    try {
        const { name, team_size, position, industry, license_seats } = req.body;
        const userId = Number(req.user.id);
        const seats = parseInt(license_seats) || 5;
        
        if (!name || name.trim().length < 2) {
            return res.status(400).json({ message: 'Organization name must be at least 2 characters' });
        }
        
        // Generate unique slug
        let slug = name.toLowerCase().replace(/[^a-z0-9]+/g, '-').replace(/(^-|-$)/g, '');
        const slugCheck = await db.query('SELECT id FROM organizations WHERE slug = ?', [slug]);
        if (slugCheck.rows.length > 0) {
            slug = `${slug}-${Date.now().toString(36)}`;
        }
        
        // Calculate 7-day trial period
        const trialStartDate = new Date();
        const trialEndDate = new Date(Date.now() + 7 * 24 * 60 * 60 * 1000); // 7 days
        
        // Create organization with extended fields and trial dates
        const result = await db.query(
            `INSERT INTO organizations (name, slug, owner_id, license_seats, used_seats, plan_type, team_size, admin_position, industry, trial_start_date, trial_end_date, trial_status) 
             VALUES (?, ?, ?, ?, 1, 'trial', ?, ?, ?, ?, ?, 'active')`,
            [name.trim(), slug, userId, seats, team_size || '5-10', position || null, industry || null, trialStartDate, trialEndDate]
        );
        
        const orgId = result.insertId;
        
        // Add owner as first member
        await db.query(
            `INSERT INTO organization_members (organization_id, user_id, role) VALUES (?, ?, 'owner')`,
            [orgId, userId]
        );
        
        // Update user's organization_id
        await db.query('UPDATE users SET organization_id = ? WHERE id = ?', [orgId, userId]);
        
        res.status(201).json({
            message: 'Organization created successfully',
            organization: { 
                id: orgId, 
                name: name.trim(), 
                slug, 
                team_size, 
                position, 
                industry,
                trial_end_date: trialEndDate,
                days_remaining: 7
            }
        });
    } catch (error) {
        console.error('Error creating organization:', error);
        res.status(500).json({ message: 'Failed to create organization' });
    }
};

// ============================================
// GET ORGANIZATION DETAILS
// ============================================
const getOrganization = async (req, res) => {
    try {
        const { id } = req.params;
        const userId = req.user.id;
        
        // Check membership
        const membership = await db.query(
            'SELECT role FROM organization_members WHERE organization_id = ? AND user_id = ?',
            [id, userId]
        );
        
        if (membership.rows.length === 0) {
            return res.status(403).json({ message: 'Not a member of this organization' });
        }
        
        const org = await db.query(
            `SELECT o.*, 
                    (SELECT COUNT(*) FROM organization_members WHERE organization_id = o.id) as member_count
             FROM organizations o WHERE o.id = ?`,
            [id]
        );
        
        if (org.rows.length === 0) {
            return res.status(404).json({ message: 'Organization not found' });
        }
        
        res.json({ 
            organization: org.rows[0],
            userRole: membership.rows[0].role
        });
    } catch (error) {
        console.error('Error fetching organization:', error);
        res.status(500).json({ message: 'Failed to fetch organization' });
    }
};

// ============================================
// GET ORGANIZATION MEMBERS
// ============================================
const getMembers = async (req, res) => {
    try {
        const { id } = req.params;
        const userId = req.user.id;
        
        // Check membership
        const membership = await db.query(
            'SELECT role FROM organization_members WHERE organization_id = ? AND user_id = ?',
            [id, userId]
        );
        
        if (membership.rows.length === 0) {
            return res.status(403).json({ message: 'Not a member of this organization' });
        }
        
        const members = await db.query(
            `SELECT 
                om.id as membership_id,
                om.role,
                om.joined_at,
                u.id as user_id,
                u.name,
                u.email,
                u.profile_picture,
                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 IN ('graded', 'completed')) as investigations_completed,
                (SELECT AVG(grade) FROM investigations WHERE user_id = u.id AND status IN ('graded', 'completed')) as avg_score
             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 om.role = 'owner' DESC, om.role = 'admin' DESC, u.name ASC`,
            [id]
        );
        
        res.json({ members: members.rows });
    } catch (error) {
        console.error('Error fetching members:', error);
        res.status(500).json({ message: 'Failed to fetch members' });
    }
};

// ============================================
// INVITE MEMBER
// ============================================
const inviteMember = async (req, res) => {
    try {
        const { id } = req.params;
        const { email, role = 'member' } = req.body;
        const userId = req.user.id;
        
        // Check admin permission
        const membership = await db.query(
            'SELECT role FROM organization_members WHERE organization_id = ? AND user_id = ?',
            [id, userId]
        );
        
        if (membership.rows.length === 0 || !['owner', 'admin'].includes(membership.rows[0].role)) {
            return res.status(403).json({ message: 'Only admins can invite members' });
        }
        
        // Check seat availability
        const org = await db.query('SELECT license_seats, used_seats FROM organizations WHERE id = ?', [id]);
        if (org.rows[0].used_seats >= org.rows[0].license_seats) {
            return res.status(400).json({ message: 'No available seats. Please upgrade your plan.' });
        }
        
        // Check if already invited or member
        const existingInvite = await db.query(
            'SELECT id FROM organization_invites WHERE organization_id = ? AND email = ? AND accepted_at IS NULL',
            [id, email]
        );
        if (existingInvite.rows.length > 0) {
            return res.status(400).json({ message: 'User already has a pending invite' });
        }
        
        const existingMember = await db.query(
            `SELECT om.id FROM organization_members om
             JOIN users u ON om.user_id = u.id
             WHERE om.organization_id = ? AND u.email = ?`,
            [id, email]
        );
        if (existingMember.rows.length > 0) {
            return res.status(400).json({ message: 'User is already a member' });
        }
        
        // Create invite
        const token = crypto.randomBytes(32).toString('hex');
        const expiresAt = new Date(Date.now() + 7 * 24 * 60 * 60 * 1000); // 7 days
        
        await db.query(
            `INSERT INTO organization_invites (organization_id, email, token, role, invited_by, expires_at)
             VALUES (?, ?, ?, ?, ?, ?)`,
            [id, email, token, role, userId, expiresAt]
        );
        
        // Get org name for email
        const orgNameRes = await db.query('SELECT name FROM organizations WHERE id = ?', [id]);
        const orgName = orgNameRes.rows[0]?.name || 'InfoSecLabs Business';

        // Send invite email
        await sendOrganizationInviteEmail(email, orgName, token, role);
        
        res.status(201).json({ 
            message: 'Invitation sent successfully',
            invite: { email, role, token, expiresAt }
        });
    } catch (error) {
        console.error('Error inviting member:', error);
        res.status(500).json({ message: 'Failed to send invitation' });
    }
};

// ============================================
// ACCEPT INVITE
// ============================================
const acceptInvite = async (req, res) => {
    try {
        const { token } = req.params;
        const userId = req.user.id;
        
        const invite = await db.query(
            `SELECT oi.*, o.name as org_name, o.used_seats, o.license_seats
             FROM organization_invites oi
             JOIN organizations o ON oi.organization_id = o.id
             WHERE oi.token = ? AND oi.accepted_at IS NULL`,
            [token]
        );
        
        if (invite.rows.length === 0) {
            return res.status(404).json({ message: 'Invalid or expired invitation' });
        }
        
        const inv = invite.rows[0];
        
        if (new Date(inv.expires_at) < new Date()) {
            return res.status(400).json({ message: 'Invitation has expired' });
        }
        
        if (inv.used_seats >= inv.license_seats) {
            return res.status(400).json({ message: 'Organization has no available seats' });
        }
        
        // Check if user email matches invite
        const user = await db.query('SELECT email FROM users WHERE id = ?', [userId]);
        if (user.rows[0].email.toLowerCase() !== inv.email.toLowerCase()) {
            return res.status(403).json({ message: 'This invitation was sent to a different email' });
        }
        
        // Add as member
        await db.query(
            `INSERT INTO organization_members (organization_id, user_id, role, invited_by)
             VALUES (?, ?, ?, ?)`,
            [inv.organization_id, userId, inv.role, inv.invited_by]
        );
        
        // Update seat count
        await db.query('UPDATE organizations SET used_seats = used_seats + 1 WHERE id = ?', [inv.organization_id]);
        
        // Update user's organization_id
        await db.query('UPDATE users SET organization_id = ? WHERE id = ?', [inv.organization_id, userId]);
        
        // Mark invite as accepted
        await db.query('UPDATE organization_invites SET accepted_at = NOW() WHERE id = ?', [inv.id]);
        
        res.json({ message: 'Successfully joined organization', organization: inv.org_name });
    } catch (error) {
        console.error('Error accepting invite:', error);
        res.status(500).json({ message: 'Failed to accept invitation' });
    }
};

// ============================================
// REMOVE MEMBER
// ============================================
const removeMember = async (req, res) => {
    try {
        const { id, userId: targetUserId } = req.params;
        const adminId = req.user.id;
        
        // Check admin permission
        const adminMembership = await db.query(
            'SELECT role FROM organization_members WHERE organization_id = ? AND user_id = ?',
            [id, adminId]
        );
        
        if (adminMembership.rows.length === 0 || !['owner', 'admin'].includes(adminMembership.rows[0].role)) {
            return res.status(403).json({ message: 'Only admins can remove members' });
        }
        
        // Check target membership
        const targetMembership = await db.query(
            'SELECT role FROM organization_members WHERE organization_id = ? AND user_id = ?',
            [id, targetUserId]
        );
        
        if (targetMembership.rows.length === 0) {
            return res.status(404).json({ message: 'Member not found' });
        }
        
        if (targetMembership.rows[0].role === 'owner') {
            return res.status(400).json({ message: 'Cannot remove the organization owner' });
        }
        
        // Remove member
        await db.query('DELETE FROM organization_members WHERE organization_id = ? AND user_id = ?', [id, targetUserId]);
        
        // Update seat count
        await db.query('UPDATE organizations SET used_seats = used_seats - 1 WHERE id = ?', [id]);
        
        // Clear user's organization_id
        await db.query('UPDATE users SET organization_id = NULL WHERE id = ?', [targetUserId]);
        
        res.json({ message: 'Member removed successfully' });
    } catch (error) {
        console.error('Error removing member:', error);
        res.status(500).json({ message: 'Failed to remove member' });
    }
};

// ============================================
// GET TEAM ANALYTICS
// ============================================
const getTeamAnalytics = async (req, res) => {
    try {
        const { id } = req.params;
        const userId = Number(req.user.id);
        
        // Check membership
        const membership = await db.query(
            'SELECT role FROM organization_members WHERE organization_id = ? AND user_id = ?',
            [id, userId]
        );
        
        if (membership.rows.length === 0) {
            return res.status(403).json({ message: 'Not a member of this organization' });
        }
        
        // Get member IDs
        const members = await db.query(
            'SELECT user_id FROM organization_members WHERE organization_id = ?',
            [id]
        );
        const memberIds = members.rows.map(m => m.user_id);
        
        if (memberIds.length === 0) {
            return res.json({ analytics: {} });
        }
        
        // Team totals
        const teamStats = await db.query(
            `SELECT 
                SUM(ux.total_xp) as team_xp,
                AVG(ux.total_xp) as avg_xp,
                AVG(ux.current_level) as avg_level
             FROM user_xp ux
             WHERE ux.user_id IN (?)`,
            [memberIds]
        );
        
        // Investigations stats
        const investigationStats = await db.query(
            `SELECT 
                COUNT(*) as total_investigations,
                SUM(CASE WHEN status IN ('graded', 'completed') THEN 1 ELSE 0 END) as completed,
                AVG(CASE WHEN status IN ('graded', 'completed') THEN grade ELSE NULL END) as avg_score
             FROM investigations
             WHERE user_id IN (?)`,
            [memberIds]
        );
        
        // 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
             WHERE upp.user_id IN (?)
             GROUP BY lp.id, lp.title`,
            [memberIds]
        );

        // Weekly Activity (Last 7 Days)
        const weeklyActivity = [];
        const days = ['Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat'];
        const today = new Date();
        
        // Generate last 7 days mapping
        const last7Days = [];
        for (let i = 6; i >= 0; i--) {
            const d = new Date(today);
            d.setDate(today.getDate() - i);
            const dayName = days[d.getDay()];
            const dateStr = d.toISOString().split('T')[0];
            last7Days.push({ day: dayName, date: dateStr, count: 0 });
        }

        // Get completed tasks/investigations in last 7 days, filtered by when user joined the org
        const activityQuery = await db.query(
            `SELECT DATE(completed_at) as activity_date, COUNT(*) as count
             FROM (
                SELECT utp.completed_at 
                FROM user_task_progress utp
                JOIN organization_members om ON utp.user_id = om.user_id
                WHERE om.organization_id = ? 
                  AND utp.completed = 1 
                  AND utp.completed_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
                  AND utp.completed_at >= om.joined_at
                
                UNION ALL
                
                SELECT i.updated_at as completed_at 
                FROM investigations i
                JOIN organization_members om ON i.user_id = om.user_id
                WHERE om.organization_id = ?
                  AND i.status IN ('graded', 'completed')
                  AND i.updated_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
                  AND i.updated_at >= om.joined_at
             ) as combined_activity
             GROUP BY DATE(completed_at)`,
            [id, id]
        );

        // Map database results to our 7-day array
        activityQuery.rows.forEach(row => {
            // Adjust for potentially different date formats returned by driver
            const actDate = new Date(row.activity_date).toISOString().split('T')[0];
            const dayEntry = last7Days.find(d => d.date === actDate);
            if (dayEntry) {
                dayEntry.count += parseInt(row.count, 10);
            }
        });

        // Severity Stats (for Pie/Bar Chart)
        const severityStats = await db.query(`
            SELECT a.severity, COUNT(*) as count
            FROM alerts a
            JOIN investigations i ON a.id = i.alert_id
            JOIN organization_members om ON i.user_id = om.user_id
            WHERE om.organization_id = ?
            GROUP BY a.severity
        `, [id]);

        // Status Stats (for Pie Chart)
        const statusStats = await db.query(`
            SELECT status, COUNT(*) as count
            FROM investigations i
            JOIN organization_members om ON i.user_id = om.user_id
            WHERE om.organization_id = ?
            GROUP BY status
        `, [id]);

        res.json({
            analytics: {
                memberCount: memberIds.length,
                teamXP: teamStats.rows[0]?.team_xp || 0,
                avgXP: Math.round(teamStats.rows[0]?.avg_xp || 0),
                avgLevel: Math.round(teamStats.rows[0]?.avg_level || 1),
                investigations: investigationStats.rows[0] || {},
                pathProgress: pathProgress.rows,
                weeklyActivity: last7Days.map(d => ({ name: d.day, value: d.count })),
                severityStats: severityStats.rows,
                statusStats: statusStats.rows
            }
        });
    } catch (error) {
        console.error('Error fetching team analytics:', error);
        res.status(500).json({ message: 'Failed to fetch analytics' });
    }
};

// ============================================
// GET USER'S ORGANIZATION
// ============================================
const getMyOrganization = async (req, res) => {
    try {
        const userId = req.user.id;
        
        const membership = await db.query(
            `SELECT o.*, om.role as user_role
             FROM organization_members om
             JOIN organizations o ON om.organization_id = o.id
             WHERE om.user_id = ?`,
            [userId]
        );
        
        if (membership.rows.length === 0) {
            return res.json({ organization: null });
        }
        
        const org = membership.rows[0];
        
        // Calculate trial status
        let trialInfo = {
            is_trial: org.plan_type === 'trial',
            trial_expired: false,
            days_remaining: null
        };
        
        if (org.trial_end_date) {
            const now = new Date();
            const endDate = new Date(org.trial_end_date);
            const diffTime = endDate - now;
            const diffDays = Math.ceil(diffTime / (1000 * 60 * 60 * 24));
            
            trialInfo.days_remaining = Math.max(0, diffDays);
            trialInfo.trial_expired = diffDays <= 0;
            
            // Auto-update trial_status if expired
            if (trialInfo.trial_expired && org.trial_status === 'active') {
                await db.query(
                    'UPDATE organizations SET trial_status = ? WHERE id = ?',
                    ['expired', org.id]
                );
                org.trial_status = 'expired';
            }
        }
        
        res.json({ 
            organization: { ...org, ...trialInfo }
        });
    } catch (error) {
        console.error('Error fetching user organization:', error);
        res.status(500).json({ message: 'Failed to fetch organization' });
    }
};

// ============================================
// UPDATE ORGANIZATION SETTINGS
// ============================================
const updateSettings = async (req, res) => {
    try {
        const { id } = req.params;
        const { name, logo, primary_color, secondary_color, allow_custom_alerts } = req.body;
        const userId = req.user.id;
        
        // Check admin permission
        const membership = await db.query(
            'SELECT role FROM organization_members WHERE organization_id = ? AND user_id = ? AND role IN ("owner", "admin")',
            [id, userId]
        );
        
        if (membership.rows.length === 0) {
            return res.status(403).json({ message: 'Admin access required' });
        }
        
        // Build settings JSON
        const settingsUpdate = {};
        if (logo !== undefined) settingsUpdate.logo = logo;
        if (primary_color) settingsUpdate.primary_color = primary_color;
        if (secondary_color) settingsUpdate.secondary_color = secondary_color;
        if (allow_custom_alerts !== undefined) settingsUpdate.allow_custom_alerts = allow_custom_alerts;
        if (req.body.show_on_global_leaderboard !== undefined) settingsUpdate.show_on_global_leaderboard = req.body.show_on_global_leaderboard;
        
        // Update org
        const updates = [];
        const params = [];
        
        if (name) {
            updates.push('name = ?');
            params.push(name);
        }
        
        if (Object.keys(settingsUpdate).length > 0) {
            updates.push('settings = JSON_MERGE_PATCH(COALESCE(settings, "{}"), ?)');
            params.push(JSON.stringify(settingsUpdate));
        }
        
        if (updates.length > 0) {
            params.push(id);
            await db.query(`UPDATE organizations SET ${updates.join(', ')} WHERE id = ?`, params);
        }
        
        res.json({ message: 'Settings updated successfully' });
    } catch (error) {
        console.error('Error updating settings:', error);
        res.status(500).json({ message: 'Failed to update settings' });
    }
};

// ============================================
// UPLOAD ORGANIZATION LOGO
// ============================================
const uploadLogo = async (req, res) => {
    try {
        const { id } = req.params;
        const userId = req.user.id;
        
        // Check admin permission
        const membership = await db.query(
            'SELECT role FROM organization_members WHERE organization_id = ? AND user_id = ? AND role IN ("owner", "admin")',
            [id, userId]
        );
        
        if (membership.rows.length === 0) {
            return res.status(403).json({ message: 'Admin access required' });
        }
        
        if (!req.file) {
            return res.status(400).json({ message: 'No file uploaded' });
        }
        
        const logoPath = `/uploads/org-logos/${req.file.filename}`;
        
        await db.query(
            'UPDATE organizations SET settings = JSON_SET(COALESCE(settings, "{}"), "$.logo", ?) WHERE id = ?',
            [logoPath, id]
        );
        
        res.json({ message: 'Logo uploaded successfully', logo: logoPath });
    } catch (error) {
        console.error('Error uploading logo:', error);
        res.status(500).json({ message: 'Failed to upload logo' });
    }
};

// ============================================
// CREATE CUSTOM ALERT (ORG SPECIFIC)
// ============================================
const createCustomAlert = async (req, res) => {
    try {
        const { id } = req.params;
        const { title, severity, alert_type, details, raw_log, playbook_solution, difficulty } = req.body;
        const userId = req.user.id;
        
        // Check admin permission
        const membership = await db.query(
            'SELECT role FROM organization_members WHERE organization_id = ? AND user_id = ? AND role IN ("owner", "admin")',
            [id, userId]
        );
        
        if (membership.rows.length === 0) {
            return res.status(403).json({ message: 'Admin access required' });
        }
        
        // Check if custom alerts allowed (skip for now - default allow for all)
        // const org = await db.query('SELECT settings FROM organizations WHERE id = ?', [id]);
        // const settings = org.rows[0]?.settings ? JSON.parse(org.rows[0].settings) : {};
        // if (!settings.allow_custom_alerts) {
        //     return res.status(403).json({ message: 'Custom alerts not enabled for this organization' });
        // }

        let finalAlertData = {
            title,
            severity,
            alert_type,
            details,
            raw_log,
            playbook_solution: (typeof playbook_solution === 'object' && playbook_solution !== null) 
                ? JSON.stringify(playbook_solution) 
                : (playbook_solution || null),
            difficulty: difficulty || 'Intermediate'
        };

        // If playbook solution is missing (which is standard from frontend), use AI to generate it + consistent raw_log
        if (!playbook_solution) {
            try {
                // If user provided a raw_log, treat it as context/inspiration for the AI
                const aiContextDetails = raw_log 
                    ? `${details}\n\nTechnical Context (User provided sample log): ${raw_log}` 
                    : details;

                const aiAlert = await generateCustomAlert({
                    title,
                    severity,
                    alert_type,
                    details: aiContextDetails,
                    difficulty: difficulty || 'Intermediate'
                }, process.env.OPENAI_API_KEY);

                // Use AI generated content for consistency
                finalAlertData.raw_log = JSON.stringify(aiAlert.raw_log);
                finalAlertData.playbook_solution = JSON.stringify(aiAlert.playbook_solution);
                
                // Optional: Use AI enhanced details if they are better? For now keep user details or append.
                // But keep user title to not confuse them.

            } catch (aiError) {
                console.error('[CreateCustomAlert] AI Generation failed:', aiError.message);
                // Fallback: Create a minimal valid V2 playbook so grading doesn't crash
                finalAlertData.playbook_solution = JSON.stringify({
                    playbook_version: 2,
                    artifacts: [],
                    expected_actions: ["close_alert"],
                    expected_verdict: "true_positive",
                    analysis_notes: "AI Generation failed. Manual review required."
                });
            }
        }
        
        // Create alert with organization_id
        const result = await db.query(
            `INSERT INTO alerts (title, severity, alert_type, details, raw_log, playbook_solution, difficulty, status, created_by, organization_id)
             VALUES (?, ?, ?, ?, ?, ?, ?, 'active', ?, ?)`,
            [
                finalAlertData.title, 
                finalAlertData.severity, 
                finalAlertData.alert_type, 
                JSON.stringify(finalAlertData.details || ''), 
                finalAlertData.raw_log, 
                finalAlertData.playbook_solution, 
                finalAlertData.difficulty, 
                userId, 
                id
            ]
        );
        
        res.status(201).json({
            message: 'Custom alert created successfully',
            alert: { id: result.insertId, title }
        });
    } catch (error) {
        console.error('Error creating custom alert:', error);
        res.status(500).json({ message: 'Failed to create alert', error: error.message });
    }
};

// ============================================
// GET ORGANIZATION CUSTOM ALERTS
// ============================================
const getOrgAlerts = async (req, res) => {
    try {
        const { id } = req.params;
        const userId = req.user.id;
        
        // Check membership
        const membership = await db.query(
            'SELECT role FROM organization_members WHERE organization_id = ? AND user_id = ?',
            [id, userId]
        );
        
        if (membership.rows.length === 0) {
            return res.status(403).json({ message: 'Not a member of this organization' });
        }
        
        const alerts = await db.query(
            `SELECT id, title, severity, alert_type, difficulty, status, created_at
             FROM alerts
             WHERE organization_id = ?
             ORDER BY created_at DESC`,
            [id]
        );
        
        res.json({ alerts: alerts.rows });
    } catch (error) {
        console.error('Error fetching org alerts:', error);
        res.status(500).json({ message: 'Failed to fetch alerts' });
    }
};

// ============================================
// DELETE CUSTOM ALERT
// ============================================
const deleteCustomAlert = async (req, res) => {
    try {
        const { id, alertId } = req.params;
        const userId = req.user.id;
        
        // Check admin permission
        const membership = await db.query(
            'SELECT role FROM organization_members WHERE organization_id = ? AND user_id = ? AND role IN ("owner", "admin")',
            [id, userId]
        );
        
        if (membership.rows.length === 0) {
            return res.status(403).json({ message: 'Admin access required' });
        }
        
        // Check if alert belongs to organization
        const alert = await db.query(
            'SELECT id FROM alerts WHERE id = ? AND organization_id = ?',
            [alertId, id]
        );
        
        if (alert.rows.length === 0) {
            return res.status(404).json({ message: 'Alert not found or does not belong to this organization' });
        }
        
        // Delete alert (soft delete could be an option, but hard delete for now as per requirement)
        await db.query('DELETE FROM alerts WHERE id = ?', [alertId]);
        
        res.json({ message: 'Alert deleted successfully' });
    } catch (error) {
        console.error('Error deleting custom alert:', error);
        res.status(500).json({ message: 'Failed to delete alert' });
    }
};

// ============================================
// ASSIGN LEARNING PATH OR CTF TO MEMBERS
// ============================================
const assignLearningPath = async (req, res) => {
    try {
        const { id } = req.params;
        const { user_ids, learning_path_id, operation_id, due_date, is_mandatory = false } = req.body;
        const adminId = req.user.id;
        
        // Check admin permission
        const membership = await db.query(
            'SELECT role FROM organization_members WHERE organization_id = ? AND user_id = ? AND role IN ("owner", "admin")',
            [id, adminId]
        );
        
        if (membership.rows.length === 0) {
            return res.status(403).json({ message: 'Admin access required' });
        }
        
        // Verify all users are org members
        const placeholders = user_ids.map(() => '?').join(',');
        const members = await db.query(
            `SELECT user_id FROM organization_members WHERE organization_id = ? AND user_id IN (${placeholders})`,
            [id, ...user_ids]
        );
        
        if (members.rows.length !== user_ids.length) {
            return res.status(400).json({ message: 'Some users are not members of this organization' });
        }
        
        // Create assignments - handle both learning paths and CTF operations
        for (const userId of user_ids) {
            if (operation_id) {
                // CTF/Operation assignment
                await db.query(
                    `INSERT INTO organization_path_assignments (organization_id, user_id, operation_id, assigned_by, due_date, is_mandatory)
                     VALUES (?, ?, ?, ?, ?, ?)
                     ON DUPLICATE KEY UPDATE due_date = VALUES(due_date), is_mandatory = VALUES(is_mandatory)`,
                    [id, userId, operation_id, adminId, due_date || null, is_mandatory]
                );
            } else if (learning_path_id) {
                // Learning path assignment
                await db.query(
                    `INSERT INTO organization_path_assignments (organization_id, user_id, learning_path_id, assigned_by, due_date, is_mandatory)
                     VALUES (?, ?, ?, ?, ?, ?)
                     ON DUPLICATE KEY UPDATE due_date = VALUES(due_date), is_mandatory = VALUES(is_mandatory)`,
                    [id, userId, learning_path_id, adminId, due_date || null, is_mandatory]
                );
            }
        }
        
        const itemType = operation_id ? 'CTF mission' : 'Learning path';
        res.json({ message: `${itemType} assigned to ${user_ids.length} members` });
    } catch (error) {
        console.error('Error assigning:', error);
        res.status(500).json({ message: `Failed to assign: ${error.message}` });
    }
};

// ============================================
// GET LEARNING PATH ASSIGNMENTS
// ============================================
// GET ORGANIZATION ASSIGNMENTS (PATHS, CTF, ALERTS)
// ============================================
const getPathAssignments = async (req, res) => {
    try {
        const { id } = req.params;
        const userId = req.user.id;
        
        // Check membership
        const membership = await db.query(
            'SELECT role FROM organization_members WHERE organization_id = ? AND user_id = ?',
            [id, userId]
        );
        
        if (membership.rows.length === 0) {
            return res.status(403).json({ message: 'Not a member of this organization' });
        }
        
        // 1. Fetch Path & CTF Assignments
        const pathAssignments = await db.query(
            `SELECT 
                opa.id as assignment_id,
                opa.user_id,
                u.name as user_name, 
                u.email,
                u.profile_picture,
                opa.created_at as assigned_date,
                opa.due_date,
                opa.is_mandatory,
                
                -- Determine Type & Title
                CASE 
                    WHEN opa.operation_id IS NOT NULL THEN 'ctf'
                    ELSE 'path'
                END as type,
                
                CASE 
                    WHEN opa.operation_id IS NOT NULL THEN op.title
                    ELSE lp.title
                END as title,
                
                -- Progress
                CASE 
                    WHEN opa.operation_id IS NOT NULL THEN COALESCE(uop.current_alert_sequence, 0)
                    ELSE COALESCE(upp.percentage_completed, 0)
                END as progress,
                
                -- Status
                CASE 
                   WHEN opa.operation_id IS NOT NULL AND uop.completed_at IS NOT NULL THEN 'completed'
                   WHEN opa.learning_path_id IS NOT NULL AND upp.completed_at IS NOT NULL THEN 'completed'
                   ELSE 'pending'
                END as status,
                
                NULL as score -- Paths don't have a single score usually
                
             FROM organization_path_assignments opa
             JOIN users u ON opa.user_id = u.id
             LEFT JOIN learning_paths lp ON opa.learning_path_id = lp.id
             LEFT JOIN operations op ON opa.operation_id = op.id
             LEFT JOIN user_path_progress upp ON opa.user_id = upp.user_id AND opa.learning_path_id = upp.learning_path_id
             LEFT JOIN user_operation_progress uop ON opa.user_id = uop.user_id AND opa.operation_id = uop.operation_id
             WHERE opa.organization_id = ?`,
            [id]
        );

        // 2. Fetch Alert Assignments (Investigations) for Org Members
        // We find investigations where the user is in the org.
        // Ideally we should filter by investigations assigned *by* the org, but currently we just check if user is in org.
        // Refinement: Only show investigations assigned by admins of this org? 
        // For now, let's show ALL investigations for members of this org to keep it comprehensive.
        const alertAssignments = await db.query(
            `SELECT 
                i.id as assignment_id,
                i.user_id,
                u.name as user_name, 
                u.email,
                u.profile_picture,
                i.created_at as assigned_date,
                i.due_date,
                0 as is_mandatory, -- Alerts don't have mandatory flag in schema yet, assume optional or logic else
                
                'alert' as type,
                a.title,
                
                CASE 
                    WHEN i.status = 'graded' THEN 100
                    WHEN i.status = 'submitted' THEN 50
                    ELSE 0
                END as progress,
                
                i.status,
                i.grade as score
                
             FROM investigations i
             JOIN users u ON i.user_id = u.id
             JOIN organization_members om ON u.id = om.user_id
             JOIN alerts a ON i.alert_id = a.id
             WHERE om.organization_id = ?
             AND (i.assigned_by IS NOT NULL) -- Only manually assigned ones (optional filter)
            `,
            [id]
        );

        // Combine and Sort
        const allAssignments = [
            ...pathAssignments.rows,
            ...alertAssignments.rows
        ].sort((a, b) => new Date(b.assigned_date) - new Date(a.assigned_date));
        
        res.json({ assignments: allAssignments });
    } catch (error) {
        console.error('Error fetching assignments:', error);
        res.status(500).json({ message: 'Failed to fetch assignments' });
    }
};

// ============================================
// ASSIGN ALERTS TO MEMBERS
// ============================================
const assignAlertsToMembers = async (req, res) => {
    try {
        const { id } = req.params;
        const { alert_ids, user_ids, due_date } = req.body;
        const assignerId = req.user.id;

        if (!alert_ids || !Array.isArray(alert_ids) || alert_ids.length === 0) {
            return res.status(400).json({ message: 'No alerts specified' });
        }

        if (!user_ids || !Array.isArray(user_ids) || user_ids.length === 0) {
            return res.status(400).json({ message: 'No users specified' });
        }

        // Verify admin access
        const membership = await db.query(
            'SELECT role FROM organization_members WHERE organization_id = ? AND user_id = ? AND role IN ("owner", "admin")',
            [id, assignerId]
        );

        if (membership.rows.length === 0) {
            return res.status(403).json({ message: 'Admin access required' });
        }

        // Assign alerts -> Create investigations
        let assignedCount = 0;
        
        for (const userId of user_ids) {
            // Verify user belongs to org
            const userCheck = await db.query(
                'SELECT id FROM organization_members WHERE organization_id = ? AND user_id = ?',
                [id, userId]
            );
            
            if (userCheck.rows.length === 0) continue;

            for (const alertId of alert_ids) {
                // Check if already assigned/exists
                const existing = await db.query(
                    'SELECT id FROM investigations WHERE user_id = ? AND alert_id = ?',
                    [userId, alertId]
                );

                if (existing.rows.length === 0) {
                    await db.query(
                        'INSERT INTO investigations (user_id, alert_id, status, assigned_by, due_date) VALUES (?, ?, "pending", ?, ?)',
                        [userId, alertId, assignerId, due_date || null]
                    );
                    assignedCount++;
                }
            }
        }

        res.json({ message: `Successfully assigned alerts`, assignedCount });

    } catch (error) {
        console.error('Error assigning alerts:', error);
        res.status(500).json({ message: `Failed to assign alerts: ${error.message}` });
    }
};

// ============================================
// REPORTS & EXPORTS
// ============================================

// ============================================
// LEADERBOARD
// ============================================
const getLeaderboard = async (req, res) => {
    try {
        const { id } = req.params;
        
        const result = await db.query(`
            SELECT 
                u.name,
                COALESCE(ux.current_level, 1) as level,
                COALESCE(ux.total_xp, 0) as xp,
                u.profile_picture
            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 u.role != 'admin' AND u.username != 'admin'
            ORDER BY xp DESC
            LIMIT 10
        `, [id]);

        res.json({ leaderboard: result.rows });
    } catch (error) {
        console.error('Get Leaderboard Error:', error);
        res.status(500).json({ message: 'Failed to fetch leaderboard' });
    }
};

const exportTeamProgress = async (req, res) => {
    try {
        const { id } = req.params;
        const { format } = req.query; // 'csv' or 'pdf'
        
        // Fetch org details for branding
        const orgRes = await db.query('SELECT name, settings FROM organizations WHERE id = ?', [id]);
        const org = orgRes.rows[0] || { name: 'Organization' };

        // Fetch members with stats
        const result = await db.query(`
            SELECT 
                u.name,
                u.email,
                om.role,
                COALESCE(ux.current_level, 1) as level,
                COALESCE(ux.total_xp, 0) as xp,
                (SELECT COUNT(*) FROM user_task_progress utp WHERE utp.user_id = u.id AND utp.completed = TRUE) as tasks_completed,
                (SELECT COUNT(*) FROM investigations i WHERE i.user_id = u.id AND i.status IN ('graded', 'closed')) as alerts_resolved,
                u.last_usage_reset as last_active
            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 u.name
        `, [id]);

        if (format === 'pdf') {
            res.setHeader('Content-Type', 'application/pdf');
            res.setHeader('Content-Disposition', `attachment; filename="team_progress_${id}.pdf"`);
            return generateProgressReport(org, result.rows, res);
        }

        // Generate CSV (Default)
        const headers = ['Name', 'Email', 'Role', 'Level', 'XP', 'Tasks Completed', 'Alerts Resolved', 'Last Active'];
        const csvRows = [headers.join(',')];

        result.rows.forEach(row => {
            csvRows.push([
                `"${row.name}"`,
                `"${row.email}"`,
                row.role,
                row.level,
                row.xp,
                row.tasks_completed,
                row.alerts_resolved,
                `"${row.last_active ? new Date(row.last_active).toISOString().split('T')[0] : 'N/A'}"`
            ].join(','));
        });

        const csvString = csvRows.join('\n');
        
        res.header('Content-Type', 'text/csv');
        res.attachment(`team_progress_${id}_${Date.now()}.csv`);
        res.send(csvString);

    } catch (error) {
        console.error('Export Progress Error:', error);
        res.status(500).json({ message: 'Failed to generate report' });
    }
};

const exportComplianceReport = async (req, res) => {
    try {
        const { id } = req.params;
        const { format } = req.query; // 'csv' or 'pdf'

        // Fetch org details for branding
        const orgRes = await db.query('SELECT name, settings FROM organizations WHERE id = ?', [id]);
        const org = orgRes.rows[0] || { name: 'Organization' };
        
        // Dummy compliance data logic - in real world this would query SLA metrics
        const result = await db.query(`
            SELECT 
                a.title as alert_title,
                a.severity,
                u.name as investigator,
                i.status,
                i.created_at as assigned_at,
                i.updated_at as resolved_at,
                TIMESTAMPDIFF(MINUTE, i.created_at, i.updated_at) as resolution_time_mins,
                i.grade
            FROM alerts a
            JOIN investigations i ON a.id = i.alert_id
            JOIN users u ON i.user_id = u.id
            JOIN organization_members om ON u.id = om.user_id
            WHERE om.organization_id = ? AND i.status IN ('graded', 'closed')
            ORDER BY i.updated_at DESC
            LIMIT 100
        `, [id]);

        if (format === 'pdf') {
            res.setHeader('Content-Type', 'application/pdf');
            res.setHeader('Content-Disposition', `attachment; filename="compliance_report_${id}.pdf"`);
            return generateComplianceReport(org, result.rows, res);
        }

        const headers = ['Alert Title', 'Severity', 'Investigator', 'Status', 'Assigned At', 'Resolved At', 'Resolution Time (Mins)', 'Grade'];
        const csvRows = [headers.join(',')];

        result.rows.forEach(row => {
            csvRows.push([
                `"${row.alert_title}"`,
                row.severity,
                `"${row.investigator}"`,
                row.status,
                row.assigned_at,
                row.resolved_at,
                row.resolution_time_mins || 0,
                row.grade || 'N/A'
            ].join(','));
        });

        const csvString = csvRows.join('\n');
        
        res.header('Content-Type', 'text/csv');
        res.attachment(`compliance_report_${id}_${Date.now()}.csv`);
        res.send(csvString);
    } catch (error) {
        console.error('Export Compliance Error:', error);
        res.status(500).json({ message: 'Failed to generate report' });
    }
};

const exportRawData = async (req, res) => {
    try {
        const { id } = req.params;
        
        // Export everything associated with org members
        const members = await db.query('SELECT user_id FROM organization_members WHERE organization_id = ?', [id]);
        const memberIds = members.rows.map(m => m.user_id);

        if (memberIds.length === 0) {
            return res.json({ message: 'No data available' });
        }

        const investigations = await db.query(`
            SELECT i.*, a.title as alert_title, u.name as user_name 
            FROM investigations i 
            JOIN alerts a ON i.alert_id = a.id
            JOIN users u ON i.user_id = u.id
            WHERE i.user_id IN (${memberIds.join(',')})
        `);

        res.header('Content-Type', 'application/json');
        res.attachment(`raw_data_${id}_${Date.now()}.json`);
        res.send(JSON.stringify(investigations.rows, null, 2));

    } catch (error) {
        console.error('Export Raw Error:', error);
        res.status(500).json({ message: 'Failed to generate export' });
    }
};

// ============================================
// CANCEL ORGANIZATION SUBSCRIPTION
// ============================================
const cancelSubscription = async (req, res) => {
    try {
        const { id } = req.params;
        const userId = req.user.id;
        
        // Check admin permission
        const membership = await db.query(
            "SELECT role FROM organization_members WHERE organization_id = ? AND user_id = ? AND role = 'owner'",
            [id, userId]
        );
        
        if (membership.rows.length === 0) {
            return res.status(403).json({ message: 'Only the organization owner can cancel the subscription' });
        }
        
        await db.query(
            "UPDATE organizations SET plan_type = 'free', license_seats = 5, trial_status = 'expired' WHERE id = ?",
            [id]
        );
        
        res.json({ message: 'Subscription cancelled successfully. Your organization has been reverted to the Free plan.' });
    } catch (error) {
        console.error('Error cancelling subscription:', error);
        res.status(500).json({ message: `Failed to cancel subscription: ${error.message}` });
    }
};

module.exports = {
    createOrganization,
    getOrganization,
    getMembers,
    inviteMember,
    acceptInvite,
    removeMember,
    getTeamAnalytics,
    getMyOrganization,
    updateSettings,
    uploadLogo,
    createCustomAlert,
    getOrgAlerts,
    assignLearningPath,
    getPathAssignments,
    assignAlertsToMembers,
    deleteCustomAlert,
    getLeaderboard,
    exportTeamProgress,
    exportComplianceReport,
    exportRawData,
    cancelSubscription
};
