`sms-scheduler/sms-scheduler.ts`

323 lines
/**
 * Dear Bubbe SMS Scheduler
 *
 * Sends daily Bubbe wisdom via SMS using Twilio
 */

import { Twilio } from 'twilio';
import cron from 'node-cron';
import { Pool } from 'pg';

// Twilio configuration
const twilioClient = new Twilio(
  process.env.TWILIO_ACCOUNT_SID || '',
  process.env.TWILIO_AUTH_TOKEN || ''
);

const TWILIO_PHONE_NUMBER = process.env.TWILIO_PHONE_NUMBER || '';

// Database configuration
const pool = new Pool({
  connectionString: process.env.DATABASE_URL || 'postgresql://localhost/dear_bubbe'
});

interface Subscriber {
  id: string;
  phoneNumber: string;
  timezone: string;
  deliveryTime: string;
  active: boolean;
  categoriesPreference: string[];
  frequency: string;
}

interface BubbeMessage {
  id: string;
  bubbeRewrittenQuestion: string;
  bubbeAnswer: string;
  category: string;
  sentCount: number;
}

/**
 * Get active subscribers ready for today's message
 */
async function getActiveSubscribers(): Promise<Subscriber[]> {
  const query = `
    SELECT
      id,
      phone_number as "phoneNumber",
      timezone,
      delivery_time as "deliveryTime",
      active,
      categories_preference as "categoriesPreference",
      frequency
    FROM subscribers
    WHERE active = TRUE
  `;

  const result = await pool.query(query);
  return result.rows;
}

/**
 * Select a Bubbe message for a subscriber
 * Ensures variety and avoids recent repeats
 */
async function selectMessageForSubscriber(subscriber: Subscriber): Promise<BubbeMessage | null> {
  const categoryFilter = subscriber.categoriesPreference && subscriber.categoriesPreference.length > 0
    ? `AND category = ANY($1::text[])`
    : '';

  const query = `
    SELECT
      id,
      bubbe_rewritten_question as "bubbeRewrittenQuestion",
      bubbe_answer as "bubbeAnswer",
      category,
      sent_count as "sentCount"
    FROM dear_bubbe_entries
    WHERE approved = TRUE
      AND sms_length_ok = TRUE
      AND (next_eligible_date IS NULL OR next_eligible_date <= CURRENT_DATE)
      ${categoryFilter}
      AND id NOT IN (
        SELECT entry_id
        FROM sms_delivery_log
        WHERE phone_number = $${categoryFilter ? '2' : '1'}
          AND sent_at > CURRENT_DATE - INTERVAL '30 days'
      )
    ORDER BY sent_count ASC, RANDOM()
    LIMIT 1
  `;

  const params = categoryFilter
    ? [subscriber.categoriesPreference, subscriber.phoneNumber]
    : [subscriber.phoneNumber];

  const result = await pool.query(query, params);

  return result.rows.length > 0 ? result.rows[0] : null;
}

/**
 * Send SMS via Twilio
 */
async function sendSMS(phoneNumber: string, message: string): Promise<{
  success: boolean;
  sid?: string;
  error?: string;
}> {
  try {
    const result = await twilioClient.messages.create({
      body: message,
      from: TWILIO_PHONE_NUMBER,
      to: phoneNumber
    });

    return {
      success: true,
      sid: result.sid
    };
  } catch (error: any) {
    return {
      success: false,
      error: error.message
    };
  }
}

/**
 * Log SMS delivery
 */
async function logDelivery(
  entryId: string,
  phoneNumber: string,
  status: string,
  twilioSid?: string,
  errorMessage?: string
) {
  await pool.query(`
    INSERT INTO sms_delivery_log (
      entry_id,
      phone_number,
      delivery_status,
      twilio_sid,
      error_message
    ) VALUES ($1, $2, $3, $4, $5)
  `, [entryId, phoneNumber, status, twilioSid, errorMessage]);
}

/**
 * Update entry stats after sending
 */
async function updateEntrySta(entryId: string) {
  await pool.query(`
    UPDATE dear_bubbe_entries
    SET
      sent_count = sent_count + 1,
      last_sent_date = CURRENT_TIMESTAMP,
      next_eligible_date = CURRENT_DATE + INTERVAL '30 days'
    WHERE id = $1
  `, [entryId]);
}

/**
 * Update subscriber stats
 */
async function updateSubscriberStats(subscriberId: string) {
  await pool.query(`
    UPDATE subscribers
    SET total_messages_received = total_messages_received + 1
    WHERE id = $1
  `, [subscriberId]);
}

/**
 * Process daily SMS sends
 */
