const express = require('express');
const { body, validationResult } = require('express-validator');
const { getDatabase } = require('../config/database');
const { authenticate, authorize, auditLog } = require('../middleware/auth');
const multer = require('multer');
const path = require('path');
const fs = require('fs');
const cron = require('node-cron');

const router = express.Router();

// Configure multer for file uploads
const storage = multer.diskStorage({
  destination: (req, file, cb) => {
    const uploadDir = path.join(__dirname, '../uploads/tasks');
    if (!fs.existsSync(uploadDir)) {
      fs.mkdirSync(uploadDir, { recursive: true });
    }
    cb(null, uploadDir);
  },
  filename: (req, file, cb) => {
    const uniqueSuffix = Date.now() + '-' + Math.round(Math.random() * 1E9);
    cb(null, 'task-' + uniqueSuffix + path.extname(file.originalname));
  }
});

const upload = multer({ 
  storage,
  limits: { fileSize: 10 * 1024 * 1024 },
  fileFilter: (req, file, cb) => {
    const allowedTypes = ['.pdf', '.doc', '.docx', '.xls', '.xlsx', '.png', '.jpg', '.jpeg', '.gif', '.zip'];
    const ext = path.extname(file.originalname).toLowerCase();
    if (allowedTypes.includes(ext)) {
      cb(null, true);
    } else {
      cb(new Error('Invalid file type'));
    }
  }
});

// Get all tasks with filters
router.get('/', authenticate, (req, res) => {
  try {
    const db = getDatabase();
    const { status, assignee, quarter, rockId, keyResultId, focusAreaId, priority, myTasks, pendingApproval, overdue } = req.query;
    
    let query = `
      SELECT 
        t.*,
        assignee.first_name as assignee_first_name,
        assignee.last_name as assignee_last_name,
        assigner.first_name as assigner_first_name,
        assigner.last_name as assigner_last_name,
        reviewer.first_name as reviewer_first_name,
        reviewer.last_name as reviewer_last_name,
        r.title as rock_title,
        kr.title as key_result_title,
        fa.name as focus_area_name
      FROM tasks t
      LEFT JOIN users assignee ON t.assignee_id = assignee.id
      LEFT JOIN users assigner ON t.assigner_id = assigner.id
      LEFT JOIN users reviewer ON t.reviewer_id = reviewer.id
      LEFT JOIN rocks r ON t.rock_id = r.id
      LEFT JOIN key_results kr ON t.key_result_id = kr.id
      LEFT JOIN focus_areas fa ON t.focus_area_id = fa.id
      WHERE 1=1
    `;
    
    const params = [];
    
    if (status) {
      query += ' AND t.status = ?';
      params.push(status);
    }
    
    if (assignee) {
      query += ' AND t.assignee_id = ?';
      params.push(assignee);
    }
    
    if (quarter) {
      query += ' AND t.quarter = ?';
      params.push(quarter);
    }
    
    if (rockId) {
      query += ' AND t.rock_id = ?';
      params.push(rockId);
    }
    
    if (keyResultId) {
      query += ' AND t.key_result_id = ?';
      params.push(keyResultId);
    }
    
    if (focusAreaId) {
      query += ' AND t.focus_area_id = ?';
      params.push(focusAreaId);
    }
    
    if (priority) {
      query += ' AND t.priority = ?';
      params.push(priority);
    }
    
    if (myTasks === 'true') {
      query += ' AND (t.assignee_id = ? OR t.assigner_id = ? OR t.reviewer_id = ?)';
      params.push(req.user.id, req.user.id, req.user.id);
    }
    
    if (pendingApproval === 'true') {
      query += ' AND t.requires_approval = 1 AND t.approval_status = 'pending'';
      if (req.user.role !== 'admin' && req.user.role !== 'chro') {
        query += ' AND t.reviewer_id = ?';
        params.push(req.user.id);
      }
    }
    
    if (overdue === 'true') {
      query += ' AND t.due_date < date('now') AND t.status NOT IN ('completed', 'cancelled')';
    }
    
    query += ' ORDER BY t.created_at DESC';
    
    const tasks = db.prepare(query).all(...params);
    
    res.json({ tasks });
  } catch (error) {
    console.error('Error fetching tasks:', error);
    res.status(500).json({ error: 'Server error' });
  }
});

