eamco_auto_api/app/routers/fixstuff_auto.py

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))