ids.alfacom.it/server/storage.ts

import {
  routers,
  networkLogs,
  detections,
  whitelist,
  trainingHistory,
  networkAnalytics,
  publicLists,
  publicBlacklistIps,
  type Router,
  type InsertRouter,
  type NetworkLog,
  type InsertNetworkLog,
  type Detection,
  type InsertDetection,
  type Whitelist,
  type InsertWhitelist,
  type TrainingHistory,
  type InsertTrainingHistory,
  type NetworkAnalytics,
  type PublicList,
  type InsertPublicList,
  type PublicBlacklistIp,
  type InsertPublicBlacklistIp,
} from "@shared/schema";
import { db } from "./db";
import { eq, desc, and, gte, sql, inArray } from "drizzle-orm";

export interface IStorage {
  // Routers
  getAllRouters(): Promise<Router[]>;
  getRouterById(id: string): Promise<Router | undefined>;
  createRouter(router: InsertRouter): Promise<Router>;
  updateRouter(id: string, router: Partial<InsertRouter>): Promise<Router | undefined>;
  deleteRouter(id: string): Promise<boolean>;

  // Network Logs
  getRecentLogs(limit: number): Promise<NetworkLog[]>;
  getLogsByIp(sourceIp: string, limit: number): Promise<NetworkLog[]>;
  createLog(log: InsertNetworkLog): Promise<NetworkLog>;
  getLogsForTraining(limit: number, minTimestamp?: Date): Promise<NetworkLog[]>;

  // Detections
  getAllDetections(options: {
    limit?: number;
    anomalyType?: string;
    minScore?: number;
    maxScore?: number;
  }): Promise<Detection[]>;
  getDetectionByIp(sourceIp: string): Promise<Detection | undefined>;
  createDetection(detection: InsertDetection): Promise<Detection>;
  updateDetection(id: string, detection: Partial<InsertDetection>): Promise<Detection | undefined>;
  getUnblockedDetections(): Promise<Detection[]>;

  // Whitelist
  getAllWhitelist(): Promise<Whitelist[]>;
  getWhitelistByIp(ipAddress: string): Promise<Whitelist | undefined>;
  createWhitelist(whitelist: InsertWhitelist): Promise<Whitelist>;
  deleteWhitelist(id: string): Promise<boolean>;
  isWhitelisted(ipAddress: string): Promise<boolean>;

  // Training History
  getTrainingHistory(limit: number): Promise<TrainingHistory[]>;
  createTrainingHistory(history: InsertTrainingHistory): Promise<TrainingHistory>;
  getLatestTraining(): Promise<TrainingHistory | undefined>;

  // Network Analytics
  getAnalyticsByDateRange(startDate: Date, endDate: Date, hourly?: boolean): Promise<NetworkAnalytics[]>;
  getRecentAnalytics(days: number, hourly?: boolean): Promise<NetworkAnalytics[]>;
  
  // Dashboard Live Stats
  getLiveDashboardStats(hours: number): Promise<{
    totalPackets: number;
    attackPackets: number;
    normalPackets: number;
    uniqueIps: number;
    attackUniqueIps: number;
    attacksByCountry: Record<string, number>;
    attacksByType: Record<string, number>;
    recentDetections: Detection[];
  }>;

  // Public Lists
  getAllPublicLists(): Promise<PublicList[]>;
  getPublicListById(id: string): Promise<PublicList | undefined>;
  createPublicList(list: InsertPublicList): Promise<PublicList>;
  updatePublicList(id: string, list: Partial<InsertPublicList>): Promise<PublicList | undefined>;
  deletePublicList(id: string): Promise<boolean>;
  
  // Public Blacklist IPs
  getPublicBlacklistIps(options: {
    limit?: number;
    listId?: string;
    ipAddress?: string;
    isActive?: boolean;
  }): Promise<PublicBlacklistIp[]>;
  getPublicBlacklistStats(): Promise<{
    totalLists: number;
    totalIps: number;
    overlapWithDetections: number;
  }>;
  upsertBlacklistIp(listId: string, ipAddress: string, cidrRange: string | null): Promise<{created: boolean}>;

  // System
  testConnection(): Promise<boolean>;
}

export class DatabaseStorage implements IStorage {
  // Routers
  async getAllRouters(): Promise<Router[]> {
    return await db.select().from(routers).orderBy(desc(routers.createdAt));
  }

  async getRouterById(id: string): Promise<Router | undefined> {
    const [router] = await db.select().from(routers).where(eq(routers.id, id));
    return router || undefined;
  }

