<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class GrantRequestedRolePageAccess extends Migration
{
    public function up(): void
    {
        $this->grantNavAccess();
        $this->grantNamedPermissions();
        cache()->clean();
    }

    public function down(): void
    {
    }

    private function grantNavAccess(): void
    {
        if (! $this->db->tableExists('nav_items') || ! $this->db->tableExists('role_nav_items')) {
            return;
        }

        $this->grantNavItemsToRoles(
            ['head of csm', 'head_of_department_communication', 'csm_contributor'],
            [
                ['label' => 'Communication'],
                ['url' => 'admin/enrollment/new-students'],
                ['url' => 'whatsapp/'],
                ['url' => 'admin/print-requests'],
                ['url' => '/administrator/absence'],
            ]
        );

        $financialNavItems = array_merge(
            [['label' => 'Financial']],
            $this->findChildNavSpecsForParentLabel('Financial'),
            [
                ['label' => 'Event Management'],
                ['url' => 'administrator/events'],
                ['url' => 'admin/print-requests'],
                ['url' => '/administrator/absence'],
            ]
        );

        $this->grantNavItemsToRoles(
            ['head fa', 'head of fa', 'head_of_fa', 'head of department (finance)', 'head of department finance'],
            array_merge($financialNavItems, [
                ['url' => 'admin/enrollment/new-students'],
            ])
        );

        $this->grantNavItemsToRoles(
            ['financial_contributor'],
            [
                ['label' => 'Financial'],
                ['url' => 'payment/manual_pay'],
                ['url' => '/payment/manual'],
                ['url' => 'admin/print-requests'],
                ['url' => '/administrator/absence'],
            ]
        );
    }

    private function grantNamedPermissions(): void
    {
        if (
            ! $this->db->tableExists('roles')
            || ! $this->db->tableExists('permissions')
            || ! $this->db->tableExists('role_permissions')
        ) {
            return;
        }

        $this->grantPermissionsToRoles(
            ['head of csm', 'head_of_department_communication', 'csm_contributor'],
            [
                'view_new_students' => ['read' => true],
            ]
        );

        $this->grantPermissionsToRoles(
            ['head fa', 'head of fa', 'head_of_fa', 'head of department (finance)', 'head of department finance'],
            [
                'view_new_students' => ['read' => true],
                'view_invoice' => ['read' => true],
                'view_payment' => ['read' => true],
                'view_financial_reports' => ['create' => true, 'read' => true, 'update' => true],
                'create_invoice' => ['create' => true, 'read' => true],
                'update_invoice' => ['read' => true, 'update' => true],
                'create_payment' => ['create' => true, 'read' => true],
                'update_payment' => ['read' => true, 'update' => true],
                'oversee_financial_aid' => ['create' => true, 'read' => true, 'update' => true],
            ]
        );

        $this->grantPermissionsToRoles(
            ['financial_contributor'],
            [
                'view_invoice' => ['read' => true],
                'view_payment' => ['read' => true],
                'create_payment' => ['create' => true, 'read' => true],
                'update_payment' => ['read' => true, 'update' => true],
            ]
        );
    }

    /**
     * @param list<string> $roleKeys
     * @param list<array{label?: string, url?: string}> $navSpecs
     */
    private function grantNavItemsToRoles(array $roleKeys, array $navSpecs): void
    {
        $navIds = $this->resolveNavItemIds($navSpecs);
        if ($navIds === []) {
            return;
        }

        $now = date('Y-m-d H:i:s');

        if ($this->db->fieldExists('role_id', 'role_nav_items') && $this->db->tableExists('roles')) {
            foreach ($this->resolveRoleIds($roleKeys) 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,
                        ]);
                    }
                }
            }
        }

        if ($this->db->fieldExists('role', 'role_nav_items')) {
            foreach ($roleKeys as $role) {
                foreach ($navIds as $navId) {
                    $exists = $this->db->table('role_nav_items')
                        ->where('LOWER(role)', strtolower($role))
                        ->where('nav_item_id', $navId)
                        ->countAllResults() > 0;

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

    /**
     * @param list<string> $roleKeys
     * @param array<string, array{create?: bool, read?: bool, update?: bool, delete?: bool}> $permissions
     */
    private function grantPermissionsToRoles(array $roleKeys, array $permissions): void
    {
        $roleIds = $this->resolveRoleIds($roleKeys);
        if ($roleIds === []) {
            return;
        }

        $now = date('Y-m-d H:i:s');

        foreach ($permissions as $permissionName => $flags) {
            $permissionId = $this->resolvePermissionId($permissionName);
            if ($permissionId <= 0) {
                continue;
            }

            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 ($this->db->fieldExists('can_manage', 'role_permissions')) {
                    $grant['can_manage'] = 0;
                }

                if ($existing === null) {
                    $grant['role_id'] = $roleId;
                    $grant['permission_id'] = $permissionId;
                    $grant['created_at'] = $now;
                    $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,
                    ]);
            }
        }
    }

    /**
     * @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)));
        if ($normalized === []) {
            return [];
        }

        $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)));
    }

    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<array{label?: string, url?: string}> $navSpecs
     * @return list<int>
     */
    private function resolveNavItemIds(array $navSpecs): array
    {
        $ids = [];
        foreach ($navSpecs as $spec) {
            if (isset($spec['url'])) {
                $url = $this->normalizePath($spec['url']);
                $rows = $this->db->table('nav_items')
                    ->select('id, url')
                    ->where('url IS NOT NULL', null, false)
                    ->get()
                    ->getResultArray();

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

                continue;
            } elseif (isset($spec['label'])) {
                $builder = $this->db->table('nav_items')->select('id');
                $builder->where('LOWER(label)', strtolower($spec['label']));
            } else {
                continue;
            }

            foreach ($builder->get()->getResultArray() as $row) {
                $ids[] = (int) $row['id'];
            }
        }

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

    /**
     * @return list<array{url: string}>
     */
    private function findChildNavSpecsForParentLabel(string $parentLabel): array
    {
        $parentColumn = $this->parentColumn();
        if ($parentColumn === null) {
            return [];
        }

        $parents = $this->db->table('nav_items')
            ->select('id')
            ->where('LOWER(label)', strtolower($parentLabel))
            ->get()
            ->getResultArray();

        $parentIds = array_values(array_filter(array_map(static fn (array $row): int => (int) $row['id'], $parents)));
        if ($parentIds === []) {
            return [];
        }

        $children = $this->db->table('nav_items')
            ->select('url')
            ->whereIn($parentColumn, $parentIds)
            ->where('url IS NOT NULL', null, false)
            ->get()
            ->getResultArray();

        return array_values(array_filter(array_map(
            static fn (array $row): array => ['url' => (string) $row['url']],
            $children
        ), static fn (array $spec): bool => trim($spec['url']) !== ''));
    }

    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;
    }

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