/**
 * routes/v1-api.js — REST API v1 read-only endpoints.
 * Owns: /api/v1/* — health data, TuneOps tickets, activity log, team endpoints.
 * Does NOT own: API key management (routes/settings-api.js), auth middleware (middleware/api-auth.js).
 *
 * All endpoints require a valid API key via Authorization: Bearer tv_api_XXXX.
 * Access is tier-gated: enterprise=all, business=health+tuneops, team=health only.
 */

const express = require('express');
const router = express.Router();
const pool = require('../db/index');
const { requireApiKey } = require('../middleware/api-auth');

// ── Helpers ──────────────────────────────────────────────────────────────────

/**
 * Build a standard paginated response envelope.
 */
function paginate(rows, total, page, perPage) {
  return {
    data: rows,
    meta: {
      total,
      page,
      per_page: perPage,
      has_more: page * perPage < total,
    },
  };
}

/**
 * Parse ?page and ?per_page query params with sane defaults and maximums.
 */
function parsePagination(query) {
  const page = Math.max(1, parseInt(query.page, 10) || 1);
  const perPage = Math.min(100, Math.max(1, parseInt(query.per_page, 10) || 50));
  const offset = (page - 1) * perPage;
  return { page, perPage, offset };
}

// ── Health: Connections ───────────────────────────────────────────────────────

// GET /api/v1/health/connections — list all connections with latest health score
router.get('/health/connections', requireApiKey('health'), async (req, res) => {
  try {
    const { page, perPage, offset } = parsePagination(req.query);
    const userId = req.apiUser.id;

    const countResult = await pool.query(
      `SELECT COUNT(*) AS cnt FROM oracle_connections WHERE user_id = :p1`,
      { p1: userId }
    );
    const total = parseInt(countResult.rows[0].cnt, 10);

    const { rows } = await pool.query(
      `SELECT oc.id, oc.name, oc.host, oc.port, oc.service_name,
              oc.connection_type, oc.created_at, oc.updated_at,
              hc.id AS latest_check_id, hc.score, hc.created_at AS last_checked_at,
              hc.summary_text, hc.analysis_stage
       FROM oracle_connections oc
       OUTER APPLY (
         SELECT id, overall_score AS score, created_at, summary_text, analysis_stage
         FROM health_checks
         WHERE connection_id = oc.id
         ORDER BY created_at DESC
         FETCH FIRST 1 ROW ONLY
       ) hc
       WHERE oc.user_id = :p1
       ORDER BY oc.created_at DESC
       OFFSET :p3 ROWS FETCH NEXT :p2 ROWS ONLY`,
      { p1: userId, p2: perPage, p3: offset }
    );

    res.json(paginate(rows, total, page, perPage));
  } catch (err) {
    console.error('[v1-api] GET /health/connections error:', err.message);
    res.status(500).json({ error: { code: 'internal_error', message: 'Failed to fetch connections' } });
  }
});

// GET /api/v1/health/connections/:id — single connection health summary
router.get('/health/connections/:id', requireApiKey('health'), async (req, res) => {
  try {
    const userId = req.apiUser.id;
    const connId = parseInt(req.params.id, 10);

    const { rows } = await pool.query(
      `SELECT oc.id, oc.name, oc.host, oc.port, oc.service_name,
              oc.connection_type, oc.created_at, oc.updated_at,
              hc.id AS latest_check_id, hc.score, hc.created_at AS last_checked_at,
              hc.summary_text, hc.top_action, hc.analysis_stage,
              hc.ebs_summary, hc.ebs_action
       FROM oracle_connections oc
       OUTER APPLY (
         SELECT id, overall_score AS score, created_at, summary_text, top_action, analysis_stage, ebs_summary, ebs_action
         FROM health_checks
         WHERE connection_id = oc.id
         ORDER BY created_at DESC
         FETCH FIRST 1 ROW ONLY
       ) hc
       WHERE oc.id = :p1 AND oc.user_id = :p2`,
      { p1: connId, p2: userId }
    );

    if (!rows[0]) {
      return res.status(404).json({ error: { code: 'not_found', message: 'Connection not found' } });
    }

    res.json({ data: rows[0] });
  } catch (err) {
    console.error('[v1-api] GET /health/connections/:id error:', err.message);
    res.status(500).json({ error: { code: 'internal_error', message: 'Failed to fetch connection' } });
  }
});

