-- autoSMART Database Schema - Fixed for PostgreSQL 15

-- This version removes problematic syntax and creates a working schema

-- Drop existing tables if they exist

DROP TABLE IF EXISTS smart_readings CASCADE;

DROP TABLE IF EXISTS predictions CASCADE;

DROP TABLE IF EXISTS alert_history CASCADE;

DROP TABLE IF EXISTS hdd_presence CASCADE;

DROP TABLE IF EXISTS hdd_inventory CASCADE;

-- Create required extensions

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE EXTENSION IF NOT EXISTS "btree_gin";

-- HDD Inventory Table (Hardware-based tracking)

CREATE TABLE hdd_inventory (

    id                  SERIAL PRIMARY KEY,

    serial_number       VARCHAR(100) NOT NULL,

    model_name          VARCHAR(200) NOT NULL,

    firmware            VARCHAR(50),

    size_gb             INTEGER,

    manufacturer        VARCHAR(100),

    current_device_path VARCHAR(50),

    current_node_id     VARCHAR(50),

    current_slot        VARCHAR(20),

    madagascar_id       VARCHAR(100),

    first_seen          TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    last_seen           TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    status              VARCHAR(20) DEFAULT 'active',

    status_changed_at   TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    notes               TEXT,

    created_at          TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    updated_at          TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    -- Hardware identification constraint

    CONSTRAINT unique_hardware_id UNIQUE (serial_number, model_name)

);

-- Create index for device path (but allow NULLs and duplicates)

CREATE INDEX idx_hdd_inventory_device_path ON hdd_inventory(current_device_path) WHERE current_device_path IS NOT NULL;

CREATE INDEX idx_hdd_inventory_node ON hdd_inventory(current_node_id);

CREATE INDEX idx_hdd_inventory_status ON hdd_inventory(status);

CREATE INDEX idx_hdd_inventory_last_seen ON hdd_inventory(last_seen);

-- HDD Presence Table (tracks HDD mobility across nodes)

CREATE TABLE hdd_presence (

    id SERIAL PRIMARY KEY,

    serial_number VARCHAR(64) NOT NULL,

    node VARCHAR(64) NOT NULL,

    data_start TIMESTAMP NOT NULL,

    data_end TIMESTAMP NOT NULL,

    is_current BOOLEAN NOT NULL DEFAULT TRUE

);

CREATE INDEX idx_hdd_presence_serial_current ON hdd_presence(serial_number, is_current);

CREATE INDEX idx_hdd_presence_node ON hdd_presence(node);

CREATE INDEX idx_hdd_presence_data_end ON hdd_presence(data_end DESC);

-- SMART Readings Table (with differential storage)

CREATE TABLE smart_readings (

    id                   BIGSERIAL PRIMARY KEY,

    hdd_id               INTEGER REFERENCES hdd_inventory(id),

    serial_number        VARCHAR(100) NOT NULL,

    device_path          VARCHAR(50),

    node_id              VARCHAR(50),

    timestamp            TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    collection_ok        BOOLEAN DEFAULT true,

    temperature          INTEGER,

    parameters_json      JSONB,

    reading_type         VARCHAR(20) DEFAULT 'full',

    changes_detected     BOOLEAN DEFAULT true,

    changed_parameters   JSONB,

    previous_reading_id  INTEGER REFERENCES smart_readings(id),

    checksum             VARCHAR(64)

);

CREATE INDEX idx_smart_readings_hdd_id ON smart_readings(hdd_id);

CREATE INDEX idx_smart_readings_timestamp ON smart_readings(timestamp DESC);

CREATE INDEX idx_smart_readings_serial ON smart_readings(serial_number);

CREATE INDEX idx_smart_readings_device_path ON smart_readings(device_path);

CREATE INDEX idx_smart_readings_type ON smart_readings(reading_type);

CREATE INDEX idx_smart_readings_checksum ON smart_readings(checksum);

CREATE INDEX idx_smart_readings_previous ON smart_readings(previous_reading_id);

-- GIN index for JSONB parameters

CREATE INDEX idx_smart_readings_parameters ON smart_readings USING GIN (parameters_json);

CREATE INDEX idx_smart_readings_changed_params ON smart_readings USING GIN (changed_parameters);

-- Predictions Table

