/**
 * db/clone-recipes.js — clone_recipes and clone_history table queries.
 *
 * Owns: CRUD for clone_recipes (wizard recipes) and clone_history (run log).
 * Does NOT own: SSH step execution (services/ssh-executor.js),
 *               route handling (routes/ebs-clone.js), Oracle connection auth.
 */

'use strict';

const pool = require('./index');

// ── Clone Recipes ─────────────────────────────────────────────────────────────

/**
 * List recipes for a company, newest first.
 */
// `wizard` scopes the list. Without it both wizards filtered on company_id alone,
// so a DB recipe appeared in EBS Clone's Saved Recipes and vice versa — the same
// bug class as the unfiltered connection pickers, one layer down. Rows predating
// the column (wizard IS NULL) are shown to BOTH rather than hidden from both:
// losing sight of a saved recipe is worse than seeing one that is not yours.
async function listRecipes(companyId, wizard) {
  const { rows } = await pool.query(
    `SELECT r.*,
            s.name AS source_display_name,
            t.name AS target_display_name,
            u.email        AS created_by_email
     FROM clone_recipes r
     LEFT JOIN oracle_connections s ON s.id = r.source_connection_id
     LEFT JOIN oracle_connections t ON t.id = r.target_connection_id
     LEFT JOIN users u ON u.id = r.created_by
     WHERE r.company_id = :p1
       AND (:p2 IS NULL OR r.wizard IS NULL OR r.wizard = :p2)
     ORDER BY r.updated_at DESC`,
    { p1: companyId, p2: wizard || null }
  );
  return rows;
}

/**
 * Get a single recipe by id. Returns null if not found.
 */
async function getRecipeById(id) {
  const { rows } = await pool.query(
    `SELECT r.*,
            s.name AS source_display_name,
            t.name AS target_display_name,
            u.email        AS created_by_email
     FROM clone_recipes r
     LEFT JOIN oracle_connections s ON s.id = r.source_connection_id
     LEFT JOIN oracle_connections t ON t.id = r.target_connection_id
     LEFT JOIN users u ON u.id = r.created_by
     WHERE r.id = :p1`,
    { p1: id }
  );
  return rows[0] || null;
}

/**
 * Find the most recent recipe for a source→target pair within a company.
 */
async function findRecipeByPair(companyId, sourceConnectionId, targetConnectionId) {
  const { rows } = await pool.query(
    `SELECT * FROM clone_recipes
     WHERE company_id = :p1
       AND source_connection_id = :p2
       AND target_connection_id = :p3
     ORDER BY updated_at DESC
     FETCH FIRST 1 ROWS ONLY`,
    { p1: companyId, p2: sourceConnectionId, p3: targetConnectionId }
  );
  return rows[0] || null;
}

/**
 * Create a new recipe. Returns the created row.
 */
async function createRecipe({
  companyId,
  sourceConnectionId,
  targetConnectionId,
  // Tier-aware connections. An EBS clone spans BOTH tiers, so one connection per
  // side cannot describe it. The legacy pair is still written, so a rollback and
  // any older reader keep working.
  sourceDbConnectionId   = null,
  sourceAppsConnectionId = null,
  targetDbConnectionId   = null,
  targetAppsConnectionId = null,
  wizard                 = null,
  recipeName,
  preChecksConfig = {},
  cloneSteps = [],
  postSteps = [],
  createdBy,
}) {
  const ins = await pool.query(
    `INSERT INTO clone_recipes
       (company_id, source_connection_id, target_connection_id,
        source_db_connection_id, source_apps_connection_id,
        target_db_connection_id, target_apps_connection_id, wizard,
        recipe_name, pre_checks_config, clone_steps, post_steps, created_by)
     VALUES (:p1, :p2, :p3, :p9, :p10, :p11, :p12, :p13, :p4, :p5, :p6, :p7, :p8)
     RETURNING id INTO :out_id`,
    {
      p1: companyId,
      p2: sourceConnectionId || null,
      p3: targetConnectionId || null,
      p4: recipeName,
      p5: JSON.stringify(preChecksConfig),
      p6: JSON.stringify(cloneSteps),
      p7: JSON.stringify(postSteps),
      p8: createdBy || null,
      p9:  sourceDbConnectionId   || null,
      p10: sourceAppsConnectionId || null,
      p11: targetDbConnectionId   || null,
      p12: targetAppsConnectionId || null,
      p13: wizard || null,
      out_id: pool.outNumber(),
    }
  );
  return getRecipeById(ins.outBinds.out_id[0]);
}

/**
 * Update recipe steps and metadata. Returns the updated row.
 */
