<?php
require_once 'db_connect.php';

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

$tenant_id = isset($_GET['tenant_id']) ? (int)$_GET['tenant_id'] : null;
$start_date = $_GET['start_date'] ?? date('Y-m-01');
$end_date   = $_GET['end_date']   ?? date('Y-m-d');

if (!$tenant_id) die('Invalid tenant.');

$stmt = $conn_rbe->prepare("SELECT fullname FROM rbe_users WHERE id = ?");
$stmt->execute([$tenant_id]);
$tenant_name = $stmt->fetchColumn() ?: 'Unknown Tenant';

$stmt = $conn_rbe->prepare("SELECT landlord_id FROM tenant_landlord_map WHERE tenant_id = ? LIMIT 1");
$stmt->execute([$tenant_id]);
$landlord_id = $stmt->fetchColumn();
if (!$landlord_id) die('No landlord mapped for tenant.');

function calculate_flecto_total_kwh($row) {
    return floatval($row['reg_a'] ?? 0) + floatval($row['reg_e'] ?? 0) + floatval($row['reg_f'] ?? 0) + floatval($row['reg_h'] ?? 0) + floatval($row['reg_l'] ?? 0);
}
function calculateTieredCost(float $amount, array $tiers): float {
    if ($amount <= 0) return 0.0;
    $total = 0.0;
    foreach ($tiers as $tier) {
        $from = isset($tier['tier_from']) ? (float)$tier['tier_from'] : 0.0;
        $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;  // [from, to)
        if ($billable > 0) $total += $billable * $rate;
    }
    return round($total, 2);
}

$reports   = [];
$total_due = 0.0;

// Default property (fallback)
$stmt = $conn_rbe->prepare("
    SELECT p.property_number
    FROM properties p
    JOIN tenant_landlord_map tlm ON tlm.landlord_id = p.landlord_id
    WHERE tlm.tenant_id = ?
    ORDER BY p.id ASC LIMIT 1
");
$stmt->execute([$tenant_id]);
$pname = $stmt->fetchColumn() ?: 'Unknown';

// Tiers (landlord scope; tenant overrides would be sorted first if present)
$stmt = $conn_rbe->prepare("
    SELECT * FROM billing_tiers
    WHERE landlord_id = ?
    ORDER BY tenant_id DESC, type, tier_from ASC
");
$stmt->execute([$landlord_id]);
$tiers_by_type = [];
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $t) {
    $type = strtolower($t['type']);
    $tiers_by_type[$type][] = $t;
}

/* ============== IAMMETER ============== */
$stmt = $conn_rbe->prepare("SELECT id, serial, property_id FROM smart_meters WHERE tenant_id = ?");
$stmt->execute([$tenant_id]);
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $meter) {
    $mid = (int)$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_ts = $timestamps[0];
        $last_ts  = 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_ts, $last_ts]);
        $rows = $stmt3->fetchAll(PDO::FETCH_ASSOC);
        $sums = [$first_ts => 0.0, $last_ts => 0.0];
        foreach ($rows as $r) { $sums[$r['reading_timestamp']] += (float)$r['energy_total']; }

        // If stored in Wh, change /1 to /1000
        $kwh  = max(0, ($sums[$last_ts] - $sums[$first_ts]) / 1);
        $cost = calculateTieredCost($kwh, $tiers_by_type['electricity'] ?? []);

        $stmt_prop = $conn_rbe->prepare("SELECT property_number FROM properties WHERE id = ?");
        $stmt_prop->execute([(int)$meter['property_id']]);
        $meter_property = $stmt_prop->fetchColumn() ?: $pname;

        $reports[] = ['type'=>'Electricity (IAMMETER)','property'=>$meter_property,'meter'=>$meter['serial'],'usage'=>round($kwh,3),'unit'=>'kWh','cost'=>round($cost,2)];
        $total_due += $cost;
    }
}

