// @ts-nocheck
import { clients, simswaps, users, categories, mtnStatistics, recentlyViewed, mtnMobileTop15, staffNotifications, mtnMobileDailyUsage, mtnMobileSyncProgress, apiKeys, type Client, type InsertClient, type Simswap, type InsertSimswap, type ClientWithSimswaps, type TelkomClient, type User, type InsertUser, type Category, type MtnStatistics, type InsertMtnStatistics, type RecentlyViewed, type InsertRecentlyViewed, type MtnMobileTop15, type InsertMtnMobileTop15, type StaffNotification, type InsertStaffNotification, type MtnMobileDailyUsage, type InsertMtnMobileDailyUsage, type MtnMobileSyncProgress, type InsertMtnMobileSyncProgress, type ApiKey, type InsertApiKey } from "@shared/schema";
import { db, pool } from "./db";
import { eq, and, desc, asc, ilike, or, count, sql } from "drizzle-orm";

export interface IStorage {
  // Client operations
  getClients(search?: string, category?: string, limit?: number, offset?: number): Promise<ClientWithSimswaps[]>;
  getClientsCount(search?: string, category?: string): Promise<number>;
  getClient(id: number): Promise<ClientWithSimswaps | undefined>;
  getTelkomClients(page?: number, limit?: number): Promise<TelkomClient[]>;
  getTelkomClientsCount(): Promise<number>;
  createClient(client: InsertClient): Promise<Client>;
  updateClient(id: number, client: Partial<InsertClient>): Promise<Client>;
  deleteClient(id: number): Promise<void>;
  
  // Simswap operations
  getSimswaps(clientId?: number): Promise<Simswap[]>;
  getSimswap(id: number): Promise<Simswap | undefined>;
  createSimswap(simswap: InsertSimswap): Promise<Simswap>;
  updateSimswap(id: number, simswap: Partial<InsertSimswap>): Promise<Simswap>;
  deleteSimswap(id: number): Promise<void>;
  
  // Statistics
  getStats(): Promise<{
    totalClients: number;
    telkomClients: number;
    activeSimswaps: number;
    totalDataUsage: number;
  }>;
  
  // User operations
  getUser(id: number): Promise<User | undefined>;
  getUserByUsername(username: string): Promise<User | undefined>;
  createUser(user: InsertUser): Promise<User>;
  updateUser(id: number, user: Partial<InsertUser>): Promise<User>;
  deleteUser(id: number): Promise<void>;
  getUsers(): Promise<User[]>;
  getAllUsers(): Promise<User[]>;
  
  // Database access for activity logs
  db: typeof db;
  
  // Category operations
  getCategories(): Promise<Category[]>;
  getActiveCategories(): Promise<Category[]>;
  
  // MTN Statistics operations
  saveMtnStatistics(stats: InsertMtnStatistics): Promise<MtnStatistics>;
  getMtnStatistics(username: string, year: number, month: number): Promise<MtnStatistics | undefined>;
  getMtnClientsWithNoData(year: number, month: number): Promise<{ username: string; name: string; msisdn: string }[]>;
  getAllMtnFixedUsernames(): Promise<string[]>;
  
  // MTN Mobile Top 15 operations
  saveMtnMobileTop15Stats(stats: InsertMtnMobileTop15): Promise<MtnMobileTop15>;
  getMtnMobileTop15(year: number, month: number): Promise<{ accountNumber: string; clientName: string; msisdn: string; totalBytes: number; connectedTimeMinutes: number; rankPosition: number }[]>;
  getMtnMobileBottom30(year: number, month: number): Promise<{ accountNumber: string; clientName: string; msisdn: string; totalBytes: number; connectedTimeMinutes: number; rankPosition: number }[]>;
  clearMtnMobileTop15(year: number, month: number): Promise<void>;
  
  // MTN Fixed Top 15 operations (reusing same table structure)
  getMtnFixedTop15(year: number, month: number): Promise<{ accountNumber: string; clientName: string; msisdn: string; totalBytes: number; connectedTimeMinutes: number; rankPosition: number }[]>;
  getMtnFixedBottom30(year: number, month: number): Promise<{ accountNumber: string; clientName: string; msisdn: string; totalBytes: number; connectedTimeMinutes: number; rankPosition: number }[]>;
  
  // Recently viewed operations
  trackClientView(clientId: number, userId: number): Promise<void>;
  getRecentlyViewedClients(limit?: number): Promise<ClientWithSimswaps[]>;
  
  // Staff notification operations
  getStaffNotifications(limit?: number): Promise<StaffNotification[]>;
  getStaffNotification(id: number): Promise<StaffNotification | undefined>;
  createStaffNotification(notification: InsertStaffNotification): Promise<StaffNotification>;
  updateStaffNotification(id: number, notification: Partial<InsertStaffNotification>): Promise<StaffNotification>;
  deleteStaffNotification(id: number): Promise<void>;
  getActiveStaffNotifications(): Promise<StaffNotification[]>;
  
  // MTN Mobile Daily Usage operations
  getMtnMobileDailyUsage(username: string, year: number, month: number): Promise<MtnMobileDailyUsage[]>;
  saveMtnMobileDailyUsage(usage: InsertMtnMobileDailyUsage): Promise<MtnMobileDailyUsage>;
  upsertMtnMobileDailyUsage(usage: InsertMtnMobileDailyUsage): Promise<MtnMobileDailyUsage>;
  getLastSyncDate(username: string): Promise<{year: number, month: number, day: number} | null>;
  getAllMtnMobileAccounts(): Promise<string[]>;
  
