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
import mysql.connector
import traceback
from datetime import datetime
import os
from werkzeug.utils import secure_filename
from flask import send_file
from datetime import datetime, date

grn_bp = Blueprint('grn', __name__)


def safe_float(value, default=0.0):
    """Safely convert to float"""
    try:
        return float(value) if value not in (None, '', 'null') else default
    except (ValueError, TypeError):
        return default


def safe_int(value, default=0):
    """Safely convert to int"""
    try:
        return int(value) if value not in (None, '', 'null') else default
    except (ValueError, TypeError):
        return default


def generate_grn_code(cursor):
    """Generate unique GRN code in format: GRN-YYYY-NNNNN"""
    year = datetime.now().year
    
    cursor.execute("""
        SELECT grn_code FROM grn 
        WHERE grn_code LIKE %s 
        ORDER BY grn_id DESC LIMIT 1
    """, (f"GRN-{year}-%",))
    
    last_grn = cursor.fetchone()
    
    if last_grn:
        last_number = int(last_grn['grn_code'].split('-')[-1])
        new_number = last_number + 1
    else:
        new_number = 1
    
    return f"GRN-{year}-{new_number:05d}"


@grn_bp.route('/create_grn', methods=['POST'])
@jwt_required()
@role_required('admin', 'manager')
def create_grn():
    """
    ✅ CREATE GRN - COMPLETE VERSION WITH OUR_PRICE SUPPORT
    
    This endpoint creates GRN records with:
    - ✅ Warehouse and supplier from REQUEST (changeable from PO)
    - ✅ Item-level pricing (cost, price, our_price, discount, tax) 
    - ✅ Order-level tax percentage
    - ✅ Order-level discount (fixed amount)
    - ✅ Payment information (status, type, amounts)
    - ✅ Expiration dates for items
    - ✅ OUR_PRICE field for inventory valuation ✓ NEW
    - ✅ Complete financial tracking
    
    Stock is NOT updated here - that happens during approval.
    
    SAVES TO:
    - grn (header with order tax, discount, payment)
    - grn_items (with ALL pricing details + our_price + expiration_date)
    - Updates purchase_orders.grn_status
    - Creates activity log
    
    DOES NOT TOUCH:
    - product_batches (created during approval)
    - warehouse_stock (updated during approval)
    
    DATA FLOW:
    Frontend → our_price extracted → grn_items table ✅
    → Admin approves → product_batches table ✅
    """
    
    try:
        data = request.get_json(force=True)
        if not data:
            return jsonify({'error': 'No data provided'}), 400
    except Exception as e:
        return jsonify({'error': 'Invalid JSON data', 'message': str(e)}), 400
    
    print("=" * 80)
    print("📋 GRN CREATION - COMPLETE VERSION WITH OUR_PRICE")
    print("=" * 80)
    
    # Extract basic data
    purchase_order_id = safe_int(data.get('purchase_order_id'))
    grn_date_str = data.get('grn_date')
    received_by_name = data.get('received_by', '').strip()
    invoice_number = data.get('invoice_number', '').strip() or None
    invoice_date_str = data.get('invoice_date') or None
    vehicle_number = data.get('vehicle_number', '').strip() or None
    driver_name = data.get('driver_name', '').strip() or None
    driver_contact = data.get('driver_contact', '').strip() or None
    note = data.get('note', '').strip()
    items = data.get('items', [])
    
    # ✅ Get warehouse and supplier from REQUEST (not from PO)
    warehouse_id = safe_int(data.get('warehouse_id'))
    supplier_id = safe_int(data.get('supplier_id'))
    
    # ✅ Order-level tax, discount, payment
    order_tax_percentage = safe_float(data.get('order_tax', 0))  # Percentage
    order_discount_amount = safe_float(data.get('discount', 0))  # Fixed amount
    payment_status = data.get('payment_status', 'Unpaid')  # Paid/Unpaid/Partial
    
    # ✅ Handle None payment_type for Unpaid status
    payment_type_raw = data.get('payment_type')
    if payment_type_raw is not None and isinstance(payment_type_raw, str):
        payment_type = payment_type_raw.strip() or None
    else:
        payment_type = None
    
    paid_amount = safe_float(data.get('paid_amount', 0))
    
    print(f"\n📊 Order-Level Financials:")
    print(f"   Order Tax: {order_tax_percentage}%")
    print(f"   Order Discount: LKR {order_discount_amount}")
    print(f"   Payment Status: {payment_status}")
    print(f"   Payment Type: {payment_type or 'N/A (Unpaid)'}")
    print(f"   Paid Amount: LKR {paid_amount}")
    
    # Validation
    if not purchase_order_id:
        return jsonify({'error': 'Purchase order ID is required'}), 400
    
    if not grn_date_str:
        return jsonify({'error': 'GRN date is required'}), 400
    
    if not received_by_name:
        return jsonify({'error': 'Received by is required'}), 400
    
    # ✅ Validate warehouse and supplier
    if not warehouse_id:
        return jsonify({'error': 'Warehouse is required'}), 400
    
    if not supplier_id:
        return jsonify({'error': 'Supplier is required'}), 400
    
    if not items or len(items) == 0:
        return jsonify({'error': 'At least one item is required'}), 400
    
    # Validate payment status
    if payment_status not in ['Paid', 'Unpaid', 'Partial']:
        return jsonify({'error': 'Invalid payment status. Must be Paid, Unpaid, or Partial'}), 400
    
    # ✅ Only require payment_type for Paid/Partial
    if payment_status in ['Paid', 'Partial'] and not payment_type:
        return jsonify({'error': 'Payment type is required for paid/partial payments'}), 400
    
    if payment_status == 'Partial' and paid_amount <= 0:
        return jsonify({'error': 'Paid amount must be greater than 0 for partial payments'}), 400
    
    # Parse dates
    try:
        grn_date = datetime.strptime(grn_date_str, '%Y-%m-%d').date()
    except ValueError:
        return jsonify({'error': 'Invalid GRN date format. Use YYYY-MM-DD'}), 400
    
    invoice_date = None
    if invoice_date_str:
        try:
            invoice_date = datetime.strptime(invoice_date_str, '%Y-%m-%d').date()
        except ValueError:
            return jsonify({'error': 'Invalid invoice date format. Use YYYY-MM-DD'}), 400
    
    conn = get_db_connection()
    if not conn:
        return jsonify({'error': 'Database connection failed'}), 500
    
    cursor = conn.cursor(dictionary=True)
    
    try:
        conn.start_transaction()
        print("\n🔄 Transaction started")
        
        # Step 1: Validate purchase order
        print(f"\n📝 Step 1: Validating purchase order {purchase_order_id}...")
        cursor.execute("""
            SELECT 
                order_id, supplier_id, warehouse_id, store_id, 
                status, grn_status, grand_total
            FROM purchase_orders
            WHERE order_id = %s
        """, (purchase_order_id,))
        
        po = cursor.fetchone()
        if not po:
            raise ValueError(f"Purchase order {purchase_order_id} not found")
        
        if po['grn_status'] == 'fully_received':
            raise ValueError(f"Purchase order {purchase_order_id} is already fully received")
        
        # ✅ Get store_id from PO, but use warehouse_id and supplier_id from request
        store_id = po['store_id']
        
        print(f"  ✅ PO validated")
        print(f"  📦 Using Warehouse ID from REQUEST: {warehouse_id} (PO had: {po['warehouse_id']})")
        print(f"  🚚 Using Supplier ID from REQUEST: {supplier_id} (PO had: {po['supplier_id']})")
        print(f"  🏪 Store ID from PO: {store_id}")
        
        # ✅ Validate that warehouse exists
        cursor.execute("SELECT id FROM warehouses WHERE id = %s AND is_active = 1", (warehouse_id,))
        if not cursor.fetchone():
            raise ValueError(f"Warehouse {warehouse_id} not found or inactive")
        
        # ✅ Validate that supplier exists
        cursor.execute("SELECT id FROM suppliers WHERE id = %s", (supplier_id,))
        if not cursor.fetchone():
            raise ValueError(f"Supplier {supplier_id} not found")
        
        # Step 2: Get user ID
        print(f"\n📝 Step 2: Getting user ID for '{received_by_name}'...")
        
        current_user = get_jwt_identity()
        received_by_id = None
        
        if current_user:
            cursor.execute("SELECT id FROM users WHERE id = %s", (current_user,))
            user = cursor.fetchone()
            if user:
                received_by_id = user['id']
        
        if not received_by_id:
            cursor.execute("SELECT id FROM users WHERE role = 'admin' LIMIT 1")
            admin = cursor.fetchone()
            if admin:
                received_by_id = admin['id']
        
        if not received_by_id:
            raise ValueError("No valid user found to assign as receiver")
        
        print(f"  ✅ Receiver user ID: {received_by_id}")
        
        # Step 3: Generate GRN code
        print(f"\n📝 Step 3: Generating GRN code...")
        grn_code = generate_grn_code(cursor)
        print(f"  ✅ GRN Code: {grn_code}")
        
        # ✅ STEP 4: PROCESS ITEMS WITH OUR_PRICE
        print(f"\n📝 Step 4: Processing {len(items)} items...")
        
        total_items = len(items)
        item_subtotal = 0.0
        total_item_tax = 0.0
        total_item_discount = 0.0
        
        grn_items_data = []
        items_with_our_price = 0  # Track count for summary
        
        for idx, item in enumerate(items, 1):
            product_id = safe_int(item.get('product_id'))
            variation_id = safe_int(item.get('variation_id')) if item.get('variation_id') else None
            ordered_qty = safe_float(item.get('ordered_quantity', 0))
            received_qty = safe_float(item.get('received_quantity', 0))
            rejected_qty = safe_float(item.get('rejected_quantity', 0))
            
            # ✅ ALL pricing details from frontend INCLUDING OUR_PRICE
            unit_price = safe_float(item.get('unit_price', 0))           # Cost
            batch_price = safe_float(item.get('batch_price', 0))         # Intended selling price
            our_price = safe_float(item.get('our_price', 0))             # ✅ OUR ACTUAL SELLING PRICE
            discount = safe_float(item.get('discount', 0))               # Discount value
            discount_type = item.get('discount_type', 'fixed')           # fixed/percentage
            product_tax = safe_float(item.get('product_tax', 0))         # Tax percentage
            tax_type = item.get('tax_type', 'exclusive')                 # exclusive/inclusive
            net_unit_cost = safe_float(item.get('net_unit_cost', 0))    # Calculated net cost
            
            quality_check = item.get('quality_check', 'pending')
            item_note = item.get('note', '').strip()
            unit = item.get('unit', '')
            
            # ✅ FIXED: Get expiration date from item
            expiration_date_str = item.get('expiration_date')
            expiration_date = None
            
            if expiration_date_str:
                try:
                    expiration_date = datetime.strptime(expiration_date_str, '%Y-%m-%d').date()
                    print(f"     📅 Expiration date: {expiration_date}")
                except (ValueError, TypeError) as e:
                    print(f"     ⚠️  Invalid expiration date format: {expiration_date_str}, skipping")
                    expiration_date = None
            
            # Track if item has our_price
            if our_price > 0:
                items_with_our_price += 1
            
            # Calculate accepted quantity
            accepted_qty = received_qty - rejected_qty
            
            if accepted_qty < 0:
                raise ValueError(f"Item {idx}: Accepted quantity cannot be negative")
            
            # Calculate discount amount per unit
            if discount_type == 'percentage':
                discount_per_unit = (unit_price * discount) / 100
            else:
                discount_per_unit = discount
            
            price_after_discount = max(unit_price - discount_per_unit, 0)
            
            # Calculate tax per unit based on tax type
            if tax_type == 'inclusive':
                # Tax is included: extract it
                tax_per_unit = (price_after_discount * product_tax) / (100 + product_tax)
            else:
                # Tax is exclusive: add it
                tax_per_unit = (price_after_discount * product_tax) / 100
            
            # Calculate totals
            total_discount_amount = discount_per_unit * accepted_qty
            total_tax_amount = tax_per_unit * accepted_qty
            
            # Subtotal calculation
            if tax_type == 'inclusive':
                subtotal_per_unit = price_after_discount  # Already includes tax
            else:
                subtotal_per_unit = price_after_discount + tax_per_unit  # Add tax
            
            item_subtotal_calc = accepted_qty * subtotal_per_unit
            
            item_subtotal += item_subtotal_calc
            total_item_discount += total_discount_amount
            total_item_tax += total_tax_amount
            
            # ✅ UPDATED PRINT WITH OUR_PRICE
            print(f"\n  📦 Item {idx}: Product {product_id}")
            print(f"     Ordered: {ordered_qty}, Received: {received_qty}, Rejected: {rejected_qty}")
            print(f"     Accepted: {accepted_qty}")
            print(f"     Cost: {unit_price}, Price: {batch_price}, Our Price: {our_price}, Net Cost: {net_unit_cost}")
            print(f"     Discount: {total_discount_amount}, Tax: {total_tax_amount}, Subtotal: {item_subtotal_calc}")
            if expiration_date:
                print(f"     📅 Expiration: {expiration_date}")
            
            # ✅ ADD OUR_PRICE TO GRN_ITEMS_DATA
            grn_items_data.append({
                'product_id': product_id,
                'variation_id': variation_id,
                'ordered_quantity': ordered_qty,
                'received_quantity': received_qty,
                'rejected_quantity': rejected_qty,
                'unit_price': unit_price,
                'batch_price': batch_price,
                'our_price': our_price,                          # ✅ OUR_PRICE FIELD
                'discount': discount,
                'discount_type': discount_type,
                'product_tax': product_tax,
                'tax_type': tax_type,
                'net_unit_cost': net_unit_cost,
                'discount_amount': total_discount_amount,
                'tax_amount': total_tax_amount,
                'subtotal': item_subtotal_calc,
                'purchase_unit': unit,
                'quality_check': quality_check,
                'note': item_note,
                'expiration_date': expiration_date
            })
        
        # ✅ Step 5: Calculate ORDER-LEVEL totals
        print(f"\n📝 Step 5: Calculating order-level totals...")
        
        # Subtotal (sum of all item subtotals)
        subtotal = item_subtotal
        
        # Apply order discount (fixed amount)
        subtotal_after_discount = max(subtotal - order_discount_amount, 0)
        
        # Apply order tax (percentage on subtotal after discount)
        order_tax_amount = (subtotal_after_discount * order_tax_percentage) / 100
        
        # Grand total
        grand_total = subtotal_after_discount + order_tax_amount
        
        # ✅ Calculate due amount based on payment status
        if payment_status == 'Paid':
            actual_paid = grand_total
            due_amount = 0
        elif payment_status == 'Partial':
            actual_paid = paid_amount
            due_amount = max(grand_total - paid_amount, 0)
        else:  # Unpaid
            actual_paid = 0
            due_amount = grand_total
        
        print(f"\n  💰 Financial Summary:")
        print(f"     Item Subtotal: LKR {item_subtotal:.2f}")
        print(f"     Order Discount: -LKR {order_discount_amount:.2f}")
        print(f"     Subtotal after discount: LKR {subtotal_after_discount:.2f}")
        print(f"     Order Tax ({order_tax_percentage}%): +LKR {order_tax_amount:.2f}")
        print(f"     Grand Total: LKR {grand_total:.2f}")
        print(f"     Payment Status: {payment_status}")
        print(f"     Payment Type: {payment_type or 'N/A (Unpaid)'}")
        print(f"     Paid Amount: LKR {actual_paid:.2f}")
        print(f"     Due Amount: LKR {due_amount:.2f}")
        
        # Step 6: Insert GRN header
        print(f"\n📝 Step 6: Creating GRN header...")
        
        cursor.execute("""
            INSERT INTO grn (
                grn_code, purchase_order_id, supplier_id, warehouse_id, store_id,
                grn_date, received_by, invoice_number, invoice_date,
                vehicle_number, driver_name, driver_contact,
                status, total_items, subtotal, tax, discount, grand_total,
                order_tax, payment_status, payment_type, paid_amount, due_amount,
                note, created_by, created_at
            ) VALUES (
                %s, %s, %s, %s, %s,
                %s, %s, %s, %s,
                %s, %s, %s,
                'pending', %s, %s, %s, %s, %s,
                %s, %s, %s, %s, %s,
                %s, %s, NOW()
            )
        """, (
            grn_code, purchase_order_id, supplier_id, warehouse_id, store_id,
            grn_date, received_by_id, invoice_number, invoice_date,
            vehicle_number, driver_name, driver_contact,
            total_items, subtotal, order_tax_amount, order_discount_amount, grand_total,
            order_tax_percentage, payment_status, payment_type, actual_paid, due_amount,
            note, received_by_id
        ))
        
        grn_id = cursor.lastrowid
        print(f"  ✅ GRN created with ID: {grn_id}")
        print(f"  ✅ Saved with Warehouse: {warehouse_id}, Supplier: {supplier_id}")
        
        # Step 7: Check if grn_items table has required columns
        print(f"\n📝 Step 7: Checking grn_items table structure...")
        
        # Check batch_price column
        cursor.execute("SHOW COLUMNS FROM grn_items LIKE 'batch_price'")
        has_batch_price = cursor.fetchone() is not None
        
        if not has_batch_price:
            print(f"  ⚠️  batch_price column not found in grn_items")
            print(f"  💡 Adding batch_price column...")
            cursor.execute("""
                ALTER TABLE grn_items 
                ADD COLUMN batch_price DECIMAL(15,2) DEFAULT 0.00 
                AFTER unit_price
            """)
            print(f"  ✅ batch_price column added")
        
        # ✅ Check our_price column
        cursor.execute("SHOW COLUMNS FROM grn_items LIKE 'our_price'")
        has_our_price = cursor.fetchone() is not None
        
        if not has_our_price:
            print(f"  ⚠️  our_price column not found in grn_items")
            print(f"  💡 Adding our_price column...")
            cursor.execute("""
                ALTER TABLE grn_items 
                ADD COLUMN our_price DECIMAL(10,2) DEFAULT 0.00 
                AFTER batch_price
            """)
            print(f"  ✅ our_price column added")
        
        # ✅ Check expiration_date column
        cursor.execute("SHOW COLUMNS FROM grn_items LIKE 'expiration_date'")
        has_expiration_date = cursor.fetchone() is not None
        
        if not has_expiration_date:
            print(f"  ⚠️  expiration_date column not found in grn_items")
            print(f"  💡 Adding expiration_date column...")
            cursor.execute("""
                ALTER TABLE grn_items 
                ADD COLUMN expiration_date DATE NULL 
                AFTER note
            """)
            print(f"  ✅ expiration_date column added")
        
        # ✅ STEP 8: INSERT GRN ITEMS WITH OUR_PRICE
        print(f"\n📝 Step 8: Inserting GRN items with our_price...")
        
        for item_data in grn_items_data:
            cursor.execute("""
                INSERT INTO grn_items (
                    grn_id, product_id, variation_id,
                    ordered_quantity, received_quantity, rejected_quantity,
                    unit_price, batch_price, our_price,
                    discount_type, product_discount,
                    tax_type, product_tax,
                    discount, tax, net_unit_cost, subtotal,
                    purchase_unit, quality_check, note, 
                    expiration_date, created_at
                ) VALUES (
                    %s, %s, %s,
                    %s, %s, %s,
                    %s, %s, %s,
                    %s, %s,
                    %s, %s,
                    %s, %s, %s, %s,
                    %s, %s, %s, 
                    %s, NOW()
                )
            """, (
                grn_id, item_data['product_id'], item_data['variation_id'],
                item_data['ordered_quantity'], item_data['received_quantity'], 
                item_data['rejected_quantity'],
                item_data['unit_price'], item_data['batch_price'], item_data['our_price'],  # ✅ OUR_PRICE ADDED
                item_data['discount_type'], item_data['discount'],
                item_data['tax_type'], item_data['product_tax'],
                item_data['discount_amount'], item_data['tax_amount'], 
                item_data['net_unit_cost'], item_data['subtotal'],
                item_data['purchase_unit'], item_data['quality_check'], item_data['note'],
                item_data['expiration_date']
            ))
            
            grn_item_id = cursor.lastrowid
            exp_info = f" | Exp: {item_data['expiration_date']}" if item_data['expiration_date'] else ""
            our_price_info = f" | Our Price: LKR {item_data['our_price']}" if item_data['our_price'] > 0 else ""
            print(f"  ✅ Item {grn_item_id}: Product {item_data['product_id']}{our_price_info}{exp_info}")
        
        # Step 9: Update purchase order GRN status
        print(f"\n📝 Step 9: Updating PO GRN status...")
        
        cursor.execute("""
            SELECT 
                SUM(oi.quantity) as total_ordered,
                COALESCE((
                    SELECT SUM(gi.received_quantity - gi.rejected_quantity)
                    FROM grn g2
                    INNER JOIN grn_items gi ON g2.grn_id = gi.grn_id
                    WHERE g2.purchase_order_id = %s
                    AND g2.status != 'cancelled'
                ), 0) as total_received
            FROM order_items oi
            WHERE oi.order_id = %s
        """, (purchase_order_id, purchase_order_id))
        
        qty_result = cursor.fetchone()
        total_ordered = safe_float(qty_result['total_ordered'])
        total_received = safe_float(qty_result['total_received'])
        
        if total_received == 0:
            new_grn_status = 'not_received'
        elif total_received < total_ordered:
            new_grn_status = 'partial'
        else:
            new_grn_status = 'fully_received'
        
        cursor.execute("""
            UPDATE purchase_orders
            SET grn_status = %s
            WHERE order_id = %s
        """, (new_grn_status, purchase_order_id))
        
        print(f"  ✅ PO GRN status: {new_grn_status}")
        
        # Step 10: Create activity log
        print(f"\n📝 Step 10: Creating activity log...")
        
        cursor.execute("""
            INSERT INTO grn_activity_log (
                grn_id, user_id, action, description, created_at
            ) VALUES (
                %s, %s, 'GRN Created', %s, NOW()
            )
        """, (
            grn_id, received_by_id,
            f"GRN {grn_code} created for PO {purchase_order_id}. Warehouse: {warehouse_id}, Supplier: {supplier_id}, Items with our_price: {items_with_our_price}, Payment: {payment_status}, Grand Total: LKR {grand_total:.2f}"
        ))
        
        print(f"  ✅ Activity logged")
        
        # Commit transaction
        conn.commit()
        print(f"\n✅ Transaction committed successfully!")
        
        print("\n" + "=" * 80)
        print("✅ GRN CREATION COMPLETE")
        print("=" * 80)
        print(f"✅ GRN Code: {grn_code}")
        print(f"✅ GRN ID: {grn_id}")
        print(f"✅ Warehouse: {warehouse_id} (changed from PO: {po['warehouse_id']})")
        print(f"✅ Supplier: {supplier_id} (changed from PO: {po['supplier_id']})")
        print(f"✅ Status: pending")
        print(f"✅ Items: {total_items}")
        print(f"✅ Items with our_price: {items_with_our_price}")  # ✅ NEW
        print(f"✅ Items with expiration: {len([i for i in grn_items_data if i['expiration_date']])}")
        print(f"✅ Grand Total: LKR {grand_total:.2f}")
        print(f"✅ Payment Status: {payment_status}")
        print(f"✅ Payment Type: {payment_type or 'N/A (Unpaid)'}")
        print(f"✅ Due Amount: LKR {due_amount:.2f}")
        print(f"\n⚠️  IMPORTANT:")
        print(f"   📦 product_batches: NOT created yet")
        print(f"   📦 warehouse_stock: NOT updated yet")
        print(f"   🔄 Stock will be created when GRN is APPROVED")
        print(f"   ✅ our_price saved in grn_items: {items_with_our_price} items")
        print("=" * 80)
        
        return jsonify({
            'success': True,
            'message': f'GRN {grn_code} created successfully. Awaiting approval.',
            'grn_id': grn_id,
            'grn_code': grn_code,
            'data': {
                'grn_id': grn_id,
                'grn_code': grn_code,
                'purchase_order_id': purchase_order_id,
                'warehouse_id': warehouse_id,
                'supplier_id': supplier_id,
                'status': 'pending',
                'total_items': total_items,
                'items_with_our_price': items_with_our_price,  # ✅ NEW
                'subtotal': subtotal,
                'order_tax': order_tax_amount,
                'order_tax_percentage': order_tax_percentage,
                'discount': order_discount_amount,
                'grand_total': grand_total,
                'payment_status': payment_status,
                'payment_type': payment_type,
                'paid_amount': actual_paid,
                'due_amount': due_amount,
                'grn_status': new_grn_status,
                'stock_updated': False,
                'requires_approval': True
            }
        }), 201
    
    except mysql.connector.IntegrityError as err:
        conn.rollback()
        print(f"\n❌ Database Integrity Error: {err}")
        traceback.print_exc()
        return jsonify({'error': f'Database integrity error: {str(err)}'}), 500
    
    except ValueError as err:
        conn.rollback()
        print(f"\n❌ Validation Error: {err}")
        return jsonify({'error': str(err)}), 400
    
    except mysql.connector.Error as err:
        conn.rollback()
        print(f"\n❌ Database Error: {err}")
        traceback.print_exc()
        return jsonify({'error': f'Database error: {str(err)}'}), 500
    
    except Exception as e:
        conn.rollback()
        print(f"\n❌ Unexpected Error: {e}")
        traceback.print_exc()
        return jsonify({'error': f'Server error: {str(e)}'}), 500
    
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()
        print("\n🔒 Database connection closed\n")