CREATE TABLE predictions (

    id                    SERIAL PRIMARY KEY,

    hdd_id                INTEGER REFERENCES hdd_inventory(id),

    serial_number         VARCHAR(100) NOT NULL,

    device_path           VARCHAR(50),

    timestamp             TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    risk_level            VARCHAR(20),

    failure_probability   DECIMAL(5,4),

    predicted_failure_date DATE,

    confidence_score      DECIMAL(5,4),

    analysis_summary      TEXT,

    recommendations       JSONB,

    openai_response       JSONB,

    created_at            TIMESTAMP WITH TIME ZONE DEFAULT NOW()

);

CREATE INDEX idx_predictions_hdd_id ON predictions(hdd_id);

CREATE INDEX idx_predictions_timestamp ON predictions(timestamp DESC);

CREATE INDEX idx_predictions_risk_level ON predictions(risk_level);

CREATE INDEX idx_predictions_serial ON predictions(serial_number);

-- Alert History Table

CREATE TABLE alert_history (

    id              SERIAL PRIMARY KEY,

    hdd_id          INTEGER REFERENCES hdd_inventory(id),

    serial_number   VARCHAR(100) NOT NULL,

    alert_type      VARCHAR(50),

    severity        VARCHAR(20),

    message         TEXT,

    sent_at         TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    sent_to         TEXT,

    delivery_status VARCHAR(20) DEFAULT 'pending',

    related_reading_id BIGINT REFERENCES smart_readings(id),

    related_prediction_id INTEGER REFERENCES predictions(id)

);

CREATE INDEX idx_alert_history_hdd_id ON alert_history(hdd_id);

CREATE INDEX idx_alert_history_sent_at ON alert_history(sent_at DESC);

CREATE INDEX idx_alert_history_severity ON alert_history(severity);

CREATE INDEX idx_alert_history_serial ON alert_history(serial_number);

-- System Configuration Table

CREATE TABLE IF NOT EXISTS system_config (

    id          SERIAL PRIMARY KEY,

    config_key  VARCHAR(100) UNIQUE NOT NULL,

    value       TEXT,

    description TEXT,

    created_at  TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    updated_at  TIMESTAMP WITH TIME ZONE DEFAULT NOW()

);

-- Insert default configuration

INSERT INTO system_config (config_key, value, description) VALUES

('collection_interval_seconds', '1800', 'SMART data collection interval in seconds')

ON CONFLICT (config_key) DO NOTHING;

INSERT INTO system_config (config_key, value, description) VALUES

('differential_storage_enabled', 'true', 'Enable differential storage optimization'),

('forced_storage_interval_hours', '24', 'Hours between forced full readings'),

('critical_parameter_force_store', 'true', 'Force storage for critical parameter changes'),

('temperature_change_threshold', '5', 'Temperature change threshold for storage (Celsius)')

ON CONFLICT (config_key) DO NOTHING;

-- SMART Thresholds Table

CREATE TABLE IF NOT EXISTS smart_thresholds (

    id                SERIAL PRIMARY KEY,

    parameter_name    VARCHAR(100) NOT NULL,

    warning_threshold NUMERIC,

    critical_threshold NUMERIC,

    weight            NUMERIC DEFAULT 1.0,

    enabled           BOOLEAN DEFAULT true,

    description       TEXT,

    created_at        TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    updated_at        TIMESTAMP WITH TIME ZONE DEFAULT NOW()

);

-- Insert default SMART thresholds

INSERT INTO smart_thresholds (parameter_name, warning_threshold, critical_threshold, weight, description) VALUES

('Reallocated_Sector_Ct', 1, 5, 10.0, 'Reallocated sector count - critical for drive health'),

('Spin_Retry_Count', 1, 10, 8.0, 'Spindle motor retry attempts'),

('Reallocated_Event_Count', 1, 10, 9.0, 'Number of reallocation events'),

('Current_Pending_Sector', 1, 5, 9.5, 'Sectors waiting to be reallocated'),

('Offline_Uncorrectable', 1, 1, 10.0, 'Uncorrectable sectors found during offline scan'),

('UDMA_CRC_Error_Count', 10, 50, 5.0, 'Communication errors between drive and controller'),

('Raw_Read_Error_Rate', 100000, 1000000, 3.0, 'Raw read error rate (varies by manufacturer)'),

