from extensions.ext_database import db
from models.account import Account
from models.dept import Dept


class DeptService:

    @staticmethod
    def get_dept_account_list():
        dept_list=[]
        account_list=[]

        dept_results = (
            db.session.query(Dept.dept_id, Dept.dept_name)
            .filter(Dept.status == 'active')
            .all()
        )

        account_results = (
            db.session.query(Account.dept_id, Account.id,Account.email)
            .filter(Account.status == 'active')
            .all()
        )
        for dept_row in dept_results:
            for account_row in account_results:
                if account_row.dept_id == dept_row.dept_id:
                   account_list.append({"account_id":account_row.id,"email":account_row.email})
            dept_list.append({"dept_id": dept_row.dept_id, "dept_name": dept_row.dept_name,"accounts":account_list})

        return dept_list
    @staticmethod
    def get_dept_list():
        dept_list = []
        dept_results = (
            db.session.query(Dept.dept_id, Dept.dept_name)
            .filter(Dept.status == 'active')
            .all()
        )
        for dept_row in dept_results:
            dept_list.append({"dept_id": dept_row.dept_id, "dept_name": dept_row.dept_name})
        return  dept_list