import os
import pymysql
from dotenv import load_dotenv

load_dotenv()

def get_connection():
    return pymysql.connect(
        host=os.getenv("DB_HOST"),
        user=os.getenv("DB_USER"),
        password=os.getenv("DB_PASSWORD"),
        database=os.getenv("DB_NAME"),
        port=int(os.getenv("DB_PORT")),
        cursorclass=pymysql.cursors.DictCursor
    )

# SQL base sem WHERE final
BASE_QUERY = """
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'
        )
    )
"""

def fetch_produto_por_sku(sku: str):
    query = BASE_QUERY + " WHERE cpe.sku = %s AND status.value = 1 LIMIT 1;"
    conn = get_connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute(query, (sku,))
            return cursor.fetchone()
    finally:
        conn.close()

def fetch_produtos_por_termos(termos: list[str]):
    filtros = " AND ".join([
        "(LOWER(name.value) LIKE %s OR LOWER(full_desc.value) LIKE %s)" for _ in termos
    ])
    valores = []
    for termo in termos:
        padrao = f"%{termo.lower()}%"
        valores.extend([padrao, padrao])

    query = BASE_QUERY + f" WHERE status.value = 1 AND {filtros} LIMIT 5;"

    conn = get_connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute(query, valores)
            return cursor.fetchall()
    finally:
        conn.close()