from flask import Blueprint, request, jsonify, send_from_directory
from flask_jwt_extended import jwt_required
from db.db import get_db_connection
from config.auth import role_required
from datetime import datetime
from datetime import timedelta
from decimal import Decimal
from datetime import date
import traceback
from mysql.connector import Error
import json

report_bp = Blueprint('report', __name__)

@report_bp.route('/cash_today_summary', methods=['GET'])
@jwt_required()
@role_required('admin','cashier')
def cash_today_summary():
    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        today = date.today()

        # Doctor Fees (from received invoices)
        cursor.execute("""
            SELECT SUM(ds.total)
            FROM doctor_charges_sale ds
            JOIN invoice_sale i ON ds.invoice_id = i.invoice_id
            WHERE DATE(i.sale_date) = %s AND i.status = 'received'
        """, (today,))
        doctor_fees = cursor.fetchone()[0] or Decimal(0)

        # Medicine Fees (from received invoices)
        cursor.execute("""
            SELECT SUM(sp.total)
            FROM sale_products sp
            JOIN invoice_sale i ON sp.invoice_id = i.invoice_id
            WHERE DATE(i.sale_date) = %s AND i.status = 'received'
        """, (today,))
        medicine_fees = cursor.fetchone()[0] or Decimal(0)

        # Total Sale
        total_sale = float(doctor_fees) + float(medicine_fees)

        # Today's Expenses
        cursor.execute("""
            SELECT SUM(amount)
            FROM expenses
            WHERE DATE(date) = %s
        """, (today,))
        total_expense = cursor.fetchone()[0] or Decimal(0)

        # Cash Payment (received invoices only)
        cursor.execute("""
            SELECT SUM(invoiceTotal)
            FROM invoice_sale
            WHERE payment_method = 'paid' AND DATE(sale_date) = %s AND status = 'received'
        """, (today,))
        cash_payment_total = cursor.fetchone()[0] or Decimal(0)

        # Today's Return Amount from sale_return table
        cursor.execute("""
            SELECT SUM(total_return_amount)
            FROM sale_return
            WHERE DATE(return_date) = %s
        """, (today,))
        total_return_amount = cursor.fetchone()[0] or Decimal(0)

        # Final Total Cash = Sales - Expenses - Returns
        total_cash = total_sale - float(total_expense) - float(total_return_amount)

        summary = {
            "date": today.strftime('%Y-%m-%d'),
            "doctor_fees": round(float(doctor_fees), 2),
            "medicine_fees": round(float(medicine_fees), 2),
            "total_sale": round(total_sale, 2),
            "cash_payment": round(float(cash_payment_total), 2),
            "total_expense": round(float(total_expense), 2),
            "total_return_amount": round(float(total_return_amount), 2),
            "total_cash": round(total_cash, 2)
        }

        cursor.close()
        conn.close()
        return jsonify(summary), 200

    except Exception as e:
        return jsonify({"error": str(e)}), 500


@report_bp.route('/get_close_cash_summary', methods=['GET'])
@jwt_required()
@role_required('admin','cashier')
def get_close_cash_summary():
    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        # Doctor fees
        cursor.execute("SELECT SUM(ds.total) FROM doctor_charges_sale ds")
        doctor_fees = cursor.fetchone()[0] or Decimal(0)

        # Medicine fees
        cursor.execute("SELECT SUM(sp.total) FROM sale_products sp")
        medicine_fees = cursor.fetchone()[0] or Decimal(0)

        # Total sale
        total_sale = float(doctor_fees) + float(medicine_fees)

        # Expenses
        cursor.execute("SELECT SUM(amount) FROM expenses")
        total_expense = cursor.fetchone()[0] or Decimal(0)

        # Total Cash
        total_cash = total_sale - float(total_expense)

        # Paid invoices
        cursor.execute("""
            SELECT SUM(invoiceTotal) FROM invoice_sale 
            WHERE payment_method = 'paid'
        """)
        cash_payment_total = cursor.fetchone()[0] or Decimal(0)

        # Register closed flag
        register_closed = total_cash < 1

        summary = {
            "doctor_fees": float(doctor_fees),
            "medicine_fees": float(medicine_fees),
            "total_sale": float(total_sale),
            "total_expense": float(total_expense),
            "total_cash": float(total_cash),
            "cash_payment_total": float(cash_payment_total),
            "register_closed": register_closed
        }

        cursor.close()
        conn.close()
        return jsonify(summary), 200

    except Exception as e:
        return jsonify({"error": str(e)}), 500    


# ✅ CORRECTED: Using new schema columns
@report_bp.route('/get_register_status', methods=['GET'])
@jwt_required()
@role_required('admin','cashier')
def get_register_status():
    user_id = request.args.get('user_id')
    conn = get_db_connection()
    cursor = conn.cursor()
    
    # ✅ FIXED: opening_cash instead of cash_in_hand
    cursor.execute("""
        SELECT id, login_time, opening_cash
        FROM close_register_logs
        WHERE user_id = %s AND status = 'open'
        ORDER BY id DESC LIMIT 1
    """, (user_id,))
    
    row = cursor.fetchone()
    cursor.close()
    conn.close()

    if row:
        return jsonify({
            'success': True,
            'data': {
                'register_id': row[0],
                'login_time': row[1].strftime('%Y-%m-%d %H:%M:%S'),
                'opening_cash': float(row[2])  # ✅ Changed from cash_in_hand
            }
        })
    else:
        return jsonify({'success': False, 'message': 'No open register found'})


# ✅ CORRECTED: Using new cash_transactions table
@report_bp.route('/add_cash_in_hand', methods=['POST'])
@jwt_required()
@role_required('admin', 'cashier')
def add_cash_in_hand():
    try:
        data = request.json
        user_id = data.get('user_id')
        amount = data.get('cash_in_hand')  # amount to add
        transaction_type = data.get('transaction_type', 'add')  # 'add' or 'remove'
        reason = data.get('reason')
        note = data.get('note')

        if user_id is None or amount is None:
            return jsonify({'success': False, 'message': 'Missing required fields'}), 400

        conn = get_db_connection()
        cursor = conn.cursor()

        # ✅ Check if register is open
        cursor.execute("""
            SELECT id, store_id, opening_cash, total_cash_added, total_cash_removed
            FROM close_register_logs 
            WHERE user_id=%s AND status='open' 
            ORDER BY id DESC LIMIT 1
        """, (user_id,))
        
        register = cursor.fetchone()

        if not register:
            cursor.close()
            conn.close()
            return jsonify({
                'success': False, 
                'message': 'No open register found. Please open register first.'
            }), 400

        register_id = register[0]
        store_id = register[1]
        total_cash_added = float(register[3] or 0)
        total_cash_removed = float(register[4] or 0)

        # ✅ Insert into cash_transactions table
        cursor.execute("""
            INSERT INTO cash_transactions (
                register_log_id, user_id, store_id, 
                transaction_type, amount, reason, note, created_by
            )
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
        """, (register_id, user_id, store_id, transaction_type, amount, reason, note, user_id))

        # ✅ Update close_register_logs totals
        if transaction_type == 'add':
            new_total_added = total_cash_added + float(amount)
            cursor.execute("""
                UPDATE close_register_logs 
                SET total_cash_added = %s 
                WHERE id = %s
            """, (new_total_added, register_id))
        elif transaction_type == 'remove':
            new_total_removed = total_cash_removed + float(amount)
            cursor.execute("""
                UPDATE close_register_logs 
                SET total_cash_removed = %s 
                WHERE id = %s
            """, (new_total_removed, register_id))

        conn.commit()
        cursor.close()
        conn.close()

        return jsonify({
            'success': True,
            'message': f'Cash {transaction_type} recorded successfully'
        }), 200

    except Exception as e:
        return jsonify({'success': False, 'message': str(e)}), 500


# ✅ CORRECTED: Using new schema columns
@report_bp.route('/open_register', methods=['POST'])
@jwt_required()
@role_required('admin','cashier')
def open_register():
    try:
        data = request.json
        user_id = data.get('user_id')
        opening_cash = data.get('cash_in_hand')  # ✅ Will be stored as opening_cash
        
        if not user_id or opening_cash is None:
            return jsonify({
                'success': False, 
                'message': 'User ID and opening cash are required'
            }), 400

        conn = get_db_connection()
        if not conn:
            return jsonify({'success': False, 'message': 'Database connection failed'}), 500
            
        cursor = conn.cursor(dictionary=True)

        # Get user's store_id
        cursor.execute("SELECT store_id FROM users WHERE id = %s", (user_id,))
        user = cursor.fetchone()
        
        if not user:
            cursor.close()
            conn.close()
            return jsonify({'success': False, 'message': 'User not found'}), 404
            
        store_id = user['store_id']
        
        if not store_id:
            cursor.close()
            conn.close()
            return jsonify({
                'success': False, 
                'message': 'User is not assigned to any store'
            }), 400

        # Check if already an open register
        cursor.execute("""
            SELECT id FROM close_register_logs 
            WHERE user_id = %s AND status = 'open' 
            ORDER BY id DESC LIMIT 1
        """, (user_id,))
        
        existing = cursor.fetchone()
        
        if existing:
            cursor.close()
            conn.close()
            return jsonify({
                'success': False, 
                'message': 'Register already open for this user'
            }), 409

        # ✅ Insert with new schema columns
        cursor.execute("""
            INSERT INTO close_register_logs (
                store_id, 
                user_id, 
                login_time, 
                opening_cash,
                opening_balance,
                status
            )
            VALUES (%s, %s, %s, %s, %s, 'open')
        """, (store_id, user_id, datetime.now(), opening_cash, opening_cash))

        conn.commit()
        register_id = cursor.lastrowid

        # ✅ Create opening transaction record
        cursor.execute("""
            INSERT INTO cash_transactions (
                register_log_id, user_id, store_id,
                transaction_type, amount, reason, created_by
            )
            VALUES (%s, %s, %s, 'opening', %s, 'Register opened', %s)
        """, (register_id, user_id, store_id, opening_cash, user_id))

        conn.commit()
        cursor.close()
        conn.close()
        
        return jsonify({
            'success': True, 
            'message': 'Register opened successfully',
            'register_id': register_id
        }), 200

    except Exception as e:
        print(f"❌ Error opening register: {e}")
        return jsonify({
            'success': False, 
            'message': f'Error opening register: {str(e)}'
        }), 500


# ✅ CORRECTED: Using new schema columns
@report_bp.route('/check_register_open', methods=['GET'])
@jwt_required()
@role_required('admin','cashier')
def check_register_open():
    try:
        user_id = request.args.get('user_id')
        
        if not user_id:
            return jsonify({'error': 'User ID required'}), 400
            
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)
        
        # ✅ FIXED: opening_cash instead of cash_in_hand
        cursor.execute("""
            SELECT id, store_id, opening_cash, login_time 
            FROM close_register_logs 
            WHERE user_id = %s AND status = 'open'
            ORDER BY id DESC LIMIT 1
        """, (user_id,))
        
        register = cursor.fetchone()
        
        cursor.close()
        conn.close()
        
        if register:
            return jsonify({
                'open': True,
                'register': register
            }), 200
        else:
            return jsonify({'open': False}), 200
            
    except Exception as e:
        print(f"❌ Error checking register: {e}")
        return jsonify({'error': str(e)}), 500
    

    
    
    

# @report_bp.route('/close_register', methods=['POST'])
# @jwt_required()
# @role_required('admin','user')
# def close_register():
#     data = request.json
#     user_id = data['user_id']
#     cash_in_hand_while_closing = data['cash_in_hand_while_closing']
#     total_cash = data['total_cash']
#     total_sale_amount = data['total_sale_amount']
#     doctor_fees = data['doctor_fees']
#     medical_fees = data['medical_fees']
#     sale_return_amount = data['sale_return_amount']
#     total_expense = data['total_expense']
#     notes = data.get('notes', '')

#     conn = get_db_connection()
#     cursor = conn.cursor()

#     cursor.execute("""
#         UPDATE close_register_logs
#         SET logout_time=%s,
#             cash_in_hand_while_closing=%s,
#             total_cash=%s,
#             total_sale_amount=%s,
#             doctor_fees=%s,
#             medical_fees=%s,
#             sale_return_amount=%s,
#             total_expense=%s,
#             notes=%s,
#             status='close'
#         WHERE user_id=%s AND logout_time IS NULL
#     """, (
#         datetime.now(),
#         cash_in_hand_while_closing,
#         total_cash,
#         total_sale_amount,
#         doctor_fees,
#         medical_fees,
#         sale_return_amount,
#         total_expense,
#         notes,
#         user_id
#     ))

#     conn.commit()
#     cursor.close()
#     conn.close()

#     return jsonify({'success': True, 'message': 'Register closed successfully'})


# @report_bp.route('/get_cash_summary', methods=['GET'])
# @jwt_required()
# @role_required('admin', 'user')
# def get_cash_summary():
#     try:
#         user_id = request.args.get('user_id')
#         if not user_id:
#             return jsonify({'error': 'Missing user_id'}), 400

#         conn = get_db_connection()
#         cursor = conn.cursor()

#         # ✅ Get open register session
#         cursor.execute("""
#             SELECT id, cash_in_hand, login_time, logout_time, status
#             FROM close_register_logs
#             WHERE user_id = %s AND status = 'open'
#         """, (user_id,))
#         register = cursor.fetchone()

#         if not register:
#             return jsonify({'error': 'Open register not found'}), 404

#         register_id, cash_in_hand, login_time, logout_time, status = register
#         cash_in_hand = float(cash_in_hand or 0.0)
#         end_time = logout_time or datetime.now()

#         # ✅ Doctor Fees
#         cursor.execute("""
#             SELECT IFNULL(SUM(total), 0)
#             FROM doctor_charges_sale
#             WHERE created_at BETWEEN %s AND %s
#         """, (login_time, end_time))
#         doctor_fees = float(cursor.fetchone()[0])

#         # ✅ Medicine Fees
#         cursor.execute("""
#             SELECT IFNULL(SUM(total), 0)
#             FROM sale_products
#             WHERE created_at BETWEEN %s AND %s
#         """, (login_time, end_time))
#         medicine_fees = float(cursor.fetchone()[0])

#         # ✅ Total Sale (received + suspended invoices)
#         cursor.execute("""
#             SELECT IFNULL(SUM(invoiceTotal), 0)
#             FROM invoice_sale
#             WHERE created_at BETWEEN %s AND %s
#             AND status IN ('received', 'suspended')
#         """, (login_time, end_time))
#         total_sale = float(cursor.fetchone()[0])

#         # ✅ Sale Returns
#         cursor.execute("""
#             SELECT IFNULL(SUM(total_return_amount), 0)
#             FROM sale_return
#             WHERE created_at BETWEEN %s AND %s
#         """, (login_time, end_time))
#         total_return_amount = float(cursor.fetchone()[0])

#         # ✅ Expenses
#         cursor.execute("""
#             SELECT IFNULL(SUM(amount), 0)
#             FROM expenses
#             WHERE created_at BETWEEN %s AND %s
#         """, (login_time, end_time))
#         total_expense = float(cursor.fetchone()[0])

#         # ✅ Cash Payments (paid → invoiceTotal, partial → tenderAmount)
#         # Include partial payments even if status = 'suspended'
#         cursor.execute("""
#             SELECT IFNULL(SUM(
#                 CASE 
#                     WHEN payment_method = 'paid' AND status = 'received' THEN invoiceTotal
#                     WHEN payment_method = 'partial' AND status IN ('received', 'suspended') THEN tenderAmount
#                     ELSE 0
#                 END
#             ), 0)
#             FROM invoice_sale
#             WHERE created_at BETWEEN %s AND %s
#         """, (login_time, end_time))
#         cash_payment_total = float(cursor.fetchone()[0])

#         # ✅ Calculate total cash
#         total_cash = cash_payment_total - total_expense 

#         cursor.close()
#         conn.close()

#         # ✅ JSON Response
#         return jsonify({
#             "status": status,
#             "cash_in_hand": cash_in_hand,
#             "doctor_fees": doctor_fees,
#             "medicine_fees": medicine_fees,
#             "total_sale": total_sale,
#             "total_return_amount": total_return_amount,
#             "total_expense": total_expense,
#             "cash_payment_total": cash_payment_total,
#             "total_cash": total_cash
#         })

#     except Exception as e:
#         print("Summary error:", e)
#         return jsonify({"error": str(e)}), 500


# @report_bp.route('/get_today_summary', methods=['GET'])
# @jwt_required()
# @role_required('admin', 'cashier', 'manager')
# def get_today_summary():
#     try:
#         conn = get_db_connection()
#         cursor = conn.cursor(dictionary=True)

#         today = date.today()
#         start_time = datetime.combine(today, datetime.min.time())
#         end_time = datetime.combine(today, datetime.max.time())

#         # ============================================
#         # 1. TOTAL SALES BY STATUS
#         # ============================================
#         cursor.execute("""
#             SELECT 
#                 IFNULL(SUM(CASE WHEN status = 'received' THEN invoiceTotal ELSE 0 END), 0) as received_sales,
#                 IFNULL(SUM(CASE WHEN status = 'suspended' THEN invoiceTotal ELSE 0 END), 0) as suspended_sales,
#                 IFNULL(SUM(invoiceTotal), 0) as total_sales,
#                 COUNT(CASE WHEN status = 'received' THEN 1 END) as received_count,
#                 COUNT(CASE WHEN status = 'suspended' THEN 1 END) as suspended_count
#             FROM invoice_sale
#             WHERE created_at BETWEEN %s AND %s
#         """, (start_time, end_time))
#         sales_data = cursor.fetchone()

#         # ============================================
#         # 2. PAYMENT METHODS BREAKDOWN
#         # ============================================
#         cursor.execute("""
#             SELECT 
#                 pm.method_name,
#                 IFNULL(SUM(sp.amount), 0) as total_amount,
#                 COUNT(sp.id) as transaction_count
#             FROM sale_payments sp
#             INNER JOIN invoice_sale inv ON sp.sale_id = inv.invoice_id
#             LEFT JOIN payment_methods pm ON sp.payment_method = pm.id
#             WHERE inv.created_at BETWEEN %s AND %s
#             GROUP BY pm.method_name
#             ORDER BY total_amount DESC
#         """, (start_time, end_time))
#         payment_methods = cursor.fetchall()

#         # ============================================
#         # 3. PAYMENT STATUS BREAKDOWN
#         # ============================================
#         cursor.execute("""
#             SELECT 
#                 payment_status,
#                 COUNT(*) as count,
#                 IFNULL(SUM(invoiceTotal), 0) as total_amount,
#                 IFNULL(SUM(tenderAmount), 0) as paid_amount,
#                 IFNULL(SUM(remainingAmount), 0) as due_amount
#             FROM invoice_sale
#             WHERE created_at BETWEEN %s AND %s
#             GROUP BY payment_status
#         """, (start_time, end_time))
#         payment_status_breakdown = cursor.fetchall()

#         # ============================================
#         # 4. TOTAL CASH COLLECTED (from sale_payments)
#         # ============================================
#         cursor.execute("""
#             SELECT IFNULL(SUM(sp.amount), 0) as total_cash
#             FROM sale_payments sp
#             INNER JOIN invoice_sale inv ON sp.sale_id = inv.invoice_id
#             INNER JOIN payment_methods pm ON sp.payment_method = pm.id
#             WHERE inv.created_at BETWEEN %s AND %s
#             AND LOWER(pm.method_name) = 'cash'
#         """, (start_time, end_time))
#         cash_payment = float(cursor.fetchone()['total_cash'])

#         # ============================================
#         # 5. CARD PAYMENTS
#         # ============================================
#         cursor.execute("""
#             SELECT IFNULL(SUM(sp.amount), 0) as total_card
#             FROM sale_payments sp
#             INNER JOIN invoice_sale inv ON sp.sale_id = inv.invoice_id
#             INNER JOIN payment_methods pm ON sp.payment_method = pm.id
#             WHERE inv.created_at BETWEEN %s AND %s
#             AND LOWER(pm.method_name) IN ('card', 'credit card', 'debit card', 'credit/debit card')
#         """, (start_time, end_time))
#         card_payment = float(cursor.fetchone()['total_card'])

#         # ============================================
#         # 6. SALE RETURNS
#         # ============================================
#         cursor.execute("""
#             SELECT 
#                 IFNULL(SUM(total_return_amount), 0) as total_return,
#                 COUNT(*) as return_count
#             FROM sale_return
#             WHERE created_at BETWEEN %s AND %s
#         """, (start_time, end_time))
#         return_data = cursor.fetchone()

#         # ============================================
#         # 7. EXPENSES
#         # ============================================
#         cursor.execute("""
#             SELECT 
#                 IFNULL(SUM(amount), 0) as total_expense,
#                 COUNT(*) as expense_count
#             FROM expenses
#             WHERE created_at BETWEEN %s AND %s
#         """, (start_time, end_time))
#         expense_data = cursor.fetchone()

#         # ============================================
#         # 8. TOTAL PAYMENTS COLLECTED (all methods)
#         # ============================================
#         cursor.execute("""
#             SELECT IFNULL(SUM(sp.amount), 0) as total_payments
#             FROM sale_payments sp
#             INNER JOIN invoice_sale inv ON sp.sale_id = inv.invoice_id
#             WHERE inv.created_at BETWEEN %s AND %s
#         """, (start_time, end_time))
#         total_payments_collected = float(cursor.fetchone()['total_payments'])

#         cursor.close()
#         conn.close()

#         # ============================================
#         # FINAL CALCULATIONS
#         # ============================================
#         total_sale = float(sales_data['total_sales'])
#         received_sales = float(sales_data['received_sales'])
#         suspended_sales = float(sales_data['suspended_sales'])
#         sale_return = float(return_data['total_return'])
#         total_expense = float(expense_data['total_expense'])
        
#         # Net cash = Cash payments - Returns - Expenses
#         net_cash = cash_payment - sale_return - total_expense

#         return jsonify({
#             "date": today.strftime("%Y-%m-%d"),
            
#             # Sales Overview
#             "total_sale": total_sale,
#             "received_sales": received_sales,
#             "suspended_sales": suspended_sales,
#             "received_count": sales_data['received_count'],
#             "suspended_count": sales_data['suspended_count'],
            
#             # Payment Methods Breakdown
#             "payment_methods": payment_methods,
            
#             # Payment Status
#             "payment_status": payment_status_breakdown,
            
#             # Totals by Payment Type
#             "cash_payment": cash_payment,
#             "card_payment": card_payment,
#             "total_payments_collected": total_payments_collected,
            
#             # Deductions
#             "sale_return": sale_return,
#             "return_count": return_data['return_count'],
#             "total_expense": total_expense,
#             "expense_count": expense_data['expense_count'],
            
#             # Final Totals
#             "net_cash": net_cash,
#             "total_cash_in_hand": cash_payment - sale_return
#         })

#     except Exception as e:
#         print(f"❌ Error in today summary: {e}")
#         return jsonify({"error": str(e)}), 500

