from __future__ import annotations

import asyncio
import logging
from typing import Any, Dict, List, Optional

from supabase import create_client, Client

from app.config import get_settings

logger = logging.getLogger(__name__)


class SupabaseClient:
    def __init__(self, url: str, service_key: str):
        self._url = url
        self._service_key = service_key
        self._client: Optional[Client] = None

    async def initialize(self):
        self._client = await asyncio.to_thread(create_client, self._url, self._service_key)
        logger.info("Supabase client initialized")

    @property
    def client(self) -> Client:
        if self._client is None:
            raise RuntimeError("Supabase client not initialized. Call initialize() first.")
        return self._client

    async def close(self):
        self._client = None
        set_supabase_client(None)
        logger.info("Supabase client closed")

    # --- Table operations ---

    async def select(
        self, table: str, columns: str = "*",
        eq: Optional[tuple[str, Any]] = None,
        order: Optional[tuple[str, bool]] = None,
        limit: Optional[int] = None,
        offset: Optional[int] = None,
    ) -> List[Dict[str, Any]]:
        query = self.client.table(table).select(columns)
        if eq:
            query = query.eq(eq[0], eq[1])
        if order:
            query = query.order(order[0], desc=order[1])
        if limit:
            query = query.limit(limit)
        if offset:
            query = query.offset(offset)
        result = await asyncio.to_thread(query.execute)
        return result.data if result else []

    async def select_in(
        self, table: str, column: str, values: List[Any],
        columns: str = "*",
    ) -> List[Dict[str, Any]]:
        query = self.client.table(table).select(columns).in_(column, values)
        result = await asyncio.to_thread(query.execute)
        return result.data if result else []

    async def insert(
        self, table: str, data: Dict[str, Any],
        returning: str = "representation",
    ) -> Optional[Dict[str, Any]]:
        query = self.client.table(table).insert(data, returning=returning)
        result = await asyncio.to_thread(query.execute)
        if result and result.data:
            return result.data[0]
        return None

    async def update(
        self, table: str, column: str, value: Any,
        data: Dict[str, Any],
    ) -> List[Dict[str, Any]]:
        query = self.client.table(table).update(data).eq(column, value)
        result = await asyncio.to_thread(query.execute)
        return result.data if result else []

    async def delete(
        self, table: str, column: str, value: Any,
    ) -> List[Dict[str, Any]]:
        query = self.client.table(table).delete().eq(column, value)
        result = await asyncio.to_thread(query.execute)
        return result.data if result else []

    async def upsert(
        self, table: str, data: Dict[str, Any],
        on_conflict: str = "id",
    ) -> Optional[Dict[str, Any]]:
        query = self.client.table(table).upsert(data, on_conflict=on_conflict)
        result = await asyncio.to_thread(query.execute)
        if result and result.data:
            return result.data[0]
        return None

    async def find_one(
        self, table: str, column: str, value: Any,
        columns: str = "*",
    ) -> Optional[Dict[str, Any]]:
        rows = await self.select(table, columns=columns, eq=(column, value), limit=1)
        return rows[0] if rows else None

    async def find_all(
        self, table: str, column: str, value: Any,
        columns: str = "*",
    ) -> List[Dict[str, Any]]:
        return await self.select(table, columns=columns, eq=(column, value))

    async def count(
        self, table: str, column: str, value: Any,
    ) -> int:
        query = self.client.table(table).select("*", count="exact").eq(column, value)
        result = await asyncio.to_thread(query.execute)
        return result.count if hasattr(result, "count") and result.count else 0


_client_instance: Optional[SupabaseClient] = None


def get_supabase_client() -> Optional[SupabaseClient]:
    global _client_instance
    return _client_instance


def set_supabase_client(client: SupabaseClient):
    global _client_instance
    _client_instance = client


async def create_supabase_client() -> SupabaseClient:
    settings = get_settings()
    url = settings.supabase_url
    key = settings.supabase_service_role_key
    if not url or not key:
        logger.warning("Supabase not configured (SUPABASE_URL or SUPABASE_SERVICE_ROLE_KEY missing)")
        return None
    client = SupabaseClient(url, key)
    await client.initialize()
    set_supabase_client(client)
    return client