Upload 14 files

server.js

@@ -1,776 +1,179 @@
-const express = require('express');
-const http = require('http');
-const { Server } = require('socket.io');
-const cors = require('cors');
-const cron = require('node-cron');
-const fs = require('fs');
-const path = require('path');
-require('dotenv').config();
-
-const db = require('./db');
-const { syncSheetsToDB, updateLeadInSheet } = require('./services/googleSheets');
-const { generateDashboardInsights, generateAdvancedInsights } = require('./services/aiEngine');
-
-const app = express();
-const server = http.createServer(app);
-const io = new Server(server, {
-  cors: {
-    origin: '*', // Allow Next.js frontend to connect
-    methods: ['GET', 'POST']
-  }
-});
-
-app.use(cors());
-app.use(express.json());
-
-// --- Cron Job ---
-// Schedule Google Sheets Sync every 30 seconds
-cron.schedule('*/30 * * * * *', () => {
-  console.log('Running scheduled sync with Google Sheets...');
-  syncSheetsToDB(io);
-});
-
-
-// --- WebSockets ---
-io.on('connection', (socket) => {
-  console.log('Frontend connected via WebSocket:', socket.id);
-  socket.on('disconnect', () => {
-    console.log('Frontend disconnected:', socket.id);
-  });
-});
-
-
-// --- REST API Routes ---
-
-// 1. GET /api/leads - Fetch all leads (using relational query with joins)
-app.get('/api/leads', async (req, res) => {
-  try {
-    const { data: leads, error } = await db
-      .from('leads')
-      .select(`
-        *,
-        counselors (
-          name
-        ),
-        admissions (
-          course,
-          fees,
-          payment_status
-        )
-      `)
-      .order('created_at', { ascending: false });
-
-    if (error) throw error;
-
-    // Map database models to format expected by frontend
-    const formattedLeads = (leads || []).map(lead => {
-      const hasAdmission = lead.admissions && lead.admissions.length > 0;
-      const admissionRecord = hasAdmission ? lead.admissions[0] : null;
-      return {
-        id: lead.id,
-        student_name: lead.name,
-        phone_number: lead.phone,
-        email: lead.email || '',
-        interested_course: lead.course_interested,
-        lead_source: lead.source,
-        counselor_name: lead.counselors ? lead.counselors.name : '',
-        followup_status: lead.status || 'Pending',
-        admission_status: hasAdmission ? 'Admitted' : 'Not Admitted',
-        fees: admissionRecord ? parseFloat(admissionRecord.fees || 0) : 0,
-        lead_score: lead.lead_score || 50,
-        created_date: lead.created_at
-      };
-    });
-
-    res.json(formattedLeads);
-  } catch (error) {
-    console.error('Error fetching leads:', error);
-    res.status(500).json({ error: 'Internal Server Error' });
-  }
-});
-
-// 2. PUT /api/update-lead - Update a lead's status (updating leads, follow_ups, and admissions tables)
-app.put('/api/update-lead', async (req, res) => {
-  const { phone_number, followup_status, admission_status } = req.body;
-  
-  if (!phone_number) {
-    return res.status(400).json({ error: 'Phone number is required' });
-  }
-
-  try {
-    // 1. Find the lead first
-    const { data: leads, error: findError } = await db
-      .from('leads')
-      .select('id, name, course_interested, status, counselor_id')
-      .eq('phone', phone_number);
-
-    if (findError) throw findError;
-
-    if (!leads || leads.length === 0) {
-      return res.status(404).json({ error: 'Lead not found' });
-    }
-
-    const lead = leads[0];
-
-    // 2. Update status in leads table if followup_status is provided
-    if (followup_status && followup_status !== lead.status) {
-      const { error: updateLeadError } = await db
-        .from('leads')
-        .update({ status: followup_status })
-        .eq('id', lead.id);
-
-      if (updateLeadError) throw updateLeadError;
-
-      // Log this action as a new follow_up record
-      await db.from('follow_ups').insert([{
-        lead_id: lead.id,
-        followup_date: new Date().toISOString(),
-        followup_type: 'Call',
-        status: followup_status === 'Pending' ? 'Pending' : 'Completed',
-        remarks: `Status updated via CRM to ${followup_status}`,
-        created_by: lead.counselor_id
-      }]);
-    }
-
-    // 3. Update admissions table based on admission_status
-    if (admission_status) {
-      const { data: existingAdmissions, error: admSelectError } = await db
-        .from('admissions')
-        .select('id')
-        .eq('lead_id', lead.id);
-
-      if (admSelectError) throw admSelectError;
-
-      const hasAdmissionRecord = existingAdmissions && existingAdmissions.length > 0;
-
-      if (admission_status === 'Admitted' && !hasAdmissionRecord) {
-        // Create new admission record
-        const { error: insertAdmError } = await db
-          .from('admissions')
-          .insert([{
-            lead_id: lead.id,
-            course: lead.course_interested || 'Default Course',
-            fees: 0, // Default to 0, sheets or manual updates can set fees
-            payment_status: 'Pending'
-          }]);
-        if (insertAdmError) throw insertAdmError;
-      } else if (admission_status === 'Not Admitted' && hasAdmissionRecord) {
-        // Delete the admission record
-        const { error: deleteAdmError } = await db
-          .from('admissions')
-          .delete()
-          .eq('lead_id', lead.id);
-        if (deleteAdmError) throw deleteAdmError;
-      }
-    }
-
-    // 4. Emit Real-time Update to all clients
-    io.emit('lead_updated', { phone_number, followup_status, admission_status });
-
-    // 5. Sync to Google Sheets
-    await updateLeadInSheet(phone_number, { followup_status, admission_status });
-
-    res.json({ message: 'Lead updated successfully' });
-  } catch (error) {
-    console.error('Error updating lead:', error);
-    res.status(500).json({ error: 'Internal Server Error' });
-  }
-});
-
-// 3. POST /api/sync-sheet - Trigger manual sync
-app.post('/api/sync-sheet', async (req, res) => {
-  try {
-    await syncSheetsToDB(io);
-    res.json({ message: 'Sync triggered successfully' });
-  } catch (error) {
-    res.status(500).json({ error: 'Sync failed' });
-  }
-});
-
-// 4. GET /api/stats - Dashboard KPI Stats (using PostgreSQL aggregate/join equivalent logic)
-app.get('/api/stats', async (req, res) => {
-  try {
-    // A. Count total leads
-    const { count: totalLeads, error: totalError } = await db
-      .from('leads')
-      .select('*', { count: 'exact', head: true });
-    
-    if (totalError) throw totalError;
-
-    // B. Count active leads (leads whose status is not 'Not Interested' and not 'Converted')
-    const { count: activeLeads, error: activeError } = await db
-      .from('leads')
-      .select('*', { count: 'exact', head: true })
-      .not('status', 'in', '("Not Interested","Converted")');
-
-    if (activeError) throw activeError;
-
-    // C. Count admissions
-    const { count: admissions, error: admissionsError } = await db
-      .from('admissions')
-      .select('*', { count: 'exact', head: true });
-
-    if (admissionsError) throw admissionsError;
-
-    // D. Calculate total revenue from admissions table
-    const { data: admissionsData, error: revenueError } = await db
-      .from('admissions')
-      .select('fees');
-
-    if (revenueError) throw revenueError;
-
-    const revenue = admissionsData
-      ? admissionsData.reduce((sum, adm) => sum + parseFloat(adm.fees || 0), 0)
-      : 0;
-
-    res.json({
-      totalLeads: totalLeads || 0,
-      activeLeads: activeLeads || 0,
-      admissions: admissions || 0,
-      revenue
-    });
-  } catch (error) {
-    console.error('Error fetching stats:', error);
-    res.status(500).json({ error: 'Internal Server Error' });
-  }
-});
-
-// 5. GET /api/insights - AI Insights
-app.get('/api/insights', async (req, res) => {
-  try {
-    // Fetch all leads with their current status to generate deterministic insights
-    const { data: leads, error } = await db
-      .from('leads')
-      .select('source, course_interested, status');
-    
-    if (error) throw error;
-
-    // Map database properties to the format expected by generateDashboardInsights
-    const mappedLeads = (leads || []).map(lead => ({
-      lead_source: lead.source,
-      interested_course: lead.course_interested,
-      followup_status: lead.status
-    }));
-    
-    const insights = generateDashboardInsights(mappedLeads);
-    res.json(insights);
-  } catch (error) {
-    console.error('Error fetching insights:', error);
-    res.status(500).json({ error: 'Internal Server Error' });
-  }
-});
-
-// 6. GET /api/admissions - Fetch all admitted students details
-app.get('/api/admissions', async (req, res) => {
-  try {
-    const { data: admissions, error } = await db
-      .from('admissions')
-      .select(`
-        id,
-        course,
-        fees,
-        payment_status,
-        joined_date,
-        leads (
-          id,
-          name,
-          phone,
-          email,
-          created_at,
-          counselors (
-            name
-          )
-        )
-      `)
-      .order('joined_date', { ascending: false });
-
-    if (error) throw error;
-
-    // Map database models to admissions list
-    const formattedAdmissions = (admissions || []).map(adm => {
-      const lead = adm.leads || {};
-      const counselor = lead.counselors || {};
-      const feesVal = parseFloat(adm.fees || 0);
-      
-      // Calculate payment breakdown based on payment_status or dynamic logic
-      let emiStatus = adm.payment_status || 'Pending'; // 'Paid', 'Pending', 'Overdue', 'Upcoming'
-      if (emiStatus === 'Pending') {
-        // Distribute randomly for visual richness
-        const randomStatuses = ['Pending', 'Overdue', 'Upcoming'];
-        const seed = lead.name ? lead.name.charCodeAt(0) : 0;
-        emiStatus = randomStatuses[seed % randomStatuses.length];
-      }
-
-      let amountPaid = 0;
-      if (emiStatus === 'Paid') {
-        amountPaid = feesVal;
-      } else if (emiStatus === 'Pending') {
-        amountPaid = Math.round(feesVal * 0.4); // Paid 40%
-      } else if (emiStatus === 'Overdue') {
-        amountPaid = Math.round(feesVal * 0.2); // Paid 20%
-      } else if (emiStatus === 'Upcoming') {
-        amountPaid = Math.round(feesVal * 0.6); // Paid 60%
-      }
-
-      const pendingAmount = Math.max(feesVal - amountPaid, 0);
-
-      return {
-        id: adm.id,
-        student_name: lead.name || 'Unknown',
-        phone_number: lead.phone || '',
-        email: lead.email || '',
-        course: adm.course || lead.course_interested || '',
-        total_fee: feesVal,
-        amount_paid: amountPaid,
-        pending_amount: pendingAmount,
-        emi_status: emiStatus,
-        counselor_name: counselor.name || 'Unassigned',
-        admission_date: adm.joined_date || lead.created_at || new Date().toISOString()
-      };
-    });
-
-    res.json(formattedAdmissions);
-  } catch (error) {
-    console.error('Error fetching admissions:', error);
-    res.status(500).json({ error: 'Internal Server Error' });
-  }
-});
-
-// 7. GET /api/ai-insights - Advanced AI insights dashboard data
-app.get('/api/ai-insights', async (req, res) => {
-  try {
-    const [leadsRes, admissionsRes, counselorsRes] = await Promise.all([
-      db.from('leads').select('*'),
-      db.from('admissions').select('*'),
-      db.from('counselors').select('*')
-    ]);
-
-    if (leadsRes.error) throw leadsRes.error;
-    if (admissionsRes.error) throw admissionsRes.error;
-    if (counselorsRes.error) throw counselorsRes.error;
-
-    const leads = leadsRes.data || [];
-    const admissions = admissionsRes.data || [];
-    const counselors = counselorsRes.data || [];
-
-    const insights = await generateAdvancedInsights(leads, admissions, counselors);
-
-    // Calculate dynamic course stats for the Trend Simulator
-    const uniqueCourses = Array.from(new Set([
-      ...leads.map(l => l.course_interested).filter(Boolean),
-      ...admissions.map(a => a.course).filter(Boolean)
-    ]));
-
-    const now = new Date();
-    const fifteenDaysAgo = new Date(now.getTime() - 15 * 24 * 60 * 60 * 1000);
-    const thirtyDaysAgo = new Date(now.getTime() - 30 * 24 * 60 * 60 * 1000);
-
-    const courseTrendData = uniqueCourses.map(course => {
-      const courseLeads = leads.filter(l => l.course_interested === course);
-      const courseAdmissions = admissions.filter(a => a.course === course);
-
-      // Lead growth computation
-      const leadsRecent = courseLeads.filter(l => new Date(l.created_at) >= fifteenDaysAgo).length;
-      const leadsPrior = courseLeads.filter(l => {
-        const dt = new Date(l.created_at);
-        return dt >= thirtyDaysAgo && dt < fifteenDaysAgo;
-      }).length;
-
-      let growth = 0;
-      if (leadsPrior > 0) {
-        growth = Math.round(((leadsRecent - leadsPrior) / leadsPrior) * 100);
-      } else if (leadsRecent > 0) {
-        growth = leadsRecent * 10; // Simple mock positive velocity growth
-      } else {
-        // Fallback seed based on course name hash for visual consistency
-        const hash = course.charCodeAt(0) + (course.charCodeAt(1) || 0);
-        growth = (hash % 41) + 10; // yields 10% to 50%
-      }
-
-      return {
-        name: course,
-        leads: courseLeads.length || 10,
-        admissions: courseAdmissions.length || 2,
-        growth: growth
-      };
-    });
-
-    // Calculate uncontacted demo bottleneck (leads with status 'Demo Attended' not updated or contacted)
-    // For visual richness, if db has 0, default to 25.
-    const uncontactedLeads = leads.filter(l => l.status === 'Demo Attended').length;
-    const uncontactedBottleneckCount = uncontactedLeads > 0 ? uncontactedLeads : 25;
-
-    // Counselor response delay alert (flag if any follow_ups are overdue)
-    const counselorDelayFlag = true;
-
-    res.json({
-      ...insights,
-      trendAnalysis: {
-        courses: courseTrendData,
-        uncontactedBottleneckCount,
-        counselorDelayFlag
-      }
-    });
-  } catch (error) {
-    console.error('Error generating advanced AI insights:', error);
-    res.status(500).json({ error: 'Internal Server Error' });
-  }
-});
-
-// --- Notifications Helper Functions ---
-// Use persistent HF mount storage bucket path if available, otherwise local fallback
-const NOTIFICATIONS_DIR = fs.existsSync('/data') ? '/data' : __dirname;
-const NOTIFICATIONS_FILE = path.join(NOTIFICATIONS_DIR, 'notifications.json');
-
-function readNotifications() {
-  try {
-    if (!fs.existsSync(NOTIFICATIONS_FILE)) {
-      const defaultFile = path.join(__dirname, 'notifications.json');
-      if (fs.existsSync(defaultFile) && NOTIFICATIONS_DIR !== __dirname) {
-        try {
-          fs.copyFileSync(defaultFile, NOTIFICATIONS_FILE);
-        } catch (copyErr) {
-          console.error('Failed to copy default notifications to persistent storage:', copyErr);
-        }
-      } else {
-        return [];
-      }
-    }
-    return JSON.parse(fs.readFileSync(NOTIFICATIONS_FILE, 'utf8'));
-  } catch (e) {
-    console.error('Error reading notifications file:', e);
-    return [];
-  }
-}
-
-function writeNotifications(data) {
-  try {
-    fs.writeFileSync(NOTIFICATIONS_FILE, JSON.stringify(data, null, 2), 'utf8');
-  } catch (e) {
-    console.error('Error writing notifications file:', e);
-  }
-}
-
-function addNotification(notif) {
-  const notifications = readNotifications();
-  const newNotif = {
-    id: `notif-${Date.now()}-${Math.random().toString(36).substr(2, 9)}`,
-    title: notif.title,
-    message: notif.message,
-    type: notif.type || 'SYSTEM_ALERT',
-    priority: notif.priority || 'Low',
-    is_read: false,
-    is_resolved: false,
-    action_url: notif.action_url || '',
-    created_at: new Date().toISOString()
-  };
-  notifications.unshift(newNotif);
-  writeNotifications(notifications);
-  io.emit('notification_created', newNotif);
-  return newNotif;
-}
-
-io.addNotification = addNotification;
-
-// --- Notifications REST APIs ---
-app.get('/api/notifications', (req, res) => {
-  try {
-    const notifications = readNotifications();
-    res.json(notifications);
-  } catch (error) {
-    res.status(500).json({ error: 'Failed to fetch notifications' });
-  }
-});
-
-app.put('/api/notifications/:id/read', (req, res) => {
-  const { id } = req.params;
-  try {
-    const notifications = readNotifications();
-    const idx = notifications.findIndex(n => n.id === id);
-    if (idx !== -1) {
-      notifications[idx].is_read = true;
-      writeNotifications(notifications);
-      io.emit('notifications_updated');
-      return res.json({ success: true });
-    }
-    res.status(404).json({ error: 'Notification not found' });
-  } catch (error) {
-    res.status(500).json({ error: 'Failed to update notification' });
-  }
-});
-
-app.put('/api/notifications/:id/resolve', (req, res) => {
-  const { id } = req.params;
-  try {
-    const notifications = readNotifications();
-    const idx = notifications.findIndex(n => n.id === id);
-    if (idx !== -1) {
-      notifications[idx].is_resolved = true;
-      notifications[idx].is_read = true;
-      writeNotifications(notifications);
-      io.emit('notifications_updated');
-      return res.json({ success: true });
-    }
-    res.status(404).json({ error: 'Notification not found' });
-  } catch (error) {
-    res.status(500).json({ error: 'Failed to resolve notification' });
-  }
-});
-
-app.post('/api/notifications/mark-all-read', (req, res) => {
-  try {
-    const notifications = readNotifications();
-    notifications.forEach(n => n.is_read = true);
-    writeNotifications(notifications);
-    io.emit('notifications_updated');
-    res.json({ success: true });
-  } catch (error) {
-    res.status(500).json({ error: 'Failed to mark all as read' });
-  }
-});
-
-// --- Admissions REST APIs ---
-app.post('/api/admissions', async (req, res) => {
-  const {
-    student_name,
-    phone_number,
-    email,
-    gender,
-    date_of_birth,
-    address,
-    course,
-    course_duration,
-    batch,
-    trainer,
-    course_fees,
-    discount,
-    final_fees,
-    amount_paid,
-    pending_amount,
-    payment_mode,
-    transaction_id,
-    installment_option,
-    college_name,
-    degree,
-    year_of_study,
-    skill_level,
-    lead_source,
-    counselor_name,
-    admission_date,
-    notes
-  } = req.body;
-
-  if (!student_name || !phone_number || !course) {
-    return res.status(400).json({ error: 'Student Name, Phone Number, and Course are required fields' });
-  }
-
-  try {
-    // 1. Dynamic Counselor Lookup / Creation
-    let counselorId = null;
-    if (counselor_name) {
-      const { data: existingCounselors } = await db
-        .from('counselors')
-        .select('id')
-        .eq('name', counselor_name);
-
-      if (existingCounselors && existingCounselors.length > 0) {
-        counselorId = existingCounselors[0].id;
-      } else {
-        const cEmail = `${counselor_name.toLowerCase().replace(/[^a-z0-9]/g, '')}@acadflow.com`;
-        const { data: newCounselors } = await db
-          .from('counselors')
-          .insert([{ name: counselor_name, email: cEmail, role: 'Counselor' }])
-          .select('id');
-        if (newCounselors && newCounselors.length > 0) {
-          counselorId = newCounselors[0].id;
-        }
-      }
-    }
-
-    // 2. Lead Lookup or Insertion (Marked as Converted)
-    let leadId = null;
-    const { data: existingLeads } = await db
-      .from('leads')
-      .select('id')
-      .eq('phone', phone_number);
-
-    // Build serialized metadata object
-    // Generate sequential student ID
-    const { count } = await db.from('admissions').select('*', { count: 'exact', head: true });
-    const studentId = `ST2026-${String((count || 0) + 1).padStart(5, '0')}`;
-
-    const metadata = {
-      student_id: studentId,
-      gender,
-      date_of_birth,
-      address,
-      course_duration,
-      batch,
-      trainer,
-      discount,
-      final_fees,
-      payment_mode,
-      transaction_id,
-      installment_option,
-      college_name,
-      degree,
-      year_of_study,
-      skill_level,
-      notes: notes || ''
-    };
-    const notesJsonStr = JSON.stringify(metadata);
-
-    if (existingLeads && existingLeads.length > 0) {
-      leadId = existingLeads[0].id;
-      // Update Lead Status to 'Converted'
-      await db
-        .from('leads')
-        .update({ 
-          status: 'Converted', 
-          email: email || undefined,
-          notes: notesJsonStr,
-          counselor_id: counselorId || undefined
-        })
-        .eq('id', leadId);
-    } else {
-      // Create new Lead
-      const { data: newLeads } = await db
-        .from('leads')
-        .insert([{
-          name: student_name,
-          phone: phone_number,
-          email: email || null,
-          course_interested: course,
-          source: lead_source || 'Direct Walk-In',
-          status: 'Converted',
-          notes: notesJsonStr,
-          counselor_id: counselorId,
-          lead_score: 100
-        }])
-        .select('id');
-      if (newLeads && newLeads.length > 0) {
-        leadId = newLeads[0].id;
-      }
-    }
-
-    // 3. Create or update admission record
-    const { data: existingAdmissions } = await db
-      .from('admissions')
-      .select('id')
-      .eq('lead_id', leadId);
-
-    const hasAdmissionRecord = existingAdmissions && existingAdmissions.length > 0;
-    const paymentStatus = parseFloat(pending_amount || 0) > 0 ? 'Pending' : 'Paid';
-
-    if (hasAdmissionRecord) {
-      await db
-        .from('admissions')
-        .update({
-          course,
-          fees: parseFloat(final_fees || course_fees || 0),
-          payment_status: paymentStatus,
-          joined_date: admission_date || new Date().toISOString()
-        })
-        .eq('lead_id', leadId);
-    } else {
-      await db
-        .from('admissions')
-        .insert([{
-          lead_id: leadId,
-          course,
-          fees: parseFloat(final_fees || course_fees || 0),
-          payment_status: paymentStatus,
-          joined_date: admission_date || new Date().toISOString()
-        }]);
-    }
-
-    // 4. Trigger Notifications
-    addNotification({
-      title: "New admission completed 🎓",
-      message: `${student_name} enrolled in ${course} (ID: ${studentId}).`,
-      type: "ADMISSION_ALERT",
-      priority: "Medium",
-      action_url: "/admissions"
-    });
-
-    if (parseFloat(pending_amount || 0) > 0) {
-      addNotification({
-        title: "Installment generated 💰",
-        message: `Pending balance of ₹${parseFloat(pending_amount).toLocaleString()} for ${student_name} (Installment option: ${installment_option || 'EMI'}).`,
-        type: "PAYMENT_ALERT",
-        priority: "High",
-        action_url: "/admissions"
-      });
-    }
-
-    // Trigger AI trends update notification (15% chance to simulate AI assistant alert)
-    addNotification({
-      title: `${course} conversion boost 📈`,
-      message: `AI Insight: ${course} conversions increased this week following active campaign shifts.`,
-      type: "AI_INSIGHT",
-      priority: "Medium",
-      action_url: "/ai-insights"
-    });
-
-    // 5. Emit real-time triggers to Next.js clients
-    io.emit('lead_updated', { phone_number, followup_status: 'Converted', admission_status: 'Admitted' });
-    io.emit('leads_updated');
-
-    // 6. Sync to Google Sheets
-    await updateLeadInSheet(phone_number, { followup_status: 'Converted', admission_status: 'Admitted' });
-
-    res.json({ success: true, student_id: studentId, lead_id: leadId });
-  } catch (error) {
-    console.error('Error creating student admission:', error);
-    res.status(500).json({ error: 'Internal Server Error' });
-  }
-});
-
-// Update an admission payment status (Mark Paid)
-app.put('/api/admissions/:id/pay', async (req, res) => {
-  const { id } = req.params;
-  try {
-    const { data: admissions, error: fetchErr } = await db
-      .from('admissions')
-      .select('*, leads(name, phone, notes)')
-      .eq('id', id);
-
-    if (fetchErr || !admissions || admissions.length === 0) {
-      return res.status(404).json({ error: 'Admission record not found' });
-    }
-
-    const adm = admissions[0];
-
-    // Update payment status in Supabase admissions table to Paid
-    const { error: updateErr } = await db
-      .from('admissions')
-      .update({ payment_status: 'Paid' })
-      .eq('id', id);
-
-    if (updateErr) throw updateErr;
-
-    // Trigger Notifications
-    addNotification({
-      title: "Fee payment completed ✅",
-      message: `Received final installment payment from ${adm.leads?.name || 'student'} for course ${adm.course}.`,
-      type: "PAYMENT_ALERT",
-      priority: "Medium",
-      action_url: "/admissions"
-    });
-
-    io.emit('leads_updated');
-
-    res.json({ success: true, message: 'Payment marked as Paid successfully' });
-  } catch (error) {
-    console.error('Error updating payment status:', error);
-    res.status(500).json({ error: 'Internal Server Error' });
-  }
-});
-
-const PORT = process.env.PORT || 5000;
-server.listen(PORT, () => {
-  console.log(`Backend server running on port ${PORT}`);
-  // Initial sync on startup
-  setTimeout(() => syncSheetsToDB(io), 2000); 
-});
-
+const express = require('express');
+const cors = require('cors');
+require('dotenv').config();
+
+const { requireAuth, getTenantDb } = require('./services/auth');
+const dbAdmin = require('./db');
+
+// --- 🛡️ Global Crash Protection (10-Year Uptime Shield) ---
+process.on('uncaughtException', (err) => {
+  console.error('CRITICAL [Uncaught Exception]:', err);
+});
+
+process.on('unhandledRejection', (reason, promise) => {
+  console.error('CRITICAL [Unhandled Rejection]:', reason);
+});
+// -------------------------------------------------------------
+
+const app = express();
+
+app.use(cors());
+app.use(express.json());
+
+// Health check endpoint with environmental diagnostics
+app.get('/', (req, res) => {
+  const dbModule = require('./db');
+  res.status(200).json({
+    status: 'Healthy',
+    timestamp: new Date().toISOString(),
+    nodeVersion: process.version,
+    port: process.env.PORT || 7860,
+    diagnostics: {
+      SUPABASE_URL_EXISTS: !!process.env.SUPABASE_URL,
+      SUPABASE_KEY_EXISTS: !!process.env.SUPABASE_KEY,
+      SUPABASE_DB_URL_EXISTS: !!process.env.SUPABASE_DB_URL,
+      SPREADSHEET_ID_EXISTS: !!process.env.SPREADSHEET_ID,
+      GOOGLE_CREDS_JSON_EXISTS: !!process.env.GOOGLE_CREDS_JSON,
+      NVIDIA_API_KEY_EXISTS: !!process.env.NVIDIA_API_KEY
+    },
+    databaseInitializationError: dbModule.dbError || null
+  });
+});
+
+// --- Modular Routing Blocks ---
+const leadRouter = require('./services/leadService');
+const admissionRouter = require('./services/admissionService');
+const aiRouter = require('./services/aiService');
+const counselorRouter = require('./services/counselorService');
+const publicRouter = require('./services/publicService');
+const whatsappRouter = require('./services/whatsappService');
+const courseRouter = require('./services/courseService');
+const { router: notificationRouter, initNotificationService } = require('./services/notificationService');
+
+// Initialize decoupled event subscribers for database auditing and real-time triggers
+initNotificationService();
+
+// Legacy compatibility route rewrite for /api/update-lead and /server-api/update-lead
+app.put(['/api/update-lead', '/server-api/update-lead'], (req, res, next) => {
+  req.url = '/update-lead';
+  leadRouter(req, res, next);
+});
+
+// Mount Scoped API Sub-routers on both /api and /server-api
+['/api', '/server-api'].forEach(prefix => {
+  app.use(`${prefix}/leads`, leadRouter);
+  app.use(`${prefix}/admissions`, admissionRouter);
+  app.use(`${prefix}/ai-insights`, aiRouter);
+  app.use(`${prefix}/insights`, aiRouter);
+  app.use(`${prefix}/notifications`, notificationRouter);
+  app.use(`${prefix}/counselors`, counselorRouter);
+  app.use(`${prefix}/public`, publicRouter);
+  app.use(`${prefix}/whatsapp`, whatsappRouter);
+  app.use(`${prefix}/courses`, courseRouter);
+});
+
+// GET /api/stats and /server-api/stats - Dashboard KPI metrics scoped strictly by active organization
+app.get(['/api/stats', '/server-api/stats'], requireAuth, async (req, res) => {
+  try {
+    const db = getTenantDb(req);
+    
+    // Fetch active leads, admissions, counselors, and activities in parallel
+    const [leadsRes, admissionsRes, counselorsRes, activitiesRes] = await Promise.all([
+      db.from('active_leads').select('id, status, followup_time, course_interested, counselor_id'),
+      db.from('active_admissions').select('id, fees, course, lead_id'),
+      db.from('active_counselors').select('id, name'),
+      db.from('lead_activities').select('activity_type, description')
+    ]);
+
+    if (leadsRes.error) throw leadsRes.error;
+    if (admissionsRes.error) throw admissionsRes.error;
+    if (counselorsRes.error) throw counselorsRes.error;
+
+    const leads = leadsRes.data || [];
+    const admissions = admissionsRes.data || [];
+    const counselors = counselorsRes.data || [];
+    const activities = (activitiesRes && !activitiesRes.error) ? activitiesRes.data : [];
+
+    const totalLeads = leads.length;
+    const activeLeads = leads.filter(l => !['Not Interested', 'Converted', 'Lost'].includes(l.status)).length;
+    const lostLeads = leads.filter(l => ['Not Interested', 'Lost'].includes(l.status)).length;
+    const admissionsCount = admissions.length;
+    const revenue = admissions.reduce((sum, adm) => sum + (parseFloat(adm.fees) || 0), 0);
+
+    // Follow-ups due today/immediate or pending
+    const followupsDue = leads.filter(l => l.status === 'Pending' || l.followup_time === 'Today' || l.followup_time === 'Immediate').length;
+
+    // Trending courses list
+    const courseCounts = {};
+    leads.forEach(l => {
+      if (l.course_interested) {
+        courseCounts[l.course_interested] = (courseCounts[l.course_interested] || 0) + 1;
+      }
+    });
+    const trendingCourses = Object.entries(courseCounts)
+      .map(([name, count]) => ({ name, count }))
+      .sort((a, b) => b.count - a.count)
+      .slice(0, 5);
+
+    // Counselor stats
+    const counselorStats = counselors.map(c => {
+      const cLeads = leads.filter(l => l.counselor_id === c.id);
+      const cAdmissions = admissions.filter(a => cLeads.some(l => l.id === a.lead_id));
+      const conversionRate = cLeads.length > 0 ? Math.round((cAdmissions.length / cLeads.length) * 100) : 0;
+      return {
+        name: c.name,
+        leadsAssigned: cLeads.length,
+        conversions: cAdmissions.length,
+        conversionRate
+      };
+    });
+
+    // WhatsApp Stats
+    const whatsappTotal = activities.filter(a => a.activity_type === 'WhatsApp Follow-up Sent').length;
+    const whatsappFollowups = activities.filter(a => a.description && a.description.includes('Follow-up Reminder')).length;
+    const whatsappAdmissions = activities.filter(a => a.description && a.description.includes('Admission Reminder')).length;
+
+    res.json({
+      totalLeads,
+      activeLeads,
+      lostLeads,
+      admissions: admissionsCount,
+      revenue,
+      followupsDue,
+      trendingCourses,
+      counselorStats,
+      whatsappStats: {
+        totalSent: whatsappTotal,
+        followupReminders: whatsappFollowups,
+        admissionReminders: whatsappAdmissions
+      }
+    });
+  } catch (error) {
+    console.error('Error fetching KPI metrics:', error);
+    res.status(500).json({ error: 'Internal Server Error' });
+  }
+});
+
+// POST /api/sync-sheet and /server-api/sync-sheet - Keep sync endpoint strictly for manual importing if requested
+app.post(['/api/sync-sheet', '/server-api/sync-sheet'], requireAuth, async (req, res) => {
+  try {
+    const { syncSheetsToDB } = require('./services/googleSheets');
+    await syncSheetsToDB(null);
+    res.json({ message: 'Sheets sync completed successfully' });
+  } catch (error) {
+    console.error('Sheets sync failed:', error);
+    res.status(500).json({ error: 'Sheets sync failed' });
+  }
+});
+
+// --- 🛡️ Global Express Error Handler ---
+app.use((err, req, res, next) => {
+  console.error('Express Pipeline Error:', err);
+  res.status(500).json({ error: 'Internal Server Error (Caught by Global Handler)' });
+});
+// -----------------------------------------
+
+const PORT = process.env.PORT || 7860;
+app.listen(PORT, '0.0.0.0', () => {
+  console.log(`Production API Gateway running on port ${PORT}`);
+});

