from sqlalchemy import func, or_
from sqlalchemy.dialects.postgresql import ARRAY, INTEGER
from sqlalchemy.future import select
from sqlalchemy.orm import Query, aliased, contains_eager
from typing import Any, Dict, List

from server.constants.settings import Include as SettingsInclude
from server.db.models.core import Account
from server.db.models.users import Device, Settings


def q_max_version(settings: dict = None) -> Query:
    """Max settings version query.

    Args:
        settings: Settings filters.

    Returns:
        Query.

    """
    settings_type = (settings or {}).get("type")
    aliased_settings = aliased(Settings)
    q = (
        select(func.max(aliased_settings.version))
        .where(aliased_settings.account_id == Account.id)
        .where(aliased_settings.type == settings_type)
    )
    if settings and settings["version"]:
        q = q.where(aliased_settings.version == settings["version"])
    return q


def q_app_versions(q: Query, app_version: Dict[str, Any]) -> Query:
    """Extend query with additional filters by device app_versions

    Args:
        q: Query to extend
        app_version: {
          min: str None,
          max: str None,
          eq: str None
          include_unset: false
        }

    Returns:
       Query
    """

    filters = {
        "min": (
            func.cast(func.string_to_array(Device.app_version, "."), ARRAY(INTEGER))
            >= func.cast(func.string_to_array(app_version["min"], "."), ARRAY(INTEGER))
        ),
        "max": (
            func.cast(func.string_to_array(Device.app_version, "."), ARRAY(INTEGER))
            <= func.cast(func.string_to_array(app_version["max"], "."), ARRAY(INTEGER))
        ),
        "eq": (Device.app_version == app_version["eq"]),
        "include_unset": Device.app_version.is_(None),
    }

    queries = []

    for key, app_version_filter in filters.items():
        if app_version.get(key):
            queries.append(app_version_filter)

    if queries:
        q = q.where(or_(*queries))

    return q


def q_settings_versions(q: Query, versions: Dict[str, Any]) -> Query:
    """Extend query with additional filters by device app_versions

    Args:
        q: Query to extend
        versions: {
          min: int None,
          max: int None,
          eq: int None
    }
    """
    aliased_settings = aliased(Settings)

    filters = {
        "min": (Settings.version >= (versions["min"] or func.min(aliased_settings.version))),
        "max": (Settings.version <= (versions["max"] or func.max(aliased_settings.version))),
        "eq": (Settings.version == versions["eq"]),
    }

    queries = []

    for key, settings_version_filter in filters.items():
        if versions.get(key):
            queries.append(settings_version_filter)

    if queries:
        q = q.where(or_(*queries))

    return q


async def get_accounts_with_include(
    app_slug: str = None,
    device: dict = None,
    user: dict = None,
    account: dict = None,
    settings: dict = None,
    include: List[str] = None,
) -> List[Account]:
    """Get filtered accounts with settings and devices.

    Args:
        app_slug: App code.
        device: Device filters.
        user: User filters.
        account: Account filters.
        settings: Settings filters.
        include: Include devices or not.

    Returns:
        List of accounts.

    """
    q = (
        select(Account)
        .outerjoin(Account.settings)
        .options(contains_eager(Account.settings))
        .where(Account.is_active.is_(True))
        .where(Settings.type == (settings or {}).get("type"))
        .where(Settings.version == q_max_version(settings))
    )

    if (include and SettingsInclude.DEVICES.value in include) or device:
        q = q.outerjoin(Account.devices).options(contains_eager(Account.devices))

    if app_slug:
        q = q.where(Account.app_slug == app_slug)
    if user:
        q = q.where(Account.user_id.in_(user["id"]))
    if account:
        q = q.where(Account.id.in_(account["id"]))
    if device:
        if device["is_active"] is not None:
            q = q.where(Device.status == device["is_active"])
        if device["os"]:
            q = q.where(Device.os.in_(device["os"]))
        if device["app_version"]:
            q = q_app_versions(q, device["app_version"])
    if settings and settings["versions"]:
        q = q_settings_versions(q, settings["versions"])
    return await Account.get_bulk_unique(q.order_by(Account.id))