<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class AddEnrollmentAdministrationNavItem extends Migration
{
    private string $label = 'Enrollment Administration';
    private string $url = 'administrator/enrollment-admin';

    public function up(): void
    {
        if (! $this->db->tableExists('nav_items')) {
            return;
        }

        $parentColumn = $this->parentColumn();
        $parent = $this->studentAffairsParent($parentColumn);
        $navItemId = $this->navItemId($parentColumn, $parent);

        if ($navItemId <= 0) {
            return;
        }

        $this->grantRoles($navItemId, [
            'administrator',
            'principal',
            'vice_principal',
            'head of department (education)',
        ]);

        cache()->clean();
    }

    public function down(): void
    {
        if (! $this->db->tableExists('nav_items')) {
            return;
        }

        $row = $this->db->table('nav_items')
            ->where('url', $this->url)
            ->get()
            ->getRowArray();

        if ($row === null) {
            return;
        }

        if ($this->db->tableExists('role_nav_items')) {
            $this->db->table('role_nav_items')
                ->where('nav_item_id', (int) $row['id'])
                ->delete();
        }

        $this->db->table('nav_items')
            ->where('id', (int) $row['id'])
            ->delete();

        cache()->clean();
    }

    private function navItemId(?string $parentColumn, ?array $parent): int
    {
        $existing = $this->db->table('nav_items')
            ->where('url', $this->url)
            ->get()
            ->getRowArray();

        if ($existing !== null) {
            $updates = [
                'label' => $this->label,
                'is_enabled' => 1,
                'updated_at' => date('Y-m-d H:i:s'),
            ];
            if ($parentColumn !== null && ! empty($parent['id'])) {
                $updates[$parentColumn] = (int) $parent['id'];
            }

            $this->db->table('nav_items')
                ->where('id', (int) $existing['id'])
                ->update($updates);

            return (int) $existing['id'];
        }

        $insert = [
            'label' => $this->label,
            'url' => $this->url,
            'sort_order' => 6,
            'is_enabled' => 1,
            'created_at' => date('Y-m-d H:i:s'),
            'updated_at' => date('Y-m-d H:i:s'),
        ];
        if ($this->db->fieldExists('icon_class', 'nav_items')) {
            $insert['icon_class'] = 'bi bi-diagram-3';
        }
        if ($parentColumn !== null && ! empty($parent['id'])) {
            $insert[$parentColumn] = (int) $parent['id'];
        }

        $this->db->table('nav_items')->insert($insert);

        return (int) $this->db->insertID();
    }

    private function studentAffairsParent(?string $parentColumn): ?array
    {
        $builder = $this->db->table('nav_items')->where('label', 'Student-Affairs');
        if ($parentColumn !== null) {
            $builder->where($parentColumn, null);
        }

        return $builder->get()->getRowArray();
    }

    private function grantRoles(int $navItemId, array $roleNames): void
    {
        if (! $this->db->tableExists('role_nav_items')) {
            return;
        }

        if ($this->db->fieldExists('role_id', 'role_nav_items') && $this->db->tableExists('roles')) {
            foreach ($roleNames as $roleName) {
                $roleBuilder = $this->db->table('roles')->select('id');
                $roleBuilder->groupStart()
                    ->where('LOWER(name)', strtolower($roleName));
                if ($this->db->fieldExists('slug', 'roles')) {
                    $roleBuilder->orWhere('LOWER(slug)', strtolower($roleName));
                }
                $role = $roleBuilder->groupEnd()
                    ->limit(1)
                    ->get()
                    ->getRowArray();
                if ($role === null) {
                    continue;
                }

                $exists = $this->db->table('role_nav_items')
                    ->where('role_id', (int) $role['id'])
                    ->where('nav_item_id', $navItemId)
                    ->countAllResults() > 0;
                if (! $exists) {
                    $this->db->table('role_nav_items')->insert([
                        'role_id' => (int) $role['id'],
                        'nav_item_id' => $navItemId,
                        'created_at' => date('Y-m-d H:i:s'),
                        'updated_at' => date('Y-m-d H:i:s'),
                    ]);
                }
            }

            return;
        }

        if (! $this->db->fieldExists('role', 'role_nav_items')) {
            return;
        }

        foreach ($roleNames as $roleName) {
            $roleName = strtolower($roleName);
            $exists = $this->db->table('role_nav_items')
                ->where('role', $roleName)
                ->where('nav_item_id', $navItemId)
                ->countAllResults() > 0;
            if (! $exists) {
                $this->db->table('role_nav_items')->insert([
                    'role' => $roleName,
                    'nav_item_id' => $navItemId,
                    'created_at' => date('Y-m-d H:i:s'),
                    'updated_at' => date('Y-m-d H:i:s'),
                ]);
            }
        }
    }

    private function parentColumn(): ?string
    {
        if ($this->db->fieldExists('menu_parent_id', 'nav_items')) {
            return 'menu_parent_id';
        }

        if ($this->db->fieldExists('parent_id', 'nav_items')) {
            return 'parent_id';
        }

        return null;
    }
}