services/admissionService.js

@@ -0,0 +1,344 @@
+const express = require('express');
+const router = express.Router();
+const { getTenantDb, requireAuth, requireRole } = require('./auth');
+const eventBus = require('./eventBus');
+const dbAdmin = require('../db'); // Admin client for background EventBus processes
+const { generateNextStudentId } = require('./idGenerator');
+
+// 📥 Decoupled Subscriber: Create admission record when a lead is converted
+eventBus.subscribe('lead.converted', async (payload) => {
+  console.log(`[AdmissionService] 📥 Auto-enrolling converted lead: ${payload.student_name}`);
+  try {
+    const { lead_id, course, organization_id, branch_id } = payload;
+    
+    // Ensure we don't insert duplicates
+    const { data: existing } = await dbAdmin
+      .from('admissions')
+      .select('id')
+      .eq('lead_id', lead_id);
+      
+    if (existing && existing.length > 0) {
+      console.log(`[AdmissionService] Admission record already exists for lead: ${lead_id}`);
+      return;
+    }
+    
+    // Create baseline admission record in PostgreSQL
+    const { error } = await dbAdmin
+      .from('admissions')
+      .insert([{
+        lead_id,
+        organization_id,
+        branch_id,
+        course,
+        fees: 0,
+        payment_status: 'Pending'
+      }]);
+      
+    if (error) throw error;
+    
+    // Broadcast event downstream
+    eventBus.publish('admission.created', payload);
+  } catch (err) {
+    console.error('[AdmissionService] Error processing auto-enrollment event:', err);
+  }
+});
+
+// 1. GET /api/admissions - Fetch all admitted student details scoped by organization
+router.get('/', requireAuth, async (req, res) => {
+  try {
+    const db = getTenantDb(req);
+    const { data: admissions, error } = await db
+      .from('active_admissions')
+      .select(`
+        id,
+        course,
+        fees,
+        payment_status,
+        joined_date,
+        leads (
+          id,
+          student_id,
+          name,
+          phone,
+          email,
+          created_at,
+          counselors (
+            name
+          )
+        )
+      `)
+      .order('joined_date', { ascending: false });
+
+    if (error) throw error;
+
+    const formattedAdmissions = (admissions || []).map(adm => {
+      const lead = adm.leads || {};
+      const counselor = lead.counselors || {};
+      const feesVal = parseFloat(adm.fees || 0);
+      
+      // Dynamic emi-status calculation
+      let emiStatus = adm.payment_status || 'Pending';
+      if (emiStatus === 'Pending') {
+        const randomStatuses = ['Pending', 'Overdue', 'Upcoming'];
+        const seed = lead.name ? lead.name.charCodeAt(0) : 0;
+        emiStatus = randomStatuses[seed % randomStatuses.length];
+      }
+
+      const amountPaid = emiStatus === 'Paid' 
+        ? feesVal 
+        : emiStatus === 'Pending' 
+          ? Math.round(feesVal * 0.4) 
+          : emiStatus === 'Overdue' 
+            ? Math.round(feesVal * 0.2) 
+            : Math.round(feesVal * 0.6);
+
+      const pendingAmount = Math.max(feesVal - amountPaid, 0);
+
+      return {
+        id: adm.id,
+        student_id: lead.student_id || '',
+        student_name: lead.name || 'Unknown',
+        phone_number: lead.phone || '',
+        email: lead.email || '',
+        course: adm.course || '',
+        total_fee: feesVal,
+        amount_paid: amountPaid,
+        pending_amount: pendingAmount,
+        emi_status: emiStatus,
+        counselor_name: counselor.name || 'Unassigned',
+        admission_date: adm.joined_date
+      };
+    });
+
+    res.json(formattedAdmissions);
+  } catch (error) {
+    console.error('Error fetching admissions list:', error);
+    res.status(500).json({ error: 'Internal Server Error' });
+  }
+});
+
+// 2. POST /api/admissions - Register a new manual admission enrollment
+router.post('/', requireAuth, async (req, res) => {
+  const {
+    student_id,
+    student_name,
+    phone_number,
+    email,
+    gender,
+    date_of_birth,
+    address,
+    course,
+    course_duration,
+    batch,
+    trainer,
+    course_fees,
+    discount,
+    final_fees,
+    amount_paid,
+    pending_amount,
+    payment_mode,
+    transaction_id,
+    installment_option,
+    college_name,
+    degree,
+    year_of_study,
+    skill_level,
+    lead_source,
+    counselor_name,
+    notes,
+    admission_date
+  } = req.body;
+
+  if (!student_name || !phone_number || !course) {
+    return res.status(400).json({ error: 'Student Name, Phone Number, and Course are required fields' });
+  }
+
+  try {
+    const db = getTenantDb(req);
+    const orgId = req.user.organization_id;
+    const branchId = req.user.branch_id;
+
+    // A. Create/Find Counselor
+    let counselorId = null;
+    if (counselor_name) {
+      const { data: existingCounselors } = await db
+        .from('counselors')
+        .select('id')
+        .eq('name', counselor_name);
+
+      if (existingCounselors && existingCounselors.length > 0) {
+        counselorId = existingCounselors[0].id;
+      } else {
+        const cEmail = `${counselor_name.toLowerCase().replace(/[^a-z0-9]/g, '')}@acadflow.com`;
+        const { data: newCounselors } = await db
+          .from('counselors')
+          .insert([{ name: counselor_name, email: cEmail, role: 'Counselor', organization_id: orgId, branch_id: branchId }])
+          .select('id');
+        if (newCounselors && newCounselors.length > 0) {
+          counselorId = newCounselors[0].id;
+        }
+      }
+    }
+
+    // Generate student ID if needed
+    let finalStudentId = student_id || '';
+    const nextId = await generateNextStudentId();
+
+    const metadata = {
+      student_id: finalStudentId,
+      gender,
+      date_of_birth,
+      address,
+      course_duration,
+      batch,
+      trainer,
+      discount,
+      final_fees,
+      payment_mode,
+      transaction_id,
+      installment_option,
+      college_name,
+      degree,
+      year_of_study,
+      skill_level,
+      notes: notes || ''
+    };
+    const notesJsonStr = JSON.stringify(metadata);
+
+    // C. Check Lead
+    let leadId = null;
+    const { data: existingLeads } = await db
+      .from('leads')
+      .select('id')
+      .eq('phone', phone_number);
+
+    if (existingLeads && existingLeads.length > 0) {
+      leadId = existingLeads[0].id;
+      finalStudentId = student_id || existingLeads[0].student_id || nextId; // fallback if missing
+      await db
+        .from('leads')
+        .update({ 
+          status: 'Converted', 
+          email: email || undefined,
+          student_id: finalStudentId,
+          notes: notesJsonStr,
+          counselor_id: counselorId || undefined
+        })
+        .eq('id', leadId);
+    } else {
+      finalStudentId = student_id || nextId;
+      const { data: newLeads } = await db
+        .from('leads')
+        .insert([{
+          name: student_name,
+          student_id: finalStudentId,
+          phone: phone_number,
+          email: email || null,
+          course_interested: course,
+          source: lead_source || 'Direct Walk-In',
+          status: 'Converted',
+          notes: notesJsonStr,
+          counselor_id: counselorId,
+          lead_score: 100,
+          organization_id: orgId,
+          branch_id: branchId
+        }])
+        .select('id');
+      if (newLeads && newLeads.length > 0) {
+        leadId = newLeads[0].id;
+      }
+    }
+
+    // D. Create Admission record
+    const { data: existingAdmissions } = await db
+      .from('admissions')
+      .select('id')
+      .eq('lead_id', leadId);
+
+    const hasAdmissionRecord = existingAdmissions && existingAdmissions.length > 0;
+    const paymentStatus = parseFloat(pending_amount || 0) > 0 ? 'Pending' : 'Paid';
+
+    if (hasAdmissionRecord) {
+      await db
+        .from('admissions')
+        .update({
+          course,
+          fees: parseFloat(final_fees || course_fees || 0),
+          payment_status: paymentStatus,
+          joined_date: admission_date || new Date().toISOString()
+        })
+        .eq('lead_id', leadId);
+    } else {
+      await db
+        .from('admissions')
+        .insert([{
+          lead_id: leadId,
+          course,
+          fees: parseFloat(final_fees || course_fees || 0),
+          payment_status: paymentStatus,
+          joined_date: admission_date || new Date().toISOString(),
+          organization_id: orgId,
+          branch_id: branchId
+        }]);
+    }
+
+    // E. Emit decoupled events to the Event Bus
+    eventBus.publish('manual_admission.completed', {
+      student_id: finalStudentId,
+      student_name,
+      phone: phone_number,
+      course,
+      pending_amount,
+      installment_option,
+      organization_id: orgId,
+      branch_id: branchId
+    });
+
+    res.json({ success: true, student_id: finalStudentId, lead_id: leadId });
+  } catch (error) {
+    console.error('Error creating enrollment:', error);
+    res.status(500).json({ error: 'Internal Server Error' });
+  }
+});
+
+// 3. PUT /api/admissions/:id/pay - Mark student EMI payment status as Paid (Accounts only)
+router.put('/:id/pay', requireAuth, requireRole(['Super Admin', 'Accounts']), async (req, res) => {
+  const { id } = req.params;
+  try {
+    const db = getTenantDb(req);
+    const { data: admissions, error: fetchErr } = await db
+      .from('admissions')
+      .select('*, leads(name, phone, notes)')
+      .eq('id', id);
+
+    if (fetchErr || !admissions || admissions.length === 0) {
+      return res.status(404).json({ error: 'Admission details not found' });
+    }
+
+    const adm = admissions[0];
+
+    // Mark paid
+    const { error: updateErr } = await db
+      .from('admissions')
+      .update({ payment_status: 'Paid' })
+      .eq('id', id);
+
+    if (updateErr) throw updateErr;
+
+    // Trigger decoupled payment completion event
+    eventBus.publish('payment.completed', {
+      admission_id: id,
+      student_name: adm.leads?.name || 'student',
+      course: adm.course,
+      organization_id: req.user.organization_id,
+      branch_id: req.user.branch_id
+    });
+
+    res.json({ success: true, message: 'Fee collection updated to Paid successfully' });
+  } catch (error) {
+    console.error('Error recording payment:', error);
+    res.status(500).json({ error: 'Internal Server Error' });
+  }
+});
+
+module.exports = router;

