<?php
// ========================================================================
// Afrihair Background Validation & Sync Loop
// Run this via cron every 5-10 minutes, or triggered silently by api.php
// ========================================================================

// --- DATABASE CONFIGURATION ---
$db_host = "localhost";
$db_user = "afrihair_wh";
$db_pass = "wXZ@YMO[ue5efq~b";
$db_name = "order_locks";

$db = new mysqli($db_host, $db_user, $db_pass, $db_name);
if ($db->connect_error) {
    die("Database connection failed: " . $db->connect_error);
}

// Ensure the cache tables exist
$db->query("CREATE TABLE IF NOT EXISTS packie_cache (
    shopify_order_number VARCHAR(50) PRIMARY KEY,
    packie_id INT NOT NULL,
    packie_status VARCHAR(20) DEFAULT 'PENDING',
    last_checked TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)");

$db->query("CREATE TABLE IF NOT EXISTS packie_stocks (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    serviceType INT,
    height DECIMAL(10,2),
    width DECIMAL(10,2),
    length DECIMAL(10,2),
    weight DECIMAL(10,2),
    cubicWeight DECIMAL(10,2)
)");

// --- AUTHENTICATION ---
$token_file = __DIR__ . '/packie_token.txt';
if (!file_exists($token_file)) die("ERROR: Token file missing.\n");
$packie_token = trim(file_get_contents($token_file));

$headers = [
    'Content-Type: application/json',
    'Accept: application/json',
    'Authorization: Bearer ' . $packie_token 
];

// ==========================================
// STEP 1: FORCE SHOPIFY IMPORT
// ==========================================
echo "1. Forcing Shopify Sync...\n";
$ch1 = curl_init("https://www.packie.co.nz/api/orders/import-live-shopify");
curl_setopt($ch1, CURLOPT_POST, true);
curl_setopt($ch1, CURLOPT_POSTFIELDS, "{}"); 
curl_setopt($ch1, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch1, CURLOPT_HTTPHEADER, $headers);
curl_exec($ch1);
curl_close($ch1);

sleep(3); // Give Packie 3 seconds to process the import

// ==========================================
// STEP 2: GRAB THE MASTER ORDER LIST
// ==========================================
echo "2. Fetching Orders from Packie...\n";
$ch2 = curl_init("https://www.packie.co.nz/api/Orders/get-orders?status=1&search=&integrationId=0&from=&to=&page=1&perPage=100");
curl_setopt($ch2, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch2, CURLOPT_HTTPHEADER, $headers);
$response = curl_exec($ch2);
curl_close($ch2);

$data = json_decode($response, true);
if (!$data['success'] || empty($data['result']['orders'])) {
    die("No new orders found or failed to fetch.\n");
}

$orders = $data['result']['orders'];

// ==========================================
// STEP 3: PROCESS & FLAG ORDERS
// ==========================================
echo "3. Processing " . count($orders) . " orders...\n";

