<?php

class Customer extends ObjectModel
{
    public $id;
    public $id_customer;
    public $id_shop_default;
    public $id_shop_group;
    public $id_gender;
    public $id_default_group;
    public $id_lang;
    public $id_risk;
    public $company;
    public $siret;
    public $ape;
    public $firstname;
    public $lastname;
    public $email;
    public $passwd;
    public $last_passwd_gen;
    public $birthday;
    public $newsletter;
    public $ip_registration_newsletter;
    public $newsletter_date_add;
    public $optin;
    public $website;
    public $outstanding_allow_amount;
    public $show_public_prices;
    public $max_payment_days;
    public $secure_key;
    public $note;
    public $active = true;
    public $is_guest = false;
    public $deleted = false;
    public $date_add;
    public $date_upd;
    public $years;
    public $days;
    public $months;
    public $geoloc_id_country;
    public $geoloc_id_state;
    public $geoloc_postcode;
    public $logged = false;
    public $id_guest;
    public $groupBox;
    public $id_default_group_old;
    public $current_shop_id;
    public $id_shop_list;
    public $force_id = false;

    public static $definition = [
        'table' => 'customer',
        'primary' => 'id_customer',
        'fields' => [
            'id_shop_default' => ['type' => self::TYPE_INT],
            'id_shop_group' => ['type' => self::TYPE_INT],
            'id_gender' => ['type' => self::TYPE_INT],
            'id_default_group' => ['type' => self::TYPE_INT, 'required' => true],
            'id_lang' => ['type' => self::TYPE_INT],
            'id_risk' => ['type' => self::TYPE_INT],
            'company' => ['type' => self::TYPE_STRING, 'size' => 255],
            'siret' => ['type' => self::TYPE_STRING, 'size' => 14],
            'ape' => ['type' => self::TYPE_STRING, 'size' => 5],
            'firstname' => ['type' => self::TYPE_STRING, 'required' => true, 'size' => 255],
            'lastname' => ['type' => self::TYPE_STRING, 'required' => true, 'size' => 255],
            'email' => ['type' => self::TYPE_STRING, 'required' => true, 'size' => 255],
            'passwd' => ['type' => self::TYPE_STRING, 'required' => true, 'size' => 255],
            'last_passwd_gen' => ['type' => self::TYPE_STRING],
            'birthday' => ['type' => self::TYPE_DATE],
            'newsletter' => ['type' => self::TYPE_BOOL],
            'ip_registration_newsletter' => ['type' => self::TYPE_STRING],
            'newsletter_date_add' => ['type' => self::TYPE_DATE],
            'optin' => ['type' => self::TYPE_BOOL],
            'website' => ['type' => self::TYPE_STRING, 'size' => 128],
            'outstanding_allow_amount' => ['type' => self::TYPE_FLOAT],
            'show_public_prices' => ['type' => self::TYPE_BOOL],
            'max_payment_days' => ['type' => self::TYPE_INT],
            'secure_key' => ['type' => self::TYPE_STRING, 'size' => 32],
            'note' => ['type' => self::TYPE_HTML],
            'active' => ['type' => self::TYPE_BOOL],
            'is_guest' => ['type' => self::TYPE_BOOL],
            'deleted' => ['type' => self::TYPE_BOOL],
            'date_add' => ['type' => self::TYPE_DATE, 'shop' => true],
            'date_upd' => ['type' => self::TYPE_DATE, 'shop' => true],
            'geoloc_id_country' => ['type' => self::TYPE_INT],
            'geoloc_id_state' => ['type' => self::TYPE_INT],
            'geoloc_postcode' => ['type' => self::TYPE_STRING],
        ],
    ];

    public function __construct($idCustomer = null, $idLang = null, $idShop = null)
    {
        parent::__construct($idCustomer, $idLang, $idShop);
    }