// GET /api/v1/health/connections/:id/checks — health check history
router.get('/health/connections/:id/checks', requireApiKey('health'), async (req, res) => {
  try {
    const userId = req.apiUser.id;
    const connId = parseInt(req.params.id, 10);
    const { page, perPage, offset } = parsePagination(req.query);

    // Verify ownership
    const ownerCheck = await pool.query(
      `SELECT id FROM oracle_connections WHERE id = :p1 AND user_id = :p2`,
      { p1: connId, p2: userId }
    );
    if (!ownerCheck.rows[0]) {
      return res.status(404).json({ error: { code: 'not_found', message: 'Connection not found' } });
    }

    // Date range filter
    let dateClause = '';
    const params = { p_conn: connId };
    if (req.query.from) { params.p_from = req.query.from; dateClause += ` AND created_at >= :p_from`; }
    if (req.query.to)   { params.p_to   = req.query.to;   dateClause += ` AND created_at <= :p_to`; }

    const countResult = await pool.query(
      `SELECT COUNT(*) AS cnt FROM health_checks WHERE connection_id = :p_conn${dateClause}`,
      params
    );
    const total = parseInt(countResult.rows[0].cnt, 10);

    params.p_limit = perPage; params.p_offset = offset;
    const { rows } = await pool.query(
      `SELECT id, overall_score AS score, analysis_stage, summary_text, top_action,
              ebs_summary, ebs_action, created_at
       FROM health_checks
       WHERE connection_id = :p_conn${dateClause}
       ORDER BY created_at DESC
       OFFSET :p_offset ROWS FETCH NEXT :p_limit ROWS ONLY`,
      params
    );

    res.json(paginate(rows, total, page, perPage));
  } catch (err) {
    console.error('[v1-api] GET /health/connections/:id/checks error:', err.message);
    res.status(500).json({ error: { code: 'internal_error', message: 'Failed to fetch checks' } });
  }
});

// GET /api/v1/health/connections/:id/checks/latest — most recent check results
router.get('/health/connections/:id/checks/latest', requireApiKey('health'), async (req, res) => {
  try {
    const userId = req.apiUser.id;
    const connId = parseInt(req.params.id, 10);

    // Verify ownership
    const ownerCheck = await pool.query(
      `SELECT id FROM oracle_connections WHERE id = :p1 AND user_id = :p2`,
      { p1: connId, p2: userId }
    );
    if (!ownerCheck.rows[0]) {
      return res.status(404).json({ error: { code: 'not_found', message: 'Connection not found' } });
    }

    // Get latest health check
    const hcResult = await pool.query(
      `SELECT id, run_id, overall_score AS score, analysis_stage, summary_text, top_action,
              ebs_summary, ebs_action, created_at
       FROM health_checks
       WHERE connection_id = :p1
       ORDER BY created_at DESC
       FETCH FIRST 1 ROWS ONLY`,
      { p1: connId }
    );
    if (!hcResult.rows[0]) {
      return res.status(404).json({ error: { code: 'not_found', message: 'No health checks found for this connection' } });
    }

    const hc = hcResult.rows[0];

    // Get individual check results
    // NOTE: check_results has no category/title/metric_line/remediation/severity
    // columns (that shape never shipped; it looks like finding_history's), so they
    // are mapped onto the real schema here.
    //
    // bug #6 (FIXED): this filtered `run_id = <health_checks.id>`, comparing a
    // VARCHAR2(36) UUID to a NUMBER — Oracle coerced it and raised ORA-01722 on
    // every call. health_checks.run_id now carries the same UUID (stamped in
    // runAIAnalysis), so it is a real key. Runs from before that column existed
    // have run_id NULL and return an empty checks array rather than a 500.
    const { rows: checkRows } = hc.run_id ? await pool.query(
      `SELECT check_id, check_category AS category, status,
              metric_name AS title, NULL AS metric_line,
              recommendation AS remediation, status AS severity
       FROM check_results
       WHERE run_id = :p1
       ORDER BY check_category, check_id`,
      { p1: hc.run_id }
    ) : { rows: [] };

    res.json({
      data: {
        ...hc,
        checks: checkRows,
      }
    });
  } catch (err) {
    console.error('[v1-api] GET /health/connections/:id/checks/latest error:', err.message);
    res.status(500).json({ error: { code: 'internal_error', message: 'Failed to fetch latest check' } });
  }
});

