<?php

declare(strict_types=1);

namespace App\Modules\Administration\Repositories;

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

final class UserRepository
{
    private PDO $pdo;

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

    public function all(): array
    {
        return $this->query
            ->table('users')
            ->select([
                'id',
                'username',
                'email',
                'full_name',
                'active',
                'created_at'
            ])
            ->orderBy('id', 'DESC')
            ->get();
    }

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

    public function findByUsername(
        string $username
    ): ?array {
        return $this->query
            ->table('users')
            ->where('username', $username)
            ->first();
    }

    public function findByEmail(
        string $email
    ): ?array {
        return $this->query
            ->table('users')
            ->where('email', $email)
            ->first();
    }

    public function totalUsers(): int
    {
        return $this->query
            ->table('users')
            ->count();
    }

    public function roles(): array
    {
        return $this->query
            ->table('roles')
            ->where('active', 1)
            ->orderBy('role_name', 'ASC')
            ->get();
    }

    public function roleExists(int $roleId): bool
    {
        return $this->query
            ->table('roles')
            ->where('id', $roleId)
            ->where('active', 1)
            ->exists();
    }

    public function userRoleId(int $userId): int
    {
        $statement = $this->pdo->prepare(
            'SELECT role_id
             FROM user_roles
             WHERE user_id = ?
             LIMIT 1'
        );

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

        return (int) $statement->fetchColumn();
    }

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

        try {

            $statement = $this->pdo->prepare(
                'INSERT INTO users
                (
                    username,
                    email,
                    password,
                    full_name,
                    failed_attempts,
                    active,
                    created_at,
                    updated_at
                )
                VALUES
                (
                    ?,
                    ?,
                    ?,
                    ?,
                    0,
                    ?,
                    NOW(),
                    NOW()
                )'
            );

            $statement->execute([
                $userData['username'],
                $userData['email'],
                $userData['password'],
                $userData['full_name'],
                $userData['active']
            ]);

            $userId = (int) $this->pdo->lastInsertId();

            $roleStatement = $this->pdo->prepare(
                'INSERT INTO user_roles
                (
                    user_id,
                    role_id,
                    created_at,
                    updated_at
                )
                VALUES (?, ?, NOW(), NOW())'
            );

            $roleStatement->execute([
                $userId,
                $roleId
            ]);

            $this->pdo->commit();

        } catch (\Throwable $exception) {

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

            throw $exception;
        }
    }

    public function updateWithRole(
        int $userId,
        array $userData,
        int $roleId
    ): void {
        $this->pdo->beginTransaction();

        try {

            $statement = $this->pdo->prepare(
                'UPDATE users
                 SET username = ?,
                     email = ?,
                     full_name = ?,
                     active = ?,
                     updated_at = NOW()
                 WHERE id = ?'
            );

            $statement->execute([
                $userData['username'],
                $userData['email'],
                $userData['full_name'],
                $userData['active'],
                $userId
            ]);

            $deleteRole = $this->pdo->prepare(
                'DELETE FROM user_roles
                 WHERE user_id = ?'
            );

            $deleteRole->execute([$userId]);

            $insertRole = $this->pdo->prepare(
                'INSERT INTO user_roles
                (
                    user_id,
                    role_id,
                    created_at,
                    updated_at
                )
                VALUES (?, ?, NOW(), NOW())'
            );

            $insertRole->execute([
                $userId,
                $roleId
            ]);

            $this->pdo->commit();

        } catch (\Throwable $exception) {

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

            throw $exception;
        }
    }

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

    public function resetPassword(
        int $userId,
        string $passwordHash
    ): bool {
        return $this->query
            ->table('users')
            ->where('id', $userId)
            ->update([
                'password' => $passwordHash,
                'failed_attempts' => 0,
                'updated_at' => date('Y-m-d H:i:s')
            ]);
    }
}