    /**
     * Kunde hinzufügen
     */
    public function add($autoDate = true, $nullValues = false)
    {
        if (!$this->secure_key) {
            $this->secure_key = Tools::passwdGen(32);
        }
        
        if (!$this->id_lang) {
            $this->id_lang = Context::getContext()->language->id;
        }
        
        $ret = parent::add($autoDate, $nullValues);
        
        if ($ret && $this->groupBox) {
            $this->addGroups($this->groupBox);
        }
        
        return $ret;
    }

    /**
     * Kunde aktualisieren
     */
    public function update($nullValues = false)
    {
        $ret = parent::update($nullValues);
        
        if ($ret && $this->groupBox) {
            $this->addGroups($this->groupBox);
        }
        
        return $ret;
    }

    /**
     * Kunde löschen
     */
    public function delete()
    {
        // Adressen löschen
        $addresses = $this->getAddresses();
        foreach ($addresses as $address) {
            $addressObj = new Address($address['id_address']);
            $addressObj->delete();
        }
        
        // Bestellungen löschen (optional)
        // $orders = $this->getOrders();
        // foreach ($orders as $order) {
        //     $orderObj = new Order($order['id_order']);
        //     $orderObj->delete();
        // }
        
        return parent::delete();
    }

    /**
     * Alle Kunden abrufen
     */
    public static function getCustomers($active = true, $deleted = false)
    {
        $sql = 'SELECT c.*
                FROM ' . _DB_PREFIX_ . 'customer c
                WHERE 1=1';
        
        if ($active !== null) {
            $sql .= ' AND c.active = ' . (int)$active;
        }
        
        if ($deleted !== null) {
            $sql .= ' AND c.deleted = ' . (int)$deleted;
        }
        
        $sql .= ' ORDER BY c.date_add DESC';
        
        return Db::getInstance()->executeS($sql);
    }

    /**
     * Kunden nach E-Mail abrufen
     */
    public static function getCustomersByEmail($email)
    {
        $sql = 'SELECT c.*
                FROM ' . _DB_PREFIX_ . 'customer c
                WHERE c.email = "' . pSQL($email) . '"
                ORDER BY c.date_add DESC';
        
        return Db::getInstance()->executeS($sql);
    }

    /**
     * Kunde nach E-Mail abrufen
     */
    public static function getByEmail($email, $passwd = null, $ignoreGuest = true)
    {
        $sql = 'SELECT c.*
                FROM ' . _DB_PREFIX_ . 'customer c
                WHERE c.email = "' . pSQL($email) . '"';
        
        if ($ignoreGuest) {
            $sql .= ' AND c.is_guest = 0';
        }
        
        if ($passwd) {
            $sql .= ' AND c.passwd = "' . pSQL($passwd) . '"';
        }
        
        $sql .= ' LIMIT 1';
        
        return Db::getInstance()->getRow($sql);
    }

    /**
     * Kunde existiert
     */
    public static function customerExists($email, $returnId = false, $ignoreGuest = true)
    {
        $sql = 'SELECT c.id_customer
                FROM ' . _DB_PREFIX_ . 'customer c
                WHERE c.email = "' . pSQL($email) . '"';
        
        if ($ignoreGuest) {
            $sql .= ' AND c.is_guest = 0';
        }
        
        $sql .= ' LIMIT 1';
        
        $result = Db::getInstance()->getRow($sql);
        
        if ($returnId) {
            return $result ? $result['id_customer'] : false;
        }
        
        return $result !== false;
    }

    /**
     * Gruppen abrufen
     */
    public function getGroups()
    {
        $sql = 'SELECT cg.id_group, g.name
                FROM ' . _DB_PREFIX_ . 'customer_group cg
                LEFT JOIN ' . _DB_PREFIX_ . 'group g ON (cg.id_group = g.id_group)
                WHERE cg.id_customer = ' . (int)$this->id;
        
        return Db::getInstance()->executeS($sql);
    }

