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)