from django.db import connection
from common.cash_bank_utils import get_current_balance
from common.utils import calculate_financial_year

def transfer_pending_counts(request):
    """
    Calculates pending counts for the Material Transfer system sidebar badges.
    Rules:
    1. Material Request: Count shown ONLY to the Supplying Branch in 'Material Outward'.
    2. Material Outward: Count shown ONLY to the Receiving Branch in 'Material Inward'.
    """
    if 'user_id' not in request.session:
        return {}

    branch_id = request.session.get('branch_id')
    
    # 1. Pending Transfer Outward (Requests RECEIVED BY this branch FROM others)
    # Validation: SUM(request_qty) > SUM(outward_qty) for each request
    pending_transfer_out_count = 0
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT COUNT(*) FROM (
                SELECT r.id
                FROM tm_material_po r
                INNER JOIN tx_material_po ri ON r.id = ri.tm_po_id
                LEFT JOIN (
                    SELECT material_po_id, 
                           SUM(CASE WHEN status = 1 AND is_active = 1 THEN quantity ELSE 0 END) as outward_qty
                    FROM tx_material_out
                    WHERE status = 1 AND is_active = 1
                    GROUP BY material_po_id
                ) o ON r.id = o.material_po_id
                WHERE r.supply_branch_id = %s 
                  AND r.status = 1 AND r.is_active = 1
                  AND ri.status = 1 AND ri.is_active = 1
                  AND r.id NOT IN (
                    SELECT DISTINCT txo.material_po_id 
                    FROM tx_material_out txo
                    INNER JOIN tm_material_out tmo ON txo.tm_material_id = tmo.id
                    WHERE tmo.outward_status = 'rejected'
                      AND txo.material_po_id IS NOT NULL
                  )
                GROUP BY r.id
                HAVING SUM(ri.quantity) > COALESCE(MAX(o.outward_qty), 0)
            ) as pending
        """, [branch_id])
        row = cursor.fetchone()
        pending_transfer_out_count = row[0] if row else 0

    # 2. Pending Transfer Inward (Outwards SENT TO this branch FROM others)
    # Validation: SUM(outward_qty) > SUM(inward_qty) for each outward
    pending_transfer_in_count = 0
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT COUNT(*) FROM (
                SELECT o.id
                FROM tm_material_out o
                INNER JOIN tx_material_out oi ON o.id = oi.tm_material_id
                LEFT JOIN (
                    SELECT material_outward_id, 
                           SUM(CASE WHEN status = 1 AND is_active = 1 THEN quantity ELSE 0 END) as inward_qty
                    FROM tx_material_in
                    WHERE status = 1 AND is_active = 1
                    GROUP BY material_outward_id
                ) i ON o.id = i.material_outward_id
                WHERE o.supply_branch_id = %s 
                  AND o.status = 1 AND o.is_active = 1
                  AND oi.status = 1 AND oi.is_active = 1
                  AND o.outward_status != 'rejected'
                GROUP BY o.id
                HAVING SUM(oi.quantity) > COALESCE(MAX(i.inward_qty), 0)
            ) as pending
        """, [branch_id])
        row = cursor.fetchone()
        pending_transfer_in_count = row[0] if row else 0

    # User rule: Requesting branch does NOT see a count on their own request module
    pending_transfer_req_count = 0 

    return {
        'pending_transfer_req_count': pending_transfer_req_count, 
        'pending_transfer_out_count': pending_transfer_out_count, 
        'pending_transfer_in_count': pending_transfer_in_count,   
        'total_pending_transfer': pending_transfer_out_count + pending_transfer_in_count
    }


