<?php

namespace Myvetshop\Module\Clinique\Adapter\Repository;

use PrestaShop\PrestaShop\Adapter\LegacyContext;

class StoreRepository
{
    private \Db $db;

    private LegacyContext $legacyContext;

    public function __construct(
        \Db $db,
        LegacyContext $legacyContext
    ) {
        $this->db = $db;
        $this->legacyContext = $legacyContext;
    }

    /**
     * @return list<array{
     *     id_store: int,
     *     id_country: int,
     *     id_state: int,
     *     city: string,
     *     postcode: string,
     *     latitude: float,
     *     longitude: float,
     *     phone: string,
     *     fax: string,
     *     email: string,
     *     active: int,
     *     website: string,
     *     date_add: string,
     *     date_upd: string,
     *     distance: float,
     *     country: string,
     *     state: string,
     *     code_privilege: string
     * }>
     */
    public function findAll(
        int $start = 0,
        int $limit = 20
    ): array {
        $context = $this->legacyContext->getContext();

        $query = '
                SELECT s.*, sl.*, cl.name country, st.iso_code state, cl.id_country, mc.code_privilege id_country
                FROM ' . _DB_PREFIX_ . 'store s
                ' . \Shop::addSqlAssociation('store', 's') . '
                LEFT JOIN ' . _DB_PREFIX_ . 'country_lang cl ON (cl.id_country = s.id_country)
                LEFT JOIN ' . _DB_PREFIX_ . 'state st ON (st.id_state = s.id_state)
                LEFT JOIN ' . _DB_PREFIX_ . 'store_lang sl ON (sl.id_store = s.id_store AND sl.id_lang = ' . (int) $context->language->id . ')
                INNER JOIN ' . _DB_PREFIX_ . 'myvetshop_clinique mc ON (mc.id_store = s.id_store)
                WHERE s.active = 1 AND
                cl.id_lang = ' . (int) $context->language->id . '
                ORDER BY id_store ASC
                LIMIT ' . $start . ', ' . $limit;

        $stores = $this->db->executeS($query);

        \assert(\is_array($stores));

        return $stores;
    }

    /**
     * @return list<array{
     *     id_store: int,
     *     id_country: int,
     *     id_state: int,
     *     city: string,
     *     postcode: string,
     *     latitude: float,
     *     longitude: float,
     *     phone: string,
     *     fax: string,
     *     email: string,
     *     active: int,
     *     website: string,
     *     date_add: string,
     *     date_upd: string,
     *     distance: float,
     *     country: string,
     *     state: string,
     *     code_privilege: string
     * }>
     */
    public function findByDistance(
        float $latitude,
        float $longitude,
        ?int $distance = null,
        int $start = 0,
        int $limit = 20
    ): array {
        $context = $this->legacyContext->getContext();

        $query = '
                SELECT s.*, sl.*, cl.name country, st.iso_code state,
                (6371 * acos(
                        cos(radians(' . $latitude . '))
                        * cos(radians(latitude))
                        * cos(radians(longitude) - radians(' . $longitude . '))
                        + sin(radians(' . $latitude . '))
                        * sin(radians(latitude))
                    )
                ) distance,
                cl.id_country id_country,
                mc.code_privilege code_privilege
                FROM ' . _DB_PREFIX_ . 'store s
                ' . \Shop::addSqlAssociation('store', 's') . '
                LEFT JOIN ' . _DB_PREFIX_ . 'country_lang cl ON (cl.id_country = s.id_country)
                LEFT JOIN ' . _DB_PREFIX_ . 'state st ON (st.id_state = s.id_state)
                LEFT JOIN ' . _DB_PREFIX_ . 'store_lang sl ON (sl.id_store = s.id_store AND sl.id_lang = ' . (int) $context->language->id . ')
                INNER JOIN ' . _DB_PREFIX_ . 'myvetshop_clinique mc ON (mc.id_store = s.id_store)
                WHERE s.active = 1 AND
                cl.id_lang = ' . (int) $context->language->id;

        if (null !== $distance) {
            $query .= '
                HAVING distance < ' . $distance;
        }
        $query .= '
                ORDER BY distance ASC
                LIMIT ' . $start . ', ' . $limit;

        $stores = $this->db->executeS($query);

        \assert(\is_array($stores));

        return $stores;
    }

    /**
     * @return list<array{
     *     id_store: int,
     *     id_country: int,
     *     id_state: int,
     *     city: string,
     *     postcode: string,
     *     latitude: float,
     *     longitude: float,
     *     phone: string,
     *     fax: string,
     *     email: string,
     *     active: int,
     *     website: string,
     *     date_add: string,
     *     date_upd: string,
     *     distance: float,
     *     country: string,
     *     state: string,
     *     code_privilege: string
     * }>
     */
    public function search(
        string $searQuery,
        int $start = 0,
        int $limit = 20
    ): array {
        $context = $this->legacyContext->getContext();

        $query = '
                SELECT s.*, sl.*, cl.name country, st.iso_code state,
                cl.id_country id_country,
                mc.code_privilege code_privilege
                FROM ' . _DB_PREFIX_ . 'store s
                ' . \Shop::addSqlAssociation('store', 's') . '
                LEFT JOIN ' . _DB_PREFIX_ . 'country_lang cl ON (cl.id_country = s.id_country)
                LEFT JOIN ' . _DB_PREFIX_ . 'state st ON (st.id_state = s.id_state)
                LEFT JOIN ' . _DB_PREFIX_ . 'store_lang sl ON (sl.id_store = s.id_store AND sl.id_lang = ' . (int) $context->language->id . ')
                INNER JOIN ' . _DB_PREFIX_ . 'myvetshop_clinique mc ON (mc.id_store = s.id_store)
                WHERE s.active = 1 AND
                cl.id_lang = ' . (int) $context->language->id . '
                AND (sl.name LIKE "%' . $this->db->escape($searQuery) . '%" OR mc.code_privilege LIKE "%' . $this->db->escape($searQuery) . '%")
                ORDER BY s.date_add DESC
                LIMIT ' . $start . ', ' . $limit;

        $stores = $this->db->executeS($query);

        \assert(\is_array($stores));

        return $stores;
    }
}
