import { Router, Request, Response } from 'express';
import { db } from '../database/connection';
import { pantryItems, orders, categories, users, orderItems } from '../database/schema';
import { eq, desc, sql, and, or, lte, gte, count, isNull, isNotNull } from 'drizzle-orm';
import { authenticateUser, requireAdmin, requireAdminOrTeam } from '../middleware/auth';

const router = Router();

// Heads-up: `orders.unit` stores workflow status (pending / ready / picked-up), not a weight unit.
// After scheduling, `orders.priority` holds the pickup instant as an ISO string.

// ====================
// INVENTORY ROUTES
// ====================

// GET /api/pantry/inventory
router.get('/inventory', authenticateUser, async (req: Request, res: Response) => {
  try {
    const items = await db
      .select({
        id: pantryItems.id,
        name: pantryItems.name,
        description: pantryItems.description,
        quantity: pantryItems.quantity,
        unit: pantryItems.unit,
        expiryDate: pantryItems.expiryDate,
        isLowStock: pantryItems.isLowStock,
        imageUrl: pantryItems.imageUrl,
        categoryId: pantryItems.categoryId,
        createdAt: pantryItems.createdAt,
      })
      .from(pantryItems)
      .where(gte(pantryItems.quantity, 1))
      .orderBy(pantryItems.name);

    const cats = await db.select().from(categories);

    return res.json({ items, categories: cats });
  } catch (error) {
    console.error('Error fetching inventory:', error);
    return res.status(500).json({ error: 'Failed to fetch inventory' });
  }
});

// POST /api/pantry/inventory - Add new inventory item (admin or team)
router.post('/inventory', authenticateUser, requireAdminOrTeam, async (req: Request, res: Response) => {
  try {
    const { name, description, quantity, unit, expiryDate, categoryId, lowStockThreshold, imageUrl } = req.body;

    if (!name || quantity === undefined) {
      return res.status(400).json({ error: 'Name and quantity are required' });
    }

    const [newItem] = await db
      .insert(pantryItems)
      .values({
        name,
        description,
        quantity,
        unit,
        expiryDate: expiryDate ? new Date(expiryDate) : null,
        categoryId: categoryId || null,
        lowStockThreshold: lowStockThreshold || 5,
        isLowStock: quantity <= (lowStockThreshold || 5),
        imageUrl,
      })
      .returning();

    return res.status(201).json({ item: newItem });
  } catch (error) {
    console.error('Error creating inventory item:', error);
    return res.status(500).json({ error: 'Failed to create inventory item' });
  }
});

// PUT /api/pantry/inventory/:id - Update inventory item (admin or team)
router.put('/inventory/:id', authenticateUser, requireAdminOrTeam, async (req: Request, res: Response) => {
  try {
    const { id } = req.params;
    const updates = req.body;

    const updateData: any = {
      name: updates.name,
      description: updates.description || null,
      quantity: parseInt(updates.quantity) || 0,
      unit: updates.unit || null,
      expiryDate: updates.expiryDate ? new Date(updates.expiryDate) : null,
      categoryId: updates.categoryId || null,
      lowStockThreshold: parseInt(updates.lowStockThreshold) || 5,
      imageUrl: updates.imageUrl || null,
      updatedAt: new Date(),
    };
    
    updateData.isLowStock = updateData.quantity <= updateData.lowStockThreshold;

    console.log('Updating item with data:', updateData);

    const [updatedItem] = await db
      .update(pantryItems)
      .set(updateData)
      .where(eq(pantryItems.id, id))
      .returning();

    if (!updatedItem) {
      return res.status(404).json({ error: 'Item not found' });
    }

    console.log('✓ Item updated successfully:', updatedItem.id);
    return res.json({ item: updatedItem });
  } catch (error) {
    console.error('Error updating inventory item:', error);
    return res.status(500).json({ error: 'Failed to update inventory item' });
  }
});

