"""Subaccount Model.

This subaccount model uses sqlalchemy. It provides core functionality
for getting subaccount information by id, vendor_id, and a combo of both.
"""

import datetime
from typing import Tuple

from ddtrace import tracer
from flask import g
from owsresponse import response
from sqlalchemy import (
    Column,
    DateTime,
    Enum,
    Float,
    ForeignKey,
    Integer,
    String,
    Text,
    func,
)
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session, relationship

from account.connectors import mysql
from account.connectors.sentry import sentry_client
from account.constants import constants, error
from account.models import CompanyBrand
from account.models.parent_company import ParentCompany
from account.models.sql.subaccount_document import (
    SUBACCOUNT_DOCUMENT_SQL,
    SUBACCOUNT_DOCUMENT_WITH_TENANT_UUIDS_SQL,
)
from account.models.sql.subaccount_with_contacts import (
    SUBACCOUNT_WITH_CONTACTS_SQL,  # noqa
)
from account.models.types import (
    CompanyBrand as CompanyBrandType,
    DeletedSubaccount,
    Subaccount as SubaccountType,
    Vendor as VendorType,
)
from account.models.vendor import Vendor


class Subaccount(mysql.BaseModel):
    """Subaccount DB Model."""

    __tablename__ = 'subaccount'

    subaccount_id = Column(Integer, primary_key=True)
    vendor_id = Column(Integer, ForeignKey('vendor.vendor_id'), default=None)
    subaccount_uuid = Column(String, default=None)
    subaccount_name = Column(String)
    description = Column(Text)
    date_deleted = Column(DateTime)
    country_id = Column(Integer, default=None)
    commission_override = Column(Float, default=1)
    subaccount_split_type = Column(Enum(*['Gross', 'Net']), default='Net')
    date_created = Column(DateTime, default=datetime.datetime.now)

    vendor = relationship('Vendor', primaryjoin='Subaccount.vendor_id == Vendor.vendor_id')

    def to_dict(self):
        """Get a dict representation of Vendor."""
        return {
            'subaccount_id': self.subaccount_id,
            'vendor_id': self.vendor_id,
            'subaccount_uuid': self.subaccount_uuid,
            'subaccount_name': self.subaccount_name,
            'description': self.description,
            'country_id': self.country_id,
            'commission_override': self.commission_override,
            'subaccount_split_type': self.subaccount_split_type,
            'date_created': self.date_created,
        }


DEFAULT_FIELDS = (
    Subaccount.subaccount_id,
    Subaccount.vendor_id,
    Subaccount.subaccount_uuid,
    Subaccount.subaccount_name,
    Subaccount.description,
    Subaccount.country_id,
    Subaccount.commission_override,
    Subaccount.subaccount_split_type,
)

CONDITION_ACTIVE_SUBACCOUNT = Subaccount.date_deleted == None  # noqa
CONDITION_DEACTIVE_SUBACCOUNT = Subaccount.date_deleted != None  # noqa
DISABLED_SUBACCOUNTS = 'deactivated'


def get_subaccounts(vendor_id, status, page_offset, page_limit):
    """Get list of subaccounts for vendor with oldest active contact ids.

    Args:
        vendor_id (int): unique identifier for the vendor.
        status (string): Subaccount status.
        page_offset (int): record index used to start.
        page_limit (int): number of records to fetch.

    Returns:
        response.Response: containing a list of subaccount dicts.
    """
    subaccount_status_condition = 'NULL'

    if status == DISABLED_SUBACCOUNTS:
        subaccount_status_condition = 'NOT NULL'

    limit_offset = f'LIMIT {page_limit} OFFSET {page_offset}' if page_limit != 0 else ''

    with mysql.session_scope(read_only=True) as session:
        result = session.execute(
            SUBACCOUNT_WITH_CONTACTS_SQL.format(
                vendor_id=vendor_id,
                limit_offset=limit_offset,
                date_deleted=subaccount_status_condition,
            )
        ).fetchall()

        if len(result):
            message = {
                'items': [dict(row) for row in result],
                'pagination': dict(type='standard', page_offset=page_offset, page_limit=page_limit),
            }
            return response.Response(message)
    return response.create_not_found_response()


