/**
 * db/outreach.js
 *
 * Owns: All DB queries for outreach_batches, outreach_recipients, outreach_send_log.
 * Does NOT own: sending emails, HTTP routes, admin auth, Postmark calls.
 */

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

// ─── Batches ─────────────────────────────────────────────────────────────────

async function getBatch(id) {
  const r = await pool.query(
    `SELECT b.*, u.email AS approved_by_email
       FROM outreach_batches b
       LEFT JOIN users u ON u.id = b.approved_by
      WHERE b.id = :p1`,
    { p1: id }
  );
  return r.rows[0] || null;
}

async function getBatchByName(name) {
  const r = await pool.query(
    'SELECT * FROM outreach_batches WHERE name = :p1',
    { p1: name }
  );
  return r.rows[0] || null;
}

async function listBatches() {
  const r = await pool.query(
    `SELECT b.*, u.email AS approved_by_email,
            COALESCE(agg.recipient_count, 0)  AS recipient_count,
            COALESCE(agg.authorized_count, 0) AS authorized_count,
            COALESCE(agg.sent_count, 0)       AS sent_count
       FROM outreach_batches b
       LEFT JOIN users u ON u.id = b.approved_by
       LEFT JOIN (
         SELECT batch_id,
                COUNT(id) AS recipient_count,
                COUNT(CASE WHEN send_authorized = TRUE THEN 1 END) AS authorized_count,
                COUNT(CASE WHEN status = 'SENT'      THEN 1 END) AS sent_count
           FROM outreach_recipients
          GROUP BY batch_id
       ) agg ON agg.batch_id = b.id
      ORDER BY b.created_at DESC`
  );
  return r.rows;
}

async function createBatch({ name, templateSubject, templateBody, sendWindowStart, sendWindowEnd, notes }) {
  const ins = await pool.query(
    `INSERT INTO outreach_batches
       (name, template_subject, template_body, send_window_start, send_window_end, notes)
     VALUES (:p1, :p2, :p3, :p4, :p5, :p6)
     RETURNING id INTO :out_id`,
    { p1: name, p2: templateSubject, p3: pool.clob(templateBody),
      p4: pool.tstz(sendWindowStart || null), p5: pool.tstz(sendWindowEnd || null),
      p6: notes || null, out_id: pool.outNumber() }
  );
  const r = await pool.query(`SELECT * FROM outreach_batches WHERE id = :p1`, { p1: ins.outBinds.out_id[0] });
  return r.rows[0];
}

// Approve a batch. approved_by is the user.id of the operator pressing the button.
async function approveBatch(batchId, approvedByUserId) {
  const upd = await pool.query(
    `UPDATE outreach_batches
        SET approval_status = 'APPROVED',
            approved_by = :p1,
            approved_at = CURRENT_TIMESTAMP,
            updated_at = CURRENT_TIMESTAMP
      WHERE id = :p2
        AND approval_status = 'PENDING'`,
    { p1: approvedByUserId, p2: batchId }
  );
  if (!upd.rowCount) return null;
  const r = await pool.query(`SELECT * FROM outreach_batches WHERE id = :p1`, { p1: batchId });
  return r.rows[0] || null;
}

async function rejectBatch(batchId) {
  const upd = await pool.query(
    `UPDATE outreach_batches
        SET approval_status = 'REJECTED', updated_at = CURRENT_TIMESTAMP
      WHERE id = :p1`,
    { p1: batchId }
  );
  if (!upd.rowCount) return null;
  const r = await pool.query(`SELECT * FROM outreach_batches WHERE id = :p1`, { p1: batchId });
  return r.rows[0] || null;
}

// ─── Recipients ───────────────────────────────────────────────────────────────

async function getRecipientsForBatch(batchId) {
  const r = await pool.query(
    `SELECT * FROM outreach_recipients WHERE batch_id = :p1 ORDER BY created_at ASC`,
    { p1: batchId }
  );
  return r.rows;
}

async function getRecipient(recipientId) {
  const r = await pool.query(
    'SELECT * FROM outreach_recipients WHERE id = :p1',
    { p1: recipientId }
  );
  return r.rows[0] || null;
}

