import datetime
import typing

from sqlalchemy import func, or_, not_, any_
from sqlalchemy.sql import expression

from atlas_um.consts import PRODUCTS_TO_AUTH0_ROLES, Products
from atlas_um.pgdb import (
    DNAAccount,
    Auth0Account,
    DNAAccountActivity,
    Auth0AccountRole,
    Auth0Role,
    dna_accounts_enabled_resource_groups,
    ResourceGroup,
)
from atlas_um.pgdb import pgdb


def get_accounts_with_statuses_by_emails(
    emails: typing.Optional[typing.List[str]] = None,
    filter_products: typing.Optional[typing.List[str]] = None,
):
    if emails is None:
        emails = []

    if filter_products is None:
        filter_products = []

    auth0_roles = []
    for product in filter_products:
        try:
            product_enum = Products(product)
        except ValueError:
            continue
        auth0_roles.extend(PRODUCTS_TO_AUTH0_ROLES.get(product_enum, []))

    auth0_account_ids_by_roles_subquery = (
        pgdb.session.query(Auth0AccountRole.auth0_account_id)
        .join(Auth0Role, Auth0Role.id == Auth0AccountRole.role_id)
        .where(Auth0Role.name.in_(auth0_roles))
    )

    dna_account_ids_by_products_subquery = (
        pgdb.session.query(
            dna_accounts_enabled_resource_groups.c.dna_account_id
        )
        .join(
            ResourceGroup,
            ResourceGroup.id
            == dna_accounts_enabled_resource_groups.c.resource_group_id,
        )
        .where(ResourceGroup.namespace_url.in_(filter_products))
    )

    all_accounts_subquery = (
        pgdb.session.query(
            func.lower(DNAAccount.email).label("email"),
            expression.label(
                "is_active",
                or_(
                    DNAAccount.expiration_date == None,  # noqa
                    DNAAccount.expiration_date
                    > datetime.datetime.now().date(),
                ),
            ),
            DNAAccountActivity.last_login.label("last_login"),
        )
        .select_from(DNAAccount)
        .where(
            DNAAccount.email.ilike(any_(emails)),
            or_(
                DNAAccount.id.in_(dna_account_ids_by_products_subquery),
                bool(filter_products) != True,  # noqa
            ),
        )
        .join(
            DNAAccountActivity,
            DNAAccountActivity.dna_account_id == DNAAccount.id,
            isouter=True,
        )
        .union(
            pgdb.session.query(
                func.lower(Auth0Account.email).label("email"),
                not_(Auth0Account.blocked).label("is_active"),
                Auth0Account.last_login.label("last_login"),
            ).where(
                Auth0Account.email.ilike(any_(emails)),
                or_(
                    Auth0Account.id.in_(auth0_account_ids_by_roles_subquery),
                    bool(filter_products) != True,  # noqa
                ),
            )
        )
        .subquery()
    )

    accounts_query = (
        pgdb.session.query(
            all_accounts_subquery.c.email.label("email"),
            func.bool_or(all_accounts_subquery.c.is_active).label("is_active"),
            func.max(all_accounts_subquery.c.last_login).label("last_login"),
        )
        .select_from(all_accounts_subquery)
        .where(all_accounts_subquery.c.email != None)  # noqa
        .group_by(all_accounts_subquery.c.email)
        .order_by(all_accounts_subquery.c.email)
    )

    return accounts_query


def get_accounts_with_statuses_by_ids(
    ids: typing.Optional[typing.List[str]] = None,
    filter_products: typing.Optional[typing.List[str]] = None,
):
    if ids is None:
        ids = []

    if filter_products is None:
        filter_products = []

    auth0_roles = []
    for product in filter_products:
        try:
            product_enum = Products(product)
        except ValueError:
            continue
        auth0_roles.extend(PRODUCTS_TO_AUTH0_ROLES.get(product_enum, []))

    auth0_account_ids_by_roles_subquery = (
        pgdb.session.query(Auth0AccountRole.auth0_account_id)
        .join(Auth0Role, Auth0Role.id == Auth0AccountRole.role_id)
        .where(Auth0Role.name.in_(auth0_roles))
    )

    dna_account_ids_by_products_subquery = (
        pgdb.session.query(
            dna_accounts_enabled_resource_groups.c.dna_account_id
        )
        .join(
            ResourceGroup,
            ResourceGroup.id
            == dna_accounts_enabled_resource_groups.c.resource_group_id,
        )
        .where(ResourceGroup.namespace_url.in_(filter_products))
    )

    all_accounts_subquery = (
        pgdb.session.query(
            DNAAccount.sub.label("id"),
            expression.label(
                "is_active",
                or_(
                    DNAAccount.expiration_date == None,  # noqa
                    DNAAccount.expiration_date
                    > datetime.datetime.now().date(),
                ),
            ),
            DNAAccountActivity.last_login.label("last_login"),
        )
        .select_from(DNAAccount)
        .where(
            DNAAccount.sub.ilike(any_(ids)),
            or_(
                DNAAccount.id.in_(dna_account_ids_by_products_subquery),
                bool(filter_products) != True,  # noqa
            ),
        )
        .join(
            DNAAccountActivity,
            DNAAccountActivity.dna_account_id == DNAAccount.id,
            isouter=True,
        )
        .union(
            pgdb.session.query(
                Auth0Account.user_id.label("id"),
                not_(Auth0Account.blocked).label("is_active"),
                Auth0Account.last_login.label("last_login"),
            ).where(
                Auth0Account.user_id.ilike(any_(ids)),
                or_(
                    Auth0Account.id.in_(auth0_account_ids_by_roles_subquery),
                    bool(filter_products) != True,  # noqa
                ),
            )
        )
        .subquery()
    )

    accounts_query = (
        pgdb.session.query(
            all_accounts_subquery.c.id.label("id"),
            func.bool_or(all_accounts_subquery.c.is_active).label("is_active"),
            func.max(all_accounts_subquery.c.last_login).label("last_login"),
        )
        .select_from(all_accounts_subquery)
        .where(all_accounts_subquery.c.id != None)  # noqa
        .group_by(all_accounts_subquery.c.id)
        .order_by(all_accounts_subquery.c.id)
    )

    return accounts_query