  // MTN Mobile Sync Progress operations
  createSyncProgress(progress: InsertMtnMobileSyncProgress): Promise<MtnMobileSyncProgress>;
  
  // API Key operations
  getApiKeys(): Promise<ApiKey[]>;
  getApiKey(id: number): Promise<ApiKey | undefined>;
  getApiKeyByHash(hash: string): Promise<ApiKey | undefined>;
  createApiKey(apiKey: InsertApiKey): Promise<ApiKey>;
  updateApiKey(id: number, apiKey: Partial<InsertApiKey>): Promise<ApiKey>;
  deleteApiKey(id: number): Promise<void>;
  updateApiKeyLastUsed(id: number): Promise<void>;
  updateSyncProgress(sessionId: string, updates: Partial<InsertMtnMobileSyncProgress>): Promise<MtnMobileSyncProgress>;
  getSyncProgress(sessionId: string): Promise<MtnMobileSyncProgress | undefined>;
  deleteSyncProgress(sessionId: string): Promise<void>;
}

export class DatabaseStorage implements IStorage {
  async getClients(search?: string, category?: string, limit = 50, offset = 0): Promise<ClientWithSimswaps[]> {
    try {
      let baseQuery = db.select().from(clients);
      
      const conditions = [];
      if (search) {
        conditions.push(
          or(
            ilike(clients.name, `%${search}%`),
            ilike(clients.contactInfo, `%${search}%`),
            ilike(clients.email, `%${search}%`),
            ilike(clients.msisdn, `%${search}%`),
            ilike(clients.simSerialNumber, `%${search}%`),
            ilike(clients.accountNumber, `%${search}%`)
          )
        );
      }
      if (category && category !== 'All Categories') {
        conditions.push(eq(clients.category, category));
      }
      
      let query = baseQuery;
      if (conditions.length > 0) {
        query = baseQuery.where(and(...conditions));
      }
      
      const clientsData = await query
        .orderBy(desc(clients.createdAt))
        .limit(limit)
        .offset(offset);
      
      const clientsWithSimswaps: ClientWithSimswaps[] = [];
      const processedIds = new Set<number>();
      
      for (const client of clientsData) {
        if (processedIds.has(client.id)) continue;
        
        // For Telkom secondary SIMs, also include the primary SIM
        if (client.category === 'Telkom' && !client.isPrimarySim && client.primarySimId) {
          const primarySim = await db.select().from(clients)
            .where(eq(clients.id, client.primarySimId))
            .limit(1);
          
          if (primarySim.length > 0) {
            const primaryClient = primarySim[0];
            const primarySimswaps = await db.select().from(simswaps).where(eq(simswaps.clientId, primaryClient.id));
            clientsWithSimswaps.push({ 
              ...primaryClient, 
              simswaps: primarySimswaps as Simswap[]
            });
            processedIds.add(primaryClient.id);
          }
        }
        
        const clientSimswaps = await db.select().from(simswaps).where(eq(simswaps.clientId, client.id));
        
        // For secondary SIMs, attach primary SIM info for display
        let primarySimInfo = null;
        if (client.category === 'Telkom' && !client.isPrimarySim && client.primarySimId) {
          const primarySim = await db.select().from(clients)
            .where(eq(clients.id, client.primarySimId))
            .limit(1);
          if (primarySim.length > 0) {
            primarySimInfo = {
              msisdn: primarySim[0].msisdn,
              accountNumber: primarySim[0].accountNumber,
              name: primarySim[0].name
            };
          }
        }
        
        clientsWithSimswaps.push({ 
          ...client, 
          simswaps: clientSimswaps as Simswap[],
          primarySimInfo
        });
        processedIds.add(client.id);
      }
      
      return clientsWithSimswaps;
    } catch (error) {
      console.error('Error fetching clients:', error);
      throw error;
    }
  }

  async getClientsCount(search?: string, category?: string): Promise<number> {
    try {
      let baseQuery = db.select({ count: count() }).from(clients);
      
      const conditions = [];
      if (search) {
        conditions.push(
          or(
            ilike(clients.name, `%${search}%`),
            ilike(clients.contactInfo, `%${search}%`),
            ilike(clients.email, `%${search}%`),
            ilike(clients.msisdn, `%${search}%`),
            ilike(clients.simSerialNumber, `%${search}%`),
            ilike(clients.accountNumber, `%${search}%`)
          )
        );
      }
      if (category && category !== 'All Categories') {
        conditions.push(eq(clients.category, category));
      }
      
      if (conditions.length > 0) {
        baseQuery = baseQuery.where(and(...conditions));
      }
      
      const [result] = await baseQuery;
      return result.count;
    } catch (error) {
      console.error('Error counting clients:', error);
      throw error;
    }
  }

  async getClient(id: number): Promise<ClientWithSimswaps | undefined> {
    try {
      const [client] = await db.select().from(clients).where(eq(clients.id, id));
      if (!client) return undefined;
      
      const clientSimswaps = await db.select().from(simswaps).where(eq(simswaps.clientId, id));
      
      // Add primary SIM info for secondary SIMs
      let primarySimInfo = null;
      if (client.category === 'Telkom' && !client.isPrimarySim && client.primarySimId) {
        const primarySim = await db.select().from(clients)
          .where(eq(clients.id, client.primarySimId))
          .limit(1);
        if (primarySim.length > 0) {
          primarySimInfo = {
            msisdn: primarySim[0].msisdn,
            accountNumber: primarySim[0].accountNumber,
            name: primarySim[0].name
          };
        }
      }
      
      return { 
        ...client, 
        simswaps: clientSimswaps as Simswap[],
        primarySimInfo
      };
    } catch (error) {
      console.error('Error fetching client:', error);
      throw error;
    }
  }