# ===========================
# 📊 GET ALL CLOSED REGISTER LOGS
# ===========================
@report_bp.route('/get_closed_register_logs', methods=['GET'])
@jwt_required()
@role_required('admin', 'manager')
def get_closed_register_logs():
    """
    ✅ Fetch all closed register sessions with complete details
       including payment_methods_breakdown JSON column
    """
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    try:
        cursor.execute("""
            SELECT 
                crl.id,
                crl.store_id,
                crl.user_id,
                crl.login_time,
                crl.logout_time,
                crl.opening_cash,
                crl.total_cash_added,
                crl.total_cash_removed,
                crl.counted_cash,
                crl.opening_balance,
                crl.closing_balance,
                crl.expected_cash,
                crl.actual_cash,
                crl.cash_difference,
                crl.total_cash,
                crl.total_card_sales,
                crl.payment_methods_breakdown,
                crl.total_sale_amount,
                crl.sale_return_amount,
                crl.total_expense,
                crl.notes,
                crl.status,
                crl.created_at,
                u.name       AS user_name,
                u.email      AS user_email,
                s.store_name
            FROM close_register_logs crl
            LEFT JOIN users  u ON crl.user_id  = u.id
            LEFT JOIN stores s ON crl.store_id = s.id
            WHERE crl.status = 'close'
            ORDER BY crl.logout_time DESC
        """)
        
        rows = cursor.fetchall()
        
        for row in rows:
            for key, value in row.items():
                if isinstance(value, Decimal):
                    row[key] = float(value)
                elif isinstance(value, datetime):
                    row[key] = value.strftime('%Y-%m-%d %H:%M:%S')
            
            # ✅ Parse payment_methods_breakdown JSON string → dict
            if row.get('payment_methods_breakdown'):
                try:
                    if isinstance(row['payment_methods_breakdown'], str):
                        row['payment_methods_breakdown'] = json.loads(
                            row['payment_methods_breakdown']
                        )
                except Exception:
                    row['payment_methods_breakdown'] = {}
            else:
                row['payment_methods_breakdown'] = {}
        
        return jsonify({
            'success': True,
            'logs':    rows,
            'total':   len(rows)
        }), 200
        
    except Exception as e:
        print(f"❌ Error fetching register logs: {e}")
        return jsonify({
            'success': False,
            'message': str(e)
        }), 500
        
    finally:
        cursor.close()
        conn.close()

# ===========================
# 🔍 GET SINGLE REGISTER DETAILS
# ===========================
@report_bp.route('/get_register_details/<int:register_id>', methods=['GET'])
@jwt_required()
@role_required('admin', 'manager', 'cashier')
def get_register_details(register_id):
    conn   = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    try:
        # ===========================
        # 1️⃣ GET REGISTER LOG
        # ===========================
        cursor.execute("""
            SELECT 
                crl.*,
                u.name       AS user_name,
                u.email      AS user_email,
                s.store_name
            FROM close_register_logs crl
            LEFT JOIN users  u ON crl.user_id  = u.id
            LEFT JOIN stores s ON crl.store_id = s.id
            WHERE crl.id = %s
        """, (register_id,))

        register_log = cursor.fetchone()

        if not register_log:
            return jsonify({
                'success': False,
                'message': 'Register log not found'
            }), 404

        login_time  = register_log['login_time']
        logout_time = register_log['logout_time']

        # ===========================
        # 2️⃣ TOTAL SALES BREAKDOWN
        # ===========================
        cursor.execute("""
            SELECT 
                COALESCE(SUM(invoiceTotal), 0)                                                AS total_sales,
                COUNT(*)                                                                       AS total_invoices,
                COUNT(CASE WHEN status = 'received'  THEN 1 END)                             AS received_count,
                COUNT(CASE WHEN status = 'suspended' THEN 1 END)                             AS suspended_count,
                COALESCE(SUM(CASE WHEN status = 'received'  THEN invoiceTotal ELSE 0 END), 0) AS received_sales,
                COALESCE(SUM(CASE WHEN status = 'suspended' THEN invoiceTotal ELSE 0 END), 0) AS suspended_sales
            FROM invoice_sale
            WHERE sale_date BETWEEN %s AND %s
        """, (login_time, logout_time))

        sales_data = cursor.fetchone()

        # ===========================
        # 3️⃣ PAYMENT METHODS BREAKDOWN (Live from sale_payments)
        # ===========================
        cursor.execute("""
            SELECT 
                pm.method_name              AS method_name,
                COALESCE(SUM(sp.amount), 0) AS total_amount,
                COUNT(sp.id)                AS transaction_count
            FROM sale_payments   sp
            JOIN invoice_sale    i  ON sp.sale_id        = i.invoice_id
            JOIN payment_methods pm ON sp.payment_method = pm.id
            WHERE i.sale_date BETWEEN %s AND %s
              AND i.status = 'received'
            GROUP BY pm.id, pm.method_name
            ORDER BY total_amount DESC
        """, (login_time, logout_time))

        payment_methods_live = cursor.fetchall()

        # ===========================
        # 4️⃣ SALES RETURNS
        # ===========================
        cursor.execute("""
            SELECT 
                COALESCE(SUM(total_return_amount), 0) AS total_returns,
                COUNT(*)                               AS return_count
            FROM sale_return
            WHERE return_date BETWEEN %s AND %s
        """, (login_time, logout_time))

        returns_data = cursor.fetchone()

        # ===========================
        # 5️⃣ EXPENSES
        # ===========================
        cursor.execute("""
            SELECT 
                COALESCE(SUM(amount), 0) AS total_expenses,
                COUNT(*)                  AS expense_count
            FROM expenses
            WHERE date BETWEEN %s AND %s
        """, (login_time, logout_time))

        expenses_data = cursor.fetchone()

        # ===========================
        # 6️⃣ CASH TRANSACTIONS (Manual Add/Remove)
        # ===========================
        cursor.execute("""
            SELECT 
                COALESCE(SUM(CASE WHEN transaction_type = 'add'
                    THEN amount ELSE 0 END), 0) AS total_added,
                COALESCE(SUM(CASE WHEN transaction_type = 'remove'
                    THEN amount ELSE 0 END), 0) AS total_removed,
                COUNT(*)                         AS transaction_count
            FROM cash_transactions
            WHERE register_log_id = %s
        """, (register_id,))

        cash_transactions = cursor.fetchone()

        # ===========================
        # 7️⃣ INDIVIDUAL INVOICES LIST
        # ===========================
        cursor.execute("""
            SELECT 
                i.invoice_id,
                i.invoice_code,
                i.invoiceTotal,
                i.tenderAmount,
                i.remainingAmount,
                i.status,
                i.payment_status,
                i.sale_date,
                COALESCE(c.name, 'Walk-in Customer') AS customer_name,
                u.name                               AS cashier_name
            FROM invoice_sale i
            LEFT JOIN customers c ON i.customer_id     = c.id
            LEFT JOIN users     u ON i.cashier_user_id = u.id
            WHERE i.sale_date BETWEEN %s AND %s
            ORDER BY i.sale_date DESC
        """, (login_time, logout_time))

        invoices = cursor.fetchall()

        # ===========================
        # 8️⃣ CASH TRANSACTION LOG
        # ===========================
        cursor.execute("""
            SELECT 
                ct.id,
                ct.transaction_type,
                ct.amount,
                ct.reason,
                ct.note,
                ct.created_at,
                u.name AS created_by_name
            FROM cash_transactions ct
            LEFT JOIN users u ON ct.user_id = u.id
            WHERE ct.register_log_id = %s
            ORDER BY ct.created_at ASC
        """, (register_id,))

        cash_transaction_log = cursor.fetchall()

        # ===========================
        # 9️⃣ USE DATABASE SAVED VALUES DIRECTLY
        # ✅ Re-calculate නොකර database ෙෙකෙ exact values use කරනවා
        # ===========================
        opening_cash   = float(register_log.get('opening_cash')    or 0)
        total_added    = float(cash_transactions.get('total_added')   or 0)
        total_removed  = float(cash_transactions.get('total_removed') or 0)
        total_returns  = float(returns_data.get('total_returns')    or 0)
        total_expenses = float(expenses_data.get('total_expenses')  or 0)

        # ✅ Cash sales — live payment methods ෙෙකෙ cash method ෙෙකෙ value
        cash_sales = 0.0
        for pm in payment_methods_live:
            if str(pm.get('method_name', '')).lower().strip() == 'cash':
                cash_sales = float(pm.get('total_amount') or 0)
                break

        # ✅ CRITICAL FIX: Database ෙෙකෙ saved values directly use කරන්න
        # Re-calculate කිරීෙමෙදී other stores/sessions ෙෙ data mix ෙවෙනනවා
        expected_cash   = float(register_log.get('expected_cash')   or 0)  # ← DB saved value
        actual_cash     = float(register_log.get('counted_cash')    or 0)  # ← DB saved value (counted_cash column)
        cash_difference = float(register_log.get('cash_difference') or 0)  # ← DB generated column

        # ===========================
        # 🔟 PARSE payment_methods_breakdown (saved JSON)
        # ===========================
        saved_breakdown = {}
        raw_breakdown   = register_log.get('payment_methods_breakdown')
        if raw_breakdown:
            try:
                if isinstance(raw_breakdown, str):
                    saved_breakdown = json.loads(raw_breakdown)
                elif isinstance(raw_breakdown, dict):
                    saved_breakdown = raw_breakdown
            except Exception:
                saved_breakdown = {}

        # ===========================
        # 1️⃣1️⃣ SERIALIZE Decimal / datetime
        # ===========================
        def serialize(obj):
            if isinstance(obj, Decimal):
                return float(obj)
            if isinstance(obj, datetime):
                return obj.strftime('%Y-%m-%d %H:%M:%S')
            return obj

        for key, value in register_log.items():
            register_log[key] = serialize(value)
        register_log['payment_methods_breakdown'] = saved_breakdown

        for pm in payment_methods_live:
            for k, v in pm.items():
                pm[k] = serialize(v)

        for inv in invoices:
            for k, v in inv.items():
                inv[k] = serialize(v)

        for ct in cash_transaction_log:
            for k, v in ct.items():
                ct[k] = serialize(v)

        # ===========================
        # ✅ RETURN FULL RESPONSE
        # ===========================
        return jsonify({
            'success':      True,
            'register_log': register_log,

            'breakdown': {
                'opening_cash':    opening_cash,
                'cash_sales':      cash_sales,
                'cash_added':      total_added,
                'cash_removed':    total_removed,
                'total_sales':     float(sales_data.get('total_sales')     or 0),
                'received_sales':  float(sales_data.get('received_sales')  or 0),
                'suspended_sales': float(sales_data.get('suspended_sales') or 0),
                'total_returns':   total_returns,
                'total_expenses':  total_expenses,
                # ✅ Database ෙෙකෙ exact saved values
                'expected_cash':   expected_cash,
                'actual_cash':     actual_cash,
                'cash_difference': cash_difference,
                'total_invoices':  int(sales_data.get('total_invoices')  or 0),
                'received_count':  int(sales_data.get('received_count')  or 0),
                'suspended_count': int(sales_data.get('suspended_count') or 0),
                'return_count':    int(returns_data.get('return_count')  or 0),
                'expense_count':   int(expenses_data.get('expense_count') or 0),
            },

            'payment_methods_live':  payment_methods_live,
            'payment_methods_saved': saved_breakdown,
            'invoices':              invoices,
            'cash_transaction_log':  cash_transaction_log,
        }), 200

    except Exception as e:
        print(f"❌ Error fetching register details: {e}")
        import traceback
        traceback.print_exc()
        return jsonify({
            'success': False,
            'message': str(e)
        }), 500

    finally:
        cursor.close()
        conn.close()

# ===========================
# 📋 GET INVOICES FOR REGISTER SESSION (Complete)
# ===========================
@report_bp.route('/get_register_invoices/<int:register_id>', methods=['GET'])
@jwt_required()
@role_required('admin', 'manager')
def get_register_invoices(register_id):
    conn   = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    try:
        # 1️⃣ GET REGISTER SESSION
        cursor.execute("""
            SELECT crl.id, crl.login_time, crl.logout_time, crl.store_id,
                   crl.user_id, crl.opening_cash, crl.counted_cash,
                   crl.expected_cash, crl.cash_difference,
                   u.name AS cashier_name, u.email AS cashier_email,
                   s.store_name
            FROM close_register_logs crl
            LEFT JOIN users  u ON crl.user_id  = u.id
            LEFT JOIN stores s ON crl.store_id = s.id
            WHERE crl.id = %s
        """, (register_id,))
        session = cursor.fetchone()

        if not session:
            return jsonify({'success': False, 'message': 'Register session not found'}), 404

        login_time  = session['login_time']
        logout_time = session['logout_time']
        store_id    = session['store_id']

        for k, v in session.items():
            if isinstance(v, Decimal): session[k] = float(v)
            elif isinstance(v, datetime): session[k] = v.strftime('%Y-%m-%d %H:%M:%S')

        # 2️⃣ GET INVOICES (filtered by store_id)
        cursor.execute("""
            SELECT i.invoice_id, i.invoice_code, i.tenderAmount, i.remainingAmount,
                   i.invoiceTotal, i.payment_method, i.payment_status, i.sale_date,
                   i.status, i.discount, i.discount_type, i.discount_percentage,
                   i.tax, i.note,
                   COALESCE(c.name, 'Walk-in Customer') AS customer_name,
                   c.contact AS customer_contact,
                   u.name    AS cashier_name
            FROM invoice_sale i
            LEFT JOIN customers c ON i.customer_id     = c.id
            LEFT JOIN users     u ON i.cashier_user_id = u.id
            WHERE i.sale_date BETWEEN %s AND %s
              AND i.store_id  = %s
            ORDER BY i.sale_date DESC
        """, (login_time, logout_time, store_id))
        invoices = cursor.fetchall()

        for invoice in invoices:
            inv_id = invoice['invoice_id']

            # Products
            cursor.execute("""
                SELECT p.product_name, p.sku,
                       pv.variation_name, pv.variation_type,
                       sp.price, sp.our_price, sp.quantity, sp.total,
                       sp.discount, sp.tax, sp.sale_unit
                FROM sale_products sp
                JOIN products p ON sp.product_id = p.id
                LEFT JOIN product_variations pv ON sp.variation_id = pv.id
                WHERE sp.invoice_id = %s
                ORDER BY sp.sale_product_id ASC
            """, (inv_id,))
            products = cursor.fetchall()
            for p in products:
                for k, v in p.items():
                    if isinstance(v, Decimal): p[k] = float(v)
            invoice['products'] = products

            # Payment Methods per invoice
            cursor.execute("""
                SELECT pm.method_name, sp.amount,
                       sp.card_number_last4, sp.card_type,
                       sp.transaction_reference, sp.payment_date
                FROM sale_payments   sp
                JOIN payment_methods pm ON sp.payment_method = pm.id
                WHERE sp.sale_id = %s
                ORDER BY sp.id ASC
            """, (inv_id,))
            payments = cursor.fetchall()
            for p in payments:
                for k, v in p.items():
                    if isinstance(v, Decimal): p[k] = float(v)
                    elif isinstance(v, datetime): p[k] = v.strftime('%Y-%m-%d %H:%M:%S')
            invoice['payment_methods'] = payments

            # Returns per invoice
            cursor.execute("""
                SELECT sr.return_id, sr.return_date, sr.total_return_amount,
                       sr.reason, sr.status AS return_status
                FROM sale_return sr
                WHERE sr.invoice_id = %s
                ORDER BY sr.return_date DESC
            """, (inv_id,))
            returns = cursor.fetchall()
            for r in returns:
                for k, v in r.items():
                    if isinstance(v, Decimal): r[k] = float(v)
                    elif isinstance(v, datetime): r[k] = v.strftime('%Y-%m-%d %H:%M:%S')
            invoice['returns'] = returns

            for k, v in invoice.items():
                if isinstance(v, Decimal): invoice[k] = float(v)
                elif isinstance(v, datetime): invoice[k] = v.strftime('%Y-%m-%d %H:%M:%S')

        # 3️⃣ GET ALL RETURNS IN SESSION (for Returns Tab)
        cursor.execute("""
            SELECT sr.return_id, sr.invoice_id, sr.return_date,
                   sr.total_return_amount, sr.reason,
                   sr.status AS return_status,
                   i.invoice_code,
                   COALESCE(c.name, 'Walk-in Customer') AS customer_name
            FROM sale_return sr
            JOIN invoice_sale i ON sr.invoice_id = i.invoice_id
            LEFT JOIN customers c ON sr.customer_id = c.id
            WHERE sr.return_date BETWEEN %s AND %s
              AND i.store_id = %s
            ORDER BY sr.return_date DESC
        """, (login_time, logout_time, store_id))
        all_returns = cursor.fetchall()

        for r in all_returns:
            # Return products
            cursor.execute("""
                SELECT p.product_name,
                       pv.variation_name, pv.variation_type,
                       srp.return_quantity, srp.return_price, srp.return_total
                FROM sale_return_products srp
                JOIN products p ON srp.product_id = p.id
                LEFT JOIN product_variations pv ON srp.variation_id = pv.id
                WHERE srp.return_id = %s
            """, (r['return_id'],))
            ret_products = cursor.fetchall()
            for p in ret_products:
                for k, v in p.items():
                    if isinstance(v, Decimal): p[k] = float(v)
            r['products'] = ret_products

            for k, v in r.items():
                if isinstance(v, Decimal): r[k] = float(v)
                elif isinstance(v, datetime): r[k] = v.strftime('%Y-%m-%d %H:%M:%S')

        # 4️⃣ GET CASH TRANSACTIONS IN SESSION
        cursor.execute("""
            SELECT ct.id, ct.transaction_type, ct.amount,
                   ct.reason, ct.note, ct.created_at,
                   u.name AS created_by_name
            FROM cash_transactions ct
            LEFT JOIN users u ON ct.user_id = u.id
            WHERE ct.register_log_id = %s
            ORDER BY ct.created_at ASC
        """, (register_id,))
        cash_transactions = cursor.fetchall()
        for t in cash_transactions:
            for k, v in t.items():
                if isinstance(v, Decimal): t[k] = float(v)
                elif isinstance(v, datetime): t[k] = v.strftime('%Y-%m-%d %H:%M:%S')

        # 5️⃣ SUMMARY
        total_invoices     = len(invoices)
        received_invoices  = [i for i in invoices if i['status'] == 'received']
        suspended_invoices = [i for i in invoices if i['status'] == 'suspended']
        total_returns_amt  = sum(float(r.get('total_return_amount', 0)) for r in all_returns)

        return jsonify({
            'success':  True,
            'session':  session,
            'invoices': invoices,
            'returns':  all_returns,
            'cash_transactions': cash_transactions,
            'summary': {
                'total_invoices':         total_invoices,
                'received_count':         len(received_invoices),
                'suspended_count':        len(suspended_invoices),
                'total_received_amount':  round(sum(float(i.get('invoiceTotal',0)) for i in received_invoices), 2),
                'total_suspended_amount': round(sum(float(i.get('invoiceTotal',0)) for i in suspended_invoices), 2),
                'total_returns_amount':   round(total_returns_amt, 2),
                'cash_transaction_count': len(cash_transactions),
            }
        }), 200

    except Exception as e:
        print(f"❌ Error: {e}")
        import traceback; traceback.print_exc()
        return jsonify({'success': False, 'message': str(e)}), 500
    finally:
        cursor.close()
        conn.close()

# ===========================
# 🔄 GET SALE RETURNS FOR REGISTER
# ===========================
@report_bp.route('/get_register_returns/<int:register_id>', methods=['GET'])
@jwt_required()
@role_required('admin', 'manager')
def get_register_returns(register_id):
    """
    ✅ Get all sale returns during a register session
    """
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    try:
        # Get time range
        cursor.execute("""
            SELECT login_time, logout_time 
            FROM close_register_logs 
            WHERE id = %s
        """, (register_id,))
        
        session = cursor.fetchone()
        
        if not session:
            return jsonify({
                'success': False,
                'message': 'Register session not found'
            }), 404
        
        login_time = session['login_time']
        logout_time = session['logout_time']
        
        # Get returns
        cursor.execute("""
            SELECT 
                sr.*,
                i.invoice_code,
                c.name AS customer_name
            FROM sale_return sr
            JOIN invoice_sale i ON sr.invoice_id = i.invoice_id
            LEFT JOIN customers c ON sr.customer_id = c.id
            WHERE sr.return_date BETWEEN %s AND %s
            ORDER BY sr.return_date DESC
        """, (login_time, logout_time))
        
        returns = cursor.fetchall()
        
        # Get return products
        for ret in returns:
            cursor.execute("""
                SELECT 
                    p.product_name,
                    pv.variation_name,
                    pv.variation_type,
                    srp.return_price,
                    srp.return_quantity,
                    srp.return_total
                FROM sale_return_products srp
                JOIN products p ON srp.product_id = p.id
                LEFT JOIN product_variations pv ON srp.variation_id = pv.id
                WHERE srp.return_id = %s
            """, (ret['return_id'],))
            
            ret['products'] = cursor.fetchall()
            
            # Convert Decimals
            for key, value in ret.items():
                if isinstance(value, Decimal):
                    ret[key] = float(value)
                elif isinstance(value, datetime):
                    ret[key] = value.strftime('%Y-%m-%d %H:%M:%S')
            
            for product in ret['products']:
                for key, value in product.items():
                    if isinstance(value, Decimal):
                        product[key] = float(value)
        
        return jsonify({
            'success': True,
            'returns': returns,
            'total': len(returns)
        }), 200
        
    except Exception as e:
        print(f"❌ Error fetching register returns: {e}")
        return jsonify({
            'success': False,
            'message': str(e)
        }), 500
        
    finally:
        cursor.close()
        conn.close()


# ===========================
# 📊 GET CASH TRANSACTIONS
# ===========================
@report_bp.route('/get_register_cash_transactions/<int:register_id>', methods=['GET'])
@jwt_required()
@role_required('admin', 'manager', 'cashier')
def get_register_cash_transactions(register_id):
    """
    ✅ Get all cash add/remove transactions for a register session
    """
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    try:
        cursor.execute("""
            SELECT 
                ct.*,
                u.name AS created_by_name
            FROM cash_transactions ct
            LEFT JOIN users u ON ct.created_by = u.id
            WHERE ct.register_log_id = %s
            ORDER BY ct.created_at ASC
        """, (register_id,))
        
        transactions = cursor.fetchall()
        
        for txn in transactions:
            for key, value in txn.items():
                if isinstance(value, Decimal):
                    txn[key] = float(value)
                elif isinstance(value, datetime):
                    txn[key] = value.strftime('%Y-%m-%d %H:%M:%S')
        
        return jsonify({
            'success': True,
            'transactions': transactions,
            'total': len(transactions)
        }), 200
        
    except Exception as e:
        print(f"❌ Error fetching cash transactions: {e}")
        return jsonify({
            'success': False,
            'message': str(e)
        }), 500
        
    finally:
        cursor.close()
        conn.close()


# ===========================
# 📈 LEGACY ENDPOINTS (For Compatibility)
# ===========================