// Get single task with activities
router.get('/:id', authenticate, (req, res) => {
  try {
    const db = getDatabase();
    const { id } = req.params;
    
    const task = db.prepare(`
      SELECT 
        t.*,
        assignee.first_name as assignee_first_name,
        assignee.last_name as assignee_last_name,
        assigner.first_name as assigner_first_name,
        assigner.last_name as assigner_last_name,
        reviewer.first_name as reviewer_first_name,
        reviewer.last_name as reviewer_last_name,
        r.title as rock_title,
        kr.title as key_result_title,
        fa.name as focus_area_name
      FROM tasks t
      LEFT JOIN users assignee ON t.assignee_id = assignee.id
      LEFT JOIN users assigner ON t.assigner_id = assigner.id
      LEFT JOIN users reviewer ON t.reviewer_id = reviewer.id
      LEFT JOIN rocks r ON t.rock_id = r.id
      LEFT JOIN key_results kr ON t.key_result_id = kr.id
      LEFT JOIN focus_areas fa ON t.focus_area_id = fa.id
      WHERE t.id = ?
    `).get(id);
    
    if (!task) {
      return res.status(404).json({ error: 'Task not found' });
    }
    
    const activities = db.prepare(`
      SELECT ta.*, u.first_name, u.last_name
      FROM task_activities ta
      LEFT JOIN users u ON ta.user_id = u.id
      WHERE ta.task_id = ?
      ORDER BY ta.created_at DESC
    `).all(id);
    
    res.json({ task, activities });
  } catch (error) {
    console.error('Error fetching task:', error);
    res.status(500).json({ error: 'Server error' });
  }
});

// Create task
router.post('/', authenticate, [
  body('title').trim().notEmpty(),
  body('assigneeId').isInt(),
  body('dueDate').isISO8601()
], auditLog('create', 'task'), (req, res) => {
  try {
    const errors = validationResult(req);
    if (!errors.isEmpty()) {
      return res.status(400).json({ errors: errors.array() });
    }

    const db = getDatabase();
    const {
      title,
      description,
      taskType = 'task',
      assigneeId,
      reviewerId,
      rockId,
      keyResultId,
      focusAreaId,
      startDate,
      dueDate,
      priority = 'medium',
      requiresApproval = false,
      quarter,
      year
    } = req.body;
    
    const durationDays = startDate && dueDate 
      ? Math.ceil((new Date(dueDate) - new Date(startDate)) / (1000 * 60 * 60 * 24))
      : null;
    
    const result = db.prepare(`
      INSERT INTO tasks (
        title, description, task_type, status, priority,
        assignee_id, assigner_id, reviewer_id,
        rock_id, key_result_id, focus_area_id,
        start_date, due_date, duration_days,
        requires_approval, approval_status,
        quarter, year
      ) VALUES (?, ?, ?, 'not_started', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'not_required', ?, ?)
    `).run(
      title, description, taskType, priority,
      assigneeId, req.user.id, reviewerId,
      rockId, keyResultId, focusAreaId,
      startDate, dueDate, durationDays,
      requiresApproval ? 1 : 0,
      quarter, year
    );
    
    const taskId = result.lastInsertRowid;
    
    db.prepare(`
      INSERT INTO task_activities (task_id, user_id, activity_type, description)
      VALUES (?, ?, 'status_change', ?)
    `).run(taskId, req.user.id, 'Task created');
    
    db.prepare(`
      INSERT INTO notifications (user_id, type, title, message, related_entity_type, related_entity_id)
      VALUES (?, 'task_assigned', ?, ?, 'task', ?)
    `).run(
      assigneeId,
      'New Task Assigned',
      `You have been assigned: ${title}`,
      taskId
    );
    
    res.status(201).json({ 
      id: taskId,
      message: 'Task created successfully'
    });
  } catch (error) {
    console.error('Error creating task:', error);
    res.status(500).json({ error: 'Server error' });
  }
});