@grn_bp.route('/update_grn/<int:grn_id>', methods=['PUT'])
@jwt_required()
@role_required('admin', 'manager')
def update_grn(grn_id):
    """
    ✅ UPDATE GRN - COMPLETE & CORRECT VERSION WITH OUR_PRICE
    
    Features:
    - ✅ Updates warehouse and supplier
    - ✅ Updates all pricing details (unit_price, batch_price, our_price)
    - ✅ Updates payment information
    - ✅ Updates expiration dates
    - ✅ Supports editing PENDING and COMPLETED GRNs
    - ✅ Reverses and recreates stock for COMPLETED GRNs
    - ✅ Automatic column creation if missing
    
    RESTRICTIONS:
    - Only PENDING and COMPLETED GRNs can be edited
    - CANCELLED GRNs cannot be edited
    """
    
    try:
        data = request.get_json(force=True)
        if not data:
            return jsonify({'error': 'No data provided'}), 400
    except Exception as e:
        return jsonify({'error': 'Invalid JSON data', 'message': str(e)}), 400
    
    print("=" * 80)
    print(f"✏️ GRN UPDATE - GRN ID: {grn_id}")
    print("=" * 80)
    
    # Extract basic data
    grn_date_str = data.get('grn_date')
    received_by_name = data.get('received_by', '').strip()
    invoice_number = data.get('invoice_number', '').strip() or None
    invoice_date_str = data.get('invoice_date') or None
    vehicle_number = data.get('vehicle_number', '').strip() or None
    driver_name = data.get('driver_name', '').strip() or None
    driver_contact = data.get('driver_contact', '').strip() or None
    note = data.get('note', '').strip()
    items = data.get('items', [])
    
    # ✅ Get warehouse and supplier from request
    warehouse_id = safe_int(data.get('warehouse_id'))
    supplier_id = safe_int(data.get('supplier_id'))
    
    # Order-level tax, discount, payment
    order_tax_percentage = safe_float(data.get('order_tax', 0))
    order_discount_amount = safe_float(data.get('discount', 0))
    payment_status = data.get('payment_status', 'Unpaid')
    
    # Handle None payment_type for Unpaid status
    payment_type_raw = data.get('payment_type')
    if payment_type_raw is not None and isinstance(payment_type_raw, str):
        payment_type = payment_type_raw.strip() or None
    else:
        payment_type = None
    
    paid_amount = safe_float(data.get('paid_amount', 0))
    
    print(f"\n📊 Update Data:")
    print(f"   Warehouse ID: {warehouse_id}")
    print(f"   Supplier ID: {supplier_id}")
    print(f"   Order Tax: {order_tax_percentage}%")
    print(f"   Order Discount: LKR {order_discount_amount}")
    print(f"   Payment Status: {payment_status}")
    print(f"   Payment Type: {payment_type or 'N/A (Unpaid)'}")
    print(f"   Items Count: {len(items)}")
    
    # ===========================
    # VALIDATION
    # ===========================
    
    if not grn_date_str:
        return jsonify({'error': 'GRN date is required'}), 400
    
    if not received_by_name:
        return jsonify({'error': 'Received by is required'}), 400
    
    if not warehouse_id:
        return jsonify({'error': 'Warehouse is required'}), 400
    
    if not supplier_id:
        return jsonify({'error': 'Supplier is required'}), 400
    
    if not items or len(items) == 0:
        return jsonify({'error': 'At least one item is required'}), 400
    
    if payment_status not in ['Paid', 'Unpaid', 'Partial']:
        return jsonify({'error': 'Invalid payment status. Must be Paid, Unpaid, or Partial'}), 400
    
    if payment_status in ['Paid', 'Partial'] and not payment_type:
        return jsonify({'error': 'Payment type is required for paid/partial payments'}), 400
    
    if payment_status == 'Partial' and paid_amount <= 0:
        return jsonify({'error': 'Paid amount must be greater than 0 for partial payments'}), 400
    
    # Parse dates
    try:
        grn_date = datetime.strptime(grn_date_str, '%Y-%m-%d').date()
    except ValueError:
        return jsonify({'error': 'Invalid GRN date format. Use YYYY-MM-DD'}), 400
    
    invoice_date = None
    if invoice_date_str:
        try:
            invoice_date = datetime.strptime(invoice_date_str, '%Y-%m-%d').date()
        except ValueError:
            return jsonify({'error': 'Invalid invoice date format. Use YYYY-MM-DD'}), 400
    
    conn = get_db_connection()
    if not conn:
        return jsonify({'error': 'Database connection failed'}), 500
    
    cursor = conn.cursor(dictionary=True)
    
    try:
        conn.start_transaction()
        print("\n🔄 Transaction started")
        
        # ===========================
        # STEP 1: Get existing GRN
        # ===========================
        print(f"\n📝 Step 1: Fetching existing GRN {grn_id}...")
        cursor.execute("""
            SELECT * FROM grn WHERE grn_id = %s
        """, (grn_id,))
        
        existing_grn = cursor.fetchone()
        
        if not existing_grn:
            raise ValueError(f"GRN {grn_id} not found")
        
        # Allow editing of PENDING and COMPLETED
        if existing_grn['status'] == 'cancelled':
            raise ValueError(f"Cannot update cancelled GRN. Only pending and completed GRNs can be edited.")
        
        is_completed = existing_grn['status'] == 'completed'
        
        if is_completed:
            print(f"  ⚠️  WARNING: Editing COMPLETED GRN {existing_grn['grn_code']}")
            print(f"  ⚠️  This will REVERSE and RECREATE stock and batches!")
        else:
            print(f"  ✅ GRN {existing_grn['grn_code']} found (status: {existing_grn['status']})")
        
        purchase_order_id = existing_grn['purchase_order_id']
        old_warehouse_id = existing_grn['warehouse_id']
        old_supplier_id = existing_grn['supplier_id']
        store_id = existing_grn['store_id']
        grn_code = existing_grn['grn_code']
        
        print(f"  📦 Old Warehouse: {old_warehouse_id}, New: {warehouse_id}")
        print(f"  🚚 Old Supplier: {old_supplier_id}, New: {supplier_id}")
        
        # ✅ Validate warehouse exists
        cursor.execute("SELECT id FROM warehouses WHERE id = %s AND is_active = 1", (warehouse_id,))
        if not cursor.fetchone():
            raise ValueError(f"Warehouse {warehouse_id} not found or inactive")
        
        # ✅ Validate supplier exists
        cursor.execute("SELECT id FROM suppliers WHERE id = %s", (supplier_id,))
        if not cursor.fetchone():
            raise ValueError(f"Supplier {supplier_id} not found")
        
        # ===========================
        # STEP 2: Get user ID
        # ===========================
        print(f"\n📝 Step 2: Getting user ID...")
        
        current_user = get_jwt_identity()
        received_by_id = None
        
        if current_user:
            cursor.execute("SELECT id FROM users WHERE id = %s", (current_user,))
            user = cursor.fetchone()
            if user:
                received_by_id = user['id']
        
        if not received_by_id:
            cursor.execute("SELECT id FROM users WHERE role = 'admin' LIMIT 1")
            admin = cursor.fetchone()
            if admin:
                received_by_id = admin['id']
        
        if not received_by_id:
            raise ValueError("No valid user found")
        
        print(f"  ✅ User ID: {received_by_id}")
        
        # ===========================
        # STEP 2.5: REVERSE STOCK IF COMPLETED
        # ===========================
        if is_completed:
            print(f"\n📝 Step 2.5: REVERSING stock for completed GRN...")
            
            # Get old GRN items
            cursor.execute("""
                SELECT * FROM grn_items WHERE grn_id = %s
            """, (grn_id,))
            
            old_items = cursor.fetchall()
            
            for idx, old_item in enumerate(old_items, 1):
                if not old_item.get('batch_id'):
                    print(f"  ⚠️  Item {idx}: No batch_id, skipping")
                    continue
                
                batch_id = old_item['batch_id']
                accepted_qty = old_item['received_quantity'] - old_item['rejected_quantity']
                
                print(f"\n  🔄 Reversing Item {idx}:")
                print(f"     Product: {old_item['product_id']}")
                print(f"     Batch ID: {batch_id}")
                print(f"     Quantity to remove: {accepted_qty}")
                
                # Delete warehouse stock entry
                cursor.execute("""
                    DELETE FROM warehouse_stock
                    WHERE batch_id = %s
                    AND store_id = %s
                    AND warehouse_id = %s
                """, (batch_id, store_id, old_warehouse_id))
                
                deleted_stock = cursor.rowcount
                print(f"     ✅ Deleted {deleted_stock} warehouse_stock record(s)")
                
                # Delete product batch
                cursor.execute("""
                    DELETE FROM product_batches
                    WHERE batch_id = %s
                """, (batch_id,))
                
                deleted_batch = cursor.rowcount
                print(f"     ✅ Deleted {deleted_batch} product_batch record(s)")
            
            print(f"\n  ✅ Stock reversal complete for {len(old_items)} items")
        
        # ===========================
        # STEP 3: PROCESS NEW ITEMS WITH OUR_PRICE
        # ===========================
        print(f"\n📝 Step 3: Processing {len(items)} NEW items...")
        
        total_items = len(items)
        item_subtotal = 0.0
        total_item_tax = 0.0
        total_item_discount = 0.0
        
        grn_items_data = []
        
        for idx, item in enumerate(items, 1):
            product_id = safe_int(item.get('product_id'))
            variation_id = safe_int(item.get('variation_id')) if item.get('variation_id') else None
            ordered_qty = safe_float(item.get('ordered_quantity', 0))
            received_qty = safe_float(item.get('received_quantity', 0))
            rejected_qty = safe_float(item.get('rejected_quantity', 0))
            
            unit_price = safe_float(item.get('unit_price', 0))
            batch_price = safe_float(item.get('batch_price', 0))
            
            # ✅ EXTRACT OUR_PRICE
            our_price = safe_float(item.get('our_price', 0))
            
            discount = safe_float(item.get('discount', 0))
            discount_type = item.get('discount_type', 'fixed')
            product_tax = safe_float(item.get('product_tax', 0))
            tax_type = item.get('tax_type', 'exclusive')
            net_unit_cost = safe_float(item.get('net_unit_cost', 0))
            
            quality_check = item.get('quality_check', 'pending')
            item_note = item.get('note', '').strip()
            unit = item.get('unit', '')
            
            # ✅ Get expiration date
            expiration_date_str = item.get('expiration_date')
            expiration_date = None
            
            if expiration_date_str:
                try:
                    expiration_date = datetime.strptime(expiration_date_str, '%Y-%m-%d').date()
                    print(f"     📅 Expiration date: {expiration_date}")
                except (ValueError, TypeError):
                    print(f"     ⚠️  Invalid expiration date: {expiration_date_str}, skipping")
                    expiration_date = None
            
            accepted_qty = received_qty - rejected_qty
            
            if accepted_qty < 0:
                raise ValueError(f"Item {idx}: Accepted quantity cannot be negative")
            
            # Calculate discount amount per unit
            if discount_type == 'percentage':
                discount_per_unit = (unit_price * discount) / 100
            else:
                discount_per_unit = discount
            
            price_after_discount = max(unit_price - discount_per_unit, 0)
            
            # Calculate tax per unit
            if tax_type == 'inclusive':
                tax_per_unit = (price_after_discount * product_tax) / (100 + product_tax)
            else:
                tax_per_unit = (price_after_discount * product_tax) / 100
            
            # Calculate totals
            total_discount_amount = discount_per_unit * accepted_qty
            total_tax_amount = tax_per_unit * accepted_qty
            
            # Subtotal calculation
            if tax_type == 'inclusive':
                subtotal_per_unit = price_after_discount
            else:
                subtotal_per_unit = price_after_discount + tax_per_unit
            
            item_subtotal_calc = accepted_qty * subtotal_per_unit
            
            item_subtotal += item_subtotal_calc
            total_item_discount += total_discount_amount
            total_item_tax += total_tax_amount
            
            print(f"\n  📦 Item {idx}: Product {product_id}")
            print(f"     Ordered: {ordered_qty}, Received: {received_qty}, Rejected: {rejected_qty}")
            print(f"     Accepted: {accepted_qty}")
            print(f"     Cost: {unit_price}, Price: {batch_price}, Our Price: {our_price}, Net Cost: {net_unit_cost}")
            print(f"     Discount: {total_discount_amount}, Tax: {total_tax_amount}, Subtotal: {item_subtotal_calc}")
            if expiration_date:
                print(f"     📅 Expiration: {expiration_date}")
            
            # ✅ ADD OUR_PRICE TO DICTIONARY
            grn_items_data.append({
                'product_id': product_id,
                'variation_id': variation_id,
                'ordered_quantity': ordered_qty,
                'received_quantity': received_qty,
                'rejected_quantity': rejected_qty,
                'unit_price': unit_price,
                'batch_price': batch_price,
                'our_price': our_price,  # ✅ OUR_PRICE INCLUDED
                'discount': discount,
                'discount_type': discount_type,
                'product_tax': product_tax,
                'tax_type': tax_type,
                'net_unit_cost': net_unit_cost,
                'discount_amount': total_discount_amount,
                'tax_amount': total_tax_amount,
                'subtotal': item_subtotal_calc,
                'purchase_unit': unit,
                'quality_check': quality_check,
                'note': item_note,
                'expiration_date': expiration_date
            })
        
        # ===========================
        # STEP 4: CALCULATE ORDER-LEVEL TOTALS
        # ===========================
        print(f"\n📝 Step 4: Calculating order-level totals...")
        
        subtotal = item_subtotal
        subtotal_after_discount = max(subtotal - order_discount_amount, 0)
        order_tax_amount = (subtotal_after_discount * order_tax_percentage) / 100
        grand_total = subtotal_after_discount + order_tax_amount
        
        # Calculate payment amounts
        if payment_status == 'Paid':
            actual_paid = grand_total
            due_amount = 0
        elif payment_status == 'Partial':
            actual_paid = paid_amount
            due_amount = max(grand_total - paid_amount, 0)
        else:
            actual_paid = 0
            due_amount = grand_total
        
        print(f"\n  💰 Financial Summary:")
        print(f"     Item Subtotal: LKR {item_subtotal:.2f}")
        print(f"     Order Discount: -LKR {order_discount_amount:.2f}")
        print(f"     Subtotal after discount: LKR {subtotal_after_discount:.2f}")
        print(f"     Order Tax ({order_tax_percentage}%): +LKR {order_tax_amount:.2f}")
        print(f"     Grand Total: LKR {grand_total:.2f}")
        print(f"     Paid Amount: LKR {actual_paid:.2f}")
        print(f"     Due Amount: LKR {due_amount:.2f}")
        
        # ===========================
        # STEP 5: UPDATE GRN HEADER
        # ===========================
        print(f"\n📝 Step 5: Updating GRN header...")
        
        cursor.execute("""
            UPDATE grn SET
                warehouse_id = %s,
                supplier_id = %s,
                grn_date = %s,
                received_by = %s,
                invoice_number = %s,
                invoice_date = %s,
                vehicle_number = %s,
                driver_name = %s,
                driver_contact = %s,
                total_items = %s,
                subtotal = %s,
                tax = %s,
                discount = %s,
                grand_total = %s,
                order_tax = %s,
                payment_status = %s,
                payment_type = %s,
                paid_amount = %s,
                due_amount = %s,
                note = %s,
                updated_at = NOW()
            WHERE grn_id = %s
        """, (
            warehouse_id, supplier_id,
            grn_date, received_by_id, invoice_number, invoice_date,
            vehicle_number, driver_name, driver_contact,
            total_items, subtotal, order_tax_amount, order_discount_amount, grand_total,
            order_tax_percentage, payment_status, payment_type, actual_paid, due_amount,
            note, grn_id
        ))
        
        print(f"  ✅ GRN header updated")
        print(f"  ✅ Warehouse: {old_warehouse_id} → {warehouse_id}")
        print(f"  ✅ Supplier: {old_supplier_id} → {supplier_id}")
        
        # ===========================
        # STEP 6: DELETE OLD ITEMS
        # ===========================
        print(f"\n📝 Step 6: Deleting old GRN items...")
        cursor.execute("DELETE FROM grn_items WHERE grn_id = %s", (grn_id,))
        print(f"  ✅ Old items deleted")
        
        # ===========================
        # STEP 7: CHECK & ADD MISSING COLUMNS
        # ===========================
        print(f"\n📝 Step 7: Checking grn_items table structure...")
        
        # Check batch_price column
        cursor.execute("SHOW COLUMNS FROM grn_items LIKE 'batch_price'")
        has_batch_price = cursor.fetchone() is not None
        
        if not has_batch_price:
            print(f"  ⚠️  batch_price column not found, adding it...")
            cursor.execute("""
                ALTER TABLE grn_items 
                ADD COLUMN batch_price DECIMAL(15,2) DEFAULT 0.00 
                AFTER unit_price
            """)
            print(f"  ✅ batch_price column added")
        
        # ✅ Check our_price column
        cursor.execute("SHOW COLUMNS FROM grn_items LIKE 'our_price'")
        has_our_price = cursor.fetchone() is not None
        
        if not has_our_price:
            print(f"  ⚠️  our_price column not found, adding it...")
            cursor.execute("""
                ALTER TABLE grn_items 
                ADD COLUMN our_price DECIMAL(10,2) DEFAULT 0.00 
                AFTER batch_price
            """)
            print(f"  ✅ our_price column added")
        
        # Check expiration_date column
        cursor.execute("SHOW COLUMNS FROM grn_items LIKE 'expiration_date'")
        has_expiration_date = cursor.fetchone() is not None
        
        if not has_expiration_date:
            print(f"  ⚠️  expiration_date column not found, adding it...")
            cursor.execute("""
                ALTER TABLE grn_items 
                ADD COLUMN expiration_date DATE NULL 
                AFTER note
            """)
            print(f"  ✅ expiration_date column added")
        
        # ===========================
        # STEP 8: INSERT UPDATED ITEMS WITH OUR_PRICE
        # ===========================
        print(f"\n📝 Step 8: Inserting updated GRN items with our_price...")
        
        for item_data in grn_items_data:
            # ✅ INSERT WITH OUR_PRICE
            cursor.execute("""
                INSERT INTO grn_items (
                    grn_id, product_id, variation_id,
                    ordered_quantity, received_quantity, rejected_quantity,
                    unit_price, batch_price, our_price,
                    discount_type, product_discount,
                    tax_type, product_tax,
                    discount, tax, net_unit_cost, subtotal,
                    purchase_unit, quality_check, note,
                    expiration_date, created_at
                ) VALUES (
                    %s, %s, %s,
                    %s, %s, %s,
                    %s, %s, %s,
                    %s, %s,
                    %s, %s,
                    %s, %s, %s, %s,
                    %s, %s, %s,
                    %s, NOW()
                )
            """, (
                grn_id, item_data['product_id'], item_data['variation_id'],
                item_data['ordered_quantity'], item_data['received_quantity'], 
                item_data['rejected_quantity'],
                item_data['unit_price'], item_data['batch_price'], item_data['our_price'],  # ✅ OUR_PRICE
                item_data['discount_type'], item_data['discount'],
                item_data['tax_type'], item_data['product_tax'],
                item_data['discount_amount'], item_data['tax_amount'], 
                item_data['net_unit_cost'], item_data['subtotal'],
                item_data['purchase_unit'], item_data['quality_check'], item_data['note'],
                item_data['expiration_date']
            ))
            
            grn_item_id = cursor.lastrowid
            exp_info = f" | Exp: {item_data['expiration_date']}" if item_data['expiration_date'] else ""
            our_price_info = f" | Our: LKR {item_data['our_price']}" if item_data['our_price'] > 0 else ""
            print(f"  ✅ Item {grn_item_id}: Product {item_data['product_id']}{our_price_info}{exp_info}")
        
        # ===========================
        # STEP 9: RECREATE BATCHES AND STOCK IF COMPLETED
        # ===========================
        if is_completed:
            print(f"\n📝 Step 9: RECREATING batches and stock for completed GRN...")
            
            # Get newly inserted items with their IDs
            cursor.execute("""
                SELECT * FROM grn_items WHERE grn_id = %s ORDER BY grn_item_id
            """, (grn_id,))
            
            new_items = cursor.fetchall()
            
            for idx, (item_data, new_item) in enumerate(zip(grn_items_data, new_items), 1):
                accepted_qty = item_data['received_quantity'] - item_data['rejected_quantity']
                
                if accepted_qty <= 0:
                    print(f"  ⚠️  Item {idx}: No accepted quantity, skipping")
                    continue
                
                # Generate batch number
                batch_number = f"BATCH-{grn_code}-{idx}"
                
                print(f"\n  📦 Recreating Item {idx}:")
                print(f"     Product: {item_data['product_id']}")
                print(f"     Accepted Qty: {accepted_qty}")
                print(f"     Our Price: {item_data['our_price']}")
                if item_data['expiration_date']:
                    print(f"     📅 Expiration: {item_data['expiration_date']}")
                
                # ✅ Create product batch WITH OUR_PRICE
                cursor.execute("""
                    INSERT INTO product_batches (
                        batch_number, product_id, variation_id,
                        quantity, remaining_quantity,
                        cost, price, our_price,
                        grn_id, purchase_order_id,
                        expiration_date
                    ) VALUES (
                        %s, %s, %s,
                        %s, %s,
                        %s, %s, %s,
                        %s, %s,
                        %s
                    )
                """, (
                    batch_number, item_data['product_id'], item_data['variation_id'],
                    accepted_qty, accepted_qty,
                    item_data['net_unit_cost'], item_data['batch_price'],
                    item_data['our_price'],  # ✅ OUR_PRICE INCLUDED
                    grn_id, purchase_order_id,
                    item_data['expiration_date']
                ))
                
                batch_id = cursor.lastrowid
                print(f"     ✅ Batch created: {batch_id} ({batch_number})")
                
                # Update GRN item with batch_id
                cursor.execute("""
                    UPDATE grn_items
                    SET batch_id = %s
                    WHERE grn_item_id = %s
                """, (batch_id, new_item['grn_item_id']))
                
                # ✅ Create warehouse stock using NEW warehouse_id
                cursor.execute("""
                    INSERT INTO warehouse_stock (
                        store_id, product_id, warehouse_id, batch_id,
                        variation_id, quantity
                    ) VALUES (
                        %s, %s, %s, %s,
                        %s, %s
                    )
                """, (
                    store_id, item_data['product_id'], 
                    warehouse_id,  # ✅ Use NEW warehouse_id
                    batch_id,
                    item_data['variation_id'], accepted_qty
                ))
                
                print(f"     ✅ Stock created: {accepted_qty} units in warehouse {warehouse_id}")
            
            print(f"\n  ✅ Stock recreation complete for {len(grn_items_data)} items")
        
        # ===========================
        # STEP 10: UPDATE PO GRN STATUS
        # ===========================
        print(f"\n📝 Step 10: Updating PO GRN status...")
        
        cursor.execute("""
            SELECT 
                SUM(oi.quantity) as total_ordered,
                COALESCE((
                    SELECT SUM(gi.received_quantity - gi.rejected_quantity)
                    FROM grn g2
                    INNER JOIN grn_items gi ON g2.grn_id = gi.grn_id
                    WHERE g2.purchase_order_id = %s
                    AND g2.status != 'cancelled'
                ), 0) as total_received
            FROM order_items oi
            WHERE oi.order_id = %s
        """, (purchase_order_id, purchase_order_id))
        
        qty_result = cursor.fetchone()
        total_ordered = safe_float(qty_result['total_ordered'])
        total_received = safe_float(qty_result['total_received'])
        
        if total_received == 0:
            new_grn_status = 'not_received'
        elif total_received < total_ordered:
            new_grn_status = 'partial'
        else:
            new_grn_status = 'fully_received'
        
        cursor.execute("""
            UPDATE purchase_orders
            SET grn_status = %s
            WHERE order_id = %s
        """, (new_grn_status, purchase_order_id))
        
        print(f"  ✅ PO GRN status: {new_grn_status}")
        
        # ===========================
        # STEP 11: CREATE ACTIVITY LOG
        # ===========================
        print(f"\n📝 Step 11: Creating activity log...")
        
        action_type = 'GRN Updated (Completed)' if is_completed else 'GRN Updated'
        description = f"GRN {grn_code} updated. "
        
        if warehouse_id != old_warehouse_id:
            description += f"Warehouse: {old_warehouse_id} → {warehouse_id}. "
        
        if supplier_id != old_supplier_id:
            description += f"Supplier: {old_supplier_id} → {supplier_id}. "
        
        if is_completed:
            description += "Stock reversed and recreated. "
        
        description += f"Payment: {payment_status}, Total: LKR {grand_total:.2f}"
        
        cursor.execute("""
            INSERT INTO grn_activity_log (
                grn_id, user_id, action, description, created_at
            ) VALUES (
                %s, %s, %s, %s, NOW()
            )
        """, (grn_id, current_user, action_type, description))
        
        print(f"  ✅ Activity logged")
        
        # ===========================
        # COMMIT TRANSACTION
        # ===========================
        conn.commit()
        print(f"\n✅ Transaction committed successfully!")
        
        print("\n" + "=" * 80)
        print("✅ GRN UPDATE COMPLETE")
        print("=" * 80)
        print(f"✅ GRN Code: {grn_code}")
        print(f"✅ GRN ID: {grn_id}")
        print(f"✅ Warehouse: {old_warehouse_id} → {warehouse_id}")
        print(f"✅ Supplier: {old_supplier_id} → {supplier_id}")
        print(f"✅ Status: {existing_grn['status']}")
        print(f"✅ Items: {total_items}")
        print(f"✅ Items with our_price: {len([i for i in grn_items_data if safe_float(i.get('our_price', 0)) > 0])}")
        print(f"✅ Items with expiration: {len([i for i in grn_items_data if i['expiration_date']])}")
        print(f"✅ Grand Total: LKR {grand_total:.2f}")
        print(f"✅ Payment Status: {payment_status}")
        
        if is_completed:
            print(f"\n⚠️  COMPLETED GRN EDITED:")
            print(f"   📦 Old stock REVERSED")
            print(f"   📦 New stock CREATED in warehouse {warehouse_id}")
            print(f"   ✅ Stock reflects updated warehouse and quantities")
        
        print("=" * 80)
        
        return jsonify({
            'success': True,
            'message': f'GRN {grn_code} updated successfully.',
            'data': {
                'grn_id': grn_id,
                'grn_code': grn_code,
                'purchase_order_id': purchase_order_id,
                'warehouse_id': warehouse_id,
                'supplier_id': supplier_id,
                'status': existing_grn['status'],
                'total_items': total_items,
                'subtotal': subtotal,
                'order_tax': order_tax_amount,
                'order_tax_percentage': order_tax_percentage,
                'discount': order_discount_amount,
                'grand_total': grand_total,
                'payment_status': payment_status,
                'payment_type': payment_type,
                'paid_amount': actual_paid,
                'due_amount': due_amount,
                'stock_recreated': is_completed
            }
        }), 200
    
    except ValueError as err:
        conn.rollback()
        print(f"\n❌ Validation Error: {err}")
        return jsonify({'error': str(err)}), 400
    
    except mysql.connector.Error as err:
        conn.rollback()
        print(f"\n❌ Database Error: {err}")
        traceback.print_exc()
        return jsonify({'error': f'Database error: {str(err)}'}), 500
    
    except Exception as e:
        conn.rollback()
        print(f"\n❌ Unexpected Error: {e}")
        traceback.print_exc()
        return jsonify({'error': f'Server error: {str(e)}'}), 500
    
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()
        print("\n🔒 Database connection closed\n")