// DELETE /api/pantry/inventory/:id - Delete inventory item (admin or team)
router.delete('/inventory/:id', authenticateUser, requireAdminOrTeam, async (req: Request, res: Response) => {
  try {
    const { id } = req.params;

    await db.delete(pantryItems).where(eq(pantryItems.id, id));

    return res.json({ message: 'Item deleted successfully' });
  } catch (error) {
    console.error('Error deleting inventory item:', error);
    return res.status(500).json({ error: 'Failed to delete inventory item' });
  }
});

// ====================
// ORDER ROUTES
// ====================

// GET /api/pantry/orders - Get user's orders or all orders (admin)
router.get('/orders', authenticateUser, async (req: Request, res: Response) => {
  try {
    const isAdmin = req.user!.role === 'admin';
    const userId = req.user!.id;

    const ordersList = await db
      .select()
      .from(orders)
      .where(isAdmin ? undefined : eq(orders.userId, userId))
      .orderBy(desc(orders.createdAt));

    if (isAdmin) {
      const ordersWithUser = await Promise.all(
        ordersList.map(async (order) => {
          const [userInfo] = await db
            .select({
              id: users.id,
              fullName: users.fullName,
              email: users.email,
            })
            .from(users)
            .where(eq(users.id, order.userId));

          return { ...order, user: userInfo };
        })
      );
      return res.json({ orders: ordersWithUser });
    }

    return res.json({ orders: ordersList });
  } catch (error) {
    console.error('Error fetching orders:', error);
    return res.status(500).json({ error: 'Failed to fetch orders' });
  }
});

// Optional cartItems: decrement pantry stock first, then insert one orders row (status pending).
router.post('/orders', authenticateUser, async (req: Request, res: Response) => {
  try {
    console.log('Order creation request:', { body: req.body, user: req.user });
    
    const { name, quantity, unit, priority, notes, cartItems } = req.body;

    if (!name) {
      console.log('Order name is missing');
      return res.status(400).json({ error: 'Order name is required' });
    }

    if (!req.user || !req.user.id) {
      console.log('User authentication failed');
      return res.status(401).json({ error: 'User not authenticated' });
    }

    if (cartItems && Array.isArray(cartItems)) {
      console.log('Processing cart items:', cartItems);
      
      for (const cartItem of cartItems) {
        const item = await db
          .select()
          .from(pantryItems)
          .where(eq(pantryItems.id, cartItem.id))
          .limit(1);

        if (item.length === 0) {
          console.log(`Item not found: ${cartItem.id}`);
          return res.status(400).json({ error: `Item ${cartItem.name} not found` });
        }

        if (item[0].quantity < cartItem.quantity) {
          console.log(`Insufficient quantity for ${cartItem.name}: ${item[0].quantity} < ${cartItem.quantity}`);
          return res.status(400).json({ 
            error: `Insufficient quantity for ${cartItem.name}. Available: ${item[0].quantity}, Requested: ${cartItem.quantity}` 
          });
        }
      }

      for (const cartItem of cartItems) {
        await db
          .update(pantryItems)
          .set({ 
            quantity: sql`${pantryItems.quantity} - ${cartItem.quantity}`,
            updatedAt: new Date()
          })
          .where(eq(pantryItems.id, cartItem.id));
      }
      console.log('Inventory deduction completed');
    }

    console.log('Creating order with data:', {
      userId: req.user.id,
      name,
      quantity: quantity || 1,
      unit: 'pending',
      isPurchased: false,
      priority: priority || 'medium',
      notes
    });

    const [newOrder] = await db
      .insert(orders)
      .values({
        userId: req.user.id,
        name,
        quantity: quantity || 1,
        unit: 'pending',
        isPurchased: false,
        priority: priority || 'medium',
        notes,
      })
      .returning();

    console.log('Order created successfully:', newOrder);
    return res.status(201).json({ order: newOrder });
  } catch (error) {
    console.error('Error creating order:', error);
    return res.status(500).json({ error: 'Failed to create order' });
  }
});

