<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

session_start();
require_once 'db_connect.php';

if (!isset($_SESSION['user_id'])) {
    header("Location: login.php");
    exit;
}
function calculateTieredCost($amount, $tiers) {
    $cost = 0;
    $remaining = $amount;

    foreach ($tiers as $tier) {
        $from = floatval($tier['tier_from']);
        $to   = ($tier['tier_to'] !== null) ? floatval($tier['tier_to']) : INF;
        $rate = floatval($tier['rate']);

        if ($remaining <= 0) break;

        $block_volume = max(0, min($remaining, $to - $from + 1));  // Correct per-block volume
        $cost += $block_volume * $rate;
        $remaining -= $block_volume;
    }

    return round($cost, 2);
}


$user_id = $_SESSION['user_id'];
$stmt = $conn_rbe->prepare("SELECT role FROM rbe_roles WHERE user_id = ? LIMIT 1");
$stmt->execute([$user_id]);
$role = $stmt->fetchColumn();
if ($role !== 'admin') die("\u274c Unauthorized access.");

$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 = [];
$stmt = $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");
while ($row = $stmt->fetch()) {
    $landlords[$row['id']] = $row['fullname'];
}

$properties_by_landlord = [];
$property_ids = [];

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);
    foreach ($properties_by_landlord as $prop) $property_ids[] = $prop['id'];
}

$reports = [];
$total_water = 0;
$total_energy = 0;

if ($landlord_id && count($property_ids) > 0) {

    $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) {
    $key = strtolower($t['type']);   // normalize keys to lowercase
    $tiers_by_type[$key][] = $t;
}



    // Build property filter condition
    if ($selected_property_id) {
        $filter_sql = "property_id = ?";
        $filter_params = [$selected_property_id];
    } else {
        $placeholders = implode(',', array_fill(0, count($property_ids), '?'));
        $filter_sql = "property_id IN ($placeholders)";
        $filter_params = $property_ids;
    }

    // Fetch IAMMETER, FLECTO, WATER meters restricted by filter
    $stmt = $conn_rbe->prepare("SELECT id, serial, 'IAMMETER' AS type, property_id FROM smart_meters WHERE $filter_sql");
    $stmt->execute($filter_params);
    $iammeters = $stmt->fetchAll(PDO::FETCH_ASSOC);

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

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

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

    foreach ($all_meters as $meter) {
        $pid = $meter['property_id'];
        $meter_id = $meter['id'];
        $serial = $meter['serial'];
        $type = $meter['type'];

        $stmt = $conn_rbe->prepare("SELECT property_number FROM properties WHERE id = ? LIMIT 1");
        $stmt->execute([$pid]);
        $pname = $stmt->fetchColumn() ?: 'Unassigned';

        if ($type === 'IAMMETER') {
            // Multi-phase kWh calculation
            $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([$meter_id, "$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([$meter_id, $first_ts, $last_ts]);
                $rows = $stmt3->fetchAll(PDO::FETCH_ASSOC);

                $sums = [$first_ts => 0, $last_ts => 0];
                foreach ($rows as $r) {
                    $sums[$r['reading_timestamp']] += floatval($r['energy_total']);
                }

                $kwh = max(0, ($sums[$last_ts] - $sums[$first_ts]) / 1);  // Stored in Wh or kWh already
                $cost = calculateTieredCost($kwh, $tiers_by_type['electricity'] ?? []);

                $total_energy += $kwh;

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

        } elseif ($type === 'FLECTO') {
            $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_row = $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_row = $stmt->fetch(PDO::FETCH_ASSOC);

            if ($start_row && $end_row) {
                $start_kwh = floatval($start_row['reg_a'] ?? 0)
                           + floatval($start_row['reg_e'] ?? 0)
                           + floatval($start_row['reg_f'] ?? 0)
                           + floatval($start_row['reg_h'] ?? 0)
                           + floatval($start_row['reg_l'] ?? 0);

                $end_kwh = floatval($end_row['reg_a'] ?? 0)
                         + floatval($end_row['reg_e'] ?? 0)
                         + floatval($end_row['reg_f'] ?? 0)
                         + floatval($end_row['reg_h'] ?? 0)
                         + floatval($end_row['reg_l'] ?? 0);

                $kwh = max(0, $end_kwh - $start_kwh);
                $cost = calculateTieredCost($kwh, $tiers_by_type['electricity'] ?? []);


                $total_energy += $kwh;

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

        } elseif ($type === 'WATER') {
            $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.00;
            $cost = calculateTieredCost($kl, $tiers_by_type['water'] ?? []);

            $total_water += $kl;

            $reports[] = [
                'type'     => 'Water',
                'property' => $pname,
                'meter'    => $serial,
                'usage'    => round($kl, 2),
                'cost'     => round($cost, 2),
                'unit'     => 'kl'
            ];
        }
    }
}
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 $row) {
        fputcsv($out, [
            $row['type'],
            $row['property'],
            $row['meter'],
            $row['usage'],
            $row['unit'],
            $row['cost']
        ]);
    }

    fclose($out);
    exit;
}

