const express = require('express');
const router = express.Router();
const mysql = require('mysql2/promise');

async function getDbConnection() {
  return await mysql.createConnection({
    host: process.env.DB_HOST || 'localhost',
    user: process.env.DB_USER || 'root',
    password: process.env.DB_PASSWORD || '',
    database: process.env.DB_NAME || 'lssoft_db',
  });
}

// 1. Get all tasks
router.get('/', async (req, res) => {
  try {
    const conn = await getDbConnection();
    // Fetch all tasks
    const [tasks] = await conn.query('SELECT * FROM tasks ORDER BY id DESC');
    
    // Fetch all assignments linked with user info
    const [assignments] = await conn.query(`
      SELECT ta.task_id, a.id as user_id, a.name, a.avatar 
      FROM task_assignments ta 
      JOIN admin a ON ta.user_id = a.id
    `);
    await conn.end();

    // Map assignments to tasks
    const tasksWithUsers = tasks.map(task => {
      task.assigned_users = assignments
        .filter(a => a.task_id === task.id)
        .map(a => ({ id: a.user_id, name: a.name, avatar: a.avatar }));
      return task;
    });

    res.json({ success: true, data: tasksWithUsers });
  } catch (error) {
    console.error('Error fetching tasks:', error);
    res.status(500).json({ success: false, message: 'Server Error', error: error.message });
  }
});

// 2. Create task
router.post('/', async (req, res) => {
  try {
    const { title, description, status, due_date, assigned_users } = req.body;
    if (!title) {
      return res.status(400).json({ success: false, message: 'Title is required' });
    }
    const conn = await getDbConnection();
    
    await conn.beginTransaction();
    const [result] = await conn.query(
      'INSERT INTO tasks (title, description, status, due_date) VALUES (?, ?, ?, ?)',
      [title, description || '', status || 'Pending', due_date || null]
    );
    const taskId = result.insertId;

    if (Array.isArray(assigned_users) && assigned_users.length > 0) {
      const values = assigned_users.map(userId => [taskId, userId]);
      await conn.query('INSERT INTO task_assignments (task_id, user_id) VALUES ?', [values]);
    }
    
    await conn.commit();
    
    // Fetch the created task
    const [rows] = await conn.query('SELECT * FROM tasks WHERE id = ?', [taskId]);
    await conn.end();
    
    res.json({ success: true, data: rows[0], message: 'Task created successfully' });
  } catch (error) {
    console.error('Error creating task:', error);
    res.status(500).json({ success: false, message: 'Server Error', error: error.message });
  }
});

// 3. Update task
router.put('/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const { title, description, status, due_date, assigned_users } = req.body;
    
    const conn = await getDbConnection();
    await conn.beginTransaction();

    await conn.query(
      'UPDATE tasks SET title = ?, description = ?, status = ?, due_date = ? WHERE id = ?',
      [title, description || '', status || 'Pending', due_date || null, id]
    );

    // Update assignments: clear old ones and insert new ones
    await conn.query('DELETE FROM task_assignments WHERE task_id = ?', [id]);
    
    if (Array.isArray(assigned_users) && assigned_users.length > 0) {
      const values = assigned_users.map(userId => [id, userId]);
      await conn.query('INSERT INTO task_assignments (task_id, user_id) VALUES ?', [values]);
    }

    await conn.commit();
    const [rows] = await conn.query('SELECT * FROM tasks WHERE id = ?', [id]);
    await conn.end();
    
    if (rows.length === 0) {
      return res.status(404).json({ success: false, message: 'Task not found' });
    }
    res.json({ success: true, data: rows[0], message: 'Task updated successfully' });
  } catch (error) {
    console.error('Error updating task:', error);
    res.status(500).json({ success: false, message: 'Server Error', error: error.message });
  }
});

// 4. Delete task
router.delete('/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const conn = await getDbConnection();
    
    await conn.beginTransaction();
    await conn.query('DELETE FROM task_assignments WHERE task_id = ?', [id]);
    const [result] = await conn.query('DELETE FROM tasks WHERE id = ?', [id]);
    await conn.commit();
    
    await conn.end();
    
    if (result.affectedRows === 0) {
      return res.status(404).json({ success: false, message: 'Task not found' });
    }
    res.json({ success: true, message: 'Task deleted successfully' });
  } catch (error) {
    console.error('Error deleting task:', error);
    res.status(500).json({ success: false, message: 'Server Error', error: error.message });
  }
});

// 5. Get task by ID (with assignments and logs)
router.get('/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const conn = await getDbConnection();
    
    // Fetch Task
    const [tasks] = await conn.query('SELECT * FROM tasks WHERE id = ?', [id]);
    if (tasks.length === 0) {
      await conn.end();
      return res.status(404).json({ success: false, message: 'Task not found' });
    }
    const task = tasks[0];

    // Fetch Assignments
    const [assignments] = await conn.query(`
      SELECT a.id, a.name, a.avatar 
      FROM task_assignments ta 
      JOIN admin a ON ta.user_id = a.id
      WHERE ta.task_id = ?
    `, [id]);
    task.assigned_users = assignments;

    // Fetch Logs
    const [logs] = await conn.query(`
      SELECT tl.id, tl.type, tl.message, tl.created_at, a.name as user_name, a.avatar as user_avatar
      FROM task_logs tl
      LEFT JOIN admin a ON tl.user_id = a.id
      WHERE tl.task_id = ?
      ORDER BY tl.created_at DESC
    `, [id]);
    task.logs = logs;

    await conn.end();
    res.json({ success: true, data: task });
  } catch (error) {
    console.error('Error fetching task details:', error);
    res.status(500).json({ success: false, message: 'Server Error', error: error.message });
  }
});

// 6. Add task log
router.post('/:id/logs', async (req, res) => {
  try {
    const { id } = req.params;
    const { user_id, type, message, status } = req.body;
    
    if (!user_id || !message) {
      return res.status(400).json({ success: false, message: 'user_id and message are required' });
    }

    const conn = await getDbConnection();
    await conn.query(
      'INSERT INTO task_logs (task_id, user_id, type, message) VALUES (?, ?, ?, ?)',
      [id, user_id, type || 'note', message]
    );

    // If status is provided, update the task's status
    if (status) {
      await conn.query('UPDATE tasks SET status = ? WHERE id = ?', [status, id]);
    }

    await conn.end();
    
    res.status(201).json({ success: true, message: 'Log added successfully' });
  } catch (error) {
    console.error('Error adding task log:', error);
    res.status(500).json({ success: false, message: 'Server Error', error: error.message });
  }
});

module.exports = router;
