-- =========================================================
-- SCHEMA: Tabella detections per IDS anomalie
-- =========================================================
-- Memorizza IP rilevati come pericolosi dal ML
-- =========================================================

-- Drop tabella se esiste (solo per ambiente di sviluppo)
DROP TABLE IF EXISTS detections CASCADE;

-- Crea tabella detections
CREATE TABLE detections (
    id VARCHAR PRIMARY KEY DEFAULT gen_random_uuid(),
    source_ip TEXT NOT NULL,
    risk_score DECIMAL(5, 2) NOT NULL,
    confidence DECIMAL(5, 2) NOT NULL,
    anomaly_type TEXT NOT NULL,
    reason TEXT,
    log_count INTEGER NOT NULL,
    first_seen TIMESTAMP NOT NULL,
    last_seen TIMESTAMP NOT NULL,
    blocked BOOLEAN NOT NULL DEFAULT FALSE,
    blocked_at TIMESTAMP,
    detected_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Indici per performance
CREATE INDEX IF NOT EXISTS detection_source_ip_idx ON detections(source_ip);
CREATE INDEX IF NOT EXISTS risk_score_idx ON detections(risk_score);
CREATE INDEX IF NOT EXISTS detected_at_idx ON detections(detected_at);

-- Commento tabella
COMMENT ON TABLE detections IS 'IP pericolosi rilevati dal sistema ML (Isolation Forest)';

-- Verifica
SELECT 'Tabella detections creata con successo!' AS status;