Backend/Database/crud.py

# crud.py  (reemplaza la versión SQLAlchemy)
import os
import httpx
from dotenv import load_dotenv
from typing import Dict, Optional, List
from fastapi import HTTPException
load_dotenv()

SUPABASE_URL = os.getenv("SUPABASE_URL").rstrip("/")
SUPABASE_KEY = os.getenv("SUPABASE_KEY")
database_service_Key = os.getenv("database_service_Key")
TABLE = os.getenv("TABLE_NAME", "idioms")

HEADERS = {
    "apikey": database_service_Key,
    "Authorization": f"Bearer {database_service_Key}",
    "Content-Type": "application/json",
    "Accept": "application/json",
}

# helpers
async def _client():
    return httpx.AsyncClient(timeout=30.0)

# CRUD
async def get_idioms(skip: int = 0, limit: int = 100000):
    async with httpx.AsyncClient() as client:
        url = f"{SUPABASE_URL}/rest/v1/{TABLE}?select=*&offset={skip}&limit={limit}"
        print("Supabase GET URL:", url)
        r = await client.get(url, headers=HEADERS)
        print("Supabase GET status:", r.status_code)
        print("Supabase GET response:", r.text)
        r.raise_for_status()
        return r.json()

async def get_all_idioms():
    all_idioms = []
    limit = 1000   # Supabase max per request
    offset = 0

    async with httpx.AsyncClient(timeout=60.0) as client:
        while True:
            url = f"{SUPABASE_URL}/rest/v1/{TABLE}?select=*&limit={limit}&offset={offset}"
            r = await client.get(url, headers=HEADERS)
            r.raise_for_status()
            data = r.json()
            if not data:
                break
            # ensure validation_count is a dict
            for item in data:                 
                if not isinstance(item.get("validation_count"), dict):
                    item["validation_count"] = {}
            all_idioms.extend(data)
            offset += limit

    return all_idioms
    
async def get_idiom(idiom_id: str):
    async with httpx.AsyncClient() as client:
        try:
            # Include both examples and meanings via foreign key embedding
            url = (
                f"{SUPABASE_URL}/rest/v1/{TABLE}?"
                f"id=eq.{idiom_id}&"
                f"select=*,"
                f"idiom_meanings!idiom_meanings_idiom_id_fkey(*),"
                f"examples!examples_idiom_id_fkey(*)"
                
            )
            print(f"Fetching idiom from Supabase: {url}")  # debug
            r = await client.get(url, headers=HEADERS)
            print("HTTP status code:", r.status_code)  # debug
            r.raise_for_status()
            data = r.json()
            print("Raw data from Supabase:", data)  # debug
        except httpx.RequestError as e:
            print("Request failed:", e)
            raise HTTPException(status_code=500, detail=f"Supabase request failed: {e}")
        except httpx.HTTPStatusError as e:
            print("HTTP error:", e)
            raise HTTPException(status_code=500, detail=f"Supabase HTTP error: {e}")
        except Exception as e:
            print("Unexpected error:", e)
            raise HTTPException(status_code=500, detail=f"Unexpected error: {e}")

        if not data:
            print(f"No idiom found for id: {idiom_id}")  # debug
            return None

        idiom = data[0]
        if not isinstance(idiom, dict):
            print(f"Unexpected data type for idiom: {type(idiom)}")  # debug
            raise ValueError(f"Expected dict, got: {type(idiom)}")

        # --- Transform examples ---
        raw_examples = idiom.get("examples") or []
        idiom["examples"] = [
            {
                "id": ex.get("id"),
                "source_text": ex.get("source_text") or "",
                "source_language": ex.get("source_language") or idiom.get("language"),
                "translations": json.loads(ex["translations"]) if isinstance(ex.get("translations"), str) else ex.get("translations") or [],
                "dialect": ex.get("dialect"),
                "url": ex.get("url"),
                "source": ex.get("source"),
            }
            for ex in raw_examples
        ]
        print(f"Found {len(idiom['examples'])} examples")  # debug

        # --- Transform meanings ---
        raw_meanings = idiom.get("idiom_meanings") or []
        print("Raw meanings data:", raw_meanings)  # debug
        idiom["meanings"] = [
            {
                "meaning_id": m.get("meaning_id"),
                "idiom_id": m.get("idiom_id"),
                "sense_number": m.get("sense_number"),
                "register": m.get("register") or [],
                "region": m.get("region") or [],
                "definitions": m.get("definitions") or [],
                "version": m.get("version"),  # optional, if you need it
            }
            for m in raw_meanings
        ]
        print("Transformed meanings data:", idiom["meanings"])  # debug
        print(f"Found {len(idiom['meanings'])} meanings")  # debug
        return idiom



async def search_idioms(query: str = "", language: Optional[str] = None, skip: int = 0, limit: int = 50):
    async with httpx.AsyncClient() as client:
        # Compose select param to embed idiom_meanings
        select_query = "*,idiom_meanings!idiom_meanings_idiom_id_fkey(*)"
        
        url = (
            f"{SUPABASE_URL}/rest/v1/{TABLE}"
            f"?offset={skip}&limit={limit}&select={select_query}"
        )
        
        # Maintain partial text match on idiom column
        if query:
            url += f"&idiom=ilike.*{query}*"
        
        # Maintain language filter if specified and not "all"
        if language and language.lower() not in ("all", "*"):
            url += f"&language=eq.{language}"
        
        r = await client.get(url, headers=HEADERS)
        r.raise_for_status()
        data = r.json()
        
        # Ensure validation_count is a dict
        for item in data:
            if not isinstance(item.get("validation_count"), dict):
                item["validation_count"] = {}
            
            # Transform embedded idiom_meanings to meanings field for UI use
            raw_meanings = item.get("idiom_meanings") or []
            item["meanings"] = [
                {
                    "meaning_id": m.get("meaning_id"),
                    "idiom_id": m.get("idiom_id"),
                    "sense_number": m.get("sense_number"),
                    "register": m.get("register") or [],
                    "region": m.get("region") or [],
                    "definitions": m.get("definitions") or [],
                    "version": m.get("version"),
                }
                for m in raw_meanings
            ]
        
        return data


    
async def create_idiom(item: dict):
    async with httpx.AsyncClient() as client:
        url = f"{SUPABASE_URL}/rest/v1/{TABLE}"
        r = await client.post(url, json=item, headers=HEADERS)
        r.raise_for_status()  # fail if not 2xx

        try:
            data = r.json()
        except ValueError:
            # Supabase returned empty body, fallback to the original item
            data = item

        if isinstance(data, list) and data:
            return data[0]
        if isinstance(data, dict) and data:
            return data
        # final fallback
        return item
async def update_idiom(idiom_id: str, item: dict):
    async with httpx.AsyncClient() as client:
        url = f"{SUPABASE_URL}/rest/v1/{TABLE}?id=eq.{idiom_id}"
        r = await client.patch(url, json=item, headers=HEADERS)
        if r.status_code not in (200, 204):
            raise httpx.HTTPStatusError("Update failed", request=r.request, response=r)
        # After patch, fetch the updated row
        return await get_idiom(idiom_id)

async def delete_idiom(idiom_id: str):
    async with httpx.AsyncClient() as client:
        url = f"{SUPABASE_URL}/rest/v1/{TABLE}?id=eq.{idiom_id}"
        r = await client.delete(url, headers=HEADERS)
        if r.status_code not in (200, 204):
            raise httpx.HTTPStatusError("Delete failed", request=r.request, response=r)
        return {"status": "deleted"}