<?php
// tenant-dashboard.php
// Tenant dashboard with month toggles (this month-to-now / previous month)
// Landlord-scoped tier billing + boundary-aware reading diffs + fixed fees (incl. Other 1..5)
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

session_start();
date_default_timezone_set('Africa/Johannesburg');

require_once 'db_connect.php'; // provides $conn_rbe (PDO)

// ---------------------------
// Access Control
// ---------------------------
$role = null;
$tenant_id = null;

if (isset($_GET['tenant_id']) && isset($_SESSION['user_id'])) {
    $admin_id = (int)$_SESSION['user_id'];
    $stmt = $conn_rbe->prepare("SELECT role FROM rbe_roles WHERE user_id = ? LIMIT 1");
    $stmt->execute([$admin_id]);
    $role_val = $stmt->fetchColumn();
    if ($role_val === 'admin' || $role_val === 'landlord') {
        $role = $role_val;
        $tenant_id = (int)($_GET['tenant_id']);
    } else {
        exit('Access denied.');
    }
} elseif (isset($_SESSION['user_id'])) {
    $tenant_id = (int)$_SESSION['user_id'];
    $stmt = $conn_rbe->prepare("SELECT role FROM rbe_roles WHERE user_id = ? LIMIT 1");
    $stmt->execute([$tenant_id]);
    $role_val = $stmt->fetchColumn();
    if ($role_val !== 'tenant') {
        exit('Access denied.');
    }
    $role = 'tenant';
} else {
    header("Location: login.php");
    exit();
}

// ---------------------------
// Period selection
// ---------------------------
// ?period=this  (default) -> current month 00:00 to now
// ?period=prev           -> previous month 00:00 to previous month 23:59:59
$period = strtolower($_GET['period'] ?? 'this');
$tz = new DateTimeZone('Africa/Johannesburg');
$now = new DateTime('now', $tz);

if ($period === 'prev') {
    $start_dt = new DateTime('first day of last month 00:00:00', $tz);
    $end_dt   = new DateTime('last day of last month 23:59:59', $tz);
    $period_label = $start_dt->format('F Y') . ' (full month)';
    $days_divisor = (int)$start_dt->format('t'); // days in last month
} else {
    $start_dt = new DateTime('first day of this month 00:00:00', $tz);
    $end_dt   = clone $now; // to "right now"
    $period = 'this'; // normalize
    $period_label = 'This Month (to now)';
    $days_divisor = max(1, (int)$now->format('j')); // days passed this month
}

$RANGE_START = $start_dt->format('Y-m-d H:i:s');
$RANGE_END   = $end_dt->format('Y-m-d H:i:s');

// ---------------------------
// Helpers
// ---------------------------
function esc($v){ return htmlspecialchars((string)$v, ENT_QUOTES, 'UTF-8'); }

// ---------------------------
// Tenant & Landlord
// ---------------------------
$stmt = $conn_rbe->prepare("SELECT fullname, email, phone FROM rbe_users WHERE id = ?");
$stmt->execute([$tenant_id]);
$tenant = $stmt->fetch(PDO::FETCH_ASSOC) ?: ['fullname'=>'', 'email'=>'', 'phone'=>''];

$stmt = $conn_rbe->prepare("SELECT landlord_id FROM tenant_landlord_map WHERE tenant_id = ? LIMIT 1");
$stmt->execute([$tenant_id]);
$landlord_id = $stmt->fetchColumn();

$landlord_name = 'Unassigned';
if ($landlord_id) {
    $stmt = $conn_rbe->prepare("SELECT fullname FROM rbe_users WHERE id = ?");
    $stmt->execute([$landlord_id]);
    $landlord_name = $stmt->fetchColumn() ?: 'Unassigned';
}