// Prepare our MySQLi queries outside the loop for speed
$check_stmt = $db->prepare("SELECT packie_status FROM packie_cache WHERE shopify_order_number = ?");
$insert_stmt = $db->prepare("
    INSERT INTO packie_cache (shopify_order_number, packie_id, packie_status) 
    VALUES (?, ?, ?) 
    ON DUPLICATE KEY UPDATE packie_id = VALUES(packie_id), packie_status = VALUES(packie_status)
");

foreach ($orders as $order) {
    $shopify_order_number = $order['orderNumber']; // e.g. "16998"
    $packie_id = $order['id']; // e.g. 2099700

    // 1. Check if we already validated this order
    $check_stmt->bind_param("s", $shopify_order_number);
    $check_stmt->execute();
    $res = $check_stmt->get_result();
    $existing = $res->fetch_assoc();

    // Skip it if already fully checked or printed!
    if ($existing && in_array($existing['packie_status'], ['READY', 'RURAL', 'INVALID', 'PRINTED'])) {
        continue; 
    }

    $status = 'READY';

    // 2. Check Packie's native validity flag first
    if ($order['addressValidity'] === 0) {
        $status = 'INVALID';
        echo " -> Order #{$shopify_order_number} flagged as INVALID (Packie caught it).\n";
    } 
    else {
        // 3. Address is good. Do a quick quote to check for Rural fees.
        $calc_payload = [
            "consignmentParcels" => [[
                "id" => "4", 
                "height" => "29.7",
                "width" => "1",
                "length" => "21",
                "itemSKU" => "",
                "weight" => "5",
                "isPallet" => false,
                "dangerousGoodsRate" => 0,
                "internationalDeclaredValue" => "0.00",
                "markupRate" => 0,
                "quantity" => "1",
                "serviceType" => 4
            ]],
            "hasDangerousGoods" => false,
            "integratedOrderId" => $packie_id,
            "integrationType" => 1,
            "isInternational" => false,
            "receiver" => $order['receiver'],
            "sender" => $order['sender'],
            "shippingDescription" => $order['shippingDescription'] ?? "Fastest Delivery",
            "shippingMethod" => $order['shippingMethod'] ?? "Fastest Delivery",
            "signatureRequired" => false
        ];

        $ch3 = curl_init("https://www.packie.co.nz/api/Consignments/calculate");
        curl_setopt($ch3, CURLOPT_POST, true);
        curl_setopt($ch3, CURLOPT_POSTFIELDS, json_encode($calc_payload));
        curl_setopt($ch3, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($ch3, CURLOPT_HTTPHEADER, $headers);
        $calc_response = curl_exec($ch3);
        curl_close($ch3);
        
        $calc_data = json_decode($calc_response, true);

        if (isset($calc_data['success']) && $calc_data['success'] && !empty($calc_data['result']['rates'])) {
            $first_rate = $calc_data['result']['rates'][0];
            if (isset($first_rate['rural_rate']) && floatval($first_rate['rural_rate']) > 0) {
                $status = 'RURAL';
                echo " -> Order #{$shopify_order_number} flagged as RURAL.\n";
            } else {
                echo " -> Order #{$shopify_order_number} is READY.\n";
            }
        } else {
             $status = 'INVALID';
             echo " -> Order #{$shopify_order_number} failed to get a quote.\n";
        }
    }

    // 4. Save the final result to the database
    // "sis" means: String, Integer, String
    $insert_stmt->bind_param("sis", $shopify_order_number, $packie_id, $status);
    $insert_stmt->execute();
    
    usleep(250000); // 0.25 second pause so we don't spam Packie
}

// ==========================================
// STEP 4: UPDATE PACKAGING STOCKS IN DB
// ==========================================
echo "4. Fetching latest packaging stocks...\n";
$ch4 = curl_init("https://www.packie.co.nz/api/stocks");
curl_setopt($ch4, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch4, CURLOPT_HTTPHEADER, $headers);
$stocks_res = curl_exec($ch4);
curl_close($ch4);

$stocks_data = json_decode($stocks_res, true);
if (isset($stocks_data['success']) && $stocks_data['success'] && !empty($stocks_data['result'])) {
    $stock_stmt = $db->prepare("
        INSERT INTO packie_stocks (id, name, serviceType, height, width, length, weight, cubicWeight) 
        VALUES (?, ?, ?, ?, ?, ?, ?, ?) 
        ON DUPLICATE KEY UPDATE 
        name=VALUES(name), serviceType=VALUES(serviceType), height=VALUES(height), 
        width=VALUES(width), length=VALUES(length), weight=VALUES(weight), cubicWeight=VALUES(cubicWeight)
    ");
    
    foreach ($stocks_data['result'] as $stock) {
        $stock_stmt->bind_param("isiddddd", 
            $stock['id'], $stock['name'], $stock['serviceType'], 
            $stock['height'], $stock['width'], $stock['length'], 
            $stock['weight'], $stock['cubicWeight']
        );
        $stock_stmt->execute();
    }
    echo "Stocks updated in database.\n";
}

echo "Sync Complete.\n";
?>