async function addRecipient(batchId, { email, name, company }) {
  let ins;
  try {
    ins = await pool.query(
      `INSERT INTO outreach_recipients (batch_id, email, name, company)
       VALUES (:p1, :p2, :p3, :p4)
       RETURNING id INTO :out_id`,
      { p1: batchId, p2: email.toLowerCase().trim(), p3: name || null, p4: company || null,
        out_id: pool.outNumber() }
    );
  } catch (err) {
    if (err.errorNum === 1) return null;   // ORA-00001: already a recipient (DO NOTHING)
    throw err;
  }
  const r = await pool.query(`SELECT * FROM outreach_recipients WHERE id = :p1`, { p1: ins.outBinds.out_id[0] });
  return r.rows[0] || null;
}

async function authorizeRecipient(recipientId) {
  // outreach_recipients has NO updated_at column (verified in Oracle AND Neon);
  // the sibling writes markRecipientSent/markRecipientBlocked never touched it.
  // Dropped the bad SET (was pre-existing bug #7).
  const upd = await pool.query(
    `UPDATE outreach_recipients
        SET send_authorized = TRUE
      WHERE id = :p1`,
    { p1: recipientId }
  );
  if (!upd.rowCount) return null;
  const r = await pool.query(`SELECT * FROM outreach_recipients WHERE id = :p1`, { p1: recipientId });
  return r.rows[0] || null;
}

async function markRecipientSent(recipientId) {
  const upd = await pool.query(
    `UPDATE outreach_recipients
        SET status = 'SENT', sent_at = CURRENT_TIMESTAMP
      WHERE id = :p1`,
    { p1: recipientId }
  );
  if (!upd.rowCount) return null;
  const r = await pool.query(`SELECT * FROM outreach_recipients WHERE id = :p1`, { p1: recipientId });
  return r.rows[0] || null;
}

async function markRecipientBlocked(recipientId) {
  await pool.query(
    `UPDATE outreach_recipients SET status = 'BLOCKED' WHERE id = :p1`,
    { p1: recipientId }
  );
}

// ─── Send Log ─────────────────────────────────────────────────────────────────

async function logSendAttempt({
  batchId,
  recipientId,
  recipientEmail,
  gateResult,        // 'ALLOWED' | 'BLOCKED'
  blockedReason,     // e.g. 'ENV_DISABLED', 'BATCH_NOT_APPROVED', 'RECIPIENT_NOT_AUTHORIZED'
  gateFailed,        // which gate index ('gate1', 'gate2', 'gate3') or null
  postmarkMessageId,
  errorMessage,
  metadata,
}) {
  const ins = await pool.query(
    `INSERT INTO outreach_send_log
       (batch_id, recipient_id, recipient_email, gate_result,
        blocked_reason, gate_failed, postmark_message_id, error_message, metadata)
     VALUES (:p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9)
     RETURNING id INTO :out_id`,
    {
      p1: batchId || null,
      p2: recipientId || null,
      p3: recipientEmail || null,
      p4: gateResult,
      p5: blockedReason || null,
      p6: gateFailed || null,
      p7: postmarkMessageId || null,
      p8: errorMessage || null,
      p9: metadata ? JSON.stringify(metadata) : null,
      out_id: pool.outNumber(),
    }
  );
  return ins.outBinds.out_id[0];
}

async function getRecentSendLog(limit = 100) {
  const r = await pool.query(
    `SELECT l.*, b.name AS batch_name
       FROM outreach_send_log l
       LEFT JOIN outreach_batches b ON b.id = l.batch_id
      ORDER BY l.attempted_at DESC
      FETCH FIRST :p1 ROWS ONLY`,
    { p1: limit }
  );
  return r.rows;
}

module.exports = {
  // Batches
  getBatch,
  getBatchByName,
  listBatches,
  createBatch,
  approveBatch,
  rejectBatch,
  // Recipients
  getRecipientsForBatch,
  getRecipient,
  addRecipient,
  authorizeRecipient,
  markRecipientSent,
  markRecipientBlocked,
  // Send log
  logSendAttempt,
  getRecentSendLog,
};