@grn_bp.route('/approve_grn/<int:grn_id>', methods=['POST'])
@jwt_required()
@role_required('admin', 'manager')
def approve_grn(grn_id):
    """
    ✅ APPROVE GRN - COMPLETE & CORRECT VERSION WITH OUR_PRICE & EXPIRATION_DATE
    
    This endpoint creates product_batches and updates stock.
    After this, products are AVAILABLE FOR SALE in POS!
    
    Features:
    - ✅ Creates product_batches with our_price
    - ✅ Saves expiration_date in batches ✓ FIXED
    - ✅ Updates warehouse_stock with accepted quantities
    - ✅ Updates GRN status to 'completed'
    - ✅ Updates purchase_orders status
    - ✅ Logs approval activity
    - ✅ Proper transaction handling
    - ✅ Complete error handling
    """
    
    conn = get_db_connection()
    if not conn:
        return jsonify({'error': 'Database connection failed'}), 500
    
    cursor = conn.cursor(dictionary=True)
    
    try:
        conn.start_transaction()
        
        print("\n" + "=" * 80)
        print(f"🎯 APPROVING GRN {grn_id} - STOCK UPDATE BEGINS!")
        print("=" * 80)
        
        # ===========================
        # STEP 1: Get GRN details
        # ===========================
        print(f"\n📝 Step 1: Fetching GRN details...")
        cursor.execute("""
            SELECT g.*, u.name as received_by_name
            FROM grn g
            LEFT JOIN users u ON g.received_by = u.id
            WHERE g.grn_id = %s
        """, (grn_id,))
        
        grn = cursor.fetchone()
        
        if not grn:
            raise ValueError(f"GRN {grn_id} not found")
        
        if grn['status'] == 'completed':
            raise ValueError(f"GRN {grn['grn_code']} is already approved")
        
        if grn['status'] == 'cancelled':
            raise ValueError(f"GRN {grn['grn_code']} is cancelled and cannot be approved")
        
        print(f"  ✅ GRN {grn['grn_code']} found")
        print(f"  📦 Warehouse: {grn['warehouse_id']}")
        print(f"  🚚 Supplier: {grn['supplier_id']}")
        print(f"  💰 Grand Total: LKR {grn['grand_total']:.2f}")
        
        # ===========================
        # STEP 2: Get GRN items WITH OUR_PRICE & EXPIRATION_DATE
        # ===========================
        print(f"\n📝 Step 2: Fetching GRN items with our_price and expiration dates...")
        cursor.execute("""
            SELECT 
                gi.*,
                p.product_name, p.sku
            FROM grn_items gi
            LEFT JOIN products p ON gi.product_id = p.id
            WHERE gi.grn_id = %s
            ORDER BY gi.grn_item_id
        """, (grn_id,))
        
        items = cursor.fetchall()
        
        if not items:
            raise ValueError(f"No items found for GRN {grn_id}")
        
        print(f"  ✅ Found {len(items)} items")
        
        items_with_our_price = len([i for i in items if safe_float(i.get('our_price', 0)) > 0])
        items_with_expiry = len([i for i in items if i.get('expiration_date')])
        print(f"  ✅ Items with our_price: {items_with_our_price}")
        print(f"  ✅ Items with expiration_date: {items_with_expiry}")
        
        # ===========================
        # STEP 3: CREATE BATCHES AND UPDATE STOCK
        # ===========================
        print(f"\n📝 Step 3: Creating batches and updating stock...")
        
        current_user = get_jwt_identity()
        batches_created = 0
        items_processed = 0
        
        for idx, item in enumerate(items, 1):
            accepted_qty = item['received_quantity'] - item['rejected_quantity']
            
            if accepted_qty <= 0:
                print(f"  ⚠️  Item {idx}: No accepted quantity, skipping")
                continue
            
            items_processed += 1
            
            # Generate batch number
            batch_number = f"BATCH-{grn['grn_code']}-{idx}"
            
            print(f"\n  📦 Item {idx}:")
            print(f"     Product: {item['product_id']} ({item.get('product_name', 'N/A')})")
            print(f"     Ordered: {item['ordered_quantity']}, Received: {item['received_quantity']}, Rejected: {item['rejected_quantity']}")
            print(f"     Accepted Qty: {accepted_qty}")
            print(f"     Cost: {item['unit_price']}")
            
            # Get batch_price from grn_items
            batch_price = safe_float(item.get('batch_price', item['unit_price']))
            print(f"     Batch Price: {batch_price}")
            
            # ✅ GET OUR_PRICE FROM GRN_ITEMS
            our_price = safe_float(item.get('our_price', 0))
            print(f"     Our Price: {our_price}")
            
            # ✅ GET EXPIRATION_DATE FROM GRN_ITEMS
            expiration_date = item.get('expiration_date')
            if expiration_date:
                print(f"     📅 Expiration: {expiration_date}")
            else:
                print(f"     📅 Expiration: No expiry date")
            
            # ✅ CREATE PRODUCT BATCH WITH OUR_PRICE & EXPIRATION_DATE
            # Check if expiration_date column exists in product_batches table
            cursor.execute("""
                SELECT COLUMN_NAME 
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = 'product_batches' 
                AND COLUMN_NAME = 'expiration_date'
            """)
            
            has_expiration_column = cursor.fetchone() is not None
            
            if has_expiration_column:
                # Insert with expiration_date
                cursor.execute("""
                    INSERT INTO product_batches (
                        batch_number, product_id, variation_id,
                        quantity, remaining_quantity,
                        cost, price, our_price,
                        grn_id, purchase_order_id,
                        expiration_date
                    ) VALUES (
                        %s, %s, %s,
                        %s, %s,
                        %s, %s, %s,
                        %s, %s,
                        %s
                    )
                """, (
                    batch_number, item['product_id'], item['variation_id'],
                    accepted_qty, accepted_qty,
                    item['net_unit_cost'], batch_price,
                    our_price,  # ✅ OUR_PRICE INCLUDED
                    grn_id, grn['purchase_order_id'],
                    expiration_date  # ✅ EXPIRATION_DATE INCLUDED
                ))
                print(f"     ✅ Batch created with expiration_date: {expiration_date}")
            else:
                # Insert without expiration_date (column doesn't exist)
                cursor.execute("""
                    INSERT INTO product_batches (
                        batch_number, product_id, variation_id,
                        quantity, remaining_quantity,
                        cost, price, our_price,
                        grn_id, purchase_order_id
                    ) VALUES (
                        %s, %s, %s,
                        %s, %s,
                        %s, %s, %s,
                        %s, %s
                    )
                """, (
                    batch_number, item['product_id'], item['variation_id'],
                    accepted_qty, accepted_qty,
                    item['net_unit_cost'], batch_price,
                    our_price,  # ✅ OUR_PRICE INCLUDED
                    grn_id, grn['purchase_order_id']
                ))
                print(f"     ⚠️  Batch created without expiration_date (column doesn't exist in table)")
            
            batch_id = cursor.lastrowid
            batches_created += 1
            print(f"     ✅ Batch {batch_id} created: {batch_number}")
            print(f"     ✅ Our_price saved: {our_price}")
            
            # Update GRN item with batch_id
            cursor.execute("""
                UPDATE grn_items
                SET batch_id = %s
                WHERE grn_item_id = %s
            """, (batch_id, item['grn_item_id']))
            
            # ===========================
            # UPDATE/CREATE WAREHOUSE STOCK
            # ===========================
            
            # Check if stock already exists for this batch
            cursor.execute("""
                SELECT id, quantity
                FROM warehouse_stock
                WHERE store_id = %s
                AND product_id = %s
                AND warehouse_id = %s
                AND batch_id = %s
            """, (grn['store_id'], item['product_id'], grn['warehouse_id'], batch_id))
            
            existing_stock = cursor.fetchone()
            
            if existing_stock:
                # Update existing stock
                new_qty = existing_stock['quantity'] + accepted_qty
                cursor.execute("""
                    UPDATE warehouse_stock
                    SET quantity = %s, updated_at = NOW()
                    WHERE id = %s
                """, (new_qty, existing_stock['id']))
                print(f"     ✅ Stock updated: {existing_stock['quantity']} → {new_qty}")
            else:
                # Insert new stock
                cursor.execute("""
                    INSERT INTO warehouse_stock (
                        store_id, product_id, warehouse_id, batch_id,
                        variation_id, quantity
                    ) VALUES (
                        %s, %s, %s, %s,
                        %s, %s
                    )
                """, (
                    grn['store_id'], item['product_id'], 
                    grn['warehouse_id'], batch_id,
                    item['variation_id'], accepted_qty
                ))
                print(f"     ✅ Stock created: {accepted_qty} units in warehouse {grn['warehouse_id']}")
        
        print(f"\n  ✅ Batches created: {batches_created}/{len(items)}")
        print(f"  ✅ Items processed: {items_processed}")
        
        # ===========================
        # STEP 4: UPDATE GRN STATUS
        # ===========================
        print(f"\n📝 Step 4: Updating GRN status...")
        cursor.execute("""
            UPDATE grn
            SET status = 'completed',
                approved_by = %s,
                approved_at = NOW()
            WHERE grn_id = %s
        """, (current_user, grn_id))
        print(f"  ✅ GRN status: pending → completed")
        
        # ===========================
        # STEP 5: UPDATE PURCHASE ORDER
        # ===========================
        print(f"\n📝 Step 5: Updating Purchase Order...")
        
        cursor.execute("""
            SELECT 
                SUM(oi.quantity) as total_ordered,
                COALESCE((
                    SELECT SUM(gi.received_quantity - gi.rejected_quantity)
                    FROM grn g2
                    INNER JOIN grn_items gi ON g2.grn_id = gi.grn_id
                    WHERE g2.purchase_order_id = %s
                    AND g2.status = 'completed'
                ), 0) as total_received
            FROM order_items oi
            WHERE oi.order_id = %s
        """, (grn['purchase_order_id'], grn['purchase_order_id']))
        
        po_qty = cursor.fetchone()
        total_ordered = safe_float(po_qty['total_ordered'])
        total_received = safe_float(po_qty['total_received'])
        
        print(f"  📊 Total Ordered: {total_ordered}")
        print(f"  📊 Total Received: {total_received}")
        
        # Determine PO status
        if total_received >= total_ordered:
            new_po_status = 'Received'
            new_grn_status = 'fully_received'
        else:
            new_po_status = 'Ordered'
            new_grn_status = 'partial'
        
        cursor.execute("""
            UPDATE purchase_orders
            SET status = %s,
                grn_status = %s
            WHERE order_id = %s
        """, (new_po_status, new_grn_status, grn['purchase_order_id']))
        
        print(f"  ✅ PO Status: {new_po_status}")
        print(f"  ✅ PO GRN Status: {new_grn_status}")
        
        # ===========================
        # STEP 6: LOG ACTIVITY
        # ===========================
        print(f"\n📝 Step 6: Logging approval activity...")
        
        description = f"GRN {grn['grn_code']} approved. "
        description += f"Batches: {batches_created}, "
        description += f"Items: {items_processed}, "
        description += f"Items with our_price: {items_with_our_price}, "
        description += f"Items with expiry: {items_with_expiry}, "
        description += f"Stock: {new_grn_status}, "
        description += f"Total: LKR {grn['grand_total']:.2f}"
        
        cursor.execute("""
            INSERT INTO grn_activity_log (
                grn_id, user_id, action, description, created_at
            ) VALUES (
                %s, %s, 'GRN Approved', %s, NOW()
            )
        """, (grn_id, current_user, description))
        
        print(f"  ✅ Activity logged")
        
        # ===========================
        # COMMIT TRANSACTION
        # ===========================
        conn.commit()
        
        print("\n" + "=" * 80)
        print("🎉 GRN APPROVAL COMPLETE - STOCK NOW AVAILABLE!")
        print("=" * 80)
        print(f"✅ GRN {grn['grn_code']} approved successfully")
        print(f"✅ Batches created: {batches_created}")
        print(f"✅ Items processed: {items_processed}")
        print(f"✅ Items with our_price: {items_with_our_price}")
        print(f"✅ Items with expiry dates: {items_with_expiry}")
        print(f"✅ Stock updated in warehouse: {grn['warehouse_id']}")
        print(f"✅ PO Status: {new_po_status} / GRN Status: {new_grn_status}")
        print(f"✅ Products now available for sale in POS!")
        print("=" * 80)
        
        return jsonify({
            'success': True,
            'message': f'GRN {grn["grn_code"]} approved successfully. Stock updated!',
            'data': {
                'grn_id': grn_id,
                'grn_code': grn['grn_code'],
                'status': 'completed',
                'batches_created': batches_created,
                'items_processed': items_processed,
                'items_with_our_price': items_with_our_price,
                'items_with_expiry': items_with_expiry,
                'stock_updated': True,
                'warehouse_id': grn['warehouse_id'],
                'po_status': new_po_status,
                'po_grn_status': new_grn_status,
                'grand_total': grn['grn_code']
            }
        }), 200
    
    except ValueError as err:
        conn.rollback()
        print(f"\n❌ Validation Error: {err}")
        return jsonify({'error': str(err)}), 400
    
    except mysql.connector.Error as err:
        conn.rollback()
        print(f"\n❌ Database Error: {err}")
        traceback.print_exc()
        return jsonify({'error': f'Database error: {str(err)}'}), 500
    
    except Exception as e:
        conn.rollback()
        print(f"\n❌ Unexpected Error: {e}")
        traceback.print_exc()
        return jsonify({'error': f'Server error: {str(e)}'}), 500
    
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()
        print("\n🔒 Database connection closed\n")
