-- autoSMART Database Schema v2.0 — Normalizare SMART parameters din JSONB

-- Version: 2.0

-- Description: Replaces blob-based JSONB storage with structured EAV model

-- Date: 2026-05-20

-- This schema REPLACES smart_readings with smart_collection_events + smart_param_values

-- Backward compat: v_smart_readings_compat view emulates old schema

-- ============================================================================

-- EXTENSIONS

-- ============================================================================

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE EXTENSION IF NOT EXISTS "btree_gin";

-- ============================================================================

-- TIMESTAMP TRIGGER (reusable)

-- ============================================================================

CREATE OR REPLACE FUNCTION update_timestamp() RETURNS TRIGGER AS $$

BEGIN

    NEW.updated_at = NOW();

    RETURN NEW;

END;

$$ LANGUAGE plpgsql;

-- ============================================================================

-- SMART PARAMETER CATALOG — Registru parametri cu auto-discovery

-- ============================================================================

CREATE TABLE smart_param_catalog (

    id                  SERIAL PRIMARY KEY,

    param_name          VARCHAR(100) NOT NULL,

    param_id_ata        INTEGER,

    device_type         VARCHAR(20) DEFAULT 'any',

    unit                VARCHAR(20),

    description         TEXT,

    warning_threshold   NUMERIC,

    critical_threshold  NUMERIC,

    health_weight       NUMERIC DEFAULT 1.0,

    is_critical         BOOLEAN DEFAULT false,

    lower_is_better     BOOLEAN DEFAULT true,

    track_raw_value     BOOLEAN DEFAULT true,

    track_normalized    BOOLEAN DEFAULT false,

    first_seen          TIMESTAMPTZ DEFAULT NOW(),

    created_at          TIMESTAMPTZ DEFAULT NOW(),

    updated_at          TIMESTAMPTZ DEFAULT NOW(),

    CONSTRAINT uq_param_name UNIQUE (param_name)

);

CREATE INDEX idx_param_catalog_name ON smart_param_catalog(param_name);

CREATE INDEX idx_param_catalog_critical ON smart_param_catalog(is_critical) WHERE is_critical = true;

CREATE TRIGGER update_param_catalog_timestamp

    BEFORE UPDATE ON smart_param_catalog

    FOR EACH ROW EXECUTE FUNCTION update_timestamp();

-- ============================================================================

-- SMART COLLECTION EVENTS — Metadate per-colectare (înlocuiește smart_readings)

-- ============================================================================

CREATE TABLE smart_collection_events (

    id              BIGSERIAL PRIMARY KEY,

    hdd_id          INTEGER NOT NULL REFERENCES hdd_inventory(id),

    serial_number   VARCHAR(100) NOT NULL,

    node_id         VARCHAR(50) NOT NULL,

    collected_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    collection_ok   BOOLEAN DEFAULT true,

    temperature     SMALLINT,

    checksum        VARCHAR(64),

    param_count     SMALLINT,

    notes           TEXT

);

CREATE INDEX idx_sce_hdd_id_time  ON smart_collection_events(hdd_id, collected_at DESC);

CREATE INDEX idx_sce_serial       ON smart_collection_events(serial_number);

CREATE INDEX idx_sce_node_time    ON smart_collection_events(node_id, collected_at DESC);

CREATE INDEX idx_sce_collected_at ON smart_collection_events(collected_at DESC);

CREATE INDEX idx_sce_checksum     ON smart_collection_events(checksum) WHERE checksum IS NOT NULL;

-- ============================================================================

-- SMART PARAM VALUES — Tabel EAV partiționat lunar

-- ============================================================================

CREATE TABLE smart_param_values (

    id              BIGSERIAL,

    event_id        BIGINT NOT NULL,

    hdd_id          INTEGER NOT NULL,

    param_id        INTEGER NOT NULL REFERENCES smart_param_catalog(id),

    collected_at    TIMESTAMPTZ NOT NULL,

    raw_value       BIGINT,

    normalized_value SMALLINT,

    worst_value     SMALLINT,

    threshold_value SMALLINT,

    when_failed     VARCHAR(20),

    PRIMARY KEY (collected_at, id)

) PARTITION BY RANGE (collected_at);