async function processDailySends() {
  console.log('\n📱 Starting daily Bubbe SMS delivery...\n');

  const subscribers = await getActiveSubscribers();
  console.log(`Found ${subscribers.length} active subscribers`);

  let successCount = 0;
  let failCount = 0;

  for (const subscriber of subscribers) {
    try {
      // Select message
      const message = await selectMessageForSubscriber(subscriber);

      if (!message) {
        console.log(`⚠️  No eligible message for ${subscriber.phoneNumber}`);
        continue;
      }

      // Format SMS text
      const smsText = `Dear Bubbe:\n\n${message.bubbeRewrittenQuestion}\n\n${message.bubbeAnswer}\n\n💙 Reply STOP to unsubscribe`;

      // Send SMS
      const result = await sendSMS(subscriber.phoneNumber, smsText);

      if (result.success) {
        // Log success
        await logDelivery(message.id, subscriber.phoneNumber, 'sent', result.sid);
        await updateEntrySta(message.id);
        await updateSubscriberStats(subscriber.id);

        successCount++;
        console.log(`✅ Sent to ${subscriber.phoneNumber}: ${message.category}`);
      } else {
        // Log failure
        await logDelivery(message.id, subscriber.phoneNumber, 'failed', undefined, result.error);
        failCount++;
        console.log(`❌ Failed to send to ${subscriber.phoneNumber}: ${result.error}`);
      }

      // Rate limiting - Twilio allows 10/sec but let's be gentle
      await new Promise(resolve => setTimeout(resolve, 500));

    } catch (error: any) {
      console.error(`Error processing ${subscriber.phoneNumber}:`, error.message);
      failCount++;
    }
  }

  console.log(`\n📊 Delivery Summary:`);
  console.log(`   ✅ Successful: ${successCount}`);
  console.log(`   ❌ Failed: ${failCount}`);
  console.log(`   📧 Total: ${subscribers.length}\n`);

  return { successCount, failCount };
}

/**
 * Handle STOP requests (Twilio webhook)
 */
async function handleStopRequest(phoneNumber: string) {
  await pool.query(`
    UPDATE subscribers
    SET active = FALSE, unsubscribed_at = CURRENT_TIMESTAMP
    WHERE phone_number = $1
  `, [phoneNumber]);

  console.log(`🛑 Unsubscribed: ${phoneNumber}`);
}

/**
 * Handle START requests (Twilio webhook)
 */
async function handleStartRequest(phoneNumber: string) {
  // Check if subscriber exists
  const existing = await pool.query(`
    SELECT id FROM subscribers WHERE phone_number = $1
  `, [phoneNumber]);

  if (existing.rows.length > 0) {
    // Reactivate
    await pool.query(`
      UPDATE subscribers
      SET active = TRUE, unsubscribed_at = NULL
      WHERE phone_number = $1
    `, [phoneNumber]);

    console.log(`✅ Reactivated: ${phoneNumber}`);
  } else {
    // New subscriber
    await pool.query(`
      INSERT INTO subscribers (phone_number, active)
      VALUES ($1, TRUE)
    `, [phoneNumber]);

    console.log(`🆕 New subscriber: ${phoneNumber}`);
  }

  // Send welcome message
  await sendSMS(phoneNumber, "Welcome to Dear Bubbe! You'll receive daily Yiddish wisdom at 8am. Reply STOP anytime to unsubscribe. Oy vey, you're going to love this!");
}

/**
 * Setup cron jobs
 */
function setupCronJobs() {
  // Daily at 8:00 AM (adjust timezone as needed)
  cron.schedule('0 8 * * *', async () => {
    console.log('\n⏰ Cron triggered: Daily Bubbe delivery');
    await processDailySends();
  });

  console.log('✅ Cron job scheduled: Daily at 8:00 AM');
}

// Export functions
export {
  processDailySends,
  handleStopRequest,
  handleStartRequest,
  setupCronJobs
};

// CLI usage
if (require.main === module) {
  const command = process.argv[2];

  if (command === 'send-now') {
    processDailySends()
      .then(() => process.exit(0))
      .catch(err => {
        console.error('Error:', err);
        process.exit(1);
      });
  } else if (command === 'start-scheduler') {
    console.log('🥯 Dear Bubbe SMS Scheduler Starting...\n');
    setupCronJobs();
    console.log('\n✨ Scheduler running. Press Ctrl+C to stop.\n');
  } else {
    console.log('Usage:');
    console.log('  send-now         Send SMS to all subscribers immediately');
    console.log('  start-scheduler  Start the cron scheduler');
  }
}