<?php
require_once __DIR__ . '/../../../includes/auth.php';
requireLogin();
requirePermission('view_billing');

require_once __DIR__ . '/../../../config/database.php';
require_once __DIR__ . '/../engine/calculate_block_bill.php';

$userId        = (int)($_SESSION['user_id'] ?? 0);
$companyIdSess = (int)($_SESSION['company_id'] ?? 0);
$isSuper       = !empty($_SESSION['is_super_admin']);

$periodId = isset($_GET['period_id']) ? (int)$_GET['period_id'] : 0;
if ($periodId <= 0) {
    die("Missing period_id.");
}

/* ===============================
   Fetch Billing Period
================================= */
$stmt = $pdo->prepare("SELECT * FROM billing_periods WHERE id = ?");
$stmt->execute([$periodId]);
$period = $stmt->fetch(PDO::FETCH_ASSOC);

if (!$period) {
    die("Billing period not found.");
}

if (($period['status'] ?? '') !== 'OPEN') {
    die("Billing period is not OPEN.");
}

$periodCompanyId = (int)$period['company_id'];
$periodStart     = $period['period_start'];
$periodEnd       = $period['period_end'];

if (!$isSuper && $periodCompanyId !== $companyIdSess) {
    die("Access denied for this billing period.");
}

/* ===============================
   Helpers
================================= */
function ym_from_date(string $date): string {
    return substr($date, 0, 7);
}

function money(float $v): float {
    return round($v, 2);
}