?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Admin Utility Reports</title>
    <style>
        body { font-family: Arial, sans-serif; background: #051a25; color: white; padding: 30px; }
        h1 { color: #7ed957; }
        form label { margin-right: 10px; }
        form select, input[type="date"] { padding: 8px; border-radius: 6px; border: none; margin-right: 10px; background: #1b3a4d; color: #fff; }
        button { padding: 8px 16px; background: #7ed957; color: #000; border: none; border-radius: 6px; cursor: pointer; }
        .summary { display: flex; gap: 20px; margin: 20px 0; }
        .card { background: #1a3c4f; padding: 20px; border-radius: 10px; flex: 1; text-align: center; }
        .card h3 { margin: 0 0 10px; color: #7ed957; }
        table { width: 100%; border-collapse: collapse; background: #122c4c; margin-top: 20px; }
        th, td { padding: 10px; border: 1px solid #7ed957; text-align: center; }
        th { background: #1b3a4d; color: #7ed957; }
        tr:nth-child(even) { background: #1b3a4d; }
        a.btn { background: #7ed957; color: #000; padding: 10px 20px; text-decoration: none; border-radius: 6px; display: inline-block; margin-top: 20px; }
        .nav-btn {
            display: inline-block;
            background: #7ed957;
            color: #000;
            padding: 10px 20px;
            border-radius: 8px;
            text-decoration: none;
            font-weight: bold;
            margin-bottom: 30px;
        }

        .nav-btn:hover {
            background: #00cc77;
        }
    </style>
    <link rel="stylesheet" href="styles.css">
</head>
<body>
    <a href="admin-dashboard.php" class="nav-btn">⬅ Back to Dashboard</a>
    <h1>📊 Admin Utility Reports</h1>
    <form method="get">
        <select name="landlord_id" onchange="this.form.submit()">
            <option value="">-- Select Landlord --</option>
            <?php foreach ($landlords as $id => $name): ?>
                <option value="<?= $id ?>" <?= ($landlord_id == $id) ? 'selected' : '' ?>><?= htmlspecialchars($name) ?></option>
            <?php endforeach; ?>
        </select>

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

        <input type="date" name="start_date" value="<?= htmlspecialchars($start_date) ?>">
        <input type="date" name="end_date" value="<?= htmlspecialchars($end_date) ?>">
        <button type="submit">🔍 Filter</button>
    </form>

    <h2>Summary</h2>
    <p>Total Electricity: <?= round($total_energy, 2) ?> kWh</p>
    <p>Total Water: <?= round($total_water, 2) ?> kl</p>

    <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">⬇️ Export CSV</a>

    <table border="1" cellpadding="8" cellspacing="0">
        <tr>
            <th>Type</th>
            <th>Property</th>
            <th>Meter</th>
            <th>Usage</th>
            <th>Unit</th>
            <th>Cost (R)</th>
        </tr>
        <?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; ?>
    </table>
</body>
</html>
