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

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

  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;
  const totalConnections = db.prepare('SELECT COUNT(*) as cnt FROM network_connections').get().cnt;
  const totalDiagrams = db.prepare('SELECT COUNT(*) as cnt FROM data_diagrams').get().cnt;

  const racksByClient = db.prepare(`
    SELECT c.name as client_name, COUNT(*) as cnt
    FROM racks r JOIN clients c ON c.id = r.client_id
    GROUP BY c.id ORDER BY cnt DESC LIMIT 10
  `).all();

  const deviceTypes = db.prepare(`
    SELECT device_type, COUNT(*) as cnt
    FROM rack_devices GROUP BY device_type ORDER BY cnt DESC
  `).all();

  const netDeviceTypes = db.prepare(`
    SELECT device_type, COUNT(*) as cnt
    FROM network_devices GROUP BY device_type ORDER BY cnt DESC
  `).all();

  const diagramTypes = db.prepare(`
    SELECT diagram_type, COUNT(*) as cnt
    FROM data_diagrams GROUP BY diagram_type ORDER BY cnt DESC
  `).all();

  const recentRacks = 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 recentDiagrams = 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 recentNetDevices = 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 6
  `).all();

  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
  `).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
  `).all();

  const connections = db.prepare(`
    SELECT nc.*, c.name as client_name
    FROM network_connections nc JOIN clients c ON c.id = nc.client_id
    ORDER BY nc.created_at DESC
  `).all();

  const diagrams = db.prepare(`
    SELECT d.*, c.name as client_name,
      json_array_length(d.nodes) as node_count
    FROM data_diagrams d JOIN clients c ON c.id = d.client_id
    ORDER BY d.updated_at DESC
  `).all();

  const clients = db.prepare('SELECT id, name FROM clients ORDER BY name').all();
  const hardwareLibrary = require('../data/hardware-library');

  res.render('infrastructure', {
    tab, racks, clients, hardwareLibrary,
    netDevices, connections, diagrams,
    totalRacks, totalRackDevices, totalNetworkDevices, totalConnections, totalDiagrams,
    racksByClient, deviceTypes, netDeviceTypes, diagramTypes,
    recentRacks, recentDiagrams, recentNetDevices
  });
});

module.exports = router;