// Saves pickup instant into `priority` as ISO; body still requires pickupLocation for checkout validation.
router.patch('/orders/:id/schedule', authenticateUser, async (req: Request, res: Response) => {
  try {
    const { id } = req.params;
    const { pickupAtISO, pickupLocation } = req.body;

    if (!pickupAtISO || !pickupLocation) {
      return res.status(400).json({ error: 'pickupAtISO and pickupLocation are required' });
    }

    const [updated] = await db
      .update(orders)
      .set({
        priority: pickupAtISO,
        unit: 'pending',
        updatedAt: new Date()
      })
      .where(eq(orders.id, id))
      .returning();

    if (!updated) {
      return res.status(404).json({ error: 'Order not found' });
    }

    return res.json({ order: updated });

  } catch (error) {
    console.error('Error scheduling pickup:', error);
    return res.status(500).json({ error: 'Failed to schedule pickup' });
  }
});

// PATCH /api/pantry/orders/:id/status - Update order status (admin only)
router.patch('/orders/:id/status', authenticateUser, requireAdmin, async (req: Request, res: Response) => {
  try {
    const { id } = req.params;
    const { status } = req.body; // 'pending', 'ready', 'picked-up'

    const [updatedOrder] = await db
      .update(orders)
      .set({ 
        unit: status, // Store status in unit field
        isPurchased: status === 'picked-up', // Mark as completed when picked up
        updatedAt: new Date() 
      })
      .where(eq(orders.id, id))
      .returning();

    if (!updatedOrder) {
      return res.status(404).json({ error: 'Order not found' });
    }

    return res.json({ order: updatedOrder });
  } catch (error) {
    console.error('Error updating order status:', error);
    return res.status(500).json({ error: 'Failed to update order status' });
  }
});

// ====================
// ANALYTICS ROUTES
// ====================

// GET /api/pantry/stats - Get dashboard statistics
router.get('/stats', authenticateUser, async (req: Request, res: Response) => {
  try {
    const isAdminOrTeam = req.user!.role === 'admin' || req.user!.role === 'volunteer';

    if (!isAdminOrTeam) {
      const [userOrders] = await db
        .select({ count: count() })
        .from(orders)
        .where(eq(orders.userId, req.user!.id));

      const [itemsReceivedResult] = await db
        .select({ total: sql<number>`COALESCE(SUM(${orders.quantity}), 0)` })
        .from(orders)
        .where(and(eq(orders.userId, req.user!.id), eq(orders.isPurchased, true)));

      const [lastPickedUpOrder] = await db
        .select({ updatedAt: orders.updatedAt })
        .from(orders)
        .where(and(eq(orders.userId, req.user!.id), eq(orders.isPurchased, true)))
        .orderBy(desc(orders.updatedAt))
        .limit(1);

      const [pendingOrdersResult] = await db
        .select({ count: count() })
        .from(orders)
        .where(and(
          eq(orders.userId, req.user!.id), 
          eq(orders.unit, 'pending'),
          eq(orders.isPurchased, false)
        ));

      return res.json({
        totalOrders: Number(userOrders.count) || 0,
        itemsReceived: Number(itemsReceivedResult.total) || 0,
        lastPickup: lastPickedUpOrder?.updatedAt || null,
        pendingOrders: Number(pendingOrdersResult.count) || 0,
        expiringItems: 0,
      });
    }

    const [totalUsersResult] = await db.select({ count: count() }).from(users);
    const totalUsersCount = totalUsersResult?.count || 0;

    const [totalItemsResult] = await db
      .select({ total: sql<number>`COALESCE(SUM(${pantryItems.quantity}), 0)` })
      .from(pantryItems);
    const totalItemsCount = Number(totalItemsResult?.total) || 0;
    
    const now = new Date();
    const sevenDaysFromNow = new Date(now.getTime() + 7 * 24 * 60 * 60 * 1000);
    
    const [expiringItemsResult] = await db
      .select({ count: count() })
      .from(pantryItems)
      .where(and(
        isNotNull(pantryItems.expiryDate),
        lte(pantryItems.expiryDate, sevenDaysFromNow),
        gte(pantryItems.expiryDate, now)
      ));
    const expiringCount = expiringItemsResult?.count || 0;

    const [lowStockResult] = await db
      .select({ count: count() })
      .from(pantryItems)
      .where(eq(pantryItems.isLowStock, true));
    const lowStockCount = lowStockResult?.count || 0;

    console.log('Stats:', { 
      totalUsers: totalUsersCount, 
      totalItems: totalItemsCount, 
      expiring: expiringCount, 
      lowStock: lowStockCount 
    });

    return res.json({
      totalUsers: totalUsersCount,
      totalItems: totalItemsCount,
      expiringItems: expiringCount,
      lowStockItems: lowStockCount,
    });
  } catch (error) {
    console.error('Error fetching stats:', error);
    return res.status(500).json({ error: 'Failed to fetch statistics' });
  }
});