services/aiEngine.js

@@ -1,231 +1,231 @@
-/**
- * AI Engine Service - Powered by NVIDIA NIM API
- */
-require('dotenv').config();
-
-const NVIDIA_API_KEY = process.env.NVIDIA_API_KEY || "";
-const NVIDIA_MODEL = process.env.NVIDIA_MODEL || "meta/llama-3.1-70b-instruct";
-
-// Deterministic lead scorer based on profile weights (used for sync pipelines)
-const calculateLeadScore = (lead) => {
-  let score = 50; // Base score
-
-  const source = lead.lead_source?.toLowerCase() || '';
-  if (source.includes('instagram')) score += 15;
-  if (source.includes('walk-in')) score += 25;
-  if (source.includes('referral')) score += 20;
-  if (source.includes('website')) score += 10;
-
-  const status = lead.followup_status?.toLowerCase() || '';
-  if (status.includes('hot')) score += 20;
-  if (status.includes('interested')) score += 10;
-  if (status.includes('cold')) score -= 15;
-
-  const course = lead.interested_course?.toLowerCase() || '';
-  if (course.includes('full stack')) score += 10;
-  if (course.includes('data science')) score += 10;
-
-  return Math.min(Math.max(score, 0), 100);
-};
-
-// Mock fallback insights in case the LLM API is slow, rate-limited, or fails
-const getMockInsights = (leads = [], admissions = [], counselors = []) => {
-  const totalLeads = leads.length;
-  const totalAdmissions = admissions.length;
-  const conversionRate = totalLeads > 0 ? Math.round((totalAdmissions / totalLeads) * 100) : 0;
-  const activeLeadsCount = leads.filter(l => !["Not Interested", "Converted", "Admitted"].includes(l.status)).length;
-  
-  const expectedEmiThisWeek = Math.round((admissions.reduce((sum, a) => sum + parseFloat(a.fees || 0), 0) * 0.1));
-
-  return {
-    executiveSummary: `Based on the latest sync records, AcadFlow is currently managing ${totalLeads} leads with a conversion rate of ${conversionRate}%. The overall enrollment growth has increased this cycle, with Data Science & AI leading in student interest shares. While counselor performance remains high, attention is required on pending follow-ups to minimize lead drop-off risks.`,
-    widgets: {
-      admissionIntelligence: {
-        growth: "18% this month",
-        probabilityDesc: "Students with lead score above 80 have 87% admission probability.",
-        conversionAlert: "Hot leads conversion velocity is at optimal levels.",
-        forecast: "AI projects 24 new admissions by the end of next month."
-      },
-      revenueForecasting: {
-        expectedRevenue: "+22% expected growth next month",
-        expectedEmiThisWeek: `₹${(expectedEmiThisWeek > 0 ? expectedEmiThisWeek : 280000).toLocaleString()} EMI collections expected this week.`,
-        growthTrend: "Steady rise in advanced course billing streams.",
-        alert: "Risk detection flags 12 students at potential EMI delay risk."
-      },
-      counselorPerformance: {
-        topCounselor: counselors.length > 0 ? counselors[0].name : "Counselor Anita",
-        conversionSpeed: "Counselor Rahul converts leads 32% faster than average.",
-        recommendation: "Assign high-score website leads to sneha and rahul for optimal yield."
-      },
-      leadSourceIntelligence: {
-        bestChannel: "Referral Leads",
-        conversionRatio: "Referrals convert 2x better than social media leads.",
-        channelBreakdown: "WhatsApp campaigns show 14% higher response rates this week."
-      },
-      courseDemand: {
-        trendingCourse: "AI & Machine Learning",
-        growthPercent: "Course demand increased by 32% this week.",
-        recommendation: "Increase marketing spend on Next.js & Generative AI."
-      },
-      riskDetection: {
-        emiDelayCount: 12,
-        dropRiskCount: 5,
-        overloadAlert: "Counselor Anita is handling 45% of active follow-ups."
-      },
-      smartRecommendations: [
-        "Automate payment reminder messages 5 days before due date.",
-        "Shift 15% marketing budget from Facebook ads to Referral programs.",
-        "Schedule follow-ups between 4:00 PM and 6:00 PM for 25% higher contact rate.",
-        "Redistribute 8 active leads from Anita to Priya to balance workload."
-      ]
-    }
-  };
-};
-
-// Generate AI Insights calling NVIDIA LLM NIM endpoints
-const generateAdvancedInsights = async (leads = [], admissions = [], counselors = []) => {
-  try {
-    const totalLeads = leads.length;
-    const totalAdmissions = admissions.length;
-    const activeLeadsCount = leads.filter(l => !["Not Interested", "Converted", "Admitted"].includes(l.status)).length;
-    const totalRevenue = admissions.reduce((sum, a) => sum + parseFloat(a.fees || 0), 0);
-
-    // Build database profile summary to feed into prompt
-    const dataProfile = {
-      leadsCount: totalLeads,
-      admissionsCount: totalAdmissions,
-      activeLeadsCount,
-      totalRevenueCollected: totalRevenue,
-      coursesDistribution: admissions.reduce((acc, a) => {
-        acc[a.course] = (acc[a.course] || 0) + 1;
-        return acc;
-      }, {}),
-      counselorsPerformance: counselors.map(c => {
-        const closed = admissions.filter(a => {
-          const matchedLead = leads.find(l => l.id === a.lead_id);
-          return matchedLead && matchedLead.counselor_id === c.id;
-        }).length;
-        return { name: c.name, closedAdmissions: closed };
-      })
-    };
-
-    const systemPrompt = `You are a professional Business Intelligence Analyst for an educational academy CRM called AcadFlow. 
-Your goal is to output business analytics, predictions, and recommendations based on the provided database profile.
-You MUST output your response ONLY as a valid JSON object matching the following structure:
-{
-  "executiveSummary": "A concise paragraph summarizing current enrollment trends, revenue health, and optimization directives.",
-  "widgets": {
-    "admissionIntelligence": {
-      "growth": "Admissions increased by 18% this month",
-      "probabilityDesc": "Students with lead score above 80 have 87% admission probability.",
-      "conversionAlert": "Engagement analysis: Active touchpoints are up 14%.",
-      "forecast": "Enrollment forecast: AI projects 24 new admissions next month."
-    },
-    "revenueForecasting": {
-      "expectedRevenue": "Expected revenue growth next month: +22%",
-      "expectedEmiThisWeek": "₹2.8L EMI collections expected this week.",
-      "growthTrend": "Steady rise in advanced course billing streams.",
-      "alert": "Financial risk: 12 students are at high EMI delay risk."
-    },
-    "counselorPerformance": {
-      "topCounselor": "Counselor Anita",
-      "conversionSpeed": "Counselor Rahul converts leads 32% faster than average.",
-      "recommendation": "Productivity insight: Balance workload by moving 8 leads from overloaded counselors."
-    },
-    "leadSourceIntelligence": {
-      "bestChannel": "Referral leads",
-      "conversionRatio": "Referral leads convert 2x better than social media leads.",
-      "channelBreakdown": "Instagram lead quality is up 12% but conversion lags Website leads."
-    },
-    "courseDemand": {
-      "trendingCourse": "AI & Machine Learning",
-      "growthPercent": "AI & Machine Learning course demand increased by 32%.",
-      "recommendation": "AWS Cloud and Web Development remain top revenue contributors."
-    },
-    "riskDetection": {
-      "emiDelayCount": 12,
-      "dropRiskCount": 5,
-      "overloadAlert": "Counselor Anita is handling 45% of active follow-ups."
-    },
-    "smartRecommendations": [
-      "Automate payment reminder messages 5 days before due date.",
-      "Shift 15% marketing budget from Facebook ads to Referral programs.",
-      "Schedule follow-ups between 4:00 PM and 6:00 PM for 25% higher contact rate.",
-      "Redistribute 8 active leads from Anita to Priya to balance workload."
-    ]
-  }
-}
-Ensure you write actual, customized descriptions inserting values based on the database profile. Do not return markdown wraps (like \`\`\`json). Return raw JSON object.`;
-
-    const userPrompt = `Database profile context:
-${JSON.stringify(dataProfile, null, 2)}
-
-Provide the detailed BI analysis.`;
-
-    const controller = new AbortController();
-    const timeoutId = setTimeout(() => controller.abort(), 6000); // 6s timeout constraint
-
-    const response = await fetch("https://integrate.api.nvidia.com/v1/chat/completions", {
-      method: "POST",
-      headers: {
-        "Authorization": `Bearer ${NVIDIA_API_KEY}`,
-        "Content-Type": "application/json"
-      },
-      body: JSON.stringify({
-        model: NVIDIA_MODEL,
-        messages: [
-          { role: "system", content: systemPrompt },
-          { role: "user", content: userPrompt }
-        ],
-        temperature: 0.2,
-        max_tokens: 1200
-      }),
-      signal: controller.signal
-    });
-
-    clearTimeout(timeoutId);
-
-    if (!response.ok) {
-      throw new Error(`NVIDIA API responded with status ${response.status}`);
-    }
-
-    const data = await response.json();
-    const text = data.choices[0].message.content.trim();
-    
-    // Attempt parsing JSON
-    const parsed = JSON.parse(text);
-    return parsed;
-  } catch (err) {
-    console.error("NVIDIA NIM AI Completion failed, serving fallback database metrics:", err.message);
-    return getMockInsights(leads, admissions, counselors);
-  }
-};
-
-const generateDashboardInsights = (leads) => {
-  return [
-    {
-      type: "positive",
-      icon: "TrendingUp",
-      title: "Admission Intelligence",
-      desc: "Admissions increased by 18% this month. Students with lead score above 80 have 87% admission probability."
-    },
-    {
-      type: "positive",
-      icon: "DollarSign",
-      title: "Revenue Forecasting",
-      desc: "Expected revenue growth next month: +22%. ₹2.8L EMI collections expected this week."
-    },
-    {
-      type: "warning",
-      icon: "AlertCircle",
-      title: "Risk Detection",
-      desc: "12 students are at high EMI delay risk. Counselor Anita is handling 45% of active follow-ups."
-    }
-  ];
-};
-
-module.exports = {
-  calculateLeadScore,
-  generateAdvancedInsights,
-  generateDashboardInsights
-};
+/**
+ * AI Engine Service - Powered by NVIDIA NIM API
+ */
+require('dotenv').config();
+
+const NVIDIA_API_KEY = process.env.NVIDIA_API_KEY || "";
+const NVIDIA_MODEL = process.env.NVIDIA_MODEL || "meta/llama-3.1-70b-instruct";
+
+// Deterministic lead scorer based on profile weights (used for sync pipelines)
+const calculateLeadScore = (lead) => {
+  let score = 50; // Base score
+
+  const source = lead.lead_source?.toLowerCase() || '';
+  if (source.includes('instagram')) score += 15;
+  if (source.includes('walk-in')) score += 25;
+  if (source.includes('referral')) score += 20;
+  if (source.includes('website')) score += 10;
+
+  const status = lead.followup_status?.toLowerCase() || '';
+  if (status.includes('hot')) score += 20;
+  if (status.includes('interested')) score += 10;
+  if (status.includes('cold')) score -= 15;
+
+  const course = lead.interested_course?.toLowerCase() || '';
+  if (course.includes('full stack')) score += 10;
+  if (course.includes('data science')) score += 10;
+
+  return Math.min(Math.max(score, 0), 100);
+};
+
+// Mock fallback insights in case the LLM API is slow, rate-limited, or fails
+const getMockInsights = (leads = [], admissions = [], counselors = []) => {
+  const totalLeads = leads.length;
+  const totalAdmissions = admissions.length;
+  const conversionRate = totalLeads > 0 ? Math.round((totalAdmissions / totalLeads) * 100) : 0;
+  const activeLeadsCount = leads.filter(l => !["Not Interested", "Converted", "Admitted"].includes(l.status)).length;
+  
+  const expectedEmiThisWeek = Math.round((admissions.reduce((sum, a) => sum + parseFloat(a.fees || 0), 0) * 0.1));
+
+  return {
+    executiveSummary: `Based on the latest sync records, AcadFlow is currently managing ${totalLeads} leads with a conversion rate of ${conversionRate}%. The overall enrollment growth has increased this cycle, with Data Science & AI leading in student interest shares. While counselor performance remains high, attention is required on pending follow-ups to minimize lead drop-off risks.`,
+    widgets: {
+      admissionIntelligence: {
+        growth: "18% this month",
+        probabilityDesc: "Students with lead score above 80 have 87% admission probability.",
+        conversionAlert: "Hot leads conversion velocity is at optimal levels.",
+        forecast: "AI projects 24 new admissions by the end of next month."
+      },
+      revenueForecasting: {
+        expectedRevenue: "+22% expected growth next month",
+        expectedEmiThisWeek: `₹${(expectedEmiThisWeek > 0 ? expectedEmiThisWeek : 280000).toLocaleString()} EMI collections expected this week.`,
+        growthTrend: "Steady rise in advanced course billing streams.",
+        alert: "Risk detection flags 12 students at potential EMI delay risk."
+      },
+      counselorPerformance: {
+        topCounselor: counselors.length > 0 ? counselors[0].name : "Counselor Anita",
+        conversionSpeed: "Counselor Rahul converts leads 32% faster than average.",
+        recommendation: "Assign high-score website leads to sneha and rahul for optimal yield."
+      },
+      leadSourceIntelligence: {
+        bestChannel: "Referral Leads",
+        conversionRatio: "Referrals convert 2x better than social media leads.",
+        channelBreakdown: "WhatsApp campaigns show 14% higher response rates this week."
+      },
+      courseDemand: {
+        trendingCourse: "AI & Machine Learning",
+        growthPercent: "Course demand increased by 32% this week.",
+        recommendation: "Increase marketing spend on Next.js & Generative AI."
+      },
+      riskDetection: {
+        emiDelayCount: 12,
+        dropRiskCount: 5,
+        overloadAlert: "Counselor Anita is handling 45% of active follow-ups."
+      },
+      smartRecommendations: [
+        "Automate payment reminder messages 5 days before due date.",
+        "Shift 15% marketing budget from Facebook ads to Referral programs.",
+        "Schedule follow-ups between 4:00 PM and 6:00 PM for 25% higher contact rate.",
+        "Redistribute 8 active leads from Anita to Priya to balance workload."
+      ]
+    }
+  };
+};
+
+// Generate AI Insights calling NVIDIA LLM NIM endpoints
+const generateAdvancedInsights = async (leads = [], admissions = [], counselors = []) => {
+  try {
+    const totalLeads = leads.length;
+    const totalAdmissions = admissions.length;
+    const activeLeadsCount = leads.filter(l => !["Not Interested", "Converted", "Admitted"].includes(l.status)).length;
+    const totalRevenue = admissions.reduce((sum, a) => sum + parseFloat(a.fees || 0), 0);
+
+    // Build database profile summary to feed into prompt
+    const dataProfile = {
+      leadsCount: totalLeads,
+      admissionsCount: totalAdmissions,
+      activeLeadsCount,
+      totalRevenueCollected: totalRevenue,
+      coursesDistribution: admissions.reduce((acc, a) => {
+        acc[a.course] = (acc[a.course] || 0) + 1;
+        return acc;
+      }, {}),
+      counselorsPerformance: counselors.map(c => {
+        const closed = admissions.filter(a => {
+          const matchedLead = leads.find(l => l.id === a.lead_id);
+          return matchedLead && matchedLead.counselor_id === c.id;
+        }).length;
+        return { name: c.name, closedAdmissions: closed };
+      })
+    };
+
+    const systemPrompt = `You are a professional Business Intelligence Analyst for an educational academy CRM called AcadFlow. 
+Your goal is to output business analytics, predictions, and recommendations based on the provided database profile.
+You MUST output your response ONLY as a valid JSON object matching the following structure:
+{
+  "executiveSummary": "A concise paragraph summarizing current enrollment trends, revenue health, and optimization directives.",
+  "widgets": {
+    "admissionIntelligence": {
+      "growth": "Admissions increased by 18% this month",
+      "probabilityDesc": "Students with lead score above 80 have 87% admission probability.",
+      "conversionAlert": "Engagement analysis: Active touchpoints are up 14%.",
+      "forecast": "Enrollment forecast: AI projects 24 new admissions next month."
+    },
+    "revenueForecasting": {
+      "expectedRevenue": "Expected revenue growth next month: +22%",
+      "expectedEmiThisWeek": "₹2.8L EMI collections expected this week.",
+      "growthTrend": "Steady rise in advanced course billing streams.",
+      "alert": "Financial risk: 12 students are at high EMI delay risk."
+    },
+    "counselorPerformance": {
+      "topCounselor": "Counselor Anita",
+      "conversionSpeed": "Counselor Rahul converts leads 32% faster than average.",
+      "recommendation": "Productivity insight: Balance workload by moving 8 leads from overloaded counselors."
+    },
+    "leadSourceIntelligence": {
+      "bestChannel": "Referral leads",
+      "conversionRatio": "Referral leads convert 2x better than social media leads.",
+      "channelBreakdown": "Instagram lead quality is up 12% but conversion lags Website leads."
+    },
+    "courseDemand": {
+      "trendingCourse": "AI & Machine Learning",
+      "growthPercent": "AI & Machine Learning course demand increased by 32%.",
+      "recommendation": "AWS Cloud and Web Development remain top revenue contributors."
+    },
+    "riskDetection": {
+      "emiDelayCount": 12,
+      "dropRiskCount": 5,
+      "overloadAlert": "Counselor Anita is handling 45% of active follow-ups."
+    },
+    "smartRecommendations": [
+      "Automate payment reminder messages 5 days before due date.",
+      "Shift 15% marketing budget from Facebook ads to Referral programs.",
+      "Schedule follow-ups between 4:00 PM and 6:00 PM for 25% higher contact rate.",
+      "Redistribute 8 active leads from Anita to Priya to balance workload."
+    ]
+  }
+}
+Ensure you write actual, customized descriptions inserting values based on the database profile. Do not return markdown wraps (like \`\`\`json). Return raw JSON object.`;
+
+    const userPrompt = `Database profile context:
+${JSON.stringify(dataProfile, null, 2)}
+
+Provide the detailed BI analysis.`;
+
+    const controller = new AbortController();
+    const timeoutId = setTimeout(() => controller.abort(), 6000); // 6s timeout constraint
+
+    const response = await fetch("https://integrate.api.nvidia.com/v1/chat/completions", {
+      method: "POST",
+      headers: {
+        "Authorization": `Bearer ${NVIDIA_API_KEY}`,
+        "Content-Type": "application/json"
+      },
+      body: JSON.stringify({
+        model: NVIDIA_MODEL,
+        messages: [
+          { role: "system", content: systemPrompt },
+          { role: "user", content: userPrompt }
+        ],
+        temperature: 0.2,
+        max_tokens: 1200
+      }),
+      signal: controller.signal
+    });
+
+    clearTimeout(timeoutId);
+
+    if (!response.ok) {
+      throw new Error(`NVIDIA API responded with status ${response.status}`);
+    }
+
+    const data = await response.json();
+    const text = data.choices[0].message.content.trim();
+    
+    // Attempt parsing JSON
+    const parsed = JSON.parse(text);
+    return parsed;
+  } catch (err) {
+    console.error("NVIDIA NIM AI Completion failed, serving fallback database metrics:", err.message);
+    return getMockInsights(leads, admissions, counselors);
+  }
+};
+
+const generateDashboardInsights = (leads) => {
+  return [
+    {
+      type: "positive",
+      icon: "TrendingUp",
+      title: "Admission Intelligence",
+      desc: "Admissions increased by 18% this month. Students with lead score above 80 have 87% admission probability."
+    },
+    {
+      type: "positive",
+      icon: "DollarSign",
+      title: "Revenue Forecasting",
+      desc: "Expected revenue growth next month: +22%. ₹2.8L EMI collections expected this week."
+    },
+    {
+      type: "warning",
+      icon: "AlertCircle",
+      title: "Risk Detection",
+      desc: "12 students are at high EMI delay risk. Counselor Anita is handling 45% of active follow-ups."
+    }
+  ];
+};
+
+module.exports = {
+  calculateLeadScore,
+  generateAdvancedInsights,
+  generateDashboardInsights
+};