def get_subaccount_count(vendor_id, status=None):
    """Get number of subaccounts for vendor.

    Args:
        vendor_id (int): unique identifier for the vendor.
        status (string): Subaccount status.

    Returns:
        response.Response: contains number of subaccounts
    """
    subaccount_status_condition = CONDITION_ACTIVE_SUBACCOUNT
    if status == DISABLED_SUBACCOUNTS:
        subaccount_status_condition = CONDITION_DEACTIVE_SUBACCOUNT
    with mysql.session_scope(read_only=True) as session:
        count = (
            session.query(func.count(Subaccount.subaccount_id))
            .filter(Subaccount.vendor_id == vendor_id, subaccount_status_condition)
            .one_or_none()
        )
        if count:
            message = count[0]
            return response.Response(message)
    return response.create_not_found_response()


def get_subaccount(subaccount_id):
    """Get information about a subaccount.

    Gets subaccount information for an active subaccount.

    Args:
        subaccount_id (int): unique identifier of the subaccount.

    Returns:
        response.Response: containing dict of subaccount or error.
    """
    with mysql.session_scope(read_only=True) as session:
        row = (
            session.query(*DEFAULT_FIELDS)
            .filter(Subaccount.subaccount_id == subaccount_id, CONDITION_ACTIVE_SUBACCOUNT)
            .one_or_none()
        )
        if row:
            return response.Response(row._asdict())
    return response.create_not_found_response()


def get_subaccount_for_vendor(subaccount_id, vendor_id):
    """Get information about a subaccount for a vendor_id.

    Gets subaccount information for an active subaccount.

    Args:
        subaccount_id (int): unique identifer of the subaccount.
        vendor_id (int): unique identifier of the vendor.

    Returns:
        response.Response: containing dict of subaccount or error.
    """
    with mysql.session_scope(read_only=True) as session:
        row = (
            session.query(*DEFAULT_FIELDS)
            .filter(
                Subaccount.subaccount_id == subaccount_id,
                Subaccount.vendor_id == vendor_id,
                CONDITION_ACTIVE_SUBACCOUNT,
            )
            .one_or_none()
        )
        if row:
            return response.Response(row._asdict())
    return response.create_not_found_response()


def get_subaccount_document_by_id(subaccount_id, with_tenant_uuids=False):
    """Get the document as defined in cloudsearch corpus by subaccount id."""
    try:
        query = (
            SUBACCOUNT_DOCUMENT_WITH_TENANT_UUIDS_SQL
            if with_tenant_uuids
            else SUBACCOUNT_DOCUMENT_SQL
        )

        with mysql.session_scope(read_only=True) as session:
            params = {'subaccount_id': subaccount_id}
            subaccount = session.execute(query, params).fetchone()
            if not subaccount:
                return response.create_not_found_response(
                    message='Subaccount : {} not found.'.format(subaccount_id)
                )
            return response.Response(dict(subaccount))
    except SQLAlchemyError:
        sentry_client.capture_exception()
        return response.create_fatal_response('Could not connect to Mysql Database')


def get_subaccount_by_vendor_id(subaccount_id, vendor_id):
    """Get information about subaccount by the given vendor_id and subaccount_id.

    Gets subaccount information.

    Args:
        subaccount_id (int): unique identifer of the subaccount.
        vendor_id (int): unique identifier of the vendor.

    Returns:
        response.Response: containing dict of subaccount (includes disabled
        subaccount) or returns error.
    """
    with mysql.session_scope(read_only=True) as session:
        row = (
            session.query(*DEFAULT_FIELDS)
            .filter(
                Subaccount.subaccount_id == subaccount_id,
                Subaccount.vendor_id == vendor_id,
            )
            .one_or_none()
        )
        if row:
            return response.Response(row._asdict())
    return response.create_not_found_response()


def update_subaccount_status(subaccount_id, is_active):
    """Update given subaccount status for given subaccount_id.

    Args:
        subaccount_id (int): unique identifier of the subaccount.
        is_active (bool): status of subaccount to be updated.

    Returns:
        response.Response: Returns status of subaccount on success
        or returns error.
    """
    try:
        with mysql.session_scope() as session:
            existing_subaccount = session.query(Subaccount).get(subaccount_id)
            if not existing_subaccount:
                return response.create_not_found_response(
                    message=error.ERROR_MESSAGE_SUBACCOUNT_NOT_FOUND
                )

            existing_subaccount.date_deleted = None
            if not is_active:
                existing_subaccount.date_deleted = datetime.datetime.now()
            session.commit()
            return response.Response(status=200, message={'active': is_active})
    except SQLAlchemyError as e:
        if sentry_client:
            sentry_client.capture_exception()
        g.log.exception(e.args)
        return response.create_fatal_response(e.args)


