from flask import Blueprint, request, jsonify
from flask_jwt_extended import jwt_required
from db.db import get_db_connection
from config.auth import role_required
from datetime import datetime, timedelta, date
import json
import traceback

sale_return_bp = Blueprint('sale_return', __name__)


# ============================================================
# VARIATION_ID NORMALIZER
# ============================================================

def _vid(v):
    """
    Normalise variation_id to None (never 0 or '0').
    MySQL returns 0 for non-variation products; Python dict keys
    (pid, 0) != (pid, None), which breaks sold_map / returned_map lookups.
    """
    if v is None or v == 0 or v == '0' or v == '':
        return None
    try:
        return int(v)
    except (TypeError, ValueError):
        return None


# ============================================================
# STOCK HELPERS
# ============================================================

def _insert_return_item_batch(cursor, return_id, return_product_id,
                               batch_id, grn_id, quantity, cost,
                               expiration_date):
    """
    Insert one row into sale_return_product_items for batch-level traceability.
    Mirrors sale_product_items — called after every successful stock restore.
    """
    cursor.execute("""
        INSERT INTO sale_return_product_items
            (return_product_id, return_id, batch_id, grn_id,
             quantity, cost, expiration_date)
        VALUES (%s, %s, %s, %s, %s, %s, %s)
    """, (return_product_id, return_id, batch_id, grn_id,
          float(quantity), float(cost or 0), expiration_date))


# ============================================================
# ✅ NEW: TRACE-AWARE STOCK REVERSAL
# ============================================================

def _deduct_stock_via_trace(cursor, return_id, warehouse_id, store_id):
    """
    Reverse stock using the exact batch rows recorded in sale_return_product_items.

    This is the CORRECT way to reverse a sale return because:
      - sale_return_product_items records exactly which batch_id received
        which quantity when the return was created (price-aware)
      - Generic FIFO reversal ignores price, so for a product sold at 3 different
        prices (3 different batches), it would deduct from the wrong batch

    IMPORTANT: Call this BEFORE deleting sale_return_products, because the
    trace rows are cascade-deleted with sale_return_products.

    Returns:
        True  — fully reversed via trace rows
        False — no trace rows found (old data pre-migration), caller must
                fall back to _deduct_stock_for_return_reversal()
    """
    # Aggregate quantity per batch across all products in this return
    # (a single batch could appear in multiple return_product_items rows
    #  if the same batch was used for multiple products)
    cursor.execute("""
        SELECT
            srpi.batch_id,
            SUM(srpi.quantity) AS total_qty,
            ws.id              AS ws_id,
            ws.quantity        AS ws_qty,
            pb.remaining_quantity
        FROM sale_return_product_items srpi
        INNER JOIN warehouse_stock ws
            ON  ws.batch_id     = srpi.batch_id
            AND ws.warehouse_id = %s
            AND ws.store_id     = %s
        INNER JOIN product_batches pb
            ON  pb.batch_id = srpi.batch_id
        WHERE srpi.return_id = %s
        GROUP BY srpi.batch_id, ws.id, ws.quantity, pb.remaining_quantity
        FOR UPDATE
    """, (warehouse_id, store_id, return_id))

    trace_rows = cursor.fetchall()

    if not trace_rows:
        print(f"   ⚠️  [Trace] No sale_return_product_items rows found for "
              f"return_id={return_id}. Falling back to generic reversal.")
        return False

    print(f"   ✅ [Trace] Found {len(trace_rows)} distinct batch(es) to reverse "
          f"for return_id={return_id}")

    for row in trace_rows:
        deduct = float(row['total_qty'])

        cursor.execute("""
            UPDATE product_batches
            SET remaining_quantity = remaining_quantity - %s
            WHERE batch_id = %s
        """, (deduct, row['batch_id']))

        if cursor.rowcount != 1:
            raise Exception(
                f"Failed to update product_batches for batch {row['batch_id']}"
            )

        cursor.execute("""
            UPDATE warehouse_stock
            SET quantity = quantity - %s
            WHERE id = %s
        """, (deduct, row['ws_id']))

        if cursor.rowcount != 1:
            raise Exception(
                f"Failed to update warehouse_stock for ws_id {row['ws_id']}"
            )

        print(f"   ↩️  [Trace] Reversed {deduct} ← batch {row['batch_id']} "
              f"/ warehouse {warehouse_id} / store {store_id}")

    return True


def _deduct_stock_for_return_reversal(cursor, product_id, variation_id, qty,
                                       warehouse_id, store_id):
    """
    Generic FIFO stock reversal — FALLBACK ONLY.

    Used only when sale_return_product_items has no rows (e.g. old returns
    created before the traceability migration). For new returns always use
    _deduct_stock_via_trace() first.

    WARNING: This function is price-blind — it picks the oldest available
    batch regardless of which price/batch the product was actually sold from.
    For a product sold at multiple prices this can deduct from the wrong batch.
    """
    if not warehouse_id:
        raise Exception(
            f"warehouse_id is required to reverse stock for product {product_id}"
        )

    remaining = float(qty)
    vid = _vid(variation_id)

    if vid:
        cursor.execute("""
            SELECT pb.batch_id,
                   pb.remaining_quantity,
                   ws.id        AS ws_id,
                   ws.quantity  AS ws_qty
            FROM product_batches pb
            INNER JOIN warehouse_stock ws
                ON  pb.batch_id     = ws.batch_id
                AND ws.warehouse_id = %s
                AND ws.product_id   = %s
                AND ws.store_id     = %s
            WHERE pb.product_id   = %s
              AND pb.variation_id = %s
              AND pb.remaining_quantity > 0
              AND ws.quantity > 0
            ORDER BY pb.batch_id ASC
            FOR UPDATE
        """, (warehouse_id, product_id, store_id, product_id, vid))
    else:
        cursor.execute("""
            SELECT pb.batch_id,
                   pb.remaining_quantity,
                   ws.id        AS ws_id,
                   ws.quantity  AS ws_qty
            FROM product_batches pb
            INNER JOIN warehouse_stock ws
                ON  pb.batch_id     = ws.batch_id
                AND ws.warehouse_id = %s
                AND ws.product_id   = %s
                AND ws.store_id     = %s
            WHERE pb.product_id = %s
              AND (pb.variation_id IS NULL OR pb.variation_id = 0)
              AND pb.remaining_quantity > 0
              AND ws.quantity > 0
            ORDER BY pb.batch_id ASC
            FOR UPDATE
        """, (warehouse_id, product_id, store_id, product_id))

    batches = cursor.fetchall()

    if not batches:
        raise Exception(
            f"No stock to reverse for product {product_id}"
            f"{f', variation {vid}' if vid else ''} "
            f"in warehouse {warehouse_id} / store {store_id}."
        )

    for batch in batches:
        if remaining <= 0:
            break

        available  = min(float(batch['remaining_quantity']), float(batch['ws_qty']))
        deduct_now = min(remaining, available)

        cursor.execute("""
            UPDATE product_batches
            SET remaining_quantity = remaining_quantity - %s
            WHERE batch_id = %s
        """, (deduct_now, batch['batch_id']))

        cursor.execute("""
            UPDATE warehouse_stock
            SET quantity = quantity - %s
            WHERE id = %s
        """, (deduct_now, batch['ws_id']))

        print(f"   ↩️  [Generic] Reversed {deduct_now} ← batch {batch['batch_id']} "
              f"/ warehouse {warehouse_id} / store {store_id}")
        remaining -= deduct_now

    if remaining > 0.001:
        raise Exception(
            f"Insufficient stock to reverse for product {product_id}"
            f"{f', variation {vid}' if vid else ''}. "
            f"Short by {remaining:.3f}."
        )


