<?php
// ============================================================
// modules/reports/reports.php — Unified Reporting Dashboard (MVP)
// - Role-aware (admin, landlord, tenant)
// - Electricity (IAMMETER + FLECTO) + Water
// - Tiered billing integration
// - CSV export and archive to report_archives
// ============================================================

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

require_once __DIR__ . '/../../core/db_connect.php';
require_once __DIR__ . '/../../core/auth.php';
require_once __DIR__ . '/../../core/functions.php';

require_login();

$role = current_user_role($conn_rbe);
$user_id = current_user_id();
$landlord_id = landlord_scope_id();

// Resolve landlord scope for tenant role
if ($role === 'tenant') {
  $st = $conn_rbe->prepare('SELECT landlord_id FROM tenant_landlord_map WHERE tenant_id=? LIMIT 1');
  $st->execute([$user_id]);
  $landlord_id = (int)($st->fetchColumn() ?: 0);
}

// Include appropriate sidebar navigation
if ($role === 'admin') {
  @include __DIR__ . '/../../admin-nav.php';
} else {
  @include __DIR__ . '/../../landlord-nav.php';
}

// Filters
$selected_property_id = isset($_GET['property_id']) && $_GET['property_id'] !== '' ? (int)$_GET['property_id'] : null;
$tenant_filter_id     = isset($_GET['tenant_id']) && $_GET['tenant_id'] !== '' ? (int)$_GET['tenant_id'] : null;
$start_date = $_GET['start_date'] ?? date('Y-m-d', strtotime('-7 days'));
$end_date   = $_GET['end_date'] ?? date('Y-m-d');

// Landlords list (admin only)
$landlords = [];
if ($role === 'admin') {
  $landlords = $conn_rbe->query("SELECT u.id, u.fullname FROM rbe_users u JOIN rbe_roles r ON r.user_id=u.id AND r.role='landlord' ORDER BY u.fullname")
                        ->fetchAll(PDO::FETCH_KEY_PAIR);
}

// Tenant list (landlord scope)
$tenants = [];
if ($landlord_id) {
  $st = $conn_rbe->prepare("SELECT u.id, u.fullname FROM rbe_users u JOIN tenant_landlord_map m ON m.tenant_id=u.id WHERE m.landlord_id=? ORDER BY u.fullname");
  $st->execute([$landlord_id]);
  $tenants = $st->fetchAll(PDO::FETCH_ASSOC);
}

// Property list (landlord scope)
$properties = [];
if ($landlord_id) {
  $st = $conn_rbe->prepare('SELECT id, property_number FROM properties WHERE landlord_id=? ORDER BY property_number');
  $st->execute([$landlord_id]);
  $properties = $st->fetchAll(PDO::FETCH_ASSOC);
}

// Build tier map
$tiers_by_type = [];
if ($landlord_id) {
  $st = $conn_rbe->prepare('SELECT * FROM billing_tiers WHERE landlord_id=? ORDER BY type, tier_from');
  $st->execute([$landlord_id]);
  foreach ($st->fetchAll(PDO::FETCH_ASSOC) as $t) {
    $tiers_by_type[strtolower($t['type'])][] = $t;
  }
}

