<?php

declare(strict_types=1);

use Myvetshop\Module\Clinique\Builder\ShippingBuilder;

/**
 * Created by Aurélien RICHAUD (16/01/2017 16:24)
 */
class Clinique extends ObjectModel
{
    public const TABLE = 'myvetshop_clinique';

    public const CARRIER_DOMICILE = 'domicile';
    public const CARRIER_CLINIQUE = 'clinique';
    public const CARRIER_INCONNU = 'inconnu';

    /**
     * @var array
     */
    protected static $reductionsByCat
        = [
            // Alimentation diététique
            33 => 0.030,
            25 => 0.030,
            30 => 0.030,
            // Alimentation médicalisée
            620 => 0.030,
            621 => 0.030,
            // Alimentation physiologique
            27 => 0.100,
            32 => 0.100,
            // Alimentation premium
            615 => 0.100,
            617 => 0.100,
        ];

    protected $cacheEmployee;

    /**
     * @var CliniqueHolidays|false|null
     */
    protected $cacheCurrentHoliday = false;

    /**
     * @var int
     */
    public $id_myvetshop_clinique;

    /**
     * @var int
     */
    public $id_sso;

    /**
     * @var int
     */
    public $id_employee;

    /**
     * @var int
     */
    public $id_carrier;

    /**
     * @var int
     */
    public $id_carrier_home;

    /**
     * @var int
     */
    public $id_group;

    /**
     * @var int
     */
    public $id_group_rural;

    /**
     * @var int
     */
    public $id_store;

    /**
     * @var int
     */
    public $id_theme;

    /**
     * @var string
     */
    public $name;

    /**
     * @var string
     */
    public $code_privilege;

    /**
     * @var string
     */
    public $centrale;

    /**
     * @var int
     */
    public $id_warehouse;

    /**
     * @var string
     */
    public $code_client;

    /**
     * @var string
     */
    public $mdp;

    /**
     * @var string
     */
    public $code_filiere;

    /**
     * @var string
     */
    public $theme;

    /**
     * @var bool
     */
    public $masquer_logo;

    /**
     * @var string
     */
    public $email_original;

    /**
     * @var string
     */
    public $jours_livraison;

    /**
     * @var string
     */
    public $url_rendez_vous;

    /**
     * @var string|null
     */
    public $message;

    /**
     * @var bool
     */
    public $rurale;

    /**
     * @var string
     */
    public $vetoAprenom;

    /**
     * @var string
     */
    public $vetoAnom;

    /**
     * @var string
     */
    public $vetoAurlrdv;

    /**
     * @var string
     */
    public $vetoBprenom;

    /**
     * @var string
     */
    public $vetoBnom;

    /**
     * @var string
     */
    public $vetoBurlrdv;

    /**
     * @var string
     */
    public $vetoCprenom;

    /**
     * @var string
     */
    public $vetoCnom;

    /**
     * @var string
     */
    public $vetoCurlrdv;

    /**
     * @var string
     */
    public $vetoDprenom;

    /**
     * @var string
     */
    public $vetoDnom;

    /**
     * @var string
     */
    public $vetoDurlrdv;

    /**
     * @var string
     */
    public $vetoEprenom;

    /**
     * @var string
     */
    public $vetoEnom;

    /**
     * @var string
     */
    public $vetoEurlrdv;

    /**
     * @var bool
     */
    public $deleted;

    /**
     * @var bool
     */
    public $don;

