from fastapi import APIRouter, Depends, HTTPException from fastapi.responses import StreamingResponse from sqlalchemy import func from sqlalchemy.orm import Session from typing import List, Optional import io from ..database import get_db from ..models import StockDelivery, PumpReading, DailyShift, TankDipReading, StockAdjustment from ..schemas import StockDeliveryCreate, StockDeliveryOut, StockAdjustmentCreate, StockAdjustmentOut from ..dependencies import get_current_user router = APIRouter() FUEL_TYPES = ['LP92', 'EURO3', 'LAD', 'LADXM'] @router.get("/stock-deliveries", response_model=List[StockDeliveryOut]) def list_deliveries( date_from: Optional[str] = None, date_to: Optional[str] = None, fuel_type: Optional[str] = None, db: Session = Depends(get_db), _=Depends(get_current_user), ): q = db.query(StockDelivery) if date_from: q = q.filter(StockDelivery.delivery_date >= date_from) if date_to: q = q.filter(StockDelivery.delivery_date <= date_to) if fuel_type: q = q.filter(StockDelivery.fuel_type == fuel_type) return q.order_by(StockDelivery.delivery_date.desc()).all() @router.post("/stock-deliveries", response_model=StockDeliveryOut) def create_delivery( body: StockDeliveryCreate, db: Session = Depends(get_db), user=Depends(get_current_user), ): delivery = StockDelivery(received_by=user.id, **body.model_dump()) db.add(delivery) db.commit() db.refresh(delivery) try: from .audit import log_audit log_audit(db, user.id, 'CREATE', 'stock_deliveries', delivery.id, f"Delivery {body.fuel_type} {body.liters}L from {body.supplier or 'N/A'}") db.commit() except Exception: pass return delivery @router.delete("/stock-deliveries/{delivery_id}") def delete_delivery( delivery_id: int, db: Session = Depends(get_db), user=Depends(get_current_user), ): d = db.query(StockDelivery).filter(StockDelivery.id == delivery_id).first() if not d: raise HTTPException(status_code=404, detail="Delivery not found") db.delete(d) db.commit() try: from .audit import log_audit log_audit(db, user.id, 'DELETE', 'stock_deliveries', delivery_id, f"Deleted delivery {delivery_id}") db.commit() except Exception: pass return {"ok": True} @router.get("/stock/summary") def stock_summary( date_from: Optional[str] = None, date_to: Optional[str] = None, db: Session = Depends(get_db), _=Depends(get_current_user), ): """Stock book: per fuel_type — delivered, sold, theoretical closing, latest dip, variance.""" result = [] for ft in FUEL_TYPES: # Total delivered del_q = db.query(func.coalesce(func.sum(StockDelivery.liters), 0)).filter( StockDelivery.fuel_type == ft ) if date_from: del_q = del_q.filter(StockDelivery.delivery_date >= date_from) if date_to: del_q = del_q.filter(StockDelivery.delivery_date <= date_to) total_delivered = float(del_q.scalar()) # Total sold (pump readings sale_ltr for this fuel type) sold_q = ( db.query(func.coalesce(func.sum(PumpReading.sale_ltr), 0)) .join(DailyShift, PumpReading.shift_id == DailyShift.id) .join(PumpReading.pump) ) from ..models import Pump sold_q = sold_q.filter(Pump.fuel_type == ft) if date_from: sold_q = sold_q.filter(DailyShift.record_date >= date_from) if date_to: sold_q = sold_q.filter(DailyShift.record_date <= date_to) total_sold = float(sold_q.scalar()) theoretical_closing = total_delivered - total_sold # Latest dip reading dip_q = db.query(TankDipReading).filter(TankDipReading.tank_name == ft) if date_to: dip_q = dip_q.filter(TankDipReading.read_date <= date_to) latest_dip = dip_q.order_by(TankDipReading.read_date.desc()).first() latest_dip_ltr = float(latest_dip.volume_ltr) if latest_dip else 0.0 # Stock adjustments adj_q = db.query(StockAdjustment).filter(StockAdjustment.fuel_type == ft) if date_from: adj_q = adj_q.filter(StockAdjustment.adjustment_date >= date_from) if date_to: adj_q = adj_q.filter(StockAdjustment.adjustment_date <= date_to) adjustments = adj_q.all() adj_positive = sum( float(a.quantity_ltr) for a in adjustments if a.adjustment_type in ('OPENING_STOCK', 'CORRECTION') and float(a.quantity_ltr) > 0 ) adj_negative = sum( float(a.quantity_ltr) for a in adjustments if a.adjustment_type in ('LOSS', 'EVAPORATION', 'SPILLAGE') or float(a.quantity_ltr) < 0 ) theoretical_closing = total_delivered + adj_positive - abs(adj_negative) - total_sold variance_ltr = latest_dip_ltr - theoretical_closing result.append({ "fuel_type": ft, "total_delivered_ltr": round(total_delivered, 2), "total_sold_ltr": round(total_sold, 2), "adj_positive_ltr": round(adj_positive, 2), "adj_negative_ltr": round(adj_negative, 2), "theoretical_closing": round(theoretical_closing, 2), "latest_dip_ltr": round(latest_dip_ltr, 2), "variance_ltr": round(variance_ltr, 2), }) return result # ── Stock Adjustments ───────────────────────────────────────────────────── @router.get("/stock-adjustments", response_model=List[StockAdjustmentOut]) def list_adjustments( date_from: Optional[str] = None, date_to: Optional[str] = None, fuel_type: Optional[str] = None, db: Session = Depends(get_db), _=Depends(get_current_user), ): q = db.query(StockAdjustment) if date_from: q = q.filter(StockAdjustment.adjustment_date >= date_from) if date_to: q = q.filter(StockAdjustment.adjustment_date <= date_to) if fuel_type: q = q.filter(StockAdjustment.fuel_type == fuel_type) return q.order_by(StockAdjustment.adjustment_date.desc(), StockAdjustment.id.desc()).all() @router.post("/stock-adjustments", response_model=StockAdjustmentOut) def create_adjustment( body: StockAdjustmentCreate, db: Session = Depends(get_db), user=Depends(get_current_user), ): adj = StockAdjustment(**body.model_dump(), created_by=user.id) db.add(adj) db.commit() db.refresh(adj) return adj @router.delete("/stock-adjustments/{adj_id}") def delete_adjustment( adj_id: int, db: Session = Depends(get_db), _=Depends(get_current_user), ): adj = db.query(StockAdjustment).filter(StockAdjustment.id == adj_id).first() if not adj: raise HTTPException(404, "Adjustment not found") db.delete(adj) db.commit() return {"ok": True} @router.get("/stock/reconciliation") def stock_reconciliation( date_from: Optional[str] = None, date_to: Optional[str] = None, db: Session = Depends(get_db), _=Depends(get_current_user), ): """Detailed per-fuel reconciliation with adjustment breakdown.""" result = [] for ft in FUEL_TYPES: deliveries = db.query(StockDelivery).filter(StockDelivery.fuel_type == ft) if date_from: deliveries = deliveries.filter(StockDelivery.delivery_date >= date_from) if date_to: deliveries = deliveries.filter(StockDelivery.delivery_date <= date_to) total_delivered = sum(float(d.liters) for d in deliveries.all()) sold_q = ( db.query(func.coalesce(func.sum(PumpReading.sale_ltr), 0)) .join(DailyShift, PumpReading.shift_id == DailyShift.id) .join(PumpReading.pump) ) from ..models import Pump sold_q = sold_q.filter(Pump.fuel_type == ft) if date_from: sold_q = sold_q.filter(DailyShift.record_date >= date_from) if date_to: sold_q = sold_q.filter(DailyShift.record_date <= date_to) total_sold = float(sold_q.scalar()) adj_q = db.query(StockAdjustment).filter(StockAdjustment.fuel_type == ft) if date_from: adj_q = adj_q.filter(StockAdjustment.adjustment_date >= date_from) if date_to: adj_q = adj_q.filter(StockAdjustment.adjustment_date <= date_to) adjustments = adj_q.all() adj_detail = [ { "id": a.id, "date": str(a.adjustment_date), "type": a.adjustment_type, "qty": float(a.quantity_ltr), "reason": a.reason, } for a in adjustments ] adj_positive = sum( float(a.quantity_ltr) for a in adjustments if a.adjustment_type in ('OPENING_STOCK', 'CORRECTION') and float(a.quantity_ltr) > 0 ) adj_negative = sum( float(a.quantity_ltr) for a in adjustments if a.adjustment_type in ('LOSS', 'EVAPORATION', 'SPILLAGE') or float(a.quantity_ltr) < 0 ) theoretical = total_delivered + adj_positive - abs(adj_negative) - total_sold dip = ( db.query(TankDipReading) .filter(TankDipReading.tank_name == ft) .order_by(TankDipReading.read_date.desc()) .first() ) latest_dip = float(dip.volume_ltr) if dip else 0.0 result.append({ "fuel_type": ft, "total_delivered_ltr": round(total_delivered, 2), "total_sold_ltr": round(total_sold, 2), "adjustments": adj_detail, "adj_positive_ltr": round(adj_positive, 2), "adj_negative_ltr": round(adj_negative, 2), "theoretical_closing": round(theoretical, 2), "latest_dip_ltr": round(latest_dip, 2), "variance_ltr": round(latest_dip - theoretical, 2), }) return result @router.get("/stock/reconciliation/pdf") def stock_reconciliation_pdf( date_from: Optional[str] = None, date_to: Optional[str] = None, db: Session = Depends(get_db), _=Depends(get_current_user), ): data = stock_reconciliation(date_from=date_from, date_to=date_to, db=db) from reportlab.lib.pagesizes import A4 from reportlab.lib import colors from reportlab.lib.styles import getSampleStyleSheet from reportlab.lib.units import mm from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer buf = io.BytesIO() doc = SimpleDocTemplate(buf, pagesize=A4, rightMargin=15*mm, leftMargin=15*mm, topMargin=15*mm, bottomMargin=15*mm) styles = getSampleStyleSheet() story = [Paragraph("Stock Reconciliation Report", styles['Heading1']), Spacer(1, 5*mm)] headers = ['Fuel', 'Delivered (L)', 'Sold (L)', 'Adj+ (L)', 'Adj- (L)', 'Theoretical (L)', 'Latest Dip (L)', 'Variance (L)'] rows = [headers] for r in data: rows.append([ r['fuel_type'], f"{r['total_delivered_ltr']:,.2f}", f"{r['total_sold_ltr']:,.2f}", f"{r['adj_positive_ltr']:,.2f}", f"{r['adj_negative_ltr']:,.2f}", f"{r['theoretical_closing']:,.2f}", f"{r['latest_dip_ltr']:,.2f}", f"{r['variance_ltr']:,.2f}", ]) t = Table(rows, repeatRows=1) t.setStyle(TableStyle([ ('BACKGROUND', (0,0), (-1,0), colors.HexColor('#1e293b')), ('TEXTCOLOR', (0,0), (-1,0), colors.white), ('FONTSIZE', (0,0), (-1,-1), 9), ('GRID', (0,0), (-1,-1), 0.5, colors.lightgrey), ('ALIGN', (1,0), (-1,-1), 'RIGHT'), ('ROWBACKGROUNDS', (0,1), (-1,-1), [colors.white, colors.HexColor('#f8fafc')]), ])) story.append(t) doc.build(story) buf.seek(0) return StreamingResponse( buf, media_type='application/pdf', headers={'Content-Disposition': 'attachment; filename="stock_reconciliation.pdf"'}, )