from sqlalchemy.orm import Session
from typing import List, Optional
from datetime import datetime
from sqlalchemy import func
import logging
from src.smart_inventory.apps.inventory.schemas import WebhookData
from . import models
from ..products.models import Product, Location, Vendor, Company, Discount, ProductVarient

logger = logging.getLogger(__name__)

async def process_webhook_event(
    db: Session,
    event_type: str,
    data: WebhookData,
):
    data = WebhookData(**data)
    company = db.query(Company).filter(Company.company_id == data.CompanyId).first() if data.CompanyId else None
    vendor = db.query(Vendor).filter(Vendor.vendor_id == data.VendorId).first() if data.VendorId else None
    fromlocation = db.query(Location).filter(Location.location_id == data.FromLocationId).first() if data.FromLocationId else None
    tolocation = db.query(Location).filter(Location.location_id == data.ToLocationId).first() if data.ToLocationId else None
    
    # purchase order
    if event_type == "PO":
        logger.info("Processing Purchase Order event")
        # Add your logic for handling Purchase Order events here
        podata = models.PurchaseOrder(
            company_id=company.id if company else None,
            supplier_id=vendor.id if vendor else None,
            location_id=fromlocation.id if fromlocation else None,
            status=models.PurchaseOrderStatus.SENT,
            ref_id=data.RefId,
            ref_number=data.RefNumber,
            linked_ref_number=data.LinkedRefNumber,
            linked_ref_datetime=data.LinkedRefDateTime,
            order_date=data.RefDateTime,
        )
        db.add(podata)  
        db.commit()
        db.refresh(podata)
        
        for item in data.ItemDetail:
            if item.ProductVarientDetailId:
                product_varient = db.query(ProductVarient).filter(
                    ProductVarient.varient_id == item.ProductVarientDetailId
                ).first()
                if not product_varient:
                    logger.warning(f"Product Variant with ID {item.ProductVarientDetailId} not found. Skipping item.")
                    continue
                product = (
                        db.query(Product)
                        .filter(Product.product_id == item.ProductId)
                        .filter(Product.company_id == company.id if company else None)
                        .filter(Product.product_variant_id == product_varient.id)
                        .first()
                )
            else:
                product = (
                            db.query(Product)
                            .filter(Product.product_id == item.ProductId)
                            .filter(Product.company_id == company.id if company else None)
                            .first()
                        )
            if not product:
                logger.warning(f"Product with ID {item.ProductId} not found. Skipping item.")
                continue
            
            po_item = models.PurchaseOrderLine(
                purchase_order_id=podata.id,
                company_id=company.id if company else None,
                product_id=product.id,
                ordered_qty=item.Quantity,
                received_qty=0,
            )
            db.add(po_item)
            
        db.commit()

    # purchase receive order
    elif event_type == "RO":
        logger.info("Processing Purchase Receive Order event")
        # Add your logic for handling Return Order events here
        logger.info(data)
        podata = db.query(models.PurchaseOrder).filter(models.PurchaseOrder.ref_number == data.LinkedRefNumber).first()
        if not podata:
             logger.error(f"Purchase Order with RefId {data.RefId} not found for Receive Purchase Order.")
             return {"status": "error", "message": f"Purchase Order with RefId {data.RefId} not found."}
        #podata.status = models.PurchaseOrderStatus.RECEIVED
        #db.commit()
        rodata = models.PurchaseReceiveOrder(
            company_id=company.id if company else None,
            supplier_id=vendor.id if vendor else None,
            location_id=fromlocation.id if fromlocation else None,
            purchase_order_id=podata.id,
            status=models.PurchaseOrderStatus.RECEIVED,
            ref_id=data.RefId,
            ref_number=data.RefNumber,
            linked_ref_number=data.LinkedRefNumber,
            linked_ref_datetime=data.LinkedRefDateTime,
            received_date=data.RefDateTime,
        )
        db.add(rodata)  
        db.commit()
        db.refresh(rodata)
        
        for item in data.ItemDetail:
            if item.ProductVarientDetailId:
                product_varient = db.query(ProductVarient).filter(
                    ProductVarient.varient_id == item.ProductVarientDetailId
                ).first()
                if not product_varient:
                    logger.warning(f"Product Variant with ID {item.ProductVarientDetailId} not found. Skipping item.")
                    continue
                product = (
                        db.query(Product)
                        .filter(Product.product_id == item.ProductId)
                        .filter(Product.company_id == company.id if company else None)
                        .filter(Product.product_variant_id == product_varient.id)
                        .first()
                )
            else:
                product = (
                            db.query(Product)
                            .filter(Product.product_id == item.ProductId)
                            .filter(Product.company_id == company.id if company else None)
                            .first()
                        )
            if not product:
                logger.warning(f"Product with ID {item.ProductId} not found. Skipping item.")
                continue
            
            po_item = models.PurchaseOrderReceiveLine(
                purchase_order_receive_id=rodata.id,
                company_id=company.id if company else None,
                product_id=product.id,
                received_qty=item.Quantity,
            )
            db.add(po_item)
            
            # Update received_qty in PurchaseOrderLine
            po_line = db.query(models.PurchaseOrderLine).filter(
                models.PurchaseOrderLine.purchase_order_id == podata.id,
                models.PurchaseOrderLine.product_id == product.id
            ).first()
            if po_line:
                po_line.received_qty = po_line.received_qty + item.Quantity
                db.add(po_line)
            
            # Update InventoryBatch
            currentstock_fl = db.query(models.InventoryBatch).filter(
                models.InventoryBatch.company_id == company.id if company else None,
                models.InventoryBatch.product_id == product.id,
                models.InventoryBatch.location_id == fromlocation.id if fromlocation else None,
            ).first()
            
            if currentstock_fl:
                currentstock_fl.quantity_on_hand = currentstock_fl.quantity_on_hand + item.Quantity
                # currentstock_fl.batch_ref = data.RefNumber
                currentstock_fl.received_date = data.RefDateTime or datetime.now()
                currentstock_fl.updated_at = datetime.now()
            else:
                currentstock_fl = models.InventoryBatch(
                    company_id=company.id if company else None,
                    product_id=product.id,
                    location_id=fromlocation.id if fromlocation else None,
                    batch_ref=data.RefNumber,
                    quantity_on_hand=item.Quantity,
                    received_date=data.RefDateTime or datetime.now(),
                    created_at=datetime.now(),
                    updated_at=datetime.now(),
                )
            db.add(currentstock_fl)
                
            fl_move = models.InventoryMovement(
                company_id=company.id if company else None,
                product_id=product.id,
                location_id=fromlocation.id if fromlocation else None,
                batch_id=currentstock_fl.id if currentstock_fl else None,
                quantity_delta=item.Quantity,
                movement_type=models.MovementType.RECEIPT,
                reference=rodata.id,
            )
            db.add(fl_move)
        
        # Check if all PO lines are fully received, then update PO status
        all_po_lines = db.query(models.PurchaseOrderLine).filter(
            models.PurchaseOrderLine.purchase_order_id == podata.id
        ).all()
        all_received = all(line.received_qty >= line.ordered_qty for line in all_po_lines)
        if all_received:
            podata.status = models.PurchaseOrderStatus.RECEIVED
            db.add(podata)
            
        db.commit()
        
    # purchase return
    elif event_type == "VR":
        logger.info("Processing Vendor Return event")
        # Add your logic for handling Credit Note events here
        logger.info(data)
        podata = db.query(models.PurchaseReceiveOrder).filter(models.PurchaseReceiveOrder.ref_number == data.LinkedRefNumber).first()
        if not podata:
             logger.error(f"Purchase Order with RefId {data.RefId} not found for Receive Purchase Order.")
             return {"status": "error", "message": f"Purchase Order with RefId {data.RefId} not found."}
         
        rodata = models.PurchaseReturnOrder(
            company_id=company.id if company else None,
            supplier_id=vendor.id if vendor else None,
            location_id=fromlocation.id if fromlocation else None,
            purchase_receive_order_id=podata.id,
            status=models.PurchaseOrderStatus.RETURNED,
            ref_id=data.RefId,
            ref_number=data.RefNumber,
            linked_ref_number=data.LinkedRefNumber,
            linked_ref_datetime=data.LinkedRefDateTime,
            returned_date=data.RefDateTime,
        )
        db.add(rodata)  
        db.commit()
        db.refresh(rodata)
        
        for item in data.ItemDetail:
            if item.ProductVarientDetailId:
                product_varient = db.query(ProductVarient).filter(
                    ProductVarient.varient_id == item.ProductVarientDetailId
                ).first()
                if not product_varient:
                    logger.warning(f"Product Variant with ID {item.ProductVarientDetailId} not found. Skipping item.")
                    continue
                product = (
                        db.query(Product)
                        .filter(Product.product_id == item.ProductId)
                        .filter(Product.company_id == company.id if company else None)
                        .filter(Product.product_variant_id == product_varient.id)
                        .first()
                )
            else:
                product = (
                            db.query(Product)
                            .filter(Product.product_id == item.ProductId)
                            .filter(Product.company_id == company.id if company else None)
                            .first()
                        )
            if not product:
                logger.warning(f"Product with ID {item.ProductId} not found. Skipping item.")
                continue
            
            po_item = models.PurchaseOrderReturnLine(
                purchase_order_return_id=rodata.id,
                company_id=company.id if company else None,
                product_id=product.id,
                return_qty=item.Quantity,
            )
            db.add(po_item)
            
            # Add your logic for handling Sales Order events here
            currentstock_fl = db.query(models.InventoryBatch).filter(
                models.InventoryBatch.company_id == company.id if company else None,
                models.InventoryBatch.product_id == product.id,
                models.InventoryBatch.location_id == fromlocation.id if fromlocation else None,
            ).first()
            
            if currentstock_fl:
                currentstock_fl.quantity_on_hand = currentstock_fl.quantity_on_hand - item.Quantity
                # currentstock_fl.batch_ref = data.RefNumber
                currentstock_fl.received_date = data.RefDateTime or datetime.now()
                currentstock_fl.updated_at = datetime.now()
                db.add(currentstock_fl)
                
            fl_move = models.InventoryMovement(
                company_id=company.id if company else None,
                product_id=product.id,
                location_id=fromlocation.id if fromlocation else None,
                batch_id=currentstock_fl.id if currentstock_fl else None,
                quantity_delta=(item.Quantity * -1),
                movement_type=models.MovementType.PURCHASE_RETURN,
                reference=rodata.id,
            )
            db.add(fl_move)
            
        db.commit()
        
    # sales order
    elif event_type == "ORD":
        logger.info("Processing Sales Order event")
        # Add your logic for handling Sales Order events here
        sodata = models.SalesOrder(
            company_id=company.id if company else None,            
            location_id=tolocation.id if tolocation else None,
            ref_id=data.RefId,
            ref_number=data.RefNumber,
            linked_ref_number=data.LinkedRefNumber,
            linked_ref_datetime=data.LinkedRefDateTime,
            sold_date=data.RefDateTime,
            channel="store",
        )
        db.add(sodata)
        db.commit()
        db.refresh(sodata)
        for item in data.ItemDetail:
            if item.ProductVarientDetailId:
                product_varient = db.query(ProductVarient).filter(
                    ProductVarient.varient_id == item.ProductVarientDetailId
                ).first()
                if not product_varient:
                    logger.warning(f"Product Variant with ID {item.ProductVarientDetailId} not found. Skipping item.")
                    continue
                product = (
                        db.query(Product)
                        .filter(Product.product_id == item.ProductId)
                        .filter(Product.company_id == company.id if company else None)
                        .filter(Product.product_variant_id == product_varient.id)
                        .first()
                )
            else:
                product = (
                            db.query(Product)
                            .filter(Product.product_id == item.ProductId)
                            .filter(Product.company_id == company.id if company else None)
                            .first()
                        )
            if not product:
                logger.warning(f"Product with ID {item.ProductId} not found. Skipping item.")
                continue
            
            if item.PromotionId:
                promotion = db.query(Discount).filter(
                    Discount.discount_id == item.PromotionId
                ).first()
            
            so_item = models.SalesOrderLine(
                sales_order_id=sodata.id,
                company_id=company.id if company else None,
                product_id=product.id,
                promotion_id=promotion.id if item.PromotionId and promotion  else None,
                quantity=item.Quantity,
            )
            db.add(so_item)
            
            # Add your logic for handling Sales Order events here
            currentstock_fl = db.query(models.InventoryBatch).filter(
                models.InventoryBatch.company_id == company.id if company else None,
                models.InventoryBatch.product_id == product.id,
                models.InventoryBatch.location_id == fromlocation.id if fromlocation else None,
            ).first()
            
            if currentstock_fl:
                currentstock_fl.quantity_on_hand = currentstock_fl.quantity_on_hand - item.Quantity
                # currentstock_fl.batch_ref = data.RefNumber
                currentstock_fl.received_date = data.RefDateTime or datetime.now()
                currentstock_fl.updated_at = datetime.now()
                db.add(currentstock_fl)
                
            fl_move = models.InventoryMovement(
                company_id=company.id if company else None,
                product_id=product.id,
                location_id=fromlocation.id if fromlocation else None,
                batch_id=currentstock_fl.id if currentstock_fl else None,
                quantity_delta=(item.Quantity * -1),
                movement_type=models.MovementType.SALE,
                reference=sodata.id,
            )
            db.add(fl_move)
            
        db.commit()
    
    # sales order return
    elif event_type == "RTO":
        logger.info("Processing Sales Order Return event")
        # Add your logic for handling Sales Order events here
        logger.info(data)
        podata = db.query(models.SalesOrder).filter(models.SalesOrder.ref_number == data.LinkedRefNumber).first()
        if not podata:
             logger.error(f"Sales Order with RefId {data.RefId} not found for Receive Purchase Order.")
             return {"status": "error", "message": f"Purchase Order with RefId {data.RefId} not found."}
         
        rodata = models.SalesReturnOrder(
            company_id=company.id if company else None,            
            location_id=tolocation.id if tolocation else None,
            ref_id=data.RefId,
            ref_number=data.RefNumber,
            linked_ref_number=data.LinkedRefNumber,
            linked_ref_datetime=data.LinkedRefDateTime,
            returned_date=data.RefDateTime,
            channel="store",
            sales_order_id=podata.id,
        )
        db.add(rodata)  
        db.commit()
        db.refresh(rodata)
        
        for item in data.ItemDetail:
            if item.ProductVarientDetailId:
                product_varient = db.query(ProductVarient).filter(
                    ProductVarient.varient_id == item.ProductVarientDetailId
                ).first()
                if not product_varient:
                    logger.warning(f"Product Variant with ID {item.ProductVarientDetailId} not found. Skipping item.")
                    continue
                product = (
                        db.query(Product)
                        .filter(Product.product_id == item.ProductId)
                        .filter(Product.company_id == company.id if company else None)
                        .filter(Product.product_variant_id == product_varient.id)
                        .first()
                )
            else:
                product = (
                            db.query(Product)
                            .filter(Product.product_id == item.ProductId)
                            .filter(Product.company_id == company.id if company else None)
                            .first()
                        )
            if not product:
                logger.warning(f"Product with ID {item.ProductId} not found. Skipping item.")
                continue
            
            po_item = models.SalesReturnOrderLine(
                sales_return_order_id=rodata.id,
                company_id=company.id if company else None,
                product_id=product.id,
                quantity=item.Quantity,
            )
            db.add(po_item)
            
            # Add your logic for handling Sales Order events here
            currentstock_fl = db.query(models.InventoryBatch).filter(
                models.InventoryBatch.company_id == company.id if company else None,
                models.InventoryBatch.product_id == product.id,
                models.InventoryBatch.location_id == fromlocation.id if fromlocation else None,
            ).first()
            
            if currentstock_fl:
                currentstock_fl.quantity_on_hand = currentstock_fl.quantity_on_hand + item.Quantity
                # currentstock_fl.batch_ref = data.RefNumber
                currentstock_fl.received_date = data.RefDateTime or datetime.now()
                currentstock_fl.updated_at = datetime.now()
                db.add(currentstock_fl)
                
            fl_move = models.InventoryMovement(
                company_id=company.id if company else None,
                product_id=product.id,
                location_id=fromlocation.id if fromlocation else None,
                batch_id=currentstock_fl.id if currentstock_fl else None,
                quantity_delta=item.Quantity,
                movement_type=models.MovementType.SALE_RETURN,
                reference=rodata.id,
            )
            db.add(fl_move)
            
        db.commit()

    
    # stock transfer
    elif event_type == "STR":
        logger.info("Processing Stock transfer event")
        for item in data.ItemDetail:
            if item.ProductVarientDetailId:
                product_varient = db.query(ProductVarient).filter(
                    ProductVarient.varient_id == item.ProductVarientDetailId
                ).first()
                if not product_varient:
                    logger.warning(f"Product Variant with ID {item.ProductVarientDetailId} not found. Skipping item.")
                    continue
                product = (
                        db.query(Product)
                        .filter(Product.product_id == item.ProductId)
                        .filter(Product.company_id == company.id if company else None)
                        .filter(Product.product_variant_id == product_varient.id)
                        .first()
                )
            else:
                product = (
                            db.query(Product)
                            .filter(Product.product_id == item.ProductId)
                            .filter(Product.company_id == company.id if company else None)
                            .first()
                        )
            if not product:
                logger.warning(f"Product with ID {item.ProductId} not found. Skipping item.")
                continue
            
            # Add your logic for handling Sales Order events here
            currentstock_fl = db.query(models.InventoryBatch).filter(
                models.InventoryBatch.company_id == company.id if company else None,
                models.InventoryBatch.product_id == product.id,
                models.InventoryBatch.location_id == fromlocation.id if fromlocation else None,
            ).first()
            
            if currentstock_fl:
                currentstock_fl.quantity_on_hand = currentstock_fl.quantity_on_hand - item.Quantity
                # currentstock_fl.batch_ref = data.RefNumber
                currentstock_fl.received_date = data.RefDateTime or datetime.now()
                currentstock_fl.updated_at = datetime.now()
                db.add(currentstock_fl)
                
            fl_move = models.InventoryMovement(
                company_id=company.id if company else None,
                product_id=product.id,
                location_id=fromlocation.id if fromlocation else None,
                batch_id=currentstock_fl.id if currentstock_fl else None,
                quantity_delta=(item.Quantity * -1),
                movement_type=models.MovementType.TRANSFER_OUT,
                reference=data.RefNumber,
            )
            db.add(fl_move)
            db.commit()

            currentstock_tl = db.query(models.InventoryBatch).filter(
                models.InventoryBatch.company_id == company.id if company else None,
                models.InventoryBatch.product_id == product.id,
                models.InventoryBatch.location_id == tolocation.id if tolocation else None,
            ).first()
            
            if currentstock_tl:
                currentstock_tl.quantity_on_hand = currentstock_tl.quantity_on_hand + item.Quantity
                # currentstock_tl.batch_ref = data.RefNumber
                currentstock_tl.received_date = data.RefDateTime or datetime.now()
                currentstock_tl.updated_at = datetime.now()
                db.add(currentstock_tl)
            db.commit()
            
            tl_move = models.InventoryMovement(
                company_id=company.id if company else None,
                product_id=product.id,
                location_id=fromlocation.id if fromlocation else None,
                batch_id=currentstock_tl.id if currentstock_tl else None,
                quantity_delta=item.Quantity,
                movement_type=models.MovementType.TRANSFER_IN,
                reference=data.RefNumber,
            )
            db.add(tl_move)
            db.commit()

    return {"status": "unhandled event type", "data": data}