-- Alerts tablosu
CREATE TABLE alerts (
    id UUID PRIMARY KEY,
    incident_id TEXT NOT NULL,
    severity TEXT NOT NULL,
    message TEXT NOT NULL,
    metric_name TEXT NOT NULL,
    current_value DOUBLE PRECISION NOT NULL,
    threshold DOUBLE PRECISION NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL,
    resolved_at TIMESTAMP WITH TIME ZONE,
    metadata JSONB
);

-- Metrics tablosu
CREATE TABLE metrics (
    id UUID PRIMARY KEY,
    metric_name TEXT NOT NULL,
    value DOUBLE PRECISION NOT NULL,
    timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
    labels JSONB
);

-- Model health history
CREATE TABLE model_health (
    id UUID PRIMARY KEY,
    status TEXT NOT NULL,
    accuracy DOUBLE PRECISION NOT NULL,
    timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
    metrics JSONB
);

-- Patterns tablosu
CREATE TABLE patterns (
    id UUID PRIMARY KEY,
    pattern_type TEXT NOT NULL,
    confidence DOUBLE PRECISION NOT NULL,
    frequency INTEGER NOT NULL,
    first_seen TIMESTAMP WITH TIME ZONE NOT NULL,
    last_seen TIMESTAMP WITH TIME ZONE NOT NULL,
    affected_metrics JSONB
);

-- İndeksler
CREATE INDEX idx_alerts_created_at ON alerts(created_at);
CREATE INDEX idx_alerts_severity ON alerts(severity);
CREATE INDEX idx_metrics_timestamp ON metrics(timestamp);
CREATE INDEX idx_model_health_timestamp ON model_health(timestamp);
CREATE INDEX idx_patterns_type ON patterns(pattern_type);

-- Views
CREATE VIEW alert_summary AS
SELECT 
    date_trunc('hour', created_at) as hour,
    severity,
    COUNT(*) as count,
    AVG(current_value) as avg_value
FROM alerts
GROUP BY 1, 2;

CREATE VIEW pattern_summary AS
SELECT 
    pattern_type,
    COUNT(*) as count,
    AVG(confidence) as avg_confidence,
    MAX(last_seen) as last_seen
FROM patterns
GROUP BY 1; 