<?php
// cron.php - fetch daily_report per branch for target date (yesterday), save closed_balance (amount + other_amount) and send SMS

header("Access-Control-Allow-Origin: *");
date_default_timezone_set('Africa/Dar_es_Salaam');
include './config.php'; // $conn is mysqli connection

// Beem Africa API credentials (keep secure in production)
$api_key     = "106de5fd0d63b2de";
$secret_key  = "NDY1NGE3NjZjYTgzZmE5MTFkOThkODg1MDZjMmE1NTVmOTc2OGJlMzc1MDRmYjNlMDkxODE1MWFiM2JkY2FlNg==";
$sender_name = "Bexa Mikopo";
$beeam_url   = "https://apisms.beem.africa/v1/send";

$recipients = [
   '255688231247',
   '255763398254',
];

$targetDate = date('Y-m-d', strtotime('-1 day'));
$dailyReportUrlBase = "https://api.bexamicrofinance.com/api_v1/admin/daily_report.php";

// 1) Fetch branches
$branchSql = "SELECT branch_id, branch_name FROM branches";
$branchRes = $conn->query($branchSql);
if (!$branchRes) {
    error_log("Failed to fetch branches: " . $conn->error);
    exit;
}

while ($branch = $branchRes->fetch_assoc()) {
    $bId   = intval($branch['branch_id']);
    $bName = $branch['branch_name'];

    // 2) GET daily_report for this branch (it returns many dates; we'll pick targetDate)
    $url = sprintf("%s?branch_id=%d", $dailyReportUrlBase, $bId);
    $ch = curl_init($url);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_TIMEOUT, 30);
    $response = curl_exec($ch);
    $httpcode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
    if ($httpcode < 200 || $httpcode >= 300 || $response === false) {
        error_log("Failed to GET daily_report for branch $bId: HTTP $httpcode");
        curl_close($ch);
        continue;
    }
    curl_close($ch);

    $allData = json_decode($response, true);
    if (!is_array($allData)) {
        error_log("daily_report returned invalid JSON for branch $bId");
        continue;
    }

    // 3) Find row for targetDate
    $found = false;
    $closedLoans = 0.0;
    $closedOther = 0.0;

    foreach ($allData as $row) {
        if (isset($row['branch_id']) && intval($row['branch_id']) == $bId && isset($row['date']) && $row['date'] == $targetDate) {
            // Expecting fields closed_loans and closed_other from updated daily_report.php
            $closedLoans = isset($row['closed_loans']) ? floatval($row['closed_loans']) : 0.0;
            $closedOther = isset($row['closed_other']) ? floatval($row['closed_other']) : 0.0;
            $found = true;
            break;
        }
    }

    // 4) Fallback: if not found, get latest closed_balance before targetDate
    if (!$found) {
        $prevStmt = $conn->prepare("
            SELECT amount, other_amount
              FROM closed_balance
             WHERE branch_id = ?
               AND DATE(date) < ?
             ORDER BY DATE(date) DESC
             LIMIT 1
        ");
        if ($prevStmt) {
            $prevStmt->bind_param("is", $bId, $targetDate);
            $prevStmt->execute();
            $prevRes = $prevStmt->get_result();
            if ($prevRes && $rowPrev = $prevRes->fetch_assoc()) {
                $closedLoans = floatval($rowPrev['amount']);
                $closedOther = floatval($rowPrev['other_amount']);
            } else {
                $closedLoans = 0.0;
                $closedOther = 0.0;
            }
            $prevStmt->close();
        } else {
            error_log("Failed to prepare prev closed_balance stmt: " . $conn->error);
            $closedLoans = 0.0;
            $closedOther = 0.0;
        }
    }

    // 5) Upsert into closed_balance (avoid duplicates)
    // Check if record for this branch and date exists
    $checkStmt = $conn->prepare("SELECT id FROM closed_balance WHERE branch_id = ? AND DATE(date) = ?");
    if (!$checkStmt) {
        error_log("Failed to prepare check stmt: " . $conn->error);
        continue;
    }
    $checkStmt->bind_param("is", $bId, $targetDate);
    $checkStmt->execute();
    $checkRes = $checkStmt->get_result();
    $existingId = null;
    if ($checkRes && $er = $checkRes->fetch_assoc()) {
        $existingId = intval($er['id']);
    }
    $checkStmt->close();

    if ($existingId !== null) {
        // UPDATE existing record
        $updateStmt = $conn->prepare("UPDATE closed_balance SET amount = ?, other_amount = ?, date = ? WHERE id = ?");
        if ($updateStmt) {
            $updateStmt->bind_param("ddsi", $closedLoans, $closedOther, $targetDate, $existingId);
            if (!$updateStmt->execute()) {
                error_log("Failed to update closed_balance id {$existingId}: " . $updateStmt->error);
            }
            $updateStmt->close();
        } else {
            error_log("Failed to prepare update stmt: " . $conn->error);
        }
    } else {
        // INSERT new record
        $insertSql = "INSERT INTO closed_balance (amount, other_amount, branch_id, date) VALUES (?, ?, ?, ?)";
        $stmt = $conn->prepare($insertSql);
        if ($stmt) {
            $stmt->bind_param("ddis", $closedLoans, $closedOther, $bId, $targetDate);
            if (!$stmt->execute()) {
                error_log("Failed to insert closed_balance for branch $bId on $targetDate: " . $stmt->error);
            }
            $stmt->close();
        } else {
            error_log("Failed to prepare insert stmt: " . $conn->error);
        }
    }

    // 6) Send SMS (include both balances)
    $formattedLoans = number_format($closedLoans, 0, '.', ',');
    $formattedOther = number_format($closedOther, 0, '.', ',');
    $message = sprintf(
        "%s branch closed balances za %s -> Loans: %s Tsh, Other: %s Tsh",
        $bName,
        $targetDate,
        $formattedLoans,
        $formattedOther
    );

    foreach ($recipients as $phone) {
        $formatted_phone = preg_replace('/[^0-9]/', '', $phone);
        $data = [
            'source_addr' => $sender_name,
            'encoding'    => 0,
            'message'     => $message,
            'recipients'  => [
                ['recipient_id' => '1', 'dest_addr' => $formatted_phone]
            ],
        ];
        $payload = json_encode($data);

        $ch = curl_init($beeam_url);
        curl_setopt($ch, CURLOPT_HTTPHEADER, [
            'Content-Type: application/json',
            'Authorization: Basic ' . base64_encode("$api_key:$secret_key"),
        ]);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($ch, CURLOPT_POST, true);
        curl_setopt($ch, CURLOPT_POSTFIELDS, $payload);
        curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 0);
        curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0);
        curl_setopt($ch, CURLOPT_TIMEOUT, 30);

        $smsResponse = curl_exec($ch);
        $smsHttpCode  = curl_getinfo($ch, CURLINFO_HTTP_CODE);
        if ($smsHttpCode < 200 || $smsHttpCode >= 300) {
            error_log("SMS send failed for branch $bId to $formatted_phone: HTTP $smsHttpCode, response: $smsResponse");
        }
        curl_close($ch);
    }
}

// Close DB
$conn->close();
