<?php
declare(strict_types=1);

namespace App\Model\Table;

use Cake\ORM\Table;
use Cake\Datasource\ConnectionManager;

class ShippingRatesTable extends Table
{
	public function initialize(array $config): void
	{
		parent::initialize($config);

		$this->setTable('shipping_rate');
		$this->setPrimaryKey('id');

		$this->belongsTo('Country', [
			'foreignKey' => 'country_id',
			'dependent' => true
		]);
	}

	public function getDetails(int $id): ?array
	{
		$result = $this->find()
			->where(['ShippingRate.id' => $id])
			->contain(['Country'])
			->first();

		return $result ? $result->toArray() : null;
	}

	public function getShippingRateAgainstCountry(int $country_id): ?array
	{
		$result = $this->find()
			->where(['ShippingRate.country_id' => $country_id])
			->contain(['Country'])
			->first();

		return $result ? $result->toArray() : null;
	}

	public function getCountryAgainstShortName(string $name): ?array
	{
		$result = $this->find()
			->matching('Country', function ($q) use ($name) {
				return $q->where(['Country.short_name' => $name]);
			})
			->first();

		return $result ? $result->toArray() : null;
	}

	public function getCountriesAgainstKeyword(string $keyword): array
	{
		$conn = ConnectionManager::get('default');
		$stmt = $conn->execute("
			SELECT id, name, 'country' as type FROM countries WHERE name LIKE :keyword 
			UNION
			SELECT id, name, 'city' as type FROM cities WHERE name LIKE :keyword 
			UNION
			SELECT id, name, 'state' as type FROM states WHERE name LIKE :keyword 
			LIMIT 5
		", ['keyword' => "$keyword%"]);

		return $stmt->fetchAll('assoc');
	}

	public function getAll(): array
	{
		return $this->find()
			->contain(['Country'])
			->order(['Country.name' => 'ASC'])
			->toArray();
	}

	public function setDefaultToZero(): void
	{
		$this->getAssociation('Country')->getTarget()->updateAll(['default' => 0], []);
	}
}
?>