    /**
     * @see ObjectModel::$definition
     */
    public static $definition = [
        'table' => self::TABLE,
        'primary' => 'id_myvetshop_clinique',
        'multilang' => false,
        'multilang_shop' => false,
        'fields' => [
            // ID Externe
            'id_sso' => ['type' => self::TYPE_INT],

            // Clés étrangères
            'id_employee' => ['type' => self::TYPE_INT],
            'id_carrier' => ['type' => self::TYPE_INT],
            'id_carrier_home' => ['type' => self::TYPE_INT],
            'id_group' => ['type' => self::TYPE_INT],
            'id_group_rural' => ['type' => self::TYPE_INT],
            'id_store' => ['type' => self::TYPE_INT],
            'id_theme' => ['type' => self::TYPE_INT],

            // Informations supplémentaires
            'code_privilege' => ['type' => self::TYPE_STRING, 'required' => true, 'size' => 10],
            'centrale' => ['type' => self::TYPE_STRING, 'required' => true, 'size' => 10],
            'id_warehouse' => ['type' => self::TYPE_INT, 'required' => true],
            'code_client' => ['type' => self::TYPE_STRING, 'required' => false, 'size' => 10],
            'mdp' => ['type' => self::TYPE_STRING, 'required' => false, 'size' => 10],
            'name' => ['type' => self::TYPE_STRING, 'required' => true, 'size' => 255],
            'code_filiere' => ['type' => self::TYPE_STRING, 'required' => false, 'size' => 10],

            // Thème
            'theme' => ['type' => self::TYPE_STRING, 'required' => false, 'size' => 10],
            'masquer_logo' => ['type' => self::TYPE_BOOL, 'validate' => 'isBool', 'required' => false],

            // E-Mail pour le contact
            'email_original' => ['type' => self::TYPE_STRING, 'required' => false],

            // Informations concernant la livraison
            'jours_livraison' => ['type' => self::TYPE_STRING, 'required' => false, 'size' => 55],

            // URL pour la prise de rendez-vous en ligne
            'url_rendez_vous' => ['type' => self::TYPE_STRING, 'required' => false, 'size' => 250],
            'message' => ['type' => self::TYPE_HTML, 'required' => false, 'validate' => 'isCleanHtml', 'size' => 3999999999999],
            'rurale' => ['type' => self::TYPE_BOOL, 'validate' => 'isBool', 'required' => false],

            // Flag pour la suppression
            'deleted' => ['type' => self::TYPE_BOOL, 'validate' => 'isBool', 'required' => false],

            // Vétérinaire 1
            'vetoAprenom' => ['type' => self::TYPE_STRING, 'required' => false, 'size' => 55],
            'vetoAnom' => ['type' => self::TYPE_STRING, 'required' => false, 'size' => 55],
            'vetoAurlrdv' => ['type' => self::TYPE_STRING, 'required' => false, 'size' => 100],

            // Vétérinaire 2
            'vetoBprenom' => ['type' => self::TYPE_STRING, 'required' => false, 'size' => 55],
            'vetoBnom' => ['type' => self::TYPE_STRING, 'required' => false, 'size' => 55],
            'vetoBurlrdv' => ['type' => self::TYPE_STRING, 'required' => false, 'size' => 100],

            // Vétérinaire 3
            'vetoCprenom' => ['type' => self::TYPE_STRING, 'required' => false, 'size' => 55],
            'vetoCnom' => ['type' => self::TYPE_STRING, 'required' => false, 'size' => 55],
            'vetoCurlrdv' => ['type' => self::TYPE_STRING, 'required' => false, 'size' => 100],

            // Vétérinaire 4
            'vetoDprenom' => ['type' => self::TYPE_STRING, 'required' => false, 'size' => 55],
            'vetoDnom' => ['type' => self::TYPE_STRING, 'required' => false, 'size' => 55],
            'vetoDurlrdv' => ['type' => self::TYPE_STRING, 'required' => false, 'size' => 100],

            // Vétérinaire 5
            'vetoEprenom' => ['type' => self::TYPE_STRING, 'required' => false, 'size' => 55],
            'vetoEnom' => ['type' => self::TYPE_STRING, 'required' => false, 'size' => 55],
            'vetoEurlrdv' => ['type' => self::TYPE_STRING, 'required' => false, 'size' => 100],

            // Activation / désactivation de la possibilité de faire un don
            'don' => ['type' => self::TYPE_BOOL, 'validate' => 'isBool', 'required' => false],
        ],
    ];

    public function __construct($id = null, $id_lang = null, $id_shop = null)
    {
        parent::__construct($id, $id_lang, $id_shop);

        if (Validate::isLoadedObject($this)) {
            $carrier = $this->getCarrierDefault();

            //On active/désactive le transporteur "Livraison en clinique" en fonction des vacances de la clinique
            if ($this->isOnHolidays() && $carrier->active) {
                $carrier->active = false;
                $carrier->save();
            }

            if (!$this->isOnHolidays() && !$carrier->active) {
                $carrier->active = true;
                $carrier->save();
            }
        }
    }

    public function willBeOnHolidays(): bool
    {
        return !$this->isOnHolidays() && CliniqueHolidays::hasCliniqueOnHolidaysSoon((int) $this->id);
    }

    public function isOnHolidays(): bool
    {
        return CliniqueHolidays::hasCliniqueOnHolidays((int) $this->id);
    }

    public function getCurrentHoliday(): ?CliniqueHolidays
    {
        if (false === $this->cacheCurrentHoliday) {
            $this->cacheCurrentHoliday = CliniqueHolidays::getCurrentHolliday((int) $this->id);
        }

        return $this->cacheCurrentHoliday;
    }

    public function getHolidayMessage(): ?string
    {
        return CliniqueHolidays::getCliniqueMessage((int) $this->id);
    }

    public function isCliniqueTest(): bool
    {
        return in_array($this->code_privilege, self::getCodeReserved());
    }

    public function getCarrierDefault(): Carrier
    {
        return new Carrier($this->id_carrier);
    }

    public function getCarrierHome(): Carrier
    {
        return new Carrier($this->id_carrier_home);
    }

    public function getGroupDefault(): Group
    {
        return new Group($this->id_group);
    }

    public function getStore($langId = null): Store
    {
        return new Store($this->id_store, $langId);
    }

    public function getGroupRural(): ?Group
    {
        return $this->rurale ? new Group($this->id_group_rural) : null;
    }

    /**
     * @return MyvetshopVeterinaire[]
     */
    public function getVeterinaires(): array
    {
        return MyvetshopVeterinaire::getByCliniqueId((int) $this->id);
    }

    public function getShipping(): MyvetshopCliniqueShipping
    {
        return MyvetshopCliniqueShipping::getByCliniqueId((int) $this->id);
    }

    public function hasTheme(): bool
    {
        return $this->id_theme > 0 && !is_null($this->getTheme());
    }

    public function getTheme(): ?MyvetshopTheme
    {
        return $this->id_theme ? new MyvetshopTheme($this->id_theme) : null;
    }

    public static function isCodeReserved(string $code): bool
    {
        return in_array(strtoupper($code), self::getCodeReserved());
    }

