services.py

from sqlalchemy import cast, Date
from db import engine
from sqlmodel import Session, select
from models import Settings, Medication_schedule, Storage, Work_schedule

def testConnection():
    try:
        with engine.connect() as connection:
            return "การเชื่อมต่อกับฐานข้อมูลสำเร็จ"
    except Exception as e:
        return f"การเชื่อมต่อกับฐานข้อมูลล้มเหลว: {e}"

# Get data from database
def getSettings(db: Session):
    return db.query(Settings).all()

def getMedication_schedule(db: Session):
    return db.query(Medication_schedule).all()

def getStorage(db: Session):
    return db.query(Storage).all()

def getWorkSchedule(db: Session):
    return db.query(Work_schedule).all()

def getSettingsByDate(db: Session, date: str):
    return db.query(Settings).filter(Settings.exp == date).all()

def getMedication_scheduleByDate(db: Session, date: str):
    return db.query(Medication_schedule).filter(cast(Medication_schedule.date, Date) == date.split('T')[0]).all()

def getStorageByDate(db: Session, date: str):
    return db.query(Storage).filter(Storage.action_date == date).all()

def getWorkScheduleByDate(db: Session, date: str):
    return db.query(Work_schedule).filter(cast(Work_schedule.work_date, Date) == date.split('T')[0]).all()

# Create data in database
def create_settings(madication_name: str, madication_properties: str, exp: str):
    settings_1 = Settings(madication_name=madication_name, madication_properties=madication_properties, exp=exp)
    
    with Session(engine) as session:
        session.add(settings_1)
        session.commit()
        session.refresh(settings_1)

    session.close()

def create_medication_schedule(date: str, quantity_servo1: int, quantity_servo2: int, quantity_servo3: int, quantity_servo4: int):
    medication_schedule_1 = Medication_schedule(date=date, quantity_servo1=quantity_servo1, quantity_servo2=quantity_servo2, quantity_servo3=quantity_servo3, quantity_servo4=quantity_servo4)
    
    with Session(engine) as session:
        session.add(medication_schedule_1)
        session.commit()
        session.refresh(medication_schedule_1)

    session.close()

def create_storage(servo_id: int, quantity: int, action_date: str):
    storage_1 = Storage(servo_id=servo_id, quantity=quantity, action_date=action_date)
    
    with Session(engine) as session:
        session.add(storage_1)
        session.commit()
        session.refresh(storage_1)

    session.close()

def create_work_schedule(medication_id: int, work_date: str):
    work_schedule_1 = Work_schedule(medication_id=medication_id, work_date=work_date)
    
    with Session(engine) as session:
        session.add(work_schedule_1)
        session.commit()
        session.refresh(work_schedule_1)

    session.close()

# Update data in database
def update_settings(id: int, madication_name: str, madication_properties: str, exp: str):
    with Session(engine) as session:
        settings_1 = session.exec(select(Settings).where(Settings.servo_id == id)).one()
        settings_1.madication_name = madication_name
        settings_1.madication_properties = madication_properties
        settings_1.exp = exp
        session.add(settings_1)
        session.commit()
        session.refresh(settings_1)

    session.close()

def update_medication_schedule(id: int, date: str, quantity_servo1: int, quantity_servo2: int, quantity_servo3: int, quantity_servo4: int):
    with Session(engine) as session:
        medication_schedule_1 = session.exec(select(Medication_schedule).where(Medication_schedule.medication_id == id)).one()
        medication_schedule_1.date = date
        medication_schedule_1.quantity_servo1 = quantity_servo1
        medication_schedule_1.quantity_servo2 = quantity_servo2
        medication_schedule_1.quantity_servo3 = quantity_servo3
        medication_schedule_1.quantity_servo4 = quantity_servo4
        session.add(medication_schedule_1)
        session.commit()
        session.refresh(medication_schedule_1)

    session.close()

# Delete data in database
def delete_medication_schedule(id: int):
    with Session(engine) as session:
        statement = select(Medication_schedule).where(Medication_schedule.medication_id == id)  
        results = session.exec(statement)  
        medication_schedule_1 = results.one() 

        session.delete(medication_schedule_1)  
        session.commit()
    session.close()