    /**
     * Gruppen hinzufügen
     */
    public function addGroups($groups)
    {
        $sql = 'DELETE FROM ' . _DB_PREFIX_ . 'customer_group
                WHERE id_customer = ' . (int)$this->id;
        Db::getInstance()->execute($sql);
        
        foreach ($groups as $groupId) {
            $sql = 'INSERT INTO ' . _DB_PREFIX_ . 'customer_group (id_customer, id_group)
                    VALUES (' . (int)$this->id . ', ' . (int)$groupId . ')';
            Db::getInstance()->execute($sql);
        }
    }

    /**
     * Gruppe aktualisieren
     */
    public function updateGroup($idGroup)
    {
        $this->id_default_group = $idGroup;
        return $this->update();
    }

    /**
     * Adressen abrufen
     */
    public function getAddresses($idLang = null)
    {
        if (!$idLang) {
            $idLang = Context::getContext()->language->id;
        }
        
        $sql = 'SELECT a.*, cl.name as country_name, sl.name as state_name
                FROM ' . _DB_PREFIX_ . 'address a
                LEFT JOIN ' . _DB_PREFIX_ . 'country_lang cl ON (a.id_country = cl.id_country AND cl.id_lang = ' . (int)$idLang . ')
                LEFT JOIN ' . _DB_PREFIX_ . 'state_lang sl ON (a.id_state = sl.id_state AND sl.id_lang = ' . (int)$idLang . ')
                WHERE a.id_customer = ' . (int)$this->id . ' AND a.deleted = 0
                ORDER BY a.date_add DESC';
        
        return Db::getInstance()->executeS($sql);
    }

    /**
     * Standard-Adresse abrufen
     */
    public function getDefaultAddress()
    {
        $sql = 'SELECT a.*
                FROM ' . _DB_PREFIX_ . 'address a
                WHERE a.id_customer = ' . (int)$this->id . ' AND a.deleted = 0
                ORDER BY a.date_add ASC
                LIMIT 1';
        
        return Db::getInstance()->getRow($sql);
    }

    /**
     * Bestellungen abrufen
     */
    public function getOrders($includeHidden = false)
    {
        $sql = 'SELECT o.*
                FROM ' . _DB_PREFIX_ . 'orders o
                WHERE o.id_customer = ' . (int)$this->id;
        
        if (!$includeHidden) {
            $sql .= ' AND o.hidden = 0';
        }
        
        $sql .= ' ORDER BY o.date_add DESC';
        
        return Db::getInstance()->executeS($sql);
    }

    /**
     * Gesamtausgaben abrufen
     */
    public function getTotalSpent()
    {
        $sql = 'SELECT SUM(o.total_paid_tax_incl) as total_spent
                FROM ' . _DB_PREFIX_ . 'orders o
                WHERE o.id_customer = ' . (int)$this->id . ' AND o.current_state IN (3, 4, 5)';
        
        $result = Db::getInstance()->getRow($sql);
        
        return $result['total_spent'] ?: 0;
    }

    /**
     * Statistiken abrufen
     */
    public function getStats()
    {
        $sql = 'SELECT 
                    COUNT(DISTINCT o.id_order) as total_orders,
                    SUM(o.total_paid_tax_incl) as total_spent,
                    AVG(o.total_paid_tax_incl) as avg_order_value,
                    MAX(o.date_add) as last_order_date
                FROM ' . _DB_PREFIX_ . 'orders o
                WHERE o.id_customer = ' . (int)$this->id . ' AND o.current_state IN (3, 4, 5)';
        
        return Db::getInstance()->getRow($sql);
    }

    /**
     * Beste Verkäufe abrufen
     */
    public function getBestSales()
    {
        $sql = 'SELECT 
                    p.id_product,
                    pl.name,
                    SUM(od.product_quantity) as total_quantity,
                    SUM(od.total_price_tax_incl) as total_revenue
                FROM ' . _DB_PREFIX_ . 'orders o
                LEFT JOIN ' . _DB_PREFIX_ . 'order_detail od ON (o.id_order = od.id_order)
                LEFT JOIN ' . _DB_PREFIX_ . 'product p ON (od.product_id = p.id_product)
                LEFT JOIN ' . _DB_PREFIX_ . 'product_lang pl ON (p.id_product = pl.id_product AND pl.id_lang = ' . (int)Context::getContext()->language->id . ')
                WHERE o.id_customer = ' . (int)$this->id . ' AND o.current_state IN (3, 4, 5)
                GROUP BY p.id_product
                ORDER BY total_revenue DESC
                LIMIT 10';
        
        return Db::getInstance()->executeS($sql);
    }

