-- Agents enregistrés (scan-network, metric)
CREATE TABLE IF NOT EXISTS agents (
    id          TEXT PRIMARY KEY,
    hostname    TEXT NOT NULL,
    agent_type  TEXT NOT NULL CHECK(agent_type IN ('scan-network', 'metric')),
    ip          TEXT NOT NULL,
    version     TEXT NOT NULL,
    status      TEXT NOT NULL DEFAULT 'online' CHECK(status IN ('online', 'offline')),
    last_seen   TEXT NOT NULL,
    created_at  TEXT NOT NULL
);

-- Équipements réseau découverts
CREATE TABLE IF NOT EXISTS devices (
    ip          TEXT PRIMARY KEY,
    mac         TEXT,
    hostname    TEXT,
    vendor      TEXT,
    state       TEXT NOT NULL DEFAULT 'unknown' CHECK(state IN ('online', 'offline', 'sleep', 'unknown')),
    services    TEXT NOT NULL DEFAULT '[]',  -- JSON array
    open_ports  TEXT NOT NULL DEFAULT '[]',  -- JSON array
    last_seen   TEXT NOT NULL,
    first_seen  TEXT NOT NULL
);

-- Métriques système (dernière valeur par agent)
CREATE TABLE IF NOT EXISTS metrics (
    agent_id        TEXT NOT NULL REFERENCES agents(id),
    timestamp       TEXT NOT NULL,
    cpu_percent     REAL,
    ram_percent     REAL,
    load_avg        REAL,
    temperature_c   REAL,
    disk_percent    REAL,
    net_rx_bps      INTEGER,
    net_tx_bps      INTEGER,
    extra           TEXT DEFAULT '{}',  -- JSON pour champs futurs
    PRIMARY KEY (agent_id)
);

-- Événements système
CREATE TABLE IF NOT EXISTS events (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    agent_id    TEXT NOT NULL,
    event_type  TEXT NOT NULL,
    timestamp   TEXT NOT NULL,
    data        TEXT NOT NULL DEFAULT '{}'  -- JSON
);

CREATE INDEX IF NOT EXISTS idx_events_agent    ON events(agent_id);
CREATE INDEX IF NOT EXISTS idx_events_ts       ON events(timestamp);
CREATE INDEX IF NOT EXISTS idx_devices_state   ON devices(state);