File Manager

✏️ Edit File: /home/u625410797/domains/monothailand.com/public_html/smart_panel/api/v1/history.php

<?php
// =============================================================================
// Smart_PanelMASTERPIECE - HISTORY API V2 (PHP AGGREGATION ENGINE)
// High-fidelity telemetry engine with robust carry-over energy and spike protection.
// Aligned with Flutter HistoryEntry model for zero-latency UI restoration.
// =============================================================================
header("Content-Type: application/json; charset=UTF-8");
header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: GET, OPTIONS');
header('Access-Control-Allow-Headers: Content-Type, Authorization, X-Api-Key');
require_once '../../config.php';

// [GOD MODE] Dual-Core Authentication Protocol
$auth = Security::requireAuth();
$userId = $auth['user_id'];

if ($_SERVER['REQUEST_METHOD'] !== 'GET') {
    Response::error(405, "Method Not Allowed");
}

/**
 * Parameters:
 * @param string $slot_id - Specific device UUID or 'all' for site overview
 * @param string $period  - day, week, month, year, lifetime
 * @param string $base_date - reference date (YYYY-MM-DD)
 * @param string $site_id - required if slot_id='all'
 */
$slotId   = $_GET['slot_id'] ?? null;
$period   = $_GET['period'] ?? 'day'; 
$baseDate = $_GET['base_date'] ?? date('Y-m-d');

if (!$slotId) {
    Response::error(400, "Missing required parameter: slot_id");
}