/* ============== FLECTO ============== */
$stmt = $conn_rbe->prepare("SELECT serial_number, property_id FROM flecto_meters WHERE tenant_id = ?");
$stmt->execute([$tenant_id]);
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $meter) {
    $fid = $meter['serial_number'];
    $stmt2 = $conn_rbe->prepare("
        SELECT * FROM flecto_meter_readings
        WHERE serial = ? AND created_at BETWEEN ? AND ?
        ORDER BY created_at ASC
    ");
    $stmt2->execute([$fid, "$start_date 00:00:00", "$end_date 23:59:59"]);
    $rows = $stmt2->fetchAll(PDO::FETCH_ASSOC);

    if (count($rows) >= 2) {
        $first = $rows[0]; $last = end($rows);
        $kwh  = max(0, calculate_flecto_total_kwh($last) - calculate_flecto_total_kwh($first));
        $cost = calculateTieredCost($kwh, $tiers_by_type['electricity'] ?? []);

        $stmt_prop = $conn_rbe->prepare("SELECT property_number FROM properties WHERE id = ?");
        $stmt_prop->execute([(int)$meter['property_id']]);
        $meter_property = $stmt_prop->fetchColumn() ?: $pname;

        $reports[] = ['type'=>'Electricity (FLECTO)','property'=>$meter_property,'meter'=>$fid,'usage'=>round($kwh,3),'unit'=>'kWh','cost'=>round($cost,2)];
        $total_due += $cost;
    }
}

/* ============== WATER ============== */
$stmt = $conn_rbe->prepare("SELECT serial_number, property_id FROM water_meters WHERE tenant_id = ?");
$stmt->execute([$tenant_id]);
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $meter) {
    $serial = $meter['serial_number'];
    $stmt2 = $conn_rbe->prepare("
        SELECT MIN(volume_liters), MAX(volume_liters)
        FROM water_meter_readings
        WHERE serial_number = ? AND reading_datetime BETWEEN ? AND ?
    ");
    $stmt2->execute([$serial, "$start_date 00:00:00", "$end_date 23:59:59"]);
    [$minL, $maxL] = $stmt2->fetch(PDO::FETCH_NUM);
    if ($minL !== null && $maxL !== null) {
        $kl   = max(0, $maxL - $minL) / 1000.0;
        $cost = calculateTieredCost($kl, $tiers_by_type['water'] ?? []);

        $stmt_prop = $conn_rbe->prepare("SELECT property_number FROM properties WHERE id = ?");
        $stmt_prop->execute([(int)$meter['property_id']]);
        $meter_property = $stmt_prop->fetchColumn() ?: $pname;

        $reports[] = ['type'=>'Water','property'=>$meter_property,'meter'=>$serial,'usage'=>round($kl,2),'unit'=>'kl','cost'=>round($cost,2)];
        $total_due += $cost;
    }
}

/* ============== EXTRA FEES (SAFE MERGE: tenant overrides global ONLY WHEN NOT EMPTY) ============== */

/* 1. GLOBAL FEES */
$stmt = $conn_rbe->prepare("
    SELECT *
    FROM billing_settings
    WHERE landlord_id = ?
      AND (tenant_id IS NULL OR tenant_id = 0)
    ORDER BY id DESC LIMIT 1
");
$stmt->execute([$landlord_id]);
$global_fees = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];

/* 2. TENANT OVERRIDES */
$stmt = $conn_rbe->prepare("
    SELECT *
    FROM billing_settings
    WHERE landlord_id = ?
      AND tenant_id = ?
    ORDER BY id DESC LIMIT 1
");
$stmt->execute([$landlord_id, $tenant_id]);
$tenant_fees = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];

/* 3. Remove empty values so tenant does NOT override with blank */
foreach ($tenant_fees as $k => $v) {
    if ($v === '' || $v === null) {
        unset($tenant_fees[$k]);
    }
}

/* 4. Final merged fee array */
$fees_final = array_merge($global_fees, $tenant_fees);

/* 5. Fee mapping */
$fee_map = [
    'sewage_fee'         => 'Sewage Fee',
    'refuse_removal_fee' => 'Refuse Removal Fee',
    'rates_and_taxes'    => 'Rates & Taxes',
    'other_fee_name'     => 'Other Fee 1',
    'other_fee_name_2'   => 'Other Fee 2',
    'other_fee_name_3'   => 'Other Fee 3',
    'other_fee_name_4'   => 'Other Fee 4',
    'other_fee_name_5'   => 'Other Fee 5',
];

/* 6. Render fees */
foreach ($fee_map as $col => $label) {

    // Handle paired OTHER fees
    if (str_starts_with($col, 'other_fee_name')) {

        // Determine correct suffix
        if ($col === 'other_fee_name') {
            $suffix = '';
        } else {
            $suffix = '_' . str_replace('other_fee_name_', '', $col);
        }

        $name = trim($fees_final[$col] ?? '');
        $amt  = floatval($fees_final["other_fee{$suffix}"] ?? 0);

        if ($amt > 0) {
            $reports[] = [
                'type'     => ($name ?: $label),
                'property' => '—',
                'meter'    => '-',
                'usage'    => '-',
                'unit'     => '-',
                'cost'     => round($amt, 2),
            ];
            $total_due += $amt;
        }

        continue;
    }

    // Normal fixed fees
    $amt = floatval($fees_final[$col] ?? 0);

    if ($amt > 0) {
        $reports[] = [
            'type'     => $label,
            'property' => '—',
            'meter'    => '-',
            'usage'    => '-',
            'unit'     => '-',
            'cost'     => round($amt, 2),
        ];
        $total_due += $amt;
    }
}

?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<title>RBE Utility Report</title>
<style>
  :root {
    --page-w: 210mm;
    --page-h: 297mm;
    --pad: 10mm;         /* inner padding on print */
  }

  body {
    font-family: Arial, sans-serif;
    margin: 0;
    background: #f9f9f9;
    color: #051a25;
  }

  .invoice-wrapper {
    max-width: 800px;
    margin: 30px auto;
    background: #fff;
    padding: 30px;
    border-radius: 10px;
    box-shadow: 0 0 15px rgba(0,0,0,0.1);
  }

  .invoice-header {
    display: flex;
    justify-content: space-between;
    align-items: center;
    border-bottom: 3px solid #7ed957;
    padding-bottom: 16px;
    margin-bottom: 16px;
  }

  .logo { max-height: 80px; }

  .invoice-summary {
    display: flex;
    justify-content: space-between;
    gap: 16px;
    background: #051a25;
    color: #fff;
    padding: 16px;
    border-radius: 8px;
    margin-bottom: 16px;
  }
  .invoice-summary h2, .invoice-summary h3 { margin: 0 0 8px 0; color: #7ed957; }

  .invoice-table { width: 100%; border-collapse: collapse; margin-bottom: 12px; }
  .invoice-table thead { background: #7ed957; color: #051a25; }
  .invoice-table th, .invoice-table td { padding: 10px 12px; text-align: left; border-bottom: 1px solid #ddd; }
  .invoice-table tfoot { font-weight: bold; background: #051a25; color: #7ed957; }
  .total-label, .total-amount { text-align: right; }

  .invoice-footer { text-align: center; font-size: 0.9em; color: #777; border-top: 1px solid #eee; padding-top: 10px; }

  /* ===== PRINT (A4, one page, no browser margins) ===== */
  @media print {
    @page { size: A4 portrait; margin: 0; }   /* browser margins = 0 */

    html, body { width: var(--page-w); height: var(--page-h); background: #fff; }
    body { margin: 0 !important; }

    /* Remove UI-only bits */
    .no-print { display: none !important; }

    /* Fit everything neatly on one page */
    .invoice-wrapper {
      width: var(--page-w);
      min-height: var(--page-h);
      margin: 0 !important;
      padding: var(--pad);
      box-shadow: none !important;
      border-radius: 0 !important;

      /* Compact mode: shrink slightly for safety; tweak if needed */
      font-size: 11px;
      line-height: 1.2;
    }

    .invoice-header { padding-bottom: 8px; margin-bottom: 8px; border-bottom-width: 2px; }
    .logo { max-height: 48px; }

    .invoice-summary { padding: 10px; gap: 10px; }
    .invoice-summary h2, .invoice-summary h3 { margin-bottom: 6px; }

    .invoice-table th, .invoice-table td { padding: 6px 8px; }
    .invoice-table th { font-size: 12px; }
    .invoice-table td { font-size: 11px; }

    /* Avoid unintended page breaks; keep whole doc together */
    .invoice-wrapper,
    .invoice-summary,
    .invoice-table,
    .invoice-table thead,
    .invoice-table tbody,
    .invoice-table tfoot,
    .invoice-table tr,
    .invoice-table td,
    .invoice-table th {
      page-break-inside: avoid !important;
    }

    /* Ensure brand colors print nicely */
    * { -webkit-print-color-adjust: exact; print-color-adjust: exact; }

    /* Last-resort scaling if content still tries to spill:
       If you have LOTS of rows, lower zoom to 0.85/0.80. */
    .invoice-wrapper { zoom: 0.90; }
  }

  /* Fallback for browsers that ignore zoom in @media print (e.g., some Firefox versions) */
  @supports not (zoom: 1) {
    @media print {
      .invoice-wrapper {
        transform: scale(0.90);
        transform-origin: top left;
        width: calc(var(--page-w) / 0.90);
        height: calc(var(--page-h) / 0.90);
        overflow: hidden;
      }
    }
  }
</style>
</head>
<body>

<div class="invoice-wrapper">

  <header class="invoice-header">
    <img src="https://rbelectrical.enterprises/wp-content/uploads/2024/07/RBE-Electrical-Logo-clear2.png" alt="RBE Logo" class="logo">
  </header>

  <section class="invoice-summary">
    <div>
      <h2>Utility Report</h2>
      <p><strong>Tenant:</strong> <?= htmlspecialchars($tenant_name) ?></p>
      <p><strong>Period:</strong> <?= htmlspecialchars($start_date) ?> to <?= htmlspecialchars($end_date) ?></p>
    </div>
    <div>
      <h3>Total Due</h3>
      <p style="font-size: 1.4em; color: #7ed957; font-weight: bold;">R <?= number_format($total_due, 2) ?></p>
    </div>
  </section>

  <table class="invoice-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><?= is_numeric($r['usage']) ? htmlspecialchars($r['usage']) : '-' ?></td>
        <td><?= htmlspecialchars($r['unit']) ?></td>
        <td><?= number_format($r['cost'], 2) ?></td>
      </tr>
      <?php endforeach; ?>
    </tbody>
    <tfoot>
      <tr>
        <td colspan="5" class="total-label">TOTAL DUE</td>
        <td class="total-amount">R <?= number_format($total_due, 2) ?></td>
      </tr>
    </tfoot>
  </table>

  <footer class="invoice-footer">
    <p>Thank you for your business!</p>
  </footer>

  <div class="no-print" style="text-align:center; margin-top: 16px;">
    <button onclick="window.print()">🖨️ Print Invoice</button>
    <a href="reports.php?landlord_id=<?= urlencode((string)$landlord_id) ?>" style="margin-left:16px;">⬅️ Back to Reports</a>
  </div>

</div>

</body>
</html>