@report_bp.route('/invoices_for_all_closed_registers', methods=['GET'])
@jwt_required()
@role_required('admin')
def invoices_for_all_closed_registers():
    """
    ⚠️ LEGACY: Get all invoices from all closed registers
    Consider using /get_register_invoices/<id> instead
    """
    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        cursor.execute("""
            SELECT login_time, logout_time, id
            FROM close_register_logs 
            WHERE status = 'close' AND logout_time IS NOT NULL
            ORDER BY logout_time DESC
        """)
        closed_sessions = cursor.fetchall()

        all_invoice_details = []

        for session in closed_sessions:
            login_time = session['login_time']
            logout_time = session['logout_time']
            register_id = session['id']

            cursor.execute("""
                SELECT 
                    i.invoice_id,
                    i.invoice_code,
                    i.tenderAmount,
                    i.remainingAmount,
                    i.invoiceTotal,
                    i.payment_method,
                    i.sale_date,
                    i.status,
                    c.name AS customer_name
                FROM invoice_sale i
                LEFT JOIN customers c ON i.customer_id = c.id
                WHERE i.sale_date BETWEEN %s AND %s
            """, (login_time, logout_time))

            invoices = cursor.fetchall()

            for invoice in invoices:
                cursor.execute("""
                    SELECT 
                        p.product_name,
                        sp.price,
                        sp.quantity,
                        sp.total
                    FROM sale_products sp
                    JOIN products p ON sp.product_id = p.id
                    WHERE sp.invoice_id = %s
                """, (invoice['invoice_id'],))
                
                products = cursor.fetchall()
                
                invoice_data = {
                    "register_id": register_id,
                    "invoice_id": invoice['invoice_id'],
                    "invoice_code": invoice['invoice_code'],
                    "tenderAmount": float(invoice['tenderAmount']) if invoice['tenderAmount'] else 0,
                    "remainingAmount": float(invoice['remainingAmount']) if invoice['remainingAmount'] else 0,
                    "invoiceTotal": float(invoice['invoiceTotal']) if invoice['invoiceTotal'] else 0,
                    "payment_method": invoice['payment_method'],
                    "customer_name": invoice['customer_name'],
                    "sale_date": invoice['sale_date'].strftime('%Y-%m-%d %H:%M:%S') if invoice['sale_date'] else '',
                    "status": invoice['status'],
                    "products": [
                        {
                            "product_name": p['product_name'],
                            "price": float(p['price']) if p['price'] else 0,
                            "quantity": float(p['quantity']) if p['quantity'] else 0,
                            "total": float(p['total']) if p['total'] else 0
                        }
                        for p in products
                    ]
                }
                
                all_invoice_details.append(invoice_data)

        cursor.close()
        conn.close()

        return jsonify({"invoices": all_invoice_details}), 200

    except Exception as e:
        print("❌ Error:", e)
        return jsonify({"error": str(e)}), 500


@report_bp.route('/view_all_sale_returns_in_closed_registers', methods=['GET'])
@jwt_required()
@role_required('admin')
def view_all_sale_returns_in_closed_registers():
    """
    ⚠️ LEGACY: Get all returns from all closed registers
    Consider using /get_register_returns/<id> instead
    """
    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        cursor.execute("""
            SELECT login_time, logout_time, id
            FROM close_register_logs 
            WHERE status = 'close' AND logout_time IS NOT NULL
        """)
        closed_sessions = cursor.fetchall()

        all_returns = []

        for session in closed_sessions:
            login_time = session['login_time']
            logout_time = session['logout_time']
            register_id = session['id']

            cursor.execute("""
                SELECT 
                    sr.*, 
                    i.invoice_code,
                    c.name AS customer_name
                FROM sale_return sr
                JOIN invoice_sale i ON sr.invoice_id = i.invoice_id
                LEFT JOIN customers c ON sr.customer_id = c.id
                WHERE sr.return_date BETWEEN %s AND %s
                ORDER BY sr.return_date DESC
            """, (login_time, logout_time))

            returns = cursor.fetchall()

            for r in returns:
                r['register_id'] = register_id
                
                if r.get('return_date') and isinstance(r['return_date'], datetime):
                    r['return_date'] = r['return_date'].strftime('%Y-%m-%d %H:%M:%S')

                cursor.execute("""
                    SELECT 
                        p.product_name,
                        pv.variation_name,
                        srp.return_quantity,
                        srp.return_price,
                        srp.return_total
                    FROM sale_return_products srp
                    JOIN products p ON srp.product_id = p.id
                    LEFT JOIN product_variations pv ON srp.variation_id = pv.id
                    WHERE srp.return_id = %s
                """, (r['return_id'],))
                
                r['products'] = cursor.fetchall()
                
                # Convert Decimals
                for key, value in r.items():
                    if isinstance(value, Decimal):
                        r[key] = float(value)
                
                for product in r['products']:
                    for key, value in product.items():
                        if isinstance(value, Decimal):
                            product[key] = float(value)

                all_returns.append(r)

        cursor.close()
        conn.close()

        return jsonify(all_returns), 200

    except Exception as e:
        print("❌ Error:", e)
        return jsonify({"error": str(e)}), 500


@report_bp.route('/view_product_alert', methods=['GET'])
@jwt_required()
@role_required('admin')
def view_product_alert():
    """
    Fetch products with low stock OR out of stock (quantity <= alert threshold OR quantity = 0).
    
    Shows:
    - Current quantity at each location
    - Alert threshold
    - Items where quantity <= alert_quantity (includes zero/out-of-stock)
    """
    conn = get_db_connection()
    if conn is None:
        return jsonify({'error': 'Database connection failed'}), 500

    cursor = conn.cursor(dictionary=True)

    try:
        results = []

        # ============================================
        # SINGLE PRODUCTS - Low stock + Out of stock
        # ============================================
        cursor.execute("""
            SELECT 
                p.id,
                p.product_name,
                p.sku,
                p.product_type,
                p.stock_alert,
                u.unit_short AS unit,
                
                -- Price range
                MIN(pb.cost) AS min_cost,
                MAX(pb.cost) AS max_cost,
                MIN(pb.price) AS min_price,
                MAX(pb.price) AS max_price,
                
                -- Aggregated quantities at this location
                SUM(pb.remaining_quantity) AS total_remaining_quantity,
                SUM(COALESCE(ws.quantity, 0)) AS total_warehouse_quantity,
                
                -- Batch/GRN info
                GROUP_CONCAT(DISTINCT pb.batch_number ORDER BY pb.batch_number SEPARATOR ', ') AS batch_numbers,
                GROUP_CONCAT(DISTINCT g.grn_code ORDER BY g.grn_code SEPARATOR ', ') AS grn_codes,
                
                -- Expiry
                MIN(pb.expiration_date) AS earliest_expiry,
                
                -- Location
                COALESCE(s.store_name, '-') AS store_name,
                COALESCE(w.warehouse_name, '-') AS warehouse_name,
                s.id AS store_id,
                w.id AS warehouse_id
                
            FROM products p
            LEFT JOIN units u ON p.sale_unit_id = u.id
            INNER JOIN product_batches pb ON pb.product_id = p.id AND pb.variation_id IS NULL
            LEFT JOIN grn g ON pb.grn_id = g.grn_id
            LEFT JOIN warehouse_stock ws ON ws.batch_id = pb.batch_id
            LEFT JOIN stores s ON ws.store_id = s.id
            LEFT JOIN warehouses w ON ws.warehouse_id = w.id
            
            WHERE p.product_type = 'single'
            
            GROUP BY p.id, p.product_name, p.sku, p.stock_alert, u.unit_short,
                     s.id, w.id, s.store_name, w.warehouse_name
            
            -- ✅ FIXED: Include zero/out-of-stock AND low stock
            HAVING SUM(COALESCE(ws.quantity, 0)) <= p.stock_alert
            
            ORDER BY p.product_name, s.store_name, w.warehouse_name
        """)
        single_products = cursor.fetchall()

        for product in single_products:
            qty = float(product['total_warehouse_quantity'] or 0)
            alert_threshold = float(product['stock_alert'] or 0)

            # ✅ FIXED: Correctly flag out-of-stock vs low-stock
            if qty == 0:
                stock_status = "out_of_stock"
            else:
                stock_status = "low_stock"

            min_cost = float(product['min_cost'] or 0)
            max_cost = float(product['max_cost'] or 0)
            min_price = float(product['min_price'] or 0)
            max_price = float(product['max_price'] or 0)

            cost_display = f"{min_cost}" if min_cost == max_cost else f"{min_cost} - {max_cost}"
            price_display = f"{min_price}" if min_price == max_price else f"{min_price} - {max_price}"

            results.append({
                "product_code": product['sku'],
                "product_name": product['product_name'],
                "batch_numbers": product['batch_numbers'] or '-',
                "grn_codes": product['grn_codes'] or '-',
                "cost": cost_display,
                "price": price_display,
                "quantity": qty,
                "remaining_quantity": float(product['total_remaining_quantity'] or 0),
                "alert_quantity": alert_threshold,
                "unit": product['unit'],
                "store_name": product['store_name'],
                "warehouse_name": product['warehouse_name'],
                "expiration_date": str(product['earliest_expiry']) if product['earliest_expiry'] else None,
                "stock_alert_flag": True,
                "stock_status": stock_status,   # ✅ NEW: "out_of_stock" | "low_stock"
                "variation_name": None,
                "variation_type": None,
                "parent_product_name": None
            })

        # ============================================
        # VARIABLE PRODUCTS - Low stock + Out of stock
        # ============================================
        # ✅ FIXED: Get unique parent products that have at least one alert variation
        # Removed the broken GROUP BY + HAVING that mixed pv columns with DISTINCT
        cursor.execute("""
            SELECT DISTINCT
                p.id,
                p.product_name,
                p.sku,
                p.stock_alert,
                u.unit_short AS unit
            FROM products p
            LEFT JOIN units u ON p.sale_unit_id = u.id
            INNER JOIN product_variations pv ON pv.product_id = p.id
            INNER JOIN product_batches pb ON pb.variation_id = pv.id
            LEFT JOIN warehouse_stock ws ON ws.batch_id = pb.batch_id
            WHERE p.product_type = 'variable'
            GROUP BY p.id, p.product_name, p.sku, p.stock_alert, u.unit_short, 
                     pv.id, pv.variation_stock_alert
            HAVING SUM(COALESCE(ws.quantity, 0)) <= pv.variation_stock_alert
        """)
        variable_products = cursor.fetchall()

        added_parent_headers = set()

        for product in variable_products:
            cursor.execute("""
                SELECT 
                    pv.id AS variation_id,
                    pv.variation_name,
                    pv.variation_type,
                    pv.variation_sku,
                    pv.variation_stock_alert,
                    
                    -- Price range
                    MIN(pb.cost) AS min_cost,
                    MAX(pb.cost) AS max_cost,
                    MIN(pb.price) AS min_price,
                    MAX(pb.price) AS max_price,
                    
                    -- Quantities at this location
                    SUM(pb.remaining_quantity) AS total_remaining_quantity,
                    SUM(COALESCE(ws.quantity, 0)) AS total_warehouse_quantity,
                    
                    -- Batch/GRN info
                    GROUP_CONCAT(DISTINCT pb.batch_number ORDER BY pb.batch_number SEPARATOR ', ') AS batch_numbers,
                    GROUP_CONCAT(DISTINCT g.grn_code ORDER BY g.grn_code SEPARATOR ', ') AS grn_codes,
                    
                    -- Expiry
                    MIN(pb.expiration_date) AS earliest_expiry,
                    
                    -- Location
                    COALESCE(s.store_name, '-') AS store_name,
                    COALESCE(w.warehouse_name, '-') AS warehouse_name,
                    s.id AS store_id,
                    w.id AS warehouse_id
                    
                FROM product_variations pv
                INNER JOIN product_batches pb ON pb.variation_id = pv.id
                LEFT JOIN grn g ON pb.grn_id = g.grn_id
                LEFT JOIN warehouse_stock ws ON ws.batch_id = pb.batch_id
                LEFT JOIN stores s ON ws.store_id = s.id
                LEFT JOIN warehouses w ON ws.warehouse_id = w.id
                
                WHERE pv.product_id = %s
                
                GROUP BY pv.id, pv.variation_name, pv.variation_type, pv.variation_sku,
                         pv.variation_stock_alert,
                         s.id, w.id, s.store_name, w.warehouse_name
                
                -- ✅ FIXED: Include zero/out-of-stock AND low stock
                HAVING SUM(COALESCE(ws.quantity, 0)) <= pv.variation_stock_alert
                
                ORDER BY pv.variation_name, s.store_name, w.warehouse_name
            """, (product['id'],))

            variations = cursor.fetchall()

            if variations and product['id'] not in added_parent_headers:
                results.append({
                    "product_code": product['sku'],
                    "product_name": product['product_name'],
                    "batch_numbers": '-',
                    "grn_codes": '-',
                    "cost": '-',
                    "price": '-',
                    "quantity": None,
                    "remaining_quantity": None,
                    "alert_quantity": None,
                    "unit": product['unit'],
                    "store_name": '-',
                    "warehouse_name": '-',
                    "expiration_date": None,
                    "stock_alert_flag": False,
                    "stock_status": "parent_header",   # ✅ NEW
                    "variation_name": None,
                    "variation_type": None,
                    "parent_product_name": None
                })
                added_parent_headers.add(product['id'])

            for v in variations:
                qty = float(v['total_warehouse_quantity'] or 0)
                alert_threshold = float(v['variation_stock_alert'] or 0)

                # ✅ FIXED: Correctly flag out-of-stock vs low-stock
                if qty == 0:
                    stock_status = "out_of_stock"
                else:
                    stock_status = "low_stock"

                min_cost = float(v['min_cost'] or 0)
                max_cost = float(v['max_cost'] or 0)
                min_price = float(v['min_price'] or 0)
                max_price = float(v['max_price'] or 0)

                cost_display = f"{min_cost}" if min_cost == max_cost else f"{min_cost} - {max_cost}"
                price_display = f"{min_price}" if min_price == max_price else f"{min_price} - {max_price}"

                results.append({
                    "product_code": v['variation_sku'],
                    "product_name": f"{product['product_name']} - {v['variation_name']} ({v['variation_type']})",
                    "batch_numbers": v['batch_numbers'] or '-',
                    "grn_codes": v['grn_codes'] or '-',
                    "cost": cost_display,
                    "price": price_display,
                    "quantity": qty,
                    "remaining_quantity": float(v['total_remaining_quantity'] or 0),
                    "alert_quantity": alert_threshold,
                    "unit": product['unit'],
                    "store_name": v['store_name'],
                    "warehouse_name": v['warehouse_name'],
                    "expiration_date": str(v['earliest_expiry']) if v['earliest_expiry'] else None,
                    "stock_alert_flag": True,
                    "stock_status": stock_status,   # ✅ NEW: "out_of_stock" | "low_stock"
                    "variation_name": v['variation_name'],
                    "variation_type": v['variation_type'],
                    "parent_product_name": product['product_name']
                })

        return jsonify(results), 200

    except Exception as err:
        print(f"Error fetching products: {err}")
        import traceback
        traceback.print_exc()
        return jsonify({'error': 'Failed to fetch products'}), 500

    finally:
        cursor.close()
        conn.close()
        
@report_bp.route('/view_product_stock', methods=['GET'])
@jwt_required()
@role_required('admin')
def view_product_stock():
    """
    Get stock levels for all products across all warehouses.
    ✅ Returns products list + summary statistics (totals, expiry counts, low stock count,
       total stock cost/price/our_price calculated from current batch quantities).
    ✅ cost / price / our_price / quantity / expiry_date are all built from the SAME batch rows
       so every value is index-aligned in the frontend.
    ✅ Fixed: removed DISTINCT so same-cost batches with different quantities are not merged.
    ✅ Fixed: ORDER BY expiration_date ASC (NULLS LAST) so earliest expiry shows first.
    """
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    try:
        warehouse_id   = request.args.get('warehouse_id',   type=int)
        category_id    = request.args.get('category_id',    type=int)
        low_stock_only = request.args.get('low_stock_only', type=int)

        # ── Main query: get all products / variations / warehouses ──────────
        query = """
            SELECT 
                p.id AS product_id,
                p.product_name,
                p.sku AS product_code,
                p.product_type,
                p.stock_alert,

                w.id   AS warehouse_id,
                w.warehouse_name,

                c.category_name AS category,

                pv.id             AS variation_id,
                pv.variation_name,
                pv.variation_type,
                pv.variation_sku,
                pv.variation_stock_alert,

                SUM(COALESCE(ws.quantity, 0)) AS quantity,

                u.unit_short AS unit,
                u.unit_name,

                CASE 
                    WHEN pv.id IS NOT NULL THEN
                        CASE WHEN SUM(COALESCE(ws.quantity, 0)) <= COALESCE(pv.variation_stock_alert, 0)
                             THEN 1 ELSE 0 END
                    ELSE
                        CASE WHEN SUM(COALESCE(ws.quantity, 0)) <= p.stock_alert
                             THEN 1 ELSE 0 END
                END AS stock_alert_flag

            FROM products p
            LEFT JOIN categories        c  ON p.category_id     = c.id
            LEFT JOIN product_variations pv ON p.id              = pv.product_id
            LEFT JOIN warehouse_stock   ws  ON p.id              = ws.product_id
                AND (pv.id IS NULL OR ws.variation_id = pv.id)
            LEFT JOIN warehouses        w   ON ws.warehouse_id   = w.id
            LEFT JOIN units             u   ON p.sale_unit_id    = u.id
            WHERE 1=1
        """

        params = []

        if warehouse_id:
            query += " AND ws.warehouse_id = %s"
            params.append(warehouse_id)

        if category_id:
            query += " AND p.category_id = %s"
            params.append(category_id)

        query += """
            GROUP BY
                p.id, p.product_name, p.sku, p.product_type, p.stock_alert,
                w.id, w.warehouse_name,
                c.category_name,
                pv.id, pv.variation_name, pv.variation_type, pv.variation_sku,
                pv.variation_stock_alert,
                u.unit_short, u.unit_name
            HAVING SUM(COALESCE(ws.quantity, 0)) > 0
            ORDER BY p.product_name, pv.variation_name
        """

        cursor.execute(query, params)
        rows = cursor.fetchall()

        products      = []
        seen_products = {}   # tracks parent header rows already added

        # ── Summary counters ─────────────────────────────────────────────────
        from datetime import date, timedelta
        today        = date.today()
        soon_cutoff  = today + timedelta(days=90)

        summary_total_products   = 0   # total product/variation rows (non-parent)
        summary_expiring_soon    = 0   # rows with at least one batch expiring ≤ 90 days
        summary_expired          = 0   # rows with at least one batch already expired
        summary_low_stock        = 0   # rows flagged as stock alert
        summary_total_cost_value = 0.0 # SUM(batch.cost * ws.quantity) across all rows
        summary_total_price_value= 0.0 # SUM(batch.price * ws.quantity)
        summary_total_our_price_value = 0.0  # SUM(batch.our_price * ws.quantity)

        for row in rows:
            product_key = f"{row['product_id']}_{row['warehouse_id']}"

            # ── Parent header row for variable products ──────────────────────
            if row["product_type"] == "variable" and product_key not in seen_products:
                products.append({
                    "product_id":          row["product_id"],
                    "product_name":        row["product_name"],
                    "product_code":        row["product_code"],
                    "product_type":        row["product_type"],
                    "cost_display":        "-",
                    "price_display":       "-",
                    "our_price_display":   "-",
                    "quantity_display":    "-",
                    "expiry_date_display": "-",
                    "warehouse_id":        row["warehouse_id"],
                    "warehouse_name":      row["warehouse_name"],
                    "category":            row["category"],
                    "variation_id":        None,
                    "variation_name":      None,
                    "variation_type":      None,
                    "quantity":            None,
                    "unit":                row["unit"]      or "",
                    "unit_name":           row["unit_name"] or "",
                    "stock_alert":         row["stock_alert"],
                    "stock_alert_flag":    False,
                })
                seen_products[product_key] = True

            # ── Fetch per-batch details ──────────────────────────────────────
            batch_query = """
                SELECT
                    pb.batch_id,
                    pb.cost,
                    pb.price,
                    pb.our_price,
                    pb.expiration_date,
                    ws.quantity
                FROM product_batches pb
                INNER JOIN warehouse_stock ws
                    ON pb.batch_id     = ws.batch_id
                   AND ws.warehouse_id = %s
                WHERE pb.product_id          = %s
                  AND pb.remaining_quantity  > 0
                  AND ws.quantity            > 0
            """
            batch_params = [row["warehouse_id"], row["product_id"]]

            if row["variation_id"]:
                batch_query += """
                  AND pb.variation_id  = %s
                  AND ws.variation_id  = %s
                """
                batch_params.extend([row["variation_id"], row["variation_id"]])
            else:
                batch_query += """
                  AND pb.variation_id IS NULL
                  AND ws.variation_id IS NULL
                """

            # Nearest expiry first; batches with no expiry date go to the end
            batch_query += """
                ORDER BY
                    CASE WHEN pb.expiration_date IS NULL THEN 1 ELSE 0 END ASC,
                    pb.expiration_date ASC,
                    pb.batch_id        ASC
            """

            cursor.execute(batch_query, batch_params)
            batches = cursor.fetchall()

            # ── Build index-aligned display strings ──────────────────────────
            costs        = []
            prices       = []
            our_prices   = []
            quantities   = []
            expiry_dates = []

            # Expiry status flags for this product row
            row_has_expired = False
            row_has_soon    = False

            for b in batches:
                cost_val      = float(b['cost'])      if b["cost"]      is not None else 0.0
                price_val     = float(b['price'])     if b["price"]     is not None else 0.0
                our_price_val = float(b.get('our_price', 0) or 0)
                qty_val       = float(b['quantity'])  if b["quantity"]  is not None else 0.0

                costs.append(
                    f"{cost_val:.2f}" if b["cost"] is not None else "-"
                )
                prices.append(
                    f"{price_val:.2f}" if b["price"] is not None else "-"
                )
                our_prices.append(
                    f"{our_price_val:.2f}" if b.get("our_price") is not None else "-"
                )
                quantities.append(
                    f"{qty_val:.2f}" if b["quantity"] is not None else "-"
                )

                # ISO format (YYYY-MM-DD) so JS new Date() parses correctly
                exp_date = b.get("expiration_date")
                if exp_date is not None:
                    expiry_dates.append(exp_date.strftime("%Y-%m-%d"))
                    # Check expiry status
                    if exp_date < today:
                        row_has_expired = True
                    elif exp_date <= soon_cutoff:
                        row_has_soon = True
                else:
                    expiry_dates.append("-")

                # ── Accumulate summary value totals ──────────────────────────
                summary_total_cost_value      += cost_val      * qty_val
                summary_total_price_value     += price_val     * qty_val
                summary_total_our_price_value += our_price_val * qty_val

            sep = " - "
            cost_display        = sep.join(costs)        if costs        else "-"
            price_display       = sep.join(prices)       if prices       else "-"
            our_price_display   = sep.join(our_prices)   if our_prices   else "-"
            quantity_display    = sep.join(quantities)   if quantities   else "-"
            expiry_date_display = sep.join(expiry_dates) if expiry_dates else "-"

            # Total quantity kept for stock_alert_flag
            total_quantity   = float(row["quantity"]) if row["quantity"] else 0.0
            is_stock_alert   = bool(row["stock_alert_flag"])

            # ── Update summary counters ──────────────────────────────────────
            summary_total_products += 1
            if is_stock_alert:
                summary_low_stock += 1
            if row_has_expired:
                summary_expired += 1
            if row_has_soon and not row_has_expired:
                summary_expiring_soon += 1

            # ── Product name / code ──────────────────────────────────────────
            if row["variation_name"]:
                product_display  = f"↳ {row['variation_name']}"
                if row["variation_type"]:
                    product_display += f" ({row['variation_type']})"
                product_code = row["variation_sku"] or row["product_code"]
            else:
                product_display = row["product_name"]
                product_code    = row["product_code"]

            products.append({
                "product_id":          row["product_id"],
                "product_name":        product_display,
                "product_code":        product_code,
                "product_type":        row["product_type"],

                "cost_display":        cost_display,
                "price_display":       price_display,
                "our_price_display":   our_price_display,
                "quantity_display":    quantity_display,
                "expiry_date_display": expiry_date_display,

                "warehouse_id":        row["warehouse_id"],
                "warehouse_name":      row["warehouse_name"],
                "category":            row["category"],

                "variation_id":        row["variation_id"],
                "variation_name":      row["variation_name"],
                "variation_type":      row["variation_type"],

                "quantity":            total_quantity,
                "unit":                row["unit"]      or "",
                "unit_name":           row["unit_name"] or "",

                "stock_alert":         row["stock_alert"],
                "stock_alert_flag":    is_stock_alert,
            })

        # ── Optional: filter to low-stock only ──────────────────────────────
        if low_stock_only:
            products = [p for p in products if p.get("stock_alert_flag")]

        # ── Build summary object ─────────────────────────────────────────────
        summary = {
            "total_products":         summary_total_products,
            "expiring_soon":          summary_expiring_soon,
            "expired":                summary_expired,
            "low_stock":              summary_low_stock,
            "total_cost_value":       round(summary_total_cost_value,       2),
            "total_price_value":      round(summary_total_price_value,      2),
            "total_our_price_value":  round(summary_total_our_price_value,  2),
        }

        return jsonify({
            "products": products,
            "summary":  summary,
        }), 200

    except Exception as e:
        print(f"❌ Error in view_product_stock: {e}")
        import traceback
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500

    finally:
        cursor.close()
        conn.close()