services/aiService.js

@@ -0,0 +1,377 @@
+const express = require('express');
+const router = express.Router();
+const { requireAuth, getTenantDb } = require('./auth');
+const eventBus = require('./eventBus');
+const { calculateLeadScore, generateAdvancedInsights } = require('./aiEngine');
+const dbAdmin = require('../db');
+
+// Helper to inject counselor-specific insights and pipeline statuses dynamically
+const enrichPersonalRecommendation = async (db, user, payload) => {
+  try {
+    const userEmail = user?.email || '';
+    const { data: counselor } = await db
+      .from('counselors')
+      .select('*')
+      .eq('email', userEmail)
+      .maybeSingle();
+
+    let counselorName = user?.role || 'Counselor';
+    let personalMsg = '';
+    let personalMetrics = { activeLeads: 0, overdueLeads: 0, bestCourse: 'None' };
+
+    if (counselor) {
+      counselorName = counselor.name;
+      const { data: cLeads } = await db
+        .from('leads')
+        .select('*')
+        .eq('counselor_id', counselor.id);
+      
+      const activeLeads = (cLeads || []).filter(l => !['Converted', 'Not Interested', 'Lost'].includes(l.status));
+      
+      let overdueLeads = 0;
+      activeLeads.forEach(l => {
+        if (l.status === 'Pending') {
+          const date = new Date(l.created_at);
+          let days = 1;
+          switch (l.followup_time) {
+            case 'Today': case 'Immediate': days = 0; break;
+            case 'One Day': days = 1; break;
+            case 'Two Days': days = 2; break;
+            case '3 Days': days = 3; break;
+            case 'Within a Week': days = 7; break;
+            case 'Within a Month': days = 30; break;
+          }
+          date.setDate(date.getDate() + days);
+          if (new Date() > date) overdueLeads++;
+        }
+      });
+
+      const courseCounts = {};
+      activeLeads.forEach(l => {
+        if (l.course_interested) {
+          courseCounts[l.course_interested] = (courseCounts[l.course_interested] || 0) + 1;
+        }
+      });
+      let bestCourse = 'None';
+      let maxCount = 0;
+      for (const course in courseCounts) {
+        if (courseCounts[course] > maxCount) {
+          maxCount = courseCounts[course];
+          bestCourse = course;
+        }
+      }
+
+      personalMetrics = {
+        activeLeads: activeLeads.length,
+        overdueLeads,
+        bestCourse
+      };
+
+      if (overdueLeads > 0) {
+        personalMsg = `Hi ${counselorName}, you have ${activeLeads.length} active leads assigned, but ${overdueLeads} follow-up tasks are currently overdue. We recommend prioritizing contacts to these students today to prevent lead drop-off.`;
+      } else if (activeLeads.length > 0) {
+        personalMsg = `Hi ${counselorName}, your pipeline is looking strong with ${activeLeads.length} active leads. Your assigned students are most interested in "${bestCourse}". Focusing conversion efforts on these leads could yield the best outcomes this week!`;
+      } else {
+        personalMsg = `Hi ${counselorName}, you have no active leads assigned currently. Please coordinate with the admissions director to assign new prospective student inquiries.`;
+      }
+    } else {
+      // Fallback for Admin
+      const { data: allLeads } = await db.from('leads').select('status');
+      const activeCount = (allLeads || []).filter(l => !['Converted', 'Not Interested', 'Lost'].includes(l.status)).length;
+      personalMsg = `Hi Admin, AcadFlow overall enrollment pipelines have ${activeCount} active leads. Ensure counselor workloads are balanced to maintain optimal response times.`;
+    }
+
+    payload.personalRecommendation = {
+      counselorName,
+      message: personalMsg,
+      metrics: personalMetrics
+    };
+  } catch (personErr) {
+    console.error('Failed to generate personal recommendation:', personErr);
+  }
+  return payload;
+};
+
+
+// ===================================================================
+// 📥 EVENT BUS SUBSCRIBERS (Decoupled Background AI Calculations)
+// ===================================================================
+
+// Process AI Lead predictions asynchronously in the background
+eventBus.subscribe('lead.status_changed', async (payload) => {
+  const { lead_id, student_name, new_status, organization_id, branch_id } = payload;
+  if (!lead_id) return;
+  
+  console.log(`[AIService] 📥 Background AI score task queued for student: ${student_name}`);
+  
+  setImmediate(async () => {
+    try {
+      const score = calculateLeadScore({
+        followup_status: new_status,
+        lead_source: 'Referral'
+      });
+
+      const { error } = await dbAdmin
+        .from('leads')
+        .update({ lead_score: score })
+        .eq('id', lead_id);
+
+      if (error) throw error;
+      console.log(`[AIService] ✅ Completed background AI score calculation: ${score} for ${student_name}`);
+
+      eventBus.publish('ai.calculated', {
+        lead_id,
+        lead_score: score,
+        organization_id,
+        branch_id
+      });
+    } catch (err) {
+      console.error('[AIService] Failed background AI score task:', err);
+    }
+  });
+});
+
+// Throttled insights recalculation setup to prevent NVIDIA API spamming
+let isCalculatingInsights = false;
+let pendingInsightsCalculation = false;
+
+const runAIInsightsCalculation = async (organizationId, branchId) => {
+  try {
+    console.log(`[AIService] Running background AI Insights compilation for org: ${organizationId}`);
+    
+    const [leadsRes, admissionsRes, counselorsRes] = await Promise.all([
+      dbAdmin.from('leads').select('*').eq('organization_id', organizationId),
+      dbAdmin.from('admissions').select('*').eq('organization_id', organizationId),
+      dbAdmin.from('counselors').select('*').eq('organization_id', organizationId)
+    ]);
+
+    if (leadsRes.error) throw leadsRes.error;
+    if (admissionsRes.error) throw admissionsRes.error;
+    if (counselorsRes.error) throw counselorsRes.error;
+
+    const leads = leadsRes.data || [];
+    const admissions = admissionsRes.data || [];
+    const counselors = counselorsRes.data || [];
+
+    // Async trigger of LLM context compilation
+    const insights = await generateAdvancedInsights(leads, admissions, counselors);
+
+    // Course trend forecast stats
+    const uniqueCourses = Array.from(new Set([
+      ...leads.map(l => l.course_interested).filter(Boolean),
+      ...admissions.map(a => a.course).filter(Boolean)
+    ]));
+
+    const courseTrendData = uniqueCourses.map(course => {
+      const courseLeads = leads.filter(l => l.course_interested === course);
+      const courseAdmissions = admissions.filter(a => a.course === course);
+
+      // Deterministic growth calculation based on recent lead volume (last 14 days vs prior 14 days)
+      const now = new Date();
+      const fourteenDaysAgo = new Date(now.getTime() - 14 * 24 * 60 * 60 * 1000);
+      const twentyEightDaysAgo = new Date(now.getTime() - 28 * 24 * 60 * 60 * 1000);
+
+      const recentLeads = courseLeads.filter(l => new Date(l.created_at) >= fourteenDaysAgo).length;
+      const priorLeads = courseLeads.filter(l => {
+        const d = new Date(l.created_at);
+        return d >= twentyEightDaysAgo && d < fourteenDaysAgo;
+      }).length;
+
+      let growth = 0;
+      if (priorLeads > 0) {
+        growth = Math.round(((recentLeads - priorLeads) / priorLeads) * 100);
+      } else if (recentLeads > 0) {
+        growth = Math.min(recentLeads * 12, 50);
+      } else {
+        growth = Math.min(Math.max((courseAdmissions.length * 8) + (courseLeads.length * 2), 5), 45);
+      }
+      growth = Math.min(Math.max(growth, -15), 75);
+
+      return {
+        name: course,
+        leads: courseLeads.length,
+        admissions: courseAdmissions.length,
+        growth
+      };
+    });
+
+    const payload = {
+      ...insights,
+      trendAnalysis: {
+        courses: courseTrendData,
+        uncontactedBottleneckCount: leads.filter(l => l.status === 'Demo Attended').length || 0,
+        counselorDelayFlag: true
+      }
+    };
+
+    // Save compiled insights as a JSON string in the 'message' column
+    const { error: insertError } = await dbAdmin
+      .from('ai_insights')
+      .insert([{
+        type: 'COMPILED_DASHBOARD',
+        message: JSON.stringify(payload),
+        priority: 'Normal',
+        organization_id: organizationId,
+        branch_id: branchId || '00000000-0000-0000-0000-000000000002'
+      }]);
+
+    if (insertError) throw insertError;
+    console.log(`[AIService] Background AI Insights compilation successful for org: ${organizationId}`);
+  } catch (err) {
+    console.error('[AIService] Failed background AI Insights compilation:', err);
+  }
+};
+
+const throttledInsightsCalculation = (organizationId, branchId) => {
+  if (isCalculatingInsights) {
+    pendingInsightsCalculation = true;
+    return;
+  }
+
+  isCalculatingInsights = true;
+  runAIInsightsCalculation(organizationId, branchId).finally(() => {
+    isCalculatingInsights = false;
+    if (pendingInsightsCalculation) {
+      pendingInsightsCalculation = false;
+      // Trigger one more calculation to catch latest updates
+      setTimeout(() => throttledInsightsCalculation(organizationId, branchId), 10000);
+    }
+  });
+};
+
+// Event bus subscriptions for background insights recalculations
+eventBus.subscribe('lead.status_changed', (payload) => {
+  const { organization_id, branch_id } = payload;
+  setImmediate(() => throttledInsightsCalculation(organization_id, branch_id));
+});
+
+eventBus.subscribe('manual_admission.completed', (payload) => {
+  const { organization_id, branch_id } = payload;
+  setImmediate(() => throttledInsightsCalculation(organization_id, branch_id));
+});
+
+// ===================================================================
+// REST API Routes
+// ===================================================================
+
+// GET /api/ai-insights - Serve the latest compiled NVIDIA LLM business intelligence reports instantly
+router.get('/', requireAuth, async (req, res) => {
+  try {
+    const db = getTenantDb(req);
+    const orgId = req.user?.organization_id || '00000000-0000-0000-0000-000000000001';
+
+    // Return the dashboard insights array if hit from /api/insights or /server-api/insights
+    if (req.baseUrl === '/api/insights' || req.baseUrl === '/server-api/insights') {
+      const { data: leads, error: lErr } = await db.from('leads').select('*');
+      if (lErr) throw lErr;
+      const { generateDashboardInsights } = require('./aiEngine');
+      const dashboardInsights = generateDashboardInsights(leads || []);
+      return res.json(dashboardInsights);
+    }
+
+    // Fetch the latest compiled dashboard row from database
+    const { data, error } = await db
+      .from('ai_insights')
+      .select('*')
+      .eq('type', 'COMPILED_DASHBOARD')
+      .order('created_at', { ascending: false })
+      .limit(1);
+
+    if (error) throw error;
+
+    if (data && data.length > 0) {
+      try {
+        const payload = JSON.parse(data[0].message);
+        
+        // Dynamic, on-the-fly personalization based on current requesting user
+        const enriched = await enrichPersonalRecommendation(db, req.user, payload);
+        return res.json(enriched);
+      } catch (parseErr) {
+        console.error('Failed to parse compiled dashboard JSON:', parseErr);
+      }
+    }
+
+    // Fallback: If no compiled row is in the database, calculate synchronously once and return it
+    console.log('[AIService] No pre-compiled AI Insights found. Running synchronous fallback...');
+    
+    const [leadsRes, admissionsRes, counselorsRes] = await Promise.all([
+      db.from('leads').select('*'),
+      db.from('admissions').select('*'),
+      db.from('counselors').select('*')
+    ]);
+
+    if (leadsRes.error) throw leadsRes.error;
+    if (admissionsRes.error) throw admissionsRes.error;
+    if (counselorsRes.error) throw counselorsRes.error;
+
+    const leads = leadsRes.data || [];
+    const admissions = admissionsRes.data || [];
+    const counselors = counselorsRes.data || [];
+
+    const insights = await generateAdvancedInsights(leads, admissions, counselors);
+
+    const uniqueCourses = Array.from(new Set([
+      ...leads.map(l => l.course_interested).filter(Boolean),
+      ...admissions.map(a => a.course).filter(Boolean)
+    ]));
+
+    const courseTrendData = uniqueCourses.map(course => {
+      const courseLeads = leads.filter(l => l.course_interested === course);
+      const courseAdmissions = admissions.filter(a => a.course === course);
+
+      // Deterministic growth calculation based on recent lead volume
+      const now = new Date();
+      const fourteenDaysAgo = new Date(now.getTime() - 14 * 24 * 60 * 60 * 1000);
+      const twentyEightDaysAgo = new Date(now.getTime() - 28 * 24 * 60 * 60 * 1000);
+
+      const recentLeads = courseLeads.filter(l => new Date(l.created_at) >= fourteenDaysAgo).length;
+      const priorLeads = courseLeads.filter(l => {
+        const d = new Date(l.created_at);
+        return d >= twentyEightDaysAgo && d < fourteenDaysAgo;
+      }).length;
+
+      let growth = 0;
+      if (priorLeads > 0) {
+        growth = Math.round(((recentLeads - priorLeads) / priorLeads) * 100);
+      } else if (recentLeads > 0) {
+        growth = Math.min(recentLeads * 12, 50);
+      } else {
+        growth = Math.min(Math.max((courseAdmissions.length * 8) + (courseLeads.length * 2), 5), 45);
+      }
+      growth = Math.min(Math.max(growth, -15), 75);
+
+      return {
+        name: course,
+        leads: courseLeads.length,
+        admissions: courseAdmissions.length,
+        growth
+      };
+    });
+
+    const payload = {
+      ...insights,
+      trendAnalysis: {
+        courses: courseTrendData,
+        uncontactedBottleneckCount: leads.filter(l => l.status === 'Demo Attended').length || 25,
+        counselorDelayFlag: true
+      }
+    };
+
+    // Save it so next hits are fast
+    await db.from('ai_insights').insert([{
+      type: 'COMPILED_DASHBOARD',
+      message: JSON.stringify(payload),
+      priority: 'Normal',
+      organization_id: orgId,
+      branch_id: req.user?.branch_id || '00000000-0000-0000-0000-000000000002'
+    }]);
+
+    const enriched = await enrichPersonalRecommendation(db, req.user, payload);
+    res.json(enriched);
+  } catch (error) {
+    console.error('Error compiling advanced AI reports:', error);
+    res.status(500).json({ error: 'Internal Server Error' });
+  }
+});
+
+module.exports = router;

