from flask import Blueprint, request, jsonify, send_from_directory
from flask_jwt_extended import jwt_required, get_jwt_identity
from db.db import get_db_connection
from werkzeug.utils import secure_filename
from config.auth import role_required
from product_image_optimizer import ProductImageOptimizer, optimize_to_50kb, optimize_to_100kb, optimize_to_size
import mysql.connector
import traceback
from datetime import datetime, date
import json
import os
import base64
from datetime import datetime, timedelta
from decimal import Decimal

product_bp = Blueprint('product', __name__)


# ==========================================
# FILE UPLOAD CONFIGURATION
# ==========================================
UPLOAD_FOLDER = 'uploads/products'
os.makedirs(UPLOAD_FOLDER, exist_ok=True)

MAX_IMAGE_SIZE = 5 * 1024 * 1024  # 5MB (before optimization)
MAX_CERT_SIZE = 10 * 1024 * 1024  # 10MB

# Image optimization settings
IMAGE_OPTIMIZATION_MODE = 'target_size'
IMAGE_TARGET_SIZE_KB = 20
IMAGE_QUALITY = 55
IMAGE_MAX_DIMENSION = 1200


# ==========================================
# FILE SERVING ROUTE
# ==========================================
@product_bp.route('/uploads/products/<path:filename>', methods=['GET'])
def serve_product_file(filename):
    """Serve uploaded product files (images and certificates)"""
    try:
        return send_from_directory(UPLOAD_FOLDER, filename)
    except FileNotFoundError:
        return jsonify({'error': 'File not found'}), 404


# ==========================================
# HELPER FUNCTIONS
# ==========================================

def validate_required_fields(data, fields):
    """Validate that required fields exist and are not empty"""
    missing = []
    for field in fields:
        value = data.get(field)
        if value is None or value == '':
            missing.append(field)
    return missing


def safe_float(value, default=0.0):
    """Safely convert value to float"""
    try:
        return float(value) if value not in (None, '', 'null') else default
    except (ValueError, TypeError):
        return default


def safe_int(value, default=0):
    """Safely convert value to int"""
    try:
        return int(value) if value not in (None, '', 'null') else default
    except (ValueError, TypeError):
        return default


def save_base64_file(base64_data, file_prefix='file'):
    """Save base64 file to disk with automatic image optimization"""
    try:
        if not base64_data or 'base64,' not in base64_data:
            return None

        header, encoded = base64_data.split(',', 1)

        is_image = 'image/' in header
        is_pdf = 'application/pdf' in header

        # Optimize images
        if is_image:
            print(f"\n🖼️  Optimizing image (Mode: {IMAGE_OPTIMIZATION_MODE})...")

            optimized_base64 = None

            if IMAGE_OPTIMIZATION_MODE == 'target_size':
                print(f"🎯 Target size: {IMAGE_TARGET_SIZE_KB}KB")
                optimized_base64 = optimize_to_size(
                    base64_data,
                    target_kb=IMAGE_TARGET_SIZE_KB
                )
            else:
                print(f"📊 Quality: {IMAGE_QUALITY}%, Max dimension: {IMAGE_MAX_DIMENSION}px")
                optimized_base64 = ProductImageOptimizer.optimize_base64_image(
                    base64_data,
                    max_width=IMAGE_MAX_DIMENSION,
                    max_height=IMAGE_MAX_DIMENSION,
                    quality=IMAGE_QUALITY
                )

            if optimized_base64:
                header, encoded = optimized_base64.split(',', 1)
                print(f"✅ Image optimized successfully")
            else:
                print(f"⚠️  Optimization failed, using original image")

        # Determine extension
        if 'image/jpeg' in header or 'image/jpg' in header:
            ext = '.jpg'
        elif 'image/png' in header:
            ext = '.png'
        elif 'image/gif' in header:
            ext = '.gif'
        elif 'image/webp' in header:
            ext = '.webp'
        elif 'application/pdf' in header:
            ext = '.pdf'
        else:
            ext = '.bin'

        # Generate unique filename
        timestamp = datetime.now().strftime('%Y%m%d_%H%M%S_%f')
        filename = secure_filename(f"{file_prefix}_{timestamp}{ext}")
        filepath = os.path.join(UPLOAD_FOLDER, filename)

        # Decode and save
        file_data = base64.b64decode(encoded)

        # Validate size
        file_size = len(file_data)
        if is_image and file_size > MAX_IMAGE_SIZE:
            print(f"❌ Image too large after optimization: {file_size:,} bytes (max: {MAX_IMAGE_SIZE:,})")
            return None
        if is_pdf and file_size > MAX_CERT_SIZE:
            print(f"❌ PDF too large: {file_size:,} bytes (max: {MAX_CERT_SIZE:,})")
            return None

        # Save file
        with open(filepath, 'wb') as f:
            f.write(file_data)

        print(f"💾 Saved: {filename} ({file_size:,} bytes = {file_size/1024:.1f}KB)")

        return filepath

    except Exception as e:
        print(f"❌ Error saving file: {e}")
        import traceback
        traceback.print_exc()
        return None


def cleanup_uploaded_files(image_paths, certificate_path):
    """Clean up uploaded files in case of error"""
    if image_paths:
        for path in image_paths:
            try:
                if os.path.exists(path):
                    os.remove(path)
                    print(f"  🗑️  Cleaned up: {path}")
            except Exception as e:
                print(f"  ⚠️  Could not delete {path}: {e}")

    if certificate_path and os.path.exists(certificate_path):
        try:
            os.remove(certificate_path)
            print(f"  🗑️  Cleaned up certificate: {certificate_path}")
        except Exception as e:
            print(f"  ⚠️  Could not delete certificate: {e}")


def generate_grn_code(cursor):
    """
    Generate the next sequential GRN code, e.g. GRN-000001, GRN-000002, ...

    Uses the highest existing grn_id (numeric, always reliable) rather than
    string-sorting grn_code, since "GRN-000009" > "GRN-000010" alphabetically
    but not numerically.
    """
    cursor.execute("SELECT grn_id, grn_code FROM grn ORDER BY grn_id DESC LIMIT 1")
    last_row = cursor.fetchone()

    next_number = 1
    if last_row:
        next_number = safe_int(last_row.get('grn_id'), 0) + 1

        # Guard against gaps from deleted/cancelled GRNs by also checking the
        # numeric suffix already encoded in the last stored code.
        old_code = last_row.get('grn_code') or ''
        if '-' in old_code:
            try:
                code_number = int(old_code.split('-')[-1])
                next_number = max(next_number, code_number + 1)
            except (ValueError, IndexError):
                pass

    return f"GRN-{next_number:06d}"


def generate_batch_number(cursor, grn_code, sequence):
    """
    Generate a batch number tied to the GRN code, e.g. BATCH-GRN-000001-1.
    Keeping the GRN code inside the batch number makes it easy to trace a
    batch back to the GRN that brought the stock in.
    """
    return f"BATCH-{grn_code}-{sequence}"


def apply_stock_from_grn_items(cursor, grn_items_data, purchase_order_id, grn_id, grn_code, warehouse_id, store_id):
    """
    Called ONLY when a GRN is saved with status='approved'.

    For every GRN item with an accepted quantity > 0, this:
      1. Creates a product_batches row (FIFO/price-aware batch, same shape
         used everywhere else in the app — cost/price/our_price/expiration/etc.)
      2. Adds that quantity into warehouse_stock for the target warehouse
         (creates the row if it doesn't exist yet, otherwise increments it).

    Returns the list of batch_ids created, for logging/response purposes.

    NOTE: warehouse_stock schema is:
        id, store_id (NOT NULL), product_id, warehouse_id, variation_id,
        batch_id, quantity, updated_at
    There is NO created_at column, and store_id is required (NOT NULL,
    no default) — both the SELECT and INSERT below reflect that.

    our_price / batch_price: product_batches has THREE distinct price-related
    columns — cost, price, and our_price. cost = purchase cost, price = the
    batch/purchase price, our_price = the intended selling price for THIS
    batch. Both price (batch_price) and our_price are read from grn_items_data
    and stored into their own dedicated columns — they are never collapsed
    into a single value.
    """
    created_batches = []
    sequence = 1

    for item in grn_items_data:
        accepted_qty = safe_float(item.get('accepted_quantity', 0))
        if accepted_qty <= 0:
            # Nothing accepted for this line (e.g. fully rejected) — skip silently.
            continue

        product_id = item['product_id']
        variation_id = item.get('variation_id')
        cost = safe_float(item.get('net_unit_cost', item.get('unit_price', 0)))

        # Keep the batch/purchase price ("price") and the intended selling
        # price ("our_price") as two separate values instead of collapsing
        # them into a single column. Falls back to batch_price if our_price
        # wasn't supplied, so nothing breaks if a caller omits it.
        batch_price = safe_float(item.get('batch_price', 0))
        our_price = safe_float(item.get('our_price', batch_price))

        expiration_date = item.get('expiration_date')

        batch_number = generate_batch_number(cursor, grn_code, sequence)
        sequence += 1

        # ---- 1. Create the stock batch (storing cost, price AND our_price) ----
        cursor.execute("""
            INSERT INTO product_batches (
                product_id, variation_id, purchase_order_id, grn_id,
                batch_number, quantity, remaining_quantity,
                cost, price, our_price, expiration_date, created_on
            )
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW())
        """, (
            product_id, variation_id, purchase_order_id, grn_id,
            batch_number, accepted_qty, accepted_qty,
            cost, batch_price, our_price, expiration_date
        ))
        batch_id = cursor.lastrowid
        created_batches.append({
            'batch_id': batch_id,
            'batch_number': batch_number,
            'product_id': product_id,
            'variation_id': variation_id,
            'quantity': accepted_qty
        })
        print(f"    📦 Batch created: {batch_number} (ID: {batch_id}) — qty {accepted_qty}, "
              f"cost {cost}, price {batch_price}, our_price {our_price}")

        # ---- 2. Upsert warehouse_stock (match by batch_id to keep one row per batch) ----
        cursor.execute("""
            SELECT id, quantity FROM warehouse_stock
            WHERE product_id = %s AND warehouse_id = %s AND store_id = %s
              AND batch_id = %s AND variation_id <=> %s
            LIMIT 1
        """, (product_id, warehouse_id, store_id, batch_id, variation_id))
        existing_stock = cursor.fetchone()

        if existing_stock:
            cursor.execute("""
                UPDATE warehouse_stock
                SET quantity = quantity + %s, updated_at = NOW()
                WHERE id = %s
            """, (accepted_qty, existing_stock['id']))
            print(f"    ✅ Warehouse stock updated (+{accepted_qty}, batch_id={batch_id}) for product {product_id}"
                  f"{' variation ' + str(variation_id) if variation_id else ''} in warehouse {warehouse_id}")
        else:
            cursor.execute("""
                INSERT INTO warehouse_stock (
                    store_id, product_id, variation_id, warehouse_id, batch_id, quantity, updated_at
                )
                VALUES (%s, %s, %s, %s, %s, %s, NOW())
            """, (store_id, product_id, variation_id, warehouse_id, batch_id, accepted_qty))
            print(f"    ✅ Warehouse stock row created ({accepted_qty}, batch_id={batch_id}) for product {product_id}"
                  f"{' variation ' + str(variation_id) if variation_id else ''} in warehouse {warehouse_id}")

    return created_batches


# ==========================================
# GET ALL PRODUCTS — FIXED after schema change
# products.base_unit_id NOW → base_units.id (direct)
# OLD: products.base_unit_id → units.id → base_units.id
# ==========================================

