<?php
// fix_small_remaining.php
// Inatumia $conn (mysqli) kama ulivyoeleza — usibadilishe bila sababu
include '../../config.php';

// SELECT ya kutafuta schedules (ile uliyokubali)
$sql_select = "
SELECT
  last.loan_id,
  last.customer_id,
  last.id AS schedule_id,
  last.schedule_date,
  last.status AS last_status,
  last.scheduled_amount,
  last.paid_amount,
  (last.scheduled_amount - last.paid_amount) AS remaining
FROM loans_schedule last
JOIN (
  SELECT loan_id, MAX(schedule_date) AS max_date
  FROM loans_schedule
  GROUP BY loan_id
) lm ON last.loan_id = lm.loan_id AND last.schedule_date = lm.max_date
JOIN (
  SELECT loan_id
  FROM loans_schedule
  GROUP BY loan_id
  HAVING SUM(CASE WHEN LOWER(status) <> 'full paid' THEN 1 ELSE 0 END) = 1
) t ON t.loan_id = last.loan_id
WHERE LOWER(last.status) LIKE '%part%'
  AND (last.scheduled_amount - last.paid_amount) > 0
  AND (last.scheduled_amount - last.paid_amount) < 5.0
";

mysqli_begin_transaction($conn);

try {
    $res = mysqli_query($conn, $sql_select);
    if (!$res) {
        throw new Exception("Select error: " . mysqli_error($conn));
    }

    if (mysqli_num_rows($res) === 0) {
        // Hakuna rekodi za kuboresha
        mysqli_commit($conn);
        echo "Hakuna schedules zinazokidhi vigezo.\n";
        exit;
    }

    // Prepare statements
    $stmt_update_schedule = mysqli_prepare($conn, "UPDATE loans_schedule SET paid_amount = scheduled_amount, status = 'Full Paid' WHERE id = ?");
    if (!$stmt_update_schedule) throw new Exception("Prepare update schedule failed: " . mysqli_error($conn));

    $stmt_check_remaining = mysqli_prepare($conn, "SELECT COUNT(*) AS cnt FROM loans_schedule WHERE loan_id = ? AND (LOWER(status) <> 'full paid' OR (scheduled_amount - paid_amount) > 0.001)");
    if (!$stmt_check_remaining) throw new Exception("Prepare check remaining failed: " . mysqli_error($conn));

    $stmt_update_loan = mysqli_prepare($conn, "UPDATE loans SET status = 'Paid' WHERE loan_id = ?");
    if (!$stmt_update_loan) throw new Exception("Prepare update loan failed: " . mysqli_error($conn));

    $updatedSchedules = 0;
    $updatedLoans = 0;
    $processedLoanIds = [];

    while ($row = mysqli_fetch_assoc($res)) {
        $schedule_id = (int)$row['schedule_id'];
        $loan_id = $row['loan_id'];

        // Update schedule: paid_amount = scheduled_amount, status = 'Full Paid'
        mysqli_stmt_bind_param($stmt_update_schedule, "i", $schedule_id);
        if (!mysqli_stmt_execute($stmt_update_schedule)) {
            throw new Exception("Failed to update schedule id {$schedule_id}: " . mysqli_stmt_error($stmt_update_schedule));
        }
        $updatedSchedules++;

        // Avoid checking same loan multiple times unnecessarily
        if (in_array($loan_id, $processedLoanIds, true)) {
            continue;
        }
        $processedLoanIds[] = $loan_id;

        // Check if any remaining unpaid/partial schedules for this loan
        mysqli_stmt_bind_param($stmt_check_remaining, "i", $loan_id);
        if (!mysqli_stmt_execute($stmt_check_remaining)) {
            throw new Exception("Failed to check remaining for loan {$loan_id}: " . mysqli_stmt_error($stmt_check_remaining));
        }
        $res_check = mysqli_stmt_get_result($stmt_check_remaining);
        $r = mysqli_fetch_assoc($res_check);
        $cnt = (int)$r['cnt'];

        if ($cnt === 0) {
            // All schedules full paid -> mark loan as Paid
            mysqli_stmt_bind_param($stmt_update_loan, "i", $loan_id);
            if (!mysqli_stmt_execute($stmt_update_loan)) {
                throw new Exception("Failed to update loan {$loan_id}: " . mysqli_stmt_error($stmt_update_loan));
            }
            $updatedLoans++;
        }
    }

    mysqli_commit($conn);

    echo "Imefanikiwa. Schedules zilizosasishwa: {$updatedSchedules}. Mikopo (loans) zilizowekwa Paid: {$updatedLoans}.\n";

} catch (Exception $e) {
    mysqli_rollback($conn);
    echo "ERROR: " . $e->getMessage() . "\n";
    // optionally log to file
    // error_log($e->getMessage());
}
