import os
import sqlite3
import logging
from flask import Flask, request, jsonify
from dotenv import load_dotenv
# Load environment variables
load_dotenv(os.path.join(os.path.dirname(__file__), '../.env'))
BASE_DIR = os.path.abspath(os.path.join(os.path.dirname(__file__), '..'))
DB_PATH = os.path.join(BASE_DIR, 'inventory.db')
app = Flask(__name__)
# Setup logging
logging.basicConfig(
filename=os.path.join(os.path.dirname(__file__), 'webhook.log'),
level=logging.INFO,
format='%(asctime)s - %(levelname)s - %(message)s'
)
def update_local_stock(sku, quantity_sold):
"""
Updates local inventory based on product type (weight vs quantity).
Supports bundle/starter pack logic.
"""
conn = sqlite3.connect(DB_PATH)
conn.row_factory = sqlite3.Row
cursor = conn.cursor()
try:
# 1. Check if it's a bundle/starter pack
cursor.execute("SELECT component_sku, component_quantity FROM product_bundle WHERE bundle_sku = ?", (sku,))
bundle_components = cursor.fetchall()
if bundle_components:
# It's a bundle!
logging.info(f"Processing bundle SKU: {sku} (Quantity: {quantity_sold})")
for component in bundle_components:
comp_sku = component['component_sku']
comp_qty_per_bundle = component['component_quantity']
# Total units of component sold = quantity_sold (bundles) * comp_qty_per_bundle
total_comp_units_sold = quantity_sold * comp_qty_per_bundle
# Find the component product
cursor.execute("SELECT id, name, product_type, weight_per_packet_g FROM product WHERE sku = ?", (comp_sku,))
comp_product = cursor.fetchone()
if not comp_product:
logging.error(f"Bundle Error: Component SKU {comp_sku} not found in local database.")
continue
comp_id = comp_product['id']
comp_name = comp_product['name']
comp_type = comp_product['product_type']
if comp_type == 'weight':
comp_weight = comp_product['weight_per_packet_g']
deduction = total_comp_units_sold * comp_weight
cursor.execute("UPDATE product SET current_stock_g = current_stock_g - ? WHERE id = ?", (deduction, comp_id))
logging.info(f" -> Bundle Component (Weight) Success: Subtracted {deduction:.2f}g from '{comp_name}' (SKU: {comp_sku})")
else:
# Quantity based
deduction = total_comp_units_sold
cursor.execute("UPDATE product SET current_stock_qty = current_stock_qty - ? WHERE id = ?", (deduction, comp_id))
logging.info(f" -> Bundle Component (Quantity) Success: Subtracted {deduction} units from '{comp_name}' (SKU: {comp_sku})")
conn.commit()
return True, f"Processed bundle SKU {sku}"
# 2. If not a bundle, do standard deduction
cursor.execute("SELECT id, name, product_type, weight_per_packet_g FROM product WHERE sku = ?", (sku,))
product = cursor.fetchone()
if not product:
logging.error(f"Webhook Error: SKU {sku} not found in local database.")
return False, "SKU not found"
product_id = product['id']
product_name = product['name']
product_type = product['product_type']
if product_type == 'weight':
weight_per_unit = product['weight_per_packet_g']
total_deduction = quantity_sold * weight_per_unit
cursor.execute("UPDATE product SET current_stock_g = current_stock_g - ? WHERE id = ?", (total_deduction, product_id))
log_msg = f"SUCCESS: Subtracted {total_deduction:.2f}g from '{product_name}' (SKU: {sku})"
else:
# Quantity based
total_deduction = quantity_sold
cursor.execute("UPDATE product SET current_stock_qty = current_stock_qty - ? WHERE id = ?", (total_deduction, product_id))
log_msg = f"SUCCESS: Subtracted {total_deduction} units from '{product_name}' (SKU: {sku})"
conn.commit()
logging.info(log_msg)
return True, f"Updated {product_name} by -{total_deduction}"
except Exception as e:
logging.error(f"Database Error during stock update: {e}")
conn.rollback()
return False, str(e)
finally:
conn.close()
@app.route('/webhook/woocommerce/order-created', methods=['POST'])
def handle_order_created():
"""
Endpoint for WooCommerce 'Order Created' webhook.
"""
if not request.is_json:
logging.info("Received non-JSON request (likely a test ping). Returning 200 OK.")
return jsonify({"status": "ignored", "reason": "not_json"}), 200
data = request.get_json(silent=True)
if not data:
logging.warning("Received empty JSON body. Returning 200 OK to avoid 415/400 errors.")
return jsonify({"status": "ignored", "reason": "empty_json"}), 200
order_id = data.get('id')
line_items = data.get('line_items', [])
logging.info(f"Received Order #{order_id} from WooCommerce.")
if not line_items:
logging.warning(f"Order #{order_id} contained no line items.")
return jsonify({"status": "ignored", "reason": "no_items"}), 200
success_count = 0
errors = []
for item in line_items:
sku = item.get('sku')
quantity = item.get('quantity')
if not sku or not quantity:
errors.append(f"Missing SKU or quantity in item: {item.get('name')}")
continue
success, message = update_local_stock(sku, quantity)
if success:
success_count += 1
else:
errors.append(f"{sku}: {message}")
response_payload = {
"order_id": order_id,
"processed_items": success_count,
"errors": errors
}
if errors:
logging.warning(f"Order #{order_id} processed with errors: {errors}")
return jsonify(response_payload), 207 # Multi-Status
return jsonify(response_payload), 200
@app.route('/health', methods=['GET'])
def health_check():
return jsonify({"status": "alive"}), 200
if __name__ == '__main__':
app.run(host='127.0.0.1', port=5002)