import logging from fastapi import APIRouter from fastapi.responses import JSONResponse from fastapi.encoders import jsonable_encoder from database import session from sqlalchemy import func from datetime import date from decimal import Decimal from app.models.auto import Auto_Delivery, Tickets_Auto_Delivery, Auto_Temp, KFactorHistory from app.models.delivery import Delivery from app.constants import DEFAULT_TANK_SIZE_GALLONS from app.script.fuel_estimator import FuelEstimator logger = logging.getLogger(__name__) # Constants from fuel_estimator HOT_WATER_DAILY_USAGE = Decimal('1.0') K_FACTOR_SMOOTHING_WEIGHT = Decimal('0.7') TUNING_FACTOR = Decimal('1.1') router = APIRouter( prefix="/fixstuff", tags=["fixstuff"], responses={404: {"description": "Not found"}}, ) @router.get("/lastdelivered", status_code=200) def fix_customer_last_delivered(): """ Updates the last_fill date in the auto_delivery table for each customer by finding the most recent completed delivery (ticket with non-NULL fill_date) from the auto_tickets table, matched by account_number. Returns statistics and a list of changes made. """ logger.info("GET /fixstuff/lastdelivered - Fixing customer last delivered dates") auto_deliveries = session.query(Auto_Delivery).all() changes = [] total_customers = len(auto_deliveries) tickets_found = 0 updates_made = 0 for ad in auto_deliveries: latest_ticket = session.query(Tickets_Auto_Delivery).filter( Tickets_Auto_Delivery.account_number == ad.account_number, Tickets_Auto_Delivery.fill_date.isnot(None) ).order_by(Tickets_Auto_Delivery.fill_date.desc()).first() if latest_ticket: tickets_found += 1 if ad.last_fill != latest_ticket.fill_date: updates_made += 1 old_date = ad.last_fill ad.last_fill = latest_ticket.fill_date changes.append({ "id": ad.id, "customer_full_name": ad.customer_full_name, "before_date": str(old_date) if old_date else None, "new_date": str(latest_ticket.fill_date) }) session.add(ad) session.commit() result = { "total_customers": total_customers, "tickets_found": tickets_found, "updates_made": updates_made, "changes": changes } return JSONResponse(content=jsonable_encoder(result)) @router.get("/estimate_gallons/{update_db}", status_code=200) def estimate_customer_gallons(update_db: int): """ Estimates current gallons for each customer based on delivery history and weather. update_db: 0 for estimation only (no DB changes), 1 for estimation with DB updates. No tickets: assume 100 gallons. Single delivery: use weather for 2000 sq ft home. Multiple deliveries: use historical average. Includes address and scaling factor. When update_db=1, updates estimated_gallons_left and house_factor in database. """ logger.info(f"GET /fixstuff/estimate_gallons/{update_db} - Estimating customer gallons (update_db={update_db})") auto_deliveries = session.query(Auto_Delivery).all() estimates = [] for ad in auto_deliveries: tickets = session.query(Tickets_Auto_Delivery).filter( Tickets_Auto_Delivery.account_number == ad.account_number, Tickets_Auto_Delivery.fill_date.isnot(None) ).order_by(Tickets_Auto_Delivery.fill_date).all() # Get tank size and hot water setting tank_size = Decimal(ad.tank_size) if ad.tank_size else Decimal(DEFAULT_TANK_SIZE_GALLONS) # Adjust effective tank capacity (not filled to 100%) if tank_size == DEFAULT_TANK_SIZE_GALLONS: effective_tank = Decimal('250') elif tank_size == 330: effective_tank = Decimal('300') else: effective_tank = tank_size hot_water = ad.hot_water_summer == 1 calculated_scaling = None # For DB update if not tickets: estimated_gallons = Decimal('100') calculated_scaling = Decimal('0.12') # No deliveries = use average baseline else: last_fill = tickets[-1].fill_date estimated_gallons_left = effective_tank today = date.today() if len(tickets) == 1: # Single delivery: use weather data for 2000 sq ft home, only heat when temp <=65 calculated_scaling = Decimal('0.12') if last_fill < today: # Get daily weather data temp_days = session.query(Auto_Temp).filter( Auto_Temp.todays_date > last_fill, Auto_Temp.todays_date <= today ).all() heating_usage = Decimal('0') hot_water_usage = Decimal('0') house_factor_2000_sqft = Decimal('0.12') # gallons per degree day (average) for temp in temp_days: degree_day = max(0, 65 - float(temp.temp_avg)) heating_usage += house_factor_2000_sqft * Decimal(degree_day) if hot_water: hot_water_usage += HOT_WATER_DAILY_USAGE total_usage = heating_usage + hot_water_usage estimated_gallons_left = max(Decimal('0'), estimated_gallons_left - total_usage) else: # Multiple deliveries: calculate house_factor (gallons per degree day) daily_heating_usages = [] avg_degree_per_days = [] for i in range(len(tickets) - 1): prev_ticket = tickets[i] next_ticket = tickets[i + 1] days = (next_ticket.fill_date - prev_ticket.fill_date).days if days > 0: # Calculate degree days for this interval from temp_avg interval_temps = session.query(Auto_Temp).filter( Auto_Temp.todays_date > prev_ticket.fill_date, Auto_Temp.todays_date <= next_ticket.fill_date ).all() total_degree_days = sum(max(0, 65 - float(temp.temp_avg)) for temp in interval_temps) total_degree_days = Decimal(total_degree_days) avg_degree_per_day = total_degree_days / days total_hot_water = HOT_WATER_DAILY_USAGE * days gallons_heating = prev_ticket.gallons_delivered - total_hot_water if gallons_heating > 0 and total_degree_days > 0: daily_heating = gallons_heating / days daily_heating_usages.append(daily_heating) avg_degree_per_days.append(avg_degree_per_day) if daily_heating_usages and avg_degree_per_days: average_daily_heating = sum(daily_heating_usages) / len(daily_heating_usages) average_degree_days_per_day = sum(avg_degree_per_days) / len(avg_degree_per_days) house_factor = average_daily_heating / average_degree_days_per_day calculated_scaling = house_factor * TUNING_FACTOR else: house_factor = Decimal('0.12') # Default average calculated_scaling = house_factor # Calculate usage from last_fill to today using temperature-dependent heating if last_fill < today: temp_days = session.query(Auto_Temp).filter( Auto_Temp.todays_date > last_fill, Auto_Temp.todays_date <= today ).all() heating_usage = Decimal('0') hot_water_usage = Decimal('0') for temp in temp_days: degree_day = max(0, 65 - float(temp.temp_avg)) heating_usage += house_factor * Decimal(degree_day) if hot_water: hot_water_usage += HOT_WATER_DAILY_USAGE total_usage = heating_usage + hot_water_usage estimated_gallons_left = max(Decimal('0'), estimated_gallons_left - total_usage) estimated_gallons = estimated_gallons_left # Update database if requested if update_db == 1: ad.estimated_gallons_left = estimated_gallons if calculated_scaling is not None: ad.house_factor = calculated_scaling session.add(ad) last_5 = tickets[-5:] if tickets else [] scaling_factor = float(ad.house_factor) if ad.house_factor else None estimates.append({ "id": ad.id, "total_deliveries": len(tickets), "customer_full_name": ad.customer_full_name, "account_number": ad.account_number, "address": ad.customer_address, "estimated_gallons": float(estimated_gallons), "scaling_factor": scaling_factor, "last_5_deliveries": [ { "fill_date": str(t.fill_date), "gallons_delivered": float(t.gallons_delivered), "price_per_gallon": float(t.price_per_gallon), "total_amount_customer": float(t.total_amount_customer) } for t in last_5 ] }) if update_db == 1: session.commit() return JSONResponse(content=jsonable_encoder(estimates)) @router.get("/backfill_kfactor_history", status_code=200) def backfill_kfactor_history(): """ Backfill the auto_kfactor_history table from existing ticket data. For each auto customer with 2+ tickets, calculates K-factor for each consecutive ticket pair and inserts into history. Then runs the rolling K-factor calculation to set the customer's house_factor, confidence, and source. """ logger.info("GET /fixstuff/backfill_kfactor_history - Starting K-factor history backfill") estimator = FuelEstimator(session=session) auto_deliveries = session.query(Auto_Delivery).all() stats = { "customers_processed": 0, "customers_skipped": 0, "history_entries_created": 0, "customers_updated": 0, } for ad in auto_deliveries: tickets = session.query(Tickets_Auto_Delivery).filter( Tickets_Auto_Delivery.customer_id == ad.customer_id, Tickets_Auto_Delivery.fill_date.isnot(None) ).order_by(Tickets_Auto_Delivery.fill_date).all() if len(tickets) < 2: stats["customers_skipped"] += 1 # Set division avg for customers with <2 tickets if ad.confidence_score is None: ad.confidence_score = 20 if ad.k_factor_source is None: ad.k_factor_source = 'default' continue stats["customers_processed"] += 1 # Check if this customer already has history entries existing = session.query(KFactorHistory).filter( KFactorHistory.customer_id == ad.customer_id ).count() if existing > 0: continue for i in range(len(tickets) - 1): prev_ticket = tickets[i] next_ticket = tickets[i + 1] start_date = prev_ticket.fill_date end_date = next_ticket.fill_date num_days = (end_date - start_date).days if num_days <= 0: continue # Calculate HDD for the interval interval_temps = session.query(Auto_Temp).filter( Auto_Temp.todays_date > start_date, Auto_Temp.todays_date <= end_date ).all() total_hdd = Decimal(sum(max(0, 65 - float(temp.temp_avg)) for temp in interval_temps)) if total_hdd == 0: continue # Hot water adjustment total_hot_water = Decimal('0.0') if ad.hot_water_summer == 1: total_hot_water = Decimal(num_days) * HOT_WATER_DAILY_USAGE gallons_for_heating = next_ticket.gallons_delivered - total_hot_water k_factor_obs = None if gallons_for_heating > 0 and total_hdd > 0: k_factor_obs = gallons_for_heating / total_hdd is_budget = estimator._is_budget_fill(next_ticket.gallons_delivered) # Flag the ticket too next_ticket.is_budget_fill = is_budget history_entry = KFactorHistory( customer_id=ad.customer_id, ticket_id=next_ticket.id, fill_date=next_ticket.fill_date, gallons_delivered=next_ticket.gallons_delivered, total_hdd=total_hdd, days_in_period=num_days, k_factor=k_factor_obs, is_budget_fill=is_budget, is_outlier=False, created_at=date.today() ) session.add(history_entry) stats["history_entries_created"] += 1 # Flush so rolling calc can see the new entries session.flush() # Run rolling K-factor calculation to set customer values new_k, confidence, source = estimator._calculate_rolling_k_factor(ad.customer_id) ad.house_factor = new_k ad.confidence_score = confidence ad.k_factor_source = source stats["customers_updated"] += 1 session.commit() logger.info(f"Backfill complete: {stats}") return JSONResponse(content=jsonable_encoder(stats))