const express = require('express');
const { getDatabase } = require('../config/database');
const { authenticate } = require('../middleware/auth');

const router = express.Router();

// Main dashboard data
router.get('/overview', authenticate, async (req, res) => {
  try {
    const db = getDatabase();
    const userId = req.user.id;
    const userRole = req.user.role;
    
    const now = new Date();
    const quarter = 'Q' + Math.ceil((now.getMonth() + 1) / 3);
    const year = now.getFullYear();
    
    // Task Summary
    let taskQuery = `
      SELECT 
        COUNT(*) as total,
        SUM(CASE WHEN status = 'not_started' THEN 1 ELSE 0 END) as notStarted,
        SUM(CASE WHEN status = 'in_progress' THEN 1 ELSE 0 END) as inProgress,
        SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed,
        SUM(CASE WHEN due_date < date('now') AND status NOT IN ('completed', 'cancelled') THEN 1 ELSE 0 END) as overdue,
        SUM(CASE WHEN requires_approval = 1 AND approval_status = 'pending' THEN 1 ELSE 0 END) as pendingApproval
      FROM tasks
      WHERE 1=1
    `;
    
    const taskParams = [];
    
    if (userRole !== 'admin' && userRole !== 'chro') {
      taskQuery += ' AND (assignee_id = ? OR assigner_id = ? OR reviewer_id = ?)';
      taskParams.push(userId, userId, userId);
    }
    
    const taskStats = db.prepare(taskQuery).get(...taskParams);
    
    // Rocks Summary
    let rocksQuery = `
      SELECT 
        COUNT(*) as total,
        SUM(CASE WHEN status = 'on_track' THEN 1 ELSE 0 END) as onTrack,
        SUM(CASE WHEN status = 'at_risk' THEN 1 ELSE 0 END) as atRisk,
        SUM(CASE WHEN status = 'off_track' THEN 1 ELSE 0 END) as offTrack,
        SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed,
        ROUND(AVG(progress), 1) as avgProgress
      FROM rocks
      WHERE quarter = ? AND year = ?
    `;
    
    const rocksParams = [quarter, year];
    
    if (userRole !== 'admin' && userRole !== 'chro') {
      rocksQuery += ' AND owner_id = ?';
      rocksParams.push(userId);
    }
    
    const rockStats = db.prepare(rocksQuery).get(...rocksParams);
    
    // OKR Summary
    let okrQuery = `
      SELECT 
        COUNT(*) as total,
        SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active,
        SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed,
        ROUND(AVG(progress), 1) as avgProgress
      FROM objectives
      WHERE quarter = ? AND year = ?
    `;
    
    const okrParams = [quarter, year];
    
    if (userRole !== 'admin' && userRole !== 'chro') {
      okrQuery += ' AND owner_id = ?';
      okrParams.push(userId);
    }
    
    const okrStats = db.prepare(okrQuery).get(...okrParams);
    
    // Recent Tasks
    let recentTasksQuery = `
      SELECT 
        t.id, t.title, t.status, t.progress, t.due_date, t.priority,
        t.requires_approval, t.approval_status,
        assignee.first_name as assignee_first_name,
        assignee.last_name as assignee_last_name
      FROM tasks t
      LEFT JOIN users assignee ON t.assignee_id = assignee.id
      WHERE 1=1
    `;
    
    const recentTasksParams = [];
    
    if (userRole !== 'admin' && userRole !== 'chro') {
      recentTasksQuery += ' AND (t.assignee_id = ? OR t.assigner_id = ? OR t.reviewer_id = ?)';
      recentTasksParams.push(userId, userId, userId);
    }
    
    recentTasksQuery += ' ORDER BY t.updated_at DESC LIMIT 10';
    
    const recentTasks = db.prepare(recentTasksQuery).all(...recentTasksParams);
    
    // Scorecard Health
    const scorecardHealth = db.prepare(`
      SELECT 
        c.perspective,
        COUNT(m.id) as total_metrics,
        SUM(CASE WHEN latest.value >= m.target_value * 0.9 THEN 1 ELSE 0 END) as on_track
      FROM scorecard_categories c
      JOIN scorecard_metrics m ON m.category_id = c.id
      LEFT JOIN (
        SELECT metric_id, value
        FROM scorecard_entries
        WHERE (metric_id, week_starting) IN (
          SELECT metric_id, MAX(week_starting)
          FROM scorecard_entries
          GROUP BY metric_id
        )
      ) latest ON latest.metric_id = m.id
      WHERE m.is_active = 1
      GROUP BY c.perspective
    `).all();
    
    // Focus Areas Progress
    const focusAreas = db.prepare(`
      SELECT 
        fa.id, fa.name, fa.priority,
        COUNT(DISTINCT r.id) as rocks_count,
        COUNT(DISTINCT o.id) as objectives_count,
        COUNT(DISTINCT t.id) as tasks_count,
        ROUND(AVG(r.progress), 1) as avg_rock_progress
      FROM focus_areas fa
      LEFT JOIN rocks r ON r.focus_area_id = fa.id AND r.quarter = ? AND r.year = ?
      LEFT JOIN objectives o ON o.focus_area_id = fa.id AND o.quarter = ? AND o.year = ?
      LEFT JOIN tasks t ON t.focus_area_id = fa.id
      WHERE fa.status = 'active'
      GROUP BY fa.id
      ORDER BY fa.priority
    `).all(quarter, year, quarter, year);
    
    // Notifications
    const notifications = db.prepare(`
      SELECT * FROM notifications
      WHERE user_id = ? AND is_read = 0
      ORDER BY created_at DESC
      LIMIT 10
    `).all(userId);
    
    // Upcoming Deadlines
    const upcomingDeadlines = db.prepare(`
      SELECT 
        t.id, t.title, t.due_date, t.status,
        'task' as type
      FROM tasks t
      WHERE t.due_date BETWEEN date('now') AND date('now', '+7 days')
      AND t.status NOT IN ('completed', 'cancelled')
      ${userRole !== 'admin' && userRole !== 'chro' ? 'AND (t.assignee_id = ? OR t.assigner_id = ?)' : ''}
      
      UNION ALL
      
      SELECT 
        r.id, r.title, r.due_date, r.status,
        'rock' as type
      FROM rocks r
      WHERE r.due_date BETWEEN date('now') AND date('now', '+7 days')
      AND r.status != 'completed'
      AND r.quarter = ? AND r.year = ?
      ${userRole !== 'admin' && userRole !== 'chro' ? 'AND r.owner_id = ?' : ''}
      
      ORDER BY due_date
      LIMIT 10
    `).all(
      ...(userRole !== 'admin' && userRole !== 'chro' ? [userId, userId] : []),
      quarter, year,
      ...(userRole !== 'admin' && userRole !== 'chro' ? [userId] : [])
    );
    
    res.json({
      quarter,
      year,
      taskStats,
      rockStats,
      okrStats,
      recentTasks,
      scorecardHealth,
      focusAreas,
      notifications,
      upcomingDeadlines
    });
  } catch (error) {
    console.error('Dashboard error:', error);
    res.status(500).json({ error: 'Server error' });
  }
});

// Team Performance
router.get('/team-performance', authenticate, (req, res) => {
  try {
    const db = getDatabase();
    const userId = req.user.id;
    
    const teamMembers = db.prepare(`
      SELECT 
        u.id, u.first_name, u.last_name, u.email, u.role, u.department,
        COUNT(DISTINCT t.id) as total_tasks,
        SUM(CASE WHEN t.status = 'completed' THEN 1 ELSE 0 END) as completed_tasks,
        COUNT(DISTINCT r.id) as total_rocks,
        SUM(CASE WHEN r.status = 'completed' THEN 1 ELSE 0 END) as completed_rocks
      FROM users u
      LEFT JOIN tasks t ON t.assignee_id = u.id AND t.created_at >= date('now', '-30 days')
      LEFT JOIN rocks r ON r.owner_id = u.id AND r.quarter = 'Q' || ((strftime('%m', 'now') + 2) / 3) AND r.year = strftime('%Y', 'now')
      WHERE u.reporting_manager_id = ? AND u.is_active = 1
      GROUP BY u.id
    `).all(userId);
    
    res.json({ teamMembers });
  } catch (error) {
    console.error('Team performance error:', error);
    res.status(500).json({ error: 'Server error' });
  }
});

module.exports = router;