def _reverse_return_stock(cursor, return_id, warehouse_id, store_id,
                           old_items):
    """
    Master reversal function called by both delete and update.

    Strategy:
      1. Try trace-based reversal (_deduct_stock_via_trace) — price-aware,
         uses exact batch rows from sale_return_product_items.
      2. If no trace rows exist (old data), fall back to generic per-product
         FIFO reversal (_deduct_stock_for_return_reversal).

    old_items: list of dicts with keys product_id, variation_id, return_quantity
               (fetched from sale_return_products BEFORE any delete)
    """
    # ── Attempt trace-based reversal first ───────────────────────────────────
    trace_ok = _deduct_stock_via_trace(cursor, return_id, warehouse_id, store_id)

    if trace_ok:
        print(f"   ✅ [Reversal] Trace-based reversal complete for "
              f"return_id={return_id}")
        return

    # ── Fallback: generic per-product reversal ───────────────────────────────
    print(f"   ⚠️  [Reversal] Using generic fallback for return_id={return_id}")
    for item in old_items:
        _deduct_stock_for_return_reversal(
            cursor,
            item['product_id'],
            _vid(item['variation_id']),
            float(item['return_quantity']),
            warehouse_id,
            store_id,
        )


def _restore_stock_for_return_price_aware(
        cursor, product_id, variation_id, return_qty,
        warehouse_id, store_id, invoice_id, unit_price,
        return_id, return_product_id):
    """
    Restore returned stock using 3-tier strategy.

    Tier 1 — Match by unit_price in sale_products → sale_product_items.
    Tier 2 — Broader price-match across all sale_product_items for this invoice.
    Tier 3 — Generic LIFO restore (last resort).

    After every successful batch update a row is inserted into
    sale_return_product_items for full traceability.
    """
    if not warehouse_id:
        raise Exception(
            f"warehouse_id is required to restore stock for product {product_id}"
        )

    remaining = float(return_qty)
    vid = _vid(variation_id)

    # ── Tier 1: Match sale_product by unit_price → get its batch(es) ────────
    print(f"   🔍 [Tier 1] product={product_id}, variation={vid}, "
          f"invoice={invoice_id}, unit_price={unit_price}")

    if vid:
        cursor.execute("""
            SELECT sp.sale_product_id
            FROM sale_products sp
            WHERE sp.invoice_id   = %s
              AND sp.product_id   = %s
              AND sp.variation_id = %s
              AND ABS(COALESCE(sp.unit_price, sp.price, 0) - %s) < 0.01
            ORDER BY sp.sale_product_id DESC
            LIMIT 1
        """, (invoice_id, product_id, vid, float(unit_price)))
    else:
        cursor.execute("""
            SELECT sp.sale_product_id
            FROM sale_products sp
            WHERE sp.invoice_id   = %s
              AND sp.product_id   = %s
              AND (sp.variation_id IS NULL OR sp.variation_id = 0)
              AND ABS(COALESCE(sp.unit_price, sp.price, 0) - %s) < 0.01
            ORDER BY sp.sale_product_id DESC
            LIMIT 1
        """, (invoice_id, product_id, float(unit_price)))

    sp_row = cursor.fetchone()

    if sp_row:
        sale_product_id = sp_row['sale_product_id']
        print(f"   ✅ [Tier 1] Found sale_product_id={sale_product_id} "
              f"matching unit_price={unit_price}")

        cursor.execute("""
            SELECT spi.batch_id,
                   spi.quantity,
                   spi.cost,
                   spi.grn_id,
                   ws.id        AS ws_id,
                   ws.quantity  AS ws_qty,
                   pb.expiration_date
            FROM sale_product_items spi
            INNER JOIN warehouse_stock ws
                ON  spi.batch_id    = ws.batch_id
                AND ws.warehouse_id = %s
                AND ws.product_id   = %s
                AND ws.store_id     = %s
            LEFT JOIN product_batches pb ON spi.batch_id = pb.batch_id
            WHERE spi.sale_product_id = %s
            ORDER BY spi.sale_product_item_id DESC
            FOR UPDATE
        """, (warehouse_id, product_id, store_id, sale_product_id))

        tier1_batches = cursor.fetchall()

        if tier1_batches:
            print(f"   ✅ [Tier 1] Found {len(tier1_batches)} batch(es) "
                  f"via sale_product_items")

            for batch in tier1_batches:
                if remaining <= 0:
                    break

                restore_now = remaining

                cursor.execute("""
                    UPDATE product_batches
                    SET remaining_quantity = remaining_quantity + %s
                    WHERE batch_id = %s
                """, (restore_now, batch['batch_id']))

                if cursor.rowcount != 1:
                    raise Exception(
                        f"Failed to update product_batches "
                        f"for batch {batch['batch_id']}"
                    )

                cursor.execute("""
                    UPDATE warehouse_stock
                    SET quantity = quantity + %s
                    WHERE id = %s
                """, (restore_now, batch['ws_id']))

                if cursor.rowcount != 1:
                    raise Exception(
                        f"Failed to update warehouse_stock "
                        f"for ws_id {batch['ws_id']}"
                    )

                _insert_return_item_batch(
                    cursor, return_id, return_product_id,
                    batch['batch_id'], batch.get('grn_id'),
                    restore_now, batch.get('cost', 0),
                    batch.get('expiration_date')
                )

                print(f"   📦 [Tier 1] Restored {restore_now} → "
                      f"batch {batch['batch_id']} @ unit_price {unit_price}")
                remaining -= restore_now

            if remaining <= 0.001:
                return
        else:
            print(f"   ⚠️ [Tier 1] sale_product_id={sale_product_id} found "
                  f"but no sale_product_items with warehouse_stock match")
    else:
        print(f"   ⚠️ [Tier 1] No sale_products row found matching "
              f"unit_price={unit_price} for product={product_id}")

    # ── Tier 2: Broader search — all sale_product_items for this invoice ─────
    print(f"   🔍 [Tier 2] Broader price-match search for product={product_id}, "
          f"unit_price={unit_price}")

    if vid:
        cursor.execute("""
            SELECT spi.batch_id,
                   spi.quantity,
                   spi.cost,
                   spi.grn_id,
                   ws.id        AS ws_id,
                   ws.quantity  AS ws_qty,
                   pb.expiration_date
            FROM sale_products sp
            JOIN sale_product_items spi
                ON  spi.sale_product_id = sp.sale_product_id
            INNER JOIN warehouse_stock ws
                ON  spi.batch_id    = ws.batch_id
                AND ws.warehouse_id = %s
                AND ws.product_id   = %s
                AND ws.store_id     = %s
            LEFT JOIN product_batches pb ON spi.batch_id = pb.batch_id
            WHERE sp.invoice_id   = %s
              AND sp.product_id   = %s
              AND sp.variation_id = %s
              AND ABS(COALESCE(sp.unit_price, sp.price, 0) - %s) < 0.01
            ORDER BY spi.sale_product_item_id DESC
            FOR UPDATE
        """, (warehouse_id, product_id, store_id,
              invoice_id, product_id, vid, float(unit_price)))
    else:
        cursor.execute("""
            SELECT spi.batch_id,
                   spi.quantity,
                   spi.cost,
                   spi.grn_id,
                   ws.id        AS ws_id,
                   ws.quantity  AS ws_qty,
                   pb.expiration_date
            FROM sale_products sp
            JOIN sale_product_items spi
                ON  spi.sale_product_id = sp.sale_product_id
            INNER JOIN warehouse_stock ws
                ON  spi.batch_id    = ws.batch_id
                AND ws.warehouse_id = %s
                AND ws.product_id   = %s
                AND ws.store_id     = %s
            LEFT JOIN product_batches pb ON spi.batch_id = pb.batch_id
            WHERE sp.invoice_id   = %s
              AND sp.product_id   = %s
              AND (sp.variation_id IS NULL OR sp.variation_id = 0)
              AND ABS(COALESCE(sp.unit_price, sp.price, 0) - %s) < 0.01
            ORDER BY spi.sale_product_item_id DESC
            FOR UPDATE
        """, (warehouse_id, product_id, store_id,
              invoice_id, product_id, float(unit_price)))

    tier2_batches = cursor.fetchall()

    if tier2_batches:
        print(f"   ✅ [Tier 2] Found {len(tier2_batches)} batch(es) "
              f"via broader price-match")

        for batch in tier2_batches:
            if remaining <= 0:
                break

            restore_now = remaining

            cursor.execute("""
                UPDATE product_batches
                SET remaining_quantity = remaining_quantity + %s
                WHERE batch_id = %s
            """, (restore_now, batch['batch_id']))

            cursor.execute("""
                UPDATE warehouse_stock
                SET quantity = quantity + %s
                WHERE id = %s
            """, (restore_now, batch['ws_id']))

            _insert_return_item_batch(
                cursor, return_id, return_product_id,
                batch['batch_id'], batch.get('grn_id'),
                restore_now, batch.get('cost', 0),
                batch.get('expiration_date')
            )

            print(f"   📦 [Tier 2] Restored {restore_now} → "
                  f"batch {batch['batch_id']} @ unit_price {unit_price}")
            remaining -= restore_now

        if remaining <= 0.001:
            return
    else:
        print(f"   ⚠️ [Tier 2] No batches found via broader price-match")

    # ── Tier 3: Generic LIFO restore (last resort) ───────────────────────────
    print(f"   ⚠️ [Tier 3] Generic LIFO fallback for product={product_id}, "
          f"remaining={remaining}")
    _restore_stock_generic(
        cursor, product_id, vid, remaining, warehouse_id, store_id,
        return_id, return_product_id
    )