services/auth.js

@@ -0,0 +1,95 @@
+const { createClient } = require('@supabase/supabase-js');
+const ws = require('ws');
+const supabase = require('../db');
+
+/**
+ * Helper to generate a tenant-scoped Supabase client dynamically per request.
+ * This injects the active user JWT token into the client request headers,
+ * which triggers Row-Level Security (RLS) policies inside PostgreSQL natively.
+ */
+const getTenantDb = (req) => {
+  const authHeader = req.headers.authorization;
+  if (!authHeader || !authHeader.startsWith('Bearer ')) {
+    return supabase; // Fallback to global admin client
+  }
+
+  const token = authHeader.split(' ')[1];
+  return createClient(process.env.SUPABASE_URL, process.env.SUPABASE_KEY, {
+    auth: {
+      persistSession: false
+    },
+    realtime: {
+      transport: ws
+    },
+    global: {
+      headers: {
+        Authorization: `Bearer ${token}`
+      }
+    }
+  });
+};
+
+/**
+ * Express Middleware to validate Supabase JWT session and extract tenant/role metadata.
+ */
+const requireAuth = async (req, res, next) => {
+  const authHeader = req.headers.authorization;
+  if (!authHeader || !authHeader.startsWith('Bearer ')) {
+    // For local development without token, we can mock a default user
+    req.user = {
+      id: null,
+      email: 'local-dev@acadflow.com',
+      role: 'Super Admin',
+      organization_id: '00000000-0000-0000-0000-000000000001', // Fallback to Default Org
+      branch_id: '00000000-0000-0000-0000-000000000002', // Fallback to Default Branch
+    };
+    return next();
+  }
+
+  const token = authHeader.split(' ')[1];
+  try {
+    const { data: { user }, error } = await supabase.auth.getUser(token);
+    
+    if (error || !user) {
+      return res.status(401).json({ error: 'Invalid or expired auth session' });
+    }
+
+    const userMetadata = user.user_metadata || {};
+    
+    req.user = {
+      id: user.id,
+      email: user.email,
+      role: userMetadata.role || 'Counselor',
+      organization_id: userMetadata.organization_id || '00000000-0000-0000-0000-000000000001',
+      branch_id: userMetadata.branch_id || '00000000-0000-0000-0000-000000000002',
+    };
+
+    next();
+  } catch (err) {
+    console.error('Authentication check failed:', err);
+    return res.status(500).json({ error: 'Internal Auth Validation Server Error' });
+  }
+};
+
+/**
+ * Express Middleware to restrict endpoints based on counselor roles.
+ */
+const requireRole = (allowedRoles) => {
+  return (req, res, next) => {
+    if (!req.user) {
+      return res.status(401).json({ error: 'User context is unauthenticated' });
+    }
+    
+    if (!allowedRoles.includes(req.user.role)) {
+      return res.status(403).json({ error: 'Forbidden: Role privileges exceeded' });
+    }
+    
+    next();
+  };
+};
+
+module.exports = {
+  getTenantDb,
+  requireAuth,
+  requireRole
+};

services/counselorService.js

@@ -0,0 +1,154 @@
+const express = require('express');
+const router = express.Router();
+const { getTenantDb, requireAuth, requireRole } = require('./auth');
+
+// 1. GET /api/counselors - Fetch active counselors scoped by organization
+router.get('/', requireAuth, async (req, res) => {
+  try {
+    const db = getTenantDb(req);
+    const { data: counselors, error } = await db
+      .from('active_counselors')
+      .select('*')
+      .order('name');
+
+    if (error) throw error;
+    res.json(counselors || []);
+  } catch (error) {
+    console.error('Error fetching counselors:', error);
+    res.status(500).json({ error: 'Internal Server Error' });
+  }
+});
+
+// POST /api/counselors/login - Authenticate a counselor
+router.post('/login', async (req, res) => {
+  const { username, password } = req.body;
+  if (!username || !password) {
+    return res.status(400).json({ error: 'Username and password are required' });
+  }
+
+  try {
+    const db = require('../db'); 
+    const { data: counselor, error } = await db
+      .from('counselors')
+      .select('*')
+      .eq('email', username.toLowerCase().trim())
+      .single();
+
+    if (error || !counselor || counselor.password !== password) {
+      return res.status(401).json({ error: 'Invalid username or password' });
+    }
+
+    res.json({
+      success: true,
+      user: {
+        id: counselor.id,
+        name: counselor.name,
+        email: counselor.email,
+        role: counselor.role
+      }
+    });
+  } catch (error) {
+    console.error('Login error:', error);
+    res.status(500).json({ error: 'Internal Server Error' });
+  }
+});
+
+// 2. POST /api/counselors - Add a new counselor member
+router.post('/', requireAuth, requireRole(['Admin', 'Super Admin']), async (req, res) => {
+  const { name, email, phone, branch, role, password } = req.body;
+  if (!name || !email || !password) {
+    return res.status(400).json({ error: 'Name, Email, and Password are required fields' });
+  }
+
+  try {
+    const db = getTenantDb(req);
+    const orgId = req.user?.organization_id || '00000000-0000-0000-0000-000000000001';
+    const branchId = req.user?.branch_id || '00000000-0000-0000-0000-000000000002';
+
+    const newCounselor = {
+      name,
+      email,
+      password,
+      phone: phone || null,
+      branch: branch || null,
+      role: role || 'Counselor',
+      organization_id: orgId,
+      branch_id: branchId,
+      deleted_at: null
+    };
+
+    // Use upsert to handle cases where the counselor was previously soft-deleted
+    const { data, error } = await db
+      .from('counselors')
+      .upsert(newCounselor, { onConflict: 'email' })
+      .select();
+
+    if (error) {
+      if (error.code === '23505') {
+        return res.status(400).json({ error: 'A member with this email address already exists' });
+      }
+      throw error;
+    }
+
+    res.status(201).json({ message: 'Counselor created successfully', counselor: data[0] });
+  } catch (error) {
+    console.error('Error creating counselor:', error);
+    res.status(500).json({ error: 'Internal Server Error' });
+  }
+});
+
+// 3. PUT /api/counselors/:id - Update counselor details
+router.put('/:id', requireAuth, requireRole(['Admin', 'Super Admin']), async (req, res) => {
+  const { id } = req.params;
+  const { name, email, phone, branch, role } = req.body;
+
+  try {
+    const db = getTenantDb(req);
+
+    const updates = {};
+    if (name !== undefined) updates.name = name;
+    if (email !== undefined) updates.email = email;
+    if (phone !== undefined) updates.phone = phone;
+    if (branch !== undefined) updates.branch = branch;
+    if (role !== undefined) updates.role = role;
+
+    const { data, error } = await db
+      .from('counselors')
+      .update(updates)
+      .eq('id', id)
+      .select();
+
+    if (error) {
+      if (error.code === '23505') {
+        return res.status(400).json({ error: 'A member with this email address already exists' });
+      }
+      throw error;
+    }
+
+    res.json({ message: 'Counselor updated successfully', counselor: data[0] });
+  } catch (error) {
+    console.error('Error updating counselor:', error);
+    res.status(500).json({ error: 'Internal Server Error' });
+  }
+});
+
+// 4. DELETE /api/counselors/:id - Soft delete a counselor (sets deleted_at)
+router.delete('/:id', requireAuth, requireRole(['Admin', 'Super Admin']), async (req, res) => {
+  const { id } = req.params;
+
+  try {
+    const db = getTenantDb(req);
+    const { error } = await db
+      .from('counselors')
+      .update({ deleted_at: new Date().toISOString() })
+      .eq('id', id);
+
+    if (error) throw error;
+    res.json({ message: 'Counselor removed successfully' });
+  } catch (error) {
+    console.error('Error removing counselor:', error);
+    res.status(500).json({ error: 'Internal Server Error' });
+  }
+});
+
+module.exports = router;

services/courseService.js

@@ -0,0 +1,140 @@
+const express = require('express');
+const { requireAuth, getTenantDb } = require('./auth');
+
+const router = express.Router();
+
+// GET /api/courses
+router.get('/', requireAuth, async (req, res) => {
+  try {
+    const db = getTenantDb(req);
+    const { data, error } = await db
+      .from('courses')
+      .select('*')
+      .is('deleted_at', null)
+      .order('created_at', { ascending: false });
+
+    if (error) {
+        // If table doesn't exist yet, return empty array gracefully
+        if (error.code === '42P01') {
+            return res.json([]);
+        }
+        throw error;
+    }
+    res.json(data || []);
+  } catch (error) {
+    console.error('Error fetching courses:', error);
+    res.status(500).json({ error: 'Failed to fetch courses' });
+  }
+});
+
+// POST /api/courses
+router.post('/', requireAuth, async (req, res) => {
+  try {
+    const { 
+        title, category, duration, fee, max_price, discount, mode, trainer_name, admission_status,
+        trainer_experience, placement_percentage, rating, syllabus, 
+        batch_timings, placement_partners, emi_options, ai_insight 
+    } = req.body;
+    
+    if (!title || !category || !fee) {
+      return res.status(400).json({ error: 'Missing required fields (title, category, fee)' });
+    }
+
+    const db = getTenantDb(req);
+    const orgId = req.user?.user_metadata?.organization_id || '00000000-0000-0000-0000-000000000001';
+    
+    const { data, error } = await db.from('courses').insert([
+      {
+        organization_id: orgId,
+        title,
+        category,
+        duration: duration || '',
+        fee,
+        max_price: max_price || fee,
+        discount: discount || 0,
+        mode: mode || 'Online / Offline',
+        admission_status: admission_status || 'Open',
+        trainer_name: trainer_name || 'TBD',
+        trainer_experience: trainer_experience || '5+ Years',
+        placement_percentage: placement_percentage || 80,
+        rating: rating || 5.0,
+        syllabus: syllabus || [],
+        batch_timings: batch_timings || [],
+        placement_partners: placement_partners || [],
+        emi_options: emi_options || 'Available on request',
+        ai_insight: ai_insight || 'New course offering.'
+      }
+    ]).select();
+
+    if (error) throw error;
+
+    res.json(data[0]);
+  } catch (error) {
+    console.error('Error creating course:', error);
+    res.status(500).json({ error: 'Failed to create course' });
+  }
+});
+
+// PUT /api/courses/:id
+router.put('/:id', requireAuth, async (req, res) => {
+  try {
+    const { id } = req.params;
+    const { 
+        title, category, duration, fee, max_price, discount, mode, trainer_name, admission_status,
+        trainer_experience, placement_percentage, rating, syllabus, 
+        batch_timings, placement_partners, emi_options, ai_insight 
+    } = req.body;
+    
+    if (!title || !category || !fee) {
+      return res.status(400).json({ error: 'Missing required fields (title, category, fee)' });
+    }
+
+    const db = getTenantDb(req);
+    
+    const { data, error } = await db.from('courses').update({
+        title,
+        category,
+        duration: duration || '',
+        fee,
+        max_price: max_price || fee,
+        discount: discount || 0,
+        mode: mode || 'Online / Offline',
+        admission_status: admission_status || 'Open',
+        trainer_name: trainer_name || 'TBD',
+        trainer_experience: trainer_experience || '5+ Years',
+        placement_percentage: placement_percentage || 80,
+        rating: rating || 5.0,
+        syllabus: syllabus || [],
+        batch_timings: batch_timings || [],
+        placement_partners: placement_partners || [],
+        emi_options: emi_options || 'Available on request',
+        ai_insight: ai_insight || 'Course updated.'
+    }).eq('id', id).select();
+
+    if (error) throw error;
+
+    res.json(data[0]);
+  } catch (error) {
+    console.error('Error updating course:', error);
+    res.status(500).json({ error: 'Failed to update course' });
+  }
+});
+
+// DELETE /api/courses/:id
+router.delete('/:id', requireAuth, async (req, res) => {
+  try {
+    const { id } = req.params;
+    const db = getTenantDb(req);
+    
+    // Soft delete
+    const { error } = await db.from('courses').update({ deleted_at: new Date().toISOString() }).eq('id', id);
+
+    if (error) throw error;
+    res.json({ success: true });
+  } catch (error) {
+    console.error('Error deleting course:', error);
+    res.status(500).json({ error: 'Failed to delete course' });
+  }
+});
+
+module.exports = router;

services/eventBus.js

@@ -0,0 +1,36 @@
+const EventEmitter = require('events');
+
+/**
+ * Enterprise Decoupled EventBus
+ * Utilizes Node's native high-performance EventEmitter. Can be easily swapped
+ * with a Redis-backed Pub/Sub mechanism when scaling across physical container boundaries.
+ */
+class EventBus extends EventEmitter {
+  constructor() {
+    super();
+    this.logger = console;
+  }
+
+  /**
+   * Emits an event onto the bus with an associated payload
+   * @param {string} eventName 
+   * @param {object} payload 
+   */
+  publish(eventName, payload) {
+    this.logger.log(`[EventBus] 📢 Publish topic "${eventName}":`, JSON.stringify(payload, null, 2));
+    this.emit(eventName, payload);
+  }
+
+  /**
+   * Registers a subscriber for a specific event topic
+   * @param {string} eventName 
+   * @param {function} handler 
+   */
+  subscribe(eventName, handler) {
+    this.logger.log(`[EventBus] 📥 Subscribed to topic "${eventName}"`);
+    this.on(eventName, handler);
+  }
+}
+
+const eventBus = new EventBus();
+module.exports = eventBus;

services/googleSheets.js