    /**
     * Passwort prüfen
     */
    public function checkPassword($passwd)
    {
        return password_verify($passwd, $this->passwd);
    }

    /**
     * Passwort aktualisieren
     */
    public function updatePassword($passwd)
    {
        $this->passwd = password_hash($passwd, PASSWORD_DEFAULT);
        $this->last_passwd_gen = date('Y-m-d H:i:s');
        
        return $this->update();
    }

    /**
     * Passwort generieren
     */
    public function generatePassword()
    {
        $passwd = Tools::passwdGen(8);
        $this->updatePassword($passwd);
        
        return $passwd;
    }

    /**
     * Vollständiger Name
     */
    public function getFullName()
    {
        return $this->firstname . ' ' . $this->lastname;
    }

    /**
     * Altersberechnung
     */
    public function getAge()
    {
        if (!$this->birthday) {
            return null;
        }
        
        $birthday = new DateTime($this->birthday);
        $today = new DateTime();
        $age = $today->diff($birthday);
        
        return $age->y;
    }

    /**
     * Newsletter abonnieren
     */
    public function subscribeNewsletter()
    {
        $this->newsletter = 1;
        $this->newsletter_date_add = date('Y-m-d H:i:s');
        $this->ip_registration_newsletter = Tools::getRemoteAddr();
        
        return $this->update();
    }

    /**
     * Newsletter abbestellen
     */
    public function unsubscribeNewsletter()
    {
        $this->newsletter = 0;
        
        return $this->update();
    }

    /**
     * Opt-in
     */
    public function optIn()
    {
        $this->optin = 1;
        
        return $this->update();
    }

    /**
     * Opt-out
     */
    public function optOut()
    {
        $this->optin = 0;
        
        return $this->update();
    }

    /**
     * Kunde aktivieren
     */
    public function activate()
    {
        $this->active = 1;
        
        return $this->update();
    }

    /**
     * Kunde deaktivieren
     */
    public function deactivate()
    {
        $this->active = 0;
        
        return $this->update();
    }

    /**
     * Als gelöscht markieren
     */
    public function softDelete()
    {
        $this->deleted = 1;
        
        return $this->update();
    }

    /**
     * Wiederherstellen
     */
    public function restore()
    {
        $this->deleted = 0;
        
        return $this->update();
    }

    /**
     * Webservice: Gruppen
     */
    public function getWsGroups()
    {
        $groups = $this->getGroups();
        $result = [];
        
        foreach ($groups as $group) {
            $result[] = $group['id_group'];
        }
        
        return $result;
    }

    /**
     * Webservice: Gruppen setzen
     */
    public function setWsGroups($groups)
    {
        $this->addGroups($groups);
        
        return true;
    }

    /**
     * Kunden nach Gruppe
     */
    public static function getCustomersByGroup($idGroup)
    {
        $sql = 'SELECT c.*
                FROM ' . _DB_PREFIX_ . 'customer c
                LEFT JOIN ' . _DB_PREFIX_ . 'customer_group cg ON (c.id_customer = cg.id_customer)
                WHERE cg.id_group = ' . (int)$idGroup . '
                ORDER BY c.date_add DESC';
        
        return Db::getInstance()->executeS($sql);
    }

    /**
     * Kunden nach Datum
     */
    public static function getCustomersByDate($dateFrom, $dateTo)
    {
        $sql = 'SELECT c.*
                FROM ' . _DB_PREFIX_ . 'customer c
                WHERE c.date_add BETWEEN "' . pSQL($dateFrom) . '" AND "' . pSQL($dateTo) . '"
                ORDER BY c.date_add DESC';
        
        return Db::getInstance()->executeS($sql);
    }

