"""Verify: Reverse LIKE fallback — DB as geography dictionary."""
import asyncio
import sys

sys.stdout.reconfigure(encoding='utf-8')
from common.starrocks_connection import get_db_connection

TABLE = "shared_source.chatbot_rsa_store_schedule_with_text_embedding"

def tokenize(location):
    clean = location.lower().strip()
    for p in ["quận ", "huyện ", "tỉnh ", "thành phố ", "tp. ", "tp "]:
        clean = clean.replace(p, "")
    clean = clean.strip()
    tokens = list(dict.fromkeys(t for t in clean.replace(',', ' ').split() if t.strip()))
    return clean, tokens

def build(where):
    return f"""SELECT store_name, city, state FROM {TABLE} WHERE {where} ORDER BY state, city, store_name LIMIT 20"""

async def search(db, location):
    clean, tokens = tokenize(location)
    if not tokens:
        return clean, tokens, "none", []
    
    text_col = "LOWER(concat_ws(' ', store_name, address, city, state))"
    
    # Step 1: AND
    and_conds = [f"{text_col} LIKE '%{tk}%'" for tk in tokens]
    results = await db.execute_query_async(build(' AND '.join(and_conds)))
    method = "AND"
    
    # Step 2: Reverse LIKE fallback
    if not results and len(tokens) >= 2:
        city_stripped = """LOWER(TRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
            city, 'Quận ', ''), 'Huyện ', ''), 'Thành phố ', ''), 'Thị xã ', ''), 'TP. ', '')))"""
        state_lower = "LOWER(TRIM(state))"
        fallback = f"""
            (LOCATE({city_stripped}, '{clean}') > 0 AND LENGTH({city_stripped}) > 1)
            OR
            (LOCATE({state_lower}, '{clean}') > 0 AND LENGTH({state_lower}) > 1)
        """
        results = await db.execute_query_async(build(fallback))
        method = "REVERSE-LIKE"
    
    return clean, tokens, method, results

async def main():
    db = get_db_connection()
    cases = [
        "Hà Đông",                    # simple district
        "Hà Đông Hà Nội",             # district + city
        "Hà Đông Cầu Giấy",           # TWO districts!
        "Hà Đông Nguyễn Trãi",        # district + street (not in DB)
        "Cầu Giấy",                   # simple
        "Đà Nẵng",                    # province
        "Vincom",                      # brand name
        "Quận Hoàng Mai",             # with prefix
        "Hoàng Mai Thanh Xuân",       # TWO districts
        "Bắc Ninh Hải Dương",         # TWO provinces
    ]
    
    for loc in cases:
        clean, tokens, method, rows = await search(db, loc)
        s = "✅" if rows else "❌"
        print(f"{s} '{loc}' → tokens={tokens} → [{method}] → {len(rows)} stores")
        for r in rows[:3]:
            print(f"   🏪 {r['store_name']} | {r['city']} | {r['state']}")
        if len(rows) > 3:
            print(f"   ... +{len(rows)-3} more")
        print()

asyncio.run(main())
