import json import traceback from datetime import datetime from django.utils import timezone from django.db.models import Sum from sales.models import POSOrder from inventory.models import Product, Supplier, PurchaseOrder, PurchaseOrderItem from analytics.models import OperatingExpense def get_stock_report(company): """ Returns a JSON string of stock numbers: low stock, out of stock items, and top 5 items. """ try: low_stock = list(Product.objects.filter(company=company, stock_status='low_stock').values('name', 'stock_quantity', 'sku')) out_of_stock = list(Product.objects.filter(company=company, stock_status='out_of_stock').values('name', 'sku')) # Optionally get top quantities top_stock = list(Product.objects.filter(company=company).order_by('-stock_quantity').values('name', 'stock_quantity')[:5]) return json.dumps({ "low_stock_items": low_stock, "out_of_stock_items": out_of_stock, "highest_stock_items": top_stock }) except Exception as e: return json.dumps({"error": str(e)}) def get_financial_summary(company, start_date=None, end_date=None): """ Returns a financial summary (sales, revenue, profit, expenses) for a specific date range. start_date and end_date should be 'YYYY-MM-DD' strings. If none, defaults to today. """ try: if not start_date: start_dt = timezone.now().date() else: start_dt = datetime.strptime(start_date, '%Y-%m-%d').date() if not end_date: end_dt = start_dt else: end_dt = datetime.strptime(end_date, '%Y-%m-%d').date() orders = POSOrder.objects.filter( company=company, status='completed', created_at__date__gte=start_dt, created_at__date__lte=end_dt ) total_revenue = orders.aggregate(total=Sum('final_amount'))['total'] or 0 total_tax = orders.aggregate(total=Sum('tax_amount'))['total'] or 0 # Calculate COGS correctly using product cost_price (not selling price) from sales.models import POSOrderItem cogs = 0 gross_profit = 0 items_qs = POSOrderItem.objects.filter(order__in=orders).select_related('product') for item in items_qs: cost = float(item.product.cost_price or 0) if item.product else 0.0 qty = float(item.quantity) revenue_line = float(item.unit_price) * qty cogs_line = cost * qty cogs += cogs_line gross_profit += revenue_line - cogs_line unpaid = orders.filter(balance_amount__gt=0).aggregate(total=Sum('balance_amount'))['total'] or 0 expenses = OperatingExpense.objects.filter( company=company, expense_date__gte=start_dt, expense_date__lte=end_dt ).aggregate(total=Sum('amount'))['total'] or 0 net_profit = gross_profit - float(expenses) return json.dumps({ "period": f"{start_dt} to {end_dt}", "total_revenue": float(total_revenue), "total_tax_collected": float(total_tax), "cogs": round(cogs, 2), "gross_profit": round(gross_profit, 2), "operating_expenses": float(expenses), "net_profit": round(net_profit, 2), "unpaid_credit_issued": float(unpaid), "orders_count": orders.count() }) except Exception as e: print(traceback.format_exc()) return json.dumps({"error": str(e)}) def draft_purchase_order(company, supplier_name, items): """ Drafts a purchase order. supplier_name (str): Name of supplier to search for. items (list): List of dicts like [{"sku_or_name": "Milk", "quantity": 10}, ...] """ try: # Find supplier supplier = Supplier.objects.filter(company=company, name__icontains=supplier_name).first() if not supplier: return json.dumps({"error": f"Supplier containing '{supplier_name}' not found."}) po = PurchaseOrder.objects.create( supplier=supplier, status="draft", reference_number=f"PO-AI-{timezone.now().strftime('%M%S')}" ) added_items = [] missing_items = [] # Parse items securely (in case AI sends string instead of dict) if isinstance(items, str): try: items = json.loads(items) except: items = [] for item in items: search_term = item.get('sku_or_name', '') qty = int(item.get('quantity', 1)) prod = Product.objects.filter(company=company, name__icontains=search_term).first() if not prod: prod = Product.objects.filter(company=company, sku__icontains=search_term).first() if prod: PurchaseOrderItem.objects.create( purchase_order=po, product=prod, quantity=qty, unit_cost=prod.cost_price or 0 ) added_items.append(prod.name) else: missing_items.append(search_term) if not added_items: po.delete() return json.dumps({"error": "Failed to match any products for the PO. Please check the product names."}) return json.dumps({ "success": True, "message": f"Drafted Purchase Order #{po.reference_number} for {supplier.name}.", "purchase_order_id": po.id, "added_products": added_items, "not_found_products": missing_items, "link": f"/inventory/purchase-orders/{po.id}/" }) except Exception as e: return json.dumps({"error": str(e)}) def add_operating_expense(company, amount, category_name, reference=None, notes=None): """ Log a new operating expense for the company. amount (float/str): The expense cost/amount. category_name (str): The name of the expense category (e.g. Rent, Transport, Utilities, Salaries). reference (str, optional): Short description/receipt reference. notes (str, optional): More detailed logs. """ try: from decimal import Decimal from analytics.models import ExpenseCategory, OperatingExpense cat_name = str(category_name).strip().title() category, _ = ExpenseCategory.objects.get_or_create( company=company, name=cat_name ) expense = OperatingExpense.objects.create( company=company, category=category, amount=Decimal(str(amount)), reference=reference or '', notes=notes or '' ) return json.dumps({ "success": True, "message": f"Successfully logged expense of {expense.amount} under category '{category.name}'.", "expense_id": expense.id, "category": category.name, "amount": float(expense.amount), "date": str(expense.expense_date) }) except Exception as e: return json.dumps({"error": str(e)}) def lookup_product_details(company, search_term): """ Lookup detailed product information (price, cost, inventory levels, unit of measure, status). search_term (str): Product name, SKU, or barcode to search for. """ try: prod = Product.objects.filter(company=company, name__icontains=search_term).first() if not prod: prod = Product.objects.filter(company=company, sku__iexact=search_term).first() if not prod: prod = Product.objects.filter(company=company, barcode__iexact=search_term).first() if not prod: return json.dumps({"error": f"Product matching '{search_term}' not found in your inventory."}) return json.dumps({ "name": prod.name, "sku": prod.sku, "barcode": prod.barcode or 'None', "category": prod.category.name if prod.category else 'Uncategorized', "price": float(prod.price), "cost_price": float(prod.cost_price) if prod.cost_price else 0.0, "margin_pct": round(((float(prod.price) - float(prod.cost_price or 0)) / float(prod.price)) * 100, 2) if prod.price else 0.0, "stock_quantity": float(prod.stock_quantity), "minimum_stock": prod.minimum_stock, "stock_status": prod.get_stock_status_display(), "unit_of_measure": prod.unit_of_measure, "status": prod.get_status_display() }) except Exception as e: return json.dumps({"error": str(e)}) def get_supplier_directory(company): """ Return a list of all suppliers and their contact information. """ try: suppliers = Supplier.objects.filter(company=company) result = [] for s in suppliers: result.append({ "name": s.name, "phone": s.phone or 'N/A', "email": s.email or 'N/A', "address": s.address or 'N/A', "balance": float(s.balance) }) return json.dumps({ "suppliers": result, "total_count": len(result) }) except Exception as e: return json.dumps({"error": str(e)}) def get_customer_insights(company): """ Returns top customers based on spending and outstanding debts (balances). """ try: from decimal import Decimal from django.db.models import Sum, DecimalField from django.db.models.functions import Coalesce from sales.models import Customer, POSOrder from django.db.models import OuterRef, Subquery customers = Customer.objects.filter(company=company) spent_sub = POSOrder.objects.filter( company=company, customer=OuterRef('pk'), status='completed' ).values('customer').annotate(total=Sum('final_amount')).values('total') debt_sub = POSOrder.objects.filter( company=company, customer=OuterRef('pk'), balance_amount__gt=0 ).values('customer').annotate(total=Sum('balance_amount')).values('total') annotated_customers = customers.annotate( spent=Coalesce(Subquery(spent_sub, output_field=DecimalField()), Decimal("0.0")), debt=Coalesce(Subquery(debt_sub, output_field=DecimalField()), Decimal("0.0")) ) spenders_qs = annotated_customers.order_by('-spent')[:5] debtors_qs = annotated_customers.filter(debt__gt=0).order_by('-debt')[:5] top_spenders = [] for c in spenders_qs: top_spenders.append({ "id": c.id, "name": c.name, "phone": c.phone or 'N/A', "total_spent": float(c.spent) }) top_debtors = [] for c in debtors_qs: top_debtors.append({ "id": c.id, "name": c.name, "phone": c.phone or 'N/A', "outstanding_balance": float(c.debt) }) return json.dumps({ "top_spenders": top_spenders, "top_debtors": top_debtors, "total_customers_count": Customer.objects.filter(company=company).count() }) except Exception as e: print(traceback.format_exc()) return json.dumps({"error": str(e)}) def add_customer(company, name, phone=None, email=None, address=None, notes=None): """ Directly registers/adds a new customer in the database. """ try: from sales.models import Customer name_clean = str(name).strip() if not name_clean: return json.dumps({"error": "Customer name is required."}) customer = Customer.objects.create( company=company, name=name_clean, phone=(phone or '').strip(), email=(email or '').strip(), address=(address or '').strip(), notes=(notes or '').strip(), created_by="AI Assistant" ) return json.dumps({ "success": True, "message": f"Successfully registered customer '{customer.name}' with phone '{customer.phone or 'N/A'}'.", "customer_id": customer.id, "name": customer.name, "phone": customer.phone }) except Exception as e: return json.dumps({"error": str(e)}) def get_company_settings(company): """ Returns current company profile and operational settings. """ try: settings_obj = company.settings return json.dumps({ "company_name": company.name, "business_name": settings_obj.business_name or company.name, "address": settings_obj.business_address or company.address or 'None', "phone": settings_obj.business_phone or company.phone or 'None', "email": settings_obj.business_email or company.email or 'None', "currency_code": settings_obj.currency, "currency_symbol": settings_obj.currency_symbol, "vat_rate_pct": float(settings_obj.vat_rate), "pos_interface_mode": settings_obj.get_pos_interface_mode_display(), "receipt_header": settings_obj.receipt_header or 'None', "receipt_footer": settings_obj.receipt_footer or 'None', "thermal_printer_name": settings_obj.thermal_printer_name or 'None', "receipt_width_mm": settings_obj.receipt_width_mm, "whatsapp_receipts_enabled": settings_obj.send_receipt_via_whatsapp }) except Exception as e: return json.dumps({"error": str(e)}) def update_company_settings(company, business_phone=None, business_email=None, receipt_header=None, receipt_footer=None, thermal_printer_name=None, receipt_width_mm=None): """ Allows updating specific fields on the company's operational settings. """ try: settings_obj = company.settings updated_fields = [] if business_phone is not None: settings_obj.business_phone = str(business_phone).strip() updated_fields.append("business_phone") if business_email is not None: settings_obj.business_email = str(business_email).strip() updated_fields.append("business_email") if receipt_header is not None: settings_obj.receipt_header = str(receipt_header).strip() updated_fields.append("receipt_header") if receipt_footer is not None: settings_obj.receipt_footer = str(receipt_footer).strip() updated_fields.append("receipt_footer") if thermal_printer_name is not None: settings_obj.thermal_printer_name = str(thermal_printer_name).strip() updated_fields.append("thermal_printer_name") if receipt_width_mm is not None: try: width = int(receipt_width_mm) if width in [58, 80]: settings_obj.receipt_width_mm = width updated_fields.append("receipt_width_mm") except (ValueError, TypeError): pass if updated_fields: settings_obj.save() return json.dumps({ "success": True, "message": f"Successfully updated settings fields: {', '.join(updated_fields)}.", "updated_fields": updated_fields }) else: return json.dumps({"success": True, "message": "No fields were updated."}) except Exception as e: return json.dumps({"error": str(e)})