# ============================================================================
# END OF approve_grn FUNCTION
# ============================================================================

@grn_bp.route('/get_grns', methods=['GET'])
@jwt_required()
@role_required('admin', 'manager')
def get_grns():
    """Get all GRNs with complete details including payment information"""
    
    conn = get_db_connection()
    if not conn:
        return jsonify({'error': 'Database connection failed'}), 500
    
    cursor = conn.cursor(dictionary=True)
    
    try:
        cursor.execute("""
            SELECT 
                g.grn_id, g.grn_code, g.purchase_order_id, g.grn_date,
                g.status, g.total_items, g.subtotal, g.tax, g.discount, g.grand_total,
                g.order_tax, g.payment_status, g.payment_type, g.paid_amount, g.due_amount,
                g.note, g.invoice_number, g.invoice_date,
                g.vehicle_number, g.driver_name, g.driver_contact,
                s.supplier_name, w.warehouse_name, st.store_name,
                u.name as received_by_name,
                g.created_at, g.approved_at
            FROM grn g
            LEFT JOIN suppliers s ON g.supplier_id = s.id
            LEFT JOIN warehouses w ON g.warehouse_id = w.id
            LEFT JOIN stores st ON g.store_id = st.id
            LEFT JOIN users u ON g.received_by = u.id
            ORDER BY g.grn_id DESC
        """)
        
        grns = cursor.fetchall()
        
        # Get items for each GRN
        for grn in grns:
            cursor.execute("""
                SELECT 
                    gi.*, p.product_name, p.sku,
                    pv.variation_name, pv.variation_type,
                    pb.batch_number
                FROM grn_items gi
                LEFT JOIN products p ON gi.product_id = p.id
                LEFT JOIN product_variations pv ON gi.variation_id = pv.id
                LEFT JOIN product_batches pb ON gi.batch_id = pb.batch_id
                WHERE gi.grn_id = %s
            """, (grn['grn_id'],))
            
            grn['items'] = cursor.fetchall()
        
        return jsonify({
            'success': True,
            'data': grns,
            'count': len(grns)
        }), 200
    
    except Exception as e:
        print(f"❌ Error fetching GRNs: {str(e)}")
        traceback.print_exc()
        return jsonify({'error': str(e)}), 500
    
    finally:
        cursor.close()
        conn.close()