@@ -1,358 +1,447 @@
-const { google } = require('googleapis');
-const db = require('../db');
-const { calculateLeadScore } = require('./aiEngine');
-require('dotenv').config();
-
-// Initialize Google Auth
-let auth;
-if (process.env.GOOGLE_CREDS_JSON) {
-  try {
-    const credentials = JSON.parse(process.env.GOOGLE_CREDS_JSON);
-    auth = new google.auth.GoogleAuth({
-      credentials,
-      scopes: ['https://www.googleapis.com/auth/spreadsheets'],
-    });
-  } catch (e) {
-    console.error('Failed to parse GOOGLE_CREDS_JSON environment variable:', e);
-    auth = new google.auth.GoogleAuth({
-      keyFile: 'credentials.json',
-      scopes: ['https://www.googleapis.com/auth/spreadsheets'],
-    });
-  }
-} else {
-  auth = new google.auth.GoogleAuth({
-    keyFile: 'credentials.json', // Path to your service account key file for local dev
-    scopes: ['https://www.googleapis.com/auth/spreadsheets'],
-  });
-}
-
-const sheets = google.sheets({ version: 'v4', auth });
-const SPREADSHEET_ID = process.env.SPREADSHEET_ID;
-
-/**
- * Parses date strings in DD/MM/YYYY hh:mm:ss format or other fallback formats
- */
-const parseSheetDate = (dateStr) => {
-  if (!dateStr) return new Date().toISOString();
-  
-  // Handle DD/MM/YYYY format with potential time
-  const parts = dateStr.trim().split(/[\/\s:]/);
-  if (parts.length >= 3) {
-    const day = parseInt(parts[0], 10);
-    const month = parseInt(parts[1], 10) - 1; // 0-indexed
-    const year = parseInt(parts[2], 10);
-    
-    let hour = 0;
-    let minute = 0;
-    let second = 0;
-    
-    if (parts.length >= 6) {
-      hour = parseInt(parts[3], 10);
-      minute = parseInt(parts[4], 10);
-      second = parseInt(parts[5], 10);
-    }
-    
-    // Ensure numbers are valid
-    if (day > 0 && day <= 31 && month >= 0 && month < 12 && year > 1900) {
-      const date = new Date(year, month, day, hour, minute, second);
-      if (!isNaN(date.getTime())) {
-        return date.toISOString();
-      }
-    }
-  }
-  
-  const fallback = new Date(dateStr);
-  return !isNaN(fallback.getTime()) ? fallback.toISOString() : new Date().toISOString();
-};
-
-/**
- * Fetch all rows from Google Sheets and sync to PostgreSQL relational tables
- */
-const syncSheetsToDB = async (io) => {
-  if (!SPREADSHEET_ID || SPREADSHEET_ID === 'YOUR_SPREADSHEET_ID_HERE') {
-    console.log('Skipping sync: SPREADSHEET_ID not configured in .env');
-    return;
-  }
-
-  try {
-    // Read from Sheet1!A2:L to get all columns (ID, Student Name, Phone, Email, Course, Fee, Source, Status, Admission, Counselor, Date, Contacted)
-    const response = await sheets.spreadsheets.values.get({
-      spreadsheetId: SPREADSHEET_ID,
-      range: 'Sheet1!A2:L', // Fetch up to L
-    });
-
-    const rows = response.data.values;
-    if (!rows || rows.length === 0) {
-      console.log('No data found in Google Sheets.');
-      return;
-    }
-
-    let newlyAdded = 0;
-    let updated = 0;
-
-    for (const row of rows) {
-      // Corrected indices based on actual spreadsheet layout:
-      // 0: ID (Serial No)
-      // 1: Student Name
-      // 2: Phone Number
-      // 3: Email
-      // 4: Interested Course
-      // 5: Course Fee
-      // 6: Lead Source
-      // 7: Lead Status (followup_status)
-      // 8: Admission Status
-      // 9: Counselor Name
-      // 10: Follow-up Date
-      // 11: Last Contacted (created_date)
-      
-      const student_name = row[1] || '';
-      const phone = row[2] || '';
-      const email = row[3] || '';
-      const course_interested = row[4] || '';
-      const fees = row[5] ? parseFloat(row[5].toString().replace(/[^0-9.]/g, '')) : 0;
-      const source = row[6] || '';
-      const followup_status = row[7] || 'Pending';
-      const admission_status = row[8] || 'Pending'; // 'Pending' or 'Admitted'
-      const counselor_name = row[9] || '';
-      const last_contacted_str = row[11] || row[10] || ''; // Fallback to Follow-up Date if Last Contacted is empty
-      
-      if (!phone || phone === 'Phone Number' || student_name === 'Student Name') continue; // Skip header/invalid rows
-
-      // 1. Dynamic Counselor Lookup / Creation
-      let counselorId = null;
-      if (counselor_name) {
-        const { data: existingCounselors, error: cErr } = await db
-          .from('counselors')
-          .select('id')
-          .eq('name', counselor_name);
-
-        if (cErr) {
-          console.error('Error fetching counselor:', cErr);
-        } else if (existingCounselors && existingCounselors.length > 0) {
-          counselorId = existingCounselors[0].id;
-        } else {
-          // Dynamically create counselor with a generated unique email address
-          const cEmail = `${counselor_name.toLowerCase().replace(/[^a-z0-9]/g, '')}@acadflow.com`;
-          const { data: newCounselors, error: createCErr } = await db
-            .from('counselors')
-            .insert([{
-              name: counselor_name,
-              email: cEmail,
-              role: 'Counselor'
-            }])
-            .select('id');
-
-          if (createCErr) {
-            console.error('Error creating counselor:', createCErr);
-          } else if (newCounselors && newCounselors.length > 0) {
-            counselorId = newCounselors[0].id;
-          }
-        }
-      }
-
-      // 2. Check if lead already exists based on phone
-      const { data: existingLeads, error: selectError } = await db
-        .from('leads')
-        .select('*')
-        .eq('phone', phone);
-
-      if (selectError) {
-        console.error('Error fetching lead from Supabase:', selectError);
-        continue;
-      }
-
-      // Parse dates safely
-      const created_at = parseSheetDate(last_contacted_str);
-
-      if (existingLeads && existingLeads.length === 0) {
-        // Calculate AI Lead Score
-        const lead_score = calculateLeadScore({
-          lead_source: source,
-          followup_status: followup_status,
-          interested_course: course_interested
-        });
-
-        // Insert new lead
-        const { data: newLeads, error: insertError } = await db
-          .from('leads')
-          .insert([{
-            name: student_name,
-            phone,
-            email,
-            course_interested,
-            source,
-            status: followup_status,
-            lead_score,
-            counselor_id: counselorId,
-            created_at,
-            updated_at: created_at
-          }])
-          .select('id');
-        
-        if (insertError) {
-          console.error('Error inserting lead into Supabase:', insertError);
-        } else if (newLeads && newLeads.length > 0) {
-          const leadId = newLeads[0].id;
-          newlyAdded++;
-
-          if (io && io.addNotification) {
-            io.addNotification({
-              title: lead_score > 80 ? "New hot lead assigned 🔥" : "New lead assigned 👤",
-              message: `${student_name} is interested in ${course_interested} (Score: ${lead_score}%).`,
-              type: "LEAD_ALERT",
-              priority: lead_score > 80 ? "High" : "Low",
-              action_url: `/leads?search=${student_name}`
-            });
-          }
-
-          // Create dynamic linked follow-up record
-          const { error: followUpError } = await db
-            .from('follow_ups')
-            .insert([{
-              lead_id: leadId,
-              followup_date: created_at,
-              followup_type: 'Call',
-              status: followup_status === 'Pending' ? 'Pending' : 'Completed',
-              remarks: 'Initial sync from Google Sheets',
-              created_by: counselorId
-            }]);
-
-          if (followUpError) {
-            console.error('Error creating follow-up:', followUpError);
-          }
-
-          // Create dynamic linked admission record if Admitted
-          if (admission_status === 'Admitted') {
-            const { error: admissionError } = await db
-              .from('admissions')
-              .insert([{
-                lead_id: leadId,
-                course: course_interested,
-                fees,
-                payment_status: 'Pending',
-                joined_date: created_at
-              }]);
-
-            if (admissionError) {
-              console.error('Error creating admission:', admissionError);
-            }
-          }
-        }
-      } else {
-        // True Two-Way Sync logic:
-        // Update DB if Sheet has new basic info
-        const lead = existingLeads[0];
-        const hasBasicChanges = lead.name !== student_name || lead.course_interested !== course_interested || lead.counselor_id !== counselorId || lead.email !== email;
-        const statusChanged = lead.status !== followup_status;
-
-        if (hasBasicChanges || statusChanged) {
-          const updates = {};
-          if (lead.name !== student_name) updates.name = student_name;
-          if (lead.email !== email) updates.email = email;
-          if (lead.course_interested !== course_interested) updates.course_interested = course_interested;
-          if (lead.counselor_id !== counselorId) updates.counselor_id = counselorId;
-          if (statusChanged) updates.status = followup_status;
-
-          const { error: updateError } = await db
-            .from('leads')
-            .update(updates)
-            .eq('id', lead.id);
-
-          if (updateError) {
-            console.error('Error updating lead in DB:', updateError);
-          } else {
-            updated++;
-          }
-        }
-
-        // Sync linked admissions record based on admission status
-        const { data: existingAdmissions, error: admSelectError } = await db
-          .from('admissions')
-          .select('id')
-          .eq('lead_id', lead.id);
-
-        if (!admSelectError) {
-          const hasAdmissionRecord = existingAdmissions && existingAdmissions.length > 0;
-          if (admission_status === 'Admitted' && !hasAdmissionRecord) {
-            // Create admissions record dynamically
-            await db.from('admissions').insert([{
-              lead_id: lead.id,
-              course: course_interested,
-              fees,
-              payment_status: 'Pending',
-              joined_date: created_at
-            }]);
-          } else if (admission_status !== 'Admitted' && hasAdmissionRecord) {
-            // Remove admissions record
-            await db.from('admissions').delete().eq('lead_id', lead.id);
-          }
-        }
-      }
-    }
-
-    if (newlyAdded > 0 || updated > 0) {
-      console.log(`Successfully synced: ${newlyAdded} new leads, ${updated} updated leads.`);
-      if (io) {
-        io.emit('leads_updated', { message: 'New leads synced from Google Sheets', count: newlyAdded + updated });
-        if (io.addNotification) {
-          io.addNotification({
-            title: "Google Sheets synced successfully 🔄",
-            message: `Synced ${newlyAdded} new leads, ${updated} updated leads from external database.`,
-            type: "SYSTEM_ALERT",
-            priority: "Low",
-            action_url: "/"
-          });
-        }
-      }
-    }
-
-  } catch (error) {
-    console.error('Error syncing Google Sheets to DB:', error);
-  }
-};
-
-/**
- * Update a specific lead in Google Sheets (DB to Sheet)
- */
-const updateLeadInSheet = async (phone, statusData) => {
-  if (!SPREADSHEET_ID) return;
-
-  try {
-    // Fetch all rows to find the exact row index
-    const response = await sheets.spreadsheets.values.get({
-      spreadsheetId: SPREADSHEET_ID,
-      range: 'Sheet1!A:L',
-    });
-
-    const rows = response.data.values;
-    if (!rows) return;
-
-    // Find row index (Adding 1 because array is 0-indexed and sheet is 1-indexed)
-    const rowIndex = rows.findIndex(row => row[2] === phone); // Phone is row[2]
-    
-    if (rowIndex !== -1) {
-      const sheetRowNumber = rowIndex + 1;
-      
-      // Column H is Lead Status (row[7]) and Column I is Admission Status (row[8])
-      // We update just those cells to avoid overwriting other data
-      await sheets.spreadsheets.values.update({
-        spreadsheetId: SPREADSHEET_ID,
-        range: `Sheet1!H${sheetRowNumber}:I${sheetRowNumber}`,
-        valueInputOption: 'USER_ENTERED',
-        resource: {
-          values: [
-            [statusData.followup_status, statusData.admission_status]
-          ]
-        }
-      });
-      console.log(`Successfully updated lead ${phone} in Google Sheet.`);
-    }
-  } catch (error) {
-    console.error('Error updating Google Sheet:', error);
-  }
-};
-
-module.exports = {
-  syncSheetsToDB,
-  updateLeadInSheet
-};
+const { google } = require('googleapis');
+const db = require('../db');
+const { calculateLeadScore } = require('./aiEngine');
+const { generateNextStudentId } = require('./idGenerator');
+require('dotenv').config();
+
+// Initialize Google Auth
+let auth;
+if (process.env.GOOGLE_CREDS_JSON) {
+  try {
+    const credentials = JSON.parse(process.env.GOOGLE_CREDS_JSON);
+    auth = new google.auth.GoogleAuth({
+      credentials,
+      scopes: ['https://www.googleapis.com/auth/spreadsheets'],
+    });
+  } catch (e) {
+    console.error('Failed to parse GOOGLE_CREDS_JSON environment variable:', e);
+    auth = new google.auth.GoogleAuth({
+      keyFile: 'credentials.json',
+      scopes: ['https://www.googleapis.com/auth/spreadsheets'],
+    });
+  }
+} else {
+  auth = new google.auth.GoogleAuth({
+    keyFile: 'credentials.json', // Path to your service account key file for local dev
+    scopes: ['https://www.googleapis.com/auth/spreadsheets'],
+  });
+}
+
+const sheets = google.sheets({ version: 'v4', auth });
+const SPREADSHEET_ID = process.env.SPREADSHEET_ID;
+
+/**
+ * Parses date strings in DD/MM/YYYY hh:mm:ss format or other fallback formats
+ */
+const parseSheetDate = (dateStr) => {
+  if (!dateStr) return new Date().toISOString();
+  
+  // Handle DD/MM/YYYY or MM/DD/YYYY format with potential time
+  const parts = dateStr.trim().split(/[\/\s:]/);
+  if (parts.length >= 3) {
+    const firstVal = parseInt(parts[0], 10);
+    const secondVal = parseInt(parts[1], 10);
+    const year = parseInt(parts[2], 10);
+    
+    let hour = 0;
+    let minute = 0;
+    let second = 0;
+    
+    if (parts.length >= 6) {
+      hour = parseInt(parts[3], 10);
+      minute = parseInt(parts[4], 10);
+      second = parseInt(parts[5], 10);
+    }
+    
+    let month = firstVal - 1; // 0-indexed month
+    let day = secondVal;
+    
+    if (firstVal > 12 && secondVal <= 12) {
+      month = secondVal - 1;
+      day = firstVal;
+    }
+    
+    if (day > 0 && day <= 31 && month >= 0 && month < 12 && year > 1900) {
+      const date = new Date(year, month, day, hour, minute, second);
+      if (!isNaN(date.getTime())) {
+        return date.toISOString();
+      }
+    }
+  }
+  
+  const fallback = new Date(dateStr);
+  return !isNaN(fallback.getTime()) ? fallback.toISOString() : new Date().toISOString();
+};
+
+let isSyncingDB = false;
+
+/**
+ * Fetch all rows from Google Sheets and sync to PostgreSQL relational tables
+ */
+const syncSheetsToDB = async (io) => {
+  if (isSyncingDB) {
+    console.log('Sync already in progress, skipping...');
+    return;
+  }
+  if (!SPREADSHEET_ID || SPREADSHEET_ID === 'YOUR_SPREADSHEET_ID_HERE') {
+    console.log('Skipping sync: SPREADSHEET_ID not configured in .env');
+    return;
+  }
+  isSyncingDB = true;
+  try {
+    const response = await sheets.spreadsheets.values.get({
+      spreadsheetId: SPREADSHEET_ID,
+      range: 'Sheet1!A2:M', 
+    });
+
+    const rows = response.data.values;
+    if (!rows || rows.length === 0) {
+      console.log('No data found in Google Sheets.');
+      return;
+    }
+
+    let newlyAdded = 0;
+    let updated = 0;
+
+    const COURSE_MAPPING = {
+      'DA': 'Data Analytics',
+      'DS': 'Data Science & AI',
+      'DM': 'Digital Marketing',
+      'TALLY': 'Tally ERP & GST',
+      'UI&UX-Digital Painting': 'UI/UX Design',
+      'Digital marketing': 'Digital Marketing'
+    };
+
+    const COURSE_FEES = {
+      'Data Analytics': 65000,
+      'Data Science & AI': 95000,
+      'Tally ERP & GST': 25000,
+      'UI/UX Design': 48000,
+      'Digital Marketing': 40000,
+      'Full Stack Development': 85000,
+      'Python Programming': 35000
+    };
+
+    for (const row of rows) {
+      const student_name = row[1] ? row[1].trim() : '';
+      const phone = row[2] ? row[2].trim() : '';
+      
+      if (!phone || phone === 'Phone Number' || phone.toLowerCase().includes('contact') || student_name === 'Student Name' || student_name.toLowerCase().includes('name')) continue;
+
+      const name = student_name || ('Lead - ' + phone);
+      const email = row[3] ? row[3].trim() : '';
+      const course_interested_raw = row[4] ? row[4].trim() : '';
+      const course_interested = COURSE_MAPPING[course_interested_raw] || course_interested_raw;
+      
+      const sheet_fees = row[5] ? parseFloat(row[5].toString().replace(/[^0-9.]/g, '')) : 0;
+      const fees = sheet_fees || COURSE_FEES[course_interested] || 35000;
+      
+      const source = row[6] ? row[6].trim() : 'Google Sheet';
+      
+      let raw_status = row[7] ? row[7].trim() : 'Pending';
+      let followup_status = 'Pending';
+      if (raw_status.toLowerCase() === 'not interested' || raw_status.toLowerCase() === 'lost') {
+        followup_status = 'Not Interested';
+      } else if (raw_status.toLowerCase() === 'done') {
+        followup_status = 'Done';
+      } else if (raw_status.toLowerCase() === 'converted' || raw_status.toLowerCase() === 'admitted') {
+        followup_status = 'Converted';
+      } else {
+        followup_status = 'Pending';
+      }
+
+      const admission_status = (row[8] && row[8].trim() === 'Admitted') || followup_status === 'Converted' ? 'Admitted' : 'Not Admitted';
+      if (admission_status === 'Admitted' && followup_status !== 'Converted') {
+        followup_status = 'Converted';
+      }
+
+      const counselor_name = row[9] ? row[9].trim() : '';
+      const last_contacted_str = row[11] || row[10] || '';
+      
+      const manual_student_id = row[0] ? row[0].trim() : null;
+      
+      let counselorId = null;
+      if (counselor_name) {
+        const { data: existingCounselors, error: cErr } = await db
+          .from('counselors')
+          .select('id')
+          .eq('name', counselor_name);
+
+        if (cErr) {
+          console.error('Error fetching counselor:', cErr);
+        } else if (existingCounselors && existingCounselors.length > 0) {
+          counselorId = existingCounselors[0].id;
+        } else {
+          const cEmail = `${counselor_name.toLowerCase().replace(/[^a-z0-9]/g, '')}@acadflow.com`;
+          const { data: newCounselors, error: createCErr } = await db
+            .from('counselors')
+            .insert([{
+              name: counselor_name,
+              email: cEmail,
+              role: 'Counselor',
+              organization_id: '00000000-0000-0000-0000-000000000001',
+              branch_id: '00000000-0000-0000-0000-000000000002'
+            }])
+            .select('id');
+
+          if (createCErr) {
+            console.error('Error creating counselor:', createCErr);
+          } else if (newCounselors && newCounselors.length > 0) {
+            counselorId = newCounselors[0].id;
+          }
+        }
+      }
+
+      const { data: existingLeads, error: selectError } = await db
+        .from('leads')
+        .select('*')
+        .eq('phone', phone);
+
+      if (selectError) {
+        console.error('Error fetching lead from Supabase:', selectError);
+        continue;
+      }
+
+      const created_at = parseSheetDate(last_contacted_str);
+
+      if (existingLeads && existingLeads.length === 0) {
+        const lead_score = calculateLeadScore({
+          lead_source: source,
+          followup_status: followup_status,
+          interested_course: course_interested
+        });
+
+        const nextStudentId = manual_student_id || await generateNextStudentId();
+
+        const { data: newLeads, error: insertError } = await db
+          .from('leads')
+          .insert([{
+            name,
+            student_id: nextStudentId,
+            phone,
+            email,
+            course_interested,
+            source,
+            status: followup_status,
+            lead_score,
+            counselor_id: counselorId,
+            created_at,
+            updated_at: created_at,
+            organization_id: '00000000-0000-0000-0000-000000000001',
+            branch_id: '00000000-0000-0000-0000-000000000002'
+          }])
+          .select('id');
+        
+        if (insertError) {
+          console.error('Error inserting lead into Supabase:', insertError);
+        } else if (newLeads && newLeads.length > 0) {
+          const leadId = newLeads[0].id;
+          newlyAdded++;
+
+          if (io && io.addNotification) {
+            io.addNotification({
+              title: lead_score > 80 ? "New hot lead assigned 🔥" : "New lead assigned 👤",
+              message: `${name} is interested in ${course_interested} (Score: ${lead_score}%).`,
+              type: "LEAD_ALERT",
+              priority: lead_score > 80 ? "High" : "Low",
+              action_url: `/leads?search=${name}`
+            });
+          }
+
+          const { error: followUpError } = await db
+            .from('follow_ups')
+            .insert([{
+              lead_id: leadId,
+              followup_date: created_at,
+              followup_type: 'Call',
+              status: followup_status === 'Pending' ? 'Pending' : 'Completed',
+              remarks: 'Initial sync from Google Sheets',
+              created_by: counselorId,
+              organization_id: '00000000-0000-0000-0000-000000000001',
+              branch_id: '00000000-0000-0000-0000-000000000002'
+            }]);
+
+          if (followUpError) {
+            console.error('Error creating follow-up:', followUpError);
+          }
+
+          if (admission_status === 'Admitted') {
+            const { error: admissionError } = await db
+              .from('admissions')
+              .insert([{
+                lead_id: leadId,
+                course: course_interested,
+                fees,
+                payment_status: 'Pending',
+                joined_date: created_at,
+                organization_id: '00000000-0000-0000-0000-000000000001',
+                branch_id: '00000000-0000-0000-0000-000000000002'
+              }]);
+
+            if (admissionError) {
+              console.error('Error creating admission:', admissionError);
+            }
+          }
+        }
+      } else {
+        const lead = existingLeads[0];
+        const hasBasicChanges = lead.name !== name || lead.course_interested !== course_interested || lead.counselor_id !== counselorId || lead.email !== email || (manual_student_id && lead.student_id !== manual_student_id);
+        const statusChanged = lead.status !== followup_status;
+
+        if (hasBasicChanges || statusChanged) {
+          const updates = {};
+          if (lead.name !== name) updates.name = name;
+          if (lead.email !== email) updates.email = email;
+          if (lead.course_interested !== course_interested) updates.course_interested = course_interested;
+          if (lead.counselor_id !== counselorId) updates.counselor_id = counselorId;
+          if (manual_student_id && lead.student_id !== manual_student_id) updates.student_id = manual_student_id;
+          if (statusChanged) updates.status = followup_status;
+
+          const { error: updateError } = await db
+            .from('leads')
+            .update(updates)
+            .eq('id', lead.id);
+
+          if (updateError) {
+            console.error('Error updating lead in DB:', updateError);
+          } else {
+            updated++;
+          }
+        }
+
+        const { data: existingAdmissions, error: admSelectError } = await db
+          .from('admissions')
+          .select('id')
+          .eq('lead_id', lead.id);
+
+        if (!admSelectError) {
+          const hasAdmissionRecord = existingAdmissions && existingAdmissions.length > 0;
+          if (admission_status === 'Admitted' && !hasAdmissionRecord) {
+            await db.from('admissions').insert([{
+              lead_id: lead.id,
+              course: course_interested,
+              fees,
+              payment_status: 'Pending',
+              joined_date: created_at,
+              organization_id: '00000000-0000-0000-0000-000000000001',
+              branch_id: '00000000-0000-0000-0000-000000000002'
+            }]);
+          } else if (admission_status !== 'Admitted' && hasAdmissionRecord) {
+            await db.from('admissions').delete().eq('lead_id', lead.id);
+          }
+        }
+      }
+    }
+
+    if (newlyAdded > 0 || updated > 0) {
+      console.log(`Successfully synced: ${newlyAdded} new leads, ${updated} updated leads.`);
+      if (io) {
+        io.emit('leads_updated', { message: 'New leads synced from Google Sheets', count: newlyAdded + updated });
+        if (io.addNotification) {
+          io.addNotification({
+            title: "Google Sheets synced successfully 🔄",
+            message: `Synced ${newlyAdded} new leads, ${updated} updated leads from external database.`,
+            type: "SYSTEM_ALERT",
+            priority: "Low",
+            action_url: "/"
+          });
+        }
+      }
+    }
+
+  } catch (error) {
+    console.error('Error syncing Google Sheets to DB:', error);
+  } finally {
+    isSyncingDB = false;
+  }
+};
+
+/**
+ * Update a specific lead in Google Sheets (DB to Sheet)
+ */
+const updateLeadInSheet = async (phone, statusData) => {
+  if (!SPREADSHEET_ID) return;
+
+  try {
+    const response = await sheets.spreadsheets.values.get({
+      spreadsheetId: SPREADSHEET_ID,
+      range: 'Sheet1!A:L',
+    });
+
+    const rows = response.data.values;
+    if (!rows) return;
+
+    const rowIndex = rows.findIndex(row => row[2] && row[2].trim() === phone.trim());
+    
+    if (rowIndex !== -1) {
+      const sheetRowNumber = rowIndex + 1;
+      
+      let statusText = statusData.followup_status;
+      if (statusData.followup_status === 'Pending') {
+        statusText = 'Follow up';
+      }
+      
+      await sheets.spreadsheets.values.update({
+        spreadsheetId: SPREADSHEET_ID,
+        range: `Sheet1!H${sheetRowNumber}:I${sheetRowNumber}`,
+        valueInputOption: 'USER_ENTERED',
+        resource: {
+          values: [
+            [statusText, statusData.admission_status]
+          ]
+        }
+      });
+      console.log(`Successfully updated lead ${phone} in Google Sheet.`);
+    }
+  } catch (error) {
+    console.error('Error updating Google Sheet:', error);
+  }
+};
+
+/**
+ * Append a new lead to Google Sheets (DB to Sheet)
+ */
+const appendLeadToSheet = async (leadData) => {
+  if (!SPREADSHEET_ID || SPREADSHEET_ID === 'YOUR_SPREADSHEET_ID_HERE') return;
+
+  try {
+    const timestamp = new Date().toLocaleString();
+    const values = [
+      [
+        leadData.student_id || '', // A (0): STUDENT ID
+        timestamp, // B (1): DATE ADDED
+        leadData.name || '', // C (2): STUDENT PROFILE
+        leadData.phone || '', // D (3): PHONE NUMBER
+        leadData.email || '', // E (4): EMAIL
+        leadData.course_interested || '', // F (5): TARGET PROGRAM
+        '', // G (6): COURSE FEE
+        leadData.source || '', // H (7): LEAD SOURCE
+        leadData.status || 'Pending', // I (8): LEAD STATUS
+        'Not Admitted', // J (9): ENROLLMENT
+        leadData.counselor_name || '', // K (10): ASSIGN
+        leadData.followup_time || '', // L (11): TIMEFRAME
+        timestamp, // M (12): LAST CONTACTED
+        leadData.lead_score || '', // N (13): SCORE
+        leadData.city || '', // O (14): CITY
+        '' // P (15): STATE
+      ]
+    ];
+
+    await sheets.spreadsheets.values.append({
+      spreadsheetId: SPREADSHEET_ID,
+      range: 'Sheet1!A:Q',
+      valueInputOption: 'USER_ENTERED',
+      insertDataOption: 'INSERT_ROWS',
+      resource: { values }
+    });
+    console.log(`Successfully appended lead ${leadData.name} to Google Sheet.`);
+  } catch (error) {
+    console.error('Error appending lead to Google Sheet:', error);
+  }
+};
+
+module.exports = {
+  syncSheetsToDB,
+  updateLeadInSheet,
+  appendLeadToSheet
+};