def _restore_stock_generic(cursor, product_id, variation_id, return_qty,
                            warehouse_id, store_id,
                            return_id=None, return_product_id=None):
    """
    Generic LIFO stock restore — Tier 3 fallback.
    """
    if not warehouse_id:
        raise Exception(
            f"warehouse_id is required to restore stock for product {product_id}"
        )

    remaining = float(return_qty)
    vid = _vid(variation_id)

    if vid:
        cursor.execute("""
            SELECT pb.batch_id,
                   pb.remaining_quantity,
                   pb.cost,
                   pb.grn_id,
                   pb.expiration_date,
                   ws.id        AS ws_id,
                   ws.quantity  AS ws_qty
            FROM product_batches pb
            INNER JOIN warehouse_stock ws
                ON  pb.batch_id     = ws.batch_id
                AND ws.warehouse_id = %s
                AND ws.product_id   = %s
                AND ws.store_id     = %s
            WHERE pb.product_id   = %s
              AND pb.variation_id = %s
            ORDER BY pb.batch_id DESC
            FOR UPDATE
        """, (warehouse_id, product_id, store_id, product_id, vid))
    else:
        cursor.execute("""
            SELECT pb.batch_id,
                   pb.remaining_quantity,
                   pb.cost,
                   pb.grn_id,
                   pb.expiration_date,
                   ws.id        AS ws_id,
                   ws.quantity  AS ws_qty
            FROM product_batches pb
            INNER JOIN warehouse_stock ws
                ON  pb.batch_id     = ws.batch_id
                AND ws.warehouse_id = %s
                AND ws.product_id   = %s
                AND ws.store_id     = %s
            WHERE pb.product_id = %s
              AND (pb.variation_id IS NULL OR pb.variation_id = 0)
            ORDER BY pb.batch_id DESC
            FOR UPDATE
        """, (warehouse_id, product_id, store_id, product_id))

    batches = cursor.fetchall()

    if not batches:
        if vid:
            cursor.execute("""
                SELECT batch_id, cost, grn_id, expiration_date
                FROM product_batches
                WHERE product_id   = %s
                  AND variation_id = %s
                ORDER BY batch_id DESC LIMIT 1
            """, (product_id, vid))
        else:
            cursor.execute("""
                SELECT batch_id, cost, grn_id, expiration_date
                FROM product_batches
                WHERE product_id = %s
                  AND (variation_id IS NULL OR variation_id = 0)
                ORDER BY batch_id DESC LIMIT 1
            """, (product_id,))

        fallback = cursor.fetchone()
        if not fallback:
            raise Exception(
                f"No product_batches row found for product {product_id}"
                f"{f', variation {vid}' if vid else ''}. "
                f"Cannot restore {return_qty} units."
            )

        batch_id = fallback['batch_id']

        cursor.execute("""
            UPDATE product_batches
            SET remaining_quantity = remaining_quantity + %s
            WHERE batch_id = %s
        """, (remaining, batch_id))

        cursor.execute("""
            INSERT INTO warehouse_stock
                (store_id, product_id, warehouse_id, variation_id, batch_id, quantity)
            VALUES (%s, %s, %s, %s, %s, %s)
            ON DUPLICATE KEY UPDATE quantity = quantity + %s
        """, (store_id, product_id, warehouse_id, vid,
              batch_id, remaining, remaining))

        if return_id and return_product_id:
            _insert_return_item_batch(
                cursor, return_id, return_product_id,
                batch_id, fallback.get('grn_id'),
                remaining, fallback.get('cost', 0),
                fallback.get('expiration_date')
            )

        print(f"   📦 [Tier 3 Fallback] Restored {remaining} → "
              f"batch {batch_id} / warehouse {warehouse_id}")
        return

    for batch in batches:
        if remaining <= 0:
            break

        restore_now = remaining

        cursor.execute("""
            UPDATE product_batches
            SET remaining_quantity = remaining_quantity + %s
            WHERE batch_id = %s
        """, (restore_now, batch['batch_id']))

        cursor.execute("""
            UPDATE warehouse_stock
            SET quantity = quantity + %s
            WHERE id = %s
        """, (restore_now, batch['ws_id']))

        if return_id and return_product_id:
            _insert_return_item_batch(
                cursor, return_id, return_product_id,
                batch['batch_id'], batch.get('grn_id'),
                restore_now, batch.get('cost', 0),
                batch.get('expiration_date')
            )

        print(f"   📦 [Tier 3] Restored {restore_now} → "
              f"batch {batch['batch_id']} / warehouse {warehouse_id}")
        remaining -= restore_now

    if remaining > 0.001:
        raise Exception(
            f"Could not fully restore stock for product {product_id}"
            f"{f', variation {vid}' if vid else ''}. "
            f"Unrestored: {remaining:.3f}"
        )


