<?php
declare(strict_types=1);

namespace App\Repositories;

use App\Core\Repository;

final class LocationRepository extends Repository
{
    public function allLocations(): array
    {
        return $this->all(
            "SELECT l.*,
                    (SELECT COUNT(*) FROM items i WHERE i.location_id = l.id AND i.is_active = 1) AS items_count
             FROM item_locations l
             ORDER BY l.name ASC, l.id DESC"
        );
    }

    public function find(int $id): ?array
    {
        return $this->one(
            "SELECT l.*,
                    (SELECT COUNT(*) FROM items i WHERE i.location_id = l.id AND i.is_active = 1) AS items_count
             FROM item_locations l
             WHERE l.id = ?
             LIMIT 1",
            [$id]
        );
    }

    public function create(array $data): int
    {
        return $this->insert(
            "INSERT INTO item_locations (name, is_active, created_at, updated_at)
             VALUES (?, ?, NOW(), NOW())",
            [
                trim((string)($data['name'] ?? '')),
                !empty($data['is_active']) ? 1 : 0,
            ]
        );
    }

    public function updateLocation(int $id, array $data): int
    {
        return $this->exec(
            "UPDATE item_locations
             SET name = ?, is_active = ?, updated_at = NOW()
             WHERE id = ?",
            [
                trim((string)($data['name'] ?? '')),
                !empty($data['is_active']) ? 1 : 0,
                $id,
            ]
        );
    }

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

    public function hasItems(int $id): bool
    {
        $row = $this->one(
            "SELECT COUNT(*) AS c
             FROM items
             WHERE location_id = ? AND is_active = 1",
            [$id]
        );

        return (int)($row['c'] ?? 0) > 0;
    }
}