// GET /api/pantry/activity - Get recent activity (admin or team)
router.get('/activity', authenticateUser, requireAdminOrTeam, async (req: Request, res: Response) => {
  try {
    // Fetch recent orders as activity (all orders)
    const recentOrders = await db
      .select({
        id: orders.id,
        type: sql<string>`CASE 
          WHEN ${orders.unit} = 'picked-up' THEN 'pickup'
          ELSE 'order'
        END`.as('type'),
        description: orders.name,
        createdAt: orders.createdAt,
        userId: orders.userId,
        metadata: sql<any>`json_build_object('quantity', ${orders.quantity}, 'status', ${orders.unit})`,
      })
      .from(orders)
      .orderBy(desc(orders.createdAt))
      .limit(10);

    // Fetch recent inventory updates (items recently updated)
    const recentStockChanges = await db
      .select({
        id: pantryItems.id,
        type: sql<string>`'restock'`.as('type'),
        description: pantryItems.name,
        createdAt: pantryItems.updatedAt,
        userId: sql<string>`NULL`.as('userId'),
        metadata: sql<any>`json_build_object('quantity', ${pantryItems.quantity})`,
      })
      .from(pantryItems)
      .orderBy(desc(pantryItems.updatedAt))
      .limit(5);

    // Combine and sort
    const activities = [...recentOrders, ...recentStockChanges]
      .sort((a, b) => new Date(b.createdAt).getTime() - new Date(a.createdAt).getTime())
      .slice(0, 15);

    // Get user info for each activity
    const activitiesWithUsers = await Promise.all(
      activities.map(async (activity) => {
        if (activity.userId) {
          const [userInfo] = await db
            .select({
              id: users.id,
              fullName: users.fullName,
            })
            .from(users)
            .where(eq(users.id, activity.userId));

          return { ...activity, user: userInfo || { id: activity.userId, fullName: 'Unknown User' } };
        } else {
          // For stock changes
          return { ...activity, user: { id: null, fullName: 'Admin' } };
        }
      })
    );

    return res.json({ activities: activitiesWithUsers });
  } catch (error) {
    console.error('Error fetching activity:', error);
    return res.status(500).json({ error: 'Failed to fetch activity' });
  }
});

