PYTHON stock_manager.py

#!/usr/bin/env python3
import sqlite3
import sys
import argparse
import os

DB_PATH = './seedvault-inventory/inventory.db'

def get_db_connection():
    if not os.path.exists(DB_PATH):
        print(f"Error: Database not found at {DB_PATH}")
        sys.exit(1)
    return sqlite3.connect(DB_PATH)

def add_stock(sku, amount):
    conn = get_db_connection()
    cursor = conn.cursor()
    
    # Check if SKU exists and get current stock
    cursor.execute("SELECT name, current_stock_g FROM product WHERE sku = ?;", (sku,))
    result = cursor.fetchone()
    
    if not result:
        print(f"Error: SKU '{sku}' not found.")
        conn.close()
        return

    name, current_stock = result
    new_stock = current_stock + amount
    
    cursor.execute("UPDATE product SET current_stock_g = ? WHERE sku = ?;", (new_stock, sku))
    conn.commit()
    conn.close()
    print(f"✅ Added {amount}g to '{name}' ({sku}). New total: {new_stock:.2f}g")

def set_stock(sku, amount):
    conn = get_db_connection()
    cursor = conn.cursor()
    
    cursor.execute("SELECT name FROM product WHERE sku = ?;", (sku,))
    result = cursor.fetchone()
    
    if not result:
        print(f"Error: SKU '{sku}' not found.")
        conn.close()
        return

    name = result[0]
    cursor.execute("UPDATE product SET current_stock_g = ? WHERE sku = ?;", (amount, sku))
    conn.commit()
    conn.close()
    print(f"✅ Set '{name}' ({sku}) to exactly {amount:.2f}g")

def list_stock(low_only=False):
    conn = get_db_connection()
    cursor = conn.cursor()
    
    query = "SELECT sku, name, current_stock_g, low_stock_threshold_g FROM product"
    if low_only:
        query += " WHERE current_stock_g <= low_stock_threshold_g"
    query += " ORDER BY current_stock_g ASC;"
    
    cursor.execute(query)
    rows = cursor.fetchall()
    conn.close()
    
    if not rows:
        print("No products found.")
        return

    print(f"{'SKU':<15} | {'NAME':<40} | {'STOCK':<10} | {'THRESHOLD':<10}")
    print("-" * 85)
    for sku, name, stock, threshold in rows:
        # Ensure we handle None values for all fields to avoid format errors
        safe_sku = str(sku) if sku is not None else "N/A"
        safe_name = str(name) if name is not None else "Unknown"
        safe_stock = stock if stock is not None else 0.0
        safe_threshold = threshold if threshold is not None else 0.0
        
        status = "[LOW]" if safe_stock <= safe_threshold else ""
        s_val = f"{safe_stock:.2f}g"
        t_val = f"{safe_threshold:.2f}g"
        
        print(f"{safe_sku:<15} | {safe_name[:40]:<40} | {s_val:>10} | {t_val:>10} {status}")

def main():
    parser = argparse.ArgumentParser(description="SeedVault Inventory Stock Manager")
    subparsers = parser.add_subparsers(dest="command", help="Commands")

    # Add command
    add_parser = subparsers.add_parser('add', help='Increase stock by amount')
    add_parser.add_argument('sku', help='Product SKU')
    add_parser.add_argument('amount', type=float, help='Amount in grams to add')

    # Set command
    set_parser = subparsers.add_parser('set', help='Overwrite stock with exact amount')
    set_parser.add_argument('sku', help='Product SKU')
    set_parser.add_argument('amount', type=float, help='Exact amount in grams')

    # List command
    list_parser = subparsers.add_parser('list', help='List all stock levels')
    list_parser.add_argument('--low', action='store_true', help='Show only low stock items')

    args = parser.parse_args()

    if args.command == 'add':
        add_stock(args.sku, args.amount)
    elif args.command == 'set':
        set_stock(args.sku, args.amount)
    elif args.command == 'list':
        list_stock(low_only=args.low)
    else:
        parser.print_help()

if __name__ == "__main__":
    main()