-- 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;

-- On-demand data deletion: delete all data for a specific HDD (by serial_number)
-- 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 $$
DECLARE
    v_hdd_id            INTEGER;
    v_deleted_values    BIGINT := 0;
    v_deleted_events    BIGINT := 0;
    v_deleted_catalog   INTEGER := 0;
BEGIN
    -- Get HDD ID for this serial
    SELECT id INTO v_hdd_id FROM hdd_inventory WHERE serial_number = p_serial_number;

    IF v_hdd_id IS NULL THEN
        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;

    -- Delete from smart_collection_events
    DELETE FROM smart_collection_events
    WHERE hdd_id = v_hdd_id OR serial_number = p_serial_number;
    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;

    RETURN QUERY SELECT v_deleted_values, v_deleted_events, v_deleted_catalog;
END;
$$ 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';
    RAISE NOTICE 'Functions: upsert_param_catalog, insert_collection_event, create_monthly_partition, delete_hdd_data_by_serial';
    RAISE NOTICE 'Next step: Run sql/migrate-v1-to-v2.sql to migrate existing data';
END $$;