PYTHON populate_products.py

import requests
import sqlite3
import os

# Credentials
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 populate_local_inventory():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()

    # Clear existing products to start fresh
    print("Clearing existing products for a clean sync...")
    cursor.execute("DROP TABLE IF EXISTS product")
    # Re-create table with correct schema (no unique constraint on SKU)
    cursor.execute("""
        CREATE TABLE product (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL UNIQUE,
            sku TEXT,
            bulk_weight_g REAL DEFAULT 0.0,
            weight_per_packet_g REAL NOT NULL,
            low_stock_threshold_g REAL DEFAULT 50.0
        )
    """)
    conn.commit()

    print("Connecting to WooCommerce API...")
    
    all_products = []
    page = 1
    while True:
        response = requests.get(f"{WC_URL}?per_page=100&page={page}", auth=(WC_USERNAME, WC_PASSWORD))
        if response.status_code != 200:
            print(f"Error fetching products: {response.status_code}")
            break
        
        products = response.json()
        if not products:
            break
            
        all_products.extend(products)
        page += 1

    print(f"Found {len(all_products)} products on WooCommerce. Starting local sync...")

    added_count = 0
    error_count = 0

    for wp_product in all_products:
        name = wp_product.get('name')
        sku = wp_product.get('sku')
        
        # Handle empty string SKU by making it None
        if sku == "" or sku is None:
            sku = None

        if not name:
            error_count += 1
            continue

        try:
            # Insert product
            # We use 1.0 as a placeholder for weight_per_packet_g since it's non-nullable
            cursor.execute(
                "INSERT INTO product (name, sku, bulk_weight_g, weight_per_packet_g, low_stock_threshold_g) VALUES (?, ?, ?, ?, ?)",
                (name, sku, 0.0, 1.0, 50.0)
            )
            added_count += 1
        except Exception as e:
            print(f"Failed to add {name}: {e}")
            error_count += 1

    conn.commit()
    conn.close()

    print(f"Sync Complete!")
    print(f"- Added: {added_count}")
    print(f"- Errors: {error_count}")

if __name__ == "__main__":
    populate_local_inventory()