src/storage.py

import hashlib
import os
from datetime import datetime, timezone

import requests
from fastapi import HTTPException
from src.fingering import normalize_fingering_state


MAX_INLINE_SHEET_HTML_BYTES = int(os.getenv("NOTEPILOT_MAX_INLINE_SHEET_HTML_BYTES", "1500000"))


def _inline_sheet_html(value: str) -> str:
    text = value or ""
    if not text:
        return ""
    if len(text.encode("utf-8")) > MAX_INLINE_SHEET_HTML_BYTES:
        return ""
    return text


def _score_row_to_payload(row: dict, *, include_sheet_assets: bool = True) -> dict:
    score_data = row.get("score_data") or {}
    parts = score_data.get("parts") or []
    musicxml_source = score_data.get("musicxml_source") or ""
    fingered_musicxml_source = score_data.get("fingered_musicxml_source") or ""
    fingered_sheet_html = score_data.get("fingered_sheet_html") or ""
    right_hand = parts[0]["notes"] if parts else []
    left_hand = []
    for part in parts[1:]:
        for event in part.get("notes", []):
            pitches = event[0] if isinstance(event[0], list) else [event[0]]
            duration = event[2] if len(event) > 2 else 0.75
            merged = [pitches, event[1], duration]
            if len(event) > 3:
                merged.append(event[3])
            if len(event) > 4:
                if len(merged) == 3:
                    merged.append(None)
                merged.extend(event[4:])
            left_hand.append(merged)
    left_hand.sort(key=lambda event: event[1])
    sheet_html = row.get("sheet_html") or ""
    has_sheet = "<svg" in sheet_html or bool(musicxml_source)
    has_fingered_sheet = "<svg" in fingered_sheet_html or bool(fingered_musicxml_source)
    fingering = normalize_fingering_state(
        parts,
        score_data.get("fingering"),
        has_fingered_sheet=has_fingered_sheet,
    )
    return {
        "name": row.get("slug") or row.get("id"),
        "title": row.get("title") or row.get("slug") or row.get("id"),
        "source_type": row.get("source_type") or "converted",
        "parts": parts,
        "has_sheet": has_sheet,
        "has_fingered_sheet": has_fingered_sheet,
        "measure_beats": row.get("measure_beats") or [],
        "right_hand": right_hand,
        "left_hand": left_hand,
        "sheet_html": sheet_html if include_sheet_assets and has_sheet else "",
        "musicxml_source": musicxml_source if include_sheet_assets else "",
        "fingered_musicxml_source": fingered_musicxml_source if include_sheet_assets else "",
        "fingering": fingering,
    }


