import type { Express } from "express";
import { createServer, type Server } from "http";
import session from "express-session";
import { storage } from "./storage";
import { insertClientSchema, insertSimswapSchema, insertUserSchema, insertStaffNotificationSchema } from "@shared/schema";
import { authenticateUser, createFirstAdminUser, hashPassword } from "./auth";
import { createUsersTable } from "./create-users-table";
import { createCategoriesTable } from "./create-categories-table";
import { createMtnStatisticsTable } from "./create-mtn-statistics-table";
import { createMtnMobileTop15Table } from "./create-mtn-mobile-top15-table";
import { createStaffNotificationsTable } from "./create-staff-notifications-table";
import { createMtnMobileDailyUsageTable } from "./create-mtn-mobile-daily-usage-table";
import { createMtnMobileSyncProgressTable } from "./create-mtn-mobile-sync-progress-table";
import { createApiKeysTable } from "./create-api-keys-table";
import { createActivityLogsTable } from "./create-activity-logs-table";
import { requireApiKey, generateApiKey, type ApiAuthRequest } from "./api-auth";
import { ActivityLogger } from "./activity-logger";
import OpenWebAuth from "./openweb-auth";
import { z } from "zod";

// Session configuration
declare module 'express-session' {
  interface SessionData {
    userId?: number;
    user?: {
      id: number;
      username: string;
      name: string;
      role: string;
    };
  }
}

// Middleware to check authentication
function requireAuth(req: any, res: any, next: any) {
  if (!req.session?.userId) {
    return res.status(401).json({ error: "Authentication required" });
  }
  next();
}

