<?php
declare(strict_types=1);

namespace App\Models;

final class Equipment extends BaseModel
{
    protected static string $table = 'equipment';

    public static function paginate(?int $accountId, string $status, string $q, int $page, int $perPage): array
    {
        $where = 'e.tenant_id = :tid';
        $params = ['tid' => static::tenantId()];
        if ($status === 'active' || $status === 'retired') {
            $where .= ' AND e.status = :st';
            $params['st'] = $status;
        }
        if ($accountId) {
            $where .= ' AND e.account_id = :aid';
            $params['aid'] = $accountId;
        }
        if ($q !== '') {
            $where .= ' AND (e.name LIKE :q OR e.model LIKE :q2 OR e.serial_no LIKE :q3 OR a.name LIKE :q4 OR e.location LIKE :q5)';
            $like = '%' . $q . '%';
            $params['q'] = $like;
            $params['q2'] = $like;
            $params['q3'] = $like;
            $params['q4'] = $like;
            $params['q5'] = $like;
        }
        $total = (int) static::db()->fetchColumn("SELECT COUNT(*) FROM equipment e INNER JOIN accounts a ON a.id = e.account_id WHERE $where", $params);
        $offset = ($page - 1) * $perPage;
        $rows = static::db()->fetchAll(
            "SELECT e.*, a.name AS account_name
             FROM equipment e
             INNER JOIN accounts a ON a.id = e.account_id
             WHERE $where
             ORDER BY a.name, e.name
             LIMIT $perPage OFFSET $offset",
            $params
        );
        return ['rows' => $rows, 'total' => $total];
    }

    public static function forAccount(int $accountId): array
    {
        return static::db()->fetchAll(
            'SELECT * FROM equipment WHERE tenant_id = :tid AND account_id = :aid ORDER BY name',
            ['tid' => static::tenantId(), 'aid' => $accountId]
        );
    }

    public static function counts(): array
    {
        $rows = static::db()->fetchAll(
            'SELECT status, COUNT(*) n FROM equipment WHERE tenant_id = :tid GROUP BY status',
            ['tid' => static::tenantId()]
        );
        $out = ['all' => 0, 'active' => 0, 'retired' => 0, 'sites' => 0];
        foreach ($rows as $r) {
            $out[(string) $r['status']] = (int) $r['n'];
            $out['all'] += (int) $r['n'];
        }
        $out['sites'] = (int) static::db()->fetchColumn(
            'SELECT COUNT(DISTINCT account_id) FROM equipment WHERE tenant_id = :tid',
            ['tid' => static::tenantId()]
        );
        return $out;
    }
}