  async getTelkomClients(page = 1, limit = 10): Promise<TelkomClient[]> {
    try {
      // Get only primary SIMs for pagination (1 primary + 9 secondaries = 1 page unit)
      const offset = (page - 1) * limit;
      const primaryClients = await db.select().from(clients)
        .where(and(
          eq(clients.category, 'Telkom'),
          eq(clients.isPrimarySim, true)
        ))
        .limit(limit)
        .offset(offset);
      
      const telkomClientsWithSims: TelkomClient[] = [];
      
      for (const primaryClient of primaryClients) {
        const primarySim = {
          simNumber: primaryClient.simSerialNumber || '',
          phoneNumber: primaryClient.msisdn || '',
          status: primaryClient.status || 'Active',
          dataPlan: primaryClient.serviceDetails || ''
        };

        // Find secondary SIMs linked to this primary
        const secondaryClients = await db.select().from(clients)
          .where(and(
            eq(clients.category, 'Telkom'),
            eq(clients.primarySimId, primaryClient.id),
            eq(clients.isPrimarySim, false)
          ));

        const secondarySims = secondaryClients.map(sec => ({
          id: sec.id,
          simNumber: sec.simSerialNumber || '',
          phoneNumber: sec.msisdn || '',
          msisdn: sec.msisdn || '',
          status: sec.status || 'Active',
          dataPlan: sec.serviceDetails || '',
          name: sec.name || '',
          email: sec.email || '',
          serviceDetails: sec.serviceDetails || '',
          accountNumber: sec.accountNumber || '',
          status2: sec.status2 || '',
          category: sec.category || '',
          isReseller: sec.isReseller || false
        }));

        telkomClientsWithSims.push({
          ...primaryClient,
          primarySim,
          secondarySims
        });
      }
      
      return telkomClientsWithSims;
    } catch (error) {
      console.error('Error fetching Telkom clients:', error);
      throw error;
    }
  }

  async getTelkomClientsCount(): Promise<number> {
    try {
      // Count only primary SIMs for Telkom clients
      const [result] = await db.select({ count: count() }).from(clients)
        .where(and(
          eq(clients.category, 'Telkom'),
          eq(clients.isPrimarySim, true)
        ));
      return result.count;
    } catch (error) {
      console.error('Error counting Telkom clients:', error);
      throw error;
    }
  }

  async createClient(insertClient: InsertClient): Promise<Client> {
    try {
      const [client] = await db
        .insert(clients)
        .values(insertClient)
        .returning();
      return client;
    } catch (error) {
      console.error('Error creating client:', error);
      throw error;
    }
  }

  async updateClient(id: number, updateData: Partial<InsertClient>): Promise<Client> {
    try {
      const [client] = await db
        .update(clients)
        .set(updateData)
        .where(eq(clients.id, id))
        .returning();
      return client;
    } catch (error) {
      console.error('Error updating client:', error);
      throw error;
    }
  }

  async deleteClient(id: number): Promise<void> {
    try {
      // Delete associated simswaps first
      await db.delete(simswaps).where(eq(simswaps.clientId, id));
      // Delete client
      await db.delete(clients).where(eq(clients.id, id));
    } catch (error) {
      console.error('Error deleting client:', error);
      throw error;
    }
  }

  async getSimswaps(clientId?: number): Promise<Simswap[]> {
    try {
      if (clientId) {
        return await db.select().from(simswaps).where(eq(simswaps.clientId, clientId));
      }
      return await db.select().from(simswaps);
    } catch (error) {
      console.error('Error fetching simswaps:', error);
      throw error;
    }
  }

  async getSimswap(id: number): Promise<Simswap | undefined> {
    try {
      const [simswap] = await db.select().from(simswaps).where(eq(simswaps.id, id));
      return simswap || undefined;
    } catch (error) {
      console.error('Error fetching simswap:', error);
      throw error;
    }
  }

  async createSimswap(insertSimswap: InsertSimswap): Promise<Simswap> {
    try {
      const [simswap] = await db
        .insert(simswaps)
        .values(insertSimswap)
        .returning();
      return simswap;
    } catch (error) {
      console.error('Error creating simswap:', error);
      throw error;
    }
  }

  async updateSimswap(id: number, updateData: Partial<InsertSimswap>): Promise<Simswap> {
    try {
      const [simswap] = await db
        .update(simswaps)
        .set(updateData)
        .where(eq(simswaps.id, id))
        .returning();
      return simswap;
    } catch (error) {
      console.error('Error updating simswap:', error);
      throw error;
    }
  }

  async deleteSimswap(id: number): Promise<void> {
    try {
      await db.delete(simswaps).where(eq(simswaps.id, id));
    } catch (error) {
      console.error('Error deleting simswap:', error);
      throw error;
    }
  }

