from flask import Blueprint, request, jsonify
from flask_jwt_extended import jwt_required, get_jwt_identity
from db.db import get_db_connection
from config.auth import role_required
from datetime import datetime, timedelta
import json
from decimal import Decimal
from datetime import date, datetime

sale_bp = Blueprint('sale', __name__)

# ============================================
# WAREHOUSE-SPECIFIC STOCK MANAGEMENT HELPERS
# ============================================

def _parse_batch_ids(raw_batch_ids):
    """
    ✅ HELPER: Parse a comma-separated batch_ids string (e.g. "12,15,18")
    sent by the frontend for a specific dropdown row (a specific
    price/cost/our_price group) into a clean list of ints.

    Returns None if parsing fails or the value is empty, so calling code
    can safely fall back to price-only matching.
    """
    if not raw_batch_ids:
        return None
    try:
        if isinstance(raw_batch_ids, (list, tuple)):
            ids = [int(b) for b in raw_batch_ids if str(b).strip()]
        else:
            ids = [int(b) for b in str(raw_batch_ids).split(',') if b.strip()]
        return ids if ids else None
    except (ValueError, TypeError):
        print(f"⚠️ Could not parse batch_ids '{raw_batch_ids}' — falling back to price match")
        return None


def get_available_stock_by_warehouse(cursor, product_id, variation_id, warehouse_id, price=None, batch_ids=None):
    """
    Get available stock for a specific warehouse from batches.

    ✅ FIXED (batch-exact matching): If batch_ids is provided (the exact
       batch IDs the cashier's dropdown selection represents — a unique
       price/cost/our_price group), stock is checked ONLY against those
       batches. This prevents accidentally including a *different* batch
       that happens to share the same market price (pb.price) but has a
       different cost/our_price — which was the original bug: two batches
       both priced at MRP 2800 but with different cost (e.g. 2500 vs 2300)
       would previously get summed together because the check only
       filtered by pb.price.

    ✅ PRICE-AWARE (fallback): If batch_ids is NOT provided, falls back to
       the old behavior of filtering by pb.price (market price) — kept for
       backward compatibility with any caller that doesn't send batch_ids.
       our_price is the cashier's selling price — NOT used for batch lookup.

    Args:
        cursor: Database cursor
        product_id: Product ID
        variation_id: Variation ID (can be None)
        warehouse_id: Warehouse ID to check stock
        price: Optional pb.price filter (market price stored in batch at GRN time)
        batch_ids: Optional list[int] of exact batch_ids to restrict to

    Returns:
        float: Available stock quantity in the specified warehouse
    """
    if not warehouse_id:
        return 0.0

    # ✅ PRIORITY 1: Exact batch_ids selected by the cashier's dropdown row
    if batch_ids:
        placeholders = ','.join(['%s'] * len(batch_ids))
        query = f"""
            SELECT IFNULL(SUM(
                LEAST(pb.remaining_quantity, ws.quantity)
            ), 0) as available_stock
            FROM product_batches pb
            INNER JOIN warehouse_stock ws ON pb.batch_id = ws.batch_id
            WHERE pb.batch_id IN ({placeholders})
            AND ws.warehouse_id = %s
            AND pb.remaining_quantity > 0
            AND ws.quantity > 0
        """
        params = list(batch_ids) + [warehouse_id]
        cursor.execute(query, tuple(params))
        result = cursor.fetchone()
        return float(result['available_stock']) if result else 0.0

    # ✅ FALLBACK: price-only matching (legacy behavior)
    if variation_id:
        query = """
            SELECT IFNULL(SUM(
                LEAST(pb.remaining_quantity, ws.quantity)
            ), 0) as available_stock
            FROM product_batches pb
            INNER JOIN warehouse_stock ws ON pb.batch_id = ws.batch_id
            WHERE pb.variation_id = %s
            AND ws.warehouse_id = %s
            AND pb.remaining_quantity > 0
            AND ws.quantity > 0
        """
        params = [variation_id, warehouse_id]

        if price is not None:
            # ✅ Filter by pb.price (market price at GRN time) — NOT our_price
            query += " AND pb.price = %s"
            params.append(float(price))

        cursor.execute(query, tuple(params))
    else:
        query = """
            SELECT IFNULL(SUM(
                LEAST(pb.remaining_quantity, ws.quantity)
            ), 0) as available_stock
            FROM product_batches pb
            INNER JOIN warehouse_stock ws ON pb.batch_id = ws.batch_id
            WHERE pb.product_id = %s
            AND (pb.variation_id IS NULL OR pb.variation_id = 0)
            AND ws.warehouse_id = %s
            AND pb.remaining_quantity > 0
            AND ws.quantity > 0
        """
        params = [product_id, warehouse_id]

        if price is not None:
            # ✅ Filter by pb.price (market price at GRN time) — NOT our_price
            query += " AND pb.price = %s"
            params.append(float(price))

        cursor.execute(query, tuple(params))

    result = cursor.fetchone()
    return float(result['available_stock']) if result else 0.0


def deduct_stock_from_batches_fifo_warehouse(cursor, product_id, variation_id, quantity, warehouse_id, store_id,
                                              sale_product_id, price=None, batch_ids=None):
    """
    Deduct stock from product_batches using FIFO for a specific warehouse.
    Also updates warehouse_stock table and creates sale_product_items entries.

    ✅ FIXED (batch-exact matching): When batch_ids is provided (the exact
       batches represented by the cashier's selected price/cost/our_price
       group in the dropdown), deduction happens ONLY from those batches —
       never from a different batch that happens to share the same market
       price but has a different cost/our_price. This was the root cause of
       stock being deducted incorrectly when two batches shared the same
       MRP (e.g. Rs. 2800) but had different cost (e.g. Rs. 2500 vs Rs. 2300).

    ✅ PRICE-AWARE (fallback): Filters batches by pb.price (market price at
       GRN time) when batch_ids is not supplied. our_price is the cashier's
       selling price — stored in sale_products only.

    ✅ INCLUDES ROW LOCKING to prevent race conditions.

    Args:
        cursor: Database cursor
        product_id: Product ID
        variation_id: Variation ID (can be None)
        quantity: Quantity to deduct
        warehouse_id: Warehouse ID
        store_id: Store ID
        sale_product_id: ID from sale_products table (for linking sale_product_items)
        price: Market price (pb.price) — used to identify correct batch (fallback only)
        batch_ids: Optional list[int] of exact batch_ids to restrict deduction to

    Returns:
        list: List of dictionaries containing batch details used

    Raises:
        Exception: If insufficient stock at the specified price / batch selection
    """
    if not warehouse_id:
        raise Exception(f"Warehouse ID is required for product {product_id}")

    remaining_qty = float(quantity)
    batches_used = []

    # ✅ PRIORITY 1: Restrict strictly to the exact batch_ids selected by the cashier
    if batch_ids:
        placeholders = ','.join(['%s'] * len(batch_ids))
        query = f"""
            SELECT pb.batch_id, pb.grn_id, pb.remaining_quantity,
                   pb.cost, pb.price, pb.our_price, pb.expiration_date,
                   ws.id as warehouse_stock_id, ws.quantity as ws_qty
            FROM product_batches pb
            INNER JOIN warehouse_stock ws ON pb.batch_id = ws.batch_id
            WHERE pb.batch_id IN ({placeholders})
            AND ws.warehouse_id = %s
            AND pb.remaining_quantity > 0
            AND ws.quantity > 0
            ORDER BY pb.batch_id ASC
            FOR UPDATE
        """
        params = list(batch_ids) + [warehouse_id]
        cursor.execute(query, tuple(params))

    # ✅ FALLBACK: legacy price-based matching
    elif variation_id:
        query = """
            SELECT pb.batch_id, pb.grn_id, pb.remaining_quantity,
                   pb.cost, pb.price, pb.our_price, pb.expiration_date,
                   ws.id as warehouse_stock_id, ws.quantity as ws_qty
            FROM product_batches pb
            INNER JOIN warehouse_stock ws ON pb.batch_id = ws.batch_id
            WHERE pb.variation_id = %s
            AND pb.remaining_quantity > 0
            AND ws.warehouse_id = %s
            AND ws.quantity > 0
        """
        params = [variation_id, warehouse_id]

        if price is not None:
            # ✅ Filter by pb.price (market price) — NOT our_price
            query += " AND pb.price = %s"
            params.append(float(price))

        query += " ORDER BY pb.batch_id ASC FOR UPDATE"
        cursor.execute(query, tuple(params))
    else:
        query = """
            SELECT pb.batch_id, pb.grn_id, pb.remaining_quantity,
                   pb.cost, pb.price, pb.our_price, pb.expiration_date,
                   ws.id as warehouse_stock_id, ws.quantity as ws_qty
            FROM product_batches pb
            INNER JOIN warehouse_stock ws ON pb.batch_id = ws.batch_id
            WHERE pb.product_id = %s
            AND (pb.variation_id IS NULL OR pb.variation_id = 0)
            AND pb.remaining_quantity > 0
            AND ws.warehouse_id = %s
            AND ws.quantity > 0
        """
        params = [product_id, warehouse_id]

        if price is not None:
            # ✅ Filter by pb.price (market price) — NOT our_price
            query += " AND pb.price = %s"
            params.append(float(price))

        query += " ORDER BY pb.batch_id ASC FOR UPDATE"
        cursor.execute(query, tuple(params))

    batches = cursor.fetchall()

    if not batches:
        price_msg = f" at market price Rs. {price}" if price is not None else ""
        batch_msg = f" (batch_ids: {batch_ids})" if batch_ids else ""
        raise Exception(
            f"No stock found{price_msg}{batch_msg} in warehouse {warehouse_id} for product {product_id}"
            f"{f', variation {variation_id}' if variation_id else ''}"
        )

    # FIFO deduction across batches
    for batch in batches:
        if remaining_qty <= 0:
            break

        available_in_batch = min(float(batch['remaining_quantity']), float(batch['ws_qty']))
        deduct_now = min(remaining_qty, available_in_batch)

        cost_value = batch.get('cost', 0)
        if cost_value is None or cost_value == 0:
            print(f"⚠️ WARNING: Batch {batch['batch_id']} has zero/null cost!")

        # Update product_batches
        cursor.execute("""
            UPDATE product_batches
            SET remaining_quantity = remaining_quantity - %s
            WHERE batch_id = %s
        """, (deduct_now, batch['batch_id']))

        if cursor.rowcount != 1:
            raise Exception(f"Failed to update product_batches for batch {batch['batch_id']}")

        # Update warehouse_stock
        cursor.execute("""
            UPDATE warehouse_stock
            SET quantity = quantity - %s
            WHERE id = %s
        """, (deduct_now, batch['warehouse_stock_id']))

        if cursor.rowcount != 1:
            raise Exception(f"Failed to update warehouse_stock for warehouse_stock_id {batch['warehouse_stock_id']}")

        # Insert into sale_product_items for audit trail
        cursor.execute("""
            INSERT INTO sale_product_items (
                sale_product_id, batch_id, grn_id, quantity, cost, expiration_date
            ) VALUES (%s, %s, %s, %s, %s, %s)
        """, (
            sale_product_id,
            batch['batch_id'],
            batch['grn_id'],
            deduct_now,
            cost_value,
            batch.get('expiration_date')
        ))

        batches_used.append({
            'batch_id': batch['batch_id'],
            'grn_id': batch['grn_id'],
            'quantity': deduct_now,
            'cost': cost_value,
            'price': batch.get('price'),
            'our_price': batch.get('our_price'),
            'expiration_date': batch.get('expiration_date')
        })
        remaining_qty -= deduct_now

    # Allow small rounding errors
    if remaining_qty > 0.001:
        price_msg = f" at market price Rs. {price}" if price is not None else ""
        batch_msg = f" (batch_ids: {batch_ids})" if batch_ids else ""
        raise Exception(
            f"Insufficient stock{price_msg}{batch_msg} in warehouse {warehouse_id} for product {product_id}"
            f"{f', variation {variation_id}' if variation_id else ''}. "
            f"Short by {remaining_qty:.3f} units."
        )

    return batches_used