    public static function getCodeReserved(): array
    {
        return ['AAA111', 'VET123'];
    }

    /**
     * @return Clinique[]
     *
     * @throws PrestaShopDatabaseException
     * @throws PrestaShopException
     */
    public static function getAll(): array
    {
        $query = 'SELECT id_myvetshop_clinique FROM ' . _DB_PREFIX_ . Clinique::TABLE;
        $list = array_column(Db::getInstance()->executeS($query), 'id_myvetshop_clinique');

        return array_map(function ($cliniqueId) {
            return new self($cliniqueId);
        }, $list);
    }

    public function getEmployee(): Employee
    {
        return new Employee($this->id_employee);
    }

    protected function preSave(): void
    {
        $this->code_privilege = strtoupper($this->code_privilege);

        ///////////////////////////////////////////////////
        /// Employés
        ///////////////////////////////////////////////////
        $this->updateEmployee();

        ///////////////////////////////////////////////////
        /// Groupe par défaut
        ///////////////////////////////////////////////////
        $this->updateGroupDefault();

        ///////////////////////////////////////////////////
        /// Groupe rural
        ///////////////////////////////////////////////////
        $this->updateGroupRural();

        ///////////////////////////////////////////////////
        /// Transporteur par défaut
        ///////////////////////////////////////////////////
        $this->updateCarrierDefault();

        ///////////////////////////////////////////////////
        /// Transporteur à domicile
        ///////////////////////////////////////////////////
        $this->updateCarrierHome();

        // Supprime les associations de groupes inutiles
        Db::getInstance()->execute('DELETE FROM `' . _DB_PREFIX_ . 'carrier_group` WHERE `id_group` = ' . $this->id_group . ' AND id_carrier NOT IN (' . $this->id_carrier . ', ' . $this->id_carrier_home . ')');

        ///////////////////////////////////////////////////
        /// Magasin
        ///////////////////////////////////////////////////
        $this->updateStore();

        //Mise à jour des informations du transporteur par défaut
        $carrier = new Carrier($this->id_carrier);
        $carrier->name = $this->name;

        foreach (Language::getLanguages() as $language) {
            $carrier->delay[$language['id_lang']] = ShippingBuilder::getShippingText($this);
        }

        $carrier->save();

        // On réassocie correctement les groupes
        $this->resetCarrierDefaultGroups();
        $this->resetCarrierHomeGroups();

        //On recharge les entrepôts
        $this->resetWarehouses();
    }

    /**
     * @param bool $auto_date
     * @param bool $null_values
     *
     * @return bool
     *
     * @throws PrestaShopDatabaseException
     * @throws PrestaShopException
     */
    public function add($auto_date = true, $null_values = false)
    {
        if (!is_null(self::getByCodePrivilege($this->code_privilege))) {
            throw new Exception('Le code privilège est déjà utilisé pour une autre clinique.');
        }

        $this->preSave();

        return parent::add($auto_date, $null_values);
    }

    public function update($null_values = false)
    {
        $clinique = self::getByCodePrivilege($this->code_privilege);

        if (!is_null($clinique) && $clinique->id != $this->id) {
            throw new Exception('Le code privilège est déjà utilisé pour une autre clinique.');
        }

        $this->preSave();

        return parent::update($null_values); // TODO: Change the autogenerated stub
    }

    /**
     * Récupère la liste des commandes de la clinique (par défaut : les 10 dernières)
     *
     * @param int $limit
     * @param int $start
     *
     * @return Order[]
     *
     * @throws PrestaShopDatabaseException
     * @throws PrestaShopException
     */
    public function getOrders($limit = 10, $start = 0)
    {
        $db = Db::getInstance(false);

        $query
            = 'SELECT o.*
                    FROM `' . _DB_PREFIX_ . 'orders` o
                    INNER JOIN `' . _DB_PREFIX_ . 'customer` c ON `o`.`id_customer` = `c`.`id_customer`
                    INNER JOIN `' . _DB_PREFIX_ . 'group_lang` g ON `c`.`id_default_group` = `g`.`id_group`
                    WHERE `g`.`name` = "' . $db->escape($this->code_privilege) . '"
                    ORDER BY `o`.`invoice_date` DESC';
        if ($limit) {
            $query .= ' LIMIT ' . ($start ? (int) $start . ', ' : '') . (int) $limit;
        }

        return ObjectModel::hydrateCollection(Order::class, $db->executeS($query));
    }

    public function getOrderCount()
    {
        $db = Db::getInstance(false);

        $query
            = 'SELECT COUNT(o.id_order) AS `count`
                    FROM `' . _DB_PREFIX_ . 'orders` o
                    INNER JOIN `' . _DB_PREFIX_ . 'customer` c ON `o`.`id_customer` = `c`.`id_customer`
                    INNER JOIN `' . _DB_PREFIX_ . 'group_lang` g ON `c`.`id_default_group` = `g`.`id_group`
                    WHERE `g`.`name` = "' . $db->escape($this->code_privilege) . '"';

        $ret = $db->executeS($query);

        return $ret[0]['count'];
    }

    public function hasCustomer(int $customerId): bool
    {
        $customer = new Customer($customerId);

        return in_array($customer->id_default_group, [$this->id_group, $this->id_group_rural]);
    }