async function updateRecipe(id, {
  recipeName,
  preChecksConfig,
  cloneSteps,
  postSteps,
  sourceConnectionId,
  targetConnectionId,
  sourceDbConnectionId,
  sourceAppsConnectionId,
  targetDbConnectionId,
  targetAppsConnectionId,
  wizard,
}) {
  const fields = [];
  const binds = {};

  if (recipeName !== undefined)        { fields.push(`recipe_name = :p_name`);        binds.p_name = recipeName; }
  if (preChecksConfig !== undefined)   { fields.push(`pre_checks_config = :p_pre`);   binds.p_pre = JSON.stringify(preChecksConfig); }
  if (cloneSteps !== undefined)        { fields.push(`clone_steps = :p_steps`);       binds.p_steps = JSON.stringify(cloneSteps); }
  if (postSteps !== undefined)         { fields.push(`post_steps = :p_post`);         binds.p_post = JSON.stringify(postSteps); }
  if (sourceConnectionId !== undefined){ fields.push(`source_connection_id = :p_src`); binds.p_src = sourceConnectionId || null; }
  if (targetConnectionId !== undefined){ fields.push(`target_connection_id = :p_tgt`); binds.p_tgt = targetConnectionId || null; }
  // Tier-aware connections. Each is only touched when the caller passes it, so
  // completing one missing tier on a part-migrated recipe cannot blank the others.
  if (sourceDbConnectionId   !== undefined) { fields.push(`source_db_connection_id = :p_sdb`);    binds.p_sdb = sourceDbConnectionId   || null; }
  if (sourceAppsConnectionId !== undefined) { fields.push(`source_apps_connection_id = :p_sapp`); binds.p_sapp = sourceAppsConnectionId || null; }
  if (targetDbConnectionId   !== undefined) { fields.push(`target_db_connection_id = :p_tdb`);    binds.p_tdb = targetDbConnectionId   || null; }
  if (targetAppsConnectionId !== undefined) { fields.push(`target_apps_connection_id = :p_tapp`); binds.p_tapp = targetAppsConnectionId || null; }
  if (wizard                 !== undefined) { fields.push(`wizard = :p_wiz`);                     binds.p_wiz = wizard || null; }

  if (fields.length === 0) return getRecipeById(id);

  fields.push(`updated_at = CURRENT_TIMESTAMP`);
  binds.p_id = id;

  const upd = await pool.query(
    `UPDATE clone_recipes SET ${fields.join(', ')} WHERE id = :p_id`,
    binds
  );
  if (!upd.rowCount) return null;
  return getRecipeById(id);
}

/**
 * Delete a recipe by id.
 */
async function deleteRecipe(id) {
  await pool.query(`DELETE FROM clone_recipes WHERE id = :p1`, { p1: id });
}

/**
 * Update last_run_at + last_run_status on a recipe.
 */
async function touchRecipeRun(id, status) {
  await pool.query(
    `UPDATE clone_recipes SET last_run_at = CURRENT_TIMESTAMP, last_run_status = :p1, updated_at = CURRENT_TIMESTAMP WHERE id = :p2`,
    { p1: status, p2: id }
  );
}

// ── Clone History ─────────────────────────────────────────────────────────────

/**
 * Start a new history run for a recipe. Returns the created row.
 */
async function startRun({
  recipeId,
  companyId,
  sourceConnectionId,
  targetConnectionId,
  startedBy,
  totalSteps,
}) {
  const ins = await pool.query(
    `INSERT INTO clone_history
       (recipe_id, company_id, source_connection_id, target_connection_id,
        started_by, status, total_steps, step_results)
     VALUES (:p1, :p2, :p3, :p4, :p5, 'running', :p6, '[]')
     RETURNING id INTO :out_id`,
    { p1: recipeId || null, p2: companyId, p3: sourceConnectionId || null,
      p4: targetConnectionId || null, p5: startedBy || null, p6: totalSteps || 0,
      out_id: pool.outNumber() }
  );
  const { rows } = await pool.query(
    `SELECT * FROM clone_history WHERE id = :p1`, { p1: ins.outBinds.out_id[0] });
  return rows[0];
}

/**
 * Append a step result to a running history row.
 */