('Seek_Error_Rate', 100000, 1000000, 4.0, 'Seek error rate'),

('Power_On_Hours', 35000, 50000, 2.0, 'Total power-on time in hours'),

('Load_Cycle_Count', 100000, 300000, 2.0, 'Number of head load/unload cycles'),

('Temperature_Celsius', 50, 60, 3.0, 'Drive operating temperature'),

('Start_Stop_Count', 10000, 50000, 1.0, 'Drive start/stop cycles'),

('Power_Cycle_Count', 10000, 20000, 1.0, 'Number of power cycles')

ON CONFLICT (parameter_name) DO NOTHING;

-- Create view for reconstructed SMART data (handles differential storage)

CREATE VIEW smart_readings_reconstructed AS

WITH RECURSIVE reading_chain AS (

    -- Base case: get baseline readings

    SELECT

        id, hdd_id, serial_number, timestamp,

        parameters_json, temperature, reading_type,

        previous_reading_id, 1 as chain_level

    FROM smart_readings

    WHERE reading_type IN ('baseline', 'full')

    UNION ALL

    -- Recursive case: follow the chain of differential readings

    SELECT

        sr.id, sr.hdd_id, sr.serial_number, sr.timestamp,

        -- Merge parameters from previous reading with current changes

        COALESCE(rc.parameters_json, '{}'::jsonb) || sr.parameters_json as parameters_json,

        COALESCE(sr.temperature, rc.temperature) as temperature,

        sr.reading_type,

        sr.previous_reading_id,

        rc.chain_level + 1

    FROM smart_readings sr

    JOIN reading_chain rc ON sr.previous_reading_id = rc.id

    WHERE sr.reading_type = 'differential'

)

SELECT

    id, hdd_id, serial_number, timestamp,

    parameters_json, temperature, reading_type,

    chain_level

FROM reading_chain;

-- Latest SMART readings for all drives (using reconstructed differential data)

CREATE VIEW latest_smart_readings AS

SELECT DISTINCT ON (sr.hdd_id)

    sr.id,

    sr.hdd_id,

    sr.serial_number,

    sr.timestamp,

    sr.parameters_json,

    sr.temperature,

    hi.model_name,

    hi.manufacturer,

    hi.size_gb,

    hi.current_device_path,

    hi.current_node_id

FROM smart_readings_reconstructed sr

JOIN hdd_inventory hi ON sr.hdd_id = hi.id

ORDER BY sr.hdd_id, sr.timestamp DESC;

-- Drive health summary view

CREATE VIEW drive_health_summary AS

SELECT

    hi.id as hdd_id,

    hi.serial_number,

    hi.model_name,

    hi.manufacturer,

    hi.current_device_path,

    hi.current_node_id,

    hi.status,

    lsr.timestamp as last_reading,

    lsr.temperature,

    p.risk_level,

    p.failure_probability,

    p.predicted_failure_date,

    EXTRACT(EPOCH FROM (NOW() - lsr.timestamp))/3600 as hours_since_last_reading

FROM hdd_inventory hi

LEFT JOIN latest_smart_readings lsr ON hi.id = lsr.hdd_id

LEFT JOIN LATERAL (

    SELECT risk_level, failure_probability, predicted_failure_date

    FROM predictions

    WHERE hdd_id = hi.id

    ORDER BY timestamp DESC

    LIMIT 1

) p ON true

WHERE hi.status = 'active';

-- Function to check if SMART reading should be stored (simplified version)

CREATE OR REPLACE FUNCTION should_store_smart_reading(

    p_hdd_id INTEGER,

    p_parameters_json JSONB,

    p_checksum VARCHAR(64),

    p_timestamp TIMESTAMP WITH TIME ZONE DEFAULT NOW()

) RETURNS TABLE(

    should_store BOOLEAN,

    reading_type VARCHAR(20),

    changes_detected BOOLEAN,

    changed_parameters JSONB,

    previous_reading_id INTEGER

) AS $$

DECLARE

    v_last_reading RECORD;

    v_config_enabled BOOLEAN := true;

    v_force_interval_hours INTEGER := 24;

    v_temp_threshold INTEGER := 5;