-- Creare partiții pentru perioada viitoare (2025-2027)

CREATE TABLE smart_param_values_2025_01 PARTITION OF smart_param_values

    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE smart_param_values_2025_02 PARTITION OF smart_param_values

    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

CREATE TABLE smart_param_values_2025_03 PARTITION OF smart_param_values

    FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

CREATE TABLE smart_param_values_2025_04 PARTITION OF smart_param_values

    FOR VALUES FROM ('2025-04-01') TO ('2025-05-01');

CREATE TABLE smart_param_values_2025_05 PARTITION OF smart_param_values

    FOR VALUES FROM ('2025-05-01') TO ('2025-06-01');

CREATE TABLE smart_param_values_2025_06 PARTITION OF smart_param_values

    FOR VALUES FROM ('2025-06-01') TO ('2025-07-01');

CREATE TABLE smart_param_values_2025_07 PARTITION OF smart_param_values

    FOR VALUES FROM ('2025-07-01') TO ('2025-08-01');

CREATE TABLE smart_param_values_2025_08 PARTITION OF smart_param_values

    FOR VALUES FROM ('2025-08-01') TO ('2025-09-01');

CREATE TABLE smart_param_values_2025_09 PARTITION OF smart_param_values

    FOR VALUES FROM ('2025-09-01') TO ('2025-10-01');

CREATE TABLE smart_param_values_2025_10 PARTITION OF smart_param_values

    FOR VALUES FROM ('2025-10-01') TO ('2025-11-01');

CREATE TABLE smart_param_values_2025_11 PARTITION OF smart_param_values

    FOR VALUES FROM ('2025-11-01') TO ('2025-12-01');

CREATE TABLE smart_param_values_2025_12 PARTITION OF smart_param_values

    FOR VALUES FROM ('2025-12-01') TO ('2026-01-01');

CREATE TABLE smart_param_values_2026_01 PARTITION OF smart_param_values

    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE smart_param_values_2026_02 PARTITION OF smart_param_values

    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

CREATE TABLE smart_param_values_2026_03 PARTITION OF smart_param_values

    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

CREATE TABLE smart_param_values_2026_04 PARTITION OF smart_param_values

    FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

CREATE TABLE smart_param_values_2026_05 PARTITION OF smart_param_values

    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');

CREATE TABLE smart_param_values_2026_06 PARTITION OF smart_param_values

    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

CREATE TABLE smart_param_values_2026_07 PARTITION OF smart_param_values

    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

CREATE TABLE smart_param_values_2026_08 PARTITION OF smart_param_values

    FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

CREATE TABLE smart_param_values_2026_09 PARTITION OF smart_param_values

    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

CREATE TABLE smart_param_values_2026_10 PARTITION OF smart_param_values

    FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');

CREATE TABLE smart_param_values_2026_11 PARTITION OF smart_param_values

    FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

CREATE TABLE smart_param_values_2026_12 PARTITION OF smart_param_values

    FOR VALUES FROM ('2026-12-01') TO ('2027-01-01');

-- Partiție default pentru date neprevăzute

CREATE TABLE smart_param_values_default PARTITION OF smart_param_values DEFAULT;

-- Indexuri pe partiții

CREATE INDEX idx_spv_hdd_param_time ON smart_param_values(hdd_id, param_id, collected_at DESC);

CREATE INDEX idx_spv_event_id ON smart_param_values(event_id);

CREATE INDEX idx_spv_param_time ON smart_param_values(param_id, collected_at DESC);

CREATE INDEX idx_spv_critical_values ON smart_param_values(hdd_id, collected_at DESC)

    WHERE raw_value > 0;

-- ============================================================================

-- VIEWS

