ids.alfacom.it/shared/schema.ts

import { sql, relations } from "drizzle-orm";
import { pgTable, text, varchar, integer, timestamp, decimal, boolean, index, bigint, unique } from "drizzle-orm/pg-core";
import { createInsertSchema } from "drizzle-zod";
import { z } from "zod";

// Router MikroTik configuration
export const routers = pgTable("routers", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  name: text("name").notNull(),
  ipAddress: text("ip_address").notNull().unique(),
  apiPort: integer("api_port").notNull().default(8729),
  username: text("username").notNull(),
  password: text("password").notNull(),
  enabled: boolean("enabled").notNull().default(true),
  lastSync: timestamp("last_sync"),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

// Network logs from MikroTik (syslog)
export const networkLogs = pgTable("network_logs", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  routerName: text("router_name").notNull(), // Hostname dal syslog
  timestamp: timestamp("timestamp").notNull(),
  sourceIp: text("source_ip").notNull(),
  sourcePort: integer("source_port"),
  destinationIp: text("destination_ip"),
  destinationPort: integer("destination_port"),
  protocol: text("protocol"),
  action: text("action"),
  packetLength: integer("packet_length"),
  rawMessage: text("raw_message"),
  createdAt: timestamp("created_at").defaultNow().notNull(),
}, (table) => ({
  sourceIpIdx: index("source_ip_idx").on(table.sourceIp),
  timestampIdx: index("timestamp_idx").on(table.timestamp),
  routerNameIdx: index("router_name_idx").on(table.routerName),
}));

// Detected threats/anomalies
export const detections = pgTable("detections", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  sourceIp: text("source_ip").notNull(),
  riskScore: decimal("risk_score", { precision: 5, scale: 2 }).notNull(),
  confidence: decimal("confidence", { precision: 5, scale: 2 }).notNull(),
  anomalyType: text("anomaly_type").notNull(),
  reason: text("reason"),
  logCount: integer("log_count").notNull(),
  firstSeen: timestamp("first_seen").notNull(),
  lastSeen: timestamp("last_seen").notNull(),
  blocked: boolean("blocked").notNull().default(false),
  blockedAt: timestamp("blocked_at"),
  detectedAt: timestamp("detected_at").defaultNow().notNull(),
  // Geolocation & AS info
  country: text("country"),
  countryCode: text("country_code"),
  city: text("city"),
  organization: text("organization"),
  asNumber: text("as_number"),
  asName: text("as_name"),
  isp: text("isp"),
  // Public lists integration
  detectionSource: text("detection_source").notNull().default("ml_model"),
  blacklistId: varchar("blacklist_id").references(() => publicBlacklistIps.id, { onDelete: 'set null' }),
}, (table) => ({
  sourceIpIdx: index("detection_source_ip_idx").on(table.sourceIp),
  riskScoreIdx: index("risk_score_idx").on(table.riskScore),
  detectedAtIdx: index("detected_at_idx").on(table.detectedAt),
  countryIdx: index("country_idx").on(table.country),
  detectionSourceIdx: index("detection_source_idx").on(table.detectionSource),
}));

// Whitelist per IP fidati
export const whitelist = pgTable("whitelist", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  ipAddress: text("ip_address").notNull().unique(),
  ipInet: text("ip_inet"),
  comment: text("comment"),
  reason: text("reason"),
  createdBy: text("created_by"),
  active: boolean("active").notNull().default(true),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  // Public lists integration
  source: text("source").notNull().default("manual"),
  listId: varchar("list_id").references(() => publicLists.id, { onDelete: 'set null' }),
}, (table) => ({
  sourceIdx: index("whitelist_source_idx").on(table.source),
}));

// ML Training history
export const trainingHistory = pgTable("training_history", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  modelVersion: text("model_version").notNull(),
  recordsProcessed: integer("records_processed").notNull(),
  featuresCount: integer("features_count").notNull(),
  accuracy: decimal("accuracy", { precision: 5, scale: 2 }),
  trainingDuration: integer("training_duration"),
  status: text("status").notNull(),
  notes: text("notes"),
  trainedAt: timestamp("trained_at").defaultNow().notNull(),
});