@product_bp.route('/get_products', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier')
def get_products():
    conn = get_db_connection()
    if conn is None:
        return jsonify({'error': 'Database connection failed'}), 500

    cursor = conn.cursor(dictionary=True)
    try:
        cursor.execute("""
            SELECT 
                p.id,
                p.product_name,
                p.sku,
                p.barcode_symbology,
                p.product_image,
                p.certificate_image,
                p.product_type,
                p.stock_alert,
                p.note,
                p.product_tax,
                p.tax_type,
                p.expiration_date,
                p.created_at,
                p.updated_at,

                COALESCE(b.brand_name, '-')     AS brand_name,
                b.id                            AS brand_id,

                COALESCE(c.category_name, '-')  AS category_name,
                c.id                            AS category_id,

                COALESCE(bu1.base_unit, '-')    AS base_unit_name,
                bu1.id                          AS base_unit_id,

                COALESCE(u2.unit_name, '-')     AS sale_unit,
                u2.id                           AS sale_unit_id,
                COALESCE(bu2.base_unit, '-')    AS sale_base_unit_name,

                COALESCE(u3.unit_name, '-')     AS purchase_unit,
                u3.id                           AS purchase_unit_id,
                COALESCE(bu3.base_unit, '-')    AS purchase_base_unit_name

            FROM products p
            LEFT JOIN brands     b   ON p.brand_id         = b.id
            LEFT JOIN categories c   ON p.category_id      = c.id
            LEFT JOIN base_units bu1 ON p.base_unit_id     = bu1.id
            LEFT JOIN units      u2  ON p.sale_unit_id     = u2.id
            LEFT JOIN base_units bu2 ON u2.base_unit_id    = bu2.id
            LEFT JOIN units      u3  ON p.purchase_unit_id = u3.id
            LEFT JOIN base_units bu3 ON u3.base_unit_id    = bu3.id

            ORDER BY p.created_at DESC
        """)
        products = cursor.fetchall()

        for product in products:
            product_id = product['id']

            # Product Images
            product_images = []
            if product.get('product_image'):
                try:
                    raw = product['product_image']
                    image_paths = json.loads(raw) if isinstance(raw, str) else raw
                    if isinstance(image_paths, list):
                        for img_path in image_paths:
                            if img_path and os.path.exists(img_path):
                                filename = os.path.basename(img_path)
                                product_images.append(f"/uploads/products/{filename}")
                except (json.JSONDecodeError, TypeError) as e:
                    print(f"❌ Image parse error (product {product_id}): {e}")

            product['product_images'] = product_images
            product['image_count']    = len(product_images)

            # Certificate Image
            certificate_url = None
            if product.get('certificate_image'):
                cert_path = product['certificate_image']
                if cert_path and os.path.exists(cert_path):
                    certificate_url = f"/uploads/products/{os.path.basename(cert_path)}"

            product['certificate_url'] = certificate_url
            product['has_certificate'] = bool(certificate_url)

            product.pop('product_image',    None)
            product.pop('certificate_image', None)

            # Total Stock (single product)
            cursor.execute("""
                SELECT IFNULL(SUM(quantity), 0) AS total_stock
                FROM warehouse_stock
                WHERE product_id = %s AND variation_id IS NULL
            """, (product_id,))
            stock_row = cursor.fetchone()
            product['total_stock'] = float(stock_row['total_stock']) if stock_row else 0.0

            # Batch Price Stats (single product)
            cursor.execute("""
                SELECT
                    AVG(cost)              AS avg_cost,
                    AVG(price)             AS avg_price,
                    AVG(our_price)         AS avg_our_price,
                    MIN(cost)              AS min_cost,
                    MAX(cost)              AS max_cost,
                    MIN(price)             AS min_price,
                    MAX(price)             AS max_price,
                    MIN(our_price)         AS min_our_price,
                    MAX(our_price)         AS max_our_price,
                    SUM(quantity)          AS total_purchased,
                    SUM(remaining_quantity) AS total_remaining
                FROM product_batches
                WHERE product_id = %s AND variation_id IS NULL
            """, (product_id,))
            cp = cursor.fetchone()

            def _f(val): return float(val) if val is not None else 0.0

            product['avg_cost']        = _f(cp['avg_cost'])        if cp else 0.0
            product['avg_price']       = _f(cp['avg_price'])       if cp else 0.0
            product['avg_our_price']   = _f(cp['avg_our_price'])   if cp else 0.0
            product['min_cost']        = _f(cp['min_cost'])        if cp else 0.0
            product['max_cost']        = _f(cp['max_cost'])        if cp else 0.0
            product['min_price']       = _f(cp['min_price'])       if cp else 0.0
            product['max_price']       = _f(cp['max_price'])       if cp else 0.0
            product['min_our_price']   = _f(cp['min_our_price'])   if cp else 0.0
            product['max_our_price']   = _f(cp['max_our_price'])   if cp else 0.0
            product['total_purchased'] = _f(cp['total_purchased']) if cp else 0.0
            product['total_remaining'] = _f(cp['total_remaining']) if cp else 0.0

            # Warehouse-wise Stock
            cursor.execute("""
                SELECT
                    ws.warehouse_id,
                    COALESCE(w.warehouse_name, 'Unknown') AS warehouse_name,
                    SUM(ws.quantity)                       AS stock_quantity
                FROM warehouse_stock ws
                LEFT JOIN warehouses w ON ws.warehouse_id = w.id
                WHERE ws.product_id = %s AND ws.variation_id IS NULL
                GROUP BY ws.warehouse_id, w.warehouse_name
            """, (product_id,))
            product['warehouse_stock'] = cursor.fetchall()

            # Batch List
            cursor.execute("""
                SELECT
                    pb.batch_id,
                    pb.batch_number,
                    pb.quantity,
                    pb.remaining_quantity,
                    pb.cost,
                    pb.price,
                    pb.our_price,
                    pb.expiration_date,
                    pb.created_on,
                    po.order_id,
                    po.status       AS order_status,
                    po.grn_status,
                    COALESCE(s.supplier_name, 'Unknown') AS supplier_name,
                    pb.grn_id
                FROM product_batches pb
                LEFT JOIN purchase_orders po ON pb.purchase_order_id = po.order_id
                LEFT JOIN suppliers       s  ON po.supplier_id       = s.id
                WHERE pb.product_id = %s AND pb.variation_id IS NULL
                ORDER BY pb.created_on DESC
            """, (product_id,))
            product['batches'] = cursor.fetchall()

            # Variations (variable products only)
            if product['product_type'] == 'variable':
                cursor.execute("""
                    SELECT
                        id          AS variation_id,
                        variation_name,
                        variation_type,
                        variation_sku,
                        variation_cost,
                        variation_price,
                        variation_tax_type,
                        variation_tax,
                        variation_stock_alert,
                        expiration_date,
                        created_at,
                        updated_at
                    FROM product_variations
                    WHERE product_id = %s
                    ORDER BY created_at ASC
                """, (product_id,))
                variations = cursor.fetchall()

                for v in variations:
                    vid = v['variation_id']

                    cursor.execute("""
                        SELECT IFNULL(SUM(quantity), 0) AS total_stock
                        FROM warehouse_stock
                        WHERE product_id = %s AND variation_id = %s
                    """, (product_id, vid))
                    vsr = cursor.fetchone()
                    v['total_stock'] = float(vsr['total_stock']) if vsr else 0.0

                    cursor.execute("""
                        SELECT
                            ws.warehouse_id,
                            COALESCE(w.warehouse_name, 'Unknown') AS warehouse_name,
                            SUM(ws.quantity)                       AS stock_quantity
                        FROM warehouse_stock ws
                        LEFT JOIN warehouses w ON ws.warehouse_id = w.id
                        WHERE ws.product_id = %s AND ws.variation_id = %s
                        GROUP BY ws.warehouse_id, w.warehouse_name
                    """, (product_id, vid))
                    v['warehouse_stock'] = cursor.fetchall()

                    cursor.execute("""
                        SELECT
                            pb.batch_id,
                            pb.batch_number,
                            pb.quantity,
                            pb.remaining_quantity,
                            pb.cost,
                            pb.price,
                            pb.our_price,
                            pb.expiration_date,
                            pb.created_on,
                            po.order_id,
                            po.status       AS order_status,
                            po.grn_status,
                            COALESCE(s.supplier_name, 'Unknown') AS supplier_name,
                            pb.grn_id
                        FROM product_batches pb
                        LEFT JOIN purchase_orders po ON pb.purchase_order_id = po.order_id
                        LEFT JOIN suppliers       s  ON po.supplier_id       = s.id
                        WHERE pb.product_id = %s AND pb.variation_id = %s
                        ORDER BY pb.created_on DESC
                    """, (product_id, vid))
                    v['batches'] = cursor.fetchall()

                    cursor.execute("""
                        SELECT
                            SUM(quantity)           AS total_purchased,
                            SUM(remaining_quantity) AS total_remaining
                        FROM product_batches
                        WHERE product_id = %s AND variation_id = %s
                    """, (product_id, vid))
                    vbs = cursor.fetchone()
                    v['total_purchased'] = _f(vbs['total_purchased']) if vbs else 0.0
                    v['total_remaining'] = _f(vbs['total_remaining']) if vbs else 0.0

                product['variations']       = variations
                product['total_stock']      = sum(v['total_stock'] for v in variations)
                product['total_variations'] = len(variations)

            else:
                product['variations']       = []
                product['total_variations'] = 0

            # Associated Purchase Orders
            cursor.execute("""
                SELECT DISTINCT
                    po.order_id,
                    po.status,
                    po.grn_status,
                    po.grand_total,
                    po.created_on,
                    COALESCE(s.supplier_name,  'Unknown') AS supplier_name,
                    COALESCE(w.warehouse_name, 'Unknown') AS warehouse_name
                FROM purchase_orders po
                LEFT JOIN suppliers  s ON po.supplier_id  = s.id
                LEFT JOIN warehouses w ON po.warehouse_id = w.id
                WHERE po.order_id IN (
                    SELECT DISTINCT purchase_order_id
                    FROM product_batches
                    WHERE product_id = %s AND purchase_order_id IS NOT NULL
                )
                ORDER BY po.created_on DESC
            """, (product_id,))
            product['purchase_orders'] = cursor.fetchall()

        return jsonify({
            'success': True,
            'count': len(products),
            'data': products
        }), 200

    except mysql.connector.Error as err:
        print(f"Database Error: {err}")
        traceback.print_exc()
        return jsonify({'error': f'Database error: {str(err)}'}), 500

    except Exception as err:
        print(f"Unexpected error: {err}")
        traceback.print_exc()
        return jsonify({'error': 'Failed to fetch products'}), 500

    finally:
        if cursor: cursor.close()
        if conn:   conn.close()


# ==========================================
# GET PRODUCTS LIST — OPTIMISED FOR LISTING PAGE
# Single query with aggregated subqueries (no N+1).
# Server-side pagination, search, and filters.
# ==========================================

@product_bp.route('/get_products_list', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier')
def get_products_list():
    conn = get_db_connection()
    if conn is None:
        return jsonify({'error': 'Database connection failed'}), 500

    cursor = conn.cursor(dictionary=True)
    try:
        # ── Query params ──
        page       = safe_int(request.args.get('page'), 1) or 1
        per_page   = safe_int(request.args.get('per_page'), 50)
        per_page   = min(max(per_page, 10), 200)        # clamp 10-200
        search     = (request.args.get('search') or '').strip()
        brand_id   = request.args.get('brand_id')
        category_id = request.args.get('category_id')
        base_unit_id = request.args.get('base_unit_id')
        export     = request.args.get('export', '').lower() == 'true'

        # ── Build WHERE clauses ──
        where_clauses = []
        params = []

        if search:
            where_clauses.append("""(p.product_name LIKE %s OR p.sku LIKE %s
                OR EXISTS (SELECT 1 FROM product_variations pv WHERE pv.product_id = p.id
                           AND (pv.variation_sku LIKE %s OR pv.variation_type LIKE %s)))""")
            like_val = f"%{search}%"
            params.extend([like_val, like_val, like_val, like_val])

        if brand_id and brand_id not in ('', 'All'):
            where_clauses.append("p.brand_id = %s")
            params.append(int(brand_id))

        if category_id and category_id not in ('', 'All'):
            where_clauses.append("p.category_id = %s")
            params.append(int(category_id))

        if base_unit_id and base_unit_id not in ('', 'All'):
            where_clauses.append("p.base_unit_id = %s")
            params.append(int(base_unit_id))

        where_sql = (" WHERE " + " AND ".join(where_clauses)) if where_clauses else ""

        # ── COUNT query ──
        count_sql = f"SELECT COUNT(*) AS total FROM products p{where_sql}"
        cursor.execute(count_sql, params)
        total = cursor.fetchone()['total']

        # ── Pagination ──
        total_pages = max(1, -(-total // per_page))      # ceil division
        if page > total_pages:
            page = total_pages

        offset = (page - 1) * per_page

        # ── Main query with aggregated subqueries ──
        limit_sql = "" if export else f" LIMIT {per_page} OFFSET {offset}"

        data_sql = f"""
            SELECT
                p.id,
                p.product_name,
                p.sku,
                p.product_image,
                p.certificate_image,
                p.product_type,
                p.created_at,

                COALESCE(b.brand_name, '-')     AS brand_name,
                COALESCE(c.category_name, '-')  AS category_name,
                COALESCE(bu.base_unit, '-')     AS base_unit_name,

                COALESCE(stk.total_stock, 0)    AS total_stock,

                bp.min_price,
                bp.max_price,
                bp.avg_price,

                vp.var_prices

            FROM products p

            LEFT JOIN brands     b  ON p.brand_id     = b.id
            LEFT JOIN categories c  ON p.category_id  = c.id
            LEFT JOIN base_units bu ON p.base_unit_id  = bu.id

            LEFT JOIN (
                SELECT product_id,
                       SUM(quantity) AS total_stock
                FROM warehouse_stock
                GROUP BY product_id
            ) stk ON stk.product_id = p.id

            LEFT JOIN (
                SELECT product_id,
                       MIN(price)    AS min_price,
                       MAX(price)    AS max_price,
                       AVG(price)    AS avg_price
                FROM product_batches
                WHERE variation_id IS NULL
                GROUP BY product_id
            ) bp ON bp.product_id = p.id

            LEFT JOIN (
                SELECT product_id,
                       GROUP_CONCAT(DISTINCT variation_price ORDER BY variation_price SEPARATOR ',') AS var_prices
                FROM product_variations
                GROUP BY product_id
            ) vp ON vp.product_id = p.id

            {where_sql}

            ORDER BY p.created_at DESC
            {limit_sql}
        """

        cursor.execute(data_sql, params)
        products = cursor.fetchall()

        # ── Post-process rows ──
        for product in products:
            # Images — just parse JSON paths, skip os.path.exists
            product_images = []
            if product.get('product_image'):
                try:
                    raw = product['product_image']
                    image_paths = json.loads(raw) if isinstance(raw, str) else raw
                    if isinstance(image_paths, list):
                        for img_path in image_paths:
                            if img_path:
                                filename = os.path.basename(img_path)
                                product_images.append(f"/uploads/products/{filename}")
                except (json.JSONDecodeError, TypeError):
                    pass

            product['product_images'] = product_images
            product['image_count']    = len(product_images)

            # Certificate
            has_cert = False
            if product.get('certificate_image'):
                cert_path = product['certificate_image']
                if cert_path:
                    has_cert = True
            product['has_certificate'] = has_cert

            product.pop('product_image',     None)
            product.pop('certificate_image', None)

            # Build display_prices array
            display_prices = []
            if product['product_type'] == 'variable' and product.get('var_prices'):
                for p_str in product['var_prices'].split(','):
                    try:
                        val = float(p_str)
                        if val > 0:
                            display_prices.append(val)
                    except (ValueError, TypeError):
                        pass
            else:
                min_p = float(product['min_price']) if product.get('min_price') is not None else 0
                max_p = float(product['max_price']) if product.get('max_price') is not None else 0
                avg_p = float(product['avg_price']) if product.get('avg_price') is not None else 0
                if min_p > 0:
                    display_prices.append(min_p)
                if max_p > 0 and max_p != min_p:
                    display_prices.append(max_p)
                if not display_prices and avg_p > 0:
                    display_prices.append(avg_p)

            product['display_prices'] = display_prices
            product.pop('var_prices', None)
            product.pop('min_price',  None)
            product.pop('max_price',  None)
            product.pop('avg_price',  None)

            # Float-ify stock
            product['total_stock'] = float(product['total_stock'])

        return jsonify({
            'success': True,
            'data': products,
            'pagination': {
                'page':        page,
                'per_page':    per_page,
                'total':       total,
                'total_pages': total_pages
            }
        }), 200

    except mysql.connector.Error as err:
        print(f"Database Error: {err}")
        traceback.print_exc()
        return jsonify({'error': f'Database error: {str(err)}'}), 500

    except Exception as err:
        print(f"Unexpected error: {err}")
        traceback.print_exc()
        return jsonify({'error': 'Failed to fetch products'}), 500

    finally:
        if cursor: cursor.close()
        if conn:   conn.close()


# ==========================================
# COMBINED: ADD PRODUCT + PURCHASE ORDER + GRN
# ==========================================
# Creates the Product (+ variations), the Purchase Order (+ order_items)
# and — only if save_grn is true — the GRN (+ grn_items), all inside ONE
# database transaction. If anything fails, everything rolls back together.
#
# GRN STATUS BEHAVIOUR:
#   - grn.grn_status = 'pending'  (default) → GRN saved only, NO stock movement.
#     Approve it later (from the GRN page) to bring the stock in.
#   - grn.grn_status = 'approved'            → GRN saved AND stock is added
#     immediately: a product_batches row is created for every accepted item
#     and warehouse_stock is incremented for the GRN's warehouse, all inside
#     this same transaction. The batch row correctly stores cost, price
#     (batch/purchase price) AND our_price (intended selling price) for both
#     single-type and variable-type products.
#
# RECEIVED BY:
#   The frontend no longer lets the user type a name — "received_by" is
#   always resolved server-side from the JWT identity of the logged-in user
#   (falls back to an admin account only if the token's user can't be found).
#
# ★★★ our_price FIX (this version) ★★★
#   Previously, `order_items_enriched` (the list used both to build GRN
#   items and, later, product_batches) always set:
#       'our_price': price_value
#   ...where `price_value` is just the selling price coming from the
#   product/variation record (product_price / variation_price). This
#   completely IGNORED the `our_price` field the frontend explicitly sends
#   for every entry in `products_list` (from the dedicated "Our Price"
#   input on the Add Product page — p_our-price for single products,
#   .variation-our-price for variable products). As a result, whatever the
#   user typed into "Our Price" was silently discarded, and product_batches
#   / grn_items.our_price ended up storing the plain selling price instead
#   (or an unrelated fallback), regardless of what was entered.
#
#   FIX: read `prod.get('our_price', price_value)` — i.e. use the frontend's
#   explicit our_price value when present, and only fall back to the
#   selling price if it wasn't sent. This value now flows correctly through
#   order_items_enriched → grn_items_data → BOTH the `grn_items` table AND
#   (when the GRN is approved) the `product_batches` table.
# ==========================================

@product_bp.route('/add_product_purchase_grn', methods=['POST'])
@jwt_required()
@role_required('admin', 'manager')
def add_product_purchase_grn():

    data = request.get_json(force=True, silent=True)
    if not data:
        return jsonify({'error': 'No data provided'}), 400

    print("=" * 80)
    print("📦 COMBINED REQUEST: PRODUCT(S) + PURCHASE ORDER + GRN")
    print("=" * 80)

    # ----------------------------------------------------------------
    # 1. DETECT PAYLOAD FORMAT — multi-product vs. legacy single-product
    # ----------------------------------------------------------------
    product_entries = data.get('product_entries')
    is_multi = product_entries and isinstance(product_entries, list) and len(product_entries) > 0

    if not is_multi:
        # Legacy: wrap flat product fields into a single-entry array
        product_entries = [{
            'product_name': data.get('product_name'),
            'sku': data.get('sku'),
            'barcode_symbology': data.get('barcode_symbology'),
            'product_type': data.get('product_type', 'single'),
            'brand_id': data.get('brand_id'),
            'category_id': data.get('category_id'),
            'base_unit_id': data.get('base_unit_id'),
            'sale_unit_id': data.get('sale_unit_id'),
            'purchase_unit_id': data.get('purchase_unit_id'),
            'stock_alert': data.get('stock_alert', 0),
            'note': data.get('note', ''),
            'product_tax': data.get('product_tax', 0.0),
            'tax_type': data.get('tax_type', 'exclusive'),
            'expiration_date': data.get('expiration_date'),
            'product_cost': data.get('product_cost', 0.0),
            'product_price': data.get('product_price', 0.0),
            'product_quantity': data.get('product_quantity', 0.0),
            'product_image': data.get('product_image'),
            'certificate_image': data.get('certificate_image'),
            'variations': data.get('variations', []),
            'order_items': data.get('products', []),
        }]
        print("  (Legacy single-product payload detected — wrapped into product_entries[0])")
    else:
        print(f"  Multi-product payload: {len(product_entries)} product(s)")

    # ----------------------------------------------------------------
    # 2. EXTRACT — PURCHASE ORDER FIELDS
    # ----------------------------------------------------------------
    supplier_id = data.get('supplier_id')
    warehouse_id = data.get('warehouse_id')
    store_id = safe_int(data.get('store_id', 1))
    order_tax = safe_float(data.get('order_tax', 0.0))
    discount = safe_float(data.get('discount', 0.0))
    status = data.get('status', 'Pending')
    grand_total = safe_float(data.get('grand_total', 0.0))

    po_payment_status = data.get('payment_status', 'Unpaid')
    po_payment_type = data.get('payment_type') or None
    po_paid_amount_input = safe_float(data.get('paid_amount', 0.0))

    # ----------------------------------------------------------------
    # 3. EXTRACT — GRN FIELDS (optional block)
    # ----------------------------------------------------------------
    save_grn = bool(data.get('save_grn', False))
    grn_payload = data.get('grn') or {}

    grn_status = (grn_payload.get('grn_status') or 'pending').strip().lower()

    # ----------------------------------------------------------------
    # 4. STATUS GUARD
    # ----------------------------------------------------------------
    if status and status.lower() == 'received':
        return jsonify({
            'error': 'Status "Received" not allowed. Use "Ordered"/"Pending" and create a GRN instead.',
            'allowed_statuses': ['Pending', 'Ordered']
        }), 400

    # ----------------------------------------------------------------
    # 5. PROCESS IMAGES / CERTIFICATE — per product entry
    # ----------------------------------------------------------------
    all_saved_files = []  # track ALL saved files across all entries for cleanup on error
    per_entry_images = []  # list of (product_image_json, certificate_image_path) tuples

    for entry_idx, entry in enumerate(product_entries):
        product_images_data = entry.get('product_image')
        certificate_data = entry.get('certificate_image')

        saved_image_paths = []
        if product_images_data:
            try:
                images_list = (
                    json.loads(product_images_data)
                    if isinstance(product_images_data, str)
                    else product_images_data
                )
                if len(images_list) > 10:
                    # cleanup everything saved so far
                    for fp in all_saved_files:
                        try:
                            if os.path.exists(fp): os.remove(fp)
                        except Exception:
                            pass
                    return jsonify({'error': f'product_entries[{entry_idx}]: Maximum 10 images allowed'}), 400

                for idx, img_base64 in enumerate(images_list, 1):
                    if img_base64:
                        file_path = save_base64_file(img_base64, f'product_{entry_idx}_img_{idx}')
                        if file_path:
                            saved_image_paths.append(file_path)
                            all_saved_files.append(file_path)
            except Exception as e:
                print(f"❌ Error processing images for entry {entry_idx}: {e}")

        saved_certificate_path = None
        if certificate_data:
            try:
                saved_certificate_path = save_base64_file(certificate_data, f'certificate_{entry_idx}')
                if saved_certificate_path:
                    all_saved_files.append(saved_certificate_path)
            except Exception as e:
                print(f"❌ Error processing certificate for entry {entry_idx}: {e}")

        product_image_json = json.dumps(saved_image_paths) if saved_image_paths else None
        per_entry_images.append((product_image_json, saved_certificate_path))

    # ----------------------------------------------------------------
    # 6. VALIDATION
    # ----------------------------------------------------------------
    missing = []

    # Purchase-level required fields
    if not supplier_id:
        missing.append('supplier_id')
    if not warehouse_id:
        missing.append('warehouse_id')

    # Per-product-entry validation
    product_required = ['product_name', 'sku', 'product_type', 'brand_id', 'category_id',
                        'base_unit_id', 'sale_unit_id', 'purchase_unit_id']

    for entry_idx, entry in enumerate(product_entries):
        prefix = f'product_entries[{entry_idx}]'
        for field in product_required:
            val = entry.get(field)
            if val is None or (isinstance(val, str) and not val.strip()):
                missing.append(f'{prefix}.{field}')

        entry_product_type = entry.get('product_type', 'single')
        if entry_product_type == 'single':
            if safe_float(entry.get('product_cost', 0)) <= 0:
                missing.append(f'{prefix}.product_cost')
            if safe_float(entry.get('product_price', 0)) <= 0:
                missing.append(f'{prefix}.product_price')
            if safe_float(entry.get('product_quantity', 0)) <= 0:
                missing.append(f'{prefix}.product_quantity')
        elif entry_product_type == 'variable':
            if not (entry.get('variations') or []):
                missing.append(f'{prefix}.variations')

        entry_order_items = entry.get('order_items') or []
        if not entry_order_items:
            missing.append(f'{prefix}.order_items')

    if save_grn:
        if not grn_payload.get('grn_date'):
            missing.append('grn.grn_date')
        if grn_status not in ('pending', 'approved'):
            missing.append('grn.grn_status')
        grn_pay_status = grn_payload.get('payment_status', 'Unpaid')
        if grn_pay_status not in ('Paid', 'Unpaid', 'Partial'):
            missing.append('grn.payment_status')
        if grn_pay_status in ('Paid', 'Partial') and not (grn_payload.get('payment_type') or None):
            missing.append('grn.payment_type')
        if grn_pay_status == 'Partial' and safe_float(grn_payload.get('paid_amount', 0)) <= 0:
            missing.append('grn.paid_amount')

    if missing:
        for fp in all_saved_files:
            try:
                if os.path.exists(fp): os.remove(fp)
            except Exception:
                pass
        return jsonify({
            'error': 'Validation failed',
            'missing_fields': list(set(missing))
        }), 400

    # Parse GRN dates early so bad input fails fast
    grn_date = None
    grn_invoice_date = None
    if save_grn:
        try:
            grn_date = datetime.strptime(grn_payload['grn_date'], '%Y-%m-%d').date()
        except ValueError:
            for fp in all_saved_files:
                try:
                    if os.path.exists(fp): os.remove(fp)
                except Exception:
                    pass
            return jsonify({'error': 'Invalid grn.grn_date format. Use YYYY-MM-DD'}), 400

        invoice_date_str = grn_payload.get('invoice_date')
        if invoice_date_str:
            try:
                grn_invoice_date = datetime.strptime(invoice_date_str, '%Y-%m-%d').date()
            except ValueError:
                for fp in all_saved_files:
                    try:
                        if os.path.exists(fp): os.remove(fp)
                    except Exception:
                        pass
                return jsonify({'error': 'Invalid grn.invoice_date format. Use YYYY-MM-DD'}), 400

    # ----------------------------------------------------------------
    # 7. DATABASE WORK — EVERYTHING IN ONE TRANSACTION
    # ----------------------------------------------------------------
    conn = get_db_connection()
    if not conn:
        for fp in all_saved_files:
            try:
                if os.path.exists(fp): os.remove(fp)
            except Exception:
                pass
        return jsonify({'error': 'Database connection failed'}), 500

    cursor = conn.cursor(dictionary=True)

    try:
        conn.start_transaction()
        print("\n🔄 Transaction started")

        # ---------- 7a. VALIDATE SHARED FOREIGN KEYS ----------
        cursor.execute("SELECT id FROM suppliers WHERE id = %s", (supplier_id,))
        if not cursor.fetchone():
            raise ValueError(f"Supplier with ID {supplier_id} not found")

        cursor.execute("SELECT id FROM warehouses WHERE id = %s", (warehouse_id,))
        if not cursor.fetchone():
            raise ValueError(f"Warehouse with ID {warehouse_id} not found")

        # ---------- 7b. LOOP: INSERT PRODUCTS + VARIATIONS ----------
        products_created = []
        all_order_items_enriched = []

        for entry_idx, entry in enumerate(product_entries):
            product_name = (entry.get('product_name') or '').strip()
            sku = (entry.get('sku') or '').strip()
            barcode_symbology = entry.get('barcode_symbology')
            product_type = entry.get('product_type', 'single')
            brand_id = entry.get('brand_id')
            category_id = entry.get('category_id')
            base_unit_id = entry.get('base_unit_id')
            sale_unit_id = entry.get('sale_unit_id')
            purchase_unit_id = entry.get('purchase_unit_id')
            stock_alert = safe_int(entry.get('stock_alert', 0))
            note = (entry.get('note') or '').strip()
            product_tax = safe_float(entry.get('product_tax', 0.0))
            tax_type = entry.get('tax_type', 'exclusive')
            expiration_date = entry.get('expiration_date') or None

            product_cost = safe_float(entry.get('product_cost', 0.0))
            product_price = safe_float(entry.get('product_price', 0.0))
            product_quantity = safe_float(entry.get('product_quantity', 0.0))

            variations = entry.get('variations', []) or []
            entry_order_items = entry.get('order_items', []) or []

            product_image_json, certificate_image_path = per_entry_images[entry_idx]

            # Validate per-product foreign keys
            if brand_id:
                cursor.execute("SELECT id FROM brands WHERE id = %s", (brand_id,))
                if not cursor.fetchone():
                    raise ValueError(f"product_entries[{entry_idx}]: Brand with ID {brand_id} not found")

            if category_id:
                cursor.execute("SELECT id FROM categories WHERE id = %s", (category_id,))
                if not cursor.fetchone():
                    raise ValueError(f"product_entries[{entry_idx}]: Category with ID {category_id} not found")

            cursor.execute("SELECT id FROM base_units WHERE id = %s", (base_unit_id,))
            if not cursor.fetchone():
                raise ValueError(f"product_entries[{entry_idx}]: Base unit with ID {base_unit_id} not found")

            cursor.execute("SELECT id FROM units WHERE id = %s", (sale_unit_id,))
            if not cursor.fetchone():
                raise ValueError(f"product_entries[{entry_idx}]: Sale unit with ID {sale_unit_id} not found")

            cursor.execute("SELECT id FROM units WHERE id = %s", (purchase_unit_id,))
            if not cursor.fetchone():
                raise ValueError(f"product_entries[{entry_idx}]: Purchase unit with ID {purchase_unit_id} not found")

            # INSERT PRODUCT
            print(f"\n📦 Inserting product {entry_idx + 1}/{len(product_entries)}: '{product_name}'...")
            cursor.execute("""
                INSERT INTO products (
                    product_name, sku, barcode_symbology, product_type,
                    expiration_date, brand_id, category_id,
                    base_unit_id, sale_unit_id, purchase_unit_id,
                    stock_alert, note, product_tax, tax_type,
                    product_image, certificate_image, created_at
                )
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW())
            """, (
                product_name, sku, barcode_symbology, product_type,
                expiration_date, brand_id, category_id,
                base_unit_id, sale_unit_id, purchase_unit_id,
                stock_alert, note, product_tax, tax_type,
                product_image_json, certificate_image_path
            ))
            product_id = cursor.lastrowid
            print(f"✅ Product created with ID: {product_id}")

            # INSERT VARIATIONS (if any)
            variation_map = {}
            if product_type == 'variable' and variations:
                print(f"\n🔢 Inserting {len(variations)} variations for product {product_id}...")
                for vidx, var in enumerate(variations, 1):
                    cursor.execute("""
                        INSERT INTO product_variations (
                            product_id, variation_name, variation_type, variation_sku,
                            variation_cost, variation_price, variation_tax_type, variation_tax,
                            variation_stock_alert, expiration_date, created_at
                        )
                        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW())
                    """, (
                        product_id,
                        var.get('variation_name'),
                        var.get('variation_type'),
                        var.get('variation_sku'),
                        safe_float(var.get('variation_cost')),
                        safe_float(var.get('variation_price')),
                        var.get('variation_tax_type', 'exclusive'),
                        safe_float(var.get('variation_tax')),
                        safe_int(var.get('variation_stock_alert')),
                        var.get('expiration_date')
                    ))
                    variation_id = cursor.lastrowid
                    variation_map[var.get('variation_sku')] = {
                        'id': variation_id,
                        'quantity': safe_float(var.get('variation_quantity', 0)),
                        'data': var
                    }
                    print(f"  ✅ Variation {vidx}: {var.get('variation_type')} (ID: {variation_id})")

            # Build enriched order items for this product (with correct product_id)
            for oi_idx, prod in enumerate(entry_order_items, 1):
                sku_match = prod.get('sku')
                variation_info = variation_map.get(sku_match) if sku_match else None
                vid = variation_info['id'] if variation_info else None

                if variation_info:
                    var_data = variation_info['data']
                    default_exp_date = var_data.get('expiration_date')
                    default_tax_percentage = safe_float(var_data.get('variation_tax'))
                    default_tax_type = var_data.get('variation_tax_type', 'exclusive')
                    default_cost = safe_float(var_data.get('variation_cost'))
                    default_price = safe_float(var_data.get('variation_price'))
                    purchase_unit_name = prod.get('purchase_unit', '')
                else:
                    default_exp_date = expiration_date
                    default_tax_percentage = product_tax
                    default_tax_type = tax_type
                    default_cost = product_cost
                    default_price = product_price
                    purchase_unit_name = prod.get('purchase_unit', '')

                # Use per-batch values from frontend; fall back to product/variation defaults
                quantity_value = safe_float(prod.get('quantity'))
                unit_price_cost = safe_float(prod.get('unit_price', default_cost))
                net_unit_cost_value = safe_float(prod.get('price'))
                batch_selling_price = safe_float(prod.get('selling_price', default_price))
                our_price_value = safe_float(prod.get('our_price', batch_selling_price))
                tax_percentage = safe_float(prod.get('product_tax', default_tax_percentage))
                tax_type_value = prod.get('tax_type', default_tax_type) or default_tax_type
                subtotal = safe_float(prod.get('subtotal'))
                tax_amount = safe_float(prod.get('tax'))
                discount_amount = safe_float(prod.get('discount', 0.0))
                exp_date = prod.get('expiration_date') or default_exp_date

                all_order_items_enriched.append({
                    'product_id': product_id,
                    'variation_id': vid,
                    'ordered_quantity': quantity_value,
                    'unit_price': unit_price_cost,
                    'batch_price': batch_selling_price,
                    'our_price': our_price_value,
                    'discount': discount_amount,
                    'discount_type': 'fixed',
                    'product_tax': tax_percentage,
                    'tax_type': tax_type_value,
                    'net_unit_cost': net_unit_cost_value,
                    'purchase_unit': purchase_unit_name,
                    'quality_check': 'passed',
                    'note': '',
                    'expiration_date': exp_date,
                    # extra fields for order_items INSERT
                    '_subtotal': subtotal,
                    '_tax_amount': tax_amount,
                    '_selling_price': batch_selling_price,
                })

            products_created.append({
                'product_id': product_id,
                'product_name': product_name,
                'sku': sku,
                'product_type': product_type,
            })

        # ---------- 7c. PURCHASE ORDER — PAYMENT CALCULATION ----------
        if po_payment_status == 'Paid':
            po_paid_amount = grand_total
            po_due_amount = 0
        elif po_payment_status == 'Partial':
            po_paid_amount = po_paid_amount_input
            po_due_amount = max(grand_total - po_paid_amount_input, 0)
        else:  # Unpaid
            po_paid_amount = 0
            po_due_amount = grand_total

        # ---------- 7d. INSERT PURCHASE ORDER (once) ----------
        # Use note from the first product entry for PO note (backward compat)
        po_note = (product_entries[0].get('note') or '').strip() if product_entries else ''
        print(f"\n🛒 Creating purchase order...")
        cursor.execute("""
            INSERT INTO purchase_orders (
                supplier_id, warehouse_id, store_id,
                note, order_tax, discount,
                status, grn_status,
                grand_total,
                payment_status, payment_type, paid_amount, due_amount,
                created_on
            )
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW())
        """, (
            supplier_id, warehouse_id, store_id,
            po_note, order_tax, discount,
            status, 'not_received',
            grand_total,
            po_payment_status, po_payment_type, po_paid_amount, po_due_amount
        ))
        order_id = cursor.lastrowid
        print(f"✅ Purchase order created with ID: {order_id}")

        # ---------- 7e. INSERT ALL ORDER ITEMS ----------
        print(f"\n📝 Creating {len(all_order_items_enriched)} order items across {len(products_created)} product(s)...")

        for idx, item in enumerate(all_order_items_enriched, 1):
            cursor.execute("""
                INSERT INTO order_items (
                    order_id, product_id, variation_id,
                    product_tax, tax_type, quantity,
                    purchase_unit, discount, discount_type,
                    tax, subtotal, expiration_date,
                    unit_price, net_unit_cost, selling_price,
                    created_on
                )
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW())
            """, (
                order_id, item['product_id'], item['variation_id'],
                item['product_tax'], item['tax_type'], item['ordered_quantity'],
                item['purchase_unit'], item['discount'], 'fixed',
                item['_tax_amount'], item['_subtotal'], item['expiration_date'],
                item['unit_price'], item['net_unit_cost'], item['_selling_price']
            ))
            order_item_id = cursor.lastrowid
            print(f"  ✅ Order item {idx} created (ID: {order_item_id}, product_id: {item['product_id']})")

        print("\n⚠️  Batches and warehouse stock are only created automatically "
              "when the GRN is saved with status='approved' (see step below). "
              "A 'pending' GRN leaves stock untouched until it's approved later.")

        # ----------------------------------------------------------------
        # 8. OPTIONAL GRN STEP
        # ----------------------------------------------------------------
        grn_id = None
        grn_code = None
        stock_batches_created = []

        if save_grn:
            print(f"\n📋 Creating GRN for Purchase Order #{order_id} (status: {grn_status})...")

            grn_warehouse_id = safe_int(grn_payload.get('warehouse_id')) or warehouse_id
            grn_supplier_id = safe_int(grn_payload.get('supplier_id')) or supplier_id

            cursor.execute("SELECT id FROM warehouses WHERE id = %s AND is_active = 1", (grn_warehouse_id,))
            if not cursor.fetchone():
                raise ValueError(f"GRN warehouse {grn_warehouse_id} not found or inactive")

            cursor.execute("SELECT id FROM suppliers WHERE id = %s", (grn_supplier_id,))
            if not cursor.fetchone():
                raise ValueError(f"GRN supplier {grn_supplier_id} not found")

            # ---- Received By: ALWAYS the logged-in user, resolved from the JWT ----
            current_user = get_jwt_identity()
            received_by_id = None
            received_by_name = None
            if current_user:
                cursor.execute("SELECT id, name, email FROM users WHERE id = %s", (current_user,))
                user = cursor.fetchone()
                if user:
                    received_by_id = user['id']
                    received_by_name = user.get('name') or user.get('email')
            if not received_by_id:
                cursor.execute("SELECT id, name, email FROM users WHERE role = 'admin' LIMIT 1")
                admin = cursor.fetchone()
                if admin:
                    received_by_id = admin['id']
                    received_by_name = admin.get('name') or admin.get('email')
            if not received_by_id:
                raise ValueError("No valid user found to assign as GRN receiver")

            print(f"  👤 Received By resolved from session: {received_by_name} (user ID: {received_by_id})")

            grn_code = generate_grn_code(cursor)
            print(f"  ✅ GRN Code: {grn_code}")

            raw_grn_items = grn_payload.get('items') or all_order_items_enriched

            grn_items_data = []
            item_subtotal_total = 0.0

            for idx, item in enumerate(raw_grn_items, 1):
                p_id = safe_int(item.get('product_id'))
                v_id = safe_int(item.get('variation_id')) if item.get('variation_id') else None
                ordered_qty = safe_float(item.get('ordered_quantity', 0))
                received_qty = safe_float(item.get('received_quantity', ordered_qty))
                rejected_qty = safe_float(item.get('rejected_quantity', 0))

                unit_price = safe_float(item.get('unit_price', 0))
                batch_price = safe_float(item.get('batch_price', 0))
                our_price = safe_float(item.get('our_price', batch_price))
                item_discount = safe_float(item.get('discount', 0))
                discount_type = item.get('discount_type', 'fixed')
                item_tax = safe_float(item.get('product_tax', 0))
                item_tax_type = item.get('tax_type', 'exclusive')
                net_unit_cost = safe_float(item.get('net_unit_cost', unit_price))
                unit = item.get('purchase_unit', '')
                quality_check = item.get('quality_check', 'pending')
                item_note = (item.get('note') or '').strip()

                exp_str = item.get('expiration_date')
                item_expiration_date = None
                if exp_str:
                    try:
                        item_expiration_date = datetime.strptime(str(exp_str), '%Y-%m-%d').date()
                    except (ValueError, TypeError):
                        item_expiration_date = None

                accepted_qty = received_qty - rejected_qty
                if accepted_qty < 0:
                    raise ValueError(f"GRN item {idx}: accepted quantity cannot be negative")

                if discount_type == 'percentage':
                    discount_per_unit = (unit_price * item_discount) / 100
                else:
                    discount_per_unit = item_discount

                price_after_discount = max(unit_price - discount_per_unit, 0)

                if item_tax_type == 'inclusive':
                    tax_per_unit = (price_after_discount * item_tax) / (100 + item_tax) if item_tax else 0
                    subtotal_per_unit = price_after_discount
                else:
                    tax_per_unit = (price_after_discount * item_tax) / 100 if item_tax else 0
                    subtotal_per_unit = price_after_discount + tax_per_unit

                total_discount_amount = discount_per_unit * accepted_qty
                total_tax_amount = tax_per_unit * accepted_qty
                item_subtotal_calc = accepted_qty * subtotal_per_unit
                item_subtotal_total += item_subtotal_calc

                grn_items_data.append({
                    'product_id': p_id, 'variation_id': v_id,
                    'ordered_quantity': ordered_qty, 'received_quantity': received_qty,
                    'rejected_quantity': rejected_qty, 'accepted_quantity': accepted_qty,
                    'unit_price': unit_price, 'batch_price': batch_price, 'our_price': our_price,
                    'discount_type': discount_type, 'discount': item_discount,
                    'tax_type': item_tax_type, 'product_tax': item_tax,
                    'discount_amount': total_discount_amount, 'tax_amount': total_tax_amount,
                    'net_unit_cost': net_unit_cost, 'subtotal': item_subtotal_calc,
                    'purchase_unit': unit, 'quality_check': quality_check, 'note': item_note,
                    'expiration_date': item_expiration_date
                })

            grn_order_tax_percent = safe_float(grn_payload.get('order_tax', 0))
            grn_order_discount = safe_float(grn_payload.get('discount', 0))
            grn_payment_status = grn_payload.get('payment_status', 'Unpaid')
            grn_payment_type = grn_payload.get('payment_type') or None
            grn_paid_amount_input = safe_float(grn_payload.get('paid_amount', 0))

            subtotal_after_discount = max(item_subtotal_total - grn_order_discount, 0)
            grn_order_tax_amount = (subtotal_after_discount * grn_order_tax_percent) / 100
            grn_grand_total = subtotal_after_discount + grn_order_tax_amount

            if grn_payment_status == 'Paid':
                grn_actual_paid = grn_grand_total
                grn_due_amount = 0
            elif grn_payment_status == 'Partial':
                grn_actual_paid = grn_paid_amount_input
                grn_due_amount = max(grn_grand_total - grn_paid_amount_input, 0)
            else:
                grn_actual_paid = 0
                grn_due_amount = grn_grand_total

            grn_db_status = 'completed' if grn_status == 'approved' else 'pending'

            cursor.execute("""
                INSERT INTO grn (
                    grn_code, purchase_order_id, supplier_id, warehouse_id, store_id,
                    grn_date, received_by, invoice_number, invoice_date,
                    vehicle_number, driver_name, driver_contact,
                    status, total_items, subtotal, tax, discount, grand_total,
                    order_tax, payment_status, payment_type, paid_amount, due_amount,
                    note, created_by, created_at
                ) VALUES (
                    %s, %s, %s, %s, %s,
                    %s, %s, %s, %s,
                    %s, %s, %s,
                    %s, %s, %s, %s, %s, %s,
                    %s, %s, %s, %s, %s,
                    %s, %s, NOW()
                )
            """, (
                grn_code, order_id, grn_supplier_id, grn_warehouse_id, store_id,
                grn_date, received_by_id,
                grn_payload.get('invoice_number') or None, grn_invoice_date,
                grn_payload.get('vehicle_number') or None, grn_payload.get('driver_name') or None,
                grn_payload.get('driver_contact') or None,
                grn_db_status, len(grn_items_data), item_subtotal_total, grn_order_tax_amount,
                grn_order_discount, grn_grand_total,
                grn_order_tax_percent, grn_payment_status, grn_payment_type,
                grn_actual_paid, grn_due_amount,
                (grn_payload.get('note') or '').strip(), received_by_id
            ))
            grn_id = cursor.lastrowid
            print(f"  ✅ GRN created with ID: {grn_id} (business status: {grn_status} → db status: {grn_db_status})")

            for item_data in grn_items_data:
                cursor.execute("""
                    INSERT INTO grn_items (
                        grn_id, product_id, variation_id,
                        ordered_quantity, received_quantity, rejected_quantity,
                        unit_price, batch_price, our_price,
                        discount_type, product_discount,
                        tax_type, product_tax,
                        discount, tax, net_unit_cost, subtotal,
                        purchase_unit, quality_check, note,
                        expiration_date, created_at
                    ) VALUES (
                        %s, %s, %s,
                        %s, %s, %s,
                        %s, %s, %s,
                        %s, %s,
                        %s, %s,
                        %s, %s, %s, %s,
                        %s, %s, %s,
                        %s, NOW()
                    )
                """, (
                    grn_id, item_data['product_id'], item_data['variation_id'],
                    item_data['ordered_quantity'], item_data['received_quantity'],
                    item_data['rejected_quantity'],
                    item_data['unit_price'], item_data['batch_price'], item_data['our_price'],
                    item_data['discount_type'], item_data['discount'],
                    item_data['tax_type'], item_data['product_tax'],
                    item_data['discount_amount'], item_data['tax_amount'],
                    item_data['net_unit_cost'], item_data['subtotal'],
                    item_data['purchase_unit'], item_data['quality_check'], item_data['note'],
                    item_data['expiration_date']
                ))
                print(f"    ✅ GRN item inserted (ID: {cursor.lastrowid}) — our_price={item_data['our_price']}")

            # ---- 8a. IF APPROVED: create stock batches + update warehouse_stock NOW ----
            if grn_status == 'approved':
                print(f"\n  🟢 GRN status is 'approved' — adding stock to warehouse {grn_warehouse_id} now...")
                stock_batches_created = apply_stock_from_grn_items(
                    cursor, grn_items_data, order_id, grn_id, grn_code, grn_warehouse_id, store_id
                )
                print(f"  ✅ {len(stock_batches_created)} batch(es) created and stock added.")
            else:
                print(f"\n  🟡 GRN status is 'pending' — stock NOT added. "
                      f"Approve this GRN later to bring the stock in.")

            # ---- 8b. Update PO grn_status ----
            cursor.execute("""
                SELECT
                    SUM(oi.quantity) as total_ordered,
                    COALESCE((
                        SELECT SUM(gi.received_quantity - gi.rejected_quantity)
                        FROM grn g2
                        INNER JOIN grn_items gi ON g2.grn_id = gi.grn_id
                        WHERE g2.purchase_order_id = %s AND g2.status != 'cancelled'
                    ), 0) as total_received
                FROM order_items oi
                WHERE oi.order_id = %s
            """, (order_id, order_id))
            qty_result = cursor.fetchone()
            total_ordered = safe_float(qty_result['total_ordered'])
            total_received = safe_float(qty_result['total_received'])

            if total_received == 0:
                new_grn_status = 'not_received'
            elif total_received < total_ordered:
                new_grn_status = 'partial'
            else:
                new_grn_status = 'fully_received'

            new_po_status = 'Received' if new_grn_status == 'fully_received' else status

            cursor.execute(
                "UPDATE purchase_orders SET grn_status = %s, status = %s WHERE order_id = %s",
                (new_grn_status, new_po_status, order_id)
            )
            print(f"  ✅ PO GRN status set to: {new_grn_status} | PO status set to: {new_po_status}")

            product_ids_str = ', '.join(f"#{p['product_id']}" for p in products_created)
            activity_description = (
                f"GRN {grn_code} auto-created together with Product(s) {product_ids_str} and "
                f"PO #{order_id}. Warehouse: {grn_warehouse_id}, Supplier: {grn_supplier_id}, "
                f"Received By: {received_by_name}, Status: {grn_status}, "
                f"Grand Total: LKR {grn_grand_total:.2f}"
            )
            if grn_status == 'approved':
                activity_description += f". Stock added: {len(stock_batches_created)} batch(es)."

            cursor.execute("""
                INSERT INTO grn_activity_log (grn_id, user_id, action, description, created_at)
                VALUES (%s, %s, %s, %s, NOW())
            """, (
                grn_id, received_by_id,
                'GRN Created & Approved (Stock Added)' if grn_status == 'approved' else 'GRN Created (Pending)',
                activity_description
            ))
            print("  ✅ Activity logged")

        # ----------------------------------------------------------------
        # 9. COMMIT
        # ----------------------------------------------------------------
        conn.commit()
        print("=" * 80)
        print(f"✅ TRANSACTION COMMITTED — {len(products_created)} Product(s) + Purchase Order"
              + (f" + GRN ({grn_status})" if save_grn else "") + " saved.")
        print("=" * 80)

        product_count = len(products_created)
        msg = (
            f"{product_count} Product(s), Purchase Order"
            f"{' and GRN' if save_grn else ''} created successfully."
        )

        response_data = {
            'products_created': products_created,
            # Backward compat: keep product_id / product_name for single-product callers
            'product_id': products_created[0]['product_id'] if product_count == 1 else None,
            'product_name': products_created[0]['product_name'] if product_count == 1 else None,
            'order_id': order_id,
            'status': status,
            'grn_id': grn_id,
            'grn_code': grn_code,
            'grn_saved': save_grn,
            'grn_status': grn_status if save_grn else None,
            'stock_added': save_grn and grn_status == 'approved',
            'batches_created': len(stock_batches_created)
        }

        return jsonify({
            'success': True,
            'message': msg,
            'data': response_data
        }), 201

    except mysql.connector.IntegrityError as err:
        conn.rollback()
        for fp in all_saved_files:
            try:
                if os.path.exists(fp): os.remove(fp)
            except Exception:
                pass
        error_msg = str(err)
        print(f"\n❌ Integrity Error: {error_msg}")
        if 'Duplicate entry' in error_msg:
            if 'sku' in error_msg.lower():
                return jsonify({'error': 'SKU already exists'}), 409
            return jsonify({'error': 'Duplicate entry'}), 409
        if 'foreign key constraint' in error_msg.lower():
            return jsonify({'error': 'Invalid supplier, warehouse, brand, category or unit ID'}), 400
        return jsonify({'error': f'Database error: {error_msg}'}), 500

    except ValueError as err:
        conn.rollback()
        for fp in all_saved_files:
            try:
                if os.path.exists(fp): os.remove(fp)
            except Exception:
                pass
        print(f"\n❌ Validation Error: {err}")
        return jsonify({'error': str(err)}), 400

    except Exception as e:
        conn.rollback()
        for fp in all_saved_files:
            try:
                if os.path.exists(fp): os.remove(fp)
            except Exception:
                pass
        print(f"\n❌ Unexpected Error: {e}")
        traceback.print_exc()
        return jsonify({'error': f'Server error: {str(e)}'}), 500

    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()
        print("\n🔒 Database connection closed\n")
# ==========================================
# ✅ FIXED: ADD PRODUCT WITH PURCHASE ORDER
# ==========================================
# FIX: Use dictionary cursor and properly handle all query results

@product_bp.route('/add_product_and_order', methods=['POST'])
@jwt_required()
@role_required('admin', 'manager')
def add_product_and_order():
    """
    ✅ FIXED: Add new product with purchase order
    
    KEY FIX: Proper cursor result handling to avoid "Commands out of sync" errors
    
    Changes:
    1. Use cursor(dictionary=True) consistently
    2. Consume all results with fetchone()/fetchall() before next query
    3. Check cursor.rowcount instead of checking fetchone() existence for validation
    4. ✅ NEW: Validate base_unit_id against base_units table (not units table)
    """
    
    data = request.get_json()
    if not data:
        return jsonify({'error': 'No data provided'}), 400
    
    print("=" * 80)
    print("📦 NEW PRODUCT REQUEST (FIXED - PROPER CURSOR HANDLING)")
    print("=" * 80)
    
    # Extract fields (same as before)
    product_name = data.get('product_name', '').strip()
    sku = data.get('sku', '').strip()
    barcode_symbology = data.get('barcode_symbology')
    product_type = data.get('product_type', 'single')
    brand_id = data.get('brand_id')
    category_id = data.get('category_id')
    base_unit_id = data.get('base_unit_id')
    sale_unit_id = data.get('sale_unit_id')
    purchase_unit_id = data.get('purchase_unit_id')
    stock_alert = safe_int(data.get('stock_alert', 0))
    note = data.get('note', '').strip()
    product_tax = safe_float(data.get('product_tax', 0.0))
    tax_type = data.get('tax_type', 'exclusive')
    expiration_date = data.get('expiration_date') or None
    
    product_cost = safe_float(data.get('product_cost', 0.0))
    product_price = safe_float(data.get('product_price', 0.0))
    product_quantity = safe_float(data.get('product_quantity', 0.0))
    
    variations = data.get('variations', [])
    
    supplier_id = data.get('supplier_id')
    warehouse_id = data.get('warehouse_id')
    store_id = safe_int(data.get('store_id', 1))
    order_tax = safe_float(data.get('order_tax', 0.0))
    discount = safe_float(data.get('discount', 0.0))
    status = data.get('status', 'Pending')
    
    print("\n📋 EXTRACTED FIELDS:")
    print(f"  product_name: {product_name}")
    print(f"  sku: {sku}")
    print(f"  base_unit_id: {base_unit_id} (from base_units table)")
    print(f"  sale_unit_id: {sale_unit_id} (from units table)")
    print(f"  purchase_unit_id: {purchase_unit_id} (from units table)")
    print(f"  product_cost (PURCHASE): {product_cost} ✅")
    print(f"  product_price (SELLING): {product_price} ✅")
    print(f"  supplier_id: {supplier_id}")
    print("=" * 80)
    
    # Prevent "Received" status
    if status and status.lower() == 'received':
        return jsonify({
            'error': 'Status "Received" not allowed. Use "Ordered" and create GRN.',
            'allowed_statuses': ['Pending', 'Ordered']
        }), 400
    
    grn_status = 'not_received'
    grand_total = safe_float(data.get('grand_total', 0.0))
    products_list = data.get('products', [])
    
    # Process images (same as before)
    product_images_data = data.get('product_image')
    certificate_data = data.get('certificate_image')
    
    saved_image_paths = []
    if product_images_data:
        try:
            images_list = json.loads(product_images_data) if isinstance(product_images_data, str) else product_images_data
            
            if len(images_list) > 10:
                return jsonify({'error': 'Maximum 10 images allowed'}), 400
            
            for idx, img_base64 in enumerate(images_list, 1):
                if img_base64:
                    file_path = save_base64_file(img_base64, f'product_img_{idx}')
                    if file_path:
                        saved_image_paths.append(file_path)
        except Exception as e:
            print(f"❌ Error processing images: {e}")
    
    saved_certificate_path = None
    if certificate_data:
        try:
            saved_certificate_path = save_base64_file(certificate_data, 'certificate')
        except Exception as e:
            print(f"❌ Error processing certificate: {e}")
    
    product_image_json = json.dumps(saved_image_paths) if saved_image_paths else None
    certificate_image_path = saved_certificate_path
    
    # Validation (same as before)
    required_fields = [
        'product_name', 'sku', 'product_type',
        'brand_id', 'category_id', 'base_unit_id',
        'sale_unit_id', 'purchase_unit_id',
        'supplier_id', 'warehouse_id'
    ]
    
    missing = validate_required_fields(data, required_fields)
    
    if product_type == 'single':
        if product_cost <= 0:
            missing.append('product_cost')
        if product_price <= 0:
            missing.append('product_price')
        if product_quantity <= 0:
            missing.append('product_quantity')
    elif product_type == 'variable':
        if not variations or len(variations) == 0:
            missing.append('variations')
    
    if missing:
        cleanup_uploaded_files(saved_image_paths, saved_certificate_path)
        return jsonify({
            'error': 'Validation failed',
            'missing_fields': list(set(missing))
        }), 400
    
    # Database operations
    conn = get_db_connection()
    if not conn:
        cleanup_uploaded_files(saved_image_paths, saved_certificate_path)
        return jsonify({'error': 'Database connection failed'}), 500
    
    # ✅ FIX: Use dictionary cursor
    cursor = conn.cursor(dictionary=True)
    
    try:
        conn.start_transaction()
        print("\n🔄 Transaction started")
        
        # ✅ FIX: Validate supplier - consume result immediately
        print(f"\n🔍 Validating supplier_id: {supplier_id}")
        cursor.execute("SELECT id FROM suppliers WHERE id = %s", (supplier_id,))
        supplier_result = cursor.fetchone()  # ✅ Consume result
        
        if not supplier_result:
            raise ValueError(f"Supplier with ID {supplier_id} not found")
        print(f"✅ Supplier validated")
        
        # ✅ FIX: Validate warehouse - consume result immediately
        print(f"🔍 Validating warehouse_id: {warehouse_id}")
        cursor.execute("SELECT id FROM warehouses WHERE id = %s", (warehouse_id,))
        warehouse_result = cursor.fetchone()  # ✅ Consume result
        
        if not warehouse_result:
            raise ValueError(f"Warehouse with ID {warehouse_id} not found")
        print(f"✅ Warehouse validated")
        
        # ✅ FIX: Validate brand - consume result immediately
        if brand_id:
            print(f"🔍 Validating brand_id: {brand_id}")
            cursor.execute("SELECT id FROM brands WHERE id = %s", (brand_id,))
            brand_result = cursor.fetchone()  # ✅ Consume result
            
            if not brand_result:
                raise ValueError(f"Brand with ID {brand_id} not found")
            print(f"✅ Brand validated")
        
        # ✅ FIX: Validate category - consume result immediately
        if category_id:
            print(f"🔍 Validating category_id: {category_id}")
            cursor.execute("SELECT id FROM categories WHERE id = %s", (category_id,))
            category_result = cursor.fetchone()  # ✅ Consume result
            
            if not category_result:
                raise ValueError(f"Category with ID {category_id} not found")
            print(f"✅ Category validated")
        
        # ✅ FIXED: Validate base_unit_id against base_units table (NOT units table)
        print(f"🔍 Validating base_unit_id: {base_unit_id} (from base_units table)")
        cursor.execute("SELECT id FROM base_units WHERE id = %s", (base_unit_id,))
        base_unit_result = cursor.fetchone()  # ✅ Consume result
        
        if not base_unit_result:
            raise ValueError(f"Base unit with ID {base_unit_id} not found in base_units table")
        print(f"✅ Base unit validated")
        
        # ✅ FIXED: Validate sale_unit_id against units table
        print(f"🔍 Validating sale_unit_id: {sale_unit_id} (from units table)")
        cursor.execute("SELECT id FROM units WHERE id = %s", (sale_unit_id,))
        sale_unit_result = cursor.fetchone()  # ✅ Consume result
        
        if not sale_unit_result:
            raise ValueError(f"Sale unit with ID {sale_unit_id} not found in units table")
        print(f"✅ Sale unit validated")
        
        # ✅ FIXED: Validate purchase_unit_id against units table
        print(f"🔍 Validating purchase_unit_id: {purchase_unit_id} (from units table)")
        cursor.execute("SELECT id FROM units WHERE id = %s", (purchase_unit_id,))
        purchase_unit_result = cursor.fetchone()  # ✅ Consume result
        
        if not purchase_unit_result:
            raise ValueError(f"Purchase unit with ID {purchase_unit_id} not found in units table")
        print(f"✅ Purchase unit validated")

        # Check duplicate product SKU
        cursor.execute("SELECT id, product_name FROM products WHERE sku = %s LIMIT 1", (sku,))
        sku_conflict = cursor.fetchone()
        if sku_conflict:
            raise ValueError(f"SKU '{sku}' already exists in product '{sku_conflict['product_name']}'")

        cursor.execute(
            "SELECT pv.id, p.product_name FROM product_variations pv JOIN products p ON pv.product_id = p.id WHERE pv.variation_sku = %s LIMIT 1",
            (sku,)
        )
        var_sku_conflict = cursor.fetchone()
        if var_sku_conflict:
            raise ValueError(f"SKU '{sku}' already used as a variation SKU in product '{var_sku_conflict['product_name']}'")

        # Check duplicate variation SKUs
        if product_type == 'variable' and variations:
            var_skus = [v.get('variation_sku', '').strip() for v in variations if v.get('variation_sku', '').strip()]
            seen = set()
            for vs in var_skus:
                if vs in seen:
                    raise ValueError(f"Duplicate variation SKU '{vs}' in submitted variations")
                seen.add(vs)
            for vs in var_skus:
                cursor.execute("SELECT id, product_name FROM products WHERE sku = %s LIMIT 1", (vs,))
                conflict = cursor.fetchone()
                if conflict:
                    raise ValueError(f"Variation SKU '{vs}' already exists as a product SKU in '{conflict['product_name']}'")
                cursor.execute(
                    "SELECT pv.id, p.product_name FROM product_variations pv JOIN products p ON pv.product_id = p.id WHERE pv.variation_sku = %s LIMIT 1",
                    (vs,)
                )
                conflict = cursor.fetchone()
                if conflict:
                    raise ValueError(f"Variation SKU '{vs}' already exists in product '{conflict['product_name']}'")

        # Insert product
        print(f"\n📦 Inserting product '{product_name}'...")
        cursor.execute("""
            INSERT INTO products (
                product_name, sku, barcode_symbology, product_type,
                expiration_date, brand_id, category_id,
                base_unit_id, sale_unit_id, purchase_unit_id,
                stock_alert, note, product_tax, tax_type,
                product_image, certificate_image, created_at
            )
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW())
        """, (
            product_name, sku, barcode_symbology, product_type,
            expiration_date, brand_id, category_id,
            base_unit_id, sale_unit_id, purchase_unit_id,
            stock_alert, note, product_tax, tax_type,
            product_image_json, certificate_image_path
        ))
        
        product_id = cursor.lastrowid
        print(f"✅ Product created with ID: {product_id}")
        
        # Insert variations if needed
        variation_map = {}
        if product_type == 'variable' and variations:
            print(f"\n🔢 Inserting {len(variations)} variations...")
            for idx, var in enumerate(variations, 1):
                cursor.execute("""
                    INSERT INTO product_variations (
                        product_id, variation_name, variation_type, variation_sku,
                        variation_cost, variation_price, variation_tax_type, variation_tax,
                        variation_stock_alert, expiration_date, created_at
                    )
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW())
                """, (
                    product_id,
                    var.get('variation_name'),
                    var.get('variation_type'),
                    var.get('variation_sku'),
                    safe_float(var.get('variation_cost')),
                    safe_float(var.get('variation_price')),
                    var.get('variation_tax_type', 'exclusive'),
                    safe_float(var.get('variation_tax')),
                    safe_int(var.get('variation_stock_alert')),
                    var.get('expiration_date')
                ))
                
                variation_id = cursor.lastrowid
                variation_map[var.get('variation_sku')] = {
                    'id': variation_id,
                    'quantity': safe_float(var.get('variation_quantity', 0)),
                    'data': var
                }
                print(f"  ✅ Variation {idx}: {var.get('variation_type')} (ID: {variation_id})")
        
        # Create purchase order
        print(f"\n🛒 Creating purchase order...")
        
        cursor.execute("""
            INSERT INTO purchase_orders (
                supplier_id, warehouse_id, store_id,
                note, order_tax, discount, 
                status, grn_status,
                grand_total,
                payment_status, paid_amount, due_amount, 
                created_on
            )
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW())
        """, (
            supplier_id, warehouse_id, store_id,
            note, order_tax, discount,
            status,
            grn_status,
            grand_total,
            'Unpaid', 0.00, grand_total
        ))
        
        order_id = cursor.lastrowid
        print(f"✅ Purchase order created with ID: {order_id}")
        
        # Insert order items
        print(f"\n📝 Creating {len(products_list)} order items...")
        
        for idx, prod in enumerate(products_list, 1):
            sku_match = prod.get('sku')
            variation_info = variation_map.get(sku_match) if sku_match else None
            variation_id = variation_info['id'] if variation_info else None

            if variation_id:
                var_data = variation_info['data']
                default_exp_date = var_data.get('expiration_date')
                default_tax_percentage = safe_float(var_data.get('variation_tax'))
                default_tax_type = var_data.get('variation_tax_type', 'exclusive')
                default_cost = safe_float(var_data.get('variation_cost'))
                default_price = safe_float(var_data.get('variation_price'))
            else:
                default_exp_date = expiration_date
                default_tax_percentage = product_tax
                default_tax_type = tax_type
                default_cost = product_cost
                default_price = product_price

            # Use per-batch values from frontend; fall back to product/variation defaults
            quantity_value = safe_float(prod.get('quantity'))
            unit_price_cost = safe_float(prod.get('unit_price', default_cost))
            net_unit_cost_from_frontend = safe_float(prod.get('price'))
            batch_selling_price = safe_float(prod.get('selling_price', default_price))
            tax_percentage = safe_float(prod.get('product_tax', default_tax_percentage))
            tax_type_value = prod.get('tax_type', default_tax_type) or default_tax_type
            subtotal = safe_float(prod.get('subtotal'))
            tax_amount = safe_float(prod.get('tax'))
            discount_amount = safe_float(prod.get('discount', 0.0))
            exp_date = prod.get('expiration_date') or default_exp_date

            net_unit_cost_value = net_unit_cost_from_frontend

            print(f"\n  📌 Item {idx}:")
            print(f"     unit_price: {unit_price_cost}")
            print(f"     net_unit_cost: {net_unit_cost_value}")
            print(f"     selling_price: {batch_selling_price}")

            cursor.execute("""
                INSERT INTO order_items (
                    order_id, product_id, variation_id,
                    product_tax, tax_type, quantity,
                    purchase_unit, discount, discount_type,
                    tax, subtotal, expiration_date,
                    unit_price, net_unit_cost, selling_price,
                    created_on
                )
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW())
            """, (
                order_id, product_id, variation_id,
                tax_percentage,
                tax_type_value,
                quantity_value,
                prod.get('purchase_unit', ''),
                discount_amount,
                'fixed',
                tax_amount,
                subtotal,
                exp_date,
                unit_price_cost,
                net_unit_cost_value,
                batch_selling_price
            ))
            
            order_item_id = cursor.lastrowid
            print(f"     ✅ Order item created (ID: {order_item_id})")
        
        print("\n⚠️  Batches and stock will be created when GRN is approved")
        
        conn.commit()
        print("=" * 80)
        print("✅ TRANSACTION COMMITTED!")
        print("=" * 80)
        
        return jsonify({
            'success': True,
            'message': f"Product and order created successfully! {('Create GRN for PO #' + str(order_id)) if status == 'Ordered' else ''}",
            'data': {
                'product_id': product_id,
                'product_name': product_name,
                'order_id': order_id,
                'status': status,
                'grn_status': grn_status,
                'next_step': f"Create GRN for Purchase Order #{order_id}" if status == 'Ordered' else "Order created"
            }
        }), 201
    
    except mysql.connector.IntegrityError as err:
        conn.rollback()
        cleanup_uploaded_files(saved_image_paths, saved_certificate_path)
        error_msg = str(err)
        if 'Duplicate entry' in error_msg:
            if 'sku' in error_msg.lower():
                return jsonify({'error': 'SKU already exists'}), 409
            return jsonify({'error': 'Duplicate entry'}), 409
        elif 'foreign key constraint' in error_msg.lower():
            return jsonify({'error': 'Invalid supplier, warehouse, brand, category or unit ID'}), 400
        return jsonify({'error': f'Database error: {error_msg}'}), 500
    
    except ValueError as err:
        conn.rollback()
        cleanup_uploaded_files(saved_image_paths, saved_certificate_path)
        return jsonify({'error': str(err)}), 400
    
    except Exception as e:
        conn.rollback()
        cleanup_uploaded_files(saved_image_paths, saved_certificate_path)
        print(f"\n❌ ERROR: {e}")
        traceback.print_exc()
        return jsonify({'error': f'Server error: {str(e)}'}), 500
    
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()

# ==========================================
# CHECK SKU AVAILABILITY
# Returns whether a SKU is already in use
# (checks both products.sku and product_variations.variation_sku)
# ==========================================

@product_bp.route('/check_sku', methods=['GET'])
@jwt_required()
def check_sku():
    sku = (request.args.get('sku') or '').strip()
    if not sku:
        return jsonify({'error': 'SKU is required'}), 400

    exclude_product_id = request.args.get('exclude_product_id')

    conn = get_db_connection()
    if conn is None:
        return jsonify({'error': 'Database connection failed'}), 500

    cursor = conn.cursor(dictionary=True)
    try:
        # Check products table
        if exclude_product_id:
            cursor.execute(
                "SELECT id, product_name FROM products WHERE sku = %s AND id != %s LIMIT 1",
                (sku, int(exclude_product_id))
            )
        else:
            cursor.execute(
                "SELECT id, product_name FROM products WHERE sku = %s LIMIT 1",
                (sku,)
            )
        product_match = cursor.fetchone()

        if product_match:
            return jsonify({
                'available': False,
                'message': f"SKU '{sku}' already used by product '{product_match['product_name']}'"
            }), 200

        # Check product_variations table
        if exclude_product_id:
            cursor.execute(
                """SELECT pv.id, p.product_name
                   FROM product_variations pv
                   JOIN products p ON pv.product_id = p.id
                   WHERE pv.variation_sku = %s AND pv.product_id != %s LIMIT 1""",
                (sku, int(exclude_product_id))
            )
        else:
            cursor.execute(
                """SELECT pv.id, p.product_name
                   FROM product_variations pv
                   JOIN products p ON pv.product_id = p.id
                   WHERE pv.variation_sku = %s LIMIT 1""",
                (sku,)
            )
        variation_match = cursor.fetchone()

        if variation_match:
            return jsonify({
                'available': False,
                'message': f"SKU '{sku}' already used by a variation in product '{variation_match['product_name']}'"
            }), 200

        return jsonify({'available': True, 'message': 'SKU is available'}), 200

    except Exception as err:
        print(f"Error checking SKU: {err}")
        return jsonify({'error': 'Failed to check SKU'}), 500
    finally:
        if cursor: cursor.close()
        if conn:   conn.close()


# ==========================================
# ADD PRODUCT ONLY — NO PURCHASE ORDER
# Creates product (+ variations) without
# supplier, warehouse, PO, or stock entries.
# ==========================================

@product_bp.route('/add_product_only', methods=['POST'])
@jwt_required()
@role_required('admin', 'manager')
def add_product_only():
    data = request.get_json()
    if not data:
        return jsonify({'error': 'No data provided'}), 400

    # Extract product fields
    product_name      = (data.get('product_name') or '').strip()
    sku               = (data.get('sku') or '').strip()
    barcode_symbology = data.get('barcode_symbology')
    product_type      = data.get('product_type', 'single')
    brand_id          = data.get('brand_id')
    category_id       = data.get('category_id')
    base_unit_id      = data.get('base_unit_id')
    sale_unit_id      = data.get('sale_unit_id')
    purchase_unit_id  = data.get('purchase_unit_id')
    stock_alert       = safe_int(data.get('stock_alert', 0))
    note              = (data.get('note') or '').strip()
    product_tax       = safe_float(data.get('product_tax', 0.0))
    tax_type          = data.get('tax_type', 'exclusive')
    expiration_date   = data.get('expiration_date') or None
    product_price     = safe_float(data.get('product_price', 0.0))
    variations        = data.get('variations', [])

    # Process images
    product_images_data = data.get('product_image')
    certificate_data    = data.get('certificate_image')

    saved_image_paths = []
    if product_images_data:
        try:
            images_list = json.loads(product_images_data) if isinstance(product_images_data, str) else product_images_data
            if len(images_list) > 10:
                return jsonify({'error': 'Maximum 10 images allowed'}), 400
            for idx, img_base64 in enumerate(images_list, 1):
                if img_base64:
                    file_path = save_base64_file(img_base64, f'product_img_{idx}')
                    if file_path:
                        saved_image_paths.append(file_path)
        except Exception as e:
            print(f"Error processing images: {e}")

    saved_certificate_path = None
    if certificate_data:
        try:
            saved_certificate_path = save_base64_file(certificate_data, 'certificate')
        except Exception as e:
            print(f"Error processing certificate: {e}")

    product_image_json     = json.dumps(saved_image_paths) if saved_image_paths else None
    certificate_image_path = saved_certificate_path

    # Validation
    required_fields = [
        'product_name', 'sku', 'product_type',
        'brand_id', 'category_id', 'base_unit_id',
        'sale_unit_id', 'purchase_unit_id'
    ]
    missing = validate_required_fields(data, required_fields)

    if product_type == 'single':
        if product_price <= 0:
            missing.append('product_price')
    elif product_type == 'variable':
        if not variations or len(variations) == 0:
            missing.append('variations')

    if missing:
        cleanup_uploaded_files(saved_image_paths, saved_certificate_path)
        return jsonify({'error': 'Validation failed', 'missing_fields': list(set(missing))}), 400

    conn = get_db_connection()
    if not conn:
        cleanup_uploaded_files(saved_image_paths, saved_certificate_path)
        return jsonify({'error': 'Database connection failed'}), 500

    cursor = conn.cursor(dictionary=True)
    try:
        conn.start_transaction()

        # Ensure product_cost and product_price columns exist
        for col_name in ('product_cost', 'product_price'):
            cursor.execute(f"SHOW COLUMNS FROM products LIKE '{col_name}'")
            if not cursor.fetchone():
                cursor.execute(f"""
                    ALTER TABLE products
                    ADD COLUMN {col_name} DECIMAL(15,2) DEFAULT 0.00
                    AFTER product_tax
                """)
                print(f"  ✅ {col_name} column added to products table")

        # Validate brand
        if brand_id:
            cursor.execute("SELECT id FROM brands WHERE id = %s", (brand_id,))
            if not cursor.fetchone():
                raise ValueError(f"Brand with ID {brand_id} not found")

        # Validate category
        if category_id:
            cursor.execute("SELECT id FROM categories WHERE id = %s", (category_id,))
            if not cursor.fetchone():
                raise ValueError(f"Category with ID {category_id} not found")

        # Validate base unit
        cursor.execute("SELECT id FROM base_units WHERE id = %s", (base_unit_id,))
        if not cursor.fetchone():
            raise ValueError(f"Base unit with ID {base_unit_id} not found")

        # Validate sale unit
        cursor.execute("SELECT id FROM units WHERE id = %s", (sale_unit_id,))
        if not cursor.fetchone():
            raise ValueError(f"Sale unit with ID {sale_unit_id} not found")

        # Validate purchase unit
        cursor.execute("SELECT id FROM units WHERE id = %s", (purchase_unit_id,))
        if not cursor.fetchone():
            raise ValueError(f"Purchase unit with ID {purchase_unit_id} not found")

        # Check duplicate product SKU
        cursor.execute("SELECT id, product_name FROM products WHERE sku = %s LIMIT 1", (sku,))
        sku_conflict = cursor.fetchone()
        if sku_conflict:
            raise ValueError(f"SKU '{sku}' already exists in product '{sku_conflict['product_name']}'")

        # Also check variation SKUs table for the main SKU
        cursor.execute(
            "SELECT pv.id, p.product_name FROM product_variations pv JOIN products p ON pv.product_id = p.id WHERE pv.variation_sku = %s LIMIT 1",
            (sku,)
        )
        var_sku_conflict = cursor.fetchone()
        if var_sku_conflict:
            raise ValueError(f"SKU '{sku}' already used as a variation SKU in product '{var_sku_conflict['product_name']}'")

        # Check duplicate variation SKUs
        if product_type == 'variable' and variations:
            var_skus = [v.get('variation_sku', '').strip() for v in variations if v.get('variation_sku', '').strip()]

            # Check for duplicates within the submitted variations
            seen = set()
            for vs in var_skus:
                if vs in seen:
                    raise ValueError(f"Duplicate variation SKU '{vs}' in submitted variations")
                seen.add(vs)

            # Check against existing products
            for vs in var_skus:
                cursor.execute("SELECT id, product_name FROM products WHERE sku = %s LIMIT 1", (vs,))
                conflict = cursor.fetchone()
                if conflict:
                    raise ValueError(f"Variation SKU '{vs}' already exists as a product SKU in '{conflict['product_name']}'")

                cursor.execute(
                    "SELECT pv.id, p.product_name FROM product_variations pv JOIN products p ON pv.product_id = p.id WHERE pv.variation_sku = %s LIMIT 1",
                    (vs,)
                )
                conflict = cursor.fetchone()
                if conflict:
                    raise ValueError(f"Variation SKU '{vs}' already exists in product '{conflict['product_name']}'")

        product_cost = safe_float(data.get('product_cost', 0.0))

        # Insert product
        cursor.execute("""
            INSERT INTO products (
                product_name, sku, barcode_symbology, product_type,
                expiration_date, brand_id, category_id,
                base_unit_id, sale_unit_id, purchase_unit_id,
                stock_alert, note, product_tax, tax_type,
                product_cost, product_price,
                product_image, certificate_image, created_at
            )
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW())
        """, (
            product_name, sku, barcode_symbology, product_type,
            expiration_date, brand_id, category_id,
            base_unit_id, sale_unit_id, purchase_unit_id,
            stock_alert, note, product_tax, tax_type,
            product_cost, product_price,
            product_image_json, certificate_image_path
        ))

        product_id = cursor.lastrowid

        # Insert variations if variable type
        if product_type == 'variable' and variations:
            for var in variations:
                cursor.execute("""
                    INSERT INTO product_variations (
                        product_id, variation_name, variation_type, variation_sku,
                        variation_cost, variation_price, variation_tax_type, variation_tax,
                        variation_stock_alert, expiration_date, created_at
                    )
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW())
                """, (
                    product_id,
                    var.get('variation_name'),
                    var.get('variation_type'),
                    var.get('variation_sku'),
                    safe_float(var.get('variation_cost', 0)),
                    safe_float(var.get('variation_price')),
                    var.get('variation_tax_type', 'exclusive'),
                    safe_float(var.get('variation_tax')),
                    safe_int(var.get('variation_stock_alert')),
                    var.get('expiration_date')
                ))

        conn.commit()

        return jsonify({
            'success': True,
            'message': 'Product created successfully!',
            'data': {
                'product_id': product_id,
                'product_name': product_name
            }
        }), 201

    except mysql.connector.IntegrityError as err:
        conn.rollback()
        cleanup_uploaded_files(saved_image_paths, saved_certificate_path)
        error_msg = str(err)
        if 'Duplicate entry' in error_msg:
            if 'sku' in error_msg.lower():
                return jsonify({'error': 'SKU already exists'}), 409
            return jsonify({'error': 'Duplicate entry'}), 409
        elif 'foreign key constraint' in error_msg.lower():
            return jsonify({'error': 'Invalid brand, category or unit ID'}), 400
        return jsonify({'error': f'Database error: {error_msg}'}), 500

    except ValueError as err:
        conn.rollback()
        cleanup_uploaded_files(saved_image_paths, saved_certificate_path)
        return jsonify({'error': str(err)}), 400

    except Exception as e:
        conn.rollback()
        cleanup_uploaded_files(saved_image_paths, saved_certificate_path)
        print(f"Error: {e}")
        traceback.print_exc()
        return jsonify({'error': f'Server error: {str(e)}'}), 500

    finally:
        if cursor: cursor.close()
        if conn:   conn.close()


def convert_datetime_to_string(obj):
    """
    Convert datetime objects to ISO format strings
    Database format: 2026-01-25 22:20:01
    Returns: 2026-01-25T22:20:01
    Frontend will handle formatting to: 2026/01/25 10:20:01 PM
    """
    if isinstance(obj, datetime):
        return obj.isoformat()
    return obj


def process_row_datetimes(row):
    """Convert all datetime fields in a row to ISO strings"""
    if not row:
        return row
    
    processed_row = {}
    for key, value in row.items():
        if isinstance(value, datetime):
            processed_row[key] = value.isoformat()
        elif isinstance(value, Decimal):
            processed_row[key] = float(value)
        else:
            processed_row[key] = value
    return processed_row


# ==========================================
# GET SINGLE PRODUCT WITH EXACT TIMESTAMPS + OUR_PRICE
# ==========================================
@product_bp.route('/get_product/<int:id>', methods=['GET'])
@jwt_required()
@role_required('admin', 'manager')
def get_product(id):
    """
    Fetch complete product details with exact database timestamps
    All datetime fields are returned in ISO format: YYYY-MM-DDTHH:MM:SS
    Frontend handles conversion to display format
    """
    conn = get_db_connection()
    if conn is None:
        return jsonify({'error': 'Database connection failed'}), 500

    cursor = conn.cursor(dictionary=True)
    try:
        # Get product with all related data
        cursor.execute("""
            SELECT 
                p.id,
                p.product_name,
                p.sku,
                p.barcode_symbology,
                p.product_image,
                p.certificate_image,
                p.product_type,
                p.stock_alert,
                p.note,
                p.product_tax,
                p.tax_type,
                p.expiration_date,
                p.created_at,
                p.updated_at,
                b.brand_name,
                b.id AS brand_id,
                c.category_name,
                c.id AS category_id,
                p.base_unit_id,
                bu.base_unit AS base_unit_name,
                u2.id AS sale_unit_id,
                u2.unit_name AS sale_unit_name,
                u3.id AS purchase_unit_id,
                u3.unit_name AS purchase_unit_name
            FROM products p
            LEFT JOIN brands b ON p.brand_id = b.id
            LEFT JOIN categories c ON p.category_id = c.id
            LEFT JOIN base_units bu ON p.base_unit_id = bu.id
            LEFT JOIN units u2 ON p.sale_unit_id = u2.id
            LEFT JOIN units u3 ON p.purchase_unit_id = u3.id
            WHERE p.id = %s
        """, (id,))
        
        product = cursor.fetchone()
        
        if not product:
            return jsonify({'error': 'Product not found'}), 404

        # Convert datetime objects to ISO strings
        product = process_row_datetimes(product)

        # Process product images
        product_images = []
        if product.get('product_image'):
            try:
                image_paths = json.loads(product['product_image']) if isinstance(product['product_image'], str) else product['product_image']
                
                if isinstance(image_paths, list):
                    for img_path in image_paths:
                        if img_path and os.path.exists(img_path):
                            filename = os.path.basename(img_path)
                            image_url = f"/uploads/products/{filename}"
                            product_images.append(image_url)
            except (json.JSONDecodeError, TypeError) as e:
                print(f"❌ Error parsing product images for product {id}: {e}")
        
        product['product_images'] = product_images
        product['image_count'] = len(product_images)
        
        # Process certificate image
        certificate_url = None
        if product.get('certificate_image'):
            cert_path = product['certificate_image']
            if cert_path and os.path.exists(cert_path):
                filename = os.path.basename(cert_path)
                certificate_url = f"/uploads/products/{filename}"
        
        product['certificate_url'] = certificate_url
        product['has_certificate'] = bool(certificate_url)
        
        product.pop('product_image', None)
        product.pop('certificate_image', None)

        # Calculate total stock from warehouse_stock
        cursor.execute("""
            SELECT IFNULL(SUM(quantity), 0) AS total_stock
            FROM warehouse_stock
            WHERE product_id = %s AND variation_id IS NULL
        """, (id,))
        stock_result = cursor.fetchone()
        product['total_stock'] = float(stock_result['total_stock']) if stock_result else 0.0
        
        # Get average cost, price, and our_price from batches
        cursor.execute("""
            SELECT 
                AVG(cost) AS avg_cost,
                AVG(price) AS avg_price,
                AVG(our_price) AS avg_our_price,
                MIN(cost) AS min_cost,
                MAX(cost) AS max_cost,
                MIN(price) AS min_price,
                MAX(price) AS max_price,
                MIN(our_price) AS min_our_price,
                MAX(our_price) AS max_our_price,
                SUM(quantity) AS total_purchased,
                SUM(remaining_quantity) AS total_remaining
            FROM product_batches
            WHERE product_id = %s AND variation_id IS NULL
        """, (id,))
        cost_price_result = cursor.fetchone()
        
        if cost_price_result and cost_price_result['avg_cost']:
            product['avg_cost'] = float(cost_price_result['avg_cost'])
            product['avg_price'] = float(cost_price_result['avg_price'])
            product['avg_our_price'] = float(cost_price_result['avg_our_price']) if cost_price_result['avg_our_price'] else 0.0
            product['min_cost'] = float(cost_price_result['min_cost'])
            product['max_cost'] = float(cost_price_result['max_cost'])
            product['min_price'] = float(cost_price_result['min_price'])
            product['max_price'] = float(cost_price_result['max_price'])
            product['min_our_price'] = float(cost_price_result['min_our_price']) if cost_price_result['min_our_price'] else 0.0
            product['max_our_price'] = float(cost_price_result['max_our_price']) if cost_price_result['max_our_price'] else 0.0
            product['total_purchased'] = float(cost_price_result['total_purchased'])
            product['total_remaining'] = float(cost_price_result['total_remaining'])
            
            # Price display for supplier price
            if product['min_price'] == product['max_price']:
                product['price_display'] = f"{product['min_price']:.2f}"
            else:
                product['price_display'] = f"{product['min_price']:.2f} - {product['max_price']:.2f}"
            
            # Our price display
            if product['min_our_price'] == product['max_our_price']:
                product['our_price_display'] = f"{product['min_our_price']:.2f}"
            else:
                product['our_price_display'] = f"{product['min_our_price']:.2f} - {product['max_our_price']:.2f}"
        else:
            product['avg_cost'] = 0.0
            product['avg_price'] = 0.0
            product['avg_our_price'] = 0.0
            product['min_cost'] = 0.0
            product['max_cost'] = 0.0
            product['min_price'] = 0.0
            product['max_price'] = 0.0
            product['min_our_price'] = 0.0
            product['max_our_price'] = 0.0
            product['total_purchased'] = 0.0
            product['total_remaining'] = 0.0
            product['price_display'] = "0.00"
            product['our_price_display'] = "0.00"
        
        # Get warehouse-wise stock
        cursor.execute("""
            SELECT 
                ws.warehouse_id,
                w.warehouse_name,
                ws.batch_id,
                SUM(ws.quantity) AS stock_quantity
            FROM warehouse_stock ws
            LEFT JOIN warehouses w ON ws.warehouse_id = w.id
            WHERE ws.product_id = %s AND ws.variation_id IS NULL
            GROUP BY ws.warehouse_id, w.warehouse_name, ws.batch_id
        """, (id,))
        warehouse_stock = cursor.fetchall()
        product['warehouse_stock'] = [process_row_datetimes(row) for row in warehouse_stock]
        
        # ✅ Get batch information with EXACT database timestamps + our_price
        cursor.execute("""
            SELECT 
                pb.batch_id,
                pb.batch_number,
                pb.cost,
                pb.price,
                pb.our_price,
                pb.quantity,
                pb.remaining_quantity,
                pb.expiration_date,
                pb.created_on,
                po.order_id,
                po.status AS order_status,
                po.grn_status,
                s.supplier_name,
                pb.grn_id,
                g.grn_code,
                g.status AS grn_status_detail,
                g.grn_date,
                g.created_at AS grn_created_at
            FROM product_batches pb
            LEFT JOIN purchase_orders po ON pb.purchase_order_id = po.order_id
            LEFT JOIN suppliers s ON po.supplier_id = s.id
            LEFT JOIN grn g ON pb.grn_id = g.grn_id
            WHERE pb.product_id = %s 
              AND pb.variation_id IS NULL
              AND (
                  pb.remaining_quantity > 0
                  OR 
                  (
                      pb.remaining_quantity = 0
                      AND NOT EXISTS (
                          SELECT 1 
                          FROM product_batches pb_check
                          WHERE pb_check.product_id = pb.product_id
                            AND pb_check.variation_id IS NULL
                            AND pb_check.cost = pb.cost
                            AND pb_check.price = pb.price
                            AND pb_check.remaining_quantity > 0
                      )
                      AND pb.batch_id = (
                          SELECT pb2.batch_id
                          FROM product_batches pb2
                          WHERE pb2.product_id = pb.product_id
                            AND pb2.variation_id IS NULL
                            AND pb2.cost = pb.cost
                            AND pb2.price = pb.price
                            AND pb2.remaining_quantity = 0
                          ORDER BY pb2.created_on DESC, pb2.batch_id DESC
                          LIMIT 1
                      )
                  )
              )
            ORDER BY pb.created_on DESC, pb.batch_id DESC
        """, (id,))
        batches = cursor.fetchall()
        
        # Process batches with datetime conversion
        processed_batches = []
        for batch in batches:
            processed_batch = process_row_datetimes(batch)
            # Ensure our_price is properly converted
            if processed_batch.get('our_price') is not None:
                processed_batch['our_price'] = float(processed_batch['our_price'])
            else:
                processed_batch['our_price'] = 0.0
            processed_batches.append(processed_batch)
        
        product['batches'] = processed_batches

        # Process variations with EXACT timestamps
        if product['product_type'] == 'variable':
            cursor.execute("""
                SELECT 
                    id AS variation_id,
                    variation_name,
                    variation_type,
                    variation_sku,
                    variation_cost,
                    variation_price,
                    variation_tax_type,
                    variation_tax,
                    variation_stock_alert,
                    expiration_date,
                    created_at,
                    updated_at
                FROM product_variations
                WHERE product_id = %s
                ORDER BY created_at ASC
            """, (id,))
            variations = cursor.fetchall()
            
            processed_variations = []
            for variation in variations:
                processed_var = process_row_datetimes(variation)
                variation_id = processed_var['variation_id']
                
                # Get variation stock
                cursor.execute("""
                    SELECT IFNULL(SUM(quantity), 0) AS total_stock
                    FROM warehouse_stock
                    WHERE product_id = %s AND variation_id = %s
                """, (id, variation_id))
                var_stock_result = cursor.fetchone()
                processed_var['total_stock'] = float(var_stock_result['total_stock']) if var_stock_result else 0.0
                
                # Get warehouse stock
                cursor.execute("""
                    SELECT 
                        ws.warehouse_id,
                        w.warehouse_name,
                        ws.batch_id,
                        SUM(ws.quantity) AS stock_quantity
                    FROM warehouse_stock ws
                    LEFT JOIN warehouses w ON ws.warehouse_id = w.id
                    WHERE ws.product_id = %s AND ws.variation_id = %s
                    GROUP BY ws.warehouse_id, w.warehouse_name, ws.batch_id
                """, (id, variation_id))
                var_warehouse_stock = cursor.fetchall()
                processed_var['warehouse_stock'] = [process_row_datetimes(row) for row in var_warehouse_stock]
                
                # ✅ Get variation batches with EXACT timestamps + our_price
                cursor.execute("""
                    SELECT 
                        pb.batch_id,
                        pb.batch_number,
                        pb.cost,
                        pb.price,
                        pb.our_price,
                        pb.quantity,
                        pb.remaining_quantity,
                        pb.expiration_date,
                        pb.created_on,
                        po.order_id,
                        po.status AS order_status,
                        po.grn_status,
                        s.supplier_name,
                        pb.grn_id,
                        g.grn_code,
                        g.status AS grn_status_detail,
                        g.grn_date,
                        g.created_at AS grn_created_at
                    FROM product_batches pb
                    LEFT JOIN purchase_orders po ON pb.purchase_order_id = po.order_id
                    LEFT JOIN suppliers s ON po.supplier_id = s.id
                    LEFT JOIN grn g ON pb.grn_id = g.grn_id
                    WHERE pb.product_id = %s 
                      AND pb.variation_id = %s
                      AND (
                          pb.remaining_quantity > 0
                          OR 
                          (
                              pb.remaining_quantity = 0
                              AND NOT EXISTS (
                                  SELECT 1 
                                  FROM product_batches pb_check
                                  WHERE pb_check.product_id = pb.product_id
                                    AND pb_check.variation_id = pb.variation_id
                                    AND pb_check.cost = pb.cost
                                    AND pb_check.price = pb.price
                                    AND pb_check.remaining_quantity > 0
                              )
                              AND pb.batch_id = (
                                  SELECT pb2.batch_id
                                  FROM product_batches pb2
                                  WHERE pb2.product_id = pb.product_id
                                    AND pb2.variation_id = pb.variation_id
                                    AND pb2.cost = pb.cost
                                    AND pb2.price = pb.price
                                    AND pb2.remaining_quantity = 0
                                  ORDER BY pb2.created_on DESC, pb2.batch_id DESC
                                  LIMIT 1
                              )
                          )
                      )
                    ORDER BY pb.created_on DESC, pb.batch_id DESC
                """, (id, variation_id))
                var_batches = cursor.fetchall()
                
                # Process variation batches with our_price
                processed_var_batches = []
                for batch in var_batches:
                    processed_batch = process_row_datetimes(batch)
                    if processed_batch.get('our_price') is not None:
                        processed_batch['our_price'] = float(processed_batch['our_price'])
                    else:
                        processed_batch['our_price'] = 0.0
                    processed_var_batches.append(processed_batch)
                
                processed_var['batches'] = processed_var_batches
                
                # Get batch stats including our_price
                cursor.execute("""
                    SELECT 
                        SUM(quantity) AS total_purchased,
                        SUM(remaining_quantity) AS total_remaining,
                        AVG(our_price) AS avg_our_price,
                        MIN(our_price) AS min_our_price,
                        MAX(our_price) AS max_our_price
                    FROM product_batches
                    WHERE product_id = %s AND variation_id = %s
                """, (id, variation_id))
                var_batch_stats = cursor.fetchone()
                processed_var['total_purchased'] = float(var_batch_stats['total_purchased']) if var_batch_stats['total_purchased'] else 0.0
                processed_var['total_remaining'] = float(var_batch_stats['total_remaining']) if var_batch_stats['total_remaining'] else 0.0
                processed_var['avg_our_price'] = float(var_batch_stats['avg_our_price']) if var_batch_stats['avg_our_price'] else 0.0
                processed_var['min_our_price'] = float(var_batch_stats['min_our_price']) if var_batch_stats['min_our_price'] else 0.0
                processed_var['max_our_price'] = float(var_batch_stats['max_our_price']) if var_batch_stats['max_our_price'] else 0.0
                
                processed_variations.append(processed_var)
            
            product['variations'] = processed_variations
            product['total_stock'] = sum(v['total_stock'] for v in processed_variations)
            product['total_variations'] = len(processed_variations)
            
            if processed_variations:
                variation_prices = [float(v['variation_price']) for v in processed_variations if v['variation_price']]
                if variation_prices:
                    min_var_price = min(variation_prices)
                    max_var_price = max(variation_prices)
                    if min_var_price == max_var_price:
                        product['price_display'] = f"{min_var_price:.2f}"
                    else:
                        product['price_display'] = f"{min_var_price:.2f} - {max_var_price:.2f}"
                
                # Add our_price display for variations
                variation_our_prices = [float(v['avg_our_price']) for v in processed_variations if v.get('avg_our_price')]
                if variation_our_prices:
                    min_var_our_price = min(variation_our_prices)
                    max_var_our_price = max(variation_our_prices)
                    if min_var_our_price == max_var_our_price:
                        product['our_price_display'] = f"{min_var_our_price:.2f}"
                    else:
                        product['our_price_display'] = f"{min_var_our_price:.2f} - {max_var_our_price:.2f}"
        else:
            product['variations'] = []
            product['total_variations'] = 0
        
        # Get associated purchase orders with timestamps
        cursor.execute("""
            SELECT DISTINCT
                po.order_id,
                po.status,
                po.grn_status,
                po.grand_total,
                po.created_on,
                s.supplier_name,
                w.warehouse_name
            FROM purchase_orders po
            LEFT JOIN suppliers s ON po.supplier_id = s.id
            LEFT JOIN warehouses w ON po.warehouse_id = w.id
            WHERE po.order_id IN (
                SELECT DISTINCT purchase_order_id 
                FROM product_batches 
                WHERE product_id = %s AND purchase_order_id IS NOT NULL
            )
            ORDER BY po.created_on DESC
        """, (id,))
        purchase_orders = cursor.fetchall()
        product['purchase_orders'] = [process_row_datetimes(row) for row in purchase_orders]

        return jsonify({
            'success': True,
            'data': product,
            'timestamp_format': 'ISO 8601 (YYYY-MM-DDTHH:MM:SS)',
            'note': 'All datetime fields are in database timezone. Frontend should handle display formatting. our_price field included in batches.'
        }), 200

    except mysql.connector.Error as err:
        print(f"❌ Database Error fetching product: {err}")
        traceback.print_exc()
        return jsonify({'error': f'Database error: {str(err)}'}), 500
    
    except Exception as err:
        print(f"❌ Error fetching product: {err}")
        traceback.print_exc()
        return jsonify({'error': 'Failed to fetch product'}), 500

    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()

# ==========================================
# GET ALL GRNs WITH EXACT TIMESTAMPS
# ==========================================
@product_bp.route('/grns', methods=['GET'])
@jwt_required()
@role_required('admin', 'manager')
def get_all_grns():
    """Get all GRNs with exact database timestamps"""
    conn = get_db_connection()
    if not conn:
        return jsonify({'error': 'Database connection failed'}), 500
    
    cursor = conn.cursor(dictionary=True)
    
    try:
        # Build query with filters
        query = """
            SELECT 
                g.grn_id,
                g.grn_code,
                g.grn_date,
                g.invoice_number,
                g.invoice_date,
                g.status,
                g.payment_status,
                g.paid_amount,
                g.due_amount,
                g.grand_total,
                g.total_items,
                g.note,
                g.created_at,
                g.approved_at,
                g.updated_at,
                po.order_id,
                s.supplier_name,
                s.supplier_code,
                w.warehouse_name,
                st.store_name,
                u1.name AS received_by_name,
                u2.name AS created_by_name,
                u3.name AS approved_by_name
            FROM grn g
            LEFT JOIN purchase_orders po ON g.purchase_order_id = po.order_id
            LEFT JOIN suppliers s ON g.supplier_id = s.id
            LEFT JOIN warehouses w ON g.warehouse_id = w.id
            LEFT JOIN stores st ON g.store_id = st.id
            LEFT JOIN users u1 ON g.received_by = u1.id
            LEFT JOIN users u2 ON g.created_by = u2.id
            LEFT JOIN users u3 ON g.approved_by = u3.id
            ORDER BY g.created_at DESC
        """
        
        cursor.execute(query)
        grns = cursor.fetchall()
        
        # Process all GRNs with datetime conversion
        processed_grns = [process_row_datetimes(grn) for grn in grns]
        
        return jsonify({
            'success': True,
            'data': processed_grns,
            'total': len(processed_grns),
            'timestamp_format': 'ISO 8601 (YYYY-MM-DDTHH:MM:SS)'
        }), 200
    
    except mysql.connector.Error as err:
        print(f"❌ Database Error: {err}")
        traceback.print_exc()
        return jsonify({'error': f'Database error: {str(err)}'}), 500
    
    except Exception as err:
        print(f"❌ Error: {err}")
        traceback.print_exc()
        return jsonify({'error': 'Failed to fetch GRNs'}), 500
    
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


# ==========================================
# GET SINGLE GRN WITH EXACT TIMESTAMPS
# ==========================================
@product_bp.route('/grn/<int:grn_id>', methods=['GET'])
@jwt_required()
@role_required('admin', 'manager')
def get_grn_details(grn_id):
    """Get GRN details with exact database timestamps"""
    conn = get_db_connection()
    if not conn:
        return jsonify({'error': 'Database connection failed'}), 500
    
    cursor = conn.cursor(dictionary=True)
    
    try:
        # Get GRN header
        cursor.execute("""
            SELECT 
                g.*,
                po.order_id,
                po.status AS po_status,
                s.supplier_name,
                s.supplier_code,
                w.warehouse_name,
                st.store_name,
                u1.name AS received_by_name,
                u2.name AS created_by_name,
                u3.name AS approved_by_name
            FROM grn g
            LEFT JOIN purchase_orders po ON g.purchase_order_id = po.order_id
            LEFT JOIN suppliers s ON g.supplier_id = s.id
            LEFT JOIN warehouses w ON g.warehouse_id = w.id
            LEFT JOIN stores st ON g.store_id = st.id
            LEFT JOIN users u1 ON g.received_by = u1.id
            LEFT JOIN users u2 ON g.created_by = u2.id
            LEFT JOIN users u3 ON g.approved_by = u3.id
            WHERE g.grn_id = %s
        """, (grn_id,))
        
        grn = cursor.fetchone()
        
        if not grn:
            return jsonify({'error': 'GRN not found'}), 404
        
        # Process GRN header with datetime conversion
        grn = process_row_datetimes(grn)
        
        # Get GRN items
        cursor.execute("""
            SELECT 
                gi.*,
                p.product_name,
                p.sku,
                pv.variation_name,
                pv.variation_type,
                pb.batch_number
            FROM grn_items gi
            LEFT JOIN products p ON gi.product_id = p.id
            LEFT JOIN product_variations pv ON gi.variation_id = pv.id
            LEFT JOIN product_batches pb ON gi.batch_id = pb.batch_id
            WHERE gi.grn_id = %s
            ORDER BY gi.grn_item_id
        """, (grn_id,))
        
        grn_items = cursor.fetchall()
        grn['items'] = [process_row_datetimes(item) for item in grn_items]
        
        return jsonify({
            'success': True,
            'data': grn,
            'timestamp_format': 'ISO 8601 (YYYY-MM-DDTHH:MM:SS)'
        }), 200
    
    except mysql.connector.Error as err:
        print(f"❌ Database Error: {err}")
        traceback.print_exc()
        return jsonify({'error': f'Database error: {str(err)}'}), 500
    
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()
            
# @product_bp.route('/get_product/<int:id>', methods=['GET'])
# @jwt_required()
# @role_required('admin', 'manager')
# def get_product(id):
#     """
#     ✅ FIXED: Fetch complete product details
#     - Correctly joins base_units table
#     """
#     conn = get_db_connection()
#     if conn is None:
#         return jsonify({'error': 'Database connection failed'}), 500

#     cursor = conn.cursor(dictionary=True)
#     try:
#         # ✅ FIXED: Fetch product with correct base_units JOIN
#         cursor.execute("""
#             SELECT 
#                 p.id,
#                 p.product_name,
#                 p.sku,
#                 p.barcode_symbology,
#                 p.product_image,
#                 p.certificate_image,
#                 p.product_type,
#                 p.stock_alert,
#                 p.note,
#                 p.product_tax,
#                 p.tax_type,
#                 p.expiration_date,
#                 p.created_at,
#                 p.updated_at,
                
#                 b.brand_name,
#                 b.id AS brand_id,
                
#                 c.category_name,
#                 c.id AS category_id,
                
#                 -- ✅ FIXED: Get base_unit directly from base_units table
#                 p.base_unit_id AS base_unit_id,
#                 bu.base_unit AS base_unit_name,
                
#                 -- Sale unit (from units table)
#                 u2.id AS sale_unit_id,
#                 u2.unit_name AS sale_unit_name,
                
#                 -- Purchase unit (from units table)
#                 u3.id AS purchase_unit_id,
#                 u3.unit_name AS purchase_unit_name
                
#             FROM products p
#             LEFT JOIN brands b ON p.brand_id = b.id
#             LEFT JOIN categories c ON p.category_id = c.id
            
#             -- ✅ FIXED: Direct join to base_units table
#             LEFT JOIN base_units bu ON p.base_unit_id = bu.id
            
#             -- Sale and Purchase units from units table
#             LEFT JOIN units u2 ON p.sale_unit_id = u2.id
#             LEFT JOIN units u3 ON p.purchase_unit_id = u3.id
            
#             WHERE p.id = %s
#         """, (id,))
#         product = cursor.fetchone()

#         if not product:
#             return jsonify({'error': 'Product not found'}), 404

#         # Process product images
#         product_images = []
#         if product.get('product_image'):
#             try:
#                 image_paths = json.loads(product['product_image']) if isinstance(product['product_image'], str) else product['product_image']
                
#                 if isinstance(image_paths, list):
#                     for img_path in image_paths:
#                         if img_path and os.path.exists(img_path):
#                             filename = os.path.basename(img_path)
#                             image_url = f"/uploads/products/{filename}"
#                             product_images.append(image_url)
#             except (json.JSONDecodeError, TypeError) as e:
#                 print(f"❌ Error parsing product images for product {id}: {e}")
        
#         product['product_images'] = product_images
#         product['image_count'] = len(product_images)
        
#         # Process certificate image
#         certificate_url = None
#         if product.get('certificate_image'):
#             cert_path = product['certificate_image']
#             if cert_path and os.path.exists(cert_path):
#                 filename = os.path.basename(cert_path)
#                 certificate_url = f"/uploads/products/{filename}"
        
#         product['certificate_url'] = certificate_url
#         product['has_certificate'] = bool(certificate_url)
        
#         product.pop('product_image', None)
#         product.pop('certificate_image', None)

#         # ... rest of the code (stock, batches, variations) remains the same ...
#         # Calculate total stock from warehouse_stock
#         cursor.execute("""
#             SELECT IFNULL(SUM(quantity), 0) AS total_stock
#             FROM warehouse_stock
#             WHERE product_id = %s AND variation_id IS NULL
#         """, (id,))
#         stock_result = cursor.fetchone()
#         product['total_stock'] = float(stock_result['total_stock']) if stock_result else 0.0
        
#         # Get average cost and price from batches
#         cursor.execute("""
#             SELECT 
#                 AVG(cost) AS avg_cost,
#                 AVG(price) AS avg_price,
#                 MIN(cost) AS min_cost,
#                 MAX(cost) AS max_cost,
#                 MIN(price) AS min_price,
#                 MAX(price) AS max_price,
#                 SUM(quantity) AS total_purchased,
#                 SUM(remaining_quantity) AS total_remaining
#             FROM product_batches
#             WHERE product_id = %s AND variation_id IS NULL
#         """, (id,))
#         cost_price_result = cursor.fetchone()
        
#         if cost_price_result and cost_price_result['avg_cost']:
#             product['avg_cost'] = float(cost_price_result['avg_cost'])
#             product['avg_price'] = float(cost_price_result['avg_price'])
#             product['min_cost'] = float(cost_price_result['min_cost'])
#             product['max_cost'] = float(cost_price_result['max_cost'])
#             product['min_price'] = float(cost_price_result['min_price'])
#             product['max_price'] = float(cost_price_result['max_price'])
#             product['total_purchased'] = float(cost_price_result['total_purchased'])
#             product['total_remaining'] = float(cost_price_result['total_remaining'])
            
#             if product['min_price'] == product['max_price']:
#                 product['price_display'] = f"{product['min_price']:.2f}"
#             else:
#                 product['price_display'] = f"{product['min_price']:.2f} - {product['max_price']:.2f}"
#         else:
#             product['avg_cost'] = 0.0
#             product['avg_price'] = 0.0
#             product['min_cost'] = 0.0
#             product['max_cost'] = 0.0
#             product['min_price'] = 0.0
#             product['max_price'] = 0.0
#             product['total_purchased'] = 0.0
#             product['total_remaining'] = 0.0
#             product['price_display'] = "0.00"
        
#         # (Include all other queries for warehouse_stock, batches, variations, etc.)
#         # ... [Keep the rest of your existing code] ...

#         return jsonify({
#             'success': True,
#             'data': product
#         }), 200

#     except mysql.connector.Error as err:
#         print(f"Database Error fetching product: {err}")
#         traceback.print_exc()
#         return jsonify({'error': f'Database error: {str(err)}'}), 500
    
#     except Exception as err:
#         print(f"Error fetching product: {err}")
#         traceback.print_exc()
#         return jsonify({'error': 'Failed to fetch product'}), 500

#     finally:
#         if cursor:
#             cursor.close()
#         if conn:
#             conn.close()

# ============================================
# ✅ CORRECTED: EXPIRED PRODUCTS ENDPOINTS
# ============================================


# ──────────────────────────────────────────────────────────────────────────────
#  HELPER
# ──────────────────────────────────────────────────────────────────────────────
 
def _serialize_row(row: dict, today) -> dict:
    """
    Convert a raw DB row into a clean, JSON-safe product dict.
    Calculates days_until_expiry and assigns a fine-grained status
    (expired / critical / warning) that the frontend can use directly.
    """
    exp_date = row.get("expiration_date")
 
    # Days until expiry  (negative = already expired)
    if exp_date:
        delta = (exp_date - today).days
    else:
        delta = None
 
    # Fine-grained status
    if delta is None:
        status = "unknown"
    elif delta < 0:
        status = "expired"
    elif delta <= 7:
        status = "critical"
    else:
        status = "warning"
 
    # Prefer variation fields when product is variable
    variation_id   = row.get("variation_id")
    display_sku    = row["variation_sku"]  if variation_id else row["sku"]
    cost           = float(row["variation_cost"] or row["cost"] or 0)
    price          = float(row["variation_price"] or row["price"] or 0)
    our_price      = float(row["our_price"] or 0)
 
    # Human-readable product name
    vname = row.get("variation_name") or ""
    vtype = row.get("variation_type") or ""
    if vname and vtype:
        display_name = f"{row['product_name']} - {vname} ({vtype})"
    elif vname:
        display_name = f"{row['product_name']} - {vname}"
    else:
        display_name = row["product_name"]
 
    remaining_qty = float(row["remaining_quantity"] or 0)
    subtotal      = remaining_qty * cost
 
    return {
        # ── batch ──────────────────────────────────────────────────────────
        "batch_id":              row["batch_id"],
        "batch_number":          row["batch_number"],
        "quantity":              float(row["quantity"] or 0),
        "remaining_quantity":    remaining_qty,
        "cost":                  cost,
        "price":                 price,
        "our_price":             our_price,
        "expiration_date":       exp_date.isoformat() if exp_date else None,
        "purchase_order_id":     row["purchase_order_id"],
 
        # ── product ────────────────────────────────────────────────────────
        "product_id":            row["product_id"],
        "product_name":          row["product_name"],
        "product_display_name":  display_name,
        "sku":                   display_sku,
        "product_type":          row["product_type"],
 
        # ── variation ──────────────────────────────────────────────────────
        "variation_id":          variation_id,
        "variation_name":        vname or None,
        "variation_type":        vtype or None,
        "variation_sku":         row.get("variation_sku"),
        "variation_cost":        float(row["variation_cost"]) if row.get("variation_cost") is not None else None,
        "variation_price":       float(row["variation_price"]) if row.get("variation_price") is not None else None,
 
        # ── unit ───────────────────────────────────────────────────────────
        "sale_unit":             row.get("sale_unit") or "",
        "sale_unit_name":        row.get("sale_unit_name") or "",
 
        # ── taxonomy ───────────────────────────────────────────────────────
        "brand_name":            row.get("brand_name"),
        "category_name":         row.get("category_name"),
 
        # ── purchase / GRN ─────────────────────────────────────────────────
        "grn_id":                row.get("grn_id"),
        "grn_code":              row.get("grn_code"),
        "invoice_number":        row.get("invoice_number"),
        "purchase_status":       row.get("purchase_status"),
        "supplier_name":         row.get("supplier_name"),
 
        # ── location ───────────────────────────────────────────────────────
        "warehouse_name":        row.get("warehouse_name"),
        "store_name":            row.get("store_name"),
 
        # ── computed ───────────────────────────────────────────────────────
        "subtotal":              round(subtotal, 2),
        "days_until_expiry":     delta,          # None if no expiry date
        "status":                status,          # expired | critical | warning
    }
 
 
# ──────────────────────────────────────────────────────────────────────────────
#  GET /expired_products
# ──────────────────────────────────────────────────────────────────────────────
 
@product_bp.route('/expired_products', methods=['GET'])
@jwt_required()
@role_required('admin', 'manager')
def get_expired_products():
    """
    Return all batches that are already expired OR expiring within 30 days,
    with remaining stock > 0.
 
    Each item carries a fine-grained status:
        expired   – expiration_date < today
        critical  – 0 … 7 days remaining
        warning   – 8 … 30 days remaining
 
    Duplicate-row problem is solved by collapsing warehouse_stock to a single
    location row per batch (via a correlated sub-select), so one batch always
    produces exactly one result row.
 
    Query params:
        days  (int, default 30)  – look-ahead window in days
    """
    try:
        days_ahead = int(request.args.get("days", 30))
        if not (1 <= days_ahead <= 365):
            days_ahead = 30
    except (ValueError, TypeError):
        days_ahead = 30
 
    conn   = None
    cursor = None
 
    try:
        conn   = get_db_connection()
        cursor = conn.cursor(dictionary=True)
 
        today             = datetime.now().date()
        expiring_threshold = today + timedelta(days=days_ahead)
 
        # ── Main query ────────────────────────────────────────────────────
        # Key fix: warehouse_stock is collapsed to ONE row per batch using a
        # correlated sub-select, eliminating the duplicate-row bug.
        query = """
            SELECT
                pb.batch_id,
                pb.batch_number,
                pb.quantity,
                pb.remaining_quantity,
                pb.cost,
                pb.price,
                pb.our_price,
                pb.expiration_date,
                pb.purchase_order_id,
 
                -- Product
                p.id            AS product_id,
                p.product_name,
                p.sku,
                p.product_type,
 
                -- Variation (NULL for single products)
                pv.id           AS variation_id,
                pv.variation_name,
                pv.variation_type,
                pv.variation_sku,
                pv.variation_cost,
                pv.variation_price,
 
                -- Taxonomy
                b.brand_name,
                c.category_name,
 
                -- Unit
                u.unit_name     AS sale_unit_name,
                u.unit_short    AS sale_unit,
 
                -- GRN
                g.grn_id,
                g.grn_code,
                g.invoice_number,
 
                -- Purchase Order
                po.order_id,
                po.status       AS purchase_status,
 
                -- Supplier (via purchase order, falls back to GRN supplier)
                COALESCE(s_po.supplier_name, s_grn.supplier_name) AS supplier_name,
 
                -- Location: one warehouse/store per batch (avoids duplicate rows)
                (
                    SELECT w2.warehouse_name
                    FROM   warehouse_stock ws2
                    JOIN   warehouses w2 ON ws2.warehouse_id = w2.id
                    WHERE  ws2.batch_id = pb.batch_id
                    LIMIT  1
                ) AS warehouse_name,
                (
                    SELECT st2.store_name
                    FROM   warehouse_stock ws2
                    JOIN   stores st2 ON ws2.store_id = st2.id
                    WHERE  ws2.batch_id = pb.batch_id
                    LIMIT  1
                ) AS store_name
 
            FROM  product_batches pb
 
            -- Required joins
            INNER JOIN products           p      ON pb.product_id    = p.id
 
            -- Optional joins
            LEFT  JOIN product_variations pv     ON pb.variation_id  = pv.id
            LEFT  JOIN brands             b      ON p.brand_id       = b.id
            LEFT  JOIN categories         c      ON p.category_id    = c.id
            LEFT  JOIN units              u      ON p.sale_unit_id   = u.id
            LEFT  JOIN grn                g      ON pb.grn_id        = g.grn_id
            LEFT  JOIN purchase_orders    po     ON pb.purchase_order_id = po.order_id
            LEFT  JOIN suppliers          s_po   ON po.supplier_id   = s_po.id
            LEFT  JOIN suppliers          s_grn  ON g.supplier_id    = s_grn.id
 
            WHERE
                pb.expiration_date IS NOT NULL
                AND pb.remaining_quantity > 0
                AND pb.expiration_date <= %s
 
            ORDER BY
                pb.expiration_date  ASC,
                p.product_name      ASC
        """
 
        cursor.execute(query, (expiring_threshold,))
        rows = cursor.fetchall()
 
        # ── Categorise rows ───────────────────────────────────────────────
        expired       = []
        critical      = []
        warning       = []
 
        categories_set = set()
        suppliers_set  = set()
 
        for row in rows:
            item = _serialize_row(row, today)
 
            if item["category_name"]:
                categories_set.add(item["category_name"])
            if item["supplier_name"]:
                suppliers_set.add(item["supplier_name"])
 
            if item["status"] == "expired":
                expired.append(item)
            elif item["status"] == "critical":
                critical.append(item)
            else:
                warning.append(item)
 
        # ── Summary ───────────────────────────────────────────────────────
        def _totals(lst):
            return {
                "count": len(lst),
                "value": round(sum(i["subtotal"] for i in lst), 2),
            }
 
        all_items = expired + critical + warning
        summary = {
            "expired":       _totals(expired),
            "critical":      _totals(critical),
            "warning":       _totals(warning),
            "total_items":   len(all_items),
            "total_value":   round(sum(i["subtotal"] for i in all_items), 2),
            "days_window":   days_ahead,
        }
 
        return jsonify({
            "success":      True,
            "expired":      expired,
            "critical":     critical,
            "warning":      warning,
            # kept for backwards-compat with old frontend that reads expiring_soon
            "expiring_soon": critical + warning,
            "summary":      summary,
            "filters": {
                "categories": sorted(categories_set),
                "suppliers":  sorted(suppliers_set),
            },
            "generated_at": datetime.now().isoformat(),
        }), 200
 
    except Exception as exc:
        import traceback
        traceback.print_exc()
        return jsonify({
            "success": False,
            "error":   "Failed to fetch expired products",
            "message": str(exc),
        }), 500
 
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()
 
 
# ──────────────────────────────────────────────────────────────────────────────
#  GET /expired_products/summary
# ──────────────────────────────────────────────────────────────────────────────
 
@product_bp.route('/expired_products/summary', methods=['GET'])
@jwt_required()
@role_required('admin', 'manager')
def get_expired_products_summary():
    """
    Lightweight summary of expired / critical / warning counts and values.
    No product list is returned – ideal for dashboard widgets.
    """
    conn   = None
    cursor = None
 
    try:
        conn   = get_db_connection()
        cursor = conn.cursor(dictionary=True)
 
        query = """
            SELECT
                COUNT(DISTINCT pb.batch_id)                          AS total_batches,
                COUNT(DISTINCT pb.product_id)                        AS total_products,
                SUM(pb.remaining_quantity)                           AS total_quantity,
                SUM(pb.remaining_quantity * COALESCE(pv.variation_cost, pb.cost))
                                                                     AS total_value,
 
                -- Expired
                SUM(CASE WHEN pb.expiration_date < CURDATE()
                         THEN 1 ELSE 0 END)                          AS expired_count,
                SUM(CASE WHEN pb.expiration_date < CURDATE()
                         THEN pb.remaining_quantity * COALESCE(pv.variation_cost, pb.cost)
                         ELSE 0 END)                                 AS expired_value,
 
                -- Critical  (today … +7 days)
                SUM(CASE WHEN pb.expiration_date >= CURDATE()
                              AND pb.expiration_date <= DATE_ADD(CURDATE(), INTERVAL 7 DAY)
                         THEN 1 ELSE 0 END)                          AS critical_count,
                SUM(CASE WHEN pb.expiration_date >= CURDATE()
                              AND pb.expiration_date <= DATE_ADD(CURDATE(), INTERVAL 7 DAY)
                         THEN pb.remaining_quantity * COALESCE(pv.variation_cost, pb.cost)
                         ELSE 0 END)                                 AS critical_value,
 
                -- Warning  (+8 … +30 days)
                SUM(CASE WHEN pb.expiration_date > DATE_ADD(CURDATE(), INTERVAL 7 DAY)
                              AND pb.expiration_date <= DATE_ADD(CURDATE(), INTERVAL 30 DAY)
                         THEN 1 ELSE 0 END)                          AS warning_count,
                SUM(CASE WHEN pb.expiration_date > DATE_ADD(CURDATE(), INTERVAL 7 DAY)
                              AND pb.expiration_date <= DATE_ADD(CURDATE(), INTERVAL 30 DAY)
                         THEN pb.remaining_quantity * COALESCE(pv.variation_cost, pb.cost)
                         ELSE 0 END)                                 AS warning_value
 
            FROM  product_batches pb
            LEFT  JOIN product_variations pv ON pb.variation_id = pv.id
 
            WHERE pb.expiration_date IS NOT NULL
              AND pb.remaining_quantity > 0
        """
 
        cursor.execute(query)
        row = cursor.fetchone()
 
        def _f(v):
            return round(float(v), 2) if v is not None else 0.0
 
        return jsonify({
            "success": True,
            "summary": {
                "total_batches":   row["total_batches"]  or 0,
                "total_products":  row["total_products"] or 0,
                "total_quantity":  _f(row["total_quantity"]),
                "total_value":     _f(row["total_value"]),
                "expired": {
                    "count": row["expired_count"]  or 0,
                    "value": _f(row["expired_value"]),
                },
                "critical": {
                    "count": row["critical_count"]  or 0,
                    "value": _f(row["critical_value"]),
                },
                "warning": {
                    "count": row["warning_count"]  or 0,
                    "value": _f(row["warning_value"]),
                },
            },
        }), 200
 
    except Exception as exc:
        import traceback
        traceback.print_exc()
        return jsonify({
            "success": False,
            "error":   "Failed to fetch summary",
            "message": str(exc),
        }), 500
 
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()
 
 
# ──────────────────────────────────────────────────────────────────────────────
#  GET /expired_products/by_category
# ──────────────────────────────────────────────────────────────────────────────
 
@product_bp.route('/expired_products/by_category', methods=['GET'])
@jwt_required()
@role_required('admin', 'manager')
def get_expired_by_category():
    """
    Expired / expiring-soon batches grouped by category,
    ordered by total value descending.
    """
    conn   = None
    cursor = None
 
    try:
        conn   = get_db_connection()
        cursor = conn.cursor(dictionary=True)
 
        query = """
            SELECT
                COALESCE(c.category_name, 'Uncategorized') AS category_name,
 
                COUNT(DISTINCT pb.batch_id)                 AS batch_count,
                COUNT(DISTINCT pb.product_id)               AS product_count,
                SUM(pb.remaining_quantity)                  AS total_quantity,
                SUM(pb.remaining_quantity * COALESCE(pv.variation_cost, pb.cost))
                                                            AS total_value,
 
                SUM(CASE WHEN pb.expiration_date < CURDATE()
                         THEN 1 ELSE 0 END)                 AS expired_count,
                SUM(CASE WHEN pb.expiration_date >= CURDATE()
                              AND pb.expiration_date <= DATE_ADD(CURDATE(), INTERVAL 7 DAY)
                         THEN 1 ELSE 0 END)                 AS critical_count,
                SUM(CASE WHEN pb.expiration_date > DATE_ADD(CURDATE(), INTERVAL 7 DAY)
                              AND pb.expiration_date <= DATE_ADD(CURDATE(), INTERVAL 30 DAY)
                         THEN 1 ELSE 0 END)                 AS warning_count
 
            FROM  product_batches pb
            INNER JOIN products           p  ON pb.product_id   = p.id
            LEFT  JOIN product_variations pv ON pb.variation_id = pv.id
            LEFT  JOIN categories         c  ON p.category_id   = c.id
 
            WHERE pb.expiration_date IS NOT NULL
              AND pb.remaining_quantity > 0
              AND pb.expiration_date <= DATE_ADD(CURDATE(), INTERVAL 30 DAY)
 
            GROUP BY c.category_name
            ORDER BY total_value DESC
        """
 
        cursor.execute(query)
        rows = cursor.fetchall()
 
        def _f(v):
            return round(float(v), 2) if v is not None else 0.0
 
        categories = [
            {
                "category_name":   row["category_name"],
                "batch_count":     row["batch_count"]   or 0,
                "product_count":   row["product_count"] or 0,
                "total_quantity":  _f(row["total_quantity"]),
                "total_value":     _f(row["total_value"]),
                "expired_count":   row["expired_count"]  or 0,
                "critical_count":  row["critical_count"] or 0,
                "warning_count":   row["warning_count"]  or 0,
            }
            for row in rows
        ]
 
        return jsonify({
            "success":    True,
            "categories": categories,
        }), 200
 
    except Exception as exc:
        import traceback
        traceback.print_exc()
        return jsonify({
            "success": False,
            "error":   "Failed to fetch category data",
            "message": str(exc),
        }), 500
 
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()

# ==========================================
# ✅ UPDATE PRODUCT WITH IMAGE OPTIMIZATION + OUR_PRICE
# ==========================================

@product_bp.route('/update_product/<int:id>', methods=['PUT'])
@jwt_required()
@role_required('admin')
def update_product(id):
    """
    Complete update endpoint for products with full batch and image support.
    ✨ NEW: Automatic image optimization during updates
    ✨ NEW: Support for our_price field in batches
    
    Handles:
    - Single products: updates product table and/or specific batch (including our_price)
    - Variable products: updates product table, variations, and/or specific variation batch (including our_price)
    - Image updates: product images and certificate images (with automatic optimization)
    """
    data = request.get_json()

    if not data:
        return jsonify({'error': 'No data provided'}), 400

    conn = get_db_connection()
    if conn is None:
        return jsonify({'error': 'Database connection failed'}), 500

    cursor = conn.cursor(dictionary=True)

    try:
        # Get product info
        cursor.execute("""
            SELECT product_type, product_image, certificate_image 
            FROM products 
            WHERE id = %s
        """, (id,))
        product_info = cursor.fetchone()
        
        if not product_info:
            return jsonify({'error': 'Product not found'}), 404

        product_type = product_info['product_type']
        updated = False

        # Track files for cleanup on error
        new_image_paths = []
        new_certificate_path = None
        old_image_paths = []
        old_certificate_path = None

        # =================================================================
        # ✅ HANDLE IMAGE UPDATES WITH OPTIMIZATION
        # =================================================================
        
        # Process new product images
        if 'product_image' in data:
            # Check if it's explicitly null/empty (delete images) or has content
            if data['product_image'] is None or data['product_image'] == '':
                # Delete all images
                print("\n🗑️ Deleting all product images...")
                if product_info['product_image']:
                    try:
                        old_images = json.loads(product_info['product_image']) if isinstance(product_info['product_image'], str) else product_info['product_image']
                        if isinstance(old_images, list):
                            old_image_paths = old_images
                    except:
                        pass
                
                cursor.execute("""
                    UPDATE products 
                    SET product_image = NULL 
                    WHERE id = %s
                """, (id,))
                updated = True
                print("✅ All product images deleted")
                
            else:
                # Update images
                print("\n📸 Processing product images for update...")
                try:
                    product_images_data = data['product_image']
                    
                    # Parse incoming images
                    images_list = json.loads(product_images_data) if isinstance(product_images_data, str) else product_images_data
                    
                    if not isinstance(images_list, list):
                        images_list = [images_list]
                    
                    if len(images_list) > 10:
                        return jsonify({'error': 'Maximum 10 images allowed'}), 400
                    
                    # Save new images and keep existing ones
                    saved_image_paths = []
                    for idx, img_base64 in enumerate(images_list, 1):
                        if img_base64:
                            # Check if it's a base64 image or existing URL
                            if 'base64,' in str(img_base64):
                                # ✨ NEW IMAGE - OPTIMIZE AND SAVE IT
                                print(f"  🖼️  New image {idx} - optimizing...")
                                file_path = save_base64_file(img_base64, f'product_img_{idx}')
                                if file_path:
                                    saved_image_paths.append(file_path)
                                    new_image_paths.append(file_path)
                                    print(f"  ✅ Image {idx} optimized and saved")
                            else:
                                # Existing image URL - keep it
                                # Extract the file path from URL
                                if img_base64.startswith('/uploads/products/'):
                                    filename = img_base64.split('/uploads/products/')[-1]
                                    file_path = os.path.join(UPLOAD_FOLDER, filename)
                                    if os.path.exists(file_path):
                                        saved_image_paths.append(file_path)
                                        print(f"  ℹ️  Keeping existing image {idx}")
                    
                    if saved_image_paths:
                        # Store old images for cleanup (only ones not being kept)
                        if product_info['product_image']:
                            try:
                                old_images = json.loads(product_info['product_image']) if isinstance(product_info['product_image'], str) else product_info['product_image']
                                if isinstance(old_images, list):
                                    # Only mark for deletion if not in new list
                                    for old_img in old_images:
                                        if old_img not in saved_image_paths:
                                            old_image_paths.append(old_img)
                            except:
                                pass
                        
                        # Update database with new images
                        product_image_json = json.dumps(saved_image_paths)
                        cursor.execute("""
                            UPDATE products 
                            SET product_image = %s 
                            WHERE id = %s
                        """, (product_image_json, id))
                        updated = True
                        print(f"✅ Updated product images: {len(saved_image_paths)} total, {len(new_image_paths)} new (optimized)")
                    
                except Exception as e:
                    print(f"❌ Error processing product images: {e}")
                    traceback.print_exc()
                    # Don't fail the entire update, just log the error
        # else: If 'product_image' not in data at all, keep existing images unchanged
        
        # Process certificate image
        if 'certificate_image' in data:
            # Check if it's explicitly null/empty (delete) or has content
            if data['certificate_image'] is None or data['certificate_image'] == '':
                # Delete certificate
                print("\n🗑️ Deleting certificate...")
                if product_info['certificate_image']:
                    old_certificate_path = product_info['certificate_image']
                
                cursor.execute("""
                    UPDATE products 
                    SET certificate_image = NULL 
                    WHERE id = %s
                """, (id,))
                updated = True
                print("✅ Certificate deleted")
                
            else:
                # Update certificate
                print("\n📄 Processing certificate...")
                try:
                    certificate_data = data['certificate_image']
                    
                    # Check if it's a base64 image or existing URL
                    if 'base64,' in str(certificate_data):
                        # ✨ NEW CERTIFICATE - SAVE IT (PDFs are not optimized)
                        print("  📄 New certificate - saving...")
                        saved_certificate_path = save_base64_file(certificate_data, 'certificate')
                        if saved_certificate_path:
                            new_certificate_path = saved_certificate_path
                            
                            # Store old certificate for cleanup
                            if product_info['certificate_image']:
                                old_certificate_path = product_info['certificate_image']
                            
                            # Update database
                            cursor.execute("""
                                UPDATE products 
                                SET certificate_image = %s 
                                WHERE id = %s
                            """, (saved_certificate_path, id))
                            updated = True
                            print(f"✅ Certificate updated")
                    elif certificate_data.startswith('/uploads/products/'):
                        # Existing certificate URL - no update needed
                        print("ℹ️ Keeping existing certificate")
                        
                except Exception as e:
                    print(f"❌ Error processing certificate: {e}")
                    traceback.print_exc()
        # else: If 'certificate_image' not in data at all, keep existing certificate unchanged

        # =================================================================
        # PRODUCTS TABLE - Common fields for both single and variable
        # =================================================================
        prod_mapping = {
            'product_name': 'product_name',
            'sku': 'sku',
            'barcode_symbology': 'barcode_symbology',
            'expiration_date': 'expiration_date',
            'brand_id': 'brand_id',
            'category_id': 'category_id',
            'base_unit_id': 'base_unit_id',
            'sale_unit_id': 'sale_unit_id',
            'purchase_unit_id': 'purchase_unit_id',
            'stock_alert': 'stock_alert',
            'note': 'note',
            'product_tax': 'product_tax',
            'tax_type': 'tax_type'
        }

        prod_fields = []
        prod_values = []
        for key, column in prod_mapping.items():
            if key in data:
                prod_fields.append(f"{column} = %s")
                prod_values.append(data[key])

        if prod_fields:
            prod_values.append(id)
            sql_prod = f"UPDATE products SET {', '.join(prod_fields)} WHERE id = %s"
            cursor.execute(sql_prod, prod_values)
            updated = True
            print(f"✅ Updated products table for product {id}")

        # =================================================================
        # SINGLE PRODUCT
        # =================================================================
        if product_type == 'single':
            
            # Check if batch_id is provided (editing specific batch)
            if 'batch_id' in data and data['batch_id']:
                batch_id = data['batch_id']
                
                # Verify batch belongs to this product
                cursor.execute("""
                    SELECT batch_id, product_id, variation_id 
                    FROM product_batches 
                    WHERE batch_id = %s AND product_id = %s AND variation_id IS NULL
                """, (batch_id, id))
                
                batch_check = cursor.fetchone()
                
                if not batch_check:
                    return jsonify({
                        'error': 'Batch not found or does not belong to this product'
                    }), 404
                
                # ✅ Update batch-specific fields including our_price
                batch_mapping = {
                    'product_cost': 'cost',
                    'product_price': 'price',
                    'our_price': 'our_price',
                    'batch_number': 'batch_number',
                    'expiration_date': 'expiration_date'
                }
                
                batch_fields = []
                batch_values = []
                
                for key, column in batch_mapping.items():
                    if key in data:
                        batch_fields.append(f"{column} = %s")
                        batch_values.append(data[key])
                
                if batch_fields:
                    batch_values.append(batch_id)
                    sql_batch = f"UPDATE product_batches SET {', '.join(batch_fields)} WHERE batch_id = %s"
                    cursor.execute(sql_batch, batch_values)
                    updated = True
                    print(f"✅ Updated batch {batch_id} for single product {id}")

            if not updated:
                return jsonify({'error': 'No valid fields to update'}), 400

            conn.commit()
            
            # Clean up old files after successful commit
            cleanup_old_files(old_image_paths, old_certificate_path)
            
            return jsonify({
                'success': True,
                'message': 'Product updated successfully',
                'images_optimized': len(new_image_paths)
            }), 200

        # =================================================================
        # VARIABLE PRODUCT
        # =================================================================
        elif product_type == 'variable':
            
            # Check if variation_id is provided
            if 'variation_id' in data and data['variation_id']:
                variation_id = data['variation_id']
                
                # Verify variation belongs to this product
                cursor.execute("""
                    SELECT id, product_id 
                    FROM product_variations 
                    WHERE id = %s AND product_id = %s
                """, (variation_id, id))
                
                variation_check = cursor.fetchone()
                
                if not variation_check:
                    return jsonify({
                        'error': 'Variation not found or does not belong to this product'
                    }), 404
                
                # Update variation-level fields (not batch-specific)
                var_mapping = {
                    'variation_name': 'variation_name',
                    'variation_type': 'variation_type',
                    'variation_sku': 'variation_sku',
                    'variation_tax': 'variation_tax',
                    'variation_tax_type': 'variation_tax_type',
                    'variation_stock_alert': 'variation_stock_alert'
                }
                
                var_fields = []
                var_values = []
                
                for key, column in var_mapping.items():
                    if key in data:
                        var_fields.append(f"{column} = %s")
                        var_values.append(data[key])
                
                if var_fields:
                    var_values.append(variation_id)
                    sql_var = f"UPDATE product_variations SET {', '.join(var_fields)} WHERE id = %s"
                    cursor.execute(sql_var, var_values)
                    updated = True
                    print(f"✅ Updated variation {variation_id}")
                
                # Check if batch_id is provided (editing specific variation batch)
                if 'batch_id' in data and data['batch_id']:
                    batch_id = data['batch_id']
                    
                    # Verify batch belongs to this variation
                    cursor.execute("""
                        SELECT batch_id, product_id, variation_id 
                        FROM product_batches 
                        WHERE batch_id = %s AND product_id = %s AND variation_id = %s
                    """, (batch_id, id, variation_id))
                    
                    batch_check = cursor.fetchone()
                    
                    if not batch_check:
                        return jsonify({
                            'error': 'Batch not found or does not belong to this variation'
                        }), 404
                    
                    # ✅ Update batch-specific fields for variation including our_price
                    var_batch_mapping = {
                        'variation_cost': 'cost',
                        'variation_price': 'price',
                        'our_price': 'our_price',
                        'expiration_date': 'expiration_date'
                    }
                    
                    var_batch_fields = []
                    var_batch_values = []
                    
                    for key, column in var_batch_mapping.items():
                        if key in data:
                            var_batch_fields.append(f"{column} = %s")
                            var_batch_values.append(data[key])
                    
                    if var_batch_fields:
                        var_batch_values.append(batch_id)
                        sql_var_batch = f"UPDATE product_batches SET {', '.join(var_batch_fields)} WHERE batch_id = %s"
                        cursor.execute(sql_var_batch, var_batch_values)
                        updated = True
                        print(f"✅ Updated batch {batch_id} for variation {variation_id}")
                
                else:
                    # No batch_id provided - update variation defaults
                    var_default_mapping = {
                        'variation_cost': 'variation_cost',
                        'variation_price': 'variation_price',
                        'variation_sku': 'variation_sku',
                        'expiration_date': 'expiration_date'
                    }
                    
                    var_default_fields = []
                    var_default_values = []
                    
                    for key, column in var_default_mapping.items():
                        if key in data:
                            var_default_fields.append(f"{column} = %s")
                            var_default_values.append(data[key])
                    
                    if var_default_fields:
                        var_default_values.append(variation_id)
                        sql_var_default = f"UPDATE product_variations SET {', '.join(var_default_fields)} WHERE id = %s"
                        cursor.execute(sql_var_default, var_default_values)
                        updated = True
                        print(f"✅ Updated variation defaults for variation {variation_id}")

            if not updated:
                return jsonify({'error': 'No valid fields to update'}), 400

            conn.commit()
            
            # Clean up old files after successful commit
            cleanup_old_files(old_image_paths, old_certificate_path)
            
            return jsonify({
                'success': True,
                'message': 'Variable product updated successfully',
                'images_optimized': len(new_image_paths)
            }), 200

        else:
            return jsonify({'error': 'Unknown product type'}), 400

    except mysql.connector.Error as e:
        if conn:
            conn.rollback()
        
        # Clean up new files on error
        cleanup_new_files(new_image_paths, new_certificate_path)
        
        print(f"Database error in update_product: {e}")
        import traceback
        traceback.print_exc()
        return jsonify({
            'error': 'Database error occurred',
            'details': str(e)
        }), 500
    
    except Exception as e:
        if conn:
            conn.rollback()
        
        # Clean up new files on error
        cleanup_new_files(new_image_paths, new_certificate_path)
        
        print(f"Error in update_product: {e}")
        import traceback
        traceback.print_exc()
        return jsonify({
            'error': 'Failed to update product',
            'details': str(e)
        }), 500
    
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


# =================================================================
# HELPER FUNCTIONS FOR FILE CLEANUP
# =================================================================

def cleanup_old_files(old_image_paths, old_certificate_path):
    """Delete old files after successful update"""
    # Clean up old images
    if old_image_paths:
        for old_path in old_image_paths:
            try:
                if old_path and os.path.exists(old_path):
                    os.remove(old_path)
                    print(f"🗑️ Deleted old image: {old_path}")
            except Exception as e:
                print(f"⚠️ Could not delete old image {old_path}: {e}")
    
    # Clean up old certificate
    if old_certificate_path:
        try:
            if os.path.exists(old_certificate_path):
                os.remove(old_certificate_path)
                print(f"🗑️ Deleted old certificate: {old_certificate_path}")
        except Exception as e:
            print(f"⚠️ Could not delete old certificate {old_certificate_path}: {e}")


def cleanup_new_files(new_image_paths, new_certificate_path):
    """Delete newly uploaded files if transaction fails"""
    # Clean up new images
    if new_image_paths:
        for new_path in new_image_paths:
            try:
                if new_path and os.path.exists(new_path):
                    os.remove(new_path)
                    print(f"🗑️ Rolled back new image: {new_path}")
            except Exception as e:
                print(f"⚠️ Could not delete new image {new_path}: {e}")
    
    # Clean up new certificate
    if new_certificate_path:
        try:
            if os.path.exists(new_certificate_path):
                os.remove(new_certificate_path)
                print(f"🗑️ Rolled back new certificate: {new_certificate_path}")
        except Exception as e:
            print(f"⚠️ Could not delete new certificate {new_certificate_path}: {e}")


@product_bp.route('/delete_product/<int:id>', methods=['DELETE'])
@jwt_required()
@role_required('admin')
def delete_product(id):
    """
    Delete a product and all related data.
    
    Validations:
    - Check if product has stock
    - Check if product is used in pending orders
    - Delete all related records (variations, batches, warehouse stock)
    
    Note: This is a destructive operation. Consider soft delete for production.
    """
    conn = get_db_connection()
    if conn is None:
        return jsonify({'error': 'Database connection failed'}), 500

    cursor = conn.cursor(dictionary=True)

    try:
        # 1️⃣ Check if product exists
        cursor.execute("""
            SELECT 
                id,
                product_name,
                sku,
                product_type
            FROM products 
            WHERE id = %s
        """, (id,))
        
        product = cursor.fetchone()

        if not product:
            return jsonify({'error': 'Product not found'}), 404

        product_name = product['product_name']
        product_sku = product['sku']
        product_type = product['product_type']

        # 2️⃣ Check total stock across all warehouses
        cursor.execute("""
            SELECT IFNULL(SUM(quantity), 0) as total_stock
            FROM warehouse_stock
            WHERE product_id = %s
        """, (id,))
        
        stock_result = cursor.fetchone()
        total_stock = float(stock_result['total_stock']) if stock_result else 0.0

        if total_stock > 0:
            return jsonify({
                'error': f'Cannot delete product with existing stock. Current total stock: {total_stock}. Please remove all stock first through stock adjustments.'
            }), 400

        # 3️⃣ Check if product is used in pending purchase orders
        cursor.execute("""
            SELECT COUNT(*) as order_count
            FROM order_items oi
            JOIN purchase_orders po ON oi.order_id = po.order_id
            WHERE oi.product_id = %s 
            AND po.status IN ('pending', 'ordered')
        """, (id,))
        
        order_result = cursor.fetchone()
        pending_orders = order_result['order_count'] if order_result else 0

        if pending_orders > 0:
            return jsonify({
                'error': f'Cannot delete product used in {pending_orders} pending purchase order(s). Complete, receive, or cancel orders first.'
            }), 400

        # 4️⃣ Count related records before deletion
        cursor.execute("""
            SELECT COUNT(*) as variation_count
            FROM product_variations
            WHERE product_id = %s
        """, (id,))
        variation_count = cursor.fetchone()['variation_count']

        cursor.execute("""
            SELECT COUNT(*) as batch_count
            FROM product_batches
            WHERE product_id = %s
        """, (id,))
        batch_count = cursor.fetchone()['batch_count']

        cursor.execute("""
            SELECT COUNT(*) as warehouse_stock_count
            FROM warehouse_stock
            WHERE product_id = %s
        """, (id,))
        warehouse_stock_count = cursor.fetchone()['warehouse_stock_count']

        # Check completed orders (will be kept for history)
        cursor.execute("""
            SELECT COUNT(*) as completed_order_count
            FROM order_items oi
            JOIN purchase_orders po ON oi.order_id = po.order_id
            WHERE oi.product_id = %s 
            AND po.status IN ('received', 'completed')
        """, (id,))
        completed_orders = cursor.fetchone()['completed_order_count']

        # 5️⃣ Delete related warehouse stock (should be 0 at this point)
        cursor.execute("""
            DELETE FROM warehouse_stock 
            WHERE product_id = %s
        """, (id,))
        deleted_stock = cursor.rowcount

        # 6️⃣ Delete related product batches
        cursor.execute("""
            DELETE FROM product_batches 
            WHERE product_id = %s
        """, (id,))
        deleted_batches = cursor.rowcount

        # 7️⃣ Delete related product variations (if variable product)
        cursor.execute("""
            DELETE FROM product_variations 
            WHERE product_id = %s
        """, (id,))
        deleted_variations = cursor.rowcount

        # 8️⃣ Handle order_items - Keep for completed orders (history)
        if completed_orders > 0:
            # Don't delete - keep for history
            print(f"⚠️ Keeping {completed_orders} order_items in completed orders for history")
            deleted_order_items = 0
        else:
            # No completed orders - safe to delete
            cursor.execute("""
                DELETE FROM order_items 
                WHERE product_id = %s
            """, (id,))
            deleted_order_items = cursor.rowcount

        # 9️⃣ Finally, delete the product itself
        cursor.execute("""
            DELETE FROM products 
            WHERE id = %s
        """, (id,))

        if cursor.rowcount == 0:
            conn.rollback()
            return jsonify({'error': 'Failed to delete product'}), 500

        # Commit all changes
        conn.commit()

        return jsonify({
            'success': True,
            'message': f"✅ Product '{product_name}' (SKU: {product_sku}) deleted successfully!",
            'data': {
                'deleted_product_id': id,
                'product_name': product_name,
                'product_sku': product_sku,
                'product_type': product_type,
                'deleted_records': {
                    'variations': deleted_variations,
                    'batches': deleted_batches,
                    'warehouse_stock': deleted_stock,
                    'order_items': deleted_order_items
                },
                'related_counts_before_deletion': {
                    'variations': variation_count,
                    'batches': batch_count,
                    'warehouse_stock_entries': warehouse_stock_count
                },
                'completed_orders_kept': completed_orders
            }
        }), 200

    except mysql.connector.Error as err:
        conn.rollback()
        print(f"❌ Database Error deleting product: {err}")
        traceback.print_exc()
        return jsonify({'error': f'Database error: {str(err)}'}), 500

    except Exception as e:
        conn.rollback()
        print(f"❌ Error deleting product: {e}")
        traceback.print_exc()
        return jsonify({'error': f'Failed to delete product: {str(e)}'}), 500

    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()
            
            
@product_bp.route('/base_units', methods=['GET'])
@jwt_required()
@role_required('admin')
def get_base_units():
    try:
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Failed to connect to database'}), 500

        cursor = conn.cursor(dictionary=True)

        # Fetch base units (latest first)
        cursor.execute("SELECT * FROM base_units ORDER BY id DESC")
        base_units = cursor.fetchall()

        # Fetch corresponding units for each base unit
        for base_unit in base_units:
            cursor.execute(
                "SELECT * FROM units WHERE base_unit_id = %s",
                (base_unit['id'],)
            )
            base_unit['units'] = cursor.fetchall()

        cursor.close()
        conn.close()

        return jsonify({"base_units": base_units}), 200

    except Exception as e:
        print("Error in /base_units:", str(e))
        traceback.print_exc()
        try:
            cursor.close()
            conn.close()
        except Exception:
            pass
        return jsonify({'error': 'An error occurred while fetching base units'}), 500
            
            
@product_bp.route('/base_unit', methods=['POST'])
@jwt_required()
@role_required('admin')
def add_base_unit():
    try:
        if not request.is_json:
            return jsonify({'error': 'Request body must be JSON'}), 400

        data = request.get_json()
        base_unit = data.get('base_unit')

        if not base_unit or not base_unit.strip():
            return jsonify({'error': 'base_unit is required'}), 400

        base_unit = base_unit.strip()

        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Failed to connect to database'}), 500

        cursor = conn.cursor()

        # Check duplicate
        cursor.execute(
            "SELECT id FROM base_units WHERE base_unit = %s",
            (base_unit,)
        )
        if cursor.fetchone():
            cursor.close()
            conn.close()
            return jsonify({'error': 'Base unit already exists'}), 409

        cursor.execute(
            "INSERT INTO base_units (base_unit) VALUES (%s)",
            (base_unit,)
        )
        conn.commit()

        new_id = cursor.lastrowid

        cursor.close()
        conn.close()

        return jsonify({
            'message': 'Base unit added successfully',
            'id': new_id
        }), 201

    except Exception as e:
        print("Error in /base_unit:", str(e))
        traceback.print_exc()
        try:
            cursor.close()
            conn.rollback()
            conn.close()
        except Exception:
            pass
        return jsonify({'error': 'An error occurred while adding base unit'}), 500


@product_bp.route('/base_units/<int:id>', methods=['PUT'])
@jwt_required()
@role_required('admin')
def update_base_unit(id):
    try:
        if not request.is_json:
            return jsonify({'error': 'Request body must be JSON'}), 400

        data = request.get_json()
        base_unit = data.get('base_unit')

        if not base_unit or not base_unit.strip():
            return jsonify({'error': 'base_unit is required'}), 400

        base_unit = base_unit.strip()

        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Failed to connect to database'}), 500

        cursor = conn.cursor()

        # Check if base unit exists
        cursor.execute("SELECT base_unit FROM base_units WHERE id = %s", (id,))
        existing = cursor.fetchone()

        if not existing:
            cursor.close()
            conn.close()
            return jsonify({'error': 'Base unit not found'}), 404

        # If value unchanged
        if existing[0] == base_unit:
            cursor.close()
            conn.close()
            return jsonify({'message': 'Base unit updated successfully'}), 200

        # Check duplicate name on other rows
        cursor.execute(
            "SELECT id FROM base_units WHERE base_unit = %s AND id <> %s",
            (base_unit, id)
        )
        if cursor.fetchone():
            cursor.close()
            conn.close()
            return jsonify({'error': 'Another base unit with this name already exists'}), 409

        cursor.execute(
            "UPDATE base_units SET base_unit = %s WHERE id = %s",
            (base_unit, id)
        )
        conn.commit()

        cursor.close()
        conn.close()

        return jsonify({'message': 'Base unit updated successfully'}), 200

    except Exception as e:
        print("Error in /base_units/<id> [PUT]:", str(e))
        traceback.print_exc()
        try:
            cursor.close()
            conn.rollback()
            conn.close()
        except Exception:
            pass
        return jsonify({'error': 'An error occurred while updating base unit'}), 500

@product_bp.route('/base_units/<int:id>', methods=['DELETE'])
@jwt_required()
@role_required('admin')
def delete_base_unit(id):
    try:
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Failed to connect to database'}), 500

        cursor = conn.cursor()

        # Check if base unit exists
        cursor.execute("SELECT id FROM base_units WHERE id = %s", (id,))
        if not cursor.fetchone():
            cursor.close()
            conn.close()
            return jsonify({'error': 'Base unit not found'}), 404

        # Optional safety: prevent delete if units exist
        cursor.execute(
            "SELECT COUNT(*) FROM units WHERE base_unit_id = %s",
            (id,)
        )
        unit_count = cursor.fetchone()[0]

        if unit_count > 0:
            cursor.close()
            conn.close()
            return jsonify({
                'error': 'Cannot delete base unit with linked units'
            }), 400

        cursor.execute("DELETE FROM base_units WHERE id = %s", (id,))
        conn.commit()

        cursor.close()
        conn.close()

        return jsonify({'message': 'Base unit deleted successfully'}), 200

    except Exception as e:
        print("Error in /base_units/<id> [DELETE]:", str(e))
        traceback.print_exc()
        try:
            cursor.close()
            conn.rollback()
            conn.close()
        except Exception:
            pass
        return jsonify({'error': 'An error occurred while deleting base unit'}), 500
        

@product_bp.route('/get_brands', methods=['GET'])
@jwt_required()
@role_required('admin')
def get_brands():
    try:
        conn = get_db_connection()
        if conn is None:
            return jsonify({'error': 'Database connection failed'}), 500

        cursor = conn.cursor(dictionary=True)

        query = """
            SELECT 
                b.id, 
                b.brand_name, 
                COUNT(p.id) AS product_count
            FROM brands b
            LEFT JOIN products p ON p.brand_id = b.id
            GROUP BY b.id, b.brand_name
            ORDER BY b.id DESC
        """

        cursor.execute(query)
        brands = cursor.fetchall()

        cursor.close()
        conn.close()

        if not brands:
            return jsonify({'message': 'No brands found'}), 200

        return jsonify(brands), 200

    except Exception as e:
        print("Error in /get_brands:", str(e))
        traceback.print_exc()
        return jsonify({'error': 'An error occurred while fetching brands'}), 500

@product_bp.route('/add_brand', methods=['POST'])
@jwt_required()
@role_required('admin')
def add_brand():
    try:
        if request.is_json:
            data = request.get_json()
            brand_name = data.get('brand_name')
        else:
            brand_name = request.form.get('brand_name')

        if not brand_name or not brand_name.strip():
            return jsonify({'error': 'Brand name is required'}), 400

        conn = get_db_connection()
        if conn is None:
            return jsonify({'error': 'Database connection failed'}), 500

        cursor = conn.cursor()

        # Check if brand already exists
        cursor.execute("SELECT id FROM brands WHERE brand_name = %s", (brand_name,))
        existing_brand = cursor.fetchone()

        if existing_brand:
            cursor.close()
            conn.close()
            return jsonify({'error': 'Brand already exists'}), 409

        # ✅ Insert new brand
        cursor.execute(
            "INSERT INTO brands (brand_name) VALUES (%s)",
            (brand_name,)
        )
        conn.commit()

        # ✅ GET THE NEW ID
        new_brand_id = cursor.lastrowid

        cursor.close()
        conn.close()

        # ✅ RETURN ID WITH MESSAGE
        return jsonify({
            'message': 'Brand added successfully',
            'id': new_brand_id,
            'brand_name': brand_name
        }), 201

    except Exception as e:
        print("Error in /add_brand:", str(e))
        traceback.print_exc()
        return jsonify({'error': 'An error occurred while adding the brand'}), 500


@product_bp.route('/update_brand/<int:id>', methods=['PUT'])
@jwt_required()
@role_required('admin')
def update_brand(id):
    try:
        data = request.get_json()
        if not data:
            return jsonify({'error': 'Request body is required'}), 400

        brand_name = data.get('brand_name')

        if not brand_name or not brand_name.strip():
            return jsonify({'error': 'Brand name is required'}), 400

        conn = get_db_connection()
        if conn is None:
            return jsonify({'error': 'Database connection failed'}), 500

        cursor = conn.cursor()

        # Check if brand exists
        cursor.execute("SELECT id FROM brands WHERE id = %s", (id,))
        existing_brand = cursor.fetchone()

        if not existing_brand:
            cursor.close()
            conn.close()
            return jsonify({'error': 'Brand not found'}), 404

        # Update brand
        cursor.execute(
            "UPDATE brands SET brand_name = %s WHERE id = %s",
            (brand_name, id)
        )
        conn.commit()

        cursor.close()
        conn.close()

        return jsonify({'message': 'Brand updated successfully'}), 200

    except Exception as e:
        print("Error in /update_brand:", str(e))
        traceback.print_exc()
        return jsonify({'error': 'An error occurred while updating the brand'}), 500


@product_bp.route('/delete_brand/<int:id>', methods=['DELETE'])
@jwt_required()
@role_required('admin')
def delete_brand(id):
    """Delete a brand by ID."""
    conn = get_db_connection()
    cursor = conn.cursor()

    try:
        cursor.execute("DELETE FROM brands WHERE id = %s", (id,))
        conn.commit()
        return jsonify({'message': 'Brand deleted successfully'}), 200
    except mysql.connector.Error as err:
        conn.rollback()
        return jsonify({'error': 'Failed to delete brand'}), 500
    finally:
        cursor.close()
        conn.close()


# GET categories with product counts
@product_bp.route('/get_categories', methods=['GET'])
@jwt_required()
@role_required('admin')
def get_categories():
    try:
        conn = get_db_connection()
        if conn is None:
            return jsonify({'error': 'Database connection failed'}), 500

        cursor = conn.cursor(dictionary=True)

        query = """
            SELECT
                c.id,
                c.category_name,
                c.discount_type,
                c.discount_value,
                COUNT(p.id) AS product_count
            FROM categories c
            LEFT JOIN products p ON p.category_id = c.id
            GROUP BY c.id, c.category_name, c.discount_type, c.discount_value
            ORDER BY c.id DESC
        """

        cursor.execute(query)
        categories = cursor.fetchall()

        cursor.close()
        conn.close()

        if not categories:
            return jsonify({'message': 'No categories found'}), 200

        return jsonify(categories), 200

    except Exception as e:
        print("Error in /get_categories:", str(e))
        traceback.print_exc()
        # ensure connection/cursor closed if exception occurred before close (best-effort)
        try:
            cursor.close()
        except Exception:
            pass
        try:
            conn.close()
        except Exception:
            pass
        return jsonify({'error': 'An error occurred while fetching categories'}), 500


# ADD category
@product_bp.route('/add_category', methods=['POST'])
@jwt_required()
@role_required('admin')
def add_category():
    try:
        if request.is_json:
            data = request.get_json()
            category_name = data.get('category_name') if data else None
        else:
            data = {}
            category_name = request.form.get('category_name')

        if not category_name or not category_name.strip():
            return jsonify({'error': 'Category name is required'}), 400

        category_name = category_name.strip()

        # Optional discount fields
        discount_type = data.get('discount_type') if data else None
        discount_value = float(data.get('discount_value', 0)) if data else 0.00

        if discount_type and discount_type not in ('fixed', 'percentage'):
            return jsonify({'error': "discount_type must be 'fixed', 'percentage', or null"}), 400

        conn = get_db_connection()
        if conn is None:
            return jsonify({'error': 'Database connection failed'}), 500

        cursor = conn.cursor()

        # Check duplicate
        cursor.execute("SELECT id FROM categories WHERE category_name = %s", (category_name,))
        if cursor.fetchone():
            cursor.close()
            conn.close()
            return jsonify({'error': 'Category already exists'}), 409

        # Insert
        cursor.execute(
            "INSERT INTO categories (category_name, discount_type, discount_value) VALUES (%s, %s, %s)",
            (category_name, discount_type, discount_value)
        )
        conn.commit()

        # GET THE NEW ID
        new_category_id = cursor.lastrowid

        cursor.close()
        conn.close()

        # RETURN ID WITH MESSAGE
        return jsonify({
            'message': 'Category added successfully',
            'id': new_category_id,
            'category_name': category_name,
            'discount_type': discount_type,
            'discount_value': discount_value
        }), 201

    except Exception as e:
        print("Error in /add_category:", str(e))
        traceback.print_exc()
        try:
            if cursor:
                cursor.close()
        except Exception:
            pass
        try:
            if conn:
                conn.close()
        except Exception:
            pass
        return jsonify({'error': 'An error occurred while adding the category'}), 500


# UPDATE category
@product_bp.route('/update_category/<int:id>', methods=['PUT'])
@jwt_required()
@role_required('admin')
def update_category(id):
    try:
        if not request.is_json:
            return jsonify({'error': 'Request body must be JSON'}), 400

        data = request.get_json()
        if not data:
            return jsonify({'error': 'Request body is required'}), 400

        category_name = data.get('category_name')
        if not category_name or not category_name.strip():
            return jsonify({'error': 'Category name is required'}), 400
        category_name = category_name.strip()

        # Optional discount fields
        discount_type = data.get('discount_type')
        discount_value = float(data.get('discount_value', 0))

        if discount_type and discount_type not in ('fixed', 'percentage'):
            return jsonify({'error': "discount_type must be 'fixed', 'percentage', or null"}), 400

        conn = get_db_connection()
        if conn is None:
            return jsonify({'error': 'Database connection failed'}), 500

        cursor = conn.cursor()

        # Check target exists
        cursor.execute("SELECT id FROM categories WHERE id = %s", (id,))
        if cursor.fetchone() is None:
            cursor.close()
            conn.close()
            return jsonify({'error': 'Category not found'}), 404

        # Check duplicate name for other id
        cursor.execute("SELECT id FROM categories WHERE category_name = %s AND id <> %s", (category_name, id))
        if cursor.fetchone():
            cursor.close()
            conn.close()
            return jsonify({'error': 'Another category with this name already exists'}), 409

        # Update
        cursor.execute(
            "UPDATE categories SET category_name = %s, discount_type = %s, discount_value = %s WHERE id = %s",
            (category_name, discount_type, discount_value, id)
        )
        conn.commit()

        cursor.close()
        conn.close()
        return jsonify({'message': 'Category updated successfully'}), 200

    except Exception as e:
        print("Error in /update_category:", str(e))
        traceback.print_exc()
        try:
            if cursor:
                cursor.close()
        except Exception:
            pass
        try:
            if conn:
                conn.rollback()
                conn.close()
        except Exception:
            pass
        return jsonify({'error': 'An error occurred while updating the category'}), 500


# SET / REMOVE category discount
@product_bp.route('/set_category_discount/<int:id>', methods=['PUT'])
@jwt_required()
@role_required('admin')
def set_category_discount(id):
    try:
        if not request.is_json:
            return jsonify({'error': 'Request body must be JSON'}), 400

        data = request.get_json()
        if not data:
            return jsonify({'error': 'Request body is required'}), 400

        discount_type = data.get('discount_type')  # 'fixed', 'percentage', or null to remove
        discount_value = float(data.get('discount_value', 0))

        if discount_type and discount_type not in ('fixed', 'percentage'):
            return jsonify({'error': "discount_type must be 'fixed', 'percentage', or null"}), 400

        # If removing discount, reset value to 0
        if discount_type is None:
            discount_value = 0.00

        conn = get_db_connection()
        if conn is None:
            return jsonify({'error': 'Database connection failed'}), 500

        cursor = conn.cursor(dictionary=True)

        # Check category exists
        cursor.execute("SELECT id, category_name FROM categories WHERE id = %s", (id,))
        category = cursor.fetchone()
        if not category:
            cursor.close()
            conn.close()
            return jsonify({'error': 'Category not found'}), 404

        # Update discount fields
        cursor.execute(
            "UPDATE categories SET discount_type = %s, discount_value = %s WHERE id = %s",
            (discount_type, discount_value, id)
        )
        conn.commit()

        cursor.close()
        conn.close()

        return jsonify({
            'message': 'Category discount updated successfully',
            'id': id,
            'category_name': category['category_name'],
            'discount_type': discount_type,
            'discount_value': discount_value
        }), 200

    except Exception as e:
        print("Error in /set_category_discount:", str(e))
        traceback.print_exc()
        try:
            cursor.close()
        except Exception:
            pass
        try:
            conn.close()
        except Exception:
            pass
        return jsonify({'error': 'An error occurred while updating category discount'}), 500


# DELETE category
@product_bp.route('/delete_category/<int:id>', methods=['DELETE'])
@jwt_required()
@role_required('admin')
def delete_category(id):
    try:
        conn = get_db_connection()
        if conn is None:
            return jsonify({'error': 'Database connection failed'}), 500

        cursor = conn.cursor()

        # Check category exists
        cursor.execute("SELECT id FROM categories WHERE id = %s", (id,))
        if cursor.fetchone() is None:
            cursor.close()
            conn.close()
            return jsonify({'error': 'Category not found'}), 404

        # Optional: prevent delete when products exist (safer)
        cursor.execute("SELECT COUNT(1) AS cnt FROM products WHERE category_id = %s", (id,))
        row = cursor.fetchone()
        product_count = row[0] if row else 0
        if product_count and product_count > 0:
            cursor.close()
            conn.close()
            return jsonify({'error': 'Cannot delete category with associated products'}), 400

        # Delete
        cursor.execute("DELETE FROM categories WHERE id = %s", (id,))
        conn.commit()

        cursor.close()
        conn.close()
        return jsonify({'message': 'Category deleted successfully'}), 200

    except Exception as e:
        print("Error in /delete_category:", str(e))
        traceback.print_exc()
        try:
            if cursor:
                cursor.close()
        except Exception:
            pass
        try:
            if conn:
                conn.rollback()
                conn.close()
        except Exception:
            pass
        return jsonify({'error': 'An error occurred while deleting the category'}), 500


@product_bp.route('/add_unit', methods=['POST'])
@jwt_required()
@role_required('admin')
def add_unit():
    try:
        if not request.is_json:
            return jsonify({'error': 'Request body must be JSON'}), 400

        data = request.get_json()

        unit_name = data.get("unit_name")
        unit_short = data.get("unit_short")
        # ✅ Accept both 'base_unit' and 'base_unit_id' for backward compatibility
        base_unit_id = data.get("base_unit_id") or data.get("base_unit")

        if not unit_name or not unit_name.strip():
            return jsonify({'error': 'unit_name is required'}), 400

        if not unit_short or not unit_short.strip():
            return jsonify({'error': 'unit_short is required'}), 400

        if not base_unit_id:
            return jsonify({'error': 'base_unit_id is required'}), 400

        unit_name = unit_name.strip()
        unit_short = unit_short.strip()

        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Failed to connect to database'}), 500

        cursor = conn.cursor()

        # Check base unit exists
        cursor.execute("SELECT id FROM base_units WHERE id = %s", (base_unit_id,))
        if not cursor.fetchone():
            cursor.close()
            conn.close()
            return jsonify({'error': 'Base unit not found'}), 404

        # Prevent duplicate unit
        cursor.execute(
            "SELECT id FROM units WHERE unit_name = %s AND base_unit_id = %s",
            (unit_name, base_unit_id)
        )
        if cursor.fetchone():
            cursor.close()
            conn.close()
            return jsonify({'error': 'Unit already exists'}), 409

        cursor.execute(
            "INSERT INTO units (unit_name, unit_short, base_unit_id) VALUES (%s, %s, %s)",
            (unit_name, unit_short, base_unit_id)
        )
        conn.commit()

        new_id = cursor.lastrowid

        cursor.close()
        conn.close()

        return jsonify({
            "message": "Unit added successfully!",
            "id": new_id
        }), 201

    except Exception as e:
        print("Error in /add_unit:", str(e))
        traceback.print_exc()
        try:
            cursor.close()
            conn.rollback()
            conn.close()
        except Exception:
            pass
        return jsonify({'error': 'An error occurred while adding unit'}), 500
    
    
@product_bp.route('/get_units', methods=['GET'])
@jwt_required()
@role_required('admin', 'manager', 'cashier')
def get_all_units():
    """
    Get all units with their base unit information
    """
    conn = get_db_connection()
    if conn is None:
        return jsonify({'error': 'Database connection failed'}), 500

    cursor = conn.cursor(dictionary=True)
    try:
        cursor.execute("""
            SELECT 
                u.id,
                u.unit_name,
                u.unit_short,
                u.base_unit_id,
                bu.base_unit AS base_unit_name,
                u.created_at
            FROM units u
            LEFT JOIN base_units bu ON u.base_unit_id = bu.id
            ORDER BY u.unit_name ASC
        """)
        
        units = cursor.fetchall()
        
        return jsonify(units), 200

    except mysql.connector.Error as err:
        print(f"Database Error fetching units: {err}")
        return jsonify({'error': f'Database error: {str(err)}'}), 500
    
    except Exception as err:
        print(f"Error fetching units: {err}")
        return jsonify({'error': 'Failed to fetch units'}), 500

    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


@product_bp.route('/get_units/<int:id>', methods=['GET'])
@jwt_required()
@role_required('admin')
def get_unit(id):
    try:
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Failed to connect to database'}), 500

        cursor = conn.cursor(dictionary=True)

        cursor.execute("""
            SELECT 
                u.id, 
                u.unit_name, 
                u.unit_short, 
                u.base_unit_id, 
                b.base_unit, 
                u.created_at
            FROM units u
            JOIN base_units b ON u.base_unit_id = b.id
            WHERE u.id = %s
        """, (id,))

        unit = cursor.fetchone()

        cursor.close()
        conn.close()

        if unit:
            return jsonify(unit), 200
        else:
            return jsonify({"error": "Unit not found"}), 404

    except Exception as e:
        print("Error in /get_units/<id>:", str(e))
        traceback.print_exc()
        try:
            cursor.close()
            conn.close()
        except Exception:
            pass
        return jsonify({'error': 'An error occurred while fetching unit'}), 500


@product_bp.route('/update_unit/<int:id>', methods=['PUT'])
@jwt_required()
@role_required('admin')
def update_unit(id):
    try:
        # Validate request
        if not request.is_json:
            return jsonify({'error': 'Request body must be JSON'}), 400

        data = request.get_json()
        unit_name = data.get("unit_name")
        unit_short = data.get("unit_short")
        # ✅ Accept both 'base_unit_id' and 'base_unit' for compatibility
        base_unit_id = data.get("base_unit_id") or data.get("base_unit")

        if not unit_name or not unit_name.strip():
            return jsonify({'error': 'unit_name is required'}), 400
        if not unit_short or not unit_short.strip():
            return jsonify({'error': 'unit_short is required'}), 400
        if not base_unit_id:
            return jsonify({'error': 'base_unit_id is required'}), 400

        unit_name = unit_name.strip()
        unit_short = unit_short.strip()

        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Failed to connect to database'}), 500
        cursor = conn.cursor()

        # Check if unit exists
        cursor.execute("SELECT id FROM units WHERE id = %s", (id,))
        if not cursor.fetchone():
            cursor.close()
            conn.close()
            return jsonify({'error': f'Unit with ID {id} not found'}), 404

        # Check if base unit exists
        cursor.execute("SELECT id FROM base_units WHERE id = %s", (base_unit_id,))
        if not cursor.fetchone():
            cursor.close()
            conn.close()
            return jsonify({'error': 'Base unit not found'}), 404

        # Prevent duplicate unit name for same base unit
        cursor.execute(
            "SELECT id FROM units WHERE unit_name = %s AND base_unit_id = %s AND id <> %s",
            (unit_name, base_unit_id, id)
        )
        if cursor.fetchone():
            cursor.close()
            conn.close()
            return jsonify({'error': 'Another unit with this name already exists for this base unit'}), 409

        # Perform update
        cursor.execute(
            """
            UPDATE units
            SET unit_name = %s, unit_short = %s, base_unit_id = %s
            WHERE id = %s
            """,
            (unit_name, unit_short, base_unit_id, id)
        )
        conn.commit()

        cursor.close()
        conn.close()

        return jsonify({'message': f'Unit with ID {id} updated successfully!'}), 200

    except Exception as e:
        print("Error in /update_unit/<id>:", str(e))
        traceback.print_exc()
        try:
            cursor.close()
            conn.rollback()
            conn.close()
        except Exception:
            pass
        return jsonify({'error': 'An error occurred while updating unit'}), 500


@product_bp.route('/delete_unit/<int:id>', methods=['DELETE'])
@jwt_required()
@role_required('admin')
def delete_unit(id):
    try:
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Failed to connect to database'}), 500

        cursor = conn.cursor()

        # Check unit exists
        cursor.execute("SELECT id FROM units WHERE id = %s", (id,))
        if not cursor.fetchone():
            cursor.close()
            conn.close()
            return jsonify({'error': 'Unit not found'}), 404

        # ✅ Check if unit is being used by products
        cursor.execute("""
            SELECT COUNT(*) as count FROM products 
            WHERE base_unit_id = %s OR sale_unit_id = %s OR purchase_unit_id = %s
        """, (id, id, id))
        
        result = cursor.fetchone()
        if result and result[0] > 0:
            cursor.close()
            conn.close()
            return jsonify({
                'error': 'Cannot delete unit. It is being used by one or more products.'
            }), 409

        # Delete the unit
        cursor.execute("DELETE FROM units WHERE id = %s", (id,))
        conn.commit()

        cursor.close()
        conn.close()

        return jsonify({"message": f"Unit with ID {id} deleted successfully!"}), 200

    except Exception as e:
        print("Error in /delete_unit:", str(e))
        traceback.print_exc()
        try:
            cursor.close()
            conn.rollback()
            conn.close()
        except Exception:
            pass
        return jsonify({'error': 'An error occurred while deleting unit'}), 500

# @product_bp.route('/search_products', methods=['GET'])
# @jwt_required()
# @role_required('admin')
# def search_products():
#     """
#     Search products with warehouse-specific batch information
#     ✅ FIXED: Returns batches filtered by warehouse with correct remaining quantities
#     ✅ NEW: Handles new variation products without batches - shows product details with 0 stock
#     """
#     query = request.args.get('query')
    
#     if not query:
#         return jsonify({"status": "error", "message": "Query parameter is required"}), 400

#     conn = get_db_connection()
#     cursor = conn.cursor(dictionary=True)

#     try:
#         like_query = f"%{query}%"

#         # Main product search query
#         cursor.execute("""
#             SELECT 
#                 p.id AS product_id,
#                 p.product_name,
#                 p.sku AS product_sku,
#                 p.product_type,
#                 p.tax_type,
#                 p.product_tax,
#                 p.stock_alert,
#                 p.expiration_date,
#                 p.note,
                
#                 b.id AS brand_id,
#                 b.brand_name,
                
#                 c.id AS category_id,
#                 c.category_name,
                
#                 bu.id AS base_unit_id,
#                 bu.unit_name AS unit_name,
#                 bu.unit_short AS unit_short,
#                 base_u.base_unit AS base_unit_name,
                
#                 su.id AS sale_unit_id,
#                 su.unit_name AS sale_unit_name,
#                 su.unit_short AS sale_unit_short,
                
#                 pu.id AS purchase_unit_id,
#                 pu.unit_name AS purchase_unit_name,
#                 pu.unit_short AS purchase_unit_short,
                
#                 pv.id AS variation_id,
#                 pv.variation_name,
#                 pv.variation_type,
#                 pv.variation_sku,
#                 pv.variation_cost,
#                 pv.variation_price,
#                 pv.variation_tax_type,
#                 pv.variation_tax,
#                 pv.variation_stock_alert,
#                 pv.expiration_date AS variation_expiration_date
                
#             FROM products p
#             LEFT JOIN brands b ON p.brand_id = b.id
#             LEFT JOIN categories c ON p.category_id = c.id
#             LEFT JOIN units bu ON p.base_unit_id = bu.id
#             LEFT JOIN base_units base_u ON bu.base_unit_id = base_u.id
#             LEFT JOIN units su ON p.sale_unit_id = su.id
#             LEFT JOIN units pu ON p.purchase_unit_id = pu.id
#             LEFT JOIN product_variations pv ON pv.product_id = p.id
            
#             WHERE 
#                 LOWER(p.product_name) LIKE LOWER(%s) OR 
#                 LOWER(p.sku) LIKE LOWER(%s) OR
#                 LOWER(pv.variation_name) LIKE LOWER(%s) OR 
#                 LOWER(pv.variation_sku) LIKE LOWER(%s) OR
#                 LOWER(b.brand_name) LIKE LOWER(%s) OR
#                 LOWER(c.category_name) LIKE LOWER(%s)
            
#             GROUP BY 
#                 p.id, pv.id
            
#             ORDER BY 
#                 CASE
#                     WHEN LOWER(p.product_name) = LOWER(%s) THEN 1
#                     WHEN LOWER(p.sku) = LOWER(%s) THEN 2
#                     WHEN LOWER(pv.variation_sku) = LOWER(%s) THEN 3
#                     WHEN LOWER(p.product_name) LIKE LOWER(%s) THEN 4
#                     ELSE 5
#                 END,
#                 p.product_name ASC,
#                 pv.variation_name ASC
#         """, (like_query, like_query, like_query, like_query, like_query, like_query,
#               query, query, query, like_query))

#         rows = cursor.fetchall()
#         products = {}

#         for row in rows:
#             pid = row['product_id']
            
#             if pid not in products:
#                 # ✅ Get warehouse-specific batches for SINGLE products
#                 batches = []
#                 total_stock = 0.0
                
#                 if row['product_type'] == 'single':
#                     # Get batches with warehouse stock information
#                     cursor.execute("""
#                         SELECT 
#                             pb.batch_id,
#                             pb.batch_number,
#                             pb.cost AS product_cost,
#                             pb.price AS product_price,
#                             pb.expiration_date,
#                             pb.created_on,
#                             ws.warehouse_id,
#                             ws.quantity AS warehouse_remaining_qty,
#                             -- Total quantity across ALL warehouses for this batch
#                             (SELECT COALESCE(SUM(ws2.quantity), 0) 
#                              FROM warehouse_stock ws2 
#                              WHERE ws2.batch_id = pb.batch_id) AS product_total_stock
#                         FROM product_batches pb
#                         INNER JOIN warehouse_stock ws ON ws.batch_id = pb.batch_id
#                         WHERE pb.product_id = %s 
#                             AND pb.variation_id IS NULL
#                         ORDER BY ws.warehouse_id, pb.created_on DESC
#                     """, (pid,))
                    
#                     batch_rows = cursor.fetchall()
                    
#                     # Process batches
#                     for batch_row in batch_rows:
#                         warehouse_qty = float(batch_row['warehouse_remaining_qty']) if batch_row['warehouse_remaining_qty'] else 0.0
#                         total_qty = float(batch_row['product_total_stock']) if batch_row['product_total_stock'] else 0.0
                        
#                         batches.append({
#                             'batch_id': batch_row['batch_id'],
#                             'batch_number': batch_row['batch_number'],
#                             'product_cost': float(batch_row['product_cost']) if batch_row['product_cost'] else 0.0,
#                             'product_price': float(batch_row['product_price']) if batch_row['product_price'] else 0.0,
#                             'remaining_quantity': warehouse_qty,  # ✅ Warehouse-specific quantity
#                             'product_total_stock': total_qty,     # ✅ Total across all warehouses
#                             'expiration_date': batch_row['expiration_date'].isoformat() if batch_row['expiration_date'] else None,
#                             'created_on': batch_row['created_on'].isoformat() if batch_row['created_on'] else None,
#                             'warehouse_id': batch_row['warehouse_id']
#                         })
                    
#                     # Calculate total stock across all warehouses
#                     cursor.execute("""
#                         SELECT COALESCE(SUM(ws.quantity), 0) as total_stock
#                         FROM warehouse_stock ws
#                         INNER JOIN product_batches pb ON ws.batch_id = pb.batch_id
#                         WHERE pb.product_id = %s AND pb.variation_id IS NULL
#                     """, (pid,))
#                     total_result = cursor.fetchone()
#                     total_stock = float(total_result['total_stock']) if total_result else 0.0
                
#                 products[pid] = {
#                     "product_id": pid,
#                     "product_name": row['product_name'],
#                     "sku": row['product_sku'],
#                     "product_type": row['product_type'],
#                     "tax_type": row['tax_type'],
#                     "product_tax": float(row['product_tax']),
#                     "stock_alert": row['stock_alert'],
#                     "expiration_date": row['expiration_date'].isoformat() if row['expiration_date'] else None,
#                     "note": row['note'],
                    
#                     "brand_id": row['brand_id'],
#                     "brand_name": row['brand_name'],
#                     "category_id": row['category_id'],
#                     "category_name": row['category_name'],
                    
#                     "base_unit": {
#                         "id": row['base_unit_id'],
#                         "name": row['unit_name'],
#                         "short": row['unit_short'],
#                         "base_unit_name": row['base_unit_name']
#                     },
#                     "sale_unit": {
#                         "id": row['sale_unit_id'],
#                         "name": row['sale_unit_name'],
#                         "short": row['sale_unit_short']
#                     },
#                     "purchase_unit": {
#                         "id": row['purchase_unit_id'],
#                         "name": row['purchase_unit_name'],
#                         "short": row['purchase_unit_short']
#                     },
                    
#                     "total_stock": total_stock,
#                     "batches": batches,
#                     "variations": []
#                 }

#             # ✅ Add variation if available (EVEN IF NO BATCHES EXIST)
#             if row['variation_id']:
#                 variation_id = row['variation_id']
                
#                 # Get warehouse-specific batches for this variation
#                 cursor.execute("""
#                     SELECT 
#                         pb.batch_id,
#                         pb.batch_number,
#                         pb.cost AS product_cost,
#                         pb.price AS product_price,
#                         pb.expiration_date,
#                         pb.created_on,
#                         ws.warehouse_id,
#                         ws.quantity AS warehouse_remaining_qty,
#                         -- Total quantity across ALL warehouses for this batch
#                         (SELECT COALESCE(SUM(ws2.quantity), 0) 
#                          FROM warehouse_stock ws2 
#                          WHERE ws2.batch_id = pb.batch_id) AS product_total_stock
#                     FROM product_batches pb
#                     INNER JOIN warehouse_stock ws ON ws.batch_id = pb.batch_id
#                     WHERE pb.product_id = %s 
#                         AND pb.variation_id = %s
#                     ORDER BY ws.warehouse_id, pb.created_on DESC
#                 """, (pid, variation_id))
                
#                 var_batch_rows = cursor.fetchall()
                
#                 variation_batches = []
                
#                 # ✅ Process batches if they exist
#                 if var_batch_rows:
#                     for batch_row in var_batch_rows:
#                         warehouse_qty = float(batch_row['warehouse_remaining_qty']) if batch_row['warehouse_remaining_qty'] else 0.0
#                         total_qty = float(batch_row['product_total_stock']) if batch_row['product_total_stock'] else 0.0
                        
#                         variation_batches.append({
#                             'batch_id': batch_row['batch_id'],
#                             'batch_number': batch_row['batch_number'],
#                             'product_cost': float(batch_row['product_cost']) if batch_row['product_cost'] else 0.0,
#                             'product_price': float(batch_row['product_price']) if batch_row['product_price'] else 0.0,
#                             'remaining_quantity': warehouse_qty,  # ✅ Warehouse-specific
#                             'product_total_stock': total_qty,     # ✅ Total across all warehouses
#                             'expiration_date': batch_row['expiration_date'].isoformat() if batch_row['expiration_date'] else None,
#                             'created_on': batch_row['created_on'].isoformat() if batch_row['created_on'] else None,
#                             'warehouse_id': batch_row['warehouse_id']
#                         })
                
#                 # Calculate total stock for this variation across all warehouses
#                 cursor.execute("""
#                     SELECT COALESCE(SUM(ws.quantity), 0) as variation_total_stock
#                     FROM warehouse_stock ws
#                     INNER JOIN product_batches pb ON ws.batch_id = pb.batch_id
#                     WHERE pb.product_id = %s AND pb.variation_id = %s
#                 """, (pid, variation_id))
#                 var_total_result = cursor.fetchone()
#                 variation_total_stock = float(var_total_result['variation_total_stock']) if var_total_result else 0.0
                
#                 # ✅ IMPORTANT: Add variation even if batches list is empty
#                 # This allows new variations to be searchable and purchasable
#                 products[pid]['variations'].append({
#                     "variation_id": variation_id,
#                     "variation_name": row['variation_name'],
#                     "variation_type": row['variation_type'],
#                     "variation_sku": row['variation_sku'],
#                     # ✅ Use variation's defined cost/price if available, otherwise 0
#                     "product_cost": float(row['variation_cost']) if row['variation_cost'] else 0.0,
#                     "product_price": float(row['variation_price']) if row['variation_price'] else 0.0,
#                     "variation_tax_type": row['variation_tax_type'] or 'exclusive',
#                     "variation_tax": float(row['variation_tax'] or 0),
#                     "variation_stock_alert": row['variation_stock_alert'],
#                     "expiration_date": row['variation_expiration_date'].isoformat() if row['variation_expiration_date'] else None,
#                     "total_stock": variation_total_stock,  # Will be 0 for new variations
#                     "batches": variation_batches  # Will be empty [] for new variations
#                 })
        
#         # Calculate total stock for variable products (sum of all variations)
#         for product in products.values():
#             if product['product_type'] == 'variable' and product['variations']:
#                 product['total_stock'] = sum(v['total_stock'] for v in product['variations'])

#         print(f"✅ Found {len(products)} products matching query: {query}")
        
#         # Log variations without batches for debugging
#         for product in products.values():
#             if product['product_type'] == 'variable':
#                 for variation in product['variations']:
#                     if not variation['batches']:
#                         print(f"📦 New variation without batches: {product['product_name']} - {variation['variation_name']} (ID: {variation['variation_id']})")
        
#         return jsonify(list(products.values())), 200

#     except Exception as e:
#         print("❌ Search Error:", e)
#         import traceback
#         traceback.print_exc()
#         return jsonify({"status": "error", "message": str(e)}), 500

#     finally:
#         cursor.close()
#         conn.close()


def group_batches(batches):
    """
    ✅ FIX #4: Group batches by warehouse, price, cost, and expiry date
    ✅ CRITICAL: Ensures warehouse_id is not None
    
    Args:
        batches: List of batch dictionaries
        
    Returns:
        List of grouped batch dictionaries
    """
    if not batches:
        return []
    
    grouped = {}
    
    for batch in batches:
        # ✅ FIX #4: Skip batches without warehouse_id
        warehouse_id = batch.get('warehouse_id')
        if warehouse_id is None:
            print(f"⚠️ Warning: Skipping batch {batch.get('batch_id')} - no warehouse_id")
            continue
        
        # Create grouping key based on warehouse, price, cost, expiry
        key = (
            warehouse_id,
            batch['product_price'],
            batch['product_cost'],
            batch['expiration_date']
        )
        
        if key not in grouped:
            grouped[key] = {
                'batch_ids': [],
                'batch_numbers': [],
                'batch_count': 0,
                'warehouse_id': warehouse_id,
                'warehouse_name': batch['warehouse_name'],
                'product_cost': batch['product_cost'],
                'product_price': batch['product_price'],
                'remaining_quantity': 0.0,
                'expiration_date': batch['expiration_date'],
                'created_on': batch['created_on']  # Use earliest batch date
            }
        
        grouped[key]['batch_ids'].append(batch['batch_id'])
        grouped[key]['batch_numbers'].append(batch['batch_number'])
        grouped[key]['batch_count'] += 1
        grouped[key]['remaining_quantity'] += batch['remaining_quantity']
        
        # Keep earliest created_on date
        if batch['created_on'] and (
            grouped[key]['created_on'] is None or 
            batch['created_on'] < grouped[key]['created_on']
        ):
            grouped[key]['created_on'] = batch['created_on']
    
    # ✅ Sort by created_on (FIFO - oldest first)
    result = sorted(grouped.values(), key=lambda x: x['created_on'] or datetime.max)
    
    return result


# ==========================================
# SEARCH PRODUCTS - COMPLETE FIX
# ==========================================
@product_bp.route('/search_products', methods=['GET'])
@jwt_required()
@role_required('admin', 'manager', 'cashier')
def search_products():
    """
    Search products with warehouse-specific batch information
    
    ✅ FIX #1: Removes duplicate batch IDs
    ✅ FIX #2: Better stock validation (check > 0)
    ✅ FIX #4: Warehouse_id NULL check and validation
    ✅ FIX #5: Safe display of product names
    ✅ Returns batches with warehouse_name
    ✅ Handles variable products with variations
    ✅ Groups similar batches by price/cost/expiry
    """
    query = request.args.get('query')
    group_batches_param = request.args.get('group_batches', 'true').lower() == 'true'
    warehouse_id = request.args.get('warehouse_id')  # ✅ Optional warehouse filter
    
    if not query:
        return jsonify({"status": "error", "message": "Query parameter is required"}), 400

    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    try:
        like_query = f"%{query}%"

        print(f"\n🔍 Searching products: '{query}'")
        if warehouse_id:
            print(f"   Filtering by warehouse: {warehouse_id}")

        # Main product search query
        cursor.execute("""
            SELECT 
                p.id AS product_id,
                p.product_name,
                p.sku AS product_sku,
                p.product_type,
                p.tax_type,
                p.product_tax,
                p.stock_alert,
                p.expiration_date,
                p.note,
                
                b.id AS brand_id,
                b.brand_name,
                
                c.id AS category_id,
                c.category_name,
                
                bu.id AS base_unit_id,
                bu.unit_name AS unit_name,
                bu.unit_short AS unit_short,
                base_u.base_unit AS base_unit_name,
                
                su.id AS sale_unit_id,
                su.unit_name AS sale_unit_name,
                su.unit_short AS sale_unit_short,
                
                pu.id AS purchase_unit_id,
                pu.unit_name AS purchase_unit_name,
                pu.unit_short AS purchase_unit_short,
                
                pv.id AS variation_id,
                pv.variation_name,
                pv.variation_type,
                pv.variation_sku,
                pv.variation_cost,
                pv.variation_price,
                pv.variation_tax_type,
                pv.variation_tax,
                pv.variation_stock_alert,
                pv.expiration_date AS variation_expiration_date,

                c.discount_type AS category_discount_type,
                c.discount_value AS category_discount_value

            FROM products p
            LEFT JOIN brands b ON p.brand_id = b.id
            LEFT JOIN categories c ON p.category_id = c.id
            LEFT JOIN units bu ON p.base_unit_id = bu.id
            LEFT JOIN base_units base_u ON bu.base_unit_id = base_u.id
            LEFT JOIN units su ON p.sale_unit_id = su.id
            LEFT JOIN units pu ON p.purchase_unit_id = pu.id
            LEFT JOIN product_variations pv ON pv.product_id = p.id
            
            WHERE 
                LOWER(p.product_name) LIKE LOWER(%s) OR 
                LOWER(p.sku) LIKE LOWER(%s) OR
                LOWER(pv.variation_name) LIKE LOWER(%s) OR 
                LOWER(pv.variation_sku) LIKE LOWER(%s) OR
                LOWER(b.brand_name) LIKE LOWER(%s) OR
                LOWER(c.category_name) LIKE LOWER(%s)
            
            GROUP BY 
                p.id, pv.id
            
            ORDER BY 
                CASE
                    WHEN LOWER(p.product_name) = LOWER(%s) THEN 1
                    WHEN LOWER(p.sku) = LOWER(%s) THEN 2
                    WHEN LOWER(pv.variation_sku) = LOWER(%s) THEN 3
                    WHEN LOWER(p.product_name) LIKE LOWER(%s) THEN 4
                    ELSE 5
                END,
                p.product_name ASC,
                pv.variation_name ASC
        """, (like_query, like_query, like_query, like_query, like_query, like_query,
              query, query, query, like_query))

        rows = cursor.fetchall()
        products = {}

        print(f"   Found {len(rows)} product rows")

        for row in rows:
            pid = row['product_id']
            
            if pid not in products:
                # Get warehouse-specific batches for SINGLE products
                batches = []
                total_stock = 0.0
                
                if row['product_type'] == 'single':
                    # ✅ Build batch query with optional warehouse filter
                    batch_query = """
                        SELECT 
                            pb.batch_id,
                            pb.batch_number,
                            pb.cost AS product_cost,
                            pb.price AS product_price,
                            pb.expiration_date,
                            pb.created_on,
                            ws.warehouse_id,
                            w.warehouse_name,
                            ws.quantity AS warehouse_remaining_qty,
                            (SELECT COALESCE(SUM(ws2.quantity), 0) 
                             FROM warehouse_stock ws2 
                             WHERE ws2.batch_id = pb.batch_id) AS product_total_stock
                        FROM product_batches pb
                        INNER JOIN warehouse_stock ws ON ws.batch_id = pb.batch_id
                        LEFT JOIN warehouses w ON ws.warehouse_id = w.id
                        WHERE pb.product_id = %s 
                            AND pb.variation_id IS NULL
                            AND ws.quantity > 0
                            AND ws.warehouse_id IS NOT NULL
                    """
                    
                    batch_params = [pid]
                    
                    # ✅ Add warehouse filter if specified
                    if warehouse_id:
                        batch_query += " AND ws.warehouse_id = %s"
                        batch_params.append(int(warehouse_id))
                    
                    batch_query += " ORDER BY ws.warehouse_id, pb.created_on ASC"
                    
                    cursor.execute(batch_query, batch_params)
                    batch_rows = cursor.fetchall()
                    
                    # Process batches
                    ungrouped_batches = []
                    for batch_row in batch_rows:
                        warehouse_qty = safe_float(batch_row['warehouse_remaining_qty'])
                        
                        # ✅ FIX #2: Only include batches with stock > 0
                        if warehouse_qty <= 0:
                            continue
                        
                        # ✅ FIX #4: Skip batches without warehouse_id
                        if batch_row['warehouse_id'] is None:
                            print(f"⚠️ Skipping batch {batch_row['batch_id']} - no warehouse_id")
                            continue
                        
                        # ✅ Check batch expiry
                        is_expired = False
                        if batch_row['expiration_date']:
                            expiry = batch_row['expiration_date']
                            if isinstance(expiry, str):
                                expiry = datetime.strptime(expiry, '%Y-%m-%d').date()
                            if expiry < date.today():
                                is_expired = True
                                print(f"⚠️ Batch {batch_row['batch_number']} expired on {expiry}")
                        
                        ungrouped_batches.append({
                            'batch_id': batch_row['batch_id'],
                            'batch_number': batch_row['batch_number'],
                            'product_cost': safe_float(batch_row['product_cost']),
                            'product_price': safe_float(batch_row['product_price']),
                            'remaining_quantity': warehouse_qty,
                            'expiration_date': batch_row['expiration_date'],
                            'created_on': batch_row['created_on'],
                            'warehouse_id': batch_row['warehouse_id'],
                            'warehouse_name': batch_row['warehouse_name'] or 'Unknown Warehouse',
                            'is_expired': is_expired
                        })
                    
                    # ✅ Group batches if requested
                    if group_batches_param and ungrouped_batches:
                        batches_grouped = group_batches(ungrouped_batches)
                        
                        batches = []
                        for grouped_batch in batches_grouped:
                            # ✅ FIX #1: Ensure unique batch IDs
                            unique_batch_ids = list(set(grouped_batch['batch_ids']))
                            
                            batches.append({
                                'batch_id': unique_batch_ids[0],  # Primary batch (FIFO)
                                'batch_ids': unique_batch_ids,    # ✅ Unique IDs only
                                'batch_number': grouped_batch['batch_numbers'][0],
                                'batch_numbers': grouped_batch['batch_numbers'],
                                'batch_count': grouped_batch['batch_count'],
                                'product_cost': grouped_batch['product_cost'],
                                'product_price': grouped_batch['product_price'],
                                'remaining_quantity': grouped_batch['remaining_quantity'],
                                'expiration_date': grouped_batch['expiration_date'].isoformat() if grouped_batch['expiration_date'] else None,
                                'created_on': grouped_batch['created_on'].isoformat() if grouped_batch['created_on'] else None,
                                'warehouse_id': grouped_batch['warehouse_id'],
                                'warehouse_name': grouped_batch['warehouse_name']
                            })
                    else:
                        # No grouping - format individual batches
                        batches = [{
                            'batch_id': b['batch_id'],
                            'batch_ids': [b['batch_id']],
                            'batch_number': b['batch_number'],
                            'batch_numbers': [b['batch_number']],
                            'batch_count': 1,
                            'product_cost': b['product_cost'],
                            'product_price': b['product_price'],
                            'remaining_quantity': b['remaining_quantity'],
                            'expiration_date': b['expiration_date'].isoformat() if b['expiration_date'] else None,
                            'created_on': b['created_on'].isoformat() if b['created_on'] else None,
                            'warehouse_id': b['warehouse_id'],
                            'warehouse_name': b['warehouse_name'],
                            'is_expired': b.get('is_expired', False)
                        } for b in ungrouped_batches]
                    
                    # Calculate total stock across all warehouses
                    stock_query = """
                        SELECT COALESCE(SUM(ws.quantity), 0) as total_stock
                        FROM warehouse_stock ws
                        INNER JOIN product_batches pb ON ws.batch_id = pb.batch_id
                        WHERE pb.product_id = %s AND pb.variation_id IS NULL
                        AND ws.warehouse_id IS NOT NULL
                    """
                    stock_params = [pid]
                    
                    if warehouse_id:
                        stock_query += " AND ws.warehouse_id = %s"
                        stock_params.append(int(warehouse_id))
                    
                    cursor.execute(stock_query, stock_params)
                    total_result = cursor.fetchone()
                    total_stock = safe_float(total_result['total_stock'])
                
                # ✅ FIX #5: Safe display of product info
                products[pid] = {
                    "product_id": pid,
                    "product_name": row['product_name'] or 'Unnamed Product',
                    "sku": row['product_sku'] or 'No SKU',
                    "product_type": row['product_type'],
                    "tax_type": row['tax_type'],
                    "product_tax": safe_float(row['product_tax']),
                    "stock_alert": row['stock_alert'],
                    "expiration_date": row['expiration_date'].isoformat() if row['expiration_date'] else None,
                    "note": row['note'],
                    
                    "brand_id": row['brand_id'],
                    "brand_name": row['brand_name'],
                    "category_id": row['category_id'],
                    "category_name": row['category_name'],
                    "category_discount_type": row['category_discount_type'],
                    "category_discount_value": safe_float(row['category_discount_value']),

                    "base_unit": {
                        "id": row['base_unit_id'],
                        "name": row['unit_name'],
                        "short": row['unit_short'],
                        "base_unit_name": row['base_unit_name']
                    },
                    "sale_unit": {
                        "id": row['sale_unit_id'],
                        "name": row['sale_unit_name'],
                        "short": row['sale_unit_short']
                    },
                    "purchase_unit": {
                        "id": row['purchase_unit_id'],
                        "name": row['purchase_unit_name'],
                        "short": row['purchase_unit_short']
                    },
                    
                    "total_stock": total_stock,
                    "batches": batches,
                    "variations": []
                }

            # Add variation
            if row['variation_id']:
                variation_id = row['variation_id']
                
                # ✅ Build variation batch query
                var_batch_query = """
                    SELECT 
                        pb.batch_id,
                        pb.batch_number,
                        pb.cost AS product_cost,
                        pb.price AS product_price,
                        pb.expiration_date,
                        pb.created_on,
                        ws.warehouse_id,
                        w.warehouse_name,
                        ws.quantity AS warehouse_remaining_qty
                    FROM product_batches pb
                    INNER JOIN warehouse_stock ws ON ws.batch_id = pb.batch_id
                    LEFT JOIN warehouses w ON ws.warehouse_id = w.id
                    WHERE pb.product_id = %s 
                        AND pb.variation_id = %s
                        AND ws.quantity > 0
                        AND ws.warehouse_id IS NOT NULL
                """
                
                var_batch_params = [pid, variation_id]
                
                if warehouse_id:
                    var_batch_query += " AND ws.warehouse_id = %s"
                    var_batch_params.append(int(warehouse_id))
                
                var_batch_query += " ORDER BY ws.warehouse_id, pb.created_on ASC"
                
                cursor.execute(var_batch_query, var_batch_params)
                var_batch_rows = cursor.fetchall()
                
                # Process variation batches
                ungrouped_var_batches = []
                if var_batch_rows:
                    for batch_row in var_batch_rows:
                        warehouse_qty = safe_float(batch_row['warehouse_remaining_qty'])
                        
                        # ✅ FIX #2: Only include batches with stock > 0
                        if warehouse_qty <= 0:
                            continue
                        
                        # ✅ FIX #4: Skip batches without warehouse_id
                        if batch_row['warehouse_id'] is None:
                            print(f"⚠️ Skipping variation batch {batch_row['batch_id']} - no warehouse_id")
                            continue
                        
                        # Check expiry
                        is_expired = False
                        if batch_row['expiration_date']:
                            expiry = batch_row['expiration_date']
                            if isinstance(expiry, str):
                                expiry = datetime.strptime(expiry, '%Y-%m-%d').date()
                            if expiry < date.today():
                                is_expired = True
                        
                        ungrouped_var_batches.append({
                            'batch_id': batch_row['batch_id'],
                            'batch_number': batch_row['batch_number'],
                            'product_cost': safe_float(batch_row['product_cost']),
                            'product_price': safe_float(batch_row['product_price']),
                            'remaining_quantity': warehouse_qty,
                            'expiration_date': batch_row['expiration_date'],
                            'created_on': batch_row['created_on'],
                            'warehouse_id': batch_row['warehouse_id'],
                            'warehouse_name': batch_row['warehouse_name'] or 'Unknown Warehouse',
                            'is_expired': is_expired
                        })
                
                # ✅ Group variation batches
                if group_batches_param and ungrouped_var_batches:
                    variation_batches_grouped = group_batches(ungrouped_var_batches)
                    
                    variation_batches = []
                    for grouped_batch in variation_batches_grouped:
                        # ✅ FIX #1: Ensure unique batch IDs
                        unique_batch_ids = list(set(grouped_batch['batch_ids']))
                        
                        variation_batches.append({
                            'batch_id': unique_batch_ids[0],
                            'batch_ids': unique_batch_ids,
                            'batch_number': grouped_batch['batch_numbers'][0],
                            'batch_numbers': grouped_batch['batch_numbers'],
                            'batch_count': grouped_batch['batch_count'],
                            'product_cost': grouped_batch['product_cost'],
                            'product_price': grouped_batch['product_price'],
                            'remaining_quantity': grouped_batch['remaining_quantity'],
                            'expiration_date': grouped_batch['expiration_date'].isoformat() if grouped_batch['expiration_date'] else None,
                            'created_on': grouped_batch['created_on'].isoformat() if grouped_batch['created_on'] else None,
                            'warehouse_id': grouped_batch['warehouse_id'],
                            'warehouse_name': grouped_batch['warehouse_name']
                        })
                else:
                    variation_batches = [{
                        'batch_id': b['batch_id'],
                        'batch_ids': [b['batch_id']],
                        'batch_number': b['batch_number'],
                        'batch_numbers': [b['batch_number']],
                        'batch_count': 1,
                        'product_cost': b['product_cost'],
                        'product_price': b['product_price'],
                        'remaining_quantity': b['remaining_quantity'],
                        'expiration_date': b['expiration_date'].isoformat() if b['expiration_date'] else None,
                        'created_on': b['created_on'].isoformat() if b['created_on'] else None,
                        'warehouse_id': b['warehouse_id'],
                        'warehouse_name': b['warehouse_name'],
                        'is_expired': b.get('is_expired', False)
                    } for b in ungrouped_var_batches]
                
                # Calculate total stock for variation
                var_stock_query = """
                    SELECT COALESCE(SUM(ws.quantity), 0) as variation_total_stock
                    FROM warehouse_stock ws
                    INNER JOIN product_batches pb ON ws.batch_id = pb.batch_id
                    WHERE pb.product_id = %s AND pb.variation_id = %s
                    AND ws.warehouse_id IS NOT NULL
                """
                var_stock_params = [pid, variation_id]
                
                if warehouse_id:
                    var_stock_query += " AND ws.warehouse_id = %s"
                    var_stock_params.append(int(warehouse_id))
                
                cursor.execute(var_stock_query, var_stock_params)
                var_total_result = cursor.fetchone()
                variation_total_stock = safe_float(var_total_result['variation_total_stock'])
                
                products[pid]['variations'].append({
                    "variation_id": variation_id,
                    "variation_name": row['variation_name'],
                    "variation_type": row['variation_type'],
                    "variation_sku": row['variation_sku'],
                    "product_cost": safe_float(row['variation_cost']),
                    "product_price": safe_float(row['variation_price']),
                    "variation_tax_type": row['variation_tax_type'] or 'exclusive',
                    "variation_tax": safe_float(row['variation_tax']),
                    "variation_stock_alert": row['variation_stock_alert'],
                    "expiration_date": row['variation_expiration_date'].isoformat() if row['variation_expiration_date'] else None,
                    "total_stock": variation_total_stock,
                    "batches": variation_batches
                })
        
        # Calculate total stock for variable products
        for product in products.values():
            if product['product_type'] == 'variable' and product['variations']:
                product['total_stock'] = sum(v['total_stock'] for v in product['variations'])

        print(f"✅ Returning {len(products)} unique products")
        
        return jsonify(list(products.values())), 200

    except Exception as e:
        print("❌ Search Error:", e)
        traceback.print_exc()
        return jsonify({"status": "error", "message": str(e)}), 500

    finally:
        cursor.close()
        conn.close()

# ✅ GET PRODUCT'S ADDED VARIATIONS WITH REMAINING TYPES TO ADD
@product_bp.route('/product/<int:product_id>/variations-with-types', methods=['GET'])
@jwt_required()
def get_product_variations_with_types(product_id):
    conn = None
    cursor = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True, buffered=True)

        # 1️⃣ Check if product exists
        cursor.execute("""
            SELECT id, product_name
            FROM products
            WHERE id = %s
        """, (product_id,))
        product = cursor.fetchone()

        if not product:
            return jsonify({"error": "Product not found"}), 404

        # 2️⃣ Get variation types already added to this product
        cursor.execute("""
            SELECT variation_name, variation_type
            FROM product_variations
            WHERE product_id = %s
        """, (product_id,))

        added_types = cursor.fetchall()
        added_combinations = {(v['variation_name'], v['variation_type']) for v in added_types}

        # 3️⃣ Get ALL variations from master variations table
        cursor.execute("""
            SELECT id, name
            FROM variations
            ORDER BY name
        """)
        all_variations = cursor.fetchall()

        if not all_variations:
            return jsonify({
                "status": True,
                "product": product,
                "variations": [],
                "message": "No variations defined in the system. Please create variations first."
            }), 200

        # 4️⃣ For each variation, get all types and mark added/available
        variations_list = []

        for var in all_variations:
            variation_id = var['id']
            var_name = var['name']

            cursor.execute("""
                SELECT id, type_name, sort_order
                FROM variation_types
                WHERE variation_id = %s
                ORDER BY sort_order
            """, (variation_id,))

            all_types = cursor.fetchall()

            added_types_list = []
            not_added_types_list = []

            for type_row in all_types:
                type_data = {
                    "id": type_row['id'],
                    "type_name": type_row['type_name']
                }

                if (var_name, type_row['type_name']) in added_combinations:
                    added_types_list.append(type_data)
                else:
                    not_added_types_list.append(type_data)

            variations_list.append({
                "id": variation_id,
                "name": var_name,
                "added_types": added_types_list,
                "available_types": not_added_types_list
            })

        # 5️⃣ Return final response
        return jsonify({
            "status": True,
            "product": product,
            "variations": variations_list
        }), 200

    except mysql.connector.Error as err:
        if conn:
            conn.rollback()
        print("Database Error:", err)
        traceback.print_exc()
        return jsonify({"error": f"Database error: {str(err)}"}), 500

    except Exception as e:
        if conn:
            conn.rollback()
        print("Server Error:", e)
        traceback.print_exc()
        return jsonify({"error": f"Server error: {str(e)}"}), 500

    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


@product_bp.route('/product/<int:product_id>/add-variation', methods=['POST'])
@jwt_required()
@role_required('admin')
def add_variation_to_product(product_id):
    """
    Add a new variation to a variable product.
    Creates ONLY variation record - NO warehouse stock or batches.
    
    Stock is added separately through purchase orders or stock adjustments.
    """
    data = request.get_json()

    # Validate required fields
    required_fields = [
        'variation_name',
        'variation_type',
        'variation_sku',
        'variation_cost',
        'variation_price',
        'variation_tax_type'
    ]

    missing = [f for f in required_fields if data.get(f) is None or data.get(f) == '']
    if missing:
        return jsonify({'error': f"Missing required fields: {', '.join(missing)}"}), 400

    # Optional fields with defaults
    variation_tax = float(data.get('variation_tax', 0.0))
    variation_stock_alert = int(data.get('variation_stock_alert', 0))
    expiration_date = data.get('expiration_date') or None

    conn = get_db_connection()
    if conn is None:
        return jsonify({'error': 'Database connection failed'}), 500

    cursor = conn.cursor(dictionary=True)

    try:
        # Check if product exists and is variable type
        cursor.execute("""
            SELECT id, product_type, product_name 
            FROM products 
            WHERE id = %s
        """, (product_id,))
        product = cursor.fetchone()

        if not product:
            return jsonify({'error': 'Product not found'}), 404

        if product['product_type'] != 'variable':
            return jsonify({'error': 'Cannot add variations to non-variable products'}), 400

        # Check if this exact variation combination already exists
        cursor.execute("""
            SELECT id FROM product_variations 
            WHERE product_id = %s 
            AND variation_name = %s 
            AND variation_type = %s
        """, (product_id, data['variation_name'], data['variation_type']))
        
        if cursor.fetchone():
            return jsonify({
                'error': f"Variation '{data['variation_name']} - {data['variation_type']}' already exists for this product"
            }), 409

        # ✅ GLOBAL check — ඕනෑම product එකක same variation SKU block කරනවා
        cursor.execute("""
            SELECT pv.id, p.product_name 
            FROM product_variations pv
            JOIN products p ON pv.product_id = p.id
            WHERE pv.variation_sku = %s
        """, (data['variation_sku'],))

        sku_conflict = cursor.fetchone()
        if sku_conflict:
            return jsonify({
                'error': f"Variation SKU '{data['variation_sku']}' already exists in product '{sku_conflict['product_name']}'"
            }), 409

        # Insert the variation (ONLY variation record, no stock)
        cursor.execute("""
            INSERT INTO product_variations (
                product_id, 
                variation_name, 
                variation_type,
                variation_sku,
                variation_cost, 
                variation_price,
                variation_tax_type, 
                variation_tax, 
                variation_stock_alert, 
                expiration_date,
                created_at,
                updated_at
            ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW(), NOW())
        """, (
            product_id,
            data['variation_name'],
            data['variation_type'],
            data['variation_sku'],
            float(data['variation_cost']),
            float(data['variation_price']),
            data['variation_tax_type'],
            variation_tax,
            variation_stock_alert,
            expiration_date
        ))

        variation_id = cursor.lastrowid

        conn.commit()

        return jsonify({
            'success': True,
            'message': f"Variation '{data['variation_name']} - {data['variation_type']}' added successfully!",
            'data': {
                'variation_id': variation_id,
                'product_id': product_id,
                'product_name': product['product_name'],
                'variation_name': data['variation_name'],
                'variation_type': data['variation_type'],
                'variation_sku': data['variation_sku'],
                'variation_cost': float(data['variation_cost']),
                'variation_price': float(data['variation_price']),
                'variation_tax_type': data['variation_tax_type'],
                'variation_tax': variation_tax,
                'variation_stock_alert': variation_stock_alert,
                'expiration_date': expiration_date,
                'initial_stock': 0,
                'note': 'Add stock through Purchase Orders or Stock Adjustments'
            }
        }), 201

    except mysql.connector.Error as err:
        conn.rollback()
        print(f"Database Error adding variation: {err}")
        traceback.print_exc()
        return jsonify({'error': f'Database error: {str(err)}'}), 500

    except Exception as err:
        conn.rollback()
        print(f"Error adding variation: {err}")
        traceback.print_exc()
        return jsonify({'error': 'Failed to add variation'}), 500

    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()
            
@product_bp.route('/delete_variations/<int:variation_id>', methods=['DELETE'])
@jwt_required()
@role_required('admin')
def delete_variations(variation_id):
    """
    Delete a product variation and all related data:
    - Product batches
    - Warehouse stock
    - Purchase order items (optional - might need to keep for history)
    
    Note: This is a destructive operation. Consider soft delete for production.
    """
    conn = get_db_connection()
    if conn is None:
        return jsonify({'error': 'Database connection failed'}), 500
    
    cursor = conn.cursor(dictionary=True)

    try:
        # 1️⃣ Check if the variation exists
        cursor.execute("""
            SELECT 
                pv.id, 
                pv.product_id,
                pv.variation_name,
                pv.variation_type,
                pv.variation_sku,
                p.product_name
            FROM product_variations pv
            JOIN products p ON pv.product_id = p.id
            WHERE pv.id = %s
        """, (variation_id,))
        
        variation = cursor.fetchone()

        if not variation:
            return jsonify({'error': 'Variation not found'}), 404

        product_id = variation['product_id']
        variation_info = f"{variation['variation_name']} - {variation['variation_type']}"

        # 2️⃣ Check if variation has stock in any warehouse
        cursor.execute("""
            SELECT IFNULL(SUM(quantity), 0) as total_stock
            FROM warehouse_stock
            WHERE product_id = %s AND variation_id = %s
        """, (product_id, variation_id))
        
        stock_result = cursor.fetchone()
        total_stock = float(stock_result['total_stock']) if stock_result else 0.0

        if total_stock > 0:
            return jsonify({
                'error': f'Cannot delete variation with existing stock. Current stock: {total_stock}. Please remove stock first through stock adjustments.'
            }), 400

        # 3️⃣ Check if variation is used in any pending purchase orders
        cursor.execute("""
            SELECT COUNT(*) as order_count
            FROM order_items oi
            JOIN purchase_orders po ON oi.order_id = po.order_id
            WHERE oi.variation_id = %s 
            AND po.status IN ('pending', 'ordered')
        """, (variation_id,))
        
        order_result = cursor.fetchone()
        pending_orders = order_result['order_count'] if order_result else 0

        if pending_orders > 0:
            return jsonify({
                'error': f'Cannot delete variation used in {pending_orders} pending purchase order(s). Complete, receive, or cancel orders first.'
            }), 400

        # 4️⃣ Delete related warehouse stock (should be 0 at this point)
        cursor.execute("""
            DELETE FROM warehouse_stock 
            WHERE product_id = %s AND variation_id = %s
        """, (product_id, variation_id))
        
        deleted_stock = cursor.rowcount
        print(f"✅ Deleted {deleted_stock} warehouse_stock records")

        # 5️⃣ Delete related product batches
        cursor.execute("""
            DELETE FROM product_batches 
            WHERE product_id = %s AND variation_id = %s
        """, (product_id, variation_id))
        
        deleted_batches = cursor.rowcount
        print(f"✅ Deleted {deleted_batches} product_batches records")

        # 6️⃣ Handle order_items - Keep for completed orders (history)
        # Only delete from cancelled/draft orders if necessary
        cursor.execute("""
            SELECT COUNT(*) as completed_order_count
            FROM order_items oi
            JOIN purchase_orders po ON oi.order_id = po.order_id
            WHERE oi.variation_id = %s 
            AND po.status IN ('received', 'completed')
        """, (variation_id,))
        
        completed_result = cursor.fetchone()
        completed_orders = completed_result['completed_order_count'] if completed_result else 0

        if completed_orders > 0:
            # Don't delete - keep for history
            print(f"⚠️ Keeping {completed_orders} order_items in completed orders for history")
            deleted_order_items = 0
        else:
            # No completed orders - safe to delete
            cursor.execute("""
                DELETE FROM order_items 
                WHERE variation_id = %s
            """, (variation_id,))
            deleted_order_items = cursor.rowcount
            print(f"✅ Deleted {deleted_order_items} order_items records")

        # 7️⃣ Delete the variation itself
        cursor.execute("""
            DELETE FROM product_variations 
            WHERE id = %s
        """, (variation_id,))

        if cursor.rowcount == 0:
            conn.rollback()
            return jsonify({'error': 'Failed to delete variation'}), 500

        # 8️⃣ Check remaining variations count
        cursor.execute("""
            SELECT COUNT(*) as remaining_count
            FROM product_variations
            WHERE product_id = %s
        """, (product_id,))
        
        remaining_result = cursor.fetchone()
        remaining_variations = remaining_result['remaining_count'] if remaining_result else 0

        # 9️⃣ If no variations left and product is variable type, warn user
        warning_message = None
        if remaining_variations == 0:
            cursor.execute("""
                SELECT product_type FROM products WHERE id = %s
            """, (product_id,))
            product_result = cursor.fetchone()
            if product_result and product_result['product_type'] == 'variable':
                warning_message = "⚠️ Warning: This was the last variation. Consider converting product to 'single' type or adding new variations."

        conn.commit()

        response_data = {
            'success': True,
            'message': f"✅ Variation '{variation_info}' deleted successfully!",
            'data': {
                'deleted_variation_id': variation_id,
                'product_id': product_id,
                'product_name': variation['product_name'],
                'variation_info': variation_info,
                'variation_sku': variation['variation_sku'],
                'remaining_variations': remaining_variations,
                'deleted_records': {
                    'batches': deleted_batches,
                    'warehouse_stock': deleted_stock,
                    'order_items': deleted_order_items
                },
                'completed_orders_kept': completed_orders
            }
        }

        if warning_message:
            response_data['warning'] = warning_message

        return jsonify(response_data), 200

    except mysql.connector.Error as err:
        conn.rollback()
        print(f"❌ Database Error deleting variation: {err}")
        traceback.print_exc()
        return jsonify({'error': f'Database error: {str(err)}'}), 500

    except Exception as e:
        conn.rollback()
        print(f"❌ Error deleting variation: {e}")
        traceback.print_exc()
        return jsonify({'error': f'Failed to delete variation: {str(e)}'}), 500

    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()
            
            
@product_bp.route('/search_vproducts', methods=['GET'])
@jwt_required()
@role_required('admin', 'user', 'doctor')
def search_vproducts():
    query = request.args.get('query')
    if not query:
        return jsonify({"status": "error", "message": "Query parameter is required"}), 400

    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    try:
        like_query = f"%{query}%"

        cursor.execute("""
            SELECT
                p.id AS product_id,
                p.product_name,
                p.sku AS product_sku,
                p.product_type,
                p.product_quantity,
                p.product_price,
                p.product_cost,
                p.tax_type,
                p.product_tax,
                p.sales_unit,
                pv.id AS variation_id,
                pv.variation_name,
                pv.variation_sku,
                pv.variation_quantity,
                pv.variation_price,
                pv.variation_cost,
                pv.variation_tax_type,
                pv.variation_tax
            FROM products p
            LEFT JOIN product_variations pv ON pv.product_id = p.id
            WHERE
                (
                    LOWER(p.product_name) LIKE LOWER(%s) OR
                    LOWER(p.sku) LIKE LOWER(%s) OR
                    LOWER(pv.variation_name) LIKE LOWER(%s) OR
                    LOWER(pv.variation_sku) LIKE LOWER(%s)
                )
                AND (
                    EXISTS (
                        SELECT 1
                        FROM product_batches b
                        JOIN purchase_orders po ON po.order_id = b.purchase_order_id
                        WHERE 
                            (b.product_id = p.id OR b.variation_id = pv.id)
                            AND po.status = 'Received'
                            AND b.remaining_quantity > 0
                    )
                )
            ORDER BY 
                CASE
                    WHEN LOWER(p.product_name) = LOWER(%s) THEN 1
                    WHEN LOWER(p.sku) = LOWER(%s) THEN 1
                    WHEN LOWER(pv.variation_name) = LOWER(%s) THEN 1
                    WHEN LOWER(pv.variation_sku) = LOWER(%s) THEN 1
                    ELSE 2
                END,
                p.product_name ASC
            LIMIT 100
        """, (like_query, like_query, like_query, like_query,
              query, query, query, query))

        rows = cursor.fetchall()
        results = []

        for row in rows:
            if row['variation_id']:
                results.append({
                    "product_id": row['product_id'],
                    "variation_id": row['variation_id'],
                    "product_name": row['product_name'],
                    "variation_name": row['variation_name'],
                    "sku": row['variation_sku'],
                    "display_name": (
                        f"{row['product_name']} - {row['variation_name']} ({row['variation_sku']})"
                        if row['variation_name'] else
                        f"{row['product_name']} ({row['product_sku']})"
                    ),
                    "product_type": "variation",
                    "product_quantity": row['variation_quantity'],
                    "product_price": float(row['variation_price']),
                    "product_cost": float(row['variation_cost']),
                    "tax_type": row.get('variation_tax_type'),
                    "product_tax": float(row.get('variation_tax', 0)),
                    "sales_unit": row['sales_unit']
                })
            else:
                results.append({
                    "product_id": row['product_id'],
                    "variation_id": None,
                    "product_name": row['product_name'],
                    "variation_name": None,
                    "sku": row['product_sku'],
                    "display_name": f"{row['product_name']} ({row['product_sku']})",
                    "product_type": "single",
                    "product_quantity": row['product_quantity'],
                    "product_price": float(row['product_price']),
                    "product_cost": float(row['product_cost']),
                    "tax_type": row['tax_type'],
                    "product_tax": float(row['product_tax']),
                    "sales_unit": row['sales_unit']
                })

        return jsonify(results), 200

    except Exception as e:
        print("Error:", e)
        return jsonify({"status": "error", "message": str(e)}), 500

    finally:
        cursor.close()
        conn.close()

# @product_bp.route('/search_sales_products', methods=['GET'])
# @jwt_required()
# @role_required('admin', 'cashier')
# def search_sales_products():
#     """
#     Search products and variations with warehouse-specific batch-based pricing and stock.
#     Returns products available in the selected warehouse only.
#     ✅ GROUPS batches by cost, price, and our_price - shows combined stock with latest expiration date
#     ✅ INCLUDES our_price from product_batches table
#     ✅ INCLUDES GRN information for traceability
#     ✅ FIXED: Shows decimal quantities properly (0.25, 0.5, etc.)
#     ✅ FIXED: Variation products now searchable by product name
#     """
#     # Accept multiple possible parameters
#     query = request.args.get('query', '').strip()
#     productname = request.args.get('productname', '').strip()
#     variation = request.args.get('variation', '').strip()
#     warehouse_id = request.args.get('warehouse_id', '').strip()
#     store_id = request.args.get('store_id', '').strip()

#     # Use whichever parameter is provided
#     search_term = query or productname or variation

#     if not search_term:
#         return jsonify({"status": "error", "message": "Query parameter is required"}), 400

#     # Detect combined format like "Product - Variation"
#     main_name = None
#     variation_name = None
#     if " - " in search_term:
#         parts = search_term.split(" - ", 1)
#         main_name = parts[0].strip()
#         variation_name = parts[1].strip()
#     else:
#         main_name = search_term
#         variation_name = None  # ✅ FIXED: Don't assume variation_name = main_name

#     conn = get_db_connection()
#     if conn is None:
#         return jsonify({'error': 'Database connection failed'}), 500
        
#     cursor = conn.cursor(dictionary=True)
#     result = []

#     try:
#         # ============================================
#         # SEARCH PRODUCTS WITH UNIT INFORMATION
#         # ============================================
#         cursor.execute("""
#             SELECT 
#                 p.id,
#                 p.product_name,
#                 p.sku,
#                 p.product_type,
#                 p.tax_type,
#                 p.product_tax,
#                 p.base_unit_id,
#                 p.sale_unit_id,
#                 p.purchase_unit_id,
#                 u1.unit_name AS base_unit_name,
#                 u1.unit_short AS base_unit_short,
#                 u2.unit_name AS sale_unit_name,
#                 u2.unit_short AS sale_unit_short,
#                 u3.unit_name AS purchase_unit_name,
#                 u3.unit_short AS purchase_unit_short
#             FROM products p
#             LEFT JOIN units u1 ON p.base_unit_id = u1.id
#             LEFT JOIN units u2 ON p.sale_unit_id = u2.id
#             LEFT JOIN units u3 ON p.purchase_unit_id = u3.id
#             WHERE p.product_name LIKE %s OR p.sku LIKE %s
#         """, (f"%{main_name}%", f"%{main_name}%"))
#         products = cursor.fetchall()

#         for product in products:
#             # ============================================
#             # VARIABLE PRODUCTS (with variations)
#             # ============================================
#             if product['product_type'] == 'variable':
#                 # ✅ FIXED: If variation_name is provided, filter by it; otherwise show ALL variations
#                 if variation_name:
#                     # User searched for specific variation (e.g., "MILK - 1L")
#                     cursor.execute("""
#                         SELECT 
#                             pv.id,
#                             pv.variation_name,
#                             pv.variation_type,
#                             pv.variation_sku,
#                             pv.variation_cost,
#                             pv.variation_price,
#                             pv.variation_tax_type,
#                             pv.variation_tax
#                         FROM product_variations pv
#                         WHERE pv.product_id = %s
#                         AND (pv.variation_name LIKE %s 
#                              OR pv.variation_type LIKE %s 
#                              OR pv.variation_sku LIKE %s)
#                     """, (product['id'], f"%{variation_name}%", f"%{variation_name}%", f"%{variation_name}%"))
#                 else:
#                     # User searched only by product name (e.g., "MILK") - show ALL variations
#                     cursor.execute("""
#                         SELECT 
#                             pv.id,
#                             pv.variation_name,
#                             pv.variation_type,
#                             pv.variation_sku,
#                             pv.variation_cost,
#                             pv.variation_price,
#                             pv.variation_tax_type,
#                             pv.variation_tax
#                         FROM product_variations pv
#                         WHERE pv.product_id = %s
#                     """, (product['id'],))
                
#                 variations = cursor.fetchall()

#                 for var in variations:
#                     # ✅ GET BATCHES GROUPED BY COST, PRICE, AND OUR_PRICE - WITH GRN INFO
#                     if warehouse_id and store_id:
#                         # Filter by both warehouse and store
#                         cursor.execute("""
#                             SELECT 
#                                 pb.price,
#                                 pb.cost,
#                                 pb.our_price,
#                                 MAX(pb.expiration_date) AS latest_expiration_date,
#                                 SUM(ws.quantity) AS total_stock,
#                                 GROUP_CONCAT(DISTINCT pb.batch_id ORDER BY pb.batch_id SEPARATOR ',') AS batch_ids,
#                                 GROUP_CONCAT(DISTINCT CONCAT(g.grn_code, ' (', DATE_FORMAT(g.grn_date, '%Y-%m-%d'), ')') SEPARATOR ', ') AS grn_info
#                             FROM product_batches pb
#                             INNER JOIN warehouse_stock ws ON pb.batch_id = ws.batch_id
#                             LEFT JOIN grn g ON pb.grn_id = g.grn_id
#                             WHERE pb.product_id = %s 
#                             AND pb.variation_id = %s 
#                             AND pb.remaining_quantity > 0
#                             AND ws.warehouse_id = %s
#                             AND ws.store_id = %s
#                             AND ws.quantity > 0
#                             GROUP BY pb.price, pb.cost, pb.our_price
#                             ORDER BY pb.price ASC, pb.cost ASC
#                         """, (product['id'], var['id'], warehouse_id, store_id))
#                     elif warehouse_id:
#                         # Filter by warehouse only
#                         cursor.execute("""
#                             SELECT 
#                                 pb.price,
#                                 pb.cost,
#                                 pb.our_price,
#                                 MAX(pb.expiration_date) AS latest_expiration_date,
#                                 SUM(ws.quantity) AS total_stock,
#                                 GROUP_CONCAT(DISTINCT pb.batch_id ORDER BY pb.batch_id SEPARATOR ',') AS batch_ids,
#                                 GROUP_CONCAT(DISTINCT CONCAT(g.grn_code, ' (', DATE_FORMAT(g.grn_date, '%Y-%m-%d'), ')') SEPARATOR ', ') AS grn_info
#                             FROM product_batches pb
#                             INNER JOIN warehouse_stock ws ON pb.batch_id = ws.batch_id
#                             LEFT JOIN grn g ON pb.grn_id = g.grn_id
#                             WHERE pb.product_id = %s 
#                             AND pb.variation_id = %s 
#                             AND pb.remaining_quantity > 0
#                             AND ws.warehouse_id = %s
#                             AND ws.quantity > 0
#                             GROUP BY pb.price, pb.cost, pb.our_price
#                             ORDER BY pb.price ASC, pb.cost ASC
#                         """, (product['id'], var['id'], warehouse_id))
#                     else:
#                         # No warehouse filter - show all stock grouped by price, cost, and our_price
#                         cursor.execute("""
#                             SELECT 
#                                 price,
#                                 cost,
#                                 our_price,
#                                 MAX(expiration_date) AS latest_expiration_date,
#                                 SUM(remaining_quantity) AS total_stock,
#                                 GROUP_CONCAT(DISTINCT batch_id ORDER BY batch_id SEPARATOR ',') AS batch_ids,
#                                 GROUP_CONCAT(DISTINCT CONCAT(g.grn_code, ' (', DATE_FORMAT(g.grn_date, '%Y-%m-%d'), ')') SEPARATOR ', ') AS grn_info
#                             FROM product_batches pb
#                             LEFT JOIN grn g ON pb.grn_id = g.grn_id
#                             WHERE pb.product_id = %s 
#                             AND pb.variation_id = %s 
#                             AND pb.remaining_quantity > 0
#                             GROUP BY pb.price, pb.cost, pb.our_price
#                             ORDER BY pb.price ASC, pb.cost ASC
#                         """, (product['id'], var['id']))
                    
#                     batches = cursor.fetchall()

#                     # Default values from variation
#                     default_price = float(var['variation_price']) if var['variation_price'] else 0.0
#                     default_cost = float(var['variation_cost']) if var['variation_cost'] else 0.0
#                     default_our_price = 0.0

#                     if batches:
#                         # ✅ CREATE ENTRY FOR EACH UNIQUE COST/PRICE/OUR_PRICE COMBINATION
#                         for batch_group in batches:
#                             price = float(batch_group['price']) if batch_group['price'] else default_price
#                             cost = float(batch_group['cost']) if batch_group['cost'] else default_cost
#                             our_price = float(batch_group['our_price']) if batch_group['our_price'] else default_our_price
#                             stock = float(batch_group['total_stock']) if batch_group['total_stock'] else 0.0
#                             exp_date = batch_group['latest_expiration_date'].strftime('%Y-%m-%d') if batch_group['latest_expiration_date'] else '-'
#                             batch_ids = batch_group['batch_ids']
#                             grn_info = batch_group.get('grn_info', 'N/A')  # ✅ GRN info
                            
#                             # Use variation tax if available, otherwise product tax
#                             tax_type = var.get('variation_tax_type') or product.get('tax_type')
#                             product_tax = float(var.get('variation_tax', 0)) if var.get('variation_tax') else float(product.get('product_tax', 0))
                            
#                             # ✅ FIXED: Format quantity with decimals (removes trailing zeros)
#                             stock_display = f"{stock:.2f}".rstrip('0').rstrip('.') if stock % 1 else f"{int(stock)}"
                            
#                             # ✅ Format with our_price: MILK - 1L Pack - (Cost 120 - Our Price 140 - Price 150) Stock 200 Exp: 2025-01-15
#                             display_name = f"{product['product_name']} - {var['variation_name']} - (Cost {cost:.0f} - Our Price {our_price:.0f} - Price {price:.0f}) Stock {stock_display} Exp: {exp_date}"
                            
#                             result.append({
#                                 "product_id": product['id'],
#                                 "variation_id": var['id'],
#                                 "batch_ids": batch_ids,
#                                 "grn_info": grn_info,
#                                 "product_name": product['product_name'],
#                                 "variation_name": var['variation_name'],
#                                 "variation_type": var['variation_type'],
#                                 "sku": var['variation_sku'],
#                                 "display_name": display_name,
#                                 "product_type": "variation",
#                                 "product_quantity": stock,
#                                 "product_price": price,
#                                 "product_cost": cost,
#                                 "our_price": our_price,  # ✅ NEW FIELD
#                                 "expiration_date": exp_date,
#                                 "tax_type": tax_type,
#                                 "product_tax": product_tax,
#                                 "base_unit": product.get('base_unit_name'),
#                                 "sale_unit": product.get('sale_unit_name'),
#                                 "purchase_unit": product.get('purchase_unit_name'),
#                                 "sales_unit": product.get('sale_unit_id'),
#                                 "sales_units": [
#                                     {"id": product.get('base_unit_id'), "name": product.get('base_unit_name')},
#                                     {"id": product.get('sale_unit_id'), "name": product.get('sale_unit_name')},
#                                     {"id": product.get('purchase_unit_id'), "name": product.get('purchase_unit_name')}
#                                 ]
#                             })
#                     else:
#                         # ❌ No stock in warehouse
#                         display_name = f"{product['product_name']} - {var['variation_name']} - (Cost {default_cost:.0f} - Our Price 0 - Price {default_price:.0f}) Stock 0 Exp: -"
                        
#                         result.append({
#                             "product_id": product['id'],
#                             "variation_id": var['id'],
#                             "batch_ids": None,
#                             "grn_info": None,
#                             "product_name": product['product_name'],
#                             "variation_name": var['variation_name'],
#                             "variation_type": var['variation_type'],
#                             "sku": var['variation_sku'],
#                             "display_name": display_name,
#                             "product_type": "variation",
#                             "product_quantity": 0.0,
#                             "product_price": default_price,
#                             "product_cost": default_cost,
#                             "our_price": 0.0,  # ✅ NEW FIELD
#                             "expiration_date": '-',
#                             "tax_type": var.get('variation_tax_type') or product.get('tax_type'),
#                             "product_tax": float(var.get('variation_tax', 0)) if var.get('variation_tax') else float(product.get('product_tax', 0)),
#                             "base_unit": product.get('base_unit_name'),
#                             "sale_unit": product.get('sale_unit_name'),
#                             "purchase_unit": product.get('purchase_unit_name'),
#                             "sales_unit": product.get('sale_unit_id'),
#                             "sales_units": [
#                                 {"id": product.get('base_unit_id'), "name": product.get('base_unit_name')},
#                                 {"id": product.get('sale_unit_id'), "name": product.get('sale_unit_name')},
#                                 {"id": product.get('purchase_unit_id'), "name": product.get('purchase_unit_name')}
#                             ]
#                         })
            
#             # ============================================
#             # SINGLE PRODUCTS (no variations)
#             # ============================================
#             else:
#                 # Match if search term matches product name or SKU
#                 if (main_name.lower() in product['product_name'].lower()) or \
#                    (main_name.lower() in (product['sku'] or '').lower()):
                    
#                     # ✅ GET BATCHES GROUPED BY COST, PRICE, AND OUR_PRICE - WITH GRN INFO
#                     if warehouse_id and store_id:
#                         # Filter by both warehouse and store
#                         cursor.execute("""
#                             SELECT 
#                                 pb.price,
#                                 pb.cost,
#                                 pb.our_price,
#                                 MAX(pb.expiration_date) AS latest_expiration_date,
#                                 SUM(ws.quantity) AS total_stock,
#                                 GROUP_CONCAT(DISTINCT pb.batch_id ORDER BY pb.batch_id SEPARATOR ',') AS batch_ids,
#                                 GROUP_CONCAT(DISTINCT CONCAT(g.grn_code, ' (', DATE_FORMAT(g.grn_date, '%Y-%m-%d'), ')') SEPARATOR ', ') AS grn_info
#                             FROM product_batches pb
#                             INNER JOIN warehouse_stock ws ON pb.batch_id = ws.batch_id
#                             LEFT JOIN grn g ON pb.grn_id = g.grn_id
#                             WHERE pb.product_id = %s 
#                             AND pb.variation_id IS NULL 
#                             AND pb.remaining_quantity > 0
#                             AND ws.warehouse_id = %s
#                             AND ws.store_id = %s
#                             AND ws.quantity > 0
#                             GROUP BY pb.price, pb.cost, pb.our_price
#                             ORDER BY pb.price ASC, pb.cost ASC
#                         """, (product['id'], warehouse_id, store_id))
#                     elif warehouse_id:
#                         # Filter by warehouse only
#                         cursor.execute("""
#                             SELECT 
#                                 pb.price,
#                                 pb.cost,
#                                 pb.our_price,
#                                 MAX(pb.expiration_date) AS latest_expiration_date,
#                                 SUM(ws.quantity) AS total_stock,
#                                 GROUP_CONCAT(DISTINCT pb.batch_id ORDER BY pb.batch_id SEPARATOR ',') AS batch_ids,
#                                 GROUP_CONCAT(DISTINCT CONCAT(g.grn_code, ' (', DATE_FORMAT(g.grn_date, '%Y-%m-%d'), ')') SEPARATOR ', ') AS grn_info
#                             FROM product_batches pb
#                             INNER JOIN warehouse_stock ws ON pb.batch_id = ws.batch_id
#                             LEFT JOIN grn g ON pb.grn_id = g.grn_id
#                             WHERE pb.product_id = %s 
#                             AND pb.variation_id IS NULL 
#                             AND pb.remaining_quantity > 0
#                             AND ws.warehouse_id = %s
#                             AND ws.quantity > 0
#                             GROUP BY pb.price, pb.cost, pb.our_price
#                             ORDER BY pb.price ASC, pb.cost ASC
#                         """, (product['id'], warehouse_id))
#                     else:
#                         # No warehouse filter - show all stock
#                         cursor.execute("""
#                             SELECT 
#                                 price,
#                                 cost,
#                                 our_price,
#                                 MAX(expiration_date) AS latest_expiration_date,
#                                 SUM(remaining_quantity) AS total_stock,
#                                 GROUP_CONCAT(DISTINCT batch_id ORDER BY batch_id SEPARATOR ',') AS batch_ids,
#                                 GROUP_CONCAT(DISTINCT CONCAT(g.grn_code, ' (', DATE_FORMAT(g.grn_date, '%Y-%m-%d'), ')') SEPARATOR ', ') AS grn_info
#                             FROM product_batches pb
#                             LEFT JOIN grn g ON pb.grn_id = g.grn_id
#                             WHERE pb.product_id = %s 
#                             AND pb.variation_id IS NULL 
#                             AND pb.remaining_quantity > 0
#                             GROUP BY pb.price, pb.cost, pb.our_price
#                             ORDER BY pb.price ASC, pb.cost ASC
#                         """, (product['id'],))
                    
#                     batches = cursor.fetchall()

#                     if batches:
#                         # ✅ CREATE ENTRY FOR EACH UNIQUE COST/PRICE/OUR_PRICE COMBINATION
#                         for batch_group in batches:
#                             price = float(batch_group['price']) if batch_group['price'] else 0.0
#                             cost = float(batch_group['cost']) if batch_group['cost'] else 0.0
#                             our_price = float(batch_group['our_price']) if batch_group['our_price'] else 0.0
#                             stock = float(batch_group['total_stock']) if batch_group['total_stock'] else 0.0
#                             exp_date = batch_group['latest_expiration_date'].strftime('%Y-%m-%d') if batch_group['latest_expiration_date'] else '-'
#                             batch_ids = batch_group['batch_ids']
#                             grn_info = batch_group.get('grn_info', 'N/A')
                            
#                             # ✅ FIXED: Format quantity with decimals
#                             stock_display = f"{stock:.2f}".rstrip('0').rstrip('.') if stock % 1 else f"{int(stock)}"
                            
#                             # ✅ Format with our_price: MILK (MILK) - (Cost 120 - Our Price 140 - Price 150) Stock 200 Exp: 2025-01-15
#                             display_name = f"{product['product_name']} ({product['sku']}) - (Cost {cost:.0f} - Our Price {our_price:.0f} - Price {price:.0f}) Stock {stock_display} Exp: {exp_date}"
                            
#                             result.append({
#                                 "product_id": product['id'],
#                                 "variation_id": None,
#                                 "batch_ids": batch_ids,
#                                 "grn_info": grn_info,
#                                 "product_name": product['product_name'],
#                                 "variation_name": None,
#                                 "variation_type": None,
#                                 "sku": product['sku'],
#                                 "display_name": display_name,
#                                 "product_type": "single",
#                                 "product_quantity": stock,
#                                 "product_price": price,
#                                 "product_cost": cost,
#                                 "our_price": our_price,  # ✅ NEW FIELD
#                                 "expiration_date": exp_date,
#                                 "tax_type": product['tax_type'],
#                                 "product_tax": float(product['product_tax']) if product['product_tax'] else 0.0,
#                                 "base_unit": product.get('base_unit_name'),
#                                 "sale_unit": product.get('sale_unit_name'),
#                                 "purchase_unit": product.get('purchase_unit_name'),
#                                 "sales_unit": product.get('sale_unit_id'),
#                                 "sales_units": [
#                                     {"id": product.get('base_unit_id'), "name": product.get('base_unit_name')},
#                                     {"id": product.get('sale_unit_id'), "name": product.get('sale_unit_name')},
#                                     {"id": product.get('purchase_unit_id'), "name": product.get('purchase_unit_name')}
#                                 ]
#                             })
#                     else:
#                         # ❌ No stock in warehouse
#                         display_name = f"{product['product_name']} ({product['sku']}) - (Cost 0 - Our Price 0 - Price 0) Stock 0 Exp: -"
                        
#                         result.append({
#                             "product_id": product['id'],
#                             "variation_id": None,
#                             "batch_ids": None,
#                             "grn_info": None,
#                             "product_name": product['product_name'],
#                             "variation_name": None,
#                             "variation_type": None,
#                             "sku": product['sku'],
#                             "display_name": display_name,
#                             "product_type": "single",
#                             "product_quantity": 0.0,
#                             "product_price": 0.0,
#                             "product_cost": 0.0,
#                             "our_price": 0.0,  # ✅ NEW FIELD
#                             "expiration_date": '-',
#                             "tax_type": product['tax_type'],
#                             "product_tax": float(product['product_tax']) if product['product_tax'] else 0.0,
#                             "base_unit": product.get('base_unit_name'),
#                             "sale_unit": product.get('sale_unit_name'),
#                             "purchase_unit": product.get('purchase_unit_name'),
#                             "sales_unit": product.get('sale_unit_id'),
#                             "sales_units": [
#                                 {"id": product.get('base_unit_id'), "name": product.get('base_unit_name')},
#                                 {"id": product.get('sale_unit_id'), "name": product.get('sale_unit_name')},
#                                 {"id": product.get('purchase_unit_id'), "name": product.get('purchase_unit_name')}
#                             ]
#                         })

#         return jsonify(result), 200

#     except mysql.connector.Error as err:
#         print(f"❌ Database Error in search_sales_products: {err}")
#         traceback.print_exc()
#         return jsonify({"status": "error", "message": f"Database error: {str(err)}"}), 500
    
#     except Exception as e:
#         print(f"❌ Error in search_sales_products: {e}")
#         traceback.print_exc()
#         return jsonify({"status": "error", "message": str(e)}), 500

#     finally:
#         if cursor:
#             cursor.close()
#         if conn:
#             conn.close()



@product_bp.route('/search_sales_products', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier')
def search_sales_products():
    """
    Search products and variations with warehouse-specific batch-based pricing and stock.
    Returns products available in the selected warehouse only.
    ✅ GROUPS batches by cost, price, and our_price - shows combined stock with latest expiration date
    ✅ INCLUDES our_price from product_batches table
    ✅ INCLUDES GRN information for traceability
    ✅ FIXED: Shows decimal quantities properly (0.25, 0.5, etc.)
    ✅ FIXED: Variation products now searchable by product name
    ✅ FIXED: Variation SKU search now works correctly
    """
    # Accept multiple possible parameters
    query = request.args.get('query', '').strip()
    productname = request.args.get('productname', '').strip()
    variation = request.args.get('variation', '').strip()
    warehouse_id = request.args.get('warehouse_id', '').strip()
    store_id = request.args.get('store_id', '').strip()

    # Use whichever parameter is provided
    search_term = query or productname or variation

    if not search_term:
        return jsonify({"status": "error", "message": "Query parameter is required"}), 400

    # Detect combined format like "Product - Variation"
    main_name = None
    variation_name = None
    if " - " in search_term:
        parts = search_term.split(" - ", 1)
        main_name = parts[0].strip()
        variation_name = parts[1].strip()
    else:
        main_name = search_term
        variation_name = None

    conn = get_db_connection()
    if conn is None:
        return jsonify({'error': 'Database connection failed'}), 500

    cursor = conn.cursor(dictionary=True)
    result = []

    try:
        # ============================================
        # SEARCH PRODUCTS WITH UNIT INFORMATION
        # ✅ FIXED: Now also joins product_variations to search by variation_sku
        # ============================================
        cursor.execute("""
            SELECT DISTINCT
                p.id,
                p.product_name,
                p.sku,
                p.product_type,
                p.tax_type,
                p.product_tax,
                p.base_unit_id,
                p.sale_unit_id,
                p.purchase_unit_id,
                u1.unit_name AS base_unit_name,
                u1.unit_short AS base_unit_short,
                u2.unit_name AS sale_unit_name,
                u2.unit_short AS sale_unit_short,
                u3.unit_name AS purchase_unit_name,
                u3.unit_short AS purchase_unit_short,
                cat.discount_type AS category_discount_type,
                cat.discount_value AS category_discount_value
            FROM products p
            LEFT JOIN units u1 ON p.base_unit_id = u1.id
            LEFT JOIN units u2 ON p.sale_unit_id = u2.id
            LEFT JOIN units u3 ON p.purchase_unit_id = u3.id
            LEFT JOIN product_variations pv ON pv.product_id = p.id
            LEFT JOIN categories cat ON p.category_id = cat.id
            WHERE p.product_name LIKE %s
               OR p.sku LIKE %s
               OR pv.variation_sku LIKE %s
               OR pv.variation_name LIKE %s
        """, (f"%{main_name}%", f"%{main_name}%", f"%{main_name}%", f"%{main_name}%"))
        products = cursor.fetchall()

        for product in products:
            # ============================================
            # VARIABLE PRODUCTS (with variations)
            # ============================================
            if product['product_type'] == 'variable':

                if variation_name:
                    # User searched for specific variation (e.g., "MILK - 1L")
                    cursor.execute("""
                        SELECT
                            pv.id,
                            pv.variation_name,
                            pv.variation_type,
                            pv.variation_sku,
                            pv.variation_cost,
                            pv.variation_price,
                            pv.variation_tax_type,
                            pv.variation_tax
                        FROM product_variations pv
                        WHERE pv.product_id = %s
                        AND (pv.variation_name LIKE %s
                             OR pv.variation_type LIKE %s
                             OR pv.variation_sku LIKE %s)
                    """, (product['id'], f"%{variation_name}%", f"%{variation_name}%", f"%{variation_name}%"))

                else:
                    # ✅ FIXED: Check if search_term matches a variation_sku or variation_name for this product
                    cursor.execute("""
                        SELECT COUNT(*) as cnt
                        FROM product_variations
                        WHERE product_id = %s
                        AND (variation_sku LIKE %s OR variation_name LIKE %s)
                    """, (product['id'], f"%{main_name}%", f"%{main_name}%"))

                    sku_match_count = cursor.fetchone()['cnt']

                    if sku_match_count > 0 and main_name.lower() not in product['product_name'].lower():
                        # Search term matched a variation SKU/name specifically - show only those
                        cursor.execute("""
                            SELECT
                                pv.id,
                                pv.variation_name,
                                pv.variation_type,
                                pv.variation_sku,
                                pv.variation_cost,
                                pv.variation_price,
                                pv.variation_tax_type,
                                pv.variation_tax
                            FROM product_variations pv
                            WHERE pv.product_id = %s
                            AND (pv.variation_sku LIKE %s OR pv.variation_name LIKE %s)
                        """, (product['id'], f"%{main_name}%", f"%{main_name}%"))
                    else:
                        # Search term matched product name or SKU - show ALL variations
                        cursor.execute("""
                            SELECT
                                pv.id,
                                pv.variation_name,
                                pv.variation_type,
                                pv.variation_sku,
                                pv.variation_cost,
                                pv.variation_price,
                                pv.variation_tax_type,
                                pv.variation_tax
                            FROM product_variations pv
                            WHERE pv.product_id = %s
                        """, (product['id'],))

                variations = cursor.fetchall()

                for var in variations:
                    # ✅ GET BATCHES GROUPED BY COST, PRICE, AND OUR_PRICE - WITH GRN INFO
                    if warehouse_id and store_id:
                        cursor.execute("""
                            SELECT
                                pb.price,
                                pb.cost,
                                pb.our_price,
                                MAX(pb.expiration_date) AS latest_expiration_date,
                                SUM(ws.quantity) AS total_stock,
                                GROUP_CONCAT(DISTINCT pb.batch_id ORDER BY pb.batch_id SEPARATOR ',') AS batch_ids,
                                GROUP_CONCAT(DISTINCT CONCAT(g.grn_code, ' (', DATE_FORMAT(g.grn_date, '%Y-%m-%d'), ')') SEPARATOR ', ') AS grn_info
                            FROM product_batches pb
                            INNER JOIN warehouse_stock ws ON pb.batch_id = ws.batch_id
                            LEFT JOIN grn g ON pb.grn_id = g.grn_id
                            WHERE pb.product_id = %s
                            AND pb.variation_id = %s
                            AND pb.remaining_quantity > 0
                            AND ws.warehouse_id = %s
                            AND ws.store_id = %s
                            AND ws.quantity > 0
                            GROUP BY pb.price, pb.cost, pb.our_price
                            ORDER BY pb.price ASC, pb.cost ASC
                        """, (product['id'], var['id'], warehouse_id, store_id))
                    elif warehouse_id:
                        cursor.execute("""
                            SELECT
                                pb.price,
                                pb.cost,
                                pb.our_price,
                                MAX(pb.expiration_date) AS latest_expiration_date,
                                SUM(ws.quantity) AS total_stock,
                                GROUP_CONCAT(DISTINCT pb.batch_id ORDER BY pb.batch_id SEPARATOR ',') AS batch_ids,
                                GROUP_CONCAT(DISTINCT CONCAT(g.grn_code, ' (', DATE_FORMAT(g.grn_date, '%Y-%m-%d'), ')') SEPARATOR ', ') AS grn_info
                            FROM product_batches pb
                            INNER JOIN warehouse_stock ws ON pb.batch_id = ws.batch_id
                            LEFT JOIN grn g ON pb.grn_id = g.grn_id
                            WHERE pb.product_id = %s
                            AND pb.variation_id = %s
                            AND pb.remaining_quantity > 0
                            AND ws.warehouse_id = %s
                            AND ws.quantity > 0
                            GROUP BY pb.price, pb.cost, pb.our_price
                            ORDER BY pb.price ASC, pb.cost ASC
                        """, (product['id'], var['id'], warehouse_id))
                    else:
                        cursor.execute("""
                            SELECT
                                pb.price,
                                pb.cost,
                                pb.our_price,
                                MAX(pb.expiration_date) AS latest_expiration_date,
                                SUM(pb.remaining_quantity) AS total_stock,
                                GROUP_CONCAT(DISTINCT pb.batch_id ORDER BY pb.batch_id SEPARATOR ',') AS batch_ids,
                                GROUP_CONCAT(DISTINCT CONCAT(g.grn_code, ' (', DATE_FORMAT(g.grn_date, '%Y-%m-%d'), ')') SEPARATOR ', ') AS grn_info
                            FROM product_batches pb
                            LEFT JOIN grn g ON pb.grn_id = g.grn_id
                            WHERE pb.product_id = %s
                            AND pb.variation_id = %s
                            AND pb.remaining_quantity > 0
                            GROUP BY pb.price, pb.cost, pb.our_price
                            ORDER BY pb.price ASC, pb.cost ASC
                        """, (product['id'], var['id']))

                    batches = cursor.fetchall()

                    # Default values from variation
                    default_price = float(var['variation_price']) if var['variation_price'] else 0.0
                    default_cost = float(var['variation_cost']) if var['variation_cost'] else 0.0
                    default_our_price = 0.0

                    if batches:
                        for batch_group in batches:
                            price = float(batch_group['price']) if batch_group['price'] else default_price
                            cost = float(batch_group['cost']) if batch_group['cost'] else default_cost
                            our_price = float(batch_group['our_price']) if batch_group['our_price'] else default_our_price
                            stock = float(batch_group['total_stock']) if batch_group['total_stock'] else 0.0
                            exp_date = batch_group['latest_expiration_date'].strftime('%Y-%m-%d') if batch_group['latest_expiration_date'] else '-'
                            batch_ids = batch_group['batch_ids']
                            grn_info = batch_group.get('grn_info', 'N/A')

                            tax_type = var.get('variation_tax_type') or product.get('tax_type')
                            product_tax = float(var.get('variation_tax', 0)) if var.get('variation_tax') else float(product.get('product_tax', 0))

                            stock_display = f"{stock:.2f}".rstrip('0').rstrip('.') if stock % 1 else f"{int(stock)}"

                            display_name = f"{product['product_name']} - {var['variation_name']} - (Cost {cost:.0f} - Our Price {our_price:.0f} - Price {price:.0f}) Stock {stock_display} Exp: {exp_date}"

                            result.append({
                                "product_id": product['id'],
                                "variation_id": var['id'],
                                "batch_ids": batch_ids,
                                "grn_info": grn_info,
                                "product_name": product['product_name'],
                                "variation_name": var['variation_name'],
                                "variation_type": var['variation_type'],
                                "sku": var['variation_sku'],
                                "display_name": display_name,
                                "product_type": "variation",
                                "product_quantity": stock,
                                "product_price": price,
                                "product_cost": cost,
                                "our_price": our_price,
                                "expiration_date": exp_date,
                                "tax_type": tax_type,
                                "product_tax": product_tax,
                                "base_unit": product.get('base_unit_name'),
                                "sale_unit": product.get('sale_unit_name'),
                                "purchase_unit": product.get('purchase_unit_name'),
                                "sales_unit": product.get('sale_unit_id'),
                                "sales_units": [
                                    {"id": product.get('base_unit_id'), "name": product.get('base_unit_name')},
                                    {"id": product.get('sale_unit_id'), "name": product.get('sale_unit_name')},
                                    {"id": product.get('purchase_unit_id'), "name": product.get('purchase_unit_name')}
                                ],
                                "category_discount_type": product.get('category_discount_type'),
                                "category_discount_value": float(product.get('category_discount_value') or 0)
                            })
                    else:
                        # No stock in warehouse
                        display_name = f"{product['product_name']} - {var['variation_name']} - (Cost {default_cost:.0f} - Our Price 0 - Price {default_price:.0f}) Stock 0 Exp: -"

                        result.append({
                            "product_id": product['id'],
                            "variation_id": var['id'],
                            "batch_ids": None,
                            "grn_info": None,
                            "product_name": product['product_name'],
                            "variation_name": var['variation_name'],
                            "variation_type": var['variation_type'],
                            "sku": var['variation_sku'],
                            "display_name": display_name,
                            "product_type": "variation",
                            "product_quantity": 0.0,
                            "product_price": default_price,
                            "product_cost": default_cost,
                            "our_price": 0.0,
                            "expiration_date": '-',
                            "tax_type": var.get('variation_tax_type') or product.get('tax_type'),
                            "product_tax": float(var.get('variation_tax', 0)) if var.get('variation_tax') else float(product.get('product_tax', 0)),
                            "base_unit": product.get('base_unit_name'),
                            "sale_unit": product.get('sale_unit_name'),
                            "purchase_unit": product.get('purchase_unit_name'),
                            "sales_unit": product.get('sale_unit_id'),
                            "sales_units": [
                                {"id": product.get('base_unit_id'), "name": product.get('base_unit_name')},
                                {"id": product.get('sale_unit_id'), "name": product.get('sale_unit_name')},
                                {"id": product.get('purchase_unit_id'), "name": product.get('purchase_unit_name')}
                            ],
                            "category_discount_type": product.get('category_discount_type'),
                            "category_discount_value": float(product.get('category_discount_value') or 0)
                        })

            # ============================================
            # SINGLE PRODUCTS (no variations)
            # ============================================
            else:
                if (main_name.lower() in product['product_name'].lower()) or \
                   (main_name.lower() in (product['sku'] or '').lower()):

                    if warehouse_id and store_id:
                        cursor.execute("""
                            SELECT
                                pb.price,
                                pb.cost,
                                pb.our_price,
                                MAX(pb.expiration_date) AS latest_expiration_date,
                                SUM(ws.quantity) AS total_stock,
                                GROUP_CONCAT(DISTINCT pb.batch_id ORDER BY pb.batch_id SEPARATOR ',') AS batch_ids,
                                GROUP_CONCAT(DISTINCT CONCAT(g.grn_code, ' (', DATE_FORMAT(g.grn_date, '%Y-%m-%d'), ')') SEPARATOR ', ') AS grn_info
                            FROM product_batches pb
                            INNER JOIN warehouse_stock ws ON pb.batch_id = ws.batch_id
                            LEFT JOIN grn g ON pb.grn_id = g.grn_id
                            WHERE pb.product_id = %s
                            AND pb.variation_id IS NULL
                            AND pb.remaining_quantity > 0
                            AND ws.warehouse_id = %s
                            AND ws.store_id = %s
                            AND ws.quantity > 0
                            GROUP BY pb.price, pb.cost, pb.our_price
                            ORDER BY pb.price ASC, pb.cost ASC
                        """, (product['id'], warehouse_id, store_id))
                    elif warehouse_id:
                        cursor.execute("""
                            SELECT
                                pb.price,
                                pb.cost,
                                pb.our_price,
                                MAX(pb.expiration_date) AS latest_expiration_date,
                                SUM(ws.quantity) AS total_stock,
                                GROUP_CONCAT(DISTINCT pb.batch_id ORDER BY pb.batch_id SEPARATOR ',') AS batch_ids,
                                GROUP_CONCAT(DISTINCT CONCAT(g.grn_code, ' (', DATE_FORMAT(g.grn_date, '%Y-%m-%d'), ')') SEPARATOR ', ') AS grn_info
                            FROM product_batches pb
                            INNER JOIN warehouse_stock ws ON pb.batch_id = ws.batch_id
                            LEFT JOIN grn g ON pb.grn_id = g.grn_id
                            WHERE pb.product_id = %s
                            AND pb.variation_id IS NULL
                            AND pb.remaining_quantity > 0
                            AND ws.warehouse_id = %s
                            AND ws.quantity > 0
                            GROUP BY pb.price, pb.cost, pb.our_price
                            ORDER BY pb.price ASC, pb.cost ASC
                        """, (product['id'], warehouse_id))
                    else:
                        cursor.execute("""
                            SELECT
                                pb.price,
                                pb.cost,
                                pb.our_price,
                                MAX(pb.expiration_date) AS latest_expiration_date,
                                SUM(pb.remaining_quantity) AS total_stock,
                                GROUP_CONCAT(DISTINCT pb.batch_id ORDER BY pb.batch_id SEPARATOR ',') AS batch_ids,
                                GROUP_CONCAT(DISTINCT CONCAT(g.grn_code, ' (', DATE_FORMAT(g.grn_date, '%Y-%m-%d'), ')') SEPARATOR ', ') AS grn_info
                            FROM product_batches pb
                            LEFT JOIN grn g ON pb.grn_id = g.grn_id
                            WHERE pb.product_id = %s
                            AND pb.variation_id IS NULL
                            AND pb.remaining_quantity > 0
                            GROUP BY pb.price, pb.cost, pb.our_price
                            ORDER BY pb.price ASC, pb.cost ASC
                        """, (product['id'],))

                    batches = cursor.fetchall()

                    if batches:
                        for batch_group in batches:
                            price = float(batch_group['price']) if batch_group['price'] else 0.0
                            cost = float(batch_group['cost']) if batch_group['cost'] else 0.0
                            our_price = float(batch_group['our_price']) if batch_group['our_price'] else 0.0
                            stock = float(batch_group['total_stock']) if batch_group['total_stock'] else 0.0
                            exp_date = batch_group['latest_expiration_date'].strftime('%Y-%m-%d') if batch_group['latest_expiration_date'] else '-'
                            batch_ids = batch_group['batch_ids']
                            grn_info = batch_group.get('grn_info', 'N/A')

                            stock_display = f"{stock:.2f}".rstrip('0').rstrip('.') if stock % 1 else f"{int(stock)}"

                            display_name = f"{product['product_name']} ({product['sku']}) - (Cost {cost:.0f} - Our Price {our_price:.0f} - Price {price:.0f}) Stock {stock_display} Exp: {exp_date}"

                            result.append({
                                "product_id": product['id'],
                                "variation_id": None,
                                "batch_ids": batch_ids,
                                "grn_info": grn_info,
                                "product_name": product['product_name'],
                                "variation_name": None,
                                "variation_type": None,
                                "sku": product['sku'],
                                "display_name": display_name,
                                "product_type": "single",
                                "product_quantity": stock,
                                "product_price": price,
                                "product_cost": cost,
                                "our_price": our_price,
                                "expiration_date": exp_date,
                                "tax_type": product['tax_type'],
                                "product_tax": float(product['product_tax']) if product['product_tax'] else 0.0,
                                "base_unit": product.get('base_unit_name'),
                                "sale_unit": product.get('sale_unit_name'),
                                "purchase_unit": product.get('purchase_unit_name'),
                                "sales_unit": product.get('sale_unit_id'),
                                "sales_units": [
                                    {"id": product.get('base_unit_id'), "name": product.get('base_unit_name')},
                                    {"id": product.get('sale_unit_id'), "name": product.get('sale_unit_name')},
                                    {"id": product.get('purchase_unit_id'), "name": product.get('purchase_unit_name')}
                                ],
                                "category_discount_type": product.get('category_discount_type'),
                                "category_discount_value": float(product.get('category_discount_value') or 0)
                            })
                    else:
                        # No stock in warehouse
                        display_name = f"{product['product_name']} ({product['sku']}) - (Cost 0 - Our Price 0 - Price 0) Stock 0 Exp: -"

                        result.append({
                            "product_id": product['id'],
                            "variation_id": None,
                            "batch_ids": None,
                            "grn_info": None,
                            "product_name": product['product_name'],
                            "variation_name": None,
                            "variation_type": None,
                            "sku": product['sku'],
                            "display_name": display_name,
                            "product_type": "single",
                            "product_quantity": 0.0,
                            "product_price": 0.0,
                            "product_cost": 0.0,
                            "our_price": 0.0,
                            "expiration_date": '-',
                            "tax_type": product['tax_type'],
                            "product_tax": float(product['product_tax']) if product['product_tax'] else 0.0,
                            "base_unit": product.get('base_unit_name'),
                            "sale_unit": product.get('sale_unit_name'),
                            "purchase_unit": product.get('purchase_unit_name'),
                            "sales_unit": product.get('sale_unit_id'),
                            "sales_units": [
                                {"id": product.get('base_unit_id'), "name": product.get('base_unit_name')},
                                {"id": product.get('sale_unit_id'), "name": product.get('sale_unit_name')},
                                {"id": product.get('purchase_unit_id'), "name": product.get('purchase_unit_name')}
                            ],
                            "category_discount_type": product.get('category_discount_type'),
                            "category_discount_value": float(product.get('category_discount_value') or 0)
                        })

        return jsonify(result), 200

    except mysql.connector.Error as err:
        print(f"❌ Database Error in search_sales_products: {err}")
        traceback.print_exc()
        return jsonify({"status": "error", "message": f"Database error: {str(err)}"}), 500

    except Exception as e:
        print(f"❌ Error in search_sales_products: {e}")
        traceback.print_exc()
        return jsonify({"status": "error", "message": str(e)}), 500

    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


@product_bp.route('/warehouse_stock_search', methods=['GET'])
@jwt_required()
@role_required('admin', 'manager')
def warehouse_stock_search():
    """
    Search products - Groups by Product + Cost + Price + Supplier + Expiry
    Gets batch details from product_batches table
    """
    
    warehouse_id = request.args.get('warehouse_id', '').strip()
    store_id = request.args.get('store_id', '').strip()
    query = request.args.get('query', '').strip()
    
    if not warehouse_id or not store_id:
        return jsonify({
            "status": "error", 
            "message": "warehouse_id and store_id are required"
        }), 400
    
    conn = get_db_connection()
    if conn is None:
        return jsonify({'error': 'Database connection failed'}), 500
        
    cursor = conn.cursor(dictionary=True)
    result = []
    
    try:
        search_condition = ""
        search_params = [warehouse_id, store_id]
        
        if query:
            search_condition = """
                AND (
                    p.product_name LIKE %s 
                    OR p.sku LIKE %s
                    OR pv.variation_name LIKE %s
                    OR pv.variation_type LIKE %s
                    OR pv.variation_sku LIKE %s
                    OR pb.batch_number LIKE %s
                    OR g.grn_code LIKE %s
                    OR s.supplier_name LIKE %s
                )
            """
            search_term = f"%{query}%"
            search_params.extend([search_term] * 8)
        
        # ✅ CORRECTED: Get cost, price, expiry from product_batches (pb)
        query_sql = f"""
            SELECT 
                -- Grouping Keys (from product_batches)
                ws.product_id,
                ws.variation_id,
                COALESCE(pb.cost, 0) as batch_cost,
                COALESCE(pb.price, 0) as batch_price,
                pb.expiration_date,
                po.supplier_id,
                
                -- Aggregated Data
                GROUP_CONCAT(DISTINCT ws.batch_id ORDER BY ws.batch_id) as batch_ids,
                SUM(ws.quantity) as total_stock,
                GROUP_CONCAT(DISTINCT pb.batch_number ORDER BY pb.batch_number SEPARATOR ', ') as batch_numbers,
                GROUP_CONCAT(DISTINCT g.grn_code ORDER BY g.grn_code SEPARATOR ', ') as grn_codes,
                MIN(ws.id) as first_stock_id,
                
                -- Product Details
                p.product_name,
                p.sku,
                p.product_type,
                p.tax_type,
                p.product_tax,
                p.base_unit_id,
                p.sale_unit_id,
                p.purchase_unit_id,
                
                -- Unit Names
                u1.unit_name AS base_unit_name,
                u1.unit_short AS base_unit_short,
                u2.unit_name AS sale_unit_name,
                u2.unit_short AS sale_unit_short,
                u3.unit_name AS purchase_unit_name,
                u3.unit_short AS purchase_unit_short,
                
                -- Variation Details
                pv.variation_name,
                pv.variation_type,
                pv.variation_sku,
                pv.variation_cost,
                pv.variation_price,
                pv.variation_tax_type,
                pv.variation_tax,
                
                -- Supplier Details
                s.supplier_name,
                s.supplier_code
                
            FROM warehouse_stock ws
            INNER JOIN products p ON ws.product_id = p.id
            LEFT JOIN product_variations pv ON ws.variation_id = pv.id
            LEFT JOIN product_batches pb ON ws.batch_id = pb.batch_id  -- ✅ JOIN to get batch details
            LEFT JOIN grn g ON pb.grn_id = g.grn_id
            LEFT JOIN purchase_orders po ON g.purchase_order_id = po.order_id
            LEFT JOIN suppliers s ON po.supplier_id = s.id
            LEFT JOIN units u1 ON p.base_unit_id = u1.id
            LEFT JOIN units u2 ON p.sale_unit_id = u2.id
            LEFT JOIN units u3 ON p.purchase_unit_id = u3.id
            
            WHERE ws.warehouse_id = %s
            AND ws.store_id = %s
            AND ws.quantity > 0
            {search_condition}
            
            GROUP BY 
                ws.product_id,
                ws.variation_id,
                batch_cost,          -- ✅ From product_batches
                batch_price,         -- ✅ From product_batches
                pb.expiration_date,  -- ✅ From product_batches
                po.supplier_id,
                p.product_name,
                p.sku,
                p.product_type,
                p.tax_type,
                p.product_tax,
                p.base_unit_id,
                p.sale_unit_id,
                p.purchase_unit_id,
                u1.unit_name,
                u1.unit_short,
                u2.unit_name,
                u2.unit_short,
                u3.unit_name,
                u3.unit_short,
                pv.variation_name,
                pv.variation_type,
                pv.variation_sku,
                pv.variation_cost,
                pv.variation_price,
                pv.variation_tax_type,
                pv.variation_tax,
                s.supplier_name,
                s.supplier_code
                
            ORDER BY p.product_name, pv.variation_name, batch_cost, pb.expiration_date
        """
        
        cursor.execute(query_sql, search_params)
        stock_groups = cursor.fetchall()
        
        # Format results
        for item in stock_groups:
            # Determine tax type and tax amount
            tax_type = item.get('variation_tax_type') or item.get('tax_type')
            product_tax = float(item.get('variation_tax', 0)) if item.get('variation_tax') else float(item.get('product_tax', 0))
            
            # ✅ Cost and price from product_batches (via pb.cost, pb.price)
            cost = float(item['batch_cost']) if item['batch_cost'] else 0.0
            price = float(item['batch_price']) if item['batch_price'] else 0.0
            
            # Total stock for this group
            total_stock = float(item['total_stock']) if item['total_stock'] else 0.0
            
            # ✅ Expiration date from product_batches
            exp_date = item['expiration_date'].strftime('%Y-%m-%d') if item['expiration_date'] else '-'
            
            # Supplier info
            supplier_name = item['supplier_name'] or 'Unknown'
            supplier_code = item['supplier_code'] or '-'
            
            # Batch info
            batch_numbers = item['batch_numbers'] or '-'
            grn_codes = item['grn_codes'] or '-'
            
            # Build display name
            if item['variation_id']:
                base_name = f"{item['product_name']} - {item['variation_name']}"
                if item['variation_type']:
                    base_name += f" ({item['variation_type']})"
                sku = item['variation_sku']
            else:
                base_name = item['product_name']
                sku = item['sku']
            
            # Display format
            display_name = (
                f"{base_name} | "
                f"Supplier: {supplier_name} | "
                f"Cost {cost:.2f} - Price {price:.2f} | "
                f"Stock {total_stock:.2f} | "
                f"Exp: {exp_date}"
            )
            
            if batch_numbers != '-':
                display_name += f" | Batches: {batch_numbers}"
            
            result.append({
                # IDs
                "stock_id": item['first_stock_id'],
                "product_id": item['product_id'],
                "variation_id": item['variation_id'],
                "batch_ids": item['batch_ids'],  # Comma-separated list
                "supplier_id": item['supplier_id'],
                
                # Product Info
                "product_name": item['product_name'],
                "variation_name": item['variation_name'],
                "variation_type": item['variation_type'],
                "sku": sku,
                "display_name": display_name,
                "product_type": "variation" if item['variation_id'] else "single",
                
                # ✅ Pricing & Stock from product_batches
                "product_price": price,
                "product_cost": cost,
                "product_quantity": total_stock,
                
                # ✅ Batch & Supplier Info from product_batches
                "batch_numbers": batch_numbers,
                "grn_codes": grn_codes,
                "supplier_name": supplier_name,
                "supplier_code": supplier_code,
                "expiration_date": exp_date,
                
                # Tax Info
                "tax_type": tax_type,
                "product_tax": product_tax,
                
                # Units
                "base_unit": item.get('base_unit_name'),
                "sale_unit": item.get('sale_unit_name'),
                "purchase_unit": item.get('purchase_unit_name'),
                "sales_unit": item.get('sale_unit_id'),
                "sales_units": [
                    {"id": item.get('base_unit_id'), "name": item.get('base_unit_name')},
                    {"id": item.get('sale_unit_id'), "name": item.get('sale_unit_name')},
                    {"id": item.get('purchase_unit_id'), "name": item.get('purchase_unit_name')}
                ]
            })
        
        return jsonify(result), 200
        
    except mysql.connector.Error as err:
        print(f"❌ Database Error: {err}")
        traceback.print_exc()
        return jsonify({
            "status": "error", 
            "message": f"Database error: {str(err)}"
        }), 500
    
    except Exception as e:
        print(f"❌ Error: {e}")
        traceback.print_exc()
        return jsonify({
            "status": "error", 
            "message": str(e)
        }), 500
    
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


@product_bp.route('/search_products_for_purchase_return', methods=['GET'])
@jwt_required()
@role_required('admin', 'manager')
def search_products_for_purchase_return():
    """
    ✅ GRN-AWARE Purchase Return Search
    
    Groups products by:
    - Product/Variation
    - Supplier
    - Cost
    - Price
    - Expiration Date (if exists)
    
    Shows separate entries for different cost/price/expiry combinations
    User can select specific batch group to return
    
    Example Results:
    1. Milk 1L - Supplier ABC - Cost 120 - Price 150 - Exp: 2025-06-15 - Stock: 50
    2. Milk 1L - Supplier ABC - Cost 120 - Price 150 - Exp: 2025-07-20 - Stock: 30
    3. Milk 1L - Supplier XYZ - Cost 125 - Price 155 - Exp: 2025-06-10 - Stock: 25
    """
    query = request.args.get('query', '').strip()
    warehouse_id = request.args.get('warehouse_id', '').strip()
    store_id = request.args.get('store_id', '').strip()

    if not query:
        return jsonify({"error": "Query parameter is required"}), 400
    
    if not warehouse_id or not store_id:
        return jsonify({"error": "Both warehouse_id and store_id are required"}), 400

    # Parse search term (handle "Product - Variation" format)
    main_name = None
    variation_name = None
    if " - " in query:
        parts = query.split(" - ", 1)
        main_name = parts[0].strip()
        variation_name = parts[1].strip()
    else:
        main_name = query
        variation_name = query

    conn = get_db_connection()
    if conn is None:
        return jsonify({'error': 'Database connection failed'}), 500
        
    cursor = conn.cursor(dictionary=True)
    result = []

    try:
        # ============================================
        # STEP 1: Search Products
        # ============================================
        cursor.execute("""
            SELECT 
                p.id,
                p.product_name,
                p.sku,
                p.product_type,
                p.tax_type,
                p.product_tax,
                p.purchase_unit_id,
                u.unit_short AS purchase_unit
            FROM products p
            LEFT JOIN units u ON p.purchase_unit_id = u.id
            WHERE p.product_name LIKE %s OR p.sku LIKE %s
        """, (f"%{main_name}%", f"%{main_name}%"))
        
        products = cursor.fetchall()

        for product in products:
            product_id = product['id']
            product_name = product['product_name']
            product_sku = product['sku']
            product_type = product['product_type']
            purchase_unit = product['purchase_unit'] or ''
            tax_type = product['tax_type']
            product_tax = float(product['product_tax']) if product['product_tax'] else 0.0

            # ============================================
            # VARIABLE PRODUCTS (with variations)
            # ============================================
            if product_type == 'variable':
                cursor.execute("""
                    SELECT 
                        pv.id,
                        pv.variation_name,
                        pv.variation_type,
                        pv.variation_sku,
                        pv.variation_tax_type,
                        pv.variation_tax
                    FROM product_variations pv
                    WHERE pv.product_id = %s
                    AND (pv.variation_name LIKE %s 
                         OR pv.variation_type LIKE %s 
                         OR pv.variation_sku LIKE %s)
                """, (product_id, f"%{variation_name}%", f"%{variation_name}%", f"%{variation_name}%"))
                
                variations = cursor.fetchall()

                for var in variations:
                    variation_id = var['id']
                    variation_name_str = var['variation_name']
                    variation_sku = var['variation_sku']
                    var_tax_type = var['variation_tax_type'] or tax_type
                    var_tax = float(var['variation_tax']) if var['variation_tax'] else product_tax

                    # ✅ GROUP BY: Supplier, Cost, Price, Expiration Date
                    # ✅ FIXED: MariaDB-compatible NULL handling
                    cursor.execute("""
                        SELECT 
                            po.supplier_id,
                            s.supplier_name,
                            pb.cost,
                            pb.price,
                            pb.expiration_date,
                            SUM(ws.quantity) AS total_stock,
                            GROUP_CONCAT(DISTINCT pb.batch_id ORDER BY pb.batch_id SEPARATOR ',') AS batch_ids,
                            MIN(pb.batch_id) AS first_batch_id,
                            GROUP_CONCAT(DISTINCT g.grn_code ORDER BY g.grn_code SEPARATOR ', ') AS grn_codes
                        FROM product_batches pb
                        INNER JOIN warehouse_stock ws ON pb.batch_id = ws.batch_id
                        INNER JOIN grn g ON pb.grn_id = g.grn_id
                        INNER JOIN purchase_orders po ON g.purchase_order_id = po.order_id
                        LEFT JOIN suppliers s ON po.supplier_id = s.id
                        WHERE pb.product_id = %s 
                        AND pb.variation_id = %s 
                        AND pb.remaining_quantity > 0
                        AND ws.warehouse_id = %s
                        AND ws.store_id = %s
                        AND ws.quantity > 0
                        AND g.status = 'completed'
                        AND po.status = 'Received'
                        GROUP BY 
                            po.supplier_id, 
                            pb.cost, 
                            pb.price, 
                            pb.expiration_date
                        ORDER BY 
                            s.supplier_name ASC, 
                            CASE WHEN pb.expiration_date IS NULL THEN 1 ELSE 0 END,
                            pb.expiration_date ASC,
                            pb.cost ASC, 
                            pb.price ASC
                    """, (product_id, variation_id, warehouse_id, store_id))
                    
                    batch_groups = cursor.fetchall()

                    if batch_groups:
                        for group in batch_groups:
                            supplier_id = group['supplier_id']
                            supplier_name = group['supplier_name'] or 'Unknown'
                            cost = float(group['cost']) if group['cost'] else 0.0
                            price = float(group['price']) if group['price'] else 0.0
                            expiration_date = group['expiration_date']
                            stock = float(group['total_stock']) if group['total_stock'] else 0.0
                            batch_ids = group['batch_ids']
                            grn_codes = group['grn_codes']
                            
                            # Format expiration date
                            if expiration_date:
                                exp_str = expiration_date.strftime('%Y-%m-%d')
                                exp_display = f"Exp: {exp_str}"
                            else:
                                exp_str = None
                                exp_display = "No Expiry"
                            
                            # ✅ BUILD DISPLAY NAME
                            display_name = (
                                f"{product_name} - {variation_name_str} | "
                                f"Supplier: {supplier_name} | "
                                f"Cost: Rs {cost:.2f} | "
                                f"Price: Rs {price:.2f} | "
                                f"{exp_display} | "
                                f"Stock: {stock:.2f} {purchase_unit}"
                            )
                            
                            result.append({
                                "product_id": product_id,
                                "variation_id": variation_id,
                                "batch_ids": batch_ids,
                                "grn_codes": grn_codes,
                                "supplier_id": supplier_id,
                                "supplier_name": supplier_name,
                                "product_name": product_name,
                                "variation_name": variation_name_str,
                                "sku": variation_sku,
                                "display_name": display_name,
                                "product_type": "variation",
                                "product_quantity": stock,
                                "product_price": price,
                                "product_cost": cost,
                                "expiration_date": exp_str,
                                "tax_type": var_tax_type,
                                "product_tax": var_tax,
                                "purchase_unit": purchase_unit
                            })

            # ============================================
            # SINGLE PRODUCTS (no variations)
            # ============================================
            else:
                if (main_name.lower() in product_name.lower()) or \
                   (variation_name.lower() in product_name.lower()) or \
                   (main_name.lower() in (product_sku or '').lower()):
                    
                    # ✅ GROUP BY: Supplier, Cost, Price, Expiration Date
                    # ✅ FIXED: MariaDB-compatible NULL handling
                    cursor.execute("""
                        SELECT 
                            po.supplier_id,
                            s.supplier_name,
                            pb.cost,
                            pb.price,
                            pb.expiration_date,
                            SUM(ws.quantity) AS total_stock,
                            GROUP_CONCAT(DISTINCT pb.batch_id ORDER BY pb.batch_id SEPARATOR ',') AS batch_ids,
                            MIN(pb.batch_id) AS first_batch_id,
                            GROUP_CONCAT(DISTINCT g.grn_code ORDER BY g.grn_code SEPARATOR ', ') AS grn_codes
                        FROM product_batches pb
                        INNER JOIN warehouse_stock ws ON pb.batch_id = ws.batch_id
                        INNER JOIN grn g ON pb.grn_id = g.grn_id
                        INNER JOIN purchase_orders po ON g.purchase_order_id = po.order_id
                        LEFT JOIN suppliers s ON po.supplier_id = s.id
                        WHERE pb.product_id = %s 
                        AND pb.variation_id IS NULL 
                        AND pb.remaining_quantity > 0
                        AND ws.warehouse_id = %s
                        AND ws.store_id = %s
                        AND ws.quantity > 0
                        AND g.status = 'completed'
                        AND po.status = 'Received'
                        GROUP BY 
                            po.supplier_id, 
                            pb.cost, 
                            pb.price, 
                            pb.expiration_date
                        ORDER BY 
                            s.supplier_name ASC, 
                            CASE WHEN pb.expiration_date IS NULL THEN 1 ELSE 0 END,
                            pb.expiration_date ASC,
                            pb.cost ASC, 
                            pb.price ASC
                    """, (product_id, warehouse_id, store_id))
                    
                    batch_groups = cursor.fetchall()

                    if batch_groups:
                        for group in batch_groups:
                            supplier_id = group['supplier_id']
                            supplier_name = group['supplier_name'] or 'Unknown'
                            cost = float(group['cost']) if group['cost'] else 0.0
                            price = float(group['price']) if group['price'] else 0.0
                            expiration_date = group['expiration_date']
                            stock = float(group['total_stock']) if group['total_stock'] else 0.0
                            batch_ids = group['batch_ids']
                            grn_codes = group['grn_codes']
                            
                            # Format expiration date
                            if expiration_date:
                                exp_str = expiration_date.strftime('%Y-%m-%d')
                                exp_display = f"Exp: {exp_str}"
                            else:
                                exp_str = None
                                exp_display = "No Expiry"
                            
                            # ✅ BUILD DISPLAY NAME
                            display_name = (
                                f"{product_name} ({product_sku}) | "
                                f"Supplier: {supplier_name} | "
                                f"Cost: Rs {cost:.2f} | "
                                f"Price: Rs {price:.2f} | "
                                f"{exp_display} | "
                                f"Stock: {stock:.2f} {purchase_unit}"
                            )
                            
                            result.append({
                                "product_id": product_id,
                                "variation_id": None,
                                "batch_ids": batch_ids,
                                "grn_codes": grn_codes,
                                "supplier_id": supplier_id,
                                "supplier_name": supplier_name,
                                "product_name": product_name,
                                "variation_name": None,
                                "sku": product_sku,
                                "display_name": display_name,
                                "product_type": "single",
                                "product_quantity": stock,
                                "product_price": price,
                                "product_cost": cost,
                                "expiration_date": exp_str,
                                "tax_type": tax_type,
                                "product_tax": product_tax,
                                "purchase_unit": purchase_unit
                            })

        return jsonify(result), 200

    except mysql.connector.Error as err:
        print(f"❌ Database Error: {err}")
        traceback.print_exc()
        return jsonify({"error": f"Database error: {str(err)}"}), 500
    
    except Exception as e:
        print(f"❌ Error: {e}")
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500

    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()
            
# @product_bp.route('/variations', methods=['GET'])
# @jwt_required()
# @role_required('admin')
# def view_all_variation():
#     conn = get_db_connection()
#     if conn is None:
#         return jsonify({"error": "Database connection failed"}), 500

#     try:
#         cursor = conn.cursor(dictionary=True)

#         query = """
#             SELECT 
#                 p.product_name AS product_name,
#                 p.sku AS product_sku,
#                 pv.variation_name AS variation_name,
#                 pv.variation_sku AS variation_sku
#             FROM product_variations pv
#             JOIN products p ON pv.product_id = p.id
#             ORDER BY p.product_name, pv.variation_name
#         """
#         cursor.execute(query)
#         results = cursor.fetchall()

#         return jsonify(results), 200

#     except Exception as e:
#         print("Error:", e)
#         traceback.print_exc()
#         return jsonify({"error": "Failed to fetch variations"}), 500
#     finally:
#         cursor.close()
#         conn.close()



