<?php

/*
 *   $Id$
 *
 *   AbanteCart, Ideal OpenSource Ecommerce Solution
 *   http://www.AbanteCart.com
 *
 *   Copyright © 2011-2026 Belavier Commerce LLC
 *
 *   This source file is subject to Open Software License (OSL 3.0)
 *   License details are bundled with this package in the file LICENSE.txt.
 *   It is also available at this URL:
 *   <http://www.opensource.org/licenses/OSL-3.0>
 *
 *  UPGRADE NOTE:
 *    Do not edit or add to this file if you wish to upgrade AbanteCart to newer
 *    versions in the future. If you wish to customize AbanteCart for your
 *    needs, please refer to http://www.AbanteCart.com for more information.
 */

/** @noinspection PhpMultipleClassDeclarationsInspection */

/** @noinspection PhpUndefinedClassInspection */

use Symfony\Component\Mailer\Exception\TransportExceptionInterface;

if (!defined('DIR_CORE') || !IS_ADMIN) {
    header('Location: static_pages/');
}

class ModelSaleCustomer extends Model
{

    /**
     * @param $data
     *
     * @return int
     * @throws AException
     */
    public function addCustomer($data)
    {
        //encrypt customer data
        $key_sql = '';
        if ($this->dcrypt->active) {
            $data = $this->dcrypt->encrypt_data($data, 'customers');
            $key_sql = ", key_id = '" . (int) $data['key_id'] . "'";
        }
        $salt_key = genToken(8);
        $this->db->query(
            "INSERT INTO " . $this->db->table("customers") . "
            SET store_id = '" . (int) $data['store_id'] . "',
                loginname = '" . $this->db->escape($data['loginname']) . "',
                firstname = '" . $this->db->escape($data['firstname']) . "',
                lastname = '" . $this->db->escape($data['lastname']) . "',
                email = '" . $this->db->escape($data['email']) . "',
                telephone = '" . $this->db->escape($data['telephone']) . "',
                fax = '" . $this->db->escape($data['fax']) . "',
                newsletter = '" . (int) $data['newsletter'] . "',
                customer_group_id = '" . (int) $data['customer_group_id'] . "',
                salt = '" . $this->db->escape($salt_key) . "', 
                password = '" . $this->db->escape(passwordHash($data['password'], $salt_key)) . "',
                status = '" . (int) $data['status'] . "',
                approved = '" . (int) $data['approved'] . "'"
            . $key_sql . ",
                date_added = NOW()"
        );
        $customer_id = $this->db->getLastId();

        //enable notification setting for newsletter via email
        if ($data['newsletter']) {
            $this->saveCustomerNotificationSettings($customer_id, ['newsletter' => ['email' => 1]]);
        }

        $this->editCustomerNotifications($customer_id, $data);
        return $customer_id;
    }

    /**
     * @param int $customer_id
     * @param array $address
     *
     * @return int
     * @throws AException
     */
    public function addAddress($customer_id, $address = [])
    {
        if (!(int) $customer_id || !$address || !is_array($address)) {
            return false;
        }
        //encrypt address data
        $key_sql = '';
        if ($this->dcrypt->active) {
            $address = $this->dcrypt->encrypt_data($address, 'addresses');
            $key_sql = ", key_id = '" . (int) $address['key_id'] . "'";
        }
        $this->db->query(
            "INSERT INTO " . $this->db->table("addresses") . "
            SET customer_id = '" . (int) $customer_id . "',
                firstname = '" . $this->db->escape($address['firstname']) . "',
                lastname = '" . $this->db->escape($address['lastname']) . "',
                company = '" . $this->db->escape($address['company']) . "',
                address_1 = '" . $this->db->escape($address['address_1']) . "',
                address_2 = '" . $this->db->escape($address['address_2']) . "',
                city = '" . $this->db->escape($address['city']) . "',
                postcode = '" . $this->db->escape($address['postcode']) . "',
                country_id = '" . (int) $address['country_id'] . "'"
            . $key_sql . ",
                zone_id = '" . (int) $address['zone_id'] . "'"
        );
        return (int) $this->db->getLastId();
    }