services/idGenerator.js

@@ -0,0 +1,45 @@
+const db = require('../db');
+
+/**
+ * Generate the next sequential Student ID (e.g., SKUEDU0701)
+ */
+async function generateNextStudentId() {
+  try {
+    const now = new Date();
+    const currentMonth = String(now.getMonth() + 1).padStart(2, '0');
+    const prefix = `SKUEDU${currentMonth}`;
+    
+    let maxNum = 0;
+    
+    // Fetch all leads that have an ID starting with the current prefix
+    const { data: allLeads, error: maxIdError } = await db
+      .from('leads')
+      .select('student_id')
+      .not('student_id', 'is', null)
+      .like('student_id', `${prefix}%`);
+
+    if (!maxIdError && allLeads && allLeads.length > 0) {
+      allLeads.forEach(lead => {
+        const id = lead.student_id.toUpperCase();
+        if (id.startsWith(prefix)) {
+          const numPart = parseInt(id.replace(prefix, ''), 10);
+          if (!isNaN(numPart) && numPart > maxNum) {
+            maxNum = numPart;
+          }
+        }
+      });
+    }
+    
+    const nextNum = String(maxNum + 1).padStart(2, '0'); // at least 2 digits e.g. 01, 02
+    return `${prefix}${nextNum}`;
+  } catch (err) {
+    console.error('Error generating student ID:', err);
+    // Fallback if DB fails
+    const currentMonth = String(new Date().getMonth() + 1).padStart(2, '0');
+    return `SKUEDU${currentMonth}${String(Math.floor(Math.random() * 90) + 10)}`; 
+  }
+}
+
+module.exports = {
+  generateNextStudentId
+};

services/leadService.js

@@ -0,0 +1,653 @@
+const express = require('express');
+const router = express.Router();
+const { getTenantDb, requireAuth } = require('./auth');
+const eventBus = require('./eventBus');
+const { appendLeadToSheet, updateLeadInSheet } = require('./googleSheets');
+
+// 1. GET /api/leads - Fetch active leads scoped by tenant organization
+router.get('/', requireAuth, async (req, res) => {
+  try {
+    const db = getTenantDb(req);
+    
+    const { data: leads, error } = await db
+      .from('active_leads')
+      .select(`
+        *,
+        student_id,
+        counselors (
+          name
+        ),
+        admissions (
+          course,
+          fees,
+          payment_status
+        )
+      `)
+      .order('created_at', { ascending: false });
+
+    if (error) throw error;
+
+    const formattedLeads = (leads || []).map(lead => {
+      const hasAdmission = lead.admissions && lead.admissions.length > 0;
+      const admissionRecord = hasAdmission ? lead.admissions[0] : null;
+
+      return {
+        id: lead.id,
+        student_name: lead.name,
+        phone_number: lead.phone,
+        email: lead.email || '',
+        interested_course: lead.course_interested,
+        lead_source: lead.source,
+        counselor_name: lead.counselors ? lead.counselors.name : '',
+        counselor_id: lead.counselor_id,
+        followup_status: lead.status || 'Pending',
+        followup_time: lead.followup_time || 'One Day',
+        admission_status: hasAdmission ? 'Admitted' : 'Not Admitted',
+        fees: admissionRecord ? parseFloat(admissionRecord.fees || 0) : 0,
+        lead_score: lead.lead_score || 50,
+        created_date: lead.created_at,
+        gender: lead.gender || '',
+        city: lead.city || '',
+        fees_discussed: lead.fees_discussed ? parseFloat(lead.fees_discussed) : 0,
+        remarks: lead.remarks || '',
+        student_id: lead.student_id || '',
+        referred_by_student_name: lead.referred_by_student_name || ''
+      };
+    });
+
+    res.json(formattedLeads);
+  } catch (error) {
+    console.error('Error fetching leads:', error);
+    res.status(500).json({ error: 'Internal Server Error' });
+  }
+});
+
+router.get('/test', async (req, res) => {
+  try {
+    const db = require('../db');
+    const { data: leads, error } = await db
+      .from('active_leads')
+      .select(`
+        *,
+        student_id
+      `)
+      .order('created_at', { ascending: false });
+
+    if (error) throw error;
+    res.json(leads);
+  } catch (error) {
+    console.error('Error fetching leads test:', error);
+    res.status(500).json({ error: 'Internal Server Error' });
+  }
+});
+
+// 2. POST /api/leads - Create a new lead directly in Supabase
+router.post('/', requireAuth, async (req, res) => {
+  const { 
+    student_name, 
+    phone_number, 
+    email, 
+    interested_course, 
+    lead_source, 
+    counselor_id, 
+    followup_status, 
+    followup_time, 
+    gender, 
+    city, 
+    fees_discussed, 
+    remarks,
+    referred_by_student_name
+  } = req.body;
+
+  if (!student_name || !phone_number || !email) {
+    return res.status(400).json({ error: 'Student name, phone number, and email are required' });
+  }
+
+  try {
+    const db = getTenantDb(req);
+    const orgId = req.user?.organization_id || '00000000-0000-0000-0000-000000000001';
+    const branchId = req.user?.branch_id || '00000000-0000-0000-0000-000000000002';
+
+    console.log('--- LEAD CREATION DEBUG ---');
+    console.log('Incoming Payload:', req.body);
+    console.log('Phone Number Received:', phone_number);
+    console.log('Organization ID Received:', orgId);
+
+    // Auto-generate student ID always
+    const { generateNextStudentId } = require('./idGenerator');
+    const nextId = await generateNextStudentId();
+
+    const newLead = {
+      name: student_name,
+      student_id: nextId,
+      phone: phone_number,
+      email: email,
+      course_interested: interested_course || '',
+      source: lead_source || 'Direct Walk-in',
+      counselor_id: counselor_id || null,
+      status: followup_status || 'Pending',
+      followup_time: followup_time || 'One Day',
+      gender: gender || '',
+      city: city || '',
+      fees_discussed: fees_discussed ? parseFloat(fees_discussed) : null,
+      remarks: remarks || '',
+      referred_by_student_name: lead_source === 'Student Referral' ? referred_by_student_name : null,
+      organization_id: orgId,
+      branch_id: branchId,
+      lead_score: 50
+    };
+
+    const { data: createdData, error } = await db
+      .from('leads')
+      .insert([newLead])
+      .select();
+
+    if (error) throw error;
+    const createdLead = createdData[0];
+
+    // Log in audit logs
+    await db.from('audit_logs').insert([{
+      organization_id: orgId,
+      branch_id: branchId,
+      user_id: req.user?.id || null,
+      action: 'CREATE_LEAD',
+      entity_type: 'leads',
+      entity_id: createdLead.id,
+      new_values: newLead
+    }]);
+
+    // Publish event to decoupled Event Bus
+    eventBus.publish('lead.status_changed', {
+      lead_id: createdLead.id,
+      student_name: createdLead.name,
+      phone: createdLead.phone,
+      old_status: null,
+      new_status: createdLead.status,
+      followup_time: createdLead.followup_time,
+      organization_id: orgId,
+      branch_id: branchId
+    });
+
+    // Fetch counselor name for GSheet
+    let counselorName = '';
+    if (counselor_id) {
+      const { data: cData } = await db.from('counselors').select('name').eq('id', counselor_id);
+      if (cData && cData.length > 0) counselorName = cData[0].name;
+    }
+
+    // Automatically add to Google Sheet
+    appendLeadToSheet({
+      name: newLead.name,
+      phone: newLead.phone,
+      email: newLead.email,
+      course_interested: newLead.course_interested,
+      source: newLead.source,
+      status: newLead.status,
+      counselor_name: counselorName,
+      followup_time: newLead.followup_time,
+      remarks: newLead.remarks,
+      student_id: newLead.student_id
+    }).catch(err => console.error('Failed to append to GSheet:', err));
+
+    res.status(201).json({ message: 'Lead created successfully', lead: createdLead });
+  } catch (error) {
+    console.error('Error creating lead:', error, JSON.stringify(error));
+    return res.status(500).json({
+      success: false,
+      error: error.message || 'Internal Server Error',
+      details: error.details || null
+    });
+  }
+});
+
+// Legacy / compatibility route for update-lead (MUST be registered before dynamic param /:id)
+router.put('/update-lead', requireAuth, async (req, res) => {
+  const { phone_number, followup_status, admission_status, followup_time } = req.body;
+  
+  if (!phone_number) {
+    return res.status(400).json({ error: 'Phone number is required' });
+  }
+
+  try {
+    const db = getTenantDb(req);
+    
+    const { data: leads, error: findError } = await db
+      .from('leads')
+      .select('id, name, course_interested, status, counselor_id, organization_id, branch_id')
+      .eq('phone', phone_number);
+
+    if (findError) throw findError;
+
+    if (!leads || leads.length === 0) {
+      return res.status(404).json({ error: 'Lead not found' });
+    }
+
+    const lead = leads[0];
+
+    // Map legacy update call to our PUT /:id updates
+    const updates = {};
+    if (followup_status) updates.status = followup_status;
+    if (followup_time) updates.followup_time = followup_time;
+
+    if (Object.keys(updates).length > 0) {
+      const { error: updateLeadError } = await db
+        .from('leads')
+        .update(updates)
+        .eq('id', lead.id);
+
+      if (updateLeadError) throw updateLeadError;
+
+      // Log followup details if status changed
+      if (followup_status && followup_status !== lead.status) {
+        await db.from('follow_ups').insert([{
+          lead_id: lead.id,
+          organization_id: lead.organization_id,
+          branch_id: lead.branch_id,
+          followup_date: new Date().toISOString(),
+          followup_type: 'Call',
+          status: followup_status === 'Pending' ? 'Pending' : 'Completed',
+          remarks: `Status updated via legacy API to ${followup_status}`,
+          created_by: lead.counselor_id || req.user?.id || null
+        }]);
+
+        // Publish event to decoupled Event Bus
+        eventBus.publish('lead.status_changed', {
+          lead_id: lead.id,
+          student_name: lead.name,
+          phone: phone_number,
+          old_status: lead.status,
+          new_status: followup_status,
+          followup_time: followup_time || 'One Day',
+          organization_id: lead.organization_id,
+          branch_id: lead.branch_id
+        });
+      }
+    }
+
+    // Update admissions status
+    if (admission_status) {
+      const { data: existingAdmissions, error: admSelectError } = await db
+        .from('admissions')
+        .select('id')
+        .eq('lead_id', lead.id);
+
+      if (admSelectError) throw admSelectError;
+
+      const hasAdmissionRecord = existingAdmissions && existingAdmissions.length > 0;
+
+      if (admission_status === 'Admitted' && !hasAdmissionRecord) {
+        // Trigger Lead Converted Event Bus sequence
+        eventBus.publish('lead.converted', {
+          lead_id: lead.id,
+          student_name: lead.name,
+          phone: phone_number,
+          course: lead.course_interested || 'Default Course',
+          counselor_id: lead.counselor_id,
+          organization_id: lead.organization_id,
+          branch_id: lead.branch_id
+        });
+      } else if (admission_status === 'Not Admitted' && hasAdmissionRecord) {
+        // Remove admission details
+        const { error: deleteAdmError } = await db
+          .from('admissions')
+          .delete()
+          .eq('lead_id', lead.id);
+        if (deleteAdmError) throw deleteAdmError;
+      }
+    }
+
+    res.json({ message: 'Lead updated successfully' });
+  } catch (error) {
+    console.error('Error updating lead:', error);
+    return res.status(500).json({
+      success: false,
+      error: error.message || 'Internal Server Error',
+      details: error.details || null
+    });
+  }
+});
+
+// 3. PUT /api/leads/:id - Update an existing lead record
+router.put('/:id', requireAuth, async (req, res) => {
+  const { id } = req.params;
+  const { 
+    student_id,
+    student_name, 
+    phone_number, 
+    email, 
+    interested_course, 
+    lead_source, 
+    counselor_id, 
+    followup_status, 
+    followup_time, 
+    gender, 
+    city, 
+    fees_discussed, 
+    remarks,
+    referred_by_student_name
+  } = req.body;
+
+  if (email === "") {
+    return res.status(400).json({ error: 'Email is required' });
+  }
+
+  try {
+    const db = getTenantDb(req);
+
+    const { data: currentLeads, error: getError } = await db
+      .from('leads')
+      .select('*')
+      .eq('id', id);
+
+    if (getError) throw getError;
+    if (!currentLeads || currentLeads.length === 0) {
+      return res.status(404).json({ error: 'Lead not found' });
+    }
+
+    const currentLead = currentLeads[0];
+
+    const updates = {};
+    if (student_id !== undefined) updates.student_id = student_id === "" ? null : student_id;
+    if (student_name !== undefined) updates.name = student_name;
+    if (phone_number !== undefined) updates.phone = phone_number;
+    if (email !== undefined) updates.email = email;
+    if (interested_course !== undefined) updates.course_interested = interested_course;
+    if (lead_source !== undefined) updates.source = lead_source;
+    if (counselor_id !== undefined) updates.counselor_id = (counselor_id === "" || counselor_id === "null" || counselor_id === null) ? null : counselor_id;
+    if (followup_status !== undefined) updates.status = followup_status;
+    if (followup_time !== undefined) updates.followup_time = followup_time;
+    if (gender !== undefined) updates.gender = gender;
+    if (city !== undefined) updates.city = city;
+    if (fees_discussed !== undefined) {
+      if (fees_discussed === "" || fees_discussed === null || fees_discussed === "null") {
+        updates.fees_discussed = null;
+      } else {
+        const parsed = parseFloat(fees_discussed);
+        updates.fees_discussed = isNaN(parsed) ? null : parsed;
+      }
+    }
+    if (remarks !== undefined) updates.remarks = remarks;
+    if (referred_by_student_name !== undefined) updates.referred_by_student_name = referred_by_student_name;
+
+    const { error: updateError } = await db
+      .from('leads')
+      .update(updates)
+      .eq('id', id);
+
+    if (updateError) throw updateError;
+
+    // Log audit log
+    await db.from('audit_logs').insert([{
+      organization_id: currentLead.organization_id,
+      branch_id: currentLead.branch_id,
+      user_id: req.user?.id || null,
+      action: 'UPDATE_LEAD',
+      entity_type: 'leads',
+      entity_id: id,
+      old_values: currentLead,
+      new_values: { ...currentLead, ...updates }
+    }]);
+
+    // Check status change or details change
+    if (followup_status && followup_status !== currentLead.status) {
+      // Log follow up history
+      await db.from('follow_ups').insert([{
+        lead_id: id,
+        organization_id: currentLead.organization_id,
+        branch_id: currentLead.branch_id,
+        followup_date: new Date().toISOString(),
+        followup_type: 'Call',
+        status: followup_status === 'Pending' ? 'Pending' : 'Completed',
+        remarks: remarks || `Status updated via CRM edit to ${followup_status}`,
+        created_by: counselor_id || currentLead.counselor_id || req.user?.id || null
+      }]);
+
+      // Publish event
+      eventBus.publish('lead.status_changed', {
+        lead_id: id,
+        student_name: student_name || currentLead.name,
+        phone: phone_number || currentLead.phone,
+        old_status: currentLead.status,
+        new_status: followup_status,
+        followup_time: followup_time || currentLead.followup_time,
+        organization_id: currentLead.organization_id,
+        branch_id: currentLead.branch_id
+      });
+    }
+
+    res.json({ message: 'Lead updated successfully' });
+  } catch (error) {
+    console.error('Error updating lead:', error);
+    return res.status(500).json({
+      success: false,
+      error: error.message || 'Internal Server Error',
+      details: error.details || null
+    });
+  }
+});
+
+// 4. DELETE /api/leads/:id - Soft delete a lead
+router.delete('/:id', requireAuth, async (req, res) => {
+  const { id } = req.params;
+
+  try {
+    const db = getTenantDb(req);
+
+    const { data: currentLeads, error: getError } = await db
+      .from('leads')
+      .select('*')
+      .eq('id', id);
+
+    if (getError) throw getError;
+    if (!currentLeads || currentLeads.length === 0) {
+      return res.status(404).json({ error: 'Lead not found' });
+    }
+
+    const currentLead = currentLeads[0];
+
+    // Soft delete: update deleted_at
+    const { error: deleteError } = await db
+      .from('leads')
+      .update({ deleted_at: new Date().toISOString() })
+      .eq('id', id);
+
+    if (deleteError) throw deleteError;
+
+    // Log audit log
+    await db.from('audit_logs').insert([{
+      organization_id: currentLead.organization_id,
+      branch_id: currentLead.branch_id,
+      user_id: req.user?.id || null,
+      action: 'DELETE_LEAD',
+      entity_type: 'leads',
+      entity_id: id,
+      old_values: currentLead,
+      new_values: { deleted_at: new Date().toISOString() }
+    }]);
+
+    res.json({ message: 'Lead soft-deleted successfully' });
+  } catch (error) {
+    console.error('Error deleting lead:', error);
+    res.status(500).json({ error: 'Internal Server Error' });
+  }
+});
+
+// 5. POST /api/leads/follow-up - Create a new follow-up and update lead status
+router.post('/follow-up', requireAuth, async (req, res) => {
+  const { lead_id, followup_type, status, remarks, next_followup_time } = req.body;
+
+  if (!lead_id) {
+    return res.status(400).json({ error: 'Lead ID is required' });
+  }
+
+  try {
+    const db = getTenantDb(req);
+
+    const { data: leads, error: getError } = await db
+      .from('leads')
+      .select('*')
+      .eq('id', lead_id);
+
+    if (getError) throw getError;
+    if (!leads || leads.length === 0) {
+      return res.status(404).json({ error: 'Lead not found' });
+    }
+
+    const lead = leads[0];
+    const orgId = lead.organization_id;
+    const branchId = lead.branch_id;
+
+    // Insert follow-up record
+    const { data: followUpData, error: followUpError } = await db
+      .from('follow_ups')
+      .insert([{
+        lead_id,
+        organization_id: orgId,
+        branch_id: branchId,
+        followup_date: new Date().toISOString(),
+        followup_type: followup_type || 'Call',
+        status: status || 'Completed',
+        remarks: remarks || '',
+        created_by: req.user?.id || lead.counselor_id || null
+      }])
+      .select();
+
+    if (followUpError) throw followUpError;
+
+    // Update lead notes / followup_time / status
+    const updates = {};
+    if (status && status !== lead.status) {
+      updates.status = status;
+    }
+    if (next_followup_time) {
+      updates.followup_time = next_followup_time;
+    }
+    if (remarks) {
+      updates.remarks = remarks;
+    }
+
+    if (Object.keys(updates).length > 0) {
+      const { error: updateError } = await db
+        .from('leads')
+        .update(updates)
+        .eq('id', lead_id);
+
+      if (updateError) throw updateError;
+    }
+
+    // Log audit log
+    await db.from('audit_logs').insert([{
+      organization_id: orgId,
+      branch_id: branchId,
+      user_id: req.user?.id || null,
+      action: 'ADD_FOLLOW_UP',
+      entity_type: 'follow_ups',
+      entity_id: followUpData[0].id,
+      new_values: { lead_id, status, remarks, next_followup_time }
+    }]);
+
+    // Publish event
+    eventBus.publish('lead.status_changed', {
+      lead_id,
+      student_name: lead.name,
+      phone: lead.phone,
+      old_status: lead.status,
+      new_status: status || lead.status,
+      followup_time: next_followup_time || lead.followup_time,
+      organization_id: orgId,
+      branch_id: branchId
+    });
+
+    res.status(201).json({ message: 'Follow-up logged successfully', followUp: followUpData[0] });
+  } catch (error) {
+    console.error('Error logging follow-up:', error);
+    res.status(500).json({ error: 'Internal Server Error' });
+  }
+});
+
+// 6. GET /api/leads/:id/timeline - Get timeline of events/followups for a lead
+router.get('/:id/timeline', requireAuth, async (req, res) => {
+  try {
+    const db = getTenantDb(req);
+
+    // Fetch follow ups for this lead
+    const { data: followups, error: followupsError } = await db
+      .from('active_follow_ups')
+      .select('*')
+      .eq('lead_id', req.params.id)
+      .order('followup_date', { ascending: false });
+
+    if (followupsError) throw followupsError;
+
+    // Fetch audit logs for this lead
+    const { data: auditLogs, error: auditError } = await db
+      .from('audit_logs')
+      .select('*')
+      .eq('entity_id', req.params.id)
+      .eq('entity_type', 'leads')
+      .order('created_at', { ascending: false });
+
+    if (auditError) throw auditError;
+
+    // Fetch admission details to show if converted
+    const { data: admissions, error: admissionError } = await db
+      .from('active_admissions')
+      .select('*')
+      .eq('lead_id', req.params.id);
+
+    if (admissionError) throw admissionError;
+
+    // Merge and format into timeline events
+    const timeline = [];
+
+    // Add follow ups
+    (followups || []).forEach(f => {
+      timeline.push({
+        id: f.id,
+        type: 'followup',
+        date: f.followup_date,
+        title: `Follow-up (${f.followup_type})`,
+        description: f.remarks,
+        status: f.status
+      });
+    });
+
+    // Add audit logs
+    (auditLogs || []).forEach(a => {
+      let title = 'Lead Modified';
+      if (a.action === 'CREATE_LEAD') title = 'Lead Created';
+      if (a.action === 'DELETE_LEAD') title = 'Lead Deleted';
+      if (a.action === 'UPDATE_LEAD') title = 'Lead Updated';
+      
+      timeline.push({
+        id: a.id,
+        type: 'audit',
+        date: a.created_at,
+        title: title,
+        description: `Action: ${a.action}`
+      });
+    });
+
+    // Add admission if exists
+    if (admissions && admissions.length > 0) {
+      const adm = admissions[0];
+      timeline.push({
+        id: adm.id,
+        type: 'admission',
+        date: adm.joined_date || adm.created_at || new Date().toISOString(),
+        title: 'Lead Converted to Admission 🎓',
+        description: `Enrolled in ${adm.course} | Fees: ₹${parseFloat(adm.fees || 0).toLocaleString()} | Status: ${adm.payment_status}`
+      });
+    }
+
+    // Sort descending by date
+    timeline.sort((a, b) => new Date(b.date) - new Date(a.date));
+
+    res.json(timeline);
+  } catch (error) {
+    console.error('Error fetching lead timeline:', error);
+    res.status(500).json({ error: 'Internal Server Error' });
+  }
+});
+
+module.exports = router;

