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
import json
from datetime import date

sale_return_bp = Blueprint('sale_return', __name__)


# ============================================================
# STOCK HELPERS — scoped to sale_return, no sale.py imports
# ============================================================

def _restore_stock_for_return(cursor, product_id, variation_id, return_qty, warehouse_id, store_id):
    """
    Restore returned stock back into product_batches + warehouse_stock.

    Strategy (LIFO on batch_id — newest batch first, mirrors FIFO deduction):
      1. Find batches that belong to this product/variation in this warehouse.
      2. Add the returned quantity back, spreading across batches newest-first.
      3. If a warehouse_stock row is missing (edge case), re-create it.

    This does NOT create sale_product_items entries — sale returns
    are not original sales, so we just top-up the batches.
    """
    if not warehouse_id:
        raise Exception(f"warehouse_id is required to restore stock for product {product_id}")

    remaining = float(return_qty)

    if variation_id:
        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
            WHERE pb.variation_id = %s
            ORDER BY pb.batch_id DESC
            FOR UPDATE
        """, (warehouse_id, variation_id))
    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
            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))

    batches = cursor.fetchall()

    if not batches:
        # No existing batch/warehouse_stock rows found.
        # Try to find ANY batch for this product so we can at least top it up.
        if variation_id:
            cursor.execute("""
                SELECT batch_id FROM product_batches
                WHERE variation_id = %s
                ORDER BY batch_id DESC LIMIT 1
            """, (variation_id,))
        else:
            cursor.execute("""
                SELECT batch_id 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 {variation_id}' if variation_id else ''}. "
                f"Cannot restore {return_qty} units."
            )

        batch_id = fallback['batch_id']

        # Restore to product_batches
        cursor.execute("""
            UPDATE product_batches
            SET remaining_quantity = remaining_quantity + %s
            WHERE batch_id = %s
        """, (remaining, batch_id))

        # Re-create warehouse_stock row
        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, variation_id, batch_id, remaining, remaining))

        print(f"   📦 [Fallback] Restored {remaining} → batch {batch_id} / warehouse {warehouse_id}")
        return

    # Normal path: distribute across existing batches (LIFO order)
    for batch in batches:
        if remaining <= 0:
            break

        restore_now = remaining  # restore all into first/newest batch (simplest correct approach)

        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']))

        print(f"   📦 Restored {restore_now} → 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 {variation_id}' if variation_id else ''}. "
            f"Unrestored: {remaining:.3f}"
        )


