/**
 * db/tuneops-notifications.js — TuneOps email notification storage.
 *
 * Owns: tuneops_notification_prefs, tuneops_notification_mutes,
 *       tuneops_notification_log tables.
 * Does NOT own: ticket data, user auth, sending the emails.
 */

'use strict';

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

// ── Preferences ──────────────────────────────────────────────────────────────

/**
 * Get prefs for a user. Returns default values if no row exists.
 */
async function getPrefs(userId) {
  const { rows } = await pool.query(
    `SELECT * FROM tuneops_notification_prefs WHERE user_id = :p1`,
    { p1: userId }
  );
  if (rows[0]) return rows[0];
  // Default: all enabled, threshold = 'warning'
  return {
    user_id: userId,
    notifications_enabled: true,
    severity_threshold: 'warning',
  };
}

/**
 * Upsert notification prefs for a user.
 * notificationsEnabled: boolean
 * severityThreshold: 'info' | 'warning' | 'critical'
 */
async function upsertPrefs(userId, { notificationsEnabled, severityThreshold }) {
  await pool.query(
    `MERGE INTO tuneops_notification_prefs t
     USING (SELECT :p1 AS user_id, :p2 AS notifications_enabled, :p3 AS severity_threshold FROM DUAL) s
        ON (t.user_id = s.user_id)
     WHEN MATCHED THEN UPDATE SET
       notifications_enabled = s.notifications_enabled,
       severity_threshold    = s.severity_threshold,
       updated_at            = CURRENT_TIMESTAMP
     WHEN NOT MATCHED THEN
       INSERT (user_id, notifications_enabled, severity_threshold, updated_at)
       VALUES (s.user_id, s.notifications_enabled, s.severity_threshold, CURRENT_TIMESTAMP)`,
    { p1: userId, p2: pool.bool(notificationsEnabled), p3: severityThreshold }
  );
  const { rows } = await pool.query(`SELECT * FROM tuneops_notification_prefs WHERE user_id = :p1`, { p1: userId });
  return rows[0];
}

// ── Mutes ─────────────────────────────────────────────────────────────────────

/**
 * Check whether a user has an active mute on a connection.
 */
async function isConnectionMuted(userId, connectionId) {
  const { rows } = await pool.query(
    `SELECT 1 FROM tuneops_notification_mutes
     WHERE user_id = :p1
       AND connection_id = :p2
       AND muted_until > CURRENT_TIMESTAMP
     FETCH FIRST 1 ROWS ONLY`,
    { p1: userId, p2: connectionId }
  );
  return rows.length > 0;
}

/**
 * Mute notifications for a connection for the given number of hours (default 24).
 */
async function muteConnection(userId, connectionId, hours = 24) {
  try {
    await pool.query(
      `INSERT INTO tuneops_notification_mutes (user_id, connection_id, muted_until)
       VALUES (:p1, :p2, CURRENT_TIMESTAMP + NUMTODSINTERVAL(:p3, 'HOUR'))`,
      { p1: userId, p2: connectionId, p3: hours }
    );
  } catch (err) {
    if (err.errorNum !== 1) throw err;   // ORA-00001: already muted (DO NOTHING)
  }
}

// ── Notification log ──────────────────────────────────────────────────────────

/**
 * Count emails sent for a specific ticket in the last hour.
 * Used for rate-limiting (max 10 per ticket per hour).
 */
async function countRecentForTicket(ticketNumber) {
  const { rows } = await pool.query(
    `SELECT COUNT(*) AS cnt
     FROM tuneops_notification_log
     WHERE ticket_number = :p1
       AND sent_at > CURRENT_TIMESTAMP - INTERVAL '1' HOUR`,
    { p1: ticketNumber }
  );
  return rows[0]?.cnt || 0;
}

/**
 * Check whether this exact (ticket, event, recipient) was already sent within
 * the dedup window (default 1 hour).
 */
async function isDuplicate(ticketNumber, eventType, recipientEmail, windowMinutes = 60) {
  const { rows } = await pool.query(
    `SELECT 1 FROM tuneops_notification_log
     WHERE ticket_number    = :p1
       AND event_type       = :p2
       AND recipient_email  = :p3
       AND sent_at > CURRENT_TIMESTAMP - NUMTODSINTERVAL(:p4, 'MINUTE')
     FETCH FIRST 1 ROWS ONLY`,
    { p1: ticketNumber, p2: eventType, p3: recipientEmail, p4: windowMinutes }
  );
  return rows.length > 0;
}

/**
 * Record a sent notification.
 */
async function logSend(ticketNumber, eventType, recipientEmail, metadata = null) {
  await pool.query(
    `INSERT INTO tuneops_notification_log (ticket_number, event_type, recipient_email, metadata)
     VALUES (:p1, :p2, :p3, :p4)`,
    { p1: ticketNumber, p2: eventType, p3: recipientEmail, p4: metadata ? JSON.stringify(metadata) : null }
  );
}

module.exports = {
  getPrefs,
  upsertPrefs,
  isConnectionMuted,
  muteConnection,
  countRecentForTicket,
  isDuplicate,
  logSend,
};
