<?php
declare(strict_types=1);
namespace App\Repositories;
use App\Core\Repository;

final class UserRepository extends Repository {
    public function findByUsername(string $username): ?array {
        return $this->one("SELECT u.*, r.code role_code, r.name role_name FROM users u JOIN roles r ON r.id=u.role_id WHERE username=? LIMIT 1", [$username]);
    }

    public function findById(int $id): ?array {
        return $this->one("SELECT u.*, r.code role_code, r.name role_name FROM users u JOIN roles r ON r.id=u.role_id WHERE u.id=? LIMIT 1", [$id]);
    }

    public function allUsers(): array {
        return $this->all("SELECT u.*, r.code role_code, r.name role_name FROM users u JOIN roles r ON r.id=u.role_id ORDER BY u.id DESC");
    }

    public function allRoles(): array {
        return $this->all("SELECT * FROM roles ORDER BY id ASC");
    }

    public function findRoleById(int $id): ?array {
        return $this->one("SELECT * FROM roles WHERE id=? LIMIT 1", [$id]);
    }

    public function createUser(array $data): int {
        return $this->insert(
            "INSERT INTO users (role_id, full_name, username, password_hash, is_active, created_at, updated_at)
             VALUES (?, ?, ?, ?, ?, NOW(), NOW())",
            [(int)$data['role_id'], trim((string)$data['full_name']), trim((string)$data['username']), (string)$data['password_hash'], (int)$data['is_active']]
        );
    }

    public function updateUser(int $id, array $data): int {
        return $this->exec(
            "UPDATE users SET role_id=?, full_name=?, username=?, is_active=?, updated_at=NOW() WHERE id=?",
            [(int)$data['role_id'], trim((string)$data['full_name']), trim((string)$data['username']), (int)$data['is_active'], $id]
        );
    }

    public function updatePassword(int $id, string $passwordHash): int {
        return $this->exec("UPDATE users SET password_hash=?, updated_at=NOW() WHERE id=?", [$passwordHash, $id]);
    }

    public function deleteUser(int $id): int {
        return $this->exec("DELETE FROM users WHERE id=? LIMIT 1", [$id]);
    }

    public function usernameExists(string $username, ?int $exceptId = null): bool {
        $sql = "SELECT id FROM users WHERE username=?";
        $params = [$username];
        if ($exceptId !== null) {
            $sql .= " AND id<>?";
            $params[] = $exceptId;
        }
        return $this->one($sql . " LIMIT 1", $params) !== null;
    }

    public function permissionCodesByRole(int $roleId): array {
        try {
            $rows = $this->all(
                "SELECT p.code
                 FROM permissions p
                 JOIN role_permissions rp ON rp.permission_id = p.id
                 WHERE rp.role_id=?
                 ORDER BY p.sort_order ASC, p.id ASC",
                [$roleId]
            );
            return array_values(array_map(fn($r) => (string)$r['code'], $rows));
        } catch (\Throwable $e) {
            return [];
        }
    }

    public function allPermissions(): array {
        try {
            return $this->all("SELECT * FROM permissions ORDER BY sort_order ASC, id ASC");
        } catch (\Throwable $e) {
            return [];
        }
    }

    public function syncRolePermissions(int $roleId, array $permissionIds): void {
        $this->exec("DELETE FROM role_permissions WHERE role_id=?", [$roleId]);
        foreach ($permissionIds as $pid) {
            $this->exec("INSERT INTO role_permissions (role_id, permission_id, created_at) VALUES (?, ?, NOW())", [$roleId, (int)$pid]);
        }
    }
}