export async function registerRoutes(app: Express): Promise<Server> {
  // Configure session middleware
  app.use(session({
    secret: 'masterfile-secret-key-2024',
    resave: false,
    saveUninitialized: false,
    cookie: {
      secure: false, // Set to true in production with HTTPS
      httpOnly: true,
      maxAge: 24 * 60 * 60 * 1000 // 24 hours
    }
  }));

  // Create users table and initialize first admin user
  await createUsersTable();
  await createFirstAdminUser();
  
  // Create categories table and seed initial data
  await createCategoriesTable();
  
  // Create MTN statistics table for admin tools
  await createMtnStatisticsTable();
  
  // Create MTN Mobile Top 15 table for usage rankings
  await createMtnMobileTop15Table();
  
  // Create staff notifications table
  await createStaffNotificationsTable();
  
  // Create MTN Mobile daily usage tables
  await createMtnMobileDailyUsageTable();
  await createMtnMobileSyncProgressTable();
  
  // Create API keys table
  await createApiKeysTable();
  
  // Create activity logs table
  await createActivityLogsTable();

  // Authentication routes
  app.post("/api/auth/login", async (req, res) => {
    try {
      const { username, password } = req.body;
      
      if (!username || !password) {
        return res.status(400).json({ error: "Username and password are required" });
      }

      const user = await authenticateUser(username, password);
      if (!user) {
        return res.status(401).json({ error: "Invalid credentials" });
      }

      // Set session
      req.session.userId = user.id;
      req.session.user = {
        id: user.id,
        username: user.username,
        name: user.name,
        role: user.role
      };

      // Log successful login
      await ActivityLogger.login(user.id, req);

      res.json({
        id: user.id,
        username: user.username,
        name: user.name,
        role: user.role
      });
    } catch (error) {
      console.error("Login error:", error);
      res.status(500).json({ error: "Internal server error" });
    }
  });

  app.post("/api/auth/logout", async (req, res) => {
    const userId = req.session.userId;
    
    req.session.destroy(async (err) => {
      if (err) {
        return res.status(500).json({ error: "Could not log out" });
      }
      
      // Log successful logout
      if (userId) {
        await ActivityLogger.logout(userId, req);
      }
      
      res.json({ message: "Logged out successfully" });
    });
  });

  app.get("/api/auth/me", requireAuth, async (req, res) => {
    try {
      const user = await storage.getUser(req.session.userId!);
      if (!user) {
        return res.status(404).json({ error: "User not found" });
      }
      
      res.json({
        id: user.id,
        username: user.username,
        name: user.name,
        role: user.role
      });
    } catch (error) {
      console.error("Get user error:", error);
      res.status(500).json({ error: "Internal server error" });
    }
  });

  // OpenWeb API test route (for debugging authentication)
  app.get("/api/openweb/test", requireAuth, async (req, res) => {
    try {
      const openWebAuth = OpenWebAuth.getInstance();
      
      // Test authentication and get token status
      const isAuthenticated = await openWebAuth.testAuthentication();
      const tokenStatus = openWebAuth.getTokenStatus();
      
      if (!isAuthenticated) {
        return res.status(500).json({ 
          error: "OpenWeb API authentication failed",
          tokenStatus
        });
      }

      // Test a simple API call
      const testResponse = await openWebAuth.makeAuthenticatedRequest(
        'http://api.openweb.live:3000/proxy/usernameInfo?usernames=test@mobile.is.co.za',
        { method: 'GET' }
      );

      res.json({
        authenticated: isAuthenticated,
        tokenStatus,
        testCall: {
          status: testResponse.status,
          statusText: testResponse.statusText,
          ok: testResponse.ok
        }
      });
      
    } catch (error) {
      console.error("OpenWeb API test error:", error);
      res.status(500).json({ 
        error: "OpenWeb API test failed",
        message: error instanceof Error ? error.message : 'Unknown error'
      });
    }
  });

  // Usage stats proxy route for MTN Fixed clients
  app.get("/api/usage-stats/:username", requireAuth, async (req, res) => {
    try {
      const { username } = req.params;
      const { year, month } = req.query as { year?: string; month?: string };
      
      if (!username || !year || !month) {
        return res.status(400).json({ error: "Missing required parameters: username, year, month" });
      }

      // Clean and format username
      const cleanedUsername = username.toString().trim();

      const apiUrl = `http://api.openweb.live:3000/proxy/monthUsage?usernames=${encodeURIComponent(cleanedUsername)}&year=${year}&month=${month}`;
      
      const openWebAuth = OpenWebAuth.getInstance();
      const response = await openWebAuth.makeAuthenticatedRequest(apiUrl, {
        method: 'GET',
      });

      if (!response.ok) {
        return res.status(response.status).json({ error: `API request failed: ${response.statusText}` });
      }

      const data = await response.json();
      res.json(data);
    } catch (error) {
      console.error("Error fetching usage stats:", error);
      res.status(500).json({ error: "Failed to fetch usage statistics" });
    }
  });

  // Account info proxy route for MTN Fixed clients
  app.get("/api/account-info/:username", requireAuth, async (req, res) => {
    try {
      const { username } = req.params;
      
      if (!username) {
        return res.status(400).json({ error: "Missing required parameter: username" });
      }

      // Clean and format username
      const cleanedUsername = username.toString().trim();

      const apiUrl = `http://api.openweb.live:3000/proxy/usernameInfo?usernames=${encodeURIComponent(cleanedUsername)}`;
      
      const openWebAuth = OpenWebAuth.getInstance();
      const response = await openWebAuth.makeAuthenticatedRequest(apiUrl, {
        method: 'GET',
      });

      if (!response.ok) {
        return res.status(response.status).json({ error: `API request failed: ${response.statusText}` });
      }

      const data = await response.json();
      res.json(data);
    } catch (error) {
      console.error("Error fetching account info:", error);
      res.status(500).json({ error: "Failed to fetch account information" });
    }
  });

  // Current sessions proxy route for MTN Fixed clients
  app.get("/api/current-sessions/:username", requireAuth, async (req, res) => {
    try {
      const { username } = req.params;
      
      if (!username) {
        return res.status(400).json({ error: "Missing required parameter: username" });
      }

      // Clean and format username
      const cleanedUsername = username.toString().trim();

      const apiUrl = `http://api.openweb.live:3000/proxy/currentSessions?usernames=${encodeURIComponent(cleanedUsername)}`;
      
      const openWebAuth = OpenWebAuth.getInstance();
      const response = await openWebAuth.makeAuthenticatedRequest(apiUrl, {
        method: 'GET',
      });

      if (!response.ok) {
        return res.status(response.status).json({ error: `API request failed: ${response.statusText}` });
      }

      const data = await response.json();
      res.json(data);
    } catch (error) {
      console.error("Error fetching current sessions:", error);
      res.status(500).json({ error: "Failed to fetch current sessions" });
    }
  });

  // MTN Mobile (GSM) 3-month usage history proxy route
  app.get("/api/mtn-mobile-usage/:username", requireAuth, async (req, res) => {
    try {
      const { username } = req.params;
      
      if (!username) {
        return res.status(400).json({ error: "Missing required parameter: username" });
      }

      // Clean and format username
      let cleanedUsername = username.toString().trim();
      
      // Auto-format MTN Mobile accounts by appending @mobile.is.co.za if missing
      if (!cleanedUsername.includes('@')) {
        cleanedUsername = `${cleanedUsername}@mobile.is.co.za`;
      }

      const currentDate = new Date();
      const usageHistory = [];

      // Get current month and 2 months before
      for (let i = 0; i < 3; i++) {
        const targetDate = new Date(currentDate.getFullYear(), currentDate.getMonth() - i, 1);
        const year = targetDate.getFullYear();
        const month = targetDate.getMonth() + 1;

        try {
          const apiUrl = `http://api.openweb.live:3000/proxy/monthUsage?usernames=${encodeURIComponent(cleanedUsername)}&year=${year}&month=${month}`;
          
          const openWebAuth = OpenWebAuth.getInstance();
          const response = await openWebAuth.makeAuthenticatedRequest(apiUrl, {
            method: 'GET',
          });

          if (response.ok) {
            const data = await response.json();
            usageHistory.push({
              year,
              month,
              monthName: targetDate.toLocaleString('default', { month: 'long' }),
              data: data.data || [],
              success: true
            });
          } else {
            usageHistory.push({
              year,
              month,
              monthName: targetDate.toLocaleString('default', { month: 'long' }),
              data: [],
              success: false,
              error: `API request failed: ${response.statusText}`
            });
          }
        } catch (error) {
          usageHistory.push({
            year,
            month,
            monthName: targetDate.toLocaleString('default', { month: 'long' }),
            data: [],
            success: false,
            error: error.message
          });
        }
      }

      res.json({ ok: true, data: usageHistory });
    } catch (error) {
      console.error("Error fetching MTN Mobile usage history:", error);
      res.status(500).json({ error: "Failed to fetch MTN Mobile usage history" });
    }
  });

  // MTN Mobile (GSM) current sessions proxy route
  app.get("/api/mtn-mobile-sessions/:username", requireAuth, async (req, res) => {
    try {
      const { username } = req.params;
      
      if (!username) {
        return res.status(400).json({ error: "Missing required parameter: username" });
      }

      // Clean and format username
      let cleanedUsername = username.toString().trim();
      
      // Auto-format MTN Mobile accounts by appending @mobile.is.co.za if missing
      if (!cleanedUsername.includes('@')) {
        cleanedUsername = `${cleanedUsername}@mobile.is.co.za`;
      }

      const apiUrl = `http://api.openweb.live:3000/proxy/currentSessions?usernames=${encodeURIComponent(cleanedUsername)}`;
      
      const openWebAuth = OpenWebAuth.getInstance();
      const response = await openWebAuth.makeAuthenticatedRequest(apiUrl, {
        method: 'GET',
      });

      if (!response.ok) {
        return res.status(response.status).json({ error: `API request failed: ${response.statusText}` });
      }

      const data = await response.json();
      res.json(data);
    } catch (error) {
      console.error("Error fetching MTN Mobile sessions:", error);
      res.status(500).json({ error: "Failed to fetch MTN Mobile sessions" });
    }
  });

  // MTN Mobile (GSM) daily usage proxy route
  app.get("/api/mtn-mobile-daily-usage/:username", requireAuth, async (req, res) => {
    const { username } = req.params;
    const logPrefix = `[MTN Mobile Daily Usage ${new Date().toISOString()}]`;
    
    try {
      console.log(`${logPrefix} Starting daily usage fetch for username: ${username}`);
      
      if (!username) {
        console.error(`${logPrefix} Error: Missing required parameter: username`);
        return res.status(400).json({ error: "Missing required parameter: username" });
      }

      // Get current date details
      const currentDate = new Date();
      const year = currentDate.getFullYear();
      const month = currentDate.getMonth() + 1; // JavaScript months are 0-indexed
      const currentDay = currentDate.getDate();
      
      console.log(`${logPrefix} Current date info - Year: ${year}, Month: ${month}, Day: ${currentDay}`);
      
      // Clean account number by removing potential trailing spaces
      const cleanUsername = username.trim();
      console.log(`${logPrefix} Cleaned username: "${cleanUsername}"`);
      
      const dailyUsageData = [];
      let totalUsageBytes = 0;
      let errorCount = 0;
      
      console.log(`${logPrefix} Fetching daily usage from day 1 to day ${currentDay}`);
      
      // Fetch usage for each day from 1st to current day
      for (let day = 1; day <= currentDay; day++) {
        const dayLogPrefix = `${logPrefix} [Day ${day}]`;
        
        try {
          // Format day with leading zero if needed
          const formattedDay = day.toString().padStart(2, '0');
          const formattedMonth = month.toString().padStart(2, '0');
          
          // Construct API URL - username already includes @mobile.is.co.za
          const apiUrl = `http://api.openweb.live:3000/proxy/dayUsage?usernames=${encodeURIComponent(cleanUsername)}&year=${year}&month=${formattedMonth}&day=${formattedDay}`;
          
          console.log(`${dayLogPrefix} API URL: ${apiUrl}`);
          
          const openWebAuth = OpenWebAuth.getInstance();
          const response = await openWebAuth.makeAuthenticatedRequest(apiUrl, {
            method: 'GET',
          });

          console.log(`${dayLogPrefix} API response status: ${response.status}`);

          if (response.ok) {
            const responseText = await response.text();
            console.log(`${dayLogPrefix} Raw API response:`, responseText);
            
            let apiResponse;
            try {
              apiResponse = JSON.parse(responseText);
              console.log(`${dayLogPrefix} Parsed API response:`, JSON.stringify(apiResponse, null, 2));
            } catch (parseError) {
              console.error(`${dayLogPrefix} Failed to parse API response as JSON:`, parseError);
              console.error(`${dayLogPrefix} Raw response was:`, responseText);
              errorCount++;
              dailyUsageData.push({
                day: day,
                date: `${year}-${formattedMonth}-${formattedDay}`,
                usageBytes: 0,
                usageGB: "0.000",
                entries: 0,
                success: false,
                error: `JSON parse error: ${parseError.message}`
              });
              continue;
            }
            
            if (apiResponse.ok && apiResponse.data && Array.isArray(apiResponse.data)) {
              let dayTotalBytes = 0;
              
              // Sum all charged_quantity values for the day
              apiResponse.data.forEach((entry, index) => {
                const chargedQuantity = parseInt(entry.charged_quantity) || 0;
                dayTotalBytes += chargedQuantity;
                console.log(`${dayLogPrefix} Entry ${index + 1}: UserName="${entry.UserName}", MSISDSN="${entry.MSISDSN}", charged_quantity=${chargedQuantity} bytes`);
              });
              
              totalUsageBytes += dayTotalBytes;
              
              dailyUsageData.push({
                day: day,
                date: `${year}-${formattedMonth}-${formattedDay}`,
                usageBytes: dayTotalBytes,
                usageGB: (dayTotalBytes / (1024 * 1024 * 1024)).toFixed(3),
                entries: apiResponse.data.length,
                success: true,
                error: null
              });
              
              console.log(`${dayLogPrefix} SUCCESS: Day total: ${dayTotalBytes} bytes (${(dayTotalBytes / (1024 * 1024 * 1024)).toFixed(3)} GB) from ${apiResponse.data.length} entries`);
            } else {
              console.log(`${dayLogPrefix} No data in response or response not ok. API Response:`, apiResponse);
              dailyUsageData.push({
                day: day,
                date: `${year}-${formattedMonth}-${formattedDay}`,
                usageBytes: 0,
                usageGB: "0.000",
                entries: 0,
                success: true,
                error: null
              });
            }
          } else {
            // Get detailed error information
            let errorResponseText = '';
            try {
              errorResponseText = await response.text();
              console.error(`${dayLogPrefix} API ERROR response body:`, errorResponseText);
            } catch (textError) {
              console.error(`${dayLogPrefix} Failed to read error response body:`, textError);
            }
            
            console.error(`${dayLogPrefix} API request failed - Status: ${response.status}, StatusText: ${response.statusText}`);
            console.error(`${dayLogPrefix} Request URL was: ${apiUrl}`);
            console.error(`${dayLogPrefix} Response headers:`, Object.fromEntries(response.headers.entries()));
            
            errorCount++;
            dailyUsageData.push({
              day: day,
              date: `${year}-${formattedMonth}-${formattedDay}`,
              usageBytes: 0,
              usageGB: "0.000",
              entries: 0,
              success: false,
              error: `API request failed: ${response.statusText}`
            });
          }
        } catch (error) {
          console.error(`${dayLogPrefix} Error fetching day usage:`, error);
          errorCount++;
          dailyUsageData.push({
            day: day,
            date: `${year}-${formattedMonth}-${formattedDay}`,
            usageBytes: 0,
            usageGB: "0.000",
            entries: 0,
            success: false,
            error: error.message
          });
        }
        
        // Add small delay to avoid overwhelming the API
        if (day < currentDay) {
          await new Promise(resolve => setTimeout(resolve, 100));
        }
      }
      
      // Calculate summary
      const totalUsageGB = (totalUsageBytes / (1024 * 1024 * 1024)).toFixed(3);
      const todayUsage = dailyUsageData.find(d => d.day === currentDay);
      
      console.log(`${logPrefix} Summary - Total usage: ${totalUsageBytes} bytes (${totalUsageGB} GB), Today's usage: ${todayUsage?.usageGB || '0.000'} GB, Errors: ${errorCount}`);
      
      res.json({
        ok: true,
        data: {
          username: cleanUsername,
          year,
          month,
          currentDay,
          dailyUsage: dailyUsageData,
          summary: {
            totalUsageBytes,
            totalUsageGB,
            todayUsageBytes: todayUsage?.usageBytes || 0,
            todayUsageGB: todayUsage?.usageGB || "0.000",
            daysWithData: dailyUsageData.filter(d => d.success && d.usageBytes > 0).length,
            totalDays: currentDay,
            errorCount
          }
        }
      });
      
    } catch (error) {
      console.error(`${logPrefix} Error fetching MTN Mobile daily usage:`, error);
      res.status(500).json({ error: "Failed to fetch MTN Mobile daily usage" });
    }
  });

  // MTN Mobile (GSM) Daily Usage from Database endpoint
  app.get("/api/mtn-mobile-daily-usage-db/:username", requireAuth, async (req, res) => {
    try {
      const { username } = req.params;
      const currentDate = new Date();
      const year = currentDate.getFullYear();
      const month = currentDate.getMonth() + 1;
      const currentDay = currentDate.getDate();
      
      // Get stored daily usage data from database
      const dailyUsageRecords = await storage.getMtnMobileDailyUsage(username.trim(), year, month);
      
      let totalUsageBytes = 0;
      let errorCount = 0;
      const dailyUsageData = [];
      
      // Build complete daily usage array for the month
      for (let day = 1; day <= currentDay; day++) {
        const record = dailyUsageRecords.find(r => r.day === day);
        const formattedDay = day.toString().padStart(2, '0');
        const formattedMonth = month.toString().padStart(2, '0');
        
        if (record) {
          totalUsageBytes += record.usageBytes;
          if (!record.success) errorCount++;
          
          dailyUsageData.push({
            day: record.day,
            date: `${year}-${formattedMonth}-${formattedDay}`,
            usageBytes: record.usageBytes,
            usageGB: record.usageGB,
            entries: record.entries,
            success: record.success,
            error: record.error
          });
        } else {
          // No data synced yet for this day
          dailyUsageData.push({
            day: day,
            date: `${year}-${formattedMonth}-${formattedDay}`,
            usageBytes: 0,
            usageGB: "0.000",
            entries: 0,
            success: false,
            error: "Not synced yet"
          });
          errorCount++;
        }
      }
      
      // Calculate today's usage
      const todayEntry = dailyUsageData.find(entry => entry.day === currentDay);
      const todayUsageBytes = todayEntry?.usageBytes || 0;
      const todayUsageGB = (todayUsageBytes / (1024 * 1024 * 1024)).toFixed(3);
      
      res.json({
        ok: true,
        data: {
          username: username.trim(),
          year: year,
          month: month,
          currentDay: currentDay,
          dailyUsage: dailyUsageData,
          summary: {
            totalUsageBytes: totalUsageBytes,
            totalUsageGB: (totalUsageBytes / (1024 * 1024 * 1024)).toFixed(3),
            todayUsageBytes: todayUsageBytes,
            todayUsageGB: todayUsageGB,
            daysWithData: dailyUsageData.filter(day => day.usageBytes > 0).length,
            totalDays: currentDay,
            errorCount: errorCount
          }
        }
      });
    } catch (error) {
      console.error('Error fetching MTN Mobile daily usage from database:', error);
      res.status(500).json({
        ok: false,
        error: error.message || 'Failed to fetch daily usage data'
      });
    }
  });


  // Start MTN Mobile Daily Usage Sync endpoint
  app.post("/api/mtn-mobile-daily-usage/sync", async (req, res) => {
    try {
      // Generate unique session ID for this sync operation
      const sessionId = `sync_${Date.now()}_${Math.random().toString(36).substr(2, 9)}`;
      
      // Get all MTN Mobile accounts
      const accounts = await storage.getAllMtnMobileAccounts();
      
      if (accounts.length === 0) {
        return res.status(400).json({
          ok: false,
          error: "No MTN Mobile (GSM) accounts found"
        });
      }
      
      // Initialize sync progress
      await storage.createSyncProgress({
        sessionId,
        username: req.session.user?.username || 'system',
        totalAccounts: accounts.length,
        processedAccounts: 0,
        currentAccount: null,
        currentStatus: "Starting sync...",
        totalDays: 0,
        processedDays: 0,
        errors: [],
        isCompleted: false
      });
      
      // Start the sync process in the background
      syncMtnMobileDailyUsage(sessionId, accounts).catch(error => {
        console.error("Background sync error:", error);
      });
      
      res.json({
        ok: true,
        sessionId: sessionId,
        message: "Sync started successfully",
        totalAccounts: accounts.length
      });
    } catch (error) {
      console.error('Error starting MTN Mobile daily usage sync:', error);
      res.status(500).json({
        ok: false,
        error: error.message || 'Failed to start sync'
      });
    }
  });

  // Single account sync endpoint with detailed debugging
  app.post("/api/mtn-mobile-daily-usage/sync-single", requireAuth, async (req, res) => {
    try {
      const { accountNumber } = req.body;
      
      if (!accountNumber) {
        return res.status(400).json({
          ok: false,
          error: "Account number is required"
        });
      }
      
      // Clean and format account number
      let cleanedAccount = accountNumber.toString().trim();
      
      // Auto-format MTN Mobile accounts by appending @mobile.is.co.za if missing
      if (!cleanedAccount.includes('@')) {
        // Assume it's an MTN Mobile account if no @ symbol
        cleanedAccount = `${cleanedAccount}@mobile.is.co.za`;
        console.log(`[Sync-Single] Auto-formatted account: ${accountNumber} -> ${cleanedAccount}`);
      }
      
      // Validate account format after cleaning
      const isMtnMobile = cleanedAccount.includes('@mobile.is.co.za');
      const isMtnFixed = cleanedAccount.includes('@') && !cleanedAccount.includes('@mobile.is.co.za');
      
      if (!isMtnMobile && !isMtnFixed) {
        return res.status(400).json({
          ok: false,
          error: `Invalid account format: "${cleanedAccount}". Must be a valid MTN Mobile (ending with @mobile.is.co.za) or MTN Fixed account.`
        });
      }
      
      console.log(`[Sync-Single] Processing account: "${cleanedAccount}" (original: "${accountNumber}")`);
      
      // Generate unique session ID for this sync operation
      const sessionId = `single_sync_${Date.now()}_${Math.random().toString(36).substr(2, 9)}`;
      
      // Start single account sync with detailed debugging
      const result = await syncSingleAccountDailyUsage(sessionId, cleanedAccount);
      
      res.json({
        ok: true,
        sessionId: sessionId,
        accountNumber: cleanedAccount,
        originalAccount: accountNumber,
        result: result
      });
      
    } catch (error) {
      console.error('Error in single account sync:', error);
      res.status(500).json({
        ok: false,
        error: error.message || 'Failed to sync single account'
      });
    }
  });

  // Test API call endpoint to verify enhanced error logging
  app.get("/api/mtn-mobile-daily-usage/test-api-call", async (req, res) => {
    try {
      const account = req.query.account as string;
      if (!account) {
        return res.status(400).json({ error: "Account parameter required" });
      }
      
      const url = `https://mobile.mtn.co.za/appapi/mobile/api/GetDataUsageForDate?username=${encodeURIComponent(account)}&year=2025&month=7&day=15`;
      console.log(`[Test API Call] Making request to: ${url}`);
      console.log(`[Test API Call] Account: ${account}`);
      
      const response = await fetch(url);
      const responseText = await response.text();
      
      console.log(`[Test API Call] Response status: ${response.status}`);
      console.log(`[Test API Call] Response headers:`, Object.fromEntries(response.headers.entries()));
      console.log(`[Test API Call] Response body: ${responseText}`);
      
      if (!response.ok) {
        console.error(`[Test API Call] API Error - Status: ${response.status}, Body: ${responseText}`);
        return res.status(500).json({ 
          error: "API call failed", 
          status: response.status, 
          body: responseText,
          url: url 
        });
      }
      
      try {
        const data = JSON.parse(responseText);
        console.log(`[Test API Call] Parsed data:`, data);
        res.json({ success: true, data, account, url });
      } catch (parseError) {
        console.error(`[Test API Call] JSON Parse Error:`, parseError);
        res.status(500).json({ 
          error: "Failed to parse response", 
          parseError: parseError.message, 
          body: responseText 
        });
      }
    } catch (error) {
      console.error(`[Test API Call] Fetch Error:`, error);
      res.status(500).json({ error: "Network error", details: error.message });
    }
  });

  // Get MTN Mobile Sync Progress endpoint
  app.get("/api/mtn-mobile-daily-usage/sync/:sessionId", async (req, res) => {
    try {
      const { sessionId } = req.params;
      const progress = await storage.getSyncProgress(sessionId);
      
      if (!progress) {
        return res.status(404).json({
          ok: false,
          error: "Sync session not found"
        });
      }
      
      res.json({
        ok: true,
        data: progress
      });
    } catch (error) {
      console.error('Error fetching sync progress:', error);
      res.status(500).json({
        ok: false,
        error: error.message || 'Failed to fetch sync progress'
      });
    }
  });

  // Stop MTN Mobile Daily Usage Sync endpoint
  app.post("/api/mtn-mobile-daily-usage/sync/:sessionId/stop", requireAuth, async (req, res) => {
    try {
      const { sessionId } = req.params;
      
      // Check if sync session exists
      const progress = await storage.getSyncProgress(sessionId);
      if (!progress) {
        return res.status(404).json({
          ok: false,
          error: "Sync session not found"
        });
      }
      
      // Mark as cancelled
      await storage.updateSyncProgress(sessionId, {
        isCancelled: true,
        currentStatus: "Cancelled by user",
        completedAt: new Date()
      });
      
      console.log(`[Daily Usage Sync ${sessionId}] Cancellation requested by user`);
      
      res.json({
        ok: true,
        message: "Sync cancellation requested"
      });
    } catch (error) {
      console.error('Error stopping sync:', error);
      res.status(500).json({
        ok: false,
        error: error.message || 'Failed to stop sync'
      });
    }
  });

  // Bulk sync MTN Mobile (GSM) usage statistics with comprehensive error logging
  app.post("/api/admin/sync-mtn-mobile-stats", requireAuth, async (req, res) => {
    const syncStartTime = new Date();
    const logPrefix = `[MTN Mobile Sync ${syncStartTime.toISOString()}]`;
    
    try {
      console.log(`${logPrefix} Starting MTN Mobile (GSM) bulk sync...`);
      
      const currentDate = new Date();
      const currentYear = currentDate.getFullYear();
      const currentMonth = currentDate.getMonth() + 1;

      console.log(`${logPrefix} Target period: ${currentYear}-${currentMonth.toString().padStart(2, '0')}`);

      // Clear existing Top 15 data for the current month
      console.log(`${logPrefix} Clearing existing Top 15 data for ${currentYear}-${currentMonth}...`);
      await storage.clearMtnMobileTop15(currentYear, currentMonth);

      // Get all MTN Mobile (GSM) clients with account numbers
      console.log(`${logPrefix} Fetching MTN Mobile (GSM) clients...`);
      const mtnMobileClients = await storage.getClients(undefined, "MTN Mobile (GSM)", 1000, 0);
      const clientsWithAccounts = mtnMobileClients.filter(client => 
        client.accountNumber && client.accountNumber.trim().length > 0
      );
      
      console.log(`${logPrefix} Found ${mtnMobileClients.length} total MTN Mobile clients, ${clientsWithAccounts.length} with account numbers`);
      
      if (clientsWithAccounts.length === 0) {
        console.log(`${logPrefix} No MTN Mobile (GSM) clients with account numbers found`);
        return res.json({ success: true, message: "No MTN Mobile (GSM) clients with account numbers found", processed: 0 });
      }

      let processed = 0;
      let errors = 0;
      const results: Array<{client: string; account: string; usage: number; connectedTime: number}> = [];
      const errorDetails: Array<{client: string; account: string; error: string}> = [];

      // Process in batches to avoid overwhelming the API
      const batchSize = 10;
      console.log(`${logPrefix} Processing ${clientsWithAccounts.length} clients in batches of ${batchSize}...`);
      
      for (let i = 0; i < clientsWithAccounts.length; i += batchSize) {
        const batch = clientsWithAccounts.slice(i, i + batchSize);
        const batchNumber = Math.floor(i / batchSize) + 1;
        const totalBatches = Math.ceil(clientsWithAccounts.length / batchSize);
        
        console.log(`${logPrefix} Processing batch ${batchNumber}/${totalBatches} (clients ${i + 1}-${Math.min(i + batchSize, clientsWithAccounts.length)})`);
        
        await Promise.all(batch.map(async (client, batchIndex) => {
          const clientLogPrefix = `${logPrefix} [Client: ${client.name} | Account: ${client.accountNumber}]`;
          
          try {
            // Clean and validate account number
            const cleanAccountNumber = client.accountNumber!.trim();
            
            // Validate account number format
            if (!cleanAccountNumber || cleanAccountNumber.length < 5) {
              console.log(`${clientLogPrefix} Invalid account number format: "${cleanAccountNumber}"`);
              errorDetails.push({
                client: client.name,
                account: cleanAccountNumber,
                error: `Invalid account number format: "${cleanAccountNumber}"`
              });
              errors++;
              return;
            }
            
            console.log(`${clientLogPrefix} Fetching usage data for cleaned account: "${cleanAccountNumber}"`);
            
            const apiUrl = `http://api.openweb.live:3000/proxy/monthUsage?usernames=${encodeURIComponent(cleanAccountNumber)}&year=${currentYear}&month=${currentMonth}`;
            
            console.log(`${clientLogPrefix} API URL: ${apiUrl}`);
            
            // Use authenticated request
            const openWebAuth = OpenWebAuth.getInstance();
            const response = await openWebAuth.makeAuthenticatedRequest(apiUrl, {
              method: 'GET',
            });

            console.log(`${clientLogPrefix} API response status: ${response.status}`);

            if (response.ok) {
              const apiResponse = await response.json();
              console.log(`${clientLogPrefix} API response:`, JSON.stringify(apiResponse, null, 2));
              
              if (apiResponse.ok && apiResponse.data && Array.isArray(apiResponse.data) && apiResponse.data.length > 0) {
                const monthData = apiResponse.data[0];
                console.log(`${clientLogPrefix} Month data:`, JSON.stringify(monthData, null, 2));
                
                // Parse connected time from "HH:MM:SS" format to minutes
                const connectedTime = monthData.ConnectedTime || "0:0:0";
                const [hours, minutes, seconds] = connectedTime.split(':').map(Number);
                const connectedTimeMinutes = (hours * 60) + minutes + Math.round(seconds / 60);
                const totalBytes = parseInt(monthData.Total) || 0;

                console.log(`${clientLogPrefix} Parsed usage: ${totalBytes} bytes, ${connectedTimeMinutes} minutes`);

                // Save to MTN Mobile Top 15 table
                await storage.saveMtnMobileTop15Stats({
                  accountNumber: cleanAccountNumber,
                  clientName: client.name,
                  msisdn: monthData.MSISDSN || monthData.msisdn || client.msisdn || null,
                  year: currentYear,
                  month: currentMonth,
                  totalBytes: totalBytes,
                  connectedTimeMinutes: connectedTimeMinutes,
                  rankPosition: null // Will be calculated later
                });
                
                results.push({
                  client: client.name,
                  account: cleanAccountNumber,
                  usage: totalBytes,
                  connectedTime: connectedTimeMinutes
                });
                
                processed++;
                console.log(`${clientLogPrefix} Successfully processed and saved to database`);
              } else {
                console.log(`${clientLogPrefix} No usage data found in API response`);
                
                // Save zero usage record
                await storage.saveMtnMobileTop15Stats({
                  accountNumber: cleanAccountNumber,
                  clientName: client.name,
                  msisdn: client.msisdn || null,
                  year: currentYear,
                  month: currentMonth,
                  totalBytes: 0,
                  connectedTimeMinutes: 0,
                  rankPosition: null
                });
                
                results.push({
                  client: client.name,
                  account: cleanAccountNumber,
                  usage: 0,
                  connectedTime: 0
                });
                
                processed++;
                console.log(`${clientLogPrefix} Saved zero usage record`);
              }
            } else {
              const errorText = await response.text();
              console.error(`${clientLogPrefix} API request failed with status ${response.status}: ${errorText}`);
              
              errorDetails.push({
                client: client.name,
                account: cleanAccountNumber,
                error: `API request failed: ${response.status} - ${errorText}`
              });
              errors++;
            }
          } catch (error: any) {
            console.error(`${clientLogPrefix} Exception during processing:`, error);
            
            errorDetails.push({
              client: client.name,
              account: client.accountNumber?.trim() || 'unknown',
              error: error.message || String(error)
            });
            errors++;
          }
        }));

        // Add delay between batches
        if (i + batchSize < clientsWithAccounts.length) {
          console.log(`${logPrefix} Waiting 2 seconds before next batch...`);
          await new Promise(resolve => setTimeout(resolve, 2000));
        }
      }

      // Sort results and calculate rankings
      const sortedResults = results.sort((a, b) => b.usage - a.usage);
      
      // Update rankings in database
      console.log(`${logPrefix} Updating rankings in database...`);
      for (let i = 0; i < Math.min(sortedResults.length, 15); i++) {
        const result = sortedResults[i];
        await storage.saveMtnMobileTop15Stats({
          accountNumber: result.account,
          clientName: result.client,
          msisdn: null, // Will be preserved from existing record
          year: currentYear,
          month: currentMonth,
          totalBytes: result.usage,
          connectedTimeMinutes: result.connectedTime,
          rankPosition: i + 1
        });
      }

      const syncEndTime = new Date();
      const syncDuration = syncEndTime.getTime() - syncStartTime.getTime();
      
      console.log(`${logPrefix} Sync completed in ${syncDuration}ms. Processed: ${processed}, Errors: ${errors}`);
      if (errorDetails.length > 0) {
        console.log(`${logPrefix} Error details:`, JSON.stringify(errorDetails, null, 2));
      }

      res.json({ 
        success: true, 
        message: `MTN Mobile sync completed. Processed: ${processed}, Errors: ${errors}`,
        processed,
        errors,
        total: clientsWithAccounts.length,
        syncDurationMs: syncDuration,
        results: sortedResults.slice(0, 15),
        errorDetails: errorDetails.slice(0, 10) // Include first 10 errors for debugging
      });
    } catch (error: any) {
      const syncEndTime = new Date();
      const syncDuration = syncEndTime.getTime() - syncStartTime.getTime();
      
      console.error(`${logPrefix} Critical error in MTN Mobile bulk sync:`, error);
      console.error(`${logPrefix} Stack trace:`, error.stack);
      
      res.status(500).json({ 
        error: "Failed to sync MTN Mobile statistics", 
        details: error.message,
        syncDurationMs: syncDuration
      });
    }
  });

  // Sync 3 Months of MTN Mobile (GSM) usage data for all clients
  app.post("/api/admin/sync-mtn-mobile-3months", requireAuth, async (req, res) => {
    const syncStartTime = new Date();
    const logPrefix = `[MTN Mobile 3-Month Sync ${syncStartTime.toISOString()}]`;
    
    try {
      console.log(`${logPrefix} Starting MTN Mobile (GSM) 3-month bulk sync...`);
      
      const currentDate = new Date();
      const currentYear = currentDate.getFullYear();
      const currentMonth = currentDate.getMonth() + 1;

      // Calculate 3 months to sync (current month and 2 previous months)
      const monthsToSync = [];
      for (let i = 0; i < 3; i++) {
        const targetDate = new Date(currentYear, currentMonth - 1 - i, 1);
        monthsToSync.push({
          year: targetDate.getFullYear(),
          month: targetDate.getMonth() + 1,
          monthName: targetDate.toLocaleString('default', { month: 'long' })
        });
      }

      console.log(`${logPrefix} Target periods:`, monthsToSync.map(m => `${m.year}-${m.month.toString().padStart(2, '0')} (${m.monthName})`).join(', '));

      // Get all MTN Mobile (GSM) clients with account numbers
      console.log(`${logPrefix} Fetching MTN Mobile (GSM) clients...`);
      const mtnMobileClients = await storage.getClients(undefined, "MTN Mobile (GSM)", 1000, 0);
      const clientsWithAccounts = mtnMobileClients.filter(client => 
        client.accountNumber && client.accountNumber.trim().length > 0
      );
      
      console.log(`${logPrefix} Found ${mtnMobileClients.length} total MTN Mobile clients, ${clientsWithAccounts.length} with account numbers`);
      
      if (clientsWithAccounts.length === 0) {
        console.log(`${logPrefix} No MTN Mobile (GSM) clients with account numbers found`);
        return res.json({ success: true, message: "No MTN Mobile (GSM) clients with account numbers found", processed: 0 });
      }

      let totalProcessed = 0;
      let totalErrors = 0;
      const results: Array<{client: string; account: string; month: string; usage: number; connectedTime: number}> = [];
      const errorDetails: Array<{client: string; account: string; month: string; error: string}> = [];

      // Process each month for all clients  
      for (const monthInfo of monthsToSync) {
        const monthLogPrefix = `${logPrefix} [${monthInfo.monthName} ${monthInfo.year}]`;
        console.log(`${monthLogPrefix} Processing month ${monthInfo.month}/${monthInfo.year}...`);

        // Clear existing data for this month
        await storage.clearMtnMobileTop15(monthInfo.year, monthInfo.month);

        // Process clients in batches
        const batchSize = 8;
        console.log(`${monthLogPrefix} Processing ${clientsWithAccounts.length} clients in batches of ${batchSize}...`);
        
        for (let i = 0; i < clientsWithAccounts.length; i += batchSize) {
          const batch = clientsWithAccounts.slice(i, i + batchSize);
          const batchNumber = Math.floor(i / batchSize) + 1;
          const totalBatches = Math.ceil(clientsWithAccounts.length / batchSize);
          
          console.log(`${monthLogPrefix} Processing batch ${batchNumber}/${totalBatches} (clients ${i + 1}-${Math.min(i + batchSize, clientsWithAccounts.length)})`);
          
          await Promise.all(batch.map(async (client) => {
            const clientLogPrefix = `${monthLogPrefix} [Client: ${client.name} | Account: ${client.accountNumber}]`;
            
            try {
              // Clean and validate account number
              const cleanAccountNumber = client.accountNumber!.trim();
              
              if (!cleanAccountNumber || cleanAccountNumber.length < 5) {
                console.log(`${clientLogPrefix} Invalid account number format: "${cleanAccountNumber}"`);
                errorDetails.push({
                  client: client.name,
                  account: cleanAccountNumber,
                  month: `${monthInfo.year}-${monthInfo.month}`,
                  error: `Invalid account number format: "${cleanAccountNumber}"`
                });
                totalErrors++;
                return;
              }
              
              console.log(`${clientLogPrefix} Fetching usage data for cleaned account: "${cleanAccountNumber}"`);
              
              const apiUrl = `http://api.openweb.live:3000/proxy/monthUsage?usernames=${encodeURIComponent(cleanAccountNumber)}&year=${monthInfo.year}&month=${monthInfo.month}`;
              
              console.log(`${clientLogPrefix} API URL: ${apiUrl}`);
              
              // Use authenticated request
              const openWebAuth = OpenWebAuth.getInstance();
              const response = await openWebAuth.makeAuthenticatedRequest(apiUrl, {
                method: 'GET',
              });

              console.log(`${clientLogPrefix} API response status: ${response.status}`);

              if (response.ok) {
                const apiResponse = await response.json();
                console.log(`${clientLogPrefix} API response:`, JSON.stringify(apiResponse, null, 2));
                
                if (apiResponse.ok && apiResponse.data && Array.isArray(apiResponse.data) && apiResponse.data.length > 0) {
                  const monthData = apiResponse.data[0];
                  console.log(`${clientLogPrefix} Month data:`, JSON.stringify(monthData, null, 2));
                  
                  // Parse connected time from "HH:MM:SS" format to minutes
                  const connectedTime = monthData.ConnectedTime || "0:0:0";
                  const [hours, minutes, seconds] = connectedTime.split(':').map(Number);
                  const connectedTimeMinutes = (hours * 60) + minutes + Math.round(seconds / 60);
                  const totalBytes = parseInt(monthData.Total) || 0;

                  console.log(`${clientLogPrefix} Parsed usage: ${totalBytes} bytes, ${connectedTimeMinutes} minutes`);

                  // Save to MTN Mobile Top 15 table
                  await storage.saveMtnMobileTop15Stats({
                    accountNumber: cleanAccountNumber,
                    clientName: client.name,
                    msisdn: monthData.MSISDSN || monthData.msisdn || client.msisdn || null,
                    year: monthInfo.year,
                    month: monthInfo.month,
                    totalBytes: totalBytes,
                    connectedTimeMinutes: connectedTimeMinutes,
                    rankPosition: null // Will be calculated later
                  });
                  
                  results.push({
                    client: client.name,
                    account: cleanAccountNumber,
                    month: `${monthInfo.year}-${monthInfo.month}`,
                    usage: totalBytes,
                    connectedTime: connectedTimeMinutes
                  });
                  
                  totalProcessed++;
                  console.log(`${clientLogPrefix} Successfully processed and saved to database`);
                } else {
                  console.log(`${clientLogPrefix} No usage data found in API response`);
                  
                  // Save zero usage record
                  await storage.saveMtnMobileTop15Stats({
                    accountNumber: cleanAccountNumber,
                    clientName: client.name,
                    msisdn: client.msisdn || null,
                    year: monthInfo.year,
                    month: monthInfo.month,
                    totalBytes: 0,
                    connectedTimeMinutes: 0,
                    rankPosition: null
                  });
                  
                  results.push({
                    client: client.name,
                    account: cleanAccountNumber,
                    month: `${monthInfo.year}-${monthInfo.month}`,
                    usage: 0,
                    connectedTime: 0
                  });
                  
                  totalProcessed++;
                  console.log(`${clientLogPrefix} Saved zero usage record`);
                }
              } else {
                const errorText = await response.text();
                console.error(`${clientLogPrefix} API request failed with status ${response.status}: ${errorText}`);
                
                errorDetails.push({
                  client: client.name,
                  account: cleanAccountNumber,
                  month: `${monthInfo.year}-${monthInfo.month}`,
                  error: `API request failed: ${response.status} - ${errorText}`
                });
                totalErrors++;
              }
            } catch (error: any) {
              console.error(`${clientLogPrefix} Exception during processing:`, error);
              
              errorDetails.push({
                client: client.name,
                account: client.accountNumber || 'N/A',
                month: `${monthInfo.year}-${monthInfo.month}`,
                error: error.message || 'Unknown error'
              });
              totalErrors++;
            }
          }));

          // Add small delay between batches to prevent API rate limiting
          if (batchNumber < totalBatches) {
            await new Promise(resolve => setTimeout(resolve, 1000));
          }
        }
      }

      const syncEndTime = new Date();
      const syncDuration = syncEndTime.getTime() - syncStartTime.getTime();
      
      console.log(`${logPrefix} Sync completed. Total processed: ${totalProcessed}, Total errors: ${totalErrors}, Duration: ${syncDuration}ms`);
      
      res.json({ 
        success: true, 
        message: `MTN Mobile 3-month sync completed. Processed: ${totalProcessed} records across 3 months, Errors: ${totalErrors}`,
        processed: totalProcessed,
        errors: totalErrors,
        months: monthsToSync.length,
        clients: clientsWithAccounts.length,
        syncDurationMs: syncDuration,
        results: results.slice(0, 20), // Show first 20 results
        errorDetails: errorDetails.slice(0, 10) // Show first 10 errors for debugging
      });
    } catch (error: any) {
      const syncEndTime = new Date();
      const syncDuration = syncEndTime.getTime() - syncStartTime.getTime();
      
      console.error(`${logPrefix} Critical error in MTN Mobile 3-month bulk sync:`, error);
      console.error(`${logPrefix} Stack trace:`, error.stack);
      
      res.status(500).json({ 
        error: "Failed to sync MTN Mobile 3-month statistics", 
        details: error.message,
        syncDurationMs: syncDuration
      });
    }
  });

  // Get Top 15 MTN Mobile (GSM) users by current month usage from dedicated table
  app.get("/api/admin/mtn-mobile-top15", requireAuth, async (req, res) => {
    const logPrefix = `[MTN Mobile Top 15 Query]`;
    
    try {
      console.log(`${logPrefix} Fetching Top 15 MTN Mobile users...`);
      
      const currentDate = new Date();
      const currentYear = currentDate.getFullYear();
      const currentMonth = currentDate.getMonth() + 1;

      console.log(`${logPrefix} Query period: ${currentYear}-${currentMonth.toString().padStart(2, '0')}`);

      // Get MTN Mobile Top 15 from dedicated table
      const topUsers = await storage.getMtnMobileTop15(currentYear, currentMonth);
      
      console.log(`${logPrefix} Found ${topUsers.length} users in Top 15 table`);
      
      // Transform for frontend compatibility
      const transformedUsers = topUsers.map(user => ({
        username: user.accountNumber,
        name: user.clientName,
        msisdn: user.msisdn || '',
        totalBytes: user.totalBytes,
        connectedTimeMinutes: user.connectedTimeMinutes
      }));
      
      console.log(`${logPrefix} Returning ${transformedUsers.length} transformed users`);
      
      res.json({
        success: true,
        year: currentYear,
        month: currentMonth,
        monthName: currentDate.toLocaleString('default', { month: 'long' }),
        users: transformedUsers,
        count: transformedUsers.length
      });
    } catch (error: any) {
      console.error(`${logPrefix} Error fetching MTN Mobile top 15:`, error);
      console.error(`${logPrefix} Stack trace:`, error.stack);
      
      res.status(500).json({ 
        error: "Failed to fetch top MTN Mobile users",
        details: error.message
      });
    }
  });

  // Get Bottom 30 MTN Mobile (GSM) users by current month usage (lowest/zero usage)
  app.get("/api/admin/mtn-mobile-bottom30", requireAuth, async (req, res) => {
    const logPrefix = `[MTN Mobile Bottom 30 Query]`;
    
    try {
      console.log(`${logPrefix} Fetching Bottom 30 MTN Mobile users...`);
      
      const currentDate = new Date();
      const currentYear = currentDate.getFullYear();
      const currentMonth = currentDate.getMonth() + 1;

      console.log(`${logPrefix} Query period: ${currentYear}-${currentMonth.toString().padStart(2, '0')}`);

      // Get MTN Mobile Bottom 30 from dedicated table (lowest usage)
      const bottomUsers = await storage.getMtnMobileBottom30(currentYear, currentMonth);
      
      console.log(`${logPrefix} Found ${bottomUsers.length} users in Bottom 30 table`);
      
      // Transform for frontend compatibility
      const transformedUsers = bottomUsers.map(user => ({
        username: user.accountNumber,
        name: user.clientName,
        msisdn: user.msisdn || '',
        totalBytes: user.totalBytes,
        connectedTimeMinutes: user.connectedTimeMinutes
      }));
      
      console.log(`${logPrefix} Returning ${transformedUsers.length} transformed users`);
      
      res.json({
        success: true,
        year: currentYear,
        month: currentMonth,
        monthName: currentDate.toLocaleString('default', { month: 'long' }),
        users: transformedUsers,
        count: transformedUsers.length
      });
    } catch (error: any) {
      console.error(`${logPrefix} Error fetching MTN Mobile bottom 30:`, error);
      console.error(`${logPrefix} Stack trace:`, error.stack);
      
      res.status(500).json({ 
        error: "Failed to fetch bottom MTN Mobile users",
        details: error.message
      });
    }
  });

  // Bulk refresh MTN statistics for all users
  app.post("/api/admin/refresh-mtn-stats", requireAuth, async (req, res) => {
    try {
      const currentDate = new Date();
      const currentYear = currentDate.getFullYear();
      const currentMonth = currentDate.getMonth() + 1;

      // Get all MTN Fixed usernames
      const usernames = await storage.getAllMtnFixedUsernames();
      
      if (usernames.length === 0) {
        return res.json({ success: true, message: "No MTN Fixed clients found", processed: 0 });
      }

      let processed = 0;
      let errors = 0;

      // Process in batches to avoid overwhelming the API
      const batchSize = 10;
      for (let i = 0; i < usernames.length; i += batchSize) {
        const batch = usernames.slice(i, i + batchSize);
        
        await Promise.all(batch.map(async (username) => {
          try {
            const apiUrl = `http://api.openweb.live:3000/proxy/monthUsage?usernames=${encodeURIComponent(username)}&year=${currentYear}&month=${currentMonth}`;
            
            const openWebAuth = OpenWebAuth.getInstance();
            const response = await openWebAuth.makeAuthenticatedRequest(apiUrl, {
              method: 'GET',
            });

            if (response.ok) {
              const apiResponse = await response.json();
              
              if (apiResponse.ok && apiResponse.data && Array.isArray(apiResponse.data) && apiResponse.data.length > 0) {
                const monthData = apiResponse.data[0];
                
                // Parse connected time from "HH:MM:SS" format to minutes
                const connectedTime = monthData.ConnectedTime || "0:0:0";
                const [hours, minutes, seconds] = connectedTime.split(':').map(Number);
                const connectedTimeMinutes = (hours * 60) + minutes + Math.round(seconds / 60);

                await storage.saveMtnStatistics({
                  username: username,
                  msisdn: monthData.MSISDSN || monthData.msisdn || null,
                  year: currentYear,
                  month: currentMonth,
                  totalBytes: parseInt(monthData.Total) || 0,
                  connectedTimeMinutes: connectedTimeMinutes
                });
                
                processed++;
              } else {
                // Save zero usage record for users with no data
                await storage.saveMtnStatistics({
                  username: username,
                  msisdn: null,
                  year: currentYear,
                  month: currentMonth,
                  totalBytes: 0,
                  connectedTimeMinutes: 0
                });
                processed++;
              }
            } else {
              errors++;
            }
          } catch (error) {
            console.error(`Error processing ${username}:`, error);
            errors++;
          }
        }));

        // Add small delay between batches
        if (i + batchSize < usernames.length) {
          await new Promise(resolve => setTimeout(resolve, 100));
        }
      }

      res.json({ 
        success: true, 
        message: `Bulk refresh completed. Processed: ${processed}, Errors: ${errors}`,
        processed,
        errors,
        total: usernames.length
      });
    } catch (error) {
      console.error("Error in bulk refresh:", error);
      res.status(500).json({ error: "Failed to refresh MTN statistics" });
    }
  });

  // Get MTN clients with no data usage for current month
  app.get("/api/admin/mtn-no-data", requireAuth, async (req, res) => {
    try {
      const currentDate = new Date();
      const currentYear = currentDate.getFullYear();
      const currentMonth = currentDate.getMonth() + 1;

      const clientsWithNoData = await storage.getMtnClientsWithNoData(currentYear, currentMonth);
      
      res.json({
        success: true,
        year: currentYear,
        month: currentMonth,
        monthName: currentDate.toLocaleString('default', { month: 'long' }),
        clients: clientsWithNoData,
        count: clientsWithNoData.length
      });
    } catch (error) {
      console.error("Error fetching MTN clients with no data:", error);
      res.status(500).json({ error: "Failed to fetch clients with no data usage" });
    }
  });

  // User management routes
  app.get("/api/users", requireAuth, async (req, res) => {
    try {
      const users = await storage.getUsers();
      const sanitizedUsers = users.map(user => ({
        id: user.id,
        username: user.username,
        name: user.name,
        mobileNumber: user.mobileNumber,
        role: user.role,
        isActive: user.isActive,
        createdAt: user.createdAt,
        updatedAt: user.updatedAt
      }));
      res.json(sanitizedUsers);
    } catch (error) {
      console.error("Error fetching users:", error);
      res.status(500).json({ error: "Internal server error" });
    }
  });

  app.post("/api/users", requireAuth, async (req, res) => {
    try {
      const validatedData = insertUserSchema.parse(req.body);
      
      // Hash the password
      const hashedPassword = await hashPassword(validatedData.password);
      
      const user = await storage.createUser({
        ...validatedData,
        password: hashedPassword
      });
      
      // Return user without password
      const { password, ...userWithoutPassword } = user;
      res.status(201).json(userWithoutPassword);
    } catch (error) {
      console.error("Error creating user:", error);
      res.status(500).json({ error: "Internal server error" });
    }
  });

  app.put("/api/users/:id", requireAuth, async (req, res) => {
    try {
      const id = parseInt(req.params.id);
      const updateData = req.body;
      
      // If password is being updated, hash it
      if (updateData.password) {
        updateData.password = await hashPassword(updateData.password);
      }
      
      const user = await storage.updateUser(id, updateData);
      
      // Return user without password
      const { password, ...userWithoutPassword } = user;
      res.json(userWithoutPassword);
    } catch (error) {
      console.error("Error updating user:", error);
      res.status(500).json({ error: "Internal server error" });
    }
  });

  app.delete("/api/users/:id", requireAuth, async (req, res) => {
    try {
      const id = parseInt(req.params.id);
      
      // Prevent deleting the current user
      if (id === req.session.userId) {
        return res.status(400).json({ error: "Cannot delete your own account" });
      }
      
      await storage.deleteUser(id);
      res.json({ message: "User deleted successfully" });
    } catch (error) {
      console.error("Error deleting user:", error);
      res.status(500).json({ error: "Internal server error" });
    }
  });

  // Categories routes
  app.get("/api/categories", requireAuth, async (req, res) => {
    try {
      const categories = await storage.getActiveCategories();
      res.json(categories);
    } catch (error) {
      console.error("Error fetching categories:", error);
      res.status(500).json({ error: "Failed to fetch categories" });
    }
  });
  
  // Client routes (now protected)
  app.get("/api/clients", async (req, res) => {
    try {
      const { search, category, limit = "50", offset = "0" } = req.query;
      const [clients, total] = await Promise.all([
        storage.getClients(
          search as string,
          category as string,
          parseInt(limit as string),
          parseInt(offset as string)
        ),
        storage.getClientsCount(search as string, category as string)
      ]);
      res.json({ clients, total });
    } catch (error) {
      console.error("Error fetching clients:", error);
      res.status(500).json({ message: "Failed to fetch clients" });
    }
  });

  app.get("/api/clients/telkom", async (req, res) => {
    try {
      const { page = "1", limit = "10" } = req.query;
      const [clients, total] = await Promise.all([
        storage.getTelkomClients(
          parseInt(page as string),
          parseInt(limit as string)
        ),
        storage.getTelkomClientsCount()
      ]);
      res.json({ clients, total });
    } catch (error) {
      console.error("Error fetching Telkom clients:", error);
      res.status(500).json({ message: "Failed to fetch Telkom clients" });
    }
  });

  app.get("/api/clients/:id", async (req, res) => {
    try {
      const client = await storage.getClient(parseInt(req.params.id));
      if (!client) {
        return res.status(404).json({ message: "Client not found" });
      }
      res.json(client);
    } catch (error) {
      console.error("Error fetching client:", error);
      res.status(500).json({ message: "Failed to fetch client" });
    }
  });

  app.post("/api/clients", requireAuth, async (req, res) => {
    try {
      const validatedData = insertClientSchema.parse(req.body);
      const client = await storage.createClient(validatedData);
      
      // Log client creation activity
      await ActivityLogger.createClient(
        req.session.userId!,
        client.id,
        client.name,
        req
      );
      
      res.status(201).json(client);
    } catch (error) {
      if (error instanceof z.ZodError) {
        return res.status(400).json({ message: "Invalid data", errors: error.errors });
      }
      console.error("Error creating client:", error);
      res.status(500).json({ message: "Failed to create client" });
    }
  });

  app.put("/api/clients/:id", requireAuth, async (req, res) => {
    try {
      const clientId = parseInt(req.params.id);
      const validatedData = insertClientSchema.partial().parse(req.body);
      
      // Get old client data for comparison
      const oldClient = await storage.getClient(clientId);
      const client = await storage.updateClient(clientId, validatedData);
      
      // Log client edit activity with changes
      const changes = {
        oldData: oldClient ? {
          name: oldClient.name,
          category: oldClient.category,
          msisdn: oldClient.msisdn,
          status: oldClient.status
        } : null,
        newData: {
          name: client.name,
          category: client.category,
          msisdn: client.msisdn,
          status: client.status
        }
      };
      
      await ActivityLogger.editClient(
        req.session.userId!,
        client.id,
        client.name,
        changes,
        req
      );
      
      res.json(client);
    } catch (error) {
      if (error instanceof z.ZodError) {
        return res.status(400).json({ message: "Invalid data", errors: error.errors });
      }
      console.error("Error updating client:", error);
      res.status(500).json({ message: "Failed to update client" });
    }
  });

  app.delete("/api/clients/:id", requireAuth, async (req, res) => {
    try {
      const clientId = parseInt(req.params.id);
      
      // Get client data before deletion for logging
      const client = await storage.getClient(clientId);
      
      await storage.deleteClient(clientId);
      
      // Log client deletion activity
      if (client) {
        await ActivityLogger.deleteClient(
          req.session.userId!,
          client.id,
          client.name,
          req
        );
      }
      
      res.status(204).send();
    } catch (error) {
      console.error("Error deleting client:", error);
      res.status(500).json({ message: "Failed to delete client" });
    }
  });

  // Simswap routes
  app.get("/api/simswaps", async (req, res) => {
    try {
      const { clientId } = req.query;
      const simswaps = await storage.getSimswaps(clientId ? parseInt(clientId as string) : undefined);
      res.json(simswaps);
    } catch (error) {
      console.error("Error fetching simswaps:", error);
      res.status(500).json({ message: "Failed to fetch simswaps" });
    }
  });

  app.post("/api/simswaps", async (req, res) => {
    try {
      const validatedData = insertSimswapSchema.parse(req.body);
      const simswap = await storage.createSimswap(validatedData);
      res.status(201).json(simswap);
    } catch (error) {
      if (error instanceof z.ZodError) {
        return res.status(400).json({ message: "Invalid data", errors: error.errors });
      }
      console.error("Error creating simswap:", error);
      res.status(500).json({ message: "Failed to create simswap" });
    }
  });

  app.put("/api/simswaps/:id", async (req, res) => {
    try {
      const validatedData = insertSimswapSchema.partial().parse(req.body);
      const simswap = await storage.updateSimswap(parseInt(req.params.id), validatedData);
      res.json(simswap);
    } catch (error) {
      if (error instanceof z.ZodError) {
        return res.status(400).json({ message: "Invalid data", errors: error.errors });
      }
      console.error("Error updating simswap:", error);
      res.status(500).json({ message: "Failed to update simswap" });
    }
  });

  app.delete("/api/simswaps/:id", async (req, res) => {
    try {
      await storage.deleteSimswap(parseInt(req.params.id));
      res.status(204).send();
    } catch (error) {
      console.error("Error deleting simswap:", error);
      res.status(500).json({ message: "Failed to delete simswap" });
    }
  });

  // Statistics route
  app.get("/api/stats", async (req, res) => {
    try {
      const stats = await storage.getStats();
      res.json(stats);
    } catch (error) {
      console.error("Error fetching stats:", error);
      res.status(500).json({ message: "Failed to fetch statistics" });
    }
  });

  // Recently viewed clients route
  app.get("/api/recently-viewed", requireAuth, async (req, res) => {
    try {
      const limit = parseInt(req.query.limit as string) || 10;
      const recentClients = await storage.getRecentlyViewedClients(limit);
      res.json(recentClients);
    } catch (error) {
      console.error("Error fetching recently viewed clients:", error);
      res.status(500).json({ message: "Failed to fetch recently viewed clients" });
    }
  });

  // Track client view route
  app.post("/api/track-view/:clientId", requireAuth, async (req, res) => {
    try {
      const clientId = parseInt(req.params.clientId);
      const userId = req.session?.user?.id;
      
      if (!userId) {
        return res.status(401).json({ error: "User not authenticated" });
      }
      
      await storage.trackClientView(clientId, userId);
      
      // Get client data for logging
      const client = await storage.getClient(clientId);
      if (client) {
        await ActivityLogger.viewClient(userId, client.id, client.name, req);
      }
      
      res.json({ success: true });
    } catch (error) {
      console.error("Error tracking client view:", error);
      res.status(500).json({ message: "Failed to track client view" });
    }
  });

  // MTN Device Unlock Proxy Endpoint
  app.post("/api/unlock-device", requireAuth, async (req, res) => {
    console.log("[UNLOCK] Device unlock request initiated");
    console.log("[UNLOCK] Request body:", JSON.stringify(req.body, null, 2));
    console.log("[UNLOCK] User session:", req.session?.user?.username || "Unknown");
    console.log("[UNLOCK] Timestamp:", new Date().toISOString());

    try {
      const { Type, Username, Comment, Latitude, Longitude } = req.body;

      // Validate required fields
      if (!Type || !Username || !Comment) {
        console.error("[UNLOCK] Missing required fields - Type:", Type, "Username:", Username, "Comment:", Comment);
        return res.status(400).json({ 
          error: "Missing required fields", 
          required: ["Type", "Username", "Comment"] 
        });
      }

      console.log("[UNLOCK] Validation passed - preparing external API call");
      console.log("[UNLOCK] Target API: http://api.openweb.live:3000/proxy/unlockMTNSim");

      // Build payload based on unlock type
      let unlockPayload = {
        Type: Type,
        Username: Username,
        Comment: Comment
      };

      // Add location data for LocationUnlock type
      if (Type === "LocationUnlock" && Latitude && Longitude) {
        unlockPayload.Location = {
          Latitude: Latitude,
          Longitude: Longitude
        };
      }

      console.log("[UNLOCK] Prepared payload:", JSON.stringify(unlockPayload, null, 2));

      // Make the external API call
      const openWebAuth = OpenWebAuth.getInstance();
      const response = await openWebAuth.makeAuthenticatedRequest('http://api.openweb.live:3000/proxy/unlockMTNSim', {
        method: 'POST',
        body: JSON.stringify(unlockPayload)
      });

      console.log("[UNLOCK] External API response status:", response.status);
      console.log("[UNLOCK] External API response headers:", Object.fromEntries(response.headers.entries()));

      if (response.ok) {
        const result = await response.text();
        console.log("[UNLOCK] External API success response:", result);
        
        let parsedResult;
        try {
          parsedResult = JSON.parse(result);
        } catch (parseError) {
          console.log("[UNLOCK] Response is not JSON, treating as text");
          parsedResult = { message: result };
        }

        console.log("[UNLOCK] Successfully processed unlock request for user:", Username);
        res.json({ 
          success: true, 
          message: "Device unlock request processed successfully",
          data: parsedResult,
          timestamp: new Date().toISOString()
        });
      } else {
        const errorText = await response.text();
        console.error("[UNLOCK] External API error response:", errorText);
        console.error("[UNLOCK] Failed with status:", response.status, response.statusText);
        
        res.status(response.status).json({ 
          error: "External API error", 
          message: errorText || response.statusText,
          status: response.status 
        });
      }
    } catch (error) {
      console.error("[UNLOCK] Critical error during unlock process:", error);
      console.error("[UNLOCK] Error stack:", error.stack);
      console.error("[UNLOCK] Error name:", error.name);
      console.error("[UNLOCK] Error message:", error.message);
      
      res.status(500).json({ 
        error: "Internal server error during unlock process",
        message: error.message,
        timestamp: new Date().toISOString()
      });
    }
  });

  // Staff Notifications routes
  app.get("/api/staff-notifications", requireAuth, async (req, res) => {
    try {
      const notifications = await storage.getActiveStaffNotifications();
      res.json(notifications);
    } catch (error) {
      console.error("Error fetching staff notifications:", error);
      res.status(500).json({ error: "Failed to fetch staff notifications" });
    }
  });

  app.get("/api/staff-notifications/all", requireAuth, async (req, res) => {
    try {
      if (req.session?.user?.role !== "admin") {
        return res.status(403).json({ error: "Access denied" });
      }
      const notifications = await storage.getStaffNotifications();
      res.json(notifications);
    } catch (error) {
      console.error("Error fetching all staff notifications:", error);
      res.status(500).json({ error: "Failed to fetch staff notifications" });
    }
  });

  app.get("/api/staff-notifications/:id", requireAuth, async (req, res) => {
    try {
      if (req.session?.user?.role !== "admin") {
        return res.status(403).json({ error: "Access denied" });
      }
      const notification = await storage.getStaffNotification(parseInt(req.params.id));
      if (!notification) {
        return res.status(404).json({ error: "Staff notification not found" });
      }
      res.json(notification);
    } catch (error) {
      console.error("Error fetching staff notification:", error);
      res.status(500).json({ error: "Failed to fetch staff notification" });
    }
  });

  app.post("/api/staff-notifications", requireAuth, async (req, res) => {
    try {
      if (req.session?.user?.role !== "admin") {
        return res.status(403).json({ error: "Access denied" });
      }
      const validatedData = insertStaffNotificationSchema.parse({
        ...req.body,
        createdBy: req.session.user.id
      });
      const notification = await storage.createStaffNotification(validatedData);
      res.status(201).json(notification);
    } catch (error) {
      if (error instanceof z.ZodError) {
        return res.status(400).json({ error: "Invalid data", details: error.errors });
      }
      console.error("Error creating staff notification:", error);
      res.status(500).json({ error: "Failed to create staff notification" });
    }
  });

  app.put("/api/staff-notifications/:id", requireAuth, async (req, res) => {
    try {
      if (req.session?.user?.role !== "admin") {
        return res.status(403).json({ error: "Access denied" });
      }
      const validatedData = insertStaffNotificationSchema.partial().parse(req.body);
      const notification = await storage.updateStaffNotification(parseInt(req.params.id), validatedData);
      res.json(notification);
    } catch (error) {
      if (error instanceof z.ZodError) {
        return res.status(400).json({ error: "Invalid data", details: error.errors });
      }
      console.error("Error updating staff notification:", error);
      res.status(500).json({ error: "Failed to update staff notification" });
    }
  });

  app.delete("/api/staff-notifications/:id", requireAuth, async (req, res) => {
    try {
      if (req.session?.user?.role !== "admin") {
        return res.status(403).json({ error: "Access denied" });
      }
      await storage.deleteStaffNotification(parseInt(req.params.id));
      res.status(204).send();
    } catch (error) {
      console.error("Error deleting staff notification:", error);
      res.status(500).json({ error: "Failed to delete staff notification" });
    }
  });

  // ================================
  // EXTERNAL API ENDPOINTS
  // ================================
  
  // External API - Get all categories
  app.get("/api/external/categories", await requireApiKey(['read:categories']), async (req: ApiAuthRequest, res) => {
    try {
      const categories = await storage.getActiveCategories();
      res.json({
        success: true,
        data: categories.map(cat => ({
          id: cat.id,
          name: cat.name,
          description: cat.description || "",
          isArchived: cat.isArchived || false
        }))
      });
    } catch (error) {
      console.error('Error fetching categories for external API:', error);
      res.status(500).json({ success: false, error: "Failed to fetch categories" });
    }
  });

  // External API - Get accounts by category
  app.get("/api/external/accounts/:category", await requireApiKey(['read:accounts']), async (req: ApiAuthRequest, res) => {
    try {
      const { category } = req.params;
      const { limit = 1000, offset = 0 } = req.query;
      
      const clients = await storage.getClients("", category, Number(limit), Number(offset));
      const total = await storage.getClientsCount("", category);
      
      res.json({
        success: true,
        data: {
          accounts: clients.map(client => ({
            id: client.id,
            name: client.name,
            email: client.email || "",
            category: client.category,
            serviceDetails: client.serviceDetails || "",
            accountNumber: client.accountNumber || "",
            status: client.status || "Active",
            contactInfo: client.contactInfo || "",
            address: client.address || "",
            notes: client.notes || "",
            msisdn: client.msisdn || "",
            agent: client.agent || "",
            telkomHandle: client.telkomHandle || "",
            fup: client.fup || "", // FUP field for Telkom clients
            simType: client.simType || "",
            simSerialNumber: client.simSerialNumber || "",
            status2: client.status2 || "",
            comments: client.comments || "",
            isReseller: client.isReseller || false,
            createdAt: client.createdAt,
            updatedAt: client.updatedAt
          })),
          pagination: {
            total,
            limit: Number(limit),
            offset: Number(offset),
            hasMore: (Number(offset) + Number(limit)) < total
          }
        }
      });
    } catch (error) {
      console.error('Error fetching accounts for external API:', error);
      res.status(500).json({ success: false, error: "Failed to fetch accounts" });
    }
  });

  // ================================
  // API KEY MANAGEMENT (Admin Only)
  // ================================
  
  // Get all API keys (admin only)
  app.get("/api/admin/api-keys", requireAuth, async (req, res) => {
    try {
      if (req.session?.user?.role !== 'admin') {
        return res.status(403).json({ error: "Admin access required" });
      }
      
      const apiKeys = await storage.getApiKeys();
      res.json(apiKeys.map(key => ({
        ...key,
        keyHash: undefined // Don't expose the hash
      })));
    } catch (error) {
      console.error('Error fetching API keys:', error);
      res.status(500).json({ error: "Failed to fetch API keys" });
    }
  });

  // Create new API key (admin only)
  app.post("/api/admin/api-keys", requireAuth, async (req, res) => {
    try {
      if (req.session?.user?.role !== 'admin') {
        return res.status(403).json({ error: "Admin access required" });
      }
      
      const { name, permissions, expiresAt } = req.body;
      
      if (!name) {
        return res.status(400).json({ error: "API key name is required" });
      }
      
      const { key, hash, preview } = generateApiKey();
      
      const apiKey = await storage.createApiKey({
        name,
        keyHash: hash,
        keyPreview: preview,
        createdBy: req.session.user.id,
        permissions: permissions || ['read:categories', 'read:accounts'],
        expiresAt: expiresAt ? new Date(expiresAt) : null,
        isActive: true
      });
      
      res.json({
        ...apiKey,
        keyHash: undefined, // Don't expose the hash
        key: key // Only return the actual key on creation
      });
    } catch (error) {
      console.error('Error creating API key:', error);
      res.status(500).json({ error: "Failed to create API key" });
    }
  });

  // Update API key (admin only)
  app.put("/api/admin/api-keys/:id", requireAuth, async (req, res) => {
    try {
      if (req.session?.user?.role !== 'admin') {
        return res.status(403).json({ error: "Admin access required" });
      }
      
      const { id } = req.params;
      const { name, permissions, isActive, expiresAt } = req.body;
      
      const updatedKey = await storage.updateApiKey(Number(id), {
        name,
        permissions,
        isActive,
        expiresAt: expiresAt ? new Date(expiresAt) : null
      });
      
      res.json({
        ...updatedKey,
        keyHash: undefined // Don't expose the hash
      });
    } catch (error) {
      console.error('Error updating API key:', error);
      res.status(500).json({ error: "Failed to update API key" });
    }
  });

  // Delete API key (admin only)
  app.delete("/api/admin/api-keys/:id", requireAuth, async (req, res) => {
    try {
      if (req.session?.user?.role !== 'admin') {
        return res.status(403).json({ error: "Admin access required" });
      }
      
      const { id } = req.params;
      await storage.deleteApiKey(Number(id));
      res.json({ success: true });
    } catch (error) {
      console.error('Error deleting API key:', error);
      res.status(500).json({ error: "Failed to delete API key" });
    }
  });

  // ================================
  // API DOCUMENTATION
  // ================================
  
  // API Documentation endpoint - Raw JSON
  app.get("/api/docs.json", (req, res) => {
    const docs = {
      title: "Masterfile Client Management API",
      version: "1.0.0",
      description: "API for accessing client and category data from the Masterfile system",
      baseUrl: `https://masterfile.openweb.live`,
      authentication: {
        type: "Bearer Token",
        description: "Include your API key in the Authorization header as 'Bearer YOUR_API_KEY'",
        example: "Authorization: Bearer your-api-key-here"
      },
      endpoints: {
        categories: {
          method: "GET",
          path: "/api/external/categories",
          description: "Get all available categories",
          permissions: ["read:categories"],
          response: {
            success: true,
            data: [
              {
                id: 1,
                name: "Telkom",
                description: "Telkom clients and services",
                isArchived: false
              }
            ]
          }
        },
        accounts: {
          method: "GET", 
          path: "/api/external/accounts/:category",
          description: "Get all accounts for a specific category",
          permissions: ["read:accounts"],
          parameters: {
            category: "Category name (e.g., 'Telkom', 'MTN Mobile (GSM)', 'MTN Fixed')",
            limit: "Optional: Number of results to return (default: 1000)",
            offset: "Optional: Number of results to skip (default: 0)"
          },
          response: {
            success: true,
            data: {
              accounts: [
                {
                  id: 1,
                  name: "Client Name",
                  email: "client@example.com",
                  category: "Telkom",
                  serviceDetails: "Service information",
                  accountNumber: "Account number",
                  status: "Active",
                  contactInfo: "Contact details",
                  address: "Physical address",
                  notes: "Additional notes",
                  msisdn: "Mobile number",
                  agent: "Agent name",
                  telkomHandle: "Telkom handle",
                  fup: "50", // FUP in GB for Telkom clients
                  simType: "SIM type",
                  simSerialNumber: "SIM serial number",
                  status2: "Secondary status",
                  comments: "Comments",
                  isReseller: false,
                  createdAt: "2025-01-01T00:00:00.000Z",
                  updatedAt: "2025-01-01T00:00:00.000Z"
                }
              ],
              pagination: {
                total: 100,
                limit: 1000,
                offset: 0,
                hasMore: false
              }
            }
          }
        }
      },
      errorHandling: {
        "401": "Invalid or missing API key",
        "403": "Insufficient permissions",
        "404": "Resource not found",
        "500": "Internal server error"
      },
      examples: {
        getCategories: `curl -H "Authorization: Bearer YOUR_API_KEY" https://masterfile.openweb.live/api/external/categories`,
        getAccounts: `curl -H "Authorization: Bearer YOUR_API_KEY" https://masterfile.openweb.live/api/external/accounts/Telkom?limit=10&offset=0`
      }
    };
    
    res.json(docs);
  });

  // API Documentation endpoint - Formatted HTML
  app.get("/api/docs", (req, res) => {
    const baseUrl = `https://masterfile.openweb.live`;
    
    const html = `
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Masterfile Client Management API Documentation</title>
    <style>
        * {
            margin: 0;
            padding: 0;
            box-sizing: border-box;
        }
        
        body {
            font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, Oxygen, Ubuntu, Cantarell, sans-serif;
            line-height: 1.6;
            color: #333;
            background-color: #f8f9fa;
        }
        
        .container {
            max-width: 1200px;
            margin: 0 auto;
            padding: 20px;
        }
        
        .header {
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            color: white;
            padding: 40px 0;
            margin-bottom: 30px;
            border-radius: 8px;
        }
        
        .header h1 {
            font-size: 2.5rem;
            margin-bottom: 10px;
            text-align: center;
        }
        
        .header p {
            font-size: 1.1rem;
            text-align: center;
            opacity: 0.9;
        }
        
        .version-badge {
            display: inline-block;
            background: rgba(255,255,255,0.2);
            padding: 4px 12px;
            border-radius: 20px;
            font-size: 0.9rem;
            margin-left: 10px;
        }
        
        .section {
            background: white;
            border-radius: 8px;
            padding: 30px;
            margin-bottom: 30px;
            box-shadow: 0 2px 4px rgba(0,0,0,0.1);
        }
        
        .section h2 {
            color: #2c3e50;
            border-bottom: 3px solid #3498db;
            padding-bottom: 10px;
            margin-bottom: 20px;
            font-size: 1.8rem;
        }
        
        .section h3 {
            color: #34495e;
            margin: 25px 0 15px 0;
            font-size: 1.3rem;
        }
        
        .endpoint {
            border: 1px solid #e1e8ed;
            border-radius: 6px;
            margin-bottom: 20px;
            overflow: hidden;
        }
        
        .endpoint-header {
            background: #f8f9fa;
            padding: 15px 20px;
            border-bottom: 1px solid #e1e8ed;
        }
        
        .method {
            display: inline-block;
            padding: 4px 8px;
            border-radius: 4px;
            font-weight: bold;
            font-size: 0.8rem;
            margin-right: 10px;
        }
        
        .method.get {
            background: #d4edda;
            color: #155724;
        }
        
        .endpoint-path {
            font-family: 'Monaco', 'Menlo', 'Ubuntu Mono', monospace;
            font-size: 1.1rem;
            color: #495057;
        }
        
        .endpoint-body {
            padding: 20px;
        }
        
        .code-block {
            background: #f8f9fa;
            border: 1px solid #e9ecef;
            border-radius: 4px;
            padding: 15px;
            margin: 15px 0;
            font-family: 'Monaco', 'Menlo', 'Ubuntu Mono', monospace;
            font-size: 0.9rem;
            overflow-x: auto;
            white-space: pre;
        }
        
        .response-example {
            background: #f1f3f4;
            border-left: 4px solid #4caf50;
            padding: 15px;
            margin: 15px 0;
        }
        
        .auth-box {
            background: #fff3cd;
            border: 1px solid #ffeaa7;
            border-radius: 6px;
            padding: 20px;
            margin: 20px 0;
        }
        
        .auth-box h4 {
            color: #856404;
            margin-bottom: 10px;
        }
        
        .parameters table {
            width: 100%;
            border-collapse: collapse;
            margin: 15px 0;
        }
        
        .parameters th,
        .parameters td {
            padding: 12px;
            text-align: left;
            border-bottom: 1px solid #ddd;
        }
        
        .parameters th {
            background-color: #f8f9fa;
            font-weight: 600;
        }
        
        .error-codes {
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));
            gap: 15px;
            margin: 20px 0;
        }
        
        .error-code {
            background: #f8f9fa;
            border-left: 4px solid #dc3545;
            padding: 15px;
            border-radius: 4px;
        }
        
        .error-code strong {
            color: #dc3545;
        }
        
        .try-it-btn {
            background: #007bff;
            color: white;
            border: none;
            padding: 10px 20px;
            border-radius: 4px;
            cursor: pointer;
            font-size: 0.9rem;
            margin: 10px 0;
        }
        
        .try-it-btn:hover {
            background: #0056b3;
        }
        
        .base-url {
            background: #e3f2fd;
            border: 1px solid #90caf9;
            padding: 10px 15px;
            border-radius: 4px;
            margin: 15px 0;
            font-family: monospace;
        }
    </style>
</head>
<body>
    <div class="container">
        <div class="header">
            <h1>Masterfile Client Management API
                <span class="version-badge">v1.0.0</span>
            </h1>
            <p>Comprehensive API for accessing client and category data from the Masterfile telecommunications management system</p>
        </div>

        <div class="section">
            <h2>📋 Overview</h2>
            <p>The Masterfile API provides secure access to client and category data for external applications. This REST API uses JSON for data exchange and requires API key authentication for all requests.</p>
            
            <div class="base-url">
                <strong>Base URL:</strong> ${baseUrl}
            </div>
        </div>

        <div class="section">
            <h2>🔐 Authentication</h2>
            <div class="auth-box">
                <h4>🔑 API Key Authentication</h4>
                <p>All API requests require a valid API key to be included in the request header.</p>
                <div class="code-block">Authorization: Bearer YOUR_API_KEY</div>
                <p><strong>Note:</strong> Contact your system administrator to obtain an API key with appropriate permissions.</p>
            </div>
        </div>

        <div class="section">
            <h2>📚 Endpoints</h2>
            
            <div class="endpoint">
                <div class="endpoint-header">
                    <span class="method get">GET</span>
                    <span class="endpoint-path">/api/external/categories</span>
                </div>
                <div class="endpoint-body">
                    <p><strong>Description:</strong> Retrieve all available client categories</p>
                    <p><strong>Required Permission:</strong> <code>read:categories</code></p>
                    
                    <h4>📥 Response Example</h4>
                    <div class="response-example">
                        <div class="code-block">{
  "success": true,
  "data": [
    {
      "id": 1,
      "name": "Telkom",
      "description": "Telkom clients and services",
      "isArchived": false
    },
    {
      "id": 2,
      "name": "MTN Mobile (GSM)",
      "description": "MTN Mobile GSM clients",
      "isArchived": false
    }
  ]
}</div>
                    </div>
                    
                    <h4>💻 Try it now</h4>
                    <div class="code-block">curl -H "Authorization: Bearer YOUR_API_KEY" \\
     ${baseUrl}/api/external/categories</div>
                </div>
            </div>

            <div class="endpoint">
                <div class="endpoint-header">
                    <span class="method get">GET</span>
                    <span class="endpoint-path">/api/external/accounts/:category</span>
                </div>
                <div class="endpoint-body">
                    <p><strong>Description:</strong> Retrieve all accounts for a specific category</p>
                    <p><strong>Required Permission:</strong> <code>read:accounts</code></p>
                    
                    <h4>📋 Parameters</h4>
                    <div class="parameters">
                        <table>
                            <thead>
                                <tr>
                                    <th>Parameter</th>
                                    <th>Type</th>
                                    <th>Required</th>
                                    <th>Description</th>
                                </tr>
                            </thead>
                            <tbody>
                                <tr>
                                    <td><code>category</code></td>
                                    <td>Path</td>
                                    <td>✅ Yes</td>
                                    <td>Category name (e.g., "Telkom", "MTN Mobile (GSM)")</td>
                                </tr>
                                <tr>
                                    <td><code>limit</code></td>
                                    <td>Query</td>
                                    <td>❌ No</td>
                                    <td>Number of results to return (default: 1000)</td>
                                </tr>
                                <tr>
                                    <td><code>offset</code></td>
                                    <td>Query</td>
                                    <td>❌ No</td>
                                    <td>Number of results to skip (default: 0)</td>
                                </tr>
                            </tbody>
                        </table>
                    </div>
                    
                    <h4>📥 Response Example</h4>
                    <div class="response-example">
                        <div class="code-block">{
  "success": true,
  "data": {
    "accounts": [
      {
        "id": 1,
        "name": "Client Name",
        "email": "client@example.com",
        "category": "Telkom",
        "serviceDetails": "Service information",
        "accountNumber": "12345",
        "status": "Active",
        "contactInfo": "Contact details",
        "address": "Physical address",
        "notes": "Additional notes",
        "msisdn": "0821234567",
        "agent": "Agent name",
        "telkomHandle": "handle123",
        "fup": "50",
        "simType": "Standard SIM",
        "simSerialNumber": "89270123456789",
        "status2": "Secondary status",
        "comments": "Comments",
        "isReseller": false,
        "createdAt": "2025-01-01T00:00:00.000Z",
        "updatedAt": "2025-01-01T00:00:00.000Z"
      }
    ],
    "pagination": {
      "total": 1,
      "limit": 1000,
      "offset": 0,
      "hasMore": false
    }
  }
}</div>
                    </div>
                    
                    <h4>💻 Try it now</h4>
                    <div class="code-block">curl -H "Authorization: Bearer YOUR_API_KEY" \\
     "${baseUrl}/api/external/accounts/Telkom?limit=10&offset=0"</div>
                </div>
            </div>
        </div>

        <div class="section">
            <h2>🔴 Error Handling</h2>
            <p>The API uses standard HTTP status codes to indicate the success or failure of requests.</p>
            
            <div class="error-codes">
                <div class="error-code">
                    <strong>401 Unauthorized</strong><br>
                    Invalid or missing API key
                </div>
                <div class="error-code">
                    <strong>403 Forbidden</strong><br>
                    Insufficient permissions for the requested resource
                </div>
                <div class="error-code">
                    <strong>404 Not Found</strong><br>
                    Resource not found
                </div>
                <div class="error-code">
                    <strong>500 Internal Server Error</strong><br>
                    Server error occurred
                </div>
            </div>
            
            <h4>Error Response Format</h4>
            <div class="code-block">{
  "success": false,
  "error": "Detailed error message"
}</div>
        </div>

        <div class="section">
            <h2>📝 Special Notes</h2>
            <h3>🔍 FUP Field for Telkom Clients</h3>
            <p>For clients in the "Telkom" category, the API includes a special <code>fup</code> field that represents the Fair Usage Policy limit in gigabytes (GB). This field is optional but provides important service limitation information.</p>
            
            <h3>🔄 Pagination</h3>
            <p>Large datasets are paginated for performance. Use the <code>limit</code> and <code>offset</code> parameters to navigate through results. The response includes pagination metadata to help you determine if more data is available.</p>
            
            <h3>📊 Data Categories</h3>
            <p>The system supports multiple telecommunications categories including:</p>
            <ul style="margin: 15px 0; padding-left: 30px;">
                <li>Telkom</li>
                <li>MTN Mobile (GSM)</li>
                <li>MTN Fixed</li>
                <li>Axxess (MTN Fixed)</li>
                <li>Axxess (Telkom)</li>
                <li>Axxess (Fibre Lines)</li>
                <li>OpenServe (Fibre Lines)</li>
            </ul>
        </div>

        <div class="section">
            <h2>🚀 Getting Started</h2>
            <ol style="margin: 15px 0; padding-left: 30px;">
                <li><strong>Obtain API Key:</strong> Contact your system administrator to get an API key with appropriate permissions</li>
                <li><strong>Test Authentication:</strong> Use the categories endpoint to verify your API key works</li>
                <li><strong>Retrieve Categories:</strong> Get the list of available categories to understand your data structure</li>
                <li><strong>Fetch Account Data:</strong> Use the accounts endpoint with specific categories to retrieve client information</li>
            </ol>
            
            <p style="margin-top: 20px;"><strong>Need help?</strong> Contact your system administrator for API key management and technical support.</p>
        </div>
    </div>
</body>
</html>`;
    
    res.setHeader('Content-Type', 'text/html');
    res.send(html);
  });

  // Activity Logs API routes
  app.get("/api/activity-logs", requireAuth, async (req, res) => {
    try {
      // Only admin users can view activity logs
      if (req.session.user?.role !== "admin") {
        return res.status(403).json({ error: "Admin access required" });
      }

      const { page = "1", limit = "50", search, action, user: userId } = req.query as Record<string, string>;
      const offset = (parseInt(page) - 1) * parseInt(limit);

      let whereConditions: string[] = [];
      let queryParams: any[] = [];
      let paramIndex = 1;

      // Build WHERE conditions
      if (search) {
        whereConditions.push(`(al.action ILIKE $${paramIndex} OR al.resource_name ILIKE $${paramIndex + 1} OR al.details ILIKE $${paramIndex + 2})`);
        const searchPattern = `%${search}%`;
        queryParams.push(searchPattern, searchPattern, searchPattern);
        paramIndex += 3;
      }

      if (action && action !== "all") {
        whereConditions.push(`al.action = $${paramIndex}`);
        queryParams.push(action);
        paramIndex++;
      }

      if (userId && userId !== "all") {
        whereConditions.push(`al.user_id = $${paramIndex}`);
        queryParams.push(parseInt(userId));
        paramIndex++;
      }

      const whereClause = whereConditions.length > 0 ? `WHERE ${whereConditions.join(" AND ")}` : "";

      // Get total count
      const countQuery = `
        SELECT COUNT(*) as total
        FROM activity_logs al
        LEFT JOIN users u ON al.user_id = u.id
        ${whereClause}
      `;
      
      const countResult = await storage.db.execute(countQuery, queryParams.slice(0, -2)); // Remove limit/offset params
      const total = parseInt(countResult.rows[0]?.total || "0");

      // Get paginated data
      const dataQuery = `
        SELECT 
          al.*,
          u.username
        FROM activity_logs al
        LEFT JOIN users u ON al.user_id = u.id
        ${whereClause}
        ORDER BY al.timestamp DESC
        LIMIT $${paramIndex} OFFSET $${paramIndex + 1}
      `;
      
      queryParams.push(parseInt(limit), offset);
      const dataResult = await storage.db.execute(dataQuery, queryParams);

      const logs = dataResult.rows.map((row: any) => ({
        id: row.id,
        userId: row.user_id,
        action: row.action,
        resourceType: row.resource_type,
        resourceId: row.resource_id,
        resourceName: row.resource_name,
        details: row.details,
        ipAddress: row.ip_address,
        userAgent: row.user_agent,
        timestamp: row.timestamp,
        user: { username: row.username }
      }));

      res.json({ logs, total, page: parseInt(page), limit: parseInt(limit) });
    } catch (error: any) {
      console.error("Get activity logs error:", error);
      res.status(500).json({ error: "Internal server error" });
    }
  });

  app.get("/api/users", requireAuth, async (req, res) => {
    try {
      // Only admin users can view user list
      if (req.session.user?.role !== "admin") {
        return res.status(403).json({ error: "Admin access required" });
      }

      const users = await storage.getAllUsers();
      res.json({ users });
    } catch (error: any) {
      console.error("Get users error:", error);
      res.status(500).json({ error: "Internal server error" });
    }
  });

  // MTN Fixed Admin Routes
  app.get("/api/admin/mtn-fixed-top15", requireAuth, async (req, res) => {
    try {
      if (req.session.user?.role !== "admin") {
        return res.status(403).json({ error: "Admin access required" });
      }

      const now = new Date();
      const year = now.getFullYear();
      const month = now.getMonth() + 1;

      console.log(`[MTN Fixed Top 15 Query] Fetching Top 15 MTN Fixed users...`);
      console.log(`[MTN Fixed Top 15 Query] Query period: ${year}-${month.toString().padStart(2, '0')}`);

      const topUsers = await storage.getMtnFixedTop15(year, month);
      
      console.log(`[MTN Fixed Top 15 Query] Found ${topUsers.length} users in Top 15 table`);

      const monthNames = [
        "January", "February", "March", "April", "May", "June",
        "July", "August", "September", "October", "November", "December"
      ];

      const transformedUsers = topUsers.map((user, index) => ({
        rank: index + 1,
        clientName: user.clientName,
        accountNumber: user.accountNumber,
        msisdn: user.msisdn,
        totalBytes: user.totalBytes,
        totalGB: (user.totalBytes / (1024 * 1024 * 1024)).toFixed(3),
        connectedTimeMinutes: user.connectedTimeMinutes
      }));

      console.log(`[MTN Fixed Top 15 Query] Returning ${transformedUsers.length} transformed users`);

      res.json({
        success: true,
        year,
        month,
        monthName: monthNames[month - 1],
        users: transformedUsers
      });
    } catch (error: any) {
      console.error("MTN Fixed Top 15 error:", error);
      res.status(500).json({ error: "Failed to fetch MTN Fixed top 15 data" });
    }
  });

  app.post("/api/admin/mtn-fixed-test", requireAuth, async (req, res) => {
    const logPrefix = "[MTN Fixed API Test]";
    try {
      console.log(`${logPrefix} =================================`);
      console.log(`${logPrefix} Starting OpenWeb API test for MTN Fixed accounts...`);
      console.log(`${logPrefix} User: ${req.session.user?.username} (ID: ${req.session.user?.id})`);
      console.log(`${logPrefix} Request timestamp: ${new Date().toISOString()}`);
      
      if (req.session.user?.role !== "admin") {
        console.log(`${logPrefix} ACCESS DENIED - User role: ${req.session.user?.role}`);
        return res.status(403).json({ error: "Admin access required" });
      }

      console.log(`${logPrefix} Admin access confirmed`);
      
      // Get ALL MTN Fixed usernames from database
      console.log(`${logPrefix} Calling getAllMtnFixedUsernames()...`);
      const mtnFixedUsernames = await storage.getAllMtnFixedUsernames();
      console.log(`${logPrefix} getAllMtnFixedUsernames() completed`);
      console.log(`${logPrefix} Raw result:`, mtnFixedUsernames);
      console.log(`${logPrefix} Found ${mtnFixedUsernames.length} MTN Fixed accounts in database`);
      
      if (mtnFixedUsernames.length === 0) {
        return res.json({
          success: false,
          message: "No MTN Fixed accounts found in database",
          accountsTested: 0,
          timestamp: new Date().toISOString()
        });
      }

      // Test with first account to verify API connectivity
      const testAccount = mtnFixedUsernames[0];
      console.log(`${logPrefix} Testing API connectivity with account: ${testAccount}`);
      
      try {
        const now = new Date();
        const year = now.getFullYear();
        const month = now.getMonth() + 1;
        
        // Test OpenWeb API call
        const openWebAuth = OpenWebAuth.getInstance();
        const response = await openWebAuth.makeAuthenticatedRequest(
          `http://api.openweb.live:3000/proxy/monthUsage?usernames=${encodeURIComponent(testAccount)}&year=${year}&month=${month}`
        );
        
        console.log(`${logPrefix} API test successful - status: ${response.status}`);
        
        res.json({
          success: true,
          message: "MTN Fixed API test completed successfully",
          accountsTested: 1,
          totalMtnFixedAccounts: mtnFixedUsernames.length,
          testAccount: testAccount,
          apiStatus: response.status,
          timestamp: new Date().toISOString()
        });
        
      } catch (apiError: any) {
        console.error(`${logPrefix} API test failed:`, apiError.message);
        res.json({
          success: false,
          message: "MTN Fixed API test failed",
          error: apiError.message,
          accountsTested: 0,
          totalMtnFixedAccounts: mtnFixedUsernames.length,
          timestamp: new Date().toISOString()
        });
      }
      
    } catch (error: any) {
      console.error(`${logPrefix} TEST ERROR - Full details:`, {
        message: error.message,
        stack: error.stack,
        name: error.name,
        timestamp: new Date().toISOString()
      });
      res.status(500).json({ 
        error: "Failed to run MTN Fixed API test",
        details: error.message,
        timestamp: new Date().toISOString()
      });
    }
  });

  app.post("/api/admin/mtn-fixed-sync", requireAuth, async (req, res) => {
    const logPrefix = "[MTN Fixed Sync & Top 15]";
    try {
      console.log(`${logPrefix} =================================`);
      console.log(`${logPrefix} Starting MTN Fixed sync & top 15 for current month...`);
      console.log(`${logPrefix} User: ${req.session.user?.username} (ID: ${req.session.user?.id})`);
      console.log(`${logPrefix} Request timestamp: ${new Date().toISOString()}`);
      
      if (req.session.user?.role !== "admin") {
        console.log(`${logPrefix} ACCESS DENIED - User role: ${req.session.user?.role}`);
        return res.status(403).json({ error: "Admin access required" });
      }

      console.log(`${logPrefix} Admin access confirmed`);
      
      // Get ALL MTN Fixed usernames (Account Numbers)
      console.log(`${logPrefix} Calling getAllMtnFixedUsernames()...`);
      const mtnFixedUsernames = await storage.getAllMtnFixedUsernames();
      console.log(`${logPrefix} getAllMtnFixedUsernames() completed`);
      console.log(`${logPrefix} Raw result:`, mtnFixedUsernames);
      console.log(`${logPrefix} Processing ${mtnFixedUsernames.length} MTN Fixed accounts for sync & top 15`);
      
      if (mtnFixedUsernames.length === 0) {
        return res.json({
          success: false,
          message: "No MTN Fixed accounts found to sync",
          processed: 0,
          timestamp: new Date().toISOString()
        });
      }

      const openWebAuth = OpenWebAuth.getInstance();
      let processedCount = 0;
      let errorCount = 0;
      const errors: string[] = [];
      
      // Get current month details
      const now = new Date();
      const currentYear = now.getFullYear();
      const currentMonth = now.getMonth() + 1;
      
      console.log(`${logPrefix} Syncing current month data for ${currentYear}-${currentMonth}`);
      
      // First clear existing Top 15 data for current month
      await storage.clearMtnMobileTop15(currentYear, currentMonth);
      console.log(`${logPrefix} Cleared existing Top 15 data for ${currentYear}-${currentMonth}`);
      
      // Process ALL MTN Fixed accounts for current month
      for (const accountNumber of mtnFixedUsernames) {
        try {
          console.log(`${logPrefix} Processing MTN Fixed account: ${accountNumber}`);
          
          const response = await openWebAuth.makeAuthenticatedRequest(
            `http://api.openweb.live:3000/proxy/monthUsage?usernames=${encodeURIComponent(accountNumber)}&year=${currentYear}&month=${currentMonth}`
          );
          
          if (response.ok) {
            const data = await response.json();
            if (data && data.data && data.data.length > 0) {
              const usage = data.data[0];
              const totalBytes = parseInt(usage.charged_quantity) || 0;
              
              // Save as MTN Statistics (for usage display)
              await storage.saveMtnStatistics({
                username: accountNumber,
                year: currentYear,
                month: currentMonth,
                msisdn: usage.MSISDSN || null,
                totalBytes: totalBytes,
                connectedTimeMinutes: 0 // Will be calculated if needed
              });
              
              // Save as Top 15 entry (for leaderboard)
              await storage.saveMtnMobileTop15Stats({
                year: currentYear,
                month: currentMonth,
                accountNumber: accountNumber,
                clientName: `MTN Fixed Client`, // Will be updated with real name if available
                msisdn: usage.MSISDSN || null,
                totalBytes: totalBytes,
                connectedTimeMinutes: 0,
                rankPosition: 0 // Will be calculated during retrieval
              });
              
              processedCount++;
            }
          } else {
            errorCount++;
            errors.push(`${accountNumber}: API Error ${response.status}`);
          }
        } catch (error: any) {
          errorCount++;
          errors.push(`${accountNumber}: ${error.message}`);
        }
      }
      
      console.log(`${logPrefix} Sync completed - Processed: ${processedCount}, Errors: ${errorCount}`);
      
      res.json({
        success: true,
        message: `MTN Fixed sync completed - ${processedCount} accounts processed`,
        processed: processedCount,
        errors: errorCount,
        totalAccounts: mtnFixedUsernames.length,
        errorDetails: errors.length > 0 ? errors.slice(0, 10) : undefined, // Show first 10 errors
        timestamp: new Date().toISOString()
      });
      
    } catch (error: any) {
      console.error(`${logPrefix} SYNC ERROR:`, error);
      res.status(500).json({ 
        error: "Failed to sync MTN Fixed data",
        details: error.message,
        timestamp: new Date().toISOString()
      });
    }
  });

  const httpServer = createServer(app);
  return httpServer;
}