    /**
     * Kunden nach Land
     */
    public static function getCustomersByCountry($idCountry)
    {
        $sql = 'SELECT c.*
                FROM ' . _DB_PREFIX_ . 'customer c
                LEFT JOIN ' . _DB_PREFIX_ . 'address a ON (c.id_customer = a.id_customer)
                WHERE a.id_country = ' . (int)$idCountry . ' AND a.deleted = 0
                GROUP BY c.id_customer
                ORDER BY c.date_add DESC';
        
        return Db::getInstance()->executeS($sql);
    }

    /**
     * Kunden-Suche
     */
    public static function searchCustomers($query, $limit = 10)
    {
        $sql = 'SELECT c.*
                FROM ' . _DB_PREFIX_ . 'customer c
                WHERE (c.firstname LIKE "%' . pSQL($query) . '%" 
                    OR c.lastname LIKE "%' . pSQL($query) . '%" 
                    OR c.email LIKE "%' . pSQL($query) . '%" 
                    OR c.company LIKE "%' . pSQL($query) . '%")
                ORDER BY c.date_add DESC
                LIMIT ' . (int)$limit;
        
        return Db::getInstance()->executeS($sql);
    }

    /**
     * Kunden-Statistiken
     */
    public static function getCustomerStats()
    {
        $sql = 'SELECT 
                    COUNT(*) as total_customers,
                    COUNT(CASE WHEN active = 1 THEN 1 END) as active_customers,
                    COUNT(CASE WHEN newsletter = 1 THEN 1 END) as newsletter_subscribers,
                    COUNT(CASE WHEN is_guest = 1 THEN 1 END) as guest_customers,
                    COUNT(CASE WHEN deleted = 1 THEN 1 END) as deleted_customers,
                    AVG(DATEDIFF(NOW(), date_add)) as avg_customer_age_days
                FROM ' . _DB_PREFIX_ . 'customer';
        
        return Db::getInstance()->getRow($sql);
    }

    /**
     * Kunden-Export
     */
    public static function exportCustomers($format = 'csv')
    {
        $customers = self::getCustomers();
        
        if ($format === 'csv') {
            $filename = 'customers_' . date('Y-m-d_H-i-s') . '.csv';
            $filepath = _PS_DOWNLOAD_DIR_ . $filename;
            
            $fp = fopen($filepath, 'w');
            fputcsv($fp, ['ID', 'Firstname', 'Lastname', 'Email', 'Company', 'Date Added', 'Active']);
            
            foreach ($customers as $customer) {
                fputcsv($fp, [
                    $customer['id_customer'],
                    $customer['firstname'],
                    $customer['lastname'],
                    $customer['email'],
                    $customer['company'],
                    $customer['date_add'],
                    $customer['active']
                ]);
            }
            
            fclose($fp);
            
            return $filepath;
        }
        
        return $customers;
    }

    // Erweiterte Customer-Funktionen
    public function addWs($autodate = true, $null_values = false)
    {
        if (!$this->secure_key) {
            $this->secure_key = Tools::passwdGen(32);
        }

        if (!$this->id_lang) {
            $this->id_lang = Context::getContext()->language->id;
        }

        $success = $this->add($autodate, $null_values);
        if ($success && $this->groupBox) {
            $this->addGroups($this->groupBox);
        }

        return $success;
    }

    public function updateWs($null_values = false)
    {
        $success = $this->update($null_values);
        if ($success && $this->groupBox) {
            $this->addGroups($this->groupBox);
        }

        return $success;
    }

    public function getByEmailWithPassword($email, $plaintextPassword = null, $ignoreGuest = true)
    {
        $sql = 'SELECT c.*
                FROM ' . _DB_PREFIX_ . 'customer c
                WHERE c.email = "' . pSQL($email) . '"';

        if ($ignoreGuest) {
            $sql .= ' AND c.is_guest = 0';
        }

        if ($plaintextPassword) {
            $sql .= ' AND c.passwd = "' . pSQL(Tools::encrypt($plaintextPassword)) . '"';
        }

        $sql .= ' LIMIT 1';

        return Db::getInstance()->getRow($sql);
    }