    /**
     * Récupère la liste des clients de la clinique
     *
     * @param int $limit
     * @param int $start
     *
     * @return Customer[]
     *
     * @throws PrestaShopDatabaseException
     * @throws PrestaShopException
     */
    public function getCustomers($limit = 10, $start = 0)
    {
        $db = Db::getInstance(false);

        $query
            = 'SELECT c.*
                    FROM `' . _DB_PREFIX_ . 'customer` c
                    INNER JOIN `' . _DB_PREFIX_ . 'group_lang` g ON `c`.`id_default_group` = `g`.`id_group`
                    WHERE `c`.`deleted` = 0 AND `g`.`name` = "' . $db->escape($this->code_privilege) . '"
                    ORDER BY `c`.`date_add` DESC';
        if ($limit) {
            $query .= ' LIMIT ' . ($start ? (int) $start . ', ' : '') . (int) $limit;
        }

        return ObjectModel::hydrateCollection(Customer::class, $db->executeS($query));
    }

    public function getCustomerCount()
    {
        $db = Db::getInstance(false);

        $query
            = 'SELECT COUNT(c.id_customer) AS `count`
                    FROM `' . _DB_PREFIX_ . 'customer` c
                    INNER JOIN `' . _DB_PREFIX_ . 'group_lang` g ON `c`.`id_default_group` = `g`.`id_group`
                    WHERE `g`.`name` = "' . $db->escape($this->code_privilege) . '"';

        $ret = $db->executeS($query);

        return $ret[0]['count'];
    }

    /**
     * @return int[]|null
     */
    public function getAccessoireCategories()
    {
        $db = Db::getInstance();
        $centraleCatName = '';

        switch ($this->centrale) {
            case 'alcyon':
                $centraleCatName = 'Alcyon';
                break;

            case 'centravet':
                $centraleCatName = 'CentraVet';
                break;

            case 'coveto':
                $centraleCatName = 'CoVeto';
                break;

            case 'hippocampe':
                $centraleCatName = 'Hippocampe';
                break;

            default:
                return null;
        }

        $idCatCentrale = $db->getValue('SELECT `id_category` FROM `ps_category_lang` WHERE `name` = "' . $db->escape($centraleCatName) . '"');

        if (!$idCatCentrale) {
            return null;
        }

        $idCatAccessoires = $db->getValue(
            'SELECT `c`.`id_category`'
            . ' FROM `ps_category` c'
            . ' INNER JOIN `ps_category_lang` cl ON `cl`.`id_category` = `c`.`id_category`'
            . ' WHERE `c`.`id_parent` = ' . (int) $idCatCentrale . ' AND `cl`.`name` = "' . $db->escape('Accessoires') . '"'
        );
        if (!$idCatAccessoires) {
            return null;
        }

        $ret = [(int) $idCatAccessoires];

        foreach ($db->executeS('SELECT c.* FROM `ps_category` c WHERE id_parent = ' . (int) $idCatAccessoires) as $row) {
            $ret[] = (int) $row['id_category'];
        }

        return $ret;
    }

    public function setPriceCarrierHome(float $price): void
    {
        Db::getInstance()->execute('UPDATE `' . _DB_PREFIX_ . 'delivery` SET `price` = ' . $price . ' WHERE `id_carrier` = ' . (int) $this->id_carrier_home);
    }

    public static function getByCliniqueId(int $cliniqueId): ?self
    {
        $query = (new DbQuery())
            ->select(self::$definition['primary'])
            ->from(self::TABLE)
            ->where(self::$definition['primary'] . '=' . $cliniqueId);

        $cliniqueId = Db::getInstance()->getValue($query);

        return $cliniqueId ? new Clinique($cliniqueId) : null;
    }

    public static function get(): ?self
    {
        $customer = Context::getContext()->customer;

        return $customer ? Clinique::getCliniqueByGroupId((int) $customer->id_default_group) : null;
    }

    public static function getCliniqueByEmployeeId(int $employeeId): ?Clinique
    {
        $query = (new DbQuery())
            ->select(self::$definition['primary'])
            ->from(self::TABLE)
            ->where('id_employee=' . $employeeId);

        $cliniqueId = Db::getInstance()->getValue($query);

        return $cliniqueId ? new Clinique($cliniqueId) : null;
    }

    /**
     * @param $id_employee
     *
     * @return int|null
     *
     * @throws PrestaShopDatabaseException
     *
     * @deprecated Please use Clinique::getCliniqueByEmployeeId instead
     */
    public static function getCliniqueByEmployee($id_employee)
    {
        $db = Db::getInstance(false);

        $cliniques = $db->executeS(
            '
                    SELECT c.*
                    FROM `' . _DB_PREFIX_ . self::TABLE . '` c
                    WHERE `c`.`id_employee` = ' . (int) $id_employee
        );

        if (0 == count($cliniques)) {
            return null;
        }

        return (int) $cliniques[0]['id_myvetshop_clinique'];
    }