// Background sync function for MTN Mobile Daily Usage
async function syncMtnMobileDailyUsage(sessionId: string, accounts: string[]) {
  const logPrefix = `[Daily Usage Sync ${sessionId}]`;
  console.log(`${logPrefix} Starting background sync for ${accounts.length} accounts`);
  console.log(`${logPrefix} First 10 accounts to sync:`, accounts.slice(0, 10));
  
  // Validate all accounts before starting
  const invalidAccounts = accounts.filter(account => !account.includes('@mobile.is.co.za'));
  if (invalidAccounts.length > 0) {
    console.error(`${logPrefix} Found ${invalidAccounts.length} accounts with invalid format:`, invalidAccounts);
  }
  
  try {
    const currentDate = new Date();
    const year = currentDate.getFullYear();
    const month = currentDate.getMonth() + 1;
    const currentDay = currentDate.getDate();
    
    console.log(`${logPrefix} Sync period: ${year}-${month.toString().padStart(2, '0')} up to day ${currentDay}`);
    
    let totalDaysToProcess = 0;
    let processedDays = 0;
    let processedAccounts = 0;
    const errors: string[] = [];
    
    // First pass: calculate total days to process for progress tracking
    for (const account of accounts) {
      const lastSyncDate = await storage.getLastSyncDate(account);
      
      let startDay = 1;
      if (lastSyncDate && lastSyncDate.year === year && lastSyncDate.month === month) {
        startDay = lastSyncDate.day + 1; // Start from next day after last sync
      }
      
      if (startDay <= currentDay) {
        totalDaysToProcess += (currentDay - startDay + 1);
      }
    }
    
    await storage.updateSyncProgress(sessionId, {
      totalDays: totalDaysToProcess,
      currentStatus: `Syncing ${accounts.length} accounts (${totalDaysToProcess} total days)`
    });
    
    // Second pass: actual sync processing
    for (let accountIndex = 0; accountIndex < accounts.length; accountIndex++) {
      const account = accounts[accountIndex];
      const accountLogPrefix = `${logPrefix} [Account ${accountIndex + 1}/${accounts.length}: ${account}]`;
      
      // Check for cancellation before processing each account
      const currentProgress = await storage.getSyncProgress(sessionId);
      if (currentProgress?.isCancelled) {
        console.log(`${accountLogPrefix} Sync cancelled by user, stopping...`);
        await storage.updateSyncProgress(sessionId, {
          currentStatus: "Sync cancelled by user",
          isCompleted: true,
          completedAt: new Date()
        });
        return;
      }
      
      try {
        console.log(`${accountLogPrefix} Starting sync...`);
        
        await storage.updateSyncProgress(sessionId, {
          currentAccount: account,
          currentStatus: `Processing account ${account} (${accountIndex + 1}/${accounts.length})`
        });
        
        // Get last sync date for intelligent incremental sync
        const lastSyncDate = await storage.getLastSyncDate(account);
        
        let startDay = 1;
        if (lastSyncDate && lastSyncDate.year === year && lastSyncDate.month === month) {
          startDay = lastSyncDate.day + 1; // Start from next day after last sync
          console.log(`${accountLogPrefix} Last sync: ${lastSyncDate.year}-${lastSyncDate.month}-${lastSyncDate.day}, starting from day ${startDay}`);
        } else {
          console.log(`${accountLogPrefix} No previous sync found, starting from day 1`);
        }
        
        // Skip if already fully synced
        if (startDay > currentDay) {
          console.log(`${accountLogPrefix} Already fully synced, skipping`);
          processedAccounts++;
          continue;
        }
        
        // Sync each day from startDay to currentDay
        for (let day = startDay; day <= currentDay; day++) {
          const dayLogPrefix = `${accountLogPrefix} [Day ${day}]`;
          
          // Check for cancellation before processing each day
          const dayProgress = await storage.getSyncProgress(sessionId);
          if (dayProgress?.isCancelled) {
            console.log(`${dayLogPrefix} Sync cancelled by user, stopping...`);
            await storage.updateSyncProgress(sessionId, {
              currentStatus: "Sync cancelled by user",
              isCompleted: true,
              completedAt: new Date()
            });
            return;
          }
          
          try {
            console.log(`${dayLogPrefix} Fetching usage data...`);
            
            // Format parameters
            const formattedDay = day.toString().padStart(2, '0');
            const formattedMonth = month.toString().padStart(2, '0');
            
            // Construct API URL - accounts already include @mobile.is.co.za
            const cleanAccount = account.trim();
            
            // Validate account format
            if (!cleanAccount.includes('@')) {
              throw new Error(`Invalid account format: ${cleanAccount} (missing @ symbol)`);
            }
            
            // Double-check encoding
            const encodedAccount = encodeURIComponent(cleanAccount);
            const apiUrl = `http://api.openweb.live:3000/proxy/dayUsage?usernames=${encodedAccount}&year=${year}&month=${formattedMonth}&day=${formattedDay}`;
            
            console.log(`${dayLogPrefix} CALLING API:`);
            console.log(`${dayLogPrefix}   Original account: "${account}"`);
            console.log(`${dayLogPrefix}   Cleaned account: "${cleanAccount}"`);
            console.log(`${dayLogPrefix}   Encoded account: "${encodedAccount}"`);
            console.log(`${dayLogPrefix}   Full URL: ${apiUrl}`);
            
            // Add timeout control to prevent hanging
            const controller = new AbortController();
            const timeoutId = setTimeout(() => controller.abort(), 30000); // 30 second timeout
            
            let response;
            try {
              const openWebAuth = OpenWebAuth.getInstance();
              response = await openWebAuth.makeAuthenticatedRequest(apiUrl, {
                method: 'GET',
                signal: controller.signal,
              });
              clearTimeout(timeoutId);
            } catch (fetchError: any) {
              clearTimeout(timeoutId);
              if (fetchError.name === 'AbortError') {
                throw new Error(`API timeout after 30 seconds for ${account} day ${day}`);
              }
              throw fetchError;
            }
            
            console.log(`${dayLogPrefix} API response status: ${response.status}`);
            console.log(`${dayLogPrefix} API response headers:`, Object.fromEntries(response.headers.entries()));
            
            if (response.ok) {
              let apiResponse;
              let responseText = '';
              
              try {
                responseText = await response.text();
                console.log(`${dayLogPrefix} Raw API response text:`, responseText);
                apiResponse = JSON.parse(responseText);
                console.log(`${dayLogPrefix} Parsed API response:`, JSON.stringify(apiResponse, null, 2));
              } catch (parseError) {
                console.error(`${dayLogPrefix} Failed to parse API response as JSON:`, parseError);
                console.error(`${dayLogPrefix} Raw response was:`, responseText);
                
                await storage.upsertMtnMobileDailyUsage({
                  username: cleanAccount,
                  year: year,
                  month: month,
                  day: day,
                  date: `${year}-${formattedMonth}-${formattedDay}`,
                  usageBytes: 0,
                  usageGB: "0.000",
                  entries: 0,
                  success: false,
                  error: `JSON parse error: ${parseError.message}`
                });
                
                errors.push(`${account} Day ${day}: JSON parse error: ${parseError.message}`);
                processedDays++;
                continue;
              }
              
              if (apiResponse.ok && apiResponse.data && Array.isArray(apiResponse.data)) {
                let dayTotalBytes = 0;
                
                // Sum all charged_quantity values for the day
                apiResponse.data.forEach((entry) => {
                  const chargedQuantity = parseInt(entry.charged_quantity) || 0;
                  dayTotalBytes += chargedQuantity;
                });
                
                // Save to database
                await storage.upsertMtnMobileDailyUsage({
                  username: cleanAccount,
                  year: year,
                  month: month,
                  day: day,
                  date: `${year}-${formattedMonth}-${formattedDay}`,
                  usageBytes: dayTotalBytes,
                  usageGB: (dayTotalBytes / (1024 * 1024 * 1024)).toFixed(3),
                  entries: apiResponse.data.length,
                  success: true,
                  error: null
                });
                
                console.log(`${dayLogPrefix} SUCCESS: Saved ${dayTotalBytes} bytes (${(dayTotalBytes / (1024 * 1024 * 1024)).toFixed(3)} GB) from ${apiResponse.data.length} entries`);
              } else {
                // No data available for this day
                await storage.upsertMtnMobileDailyUsage({
                  username: cleanAccount,
                  year: year,
                  month: month,
                  day: day,
                  date: `${year}-${formattedMonth}-${formattedDay}`,
                  usageBytes: 0,
                  usageGB: "0.000",
                  entries: 0,
                  success: true,  // Changed to true since API call succeeded but no data
                  error: null
                });
                
                console.log(`${dayLogPrefix} SUCCESS: No usage data for this day (API response: ${JSON.stringify(apiResponse)})`);
              }
            } else {
              // API request failed - get detailed error information
              let errorResponseText = '';
              let detailedError = `HTTP ${response.status}: ${response.statusText}`;
              
              try {
                errorResponseText = await response.text();
                console.error(`${dayLogPrefix} API ERROR RESPONSE BODY:`, errorResponseText);
                
                // Try to parse error response as JSON for more details
                try {
                  const errorJson = JSON.parse(errorResponseText);
                  detailedError = `HTTP ${response.status}: ${JSON.stringify(errorJson)}`;
                } catch (parseError) {
                  detailedError = `HTTP ${response.status}: ${errorResponseText || response.statusText}`;
                }
              } catch (textError) {
                console.error(`${dayLogPrefix} Failed to read error response body:`, textError);
              }
              
              console.error(`${dayLogPrefix} DETAILED API ERROR:`, {
                status: response.status,
                statusText: response.statusText,
                url: apiUrl,
                account: cleanAccount,
                responseBody: errorResponseText,
                headers: Object.fromEntries(response.headers.entries())
              });
              
              await storage.upsertMtnMobileDailyUsage({
                username: cleanAccount,
                year: year,
                month: month,
                day: day,
                date: `${year}-${formattedMonth}-${formattedDay}`,
                usageBytes: 0,
                usageGB: "0.000",
                entries: 0,
                success: false,
                error: detailedError
              });
              
              console.error(`${dayLogPrefix} FAILED: ${detailedError}`);
              errors.push(`${account} Day ${day}: ${detailedError}`);
            }
            
            processedDays++;
            
            // Update progress
            await storage.updateSyncProgress(sessionId, {
              processedDays: processedDays,
              currentStatus: `Processing ${account} - Day ${day}/${currentDay} (${processedDays}/${totalDaysToProcess} total days)`
            });
            
            // Small delay to avoid overwhelming the API
            await new Promise(resolve => setTimeout(resolve, 200));
            
          } catch (dayError) {
            console.error(`${dayLogPrefix} CRITICAL DAY ERROR:`, {
              error: dayError,
              message: dayError.message,
              stack: dayError.stack,
              account: cleanAccount,
              day: day,
              apiUrl: apiUrl || 'URL not constructed'
            });
            
            // Save error to database with detailed information
            await storage.upsertMtnMobileDailyUsage({
              username: cleanAccount,
              year: year,
              month: month,
              day: day,
              date: `${year}-${formattedMonth}-${formattedDay}`,
              usageBytes: 0,
              usageGB: "0.000",
              entries: 0,
              success: false,
              error: `Critical error: ${dayError.message || "Unknown error"} | ${dayError.name || ""}`
            });
            
            errors.push(`${account} Day ${day}: Critical error - ${dayError.message || "Unknown error"}`);
            processedDays++;
          }
        }
        
        processedAccounts++;
        console.log(`${accountLogPrefix} Completed sync`);
        
      } catch (accountError) {
        console.error(`${accountLogPrefix} Error:`, accountError);
        errors.push(`${account}: ${accountError.message || "Unknown error"}`);
        processedAccounts++;
      }
    }
    
    // Mark sync as completed
    await storage.updateSyncProgress(sessionId, {
      processedAccounts: processedAccounts,
      processedDays: processedDays,
      currentAccount: null,
      currentStatus: `Sync completed! Processed ${processedAccounts} accounts and ${processedDays} days`,
      errors: errors,
      isCompleted: true
    });
    
    console.log(`${logPrefix} Sync completed. Processed: ${processedAccounts} accounts, ${processedDays} days. Errors: ${errors.length}`);
    
    // Clean up sync progress after some time (optional)
    setTimeout(async () => {
      try {
        await storage.deleteSyncProgress(sessionId);
        console.log(`${logPrefix} Cleaned up sync progress`);
      } catch (error) {
        console.error(`${logPrefix} Error cleaning up sync progress:`, error);
      }
    }, 300000); // Clean up after 5 minutes
    
  } catch (error) {
    console.error(`${logPrefix} Critical sync error:`, error);
    
    try {
      await storage.updateSyncProgress(sessionId, {
        currentStatus: `Sync failed: ${error.message || "Unknown error"}`,
        errors: [...errors, `Critical error: ${error.message || "Unknown error"}`],
        isCompleted: true
      });
    } catch (updateError) {
      console.error(`${logPrefix} Error updating sync progress with failure:`, updateError);
    }
  }
}