  async getStats(): Promise<{
    totalClients: number;
    telkomClients: number;
    activeSimswaps: number;
    totalDataUsage: number;
  }> {
    try {
      const totalClientsResult = await db.select({ count: count() }).from(clients);
      const telkomClientsResult = await db.select({ count: count() }).from(clients).where(eq(clients.category, 'Telkom'));
      const activeSimswapsResult = await db.select({ count: count() }).from(simswaps).where(eq(simswaps.status, 'completed'));
      
      // Calculate data usage estimate from service details
      const allClients = await db.select().from(clients);
      const totalDataUsage = allClients.reduce((total, client) => {
        if (client.serviceDetails) {
          const planSize = parseInt(client.serviceDetails.replace(/\D/g, '')) || 0;
          return total + planSize;
        }
        return total;
      }, 0) / 1000; // Convert to TB
      
      return {
        totalClients: totalClientsResult[0]?.count || 0,
        telkomClients: telkomClientsResult[0]?.count || 0,
        activeSimswaps: activeSimswapsResult[0]?.count || 0,
        totalDataUsage: Math.round(totalDataUsage * 10) / 10
      };
    } catch (error) {
      console.error('Error fetching stats:', error);
      return {
        totalClients: 0,
        telkomClients: 0,
        activeSimswaps: 0,
        totalDataUsage: 0
      };
    }
  }

  // User operations
  async getUser(id: number): Promise<User | undefined> {
    try {
      const [user] = await db.select().from(users).where(eq(users.id, id));
      return user || undefined;
    } catch (error) {
      console.error('Error fetching user:', error);
      throw error;
    }
  }

  async getUserByUsername(username: string): Promise<User | undefined> {
    try {
      const [user] = await db.select().from(users).where(eq(users.username, username));
      return user || undefined;
    } catch (error) {
      console.error('Error fetching user by username:', error);
      throw error;
    }
  }

  async createUser(insertUser: InsertUser): Promise<User> {
    try {
      const [user] = await db
        .insert(users)
        .values({
          ...insertUser,
          updatedAt: new Date()
        })
        .returning();
      return user;
    } catch (error) {
      console.error('Error creating user:', error);
      throw error;
    }
  }

  async updateUser(id: number, updateData: Partial<InsertUser>): Promise<User> {
    try {
      const [user] = await db
        .update(users)
        .set({
          ...updateData,
          updatedAt: new Date()
        })
        .where(eq(users.id, id))
        .returning();
      return user;
    } catch (error) {
      console.error('Error updating user:', error);
      throw error;
    }
  }

  async deleteUser(id: number): Promise<void> {
    try {
      await db.delete(users).where(eq(users.id, id));
    } catch (error) {
      console.error('Error deleting user:', error);
      throw error;
    }
  }

  async getUsers(): Promise<User[]> {
    try {
      return await db.select().from(users).orderBy(desc(users.createdAt));
    } catch (error) {
      console.error('Error fetching users:', error);
      throw error;
    }
  }

  async getCategories(): Promise<Category[]> {
    try {
      return await db.select().from(categories).orderBy(categories.name);
    } catch (error) {
      console.error('Error fetching categories:', error);
      throw error;
    }
  }

  async getActiveCategories(): Promise<Category[]> {
    try {
      const result = await pool.query('SELECT * FROM categories ORDER BY name');
      return result.rows;
    } catch (error) {
      console.error('Error fetching active categories:', error);
      throw error;
    }
  }

  // MTN Statistics operations
  async saveMtnStatistics(stats: InsertMtnStatistics): Promise<MtnStatistics> {
    try {
      const [savedStats] = await db
        .insert(mtnStatistics)
        .values({
          ...stats,
          refreshedAt: new Date()
        })
        .onConflictDoUpdate({
          target: [mtnStatistics.username, mtnStatistics.year, mtnStatistics.month],
          set: {
            totalBytes: stats.totalBytes,
            connectedTimeMinutes: stats.connectedTimeMinutes,
            msisdn: stats.msisdn,
            refreshedAt: new Date(),
            updatedAt: new Date()
          }
        })
        .returning();
      return savedStats;
    } catch (error) {
      console.error('Error saving MTN statistics:', error);
      throw error;
    }
  }

  async getMtnStatistics(username: string, year: number, month: number): Promise<MtnStatistics | undefined> {
    try {
      const [stats] = await db
        .select()
        .from(mtnStatistics)
        .where(
          and(
            eq(mtnStatistics.username, username),
            eq(mtnStatistics.year, year),
            eq(mtnStatistics.month, month)
          )
        );
      return stats || undefined;
    } catch (error) {
      console.error('Error fetching MTN statistics:', error);
      throw error;
    }
  }

  async getMtnClientsWithNoData(year: number, month: number): Promise<{ username: string; name: string; msisdn: string }[]> {
    try {
      // Get all MTN Fixed clients and their usernames
      const mtnClients = await db
        .select({
          username: clients.accountNumber,
          name: clients.name,
          msisdn: clients.msisdn
        })
        .from(clients)
        .where(eq(clients.category, 'MTN Fixed'));

      // Get statistics for the specified month
      const statsForMonth = await db
        .select()
        .from(mtnStatistics)
        .where(
          and(
            eq(mtnStatistics.year, year),
            eq(mtnStatistics.month, month)
          )
        );

      // Filter clients with no data usage (0 bytes or no record)
      const clientsWithNoData = mtnClients.filter(client => {
        if (!client.username) return false;
        
        const clientStats = statsForMonth.find(stat => stat.username === client.username);
        return !clientStats || clientStats.totalBytes === 0;
      });

      return clientsWithNoData.map(client => ({
        username: client.username || '',
        name: client.name,
        msisdn: client.msisdn || ''
      }));
    } catch (error) {
      console.error('Error fetching MTN clients with no data:', error);
      throw error;
    }
  }

