<?php
/**
 * ONE-TIME BACKFILL — re-syncs stale Shopify-owned order fields (financial_status,
 * cancelled_at, tags, fulfillment_status, totals, line_items, etc.) from Shopify's
 * live Admin API into shopify_orders, for orders created in the last 3 months.
 *
 * WHY: the orders/updated webhook was added AFTER many existing orders had already
 * changed status in Shopify (e.g. pending -> paid via COD reconciliation, or
 * cancelled -> voided). Those orders never got a webhook fire, so the DB is frozen
 * at whatever status existed at order creation. This script asks Shopify directly
 * for the CURRENT truth and updates only the same Shopify-owned columns the webhook
 * already updates on every hit — it NEVER touches telecaller_status, agent,
 * internal_remarks, risk_level, payment_terms_name, or next_payment_due_at.
 *
 * Safe to run multiple times (idempotent UPDATE, never INSERTs new rows).
 * Run once from CLI (php shopify_status_backfill.php) or via browser.
 */

// ── 1. FILL THESE IN — Dev Dashboard app credentials (Settings tab of your app) ──
// NOTE: Shopify deprecated static custom-app tokens on Jan 1, 2026. Apps created via
// Dev Dashboard now use Client ID + Client Secret, exchanged programmatically for a
// short-lived (24hr) access token via the Client Credentials Grant — see getAccessToken()
// below. This only works if the app and the store are in the SAME Shopify organization
// in the Dev Dashboard (Dev stores page) — if you get a "shop_not_permitted" error, that's
// almost always why.
$stores = [
    [
        'label'         => '.com',
        'domain'        => '89b3cb-1a.myshopify.com',   // <-- fill in
        'client_id'     => '6050afc3025d023364413dc6bc918f0a',              // <-- fill in, from app's Settings tab
        'client_secret' => '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',          // <-- fill in, from app's Settings tab
    ],
    [
        'label'         => '.in',
        'domain'        => 'rghudk-fk.myshopify.com', // <-- fill in
        'client_id'     => '6b2406c224ed404f449b1f5f82be67ba',              // <-- fill in
        'client_secret' => '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',          // <-- fill in
    ],
];

$apiVersion = '2024-10';
$sinceDate  = date('Y-m-d', strtotime('-3 months'));

/**
 * Exchanges Client ID + Client Secret for a temporary Admin API access token.
 * Token is valid ~24 hours (86399s) — more than enough for this one-time run.
 */
function getAccessToken($domain, $clientId, $clientSecret) {
    $ch = curl_init("https://{$domain}/admin/oauth/access_token");
    curl_setopt_array($ch, [
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_POST           => true,
        CURLOPT_POSTFIELDS     => http_build_query([
            'grant_type'    => 'client_credentials',
            'client_id'     => $clientId,
            'client_secret' => $clientSecret,
        ]),
        CURLOPT_HTTPHEADER => ['Content-Type: application/x-www-form-urlencoded'],
        CURLOPT_TIMEOUT    => 20,
    ]);
    $resp = curl_exec($ch);
    $code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
    curl_close($ch);
    if ($resp === false || $code !== 200) {
        throw new Exception("Token exchange failed (HTTP $code): " . substr((string)$resp, 0, 300));
    }
    $data = json_decode($resp, true);
    if (empty($data['access_token'])) {
        throw new Exception("Token exchange response missing access_token: " . substr($resp, 0, 300));
    }
    return $data['access_token'];
}

