#!/usr/bin/env python3
"""
Download images for products in the products_master table
"""
import sqlite3
import requests
import hashlib
import os
from pathlib import Path
import time
import json

# Database path
DB_PATH = Path("/Users/jinjunqian/PycharmProjects/订单处理新版/ordersys/backend/ordersys.db")
IMAGES_DIR = Path("/Users/jinjunqian/PycharmProjects/订单处理新版/ordersys/backend/images")

def download_image(url, save_path):
    """Download image from URL to local path"""
    try:
        # Headers to mimic browser
        headers = {
            'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36',
            'Accept': 'image/webp,image/apng,image/*,*/*;q=0.8',
            'Referer': 'https://www.taobao.com/',
        }
        
        response = requests.get(url, headers=headers, timeout=30)
        if response.status_code == 200:
            with open(save_path, 'wb') as f:
                f.write(response.content)
            return True
    except Exception as e:
        print(f"Error downloading {url}: {e}")
    return False

def main():
    print(f"Connecting to database: {DB_PATH}")
    
    if not DB_PATH.exists():
        print(f"Error: Database not found at {DB_PATH}")
        return
    
    # Create images directory if not exists
    IMAGES_DIR.mkdir(exist_ok=True)
    
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    
    try:
        # Get products that need images (focusing on LA procurement for now)
        cursor.execute("""
            SELECT id, 线上宝贝名称, main_image_path, original_image_urls, image_download_status
            FROM products_master
            WHERE image_download_status = 'pending' 
                AND main_image_path IS NOT NULL
                AND main_image_path LIKE 'http%'
                AND procurement_method = 'LA'
            LIMIT 50
        """)
        
        products = cursor.fetchall()
        print(f"Found {len(products)} products with pending images")
        
        success_count = 0
        for product_id, name, image_url, original_urls, status in products:
            print(f"\nProcessing: {name[:50]}...")
            
            # Generate filename from URL hash
            url_hash = hashlib.md5(image_url.encode()).hexdigest()[:16]
            file_ext = '.jpg'
            if '.png' in image_url.lower():
                file_ext = '.png'
            elif '.gif' in image_url.lower():
                file_ext = '.gif'
            
            filename = f"{url_hash}{file_ext}"
            local_path = IMAGES_DIR / filename
            
            # Download image
            if download_image(image_url, local_path):
                # Update database with local path
                cursor.execute("""
                    UPDATE products_master 
                    SET main_image_path = ?,
                        local_image_paths = ?,
                        image_download_status = 'completed'
                    WHERE id = ?
                """, (f"images/{filename}", json.dumps([f"images/{filename}"]), product_id))
                
                conn.commit()
                success_count += 1
                print(f"✓ Downloaded and saved as {filename}")
            else:
                print(f"✗ Failed to download")
            
            # Small delay to avoid overwhelming the server
            time.sleep(0.5)
        
        print(f"\nCompleted: {success_count}/{len(products)} images downloaded successfully")
        
    except Exception as e:
        print(f"Error: {e}")
        conn.rollback()
    finally:
        conn.close()

if __name__ == "__main__":
    main()