# ============================================================================
# COMPLETE & CORRECT GET_GRN FUNCTION - WITH OUR_PRICE (FIXED)
# ============================================================================
# This is a production-ready, tested function
# ✅ Returns all GRN details including our_price
# ✅ Formats dates for HTML inputs
# ✅ Includes warehouse, supplier, payment info
# ✅ Complete error handling
# ✅ FIXED: store_address column name issue
# ============================================================================

@grn_bp.route('/get_grn/<int:grn_id>', methods=['GET'])
@jwt_required()
@role_required('admin', 'manager')
def get_grn(grn_id):
    """
    ✅ GET GRN - COMPLETE & CORRECT VERSION WITH OUR_PRICE
    
    Returns complete GRN details including:
    - ✅ GRN header with all fields
    - ✅ Warehouse and supplier information
    - ✅ All pricing details (unit_price, batch_price, our_price) ✓ NEW
    - ✅ Payment information
    - ✅ Expiration dates for items
    - ✅ Activity log with all changes
    - ✅ Properly formatted dates for HTML inputs
    - ✅ Product and variation details
    - ✅ Batch information if approved
    
    Returns:
    - Complete GRN object with all related data
    - Items array with all pricing fields including our_price
    - Activity log with history
    - User names for received_by, created_by, approved_by
    - Formatted dates ready for HTML date inputs
    
    Response Format:
    {
      "success": true,
      "data": {
        "grn_id": 123,
        "grn_code": "GRN-2026-00001",
        "warehouse_name": "Main Warehouse",
        "supplier_name": "Supplier ABC",
        "payment_status": "Paid",
        "items": [
          {
            "grn_item_id": 1,
            "product_id": 10,
            "product_name": "Product Name",
            "unit_price": 50.00,
            "batch_price": 55.00,
            "our_price": 60.00,
            "expiration_date": "2026-12-31",
            ...
          }
        ],
        "activity_log": [
          {
            "action": "GRN Approved",
            "description": "...",
            "user_name": "Admin User",
            "created_at": "2026-02-12 10:30:00"
          }
        ]
      }
    }
    """
    
    conn = get_db_connection()
    if not conn:
        return jsonify({'error': 'Database connection failed'}), 500
    
    cursor = conn.cursor(dictionary=True)
    
    try:
        print(f"\n📦 Fetching GRN {grn_id}...")
        
        # ===========================
        # STEP 1: GET GRN HEADER
        # ===========================
        print(f"\n📝 Step 1: Fetching GRN header...")
        cursor.execute("""
            SELECT 
                g.*,
                s.supplier_name, s.supplier_email, s.supplier_contact, s.supplier_address,
                w.warehouse_name,
                st.store_name, st.address as store_address,
                u.name as received_by_name,
                u2.name as created_by_name,
                u3.name as approved_by_name
            FROM grn g
            LEFT JOIN suppliers s ON g.supplier_id = s.id
            LEFT JOIN warehouses w ON g.warehouse_id = w.id
            LEFT JOIN stores st ON g.store_id = st.id
            LEFT JOIN users u ON g.received_by = u.id
            LEFT JOIN users u2 ON g.created_by = u2.id
            LEFT JOIN users u3 ON g.approved_by = u3.id
            WHERE g.grn_id = %s
        """, (grn_id,))
        
        grn = cursor.fetchone()
        
        if not grn:
            print(f"❌ GRN {grn_id} not found")
            return jsonify({'error': 'GRN not found'}), 404
        
        print(f"  ✅ GRN {grn['grn_code']} found")
        print(f"     Status: {grn['status']}")
        print(f"     Grand Total: LKR {grn['grand_total']:.2f}")
        
        # ===========================
        # STEP 2: GET GRN ITEMS WITH OUR_PRICE
        # ===========================
        print(f"\n📝 Step 2: Fetching GRN items with our_price...")
        cursor.execute("""
            SELECT 
                gi.*,
                p.product_name, p.sku,
                pv.variation_name, pv.variation_type, pv.variation_sku,
                pb.batch_number, pb.batch_id
            FROM grn_items gi
            LEFT JOIN products p ON gi.product_id = p.id
            LEFT JOIN product_variations pv ON gi.variation_id = pv.id
            LEFT JOIN product_batches pb ON gi.batch_id = pb.batch_id
            WHERE gi.grn_id = %s
            ORDER BY gi.grn_item_id
        """, (grn_id,))
        
        items = cursor.fetchall()
        
        print(f"  ✅ Found {len(items)} items")
        
        items_with_our_price = 0
        items_with_expiration = 0
        
        # ===========================
        # STEP 3: FORMAT ITEM DATES & TRACK COUNTS
        # ===========================
        print(f"\n📝 Step 3: Formatting item dates and tracking our_price...")
        
        for item in items:
            # ✅ Format expiration_date
            if item.get('expiration_date'):
                if isinstance(item['expiration_date'], (datetime, date)):
                    item['expiration_date'] = item['expiration_date'].strftime('%Y-%m-%d')
                    items_with_expiration += 1
            
            # ✅ Track items with our_price
            if safe_float(item.get('our_price', 0)) > 0:
                items_with_our_price += 1
                print(f"  ✅ Item {item['grn_item_id']}: our_price = {item['our_price']}")
        
        print(f"  ✅ Items with our_price: {items_with_our_price}")
        print(f"  ✅ Items with expiration: {items_with_expiration}")
        
        grn['items'] = items
        
        # ===========================
        # STEP 4: GET ACTIVITY LOG
        # ===========================
        print(f"\n📝 Step 4: Fetching activity log...")
        cursor.execute("""
            SELECT 
                al.*,
                u.name as user_name
            FROM grn_activity_log al
            LEFT JOIN users u ON al.user_id = u.id
            WHERE al.grn_id = %s
            ORDER BY al.created_at DESC
        """, (grn_id,))
        
        activity_log = cursor.fetchall()
        print(f"  ✅ Found {len(activity_log)} activity entries")
        
        grn['activity_log'] = activity_log
        
        # ===========================
        # STEP 5: FORMAT HEADER DATES FOR HTML INPUTS
        # ===========================
        print(f"\n📝 Step 5: Formatting header dates...")
        
        # Format grn_date (YYYY-MM-DD for HTML date input)
        if grn.get('grn_date'):
            if isinstance(grn['grn_date'], (datetime, date)):
                grn['grn_date'] = grn['grn_date'].strftime('%Y-%m-%d')
                print(f"  ✅ grn_date formatted: {grn['grn_date']}")
        
        # Format invoice_date (YYYY-MM-DD for HTML date input)
        if grn.get('invoice_date'):
            if isinstance(grn['invoice_date'], (datetime, date)):
                grn['invoice_date'] = grn['invoice_date'].strftime('%Y-%m-%d')
                print(f"  ✅ invoice_date formatted: {grn['invoice_date']}")
        
        # Format created_at for display (YYYY-MM-DD HH:MM:SS)
        if grn.get('created_at'):
            if isinstance(grn['created_at'], datetime):
                grn['created_at'] = grn['created_at'].strftime('%Y-%m-%d %H:%M:%S')
        
        # Format updated_at for display (YYYY-MM-DD HH:MM:SS)
        if grn.get('updated_at'):
            if isinstance(grn['updated_at'], datetime):
                grn['updated_at'] = grn['updated_at'].strftime('%Y-%m-%d %H:%M:%S')
        
        # Format approved_at if present (YYYY-MM-DD HH:MM:SS)
        if grn.get('approved_at'):
            if isinstance(grn['approved_at'], datetime):
                grn['approved_at'] = grn['approved_at'].strftime('%Y-%m-%d %H:%M:%S')
        
        # ===========================
        # STEP 6: CALCULATE SUMMARY STATS
        # ===========================
        print(f"\n📝 Step 6: Calculating summary statistics...")
        
        total_ordered = sum(safe_float(i.get('ordered_quantity', 0)) for i in items)
        total_received = sum(safe_float(i.get('received_quantity', 0)) for i in items)
        total_rejected = sum(safe_float(i.get('rejected_quantity', 0)) for i in items)
        total_accepted = total_received - total_rejected
        
        grn['summary'] = {
            'total_items': len(items),
            'items_with_our_price': items_with_our_price,
            'items_with_expiration': items_with_expiration,
            'total_ordered': total_ordered,
            'total_received': total_received,
            'total_rejected': total_rejected,
            'total_accepted': total_accepted,
            'total_activities': len(activity_log)
        }
        
        print(f"  ✅ Summary calculated:")
        print(f"     Total Items: {len(items)}")
        print(f"     Total Ordered: {total_ordered}")
        print(f"     Total Received: {total_received}")
        print(f"     Total Rejected: {total_rejected}")
        print(f"     Total Accepted: {total_accepted}")
        
        print(f"\n✅ GRN {grn['grn_code']} fetched successfully")
        print(f"   Code: {grn['grn_code']}")
        print(f"   Status: {grn['status']}")
        print(f"   Items: {len(items)}")
        print(f"   Items with our_price: {items_with_our_price}")
        print(f"   Items with expiration: {items_with_expiration}")
        print(f"   Total: LKR {grn['grand_total']:.2f}")
        
        return jsonify({
            'success': True,
            'data': grn
        }), 200
    
    except Exception as e:
        print(f"❌ Error fetching GRN {grn_id}: {str(e)}")
        traceback.print_exc()
        return jsonify({'error': str(e)}), 500
    
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()
        print("\n🔒 Database connection closed\n")

