-- Migration: Add v13.0 failure probability column
-- Adds a second failure probability column for the modern algorithm (without attr 193)

-- Add new column for v13.0+ algorithm (without attribute 193)
ALTER TABLE smart_metrics
ADD COLUMN IF NOT EXISTS failure_probability_v13_pct DECIMAL(5, 2);

-- Rename existing column to clarify it includes attribute 193 (pre-v13.0)
ALTER TABLE smart_metrics
RENAME COLUMN failure_probability_pct TO failure_probability_v12_pct;

-- Update views to include both columns
DROP VIEW IF EXISTS smart_latest CASCADE;
CREATE VIEW smart_latest AS
SELECT
    m.id,
    m.timestamp,
    m.device_wwn,
    d.device_path,
    d.serial_number,
    d.model,
    d.manufacturer,
    d.capacity_bytes,
    d.size_tb,
    d.disk_role,
    m.temperature_celsius,
    m.power_on_days,
    m.error_count,
    m.failure_probability_v12_pct,  -- With attr 193 (matches your current SnapRAID)
    m.failure_probability_v13_pct,  -- Without attr 193 (modern SnapRAID v13.0+)
    m.smart_attributes
FROM smart_metrics m
INNER JOIN devices d ON m.device_wwn = d.device_wwn
WHERE m.timestamp = (
    SELECT MAX(m2.timestamp)
    FROM smart_metrics m2
    WHERE m2.device_wwn = m.device_wwn
);

DROP VIEW IF EXISTS smart_high_risk;
CREATE VIEW smart_high_risk AS
SELECT *
FROM smart_latest
WHERE failure_probability_v12_pct > 50  -- Using v12 since that matches your SnapRAID
ORDER BY failure_probability_v12_pct DESC;

DROP VIEW IF EXISTS smart_summary;
CREATE VIEW smart_summary AS
SELECT
    device_path,
    serial_number,
    temperature_celsius,
    power_on_days,
    error_count,
    failure_probability_v12_pct,  -- With attr 193
    failure_probability_v13_pct,  -- Without attr 193
    size_tb,
    disk_role
FROM smart_latest
ORDER BY
    CASE disk_role
        WHEN 'parity' THEN 1
        WHEN '2-parity' THEN 2
        ELSE 3
    END,
    device_path;

COMMENT ON COLUMN smart_metrics.failure_probability_v12_pct IS
    'Failure probability using SnapRAID <v13.0 algorithm (includes attribute 193 Load Cycle Count)';

COMMENT ON COLUMN smart_metrics.failure_probability_v13_pct IS
    'Failure probability using SnapRAID v13.0+ algorithm (excludes attribute 193)';