How to Send Daily Digest Notifications

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

async function sendDailyDigest() {
  const yesterday = new Date();
  yesterday.setDate(yesterday.getDate() - 1);
  const dateStr = yesterday.toISOString().split('T')[0];

  // Aggregate yesterday's data
  const stats = await pool.query(`
    SELECT
      COUNT(*) as total_orders,
      COALESCE(SUM(total), 0) as revenue,
      COUNT(DISTINCT customer_email) as unique_customers,
      COALESCE(AVG(total), 0) as avg_order_value,
      COUNT(*) FILTER (WHERE status = 'refunded') as refunds
    FROM orders
    WHERE created_at >= $1::date
    AND created_at < ($1::date + interval '1 day')
  `, [dateStr]);

  const prevStats = await pool.query(`
    SELECT COUNT(*) as total_orders, COALESCE(SUM(total), 0) as revenue
    FROM orders
    WHERE created_at >= ($1::date - interval '1 day')
    AND created_at < $1::date
  `, [dateStr]);

  const s = stats.rows[0];
  const prev = prevStats.rows[0];

  // Calculate trends
  const orderTrend = prev.total_orders > 0
    ? ((s.total_orders - prev.total_orders) / prev.total_orders * 100).toFixed(1)
    : 'N/A';
  const revTrend = prev.revenue > 0
    ? ((s.revenue - prev.revenue) / prev.revenue * 100).toFixed(1)
    : 'N/A';

  // Top products
  const topProducts = await pool.query(`
    SELECT product_name, SUM(quantity) as units, SUM(line_total) as revenue
    FROM order_items oi
    JOIN orders o ON o.id = oi.order_id
    WHERE o.created_at >= $1::date AND o.created_at < ($1::date + interval '1 day')
    GROUP BY product_name ORDER BY revenue DESC LIMIT 5
  `, [dateStr]);

  // Build the digest message
  const topList = topProducts.rows
    .map((p, i) => `${i + 1}. ${p.product_name}: ${p.units} units ($${p.revenue})`)
    .join('\n');

  const telegramMessage = `
<b>Daily Sales Digest - ${dateStr}</b>

Orders: <b>${s.total_orders}</b> (${orderTrend}% vs yesterday)
Revenue: <b>$${Number(s.revenue).toFixed(2)}</b> (${revTrend}%)
Customers: ${s.unique_customers}
Avg Order: $${Number(s.avg_order_value).toFixed(2)}
Refunds: ${s.refunds}

<b>Top Products:</b>
${topList}
  `.trim();

  // Send to all channels
  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: telegramMessage,
      channels: ['telegram', 'email', 'slack'],
      metadata: {
        email: {
          to: '[email protected]',
          subject: `Daily Sales Report - ${dateStr}: $${Number(s.revenue).toFixed(2)}`,
          html: `<h2>Daily Sales Digest - ${dateStr}</h2>
            <table style="border-collapse:collapse;width:100%">
              <tr><td>Orders</td><td><b>${s.total_orders}</b> (${orderTrend}%)</td></tr>
              <tr><td>Revenue</td><td><b>$${Number(s.revenue).toFixed(2)}</b> (${revTrend}%)</td></tr>
              <tr><td>Customers</td><td>${s.unique_customers}</td></tr>
              <tr><td>Avg Order</td><td>$${Number(s.avg_order_value).toFixed(2)}</td></tr>
              <tr><td>Refunds</td><td>${s.refunds}</td></tr>
            </table>
            <h3>Top Products</h3>
            <ol>${topProducts.rows.map(p =>
              '<li>' + p.product_name + ': ' + p.units + ' units ($' + p.revenue + ')</li>'
            ).join('')}</ol>`
        },
        slack: {
          blocks: [
            { type: 'header', text: { type: 'plain_text', text: `Daily Sales Digest - ${dateStr}` }},
            { type: 'section', fields: [
              { type: 'mrkdwn', text: `*Orders:*\n${s.total_orders} (${orderTrend}%)` },
              { type: 'mrkdwn', text: `*Revenue:*\n$${Number(s.revenue).toFixed(2)} (${revTrend}%)` },
              { type: 'mrkdwn', text: `*Customers:*\n${s.unique_customers}` },
              { type: 'mrkdwn', text: `*Avg Order:*\n$${Number(s.avg_order_value).toFixed(2)}` }
            ]}
          ]
        }
      }
    })
  });

  console.log(`Daily digest sent for ${dateStr}`);
}

sendDailyDigest().catch(console.error);

# Run daily digest at 9 AM
0 9 * * * /usr/bin/node /path/to/daily-digest.js >> /var/log/daily-digest.log 2>&1

import requests, os, psycopg2
from datetime import datetime, timedelta

def send_activity_digest():
    conn = psycopg2.connect(os.environ['DATABASE_URL'])
    cur = conn.cursor()
    yesterday = (datetime.now() - timedelta(days=1)).strftime('%Y-%m-%d')

    # Gather stats
    cur.execute("""
        SELECT
            (SELECT COUNT(*) FROM users WHERE created_at::date = %s) as new_users,
            (SELECT COUNT(*) FROM sessions WHERE created_at::date = %s) as sessions,
            (SELECT COUNT(*) FROM api_calls WHERE created_at::date = %s) as api_calls,
            (SELECT COUNT(*) FROM errors WHERE created_at::date = %s) as errors
    """, [yesterday] * 4)

    stats = cur.fetchone()
    new_users, sessions, api_calls, errors = stats

    # Error rate
    error_rate = (errors / max(api_calls, 1) * 100)

    message = f"""Daily Activity Report - {yesterday}

New Users: {new_users}
Sessions: {sessions}
API Calls: {api_calls:,}
Errors: {errors} ({error_rate:.2f}% error rate)"""

    requests.post(
        'https://api.one-ping.com/send',
        headers={'Authorization': f'Bearer {os.environ["ONEPING_API_KEY"]}'},
        json={
            'message': message,
            'channels': ['telegram', 'slack', 'email'],
            'metadata': {
                'email': {
                    'to': '[email protected]',
                    'subject': f'Daily Activity Report - {yesterday}'
                }
            }
        }
    )

    cur.close()
    conn.close()

send_activity_digest()