  async createRouter(insertRouter: InsertRouter): Promise<Router> {
    const [router] = await db.insert(routers).values(insertRouter).returning();
    return router;
  }

  async updateRouter(id: string, updateData: Partial<InsertRouter>): Promise<Router | undefined> {
    const [router] = await db
      .update(routers)
      .set(updateData)
      .where(eq(routers.id, id))
      .returning();
    return router || undefined;
  }

  async deleteRouter(id: string): Promise<boolean> {
    const result = await db.delete(routers).where(eq(routers.id, id));
    return result.rowCount !== null && result.rowCount > 0;
  }

  // Network Logs
  async getRecentLogs(limit: number): Promise<NetworkLog[]> {
    return await db
      .select()
      .from(networkLogs)
      .orderBy(desc(networkLogs.timestamp))
      .limit(limit);
  }

  async getLogsByIp(sourceIp: string, limit: number): Promise<NetworkLog[]> {
    return await db
      .select()
      .from(networkLogs)
      .where(eq(networkLogs.sourceIp, sourceIp))
      .orderBy(desc(networkLogs.timestamp))
      .limit(limit);
  }

  async createLog(insertLog: InsertNetworkLog): Promise<NetworkLog> {
    const [log] = await db.insert(networkLogs).values(insertLog).returning();
    return log;
  }

  async getLogsForTraining(limit: number, minTimestamp?: Date): Promise<NetworkLog[]> {
    const conditions = minTimestamp
      ? and(gte(networkLogs.timestamp, minTimestamp))
      : undefined;

    return await db
      .select()
      .from(networkLogs)
      .where(conditions)
      .orderBy(desc(networkLogs.timestamp))
      .limit(limit);
  }

  // Detections
  async getAllDetections(options: {
    limit?: number;
    anomalyType?: string;
    minScore?: number;
    maxScore?: number;
  }): Promise<Detection[]> {
    const { limit = 5000, anomalyType, minScore, maxScore } = options;
    
    // Build WHERE conditions
    const conditions = [];
    
    if (anomalyType) {
      conditions.push(eq(detections.anomalyType, anomalyType));
    }
    
    // Cast riskScore to numeric for proper comparison (stored as text in DB)
    if (minScore !== undefined) {
      conditions.push(sql`${detections.riskScore}::numeric >= ${minScore}`);
    }
    
    if (maxScore !== undefined) {
      conditions.push(sql`${detections.riskScore}::numeric <= ${maxScore}`);
    }
    
    const query = db
      .select()
      .from(detections)
      .orderBy(desc(detections.detectedAt))
      .limit(limit);
    
    if (conditions.length > 0) {
      return await query.where(and(...conditions));
    }
    
    return await query;
  }

  async getDetectionByIp(sourceIp: string): Promise<Detection | undefined> {
    const [detection] = await db
      .select()
      .from(detections)
      .where(eq(detections.sourceIp, sourceIp))
      .orderBy(desc(detections.detectedAt))
      .limit(1);
    return detection || undefined;
  }

  async createDetection(insertDetection: InsertDetection): Promise<Detection> {
    const [detection] = await db
      .insert(detections)
      .values(insertDetection)
      .returning();
    return detection;
  }

  async updateDetection(
    id: string,
    updateData: Partial<InsertDetection>
  ): Promise<Detection | undefined> {
    const [detection] = await db
      .update(detections)
      .set(updateData)
      .where(eq(detections.id, id))
      .returning();
    return detection || undefined;
  }

  async getUnblockedDetections(): Promise<Detection[]> {
    return await db
      .select()
      .from(detections)
      .where(eq(detections.blocked, false))
      .orderBy(desc(detections.riskScore));
  }

  // Whitelist
  async getAllWhitelist(): Promise<Whitelist[]> {
    return await db
      .select()
      .from(whitelist)
      .where(eq(whitelist.active, true))
      .orderBy(desc(whitelist.createdAt));
  }

  async getWhitelistByIp(ipAddress: string): Promise<Whitelist | undefined> {
    const [item] = await db
      .select()
      .from(whitelist)
      .where(and(eq(whitelist.ipAddress, ipAddress), eq(whitelist.active, true)));
    return item || undefined;
  }

  async createWhitelist(insertWhitelist: InsertWhitelist): Promise<Whitelist> {
    const [item] = await db.insert(whitelist).values(insertWhitelist).returning();
    return item;
  }