@tracer.wrap()
def delete_subaccount_by_uuid(
    subaccount_uuid: str,
) -> DeletedSubaccount:
    """Soft-delete a subaccount by setting its date_deleted to now.

    Args:
        subaccount_uuid (str): UUID of the subaccount to delete.

    Returns:
        dict: subaccount_uuid and date_deleted on success.

    Raises:
        SQLAlchemyError: on database error.
    """
    try:
        with mysql.session_scope() as session:
            existing_subaccount = (
                session.query(Subaccount)
                .filter(Subaccount.subaccount_uuid == subaccount_uuid)
                .first()
            )
            date_deleted = None
            if existing_subaccount and not existing_subaccount.date_deleted:
                date_deleted = datetime.datetime.now()
                existing_subaccount.date_deleted = date_deleted
                session.commit()
            return DeletedSubaccount(subaccount_uuid=subaccount_uuid, date_deleted=date_deleted)
    except SQLAlchemyError as e:
        if sentry_client:
            sentry_client.capture_exception()
        g.log.exception(e.args)
        raise


def create_subaccount(details):
    """Create a new subaccount.

    Args:
        details (dict): details for subaccount.

    Returns:
        response.Response: containing dict of subaccount.
    """
    try:
        with mysql.session_scope() as session:
            subaccount = Subaccount(**details)
            session.add(subaccount)
            session.commit()
            return response.Response(subaccount.to_dict())
    except SQLAlchemyError as e:
        if sentry_client:
            sentry_client.capture_exception()
        return response.create_fatal_response(e.args)


def lookup_subaccounts_by_uuids(
    subaccount_uuids: list[str],
    fetch_flags: list[str],
) -> response.Response:
    """
    Lookup subaccounts by uuids

    Args:
        subaccount_uuids: list of subaccount uuids
        fetch_flags: list of additional identifier attributes to fetch

    Return:
        response.Response.message: list(obj).
        obj shape:
            {
                subaccount_id: number
                vendor_id: number
                uuid: str
                vendor_uuid: str  (when fetch flags are requested)
                company_brand_uuid: str  (when fetch flags are requested)
            }
    """
    if not subaccount_uuids:
        return response.Response([])

    select_entities = [
        Subaccount.subaccount_id,
        Subaccount.vendor_id,
        Subaccount.subaccount_uuid.label('uuid'),
    ]
    join_entities = []

    if constants.FETCH_TENANT_HIERARCHY in fetch_flags:
        # Add Vendor uuid
        select_entities.append(Vendor.vendor_uuid)
        join_entities.append(Subaccount.vendor)

        # Add CompanyBrand uuid
        select_entities.append(CompanyBrand.uuid.label('company_brand_uuid'))
        join_entities.append(Vendor.company_brand)

        # Add ParentCompany uuid
        select_entities.append(ParentCompany.uuid.label('parent_company_uuid'))
        join_entities.append(CompanyBrand.parent_company)

    with mysql.session_scope(read_only=True) as session:
        query = session.query(*select_entities)
        if join_entities:
            query = query.join(*join_entities)
        result = query.filter(Subaccount.subaccount_uuid.in_(subaccount_uuids))
        if not result:
            return response.create_not_found_response(message='subaccount_uuid not found.')
        return response.Response([row._asdict() for row in result])


def lookup_subaccounts_by_subaccount_ids(
    subaccount_ids: list[str],
    fetch_flags: list[str],
) -> response.Response:
    """
    Lookup subaccounts by subaccount ids

    Args:
        subaccount_ids: list of subaccount ids
        fetch_flags: list of additional identifier attributes to fetch

    Return:
        response.Response.message: list(obj).
        obj shape:
            {
                subaccount_id: number
                vendor_id: number
                uuid: str
                vendor_uuid: str  (when fetch flags are requested)
                company_brand_uuid: str  (when fetch flags are requested)
            }
    """
    if not subaccount_ids:
        return response.Response([])

    select_entities = [
        Subaccount.subaccount_id,
        Subaccount.vendor_id,
        Subaccount.subaccount_uuid.label('uuid'),
    ]
    join_entities = []

    if constants.FETCH_TENANT_HIERARCHY in fetch_flags:
        # Add Vendor uuid
        select_entities.append(Vendor.vendor_uuid)
        join_entities.append(Subaccount.vendor)

        # Add CompanyBrand uuid
        select_entities.append(CompanyBrand.uuid.label('company_brand_uuid'))
        join_entities.append(Vendor.company_brand)

        # Add ParentCompany uuid
        select_entities.append(ParentCompany.uuid.label('parent_company_uuid'))
        join_entities.append(CompanyBrand.parent_company)

    with mysql.session_scope(read_only=True) as session:
        query = session.query(*select_entities)
        if join_entities:
            query = query.join(*join_entities)
        result = query.filter(Subaccount.subaccount_id.in_(subaccount_ids))
        if not result:
            return response.create_not_found_response(message='subaccount_id not found.')
        return response.Response([row._asdict() for row in result])