// GET /api/v1/health/connections/:id/checks/:checkId — single check detail
router.get('/health/connections/:id/checks/:checkId', requireApiKey('health'), async (req, res) => {
  try {
    const userId = req.apiUser.id;
    const connId = parseInt(req.params.id, 10);
    const checkId = parseInt(req.params.checkId, 10);

    // Verify ownership
    const ownerCheck = await pool.query(
      `SELECT id FROM oracle_connections WHERE id = :p1 AND user_id = :p2`,
      { p1: connId, p2: userId }
    );
    if (!ownerCheck.rows[0]) {
      return res.status(404).json({ error: { code: 'not_found', message: 'Connection not found' } });
    }

    // GROUP BY on health_checks.id (Postgres functional-dependency shortcut) is
    // invalid in Oracle and impossible over its CLOB columns; use a correlated
    // JSON_ARRAYAGG subquery instead. check_results phantom columns mapped to the
    // real schema. bug #6 FIXED: joins cr.run_id = hc.run_id (both VARCHAR2(36))
    // instead of cr.run_id = hc.id (UUID vs NUMBER -> ORA-01722).
    const { rows } = await pool.query(
      `SELECT hc.id, hc.overall_score AS score, hc.analysis_stage, hc.summary_text, hc.top_action,
              hc.ebs_summary, hc.ebs_action, hc.created_at,
              (SELECT JSON_ARRAYAGG(
                 JSON_OBJECT(
                   'check_id'    VALUE cr.check_id,
                   'category'    VALUE cr.check_category,
                   'status'      VALUE cr.status,
                   'title'       VALUE cr.metric_name,
                   'metric_line' VALUE NULL,
                   'remediation' VALUE cr.recommendation,
                   'severity'    VALUE cr.status
                 ) ORDER BY cr.check_category, cr.check_id
                 RETURNING JSON
               )
               FROM check_results cr WHERE cr.run_id = hc.run_id) AS checks
       FROM health_checks hc
       WHERE hc.id = :p1 AND hc.connection_id = :p2`,
      { p1: checkId, p2: connId }
    );

    if (!rows[0]) {
      return res.status(404).json({ error: { code: 'not_found', message: 'Health check not found' } });
    }

    res.json({ data: rows[0] });
  } catch (err) {
    console.error('[v1-api] GET /health/connections/:id/checks/:checkId error:', err.message);
    res.status(500).json({ error: { code: 'internal_error', message: 'Failed to fetch check detail' } });
  }
});

