PHP-MVC-TERRITORY/app/Repositories/HouseholderNameRepository.php

<?php

declare(strict_types=1);

namespace App\Repositories;

use Core\Repository;

class HouseholderNameRepository extends Repository
{
    protected string $table      = 'householder_names';
    protected string $primaryKey = 'id';

    public function countAll(string $search = '', string $householdId = ''): int
    {
        [$sql, $params] = $this->buildFilterQuery(
            'SELECT COUNT(*) AS n FROM householder_names hn
             JOIN households h ON h.id = hn.household_id',
            $search, $householdId
        );

        $row = $this->database->first($sql, $params);
        return (int) ($row['n'] ?? 0);
    }

    /** @return list<array<string,mixed>> */
    public function findPaged(int $page, int $perPage, string $search = '', string $householdId = ''): array
    {
        $offset = ($page - 1) * $perPage;

        [$where, $params] = $this->buildFilterQuery(
            'SELECT hn.*, h.address AS household_address, t.name AS territory_name
             FROM householder_names hn
             JOIN households h ON h.id = hn.household_id
             JOIN territories t ON t.id = h.territory_id',
            $search, $householdId
        );

        return $this->database->query(
            $where . ' ORDER BY h.address ASC, hn.name ASC LIMIT :limit OFFSET :offset',
            array_merge($params, ['limit' => $perPage, 'offset' => $offset])
        );
    }

    /** @return list<array<string,mixed>> */
    public function findAllByHousehold(int|string $householdId): array
    {
        return $this->database->query(
            'SELECT * FROM householder_names WHERE household_id = :id ORDER BY name ASC',
            ['id' => $householdId]
        );
    }

    public function findWithHousehold(int|string $id): ?array
    {
        return $this->database->first(
            'SELECT hn.*, h.address AS household_address, h.territory_id,
                    t.name AS territory_name
             FROM householder_names hn
             JOIN households h ON h.id = hn.household_id
             JOIN territories t ON t.id = h.territory_id
             WHERE hn.id = :id',
            ['id' => $id]
        );
    }

    public function toggleLetterReturned(int|string $id): bool
    {
        return $this->database->execute(
            'UPDATE householder_names
             SET letter_returned = CASE WHEN letter_returned = 1 THEN 0 ELSE 1 END,
                 return_date     = CASE WHEN letter_returned = 0 THEN datetime(\'now\') ELSE NULL END,
                 updated_at      = datetime(\'now\')
             WHERE id = :id',
            ['id' => $id]
        );
    }

    /** @return array{string, array<string,mixed>} */
    private function buildFilterQuery(string $base, string $search, string $householdId): array
    {
        $conditions = [];
        $params     = [];

        if ($search !== '') {
            $conditions[] = 'hn.name LIKE :s';
            $params['s']  = '%' . $search . '%';
        }

        if ($householdId !== '') {
            $conditions[]      = 'hn.household_id = :hid';
            $params['hid']     = $householdId;
        }

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

        return [$sql, $params];
    }
}