def balance_marquee_processor(request):
    """
    Generates balance marquee HTML for store users.
    Shows cash balance and bank-wise balances in a scrolling marquee.
    """
    if 'user_id' not in request.session:
        return {}
    
    user_type = request.session.get('user_type', '')
    
    # Show for store and wholesale users
    if user_type not in ('stores', 'wholesale', 'wholesale_customer'):
        return {'balance_marquee': ''}
    
    branch_id = request.session.get('branch_id')
    fyf_name = request.session.get('fyf')
    
    if not branch_id or not fyf_name:
        return {'balance_marquee': ''}
    
    try:
        financial_year = calculate_financial_year(fyf_name)
        balance_data = get_current_balance(branch_id, financial_year)
        
        # Build marquee HTML
        # Build marquee HTML
        marquee_parts = []

        # Common styles
        # Common styles
        label_style = "color: #000; font-weight: 700;"
        value_style = "color: #dc3545; font-weight: 600;"

        # Add cash balance
        cash_balance = balance_data.get('cash_balance', 0)
        marquee_parts.append(
            f'<span>'
            f'<span style="{label_style}">Cash:</span> '
            f'<span style="{value_style}">₹{cash_balance:.2f}</span>'
            f'</span>'
        )

        # Add bank balances
        bank_list = balance_data.get('bank_wise', [])
        for bank in bank_list:
            bank_name = bank.get('bank_name', 'Unknown')
            bank_balance = bank.get('balance', 0)

            marquee_parts.append(
                f'<span>'
                f'<span style="{label_style}">{bank_name}:</span> '
                f'<span style="{value_style}">₹{bank_balance:.2f}</span>'
                f'</span>'
            )


        # Join with separator
        marquee_html = ' &nbsp;&nbsp;|&nbsp;&nbsp; '.join(marquee_parts)

        
        return {'balance_marquee': marquee_html}
    
    except Exception as e:
        # Silently fail and return empty marquee
        return {'balance_marquee': ''}



def wholesale_internal_count(request):
    """
    Calculates pending counts for wholesale internal sales (B2B Boys).
    Displayed in the Wholesale sidebar and Purchase Inward submenu.
    """
    if 'user_id' not in request.session:
        return {}

    branch_id = request.session.get('branch_id')
    print('branch_id', branch_id)
    if not branch_id:
        return {}

    with connection.cursor() as cursor:
        # Fetch the count from sales_order_table (tm_sales)
        # Filter: sales_channel = wholesale_internal
        # Match customer_id with branch.id
        # Apply branch filters: is_wholesale = 1, store_type = wholesale
        cursor.execute("""
            SELECT COUNT(s.id)
            FROM tm_sales s
            INNER JOIN branches b ON s.customer_id = b.id
            WHERE s.sales_channel = 'wholesale_internal'
              AND b.is_wholesale = 1
              AND b.store_type = 'wholesale'
              AND s.customer_id = %s
              AND s.status = 1 AND s.is_active = 1
              AND s.sales_status = 'pending'
        """, [branch_id])
        row = cursor.fetchone()
        wholesale_internal_count = row[0] if row else 0
        
    return {
        'wholesale_internal_count': wholesale_internal_count
    }


def sales_request_pending_counts(request):
    """
    Calculates pending counts for Sales Requests (Wholesale POs) 
    where the current branch is the supply branch.
    Follows the pattern of transfer_pending_counts.
    """
    if 'user_id' not in request.session:
        return {}

    branch_id = request.session.get('branch_id')
    fyf_name = request.session.get('fyf')
    financial_year = calculate_financial_year(fyf_name) if fyf_name else None

    pending_sales_request_count = 0
    
    if branch_id and financial_year:
        with connection.cursor() as cursor:
            cursor.execute("""
                SELECT COUNT(*)
                FROM tm_wholesale_po
                WHERE supply_branch_id = %s
                  AND po_status = 'pending'
                  AND status = 1 
                  AND is_active = 1
                  AND current_fy = %s
            """, [branch_id, financial_year])
            row = cursor.fetchone()
            pending_sales_request_count = row[0] if row else 0

    return {
        'pending_sales_request_count': pending_sales_request_count,
        'total_pending_sales': pending_sales_request_count # Currently only Sales Request contributes to this
    }