<?php
if (session_status() === PHP_SESSION_NONE) {
    session_start();
}
ob_start(); // start buffer to prevent stray whitespace breaking JSON
header('Content-Type: application/json; charset=utf-8');

require_once __DIR__ . '/db_connect.php';

function json_response($data, int $code = 200) {
  http_response_code($code);
  header('Content-Type: application/json');
  echo json_encode($data);
  exit;
}

function getMeters(array $filter = []): array {
  global $conn_rbe;
  $clauses = [];
  $params = [];

  if (!empty($filter['landlord_id'])) {
    $clauses[] = 'landlord_id = ?';
    $params[] = (int)$filter['landlord_id'];
  }
  if (!empty($filter['source'])) {
    $clauses[] = 'source = ?';
    $params[] = (string)$filter['source'];
  }
  if (!empty($filter['is_active'])) {
    $clauses[] = 'is_active = ?';
    $params[] = (int)$filter['is_active'];
  }

  $whereSQL = $clauses ? ('WHERE ' . implode(' AND ', $clauses)) : '';

  $limit = isset($filter['limit']) ? (int)$filter['limit'] : 200;
  if ($limit < 1) $limit = 1;
  if ($limit > 500) $limit = 500; // hard cap

  $sql = "SELECT source, meter_id, serial_number, meter_type, property_id, landlord_id, tenant_id, agent_id,
                 last_synced_at, landlord_name, tenant_name, property_number, address, created_at, is_active
          FROM vw_meters_combined
          $whereSQL
          ORDER BY created_at DESC
          LIMIT $limit";

  $stmt = $conn_rbe->prepare($sql);
  $stmt->execute($params);
  return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
}

function getDashboardStats(): array {
  global $conn_rbe;
  $stats = [
    'total_meters'    => 0,
    'active_tenants'  => 0,
    'total_landlords' => 0,
  ];
  try {
    $stats['total_meters']    = (int)$conn_rbe->query('SELECT COUNT(*) FROM vw_meters_combined')->fetchColumn();
    $stats['active_tenants']  = (int)$conn_rbe->query('SELECT COUNT(DISTINCT tenant_id) FROM vw_meters_combined WHERE tenant_id IS NOT NULL')->fetchColumn();
    $stats['total_landlords'] = (int)$conn_rbe->query('SELECT COUNT(DISTINCT landlord_id) FROM vw_meters_combined')->fetchColumn();
  } catch (Throwable $e) {
    // leave defaults
  }
  return $stats;
}

function toggleWallet(int $tenant_id, int $enabled): array {
  global $conn_rbe;
  $stmt = $conn_rbe->prepare('UPDATE tenant_wallet SET is_enabled = ? WHERE tenant_id = ?');
  $stmt->execute([$enabled ? 1 : 0, $tenant_id]);
  return ['status' => 'ok'];
}

function compareBulkMeters(int $bulk_id): array {
  // Placeholder for future comparison logic
  return [
    'status' => 'todo',
    'bulk_id' => $bulk_id,
    'message' => 'Comparison logic pending implementation.'
  ];
}

function getLandlords(): array {
  global $conn_rbe;
  $sql = "SELECT DISTINCT landlord_id AS id, landlord_name AS name
          FROM vw_meters_combined
          WHERE landlord_id IS NOT NULL
          ORDER BY landlord_name ASC";
  $rows = $conn_rbe->query($sql)->fetchAll(PDO::FETCH_ASSOC) ?: [];
  return array_values(array_filter($rows, function($r){ return !empty($r['id']); }));
}

