import requests
import sqlite3
import os
import re

# Credentials from sync_inventory.py
WC_USERNAME = "justinhenshaw03@gmail.com"
WC_PASSWORD = "zrzV 4GEe Cph4 2gU0 5mub BbCW"
WC_URL = "https://seedvault.market/wp-json/wc/v3/products"
DB_PATH = os.path.join(os.path.dirname(__file__), 'inventory.db')

def clean_name(name):
    """
    Normalizes strings for better comparison:
    - Lowercase
    - Remove non-alphanumeric (except spaces)
    - Remove common filler words
    """
    if not name:
        return ""
    name = name.lower()
    # Remove punctuation and special characters
    name = re.sub(r'[^a-z0-9\s]', '', name)
    # Remove common filler words that add noise to matching
    fillers = {'seeds', 'seed', 'pack', 'packet', 'starter', 'kit', 'edition', 'the', 'a', 'an'}
    words = name.split()
    cleaned_words = [w for w in words if w not in fillers]
    return " ".join(cleaned_words).strip()

def fetch_skus_from_wc():
    print("Fetching products from WooCommerce...")
    response = requests.get(WC_URL, auth=(WC_USERNAME, WC_PASSWORD))
    
    if response.status_code != 200:
        print(f"Error fetching from WC: {response.status_code} - {response.text}")
        return None
    
    return response.json()

def sync_skus_to_local():
    wc_products = fetch_skus_from_wc()
    if wc_products is None:
        return

    # Build a list of dicts with normalized names
    wc_data = []
    for wp in wc_products:
        name = wp.get('name', '').strip()
        sku = wp.get('sku')
        if name and sku:
            wc_data.append({
                'clean_name': clean_name(name),
                'sku': sku,
                'original_name': name
            })

    print(f"\nFound {len(wc_data)} products with SKUs in WooCommerce.")

    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()

    try:
        cursor.execute("SELECT id, name FROM Product")
        local_products = cursor.fetchall()
        
        updated_count = 0
        skipped_count = 0
        not_found_count = 0

        for lp in local_products:
            lp_id = lp['id']
            lp_name_raw = lp['name']
            lp_clean = clean_name(lp_name_raw)
            
            if not lp_clean:
                skipped_count += 1
                continue

            match_found = False
            matched_sku = None
            matched_wc_name = ""

            # 1. Try Exact Clean Match (Highest confidence)
            for wc in wc_data:
                if lp_clean == wc['clean_name']:
                    matched_sku = wc['sku']
                    matched_wc_name = wc['original_name']
                    match_found = True
                    break
            
            # 2. Try Containment Match (Medium confidence)
            if not match_found:
                for wc in wc_data:
                    if (lp_clean in wc['clean_name']) or (wc['clean_name'] in lp_clean):
                        matched_sku = wc['sku']
                        matched_wc_name = wc['original_name']
                        match_found = True
                        break
            
            # 3. Try Word-Set Intersection (Lowest confidence - matches if most words overlap)
            if not match_found:
                lp_words = set(lp_clean.split())
                for wc in wc_data:
                    wc_words = set(wc['clean_name'].split())
                    intersection = lp_words.intersection(wc_words)
                    # If at least 75% of the local words match the WC words
                    if len(lp_words) > 0 and (len(intersection) / len(lp_words)) >= 0.75:
                        matched_sku = wc['sku']
                        matched_wc_name = wc['original_name']
                        match_found = True
                        break

            if match_found:
                cursor.execute("UPDATE Product SET sku = ? WHERE id = ?", (matched_sku, lp_id))
                updated_count += 1
                print(f"  ✅ Updated Local: '{lp_name_raw}' -> '{matched_sku}' (Matched: '{matched_wc_name}')")
            else:
                not_found_count += 1
                print(f"  ❌ No match for Local: '{lp_name_raw}'")

        conn.commit()
        print(f"\n--- Sync Summary ---")
        print(f"Successfully updated: {updated_count}")
        print(f"No match found:      {not_found_count}")
        print(f"Skipped (invalid):   {skipped_count}")
        print(f"Total local products: {len(local_products)}")

    except Exception as e:
        print(f"Database error: {e}")
        conn.rollback()
    finally:
        conn.close()

if __name__ == "__main__":
    sync_skus_to_local()