'use strict';
// services/mailLogStore.js — records every email the portal attempts to
// send (welcome/update mails, stage-change notifications, deviation
// approve/reject, etc.) so admins can confirm delivery without digging
// through server console logs. Instrumented centrally in services/mailer.js
// so every call site is covered automatically — nothing else needs to
// change when a new notification type is added.
const { getPool } = require('./appSqlPool');

const TABLE = `[dbo].[ZMAIL_LOG]`;

async function exec(sqlQuery, params = []) {
  const pool = await getPool();
  const req  = pool.request();
  let i = 0;
  const text = sqlQuery.replace(/\?/g, () => { const n = `p${i}`; req.input(n, params[i]); i++; return `@${n}`; });
  const result = await req.query(text);
  return result.recordset || [];
}
function isAlreadyExists(e) {
  const m = (e.message || '').toLowerCase();
  return m.includes('already exists') || m.includes('there is already an object');
}

async function bootstrap() {
  console.log('[MAIL-LOG] Checking table', TABLE, '…');
  await exec(`
    CREATE TABLE ${TABLE} (
      [ID]         BIGINT IDENTITY(1,1) PRIMARY KEY,
      [SENT_AT]    DATETIME2 NOT NULL,
      [TO_EMAILS]  NVARCHAR(1000),
      [SUBJECT]    NVARCHAR(400),
      [STATUS]     NVARCHAR(20) NOT NULL,
      [ERROR]      NVARCHAR(1000),
      [MESSAGE_ID] NVARCHAR(200)
    )
  `).catch(e => { if (isAlreadyExists(e)) { console.log('[MAIL-LOG] Table exists — OK'); } else throw e; });
  await exec(`CREATE INDEX IX_ZMAIL_LOG_AT ON ${TABLE} ([SENT_AT] DESC)`).catch(() => {});
  console.log('[MAIL-LOG] ✅ Ready');
}

// Never throws — a logging failure must never break the mail send it's
// recording, or the request that triggered it.
async function record({ to, subject, status, error, messageId }) {
  try {
    const now = new Date().toISOString().replace('T', ' ').replace('Z', '').substring(0, 23);
    const toStr = (Array.isArray(to) ? to : [to]).filter(Boolean).join(', ');
    await exec(
      `INSERT INTO ${TABLE} ([SENT_AT],[TO_EMAILS],[SUBJECT],[STATUS],[ERROR],[MESSAGE_ID]) VALUES (?,?,?,?,?,?)`,
      [now, toStr.slice(0, 1000), (subject || '').slice(0, 400), status || 'UNKNOWN', (error || '').slice(0, 1000), messageId || null]
    );
  } catch (err) { console.warn('[MAIL-LOG] record failed (non-fatal):', err.message); }
}

function fromRow(r) {
  return {
    id: Number(r.ID), at: r.SENT_AT ? new Date(r.SENT_AT).toISOString() : null,
    to: r.TO_EMAILS || '', subject: r.SUBJECT || '', status: r.STATUS || '',
    error: r.ERROR || '', messageId: r.MESSAGE_ID || '',
  };
}

async function list({ from, to, status, q, top = 50, skip = 0 } = {}) {
  const where = []; const params = [];
  if (from)   { where.push(`[SENT_AT] >= ?`); params.push(String(from).slice(0, 10) + ' 00:00:00'); }
  if (to)     { where.push(`[SENT_AT] <= ?`); params.push(String(to).slice(0, 10) + ' 23:59:59'); }
  if (status) { where.push(`[STATUS] = ?`); params.push(status); }
  if (q) { where.push(`([TO_EMAILS] LIKE ? OR [SUBJECT] LIKE ?)`); const like = `%${q}%`; params.push(like, like); }
  const w = where.length ? `WHERE ${where.join(' AND ')}` : '';
  const t = Math.min(200, parseInt(top) || 50);
  const s = Math.max(0, parseInt(skip) || 0);
  const rows = await exec(`SELECT * FROM ${TABLE} ${w} ORDER BY [ID] DESC OFFSET ${s} ROWS FETCH NEXT ${t} ROWS ONLY`, params);
  const cnt = await exec(`SELECT COUNT(*) AS N FROM ${TABLE} ${w}`, params);
  return { data: rows.map(fromRow), total: Number(cnt[0] && cnt[0].N) || 0 };
}

module.exports = { bootstrap, record, list };