kaffeekasse-saas/app/imports.php

<?php

declare(strict_types=1);

require_once __DIR__ . '/bootstrap.php';
require_once __DIR__ . '/ledger.php';

/**
 * Parses a decimal amount ("12,34" or "12.34") into cents. Returns null for
 * anything that is not a plausible positive amount.
 */
function imports_normalize_amount(string $raw): ?int
{
    $normalized = str_replace(',', '.', trim($raw));
    if (!preg_match('/^-?[0-9]+(?:\.[0-9]{1,2})?$/', $normalized)) {
        return null;
    }

    $cents = (int)round(((float)$normalized) * 100);

    return $cents > 0 ? $cents : null;
}

/**
 * Matches a CSV name against a participant's paypal_name or display_name
 * (case-insensitive).
 *
 * @return array{participant_id: int, display_name: string}|null
 */
function imports_find_participant(PDO $pdo, int $tenantId, string $name): ?array
{
    $name = trim($name);
    if ($name === '') {
        return null;
    }

    $stmt = $pdo->prepare(
        'SELECT id, display_name
         FROM participants
         WHERE tenant_id = ?
           AND (LOWER(paypal_name) = LOWER(?) OR LOWER(display_name) = LOWER(?))
         LIMIT 1'
    );
    $stmt->execute([$tenantId, $name, $name]);
    $row = $stmt->fetch();

    if ($row === false) {
        return null;
    }

    return [
        'participant_id' => (int)$row['id'],
        'display_name' => (string)$row['display_name'],
    ];
}

/**
 * A payment counts as a duplicate if the same participant already has a
 * non-voided ledger payment of the same amount on the same day.
 */
function imports_is_duplicate(PDO $pdo, int $tenantId, int $participantId, int $amountCents, string $bookedAtDate): bool
{
    $stmt = $pdo->prepare(
        "SELECT COUNT(*)
         FROM ledger_entries
         WHERE tenant_id = ?
           AND participant_id = ?
           AND type = 'payment'
           AND amount_cents = ?
           AND DATE(booked_at) = ?
           AND voided_at IS NULL"
    );
    $stmt->execute([$tenantId, $participantId, $amountCents, $bookedAtDate]);

    return (int)$stmt->fetchColumn() > 0;
}

/**
 * Parses a PayPal-style export CSV: column 0 is the booking date, column 3
 * the payer name, column 7 the gross amount. The first line is a header and
 * is skipped.
 *
 * @return list<array{row_number: int, raw_name: string, amount_raw: string, date_raw: string, raw: list<string>}>
 */
function imports_parse_csv(string $filePath): array
{
    $handle = fopen($filePath, 'r');
    if ($handle === false) {
        throw new RuntimeException('Die CSV-Datei konnte nicht gelesen werden.');
    }

    $rows = [];
    $rowNumber = 0;
    fgetcsv($handle); // Header ueberspringen

    while (($data = fgetcsv($handle, 1000, ',')) !== false) {
        $rowNumber++;
        $rows[] = [
            'row_number' => $rowNumber,
            'raw_name' => trim((string)($data[3] ?? '')),
            'amount_raw' => trim((string)($data[7] ?? '')),
            'date_raw' => trim((string)($data[0] ?? '')),
            'raw' => $data,
        ];
    }

    fclose($handle);

    return $rows;
}

function imports_create_batch(PDO $pdo, int $tenantId, string $originalFilename, string $checksum, ?int $createdByUserId): int
{
    $stmt = $pdo->prepare(
        'INSERT INTO payment_import_batches (tenant_id, original_filename, checksum, status, created_by_user_id)
         VALUES (?, ?, ?, ?, ?)'
    );
    $stmt->execute([$tenantId, $originalFilename, $checksum, 'previewed', $createdByUserId]);

    return (int)$pdo->lastInsertId();
}

function imports_store_row(
    PDO $pdo,
    int $batchId,
    int $rowNumber,
    ?int $participantId,
    string $rawName,
    int $amountCents,
    string $bookedAt,
    string $status,
    array $rawRow
): int {
    $stmt = $pdo->prepare(
        'INSERT INTO payment_import_rows
           (batch_id, row_num, participant_id, raw_name, amount_cents, booked_at, status, raw_json)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?)'
    );
    $stmt->execute([
        $batchId,
        $rowNumber,
        $participantId,
        $rawName,
        $amountCents,
        $bookedAt,
        $status,
        json_encode($rawRow, JSON_UNESCAPED_UNICODE),
    ]);

    return (int)$pdo->lastInsertId();
}

/**
 * @return array{batch: array{id: int, original_filename: string, status: string, created_at: string}, rows: list<array>}|null
 */
