<?php
namespace App\Models;

use App\Core\Database;

final class User
{
    public static function find(int $id): ?array
    {
        $stmt = Database::connection()->prepare(
            'SELECT * FROM utilisateurs WHERE id = :id AND deleted_at IS NULL LIMIT 1'
        );
        $stmt->execute(['id' => $id]);
        return $stmt->fetch() ?: null;
    }

    public static function findByEmail(string $email): ?array
    {
        $stmt = Database::connection()->prepare(
            'SELECT * FROM utilisateurs WHERE email = :email AND deleted_at IS NULL LIMIT 1'
        );
        $stmt->execute(['email' => mb_strtolower(trim($email))]);
        return $stmt->fetch() ?: null;
    }

    public static function all(): array
    {
        return Database::connection()->query(
            'SELECT id, nom, prenom, email, statut, derniere_connexion_at
             FROM utilisateurs
             WHERE deleted_at IS NULL
             ORDER BY nom, prenom'
        )->fetchAll();
    }

    public static function hasPermission(int $userId, string $code): bool
    {
        $decision = self::authorizationDecision($userId, $code);
        return $decision['allowed']
            && in_array($decision['scope'], ['global', 'organisation'], true);
    }

    public static function authorizationDecision(int $userId, string $code): array
    {
        $stmt = Database::connection()->prepare(
            "SELECT
                COALESCE(
                    (
                        SELECT up.accordee
                        FROM utilisateur_permissions up
                        INNER JOIN permissions p_override
                                ON p_override.id = up.permission_id
                               AND p_override.actif = 1
                        WHERE up.utilisateur_id = :override_user_id
                          AND p_override.code = :override_code
                        LIMIT 1
                    ),
                    (
                        SELECT 1
                        FROM utilisateur_roles ur
                        INNER JOIN roles r
                                ON r.id = ur.role_id
                               AND r.actif = 1
                               AND r.deleted_at IS NULL
                        INNER JOIN role_permissions rp
                                ON rp.role_id = r.id
                        INNER JOIN permissions p_role
                                ON p_role.id = rp.permission_id
                               AND p_role.actif = 1
                        WHERE ur.utilisateur_id = :role_user_id
                          AND p_role.code = :role_code
                        LIMIT 1
                    ),
                    0
                ) AS autorisee,
                (
                    SELECT up.perimetre
                    FROM utilisateur_permissions up
                    INNER JOIN permissions p_scope ON p_scope.id=up.permission_id AND p_scope.actif=1
                    WHERE up.utilisateur_id=:scope_user_id AND p_scope.code=:scope_code AND up.accordee=1
                    LIMIT 1
                ) AS override_scope"
        );

        $stmt->execute([
            'override_user_id' => $userId,
            'override_code' => $code,
            'role_user_id' => $userId,
            'role_code' => $code,
            'scope_user_id' => $userId,
            'scope_code' => $code,
        ]);
        $row = $stmt->fetch() ?: [];
        return [
            'allowed' => (int)($row['autorisee'] ?? 0) === 1,
            'scope' => (string)($row['override_scope'] ?? 'organisation'),
        ];
    }

    public static function hasAnyRole(int $userId, string ...$codes): bool
    {
        $codes=array_values(array_unique(array_filter($codes)));
        if($userId<=0 || !$codes){
            return false;
        }
        $placeholders=implode(',',array_fill(0,count($codes),'?'));
        $stmt=Database::connection()->prepare(
            "SELECT 1
             FROM utilisateur_roles ur
             INNER JOIN roles r
                     ON r.id=ur.role_id
                    AND r.actif=1
                    AND r.deleted_at IS NULL
             WHERE ur.utilisateur_id=?
               AND r.code IN ($placeholders)
             LIMIT 1"
        );
        $stmt->execute(array_merge([$userId],$codes));
        return (bool)$stmt->fetchColumn();
    }

    public static function permissionScope(int $userId, string $code): ?string
    {
        $stmt = Database::connection()->prepare(
            "SELECT up.perimetre
             FROM utilisateur_permissions up
             INNER JOIN permissions p ON p.id = up.permission_id
             WHERE up.utilisateur_id = :user_id
               AND p.code = :code
               AND up.accordee = 1
             LIMIT 1"
        );
        $stmt->execute(['user_id' => $userId, 'code' => $code]);

        $scope = $stmt->fetchColumn();
        return is_string($scope) && $scope !== '' ? $scope : null;
    }

    public static function updateLastLogin(int $id): void
    {
        $stmt = Database::connection()->prepare(
            'UPDATE utilisateurs
             SET derniere_connexion_at = NOW(), derniere_ip = :ip
             WHERE id = :id'
        );
        $stmt->execute([
            'id' => $id,
            'ip' => $_SERVER['REMOTE_ADDR'] ?? null,
        ]);
    }

    public static function logConnection(
        ?int $userId,
        string $email,
        bool $success,
        ?string $reason
    ): void {
        $stmt = Database::connection()->prepare(
            'INSERT INTO connexions_logs
             (utilisateur_id, email_tentee, succes, motif_echec, ip_address, user_agent)
             VALUES (:user_id, :email, :success, :reason, :ip, :user_agent)'
        );
        $stmt->execute([
            'user_id' => $userId,
            'email' => $email,
            'success' => $success ? 1 : 0,
            'reason' => $reason,
            'ip' => $_SERVER['REMOTE_ADDR'] ?? null,
            'user_agent' => $_SERVER['HTTP_USER_AGENT'] ?? null,
        ]);
    }
}
