/**
 * routes/ebs-clone.js — EBS Clone & Scale wizard API + page.
 *
 * Owns: /ebs-clone page, /api/ebs-clone/* endpoints (recipes CRUD, history, step execution).
 * Does NOT own: SSH execution (services/ssh-executor.js), Oracle connection auth (server.js),
 *               SSH credential storage (db/ssh-targets.js).
 *
 * Routes:
 *   GET  /ebs-clone                           — serve the wizard page
 *   GET  /api/ebs-clone/connections           — list EBS-detected connections for current user
 *   GET  /api/ebs-clone/recipes               — list recipes for company
 *   GET  /api/ebs-clone/recipes/:id           — get recipe by id
 *   POST /api/ebs-clone/recipes               — create recipe
 *   PUT  /api/ebs-clone/recipes/:id           — update recipe
 *   DELETE /api/ebs-clone/recipes/:id         — delete recipe
 *   GET  /api/ebs-clone/history               — clone run history
 *   POST /api/ebs-clone/history/start         — start a clone run (creates history row)
 *   POST /api/ebs-clone/history/:id/finish    — finish a clone run
 *   GET  /api/ebs-clone/ssh-targets/:connId   — SSH targets for a connection
 *   POST /api/ebs-clone/execute-step          — execute a single clone step via SSH
 */

'use strict';

const express    = require('express');
const path       = require('path');
const pool       = require('../db/index');
const sshDb      = require('../db/ssh-targets');
const recipeDb   = require('../db/clone-recipes');
const executor   = require('../services/ssh-executor');
const { runCloneStep } = require('../services/clone-step-runner');
const { requireAuth, requireRole } = require('../middleware/auth');

const router = express.Router();

// ── Company ID from authenticated user ───────────────────────────────────────

function companyId(user) {
  return user.company_domain || user.email.split('@')[1] || `user_${user.id}`;
}

// ── GET /ebs-clone ────────────────────────────────────────────────────────────

router.get('/ebs-clone', requireAuth, (req, res) => {
  res.sendFile(path.join(__dirname, '..', 'public', 'ebs-clone.html'));
});

// ── GET /api/ebs-clone/connections ───────────────────────────────────────────
// Returns connections with EBS detected for current user (or all for team).
// Frontend shows display_name only — no env labels.

router.get('/api/ebs-clone/connections', requireAuth, async (req, res) => {
  try {
    // oracle_connections has no display_name/ebs_detected/is_demo columns
    // (pre-existing bug #4) — real columns are name and is_ebs; there is no
    // per-connection is_demo. Aliased back to the keys the wizard UI reads.
    // An EBS clone spans BOTH tiers — ebs.clone.preclone.db is db_tier while
    // ebs.clone.preclone.apps is apps_tier — so this deliberately does NOT filter
    // by server_type the way db-clone does. What it must do is say which tier each
    // connection is, so the wizard can offer four correct pickers instead of one
    // ambiguous pair.
    const { rows } = await pool.query(
      `SELECT id, name AS display_name, host, port, service_name, connection_type,
              is_ebs AS ebs_detected, ebs_login_url, server_type, ebs_instance_name
       FROM oracle_connections
       WHERE user_id = :p1
       ORDER BY ebs_instance_name NULLS LAST, name`,
      { p1: req.user.id }
    );
    res.json({ success: true, connections: rows });
  } catch (err) {
    console.error('[ebs-clone] connections error:', err.message);
    res.status(500).json({ error: 'Failed to fetch connections' });
  }
});

// ── GET /api/ebs-clone/command-tiers ─────────────────────────────────────────
// Which tier each clone command must run on, derived from COMMAND_WHITELIST.
//
// The wizard needs this to route a step to the right connection: with one
// connection per side, ebs.clone.preclone.db (db_tier) and
// ebs.clone.preclone.apps (apps_tier) both went to the same agent, and whichever
// did not match was refused. Served rather than restated in the page, because a
// second copy of a tier mapping is exactly the kind of thing that drifts —
// three different db-ops counts drifted in CLAUDE.md the same way.
router.get('/api/ebs-clone/command-tiers', requireAuth, (req, res) => {
  const { COMMAND_WHITELIST } = require('../services/ssh-executor');
  const tiers = {};
  for (const [key, def] of Object.entries(COMMAND_WHITELIST)) {
    if (!key.includes('.clone.')) continue;
    const roles = def.allowedRoles || [];
    // Steps valid on either tier (a disk check, say) are marked 'any' and follow
    // whichever connection the side already has.
    tiers[key] = roles.includes('apps_tier') && roles.includes('db_tier') ? 'any'
               : roles.includes('apps_tier') ? 'apps'
               : roles.includes('db_tier')   ? 'db'
               : 'any';
  }
  res.json({ success: true, tiers });
});

// ── GET /api/ebs-clone/recipes ────────────────────────────────────────────────