    /**
     * @param $id_carrier
     *
     * @return int|null
     *
     * @throws PrestaShopDatabaseException
     */
    public static function getCliniqueIdByCarrier($id_carrier)
    {
        $db = Db::getInstance(false);

        $cliniques = $db->executeS(
            '
                    SELECT c.*
                    FROM `' . _DB_PREFIX_ . self::TABLE . '` c
                    WHERE `c`.`id_carrier` = ' . (int) $id_carrier . ' OR `c`.`id_carrier_home` = ' . (int) $id_carrier
        );

        if (0 == count($cliniques)) {
            return null;
        }

        return (int) $cliniques[0]['id_myvetshop_clinique'];
    }

    public static function getCliniqueByCarrierId(int $carrierId): ?Clinique
    {
        $cliniqueId = self::getCliniqueIdByCarrier($carrierId);

        return !is_null($cliniqueId) ? new Clinique($cliniqueId) : null;
    }

    /**
     * @param int $id_group
     *
     * @return int|null
     *
     * @throws PrestaShopDatabaseException
     */
    public static function getCliniqueIdByGroup($id_group)
    {
        $db = Db::getInstance(false);

        $cliniques = $db->executeS(
            'SELECT c.*
                    FROM `' . _DB_PREFIX_ . self::TABLE . '` c
                    WHERE `c`.`id_group` = ' . (int) $id_group . ' OR `c`.`id_group_rural` = ' . (int) $id_group
        );

        if (0 == count($cliniques)) {
            return null;
        }

        return (int) $cliniques[0]['id_myvetshop_clinique'];
    }

    public static function getCliniqueByGroupId(int $groupId): ?self
    {
        $query = (new DbQuery())
            ->select(self::$definition['primary'])
            ->from(self::$definition['table'])
            ->where('id_group=' . $groupId . ' OR id_group_rural=' . $groupId);

        $cliniqueId = Db::getInstance()->getValue($query);

        return $cliniqueId ? new self($cliniqueId) : null;
    }

    /**
     * @param int $id_sso
     *
     * @return int|null
     *
     * @throws PrestaShopDatabaseException
     */
    public static function getCliniqueByIdSSO($id_sso)
    {
        $db = Db::getInstance(false);

        $cliniques = $db->executeS(
            'SELECT c.*
                    FROM `' . _DB_PREFIX_ . self::TABLE . '` c
                    WHERE `c`.`id_sso` = ' . (int) $id_sso
        );

        if (0 == count($cliniques)) {
            return null;
        }

        return (int) $cliniques[0]['id_myvetshop_clinique'];
    }

    /**
     * @param string $code
     *
     * @return Clinique|null
     *
     * @throws PrestaShopDatabaseException
     * @throws PrestaShopException
     */
    public static function getByCodePrivilege(string $code): ?Clinique
    {
        $codeUpper = strtoupper($code);

        if ('-PRO' === substr($codeUpper, -4)) {
            $codePrivilege = substr($codeUpper, 0, -4);
            $pro = true;
        } else {
            $codePrivilege = $codeUpper;
            $pro = false;
        }

        $query = 'SELECT id_myvetshop_clinique FROM ' . _DB_PREFIX_ . self::TABLE . ' WHERE code_privilege="' . Db::getInstance()->escape(strtoupper($codePrivilege)) . '"';
        if ($pro) {
            $query .= ' AND rurale = 1';
        }
        $cliniqueId = (int) Db::getInstance()->getValue($query);

        $clinique = new self($cliniqueId);

        return Validate::isLoadedObject($clinique) ? $clinique : null;
    }

    /**
     * Détermine si un livreur est "à domicile" ou "en clinique"
     *
     * @param int $id_carrier
     *
     * @return string domicile, clinique ou inconnu
     *
     * @throws PrestaShopDatabaseException
     */
    public static function getCarrierType($id_carrier)
    {
        $clinique_id = self::getCliniqueIdByCarrier($id_carrier);

        if (!$clinique_id) {
            return self::CARRIER_INCONNU;
        }

        $clinique = new Clinique($clinique_id);

        if ($id_carrier == $clinique->id_carrier) {
            return self::CARRIER_CLINIQUE;
        } elseif ($id_carrier == $clinique->id_carrier_home) {
            return self::CARRIER_DOMICILE;
        }

        return self::CARRIER_INCONNU;
    }

    /**
     * @return array
     */
    public static function getReductionsByCat()
    {
        return self::$reductionsByCat;
    }

