<?php

declare(strict_types=1);

namespace App\Repositories;

use App\Core\Database;
use PDO;

final class UserRepository
{
    public function paginate(string $search = '', string $status = '', string $role = '', int $page = 1, int $perPage = 25, ?string $visibilityRole = null): array
    {
        $where = [];
        $params = [];
        if ($search !== '') {
            $where[] = '(u.full_name LIKE :search OR u.email LIKE :search OR u.phone LIKE :search)';
            $params['search'] = '%' . $search . '%';
        }
        if ($status !== '') {
            $where[] = 'u.status = :status';
            $params['status'] = $status;
        }
        if ($role !== '') {
            $where[] = 'EXISTS (SELECT 1 FROM user_roles ur2 INNER JOIN roles r2 ON r2.id = ur2.role_id WHERE ur2.user_id = u.id AND r2.name = :role)';
            $params['role'] = $role;
        }
        if ($visibilityRole === '__NONE__') {
            $where[] = '1 = 0';
        } elseif ($visibilityRole !== null) {
            $where[] = 'EXISTS (SELECT 1 FROM user_roles ur3 INNER JOIN roles r3 ON r3.id = ur3.role_id WHERE ur3.user_id = u.id AND r3.name = :visibility_role)';
            $params['visibility_role'] = $visibilityRole;
        }
        $whereSql = $where ? 'WHERE ' . implode(' AND ', $where) : '';
        $pdo = Database::connection();

        $count = $pdo->prepare("SELECT COUNT(*) FROM users u $whereSql");
        $count->execute($params);
        $total = (int) $count->fetchColumn();

        $pages = max(1, (int) ceil($total / $perPage));
        $page = min(max(1, $page), $pages);
        $offset = ($page - 1) * $perPage;

        $sql = "SELECT u.id, u.full_name, u.email, u.phone, u.status, u.last_login_at, u.created_at,
                       COALESCE(GROUP_CONCAT(r.display_name ORDER BY r.display_name SEPARATOR ', '), '') AS role_names,
                       COALESCE(GROUP_CONCAT(r.name ORDER BY r.name SEPARATOR ','), '') AS role_keys
                FROM users u
                LEFT JOIN user_roles ur ON ur.user_id = u.id
                LEFT JOIN roles r ON r.id = ur.role_id
                $whereSql
                GROUP BY u.id, u.full_name, u.email, u.phone, u.status, u.last_login_at, u.created_at
                ORDER BY u.full_name ASC
                LIMIT :limit OFFSET :offset";
        $stmt = $pdo->prepare($sql);
        foreach ($params as $key => $value) $stmt->bindValue(':' . $key, $value);
        $stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();

        return ['items' => $stmt->fetchAll(), 'total' => $total, 'page' => $page, 'pages' => $pages, 'perPage' => $perPage];
    }

    public function find(int $id): ?array
    {
        $stmt = Database::connection()->prepare('SELECT id, full_name, email, phone, status, last_login_at, created_at, updated_at FROM users WHERE id = :id LIMIT 1');
        $stmt->execute(['id' => $id]);
        $user = $stmt->fetch();
        if (!is_array($user)) return null;
        $user['roles'] = $this->rolesForUser($id);
        return $user;
    }

    public function findByEmail(string $email): ?array
    {
        $stmt = Database::connection()->prepare('SELECT id, full_name, email, phone, status, password_hash FROM users WHERE email = :email LIMIT 1');
        $stmt->execute(['email' => strtolower(trim($email))]);
        $user = $stmt->fetch();
        return is_array($user) ? $user : null;
    }

    public function roles(): array
    {
        return Database::connection()->query('SELECT id, name, display_name FROM roles ORDER BY display_name')->fetchAll();
    }

    public function rolesForUser(int $userId): array
    {
        $stmt = Database::connection()->prepare('SELECT r.id, r.name, r.display_name FROM user_roles ur INNER JOIN roles r ON r.id = ur.role_id WHERE ur.user_id = :user_id ORDER BY r.display_name');
        $stmt->execute(['user_id' => $userId]);
        return $stmt->fetchAll();
    }

    public function create(array $data): int
    {
        $stmt = Database::connection()->prepare('INSERT INTO users (full_name, email, phone, password_hash, status) VALUES (:full_name, :email, :phone, :password_hash, :status)');
        $stmt->execute($data);
        return (int) Database::connection()->lastInsertId();
    }

    public function update(int $id, array $data): void
    {
        $sets = [];
        $params = ['id' => $id];
        foreach ($data as $key => $value) {
            $sets[] = "$key = :$key";
            $params[$key] = $value;
        }
        if (!$sets) return;
        Database::connection()->prepare('UPDATE users SET ' . implode(', ', $sets) . ', updated_at = NOW() WHERE id = :id')->execute($params);
    }

    public function assignRoles(int $userId, array $roleIds): void
    {
        $pdo = Database::connection();
        $delete = $pdo->prepare('DELETE FROM user_roles WHERE user_id = :user_id');
        $delete->execute(['user_id' => $userId]);
        if (!$roleIds) return;
        $insert = $pdo->prepare('INSERT INTO user_roles (user_id, role_id) VALUES (:user_id, :role_id)');
        foreach (array_unique(array_map('intval', $roleIds)) as $roleId) {
            $insert->execute(['user_id' => $userId, 'role_id' => $roleId]);
        }
    }

    public function hasRole(int $userId, string $roleName): bool
    {
        $stmt = Database::connection()->prepare('SELECT 1 FROM user_roles ur INNER JOIN roles r ON r.id = ur.role_id WHERE ur.user_id = :user_id AND r.name = :role LIMIT 1');
        $stmt->execute(['user_id' => $userId, 'role' => $roleName]);
        return (bool) $stmt->fetchColumn();
    }

    public function countActiveSuperAdmins(): int
    {
        return (int) Database::connection()->query("SELECT COUNT(*) FROM users u INNER JOIN user_roles ur ON ur.user_id = u.id INNER JOIN roles r ON r.id = ur.role_id WHERE u.status = 'active' AND r.name = 'SUPER_ADMIN'")->fetchColumn();
    }

    public function audit(int $userId, string $action, int $entityId, array $metadata = []): void
    {
        $stmt = Database::connection()->prepare('INSERT INTO audit_logs (user_id, action, entity_type, entity_id, ip_address, metadata, created_at) VALUES (:user_id, :action, :entity_type, :entity_id, :ip, :metadata, NOW())');
        $stmt->execute([
            'user_id' => $userId,
            'action' => $action,
            'entity_type' => 'user',
            'entity_id' => $entityId,
            'ip' => $_SERVER['REMOTE_ADDR'] ?? null,
            'metadata' => json_encode($metadata, JSON_THROW_ON_ERROR),
        ]);
    }
}