BEGIN

    -- Get configuration

    SELECT (value::boolean) INTO v_config_enabled

    FROM system_config WHERE config_key = 'differential_storage_enabled';

    SELECT (value::integer) INTO v_force_interval_hours

    FROM system_config WHERE config_key = 'forced_storage_interval_hours';

    SELECT (value::integer) INTO v_temp_threshold

    FROM system_config WHERE config_key = 'temperature_change_threshold';

    -- If differential storage is disabled, always store as full

    IF v_config_enabled IS FALSE OR v_config_enabled IS NULL THEN

        RETURN QUERY SELECT true, 'full'::varchar(20), true, NULL::jsonb, NULL::integer;

        RETURN;

    END IF;

    -- Get the last reading for this HDD

    SELECT id, checksum, timestamp, parameters_json, temperature

    INTO v_last_reading

    FROM smart_readings

    WHERE hdd_id = p_hdd_id

    ORDER BY timestamp DESC

    LIMIT 1;

    -- If no previous reading, store as baseline

    IF v_last_reading IS NULL THEN

        RETURN QUERY SELECT true, 'baseline'::varchar(20), true, NULL::jsonb, NULL::integer;

        RETURN;

    END IF;

    -- If checksum matches, no changes detected

    IF v_last_reading.checksum = p_checksum THEN

        RETURN QUERY SELECT false, 'skipped'::varchar(20), false, NULL::jsonb, v_last_reading.id;

        RETURN;

    END IF;

    -- If forced interval exceeded, store as full

    IF p_timestamp > v_last_reading.timestamp + (v_force_interval_hours || ' hours')::interval THEN

        RETURN QUERY SELECT true, 'full'::varchar(20), true, NULL::jsonb, v_last_reading.id;

        RETURN;

    END IF;

    -- Otherwise, store as differential

    RETURN QUERY SELECT true, 'differential'::varchar(20), true, '[]'::jsonb, v_last_reading.id;

    RETURN;

END;

$$ LANGUAGE plpgsql;

-- Function to update HDD presence tracking

CREATE OR REPLACE FUNCTION update_hdd_presence(

    p_serial_number VARCHAR(64),

    p_node VARCHAR(64)

) RETURNS VOID AS $$

BEGIN

    -- Mark all previous presence records for this serial as historic

    UPDATE hdd_presence

    SET is_current = FALSE

    WHERE serial_number = p_serial_number AND is_current = TRUE AND node <> p_node;

    -- Check if there's already a current presence for this serial/node

    IF EXISTS (SELECT 1 FROM hdd_presence WHERE serial_number = p_serial_number AND node = p_node AND is_current = TRUE) THEN

        -- Update data_end for existing current presence

        UPDATE hdd_presence

        SET data_end = NOW()

        WHERE serial_number = p_serial_number AND node = p_node AND is_current = TRUE;

    ELSE

        -- Create new presence record

        INSERT INTO hdd_presence (serial_number, node, data_start, data_end, is_current)

        VALUES (p_serial_number, p_node, NOW(), NOW(), TRUE);

    END IF;

END;

$$ LANGUAGE plpgsql;

-- Function to update timestamps

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

BEGIN

    NEW.updated_at = NOW();

    RETURN NEW;

END;

$$ LANGUAGE plpgsql;

-- Create triggers for timestamp updates

CREATE TRIGGER update_hdd_inventory_timestamp

    BEFORE UPDATE ON hdd_inventory

    FOR EACH ROW EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_smart_thresholds_timestamp

    BEFORE UPDATE ON smart_thresholds

    FOR EACH ROW EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_system_config_timestamp

    BEFORE UPDATE ON system_config

    FOR EACH ROW EXECUTE FUNCTION update_timestamp();

-- Grant permissions to autosmart user

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;

-- Grant specific permissions for hdd_presence table

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

-- Final message

DO $$

BEGIN

    RAISE NOTICE 'autoSMART database schema deployed successfully!';

    RAISE NOTICE 'Tables created: hdd_inventory, hdd_presence, smart_readings, predictions, smart_thresholds, alert_history, system_config';

    RAISE NOTICE 'Views created: smart_readings_reconstructed, latest_smart_readings, drive_health_summary';

    RAISE NOTICE 'Functions created: update_hdd_presence(), should_store_smart_reading()';

    RAISE NOTICE 'Permissions granted to autosmart user';

END $$;