    /**
     * @param int $customer_id
     * @param array $data
     *
     * @throws AException
     */
    public function editCustomer($customer_id, $data)
    {
        //encrypt address data
        $key_sql = '';
        $this->editCustomerNotifications($customer_id, $data);
        if ($this->dcrypt->active) {
            $data = $this->dcrypt->encrypt_data($data, 'customers');
            $key_sql = ", key_id = '" . (int) $data['key_id'] . "'";
        }
        $this->db->query(
            "UPDATE " . $this->db->table("customers") . "
            SET store_id = '" . (int) $data['store_id'] . "',
                loginname = '" . $this->db->escape($data['loginname']) . "',
                firstname = '" . $this->db->escape($data['firstname']) . "',
                lastname = '" . $this->db->escape($data['lastname']) . "',
                email = '" . $this->db->escape($data['email']) . "',
                telephone = '" . $this->db->escape($data['telephone']) . "',
                fax = '" . $this->db->escape($data['fax']) . "',
                newsletter = '" . (int) $data['newsletter'] . "',
                customer_group_id = '" . (int) $data['customer_group_id'] . "',
                status = '" . (int) $data['status'] . "'"
            . $key_sql . ", 
                approved = '" . (int) $data['approved'] . "'
            WHERE customer_id = '" . $customer_id . "'"
        );

        if ($data['password']) {
            $salt_key = genToken(8);
            $this->db->query(
                "UPDATE " . $this->db->table("customers") . "
                SET
                    salt = '" . $this->db->escape($salt_key) . "', 
                    password = '" . $this->db->escape(passwordHash($data['password'], $salt_key)) . "'
                WHERE customer_id = '" . $customer_id . "'"
            );
            //destroy all active sessions
            $customer = new ACustomer($this->registry);
            $customer->deleteActiveSessionsByID($customer_id);
        }

        if (isset($data['newsletter'])) {
            //enable notification setting for newsletter via email
            $this->saveCustomerNotificationSettings(
                $customer_id, ['newsletter' => ['email' => (int) $data['newsletter']]]
            );
        }
    }

    /**
     * @param int $customer_id
     * @param int $address_id
     * @param array $address
     *
     * @return bool
     * @throws AException
     */
    public function editAddress($customer_id, $address_id, $address)
    {
        if (!(int) $customer_id || !(int) $address_id || !$address || !is_array($address)) {
            return false;
        }

        $this->deleteAddress($customer_id, $address_id);
        //encrypt address data
        $key_sql = '';
        if ($this->dcrypt->active) {
            $address = $this->dcrypt->encrypt_data($address, 'addresses');
            $key_sql = ", key_id = '" . (int) $address['key_id'] . "'";
        }
        $this->db->query(
            "INSERT INTO " . $this->db->table("addresses") . "
            SET   address_id = '" . (int) $address_id . "',
                customer_id = '" . (int) $customer_id . "',
                firstname = '" . $this->db->escape($address['firstname']) . "',
                lastname = '" . $this->db->escape($address['lastname']) . "',
                company = '" . $this->db->escape($address['company']) . "',
                address_1 = '" . $this->db->escape($address['address_1']) . "',
                address_2 = '" . $this->db->escape($address['address_2']) . "',
                city = '" . $this->db->escape($address['city']) . "',
                postcode = '" . $this->db->escape($address['postcode']) . "',
                country_id = '" . (int) $address['country_id'] . "'"
            . $key_sql . ",
                zone_id = '" . (int) $address['zone_id'] . "'"
        );
        return true;
    }

    /**
     * @param int $customer_id
     * @param int $address_id
     *
     * @return bool
     * @throws AException
     */
    public function deleteAddress($customer_id, $address_id)
    {
        if (!(int) $customer_id || !(int) $address_id) {
            return false;
        }
        $this->db->query(
            "DELETE FROM " . $this->db->table("addresses") . " 
             WHERE customer_id = '" . (int) $customer_id . "' 
                AND address_id = '" . (int) $address_id . "'"
        );
        return true;
    }

    /**
     * @param int $customer_id
     * @param string $field
     * @param mixed $value
     *
     * @return bool
     * @throws AException
     */
    public function editCustomerField($customer_id, $field, $value)
    {
        if (!$customer_id || !$field) {
            return false;
        }

        $data = [
            'loginname',
            'firstname',
            'lastname',
            'email',
            'telephone',
            'fax',
            'newsletter',
            'customer_group_id',
            'status',
            'approved',
        ];

        //adds IM fields
        //get only active IM drivers
        $im_protocols = $this->im->getProtocols();
        foreach ($im_protocols as $protocol) {
            if (!in_array($protocol, $data)) {
                $data[] = $protocol;
            }
        }

        if (in_array($field, $data)) {
            if ($this->dcrypt->active && in_array($field, $this->dcrypt->getEncryptedFields("customers"))) {
                //check key_id to use
                $query_key = $this->db->query(
                    "SELECT key_id
                     FROM " . $this->db->table("customers") . "
                     WHERE customer_id = '" . (int) $customer_id . "'"
                );
                $key_id = $query_key->rows[0]['key_id'];
                $value = $this->dcrypt->encrypt_field($value, $key_id);
            }
        }
        $this->db->query(
            "UPDATE " . $this->db->table("customers") . "
            SET " . $field . " = '" . $this->db->escape($value) . "'
            WHERE customer_id = '" . (int) $customer_id . "'"
        );

        if ($field == 'password') {
            $salt_key = genToken(8);
            $this->db->query(
                "UPDATE " . $this->db->table("customers") . "
                SET 
                    salt = '" . $this->db->escape($salt_key) . "', 
                    password = '" . $this->db->escape(passwordHash($value, $salt_key)) . "'
                WHERE customer_id = '" . (int) $customer_id . "'"
            );
        }
        if ($field == 'newsletter') {
            $this->saveCustomerNotificationSettings($customer_id, ['newsletter' => ['email' => (int) $value]]);
        }
        return true;
    }

