const express = require('express');
const router = express.Router();
const db = require('../../database');

/**
 * Tasks Module — Trinity Console
 * 
 * Web interface for task management. Source of truth: PostgreSQL tasks table.
 * Complements Discord /tasks command for ChatOps workflow.
 * 
 * GET  /admin/tasks              — Task board view
 * POST /admin/tasks/update/:id   — Update task (HTMX)
 * POST /admin/tasks/create       — Create task (HTMX)
 * 
 * Chronicler #78 | April 11, 2026
 */

const PRIORITIES = ['critical', 'high', 'medium', 'low', 'wish'];
const STATUSES = ['open', 'in_progress', 'blocked', 'done', 'obsolete'];
const OWNERS = ['Michael', 'Meg', 'Holly', 'Trinity', 'unassigned'];

router.get('/', async (req, res) => {
  try {
    const { status, priority, owner } = req.query;
    let where = 'WHERE 1=1';
    const params = [];
    let p = 0;

    // Default: hide done and obsolete
    if (status) {
      p++; where += ` AND status = $${p}`; params.push(status);
    } else if (!req.query.all) {
      where += ` AND status NOT IN ('done', 'obsolete')`;
    }
    if (priority) { p++; where += ` AND priority = $${p}`; params.push(priority); }
    if (owner) { p++; where += ` AND owner = $${p}`; params.push(owner); }

    const result = await db.query(
      `SELECT * FROM tasks ${where} ORDER BY
        CASE status WHEN 'in_progress' THEN 1 WHEN 'blocked' THEN 2 WHEN 'open' THEN 3 WHEN 'done' THEN 4 WHEN 'obsolete' THEN 5 END,
        CASE priority WHEN 'critical' THEN 1 WHEN 'high' THEN 2 WHEN 'medium' THEN 3 WHEN 'low' THEN 4 WHEN 'wish' THEN 5 END,
        task_number`,
      params
    );

    // Stats
    const statsResult = await db.query(`
      SELECT
        COUNT(*) FILTER (WHERE status NOT IN ('done','obsolete')) as active,
        COUNT(*) FILTER (WHERE status = 'done') as done,
        COUNT(*) FILTER (WHERE status = 'in_progress') as in_progress,
        COUNT(*) FILTER (WHERE status = 'blocked') as blocked,
        COUNT(*) FILTER (WHERE priority IN ('critical','high') AND status NOT IN ('done','obsolete')) as high_priority
      FROM tasks
    `);

    // Session summary: tasks completed today
    const todayResult = await db.query(
      `SELECT COUNT(*) as count FROM tasks WHERE completed_at::date = CURRENT_DATE`
    );
    const completedToday = parseInt(todayResult.rows[0].count) || 0;

    // All tasks for kanban (unfiltered active tasks)
    const kanbanResult = await db.query(
      `SELECT * FROM tasks ORDER BY
        CASE priority WHEN 'critical' THEN 1 WHEN 'high' THEN 2 WHEN 'medium' THEN 3 WHEN 'low' THEN 4 WHEN 'wish' THEN 5 END,
        task_number`
    );

    res.render('admin/tasks/index', {
      title: 'Tasks',
      currentPath: '/tasks',
      tasks: result.rows,
      allTasks: kanbanResult.rows,
      stats: statsResult.rows[0],
      completedToday,
      filters: { status, priority, owner, all: req.query.all },
      priorities: PRIORITIES,
      statuses: STATUSES,
      owners: OWNERS,
      adminUser: req.user,
      layout: 'layout'
    });
  } catch (err) {
    console.error('[Tasks] Route error:', err);
    res.status(500).send('Error loading tasks');
  }
});

// POST /admin/tasks/update/:id — Quick status update (HTMX)
router.post('/update/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const { status, priority, owner } = req.body;
    const updates = [];
    const params = [];
    let p = 0;

    if (status) { p++; updates.push(`status = $${p}`); params.push(status); }
    if (priority) { p++; updates.push(`priority = $${p}`); params.push(priority); }
    if (owner) { p++; updates.push(`owner = $${p}`); params.push(owner); }

    if (status === 'done') {
      updates.push(`completed_at = NOW()`);
      const completedBy = req.user?.username || 'Trinity Console';
      p++; updates.push(`completed_by = $${p}`); params.push(completedBy);
    }

    updates.push('updated_at = NOW()');
    p++; params.push(id);

    await db.query(`UPDATE tasks SET ${updates.join(', ')} WHERE id = $${p}`, params);

    res.redirect('/admin/tasks');
  } catch (err) {
    console.error('[Tasks] Update error:', err);
    res.status(500).send('Error updating task');
  }
});

// POST /admin/tasks/create — Create new task
router.post('/create', async (req, res) => {
  try {
    const { title, description, priority, owner } = req.body;
    if (!title) return res.redirect('/admin/tasks');

    const maxResult = await db.query('SELECT COALESCE(MAX(task_number), 0) + 1 as next FROM tasks');
    const taskNumber = maxResult.rows[0].next;

    await db.query(
      `INSERT INTO tasks (task_number, title, description, priority, owner)
       VALUES ($1, $2, $3, $4, $5)`,
      [taskNumber, title, description || null, priority || 'medium', owner || 'unassigned']
    );

    console.log(`📋 [Tasks] Created #${taskNumber}: ${title} via Trinity Console`);
    res.redirect('/admin/tasks');
  } catch (err) {
    console.error('[Tasks] Create error:', err);
    res.status(500).send('Error creating task');
  }
});

module.exports = router;