  async getAllMtnFixedUsernames(): Promise<string[]> {
    try {
      console.log('[getAllMtnFixedUsernames] Starting MTN Fixed username retrieval...');
      
      const mtnClients = await db
        .select({ 
          username: clients.accountNumber,
          name: clients.name,
          id: clients.id 
        })
        .from(clients)
        .where(eq(clients.category, 'MTN Fixed'));

      console.log(`[getAllMtnFixedUsernames] Found ${mtnClients.length} MTN Fixed clients in database`);
      
      if (mtnClients.length === 0) {
        console.log('[getAllMtnFixedUsernames] No MTN Fixed clients found in database');
        return [];
      }
      
      // Log first few clients for debugging
      mtnClients.slice(0, 3).forEach((client, index) => {
        console.log(`[getAllMtnFixedUsernames] Client ${index + 1}: ID=${client.id}, Name="${client.name}", Account="${client.username}"`);
      });

      const validUsernames = mtnClients
        .map(client => client.username)
        .filter(username => {
          if (!username || typeof username !== 'string') {
            console.log(`[getAllMtnFixedUsernames] Filtered out null/invalid username: "${username}"`);
            return false;
          }
          
          const trimmedUsername = username.trim();
          
          if (trimmedUsername === '') {
            console.log(`[getAllMtnFixedUsernames] Filtered out empty username: "${username}"`);
            return false;
          }
          
          // Filter out pure numbers (MSISDNs/phone numbers)
          if (/^\d+$/.test(trimmedUsername)) {
            console.log(`[getAllMtnFixedUsernames] Filtered out MSISDN/phone number: "${trimmedUsername}"`);
            return false;
          }
          
          // MTN Fixed accounts should contain @ symbol (like lungisa3@openwebwifi)
          if (!trimmedUsername.includes('@')) {
            console.log(`[getAllMtnFixedUsernames] Filtered out non-email format account: "${trimmedUsername}"`);
            return false;
          }
          
          return true;
        })
        .map(username => username.trim()) as string[];
      
      console.log(`[getAllMtnFixedUsernames] Returning ${validUsernames.length} valid MTN Fixed usernames`);
      
      // Log first few valid usernames for verification
      validUsernames.slice(0, 5).forEach((username, index) => {
        console.log(`[getAllMtnFixedUsernames] Valid Username ${index + 1}: "${username}"`);
      });
      
      return validUsernames;
    } catch (error) {
      console.error('[getAllMtnFixedUsernames] Error fetching MTN Fixed usernames:', error);
      throw error;
    }
  }

  // MTN Mobile Top 15 operations
  async saveMtnMobileTop15Stats(stats: InsertMtnMobileTop15): Promise<MtnMobileTop15> {
    try {
      const [savedStats] = await db
        .insert(mtnMobileTop15)
        .values({
          ...stats,
          syncTimestamp: new Date(),
          updatedAt: new Date()
        })
        .onConflictDoUpdate({
          target: [mtnMobileTop15.accountNumber, mtnMobileTop15.year, mtnMobileTop15.month],
          set: {
            clientName: stats.clientName,
            msisdn: stats.msisdn,
            totalBytes: stats.totalBytes,
            connectedTimeMinutes: stats.connectedTimeMinutes,
            rankPosition: stats.rankPosition,
            syncTimestamp: new Date(),
            updatedAt: new Date()
          }
        })
        .returning();
      return savedStats;
    } catch (error) {
      console.error('Error saving MTN Mobile Top 15 stats:', error);
      throw error;
    }
  }

  async getMtnMobileTop15(year: number, month: number): Promise<{ accountNumber: string; clientName: string; msisdn: string; totalBytes: number; connectedTimeMinutes: number; rankPosition: number }[]> {
    try {
      const result = await db
        .select({
          accountNumber: mtnMobileTop15.accountNumber,
          clientName: mtnMobileTop15.clientName,
          msisdn: mtnMobileTop15.msisdn,
          totalBytes: mtnMobileTop15.totalBytes,
          connectedTimeMinutes: mtnMobileTop15.connectedTimeMinutes,
          rankPosition: mtnMobileTop15.rankPosition
        })
        .from(mtnMobileTop15)
        .where(
          and(
            eq(mtnMobileTop15.year, year),
            eq(mtnMobileTop15.month, month)
          )
        )
        .orderBy(desc(mtnMobileTop15.totalBytes))
        .limit(15);

      return result.map(row => ({
        accountNumber: row.accountNumber,
        clientName: row.clientName,
        msisdn: row.msisdn || '',
        totalBytes: row.totalBytes,
        connectedTimeMinutes: row.connectedTimeMinutes,
        rankPosition: row.rankPosition || 0
      }));
    } catch (error) {
      console.error('Error fetching MTN Mobile top 15:', error);
      throw error;
    }
  }

  async getMtnMobileBottom30(year: number, month: number): Promise<{ accountNumber: string; clientName: string; msisdn: string; totalBytes: number; connectedTimeMinutes: number; rankPosition: number }[]> {
    try {
      const result = await db
        .select({
          accountNumber: mtnMobileTop15.accountNumber,
          clientName: mtnMobileTop15.clientName,
          msisdn: mtnMobileTop15.msisdn,
          totalBytes: mtnMobileTop15.totalBytes,
          connectedTimeMinutes: mtnMobileTop15.connectedTimeMinutes,
          rankPosition: mtnMobileTop15.rankPosition
        })
        .from(mtnMobileTop15)
        .where(
          and(
            eq(mtnMobileTop15.year, year),
            eq(mtnMobileTop15.month, month)
          )
        )
        .orderBy(asc(mtnMobileTop15.totalBytes))
        .limit(30);

      return result.map(row => ({
        accountNumber: row.accountNumber,
        clientName: row.clientName,
        msisdn: row.msisdn || '',
        totalBytes: row.totalBytes,
        connectedTimeMinutes: row.connectedTimeMinutes,
        rankPosition: row.rankPosition || 0
      }));
    } catch (error) {
      console.error('Error fetching MTN Mobile bottom 30:', error);
      throw error;
    }
  }