    public function editCustomerNotifications($customer_id, $data)
    {
        if (!$data || !$customer_id) {
            return false;
        }

        $upd = [];
        //get only active IM drivers
        $im_protocols = $this->im->getProtocols();
        foreach ($im_protocols as $protocol) {
            if (isset($data[$protocol])) {
                $upd[$protocol] =
                    "`" . $this->db->escape($protocol) . "` = '" . $this->db->escape($data[$protocol]) . "'";
            }
        }

        //get all columns
        $sql = "SELECT COLUMN_NAME
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA = '" . DB_DATABASE . "' 
                    AND TABLE_NAME = '" . $this->db->table("customers") . "'";
        $result = $this->db->query($sql);
        $columns = [];
        foreach ($result->rows as $row) {
            $columns[] = $row['COLUMN_NAME'];
        }

        //remove not IM data
        $diff = array_diff($im_protocols, $columns);
        foreach ($diff as $k) {
            unset($data[$k]);
        }

        $key_sql = '';
        if ($this->dcrypt->active) {
            $data = $this->dcrypt->encrypt_data($data, 'customers');
            $key_sql = ", key_id = '" . (int) $data['key_id'] . "'";
        }

        $sql = "UPDATE " . $this->db->table('customers') . "
                SET " . implode(', ', $upd) . "\n"
            . $key_sql .
            " WHERE customer_id = '" . (int) $customer_id . "'";
        $this->db->query($sql);
        return true;
    }

    public function saveCustomerNotificationSettings($customer_id, $settings = [])
    {
        if (!$customer_id) {
            return null;
        }

        $sendPoints = array_keys($this->im->sendpoints);
        $im_protocols = $this->im->getProtocols();

        $update = [];
        foreach ($settings as $sendpoint => $row) {
            if (!in_array($sendpoint, $sendPoints)) {
                continue;
            }
            foreach ($im_protocols as $protocol) {
                $update[$sendpoint][$protocol] = (int) $row[$protocol];
            }
        }

        if ($update) {
            foreach ($update as $sendpoint => $row) {
                foreach ($row as $protocol => $status) {
                    $sql = "DELETE FROM " . $this->db->table('customer_notifications') . "
                            WHERE customer_id = " . $customer_id . "
                                AND sendpoint = '" . $this->db->escape($sendpoint) . "'
                                AND protocol = '" . $this->db->escape($protocol) . "'";
                    $this->db->query($sql);

                    $sql = "INSERT INTO " . $this->db->table('customer_notifications') . "
                            (customer_id, sendpoint,protocol,status, date_added)
                        VALUES
                        ('" . $customer_id . "',
                        '" . $this->db->escape($sendpoint) . "',
                        '" . $this->db->escape($protocol) . "',
                        '" . (int) $status . "',
                        NOW());";
                    $this->db->query($sql);
                }
            }
        }
        return true;
    }

    /**
     * @param int $customer_id
     * @param string $default_address_id
     *
     * @throws AException
     */
    public function setDefaultAddress($customer_id, $default_address_id)
    {
        if ($customer_id && $default_address_id) {
            $this->db->query(
                "UPDATE " . $this->db->table("customers") . "
                SET address_id = '" . (int) $default_address_id . "'
                WHERE customer_id = '" . (int) $customer_id . "'"
            );
        }
    }

    /**
     * @param int $address_id
     * @param string $field_name
     * @param mixed $value
     *
     * @throws AException
     */
    public function editAddressField($address_id, $field_name, $value)
    {
        $data = [
            'firstname',
            'lastname',
            'company',
            'address_1',
            'address_2',
            'city',
            'postcode',
            'country_id',
            'zone_id',
        ];
        if (in_array($field_name, $data)) {
            if ($this->dcrypt->active && in_array($field_name, $this->dcrypt->getEncryptedFields("addresses"))) {
                //check key_id to use
                $query_key = $this->db->query(
                    "SELECT key_id
                    FROM " . $this->db->table("addresses") . "
                    WHERE customer_id = '" . (int) $address_id . "'"
                );
                $key_id = $query_key->rows[0]['key_id'];
                $value = $this->dcrypt->encrypt_field($value, $key_id);
            }
        }
        $this->db->query(
            "UPDATE " . $this->db->table("addresses") . "
          SET " . $field_name . " = '" . $this->db->escape($value) . "'
          WHERE address_id = '" . (int) $address_id . "'"
        );
    }