-- ============================================================================

-- View 1: Ultimele valori per disc per parametru

CREATE VIEW v_latest_param_values AS

SELECT DISTINCT ON (spv.hdd_id, spv.param_id)

    spv.hdd_id,

    spv.param_id,

    spc.param_name,

    spc.unit,

    spc.is_critical,

    spv.raw_value,

    spv.normalized_value,

    spv.collected_at,

    hi.serial_number,

    hi.model_name,

    hi.current_node_id

FROM smart_param_values spv

JOIN smart_param_catalog spc ON spv.param_id = spc.id

JOIN hdd_inventory hi ON spv.hdd_id = hi.id

WHERE hi.status = 'active'

ORDER BY spv.hdd_id, spv.param_id, spv.collected_at DESC;

-- View 2: Starea curentă a fiecărui disc

CREATE VIEW v_drive_health_summary AS

SELECT

    hi.id              AS hdd_id,

    hi.serial_number,

    hi.model_name,

    hi.manufacturer,

    hi.current_node_id,

    hi.current_device_path,

    hi.status,

    hi.size_gb,

    sce_last.collected_at   AS last_collection,

    sce_last.temperature    AS last_temperature,

    sce_last.collection_ok  AS last_collection_ok,

    EXTRACT(EPOCH FROM (NOW() - sce_last.collected_at)) / 3600 AS hours_since_collection,

    critical_counts.params_above_zero AS critical_params_triggered,

    pred.risk_level,

    pred.failure_probability,

    pred.predicted_failure_date

FROM hdd_inventory hi

LEFT JOIN LATERAL (

    SELECT collected_at, temperature, collection_ok

    FROM smart_collection_events

    WHERE hdd_id = hi.id

    ORDER BY collected_at DESC

    LIMIT 1

) sce_last ON true

LEFT JOIN LATERAL (

    SELECT COUNT(*) AS params_above_zero

    FROM smart_param_values spv

    JOIN smart_param_catalog spc ON spv.param_id = spc.id

    WHERE spv.hdd_id = hi.id

      AND spc.is_critical = true

      AND spv.raw_value > 0

      AND spv.collected_at >= COALESCE((

          SELECT collected_at FROM smart_collection_events

          WHERE hdd_id = hi.id ORDER BY collected_at DESC LIMIT 1

      ), NOW()) - INTERVAL '5 minutes'

) critical_counts ON true

LEFT JOIN LATERAL (

    SELECT risk_level, failure_probability, predicted_failure_date

    FROM predictions

    WHERE hdd_id = hi.id

    ORDER BY timestamp DESC

    LIMIT 1

) pred ON true

WHERE hi.status = 'active';

-- View 3: Trending parametru specific

CREATE VIEW v_param_trend AS

SELECT

    hi.serial_number,

    hi.model_name,

    hi.current_node_id,

    spc.param_name,

    spc.unit,

    spc.is_critical,

    spc.warning_threshold,

    spc.critical_threshold,

    spv.raw_value,

    spv.normalized_value,

    spv.collected_at,

    sce.node_id AS collected_on_node,

    sce.temperature AS drive_temp_at_collection

FROM smart_param_values spv

JOIN smart_collection_events sce ON spv.event_id = sce.id

JOIN smart_param_catalog spc ON spv.param_id = spc.id

JOIN hdd_inventory hi ON spv.hdd_id = hi.id;

-- View 4: Cluster overview

CREATE VIEW v_cluster_overview AS

SELECT

    hi.current_node_id AS node,

    COUNT(*) AS total_drives,

    COUNT(*) FILTER (WHERE dhs.last_collection > NOW() - INTERVAL '1 hour') AS drives_recently_collected,

    COUNT(*) FILTER (WHERE dhs.critical_params_triggered > 0) AS drives_with_critical_params,

    COUNT(*) FILTER (WHERE dhs.risk_level IN ('high', 'critical')) AS drives_high_risk,

    MAX(dhs.last_collection) AS latest_collection,

    MIN(dhs.last_collection) AS oldest_collection

