from __future__ import annotations

import json
import urllib.error
import urllib.parse
import urllib.request
from collections.abc import Callable
from typing import Any


Transport = Callable[..., Any]


class SupabaseServiceRoleError(RuntimeError):
    pass


class SupabaseServiceRoleRepository:
    def __init__(
        self,
        *,
        supabase_url: str,
        service_role_key: str,
        transport: Transport | None = None,
    ) -> None:
        if not supabase_url.strip():
            raise ValueError("SUPABASE_URL is required")
        if not service_role_key.strip():
            raise ValueError("SUPABASE_SERVICE_ROLE_KEY is required")
        self.supabase_url = supabase_url.rstrip("/")
        self._service_role_key = service_role_key
        self._transport = transport or _urllib_transport

    def find_user_by_auth_uid(self, *, auth_uid: str) -> dict[str, Any] | None:
        rows = self._request_json(
            method="GET",
            table="users",
            query={
                "auth_uid": f"eq.{auth_uid}",
                "select": "user_id,auth_uid,email,status",
                "limit": "1",
            },
        )
        if not rows:
            return None
        if not isinstance(rows, list) or not isinstance(rows[0], dict):
            raise SupabaseServiceRoleError("Supabase users select returned an invalid response")
        return rows[0]

    def find_user_by_id(self, *, user_id: str) -> dict[str, Any] | None:
        rows = self._request_json(
            method="GET",
            table="users",
            query={
                "user_id": f"eq.{user_id}",
                "select": "user_id,status",
                "limit": "1",
            },
        )
        if not rows:
            return None
        if not isinstance(rows, list) or not isinstance(rows[0], dict):
            raise SupabaseServiceRoleError("Supabase users select returned an invalid response")
        return rows[0]

    def insert_user(self, row: dict[str, Any]) -> dict[str, Any]:
        return self._insert_one("users", row)

    def insert_api_session(self, row: dict[str, Any]) -> dict[str, Any]:
        return self._insert_one("api_sessions", row)

    def find_api_session(self, *, session_id: str) -> dict[str, Any] | None:
        rows = self._request_json(
            method="GET",
            table="api_sessions",
            query={
                "session_id": f"eq.{session_id}",
                "select": "session_id,user_id,status,expires_at",
                "limit": "1",
            },
        )
        if not rows:
            return None
        if not isinstance(rows, list) or not isinstance(rows[0], dict):
            raise SupabaseServiceRoleError("Supabase api_sessions select returned an invalid response")
        return rows[0]

    def insert_pet_profile(self, row: dict[str, Any]) -> dict[str, Any]:
        return self._insert_one("pet_profiles", row)

    def find_pet_profile(self, *, user_id: str, pet_id: str) -> dict[str, Any] | None:
        rows = self._request_json(
            method="GET",
            table="pet_profiles",
            query={
                "pet_id": f"eq.{pet_id}",
                "user_id": f"eq.{user_id}",
                "select": "pet_id,user_id,status",
                "limit": "1",
            },
        )
        if not rows:
            return None
        if not isinstance(rows, list) or not isinstance(rows[0], dict):
            raise SupabaseServiceRoleError("Supabase pet_profiles select returned an invalid response")
        return rows[0]

    def insert_pet_media(self, row: dict[str, Any]) -> dict[str, Any]:
        return self._insert_one("pet_media", row)

    def find_active_entitlements(self, *, user_id: str) -> list[dict[str, Any]]:
        rows = self._request_json(
            method="GET",
            table="entitlements",
            query={
                "user_id": f"eq.{user_id}",
                "status": "eq.active",
                "select": "type,status,quantity_total,quantity_used,expires_at",
            },
        )
        if not isinstance(rows, list):
            raise SupabaseServiceRoleError("Supabase entitlements select returned an invalid response")
        return [row for row in rows if isinstance(row, dict)]

    def find_beta_invite_by_code_hash(self, *, code_hash: str) -> dict[str, Any] | None:
        rows = self._request_json(
            method="GET",
            table="beta_invites",
            query={
                "code_hash": f"eq.{code_hash}",
                "select": "invite_id,code_hash,status,used_count,max_uses,grant_plan_code,grant_months,expires_at",
                "limit": "1",
            },
        )
        if not rows:
            return None
        if not isinstance(rows, list) or not isinstance(rows[0], dict):
            raise SupabaseServiceRoleError("Supabase beta_invites select returned an invalid response")
        return rows[0]

    def find_beta_invite_redemption(self, *, invite_id: str, user_id: str) -> dict[str, Any] | None:
        rows = self._request_json(
            method="GET",
            table="beta_invite_redemptions",
            query={
                "invite_id": f"eq.{invite_id}",
                "user_id": f"eq.{user_id}",
                "select": "redemption_id",
                "limit": "1",
            },
        )
        if not rows:
            return None
        if not isinstance(rows, list) or not isinstance(rows[0], dict):
            raise SupabaseServiceRoleError("Supabase beta_invite_redemptions select returned an invalid response")
        return rows[0]

    def insert_beta_invite_redemption(self, row: dict[str, Any]) -> dict[str, Any]:
        return self._insert_one("beta_invite_redemptions", row)

    def insert_subscription(self, row: dict[str, Any]) -> dict[str, Any]:
        return self._insert_one("subscriptions", row)

    def insert_order(self, row: dict[str, Any]) -> dict[str, Any]:
        return self._insert_one("orders", row)

    def insert_entitlements(self, rows: list[dict[str, Any]]) -> list[dict[str, Any]]:
        inserted = self._request_json(method="POST", table="entitlements", rows=rows)
        if not isinstance(inserted, list) or any(not isinstance(row, dict) for row in inserted):
            raise SupabaseServiceRoleError("Supabase entitlements insert returned an invalid response")
        return inserted

    def find_webhook_event(self, *, provider_event_id: str) -> dict[str, Any] | None:
        rows = self._request_json(
            method="GET",
            table="webhook_events",
            query={
                "provider": "eq.stripe",
                "provider_event_id": f"eq.{provider_event_id}",
                "select": "webhook_event_id,status",
                "limit": "1",
            },
        )
        if not rows:
            return None
        if not isinstance(rows, list) or not isinstance(rows[0], dict):
            raise SupabaseServiceRoleError("Supabase webhook_events select returned an invalid response")
        return rows[0]

    def insert_webhook_event(self, row: dict[str, Any]) -> dict[str, Any]:
        return self._insert_one("webhook_events", row)

    def update_webhook_event_status(
        self,
        *,
        webhook_event_id: str,
        status: str,
        processed_at: str,
    ) -> dict[str, Any]:
        return self._patch_one(
            "webhook_events",
            query={"webhook_event_id": f"eq.{webhook_event_id}"},
            row={
                "status": status,
                "processed_at": processed_at,
            },
        )

    def update_beta_invite_usage(
        self,
        *,
        invite_id: str,
        used_count: int,
        status: str,
    ) -> dict[str, Any]:
        return self._patch_one(
            "beta_invites",
            query={"invite_id": f"eq.{invite_id}"},
            row={"used_count": used_count, "status": status},
        )

    def find_pet_media(self, *, user_id: str, pet_id: str, media_id: str) -> dict[str, Any] | None:
        rows = self._request_json(
            method="GET",
            table="pet_media",
            query={
                "media_id": f"eq.{media_id}",
                "pet_id": f"eq.{pet_id}",
                "user_id": f"eq.{user_id}",
                "select": "media_id,user_id,pet_id,object_key,bucket,mime_type,size_bytes,sha256,source_quality,deletion_status",
                "limit": "1",
            },
        )
        if not rows:
            return None
        if not isinstance(rows, list) or not isinstance(rows[0], dict):
            raise SupabaseServiceRoleError("Supabase pet_media select returned an invalid response")
        return rows[0]

    def find_pet_media_by_id(self, *, media_id: str) -> dict[str, Any] | None:
        rows = self._request_json(
            method="GET",
            table="pet_media",
            query={
                "media_id": f"eq.{media_id}",
                "select": "media_id,user_id,pet_id,object_key,bucket,source_quality,deletion_status",
                "limit": "1",
            },
        )
        if not rows:
            return None
        if not isinstance(rows, list) or not isinstance(rows[0], dict):
            raise SupabaseServiceRoleError("Supabase pet_media select returned an invalid response")
        return rows[0]

    def update_pet_media_quality(
        self,
        *,
        media_id: str,
        source_quality: str,
        quality_failure_reason: str | None,
        updated_at: str,
    ) -> dict[str, Any]:
        return self._patch_one(
            "pet_media",
            query={"media_id": f"eq.{media_id}"},
            row={
                "source_quality": source_quality,
                "quality_failure_reason": quality_failure_reason,
                "updated_at": updated_at,
            },
        )

    def update_pet_media_deletion(
        self,
        *,
        media_id: str,
        deletion_status: str,
        deleted_at: str | None,
        updated_at: str,
    ) -> dict[str, Any]:
        row = {
            "deletion_status": deletion_status,
            "updated_at": updated_at,
        }
        if deleted_at is not None:
            row["deleted_at"] = deleted_at
        return self._patch_one(
            "pet_media",
            query={"media_id": f"eq.{media_id}"},
            row=row,
        )

    def insert_pet_build(self, row: dict[str, Any]) -> dict[str, Any]:
        return self._insert_one("pet_builds", row)

    def find_pet_build(self, *, user_id: str, build_id: str) -> dict[str, Any] | None:
        rows = self._request_json(
            method="GET",
            table="pet_builds",
            query={
                "build_id": f"eq.{build_id}",
                "user_id": f"eq.{user_id}",
                "select": (
                    "build_id,user_id,pet_id,status,input_media_refs,image_provider,image_model,"
                    "output_package_ref,preview_ref,allowed_actions,checksum_valid,runtime_compatible,"
                    "user_approved,approval_answers,approved_at,failure_code,failure_reason,updated_at"
                ),
                "limit": "1",
            },
        )
        if not rows:
            return None
        if not isinstance(rows, list) or not isinstance(rows[0], dict):
            raise SupabaseServiceRoleError("Supabase pet_builds select returned an invalid response")
        return rows[0]

    def update_pet_build_approval(
        self,
        *,
        build_id: str,
        status: str,
        user_approved: bool,
        approval_answers: dict[str, Any],
        approved_at: str,
        updated_at: str,
    ) -> dict[str, Any]:
        return self._patch_one(
            "pet_builds",
            query={"build_id": f"eq.{build_id}"},
            row={
                "status": status,
                "user_approved": user_approved,
                "approval_answers": approval_answers,
                "approved_at": approved_at,
                "updated_at": updated_at,
            },
        )

    def claim_next_cloud_build(self, *, worker_id: str, now: str) -> dict[str, Any]:
        rows = self._request_json(
            method="GET",
            table="pet_builds",
            query={
                "status": "eq.queued",
                "select": "build_id,user_id,pet_id,status,input_media_refs,image_provider,image_model,cost_cap_cents,cost_estimate_cents,user_approved",
                "limit": "1",
            },
        )
        if not rows:
            raise KeyError("queued build not found")
        if not isinstance(rows, list) or not isinstance(rows[0], dict):
            raise SupabaseServiceRoleError("Supabase pet_builds select returned an invalid response")
        build = rows[0]
        updated = self._patch_one(
            "pet_builds",
            query={"build_id": f"eq.{build['build_id']}"},
            row={
                "status": "processing",
                "updated_at": now,
            },
        )
        return {**build, **updated}

    def update_cloud_build_success(
        self,
        *,
        build_id: str,
        status: str,
        output_package_ref: str,
        preview_ref: str | None,
        user_approved: bool,
        allowed_actions: list[str] | None = None,
        checksum_valid: bool = True,
        runtime_compatible: bool = True,
        updated_at: str,
        completed_at: str,
    ) -> dict[str, Any]:
        return self._patch_one(
            "pet_builds",
            query={"build_id": f"eq.{build_id}"},
            row={
                "status": status,
                "output_package_ref": output_package_ref,
                "preview_ref": preview_ref,
                "allowed_actions": allowed_actions or [],
                "checksum_valid": checksum_valid,
                "runtime_compatible": runtime_compatible,
                "user_approved": user_approved,
                "approval_answers": {},
                "updated_at": updated_at,
                "completed_at": completed_at,
            },
        )

    def update_cloud_build_failure(
        self,
        *,
        build_id: str,
        failure_code: str,
        failure_reason: str,
        updated_at: str,
        completed_at: str,
    ) -> dict[str, Any]:
        return self._patch_one(
            "pet_builds",
            query={"build_id": f"eq.{build_id}"},
            row={
                "status": "failed",
                "failure_code": failure_code,
                "failure_reason": failure_reason,
                "updated_at": updated_at,
                "completed_at": completed_at,
            },
        )

    def insert_provider_call_metrics(self, rows: list[dict[str, Any]]) -> list[dict[str, Any]]:
        if not rows:
            return []
        inserted = self._request_json(method="POST", table="provider_call_metrics", rows=rows)
        if not isinstance(inserted, list) or any(not isinstance(row, dict) for row in inserted):
            raise SupabaseServiceRoleError("Supabase provider_call_metrics insert returned an invalid response")
        return inserted

    def insert_pet_package(self, row: dict[str, Any]) -> dict[str, Any]:
        return self._insert_one("pet_packages", row)

    def find_pet_package_by_build(self, *, user_id: str, build_id: str) -> dict[str, Any] | None:
        rows = self._request_json(
            method="GET",
            table="pet_packages",
            query={
                "build_id": f"eq.{build_id}",
                "user_id": f"eq.{user_id}",
                "select": (
                    "package_id,user_id,pet_id,build_id,status,runtime_version,package_sha256,"
                    "package_storage_ref,bucket,object_key,user_approved,allowed_actions"
                ),
                "limit": "1",
            },
        )
        if not rows:
            return None
        if not isinstance(rows, list) or not isinstance(rows[0], dict):
            raise SupabaseServiceRoleError("Supabase pet_packages select returned an invalid response")
        return rows[0]

    def find_pet_package(self, *, package_id: str) -> dict[str, Any] | None:
        rows = self._request_json(
            method="GET",
            table="pet_packages",
            query={
                "package_id": f"eq.{package_id}",
                "select": "package_id,user_id,pet_id,build_id,status,runtime_version,package_sha256,package_storage_ref,bucket,object_key,user_approved,allowed_actions",
                "limit": "1",
            },
        )
        if not rows:
            return None
        if not isinstance(rows, list) or not isinstance(rows[0], dict):
            raise SupabaseServiceRoleError("Supabase pet_packages select returned an invalid response")
        return rows[0]

    def update_pet_package_approval(
        self,
        *,
        package_id: str,
        status: str,
        user_approved: bool,
        updated_at: str,
    ) -> dict[str, Any]:
        return self._patch_one(
            "pet_packages",
            query={"package_id": f"eq.{package_id}"},
            row={
                "status": status,
                "user_approved": user_approved,
                "updated_at": updated_at,
            },
        )

    def update_pet_package_deletion(
        self,
        *,
        package_id: str,
        status: str,
        deleted_at: str,
        updated_at: str,
    ) -> dict[str, Any]:
        return self._patch_one(
            "pet_packages",
            query={"package_id": f"eq.{package_id}"},
            row={
                "status": status,
                "deleted_at": deleted_at,
                "updated_at": updated_at,
            },
        )

    def insert_event(self, row: dict[str, Any]) -> dict[str, Any]:
        return self._insert_one("events", row)

    def _insert_one(self, table: str, row: dict[str, Any]) -> dict[str, Any]:
        rows = self._request_json(method="POST", table=table, rows=[row])
        if not isinstance(rows, list) or not rows or not isinstance(rows[0], dict):
            raise SupabaseServiceRoleError(f"Supabase {table} insert returned an invalid response")
        return rows[0]

    def _patch_one(self, table: str, *, query: dict[str, str], row: dict[str, Any]) -> dict[str, Any]:
        rows = self._request_json(method="PATCH", table=table, row=row, query=query)
        if not isinstance(rows, list) or not rows or not isinstance(rows[0], dict):
            raise SupabaseServiceRoleError(f"Supabase {table} update returned an invalid response")
        return rows[0]

    def _request_json(
        self,
        *,
        method: str,
        table: str,
        rows: list[dict[str, Any]] | None = None,
        row: dict[str, Any] | None = None,
        query: dict[str, str] | None = None,
    ) -> Any:
        url = f"{self.supabase_url}/rest/v1/{table}"
        if query:
            url = f"{url}?{urllib.parse.urlencode(query)}"
        headers = {
            "apikey": self._service_role_key,
            "Authorization": f"Bearer {self._service_role_key}",
            "Accept": "application/json",
            "Content-Type": "application/json",
            "Prefer": "return=representation",
        }
        if rows is not None and row is not None:
            raise ValueError("Supabase request cannot include both rows and row")
        if rows is not None:
            body = json.dumps(rows, ensure_ascii=False)
        elif row is not None:
            body = json.dumps(row, ensure_ascii=False)
        else:
            body = None
        try:
            return self._transport(method=method, url=url, headers=headers, body=body)
        except urllib.error.HTTPError as exc:
            raise SupabaseServiceRoleError(f"Supabase request failed: table={table} status={exc.code}") from exc
        except urllib.error.URLError as exc:
            raise SupabaseServiceRoleError(f"Supabase request failed: table={table} reason=network_error") from exc


def _urllib_transport(*, method: str, url: str, headers: dict[str, str], body: str | None = None) -> Any:
    data = body.encode("utf-8") if body is not None else None
    request = urllib.request.Request(url, data=data, headers=headers, method=method)
    with urllib.request.urlopen(request, timeout=15) as response:
        raw = response.read()
    if not raw:
        return None
    return json.loads(raw.decode("utf-8"))