const express = require('express');
const router = express.Router();
const path = require('path');
const fs = require('fs');
const { getDb, getSettings } = require('../db');
const { generateAdvice, syncAdviceToDb, getAdviceActionUrl } = require('../advice');
const { getConsultingToolkit } = require('../lib/consulting-hub');
const { getServicesStatus } = require('../lib/services');

router.get('/', async (req, res) => {
  const db = getDb();
  const tab = req.query.tab || 'overview';

  const clients = db.prepare(`
    SELECT c.*,
      (SELECT COUNT(*) FROM engagements e WHERE e.client_id = c.id) as engagement_count,
      (SELECT COUNT(*) FROM engagements e WHERE e.client_id = c.id AND e.status = 'in_progress') as active_engagements,
      (SELECT MAX(a.created_at) FROM assessments a WHERE a.client_id = c.id) as last_assessment
    FROM clients c ORDER BY c.updated_at DESC
  `).all();

  const stats = {
    total: clients.length,
    active: clients.filter(c => c.status === 'active').length,
    leads: clients.filter(c => c.status === 'lead').length,
    paused: clients.filter(c => c.status === 'paused').length,
    archived: clients.filter(c => c.status === 'archived').length,
    activeEngagements: clients.reduce((s, c) => s + c.active_engagements, 0),
    totalEngagements: clients.reduce((s, c) => s + c.engagement_count, 0)
  };

  const totalDiagrams = db.prepare('SELECT COUNT(*) as cnt FROM data_diagrams').get().cnt;
  const totalRacks = db.prepare('SELECT COUNT(*) as cnt FROM racks').get().cnt;
  const totalRackDevices = db.prepare('SELECT COUNT(*) as cnt FROM rack_devices').get().cnt;
  const totalNetworkDevices = db.prepare('SELECT COUNT(*) as cnt FROM network_devices').get().cnt;

  let datacenterCount = 0;
  let marketCompaniesCount = 0;
  try {
    datacenterCount = db.prepare('SELECT COUNT(*) as cnt FROM datacenters').get().cnt;
    marketCompaniesCount = db.prepare('SELECT COUNT(*) as cnt FROM companies').get().cnt;
  } catch (e) { /* tables may not exist yet */ }

  let qualityFileCount = 0;
  try {
    const uploadDir = path.join(__dirname, '..', 'data', 'uploads');
    if (fs.existsSync(uploadDir)) {
      qualityFileCount = fs.readdirSync(uploadDir).filter(f => f !== '.gitkeep').length;
    }
  } catch (e) { /* ignore */ }

  const overdueTasks = db.prepare('SELECT COUNT(*) as cnt FROM tasks WHERE status IN (\'todo\',\'in_progress\') AND due_date < date(\'now\')').get().cnt;
  const totalTasks = db.prepare('SELECT COUNT(*) as cnt FROM tasks').get().cnt;
  const completedTasks = db.prepare('SELECT COUNT(*) as cnt FROM tasks WHERE status = \'done\'').get().cnt;
  const assessmentsCount = db.prepare('SELECT COUNT(*) as cnt FROM assessments').get().cnt;

  const recentEngagements = db.prepare(`
    SELECT e.*, c.name as client_name FROM engagements e JOIN clients c ON c.id = e.client_id
    WHERE e.status = 'in_progress' ORDER BY e.updated_at DESC LIMIT 8
  `).all();

  const allEngagements = db.prepare(`
    SELECT e.*, c.name as client_name FROM engagements e JOIN clients c ON c.id = e.client_id ORDER BY e.updated_at DESC
  `).all();

  const recentAssessments = db.prepare(`
    SELECT a.id, a.created_at, c.name as client_name FROM assessments a JOIN clients c ON c.id = a.client_id ORDER BY a.created_at DESC LIMIT 5
  `).all();

  const engagementByType = db.prepare('SELECT type, COUNT(*) as cnt FROM engagements GROUP BY type ORDER BY cnt DESC').all();
  const engagementByStatus = db.prepare('SELECT status, COUNT(*) as cnt FROM engagements GROUP BY status ORDER BY cnt DESC').all();
  const clientsByIndustry = db.prepare("SELECT industry, COUNT(*) as cnt FROM clients WHERE industry IS NOT NULL AND industry != '' GROUP BY industry ORDER BY cnt DESC LIMIT 6").all();

  for (const client of clients) {
    const adviceItems = generateAdvice(client.id);
    if (adviceItems.length > 0) syncAdviceToDb(client.id, adviceItems);
  }

  const activeAdvice = db.prepare(`
    SELECT a.*, c.name as client_name
    FROM advice_log a
    JOIN clients c ON c.id = a.client_id
    WHERE a.dismissed = 0
    ORDER BY CASE a.priority WHEN 'critical' THEN 0 WHEN 'high' THEN 1 WHEN 'medium' THEN 2 ELSE 3 END,
    a.created_at DESC
    LIMIT 12
  `).all().map(a => ({
    ...a,
    action_url: getAdviceActionUrl(a.advice_type, a.client_id)
  }));

  const consultingStats = {
    assessments: assessmentsCount,
    activeConsulting: db.prepare("SELECT COUNT(*) as cnt FROM engagements WHERE status = 'in_progress' AND type IN ('consulting','architecture')").get().cnt,
    clientsNeedingAssessment: db.prepare(`
      SELECT COUNT(*) as cnt FROM clients c
      WHERE c.status IN ('active','lead')
      AND NOT EXISTS (SELECT 1 FROM assessments a WHERE a.client_id = c.id)
    `).get().cnt,
    openAdvice: db.prepare('SELECT COUNT(*) as cnt FROM advice_log WHERE dismissed = 0').get().cnt,
  };

  const { toolkit, playbooks, maturityLayers } = getConsultingToolkit();

  const timeEntries = db.prepare('SELECT t.*, c.name as client_name, e.title as engagement_title, u.username FROM time_entries t LEFT JOIN clients c ON c.id = t.client_id LEFT JOIN engagements e ON e.id = t.engagement_id LEFT JOIN users u ON u.id = t.user_id ORDER BY t.date DESC LIMIT 20').all();
  const totalHours = db.prepare('SELECT COALESCE(SUM(hours),0) as h FROM time_entries').get().h;
  const billableHours = db.prepare('SELECT COALESCE(SUM(hours),0) as h FROM time_entries WHERE billable=1').get().h;
  const invoices = db.prepare('SELECT i.*, c.name as client_name FROM invoices i JOIN clients c ON c.id = i.client_id ORDER BY i.created_at DESC LIMIT 10').all();
  const totalInvoiced = db.prepare("SELECT COALESCE(SUM(total),0) as t FROM invoices WHERE status != 'draft'").get().t;
  const totalPaid = db.prepare("SELECT COALESCE(SUM(total),0) as t FROM invoices WHERE status='paid'").get().t;
  const totalOutstanding = db.prepare("SELECT COALESCE(SUM(total),0) as t FROM invoices WHERE status IN ('sent','overdue')").get().t;

  const racks = db.prepare('SELECT r.*, c.name as client_name, (SELECT COUNT(*) FROM rack_devices d WHERE d.rack_id = r.id) as device_count FROM racks r JOIN clients c ON c.id = r.client_id ORDER BY r.updated_at DESC LIMIT 6').all();
  const netDevices = db.prepare('SELECT nd.*, c.name as client_name FROM network_devices nd JOIN clients c ON c.id = nd.client_id ORDER BY nd.updated_at DESC LIMIT 10').all();
  const diagrams = db.prepare('SELECT d.*, c.name as client_name FROM data_diagrams d JOIN clients c ON c.id = d.client_id ORDER BY d.updated_at DESC LIMIT 6').all();

  const allTasks = db.prepare(`
    SELECT t.*, e.title as engagement_title, e.id as eid, c.name as client_name
    FROM tasks t JOIN engagements e ON e.id = t.engagement_id JOIN clients c ON c.id = e.client_id
    ORDER BY CASE t.status WHEN 'todo' THEN 0 WHEN 'in_progress' THEN 1 WHEN 'done' THEN 2 WHEN 'cancelled' THEN 3 END,
    CASE t.priority WHEN 'critical' THEN 0 WHEN 'high' THEN 1 WHEN 'medium' THEN 2 WHEN 'low' THEN 3 END,
    t.due_date ASC
  `).all();

  let servicesStatus = {};
  try {
    servicesStatus = await getServicesStatus();
  } catch (e) {
    servicesStatus = { consulting: { online: true, label: 'Mek-Tech Core' } };
  }

  res.render('dashboard', {
    tab, clients, stats,
    showcaseUrl: getSettings().showcase_url || 'http://192.168.1.247:3010',
    totalDiagrams, totalRacks, totalRackDevices, totalNetworkDevices,
    datacenterCount, marketCompaniesCount, qualityFileCount,
    overdueTasks, totalTasks, completedTasks, assessmentsCount,
    recentEngagements, allEngagements, recentAssessments,
    engagementByType, engagementByStatus, clientsByIndustry,
    activeAdvice,
    consultingStats, toolkit, playbooks, maturityLayers,
    timeEntries, totalHours, billableHours, invoices, totalInvoiced, totalPaid, totalOutstanding,
    racks, netDevices, diagrams,
    allTasks, servicesStatus
  });
});

module.exports = router;