// GET /api/v1/health/fleet — fleet dashboard (all connections, scores, alerts)
router.get('/health/fleet', requireApiKey('health'), async (req, res) => {
  try {
    const userId = req.apiUser.id;

    const { rows } = await pool.query(
      `SELECT oc.id, oc.name, oc.host, oc.connection_type,
              hc.score, hc.created_at AS last_checked_at, hc.summary_text,
              hc.analysis_stage,
              CASE
                WHEN hc.score IS NULL THEN 'never_run'
                WHEN hc.score >= 80 THEN 'healthy'
                WHEN hc.score >= 60 THEN 'warning'
                ELSE 'critical'
              END AS status
       FROM oracle_connections oc
       OUTER APPLY (
         SELECT overall_score AS score, created_at, summary_text, analysis_stage
         FROM health_checks
         WHERE connection_id = oc.id
         ORDER BY created_at DESC
         FETCH FIRST 1 ROW ONLY
       ) hc
       WHERE oc.user_id = :p1
       ORDER BY oc.name`,
      { p1: userId }
    );

    const summary = {
      total: rows.length,
      healthy: rows.filter(r => r.status === 'healthy').length,
      warning: rows.filter(r => r.status === 'warning').length,
      critical: rows.filter(r => r.status === 'critical').length,
      never_run: rows.filter(r => r.status === 'never_run').length,
    };

    res.json({ data: rows, summary });
  } catch (err) {
    console.error('[v1-api] GET /health/fleet error:', err.message);
    res.status(500).json({ error: { code: 'internal_error', message: 'Failed to fetch fleet data' } });
  }
});

// ── TuneOps Tickets ───────────────────────────────────────────────────────────

// GET /api/v1/tuneops/tickets — list tickets
router.get('/tuneops/tickets', requireApiKey('tuneops'), async (req, res) => {
  try {
    const userId = req.apiUser.id;
    const { page, perPage, offset } = parsePagination(req.query);

    // Build filter clauses (named binds; $${n} array indexing has no Oracle form)
    const params = { p_user: userId };
    const filters = [];
    let fi = 0;
    const addFilter = (frag, val) => { const k = `f${fi++}`; filters.push(frag.replace('?', `:${k}`)); params[k] = val; };

    // finding_history has no status column — it's derived from resolved_at (the
    // original f.status = $n was a pre-existing bug). Map to the resolved_at check.
    if (req.query.status)        filters.push(req.query.status === 'resolved' ? 'f.resolved_at IS NOT NULL' : 'f.resolved_at IS NULL');
    if (req.query.severity)      addFilter('f.severity = ?', req.query.severity);
    if (req.query.connection_id) addFilter('f.connection_id = ?', parseInt(req.query.connection_id, 10));
    if (req.query.from)          addFilter('f.first_seen_at >= ?', req.query.from);
    if (req.query.to)            addFilter('f.first_seen_at <= ?', req.query.to);

    const whereExtra = filters.length ? ' AND ' + filters.join(' AND ') : '';

    const countResult = await pool.query(
      `SELECT COUNT(*) AS cnt
       FROM finding_history f
       JOIN oracle_connections oc ON oc.id = f.connection_id
       WHERE oc.user_id = :p_user${whereExtra}`,
      params
    );
    const total = parseInt(countResult.rows[0].cnt, 10);

    params.p_limit = perPage; params.p_offset = offset;
    const { rows } = await pool.query(
      `SELECT f.id, f.connection_id, oc.name AS connection_name,
              f.check_id, f.finding_key, f.title, f.metric_line,
              f.remediation, f.severity,
              f.first_seen_at, f.last_seen_at, f.resolved_at,
              CASE WHEN f.resolved_at IS NOT NULL THEN 'resolved' ELSE 'open' END AS status
       FROM finding_history f
       JOIN oracle_connections oc ON oc.id = f.connection_id
       WHERE oc.user_id = :p_user${whereExtra}
       ORDER BY f.last_seen_at DESC
       OFFSET :p_offset ROWS FETCH NEXT :p_limit ROWS ONLY`,
      params
    );

    res.json(paginate(rows, total, page, perPage));
  } catch (err) {
    console.error('[v1-api] GET /tuneops/tickets error:', err.message);
    res.status(500).json({ error: { code: 'internal_error', message: 'Failed to fetch tickets' } });
  }
});

