<?php
// ======================================================================
// invoice_engine.php — Unified Billing Engine for RBE Utilities
// ======================================================================
// This engine generates a complete invoice breakdown for ANY tenant,
// for ANY date range, using:
//  • IAMMETER energy (3-phase complete timestamps)
//  • FLECTO energy (register sums)
//  • Water consumption (L → kL)
//  • Tiered billing (Electricity + Water)
//  • Tenant-specific overrides
//  • Global settings with proportional distribution
//  • Extra fees (per-tenant)
// ======================================================================

if (!function_exists('calculate_invoice')):

function calculate_invoice(PDO $conn, int $tenant_id, string $start_date, string $end_date): array
{
    // ---------------------------------------------------------------
    // Basic Validation
    // ---------------------------------------------------------------
    if ($tenant_id <= 0) {
        return ['error' => 'Invalid tenant_id'];
    }

    // Normalize date boundaries
    $start_dt = $start_date . " 00:00:00";
    $end_dt   = $end_date   . " 23:59:59";

    // ---------------------------------------------------------------
    // Fetch tenant + landlord
    // ---------------------------------------------------------------
    $st = $conn->prepare("SELECT fullname FROM rbe_users WHERE id = ?");
    $st->execute([$tenant_id]);
    $tenant_name = $st->fetchColumn() ?: 'Unknown Tenant';

    $st = $conn->prepare("SELECT landlord_id FROM tenant_landlord_map WHERE tenant_id = ? LIMIT 1");
    $st->execute([$tenant_id]);
    $landlord_id = (int)$st->fetchColumn();
    if (!$landlord_id) {
        return ['error' => 'Tenant has no landlord'];
    }

    // ---------------------------------------------------------------
    // Load tiers (tenant first, then landlord/global)
    // ---------------------------------------------------------------
    $stmt = $conn->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) {
        $key = strtolower(trim($t['type']));
        $tiers_by_type[$key][] = $t;
    }

    // ---------------------------------------------------------------
    // Helpers
    // ---------------------------------------------------------------
    $calcTiered = function(float $amount, array $tiers): float {
        if ($amount <= 0 || empty($tiers)) return 0.0;
        $total = 0.0;
        foreach ($tiers as $tier) {
            $from = (float)($tier['tier_from'] ?? 0);
            $to   = ($tier['tier_to'] !== null) ? (float)$tier['tier_to'] : INF;
            $rate = (float)($tier['rate'] ?? 0);
            if ($amount <= $from) continue;
            if ($to <= $from) continue;
            $billable = min($amount, $to) - $from;
            if ($billable > 0) {
                $total += $billable * $rate;
            }
        }
        return round($total, 2);
    };

    $sumFlecto = function($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);
    };

    // ---------------------------------------------------------------
    // Start building invoice lines
    // ---------------------------------------------------------------
    $lines = [];
    $total_due = 0.0;

    // ---------------------------------------------------------------
    // IAMMETER
    // ---------------------------------------------------------------
    $stmt = $conn->prepare("SELECT id, serial, property_id FROM smart_meters WHERE tenant_id = ?");
    $stmt->execute([$tenant_id]);
    $iammeters = $stmt->fetchAll(PDO::FETCH_ASSOC);

    foreach ($iammeters as $m) {
        $mid = (int)$m['id'];

        // Get timestamps with 3-phase complete
        $q = $conn->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
        ");
        $q->execute([$mid, $start_dt, $end_dt]);
        $ts = $q->fetchAll(PDO::FETCH_COLUMN);

        if (count($ts) < 2) continue;

        $first = $ts[0];
        $last  = $ts[count($ts)-1];

        // Sum energy totals (could be Wh or kWh depending on your source)
        $q2 = $conn->prepare("
            SELECT reading_timestamp, energy_total
            FROM smart_meter_readings
            WHERE meter_id = ? AND reading_timestamp IN (?,?)
        ");
        $q2->execute([$mid, $first, $last]);
        $rows = $q2->fetchAll(PDO::FETCH_ASSOC);

        $sums = [$first => 0, $last => 0];
        foreach ($rows as $r) {
            $sums[$r['reading_timestamp']] += (float)$r['energy_total'];
        }

        // If ever stored in Wh, divide by 1000. (Invoice uses old logic: /1)
        $kwh = max(($sums[$last] - $sums[$first]) / 1, 0);

        $cost = $calcTiered($kwh, $tiers_by_type['electricity'] ?? []);

        // Property lookup
        $q3 = $conn->prepare("SELECT property_number FROM properties WHERE id = ?");
        $q3->execute([$m['property_id']]);
        $property = $q3->fetchColumn() ?: 'Unknown';

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

    // ---------------------------------------------------------------
    // FLECTO
    // ---------------------------------------------------------------
    $stmt = $conn->prepare("SELECT serial_number, property_id FROM flecto_meters WHERE tenant_id = ?");
    $stmt->execute([$tenant_id]);
    $flist = $stmt->fetchAll(PDO::FETCH_ASSOC);

    foreach ($flist as $m) {
        $serial = $m['serial_number'];

        $q = $conn->prepare("
            SELECT *
            FROM flecto_meter_readings
            WHERE serial = ?
              AND created_at BETWEEN ? AND ?
            ORDER BY created_at ASC
        ");
        $q->execute([$serial, $start_dt, $end_dt]);
        $rows = $q->fetchAll(PDO::FETCH_ASSOC);

        if (count($rows) < 2) continue;

        $first = $rows[0];
        $last  = $rows[count($rows)-1];

        $kwh = max($sumFlecto($last) - $sumFlecto($first), 0);
        $cost = $calcTiered($kwh, $tiers_by_type['electricity'] ?? []);

        $q3 = $conn->prepare("SELECT property_number FROM properties WHERE id = ?");
        $q3->execute([$m['property_id']]);
        $property = $q3->fetchColumn() ?: 'Unknown';

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

    // ---------------------------------------------------------------
    // WATER
    // ---------------------------------------------------------------
    $stmt = $conn->prepare("SELECT serial_number, property_id FROM water_meters WHERE tenant_id = ?");
    $stmt->execute([$tenant_id]);
    $wlist = $stmt->fetchAll(PDO::FETCH_ASSOC);

    foreach ($wlist as $m) {
        $serial = $m['serial_number'];

        $q = $conn->prepare("
            SELECT MIN(volume_liters), MAX(volume_liters)
            FROM water_meter_readings
            WHERE serial_number = ?
              AND reading_datetime BETWEEN ? AND ?
        ");
        $q->execute([$serial, $start_dt, $end_dt]);
        [$minL, $maxL] = $q->fetch(PDO::FETCH_NUM);

        if ($minL === null || $maxL === null) continue;

        $kl = max($maxL - $minL, 0) / 1000;
        $cost = $calcTiered($kl, $tiers_by_type['water'] ?? []);

        $q3 = $conn->prepare("SELECT property_number FROM properties WHERE id = ?");
        $q3->execute([$m['property_id']]);
        $property = $q3->fetchColumn() ?: 'Unknown';

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

    // ---------------------------------------------------------------
    // EXTRA FEES (tenant settings + global)
    // ---------------------------------------------------------------
    require_once __DIR__ . '/functions.php'; // needed for distribute_fee()

    // Fee mode defaults
    $modes = ['sewage'=>'flat','refuse'=>'flat','rates'=>'flat','other'=>'flat'];
    $q = $conn->prepare("SELECT fee_type, mode FROM billing_fee_modes WHERE landlord_id=?");
    $q->execute([$landlord_id]);
    foreach ($q->fetchAll(PDO::FETCH_ASSOC) as $m) {
        $modes[strtolower(trim($m['fee_type']))] = $m['mode'];
    }

    // Tenant + global settings
    $q = $conn->prepare("SELECT * FROM billing_settings WHERE landlord_id=? AND tenant_id=? ORDER BY id DESC LIMIT 1");
    $q->execute([$landlord_id, $tenant_id]);
    $tenant_settings = $q->fetch(PDO::FETCH_ASSOC) ?: [];

    $q = $conn->prepare("SELECT * FROM billing_settings WHERE landlord_id=? AND tenant_id IS NULL ORDER BY id DESC LIMIT 1");
    $q->execute([$landlord_id]);
    $global_settings = $q->fetch(PDO::FETCH_ASSOC) ?: [];

    // Property scope for proportional distribution
    $q = $conn->prepare("SELECT DISTINCT property_id FROM tenant_landlord_map WHERE tenant_id=? AND landlord_id=?");
    $q->execute([$tenant_id, $landlord_id]);
    $tenant_properties = array_map('intval', $q->fetchAll(PDO::FETCH_COLUMN));
    if (!$tenant_properties) $tenant_properties = [null];

    $addFee = function(string $label, float $amount) use (&$lines, &$total_due) {
        if ($amount > 0) {
            $lines[] = [
                'type'     => $label,
                'property' => '—',
                'meter'    => '-',
                'usage'    => '-',
                'unit'     => '-',
                'cost'     => round($amount, 2),
            ];
            $total_due += $amount;
        }
    };

    // A) Tenant overrides
    foreach ([
        ['label'=>'Sewage Fee','col'=>'sewage_fee'],
        ['label'=>'Refuse Removal Fee','col'=>'refuse_removal_fee'],
        ['label'=>'Rates & Taxes','col'=>'rates_and_taxes']
    ] as $f) {
        $v = (float)($tenant_settings[$f['col']] ?? 0);
        if ($v > 0) $addFee($f['label'], $v);
    }

    $others_tenant = [
        ['name'=>$tenant_settings['other_fee_name']   ?? '', 'amt'=>(float)($tenant_settings['other_fee']   ?? 0)],
        ['name'=>$tenant_settings['other_fee_name_2'] ?? '', 'amt'=>(float)($tenant_settings['other_fee_2'] ?? 0)],
        ['name'=>$tenant_settings['other_fee_name_3'] ?? '', 'amt'=>(float)($tenant_settings['other_fee_3'] ?? 0)],
        ['name'=>$tenant_settings['other_fee_name_4'] ?? '', 'amt'=>(float)($tenant_settings['other_fee_4'] ?? 0)],
        ['name'=>$tenant_settings['other_fee_name_5'] ?? '', 'amt'=>(float)($tenant_settings['other_fee_5'] ?? 0)],
    ];

    $has_other_override = false;
    foreach ($others_tenant as $o) {
        if ($o['amt'] > 0) $has_other_override = true;
        if ($o['amt'] > 0) $addFee(trim($o['name'] ?: 'Other Fee'), $o['amt']);
    }

    // B) Global fees (distributed)
    foreach ([
        ['label'=>'Sewage Fee','col'=>'sewage_fee','mode'=>'sewage'],
        ['label'=>'Refuse Removal Fee','col'=>'refuse_removal_fee','mode'=>'refuse'],
        ['label'=>'Rates & Taxes','col'=>'rates_and_taxes','mode'=>'rates']
    ] as $f) {
        if ((float)($tenant_settings[$f['col']] ?? 0) > 0) continue; // overridden

        $global_amt = (float)($global_settings[$f['col']] ?? 0);
        if ($global_amt <= 0) continue;

        $share = 0.0;
        foreach ($tenant_properties as $pid) {
            $alloc = distribute_fee($conn, $landlord_id, $global_amt, $modes[$f['mode']], $pid);
            if (isset($alloc[$tenant_id])) $share += $alloc[$tenant_id];
        }
        if ($share > 0) $addFee($f['label'], $share);
    }

    // Other global fees (if no tenant override)
    if (!$has_other_override) {
        $global_other = [
            ['name'=>$global_settings['other_fee_name']   ?? 'Other Fee', 'amt'=>(float)($global_settings['other_fee']   ?? 0)],
            ['name'=>$global_settings['other_fee_name_2'] ?? 'Other Fee', 'amt'=>(float)($global_settings['other_fee_2'] ?? 0)],
            ['name'=>$global_settings['other_fee_name_3'] ?? 'Other Fee', 'amt'=>(float)($global_settings['other_fee_3'] ?? 0)],
            ['name'=>$global_settings['other_fee_name_4'] ?? 'Other Fee', 'amt'=>(float)($global_settings['other_fee_4'] ?? 0)],
            ['name'=>$global_settings['other_fee_name_5'] ?? 'Other Fee', 'amt'=>(float)($global_settings['other_fee_5'] ?? 0)],
        ];

        foreach ($global_other as $o) {
            if ($o['amt'] <= 0) continue;

            $share = 0.0;
            foreach ($tenant_properties as $pid) {
                $alloc = distribute_fee($conn, $landlord_id, $o['amt'], $modes['other'], $pid);
                if (isset($alloc[$tenant_id])) $share += $alloc[$tenant_id];
            }

            if ($share > 0) {
                $label = trim($o['name']) ?: 'Other Fee';
                $addFee($label, $share);
            }
        }
    }

    // C) Extra per-tenant fees
    $q = $conn->prepare("
        SELECT fee_type, custom_label, amount
        FROM extra_fees
        WHERE tenant_id = ? AND landlord_id = ? AND active = 1
    ");
    $q->execute([$tenant_id, $landlord_id]);
    foreach ($q->fetchAll(PDO::FETCH_ASSOC) as $ef) {
        $base = trim($ef['custom_label'] ?? '');
        if ($base === '') {
            $type = strtolower((string)$ef['fee_type']);
            $label = match ($type) {
                'aircon_fee'      => 'Aircon Fee',
                'generator_fee'   => 'Generator Fee',
                'elec_avail_fee'  => 'Electricity Availability Fee',
                'elec_basic_fee'  => 'Electricity Basic Fee',
                'water_avail_fee' => 'Water Availability Fee',
                'water_basic_fee' => 'Water Basic Fee',
                default           => 'Extra Fee',
            };
        } else {
            $label = $base;
        }
        $amt = (float)$ef['amount'];
        if ($amt > 0) $addFee($label, $amt);
    }

    // ---------------------------------------------------------------
    // Build Response
    // ---------------------------------------------------------------
    return [
        'tenant' => [
            'id'   => $tenant_id,
            'name' => $tenant_name,
            'landlord_id' => $landlord_id,
        ],

        'period' => [
            'start' => $start_date,
            'end'   => $end_date,
        ],

        'lines'  => $lines,

        'totals' => [
            'grand_total' => round($total_due, 2),
        ],
    ];
}

endif; // end function_exists

?>
