import json
from fastapi import APIRouter, HTTPException
import httpx
import logging
from common.cache import redis_cache
from common.sqlite_db import sqlite_db

router = APIRouter(prefix="/api/stock-cache", tags=["Stock Cache"])
logger = logging.getLogger(__name__)

# Fake list of SKUs for testing
TEST_SKUS = [
    "6TW25W005", "6TW25W005-SB118", "8TE24A003", "8TE24A003-SW001",
    "6TS24W006", "6DS24W002", "8BA23W001", "8TW23A004", "1TW23W001"
]

@router.post("/sync")
async def sync_stock(skus: list[str] = None):
    """
    Worker route to fetch stock from Middleware and push to Redis.
    In real usage, this might be triggered by a Cron job and fetch 2000 SKUs.
    For this demo, it takes chunks of 100 SKUs safely.
    """
    if not skus:
        try:
            # Fetch real SKUs from the SQLite database
            rows = await sqlite_db.fetch_all("SELECT DISTINCT product_color_code FROM sr__test_db__magento_product_dimension_with_text_embedding WHERE product_color_code IS NOT NULL LIMIT 2000")
            if rows:
                skus = [r["product_color_code"] for r in rows]
            else:
                skus = TEST_SKUS
        except Exception as e:
            logger.error(f"Failed to fetch SKUs from local DB: {e}")
            skus = TEST_SKUS
    client = redis_cache.get_client()
    if not client:
        raise HTTPException(status_code=500, detail="Redis is not enabled or not connected")

    chunk_size = 100
    total_synced = 0
    errors = []

    async with httpx.AsyncClient(timeout=10.0) as http_client:
        for i in range(0, len(skus), chunk_size):
            chunk = skus[i:i + chunk_size]
            csv_skus = ",".join(chunk)
            
            try:
                url = "https://canifa.com/v1/middleware/stock_get_stock_list_parent"
                # API expects GET ?skus=xxx
                response = await http_client.get(url, params={"skus": csv_skus})
                response.raise_for_status()
                data = response.json()
                
                # Assumed CANIFA API structure: {"result": [{"sku": "xxxx", "quantity": 10}, ...]}
                pipe = client.pipeline()
                
                if isinstance(data, dict) and "result" in data and isinstance(data["result"], list):
                    for item in data["result"]:
                        sku = item.get("sku") or item.get("product_color_code", "")
                        if sku:
                            # Save the entire dictionary as a JSON string so check_is_stock can read it directly
                            pipe.setex(f"stock:{sku}", 300, json.dumps(item, ensure_ascii=False))
                            total_synced += 1
                
                # Executing pipeline
                if total_synced > 0:
                    await pipe.execute()
                    
            except httpx.HTTPStatusError as e:
                msg = f"HTTP error {e.response.status_code} fetching chunk {i}: {e.response.text[:200]}"
                logger.error(msg)
                errors.append(msg)
            except httpx.ConnectError as e:
                msg = f"Connection error fetching chunk {i}: {repr(e)}"
                logger.error(msg)
                errors.append(msg)
            except asyncio.TimeoutError:
                msg = f"Timeout fetching chunk {i}"
                logger.error(msg)
                errors.append(msg)
            except Exception as e:
                msg = f"Unexpected error fetching chunk {i}: {repr(e)}"
                logger.error(msg)
                errors.append(msg)
                
    return {
        "message": "Sync completed",
        "total_skus_processed": min(total_synced, len(skus)), # roughly
        "errors": errors
    }

@router.get("/status")
async def get_test_stock(sku: str):
    """
    Check stock of a specific SKU from Redis (Ultra fast 1ms).
    """
    client = redis_cache.get_client()
    if not client:
        raise HTTPException(status_code=500, detail="Redis is not connected")
        
    val = await client.get(f"stock:{sku}")
    if val is None:
        return {"sku": sku, "cached": False, "status": 0, "details": None}
        
    try:
        data = json.loads(val)
        status = data.get("status", 0)
        return {"sku": sku, "cached": True, "status": status, "details": data}
    except Exception:
        # Fallback if old string data is cached
        return {"sku": sku, "cached": True, "status": val, "details": None}

@router.get("/keys")
async def get_all_cached_keys():
    client = redis_cache.get_client()
    if not client:
         return {"total": 0, "keys": []}
         
    keys = await client.keys("stock:*")
    return {"total": len(keys), "keys": keys}

@router.get("/list")
async def list_stock_cache(limit: int = 500):
    """
    List all cached stock data using MGET for performance, 
    enriched with metadata from SQLite DB.
    """
    client = redis_cache.get_client()
    if not client:
        return []
        
    keys = await client.keys("stock:*")
    if not keys:
        return []
        
    # Sort keys for consistent display
    keys.sort()
    
    # Limit for safety
    keys = keys[:limit]
    
    values = await client.mget(keys)
    
    # Extract SKUs to fetch metadata from SQLite in one batch
    skus = [key.replace("stock:", "") for key in keys]
    
    # Fetch metadata from SQLite
    metadata_map = {}
    if skus:
        # We search by product_color_code or internal_ref_code
        # Using a list of SKUs in a single query
        placeholders = ",".join(["?"] * len(skus))
        query = f"""
            SELECT product_color_code, product_name, product_image_url_thumbnail, sale_price, original_price, product_line_vn
            FROM sr__test_db__magento_product_dimension_with_text_embedding
            WHERE product_color_code IN ({placeholders})
        """
        try:
            from common.sqlite_db import sqlite_db
            rows = await sqlite_db.fetch_all(query, tuple(skus))
            for row in rows:
                metadata_map[row['product_color_code']] = dict(row)
        except Exception as e:
            logger.error(f"Error fetching product metadata: {e}")

    result = []
    for key, val in zip(keys, values):
        sku = key.replace("stock:", "")
        meta = metadata_map.get(sku, {})
        
        try:
            data = json.loads(val) if val else {}
            # Normalize structure for frontend
            result.append({
                "sku": sku,
                "status": data.get("status", "0"),
                "total_quantity": data.get("total_quantity", 0),
                "last_sync": data.get("sync_time", "N/A"),
                "product_name": meta.get("product_name") or sku,
                "image_url": meta.get("product_image_url_thumbnail"),
                "price": meta.get("sale_price"),
                "original_price": meta.get("original_price"),
                "product_line": meta.get("product_line_vn"),
                "raw": data
            })
        except:
            result.append({
                "sku": sku,
                "status": val,
                "total_quantity": 0,
                "last_sync": "Legacy",
                "product_name": meta.get("product_name") or sku,
                "image_url": meta.get("product_image_url_thumbnail"),
                "price": meta.get("sale_price"),
                "raw": {}
            })
            
    return result

import asyncio

async def stock_cache_worker_loop():
    """
    Background worker to automatically sync stock to Redis every 3 minutes.
    """
    while True:
        try:
            logger.info("🔄 [Cron] Bắt đầu tự động đồng bộ Stock Cache (mỗi 3 phút)...")
            
            # Fetch all SKUs or a specific hot list from your Database here.
            # Currently falling back to sync_stock() with TEST_SKUS list.
            result = await sync_stock(None)
            
            logger.info(f"✅ [Cron] Đồng bộ Stock Cache xong! Số mã đã xử lý: {result['total_skus_processed']}")
        except asyncio.CancelledError:
            logger.info("🛑 [Cron] Tắt cron job đồng bộ Stock Cache")
            break
        except Exception as e:
            logger.error(f"❌ [Cron] Lỗi đồng bộ Stock Cache: {e}")
        
        # Chạy lại sau mỗi 3 phút (180 giây)
        await asyncio.sleep(180)