// GET /api/v1/tuneops/tickets/:ticketNumber — single ticket detail
router.get('/tuneops/tickets/:ticketNumber', requireApiKey('tuneops'), async (req, res) => {
  try {
    const userId = req.apiUser.id;
    const ticketId = parseInt(req.params.ticketNumber, 10);

    const { rows } = await pool.query(
      `SELECT f.id, f.connection_id, oc.name AS connection_name,
              f.check_id, f.finding_key, f.title, f.metric_line,
              f.remediation, f.severity,
              f.first_seen_at, f.last_seen_at, f.resolved_at,
              CASE WHEN f.resolved_at IS NOT NULL THEN 'resolved' ELSE 'open' END AS status
       FROM finding_history f
       JOIN oracle_connections oc ON oc.id = f.connection_id
       WHERE f.id = :p1 AND oc.user_id = :p2`,
      { p1: ticketId, p2: userId }
    );

    if (!rows[0]) {
      return res.status(404).json({ error: { code: 'not_found', message: 'Ticket not found' } });
    }

    res.json({ data: rows[0] });
  } catch (err) {
    console.error('[v1-api] GET /tuneops/tickets/:id error:', err.message);
    res.status(500).json({ error: { code: 'internal_error', message: 'Failed to fetch ticket' } });
  }
});

// GET /api/v1/tuneops/stats — aggregate ticket stats
router.get('/tuneops/stats', requireApiKey('tuneops'), async (req, res) => {
  try {
    const userId = req.apiUser.id;

    const { rows } = await pool.query(
      `SELECT
         COUNT(CASE WHEN f.resolved_at IS NULL THEN 1 END) AS open_count,
         COUNT(CASE WHEN f.resolved_at >= CURRENT_TIMESTAMP - INTERVAL '7' DAY THEN 1 END) AS resolved_this_week,
         COUNT(CASE WHEN f.severity = 'critical' AND f.resolved_at IS NULL THEN 1 END) AS critical_open,
         COUNT(CASE WHEN f.severity = 'warning' AND f.resolved_at IS NULL THEN 1 END) AS warning_open,
         ROUND(AVG(CASE WHEN f.resolved_at IS NOT NULL
           THEN (CAST(f.resolved_at AS DATE) - CAST(f.first_seen_at AS DATE)) * 24 END), 1) AS avg_resolution_hours
       FROM finding_history f
       JOIN oracle_connections oc ON oc.id = f.connection_id
       WHERE oc.user_id = :p1`,
      { p1: userId }
    );

    res.json({ data: rows[0] || {} });
  } catch (err) {
    console.error('[v1-api] GET /tuneops/stats error:', err.message);
    res.status(500).json({ error: { code: 'internal_error', message: 'Failed to fetch stats' } });
  }
});

// ── Activity Log ──────────────────────────────────────────────────────────────

// GET /api/v1/activity — activity/audit log (enterprise only)
router.get('/activity', requireApiKey('activity'), async (req, res) => {
  try {
    const userId = req.apiUser.id;
    const { page, perPage, offset } = parsePagination(req.query);

    const params = { p_user: userId };
    const filters = [];
    let fi = 0;
    const addFilter = (frag, val) => { const k = `f${fi++}`; filters.push(frag.replace('?', `:${k}`)); params[k] = val; };

    if (req.query.action_type) addFilter('ae.event_name = ?', req.query.action_type);
    if (req.query.from)        addFilter('ae.occurred_at >= ?', req.query.from);
    if (req.query.to)          addFilter('ae.occurred_at <= ?', req.query.to);

    const whereExtra = filters.length ? ' AND ' + filters.join(' AND ') : '';

    const countResult = await pool.query(
      `SELECT COUNT(*) AS cnt FROM analytics_events ae WHERE ae.user_id = :p_user${whereExtra}`,
      params
    );
    const total = parseInt(countResult.rows[0].cnt, 10);

    params.p_limit = perPage; params.p_offset = offset;
    const { rows } = await pool.query(
      `SELECT ae.id, ae.event_name AS action_type, ae.page_path,
              ae.session_id, ae.properties, ae.occurred_at
       FROM analytics_events ae
       WHERE ae.user_id = :p_user${whereExtra}
       ORDER BY ae.occurred_at DESC
       OFFSET :p_offset ROWS FETCH NEXT :p_limit ROWS ONLY`,
      params
    );

    res.json(paginate(rows, total, page, perPage));
  } catch (err) {
    console.error('[v1-api] GET /activity error:', err.message);
    res.status(500).json({ error: { code: 'internal_error', message: 'Failed to fetch activity' } });
  }
});