# -------------------------------------------------------------------
# 5️⃣ GET GRN RECORDS BY PRODUCT (with warehouse + variation filter)
# -------------------------------------------------------------------
@report_bp.route('/get_grn_by_product/<int:product_id>', methods=['GET'])
@jwt_required()
@role_required('admin')
def get_grn_by_product(product_id):
    """
    Get all GRN (Goods Received Notes) records for a specific product.
    Includes per-item details from grn_items joined to grn and product_batches.
 
    Schema notes (document 5):
        grn:
            grn_id, grn_code, purchase_order_id, supplier_id, warehouse_id,
            store_id, grn_date (DATE), received_by, invoice_number,
            invoice_date (DATE), vehicle_number, driver_name, driver_contact,
            status ENUM('pending','partial','completed','cancelled'),
            subtotal, tax, discount, grand_total, note, created_by,
            approved_by, created_at, approved_at, updated_at
 
        grn_items:
            grn_item_id, grn_id, product_id, variation_id, batch_id,
            ordered_quantity, received_quantity, rejected_quantity,
            unit_price, batch_price, our_price (ALTER TABLE added),
            discount_type, product_discount, tax_type, product_tax,
            discount, tax, net_unit_cost, subtotal, purchase_unit,
            expiration_date (DATE), manufacturing_date (DATE),
            quality_check ENUM('passed','failed','pending'),
            rejection_reason, note, created_at,
            accepted_quantity  ← GENERATED ALWAYS AS (received_quantity - rejected_quantity) STORED
 
        product_batches:
            batch_id, product_id, variation_id, batch_number, quantity,
            remaining_quantity, cost, price,
            our_price (ALTER TABLE added),
            expiration_date, purchase_order_id, grn_id, created_on
 
    Query Params:
        - variation_id  (optional int)
        - warehouse_id  (optional int)
        - start         (optional YYYY-MM-DD)
        - end           (optional YYYY-MM-DD)
 
    Frontend (GRN tab) uses per row:
        grn_date, grn_code, purchase_order_id (→ PO-xxx),
        product_name, product_sku, variation_name, variation_type, variation_sku,
        supplier_name, warehouse_name, batch_number,
        received_quantity, rejected_quantity, accepted_quantity,
        unit_price, subtotal, quality_check, grn_status
        + expand sub-row: grn_id, invoice_number, invoice_date,
          expiration_date, received_by_name, vehicle_number,
          driver_name, purchase_unit, note, item_note, rejection_reason
    """
    conn   = get_db_connection()
    cursor = conn.cursor(dictionary=True)
 
    try:
        variation_id = request.args.get('variation_id', type=int)
        warehouse_id = request.args.get('warehouse_id', type=int)
        start_date   = request.args.get('start')
        end_date     = request.args.get('end')
 
        query = """
            SELECT
                -- GRN header
                g.grn_id,
                g.grn_code,
                g.grn_date,
                g.status                AS grn_status,
                g.purchase_order_id,
                g.invoice_number,
                g.invoice_date,
                g.vehicle_number,
                g.driver_name,
                g.driver_contact,
                g.note                  AS grn_note,
                g.grand_total           AS grn_grand_total,
                g.subtotal              AS grn_subtotal,
                g.tax                   AS grn_tax,
                g.discount              AS grn_discount,
                g.payment_status        AS grn_payment_status,
                g.warehouse_id,
                g.store_id,
 
                -- Supplier
                s.supplier_name,
 
                -- Warehouse
                w.warehouse_name,
 
                -- Received-by user
                u.name                  AS received_by_name,
 
                -- Created-by user
                uc.name                 AS created_by_name,
 
                -- GRN Item
                gi.grn_item_id,
                gi.ordered_quantity,
                gi.received_quantity,
                gi.rejected_quantity,
                gi.accepted_quantity,       -- GENERATED ALWAYS (stored)
                gi.unit_price,
                gi.net_unit_cost,
                gi.batch_price,
                gi.our_price            AS item_our_price,
                gi.discount             AS item_discount,
                gi.tax                  AS item_tax,
                gi.product_discount,
                gi.product_tax,
                gi.subtotal,
                gi.purchase_unit,
                gi.expiration_date,
                gi.manufacturing_date,
                gi.quality_check,
                gi.rejection_reason,
                gi.note                 AS item_note,
 
                -- Product
                p.id                    AS product_id,
                p.product_name,
                p.sku                   AS product_sku,
                p.product_type,
 
                -- Variation (all 3 fields required by formatProductNameHTML)
                pv.id                   AS variation_id,
                pv.variation_name,
                pv.variation_type,
                pv.variation_sku,
 
                -- Batch linked via grn_items.batch_id (most accurate join)
                pb.batch_id,
                pb.batch_number,
                pb.quantity             AS batch_total_quantity,
                pb.remaining_quantity,
                pb.cost                 AS batch_cost,
                pb.price                AS batch_price_sell,
                pb.our_price            AS batch_our_price,
                pb.expiration_date      AS batch_expiration_date
 
            FROM grn_items gi
            JOIN  grn                   g   ON gi.grn_id      = g.grn_id
            JOIN  products              p   ON gi.product_id  = p.id
            LEFT JOIN product_variations pv ON gi.variation_id = pv.id
            LEFT JOIN suppliers         s   ON g.supplier_id  = s.id
            LEFT JOIN warehouses        w   ON g.warehouse_id = w.id
            LEFT JOIN users             u   ON g.received_by  = u.id
            LEFT JOIN users             uc  ON g.created_by   = uc.id
            -- Batch: prefer the batch directly linked to the GRN item (gi.batch_id),
            -- fall back to any batch for this grn+product+variation combination.
            LEFT JOIN product_batches   pb
                ON  pb.batch_id    = gi.batch_id
                AND pb.product_id  = gi.product_id
                AND (gi.variation_id IS NULL OR pb.variation_id = gi.variation_id)
            WHERE gi.product_id = %s
        """
        params = [product_id]
 
        # ── optional filters ─────────────────────────────────────────
        if warehouse_id:
            query += " AND g.warehouse_id = %s"
            params.append(warehouse_id)
 
        if variation_id:
            query += " AND gi.variation_id = %s"
            params.append(variation_id)
 
        # grn_date is DATE, so direct string comparison works fine
        if start_date and end_date:
            query += " AND g.grn_date BETWEEN %s AND %s"
            params.extend([start_date, end_date])
 
        query += " ORDER BY g.grn_date DESC, g.grn_id DESC"
 
        cursor.execute(query, params)
        rows = cursor.fetchall()
 
        results = []
        for row in rows:
            # ── date formatting ──────────────────────────────────────
            def fmt_date(val):
                if not val:
                    return None
                if isinstance(val, str):
                    return val[:10]
                return val.strftime("%Y-%m-%d")
 
            # accepted_quantity is a STORED GENERATED column; always present
            # but guard against edge-case NULL just in case
            accepted = row.get('accepted_quantity')
            if accepted is None:
                accepted = (float(row['received_quantity'] or 0)
                            - float(row['rejected_quantity'] or 0))
 
            results.append({
                # ── GRN header ───────────────────────────────────────
                "grn_id":               row["grn_id"],
                "grn_code":             row["grn_code"],
                "grn_date":             fmt_date(row["grn_date"]),
                "grn_status":           row["grn_status"],
                "purchase_order_id":    row["purchase_order_id"],
                "invoice_number":       row["invoice_number"],
                "invoice_date":         fmt_date(row["invoice_date"]),
                "vehicle_number":       row["vehicle_number"],
                "driver_name":          row["driver_name"],
                "driver_contact":       row["driver_contact"],
                "note":                 row["grn_note"],
                "grn_grand_total":      float(row["grn_grand_total"]  or 0),
                "grn_subtotal":         float(row["grn_subtotal"]     or 0),
                "grn_tax":              float(row["grn_tax"]          or 0),
                "grn_discount":         float(row["grn_discount"]     or 0),
                "grn_payment_status":   row["grn_payment_status"],
                "warehouse_id":         row["warehouse_id"],
                "store_id":             row["store_id"],
 
                # ── Parties ──────────────────────────────────────────
                "supplier_name":        row["supplier_name"]    or "N/A",
                "warehouse_name":       row["warehouse_name"]   or "N/A",
                "received_by_name":     row["received_by_name"],
                "created_by_name":      row["created_by_name"],
 
                # ── GRN Item ─────────────────────────────────────────
                "grn_item_id":          row["grn_item_id"],
                "ordered_quantity":     float(row["ordered_quantity"]   or 0),
                "received_quantity":    float(row["received_quantity"]  or 0),
                "rejected_quantity":    float(row["rejected_quantity"]  or 0),
                "accepted_quantity":    float(accepted                  or 0),
                "unit_price":           float(row["unit_price"]         or 0),
                "net_unit_cost":        float(row["net_unit_cost"]      or 0),
                "batch_price":          float(row["batch_price"]        or 0),
                "item_our_price":       float(row["item_our_price"]     or 0),
                "item_discount":        float(row["item_discount"]      or 0),
                "item_tax":             float(row["item_tax"]           or 0),
                "product_discount":     float(row["product_discount"]   or 0),
                "product_tax":          float(row["product_tax"]        or 0),
                "subtotal":             float(row["subtotal"]           or 0),
                "purchase_unit":        row["purchase_unit"],
                "expiration_date":      fmt_date(row["expiration_date"]),
                "manufacturing_date":   fmt_date(row["manufacturing_date"]),
                "quality_check":        row["quality_check"] or "pending",
                "rejection_reason":     row["rejection_reason"],
                "item_note":            row["item_note"],
 
                # ── Product ──────────────────────────────────────────
                "product_id":           row["product_id"],
                "product_name":         row["product_name"],
                "product_sku":          row["product_sku"],
                "product_code":         row["product_sku"],   # alias for formatProductName()
                "sku":                  row["product_sku"],   # alias for filterGRN() search
                "product_type":         row["product_type"],
 
                # ── Variation ────────────────────────────────────────
                "variation_id":         row["variation_id"],
                "variation_name":       row["variation_name"],
                "variation_type":       row["variation_type"],
                "variation_sku":        row["variation_sku"],
 
                # ── Batch ────────────────────────────────────────────
                "batch_id":             row["batch_id"],
                "batch_number":         row["batch_number"],
                "batch_total_quantity": float(row["batch_total_quantity"] or 0),
                "remaining_quantity":   float(row["remaining_quantity"]   or 0),
                "batch_cost":           float(row["batch_cost"]           or 0),
                "batch_price_sell":     float(row["batch_price_sell"]     or 0),
                "batch_our_price":      float(row["batch_our_price"]      or 0),
                "batch_expiration_date":fmt_date(row["batch_expiration_date"]),
            })
 
        return jsonify(results), 200
 
    except Exception as e:
        print(f"❌ Error in get_grn_by_product: {e}")
        import traceback
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500
 
    finally:
        cursor.close()
        conn.close()
 
 
# -------------------------------------------------------------------
# 1️⃣ VIEW PRODUCT PURCHASES (with warehouse filter)
# -------------------------------------------------------------------
@report_bp.route('/view_product_stock/<int:product_id>', methods=['GET'])
@jwt_required()
@role_required('admin')
def view_product_purchases(product_id):
    """
    Get purchase history for a specific product.
    Joins: purchase_orders → order_items → products → product_variations
           → suppliers → warehouses → units → product_batches → grn
 
    Schema notes (document 5):
        purchase_orders:
            order_id, order_tax, discount, status, grn_status,
            grand_total, supplier_id, payment_status, payment_type,
            paid_amount, due_amount, warehouse_id, store_id,
            note, created_on (TIMESTAMP), updated_on
 
        order_items:
            item_id, order_id, product_id, variation_id,
            discount_type, product_discount, tax_type, product_tax,
            quantity, unit_price, net_unit_cost, selling_price,
            purchase_unit, discount, tax, subtotal,
            created_on, expiration_date (DATE)
 
        product_batches:
            batch_id, product_id, variation_id, batch_number,
            quantity, remaining_quantity, cost, price,
            our_price (ALTER TABLE added),
            expiration_date, purchase_order_id, grn_id, created_on
 
    Filters: variation_id, warehouse_id, start/end date
 
    Frontend (purchase tab) expects per row:
        order_date, order_id (→ PO-xxx), product_name, product_code / sku,
        variation_name, variation_type, variation_sku,
        supplier_name, status / purchase_status,
        batch_number / batch_id,
        quantity / ordered_quantity, remaining_quantity, subtotal,
        warehouse_name, grn_code, grn_status
    """
    conn   = get_db_connection()
    cursor = conn.cursor(dictionary=True)
 
    try:
        variation_id = request.args.get('variation_id', type=int)
        warehouse_id = request.args.get('warehouse_id', type=int)
        start_date   = request.args.get('start')
        end_date     = request.args.get('end')
 
        query = """
            SELECT
                -- Purchase Order
                po.order_id,
                po.created_on           AS order_date,
                po.status               AS purchase_status,
                po.grn_status,
                po.grand_total,
                po.order_tax,
                po.discount             AS order_discount,
                po.payment_status,
                po.payment_type,
                po.paid_amount,
                po.due_amount,
                po.warehouse_id,
                po.store_id,
                po.note,
 
                -- Supplier
                s.supplier_name,
 
                -- Warehouse
                w.warehouse_name,
 
                -- Product
                p.id                    AS product_id,
                p.product_name,
                p.sku                   AS product_sku,
                p.product_type,
 
                -- Variation (all 3 fields required by formatProductNameHTML)
                pv.id                   AS variation_id,
                pv.variation_name,
                pv.variation_type,
                pv.variation_sku,
 
                -- Order Item
                oi.item_id,
                oi.quantity             AS ordered_quantity,
                oi.unit_price,
                oi.net_unit_cost,
                oi.selling_price,
                oi.subtotal,
                oi.purchase_unit,
                oi.discount             AS item_discount,
                oi.tax                  AS item_tax,
                oi.product_discount,
                oi.product_tax,
                oi.expiration_date      AS item_expiration_date,
 
                -- Batch (LEFT JOIN: a PO row may not have a batch yet)
                pb.batch_id,
                pb.batch_number,
                pb.quantity             AS batch_quantity,
                pb.remaining_quantity,
                pb.cost                 AS batch_cost,
                pb.price                AS batch_price,
                pb.our_price            AS batch_our_price,
                pb.expiration_date      AS batch_expiration_date,
 
                -- GRN (via batch)
                g.grn_id,
                g.grn_code,
                g.grn_date,
                g.status                AS grn_status,
 
                -- Unit
                u.unit_short            AS unit,
                u.unit_name
            FROM purchase_orders po
            INNER JOIN order_items      oi  ON po.order_id      = oi.order_id
            INNER JOIN products         p   ON oi.product_id    = p.id
            LEFT JOIN  product_variations pv ON oi.variation_id  = pv.id
            LEFT JOIN  suppliers        s   ON po.supplier_id   = s.id
            LEFT JOIN  warehouses       w   ON po.warehouse_id  = w.id
            LEFT JOIN  units            u   ON p.sale_unit_id   = u.id
            -- Batch: match on purchase_order_id + product_id + variation_id
            -- This is the correct non-multiplying join.
            LEFT JOIN  product_batches  pb
                ON  pb.purchase_order_id = po.order_id
                AND pb.product_id        = oi.product_id
                AND (oi.variation_id IS NULL OR pb.variation_id = oi.variation_id)
            -- GRN via the batch's grn_id
            LEFT JOIN  grn              g   ON pb.grn_id        = g.grn_id
            WHERE oi.product_id = %s
        """
        params = [product_id]
 
        # ── optional filters ─────────────────────────────────────────
        if warehouse_id:
            query += " AND po.warehouse_id = %s"
            params.append(warehouse_id)
 
        if variation_id:
            query += " AND oi.variation_id = %s"
            params.append(variation_id)
 
        # created_on is TIMESTAMP → use DATE() for day-level comparison
        if start_date and end_date:
            query += " AND DATE(po.created_on) BETWEEN %s AND %s"
            params.extend([start_date, end_date])
 
        query += " ORDER BY po.created_on DESC, pb.batch_number"
 
        cursor.execute(query, params)
        rows = cursor.fetchall()
 
        purchases = []
        for row in rows:
            # ── build display product name ────────────────────────────
            product_display = row["product_name"] or ""
 
            # ── date helpers ─────────────────────────────────────────
            def fmt_dt(val, fmt="%Y-%m-%d %H:%M:%S"):
                if not val:
                    return None
                if isinstance(val, str):
                    return val
                return val.strftime(fmt)
 
            def fmt_date(val):
                if not val:
                    return None
                if isinstance(val, str):
                    return val[:10]
                return val.strftime("%Y-%m-%d")
 
            # expiration_date: prefer batch-level, fall back to order-item level
            expiry = row["batch_expiration_date"] or row["item_expiration_date"]
 
            purchases.append({
                # ── PO ───────────────────────────────────────────────
                "order_id":          row["order_id"],
                "order_date":        fmt_dt(row["order_date"]),
                "purchase_status":   row["purchase_status"],
                "status":            row["purchase_status"],    # alias for frontend
                "grn_status":        row["grn_status"],
                "grand_total":       float(row["grand_total"]       or 0),
                "order_tax":         float(row["order_tax"]         or 0),
                "order_discount":    float(row["order_discount"]    or 0),
                "payment_status":    row["payment_status"],
                "payment_type":      row["payment_type"],
                "paid_amount":       float(row["paid_amount"]       or 0),
                "due_amount":        float(row["due_amount"]        or 0),
                "warehouse_id":      row["warehouse_id"],
                "store_id":          row["store_id"],
                "note":              row["note"],
 
                # ── Supplier / location ───────────────────────────────
                "supplier_name":     row["supplier_name"]  or "N/A",
                "warehouse_name":    row["warehouse_name"],
 
                # ── Product identity ─────────────────────────────────
                "product_id":        row["product_id"],
                "product_name":      product_display,
                "product_sku":       row["product_sku"],
                "product_code":      row["product_sku"],   # alias for formatProductName()
                "sku":               row["product_sku"],   # alias for filterPurchase() search
                "product_type":      row["product_type"],
 
                # ── Variation ────────────────────────────────────────
                "variation_id":      row["variation_id"],
                "variation_name":    row["variation_name"],
                "variation_type":    row["variation_type"],
                "variation_sku":     row["variation_sku"],
 
                # ── Order item quantities / pricing ──────────────────
                "item_id":           row["item_id"],
                "quantity":          float(row["ordered_quantity"]  or 0),
                "ordered_quantity":  float(row["ordered_quantity"]  or 0),
                "unit_price":        float(row["unit_price"]        or 0),
                "net_unit_cost":     float(row["net_unit_cost"]     or 0),
                "selling_price":     float(row["selling_price"]     or 0),
                "subtotal":          float(row["subtotal"]          or 0),
                "purchase_unit":     row["purchase_unit"],
                "item_discount":     float(row["item_discount"]     or 0),
                "item_tax":          float(row["item_tax"]          or 0),
                "product_discount":  float(row["product_discount"]  or 0),
                "product_tax":       float(row["product_tax"]       or 0),
 
                # ── Batch ────────────────────────────────────────────
                "batch_id":          row["batch_id"],
                "batch_number":      row["batch_number"],
                "batch_quantity":    float(row["batch_quantity"]    or 0),
                "remaining_quantity":float(row["remaining_quantity"]or 0),
                "batch_cost":        float(row["batch_cost"]        or 0),
                "batch_price":       float(row["batch_price"]       or 0),
                "batch_our_price":   float(row["batch_our_price"]   or 0),
                "expiration_date":   fmt_date(expiry),
 
                # ── GRN ──────────────────────────────────────────────
                "grn_id":            row["grn_id"],
                "grn_code":          row["grn_code"],
                "grn_date":          fmt_date(row["grn_date"]),
                "grn_status":        row["grn_status"],
 
                # ── Unit ─────────────────────────────────────────────
                "unit":              row["unit"]      or "",
                "unit_name":         row["unit_name"] or "",
            })
 
        return jsonify(purchases), 200
 
    except Exception as e:
        print(f"❌ Error in view_product_purchases: {e}")
        import traceback
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500
 
    finally:
        cursor.close()
        conn.close()
 
