<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

$host = "localhost";  
$username = "ndcordersmax_user";
$password = "GQa[Qk3A=qj?ADO]";
$database = "ndcordersmax_db";

$conn = new mysqli($host, $username, $password, $database);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

require __DIR__ . '/stripe-php/init.php';
\Stripe\Stripe::setApiKey('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'); 

try {
    $has_more = true;
    $starting_after = null;
    $total_synced = 0;

    while ($has_more) {
        $charges = \Stripe\Charge::all([
            'limit' => 100,
            'starting_after' => $starting_after
        ]);

        foreach ($charges->data as $charge) {
            $transaction_id = $conn->real_escape_string($charge->id);
            $amount         = $charge->amount / 100;
            $receipt_number = $conn->real_escape_string($charge->receipt_number ?? '');
            $created        = date("Y-m-d H:i:s", $charge->created);
            $datetime_added = date("Y-m-d H:i:s");

            // Billing details
            $billing = $charge->billing_details;
            $billing_name    = $conn->real_escape_string($billing->name ?? '');
            $billing_email   = $conn->real_escape_string($billing->email ?? '');
            $billing_phone   = $conn->real_escape_string($billing->phone ?? '');
            $billing_address = $billing->address ?? null;
            $billing_line1   = $conn->real_escape_string($billing_address->line1 ?? '');
            $billing_line2   = $conn->real_escape_string($billing_address->line2 ?? '');
            $billing_city    = $conn->real_escape_string($billing_address->city ?? '');
            $billing_state   = $conn->real_escape_string($billing_address->state ?? '');
            $billing_zip     = $conn->real_escape_string($billing_address->postal_code ?? '');
            $billing_country = $conn->real_escape_string($billing_address->country ?? '');

            // Shipping details
            $shipping = $charge->shipping;
            $shipping_name    = $conn->real_escape_string($shipping->name ?? '');
            $shipping_phone   = $conn->real_escape_string($shipping->phone ?? '');
            $shipping_address = $shipping->address ?? null;
            $shipping_line1   = $conn->real_escape_string($shipping_address->line1 ?? '');
            $shipping_line2   = $conn->real_escape_string($shipping_address->line2 ?? '');
            $shipping_city    = $conn->real_escape_string($shipping_address->city ?? '');
            $shipping_state   = $conn->real_escape_string($shipping_address->state ?? '');
            $shipping_zip     = $conn->real_escape_string($shipping_address->postal_code ?? '');
            $shipping_country = $conn->real_escape_string($shipping_address->country ?? '');

            if (empty($billing_email)) {
                $billing_email = $charge->receipt_email;
            }

            $status = $charge->status;

            // Check if already exists
            $check_sql = "SELECT COUNT(*) AS cnt FROM stripe_charges WHERE transaction_id = '$transaction_id'";
            $check_res = $conn->query($check_sql);
            $row = $check_res->fetch_assoc();

            if ($row['cnt'] == 0) {
                $sql = "INSERT INTO stripe_charges 
                    (transaction_id, amount, receipt_number, created_at, 
                     billing_name, billing_email, billing_phone, billing_line1, billing_line2, billing_city, billing_state, billing_zip, billing_country,
                     shipping_name, shipping_phone, shipping_line1, shipping_line2, shipping_city, shipping_state, shipping_zip, shipping_country, status, datetime_added)
                    VALUES (
                        '$transaction_id',
                        '$amount',
                        '$receipt_number',
                        '$created',
                        '$billing_name',
                        '$billing_email',
                        '$billing_phone',
                        '$billing_line1',
                        '$billing_line2',
                        '$billing_city',
                        '$billing_state',
                        '$billing_zip',
                        '$billing_country',
                        '$shipping_name',
                        '$shipping_phone',
                        '$shipping_line1',
                        '$shipping_line2',
                        '$shipping_city',
                        '$shipping_state',
                        '$shipping_zip',
                        '$shipping_country',
                        '$status',
                        '$datetime_added'
                    )";

                if ($conn->query($sql) === TRUE) {
                    $total_synced++;
                } else {
                    echo "Error inserting $transaction_id: " . $conn->error . "<br>";
                }
            } else {
                echo "Skipped (already exists): $transaction_id<br>";
            }
        }
        if (!empty($charges->data)) {
            $last_charge = end($charges->data);
            $starting_after = $last_charge->id;
        }

        $has_more = $charges->has_more;
        echo "Fetched batch of " . count($charges->data) . " charges...<br>";
    }

    echo "<br>Sync completed. Total new records inserted: $total_synced";
} catch (Exception $e) {
    echo "Error: " . $e->getMessage();
}
