-- Populate devices table with actual drive data
-- Based on device_mappings.txt and snapraid smart output

-- Device 1: /dev/sda [parity]
INSERT INTO devices (device_wwn, device_path, serial_number, model, manufacturer, capacity_bytes, size_tb, disk_role, notes)
VALUES (
    '0x5000c500744487c5',
    '/dev/sda',
    'ZX20AAMV',
    'WDC WD200EFAX',
    'Western Digital',
    20000000000000,
    20.0,
    'parity',
    'Parity drive - 636 days old'
);

-- Device 2: /dev/sdb [d2]
INSERT INTO devices (device_wwn, device_path, serial_number, model, manufacturer, capacity_bytes, size_tb, disk_role, notes)
VALUES (
    '0x5000c500e7a6a520',
    '/dev/sdb',
    'ZX22EJ3Y',
    'WDC WD200EFAX',
    'Western Digital',
    20000000000000,
    20.0,
    'd2',
    'Data drive 2 - 637 days old'
);

-- Device 3: /dev/sdc [d1]
INSERT INTO devices (device_wwn, device_path, serial_number, model, manufacturer, capacity_bytes, size_tb, disk_role, notes)
VALUES (
    '0x5000c500c570663e',
    '/dev/sdc',
    'ZLW0A3QJ',
    'WDC WD120EFAX',
    'Western Digital',
    12000000000000,
    12.0,
    'd1',
    'Data drive 1 - 1968 days old - HIGH RISK 81%'
);

-- Device 4: /dev/sdd [d5]
INSERT INTO devices (device_wwn, device_path, serial_number, model, manufacturer, capacity_bytes, size_tb, disk_role, notes)
VALUES (
    '0x5000c500e584b9c8',
    '/dev/sdd',
    'ZTN1BNEZ',
    'WDC WD120EFAX',
    'Western Digital',
    12000000000000,
    12.0,
    'd5',
    'Data drive 5 - 1014 days old'
);

-- Device 5: /dev/sde (unused)
INSERT INTO devices (device_wwn, device_path, serial_number, model, manufacturer, capacity_bytes, size_tb, disk_role, notes)
VALUES (
    '0x5000cca234c69287',
    '/dev/sde',
    'YVGGG6EC',
    'WDC WD30EFRX',
    'Western Digital',
    3000000000000,
    3.0,
    '-',
    'Unused drive - 1983 days old'
);

-- Device 6: /dev/sdf (unused)
INSERT INTO devices (device_wwn, device_path, serial_number, model, manufacturer, capacity_bytes, size_tb, disk_role, notes)
VALUES (
    '0x5000c500c455c45e',
    '/dev/sdf',
    'ZA1GX9F4',
    'WDC WD80EFAX',
    'Western Digital',
    8000000000000,
    8.0,
    '-',
    'Unused drive - 2072 days old - HIGH RISK 84%'
);

-- Device 7: /dev/sdg [2-parity]
INSERT INTO devices (device_wwn, device_path, serial_number, model, manufacturer, capacity_bytes, size_tb, disk_role, notes)
VALUES (
    '0x5000cca26ae062f3',
    '/dev/sdg',
    '2TJ97M6D',
    'WDC WD100EFAX',
    'Western Digital',
    10000000000000,
    10.0,
    '2-parity',
    'Second parity drive - 2540 days old'
);

-- Device 8: /dev/sdh [2-parity]
INSERT INTO devices (device_wwn, device_path, serial_number, model, manufacturer, capacity_bytes, size_tb, disk_role, notes)
VALUES (
    '0x5000cca266e83d77',
    '/dev/sdh',
    '7JJVJ65C',
    'WDC WD100EFAX',
    'Western Digital',
    10000000000000,
    10.0,
    '2-parity',
    'Second parity drive - 2354 days old - 1 error reported'
);

-- Device 9: /dev/sdi (unused)
INSERT INTO devices (device_wwn, device_path, serial_number, model, manufacturer, capacity_bytes, size_tb, disk_role, notes)
VALUES (
    '0x5000c500c46b0832',
    '/dev/sdi',
    'ZA1GW0XM',
    'WDC WD80EFAX',
    'Western Digital',
    8000000000000,
    8.0,
    '-',
    'Unused drive - 2074 days old - HIGH RISK 84%'
);

-- Device 10: /dev/nvme0n1 [OS drive] - Note: WWN format different for NVMe
-- NVMe devices may not report WWN in the same format, skipping for now
-- You can add this manually if needed after checking actual WWN from InfluxDB
-- INSERT INTO devices (device_wwn, device_path, serial_number, model, manufacturer, capacity_bytes, size_tb, disk_role, notes)
-- VALUES (
--     '25243x801438',  -- May need different format
--     '/dev/nvme0n1',
--     '25243X801438',
--     'NVMe SSD',
--     'Unknown',
--     NULL,
--     NULL,
--     'os',
--     'Operating system drive'
-- );

-- Verify the data
SELECT device_path, serial_number, size_tb, disk_role, notes
FROM devices
ORDER BY device_path;