# -------------------------------------------------------------------
# 2️⃣ VIEW PRODUCT PURCHASE RETURNS (with warehouse filter)
# -------------------------------------------------------------------
@report_bp.route('/view_product_stock_purchase_return/<int:product_id>', methods=['GET'])
@jwt_required()
@role_required('admin')
def view_product_purchase_returns(product_id):
    """
    Get purchase return history for a specific product.
 
    Joins:
        purchase_returns        pr
        purchase_orders         po   (for purchase_id / order_id)
        suppliers               s    (pr.supplier_id → s.id)
        warehouses              w    (pr.warehouse_id → w.id)
        purchase_return_items   pri  (pr.return_id → pri.return_id)
        products                p    (pri.product_id → p.id)
        product_variations      pv   (pri.variation_id → pv.id)
 
    Schema fields used (document 5):
        purchase_returns:
            return_id, order_id, supplier_id, return_note,
            return_tax, return_discount, return_status,
            return_total, created_on, warehouse_id, store_id
 
        purchase_return_items:
            return_item_id, return_id, product_id, variation_id,
            quantity, price, unit_price, discount, tax,
            subtotal, product_discount, product_tax,
            discount_type, tax_type, created_on
 
        products:   id, product_name, sku
        product_variations: id, variation_name, variation_type, variation_sku
 
    Frontend (purchase-return tab) expects per row:
        created_on      → formatted timestamp
        return_id       → displayed as PR-{return_id}
        product_name    → used by formatProductNameHTML()
        product_code    → p.sku
        sku             → p.sku  (alias)
        variation_name  → used by formatProductNameHTML()
        variation_type  → used by formatProductNameHTML()
        variation_sku   → used by formatProductNameHTML()
        supplier_name
        quantity        → fmtNum()
        subtotal        → fmtNum()
        warehouse_name
    """
    conn   = get_db_connection()
    cursor = conn.cursor(dictionary=True)
 
    try:
        variation_id = request.args.get('variation_id', type=int)
        warehouse_id = request.args.get('warehouse_id', type=int)
        start_date   = request.args.get('start')
        end_date     = request.args.get('end')
 
        query = """
            SELECT
                -- purchase_returns header
                pr.return_id,
                pr.warehouse_id,
                pr.store_id,
                pr.return_status,
                pr.return_total,
                pr.return_note,
                pr.return_tax,
                pr.return_discount,
                pr.created_on,
 
                -- linked purchase order
                po.order_id             AS purchase_id,
 
                -- supplier
                s.supplier_name,
 
                -- warehouse
                w.warehouse_name,
 
                -- purchase_return_items line
                pri.return_item_id,
                pri.product_id,
                pri.variation_id,
                pri.quantity,
                pri.price,
                pri.unit_price,
                pri.subtotal,
                pri.discount,
                pri.tax,
                pri.product_discount,
                pri.product_tax,
                pri.discount_type,
                pri.tax_type,
 
                -- product
                p.product_name,
                p.sku                   AS product_code,
 
                -- variation  (all 3 fields needed by formatProductNameHTML)
                pv.variation_name,
                pv.variation_type,
                pv.variation_sku
            FROM purchase_returns pr
            JOIN  purchase_orders         po  ON pr.order_id    = po.order_id
            JOIN  suppliers               s   ON pr.supplier_id = s.id
            LEFT JOIN warehouses          w   ON pr.warehouse_id = w.id
            JOIN  purchase_return_items   pri ON pr.return_id   = pri.return_id
            JOIN  products                p   ON pri.product_id = p.id
            LEFT JOIN product_variations  pv  ON pri.variation_id = pv.id
            WHERE pri.product_id = %s
        """
        params = [product_id]
 
        # ── optional filters ────────────────────────────────────────
        if warehouse_id:
            query += " AND pr.warehouse_id = %s"
            params.append(warehouse_id)
 
        if variation_id:
            query += " AND pri.variation_id = %s"
            params.append(variation_id)
 
        if start_date and end_date:
            query += " AND pr.created_on BETWEEN %s AND %s"
            params.extend([start_date + " 00:00:00", end_date + " 23:59:59"])
 
        query += " ORDER BY pr.created_on DESC"
 
        cursor.execute(query, params)
        data = cursor.fetchall()
 
        # ── serialise each row ──────────────────────────────────────
        for row in data:
            # datetime → string
            if row.get("created_on") and isinstance(row["created_on"], datetime):
                row["created_on"] = row["created_on"].strftime("%Y/%m/%d %I:%M:%S %p")
 
            # Decimal → float  (mysql-connector returns Decimal for DECIMAL cols)
            for field in (
                'quantity', 'price', 'unit_price', 'subtotal',
                'discount', 'tax', 'product_discount', 'product_tax',
                'return_total', 'return_tax', 'return_discount',
            ):
                if row.get(field) is not None:
                    row[field] = float(row[field])
 
            # add sku alias so frontend formatProductName() finds it
            row['sku'] = row.get('product_code')
 
        return jsonify(data), 200
 
    except Exception as e:
        print("❌ Error in view_product_purchase_returns:", str(e))
        import traceback; traceback.print_exc()
        return jsonify({"error": str(e)}), 500
 
    finally:
        cursor.close()
        conn.close()
# -------------------------------------------------------------------
# 3️⃣ SALES BY PRODUCT (with warehouse filter)
# -------------------------------------------------------------------
@report_bp.route('/get_sales_by_product/<int:product_id>', methods=['GET'])
@jwt_required()
@role_required('admin')
def get_sales_by_product(product_id):
    """
    Get sales history for a specific product.
    Joins: sale_products → invoice_sale → products → product_variations
           → warehouses → customers
    Filters: variation_id, warehouse_id, start/end date
    """
    variation_id = request.args.get('variation_id', default=None, type=int)
    warehouse_id = request.args.get('warehouse_id', default=None, type=int)
    start_date   = request.args.get('start')
    end_date     = request.args.get('end')
 
    conn   = None
    cursor = None
    try:
        conn   = get_db_connection()
        cursor = conn.cursor(dictionary=True)
 
        # ── Core query ──────────────────────────────────────────────
        # NOTE: customer_name is aliased "patient_name" to match the
        # original code and the frontend column header "Patient".
        query = """
            SELECT
                inv.invoice_code,
                inv.sale_date,
                inv.warehouse_id,
                inv.status              AS invoice_status,
                inv.payment_status,
 
                sp.sale_product_id,
                sp.product_id,
                sp.variation_id,
                sp.price,
                sp.quantity,
                sp.total,
                sp.sale_unit,
                sp.discount             AS product_discount,
                sp.tax                  AS product_tax,
 
                p.product_name,
                p.sku                   AS product_code,
 
                pv.variation_name,
                pv.variation_type,
                pv.variation_sku,
 
                w.warehouse_name,
 
                c.name                  AS customer_name,
                c.name                  AS patient_name
            FROM sale_products sp
            JOIN invoice_sale          inv ON sp.invoice_id   = inv.invoice_id
            JOIN products              p   ON sp.product_id   = p.id
            LEFT JOIN product_variations pv ON sp.variation_id = pv.id
            LEFT JOIN warehouses       w   ON inv.warehouse_id = w.id
            LEFT JOIN customers        c   ON inv.customer_id  = c.id
            WHERE sp.product_id = %s
        """
        params = [product_id]
 
        # ── Optional filters ────────────────────────────────────────
        if warehouse_id:
            query += " AND inv.warehouse_id = %s"
            params.append(warehouse_id)
 
        if variation_id:
            query += " AND sp.variation_id = %s"
            params.append(variation_id)
 
        # Date filter: use full day range (00:00:00 → 23:59:59)
        if start_date and end_date:
            query += " AND inv.sale_date BETWEEN %s AND %s"
            params.extend([start_date + " 00:00:00", end_date + " 23:59:59"])
 
        query += " ORDER BY inv.sale_date DESC"
 
        cursor.execute(query, params)
        results = cursor.fetchall()
 
        # ── Format datetime for JSON ─────────────────────────────────
        for row in results:
            if row.get('sale_date'):
                row['sale_date'] = datetime.strftime(
                    row['sale_date'], "%Y/%m/%d %I:%M:%S %p"
                )
            # Ensure Decimal fields are plain floats
            for field in ('price', 'quantity', 'total',
                          'product_discount', 'product_tax'):
                if row.get(field) is not None:
                    row[field] = float(row[field])
 
        return jsonify(results), 200
 
    except Exception as e:
        print("❌ Error in get_sales_by_product:", e)
        import traceback; traceback.print_exc()
        return jsonify({"error": str(e)}), 500
 
    finally:
        if cursor: cursor.close()
        if conn:   conn.close()
 
 
# -------------------------------------------------------------------
# 4️⃣ SALES RETURN BY PRODUCT (with warehouse filter)
# -------------------------------------------------------------------
@report_bp.route('/get_sales_return_by_product/<int:product_id>', methods=['GET'])
@jwt_required()
@role_required('admin')
def get_sales_return_by_product(product_id):
    """
    Get sales return history for a specific product.
    Joins: sale_return_products → sale_return → invoice_sale → products
           → product_variations → warehouses → customers
    Filters: variation_id, warehouse_id, start/end date
 
    Schema notes (from document 5):
      • sale_return_products.return_quantity  INT(11)
      • sale_return_products.return_price     DECIMAL(10,2)
      • sale_return_products.return_total     DECIMAL(10,2)
      • sale_return.total_return_amount       DECIMAL(10,2)  ← grand total
      • sale_return.return_date               TIMESTAMP
      • customers.name                        VARCHAR(100)
    """
    variation_id = request.args.get('variation_id', default=None, type=int)
    warehouse_id = request.args.get('warehouse_id', default=None, type=int)
    start_date   = request.args.get('start')
    end_date     = request.args.get('end')
 
    conn   = None
    cursor = None
    try:
        conn   = get_db_connection()
        cursor = conn.cursor(dictionary=True)
 
        # ── Core query ──────────────────────────────────────────────
        # return_total  = line-level subtotal  (sale_return_products)
        # total_return_amount = invoice-level grand total (sale_return)
        # Frontend summary card sums return_total per row.
        query = """
            SELECT
                sr.return_id,
                sr.return_date,
                sr.warehouse_id,
                sr.total_return_amount,
                sr.reason,
                sr.status               AS return_status,
 
                inv.invoice_code,
 
                srp.return_product_id,
                srp.product_id,
                srp.variation_id,
                srp.return_quantity,
                srp.return_price,
                srp.return_total,
                srp.product_discount,
                srp.product_tax,
                srp.sale_unit,
 
                p.product_name,
                p.sku                   AS product_code,
 
                pv.variation_name,
                pv.variation_type,
                pv.variation_sku,
 
                w.warehouse_name,
 
                c.name                  AS customer_name,
                c.name                  AS patient_name
            FROM sale_return_products srp
            JOIN sale_return           sr  ON srp.return_id   = sr.return_id
            JOIN invoice_sale          inv ON sr.invoice_id   = inv.invoice_id
            JOIN products              p   ON srp.product_id  = p.id
            LEFT JOIN product_variations pv ON srp.variation_id = pv.id
            LEFT JOIN warehouses       w   ON sr.warehouse_id  = w.id
            LEFT JOIN customers        c   ON sr.customer_id   = c.id
            WHERE srp.product_id = %s
        """
        params = [product_id]
 
        # ── Optional filters ────────────────────────────────────────
        if warehouse_id:
            query += " AND sr.warehouse_id = %s"
            params.append(warehouse_id)
 
        if variation_id:
            query += " AND srp.variation_id = %s"
            params.append(variation_id)
 
        # Date filter: use full day range (00:00:00 → 23:59:59)
        if start_date and end_date:
            query += " AND sr.return_date BETWEEN %s AND %s"
            params.extend([start_date + " 00:00:00", end_date + " 23:59:59"])
 
        query += " ORDER BY sr.return_date DESC"
 
        cursor.execute(query, params)
        results = cursor.fetchall()
 
        # ── Format datetime + Decimal for JSON ───────────────────────
        for row in results:
            if row.get('return_date'):
                row['return_date'] = datetime.strftime(
                    row['return_date'], "%Y/%m/%d %I:%M:%S %p"
                )
            for field in ('return_quantity', 'return_price', 'return_total',
                          'total_return_amount', 'product_discount', 'product_tax'):
                if row.get(field) is not None:
                    row[field] = float(row[field])
 
        return jsonify(results), 200
 
    except Exception as e:
        print("❌ Error in get_sales_return_by_product:", e)
        import traceback; traceback.print_exc()
        return jsonify({"error": str(e)}), 500
 
    finally:
        if cursor: cursor.close()
        if conn:   conn.close()

@report_bp.route('/dashboard_view_product_alert', methods=['GET'])
@jwt_required()
@role_required('admin', 'user', 'cashier')
def dashboard_view_product_alert():
    conn = None
    cursor = None
    
    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)
        
        cursor.execute("""
            SELECT 
                p.id,
                p.sku as product_code,
                p.product_name,
                NULL as variation_name,
                NULL as variation_type,
                COALESCE(SUM(ws.quantity), 0) as quantity,
                p.stock_alert as alert_quantity,
                u.unit_name as unit
            FROM products p
            -- ✅ Only products that have at least one GRN
            INNER JOIN (
                SELECT DISTINCT product_id 
                FROM grn_items
            ) grn_products ON p.id = grn_products.product_id
            LEFT JOIN warehouse_stock ws ON p.id = ws.product_id AND ws.variation_id IS NULL
            LEFT JOIN units u ON p.sale_unit_id = u.id
            WHERE p.product_type = 'single'
            GROUP BY p.id, p.sku, p.product_name, p.stock_alert, u.unit_name
            HAVING quantity <= p.stock_alert
            
            UNION ALL
            
            SELECT 
                pv.id,
                pv.variation_sku as product_code,
                p.product_name,
                pv.variation_name,
                pv.variation_type,
                COALESCE(SUM(ws.quantity), 0) as quantity,
                pv.variation_stock_alert as alert_quantity,
                u.unit_name as unit
            FROM product_variations pv
            JOIN products p ON pv.product_id = p.id
            -- ✅ Only variations that have at least one GRN
            INNER JOIN (
                SELECT DISTINCT variation_id 
                FROM grn_items 
                WHERE variation_id IS NOT NULL
            ) grn_variations ON pv.id = grn_variations.variation_id
            LEFT JOIN warehouse_stock ws ON pv.id = ws.variation_id
            LEFT JOIN units u ON p.sale_unit_id = u.id
            WHERE p.product_type = 'variable'
            GROUP BY pv.id, pv.variation_sku, p.product_name, pv.variation_name, pv.variation_type, pv.variation_stock_alert, u.unit_name
            HAVING quantity <= pv.variation_stock_alert
            
            ORDER BY quantity ASC
            LIMIT 10
        """)
        
        products = cursor.fetchall()
        
        result = []
        for product in products:
            if product['variation_name'] and product['variation_type']:
                display_name = f"{product['product_name']} - {product['variation_name']} ({product['variation_type']})"
            else:
                display_name = product['product_name']
            
            result.append({
                "product_id": product['id'],
                "product_code": product['product_code'] or 'N/A',
                "product_name": display_name,
                "quantity": float(product['quantity'] or 0),
                "alert_quantity": float(product['alert_quantity'] or 0),
                "unit": product['unit'] or 'units'
            })
        
        return jsonify(result), 200
        
    except Exception as e:
        logger.error(f"❌ Error in dashboard_view_product_alert: {e}")
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500
        
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()

@report_bp.route('/get_supplier_summary', methods=['GET'])
@jwt_required()
@role_required('admin')
def get_supplier_summary():
    conn = None
    cursor = None

    try:
        store_id     = request.args.get('store_id',     type=int)
        warehouse_id = request.args.get('warehouse_id', type=int)

        conn   = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        # ✅ Use separate subqueries for PO and GRN to avoid row multiplication
        # When you JOIN both PO and GRN directly, COUNT gets inflated due to
        # cross-joining (e.g., 3 POs × 4 GRNs = 12 rows per supplier)

        po_conditions  = ["po.supplier_id = s.id"]
        grn_conditions = ["g.supplier_id  = s.id"]
        po_params      = []
        grn_params     = []

        if store_id:
            po_conditions.append("po.store_id = %s")
            po_params.append(store_id)
            grn_conditions.append("g.store_id = %s")
            grn_params.append(store_id)

        if warehouse_id:
            po_conditions.append("po.warehouse_id = %s")
            po_params.append(warehouse_id)
            grn_conditions.append("g.warehouse_id = %s")
            grn_params.append(warehouse_id)

        po_where  = " AND ".join(po_conditions)
        grn_where = " AND ".join(grn_conditions)

        query = f"""
            SELECT
                s.id            AS supplier_id,
                s.supplier_name,
                s.supplier_contact,

                -- ✅ PO stats via correlated subquery (avoids JOIN multiplication)
                COALESCE((
                    SELECT COUNT(DISTINCT po.order_id)
                    FROM purchase_orders po
                    WHERE {po_where}
                ), 0) AS purchase_count,

                COALESCE((
                    SELECT SUM(po.grand_total)
                    FROM purchase_orders po
                    WHERE {po_where}
                ), 0) AS total_purchase,

                -- ✅ GRN stats via correlated subquery
                COALESCE((
                    SELECT COUNT(DISTINCT g.grn_id)
                    FROM grn g
                    WHERE {grn_where}
                ), 0) AS grn_count,

                COALESCE((
                    SELECT SUM(g.grand_total)
                    FROM grn g
                    WHERE {grn_where}
                ), 0) AS total_grn,

                -- ✅ Extra useful fields
                COALESCE((
                    SELECT SUM(po.due_amount)
                    FROM purchase_orders po
                    WHERE {po_where}
                ), 0) AS total_due,

                COALESCE((
                    SELECT SUM(po.paid_amount)
                    FROM purchase_orders po
                    WHERE {po_where}
                ), 0) AS total_paid,

                -- ✅ Last purchase date
                (
                    SELECT MAX(po.created_on)
                    FROM purchase_orders po
                    WHERE {po_where}
                ) AS last_purchase_date,

                -- ✅ Last GRN date
                (
                    SELECT MAX(g.grn_date)
                    FROM grn g
                    WHERE {grn_where}
                ) AS last_grn_date

            FROM suppliers s
            ORDER BY s.supplier_name ASC
        """

        # ✅ Each subquery uses its own params — build the full params tuple
        # Order: purchase_count, total_purchase, grn_count, total_grn,
        #        total_due, total_paid, last_purchase_date, last_grn_date
        # = 4× PO params + 4× GRN params
        all_params = (
            po_params +   # purchase_count
            po_params +   # total_purchase
            grn_params +  # grn_count
            grn_params +  # total_grn
            po_params +   # total_due
            po_params +   # total_paid
            po_params +   # last_purchase_date
            grn_params    # last_grn_date
        )

        cursor.execute(query, all_params)
        rows = cursor.fetchall()

        data = []
        for row in rows:
            last_purchase = row['last_purchase_date']
            last_grn      = row['last_grn_date']

            data.append({
                "supplier_id":       row['supplier_id'],
                "supplier_name":     row['supplier_name']    or '-',
                "contact_number":    row['supplier_contact'] or '-',
                "purchase_count":    int(row['purchase_count']  or 0),
                "total_purchase":    float(row['total_purchase'] or 0),
                "grn_count":         int(row['grn_count']        or 0),
                "total_grn":         float(row['total_grn']      or 0),
                "total_due":         float(row['total_due']      or 0),
                "total_paid":        float(row['total_paid']     or 0),
                "last_purchase_date": last_purchase.strftime('%Y-%m-%d') if last_purchase else None,
                "last_grn_date":      last_grn.strftime('%Y-%m-%d')      if last_grn      else None,
            })

        return jsonify(data), 200

    except Exception as e:
        print(f"❌ Error in get_supplier_summary: {e}")
        import traceback
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500

    finally:
        try:
            if cursor: cursor.close()
        except Exception:
            pass
        try:
            if conn: conn.close()
        except Exception:
            pass

# ===========================
# 2️⃣ SUPPLIER ORDERS - FIXED
# ===========================
@report_bp.route('/get_supplier_orders/<int:supplier_id>', methods=['GET'])
@jwt_required()
@role_required('admin')
def get_supplier_orders(supplier_id):
    conn = None
    cursor = None
    try:
        conn   = get_db_connection()
        cursor = conn.cursor(dictionary=True)  # ✅ dictionary=True

        cursor.execute("""
            SELECT
                po.order_id,
                po.note,
                po.order_tax,
                po.discount          AS order_discount,
                po.status,
                po.grn_status,
                po.grand_total,
                po.payment_status,
                po.paid_amount,
                po.due_amount,
                po.created_on        AS order_date,
                oi.product_id,
                oi.variation_id,
                oi.quantity,
                oi.discount          AS item_discount,
                oi.tax               AS item_tax,
                oi.subtotal,
                oi.unit_price,
                oi.net_unit_cost,
                oi.selling_price,
                p.product_name,
                p.sku,
                pv.variation_name,
                pv.variation_type,
                pv.variation_sku,
                pv.variation_cost,
                pv.variation_price,
                s.supplier_name
            FROM purchase_orders po
            LEFT JOIN suppliers s    ON po.supplier_id  = s.id
            LEFT JOIN order_items oi ON po.order_id     = oi.order_id
            LEFT JOIN products p     ON oi.product_id   = p.id
            LEFT JOIN product_variations pv ON oi.variation_id = pv.id
            WHERE po.supplier_id = %s
            ORDER BY po.order_id DESC
        """, (supplier_id,))

        rows   = cursor.fetchall()
        orders = {}

        for row in rows:
            oid = row['order_id']
            if oid not in orders:
                order_date = row['order_date']
                orders[oid] = {
                    'order_id':       oid,
                    'note':           row['note'],
                    'order_tax':      float(row['order_tax']      or 0),
                    'order_discount': float(row['order_discount'] or 0),
                    'status':         row['status'],
                    'grn_status':     row['grn_status'],
                    'grand_total':    float(row['grand_total']    or 0),
                    'payment_status': row['payment_status'],
                    'paid_amount':    float(row['paid_amount']    or 0),
                    'due_amount':     float(row['due_amount']     or 0),
                    'order_date':     order_date.strftime("%Y-%m-%d %H:%M:%S") if order_date else None,
                    'supplier_name':  row['supplier_name'],
                    'items':          []
                }

            if row['product_id'] is not None:
                orders[oid]['items'].append({
                    'product_id':      row['product_id'],
                    'variation_id':    row['variation_id'],
                    'quantity':        float(row['quantity']        or 0),
                    'item_discount':   float(row['item_discount']   or 0),
                    'item_tax':        float(row['item_tax']        or 0),
                    'subtotal':        float(row['subtotal']        or 0),
                    'unit_price':      float(row['unit_price']      or 0),
                    'net_unit_cost':   float(row['net_unit_cost']   or 0),
                    'selling_price':   float(row['selling_price']   or 0),
                    'product_name':    row['product_name'],
                    'sku':             row['sku'],
                    'variation_name':  row['variation_name'],
                    'variation_type':  row['variation_type'],
                    'variation_sku':   row['variation_sku'],
                    'variation_cost':  float(row['variation_cost']  or 0),
                    'variation_price': float(row['variation_price'] or 0),
                })

        return jsonify(list(orders.values())), 200

    except Exception as e:
        print(f"❌ get_supplier_orders error: {e}")
        import traceback; traceback.print_exc()
        return jsonify({"error": str(e)}), 500
    finally:
        try:
            if cursor: cursor.close()
        except: pass
        try:
            if conn: conn.close()
        except: pass


