mek-tech-consulting/routes/projects.js

const express = require('express');
const router = express.Router();
const { getDb } = require('../db');

// Initialize projects table
function initProjectsTable() {
  const db = getDb();
  db.exec(`
    CREATE TABLE IF NOT EXISTS projects (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      description TEXT,
      client_id INTEGER,
      start_date TEXT,
      end_date TEXT,
      status TEXT DEFAULT 'planning',
      priority TEXT DEFAULT 'medium',
      budget REAL,
      created_at TEXT DEFAULT CURRENT_TIMESTAMP,
      updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (client_id) REFERENCES clients(id)
    )
  `);
  
  db.exec(`
    CREATE TABLE IF NOT EXISTS project_tasks (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      project_id INTEGER NOT NULL,
      title TEXT NOT NULL,
      description TEXT,
      status TEXT DEFAULT 'todo',
      priority TEXT DEFAULT 'medium',
      assignee_id INTEGER,
      start_date TEXT,
      due_date TEXT,
      progress INTEGER DEFAULT 0,
      created_at TEXT DEFAULT CURRENT_TIMESTAMP,
      updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (project_id) REFERENCES projects(id),
      FOREIGN KEY (assignee_id) REFERENCES users(id)
    )
  `);
  
  db.exec(`
    CREATE TABLE IF NOT EXISTS project_milestones (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      project_id INTEGER NOT NULL,
      name TEXT NOT NULL,
      target_date TEXT,
      status TEXT DEFAULT 'pending',
      created_at TEXT DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (project_id) REFERENCES projects(id)
    )
  `);
}

router.get('/', (req, res) => {
  initProjectsTable();
  const db = getDb();
  const tab = req.query.tab || 'kanban';
  
  const projects = db.prepare(`
    SELECT p.*, c.name as client_name,
      (SELECT COUNT(*) FROM project_tasks pt WHERE pt.project_id = p.id) as task_count,
      (SELECT COUNT(*) FROM project_tasks pt WHERE pt.project_id = p.id AND pt.status = 'done') as completed_tasks
    FROM projects p
    LEFT JOIN clients c ON c.id = p.client_id
    ORDER BY p.updated_at DESC
  `).all();
  
  const projectTasks = db.prepare(`
    SELECT pt.*, p.name as project_name, u.username as assignee_name
    FROM project_tasks pt
    LEFT JOIN projects p ON p.id = pt.project_id
    LEFT JOIN users u ON u.id = pt.assignee_id
    ORDER BY pt.due_date ASC
  `).all();
  
  const milestones = db.prepare(`
    SELECT m.*, p.name as project_name
    FROM project_milestones m
    LEFT JOIN projects p ON p.id = m.project_id
    ORDER BY m.target_date ASC
  `).all();
  
  const clients = db.prepare('SELECT id, name FROM clients ORDER BY name').all();
  const users = db.prepare('SELECT id, username FROM users ORDER BY username').all();
  
  res.render('projects', {
    tab, projects, projectTasks, milestones, clients, users
  });
});

router.post('/new', (req, res) => {
  initProjectsTable();
  const db = getDb();
  const { name, description, client_id, start_date, end_date, priority, budget } = req.body;
  
  db.prepare(`
    INSERT INTO projects (name, description, client_id, start_date, end_date, priority, budget)
    VALUES (?, ?, ?, ?, ?, ?, ?)
  `).run(name, description, client_id, start_date, end_date, priority, budget);
  
  res.redirect('/projects');
});

router.post('/tasks/new', (req, res) => {
  initProjectsTable();
  const db = getDb();
  const { project_id, title, description, priority, assignee_id, start_date, due_date } = req.body;
  
  db.prepare(`
    INSERT INTO project_tasks (project_id, title, description, priority, assignee_id, start_date, due_date)
    VALUES (?, ?, ?, ?, ?, ?, ?)
  `).run(project_id, title, description, priority, assignee_id, start_date, due_date);
  
  res.redirect('/projects');
});

router.post('/tasks/:id/status', (req, res) => {
  initProjectsTable();
  const db = getDb();
  const { status } = req.body;
  const progress = status === 'done' ? 100 : status === 'in_progress' ? 50 : 0;
  
  db.prepare(`
    UPDATE project_tasks SET status = ?, progress = ?, updated_at = CURRENT_TIMESTAMP
    WHERE id = ?
  `).run(status, progress, req.params.id);
  
  res.json({ success: true });
});

router.post('/tasks/:id/progress', (req, res) => {
  initProjectsTable();
  const db = getDb();
  const { progress } = req.body;
  
  db.prepare(`
    UPDATE project_tasks SET progress = ?, updated_at = CURRENT_TIMESTAMP
    WHERE id = ?
  `).run(progress, req.params.id);
  
  res.json({ success: true });
});

router.get('/:id', (req, res) => {
  initProjectsTable();
  const db = getDb();
  
  const project = db.prepare(`
    SELECT p.*, c.name as client_name
    FROM projects p
    LEFT JOIN clients c ON c.id = p.client_id
    WHERE p.id = ?
  `).get(req.params.id);
  
  if (!project) return res.redirect('/projects');

  const tasks = db.prepare(`
    SELECT pt.*, u.username as assignee_name
    FROM project_tasks pt
    LEFT JOIN users u ON u.id = pt.assignee_id
    WHERE pt.project_id = ?
    ORDER BY pt.due_date ASC
  `).all(req.params.id);
  
  const milestones = db.prepare(`
    SELECT * FROM project_milestones
    WHERE project_id = ?
    ORDER BY target_date ASC
  `).all(req.params.id);
  
  res.render('project-detail', { project, tasks, milestones });
});

module.exports = router;