db_user.py

import os
import json
from db_client import supabase, db_execute

from classify_parameters import TRACKED_FIELDS

### HELPER FOR IMPORTING AND EXPORTING USER PROFILE TO SUPABASE
###############################################################

def load_user_info(user_id: str):
    """Fetch user profile from Supabase and convert types back to Python."""
    resp = supabase.table("user_profiles").select("*").eq("id", user_id).execute()
    if not resp.data:
        print(f"⚠️ No profile found for {user_id}, returning empty profile")
        return {"user_profile": {}, "interaction_log": {}}

    raw_profile = resp.data[0]
    clean_profile = {}

    for field in TRACKED_FIELDS:
        value = raw_profile.get(field)

        if value is None:
            clean_profile[field] = None
            continue

        # Kids → parse JSONB string back to list[dict]
        if field == "kids":
            try:
                if isinstance(value, str):  # Supabase might return it as JSON string
                    clean_profile[field] = json.loads(value)
                else:
                    clean_profile[field] = value
            except Exception as e:
                print(f"⚠️ Could not parse kids JSON: {value}, error={e}")
                clean_profile[field] = []

        # Arrays → keep as Python lists
        elif field in ["hobbies", "interests", "sports_played", "sports_watched", "language_spoken"]:
            if isinstance(value, list):
                clean_profile[field] = value
            elif isinstance(value, str):
                # fallback: single string returned, wrap into list
                clean_profile[field] = [value]
            else:
                clean_profile[field] = []

        # Default → text
        else:
            clean_profile[field] = str(value)

    return {"user_profile": clean_profile, "interaction_log": {}}



def save_user_info(user_info: dict, user_id: str):
    """Save/update user profile in Supabase, merging with existing values."""

    profile = user_info.get("user_profile", {})
    if not profile:
        print("⚠️ Nothing to save, user_profile empty")
        return

    # Load existing profile first
    existing_data = load_user_info(user_id).get("user_profile", {})

    clean_profile = {}

    for field in TRACKED_FIELDS:
        new_value = profile.get(field)
        old_value = existing_data.get(field)

        # ---- Handle Kids specially ----
        if field == "kids":
            try:
                old_kids = old_value or []
                if isinstance(old_kids, str):
                    old_kids = json.loads(old_kids)
                if not isinstance(old_kids, list):
                    old_kids = []

                new_kids = new_value or []
                if isinstance(new_kids, str):
                    new_kids = json.loads(new_kids)
                if not isinstance(new_kids, list):
                    new_kids = []

                # Merge by name
                merged_kids = {k["name"]: k for k in old_kids if isinstance(k, dict) and "name" in k}

                for nk in new_kids:
                    if not isinstance(nk, dict) or "name" not in nk:
                        continue
                    name = nk["name"]
                    if name in merged_kids:
                        # Update existing kid with new non-empty fields
                        for k, v in nk.items():
                            if v not in [None, "", []]:
                                merged_kids[name][k] = v
                    else:
                        merged_kids[name] = nk

                clean_profile[field] = json.dumps(list(merged_kids.values()))

            except Exception as e:
                print(f"⚠️ Could not merge kids info: {new_value}, error={e}")
                clean_profile[field] = old_value

        # ---- Handle list-type fields ----
        elif field in ["hobbies", "interests", "sports_played", "sports_watched", "language_spoken"]:
            old_list = old_value or []
            if isinstance(old_list, str):
                old_list = [old_list]

            if isinstance(new_value, str):
                new_list = [new_value]
            elif isinstance(new_value, list):
                new_list = new_value
            else:
                new_list = []

            merged = list({v.strip() for v in (old_list + new_list) if v})
            clean_profile[field] = merged if merged else old_value

        # ---- Handle simple fields ----
        else:
            if new_value not in [None, "", []]:
                clean_profile[field] = str(new_value)
            else:
                clean_profile[field] = old_value

    # Always set ID
    clean_profile["id"] = user_id

    # Upsert into Supabase
    supabase.table("user_profiles").upsert(clean_profile).execute()