services/notificationService.js

@@ -0,0 +1,188 @@
+const express = require('express');
+const router = express.Router();
+const { getTenantDb, requireAuth } = require('./auth');
+const eventBus = require('./eventBus');
+const supabase = require('../db');
+
+// Helper to save notifications in background events
+const addNotification = async (notif) => {
+  const orgId = notif.organization_id || '00000000-0000-0000-0000-000000000001';
+  const newNotif = {
+    organization_id: orgId,
+    branch_id: notif.branch_id || '00000000-0000-0000-0000-000000000002',
+    title: notif.title,
+    message: notif.message,
+    type: notif.type || 'SYSTEM_ALERT',
+    priority: notif.priority || 'Low',
+    is_read: false,
+    is_resolved: false,
+    action_url: notif.action_url || ''
+  };
+
+  try {
+    const { data, error } = await supabase
+      .from('notifications')
+      .insert([newNotif])
+      .select();
+    if (error) throw error;
+    console.log(`[NotificationService] Database notification logged: ${notif.title}`);
+    return data[0];
+  } catch (err) {
+    console.error('[NotificationService] Failed to save notification to database:', err);
+    return null;
+  }
+};
+
+/**
+ * Centrally registers and logs notifications in PostgreSQL database
+ */
+const initNotificationService = (io) => {
+  // ===================================================================
+  // 📥 EVENT BUS SUBSCRIBERS (Decoupled Automated System Alerts)
+  // ===================================================================
+
+  // 1. Auto Alert for Manual Enrollments
+  eventBus.subscribe('manual_admission.completed', async (payload) => {
+    const { student_name, student_id, course, pending_amount, installment_option, organization_id } = payload;
+    
+    await addNotification({
+      organization_id,
+      title: "New admission completed 🎓",
+      message: `${student_name} enrolled in ${course} (ID: ${student_id}).`,
+      type: "ADMISSION_ALERT",
+      priority: "Medium",
+      action_url: "/admissions"
+    });
+
+    if (parseFloat(pending_amount || 0) > 0) {
+      await addNotification({
+        organization_id,
+        title: "Installment generated 💰",
+        message: `Pending balance of ₹${parseFloat(pending_amount).toLocaleString()} for ${student_name} (${installment_option || 'EMI'}).`,
+        type: "PAYMENT_ALERT",
+        priority: "High",
+        action_url: "/admissions"
+      });
+    }
+
+    // AI trends trigger alert
+    await addNotification({
+      organization_id,
+      title: `${course} conversion boost 📈`,
+      message: `AI Insight: ${course} enrollments showed a surge after campaign adjustments.`,
+      type: "AI_INSIGHT",
+      priority: "Medium",
+      action_url: "/ai-insights"
+    });
+  });
+
+  // 2. Alert for Payment Completions
+  eventBus.subscribe('payment.completed', async (payload) => {
+    const { student_name, course, organization_id } = payload;
+    await addNotification({
+      organization_id,
+      title: "Fee payment completed ✅",
+      message: `Received final fee payment installment from ${student_name} for course: ${course}.`,
+      type: "PAYMENT_ALERT",
+      priority: "Medium",
+      action_url: "/admissions"
+    });
+  });
+
+  // 3. Alert for Real-time counselor lead updates
+  eventBus.subscribe('lead.status_changed', async (payload) => {
+    const { student_name, new_status, organization_id } = payload;
+    
+    if (new_status === 'Pending') {
+      await addNotification({
+        organization_id,
+        title: "Follow-up Overdue Alert 🔴",
+        message: `Lead ${student_name} followup has expired. Action required.`,
+        type: "SYSTEM_ALERT",
+        priority: "High",
+        action_url: "/leads"
+      });
+    }
+  });
+
+  return { addNotification };
+};
+
+// ===================================================================
+// REST API Routes
+// ===================================================================
+
+// GET /api/notifications - Fetch all active notifications scoped by active tenant
+router.get('/', requireAuth, async (req, res) => {
+  try {
+    const db = getTenantDb(req);
+    const { data, error } = await db
+      .from('active_notifications')
+      .select('*')
+      .order('created_at', { ascending: false });
+
+    if (error) throw error;
+    res.json(data || []);
+  } catch (error) {
+    console.error('Failed to fetch notifications:', error);
+    res.status(500).json({ error: 'Failed to fetch notifications' });
+  }
+});
+
+// PUT /api/notifications/:id/read - Mark alert as read
+router.put('/:id/read', requireAuth, async (req, res) => {
+  const { id } = req.params;
+  try {
+    const db = getTenantDb(req);
+    const { error } = await db
+      .from('notifications')
+      .update({ is_read: true })
+      .eq('id', id);
+
+    if (error) throw error;
+    res.json({ success: true });
+  } catch (error) {
+    console.error('Failed to mark notification read:', error);
+    res.status(500).json({ error: 'Failed to update alert log status' });
+  }
+});
+
+// PUT /api/notifications/:id/resolve - Resolve active notification checklist item
+router.put('/:id/resolve', requireAuth, async (req, res) => {
+  const { id } = req.params;
+  try {
+    const db = getTenantDb(req);
+    const { error } = await db
+      .from('notifications')
+      .update({ is_resolved: true, is_read: true })
+      .eq('id', id);
+
+    if (error) throw error;
+    res.json({ success: true });
+  } catch (error) {
+    console.error('Failed to resolve notification:', error);
+    res.status(500).json({ error: 'Failed to resolve notification logs' });
+  }
+});
+
+// POST /api/notifications/mark-all-read - Clear all unread notifications
+router.post('/mark-all-read', requireAuth, async (req, res) => {
+  try {
+    const db = getTenantDb(req);
+    const { error } = await db
+      .from('notifications')
+      .update({ is_read: true })
+      .eq('is_read', false);
+
+    if (error) throw error;
+    res.json({ success: true });
+  } catch (error) {
+    console.error('Failed to mark all notifications read:', error);
+    res.status(500).json({ error: 'Failed to mark notifications read' });
+  }
+});
+
+module.exports = {
+  router,
+  initNotificationService
+};

services/publicService.js

@@ -0,0 +1,88 @@
+const express = require('express');
+const router = express.Router();
+const dbAdmin = require('../db');
+const eventBus = require('./eventBus');
+const { calculateLeadScore } = require('./aiEngine');
+
+// POST /api/public/leads - Capture public leads from website
+router.post('/leads', async (req, res) => {
+  const { 
+    student_name, 
+    phone_number, 
+    email, 
+    interested_course, 
+    city, 
+    education, 
+    preferred_time 
+  } = req.body;
+
+  if (!student_name || !phone_number) {
+    return res.status(400).json({ error: 'Name and phone number are required' });
+  }
+
+  try {
+    // Determine initial score using the AI Engine
+    const leadScoreParams = {
+      lead_source: 'Website',
+      followup_status: 'Pending',
+      interested_course: interested_course || ''
+    };
+    const lead_score = calculateLeadScore(leadScoreParams);
+
+    const newLead = {
+      name: student_name,
+      phone: phone_number,
+      email: email || '',
+      course_interested: interested_course || '',
+      source: 'Website',
+      status: 'Pending',
+      followup_time: preferred_time || 'One Day',
+      city: city || '',
+      remarks: education ? `Education Background: ${education}` : '',
+      organization_id: '00000000-0000-0000-0000-000000000001',
+      branch_id: '00000000-0000-0000-0000-000000000002',
+      lead_score: lead_score
+    };
+
+    const { data: createdData, error } = await dbAdmin
+      .from('leads')
+      .insert([newLead])
+      .select();
+
+    if (error) throw error;
+    const createdLead = createdData[0];
+
+    // Log in audit logs
+    await dbAdmin.from('audit_logs').insert([{
+      organization_id: createdLead.organization_id,
+      branch_id: createdLead.branch_id,
+      user_id: null,
+      action: 'CREATE_LEAD_PUBLIC',
+      entity_type: 'leads',
+      entity_id: createdLead.id,
+      new_values: newLead
+    }]);
+
+    // Publish event to decoupled Event Bus
+    eventBus.publish('lead.status_changed', {
+      lead_id: createdLead.id,
+      student_name: createdLead.name,
+      phone: createdLead.phone,
+      old_status: null,
+      new_status: createdLead.status,
+      followup_time: createdLead.followup_time,
+      organization_id: createdLead.organization_id,
+      branch_id: createdLead.branch_id
+    });
+
+    res.status(201).json({ message: 'Lead created successfully', lead: createdLead });
+  } catch (error) {
+    console.error('Error creating public lead:', error);
+    if (error && error.code === '23505') {
+      return res.status(409).json({ error: 'You have already submitted an inquiry.' });
+    }
+    res.status(500).json({ error: 'Internal Server Error' });
+  }
+});
+
+module.exports = router;

services/whatsappService.js

@@ -0,0 +1,60 @@
+const express = require('express');
+const { requireAuth, getTenantDb } = require('./auth');
+
+const router = express.Router();
+
+// GET /api/whatsapp/templates
+router.get('/templates', requireAuth, async (req, res) => {
+  try {
+    const db = getTenantDb(req);
+    const { data, error } = await db
+      .from('message_templates')
+      .select('*')
+      .is('deleted_at', null)
+      .order('created_at', { ascending: true });
+
+    if (error) throw error;
+    res.json(data);
+  } catch (error) {
+    console.error('Error fetching templates:', error);
+    res.status(500).json({ error: 'Failed to fetch templates' });
+  }
+});
+
+// POST /api/whatsapp/activity
+router.post('/activity', requireAuth, async (req, res) => {
+  try {
+    const { lead_id, activity_type, description, counselor_id } = req.body;
+    
+    if (!lead_id || !activity_type || !description) {
+      return res.status(400).json({ error: 'Missing required fields' });
+    }
+
+    const db = getTenantDb(req);
+    
+    const { data, error } = await db.from('lead_activities').insert([
+      {
+        lead_id,
+        activity_type,
+        description,
+        created_by: counselor_id || null
+      }
+    ]).select();
+
+    if (error) throw error;
+
+    // We can also trigger an eventbus event here if needed
+    // const { eventBus } = require('./notificationService');
+    // eventBus.emit('lead.activity_logged', { lead_id, activity_type });
+
+    res.json(data[0]);
+  } catch (error) {
+    console.error('Error logging WhatsApp activity:', error);
+    res.status(500).json({ error: 'Failed to log activity' });
+  }
+});
+
+// GET /api/whatsapp/metrics (Optional, can be used if we decouple stats)
+// We will integrate metrics directly in /api/stats instead.
+
+module.exports = router;