const express = require('express');
const router  = express.Router();
const { requireAuth } = require('../middleware/auth');
const pool = require('../db');

// GET /clients → Clients dashboard page
router.get('/', requireAuth, (req, res) => {
  res.sendFile('clients-dashboard.html', { root: 'public' });
});

// GET /clients/client/:slug → client detail page
router.get('/client/:slug', requireAuth, (req, res) => {
  res.sendFile('clients-detail.html', { root: 'public' });
});

// GET /api/clients/clients — list all clients with health rollup
router.get('/clients', requireAuth, async (req, res) => {
  try {
    const { rows } = await pool.query(`
      SELECT
        c.client_name,
        c.client_slug,
        COUNT(c.id)::int                                        AS conn_count,
        COUNT(DISTINCT c.ebs_instance_name)::int                AS instance_count,
        ROUND(AVG(h.score))::int                                AS avg_score,
        MIN(h.score)::int                                       AS min_score,
        SUM(CASE WHEN h.score < 50 THEN 1 ELSE 0 END)::int     AS critical_count,
        SUM(CASE WHEN h.score >= 50 AND h.score < 80 THEN 1 ELSE 0 END)::int AS warning_count,
        JSON_AGG(
          JSON_BUILD_OBJECT('id', c.id, 'score', h.score)
          ORDER BY c.name
        ) AS connections
      FROM oracle_connections c
      LEFT JOIN LATERAL (
        SELECT overall_score AS score FROM health_checks
        WHERE connection_id = c.id
        ORDER BY created_at DESC LIMIT 1
      ) h ON true
      WHERE c.user_id = $1
        AND c.client_name IS NOT NULL
      GROUP BY c.client_name, c.client_slug
      ORDER BY c.client_name
    `, [req.user.id]);
    res.json({ clients: rows });
  } catch (err) {
    console.error('[clients] /clients error:', err.message);
    res.status(500).json({ error: 'Failed to load clients' });
  }
});

// GET /api/clients/clients/:slug — connections for one client grouped by ebs_instance_name
router.get('/clients/:slug', requireAuth, async (req, res) => {
  try {
    const { rows } = await pool.query(`
      SELECT
        c.id, c.name, c.host, c.server_type, c.ebs_instance_name,
        c.client_name, c.client_slug, c.proxy_url,
        h.score, h.created_at AS last_check
      FROM oracle_connections c
      LEFT JOIN LATERAL (
        SELECT overall_score AS score, created_at FROM health_checks
        WHERE connection_id = c.id
        ORDER BY created_at DESC LIMIT 1
      ) h ON true
      WHERE c.user_id = $1
        AND c.client_slug = $2
      ORDER BY c.ebs_instance_name NULLS LAST, c.name
    `, [req.user.id, req.params.slug]);

    if (!rows.length) {
      return res.status(404).json({ error: 'Client not found' });
    }

    // Group by ebs_instance_name
    const instances = {};
    rows.forEach(r => {
      const key = r.ebs_instance_name || 'Standalone';
      if (!instances[key]) instances[key] = [];
      instances[key].push(r);
    });

    res.json({ client_name: rows[0].client_name || req.params.slug, instances });
  } catch (err) {
    console.error('[clients] /clients/:slug error:', err.message);
    res.status(500).json({ error: 'Failed to load client' });
  }
});

// PATCH /api/clients/clients/:slug — rename client (update client_name + client_slug)
router.patch('/clients/:slug', requireAuth, async (req, res) => {
  try {
    const { client_name, client_slug } = req.body;
    if (!client_name || !client_slug) return res.status(400).json({ error: 'client_name and client_slug required' });
    await pool.query(
      `UPDATE oracle_connections SET client_name=$1, client_slug=$2 WHERE client_slug=$3 AND user_id=$4`,
      [client_name, client_slug, req.params.slug, req.user.id]
    );
    res.json({ ok: true });
  } catch(err) {
    console.error('[clients] patch error:', err.message);
    res.status(500).json({ error: 'Failed to rename client' });
  }
});

// DELETE /api/clients/clients/:slug — unassign all connections from a client
router.delete('/clients/:slug', requireAuth, async (req, res) => {
  try {
    await pool.query(
      `UPDATE oracle_connections
       SET client_name = NULL, client_slug = NULL
       WHERE client_slug = $1 AND user_id = $2`,
      [req.params.slug, req.user.id]
    );
    res.json({ ok: true });
  } catch (err) {
    console.error('[clients] delete client error:', err.message);
    res.status(500).json({ error: 'Failed to delete client' });
  }
});

module.exports = router;