function imports_fetch_batch(PDO $pdo, int $tenantId, int $batchId): ?array
{
    $stmt = $pdo->prepare(
        'SELECT id, original_filename, status, created_at
         FROM payment_import_batches
         WHERE id = ? AND tenant_id = ?'
    );
    $stmt->execute([$batchId, $tenantId]);
    $batch = $stmt->fetch();
    if ($batch === false) {
        return null;
    }

    $stmt = $pdo->prepare(
        'SELECT r.id, r.row_num, r.participant_id, r.raw_name, r.amount_cents, r.booked_at, r.status, p.display_name
         FROM payment_import_rows r
         LEFT JOIN participants p ON p.id = r.participant_id
         WHERE r.batch_id = ?
         ORDER BY r.row_num'
    );
    $stmt->execute([$batchId]);

    return [
        'batch' => [
            'id' => (int)$batch['id'],
            'original_filename' => (string)$batch['original_filename'],
            'status' => (string)$batch['status'],
            'created_at' => (string)$batch['created_at'],
        ],
        'rows' => $stmt->fetchAll(),
    ];
}

/**
 * Commits every 'matched' row of a previewed batch straight into the ledger.
 * Already-committed batches are rejected.
 *
 * @return array{imported: int}
 */
function imports_commit_batch(PDO $pdo, int $tenantId, int $batchId): array
{
    $stmt = $pdo->prepare('SELECT status FROM payment_import_batches WHERE id = ? AND tenant_id = ?');
    $stmt->execute([$batchId, $tenantId]);
    $status = $stmt->fetchColumn();
    if ($status === false) {
        throw new RuntimeException('Der Import wurde nicht gefunden.');
    }
    if ($status !== 'previewed') {
        throw new RuntimeException('Dieser Import wurde bereits verarbeitet.');
    }

    $stmt = $pdo->prepare(
        "SELECT r.id, r.participant_id, r.amount_cents, r.booked_at
         FROM payment_import_rows r
         JOIN participants p ON p.id = r.participant_id
         WHERE r.batch_id = ? AND r.status = 'matched'"
    );
    $stmt->execute([$batchId]);
    $rows = $stmt->fetchAll();

    $imported = 0;

    $pdo->beginTransaction();
    try {
        $updateRow = $pdo->prepare(
            "UPDATE payment_import_rows SET status = 'imported', ledger_entry_id = ? WHERE id = ?"
        );

        foreach ($rows as $row) {
            $ledgerEntryId = ledger_record_payment(
                $pdo,
                $tenantId,
                (int)$row['participant_id'],
                (int)$row['amount_cents'],
                'csv_import'
            );

            $updateRow->execute([$ledgerEntryId, $row['id']]);
            $imported++;
        }

        $pdo->prepare("UPDATE payment_import_batches SET status = 'committed', committed_at = NOW() WHERE id = ?")
            ->execute([$batchId]);

        $pdo->commit();
    } catch (Throwable $e) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }

        throw $e;
    }

    return ['imported' => $imported];
}

/**
 * Parst eine Mitglieder-CSV (Vorlage: Name, E-Mail, PayPal-Name, Aktiv). Erste
 * Zeile ist die Kopfzeile und wird uebersprungen. Trennzeichen wird anhand der
 * Kopfzeile automatisch erkannt (Komma oder Semikolon - Excel im deutschen
 * Gebietsschema exportiert Semikolon).
 *
 * @return list<array{row_number:int, name:string, email:string, paypal_name:string, active:bool}>
 */
function imports_parse_member_csv(string $filePath): array
{
    $handle = fopen($filePath, 'r');
    if ($handle === false) {
        throw new RuntimeException('Die CSV-Datei konnte nicht gelesen werden.');
    }

    $firstLine = fgets($handle);
    if ($firstLine === false) {
        fclose($handle);
        return [];
    }
    // BOM entfernen, Trennzeichen erkennen.
    $firstLine = preg_replace('/^\xEF\xBB\xBF/', '', $firstLine);
    $delimiter = (substr_count($firstLine, ';') > substr_count($firstLine, ',')) ? ';' : ',';

    $rows = [];
    $rowNumber = 0;
    while (($data = fgetcsv($handle, 2000, $delimiter)) !== false) {
        // Komplett leere Zeilen ueberspringen.
        if ($data === [null] || (count($data) === 1 && trim((string)$data[0]) === '')) {
            continue;
        }
        $rowNumber++;
        $aktivRaw = strtolower(trim((string)($data[3] ?? 'ja')));
        $active = !in_array($aktivRaw, ['nein', 'no', '0', 'false', 'inaktiv'], true);
        $rows[] = [
            'row_number' => $rowNumber,
            'name' => trim((string)($data[0] ?? '')),
            'email' => trim((string)($data[1] ?? '')),
            'paypal_name' => trim((string)($data[2] ?? '')),
            'active' => $active,
        ];
    }

    fclose($handle);

    return $rows;
}