<?php

declare(strict_types=1);

namespace App\Model;

use Mosaico\Model\BaseModel;

class CustomersModel extends BaseModel
{

    public function getCustomerCreate(): bool|object
    {
        $query = $this->connect()->query("
        	SELECT 
            A.id_customer,
            A.id_magento,
            A.firstname,
            A.lastname,
            A.email,
            A.password,
            A.id_group_bc
			FROM customers A
            WHERE A.is_create = 1
            ORDER BY A.id_magento
		");
        return $query;
    }

    public function getCustomerUpdate(): bool|object
    {
        $query = $this->connect()->query("
            SELECT 
            A.id_customer,
            A.firstname,
            A.lastname,
            A.email,
            A.password,
            A.id_group_bc
            FROM customers A
            WHERE A.is_update = 1
        ");
        return $query;
    }

    public function updateIdBigcommerce(int $id_customer, int $id_bigcommerce): void
    {
        $query = $this->connect()->query("
            UPDATE customers SET
            id_bigcommerce      = " . $id_bigcommerce . ",
            is_create           = 0
            WHERE id_customer   = " . $id_customer . "
            ");
    }

    public function updateStatusBigcommerce(int $id_customer, int $status): void
    {
        $query = $this->connect()->query("
            UPDATE customers SET
            is_create           = " . $status . "
            WHERE id_customer   = " . $id_customer . "
            ");
    }

    public function updateStatusErrorBigcommerce(int $id_customer, int $status, string $error): void
    {
        $query = $this->connect()->query("
            UPDATE customers SET
            is_create           = 2,
            log_error           = '" . $error . "'
            WHERE id_customer   = " . $id_customer . "
            ");
    }

    public function updateIsUpdate(int $id_customer): void
    {
        $query = $this->connect()->query("
            UPDATE customers SET
            is_update           = 0
            WHERE id_customer   = " . $id_customer . "
            ");
    }

    
    
    public function getCustomerAddressCreate(): bool|object
    {
        $query = $this->connect()->query("
            SELECT 
            id_customer,
            numero_cliente,
            id_bigcommerce,
            first_name,
            is_address_create
            FROM clientes
            WHERE is_address_create = 1
            AND id_bigcommerce > 0
            ORDER BY numero_cliente
            LIMIT 400
        ");
        return $query;
    }

    public function updateCustomerAddressIsCreate(int $id_customer): void
    {
        $query = $this->connect()->query("
            UPDATE clientes SET
            is_address_create  = 0
            WHERE id_customer   = " . $id_customer . "
            ");
    }

    public function getAddressCreate(): bool|object
    {
        $query = $this->connect()->query("
            SELECT 
            id_direccion,
            B.id_bigcommerce as customer_id,
            A.id_bigcommerce,
            A.numero_cliente,
            A.colonia,
            A.ciudad,
            A.telefono_1,
            clave_direccion,
            identificador,
            IF(
              LENGTH(A.calle) < 3,
              'sincalle',
              A.calle
            ) AS calle,
            B.first_name,
            B.last_name,
            C.estado,
            D.code_iso_2,
            IF(
              LENGTH(A.codigo_postal) < 3,
              '00000',
              A.codigo_postal
            ) AS codigo_postal
            FROM direcciones A
            JOIN clientes B
            ON A.numero_cliente = B.numero_cliente
            JOIN estados C
            ON A.id_estado = C.id_estado
            JOIN paises D
            ON A.id_pais = D.id_pais
            WHERE A.is_create = 1
            AND B.id_bigcommerce IS NOT NULL
            AND A.id_bigcommerce IS NULL
            AND B.is_deleted = 0
            ");
        return $query;

    }

    public function updateAddressIdBigCommerce(
        int $id_direccion,
        int $id_bigcommerce
    ): void {
        $query = $this->connect()->query("
            UPDATE direcciones SET
            id_bigcommerce      = " . $id_bigcommerce . ",
            is_create           = 0
            WHERE id_direccion   = " . $id_direccion . "
            ");
    }

    public function updateAddressStatusBigcommerce(int $id_direccion, int $status): void
    {
        $query = $this->connect()->query("
            UPDATE direcciones SET
            is_create           = " . $status . "
            WHERE id_direccion   = " . $id_direccion . "
            ");
    }

    public function getCustomerAddressUpdate(): bool|object
    {
        $query = $this->connect()->query("
            SELECT 
            cd.id_direccion,
            cd.id_bigcommerce,
            c.first_name,
            c.last_name,
            IF(
              LENGTH(cd.calle) < 3,
              'sincalle',
              cd.calle
            ) AS calle,
            cd.colonia,
            cd.ciudad,
            ed.estado,
            p.code_iso_2,
            cd.telefono_1,
            IF(
              LENGTH(cd.codigo_postal) < 3,
              '00000',
              cd.codigo_postal
            ) AS codigo_postal
            FROM direcciones cd
            LEFT JOIN clientes c
            ON c.numero_cliente = cd.numero_cliente
            LEFT JOIN estados ed
            ON cd.id_estado = ed.id_estado
            LEFT JOIN paises p
            ON cd.id_pais = p.id_pais
            WHERE cd.is_update = 1
            AND cd.id_bigcommerce IS NOT NULL
            ");

        return $query;
    }

    public function updateCustomerAddressIsUpdate(int $id_direccion): void
    {
        $query = $this->connect()->query("
            UPDATE direcciones SET
            is_update  = 0
            WHERE id_direccion   = " . $id_direccion . "
            ");
    }

    public function getCustomerAddressDelete(): bool|object
    {
        $query = $this->connect()->query("
            SELECT 
            id_bigcommerce
            FROM direcciones
            WHERE is_deleted = 1
            AND id_bigcommerce IS NOT NULL
            ");

        return $query;
    }

    public function getPaises(): bool|object
    {
        $query = $this->connect()->query("
            SELECT id_pais,pais FROM paises
            ");

        return $query;
    }

    public function getEstados(): bool|object
    {
        $query = $this->connect()->query("
            SELECT id_estado,id_pais,estado FROM estados
            ");

        return $query;
    }

    public function getEstadosPais(int $id_pais): bool|object
    {
        $query = $this->connect()->query("
            SELECT id_estado,id_pais,estado 
            FROM estados
            WHERE id_pais = " . $id_pais . "
            ");

        return $query;
    }

    public function updateDireccion(array $direccion): bool|object
    {
        $query = $this->connect()->query(
            "UPDATE direcciones  SET
            calle           = '" . $direccion['calle'] . "',
            colonia         = '" . $direccion['colonia'] . "',
            id_pais         = '" . $direccion['id_pais'] . "',
            id_estado       = '" . $direccion['id_estado'] . "',
            codigo_postal   = '" . $direccion['codigo_postal'] . "',
            is_create       = " . $direccion['is_create'] . ",
            is_update       = " . $direccion['is_update'] . "
            WHERE id_direccion  = " . $direccion['id_direccion'] . "
            "
        );
        return $query;
    }


    public function getCustomersError(): bool|object
    {
        $query = $this->connect()->query(
            "SELECT 
            id_customer,
            numero_cliente,
            first_name,
            last_name,
            email,
            is_create,
            is_update,
            log_error
            FROM clientes
            WHERE is_create = 2
            "
        );
        return $query;
    }

    public function getClientesDelete(): bool|object
    {
        $query = $this->connect()->query("
            SELECT id_bigcommerce, id_customer FROM
            customers
            WHERE is_delete = 1
            ");
        return $query;
    }

    public function updateClienteDeleted(int $id_bigcommerce): void
    {
        $query = $this->connect()->query("
            UPDATE customers SET
              is_delete = 0,
              is_deleted = 1
              WHERE id_bigcommerce = " . $id_bigcommerce . "
            ");
    }
}