from datetime import datetime, timedelta
from sqlalchemy.orm import Session
from db import SessionLocal
from models.bid import Bid
from models.auth import User
from gridstatus import ISONE
from zoneinfo import ZoneInfo

NEW_ENGLAND_TZ = ZoneInfo("America/New_York")
iso = ISONE()

def get_day_ahead_price(target_time: datetime) -> float:
    """Fetch the Day Ahead clearing price for the hour of target_time."""
    df = iso.get_lmp(date=target_time.date(), market="DAY_AHEAD_HOURLY", locations="ALL")
    df = df.groupby("Interval Start")["LMP"].mean().reset_index()

    for _, row in df.iterrows():
        if abs(row["Interval Start"] - target_time.replace(minute=0, second=0, microsecond=0)) < timedelta(minutes=30):
            return row["LMP"]

    raise ValueError(f"No day ahead price found for {target_time}")

def get_real_time_prices(target_time: datetime) -> list[float]:
    """Fetch the Real Time 5-min prices during the hour of target_time."""
    df = iso.get_lmp(date=target_time.date(), market="REAL_TIME_5_MIN", locations="ALL")
    df = df.groupby("Interval Start")["LMP"].mean().reset_index()

    start_of_hour = target_time.replace(minute=0, second=0, microsecond=0)
    end_of_hour = start_of_hour + timedelta(hours=1)

    prices = [
        row["LMP"]
        for _, row in df.iterrows()
        if start_of_hour <= row["Interval Start"] < end_of_hour
    ]

    return prices

def process_bids():
    db: Session = SessionLocal()

    now = datetime.now(NEW_ENGLAND_TZ)

    # Step 1: Process Submitted bids
    submitted_bids = db.query(Bid).filter(Bid.status == "Submitted").all()
    for bid in submitted_bids:
        try:
            target_time = bid.timestamp.astimezone(NEW_ENGLAND_TZ)

            # Only process bids whose timestamp is already in the past
            if target_time > now:
                print(f"Skipping future bid {bid.id} at {target_time}")
                continue

            print(f"Processing submitted bid {bid.id} for {target_time}")

            day_ahead_price = get_day_ahead_price(target_time)

            if bid.price >= day_ahead_price:
                # Bid was accepted
                bid.status = "Executed"
                bid.pnl = None  # Will be calculated later
                print(f"Bid {bid.id}: Executed (waiting for real-time PnL)")
            else:
                # Bid rejected
                bid.status = "Fail"
                bid.pnl = None
                print(f"Bid {bid.id}: Failed (price too low)")

            db.commit()

        except Exception as e:
            print(f"Error processing submitted bid {bid.id}: {e}")

    # Step 2: Process Executed bids (calculate PnL)
    executed_bids = db.query(Bid).filter(Bid.status == "Executed").all()
    for bid in executed_bids:
        try:
            target_time = bid.timestamp.astimezone(NEW_ENGLAND_TZ)

            # Only settle if the whole real-time period is over
            if now < target_time + timedelta(hours=1):
                print(f"Skipping bid {bid.id}, real-time window not complete yet")
                continue

            print(f"Settling executed bid {bid.id} for {target_time}")

            real_time_prices = get_real_time_prices(target_time)

            if not real_time_prices:
                print(f"No real-time prices available for bid {bid.id} yet")
                continue  # Retry later

            avg_real_time_price = sum(real_time_prices) / len(real_time_prices)

            day_ahead_price = get_day_ahead_price(target_time)

            pnl = (day_ahead_price - avg_real_time_price) * bid.quantity

            bid.status = "Settled"
            bid.pnl = pnl
            print(f"Bid {bid.id} Settled | PnL: ${pnl:.2f}")

            db.commit()

        except Exception as e:
            print(f"Error settling executed bid {bid.id}: {e}")

    db.close()

if __name__ == "__main__":
    process_bids()