<?php

declare(strict_types=1);

namespace ApiModel\Infrastructure\Persistence\Repositories;

use ApiModel\Domain\Repository\ProfileRepositoryInterface;
use ApiModel\Infrastructure\Persistence\Database;
use PDO;

final class PdoProfileRepository implements ProfileRepositoryInterface
{
    public function __construct(private readonly PDO $pdo) {}

    public function findByRoleAndPage(string $roleId, string $pageId): ?array
    {
        $st = $this->pdo->prepare('SELECT * FROM auth.profile WHERE role_id = ? AND page_id = ?');
        $st->execute([$roleId, $pageId]);
        $row = $st->fetch();

        return $row !== false ? $row : null;
    }

    public function listByRole(string $roleId): array
    {
        $st = $this->pdo->prepare('SELECT * FROM auth.profile WHERE role_id = ?');
        $st->execute([$roleId]);
        return $st->fetchAll();
    }

    public function savePermission(string $roleId, string $pageId, bool $canView, bool $canCreate, bool $canEdit, bool $canDelete): void
    {
        $existing = $this->findByRoleAndPage($roleId, $pageId);

        if ($existing) {
            $this->pdo->prepare(
                'UPDATE auth.profile 
                 SET can_view = ?, can_create = ?, can_edit = ?, can_delete = ?, updated_at = CURRENT_TIMESTAMP
                 WHERE role_id = ? AND page_id = ?'
            )->execute([
                Database::bool($canView),
                Database::bool($canCreate),
                Database::bool($canEdit),
                Database::bool($canDelete),
                $roleId,
                $pageId
            ]);
        } else {
            $uuid = Database::uuid();
            $this->pdo->prepare(
                'INSERT INTO auth.profile (id, role_id, page_id, can_view, can_create, can_edit, can_delete)
                 VALUES (?, ?, ?, ?, ?, ?, ?)'
            )->execute([
                $uuid,
                $roleId,
                $pageId,
                Database::bool($canView),
                Database::bool($canCreate),
                Database::bool($canEdit),
                Database::bool($canDelete)
            ]);
        }
    }

    public function getPermissionsForUser(string $userId): array
    {
        $sql = '
            SELECT 
                pg.id as page_id,
                pg.path,
                pg.name_es,
                pg.name_en,
                pg.module_id,
                m.name_es as module_name_es,
                m.name_en as module_name_en,
                m.icon as module_icon,
                m.order_index as module_order,
                pg.icon as page_icon,
                pg.order_index as page_order,
                MAX(CASE WHEN p.can_view THEN 1 ELSE 0 END) as can_view,
                MAX(CASE WHEN p.can_create THEN 1 ELSE 0 END) as can_create,
                MAX(CASE WHEN p.can_edit THEN 1 ELSE 0 END) as can_edit,
                MAX(CASE WHEN p.can_delete THEN 1 ELSE 0 END) as can_delete
            FROM auth.role_user ru
            JOIN auth.profile p ON p.role_id = ru.role_id
            JOIN auth.page pg ON pg.id = p.page_id
            JOIN auth.module m ON m.id = pg.module_id
            WHERE ru.user_id = ? AND pg.is_active = TRUE AND m.is_active = TRUE AND p.can_view = TRUE
            GROUP BY pg.id, pg.path, pg.name_es, pg.name_en, pg.module_id, m.name_es, m.name_en, m.icon, m.order_index, pg.icon, pg.order_index
            ORDER BY m.order_index ASC, pg.order_index ASC
        ';

        $st = $this->pdo->prepare($sql);
        $st->execute([$userId]);
        $rows = $st->fetchAll();

        $out = [];
        foreach ($rows as $r) {
            $out[] = [
                'page_id' => $r['page_id'],
                'path' => $r['path'],
                'name_es' => $r['name_es'],
                'name_en' => $r['name_en'],
                'module_id' => $r['module_id'],
                'module_name_es' => $r['module_name_es'],
                'module_name_en' => $r['module_name_en'],
                'module_icon' => $r['module_icon'],
                'module_order' => (int) $r['module_order'],
                'page_icon' => $r['page_icon'],
                'page_order' => (int) $r['page_order'],
                'can_view' => (bool) $r['can_view'],
                'can_create' => (bool) $r['can_create'],
                'can_edit' => (bool) $r['can_edit'],
                'can_delete' => (bool) $r['can_delete'],
            ];
        }

        return $out;
    }

    public function userIsAdministrator(string $userId): bool
    {
        $st = $this->pdo->prepare(
            'SELECT r.name_en, r.name_es FROM auth.roles r
             JOIN auth.role_user ru ON ru.role_id = r.id
             WHERE ru.user_id = ? AND r.is_active = TRUE',
        );
        $st->execute([$userId]);

        foreach ($st->fetchAll() as $row) {
            $en = strtoupper(trim((string) $row['name_en']));
            $es = strtoupper(trim((string) $row['name_es']));
            if (in_array($en, ['ADMIN', 'ADMINISTRATOR', 'ADMINISTRADOR'], true)
                || in_array($es, ['ADMIN', 'ADMINISTRATOR', 'ADMINISTRADOR'], true)) {
                return true;
            }
        }

        return false;
    }
}