# ===========================
# 3️⃣ SUPPLIER PURCHASE RETURNS - FIXED
# ===========================
@report_bp.route('/get_supplier_return_orders/<int:supplier_id>', methods=['GET'])
@jwt_required()
@role_required('admin')
def get_supplier_return_orders(supplier_id):
    conn = None
    cursor = None
    try:
        conn   = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        cursor.execute("""
            SELECT
                pr.return_id,
                pr.order_id          AS purchase_id,
                s.supplier_name,
                pr.return_status,
                pr.return_total,
                pr.created_on,
                po.grand_total       AS purchase_total,
                pri.product_id,
                pri.variation_id,
                pri.quantity,
                pri.discount         AS item_discount,
                pri.tax              AS item_tax,
                pri.subtotal,
                p.product_name,
                p.sku,
                pv.variation_name,
                pv.variation_type,
                pv.variation_sku
            FROM purchase_returns pr
            JOIN  purchase_orders po   ON pr.order_id    = po.order_id
            JOIN  suppliers s          ON pr.supplier_id = s.id
            LEFT JOIN purchase_return_items pri ON pr.return_id   = pri.return_id
            LEFT JOIN products p               ON pri.product_id  = p.id
            LEFT JOIN product_variations pv    ON pri.variation_id = pv.id
            WHERE pr.supplier_id = %s
            ORDER BY pr.created_on DESC
        """, (supplier_id,))

        rows    = cursor.fetchall()
        returns = {}

        for row in rows:
            rid = row['return_id']
            if rid not in returns:
                created = row['created_on']
                returns[rid] = {
                    'return_id':      rid,
                    'purchase_id':    row['purchase_id'],
                    'supplier_name':  row['supplier_name'],
                    'return_status':  row['return_status'],
                    'return_total':   float(row['return_total']    or 0),
                    'created_on':     created.strftime("%Y-%m-%d %H:%M:%S") if created else None,
                    'purchase_total': float(row['purchase_total']  or 0),
                    'items':          []
                }

            if row['product_id'] is not None:
                returns[rid]['items'].append({
                    'product_id':     row['product_id'],
                    'variation_id':   row['variation_id'],
                    'quantity':       float(row['quantity']      or 0),
                    'item_discount':  float(row['item_discount'] or 0),
                    'item_tax':       float(row['item_tax']      or 0),
                    'subtotal':       float(row['subtotal']      or 0),
                    'product_name':   row['product_name'],
                    'sku':            row['sku'],
                    'variation_name': row['variation_name'],
                    'variation_type': row['variation_type'],
                    'variation_sku':  row['variation_sku'],
                })

        return jsonify(list(returns.values())), 200

    except Exception as e:
        print(f"❌ get_supplier_return_orders error: {e}")
        import traceback; traceback.print_exc()
        return jsonify({"error": str(e)}), 500
    finally:
        try:
            if cursor: cursor.close()
        except: pass
        try:
            if conn: conn.close()
        except: pass


# ===========================
# 4️⃣ SUPPLIER GRN - FIXED
# ===========================
@report_bp.route('/get_supplier_grn/<int:supplier_id>', methods=['GET'])
@jwt_required()
@role_required('admin')
def get_supplier_grn(supplier_id):
    conn = None
    cursor = None
    try:
        conn   = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        cursor.execute("""
            SELECT
                g.grn_id,
                g.grn_code,
                g.purchase_order_id,
                g.grn_date,
                g.invoice_number,
                g.status,
                g.grand_total,
                g.payment_status,
                g.paid_amount,
                g.due_amount,
                g.subtotal,
                g.tax               AS grn_tax,
                g.discount          AS grn_discount,
                s.supplier_name,
                gi.product_id,
                gi.variation_id,
                gi.received_quantity,
                gi.accepted_quantity,
                gi.rejected_quantity,
                gi.unit_price,
                gi.net_unit_cost,
                gi.subtotal         AS item_subtotal,
                p.product_name,
                p.sku,
                pv.variation_name,
                pv.variation_type,
                pv.variation_sku
            FROM grn g
            JOIN  suppliers s          ON g.supplier_id  = s.id
            LEFT JOIN grn_items gi     ON g.grn_id       = gi.grn_id
            LEFT JOIN products p       ON gi.product_id  = p.id
            LEFT JOIN product_variations pv ON gi.variation_id = pv.id
            WHERE g.supplier_id = %s
            ORDER BY g.grn_date DESC, g.grn_id DESC
        """, (supplier_id,))

        rows = cursor.fetchall()
        grns = {}

        for row in rows:
            gid = row['grn_id']
            if gid not in grns:
                grn_date = row['grn_date']
                grns[gid] = {
                    'grn_id':            gid,
                    'grn_code':          row['grn_code'],
                    'purchase_order_id': row['purchase_order_id'],  # ✅ can be None for standalone GRNs
                    'grn_date':          grn_date.strftime("%Y-%m-%d") if grn_date else None,
                    'invoice_number':    row['invoice_number'],
                    'status':            row['status'],
                    'grand_total':       float(row['grand_total']    or 0),
                    'payment_status':    row['payment_status'],
                    'paid_amount':       float(row['paid_amount']    or 0),
                    'due_amount':        float(row['due_amount']     or 0),
                    'subtotal':          float(row['subtotal']       or 0),
                    'grn_tax':           float(row['grn_tax']        or 0),
                    'grn_discount':      float(row['grn_discount']   or 0),
                    'supplier_name':     row['supplier_name'],
                    'items':             []
                }

            if row['product_id'] is not None:
                grns[gid]['items'].append({
                    'product_id':        row['product_id'],
                    'variation_id':      row['variation_id'],
                    'product_name':      row['product_name'],
                    'sku':               row['sku'],
                    'variation_name':    row['variation_name'],
                    'variation_type':    row['variation_type'],
                    'variation_sku':     row['variation_sku'],
                    'received_quantity': float(row['received_quantity'] or 0),
                    'accepted_quantity': float(row['accepted_quantity'] or 0),
                    'rejected_quantity': float(row['rejected_quantity'] or 0),
                    'unit_price':        float(row['unit_price']        or 0),
                    'net_unit_cost':     float(row['net_unit_cost']     or 0),
                    'item_subtotal':     float(row['item_subtotal']     or 0),
                })

        return jsonify(list(grns.values())), 200

    except Exception as e:
        print(f"❌ get_supplier_grn error: {e}")
        import traceback; traceback.print_exc()
        return jsonify({"error": str(e)}), 500
    finally:
        try:
            if cursor: cursor.close()
        except: pass
        try:
            if conn: conn.close()
        except: pass



@report_bp.route('/get_customer_summary', methods=['GET'])
@jwt_required()
@role_required('admin')
def get_customer_summary():
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    try:
        cursor.execute("""
            SELECT 
                c.id AS customer_id,
                c.name AS customer_name,
                c.contact AS contact_number,
                c.email AS email,
                COUNT(DISTINCT i.invoice_id) AS total_invoices,
                IFNULL(SUM(i.invoiceTotal), 0) AS total_sales,
                IFNULL(SUM(i.discount), 0) AS total_discount,
                IFNULL(SUM(i.tax), 0) AS total_tax,
                IFNULL((
                    SELECT SUM(sp2.total)
                    FROM sale_products sp2
                    INNER JOIN invoice_sale i2 ON sp2.invoice_id = i2.invoice_id
                    WHERE i2.customer_id = c.id
                ), 0) AS total_products_value,
                IFNULL((
                    SELECT SUM(sp2.quantity)
                    FROM sale_products sp2
                    INNER JOIN invoice_sale i2 ON sp2.invoice_id = i2.invoice_id
                    WHERE i2.customer_id = c.id
                ), 0) AS total_items_sold,
                IFNULL((
                    SELECT SUM(i3.remainingAmount)
                    FROM invoice_sale i3
                    WHERE i3.customer_id = c.id
                    AND i3.payment_status IN ('partial', 'unpaid')
                ), 0) AS outstanding_amount,
                (
                    SELECT MAX(i4.sale_date)
                    FROM invoice_sale i4
                    WHERE i4.customer_id = c.id
                ) AS last_purchase_date
            FROM customers c
            INNER JOIN invoice_sale i ON i.customer_id = c.id
            WHERE i.payment_status IN ('paid', 'partial', 'unpaid')
            GROUP BY c.id, c.name, c.contact, c.email
            HAVING total_invoices > 0
            ORDER BY total_sales DESC
        """)
        customers = cursor.fetchall()

        # Build payment breakdown per customer in one query
        if customers:
            customer_ids = [c['customer_id'] for c in customers]
            placeholders = ','.join(['%s'] * len(customer_ids))

            cursor.execute(f"""
                SELECT 
                    customer_id,
                    payment_status,
                    COUNT(*) AS count,
                    SUM(invoiceTotal) AS amount
                FROM invoice_sale
                WHERE customer_id IN ({placeholders})
                GROUP BY customer_id, payment_status
            """, customer_ids)

            breakdown_rows = cursor.fetchall()

            # Index by customer_id
            breakdown_map = {}
            for row in breakdown_rows:
                cid = row['customer_id']
                if cid not in breakdown_map:
                    breakdown_map[cid] = {}
                breakdown_map[cid][row['payment_status']] = {
                    'count': row['count'],
                    'amount': float(row['amount'])
                }

            for c in customers:
                cid = c['customer_id']
                c['payment_breakdown'] = breakdown_map.get(cid, {})
                c['total_sales'] = float(c['total_sales'])
                c['total_discount'] = float(c['total_discount'])
                c['total_tax'] = float(c['total_tax'])
                c['total_products_value'] = float(c['total_products_value'])
                c['total_items_sold'] = float(c['total_items_sold'])
                c['outstanding_amount'] = float(c['outstanding_amount'])
                c['last_purchase_date'] = (
                    c['last_purchase_date'].isoformat()
                    if c['last_purchase_date'] else None
                )

        return jsonify({
            "success": True,
            "customers": customers,
            "total_customers": len(customers)
        }), 200

    except Exception as e:
        import traceback
        traceback.print_exc()
        return jsonify({"success": False, "error": str(e)}), 500

    finally:
        cursor.close()
        conn.close()


@report_bp.route('/get_customer_sales/<int:customer_id>', methods=['GET'])
@jwt_required()
@role_required('admin', 'manager', 'cashier')
def get_customer_sales(customer_id):
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    try:
        cursor.execute("""
            SELECT 
                i.invoice_id,
                i.invoice_code,
                c.name AS customer_name,
                c.contact,
                c.email,
                i.status,
                i.invoiceTotal AS grand_total,
                i.payment_status,
                i.payment_method,
                i.tenderAmount,
                i.remainingAmount,
                i.discount,
                i.tax,
                i.sale_date,
                s.store_name,
                w.warehouse_name,
                u.name AS cashier_name
            FROM invoice_sale i
            JOIN customers c ON i.customer_id = c.id
            LEFT JOIN stores s ON i.store_id = s.id
            LEFT JOIN warehouses w ON i.warehouse_id = w.id
            LEFT JOIN users u ON i.cashier_user_id = u.id
            WHERE c.id = %s
            ORDER BY i.sale_date DESC
        """, (customer_id,))
        sales = cursor.fetchall()

        if not sales:
            return jsonify({
                "success": True,
                "sales": [],
                "total_sales": 0,
                "message": "No sales found for this customer"
            }), 200

        # Fetch all product totals for this customer's invoices in one query
        invoice_ids = [s['invoice_id'] for s in sales]
        placeholders = ','.join(['%s'] * len(invoice_ids))

        cursor.execute(f"""
            SELECT 
                sp.invoice_id,
                IFNULL(SUM(sp.total), 0) AS total_products,
                IFNULL(SUM(sp.quantity), 0) AS total_quantity,
                IFNULL(SUM(sp.discount), 0) AS product_discount,
                IFNULL(SUM(sp.tax), 0) AS product_tax
            FROM sale_products sp
            WHERE sp.invoice_id IN ({placeholders})
            GROUP BY sp.invoice_id
        """, invoice_ids)

        product_totals_map = {row['invoice_id']: row for row in cursor.fetchall()}

        # Fetch all product details in one query
        cursor.execute(f"""
            SELECT 
                sp.invoice_id,
                sp.sale_product_id,
                p.product_name,
                pv.variation_name,
                sp.quantity,
                sp.price,
                sp.unit_price,
                sp.total,
                sp.discount,
                sp.tax,
                sp.sale_unit
            FROM sale_products sp
            JOIN products p ON sp.product_id = p.id
            LEFT JOIN product_variations pv ON sp.variation_id = pv.id
            WHERE sp.invoice_id IN ({placeholders})
        """, invoice_ids)

        products_map = {}
        for p in cursor.fetchall():
            iid = p['invoice_id']
            if iid not in products_map:
                products_map[iid] = []
            p['quantity'] = float(p.get('quantity', 0))
            p['price'] = float(p.get('price', 0))
            p['unit_price'] = float(p.get('unit_price', 0))
            p['total'] = float(p.get('total', 0))
            p['discount'] = float(p.get('discount', 0)) if p.get('discount') else 0.0
            p['tax'] = float(p.get('tax', 0)) if p.get('tax') else 0.0
            products_map[iid].append(p)

        # Fetch all payments in one query
        cursor.execute(f"""
            SELECT 
                spm.sale_id,
                pm.method_name,
                spm.amount,
                spm.transaction_reference,
                spm.payment_date
            FROM sale_payments spm
            JOIN payment_methods pm ON spm.payment_method = pm.id
            WHERE spm.sale_id IN ({placeholders})
            ORDER BY spm.payment_date
        """, invoice_ids)

        payments_map = {}
        for pay in cursor.fetchall():
            sid = pay['sale_id']
            if sid not in payments_map:
                payments_map[sid] = []
            pay['amount'] = float(pay.get('amount', 0))
            if pay.get('payment_date'):
                pay['payment_date'] = pay['payment_date'].strftime('%Y-%m-%d %H:%M:%S')
            payments_map[sid].append(pay)

        # Assemble
        status_counts = {}
        status_totals = {}

        for s in sales:
            iid = s['invoice_id']
            pt = product_totals_map.get(iid, {})

            s['total_products'] = float(pt.get('total_products', 0))
            s['total_quantity'] = float(pt.get('total_quantity', 0))
            s['product_discount'] = float(pt.get('product_discount', 0))
            s['product_tax'] = float(pt.get('product_tax', 0))
            s['products'] = products_map.get(iid, [])
            s['payments'] = payments_map.get(iid, [])

            s['grand_total'] = float(s['grand_total'])
            s['tenderAmount'] = float(s.get('tenderAmount', 0))
            s['remainingAmount'] = float(s.get('remainingAmount', 0))
            s['discount'] = float(s.get('discount', 0))
            s['tax'] = float(s.get('tax', 0))

            if s.get('sale_date'):
                s['sale_date'] = s['sale_date'].strftime('%Y-%m-%d %H:%M:%S')

            status = s['status']
            status_counts[status] = status_counts.get(status, 0) + 1
            status_totals[status] = status_totals.get(status, 0.0) + s['grand_total']

        return jsonify({
            "success": True,
            "sales": sales,
            "total_sales": len(sales),
            "status_breakdown": {
                "counts": status_counts,
                "totals": {k: round(v, 2) for k, v in status_totals.items()}
            }
        }), 200

    except Exception as e:
        import traceback
        traceback.print_exc()
        return jsonify({"success": False, "error": str(e)}), 500

    finally:
        cursor.close()
        conn.close()


# ===========================
# ✅ MISSING ENDPOINT — required by customer_sale_details.html
# ===========================
@report_bp.route('/get_customer_sales_return/<int:customer_id>', methods=['GET'])
@jwt_required()
@role_required('admin', 'manager', 'cashier')
def get_customer_sales_return(customer_id):
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    try:
        cursor.execute("""
            SELECT 
                sr.return_id,
                sr.return_date,
                sr.total_return_amount,
                sr.reason,
                sr.order_tax,
                sr.order_discount,
                sr.status,
                i.invoice_code,
                c.name AS customer_name,
                s.store_name,
                w.warehouse_name
            FROM sale_return sr
            JOIN invoice_sale i ON sr.invoice_id = i.invoice_id
            LEFT JOIN customers c ON sr.customer_id = c.id
            LEFT JOIN stores s ON sr.store_id = s.id
            LEFT JOIN warehouses w ON sr.warehouse_id = w.id
            WHERE sr.customer_id = %s
            ORDER BY sr.return_date DESC
        """, (customer_id,))
        returns = cursor.fetchall()

        if not returns:
            return jsonify({
                "success": True,
                "sale_returns": [],
                "total_returns": 0,
                "message": "No sale returns found for this customer"
            }), 200

        status_counts = {}
        status_totals = {}

        for r in returns:
            r['total_return_amount'] = float(r.get('total_return_amount', 0))
            r['order_tax'] = float(r.get('order_tax', 0))
            r['order_discount'] = float(r.get('order_discount', 0))

            if r.get('return_date'):
                r['return_date'] = r['return_date'].strftime('%Y-%m-%d %H:%M:%S')

            status = r['status'] or 'unknown'
            status_counts[status] = status_counts.get(status, 0) + 1
            status_totals[status] = (
                status_totals.get(status, 0.0) + r['total_return_amount']
            )

        return jsonify({
            "success": True,
            "sale_returns": returns,
            "total_returns": len(returns),
            "status_breakdown": {
                "counts": status_counts,
                "totals": {k: round(v, 2) for k, v in status_totals.items()}
            }
        }), 200

    except Exception as e:
        import traceback
        traceback.print_exc()
        return jsonify({"success": False, "error": str(e)}), 500

    finally:
        cursor.close()
        conn.close()



# ✅ Optional: Filter by specific status
@report_bp.route('/get_customer_sales/<int:customer_id>/status/<string:status>', methods=['GET'])
@jwt_required()
@role_required('admin', 'manager', 'cashier')
def get_customer_sales_by_status(customer_id, status):
    """
    Get customer sales filtered by specific status.
    Usage: /get_customer_sales/1/status/received
           /get_customer_sales/1/status/suspended
    """
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    try:
        query = """
            SELECT 
                i.invoice_id,
                i.invoice_code,
                i.status,
                i.invoiceTotal AS grand_total,
                i.payment_status,
                i.sale_date,
                c.name AS customer_name
            FROM invoice_sale i
            JOIN customers c ON i.customer_id = c.id
            WHERE c.id = %s AND i.status = %s
            ORDER BY i.sale_date DESC
        """
        cursor.execute(query, (customer_id, status))
        sales = cursor.fetchall()

        return jsonify({
            "success": True,
            "sales": sales,
            "total_sales": len(sales),
            "filtered_by_status": status
        }), 200

    except Exception as e:
        return jsonify({
            "success": False,
            "error": str(e)
        }), 500

    finally:
        cursor.close()
        conn.close()
        
        
@report_bp.route('/get_best_customer_summary', methods=['GET'])
@jwt_required()
@role_required('admin')
def get_best_customer_summary():
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    try:
        cursor.execute("""
            SELECT 
                c.id AS customer_id,
                c.name AS customer_name,
                c.contact AS contact_number,
                COUNT(DISTINCT i.invoice_id) AS sale_count,
                IFNULL(SUM(i.invoiceTotal), 0) AS total_sales
            FROM customers c
            JOIN invoice_sale i ON i.customer_id = c.id
            GROUP BY c.id, c.name, c.contact
            HAVING total_sales > 0
            ORDER BY total_sales DESC
        """)
        customers = cursor.fetchall()

        processed_customers = []
        for c in customers:
            processed_customers.append({
                "Name": c['customer_name'] if c['customer_name'] else 'Walk-in Customer',
                "Phone": c['contact_number'] if c['contact_number'] else 'N/A',
                "Sale Count": c['sale_count'],
                "Total Amount": float(c['total_sales'])
            })

        return jsonify({
            "customers": processed_customers,
            "total_count": len(processed_customers)
        }), 200

    except Exception as e:
        print("Error fetching customer summary:", e)
        return jsonify({"error": str(e)}), 500

    finally:
        cursor.close()
        conn.close()


@report_bp.route('/dashboard_get_best_customer_summary', methods=['GET'])
@jwt_required()
@role_required('admin', 'user', 'cashier')
def dashboard_get_best_customer_summary():
    """
    Get top 5 customers by total sales
    ✅ Fixed: Changed 'phone' to 'contact' (correct column name in customers table)
    """
    conn = None
    cursor = None
    
    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)
        
        cursor.execute("""
            SELECT 
                c.id,
                c.name AS Name,
                c.email,
                c.contact,
                COUNT(DISTINCT i.invoice_id) as total_invoices,
                SUM(i.invoiceTotal) as total_amount
            FROM customers c
            JOIN invoice_sale i ON c.id = i.customer_id
            WHERE i.status = 'received'
            GROUP BY c.id, c.name, c.email, c.contact
            ORDER BY total_amount DESC
            LIMIT 5
        """)
        
        customers = cursor.fetchall()
        
        result = []
        for customer in customers:
            result.append({
                "customer_id": customer['id'],
                "Name": customer['Name'],
                "email": customer['email'],
                "contact": customer['contact'],  # ✅ Changed from 'phone' to 'contact'
                "total_invoices": customer['total_invoices'],
                "Total Amount": float(customer['total_amount'] or 0)
            })
        
        return jsonify({
            "message": "Best customers fetched successfully",
            "best_customers": result
        }), 200
        
    except Exception as e:
        logger.error(f"❌ Error in dashboard_get_best_customer_summary: {e}")
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500
        
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()

        
        
@report_bp.route('/all_purchase_return_details', methods=['GET'])
@jwt_required()
@role_required('admin')
def all_purchase_return_details():
    try:
        start_date = request.args.get('start_date')
        end_date = request.args.get('end_date')

        if not start_date or not end_date:
            return jsonify({"error": "start_date and end_date are required"}), 400

        conn = get_db_connection()
        if not conn:
            return jsonify({"error": "Failed to connect to database"}), 500

        cursor = conn.cursor()

        # Fetch purchase return details in date range
        query = """
            SELECT DATE(pr.created_on) AS return_date, 
                   p.product_name, 
                   pri.quantity, 
                   pri.subtotal, 
                   pri.product_discount, 
                   pri.product_tax
            FROM purchase_return_items pri
            JOIN products p ON pri.product_id = p.id
            JOIN purchase_returns pr ON pri.return_id = pr.return_id
            WHERE DATE(pr.created_on) BETWEEN %s AND %s
            ORDER BY pr.created_on DESC;
        """
        cursor.execute(query, (start_date, end_date))
        results = cursor.fetchall()

        # Total purchase return amount in date range
        total_query = """
            SELECT SUM(pri.subtotal)
            FROM purchase_return_items pri
            JOIN purchase_returns pr ON pri.return_id = pr.return_id
            WHERE DATE(pr.created_on) BETWEEN %s AND %s;
        """
        cursor.execute(total_query, (start_date, end_date))
        total_amount = cursor.fetchone()[0] or 0.0

        # Format results for JSON
        purchase_return_details = []
        for row in results:
            purchase_return_details.append({
                "return_date": str(row[0]),
                "product_name": row[1],
                "quantity": row[2],
                "subtotal": str(Decimal(row[3])),
                "product_discount": str(Decimal(row[4])),
                "product_tax": str(Decimal(row[5]))
            })

        cursor.close()
        conn.close()

        return jsonify({
            "total_purchase_amount": str(Decimal(total_amount)),
            "purchase_return_details": purchase_return_details
        }), 200

    except Exception as e:
        print(f"Error: {str(e)}")
        return jsonify({"error": "An error occurred while fetching purchase return details"}), 500