  async clearMtnMobileTop15(year: number, month: number): Promise<void> {
    try {
      await db.delete(mtnMobileTop15)
        .where(
          and(
            eq(mtnMobileTop15.year, year),
            eq(mtnMobileTop15.month, month)
          )
        );
    } catch (error) {
      console.error('Error clearing MTN Mobile top 15 data:', error);
      throw error;
    }
  }

  // MTN Fixed Top 15 operations (reusing same table with different data)
  async getMtnFixedTop15(year: number, month: number): Promise<{ accountNumber: string; clientName: string; msisdn: string; totalBytes: number; connectedTimeMinutes: number; rankPosition: number }[]> {
    try {
      // For now, return empty array until MTN Fixed sync is implemented
      // This uses the same table as MTN Mobile but would be populated with MTN Fixed data
      const result = await db
        .select({
          accountNumber: mtnMobileTop15.accountNumber,
          clientName: mtnMobileTop15.clientName,
          msisdn: mtnMobileTop15.msisdn,
          totalBytes: mtnMobileTop15.totalBytes,
          connectedTimeMinutes: mtnMobileTop15.connectedTimeMinutes,
          rankPosition: mtnMobileTop15.rankPosition
        })
        .from(mtnMobileTop15)
        .where(
          and(
            eq(mtnMobileTop15.year, year),
            eq(mtnMobileTop15.month, month),
            // Add condition to filter MTN Fixed accounts (contains @ but not @mobile.is.co.za)
            and(
              sql`${mtnMobileTop15.accountNumber} LIKE '%@%'`,
              sql`${mtnMobileTop15.accountNumber} NOT LIKE '%@mobile.is.co.za'`
            )
          )
        )
        .orderBy(desc(mtnMobileTop15.totalBytes))
        .limit(15);

      return result.map(row => ({
        accountNumber: row.accountNumber,
        clientName: row.clientName,
        msisdn: row.msisdn || '',
        totalBytes: row.totalBytes,
        connectedTimeMinutes: row.connectedTimeMinutes,
        rankPosition: row.rankPosition || 0
      }));
    } catch (error) {
      console.error('Error fetching MTN Fixed top 15:', error);
      throw error;
    }
  }

  async getMtnFixedBottom30(year: number, month: number): Promise<{ accountNumber: string; clientName: string; msisdn: string; totalBytes: number; connectedTimeMinutes: number; rankPosition: number }[]> {
    try {
      // For now, return empty array until MTN Fixed sync is implemented
      // This uses the same table as MTN Mobile but would be populated with MTN Fixed data
      const result = await db
        .select({
          accountNumber: mtnMobileTop15.accountNumber,
          clientName: mtnMobileTop15.clientName,
          msisdn: mtnMobileTop15.msisdn,
          totalBytes: mtnMobileTop15.totalBytes,
          connectedTimeMinutes: mtnMobileTop15.connectedTimeMinutes,
          rankPosition: mtnMobileTop15.rankPosition
        })
        .from(mtnMobileTop15)
        .where(
          and(
            eq(mtnMobileTop15.year, year),
            eq(mtnMobileTop15.month, month),
            // Add condition to filter MTN Fixed accounts (contains @ but not @mobile.is.co.za)
            and(
              sql`${mtnMobileTop15.accountNumber} LIKE '%@%'`,
              sql`${mtnMobileTop15.accountNumber} NOT LIKE '%@mobile.is.co.za'`
            )
          )
        )
        .orderBy(asc(mtnMobileTop15.totalBytes))
        .limit(30);

      return result.map(row => ({
        accountNumber: row.accountNumber,
        clientName: row.clientName,
        msisdn: row.msisdn || '',
        totalBytes: row.totalBytes,
        connectedTimeMinutes: row.connectedTimeMinutes,
        rankPosition: row.rankPosition || 0
      }));
    } catch (error) {
      console.error('Error fetching MTN Fixed bottom 30:', error);
      throw error;
    }
  }

  async trackClientView(clientId: number, userId: number): Promise<void> {
    try {
      // Remove any existing entry for this client and user combination
      await db.delete(recentlyViewed)
        .where(and(eq(recentlyViewed.clientId, clientId), eq(recentlyViewed.userId, userId)));
      
      // Insert new view record
      await db.insert(recentlyViewed).values({
        clientId,
        userId
      });
    } catch (error) {
      console.error("Error tracking client view:", error);
    }
  }

  async getRecentlyViewedClients(limit: number = 10): Promise<ClientWithSimswaps[]> {
    try {
      // Get recently viewed client IDs with join to get client data
      const recentViewsWithClients = await db
        .select({
          clientId: recentlyViewed.clientId,
          viewedAt: recentlyViewed.viewedAt,
          client: clients,
        })
        .from(recentlyViewed)
        .innerJoin(clients, eq(recentlyViewed.clientId, clients.id))
        .orderBy(desc(recentlyViewed.viewedAt))
        .limit(limit);

      // Get simswaps for each client
      const clientsWithSimswaps: ClientWithSimswaps[] = [];
      
      for (const view of recentViewsWithClients) {
        const clientSimswaps = await db.select()
          .from(simswaps)
          .where(eq(simswaps.clientId, view.client.id))
          .orderBy(desc(simswaps.requestedAt));

        clientsWithSimswaps.push({
          ...view.client,
          simswaps: clientSimswaps
        });
      }

      return clientsWithSimswaps;
    } catch (error) {
      console.error("Error fetching recently viewed clients:", error);
      return [];
    }
  }