function getHealthStatus(): array {
  global $conn_rbe;
  $out = [
    'db' => 'ok',
    'flecto_api' => 'unknown',
    'iammeter_api' => 'unknown',
    'cron_status' => 'unknown',
    'token_valid' => 'unknown',
  ];
  try {
    $conn_rbe->query('SELECT 1');
    $out['db'] = 'ok';
  } catch (Throwable $e) {
    $out['db'] = 'down';
  }

  try {
    $lastF = $conn_rbe->query("SELECT MAX(GREATEST(COALESCE(created_at,'1000-01-01'), COALESCE(fetched_at,'1000-01-01'))) FROM flecto_meter_readings")->fetchColumn();
    if ($lastF && strtotime($lastF) >= (time() - 2*3600)) $out['flecto_api'] = 'ok'; else $out['flecto_api'] = 'late';
  } catch (Throwable $e) {}

  try {
    $lastI = $conn_rbe->query("SELECT MAX(reading_timestamp) FROM smart_meter_readings")->fetchColumn();
    if ($lastI && strtotime($lastI) >= (time() - 2*3600)) $out['iammeter_api'] = 'ok'; else $out['iammeter_api'] = 'late';
  } catch (Throwable $e) {}

  // Cron: prefer cron_logs table if present; fallback to any reading within 30m
  try {
    $hasCron = $conn_rbe->query("SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'cron_logs'")->fetchColumn();
    $lastCron = null;
    if ($hasCron) {
      $lastCron = $conn_rbe->query("SELECT MAX(run_at) FROM cron_logs")->fetchColumn();
    } else {
      $lastCron = $conn_rbe->query("SELECT MAX(ts) FROM (
        SELECT MAX(reading_timestamp) AS ts FROM smart_meter_readings
        UNION ALL
        SELECT MAX(COALESCE(created_at, fetched_at)) FROM flecto_meter_readings
        UNION ALL
        SELECT MAX(reading_datetime) FROM water_meter_readings
      ) t")->fetchColumn();
    }
    if ($lastCron && strtotime($lastCron) >= (time() - 1800)) $out['cron_status'] = 'ok'; else $out['cron_status'] = 'late';
  } catch (Throwable $e) {}

  // Token validity: try generic api_tokens table; fallback ok
  try {
    $hasTok = $conn_rbe->query("SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'api_tokens'")->fetchColumn();
    if ($hasTok) {
      $c = (int)$conn_rbe->query("SELECT COUNT(*) FROM api_tokens WHERE expires_at > NOW()")->fetchColumn();
      $out['token_valid'] = $c > 0 ? 'ok' : 'invalid';
    } else {
      $out['token_valid'] = 'ok';
    }
  } catch (Throwable $e) {}

  return $out;
}

