<?php

header('Content-Type: application/json');

// Function to format phone numbers
function formatPhoneNumber($phone) {
    // Remove all non-digit characters
    $phone = preg_replace('/\D/', '', $phone);

    // If phone starts with '0', remove it and add '255'
    if (str_starts_with($phone, '0') && strlen($phone) == 10) {
        return '255' . substr($phone, 1);
    }
    
    // If phone has 9 digits, assume it's a local number without the leading 0, so add '255'
    if (strlen($phone) == 9) {
        return '255' . $phone;
    }
    
    // If phone starts with '+255', remove the '+'
    if (str_starts_with($phone, '+255')) {
        return substr($phone, 1);
    }

    // Default return the phone number as is
    return $phone;
}

// Database credentials
$servername = "localhost";
$username = "alvycfgu_fredy";
$password = "@Lai19911011";
$dbname = "alvycfgu_OMEGA_CHURCH"; // Your DB name

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    // Send a proper JSON error response
    http_response_code(500);
    die(json_encode(['status' => 'error', 'message' => 'Connection failed: ' . $conn->connect_error]));
}

// Hardcoded default values
$partnership_type_id = 3;
$commitment_amount = 200000.00;
$commitment_currency = 'TZS';
$commitment_frequency = 'Monthly';
$start_date = date('Y-m-d');
$status = 'Active';
$description = 'Automatically generated agreement.';
$created_by_user_id = 1;

// Get input from POST request
$input_phone_numbers = $_POST['phone_numbers'] ?? '';
$phone_numbers_array = [];

// Sanitize and split input string
$phones_raw = preg_split('/[\n,;]+/', $input_phone_numbers, -1, PREG_SPLIT_NO_EMPTY);
if (!empty($phones_raw)) {
    $phone_numbers_array = array_filter(array_unique(array_map('trim', $phones_raw)));
}

if (empty($phone_numbers_array)) {
    http_response_code(400);
    die(json_encode(['status' => 'error', 'message' => 'No valid phone numbers provided.']));
}

$results = [];

// Use transactions for safety
$conn->begin_transaction();

try {
    foreach ($phone_numbers_array as $phone) {
        $partner_id = null;
        $formatted_phone = formatPhoneNumber($phone);

        // Step 1: Find partner_id from phone_number1
        $stmt_partner = $conn->prepare("SELECT partner_id FROM Partners WHERE phone_number1 = ?");
        $stmt_partner->bind_param("s", $formatted_phone);
        $stmt_partner->execute();
        $result_partner = $stmt_partner->get_result();

        if ($result_partner->num_rows > 0) {
            $row = $result_partner->fetch_assoc();
            $partner_id = $row['partner_id'];
        }
        $stmt_partner->close();

        if ($partner_id) {
            // Step 2: Check if an agreement already exists for this partner
            $stmt_agreement_check = $conn->prepare("SELECT agreement_id FROM Partnership_Agreements WHERE partner_id = ?");
            $stmt_agreement_check->bind_param("i", $partner_id);
            $stmt_agreement_check->execute();
            $result_agreement_check = $stmt_agreement_check->get_result();

            if ($result_agreement_check->num_rows > 0) {
                // Update existing agreement
                $stmt_update = $conn->prepare("UPDATE Partnership_Agreements SET commitment_amount = ?, partnership_type_id = ?, updated_at = CURRENT_TIMESTAMP(), updated_by_user_id = ? WHERE partner_id = ?");
                $stmt_update->bind_param("diii", $commitment_amount, $partnership_type_id, $created_by_user_id, $partner_id);
                if ($stmt_update->execute()) {
                    $results[] = ['phone' => $phone, 'status' => 'updated', 'message' => 'Agreement updated successfully.'];
                } else {
                    $results[] = ['phone' => $phone, 'status' => 'error', 'message' => 'Error updating agreement: ' . $stmt_update->error];
                }
                $stmt_update->close();
            } else {
                // Insert new agreement
                $agreement_code = 'AGR-' . bin2hex(random_bytes(4));
                $stmt_insert = $conn->prepare("INSERT INTO Partnership_Agreements (agreement_code, partner_id, partnership_type_id, commitment_amount, commitment_currency, commitment_frequency, start_date, status, description, created_by_user_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
                $stmt_insert->bind_param("siidsssssi", $agreement_code, $partner_id, $partnership_type_id, $commitment_amount, $commitment_currency, $commitment_frequency, $start_date, $status, $description, $created_by_user_id);
                if ($stmt_insert->execute()) {
                    $results[] = ['phone' => $phone, 'status' => 'inserted', 'message' => 'New agreement inserted successfully.'];
                } else {
                    $results[] = ['phone' => $phone, 'status' => 'error', 'message' => 'Error inserting agreement: ' . $stmt_insert->error];
                }
                $stmt_insert->close();
            }
            $stmt_agreement_check->close();
        } else {
            // Phone number not found
            $results[] = ['phone' => $phone, 'status' => 'skipped', 'message' => 'No partner found with this phone number.'];
        }
    }

    $conn->commit();
    echo json_encode(['status' => 'success', 'results' => $results]);

} catch (mysqli_sql_exception $exception) {
    $conn->rollback();
    http_response_code(500);
    echo json_encode(['status' => 'error', 'message' => 'Transaction failed: ' . $exception->getMessage()]);

} finally {
    $conn->close();
}