#use in classify_chat.py to update countries
def update_country(role: str, country_name: str, user_id: str) -> bool:
    """Update living_country or origin_country in Supabase."""
    field = "living_country" if role == "living" else "origin_country"
    try:
        supabase.table("user_profiles").update({field: country_name}).eq("id", user_id).execute()
        print(f"✅ Updated {field} for {user_id} → {country_name}")
        return True
    except Exception as e:
        print(f"❌ Failed to update {field} for {user_id}: {e}")
        return False

### ENSURE USER ROW EXISTS (FK parent for history tables)
###############################################################

def ensure_user_row(user_id: str, email: str = "") -> None:
    """No-op.

    Previously inserted into public.users as a supposed FK parent for the
    history tables. That table does not exist (Postgrest returns PGRST205),
    so this ran on every authenticated request, failed 100% of the time, and
    added a wasted round-trip. The history tables work without it. Kept as a
    no-op so the import in app.py stays valid.
    """
    return None


### HELPER FOR IMPORTING AND EXPORTING HISORIES TO SUPABASE
###############################################################

def load_history_for_display(user_id: str):
    """Return flattened total history for UI display."""
    total = _load_history("chat_history_total", user_id)
    messages = []
    for session in total["sessions"]:
        for m in session["messages"]:
            messages.append(m)
    return messages

def _load_history(table: str, user_id: str):
    """Fetch messages JSON from Supabase, init if missing."""
    resp = db_execute(supabase.table(table).select("messages").eq("id", user_id))
    if resp.data:
        return resp.data[0]["messages"]
    init_state = {"sessions": []}
    try:
        db_execute(supabase.table(table).upsert({"id": user_id, "messages": init_state}))
    except Exception as e:
        print(f"⚠️ _load_history could not init {table} for {user_id}: {e}")
    return init_state

def _save_history(table: str, messages: dict, user_id: str):
    """Save messages JSON back into Supabase."""
    db_execute(supabase.table(table).upsert({"id": user_id, "messages": messages}))

### HELPER FOR DOWNLOADING FAISS FROM SUPABASE
###############################################################

def download_faiss_from_supabase(db_key: str, username: str = None) -> str:
    """
    Download FAISS index (index.faiss + index.pkl if present) from Supabase Storage.
    Bucket = 'Databases'
    - db1/db2/db3 → Databases/shared/db1/
    - db4+    → Databases/users/user_<username>/dbX/
    """
    bucket_name = "Databases"

    if db_key in ["db1", "db2", "db3","db8"]:
        path_prefix = f"shared/{db_key}"
        tmp_dir = f"/tmp/global_{db_key}"
    else:
        if not username:
            raise ValueError(f"db_key {db_key} requires a username")
        path_prefix = f"users/user_{username}/{db_key}"
        tmp_dir = f"/tmp/{username}_{db_key}"

    os.makedirs(tmp_dir, exist_ok=True, mode=0o700)

    for filename in ["index.faiss", "index.pkl"]:
        path = f"{path_prefix}/{filename}"
        try:
            resp = supabase.storage.from_(bucket_name).download(path)
            if not resp:
                print(f"[WARN] {filename} not found in bucket, skipping")
                continue
        except Exception as e:
            print(f"[WARN] Failed to download {path} from {bucket_name}: {e}")
            continue
        with open(os.path.join(tmp_dir, filename), "wb") as f:
            f.write(resp)
    if not os.path.exists(os.path.join(tmp_dir, "index.faiss")):
        raise FileNotFoundError(f"index.faiss missing in {bucket_name}/{path_prefix}")

    return tmp_dir



### HELPER FOR db3 and db6 UPLOAD FAISS AND JSON TO SUPABASE
###############################################################