// GET /api/pantry/alerts
router.get('/alerts', authenticateUser, requireAdminOrTeam, async (req: Request, res: Response) => {
  try {
    const alerts = [];
    const now = new Date();

    // Check for expiring items (within 7 days)
    const sevenDaysFromNow = new Date(now.getTime() + 7 * 24 * 60 * 60 * 1000);
    const expiringItems = await db
      .select()
      .from(pantryItems)
      .where(and(
        isNotNull(pantryItems.expiryDate),
        lte(pantryItems.expiryDate, sevenDaysFromNow),
        gte(pantryItems.expiryDate, now)
      ));

    // Create individual alerts for each expiring item with details
    for (const item of expiringItems) {
      const daysUntilExpiry = Math.ceil((new Date(item.expiryDate!).getTime() - now.getTime()) / (1000 * 60 * 60 * 24));
      const priority = daysUntilExpiry <= 2 ? 'critical' : daysUntilExpiry <= 4 ? 'high' : 'medium';
      
      // Get category name if exists
      let categoryName = 'Uncategorized';
      if (item.categoryId) {
        const [category] = await db
          .select()
          .from(categories)
          .where(eq(categories.id, item.categoryId))
          .limit(1);
        if (category) {
          categoryName = category.name;
        }
      }

      alerts.push({
        id: `expiring-${item.id}`,
        type: 'expiring',
        priority,
        title: `${item.name} Expiring Soon`,
        message: `${item.name} (${categoryName}) expires in ${daysUntilExpiry} day${daysUntilExpiry !== 1 ? 's' : ''}. Current stock: ${item.quantity} ${item.unit || 'units'}`,
        itemId: item.id,
        itemName: item.name,
        category: categoryName,
        quantity: item.quantity,
        unit: item.unit,
        expiryDate: item.expiryDate,
        daysUntilExpiry,
        isResolved: false,
        createdAt: now,
      });
    }

    // Check for low stock items
    const lowStockItems = await db
      .select()
      .from(pantryItems)
      .where(eq(pantryItems.isLowStock, true));

    // Create individual alerts for each low stock item
    for (const item of lowStockItems) {
      // Get category name if exists
      let categoryName = 'Uncategorized';
      if (item.categoryId) {
        const [category] = await db
          .select()
          .from(categories)
          .where(eq(categories.id, item.categoryId))
          .limit(1);
        if (category) {
          categoryName = category.name;
        }
      }

      alerts.push({
        id: `low-stock-${item.id}`,
        type: 'low-stock',
        priority: 'medium',
        title: `${item.name} Low Stock`,
        message: `${item.name} (${categoryName}) is running low. Current stock: ${item.quantity} ${item.unit || 'units'} (threshold: ${item.lowStockThreshold})`,
        itemId: item.id,
        itemName: item.name,
        category: categoryName,
        quantity: item.quantity,
        unit: item.unit,
        lowStockThreshold: item.lowStockThreshold,
        isResolved: false,
        createdAt: now,
      });
    }

    // Check for expired items
    const expiredItems = await db
      .select()
      .from(pantryItems)
      .where(and(
        isNotNull(pantryItems.expiryDate),
        lte(pantryItems.expiryDate, now)
      ));

    // Create individual alerts for each expired item
    for (const item of expiredItems) {
      const daysExpired = Math.ceil((now.getTime() - new Date(item.expiryDate!).getTime()) / (1000 * 60 * 60 * 24));
      
      // Get category name if exists
      let categoryName = 'Uncategorized';
      if (item.categoryId) {
        const [category] = await db
          .select()
          .from(categories)
          .where(eq(categories.id, item.categoryId))
          .limit(1);
        if (category) {
          categoryName = category.name;
        }
      }

      alerts.push({
        id: `expired-${item.id}`,
        type: 'expired',
        priority: 'critical',
        title: `${item.name} Expired`,
        message: `${item.name} (${categoryName}) expired ${daysExpired} day${daysExpired !== 1 ? 's' : ''} ago. Current stock: ${item.quantity} ${item.unit || 'units'}`,
        itemId: item.id,
        itemName: item.name,
        category: categoryName,
        quantity: item.quantity,
        unit: item.unit,
        expiryDate: item.expiryDate,
        daysExpired,
        isResolved: false,
        createdAt: now,
      });
    }

    // Sort alerts by priority (critical > high > medium) and then by creation date
    const priorityOrder = { critical: 1, high: 2, medium: 3 };
    alerts.sort((a, b) => {
      const priorityDiff = priorityOrder[a.priority as keyof typeof priorityOrder] - priorityOrder[b.priority as keyof typeof priorityOrder];
      if (priorityDiff !== 0) return priorityDiff;
      return new Date(b.createdAt).getTime() - new Date(a.createdAt).getTime();
    });

    return res.json({ alerts });
  } catch (error) {
    console.error('Error fetching alerts:', error);
    return res.status(500).json({ error: 'Failed to fetch alerts' });
  }
});

// ====================
// CATEGORY ROUTES
// ====================

// GET /api/pantry/categories - Get all categories
router.get('/categories', authenticateUser, async (req: Request, res: Response) => {
  try {
    const cats = await db.select().from(categories).orderBy(categories.name);
    return res.json({ categories: cats });
  } catch (error) {
    console.error('Error fetching categories:', error);
    return res.status(500).json({ error: 'Failed to fetch categories' });
  }
});