router.get('/api/ebs-clone/recipes', requireAuth, async (req, res) => {
  try {
    const recipes = await recipeDb.listRecipes(companyId(req.user), 'ebs');
    res.json({ success: true, recipes });
  } catch (err) {
    console.error('[ebs-clone] list recipes error:', err.message);
    res.status(500).json({ error: 'Failed to fetch recipes' });
  }
});

// ── GET /api/ebs-clone/recipes/:id ───────────────────────────────────────────

router.get('/api/ebs-clone/recipes/:id', requireAuth, async (req, res) => {
  try {
    const recipe = await recipeDb.getRecipeById(parseInt(req.params.id, 10));
    if (!recipe) return res.status(404).json({ error: 'Recipe not found' });
    if (recipe.company_id !== companyId(req.user)) return res.status(403).json({ error: 'Access denied' });
    res.json({ success: true, recipe });
  } catch (err) {
    console.error('[ebs-clone] get recipe error:', err.message);
    res.status(500).json({ error: 'Failed to fetch recipe' });
  }
});

// ── POST /api/ebs-clone/recipes ───────────────────────────────────────────────
// Body: { source_connection_id, target_connection_id, recipe_name, pre_checks_config, clone_steps, post_steps }

router.post('/api/ebs-clone/recipes', requireAuth, requireRole('senior_dba'), async (req, res) => {
  try {
    const {
      source_connection_id,
      target_connection_id,
      // Tier-aware connections — an EBS clone spans both tiers.
      source_db_connection_id,
      source_apps_connection_id,
      target_db_connection_id,
      target_apps_connection_id,
      recipe_name,
      pre_checks_config,
      clone_steps,
      post_steps,
    } = req.body;

    if (!recipe_name || !recipe_name.trim()) {
      return res.status(400).json({ error: 'recipe_name is required' });
    }

    const recipe = await recipeDb.createRecipe({
      companyId: companyId(req.user),
      sourceConnectionId: source_connection_id ? parseInt(source_connection_id, 10) : null,
      targetConnectionId: target_connection_id ? parseInt(target_connection_id, 10) : null,
      sourceDbConnectionId:   source_db_connection_id   ? parseInt(source_db_connection_id, 10)   : null,
      sourceAppsConnectionId: source_apps_connection_id ? parseInt(source_apps_connection_id, 10) : null,
      targetDbConnectionId:   target_db_connection_id   ? parseInt(target_db_connection_id, 10)   : null,
      targetAppsConnectionId: target_apps_connection_id ? parseInt(target_apps_connection_id, 10) : null,
      // Stamped server-side, never taken from the body: the wizard a row belongs
      // to is a fact about which endpoint wrote it, not a client assertion.
      wizard: 'ebs',
      recipeName: recipe_name.trim(),
      preChecksConfig: pre_checks_config || {},
      cloneSteps: clone_steps || [],
      postSteps: post_steps || [],
      createdBy: req.user.id,
    });

    res.status(201).json({ success: true, recipe });
  } catch (err) {
    console.error('[ebs-clone] create recipe error:', err.message);
    res.status(500).json({ error: 'Failed to create recipe' });
  }
});

// ── PUT /api/ebs-clone/recipes/:id ───────────────────────────────────────────

router.put('/api/ebs-clone/recipes/:id', requireAuth, requireRole('senior_dba'), async (req, res) => {
  try {
    const id = parseInt(req.params.id, 10);
    const existing = await recipeDb.getRecipeById(id);
    if (!existing) return res.status(404).json({ error: 'Recipe not found' });
    if (existing.company_id !== companyId(req.user)) return res.status(403).json({ error: 'Access denied' });

    const updated = await recipeDb.updateRecipe(id, {
      recipeName: req.body.recipe_name,
      preChecksConfig: req.body.pre_checks_config,
      cloneSteps: req.body.clone_steps,
      postSteps: req.body.post_steps,
      sourceConnectionId: req.body.source_connection_id,
      targetConnectionId: req.body.target_connection_id,
    });

    res.json({ success: true, recipe: updated });
  } catch (err) {
    console.error('[ebs-clone] update recipe error:', err.message);
    res.status(500).json({ error: 'Failed to update recipe' });
  }
});

// ── DELETE /api/ebs-clone/recipes/:id ────────────────────────────────────────

router.delete('/api/ebs-clone/recipes/:id', requireAuth, requireRole('senior_dba'), async (req, res) => {
  try {
    const id = parseInt(req.params.id, 10);
    const existing = await recipeDb.getRecipeById(id);
    if (!existing) return res.status(404).json({ error: 'Recipe not found' });
    if (existing.company_id !== companyId(req.user)) return res.status(403).json({ error: 'Access denied' });
    await recipeDb.deleteRecipe(id);
    res.json({ success: true });
  } catch (err) {
    console.error('[ebs-clone] delete recipe error:', err.message);
    res.status(500).json({ error: 'Failed to delete recipe' });
  }
});

// ── GET /api/ebs-clone/history ────────────────────────────────────────────────

