<?php

declare(strict_types=1);

namespace App\Models;

class UnitMeasure extends BaseModel
{
    protected string $table = 'unidades_medida';

    public function listByCompany(int $companyId, bool $onlyActive = false): array
    {
        $sql = 'SELECT
                    id,
                    empresa_id,
                    nombre,
                    abreviatura,
                    activo,
                    created_at,
                    updated_at
                FROM unidades_medida
                WHERE empresa_id = :empresa_id';

        if ($onlyActive) {
            $sql .= ' AND activo = 1';
        }

        $sql .= ' ORDER BY nombre ASC, id DESC';

        $stmt = $this->db()->prepare($sql);
        $stmt->execute([':empresa_id' => $companyId]);

        return $stmt->fetchAll() ?: [];
    }

    public function existsByNameOrAbbreviation(int $companyId, string $name, string $abbreviation, ?int $excludeId = null): bool
    {
        $sql = 'SELECT 1
                FROM unidades_medida
                WHERE empresa_id = :empresa_id
                  AND (LOWER(nombre) = :nombre OR LOWER(abreviatura) = :abreviatura)';

        $params = [
            ':empresa_id' => $companyId,
            ':nombre' => strtolower(trim($name)),
            ':abreviatura' => strtolower(trim($abbreviation)),
        ];

        if ($excludeId !== null && $excludeId > 0) {
            $sql .= ' AND id <> :exclude_id';
            $params[':exclude_id'] = $excludeId;
        }

        $sql .= ' LIMIT 1';

        $stmt = $this->db()->prepare($sql);
        $stmt->execute($params);

        return (bool) $stmt->fetchColumn();
    }

    public function countProductsUsing(int $companyId, int $unitId): int
    {
        $stmt = $this->db()->prepare(
            'SELECT COUNT(*)
             FROM productos
             WHERE empresa_id = :empresa_id
               AND unidad_medida_id = :unidad_medida_id'
        );
        $stmt->execute([
            ':empresa_id' => $companyId,
            ':unidad_medida_id' => $unitId,
        ]);

        return (int) $stmt->fetchColumn();
    }
}
