<?php

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Migrations\Migration;

/**
 * Moves data out of the ad-hoc `product_compatibles` table (created by the
 * AdditionalReportController beta) into the new normalized tables:
 *   - compatible_product_id > 0  -> pc_compatibles (product <-> product)
 *   - compatible_product_id = 0  -> pc_part_numbers (a serial / part number)
 *
 * The legacy table is left in place (deprecated) for safety. Idempotent:
 * uses the unique keys on the target tables to avoid duplicates on re-run.
 */
class MigrateLegacyProductCompatibles extends Migration
{
    public function up()
    {
        if (! Schema::hasTable('product_compatibles')
            || ! Schema::hasTable('pc_part_numbers')
            || ! Schema::hasTable('pc_compatibles')) {
            return;
        }

        $now = now();

        DB::table('product_compatibles')->orderBy('id')->chunk(500, function ($rows) use ($now) {
            foreach ($rows as $row) {
                $businessId = DB::table('products')->where('id', $row->product_id)->value('business_id');
                if (empty($businessId)) {
                    continue; // orphan row; skip
                }

                if ((int) $row->compatible_product_id > 0) {
                    // Product <-> product compatibility
                    DB::table('pc_compatibles')->updateOrInsert(
                        [
                            'product_id'            => $row->product_id,
                            'compatible_product_id' => $row->compatible_product_id,
                            'relation_type'         => 'compatible',
                        ],
                        [
                            'business_id'  => $businessId,
                            'source'       => 'manual',
                            'confidence'   => 100,
                            'needs_review' => 0,
                            'verified_at'  => $now,
                            'updated_at'   => $now,
                            'created_at'   => $row->created_at ?? $now,
                        ]
                    );
                } else {
                    // A free-text serial / part number
                    $value = trim((string) $row->compatible_with);
                    if ($value === '' || $value === 'product_relation') {
                        continue;
                    }
                    $normalized = strtoupper(preg_replace('/[^A-Za-z0-9]/', '', $value));
                    if ($normalized === '') {
                        continue;
                    }

                    DB::table('pc_part_numbers')->updateOrInsert(
                        [
                            'business_id' => $businessId,
                            'product_id'  => $row->product_id,
                            'normalized'  => $normalized,
                        ],
                        [
                            'part_number'  => $value,
                            'type'         => 'other',
                            'is_primary'   => 0,
                            'source'       => 'manual',
                            'confidence'   => 100,
                            'needs_review' => 0,
                            'verified_at'  => $now,
                            'updated_at'   => $now,
                            'created_at'   => $row->created_at ?? $now,
                        ]
                    );
                }
            }
        });
    }

    public function down()
    {
        // Non-destructive forward migration; nothing to reverse.
    }
}
