<?php

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

/**
 * Bundle a club's registration product ("قيد") onto its team ("فريق") programmes.
 *
 * Every player in a team programme is required to hold a registration card, but
 * nothing in the system encoded that — so the group view had no way to flag who
 * was missing one.
 *
 * Deliberately conditional: it only acts where an academy has BOTH an active
 * product whose name contains "قيد" AND programmes whose name begins with
 * "فريق". On any client without that shape it does nothing at all, which is
 * what makes it safe to ship to every tenant.
 *
 * Idempotent — the pivot's unique key means re-running inserts nothing.
 */
return new class extends Migration
{
    public function up(): void
    {
        if (! Schema::hasTable('program_products')
            || ! Schema::hasTable('products')
            || ! Schema::hasTable('training_programs')) {
            return;
        }

        $products = DB::table('products')
            ->where('is_active', true)
            ->whereNull('deleted_at')
            ->where('name_ar', 'ilike', '%قيد%')
            ->get(['id', 'academy_id']);

        foreach ($products as $product) {
            $programIds = DB::table('training_programs')
                ->where('academy_id', $product->academy_id)
                ->whereNull('deleted_at')
                ->where('name_ar', 'ilike', 'فريق%')
                ->pluck('id');

            foreach ($programIds as $programId) {
                $exists = DB::table('program_products')
                    ->where('training_program_id', $programId)
                    ->where('product_id', $product->id)
                    ->exists();

                if ($exists) {
                    continue;
                }

                DB::table('program_products')->insert([
                    'academy_id' => $product->academy_id,
                    'training_program_id' => $programId,
                    'product_id' => $product->id,
                    'is_required' => true,
                    'quantity' => 1,
                    'created_at' => now(),
                    'updated_at' => now(),
                ]);
            }
        }
    }

    public function down(): void
    {
        if (! Schema::hasTable('program_products')) {
            return;
        }

        // Remove only the links this migration could have created.
        $productIds = DB::table('products')->where('name_ar', 'ilike', '%قيد%')->pluck('id');

        if ($productIds->isEmpty()) {
            return;
        }

        $programIds = DB::table('training_programs')->where('name_ar', 'ilike', 'فريق%')->pluck('id');

        DB::table('program_products')
            ->whereIn('product_id', $productIds)
            ->whereIn('training_program_id', $programIds)
            ->delete();
    }
};