// Update task progress
router.put('/:id/progress', authenticate, [
  body('progress').isInt({ min: 0, max: 100 }),
  body('status').optional().isIn(['not_started', 'in_progress', 'completed', 'on_hold'])
], (req, res) => {
  try {
    const errors = validationResult(req);
    if (!errors.isEmpty()) {
      return res.status(400).json({ errors: errors.array() });
    }

    const db = getDatabase();
    const { id } = req.params;
    const { progress, status, notes } = req.body;
    
    const task = db.prepare('SELECT * FROM tasks WHERE id = ?').get(id);
    if (!task) {
      return res.status(404).json({ error: 'Task not found' });
    }
    
    if (task.assignee_id !== req.user.id && task.assigner_id !== req.user.id && 
        req.user.role !== 'admin' && req.user.role !== 'chro') {
      return res.status(403).json({ error: 'Not authorized to update this task' });
    }
    
    let newStatus = status;
    let completedDate = null;
    
    if (progress === 100) {
      newStatus = 'completed';
      completedDate = new Date().toISOString();
    } else if (progress > 0 && !status) {
      newStatus = 'in_progress';
    }
    
    if (task.requires_approval && progress === 100 && task.approval_status !== 'approved') {
      newStatus = 'in_progress';
      
      db.prepare(`
        UPDATE tasks 
        SET approval_status = 'pending', progress = ?
        WHERE id = ?
      `).run(progress, id);
      
      if (task.reviewer_id) {
        db.prepare(`
          INSERT INTO notifications (user_id, type, title, message, related_entity_type, related_entity_id)
          VALUES (?, 'approval_required', ?, ?, 'task', ?)
        `).run(
          task.reviewer_id,
          'Approval Required',
          `Task \"${task.title}\" is ready for your approval`,
          id
        );
      }
    } else {
      db.prepare(`
        UPDATE tasks 
        SET progress = ?, 
            status = COALESCE(?, status),
            completed_date = ?,
            updated_at = CURRENT_TIMESTAMP
        WHERE id = ?
      `).run(progress, newStatus, completedDate, id);
    }
    
    db.prepare(`
      INSERT INTO task_activities (task_id, user_id, activity_type, description, old_value, new_value)
      VALUES (?, ?, 'progress_update', ?, ?, ?)
    `).run(id, req.user.id, notes || 'Progress updated', task.progress.toString(), progress.toString());
    
    res.json({ message: 'Progress updated successfully' });
  } catch (error) {
    console.error('Error updating progress:', error);
    res.status(500).json({ error: 'Server error' });
  }
});

// Upload proof of work
router.post('/:id/upload', authenticate, upload.single('file'), (req, res) => {
  try {
    const db = getDatabase();
    const { id } = req.params;
    const { description } = req.body;
    
    const task = db.prepare('SELECT * FROM tasks WHERE id = ?').get(id);
    if (!task) {
      return res.status(404).json({ error: 'Task not found' });
    }
    
    if (!req.file) {
      return res.status(400).json({ error: 'No file uploaded' });
    }
    
    const fileUrl = `/uploads/tasks/${req.file.filename}`;
    
    db.prepare(`
      INSERT INTO task_activities (task_id, user_id, activity_type, description, file_url, file_name, file_type)
      VALUES (?, ?, 'file_upload', ?, ?, ?, ?)
    `).run(
      id, 
      req.user.id, 
      description || 'File uploaded',
      fileUrl,
      req.file.originalname,
      req.file.mimetype
    );
    
    res.json({ 
      message: 'File uploaded successfully',
      fileUrl,
      fileName: req.file.originalname
    });
  } catch (error) {
    console.error('Error uploading file:', error);
    res.status(500).json({ error: 'Server error' });
  }
});

