<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class BackfillHeadFaNewStudentsAccess extends Migration
{
    public function up(): void
    {
        $roleIds = $this->resolveRoleIds([
            'head fa',
            'head of fa',
            'head_of_fa',
            'head of department (finance)',
            'head of department finance',
        ]);

        if ($roleIds === []) {
            return;
        }

        $this->grantPermission($roleIds, 'view_new_students', ['read' => true]);
        $this->grantNavUrl($roleIds, 'admin/enrollment/new-students');
        cache()->clean();
    }

    public function down(): void
    {
    }

    /**
     * @param list<string> $roleKeys
     * @return list<int>
     */
    private function resolveRoleIds(array $roleKeys): array
    {
        if (! $this->db->tableExists('roles')) {
            return [];
        }

        $normalized = array_values(array_unique(array_map('strtolower', $roleKeys)));
        $builder = $this->db->table('roles')->select('id');
        $builder->groupStart()->whereIn('LOWER(name)', $normalized);
        if ($this->db->fieldExists('slug', 'roles')) {
            $builder->orWhereIn('LOWER(slug)', $normalized);
        }
        $rows = $builder->groupEnd()->get()->getResultArray();

        return array_values(array_unique(array_map(static fn (array $row): int => (int) $row['id'], $rows)));
    }

    /**
     * @param list<int> $roleIds
     * @param array{create?: bool, read?: bool, update?: bool, delete?: bool} $flags
     */
    private function grantPermission(array $roleIds, string $permissionName, array $flags): void
    {
        if (! $this->db->tableExists('permissions') || ! $this->db->tableExists('role_permissions')) {
            return;
        }

        $permissionId = $this->resolvePermissionId($permissionName);
        $now = date('Y-m-d H:i:s');

        foreach ($roleIds as $roleId) {
            $existing = $this->db->table('role_permissions')
                ->where('role_id', $roleId)
                ->where('permission_id', $permissionId)
                ->get()
                ->getRowArray();

            $grant = [
                'can_create' => ! empty($flags['create']) ? 1 : 0,
                'can_read' => ! empty($flags['read']) ? 1 : 0,
                'can_update' => ! empty($flags['update']) ? 1 : 0,
                'can_delete' => ! empty($flags['delete']) ? 1 : 0,
                'updated_at' => $now,
            ];

            if ($existing === null) {
                $grant['role_id'] = $roleId;
                $grant['permission_id'] = $permissionId;
                $grant['created_at'] = $now;
                if ($this->db->fieldExists('can_manage', 'role_permissions')) {
                    $grant['can_manage'] = 0;
                }
                $this->db->table('role_permissions')->insert($grant);
                continue;
            }

            $this->db->table('role_permissions')
                ->where('id', (int) $existing['id'])
                ->update([
                    'can_create' => max((int) ($existing['can_create'] ?? 0), $grant['can_create']),
                    'can_read' => max((int) ($existing['can_read'] ?? 0), $grant['can_read']),
                    'can_update' => max((int) ($existing['can_update'] ?? 0), $grant['can_update']),
                    'can_delete' => max((int) ($existing['can_delete'] ?? 0), $grant['can_delete']),
                    'updated_at' => $now,
                ]);
        }
    }

    private function resolvePermissionId(string $permissionName): int
    {
        $permission = $this->db->table('permissions')
            ->select('id')
            ->where('LOWER(name)', strtolower($permissionName))
            ->get()
            ->getRowArray();

        if ($permission !== null) {
            return (int) $permission['id'];
        }

        $now = date('Y-m-d H:i:s');
        $insert = [
            'name' => $permissionName,
            'created_at' => $now,
            'updated_at' => $now,
        ];
        if ($this->db->fieldExists('description', 'permissions')) {
            $insert['description'] = 'Seeded route permission.';
        }

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

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

    /**
     * @param list<int> $roleIds
     */
    private function grantNavUrl(array $roleIds, string $url): void
    {
        if (
            ! $this->db->tableExists('nav_items')
            || ! $this->db->tableExists('role_nav_items')
            || ! $this->db->fieldExists('role_id', 'role_nav_items')
        ) {
            return;
        }

        $navIds = $this->resolveNavItemIdsByUrl($url);
        if ($navIds === []) {
            return;
        }

        $now = date('Y-m-d H:i:s');
        foreach ($roleIds as $roleId) {
            foreach ($navIds as $navId) {
                $exists = $this->db->table('role_nav_items')
                    ->where('role_id', $roleId)
                    ->where('nav_item_id', $navId)
                    ->countAllResults() > 0;

                if (! $exists) {
                    $this->db->table('role_nav_items')->insert([
                        'role_id' => $roleId,
                        'nav_item_id' => $navId,
                        'created_at' => $now,
                        'updated_at' => $now,
                    ]);
                }
            }
        }
    }

    /**
     * @return list<int>
     */
    private function resolveNavItemIdsByUrl(string $url): array
    {
        $targetUrl = $this->normalizePath($url);
        $rows = $this->db->table('nav_items')
            ->select('id, url')
            ->where('url IS NOT NULL', null, false)
            ->get()
            ->getResultArray();

        $ids = [];
        foreach ($rows as $row) {
            if ($this->normalizePath((string) ($row['url'] ?? '')) === $targetUrl) {
                $ids[] = (int) $row['id'];
            }
        }

        return array_values(array_unique(array_filter($ids)));
    }

    private function normalizePath(string $path): string
    {
        return trim(preg_replace('#/+#', '/', $path), '/');
    }
}