<?php

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

/**
 * Give each branch its own copy of a programme two branches were sharing.
 *
 * 2026_09_13_000002 moved programmes into the strict bucket and backfilled each
 * one to the single branch its groups run at. Where a programme's groups ran at
 * *two* branches it declined to guess and fell through to the main-branch
 * fallback — which is the wrong answer, and quietly so: the group at the other
 * branch keeps pointing at a programme that branch can no longer see. Its name
 * renders blank, and PricingService cannot find a base price for it, so the
 * enrolment cannot be billed at all. `لا يوجد سعر محدد`, on a group that was
 * working the day before.
 *
 * OC-Sport does not have this shape — no group there points at another branch's
 * programme — which is exactly why it needs writing down rather than leaving to
 * the next person to discover. Every client runs `migrate --force` on its next
 * container start with no staging in between, and the tenants are not all shaped
 * like the one that was available to test against.
 *
 * So: replicate rather than pick a winner. The programme keeps its identity at
 * the branch it was pinned to, and every other branch using it gets a copy of
 * its own, with that branch's groups, enrolments, prices and product bundles
 * repointed at the copy. Nothing is deleted and nothing moves branch; the only
 * new rows are the copies.
 *
 * This is deliberately not clever about "the same programme". Two branches
 * running "فريق 2018" now have two rows, and editing one does not edit the
 * other. That is the point — it is what a branch being its own installation
 * means, and it is the same answer the product already gives for groups.
 */
return new class extends Migration
{
    public function up(): void
    {
        foreach ($this->orphanedPairs() as $pair) {
            $this->giveBranchItsOwnCopy((int) $pair->programme_id, (int) $pair->branch_id);
        }
    }

    /**
     * Every (programme, branch) pair where a group sits in one branch and its
     * programme in another.
     *
     * Driven off the groups rather than off the programmes, because a group is
     * the thing that breaks: an unused programme filed at the wrong branch is
     * untidy, but a group whose programme is invisible cannot be billed.
     */
    private function orphanedPairs(): array
    {
        if (! Schema::hasTable('training_groups') || ! Schema::hasColumn('training_groups', 'branch_id')) {
            return [];
        }

        return DB::select("
            SELECT DISTINCT g.training_program_id AS programme_id, g.branch_id
            FROM training_groups g
            JOIN training_programs p ON p.id = g.training_program_id
            WHERE g.branch_id IS NOT NULL
              AND p.branch_id IS NOT NULL
              AND g.branch_id <> p.branch_id
              AND g.deleted_at IS NULL
            ORDER BY g.training_program_id, g.branch_id
        ");
    }

    private function giveBranchItsOwnCopy(int $programmeId, int $branchId): void
    {
        $programme = DB::table('training_programs')->find($programmeId);

        if (! $programme) {
            return;
        }

        $columns = Schema::getColumnListing('training_programs');

        $copy = [];
        foreach ($columns as $column) {
            if ($column === 'id') {
                continue;
            }

            $copy[$column] = $programme->{$column} ?? null;
        }

        $copy['uuid'] = (string) Str::uuid();
        $copy['branch_id'] = $branchId;
        $copy['created_at'] = now();
        $copy['updated_at'] = now();

        // (academy_id, slug) is unique. Suffixing with the branch id rather than
        // its code because a code is nullable and a slug collision here would
        // abort the whole deploy.
        if (array_key_exists('slug', $copy)) {
            $copy['slug'] = $this->uniqueSlug((string) $programme->slug, (int) $programme->academy_id, $branchId);
        }

        $newId = (int) DB::table('training_programs')->insertGetId($copy);

        // The groups that made this necessary, and everything hanging off them.
        DB::table('training_groups')
            ->where('training_program_id', $programmeId)
            ->where('branch_id', $branchId)
            ->update(['training_program_id' => $newId]);

        if (Schema::hasColumn('enrollments', 'training_program_id')) {
            DB::table('enrollments')
                ->where('training_program_id', $programmeId)
                ->where('branch_id', $branchId)
                ->update(['training_program_id' => $newId]);
        }

        $this->copyPrices($programmeId, $newId, $branchId, (int) $programme->academy_id);
        $this->copyBundledProducts($programmeId, $newId, (int) $programme->academy_id);
    }

    private function uniqueSlug(string $slug, int $academyId, int $branchId): string
    {
        $candidate = Str::limit($slug, 200, '').'-b'.$branchId;
        $suffix = 1;

        while (DB::table('training_programs')
            ->where('academy_id', $academyId)
            ->where('slug', $candidate)
            ->exists()
        ) {
            $candidate = Str::limit($slug, 195, '').'-b'.$branchId.'-'.$suffix++;
        }

        return $candidate;
    }

    /**
     * A copy with no price is a copy that cannot be sold, so the prices come
     * with it. Only the active ones: a superseded price belongs to the history
     * of the original row, not to a branch that never charged it.
     */
    private function copyPrices(int $fromId, int $toId, int $branchId, int $academyId): void
    {
        if (! Schema::hasTable('base_prices')) {
            return;
        }

        $columns = Schema::getColumnListing('base_prices');

        $prices = DB::table('base_prices')
            ->where('priceable_type', 'like', '%TrainingProgram')
            ->where('priceable_id', $fromId)
            ->where('is_active', true)
            ->whereNull('deleted_at')
            ->get();

        foreach ($prices as $price) {
            $copy = [];
            foreach ($columns as $column) {
                if ($column === 'id') {
                    continue;
                }

                $copy[$column] = $price->{$column} ?? null;
            }

            $copy['uuid'] = (string) Str::uuid();
            $copy['priceable_id'] = $toId;
            $copy['branch_id'] = $branchId;
            $copy['academy_id'] = $academyId;
            $copy['created_at'] = now();
            $copy['updated_at'] = now();

            DB::table('base_prices')->insert($copy);
        }
    }

    private function copyBundledProducts(int $fromId, int $toId, int $academyId): void
    {
        if (! Schema::hasTable('program_products')) {
            return;
        }

        $rows = DB::table('program_products')->where('training_program_id', $fromId)->get();

        foreach ($rows as $row) {
            $exists = DB::table('program_products')
                ->where('training_program_id', $toId)
                ->where('product_id', $row->product_id)
                ->exists();

            if ($exists) {
                continue;
            }

            DB::table('program_products')->insert([
                'academy_id' => $academyId,
                'training_program_id' => $toId,
                'product_id' => $row->product_id,
                'is_required' => $row->is_required,
                'quantity' => $row->quantity,
                'created_at' => now(),
                'updated_at' => now(),
            ]);
        }
    }

    /**
     * Not reversible, and deliberately so. The copies are now the programmes
     * those branches' groups point at, with their own enrolments and prices
     * hanging off them; merging them back would have to pick which of two
     * diverged rows is the real one, and there is no answer to that question.
     */
    public function down(): void
    {
    }
};
