
    ytQhr                     V    d dl Z d dlZd dlmZ  e        d ZdZdefdZdee   fdZ	y)	    N)load_dotenvc                  8   t        j                  t        j                  d      t        j                  d      t        j                  d      t        j                  d      t	        t        j                  d            t         j
                  j                        S )NDB_HOSTDB_USERDB_PASSWORDDB_NAMEDB_PORT)hostuserpassworddatabaseportcursorclass)pymysqlconnectosgetenvintcursors
DictCursor     '/var/www/html/aweda_api/app/database.pyget_connectionr      sa    ??YYy!YYy!=)9%9%&OO.. r   ur  
SELECT 
    cpe.sku AS sku,
    name.value AS name,
    CASE 
        WHEN status.value = 1 THEN 'Ativo'
        ELSE 'Inativo'
    END AS status,
    COALESCE(stock.qty, 0) AS total_quantity_in_stock,
    COALESCE(price.value, 0) AS preco_normal,

    -- Escolhe o menor valor disponível: promoção de regra > special_price > preço normal
    COALESCE(promo_price.rule_price, special_price.value, price.value) AS preco_com_desconto,

    short_desc.value AS short_description,
    full_desc.value AS full_description,
    CONCAT('https://aweda.com.br/', url_key.value) AS product_url

FROM u217871916_aweda.catalog_product_entity AS cpe

-- Nome
LEFT JOIN u217871916_aweda.catalog_product_entity_varchar AS name 
    ON name.entity_id = cpe.entity_id 
    AND name.attribute_id = (
        SELECT attribute_id FROM u217871916_aweda.eav_attribute 
        WHERE attribute_code = 'name' 
        AND entity_type_id = (
            SELECT entity_type_id FROM u217871916_aweda.eav_entity_type 
            WHERE entity_type_code = 'catalog_product'
        )
    )

-- Status
LEFT JOIN u217871916_aweda.catalog_product_entity_int AS status 
    ON status.entity_id = cpe.entity_id 
    AND status.attribute_id = (
        SELECT attribute_id FROM u217871916_aweda.eav_attribute 
        WHERE attribute_code = 'status' 
        AND entity_type_id = (
            SELECT entity_type_id FROM u217871916_aweda.eav_entity_type 
            WHERE entity_type_code = 'catalog_product'
        )
    )

-- Preço
LEFT JOIN u217871916_aweda.catalog_product_entity_decimal AS price 
    ON price.entity_id = cpe.entity_id 
    AND price.attribute_id = (
        SELECT attribute_id FROM u217871916_aweda.eav_attribute 
        WHERE attribute_code = 'price' 
        AND entity_type_id = (
            SELECT entity_type_id FROM u217871916_aweda.eav_entity_type 
            WHERE entity_type_code = 'catalog_product'
        )
    )
    AND price.store_id = 0

-- Special price
LEFT JOIN u217871916_aweda.catalog_product_entity_decimal AS special_price 
    ON special_price.entity_id = cpe.entity_id 
    AND special_price.attribute_id = (
        SELECT attribute_id FROM u217871916_aweda.eav_attribute 
        WHERE attribute_code = 'special_price' 
        AND entity_type_id = (
            SELECT entity_type_id FROM u217871916_aweda.eav_entity_type 
            WHERE entity_type_code = 'catalog_product'
        )
    )
    AND special_price.store_id = 0

-- Preço com regra de catálogo
LEFT JOIN (
    SELECT product_id, MIN(rule_price) AS rule_price
    FROM u217871916_aweda.catalogrule_product_price
    WHERE rule_date = CURDATE()
    GROUP BY product_id
) AS promo_price ON promo_price.product_id = cpe.entity_id

-- Estoque
LEFT JOIN u217871916_aweda.cataloginventory_stock_item AS stock 
    ON stock.product_id = cpe.entity_id

-- URL do produto
LEFT JOIN u217871916_aweda.catalog_product_entity_varchar AS url_key 
    ON url_key.entity_id = cpe.entity_id 
    AND url_key.attribute_id = (
        SELECT attribute_id FROM u217871916_aweda.eav_attribute 
        WHERE attribute_code = 'url_key' 
        AND entity_type_id = (
            SELECT entity_type_id FROM u217871916_aweda.eav_entity_type 
            WHERE entity_type_code = 'catalog_product'
        )
    )

-- Descrição curta
LEFT JOIN u217871916_aweda.catalog_product_entity_text AS short_desc 
    ON short_desc.entity_id = cpe.entity_id 
    AND short_desc.attribute_id = (
        SELECT attribute_id FROM u217871916_aweda.eav_attribute 
        WHERE attribute_code = 'short_description' 
        AND entity_type_id = (
            SELECT entity_type_id FROM u217871916_aweda.eav_entity_type 
            WHERE entity_type_code = 'catalog_product'
        )
    )

-- Descrição completa
LEFT JOIN u217871916_aweda.catalog_product_entity_text AS full_desc 
    ON full_desc.entity_id = cpe.entity_id 
    AND full_desc.attribute_id = (
        SELECT attribute_id FROM u217871916_aweda.eav_attribute 
        WHERE attribute_code = 'description' 
        AND entity_type_id = (
            SELECT entity_type_id FROM u217871916_aweda.eav_entity_type 
            WHERE entity_type_code = 'catalog_product'
        )
    )
skuc                 *   t         dz   }t               }	 |j                         5 }|j                  || f       |j	                         cd d d        |j                          S # 1 sw Y   nxY w	 |j                          y # |j                          w xY w)Nz1 WHERE cpe.sku = %s AND status.value = 1 LIMIT 1;)
BASE_QUERYr   cursorexecutefetchoneclose)r   queryconnr   s       r   fetch_produto_por_skur$      s~    LLED[[] 	%fNN53&)??$	% 	% 	

		% 	% 	% 	



s"   B  #A"	B  "A+'B   Btermosc                    dj                  | D cg c]  }d c}      }g }| D ])  }d|j                          d}|j                  ||g       + t        d| dz   }t	               }	 |j                         5 }|j                  ||       |j                         cd d d        |j                          S c c}w # 1 sw Y   nxY w	 |j                          y # |j                          w xY w)Nz AND z=(LOWER(name.value) LIKE %s OR LOWER(full_desc.value) LIKE %s)%z WHERE status.value = 1 AND z	 LIMIT 5;)	joinlowerextendr   r   r   r   fetchallr!   )	r%   _filtrosvalorestermopadraor"   r#   r   s	            r   fetch_produtos_por_termosr1      s    llQWLMG G G )U[[]O1%'() 7y	JJED[[] 	%fNN5'*??$	% 	% 	

!	% 	% 	% 	



s)   	B4(C 8"B9	C 9C>C C))
r   r   dotenvr   r   r   strr$   listr1   r   r   r   <module>r5      s=    	   u
ns d3i r   