// Network analytics - aggregazioni permanenti per statistiche long-term
export const networkAnalytics = pgTable("network_analytics", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  date: timestamp("date", { mode: 'date' }).notNull(),
  hour: integer("hour"), // NULL = giornaliera, 0-23 = oraria
  
  // Traffico totale
  totalPackets: integer("total_packets").notNull().default(0),
  totalBytes: bigint("total_bytes", { mode: 'number' }).notNull().default(0),
  uniqueIps: integer("unique_ips").notNull().default(0),
  
  // Traffico normale (non anomalo)
  normalPackets: integer("normal_packets").notNull().default(0),
  normalBytes: bigint("normal_bytes", { mode: 'number' }).notNull().default(0),
  normalUniqueIps: integer("normal_unique_ips").notNull().default(0),
  topNormalIps: text("top_normal_ips"), // JSON: [{ip, packets, bytes, country}]
  
  // Attacchi/Anomalie
  attackPackets: integer("attack_packets").notNull().default(0),
  attackBytes: bigint("attack_bytes", { mode: 'number' }).notNull().default(0),
  attackUniqueIps: integer("attack_unique_ips").notNull().default(0),
  attacksByCountry: text("attacks_by_country"), // JSON: {IT: 5, RU: 30}
  attacksByType: text("attacks_by_type"), // JSON: {ddos: 10, port_scan: 5}
  topAttackers: text("top_attackers"), // JSON: [{ip, country, risk_score, packets}]
  
  // Dettagli geografici (tutto il traffico)
  trafficByCountry: text("traffic_by_country"), // JSON: {IT: {normal: 100, attacks: 5}}
  
  createdAt: timestamp("created_at").defaultNow().notNull(),
}, (table) => ({
  dateHourIdx: index("network_analytics_date_hour_idx").on(table.date, table.hour),
  dateIdx: index("network_analytics_date_idx").on(table.date),
  // CRITICAL: Vincolo UNIQUE per ON CONFLICT in aggregator
  dateHourUnique: unique("network_analytics_date_hour_key").on(table.date, table.hour),
}));

// Public threat/whitelist sources
export const publicLists = pgTable("public_lists", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  name: text("name").notNull(),
  type: text("type").notNull(),
  url: text("url").notNull(),
  enabled: boolean("enabled").notNull().default(true),
  fetchIntervalMinutes: integer("fetch_interval_minutes").notNull().default(10),
  lastFetch: timestamp("last_fetch"),
  lastSuccess: timestamp("last_success"),
  totalIps: integer("total_ips").notNull().default(0),
  activeIps: integer("active_ips").notNull().default(0),
  errorCount: integer("error_count").notNull().default(0),
  lastError: text("last_error"),
  createdAt: timestamp("created_at").defaultNow().notNull(),
}, (table) => ({
  typeIdx: index("public_lists_type_idx").on(table.type),
  enabledIdx: index("public_lists_enabled_idx").on(table.enabled),
}));

// Public blacklist IPs from external sources
export const publicBlacklistIps = pgTable("public_blacklist_ips", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  ipAddress: text("ip_address").notNull(),
  cidrRange: text("cidr_range"),
  ipInet: text("ip_inet"),
  cidrInet: text("cidr_inet"),
  listId: varchar("list_id").notNull().references(() => publicLists.id, { onDelete: 'cascade' }),
  firstSeen: timestamp("first_seen").defaultNow().notNull(),
  lastSeen: timestamp("last_seen").defaultNow().notNull(),
  isActive: boolean("is_active").notNull().default(true),
}, (table) => ({
  ipAddressIdx: index("public_blacklist_ip_idx").on(table.ipAddress),
  listIdIdx: index("public_blacklist_list_idx").on(table.listId),
  isActiveIdx: index("public_blacklist_active_idx").on(table.isActive),
  ipListUnique: unique("public_blacklist_ip_list_key").on(table.ipAddress, table.listId),
}));