// Single account sync function with detailed debugging
async function syncSingleAccountDailyUsage(sessionId: string, accountNumber: string) {
  const logPrefix = `[Single Account Sync ${sessionId}]`;
  console.log(`${logPrefix} Starting sync for account: ${accountNumber}`);
  
  const debugInfo = {
    accountNumber: accountNumber,
    sessionId: sessionId,
    startTime: new Date().toISOString(),
    apiCalls: [],
    errors: [],
    summary: {
      totalDaysProcessed: 0,
      successfulDays: 0,
      failedDays: 0,
      totalBytesProcessed: 0,
      totalEntriesProcessed: 0
    }
  };
  
  try {
    // Clean account number
    const cleanAccount = accountNumber.trim();
    
    // Validate account format for both MTN Mobile and MTN Fixed
    if (!cleanAccount.includes('@')) {
      throw new Error(`Invalid account format: ${cleanAccount} (must contain @ symbol)`);
    }
    
    const currentDate = new Date();
    const year = currentDate.getFullYear();
    const month = currentDate.getMonth() + 1;
    const currentDay = currentDate.getDate();
    
    console.log(`${logPrefix} Sync period: ${year}-${month.toString().padStart(2, '0')} up to day ${currentDay}`);
    
    // Get last sync date for this account (for logging purposes)
    const lastSyncDate = await storage.getLastSyncDate(cleanAccount);
    
    // Always start from day 1 of the current month to ensure complete sync
    let startDay = 1;
    
    console.log(`${logPrefix} Starting from day ${startDay}, last sync: ${lastSyncDate ? `${lastSyncDate.year}-${lastSyncDate.month}-${lastSyncDate.day}` : 'never'}`);
    console.log(`${logPrefix} Syncing from 1st of current month to today (day ${currentDay})`);
    
    if (currentDay < 1) {
      console.log(`${logPrefix} No days to sync`);
      return {
        ok: true,
        message: "No days to sync",
        debugInfo: debugInfo
      };
    }
    
    // Process each day from startDay to currentDay
    for (let day = startDay; day <= currentDay; day++) {
      const dayLogPrefix = `${logPrefix} [Day ${day}]`;
      const formattedMonth = month.toString().padStart(2, '0');
      const formattedDay = day.toString().padStart(2, '0');
      
      console.log(`${dayLogPrefix} Fetching usage data...`);
      
      debugInfo.summary.totalDaysProcessed++;
      
      try {
        // Construct API URL
        const apiUrl = `http://api.openweb.live:3000/proxy/dayUsage?usernames=${encodeURIComponent(cleanAccount)}&year=${year}&month=${formattedMonth}&day=${formattedDay}`;
        
        const apiCallDebug = {
          day: day,
          url: apiUrl,
          originalAccount: accountNumber,
          cleanedAccount: cleanAccount,
          encodedAccount: encodeURIComponent(cleanAccount),
          requestTime: new Date().toISOString(),
          response: null,
          error: null,
          success: false
        };
        
        console.log(`${dayLogPrefix} CALLING API:`);
        console.log(`${dayLogPrefix}   Original account: "${accountNumber}"`);
        console.log(`${dayLogPrefix}   Cleaned account: "${cleanAccount}"`);
        console.log(`${dayLogPrefix}   Encoded account: "${encodeURIComponent(cleanAccount)}"`);
        console.log(`${dayLogPrefix}   Full URL: ${apiUrl}`);
        
        const openWebAuth = OpenWebAuth.getInstance();
        const response = await openWebAuth.makeAuthenticatedRequest(apiUrl);
        
        apiCallDebug.response = {
          status: response.status,
          statusText: response.statusText,
          headers: Object.fromEntries(response.headers.entries()),
          ok: response.ok
        };
        
        console.log(`${dayLogPrefix} API response status: ${response.status}`);
        console.log(`${dayLogPrefix} API response headers:`, Object.fromEntries(response.headers.entries()));
        
        if (response.ok) {
          const responseText = await response.text();
          console.log(`${dayLogPrefix} Raw API response text: ${responseText}`);
          
          apiCallDebug.response.rawText = responseText;
          
          let data;
          try {
            data = JSON.parse(responseText);
            apiCallDebug.response.parsedData = data;
            console.log(`${dayLogPrefix} Parsed API response:`, data);
          } catch (parseError) {
            console.error(`${dayLogPrefix} JSON parse error:`, parseError);
            apiCallDebug.error = `JSON parse error: ${parseError.message}`;
            debugInfo.errors.push(`Day ${day}: JSON parse error - ${parseError.message}`);
            debugInfo.summary.failedDays++;
            continue;
          }
          
          if (data && data.ok && data.data && Array.isArray(data.data)) {
            // Calculate total usage from all entries
            const totalUsageBytes = data.data.reduce((sum, entry) => {
              return sum + (parseInt(entry.charged_quantity) || 0);
            }, 0);
            
            const totalUsageGB = (totalUsageBytes / (1024 * 1024 * 1024)).toFixed(3);
            
            // Save to database
            await storage.upsertMtnMobileDailyUsage({
              username: cleanAccount,
              year: year,
              month: month,
              day: day,
              date: `${year}-${formattedMonth}-${formattedDay}`,
              usageBytes: totalUsageBytes,
              usageGB: totalUsageGB,
              entries: data.data.length,
              success: true,
              error: null
            });
            
            apiCallDebug.success = true;
            apiCallDebug.response.processedBytes = totalUsageBytes;
            apiCallDebug.response.processedGB = totalUsageGB;
            apiCallDebug.response.entriesCount = data.data.length;
            
            debugInfo.summary.successfulDays++;
            debugInfo.summary.totalBytesProcessed += totalUsageBytes;
            debugInfo.summary.totalEntriesProcessed += data.data.length;
            
            console.log(`${dayLogPrefix} SUCCESS: Saved ${totalUsageBytes} bytes (${totalUsageGB} GB) from ${data.data.length} entries`);
          } else {
            console.log(`${dayLogPrefix} No data or invalid response format`);
            apiCallDebug.error = "No data or invalid response format";
            debugInfo.errors.push(`Day ${day}: No data or invalid response format`);
            debugInfo.summary.failedDays++;
          }
        } else {
          let errorResponseText = "No response body";
          let detailedError = `HTTP ${response.status}: ${response.statusText}`;
          
          try {
            errorResponseText = await response.text();
            apiCallDebug.response.errorText = errorResponseText;
            console.error(`${dayLogPrefix} API ERROR RESPONSE BODY:`, errorResponseText);
            
            try {
              const errorJson = JSON.parse(errorResponseText);
              detailedError = `HTTP ${response.status}: ${JSON.stringify(errorJson)}`;
            } catch (parseError) {
              detailedError = `HTTP ${response.status}: ${errorResponseText || response.statusText}`;
            }
          } catch (textError) {
            console.error(`${dayLogPrefix} Failed to read error response body:`, textError);
          }
          
          apiCallDebug.error = detailedError;
          debugInfo.errors.push(`Day ${day}: ${detailedError}`);
          debugInfo.summary.failedDays++;
          
          console.error(`${dayLogPrefix} FAILED: ${detailedError}`);
          
          // Save error to database
          await storage.upsertMtnMobileDailyUsage({
            username: cleanAccount,
            year: year,
            month: month,
            day: day,
            date: `${year}-${formattedMonth}-${formattedDay}`,
            usageBytes: 0,
            usageGB: "0.000",
            entries: 0,
            success: false,
            error: detailedError
          });
        }
        
        debugInfo.apiCalls.push(apiCallDebug);
        
        // Small delay to avoid overwhelming the API
        await new Promise(resolve => setTimeout(resolve, 300));
        
      } catch (dayError) {
        console.error(`${dayLogPrefix} CRITICAL DAY ERROR:`, dayError);
        debugInfo.errors.push(`Day ${day}: Critical error - ${dayError.message}`);
        debugInfo.summary.failedDays++;
        
        // Save error to database
        await storage.upsertMtnMobileDailyUsage({
          username: cleanAccount,
          year: year,
          month: month,
          day: day,
          date: `${year}-${formattedMonth}-${formattedDay}`,
          usageBytes: 0,
          usageGB: "0.000",
          entries: 0,
          success: false,
          error: `Critical error: ${dayError.message || "Unknown error"}`
        });
      }
    }
    
    debugInfo.endTime = new Date().toISOString();
    debugInfo.summary.totalProcessingTime = new Date().getTime() - new Date(debugInfo.startTime).getTime();
    
    console.log(`${logPrefix} Single account sync completed`);
    console.log(`${logPrefix} Summary:`, debugInfo.summary);
    
    return {
      ok: true,
      message: `Successfully synced account ${cleanAccount}`,
      debugInfo: debugInfo
    };
    
  } catch (error) {
    console.error(`${logPrefix} Critical sync error:`, error);
    debugInfo.errors.push(`Critical error: ${error.message}`);
    debugInfo.endTime = new Date().toISOString();
    
    return {
      ok: false,
      error: error.message || "Unknown error",
      debugInfo: debugInfo
    };
  }

  // Activity Logs API routes
  app.get("/api/activity-logs", requireAuth, async (req, res) => {
    try {
      // Only admin users can view activity logs
      if (req.session.user?.role !== "admin") {
        return res.status(403).json({ error: "Admin access required" });
      }

      const { page = "1", limit = "50", search, action, user: userId } = req.query as Record<string, string>;
      const offset = (parseInt(page) - 1) * parseInt(limit);

      let whereConditions: string[] = [];
      let queryParams: any[] = [];
      let paramIndex = 1;

      // Build WHERE conditions
      if (search) {
        whereConditions.push(`(al.action ILIKE $${paramIndex} OR al.resource_name ILIKE $${paramIndex + 1} OR al.details ILIKE $${paramIndex + 2})`);
        const searchPattern = `%${search}%`;
        queryParams.push(searchPattern, searchPattern, searchPattern);
        paramIndex += 3;
      }

      if (action && action !== "all") {
        whereConditions.push(`al.action = $${paramIndex}`);
        queryParams.push(action);
        paramIndex++;
      }

      if (userId && userId !== "all") {
        whereConditions.push(`al.user_id = $${paramIndex}`);
        queryParams.push(parseInt(userId));
        paramIndex++;
      }

      const whereClause = whereConditions.length > 0 ? `WHERE ${whereConditions.join(" AND ")}` : "";

      // Get total count
      const countQuery = `
        SELECT COUNT(*) as total
        FROM activity_logs al
        LEFT JOIN users u ON al.user_id = u.id
        ${whereClause}
      `;
      
      const countResult = await storage.db.execute(countQuery, queryParams);
      const total = parseInt(countResult.rows[0]?.total || "0");

      // Get paginated results with user information
      const dataQuery = `
        SELECT al.*, u.username
        FROM activity_logs al
        LEFT JOIN users u ON al.user_id = u.id
        ${whereClause}
        ORDER BY al.timestamp DESC
        LIMIT $${paramIndex} OFFSET $${paramIndex + 1}
      `;
      
      queryParams.push(parseInt(limit), offset);
      const dataResult = await storage.db.execute(dataQuery, queryParams);

      const logs = dataResult.rows.map((row: any) => ({
        id: row.id,
        userId: row.user_id,
        action: row.action,
        resourceType: row.resource_type,
        resourceId: row.resource_id,
        resourceName: row.resource_name,
        details: row.details,
        ipAddress: row.ip_address,
        userAgent: row.user_agent,
        timestamp: row.timestamp,
        user: { username: row.username }
      }));

      res.json({ logs, total, page: parseInt(page), limit: parseInt(limit) });
    } catch (error) {
      console.error("Get activity logs error:", error);
      res.status(500).json({ error: "Internal server error" });
    }
  });

  app.get("/api/users", requireAuth, async (req, res) => {
    try {
      // Only admin users can view user list
      if (req.session.user?.role !== "admin") {
        return res.status(403).json({ error: "Admin access required" });
      }

      const users = await storage.getAllUsers();
      res.json({ users });
    } catch (error) {
      console.error("Get users error:", error);
      res.status(500).json({ error: "Internal server error" });
    }
  });

  // MTN Fixed Admin Routes
  app.get("/api/admin/mtn-fixed-top15", requireAuth, async (req, res) => {
    try {
      if (req.session.user?.role !== "admin") {
        return res.status(403).json({ error: "Admin access required" });
      }

      const now = new Date();
      const year = now.getFullYear();
      const month = now.getMonth() + 1;

      console.log(`[MTN Fixed Top 15 Query] Fetching Top 15 MTN Fixed users...`);
      console.log(`[MTN Fixed Top 15 Query] Query period: ${year}-${month.toString().padStart(2, '0')}`);

      const topUsers = await storage.getMtnFixedTop15(year, month);
      
      console.log(`[MTN Fixed Top 15 Query] Found ${topUsers.length} users in Top 15 table`);

      const monthNames = [
        "January", "February", "March", "April", "May", "June",
        "July", "August", "September", "October", "November", "December"
      ];

      const transformedUsers = topUsers.map((user, index) => ({
        rank: index + 1,
        clientName: user.clientName,
        accountNumber: user.accountNumber,
        msisdn: user.msisdn,
        totalBytes: user.totalBytes,
        totalGB: (user.totalBytes / (1024 * 1024 * 1024)).toFixed(3),
        connectedTimeMinutes: user.connectedTimeMinutes
      }));

      console.log(`[MTN Fixed Top 15 Query] Returning ${transformedUsers.length} transformed users`);

      res.json({
        success: true,
        year,
        month,
        monthName: monthNames[month - 1],
        users: transformedUsers
      });
    } catch (error) {
      console.error("MTN Fixed Top 15 error:", error);
      res.status(500).json({ error: "Failed to fetch MTN Fixed top 15 data" });
    }
  });

  app.get("/api/admin/mtn-fixed-bottom30", requireAuth, async (req, res) => {
    try {
      if (req.session.user?.role !== "admin") {
        return res.status(403).json({ error: "Admin access required" });
      }

      const now = new Date();
      const year = now.getFullYear();
      const month = now.getMonth() + 1;

      console.log(`[MTN Fixed Bottom 30 Query] Fetching Bottom 30 MTN Fixed users...`);
      console.log(`[MTN Fixed Bottom 30 Query] Query period: ${year}-${month.toString().padStart(2, '0')}`);

      const bottomUsers = await storage.getMtnFixedBottom30(year, month);
      
      console.log(`[MTN Fixed Bottom 30 Query] Found ${bottomUsers.length} users in Bottom 30 table`);

      const monthNames = [
        "January", "February", "March", "April", "May", "June",
        "July", "August", "September", "October", "November", "December"
      ];

      const transformedUsers = bottomUsers.map((user, index) => ({
        rank: index + 1,
        clientName: user.clientName,
        accountNumber: user.accountNumber,
        msisdn: user.msisdn,
        totalBytes: user.totalBytes,
        totalGB: (user.totalBytes / (1024 * 1024 * 1024)).toFixed(3),
        connectedTimeMinutes: user.connectedTimeMinutes
      }));

      console.log(`[MTN Fixed Bottom 30 Query] Returning ${transformedUsers.length} transformed users`);

      res.json({
        success: true,
        year,
        month,
        monthName: monthNames[month - 1],
        users: transformedUsers
      });
    } catch (error) {
      console.error("MTN Fixed Bottom 30 error:", error);
      res.status(500).json({ error: "Failed to fetch MTN Fixed bottom 30 data" });
    }
  });

  app.post("/api/admin/mtn-fixed-sync", requireAuth, async (req, res) => {
    const logPrefix = "[MTN Fixed Sync & Top 15]";
    try {
      console.log(`${logPrefix} =================================`);
      console.log(`${logPrefix} Starting MTN Fixed sync & top 15 for current month...`);
      console.log(`${logPrefix} User: ${req.session.user?.username} (ID: ${req.session.user?.id})`);
      console.log(`${logPrefix} Request timestamp: ${new Date().toISOString()}`);
      
      if (req.session.user?.role !== "admin") {
        console.log(`${logPrefix} ACCESS DENIED - User role: ${req.session.user?.role}`);
        return res.status(403).json({ error: "Admin access required" });
      }

      console.log(`${logPrefix} Admin access confirmed`);
      
      // Get ALL MTN Fixed usernames (Account Numbers)
      console.log(`${logPrefix} Calling getAllMtnFixedUsernames()...`);
      const mtnFixedUsernames = await storage.getAllMtnFixedUsernames();
      console.log(`${logPrefix} getAllMtnFixedUsernames() completed`);
      console.log(`${logPrefix} Raw result:`, mtnFixedUsernames);
      console.log(`${logPrefix} Processing ${mtnFixedUsernames.length} MTN Fixed accounts for sync & top 15`);
      
      if (mtnFixedUsernames.length === 0) {
        return res.json({
          success: false,
          message: "No MTN Fixed accounts found to sync",
          processed: 0,
          timestamp: new Date().toISOString()
        });
      }

      const openWebAuth = OpenWebAuth.getInstance();
      let processedCount = 0;
      let errorCount = 0;
      const errors: string[] = [];
      
      // Get current month details
      const now = new Date();
      const currentYear = now.getFullYear();
      const currentMonth = now.getMonth() + 1;
      
      console.log(`${logPrefix} Syncing current month data for ${currentYear}-${currentMonth}`);
      
      // First clear existing Top 15 data for current month
      await storage.clearMtnMobileTop15(currentYear, currentMonth);
      console.log(`${logPrefix} Cleared existing Top 15 data for ${currentYear}-${currentMonth}`);
      
      // Process ALL MTN Fixed accounts for current month
      for (const accountNumber of mtnFixedUsernames) {
        try {
          console.log(`${logPrefix} Processing MTN Fixed account: ${accountNumber}`);
          
          const response = await openWebAuth.makeAuthenticatedRequest(
            `http://api.openweb.live:3000/proxy/monthUsage?usernames=${encodeURIComponent(accountNumber)}&year=${currentYear}&month=${currentMonth}`
          );
          
          const data = await response.json();
          
          if (data && data.data && data.data.length > 0) {
            const stats = data.data[0];
            
            // Save usage statistics to database
            await storage.saveMtnStatistics({
              username: accountNumber,
              year: currentYear,
              month: currentMonth,
              totalBytes: stats.totalBytes || 0,
              connectedTimeMinutes: stats.connectedTimeMinutes || 0,
              updatedAt: new Date().toISOString()
            });
            
            // Also save to Top 15 table for ranking
            await storage.saveMtnMobileTop15Stats({
              accountNumber: accountNumber,
              clientName: `MTN Fixed - ${accountNumber}`, // Placeholder client name
              msisdn: accountNumber,
              year: currentYear,
              month: currentMonth,
              totalBytes: stats.totalBytes || 0,
              connectedTimeMinutes: stats.connectedTimeMinutes || 0,
              rankPosition: 0, // Will be calculated later
              updatedAt: new Date().toISOString()
            });
            
            console.log(`${logPrefix} Saved MTN Fixed stats for ${accountNumber}: ${(stats.totalBytes / (1024*1024*1024)).toFixed(2)}GB`);
          } else {
            console.log(`${logPrefix} No data returned for ${accountNumber}`);
          }
          
          processedCount++;
          
        } catch (accountError: any) {
          console.error(`${logPrefix} Error processing MTN Fixed account ${accountNumber}:`, accountError.message);
          errorCount++;
          errors.push(`${accountNumber}: ${accountError.message}`);
        }
      }
      
      console.log(`${logPrefix} Sync completed - Processed: ${processedCount}, Errors: ${errorCount}`);
      
      res.json({
        success: true,
        message: `MTN Fixed sync completed - ${processedCount} accounts processed`,
        processed: processedCount,
        errors: errorCount,
        totalAccounts: mtnFixedUsernames.length,
        errorDetails: errors.length > 0 ? errors.slice(0, 10) : undefined, // Show first 10 errors
        timestamp: new Date().toISOString()
      });
      
    } catch (error: any) {
      console.error(`${logPrefix} SYNC ERROR:`, error);
      res.status(500).json({ 
        error: "Failed to sync MTN Fixed data",
        details: error.message,
        timestamp: new Date().toISOString()
      });
    }
  });

  app.post("/api/admin/mtn-fixed-sync-3months", requireAuth, async (req, res) => {
    const logPrefix = "[MTN Fixed 3 Months Sync]";
    try {
      console.log(`${logPrefix} =================================`);
      console.log(`${logPrefix} Starting MTN Fixed 3 months sync...`);
      console.log(`${logPrefix} Request timestamp: ${new Date().toISOString()}`);
      console.log(`${logPrefix} Session user:`, req.session?.user);
      
      if (req.session.user?.role !== "admin") {
        console.log(`${logPrefix} ACCESS DENIED - User role: ${req.session.user?.role}`);
        return res.status(403).json({ error: "Admin access required" });
      }

      console.log(`${logPrefix} Admin access confirmed`);
      console.log(`${logPrefix} User: ${req.session.user?.username} (ID: ${req.session.user?.id})`);
      console.log(`${logPrefix} Session ID: ${req.session.id}`);
      
      // Get ALL MTN Fixed usernames (Account Numbers)
      console.log(`${logPrefix} Fetching MTN Fixed usernames from database...`);
      const mtnFixedUsernames = await storage.getAllMtnFixedUsernames();
      console.log(`${logPrefix} Retrieved ${mtnFixedUsernames.length} MTN Fixed accounts from database`);
      
      if (mtnFixedUsernames.length === 0) {
        console.log(`${logPrefix} No MTN Fixed accounts found - returning early`);
        const emptyResponse = {
          success: false,
          message: "No MTN Fixed accounts found to sync",
          processed: 0,
          monthsProcessed: 0,
          timestamp: new Date().toISOString()
        };
        console.log(`${logPrefix} Returning response:`, emptyResponse);
        return res.json(emptyResponse);
      }

      console.log(`${logPrefix} Initializing OpenWeb authentication...`);
      const openWebAuth = OpenWebAuth.getInstance();
      let processedCount = 0;
      let errorCount = 0;
      const errors: string[] = [];
      
      // Get current date and calculate 3 months back
      const now = new Date();
      const currentYear = now.getFullYear();
      const currentMonth = now.getMonth() + 1;
      
      const months = [];
      for (let i = 0; i < 3; i++) {
        const monthDate = new Date(currentYear, currentMonth - 1 - i, 1);
        months.push({
          year: monthDate.getFullYear(),
          month: monthDate.getMonth() + 1
        });
      }
      
      console.log(`${logPrefix} Syncing ${months.length} months: ${months.map(m => `${m.year}-${m.month}`).join(', ')}`);
      
      // Process ALL MTN Fixed accounts for 3 months using Account Number as username
      for (const accountNumber of mtnFixedUsernames) {
        try {
          console.log(`${logPrefix} Processing MTN Fixed account: ${accountNumber}`);
          
          for (const { year, month } of months) {
            try {
              const response = await openWebAuth.makeAuthenticatedRequest(
                `http://api.openweb.live:3000/proxy/monthUsage?usernames=${encodeURIComponent(accountNumber)}&year=${year}&month=${month}`
              );
              
              const data = await response.json();
              
              if (data && data.data && data.data.length > 0) {
                // Save usage statistics to database (MTN Fixed shares same statistics table)
                const stats = data.data[0];
                await storage.saveMtnStatistics({
                  username: accountNumber,
                  year: year,
                  month: month,
                  totalBytes: stats.totalBytes || 0,
                  connectedTimeMinutes: stats.connectedTimeMinutes || 0,
                  updatedAt: new Date().toISOString()
                });
                
                console.log(`${logPrefix} Saved MTN Fixed stats for ${accountNumber} - ${year}-${month}: ${(stats.totalBytes / (1024*1024*1024)).toFixed(2)}GB`);
              } else {
                console.log(`${logPrefix} No data returned for ${accountNumber} - ${year}-${month}`);
              }
            } catch (monthError: any) {
              console.error(`${logPrefix} Error processing ${accountNumber} for ${year}-${month}:`, monthError.message);
              errors.push(`${accountNumber} (${year}-${month}): ${monthError.message}`);
            }
          }
          
          processedCount++;
          
        } catch (accountError: any) {
          console.error(`${logPrefix} Error processing MTN Fixed account ${accountNumber}:`, accountError.message);
          errorCount++;
          errors.push(`${accountNumber}: ${accountError.message}`);
        }
      }
      
      console.log(`${logPrefix} 3 months sync completed - Processed: ${processedCount}, Errors: ${errorCount}`);
      
      const finalResponse = {
        success: true,
        message: `MTN Fixed 3 months sync completed - ${processedCount} accounts processed`,
        processed: processedCount,
        errors: errorCount,
        monthsProcessed: 3,
        totalAccounts: mtnFixedUsernames.length,
        errorDetails: errors.length > 0 ? errors.slice(0, 10) : undefined, // Show first 10 errors
        timestamp: new Date().toISOString()
      };
      
      console.log(`${logPrefix} Returning final response:`, finalResponse);
      res.json(finalResponse);
      
    } catch (error: any) {
      console.error(`${logPrefix} =================================`);
      console.error(`${logPrefix} CRITICAL ERROR OCCURRED:`, error);
      console.error(`${logPrefix} Error type:`, error.constructor.name);
      console.error(`${logPrefix} Error message:`, error.message);
      console.error(`${logPrefix} Error stack:`, error.stack);
      console.error(`${logPrefix} =================================`);
      
      const errorResponse = {
        error: "Failed to run MTN Fixed 3 months sync",
        details: error.message,
        timestamp: new Date().toISOString()
      };
      
      console.error(`${logPrefix} Returning error response:`, errorResponse);
      res.status(500).json(errorResponse);
    }
  });

  app.post("/api/admin/mtn-fixed-sync-daily", requireAuth, async (req, res) => {
    const logPrefix = "[MTN Fixed Daily Sync]";
    try {
      if (req.session.user?.role !== "admin") {
        console.log(`${logPrefix} ACCESS DENIED - User role: ${req.session.user?.role}`);
        return res.status(403).json({ error: "Admin access required" });
      }

      console.log(`${logPrefix} Starting MTN Fixed daily usage sync for current month...`);
      console.log(`${logPrefix} User: ${req.session.user?.username} (ID: ${req.session.user?.id})`);
      
      // Get ALL MTN Fixed usernames (Account Numbers)
      const mtnFixedUsernames = await storage.getAllMtnFixedUsernames();
      console.log(`${logPrefix} Processing ${mtnFixedUsernames.length} MTN Fixed accounts for daily usage sync`);
      
      if (mtnFixedUsernames.length === 0) {
        return res.json({
          success: false,
          message: "No MTN Fixed accounts found to sync",
          processed: 0,
          timestamp: new Date().toISOString()
        });
      }

      const openWebAuth = OpenWebAuth.getInstance();
      let processedCount = 0;
      let errorCount = 0;
      const errors: string[] = [];
      
      // Get current month details
      const now = new Date();
      const currentYear = now.getFullYear();
      const currentMonth = now.getMonth() + 1;
      const currentDay = now.getDate();
      
      console.log(`${logPrefix} Syncing daily usage for ${currentYear}-${currentMonth} (up to day ${currentDay})`);
      
      // Process ALL MTN Fixed accounts for current month daily usage
      for (const accountNumber of mtnFixedUsernames) {
        try {
          console.log(`${logPrefix} Processing MTN Fixed account daily usage: ${accountNumber}`);
          
          // Sync all days from 1st to current day
          for (let day = 1; day <= currentDay; day++) {
            try {
              const response = await openWebAuth.makeAuthenticatedRequest(
                `http://api.openweb.live:3000/proxy/dayUsage?usernames=${encodeURIComponent(accountNumber)}&year=${currentYear}&month=${currentMonth}&day=${day}`
              );
              
              const data = await response.json();
              
              if (data && data.data && data.data.length > 0) {
                // Save daily usage to database (similar to MTN Mobile daily usage)
                const dayStats = data.data[0];
                await storage.saveMtnMobileDailyUsage({
                  accountNumber: accountNumber,
                  year: currentYear,
                  month: currentMonth,
                  day: day,
                  usageBytes: dayStats.totalBytes || 0,
                  updatedAt: new Date().toISOString()
                });
                
                console.log(`${logPrefix} Saved MTN Fixed daily usage for ${accountNumber} - ${currentYear}-${currentMonth}-${day}: ${(dayStats.totalBytes / (1024*1024*1024)).toFixed(3)}GB`);
              }
            } catch (dayError: any) {
              console.error(`${logPrefix} Error processing ${accountNumber} for ${currentYear}-${currentMonth}-${day}:`, dayError.message);
              errors.push(`${accountNumber} (${currentYear}-${currentMonth}-${day}): ${dayError.message}`);
            }
          }
          
          processedCount++;
          
        } catch (accountError: any) {
          console.error(`${logPrefix} Error processing MTN Fixed account ${accountNumber}:`, accountError.message);
          errorCount++;
          errors.push(`${accountNumber}: ${accountError.message}`);
        }
      }
      
      console.log(`${logPrefix} Daily usage sync completed - Processed: ${processedCount}, Errors: ${errorCount}`);
      
      res.json({
        success: true,
        message: `MTN Fixed daily usage sync completed - ${processedCount} accounts processed`,
        processed: processedCount,
        errors: errorCount,
        daysProcessed: currentDay,
        totalAccounts: mtnFixedUsernames.length,
        errorDetails: errors.length > 0 ? errors.slice(0, 10) : undefined, // Show first 10 errors
        timestamp: new Date().toISOString()
      });
      
    } catch (error: any) {
      console.error(`${logPrefix} SYNC ERROR:`, error);
      res.status(500).json({ 
        error: "Failed to run MTN Fixed daily usage sync",
        details: error.message,
        timestamp: new Date().toISOString()
      });
    }
  });

  app.post("/api/admin/mtn-fixed-test", requireAuth, async (req, res) => {
    const logPrefix = "[MTN Fixed API Test]";
    try {
      console.log(`${logPrefix} =================================`);
      console.log(`${logPrefix} Starting OpenWeb API test for MTN Fixed accounts...`);
      console.log(`${logPrefix} User: ${req.session.user?.username} (ID: ${req.session.user?.id})`);
      console.log(`${logPrefix} Request timestamp: ${new Date().toISOString()}`);
      
      if (req.session.user?.role !== "admin") {
        console.log(`${logPrefix} ACCESS DENIED - User role: ${req.session.user?.role}`);
        return res.status(403).json({ error: "Admin access required" });
      }

      console.log(`${logPrefix} Admin access confirmed`);
      
      // Get ALL MTN Fixed usernames from database
      console.log(`${logPrefix} Calling getAllMtnFixedUsernames()...`);
      const mtnFixedUsernames = await storage.getAllMtnFixedUsernames();
      console.log(`${logPrefix} getAllMtnFixedUsernames() completed`);
      console.log(`${logPrefix} Raw result:`, mtnFixedUsernames);
      console.log(`${logPrefix} Found ${mtnFixedUsernames.length} MTN Fixed accounts in database`);
      
      if (mtnFixedUsernames.length === 0) {
        return res.json({
          success: false,
          message: "No MTN Fixed accounts found in database",
          accountsTested: 0,
          timestamp: new Date().toISOString()
        });
      }

      // Test with first account to verify API connectivity
      const testAccount = mtnFixedUsernames[0];
      console.log(`${logPrefix} Testing API connectivity with account: ${testAccount}`);
      
      try {
        const now = new Date();
        const year = now.getFullYear();
        const month = now.getMonth() + 1;
        
        // Test OpenWeb API call
        const openWebAuth = OpenWebAuth.getInstance();
        const response = await openWebAuth.makeAuthenticatedRequest(
          `http://api.openweb.live:3000/proxy/monthUsage?usernames=${encodeURIComponent(testAccount)}&year=${year}&month=${month}`
        );
        
        console.log(`${logPrefix} API test successful - status: ${response.status}`);
        
        res.json({
          success: true,
          message: "MTN Fixed API test completed successfully",
          accountsTested: 1,
          totalMtnFixedAccounts: mtnFixedUsernames.length,
          testAccount: testAccount,
          apiStatus: response.status,
          timestamp: new Date().toISOString()
        });
        
      } catch (apiError: any) {
        console.error(`${logPrefix} API test failed:`, apiError.message);
        res.json({
          success: false,
          message: "MTN Fixed API test failed",
          error: apiError.message,
          accountsTested: 0,
          totalMtnFixedAccounts: mtnFixedUsernames.length,
          timestamp: new Date().toISOString()
        });
      }
      
    } catch (error: any) {
      console.error(`${logPrefix} TEST ERROR - Full details:`, {
        message: error.message,
        stack: error.stack,
        name: error.name,
        timestamp: new Date().toISOString()
      });
      res.status(500).json({ 
        error: "Failed to run MTN Fixed API test",
        details: error.message,
        timestamp: new Date().toISOString()
      });
    }
  });

  const server = createServer(app);
  return server;
}
