1  /**
  2   * Follow-up Sequence Generator
  3   *
  4   * Creates multi-touch follow-up messages for sites that have been contacted
  5   * but haven't replied. 8-touch cadence over 42 days:
  6   *
  7   *   Touch 1 (Day 0):  Initial outreach — already exists
  8   *   Touch 2 (Day 3):  Different weakness + social proof
  9   *   Touch 3 (Day 7):  ROI framing ("costing you $X/month")
 10   *   Touch 4 (Day 14): Case study + sample report link
 11   *   Touch 5 (Day 21): Quick win offer (free fix)
 12   *   Touch 6 (Day 28): Ad waste angle (if running ads) / competitor gap
 13   *   Touch 7 (Day 35): Authority / testimonial
 14   *   Touch 8 (Day 42): Breakup email ("closing the file")
 15   *
 16   * Each follow-up uses a different value angle to avoid repetition.
 17   * SMS follow-ups are shorter than email follow-ups.
 18   *
 19   * Edge cases handled:
 20   *   - Don't follow up sites that received an inbound reply
 21   *   - Don't follow up opted-out contacts
 22   *   - Respect 3-day per-site cooldown between any sends
 23   *   - Don't generate follow-ups for sites already at max sequence step
 24   */
 25  
 26  import { run, getOne, getAll } from '../utils/db.js';
 27  import { readFileSync, existsSync } from 'fs';
 28  import { join, dirname } from 'path';
 29  import { fileURLToPath } from 'url';
 30  import Logger from '../utils/logger.js';
 31  import { spin } from '../utils/spintax.js';
 32  import { computeGrade } from '../score.js';
 33  import { getCountryByCode } from '../config/countries.js';
 34  import { getScoreDataWithFallback } from '../utils/score-storage.js';
 35  import { getContactsDataWithFallback } from '../utils/contacts-storage.js';
 36  import { checkFixApplied } from '../utils/fix-checker.js';
 37  
 38  const __filename = fileURLToPath(import.meta.url);
 39  const __dirname = dirname(__filename);
 40  const projectRoot = join(__dirname, '../..');
 41  
 42  const logger = new Logger('FollowupGenerator');
 43  
 44  // Touch cadence: days after initial outreach for each follow-up
 45  const TOUCH_CADENCE = {
 46    2: 3,   // Touch 2: 3 days after initial
 47    3: 7,   // Touch 3: 7 days after initial
 48    4: 14,  // Touch 4: 14 days after initial
 49    5: 21,  // Touch 5: 21 days — quick win offer
 50    6: 28,  // Touch 6: 28 days — ad waste / competitor gap
 51    7: 35,  // Touch 7: 35 days — authority / testimonial
 52    8: 42,  // Touch 8: 42 days — breakup
 53  };
 54  
 55  const MAX_SEQUENCE_STEP = 8;
 56  
 57  /**
 58   * Get sites eligible for follow-up generation.
 59   *
 60   * Criteria:
 61   *   - status = 'outreach_sent'
 62   *   - next_followup_at <= now (or next_followup_at IS NULL for first follow-up scheduling)
 63   *   - No inbound reply received
 64   *   - Not opted out
 65   *   - Current max sequence_step < MAX_SEQUENCE_STEP
 66   *   - conversation_status not in closed states
 67   *
 68   * @param {number} limit - Max sites to process
 69   * @returns {Promise<Array>} Sites eligible for follow-up
 70   */
 71  export async function getFollowupEligibleSites(limit = 10) {
 72    const closedStatuses = ['not_interested', 'closed', 'unsubscribed', 'paid', 'report_delivered'];
 73  
 74    const sites = await getAll(
 75      `SELECT
 76         s.id,
 77         s.domain,
 78         s.landing_page_url,
 79         s.keyword,
 80         s.score,
 81         s.grade,
 82         s.country_code,
 83         s.city,
 84         s.state,
 85         s.last_outreach_at,
 86         s.next_followup_at,
 87         s.campaign_tag,
 88         s.free_fix_before_state,
 89         -- Get the max sequence step already generated for this site
 90         (SELECT MAX(m.sequence_step) FROM messages m
 91          WHERE m.site_id = s.id AND m.direction = 'outbound'
 92            AND m.sequence_step IS NOT NULL) as max_step,
 93         -- Get the last sent outbound message timestamp
 94         (SELECT MAX(m.sent_at) FROM messages m
 95          WHERE m.site_id = s.id AND m.direction = 'outbound'
 96            AND m.delivery_status IN ('sent', 'delivered')) as last_sent_at
 97       FROM sites s
 98       WHERE s.status = 'outreach_sent'
 99         -- Due for follow-up: next_followup_at has passed
100         AND s.next_followup_at IS NOT NULL
101         AND s.next_followup_at <= NOW()
102         -- No inbound reply received (no messages with direction='inbound')
103         AND NOT EXISTS (
104           SELECT 1 FROM messages m
105           WHERE m.site_id = s.id
106             AND m.direction = 'inbound'
107         )
108         -- Not opted out (check both opt_outs and unsubscribed_emails)
109         AND NOT EXISTS (
110           SELECT 1 FROM opt_outs o
111           JOIN messages m ON (
112             (o.phone = m.contact_uri AND o.method = 'sms')
113             OR (o.email = m.contact_uri AND o.method = 'email')
114           )
115           WHERE m.site_id = s.id AND m.direction = 'outbound'
116         )
117         -- Not in a closed conversation state
118         AND (s.conversation_status IS NULL
119              OR s.conversation_status != ALL($1::text[]))
120         -- Haven't already generated all 8 touches
121         AND (SELECT COALESCE(MAX(m2.sequence_step), 1) FROM messages m2
122              WHERE m2.site_id = s.id AND m2.direction = 'outbound'
123                AND m2.sequence_step IS NOT NULL) < $2
124         -- Respect 3-day cooldown since last actual send
125         AND (s.last_outreach_at IS NULL
126              OR s.last_outreach_at < NOW() - INTERVAL '3 days')
127         -- Must have at least one successfully delivered email/sms to follow up on
128         AND EXISTS (
129           SELECT 1 FROM messages m3
130           WHERE m3.site_id = s.id AND m3.direction = 'outbound'
131             AND m3.delivery_status IN ('sent', 'delivered')
132             AND m3.contact_method IN ('email', 'sms')
133         )
134       ORDER BY s.next_followup_at ASC
135       LIMIT $3`,
136      [closedStatuses, MAX_SEQUENCE_STEP, limit]
137    );
138  
139    return sites;
140  }
141  
142  /**
143   * Load follow-up templates for a country/channel combination.
144   * Returns null if country-specific templates don't exist (no AU fallback —
145   * each country must have its own templates with correct spelling/tone).
146   *
147   * @param {string} countryCode - ISO country code
148   * @param {string} channel - 'email' or 'sms'
149   * @returns {Object|null} Templates object with followup fields
150   */
151  /**
152   * Campaign-specific template file prefix map (DR-128).
153   * 'freefix' → 'freefix-followup-{channel}.json'
154   * 'standard'/null → 'followup-{channel}.json' (original)
155   */
156  const FOLLOWUP_CAMPAIGN_PREFIXES = { freefix: 'freefix-', review_acquisition: 'review-campaign-' };
157  
158  function loadFollowupTemplates(countryCode, channel, campaignTag = null) {
159    const templatesDir = join(projectRoot, 'data', 'templates');
160    const prefix = FOLLOWUP_CAMPAIGN_PREFIXES[campaignTag] || ''; // eslint-disable-line security/detect-object-injection
161    const filename = `${prefix}followup-${channel}.json`;
162    const paths = [
163      join(templatesDir, countryCode, filename),
164    ];
165  
166    // Fall back to standard templates if campaign-specific not found
167    if (prefix) {
168      paths.push(join(templatesDir, countryCode, `followup-${channel}.json`));
169    }
170  
171    for (const p of paths) {
172      if (existsSync(p)) {
173        try {
174          return JSON.parse(readFileSync(p, 'utf8'));
175        } catch (e) {
176          logger.warn(`Failed to parse template ${p}: ${e.message}`);
177        }
178      }
179    }
180    return null;
181  }
182  
183  /**
184   * Extract weaknesses from score_json for follow-up messaging.
185   * Returns weaknesses NOT already used in the initial outreach (different angle).
186   *
187   * @param {Object} scoreJson - Parsed score_json
188   * @param {number} skipCount - Number of top weaknesses to skip (already used)
189   * @returns {Array} Array of {factor, score, reasoning}
190   */
191  function extractWeaknesses(scoreJson, skipCount = 1) {
192    if (!scoreJson?.factor_scores) return [];
193    return Object.entries(scoreJson.factor_scores)
194      .filter(([, f]) => f.score < 5)
195      .sort((a, b) => a[1].score - b[1].score)
196      .slice(skipCount) // Skip the ones already used in initial outreach
197      .map(([name, f]) => ({
198        factor: name.replace(/_/g, ' '),
199        score: f.score,
200        reasoning: f.reasoning,
201      }))
202      .slice(0, 3);
203  }
204  
205  /**
206   * Generate follow-up messages for a single site.
207   *
208   * @param {Object} site - Site record from getFollowupEligibleSites
209   * @returns {Promise<Object>} Result with generated messages count
210   */
211  export async function generateFollowupsForSite(site) {
212    const nextStep = (site.max_step || 1) + 1;
213    if (nextStep > MAX_SEQUENCE_STEP) {
214      return { generated: 0, reason: 'max_step_reached' };
215    }
216  
217    const scoreJson = getScoreDataWithFallback(site.id, site) ?? {};
218    const contactsJson = getContactsDataWithFallback(site.id, site) ?? {};
219    const businessName = contactsJson.business_name || site.domain;
220    const grade = site.grade || computeGrade(site.score);
221    const country = getCountryByCode(site.country_code);
222    const pricing = country
223      ? `${country.currencySymbol}${Math.round(country.priceUsd / 100)}`
224      : '$297';
225  
226    // Get existing outbound contact methods for this site (follow up on same channels)
227    const existingChannels = await getAll(
228      `SELECT DISTINCT contact_method, contact_uri
229       FROM messages
230       WHERE site_id = $1 AND direction = 'outbound'
231         AND delivery_status IN ('sent', 'delivered')
232         AND contact_method IN ('email', 'sms')
233       ORDER BY sent_at DESC`,
234      [site.id]
235    );
236  
237    if (existingChannels.length === 0) {
238      return { generated: 0, reason: 'no_sent_channels' };
239    }
240  
241    // For freefix campaign: check if the prospect applied the suggested fix (DR-128)
242    let fixCheckResult = null;
243    if (site.campaign_tag === 'freefix' && site.free_fix_before_state && nextStep >= 2 && nextStep <= 7) {
244      try {
245        fixCheckResult = await checkFixApplied(site);
246        logger.info(`Fix check for site ${site.id}: applied=${fixCheckResult.applied}`);
247      } catch (err) {
248        logger.warn(`Fix check failed for site ${site.id}: ${err.message}`);
249      }
250    }
251  
252    let generated = 0;
253  
254    for (const { contact_method: channel, contact_uri: contactUri } of existingChannels) {
255      // Check if this follow-up step already exists for this channel
256      const exists = await getOne(
257        `SELECT 1 FROM messages
258         WHERE site_id = $1 AND direction = 'outbound'
259           AND contact_method = $2 AND contact_uri = $3
260           AND sequence_step = $4`,
261        [site.id, channel, contactUri, nextStep]
262      );
263  
264      if (exists) continue;
265  
266      // Check opt-out for this specific contact
267      const optedOut = await getOne(
268        `SELECT 1 FROM opt_outs
269         WHERE (phone = $1 AND method = 'sms') OR (email = $1 AND method = 'email')`,
270        [contactUri]
271      );
272  
273      if (optedOut) continue;
274  
275      // Check unsubscribed emails
276      if (channel === 'email') {
277        const unsub = await getOne(
278          `SELECT 1 FROM unsubscribed_emails WHERE email = $1`,
279          [contactUri]
280        );
281        if (unsub) continue;
282      }
283  
284      // Generate the follow-up message
285      const message = await buildFollowupMessage({
286        step: nextStep,
287        channel,
288        site,
289        scoreJson,
290        businessName,
291        grade,
292        pricing,
293        contactsJson,
294        fixCheckResult,
295      });
296  
297      if (!message) {
298        logger.warn(`No template for step ${nextStep} ${channel} — site ${site.id}`);
299        continue;
300      }
301  
302      // Determine message_type for this step
303      const messageType = `followup${nextStep}`;
304  
305      // GDPR check for approval status
306      let approvalStatus = 'pending';
307      if (channel === 'email' && country?.requiresGDPRCheck) {
308        approvalStatus = 'gdpr_blocked';
309      }
310  
311      // Insert the follow-up message
312      try {
313        await run(
314          `INSERT INTO messages (
315             site_id, direction, contact_method, contact_uri,
316             message_body, subject_line, approval_status,
317             message_type, sequence_step, campaign_tag
318           ) VALUES ($1, 'outbound', $2, $3, $4, $5, $6, $7, $8, $9)`,
319          [
320            site.id,
321            channel,
322            contactUri,
323            message.body,
324            message.subject || null,
325            approvalStatus,
326            messageType,
327            nextStep,
328            site.campaign_tag || null,
329          ]
330        );
331        generated++;
332      } catch (e) {
333        // Handle dedup constraint violations gracefully
334        if (e.message.includes('unique') || e.message.includes('duplicate')) {
335          logger.info(`Dedup: follow-up step ${nextStep} already exists for site ${site.id} ${channel}`);
336        } else {
337          logger.error(`Failed to insert follow-up for site ${site.id}: ${e.message}`);
338        }
339      }
340    }
341  
342    // Update next_followup_at for the next touch
343    if (generated > 0) {
344      const nextNextStep = nextStep + 1;
345      if (nextNextStep <= MAX_SEQUENCE_STEP) {
346        const daysUntilNext = TOUCH_CADENCE[nextNextStep] - TOUCH_CADENCE[nextStep];
347        await run(
348          `UPDATE sites
349           SET next_followup_at = NOW() + INTERVAL '${daysUntilNext} days',
350               updated_at = CURRENT_TIMESTAMP
351           WHERE id = $1`,
352          [site.id]
353        );
354      } else {
355        // All follow-ups generated — clear next_followup_at
356        await run(
357          `UPDATE sites
358           SET next_followup_at = NULL,
359               updated_at = CURRENT_TIMESTAMP
360           WHERE id = $1`,
361          [site.id]
362        );
363      }
364    }
365  
366    return { generated, step: nextStep };
367  }
368  
369  /**
370   * Build a follow-up message using templates and spintax.
371   *
372   * @param {Object} params - Message parameters
373   * @returns {Object|null} {body, subject} or null if no template
374   */
375  async function buildFollowupMessage({ step, channel, site, scoreJson, businessName, grade, pricing, contactsJson, fixCheckResult }) {
376    // Try loading templates
377    const templates = loadFollowupTemplates(site.country_code, channel, site.campaign_tag);
378  
379    // Get the first contact name if available
380    const contacts = contactsJson?.contacts || [];
381    const contactName = contacts.find(c => c.name)?.name || null;
382    const firstname = contactName ? contactName.split(' ')[0] : null;
383  
384    // Get weaknesses for different angles
385    const allWeaknesses = scoreJson?.factor_scores
386      ? Object.entries(scoreJson.factor_scores)
387          .filter(([, f]) => f.score < 5)
388          .sort((a, b) => a[1].score - b[1].score)
389          .map(([name, f]) => ({
390            factor: name.replace(/_/g, ' '),
391            score: f.score,
392            reasoning: f.reasoning,
393          }))
394      : [];
395  
396    const industry = scoreJson?.industry_classification || scoreJson?.overall_calculation?.industry_classification || 'business';
397    const city = site.city || contactsJson?.city || '';
398  
399    if (!templates?.templates) {
400      logger.error(`No followup templates for ${site.country_code} ${channel} — skipping site ${site.id}`);
401      return null;
402    }
403  
404    let stepTemplates = templates.templates.filter(t => t.step === step);
405    if (stepTemplates.length === 0) {
406      logger.error(`No followup templates for ${site.country_code} ${channel} step ${step} — skipping site ${site.id}`);
407      return null;
408    }
409  
410    // For freefix campaign: filter by fix_check status if templates have the field (DR-128)
411    if (fixCheckResult && stepTemplates.some(t => t.fix_check)) {
412      const fixTag = fixCheckResult.applied ? 'applied' : 'not_applied';
413      const filtered = stepTemplates.filter(t => t.fix_check === fixTag);
414      if (filtered.length > 0) stepTemplates = filtered;
415      // If no match, fall through to use all step templates
416    }
417  
418    const template = stepTemplates[Math.floor(Math.random() * stepTemplates.length)];
419    const vars = {
420      firstname,
421      domain: site.domain,
422      grade,
423      score: Math.round(site.score),
424      industry,
425      city,
426      businessName,
427      pricing,
428      siteId: site.id,
429      weakness: allWeaknesses[0]?.factor || 'homepage conversion',
430      weakness2: allWeaknesses[1]?.factor || 'call-to-action clarity',
431      reasoning: allWeaknesses[0]?.reasoning || '',
432      fixElement: fixCheckResult?.element || '',
433      brand_url_short: process.env.BRAND_DOMAIN,
434      brand_url: process.env.BRAND_URL,
435      persona_name: process.env.PERSONA_NAME || '',
436      persona_first_name: process.env.PERSONA_FIRST_NAME || '',
437      brand_name: process.env.BRAND_NAME || '',
438      gbp_review_link: process.env.GBP_REVIEW_LINK || '',
439      trustpilot_link: process.env.TRUSTPILOT_LINK || '',
440    };
441    const body = resolveTemplate(template.body_spintax || template.body, vars);
442    const subject = template.subject_spintax
443      ? resolveTemplate(template.subject_spintax, vars)
444      : null;
445    return { body, subject };
446  }
447  
448  /**
449   * Resolve template variables and spintax.
450   */
451  function resolveTemplate(template, vars) {
452    let text = template;
453    // Replace [var] placeholders
454    for (const [key, value] of Object.entries(vars)) {
455      if (value !== null && value !== undefined) {
456        text = text.replace(new RegExp(`\\[${key}\\]`, 'gi'), String(value));
457      }
458    }
459    // Resolve [var|fallback] with fallback
460    text = text.replace(/\[(\w+)\|([^\]]+)\]/g, (match, key, fallback) => {
461      return vars[key] || fallback;
462    });
463    // Resolve spintax
464    return spin(text);
465  }
466  
467  /**
468   * Set next_followup_at on a site after the initial outreach is sent.
469   * Called from the outreach stage when a message is successfully delivered.
470   *
471   * @param {number} siteId - Site ID
472   * @returns {Promise<void>}
473   */
474  export async function scheduleFirstFollowup(siteId) {
475    try {
476      // Only schedule if not already scheduled and site is in outreach_sent status
477      const site = await getOne(
478        `SELECT next_followup_at, status FROM sites WHERE id = $1`,
479        [siteId]
480      );
481  
482      if (!site) return;
483  
484      // Schedule first follow-up (touch 2) for 3 days from now
485      // Only if next_followup_at is not already set
486      if (!site.next_followup_at) {
487        await run(
488          `UPDATE sites
489           SET next_followup_at = NOW() + INTERVAL '3 days',
490               updated_at = CURRENT_TIMESTAMP
491           WHERE id = $1 AND next_followup_at IS NULL`,
492          [siteId]
493        );
494      }
495    } catch (e) {
496      logger.error(`Failed to schedule follow-up for site ${siteId}: ${e.message}`);
497    }
498  }
499  
500  /**
501   * Run the follow-up generation stage.
502   * Finds sites due for follow-up and generates the next touch.
503   *
504   * @param {Object} options - Stage options
505   * @param {number} options.limit - Max sites to process per run
506   * @returns {Promise<Object>} Stage results
507   */
508  export async function runFollowupGenerationStage(options = {}) {
509    const startTime = Date.now();
510    const limit = options.limit || 20;
511  
512    logger.info(`Starting Follow-up Generation (limit=${limit})...`);
513  
514    const sites = await getFollowupEligibleSites(limit);
515  
516    if (sites.length === 0) {
517      logger.info('No sites due for follow-up');
518      return {
519        processed: 0,
520        generated: 0,
521        skipped: 0,
522        duration: Date.now() - startTime,
523      };
524    }
525  
526    logger.info(`Found ${sites.length} sites due for follow-up`);
527  
528    let totalGenerated = 0;
529    let totalSkipped = 0;
530  
531    for (const site of sites) {
532      try {
533        const result = await generateFollowupsForSite(site);
534        totalGenerated += result.generated;
535        if (result.generated === 0) totalSkipped++;
536        logger.info(
537          `Site ${site.id} (${site.domain}): generated ${result.generated} follow-up(s) ` +
538          `at step ${result.step || 'n/a'}${result.reason ? ` — ${result.reason}` : ''}`
539        );
540      } catch (e) {
541        logger.error(`Failed to generate follow-up for site ${site.id}: ${e.message}`);
542        totalSkipped++;
543      }
544    }
545  
546    const duration = Date.now() - startTime;
547    logger.info(
548      `Follow-up generation complete: ${totalGenerated} generated, ` +
549      `${totalSkipped} skipped in ${Math.round(duration / 1000)}s`
550    );
551  
552    return {
553      processed: sites.length,
554      generated: totalGenerated,
555      skipped: totalSkipped,
556      duration,
557    };
558  }