  // Staff notification operations
  async getStaffNotifications(limit: number = 10): Promise<StaffNotification[]> {
    try {
      const notifications = await db
        .select()
        .from(staffNotifications)
        .orderBy(desc(staffNotifications.createdAt))
        .limit(limit);
      return notifications;
    } catch (error) {
      console.error('Error fetching staff notifications:', error);
      throw error;
    }
  }

  async getStaffNotification(id: number): Promise<StaffNotification | undefined> {
    try {
      const [notification] = await db
        .select()
        .from(staffNotifications)
        .where(eq(staffNotifications.id, id));
      return notification;
    } catch (error) {
      console.error('Error fetching staff notification:', error);
      throw error;
    }
  }

  async createStaffNotification(notification: InsertStaffNotification): Promise<StaffNotification> {
    try {
      const [createdNotification] = await db
        .insert(staffNotifications)
        .values({
          ...notification,
          createdAt: new Date(),
          updatedAt: new Date()
        })
        .returning();
      return createdNotification;
    } catch (error) {
      console.error('Error creating staff notification:', error);
      throw error;
    }
  }

  async updateStaffNotification(id: number, notification: Partial<InsertStaffNotification>): Promise<StaffNotification> {
    try {
      const [updatedNotification] = await db
        .update(staffNotifications)
        .set({
          ...notification,
          updatedAt: new Date()
        })
        .where(eq(staffNotifications.id, id))
        .returning();
      return updatedNotification;
    } catch (error) {
      console.error('Error updating staff notification:', error);
      throw error;
    }
  }

  async deleteStaffNotification(id: number): Promise<void> {
    try {
      await db
        .delete(staffNotifications)
        .where(eq(staffNotifications.id, id));
    } catch (error) {
      console.error('Error deleting staff notification:', error);
      throw error;
    }
  }

  async getActiveStaffNotifications(): Promise<StaffNotification[]> {
    try {
      const notifications = await db
        .select()
        .from(staffNotifications)
        .where(eq(staffNotifications.isActive, true))
        .orderBy(desc(staffNotifications.createdAt))
        .limit(3);
      return notifications;
    } catch (error) {
      console.error('Error fetching active staff notifications:', error);
      throw error;
    }
  }

  // MTN Mobile Daily Usage operations
  async getMtnMobileDailyUsage(username: string, year: number, month: number): Promise<MtnMobileDailyUsage[]> {
    try {
      const usage = await db.select()
        .from(mtnMobileDailyUsage)
        .where(and(
          eq(mtnMobileDailyUsage.username, username),
          eq(mtnMobileDailyUsage.year, year),
          eq(mtnMobileDailyUsage.month, month)
        ))
        .orderBy(mtnMobileDailyUsage.day);
      
      return usage;
    } catch (error) {
      console.error('Error fetching MTN Mobile daily usage:', error);
      throw error;
    }
  }

  async saveMtnMobileDailyUsage(usage: InsertMtnMobileDailyUsage): Promise<MtnMobileDailyUsage> {
    try {
      const [result] = await db.insert(mtnMobileDailyUsage).values(usage).returning();
      return result;
    } catch (error) {
      console.error('Error saving MTN Mobile daily usage:', error);
      throw error;
    }
  }

  async upsertMtnMobileDailyUsage(usage: InsertMtnMobileDailyUsage): Promise<MtnMobileDailyUsage> {
    try {
      // Try to update first
      const [updated] = await db.update(mtnMobileDailyUsage)
        .set({
          ...usage,
          lastSyncedAt: new Date(),
          updatedAt: new Date()
        })
        .where(and(
          eq(mtnMobileDailyUsage.username, usage.username),
          eq(mtnMobileDailyUsage.year, usage.year),
          eq(mtnMobileDailyUsage.month, usage.month),
          eq(mtnMobileDailyUsage.day, usage.day)
        ))
        .returning();
      
      if (updated) {
        return updated;
      }
      
      // If no rows updated, insert new record
      return await this.saveMtnMobileDailyUsage(usage);
    } catch (error) {
      // If update fails, try to insert
      try {
        return await this.saveMtnMobileDailyUsage(usage);
      } catch (insertError) {
        console.error('Error upserting MTN Mobile daily usage:', error);
        throw error;
      }
    }
  }

  async getLastSyncDate(username: string): Promise<{year: number, month: number, day: number} | null> {
    try {
      const [lastSync] = await db.select({
        year: mtnMobileDailyUsage.year,
        month: mtnMobileDailyUsage.month,
        day: mtnMobileDailyUsage.day
      })
      .from(mtnMobileDailyUsage)
      .where(eq(mtnMobileDailyUsage.username, username))
      .orderBy(desc(mtnMobileDailyUsage.year), desc(mtnMobileDailyUsage.month), desc(mtnMobileDailyUsage.day))
      .limit(1);
      
      return lastSync || null;
    } catch (error) {
      console.error('Error fetching last sync date:', error);
      return null;
    }
  }

