<?php
// ============================================================
// admin-reports.php — RBE Utilities Admin Utility Reports (v2.0)
// ------------------------------------------------------------
// - Combined IAMMETER, FLECTO, WATER analysis
// - Tiered billing integration
// - CSV export
// - Landlord/property filters
// - Unified Admin v2.0 UI
// ============================================================

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

session_start();
require_once __DIR__ . '/core/db_connect.php';
include 'admin-nav.php';

// ------------------------------------------------------------
// 🛡️ ACCESS CONTROL
// ------------------------------------------------------------
if (!isset($_SESSION['user_id'])) {
  header("Location: login.php");
  exit();
}
$user_id = (int)$_SESSION['user_id'];
$stmt = $conn_rbe->prepare("SELECT role FROM rbe_roles WHERE user_id=? LIMIT 1");
$stmt->execute([$user_id]);
if ($stmt->fetchColumn() !== 'admin') exit("❌ Unauthorized access.");

// ------------------------------------------------------------
// ⚙️ HELPERS
// ------------------------------------------------------------
function calculateTieredCost($amount, $tiers) {
  $cost = 0; $remaining = $amount;
  foreach ($tiers as $tier) {
    $from = (float)$tier['tier_from'];
    $to   = ($tier['tier_to'] !== null) ? (float)$tier['tier_to'] : INF;
    $rate = (float)$tier['rate'];
    if ($remaining <= 0) break;
    $block_volume = max(0, min($remaining, $to - $from + 1));
    $cost += $block_volume * $rate;
    $remaining -= $block_volume;
  }
  return round($cost, 2);
}

// ------------------------------------------------------------
// 🔍 FILTERS
// ------------------------------------------------------------
$landlord_id = $_GET['landlord_id'] ?? null;
$selected_property_id = $_GET['property_id'] ?? null;
$start_date = $_GET['start_date'] ?? date('Y-m-d', strtotime('-7 days'));
$end_date   = $_GET['end_date'] ?? date('Y-m-d');