function getInsights(): array {
  global $conn_rbe;
  $out = [ 'kwh_today'=>0.0, 'kl_today'=>0.0, 'revenue_today'=>0.0, 'offline_meters'=>0, 'active_alerts'=>0 ];
  try {
    // Energy (Wh → kWh)
    $stmt = $conn_rbe->query("SELECT ROUND(SUM(energy_total)/1000, 2) AS kwh_today FROM smart_meter_readings WHERE DATE(reading_timestamp)=CURDATE()");
    $out['kwh_today'] = (float)($stmt->fetchColumn() ?? 0);
  } catch (Throwable $e) {}

  // Override kWh today using unified energy engine (FLECTO + IAMMETER)
  try {
    $kwh_today = 0.0;

    // FLECTO: import registers (reg_a + reg_h + reg_l), delta per serial for today
    try {
      $sqlF = "
        SELECT ROUND(COALESCE(SUM(
          (COALESCE(max_reg_a,0) - COALESCE(min_reg_a,0)) +
          (COALESCE(max_reg_h,0) - COALESCE(min_reg_h,0)) +
          (COALESCE(max_reg_l,0) - COALESCE(min_reg_l,0))
        ),0), 3) AS kwh_today
        FROM (
          SELECT
            serial,
            MIN(reg_a) AS min_reg_a,
            MAX(reg_a) AS max_reg_a,
            MIN(reg_h) AS min_reg_h,
            MAX(reg_h) AS max_reg_h,
            MIN(reg_l) AS min_reg_l,
            MAX(reg_l) AS max_reg_l
          FROM flecto_meter_readings
          WHERE DATE(created_at) = CURDATE()
          GROUP BY serial
        ) t
      ";
      $stmtF = $conn_rbe->query($sqlF);
      $kwh_today += (float)($stmtF->fetchColumn() ?? 0);
    } catch (Throwable $eInner) {}

    // IAMMETER: cumulative Wh per phase; daily delta per meter/phase -> kWh
    try {
      $sqlI = "
        SELECT ROUND(COALESCE(SUM(GREATEST(last_wh - first_wh, 0)),0) / 1000, 3) AS kwh_today
        FROM (
          SELECT
            meter_id,
            phase,
            MIN(energy_total) AS first_wh,
            MAX(energy_total) AS last_wh
          FROM smart_meter_readings
          WHERE DATE(reading_timestamp) = CURDATE()
          GROUP BY meter_id, phase
        ) p
      ";
      $stmtI = $conn_rbe->query($sqlI);
      $kwh_today += (float)($stmtI->fetchColumn() ?? 0);
    } catch (Throwable $eInner) {}

    $out['kwh_today'] = round($kwh_today, 2);
  } catch (Throwable $e) {}

  try {
  // Water (liters → kiloliters based on daily difference per meter)
  $stmt2 = $conn_rbe->query("
    SELECT ROUND(SUM(usage_liters)/1000, 2) AS kl_today
    FROM (
      SELECT 
        (MAX(volume_liters) - MIN(volume_liters)) AS usage_liters
      FROM water_meter_readings
      WHERE DATE(reading_datetime) = CURDATE()
      GROUP BY serial_number
    ) AS usage_data
  ");
  $out['kl_today'] = (float)($stmt2->fetchColumn() ?? 0);
} catch (Throwable $e) {}

// Unified offline meter detection using VW_METERS_COMBINED
try {
    $stmt = $conn_rbe->query("
        SELECT COUNT(*) 
        FROM vw_meters_combined
        WHERE 
            last_synced_at IS NULL
            OR last_synced_at = '0000-00-00 00:00:00'
            OR last_synced_at < (NOW() - INTERVAL 2 HOUR)
    ");
    $out['offline_meters'] = (int)($stmt->fetchColumn() ?? 0);

} catch (Throwable $e) {
    $out['offline_meters'] = 0;
}


  // Active alerts — mirrors offline detection results if system_alerts not used
  try {
    $hasAlerts = $conn_rbe->query("
      SELECT 1 FROM information_schema.tables 
      WHERE table_schema = DATABASE() AND table_name = 'system_alerts'
    ")->fetchColumn();

    if ($hasAlerts) {
      $out['active_alerts'] = (int)$conn_rbe->query("
        SELECT COUNT(*) FROM system_alerts WHERE status IN ('new','open')
      ")->fetchColumn();
    } else {
      $out['active_alerts'] = $out['offline_meters'];
    }
  } catch (Throwable $e) {
    $out['active_alerts'] = $out['offline_meters'];
  }



  try {
    $hasAlerts = $conn_rbe->query("SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'system_alerts'")->fetchColumn();
    if ($hasAlerts) {
      $out['active_alerts'] = (int)$conn_rbe->query("SELECT COUNT(*) FROM system_alerts WHERE status IN ('new','open')")
                                      ->fetchColumn();
    } else {
      $out['active_alerts'] = $out['offline_meters'];
    }
  } catch (Throwable $e) {}
  return $out;
}

function getChartEnergyWater(): array {
  global $conn_rbe;
  // Accept optional from/to (YYYY-MM-DD)
  $from = $_GET['from'] ?? null; $to = $_GET['to'] ?? null;
  $days = [];
  if ($from && $to) {
    $start = strtotime($from); $end = strtotime($to);
    if ($start && $end && $end >= $start) {
      for ($t=$start; $t <= $end; $t+=86400) { $days[] = date('Y-m-d', $t); }
    }
  }
  if (!$days) { for ($i=6; $i>=0; $i--) { $days[] = date('Y-m-d', strtotime("-$i day")); } }
  $energy = array_fill(0, 7, 0.0);
  $water  = array_fill(0, 7, 0.0);
  try {
    $sqlE = 'SELECT DATE(reading_timestamp) d, ROUND(SUM(energy_total)/1000,2) v FROM smart_meter_readings WHERE 1';
    if ($from) $sqlE .= " AND DATE(reading_timestamp) >= '".addslashes($from)."'";
    if ($to)   $sqlE .= " AND DATE(reading_timestamp) <= '".addslashes($to)."'";
    if (!$from || !$to) $sqlE .= ' AND reading_timestamp >= CURDATE() - INTERVAL 7 DAY';
    $sqlE .= ' GROUP BY d';
    $stmt = $conn_rbe->query($sqlE);
    while ($r = $stmt->fetch(PDO::FETCH_ASSOC)) {
      $idx = array_search($r['d'], $days, true);
      if ($idx !== false) $energy[$idx] = (float)$r['v'];
    }
  } catch (Throwable $e) {}
  try {
    $sqlW = 'SELECT DATE(reading_datetime) d, ROUND(SUM(volume_liters)/1000,2) v FROM water_meter_readings WHERE 1';
    if ($from) $sqlW .= " AND DATE(reading_datetime) >= '".addslashes($from)."'";
    if ($to)   $sqlW .= " AND DATE(reading_datetime) <= '".addslashes($to)."'";
    if (!$from || !$to) $sqlW .= ' AND reading_datetime >= CURDATE() - INTERVAL 7 DAY';
    $sqlW .= ' GROUP BY d';
    $stmt = $conn_rbe->query($sqlW);
    while ($r = $stmt->fetch(PDO::FETCH_ASSOC)) {
      $idx = array_search($r['d'], $days, true);
      if ($idx !== false) $water[$idx] = (float)$r['v'];
    }
  } catch (Throwable $e) {}
  $labels = array_map(function($d){ return date('D', strtotime($d)); }, $days);
  return [
    'labels' => $labels,
    'datasets' => [
      ['label' => 'Energy (kWh)','data' => $energy],
      ['label' => 'Water (kL)','data' => $water]
    ]
  ];
}

function getChartWallet(): array {
  global $conn_rbe;
  $from = $_GET['from'] ?? null; $to = $_GET['to'] ?? null;
  $days = [];
  if ($from && $to) {
    $start = strtotime($from); $end = strtotime($to);
    if ($start && $end && $end >= $start) { for ($t=$start; $t <= $end; $t+=86400) { $days[] = date('Y-m-d', $t); } }
  }
  if (!$days) { for ($i=29; $i>=0; $i--) { $days[] = date('Y-m-d', strtotime("-$i day")); } }
  $dep = array_fill(0, 30, 0.0);
  $pay = array_fill(0, 30, 0.0);
  try {
    $sqlD = 'SELECT DATE(created_at) d, ROUND(SUM(amount),2) v FROM tenant_wallet_deposits WHERE 1';
    if ($from) $sqlD .= " AND DATE(created_at) >= '".addslashes($from)."'";
    if ($to)   $sqlD .= " AND DATE(created_at) <= '".addslashes($to)."'";
    if (!$from || !$to) $sqlD .= ' AND created_at >= CURDATE() - INTERVAL 30 DAY';
    $sqlD .= ' GROUP BY d';
    $stmt = $conn_rbe->query($sqlD);
    while ($r = $stmt->fetch(PDO::FETCH_ASSOC)) {
      $idx = array_search($r['d'], $days, true);
      if ($idx !== false) $dep[$idx] = (float)$r['v'];
    }
  } catch (Throwable $e) {}
  try {
    $sqlP = 'SELECT DATE(created_at) d, ROUND(SUM(amount),2) v FROM landlord_wallet_payouts WHERE 1';
    if ($from) $sqlP .= " AND DATE(created_at) >= '".addslashes($from)."'";
    if ($to)   $sqlP .= " AND DATE(created_at) <= '".addslashes($to)."'";
    if (!$from || !$to) $sqlP .= ' AND created_at >= CURDATE() - INTERVAL 30 DAY';
    $sqlP .= ' GROUP BY d';
    $stmt = $conn_rbe->query($sqlP);
    while ($r = $stmt->fetch(PDO::FETCH_ASSOC)) {
      $idx = array_search($r['d'], $days, true);
      if ($idx !== false) $pay[$idx] = (float)$r['v'];
    }
  } catch (Throwable $e) {}
  $labels = array_map(function($d){ return date('d M', strtotime($d)); }, $days);
  return [
    'labels' => $labels,
    'datasets' => [
      ['label' => 'Deposits (R)','data' => $dep],
      ['label' => 'Payouts (R)','data' => $pay]
    ]
  ];
}

function getFinanceSummary(): array {
  global $conn_rbe;
  $out = [ 'deposits_total'=>0.0, 'payouts_total'=>0.0, 'net'=>0.0, 'top_landlords'=>[] ];
  try { $out['deposits_total'] = (float)$conn_rbe->query('SELECT ROUND(COALESCE(SUM(amount),0),2) FROM tenant_wallet_deposits')->fetchColumn(); } catch (Throwable $e) {}
  try { $out['payouts_total']  = (float)$conn_rbe->query('SELECT ROUND(COALESCE(SUM(amount),0),2) FROM landlord_wallet_payouts')->fetchColumn(); } catch (Throwable $e) {}
  $out['net'] = round($out['deposits_total'] - $out['payouts_total'], 2);
  try {
    $stmt = $conn_rbe->query("SELECT p.landlord_id, u.fullname AS landlord_name, ROUND(COALESCE(SUM(p.amount),0),2) AS total
                               FROM landlord_wallet_payouts p
                               LEFT JOIN rbe_users u ON u.id = p.landlord_id
                               GROUP BY p.landlord_id, landlord_name
                               ORDER BY total DESC
                               LIMIT 5");
    $out['top_landlords'] = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
  } catch (Throwable $e) {}
  return $out;
}

function getAlerts(): array {
    global $conn_rbe;

    try {
        // Convert stored UTC timestamps → SAST before comparing
        $stmt = $conn_rbe->query("
            SELECT 
                source AS type,
                serial_number AS meter,
                property_number AS property,
                last_synced_at
            FROM vw_meters_combined
            WHERE 
                last_synced_at IS NULL
                OR last_synced_at = '0000-00-00 00:00:00'
                OR CONVERT_TZ(last_synced_at, '+00:00', '+02:00') < (NOW() - INTERVAL 2 HOUR)
            ORDER BY last_synced_at ASC
            LIMIT 10
        ");

        $rows = [];
        while ($r = $stmt->fetch(PDO::FETCH_ASSOC)) {

            $ts = $r['last_synced_at'];

            // Convert UTC → SAST (+02:00)
            $lastSync = $ts ? strtotime($ts . ' UTC') + 7200 : 0;

            // Nice “time ago”
            if ($lastSync > 0) {
                $diff  = time() - $lastSync;
                $hours = floor($diff / 3600);
                $mins  = floor(($diff % 3600) / 60);
                $ago   = ($hours > 0 ? "{$hours}h " : '') . "{$mins}m ago";
            } else {
                $ago = "Never synced";
            }

            switch ($r['type']) {
                case 'IAMMETER':
                    $message = "IAMMETER meter {$r['meter']} has not synced in {$ago}.";
                    break;
                case 'FLECTO':
                    $message = "FLECTO meter {$r['meter']} last synced {$ago}.";
                    break;
                case 'WATER':
                    $message = "Water meter {$r['meter']} last reported usage {$ago}.";
                    break;
                default:
                    $message = "Meter {$r['meter']} is offline (last sync {$ago}).";
            }

            $rows[] = [
                'type'     => $r['type'],
                'meter'    => $r['meter'],
                'property' => $r['property'] ?: 'Unknown',
                'time'     => $lastSync ? date('H:i', $lastSync) : '—',
                'status'   => 'new',
                'message'  => $message
            ];
        }

        return $rows;

    } catch (Throwable $e) {
        return [[
            'type'     => 'Error',
            'meter'    => '—',
            'property' => '—',
            'time'     => '—',
            'status'   => 'error',
            'message'  => $e->getMessage()
        ]];
    }
}




function getActivity(): array {
  global $conn_rbe;
  $events = [];

  try {
    // IAMMETER last sync
    $ts = $conn_rbe->query("SELECT MAX(reading_timestamp) FROM smart_meter_readings")->fetchColumn();
    if ($ts) $events[] = ['ts' => $ts, 'ev' => '⚡ Latest IAMMETER sync'];

    // FLECTO last sync (UTC→SAST conversion)
    $ts = $conn_rbe->query("
      SELECT MAX(GREATEST(
        CONVERT_TZ(created_at, '+00:00', '+02:00'),
        COALESCE(fetched_at, '1000-01-01')
      )) FROM flecto_meter_readings
    ")->fetchColumn();
    if ($ts) $events[] = ['ts' => $ts, 'ev' => '🔌 Latest FLECTO sync'];

    // WATER last usage (actual usage increase)
    $ts = $conn_rbe->query("
      SELECT MAX(CONVERT_TZ(r.reading_datetime, '+00:00', '+02:00')) 
      FROM (
        SELECT w1.serial_number, w1.reading_datetime, w1.volume_liters,
               LAG(w1.volume_liters) OVER (PARTITION BY w1.serial_number ORDER BY w1.reading_datetime) AS prev_vol
        FROM water_meter_readings w1
      ) r
      WHERE (r.volume_liters - r.prev_vol) > 0
    ")->fetchColumn();
    if ($ts) $events[] = ['ts' => $ts, 'ev' => '💧 Latest WATER usage detected'];

    // WALLET last deposit or payout
    $ts = $conn_rbe->query("
      SELECT MAX(ts) FROM (
        SELECT MAX(created_at) AS ts FROM tenant_wallet_deposits
        UNION ALL
        SELECT MAX(created_at) AS ts FROM landlord_wallet_payouts
      ) x
    ")->fetchColumn();
    if ($ts) $events[] = ['ts' => $ts, 'ev' => '💰 Latest WALLET transaction'];

  } catch (Throwable $e) {
    // no fatal crash; show empty if needed
  }

  // Sort by timestamp desc (latest first)
  usort($events, fn($a, $b) => strcmp($b['ts'] ?? '', $a['ts'] ?? ''));
  $events = array_slice($events, 0, 10);

  // Format output with date and time
  return array_map(function($r) {
    $formatted = $r['ts'] ? date('Y-m-d H:i', strtotime($r['ts'])) : '';
    return [
      'time'  => $formatted,
      'event' => $r['ev'] ?? ''
    ];
  }, $events);
}


function triggerManualSync(): array {
  // Placeholder: insert into cron_logs as a trigger marker if table exists
  global $conn_rbe;
  try {
    $has = $conn_rbe->query("SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name='cron_logs'")->fetchColumn();
    if ($has) {
      $stmt = $conn_rbe->prepare("INSERT INTO cron_logs (job, run_at, status, note) VALUES ('manual_sync', NOW(), 'queued', 'Triggered from dashboard')");
      $stmt->execute();
    }
  } catch (Throwable $e) {}
  return ['status'=>'ok','message'=>'Manual sync triggered successfully'];
}

function clearCache(): array {
  global $conn_rbe;
  $cleared = false; $tablesTried = [];
  $candidates = ['meter_readings_cache','cached_readings','temp_cached_readings'];
  foreach ($candidates as $t) {
    try {
      $exists = $conn_rbe->prepare("SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = ?");
      $exists->execute([$t]);
      if ($exists->fetchColumn()) {
        $conn_rbe->exec("TRUNCATE TABLE `$t`");
        $cleared = true; $tablesTried[] = $t;
      }
    } catch (Throwable $e) { $tablesTried[] = $t; }
  }
  return ['status'=>'ok','cleared'=>$cleared,'tables'=>$tablesTried];
}

function getCronLogs(): array {
  global $conn_rbe;
  try {
    $has = $conn_rbe->query("SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name='cron_logs'")->fetchColumn();
    if ($has) {
      $stmt = $conn_rbe->query("SELECT DATE_FORMAT(run_at,'%H:%i') AS time, job, status FROM cron_logs ORDER BY run_at DESC LIMIT 10");
      return ['status'=>'ok','logs'=>$stmt->fetchAll(PDO::FETCH_ASSOC) ?: []];
    }
  } catch (Throwable $e) {}
  // Fallback: derive simple logs from latest readings
  $logs = [];
  try { $t = $conn_rbe->query("SELECT DATE_FORMAT(MAX(reading_timestamp),'%H:%i') FROM smart_meter_readings")->fetchColumn(); if ($t) $logs[] = ['time'=>$t,'job'=>'iammeter_sync','status'=>'ok']; } catch (Throwable $e) {}
  try { $t = $conn_rbe->query("SELECT DATE_FORMAT(MAX(COALESCE(created_at,fetched_at)),'%H:%i') FROM flecto_meter_readings")->fetchColumn(); if ($t) $logs[] = ['time'=>$t,'job'=>'flecto_sync','status'=>'ok']; } catch (Throwable $e) {}
  try { $t = $conn_rbe->query("SELECT DATE_FORMAT(MAX(reading_datetime),'%H:%i') FROM water_meter_readings")->fetchColumn(); if ($t) $logs[] = ['time'=>$t,'job'=>'water_sync','status'=>'ok']; } catch (Throwable $e) {}
  return ['status'=>'ok','logs'=>$logs];
}

function getTokens(): array {
  global $conn_rbe;
  try {
    $has = $conn_rbe->query("SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name='bearer_tokens'")->fetchColumn();
    if ($has) {
      $stmt = $conn_rbe->query("SELECT system, token, expires_at AS expires, created_at FROM bearer_tokens ORDER BY created_at DESC");
      return ['tokens'=>$stmt->fetchAll(PDO::FETCH_ASSOC) ?: []];
    }
  } catch (Throwable $e) {}
  return ['tokens'=>[]];
}

function exportData(): void {
  $type = $_GET['type'] ?? 'alerts';
  $rows = [];
  if ($type === 'alerts') { $rows = getAlerts(); }
  elseif ($type === 'activity') { $rows = getActivity(); }
  elseif ($type === 'finance') { $rows = getFinanceExportRows(); }

  header('Content-Type: text/csv');
  header('Content-Disposition: attachment; filename="export_'.$type.'_'.date('Ymd').'.csv"');
  $out = fopen('php://output','w');
  if (!empty($rows)) {
    fputcsv($out, array_keys($rows[0]));
    foreach ($rows as $r) fputcsv($out, $r);
  }
  fclose($out);
  exit;
}

function getFinanceExportRows(): array {
  $fin = getFinanceSummary();
  $rows = [];
  foreach ($fin['top_landlords'] as $tl) {
    $rows[] = [
      'landlord_id' => $tl['landlord_id'] ?? '',
      'landlord_name' => $tl['landlord_name'] ?? '',
      'total' => $tl['total'] ?? 0,
      'deposits_total' => $fin['deposits_total'] ?? 0,
      'payouts_total' => $fin['payouts_total'] ?? 0,
      'net' => $fin['net'] ?? 0,
    ];
  }
  if (!$rows) {
    $rows[] = [ 'landlord_id'=>'', 'landlord_name'=>'', 'total'=>0, 'deposits_total'=>$fin['deposits_total']??0, 'payouts_total'=>$fin['payouts_total']??0, 'net'=>$fin['net']??0 ];
  }
  return $rows;
}

function getLandlordDetails(int $id): array {
  global $conn_rbe;
  if ($id <= 0) return ['error'=>'invalid id'];
  $data = ['id'=>$id, 'landlord'=>'', 'meters'=>[], 'wallet'=>0.0, 'transactions'=>[]];
  try { $data['landlord'] = (string)$conn_rbe->query("SELECT fullname FROM rbe_users WHERE id=".(int)$id)->fetchColumn(); } catch (Throwable $e) {}
  try {
    $stmt = $conn_rbe->query("SELECT source, COUNT(*) AS count FROM vw_meters_combined WHERE landlord_id=".(int)$id." GROUP BY source");
    $data['meters'] = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
  } catch (Throwable $e) {}
  try {
    $data['wallet'] = (float)$conn_rbe->query("SELECT ROUND(COALESCE(SUM(amount),0),2) FROM wallet_transactions WHERE landlord_id=".(int)$id)->fetchColumn();
  } catch (Throwable $e) {
    try { $data['wallet'] = (float)$conn_rbe->query("SELECT ROUND(COALESCE(SUM(amount),0),2) FROM landlord_wallet_payouts WHERE landlord_id=".(int)$id)->fetchColumn(); } catch (Throwable $e2) {}
  }
  try {
    $has = $conn_rbe->query("SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name='wallet_transactions'")->fetchColumn();
    if ($has) {
      $stmt = $conn_rbe->query("SELECT DATE_FORMAT(created_at,'%Y-%m-%d %H:%i') AS time, description, ROUND(amount,2) AS amount FROM wallet_transactions WHERE landlord_id=".(int)$id." ORDER BY created_at DESC LIMIT 10");
      $data['transactions'] = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    } else {
      $stmt = $conn_rbe->prepare("SELECT DATE_FORMAT(created_at,'%Y-%m-%d %H:%i') AS time, 'Payout' AS description, ROUND(amount,2) AS amount FROM landlord_wallet_payouts WHERE landlord_id=? ORDER BY created_at DESC LIMIT 10");
      $stmt->execute([$id]);
      $data['transactions'] = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }
  } catch (Throwable $e) {}
  return $data;
}
function getMetersList(array $filter = []): array {
    global $conn_rbe;

    $params = [];
    $clauses = [];

    // Filter: Landlord
    if (!empty($filter['landlord_id'])) {
        $clauses[] = "landlord_id = ?";
        $params[] = (int)$filter['landlord_id'];
    }

    // Filter: Source
    if (!empty($filter['source']) && $filter['source'] !== 'ALL') {
        $clauses[] = "source = ?";
        $params[] = $filter['source'];
    }

    // Search filter
    if (!empty($filter['search'])) {
        $search = "%" . $filter['search'] . "%";
        $clauses[] = "(serial_number LIKE ?
                    OR landlord_name LIKE ?
                    OR tenant_name LIKE ?
                    OR property_number LIKE ?)";
        $params = array_merge($params, [$search, $search, $search, $search]);
    }

    $where = $clauses ? "WHERE " . implode(" AND ", $clauses) : "";

// Filter by STATUS (GREEN/YELLOW/RED)
if (!empty($filter['status']) && $filter['status'] !== 'ALL') {
    if ($filter['status'] === 'GREEN') {
        $clauses[] = "last_synced_at >= (NOW() - INTERVAL 2 HOUR)";
    }
    if ($filter['status'] === 'YELLOW') {
        $clauses[] = "last_synced_at < (NOW() - INTERVAL 2 HOUR)
                      AND last_synced_at >= (NOW() - INTERVAL 24 HOUR)";
    }
    if ($filter['status'] === 'RED') {
        $clauses[] = "last_synced_at < (NOW() - INTERVAL 24 HOUR)";
    }
}

    // Pagination
    $limit = isset($filter['limit']) ? max(1, min(500, (int)$filter['limit'])) : 10;
    $page  = isset($filter['page'])  ? max(1, (int)$filter['page']) : 1;
    $offset = ($page - 1) * $limit;

    // COUNT total
    $stmt = $conn_rbe->prepare("SELECT COUNT(*) FROM vw_meters_combined $where");
    $stmt->execute($params);
    $total = (int)$stmt->fetchColumn();

    // MAIN query
    $sql = "
        SELECT 
            source,
            meter_id,
            serial_number,
            meter_type,
            property_id,
            property_number,
            address,
            landlord_id,
            landlord_name,
            tenant_id,
            tenant_name,
            agent_id,
            last_synced_at,
            is_active,
            created_at
        FROM vw_meters_combined
        $where
        ORDER BY last_synced_at DESC, created_at DESC
        LIMIT $limit OFFSET $offset
    ";

    $stmt = $conn_rbe->prepare($sql);
    $stmt->execute($params);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];

    return [
        "total" => $total,
        "page"  => $page,
        "limit" => $limit,
        "rows"  => $rows
    ];
}

// If called as endpoint, route by action
if (php_sapi_name() !== 'cli') {
  $action = $_GET['action'] ?? $_POST['action'] ?? null;
  if ($action) {
    try {
      switch ($action) {
        case 'ping':
          json_response(['ok' => true, 'time' => time()]);
          break;
        case 'stats':
          json_response(getDashboardStats());
          break;
        case 'health':
          json_response(getHealthStatus());
          break;
        case 'insights':
          json_response(getInsights());
          break;
        case 'chart_energy_water':
          json_response(getChartEnergyWater());
          break;
        case 'chart_wallet':
          json_response(getChartWallet());
          break;
        case 'finance':
          json_response(getFinanceSummary());
          break;
        case 'alerts':
          json_response(getAlerts());
          break;
        case 'activity':
          json_response(getActivity());
          break;
        case 'manual_sync':
          json_response(triggerManualSync());
          break;
        case 'clear_cache':
          json_response(clearCache());
          break;
        case 'get_cron_logs':
          json_response(getCronLogs());
          break;
        case 'get_tokens':
          json_response(getTokens());
          break;
        case 'export_data':
          exportData();
          break;
        case 'landlord_details':
          $id = (int)($_GET['id'] ?? 0);
          json_response(getLandlordDetails($id));
          break;
        case 'meters':
    $filter = [];

    if (isset($_GET['landlord_id'])) $filter['landlord_id'] = (int)$_GET['landlord_id'];
    if (isset($_GET['source']))      $filter['source']      = $_GET['source'];
    if (isset($_GET['search']))      $filter['search']      = trim($_GET['search']);
    if (isset($_GET['limit']))       $filter['limit']       = (int)$_GET['limit'];
    if (isset($_GET['page']))        $filter['page']        = (int)$_GET['page'];

    json_response(getMetersList($filter));
    break;

        case 'toggle_wallet':
          $tenant_id = (int)($_POST['tenant_id'] ?? 0);
          $enabled   = (int)($_POST['enabled'] ?? 0);
          if ($tenant_id <= 0) json_response(['error' => 'Invalid tenant_id'], 400);
          json_response(toggleWallet($tenant_id, $enabled));
          break;
        case 'landlords':
          json_response(['rows' => getLandlords()]);
          break;
        case 'compare_bulk':
          $bulk_id = (int)($_GET['bulk_id'] ?? 0);
          if ($bulk_id <= 0) json_response(['error' => 'Invalid bulk_id'], 400);
          json_response(compareBulkMeters($bulk_id));
          break;
        default:
          json_response(['error' => 'Unknown action'], 400);
      }
    } catch (Throwable $e) {
      json_response(['error' => $e->getMessage()], 500);
    }
  }
  ob_end_flush();
}


?>