    /**
     * @param int $customer_id
     *
     * @return array
     * @throws AException
     */
    public function getAddressesByCustomerId($customer_id)
    {
        $address_data = [];
        $query = $this->db->query(
            "SELECT *
            FROM " . $this->db->table("addresses") . "
            WHERE customer_id = '" . (int) $customer_id . "'"
        );

        /** @var ModelLocalisationCountry $cMdl */
        $cMdl = $this->load->model('localisation/country');
        /** @var ModelLocalisationZone $zMdl */
        $zMdl = $this->load->model('localisation/zone');
        foreach ($query->rows as $result) {
            $result = $this->dcrypt->decrypt_data($result, 'addresses');

            $country_row = $cMdl->getCountry($result['country_id']);
            $country = $country_row['name'] ? : '';
            $iso_code_2 = $country_row['iso_code_2'] ? : '';
            $iso_code_3 = $country_row['iso_code_3'] ? : '';
            $address_format = $country_row['address_format'] ? : '';

            $zone_row = $zMdl->getZone($result['zone_id']);
            $zone = $zone_row['name'] ? : '';
            $code = $zone_row['code'] ? : '';

            $address_data[$result['address_id']] = array_merge(
                $result,
                [
                    'zone'           => $zone,
                    'zone_code'      => $code,
                    'country'        => $country,
                    'iso_code_2'     => $iso_code_2,
                    'iso_code_3'     => $iso_code_3,
                    'address_format' => $address_format,
                    'ext_fields'     => $result['ext_fields'] ? json_decode($result['ext_fields'], true) : [],
                ]
            );
        }
        return $address_data;
    }

    /**
     * @param int $customer_id
     *
     * @throws AException
     */
    public function deleteCustomer($customer_id)
    {
        $this->db->query(
            "DELETE FROM " . $this->db->table("customers") . " 
            WHERE customer_id = '" . (int) $customer_id . "'"
        );
        $this->db->query(
            "DELETE FROM " . $this->db->table("addresses") . " 
            WHERE customer_id = '" . (int) $customer_id . "'"
        );
        $this->db->query(
            "DELETE FROM " . $this->db->table("customer_notifications") . " 
            WHERE customer_id = '" . (int) $customer_id . "'"
        );
    }

    /**
     * @param int $customer_id
     *
     * @return array
     * @throws AException
     */
    public function getCustomer($customer_id)
    {
        $query = $this->db->query(
            "SELECT DISTINCT *,
            (SELECT COUNT(order_id)
                FROM " . $this->db->table("orders") . "
                WHERE customer_id = '" . (int) $customer_id . "'
                    AND order_status_id>0) as orders_count
           FROM " . $this->db->table("customers") . "
           WHERE customer_id = '" . (int) $customer_id . "'"
        );

        $output = $this->dcrypt->decrypt_data($query->row, 'customers');
        $output['ext_fields'] = $output['ext_fields'] ? json_decode($output['ext_fields'], true) : [];
        return $output;
    }

    /**
     * @param array $data
     *
     * @return array|int
     * @throws AException
     */
    public function getTotalCustomers($data = [])
    {
        return $this->getCustomers($data, 'total_only');
    }

    /**
     * @param array $data
     * @param string $mode
     *
     * @return array|int
     * @throws AException
     */
    public function getCustomers($data = [], $mode = 'default')
    {
        if ($mode == 'total_only' && !$this->dcrypt->active) {
            $sql = "SELECT COUNT(*) as total ";
        } else {
            $sql = "SELECT " . $this->db->getSqlCalcTotalRows() . " 
                        c.*,
                        CONCAT(c.firstname, ' ', c.lastname) AS name,
                        cg.name AS customer_group ";
        }
        if ($mode != 'total_only' && $mode != 'quick') {
            $sql .= ", (SELECT COUNT(o.order_id) as cnt
                        FROM " . $this->db->table("orders") . " o
                        WHERE c.customer_id = o.customer_id AND o.order_status_id>0) as orders_count";
        }

        if ($this->dcrypt->active) {
            $sql .= ", c.key_id ";
        }

        $sql .= " FROM " . $this->db->table("customers") . " c
                  LEFT JOIN " . $this->db->table("customer_groups") . " cg 
                    ON (c.customer_group_id = cg.customer_group_id) ";

        $whereArr = [];
        $filter = ($data['filter'] ?? []);

        if (isset($filter['name']) && has_value($filter['name'])) {
            $whereArr[] = "CONCAT(c.firstname, ' ', c.lastname) 
                LIKE '%" . $this->db->escape($filter['name'], true) . "%' ";
        }
        if (isset($filter['customer_id']) && has_value($filter['customer_id'])) {
            $whereArr[] = "c.customer_id = " . (int) $filter['customer_id'];
        }

