setup_database.sql

-- Distributed Log-Analyzer Database Schema
-- PostgreSQL 12+ required

-- Drop existing tables (for development)
DROP TABLE IF EXISTS ip_statistics CASCADE;
DROP TABLE IF EXISTS blacklist CASCADE;
DROP TABLE IF EXISTS logs CASCADE;

-- Main log storage table
CREATE TABLE logs (
    id BIGSERIAL PRIMARY KEY,
    timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
    source_ip INET NOT NULL,
    source_port INTEGER,
    destination_port INTEGER NOT NULL,
    protocol VARCHAR(10),
    action VARCHAR(20),
    message TEXT,
    client_id VARCHAR(100) NOT NULL,
    log_level VARCHAR(20) DEFAULT 'INFO',
    service_name VARCHAR(100),
    raw_log TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes for optimal query performance
CREATE INDEX idx_logs_timestamp ON logs(timestamp DESC);
CREATE INDEX idx_logs_source_ip ON logs(source_ip);
CREATE INDEX idx_logs_dest_port ON logs(destination_port);
CREATE INDEX idx_logs_client_timestamp ON logs(client_id, timestamp DESC);
CREATE INDEX idx_logs_action ON logs(action);

-- Blacklist table with comprehensive tracking
CREATE TABLE blacklist (
    id SERIAL PRIMARY KEY,
    ip_address INET NOT NULL,
    port INTEGER NOT NULL,
    reason TEXT,
    blacklist_count INTEGER DEFAULT 1,
    first_blacklisted_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    last_blacklisted_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    expires_at TIMESTAMP WITH TIME ZONE,
    is_active BOOLEAN DEFAULT TRUE,
    metadata JSONB,
    UNIQUE(ip_address, port)
);

CREATE INDEX idx_blacklist_active_ip ON blacklist(ip_address, is_active);
CREATE INDEX idx_blacklist_expires ON blacklist(expires_at) WHERE is_active = TRUE;
CREATE INDEX idx_blacklist_port ON blacklist(port);

-- Aggregated statistics table for analytics
CREATE TABLE ip_statistics (
    id BIGSERIAL PRIMARY KEY,
    ip_address INET NOT NULL,
    port INTEGER NOT NULL,
    window_start TIMESTAMP WITH TIME ZONE NOT NULL,
    window_end TIMESTAMP WITH TIME ZONE NOT NULL,
    request_count INTEGER NOT NULL,
    unique_services INTEGER DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(ip_address, port, window_start)
);

CREATE INDEX idx_ip_stats_window ON ip_statistics(window_start DESC);
CREATE INDEX idx_ip_stats_ip ON ip_statistics(ip_address);

-- Function to clean up old logs (retention policy)
CREATE OR REPLACE FUNCTION cleanup_old_logs()
RETURNS void AS $$
BEGIN
    DELETE FROM logs WHERE created_at < NOW() - INTERVAL '30 days';
    DELETE FROM ip_statistics WHERE created_at < NOW() - INTERVAL '90 days';
END;
$$ LANGUAGE plpgsql;

-- Function to expire old blacklist entries
CREATE OR REPLACE FUNCTION expire_blacklist_entries()
RETURNS INTEGER AS $$
DECLARE
    affected_rows INTEGER;
BEGIN
    UPDATE blacklist
    SET is_active = FALSE
    WHERE is_active = TRUE
      AND expires_at IS NOT NULL
      AND expires_at < NOW();
    
    GET DIAGNOSTICS affected_rows = ROW_COUNT;
    RETURN affected_rows;
END;
$$ LANGUAGE plpgsql;

-- View for active blacklist (frequently queried)
CREATE OR REPLACE VIEW active_blacklist AS
SELECT 
    ip_address,
    port,
    reason,
    blacklist_count,
    first_blacklisted_at,
    last_blacklisted_at,
    expires_at
FROM blacklist
WHERE is_active = TRUE;

-- View for top offending IPs
CREATE OR REPLACE VIEW top_offending_ips AS
SELECT 
    source_ip,
    COUNT(*) as log_count,
    COUNT(DISTINCT destination_port) as ports_accessed,
    MIN(timestamp) as first_seen,
    MAX(timestamp) as last_seen
FROM logs
WHERE timestamp > NOW() - INTERVAL '24 hours'
GROUP BY source_ip
ORDER BY log_count DESC
LIMIT 100;

-- Grant permissions (adjust user as needed)
-- GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO loganalyzer_user;
-- GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO loganalyzer_user;

-- Insert sample data for testing
INSERT INTO logs (timestamp, source_ip, source_port, destination_port, protocol, action, message, client_id, service_name)
VALUES 
    (NOW(), '192.168.1.100', 45231, 22, 'TCP', 'ACCEPT', 'SSH connection accepted', 'client-001', 'sshd'),
    (NOW(), '10.0.0.50', 33421, 443, 'TCP', 'ACCEPT', 'HTTPS connection', 'client-002', 'nginx'),
    (NOW(), '172.16.0.25', 54321, 3306, 'TCP', 'REJECT', 'Unauthorized MySQL access attempt', 'client-003', 'mysql');

COMMENT ON TABLE logs IS 'Stores all incoming log entries from distributed agents';
COMMENT ON TABLE blacklist IS 'Tracks IPs that have exceeded rate limits';
COMMENT ON TABLE ip_statistics IS 'Aggregated statistics for reporting and analytics';