# ============================================================================
# END OF get_grn FUNCTION
# ============================================================================


@grn_bp.route('/cancel_grn/<int:grn_id>', methods=['POST'])
@jwt_required()
@role_required('admin', 'manager')
def cancel_grn(grn_id):
    """Cancel a pending GRN"""
    
    conn = get_db_connection()
    if not conn:
        return jsonify({'error': 'Database connection failed'}), 500
    
    cursor = conn.cursor(dictionary=True)
    
    try:
        data = request.get_json() or {}
        reason = data.get('reason', '').strip()
        
        conn.start_transaction()
        
        # Get GRN
        cursor.execute("SELECT * FROM grn WHERE grn_id = %s", (grn_id,))
        grn = cursor.fetchone()
        
        if not grn:
            raise ValueError(f"GRN {grn_id} not found")
        
        if grn['status'] == 'completed':
            raise ValueError("Cannot cancel an approved GRN")
        
        if grn['status'] == 'cancelled':
            raise ValueError("GRN is already cancelled")
        
        # Update GRN status
        cursor.execute("""
            UPDATE grn
            SET status = 'cancelled'
            WHERE grn_id = %s
        """, (grn_id,))
        
        # Log activity
        current_user = get_jwt_identity()
        cursor.execute("""
            INSERT INTO grn_activity_log (
                grn_id, user_id, action, description, created_at
            ) VALUES (
                %s, %s, 'GRN Cancelled', %s, NOW()
            )
        """, (grn_id, current_user, f"Reason: {reason}" if reason else "GRN cancelled"))
        
        # Update PO GRN status
        cursor.execute("""
            SELECT 
                SUM(oi.quantity) as total_ordered,
                COALESCE((
                    SELECT SUM(gi.received_quantity - gi.rejected_quantity)
                    FROM grn g2
                    INNER JOIN grn_items gi ON g2.grn_id = gi.grn_id
                    WHERE g2.purchase_order_id = %s
                    AND g2.status != 'cancelled'
                ), 0) as total_received
            FROM order_items oi
            WHERE oi.order_id = %s
        """, (grn['purchase_order_id'], grn['purchase_order_id']))
        
        po_qty = cursor.fetchone()
        total_ordered = safe_float(po_qty['total_ordered'])
        total_received = safe_float(po_qty['total_received'])
        
        if total_received == 0:
            new_grn_status = 'not_received'
        elif total_received < total_ordered:
            new_grn_status = 'partial'
        else:
            new_grn_status = 'fully_received'
        
        cursor.execute("""
            UPDATE purchase_orders
            SET grn_status = %s
            WHERE order_id = %s
        """, (new_grn_status, grn['purchase_order_id']))
        
        conn.commit()
        
        return jsonify({
            'success': True,
            'message': f'GRN {grn["grn_code"]} cancelled successfully'
        }), 200
    
    except ValueError as err:
        conn.rollback()
        return jsonify({'error': str(err)}), 400
    
    except Exception as e:
        conn.rollback()
        print(f"❌ Error cancelling GRN: {str(e)}")
        traceback.print_exc()
        return jsonify({'error': str(e)}), 500
    
    finally:
        cursor.close()
        conn.close()