        if (isset($filter['name_email']) && has_value($filter['name_email'])) {
            $whereArr[] = "CONCAT(c.firstname, ' ', c.lastname, ' ', c.email) 
                            LIKE '%" . $this->db->escape($filter['name_email'], true) . "%' ";
        }
        //more specific login, last and first name search
        if (isset($filter['loginname']) && has_value($filter['loginname'])) {
            $whereArr[] = "c.loginname LIKE '" . $this->db->escape($filter['loginname']) . "' ";
        }
        if (isset($filter['firstname']) && has_value($filter['firstname'])) {
            $whereArr[] = "c.firstname LIKE '" . $this->db->escape($filter['firstname'], true) . "%' ";
        }
        if (isset($filter['lastname']) && has_value($filter['lastname'])) {
            $whereArr[] = "c.lastname LIKE '" . $this->db->escape($filter['lastname'], true) . "%' ";
        }
        //select differently if encrypted
        if (!$this->dcrypt->active) {
            if (isset($filter['email']) && has_value($filter['email'])) {
                $whereArr[] = "c.email LIKE '%" . $this->db->escape($filter['email'], true) . "%' ";
            }
            if (isset($filter['telephone']) && has_value($filter['telephone'])) {
                $whereArr[] = "c.telephone LIKE '%" . $this->db->escape($filter['telephone'], true) . "%' ";
            }
            if (isset($filter['sms']) && has_value($filter['sms'])) {
                $whereArr[] = "c.sms LIKE '%" . $this->db->escape($filter['sms'], true) . "%' ";
            }
        } //if encryption enabled
        else {
            $email = trim($filter['email']);
            //if we do not require login name, this means email is loginname and we can search by it
            if ($email
                && !$this->config->get('prevent_email_as_login')
                && !in_array('loginname', $this->dcrypt->getEncryptedFields('customers'))
            ) {
                $whereArr[] = "c.loginname LIKE '%" . $this->db->escape($email, true) . "%' ";
            }
        }

