from sqlalchemy.orm import Session
from sqlalchemy import select, desc, or_
from app.models import SpaceObject, PairScore, PairScoreHistory, ScoringRun
def upsert_space_object(db:Session,payload):
    obj=db.get(SpaceObject,payload["object_id"])
    if obj:
        for k,v in payload.items(): setattr(obj,k,v)
        db.add(obj)
    else: db.add(SpaceObject(**payload))
def list_objects(db:Session,limit=100): return db.scalars(select(SpaceObject).limit(limit)).all()
def get_object(db:Session,object_id): return db.get(SpaceObject,object_id)
def save_pair_score(db:Session,payload):
    row=db.get(PairScore,payload["pair_id"])
    if row:
        for k,v in payload.items(): setattr(row,k,v)
        db.add(row)
    else: db.add(PairScore(**payload))
def insert_pair_history(db:Session,payload): db.add(PairScoreHistory(**payload))
def list_high_risk_pairs(db:Session,limit=50): return db.scalars(select(PairScore).order_by(desc(PairScore.final_score)).limit(limit)).all()
def get_pair(db:Session,pair_id): return db.get(PairScore,pair_id)
def get_pair_history(db:Session,pair_id,limit=20): return db.scalars(select(PairScoreHistory).where(PairScoreHistory.pair_id==pair_id).order_by(desc(PairScoreHistory.created_at)).limit(limit)).all()
def create_run(db:Session,payload): db.add(ScoringRun(**payload))
def latest_runs(db:Session,limit=10): return db.scalars(select(ScoringRun).order_by(desc(ScoringRun.created_at)).limit(limit)).all()
def get_run(db:Session,run_id): return db.get(ScoringRun,run_id)
def object_pairs(db:Session,object_id:str,limit=25):
    stmt=select(PairScore).where(or_(PairScore.primary_object_id==object_id, PairScore.secondary_object_id==object_id)).order_by(desc(PairScore.final_score)).limit(limit)
    return db.scalars(stmt).all()
def pairs_in_same_shell(db:Session,shell_key:str,exclude_pair_id:str,limit=20):
    stmt=select(PairScore).where(PairScore.shell_key==shell_key, PairScore.pair_id!=exclude_pair_id).order_by(desc(PairScore.final_score)).limit(limit)
    return db.scalars(stmt).all()