    public static function isBanned($idCustomer)
    {
        $sql = 'SELECT c.id_customer
                FROM ' . _DB_PREFIX_ . 'customer c
                WHERE c.id_customer = ' . (int) $idCustomer . '
                AND c.active = 0';

        return Db::getInstance()->getValue($sql) ? true : false;
    }

    public static function customerHasAddress($idCustomer, $idAddress)
    {
        $sql = 'SELECT COUNT(*) FROM ' . _DB_PREFIX_ . 'address 
                WHERE id_customer = ' . (int) $idCustomer . ' 
                AND id_address = ' . (int) $idAddress;

        return (bool) Db::getInstance()->getValue($sql);
    }

    public static function resetStaticCache()
    {
        // Cache zurücksetzen
        return true;
    }

    public static function resetAddressCache($idCustomer = null, $idAddress = null)
    {
        // Adress-Cache zurücksetzen
        return true;
    }

    public function getSimpleAddresses($idLang = null)
    {
        if (!$idLang) {
            $idLang = Context::getContext()->language->id;
        }

        $sql = 'SELECT a.*, cl.name as country, s.name as state
                FROM ' . _DB_PREFIX_ . 'address a
                LEFT JOIN ' . _DB_PREFIX_ . 'country_lang cl ON (a.id_country = cl.id_country AND cl.id_lang = ' . (int) $idLang . ')
                LEFT JOIN ' . _DB_PREFIX_ . 'state s ON (a.id_state = s.id_state)
                WHERE a.id_customer = ' . (int) $this->id . '
                AND a.deleted = 0
                ORDER BY a.date_add DESC';

        return Db::getInstance()->executeS($sql);
    }

    public function getSimpleAddress($idAddress, $idLang = null)
    {
        if (!$idLang) {
            $idLang = Context::getContext()->language->id;
        }

        $sql = 'SELECT a.*, cl.name as country, s.name as state
                FROM ' . _DB_PREFIX_ . 'address a
                LEFT JOIN ' . _DB_PREFIX_ . 'country_lang cl ON (a.id_country = cl.id_country AND cl.id_lang = ' . (int) $idLang . ')
                LEFT JOIN ' . _DB_PREFIX_ . 'state s ON (a.id_state = s.id_state)
                WHERE a.id_address = ' . (int) $idAddress . '
                AND a.id_customer = ' . (int) $this->id . '
                AND a.deleted = 0';

        return Db::getInstance()->getRow($sql);
    }

    public function getSimpleAddressSql($idAddress = null, $idLang = null)
    {
        if (!$idLang) {
            $idLang = Context::getContext()->language->id;
        }

        $sql = 'SELECT a.*, cl.name as country, s.name as state
                FROM ' . _DB_PREFIX_ . 'address a
                LEFT JOIN ' . _DB_PREFIX_ . 'country_lang cl ON (a.id_country = cl.id_country AND cl.id_lang = ' . (int) $idLang . ')
                LEFT JOIN ' . _DB_PREFIX_ . 'state s ON (a.id_state = s.id_state)
                WHERE a.id_customer = ' . (int) $this->id . '
                AND a.deleted = 0';

        if ($idAddress) {
            $sql .= ' AND a.id_address = ' . (int) $idAddress;
        }

        $sql .= ' ORDER BY a.date_add DESC';

        return $sql;
    }

    public static function getAddressesTotalById($idCustomer)
    {
        $sql = 'SELECT COUNT(*) as total FROM ' . _DB_PREFIX_ . 'address 
                WHERE id_customer = ' . (int) $idCustomer . ' 
                AND deleted = 0';

        $result = Db::getInstance()->getRow($sql);
        return isset($result['total']) ? (int) $result['total'] : 0;
    }