        if (isset($filter['customer_group_id']) && has_value($filter['customer_group_id'])) {
            $whereArr[] = "cg.customer_group_id = '" . $this->db->escape($filter['customer_group_id']) . "'";
        }
        // select only subscribers (group + customers with subscription)
        if (isset($filter['all_subscribers']) && has_value($filter['all_subscribers'])) {
            $whereArr[] = "( (c.newsletter=1 AND c.status = 1 AND c.approved = 1) 
                OR
                (c.newsletter=1 AND cg.customer_group_id = '" . (int) $this->getSubscribersCustomerGroupId() . "'))";
        }

        // select only customers without newsletter subscribers
        if (isset($filter['only_customers']) && has_value($filter['only_customers'])) {
            $whereArr[] = "cg.customer_group_id NOT IN (" . (int) $this->getSubscribersCustomerGroupId() . ") ";
        }

        if (isset($filter['only_with_mobile_phones']) && has_value($filter['only_with_mobile_phones'])) {
            $whereArr[] = " TRIM(COALESCE(c.sms,''))  <> '' ";
        }

        if (isset($filter['status']) && has_value($filter['status'])) {
            $whereArr[] = "c.status = '" . (int) $filter['status'] . "'";
        }

        if (isset($filter['approved']) && has_value($filter['approved'])) {
            $whereArr[] = "c.approved = '" . (int) $filter['approved'] . "'";
        }

        if (isset($filter['date_added']) && has_value($filter['date_added'])) {
            $whereArr[] = "DATE(c.date_added) = DATE('" . $this->db->escape($filter['date_added']) . "')";
        }

        $store_id = null;
        if (isset($this->session->data['current_store_id']) && has_value($this->session->data['current_store_id'])) {
            $store_id = (int) $this->session->data['current_store_id'];
        }

        /** @var ModelSettingStore $sMdl */
        $sMdl = $this->load->model('setting/store');
        if (!$store_id && !$sMdl->isDefaultStore()) {
            $store_id = $this->config->get('current_store_id');
        }

        if ($store_id !== null) {
            $whereArr[] = "c.store_id = " . (int) $store_id;
        }

        if (
            (($filter['all_subscribers'] ?? null) || ($filter['only_subscribers'] ?? null))
            && $filter['newsletter_protocol']
        ) {
            $sql .= "RIGHT JOIN " . $this->db->table('customer_notifications') . " cn
                    ON (cn.customer_id = c.customer_id
                        AND cn.sendpoint='newsletter'
                        AND cn.status=1
                        AND cn.protocol = '" . $this->db->escape($filter['newsletter_protocol']) . "') ";
        }

        if ($whereArr) {
            $sql .= " WHERE " . implode(" AND ", $whereArr);
        }

        //If for total, we're done building the query
        if ($mode == 'total_only' && !$this->dcrypt->active) {
            $query = $this->db->query($sql);
            return $query->row['total'];
        }

        $sort_data = array_merge(
            [
                'customer_id'    => 'c.customer_id',
                'name'           => 'name',
                'loginname'      => 'c.loginname',
                'lastname'       => 'c.lastname',
                'email'          => 'c.email',
                'sms'            => 'c.sms',
                'customer_group' => 'customer_group',
                'status'         => 'c.status',
                'approved'       => 'c.approved',
                'date_added'     => 'c.date_added',
                'orders_count'   => 'orders_count',
            ],
            (array) $data['sort_data']
        );

        //Total calculation for encrypted mode
        // NOTE: Performance slowdown might be noticed or larger search results
        if ($mode != 'total_only') {
            $sql .= " ORDER BY " . ($sort_data[$data['sort']] ? : 'name');
            if (isset($data['order']) && (strtoupper($data['order']) == 'DESC')) {
                $sql .= " DESC";
            } else {
                $sql .= " ASC";
            }

            if (isset($data['start']) || isset($data['limit'])) {
                if ($data['start'] < 0) {
                    $data['start'] = 0;
                }
                if ($data['limit'] < 1) {
                    $data['limit'] = 20;
                }
                $sql .= " LIMIT " . (int) $data['start'] . "," . (int) $data['limit'];
            }
        }

        $query = $this->db->query($sql);
        $result_rows = $query->rows;

        if ($this->dcrypt->active) {
            if (isset($filter['telephone']) && has_value($filter['telephone'])) {
                $result_rows = $this->_filter_by_encrypted_field($result_rows, 'telephone', $filter['telephone']);
            }
            if (isset($filter['sms']) && has_value($filter['sms'])) {
                $result_rows = $this->_filter_by_encrypted_field($result_rows, 'sms', $filter['sms']);
            }
        }

        if ($mode == 'total_only') {
            //we get here only if in data encryption mode
            return count($result_rows);
        }
        //finally, decrypt data and return result
        $totalRows = $this->db->getTotalNumRows();
        for ($i = 0; $i < count($result_rows); $i++) {
            $result_rows[$i] = $this->dcrypt->decrypt_data($result_rows[$i], 'customers');
            $result_rows[$i]['total_num_rows'] = $totalRows;
        }

        return $result_rows;
    }

    /**
     * @param array $data
     * @param string $field
     * @param mixed $value
     *
     * @return array
     * @throws AException
     */
    private function _filter_by_encrypted_field($data, $field, $value)
    {
        if (!count($data)) {
            return [];
        }
        if (!has_value($field) || !has_value($value)) {
            return $data;
        }
        $result_rows = [];
        foreach ($data as $result) {
            if ($this->dcrypt->active) {
                $f_value = $this->dcrypt->decrypt_field($result[$field], $result['key_id']);
            } else {
                $f_value = $result[$field];
            }
            if (!(strpos(strtolower($f_value), strtolower($value)) === false)) {
                $result_rows[] = $result;
            }
        }
        return $result_rows;
    }

    /**
     * @param int $customer_id
     *
     * @throws AException
     */
    public function approve($customer_id)
    {
        $this->db->query(
            "UPDATE " . $this->db->table("customers") . "
            SET approved = '1'
                WHERE customer_id = '" . (int) $customer_id . "'"
        );
    }

    /**
     * @return array
     * @throws AException
     */
    public function getCustomersByNewsletter()
    {
        $store_based = '';
        if (isset($this->session->data['current_store_id']) && has_value($this->session->data['current_store_id'])) {
            $store_based = " AND store_id = " . (int) $this->session->data['current_store_id'];
        }
        $query = $this->db->query(
            "SELECT *
            FROM " . $this->db->table("customers") . "
            WHERE newsletter = '1' " . $store_based . "
            ORDER BY firstname, lastname, email"
        );
        $result_rows = [];
        foreach ($query->rows as $row) {
            $result_rows[] = $this->dcrypt->decrypt_data($row, 'customers');
        }
        return $result_rows;
    }

    /**
     * @param string $keyword
     *
     * @return array
     * @throws AException
     */
    public function getCustomersByKeyword($keyword)
    {
        $store_based = '';
        if (isset($this->session->data['current_store_id']) && has_value($this->session->data['current_store_id'])) {
            $store_based = " AND store_id = " . (int) $this->session->data['current_store_id'];
        }

        if ($keyword) {
            $query = $this->db->query(
                "SELECT *
                FROM " . $this->db->table("customers") . "
                WHERE LCASE(CONCAT(firstname, ' ', lastname)) LIKE '%" . $this->db->escape(strtolower($keyword), true) . "%'
                     OR LCASE(email) LIKE '%" . $this->db->escape(strtolower($keyword), true) . "%' " . $store_based . "
                ORDER BY firstname, lastname, email"
            );
            $result_rows = [];
            foreach ($query->rows as $row) {
                $result_rows[] = $this->dcrypt->decrypt_data($row, 'customers');
            }
            return $result_rows;
        } else {
            return [];
        }
    }

    /**
     * @param array $emails
     *
     * @return array
     * @throws AException
     */
    public function getCustomersByEmails($emails)
    {
        $store_based = '';
        if (isset($this->session->data['current_store_id']) && has_value($this->session->data['current_store_id'])) {
            $store_based = " and store_id = " . (int) $this->session->data['current_store_id'];
        }

        $emails = (array) $emails;
        $where = [];
        if ($emails) {
            $sql = "SELECT *
                   FROM " . $this->db->table("customers") . "
                   WHERE ";
            foreach ($emails as $email) {
                $where[] = "LCASE(email) LIKE '%" . $this->db->escape(strtolower($email), true) . "%'";
            }
            $sql .= implode(' OR ', $where) . $store_based;
            $sql .= " ORDER BY firstname, lastname, email";

            $query = $this->db->query($sql);
            $result_rows = [];
            foreach ($query->rows as $row) {
                $result_rows[] = $this->dcrypt->decrypt_data($row, 'customers');
            }
            return $result_rows;
        } else {
            return [];
        }
    }

    /**
     * @param int $product_id
     *
     * @return array
     * @throws AException
     */
    public function getCustomersByProduct($product_id)
    {
        if (!$product_id) {
            return [];
        }

        $query = $this->db->query(
            "SELECT *
            FROM " . $this->db->table("customers") . "
            WHERE customer_id IN (
                SELECT DISTINCT `customer_id`
                FROM `" . $this->db->table("orders") . "` o
                INNER JOIN " . $this->db->table("order_products") . " op
                    ON (o.order_id = op.order_id AND op.product_id = '" . (int) $product_id . "')
                WHERE o.order_status_id <> '0')"
        );

        $result_rows = [];
        foreach ($query->rows as $row) {
            $result_rows[] = $this->dcrypt->decrypt_data($row, 'customers');
        }
        return $result_rows;
    }

    /**
     * @param int $customer_id
     *
     * @return array
     * @throws AException
     */
    public function getAddresses($customer_id)
    {
        $query = $this->db->query(
            "SELECT *
            FROM " . $this->db->table("addresses") . "
            WHERE customer_id = '" . (int) $customer_id . "'"
        );
        $result_rows = [];
        foreach ($query->rows as $row) {
            $result_rows[] = $this->dcrypt->decrypt_data($row, 'addresses');
        }
        return $result_rows;
    }

    /**
     * @param string $loginname
     * @param string $customer_id
     *
     * @return bool
     * @throws AException
     */
    public function is_unique_loginname($loginname, $customer_id = '')
    {
        if (empty($loginname)) {
            return false;
        }
        //exclude given customer from checking
        $not_current_customer = '';
        if (has_value($customer_id)) {
            $not_current_customer = "AND customer_id <> '$customer_id'";
        }
        $query = $this->db->query(
            "SELECT COUNT(*) AS total
           FROM " . $this->db->table("customers") . "
           WHERE LOWER(`loginname`) = LOWER('" . $loginname . "') " . $not_current_customer
        );
        if ($query->row['total'] > 0) {
            return false;
        } else {
            return true;
        }
    }

    /**
     * @return int
     * @throws AException
     */
    public function getTotalCustomersAwaitingApproval()
    {
        $query = $this->db->query(
            "SELECT COUNT(*) AS total
           FROM " . $this->db->table("customers") . "
           WHERE approved = '0'"
        );
        return $query->row['total'];
    }

    /**
     * @param int $customer_id
     *
     * @return int
     * @throws AException
     */
    public function getTotalAddressesByCustomerId($customer_id)
    {
        $query = $this->db->query(
            "SELECT COUNT(*) AS total
            FROM " . $this->db->table("addresses") . "
            WHERE customer_id = '" . (int) $customer_id . "'"
        );
        return $query->row['total'];
    }

    /**
     * @param int $country_id
     *
     * @return int
     * @throws AException
     */
    public function getTotalAddressesByCountryId($country_id)
    {
        $query = $this->db->query(
            "SELECT COUNT(*) AS total
            FROM " . $this->db->table("addresses") . "
            WHERE country_id = '" . (int) $country_id . "'"
        );
        return (int) $query->row['total'];
    }

    /**
     * @param int $zone_id
     *
     * @return int
     * @throws AException
     */
    public function getTotalAddressesByZoneId($zone_id)
    {
        $query = $this->db->query(
            "SELECT COUNT(*) AS total
            FROM " . $this->db->table("addresses") . "
            WHERE zone_id = '" . (int) $zone_id . "'"
        );
        return (int) $query->row['total'];
    }

    /**
     * @param int $customer_group_id
     *
     * @return int
     * @throws AException
     */
    public function getTotalCustomersByCustomerGroupId($customer_group_id)
    {
        $query = $this->db->query(
            "SELECT COUNT(*) AS total
           FROM " . $this->db->table("customers") . "
           WHERE customer_group_id = '" . (int) $customer_group_id . "'"
        );
        return (int) $query->row['total'];
    }

    /**
     * @param array $data
     * @param string $mode
     *
     * @return array|int
     * @throws AException
     */
    public function getAllSubscribers($data = [], $mode = 'default')
    {
        $data['filter']['all_subscribers'] = 1;
        return $this->getCustomers($data, $mode);
    }

    /**
     * @param array $data
     *
     * @return array|int
     * @throws AException
     */
    public function getTotalAllSubscribers($data = [])
    {
        $data['filter']['all_subscribers'] = 1;
        return $this->getCustomers($data, 'total_only');
    }

    /**
     * @param array $data
     * @param string $mode
     *
     * @return array|int
     * @throws AException
     */
    public function getOnlyNewsletterSubscribers($data = [], $mode = 'default')
    {
        $data['filter']['customer_group_id'] = $this->getSubscribersCustomerGroupId();
        $data['filter']['only_subscribers'] = 1;
        return $this->getCustomers($data, $mode);
    }

    /**
     * @param array $data
     *
     * @return int
     * @throws AException
     */
    public function getTotalOnlyNewsletterSubscribers($data = [])
    {
        $data['filter']['customer_group_id'] = $this->getSubscribersCustomerGroupId();
        $data['filter']['only_subscribers'] = 1;
        return $this->getCustomers($data, 'total_only');
    }

    /**
     * @param array $data
     * @param string $mode
     *
     * @return array|int
     * @throws AException
     */
    public function getOnlyCustomers($data = [], $mode = 'default')
    {
        $data['filter']['only_customers'] = 1;
        return $this->getCustomers($data, $mode);
    }

    /**
     * @param array $data
     *
     * @return int
     * @throws AException
     */
    public function getTotalOnlyCustomers($data = [])
    {
        $data['filter']['only_customers'] = 1;
        return $this->getCustomers($data, 'total_only');
    }

    /**
     * @return int
     * @throws AException
     */
    public function getSubscribersCustomerGroupId()
    {
        $query = $this->db->query(
            "SELECT customer_group_id
                                    FROM `" . $this->db->table("customer_groups") . "`
                                    WHERE `name` = 'Newsletter Subscribers'
                                    LIMIT 0,1"
        );
        return !$query->row['customer_group_id'] ? (int) $this->config->get('config_customer_group_id')
            : (int) $query->row['customer_group_id'];
    }

    /**
     * @param int $customer_id
     *
     * @return bool
     * @throws AException
     */
    public function isSubscriber($customer_id)
    {
        $sql = "SELECT * 
                FROM `" . $this->db->table("customers") . "`
                WHERE customer_id = " . (int) $customer_id . " 
                    AND customer_group_id = '" . $this->getSubscribersCustomerGroupId() . "'";
        $result = $this->db->query($sql);
        return (bool) $result->num_rows;
    }

    /**
     * @param int $customer_id - customer_id
     *
     * @throws AException|TransportExceptionInterface
     */
    public function sendApproveMail($customer_id)
    {
        // send email to customer
        $customer_info = $this->getCustomer($customer_id);
        if ($customer_info && !$customer_info['approved']) {
            $this->load->language('mail/customer');
            /** @var ModelSettingStore $sMdl */
            $sMdl = $this->load->model('setting/store');
            $languageId = $this->language->getContentLanguageID() ?? $this->language->getLanguageID();
            $store_info = $sMdl->getStore((int) $customer_info['store_id']);
            if ($store_info) {
                $store_info['store_url'] = $store_info['config_url'] . 'index.php?rt=account/login';
            } else {
                $store_info = [
                    'store_name'                      => $this->config->get('store_name'),
                    'store_url'                       => $this->config->get('config_url')
                        . 'index.php?rt=account/login',
                    'config_mail_logo_' . $languageId =>
                        $this->config->get('config_mail_logo_' . $languageId)
                            ? : $this->config->get(
                            'config_mail_logo'
                        ),
                    'config_logo_' . $languageId      =>
                        $this->config->get('config_logo_' . $languageId) ? : $this->config->get('config_logo'),
                ];
            }

            $mailLogo = $store_info['config_mail_logo_' . $languageId] ? : $store_info['config_mail_logo'];
            $mailLogo = $mailLogo ? : $store_info['config_logo_' . $languageId];
            $mailLogo = $mailLogo ? : $store_info['config_logo'];

            if ($mailLogo) {
                $result = getMailLogoDetails($mailLogo);
                $this->data['mail_template_data']['logo_uri'] = $result['uri'];
                $this->data['mail_template_data']['logo_html'] = $result['html'];
            }

            $this->data['mail_template_data']['store_name'] = $store_info['store_name'];
            $this->data['mail_template_data']['store_url'] = $store_info['store_url'];
            $this->data['mail_template_data']['login_url'] = $store_info['store_url'];
            $this->data['mail_template'] = 'mail/account_create.tpl';

            //allow changing email data from extensions
            $this->extensions->hk_ProcessData($this, 'cp_customer_approve_mail');

            $mail = new AMail($this->config);
            $mail->setTo($customer_info['email']);
            $mail->setFrom($this->config->get('store_main_email'));
            $mail->setSender($store_info['store_name']);
            $mail->setTemplate('storefront_welcome_email_approved', $this->data['mail_template_data']);
            if (is_file(DIR_RESOURCE . $mailLogo)) {
                $mail->addAttachment(
                    DIR_RESOURCE . $mailLogo,
                    md5(pathinfo($mailLogo, PATHINFO_FILENAME))
                    . '.' . pathinfo($mailLogo, PATHINFO_EXTENSION)
                );
            }
            $mail->send();
        }
    }

}