$landlords = $conn_rbe->query("
  SELECT u.id, u.fullname
  FROM rbe_users u
  JOIN rbe_roles r ON u.id=r.user_id
  WHERE r.role='landlord'
  ORDER BY u.fullname ASC
")->fetchAll(PDO::FETCH_KEY_PAIR);

$properties_by_landlord = [];
if ($landlord_id) {
  $stmt = $conn_rbe->prepare("SELECT id, property_number FROM properties WHERE landlord_id=? ORDER BY property_number ASC");
  $stmt->execute([$landlord_id]);
  $properties_by_landlord = $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// ------------------------------------------------------------
// 🧮 DATA BUILD
// ------------------------------------------------------------
$reports = [];
$total_water = 0;
$total_energy = 0;

if ($landlord_id) {
  // Tier structure
  $stmt = $conn_rbe->prepare("SELECT * FROM billing_tiers WHERE landlord_id=? ORDER BY type, tier_from ASC");
  $stmt->execute([$landlord_id]);
  $tiers_all = $stmt->fetchAll(PDO::FETCH_ASSOC);
  $tiers_by_type = [];
  foreach ($tiers_all as $t) $tiers_by_type[strtolower($t['type'])][] = $t;

  // Property filter
  if ($selected_property_id) {
    $filter_sql = "property_id = ?";
    $params = [$selected_property_id];
  } else {
    $ids = array_column($properties_by_landlord, 'id');
    if (empty($ids)) $ids = [0];
    $filter_sql = "property_id IN (" . implode(',', array_fill(0, count($ids), '?')) . ")";
    $params = $ids;
  }

  // IAMMETER meters
  $stmt = $conn_rbe->prepare("SELECT id, serial, property_id FROM smart_meters WHERE $filter_sql");
  $stmt->execute($params);
  $iammeters = $stmt->fetchAll(PDO::FETCH_ASSOC);

  // FLECTO
  $stmt = $conn_rbe->prepare("SELECT id, serial_number AS serial, property_id FROM flecto_meters WHERE $filter_sql");
  $stmt->execute($params);
  $flectos = $stmt->fetchAll(PDO::FETCH_ASSOC);

  // WATER
  $stmt = $conn_rbe->prepare("SELECT id, serial_number AS serial, property_id FROM water_meters WHERE $filter_sql");
  $stmt->execute($params);
  $waters = $stmt->fetchAll(PDO::FETCH_ASSOC);

  $all_meters = array_merge($iammeters, $flectos, $waters);

  foreach ($all_meters as $meter) {
    $pid = $meter['property_id'];
    $serial = $meter['serial'];
    $pname = $conn_rbe->prepare("SELECT property_number FROM properties WHERE id=? LIMIT 1");
    $pname->execute([$pid]);
    $property_name = $pname->fetchColumn() ?: 'Unassigned';

    // IAMMETER
    if (isset($meter['serial']) && $meter === $meter && in_array('serial', array_keys($meter))) {
      $type = 'IAMMETER';
    }

    // IAMMETER Reading Calc
    if (isset($meter['serial']) && isset($meter['id']) && in_array('serial', array_keys($meter)) && in_array('id', array_keys($meter))) {
      if (isset($meter['property_id']) && in_array($meter, $iammeters, true)) {
        $mid = $meter['id'];
        $stmt2 = $conn_rbe->prepare("
          SELECT reading_timestamp
          FROM smart_meter_readings
          WHERE meter_id=? AND reading_timestamp BETWEEN ? AND ?
          GROUP BY reading_timestamp HAVING COUNT(DISTINCT phase)=3
          ORDER BY reading_timestamp ASC
        ");
        $stmt2->execute([$mid, "$start_date 00:00:00", "$end_date 23:59:59"]);
        $timestamps = $stmt2->fetchAll(PDO::FETCH_COLUMN);

        if (count($timestamps) >= 2) {
          $first = $timestamps[0];
          $last  = end($timestamps);
          $stmt3 = $conn_rbe->prepare("
            SELECT reading_timestamp, energy_total FROM smart_meter_readings
            WHERE meter_id=? AND reading_timestamp IN (?, ?)
          ");
          $stmt3->execute([$mid, $first, $last]);
          $rows = $stmt3->fetchAll(PDO::FETCH_ASSOC);
          $sums = [$first => 0, $last => 0];
          foreach ($rows as $r) $sums[$r['reading_timestamp']] += (float)$r['energy_total'];

          $kwh = max(0, ($sums[$last] - $sums[$first]));
          $cost = calculateTieredCost($kwh, $tiers_by_type['electricity'] ?? []);
          $total_energy += $kwh;
          $reports[] = ['type'=>'Electricity (IAMMETER)', 'property'=>$property_name, 'meter'=>$serial, 'usage'=>round($kwh,3), 'unit'=>'kWh', 'cost'=>round($cost,2)];
        }
      }
    }

    // FLECTO Reading Calc
    if (in_array($meter, $flectos, true)) {
      $stmt = $conn_rbe->prepare("
        SELECT reg_a, reg_e, reg_f, reg_h, reg_l FROM flecto_meter_readings
        WHERE serial=? AND fetched_at BETWEEN ? AND ? ORDER BY fetched_at ASC LIMIT 1
      ");
      $stmt->execute([$serial, "$start_date 00:00:00", "$end_date 23:59:59"]);
      $start = $stmt->fetch(PDO::FETCH_ASSOC);

      $stmt = $conn_rbe->prepare("
        SELECT reg_a, reg_e, reg_f, reg_h, reg_l FROM flecto_meter_readings
        WHERE serial=? AND fetched_at BETWEEN ? AND ? ORDER BY fetched_at DESC LIMIT 1
      ");
      $stmt->execute([$serial, "$start_date 00:00:00", "$end_date 23:59:59"]);
      $end = $stmt->fetch(PDO::FETCH_ASSOC);

      if ($start && $end) {
        $sumStart = array_sum(array_map('floatval', $start));
        $sumEnd   = array_sum(array_map('floatval', $end));
        $kwh = max(0, $sumEnd - $sumStart);
        $cost = calculateTieredCost($kwh, $tiers_by_type['electricity'] ?? []);
        $total_energy += $kwh;
        $reports[] = ['type'=>'Electricity (FLECTO)', 'property'=>$property_name, 'meter'=>$serial, 'usage'=>round($kwh,3), 'unit'=>'kWh', 'cost'=>round($cost,2)];
      }
    }

    // WATER Reading Calc
    if (in_array($meter, $waters, true)) {
      $stmt = $conn_rbe->prepare("
        SELECT MIN(volume_liters), MAX(volume_liters)
        FROM water_meter_readings
        WHERE serial_number=? AND reading_datetime BETWEEN ? AND ?
      ");
      $stmt->execute([$serial, "$start_date 00:00:00", "$end_date 23:59:59"]);
      [$start, $end] = $stmt->fetch(PDO::FETCH_NUM);
      $kl = max(0, $end - $start) / 1000;
      $cost = calculateTieredCost($kl, $tiers_by_type['water'] ?? []);
      $total_water += $kl;
      $reports[] = ['type'=>'Water', 'property'=>$property_name, 'meter'=>$serial, 'usage'=>round($kl,2), 'unit'=>'kl', 'cost'=>round($cost,2)];
    }
  }
}

// ------------------------------------------------------------
// 📦 CSV EXPORT
// ------------------------------------------------------------
if (isset($_GET['export']) && $_GET['export']==='csv') {
  header('Content-Type: text/csv');
  header('Content-Disposition: attachment; filename="RBE_Admin_Report.csv"');
  $out=fopen('php://output','w');
  fputcsv($out,['Type','Property','Meter','Usage','Unit','Cost (R)']);
  foreach($reports as $r) fputcsv($out,[$r['type'],$r['property'],$r['meter'],$r['usage'],$r['unit'],$r['cost']]);
  fclose($out); exit;
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Admin Utility Reports — RBE Utilities</title>
<link rel="stylesheet" href="/assets/css/theme.css">
<link rel="stylesheet" href="modules/billing/billing.css">
<style>
  body { margin-left: 240px; }
  @media (max-width: 900px) { body { margin-left: 0; } }
  body.sidebar-collapsed { margin-left: 72px; }
  @media (max-width: 900px) { body.sidebar-collapsed { margin-left: 0; } }
</style>
<style>
.container { margin-left:100px; padding:5px; }
.filter-bar select, .filter-bar input { margin-right:10px; }
.summary { display:flex; gap:20px; margin:20px 0; flex-wrap:wrap; }
.summary .card { flex:1; text-align:center; }
.table-wrap { overflow-x:auto; }
</style>
</head>
<body>

<div class="container">
  <h1>📊 Admin Utility Reports</h1>

  <form method="get" class="filter-bar card">
    <label>Landlord:</label>
    <select name="landlord_id" onchange="this.form.submit()">
      <option value="">-- Select --</option>
      <?php foreach($landlords as $id=>$name): ?>
        <option value="<?= $id ?>" <?= ($id==$landlord_id)?'selected':'' ?>><?= htmlspecialchars($name) ?></option>
      <?php endforeach; ?>
    </select>

    <?php if ($landlord_id): ?>
      <label>Property:</label>
      <select name="property_id">
        <option value="">All Properties</option>
        <?php foreach($properties_by_landlord as $p): ?>
          <option value="<?= $p['id'] ?>" <?= ($p['id']==$selected_property_id)?'selected':'' ?>><?= htmlspecialchars($p['property_number']) ?></option>
        <?php endforeach; ?>
      </select>
    <?php endif; ?>

    <label>From:</label>
    <input type="date" name="start_date" value="<?= htmlspecialchars($start_date) ?>">
    <label>To:</label>
    <input type="date" name="end_date" value="<?= htmlspecialchars($end_date) ?>">

    <button type="submit">🔍 Filter</button>
  </form>

  <div class="summary">
    <div class="card">
      <h3>⚡ Total Electricity</h3>
      <div><?= round($total_energy, 2) ?> kWh</div>
    </div>
    <div class="card">
      <h3>💧 Total Water</h3>
      <div><?= round($total_water, 2) ?> kl</div>
    </div>
  </div>

  <?php if ($reports): ?>
    <div class="table-wrap card">
      <h2>📋 Detailed Breakdown</h2>
      <table>
        <thead>
          <tr>
            <th>Type</th>
            <th>Property</th>
            <th>Meter</th>
            <th>Usage</th>
            <th>Unit</th>
            <th>Cost (R)</th>
          </tr>
        </thead>
        <tbody>
          <?php foreach($reports as $r): ?>
            <tr>
              <td><?= htmlspecialchars($r['type']) ?></td>
              <td><?= htmlspecialchars($r['property']) ?></td>
              <td><?= htmlspecialchars($r['meter']) ?></td>
              <td><?= $r['usage'] ?></td>
              <td><?= $r['unit'] ?></td>
              <td><?= $r['cost'] ?></td>
            </tr>
          <?php endforeach; ?>
        </tbody>
      </table>
      <a href="?landlord_id=<?= urlencode($landlord_id) ?>&property_id=<?= urlencode($selected_property_id) ?>&start_date=<?= urlencode($start_date) ?>&end_date=<?= urlencode($end_date) ?>&export=csv" class="btn" style="margin-top:20px;">⬇️ Export CSV</a>
    </div>
  <?php else: ?>
    <div class="card" style="text-align:center;">No report data for selected range.</div>
  <?php endif; ?>
</div>

</body>
</html>
