How to Send Stock and Inventory Alerts

const { Pool } = require('pg');
const pool = new Pool();

async function checkStockLevels() {
  // Find products below their low-stock threshold
  const result = await pool.query(`
    SELECT p.id, p.name, p.sku, p.stock_quantity,
           p.low_stock_threshold, p.critical_stock_threshold
    FROM products p
    WHERE p.stock_quantity <= p.low_stock_threshold
    AND p.last_stock_alert_at IS NULL
       OR p.last_stock_alert_at < NOW() - INTERVAL '24 hours'
    ORDER BY p.stock_quantity ASC
  `);

  if (result.rows.length === 0) return;

  // Separate critical from low-stock
  const critical = result.rows.filter(p => p.stock_quantity <= p.critical_stock_threshold);
  const low = result.rows.filter(p => p.stock_quantity > p.critical_stock_threshold);

  // Critical alert: Slack + Telegram
  if (critical.length > 0) {
    const items = critical.map(p =>
      `${p.name} (SKU: ${p.sku}): ${p.stock_quantity} left`
    ).join('\n');

    await fetch('https://api.one-ping.com/send', {
      method: 'POST',
      headers: {
        'Authorization': `Bearer ${process.env.ONEPING_API_KEY}`,
        'Content-Type': 'application/json'
      },
      body: JSON.stringify({
        message: `CRITICAL STOCK ALERT\n${critical.length} product(s) nearly out of stock:\n${items}`,
        channels: ['slack', 'telegram'],
        metadata: {
          slack: {
            attachments: [{
              color: '#ff0000',
              title: `${critical.length} Products Nearly Out of Stock`,
              text: items,
              footer: 'Inventory Alert | Reorder immediately'
            }]
          }
        }
      })
    });
  }

  // Low stock alert: Slack only
  if (low.length > 0) {
    const items = low.map(p =>
      `${p.name} (SKU: ${p.sku}): ${p.stock_quantity} left`
    ).join('\n');

    await fetch('https://api.one-ping.com/send', {
      method: 'POST',
      headers: {
        'Authorization': `Bearer ${process.env.ONEPING_API_KEY}`,
        'Content-Type': 'application/json'
      },
      body: JSON.stringify({
        message: `Low Stock Warning: ${low.length} product(s) running low:\n${items}`,
        channels: ['slack'],
        metadata: {
          slack: {
            attachments: [{
              color: '#ffcc00',
              title: `${low.length} Products Running Low`,
              text: items,
              footer: 'Inventory Alert | Consider reordering'
            }]
          }
        }
      })
    });
  }

  // Mark alerts as sent
  const ids = result.rows.map(p => p.id);
  await pool.query(
    'UPDATE products SET last_stock_alert_at = NOW() WHERE id = ANY($1)',
    [ids]
  );
}

checkStockLevels().catch(console.error);

async function notifyWaitlistCustomers(productId) {
  // Get product details
  const product = await pool.query(
    'SELECT * FROM products WHERE id = $1', [productId]
  );

  // Get waitlisted customers
  const waitlist = await pool.query(
    'SELECT * FROM stock_waitlist WHERE product_id = $1 AND notified = false',
    [productId]
  );

  if (waitlist.rows.length === 0) return;

  // Notify each customer
  for (const customer of waitlist.rows) {
    await fetch('https://api.one-ping.com/send', {
      method: 'POST',
      headers: {
        'Authorization': `Bearer ${process.env.ONEPING_API_KEY}`,
        'Content-Type': 'application/json'
      },
      body: JSON.stringify({
        message: `Great news! ${product.rows[0].name} is back in stock.`,
        channels: ['email', 'whatsapp'],
        recipient: customer.phone,
        metadata: {
          email: {
            to: customer.email,
            subject: `${product.rows[0].name} is Back in Stock!`,
            html: `<h2>Good news!</h2>
                   <p>${product.rows[0].name} is back in stock.</p>
                   <p>Grab yours before it sells out again:</p>
                   <a href="https://yourstore.com/products/${product.rows[0].slug}">
                     Buy Now - $${product.rows[0].price}
                   </a>`
          }
        }
      })
    });

    // Mark as notified
    await pool.query(
      'UPDATE stock_waitlist SET notified = true WHERE id = $1',
      [customer.id]
    );
  }
}