-- =======================================================
-- Migration: WIDE (hours, km) -> NARROW (unit, value_num)
-- This will create readings_new, copy data, then swap tables.
-- =======================================================

START TRANSACTION;

-- 1) create new table
CREATE TABLE IF NOT EXISTS readings_new LIKE readings;
-- If 'readings' doesn't exist yet, exit.
-- (Assumes you've run schema.sql first to define the target structure.)

-- 2) copy from old wide table structure:
-- expected columns on OLD: id, kapi_no, hours, km, recorded_at, location, device_id, received_at, source_ip
INSERT INTO readings_new (recorded_at, kapi_no, unit, value_num, location, device_id, source_ip, received_at)
SELECT recorded_at, kapi_no, 'hour' AS unit, hours AS value_num, location, IFNULL(device_id,''), source_ip, IFNULL(received_at, NOW())
FROM readings
WHERE hours IS NOT NULL;

INSERT INTO readings_new (recorded_at, kapi_no, unit, value_num, location, device_id, source_ip, received_at)
SELECT recorded_at, kapi_no, 'km'   AS unit, km    AS value_num, location, IFNULL(device_id,''), source_ip, IFNULL(received_at, NOW())
FROM readings
WHERE km IS NOT NULL;

-- 3) swap
RENAME TABLE readings TO readings_backup, readings_new TO readings;

COMMIT;

-- After migration, you may DROP the old table later:
-- DROP TABLE readings_backup;
