nshop/app/Jobs/MigrateReviewJob.php

<?php

namespace App\Jobs;

use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\SerializesModels;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

/**
 * Processes a single batch of Magento reviews and writes them into the
 * Bagisto product_reviews table.
 *
 * Each job receives an array of pre-fetched review rows so no remote DB
 * connection is held across queue serialization/unserialization.
 */
class MigrateReviewJob implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    /** Retry up to 3 times before marking as failed. */
    public int $tries = 3;

    /** Allow 5 minutes per job for large batches. */
    public int $timeout = 300;

    /**
     * @param  array<int, array<string, mixed>>  $reviews
     *        Each element contains:
     *          review_id, created_at, magento_product_id, status_id,
     *          title, comment, name, customer_email, avg_rating
     * @param  array<int, int>  $productIdMap   magento_product_id → bagisto product_id
     * @param  array<string, int>  $customerEmailMap  email → bagisto customer_id
     */
    public function __construct(
        private readonly array $reviews,
        private readonly array $productIdMap,
        private readonly array $customerEmailMap,
    ) {}

    public function handle(): void
    {
        $statusMap = [
            1 => 'approved',
            2 => 'pending',
            3 => 'disapproved',
        ];

        $now = now();
        $inserted = 0;
        $skipped = 0;

        foreach ($this->reviews as $row) {
            $bagistoProductId = $this->productIdMap[$row['magento_product_id']] ?? null;

            if ($bagistoProductId === null) {
                Log::warning('MigrateReviewJob: no Bagisto product for Magento product_id='.$row['magento_product_id'].', review_id='.$row['review_id'].'. Skipping.');
                $skipped++;
                continue;
            }

            // Deduplicate: skip if already migrated (keyed on migrated_from_asteria_id).
            $exists = DB::table('product_reviews')
                ->where('migrated_from_asteria_id', $row['review_id'])
                ->exists();

            if ($exists) {
                $skipped++;
                continue;
            }

            $rating = $this->normaliseRating($row['avg_rating']);
            $status = $statusMap[$row['status_id']] ?? 'pending';
            $customerId = $row['customer_email']
                ? ($this->customerEmailMap[strtolower($row['customer_email'])] ?? null)
                : null;

            $title   = $this->truncate((string) ($row['title'] ?? ''), 255) ?: '(no title)';
            $comment = $this->truncate((string) ($row['comment'] ?? ''), 65535);
            $name    = $this->truncate((string) ($row['name'] ?? ''), 255) ?: 'Guest';

            DB::table('product_reviews')->insert([
                'title'                   => $title,
                'comment'                 => $comment,
                'rating'                  => $rating,
                'status'                  => $status,
                'product_id'              => $bagistoProductId,
                'customer_id'             => $customerId,
                'name'                    => $name,
                'migrated_from_asteria_id' => $row['review_id'],
                'created_at'              => $row['created_at'] ?? $now,
                'updated_at'              => $now,
            ]);

            $newReviewId = DB::getPdo()->lastInsertId();

            // Download and store review images from review_media_image.url
            if (! empty($row['images'])) {
                $this->migrateImages((int) $newReviewId, $row['images']);
            }

            $inserted++;
        }

        Log::info("MigrateReviewJob: inserted={$inserted}, skipped={$skipped}");
    }

    /**
     * Store image URLs from Asteria directly as attachment paths.
     * Files will be uploaded to S3 in bulk separately.
     *
     * @param  int           $bagistoReviewId
     * @param  list<string>  $urls
     */
    private function migrateImages(int $bagistoReviewId, array $urls): void
    {
        foreach ($urls as $url) {
            if (empty($url)) {
                continue;
            }

            DB::table('product_review_attachments')->insert([
                'review_id' => $bagistoReviewId,
                'type'      => 'image',
                'mime_type' => null,
                'path'      => $url,
            ]);
        }
    }

    /**
     * Map Magento's average star value (1–5 float) to a Bagisto integer 1–5.
     * Falls back to 5 when null/zero (treat missing rating as best-case).
     */
    private function normaliseRating(mixed $avgRating): int
    {
        if ($avgRating === null || $avgRating == 0) {
            return 5;
        }

        return (int) max(1, min(5, round((float) $avgRating)));
    }

    private function truncate(string $value, int $maxLength): string
    {
        return mb_strlen($value) > $maxLength
            ? mb_substr($value, 0, $maxLength)
            : $value;
    }
}