async function appendStepResult(runId, stepIndex, result) {
  // PostgreSQL appended in one statement (step_results || $1::jsonb).
  // JSON_TRANSFORM is banned here (OSON v2 is unreadable by node-oracledb 6.10),
  // so this is a read-modify-write held under SELECT ... FOR UPDATE. The row lock
  // matters: clone steps report progress concurrently, and without it two step
  // results racing would each read the same array and one would be lost.
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const { rows } = await client.query(
      `SELECT step_results FROM clone_history WHERE id = :p1 FOR UPDATE`, { p1: runId });
    if (!rows.length) { await client.query('ROLLBACK'); return; }
    const current = Array.isArray(rows[0].step_results) ? rows[0].step_results : [];
    await client.query(
      // clone_history has no updated_at column (pre-existing bug #4 addendum) —
      // dropped so per-step progress actually persists.
      `UPDATE clone_history
       SET step_results = :p1,
           current_step = :p2
       WHERE id = :p3`,
      { p1: JSON.stringify(current.concat([result])), p2: stepIndex, p3: runId }
    );
    await client.query('COMMIT');
  } catch (err) {
    await client.query('ROLLBACK').catch(() => {});
    throw err;
  } finally {
    client.release();
  }
}

/**
 * Mark a run complete (success, failed, or aborted).
 */
async function finishRun(runId, { status, errorMessage }) {
  await pool.query(
    `UPDATE clone_history
     SET status = :p1,
         completed_at = CURRENT_TIMESTAMP,
         error_message = :p2,
         duration_ms = ROUND((
           EXTRACT(DAY    FROM (CURRENT_TIMESTAMP - started_at)) * 86400 +
           EXTRACT(HOUR   FROM (CURRENT_TIMESTAMP - started_at)) * 3600 +
           EXTRACT(MINUTE FROM (CURRENT_TIMESTAMP - started_at)) * 60 +
           EXTRACT(SECOND FROM (CURRENT_TIMESTAMP - started_at))
         ) * 1000)
     WHERE id = :p3`,
    { p1: status, p2: errorMessage || null, p3: runId }
  );
}

/**
 * List history runs for a company, newest first. Optional filter by recipe_id.
 */
async function listHistory(companyId, { recipeId, limit = 50 } = {}) {
  const binds = { p_company: companyId, p_limit: limit };
  let where = `WHERE h.company_id = :p_company`;
  if (recipeId) {
    binds.p_recipe = recipeId;
    where += ` AND h.recipe_id = :p_recipe`;
  }

  const { rows } = await pool.query(
    `SELECT h.*,
            s.name AS source_display_name,
            t.name AS target_display_name,
            u.email        AS started_by_email
     FROM clone_history h
     LEFT JOIN oracle_connections s ON s.id = h.source_connection_id
     LEFT JOIN oracle_connections t ON t.id = h.target_connection_id
     LEFT JOIN users u ON u.id = h.started_by
     ${where}
     ORDER BY h.started_at DESC
     FETCH FIRST :p_limit ROWS ONLY`,
    binds
  );
  return rows;
}

/**
 * Get a single history run by id.
 */
async function getRunById(runId) {
  const { rows } = await pool.query(
    `SELECT h.*,
            s.name AS source_display_name,
            t.name AS target_display_name,
            u.email        AS started_by_email
     FROM clone_history h
     LEFT JOIN oracle_connections s ON s.id = h.source_connection_id
     LEFT JOIN oracle_connections t ON t.id = h.target_connection_id
     LEFT JOIN users u ON u.id = h.started_by
     WHERE h.id = :p1`,
    { p1: runId }
  );
  return rows[0] || null;
}

// ── Connection list (shared across clone wizards) ─────────────────────────────

/**
 * List all oracle_connections owned by a user. Used by clone wizard connection pickers.
 * Returns id, display_name, host, port, service_name, connection_type, ebs_detected.
 * (pre-existing bug #4: display_name/ebs_detected/is_demo never existed on
 * oracle_connections — the real columns are name and is_ebs; there is no per-
 * connection is_demo, so the DEMO badge key is simply absent and reads falsy.)
 */
// DB Clone & Scale clones DATABASES: every step in its catalog is db_tier
// (rman, sqlplus, lsnrctl, df). Listing app servers here offered targets whose
// agent refuses every step in the wizard — db-ops.html has filtered the same way
// one line of code away for as long as it has existed.
//
// server_type and ebs_instance_name come back so the picker can group by
// instance instead of showing a flat list, which stops scaling the moment a
// customer has more than a handful of servers.
async function listConnectionsForUser(userId) {
  const { rows } = await pool.query(
    `SELECT id, name AS display_name, host, port, service_name, connection_type,
            is_ebs AS ebs_detected, server_type, ebs_instance_name
     FROM oracle_connections
     WHERE user_id = :p1
       AND (server_type IS NULL OR server_type <> 'apps')
     ORDER BY ebs_instance_name NULLS LAST, name`,
    { p1: userId }
  );
  return rows;
}

module.exports = {
  listRecipes,
  getRecipeById,
  findRecipeByPair,
  createRecipe,
  updateRecipe,
  deleteRecipe,
  touchRecipeRun,
  startRun,
  appendStepResult,
  finishRun,
  listHistory,
  getRunById,
  listConnectionsForUser,
};