// GET /api/v1/activity/summary — aggregate activity counts
router.get('/activity/summary', requireApiKey('activity'), async (req, res) => {
  try {
    const userId = req.apiUser.id;

    const params = { p_user: userId };
    let dateClause = '';
    if (req.query.from) { params.p_from = req.query.from; dateClause += ` AND occurred_at >= :p_from`; }
    if (req.query.to)   { params.p_to   = req.query.to;   dateClause += ` AND occurred_at <= :p_to`; }

    const { rows } = await pool.query(
      `SELECT event_name AS action_type, COUNT(*) AS "count"
       FROM analytics_events
       WHERE user_id = :p_user${dateClause}
       GROUP BY event_name
       ORDER BY COUNT(*) DESC`,
      params
    );

    res.json({ data: rows });
  } catch (err) {
    console.error('[v1-api] GET /activity/summary error:', err.message);
    res.status(500).json({ error: { code: 'internal_error', message: 'Failed to fetch activity summary' } });
  }
});

// ── Team ──────────────────────────────────────────────────────────────────────

// GET /api/v1/team/members — list team members with roles
router.get('/team/members', requireApiKey('team'), async (req, res) => {
  try {
    const userId = req.apiUser.id;

    // Find the team this user belongs to or owns
    const teamResult = await pool.query(
      `SELECT t.id FROM teams t
       JOIN team_members tm ON tm.team_id = t.id
       WHERE tm.user_id = :p1
       FETCH FIRST 1 ROWS ONLY`,
      { p1: userId }
    );

    if (!teamResult.rows[0]) {
      return res.json({ data: [], meta: { total: 0, page: 1, per_page: 50, has_more: false } });
    }

    const teamId = teamResult.rows[0].id;

    const { rows } = await pool.query(
      `SELECT tm.id, tm.user_id, tm.role, tm.joined_at,
              u.email, u.name
       FROM team_members tm
       JOIN users u ON u.id = tm.user_id
       WHERE tm.team_id = :p1
       ORDER BY tm.joined_at ASC`,
      { p1: teamId }
    );

    res.json({ data: rows, meta: { total: rows.length, page: 1, per_page: rows.length, has_more: false } });
  } catch (err) {
    console.error('[v1-api] GET /team/members error:', err.message);
    res.status(500).json({ error: { code: 'internal_error', message: 'Failed to fetch team members' } });
  }
});

// GET /api/v1/team/roles — list roles and their permissions
router.get('/team/roles', requireApiKey('team'), async (req, res) => {
  const roles = [
    {
      role: 'admin',
      label: 'Admin',
      description: 'Full access — manage team, connections, run checks, view all data',
      permissions: ['connections.manage', 'checks.run', 'reports.view', 'team.manage'],
    },
    {
      role: 'senior_dba',
      label: 'Senior DBA',
      description: 'Run checks, view all data, manage connections',
      permissions: ['connections.manage', 'checks.run', 'reports.view'],
    },
    {
      role: 'junior_dba',
      label: 'Junior DBA',
      description: 'Run checks, view reports — no connection management',
      permissions: ['checks.run', 'reports.view'],
    },
    {
      role: 'viewer',
      label: 'Viewer',
      description: 'Read-only access to reports and check results',
      permissions: ['reports.view'],
    },
  ];

  res.json({ data: roles });
});

module.exports = router;
