from __future__ import annotations

from typing import Any

from ..base import BasePostgresClient
from .queries import (
    INSERT_MESSAGE,
    UPSERT_CONTACT,
    UPSERT_CONVERSATION,
    UPSERT_TELEGRAM_TOPIC_MAP,
    UPSERT_ZALO_ACCOUNT,
)


class CRMMixin(BasePostgresClient):
    """CRM integration database queries (Zalo/Telegram)."""

    def upsert_zalo_account(self, data: dict[str, Any]) -> dict[str, Any] | None:
        """Upsert a ZaloAccount record."""
        if not self.is_enabled():
            return None
        with self.get_connection() as conn, conn.cursor() as cursor:
            cursor.execute(UPSERT_ZALO_ACCOUNT, data)
            conn.commit()
            return dict(cursor.fetchone() or {}) if cursor.rowcount else None

    def upsert_contact(self, data: dict[str, Any]) -> dict[str, Any] | None:
        """Upsert a Contact record."""
        if not self.is_enabled():
            return None
        with self.get_connection() as conn, conn.cursor() as cursor:
            cursor.execute(UPSERT_CONTACT, data)
            conn.commit()
            return dict(cursor.fetchone() or {}) if cursor.rowcount else None

    def upsert_conversation(self, data: dict[str, Any]) -> dict[str, Any] | None:
        """Upsert a Conversation record."""
        if not self.is_enabled():
            return None
        with self.get_connection() as conn, conn.cursor() as cursor:
            cursor.execute(UPSERT_CONVERSATION, data)
            conn.commit()
            return dict(cursor.fetchone() or {}) if cursor.rowcount else None

    def insert_message(self, data: dict[str, Any]) -> dict[str, Any] | None:
        """Insert a Message record."""
        if not self.is_enabled():
            return None
        with self.get_connection() as conn, conn.cursor() as cursor:
            cursor.execute(INSERT_MESSAGE, data)
            conn.commit()
            return dict(cursor.fetchone() or {}) if cursor.rowcount else None

    def upsert_telegram_topic_map(self, data: dict[str, Any]) -> dict[str, Any] | None:
        """Upsert a TelegramTopicMap record."""
        if not self.is_enabled():
            return None
        with self.get_connection() as conn, conn.cursor() as cursor:
            cursor.execute(UPSERT_TELEGRAM_TOPIC_MAP, data)
            conn.commit()
            return dict(cursor.fetchone() or {}) if cursor.rowcount else None

    def get_external_contacts(self) -> list[dict[str, Any]]:
        """Get all external contacts."""
        if not self.is_enabled():
            return []
        from .queries import GET_EXTERNAL_CONTACTS

        with self.get_connection() as conn, conn.cursor() as cursor:
            cursor.execute(GET_EXTERNAL_CONTACTS)
            return [dict(row) for row in cursor.fetchall()]

    def get_all_contacts(self) -> list[dict[str, Any]]:
        """Get all contacts (internal and external)."""
        if not self.is_enabled():
            return []
        from .queries import GET_ALL_CONTACTS

        with self.get_connection() as conn, conn.cursor() as cursor:
            cursor.execute(GET_ALL_CONTACTS)
            return [dict(row) for row in cursor.fetchall()]

    def get_messages_by_conversation(
        self, conversation_id: str
    ) -> list[dict[str, Any]]:
        """Get all messages in a conversation."""
        if not self.is_enabled():
            return []
        from .queries import GET_MESSAGES_BY_CONVERSATION

        with self.get_connection() as conn:
            with conn.cursor() as cursor:
                cursor.execute(
                    GET_MESSAGES_BY_CONVERSATION, {"conversation_id": conversation_id}
                )
                return [dict(row) for row in cursor.fetchall()]

    def get_telegram_topic_by_conversation(
        self, conversation_id: str
    ) -> dict[str, Any] | None:
        """Get topic map by conversation id."""
        if not self.is_enabled():
            return None
        from .queries import GET_TELEGRAM_TOPIC_BY_CONVERSATION

        with self.get_connection() as conn:
            with conn.cursor() as cursor:
                cursor.execute(
                    GET_TELEGRAM_TOPIC_BY_CONVERSATION,
                    {"conversation_id": conversation_id},
                )
                row = cursor.fetchone()
                return dict(row) if row else None

    def get_telegram_topic_by_id(self, telegram_topic_id: str) -> dict[str, Any] | None:
        """Get topic map by telegram topic id."""
        if not self.is_enabled():
            return None
        from .queries import GET_TELEGRAM_TOPIC_BY_ID

        with self.get_connection() as conn:
            with conn.cursor() as cursor:
                cursor.execute(
                    GET_TELEGRAM_TOPIC_BY_ID, {"telegram_topic_id": telegram_topic_id}
                )
                row = cursor.fetchone()
                return dict(row) if row else None