class SupabaseScoreStore:
    def __init__(self, url: str, service_role_key: str):
        self.base_url = url.rstrip("/")
        self.rest_url = f"{self.base_url}/rest/v1"
        self.headers = {
            "apikey": service_role_key,
            "Authorization": f"Bearer {service_role_key}",
            "Content-Type": "application/json",
        }

    def _request(self, method: str, path: str, *, params=None, json_body=None, headers=None):
        try:
            response = requests.request(
                method,
                f"{self.rest_url}/{path.lstrip('/')}",
                params=params,
                json=json_body,
                headers={**self.headers, **(headers or {})},
                timeout=20,
            )
        except requests.exceptions.Timeout as exc:
            raise HTTPException(status_code=503, detail="Supabase timed out. Try again.") from exc
        except requests.exceptions.RequestException as exc:
            raise HTTPException(status_code=503, detail="Supabase request failed. Try again.") from exc
        if response.status_code >= 400:
            try:
                error_payload = response.json()
            except ValueError:
                error_payload = {}
            error_message = str(error_payload.get("message") or response.text or "").strip()
            if (
                "schema cache" in error_message
                and ("public.app_users" in error_message or "public.app_sessions" in error_message)
            ):
                raise HTTPException(
                    status_code=503,
                    detail="Supabase auth tables are not set up. Run supabase_simple_auth.sql.",
                )
            if response.status_code >= 500:
                raise HTTPException(
                    status_code=503,
                    detail="Supabase is unavailable. Try again.",
                )
            if "57014" in response.text or "statement timeout" in response.text.lower():
                raise HTTPException(
                    status_code=503,
                    detail="Supabase timed out while saving this score. Try again.",
                )
            raise HTTPException(status_code=500, detail=f"Supabase error: {error_message or response.text}")
        if not response.text:
            return None
        return response.json()

    def list_scores(self, user_id: str):
        rows = self._request(
            "GET",
            "scores",
            params={
                "select": "slug,title,created_at",
                "user_id": f"eq.{user_id}",
                "order": "created_at.desc",
            },
        ) or []
        names = [row["slug"] for row in rows if row.get("slug")]
        return {
            "scores": names,
            "items": [
                {
                    "name": row["slug"],
                    "title": row.get("title") or row["slug"],
                    "created_at": row.get("created_at"),
                    "has_sheet": True,
                }
                for row in rows if row.get("slug")
            ],
        }

    def load_score_row(self, user_id: str, name: str):
        rows = self._request(
            "GET",
            "scores",
            params={
                "select": "id,user_id,slug,title,source_type,score_data,measure_beats,sheet_html,created_at",
                "user_id": f"eq.{user_id}",
                "slug": f"eq.{name}",
                "limit": "1",
            },
        ) or []
        if not rows:
            raise HTTPException(status_code=404, detail=f"Score '{name}' not found")
        return rows[0]

    def load_score(self, user_id: str, name: str, *, include_sheet_assets: bool = True):
        return _score_row_to_payload(
            self.load_score_row(user_id, name),
            include_sheet_assets=include_sheet_assets,
        )

    def save_score(self, user_id: str, payload: dict):
        existing = self._request(
            "GET",
            "scores",
            params={
                "select": "id,created_at",
                "user_id": f"eq.{user_id}",
                "slug": f"eq.{payload['name']}",
                "limit": "1",
            },
        ) or []
        preserve_keys = (
            "musicxml_source",
            "fingered_musicxml_source",
            "fingered_sheet_html",
            "fingering",
            "measure_beats",
            "sheet_html",
        )
        needs_existing_payload = bool(existing) and any(key not in payload for key in preserve_keys)
        existing_payload = {}
        if needs_existing_payload:
            existing_payload_rows = self._request(
                "GET",
                "scores",
                params={
                    "select": "score_data,measure_beats,sheet_html",
                    "user_id": f"eq.{user_id}",
                    "slug": f"eq.{payload['name']}",
                    "limit": "1",
                },
            ) or []
            existing_payload = existing_payload_rows[0] if existing_payload_rows else {}

        existing_score_data = existing_payload.get("score_data") or {}
        existing_measure_beats = existing_payload.get("measure_beats") or []
        existing_sheet_html = existing_payload.get("sheet_html") or ""

        def payload_or_existing(key: str, default=""):
            if key in payload:
                return payload[key] if payload[key] is not None else default
            return existing_score_data.get(key) or default

        row = {
            "user_id": user_id,
            "slug": payload["name"],
            "title": payload.get("title") or payload["name"],
            "source_type": payload.get("source_type") or "converted",
            "score_data": {
                "parts": payload["parts"],
                "musicxml_source": payload_or_existing("musicxml_source"),
                "fingered_musicxml_source": payload_or_existing("fingered_musicxml_source"),
                "fingered_sheet_html": _inline_sheet_html(payload_or_existing("fingered_sheet_html")),
                "fingering": payload["fingering"] if "fingering" in payload else (existing_score_data.get("fingering") or {}),
            },
            "measure_beats": payload["measure_beats"] if "measure_beats" in payload else (existing_measure_beats or []),
            "sheet_html": _inline_sheet_html(
                payload["sheet_html"] if "sheet_html" in payload else (existing_sheet_html or "")
            ),
        }
        if existing:
            self._request(
                "PATCH",
                "scores",
                params={
                    "user_id": f"eq.{user_id}",
                    "slug": f"eq.{payload['name']}",
                },
                json_body=row,
                headers={"Prefer": "return=minimal"},
            )
        else:
            self._request(
                "POST",
                "scores",
                json_body=row,
                headers={"Prefer": "return=minimal"},
            )
        saved_row = {
            "id": existing[0].get("id") if existing else None,
            **row,
            "created_at": (existing[0].get("created_at") if existing else None)
            or datetime.now(timezone.utc).isoformat(),
        }
        return _score_row_to_payload(saved_row)

    def delete_score(self, user_id: str, name: str):
        self._request(
            "DELETE",
            "scores",
            params={
                "user_id": f"eq.{user_id}",
                "slug": f"eq.{name}",
            },
            headers={"Prefer": "return=minimal"},
        )

    def update_score_sheet_html(self, user_id: str, name: str, sheet_html: str):
        sheet_html = _inline_sheet_html(sheet_html)
        if not sheet_html:
            return
        self._request(
            "PATCH",
            "scores",
            params={
                "user_id": f"eq.{user_id}",
                "slug": f"eq.{name}",
            },
            json_body={"sheet_html": sheet_html or ""},
            headers={"Prefer": "return=minimal"},
        )

    def upsert_score_artifact(
        self,
        *,
        score_id: str,
        user_id: str,
        artifact_type: str,
        storage_provider: str,
        object_key: str,
        content_type: str,
        size_bytes: int,
    ):
        self._request(
            "POST",
            "score_artifacts",
            params={"on_conflict": "score_id,user_id,artifact_type"},
            json_body={
                "score_id": score_id,
                "user_id": user_id,
                "artifact_type": artifact_type,
                "storage_provider": storage_provider,
                "object_key": object_key,
                "content_type": content_type,
                "size_bytes": size_bytes,
                "updated_at": datetime.now(timezone.utc).isoformat(),
            },
            headers={"Prefer": "resolution=merge-duplicates,return=minimal"},
        )

    def get_score_artifact_by_id(self, *, score_id: str, user_id: str, artifact_type: str):
        rows = self._request(
            "GET",
            "score_artifacts",
            params={
                "select": "object_key,storage_provider,content_type,size_bytes,updated_at",
                "score_id": f"eq.{score_id}",
                "user_id": f"eq.{user_id}",
                "artifact_type": f"eq.{artifact_type}",
                "limit": "1",
            },
        ) or []
        return rows[0] if rows else None

    def rename_score_title(self, user_id: str, name: str, title: str):
        result = self._request(
            "PATCH",
            "scores",
            params={
                "user_id": f"eq.{user_id}",
                "slug": f"eq.{name}",
                "select": "id,user_id,slug,title,source_type,score_data,measure_beats,sheet_html,created_at",
            },
            json_body={"title": title},
            headers={"Prefer": "return=representation"},
        ) or []
        if not result:
            raise HTTPException(status_code=404, detail=f"Score '{name}' not found")
        return _score_row_to_payload(result[0])

    def get_app_user_by_username(self, username: str):
        rows = self._request(
            "GET",
            "app_users",
            params={
                "select": "id,username,password_hash,created_at",
                "username": f"eq.{username}",
                "limit": "1",
            },
        ) or []
        return rows[0] if rows else None

    def create_app_user(self, username: str, password_hash: str):
        result = self._request(
            "POST",
            "app_users",
            params={"select": "id,username,password_hash,created_at"},
            json_body={
                "username": username,
                "password_hash": password_hash,
            },
            headers={"Prefer": "return=representation"},
        ) or []
        return result[0] if result else None

    def create_app_session(self, user_id: str, token_hash: str, expires_at_iso: str):
        result = self._request(
            "POST",
            "app_sessions",
            params={"select": "id,user_id,token_hash,expires_at,created_at"},
            json_body={
                "user_id": user_id,
                "token_hash": token_hash,
                "expires_at": expires_at_iso,
            },
            headers={"Prefer": "return=representation"},
        ) or []
        return result[0] if result else None

    def get_app_session_user(self, raw_token: str):
        token_hash = hashlib.sha256(raw_token.encode("utf-8")).hexdigest()
        rows = self._request(
            "GET",
            "app_sessions",
            params={
                "select": "id,user_id,expires_at",
                "token_hash": f"eq.{token_hash}",
                "limit": "1",
            },
        ) or []
        if not rows:
            return None
        session = rows[0]
        expires_at = session.get("expires_at")
        if expires_at:
            expires_dt = datetime.fromisoformat(expires_at.replace("Z", "+00:00"))
            if expires_dt <= datetime.now(timezone.utc):
                self.delete_app_session(raw_token)
                return None
        users = self._request(
            "GET",
            "app_users",
            params={
                "select": "id,username,created_at",
                "id": f"eq.{session['user_id']}",
                "limit": "1",
            },
        ) or []
        if not users:
            return None
        user = users[0]
        user["session_id"] = session.get("id")
        return user

    def delete_app_session(self, raw_token: str):
        token_hash = hashlib.sha256(raw_token.encode("utf-8")).hexdigest()
        self._request(
            "DELETE",
            "app_sessions",
            params={"token_hash": f"eq.{token_hash}"},
            headers={"Prefer": "return=minimal"},
        )


def create_score_store():
    url = os.getenv("SUPABASE_URL", "").strip()
    key = os.getenv("SUPABASE_SERVICE_ROLE_KEY", "").strip()
    if not (url and key):
        raise RuntimeError("SUPABASE_URL and SUPABASE_SERVICE_ROLE_KEY are required.")
    return SupabaseScoreStore(url, key)