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

const router = express.Router();

// Get all OKRs with filters
router.get('/', authenticate, (req, res) => {
  try {
    const db = getDatabase();
    const { quarter, year, owner, status, alignmentType, parentId } = req.query;
    
    let query = `
      SELECT 
        o.*,
        u.first_name as owner_first_name,
        u.last_name as owner_last_name,
        fa.name as focus_area_name,
        po.title as parent_objective_title
      FROM objectives o
      LEFT JOIN users u ON o.owner_id = u.id
      LEFT JOIN focus_areas fa ON o.focus_area_id = fa.id
      LEFT JOIN objectives po ON o.parent_objective_id = po.id
      WHERE 1=1
    `;
    
    const params = [];
    
    if (quarter) {
      query += ' AND o.quarter = ?';
      params.push(quarter);
    }
    
    if (year) {
      query += ' AND o.year = ?';
      params.push(year);
    }
    
    if (owner) {
      query += ' AND o.owner_id = ?';
      params.push(owner);
    }
    
    if (status) {
      query += ' AND o.status = ?';
      params.push(status);
    }
    
    if (alignmentType) {
      query += ' AND o.alignment_type = ?';
      params.push(alignmentType);
    }
    
    if (parentId) {
      query += ' AND o.parent_objective_id = ?';
      params.push(parentId);
    }
    
    query += ' ORDER BY o.created_at DESC';
    
    const objectives = db.prepare(query).all(...params);
    
    const okrsWithDetails = objectives.map(obj => {
      const keyResults = db.prepare(`
        SELECT * FROM key_results WHERE objective_id = ? ORDER BY id
      `).all(obj.id);
      
      const childObjectives = db.prepare(`
        SELECT o.*, u.first_name, u.last_name
        FROM objectives o
        LEFT JOIN users u ON o.owner_id = u.id
        WHERE o.parent_objective_id = ?
      `).all(obj.id);
      
      return { ...obj, keyResults, childObjectives };
    });
    
    res.json({ objectives: okrsWithDetails });
  } catch (error) {
    console.error('Error fetching OKRs:', error);
    res.status(500).json({ error: 'Server error' });
  }
});

// Get single OKR with full hierarchy
router.get('/:id', authenticate, (req, res) => {
  try {
    const db = getDatabase();
    const { id } = req.params;
    
    const objective = db.prepare(`
      SELECT 
        o.*,
        u.first_name as owner_first_name,
        u.last_name as owner_last_name,
        fa.name as focus_area_name
      FROM objectives o
      LEFT JOIN users u ON o.owner_id = u.id
      LEFT JOIN focus_areas fa ON o.focus_area_id = fa.id
      WHERE o.id = ?
    `).get(id);
    
    if (!objective) {
      return res.status(404).json({ error: 'Objective not found' });
    }
    
    const keyResults = db.prepare(`
      SELECT * FROM key_results WHERE objective_id = ? ORDER BY id
    `).all(id);
    
    let parentChain = [];
    let currentParentId = objective.parent_objective_id;
    while (currentParentId) {
      const parent = db.prepare('SELECT * FROM objectives WHERE id = ?').get(currentParentId);
      if (parent) {
        parentChain.unshift(parent);
        currentParentId = parent.parent_objective_id;
      } else {
        break;
      }
    }
    
    const childObjectives = db.prepare(`
      SELECT o.*, u.first_name, u.last_name
      FROM objectives o
      LEFT JOIN users u ON o.owner_id = u.id
      WHERE o.parent_objective_id = ?
    `).all(id);
    
    res.json({ 
      objective, 
      keyResults, 
      parentChain,
      childObjectives 
    });
  } catch (error) {
    console.error('Error fetching OKR:', error);
    res.status(500).json({ error: 'Server error' });
  }
});

// Create objective
router.post('/', authenticate, [
  body('title').trim().notEmpty(),
  body('ownerId').isInt(),
  body('quarter').matches(/^Q[1-4]$/),
  body('year').isInt({ min: 2024, max: 2030 }),
  body('alignmentType').isIn(['company', 'department', 'individual'])
], auditLog('create', 'objective'), (req, res) => {
  try {
    const errors = validationResult(req);
    if (!errors.isEmpty()) {
      return res.status(400).json({ errors: errors.array() });
    }

    const db = getDatabase();
    const {
      title,
      description,
      ownerId,
      focusAreaId,
      quarter,
      year,
      alignmentType,
      parentObjectiveId,
      keyResults = []
    } = req.body;
    
    if (parentObjectiveId) {
      const parent = db.prepare('SELECT * FROM objectives WHERE id = ?').get(parentObjectiveId);
      if (!parent) {
        return res.status(400).json({ error: 'Parent objective not found' });
      }
    }
    
    const result = db.prepare(`
      INSERT INTO objectives (
        title, description, owner_id, focus_area_id,
        quarter, year, alignment_type, parent_objective_id
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    `).run(
      title, description, ownerId, focusAreaId,
      quarter, year, alignmentType, parentObjectiveId
    );
    
    const objectiveId = result.lastInsertRowid;
    
    if (keyResults.length > 0) {
      const insertKR = db.prepare(`
        INSERT INTO key_results (
          objective_id, title, description, target_value, 
          current_value, unit, start_value, due_date
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
      `);
      
      keyResults.forEach(kr => {
        insertKR.run(
          objectiveId,
          kr.title,
          kr.description,
          kr.targetValue,
          kr.startValue || 0,
          kr.unit,
          kr.startValue || 0,
          kr.dueDate
        );
      });
    }
    
    res.status(201).json({
      id: objectiveId,
      message: 'OKR created successfully'
    });
  } catch (error) {
    console.error('Error creating OKR:', error);
    res.status(500).json({ error: 'Server error' });
  }
});

// Update key result
router.put('/key-result/:id', authenticate, [
  body('currentValue').isNumeric(),
  body('confidenceLevel').optional().isInt({ min: 1, max: 10 })
], (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 { currentValue, confidenceLevel, notes } = req.body;
    
    const kr = db.prepare('SELECT * FROM key_results WHERE id = ?').get(id);
    if (!kr) {
      return res.status(404).json({ error: 'Key result not found' });
    }
    
    const progress = Math.min(100, Math.round(
      ((currentValue - kr.start_value) / (kr.target_value - kr.start_value)) * 100
    ));
    
    let status = 'on_track';
    if (progress >= 100) {
      status = 'completed';
    } else if (progress < 30) {
      status = 'at_risk';
    }
    
    db.prepare(`
      UPDATE key_results 
      SET current_value = ?, 
          confidence_level = COALESCE(?, confidence_level),
          status = ?
      WHERE id = ?
    `).run(currentValue, confidenceLevel, status, id);
    
    const objectiveId = kr.objective_id;
    const allKRs = db.prepare('SELECT * FROM key_results WHERE objective_id = ?').all(objectiveId);
    const avgProgress = Math.round(allKRs.reduce((sum, k) => {
      const krProgress = Math.min(100, Math.round(
        ((k.current_value - k.start_value) / (k.target_value - k.start_value)) * 100
      ));
      return sum + krProgress;
    }, 0) / allKRs.length);
    
    db.prepare('UPDATE objectives SET progress = ? WHERE id = ?').run(avgProgress, objectiveId);
    
    res.json({ 
      message: 'Key result updated',
      progress: avgProgress
    });
  } catch (error) {
    console.error('Error updating key result:', error);
    res.status(500).json({ error: 'Server error' });
  }
});

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

module.exports = router;