water/server/scripts/migrate_water_device_usage.php

<?php
/** Add daily usage history; preserve existing device, stock and entitlement values. */
require dirname(__DIR__) . '/vendor/autoload.php';
\Dotenv\Dotenv::createUnsafeMutable(dirname(__DIR__))->load();
\support\App::loadAllConfig(['route']);

use plugin\saiadmin\app\model\water\WaterDevice;

if (!in_array('--apply', $argv, true)) {
    echo "Plan: create device/day usage table and copy existing dated usage snapshots.\n";
    exit;
}

$db = (new WaterDevice())->getConnection();
$db->statement('CREATE TABLE IF NOT EXISTS sa_water_device_usage (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    device_id BIGINT UNSIGNED NOT NULL,
    day DATE NOT NULL,
    liters DECIMAL(10,2) UNSIGNED NOT NULL,
    reported_at DATETIME NULL,
    UNIQUE KEY device_day (device_id, day),
    KEY usage_day (day)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4');

// Only copy the known snapshot date. Re-running never replaces an existing daily record.
$copied = $db->affectingStatement('INSERT IGNORE INTO sa_water_device_usage (device_id, day, liters, reported_at)
    SELECT id, telemetry_date, today_liters, NULL FROM sa_water_device
    WHERE telemetry_date IS NOT NULL AND today_liters IS NOT NULL AND today_liters >= 0');
echo "Applied device usage migration; copied $copied known snapshots. Existing device data preserved.\n";