    public static function install(): bool
    {
        $query = 'CREATE TABLE IF NOT EXISTS `' . _DB_PREFIX_ . static::TABLE . '` (
			  `id_myvetshop_clinique` INT(11) NOT NULL AUTO_INCREMENT,
			  `id_sso` int(11) DEFAULT NULL,
              `id_employee` int(11) DEFAULT NULL,
              `id_carrier` int(11) DEFAULT NULL,
              `id_carrier_home` int(11) DEFAULT NULL,
              `id_group` int(11) DEFAULT NULL,
              `id_group_rural` int(11) DEFAULT NULL,
              `id_store` int(11) DEFAULT NULL,
              `code_privilege` varchar(10) NOT NULL,
              `centrale` varchar(10) NOT NULL,
              `id_warehouse` int(11) DEFAULT NULL,
              `code_client` varchar(10) DEFAULT NULL,
              `mdp` varchar(10) DEFAULT NULL,
              `code_filiere` varchar(10) DEFAULT NULL,
              `theme` varchar(10) DEFAULT NULL,
              `masquer_logo` int(1) DEFAULT 0,
              `email_original` varchar(150) DEFAULT NULL,
              `jours_livraison` varchar(55) DEFAULT NULL,
              `url_rendez_vous` varchar(250) DEFAULT NULL,
              `message` text DEFAULT NULL,
              `rurale` int(1) DEFAULT NULL,
              `vetoAprenom` varchar(55) DEFAULT NULL,
              `vetoAnom` varchar(55) DEFAULT NULL,
              `vetoAurlrdv` varchar(100) DEFAULT NULL,
              `vetoBprenom` varchar(55) DEFAULT NULL,
              `vetoBnom` varchar(55) DEFAULT NULL,
              `vetoBurlrdv` varchar(100) DEFAULT NULL,
              `vetoCprenom` varchar(55) DEFAULT NULL,
              `vetoCnom` varchar(55) DEFAULT NULL,
              `vetoCurlrdv` varchar(100) DEFAULT NULL,
              `vetoDprenom` varchar(55) DEFAULT NULL,
              `vetoDnom` varchar(55) DEFAULT NULL,
              `vetoDurlrdv` varchar(100) DEFAULT NULL,
              `vetoEprenom` varchar(55) DEFAULT NULL,
              `vetoEnom` varchar(55) DEFAULT NULL,
              `vetoEurlrdv` varchar(100) DEFAULT NULL,
              `deleted` int(1) NOT NULL DEFAULT 0,
              `don` tinyint(1) DEFAULT 1,
			  
			   PRIMARY KEY (`id_myvetshop_clinique`),
			   UNIQUE (id_employee),
			   UNIQUE (id_carrier),
			   UNIQUE (id_group),
			   UNIQUE (id_store),
			   UNIQUE (code_privilege)
			);';