// POST /api/pantry/categories - Create category (admin or team)
router.post('/categories', authenticateUser, requireAdminOrTeam, async (req: Request, res: Response) => {
  try {
    const { name, description, color } = req.body;

    if (!name) {
      return res.status(400).json({ error: 'Category name is required' });
    }

    const [newCategory] = await db
      .insert(categories)
      .values({ name, description, color })
      .returning();

    return res.status(201).json({ category: newCategory });
  } catch (error) {
    console.error('Error creating category:', error);
    return res.status(500).json({ error: 'Failed to create category' });
  }
});

// GET /api/pantry/analytics (orders.unit = pickup status)
router.get('/analytics', authenticateUser, requireAdminOrTeam, async (req: Request, res: Response) => {
  const PICKED_UP = 'picked-up';
  try {
    let totalVisits = 0;
    try {
      const [result] = await db
        .select({ count: count() })
        .from(orders)
        .where(eq(orders.unit, PICKED_UP));
      totalVisits = Number(result?.count || 0);
    } catch (err) {
      console.error('Error fetching total visits:', err);
    }

    let itemsDistributed = 0;
    try {
      const [result] = await db
        .select({ total: sql<number>`COALESCE(SUM(${orders.quantity}), 0)` })
        .from(orders)
        .where(eq(orders.unit, PICKED_UP));
      itemsDistributed = Number(result?.total || 0);
    } catch (err) {
      console.error('Error fetching items distributed:', err);
    }

    let wasteReduction = 100;
    try {
      const [withExpiry] = await db
        .select({ count: count() })
        .from(pantryItems)
        .where(isNotNull(pantryItems.expiryDate));
      const totalWithExpiry = Number(withExpiry?.count || 0);
      if (totalWithExpiry > 0) {
        const [expired] = await db
          .select({ count: count() })
          .from(pantryItems)
          .where(and(isNotNull(pantryItems.expiryDate), lte(pantryItems.expiryDate, new Date())));
        const expiredCount = Number(expired?.count || 0);
        wasteReduction = Math.round(((totalWithExpiry - expiredCount) / totalWithExpiry) * 100);
      }
    } catch (err) {
      console.error('Error calculating waste reduction:', err);
    }

    let categoryDistribution: any[] = [];
    try {
      categoryDistribution = await db
        .select({
          categoryId: pantryItems.categoryId,
          categoryName: categories.name,
          count: count(),
          totalQuantity: sql<number>`SUM(${pantryItems.quantity})`
        })
        .from(pantryItems)
        .leftJoin(categories, eq(pantryItems.categoryId, categories.id))
        .groupBy(pantryItems.categoryId, categories.name)
        .orderBy(sql`SUM(${pantryItems.quantity}) DESC`);
    } catch (err) {
      console.error('Error fetching category distribution:', err);
    }

    let monthlyPickups: any[] = [];
    try {
      const sixMonthsAgo = new Date();
      sixMonthsAgo.setMonth(sixMonthsAgo.getMonth() - 6);
      sixMonthsAgo.setDate(1);
      sixMonthsAgo.setHours(0, 0, 0, 0);

      monthlyPickups = await db
        .select({
          month: sql<string>`TO_CHAR(${orders.updatedAt}, 'Mon')`,
          year: sql<number>`EXTRACT(YEAR FROM ${orders.updatedAt})::INTEGER`,
          monthNum: sql<number>`EXTRACT(MONTH FROM ${orders.updatedAt})::INTEGER`,
          count: count()
        })
        .from(orders)
        .where(and(eq(orders.unit, PICKED_UP), gte(orders.updatedAt, sixMonthsAgo)))
        .groupBy(
          sql`TO_CHAR(${orders.updatedAt}, 'Mon')`,
          sql`EXTRACT(YEAR FROM ${orders.updatedAt})`,
          sql`EXTRACT(MONTH FROM ${orders.updatedAt})`
        )
        .orderBy(sql`EXTRACT(YEAR FROM ${orders.updatedAt})`, sql`EXTRACT(MONTH FROM ${orders.updatedAt})`);
    } catch (err) {
      console.error('Error fetching monthly trends:', err);
    }

    // pad chart to 6 months
    const monthNames = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'];
    const filledMonthly: { month: string; year: number; count: number }[] = [];
    for (let i = 5; i >= 0; i--) {
      const d = new Date();
      d.setMonth(d.getMonth() - i);
      d.setDate(1);
      d.setHours(0, 0, 0, 0);
      const monthLabel = monthNames[d.getMonth()];
      const yearNum = d.getFullYear();
      const found = monthlyPickups.find((m: any) => Number(m.year) === yearNum && m.month?.trim() === monthLabel);
      filledMonthly.push({
        month: monthLabel,
        year: yearNum,
        count: found ? Number(found.count) : 0
      });
    }

    let visitsChange = 0;
    let itemsChange = 0;
    try {
      const now = new Date();
      const thisMonthStart = new Date(now.getFullYear(), now.getMonth(), 1);
      const lastMonthStart = new Date(now.getFullYear(), now.getMonth() - 1, 1);

      const [thisMonthVisits] = await db
        .select({ count: count(), total: sql<number>`COALESCE(SUM(${orders.quantity}), 0)` })
        .from(orders)
        .where(and(eq(orders.unit, PICKED_UP), gte(orders.updatedAt, thisMonthStart)));
      const [lastMonthVisits] = await db
        .select({ count: count(), total: sql<number>`COALESCE(SUM(${orders.quantity}), 0)` })
        .from(orders)
        .where(and(eq(orders.unit, PICKED_UP), gte(orders.updatedAt, lastMonthStart), lte(orders.updatedAt, thisMonthStart)));

      const thisMonthCount = Number(thisMonthVisits?.count || 0);
      const lastMonthCount = Number(lastMonthVisits?.count || 0);
      visitsChange = lastMonthCount > 0
        ? Math.round(((thisMonthCount - lastMonthCount) / lastMonthCount) * 100)
        : (thisMonthCount > 0 ? 100 : 0);

      const thisMonthItems = Number(thisMonthVisits?.total || 0);
      const lastMonthItems = Number(lastMonthVisits?.total || 0);
      itemsChange = lastMonthItems > 0
        ? Math.round(((thisMonthItems - lastMonthItems) / lastMonthItems) * 100)
        : (thisMonthItems > 0 ? 100 : 0);
    } catch (err) {
      console.error('Error calculating trend changes:', err);
    }

    // 7. Waste trend: compare expired count now vs approximate "last month" (simplified: no historical snapshot)
    let wasteChange = 0;

    return res.json({
      totalVisits,
      itemsDistributed,
      wasteReduction,
      categoryDistribution: categoryDistribution.map(cat => ({
        name: cat.categoryName || 'Uncategorized',
        count: cat.count,
        quantity: Number(cat.totalQuantity || 0)
      })),
      monthlyTrends: filledMonthly,
      trends: {
        visitsChange,
        itemsChange,
        wasteChange
      }
    });
  } catch (error) {
    console.error('Error fetching analytics:', error);
    return res.status(500).json({ error: 'Failed to fetch analytics' });
  }
});

