from collections.abc import Sequence

import sqlalchemy as sa
from sqlalchemy.orm import joinedload

from dmp.adapters.db import ReportingDB, ReportingRepository
from dmp.app_connections.enums import AppConnectionStatus
from dmp.config import Settings
from dmp.shopify.dtos import ShopifyStore
from dmp.shopify.models import ShopifyStoreAssociation
from dmp.shopify.types import ShopifyStoreOrderBy
from dmp.shopify.validators import ShopifyStoreListValidator


class StoreAssociationRepository(ReportingRepository[ShopifyStoreAssociation]):
    default_options = [joinedload(ShopifyStoreAssociation.shop)]

    def __init__(self, db: ReportingDB, settings: Settings):
        super().__init__(db)
        self.settings = settings

    def get_by_fivetran_connection_id(
        self, *, fivetran_connection_id: str
    ) -> ShopifyStoreAssociation | None:
        query = (
            sa.select(ShopifyStoreAssociation)
            .options(joinedload(ShopifyStoreAssociation.shop))
            .where(
                ShopifyStoreAssociation.fivetran_connector_id == fivetran_connection_id
            )
        )
        result = self.db.session.execute(query)
        return result.scalar_one_or_none()

    def get_count_by_account_id(self, *, vendor_id: int, subaccount_id: int) -> int:
        query = sa.select(sa.func.count()).select_from(
            sa.select(ShopifyStoreAssociation)
            .where(
                ShopifyStoreAssociation.vendor_id == vendor_id,
                ShopifyStoreAssociation.subaccount_id == subaccount_id,
            )
            .subquery()
        )
        return self.db.session.execute(query).scalar_one()

    def find_by_account_ids(
        self,
        *,
        vendor_ids: list[int],
        subaccount_ids: list[int],
    ) -> list[ShopifyStore]:
        if not vendor_ids and not subaccount_ids:
            return []
        query = self.db.query_from_template(
            "get-stores.sql",
            context={
                "vendor_ids": vendor_ids,
                "subaccount_ids": subaccount_ids,
                "shopify_schema": self.settings.snowflake_shopify_schema,
            },
        )
        return ShopifyStoreListValidator.validate_python(
            self.db.session.execute(query).mappings(),
        )

    def count_by_account_ids(
        self,
        *,
        vendor_ids: list[int],
        subaccount_ids: list[int],
    ) -> int:
        if not vendor_ids and not subaccount_ids:
            return 0
        query = self.db.query_from_template(
            "count-stores.sql",
            context={
                "vendor_ids": vendor_ids,
                "subaccount_ids": subaccount_ids,
            },
        )
        return int(self.db.session.execute(query).scalar_one())

    def find_paginated(
        self,
        *,
        vendor_ids: list[int],
        subaccount_ids: list[int],
        limit: int,
        offset: int,
        order_by: list[ShopifyStoreOrderBy],
    ) -> list[ShopifyStore]:
        if not vendor_ids and not subaccount_ids:
            return []
        query = self.db.query_from_template(
            "get-stores.sql",
            context={
                "vendor_ids": vendor_ids,
                "subaccount_ids": subaccount_ids,
                "shopify_schema": self.settings.snowflake_shopify_schema,
                "limit": limit,
                "offset": offset,
                "order_by": order_by,
            },
        )
        return ShopifyStoreListValidator.validate_python(
            self.db.session.execute(query).mappings(),
        )

    def find_to_notify_on_initial_sync_completion(
        self,
    ) -> Sequence[ShopifyStoreAssociation]:
        query = (
            sa.select(ShopifyStoreAssociation)
            .options(joinedload(ShopifyStoreAssociation.shop, innerjoin=True))
            .where(
                sa.and_(
                    ShopifyStoreAssociation.status.in_(
                        (
                            AppConnectionStatus.WAITING_FOR_PROCESSING,
                            AppConnectionStatus.CONNECTED,
                        )
                    ),
                    ShopifyStoreAssociation.initial_email_sent == sa.false(),
                )
            )
        )
        return self.db.session.execute(query).scalars().all()

    def get_fivetran_connections_ids(self) -> list[str]:
        query = sa.select(ShopifyStoreAssociation.fivetran_connector_id).where(
            ShopifyStoreAssociation.status.in_(
                AppConnectionStatus.schema_update_statuses()
            )
        )

        return list(self.db.session.execute(query).scalars().all())