        return Db::getInstance()->execute($query);
    }

    protected function resetWarehouses(): void
    {
        Db::getInstance()->execute('DELETE FROM `' . _DB_PREFIX_ . 'warehouse_carrier` WHERE `id_carrier` IN (' . $this->id_carrier . ', ' . $this->id_carrier_home . ') AND `id_warehouse` != ' . $this->id_warehouse);
        Db::getInstance()->execute(
            'INSERT INTO `' . _DB_PREFIX_ . 'warehouse_carrier` (`id_carrier`, `id_warehouse`)'
            . ' VALUES (' . $this->id_carrier . ', ' . $this->id_warehouse . '), '
            . ' (' . $this->id_carrier_home . ', ' . $this->id_warehouse . ') '
            . ' ON DUPLICATE KEY UPDATE `id_warehouse` = ' . $this->id_warehouse
        );
    }

    protected function updateEmployee(): void
    {
        //Si le compte employé n'existe pas, on le créé
        if (!$this->id_employee) {
            $employee = new Employee();
            $employee->id_profile = 5;
            $employee->id_lang = 1;
            $employee->firstname = 'Clinique';
            $employee->lastname = 'vétérinaire';
            $employee->email = $this->code_privilege . '@myvetshop.fr';
            $employee->setWsPasswd($this->code_privilege . '@myvetshop.fr' . rand(99999, 999999));
            $employee->add();

            $this->id_employee = $employee->id;
        }

        //On met à jour le nom et le prénom de l'employee par rapport au nom de la clinique
        $employee = $this->getEmployee();
        $nameArray = explode(' ', $this->name);

        if (1 == count($nameArray)) {
            $nameArray[] = 'Clinique vétérinaire';
        }

        $nameArray = array_filter($nameArray, function ($value) {
            return '' !== $value;
        });

        $lastname = array_pop($nameArray);
        $employee->lastname = $lastname;
        $employee->firstname = implode(' ', $nameArray);
        $employee->save();
    }

    protected function updateGroupDefault(): void
    {
        $languages = Language::getLanguages();

        //On initie le groupe par défaut avec le code privilège
        if (!$this->id_group) {
            $query = 'SELECT id_group FROM ' . _DB_PREFIX_ . 'group_lang WHERE id_lang=' . Context::getContext()->language->id . ' AND name="' . $this->code_privilege . '"';
            $groupId = Db::getInstance()->getValue($query);

            $group = new Group($groupId ?: null);

            foreach ($languages as $language) {
                $group->name[$language['id_lang']] = $this->code_privilege;
            }

            $group->price_display_method = 0;
            $group->add();

            $this->id_group = $group->id;
        }

        //On met à jour le nom du groupe par défaut par rapport au code privilège de la clinique
        $groupDefault = $this->getGroupDefault();

        foreach ($languages as $language) {
            $groupDefault->name[$language['id_lang']] = $this->code_privilege;
        }

        $groupDefault->save();

        //Ajout de la réduction du groupe sur une liste de catégorie
        //@TODO créer une table myvetshop_category_reduction qui va stocker le tableau reductionByCat
        //@TODO ajouter un hook pour modifier le formulaire des catégories et gérer les valeurs
        foreach (static::getReductionsByCat() as $categoryId => $reduction) {
            if (!GroupReduction::doesExist($this->id_group, $categoryId)) {
                $groupeReduction = new GroupReduction();
                $groupeReduction->id_group = $this->id_group;
                $groupeReduction->id_category = $categoryId;
                $groupeReduction->reduction = $reduction;
                $groupeReduction->save();
            }
        }

        $this->allowAccessories((int) $this->id_group);
    }

    protected function updateGroupRural(): void
    {
        $languages = Language::getLanguages();

        $rural_ids = [740];
        $last_selected_ids = [740];

        while (count($last_selected_ids)) {
            $ids = $last_selected_ids;
            $last_selected_ids = [];

            $result = Db::getInstance()->executeS('SELECT c.id_category FROM `' . _DB_PREFIX_ . 'category` c WHERE id_parent IN (' . implode(', ', $ids) . ')');
            if (!is_array($result)) {
                $result = [];
            }

            foreach ($result as $row) {
                $rural_ids[] = (int) $row['id_category'];
                $last_selected_ids[] = (int) $row['id_category'];
            }
        }

        if ($this->rurale) {
            //On créé le groupe rurale si celui-ci n'existe pas
            if (!$this->id_group_rural) {
                $name = $this->code_privilege . '-pro';
                $query = 'SELECT id_group FROM ' . _DB_PREFIX_ . 'group_lang WHERE id_lang=' . Context::getContext()->language->id . ' AND name="' . $name . '"';
                $groupId = Db::getInstance()->getValue($query);

                $group = new Group($groupId ?: null);

                foreach ($languages as $language) {
                    $group->name[$language['id_lang']] = $name;
                }
                // Affichage HT des prix
                $group->price_display_method = 1;
                $group->add();

                $this->id_group_rural = $group->id;
            }

            $this->allowAccessories($this->id_group_rural);

            // Désactive l'accès aux catégories de la rurale pour les groupes qui ne sont pas des tarifs privilège
            Db::getInstance()->execute('DELETE FROM `' . _DB_PREFIX_ . 'category_group` WHERE `id_group` <= 3 AND `id_category` IN (' . implode(', ', $rural_ids) . ')');

            if (!GroupReduction::doesExist($this->id_group_rural, 740)) {
                // Autorise le groupe a accéder à la catégorie
                $query = 'INSERT INTO `' . _DB_PREFIX_ . 'category_group` (`id_category`, `id_group`) VALUES ';
                $query .= implode(
                    ', ',
                    array_map(
                        function ($id_cat) {
                            return '(' . $id_cat . ', ' . $this->id_group_rural . ')';
                        }, $rural_ids
                    )
                );
                $query .= ' ON DUPLICATE KEY UPDATE id_group = ' . $this->id_group_rural;
                Db::getInstance()->execute($query);

                // Mise en place de la réduction
                $groupeReduction = new GroupReduction();
                $groupeReduction->id_group = $this->id_group_rural;
                $groupeReduction->id_category = 740;
                $groupeReduction->reduction = 0.185;
                $groupeReduction->save();

                // Réductions de groupe
                foreach (Clinique::getReductionsByCat() as $id_category => $reduction) {
                    if (!GroupReduction::doesExist($this->id_group_rural, $id_category)) {
                        $groupeReduction = new GroupReduction();
                        $groupeReduction->id_group = $this->id_group_rural;
                        $groupeReduction->id_category = $id_category;
                        $groupeReduction->reduction = $reduction;
                        $groupeReduction->save();
                    }
                }
            }
        } else {
            if (GroupReduction::doesExist($this->id_group_rural, 740)) {
                Db::getInstance()->execute('DELETE FROM `' . _DB_PREFIX_ . 'category_group` WHERE `id_group` = ' . (int) $this->id_group_rural . ' AND `id_category` IN (' . implode(', ', $rural_ids) . ')');
                Db::getInstance()->execute('DELETE FROM `' . _DB_PREFIX_ . 'group_reduction` WHERE `id_group` = ' . (int) $this->id_group_rural . ' AND `id_category` = 740');
            }
        }
    }

    protected function updateCarrierDefault(): void
    {
        $languages = Language::getLanguages();

        if (!$this->id_carrier) {
            $carrier = new Carrier();
            $carrier->name = 'Clinique vétérinaire ' . $this->code_privilege;
            $carrier->external_module_name = 'myvetshopclinique';
            $carrier->active = 1;
            $carrier->domicile = false;
            $carrier->is_module = true;
            $carrier->need_range = true;
            $carrier->shipping_external = true;
            $carrier->is_free = 1;
            $carrier->shipping_method = 1;

            foreach ($languages as $language) {
                $carrier->delay[$language['id_lang']] = ShippingBuilder::getShippingText($this);
            }

            $carrier->add();
            $this->id_carrier = $carrier->id;

            $carrier->setTaxRulesGroup(0);

            // On ré-associe correctement les groupes de la clinique au transporteur
            $this->resetCarrierDefaultGroups();

            $carrier->addZone(1);

            // Ajout d'une image par défaut
            $defaultImage = _PS_SHIP_IMG_DIR_ . 'img-clinique.jpg';

            if (file_exists($defaultImage)) {
                copy(_PS_SHIP_IMG_DIR_ . 'img-clinique.jpg', _PS_SHIP_IMG_DIR_ . $carrier->id . '.jpg');
            }
        }
    }

    protected function updateCarrierHome(): void
    {
        $languages = Language::getLanguages();

        //Si le transporteur de livraison à domicile n'existe pas, on le créé

        if (!$this->id_carrier_home) {
            $carrier = new Carrier();
            $carrier->name = 'Livraison à domicile';
            $carrier->active = 1;
            $carrier->domicile = true;
            $carrier->is_module = true;
            $carrier->need_range = true;
            $carrier->shipping_external = true;
            $carrier->is_free = 0;
            $carrier->external_module_name = 'myvetshopclinique';
            $carrier->shipping_method = 1;
            $carrier->max_weight = 40.0;
            $carrier->range_behavior = 1;

            foreach ($languages as $language) {
                $carrier->delay[$language['id_lang']] = '4 jours';
            }

            $carrier->add();
            $this->id_carrier_home = $carrier->id;

            // Zone + Rule Group
            $carrier->addZone(1);
            $carrier->setTaxRulesGroup(7);

            // On ré-associe correctement les groupes de la clinique au transporteur rural
            $this->resetCarrierHomeGroups();

            // Ajout d'une image par défaut
            $defaultImage = _PS_SHIP_IMG_DIR_ . 'img-domicile.jpg';

            if (file_exists($defaultImage)) {
                copy(_PS_SHIP_IMG_DIR_ . 'img-domicile.jpg', _PS_SHIP_IMG_DIR_ . $carrier->id . '.jpg');
            }

            // Mise à jour des tarifs
            $range = new RangeWeight();

            // On créé un range jusqu'à 40 Kg
            $range->id_carrier = $carrier->id;
            $range->delimiter1 = 0;
            $range->delimiter2 = 40;

            $range->add();

            // On supprime le devilery gratuit créé par défaut
            Db::getInstance()->execute('DELETE FROM `' . _DB_PREFIX_ . 'delivery` WHERE id_range_weight = ' . $range->id);

            // Ajoute le tarif de la configuration ou une par défaut (14 €)
            $price = (int) Configuration::get('MYVETSHOPCLINIQUE_CARRIER_HOME_DEFAULT_PRICE', null, null, null, 14);
            Db::getInstance()->execute('INSERT INTO `' . _DB_PREFIX_ . 'delivery` (id_shop, id_shop_group, id_carrier, id_range_weight, id_zone, price) VALUES (1, 1,' . $carrier->id . ', ' . $range->id . ', 1, ' . $price . ')');
        }
    }

    protected function updateStore(): void
    {
        $languages = Language::getLanguages();

        //On créé enfin une boutique pour la clinique si elle n'existe pas
        if (!$this->id_store) {
            $store = new Store();

            foreach ($languages as $language) {
                $langId = $language['id_lang'];
                $store->name[$langId] = 'Clinique vétérinaire ' . $this->code_privilege;
                $store->address1[$langId] = 'Inconnu';
            }

            $store->city = 'Paris';
            $store->id_country = 8;
            $store->latitude = 0;
            $store->longitude = 0;
            $store->email = $this->code_privilege . '@myvetshop.fr';
            $store->add();

            $this->id_store = $store->id;
        }
    }

    protected function resetCarrierDefaultGroups(): void
    {
        $this->resetCarrierGroups((int) $this->id_carrier);
    }

    protected function resetCarrierHomeGroups(): void
    {
        $this->resetCarrierGroups((int) $this->id_carrier_home);
    }

    protected function resetCarrierGroups(int $carrierId): void
    {
        $carrier = new Carrier($carrierId);

        if (Validate::isLoadedObject($carrier)) {
            $carrierGroups = [$this->id_group];

            if ($this->rurale && $this->id_group_rural) {
                $carrierGroups[] = $this->id_group_rural;
            }

            $carrier->setGroups($carrierGroups);
        }
    }

    protected function allowAccessories(int $groupId): void
    {
        // Catégorie d'accessoires
        $catAccessoires = $this->getAccessoireCategories();

        if ($catAccessoires) {
            foreach ($catAccessoires as $idCat) {
                $count = Db::getInstance()->getValue('SELECT COUNT(*) FROM `' . _DB_PREFIX_ . 'category_group` WHERE `id_category` = ' . (int) $idCat . ' AND id_group = ' . $groupId);

                if (!$count) {
                    Db::getInstance()->execute('INSERT INTO `' . _DB_PREFIX_ . 'category_group` (`id_category`, `id_group`) VALUES (' . (int) $idCat . ', ' . $groupId . ')');
                }
            }

            // Récupère le ID des catégories d'accessoires à retirer
            $otherCats = array_diff([274, 399, 418], $catAccessoires);
            // Supprime les accès aux catégories d'accessoires des autres centrales
            Db::getInstance()->execute('DELETE FROM `' . _DB_PREFIX_ . 'category_group` WHERE `id_group` = ' . $groupId . ' AND id_category IN (' . implode(', ', $otherCats) . ')');

            // Détermine la catégorie accessoire principale, et applique 10% de réduction
            $mainAccessoireCatId = array_values(array_intersect($catAccessoires, [274, 399, 418]));

            if (count($mainAccessoireCatId)) {
                if (!GroupReduction::doesExist($this->id_group, $mainAccessoireCatId[0])) {
                    $groupeReduction = new GroupReduction();
                    $groupeReduction->id_group = $groupId;
                    $groupeReduction->id_category = $mainAccessoireCatId[0];
                    $groupeReduction->reduction = 0.100;
                    $groupeReduction->save();
                }
            }
        }
    }
}