@grn_bp.route('/delete_grn/<int:grn_id>', methods=['DELETE'])
@jwt_required()
@role_required('admin', 'manager')
def delete_grn(grn_id):
    """
    ✅ DELETE GRN - UPDATED VERSION
    
    Rules:
    - PENDING GRNs   → Any admin/manager can delete (reason required)
    - COMPLETED GRNs → Admin ONLY can delete (reason required + stock reversed)
    - CANCELLED GRNs → Cannot delete (already inactive)
    
    For COMPLETED GRNs:
    - Reverses warehouse_stock records linked to batches
    - Deletes product_batches created during approval
    - Updates PO grn_status accordingly
    - Logs full deletion reason in activity log
    """

    conn = get_db_connection()
    if not conn:
        return jsonify({'error': 'Database connection failed'}), 500

    cursor = conn.cursor(dictionary=True)

    try:
        data = request.get_json() or {}
        reason = data.get('reason', '').strip()

        # ✅ Reason is always required
        if not reason:
            return jsonify({'error': 'Reason for deletion is required'}), 400

        conn.start_transaction()

        # ── Step 1: Get GRN ──────────────────────────────────────────────
        cursor.execute("SELECT * FROM grn WHERE grn_id = %s", (grn_id,))
        grn = cursor.fetchone()

        if not grn:
            raise ValueError(f"GRN {grn_id} not found")

        grn_code   = grn['grn_code']
        grn_status = grn['status']

        print(f"\n🗑️  DELETE GRN: {grn_code} | Status: {grn_status}")
        print(f"   Reason: {reason}")

        # ── Step 2: Permission & status checks ───────────────────────────
        if grn_status == 'cancelled':
            raise ValueError("Cannot delete a cancelled GRN.")

        current_user = get_jwt_identity()

        # Only admin can delete completed GRNs
        if grn_status == 'completed':
            # Get role of current user
            cursor.execute("SELECT role FROM users WHERE id = %s", (current_user,))
            user_row = cursor.fetchone()
            user_role = user_row['role'] if user_row else None

            if user_role != 'admin':
                raise ValueError(
                    "Only administrators can delete completed GRNs. "
                    "Please contact an admin."
                )

        is_completed = grn_status == 'completed'

        # ── Step 3: Reverse stock if COMPLETED ───────────────────────────
        if is_completed:
            print(f"\n🔄 REVERSING stock for completed GRN {grn_code}...")

            cursor.execute("""
                SELECT * FROM grn_items WHERE grn_id = %s
            """, (grn_id,))
            grn_items = cursor.fetchall()

            stock_reversed = 0
            batches_deleted = 0

            for idx, item in enumerate(grn_items, 1):
                batch_id = item.get('batch_id')

                if not batch_id:
                    print(f"  ⚠️  Item {idx}: No batch_id, skipping stock reversal")
                    continue

                accepted_qty = (
                    safe_float(item['received_quantity']) -
                    safe_float(item['rejected_quantity'])
                )

                print(f"\n  📦 Reversing Item {idx}:")
                print(f"     Product ID : {item['product_id']}")
                print(f"     Batch ID   : {batch_id}")
                print(f"     Qty removed: {accepted_qty}")

                # Delete warehouse_stock for this batch
                cursor.execute("""
                    DELETE FROM warehouse_stock
                    WHERE batch_id = %s
                    AND store_id   = %s
                    AND warehouse_id = %s
                """, (batch_id, grn['store_id'], grn['warehouse_id']))

                deleted_stock = cursor.rowcount
                stock_reversed += deleted_stock
                print(f"     ✅ warehouse_stock deleted: {deleted_stock} row(s)")

                # Delete product_batch
                cursor.execute("""
                    DELETE FROM product_batches
                    WHERE batch_id = %s
                """, (batch_id,))

                deleted_batch = cursor.rowcount
                batches_deleted += deleted_batch
                print(f"     ✅ product_batch deleted  : {deleted_batch} row(s)")

            print(f"\n  ✅ Stock reversal complete")
            print(f"     warehouse_stock rows deleted : {stock_reversed}")
            print(f"     product_batches rows deleted : {batches_deleted}")

        # ── Step 4: Log deletion BEFORE removing records ─────────────────
        cursor.execute("""
            INSERT INTO grn_activity_log (
                grn_id, user_id, action, description, created_at
            ) VALUES (%s, %s, %s, %s, NOW())
        """, (
            grn_id, current_user,
            'GRN Deleted (Completed)' if is_completed else 'GRN Deleted',
            f"GRN {grn_code} deleted. Status was: {grn_status}. "
            f"{'Stock reversed. ' if is_completed else ''}"
            f"Reason: {reason}"
        ))

        # ── Step 5: Delete GRN items ──────────────────────────────────────
        cursor.execute("DELETE FROM grn_items WHERE grn_id = %s", (grn_id,))
        print(f"  ✅ grn_items deleted")

        # ── Step 6: Delete activity log ───────────────────────────────────
        cursor.execute("DELETE FROM grn_activity_log WHERE grn_id = %s", (grn_id,))
        print(f"  ✅ grn_activity_log deleted")

        # ── Step 7: Delete GRN header ─────────────────────────────────────
        cursor.execute("DELETE FROM grn WHERE grn_id = %s", (grn_id,))
        print(f"  ✅ grn record deleted")

        # ── Step 8: Update PO grn_status ─────────────────────────────────
        cursor.execute("""
            SELECT
                SUM(oi.quantity) AS total_ordered,
                COALESCE((
                    SELECT SUM(gi.received_quantity - gi.rejected_quantity)
                    FROM grn g2
                    INNER JOIN grn_items gi ON g2.grn_id = gi.grn_id
                    WHERE g2.purchase_order_id = %s
                    AND g2.status != 'cancelled'
                ), 0) AS total_received
            FROM order_items oi
            WHERE oi.order_id = %s
        """, (grn['purchase_order_id'], grn['purchase_order_id']))

        po_qty         = cursor.fetchone()
        total_ordered  = safe_float(po_qty['total_ordered'])
        total_received = safe_float(po_qty['total_received'])

        if total_received == 0:
            new_grn_status = 'not_received'
        elif total_received < total_ordered:
            new_grn_status = 'partial'
        else:
            new_grn_status = 'fully_received'

        cursor.execute("""
            UPDATE purchase_orders
            SET grn_status = %s
            WHERE order_id = %s
        """, (new_grn_status, grn['purchase_order_id']))

        print(f"  ✅ PO grn_status updated: {new_grn_status}")

        # ── Commit ────────────────────────────────────────────────────────
        conn.commit()

        print(f"\n✅ GRN {grn_code} deleted successfully")
        if is_completed:
            print(f"   ⚠️  NOTE: Stock was reversed. Manual inventory check recommended.")

        return jsonify({
            'success': True,
            'message': (
                f'Completed GRN {grn_code} deleted. Stock has been reversed. '
                f'Please verify inventory manually.'
                if is_completed
                else f'GRN {grn_code} deleted successfully.'
            ),
            'data': {
                'grn_code':      grn_code,
                'was_completed': is_completed,
                'stock_reversed': is_completed
            }
        }), 200

    except ValueError as err:
        conn.rollback()
        print(f"❌ Validation Error: {err}")
        return jsonify({'error': str(err)}), 400

    except mysql.connector.Error as err:
        conn.rollback()
        print(f"❌ Database Error: {err}")
        traceback.print_exc()
        return jsonify({'error': f'Database error: {str(err)}'}), 500

    except Exception as e:
        conn.rollback()
        print(f"❌ Unexpected Error: {e}")
        traceback.print_exc()
        return jsonify({'error': f'Server error: {str(e)}'}), 500

    finally:
        cursor.close()
        conn.close()
        print("\n🔒 Database connection closed\n")
        
@grn_bp.route('/get_grn_attachments/<int:grn_id>', methods=['GET'])
@jwt_required()
@role_required('admin', 'manager')
def get_grn_attachments(grn_id):
    """Get all attachments for a GRN"""
    
    conn = get_db_connection()
    if not conn:
        return jsonify({'error': 'Database connection failed'}), 500
    
    cursor = conn.cursor(dictionary=True)
    
    try:
        cursor.execute("""
            SELECT 
                ga.*,
                u.name as uploaded_by_name
            FROM grn_attachments ga
            LEFT JOIN users u ON ga.uploaded_by = u.id
            WHERE ga.grn_id = %s
            ORDER BY ga.uploaded_at DESC
        """, (grn_id,))
        
        attachments = cursor.fetchall()
        
        # ✅ FORMAT DATETIME OBJECTS FOR JSON SERIALIZATION
        for attachment in attachments:
            if attachment.get('uploaded_at'):
                # Convert datetime to string format
                if isinstance(attachment['uploaded_at'], datetime):
                    attachment['uploaded_at'] = attachment['uploaded_at'].strftime('%Y-%m-%d %H:%M:%S')
        
        return jsonify({
            'success': True,
            'data': attachments,
            'count': len(attachments)
        }), 200
        
    except Exception as e:
        print(f"❌ Error fetching attachments: {str(e)}")
        import traceback
        traceback.print_exc()
        return jsonify({'error': str(e)}), 500
    
    finally:
        cursor.close()
        conn.close()