@report_bp.route('/dashboard_last_7_days_sales', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier')
def dashboard_last_7_days_sales():
    conn = None
    cursor = None
    
    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)
        
        status_filter = request.args.get('status')
        
        today = datetime.today()
        seven_days_ago = today - timedelta(days=7)
        
        base_query = """
            SELECT 
                i.invoice_id, i.invoice_code, 
                i.tenderAmount, i.remainingAmount, 
                i.invoiceTotal, i.payment_method, 
                i.customer_id, c.name AS customer_name, 
                i.sale_date, i.status,
                i.invoiceTotal AS total_medicine_amount
            FROM invoice_sale i
            LEFT JOIN customers c ON i.customer_id = c.id
            WHERE i.sale_date >= %s
            AND i.status IN ('received', 'suspended')
        """
        
        if status_filter:
            base_query += " AND i.status = %s"
            cursor.execute(base_query + " ORDER BY i.sale_date DESC", (seven_days_ago, status_filter))
        else:
            cursor.execute(base_query + " ORDER BY i.sale_date DESC", (seven_days_ago,))
        
        invoices = cursor.fetchall()
        
        total_medicine_per_day = {}
        total_returns_per_day = {}   # ✅ NEW
        
        for day in range(7):
            day_date = today - timedelta(days=day)
            day_start = day_date.replace(hour=0, minute=0, second=0, microsecond=0)
            day_end   = day_date.replace(hour=23, minute=59, second=59, microsecond=999999)
            day_key   = day_date.strftime('%Y-%m-%d')
            
            # Sales
            cursor.execute("""
                SELECT COALESCE(SUM(invoiceTotal), 0) as total
                FROM invoice_sale
                WHERE sale_date BETWEEN %s AND %s
                AND status IN ('received', 'suspended')
            """, (day_start, day_end))
            total_medicine_per_day[day_key] = float(cursor.fetchone()['total'] or 0)
            
            # ✅ Sale Returns
            cursor.execute("""
                SELECT COALESCE(SUM(total_return_amount), 0) as total
                FROM sale_return
                WHERE return_date BETWEEN %s AND %s
            """, (day_start, day_end))
            total_returns_per_day[day_key] = float(cursor.fetchone()['total'] or 0)
        
        today_start = today.replace(hour=0, minute=0, second=0, microsecond=0)
        today_end   = today.replace(hour=23, minute=59, second=59, microsecond=999999)
        
        cursor.execute("""
            SELECT COALESCE(SUM(invoiceTotal), 0) as total
            FROM invoice_sale
            WHERE sale_date BETWEEN %s AND %s
            AND status IN ('received', 'suspended')
        """, (today_start, today_end))
        total_medicine_today = float(cursor.fetchone()['total'] or 0)
        
        all_invoice_details = []
        for invoice in invoices:
            cursor.execute("""
                SELECT p.product_name, sp.price, sp.quantity, sp.total
                FROM sale_products sp
                JOIN products p ON sp.product_id = p.id
                WHERE sp.invoice_id = %s
            """, (invoice['invoice_id'],))
            products = cursor.fetchall()
            
            all_invoice_details.append({
                "invoice_id":            invoice['invoice_id'],
                "invoice_code":          invoice['invoice_code'],
                "tenderAmount":          float(invoice['tenderAmount']   or 0),
                "remainingAmount":       float(invoice['remainingAmount'] or 0),
                "invoiceTotal":          float(invoice['invoiceTotal']    or 0),
                "payment_method":        invoice['payment_method'],
                "customer_id":           invoice['customer_id'],
                "customer_name":         invoice['customer_name'],
                "sale_date":             format_datetime(invoice['sale_date']),
                "status":                invoice['status'],
                "total_medicine_amount": float(invoice['invoiceTotal']    or 0),
                "products": [
                    {
                        "product_name": p['product_name'],
                        "price":    float(p['price']    or 0),
                        "quantity": float(p['quantity'] or 0),
                        "total":    float(p['total']    or 0)
                    }
                    for p in products
                ]
            })
        
        return jsonify({
            "invoices":               all_invoice_details,
            "total_medicine_today":   total_medicine_today,
            "total_medicine_per_day": total_medicine_per_day,
            "total_returns_per_day":  total_returns_per_day,   # ✅ NEW
        }), 200
        
    except Exception as e:
        logger.error(f"❌ Error in dashboard_last_7_days_sales: {e}")
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500
        
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()
# ============================================
# 1️⃣3️⃣ LAST 7 DAYS PURCHASE ORDERS
# ============================================
@report_bp.route('/last_7day_get_all_submit_order', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier')
def last_7day_get_all_submit_order():
    """
    Get last 7 days purchase orders for dashboard
    """
    conn = None
    cursor = None
    
    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)
        
        # Query to fetch orders and items
        cursor.execute("""
            SELECT 
                po.order_id, 
                po.supplier_id, 
                s.supplier_name,  
                po.note, 
                po.order_tax, 
                po.discount, 
                po.status, 
                po.grand_total, 
                po.created_on AS order_date,
                oi.product_id,
                oi.variation_id,
                oi.quantity,
                oi.discount AS item_discount,
                oi.tax AS item_tax,
                oi.subtotal,
                p.product_name,
                p.sku,
                v.variation_name,
                v.variation_sku,
                v.variation_cost,
                v.variation_price,
                po.payment_status
            FROM purchase_orders po
            LEFT JOIN order_items oi ON po.order_id = oi.order_id
            LEFT JOIN products p ON oi.product_id = p.id
            LEFT JOIN suppliers s ON po.supplier_id = s.id
            LEFT JOIN product_variations v ON oi.variation_id = v.id
            ORDER BY po.order_id DESC
        """)
        
        rows = cursor.fetchall()
        
        orders = {}
        for row in rows:
            order_id = row['order_id']
            
            if order_id not in orders:
                orders[order_id] = {
                    'order_id': order_id,
                    'supplier_id': row['supplier_id'],
                    'supplier_name': row['supplier_name'],
                    'note': row['note'],
                    'order_tax': float(row['order_tax'] or 0),
                    'discount': float(row['discount'] or 0),
                    'status': row['status'],
                    'grand_total': float(row['grand_total'] or 0),
                    'order_date': format_datetime(row['order_date']),
                    'payment_status': row['payment_status'],
                    'items': []
                }
            
            # Only add item if product_id is not None
            if row['product_id'] is not None:
                orders[order_id]['items'].append({
                    'product_id': row['product_id'],
                    'variation_id': row['variation_id'],
                    'quantity': float(row['quantity'] or 0),
                    'discount': float(row['item_discount'] or 0),
                    'tax': float(row['item_tax'] or 0),
                    'subtotal': float(row['subtotal'] or 0),
                    'product_name': row['product_name'],
                    'sku': row['sku'],
                    'variation_name': row['variation_name'],
                    'variation_sku': row['variation_sku'],
                    'variation_cost': float(row['variation_cost'] or 0),
                    'variation_price': float(row['variation_price'] or 0)
                })
        
        # Calculate total purchase amounts for each day in the last 7 days
        total_purchase_per_day = {}
        today = datetime.today()
        
        for day in range(7):
            day_date = today - timedelta(days=day)
            day_start = day_date.replace(hour=0, minute=0, second=0, microsecond=0)
            day_end = day_date.replace(hour=23, minute=59, second=59, microsecond=999999)
            
            cursor.execute("""
                SELECT COALESCE(SUM(po.grand_total), 0) as total
                FROM purchase_orders po
                WHERE po.created_on >= %s AND po.created_on <= %s
            """, (day_start, day_end))
            
            result = cursor.fetchone()
            total_for_day = result['total'] or 0
            total_purchase_per_day[day_date.strftime('%Y-%m-%d')] = float(total_for_day)
        
        return jsonify({
            "orders": list(orders.values()),
            "total_purchase_per_day": total_purchase_per_day
        }), 200
        
    except Exception as e:
        logger.error(f"❌ Error in last_7day_get_all_submit_order: {e}")
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500
        
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()



"""
============================================
PROFIT & LOSS REPORT - COMPLETE BACKEND WITH GRN TRACKING
============================================
Complete backend API for Profit & Loss calculations with GRN integration
Author: Claude AI
Date: 2026-02-09
Version: 3.0.0

Features:
- ✅ FIFO cost tracking using sale_product_items + GRN
- ✅ Currency formatting with commas  
- ✅ GRN tracking and traceability
- ✅ Batch-level cost tracking
- ✅ Comprehensive error handling
- ✅ Date range filtering
- ✅ All P&L metrics with GRN details
============================================
"""

from flask import Blueprint, request, jsonify
from flask_jwt_extended import jwt_required, get_jwt_identity
from decimal import Decimal, ROUND_HALF_UP
from datetime import datetime, date, timedelta
import traceback
import mysql.connector
from mysql.connector import Error
from functools import wraps
import logging

# ============================================
# LOGGING CONFIGURATION
# ============================================
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s'
)
logger = logging.getLogger(__name__)




# ============================================
# UTILITY FUNCTIONS
# ============================================
def format_currency(amount):
    """Format decimal to string with commas - Shows exact value without rounding"""
    if amount is None:
        return "0.00"
    try:
        # Convert to float to handle Decimal objects
        amount_float = float(amount)
        
        # Format with many decimal places, then strip trailing zeros
        formatted = f"{amount_float:.10f}".rstrip('0').rstrip('.')
        
        # Split into integer and decimal parts
        if '.' in formatted:
            integer_part, decimal_part = formatted.split('.')
            # Ensure at least 2 decimal places
            if len(decimal_part) < 2:
                decimal_part = decimal_part.ljust(2, '0')
        else:
            integer_part = formatted
            decimal_part = "00"
        
        # Add commas to integer part
        integer_with_commas = "{:,}".format(int(float(integer_part)))
        
        return f"{integer_with_commas}.{decimal_part}"
    except (ValueError, TypeError):
        return "0.00"


def to_decimal(value):
    """Safely convert to Decimal - No rounding, exact value"""
    if value is None:
        return Decimal('0.00')
    try:
        # Return exact value without rounding
        return Decimal(str(value))
    except:
        return Decimal('0.00')


def format_datetime(dt):
    """Format datetime to string"""
    if dt is None:
        return None
    if isinstance(dt, str):
        return dt
    return dt.strftime('%Y-%m-%d %H:%M:%S')


@report_bp.route('/total_sales', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier', 'manager')
def total_sales():
    conn = None
    cursor = None
    
    try:
        start_date = request.args.get('start_date')
        end_date = request.args.get('end_date')
        
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)
        
        # ✅ FIX: Use invoiceTotal (grand total) not sale_products.total
        query = """
            SELECT COALESCE(SUM(invoiceTotal), 0) as total_sales
            FROM invoice_sale
            WHERE status IN ('received', 'suspended')
        """
        
        params = []
        if start_date and end_date:
            query += " AND DATE(sale_date) BETWEEN %s AND %s"
            params.extend([start_date, end_date])
        elif start_date:
            query += " AND DATE(sale_date) >= %s"
            params.append(start_date)
        elif end_date:
            query += " AND DATE(sale_date) <= %s"
            params.append(end_date)
        
        cursor.execute(query, tuple(params))
        result = cursor.fetchone()
        total_sales = to_decimal(result['total_sales'])
        
        return jsonify({
            "success": True,
            "total_sales": float(total_sales),
            "total_sales_formatted": format_currency(total_sales),
            "start_date": start_date,
            "end_date": end_date
        }), 200
        
    except Exception as e:
        logger.error(f"❌ Error in total_sales: {e}")
        traceback.print_exc()
        return jsonify({"success": False, "error": str(e)}), 500
        
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()

# ============================================
# 2️⃣ TOTAL SALE RETURNS
# ============================================
@report_bp.route('/total_sale_returns', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier', 'manager')
def total_sale_returns():
    """Calculate total sale returns - uses total_return_amount (grand total)"""
    conn = None
    cursor = None
    
    try:
        start_date = request.args.get('start_date')
        end_date = request.args.get('end_date')
        
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)
        
        # ✅ FIX: Use sale_return.total_return_amount (grand total)
        # NOT sale_return_products.return_total (product subtotals)
        query = """
            SELECT 
                COALESCE(SUM(total_return_amount), 0) as total_sale_returns
            FROM sale_return
            WHERE 1=1
        """
        
        params = []
        if start_date and end_date:
            query += " AND DATE(return_date) BETWEEN %s AND %s"
            params.extend([start_date, end_date])
        elif start_date:
            query += " AND DATE(return_date) >= %s"
            params.append(start_date)
        elif end_date:
            query += " AND DATE(return_date) <= %s"
            params.append(end_date)
        
        cursor.execute(query, tuple(params))
        result = cursor.fetchone()
        
        total_returns = to_decimal(result['total_sale_returns'])
        
        return jsonify({
            "success": True,
            "total_sale_returns": float(total_returns),
            "total_sale_returns_formatted": format_currency(total_returns),
            "start_date": start_date,
            "end_date": end_date
        }), 200
        
    except Exception as e:
        logger.error(f"❌ Error in total_sale_returns: {e}")
        traceback.print_exc()
        return jsonify({"success": False, "error": str(e)}), 500
        
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()
# ============================================
# 3️⃣ TOTAL PURCHASES
# ============================================
@report_bp.route('/total_submit_order', methods=['GET'])
@jwt_required()
@role_required('admin', 'manager')
def total_submit_order():
    """Calculate total purchases from purchase_orders"""
    conn = None
    cursor = None
    
    try:
        start_date = request.args.get('start_date')
        end_date = request.args.get('end_date')
        
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)
        
        query = """
            SELECT 
                COALESCE(SUM(grand_total), 0) AS total_purchases 
            FROM purchase_orders 
            WHERE 1=1
        """
        
        params = []
        if start_date and end_date:
            query += " AND DATE(created_on) BETWEEN %s AND %s"
            params.extend([start_date, end_date])
        elif start_date:
            query += " AND DATE(created_on) >= %s"
            params.append(start_date)
        elif end_date:
            query += " AND DATE(created_on) <= %s"
            params.append(end_date)
        
        cursor.execute(query, tuple(params))
        result = cursor.fetchone()
        
        total = to_decimal(result['total_purchases'])
        
        return jsonify({
            'success': True,
            'total_purchases': float(total),
            'total_purchases_formatted': format_currency(total),
            'start_date': start_date,
            'end_date': end_date
        }), 200
        
    except Exception as e:
        logger.error(f"❌ Error in total_submit_order: {e}")
        traceback.print_exc()
        return jsonify({"success": False, "error": str(e)}), 500
        
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


# ============================================
# 4️⃣ TOTAL PURCHASE RETURNS
# ============================================
@report_bp.route('/all_purchase_return', methods=['GET'])
@jwt_required()
@role_required('admin', 'manager')
def all_purchase_return():
    """Calculate total purchase returns"""
    conn = None
    cursor = None
    
    try:
        start_date = request.args.get('start_date')
        end_date = request.args.get('end_date')
        
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)
        
        query = """
            SELECT 
                COALESCE(SUM(pri.subtotal), 0) as total_purchase_amount
            FROM purchase_return_items pri
            JOIN purchase_returns pr ON pri.return_id = pr.return_id
            WHERE 1=1
        """
        
        params = []
        if start_date and end_date:
            query += " AND DATE(pr.created_on) BETWEEN %s AND %s"
            params.extend([start_date, end_date])
        elif start_date:
            query += " AND DATE(pr.created_on) >= %s"
            params.append(start_date)
        elif end_date:
            query += " AND DATE(pr.created_on) <= %s"
            params.append(end_date)
        
        cursor.execute(query, tuple(params))
        result = cursor.fetchone()
        
        total = to_decimal(result['total_purchase_amount'])
        
        return jsonify({
            'success': True,
            'total_purchase_amount': float(total),
            'total_purchase_amount_formatted': format_currency(total),
            'start_date': start_date,
            'end_date': end_date
        }), 200
        
    except Exception as e:
        logger.error(f"❌ Error in all_purchase_return_details: {e}")
        traceback.print_exc()
        return jsonify({"success": False, "error": str(e)}), 500
        
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


# ============================================
# 5️⃣ TOTAL EXPENSES
# ============================================
@report_bp.route('/total_expenses', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier', 'manager')
def total_expenses():
    """Calculate total expenses"""
    conn = None
    cursor = None
    
    try:
        start_date = request.args.get('start_date')
        end_date = request.args.get('end_date')
        
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)
        
        query = """
            SELECT 
                COALESCE(SUM(amount), 0) as total_expenses
            FROM expenses 
            WHERE 1=1
        """
        
        params = []
        if start_date and end_date:
            query += " AND DATE(date) BETWEEN %s AND %s"
            params.extend([start_date, end_date])
        elif start_date:
            query += " AND DATE(date) >= %s"
            params.append(start_date)
        elif end_date:
            query += " AND DATE(date) <= %s"
            params.append(end_date)
        
        cursor.execute(query, tuple(params))
        result = cursor.fetchone()
        
        total_expenses = to_decimal(result['total_expenses'])
        
        return jsonify({
            "success": True,
            "total_expenses": float(total_expenses),
            "total_expenses_formatted": format_currency(total_expenses),
            "start_date": start_date,
            "end_date": end_date
        }), 200
        
    except Exception as e:
        logger.error(f"❌ Error in total_expenses: {e}")
        traceback.print_exc()
        return jsonify({"success": False, "error": str(e)}), 500
        
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


# ============================================
# 6️⃣ TOTAL SALE PRODUCT COST WITH GRN TRACKING (CRITICAL)
# ============================================
@report_bp.route('/total_sale_product_cost_all', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier', 'manager')
def total_sale_product_cost_all():
    """
    ✅ CRITICAL: Calculate total product cost using FIFO with GRN tracking
    Uses sale_product_items table for accurate batch-level cost tracking
    """
    conn = None
    cursor = None
    
    try:
        start_date = request.args.get('start_date')
        end_date = request.args.get('end_date')
        
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)
        
        # ✅ Main query to get invoices with FIFO costs from sale_product_items
        query = """
            SELECT 
                isale.invoice_id,
                isale.invoice_code,
                isale.sale_date,
                isale.invoiceTotal,
                isale.payment_method,
                isale.status,
                COALESCE(SUM(spi.quantity * spi.cost), 0) as total_product_cost
            FROM invoice_sale isale
            LEFT JOIN sale_products sp ON isale.invoice_id = sp.invoice_id
            LEFT JOIN sale_product_items spi ON sp.sale_product_id = spi.sale_product_id
            WHERE isale.status IN ('received', 'suspended')
        """
        
        params = []
        if start_date and end_date:
            query += " AND DATE(isale.sale_date) BETWEEN %s AND %s"
            params.extend([start_date, end_date])
        elif start_date:
            query += " AND DATE(isale.sale_date) >= %s"
            params.append(start_date)
        elif end_date:
            query += " AND DATE(isale.sale_date) <= %s"
            params.append(end_date)
        
        query += " GROUP BY isale.invoice_id ORDER BY isale.sale_date DESC"
        
        cursor.execute(query, tuple(params))
        invoices = cursor.fetchall()
        
        total_all_product_cost = Decimal('0.00')
        invoice_details = []
        
        for invoice in invoices:
            invoice_cost = to_decimal(invoice['total_product_cost'])
            total_all_product_cost += invoice_cost
            
            # ✅ Get detailed product breakdown with GRN tracking
            cursor.execute("""
                SELECT 
                    p.product_name,
                    pv.variation_name,
                    sp.price as selling_price,
                    sp.quantity as total_quantity,
                    spi.sale_product_item_id,
                    spi.batch_id,
                    spi.grn_id,
                    spi.quantity as batch_quantity,
                    spi.cost as batch_cost,
                    spi.expiration_date as batch_expiry,
                    pb.batch_number,
                    grn.grn_code,
                    grn.grn_date,
                    (spi.quantity * spi.cost) as item_total_cost
                FROM sale_products sp
                JOIN products p ON sp.product_id = p.id
                LEFT JOIN product_variations pv ON sp.variation_id = pv.id
                LEFT JOIN sale_product_items spi ON sp.sale_product_id = spi.sale_product_id
                LEFT JOIN product_batches pb ON spi.batch_id = pb.batch_id
                LEFT JOIN grn ON spi.grn_id = grn.grn_id
                WHERE sp.invoice_id = %s
                ORDER BY sp.sale_product_id, spi.sale_product_item_id
            """, (invoice['invoice_id'],))
            products = cursor.fetchall()
            
            product_list = []
            for prod in products:
                product_list.append({
                    "product_name": prod['product_name'],
                    "variation": prod['variation_name'] or 'N/A',
                    "selling_price": float(prod['selling_price'] or 0),
                    "total_quantity": float(prod['total_quantity'] or 0),
                    "grn_tracking": {
                        "grn_id": prod['grn_id'],
                        "grn_code": prod['grn_code'],
                        "grn_date": format_datetime(prod['grn_date']),
                        "batch_id": prod['batch_id'],
                        "batch_number": prod['batch_number'],
                        "batch_quantity": float(prod['batch_quantity'] or 0),
                        "batch_cost": float(prod['batch_cost'] or 0),
                        "batch_expiry": str(prod['batch_expiry']) if prod['batch_expiry'] else None,
                        "item_total_cost": float(prod['item_total_cost'] or 0)
                    }
                })
            
            invoice_details.append({
                "invoice_id": invoice['invoice_id'],
                "invoice_code": invoice['invoice_code'],
                "sale_date": format_datetime(invoice['sale_date']),
                "invoiceTotal": float(invoice['invoiceTotal'] or 0),
                "payment_method": invoice['payment_method'],
                "status": invoice['status'],
                "total_product_cost": float(invoice_cost),
                "total_product_cost_formatted": format_currency(invoice_cost),
                "products": product_list
            })
        
        return jsonify({
            "success": True,
            "invoices": invoice_details,
            "total_all_sale_product_cost": float(total_all_product_cost),
            "total_all_sale_product_cost_formatted": format_currency(total_all_product_cost),
            "filtered": bool(start_date and end_date),
            "start_date": start_date,
            "end_date": end_date
        }), 200
        
    except Exception as e:
        logger.error(f"❌ Error in total_sale_product_cost_all: {e}")
        traceback.print_exc()
        return jsonify({"success": False, "error": str(e)}), 500
        
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


