<?php
// === CONFIGURATION ===
require_once 'vendor/autoload.php';
include('includes/dbconnection.php');

$secretKey = 'sk_live_bf659cd54e9a3b77bf51aea12dfea13f1449829c';
$baseUrl = 'https://api.paystack.co';
$logSuccess = 'reconcile_success.log';
$logError = 'reconcile_error.log';

// === Helper: Safe metadata extraction ===
function getCustom($fields, $name, $default = '') {
    foreach ($fields as $field) {
        if ($field['display_name'] === $name) {
            return $field['value'];
        }
    }
    return $default;
}

// === Fetch latest 100 successful transactions ===
$url = $baseUrl . '/transaction?status=success&perPage=100';

$ch = curl_init($url);
curl_setopt($ch, CURLOPT_HTTPHEADER, [
    "Authorization: Bearer $secretKey",
    "Content-Type: application/json"
]);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
$response = curl_exec($ch);
curl_close($ch);

$data = json_decode($response, true);

// === Validate API response ===
if (!isset($data['status']) || !$data['status']) {
    file_put_contents($logError, "[" . date('Y-m-d H:i:s') . "] API Error: $response\n", FILE_APPEND);
    exit("API call failed\n");
}

$transactions = $data['data'];
foreach ($transactions as $tx) {
    $reference = $tx['reference'];
    $amount = $tx['amount'] / 100;
    $email = $tx['customer']['email'] ?? 'no-email@unknown.com';
    $paystackStatus = $tx['status'];
    $status = ($paystackStatus === 'success') ? 'approved' : 'pending';
    $channel = $tx['channel'] ?? 'unknown';
    $paid_at = date('Y-m-d H:i:s', strtotime($tx['paid_at'] ?? 'now'));

    $metadata = $tx['metadata'] ?? [];
    $custom = $metadata['custom_fields'] ?? [];

    // Extract metadata fields
    $student_id      = getCustom($custom, 'Student ID', 'unknown');
    $studentno       = getCustom($custom, 'Student No', 'N/A');
    $studentName     = getCustom($custom, 'Student Name', 'Unnamed Student');
    $feeType         = getCustom($custom, 'Fees Type', 'General Payment');
    $discountUsed    = getCustom($custom, 'Used Discount', 'No');
    $discountAmount  = getCustom($custom, 'Discount Amount', 0.00);
    $discountPercent = getCustom($custom, 'Discount Percent', 0.00);
    $term            = getCustom($custom, 'Term ID', 'unknown');
    $session         = getCustom($custom, 'Session ID', 'unknown');

    // === Try fetching student details from students table using email ===
    $stmtStudent = $con->prepare("SELECT id, studentno, studentName FROM students WHERE email = ?");
    $stmtStudent->bind_param("s", $email);
    $stmtStudent->execute();
    $result = $stmtStudent->get_result();

    if ($row = $result->fetch_assoc()) {
        $student_id = $row['id'];
        $studentno = $row['studentno'];
        $studentName = $row['studentName'];
    }
    $stmtStudent->close();

    // === Check if payment already exists ===
    $stmtCheck = $con->prepare("SELECT id FROM payments WHERE reference = ?");
    $stmtCheck->bind_param("s", $reference);
    $stmtCheck->execute();
    $stmtCheck->store_result();

    if ($stmtCheck->num_rows > 0) {
        $stmtCheck->close();
        continue; // already saved
    }
    $stmtCheck->close();

    // === Insert missing payment ===
    $stmtInsert = $con->prepare("
        INSERT INTO payments (
            student_id, studentno, studentName, email, reference, amount, status, fee_type, 
            payment_date, discount_used, discount_amount, discount_percent, 
            payment_method, term_id, session_id
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    ");

    $payment_method = "Paystack";
    $stmtInsert->bind_param(
        "sssssdsssssdsss",
        $student_id,
        $studentno,
        $studentName,
        $email,
        $reference,
        $amount,
        $status,
        $feeType,
        $paid_at,
        $discountUsed,
        $discountAmount,
        $discountPercent,
        $payment_method,
        $term,
        $session
    );

    if ($stmtInsert->execute()) {
        file_put_contents($logSuccess, "[" . date('Y-m-d H:i:s') . "] Inserted: $reference\n", FILE_APPEND);
    } else {
        file_put_contents($logError, "[" . date('Y-m-d H:i:s') . "] DB Insert Error for $reference: " . $stmtInsert->error . "\n", FILE_APPEND);
    }

    $stmtInsert->close();
}

echo "Reconciliation complete.\n";
?>