  async deleteWhitelist(id: string): Promise<boolean> {
    const result = await db.delete(whitelist).where(eq(whitelist.id, id));
    return result.rowCount !== null && result.rowCount > 0;
  }

  async isWhitelisted(ipAddress: string): Promise<boolean> {
    const item = await this.getWhitelistByIp(ipAddress);
    return item !== undefined;
  }

  // Training History
  async getTrainingHistory(limit: number): Promise<TrainingHistory[]> {
    return await db
      .select()
      .from(trainingHistory)
      .orderBy(desc(trainingHistory.trainedAt))
      .limit(limit);
  }

  async createTrainingHistory(insertHistory: InsertTrainingHistory): Promise<TrainingHistory> {
    const [history] = await db
      .insert(trainingHistory)
      .values(insertHistory)
      .returning();
    return history;
  }

  async getLatestTraining(): Promise<TrainingHistory | undefined> {
    const [history] = await db
      .select()
      .from(trainingHistory)
      .orderBy(desc(trainingHistory.trainedAt))
      .limit(1);
    return history || undefined;
  }

  // Network Analytics
  async getAnalyticsByDateRange(startDate: Date, endDate: Date, hourly: boolean = false): Promise<NetworkAnalytics[]> {
    const hourCondition = hourly 
      ? sql`hour IS NOT NULL`
      : sql`hour IS NULL`;
    
    // DEBUG: Log query parameters
    console.log('[ANALYTICS QUERY]', {
      startDate: startDate.toISOString(),
      endDate: endDate.toISOString(),
      hourly,
      hourCondition: hourly ? 'NOT NULL' : 'NULL'
    });
    
    const results = await db
      .select()
      .from(networkAnalytics)
      .where(
        and(
          gte(networkAnalytics.date, startDate),
          sql`${networkAnalytics.date} <= ${endDate}`,
          hourCondition
        )
      )
      .orderBy(desc(networkAnalytics.date), desc(networkAnalytics.hour));
    
    console.log('[ANALYTICS RESULTS]', results.length, 'records found');
    if (results.length > 0) {
      console.log('[ANALYTICS SAMPLE]', results[0]);
    }
    
    return results;
  }

  async getRecentAnalytics(days: number, hourly: boolean = false): Promise<NetworkAnalytics[]> {
    const startDate = new Date();
    startDate.setDate(startDate.getDate() - days);
    
    return this.getAnalyticsByDateRange(startDate, new Date(), hourly);
  }

  async getLiveDashboardStats(hours: number = 72) {
    const cutoffDate = new Date();
    cutoffDate.setHours(cutoffDate.getHours() - hours);
    
    const analytics = await db
      .select()
      .from(networkAnalytics)
      .where(
        and(
          gte(networkAnalytics.date, cutoffDate),
          sql`hour IS NOT NULL`
        )
      )
      .orderBy(desc(networkAnalytics.date), desc(networkAnalytics.hour));
    
    let totalPackets = 0;
    let attackPackets = 0;
    let normalPackets = 0;
    let uniqueIps = 0;
    let attackUniqueIps = 0;
    const attacksByCountry: Record<string, number> = {};
    const attacksByType: Record<string, number> = {};
    
    analytics.forEach(record => {
      totalPackets += record.totalPackets || 0;
      attackPackets += record.attackPackets || 0;
      normalPackets += record.normalPackets || 0;
      uniqueIps += record.uniqueIps || 0;
      attackUniqueIps += record.attackUniqueIps || 0;
      
      if (record.attacksByCountry) {
        try {
          const countries = JSON.parse(record.attacksByCountry);
          Object.entries(countries).forEach(([country, count]) => {
            attacksByCountry[country] = (attacksByCountry[country] || 0) + (count as number);
          });
        } catch {}
      }
      
      if (record.attacksByType) {
        try {
          const types = JSON.parse(record.attacksByType);
          Object.entries(types).forEach(([type, count]) => {
            attacksByType[type] = (attacksByType[type] || 0) + (count as number);
          });
        } catch {}
      }
    });
    
    const recentDetections = await db
      .select()
      .from(detections)
      .where(gte(detections.detectedAt, cutoffDate))
      .orderBy(desc(detections.detectedAt))
      .limit(100);
    
    return {
      totalPackets,
      attackPackets,
      normalPackets,
      uniqueIps,
      attackUniqueIps,
      attacksByCountry,
      attacksByType,
      recentDetections,
    };
  }

  // Public Lists
  async getAllPublicLists(): Promise<PublicList[]> {
    return await db.select().from(publicLists).orderBy(desc(publicLists.createdAt));
  }