def upload_text(bucket: str, path: str, text: str):
    """
    Upload plain text to Supabase Storage.
    Overwrites existing file by removing it first if it exists.
    """
    import traceback
    #bucket = "Databases"   # enforce correct bucket

    try:
        supabase.storage.from_(bucket).remove([path])
    except Exception as e:
        print(f"[INFO] Could not remove existing file {path} in {bucket}: {e}")

    try:
        supabase.storage.from_(bucket).upload(
            path,
            text.encode("utf-8"),
            {"content-type": "text/plain"}
        )
    except Exception as e:
        print(f"[ERROR] Upload failed → bucket={bucket}, path={path}")
        traceback.print_exc()
        raise

    print(f"✅ Uploaded text file to {bucket}/{path}")




def upload_json(bucket: str, path: str, data: str):
    """
    Upload JSON data to Supabase Storage.
    Overwrites existing file by removing it first if it exists.
    """
    import traceback
    #bucket = "Databases"

    try:
        supabase.storage.from_(bucket).remove([path])
    except Exception as e:
        print(f"[INFO] Could not remove existing file {path} in {bucket}: {e}")

    try:
        supabase.storage.from_(bucket).upload(
            path,
            json.dumps(data, indent=2).encode("utf-8"),
            {"content-type": "application/json"}
        )
    except Exception as e:
        print(f"[ERROR] Upload failed → bucket={bucket}, path={path}")
        traceback.print_exc()
        raise

    print(f"✅ Uploaded JSON file to {bucket}/{path}")




def save_faiss_to_supabase(db, db_key: str, username: str = None):
    """
    Save FAISS index to Supabase Storage inside the 'Databases' bucket.
    - db1/db2 → Databases/shared/db1/
    - db3+    → Databases/users/user_<username>/dbX/
    """
    import traceback

    # Save locally first
    tmp_dir = f"/tmp/{db_key}_upload"
    os.makedirs(tmp_dir, exist_ok=True)
    db.save_local(tmp_dir, index_name="index")


    bucket_name = "Databases"

    if db_key in ["db1", "db2", "db3","db8"]:
        path_prefix = f"shared/{db_key}"
    else:
        if not username:
            raise ValueError(f"db_key {db_key} requires username for user DBs")
        path_prefix = f"users/user_{username}/{db_key}"

    for filename in ["index.faiss", "index.pkl"]:
        local_path = os.path.join(tmp_dir, filename)
        if not os.path.exists(local_path):
            print(f"[WARN] {filename} not found locally, skipping upload")
            continue
        try:
            # Remove existing file first (prevents 409 Duplicate)
            supabase.storage.from_(bucket_name).remove([f"{path_prefix}/{filename}"])
        except Exception as e:
            print(f"[INFO] Could not remove {filename} before upload: {e}")
        
        try:
            with open(local_path, "rb") as f:
                supabase.storage.from_(bucket_name).upload(
                    f"{path_prefix}/{filename}",
                    f,
                    {"content-type": "application/octet-stream"}
                )
        except Exception as e:
            print(f"[ERROR] Upload failed → bucket={bucket_name}, path={path_prefix}/{filename}")
            traceback.print_exc()
            raise

    print(f"✅ Uploaded FAISS index to {bucket_name}/{path_prefix}")

# put this in main (same file where you have your Supabase client `sb`)
def create_ui_language_row(user_id: str, ui_lang: str) -> None:
    """Create/ensure a row in user_ui_language with initial_language=ui_lang and last_message_language='unknown'."""
    supabase.table("user_ui_language").upsert(
        {
            "user_id": user_id,
            "initial_language": ui_lang,
        },
        on_conflict="user_id",
    ).execute()

def get_initial_language(user_id: str, default: str = "en") -> str:
    """Return the user's initial_language from user_ui_language, or default."""
    try:
        res = (
            supabase.table("user_ui_language")
            .select("initial_language")
            .eq("user_id", user_id)
            .limit(1)
            .execute()
        )
        rows = res.data or []
        if not rows:
            return default
        lang = (rows[0].get("initial_language") or "").lower().strip()
        if len(lang) == 2 and lang.isalpha():
            return lang
        return default
    except Exception as e:
        print(f"[get_initial_language] error: {e}")
        return default