// GET /api/pantry/pickups - Get upcoming pickups (admin/team only)
router.get('/pickups', authenticateUser, requireAdminOrTeam, async (req: Request, res: Response) => {
  try {
    // Get all orders with status 'pending' or 'ready' (upcoming pickups)
    const upcomingPickups = await db
      .select({
        id: orders.id,
        name: orders.name,
        quantity: orders.quantity,
        status: orders.unit, // 'pending', 'ready', 'picked-up'
        pickupTime: orders.priority, // stores pickup date/time
        notes: orders.notes,
        createdAt: orders.createdAt,
        updatedAt: orders.updatedAt,
        userId: orders.userId,
        userEmail: users.email,
        userFullName: users.fullName
      })
      .from(orders)
      .leftJoin(users, eq(orders.userId, users.id))
      .where(
        and(
          or(eq(orders.unit, 'pending'), eq(orders.unit, 'ready')),
          eq(orders.isPurchased, false) // not completed yet
        )
      )
      .orderBy(sql`${orders.priority} ASC`, orders.createdAt);

    return res.json({ pickups: upcomingPickups });
  } catch (error) {
    console.error('Error fetching pickups:', error);
    return res.status(500).json({ error: 'Failed to fetch pickups' });
  }
});


export { router as pantryRoutes };
