"""
Supabase Database Client for Hotel MCP Server.

This module provides a robust, type-safe interface to the Supabase database
with connection pooling, error handling, and query optimization.
"""

import logging
import os
from dataclasses import dataclass
from typing import Any, Dict, List, Optional

from postgrest.exceptions import APIError
from supabase import Client, create_client

logger = logging.getLogger(__name__)


@dataclass
class DatabaseConfig:
    """Configuration for database connection."""

    url: str
    anon_key: str
    timeout: int = 30
    max_retries: int = 3


class DatabaseError(Exception):
    """Custom exception for database operations."""

    def __init__(self, message: str, original_error: Optional[Exception] = None):
        super().__init__(message)
        self.original_error = original_error


class SupabaseClient:
    """
    Supabase client with connection management and error handling.

    Provides a high-level interface for database operations with:
    - Automatic connection management
    - Query result caching
    - Error handling and retries
    - Type-safe query methods
    """

    def __init__(self, config: Optional[DatabaseConfig] = None):
        """Initialize the Supabase client."""
        if config is None:
            config = DatabaseConfig(
                url=os.getenv("SUPABASE_URL", ""),
                anon_key=os.getenv("SUPABASE_ANON_KEY", ""),
            )

        if not config.url or not config.anon_key:
            raise DatabaseError("Supabase URL and anon key are required")

        self.config = config
        self._client: Optional[Client] = None
        self._connection_tested = False

    @property
    def client(self) -> Client:
        """Get or create the Supabase client."""
        if self._client is None:
            try:
                self._client = create_client(self.config.url, self.config.anon_key)
                logger.info("Supabase client initialized successfully")
            except Exception as e:
                raise DatabaseError(f"Failed to initialize Supabase client: {e}", e)
        return self._client

    def test_connection(self) -> bool:
        """Test the database connection."""
        if self._connection_tested:
            return True

        try:
            # Simple query to test connection - use a basic table that should always exist
            result = self.client.table("suites").select("id").limit(1).execute()

            # Verify we got a valid response
            if hasattr(result, "data"):
                self._connection_tested = True
                logger.info("Database connection test successful")
                return True
            else:
                raise DatabaseError("Invalid response from database", None)

        except Exception as e:
            logger.error(f"Database connection test failed: {e}")
            raise DatabaseError(f"Database connection failed: {e}", e)

    def _handle_query_error(self, error: Exception, query_info: str) -> None:
        """Handle and log query errors."""
        logger.error(f"Query failed [{query_info}]: {error}")
        if isinstance(error, APIError):
            raise DatabaseError(f"Database query error: {error.message}", error)
        else:
            raise DatabaseError(f"Unexpected error during query: {error}", error)

    def get_suites(
        self, site_id: Optional[str] = None, limit: int = 50, offset: int = 0
    ) -> List[Dict[str, Any]]:
        """
        Get hotel suites/rooms.

        Args:
            site_id: Filter by site ID
            limit: Maximum number of results
            offset: Number of results to skip

        Returns:
            List of suite records
        """
        try:
            # Use explicit column selection to avoid any potential RLS issues
            query = self.client.table("suites").select(
                "id, site_id, name, slug, description, features, amenities, images, videos, "
                "size, capacity, price_info, status, created_at, updated_at, translations, default_language"
            )

            if site_id:
                query = query.eq("site_id", site_id)

            query = query.limit(limit).offset(offset)
            result = query.execute()

            logger.debug(f"Retrieved {len(result.data)} suites")
            return result.data

        except Exception as e:
            self._handle_query_error(e, f"get_suites(site_id={site_id})")

    def get_suite_by_id(self, suite_id: str) -> Optional[Dict[str, Any]]:
        """Get a specific suite by ID."""
        try:
            result = (
                self.client.table("suites").select("*").eq("id", suite_id).execute()
            )

            if result.data:
                logger.debug(f"Retrieved suite {suite_id}")
                return result.data[0]
            else:
                logger.warning(f"Suite {suite_id} not found")
                return None

        except Exception as e:
            self._handle_query_error(e, f"get_suite_by_id({suite_id})")

    def get_activities(
        self, site_id: Optional[str] = None, limit: int = 50, offset: int = 0
    ) -> List[Dict[str, Any]]:
        """Get hotel activities."""
        try:
            # Use explicit column selection to avoid any potential RLS issues
            query = self.client.table("activities").select(
                "id, site_id, name, slug, description, features, images, videos, "
                "schedule, duration, price_info, location, status, created_at, "
                "updated_at, translations, default_language"
            )

            if site_id:
                query = query.eq("site_id", site_id)

            query = query.limit(limit).offset(offset)
            result = query.execute()

            logger.debug(f"Retrieved {len(result.data)} activities")
            return result.data

        except Exception as e:
            self._handle_query_error(e, f"get_activities(site_id={site_id})")

    def get_activity_by_id(self, activity_id: str) -> Optional[Dict[str, Any]]:
        """Get a specific activity by ID."""
        try:
            result = (
                self.client.table("activities")
                .select("*")
                .eq("id", activity_id)
                .execute()
            )

            if result.data:
                logger.debug(f"Retrieved activity {activity_id}")
                return result.data[0]
            else:
                logger.warning(f"Activity {activity_id} not found")
                return None

        except Exception as e:
            self._handle_query_error(e, f"get_activity_by_id({activity_id})")

    def get_facilities(
        self, site_id: Optional[str] = None, limit: int = 50, offset: int = 0
    ) -> List[Dict[str, Any]]:
        """Get hotel facilities."""
        try:
            # Use explicit column selection to avoid any potential RLS issues
            query = self.client.table("facilities").select(
                "id, site_id, name, slug, description, features, images, videos, "
                "hours, restrictions, status, created_at, updated_at, "
                "translations, default_language"
            )

            if site_id:
                query = query.eq("site_id", site_id)

            query = query.limit(limit).offset(offset)
            result = query.execute()

            logger.debug(f"Retrieved {len(result.data)} facilities")
            return result.data

        except Exception as e:
            self._handle_query_error(e, f"get_facilities(site_id={site_id})")

    def get_facility_by_id(self, facility_id: str) -> Optional[Dict[str, Any]]:
        """Get a specific facility by ID."""
        try:
            result = (
                self.client.table("facilities")
                .select("*")
                .eq("id", facility_id)
                .execute()
            )

            if result.data:
                logger.debug(f"Retrieved facility with ID: {facility_id}")
                return result.data[0]
            else:
                logger.warning(f"No facility found with ID: {facility_id}")
                return None

        except Exception as e:
            self._handle_query_error(e, f"get_facility_by_id({facility_id})")

    def get_menu_items(
        self,
        site_id: Optional[str] = None,
        dietary_restrictions: Optional[List[str]] = None,
        limit: int = 100,
        offset: int = 0,
    ) -> List[Dict[str, Any]]:
        """Get menu items with optional dietary filtering."""
        try:
            # Use explicit column selection to avoid any potential RLS issues
            query = self.client.table("menu_items").select(
                "id, site_id, name, slug, description, category, price_info, "
                "images, dietary_restrictions, allergen_info, availability_status, "
                "status, sort_order, created_at, updated_at, translations, default_language"
            )

            if site_id:
                query = query.eq("site_id", site_id)

            # Implement dietary restrictions filtering using the actual column
            if dietary_restrictions:
                logger.info(
                    f"Dietary restrictions filtering requested: {dietary_restrictions}"
                )
                # Filter by dietary_restrictions JSONB array
                for restriction in dietary_restrictions:
                    query = query.contains("dietary_restrictions", [restriction])

            query = query.limit(limit).offset(offset)
            result = query.execute()

            logger.debug(f"Retrieved {len(result.data)} menu items")
            return result.data

        except Exception as e:
            self._handle_query_error(e, f"get_menu_items(site_id={site_id})")

    def get_menu_item_by_id(self, menu_item_id: str) -> Optional[Dict[str, Any]]:
        """Get a specific menu item by ID."""
        try:
            result = (
                self.client.table("menu_items")
                .select("*")
                .eq("id", menu_item_id)
                .execute()
            )

            if result.data:
                logger.debug(f"Retrieved menu item with ID: {menu_item_id}")
                return result.data[0]
            else:
                logger.warning(f"No menu item found with ID: {menu_item_id}")
                return None

        except Exception as e:
            self._handle_query_error(e, f"get_menu_item_by_id({menu_item_id})")

    def get_galleries(
        self, site_id: Optional[str] = None, limit: int = 50, offset: int = 0
    ) -> List[Dict[str, Any]]:
        """Get photo galleries."""
        try:
            # Use explicit column selection to avoid any potential RLS issues
            query = self.client.table("galleries").select(
                "id, site_id, name, description, images, status, "
                "created_at, updated_at"
            )

            if site_id:
                query = query.eq("site_id", site_id)

            query = query.limit(limit).offset(offset)
            result = query.execute()

            logger.debug(f"Retrieved {len(result.data)} galleries")
            return result.data

        except Exception as e:
            self._handle_query_error(e, f"get_galleries(site_id={site_id})")

    def get_gallery_by_id(self, gallery_id: str) -> Optional[Dict[str, Any]]:
        """Get a specific gallery by ID."""
        try:
            result = (
                self.client.table("galleries")
                .select("*")
                .eq("id", gallery_id)
                .execute()
            )

            if result.data:
                logger.debug(f"Retrieved gallery with ID: {gallery_id}")
                return result.data[0]
            else:
                logger.warning(f"No gallery found with ID: {gallery_id}")
                return None

        except Exception as e:
            self._handle_query_error(e, f"get_gallery_by_id({gallery_id})")


# Global database client instance
_db_client: Optional[SupabaseClient] = None


def get_database_client() -> SupabaseClient:
    """Get the global database client instance."""
    global _db_client
    if _db_client is None:
        _db_client = SupabaseClient()
    return _db_client