from sqlalchemy import Boolean, Column, ForeignKey, Index, Integer, PrimaryKeyConstraint, String, UniqueConstraint, or_
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy_json import mutable_json_type
from typing import List, Optional

from server.constants.core import DSP
from server.db.models.base import Base
from server.db.models.core import Account, AccountRelatedMixin, AppRelatedMixin


class Device(AccountRelatedMixin, Base):
    __tablename__ = "devices"
    __table_args__ = (
        Index("ix_active_account", "status", "account_id", unique=False),
        UniqueConstraint("account_id", "token", name="account_token"),
    )

    id = Column(Integer, primary_key=True, autoincrement=True)
    inner_id = Column(String(255), nullable=False)
    os = Column(String(20), nullable=False)
    token = Column(String(255), nullable=False)
    status = Column(Boolean, nullable=False, default=True)  # 1=active
    app_version = Column(String(30), default=None)


class Favorites(AccountRelatedMixin, Base):
    __tablename__ = "favorites"
    __table_args__ = (Index("ix_favorites_account_id_entity_id_entity_type", "account_id", "entity_id", "entity_type"),)

    id = Column(Integer, primary_key=True, autoincrement=True)
    tag_id = Column(Integer, nullable=True, default=None)
    entity_id = Column(String(50), nullable=False)
    entity_type = Column(String(50), nullable=False)
    data = Column(mutable_json_type(dbtype=JSONB, nested=True), nullable=True)

    @classmethod
    async def list_by_params(
        cls,
        app_slug: str,
        user_list: Optional[List[str]] = None,
        entity_type_list: Optional[List[str]] = None,
        dsp_list: Optional[List[DSP]] = None,
        limit: Optional[int] = None,
        offset: Optional[int] = None,
    ):
        q = cls.q_select_with_account()
        q = q.where(Account.app_slug == app_slug)
        if user_list:
            q = q.where(Account.user_id.in_(user_list))
        if entity_type_list:
            q = q.where(Favorites.entity_type.in_(entity_type_list))

        dsp_filters = []
        for dsp in dsp_list or tuple():
            dsp_filters.append(Favorites.entity_id.startswith(dsp))
        if dsp_filters:
            q = q.where(or_(*dsp_filters))
        return await Favorites.list(query=q, limit=limit, offset=offset)


class Settings(AccountRelatedMixin, Base):
    """Account settings model.

    Attributes:
        type: Application subtype.
        version: Version.
        data: Settings data as json.

    """

    __tablename__ = "setting"
    __table_args__ = (Index("ix_setting_account_type_version", "account_id", "type", "version"),)

    id = Column(Integer, primary_key=True, autoincrement=True)
    type = Column(String(30), nullable=True)
    version = Column(Integer, nullable=False)
    data = Column(mutable_json_type(dbtype=JSONB, nested=True), nullable=False)

    @classmethod
    async def list(
        cls,
        app_slug: str = None,
        user_list: List[str] = None,
        type_list: List[str] = None,
        version_list: List[int] = None,
        **params,
    ):
        q = cls.q_select_with_account()
        if app_slug:
            q = q.where(Account.app_slug == app_slug)
        if user_list:
            q = q.where(Account.user_id.in_(user_list))
        if type_list:
            q = q.where(Settings.type.in_(type_list))
        if version_list:
            q = q.where(Settings.version.in_(version_list))
        q = q.order_by(Settings.id)
        return await super().list(query=q, **params)

    @classmethod
    async def get_max_version(cls, account_id: int, settings_type: str):
        settings = await Settings.list(
            filters=[Settings.account_id == account_id, Settings.type == settings_type],
            order_by=Settings.version.desc(),
            limit=1,
        )
        if settings:
            return settings[0]
        return None


class SettingsSchema(AppRelatedMixin, Base):
    __tablename__ = "setting_schema"
    __table_args__ = (Index("ix_setting_schema_app_slug_type_version", "app_slug", "type", "version"),)

    id = Column(Integer, primary_key=True, autoincrement=True)
    type = Column(String(30), nullable=False)
    version = Column(Integer, nullable=False)
    data = Column(mutable_json_type(dbtype=JSONB, nested=True), nullable=False)


class Personalization(AccountRelatedMixin, Base):
    __tablename__ = "personalization"

    id = Column(Integer, primary_key=True, autoincrement=True)
    subtype_1 = Column(String(100), nullable=True)
    subtype_2 = Column(String(100), nullable=True)
    external_id = Column(String(80), nullable=True)
    type = Column(String(50), nullable=False)
    data = Column(mutable_json_type(dbtype=JSONB, nested=True), nullable=False)