<?php

declare(strict_types=1);

namespace App\Models;

class Branch extends BaseModel
{
    protected string $table = 'sucursales';

    public function countByCompany(int $companyId): int
    {
        $stmt = $this->db()->prepare(
            'SELECT COUNT(*)
             FROM sucursales
             WHERE empresa_id = :empresa_id'
        );
        $stmt->execute([':empresa_id' => $companyId]);

        return (int) ($stmt->fetchColumn() ?: 0);
    }

    public function currentPlanLimit(int $companyId): ?array
    {
        $stmt = $this->db()->prepare(
            'SELECT
                p.id AS plan_id,
                p.nombre AS plan_nombre,
                p.max_sucursales,
                s.id AS suscripcion_id,
                s.estado AS suscripcion_estado
             FROM suscripciones s
             INNER JOIN planes p ON p.id = s.plan_id
             WHERE s.empresa_id = :empresa_id
             ORDER BY
                CASE s.estado
                    WHEN "activa" THEN 0
                    WHEN "vencida" THEN 1
                    WHEN "morosa" THEN 2
                    ELSE 3
                END,
                s.fecha_vencimiento DESC,
                s.id DESC
             LIMIT 1'
        );
        $stmt->execute([':empresa_id' => $companyId]);
        $row = $stmt->fetch();
        if (!$row) {
            return null;
        }

        $row['max_sucursales'] = max(0, (int) ($row['max_sucursales'] ?? 0));
        return $row;
    }

    public function listByCompany(int $companyId): array
    {
        $stmt = $this->db()->prepare(
            'SELECT
                s.id,
                s.empresa_id,
                s.nombre,
                s.codigo,
                s.telefono,
                s.direccion,
                s.ciudad_id,
                s.activa,
                s.created_at,
                s.updated_at,
                c.nombre AS ciudad,
                p.nombre AS pais,
                (
                    SELECT COUNT(*)
                    FROM usuarios u
                    WHERE u.empresa_id = s.empresa_id
                      AND u.sucursal_id = s.id
                      AND u.activo = 1
                ) AS usuarios_asignados,
                (
                    SELECT COUNT(*)
                    FROM cajas cj
                    WHERE cj.empresa_id = s.empresa_id
                      AND cj.sucursal_id = s.id
                ) AS cajas_registradas
             FROM sucursales s
             LEFT JOIN ciudades c ON c.id = s.ciudad_id
             LEFT JOIN paises p ON p.id = c.pais_id
             WHERE s.empresa_id = :empresa_id
             ORDER BY s.activa DESC, s.nombre ASC'
        );
        $stmt->execute([':empresa_id' => $companyId]);

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

    public function existsCode(int $companyId, string $code, ?int $ignoreId = null): bool
    {
        $code = trim($code);
        if ($code === '') {
            return false;
        }

        $sql = 'SELECT 1
                FROM sucursales
                WHERE empresa_id = :empresa_id
                  AND codigo = :codigo';
        $bindings = [
            ':empresa_id' => $companyId,
            ':codigo' => $code,
        ];

        if ($ignoreId !== null && $ignoreId > 0) {
            $sql .= ' AND id <> :ignore_id';
            $bindings[':ignore_id'] = $ignoreId;
        }

        $sql .= ' LIMIT 1';

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

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

    public function findWithRelations(int $companyId, int $branchId): ?array
    {
        $stmt = $this->db()->prepare(
            'SELECT
                s.*,
                c.nombre AS ciudad,
                p.nombre AS pais
             FROM sucursales s
             LEFT JOIN ciudades c ON c.id = s.ciudad_id
             LEFT JOIN paises p ON p.id = c.pais_id
             WHERE s.empresa_id = :empresa_id
               AND s.id = :sucursal_id
             LIMIT 1'
        );
        $stmt->execute([
            ':empresa_id' => $companyId,
            ':sucursal_id' => $branchId,
        ]);

        $row = $stmt->fetch();
        return $row ?: null;
    }
}
