Appointment Booked
       ↓
Store in Database (with timezone)
       ↓
Cron Job Checks Every Minute
       ↓
Calculate Time Until Appointment
       ↓
Send Reminder at 24h / 2h / 30min Before
       ↓
Customer Receives WhatsApp Message
       ↓
Customer Confirms or Reschedules
       ↓
Update Database

mkdir appointment-reminders
cd appointment-reminders
npm init -y
npm install axios dotenv node-cron pg moment-timezone

# RapidAPI
RAPIDAPI_KEY=your_rapidapi_key_here
RAPIDAPI_HOST=whatsapp-messaging-bot.p.rapidapi.com
WHATSAPP_SESSION=reminders

# Database
DATABASE_URL=postgresql://user:password@localhost:5432/appointments

# Timezone
DEFAULT_TIMEZONE=America/New_York

# Server
PORT=3000

CREATE TABLE appointments (
  id SERIAL PRIMARY KEY,
  customer_name VARCHAR(255) NOT NULL,
  customer_phone VARCHAR(20) NOT NULL,
  customer_email VARCHAR(255),
  appointment_datetime TIMESTAMP WITH TIME ZONE NOT NULL,
  timezone VARCHAR(50) DEFAULT 'America/New_York',
  service_type VARCHAR(255),
  duration_minutes INTEGER DEFAULT 60,
  notes TEXT,
  status VARCHAR(50) DEFAULT 'scheduled', -- scheduled, confirmed, cancelled, completed, no_show
  reminder_24h_sent BOOLEAN DEFAULT FALSE,
  reminder_2h_sent BOOLEAN DEFAULT FALSE,
  reminder_30min_sent BOOLEAN DEFAULT FALSE,
  confirmation_received BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Index for faster queries
CREATE INDEX idx_appointments_datetime ON appointments(appointment_datetime);
CREATE INDEX idx_appointments_status ON appointments(status);
CREATE INDEX idx_appointments_phone ON appointments(customer_phone);

const mongoose = require('mongoose');

const appointmentSchema = new mongoose.Schema({
  customerName: { type: String, required: true },
  customerPhone: { type: String, required: true },
  customerEmail: String,
  appointmentDatetime: { type: Date, required: true },
  timezone: { type: String, default: 'America/New_York' },
  serviceType: String,
  durationMinutes: { type: Number, default: 60 },
  notes: String,
  status: { 
    type: String, 
    enum: ['scheduled', 'confirmed', 'cancelled', 'completed', 'no_show'],
    default: 'scheduled'
  },
  reminders: {
    reminder24hSent: { type: Boolean, default: false },
    reminder2hSent: { type: Boolean, default: false },
    reminder30minSent: { type: Boolean, default: false }
  },
  confirmationReceived: { type: Boolean, default: false },
  createdAt: { type: Date, default: Date.now },
  updatedAt: { type: Date, default: Date.now }
});

const Appointment = mongoose.model('Appointment', appointmentSchema);

// whatsapp-client.js
const axios = require('axios');
require('dotenv').config();

class WhatsAppClient {
  constructor() {
    this.baseURL = `https://${process.env.RAPIDAPI_HOST}`;
    this.session = process.env.WHATSAPP_SESSION;
    this.headers = {
      'X-RapidAPI-Key': process.env.RAPIDAPI_KEY,
      'X-RapidAPI-Host': process.env.RAPIDAPI_HOST,
      'Content-Type': 'application/json'
    };
  }

  async sendReminder(phoneNumber, message) {
    try {
      const response = await axios.post(
        `${this.baseURL}/v1/sendText`,
        {
          chatId: phoneNumber.replace(/\D/g, ''), // Remove non-digits
          text: message,
          session: this.session
        },
        { headers: this.headers }
      );
      
      console.log(`✅ Reminder sent to ${phoneNumber}`);
      return response.data;
    } catch (error) {
      console.error(`❌ Failed to send reminder to ${phoneNumber}:`, error.response?.data || error.message);
      throw error;
    }
  }

  async sendWithButtons(phoneNumber, message, buttons) {
    // If API supports interactive buttons
    try {
      const response = await axios.post(
        `${this.baseURL}/v1/sendText`,
        {
          chatId: phoneNumber.replace(/\D/g, ''),
          text: message + '\n\nReply:\n• "CONFIRM" to confirm\n• "CANCEL" to cancel\n• "RESCHEDULE" to reschedule',
          session: this.session
        },
        { headers: this.headers }
      );
      
      return response.data;
    } catch (error) {
      console.error('Error sending interactive message:', error.response?.data || error.message);
      throw error;
    }
  }
}

module.exports = WhatsAppClient;

// reminder-scheduler.js
const cron = require('node-cron');
const { Pool } = require('pg');
const moment = require('moment-timezone');
const WhatsAppClient = require('./whatsapp-client');

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const whatsapp = new WhatsAppClient();

class ReminderScheduler {
  constructor() {
    this.defaultTimezone = process.env.DEFAULT_TIMEZONE || 'America/New_York';
  }

  start() {
    console.log('🕐 Reminder scheduler started');
    
    // Check every minute for appointments needing reminders
    cron.schedule('* * * * *', async () => {
      await this.checkAndSendReminders();
    });
  }

  async checkAndSendReminders() {
    try {
      const now = moment();
      
      // Get appointments that need reminders
      const appointments = await this.getUpcomingAppointments();
      
      for (const apt of appointments) {
        const aptTime = moment.tz(apt.appointment_datetime, apt.timezone);
        const hoursUntil = aptTime.diff(now, 'hours', true);
        const minutesUntil = aptTime.diff(now, 'minutes', true);
        
        // 24-hour reminder
        if (!apt.reminder_24h_sent && hoursUntil <= 24 && hoursUntil > 23) {
          await this.send24HourReminder(apt, aptTime);
        }
        
        // 2-hour reminder
        else if (!apt.reminder_2h_sent && hoursUntil <= 2 && hoursUntil > 1.5) {
          await this.send2HourReminder(apt, aptTime);
        }
        
        // 30-minute reminder
        else if (!apt.reminder_30min_sent && minutesUntil <= 30 && minutesUntil > 25) {
          await this.send30MinuteReminder(apt, aptTime);
        }
      }
    } catch (error) {
      console.error('Error in reminder check:', error);
    }
  }

  async getUpcomingAppointments() {
    const query = `
      SELECT * FROM appointments
      WHERE status = 'scheduled'
      AND appointment_datetime > NOW()
      AND appointment_datetime < NOW() + INTERVAL '25 hours'
      ORDER BY appointment_datetime ASC
    `;
    
    const { rows } = await pool.query(query);
    return rows;
  }

  async send24HourReminder(appointment, aptTime) {
    const message = this.format24HourMessage(appointment, aptTime);
    
    try {
      await whatsapp.sendReminder(appointment.customer_phone, message);
      
      // Mark as sent
      await pool.query(
        'UPDATE appointments SET reminder_24h_sent = TRUE WHERE id = $1',
        [appointment.id]
      );
      
      console.log(`✅ 24h reminder sent for appointment #${appointment.id}`);
    } catch (error) {
      console.error(`Failed to send 24h reminder for #${appointment.id}:`, error);
    }
  }

  async send2HourReminder(appointment, aptTime) {
    const message = this.format2HourMessage(appointment, aptTime);
    
    try {
      await whatsapp.sendReminder(appointment.customer_phone, message);
      
      await pool.query(
        'UPDATE appointments SET reminder_2h_sent = TRUE WHERE id = $1',
        [appointment.id]
      );
      
      console.log(`✅ 2h reminder sent for appointment #${appointment.id}`);
    } catch (error) {
      console.error(`Failed to send 2h reminder for #${appointment.id}:`, error);
    }
  }

  async send30MinuteReminder(appointment, aptTime) {
    const message = this.format30MinuteMessage(appointment, aptTime);
    
    try {
      await whatsapp.sendReminder(appointment.customer_phone, message);
      
      await pool.query(
        'UPDATE appointments SET reminder_30min_sent = TRUE WHERE id = $1',
        [appointment.id]
      );
      
      console.log(`✅ 30min reminder sent for appointment #${appointment.id}`);
    } catch (error) {
      console.error(`Failed to send 30min reminder for #${appointment.id}:`, error);
    }
  }

  format24HourMessage(apt, aptTime) {
    return `
📅 *Appointment Reminder*

Hi ${apt.customer_name}!

This is a friendly reminder about your upcoming appointment:

*Service:* ${apt.service_type}
*Date:* ${aptTime.format('dddd, MMMM Do')}
*Time:* ${aptTime.format('h:mm A')}
*Duration:* ${apt.duration_minutes} minutes

📍 *Location:* 123 Main Street, Suite 100

*Please confirm your appointment by replying:*
• Reply "CONFIRM" to confirm
• Reply "CANCEL" if you need to cancel
• Reply "RESCHEDULE" to reschedule

We look forward to seeing you! 😊
    `.trim();
  }

  format2HourMessage(apt, aptTime) {
    return `
⏰ *Appointment in 2 Hours*

Hi ${apt.customer_name}!

Your appointment is coming up soon:

*Service:* ${apt.service_type}
*Time:* ${aptTime.format('h:mm A')} (in ~2 hours)

📍 *Address:* 123 Main Street, Suite 100
*Parking:* Free parking available in rear lot

*Need to cancel or reschedule?*
Please let us know ASAP by replying to this message.

See you soon! 👋
    `.trim();
  }

  format30MinuteMessage(apt, aptTime) {
    return `
🔔 *Appointment in 30 Minutes*

Hi ${apt.customer_name}!

Your appointment starts in 30 minutes:

*Time:* ${aptTime.format('h:mm A')}
*Service:* ${apt.service_type}

📍 *Location:* 123 Main Street, Suite 100

*Directions:* https://maps.google.com/?q=123+Main+St

Running late? Please call us at (555) 123-4567.

See you very soon! ⏰
    `.trim();
  }
}

module.exports = ReminderScheduler;

// index.js
const express = require('express');
const bodyParser = require('body-parser');
const ReminderScheduler = require('./reminder-scheduler');
const { Pool } = require('pg');

const app = express();
const pool = new Pool({ connectionString: process.env.DATABASE_URL });

app.use(bodyParser.json());

// Start reminder scheduler
const scheduler = new ReminderScheduler();
scheduler.start();

// Health check
app.get('/', (req, res) => {
  res.json({ status: 'running', message: 'Appointment Reminder System Active' });
});

// Create appointment (API endpoint)
app.post('/api/appointments', async (req, res) => {
  try {
    const {
      customerName,
      customerPhone,
      customerEmail,
      appointmentDatetime,
      timezone,
      serviceType,
      durationMinutes,
      notes
    } = req.body;

    const query = `
      INSERT INTO appointments 
      (customer_name, customer_phone, customer_email, appointment_datetime, 
       timezone, service_type, duration_minutes, notes)
      VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
      RETURNING *
    `;

    const values = [
      customerName,
      customerPhone,
      customerEmail,
      appointmentDatetime,
      timezone || 'America/New_York',
      serviceType,
      durationMinutes || 60,
      notes
    ];

    const { rows } = await pool.query(query, values);
    const appointment = rows[0];

    // Send instant confirmation
    const confirmationMsg = `
✅ *Appointment Confirmed*

Hi ${customerName}!

Your appointment has been booked:

*Service:* ${serviceType}
*Date & Time:* ${new Date(appointmentDatetime).toLocaleString()}
*Duration:* ${durationMinutes || 60} minutes

You'll receive reminders:
• 24 hours before
• 2 hours before
• 30 minutes before

Need to reschedule? Just reply to this message!

Confirmation #: ${appointment.id}
    `.trim();

    const WhatsAppClient = require('./whatsapp-client');
    const whatsapp = new WhatsAppClient();
    await whatsapp.sendReminder(customerPhone, confirmationMsg);

    res.status(201).json({ success: true, appointment });
  } catch (error) {
    console.error('Error creating appointment:', error);
    res.status(500).json({ error: 'Failed to create appointment' });
  }
});

// Webhook to handle customer responses (confirm/cancel/reschedule)
app.post('/webhook/whatsapp', async (req, res) => {
  try {
    const { event, payload } = req.body;

    if (event === 'message' && !payload.fromMe) {
      const message = payload.body.toUpperCase().trim();
      const senderPhone = payload.from.replace('@c.us', '');

      // Find appointment for this phone number
      const { rows } = await pool.query(
        'SELECT * FROM appointments WHERE customer_phone LIKE $1 AND status = $2 ORDER BY appointment_datetime ASC LIMIT 1',
        [`%${senderPhone}%`, 'scheduled']
      );

      if (rows.length > 0) {
        const appointment = rows[0];

        if (message === 'CONFIRM' || message === 'YES') {
          await pool.query(
            'UPDATE appointments SET confirmation_received = TRUE WHERE id = $1',
            [appointment.id]
          );

          const WhatsAppClient = require('./whatsapp-client');
          const whatsapp = new WhatsAppClient();
          await whatsapp.sendReminder(
            senderPhone,
            `✅ Thank you! Your appointment is confirmed. See you soon! 😊`
          );
        }
        else if (message === 'CANCEL' || message === 'NO') {
          await pool.query(
            'UPDATE appointments SET status = $1 WHERE id = $2',
            ['cancelled', appointment.id]
          );

          const WhatsAppClient = require('./whatsapp-client');
          const whatsapp = new WhatsAppClient();
          await whatsapp.sendReminder(
            senderPhone,
            `Your appointment has been cancelled. To book a new one, please call us at (555) 123-4567 or visit our website.`
          );
        }
        else if (message === 'RESCHEDULE') {
          const WhatsAppClient = require('./whatsapp-client');
          const whatsapp = new WhatsAppClient();
          await whatsapp.sendReminder(
            senderPhone,
            `To reschedule, please call us at (555) 123-4567 or visit: https://yoururl.com/reschedule?id=${appointment.id}`
          );
        }
      }
    }

    res.status(200).json({ success: true });
  } catch (error) {
    console.error('Webhook error:', error);
    res.status(500).json({ error: 'Webhook processing failed' });
  }
});

// Start server
const PORT = process.env.PORT || 3000;
app.listen(PORT, () => {
  console.log(`✅ Server running on port ${PORT}`);
  console.log(`📅 Appointment reminder system active`);
});

// calendly-webhook.js
app.post('/webhooks/calendly', async (req, res) => {
  try {
    const event = req.body;

    if (event.event === 'invitee.created') {
      const invitee = event.payload;

      // Create appointment in database
      await pool.query(`
        INSERT INTO appointments 
        (customer_name, customer_phone, customer_email, appointment_datetime, 
         timezone, service_type, notes)
        VALUES ($1, $2, $3, $4, $5, $6, $7)
      `, [
        invitee.name,
        invitee.questions_and_answers.find(q => q.question === 'Phone Number')?.answer,
        invitee.email,
        invitee.start_time,
        invitee.timezone,
        invitee.event_type_name,
        `Calendly Event: ${invitee.event_type_uuid}`
      ]);

      console.log(`✅ Created appointment from Calendly for ${invitee.name}`);
    }

    res.status(200).json({ success: true });
  } catch (error) {
    console.error('Calendly webhook error:', error);
    res.status(500).json({ error: error.message });
  }
});

const { google } = require('googleapis');

async function syncWithGoogleCalendar() {
  const calendar = google.calendar({ version: 'v3', auth: oauth2Client });

  // Get events from Google Calendar
  const response = await calendar.events.list({
    calendarId: 'primary',
    timeMin: new Date().toISOString(),
    maxResults: 100,
    singleEvents: true,
    orderBy: 'startTime'
  });

  const events = response.data.items;

  // Create appointments for each calendar event
  for (const event of events) {
    if (event.attendees) {
      for (const attendee of event.attendees) {
        // Extract phone number from description or attendee data
        const phone = extractPhoneFromDescription(event.description);

        if (phone) {
          await createAppointment({
            customerName: attendee.email.split('@')[0],
            customerPhone: phone,
            customerEmail: attendee.email,
            appointmentDatetime: event.start.dateTime,
            serviceType: event.summary
          });
        }
      }
    }
  }
}

async function getNoShowStats(startDate, endDate) {
  const query = `
    SELECT 
      COUNT(*) FILTER (WHERE status = 'completed') as completed,
      COUNT(*) FILTER (WHERE status = 'no_show') as no_shows,
      COUNT(*) FILTER (WHERE status = 'cancelled') as cancelled,
      COUNT(*) FILTER (WHERE confirmation_received = TRUE) as confirmed,
      COUNT(*) as total
    FROM appointments
    WHERE appointment_datetime BETWEEN $1 AND $2
  `;

  const { rows } = await pool.query(query, [startDate, endDate]);
  const stats = rows[0];

  const noShowRate = (stats.no_shows / stats.total * 100).toFixed(1);
  const confirmationRate = (stats.confirmed / stats.total * 100).toFixed(1);

  return {
    total: parseInt(stats.total),
    completed: parseInt(stats.completed),
    noShows: parseInt(stats.no_shows),
    cancelled: parseInt(stats.cancelled),
    confirmed: parseInt(stats.confirmed),
    noShowRate: `${noShowRate}%`,
    confirmationRate: `${confirmationRate}%`
  };
}

// Usage
const stats = await getNoShowStats('2026-01-01', '2026-01-31');
console.log('January Stats:', stats);
// Output: {
//   total: 250,
//   completed: 215,
//   noShows: 20,
//   cancelled: 15,
//   confirmed: 230,
//   noShowRate: '8.0%',
//   confirmationRate: '92.0%'
// }

function calculateSavings(monthlyAppointments, currentNoShowRate, targetNoShowRate) {
  const avgAppointmentValue = 150; // Adjust for your business
  
  const currentNoShows = monthlyAppointments * (currentNoShowRate / 100);
  const targetNoShows = monthlyAppointments * (targetNoShowRate / 100);
  const noShowsEliminated = currentNoShows - targetNoShows;
  
  const monthlySavings = noShowsEliminated * avgAppointmentValue;
  const annualSavings = monthlySavings * 12;
  
  // WhatsApp reminder costs
  const remindersPerAppointment = 3; // 24h, 2h, 30min
  const costPerReminder = 0.003;
  const monthlyReminderCost = monthlyAppointments * remindersPerAppointment * costPerReminder;
  const annualReminderCost = monthlyReminderCost * 12;
  
  const netAnnualSavings = annualSavings - annualReminderCost;
  
  return {
    monthlyAppointments,
    currentNoShowRate: `${currentNoShowRate}%`,
    targetNoShowRate: `${targetNoShowRate}%`,
    noShowsEliminated: Math.round(noShowsEliminated),
    monthlySavings: `${Math.round(monthlySavings).toLocaleString()}`,
    annualSavings: `${Math.round(annualSavings).toLocaleString()}`,
    reminderCost: `${Math.round(annualReminderCost).toLocaleString()}`,
    netSavings: `${Math.round(netAnnualSavings).toLocaleString()}`,
    roi: `${Math.round((netAnnualSavings / annualReminderCost) * 100)}x`
  };
}

// Example: Dental clinic with 400 appointments/month
console.log(calculateSavings(400, 25, 10));
// Output: {
//   monthlyAppointments: 400,
//   currentNoShowRate: '25%',
//   targetNoShowRate: '10%',
//   noShowsEliminated: 60,
//   monthlySavings: '$9,000',
//   annualSavings: '$108,000',
//   reminderCost: '$432',
//   netSavings: '$107,568',
//   roi: '24,900x'
// }

# Create Procfile
echo "web: node index.js" > Procfile

# Deploy
heroku create appointment-reminders
heroku addons:create heroku-postgresql:hobby-dev
git push heroku main

railway init
railway up