    public static function checkPasswordStatic($idCustomer, $passwordHash)
    {
        $sql = 'SELECT passwd FROM ' . _DB_PREFIX_ . 'customer 
                WHERE id_customer = ' . (int) $idCustomer;

        $storedHash = Db::getInstance()->getValue($sql);

        if (!$storedHash) {
            return false;
        }

        return $storedHash === $passwordHash;
    }

    public static function searchByName($query, $limit = null)
    {
        $sql = 'SELECT c.* FROM ' . _DB_PREFIX_ . 'customer c
                WHERE (c.firstname LIKE "%' . pSQL($query) . '%" 
                OR c.lastname LIKE "%' . pSQL($query) . '%" 
                OR c.email LIKE "%' . pSQL($query) . '%")
                AND c.deleted = 0
                ORDER BY c.date_add DESC';

        if ($limit) {
            $sql .= ' LIMIT ' . (int) $limit;
        }

        return Db::getInstance()->executeS($sql);
    }

    public static function searchByIp($ip)
    {
        $sql = 'SELECT c.* FROM ' . _DB_PREFIX_ . 'customer c
                WHERE c.ip_registration_newsletter = "' . pSQL($ip) . '"
                ORDER BY c.date_add DESC';

        return Db::getInstance()->executeS($sql);
    }

    public function getLastEmails()
    {
        $sql = 'SELECT m.* FROM ' . _DB_PREFIX_ . 'message m
                WHERE m.id_customer = ' . (int) $this->id . '
                ORDER BY m.date_add DESC
                LIMIT 10';

        return Db::getInstance()->executeS($sql);
    }

    public function getLastConnections()
    {
        $sql = 'SELECT c.* FROM ' . _DB_PREFIX_ . 'connections c
                WHERE c.id_customer = ' . (int) $this->id . '
                ORDER BY c.date_add DESC
                LIMIT 10';

        return Db::getInstance()->executeS($sql);
    }

    public static function customerIdExistsStatic($idCustomer)
    {
        $sql = 'SELECT id_customer FROM ' . _DB_PREFIX_ . 'customer 
                WHERE id_customer = ' . (int) $idCustomer;

        return (bool) Db::getInstance()->getValue($sql);
    }

    public function updateGroupList($list)
    {
        $this->cleanGroups();
        $this->addGroups($list);

        return true;
    }

    public function cleanGroups()
    {
        return Db::getInstance()->delete('customer_group', 'id_customer = ' . (int) $this->id);
    }

    public function getBoughtProducts()
    {
        $sql = 'SELECT DISTINCT od.product_id, p.*, pl.*
                FROM ' . _DB_PREFIX_ . 'order_detail od
                LEFT JOIN ' . _DB_PREFIX_ . 'orders o ON (od.id_order = o.id_order)
                LEFT JOIN ' . _DB_PREFIX_ . 'product p ON (od.product_id = p.id_product)
                LEFT JOIN ' . _DB_PREFIX_ . 'product_lang pl ON (p.id_product = pl.id_product AND pl.id_lang = ' . (int) Context::getContext()->language->id . ')
                WHERE o.id_customer = ' . (int) $this->id . '
                AND o.valid = 1
                ORDER BY o.date_add DESC';

        return Db::getInstance()->executeS($sql);
    }

    public static function getDefaultGroupId($idCustomer)
    {
        $sql = 'SELECT id_default_group FROM ' . _DB_PREFIX_ . 'customer 
                WHERE id_customer = ' . (int) $idCustomer;

        return (int) Db::getInstance()->getValue($sql);
    }

    public static function getCurrentCountry($idCustomer, $cart = null)
    {
        if (!$cart) {
            $cart = Context::getContext()->cart;
        }

        $idCountry = $cart->id_country;
        if (!$idCountry) {
            $address = new Address($cart->id_address_delivery);
            $idCountry = $address->id_country;
        }

        return $idCountry;
    }

    public function isGuest()
    {
        return (bool) $this->is_guest;
    }

