import datetime

from sqlalchemy import or_

from extensions.ext_database import db
from models.account import Account
from models.dept import Dept
from services.account_service import AccountService


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_account(dept_id):
        dept_account = []
        account_results = (
            db.session.query(Account.dept_id, Account.id, Account.email)
            .filter(Account.status == "active", Account.dept_id == str(dept_id))
            .all()
        )
        for row in account_results:
            dept_account.append({"account_id": row.id, "email": row.email})
        return dept_account

    @staticmethod
    def get_dept_list():
        dept_list = []

        condition = or_(Dept.parent_dept_id == None, Dept.parent_dept_id == "")
        dept_results = db.session.query(Dept).filter(Dept.status == "active", condition).all()

        print(str(dept_results))
        for dept_row in dept_results:
            children_dept_list = []
            children_depts = (
                db.session.query(Dept)
                .filter(Dept.status == "active", Dept.parent_dept_id == dept_row.dept_id)
                .all()
            )
            for children_dept in children_depts:
                children_dept_list.append(
                    {
                        "dept_id": children_dept.dept_id,
                        "dept_name": children_dept.dept_name,
                        "parent_dept_id": children_dept.parent_dept_id,
                    }
                )
            dept_list.append(
                {
                    "parent_dept_id": "",
                    "dept_id": dept_row.dept_id,
                    "dept_name": dept_row.dept_name,
                    "children": children_dept_list,
                }
            )
        return dept_list

    @staticmethod
    def get_dept_by_name(dept_name):
        dept = db.session.query(Dept).filter(Dept.status == "active", Dept.dept_name == dept_name).first()
        return dept

    @staticmethod
    def get_dept_by_id_name(dept_id, dept_name):
        dept = (
            db.session.query(Dept)
            .filter(Dept.status == "active", Dept.dept_name == dept_name, Dept.dept_id == dept_id)
            .first()
        )
        return dept

    @staticmethod
    def get_dept_by_id(dept_id):
        dept = db.session.query(Dept).filter(Dept.status == "active", Dept.dept_id == dept_id).first()
        return dept

    @staticmethod
    def save_dept(dept_name, current_user, parent_dept_id):
        dept = Dept(
            dept_name=dept_name,
            status="active",
            created_at=datetime.datetime.now(),
            created_by=current_user.id,
            parent_dept_id=parent_dept_id,
        )
        db.session.add(dept)
        db.session.flush()
        db.session.commit()

    @staticmethod
    def update_dept(dept_id, dept_name, current_user, parent_dept_id):
        sql = (
            db.session.query(Dept)
            .filter(Dept.dept_id == dept_id)
            .update(
                {
                    "updated_by": current_user.id,
                    "updated_at": datetime.datetime.now(),
                    "dept_name": dept_name,
                    "parent_dept_id": parent_dept_id,
                }
            )
        )
        print(str(sql))
        db.session.commit()

    @staticmethod
    def delete_dept(dept):
        AccountService.delete_account_dept(dept)
        db.session.delete(dept)
        db.session.commit()

    @staticmethod
    def save_dept_account_list(dept_id, dept_account_list):
        for dept_account in dept_account_list:
            account_id = dept_account.get("account_id")
            AccountService.update_account_dept(dept_id, account_id)

    @staticmethod
    def delete_dept_account_list(dept_id, dept_account_list):
        for dept_account in dept_account_list:
            account_id = dept_account.get("account_id")
            AccountService.update_account_dept("", account_id)