<?php

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

return new class extends Migration
{
    private const BACKUP_TABLE = 'admin_menu__backup_pre_reorg';
    private const GRANTS_BACKUP_TABLE = 'admin_role_permissions__reorg_added';

    /**
     * Sections we are creating, keyed by a stable handle used internally
     * during this migration. The actual DB row id is captured at insert.
     */
    private const NEW_SECTIONS = [
        'shipping_tax'   => ['title' => 'Shipping & Tax',  'icon' => 'fa fa-truck',       'order' => 20],
        'reviews'        => ['title' => 'Reviews',         'icon' => 'fa fa-star-half-o', 'order' => 40],
        'communications' => ['title' => 'Communications',  'icon' => 'fa fa-envelope',    'order' => 60],
    ];

    /**
     * Items we are inserting that don't currently exist in admin_menu.
     * Parent is identified by a handle (existing section title or one of
     * the NEW_SECTIONS keys prefixed with `new:`).
     */
    private const NEW_ITEMS = [
        // Reviews section
        ['title' => 'Feefo Reviews',   'path' => '/plugins/FeefoReviews/admin/',   'parent_handle' => 'new:reviews', 'order' => 30],
        ['title' => 'Trustpilot API',  'path' => '/plugins/TrustpilotAPI/admin/',  'parent_handle' => 'new:reviews', 'order' => 40],
        // Communications section
        ['title' => 'Members Emails',  'path' => '/modules/Members/admin/emails.php', 'parent_handle' => 'new:communications', 'order' => 50],
        // Shop additions
        ['title' => 'Refunds',         'path' => '/plugins/Refunds/admin/',        'parent_handle' => 'Shop', 'order' => 250],
        // MTC Only additions
        ['title' => 'Module Builder',  'path' => '/modules/ModuleBuilder/admin/',  'parent_handle' => 'MTC Only', 'order' => 150],
    ];

    /**
     * Re-parenting plan. Keys are existing item titles to move (resolved
     * uniquely against the source section). Values describe the new
     * parent — either an existing section title or 'new:<handle>'.
     *
     * Format: source_section => [item_title => new_parent_handle, ...]
     */
    private const REPARENT_MAP = [
        'Shop' => [
            'Manage Shipping Options' => 'new:shipping_tax',
            'Manage Zones'            => 'new:shipping_tax',
            'Shipping Charges'        => 'new:shipping_tax',
            'VAT Rates'               => 'new:shipping_tax',
            // Note: this leaves Shop focused on catalog/marketing.
            // Email items move to Communications:
            'Order Status Email Text' => 'new:communications',
            'Default Email Text'      => 'new:communications',
        ],
        'Clinic' => [
            // Reviews split out
            'Reviews'      => 'new:reviews',
            'Review Types' => 'new:reviews',
        ],
        'MTC Only' => [
            // Email templates centralised
            'Core Emails'   => 'new:communications',
            'Shop Emails'   => 'new:communications',
            'Share Buttons' => 'new:communications',
        ],
        // Sections that get fully absorbed into a parent
        'Doctor Policy' => [
            'Add New Doctor Policy'  => 'Clinic',
            'Manage Doctor Policies' => 'Clinic',
        ],
        'Doctor Upload' => [
            'Upload new doctor list'   => 'Administration',
            'View uploaded doctor list' => 'Administration',
        ],
        'Black List' => [
            'Add New Black List entry' => 'Administration',
            'Manage Black List'        => 'Administration',
        ],
        'Basket Builder' => [
            // Basket Builder is a sales tool — admin creates baskets for
            // specific users and sends them payment links. Belongs in Shop.
            'View Orders'      => 'Shop',
            'Create a Basket'  => 'Shop',
        ],
        'SEO' => [
            'SEO Admin' => 'Content Manager',
        ],
        'File Manager' => [
            'File Manager' => 'Content Manager',
        ],
        'Configuration' => [
            'Roles and permissions' => 'Administration',
        ],
        'Reports' => [
            // Pull Subscription Logs from Clinic into Reports for visibility
        ],
    ];

    /**
     * Section title renames (existing section -> new title).
     */
    private const RENAMES = [
        'Admin Users'     => 'Administration',
        'Content Manager' => 'Content',
        'MTC Only'        => 'mtc. only',
    ];

    /**
     * Per-item renames keyed by path (paths are stable; titles aren't).
     */
    private const ITEM_RENAMES = [
        '/plugins/BasketBuilder/admin/index.php' => 'Basket Builder Orders',
        '/plugins/BasketBuilder/admin/add.php'   => 'Create Custom Basket',
    ];

    /**
     * Sections to delete after re-parenting (by title). These are the
     * sections that REPARENT_MAP fully empties.
     */
    private const SECTIONS_TO_DELETE = [
        'Doctor Policy',
        'Doctor Upload',
        'Black List',
        'Basket Builder',
        'SEO',
        'File Manager',
        'Configuration',
    ];

    /**
     * Final display order for sections (by title after renames).
     * Frequency-first: daily-use sections on top, config at the bottom.
     */
    private const SECTION_ORDER = [
        'Shop'           => 10,
        'Shipping & Tax' => 20,
        'Clinic'         => 30,
        'Reviews'        => 40,
        'Content'        => 50,
        'Communications' => 60,
        'Reports'        => 70,
        'Administration' => 80,
        'mtc. only'      => 90,
    ];

    public function up(): void
    {
        if (! Schema::hasTable('admin_menu')) {
            return;
        }

        // Skip on fresh installs / already-reorganised DBs. This migration
        // reshapes a production menu that contains the pre-reorg sections
        // below; if none of them are present, the seeder has already
        // produced the post-reorg state and there is nothing to do.
        $preReorgSections = ['Shop', 'Clinic', 'MTC Only', 'Admin Users', 'Content Manager'];
        $hasPreReorgMenu = DB::table('admin_menu')
            ->where('sub_id', 0)
            ->whereIn('title', $preReorgSections)
            ->exists();
        if (! $hasPreReorgMenu) {
            return;
        }

        // Backup table is DDL (CREATE TABLE) which implicitly commits any
        // open transaction on MariaDB. Do it before opening one.
        $this->backupTable();

        DB::transaction(function () {
            // 1. Audit: capture role grants tied to sections we're about to
            //    delete, and re-grant on the new parent section.
            $this->migrateRoleGrants();

            // 2. Re-parent orphaned MembersMessaging items (sub_id=1024 has
            //    no matching parent row — these are invisible in nav).
            $this->fixOrphans();

            // 3. Create the new sections.
            $newSectionIds = $this->createNewSections();

            // 4. Re-parent items per REPARENT_MAP.
            $this->reparentItems($newSectionIds);

            // 5. Insert items that exist as features but weren't in the menu.
            $this->insertNewItems($newSectionIds);

            // 6. Move Clinic > Subscription Logs into Reports.
            $this->moveSubscriptionLogs();

            // 7. Rename sections.
            foreach (self::RENAMES as $from => $to) {
                DB::table('admin_menu')
                    ->where('sub_id', 0)
                    ->where('title', $from)
                    ->update(['title' => $to]);
            }

            // 7b. Rename items where the name is ambiguous in its new
            //     home (e.g. Basket Builder's "View Orders" sitting next
            //     to Shop's "Orders").
            foreach (self::ITEM_RENAMES as $path => $newTitle) {
                DB::table('admin_menu')
                    ->where('path', $path)
                    ->update(['title' => $newTitle]);
            }

            // 8. Delete now-empty sections. Guard against orphaning: if any
            //    child rows still reference this section after re-parenting
            //    (e.g. plugin-added items not covered by REPARENT_MAP), abort
            //    rather than leave invisible orphans behind.
            foreach (self::SECTIONS_TO_DELETE as $title) {
                $sectionId = $this->sectionIdByTitle($title);
                if ($sectionId === null) {
                    continue;
                }

                $remainingChildren = DB::table('admin_menu')
                    ->where('sub_id', $sectionId)
                    ->pluck('title')
                    ->all();

                if (! empty($remainingChildren)) {
                    throw new \RuntimeException(sprintf(
                        'Refusing to delete admin_menu section "%s" (id %d): %d child row(s) remain after re-parenting: %s. Extend REPARENT_MAP to cover these items.',
                        $title,
                        $sectionId,
                        count($remainingChildren),
                        implode(', ', $remainingChildren)
                    ));
                }

                DB::table('admin_menu')
                    ->where('id', $sectionId)
                    ->where('sub_id', 0)
                    ->delete();
            }

            // 9. Apply final section ordering.
            foreach (self::SECTION_ORDER as $title => $order) {
                DB::table('admin_menu')
                    ->where('sub_id', 0)
                    ->where('title', $title)
                    ->update(['order' => $order]);
            }
        });
    }

    public function down(): void
    {
        if (! Schema::hasTable(self::BACKUP_TABLE)) {
            // Nothing to restore from.
            return;
        }

        // Wrap the restore in a transaction so a mid-restore failure leaves
        // admin_menu intact rather than half-empty. Use DELETE (transactional)
        // instead of TRUNCATE (DDL, auto-commits, non-rollbackable).
        DB::transaction(function () {
            DB::table('admin_menu')->delete();

            DB::table(self::BACKUP_TABLE)->orderBy('id')->chunk(500, function ($rows) {
                $payload = array_map(fn($r) => (array) $r, $rows->all());
                DB::table('admin_menu')->insert($payload);
            });

            // Remove the exact (role_id, permission_id) pairs migrateRoleGrants()
            // added in up(). A NOT-IN sweep against the restored admin_menu would
            // miss grants copied onto pre-existing destination sections (Clinic,
            // Administration, Content, Shop) whose ids survive the restore.
            if (Schema::hasTable(self::GRANTS_BACKUP_TABLE)) {
                DB::table(self::GRANTS_BACKUP_TABLE)->orderBy('role_id')->chunk(500, function ($rows) {
                    foreach ($rows as $row) {
                        DB::table('admin_role_permissions')
                            ->where('role_id', $row->role_id)
                            ->where('permission_id', $row->permission_id)
                            ->delete();
                    }
                });
            }
        });

        // Schema drops are DDL and auto-commit — keep them outside the
        // transaction. Only run after a successful restore.
        Schema::dropIfExists(self::GRANTS_BACKUP_TABLE);
        Schema::dropIfExists(self::BACKUP_TABLE);
    }

    private function backupTable(): void
    {
        Schema::dropIfExists(self::BACKUP_TABLE);
        // CREATE TABLE ... LIKE preserves schema; then copy rows.
        DB::statement('CREATE TABLE ' . self::BACKUP_TABLE . ' LIKE admin_menu');
        DB::statement('INSERT INTO ' . self::BACKUP_TABLE . ' SELECT * FROM admin_menu');

        // Track the exact (role_id, permission_id) pairs migrateRoleGrants()
        // adds, so down() can remove just those pairs rather than relying on
        // a NOT-IN sweep (which misses grants copied onto pre-existing
        // destination sections that survive the restore).
        Schema::dropIfExists(self::GRANTS_BACKUP_TABLE);
        Schema::create(self::GRANTS_BACKUP_TABLE, function (Blueprint $table) {
            $table->integer('role_id');
            $table->integer('permission_id');
            $table->unique(['role_id', 'permission_id']);
        });
    }

    private function fixOrphans(): void
    {
        $clinicId = $this->sectionIdByTitle('Clinic');
        if ($clinicId === null) {
            return;
        }

        // Re-parent MembersMessaging items (My messages, Unassigned messages)
        // that point to non-existent sub_id=1024.
        DB::table('admin_menu')
            ->whereIn('path', [
                '/plugins/MembersMessaging/admin/my_messages.php',
                '/plugins/MembersMessaging/admin/index.php',
            ])
            ->update(['sub_id' => $clinicId]);

        // The third orphan ('Log in as member') points to a deleted file
        // /modules/Members/admin/login_as.php — drop the row entirely.
        DB::table('admin_menu')
            ->where('path', '/modules/Members/admin/login_as.php')
            ->delete();
    }

    private function createNewSections(): array
    {
        $ids = [];
        foreach (self::NEW_SECTIONS as $handle => $section) {
            // Reuse an existing row if a previous (possibly failed) run
            // already inserted this section.
            $existingId = $this->sectionIdByTitle($section['title']);
            if ($existingId !== null) {
                $ids[$handle] = $existingId;
                continue;
            }

            $id = DB::table('admin_menu')->insertGetId([
                'sub_id'     => 0,
                'title'      => $section['title'],
                'path'       => '',
                'activePath' => '',
                'constant'   => '',
                'icon'       => $section['icon'],
                'new_window' => 0,
                'order'      => $section['order'],
            ]);
            $ids[$handle] = $id;
        }
        return $ids;
    }

    private function reparentItems(array $newSectionIds): void
    {
        foreach (self::REPARENT_MAP as $sourceSection => $items) {
            // Use resolveHandle so we match the source whether the DB
            // still holds the pre-rename title (fresh migrate) or the
            // post-rename title (e.g. seeder already produced 'mtc. only').
            $sourceId = $this->resolveHandle($sourceSection, $newSectionIds);
            if ($sourceId === null) {
                continue;
            }

            foreach ($items as $itemTitle => $destHandle) {
                $destId = $this->resolveHandle($destHandle, $newSectionIds);
                if ($destId === null) {
                    continue;
                }

                DB::table('admin_menu')
                    ->where('sub_id', $sourceId)
                    ->where('title', $itemTitle)
                    ->update(['sub_id' => $destId]);
            }
        }
    }

    private function insertNewItems(array $newSectionIds): void
    {
        foreach (self::NEW_ITEMS as $item) {
            $parentId = $this->resolveHandle($item['parent_handle'], $newSectionIds);
            if ($parentId === null) {
                continue;
            }

            // Skip if an item with the same path already exists (idempotency).
            $exists = DB::table('admin_menu')
                ->where('path', $item['path'])
                ->exists();
            if ($exists) {
                continue;
            }

            DB::table('admin_menu')->insert([
                'sub_id'     => $parentId,
                'title'      => $item['title'],
                'path'       => $item['path'],
                'activePath' => $item['path'],
                'constant'   => '',
                'icon'       => '',
                'new_window' => 0,
                'order'      => $item['order'],
            ]);
        }
    }

    private function moveSubscriptionLogs(): void
    {
        $reportsId = $this->sectionIdByTitle('Reports');
        if ($reportsId === null) {
            return;
        }

        DB::table('admin_menu')
            ->where('path', '/admin/subscription-logs')
            ->update(['sub_id' => $reportsId]);
    }

    /**
     * For each section we're about to delete, copy its role grants onto
     * the new parent so admins keep their access. Uses INSERT IGNORE
     * semantics via on-the-fly NOT EXISTS check (compound PK).
     */
    private function migrateRoleGrants(): void
    {
        foreach (self::SECTIONS_TO_DELETE as $sectionTitle) {
            $sourceId = $this->sectionIdByTitle($sectionTitle);
            if ($sourceId === null) {
                continue;
            }

            // Pick the destination section that this section's items are
            // being absorbed into. Look it up from REPARENT_MAP (all items
            // in a deleted section should re-parent to the same place).
            $itemsMap = self::REPARENT_MAP[$sectionTitle] ?? [];
            if (empty($itemsMap)) {
                continue;
            }
            $destHandle = reset($itemsMap);
            $destId = $this->resolveHandle($destHandle, []);
            if ($destId === null) {
                continue;
            }

            $grants = DB::table('admin_role_permissions')
                ->where('permission_id', $sourceId)
                ->pluck('role_id');

            foreach ($grants as $roleId) {
                $alreadyGranted = DB::table('admin_role_permissions')
                    ->where('role_id', $roleId)
                    ->where('permission_id', $destId)
                    ->exists();

                if (! $alreadyGranted) {
                    DB::table('admin_role_permissions')->insert([
                        'role_id'       => $roleId,
                        'permission_id' => $destId,
                        'created_at'    => now(),
                        'updated_at'    => now(),
                    ]);
                    DB::table(self::GRANTS_BACKUP_TABLE)->insert([
                        'role_id'       => $roleId,
                        'permission_id' => $destId,
                    ]);
                }
            }
        }
    }

    private function sectionIdByTitle(string $title): ?int
    {
        $row = DB::table('admin_menu')
            ->where('sub_id', 0)
            ->where('title', $title)
            ->first(['id']);
        return $row ? (int) $row->id : null;
    }

    /**
     * Resolve a parent handle to a section id. Handles starting with
     * 'new:' are looked up in the in-memory map of just-created
     * sections; everything else is a section title.
     */
    private function resolveHandle(string $handle, array $newSectionIds): ?int
    {
        if (str_starts_with($handle, 'new:')) {
            $key = substr($handle, 4);
            return $newSectionIds[$key] ?? null;
        }

        // After renames the title may have changed; check both.
        $aliases = [$handle];
        foreach (self::RENAMES as $from => $to) {
            if ($handle === $from) {
                $aliases[] = $to;
            } elseif ($handle === $to) {
                $aliases[] = $from;
            }
        }

        foreach ($aliases as $title) {
            $id = $this->sectionIdByTitle($title);
            if ($id !== null) {
                return $id;
            }
        }
        return null;
    }
};
