CREATE TABLE checks (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    type TEXT NOT NULL, -- script | docker_container | docker_network
    description TEXT,
    target TEXT,
    script_name TEXT,
    schedule_seconds INTEGER DEFAULT 60,
    timeout_seconds INTEGER DEFAULT 20,
    active BOOLEAN DEFAULT TRUE,
    last_run TIMESTAMP,
    last_status TEXT
);

CREATE TABLE results (
    id SERIAL PRIMARY KEY,
    check_id INTEGER REFERENCES checks(id) ON DELETE CASCADE,
    run_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status TEXT,
    message TEXT,
    duration_ms INTEGER
);

INSERT INTO checks (name, type, description, target)
VALUES
('Google HTTP', 'script', 'Prüft google.com', NULL),
('Postgres Container Running', 'docker_container', 'Ist Postgres Container aktiv?', 'info-postgres'),
('Default Network contains Postgres', 'docker_network', 'Ist Postgres im default Netzwerk?', 'bridge');

UPDATE checks 
SET script_name = 'check_http_google.sh'
WHERE name = 'Google HTTP';

UPDATE checks
SET target = 'appServer_InfoDB'
WHERE name = 'Postgres Container Running';

UPDATE checks
SET target = 'appserver_default,appServer_InfoDB'
WHERE name = 'Default Network contains Postgres';

ALTER TABLE checks
ADD COLUMN display_id TEXT;

UPDATE checks
SET display_id = id
WHERE name = 'Google HTTP';

INSERT INTO checks (name, type, description, target, script_name, display_id)
VALUES
('schooltech.ch erreichbar', 'script', 'Prüft schooltech.ch', NULL, 'check_http_schooltech.sh', '1.1')