import sqlite3
import logging

logging.basicConfig(level=logging.INFO, format="%(asctime)s [%(levelname)s] %(message)s")
logger = logging.getLogger(__name__)

def update_sqlite_colors():
    db_path = "D:\\cnf\\chatbot-canifa-feedback\\backend\\database\\canifa_ai_dump.sqlite"
    logger.info(f"Connecting to local SQLite: {db_path}")
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    query = """
    UPDATE sr__test_db__magento_product_dimension_with_text_embedding 
    SET description_data_full = json_set(
        description_data_full, 
        '$.mau_sac', 
        (
            SELECT GROUP_CONCAT(DISTINCT master_color) 
            FROM sr__test_db__magento_product_dimension_with_text_embedding sub 
            WHERE sub.magento_ref_code = sr__test_db__magento_product_dimension_with_text_embedding.magento_ref_code
            AND sub.master_color IS NOT NULL 
            AND sub.master_color != ''
        )
    )
    WHERE description_data_full IS NOT NULL 
    AND json_valid(description_data_full) = 1;
    """
    
    logger.info("Executing UPDATE query with GROUP_CONCAT and json_valid check...")
    try:
        cursor.execute("BEGIN TRANSACTION")
        cursor.execute(query)
        affected_rows = cursor.rowcount
        conn.commit()
        logger.info(f"✅ Successfully updated {affected_rows} rows with concatenated colors!")
        
        # Test 1 mã
        cursor.execute("""
            SELECT magento_ref_code, json_extract(description_data_full, '$.mau_sac') 
            FROM sr__test_db__magento_product_dimension_with_text_embedding 
            WHERE magento_ref_code = '1AK25C001' LIMIT 1
        """)
        sample = cursor.fetchone()
        logger.info(f"🔍 Sample result for 1AK25C001: {sample}")
        
    except Exception as e:
        conn.rollback()
        logger.error(f"❌ Error during update: {e}")
    finally:
        conn.close()

if __name__ == "__main__":
    update_sqlite_colors()