# ============================================================
# ENDPOINTS
# ============================================================

@sale_return_bp.route('/create_sale_return', methods=['POST'])
@jwt_required()
@role_required('admin', 'cashier')
def create_sale_return():
    data = request.get_json()

    invoice_id          = data.get('invoice_id')
    reason              = data.get('reason', '')
    total_return_amount = float(data.get('total_return_amount', 0.0))
    order_tax           = float(data.get('order_tax', 0.0))
    order_discount      = float(data.get('order_discount', 0.0))
    status              = data.get('status', 'received')
    return_items        = data.get('products', [])

    if not invoice_id or not return_items:
        return jsonify({"error": "Missing required fields"}), 400

    conn   = None
    cursor = None
    try:
        conn   = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        cursor.execute("SET TRANSACTION ISOLATION LEVEL READ COMMITTED")
        cursor.execute("START TRANSACTION")

        cursor.execute("""
            SELECT srp.product_id,
                   srp.variation_id,
                   SUM(srp.return_quantity) AS total_returned
            FROM sale_return_products srp
            JOIN sale_return sr ON srp.return_id = sr.return_id
            WHERE sr.invoice_id = %s
            GROUP BY srp.product_id, srp.variation_id
        """, (invoice_id,))
        already_returned = {
            (int(r['product_id']), _vid(r['variation_id'])): float(r['total_returned'])
            for r in cursor.fetchall()
        }

        cursor.execute("""
            SELECT product_id, variation_id, SUM(quantity) AS total_sold
            FROM sale_products
            WHERE invoice_id = %s
            GROUP BY product_id, variation_id
        """, (invoice_id,))
        sold_map = {
            (int(r['product_id']), _vid(r['variation_id'])): float(r['total_sold'])
            for r in cursor.fetchall()
        }

        for item in return_items:
            pid  = int(item['product_id'])
            vid  = _vid(item.get('variation_id'))
            rqty = float(item['return_quantity'])
            key  = (pid, vid)

            sold_qty     = sold_map.get(key, 0)
            returned_qty = already_returned.get(key, 0.0)
            remaining    = sold_qty - returned_qty

            if rqty > remaining + 0.001:
                conn.rollback()
                return jsonify({
                    "error": f"Return quantity ({rqty}) exceeds remaining "
                             f"returnable quantity ({remaining:.2f}) "
                             f"for product {pid}"
                             f"{f', variation {vid}' if vid else ''}."
                }), 400

        cursor.execute("""
            SELECT warehouse_id, store_id, customer_id
            FROM invoice_sale
            WHERE invoice_id = %s
        """, (invoice_id,))
        invoice_info = cursor.fetchone()
        if not invoice_info:
            conn.rollback()
            return jsonify({"error": "Invoice not found"}), 404

        warehouse_id = invoice_info['warehouse_id']
        store_id     = invoice_info['store_id']
        customer_id  = invoice_info['customer_id']

        cursor.execute("""
            INSERT INTO sale_return (
                invoice_id, return_date, total_return_amount, reason,
                order_tax, order_discount, status,
                warehouse_id, store_id, customer_id
            ) VALUES (%s, NOW(), %s, %s, %s, %s, %s, %s, %s, %s)
        """, (
            invoice_id, total_return_amount, reason,
            order_tax, order_discount, status,
            warehouse_id, store_id, customer_id,
        ))
        return_id = cursor.lastrowid

        for item in return_items:
            product_id   = int(item['product_id'])
            variation_id = _vid(item.get('variation_id'))
            return_qty   = float(item['return_quantity'])
            return_total = float(item.get('return_total', 0.0))
            unit_price   = float(item.get('unit_price', 0.0))

            cursor.execute("""
                INSERT INTO sale_return_products (
                    return_id, product_id, variation_id, store_id, customer_id,
                    return_quantity, return_price, return_total,
                    product_discount, discount_type, product_tax, tax_type,
                    tax, discount, unit_price, sale_unit
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s,
                          %s, %s, %s, %s, %s, %s, %s, %s)
            """, (
                return_id, product_id, variation_id, store_id, customer_id,
                return_qty,
                float(item.get('return_price', 0.0)),
                return_total,
                float(item.get('product_discount', 0.0)),
                item.get('discount_type', ''),
                float(item.get('product_tax', 0.0)),
                item.get('tax_type', ''),
                float(item.get('tax', 0.0)),
                float(item.get('discount', 0.0)),
                unit_price,
                item.get('sale_unit', ''),
            ))
            return_product_id = cursor.lastrowid

            if status.lower() == 'received':
                try:
                    _restore_stock_for_return_price_aware(
                        cursor, product_id, variation_id, return_qty,
                        warehouse_id, store_id, invoice_id, unit_price,
                        return_id, return_product_id,
                    )
                except Exception as stock_err:
                    conn.rollback()
                    return jsonify({
                        "error": f"Stock restoration failed for product "
                                 f"{product_id}: {str(stock_err)}"
                    }), 400

        conn.commit()
        return jsonify({
            "message":      "Sale return created successfully",
            "return_id":    return_id,
            "warehouse_id": warehouse_id,
            "store_id":     store_id,
        }), 201

    except Exception as e:
        if conn:
            conn.rollback()
        print("❌ Error creating sale return:", e)
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500
    finally:
        if cursor: cursor.close()
        if conn:   conn.close()