@grn_bp.route('/upload_grn_attachments', methods=['POST'])
@jwt_required()
@role_required('admin', 'manager')
def upload_grn_attachments():
    """Upload attachments for GRN"""
    
    if 'files' not in request.files:
        return jsonify({'error': 'No files provided'}), 400
    
    files = request.files.getlist('files')
    grn_id = request.form.get('grn_id')
    attachment_type = request.form.get('attachment_type', 'other')
    
    if not grn_id:
        return jsonify({'error': 'GRN ID is required'}), 400
    
    current_user = get_jwt_identity()
    
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    UPLOAD_FOLDER = 'uploads/grn_attachments'
    ALLOWED_EXTENSIONS = {'pdf', 'jpg', 'jpeg', 'png', 'doc', 'docx'}
    
    def allowed_file(filename):
        return '.' in filename and filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS
    
    uploaded_files = []
    
    try:
        # Verify GRN exists
        cursor.execute("SELECT grn_id FROM grn WHERE grn_id = %s", (grn_id,))
        if not cursor.fetchone():
            return jsonify({'error': 'GRN not found'}), 404
        
        # Create upload directory
        grn_upload_dir = os.path.join(UPLOAD_FOLDER, str(grn_id))
        os.makedirs(grn_upload_dir, exist_ok=True)
        
        for file in files:
            if file and allowed_file(file.filename):
                # Secure filename
                from werkzeug.utils import secure_filename
                original_filename = secure_filename(file.filename)
                
                # Generate unique filename
                timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
                filename = f"{timestamp}_{original_filename}"
                
                # Save file
                file_path = os.path.join(grn_upload_dir, filename)
                file.save(file_path)
                
                # Get file size and type
                file_size = os.path.getsize(file_path)
                file_type = file.content_type
                
                # Insert into database
                cursor.execute("""
                    INSERT INTO grn_attachments (
                        grn_id, file_name, file_path, file_type, file_size,
                        attachment_type, uploaded_by, uploaded_at
                    ) VALUES (%s, %s, %s, %s, %s, %s, %s, NOW())
                """, (
                    grn_id, original_filename, file_path, file_type, file_size,
                    attachment_type, current_user
                ))
                
                attachment_id = cursor.lastrowid
                
                uploaded_files.append({
                    'attachment_id': attachment_id,
                    'file_name': original_filename,
                    'file_size': file_size,
                    'file_type': file_type,
                    'attachment_type': attachment_type
                })
        
        conn.commit()
        
        return jsonify({
            'success': True,
            'message': f'{len(uploaded_files)} file(s) uploaded successfully',
            'files': uploaded_files
        }), 200
        
    except Exception as e:
        conn.rollback()
        print(f"❌ Upload error: {str(e)}")
        return jsonify({'error': str(e)}), 500
    
    finally:
        cursor.close()
        conn.close()


@grn_bp.route('/view_grn_attachment/<int:attachment_id>', methods=['GET'])
def view_grn_attachment(attachment_id):
    """
    ✅ FIXED: View/open attachment in browser - WITH TOKEN AUTH
    """
    
    print(f"\n👁️ VIEW ATTACHMENT REQUEST: ID {attachment_id}")
    
    # Get token from query parameter OR header
    token = request.args.get('token') or request.headers.get('Authorization', '').replace('Bearer ', '')
    
    if not token:
        print("❌ No token provided")
        return jsonify({
            'error': 'authorization_required',
            'message': 'Missing authentication token'
        }), 401
    
    # Verify token manually
    try:
        from flask_jwt_extended import decode_token
        decoded = decode_token(token)
        user_id = decoded.get('sub')
        user_role = decoded.get('role')
        
        print(f"✅ Token valid - User: {user_id}, Role: {user_role}")
        
        # Check role
        if user_role not in ['admin', 'manager']:
            print(f"❌ Unauthorized role: {user_role}")
            return jsonify({
                'error': 'forbidden',
                'message': 'Insufficient permissions'
            }), 403
            
    except Exception as e:
        print(f"❌ Token verification failed: {str(e)}")
        return jsonify({
            'error': 'invalid_token',
            'message': 'Invalid or expired token'
        }), 401
    
    # Get attachment
    conn = get_db_connection()
    if not conn:
        return jsonify({'error': 'Database connection failed'}), 500
    
    cursor = conn.cursor(dictionary=True)
    
    try:
        cursor.execute("""
            SELECT * FROM grn_attachments 
            WHERE attachment_id = %s
        """, (attachment_id,))
        
        attachment = cursor.fetchone()
        
        if not attachment:
            print(f"❌ Attachment {attachment_id} not found")
            return jsonify({'error': 'Attachment not found'}), 404
        
        file_path = attachment['file_path']
        print(f"📄 File path: {file_path}")
        
        if not os.path.exists(file_path):
            print(f"❌ File not found on disk: {file_path}")
            return jsonify({'error': 'File not found on server'}), 404
        
        print(f"✅ Sending file: {attachment['file_name']}")
        
        return send_file(
            file_path,
            mimetype=attachment['file_type'],
            as_attachment=False,  # Display in browser
            download_name=attachment['file_name']
        )
        
    except Exception as e:
        print(f"❌ Error: {str(e)}")
        import traceback
        traceback.print_exc()
        return jsonify({'error': str(e)}), 500
    
    finally:
        cursor.close()
        conn.close()


@grn_bp.route('/download_grn_attachment/<int:attachment_id>', methods=['GET'])
@jwt_required()
@role_required('admin', 'manager')
def download_grn_attachment(attachment_id):
    """Download attachment"""
    
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    try:
        cursor.execute("""
            SELECT * FROM grn_attachments 
            WHERE attachment_id = %s
        """, (attachment_id,))
        
        attachment = cursor.fetchone()
        
        if not attachment:
            return jsonify({'error': 'Attachment not found'}), 404
        
        from flask import send_file
        
        if not os.path.exists(attachment['file_path']):
            return jsonify({'error': 'File not found on disk'}), 404
        
        return send_file(
            attachment['file_path'],
            mimetype=attachment['file_type'],
            as_attachment=True,  # Force download
            download_name=attachment['file_name']
        )
        
    except Exception as e:
        return jsonify({'error': str(e)}), 500
    
    finally:
        cursor.close()
        conn.close()


@grn_bp.route('/delete_grn_attachment/<int:attachment_id>', methods=['DELETE'])
@jwt_required()
@role_required('admin', 'manager')
def delete_grn_attachment(attachment_id):
    """Delete attachment"""
    
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    try:
        # Get attachment info
        cursor.execute("""
            SELECT * FROM grn_attachments 
            WHERE attachment_id = %s
        """, (attachment_id,))
        
        attachment = cursor.fetchone()
        
        if not attachment:
            return jsonify({'error': 'Attachment not found'}), 404
        
        # Delete file from disk
        if os.path.exists(attachment['file_path']):
            os.remove(attachment['file_path'])
        
        # Delete from database
        cursor.execute("""
            DELETE FROM grn_attachments 
            WHERE attachment_id = %s
        """, (attachment_id,))
        
        conn.commit()
        
        return jsonify({
            'success': True,
            'message': 'Attachment deleted successfully'
        }), 200
        
    except Exception as e:
        conn.rollback()
        return jsonify({'error': str(e)}), 500
    
    finally:
        cursor.close()
        conn.close()
        
@grn_bp.route('/get_grn_by_po/<int:purchase_order_id>', methods=['GET'])
@jwt_required()
@role_required('admin', 'manager')
def get_grn_by_po(purchase_order_id):
    """
    Get all GRNs for a specific purchase order
    
    ✅ Used by continue_grn.html to calculate:
    - Total already received quantity
    - Remaining quantity per item
    
    Returns:
    {
        "success": True,
        "grns": [
            {
                "grn_id": 1,
                "grn_code": "GRN-001",
                "status": "completed",
                "items": [...]
            }
        ],
        "count": 2
    }
    """
    
    conn = get_db_connection()
    if not conn:
        return jsonify({'error': 'Database connection failed'}), 500
    
    cursor = conn.cursor(dictionary=True)
    
    try:
        # ===========================
        # Step 1: Get all GRNs for this PO
        # ===========================
        cursor.execute("""
            SELECT 
                grn_id, grn_code, purchase_order_id, status,
                grn_date, received_by, invoice_number, invoice_date,
                vehicle_number, driver_name, driver_contact,
                total_items, subtotal, tax, discount, grand_total,
                order_tax, payment_status, payment_type, paid_amount, due_amount,
                note, approved_by, approved_at, created_at, updated_at
            FROM grn 
            WHERE purchase_order_id = %s
            ORDER BY grn_id DESC
        """, (purchase_order_id,))
        
        grns = cursor.fetchall()
        
        if not grns:
            print(f"ℹ️ No GRNs found for purchase order {purchase_order_id}")
            return jsonify({
                'success': True,
                'grns': [],
                'count': 0
            }), 200
        
        # ===========================
        # Step 2: Get items for each GRN
        # ===========================
        for grn in grns:
            cursor.execute("""
                SELECT 
                    grn_item_id, grn_id, product_id, variation_id,
                    ordered_quantity, received_quantity, rejected_quantity,
                    unit_price, batch_price, discount, discount_type,
                    tax, tax_type, net_unit_cost, subtotal,
                    quality_check, unit, note, batch_id,
                    created_at, updated_at
                FROM grn_items 
                WHERE grn_id = %s
                ORDER BY grn_item_id
            """, (grn['grn_id'],))
            
            items = cursor.fetchall()
            
            # Convert Decimal to float for JSON serialization
            grn['items'] = []
            for item in items:
                grn['items'].append({
                    'grn_item_id': item['grn_item_id'],
                    'grn_id': item['grn_id'],
                    'product_id': item['product_id'],
                    'variation_id': item['variation_id'],
                    'ordered_quantity': float(item['ordered_quantity'] or 0),
                    'received_quantity': float(item['received_quantity'] or 0),
                    'rejected_quantity': float(item['rejected_quantity'] or 0),
                    'unit_price': float(item['unit_price'] or 0),
                    'batch_price': float(item['batch_price'] or 0),
                    'discount': float(item['discount'] or 0),
                    'discount_type': item['discount_type'],
                    'tax': float(item['tax'] or 0),
                    'tax_type': item['tax_type'],
                    'net_unit_cost': float(item['net_unit_cost'] or 0),
                    'subtotal': float(item['subtotal'] or 0),
                    'quality_check': item['quality_check'],
                    'unit': item['unit'],
                    'note': item['note'],
                    'batch_id': item['batch_id'],
                    'created_at': item['created_at'].strftime('%Y-%m-%d %H:%M:%S') if item['created_at'] else None,
                    'updated_at': item['updated_at'].strftime('%Y-%m-%d %H:%M:%S') if item['updated_at'] else None
                })
            
            # Convert GRN dates to strings
            if grn['grn_date']:
                grn['grn_date'] = grn['grn_date'].strftime('%Y-%m-%d')
            if grn['invoice_date']:
                grn['invoice_date'] = grn['invoice_date'].strftime('%Y-%m-%d')
            if grn['approved_at']:
                grn['approved_at'] = grn['approved_at'].strftime('%Y-%m-%d %H:%M:%S')
            if grn['created_at']:
                grn['created_at'] = grn['created_at'].strftime('%Y-%m-%d %H:%M:%S')
            if grn['updated_at']:
                grn['updated_at'] = grn['updated_at'].strftime('%Y-%m-%d %H:%M:%S')
            
            # Convert numeric fields
            grn['total_items'] = int(grn['total_items'] or 0)
            grn['subtotal'] = float(grn['subtotal'] or 0)
            grn['tax'] = float(grn['tax'] or 0)
            grn['discount'] = float(grn['discount'] or 0)
            grn['grand_total'] = float(grn['grand_total'] or 0)
            grn['order_tax'] = float(grn['order_tax'] or 0)
            grn['paid_amount'] = float(grn['paid_amount'] or 0)
            grn['due_amount'] = float(grn['due_amount'] or 0)
        
        print(f"✅ Found {len(grns)} GRN(s) for purchase order {purchase_order_id}")
        for grn in grns:
            print(f"   - GRN {grn['grn_code']}: {grn['status']}, {len(grn['items'])} items")
        
        return jsonify({
            'success': True,
            'grns': grns,
            'count': len(grns)
        }), 200
    
    except Exception as e:
        print(f"❌ Error in get_grn_by_po: {str(e)}")
        import traceback
        traceback.print_exc()
        return jsonify({'error': str(e)}), 500
    
    finally:
        cursor.close()
        conn.close()
        
        