$use_wallet = 0;
if ($landlord_id) {
    $stmt = $conn_rbe->prepare("
        SELECT use_wallet 
        FROM tenant_landlord_map 
        WHERE tenant_id = ? AND landlord_id = ?
        LIMIT 1
    ");
    $stmt->execute([$tenant_id, $landlord_id]);
    $use_wallet = (int)$stmt->fetchColumn();
}


// ---------------------------
// Billing Tiers (STRICTLY landlord-scoped)
// ---------------------------
$tiers_by_type = [];
if ($landlord_id) {
    $stmt = $conn_rbe->prepare("
        SELECT id, type, tier_from, tier_to, rate
        FROM billing_tiers
        WHERE landlord_id = ?
        ORDER BY type, tier_from ASC
    ");
    $stmt->execute([$landlord_id]);
    $tiers_all = $stmt->fetchAll(PDO::FETCH_ASSOC);
    foreach ($tiers_all as $t) {
        $key = strtolower(trim($t['type'] ?? ''));
        $tiers_by_type[$key][] = $t;
    }
}

/**
 * Continuous tier billing for quantities (kWh / kL), using landlord-defined thresholds.
 * tier_from inclusive; tier_to exclusive (NULL = open-ended).
 */
function calculateTieredCost(float $amount, array $tiers): float {
    if ($amount <= 0 || empty($tiers)) return 0.0;
    $total = 0.0;
    foreach ($tiers as $tier) {
        $from = isset($tier['tier_from']) ? (float)$tier['tier_from'] : 0.0;
        $to   = (isset($tier['tier_to']) && $tier['tier_to'] !== null) ? (float)$tier['tier_to'] : INF;
        $rate = (float)($tier['rate'] ?? 0.0);
        if ($to <= $from || $amount <= $from) continue;
        $billable = min($amount, $to) - $from;   // portion inside [from, to)
        if ($billable > 0) $total += $billable * $rate;
    }
    return round($total, 2);
}

// ---------------------------
// Meters for this tenant
// ---------------------------
$stmt = $conn_rbe->prepare("SELECT id, serial FROM smart_meters WHERE tenant_id = ?");
$stmt->execute([$tenant_id]);
$iammeters = $stmt->fetchAll(PDO::FETCH_ASSOC);

$stmt = $conn_rbe->prepare("SELECT serial_number FROM flecto_meters WHERE tenant_id = ?");
$stmt->execute([$tenant_id]);
$flectos = $stmt->fetchAll(PDO::FETCH_COLUMN);

$stmt = $conn_rbe->prepare("SELECT serial_number FROM water_meters WHERE tenant_id = ?");
$stmt->execute([$tenant_id]);
$waters = $stmt->fetchAll(PDO::FETCH_COLUMN);

// ---------------------------
// Reading helpers (boundary-aware)
// ---------------------------
// Toggle: true if IAMMETER energy_total is in Wh; false if already kWh.
$IAMMETER_STORED_IN_Wh = true;

/**
 * Get a 3-phase-complete timestamp around a pivot.
 * $dir: 'le' (<= pivot, nearest before), 'ge' (>= pivot, nearest after)
 */
function get_iammeter_complete_ts(PDO $conn, int $meter_id, string $pivot, string $dir): ?string {
    if ($dir === 'le') {
        $sql = "
          SELECT reading_timestamp
          FROM smart_meter_readings
          WHERE meter_id = ? AND reading_timestamp <= ?
          GROUP BY reading_timestamp
          HAVING COUNT(DISTINCT phase) = 3
          ORDER BY reading_timestamp DESC
          LIMIT 1
        ";
    } else {
        $sql = "
          SELECT reading_timestamp
          FROM smart_meter_readings
          WHERE meter_id = ? AND reading_timestamp >= ?
          GROUP BY reading_timestamp
          HAVING COUNT(DISTINCT phase) = 3
          ORDER BY reading_timestamp ASC
          LIMIT 1
        ";
    }
    $st = $conn->prepare($sql);
    $st->execute([$meter_id, $pivot]);
    $ts = $st->fetchColumn();
    return $ts ?: null;
}

/**
 * Sum energy_total across phases at a specific timestamp
 */
function get_iammeter_sum_at(PDO $conn, int $meter_id, string $ts): ?float {
    $st = $conn->prepare("
      SELECT SUM(energy_total) AS total_energy
      FROM smart_meter_readings
      WHERE meter_id = ? AND reading_timestamp = ?
    ");
    $st->execute([$meter_id, $ts]);
    $val = $st->fetchColumn();
    return ($val === false || $val === null) ? null : (float)$val;
}

/**
 * IAMMETER kWh used in [start..end], boundary-aware:
 * - start anchor: nearest complete reading <= start, else >= start
 * - end anchor:   nearest complete reading <= end,   else >= end
 */
function get_iammeter_energy_range(PDO $conn, int $meter_id, string $start, string $end, bool $wh_to_kwh = true): float {
    // Find anchors
    $start_ts = get_iammeter_complete_ts($conn, $meter_id, $start, 'le');
    if (!$start_ts) $start_ts = get_iammeter_complete_ts($conn, $meter_id, $start, 'ge');

    $end_ts = get_iammeter_complete_ts($conn, $meter_id, $end, 'le');
    if (!$end_ts) $end_ts = get_iammeter_complete_ts($conn, $meter_id, $end, 'ge');

    if (!$start_ts || !$end_ts) return 0.0;

    // Ensure chronological order
    if ($end_ts < $start_ts) return 0.0;

    $start_sum = get_iammeter_sum_at($conn, $meter_id, $start_ts);
    $end_sum   = get_iammeter_sum_at($conn, $meter_id, $end_ts);

    if ($start_sum === null || $end_sum === null) return 0.0;

    $diff = max($end_sum - $start_sum, 0.0);
    if ($wh_to_kwh) $diff /= 1000.0;

    return round($diff, 2);
}

/**
 * Generic boundary finders for monotonic registers (Flecto, Water):
 * - anchor_start: last reading <= start, else first reading >= start
 * - anchor_end:   last reading <= end,   else first reading >= end
 */
function get_scalar_at_anchor(PDO $conn, string $sql_le, string $sql_ge, array $params_le, array $params_ge): ?float {
    $st = $conn->prepare($sql_le);
    $st->execute($params_le);
    $val = $st->fetchColumn();
    if ($val !== false && $val !== null) return (float)$val;

    $st = $conn->prepare($sql_ge);
    $st->execute($params_ge);
    $val = $st->fetchColumn();
    if ($val !== false && $val !== null) return (float)$val;

    return null;
}

/**
 * Flecto kWh difference in [start..end], using (reg_a+reg_e+reg_f+reg_h+reg_l)
 */
function get_flecto_energy_range(PDO $conn, string $serial, string $start, string $end): float {
    $sum_expr = "(reg_a + reg_e + reg_f + reg_h + reg_l)";

    $sql_le = "SELECT $sum_expr AS total FROM flecto_meter_readings
               WHERE serial = ? AND created_at <= ?
               ORDER BY created_at DESC LIMIT 1";
    $sql_ge = "SELECT $sum_expr AS total FROM flecto_meter_readings
               WHERE serial = ? AND created_at >= ?
               ORDER BY created_at ASC LIMIT 1";

    $start_val = get_scalar_at_anchor($conn, $sql_le, $sql_ge, [$serial, $start], [$serial, $start]);
    $end_val   = get_scalar_at_anchor($conn, $sql_le, $sql_ge, [$serial, $end],   [$serial, $end]);

    if ($start_val === null || $end_val === null) return 0.0;
    return round(max($end_val - $start_val, 0.0), 2);
}

/**
 * Water kL difference in [start..end], using volume_liters
 * Adjust timestamp column name if yours differs from `created_at`.
 */
function get_water_usage_range(PDO $conn, string $serial, string $start, string $end): float {
    $sql_le = "SELECT volume_liters FROM water_meter_readings
               WHERE serial_number = ? AND created_at <= ?
               ORDER BY created_at DESC LIMIT 1";
    $sql_ge = "SELECT volume_liters FROM water_meter_readings
               WHERE serial_number = ? AND created_at >= ?
               ORDER BY created_at ASC LIMIT 1";

    $start_val = get_scalar_at_anchor($conn, $sql_le, $sql_ge, [$serial, $start], [$serial, $start]);
    $end_val   = get_scalar_at_anchor($conn, $sql_le, $sql_ge, [$serial, $end],   [$serial, $end]);

    if ($start_val === null || $end_val === null) return 0.0;

    $liters = max($end_val - $start_val, 0.0);
    return round($liters / 1000.0, 2); // liters â†’ kL
}

// ---------------------------
// Build per-meter usage & costs for selected window
// ---------------------------
$report_rows = [];
$total_estimate = 0.0;
$total_energy_sum = 0.0; // electricity only (for average daily)
$elec_tiers = $tiers_by_type['electricity'] ?? [];
$water_tiers = $tiers_by_type['water'] ?? [];

// IAMMETER
foreach ($iammeters as $m) {
    $usage = get_iammeter_energy_range($conn_rbe, (int)$m['id'], $RANGE_START, $RANGE_END, $IAMMETER_STORED_IN_Wh);
    $total_energy_sum += $usage;
    $cost = calculateTieredCost($usage, $elec_tiers);
    $total_estimate += $cost;

    $report_rows[] = [
        'type'  => 'Electricity (IAMMETER)',
        'meter' => $m['serial'],
        'usage' => $usage,
        'unit'  => 'kWh',
        'cost'  => $cost
    ];
}

// FLECTO
foreach ($flectos as $serial) {
    $usage = get_flecto_energy_range($conn_rbe, $serial, $RANGE_START, $RANGE_END);
    $total_energy_sum += $usage;
    $cost = calculateTieredCost($usage, $elec_tiers);
    $total_estimate += $cost;

    $report_rows[] = [
        'type'  => 'Electricity (FLECTO)',
        'meter' => $serial,
        'usage' => $usage,
        'unit'  => 'kWh',
        'cost'  => $cost
    ];
}

// WATER
foreach ($waters as $serial) {
    $usage = get_water_usage_range($conn_rbe, $serial, $RANGE_START, $RANGE_END);
    $cost = calculateTieredCost($usage, $water_tiers);
    $total_estimate += $cost;

    $report_rows[] = [
        'type'  => 'Water',
        'meter' => $serial,
        'usage' => $usage,
        'unit'  => 'kL',
        'cost'  => $cost
    ];
}

// ---------------------------
// Fixed fees for this tenant (sewage/refuse/rates + Other 1..5)
// Pull ONLY tenant-specific rows. (Global rows can be displayed separately if needed.)
// ---------------------------
$fixed_rows = [];
if ($landlord_id) {
    $stmt = $conn_rbe->prepare("
        SELECT *
        FROM billing_settings
        WHERE landlord_id = ? AND tenant_id = ?
        ORDER BY id ASC
    ");
    $stmt->execute([$landlord_id, $tenant_id]);
    $tenant_fee_rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    foreach ($tenant_fee_rows as $feeRow) {
        // Core fixed fees
        $map = [
            ['label' => 'Sewage Fee',          'name_col' => null,                'amt_col' => 'sewage_fee'],
            ['label' => 'Refuse Removal Fee',  'name_col' => null,                'amt_col' => 'refuse_removal_fee'],
            ['label' => 'Rates & Taxes',       'name_col' => null,                'amt_col' => 'rates_and_taxes'],
            // Other 1..5
            ['label' => null, 'name_col' => 'other_fee_name',   'amt_col' => 'other_fee'],
            ['label' => null, 'name_col' => 'other_fee_name_2', 'amt_col' => 'other_fee_2'],
            ['label' => null, 'name_col' => 'other_fee_name_3', 'amt_col' => 'other_fee_3'],
            ['label' => null, 'name_col' => 'other_fee_name_4', 'amt_col' => 'other_fee_4'],
            ['label' => null, 'name_col' => 'other_fee_name_5', 'amt_col' => 'other_fee_5'],
        ];

        foreach ($map as $def) {
            $amt = isset($feeRow[$def['amt_col']]) ? (float)$feeRow[$def['amt_col']] : 0.0;
            if ($amt <= 0) continue;

            if ($def['name_col']) {
                $nm = trim((string)($feeRow[$def['name_col']] ?? ''));
                $label = ($nm !== '') ? $nm : 'Other Fee';
            } else {
                $label = $def['label'];
            }

            // Add to table + total
            $fixed_rows[] = [
                'type'  => $label,
                'meter' => 'â€”',
                'usage' => 'â€”',
                'unit'  => 'â€”',
                'cost'  => round($amt, 2),
            ];
            $total_estimate += $amt;
        }
    }

    /* OPTIONAL: If you also want to *display* global fees without charging the tenant here:
    $stmt = $conn_rbe->prepare("
        SELECT *
        FROM billing_settings
        WHERE landlord_id = ? AND tenant_id IS NULL
        ORDER BY id ASC
    ");
    $stmt->execute([$landlord_id]);
    $global_fee_rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    // You could render these in a separate card marked "Global fees (landlord may distribute)".
    */
}

// Merge fixed rows into main report rows (after usage rows)
$report_rows = array_merge($report_rows, $fixed_rows);

// Derived insights
$average_daily_use = $days_divisor > 0 ? round($total_energy_sum / $days_divisor, 2) : 0.0;
$kva_month = 'â€”'; // wire up if/when you track it

// ---------------------------
// Render
// ---------------------------
?>
<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>Tenant Dashboard | RBE</title>
  <meta name="viewport" content="width=device-width, initial-scale=1" />
  <style>
    body { font-family: Arial, sans-serif; background: #051a25; color: #fff; margin: 0; padding: 20px; }
    h1, h2 { color: #7ed957; margin: 0 0 12px; }
    .card { background: #122c4c; padding: 20px; border-radius: 12px; margin-bottom: 20px; }
    table { width: 100%; border-collapse: collapse; background: #0b1f33; margin-top: 10px; border-radius: 12px; overflow: hidden; }
    th, td { border: 1px solid #7ed957; padding: 10px; text-align: center; }
    th { background: #1b3a4d; color: #7ed957; }
    tr:nth-child(even) { background: #102a42; }
    .btn { background: #7ed957; color: #000; padding: 10px 20px; border-radius: 8px; text-decoration: none; font-weight: bold; display: inline-block; margin: 0 10px 20px 0; }
    .btn:hover { background: #00cc77; }
    .row { display: flex; gap: 20px; flex-wrap: wrap; }
    .col { flex: 1 1 320px; }
    .muted { color: #a6c1d6; font-size: 14px; }
    .toggle { display:flex; gap:8px; margin: 8px 0 16px; flex-wrap: wrap; }
    .toggle a { background:#0b1f33; border:1px solid #7ed957; color:#7ed957; padding:8px 12px; border-radius:10px; text-decoration:none; }
    .toggle a.active { background:#7ed957; color:#000; }
    .pill { display:inline-block; padding:2px 8px; border-radius:999px; background:#0b1f33; border:1px solid #7ed957; color:#7ed957; font-size:12px; }
  </style>
</head>
<body>
<?php
$newAppUrl = RBE_V2_BASE_URL;
include __DIR__ . '/includes/new_app_banner.php';
?>

  <div style="display:flex; gap:12px; flex-wrap:wrap; align-items:center; margin-bottom:10px;">
    <?php if ($role !== 'tenant'): ?>
      <a class="btn" href="<?= $role === 'admin' ? 'admin-dashboard.php' : 'landlord-dashboard.php' ?>">Back to Dashboard</a>
    <?php endif; ?>
    <a class="btn" href="logout.php">Logout</a>
  </div>

  <h1>Tenant Dashboard</h1>
  <div class="toggle">
    <a href="?period=this<?= $role!=='tenant' ? '&tenant_id='.urlencode((string)$tenant_id) : '' ?>" class="<?= $period==='this' ? 'active' : '' ?>">This Month</a>
    <a href="?period=prev<?= $role!=='tenant' ? '&tenant_id='.urlencode((string)$tenant_id) : '' ?>" class="<?= $period==='prev' ? 'active' : '' ?>">Previous Month</a>
  </div>
  <p class="muted">
    Window: <span class="pill"><?= esc($RANGE_START) ?> â†’ <?= esc($RANGE_END) ?></span>
    â€¢ Period: <span class="pill"><?= esc($period_label) ?></span>
    â€¢ Billing tiers: landlord-specific
  </p>

  <div class="row">
    <div class="col">
      <div class="card">
        <h2>Your Profile</h2>
        <p><strong>Name:</strong> <?= esc($tenant['fullname']) ?></p>
        <p><strong>Email:</strong> <?= esc($tenant['email']) ?></p>
        <p><strong>Phone:</strong> <?= esc($tenant['phone']) ?></p>
        <p><strong>Landlord:</strong> <?= esc($landlord_name) ?></p>
      </div>
    </div>

    <div class="col">
      <div class="card">
        <h2>Usage Insights</h2>
        <p><strong>Average Daily Use:</strong> <?= number_format($average_daily_use, 2) ?> kWh/day</p>
        <p><strong>kVA for the Period:</strong> <?= esc($kva_month) ?></p>
      </div>
    </div>
  </div>
<?php if ($use_wallet): ?>
  <div class="card">
    <h2>Your Wallet</h2>
    <?php
      require_once __DIR__ . "/wallet/includes/wallet_db.php";
      $balance = getTenantBalance($tenant_id, $conn_rbe);
      $deducted_balance = $balance;

      // Only deduct if showing THIS month (to avoid confusion for previous months)
      if ($period === 'this') {
          $deducted_balance = $balance - $total_estimate;
      }
    ?>
    <p><strong>Current Balance:</strong> R <?= number_format($balance, 2) ?></p>
    <?php if ($period === 'this'): ?>
      <p><strong>Balance After Deduction:</strong> 
         R <?= number_format($deducted_balance, 2) ?>
         <?= $deducted_balance < 0 ? '<span style="color:#ff7777;">(âš ï¸ In Arrears)</span>' : '' ?>
      </p>
    <?php endif; ?>
    <a class="btn" href="wallet/tenant/deposit.php?tenant_id=<?= $tenant_id ?>">Top Up Wallet</a>
    <a class="btn" href="wallet/tenant/history.php?tenant_id=<?= $tenant_id ?>">View Transactions</a>
  </div>
<?php endif; ?>


  <div class="card">
    <h2><?= $period==='prev' ? 'Last Month' : 'This Month' ?> Usage &amp; Estimated Costs</h2>
    <table>
      <tr>
        <th>Type</th>
        <th>Meter</th>
        <th>Usage</th>
        <th>Unit</th>
        <th>Estimated Cost (R)</th>
      </tr>
      <?php foreach ($report_rows as $r): ?>
        <tr>
          <td><?= esc($r['type']) ?></td>
          <td><?= esc($r['meter']) ?></td>
          <td><?= is_numeric($r['usage']) ? number_format((float)$r['usage'], 2) : esc($r['usage']) ?></td>
          <td><?= esc($r['unit']) ?></td>
          <td><?= number_format((float)$r['cost'], 2) ?></td>
        </tr>
      <?php endforeach; ?>
      <tr style="font-weight:bold;">
        <td colspan="4" style="text-align:right;">Total Estimated Bill:</td>
        <td>R <?= number_format($total_estimate, 2) ?></td>
      </tr>
    </table>
  </div>

  <!-- OPTIONAL: Card to *display* global fees without adding them to the total
  <?php /*
  if (!empty($global_fee_rows)) {
  ?>
  <div class="card">
    <h2>Global Fees (for your information)</h2>
    <p class="muted">These fees are set at the landlord level and may be distributed separately.</p>
    <table>
      <tr><th>Label</th><th>Amount (R)</th></tr>
      <?php
        $gmap = ['sewage_fee'=>'Sewage Fee','refuse_removal_fee'=>'Refuse Removal Fee','rates_and_taxes'=>'Rates & Taxes'];
        foreach ($global_fee_rows as $g) {
          foreach ($gmap as $col=>$lbl) {
            $v = (float)($g[$col] ?? 0);
            if ($v > 0) echo "<tr><td>".esc($lbl)."</td><td>".number_format($v,2)."</td></tr>";
          }
          $others = [
            ['n'=>$g['other_fee_name'] ?? 'Other','v'=>$g['other_fee'] ?? 0],
            ['n'=>$g['other_fee_name_2'] ?? 'Other','v'=>$g['other_fee_2'] ?? 0],
            ['n'=>$g['other_fee_name_3'] ?? 'Other','v'=>$g['other_fee_3'] ?? 0],
            ['n'=>$g['other_fee_name_4'] ?? 'Other','v'=>$g['other_fee_4'] ?? 0],
            ['n'=>$g['other_fee_name_5'] ?? 'Other','v'=>$g['other_fee_5'] ?? 0],
          ];
          foreach ($others as $o) {
            $v = (float)$o['v'];
            if ($v > 0) echo "<tr><td>".esc($o['n'] ?: 'Other')."</td><td>".number_format($v,2)."</td></tr>";
          }
        }
      ?>
    </table>
  </div>
  <?php } */ ?>
  -->

</body>
</html>