  async getAllMtnMobileAccounts(): Promise<string[]> {
    try {
      const accounts = await db.select({
        accountNumber: clients.accountNumber,
        name: clients.name
      })
      .from(clients)
      .where(eq(clients.category, "MTN Mobile (GSM)"));
      
      console.log(`[getAllMtnMobileAccounts] Found ${accounts.length} MTN Mobile (GSM) clients`);
      
      const validAccounts = accounts
        .filter(account => account.accountNumber && account.accountNumber.trim().length > 0)
        .map(account => {
          let formattedAccount = account.accountNumber.trim();
          
          // Ensure account has @mobile.is.co.za suffix
          if (!formattedAccount.includes('@')) {
            formattedAccount += '@mobile.is.co.za';
            console.log(`[getAllMtnMobileAccounts] Added domain to account: ${account.accountNumber} -> ${formattedAccount} (${account.name})`);
          } else if (!formattedAccount.endsWith('@mobile.is.co.za')) {
            // Replace any existing domain with the correct one
            const username = formattedAccount.split('@')[0];
            formattedAccount = username + '@mobile.is.co.za';
            console.log(`[getAllMtnMobileAccounts] Fixed domain for account: ${account.accountNumber} -> ${formattedAccount} (${account.name})`);
          }
          
          return formattedAccount;
        });
      
      console.log(`[getAllMtnMobileAccounts] Returning ${validAccounts.length} valid accounts with proper @mobile.is.co.za format`);
      
      // Log first few accounts for verification
      validAccounts.slice(0, 5).forEach((account, index) => {
        console.log(`[getAllMtnMobileAccounts] Account ${index + 1}: ${account}`);
      });
      
      return validAccounts;
    } catch (error) {
      console.error('Error fetching MTN Mobile accounts:', error);
      throw error;
    }
  }

  // MTN Mobile Sync Progress operations
  async createSyncProgress(progress: InsertMtnMobileSyncProgress): Promise<MtnMobileSyncProgress> {
    try {
      const [result] = await db.insert(mtnMobileSyncProgress).values(progress).returning();
      return result;
    } catch (error) {
      console.error('Error creating sync progress:', error);
      throw error;
    }
  }

  async updateSyncProgress(sessionId: string, updates: Partial<InsertMtnMobileSyncProgress>): Promise<MtnMobileSyncProgress> {
    try {
      const [result] = await db.update(mtnMobileSyncProgress)
        .set({
          ...updates,
          updatedAt: new Date()
        })
        .where(eq(mtnMobileSyncProgress.sessionId, sessionId))
        .returning();
      
      return result;
    } catch (error) {
      console.error('Error updating sync progress:', error);
      throw error;
    }
  }

  async getSyncProgress(sessionId: string): Promise<MtnMobileSyncProgress | undefined> {
    try {
      const [progress] = await db.select()
        .from(mtnMobileSyncProgress)
        .where(eq(mtnMobileSyncProgress.sessionId, sessionId))
        .limit(1);
      
      return progress;
    } catch (error) {
      console.error('Error fetching sync progress:', error);
      return undefined;
    }
  }

  async deleteSyncProgress(sessionId: string): Promise<void> {
    try {
      await db.delete(mtnMobileSyncProgress)
        .where(eq(mtnMobileSyncProgress.sessionId, sessionId));
    } catch (error) {
      console.error('Error deleting sync progress:', error);
      throw error;
    }
  }

  // API Key operations
  async getApiKeys(): Promise<ApiKey[]> {
    try {
      const keys = await db.select().from(apiKeys).orderBy(desc(apiKeys.createdAt));
      return keys;
    } catch (error) {
      console.error('Error fetching API keys:', error);
      throw error;
    }
  }

  async getApiKey(id: number): Promise<ApiKey | undefined> {
    try {
      const [key] = await db.select().from(apiKeys).where(eq(apiKeys.id, id));
      return key;
    } catch (error) {
      console.error('Error fetching API key:', error);
      throw error;
    }
  }

  async getApiKeyByHash(hash: string): Promise<ApiKey | undefined> {
    try {
      const [key] = await db.select().from(apiKeys).where(eq(apiKeys.keyHash, hash));
      return key;
    } catch (error) {
      console.error('Error fetching API key by hash:', error);
      throw error;
    }
  }

  async createApiKey(apiKey: InsertApiKey): Promise<ApiKey> {
    try {
      const [createdKey] = await db
        .insert(apiKeys)
        .values({
          ...apiKey,
          createdAt: new Date(),
          updatedAt: new Date()
        })
        .returning();
      return createdKey;
    } catch (error) {
      console.error('Error creating API key:', error);
      throw error;
    }
  }

  async updateApiKey(id: number, apiKey: Partial<InsertApiKey>): Promise<ApiKey> {
    try {
      const [updatedKey] = await db
        .update(apiKeys)
        .set({
          ...apiKey,
          updatedAt: new Date()
        })
        .where(eq(apiKeys.id, id))
        .returning();
      return updatedKey;
    } catch (error) {
      console.error('Error updating API key:', error);
      throw error;
    }
  }

  async deleteApiKey(id: number): Promise<void> {
    try {
      await db.delete(apiKeys).where(eq(apiKeys.id, id));
    } catch (error) {
      console.error('Error deleting API key:', error);
      throw error;
    }
  }

  async updateApiKeyLastUsed(id: number): Promise<void> {
    try {
      await db
        .update(apiKeys)
        .set({ lastUsed: new Date() })
        .where(eq(apiKeys.id, id));
    } catch (error) {
      console.error('Error updating API key last used:', error);
      throw error;
    }
  }
  // Additional methods for activity logs
  async getAllUsers(): Promise<User[]> {
    return this.getUsers();
  }
  
  get db() {
    return db;
  }
}

export const storage = new DatabaseStorage();