def lookup_subaccount_and_vendor_and_company_brand_by_uuid(
    uuid: str, session: Session
) -> Tuple[SubaccountType, VendorType, CompanyBrandType] | None:
    """Look up subaccount, vendor, and brand by subaccount uuid.

    Args:
        uuid: subaccount uuid
        session: sqlalchemy session

    Return:
        Tuple[SubaccountType, VendorType, CompanyBrandType]: subaccount, vendor, and company brand.
    """

    select_entities = [
        Subaccount.subaccount_id,
        Subaccount.subaccount_uuid.label('uuid'),
        Vendor.vendor_id.label('vendor_id'),
        Vendor.vendor_uuid.label('vendor_uuid'),
        Vendor.migrated_to_abacus,
        CompanyBrand.id.label('company_brand_id'),
        CompanyBrand.name.label('company_brand_name'),
    ]
    join_entities = [Subaccount.vendor, Vendor.company_brand]
    query = (
        session.query(*select_entities)
        .join(*join_entities)
        .filter(Subaccount.subaccount_uuid == uuid)
    )

    result = query.first()

    if not result:
        return None

    result_dict = result._asdict()

    return (
        SubaccountType(
            subaccount_id=result_dict['subaccount_id'],
            uuid=result_dict['uuid'],
        ),
        VendorType(
            vendor_id=result_dict['vendor_id'],
            uuid=result_dict['vendor_uuid'],
            migrated_to_abacus=result_dict['migrated_to_abacus'],
        ),
        CompanyBrandType(
            id=result_dict['company_brand_id'],
            name=result_dict['company_brand_name'],
        ),
    )


def lookup_subaccounts_and_vendors_and_company_brands_by_uuids(
    subaccount_uuids: list[str], session: Session
) -> dict[str, Tuple[SubaccountType, VendorType, CompanyBrandType]]:
    """Bulk Look up subaccounts, vendors, and brands by subaccount uuids.

    Args:
        subaccount_uuids: list of subaccount UUIDs
        session: sqlalchemy session

    Return:
        dict[str, Tuple[SubaccountType, VendorType, CompanyBrandType]]:
        mapping of UUIDs to subaccount, vendor and company brand tuples.
    """

    select_entities = [
        Subaccount.subaccount_id,
        Subaccount.subaccount_uuid.label('uuid'),
        Vendor.vendor_id.label('vendor_id'),
        Vendor.vendor_uuid.label('vendor_uuid'),
        Vendor.migrated_to_abacus,
        CompanyBrand.id.label('company_brand_id'),
        CompanyBrand.name.label('company_brand_name'),
    ]
    join_entities = [Subaccount.vendor, Vendor.company_brand]
    query = (
        session.query(*select_entities)
        .join(*join_entities)
        .filter(Subaccount.subaccount_uuid.in_(subaccount_uuids))
    )

    results = query.all()

    result = {
        row.uuid: (
            SubaccountType(
                subaccount_id=row.subaccount_id,
                uuid=row.uuid,
            ),
            VendorType(
                vendor_id=row.vendor_id,
                uuid=row.vendor_uuid,
                migrated_to_abacus=row.migrated_to_abacus,
            ),
            CompanyBrandType(
                id=row.company_brand_id,
                name=row.company_brand_name,
            ),
        )
        for row in results
    }

    return result


def get_subaccount_names(subaccount_uuids: list[str]) -> list[dict]:
    """Get subaccount names for given subaccount_uuids. may raise exception.

    Args:
        subaccount_uuids (list[str]): unique identifiers for each subaccount.

    Returns:
        list[dict]: list of dicts of subaccounts.
    """
    try:
        with mysql.session_scope(read_only=True) as session:
            select_entities = [
                Subaccount.subaccount_id,
                Subaccount.subaccount_uuid,
                Subaccount.subaccount_name,
            ]

            subaccounts = (
                session.query(*select_entities)
                .filter(Subaccount.subaccount_uuid.in_(subaccount_uuids))
                .all()
            )
            subaccounts_data = [s._asdict() for s in subaccounts]
            return subaccounts_data
    except SQLAlchemyError as e:
        g.log.exception(f'error fetching subaccount names {e}')
        raise e