try {
    // --- DYNAMIC SCHEMA DETECTION [SAFETY FIRST] ---
    $allCols = $pdo->query("DESCRIBE telemetry")->fetchAll(PDO::FETCH_COLUMN);
    $hasL2   = in_array('energy_kwh2', $allCols);
    $hasL3   = in_array('energy_kwh3', $allCols);
    $ampCol  = in_array('amp_a', $allCols) ? 'amp_a' : 'current_a';

// 1. UNIFIED PRIORITY + FALLBACK RESOLUTION
    // Query ALL MACs (Master + Slave) but pick the best per time bucket.
    // Master and Slave measure the SAME load → never SUM, always Priority Pick.
    $targetMacs = [];
    $macPriority = []; // MAC => priority (1=PZEM Slave, 2=Master)
    $isAll = ($slotId === 'all');
    
    if (!$isAll) {
        $stmtInfo = $pdo->prepare("SELECT mac_address, mac_pzem FROM device_slots WHERE id = ? LIMIT 1");
        $stmtInfo->execute([$slotId]);
        $info = $stmtInfo->fetch(PDO::FETCH_ASSOC);
        if ($info) {
            $m = strtoupper(str_replace([':', '-', '.'], '', $info['mac_address']));
            $s = strtoupper(str_replace([':', '-', '.'], '', $info['mac_pzem'] ?? ''));
            // Collect BOTH MACs with priority for per-bucket fallback
            if (!empty($s) && $s !== '000000000000') {
                $targetMacs[] = $s;
                $macPriority[$s] = 1; // PZEM Slave = highest priority
            }
            if (!empty($m)) {
                $targetMacs[] = $m;
                $macPriority[$m] = 2; // Master = fallback
            }
        }
    } else {
        // Site-Wide: Aggregate all meters in a site
        $siteId = $_GET['site_id'] ?? null;
        
        if ($siteId) {
            $stmtAll = $pdo->prepare("SELECT ds.mac_address, ds.mac_pzem FROM device_slots ds JOIN rooms r ON ds.room_id = r.id WHERE r.site_id = ?");
            $stmtAll->execute([$siteId]);
        } else {
            $stmtAll = $pdo->prepare("SELECT ds.mac_address, ds.mac_pzem FROM device_slots ds JOIN rooms r ON ds.room_id = r.id JOIN sites s ON r.site_id = s.id WHERE s.user_id = ?");
            $stmtAll->execute([$userId]);
        }

foreach ($stmtAll->fetchAll() as $row) {
            $m = strtoupper(str_replace([':', '-', '.'], '', $row['mac_address']));
            $s = strtoupper(str_replace([':', '-', '.'], '', $row['mac_pzem'] ?? ''));
            if (!empty($s) && $s !== '000000000000') {
                $targetMacs[] = $s;
                $macPriority[$s] = 1;
            }
            if (!empty($m)) {
                $targetMacs[] = $m;
                $macPriority[$m] = 2;
            }
        }
    }
    $targetMacs = array_values(array_unique(array_filter($targetMacs)));

if (empty($targetMacs)) {
        Response::success([], "No physical nodes identified for calculation.");
    }

// 2. AGGREGATION CONFIGURATION (UX Optimization)
    $periodMap = [
        'day'        => ['interval' => '1 DAY',   'sec' => 900,   'fmt' => 'H:i',  'buckets' => 96], 
        'day_hourly' => ['interval' => '1 DAY',   'sec' => 3600,  'fmt' => 'H:00', 'buckets' => 24],
        'week'       => ['interval' => '7 DAY',   'sec' => 3600,  'fmt' => 'D H:00','buckets' => 168],
        'month'      => ['interval' => '30 DAY',  'sec' => 86400, 'fmt' => 'd M',  'buckets' => 30],
        'year'       => ['interval' => '365 DAY', 'sec' => 604800,'fmt' => 'M Y',  'buckets' => 52]
    ];
    $cfg = $periodMap[$period] ?? $periodMap['day'];
    
    // Boundary Logic
    $endRef   = date('Y-m-d H:i:s', strtotime($baseDate . ' +1 day'));
    $startRef = date('Y-m-d 00:00:00', strtotime($endRef . ' -' . $cfg['interval']));

// 3. ANALYTICS ENGINE CONFIG (Performance Optimization)
    // Multi-format MAC matching to leverage INDEX.
    $macListArr = [];
    foreach ($targetMacs as $m) {
        $mClean = strtoupper(str_replace([':', '-', '.'], '', $m));
        $macListArr[] = $pdo->quote($mClean);
        // Add colon version (standard PZEM style: AA:BB:CC:DD:EE:FF)
        $withColons = implode(':', str_split($mClean, 2));
        $macListArr[] = $pdo->quote($withColons);
    }
    $macList = implode(',', $macListArr);

// 4. SHARD DISCOVERY (Registry-Aware Lookup)

// We fetch all database segments that overlap with our requested interval.
    $stmtReg = $pdo->prepare("
        SELECT db_name, db_user, db_pass, start_date, end_date 
        FROM db_registry 
        WHERE start_date <= :search_end 
          AND (end_date >= :search_start OR end_date IS NULL)
        ORDER BY start_date ASC
    ");
    $stmtReg->execute([':search_start' => $startRef, ':search_end' => $endRef]);
    $segments = $stmtReg->fetchAll();

if (empty($segments)) {
        // Fallback: Default to legacy RapidSolar if registry is missing
        $segments = [[
            'db_name' => DB_NAME, 
            'db_user' => DB_USER, 
            'db_pass' => DB_PASS
        ]];
    }

$allRawResults = [];

// 4. MULTI-SHARD DATA GATHERING
    foreach ($segments as $seg) {
        $segPdo = Database::getRemoteConnection($seg['db_name'], $seg['db_user'], $seg['db_pass']);
        if (!$segPdo) continue;

// Detect schema per shard (safest approach)
        $sCols = [];
        try {
            $sCols = $segPdo->query("DESCRIBE telemetry")->fetchAll(PDO::FETCH_COLUMN);
        } catch (Exception $e) { continue; }

$sHasL2 = in_array('energy_kwh2', $sCols);
        $sHasL3 = in_array('energy_kwh3', $sCols);
        $sAmpCol = in_array('amp_a', $sCols) ? 'amp_a' : 'current_a';
        
        // Build Shard-Specific SQL logic
        $eLogic = "COALESCE(energy_kwh, 0)" . ($sHasL2 ? " + COALESCE(energy_kwh2,0)" : "") . ($sHasL3 ? " + COALESCE(energy_kwh3,0)" : "");
        $pLogic = "COALESCE(power_w, 0)" . ($sHasL2 ? " + COALESCE(power_w2,0)" : "") . ($sHasL3 ? " + COALESCE(power_w3,0)" : "");

$sql = "
            SELECT recorded_at, mac_address,
                   ($eLogic) as e,
                   ($pLogic) as p,
                   voltage_v as v, $sAmpCol as a,
                   temp_c, humidity_rh
            FROM telemetry
            WHERE (mac_address IN ($macList) OR slot_id = :slot_id)
            AND recorded_at >= :start AND recorded_at < :end
            ORDER BY recorded_at ASC
        ";

$stmt = $segPdo->prepare($sql);
        $stmt->execute([':start' => $startRef, ':end' => $endRef, ':slot_id' => $slotId]);
        
        while ($row = $stmt->fetch()) {
            $allRawResults[] = $row;
        }
        $segPdo = null; // Close connection
    }

// 5. GLOBAL SORT (Ensures carry-over energy logic works across shard boundaries)
    usort($allRawResults, function($a, $b) {
        return strtotime($a['recorded_at']) - strtotime($b['recorded_at']);
    });

// 5.5 ANCHOR ENERGY FETCH (Crucial for correct "Today" Energy)
    $lastE = [];
    $lastTimestamp = [];
    $lastP = [];
    foreach ($segments as $seg) {
        $segPdo = Database::getRemoteConnection($seg['db_name'], $seg['db_user'], $seg['db_pass']);
        if (!$segPdo) continue;
        $sCols = [];
        try { $sCols = $segPdo->query("DESCRIBE telemetry")->fetchAll(PDO::FETCH_COLUMN); } catch(Exception $e){continue;}
        $eLogic = "COALESCE(energy_kwh, 0)" . (in_array('energy_kwh2', $sCols) ? " + COALESCE(energy_kwh2,0)" : "") . (in_array('energy_kwh3', $sCols) ? " + COALESCE(energy_kwh3,0)" : "");
        foreach (explode(',', $macList) as $macStr) {
            $mac = trim($macStr, "'");
            if (isset($lastE[$mac])) continue;
            
            $mClean = strtoupper(preg_replace('/[^A-Fa-f0-9]/', '', $mac));
            $mColon = implode(':', str_split($mClean, 2));
            
            $stmt = $segPdo->prepare("SELECT recorded_at, $eLogic as e, power_w as p FROM telemetry WHERE (mac_address = ? OR mac_address = ?) AND recorded_at < ? ORDER BY recorded_at DESC LIMIT 1");
            $stmt->execute([$mClean, $mColon, $startRef]);
            $row = $stmt->fetch();
            if ($row) {
                $lastE[$mClean] = (double)$row['e'];
                $lastTimestamp[$mClean] = $row['recorded_at'];
                $lastP[$mClean] = (double)$row['p'];
            }
        }
        $segPdo = null;
    }

// 6. BUCKET INITIALIZATION
    $buckets = [];
    $startTimestamp = strtotime($startRef);
    for ($i = 0; $i < $cfg['buckets']; $i++) {
        $ts = $startTimestamp + ($i * $cfg['sec']);
        $buckets[$ts] = [
            'p' => 0, 'p_max' => 0, 'e' => 0, 'v' => 0, 'a' => 0, 't' => 0, 'h' => 0, 'cnt' => 0
        ];
    }

// 7. UNIFIED PRIORITY + FALLBACK PROCESSOR
    $bucketBestMac = [];
    foreach ($allRawResults as $r) {
        $ts = strtotime($r['recorded_at']);
        $bTs = floor($ts / $cfg['sec']) * $cfg['sec'];
        if (!isset($buckets[$bTs])) continue;
        $mac = strtoupper(preg_replace('/[^A-Fa-f0-9]/', '', $r['mac_address']));
        $priority = $macPriority[$mac] ?? 99;
        if (!isset($bucketBestMac[$bTs]) || $priority < $bucketBestMac[$bTs]) {
            $bucketBestMac[$bTs] = $priority;
        }
    }
    
    // Pass 2: Process records with carry-over energy from anchors
    foreach ($allRawResults as $r) {
        $ts = strtotime($r['recorded_at']);
        $bTs = floor($ts / $cfg['sec']) * $cfg['sec'];
        $mac = strtoupper(preg_replace('/[^A-Fa-f0-9]/', '', $r['mac_address']));
        $currE = (float)$r['e'];
        $currP = (float)$r['p'];
        
        // Strict Sensor Protection: Reject impossible power spikes (>50kW per unit)
        if ($currP > 50000) $currP = 0; 
        
        $priority = $macPriority[$mac] ?? 99;
        
        $dt = $ts - strtotime($lastTimestamp[$mac] ?? 'now');
        $prevE = $lastE[$mac] ?? null;
        $prevP = $lastP[$mac] ?? $currP;
        
        // Track state for all MACs for continuity
        $lastE[$mac] = $currE;
        $lastP[$mac] = $currP;
        $lastTimestamp[$mac] = $r['recorded_at'];
        
        if (!isset($buckets[$bTs])) continue;
        
        // Skip if this MAC is NOT the best priority for this bucket
        if (isset($bucketBestMac[$bTs]) && $priority > $bucketBestMac[$bTs]) {
            continue;
        }
        
        // --- ENERGY DELTA CALCULATION ---
        if ($prevE !== null) {
            $delta = $currE - $prevE;
            // Strict Consensus: kWh per interval (15m-1h) should never exceed 15kWh for AC units
            if ($delta > 0 && $delta < 15) {
                $buckets[$bTs]['e'] += $delta;
            } else if (($delta <= 0 || $delta >= 15) && $currP > 10 && $dt > 0 && $dt < 3600) {
                // Fallback: Riemann Sum Integration (Power * Time)
                // This is critical when device resets energy meter or sends anomaly
                $avgP = ($currP + $prevP) / 2;
                if ($avgP > 20000) $avgP = 2000; // Safety cap to 2kW if power spike detected
                $integratedDelta = ($avgP * $dt) / 3600 / 1000;
                $buckets[$bTs]['e'] += $integratedDelta;
            }
        }
        
        // --- OTHER METRICS ---
        $buckets[$bTs]['p'] += $currP;
        $buckets[$bTs]['p_max'] = max($buckets[$bTs]['p_max'], $currP);
        $buckets[$bTs]['v'] += (float)$r['v'];
        $buckets[$bTs]['a'] += (float)$r['a'];
        $buckets[$bTs]['t'] += (float)($r['temp_c'] ?? 0);
        $buckets[$bTs]['h'] += (float)($r['humidity_rh'] ?? 0);
        $buckets[$bTs]['cnt']++;
    }

// 6. FINALIZATION & UX NORMALIZATION
    // Aligned with HistoryEntry model for immediate data-binding.
    $output = [];
    foreach ($buckets as $ts => $b) {
        $cnt = $b['cnt'] ?: 1;
        $output[] = [
            'time'    => date('Y-m-d\TH:i:s', $ts),
            'label'   => date($cfg['fmt'], $ts),
            'p'       => round($b['p'] / $cnt, 2),
            'p_max'   => round($b['p_max'], 2),
            'e'       => round($b['e'], 4),
            'v'       => round($b['v'] / $cnt, 1),
            'a'       => round($b['a'] / $cnt, 2),
            'temp'    => round($b['t'] / $cnt, 1),
            'hum'     => round($b['h'] / $cnt, 1),
            'samples' => $b['cnt']
        ];
    }

Response::success($output, "Masterpiece Analytics Engine Delivered.");

} catch (Exception $e) {
    Response::error(500, "Analytics Engine Failed", $e->getMessage());
}

Batal

⬅ Naik ke api 83 item

Nama	Tipe	Ukuran	Diubah	Aksi
📁admin	dir	—	2026-04-30 04:28	📂 Buka
📁alerts	dir	—	2026-04-30 04:28	📂 Buka
📁auth	dir	—	2026-04-30 04:28	📂 Buka
📁dashboard	dir	—	2026-04-30 07:02	📂 Buka
📁industrial	dir	—	2026-07-06 17:04	📂 Buka
📁ota	dir	—	2026-04-30 04:28	📂 Buka
🐘active_ping.php	php	744 B	2026-04-30 09:54	✏️ Edit 👁️ View
🐘active_tracker.php	php	2.7 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘alert_history.php	php	4.8 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘analytics_engine.php	php	5.8 KB	2026-04-30 09:54	✏️ Edit 👁️ View
📝asset_fix_summary.txt	txt	805 B	2026-04-30 09:54	✏️ Edit 👁️ View
🐘audit_logs.php	php	1.7 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘backfill_energy.php	php	935 B	2026-04-30 09:54	✏️ Edit 👁️ View
🐘bind_hardware.php	php	4.4 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘bot_slots.php	php	2.3 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘check_serial.php	php	4.6 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘complete_command.php	php	2.8 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘control.php	php	13.5 KB	2026-05-01 01:53	✏️ Edit 👁️ View
🐘control_mass.php	php	1.6 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘control_route.php	php	2.9 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘db_explain.php	php	899 B	2026-04-30 09:54	✏️ Edit 👁️ View
🐘db_optimize.php	php	1.1 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘debug_cumulative.php	php	1.2 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘debug_db.php	php	3.1 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘debug_db_check.php	php	505 B	2026-04-30 09:54	✏️ Edit 👁️ View
🐘debug_energy.php	php	649 B	2026-04-30 09:54	✏️ Edit 👁️ View
🐘debug_slot.php	php	600 B	2026-04-30 09:54	✏️ Edit 👁️ View
🐘debug_wana.php	php	721 B	2026-04-30 09:54	✏️ Edit 👁️ View
🐘delete_node_permanent.php	php	6.2 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘delete_site.php	php	4.3 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘delete_slot.php	php	2.3 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘dump_debug.php	php	850 B	2026-04-30 09:54	✏️ Edit 👁️ View
🐘energy_anchor.php	php	1.8 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘energy_integration_engine.php	php	2.5 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘firmware_core.php	php	3.9 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘fix_gateway_schema.php	php	1.3 KB	2026-05-01 01:52	✏️ Edit 👁️ View
🐘fix_schema.php	php	6.4 KB	2026-05-01 01:52	✏️ Edit 👁️ View
🐘fleet_stats.php	php	4.2 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘geo_intelligence.php	php	3.8 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘get_commands.php	php	6.1 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘get_hidden_nodes.php	php	1009 B	2026-04-30 09:54	✏️ Edit 👁️ View
🐘get_hierarchy.php	php	16.6 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘health_analysis.php	php	6.2 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘history.php	php	13.6 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘history_new_bug.php	php	17.8 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘history_precision.php	php	9.4 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘history_v42.php	php	8.4 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘hive_core.php	php	5.9 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘inspect_db.php	php	1.6 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘inspect_schema.php	php	734 B	2026-04-30 09:54	✏️ Edit 👁️ View
🐘latest.php	php	17.3 KB	2026-05-01 01:52	✏️ Edit 👁️ View
🐘list_devices.php	php	271 B	2026-04-30 09:54	✏️ Edit 👁️ View
🐘list_slots.php	php	1.8 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘mass_command.php	php	5.2 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘mesh_sync.php	php	2.7 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘migrate_3phase.php	php	2.4 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘migrate_commands_signature.php	php	298 B	2026-04-30 09:54	✏️ Edit 👁️ View
🐘move_hardware.php	php	4.8 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘ota_manager.php	php	3.6 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘peek_data.php	php	452 B	2026-04-30 09:54	✏️ Edit 👁️ View
🐘peek_recent.php	php	407 B	2026-04-30 09:54	✏️ Edit 👁️ View
🐘push_structure.php	php	12.5 KB	2026-05-01 01:53	✏️ Edit 👁️ View
🐘register_hardware.php	php	2.8 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘room_status.php	php	4.9 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘security_digest.php	php	3.2 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘seed_admin.php	php	1.4 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘server_health.php	php	1.4 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘set_node_visibility.php	php	1.9 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘set_room_master.php	php	1.9 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘sharing.php	php	4.9 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘simulate_history.php	php	5.9 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘sync.php	php	8.1 KB	2026-05-01 01:52	✏️ Edit 👁️ View
🐘sync_location_slots.php	php	3.2 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘system_config.php	php	1.9 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘telemetry.php	php	49.8 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘telemetry_batch.php	php	17.4 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘telemetry_engine.php	php	5.9 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘telemetry_raw.php	php	6.1 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘telemetry_v2.php	php	11 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘unlink_device.php	php	2.1 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘update_sensor_config.php	php	3.2 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘update_user.php	php	4.1 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘user_manager.php	php	6.3 KB	2026-04-30 09:54	✏️ Edit 👁️ View