/**
 * db/user-preferences.js — per-user notification preferences + HC email lookup helpers.
 *
 * Owns: user_preferences table CRUD; helper query to resolve user+connection for HC emails.
 * Does NOT own: email sending logic (see services/hc-completion-email.js).
 */

'use strict';

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

/**
 * Get preferences for a user. Returns defaults if no row exists yet.
 * @param {number} userId
 * @returns {Promise<{hc_completion_email: boolean, last_hc_email_sent_at: Date|null}>}
 */
async function getPreferences(userId) {
  const result = await pool.query(
    `SELECT hc_completion_email, last_hc_email_sent_at FROM user_preferences WHERE user_id = :p1`,
    { p1: userId }
  );
  if (result.rows.length === 0) {
    return { hc_completion_email: true, last_hc_email_sent_at: null };
  }
  return result.rows[0];
}

/**
 * Upsert hc_completion_email preference for a user.
 * @param {number} userId
 * @param {boolean} enabled
 */
async function setHcCompletionEmail(userId, enabled) {
  await pool.query(
    `MERGE INTO user_preferences t
     USING (SELECT :p1 AS user_id, :p2 AS hc_completion_email FROM DUAL) s
        ON (t.user_id = s.user_id)
     WHEN MATCHED THEN UPDATE SET hc_completion_email = s.hc_completion_email, updated_at = CURRENT_TIMESTAMP
     WHEN NOT MATCHED THEN
       INSERT (user_id, hc_completion_email, updated_at)
       VALUES (s.user_id, s.hc_completion_email, CURRENT_TIMESTAMP)`,
    { p1: userId, p2: pool.bool(enabled) }
  );
}

/**
 * Stamp last_hc_email_sent_at to NOW() for throttle tracking.
 * @param {number} userId
 */
async function stampHcEmailSent(userId) {
  await pool.query(
    `MERGE INTO user_preferences t
     USING (SELECT :p1 AS user_id FROM DUAL) s
        ON (t.user_id = s.user_id)
     WHEN MATCHED THEN UPDATE SET last_hc_email_sent_at = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP
     WHEN NOT MATCHED THEN
       INSERT (user_id, last_hc_email_sent_at, updated_at)
       VALUES (s.user_id, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)`,
    { p1: userId }
  );
}

/**
 * Resolve the owning user for a connection, returning email/name for email sending.
 * @param {number} connectionId
 * @returns {Promise<{user_id, email, name, connection_name}|null>}
 */
async function getUserForConnection(connectionId) {
  const result = await pool.query(
    `SELECT oc.user_id, u.email, u.name, oc.name AS connection_name, oc.server_type
     FROM oracle_connections oc
     JOIN users u ON u.id = oc.user_id
     WHERE oc.id = :p1`,
    { p1: connectionId }
  );
  return result.rows[0] || null;
}

/**
 * Get a completed health check row for email purposes.
 * @param {number} hcId
 * @returns {Promise<{overall_score, is_demo, completed_at, connection_name, metrics}|null>}
 */
async function getHealthCheckForEmail(hcId) {
  const result = await pool.query(
    `SELECT overall_score, is_demo, completed_at, connection_name, metrics FROM health_checks WHERE id = :p1`,
    { p1: hcId }
  );
  return result.rows[0] || null;
}

module.exports = { getPreferences, setHcCompletionEmail, stampHcEmailSent, getUserForConnection, getHealthCheckForEmail };