  async getPublicListById(id: string): Promise<PublicList | undefined> {
    const [list] = await db.select().from(publicLists).where(eq(publicLists.id, id));
    return list || undefined;
  }

  async createPublicList(insertList: InsertPublicList): Promise<PublicList> {
    const [list] = await db.insert(publicLists).values(insertList).returning();
    return list;
  }

  async updatePublicList(id: string, updateData: Partial<InsertPublicList>): Promise<PublicList | undefined> {
    const [list] = await db
      .update(publicLists)
      .set(updateData)
      .where(eq(publicLists.id, id))
      .returning();
    return list || undefined;
  }

  async deletePublicList(id: string): Promise<boolean> {
    const result = await db.delete(publicLists).where(eq(publicLists.id, id));
    return result.rowCount !== null && result.rowCount > 0;
  }

  // Public Blacklist IPs
  async getPublicBlacklistIps(options: {
    limit?: number;
    listId?: string;
    ipAddress?: string;
    isActive?: boolean;
  }): Promise<PublicBlacklistIp[]> {
    const { limit = 1000, listId, ipAddress, isActive } = options;
    
    const conditions = [];
    
    if (listId) {
      conditions.push(eq(publicBlacklistIps.listId, listId));
    }
    
    if (ipAddress) {
      conditions.push(eq(publicBlacklistIps.ipAddress, ipAddress));
    }
    
    if (isActive !== undefined) {
      conditions.push(eq(publicBlacklistIps.isActive, isActive));
    }
    
    const query = db
      .select()
      .from(publicBlacklistIps)
      .orderBy(desc(publicBlacklistIps.lastSeen))
      .limit(limit);
    
    if (conditions.length > 0) {
      return await query.where(and(...conditions));
    }
    
    return await query;
  }

  async getPublicBlacklistStats(): Promise<{
    totalLists: number;
    totalIps: number;
    overlapWithDetections: number;
  }> {
    const lists = await db.select().from(publicLists).where(eq(publicLists.type, 'blacklist'));
    const totalLists = lists.length;
    
    const [{ count: totalIps }] = await db
      .select({ count: sql<number>`count(*)::int` })
      .from(publicBlacklistIps)
      .where(eq(publicBlacklistIps.isActive, true));
    
    const [{ count: overlapWithDetections }] = await db
      .select({ count: sql<number>`count(distinct ${detections.sourceIp})::int` })
      .from(detections)
      .innerJoin(publicBlacklistIps, eq(detections.sourceIp, publicBlacklistIps.ipAddress))
      .where(
        and(
          eq(publicBlacklistIps.isActive, true),
          eq(detections.detectionSource, 'public_blacklist'),
          sql`NOT EXISTS (
            SELECT 1 FROM ${whitelist}
            WHERE ${whitelist.ipAddress} = ${detections.sourceIp}
            AND ${whitelist.active} = true
          )`
        )
      );
    
    return {
      totalLists,
      totalIps: totalIps || 0,
      overlapWithDetections: overlapWithDetections || 0,
    };
  }

  async upsertBlacklistIp(listId: string, ipAddress: string, cidrRange: string | null): Promise<{created: boolean}> {
    try {
      const existing = await db
        .select()
        .from(publicBlacklistIps)
        .where(
          and(
            eq(publicBlacklistIps.listId, listId),
            eq(publicBlacklistIps.ipAddress, ipAddress)
          )
        );
      
      if (existing.length > 0) {
        await db
          .update(publicBlacklistIps)
          .set({ 
            lastSeen: new Date(),
            isActive: true,
            cidrRange: cidrRange,
            ipInet: ipAddress,
            cidrInet: cidrRange || `${ipAddress}/32`,
          })
          .where(eq(publicBlacklistIps.id, existing[0].id));
        return { created: false };
      } else {
        await db.insert(publicBlacklistIps).values({
          listId,
          ipAddress,
          cidrRange,
          ipInet: ipAddress,
          cidrInet: cidrRange || `${ipAddress}/32`,
          isActive: true,
          firstSeen: new Date(),
          lastSeen: new Date(),
        });
        return { created: true };
      }
    } catch (error) {
      console.error('[DB ERROR] Failed to upsert blacklist IP:', error);
      throw error;
    }
  }

  async testConnection(): Promise<boolean> {
    try {
      await db.execute(sql`SELECT 1`);
      return true;
    } catch (error) {
      console.error('[DB ERROR] Connection test failed:', error);
      return false;
    }
  }
}

export const storage = new DatabaseStorage();