// ── 2. DB connection (same as shopify-webhook.php) ──
$host = 'database.xhost.co.in';
$db   = 'db_iqpj6ze2cf';
$user = 'db_iqpj6ze2cf';
$pass = 'O*9Mp;%5PPDyv8oD';
$pdo  = new PDO("pgsql:host=$host;port=5432;dbname=$db", $user, $pass, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

// Same protected UPDATE list as shopify-webhook.php — Shopify-owned fields only.
$updateSql = "UPDATE shopify_orders SET
    tags               = :tags,
    financial_status   = :financial_status,
    fulfillment_status = :fulfillment_status,
    paid_at            = :paid_at,
    cancelled_at       = :cancelled_at,
    total              = :total,
    subtotal           = :subtotal,
    discount_code      = :discount_code,
    discount_amount    = :discount_amount,
    line_items         = :line_items,
    note_attributes    = :note_attributes,
    notes              = :notes,
    payment_method     = :payment_method
WHERE shopify_order_id = :shopify_order_id";
$updateStmt = $pdo->prepare($updateSql);

// Shared hosting often kills PHP after 30-300s and buffers echo output until the
// script ends — both make a long-running script look "stuck" in the browser even
// though it's working. This removes the time cap (where the host allows it) and
// forces output to flush immediately after every log line.
set_time_limit(0);
ignore_user_abort(true); // keep running even if the browser tab is closed/times out

$log = fopen('shopify_backfill_log.txt', 'a');
function logLine($fh, $msg) {
    $line = date('[Y-m-d H:i:s] ') . $msg . PHP_EOL;
    fwrite($fh, $line);
    fflush($fh); // write to disk immediately, don't wait for script end
    echo $line;
    if (ob_get_level() > 0) @ob_flush();
    @flush();
}

logLine($log, "=== Backfill started, since=$sinceDate ===");

foreach ($stores as $store) {
    // If this store already finished successfully in a prior run, skip it entirely —
    // avoids re-processing 36,000+ .com orders again just to retry a failed .in.
    $doneFile = "shopify_backfill_done_" . preg_replace('/[^a-z0-9]/i', '', $store['label']) . ".flag";
    if (file_exists($doneFile)) {
        logLine($log, "  {$store['label']}: already completed in a previous run — skipping (delete $doneFile to force a re-run)");
        continue;
    }

    // Resume support: if a previous run got cut off mid-store (host timeout), a cursor
    // file holds the next page URL + running totals so this run continues instead of
    // re-fetching everything from page 1.
    $cursorFile = "shopify_backfill_cursor_{$store['label']}.json";
    $cursorFile = str_replace(['.', '/'], '', $cursorFile); // sanitize label for filename
    $updated = 0; $skipped = 0; $errors = 0;
    $url = null;
    $pageNum = 0;

    if (file_exists($cursorFile)) {
        $resume = json_decode(file_get_contents($cursorFile), true);
        if ($resume && !empty($resume['url'])) {
            $url     = $resume['url'];
            $updated = $resume['updated'] ?? 0;
            $skipped = $resume['skipped'] ?? 0;
            $errors  = $resume['errors']  ?? 0;
            $pageNum = $resume['page']    ?? 0;
            logLine($log, "  {$store['label']}: resuming from page " . ($pageNum + 1) . " (previous run: updated=$updated skipped=$skipped errors=$errors)");
        }
    }

    try {
        $accessToken = getAccessToken($store['domain'], $store['client_id'], $store['client_secret']);
    } catch (Exception $e) {
        logLine($log, "  {$store['label']}: TOKEN EXCHANGE FAILED — " . $e->getMessage());
        continue;
    }

    if ($url === null) {
        $url = "https://{$store['domain']}/admin/api/{$apiVersion}/orders.json?"
             . "status=any&limit=250&created_at_min={$sinceDate}T00:00:00Z"
             . "&fields=id,financial_status,fulfillment_status,processed_at,cancelled_at,"
             . "total_price,subtotal_price,discount_codes,line_items,note_attributes,note,"
             . "payment_gateway_names,tags";
        logLine($log, "-- Store {$store['label']} ({$store['domain']}) --");
    }

    $storeFailed = false;
    while ($url) {
        $ch = curl_init($url);
        curl_setopt_array($ch, [
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_HEADER         => true,
            CURLOPT_HTTPHEADER     => ['X-Shopify-Access-Token: ' . $accessToken],
            CURLOPT_TIMEOUT        => 30,
        ]);
        $resp       = curl_exec($ch);
        $httpCode   = curl_getinfo($ch, CURLINFO_HTTP_CODE);
        $headerSize = curl_getinfo($ch, CURLINFO_HEADER_SIZE);
        curl_close($ch);

        if ($resp === false || $httpCode !== 200) {
            logLine($log, "  HTTP $httpCode error, aborting store {$store['label']}: " . substr((string)$resp, 0, 300));
            $storeFailed = true;
            break;
        }

        $headerStr = substr($resp, 0, $headerSize);
        $body      = substr($resp, $headerSize);
        $data      = json_decode($body, true);
        $orders    = $data['orders'] ?? [];

        foreach ($orders as $o) {
            try {
                $updateStmt->execute([
                    ':tags'                => $o['tags'] ?? null,
                    ':financial_status'    => $o['financial_status'] ?? null,
                    ':fulfillment_status'  => $o['fulfillment_status'] ?? 'unfulfilled',
                    ':paid_at'             => $o['processed_at'] ?? null,
                    ':cancelled_at'        => $o['cancelled_at'] ?? null,
                    ':total'               => $o['total_price'] ?? null,
                    ':subtotal'            => $o['subtotal_price'] ?? null,
                    ':discount_code'       => $o['discount_codes'][0]['code'] ?? null,
                    ':discount_amount'     => $o['discount_codes'][0]['amount'] ?? 0.00,
                    ':line_items'          => json_encode($o['line_items'] ?? []),
                    ':note_attributes'     => json_encode($o['note_attributes'] ?? []),
                    ':notes'               => $o['note'] ?? null,
                    ':payment_method'      => $o['payment_gateway_names'][0] ?? null,
                    ':shopify_order_id'    => $o['id'],
                ]);
                if ($updateStmt->rowCount() > 0) $updated++; else $skipped++;
            } catch (Exception $e) {
                $errors++;
                logLine($log, "  ERROR order {$o['id']}: " . $e->getMessage());
            }
        }

        $pageNum++;

        // Cursor-based pagination: Shopify returns a Link header like
        // <https://.../orders.json?...&page_info=xyz>; rel="next"
        $url = null;
        if (preg_match('/<([^>]+)>;\s*rel="next"/', $headerStr, $m)) {
            $url = $m[1];
        }

        // Save progress after every page — if the host kills this request mid-way,
        // the NEXT run picks up right here instead of starting over from page 1.
        file_put_contents($cursorFile, json_encode([
            'url' => $url, 'updated' => $updated, 'skipped' => $skipped, 'errors' => $errors, 'page' => $pageNum,
        ]));
        logLine($log, "  {$store['label']}: page $pageNum done — running totals: updated=$updated skipped=$skipped errors=$errors");

        usleep(500000); // ~2 req/sec, stay under Shopify's rate limit
    }

    logLine($log, "  {$store['label']}: FINISHED — updated=$updated skipped=$skipped errors=$errors" . ($storeFailed ? " (stopped early due to error above — will retry from this point next run)" : ""));
    if ($storeFailed) {
        // Save cursor so next run resumes from here instead of restarting; do NOT mark as done.
        file_put_contents($cursorFile, json_encode(['url' => $url, 'updated' => $updated, 'skipped' => $skipped, 'errors' => $errors, 'page' => $pageNum]));
    } else {
        if (file_exists($cursorFile)) unlink($cursorFile); // done — clear resume state for this store
        file_put_contents($doneFile, date('Y-m-d H:i:s')); // mark this store complete so future runs skip it
    }
}

logLine($log, "=== Backfill finished ===");
fclose($log);