// Schema version tracking for database migrations
export const schemaVersion = pgTable("schema_version", {
  id: integer("id").primaryKey().default(1),
  version: integer("version").notNull().default(0),
  appliedAt: timestamp("applied_at").defaultNow().notNull(),
  description: text("description"),
});

// Relations
export const routersRelations = relations(routers, ({ many }) => ({
  logs: many(networkLogs),
}));

export const publicListsRelations = relations(publicLists, ({ many }) => ({
  blacklistIps: many(publicBlacklistIps),
}));

export const publicBlacklistIpsRelations = relations(publicBlacklistIps, ({ one }) => ({
  list: one(publicLists, {
    fields: [publicBlacklistIps.listId],
    references: [publicLists.id],
  }),
}));

export const whitelistRelations = relations(whitelist, ({ one }) => ({
  list: one(publicLists, {
    fields: [whitelist.listId],
    references: [publicLists.id],
  }),
}));

export const detectionsRelations = relations(detections, ({ one }) => ({
  blacklist: one(publicBlacklistIps, {
    fields: [detections.blacklistId],
    references: [publicBlacklistIps.id],
  }),
}));

// Insert schemas
export const insertRouterSchema = createInsertSchema(routers).omit({
  id: true,
  createdAt: true,
  lastSync: true,
});

export const insertNetworkLogSchema = createInsertSchema(networkLogs).omit({
  id: true,
  createdAt: true,
});

export const insertDetectionSchema = createInsertSchema(detections).omit({
  id: true,
  detectedAt: true,
});

export const insertWhitelistSchema = createInsertSchema(whitelist).omit({
  id: true,
  createdAt: true,
});

export const insertTrainingHistorySchema = createInsertSchema(trainingHistory).omit({
  id: true,
  trainedAt: true,
});

export const insertSchemaVersionSchema = createInsertSchema(schemaVersion).omit({
  appliedAt: true,
});

export const insertNetworkAnalyticsSchema = createInsertSchema(networkAnalytics).omit({
  id: true,
  createdAt: true,
});

export const insertPublicListSchema = createInsertSchema(publicLists).omit({
  id: true,
  createdAt: true,
  lastFetch: true,
  lastSuccess: true,
});

export const insertPublicBlacklistIpSchema = createInsertSchema(publicBlacklistIps).omit({
  id: true,
  firstSeen: true,
  lastSeen: true,
});

// Types
export type Router = typeof routers.$inferSelect;
export type InsertRouter = z.infer<typeof insertRouterSchema>;

export type NetworkLog = typeof networkLogs.$inferSelect;
export type InsertNetworkLog = z.infer<typeof insertNetworkLogSchema>;

export type Detection = typeof detections.$inferSelect;
export type InsertDetection = z.infer<typeof insertDetectionSchema>;

export type Whitelist = typeof whitelist.$inferSelect;
export type InsertWhitelist = z.infer<typeof insertWhitelistSchema>;

export type TrainingHistory = typeof trainingHistory.$inferSelect;
export type InsertTrainingHistory = z.infer<typeof insertTrainingHistorySchema>;

export type SchemaVersion = typeof schemaVersion.$inferSelect;
export type InsertSchemaVersion = z.infer<typeof insertSchemaVersionSchema>;

export type NetworkAnalytics = typeof networkAnalytics.$inferSelect;
export type InsertNetworkAnalytics = z.infer<typeof insertNetworkAnalyticsSchema>;

export type PublicList = typeof publicLists.$inferSelect;
export type InsertPublicList = z.infer<typeof insertPublicListSchema>;

export type PublicBlacklistIp = typeof publicBlacklistIps.$inferSelect;
export type InsertPublicBlacklistIp = z.infer<typeof insertPublicBlacklistIpSchema>;