// Approve or reject task
router.put('/:id/approve', authenticate, [
  body('action').isIn(['approve', 'reject']),
  body('notes').optional().trim()
], (req, res) => {
  try {
    const errors = validationResult(req);
    if (!errors.isEmpty()) {
      return res.status(400).json({ errors: errors.array() });
    }

    const db = getDatabase();
    const { id } = req.params;
    const { action, notes } = req.body;
    
    const task = db.prepare('SELECT * FROM tasks WHERE id = ?').get(id);
    if (!task) {
      return res.status(404).json({ error: 'Task not found' });
    }
    
    if (task.reviewer_id !== req.user.id && req.user.role !== 'admin' && req.user.role !== 'chro') {
      return res.status(403).json({ error: 'Not authorized to approve this task' });
    }
    
    const newStatus = action === 'approve' ? 'approved' : 'rejected';
    const taskStatus = action === 'approve' ? 'completed' : 'in_progress';
    const completedDate = action === 'approve' ? new Date().toISOString() : null;
    
    db.prepare(`
      UPDATE tasks 
      SET approval_status = ?,
          status = ?,
          approved_by = ?,
          approved_at = CURRENT_TIMESTAMP,
          approval_notes = ?,
          completed_date = ?,
          updated_at = CURRENT_TIMESTAMP
      WHERE id = ?
    `).run(newStatus, taskStatus, req.user.id, notes, completedDate, id);
    
    db.prepare(`
      INSERT INTO task_activities (task_id, user_id, activity_type, description)
      VALUES (?, ?, 'approval_action', ?)
    `).run(id, req.user.id, `Task ${action}ed${notes ? ': ' + notes : ''}`);
    
    db.prepare(`
      INSERT INTO notifications (user_id, type, title, message, related_entity_type, related_entity_id)
      VALUES (?, 'task_approved', ?, ?, 'task', ?)
    `).run(
      task.assignee_id,
      `Task ${action === 'approve' ? 'Approved' : 'Rejected'}`,
      `Your task \"${task.title}\" has been ${action}ed${notes ? ': ' + notes : ''}`,
      id
    );
    
    res.json({ message: `Task ${action}ed successfully` });
  } catch (error) {
    console.error('Error approving task:', error);
    res.status(500).json({ error: 'Server error' });
  }
});

// Delete task
router.delete('/:id', authenticate, authorize(['admin', 'chro']), auditLog('delete', 'task'), (req, res) => {
  try {
    const db = getDatabase();
    const { id } = req.params;
    
    const task = db.prepare('SELECT * FROM tasks WHERE id = ?').get(id);
    if (!task) {
      return res.status(404).json({ error: 'Task not found' });
    }
    
    db.prepare('DELETE FROM tasks WHERE id = ?').run(id);
    
    res.json({ message: 'Task deleted successfully' });
  } catch (error) {
    console.error('Error deleting task:', error);
    res.status(500).json({ error: 'Server error' });
  }
});

// Get task statistics
router.get('/stats/summary', authenticate, (req, res) => {
  try {
    const db = getDatabase();
    const { quarter, year } = req.query;
    
    let whereClause = '';
    const params = [];
    
    if (quarter && year) {
      whereClause = 'WHERE quarter = ? AND year = ?';
      params.push(quarter, year);
    }
    
    const stats = db.prepare(`
      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 status = 'cancelled' THEN 1 ELSE 0 END) as cancelled,
        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
      ${whereClause}
    `).get(...params);
    
    res.json({ stats });
  } catch (error) {
    console.error('Error fetching stats:', error);
    res.status(500).json({ error: 'Server error' });
  }
});

// Scheduled job: Send overdue task reminders (runs daily at 9 AM)
cron.schedule('0 9 * * *', () => {
  console.log('Running overdue task reminder check...');
  
  try {
    const db = getDatabase();
    
    const overdueTasks = db.prepare(`
      SELECT t.*, u.email, u.first_name
      FROM tasks t
      JOIN users u ON t.assignee_id = u.id
      WHERE t.due_date < date('now')
      AND t.due_date >= date('now', '-1 day')
      AND t.status NOT IN ('completed', 'cancelled')
      AND t.reminder_sent = 0
    `).all();
    
    overdueTasks.forEach(task => {
      db.prepare(`
        INSERT INTO notifications (user_id, type, title, message, related_entity_type, related_entity_id)
        VALUES (?, 'task_overdue', ?, ?, 'task', ?)
      `).run(
        task.assignee_id,
        'Task Overdue',
        `Your task \"${task.title}\" is overdue. Please update or complete it.`,
        task.id
      );
      
      db.prepare('UPDATE tasks SET reminder_sent = 1 WHERE id = ?').run(task.id);
    });
    
    console.log(`Sent ${overdueTasks.length} overdue reminders`);
  } catch (error) {
    console.error('Error in reminder cron job:', error);
  }
});

module.exports = router;