import datetime
from sqlalchemy import func, or_, not_, case, and_
from sqlalchemy.sql import null

from atlas_um.pgdb import (
    Auth0Account,
    Auth0AccountExternalState,
    DNAAccount,
    DNAAccountExternalState,
)
from atlas_um.pgdb import pgdb


def accounts_states_report():
    all_accounts_subquery = (
        pgdb.session.query(
            func.lower(DNAAccount.email).label("email"),
            DNAAccountExternalState.is_active.label("is_active"),
            or_(
                DNAAccount.expiration_date == None,  # noqa
                DNAAccount.expiration_date > datetime.datetime.now().date(),
            ).label("is_active_atlas"),
            null().label("is_active_auth0"),
            DNAAccountExternalState.last_login.label("last_login"),
        )
        .select_from(DNAAccount)
        .join(
            DNAAccountExternalState,
            DNAAccount.id == DNAAccountExternalState.dna_account_id,
            isouter=True,
        )
        .union(
            pgdb.session.query(
                func.lower(Auth0Account.email).label("email"),
                Auth0AccountExternalState.is_active.label("is_active"),
                null().label("is_active_atlas"),
                not_(Auth0Account.blocked).label("is_active_auth0"),
                Auth0AccountExternalState.last_login.label("last_login"),
            )
            .select_from(Auth0Account)
            .join(
                Auth0AccountExternalState,
                Auth0Account.id == Auth0AccountExternalState.auth0_account_id,
                isouter=True,
            )
        )
        .subquery()
    )

    accounts_query = (
        pgdb.session.query(
            all_accounts_subquery.c.email,
            func.bool_or(all_accounts_subquery.c.is_active),
            func.bool_or(all_accounts_subquery.c.is_active_atlas),
            func.bool_or(all_accounts_subquery.c.is_active_auth0),
            func.max(all_accounts_subquery.c.last_login),
            func.max(
                case(
                    (
                        and_(
                            all_accounts_subquery.c.is_active == None,  # noqa
                            all_accounts_subquery.c.is_active_atlas
                            == True,  # noqa
                        ),
                        "Atlas invitation was not sent for external account",
                    ),
                    (
                        and_(
                            all_accounts_subquery.c.is_active == None,  # noqa
                            or_(
                                all_accounts_subquery.c.is_active_atlas
                                == False,  # noqa
                                all_accounts_subquery.c.is_active_atlas
                                == None,  # noqa
                            ),
                            all_accounts_subquery.c.is_active_auth0
                            == True,  # noqa
                        ),
                        "Auth0 only external account",
                    ),
                    else_="",
                )
            ).label("comments"),
        )
        .select_from(all_accounts_subquery)
        .where(
            all_accounts_subquery.c.email != None,  # noqa
            or_(
                all_accounts_subquery.c.is_active_atlas == True,  # noqa
                all_accounts_subquery.c.is_active_auth0 == True,  # noqa
            ),
            or_(
                all_accounts_subquery.c.is_active == None,  # noqa
                all_accounts_subquery.c.is_active == False,  # noqa
            ),
        )
        .group_by(all_accounts_subquery.c.email)
        .order_by(all_accounts_subquery.c.email)
    )

    rows = accounts_query.all()

    headers = [
        "Email",
        "Is Active in Sony USM",
        "Is Active in Atlas",
        "Is Active in Auth0",
        "Last Login",
        "Comments",
    ]

    yield headers

    for row in rows:
        result_row = list(row)
        yield result_row