from datetime import datetime, timedelta

import sqlalchemy
from sqlalchemy.dialects.postgresql import aggregate_order_by

from atlas_um import pgdb
from atlas_um.helpers.ordering import QueryOrdering
from atlas_um.pgdb.views import dna_accounts_global_claims
from atlas_um.settings import Settings

SQL_DATETIME_FORMAT = "YYYY-mm-dd HH24:MI:SS"


def dna_accounts_and_assigned_claims(
    search_term=None,
    status=None,
    resource_group_id=None,
    tag_id=None,
    order=None,
    affected=None,
    token_size_violation=False,
    expire_soon=False,
    timezone=None,
):
    """
    Returns something like pivot table iterable with DNAAccount columns,
    extended with dynamic claims columns based on actual DB data.

    Columns structure:
        "First Name",
        "Last Name",
        "Email",
        "VIP",
        "Status",
        "Personnel Type",
        "Supervisor Email",
        "Location",
        "Business Unit",
        "Employee Corporate Username",
        "Job Title",
        "Expiration Date",
        "Claim1",
        ...
        "ClaimN"

    The first row contains columns names.
    """

    timezone = timezone or "utc"

    # only assigned claim values for each account
    assigned_claims_subquery = (
        pgdb.pgdb.session.query(
            dna_accounts_global_claims.c.dna_account_id.label("id"),
            pgdb.ResourceGroup.id.label("r_id"),
            pgdb.ClaimName.id.label("n_id"),
            sqlalchemy.func.string_agg(
                pgdb.ClaimValue.friendly,
                aggregate_order_by(", ", pgdb.ClaimValue.friendly),
            ).label("value"),
        )
        .select_from(dna_accounts_global_claims)
        .join(pgdb.ClaimValue)
        .join(
            pgdb.ClaimName,
            pgdb.ClaimName.id == dna_accounts_global_claims.c.claim_name_id,
        )
        .join(pgdb.ResourceGroup)
        .filter(
            pgdb.ResourceGroup.is_deleted == False,  # noqa
            pgdb.ClaimName.is_deleted == False,  # noqa
            pgdb.ClaimValue.is_deleted == False,  # noqa
        )
        .group_by(
            dna_accounts_global_claims.c.dna_account_id.label("id"),
            pgdb.ResourceGroup.id.label("r_id"),
            pgdb.ClaimName.id.label("n_id"),
        )
        .union_all(
            # `Enabled` columns
            pgdb.pgdb.session.query(
                dna_accounts_global_claims.c.dna_account_id.label("id"),
                pgdb.ResourceGroup.id.label("r_id"),
                sqlalchemy.func.max(sqlalchemy.literal(0)).label("n_id"),
                sqlalchemy.func.max(sqlalchemy.literal("1")).label("value"),
            )
            .select_from(dna_accounts_global_claims)
            .join(pgdb.ClaimValue)
            .join(
                pgdb.ClaimName,
                pgdb.ClaimName.id
                == dna_accounts_global_claims.c.claim_name_id,
            )
            .join(pgdb.ResourceGroup)
            .filter(
                pgdb.ResourceGroup.is_deleted == False,  # noqa
                pgdb.ClaimName.is_deleted == False,  # noqa
                pgdb.ClaimValue.is_deleted == False,  # noqa
            )
            .group_by(
                dna_accounts_global_claims.c.dna_account_id.label("id"),
                pgdb.ResourceGroup.id.label("r_id"),
            )
            .having(
                sqlalchemy.func.bool_or(
                    dna_accounts_global_claims.c.is_disabled
                )
                == False  # noqa
            )
        )
        .subquery()
    )

    # raw extra data that include claims and account activities
    raw_extra_data_subquery = (
        pgdb.pgdb.session.query(
            pgdb.DNAAccount.id.label("id"),
            pgdb.ResourceGroup.id.label("r_id"),
            pgdb.ResourceGroup.name.label("r_name"),
            pgdb.ClaimName.id.label("n_id"),
            pgdb.ClaimName.friendly.label("n_friendly"),
            sqlalchemy.func.coalesce(
                assigned_claims_subquery.c.value,
                sqlalchemy.literal(""),
            ).label("value"),
        )
        .select_from(pgdb.ResourceGroup)
        .join(pgdb.ClaimName)
        .join(pgdb.DNAAccount, sqlalchemy.true, isouter=True)
        .join(
            assigned_claims_subquery,
            sqlalchemy.and_(
                assigned_claims_subquery.c.id == pgdb.DNAAccount.id,
                assigned_claims_subquery.c.n_id == pgdb.ClaimName.id,
                assigned_claims_subquery.c.r_id == pgdb.ResourceGroup.id,
            ),
            isouter=True,
        )
        .filter(
            pgdb.ResourceGroup.is_deleted == False,  # noqa
            pgdb.ClaimName.is_deleted == False,  # noqa
        )
        .union_all(
            # `Enabled` columns
            pgdb.pgdb.session.query(
                pgdb.DNAAccount.id,
                pgdb.ResourceGroup.id.label("r_id"),
                pgdb.ResourceGroup.name.label("r_name"),
                sqlalchemy.literal(0).label("n_id"),
                sqlalchemy.literal("Enabled").label("n_friendly"),
                sqlalchemy.func.coalesce(
                    assigned_claims_subquery.c.value,
                    sqlalchemy.literal("0"),
                ).label("value"),
            )
            .select_from(pgdb.ResourceGroup)
            .join(pgdb.ClaimName)
            .join(pgdb.DNAAccount, sqlalchemy.true, isouter=True)
            .join(
                assigned_claims_subquery,
                sqlalchemy.and_(
                    assigned_claims_subquery.c.id == pgdb.DNAAccount.id,
                    assigned_claims_subquery.c.n_id == sqlalchemy.literal(0),
                    assigned_claims_subquery.c.r_id == pgdb.ResourceGroup.id,
                ),
                isouter=True,
            )
            .filter(
                pgdb.ResourceGroup.is_deleted == False,  # noqa
                pgdb.ClaimName.is_deleted == False,  # noqa
            )
            .union_all(
                pgdb.pgdb.session.query(
                    pgdb.DNAAccount.id.label("id"),
                    pgdb.ResourceGroup.id.label("r_id"),
                    pgdb.ResourceGroup.name.label("r_name"),
                    sqlalchemy.literal(-3).label("n_id"),
                    sqlalchemy.literal("First Login").label("n_friendly"),
                    sqlalchemy.func.to_char(
                        sqlalchemy.func.timezone(
                            timezone,
                            sqlalchemy.func.timezone(
                                "utc", pgdb.DNAAccountActivity.first_login
                            ),
                        ),
                        SQL_DATETIME_FORMAT,
                    ).label("value"),
                )
                .select_from(pgdb.ResourceGroup)
                .join(pgdb.DNAAccount, sqlalchemy.true, isouter=True)
                .join(
                    pgdb.DNAAccountActivity,
                    sqlalchemy.and_(
                        pgdb.DNAAccountActivity.dna_account_id
                        == pgdb.DNAAccount.id,
                        pgdb.DNAAccountActivity.resource_group_id
                        == pgdb.ResourceGroup.id,
                    ),
                    isouter=True,
                )
                .filter(pgdb.ResourceGroup.is_deleted == False)  # noqa
                .union_all(
                    pgdb.pgdb.session.query(
                        pgdb.DNAAccount.id.label("id"),
                        pgdb.ResourceGroup.id.label("r_id"),
                        pgdb.ResourceGroup.name.label("r_name"),
                        sqlalchemy.literal(-2).label("n_id"),
                        sqlalchemy.literal("Last Login").label("n_friendly"),
                        sqlalchemy.func.to_char(
                            sqlalchemy.func.timezone(
                                timezone,
                                sqlalchemy.func.timezone(
                                    "utc", pgdb.DNAAccountActivity.last_login
                                ),
                            ),
                            SQL_DATETIME_FORMAT,
                        ).label("value"),
                    )
                    .select_from(pgdb.ResourceGroup)
                    .join(pgdb.DNAAccount, sqlalchemy.true, isouter=True)
                    .join(
                        pgdb.DNAAccountActivity,
                        sqlalchemy.and_(
                            pgdb.DNAAccountActivity.dna_account_id
                            == pgdb.DNAAccount.id,
                            pgdb.DNAAccountActivity.resource_group_id
                            == pgdb.ResourceGroup.id,
                        ),
                        isouter=True,
                    )
                    .filter(pgdb.ResourceGroup.is_deleted == False)  # noqa
                    .union_all(
                        pgdb.pgdb.session.query(
                            pgdb.DNAAccount.id.label("id"),
                            pgdb.ResourceGroup.id.label("r_id"),
                            pgdb.ResourceGroup.name.label("r_name"),
                            sqlalchemy.literal(-1).label("n_id"),
                            sqlalchemy.literal("Last Activity").label(
                                "n_friendly"
                            ),
                            sqlalchemy.func.to_char(
                                sqlalchemy.func.timezone(
                                    timezone,
                                    sqlalchemy.func.timezone(
                                        "utc",
                                        pgdb.DNAAccountActivity.last_activity,
                                    ),
                                ),
                                SQL_DATETIME_FORMAT,
                            ).label("value"),
                        )
                        .select_from(pgdb.ResourceGroup)
                        .join(pgdb.DNAAccount, sqlalchemy.true, isouter=True)
                        .join(
                            pgdb.DNAAccountActivity,
                            sqlalchemy.and_(
                                pgdb.DNAAccountActivity.dna_account_id
                                == pgdb.DNAAccount.id,
                                pgdb.DNAAccountActivity.resource_group_id
                                == pgdb.ResourceGroup.id,
                            ),
                            isouter=True,
                        )
                        .filter(pgdb.ResourceGroup.is_deleted == False)  # noqa
                    )
                )
            )
        )
        .subquery()
    )

    extra_column_names_query = (
        pgdb.pgdb.session.query(
            pgdb.ResourceGroup.id.label("r_id"),
            pgdb.ResourceGroup.name.label("r_name"),
            pgdb.ClaimName.id.label("n_id"),
            pgdb.ClaimName.friendly.label("n_friendly"),
        )
        .select_from(pgdb.ResourceGroup)
        .join(pgdb.ClaimName)
        .filter(
            pgdb.ResourceGroup.is_deleted == False,  # noqa
            pgdb.ClaimName.is_deleted == False,  # noqa
        )
        .union_all(
            # `Enabled` columns
            pgdb.pgdb.session.query(
                pgdb.ResourceGroup.id.label("r_id"),
                pgdb.ResourceGroup.name.label("r_name"),
                sqlalchemy.literal(0).label("n_id"),
                sqlalchemy.literal("Enabled").label("n_friendly"),
            )
            .select_from(pgdb.ResourceGroup)
            .filter(
                pgdb.ResourceGroup.is_deleted == False,  # noqa
            )
            .union_all(
                pgdb.pgdb.session.query(
                    pgdb.ResourceGroup.id.label("r_id"),
                    pgdb.ResourceGroup.name.label("r_name"),
                    sqlalchemy.literal(-3).label("n_id"),
                    sqlalchemy.literal("First Login").label("n_friendly"),
                )
                .select_from(pgdb.ResourceGroup)
                .filter(pgdb.ResourceGroup.is_deleted == False)  # noqa
                .union_all(
                    pgdb.pgdb.session.query(
                        pgdb.ResourceGroup.id.label("r_id"),
                        pgdb.ResourceGroup.name.label("r_name"),
                        sqlalchemy.literal(-2).label("n_id"),
                        sqlalchemy.literal("Last Login").label("n_friendly"),
                    )
                    .select_from(pgdb.ResourceGroup)
                    .filter(pgdb.ResourceGroup.is_deleted == False)  # noqa
                    .union_all(
                        pgdb.pgdb.session.query(
                            pgdb.ResourceGroup.id.label("r_id"),
                            pgdb.ResourceGroup.name.label("r_name"),
                            sqlalchemy.literal(-1).label("n_id"),
                            sqlalchemy.literal("Last Activity").label(
                                "n_friendly"
                            ),
                        )
                        .select_from(pgdb.ResourceGroup)
                        .filter(pgdb.ResourceGroup.is_deleted == False)  # noqa
                    )
                )
            )
        )
        .order_by(
            pgdb.ResourceGroup.id.label("r_id"),
            pgdb.ClaimName.id.label("n_id"),
        )
    )

    extra_data_subquery = (
        pgdb.pgdb.session.query(
            raw_extra_data_subquery.c.id,
            sqlalchemy.func.json_object_agg(
                sqlalchemy.func.concat(
                    raw_extra_data_subquery.c.r_name,
                    ": ",
                    raw_extra_data_subquery.c.n_friendly,
                ),
                aggregate_order_by(
                    raw_extra_data_subquery.c.value,
                    raw_extra_data_subquery.c.r_id,
                    raw_extra_data_subquery.c.n_id,
                ),
            ).label("extra_values"),
        )
        .select_from(raw_extra_data_subquery)
        .group_by(raw_extra_data_subquery.c.id)
        .subquery()
    )

    # accounts columns and claims dicts column
    accounts_query = (
        pgdb.pgdb.session.query(
            pgdb.DNAAccount.given_name,
            pgdb.DNAAccount.family_name,
            pgdb.DNAAccount.email,
            pgdb.DNAAccount.sub,
            sqlalchemy.func.cast(pgdb.DNAAccount.is_vip, sqlalchemy.types.INT),
            pgdb.DNAAccount.status,
            pgdb.PersonnelType.name,
            pgdb.DNAAccount.supervisor_email,
            pgdb.DNAAccount.location,
            pgdb.BusinessUnit.name,
            pgdb.DNAAccount.preferred_username,
            pgdb.DNAAccount.job_title,
            pgdb.DNAAccount.expiration_date,
            extra_data_subquery.c.extra_values,
        )
        .select_from(pgdb.DNAAccount)
        .join(
            extra_data_subquery,
            extra_data_subquery.c.id == pgdb.DNAAccount.id,
            isouter=True,
        )
        .join(pgdb.PersonnelType, isouter=True)
        .join(pgdb.BusinessUnit, isouter=True)
    )
    if search_term:
        accounts_query = accounts_query.filter(
            pgdb.DNAAccount.id.in_(
                pgdb.DNAAccount.query.search(search_term).with_entities(
                    pgdb.DNAAccount.id
                )
            )
        )
    if status:
        accounts_query = accounts_query.filter(
            pgdb.DNAAccount.status == status
        )
    if resource_group_id:
        accounts_query = accounts_query.filter(
            pgdb.DNAAccount.id.in_(
                pgdb.DNAAccount.query.by_resource_group_id(
                    resource_group_id
                ).with_entities(pgdb.DNAAccount.id)
            )
        )
    if tag_id:
        accounts_query = accounts_query.filter(
            pgdb.DNAAccount.id.in_(
                pgdb.DNAAccount.query.by_tag_id(tag_id).with_entities(
                    pgdb.DNAAccount.id
                )
            )
        )
    if expire_soon:
        accounts_query = accounts_query.filter(
            pgdb.DNAAccount.expiration_date != None,  # noqa
            pgdb.DNAAccount.expiration_date
            <= datetime.now().date()
            + timedelta(days=Settings.ACCOUNTS_EXPIRATION_NOTIFY_DAYS),
            pgdb.DNAAccount.expiration_date > datetime.now().date(),
        )

    if affected:
        affected_query, _ = pgdb.DNAAccount.query.affected_by(*affected)
        accounts_query = accounts_query.filter(
            pgdb.DNAAccount.id.in_(
                affected_query.with_entities(pgdb.DNAAccount.id)
            )
        )

    if token_size_violation:
        violation_query = pgdb.DNAAccount.query.token_size_violation()
        accounts_query = accounts_query.filter(
            pgdb.DNAAccount.id.in_(
                violation_query.with_entities(pgdb.DNAAccount.id)
            )
        )

    if order:
        accounts_query = QueryOrdering(
            accounts_query,
            [
                pgdb.DNAAccount.id,
                pgdb.DNAAccount.given_name,
                pgdb.DNAAccount.family_name,
                pgdb.DNAAccount.email,
            ],
            order,
        ).query

    rows = accounts_query.all()

    headers = [
        "First Name",
        "Last Name",
        "Email",
        "ID",
        "VIP",
        "Status",
        "Personnel Type",
        "Supervisor Email",
        "Location",
        "Business Unit",
        "Employee Corporate Username",
        "Job Title",
        "Expiration Date",
    ]

    if len(rows) > 0:
        # extending headers for dynamic claims columns
        headers.extend(k for k in rows[0].extra_values.keys())
    else:
        headers.extend(
            [
                f"{item.r_name}: {item.n_friendly}"
                for item in extra_column_names_query.all()
            ]
        )

    yield headers

    for row in rows:
        result_row = list(row)
        # extending columns with dynamic claims columns
        extra = result_row.pop()
        result_row.extend(extra.values())
        yield result_row