/**
 * db/outreach-lock.js
 *
 * Owns: All DB queries for outreach_attempts (hard-lock audit log).
 * Does NOT own: outreach_batches, outreach_recipients, outreach_send_log — those live in db/outreach.js.
 */

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

/**
 * Log every outreach send attempt — blocked or allowed.
 * Called by the hard-lock guard in services/outreach-mailer BEFORE Postmark is touched.
 */
async function logOutreachAttempt({
  attemptedTo,
  attemptedSubject,
  attemptedBy,
  blocked,
  unlockTokenPresent,
  blockedReason,
  metadata,
}) {
  const ins = await pool.query(
    `INSERT INTO outreach_attempts
       (attempted_to, attempted_subject, attempted_by, blocked, unlock_token_present, blocked_reason, metadata)
     VALUES (:p1, :p2, :p3, :p4, :p5, :p6, :p7)
     RETURNING id INTO :out_id`,
    {
      p1: attemptedTo || null,
      p2: attemptedSubject || null,
      p3: attemptedBy || 'unknown',
      p4: pool.bool(blocked),
      p5: pool.bool(unlockTokenPresent || false),
      p6: blockedReason || null,
      p7: metadata ? JSON.stringify(metadata) : null,
      out_id: pool.outNumber(),
    }
  );
  return ins.outBinds.out_id[0];
}

/**
 * Get the most recent N outreach attempts (newest first).
 */
async function getRecentAttempts(limit = 50) {
  const r = await pool.query(
    `SELECT * FROM outreach_attempts ORDER BY attempted_at DESC FETCH FIRST :p1 ROWS ONLY`,
    { p1: limit }
  );
  return r.rows;
}

/**
 * Count attempts in the last 24 hours (blocked vs allowed).
 */
async function getAttemptStats() {
  const r = await pool.query(
    `SELECT
       COUNT(CASE WHEN blocked = TRUE  THEN 1 END) AS blocked_count,
       COUNT(CASE WHEN blocked = FALSE THEN 1 END) AS allowed_count,
       COUNT(*) AS total_count,
       MAX(attempted_at) AS last_attempt_at
     FROM outreach_attempts
     WHERE attempted_at > CURRENT_TIMESTAMP - INTERVAL '24' HOUR`
  );
  return r.rows[0];
}

module.exports = {
  logOutreachAttempt,
  getRecentAttempts,
  getAttemptStats,
};