FROM hdd_inventory hi

JOIN v_drive_health_summary dhs ON hi.id = dhs.hdd_id

WHERE hi.status = 'active'

GROUP BY hi.current_node_id;

-- View 5: Compatibilitate cu codul existent (emulează smart_readings)

CREATE VIEW v_smart_readings_compat AS

SELECT

    sce.id,

    sce.hdd_id,

    sce.serial_number,

    sce.collected_at AS timestamp,

    sce.temperature,

    sce.node_id,

    sce.collection_ok,

    jsonb_object_agg(

        COALESCE(spc.param_name, ''),

        spv.raw_value

    ) FILTER (WHERE spc.param_name IS NOT NULL) AS parameters_json

FROM smart_collection_events sce

LEFT JOIN smart_param_values spv ON spv.event_id = sce.id

LEFT JOIN smart_param_catalog spc ON spv.param_id = spc.id

GROUP BY sce.id, sce.hdd_id, sce.serial_number, sce.collected_at, sce.temperature, sce.node_id, sce.collection_ok;

-- ============================================================================

-- FUNCTIONS

-- ============================================================================

-- Upsert parametru în catalog (auto-discovery)

CREATE OR REPLACE FUNCTION upsert_param_catalog(

    p_param_name    VARCHAR(100),

    p_param_id_ata  INTEGER DEFAULT NULL,

    p_unit          VARCHAR(20) DEFAULT 'count'

) RETURNS INTEGER AS $$

DECLARE

    v_id INTEGER;

BEGIN

    INSERT INTO smart_param_catalog (param_name, param_id_ata, unit, first_seen)

    VALUES (p_param_name, p_param_id_ata, p_unit, NOW())

    ON CONFLICT (param_name) DO UPDATE

        SET param_id_ata = COALESCE(EXCLUDED.param_id_ata, smart_param_catalog.param_id_ata),

            updated_at = NOW()

    RETURNING id INTO v_id;

    RETURN v_id;

END;

$$ LANGUAGE plpgsql;

-- Inserare atomică event + valori (înlocuiește logica de collection)

CREATE OR REPLACE FUNCTION insert_collection_event(

    p_hdd_id        INTEGER,

    p_serial        VARCHAR(100),

    p_node_id       VARCHAR(50),

    p_collected_at  TIMESTAMPTZ,

    p_temperature   SMALLINT,

    p_collection_ok BOOLEAN,

    p_checksum      VARCHAR(64),

    p_params        JSONB

) RETURNS BIGINT AS $$

DECLARE

    v_event_id  BIGINT;

    v_param_id  INTEGER;

    v_param     RECORD;

    v_count     SMALLINT := 0;

BEGIN

    INSERT INTO smart_collection_events

        (hdd_id, serial_number, node_id, collected_at,

         collection_ok, temperature, checksum, param_count)

    VALUES

        (p_hdd_id, p_serial, p_node_id, p_collected_at,

         p_collection_ok, p_temperature, p_checksum, 0)

    RETURNING id INTO v_event_id;

    FOR v_param IN SELECT * FROM jsonb_each(p_params)

    LOOP

        v_param_id := upsert_param_catalog(

            v_param.key,

            (v_param.value->>'id')::INTEGER,

            'count'

        );

        INSERT INTO smart_param_values

            (event_id, hdd_id, param_id, collected_at,

             raw_value, normalized_value, worst_value, threshold_value, when_failed)

        VALUES

            (v_event_id, p_hdd_id, v_param_id, p_collected_at,

             (v_param.value->>'raw_value')::BIGINT,

             (v_param.value->>'value')::SMALLINT,

             (v_param.value->>'worst')::SMALLINT,

             (v_param.value->>'thresh')::SMALLINT,

             v_param.value->>'when_failed');

        v_count := v_count + 1;

    END LOOP;

    UPDATE smart_collection_events SET param_count = v_count WHERE id = v_event_id;

    RETURN v_event_id;

