CREATE TABLE logs (
    log_id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    timestamp TEXT NOT NULL,
    level TEXT NOT NULL CHECK (level IN ('DEBUG', 'INFO', 'WARN', 'ERROR', 'FATAL')),
    message TEXT NOT NULL,
    args TEXT, -- JSON string of log arguments
    source TEXT, -- tag or source component
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    -- Security-specific fields
    event_type TEXT, -- For security events
    severity TEXT, -- For security events
    remote_addr TEXT,
    ccn_pubkey TEXT,
    event_id TEXT,
    risk_score REAL
);

CREATE INDEX idx_logs_timestamp ON logs(timestamp);
CREATE INDEX idx_logs_level ON logs(level);
CREATE INDEX idx_logs_created_at ON logs(created_at);
CREATE INDEX idx_logs_source ON logs(source);
CREATE INDEX idx_logs_event_type ON logs(event_type);
CREATE INDEX idx_logs_severity ON logs(severity);
CREATE INDEX idx_logs_ccn_pubkey ON logs(ccn_pubkey);

CREATE TRIGGER cleanup_old_logs
AFTER INSERT ON logs
WHEN (SELECT COUNT(*) FROM logs) > 100000
BEGIN
    DELETE FROM logs
    WHERE created_at < (unixepoch() - 2592000); -- 30 days
END;