-- 017_device_health_scores.sql: Device health scoring system

CREATE TABLE IF NOT EXISTS device_health_scores (
    id                  INTEGER PRIMARY KEY AUTOINCREMENT,
    device_uid          TEXT NOT NULL REFERENCES devices(device_uid) ON DELETE CASCADE,
    score               INTEGER NOT NULL DEFAULT 0 CHECK(score >= 0 AND score <= 100),
    status_score        INTEGER NOT NULL DEFAULT 0,
    encryption_score    INTEGER NOT NULL DEFAULT 0,
    load_score          INTEGER NOT NULL DEFAULT 0,
    alert_score         INTEGER NOT NULL DEFAULT 0,
    compliance_score    INTEGER NOT NULL DEFAULT 0,
    patch_score         INTEGER NOT NULL DEFAULT 0,
    level               TEXT NOT NULL DEFAULT 'unknown' CHECK(level IN ('healthy', 'warning', 'critical', 'unknown')),
    details             TEXT,
    computed_at         TEXT NOT NULL DEFAULT (datetime('now')),
    UNIQUE(device_uid)
);

CREATE INDEX IF NOT EXISTS idx_health_scores_level ON device_health_scores(level);
CREATE INDEX IF NOT EXISTS idx_health_scores_computed ON device_health_scores(computed_at);