/* ===============================
   Fetch ACTIVE Operational Meters
================================= */
$stmt = $pdo->prepare("
    SELECT id, company_id, meter_number, tariff_id
    FROM operational_meters
    WHERE company_id = ?
      AND status = 'ACTIVE'
    ORDER BY id ASC
");
$stmt->execute([$periodCompanyId]);

$meters = $stmt->fetchAll(PDO::FETCH_ASSOC);

if (!$meters) {
    die("No ACTIVE operational meters found for this company.");
}

/* ===============================
   Start Generation
================================= */
$vatRate = 0.15;

$created = 0;
$skipped = 0;

try {
    $pdo->beginTransaction();

    foreach ($meters as $m) {
        $meterId  = (int)$m['id'];
        $tariffId = isset($m['tariff_id']) ? (int)$m['tariff_id'] : 0;

        if ($tariffId <= 0) {
            $skipped++;
            continue;
        }

        /* ===============================
           Prevent duplicate invoice
        ================================= */
        $stmt = $pdo->prepare("
            SELECT id
            FROM invoices
            WHERE meter_id = ?
              AND period_start = ?
              AND period_end = ?
              AND status <> 'CANCELLED'
            LIMIT 1
        ");
        $stmt->execute([$meterId, $periodStart, $periodEnd]);
        $existingInvoiceId = $stmt->fetchColumn();

        if ($existingInvoiceId) {
            $skipped++;
            continue;
        }

        /* ===============================
           Get start reading
        ================================= */
        $stmt = $pdo->prepare("
            SELECT reading_value
            FROM meter_readings
            WHERE meter_id = ?
              AND reading_date < ?
            ORDER BY reading_date DESC, id DESC
            LIMIT 1
        ");
        $stmt->execute([$meterId, $periodStart]);
        $startReading = $stmt->fetchColumn();

        /* ===============================
           Get end reading
        ================================= */
        $stmt = $pdo->prepare("
            SELECT reading_value
            FROM meter_readings
            WHERE meter_id = ?
              AND reading_date <= ?
            ORDER BY reading_date DESC, id DESC
            LIMIT 1
        ");
        $stmt->execute([$meterId, $periodEnd]);
        $endReading = $stmt->fetchColumn();

        if ($startReading === false || $endReading === false) {
            $skipped++;
            continue;
        }

        $startReading = (float)$startReading;
        $endReading   = (float)$endReading;

        $consumption = $endReading - $startReading;

        if ($consumption < 0) {
            $skipped++;
            continue;
        }

        /* ===============================
           Ensure tariff exists
        ================================= */
        $stmt = $pdo->prepare("
            SELECT id, tariff_name
            FROM tariffs
            WHERE id = ?
            LIMIT 1
        ");
        $stmt->execute([$tariffId]);
        $tariff = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$tariff) {
            $skipped++;
            continue;
        }

        /* ===============================
           Calculate usage
        ================================= */
        $calc = calculateBlockBill($pdo, $tariffId, $consumption);

        $subtotal = 0.0;
        $invoiceLines = [];

        foreach ($calc['breakdown'] as $row) {
            $lineTotal = money((float)$row['amount']);
            $subtotal += $lineTotal;

            $invoiceLines[] = [
                'description' => $row['label'],
                'quantity'    => (float)$row['units'],
                'unit_price'  => money((float)$row['rate']),
                'total'       => $lineTotal
            ];
        }

        $subtotal = money($subtotal);

        /* ===============================
           Charges
        ================================= */
        $stmt = $pdo->prepare("
            SELECT bc.charge_name,
                   bc.charge_type,
                   bc.charge_value,
                   bc.apply_order
            FROM meter_charges mc
            JOIN billing_charges bc ON bc.id = mc.charge_id
            WHERE mc.meter_id = ?
              AND bc.active = 1
              AND mc.start_date <= ?
              AND (mc.end_date IS NULL OR mc.end_date >= ?)
            ORDER BY bc.apply_order ASC, bc.id ASC
        ");
        $stmt->execute([$meterId,$periodEnd,$periodStart]);

        $meterCharges = $stmt->fetchAll(PDO::FETCH_ASSOC);

        foreach ($meterCharges as $c) {

            if ($c['charge_type'] === 'percentage') {
                $amount = money($subtotal * (((float)$c['charge_value']) / 100));
            } else {
                $amount = money((float)$c['charge_value']);
            }

            if ($amount <= 0) continue;

            $subtotal += $amount;

            $invoiceLines[] = [
                'description' => $c['charge_name'],
                'quantity'    => 1,
                'unit_price'  => $amount,
                'total'       => $amount
            ];
        }

        $subtotal  = money($subtotal);
        $vatAmount = money($subtotal * $vatRate);
        $total     = money($subtotal + $vatAmount);

        /* ===============================
           Invoice number
        ================================= */
        $ym = ym_from_date($periodStart);

        $stmt = $pdo->prepare("
            SELECT id, last_seq
            FROM invoice_counters
            WHERE ym = ? AND company_id = ?
            LIMIT 1 FOR UPDATE
        ");
        $stmt->execute([$ym,$periodCompanyId]);
        $counter = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$counter) {
            $pdo->prepare("INSERT INTO invoice_counters (ym, company_id, last_seq) VALUES (?, ?, 0)")
                ->execute([$ym,$periodCompanyId]);

            $counterId = (int)$pdo->lastInsertId();
            $lastSeq = 0;
        } else {
            $counterId = (int)$counter['id'];
            $lastSeq = (int)$counter['last_seq'];
        }

        $newSeq = $lastSeq + 1;

        $pdo->prepare("UPDATE invoice_counters SET last_seq = ? WHERE id = ?")
            ->execute([$newSeq,$counterId]);

        $invoiceNumber = sprintf("EDM-INV-%s-%04d", $ym, $newSeq);

        $invoiceDate = date('Y-m-d');
        $dueDate     = date('Y-m-d', strtotime($periodEnd . ' +7 days'));

        /* ===============================
           Insert invoice
        ================================= */
        $stmt = $pdo->prepare("
            INSERT INTO invoices
            (invoice_number, company_id, meter_id, tariff_id, created_by,
             consumption, subtotal, vat_rate, vat_amount, total, status,
             period_start, period_end, invoice_date, due_date, created_at)
            VALUES
            (?, ?, ?, ?, ?,
             ?, ?, ?, ?, ?, 'DRAFT',
             ?, ?, ?, ?, NOW())
        ");
        $stmt->execute([
            $invoiceNumber,
            $periodCompanyId,
            $meterId,
            $tariffId,
            $userId,
            $consumption,
            $subtotal,
            $vatRate,
            $vatAmount,
            $total,
            $periodStart,
            $periodEnd,
            $invoiceDate,
            $dueDate
        ]);

        $invoiceId = (int)$pdo->lastInsertId();

        /* ===============================
           Insert invoice lines
        ================================= */
        $stmtLine = $pdo->prepare("
            INSERT INTO invoice_lines
            (invoice_id, description, quantity, unit_price, total)
            VALUES (?, ?, ?, ?, ?)
        ");

        foreach ($invoiceLines as $ln) {
            $stmtLine->execute([
                $invoiceId,
                $ln['description'],
                $ln['quantity'],
                money((float)$ln['unit_price']),
                money((float)$ln['total'])
            ]);
        }

        if ($vatAmount > 0) {
            $stmtLine->execute([$invoiceId,'VAT',1,$vatAmount,$vatAmount]);
        }

        $created++;
    }

    $pdo->commit();

} catch (Throwable $e) {
    $pdo->rollBack();
    die("Generation failed: " . $e->getMessage());
}

echo "<h3>Billing Generation Completed</h3>";
echo "<p>Created invoices: <b>{$created}</b></p>";
echo "<p>Skipped meters: <b>{$skipped}</b></p>";
echo "<p><a href='index.php?period_id={$periodId}'>Back to Manual Readings</a></p>";