def restore_stock_to_batches_warehouse(cursor, sale_product_id, store_id):
    """
    Restore stock to batches for a specific sale_product using sale_product_items records (LIFO restoration).
    Also updates warehouse_stock table.

    ✅ FIXED: Properly uses actual store_id parameter

    Args:
        cursor: Database cursor
        sale_product_id: ID from sale_products table
        store_id: Store ID for warehouse_stock insertion
    """

    # Get all sale_product_items for this sale_product (reverse order for LIFO)
    cursor.execute("""
        SELECT spi.sale_product_item_id, spi.batch_id, spi.quantity,
               sp.warehouse_id, sp.product_id, sp.variation_id
        FROM sale_product_items spi
        INNER JOIN sale_products sp ON spi.sale_product_id = sp.sale_product_id
        WHERE spi.sale_product_id = %s
        ORDER BY spi.sale_product_item_id DESC
    """, (sale_product_id,))

    items = cursor.fetchall()

    if not items:
        print(f"   ℹ️ No sale_product_items found for sale_product_id {sale_product_id}")
        return

    for item in items:
        restore_qty = float(item['quantity'])
        batch_id = item['batch_id']
        warehouse_id = item['warehouse_id']

        # Update product_batches
        cursor.execute("""
            UPDATE product_batches
            SET remaining_quantity = remaining_quantity + %s
            WHERE batch_id = %s
        """, (restore_qty, batch_id))

        if cursor.rowcount != 1:
            print(f"   ⚠️ Warning: Batch {batch_id} not found in product_batches")

        # Update warehouse_stock (or insert if doesn't exist)
        cursor.execute("""
            SELECT id FROM warehouse_stock
            WHERE batch_id = %s AND warehouse_id = %s
        """, (batch_id, warehouse_id))

        ws_exists = cursor.fetchone()

        if ws_exists:
            cursor.execute("""
                UPDATE warehouse_stock
                SET quantity = quantity + %s
                WHERE batch_id = %s AND warehouse_id = %s
            """, (restore_qty, batch_id, warehouse_id))
        else:
            # Re-create warehouse_stock entry using actual store_id
            print(f"   ℹ️ Recreating warehouse_stock for batch {batch_id} in warehouse {warehouse_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)
            """, (
                store_id,
                item['product_id'],
                warehouse_id,
                item['variation_id'],
                batch_id,
                restore_qty
            ))

        print(f"   📦 Restored {restore_qty} units to batch {batch_id} in warehouse {warehouse_id}")

    # Delete the sale_product_items records
    cursor.execute("""
        DELETE FROM sale_product_items
        WHERE sale_product_id = %s
    """, (sale_product_id,))

    print(f"   🗑️ Deleted {cursor.rowcount} sale_product_items entries")


def get_payment_method_id_and_name(cursor, payment_type_id):
    """
    Get payment method ID and name from ID with enhanced error handling.
    ✅ FIXED: Returns both ID and name for proper database insertion

    Args:
        cursor: Database cursor
        payment_type_id: Payment method ID (int or string)

    Returns:
        tuple: (method_id, method_name) or (1, 'Cash') as fallback
    """
    try:
        method_id = int(payment_type_id)

        cursor.execute("""
            SELECT id, method_name
            FROM payment_methods
            WHERE id = %s AND is_active = 1
        """, (method_id,))

        result = cursor.fetchone()
        if result:
            return result['id'], result['method_name']

        print(f"⚠️ WARNING: Payment method ID {method_id} not found - defaulting to Cash")
        cursor.execute("""
            SELECT id, method_name
            FROM payment_methods
            WHERE LOWER(method_name) = 'cash' AND is_active = 1
            LIMIT 1
        """)
        cash_result = cursor.fetchone()
        if cash_result:
            return cash_result['id'], cash_result['method_name']

        return 1, 'Cash'

    except (ValueError, TypeError) as e:
        print(f"⚠️ Error converting payment_type_id to int: {e} - defaulting to Cash")
        cursor.execute("""
            SELECT id, method_name
            FROM payment_methods
            WHERE LOWER(method_name) = 'cash' AND is_active = 1
            LIMIT 1
        """)
        cash_result = cursor.fetchone()
        if cash_result:
            return cash_result['id'], cash_result['method_name']

        return 1, 'Cash'


def get_unit_identifier(cursor, unit_value):
    """
    Get unit SHORT NAME (unit_short) from unit ID.
    ✅ ALWAYS returns the short name like "kg", "pcs", "box"
    ✅ NEVER returns the full name like "Kilogram", "Pieces", "Box"

    Args:
        cursor: Database cursor
        unit_value: Unit ID (int or string)

    Returns:
        str: Unit short name to store in sale_unit column (e.g., "kg", "pcs", "box")
    """
    if unit_value is None:
        return None

    try:
        unit_id = int(unit_value)

        cursor.execute("""
            SELECT unit_short
            FROM units
            WHERE id = %s
        """, (unit_id,))

        result = cursor.fetchone()
        if result and result['unit_short']:
            return result['unit_short']

        print(f"⚠️ WARNING: Unit ID {unit_id} not found or has no short name - using ID as fallback")
        return str(unit_id)

    except (ValueError, TypeError):
        return str(unit_value)

@sale_bp.route('/submit_invoice', methods=['POST'])
@jwt_required()
@role_required('admin', 'cashier')
def submit_invoice():
    conn = None
    try:
        data = request.get_json()
        print("📥 Incoming Invoice JSON Data:")
        print(json.dumps(data, indent=4, default=str))

        # ============================================
        # EXTRACT INVOICE DATA
        # ============================================
        invoice_id          = data.get('invoice_id')
        payment_only        = data.get('payment_only', False)
        tenderAmount        = float(data.get('tenderAmount', 0))
        invoiceTotal        = float(data.get('invoiceTotal', 0))
        remainingAmount     = float(data.get('remainingAmount', 0))
        discount_value      = float(data.get('discountValue', 0))
        discount_type       = data.get('discountType', 'fixed')
        discount_percentage = float(data.get('discountPercentage', 0))
        customer_id         = data.get('customer_id')
        products            = data.get('products', [])
        note                = data.get('note', '')
        payment_notes       = data.get('payment_notes', '')
        tax                 = float(data.get('tax', 0))
        warehouse_id        = data.get('warehouse_id')
        store_id            = data.get('store_id')
        payment_status      = data.get('payment_status', 'paid')
        payment_methods     = data.get('payment_methods')
        quotation_id        = data.get('quotation_id')

        # ============================================
        # VALIDATE DISCOUNT TYPE
        # ============================================
        if discount_type not in ('fixed', 'percentage'):
            discount_type = 'fixed'

        if discount_type == 'fixed':
            discount_percentage = 0.0

        # ============================================
        # VALIDATE REQUIRED FIELDS
        # ============================================
        if not store_id:
            return jsonify({"error": "Store ID is required"}), 400

        if not payment_only and (not products or len(products) == 0):
            return jsonify({"error": "At least one product is required"}), 400

        # ============================================
        # AUTO-DETERMINE INVOICE STATUS
        # ============================================
        if payment_status == 'paid':
            status = 'received'
        else:
            status = 'suspended'

        current_user = get_jwt_identity()

        conn   = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        cursor.execute("SET TRANSACTION ISOLATION LEVEL READ COMMITTED")
        cursor.execute("START TRANSACTION")

        # ============================================
        # PAYMENT-ONLY MODE (bulk payment / add payment)
        # Skips stock restore/re-deduct — only updates
        # payment amounts and inserts new payment records.
        # ============================================
        if payment_only and invoice_id:
            print(f"   💳 Payment-only mode for invoice {invoice_id}...")

            invoice_code = data.get('invoice_code')

            # Determine primary payment method label
            if not payment_methods or len(payment_methods) == 0:
                primary_payment_method = "unpaid"
            elif len(payment_methods) == 1:
                method_id, method_name = get_payment_method_id_and_name(
                    cursor, payment_methods[0]['type']
                )
                primary_payment_method = method_name.lower()
            else:
                primary_payment_method = "multiple"

            # Update invoice header (amounts + status only)
            cursor.execute("""
                UPDATE invoice_sale SET
                    tenderAmount    = %s,
                    remainingAmount = %s,
                    payment_method  = %s,
                    payment_status  = %s,
                    status          = %s,
                    note            = CASE WHEN %s != '' THEN CONCAT(IFNULL(note,''), '\nPayment Notes: ', %s) ELSE note END
                WHERE invoice_id = %s
            """, (
                tenderAmount, remainingAmount,
                primary_payment_method, payment_status, status,
                payment_notes, payment_notes,
                invoice_id
            ))
            print(f"   ✅ Updated invoice amounts (tender: {tenderAmount}, remaining: {remainingAmount}, status: {payment_status})")

            # Insert ONLY the new payment records (not existing ones)
            new_payments = data.get('new_payments', [])
            if not new_payments and payment_methods:
                # Fallback: if frontend sends all payments via payment_methods,
                # we need to figure out which are new. But with our frontend fix,
                # new_payments will be provided separately.
                new_payments = payment_methods

            if new_payments:
                print(f"   💳 Adding {len(new_payments)} new payment(s)...")
                for idx, payment in enumerate(new_payments, 1):
                    try:
                        payment_type_value = payment.get('type')
                        payment_amount = float(payment.get('amount', 0))
                        if payment_amount > 0:
                            method_id, method_name = get_payment_method_id_and_name(
                                cursor, payment_type_value
                            )
                            print(f"      [{idx}] {method_name} (ID: {method_id}): Rs. {payment_amount:.2f}")
                            cursor.execute("""
                                INSERT INTO sale_payments (
                                    sale_id, payment_method, amount, payment_date
                                ) VALUES (%s, %s, %s, NOW())
                            """, (invoice_id, method_id, payment_amount))
                            print(f"         ✅ Payment record created (ID: {cursor.lastrowid})")
                    except Exception as pe:
                        print(f"      ❌ Error processing payment {idx}: {pe}")
                        raise

            conn.commit()
            cursor.close()
            conn.close()

            print(f"✅ Payment-only update for invoice {invoice_code} completed successfully")
            print(f"   💰 Total: {invoiceTotal}, Paid: {tenderAmount}, Balance: {remainingAmount}")

            return jsonify({
                "message":        "Payment added successfully",
                "invoice_id":     invoice_id,
                "invoice_code":   invoice_code,
                "payment_status": payment_status,
                "invoice_status": status,
            }), 200

        # ============================================
        # ✅ CHECK QUOTATION STATUS ENUM SUPPORT
        # Ensure 'accepted' is present in the ENUM.
        # 'completed' is NOT used — quotations are marked 'accepted' when
        # converted to a sale.
        # ============================================
        if quotation_id:
            try:
                cursor.execute("""
                    SELECT COLUMN_TYPE
                    FROM INFORMATION_SCHEMA.COLUMNS
                    WHERE TABLE_SCHEMA = DATABASE()
                      AND TABLE_NAME   = 'quotations'
                      AND COLUMN_NAME  = 'status'
                """)
                enum_row = cursor.fetchone()
                if enum_row:
                    enum_definition = enum_row['COLUMN_TYPE']
                    if 'accepted' not in enum_definition:
                        print("   ⚠️ 'accepted' not in quotations.status ENUM — auto-fixing...")
                        cursor.execute("""
                            ALTER TABLE quotations
                            MODIFY COLUMN `status`
                            ENUM('pending','sent','accepted','rejected','expired')
                            DEFAULT 'pending'
                        """)
                        print("   ✅ quotations.status ENUM updated successfully")
            except Exception as enum_err:
                print(f"   ⚠️ Could not check/fix ENUM: {enum_err}")

        # ============================================
        # GENERATE INVOICE CODE IF NEW
        # ============================================
        invoice_code = data.get('invoice_code')
        if not invoice_id and not invoice_code:
            today_str = datetime.now().strftime("%Y%m%d")
            cursor.execute(
                "SELECT COUNT(*) as count FROM invoice_sale WHERE DATE(created_at) = CURDATE()"
            )
            count        = cursor.fetchone()["count"] + 1
            invoice_code = f"INV-{today_str}-{count:04d}"

        # ============================================
        # DETERMINE PRIMARY PAYMENT METHOD LABEL
        # ============================================
        if not payment_methods or len(payment_methods) == 0:
            primary_payment_method = "unpaid"
        elif len(payment_methods) == 1:
            method_id, method_name = get_payment_method_id_and_name(
                cursor, payment_methods[0]['type']
            )
            primary_payment_method = method_name.lower()
        else:
            primary_payment_method = "multiple"

        # ============================================
        # INSERT OR UPDATE INVOICE
        # ============================================
        if invoice_id:
            print(f"   🔄 Updating existing invoice {invoice_id}...")

            cursor.execute("""
                SELECT sale_product_id
                FROM sale_products
                WHERE invoice_id = %s
            """, (invoice_id,))
            old_sale_products = cursor.fetchall()

            print(f"   ⏪ Restoring stock from {len(old_sale_products)} products...")
            for old_sp in old_sale_products:
                restore_stock_to_batches_warehouse(
                    cursor, old_sp['sale_product_id'], store_id
                )

            cursor.execute(
                "DELETE FROM sale_products WHERE invoice_id=%s", (invoice_id,)
            )
            print(f"   🗑️ Deleted {cursor.rowcount} old sale_products")

            cursor.execute(
                "DELETE FROM sale_payments WHERE sale_id=%s", (invoice_id,)
            )
            print(f"   🗑️ Deleted {cursor.rowcount} old sale_payments")

            cursor.execute("""
                UPDATE invoice_sale SET
                    invoice_code        = %s,
                    tenderAmount        = %s,
                    remainingAmount     = %s,
                    invoiceTotal        = %s,
                    payment_method      = %s,
                    payment_status      = %s,
                    customer_id         = %s,
                    discount            = %s,
                    discount_type       = %s,
                    discount_percentage = %s,
                    status              = %s,
                    note                = %s,
                    tax                 = %s,
                    warehouse_id        = %s,
                    store_id            = %s
                WHERE invoice_id = %s
            """, (
                invoice_code, tenderAmount, remainingAmount, invoiceTotal,
                primary_payment_method, payment_status, customer_id,
                discount_value, discount_type, discount_percentage,
                status,
                f"{note}\nPayment Notes: {payment_notes}" if payment_notes else note,
                tax, warehouse_id, store_id,
                invoice_id
            ))
            print(
                f"   ✅ Updated invoice {invoice_code} "
                f"(discount_type: {discount_type}, discount_percentage: {discount_percentage})"
            )

        else:
            print(f"   ➕ Creating new invoice...")
            cursor.execute("""
                INSERT INTO invoice_sale (
                    invoice_code,
                    tenderAmount, remainingAmount, invoiceTotal,
                    payment_method, payment_status, customer_id,
                    discount, discount_type, discount_percentage,
                    status, note, tax,
                    warehouse_id, store_id, cashier_user_id
                ) VALUES (
                    %s,
                    %s, %s, %s,
                    %s, %s, %s,
                    %s, %s, %s,
                    %s, %s, %s,
                    %s, %s, %s
                )
            """, (
                invoice_code,
                tenderAmount, remainingAmount, invoiceTotal,
                primary_payment_method, payment_status, customer_id,
                discount_value, discount_type, discount_percentage,
                status,
                f"{note}\nPayment Notes: {payment_notes}" if payment_notes else note,
                tax,
                warehouse_id, store_id, current_user
            ))
            invoice_id = cursor.lastrowid
            print(
                f"   ✅ Created new invoice {invoice_code} (ID: {invoice_id}, "
                f"discount_type: {discount_type}, discount_percentage: {discount_percentage})"
            )

        # ============================================
        # INSERT PAYMENT METHODS
        # ============================================
        if payment_methods and payment_status != 'unpaid':
            print(f"   💳 Processing {len(payment_methods)} payment method(s)...")
            for idx, payment in enumerate(payment_methods, 1):
                try:
                    payment_type_value = payment.get('type')
                    payment_amount     = float(payment.get('amount', 0))

                    if payment_amount > 0:
                        method_id, method_name = get_payment_method_id_and_name(
                            cursor, payment_type_value
                        )
                        print(
                            f"      [{idx}] {method_name} (ID: {method_id}): "
                            f"Rs. {payment_amount:.2f}"
                        )
                        cursor.execute("""
                            INSERT INTO sale_payments (
                                sale_id, payment_method, amount, payment_date
                            ) VALUES (%s, %s, %s, NOW())
                        """, (invoice_id, method_id, payment_amount))
                        print(f"         ✅ Payment record created (ID: {cursor.lastrowid})")

                except ValueError as ve:
                    print(
                        f"      ⚠️ Invalid payment method value: "
                        f"{payment.get('type')} - {ve}"
                    )
                    continue
                except Exception as pe:
                    print(f"      ❌ Error processing payment {idx}: {pe}")
                    raise

        # ============================================
        # INSERT PRODUCTS WITH FIFO STOCK DEDUCTION
        # ============================================
        print(f"   📦 Processing {len(products)} products...")
        for idx, product in enumerate(products, 1):
            product_id   = int(str(product['product_id']).split('-')[0])
            variation_id = product.get('variation_id')
            if variation_id:
                variation_id = int(variation_id)

            quantity = float(product['quantity'])

            market_price   = float(product.get(
                'market_price', product.get('unit_price', product.get('price', 0))
            ))
            our_price      = float(product.get('our_price', market_price))
            selected_price = market_price

            unit_price   = float(product.get('unit_price', our_price))
            total_price  = float(product.get('total', 0))
            discount     = float(product.get('discount', 0))
            product_tax  = float(product.get('tax', 0))

            # Category discount auto-apply:
            # If the frontend sent NO manual discount, check the product's
            # category for a default discount and apply it automatically.
            product_discount_val = float(product.get('product_discount', 0))
            applied_discount_type = product.get('discount_type')

            if discount == 0 and product_discount_val == 0:
                cursor.execute("""
                    SELECT c.discount_type, c.discount_value
                    FROM categories c
                    JOIN products p ON p.category_id = c.id
                    WHERE p.id = %s AND c.discount_type IS NOT NULL
                """, (product_id,))
                cat_disc = cursor.fetchone()
                if cat_disc and cat_disc.get('discount_value', 0) and float(cat_disc['discount_value']) > 0:
                    cat_disc_type = cat_disc['discount_type']
                    cat_disc_value = float(cat_disc['discount_value'])
                    if cat_disc_type == 'percentage':
                        discount = round((our_price * cat_disc_value / 100) * quantity, 2)
                    else:  # fixed
                        discount = round(cat_disc_value * quantity, 2)
                    product_discount_val = cat_disc_value
                    applied_discount_type = cat_disc_type
                    print(f"      🏷️ Auto-applied category discount: {cat_disc_type} {cat_disc_value}")

            product_warehouse_id = product.get('warehouse_id') or warehouse_id

            if not product_warehouse_id:
                raise Exception(
                    f"Warehouse ID missing for product {product_id} (index {idx})"
                )

            # ✅ FIX: parse batch_ids sent by the frontend for this exact
            # dropdown row (comma-separated string like "12,15,18" or a
            # list of ints). These are the ONLY batches this line item is
            # allowed to deduct from. This is what fixes the bug where two
            # batches shared the same market price (pb.price) but had
            # different cost/our_price — previously the price-only match
            # could pull stock from the wrong batch.
            selected_batch_ids = _parse_batch_ids(product.get('batch_ids'))

            sales_unit_value     = product.get('sales_unit')
            sale_unit_identifier = get_unit_identifier(cursor, sales_unit_value)

            print(
                f"   📦 [{idx}/{len(products)}] Product {product_id}, "
                f"Qty {quantity}, Warehouse {product_warehouse_id}"
            )
            print(
                f"      Market Price: Rs.{market_price:.2f} | "
                f"Our Price (selling): Rs.{our_price:.2f}"
            )
            print(f"      Batch Filter Price (pb.price): Rs.{selected_price:.2f}")
            if selected_batch_ids:
                print(f"      ✅ Restricting to exact batch_ids: {selected_batch_ids}")
            else:
                print(f"      ⚠️ No batch_ids supplied — falling back to price-only match")
            print(
                f"      Sales Unit: {sales_unit_value} -> "
                f"Storing as: {sale_unit_identifier}"
            )

            available = get_available_stock_by_warehouse(
                cursor, product_id, variation_id,
                product_warehouse_id, price=selected_price,
                batch_ids=selected_batch_ids
            )

            if available < quantity:
                raise Exception(
                    f"Insufficient stock at market price Rs. {selected_price} "
                    f"in warehouse {product_warehouse_id} for product {product_id}. "
                    f"Available: {available:.2f}, Requested: {quantity:.2f}"
                )

            cursor.execute("""
                INSERT INTO sale_products (
                    invoice_id, product_id, variation_id,
                    warehouse_id,
                    price, our_price, quantity, total,
                    discount_type, product_discount,
                    tax_type, product_tax,
                    discount, tax, unit_price, sale_unit
                ) VALUES (
                    %s, %s, %s,
                    %s,
                    %s, %s, %s, %s,
                    %s, %s,
                    %s, %s,
                    %s, %s, %s, %s
                )
            """, (
                invoice_id, product_id, variation_id,
                product_warehouse_id,
                market_price, our_price, quantity, total_price,
                applied_discount_type, product_discount_val,
                product.get('tax_type'), float(product.get('product_tax', 0)),
                discount, product_tax, unit_price, sale_unit_identifier
            ))

            sale_product_id = cursor.lastrowid

            batches_used = deduct_stock_from_batches_fifo_warehouse(
                cursor, product_id, variation_id, quantity,
                product_warehouse_id, store_id, sale_product_id,
                price=selected_price,
                batch_ids=selected_batch_ids
            )

            print(f"      ✅ Used {len(batches_used)} batch(es) from WH-{product_warehouse_id}")
            for b in batches_used:
                print(
                    f"         • Batch {b['batch_id']}: {b['quantity']:.2f} units "
                    f"@ Cost Rs.{b['cost']:.2f} | "
                    f"Market Rs.{b.get('price', 'N/A')} | "
                    f"Our Price Rs.{b.get('our_price', 'N/A')}"
                )

        # ============================================
        # ✅ UPDATE QUOTATION STATUS → accepted
        # ✅ ALSO insert into invoice_sale_for_quotations_link
        # ============================================
        if quotation_id:
            try:
                cursor.execute(
                    "SELECT quotation_id, quotation_code, status FROM quotations WHERE quotation_id = %s",
                    (quotation_id,)
                )
                quotation_row = cursor.fetchone()

                if quotation_row:
                    # ✅ Mark quotation as 'accepted' — it has been converted to a sale
                    cursor.execute("""
                        UPDATE quotations
                        SET status               = 'accepted',
                            converted_to_sale_id = %s,
                            updated_at           = NOW()
                        WHERE quotation_id = %s
                    """, (invoice_id, quotation_id))

                    rows_updated = cursor.rowcount
                    print(
                        f"   ✅ Quotation {quotation_row['quotation_code']} "
                        f"(ID: {quotation_id}) marked as accepted "
                        f"→ Invoice ID: {invoice_id} "
                        f"(rows updated: {rows_updated})"
                    )

                    # ✅ Insert into invoice_sale_for_quotations_link
                    # (links invoice ↔ quotation for reporting)
                    try:
                        cursor.execute("""
                            INSERT INTO invoice_sale_for_quotations_link
                                (invoice_id, quotation_id)
                            VALUES (%s, %s)
                            ON DUPLICATE KEY UPDATE quotation_id = VALUES(quotation_id)
                        """, (invoice_id, quotation_id))
                        print(
                            f"   ✅ invoice_sale_for_quotations_link created: "
                            f"invoice {invoice_id} ↔ quotation {quotation_id}"
                        )
                    except Exception as link_err:
                        # Non-fatal — link table may not exist in all deployments
                        print(f"   ⚠️ Could not insert quotation link: {link_err}")

                else:
                    print(f"   ⚠️ Quotation ID {quotation_id} not found — skipping status update")

            except Exception as qe:
                # Non-fatal — log warning but don't fail the invoice
                print(f"   ⚠️ Could not update quotation status: {qe}")
                import traceback
                traceback.print_exc()

        # ============================================
        # COMMIT
        # ============================================
        conn.commit()
        cursor.close()
        conn.close()

        print(f"✅ Invoice {invoice_code} created/updated successfully")
        print(f"   💰 Total: {invoiceTotal}, Paid: {tenderAmount}, Balance: {remainingAmount}")
        print(
            f"   🏷️  Discount: Rs.{discount_value} "
            f"({discount_type}"
            f"{f', {discount_percentage}%' if discount_type == 'percentage' else ''})"
        )
        if quotation_id:
            print(f"   📋 Quotation {quotation_id} → accepted")

        return jsonify({
            "message":             "Invoice created/updated successfully",
            "invoice_id":          invoice_id,
            "invoice_code":        invoice_code,
            "warehouse_id":        warehouse_id,
            "store_id":            store_id,
            "payment_status":      payment_status,
            "invoice_status":      status,
            "payment_method":      primary_payment_method,
            "discount_type":       discount_type,
            "discount_percentage": discount_percentage,
            "quotation_id":        quotation_id,
        }), 200

    except Exception as e:
        if conn:
            conn.rollback()
            conn.close()
        print("❌ Error in /submit_invoice:", e)
        import traceback
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500
    
@sale_bp.route('/view_invoice_with_payments/<int:invoice_id>', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier')
def view_invoice_with_payments(invoice_id):
    conn = None
    cursor = None
    
    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        # ========================================
        # 1. GET INVOICE HEADER
        # ========================================
        cursor.execute("""
            SELECT 
                i.invoice_id, 
                i.invoice_code, 
                i.tenderAmount, 
                i.invoiceTotal, 
                i.remainingAmount, 
                i.payment_method, 
                i.payment_status, 
                i.sale_date, 
                i.customer_id, 
                i.status, 
                i.note, 
                i.discount, 
                i.tax, 
                i.warehouse_id, 
                i.store_id,
                i.cashier_user_id,
                c.name AS customer_name, 
                c.contact AS customer_contact, 
                c.address AS customer_address, 
                c.email AS customer_email,
                c.dob AS customer_dob,
                c.gender AS customer_gender,
                w.warehouse_name, 
                w.city AS warehouse_city,
                w.country AS warehouse_country,
                s.store_name,
                s.contact AS store_contact,
                s.email AS store_email,
                u.name AS cashier_name,
                u.email AS cashier_email
            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
            LEFT JOIN users u ON i.cashier_user_id = u.id
            WHERE i.invoice_id = %s
        """, (invoice_id,))
        
        invoice = cursor.fetchone()

        if not invoice:
            return jsonify({
                "success": False,
                "error": "Invoice not found",
                "message": f"No invoice exists with ID {invoice_id}"
            }), 404

        # ========================================
        # 2. GET PAYMENT METHODS BREAKDOWN
        # ========================================
        cursor.execute("""
            SELECT 
                sp.payment_method AS method_id, 
                pm.method_name, 
                sp.amount, 
                sp.payment_date,
                sp.card_number_last4,
                sp.card_type,
                sp.transaction_reference
            FROM sale_payments sp
            INNER JOIN payment_methods pm ON sp.payment_method = pm.id
            WHERE sp.sale_id = %s
            ORDER BY sp.payment_date ASC
        """, (invoice_id,))
        
        payments = cursor.fetchall()

        # ========================================
        # 3. BUILD INVOICE DETAILS OBJECT
        # ✅ FIX: added top-level "customer_id" and "customer_name"
        #    fields (in addition to the existing nested "customer"
        #    object). The edit-sale-invoice frontend was reading
        #    currentInvoiceData.customer_id directly, but this key
        #    never existed at the top level before — only nested
        #    under invoice.customer.id — so the customer was always
        #    silently treated as "not selected" and never prefilled
        #    into the customer search box on the edit screen.
        # ========================================
        invoice_details = {
            "invoice_id":      invoice['invoice_id'],
            "invoice_code":    invoice['invoice_code'],
            "sale_date":       invoice['sale_date'].strftime('%Y-%m-%d %H:%M:%S') if invoice['sale_date'] else None,
            "status":          invoice['status'],
            "note":            invoice['note'],

            "invoiceTotal":    float(invoice['invoiceTotal'])    if invoice['invoiceTotal']    else 0.00,
            "tenderAmount":    float(invoice['tenderAmount'])    if invoice['tenderAmount']    else 0.00,
            "remainingAmount": float(invoice['remainingAmount']) if invoice['remainingAmount'] else 0.00,
            "discount":        float(invoice['discount'])        if invoice['discount']        else 0.00,
            "tax":             float(invoice['tax'])             if invoice['tax']             else 0.00,

            "payment_method":  invoice['payment_method'],
            "payment_status":  invoice.get('payment_status', 'paid'),
            "payments": [
                {
                    "method_id":       p['method_id'],
                    "method_name":     p['method_name'],
                    "amount":          float(p['amount']) if p['amount'] else 0.00,
                    "payment_date":    p['payment_date'].strftime('%Y-%m-%d %H:%M:%S') if p['payment_date'] else None,
                    "card_last4":      p.get('card_number_last4'),
                    "card_type":       p.get('card_type'),
                    "transaction_ref": p.get('transaction_reference')
                }
                for p in payments
            ],
            "payment_count": len(payments),

            # ✅ FIX: top-level customer_id / customer_name so
            # frontend code that reads currentInvoiceData.customer_id
            # directly (instead of the nested customer object) works
            # correctly. This mirrors the same values inside "customer"
            # below — kept in sync, not a separate source of truth.
            "customer_id":   invoice['customer_id'],
            "customer_name": invoice['customer_name'] if invoice['customer_id'] else None,

            "customer": {
                "id":      invoice['customer_id'],
                "name":    invoice['customer_name'],
                "contact": invoice['customer_contact'],
                "email":   invoice['customer_email'],
                "address": invoice['customer_address'],
                "dob":     invoice['customer_dob'].strftime('%Y-%m-%d') if invoice.get('customer_dob') else None,
                "gender":  invoice.get('customer_gender')
            } if invoice['customer_id'] else None,

            "warehouse": {
                "id":      invoice['warehouse_id'],
                "name":    invoice['warehouse_name'],
                "city":    invoice.get('warehouse_city'),
                "country": invoice.get('warehouse_country')
            } if invoice['warehouse_id'] else None,

            "store": {
                "id":      invoice['store_id'],
                "name":    invoice['store_name'],
                "contact": invoice.get('store_contact'),
                "email":   invoice.get('store_email')
            } if invoice['store_id'] else None,

            "cashier": {
                "id":    invoice['cashier_user_id'],
                "name":  invoice['cashier_name'],
                "email": invoice['cashier_email']
            } if invoice.get('cashier_user_id') else None,

            "products": []
        }

        # ========================================
        # 4. GET PRODUCTS WITH BOTH PRICES
        # ✅ FIX: p.product_name AS product_name (base name only)
        #         pv.variation_type separately (e.g. "15", "S", "M")
        #         variation_name intentionally excluded
        # ========================================
        cursor.execute("""
            SELECT 
                sp.sale_product_id, 
                sp.product_id, 
                sp.variation_id, 
                sp.warehouse_id,
                p.product_name AS product_name,
                pv.variation_type AS variation_type,
                COALESCE(pv.variation_sku, p.sku) AS sku,
                p.product_image,
                p.barcode_symbology,
                sp.price AS market_price,
                sp.our_price AS our_selling_price,
                sp.quantity, 
                sp.total,
                sp.unit_price, 
                sp.sale_unit,
                sp.tax_type, 
                sp.product_tax, 
                sp.product_discount,
                sp.tax, 
                sp.discount_type, 
                sp.discount,
                w.warehouse_name,
                c.category_name,
                b.brand_name,
                bu.base_unit,
                u.unit_name,
                u.unit_short
            FROM sale_products sp
            INNER JOIN products p ON sp.product_id = p.id
            LEFT JOIN product_variations pv ON sp.variation_id = pv.id
            LEFT JOIN warehouses w ON sp.warehouse_id = w.id
            LEFT JOIN categories c ON p.category_id = c.id
            LEFT JOIN brands b ON p.brand_id = b.id
            LEFT JOIN base_units bu ON p.base_unit_id = bu.id
            LEFT JOIN units u ON p.sale_unit_id = u.id
            WHERE sp.invoice_id = %s
            ORDER BY sp.sale_product_id ASC
        """, (invoice_id,))
        
        products = cursor.fetchall()

        # ========================================
        # 5. PROCESS EACH PRODUCT
        # ========================================
        total_profit = 0.00
        total_cost   = 0.00

        for product in products:
            product_warehouse = product.get('warehouse_id') or invoice['warehouse_id']

            # ========================================
            # 6. GET BATCH BREAKDOWN (FIFO TRACKING)
            # ✅ MOVED UP — we need the exact batch_ids this row used
            #    BEFORE computing available_stock (see fix below).
            # ========================================
            cursor.execute("""
                SELECT 
                    spi.sale_product_item_id,
                    spi.batch_id, 
                    spi.grn_id, 
                    spi.quantity, 
                    spi.cost AS batch_cost,
                    spi.expiration_date,
                    pb.batch_number,
                    pb.price AS batch_market_price,
                    pb.our_price AS batch_our_price,
                    pb.remaining_quantity AS batch_remaining_qty,
                    g.grn_code, 
                    g.grn_date,
                    g.supplier_id,
                    s.supplier_name
                FROM sale_product_items spi
                LEFT JOIN product_batches pb ON spi.batch_id = pb.batch_id
                LEFT JOIN grn g ON spi.grn_id = g.grn_id
                LEFT JOIN suppliers s ON g.supplier_id = s.id
                WHERE spi.sale_product_id = %s
                ORDER BY spi.sale_product_item_id ASC
            """, (product['sale_product_id'],))

            batches = cursor.fetchall()

            # ✅ FIX: exact batch_ids this sale_product row actually used.
            # This is the SAME set of batches that get restored/re-deducted
            # if this row is edited — so stock must be checked against
            # THESE batches only, not every batch of the product in the
            # warehouse (which was the original bug: it summed ALL
            # price/cost groups together, inflating the editable max).
            row_batch_ids = [b['batch_id'] for b in batches if b.get('batch_id')]

            quantity = float(product['quantity'] or 0)

            # ✅ FIX: available_stock for editing purposes = 
            #   (stock currently free in this row's exact batches)
            #   + (quantity this row currently holds)
            # The "+ quantity" is required because editing this invoice
            # will RESTORE this row's quantity back to these batches
            # before re-deducting the new quantity — so the true ceiling
            # the cashier can type is the batch's free stock plus what's
            # already allocated to this row, not the sum of every batch.
            available_stock = 0
            if product_warehouse and row_batch_ids:
                available_in_batches = get_available_stock_by_warehouse(
                    cursor,
                    product['product_id'],
                    product['variation_id'],
                    product_warehouse,
                    batch_ids=row_batch_ids
                )
                available_stock = available_in_batches + quantity
            elif product_warehouse:
                # Fallback — should not normally happen (no batch records
                # found for this sale_product, e.g. legacy data).
                available_stock = get_available_stock_by_warehouse(
                    cursor,
                    product['product_id'],
                    product['variation_id'],
                    product_warehouse
                ) + quantity

            product_total_cost = sum(
                float(b['batch_cost'] or 0) * float(b['quantity'] or 0)
                for b in batches
            )

            our_price    = float(product.get('our_selling_price') or product.get('market_price') or 0)
            market_price = float(product.get('market_price') or 0)

            product_revenue = our_price * quantity
            product_profit  = product_revenue - product_total_cost

            total_cost   += product_total_cost
            total_profit += product_profit

            price_difference    = market_price - our_price
            discount_percentage = (price_difference / market_price * 100) if market_price > 0 else 0

            # ========================================
            # 7. BUILD PRODUCT OBJECT
            # ✅ FIX: product_name = base name only ("Oshman Shirt")
            #         variation_type = size value ("15") or None
            #         Frontend will combine: "Oshman Shirt — 15"
            # ✅ FIX: batch_ids added so the edit screen can send it back
            #         to update_invoices for exact-batch stock deduction.
            # ========================================
            product_data = {
                "sale_product_id": product['sale_product_id'],
                "product_id":      product['product_id'],
                "variation_id":    product['variation_id'],
                "product_name":    product['product_name'],           # ✅ "Oshman Shirt"
                "variation_type":  product.get('variation_type'),     # ✅ "15" / "S" / None
                "sku":             product['sku'],
                "product_image":   product.get('product_image'),
                "barcode_symbology": product.get('barcode_symbology'),
                "category":        product.get('category_name'),
                "brand":           product.get('brand_name'),
                "base_unit":       product.get('base_unit'),
                "sale_unit":       product['sale_unit'],
                "unit_name":       product.get('unit_name'),
                "unit_short":      product.get('unit_short'),

                "market_price":        market_price,
                "our_price":           our_price,
                "price_difference":    round(price_difference, 2),
                "discount_percentage": round(discount_percentage, 2),

                "quantity":   quantity,
                "subtotal":   float(product['total'])      if product['total']      else 0.00,
                "unit_price": float(product['unit_price']) if product['unit_price'] else 0.00,

                "tax_type":         product['tax_type'],
                "product_tax":      float(product['product_tax'])      if product['product_tax']      else 0.00,
                "tax":              float(product['tax'])               if product['tax']               else 0.00,
                "discount_type":    product['discount_type'],
                "product_discount": float(product['product_discount']) if product['product_discount'] else 0.00,
                "discount":         float(product['discount'])          if product['discount']          else 0.00,

                "total_cost":               round(product_total_cost, 2),
                "profit":                   round(product_profit, 2),
                "profit_margin_percentage": round((product_profit / product_revenue * 100) if product_revenue > 0 else 0, 2),

                "stock_quantity": available_stock,
                "warehouse_id":   product_warehouse,
                "warehouse_name": product.get('warehouse_name', 'Unknown'),

                # ✅ FIX: exact batch_ids used by this row — the edit
                # screen must send this back on update so update_invoices
                # deducts from the SAME batches (never a different one
                # sharing the same market price but different cost).
                "batch_ids": row_batch_ids,

                "batches_used": [
                    {
                        "sale_product_item_id": b['sale_product_item_id'],
                        "batch_id":             b['batch_id'],
                        "batch_number":         b['batch_number'],
                        "grn_id":               b['grn_id'],
                        "grn_code":             b['grn_code'],
                        "grn_date":             b['grn_date'].strftime('%Y-%m-%d') if b.get('grn_date') else None,
                        "supplier_id":          b.get('supplier_id'),
                        "supplier_name":        b.get('supplier_name'),
                        "quantity":             float(b['quantity']),
                        "batch_cost":           float(b['batch_cost'])         if b.get('batch_cost')         else 0.00,
                        "batch_market_price":   float(b['batch_market_price']) if b.get('batch_market_price') else 0.00,
                        "batch_our_price":      float(b['batch_our_price'])    if b.get('batch_our_price')    else 0.00,
                        "expiration_date":      b['expiration_date'].strftime('%Y-%m-%d') if b.get('expiration_date') else None,
                        "batch_remaining_qty":  float(b['batch_remaining_qty']) if b.get('batch_remaining_qty') else 0.00,
                        "batch_revenue":        round(our_price * float(b['quantity']), 2),
                        "batch_profit":         round((our_price * float(b['quantity'])) - (float(b['batch_cost'] or 0) * float(b['quantity'])), 2)
                    }
                    for b in batches
                ],
                "batch_count": len(batches)
            }

            invoice_details['products'].append(product_data)

        # ========================================
        # 8. ADD OVERALL PROFIT SUMMARY
        # ========================================
        invoice_details['profit_summary'] = {
            "total_cost":               round(total_cost, 2),
            "total_revenue":            float(invoice['invoiceTotal']) if invoice['invoiceTotal'] else 0.00,
            "gross_profit":             round(total_profit, 2),
            "profit_margin_percentage": round((total_profit / float(invoice['invoiceTotal']) * 100) if invoice['invoiceTotal'] else 0, 2),
            "total_discount_given":     float(invoice['discount']) if invoice['discount'] else 0.00,
            "total_tax":                float(invoice['tax'])      if invoice['tax']      else 0.00
        }

        # ========================================
        # 9. ADD SUMMARY COUNTS
        # ========================================
        invoice_details['summary'] = {
            "total_items":        len(products),
            "total_quantity":     sum(float(p['quantity'] or 0) for p in products),
            "unique_products":    len(set(p['product_id'] for p in products)),
            "total_batches_used": sum(len(p['batches_used']) for p in invoice_details['products'])
        }

        cursor.close()
        conn.close()

        return jsonify({
            "success": True,
            "invoice": invoice_details
        }), 200

    except Exception as e:
        print("❌ Error in view_invoice_with_payments:", str(e))
        import traceback
        traceback.print_exc()

        if cursor:
            cursor.close()
        if conn:
            conn.close()

        return jsonify({
            "success": False,
            "error": "Internal server error",
            "message": str(e)
        }), 500
        
@sale_bp.route('/check_stock_availability', methods=['POST'])
@jwt_required()
@role_required('admin', 'cashier')
def check_stock_availability():
    """
    Check if sufficient stock is available in specific warehouses for given products.
    ✅ Supports checking each product in its own warehouse
    ✅ Supports price-aware stock checking using our_price
    ✅ FIXED: Supports batch_ids-exact checking, matching the same logic used
       during actual invoice submission (submit_invoice), so this pre-check
       never gives a false "available" result that submit_invoice then rejects
       (or worse, silently pulls from a different batch).
    """
    try:
        data = request.get_json()
        products = data.get('products', [])
        
        if not products:
            return jsonify({"error": "Products array is required"}), 400
        
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)
        
        results = []
        all_available = True
        
        for product in products:
            product_id = int(str(product['product_id']).split('-')[0])
            variation_id = product.get('variation_id')
            if variation_id:
                variation_id = int(variation_id)
            requested_qty = float(product['quantity'])
            selected_price = product.get('our_price') or product.get('price')

            # ✅ FIX: honor the exact batch_ids selected in the dropdown, same
            # as submit_invoice does, instead of only matching by price.
            selected_batch_ids = _parse_batch_ids(product.get('batch_ids'))
            
            product_warehouse_id = product.get('warehouse_id')
            
            if not product_warehouse_id:
                results.append({
                    'product_id': product_id,
                    'variation_id': variation_id,
                    'product_name': product.get('productName', 'Unknown'),
                    'error': 'Warehouse ID missing'
                })
                all_available = False
                continue
            
            # Check stock in product's specific warehouse
            available = get_available_stock_by_warehouse(
                cursor, product_id, variation_id, product_warehouse_id, 
                price=selected_price if selected_price else None,
                batch_ids=selected_batch_ids
            )
            is_available = available >= requested_qty
            
            if not is_available:
                all_available = False
            
            result_item = {
                'product_id': product_id,
                'variation_id': variation_id,
                'product_name': product.get('productName', 'Unknown'),
                'warehouse_id': product_warehouse_id,
                'requested_qty': requested_qty,
                'available_stock': available,
                'is_available': is_available,
                'shortage': max(0, requested_qty - available)
            }
            
            if selected_price:
                result_item['our_price'] = float(selected_price)

            if selected_batch_ids:
                result_item['batch_ids'] = selected_batch_ids
                
            results.append(result_item)
        
        cursor.close()
        conn.close()
        
        return jsonify({
            'all_available': all_available,
            'products': results
        }), 200
        
    except Exception as e:
        print("❌ Error checking stock:", e)
        import traceback
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500


@sale_bp.route('/update_invoices/<int:invoice_id>', methods=['PUT'])
@jwt_required()
@role_required('admin', 'cashier')
def update_invoices(invoice_id):
    """
    Update invoice with multi-warehouse support, payment methods, and batch tracking.
    ✅ Compatible with database schema
    ✅ Uses sale_product_items for FIFO tracking
    ✅ Uses market_price (pb.price) for batch lookup as a FALLBACK — mirrors submit_invoice logic
    ✅ FIXED: Now honors the exact batch_ids sent by the frontend for the
       selected price/cost/our_price dropdown row, so two batches sharing
       the same market price (pb.price) but different cost/our_price
       (e.g. MRP 2800 with cost 2500 vs MRP 2800 with cost 2300) can never
       get mixed up during stock check or FIFO deduction. This is the same
       fix applied to submit_invoice / check_stock_availability.
    ✅ FIXED: Stores both market_price and our_price in sale_products
    ✅ FIXED: Payment methods use INTEGER IDs
    """
    conn = None
    try:
        data = request.get_json(force=True)
        print(f"📥 Update Invoice {invoice_id}")
        print(json.dumps(data, indent=4, default=str))

        # ============================================
        # EXTRACT DATA
        # ============================================
        invoice_code    = data.get('invoice_code')
        tenderAmount    = float(data.get('tenderAmount', 0))
        remainingAmount = float(data.get('remainingAmount', 0))
        invoiceTotal    = float(data.get('invoiceTotal', 0))
        discount_value  = float(data.get('discountValue', 0))
        customer_id     = data.get('customer_id')
        products        = data.get('products', [])
        note            = data.get('note', '')
        payment_notes   = data.get('payment_notes', '')
        tax             = float(data.get('tax', 0))
        warehouse_id    = data.get('warehouse_id')
        store_id        = data.get('store_id')
        payment_status  = data.get('payment_status', 'paid')
        payment_methods = data.get('payment_methods', [])

        # ============================================
        # VALIDATE REQUIRED FIELDS
        # ============================================
        if not store_id:
            return jsonify({"error": "Store ID is required"}), 400

        if not products or len(products) == 0:
            return jsonify({"error": "At least one product is required"}), 400

        # ============================================
        # AUTO-DETERMINE INVOICE STATUS
        # ============================================
        if payment_status == 'paid':
            status = 'received'
        else:
            status = 'suspended'

        conn   = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        cursor.execute("SET TRANSACTION ISOLATION LEVEL READ COMMITTED")
        cursor.execute("START TRANSACTION")

        # ============================================
        # CHECK INVOICE EXISTS
        # ============================================
        cursor.execute("""
            SELECT i.*, s.store_name
            FROM invoice_sale i
            LEFT JOIN stores s ON i.store_id = s.id
            WHERE i.invoice_id = %s
        """, (invoice_id,))

        existing_invoice = cursor.fetchone()
        if not existing_invoice:
            return jsonify({"error": f"Invoice {invoice_id} not found"}), 404

        # ============================================
        # RESTORE STOCK FROM OLD SALE_PRODUCT_ITEMS
        # ============================================
        cursor.execute("""
            SELECT sale_product_id, product_id
            FROM sale_products
            WHERE invoice_id = %s
        """, (invoice_id,))

        old_sale_products = cursor.fetchall()

        print(f"   ⏪ Restoring stock from {len(old_sale_products)} products...")
        for old_sp in old_sale_products:
            restore_stock_to_batches_warehouse(
                cursor, old_sp['sale_product_id'], store_id
            )

        # ============================================
        # DELETE OLD RECORDS
        # ============================================
        cursor.execute("DELETE FROM sale_products WHERE invoice_id = %s", (invoice_id,))
        deleted_products = cursor.rowcount
        cursor.execute("DELETE FROM sale_payments WHERE sale_id = %s", (invoice_id,))
        deleted_payments = cursor.rowcount

        print(f"   🗑️ Deleted {deleted_products} old products, {deleted_payments} old payments")

        # ============================================
        # DETERMINE PRIMARY PAYMENT METHOD LABEL
        # ============================================
        if len(payment_methods) == 0:
            primary_payment_method = "unpaid"
        elif len(payment_methods) == 1:
            method_id, method_name = get_payment_method_id_and_name(
                cursor, payment_methods[0]['type']
            )
            primary_payment_method = method_name.lower()
        else:
            primary_payment_method = "multiple"

        # ============================================
        # UPDATE INVOICE HEADER
        # ============================================
        cursor.execute("""
            UPDATE invoice_sale SET
                invoice_code    = %s,
                tenderAmount    = %s,
                remainingAmount = %s,
                invoiceTotal    = %s,
                payment_method  = %s,
                payment_status  = %s,
                customer_id     = %s,
                discount        = %s,
                status          = %s,
                note            = %s,
                tax             = %s,
                warehouse_id    = %s,
                store_id        = %s
            WHERE invoice_id = %s
        """, (
            invoice_code,
            tenderAmount,
            remainingAmount,
            invoiceTotal,
            primary_payment_method,
            payment_status,
            customer_id,
            discount_value,
            status,
            f"{note}\nPayment Notes: {payment_notes}" if payment_notes else note,
            tax,
            warehouse_id,
            store_id,
            invoice_id
        ))

        print(f"   ✅ Invoice header updated (status: {status}, payment_status: {payment_status})")

        # ============================================
        # INSERT PAYMENT METHODS WITH INTEGER IDs
        # ============================================
        if payment_methods and payment_status != 'unpaid':
            print(f"   💳 Processing {len(payment_methods)} payment method(s)...")
            for idx, payment in enumerate(payment_methods, 1):
                try:
                    payment_type_value = payment.get('type')
                    payment_amount     = float(payment.get('amount', 0))

                    if payment_amount > 0:
                        method_id, method_name = get_payment_method_id_and_name(
                            cursor, payment_type_value
                        )
                        cursor.execute("""
                            INSERT INTO sale_payments (
                                sale_id, payment_method, amount, payment_date
                            ) VALUES (%s, %s, %s, NOW())
                        """, (invoice_id, method_id, payment_amount))

                        print(
                            f"      [{idx}] {method_name} (ID: {method_id}): "
                            f"Rs. {payment_amount:.2f} ✅"
                        )

                except ValueError as ve:
                    print(f"      ⚠️ Invalid payment type value: {payment.get('type')} — {ve}")
                    continue
                except Exception as pe:
                    print(f"      ❌ Error processing payment {idx}: {pe}")
                    raise
        else:
            print(f"   💳 No payments to insert (payment_status: {payment_status})")

        # ============================================
        # INSERT NEW PRODUCTS WITH FIFO STOCK DEDUCTION
        # ============================================
        print(f"   📦 Processing {len(products)} products...")

        for idx, product in enumerate(products, 1):
            product_id   = int(str(product['product_id']).split('-')[0])
            variation_id = product.get('variation_id')
            if variation_id:
                variation_id = int(variation_id)

            quantity = float(product['quantity'])

            if quantity <= 0:
                raise Exception(
                    f"Invalid quantity {quantity} for product {product_id}"
                )

            # ============================================
            # ✅ Use market_price (pb.price) for batch lookup as a FALLBACK.
            # The frontend may send 'price' as a post-discount display price
            # (e.g. 2106 after 12.25% off), which won't match pb.price (2700).
            # We must filter batches by the original market price stored at GRN time.
            # This mirrors the logic in submit_invoice exactly.
            # ============================================
            market_price   = float(product.get(
                'market_price',
                product.get('unit_price', product.get('price', 0))
            ))
            our_price      = float(product.get('our_price', market_price))
            selected_price = market_price   # ← pb.price filter (GRN market price), used only as fallback

            unit_price  = float(product.get('unit_price', our_price))
            total_price = float(product.get('total', 0))
            discount    = float(product.get('discount', 0))
            product_tax = float(product.get('tax', 0))

            # Category discount auto-apply:
            # If the frontend sent NO manual discount, check the product's
            # category for a default discount and apply it automatically.
            product_discount_val = float(product.get('product_discount', 0))
            applied_discount_type = product.get('discount_type')

            if discount == 0 and product_discount_val == 0:
                cursor.execute("""
                    SELECT c.discount_type, c.discount_value
                    FROM categories c
                    JOIN products p ON p.category_id = c.id
                    WHERE p.id = %s AND c.discount_type IS NOT NULL
                """, (product_id,))
                cat_disc = cursor.fetchone()
                if cat_disc and cat_disc.get('discount_value', 0) and float(cat_disc['discount_value']) > 0:
                    cat_disc_type = cat_disc['discount_type']
                    cat_disc_value = float(cat_disc['discount_value'])
                    if cat_disc_type == 'percentage':
                        discount = round((our_price * cat_disc_value / 100) * quantity, 2)
                    else:  # fixed
                        discount = round(cat_disc_value * quantity, 2)
                    product_discount_val = cat_disc_value
                    applied_discount_type = cat_disc_type
                    print(f"      🏷️ Auto-applied category discount: {cat_disc_type} {cat_disc_value}")

            product_warehouse_id = product.get('warehouse_id') or warehouse_id

            if not product_warehouse_id:
                raise Exception(
                    f"Warehouse ID missing for product {product_id} (index {idx})"
                )

            # ============================================
            # ✅ FIX: parse batch_ids sent by the frontend for this exact
            # dropdown row (the specific price/cost/our_price group the
            # cashier selected). These are the ONLY batches this line item
            # is allowed to check/deduct from. Without this, two batches
            # sharing the same market price (e.g. MRP 2800) but with
            # different cost (2500 vs 2300) could get mixed together and
            # stock could be deducted from the wrong batch.
            #
            # ✅ For an EXISTING invoice row (edited quantity), the frontend
            # now sends this back automatically because
            # view_invoice_with_payments returns "batch_ids" per product
            # and edit_sale_invoice.js stores + re-sends it. New rows added
            # during editing (via search_sales_products) won't have
            # batch_ids yet, so they fall back to price-only matching —
            # same as before.
            # ============================================
            selected_batch_ids = _parse_batch_ids(product.get('batch_ids'))

            # ============================================
            # RESOLVE SALE UNIT SHORT NAME
            # ============================================
            sales_unit_value     = product.get('sales_unit')
            sale_unit_identifier = get_unit_identifier(cursor, sales_unit_value)

            print(
                f"\n   📦 [{idx}/{len(products)}] Product {product_id}"
                f"{f', Variation {variation_id}' if variation_id else ''}, "
                f"Qty {quantity}, WH {product_warehouse_id}"
            )
            print(
                f"      Market Price (batch filter, fallback): Rs.{market_price:.2f} | "
                f"Our Price (selling): Rs.{our_price:.2f}"
            )
            if selected_batch_ids:
                print(f"      ✅ Restricting to exact batch_ids: {selected_batch_ids}")
            else:
                print(f"      ⚠️ No batch_ids supplied — falling back to price-only match")

            # ============================================
            # CHECK AVAILABLE STOCK — EXACT BATCH_IDS FIRST, PRICE FALLBACK
            # ============================================
            available = get_available_stock_by_warehouse(
                cursor, product_id, variation_id,
                product_warehouse_id, price=selected_price,
                batch_ids=selected_batch_ids
            )

            if available < quantity:
                raise Exception(
                    f"Insufficient stock at market price Rs. {selected_price:.2f} "
                    f"in warehouse {product_warehouse_id} for product {product_id}"
                    f"{f', variation {variation_id}' if variation_id else ''}. "
                    f"Available: {available:.2f}, Requested: {quantity:.2f}"
                )

            # ============================================
            # INSERT INTO sale_products
            # ✅ Stores BOTH market_price (price) and our_price
            # ============================================
            cursor.execute("""
                INSERT INTO sale_products (
                    invoice_id, product_id, variation_id, warehouse_id,
                    price, our_price, quantity, total,
                    discount_type, product_discount,
                    tax_type, product_tax,
                    discount, tax, unit_price, sale_unit
                ) VALUES (
                    %s, %s, %s, %s,
                    %s, %s, %s, %s,
                    %s, %s,
                    %s, %s,
                    %s, %s, %s, %s
                )
            """, (
                invoice_id, product_id, variation_id, product_warehouse_id,
                market_price, our_price, quantity, total_price,
                applied_discount_type, product_discount_val,
                product.get('tax_type'),      float(product.get('product_tax', 0)),
                discount, product_tax, unit_price, sale_unit_identifier
            ))

            sale_product_id = cursor.lastrowid

            # ============================================
            # FIFO STOCK DEDUCTION + sale_product_items
            # ✅ EXACT BATCH_IDS FIRST, PRICE FALLBACK
            # ============================================
            batches_used = deduct_stock_from_batches_fifo_warehouse(
                cursor, product_id, variation_id, quantity,
                product_warehouse_id, store_id, sale_product_id,
                price=selected_price,
                batch_ids=selected_batch_ids
            )

            print(f"      ✅ Used {len(batches_used)} batch(es) from WH-{product_warehouse_id}")
            for b in batches_used:
                print(
                    f"         • Batch {b['batch_id']}: {b['quantity']:.2f} units "
                    f"@ Cost Rs.{b['cost']:.2f} | "
                    f"Market Rs.{b.get('price', 'N/A')} | "
                    f"Our Price Rs.{b.get('our_price', 'N/A')}"
                )

        # ============================================
        # COMMIT
        # ============================================
        conn.commit()
        cursor.close()
        conn.close()

        print(f"\n✅ Invoice {invoice_code} (ID: {invoice_id}) updated successfully!")
        print(f"   💰 Total: {invoiceTotal}, Paid: {tenderAmount}, Balance: {remainingAmount}")
        print(f"   🏷️  Discount: Rs.{discount_value}")

        return jsonify({
            "message":        f"Invoice {invoice_code} updated successfully",
            "invoice_id":     invoice_id,
            "invoice_code":   invoice_code,
            "payment_status": payment_status,
            "invoice_status": status,
            "payment_method": primary_payment_method
        }), 200

    except Exception as e:
        if conn:
            conn.rollback()
            conn.close()
        print(f"❌ Error updating invoice: {e}")
        import traceback
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500
    
@sale_bp.route('/view_invoice/<int:invoice_id>', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier')
def view_invoice(invoice_id):
    """
    Get detailed invoice information with batch breakdown.
    ✅ Shows complete FIFO audit trail via sale_product_items
    ✅ FIXED: Properly handles payment_method as INTEGER FK
    """
    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        # Get invoice header
        cursor.execute("""
            SELECT 
                i.invoice_id, i.invoice_code, i.tenderAmount, i.invoiceTotal, i.remainingAmount, 
                i.payment_method, i.payment_status, i.sale_date, i.customer_id, i.status, 
                i.note, i.discount, i.tax, i.warehouse_id, i.store_id,
                c.name AS customer_name, c.contact, c.address, c.dob,
                w.warehouse_name, s.store_name, u.name AS created_by_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
            LEFT JOIN users u ON i.cashier_user_id = u.id
            WHERE i.invoice_id = %s
        """, (invoice_id,))
        
        invoice = cursor.fetchone()

        if not invoice:
            return jsonify({"error": "Invoice not found"}), 404

        # ✅ FIXED: Get payment methods with JOIN
        cursor.execute("""
            SELECT sp.payment_method as method_id, pm.method_name, 
                   sp.amount, sp.payment_date
            FROM sale_payments sp
            INNER JOIN payment_methods pm ON sp.payment_method = pm.id
            WHERE sp.sale_id = %s
            ORDER BY sp.payment_date ASC
        """, (invoice_id,))
        
        payments = cursor.fetchall()

        invoice_details = {
            "invoice_id": invoice['invoice_id'],
            "invoice_code": invoice['invoice_code'],
            "tenderAmount": float(invoice['tenderAmount']) if invoice['tenderAmount'] else 0,
            "remainingAmount": float(invoice['remainingAmount']) if invoice['remainingAmount'] else 0,
            "invoiceTotal": float(invoice['invoiceTotal']) if invoice['invoiceTotal'] else 0,
            "payment_method": invoice['payment_method'],
            "payment_status": invoice.get('payment_status', 'paid'),
            "sale_date": invoice['sale_date'].strftime('%H:%M:%S %d/%m/%Y') if invoice['sale_date'] else '',
            "status": invoice['status'],
            "note": invoice['note'],
            "discount": float(invoice['discount']) if invoice['discount'] else 0,
            "tax": float(invoice['tax']) if invoice['tax'] else 0,
            "warehouse_id": invoice['warehouse_id'],
            "warehouse_name": invoice.get('warehouse_name', 'Unknown'),
            "store_id": invoice['store_id'],
            "store_name": invoice.get('store_name', 'Unknown'),
            "customer_id": invoice['customer_id'],
            "customer_name": invoice['customer_name'],
            "contact": invoice['contact'],
            "address": invoice['address'],
            "dob": str(invoice['dob']) if invoice['dob'] else '',
            "created_by": invoice.get('created_by_name', 'System'),
            "payments": [
                {
                    "method_id": p['method_id'],
                    "method": p['method_name'],
                    "amount": float(p['amount']),
                    "date": p['payment_date'].strftime('%Y-%m-%d %H:%M:%S') if p['payment_date'] else ''
                }
                for p in payments
            ],
            "payment_count": len(payments),
            "products": []
        }

        # Calculate age if DOB exists
        if invoice['dob']:
            try:
                birth_date = datetime.strptime(str(invoice['dob']), "%Y-%m-%d")
                today_date = datetime.today()
                invoice_details["age"] = today_date.year - birth_date.year - (
                    (today_date.month, today_date.day) < (birth_date.month, birth_date.day)
                )
            except:
                invoice_details["age"] = None

        # Get products with warehouse info
        cursor.execute("""
            SELECT 
                sp.sale_product_id, sp.product_id, sp.variation_id, sp.warehouse_id,
                p.product_name, pv.variation_name, pv.variation_type,
                COALESCE(pv.variation_sku, p.sku) AS sku,
                sp.price, sp.quantity, sp.total, sp.unit_price, sp.sale_unit,
                sp.tax_type, sp.product_tax, sp.product_discount,
                sp.tax, sp.discount_type, sp.discount,
                w.warehouse_name
            FROM sale_products sp
            JOIN products p ON sp.product_id = p.id
            LEFT JOIN product_variations pv ON sp.variation_id = pv.id
            LEFT JOIN warehouses w ON sp.warehouse_id = w.id
            WHERE sp.invoice_id = %s
            ORDER BY sp.sale_product_id ASC
        """, (invoice_id,))
        
        products = cursor.fetchall()

        for product in products:
            product_warehouse_id = product.get('warehouse_id') or invoice['warehouse_id']
            
            # Get current stock
            available_stock = 0
            if product_warehouse_id:
                available_stock = get_available_stock_by_warehouse(
                    cursor, product['product_id'], product['variation_id'], product_warehouse_id
                )
            
            # Get batch breakdown from sale_product_items
            cursor.execute("""
                SELECT 
                    spi.batch_id, spi.grn_id, spi.quantity, spi.cost, spi.expiration_date,
                    pb.batch_number, pb.price as batch_price,
                    g.grn_code, g.grn_date,
                    sup.supplier_name
                FROM sale_product_items spi
                LEFT JOIN product_batches pb ON spi.batch_id = pb.batch_id
                LEFT JOIN grn g ON spi.grn_id = g.grn_id
                LEFT JOIN suppliers sup ON g.supplier_id = sup.id
                WHERE spi.sale_product_id = %s
                ORDER BY spi.sale_product_item_id ASC
            """, (product['sale_product_id'],))
            
            batches = cursor.fetchall()
            
            product_detail = {
                "product_id": product['product_id'],
                "variation_id": product['variation_id'],
                "product_name": product['product_name'],
                "variation_name": product['variation_name'],
                "variation_type": product['variation_type'],
                "sku": product['sku'],
                "price": float(product['price']) if product['price'] else 0,
                "quantity": float(product['quantity']) if product['quantity'] else 0,
                "subtotal": float(product['total']) if product['total'] else 0,
                "stock_quantity": available_stock,
                "warehouse_id": product_warehouse_id,
                "warehouse_name": product.get('warehouse_name', 'Unknown'),
                "sale_unit": product['sale_unit'],
                "unit_price": float(product['unit_price']) if product['unit_price'] else 0,
                "tax_type": product['tax_type'],
                "product_tax": float(product['product_tax']) if product['product_tax'] else 0,
                "product_discount": float(product['product_discount']) if product['product_discount'] else 0,
                "tax": float(product['tax']) if product['tax'] else 0,
                "discount_type": product['discount_type'],
                "discount": float(product['discount']) if product['discount'] else 0,
                "batches_used": [
                    {
                        "batch_id": b['batch_id'],
                        "batch_number": b['batch_number'],
                        "batch_price": float(b.get('batch_price', 0)),
                        "grn_id": b['grn_id'],
                        "grn_code": b['grn_code'],
                        "grn_date": b['grn_date'].strftime('%Y-%m-%d') if b.get('grn_date') else None,
                        "supplier_name": b.get('supplier_name'),
                        "quantity": float(b['quantity']),
                        "cost": float(b['cost']) if b.get('cost') else 0,
                        "expiration_date": b['expiration_date'].strftime('%Y-%m-%d') if b.get('expiration_date') else None
                    }
                    for b in batches
                ],
                "batch_count": len(batches)
            }
            
            invoice_details['products'].append(product_detail)

        cursor.close()
        conn.close()

        return jsonify({"invoice": invoice_details}), 200

    except Exception as e:
        print("❌ Error in view_invoice:", e)
        import traceback
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500
    
    
# ============================================
# ✅ FIXED: DELETE INVOICE - WITH SALE_PRODUCT_ITEMS
# ============================================
@sale_bp.route('/delete_invoice/<int:invoice_id>', methods=['DELETE'])
@jwt_required()
@role_required('admin')
def delete_invoice(invoice_id):
    """
    Delete invoice and restore stock using sale_product_items.
    ✅ Properly restores stock to product_batches and warehouse_stock
    """
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        # ✅ Start transaction with proper isolation level
        cursor.execute("SET TRANSACTION ISOLATION LEVEL READ COMMITTED")
        cursor.execute("START TRANSACTION")

        # Check if invoice exists
        cursor.execute("""
            SELECT warehouse_id, store_id, invoice_code, invoiceTotal
            FROM invoice_sale
            WHERE invoice_id = %s
        """, (invoice_id,))
        invoice_info = cursor.fetchone()
        
        if not invoice_info:
            return jsonify({"message": f"Invoice {invoice_id} not found"}), 404

        # ✅ Get sale_product IDs to restore stock
        cursor.execute("""
            SELECT sale_product_id, product_id
            FROM sale_products
            WHERE invoice_id = %s
        """, (invoice_id,))
        sale_products = cursor.fetchall()

        if not sale_products:
            print(f"   ℹ️ No products found for invoice {invoice_id}")

        # ✅ Restore stock using sale_product_items (WITH store_id parameter)
        print(f"   ⏪ Restoring stock from {len(sale_products)} products...")
        for item in sale_products:
            restore_stock_to_batches_warehouse(cursor, item['sale_product_id'], invoice_info['store_id'])
            print(f"      ✅ Restored stock for product {item['product_id']}")

        # Delete records (CASCADE will delete sale_product_items)
        cursor.execute("DELETE FROM sale_payments WHERE sale_id = %s", (invoice_id,))
        deleted_payments = cursor.rowcount
        
        cursor.execute("DELETE FROM sale_products WHERE invoice_id = %s", (invoice_id,))
        deleted_products = cursor.rowcount
        
        cursor.execute("DELETE FROM invoice_sale WHERE invoice_id = %s", (invoice_id,))
        
        print(f"   🗑️ Deleted {deleted_payments} payments, {deleted_products} products")

        # ✅ Commit transaction
        conn.commit()
        cursor.close()
        conn.close()

        print(f"✅ Invoice {invoice_info['invoice_code']} deleted successfully")
        return jsonify({
            "message": f"Invoice {invoice_info['invoice_code']} deleted successfully",
            "invoice_code": invoice_info['invoice_code'],
            "amount": float(invoice_info['invoiceTotal'])
        }), 200

    except Exception as e:
        if conn:
            conn.rollback()
            conn.close()
        print(f"❌ Error deleting invoice: {e}")
        import traceback
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500


# ============================================
# ✅ FIXED: VIEW ALL INVOICES - WITH INTEGER PAYMENT_METHOD
# ============================================
@sale_bp.route('/view_all_invoices', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier')
def view_all_invoices():
    """
    Get all invoices with filters - includes payment breakdown.
    ✅ Optimized: Avoids N+1 query problem
    ✅ FIXED: Properly handles INTEGER payment_method FK
    """
    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        status_filter = request.args.get('status')
        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 
                i.invoice_id, i.invoice_code, i.tenderAmount, i.remainingAmount, 
                i.invoiceTotal, i.payment_method, i.payment_status,
                i.customer_id, c.name AS customer_name, i.sale_date, i.status,
                i.warehouse_id, w.warehouse_name, i.store_id, s.store_name,
                u.name AS created_by_name, i.created_at
            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
            LEFT JOIN users u ON i.cashier_user_id = u.id
        """

        conditions = []
        params = []

        if status_filter and status_filter.lower() != "all":
            conditions.append("i.status = %s")
            params.append(status_filter)

        if date_filter and date_filter != "allTime":
            if date_filter == "today":
                conditions.append("DATE(i.sale_date) = %s")
                params.append(today)
            elif date_filter == "yesterday":
                conditions.append("DATE(i.sale_date) = %s")
                params.append(today - timedelta(days=1))
            elif date_filter == "thisWeek":
                start_of_week = today - timedelta(days=today.weekday())
                end_of_week = start_of_week + timedelta(days=6)
                conditions.append("DATE(i.sale_date) BETWEEN %s AND %s")
                params.extend([start_of_week, end_of_week])
            elif date_filter == "lastWeek":
                start_of_last_week = today - timedelta(days=today.weekday() + 7)
                end_of_last_week = start_of_last_week + timedelta(days=6)
                conditions.append("DATE(i.sale_date) BETWEEN %s AND %s")
                params.extend([start_of_last_week, end_of_last_week])
            elif date_filter == "thisMonth":
                start_of_month = today.replace(day=1)
                end_of_month = (today.replace(day=28) + timedelta(days=4)).replace(day=1) - timedelta(days=1)
                conditions.append("DATE(i.sale_date) BETWEEN %s AND %s")
                params.extend([start_of_month, end_of_month])
            elif date_filter == "lastMonth":
                first_day_this_month = today.replace(day=1)
                end_of_last_month = first_day_this_month - timedelta(days=1)
                start_of_last_month = end_of_last_month.replace(day=1)
                conditions.append("DATE(i.sale_date) BETWEEN %s AND %s")
                params.extend([start_of_last_month, end_of_last_month])
            elif date_filter == "customRange" and start_date and end_date:
                try:
                    start = datetime.strptime(start_date, "%Y-%m-%d").date()
                    end = datetime.strptime(end_date, "%Y-%m-%d").date()
                    conditions.append("DATE(i.sale_date) BETWEEN %s AND %s")
                    params.extend([start, end])
                except ValueError:
                    return jsonify({"error": "Invalid custom date format"}), 400

        if conditions:
            base_query += " WHERE " + " AND ".join(conditions)

        base_query += " ORDER BY i.sale_date DESC"
        cursor.execute(base_query, tuple(params))
        invoices = cursor.fetchall()

        if not invoices:
            return jsonify({"invoices": []}), 200

        # ✅ OPTIMIZATION: Fetch all invoice IDs
        invoice_ids = [inv['invoice_id'] for inv in invoices]
        
        # ✅ FIXED: Fetch all payments with JOIN to payment_methods table
        placeholders = ','.join(['%s'] * len(invoice_ids))
        cursor.execute(f"""
            SELECT sp.sale_id, sp.payment_method as method_id, pm.method_name, 
                   sp.amount, sp.payment_date
            FROM sale_payments sp
            INNER JOIN payment_methods pm ON sp.payment_method = pm.id
            WHERE sp.sale_id IN ({placeholders})
            ORDER BY sp.sale_id, sp.payment_date ASC
        """, tuple(invoice_ids))
        
        all_payments = cursor.fetchall()
        
        # Group payments by invoice_id
        payments_by_invoice = {}
        for payment in all_payments:
            invoice_id = payment['sale_id']
            if invoice_id not in payments_by_invoice:
                payments_by_invoice[invoice_id] = []
            payments_by_invoice[invoice_id].append(payment)

        # ✅ OPTIMIZATION: Fetch all products in ONE query
        cursor.execute(f"""
            SELECT sp.invoice_id, p.product_name, sp.price, sp.quantity, sp.total
            FROM sale_products sp
            JOIN products p ON sp.product_id = p.id
            WHERE sp.invoice_id IN ({placeholders})
            ORDER BY sp.invoice_id, sp.sale_product_id
        """, tuple(invoice_ids))
        
        all_products = cursor.fetchall()
        
        # Group products by invoice_id
        products_by_invoice = {}
        for product in all_products:
            invoice_id = product['invoice_id']
            if invoice_id not in products_by_invoice:
                products_by_invoice[invoice_id] = []
            products_by_invoice[invoice_id].append(product)

        # ✅ Build response
        all_invoice_details = []

        for invoice in invoices:
            invoice_id = invoice['invoice_id']
            
            # Get payments for this invoice
            payments = payments_by_invoice.get(invoice_id, [])
            
            # Get products for this invoice
            products = products_by_invoice.get(invoice_id, [])

            invoice_detail = {
                "invoice_id": invoice_id,
                "invoice_code": invoice['invoice_code'],
                "tenderAmount": float(invoice['tenderAmount']) if invoice['tenderAmount'] else 0,
                "remainingAmount": float(invoice['remainingAmount']) if invoice['remainingAmount'] else 0,
                "invoiceTotal": float(invoice['invoiceTotal']) if invoice['invoiceTotal'] else 0,
                "payment_method": invoice['payment_method'],
                "payment_status": invoice.get('payment_status', 'paid'),
                "payment_type": invoice['payment_method'],
                "customer_id": invoice['customer_id'],
                "customer_name": invoice['customer_name'] or 'Walk-in Customer',
                "sale_date": invoice['sale_date'].strftime('%Y-%m-%d %H:%M:%S') if invoice['sale_date'] else '',
                "status": invoice['status'],
                "warehouse_id": invoice['warehouse_id'],
                "warehouse_name": invoice['warehouse_name'] or 'Unknown',
                "store_id": invoice['store_id'],
                "store_name": invoice['store_name'] or 'Unknown',
                "created_by": invoice['created_by_name'] or 'System',
                "created_by_name": invoice['created_by_name'] or 'System',
                "created_at": invoice['created_at'].strftime('%Y-%m-%d %H:%M:%S') if invoice.get('created_at') else '',
                "payments": [
                    {
                        "method_id": p['method_id'],
                        "method": p['method_name'],
                        "amount": float(p['amount']),
                        "date": p['payment_date'].strftime('%Y-%m-%d %H:%M:%S') if p['payment_date'] else ''
                    }
                    for p in payments
                ],
                "payment_count": len(payments),
                "products": [
                    {
                        "product_name": p['product_name'], 
                        "price": float(p['price']) if p['price'] else 0, 
                        "quantity": float(p['quantity']) if p['quantity'] else 0, 
                        "total": float(p['total']) if p['total'] else 0
                    }
                    for p in products
                ]
            }

            all_invoice_details.append(invoice_detail)

        cursor.close()
        conn.close()

        return jsonify({"invoices": all_invoice_details}), 200

    except Exception as e:
        print("❌ Error in view_all_invoices:", e)
        import traceback
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500


# ============================================
# ✅ CORRECTED: DASHBOARD TOP SELLING PRODUCTS
# ============================================
@sale_bp.route('/dashboard_top_selling_products', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier')
def dashboard_top_selling_products():
    """
    Get top 5 selling products for dashboard.
    ✅ Fixed: Added unit_name, variation_type, and proper SKU handling
    """
    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        cursor.execute("""
            SELECT 
                sp.product_id,
                p.product_name,
                p.sku AS product_sku,
                
                sp.variation_id,
                pv.variation_name,
                pv.variation_type,
                pv.variation_sku,
                
                -- Total quantity sold
                SUM(sp.quantity) AS total_quantity,
                
                -- Total sales value
                SUM(sp.total) AS total_sales,
                
                -- Unit price (max or avg)
                MAX(sp.unit_price) AS unit_price,
                
                -- Sale unit abbreviation
                MAX(sp.sale_unit) AS sale_unit,
                
                -- ✅ Sale unit full name
                u.unit_name AS sale_unit_name
                
            FROM sale_products sp
            JOIN products p ON sp.product_id = p.id
            LEFT JOIN product_variations pv ON sp.variation_id = pv.id
            JOIN invoice_sale i ON sp.invoice_id = i.invoice_id
            LEFT JOIN units u ON p.sale_unit_id = u.id
            WHERE i.status = 'received'
            GROUP BY 
                sp.product_id,
                p.product_name,
                p.sku,
                sp.variation_id,
                pv.variation_name,
                pv.variation_type,
                pv.variation_sku,
                u.unit_name
            ORDER BY total_quantity DESC
            LIMIT 5
        """)

        results = cursor.fetchall()
        top_products = []
        
        for product in results:
            # Determine which SKU to display
            display_sku = product['variation_sku'] if product['variation_id'] else product['product_sku']
            
            product_data = {
                "product_id": product['product_id'],
                "product_name": product['product_name'],
                "product_sku": product['product_sku'],
                "variation_id": product['variation_id'],
                "variation_name": product['variation_name'],
                "variation_type": product['variation_type'],
                "variation_sku": product['variation_sku'],
                "sku": display_sku,  # ✅ Display SKU (variation or product)
                "total_quantity": float(product['total_quantity']) if product['total_quantity'] else 0,
                "total_sales": float(product['total_sales']) if product['total_sales'] else 0,
                "unit_price": float(product['unit_price']) if product['unit_price'] else 0,
                "sale_unit": product['sale_unit'] or '',
                "sale_unit_name": product['sale_unit_name'] or ''
            }
            
            top_products.append(product_data)
        
        cursor.close()
        conn.close()

        return jsonify({
            "message": "Dashboard top selling products fetched successfully",
            "top_products": top_products
        }), 200

    except Exception as e:
        print("❌ Error in dashboard_top_selling_products:", e)
        import traceback
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500

# ============================================
# ENDPOINT 1: Get Top Selling Products (Summary)
# ============================================
@sale_bp.route('/top_selling_products', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier')
def top_selling_products():
    try:
        store_id = request.args.get('store_id', None)
        
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        # Build WHERE clause
        where_conditions = ["i.status = 'received'"]
        params = []
        
        if store_id:
            where_conditions.append("i.store_id = %s")
            params.append(store_id)
        
        where_clause = " AND ".join(where_conditions)

        cursor.execute(f"""
            SELECT 
                sp.product_id,
                p.product_name,
                p.sku AS product_sku,
                
                sp.variation_id,
                pv.variation_name,
                pv.variation_type,
                pv.variation_sku,
                
                SUM(sp.quantity) AS total_quantity,
                AVG(sp.unit_price) AS avg_price,
                SUM(sp.total) AS total_sales,
                MAX(sp.sale_unit) AS sale_unit,
                u.unit_name AS sale_unit_name
                
            FROM sale_products sp
            JOIN products p ON sp.product_id = p.id
            LEFT JOIN product_variations pv ON sp.variation_id = pv.id
            JOIN invoice_sale i ON sp.invoice_id = i.invoice_id
            LEFT JOIN units u ON p.sale_unit_id = u.id
            WHERE {where_clause}
            GROUP BY 
                sp.product_id,
                p.product_name,
                p.sku,
                sp.variation_id,
                pv.variation_name,
                pv.variation_type,
                pv.variation_sku,
                u.unit_name
            ORDER BY total_quantity DESC
        """, params)

        results = cursor.fetchall()
        top_products = []
        
        for product in results:
            total_quantity = float(product['total_quantity']) if product['total_quantity'] else 0
            avg_price = float(product['avg_price']) if product['avg_price'] else 0
            total_sales = float(product['total_sales']) if product['total_sales'] else 0
            
            display_sku = product['variation_sku'] if product['variation_id'] else product['product_sku']
            
            product_data = {
                "product_id": product['product_id'],
                "product_name": product['product_name'],
                "product_sku": product['product_sku'],
                "variation_id": product['variation_id'],
                "variation_name": product['variation_name'],
                "variation_type": product['variation_type'],
                "variation_sku": product['variation_sku'],
                "sku": display_sku,
                "avg_price": avg_price,
                "total_quantity": total_quantity,
                "sale_unit": product['sale_unit'] or '',
                "sale_unit_name": product['sale_unit_name'] or '',
                "total_sales": total_sales
            }
            
            top_products.append(product_data)
        
        cursor.close()
        conn.close()

        return jsonify({
            "message": "Top selling products fetched successfully",
            "top_products": top_products
        }), 200

    except Exception as e:
        print("❌ Error in top_selling_products:", e)
        import traceback
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500


# ============================================
# ENDPOINT 2: Get Price Breakdown for Product
# ============================================
@sale_bp.route('/product_price_breakdown', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier')
def product_price_breakdown():
    try:
        product_id = request.args.get('product_id')
        variation_id = request.args.get('variation_id', None)
        store_id = request.args.get('store_id', None)
        
        if not product_id:
            return jsonify({"error": "product_id is required"}), 400
        
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        # Build store filter
        store_filter = ""
        base_params = [product_id]
        
        if store_id:
            store_filter = "AND i.store_id = %s"

        if variation_id and variation_id != 'null':
            params = [product_id, variation_id]
            if store_id:
                params.append(store_id)
            cursor.execute(f"""
                SELECT 
                    sp.unit_price,
                    SUM(sp.quantity) AS quantity_sold,
                    SUM(sp.total) AS total_sales,
                    MAX(sp.sale_unit) AS sale_unit,
                    u.unit_name AS sale_unit_name
                FROM sale_products sp
                JOIN invoice_sale i ON sp.invoice_id = i.invoice_id
                JOIN products p ON sp.product_id = p.id
                LEFT JOIN units u ON p.sale_unit_id = u.id
                WHERE sp.product_id = %s 
                  AND sp.variation_id = %s
                  AND i.status = 'received'
                  {store_filter}
                GROUP BY sp.unit_price, u.unit_name
                ORDER BY quantity_sold DESC
            """, params)
        else:
            params = [product_id]
            if store_id:
                params.append(store_id)
            cursor.execute(f"""
                SELECT 
                    sp.unit_price,
                    SUM(sp.quantity) AS quantity_sold,
                    SUM(sp.total) AS total_sales,
                    MAX(sp.sale_unit) AS sale_unit,
                    u.unit_name AS sale_unit_name
                FROM sale_products sp
                JOIN invoice_sale i ON sp.invoice_id = i.invoice_id
                JOIN products p ON sp.product_id = p.id
                LEFT JOIN units u ON p.sale_unit_id = u.id
                WHERE sp.product_id = %s 
                  AND sp.variation_id IS NULL
                  AND i.status = 'received'
                  {store_filter}
                GROUP BY sp.unit_price, u.unit_name
                ORDER BY quantity_sold DESC
            """, params)

        results = cursor.fetchall()
        
        price_breakdown = []
        for row in results:
            price_breakdown.append({
                "unit_price": float(row['unit_price']) if row['unit_price'] else 0,
                "quantity_sold": float(row['quantity_sold']) if row['quantity_sold'] else 0,
                "total_sales": float(row['total_sales']) if row['total_sales'] else 0,
                "sale_unit": row['sale_unit'] or '',
                "sale_unit_name": row['sale_unit_name'] or ''
            })
        
        cursor.close()
        conn.close()

        return jsonify({
            "message": "Price breakdown fetched successfully",
            "price_breakdown": price_breakdown
        }), 200

    except Exception as e:
        print("❌ Error in product_price_breakdown:", e)
        import traceback
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500
# ============================================
# ✅ FIXED: PROFIT VIEW ALL INVOICES - WITH OUR_PRICE SUPPORT
# ============================================
@sale_bp.route('/profit_view_all_invoices_sales', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier')
def profit_view_all_invoices_sales():
    conn = None
    cursor = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        status_filter = request.args.get('status')
        date_filter = request.args.get('dateFilter')
        start_date = request.args.get('startDate')
        end_date = request.args.get('endDate')

        today = datetime.now().date()

        conditions = []
        params = []

        if status_filter and status_filter.lower() != "all":
            conditions.append("i.status = %s")
            params.append(status_filter)

        if date_filter and date_filter != "allTime":
            if date_filter == "today":
                conditions.append("DATE(i.sale_date) = %s")
                params.append(today)
            elif date_filter == "yesterday":
                conditions.append("DATE(i.sale_date) = %s")
                params.append(today - timedelta(days=1))
            elif date_filter == "thisWeek":
                start_of_week = today - timedelta(days=today.weekday())
                conditions.append("DATE(i.sale_date) BETWEEN %s AND %s")
                params.extend([start_of_week, start_of_week + timedelta(days=6)])
            elif date_filter == "lastWeek":
                start_of_last_week = today - timedelta(days=today.weekday() + 7)
                conditions.append("DATE(i.sale_date) BETWEEN %s AND %s")
                params.extend([start_of_last_week, start_of_last_week + timedelta(days=6)])
            elif date_filter == "thisMonth":
                start_of_month = today.replace(day=1)
                end_of_month = (today.replace(day=28) + timedelta(days=4)).replace(day=1) - timedelta(days=1)
                conditions.append("DATE(i.sale_date) BETWEEN %s AND %s")
                params.extend([start_of_month, end_of_month])
            elif date_filter == "lastMonth":
                first_day_this_month = today.replace(day=1)
                end_of_last_month = first_day_this_month - timedelta(days=1)
                start_of_last_month = end_of_last_month.replace(day=1)
                conditions.append("DATE(i.sale_date) BETWEEN %s AND %s")
                params.extend([start_of_last_month, end_of_last_month])
            elif date_filter == "customRange" and start_date and end_date:
                try:
                    start = datetime.strptime(start_date, "%Y-%m-%d").date()
                    end = datetime.strptime(end_date, "%Y-%m-%d").date()
                    conditions.append("DATE(i.sale_date) BETWEEN %s AND %s")
                    params.extend([start, end])
                except ValueError:
                    return jsonify({"error": "Invalid custom date format"}), 400

        where_clause = "WHERE " + " AND ".join(conditions) if conditions else ""

        main_query = f"""
            SELECT 
                i.invoice_id,
                i.invoice_code,
                i.tenderAmount,
                i.remainingAmount,
                i.invoiceTotal,
                i.payment_method,
                i.payment_status,
                i.discount,
                i.discount_type,
                i.discount_percentage,
                i.tax,
                i.customer_id,
                c.name AS customer_name,
                i.sale_date,
                i.status,

                sp.sale_product_id,
                sp.product_id,
                p.product_name,
                sp.quantity,
                sp.price          AS market_price,
                sp.our_price      AS our_price,
                sp.unit_price,
                sp.total          AS line_total,
                sp.discount       AS line_discount,
                sp.tax            AS line_tax,

                -- ✅ Weighted Average Cost from sale_product_items (FIFO batch cost)
                COALESCE(
                    (
                        SELECT 
                            SUM(spi.quantity * spi.cost) / NULLIF(SUM(spi.quantity), 0)
                        FROM sale_product_items spi
                        WHERE spi.sale_product_id = sp.sale_product_id
                    ), 0
                ) AS weighted_avg_cost,

                -- ✅ Total cost for this line item
                COALESCE(
                    (
                        SELECT SUM(spi.quantity * spi.cost)
                        FROM sale_product_items spi
                        WHERE spi.sale_product_id = sp.sale_product_id
                    ), 0
                ) AS total_batch_cost

            FROM invoice_sale i
            LEFT JOIN customers c ON i.customer_id = c.id
            JOIN sale_products sp ON sp.invoice_id = i.invoice_id
            JOIN products p ON sp.product_id = p.id
            {where_clause}
            ORDER BY i.sale_date DESC, i.invoice_id
        """

        cursor.execute(main_query, tuple(params))
        rows = cursor.fetchall()

        invoices_map = {}

        for row in rows:
            inv_id = row['invoice_id']

            if inv_id not in invoices_map:
                invoices_map[inv_id] = {
                    "invoice_id": inv_id,
                    "invoice_code": row['invoice_code'],
                    "tenderAmount": float(row['tenderAmount'] or 0),
                    "remainingAmount": float(row['remainingAmount'] or 0),
                    "invoiceTotal": float(row['invoiceTotal'] or 0),
                    "payment_method": row['payment_method'],
                    "payment_status": row['payment_status'],
                    "discount": float(row['discount'] or 0),
                    "discount_type": row['discount_type'],
                    "discount_percentage": float(row['discount_percentage'] or 0),
                    "tax": float(row['tax'] or 0),
                    "customer_id": row['customer_id'],
                    "customer_name": row['customer_name'],
                    "sale_date": row['sale_date'].strftime('%Y-%m-%d %H:%M:%S') if row['sale_date'] else '',
                    "status": row['status'],
                    "products": [],
                    "products_total": 0.0,   # sum of sp.total (actual billed amount)
                    "cost_total": 0.0,        # sum of actual batch costs
                    "gross_profit": 0.0       # products_total - cost_total
                }

            # ✅ Use actual selling price from our_price if available, fallback to unit_price then price
            our_price = float(row['our_price'] or 0)
            market_price = float(row['market_price'] or 0)
            unit_price = float(row['unit_price'] or 0)

            # Resolve the actual selling price (same logic as your COALESCE in old query)
            if our_price > 0:
                selling_price = our_price
            elif unit_price > 0:
                selling_price = unit_price
            else:
                selling_price = market_price

            quantity = float(row['quantity'] or 0)
            
            # ✅ Use sp.total (actual billed amount including discounts/tax)
            line_total = float(row['line_total'] or 0)
            
            # ✅ Use total_batch_cost (sum of qty * cost from sale_product_items)
            # This is the true FIFO cost for this line
            total_batch_cost = float(row['total_batch_cost'] or 0)
            weighted_avg_cost = float(row['weighted_avg_cost'] or 0)

            # ✅ Correct profit = actual revenue - actual cost
            # Using line_total (sp.total) as revenue so discounts are properly reflected
            profit = line_total - total_batch_cost

            invoices_map[inv_id]['products'].append({
                "product_name": row['product_name'],
                "market_price": market_price,
                "our_price": our_price,
                "selling_price": selling_price,
                "unit_price": unit_price,
                "weighted_avg_cost": weighted_avg_cost,
                "quantity": quantity,
                "line_total": line_total,
                "total_batch_cost": total_batch_cost,
                "profit": round(profit, 2)
            })

            invoices_map[inv_id]['products_total'] += line_total
            invoices_map[inv_id]['cost_total'] += total_batch_cost
            invoices_map[inv_id]['gross_profit'] += profit

        # ✅ Round final values
        all_invoice_details = []
        for inv in invoices_map.values():
            inv['products_total'] = round(inv['products_total'], 2)
            inv['cost_total'] = round(inv['cost_total'], 2)
            inv['gross_profit'] = round(inv['gross_profit'], 2)
            all_invoice_details.append(inv)

        return jsonify({
            "invoices": all_invoice_details,
            "summary": {
                "total_invoices": len(all_invoice_details),
                "grand_invoice_total": round(sum(inv['invoiceTotal'] for inv in all_invoice_details), 2),
                "grand_products_total": round(sum(inv['products_total'] for inv in all_invoice_details), 2),
                "grand_cost_total": round(sum(inv['cost_total'] for inv in all_invoice_details), 2),
                "grand_gross_profit": round(sum(inv['gross_profit'] for inv in all_invoice_details), 2)
            }
        }), 200

    except Exception as e:
        print("❌ Error in profit_view_all_invoices_sales:", e)
        import traceback
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500

    finally:
        try:
            if cursor:
                cursor.close()
        except Exception:
            pass
        try:
            if conn:
                conn.close()
        except Exception:
            pass
    
# ============================================
# ✅ FIXED: DASHBOARD VIEW INVOICES - WITH OUR_PRICE
# ============================================
@sale_bp.route('/dashboard_view_all_invoices', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier')
def dashboard_view_all_invoices():
    """
    Get recent 5 invoices for dashboard.
    ✅ FIXED: Returns our_price (actual selling price)
    """
    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        status_filter = request.args.get('status')

        base_query = """
            SELECT i.invoice_id, i.invoice_code, i.tenderAmount, i.remainingAmount, 
                   i.invoiceTotal, i.payment_method, i.customer_id, 
                   c.name AS customer_name, i.sale_date, i.status
            FROM invoice_sale i
            LEFT JOIN customers c ON i.customer_id = c.id
        """

        if status_filter:
            base_query += " WHERE i.status = %s ORDER BY i.sale_date DESC LIMIT 5"
            cursor.execute(base_query, (status_filter,))
        else:
            base_query += " ORDER BY i.sale_date DESC LIMIT 5"
            cursor.execute(base_query)

        invoices = cursor.fetchall()
        all_invoice_details = []

        for invoice in invoices:
            # ✅ FIXED: Get our_price (actual selling price)
            cursor.execute("""
                SELECT 
                    p.product_name, 
                    sp.price as market_price,
                    COALESCE(sp.our_price, sp.unit_price, sp.price) as our_price,
                    sp.quantity, 
                    sp.total
                FROM sale_products sp
                JOIN products p ON sp.product_id = p.id
                WHERE sp.invoice_id = %s
            """, (invoice['invoice_id'],))
            products = cursor.fetchall()

            invoice_detail = {
                "invoice_id": invoice['invoice_id'],
                "invoice_code": invoice['invoice_code'],
                "tenderAmount": float(invoice['tenderAmount']) if invoice['tenderAmount'] else 0,
                "remainingAmount": float(invoice['remainingAmount']) if invoice['remainingAmount'] else 0,
                "invoiceTotal": float(invoice['invoiceTotal']) if invoice['invoiceTotal'] else 0,
                "payment_method": invoice['payment_method'],
                "customer_id": invoice['customer_id'],
                "customer_name": invoice['customer_name'],
                "sale_date": invoice['sale_date'].strftime('%Y-%m-%d %H:%M:%S') if invoice['sale_date'] else '',
                "status": invoice['status'],
                "products": [
                    {
                        "product_name": p['product_name'],
                        "market_price": float(p['market_price']) if p['market_price'] else 0,
                        "our_price": float(p['our_price']) if p['our_price'] else 0,
                        "price": float(p['our_price']) if p['our_price'] else 0,  # ✅ For compatibility
                        "quantity": float(p['quantity']) if p['quantity'] else 0, 
                        "total": float(p['total']) if p['total'] else 0
                    }
                    for p in products
                ]
            }

            all_invoice_details.append(invoice_detail)

        cursor.close()
        conn.close()

        return jsonify({"invoices": all_invoice_details}), 200

    except Exception as e:
        print("❌ Error in dashboard_view_all_invoices:", e)
        import traceback
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500

@sale_bp.route('/get_today_summary', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier', 'manager')
def get_today_summary():
    """
    ✅ CORRECTLY handles multiple payment methods per invoice
    ✅ FIXED: total_payments_collected now uses actual sale_payments data
    """
    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        today = date.today()
        start_time = datetime.combine(today, datetime.min.time())
        end_time = datetime.combine(today, datetime.max.time())

        # ============================================
        # 1. TOTAL SALES BY STATUS
        # ============================================
        cursor.execute("""
            SELECT 
                IFNULL(SUM(CASE WHEN status = 'received' THEN invoiceTotal ELSE 0 END), 0) as received_sales,
                IFNULL(SUM(CASE WHEN status = 'suspended' THEN invoiceTotal ELSE 0 END), 0) as suspended_sales,
                IFNULL(SUM(invoiceTotal), 0) as total_sales,
                COUNT(CASE WHEN status = 'received' THEN 1 END) as received_count,
                COUNT(CASE WHEN status = 'suspended' THEN 1 END) as suspended_count
            FROM invoice_sale
            WHERE created_at BETWEEN %s AND %s
        """, (start_time, end_time))
        sales_data = cursor.fetchone()

        # ============================================
        # 2. PAYMENT METHODS BREAKDOWN (✅ FIXED FOR MULTIPLE PAYMENTS)
        # ============================================
        cursor.execute("""
            WITH invoice_payments AS (
                SELECT 
                    sp.sale_id,
                    inv.invoiceTotal,
                    pm.id as payment_method_id,
                    pm.method_name,
                    sp.amount,
                    SUM(sp.amount) OVER (PARTITION BY sp.sale_id ORDER BY sp.id) as running_total,
                    sp.id as payment_id
                FROM sale_payments sp
                INNER JOIN invoice_sale inv ON sp.sale_id = inv.invoice_id
                INNER JOIN payment_methods pm ON sp.payment_method = pm.id
                WHERE inv.created_at BETWEEN %s AND %s
            )
            SELECT 
                payment_method_id,
                method_name,
                COUNT(DISTINCT payment_id) as transaction_count,
                SUM(
                    CASE 
                        -- If running total exceeds invoice, cap this payment
                        WHEN running_total > invoiceTotal THEN 
                            GREATEST(0, invoiceTotal - (running_total - amount))
                        ELSE amount
                    END
                ) as total_amount
            FROM invoice_payments
            GROUP BY payment_method_id, method_name
            ORDER BY total_amount DESC
        """, (start_time, end_time))
        payment_methods = cursor.fetchall()

        # ============================================
        # 3. TOTAL CASH COLLECTED (✅ FIXED)
        # ============================================
        cursor.execute("""
            WITH invoice_payments AS (
                SELECT 
                    sp.sale_id,
                    inv.invoiceTotal,
                    pm.method_name,
                    sp.amount,
                    SUM(sp.amount) OVER (PARTITION BY sp.sale_id ORDER BY sp.id) as running_total
                FROM sale_payments sp
                INNER JOIN invoice_sale inv ON sp.sale_id = inv.invoice_id
                INNER JOIN payment_methods pm ON sp.payment_method = pm.id
                WHERE inv.created_at BETWEEN %s AND %s
                AND LOWER(pm.method_name) = 'cash'
            )
            SELECT IFNULL(SUM(
                CASE 
                    WHEN running_total > invoiceTotal THEN 
                        GREATEST(0, invoiceTotal - (running_total - amount))
                    ELSE amount
                END
            ), 0) as total_cash
            FROM invoice_payments
        """, (start_time, end_time))
        cash_payment_result = cursor.fetchone()
        cash_payment = float(cash_payment_result['total_cash']) if cash_payment_result else 0.0

        # ============================================
        # 4. CARD PAYMENTS (✅ FIXED)
        # ============================================
        cursor.execute("""
            WITH invoice_payments AS (
                SELECT 
                    sp.sale_id,
                    inv.invoiceTotal,
                    pm.method_name,
                    sp.amount,
                    SUM(sp.amount) OVER (PARTITION BY sp.sale_id ORDER BY sp.id) as running_total
                FROM sale_payments sp
                INNER JOIN invoice_sale inv ON sp.sale_id = inv.invoice_id
                INNER JOIN payment_methods pm ON sp.payment_method = pm.id
                WHERE inv.created_at BETWEEN %s AND %s
                AND LOWER(pm.method_name) IN ('card', 'credit card', 'debit card', 'credit/debit card')
            )
            SELECT IFNULL(SUM(
                CASE 
                    WHEN running_total > invoiceTotal THEN 
                        GREATEST(0, invoiceTotal - (running_total - amount))
                    ELSE amount
                END
            ), 0) as total_card
            FROM invoice_payments
        """, (start_time, end_time))
        card_payment_result = cursor.fetchone()
        card_payment = float(card_payment_result['total_card']) if card_payment_result else 0.0

        # ============================================
        # 5. SALE RETURNS
        # ============================================
        cursor.execute("""
            SELECT 
                IFNULL(SUM(total_return_amount), 0) as total_return,
                COUNT(*) as return_count
            FROM sale_return
            WHERE created_at BETWEEN %s AND %s
        """, (start_time, end_time))
        return_data = cursor.fetchone()

        # ============================================
        # 6. EXPENSES
        # ============================================
        cursor.execute("""
            SELECT 
                IFNULL(SUM(amount), 0) as total_expense,
                COUNT(*) as expense_count
            FROM expenses
            WHERE created_at BETWEEN %s AND %s
        """, (start_time, end_time))
        expense_data = cursor.fetchone()

        # ============================================
        # 7. TOTAL PAYMENTS COLLECTED (✅ FIXED - NOW USES ACTUAL PAYMENTS)
        # ============================================
        cursor.execute("""
            WITH invoice_payments AS (
                SELECT 
                    sp.sale_id,
                    inv.invoiceTotal,
                    sp.amount,
                    SUM(sp.amount) OVER (PARTITION BY sp.sale_id ORDER BY sp.id) as running_total
                FROM sale_payments sp
                INNER JOIN invoice_sale inv ON sp.sale_id = inv.invoice_id
                WHERE inv.created_at BETWEEN %s AND %s
            )
            SELECT IFNULL(SUM(
                CASE 
                    -- If running total exceeds invoice, cap this payment
                    WHEN running_total > invoiceTotal THEN 
                        GREATEST(0, invoiceTotal - (running_total - amount))
                    ELSE amount
                END
            ), 0) as total_payments
            FROM invoice_payments
        """, (start_time, end_time))
        total_payments_result = cursor.fetchone()
        total_payments_collected = float(total_payments_result['total_payments']) if total_payments_result else 0.0

        # ============================================
        # 8. PAYMENT STATUS BREAKDOWN
        # ============================================
        cursor.execute("""
            SELECT 
                payment_status,
                COUNT(*) as count,
                IFNULL(SUM(invoiceTotal), 0) as total_amount,
                IFNULL(SUM(LEAST(tenderAmount, invoiceTotal)), 0) as paid_amount,
                IFNULL(SUM(remainingAmount), 0) as due_amount
            FROM invoice_sale
            WHERE created_at BETWEEN %s AND %s
            GROUP BY payment_status
        """, (start_time, end_time))
        payment_status_breakdown = cursor.fetchall()

        cursor.close()
        conn.close()

        # ============================================
        # FINAL CALCULATIONS
        # ============================================
        total_sale = float(sales_data['total_sales'])
        received_sales = float(sales_data['received_sales'])
        suspended_sales = float(sales_data['suspended_sales'])
        sale_return = float(return_data['total_return'])
        total_expense = float(expense_data['total_expense'])
        
        # Net cash = Cash collected - Returns - Expenses
        net_cash = cash_payment - sale_return - total_expense
        
        # Total cash in hand = Cash collected - Returns (before expenses)
        total_cash_in_hand = cash_payment - sale_return

        return jsonify({
            "date": today.strftime("%Y-%m-%d"),
            "total_sale": total_sale,
            "received_sales": received_sales,
            "suspended_sales": suspended_sales,
            "received_count": sales_data['received_count'],
            "suspended_count": sales_data['suspended_count'],
            "payment_methods": payment_methods,
            "payment_status": payment_status_breakdown,
            "cash_payment": cash_payment,
            "card_payment": card_payment,
            "total_payments_collected": total_payments_collected,
            "sale_return": sale_return,
            "return_count": return_data['return_count'],
            "total_expense": total_expense,
            "expense_count": expense_data['expense_count'],
            "net_cash": net_cash,
            "total_cash_in_hand": total_cash_in_hand
        }), 200

    except Exception as e:
        print(f"❌ Error in today summary: {e}")
        import traceback
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500
    
    
# ============================================
# ✅ CORRECTED: GET REGISTER STATUS
# ============================================
@sale_bp.route('/get_register_status', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier', 'manager')
def get_register_status():
    """
    ✅ FIXED: Uses actual database columns from schema
    ✅ FIXED: Corrected date filtering for expenses
    """
    try:
        user_id = request.args.get('user_id')
        
        if not user_id:
            return jsonify({
                'success': False, 
                'error': 'Missing user_id parameter'
            }), 400

        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)
        
        # Query using ACTUAL columns from your schema
        cursor.execute("""
            SELECT 
                id,
                user_id,
                store_id,
                login_time,
                logout_time,
                status,
                COALESCE(opening_balance, 0) as opening_cash,
                COALESCE(total_cash, 0) as total_cash,
                COALESCE(total_card_sales, 0) as total_card_sales,
                COALESCE(total_sale_amount, 0) as total_sale_amount,
                COALESCE(sale_return_amount, 0) as sale_return_amount,
                COALESCE(total_expense, 0) as total_expense,
                COALESCE(expected_cash, 0) as expected_cash,
                COALESCE(actual_cash, 0) as actual_cash,
                COALESCE(cash_difference, 0) as cash_difference,
                COALESCE(closing_balance, 0) as closing_balance,
                notes
            FROM close_register_logs
            WHERE user_id = %s 
              AND status = 'open'
            ORDER BY id DESC 
            LIMIT 1
        """, (user_id,))
        
        register = cursor.fetchone()

        if not register:
            cursor.close()
            conn.close()
            return jsonify({
                'success': False, 
                'message': 'No open register found for this user'
            }), 404

        register_id = register['id']
        login_time = register['login_time']
        opening_cash = float(register['opening_cash'] or 0)
        store_id = register['store_id']
        end_time = datetime.now()

        # ============================================
        # GET CASH TRANSACTIONS
        # ============================================
        cursor.execute("""
            SELECT 
                COALESCE(SUM(CASE WHEN transaction_type = 'add' THEN amount ELSE 0 END), 0) as cash_added,
                COALESCE(SUM(CASE WHEN transaction_type = 'remove' THEN amount ELSE 0 END), 0) as cash_removed
            FROM cash_transactions
            WHERE register_log_id = %s
        """, (register_id,))
        
        cash_trans = cursor.fetchone()
        cash_added = float(cash_trans['cash_added']) if cash_trans else 0
        cash_removed = float(cash_trans['cash_removed']) if cash_trans else 0

        # ============================================
        # GET CASH SALES - Check payment_method column type
        # ============================================
        cursor.execute("""
            SELECT DATA_TYPE 
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_NAME = 'sale_payments' 
            AND COLUMN_NAME = 'payment_method'
            AND TABLE_SCHEMA = DATABASE()
        """)
        
        column_info = cursor.fetchone()
        is_int_payment_method = column_info and column_info['DATA_TYPE'] in ('int', 'bigint', 'smallint')
        
        if is_int_payment_method:
            # INT version - FK to payment_methods table
            cursor.execute("""
                SELECT COALESCE(SUM(
                    CASE 
                        WHEN running_total > invoiceTotal THEN 
                            GREATEST(0, invoiceTotal - (running_total - amount))
                        ELSE amount
                    END
                ), 0) as cash_sales
                FROM (
                    SELECT 
                        sp.sale_id,
                        inv.invoiceTotal,
                        sp.amount,
                        pm.method_name,
                        SUM(sp.amount) OVER (PARTITION BY sp.sale_id ORDER BY sp.id) as running_total
                    FROM sale_payments sp
                    INNER JOIN invoice_sale inv ON sp.sale_id = inv.invoice_id
                    LEFT JOIN payment_methods pm ON sp.payment_method = pm.id
                    WHERE inv.cashier_user_id = %s
                    AND inv.sale_date BETWEEN %s AND %s
                    AND LOWER(pm.method_name) = 'cash'
                ) sub
            """, (user_id, login_time, end_time))
        else:
            # VARCHAR/ENUM version - direct string comparison
            cursor.execute("""
                SELECT COALESCE(SUM(
                    CASE 
                        WHEN running_total > invoiceTotal THEN 
                            GREATEST(0, invoiceTotal - (running_total - amount))
                        ELSE amount
                    END
                ), 0) as cash_sales
                FROM (
                    SELECT 
                        sp.sale_id,
                        inv.invoiceTotal,
                        sp.amount,
                        SUM(sp.amount) OVER (PARTITION BY sp.sale_id ORDER BY sp.id) as running_total
                    FROM sale_payments sp
                    INNER JOIN invoice_sale inv ON sp.sale_id = inv.invoice_id
                    WHERE inv.cashier_user_id = %s
                    AND inv.sale_date BETWEEN %s AND %s
                    AND LOWER(sp.payment_method) = 'cash'
                ) sub
            """, (user_id, login_time, end_time))
        
        cash_sales_result = cursor.fetchone()
        cash_sales = float(cash_sales_result['cash_sales']) if cash_sales_result else 0

        # ============================================
        # ✅ FIXED: GET EXPENSES - Using created_at instead of date
        # ============================================
        cursor.execute("""
            SELECT COALESCE(SUM(amount), 0) as total_expense
            FROM expenses
            WHERE created_by = %s
            AND created_at BETWEEN %s AND %s
            AND store_id = %s
        """, (user_id, login_time, end_time, store_id))
        
        expense_result = cursor.fetchone()
        total_expense = float(expense_result['total_expense']) if expense_result else 0

        # ============================================
        # GET SALE RETURNS
        # ============================================
        cursor.execute("""
            SELECT COALESCE(SUM(total_return_amount), 0) as total_return
            FROM sale_return
            WHERE return_date BETWEEN %s AND %s
            AND store_id = %s
        """, (login_time, end_time, store_id))
        
        return_result = cursor.fetchone()
        total_return = float(return_result['total_return']) if return_result else 0

        cursor.close()
        conn.close()

        # ============================================
        # CALCULATE CURRENT CASH IN HAND
        # ============================================
        current_cash_in_hand = (
            opening_cash + 
            cash_sales + 
            cash_added - 
            cash_removed - 
            total_expense - 
            total_return
        )

        expected_cash = current_cash_in_hand

        return jsonify({
            'success': True,
            'data': {
                'register_id': register_id,
                'user_id': register['user_id'],
                'store_id': store_id,
                'login_time': login_time.strftime('%Y-%m-%d %H:%M:%S') if login_time else None,
                'status': register['status'],
                
                # Cash breakdown
                'opening_cash': opening_cash,
                'cash_sales': cash_sales,
                'cash_added': cash_added,
                'cash_removed': cash_removed,
                'expenses': total_expense,
                'returns': total_return,
                
                # Current totals
                'cash_in_hand': current_cash_in_hand,
                'expected_cash': expected_cash,
                
                # Additional info from register
                'total_sale_amount': float(register.get('total_sale_amount') or 0),
                'total_card_sales': float(register.get('total_card_sales') or 0),
                'actual_cash': float(register.get('actual_cash') or 0),
                'cash_difference': float(register.get('cash_difference') or 0),
                'notes': register.get('notes')
            }
        }), 200

    except Exception as e:
        print(f"❌ Error in get_register_status: {e}")
        import traceback
        traceback.print_exc()
        return jsonify({
            'success': False, 
            'error': str(e)
        }), 500

# ============================================
# FIXED GET CASH SUMMARY - CORRECT EXPECTED CASH
# ============================================

@sale_bp.route('/get_cash_summary', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier', 'manager')
def get_cash_summary():
    """
    ✅ FIXED: Expected Cash = Opening + Cash Received - Expenses - Returns
    ✅ Does NOT include suspended/unpaid sales
    """
    try:
        user_id = request.args.get('user_id')
        
        if not user_id:
            return jsonify({
                'success': False,
                'error': 'Missing user_id parameter'
            }), 400

        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        # ============================================
        # STEP 1: GET OPEN REGISTER
        # ============================================
        cursor.execute("""
            SELECT 
                id,
                user_id,
                store_id,
                login_time,
                logout_time,
                status,
                COALESCE(opening_balance, 0) as opening_cash,
                COALESCE(total_cash, 0) as total_cash,
                COALESCE(total_card_sales, 0) as total_card_sales,
                COALESCE(total_sale_amount, 0) as total_sale_amount,
                COALESCE(sale_return_amount, 0) as sale_return_amount,
                COALESCE(total_expense, 0) as total_expense,
                COALESCE(expected_cash, 0) as expected_cash,
                COALESCE(actual_cash, 0) as counted_cash,
                COALESCE(cash_difference, 0) as cash_difference,
                COALESCE(closing_balance, 0) as closing_balance,
                notes
            FROM close_register_logs
            WHERE user_id = %s 
              AND status = 'open'
            ORDER BY id DESC 
            LIMIT 1
        """, (user_id,))
        
        register = cursor.fetchone()

        if not register:
            cursor.close()
            conn.close()
            return jsonify({
                'success': False,
                'error': 'No open register found for this user'
            }), 404

        register_id = register['id']
        store_id = register['store_id']
        login_time = register['login_time']
        logout_time = register['logout_time']
        status = register['status']
        
        end_time = logout_time if logout_time else datetime.now()
        opening_cash = float(register['opening_cash'] or 0)
        counted_cash = float(register['counted_cash'] or 0)

        # ============================================
        # STEP 2: MANUAL CASH TRANSACTIONS
        # ============================================
        cursor.execute("""
            SELECT 
                transaction_type,
                COUNT(*) as count,
                COALESCE(SUM(amount), 0) as total
            FROM cash_transactions
            WHERE register_log_id = %s
              AND transaction_type IN ('add', 'remove')
            GROUP BY transaction_type
        """, (register_id,))
        
        cash_transactions = cursor.fetchall()
        cash_added_manual = 0
        cash_removed_manual = 0
        
        for trans in cash_transactions:
            trans_type = trans['transaction_type']
            amount = float(trans['total'] or 0)
            
            if trans_type == 'add':
                cash_added_manual += amount
            elif trans_type == 'remove':
                cash_removed_manual += amount

        # ============================================
        # STEP 3: SALES BREAKDOWN WITH SUSPENDED DETAILS
        # ============================================
        cursor.execute("""
            SELECT 
                COUNT(*) as total_invoices,
                COALESCE(SUM(invoiceTotal), 0) as total_sale,
                
                -- Received sales (fully paid)
                COALESCE(SUM(CASE WHEN status = 'received' THEN invoiceTotal ELSE 0 END), 0) as received_sale,
                COUNT(CASE WHEN status = 'received' THEN 1 END) as received_count,
                
                -- Total suspended sales (invoice totals)
                COALESCE(SUM(CASE WHEN status = 'suspended' THEN invoiceTotal ELSE 0 END), 0) as suspended_sale,
                COUNT(CASE WHEN status = 'suspended' THEN 1 END) as suspended_count,
                
                -- Suspended - Unpaid (Full invoice total, no payments)
                COALESCE(SUM(CASE 
                    WHEN status = 'suspended' AND payment_status = 'unpaid' 
                    THEN invoiceTotal ELSE 0 
                END), 0) as suspended_unpaid_total,
                COUNT(CASE 
                    WHEN status = 'suspended' AND payment_status = 'unpaid' 
                    THEN 1 
                END) as suspended_unpaid_count,
                
                -- Suspended - Partial (Invoice total)
                COALESCE(SUM(CASE 
                    WHEN status = 'suspended' AND payment_status = 'partial' 
                    THEN invoiceTotal ELSE 0 
                END), 0) as suspended_partial_total,
                COUNT(CASE 
                    WHEN status = 'suspended' AND payment_status = 'partial' 
                    THEN 1 
                END) as suspended_partial_count
                
            FROM invoice_sale
            WHERE cashier_user_id = %s 
            AND sale_date BETWEEN %s AND %s
        """, (user_id, login_time, end_time))
        
        sale_data = cursor.fetchone()
        
        total_sale = float(sale_data['total_sale'])
        received_sale = float(sale_data['received_sale'])
        suspended_sale = float(sale_data['suspended_sale'])
        
        # Unpaid suspended sales - Full invoice total is the due amount
        suspended_unpaid_due = float(sale_data['suspended_unpaid_total'] or 0)
        suspended_unpaid_count = sale_data['suspended_unpaid_count'] or 0

        # ============================================
        # STEP 3B: CALCULATE ACTUAL DUE FOR PARTIAL PAYMENTS
        # ============================================
        cursor.execute("""
            SELECT 
                inv.invoice_id,
                inv.invoiceTotal,
                COALESCE(SUM(sp.amount), 0) as total_paid,
                (inv.invoiceTotal - COALESCE(SUM(sp.amount), 0)) as due_amount
            FROM invoice_sale inv
            LEFT JOIN sale_payments sp ON inv.invoice_id = sp.sale_id
            WHERE inv.cashier_user_id = %s
            AND inv.sale_date BETWEEN %s AND %s
            AND inv.status = 'suspended'
            AND inv.payment_status = 'partial'
            GROUP BY inv.invoice_id, inv.invoiceTotal
        """, (user_id, login_time, end_time))
        
        partial_invoices = cursor.fetchall()
        suspended_partial_due = 0
        suspended_partial_count = len(partial_invoices)
        
        for invoice in partial_invoices:
            due_amount = float(invoice['due_amount'] or 0)
            suspended_partial_due += due_amount
            
            print(f"  📊 Partial Invoice #{invoice['invoice_id']}: "
                  f"Total: Rs. {invoice['invoiceTotal']}, "
                  f"Paid: Rs. {invoice['total_paid']}, "
                  f"Due: Rs. {due_amount}")

        # Calculate total actual due amount
        total_suspended_due = suspended_unpaid_due + suspended_partial_due

        print(f"\n📊 === SUSPENDED SALES DUE AMOUNTS ===")
        print(f"  Unpaid Due: Rs. {suspended_unpaid_due:.2f} ({suspended_unpaid_count} sales)")
        print(f"  Partial Due: Rs. {suspended_partial_due:.2f} ({suspended_partial_count} sales)")
        print(f"  💰 TOTAL DUE: Rs. {total_suspended_due:.2f}")

        # ============================================
        # STEP 4: CHECK PAYMENT_METHOD COLUMN TYPE
        # ============================================
        cursor.execute("""
            SELECT DATA_TYPE 
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_NAME = 'sale_payments' 
            AND COLUMN_NAME = 'payment_method'
            AND TABLE_SCHEMA = DATABASE()
        """)
        
        column_info = cursor.fetchone()
        is_int_payment_method = column_info and column_info['DATA_TYPE'] in ('int', 'bigint', 'smallint')

        # ============================================
        # STEP 5: PAYMENT BREAKDOWN (ACTUAL CASH RECEIVED)
        # ============================================
        if is_int_payment_method:
            cursor.execute("""
                WITH invoice_payments AS (
                    SELECT 
                        sp.sale_id,
                        inv.invoiceTotal,
                        pm.method_name as payment_method,
                        sp.amount,
                        SUM(sp.amount) OVER (PARTITION BY sp.sale_id ORDER BY sp.id) as running_total
                    FROM sale_payments sp
                    INNER JOIN invoice_sale inv ON sp.sale_id = inv.invoice_id
                    LEFT JOIN payment_methods pm ON sp.payment_method = pm.id
                    WHERE inv.cashier_user_id = %s 
                    AND inv.sale_date BETWEEN %s AND %s
                )
                SELECT 
                    payment_method,
                    COUNT(DISTINCT sale_id) as transaction_count,
                    SUM(
                        CASE 
                            WHEN running_total > invoiceTotal THEN 
                                GREATEST(0, invoiceTotal - (running_total - amount))
                            ELSE amount
                        END
                    ) as total_amount
                FROM invoice_payments
                GROUP BY payment_method
                ORDER BY total_amount DESC
            """, (user_id, login_time, end_time))
        else:
            cursor.execute("""
                WITH invoice_payments AS (
                    SELECT 
                        sp.sale_id,
                        inv.invoiceTotal,
                        sp.payment_method,
                        sp.amount,
                        SUM(sp.amount) OVER (PARTITION BY sp.sale_id ORDER BY sp.id) as running_total
                    FROM sale_payments sp
                    INNER JOIN invoice_sale inv ON sp.sale_id = inv.invoice_id
                    WHERE inv.cashier_user_id = %s 
                    AND inv.sale_date BETWEEN %s AND %s
                )
                SELECT 
                    payment_method,
                    COUNT(DISTINCT sale_id) as transaction_count,
                    SUM(
                        CASE 
                            WHEN running_total > invoiceTotal THEN 
                                GREATEST(0, invoiceTotal - (running_total - amount))
                            ELSE amount
                        END
                    ) as total_amount
                FROM invoice_payments
                GROUP BY payment_method
                ORDER BY total_amount DESC
            """, (user_id, login_time, end_time))
        
        payment_methods = cursor.fetchall()
        
        cash_payment_total = 0
        card_payment_total = 0
        other_payment_total = 0
        payment_breakdown = {}
        
        for pm in payment_methods:
            method = str(pm['payment_method']) if pm['payment_method'] else 'unknown'
            amount = float(pm['total_amount'] or 0)
            payment_breakdown[method] = amount
            
            if method.lower() == 'cash':
                cash_payment_total += amount
            elif method.lower() in ('card', 'credit card', 'debit card', 'credit/debit card'):
                card_payment_total += amount
            else:
                other_payment_total += amount

        # ============================================
        # STEP 6: SALE RETURNS
        # ============================================
        cursor.execute("""
            SELECT 
                COUNT(*) as return_count,
                COALESCE(SUM(total_return_amount), 0) as total_return
            FROM sale_return
            WHERE return_date BETWEEN %s AND %s
            AND store_id = %s
        """, (login_time, end_time, store_id))
        
        return_data = cursor.fetchone()
        total_return_amount = float(return_data['total_return'] or 0)
        return_count = return_data['return_count'] or 0

        # ============================================
        # STEP 7: EXPENSES
        # ============================================
        cursor.execute("""
            SELECT 
                COUNT(*) as expense_count,
                COALESCE(SUM(amount), 0) as total_expense
            FROM expenses
            WHERE date BETWEEN %s AND %s
            AND (store_id = %s OR created_by = %s)
        """, (login_time, end_time, store_id, user_id))
        
        expense_data = cursor.fetchone()
        total_expense = float(expense_data['total_expense'] or 0)
        expense_count = expense_data['expense_count'] or 0

        # ============================================
        # STEP 8: PAYMENT STATUS BREAKDOWN
        # ============================================
        cursor.execute("""
            SELECT 
                payment_status,
                COUNT(*) as count,
                COALESCE(SUM(invoiceTotal), 0) as total_amount
            FROM invoice_sale
            WHERE cashier_user_id = %s
            AND sale_date BETWEEN %s AND %s
            GROUP BY payment_status
        """, (user_id, login_time, end_time))
        
        payment_status_breakdown = cursor.fetchall()

        cursor.close()
        conn.close()

        # ============================================
        # STEP 9: ✅ CORRECT EXPECTED CASH CALCULATION
        # ============================================
        # Expected Cash = Opening Cash + Cash Payments RECEIVED + Manual Adds - Manual Removes - Expenses - Returns
        # NOTE: This is ACTUAL CASH that should be in the register
        # It does NOT include suspended/unpaid amounts (those are still owed)
        
        total_payment = cash_payment_total + card_payment_total + other_payment_total
        
        expected_cash = (
            opening_cash +                    # Starting cash
            cash_payment_total +              # Cash payments actually RECEIVED
            cash_added_manual -               # Manual cash added
            cash_removed_manual -             # Manual cash removed
            total_expense -                   # Expenses paid out
            total_return_amount               # Returns refunded
        )
        
        current_cash_in_hand = expected_cash  # Same as expected cash
        cash_difference = counted_cash - expected_cash if counted_cash > 0 else 0

        print(f"\n💰 === EXPECTED CASH CALCULATION ===")
        print(f"  Opening Cash:        + Rs. {opening_cash:.2f}")
        print(f"  Cash Received:       + Rs. {cash_payment_total:.2f}")
        print(f"  Manual Cash Added:   + Rs. {cash_added_manual:.2f}")
        print(f"  Manual Cash Removed: - Rs. {cash_removed_manual:.2f}")
        print(f"  Expenses:            - Rs. {total_expense:.2f}")
        print(f"  Returns:             - Rs. {total_return_amount:.2f}")
        print(f"  ══════════════════════════════════")
        print(f"  EXPECTED CASH:       = Rs. {expected_cash:.2f}")
        print(f"\n  💡 NOTE: Suspended sales (Rs. {total_suspended_due:.2f}) are NOT included")
        print(f"           because that money hasn't been received yet")

        # ============================================
        # STEP 10: RETURN RESPONSE
        # ============================================
        return jsonify({
            "success": True,
            
            # Register info
            "register_id": register_id,
            "status": status,
            "login_time": login_time.strftime('%Y-%m-%d %H:%M:%S') if login_time else None,
            "logout_time": logout_time.strftime('%Y-%m-%d %H:%M:%S') if logout_time else None,
            "store_id": store_id,
            
            # Opening balance & manual transactions
            "opening_cash": opening_cash,
            "cash_added_manual": cash_added_manual,
            "cash_removed_manual": cash_removed_manual,
            
            # Sales breakdown
            "total_invoices": sale_data['total_invoices'],
            "total_sale": total_sale,
            "received_sale": received_sale,
            "suspended_sale": suspended_sale,
            "received_count": sale_data['received_count'],
            "suspended_count": sale_data['suspended_count'],
            
            # Suspended breakdown with ACTUAL DUE AMOUNTS
            "suspended_unpaid_due": suspended_unpaid_due,
            "suspended_unpaid_count": suspended_unpaid_count,
            "suspended_partial_due": suspended_partial_due,
            "suspended_partial_count": suspended_partial_count,
            "total_suspended_due": total_suspended_due,
            
            # Payment breakdown (ACTUAL RECEIVED)
            "cash_payment": cash_payment_total,
            "card_payment": card_payment_total,
            "other_payment": other_payment_total,
            "total_payment": total_payment,
            "payment_breakdown": payment_breakdown,
            "payment_methods": [
                {
                    "method": str(pm['payment_method']) if pm['payment_method'] else 'unknown',
                    "count": pm['transaction_count'],
                    "amount": float(pm['total_amount'] or 0)
                }
                for pm in payment_methods
            ],
            
            # Payment status
            "payment_status": [
                {
                    "status": ps['payment_status'],
                    "count": ps['count'],
                    "amount": float(ps['total_amount'] or 0)
                }
                for ps in payment_status_breakdown
            ],
            
            # Deductions
            "total_return_amount": total_return_amount,
            "return_count": return_count,
            "total_expense": total_expense,
            "expense_count": expense_count,
            
            # ✅ CORRECTED CASH CALCULATIONS
            "cash_in_hand": current_cash_in_hand,      # Current cash that should be in register
            "expected_cash": expected_cash,             # Expected cash (same as above)
            "counted_cash": counted_cash,               # Actual counted cash (if closing)
            "cash_difference": cash_difference,         # Difference (over/short)
            
            # Notes
            "notes": register.get('notes')
            
        }), 200

    except Exception as e:
        print(f"❌ Error in get_cash_summary: {e}")
        import traceback
        traceback.print_exc()
        return jsonify({
            "success": False,
            "error": str(e)
        }), 500


@sale_bp.route('/close_register', methods=['POST'])
@jwt_required()
@role_required('admin', 'cashier', 'manager')
def close_register():
    try:
        data = request.json

        print("\n" + "="*50)
        print("📥 CLOSE REGISTER REQUEST RECEIVED")
        print("="*50)
        print(json.dumps(data, indent=2, default=str))

        # ============================================
        # VALIDATE REQUIRED FIELDS
        # ============================================
        user_id = data.get('user_id')
        if not user_id:
            return jsonify({'success': False, 'error': 'Missing user_id'}), 400

        conn   = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        # ============================================
        # GET OPEN REGISTER
        # ============================================
        cursor.execute("""
            SELECT id, store_id, opening_balance, opening_cash,
                   login_time, status
            FROM close_register_logs
            WHERE user_id = %s
              AND status = 'open'
              AND logout_time IS NULL
            ORDER BY id DESC
            LIMIT 1
        """, (user_id,))

        register = cursor.fetchone()

        if not register:
            cursor.close()
            conn.close()
            return jsonify({
                'success': False,
                'error': 'No open register found for this user'
            }), 404

        register_id     = register['id']
        store_id        = register['store_id']
        opening_balance = float(register['opening_balance'] or 0)
        opening_cash    = float(register['opening_cash']    or 0)
        login_time      = register['login_time']
        end_time        = datetime.now()

        print(f"\n✅ Found open register:")
        print(f"   Register ID:      {register_id}")
        print(f"   Store ID:         {store_id}")
        print(f"   Opening Balance:  Rs. {opening_balance}")
        print(f"   Opening Cash:     Rs. {opening_cash}")
        print(f"   Login Time:       {login_time}")

        # ============================================
        # EXTRACT FRONTEND DATA
        # ============================================
        cash_in_hand               = float(data.get('cash_in_hand',               0))
        cash_in_hand_while_closing = float(data.get('cash_in_hand_while_closing', 0))
        total_sale_amount          = float(data.get('total_sale_amount',          0))
        total_payment              = float(data.get('total_payment',              0))
        sale_return_amount         = float(data.get('sale_return_amount',         0))
        total_expense              = float(data.get('total_expense',              0))
        notes                      = data.get('notes', '')

        # ✅ Individual card sales (kept as separate column)
        total_card_sales = float(data.get('total_card_sales', 0))

        # ✅ Receive full payment_methods_breakdown from frontend
        # Expected format: { "cash": 1200.00, "card": 500.00, "cheque": 300.00, ... }
        payment_methods_breakdown = data.get('payment_methods_breakdown', {})
        if not isinstance(payment_methods_breakdown, dict):
            payment_methods_breakdown = {}

        # ✅ Serialize to JSON string for MySQL
        payment_methods_breakdown_json = json.dumps(payment_methods_breakdown)

        expected_cash = cash_in_hand
        counted_cash  = cash_in_hand_while_closing

        print(f"\n💰 Cash Amounts:")
        print(f"   Expected Cash:         Rs. {expected_cash}")
        print(f"   Counted Cash:          Rs. {counted_cash}")
        print(f"   Total Sale Amount:     Rs. {total_sale_amount}")
        print(f"   Total Payment:         Rs. {total_payment}")
        print(f"   Sale Returns:          Rs. {sale_return_amount}")
        print(f"   Expenses:              Rs. {total_expense}")
        print(f"\n💳 Payment Methods Breakdown:")
        for method, amount in payment_methods_breakdown.items():
            print(f"   {method.capitalize():<20} Rs. {amount}")

        # ============================================
        # GET CASH TRANSACTIONS (manual add/remove)
        # ============================================
        cursor.execute("""
            SELECT
                COALESCE(SUM(CASE WHEN transaction_type = 'add'
                    THEN amount ELSE 0 END), 0) as total_added,
                COALESCE(SUM(CASE WHEN transaction_type = 'remove'
                    THEN amount ELSE 0 END), 0) as total_removed
            FROM cash_transactions
            WHERE register_log_id = %s
        """, (register_id,))

        cash_trans         = cursor.fetchone()
        total_cash_added   = float(cash_trans['total_added']   or 0)
        total_cash_removed = float(cash_trans['total_removed'] or 0)

        print(f"   Cash Added (Manual):   Rs. {total_cash_added}")
        print(f"   Cash Removed:          Rs. {total_cash_removed}")

        # ============================================
        # CALCULATE VARIANCE & CLOSING BALANCE
        # ============================================
        variance        = counted_cash - expected_cash
        closing_balance = counted_cash
        actual_cash     = counted_cash

        print(f"\n🧮 Calculations:")
        print(f"   Variance: Rs. {variance} "
              f"({'OVER' if variance > 0 else 'SHORT' if variance < 0 else 'EXACT'})")
        print(f"   Closing Balance: Rs. {closing_balance}")

        # ============================================
        # ✅ UPDATE REGISTER LOG
        # ============================================
        update_query = """
            UPDATE close_register_logs
            SET
                logout_time                  = %s,
                opening_cash                 = %s,
                counted_cash                 = %s,
                total_cash_added             = %s,
                total_cash_removed           = %s,
                opening_balance              = %s,
                closing_balance              = %s,
                expected_cash                = %s,
                actual_cash                  = %s,
                cash_difference              = %s,
                total_cash                   = %s,
                total_sale_amount            = %s,
                total_card_sales             = %s,
                payment_methods_breakdown    = %s,
                sale_return_amount           = %s,
                total_expense                = %s,
                notes                        = %s,
                status                       = 'close'
            WHERE id = %s
              AND user_id = %s
              AND status = 'open'
        """

        update_params = (
            end_time,                        # logout_time
            opening_cash,                    # opening_cash
            counted_cash,                    # counted_cash
            total_cash_added,                # total_cash_added
            total_cash_removed,              # total_cash_removed
            opening_balance,                 # opening_balance
            closing_balance,                 # closing_balance
            expected_cash,                   # expected_cash
            actual_cash,                     # actual_cash
            variance,                        # cash_difference
            expected_cash,                   # total_cash
            total_sale_amount,               # total_sale_amount
            total_card_sales,                # total_card_sales
            payment_methods_breakdown_json,  # ✅ payment_methods_breakdown (JSON)
            sale_return_amount,              # sale_return_amount
            total_expense,                   # total_expense
            notes,                           # notes
            register_id,                     # WHERE id
            user_id                          # WHERE user_id
        )

        print(f"\n📝 Executing UPDATE query...")
        cursor.execute(update_query, update_params)
        rows_affected = cursor.rowcount
        print(f"   ✅ Rows affected: {rows_affected}")

        if rows_affected == 0:
            cursor.close()
            conn.close()
            return jsonify({
                'success': False,
                'error': 'Failed to close register. Already closed or not found.'
            }), 400

        conn.commit()

        # ============================================
        # VERIFY SAVED DATA
        # ============================================
        cursor.execute("""
            SELECT
                opening_cash, counted_cash, expected_cash, actual_cash,
                cash_difference, total_cash, total_sale_amount,
                total_card_sales, payment_methods_breakdown,
                sale_return_amount, total_expense,
                status, logout_time
            FROM close_register_logs
            WHERE id = %s
        """, (register_id,))

        saved = cursor.fetchone()

        # ✅ Parse JSON back for verification logging
        saved_breakdown = {}
        if saved['payment_methods_breakdown']:
            try:
                saved_breakdown = json.loads(saved['payment_methods_breakdown'])
            except Exception:
                saved_breakdown = {}

        print(f"\n✅ DATA VERIFICATION:")
        print(f"   Status:               {saved['status']}")
        print(f"   Opening Cash:         Rs. {saved['opening_cash']}")
        print(f"   Counted Cash:         Rs. {saved['counted_cash']}")
        print(f"   Expected Cash:        Rs. {saved['expected_cash']}")
        print(f"   Variance:             Rs. {saved['cash_difference']}")
        print(f"   Total Sales:          Rs. {saved['total_sale_amount']}")
        print(f"   Card Sales:           Rs. {saved['total_card_sales']}")
        print(f"   Payment Breakdown:")
        for method, amount in saved_breakdown.items():
            print(f"      {method.capitalize():<18} Rs. {amount}")
        print(f"   Returns:              Rs. {saved['sale_return_amount']}")
        print(f"   Expenses:             Rs. {saved['total_expense']}")
        print(f"   Logout Time:          {saved['logout_time']}")

        cursor.close()
        conn.close()

        print(f"\n{'='*50}")
        print("✅ REGISTER CLOSED SUCCESSFULLY")
        print(f"{'='*50}\n")

        return jsonify({
            'success': True,
            'message': 'Register closed successfully',
            'data': {
                'register_id':                register_id,
                'variance':                   float(variance),
                'expected_cash':              float(expected_cash),
                'actual_cash':                float(counted_cash),
                'closing_balance':            float(closing_balance),
                'total_card_sales':           float(total_card_sales),
                'payment_methods_breakdown':  saved_breakdown,
                'total_sale_amount':          float(total_sale_amount),
                'sale_return_amount':         float(sale_return_amount),
                'total_expense':              float(total_expense),
                'status':                     'close'
            }
        }), 200

    except Exception as e:
        print(f"\n❌ ERROR CLOSING REGISTER: {str(e)}")
        import traceback
        traceback.print_exc()

        if 'conn' in locals() and conn:
            conn.rollback()

        return jsonify({'success': False, 'error': str(e)}), 500

    finally:
        if 'cursor' in locals() and cursor:
            cursor.close()
        if 'conn' in locals() and conn:
            conn.close()
        
# @sale_bp.route('/close_register', methods=['POST'])    
# @jwt_required()
# @role_required('admin', 'cashier', 'manager')
# def close_register():
#     """
#     ✅ FIXED: Matches ACTUAL database schema columns
#     Database columns:
#     - opening_cash (not cash_in_hand)
#     - counted_cash (not cash_in_hand_while_closing)
#     - total_cash_added
#     - total_cash_removed
#     - opening_balance
#     - closing_balance
#     - expected_cash
#     - actual_cash
#     - cash_difference
#     """
#     try:
#         data = request.json
        
#         print("\n" + "="*50)
#         print("📥 CLOSE REGISTER REQUEST RECEIVED")
#         print("="*50)
#         print("📦 Full Payload:")
#         print(json.dumps(data, indent=2, default=str))
        
#         # ============================================
#         # VALIDATE REQUIRED FIELDS
#         # ============================================
#         user_id = data.get('user_id')
        
#         if not user_id:
#             return jsonify({
#                 'success': False,
#                 'error': 'Missing user_id'
#             }), 400
        
#         conn = get_db_connection()
#         cursor = conn.cursor(dictionary=True)
        
#         # ============================================
#         # GET OPEN REGISTER
#         # ============================================
#         cursor.execute("""
#             SELECT 
#                 id, 
#                 store_id, 
#                 opening_balance,
#                 opening_cash,
#                 login_time,
#                 status
#             FROM close_register_logs
#             WHERE user_id = %s 
#               AND status = 'open'
#               AND logout_time IS NULL
#             ORDER BY id DESC
#             LIMIT 1
#         """, (user_id,))
        
#         register = cursor.fetchone()
        
#         if not register:
#             cursor.close()
#             conn.close()
#             return jsonify({
#                 'success': False,
#                 'error': 'No open register found for this user'
#             }), 404
        
#         register_id = register['id']
#         store_id = register['store_id']
#         opening_balance = float(register['opening_balance'] or 0)
#         opening_cash = float(register['opening_cash'] or 0)
#         login_time = register['login_time']
#         end_time = datetime.now()
        
#         print(f"\n✅ Found open register:")
#         print(f"   Register ID: {register_id}")
#         print(f"   Store ID: {store_id}")
#         print(f"   Opening Balance: Rs. {opening_balance}")
#         print(f"   Opening Cash: Rs. {opening_cash}")
#         print(f"   Login Time: {login_time}")
        
#         # ============================================
#         # EXTRACT FRONTEND DATA
#         # ============================================
#         # Frontend sends these fields:
#         cash_in_hand = float(data.get('cash_in_hand', 0))  # Expected cash
#         cash_in_hand_while_closing = float(data.get('cash_in_hand_while_closing', 0))  # Actual counted
#         total_sale_amount = float(data.get('total_sale_amount', 0))
#         total_payment = float(data.get('total_payment', 0))
#         sale_return_amount = float(data.get('sale_return_amount', 0))
#         total_expense = float(data.get('total_expense', 0))
#         notes = data.get('notes', '')
        
#         # ✅ Map to database column names
#         expected_cash = cash_in_hand  # What should be in register
#         counted_cash = cash_in_hand_while_closing  # What was actually counted
        
#         print(f"\n💰 Cash Amounts:")
#         print(f"   Expected Cash: Rs. {expected_cash}")
#         print(f"   Counted Cash: Rs. {counted_cash}")
#         print(f"   Total Sale Amount: Rs. {total_sale_amount}")
#         print(f"   Total Payment: Rs. {total_payment}")
#         print(f"   Sale Returns: Rs. {sale_return_amount}")
#         print(f"   Expenses: Rs. {total_expense}")
        
#         # ============================================
#         # GET CASH TRANSACTIONS (added/removed)
#         # ============================================
#         cursor.execute("""
#             SELECT 
#                 COALESCE(SUM(CASE WHEN transaction_type = 'add' THEN amount ELSE 0 END), 0) as total_added,
#                 COALESCE(SUM(CASE WHEN transaction_type = 'remove' THEN amount ELSE 0 END), 0) as total_removed
#             FROM cash_transactions
#             WHERE register_log_id = %s
#         """, (register_id,))
        
#         cash_trans = cursor.fetchone()
#         total_cash_added = float(cash_trans['total_added'] or 0)
#         total_cash_removed = float(cash_trans['total_removed'] or 0)
        
#         print(f"   Cash Added (Manual): Rs. {total_cash_added}")
#         print(f"   Cash Removed: Rs. {total_cash_removed}")
        
#         # ============================================
#         # CALCULATE CARD SALES
#         # ============================================
#         # Get payment method column type
#         cursor.execute("""
#             SELECT DATA_TYPE 
#             FROM INFORMATION_SCHEMA.COLUMNS 
#             WHERE TABLE_NAME = 'sale_payments' 
#             AND COLUMN_NAME = 'payment_method'
#             AND TABLE_SCHEMA = DATABASE()
#         """)
        
#         column_info = cursor.fetchone()
#         is_int_payment_method = column_info and column_info['DATA_TYPE'] in ('int', 'bigint', 'smallint')
        
#         if is_int_payment_method:
#             # INT version - FK to payment_methods
#             cursor.execute("""
#                 SELECT COALESCE(SUM(
#                     CASE 
#                         WHEN running_total > invoiceTotal THEN 
#                             GREATEST(0, invoiceTotal - (running_total - amount))
#                         ELSE amount
#                     END
#                 ), 0) as total_card
#                 FROM (
#                     SELECT 
#                         sp.sale_id,
#                         inv.invoiceTotal,
#                         sp.amount,
#                         pm.method_name,
#                         SUM(sp.amount) OVER (PARTITION BY sp.sale_id ORDER BY sp.id) as running_total
#                     FROM sale_payments sp
#                     INNER JOIN invoice_sale inv ON sp.sale_id = inv.invoice_id
#                     INNER JOIN payment_methods pm ON sp.payment_method = pm.id
#                     WHERE inv.cashier_user_id = %s
#                       AND inv.sale_date BETWEEN %s AND %s
#                       AND LOWER(pm.method_name) IN ('card', 'credit card', 'debit card', 'credit/debit card')
#                 ) sub
#             """, (user_id, login_time, end_time))
#         else:
#             # VARCHAR/ENUM version
#             cursor.execute("""
#                 SELECT COALESCE(SUM(
#                     CASE 
#                         WHEN running_total > invoiceTotal THEN 
#                             GREATEST(0, invoiceTotal - (running_total - amount))
#                         ELSE amount
#                     END
#                 ), 0) as total_card
#                 FROM (
#                     SELECT 
#                         sp.sale_id,
#                         inv.invoiceTotal,
#                         sp.amount,
#                         SUM(sp.amount) OVER (PARTITION BY sp.sale_id ORDER BY sp.id) as running_total
#                     FROM sale_payments sp
#                     INNER JOIN invoice_sale inv ON sp.sale_id = inv.invoice_id
#                     WHERE inv.cashier_user_id = %s
#                       AND inv.sale_date BETWEEN %s AND %s
#                       AND LOWER(sp.payment_method) IN ('card', 'credit card', 'debit card', 'credit/debit card')
#                 ) sub
#             """, (user_id, login_time, end_time))
        
#         card_result = cursor.fetchone()
#         total_card_sales = float(card_result['total_card'] or 0)
        
#         print(f"   Card Sales: Rs. {total_card_sales}")
        
#         # ============================================
#         # CALCULATE VARIANCE & CLOSING BALANCE
#         # ============================================
#         variance = counted_cash - expected_cash
#         closing_balance = counted_cash  # What was counted
#         actual_cash = counted_cash  # Same as counted
        
#         print(f"\n🧮 Calculations:")
#         print(f"   Variance: Rs. {variance} ({('OVER' if variance > 0 else 'SHORT') if variance != 0 else 'EXACT'})")
#         print(f"   Closing Balance: Rs. {closing_balance}")
        
#         # ============================================
#         # ✅ UPDATE REGISTER LOG - USING ACTUAL SCHEMA COLUMNS
#         # ============================================
#         update_query = """
#             UPDATE close_register_logs
#             SET 
#                 -- Timing
#                 logout_time = %s,
                
#                 -- Cash amounts (ACTUAL DATABASE COLUMN NAMES)
#                 opening_cash = %s,
#                 counted_cash = %s,
#                 total_cash_added = %s,
#                 total_cash_removed = %s,
#                 opening_balance = %s,
#                 closing_balance = %s,
#                 expected_cash = %s,
#                 actual_cash = %s,
#                 cash_difference = %s,
#                 total_cash = %s,
                
#                 -- Sales tracking
#                 total_sale_amount = %s,
#                 total_card_sales = %s,
                
#                 -- Deductions
#                 sale_return_amount = %s,
#                 total_expense = %s,
                
#                 -- Metadata
#                 notes = %s,
#                 status = 'close'
                
#             WHERE id = %s
#               AND user_id = %s
#               AND status = 'open'
#         """
        
#         update_params = (
#             # Timing
#             end_time,
            
#             # Cash amounts (using ACTUAL column names)
#             opening_cash,           # opening_cash
#             counted_cash,           # counted_cash (actual counted)
#             total_cash_added,       # total_cash_added (manual additions)
#             total_cash_removed,     # total_cash_removed (manual removals)
#             opening_balance,        # opening_balance
#             closing_balance,        # closing_balance
#             expected_cash,          # expected_cash
#             actual_cash,            # actual_cash
#             variance,               # cash_difference
#             expected_cash,          # total_cash
            
#             # Sales
#             total_sale_amount,      # total_sale_amount
#             total_card_sales,       # total_card_sales
            
#             # Deductions
#             sale_return_amount,     # sale_return_amount
#             total_expense,          # total_expense
            
#             # Metadata
#             notes,                  # notes
            
#             # WHERE conditions
#             register_id,            # id
#             user_id                 # user_id
#         )
        
#         print(f"\n📝 Executing UPDATE query...")
        
#         cursor.execute(update_query, update_params)
#         rows_affected = cursor.rowcount
        
#         print(f"   ✅ Rows affected: {rows_affected}")
        
#         if rows_affected == 0:
#             cursor.close()
#             conn.close()
#             return jsonify({
#                 'success': False,
#                 'error': 'Failed to close register. It may already be closed or does not exist.'
#             }), 400
        
#         conn.commit()
        
#         # ============================================
#         # VERIFY DATA WAS SAVED
#         # ============================================
#         cursor.execute("""
#             SELECT 
#                 opening_cash,
#                 counted_cash,
#                 total_cash_added,
#                 total_cash_removed,
#                 opening_balance,
#                 closing_balance,
#                 expected_cash,
#                 actual_cash,
#                 cash_difference,
#                 total_cash,
#                 total_sale_amount,
#                 total_card_sales,
#                 sale_return_amount,
#                 total_expense,
#                 status,
#                 logout_time
#             FROM close_register_logs
#             WHERE id = %s
#         """, (register_id,))
        
#         saved_data = cursor.fetchone()
        
#         print(f"\n✅ DATA VERIFICATION:")
#         print(f"   Status: {saved_data['status']}")
#         print(f"   Opening Cash: Rs. {saved_data['opening_cash']}")
#         print(f"   Counted Cash: Rs. {saved_data['counted_cash']}")
#         print(f"   Expected Cash: Rs. {saved_data['expected_cash']}")
#         print(f"   Variance: Rs. {saved_data['cash_difference']}")
#         print(f"   Total Sales: Rs. {saved_data['total_sale_amount']}")
#         print(f"   Card Sales: Rs. {saved_data['total_card_sales']}")
#         print(f"   Returns: Rs. {saved_data['sale_return_amount']}")
#         print(f"   Expenses: Rs. {saved_data['total_expense']}")
#         print(f"   Logout Time: {saved_data['logout_time']}")
        
#         cursor.close()
#         conn.close()
        
#         # ============================================
#         # SUCCESS LOGGING
#         # ============================================
#         print(f"\n" + "="*50)
#         print("✅ REGISTER CLOSED SUCCESSFULLY")
#         print("="*50)
#         print(f"   User ID: {user_id}")
#         print(f"   Register ID: {register_id}")
#         print(f"   Store ID: {store_id}")
#         print(f"   Opening Cash: Rs. {opening_cash}")
#         print(f"   Expected Cash: Rs. {expected_cash}")
#         print(f"   Counted Cash: Rs. {counted_cash}")
#         print(f"   Variance: Rs. {variance}")
#         print(f"   Total Sales: Rs. {total_sale_amount}")
#         print(f"   Card Sales: Rs. {total_card_sales}")
#         print(f"   Returns: Rs. {sale_return_amount}")
#         print(f"   Expenses: Rs. {total_expense}")
#         print(f"   Closing Balance: Rs. {closing_balance}")
#         print("="*50 + "\n")
        
#         # ============================================
#         # RETURN SUCCESS RESPONSE
#         # ============================================
#         return jsonify({
#             'success': True,
#             'message': 'Register closed successfully',
#             'data': {
#                 'register_id': register_id,
#                 'variance': float(variance),
#                 'expected_cash': float(expected_cash),
#                 'actual_cash': float(counted_cash),
#                 'closing_balance': float(closing_balance),
#                 'total_card_sales': float(total_card_sales),
#                 'total_sale_amount': float(total_sale_amount),
#                 'sale_return_amount': float(sale_return_amount),
#                 'total_expense': float(total_expense),
#                 'status': 'close'
#             }
#         }), 200
        
#     except Exception as e:
#         print(f"\n" + "="*50)
#         print("❌ ERROR CLOSING REGISTER")
#         print("="*50)
#         print(f"Error: {str(e)}")
#         import traceback
#         traceback.print_exc()
#         print("="*50 + "\n")
        
#         if 'conn' in locals() and conn:
#             conn.rollback()
        
#         return jsonify({
#             'success': False,
#             'error': str(e)
#         }), 500
        
#     finally:
#         if 'cursor' in locals() and cursor:
#             cursor.close()
#         if 'conn' in locals() and conn:
#             conn.close()


    
# ============================================
# ✅ COMPLETE: GET INVOICE (Alternative)
# ============================================
@sale_bp.route('/get_invoice/<int:invoice_id>', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier')
def get_invoice(invoice_id):
    """
    Get invoice details (alternative endpoint).
    ✅ Compatible with database schema
    """
    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        cursor.execute("SELECT * FROM invoice_sale WHERE invoice_id = %s", (invoice_id,))
        invoice = cursor.fetchone()
        
        if not invoice:
            return jsonify({"error": "Invoice not found"}), 404

        cursor.execute("""
            SELECT sp.*, p.product_name 
            FROM sale_products sp 
            JOIN products p ON sp.product_id = p.id 
            WHERE sp.invoice_id = %s
        """, (invoice_id,))
        products = cursor.fetchall()

        # Convert Decimal to float
        for key, value in invoice.items():
            if isinstance(value, Decimal):
                invoice[key] = float(value)
        
        for product in products:
            for key, value in product.items():
                if isinstance(value, Decimal):
                    product[key] = float(value)

        result = {
            "invoice": invoice,
            "products": products
        }

        cursor.close()
        conn.close()

        return jsonify(result), 200

    except Exception as e:
        print("❌ Error in /get_invoice:", e)
        import traceback
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500


# ============================================
# ✅ COMPLETE: HOLD BILL - WITH OUR_PRICE + DISCOUNT_TYPE + DISCOUNT_PERCENTAGE
# ============================================
@sale_bp.route('/hold_bill', methods=['POST'])
@jwt_required()
@role_required('admin', 'cashier')
def hold_bill():
    """
    Save a bill as 'hold' status in database.
    ✅ Stock is NOT deducted yet (no sale_product_items entries)
    ✅ Stores BOTH market_price and our_price
    ✅ Stores discount_type (fixed/percentage) to invoice_sale
    ✅ Stores discount_percentage (raw % input) to invoice_sale
    """
    conn = None
    try:
        data = request.get_json()
        print("📥 Hold Bill Data:")
        print(json.dumps(data, indent=4, default=str))

        # ============================================
        # EXTRACT DATA
        # ============================================
        customer_id   = data.get('customer_id')
        customer_name = data.get('customer_name', 'Walk-in Customer')
        warehouse_id  = data.get('warehouse_id')
        store_id      = data.get('store_id')
        products      = data.get('products', [])

        medicine_grand_total = float(data.get('medicineGrandTotal', 0))
        discount_value       = float(data.get('discountValue', 0))
        grand_total          = float(data.get('grandTotal', 0))

        # ✅ Discount type — validated
        discount_type = data.get('discountType', 'fixed')
        if discount_type not in ('fixed', 'percentage'):
            discount_type = 'fixed'

        # ✅ Raw percentage input — only meaningful when type is 'percentage'
        discount_percentage = float(data.get('discountPercentage', 0))
        if discount_type == 'fixed':
            discount_percentage = 0.0   # force to zero — no ambiguity

        # ============================================
        # VALIDATION
        # ============================================
        if not warehouse_id:
            return jsonify({"error": "Warehouse ID is required"}), 400

        if not store_id:
            return jsonify({"error": "Store ID is required"}), 400

        if not products or len(products) == 0:
            return jsonify({"error": "No products to hold"}), 400

        # ============================================
        # DATABASE SETUP
        # ============================================
        current_user = get_jwt_identity()

        conn   = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        cursor.execute("START TRANSACTION")

        # ============================================
        # GENERATE HOLD INVOICE CODE
        # ============================================
        today_str = datetime.now().strftime("%Y%m%d")
        cursor.execute(
            "SELECT COUNT(*) as count FROM invoice_sale WHERE DATE(created_at) = CURDATE()"
        )
        count        = cursor.fetchone()["count"] + 1
        invoice_code = f"HOLD-{today_str}-{count:04d}"

        # ============================================
        # INSERT INVOICE WITH status = 'hold'
        # ============================================
        cursor.execute("""
            INSERT INTO invoice_sale (
                invoice_code,
                tenderAmount, remainingAmount, invoiceTotal,
                payment_method, payment_status, customer_id,
                discount, discount_type, discount_percentage,
                status, warehouse_id, store_id, cashier_user_id, tax
            ) VALUES (
                %s,
                %s, %s, %s,
                %s, %s, %s,
                %s, %s, %s,
                %s, %s, %s, %s, %s
            )
        """, (
            invoice_code,
            0, grand_total, grand_total,
            'unpaid', 'unpaid', customer_id,
            discount_value, discount_type, discount_percentage,
            'hold', warehouse_id, store_id, current_user, 0
        ))

        invoice_id = cursor.lastrowid

        print(f"   Invoice: {invoice_code} (ID: {invoice_id})")
        print(
            f"   Discount: Rs.{discount_value} "
            f"({discount_type}"
            f"{f', {discount_percentage}%' if discount_type == 'percentage' else ''})"
        )

        # ============================================
        # INSERT PRODUCTS — NO STOCK DEDUCTION
        # ============================================
        print(f"   📦 Processing {len(products)} product(s)...")

        for idx, product in enumerate(products, 1):
            product_id   = int(product['product_id'])
            variation_id = product.get('variation_id')
            if variation_id:
                variation_id = int(variation_id)

            quantity              = float(product['quantity'])
            display_price         = float(product.get('displayPrice', 0))
            discount_value_product = float(product.get('discountValue', 0))
            total                 = float(product.get('total', 0))
            product_warehouse_id  = product.get('warehouse_id') or warehouse_id

            # Both prices
            market_price = float(product.get('market_price', 0))
            our_price    = float(
                product.get('our_price') or product.get('product_price', 0)
            )

            # Convert unit ID to short name
            sales_unit_value     = product.get('sales_unit')
            sale_unit_identifier = get_unit_identifier(cursor, sales_unit_value)

            print(
                f"   [{idx}/{len(products)}] Product {product_id}: "
                f"Qty {quantity}, "
                f"Market: Rs.{market_price:.2f}, "
                f"Our: Rs.{our_price:.2f}, "
                f"Discount: {product.get('discountType')} "
                f"Rs.{product.get('discount', 0)}, "
                f"Unit: {sale_unit_identifier}"
            )

            cursor.execute("""
                INSERT INTO sale_products (
                    invoice_id, product_id, variation_id, warehouse_id,
                    price, our_price, quantity, total,
                    discount_type, product_discount,
                    tax_type, product_tax,
                    discount, tax, unit_price, sale_unit
                ) VALUES (
                    %s, %s, %s, %s,
                    %s, %s, %s, %s,
                    %s, %s,
                    %s, %s,
                    %s, %s, %s, %s
                )
            """, (
                invoice_id, product_id, variation_id, product_warehouse_id,
                market_price, our_price, quantity, total,
                product.get('discountType') or 'fixed',
                float(product.get('discount', 0)),
                product.get('tax_type') or 'exclusive',
                float(product.get('product_tax', 0)),
                discount_value_product,
                float(product.get('tax', 0)),
                our_price,
                sale_unit_identifier
            ))

        # ============================================
        # COMMIT
        # ============================================
        conn.commit()
        cursor.close()
        conn.close()

        print(f"✅ Bill {invoice_code} saved as HOLD (stock not deducted)")
        print(f"   Customer:  {customer_name}")
        print(
            f"   Discount:  Rs.{discount_value} "
            f"({discount_type}"
            f"{f', {discount_percentage}%' if discount_type == 'percentage' else ''})"
        )
        print(f"   Products:  {len(products)}")
        print(f"   Grand Total: Rs.{grand_total:.2f}")

        return jsonify({
            "message":      "Bill held successfully",
            "invoice_id":   invoice_id,
            "invoice_code": invoice_code,
            "discount_type":        discount_type,          # ✅ echo back
            "discount_percentage":  discount_percentage,    # ✅ echo back
        }), 200

    except Exception as e:
        if conn:
            conn.rollback()
            conn.close()
        print("❌ Error holding bill:", e)
        import traceback
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500
    
    
# ============================================
# ✅ COMPLETE: GET HOLD BILLS LIST
# ============================================
@sale_bp.route('/get_hold_bills', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier')
def get_hold_bills():
    """
    Get all hold bills from database.
    ✅ Compatible with database schema
    """
    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        # Get hold bills
        cursor.execute("""
            SELECT 
                i.invoice_id, i.invoice_code, i.invoiceTotal,
                i.discount, i.sale_date, i.customer_id,
                i.warehouse_id, i.store_id,
                c.name AS customer_name,
                w.warehouse_name,
                COUNT(sp.sale_product_id) AS item_count
            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 sale_products sp ON i.invoice_id = sp.invoice_id
            WHERE i.status = 'hold'
            GROUP BY i.invoice_id
            ORDER BY i.sale_date DESC
        """)
        
        hold_bills = cursor.fetchall()
        
        result = []
        for bill in hold_bills:
            result.append({
                "invoice_id": bill['invoice_id'],
                "reference": bill['invoice_code'],
                "timestamp": bill['sale_date'].isoformat() if bill['sale_date'] else '',
                "customerId": bill['customer_id'],
                "customerName": bill['customer_name'] or 'Walk-in Customer',
                "warehouseId": bill['warehouse_id'],
                "warehouseName": bill['warehouse_name'] or 'Unknown',
                "storeId": bill['store_id'],
                "discountValue": float(bill['discount']) if bill['discount'] else 0,
                "grandTotal": float(bill['invoiceTotal']) if bill['invoiceTotal'] else 0,
                "itemCount": bill['item_count']
            })
        
        cursor.close()
        conn.close()
        
        return jsonify({"hold_bills": result}), 200
        
    except Exception as e:
        print("❌ Error getting hold bills:", e)
        import traceback
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500


@sale_bp.route('/get_hold_bill/<int:invoice_id>', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier')
def get_hold_bill(invoice_id):
    """
    Get detailed information for a single hold bill.
    ✅ Returns BOTH market_price and our_price
    ✅ FIXED: Doesn't reference non-existent sales_unit table
    ✅ FIXED: Uses market_price (pb.price) for stock availability check
    ✅ FIXED: Returns discount_type so frontend can restore discount modal state
    ✅ FIXED: Returns discount_percentage so frontend restores the raw % input
    """
    conn   = None
    cursor = None
    try:
        conn   = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        # ============================================
        # GET INVOICE HEADER
        # ============================================
        cursor.execute("""
            SELECT
                i.*,
                c.name          AS customer_name,
                w.warehouse_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
            WHERE i.invoice_id = %s
              AND i.status = 'hold'
        """, (invoice_id,))

        invoice = cursor.fetchone()

        if not invoice:
            return jsonify({"error": "Hold bill not found"}), 404

        # ============================================
        # GET PRODUCTS — BOTH PRICES, NO sales_unit JOIN
        # ============================================
        cursor.execute("""
            SELECT
                sp.sale_product_id,
                sp.product_id,
                sp.variation_id,
                sp.quantity,
                sp.warehouse_id,
                sp.price,
                sp.our_price,
                sp.total,
                sp.discount,
                sp.discount_type,
                sp.product_discount,
                sp.tax_type,
                sp.product_tax,
                sp.unit_price,
                sp.sale_unit,
                p.product_name,
                p.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
        """, (invoice_id,))

        products = cursor.fetchall()

        # ============================================
        # BUILD PRODUCTS LIST
        # ============================================
        products_list = []

        for idx, prod in enumerate(products, 1):
            product_warehouse = prod.get('warehouse_id') or invoice['warehouse_id']

            # Both prices
            market_price = float(prod.get('price',      0))
            our_price    = float(prod.get('our_price')  or prod.get('unit_price') or 0)

            # ✅ Use market_price (pb.price) for batch stock lookup —
            #    consistent with submit_invoice and resume_hold_bill
            available = get_available_stock_by_warehouse(
                cursor,
                prod['product_id'],
                prod['variation_id'],
                product_warehouse,
                price=market_price
            )

            print(
                f"   [{idx}] Product {prod['product_id']}: "
                f"Market Rs.{market_price:.2f}, "
                f"Our Rs.{our_price:.2f}, "
                f"Stock: {available}"
            )

            # ============================================
            # RESOLVE SALE UNIT NAME
            # sale_unit column stores a short name (e.g. "kg", "pcs")
            # or occasionally a numeric ID on older records
            # ============================================
            sale_unit_value = prod.get('sale_unit')
            sale_unit_name  = 'Unit'   # safe default

            if sale_unit_value:
                if isinstance(sale_unit_value, str) and not sale_unit_value.isdigit():
                    # Already a short name — use directly
                    sale_unit_name = sale_unit_value
                else:
                    # Numeric ID stored — attempt lookup, fall back gracefully
                    try:
                        cursor.execute("""
                            SELECT unit_short
                            FROM units
                            WHERE id = %s
                        """, (int(sale_unit_value),))
                        unit_row = cursor.fetchone()
                        sale_unit_name = (
                            unit_row['unit_short']
                            if unit_row and unit_row['unit_short']
                            else f"Unit {sale_unit_value}"
                        )
                    except Exception:
                        sale_unit_name = f"Unit {sale_unit_value}"

            products_list.append({
                "product_id":      prod['product_id'],
                "variation_id":    prod['variation_id'],
                "product_name":    prod['product_name'],
                "variation_name":  prod.get('variation_name'),
                "variation_type":  prod.get('variation_type'),
                "sku":             prod.get('variation_sku') or prod.get('sku'),
                "quantity":        float(prod['quantity']),
                "displayPrice":    our_price,
                "discountValue":   float(prod['discount'])          if prod['discount']          else 0,
                "total":           float(prod['total']),
                "market_price":    market_price,
                "our_price":       our_price,
                "product_price":   our_price,               # compatibility alias
                "product_quantity": available,
                "warehouse_id":    product_warehouse,
                "warehouse_name":  invoice['warehouse_name'] or 'Unknown',
                "discountType":    prod.get('discount_type')        or 'fixed',
                "discount":        float(prod['product_discount'])  if prod['product_discount']  else 0,
                "tax_type":        prod['tax_type'],
                "product_tax":     float(prod['product_tax'])       if prod['product_tax']       else 0,
                "sales_unit":      prod['sale_unit'],
                "sale_unit_name":  sale_unit_name
            })

        # ============================================
        # READ INVOICE-LEVEL DISCOUNT FIELDS SAFELY
        # (columns may not exist on older DB schemas)
        # ============================================
        invoice_discount_type  = invoice.get('discount_type')       or 'fixed'
        invoice_discount_value = float(invoice['discount'])          if invoice.get('discount')            else 0.0

        # ✅ NEW: raw percentage input — 0 when type is 'fixed'
        invoice_discount_percentage = float(invoice['discount_percentage']) \
            if invoice.get('discount_percentage') else 0.0

        # Guard: if type ended up as fixed but percentage is non-zero, zero it out
        if invoice_discount_type == 'fixed':
            invoice_discount_percentage = 0.0

        print(
            f"✅ Hold bill {invoice['invoice_code']} retrieved — "
            f"{len(products_list)} product(s), "
            f"Discount: Rs.{invoice_discount_value} "
            f"({invoice_discount_type}"
            f"{f', {invoice_discount_percentage}%' if invoice_discount_type == 'percentage' else ''})"
        )

        cursor.close()
        conn.close()

        return jsonify({
            # Invoice identifiers
            "invoice_id":    invoice['invoice_id'],
            "invoice_code":  invoice['invoice_code'],

            # Customer
            "customer_id":   invoice['customer_id'],
            "customer_name": invoice['customer_name'] or 'Walk-in Customer',

            # Warehouse / store
            "warehouse_id":   invoice['warehouse_id'],
            "warehouse_name": invoice['warehouse_name'] or 'Unknown',
            "store_id":       invoice['store_id'],

            # Totals
            "medicineGrandTotal": float(invoice['invoiceTotal']) + invoice_discount_value,
            "grandTotal":         float(invoice['invoiceTotal']),

            # ── Discount (all three fields) ──────────────────────────────
            "discountValue":      invoice_discount_value,       # computed Rs. amount
            "discountType":       invoice_discount_type,        # 'fixed' | 'percentage'
            "discountPercentage": invoice_discount_percentage,  # ✅ raw % (0 when fixed)

            # Products
            "products": products_list
        }), 200

    except Exception as e:
        print("❌ Error getting hold bill:", e)
        import traceback
        traceback.print_exc()
        if cursor:
            cursor.close()
        if conn:
            conn.close()
        return jsonify({"error": str(e)}), 500
# ============================================
# ✅ DELETE HOLD BILL
# ============================================
@sale_bp.route('/delete_hold_bill/<int:invoice_id>', methods=['DELETE'])
@jwt_required()
@role_required('admin', 'cashier')
def delete_hold_bill(invoice_id):
    """
    Delete a hold bill from database.
    ✅ No stock changes needed (stock was never deducted)
    """
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        cursor.execute("START TRANSACTION")

        # Verify it's a hold bill
        cursor.execute("""
            SELECT invoice_code, status 
            FROM invoice_sale 
            WHERE invoice_id = %s
        """, (invoice_id,))
        
        invoice = cursor.fetchone()
        
        if not invoice:
            return jsonify({"error": "Hold bill not found"}), 404
        
        if invoice['status'] != 'hold':
            return jsonify({"error": "Invoice is not a hold bill"}), 400

        # Delete sale_products
        cursor.execute("DELETE FROM sale_products WHERE invoice_id = %s", (invoice_id,))
        
        # Delete invoice
        cursor.execute("DELETE FROM invoice_sale WHERE invoice_id = %s", (invoice_id,))

        conn.commit()
        cursor.close()
        conn.close()

        print(f"✅ Hold bill {invoice['invoice_code']} deleted successfully")
        
        return jsonify({
            "message": f"Hold bill {invoice['invoice_code']} has been deleted successfully"
        }), 200

    except Exception as e:
        if conn:
            conn.rollback()
            conn.close()
        print("❌ Error deleting hold bill:", e)
        import traceback
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500
    
    
    
# ============================================
# ✅ FIXED: RESUME HOLD BILL - WITHOUT sales_unit TABLE REFERENCE
# ============================================
@sale_bp.route('/resume_hold_bill/<int:invoice_id>', methods=['POST'])
@jwt_required()
@role_required('admin', 'cashier')
def resume_hold_bill(invoice_id):
    """
    Resume a hold bill and convert it to a sale.
    ✅ Uses our_price (not market price) for stock deduction
    ✅ Uses INTEGER IDs for payment_method in sale_payments table
    ✅ FIXED: Doesn't reference non-existent sales_unit table
    """
    conn = None
    try:
        data = request.get_json()
        
        # Get payment details
        tender_amount = float(data.get('tenderAmount', 0))
        status = data.get('status', 'received')
        payment_methods = data.get('payment_methods', [])
        payment_status = data.get('payment_status', 'paid')
        
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        # ✅ Start transaction
        cursor.execute("SET TRANSACTION ISOLATION LEVEL READ COMMITTED")
        cursor.execute("START TRANSACTION")

        # Get hold bill
        cursor.execute("""
            SELECT * FROM invoice_sale 
            WHERE invoice_id = %s AND status = 'hold'
        """, (invoice_id,))
        
        invoice = cursor.fetchone()
        
        if not invoice:
            return jsonify({"error": "Hold bill not found"}), 404

        warehouse_id = invoice['warehouse_id']
        store_id = invoice['store_id']
        invoice_total = float(invoice['invoiceTotal'])
        
        # Calculate remaining amount
        remaining_amount = max(0, invoice_total - tender_amount)
        
        # ✅ FIXED: Determine payment method and status
        if len(payment_methods) == 0 or payment_status == 'unpaid':
            primary_payment_method = "unpaid"
            payment_status = "unpaid"
        elif len(payment_methods) == 1:
            method_id, method_name = get_payment_method_id_and_name(cursor, payment_methods[0]['type'])
            primary_payment_method = method_name.lower()
        else:
            primary_payment_method = "multiple"

        # ✅ Get products WITH OUR_PRICE (the actual selling price)
        # ✅ FIXED: Don't join with sales_unit table
        cursor.execute("""
            SELECT 
                sale_product_id, product_id, variation_id, quantity, warehouse_id, 
                price as market_price, 
                COALESCE(our_price, unit_price, price) as our_price
            FROM sale_products 
            WHERE invoice_id = %s
        """, (invoice_id,))
        products = cursor.fetchall()

        # ✅ NOW deduct stock using OUR_PRICE (the actual selling price)
        print(f"\n📦 === RESUMING HOLD BILL {invoice['invoice_code']} ===")
        print(f"   Processing {len(products)} products...")
        
        for idx, product in enumerate(products, 1):
            product_id = product['product_id']
            variation_id = product['variation_id']
            quantity = float(product['quantity'])
            
            # ✅ CRITICAL: Use our_price (the actual selling price), not market_price
            market_price = float(product.get('market_price', 0))
            our_price = float(product.get('our_price', 0))
            
            product_warehouse = product.get('warehouse_id') or warehouse_id
            sale_product_id = product['sale_product_id']
            
            print(f"\n   [{idx}/{len(products)}] Product {product_id}:")
            print(f"      Market Price: Rs. {market_price:.2f}")
            print(f"      Our Price: Rs. {our_price:.2f}")
            print(f"      Quantity: {quantity}")
            print(f"      Warehouse: {product_warehouse}")
            
            # Validate quantity
            if quantity <= 0:
                raise Exception(f"Invalid quantity {quantity} for product {product_id}")
            
            # ✅ Check stock at OUR PRICE (the actual selling price)
            available = get_available_stock_by_warehouse(
                cursor, product_id, variation_id, product_warehouse, price=our_price
            )
            
            print(f"      Available Stock @ Our Price: {available:.2f}")
            
            if available < quantity:
                raise Exception(
                    f"Insufficient stock at our price Rs. {our_price:.2f} for product {product_id} in warehouse {product_warehouse}. "
                    f"Available: {available:.2f}, Required: {quantity:.2f}"
                )
            
            # ✅ Deduct stock using OUR PRICE
            batches_used = deduct_stock_from_batches_fifo_warehouse(
                cursor, product_id, variation_id, quantity, 
                product_warehouse, store_id, sale_product_id,
                price=our_price  # ✅ Use our_price for FIFO selection
            )
            
            print(f"      ✅ Stock deducted: {len(batches_used)} batch(es) used")

        # ✅ FIXED: Insert payment methods with INTEGER IDs
        if payment_methods and payment_status != 'unpaid':
            print(f"\n💳 Processing {len(payment_methods)} payment method(s)...")
            for idx, payment in enumerate(payment_methods, 1):
                try:
                    payment_type_value = payment.get('type')
                    payment_amount = float(payment.get('amount', 0))
                    
                    if payment_amount > 0:
                        # Get valid payment method ID and name
                        method_id, method_name = get_payment_method_id_and_name(cursor, payment_type_value)
                        
                        print(f"   [{idx}] {method_name} (ID: {method_id}): Rs. {payment_amount:.2f}")
                        
                        # ✅ CRITICAL: Insert with INTEGER ID
                        cursor.execute("""
                            INSERT INTO sale_payments (sale_id, payment_method, amount, payment_date)
                            VALUES (%s, %s, %s, NOW())
                        """, (invoice_id, method_id, payment_amount))
                        
                        print(f"      ✅ Payment record created")
                        
                except Exception as pe:
                    print(f"   ❌ Error processing payment {idx}: {pe}")
                    raise
        else:
            print(f"\n💳 No payments (Status: {payment_status})")

        # Update invoice status
        cursor.execute("""
            UPDATE invoice_sale
            SET status = %s,
                payment_method = %s,
                payment_status = %s,
                tenderAmount = %s,
                remainingAmount = %s,
                sale_date = NOW()
            WHERE invoice_id = %s
        """, (status, primary_payment_method, payment_status, tender_amount, remaining_amount, invoice_id))

        # ✅ Commit transaction
        conn.commit()
        cursor.close()
        conn.close()

        print(f"\n✅ === HOLD BILL RESUMED SUCCESSFULLY ===")
        print(f"   Invoice: {invoice['invoice_code']}")
        print(f"   Products: {len(products)}")
        print(f"   Status: {status}")
        print(f"   Payment Status: {payment_status}")
        print(f"   Tender: Rs. {tender_amount:.2f}")
        print(f"   Remaining: Rs. {remaining_amount:.2f}")
        
        return jsonify({
            "message": "Hold bill resumed successfully",
            "invoice_id": invoice_id,
            "invoice_code": invoice['invoice_code'],
            "status": status,
            "payment_status": payment_status
        }), 200

    except Exception as e:
        if conn:
            conn.rollback()
            conn.close()
        print("\n❌ === ERROR RESUMING HOLD BILL ===")
        print(f"   Error: {str(e)}")
        import traceback
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500
    
    
    
    
@sale_bp.route('/create_quotation', methods=['POST'])
@jwt_required()
@role_required('admin', 'cashier')
def create_quotation():
    """
    Create a quotation from current POS bill items.
    ✅ Stock is NOT deducted (quotation only)
    ✅ Uses existing quotations + quotation_items tables
    ✅ Stores both market_price and our_price
    ✅ Returns quotation_code for display
    """
    conn = None
    try:
        data = request.get_json()
        print("📥 Create Quotation Data:")
        print(json.dumps(data, indent=4, default=str))
 
        # ============================================
        # EXTRACT DATA
        # ============================================
        customer_id   = data.get('customer_id')
        warehouse_id  = data.get('warehouse_id')
        store_id      = data.get('store_id')
        products      = data.get('products', [])
        grand_total   = float(data.get('grandTotal', 0))
        discount_value = float(data.get('discountValue', 0))
        note          = data.get('note', '')
 
        # ============================================
        # VALIDATION
        # ============================================
        if not warehouse_id:
            return jsonify({"error": "Warehouse ID is required"}), 400
 
        if not store_id:
            return jsonify({"error": "Store ID is required"}), 400
 
        if not products or len(products) == 0:
            return jsonify({"error": "No products to quote"}), 400
 
        # ============================================
        # DATABASE SETUP
        # ============================================
        current_user = get_jwt_identity()
 
        conn   = get_db_connection()
        cursor = conn.cursor(dictionary=True)
 
        cursor.execute("START TRANSACTION")
 
        # ============================================
        # GENERATE QUOTATION CODE
        # ============================================
        today_str = datetime.now().strftime("%Y%m%d")
        cursor.execute(
            "SELECT COUNT(*) as count FROM quotations WHERE DATE(created_at) = CURDATE()"
        )
        count          = cursor.fetchone()["count"] + 1
        quotation_code = f"QUO-{today_str}-{count:04d}"
        quotation_date = datetime.now().date()
 
        # ============================================
        # CALCULATE SUBTOTAL
        # ============================================
        subtotal = sum(float(p.get('total', 0)) for p in products)
        tax      = 0.0  # Can be extended later
 
        # ============================================
        # INSERT QUOTATION HEADER
        # ============================================
        cursor.execute("""
            INSERT INTO quotations (
                quotation_code,
                quotation_date,
                note,
                subtotal,
                tax,
                discount,
                grand_total,
                status,
                created_by,
                warehouse_id,
                store_id,
                customer_id
            ) VALUES (
                %s, %s, %s, %s, %s, %s, %s,
                'pending',
                %s, %s, %s, %s
            )
        """, (
            quotation_code,
            quotation_date,
            note,
            subtotal,
            tax,
            discount_value,
            grand_total,
            current_user,
            warehouse_id,
            store_id,
            customer_id
        ))
 
        quotation_id = cursor.lastrowid
        print(f"   ✅ Quotation header created: {quotation_code} (ID: {quotation_id})")
 
        # ============================================
        # INSERT QUOTATION ITEMS
        # ============================================
        print(f"   📦 Processing {len(products)} product(s)...")
 
        for idx, product in enumerate(products, 1):
            product_id   = int(product['product_id'])
            variation_id = product.get('variation_id')
            if variation_id:
                variation_id = int(variation_id)
 
            quantity     = float(product.get('quantity', 1))
            our_price    = float(product.get('our_price') or product.get('product_price', 0))
            market_price = float(product.get('market_price') or product.get('price', 0))
            total        = float(product.get('total', 0))
 
            product_discount      = float(product.get('discount', 0))
            discount_type         = product.get('discountType', 'fixed')
            product_tax           = float(product.get('product_tax', 0))
            tax_type              = product.get('tax_type', 'exclusive')
            product_warehouse_id  = product.get('warehouse_id') or warehouse_id
 
            print(
                f"   [{idx}] Product {product_id}: "
                f"Qty {quantity}, "
                f"Market: Rs.{market_price:.2f}, "
                f"Our: Rs.{our_price:.2f}"
            )
 
            cursor.execute("""
                INSERT INTO quotation_items (
                    quotation_id,
                    product_id,
                    variation_id,
                    warehouse_id,
                    quantity,
                    price,
                    net_unit_price,
                    discount_type,
                    product_discount,
                    tax_type,
                    product_tax,
                    discount,
                    tax,
                    subtotal
                ) VALUES (
                    %s, %s, %s, %s,
                    %s, %s, %s,
                    %s, %s,
                    %s, %s,
                    %s, %s, %s
                )
            """, (
                quotation_id,
                product_id,
                variation_id,
                product_warehouse_id,
                quantity,
                our_price,           # price = our selling price
                market_price,        # net_unit_price = market price for reference
                discount_type,
                product_discount,
                tax_type,
                product_tax,
                product_discount * quantity if discount_type == 'fixed' else (our_price * product_discount / 100) * quantity,
                (our_price * product_tax / 100) * quantity if tax_type == 'exclusive' else 0,
                total
            ))
 
        # ============================================
        # COMMIT
        # ============================================
        conn.commit()
        cursor.close()
        conn.close()
 
        print(f"✅ Quotation {quotation_code} created successfully")
        print(f"   Products: {len(products)}")
        print(f"   Grand Total: Rs.{grand_total:.2f}")
 
        return jsonify({
            "message":        "Quotation created successfully",
            "quotation_id":   quotation_id,
            "quotation_code": quotation_code,
            "grand_total":    grand_total,
            "status":         "pending"
        }), 200
 
    except Exception as e:
        if conn:
            conn.rollback()
            conn.close()
        print("❌ Error creating quotation:", e)
        import traceback
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500
 
 
@sale_bp.route('/get_quotations', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier')
def get_quotations():
    """
    Get all quotations with product details.
    ✅ Returns quotation list for the quotation list page
    """
    try:
        conn   = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        cursor.execute("""
            SELECT
                q.quotation_id,
                q.quotation_code,
                q.quotation_date,
                q.grand_total,
                q.discount,
                q.status,
                q.created_at,
                q.warehouse_id,
                q.store_id,
                q.customer_id,
                q.converted_to_sale_id,
                c.name          AS customer_name,
                w.warehouse_name,
                u.name          AS created_by_name,
                COUNT(qi.item_id) AS item_count
            FROM quotations q
            LEFT JOIN customers c        ON q.customer_id  = c.id
            LEFT JOIN warehouses w       ON q.warehouse_id = w.id
            LEFT JOIN users u            ON q.created_by   = u.id
            LEFT JOIN quotation_items qi ON q.quotation_id = qi.quotation_id
            GROUP BY q.quotation_id
            ORDER BY q.created_at DESC
        """)

        quotations = cursor.fetchall()

        result = []
        for q in quotations:
            result.append({
                "quotation_id":         q['quotation_id'],
                "quotation_code":       q['quotation_code'],
                "quotation_date":       q['quotation_date'].strftime('%Y-%m-%d') if q['quotation_date'] else '',
                "grand_total":          float(q['grand_total'])        if q['grand_total']        else 0,
                "discount":             float(q['discount'])           if q['discount']           else 0,
                "status":               q['status'],
                "created_at":           q['created_at'].isoformat()   if q['created_at']         else '',
                "customer_id":          q['customer_id'],
                "customer_name":        q['customer_name']             or 'Walk-in Customer',
                "warehouse_id":         q['warehouse_id'],
                "warehouse_name":       q['warehouse_name']            or 'Unknown',
                "store_id":             q['store_id'],
                "created_by_name":      q['created_by_name']           or 'System',
                "item_count":           q['item_count'],
                "converted_to_sale_id": q['converted_to_sale_id'],   # ✅ "Converted" badge
            })

        cursor.close()
        conn.close()

        return jsonify({"quotations": result}), 200

    except Exception as e:
        print("❌ Error getting quotations:", e)
        import traceback
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500
    
    
@sale_bp.route('/get_quotation/<int:quotation_id>', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier')
def get_quotation(quotation_id):
    """
    Get single quotation detail.
    ✅ Returns all product details needed to rebuild the POS bill
    ✅ Returns store/customer/warehouse info for view page
    ✅ Returns batch allocations from quotation_product_batches
    ✅ Inline stock check from warehouse_stock table
    """
    conn   = None
    cursor = None
    try:
        conn   = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        # ============================================
        # GET QUOTATION HEADER
        # ============================================
        cursor.execute("""
            SELECT
                q.quotation_id,
                q.quotation_code,
                q.quotation_date,
                q.note,
                q.subtotal,
                q.tax,
                q.discount,
                q.grand_total,
                q.status,
                q.created_at,
                q.updated_at,
                q.warehouse_id,
                q.store_id,
                q.customer_id,
                q.converted_to_sale_id,

                -- Customer info (customers table: id, name, address, contact, email)
                c.name          AS customer_name,
                c.email         AS customer_email,
                c.contact       AS customer_contact,
                c.address       AS customer_address,

                -- Warehouse info
                w.warehouse_name,

                -- Store info (stores table: id, store_name, email, contact, address)
                s.store_name,
                s.email         AS store_email,
                s.contact       AS store_phone,
                s.address       AS store_address,

                -- Created by
                u.name          AS created_by_name

            FROM quotations q
            LEFT JOIN customers  c ON q.customer_id  = c.id
            LEFT JOIN warehouses w ON q.warehouse_id = w.id
            LEFT JOIN stores     s ON q.store_id     = s.id
            LEFT JOIN users      u ON q.created_by   = u.id
            WHERE q.quotation_id = %s
        """, (quotation_id,))

        quotation = cursor.fetchone()

        if not quotation:
            return jsonify({"error": "Quotation not found"}), 404

        # ============================================
        # GET QUOTATION ITEMS
        # ============================================
        cursor.execute("""
            SELECT
                qi.item_id,
                qi.quotation_id,
                qi.product_id,
                qi.variation_id,
                qi.batch_id,
                qi.warehouse_id,
                qi.quantity,
                qi.price,
                qi.net_unit_price,
                qi.discount_type,
                qi.product_discount,
                qi.tax_type,
                qi.product_tax,
                qi.discount,
                qi.tax,
                qi.subtotal,

                -- Product info
                p.product_name,
                p.sku,
                p.tax_type          AS product_default_tax_type,
                p.product_tax       AS product_default_tax,

                -- Unit info
                u_sale.unit_name    AS sale_unit,
                u_sale.unit_short   AS sale_unit_short,

                -- Variation info
                pv.variation_name,
                pv.variation_type,
                pv.variation_sku,

                -- Item warehouse info
                w2.warehouse_name   AS item_warehouse_name

            FROM quotation_items qi
            JOIN  products              p      ON qi.product_id   = p.id
            LEFT JOIN units             u_sale ON p.sale_unit_id  = u_sale.id
            LEFT JOIN product_variations pv    ON qi.variation_id = pv.id
            LEFT JOIN warehouses        w2     ON qi.warehouse_id  = w2.id
            WHERE qi.quotation_id = %s
            ORDER BY qi.item_id ASC
        """, (quotation_id,))

        items = cursor.fetchall()

        # ============================================
        # BUILD PRODUCTS LIST WITH STOCK + BATCH INFO
        # ============================================
        products_list = []

        for item in items:
            product_warehouse = item.get('warehouse_id') or quotation['warehouse_id']

            # ── Inline stock check from warehouse_stock ──
            try:
                if item.get('variation_id'):
                    cursor.execute("""
                        SELECT COALESCE(SUM(quantity), 0) AS available
                        FROM warehouse_stock
                        WHERE product_id   = %s
                          AND variation_id = %s
                          AND warehouse_id = %s
                    """, (item['product_id'], item['variation_id'], product_warehouse))
                else:
                    cursor.execute("""
                        SELECT COALESCE(SUM(quantity), 0) AS available
                        FROM warehouse_stock
                        WHERE product_id   = %s
                          AND warehouse_id = %s
                          AND (variation_id IS NULL OR variation_id = 0)
                    """, (item['product_id'], product_warehouse))

                stock_row = cursor.fetchone()
                available = float(stock_row['available']) if stock_row else 0.0
            except Exception:
                available = 0.0

            # ── Get batch allocations from quotation_product_batches ──
            batch_allocations = []
            try:
                cursor.execute("""
                    SELECT
                        qpb.quotation_product_batch_id,
                        qpb.batch_id,
                        qpb.grn_id,
                        qpb.quantity,
                        qpb.cost,
                        qpb.price,
                        qpb.expiration_date,
                        qpb.created_at,
                        pb.batch_number,
                        g.grn_code
                    FROM quotation_product_batches qpb
                    LEFT JOIN product_batches pb ON qpb.batch_id = pb.batch_id
                    LEFT JOIN grn            g  ON qpb.grn_id   = g.grn_id
                    WHERE qpb.quotation_item_id = %s
                    ORDER BY qpb.quotation_product_batch_id ASC
                """, (item['item_id'],))

                batches = cursor.fetchall()
                for b in batches:
                    batch_allocations.append({
                        "quotation_product_batch_id": b['quotation_product_batch_id'],
                        "batch_id":       b['batch_id'],
                        "grn_id":         b['grn_id'],
                        "grn_code":       b.get('grn_code') or '',
                        "batch_number":   b.get('batch_number') or '',
                        "quantity":       float(b['quantity']),
                        "cost":           float(b['cost'] or 0),
                        "price":          float(b['price'] or 0),
                        "expiration_date": b['expiration_date'].strftime('%Y-%m-%d') if b.get('expiration_date') else None,
                    })
            except Exception as batch_err:
                print(f"   ⚠️ Batch fetch error for item {item['item_id']}: {batch_err}")
                batch_allocations = []

            # ── Price fields ──
            # price       = our selling price (what we charge customer)
            # net_unit_price = market/reference price
            our_price    = float(item.get('price', 0))
            market_price = float(item.get('net_unit_price', 0)) or our_price

            products_list.append({
                # IDs
                "item_id":          item['item_id'],
                "product_id":       item['product_id'],
                "variation_id":     item['variation_id'],
                "batch_id":         item['batch_id'],
                "warehouse_id":     product_warehouse,

                # Names
                "product_name":     item['product_name'],
                "variation_name":   item.get('variation_name'),
                "variation_type":   item.get('variation_type'),
                "sku":              item.get('variation_sku') or item.get('sku') or '',
                "warehouse_name":   item.get('item_warehouse_name') or quotation.get('warehouse_name') or '',
                "sale_unit":        item.get('sale_unit') or '',
                "sale_unit_short":  item.get('sale_unit_short') or '',

                # Prices
                "our_price":        our_price,
                "market_price":     market_price,
                "price":            our_price,          # alias for POS
                "displayPrice":     our_price,          # alias for POS
                "net_unit_price":   market_price,       # alias for view page

                # Quantity
                "quantity":         float(item['quantity']),
                "product_quantity": available,          # current stock

                # Discount
                "discountType":     item.get('discount_type') or 'fixed',
                "discount":         float(item.get('product_discount') or 0),
                "discountValue":    float(item.get('discount') or 0),

                # Tax
                "tax_type":         item.get('tax_type') or 'exclusive',
                "product_tax":      float(item.get('product_tax') or 0),
                "tax":              float(item.get('tax') or 0),

                # Totals
                "subtotal":         float(item['subtotal']),
                "total":            float(item['subtotal']),  # alias

                # Batch allocations
                "batch_allocations": batch_allocations,
            })

        cursor.close()
        conn.close()

        # ============================================
        # CALCULATE SUBTOTAL FROM ITEMS (fallback)
        # ============================================
        items_subtotal = sum(p['subtotal'] for p in products_list)

        # ============================================
        # RETURN RESPONSE
        # ============================================
        return jsonify({
            "success": True,

            # Quotation header
            "quotation_id":    quotation['quotation_id'],
            "quotation_code":  quotation['quotation_code'],
            "quotation_date":  quotation['quotation_date'].strftime('%Y-%m-%d') if quotation['quotation_date'] else None,
            "created_at":      quotation['created_at'].isoformat() if quotation['created_at'] else None,
            "updated_at":      quotation['updated_at'].isoformat() if quotation.get('updated_at') else None,
            "expiry_date":     None,  # not in schema — extend later if needed
            "status":          quotation['status'],
            "note":            quotation.get('note') or '',
            "converted_to_sale_id": quotation.get('converted_to_sale_id'),

            # Customer
            "customer_id":      quotation['customer_id'],
            "customer_name":    quotation.get('customer_name')    or 'Walk-in Customer',
            "customer_email":   quotation.get('customer_email')   or '',
            "customer_contact": quotation.get('customer_contact') or '',
            "customer_address": quotation.get('customer_address') or '',

            # Warehouse
            "warehouse_id":    quotation['warehouse_id'],
            "warehouse_name":  quotation.get('warehouse_name') or '',

            # Store
            "store_id":        quotation['store_id'],
            "store_name":      quotation.get('store_name')   or '',
            "store_email":     quotation.get('store_email')  or '',
            "store_phone":     quotation.get('store_phone')  or '',
            "store_address":   quotation.get('store_address') or '',

            # Created by
            "created_by_name": quotation.get('created_by_name') or '',

            # Financials
            "subtotal":      float(quotation.get('subtotal')    or items_subtotal),
            "tax":           float(quotation.get('tax')         or 0),
            "discount":      float(quotation.get('discount')    or 0),
            "discountValue": float(quotation.get('discount')    or 0),
            "grand_total":   float(quotation['grand_total']),

            # Items — both keys for frontend compatibility
            "products": products_list,
            "items":    products_list,

        }), 200

    except Exception as e:
        print("❌ Error getting quotation:", e)
        import traceback
        traceback.print_exc()
        if cursor:
            try: cursor.close()
            except: pass
        if conn:
            try: conn.close()
            except: pass
        return jsonify({"error": str(e)}), 500
 
@sale_bp.route('/delete_quotation/<int:quotation_id>', methods=['DELETE'])
@jwt_required()
@role_required('admin', 'cashier')
def delete_quotation(quotation_id):
    """Delete a quotation."""
    conn = None
    try:
        conn   = get_db_connection()
        cursor = conn.cursor(dictionary=True)
 
        cursor.execute("START TRANSACTION")
 
        cursor.execute(
            "SELECT quotation_code FROM quotations WHERE quotation_id = %s",
            (quotation_id,)
        )
        quotation = cursor.fetchone()
 
        if not quotation:
            return jsonify({"error": "Quotation not found"}), 404
 
        cursor.execute(
            "DELETE FROM quotation_items WHERE quotation_id = %s", (quotation_id,)
        )
        cursor.execute(
            "DELETE FROM quotations WHERE quotation_id = %s", (quotation_id,)
        )
 
        conn.commit()
        cursor.close()
        conn.close()
 
        return jsonify({
            "message": f"Quotation {quotation['quotation_code']} deleted successfully"
        }), 200
 
    except Exception as e:
        if conn: conn.rollback(); conn.close()
        print("❌ Error deleting quotation:", e)
        return jsonify({"error": str(e)}), 500
     

@sale_bp.route('/get_customer_outstanding/<int:customer_id>', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier')
def get_customer_outstanding(customer_id):
    """
    Returns all unpaid/partial/suspended invoices for a customer,
    plus the total amount they still owe (credit bill summary).
    """
    conn = None
    cursor = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        cursor.execute("""
            SELECT
                invoice_id, invoice_code, sale_date, invoiceTotal,
                tenderAmount, remainingAmount, payment_status, status
            FROM invoice_sale
            WHERE customer_id = %s
              AND status != 'hold'
              AND (
                    status = 'suspended'
                    OR payment_status IN ('partial', 'unpaid')
                  )
              AND remainingAmount > 0
            ORDER BY sale_date ASC
        """, (customer_id,))

        rows = cursor.fetchall()

        invoices = []
        total_due = 0.0

        for r in rows:
            due = float(r['remainingAmount'] or 0)
            total_due += due
            invoices.append({
                "invoice_id":      r['invoice_id'],
                "invoice_code":    r['invoice_code'],
                "sale_date":       r['sale_date'].strftime('%Y-%m-%d %H:%M:%S') if r['sale_date'] else None,
                "invoiceTotal":    float(r['invoiceTotal'] or 0),
                "paid":            float(r['tenderAmount'] or 0),
                "due":             due,
                "payment_status":  r['payment_status'],
                "status":          r['status']
            })

        return jsonify({
            "success":       True,
            "customer_id":   customer_id,
            "invoices":      invoices,
            "invoice_count": len(invoices),
            "total_due":     round(total_due, 2)
        }), 200

    except Exception as e:
        print("❌ Error in get_customer_outstanding:", e)
        import traceback
        traceback.print_exc()
        return jsonify({"success": False, "error": str(e)}), 500

    finally:
        if cursor: cursor.close()
        if conn:   conn.close()