seedproject-web/api/commands/SeedMenuCommand.php

<?php
use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;

class SeedMenuCommand extends Command
{
    protected $commandName        = 'SeedMenu';
    protected $commandDescription = 'Create sp_menus + sp_menu_items tables and seed admin sidebar data';

    protected function configure()
    {
        $this->setName($this->commandName)
             ->setDescription($this->commandDescription);
    }

    protected function execute(InputInterface $input, OutputInterface $output)
    {
        $output->writeln('<info>Creating tables...</info>');

        Db::execute("
            CREATE TABLE IF NOT EXISTS sp_menus (
              menu_id   int unsigned NOT NULL AUTO_INCREMENT,
              slug      varchar(50)  NOT NULL,
              name      varchar(100) NOT NULL,
              active    tinyint      DEFAULT 1,
              PRIMARY KEY (menu_id),
              UNIQUE KEY (slug)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        ", []);

        Db::execute("
            CREATE TABLE IF NOT EXISTS sp_menu_items (
              item_id      int unsigned NOT NULL AUTO_INCREMENT,
              menu_id      int unsigned NOT NULL,
              parent_id    int unsigned DEFAULT NULL,
              label        varchar(100) NOT NULL,
              icon         varchar(100) DEFAULT NULL,
              type         enum('link','header','divider') DEFAULT 'link',
              url          varchar(255) DEFAULT NULL,
              perm_id      int unsigned DEFAULT NULL,
              match_prefix tinyint      DEFAULT 0,
              sort_order   int          DEFAULT 0,
              active       tinyint      DEFAULT 1,
              PRIMARY KEY (item_id),
              KEY (menu_id),
              KEY (parent_id),
              CONSTRAINT FOREIGN KEY (perm_id) REFERENCES sp_permissions(perm_id) ON DELETE SET NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        ", []);

        $output->writeln('<info>Tables ready. Seeding data...</info>');

        // Idempotent: skip if already seeded
        $existing = Db::getRow("SELECT menu_id FROM sp_menus WHERE slug = 'admin_sidebar' LIMIT 1");
        if ($existing) {
            $output->writeln('<comment>admin_sidebar already exists — skipping seed. Run with --force to re-seed (delete rows manually first).</comment>');
            return Command::SUCCESS;
        }

        // ── Insert menu ──────────────────────────────────────────────────────────
        $menuResult = json_decode(Db::insert('sp_menus', [
            'slug'   => 'admin_sidebar',
            'name'   => 'Admin Sidebar',
            'active' => 1,
        ]), true);

        $menuId = (int)$menuResult['ID'];
        $output->writeln("Menu ID: {$menuId}");

        // Helper: fetch perm_id by controller.action, return null if not found
        $perm = function(string $controller, string $action) use ($output): ?int {
            $row = Db::getRow(
                "SELECT perm_id FROM sp_permissions WHERE perm_controller = ? AND perm_action = ? LIMIT 1",
                [$controller, $action]
            );
            if (!$row) {
                $output->writeln("<comment>  Permission not found: {$controller}.{$action} — using NULL</comment>");
                return null;
            }
            return (int)$row['perm_id'];
        };

        // Helper: insert item, return item_id
        $insertItem = function(array $data) use ($menuId): int {
            $data['menu_id'] = $menuId;
            $result = json_decode(Db::insert('sp_menu_items', $data), true);
            return (int)$result['ID'];
        };

        $sort = 10;

        // ── Parent: Manage Account ───────────────────────────────────────────────
        $acctId = $insertItem([
            'parent_id'   => null,
            'label'       => 'Manage Account',
            'icon'        => 'ki-duotone ki-address-book',
            'type'        => 'link',
            'url'         => null,
            'perm_id'     => null,
            'sort_order'  => $sort += 10,
        ]);

        $subSort = 0;
        $insertItem(['parent_id' => $acctId, 'label' => 'Account Settings',   'type' => 'link', 'url' => '/account',              'perm_id' => null, 'sort_order' => $subSort += 10]);
        $insertItem(['parent_id' => $acctId, 'label' => 'Billing',            'type' => 'link', 'url' => '/account/billing',      'perm_id' => null, 'sort_order' => $subSort += 10]);
        $insertItem(['parent_id' => $acctId, 'label' => 'Invoices',           'type' => 'link', 'url' => '/account/invoices',     'perm_id' => null, 'sort_order' => $subSort += 10]);
        $insertItem(['parent_id' => $acctId, 'label' => 'Integrations & API', 'type' => 'link', 'url' => '/account/integrations', 'perm_id' => null, 'sort_order' => $subSort += 10]);
        $insertItem(['parent_id' => $acctId, 'label' => 'Activity Logs',      'type' => 'link', 'url' => '/account/activity',     'perm_id' => null, 'sort_order' => $subSort += 10]);

        // ── Parent: Administrator ────────────────────────────────────────────────
        $adminId = $insertItem([
            'parent_id'   => null,
            'label'       => 'Administrator',
            'icon'        => 'ki-duotone ki-shield-tick',
            'type'        => 'link',
            'url'         => null,
            'perm_id'     => null,
            'sort_order'  => $sort += 10,
        ]);

        $subSort = 0;
        $insertItem(['parent_id' => $adminId, 'label' => 'Overview',       'type' => 'link', 'url' => '/admin',              'perm_id' => $perm('admin', 'index'),        'sort_order' => $subSort += 10, 'match_prefix' => 0]);
        $insertItem(['parent_id' => $adminId, 'label' => 'Manage Orgs',    'type' => 'link', 'url' => '/admin/orgs',         'perm_id' => $perm('admin', 'orgs'),         'sort_order' => $subSort += 10, 'match_prefix' => 1]);
        $insertItem(['parent_id' => $adminId, 'label' => 'Manage Users',   'type' => 'link', 'url' => '/admin/users',        'perm_id' => $perm('admin', 'users'),        'sort_order' => $subSort += 10, 'match_prefix' => 1]);
        $insertItem(['parent_id' => $adminId, 'label' => 'Integrations',   'type' => 'link', 'url' => '/admin/integrations', 'perm_id' => $perm('admin', 'integrations'), 'sort_order' => $subSort += 10]);
        $insertItem(['parent_id' => $adminId, 'label' => 'User Roles',     'type' => 'link', 'url' => '/admin/roles',        'perm_id' => $perm('admin', 'roles'),        'sort_order' => $subSort += 10, 'match_prefix' => 1]);
        $insertItem(['parent_id' => $adminId, 'label' => 'Activity Logs',  'type' => 'link', 'url' => '/admin/activity',     'perm_id' => $perm('admin', 'activity'),     'sort_order' => $subSort += 10]);
        $insertItem(['parent_id' => $adminId, 'label' => 'Menu Builder',   'type' => 'link', 'url' => '/admin/menu',         'perm_id' => $perm('admin', 'menu'),         'sort_order' => $subSort += 10, 'match_prefix' => 1]);

        $output->writeln('<info>Seeded successfully.</info>');
        return Command::SUCCESS;
    }
}