    public function transformToCustomer($idLang, $password = null)
    {
        if ($this->is_guest) {
            $this->is_guest = false;
            $this->passwd = $password ? Tools::encrypt($password) : Tools::encrypt(Tools::passwdGen());
            $this->secure_key = Tools::passwdGen(32);
            $this->active = true;

            if ($this->update()) {
                $this->sendWelcomeEmail($idLang, true);
                return true;
            }
        }

        return false;
    }

    public function sendWelcomeEmail($idLang, $sendPasswordLink = false)
    {
        $templateVars = [
            '{firstname}' => $this->firstname,
            '{lastname}' => $this->lastname,
            '{email}' => $this->email,
        ];

        if ($sendPasswordLink) {
            $templateVars['{reset_link}'] = Context::getContext()->link->getPageLink('password', true, $idLang, 'token=' . $this->secure_key);
        }

        return Mail::Send(
            $idLang,
            'account',
            Mail::l('Welcome!', $idLang),
            $templateVars,
            $this->email,
            $this->firstname . ' ' . $this->lastname,
            null,
            null,
            null,
            null,
            _PS_MAIL_DIR_,
            false,
            null,
            null
        );
    }

    public function setWsPasswd($passwd)
    {
        $this->passwd = Tools::encrypt($passwd);
        $this->last_passwd_gen = date('Y-m-d H:i:s', time());

        return $this->update();
    }

    public function isLogged($withGuest = false)
    {
        if (!$withGuest && $this->is_guest) {
            return false;
        }

        return (bool) $this->id;
    }

    public function logout()
    {
        $this->logged = false;
        return $this->update();
    }

    public function mylogout()
    {
        $this->logout();
        Context::getContext()->cookie->logout();
    }

    public function getLastEmptyCart($withOrder = true)
    {
        $sql = 'SELECT c.* FROM ' . _DB_PREFIX_ . 'cart c
                WHERE c.id_customer = ' . (int) $this->id . '
                AND c.id_cart NOT IN (SELECT id_cart FROM ' . _DB_PREFIX_ . 'orders)';

        if (!$withOrder) {
            $sql .= ' AND c.id_cart NOT IN (SELECT id_cart FROM ' . _DB_PREFIX_ . 'orders)';
        }

        $sql .= ' ORDER BY c.date_add DESC LIMIT 1';

        return Db::getInstance()->getRow($sql);
    }

    public function getOutstanding()
    {
        $sql = 'SELECT SUM(total_paid) as total FROM ' . _DB_PREFIX_ . 'orders 
                WHERE id_customer = ' . (int) $this->id . ' 
                AND valid = 1';

        $result = Db::getInstance()->getRow($sql);
        return isset($result['total']) ? (float) $result['total'] : 0.0;
    }

    public function getWebserviceObjectList($sqlJoin, $sqlFilter, $sqlSort, $sqlLimit)
    {
        $sql = 'SELECT DISTINCT c.id_customer FROM ' . _DB_PREFIX_ . 'customer c' . $sqlJoin . ' WHERE 1 ' . $sqlFilter;
        if ($sqlSort) {
            $sql .= $sqlSort;
        }
        if ($sqlLimit) {
            $sql .= $sqlLimit;
        }

        return Db::getInstance()->executeS($sql);
    }

    public function stampResetPasswordToken()
    {
        $this->reset_password_token = Tools::passwdGen(32);
        $this->reset_password_validity = date('Y-m-d H:i:s', strtotime('+1 hour'));

        return $this->update();
    }

    public function hasRecentResetPasswordToken()
    {
        if (!$this->reset_password_token || !$this->reset_password_validity) {
            return false;
        }

        $validity = strtotime($this->reset_password_validity);
        return $validity > time();
    }

    public function getValidResetPasswordToken()
    {
        if ($this->hasRecentResetPasswordToken()) {
            return $this->reset_password_token;
        }

        return null;
    }

    public function removeResetPasswordToken()
    {
        $this->reset_password_token = null;
        $this->reset_password_validity = null;

        return $this->update();
    }
}