END;

$$ LANGUAGE plpgsql;

-- Creare partiție lunară (cu idempotență)

CREATE OR REPLACE FUNCTION create_monthly_partition(p_year INTEGER, p_month INTEGER)

RETURNS VOID AS $$

DECLARE

    v_table_name    TEXT;

    v_start_date    DATE;

    v_end_date      DATE;

BEGIN

    v_table_name := format('smart_param_values_%s_%s',

        p_year, lpad(p_month::TEXT, 2, '0'));

    v_start_date := make_date(p_year, p_month, 1);

    v_end_date   := v_start_date + INTERVAL '1 month';

    IF NOT EXISTS (

        SELECT 1 FROM information_schema.tables

        WHERE table_name = v_table_name AND table_schema = 'public'

    ) THEN

        EXECUTE format(

            'CREATE TABLE %I PARTITION OF smart_param_values

             FOR VALUES FROM (%L) TO (%L)',

            v_table_name, v_start_date, v_end_date

        );

        RAISE NOTICE 'Created partition: %', v_table_name;

    END IF;

END;

$$ LANGUAGE plpgsql;

-- RETENTION POLICY: On-demand only (no automatic cleanup)

-- Called from frontend when user requests to remove a drive from monitoring

CREATE OR REPLACE FUNCTION delete_hdd_data_by_serial(

    p_serial_number VARCHAR(100),

    p_keep_catalog  BOOLEAN DEFAULT true

) RETURNS TABLE(

    deleted_values BIGINT,

    deleted_events BIGINT,

    deleted_catalog INTEGER

) AS $$

    v_deleted_values    BIGINT := 0;

    v_deleted_events    BIGINT := 0;

    v_deleted_catalog   INTEGER := 0;

    SELECT id INTO v_hdd_id FROM hdd_inventory WHERE serial_number = p_serial_number;

        RAISE NOTICE 'Serial % not found in inventory', p_serial_number;

        RETURN QUERY SELECT 0::BIGINT, 0::BIGINT, 0::INTEGER;

        RETURN;

    END IF;

    -- Delete from smart_param_values (cascade via event_id FK)

    DELETE FROM smart_param_values spv

    WHERE event_id IN (

        SELECT id FROM smart_collection_events

        WHERE hdd_id = v_hdd_id

    );

    GET DIAGNOSTICS v_deleted_values = ROW_COUNT;

    GET DIAGNOSTICS v_deleted_events = ROW_COUNT;

    -- Optionally delete from hdd_inventory and catalog

    IF NOT p_keep_catalog THEN

        DELETE FROM smart_param_catalog spc

        WHERE NOT EXISTS (

            SELECT 1 FROM smart_param_values

            WHERE param_id = spc.id

        );

        GET DIAGNOSTICS v_deleted_catalog = ROW_COUNT;

        DELETE FROM hdd_inventory WHERE id = v_hdd_id;

    END IF;

    RAISE NOTICE 'Deleted % param values, % events for serial %',

        v_deleted_values, v_deleted_events, p_serial_number;

$$ LANGUAGE plpgsql;

-- ============================================================================

-- PERMISSIONS

-- ============================================================================

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO autosmart;

GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO autosmart;

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO autosmart;

-- Specific grants for collections

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE smart_param_catalog TO autosmart;

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE smart_collection_events TO autosmart;

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE smart_param_values TO autosmart;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO autosmart;

-- ============================================================================

-- FINAL MESSAGE

-- ============================================================================

DO $$

BEGIN

    RAISE NOTICE '✅ autoSMART Schema v2.0 deployed successfully!';

    RAISE NOTICE 'New tables: smart_param_catalog, smart_collection_events, smart_param_values (partitioned)';

    RAISE NOTICE 'Views: v_latest_param_values, v_drive_health_summary, v_param_trend, v_cluster_overview, v_smart_readings_compat';

END $$;