from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from app.models.sample import SampleRecord
from app.models.order import OrderRecord
from app.config import DATABASE_URL

def debug_db():
    engine = create_engine(DATABASE_URL)
    Session = sessionmaker(bind=engine)
    session = Session()

    target_pn_fragment = "PSMQC098N10LS2"
    target_cust_id = "S14500"

    print(f"--- Searching for PN containing '{target_pn_fragment}' ---")

    print("\n[Sample Records]")
    samples = session.query(SampleRecord).filter(SampleRecord.pn.contains(target_pn_fragment)).all()
    for s in samples:
        print(f"ID: {s.id}, CustID: '{s.cust_id}', PN: '{s.pn}', Customer: '{s.customer}', Date: {s.date}")

    print("\n[Order Records]")
    orders = session.query(OrderRecord).filter(OrderRecord.pn.contains(target_pn_fragment)).all()
    for o in orders:
        print(f"ID: {o.id}, CustID: '{o.cust_id}', PN: '{o.pn}', Customer: '{o.customer}', Date: {o.date}")

    print("\n--- Searching for Cust ID '{target_cust_id}' ---")
    
    print("\n[Sample Records with S14500]")
    samples_c = session.query(SampleRecord).filter(SampleRecord.cust_id == target_cust_id).limit(5).all()
    for s in samples_c:
         print(f"ID: {s.id}, CustID: '{s.cust_id}', PN: '{s.pn}'")

    print("\n[Order Records with S14500]")
    orders_c = session.query(OrderRecord).filter(OrderRecord.cust_id == target_cust_id).limit(5).all()
    for o in orders_c:
         print(f"ID: {o.id}, CustID: '{o.cust_id}', PN: '{o.pn}'")

    session.close()

if __name__ == "__main__":
    debug_db()