domovoy/app/Repositories/DiscoveredHostRepository.php

<?php

declare(strict_types=1);

namespace Domovoy\Repositories;

use Domovoy\Models\DiscoveredHost;
use PDO;

class DiscoveredHostRepository
{
    private PDO $pdo;

    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }

    public function findById(int $id): ?DiscoveredHost
    {
        $stmt = $this->pdo->prepare('SELECT * FROM discovered_hosts WHERE id = :id');
        $stmt->execute(['id' => $id]);
        $row = $stmt->fetch();
        return $row ? DiscoveredHost::fromArray($row) : null;
    }

    public function findByStatus(string $status, int $limit = 100): array
    {
        $stmt = $this->pdo->prepare('SELECT * FROM discovered_hosts WHERE status = :status ORDER BY last_seen DESC LIMIT :limit');
        $stmt->bindValue('status', $status);
        $stmt->bindValue('limit', $limit, PDO::PARAM_INT);
        $stmt->execute();
        $results = [];
        while ($row = $stmt->fetch()) {
            $results[] = DiscoveredHost::fromArray($row);
        }
        return $results;
    }

    public function findByScanJob(int $scanJobId): array
    {
        $stmt = $this->pdo->prepare('SELECT * FROM discovered_hosts WHERE scan_job_id = :scan_job_id ORDER BY ip_address ASC');
        $stmt->execute(['scan_job_id' => $scanJobId]);
        $results = [];
        while ($row = $stmt->fetch()) {
            $results[] = DiscoveredHost::fromArray($row);
        }
        return $results;
    }

    public function findAll(int $limit = 100): array
    {
        $stmt = $this->pdo->prepare('SELECT * FROM discovered_hosts ORDER BY last_seen DESC LIMIT :limit');
        $stmt->bindValue('limit', $limit, PDO::PARAM_INT);
        $stmt->execute();
        $results = [];
        while ($row = $stmt->fetch()) {
            $results[] = DiscoveredHost::fromArray($row);
        }
        return $results;
    }

    public function findFiltered(
        ?int $rangeId = null,
        ?int $scanJobId = null,
        ?string $status = 'new',
        int $limit = 100,
        string $sortBy = 'ip',
        string $sortDir = 'asc'
    ): array {
        $where = [];
        $params = [];

        if ($rangeId !== null) {
            $where[] = 'sj.network_range_id = :range_id';
            $params['range_id'] = $rangeId;
        }
        if ($scanJobId !== null) {
            $where[] = 'dh.scan_job_id = :scan_job_id';
            $params['scan_job_id'] = $scanJobId;
        }
        if ($status !== null && $status !== '' && $status !== 'all') {
            $where[] = 'dh.status = :status';
            $params['status'] = $status;
        }

        $sql = 'SELECT dh.*, sj.network_range_id, nr.name AS range_name, nr.cidr AS range_cidr,
                       d.id AS device_id, d.name AS device_name,
                       mac_device.id AS suggested_mac_device_id,
                       mac_device.name AS suggested_mac_device_name,
                       hostname_device.id AS suggested_hostname_device_id,
                       hostname_device.name AS suggested_hostname_device_name,
                       ip_device.id AS suggested_ip_device_id,
                       ip_device.name AS suggested_ip_device_name
                FROM discovered_hosts dh
                LEFT JOIN scan_jobs sj ON sj.id = dh.scan_job_id
                LEFT JOIN network_ranges nr ON nr.id = sj.network_range_id
                LEFT JOIN devices d ON d.id = dh.matched_device_id
                LEFT JOIN devices mac_device ON mac_device.mac_address = dh.mac_address
                LEFT JOIN devices hostname_device ON hostname_device.name = dh.hostname
                LEFT JOIN devices ip_device ON ip_device.primary_ip = dh.ip_address';

        if (!empty($where)) {
            $sql .= ' WHERE ' . implode(' AND ', $where);
        }

        $sortColumn = $this->sortColumn($sortBy);
        $direction = strtolower($sortDir) === 'desc' ? 'DESC' : 'ASC';
        $sql .= " ORDER BY {$sortColumn} {$direction} LIMIT :limit";

        $stmt = $this->pdo->prepare($sql);
        foreach ($params as $key => $value) {
            $stmt->bindValue($key, $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
        }
        $stmt->bindValue('limit', $limit, PDO::PARAM_INT);
        $stmt->execute();

        $results = [];
        while ($row = $stmt->fetch()) {
            $results[] = $row;
        }
        return $results;
    }

    private function sortColumn(string $sortBy): string
    {
        return match ($sortBy) {
            'hostname' => 'dh.hostname',
            'mac' => 'dh.mac_address',
            'vendor' => 'dh.vendor',
            'ports' => 'dh.open_ports_json',
            'range' => 'nr.name',
            'status' => 'dh.status',
            'device' => 'd.name',
            'last_seen' => 'dh.last_seen',
            default => 'INET_ATON(dh.ip_address)',
        };
    }

    public function save(DiscoveredHost $host): void
    {
        $now = (new \DateTimeImmutable())->format('Y-m-d H:i:s');
        if ($host->id === null) {
            $existing = $this->findExistingNewHost($host);
            if ($existing !== null) {
                $host->id = $existing->id;
                $host->firstSeen = $existing->firstSeen;
            }
        }

        if ($host->id === null) {
            $stmt = $this->pdo->prepare(
                'INSERT INTO discovered_hosts
                 (scan_job_id, ip_address, mac_address, hostname, vendor, detected_os,
                  open_ports_json, protocols_json, fingerprint_json, confidence, status,
                  matched_device_id, first_seen, last_seen, created_at, updated_at)
                 VALUES
                 (:scan_job_id, :ip_address, :mac_address, :hostname, :vendor, :detected_os,
                  :open_ports_json, :protocols_json, :fingerprint_json, :confidence, :status,
                  :matched_device_id, :first_seen, :last_seen, :created_at, :updated_at)'
            );
            $stmt->execute([
                'scan_job_id' => $host->scanJobId,
                'ip_address' => $host->ipAddress,
                'mac_address' => $host->macAddress !== null ? strtolower($host->macAddress) : null,
                'hostname' => $host->hostname,
                'vendor' => $host->vendor,
                'detected_os' => $host->detectedOs,
                'open_ports_json' => json_encode($host->openPorts),
                'protocols_json' => json_encode($host->protocols),
                'fingerprint_json' => json_encode($host->fingerprint),
                'confidence' => $host->confidence,
                'status' => $host->status,
                'matched_device_id' => $host->matchedDeviceId,
                'first_seen' => $now,
                'last_seen' => $now,
                'created_at' => $now,
                'updated_at' => $now,
            ]);
            $host->id = (int)$this->pdo->lastInsertId();
        } else {
            $stmt = $this->pdo->prepare(
                'UPDATE discovered_hosts SET
                 scan_job_id = :scan_job_id, ip_address = :ip_address,
                 mac_address = :mac_address, hostname = :hostname, vendor = :vendor,
                 detected_os = :detected_os, open_ports_json = :open_ports_json,
                 protocols_json = :protocols_json, fingerprint_json = :fingerprint_json,
                 confidence = :confidence, status = :status,
                 matched_device_id = :matched_device_id, last_seen = :last_seen,
                 updated_at = :updated_at
                 WHERE id = :id'
            );
            $stmt->execute([
                'id' => $host->id,
                'scan_job_id' => $host->scanJobId,
                'ip_address' => $host->ipAddress,
                'mac_address' => $host->macAddress !== null ? strtolower($host->macAddress) : null,
                'hostname' => $host->hostname,
                'vendor' => $host->vendor,
                'detected_os' => $host->detectedOs,
                'open_ports_json' => json_encode($host->openPorts),
                'protocols_json' => json_encode($host->protocols),
                'fingerprint_json' => json_encode($host->fingerprint),
                'confidence' => $host->confidence,
                'status' => $host->status,
                'matched_device_id' => $host->matchedDeviceId,
                'last_seen' => $now,
                'updated_at' => $now,
            ]);
        }
    }

    private function findExistingNewHost(DiscoveredHost $host): ?DiscoveredHost
    {
        if ($host->macAddress !== null && trim($host->macAddress) !== '') {
            $stmt = $this->pdo->prepare(
                'SELECT * FROM discovered_hosts
                 WHERE mac_address = :mac_address AND status = :status
                 ORDER BY last_seen DESC LIMIT 1'
            );
            $stmt->execute([
                'mac_address' => strtolower($host->macAddress),
                'status' => 'new',
            ]);
            $row = $stmt->fetch();
            if ($row) {
                return DiscoveredHost::fromArray($row);
            }
        }

        $stmt = $this->pdo->prepare(
            'SELECT * FROM discovered_hosts
             WHERE ip_address = :ip_address AND status = :status
             ORDER BY last_seen DESC LIMIT 1'
        );
        $stmt->execute([
            'ip_address' => $host->ipAddress,
            'status' => 'new',
        ]);
        $row = $stmt->fetch();

        return $row ? DiscoveredHost::fromArray($row) : null;
    }
}