@sale_return_bp.route('/check_sale_return/<int:invoice_id>', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier')
def check_sale_return(invoice_id):
    conn   = None
    cursor = None
    try:
        conn   = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        cursor.execute("""
            SELECT srp.product_id,
                   srp.variation_id,
                   SUM(srp.return_quantity) AS total_returned
            FROM sale_return_products srp
            JOIN sale_return sr ON srp.return_id = sr.return_id
            WHERE sr.invoice_id = %s
            GROUP BY srp.product_id, srp.variation_id
        """, (invoice_id,))
        returned_items = cursor.fetchall()

        cursor.execute("""
            SELECT product_id, variation_id, SUM(quantity) AS total_sold
            FROM sale_products
            WHERE invoice_id = %s
            GROUP BY product_id, variation_id
        """, (invoice_id,))
        sold_items = cursor.fetchall()

        sold_map = {
            (int(r['product_id']), _vid(r['variation_id'])): float(r['total_sold'])
            for r in sold_items
        }
        returned_map = {
            (int(r['product_id']), _vid(r['variation_id'])): float(r['total_returned'])
            for r in returned_items
        }

        can_return = any(
            sold_qty - returned_map.get(key, 0.0) > 0
            for key, sold_qty in sold_map.items()
        )

        return jsonify({
            "can_return": can_return,
            "returned_items": [
                {
                    "product_id":     r['product_id'],
                    "variation_id":   _vid(r['variation_id']),
                    "total_returned": float(r['total_returned'])
                }
                for r in returned_items
            ]
        }), 200

    except Exception as e:
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500
    finally:
        if cursor: cursor.close()
        if conn:   conn.close()


@sale_return_bp.route('/view_all_sale_returns', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier')
def view_all_sale_returns():
    conn   = None
    cursor = None
    try:
        conn   = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        invoice_id_filter = request.args.get('invoice_id')
        date_filter = request.args.get('dateFilter')
        start_date  = request.args.get('startDate')
        end_date    = request.args.get('endDate')
        today       = datetime.now().date()

        base_query = """
            SELECT
                sr.return_id,
                sr.invoice_id,
                sr.return_date,
                sr.total_return_amount,
                sr.reason,
                sr.order_tax,
                sr.order_discount,
                sr.status,
                sr.created_at,
                sr.warehouse_id,
                sr.store_id,
                sr.customer_id,
                inv.invoice_code,
                c.name           AS customer_name,
                w.warehouse_name,
                s.store_name
            FROM sale_return sr
            JOIN  invoice_sale inv ON sr.invoice_id  = inv.invoice_id
            LEFT JOIN customers  c ON sr.customer_id = c.id
            LEFT JOIN warehouses w ON sr.warehouse_id = w.id
            LEFT JOIN stores     s ON sr.store_id     = s.id
        """

        conditions, params = [], []

        if invoice_id_filter:
            conditions.append("sr.invoice_id = %s")
            params.append(int(invoice_id_filter))

        if date_filter and date_filter != 'allTime':
            if date_filter == 'today':
                conditions.append("DATE(sr.return_date) = %s")
                params.append(today)
            elif date_filter == 'yesterday':
                conditions.append("DATE(sr.return_date) = %s")
                params.append(today - timedelta(days=1))
            elif date_filter == 'thisWeek':
                start_of_week = today - timedelta(days=today.weekday())
                conditions.append("DATE(sr.return_date) BETWEEN %s AND %s")
                params.extend([start_of_week, start_of_week + timedelta(days=6)])
            elif date_filter == 'lastWeek':
                s = today - timedelta(days=today.weekday() + 7)
                conditions.append("DATE(sr.return_date) BETWEEN %s AND %s")
                params.extend([s, s + timedelta(days=6)])
            elif date_filter == 'thisMonth':
                sm = today.replace(day=1)
                em = (today.replace(day=28) + timedelta(days=4)).replace(day=1) \
                     - timedelta(days=1)
                conditions.append("DATE(sr.return_date) BETWEEN %s AND %s")
                params.extend([sm, em])
            elif date_filter == 'lastMonth':
                first_this = today.replace(day=1)
                end_last   = first_this - timedelta(days=1)
                conditions.append("DATE(sr.return_date) BETWEEN %s AND %s")
                params.extend([end_last.replace(day=1), end_last])
            elif date_filter == 'customRange' and start_date and end_date:
                try:
                    s = datetime.strptime(start_date, "%Y-%m-%d").date()
                    e = datetime.strptime(end_date,   "%Y-%m-%d").date()
                    conditions.append("DATE(sr.return_date) BETWEEN %s AND %s")
                    params.extend([s, e])
                except ValueError:
                    return jsonify({"error": "Invalid custom date format"}), 400

        if conditions:
            base_query += " WHERE " + " AND ".join(conditions)
        base_query += " ORDER BY sr.return_date DESC"

        cursor.execute(base_query, params)
        returns = cursor.fetchall()

        for r in returns:
            if r.get('return_date') and isinstance(r['return_date'], datetime):
                r['return_date'] = r['return_date'].strftime('%Y-%m-%d %H:%M:%S')
            if r.get('created_at') and isinstance(r['created_at'], datetime):
                r['created_at'] = r['created_at'].strftime('%Y-%m-%d %H:%M:%S')

            cursor.execute("""
                SELECT
                    srp.return_product_id,
                    srp.return_id,
                    srp.product_id,
                    srp.variation_id,
                    srp.store_id,
                    srp.customer_id,
                    srp.return_quantity,
                    srp.return_price,
                    srp.return_total,
                    srp.product_discount,
                    srp.discount_type,
                    srp.product_tax,
                    srp.tax_type,
                    srp.tax,
                    srp.discount,
                    srp.unit_price,
                    srp.sale_unit,
                    p.product_name,
                    p.sku          AS product_sku,
                    pv.variation_name,
                    pv.variation_type,
                    pv.variation_sku
                FROM sale_return_products srp
                JOIN products p ON srp.product_id = p.id
                LEFT JOIN product_variations pv ON srp.variation_id = pv.id
                WHERE srp.return_id = %s
            """, (r['return_id'],))
            r['products'] = cursor.fetchall()

        return jsonify(returns), 200

    except Exception as e:
        print("❌ Error in view_all_sale_returns:", e)
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500
    finally:
        if cursor: cursor.close()
        if conn:   conn.close()


@sale_return_bp.route('/view_sale_return/<int:return_id>', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier')
def view_sale_return(return_id):
    conn   = None
    cursor = None
    try:
        conn   = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        cursor.execute("""
            SELECT
                sr.return_id,
                sr.invoice_id,
                sr.return_date,
                sr.total_return_amount,
                sr.reason,
                sr.order_tax,
                sr.order_discount,
                sr.status,
                sr.created_at,
                sr.warehouse_id,
                sr.store_id,
                sr.customer_id,
                inv.invoice_code,
                c.name      AS customer_name,
                c.dob,
                c.address,
                c.contact,
                w.warehouse_name,
                s.store_name
            FROM sale_return sr
            JOIN  invoice_sale inv ON sr.invoice_id  = inv.invoice_id
            LEFT JOIN customers  c ON sr.customer_id = c.id
            LEFT JOIN warehouses w ON sr.warehouse_id = w.id
            LEFT JOIN stores     s ON sr.store_id     = s.id
            WHERE sr.return_id = %s
        """, (return_id,))
        return_data = cursor.fetchone()

        if not return_data:
            return jsonify({"error": "Sale return not found"}), 404

        if return_data.get('return_date') and \
                isinstance(return_data['return_date'], datetime):
            return_data['return_date'] = \
                return_data['return_date'].strftime('%Y-%m-%d %H:%M:%S')
        if return_data.get('created_at') and \
                isinstance(return_data['created_at'], datetime):
            return_data['created_at'] = \
                return_data['created_at'].strftime('%Y-%m-%d %H:%M:%S')

        if return_data.get('dob'):
            dob   = return_data['dob']
            today = date.today()
            age   = today.year - dob.year - (
                (today.month, today.day) < (dob.month, dob.day)
            )
            return_data['age'] = age
            return_data['dob'] = dob.strftime('%Y-%m-%d')
        else:
            return_data['age'] = None

        invoice_id = return_data['invoice_id']

        cursor.execute("""
            SELECT
                srp.return_product_id,
                srp.return_id,
                srp.product_id,
                srp.variation_id,
                srp.store_id,
                srp.customer_id,
                srp.return_quantity,
                srp.return_price,
                srp.return_total,
                srp.product_discount,
                srp.discount_type,
                srp.product_tax,
                srp.tax_type,
                srp.tax,
                srp.discount,
                srp.unit_price,
                srp.sale_unit,
                p.product_name,
                p.sku           AS product_sku,
                pv.variation_name,
                pv.variation_type,
                pv.variation_sku,
                (
                    SELECT sp2.quantity
                    FROM sale_products sp2
                    WHERE sp2.invoice_id  = %s
                      AND sp2.product_id  = srp.product_id
                      AND (
                          sp2.variation_id = srp.variation_id
                          OR (sp2.variation_id IS NULL AND srp.variation_id IS NULL)
                          OR (sp2.variation_id = 0    AND srp.variation_id IS NULL)
                          OR (sp2.variation_id IS NULL AND srp.variation_id = 0)
                      )
                      AND ABS(COALESCE(sp2.unit_price, sp2.price, 0)
                              - COALESCE(srp.unit_price, 0)) < 0.01
                    ORDER BY sp2.sale_product_id DESC
                    LIMIT 1
                ) AS sold_quantity,
                (
                    SELECT sp2.sale_unit
                    FROM sale_products sp2
                    WHERE sp2.invoice_id  = %s
                      AND sp2.product_id  = srp.product_id
                      AND (
                          sp2.variation_id = srp.variation_id
                          OR (sp2.variation_id IS NULL AND srp.variation_id IS NULL)
                          OR (sp2.variation_id = 0    AND srp.variation_id IS NULL)
                          OR (sp2.variation_id IS NULL AND srp.variation_id = 0)
                      )
                      AND ABS(COALESCE(sp2.unit_price, sp2.price, 0)
                              - COALESCE(srp.unit_price, 0)) < 0.01
                    ORDER BY sp2.sale_product_id DESC
                    LIMIT 1
                ) AS sold_unit
            FROM sale_return_products srp
            JOIN products p
                ON  srp.product_id = p.id
            LEFT JOIN product_variations pv
                ON  srp.variation_id = pv.id
            WHERE srp.return_id = %s
            ORDER BY srp.return_product_id ASC
        """, (invoice_id, invoice_id, return_id))

        return_data['products'] = cursor.fetchall()

        return jsonify(return_data), 200

    except Exception as e:
        print("❌ Error in view_sale_return:", e)
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500
    finally:
        if cursor: cursor.close()
        if conn:   conn.close()


@sale_return_bp.route('/update_sale_return/<int:return_id>', methods=['PUT'])
@jwt_required()
@role_required('admin', 'cashier')
def update_sale_return(return_id):
    data = request.get_json()
    print(f"\n📦 Updating Sale Return ID: {return_id}")

    invoice_id          = data.get('invoice_id')
    reason              = data.get('reason', '')
    total_return_amount = float(data.get('total_return_amount', 0.0))
    order_tax           = float(data.get('order_tax', 0.0))
    order_discount      = float(data.get('order_discount', 0.0))
    status              = data.get('status', 'received').lower()
    return_items        = data.get('products', [])

    if not invoice_id or not return_items:
        return jsonify({"error": "Missing required fields"}), 400

    conn   = None
    cursor = None
    try:
        conn   = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        cursor.execute("SET TRANSACTION ISOLATION LEVEL READ COMMITTED")
        cursor.execute("START TRANSACTION")

        cursor.execute("""
            SELECT status, warehouse_id, store_id
            FROM sale_return
            WHERE return_id = %s
        """, (return_id,))
        row = cursor.fetchone()
        if not row:
            conn.rollback()
            return jsonify({"error": "Sale return not found"}), 404

        old_status   = row['status'].lower()
        warehouse_id = row['warehouse_id']
        store_id     = row['store_id']

        # ── Step 1: Fetch old items BEFORE any delete (cascade will kill trace rows) ──
        if old_status == 'received':
            cursor.execute("""
                SELECT product_id, variation_id, return_quantity
                FROM sale_return_products
                WHERE return_id = %s
            """, (return_id,))
            old_items = cursor.fetchall()

            # ── Step 2: Reverse stock via trace (price-aware) ─────────────────
            try:
                _reverse_return_stock(
                    cursor, return_id, warehouse_id, store_id, old_items
                )
            except Exception as e:
                conn.rollback()
                return jsonify({
                    "error": f"Failed to reverse stock: {str(e)}"
                }), 400

        # ── Step 3: Update header ─────────────────────────────────────────────
        cursor.execute("""
            UPDATE sale_return
            SET invoice_id          = %s,
                total_return_amount = %s,
                reason              = %s,
                return_date         = NOW(),
                order_tax           = %s,
                order_discount      = %s,
                status              = %s
            WHERE return_id = %s
        """, (
            invoice_id, total_return_amount, reason,
            order_tax, order_discount, status,
            return_id,
        ))

        cursor.execute("""
            SELECT customer_id FROM sale_return WHERE return_id = %s
        """, (return_id,))
        customer_row = cursor.fetchone()
        customer_id  = customer_row['customer_id'] if customer_row else None

        # ── Step 4: Delete old product rows (cascades to sale_return_product_items) ──
        cursor.execute("""
            DELETE FROM sale_return_products WHERE return_id = %s
        """, (return_id,))

        # ── Step 4b: Validate new return quantities against available ──────────
        # After deleting old rows, "already returned" excludes THIS return,
        # so remaining = sold - other_returns.
        cursor.execute("""
            SELECT srp.product_id,
                   srp.variation_id,
                   SUM(srp.return_quantity) AS total_returned
            FROM sale_return_products srp
            JOIN sale_return sr ON srp.return_id = sr.return_id
            WHERE sr.invoice_id = %s
            GROUP BY srp.product_id, srp.variation_id
        """, (invoice_id,))
        already_returned = {
            (int(r['product_id']), _vid(r['variation_id'])): float(r['total_returned'])
            for r in cursor.fetchall()
        }

        cursor.execute("""
            SELECT product_id, variation_id, SUM(quantity) AS total_sold
            FROM sale_products
            WHERE invoice_id = %s
            GROUP BY product_id, variation_id
        """, (invoice_id,))
        sold_map = {
            (int(r['product_id']), _vid(r['variation_id'])): float(r['total_sold'])
            for r in cursor.fetchall()
        }

        for item in return_items:
            pid  = int(item['product_id'])
            vid  = _vid(item.get('variation_id'))
            rqty = float(item['return_quantity'])
            key  = (pid, vid)

            sold_qty     = sold_map.get(key, 0)
            returned_qty = already_returned.get(key, 0.0)
            remaining    = sold_qty - returned_qty

            if rqty > remaining + 0.001:
                conn.rollback()
                return jsonify({
                    "error": f"Return quantity ({rqty}) exceeds remaining "
                             f"returnable quantity ({remaining:.2f}) "
                             f"for product {pid}"
                             f"{f', variation {vid}' if vid else ''}."
                }), 400

        # ── Step 5: Insert new product rows + restore stock if 'received' ────
        for item in return_items:
            product_id   = int(item['product_id'])
            variation_id = _vid(item.get('variation_id'))
            return_qty   = float(item['return_quantity'])
            unit_price   = float(item.get('unit_price', 0.0))

            cursor.execute("""
                INSERT INTO sale_return_products (
                    return_id, product_id, variation_id, store_id, customer_id,
                    return_quantity, return_price, return_total,
                    product_discount, discount_type, product_tax, tax_type,
                    tax, discount, unit_price, sale_unit
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s,
                          %s, %s, %s, %s, %s, %s, %s, %s)
            """, (
                return_id, product_id, variation_id, store_id, customer_id,
                return_qty,
                float(item.get('return_price', 0.0)),
                float(item.get('return_total', 0.0)),
                float(item.get('product_discount', 0.0)),
                item.get('discount_type', ''),
                float(item.get('product_tax', 0.0)),
                item.get('tax_type', ''),
                float(item.get('tax', 0.0)),
                float(item.get('discount', 0.0)),
                unit_price,
                item.get('sale_unit', ''),
            ))
            return_product_id = cursor.lastrowid

            if status == 'received':
                try:
                    _restore_stock_for_return_price_aware(
                        cursor, product_id, variation_id, return_qty,
                        warehouse_id, store_id, invoice_id, unit_price,
                        return_id, return_product_id,
                    )
                except Exception as e:
                    conn.rollback()
                    return jsonify({
                        "error": f"Stock restoration failed for product "
                                 f"{product_id}: {str(e)}"
                    }), 400

        conn.commit()
        print("✅ Sale return updated successfully.")
        return jsonify({
            "message":      "Sale return updated successfully",
            "return_id":    return_id,
            "warehouse_id": warehouse_id,
            "store_id":     store_id,
        }), 200

    except Exception as e:
        if conn:
            conn.rollback()
        print("❌ Error updating sale return:", e)
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500
    finally:
        if cursor: cursor.close()
        if conn:   conn.close()


@sale_return_bp.route('/delete_sale_return/<int:return_id>', methods=['DELETE'])
@jwt_required()
@role_required('admin', 'cashier')
def delete_sale_return(return_id):
    conn   = None
    cursor = None
    try:
        conn   = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        cursor.execute("SET TRANSACTION ISOLATION LEVEL READ COMMITTED")
        cursor.execute("START TRANSACTION")

        cursor.execute("""
            SELECT status, warehouse_id, store_id
            FROM sale_return
            WHERE return_id = %s
        """, (return_id,))
        sale_return = cursor.fetchone()

        if not sale_return:
            conn.rollback()
            return jsonify({"error": "Sale return not found"}), 404

        old_status   = sale_return['status'].lower()
        warehouse_id = sale_return['warehouse_id']
        store_id     = sale_return['store_id']

        if old_status == 'received':
            # ── Step 1: Fetch old items BEFORE delete ─────────────────────────
            cursor.execute("""
                SELECT product_id, variation_id, return_quantity
                FROM sale_return_products
                WHERE return_id = %s
            """, (return_id,))
            old_items = cursor.fetchall()

            # ── Step 2: Reverse stock via trace (price-aware) ─────────────────
            try:
                _reverse_return_stock(
                    cursor, return_id, warehouse_id, store_id, old_items
                )
            except Exception as e:
                conn.rollback()
                return jsonify({
                    "error": f"Stock reversal failed: {str(e)}"
                }), 400

        # ── Step 3: Delete (cascade removes sale_return_product_items too) ────
        cursor.execute("""
            DELETE FROM sale_return_products WHERE return_id = %s
        """, (return_id,))
        cursor.execute("""
            DELETE FROM sale_return WHERE return_id = %s
        """, (return_id,))

        conn.commit()
        print("✅ Sale return deleted with correct stock reversal.")
        return jsonify({
            "message":      "Sale return deleted successfully",
            "return_id":    return_id,
            "warehouse_id": warehouse_id,
            "store_id":     store_id,
        }), 200

    except Exception as e:
        if conn:
            conn.rollback()
        print("❌ Error deleting sale return:", e)
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500
    finally:
        if cursor: cursor.close()
        if conn:   conn.close()


@sale_return_bp.route('/get_invoice_products_for_return/<int:invoice_id>', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier')
def get_invoice_products_for_return(invoice_id):
    conn   = None
    cursor = None
    try:
        conn   = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        cursor.execute("""
            SELECT
                sp.sale_product_id,
                sp.product_id,
                sp.variation_id,
                sp.warehouse_id,
                sp.quantity                                          AS sold_quantity,
                COALESCE(sp.unit_price, sp.price, 0)                AS unit_price,
                COALESCE(sp.our_price, sp.unit_price, sp.price, 0)  AS our_price,
                sp.price                                             AS market_price,
                sp.total,
                sp.discount,
                sp.discount_type,
                sp.product_discount,
                sp.tax,
                sp.tax_type,
                sp.product_tax,
                sp.sale_unit,
                p.product_name,
                p.sku                                                AS product_sku,
                pv.variation_name,
                pv.variation_type,
                pv.variation_sku
            FROM sale_products sp
            JOIN products p ON sp.product_id = p.id
            LEFT JOIN product_variations pv ON sp.variation_id = pv.id
            WHERE sp.invoice_id = %s
            ORDER BY sp.sale_product_id ASC
        """, (invoice_id,))
        sold_rows = cursor.fetchall()

        if not sold_rows:
            return jsonify({"error": "Invoice not found or has no products"}), 404

        cursor.execute("""
            SELECT
                srp.product_id,
                srp.variation_id,
                COALESCE(srp.unit_price, 0) AS unit_price,
                SUM(srp.return_quantity)    AS total_returned
            FROM sale_return_products srp
            JOIN sale_return sr ON srp.return_id = sr.return_id
            WHERE sr.invoice_id = %s
            GROUP BY srp.product_id, srp.variation_id, srp.unit_price
        """, (invoice_id,))

        returned_map = {}
        for r in cursor.fetchall():
            key = (int(r['product_id']),
                   _vid(r['variation_id']),
                   float(r['unit_price']))
            returned_map[key] = float(r['total_returned'])

        products = []
        for sp in sold_rows:
            pid        = int(sp['product_id'])
            vid        = _vid(sp['variation_id'])
            unit_price = float(sp['unit_price'])
            sold_qty   = float(sp['sold_quantity'])

            already_returned = returned_map.get((pid, vid, unit_price), 0.0)
            returnable_qty   = max(0.0, sold_qty - already_returned)

            products.append({
                "sale_product_id":     sp['sale_product_id'],
                "product_id":          pid,
                "variation_id":        vid,
                "warehouse_id":        sp['warehouse_id'],
                "sold_quantity":       sold_qty,
                "already_returned":    already_returned,
                "returnable_quantity": returnable_qty,
                "unit_price":          unit_price,
                "our_price":           float(sp['our_price']),
                "market_price":        float(sp['market_price']),
                "total":               float(sp['total']),
                "discount":            float(sp['discount'] or 0),
                "discount_type":       sp['discount_type'],
                "product_discount":    float(sp['product_discount'] or 0),
                "tax":                 float(sp['tax'] or 0),
                "tax_type":            sp['tax_type'],
                "product_tax":         float(sp['product_tax'] or 0),
                "sale_unit":           sp['sale_unit'],
                "product_name":        sp['product_name'],
                "product_sku":         sp.get('variation_sku') or sp['product_sku'],
                "variation_name":      sp.get('variation_name'),
                "variation_type":      sp.get('variation_type'),
            })

        cursor.execute("""
            SELECT i.warehouse_id, i.store_id, i.customer_id,
                   c.name AS customer_name, w.warehouse_name, s.store_name
            FROM invoice_sale i
            LEFT JOIN customers  c ON i.customer_id  = c.id
            LEFT JOIN warehouses w ON i.warehouse_id = w.id
            LEFT JOIN stores     s ON i.store_id     = s.id
            WHERE i.invoice_id = %s
        """, (invoice_id,))
        inv = cursor.fetchone()

        return jsonify({
            "invoice_id":     invoice_id,
            "warehouse_id":   inv['warehouse_id']   if inv else None,
            "store_id":       inv['store_id']       if inv else None,
            "customer_id":    inv['customer_id']    if inv else None,
            "customer_name":  inv['customer_name']  if inv else None,
            "warehouse_name": inv['warehouse_name'] if inv else None,
            "store_name":     inv['store_name']     if inv else None,
            "products":       products,
        }), 200

    except Exception as e:
        print("❌ Error in get_invoice_products_for_return:", e)
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500
    finally:
        if cursor: cursor.close()
        if conn:   conn.close()