apiVersion: v1
kind: ConfigMap
metadata:
  name: timescaledb-init
data:
  init.sql: |
    -- TimescaleDB initialization script
    CREATE EXTENSION IF NOT EXISTS timescaledb;

    CREATE TABLE IF NOT EXISTS metrics_raw (
        time        TIMESTAMPTZ NOT NULL,
        machine_id  TEXT NOT NULL,
        hostname    TEXT NOT NULL,
        metric_type TEXT NOT NULL,
        value       DOUBLE PRECISION NOT NULL,
        labels      JSONB DEFAULT '{}'::jsonb
    );

    SELECT create_hypertable('metrics_raw', 'time',
        chunk_time_interval => INTERVAL '1 hour',
        if_not_exists => TRUE
    );

    CREATE INDEX IF NOT EXISTS idx_metrics_raw_machine
        ON metrics_raw (machine_id, time DESC);
    CREATE INDEX IF NOT EXISTS idx_metrics_raw_type
        ON metrics_raw (metric_type, time DESC);

    CREATE TABLE IF NOT EXISTS metrics_1m (
        time        TIMESTAMPTZ NOT NULL,
        machine_id  TEXT NOT NULL,
        hostname    TEXT NOT NULL,
        metric_type TEXT NOT NULL,
        avg_value   DOUBLE PRECISION NOT NULL,
        min_value   DOUBLE PRECISION NOT NULL,
        max_value   DOUBLE PRECISION NOT NULL,
        sample_count INTEGER NOT NULL
    );

    SELECT create_hypertable('metrics_1m', 'time',
        chunk_time_interval => INTERVAL '1 day',
        if_not_exists => TRUE
    );

    CREATE TABLE IF NOT EXISTS machines (
        machine_id  TEXT PRIMARY KEY,
        hostname    TEXT NOT NULL,
        first_seen  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        last_seen   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        metadata    JSONB DEFAULT '{}'::jsonb,
        health      TEXT NOT NULL DEFAULT 'UNKNOWN'
    );

    CREATE TABLE IF NOT EXISTS alert_rules (
        id          SERIAL PRIMARY KEY,
        name        TEXT NOT NULL UNIQUE,
        metric_type TEXT NOT NULL,
        operator    TEXT NOT NULL,
        threshold   DOUBLE PRECISION NOT NULL,
        severity    TEXT NOT NULL,
        enabled     BOOLEAN NOT NULL DEFAULT TRUE,
        created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
    );

    CREATE TABLE IF NOT EXISTS alerts (
        id          SERIAL,
        time        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        machine_id  TEXT NOT NULL,
        rule_id     INTEGER REFERENCES alert_rules(id),
        rule_name   TEXT NOT NULL,
        metric_type TEXT NOT NULL,
        value       DOUBLE PRECISION NOT NULL,
        threshold   DOUBLE PRECISION NOT NULL,
        severity    TEXT NOT NULL,
        resolved_at TIMESTAMPTZ,
        PRIMARY KEY (id, time)
    );

    SELECT create_hypertable('alerts', 'time',
        chunk_time_interval => INTERVAL '1 day',
        if_not_exists => TRUE
    );

    SELECT add_retention_policy('metrics_raw', INTERVAL '24 hours', if_not_exists => TRUE);
    SELECT add_retention_policy('alerts', INTERVAL '30 days', if_not_exists => TRUE);

    INSERT INTO alert_rules (name, metric_type, operator, threshold, severity)
    VALUES
        ('High CPU Usage', 'CPU_PERCENT', 'gt', 80.0, 'warning'),
        ('Critical CPU Usage', 'CPU_PERCENT', 'gt', 95.0, 'critical'),
        ('High Memory Usage', 'MEMORY_PERCENT', 'gt', 85.0, 'warning'),
        ('Critical Memory Usage', 'MEMORY_PERCENT', 'gt', 95.0, 'critical')
    ON CONFLICT (name) DO NOTHING;