<?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;
}

$user_id = (int)$_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 (!in_array($role, ['admin', 'landlord'])) die("❌ Unauthorized access.");

$is_admin = ($role === 'admin');
$landlord_id = $is_admin ? (int)($_GET['landlord_id'] ?? 0) : $user_id;
if (!$landlord_id) die("⚠️ landlord_id is required.");

$selected_tenant_id = isset($_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');

// Tenants for this landlord
$stmt = $conn_rbe->prepare("
  SELECT tlm.tenant_id, u.fullname
  FROM tenant_landlord_map tlm
  JOIN rbe_users u ON tlm.tenant_id = u.id
  WHERE tlm.landlord_id = ?
");
$stmt->execute([$landlord_id]);
$tenant_rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
$tenants = [];
foreach ($tenant_rows as $t) $tenants[(int)$t['tenant_id']] = $t['fullname'];

/** Tiered pricing (continuous, landlord tiers first, then tenant overrides if present) */
function calculateTieredCost($usage, $tiers) {
    if ($usage <= 0 || empty($tiers)) return 0.0;

    $cost = 0.0;
    $remaining = $usage;

    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;

        if ($usage <= $from) continue;

        // Calculate the usable portion inside this tier
        $upper = ($to === INF) ? $usage : min($usage, $to);
        $block = max(0, $upper - $from);

        $use = min($remaining, $block);
        if ($use > 0) {
            $cost += $use * $rate;
            $remaining -= $use;
        }
    }

    return round($cost, 2);
}


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 fetchWaterUsage($conn, $serial, $start_date, $end_date) {
    // Uses reading_datetime per your existing code
    $stmt = $conn->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"]);
    return $stmt->fetch(PDO::FETCH_NUM);
}

$reports = [];
$total_energy = 0.0;
$total_water  = 0.0;
$total_fees   = 0.0;

foreach ($tenants as $tenant_id => $tenant_name) {
    if ($selected_tenant_id && $selected_tenant_id != $tenant_id) continue;

    $tenant_start = $_GET["start_date_$tenant_id"] ?? $start_date;
    $tenant_end   = $_GET["end_date_$tenant_id"]   ?? $end_date;

    // Property label
    $stmt = $conn_rbe->prepare("
        SELECT p.id AS property_id, 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]);
    $property = $stmt->fetch(PDO::FETCH_ASSOC);
    $pname = $property['property_number'] ?? 'Unknown';

    /** 
 * Correct Tier Fetch: Global + Tenant tiers, correct override order
 * Global = tenant_id IS NULL OR tenant_id = 0
 * Tenant overrides ALWAYS applied last
 */
$stmt = $conn_rbe->prepare("
    SELECT *
    FROM billing_tiers
    WHERE landlord_id = ?
      AND (tenant_id = ? OR tenant_id IS NULL OR tenant_id = 0)
    ORDER BY
        CASE WHEN tenant_id IS NULL OR tenant_id = 0 THEN 0 ELSE 1 END,
        type,
        tier_from ASC
");
$stmt->execute([$landlord_id, $tenant_id]);
$tiers = $stmt->fetchAll(PDO::FETCH_ASSOC);

/* Build merged tier lists per type:
   - Start with all GLOBAL tiers
   - Append TENANT tiers at the end so they override
*/
$tiers_by_type = [];
foreach ($tiers as $t) {
    $type = strtolower($t['type']);
    if (!isset($tiers_by_type[$type])) $tiers_by_type[$type] = [];

    if (empty($t['tenant_id'])) {
        // global tier → append first
        $tiers_by_type[$type][] = $t;
    } else {
        // tenant override → push to the END
        $tiers_by_type[$type][] = $t;
    }
}


    $tenant_usage = ['electricity' => 0.0, 'water' => 0.0];

    // ------------- 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, "$tenant_start 00:00:00", "$tenant_end 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) {
                $ts = $r['reading_timestamp'];
                $sums[$ts] += (float)$r['energy_total'];
            }

            // NOTE: If energy_total is in Wh, divide by 1000. Adjust if needed.
            $kwh = max(0, ($sums[$last_ts] - $sums[$first_ts]) / 1);
            $cost = calculateTieredCost($kwh, $tiers_by_type['electricity'] ?? []);
            $tenant_usage['electricity'] += $kwh;

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

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

    // ------------- FLECTO -------------
    $stmt = $conn_rbe->prepare("SELECT id, 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, "$tenant_start 00:00:00", "$tenant_end 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'] ?? []);
            $tenant_usage['electricity'] += $kwh;

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

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

    // ------------- WATER -------------
    $stmt = $conn_rbe->prepare("SELECT id, serial_number, property_id FROM water_meters WHERE tenant_id = ?");
    $stmt->execute([$tenant_id]);
    foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $meter) {
        [$minL, $maxL] = fetchWaterUsage($conn_rbe, $meter['serial_number'], $tenant_start, $tenant_end);
        if ($minL !== null && $maxL !== null) {
            $kl   = max(0, $maxL - $minL) / 1000.0;
            $cost = calculateTieredCost($kl, $tiers_by_type['water'] ?? []);
            $tenant_usage['water'] += $kl;

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

            $reports[] = [
                'tenant_id' => $tenant_id,
                'tenant'    => $tenant_name,
                'type'      => 'Water',
                'property'  => $meter_property,
                'meter'     => $meter['serial_number'],
                'usage'     => round($kl, 2),
                'unit'      => 'kl',
                'cost'      => round($cost, 2)
            ];
        }
    }

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

// 1. Fetch 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. Fetch TENANT override fees
$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 tenant values so global still applies
foreach ($tenant_fees as $key => $val) {
    if ($val === '' || $val === null) {
        unset($tenant_fees[$key]);
    }
}

// 4. Merge: tenant overrides global when value exists
$fees_final = array_merge($global_fees, $tenant_fees);

// 5. Define fee mapping (other fees use paired columns)
$fee_map = [
    'sewage_fee'         => 'Sewage Fee',
    'refuse_removal_fee' => 'Refuse Removal Fee',
    'rates_and_taxes'    => 'Rates & Taxes',

    // these appear with amounts inside other_fee fields
    '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 “other fees” (paired name + amount)
    if (str_starts_with($col, 'other_fee_name')) {

        // Determine correct suffix:
        // other_fee_name   → other_fee
        // other_fee_name_2 → other_fee_2
        if ($col === 'other_fee_name') {
            $suffix = ''; // first fee is "other_fee"
        } else {
            $n = str_replace('other_fee_name_', '', $col);
            $suffix = "_$n";
        }

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

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

        continue;
    }

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

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



    /* OPTIONAL: Also display (or charge) landlord "Global" rows.
       Uncomment if needed — and decide whether to add them to $total_fees.
    $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_rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    // Render like above, but consider distribution modes if you intend to charge here.
    */

    $total_energy += $tenant_usage['electricity'];
    $total_water  += $tenant_usage['water'];
}

// ----------- RENDER -----------
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>RBE Reports</title>
<style>
    body { font-family: Arial, sans-serif; background: #051a25; color: white; padding: 20px; }
    h1 { color: #7ed957; }
    table { width: 100%; margin-top: 10px; border-collapse: collapse; background: #122c4c; }
    th, td { padding: 10px; border: 1px solid #7ed957; text-align: center; }
    th { background: #1b3a4d; }
    .summary { display: flex; flex-wrap: wrap; gap: 20px; margin: 20px 0; justify-content: center; }
    .summary-box { background: #1a3c4f; border-radius: 10px; padding: 15px 25px; flex: 1 1 220px; text-align: center; position: relative; }
    .summary-box h3 { margin: 0 0 5px 0; color: #7ed957; }
    .summary-box p { font-size: 1.2em; margin: 0; }
    a.btn { display: inline-block; padding: 10px 20px; background: #7ed957; color: #000; border-radius: 8px; text-decoration: none; margin: 20px 0; }
    .nav-btn { background: #7ed957; color: #000; padding: 10px 20px; border-radius: 8px; text-decoration: none; margin-bottom: 20px; display: inline-block; }
    .tenant-block { background: #0b1f33; padding: 0; border-radius: 12px; margin-top: 30px; box-shadow: 0 0 10px rgba(0,0,0,0.4); overflow: hidden; }
    .tenant-block summary { background: #102840; padding: 15px 20px; font-size: 1.1em; color: #7ed957; cursor: pointer; font-weight: bold; display: flex; justify-content: space-between; align-items: center; }
    .fee-table { margin-top: 0; }
    .summary-total { font-size: 0.9em; color: #ffc107; }
    .final-totals { margin-top: 40px; text-align: center; font-size: 1.2em; background: #1a3c4f; padding: 15px; border-radius: 10px; }
    tr.zero-usage td { color: #ffc107; }
    .tenant-filter-form { padding: 15px 20px; background: #122c4c; border-top: 1px solid #1b3a4d; display: flex; gap: 10px; align-items: center; justify-content: flex-end; flex-wrap: wrap; }
    .tenant-filter-form input[type="date"] { background: #0b1f33; color: #fff; border: 1px solid #7ed957; padding: 6px; border-radius: 6px; font-size: 0.9em; }
    .tenant-filter-form button { background: #7ed957; color: #000; padding: 6px 12px; border-radius: 6px; font-weight: bold; border: none; cursor: pointer; }
</style>
</head>
<body>

<a href="<?= ($role === 'landlord') ? 'landlord-dashboard.php' : 'admin-dashboard.php' ?>" class="nav-btn">⬅️ Back to Dashboard</a>

<h1>📊 Full Utility Report</h1>
<p><strong>Date Range:</strong> <?= htmlspecialchars($start_date) ?> to <?= htmlspecialchars($end_date) ?></p>

<div class="summary">
    <div class="summary-box">
        <h3>🔌 Electricity</h3>
        <p><?= round($total_energy,1) ?> kWh</p>
    </div>
    <div class="summary-box">
        <h3>🚿 Water</h3>
        <p><?= round($total_water,1) ?> kl</p>
    </div>
    <div class="summary-box">
        <h3>📦 Extra Fees</h3>
        <p>R<?= number_format($total_fees,2) ?></p>
    </div>
</div>

<?php
$groupedByTenant = [];
foreach ($reports as $row) {
    $groupedByTenant[$row['tenant']][] = $row;
}
?>

<?php foreach ($groupedByTenant as $tenantName => $entries): ?>
<details open class="tenant-block">
  <summary>
    👤 <?= htmlspecialchars($tenantName) ?>
    <span class="summary-total">💰 R<?= number_format(array_sum(array_column($entries, 'cost')), 2) ?></span>
  </summary>

  <form class="tenant-filter-form" method="get">
    <input type="hidden" name="landlord_id" value="<?= htmlspecialchars($landlord_id) ?>">
    <input type="hidden" name="tenant_id" value="<?= htmlspecialchars($entries[0]['tenant_id']) ?>">
    <label>From:</label>
    <input type="date" name="start_date_<?= $entries[0]['tenant_id'] ?>" value="<?= htmlspecialchars($_GET["start_date_{$entries[0]['tenant_id']}"] ?? $start_date) ?>">
    <label>To:</label>
    <input type="date" name="end_date_<?= $entries[0]['tenant_id'] ?>" value="<?= htmlspecialchars($_GET["end_date_{$entries[0]['tenant_id']}"] ?? $end_date) ?>">
    <button type="submit">🔁 Refresh</button>
  </form>

  <table class="fee-table">
    <tr>
        <th>Type</th>
        <th>Property</th>
        <th>Meter</th>
        <th>Usage (kWh / kl)</th>
        <th>Cost (R)</th>
    </tr>
    <?php foreach ($entries as $r): ?>
    <?php
        // Style as "fee row" if usage is a dash (i.e., non-usage line item)
        $is_fee_row = !is_numeric($r['usage']);
    ?>
    <tr class="<?= ($r['usage'] == 0 || $r['usage'] === '0') ? 'zero-usage' : '' ?>"
        style="<?= $is_fee_row ? 'background: #1e364e;' : '' ?>">
        <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>R<?= number_format($r['cost'], 2) ?></td>
    </tr>
    <?php endforeach; ?>
  </table>

  <div class="tenant-actions" style="text-align:center; margin:20px 0;">
    <a href="reports.php?landlord_id=<?= urlencode((string)$landlord_id) ?>" class="btn">🔄 Reset Full Report</a>
    <a href="print_invoice.php?tenant_id=<?= $entries[0]['tenant_id'] ?>&start_date=<?= htmlspecialchars($_GET["start_date_{$entries[0]['tenant_id']}"] ?? $start_date) ?>&end_date=<?= htmlspecialchars($_GET["end_date_{$entries[0]['tenant_id']}"] ?? $end_date) ?>" class="btn">🧾 Print Bill</a>
    <button onclick="exportTableToCSV()">⬇️ Export CSV (JS)</button>
  </div>

</details>
<?php endforeach; ?>

<div class="final-totals">
  ✅ Grand Total Due: <strong>R<?= number_format(array_sum(array_column($reports, 'cost')), 2) ?></strong><br>
  🔋 Total Electricity: <?= round($total_energy, 2) ?> kWh • 🚿 Water: <?= round($total_water, 2) ?> kl
</div>

<script>
function exportTableToCSV() {
    let csv = [];
    // Export all tables rows on page (simple export)
    const rows = document.querySelectorAll("table tr");
    rows.forEach(row => {
        const cols = row.querySelectorAll("td, th");
        let csvRow = [];
        cols.forEach(col => {
            csvRow.push('"' + col.innerText.replace(/"/g, '""') + '"');
        });
        csv.push(csvRow.join(","));
    });
    const csvContent = csv.join("\n");
    const blob = new Blob([csvContent], { type: "text/csv;charset=utf-8;" });
    const url = URL.createObjectURL(blob);
    const link = document.createElement("a");
    link.setAttribute("href", url);
    link.setAttribute("download", "report.csv");
    document.body.appendChild(link);
    link.click();
    document.body.removeChild(link);
}
</script>

</body>
</html>
