<?php

declare(strict_types=1);

namespace App\Models;

class Company extends BaseModel
{
    protected string $table = 'empresas';
    protected ?string $tenantColumn = null;
    private static ?array $cachedCityOptionalColumns = null;

    public function countries(): array
    {
        $stmt = $this->db()->query(
            'SELECT id, nombre, codigo_iso2, codigo_iso3
             FROM paises
             ORDER BY nombre ASC'
        );

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

    public function currencies(): array
    {
        $stmt = $this->db()->query(
            'SELECT codigo, nombre, simbolo, decimales
             FROM monedas
             ORDER BY nombre ASC'
        );

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

    public function cities(): array
    {
        $optionalColumns = $this->optionalCityColumns();
        $optionalSelect = '';
        if (isset($optionalColumns['departamento'])) {
            $optionalSelect .= ', c.departamento';
        }
        if (isset($optionalColumns['codigo_dane'])) {
            $optionalSelect .= ', c.codigo_dane';
        }

        $stmt = $this->db()->query(
            'SELECT c.id, c.pais_id, c.nombre' . $optionalSelect . ', p.nombre AS pais
             FROM ciudades c
             INNER JOIN paises p ON p.id = c.pais_id
             ORDER BY p.nombre ASC, c.nombre ASC'
        );

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

    private function optionalCityColumns(): array
    {
        if (is_array(self::$cachedCityOptionalColumns)) {
            return self::$cachedCityOptionalColumns;
        }

        $stmt = $this->db()->query(
            'SELECT column_name
             FROM information_schema.columns
             WHERE table_schema = DATABASE()
               AND table_name = "ciudades"
               AND column_name IN ("departamento", "codigo_dane")'
        );

        $columns = [];
        foreach ($stmt->fetchAll() ?: [] as $row) {
            $column = (string) ($row['column_name'] ?? '');
            if ($column === '') {
                continue;
            }
            $columns[$column] = true;
        }

        self::$cachedCityOptionalColumns = $columns;

        return self::$cachedCityOptionalColumns;
    }

    public function cityBelongsToCountry(int $cityId, int $countryId): bool
    {
        $stmt = $this->db()->prepare(
            'SELECT 1
             FROM ciudades
             WHERE id = :city_id
               AND pais_id = :country_id
             LIMIT 1'
        );
        $stmt->execute([
            ':city_id' => $cityId,
            ':country_id' => $countryId,
        ]);

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

    public function currencyExists(string $code): bool
    {
        $stmt = $this->db()->prepare(
            'SELECT 1
             FROM monedas
             WHERE codigo = :code
             LIMIT 1'
        );
        $stmt->execute([':code' => strtoupper(trim($code))]);

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