// Fetch report data
$reports = [];
$total_energy = 0.0; $total_water = 0.0;
if ($landlord_id) {
  // Scope property ids
  if ($selected_property_id) {
    $filter_sql = 'property_id = ?';
    $params = [$selected_property_id];
  } else {
    $ids = array_column($properties, 'id');
    if (!$ids) { $ids = [0]; }
    $filter_sql = 'property_id IN (' . implode(',', array_fill(0, count($ids), '?')) . ')';
    $params = $ids;
  }

  // Tenant property filter (if tenant specified)
  if ($tenant_filter_id) {
    $st = $conn_rbe->prepare('SELECT property_id FROM tenant_landlord_map WHERE tenant_id=? AND landlord_id=?');
    $st->execute([$tenant_filter_id, $landlord_id]);
    $tpids = array_map('intval', $st->fetchAll(PDO::FETCH_COLUMN));
    if ($tpids) {
      $filter_sql .= ' AND property_id IN (' . implode(',', array_fill(0, count($tpids), '?')) . ')';
      $params = array_merge($params, $tpids);
    }
  }

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

  // FLECTO electricity
  $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 meters
  $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 = array_merge($iammeters, $flectos, $waters);

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

    // IAMMETER total kWh from three-phase grouped stamps
    if (in_array($m, $iammeters, true)) {
      $mid = (int)$m['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.0, $last => 0.0];
        foreach ($rows as $r) { $sums[$r['reading_timestamp']] += (float)$r['energy_total']; }
        $kwh = max(0.0, $sums[$last] - $sums[$first]);
        $cost = calculate_tiered_cost($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 kWh difference using selected registers
    if (in_array($m, $flectos, true)) {
      $stmtA = $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');
      $stmtA->execute([$serial, "$start_date 00:00:00", "$end_date 23:59:59"]);
      $start = $stmtA->fetch(PDO::FETCH_ASSOC);
      $stmtB = $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');
      $stmtB->execute([$serial, "$start_date 00:00:00", "$end_date 23:59:59"]);
      $endR = $stmtB->fetch(PDO::FETCH_ASSOC);
      if ($start && $endR) {
        $kwh = max(0.0, array_sum(array_map('floatval', $endR)) - array_sum(array_map('floatval', $start)));
        $cost = calculate_tiered_cost($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 kl difference
    if (in_array($m, $waters, true)) {
      $stmtW = $conn_rbe->prepare('SELECT MIN(volume_liters) AS vmin, MAX(volume_liters) AS vmax FROM water_meter_readings WHERE serial_number=? AND reading_datetime BETWEEN ? AND ?');
      $stmtW->execute([$serial, "$start_date 00:00:00", "$end_date 23:59:59"]);
      $row = $stmtW->fetch(PDO::FETCH_ASSOC);
      if ($row && $row['vmin'] !== null && $row['vmax'] !== null) {
        $kl = max(0.0, ((float)$row['vmax'] - (float)$row['vmin']) / 1000.0);
        $cost = calculate_tiered_cost($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 + archive
if (isset($_GET['export']) && $_GET['export'] === 'csv') {
  // Archive payload for 5 years retention (global_settings may override)
  try {
    $payload = json_encode([
      'filters' => [
        'landlord_id' => $landlord_id,
        'property_id' => $selected_property_id,
        'tenant_id'   => $tenant_filter_id,
        'start_date'  => $start_date,
        'end_date'    => $end_date,
      ],
      'data' => $reports,
    ], JSON_UNESCAPED_SLASHES);
    $ins = $conn_rbe->prepare('INSERT INTO report_archives (report_type, tenant_id, landlord_id, period_start, period_end, payload) VALUES (?,?,?,?,?,?)');
    $ins->execute(['UTILITIES_SUMMARY', null, $landlord_id ?: null, $start_date, $end_date, $payload]);
  } catch (Throwable $e) {
    // Non-fatal: continue with export
  }

  header('Content-Type: text/csv');
  header('Content-Disposition: attachment; filename="RBE_Utilities_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>Reports — RBE Utilities</title>
  <link rel="stylesheet" href="/assets/css/theme.css">
  <link rel="stylesheet" href="../billing/billing.css">
  <style>
    /* Offset for fixed sidebar */
    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: 10px; }
    .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>Unified Utility Reports</h1>

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

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

      <?php if ($role !== 'tenant'): ?>
        <label>Tenant:</label>
        <select name="tenant_id">
          <option value="">All</option>
          <?php foreach ($tenants as $t): ?>
            <option value="<?= (int)$t['id'] ?>" <?= ($tenant_filter_id==$t['id'])?'selected':'' ?>><?= htmlspecialchars($t['fullname']) ?></option>
          <?php endforeach; ?>
        </select>
      <?php endif; ?>
    <?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>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 class="btn" style="margin-top: 16px;"
         href="?<?= http_build_query(['landlord_id'=>$landlord_id,'property_id'=>$selected_property_id,'tenant_id'=>$tenant_filter_id,'start_date'=>$start_date,'end_date'=>$end_date,'export'=>'csv']) ?>">Export CSV</a>
    </div>
  <?php else: ?>
    <div class="card" style="text-align:center;">No data for selected filters.</div>
  <?php endif; ?>
</div>

</body>
</html>