# ============================================
# 7️⃣ PAYMENTS RECEIVED
# ============================================
@report_bp.route('/payments_received', methods=['GET'])
@jwt_required()
@role_required('admin', 'manager')
def payments_received():
    """
    Calculate total payments received from:
    1. Sales (received + partial)
    2. Purchase Returns
    """
    conn = None
    cursor = None
    
    try:
        start_date = request.args.get('start_date')
        end_date = request.args.get('end_date')
        
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)
        
        # Sales payments
        query_sales = """
            SELECT 
                COALESCE(SUM(
                    CASE 
                        WHEN isale.status = 'received' THEN isale.invoiceTotal
                        WHEN isale.status = 'suspended' AND isale.payment_status = 'partial' THEN isale.tenderAmount
                        WHEN isale.status = 'suspended' AND isale.payment_status = 'paid' THEN isale.invoiceTotal
                        ELSE 0
                    END
                ), 0) AS total_received
            FROM invoice_sale AS isale
            WHERE (
                isale.status = 'received'
                OR (isale.status = 'suspended' AND isale.payment_status IN ('partial', 'paid'))
            )
        """
        
        params_sales = []
        if start_date and end_date:
            query_sales += " AND DATE(isale.sale_date) BETWEEN %s AND %s"
            params_sales.extend([start_date, end_date])
        elif start_date:
            query_sales += " AND DATE(isale.sale_date) >= %s"
            params_sales.append(start_date)
        elif end_date:
            query_sales += " AND DATE(isale.sale_date) <= %s"
            params_sales.append(end_date)
        
        cursor.execute(query_sales, tuple(params_sales))
        sales_result = cursor.fetchone()
        total_sales = to_decimal(sales_result['total_received'])
        
        # Purchase returns
        query_returns = """
            SELECT 
                COALESCE(SUM(pri.subtotal), 0) as total_returns
            FROM purchase_return_items pri
            JOIN purchase_returns pr ON pri.return_id = pr.return_id
            WHERE 1=1
        """
        
        params_returns = []
        if start_date and end_date:
            query_returns += " AND DATE(pr.created_on) BETWEEN %s AND %s"
            params_returns.extend([start_date, end_date])
        elif start_date:
            query_returns += " AND DATE(pr.created_on) >= %s"
            params_returns.append(start_date)
        elif end_date:
            query_returns += " AND DATE(pr.created_on) <= %s"
            params_returns.append(end_date)
        
        cursor.execute(query_returns, tuple(params_returns))
        returns_result = cursor.fetchone()
        total_returns = to_decimal(returns_result['total_returns'])
        
        total_payments_received = total_sales + total_returns
        
        return jsonify({
            "success": True,
            "total_payments_received": float(total_payments_received),
            "total_payments_received_formatted": format_currency(total_payments_received),
            "sales_received": float(total_sales),
            "sales_received_formatted": format_currency(total_sales),
            "purchase_returns": float(total_returns),
            "purchase_returns_formatted": format_currency(total_returns),
            "start_date": start_date,
            "end_date": end_date
        }), 200
        
    except Exception as e:
        logger.error(f"❌ Error in payments_received: {e}")
        traceback.print_exc()
        return jsonify({"success": False, "error": str(e)}), 500
        
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


# ============================================
# 8️⃣ PROFIT & LOSS SUMMARY (Complete with GRN Tracking)
# ============================================
@report_bp.route('/profit_loss_summary', methods=['GET'])
@jwt_required()
@role_required('admin', 'manager')
def profit_loss_summary():
    conn = None
    cursor = None
    try:
        start_date = request.args.get('start_date')
        end_date = request.args.get('end_date')

        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        p = [start_date, end_date] if start_date and end_date else []

        def date_filter(col):
            if start_date and end_date:
                return f" AND DATE({col}) BETWEEN %s AND %s"
            return ""

        # 1. Total Sales (received + suspended invoices)
        cursor.execute(f"""
            SELECT COALESCE(SUM(isale.invoiceTotal), 0) AS total
            FROM invoice_sale isale
            WHERE isale.status IN ('received', 'suspended')
            {date_filter('isale.sale_date')}
        """, tuple(p))
        total_sales = to_decimal(cursor.fetchone()['total'])

        # 2. Sales Returns
        #    - status = 'received' only (pending returns exclude)
        #    - total_return_amount use (grand total, not line subtotals)
        cursor.execute(f"""
            SELECT COALESCE(SUM(sr.total_return_amount), 0) AS total
            FROM sale_return sr
            WHERE sr.status = 'received'
            {date_filter('sr.return_date')}
        """, tuple(p))
        sales_returns = to_decimal(cursor.fetchone()['total'])

        # 3. Purchases (actual received via GRN)
        cursor.execute(f"""
            SELECT COALESCE(SUM(g.grand_total), 0) AS total
            FROM grn g
            WHERE g.status IN ('completed', 'partial')
            {date_filter('g.grn_date')}
        """, tuple(p))
        total_purchases = to_decimal(cursor.fetchone()['total'])

        # 4. Purchase Returns
        cursor.execute(f"""
            SELECT COALESCE(SUM(pri.subtotal), 0) AS total
            FROM purchase_return_items pri
            JOIN purchase_returns pr ON pri.return_id = pr.return_id
            WHERE 1=1 {date_filter('pr.created_on')}
        """, tuple(p))
        purchase_returns = to_decimal(cursor.fetchone()['total'])

        # 5. Expenses
        cursor.execute(f"""
            SELECT COALESCE(SUM(e.amount), 0) AS total
            FROM expenses e
            WHERE 1=1 {date_filter('e.date')}
        """, tuple(p))
        total_expenses = to_decimal(cursor.fetchone()['total'])

        # 6. COGS via FIFO batch tracking (sale_product_items)
        cursor.execute(f"""
            SELECT COALESCE(SUM(spi.quantity * spi.cost), 0) AS total
            FROM sale_product_items spi
            JOIN sale_products sp ON spi.sale_product_id = sp.sale_product_id
            JOIN invoice_sale isale ON sp.invoice_id = isale.invoice_id
            WHERE isale.status IN ('received', 'suspended')
            {date_filter('isale.sale_date')}
        """, tuple(p))
        product_cost = to_decimal(cursor.fetchone()['total'])

        # 7. Payments Received (actual payments from sale_payments table)
        cursor.execute(f"""
            SELECT COALESCE(SUM(spay.amount), 0) AS total
            FROM sale_payments spay
            JOIN invoice_sale isale ON spay.sale_id = isale.invoice_id
            WHERE 1=1 {date_filter('isale.sale_date')}
        """, tuple(p))
        payments_received = to_decimal(cursor.fetchone()['total'])

        # Calculations
        revenue      = total_sales - sales_returns
        gross_profit = revenue - product_cost
        net_profit   = gross_profit - total_expenses

        return jsonify({
            "success": True,
            "data": {
                "sales":             {"value": float(total_sales)},
                "sales_returns":     {"value": float(sales_returns)},
                "purchases":         {"value": float(total_purchases)},
                "purchase_returns":  {"value": float(purchase_returns)},
                "expenses":          {"value": float(total_expenses)},
                "product_cost":      {"value": float(product_cost)},
                "revenue":           {"value": float(revenue)},
                "gross_profit":      {"value": float(gross_profit)},
                "net_profit":        {"value": float(net_profit)},
                "payments_received": {"value": float(payments_received)},
            },
            "period": {"start_date": start_date, "end_date": end_date}
        }), 200

    except Exception as e:
        logger.error(f"❌ profit_loss_summary error: {e}")
        traceback.print_exc()
        return jsonify({"success": False, "error": str(e)}), 500
    finally:
        if cursor: cursor.close()
        if conn:   conn.close()

# ============================================
# 9️⃣ GRN COST ANALYSIS (NEW - GRN Specific)
# ============================================
@report_bp.route('/grn_cost_analysis', methods=['GET'])
@jwt_required()
@role_required('admin', 'manager')
def grn_cost_analysis():
    """
    ✅ NEW: Analyze product costs grouped by GRN
    Shows which GRNs contributed to sales and their costs
    """
    conn = None
    cursor = None
    
    try:
        start_date = request.args.get('start_date')
        end_date = request.args.get('end_date')
        
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)
        
        query = """
            SELECT 
                grn.grn_id,
                grn.grn_code,
                grn.grn_date,
                grn.supplier_id,
                s.supplier_name,
                COUNT(DISTINCT spi.sale_product_item_id) as times_used_in_sales,
                SUM(spi.quantity) as total_quantity_sold,
                SUM(spi.quantity * spi.cost) as total_cost_contribution,
                MIN(spi.cost) as min_cost,
                MAX(spi.cost) as max_cost,
                AVG(spi.cost) as avg_cost
            FROM grn
            JOIN suppliers s ON grn.supplier_id = s.id
            LEFT JOIN sale_product_items spi ON grn.grn_id = spi.grn_id
            LEFT JOIN sale_products sp ON spi.sale_product_id = sp.sale_product_id
            LEFT JOIN invoice_sale isale ON sp.invoice_id = isale.invoice_id
            WHERE grn.status = 'completed'
        """
        
        params = []
        if start_date and end_date:
            query += " AND DATE(isale.sale_date) BETWEEN %s AND %s"
            params.extend([start_date, end_date])
        
        query += " GROUP BY grn.grn_id ORDER BY total_cost_contribution DESC"
        
        cursor.execute(query, tuple(params))
        grn_analysis = cursor.fetchall()
        
        grn_list = []
        for grn in grn_analysis:
            grn_list.append({
                "grn_id": grn['grn_id'],
                "grn_code": grn['grn_code'],
                "grn_date": format_datetime(grn['grn_date']),
                "supplier_name": grn['supplier_name'],
                "times_used_in_sales": grn['times_used_in_sales'] or 0,
                "total_quantity_sold": float(grn['total_quantity_sold'] or 0),
                "total_cost_contribution": float(grn['total_cost_contribution'] or 0),
                "total_cost_contribution_formatted": format_currency(grn['total_cost_contribution']),
                "min_cost": float(grn['min_cost'] or 0),
                "max_cost": float(grn['max_cost'] or 0),
                "avg_cost": float(grn['avg_cost'] or 0)
            })
        
        total_grn_cost = sum(float(g['total_cost_contribution'] or 0) for g in grn_analysis)
        
        return jsonify({
            "success": True,
            "grn_analysis": grn_list,
            "summary": {
                "total_grns": len(grn_list),
                "total_cost_contribution": float(total_grn_cost),
                "total_cost_contribution_formatted": format_currency(total_grn_cost)
            },
            "start_date": start_date,
            "end_date": end_date
        }), 200
        
    except Exception as e:
        logger.error(f"❌ Error in grn_cost_analysis: {e}")
        traceback.print_exc()
        return jsonify({"success": False, "error": str(e)}), 500
        
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


@report_bp.route('/total_sales_today', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier', 'manager')
def total_sales_today():
    """Today's total sales - uses invoiceTotal (grand total after discount/tax)"""
    conn = None
    cursor = None
    
    try:
        today = date.today().isoformat()
        
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)
        
        # ✅ FIX: Use invoiceTotal (grand total) not sale_products.total sum
        cursor.execute("""
            SELECT COALESCE(SUM(invoiceTotal), 0) as total_sales_today
            FROM invoice_sale
            WHERE DATE(sale_date) = %s
            AND status IN ('received', 'suspended')
        """, (today,))
        
        result = cursor.fetchone()
        total = to_decimal(result['total_sales_today'])
        
        return jsonify({
            "success": True,
            "total_sales_today": float(total),
            "total_sales_today_formatted": format_currency(total),
            "date": today
        }), 200
        
    except Exception as e:
        logger.error(f"❌ Error: {e}")
        return jsonify({"success": False, "error": str(e)}), 500
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


@report_bp.route('/today_total_received', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier', 'manager')
def today_total_received():
    conn = None
    cursor = None
    try:
        today = date.today().isoformat()
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        # ✅ FIX: Count both received invoices AND partial payments from suspended
        cursor.execute("""
            SELECT COALESCE(SUM(
                CASE 
                    WHEN status = 'received' THEN invoiceTotal
                    WHEN status = 'suspended' AND payment_status = 'partial' THEN tenderAmount
                    WHEN status = 'suspended' AND payment_status = 'paid' THEN invoiceTotal
                    ELSE 0
                END
            ), 0) as total_received
            FROM invoice_sale
            WHERE DATE(sale_date) = %s
        """, (today,))

        result = cursor.fetchone()
        total = to_decimal(result['total_received'])

        return jsonify({
            "success": True,
            "today_total_received": float(total),
            "today_total_received_formatted": format_currency(total),
            "date": today
        }), 200

    except Exception as e:
        logger.error(f"❌ Error: {e}")
        return jsonify({"success": False, "error": str(e)}), 500
    finally:
        if cursor: cursor.close()
        if conn: conn.close()



@report_bp.route('/today_total_submit_order', methods=['GET'])
@jwt_required()
@role_required('admin', 'manager')
def today_total_submit_order():
    """Today's total purchases"""
    conn = None
    cursor = None
    
    try:
        today = date.today().isoformat()
        
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)
        
        cursor.execute("""
            SELECT COALESCE(SUM(grand_total), 0) AS total_purchases
            FROM purchase_orders WHERE DATE(created_on) = %s
        """, (today,))
        
        result = cursor.fetchone()
        total = to_decimal(result['total_purchases'])
        
        return jsonify({
            'success': True,
            'total_purchases': float(total),
            'total_purchases_formatted': format_currency(total),
            'date': today
        }), 200
        
    except Exception as e:
        logger.error(f"❌ Error: {e}")
        return jsonify({"success": False, "error": str(e)}), 500
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()

@report_bp.route('/today_total_expenses', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier', 'manager')
def today_total_expenses():
    conn = None
    cursor = None
    try:
        today = date.today().isoformat()
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        # ✅ FIX: Check both 'date' and 'created_at' columns
        cursor.execute("""
            SELECT COALESCE(SUM(amount), 0) AS total_expenses
            FROM expenses 
            WHERE DATE(date) = %s 
               OR DATE(created_at) = %s
        """, (today, today))

        result = cursor.fetchone()
        total = to_decimal(result['total_expenses'])

        return jsonify({
            'success': True,
            'total_expenses': float(total),
            'total_expenses_formatted': format_currency(total),
            'date': today
        }), 200

    except Exception as e:
        logger.error(f"❌ Error: {e}")
        return jsonify({"success": False, "error": str(e)}), 500
    finally:
        if cursor: cursor.close()
        if conn: conn.close()


@report_bp.route('/dashboard_last_7_days_grn', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier')
def dashboard_last_7_days_grn():
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    try:
        today = datetime.today()
        total_grn_per_day = {}
        for day in range(7):
            day_date = today - timedelta(days=day)
            day_start = day_date.replace(hour=0, minute=0, second=0, microsecond=0)
            day_end   = day_date.replace(hour=23, minute=59, second=59)
            cursor.execute("""
                SELECT COALESCE(SUM(grand_total), 0) as total
                FROM grn
                WHERE grn_date BETWEEN %s AND %s
                AND status IN ('completed', 'pending')
            """, (day_start, day_end))
            total_grn_per_day[day_date.strftime('%Y-%m-%d')] = float(cursor.fetchone()['total'])
        return jsonify({"total_grn_per_day": total_grn_per_day}), 200
    finally:
        cursor.close()
        conn.close()


@report_bp.route('/today_total_grn', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier', 'manager')
def today_total_grn():
    conn = None
    cursor = None
    try:
        today = date.today().isoformat()
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)
        cursor.execute("""
            SELECT COALESCE(SUM(grand_total), 0) AS total_grn
            FROM grn
            WHERE DATE(created_at) = %s
            AND status IN ('completed', 'pending')
        """, (today,))
        result = cursor.fetchone()
        total = to_decimal(result['total_grn'])
        return jsonify({
            'success': True,
            'total_grn_today': float(total),
            'date': today
        }), 200
    except Exception as e:
        return jsonify({"success": False, "error": str(e)}), 500
    finally:
        if cursor: cursor.close()
        if conn: conn.close()

# -------------------------------------------------------------------
# 6️⃣ GET STOCK ADJUSTMENTS BY PRODUCT
# -------------------------------------------------------------------
@report_bp.route('/get_adjustments_by_product/<int:product_id>', methods=['GET'])
@jwt_required()
@role_required('admin')
def get_adjustments_by_product(product_id):
    """
    Get all stock adjustment records for a specific product.
 
    Joins:
        stock_adjustment_items  sai
        stock_adjustments       sa   (sai.adjustment_id → sa.adjustment_id)
        products                p    (sai.product_id → p.id)
        product_variations      pv   (sai.variation_id → pv.id)
        product_batches         pb   (sai.batch_id → pb.batch_id)
        warehouses              w    (sa.warehouse_id → w.id)
        users                   u    (sa.created_by → u.id)
 
    Schema notes (document 5):
        stock_adjustments:
            adjustment_id, adjustment_code, warehouse_id, store_id,
            adjustment_type ENUM('addition','subtraction'),
            reason VARCHAR(255), note TEXT,
            total_items INT, created_by INT,
            created_at TIMESTAMP, updated_at TIMESTAMP
 
        stock_adjustment_items:
            item_id, adjustment_id, product_id, variation_id,
            batch_id, quantity DECIMAL(10,2),
            adjustment_type ENUM('addition','subtraction'),
            note TEXT, created_at TIMESTAMP
 
        product_batches:
            batch_id, product_id, variation_id, batch_number,
            quantity, remaining_quantity, cost, price,
            our_price, expiration_date, purchase_order_id,
            grn_id, created_on
 
    Query Params:
        - variation_id  (optional int)
        - warehouse_id  (optional int)
        - start         (optional YYYY-MM-DD)
        - end           (optional YYYY-MM-DD)
 
    Frontend (adjustment tab) renderAdjustmentTable() expects per row:
        created_at          → formatted  (substring 0–16 used by frontend)
        adjustment_code     → displayed inside badge-code span
        product_name        → used by formatProductNameHTML()
        product_code / sku  → used by formatProductNameHTML() + filterAdjustment()
        variation_name      → used by formatProductNameHTML()
        variation_type      → used by formatProductNameHTML()
        variation_sku       → used by formatProductNameHTML()
        item_adjustment_type → badge: "➕ Addition" / "➖ Subtraction"
        quantity            → fmtNum()
        batch_number        → table column
        warehouse_name      → table column
        reason / note       → table column
        created_by_name     → table column
 
    Frontend filterAdjustment() searches:
        adjustment_code, product_name, product_code, sku,
        variation_name, variation_sku, reason, note,
        warehouse_name, batch_number
 
    Frontend updateSummaryCards() uses:
        allAdjustments.length  (count, not a sum)
    """
    conn   = get_db_connection()
    cursor = conn.cursor(dictionary=True)
 
    try:
        variation_id = request.args.get('variation_id', type=int)
        warehouse_id = request.args.get('warehouse_id', type=int)
        start_date   = request.args.get('start')
        end_date     = request.args.get('end')
 
        query = """
            SELECT
                -- stock_adjustments header
                sa.adjustment_id,
                sa.adjustment_code,
                sa.adjustment_type      AS header_adjustment_type,
                sa.reason,
                sa.note                 AS header_note,
                sa.total_items,
                sa.warehouse_id,
                sa.store_id,
                sa.created_at           AS created_at,
                sa.updated_at,
 
                -- warehouse
                w.warehouse_name,
 
                -- created-by user
                u.name                  AS created_by_name,
 
                -- stock_adjustment_items line
                sai.item_id,
                sai.product_id,
                sai.variation_id,
                sai.batch_id,
                sai.quantity,
                sai.adjustment_type     AS item_adjustment_type,
                sai.note                AS item_note,
                sai.created_at          AS item_created_at,
 
                -- product
                p.product_name,
                p.sku                   AS product_code,
                p.product_type,
 
                -- variation (all 3 fields required by formatProductNameHTML)
                pv.variation_name,
                pv.variation_type,
                pv.variation_sku,
 
                -- batch
                pb.batch_number,
                pb.quantity             AS batch_quantity,
                pb.remaining_quantity   AS batch_remaining_quantity,
                pb.cost                 AS batch_cost,
                pb.price                AS batch_price,
                pb.expiration_date      AS batch_expiration_date
            FROM stock_adjustment_items sai
            JOIN  stock_adjustments     sa  ON sai.adjustment_id = sa.adjustment_id
            JOIN  products              p   ON sai.product_id    = p.id
            LEFT JOIN product_variations pv ON sai.variation_id  = pv.id
            LEFT JOIN product_batches   pb  ON sai.batch_id      = pb.batch_id
            LEFT JOIN warehouses        w   ON sa.warehouse_id   = w.id
            LEFT JOIN users             u   ON sa.created_by     = u.id
            WHERE sai.product_id = %s
        """
        params = [product_id]
 
        # ── optional filters ─────────────────────────────────────────
        if warehouse_id:
            query += " AND sa.warehouse_id = %s"
            params.append(warehouse_id)
 
        if variation_id:
            query += " AND sai.variation_id = %s"
            params.append(variation_id)
 
        # created_at is TIMESTAMP → use DATE() for day-level comparison
        if start_date and end_date:
            query += " AND DATE(sa.created_at) BETWEEN %s AND %s"
            params.extend([start_date, end_date])
 
        query += " ORDER BY sa.created_at DESC, sa.adjustment_id DESC"
 
        cursor.execute(query, params)
        rows = cursor.fetchall()
 
        # ── date helpers ─────────────────────────────────────────────
        def fmt_datetime(val):
            """Full datetime string — frontend uses .substring(0, 16)."""
            if not val:
                return None
            if isinstance(val, str):
                return val
            return val.strftime("%Y-%m-%d %H:%M:%S")
 
        def fmt_date(val):
            """Date-only string YYYY-MM-DD."""
            if not val:
                return None
            if isinstance(val, str):
                return val[:10]
            return val.strftime("%Y-%m-%d")
 
        results = []
        for row in rows:
            # item_adjustment_type takes priority over header type;
            # they should always match but item level is the source of truth.
            adj_type = row['item_adjustment_type'] or row['header_adjustment_type']
 
            # reason: header reason first, fall back to item note or header note
            reason = row['reason'] or row['item_note'] or row['header_note']
 
            results.append({
                # ── adjustment header ─────────────────────────────────
                "adjustment_id":            row["adjustment_id"],
                "adjustment_code":          row["adjustment_code"],
                "adjustment_type":          row["header_adjustment_type"],
                "item_adjustment_type":     adj_type,       # frontend badge uses this
                "reason":                   reason,
                "note":                     row["header_note"],
                "total_items":              row["total_items"],
                "warehouse_id":             row["warehouse_id"],
                "store_id":                 row["store_id"],
                "created_at":               fmt_datetime(row["created_at"]),
                "updated_at":               fmt_datetime(row["updated_at"]),
 
                # ── warehouse ─────────────────────────────────────────
                "warehouse_name":           row["warehouse_name"],
 
                # ── user ──────────────────────────────────────────────
                "created_by_name":          row["created_by_name"],
 
                # ── adjustment item line ──────────────────────────────
                "item_id":                  row["item_id"],
                "product_id":               row["product_id"],
                "variation_id":             row["variation_id"],
                "batch_id":                 row["batch_id"],
                "quantity":                 float(row["quantity"]  or 0),
                "item_note":                row["item_note"],
                "item_created_at":          fmt_datetime(row["item_created_at"]),
 
                # ── product ───────────────────────────────────────────
                "product_name":             row["product_name"],
                "product_code":             row["product_code"],   # p.sku
                "sku":                      row["product_code"],   # alias — filterAdjustment() searches 'sku'
                "product_type":             row["product_type"],
 
                # ── variation ─────────────────────────────────────────
                "variation_name":           row["variation_name"],
                "variation_type":           row["variation_type"],
                "variation_sku":            row["variation_sku"],
 
                # ── batch ─────────────────────────────────────────────
                "batch_number":             row["batch_number"],
                "batch_quantity":           float(row["batch_quantity"]           or 0),
                "batch_remaining_quantity": float(row["batch_remaining_quantity"] or 0),
                "batch_cost":               float(row["batch_cost"]               or 0),
                "batch_price":              float(row["batch_price"]              or 0),
                "batch_expiration_date":    fmt_date(row["batch_expiration_date"]),
            })
 
        return jsonify(results), 200
 
    except Exception as e:
        print(f"❌ Error in get_adjustments_by_product: {e}")
        import traceback
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500
 
    finally:
        cursor.close()
        conn.close()