Recurring billing

-- Subscription plans
CREATE TABLE plans (
id UUID PRIMARY KEY,
name VARCHAR NOT NULL,            -- "Monthly Premium"
amount INTEGER NOT NULL,          -- in major unit
currency VARCHAR(3) NOT NULL,     -- "XAF", "XOF"…
interval VARCHAR NOT NULL,        -- "monthly", "weekly", "yearly"
trial_days INTEGER DEFAULT 0
);

-- Active subscriptions
CREATE TABLE subscriptions (
id UUID PRIMARY KEY,
customer_id UUID NOT NULL,
plan_id UUID NOT NULL REFERENCES plans(id),
status VARCHAR NOT NULL DEFAULT 'active', -- active, canceled, expired
current_period_start TIMESTAMP NOT NULL,
current_period_end TIMESTAMP NOT NULL,
phone VARCHAR NOT NULL,           -- for Mobile Money
canceled_at TIMESTAMP
);

-- Invoice / charge attempt history
CREATE TABLE invoices (
id UUID PRIMARY KEY,
subscription_id UUID NOT NULL REFERENCES subscriptions(id),
amount INTEGER NOT NULL,
currency VARCHAR(3) NOT NULL,
status VARCHAR NOT NULL DEFAULT 'pending', -- pending, paid, failed, void
trx_reference VARCHAR,           -- Wajub reference
due_date DATE NOT NULL,
paid_at TIMESTAMP
);

app.post('/subscribe', async (req, res) => {
const { planId, customerId, phone, email } = req.body;

const plan = await db.plans.findById(planId);
const periodEnd = addInterval(new Date(), plan.interval);

// Create the subscription (inactive until the first payment is confirmed)
const subscription = await db.subscriptions.create({
  customer_id: customerId,
  plan_id: planId,
  status: 'incomplete',
  current_period_start: new Date(),
  current_period_end: periodEnd,
  phone,
});

// Create the first invoice
const invoice = await db.invoices.create({
  subscription_id: subscription.id,
  amount: plan.amount,
  currency: plan.currency,
  due_date: new Date(),
});

// Initialize the payment
const { transaction, authorization_url } = await wajub.payments.initialize({
  amount: plan.amount,
  currency: plan.currency,
  customer: { phone, email },
  description: `Subscription ${plan.name}`,
  reference: `SUB-${subscription.id}-INV-${invoice.id}`,
  callback: `${BASE_URL}/subscribe/return?sub=${subscription.id}`,
});

await db.invoices.update(invoice.id, { trx_reference: transaction.reference });

res.json({ authorization_url, subscription_id: subscription.id });
});

app.post('/webhooks/wajub', express.raw({ type: '*/*' }), (req, res) => {
const event = wajub.webhooks.constructEvent(
  req.body, req.headers['x-wajub-signature'], SECRET
);
res.sendStatus(200);

if (event.type === 'payment.succeeded') {
  const invoice = await db.invoices.findByTrx(event.data.reference);
  if (!invoice) return;

  await db.invoices.update(invoice.id, {
    status: 'paid',
    paid_at: new Date(),
  });

  const sub = await db.subscriptions.findById(invoice.subscription_id);

  // If this is the first payment, activate the subscription
  if (sub.status === 'incomplete') {
    const periodEnd = addInterval(sub.current_period_start, sub.plan.interval);
    await db.subscriptions.update(sub.id, {
      status: 'active',
      current_period_start: new Date(),
      current_period_end: periodEnd,
    });
  }

  // If this is a renewal, extend the period
  if (sub.status === 'active') {
    const newPeriodEnd = addInterval(sub.current_period_end, sub.plan.interval);
    await db.subscriptions.update(sub.id, {
      current_period_end: newPeriodEnd,
    });
  }
}

if (event.type === 'payment.failed') {
  const invoice = await db.invoices.findByTrx(event.data.reference);
  if (!invoice) return;

  await db.invoices.update(invoice.id, { status: 'failed' });

  // Retry logic: we can retry in the renewal job
  console.log('Charge failed for invoice', invoice.id);
}
});

async function processRenewals() {
const dueTomorrow = new Date();
dueTomorrow.setDate(dueTomorrow.getDate() + 1);

const subs = await db.subscriptions.findDue({
  status: 'active',
  periodEndBefore: dueTomorrow,
});

for (const sub of subs) {
  // Check that no pending invoice already exists
  const pendingInvoice = await db.invoices.findPending(sub.id);
  if (pendingInvoice) {
    console.log(`Pending invoice exists for sub ${sub.id}, skipped`);
    continue;
  }

  // Create the renewal invoice
  const invoice = await db.invoices.create({
    subscription_id: sub.id,
    amount: sub.plan.amount,
    currency: sub.plan.currency,
    due_date: sub.current_period_end,
  });

  try {
    const { transaction } = await wajub.payments.initialize({
      amount: sub.plan.amount,
      currency: sub.plan.currency,
      customer: { phone: sub.phone },
      description: `Renewal ${sub.plan.name}`,
      reference: `SUB-${sub.id}-INV-${invoice.id}-RENEWAL`,
    });

    await db.invoices.update(invoice.id, {
      trx_reference: transaction.reference,
    });
  } catch (err) {
    await db.invoices.update(invoice.id, { status: 'failed' });
    console.error(`Renewal failed sub ${sub.id}:`, err.message);
  }
}
}

// Daily cron (e.g. node-cron, Bull, Agenda)
cron.schedule('0 6 * * *', processRenewals);

const RETRY_SCHEDULE = [
{ days: 3, label: 'First retry' },
{ days: 5, label: 'Second retry' },
{ days: 7, label: 'Final retry' },
];

async function retryFailedInvoices() {
const today = new Date();

for (const tier of RETRY_SCHEDULE) {
  const targetDate = new Date(today);
  targetDate.setDate(targetDate.getDate() - tier.days);

  const invoices = await db.invoices.findRetryable({
    status: 'failed',
    failedAt: targetDate,
    retryCount: RETRY_SCHEDULE.indexOf(tier),
  });

  for (const invoice of invoices) {
    const sub = await db.subscriptions.findById(invoice.subscription_id);

    try {
      const { transaction } = await wajub.payments.initialize({
        amount: invoice.amount,
        currency: invoice.currency,
        customer: { phone: sub.phone },
        description: `Retry ${tier.label} — ${sub.plan.name}`,
        reference: `SUB-${sub.id}-INV-${invoice.id}-RETRY-${Date.now()}`,
      });

      await db.invoices.update(invoice.id, {
        trx_reference: transaction.reference,
        retry_count: RETRY_SCHEDULE.indexOf(tier) + 1,
      });
    } catch (err) {
      console.error(`Retry failed for invoice ${invoice.id}:`, err.message);
    }
  }
}

// Cancel subscriptions after the last failed retry
await db.subscriptions.cancelExpired(RETRY_SCHEDULE.slice(-1)[0].days);
}

app.post('/subscribe/cancel', async (req, res) => {
const subId = req.body.subscriptionId;

const sub = await db.subscriptions.findById(subId);
if (!sub || sub.status === 'canceled') {
  return res.status(404).json({ error: 'Subscription not found' });
}

// Cancel immediately or at the end of the period
const cancelNow = req.body.immediate !== false;

await db.subscriptions.update(sub.id, {
  status: cancelNow ? 'canceled' : 'active',
  canceled_at: new Date(),
  cancel_at_period_end: !cancelNow,
});

// Void pending unpaid invoices
await db.invoices.voidPending(sub.id);

res.json({ status: 'canceled', effective: cancelNow ? 'immediate' : 'end_of_period' });
});