def _deduct_stock_for_return_reversal(cursor, product_id, variation_id, qty, warehouse_id):
    """
    Deduct stock that was previously restored by a sale return (FIFO — oldest batch first).
    Used when updating/deleting a sale return whose old status was 'received'.
    """
    if not warehouse_id:
        raise Exception(f"warehouse_id is required to reverse stock for product {product_id}")

    remaining = float(qty)

    if variation_id:
        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
            WHERE pb.variation_id = %s
              AND pb.remaining_quantity > 0
              AND ws.quantity > 0
            ORDER BY pb.batch_id ASC
            FOR UPDATE
        """, (warehouse_id, variation_id))
    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
            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))

    batches = cursor.fetchall()

    if not batches:
        raise Exception(
            f"No stock to reverse for product {product_id}"
            f"{f', variation {variation_id}' if variation_id else ''} "
            f"in warehouse {warehouse_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"   ↩️  Reversed {deduct_now} ← batch {batch['batch_id']} / warehouse {warehouse_id}")
        remaining -= deduct_now

    if remaining > 0.001:
        raise Exception(
            f"Insufficient stock to reverse for product {product_id}"
            f"{f', variation {variation_id}' if variation_id else ''}. "
            f"Short by {remaining:.3f}."
        )


# ============================================================
# ENDPOINTS
# ============================================================

@sale_return_bp.route('/create_sale_return', methods=['POST'])
@jwt_required()
@role_required('admin', 'cashier')
def create_sale_return():
    """
    Create a sale return with warehouse-aware stock restoration.
    Stock is restored directly into product_batches / warehouse_stock
    using the warehouse from the original invoice.
    """
    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
    try:
        conn   = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        cursor.execute("SET TRANSACTION ISOLATION LEVEL READ COMMITTED")
        cursor.execute("START TRANSACTION")

        # Pull warehouse / store / customer from original invoice
        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:
            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']

        # Insert sale_return header
        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, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """, (
            invoice_id,
            datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
            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 = item.get('variation_id')
            if variation_id:
                variation_id = int(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, return_quantity,
                    return_price, return_total, product_discount, discount_type,
                    product_tax, tax_type, tax, discount, unit_price, sale_unit,
                    store_id, customer_id
                ) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
            """, (
                return_id,
                product_id,
                variation_id,
                return_qty,
                item.get('return_price', 0.0),
                return_total,
                item.get('product_discount', 0.0),
                item.get('discount_type', ''),
                item.get('product_tax', 0.0),
                item.get('tax_type', ''),
                item.get('tax', 0.0),
                item.get('discount', 0.0),
                unit_price,
                item.get('sale_unit', ''),
                store_id,
                customer_id,
            ))

            # Restore stock only when status is 'received'
            if status.lower() == 'received':
                try:
                    _restore_stock_for_return(
                        cursor,
                        product_id,
                        variation_id,
                        return_qty,
                        warehouse_id,
                        store_id,
                    )
                except Exception as stock_err:
                    conn.rollback()
                    return jsonify({
                        "error": f"Stock restoration failed for product {product_id}: {str(stock_err)}"
                    }), 400

        conn.commit()
        cursor.close()
        conn.close()

        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()
            conn.close()
        print("❌ Error creating sale return:", e)
        return jsonify({"error": str(e)}), 500


@sale_return_bp.route('/check_sale_return/<int:invoice_id>', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier')
def check_sale_return(invoice_id):
    try:
        conn   = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        cursor.execute(
            "SELECT return_id FROM sale_return WHERE invoice_id = %s LIMIT 1",
            (invoice_id,)
        )
        sale_return = cursor.fetchone()

        cursor.close()
        conn.close()

        if sale_return:
            return jsonify({"exists": True, "sale_return_id": sale_return["return_id"]})
        return jsonify({"exists": False})

    except Exception as e:
        print("Error checking sale return:", e)
        return jsonify({"error": str(e)}), 500


@sale_return_bp.route('/view_all_sale_returns', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier')
def view_all_sale_returns():
    """View all sale returns with warehouse, store, and customer information."""
    try:
        conn   = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        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.*,
                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 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['return_date']:
                r['return_date'] = r['return_date'].strftime('%Y-%m-%d %H:%M:%S')
            cursor.execute(
                "SELECT * FROM sale_return_products WHERE return_id = %s",
                (r['return_id'],)
            )
            r['products'] = cursor.fetchall()

        cursor.close()
        conn.close()
        return jsonify(returns), 200

    except Exception as e:
        print("❌ Error:", e)
        return jsonify({"error": str(e)}), 500


@sale_return_bp.route('/view_sale_return/<int:return_id>')
@jwt_required()
@role_required('admin', 'cashier')
def view_sale_return(return_id):
    """View a single sale return with full product details."""
    try:
        conn   = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        cursor.execute("""
            SELECT
                sr.*,
                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'):
            return_data['return_date'] = return_data['return_date'].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

        cursor.execute("""
            SELECT
                srp.return_id,
                srp.product_id,
                pr.product_name,
                pr.sku          AS product_sku,
                srp.variation_id,
                pv.variation_name,
                pv.variation_type,
                pv.variation_sku,
                sp.quantity     AS sold_quantity,
                sp.sale_unit    AS sold_unit,
                srp.return_quantity,
                srp.sale_unit   AS return_unit,
                srp.unit_price,
                srp.return_price,
                srp.return_total,
                srp.product_tax,
                srp.tax_type,
                srp.product_discount,
                srp.discount_type,
                srp.discount,
                srp.tax
            FROM sale_return_products srp
            JOIN sale_return sr
                ON srp.return_id = sr.return_id
            JOIN sale_products sp
                ON sp.invoice_id  = sr.invoice_id
               AND sp.product_id  = srp.product_id
               AND (sp.variation_id = srp.variation_id
                    OR (sp.variation_id IS NULL AND srp.variation_id IS NULL))
            JOIN products pr
                ON srp.product_id = pr.id
            LEFT JOIN product_variations pv
                ON srp.variation_id = pv.id
            WHERE srp.return_id = %s
        """, (return_id,))
        return_data['products'] = cursor.fetchall()

        cursor.close()
        conn.close()
        return jsonify(return_data), 200

    except Exception as e:
        print("❌ Error:", e)
        import traceback; traceback.print_exc()
        return jsonify({"error": str(e)}), 500


@sale_return_bp.route('/update_sale_return/<int:return_id>', methods=['PUT'])
@jwt_required()
@role_required('admin', 'cashier')
def update_sale_return(return_id):
    """
    Update a sale return.

    Stock logic:
      • If old status == 'received'  → reverse (deduct) the previously restored stock.
      • Insert new items.
      • If new status == 'received'  → restore stock for the new items.
    """
    data = request.get_json()
    print(f"\n📦 Updating Sale Return ID: {return_id}")
    print(json.dumps(data, indent=4))

    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")

        # Get current return state
        cursor.execute("""
            SELECT status, warehouse_id, store_id
            FROM sale_return
            WHERE return_id = %s
        """, (return_id,))
        row = cursor.fetchone()
        if not row:
            return jsonify({"error": "Sale return not found"}), 404

        old_status   = row['status'].lower()
        warehouse_id = row['warehouse_id']
        store_id     = row['store_id']

        # 1. Reverse old stock if it was received
        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()

            for old in old_items:
                try:
                    _deduct_stock_for_return_reversal(
                        cursor,
                        old['product_id'],
                        old['variation_id'],
                        float(old['return_quantity']),
                        warehouse_id,
                    )
                except Exception as e:
                    conn.rollback()
                    return jsonify({
                        "error": f"Failed to reverse stock for product {old['product_id']}: {str(e)}"
                    }), 400

        # 2. Update header
        cursor.execute("""
            UPDATE sale_return
            SET invoice_id=%s, total_return_amount=%s, reason=%s,
                return_date=%s, order_tax=%s, order_discount=%s, status=%s
            WHERE return_id=%s
        """, (
            invoice_id, total_return_amount, reason,
            datetime.now(), order_tax, order_discount, status,
            return_id,
        ))

        # 3. Replace line items
        cursor.execute("DELETE FROM sale_return_products WHERE return_id = %s", (return_id,))

        for item in return_items:
            product_id   = int(item['product_id'])
            variation_id = item.get('variation_id')
            if variation_id:
                variation_id = int(variation_id)
            return_qty = float(item['return_quantity'])

            cursor.execute("""
                INSERT INTO sale_return_products (
                    return_id, product_id, variation_id, return_quantity,
                    return_price, return_total, product_discount, discount_type,
                    product_tax, tax_type, tax, discount, unit_price, sale_unit,
                    store_id, customer_id
                ) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,
                    (SELECT customer_id FROM sale_return WHERE return_id = %s))
            """, (
                return_id, product_id, variation_id,
                return_qty,
                item.get('return_price', 0.0),
                item.get('return_total', 0.0),
                item.get('product_discount', 0.0),
                item.get('discount_type', ''),
                item.get('product_tax', 0.0),
                item.get('tax_type', ''),
                item.get('tax', 0.0),
                item.get('discount', 0.0),
                item.get('unit_price', 0.0),
                item.get('sale_unit', ''),
                store_id,
                return_id,
            ))

            # 4. Restore stock for new items if status is received
            if status == 'received':
                try:
                    _restore_stock_for_return(
                        cursor,
                        product_id,
                        variation_id,
                        return_qty,
                        warehouse_id,
                        store_id,
                    )
                except Exception as e:
                    conn.rollback()
                    return jsonify({
                        "error": f"Stock restoration failed for product {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)
        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')
def delete_sale_return(return_id):
    """
    Delete a sale return.
    If status was 'received', reverse (deduct) the restored stock first.
    """
    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:
            return jsonify({"error": "Sale return not found"}), 404

        status       = sale_return['status'].lower()
        warehouse_id = sale_return['warehouse_id']
        store_id     = sale_return['store_id']

        if status == 'received':
            cursor.execute("""
                SELECT product_id, variation_id, return_quantity
                FROM sale_return_products
                WHERE return_id = %s
            """, (return_id,))
            return_items = cursor.fetchall()

            for item in return_items:
                try:
                    _deduct_stock_for_return_reversal(
                        cursor,
                        item['product_id'],
                        item['variation_id'],
                        float(item['return_quantity']),
                        warehouse_id,
                    )
                except Exception as e:
                    conn.rollback()
                    return jsonify({
                        "error": f"Stock reversal failed for product {item['product_id']}: {str(e)}"
                    }), 400

        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 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)
        return jsonify({"error": str(e)}), 500
    finally:
        if cursor: cursor.close()
        if conn:   conn.close()