<?php

declare(strict_types=1);

namespace App\Modules\Administration\Repositories;

use PDO;
use System\Contracts\DatabaseInterface;
use System\Database\QueryBuilder;

final class RoleRepository
{
    private PDO $pdo;

    public function __construct(
        private QueryBuilder $query,
        DatabaseInterface $database
    ) {
        $this->pdo = $database->getConnection();
    }

    public function all(): array
    {
        $statement = $this->pdo->query(
            'SELECT
                r.id,
                r.role_name,
                r.description,
                r.active,
                r.created_at,
                COUNT(DISTINCT ur.user_id) AS user_count,
                COUNT(DISTINCT rp.permission_id) AS permission_count
             FROM roles r
             LEFT JOIN user_roles ur
                ON ur.role_id = r.id
             LEFT JOIN role_permissions rp
                ON rp.role_id = r.id
             GROUP BY
                r.id,
                r.role_name,
                r.description,
                r.active,
                r.created_at
             ORDER BY r.id DESC'
        );

        return $statement->fetchAll(PDO::FETCH_ASSOC);
    }

    public function find(int $id): ?array
    {
        return $this->query
            ->table('roles')
            ->find($id);
    }

    public function findByName(
        string $roleName
    ): ?array {
        return $this->query
            ->table('roles')
            ->where('role_name', $roleName)
            ->first();
    }

    public function create(array $data): bool
    {
        return $this->query
            ->table('roles')
            ->insert($data);
    }

    public function update(
        int $id,
        array $data
    ): bool {
        return $this->query
            ->table('roles')
            ->where('id', $id)
            ->update($data);
    }

    public function setActive(
        int $id,
        int $active
    ): bool {
        return $this->query
            ->table('roles')
            ->where('id', $id)
            ->update([
                'active' => $active,
                'updated_at' => date('Y-m-d H:i:s')
            ]);
    }

    public function userCount(int $roleId): int
    {
        $statement = $this->pdo->prepare(
            'SELECT COUNT(*)
             FROM user_roles
             WHERE role_id = ?'
        );

        $statement->execute([$roleId]);

        return (int) $statement->fetchColumn();
    }
    public function activePermissions(): array
{
    return $this->query
        ->table('permissions')
        ->where('active', 1)
        ->orderBy(
            'permission_name',
            'ASC'
        )
        ->get();
}

public function permissionIds(
    int $roleId
): array {
    $statement = $this->pdo->prepare(
        'SELECT permission_id
         FROM role_permissions
         WHERE role_id = ?'
    );

    $statement->execute([
        $roleId
    ]);

    return array_map(
        'intval',
        $statement->fetchAll(
            PDO::FETCH_COLUMN
        )
    );
}

public function syncPermissions(
    int $roleId,
    array $permissionIds
): void {
    $this->pdo->beginTransaction();

    try {

        $delete = $this->pdo->prepare(
            'DELETE FROM role_permissions
             WHERE role_id = ?'
        );

        $delete->execute([
            $roleId
        ]);

        if (!empty($permissionIds)) {

            $insert = $this->pdo->prepare(
                'INSERT INTO role_permissions
                (
                    role_id,
                    permission_id,
                    created_at,
                    updated_at
                )
                VALUES
                (
                    ?,
                    ?,
                    NOW(),
                    NOW()
                )'
            );

            foreach ($permissionIds as $permissionId) {

                $insert->execute([
                    $roleId,
                    $permissionId
                ]);
            }
        }

        $this->pdo->commit();

    } catch (\Throwable $exception) {

        if ($this->pdo->inTransaction()) {
            $this->pdo->rollBack();
        }

        throw $exception;
    }
}
}