router.get('/api/ebs-clone/history', requireAuth, async (req, res) => {
  try {
    const { recipe_id, limit } = req.query;
    const history = await recipeDb.listHistory(companyId(req.user), {
      recipeId: recipe_id ? parseInt(recipe_id, 10) : undefined,
      limit: limit ? parseInt(limit, 10) : 50,
    });
    res.json({ success: true, history });
  } catch (err) {
    console.error('[ebs-clone] history error:', err.message);
    res.status(500).json({ error: 'Failed to fetch history' });
  }
});

// ── POST /api/ebs-clone/history/start ────────────────────────────────────────
// Body: { recipe_id?, source_connection_id, target_connection_id, total_steps }

router.post('/api/ebs-clone/history/start', requireAuth, requireRole('senior_dba'), async (req, res) => {
  try {
    const { recipe_id, source_connection_id, target_connection_id, total_steps } = req.body;
    const run = await recipeDb.startRun({
      recipeId: recipe_id ? parseInt(recipe_id, 10) : null,
      companyId: companyId(req.user),
      sourceConnectionId: source_connection_id ? parseInt(source_connection_id, 10) : null,
      targetConnectionId: target_connection_id ? parseInt(target_connection_id, 10) : null,
      startedBy: req.user.id,
      totalSteps: total_steps || 0,
    });
    res.json({ success: true, run });
  } catch (err) {
    console.error('[ebs-clone] start run error:', err.message);
    res.status(500).json({ error: 'Failed to start clone run' });
  }
});

// ── POST /api/ebs-clone/history/:id/finish ───────────────────────────────────
// Body: { status: 'success'|'failed'|'aborted', error_message? }

router.post('/api/ebs-clone/history/:id/finish', requireAuth, requireRole('senior_dba'), async (req, res) => {
  try {
    const runId = parseInt(req.params.id, 10);
    const { status, error_message } = req.body;
    if (!['success', 'failed', 'aborted'].includes(status)) {
      return res.status(400).json({ error: 'Invalid status' });
    }
    await recipeDb.finishRun(runId, { status, errorMessage: error_message });
    res.json({ success: true });
  } catch (err) {
    console.error('[ebs-clone] finish run error:', err.message);
    res.status(500).json({ error: 'Failed to finish clone run' });
  }
});

// ── GET /api/ebs-clone/ssh-targets/:connId ───────────────────────────────────

router.get('/api/ebs-clone/ssh-targets/:connId', requireAuth, async (req, res) => {
  try {
    const connId = parseInt(req.params.connId, 10);
    // Return user-owned targets, filtering to those linked to this connection (or unlinked)
    const all = await sshDb.listTargetsByUser(req.user.id);
    const targets = all.filter(t => !t.connection_id || t.connection_id === connId);
    res.json({ success: true, targets: targets.map(t => ({
      id: t.id,
      host: t.host,
      port: t.port,
      os_user: t.os_user,
      role: t.role,
      auth_method: t.auth_method,
      display_label: `${t.os_user}@${t.host} (${t.role})`,
    }))});
  } catch (err) {
    console.error('[ebs-clone] ssh-targets error:', err.message);
    res.status(500).json({ error: 'Failed to fetch SSH targets' });
  }
});

// ── POST /api/ebs-clone/execute-step ─────────────────────────────────────────
// Executes a single clone wizard step via whitelisted SSH commands.
// Body: { target_id, command_key, run_id?, step_index? }
//
// For custom/free-text steps the command is run through 'clone.custom' command_key
// which executes the pre-parameterized template. Custom scripts are only allowed
// when the step type is 'custom' and require senior_dba role.

router.post('/api/ebs-clone/execute-step', requireAuth, requireRole('senior_dba'), async (req, res) => {
  try {
    const { target_id, connection_id, command_key, vars, run_id, step_index, confirmed } = req.body;

    if (!command_key) {
      return res.status(400).json({ error: 'command_key required' });
    }
    // target_id is now OPTIONAL. It used to be mandatory, which made the whole
    // wizard unreachable on a standard install: install.sh never configures an
    // SSH target because modern agents are outbound-poll only. With no target we
    // run the step through the agent, which owns the shell for every clone
    // command in its own generated catalog.
    if (!target_id && !connection_id) {
      return res.status(400).json({ error: 'connection_id required when no SSH target is configured' });
    }

    const stepResult = await runCloneStep({
      connectionId: connection_id,
      targetId:     target_id,
      commandKey:   command_key,
      vars:         vars || {},
      userId:       req.user.id,
      confirmed:    confirmed === true,
    });

    if (run_id && step_index !== undefined) {
      try {
        await recipeDb.appendStepResult(parseInt(run_id, 10), step_index, stepResult);
      } catch (e) {
        console.error('[ebs-clone] appendStepResult error:', e.message);
      }
    }

    res.json({ success: true, result: stepResult });
  } catch (err) {
    console.error('[ebs-clone] execute-step error:', err.message);
    res.status(500).json({ error: err.message || 'Step execution failed' });
  }
});

module.exports = router;