def get_last_message_language(user_id: str, default: str = "en") -> str:
    """Return the language used in the previous message exchange, or default."""
    try:
        res = (
            supabase.table("user_ui_language")
            .select("last_message_language")
            .eq("user_id", user_id)
            .limit(1)
            .execute()
        )
        rows = res.data or []
        if not rows:
            return default
        lang = (rows[0].get("last_message_language") or "").lower().strip()
        if len(lang) == 2 and lang.isalpha():
            return lang
        return default
    except Exception as e:
        print(f"[get_last_message_language] error: {e}")
        return default


def set_last_message_language(user_id: str, code_language: str) -> None:
    if len(code_language) != 2 or not code_language.isalpha():
        code_language = "en"  # safety for DB CHECK constraint
    # Recording the UI language is a non-critical side-effect. Use db_execute so a
    # transient transport blip is retried, and never let a failure here abort the
    # user's reply (this used to raise RemoteProtocolError up through run_chat_app).
    try:
        db_execute(
            supabase.table("user_ui_language").upsert(
                {"user_id": user_id, "last_message_language": code_language},
                on_conflict="user_id",
            )
        )
    except Exception as e:
        print(f"[set_last_message_language] non-fatal write failed for {user_id}: {e}")

# --- JSON/TEXT DOWNLOAD HELPERS ---

def download_text(bucket: str, path: str) -> str:
    """Download a UTF-8 text file from Supabase Storage."""
    data = supabase.storage.from_(bucket).download(path)
    # supabase-py returns bytes
    return data.decode("utf-8") if isinstance(data, (bytes, bytearray)) else str(data)

def download_json(bucket: str, path: str):
    """Download a JSON file from Supabase Storage and parse to Python."""
    txt = download_text(bucket, path)
    return json.loads(txt)

def load_json(path: str):
    """
    Flexible loader:
    - Local files: 'style_examples.json' or '/abs/path/file.json'
    - Supabase Storage via URI: 'supabase://<bucket>/<path/to/file.json>'
    """
    # Supabase URI mode
    if isinstance(path, str) and path.startswith("supabase://"):
        # Example: supabase://Assets/style_examples.json
        _, rest = path.split("://", 1)
        parts = rest.split("/", 1)
        bucket = parts[0]
        key = parts[1] if len(parts) > 1 else ""
        if not key:
            raise ValueError("Supabase URI must include object path after bucket, e.g. supabase://Bucket/dir/file.json")
        return download_json(bucket, key)
        
# def get_user_profile_by_username(username: str):
#     """
#     Look up user_profiles row by username and return (profile_row, user_id).

#     Raises ValueError if no match is found.
#     """
#     resp = supabase.table("user_profiles").select("*").eq("username", username).limit(1).execute()
#     rows = resp.data or []
#     if not rows:
#         raise ValueError(f"No user_profile found for username={username!r}")
#     row = rows[0]
#     user_id = row["id"]
#     return row, user_id

def get_user_id_from_jwt(access_token: str) -> str:
    """
    Given a Supabase access token (JWT from client),
    ask Supabase who this user is, and return user.id (UUID).
    """
    res = supabase.auth.get_user(access_token)
    user = res.user
    if not user or not getattr(user, "id", None):
        raise Exception("Invalid Supabase JWT – cannot get user")
    return user.id

def get_user_profile_by_id(user_id: str):
    """
    Look up user_profiles row by Supabase Auth ID.
    Returns (profile_row, username) or (None, None) if not found.
    """
    resp = supabase.table("user_profiles").select("*").eq("id", user_id).limit(1).execute()
    rows = resp.data or []
    if not rows:
        return None, None
    row = rows[0]
    username = row.get("username") or user_id
    return row, username