PYTHON sync_inventory.py

import requests
import math
import sqlite3
import os
from datetime import datetime

# Credentials from skill/memory
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 log_sync(status, message):
    """Logs the sync result to the synclog table."""
    conn = sqlite3.connect(DB_PATH)
    try:
        cursor = conn.cursor()
        cursor.execute(
            "INSERT INTO sync_log (timestamp, status, message) VALUES (?, ?, ?)",
            (datetime.now().astimezone(), status, message)
        )
        conn.commit()
    except Exception as e:
        print(f"Failed to log sync: {e}")
    finally:
        conn.close()

def sync_to_woocommerce():
    """
    Syncs local bulk-weight inventory to WooCommerce product stock quantities.
    Formula: available_packets = floor(bulk_weight_g / weight_per_packet_g)
    """
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()

    try:
        # 1. Fetch all local products
        cursor.execute("SELECT name, sku, product_type, bulk_weight_g, weight_per_packet_g, current_stock_g, current_stock_qty FROM product")
        local_products = cursor.fetchall()
        
        results = {
            "updated": 0,
            "failed": [],
            "not_found": []
        }

        for lp in local_products:
            name = lp['name']
            sku = lp['sku']
            p_type = lp['product_type']
            
            if not sku:
                results["failed"].append(f"{name} (No SKU provided)")
                continue

            # 2. Calculate packets available based on type
            if p_type == 'quantity':
                available_packets = lp['current_stock_qty']
            else:
                available_packets = math.floor(lp['current_stock_g'] / lp['weight_per_packet_g'])

            # 3. Find product in WooCommerce via SKU
            search_url = f"{WC_URL}?sku={sku}"
            response = requests.get(search_url, auth=(WC_USERNAME, WC_PASSWORD), timeout=10)
            
            if response.status_code != 200:
                results["failed"].append(f"{name} (API Error: {response.status_code})")
                continue

            wc_products = response.json()
            
            if not wc_products:
                results["not_found"].append(f"{name} (SKU: {sku})")
                continue

            # 3. Update stock quantity
            wc_product = wc_products[0]
            wc_id = wc_product['id']

            update_payload = {
                "manage_stock": True,
                "stock_quantity": available_packets
            }

            update_resp = requests.put(
                f"{WC_URL}/{wc_id}",
                json=update_payload,
                auth=(WC_USERNAME, WC_PASSWORD),
                timeout=10
            )

            if update_resp.status_code == 200:
                results["updated"] += 1
            else:
                results["failed"].append(f"{name} (Update failed: {update_resp.text})")

        return results

    except Exception as e:
        raise e
    finally:
        conn.close()

if __name__ == "__main__":
    # Test run
    print("Starting sync...")
    try:
        res = sync_to_woocommerce()
        print(f"Sync finished: {res}")
        
        status = "success" if not res["failed"] else "partial"
        msg = f"Updated: {res['updated']}, Failed: {len(res['failed'])}, Not Found: {len(res['not_found'])}"
        log_sync(status, msg)
    except Exception as e:
        print(f"Sync failed: {e}")
        log_sync("error", str(e))