Spaces:
Runtime error
Runtime error
| """ | |
| Supabase database implementation for TreeTrack | |
| """ | |
| import json | |
| import logging | |
| from typing import Dict, List, Optional, Any | |
| from supabase_client import get_service_client | |
| logger = logging.getLogger(__name__) | |
| class SupabaseDatabase: | |
| """Uses service_role client to bypass RLS. Authorization handled by FastAPI.""" | |
| def __init__(self): | |
| try: | |
| self.client = get_service_client() | |
| self.connected = True | |
| logger.info("SupabaseDatabase initialized with service_role client") | |
| except ValueError as e: | |
| logger.warning(f"Supabase not configured: {e}") | |
| self.client = None | |
| self.connected = False | |
| def _check_connection(self): | |
| if not self.connected or not self.client: | |
| raise RuntimeError("Database not connected. Please configure Supabase credentials.") | |
| def initialize_database(self) -> bool: | |
| try: | |
| self.client.table('trees').select("id").limit(1).execute() | |
| logger.info("Trees table verified") | |
| self._ensure_telemetry_table() | |
| return True | |
| except Exception as e: | |
| logger.error(f"Failed to verify/initialize database: {e}") | |
| return False | |
| def test_connection(self) -> bool: | |
| from supabase_client import test_supabase_connection | |
| return test_supabase_connection() | |
| async def create_tree(self, tree_data: Dict[str, Any]) -> Dict[str, Any]: | |
| self._check_connection() | |
| try: | |
| result = self.client.table('trees').insert(tree_data).execute() | |
| if result.data: | |
| created_tree = result.data[0] | |
| logger.info(f"Created tree with ID: {created_tree.get('id')}") | |
| return created_tree | |
| else: | |
| raise Exception("No data returned from insert operation") | |
| except Exception as e: | |
| logger.error(f"Error creating tree: {e}") | |
| raise | |
| async def get_trees(self, limit: int = 100, offset: int = 0, | |
| species: str = None, health_status: str = None) -> List[Dict[str, Any]]: | |
| self._check_connection() | |
| try: | |
| query = self.client.table('trees').select("*") | |
| if species: | |
| query = query.ilike('scientific_name', f'%{species}%') | |
| result = query.order('updated_at', desc=True) \ | |
| .order('created_at', desc=True) \ | |
| .range(offset, offset + limit - 1) \ | |
| .execute() | |
| logger.info(f"Retrieved {len(result.data)} trees") | |
| return result.data | |
| except Exception as e: | |
| logger.error(f"Error retrieving trees: {e}") | |
| raise | |
| async def get_tree(self, tree_id: int) -> Optional[Dict[str, Any]]: | |
| self._check_connection() | |
| try: | |
| result = self.client.table('trees') \ | |
| .select("*") \ | |
| .eq('id', tree_id) \ | |
| .execute() | |
| if result.data: | |
| return result.data[0] | |
| return None | |
| except Exception as e: | |
| logger.error(f"Error retrieving tree {tree_id}: {e}") | |
| raise | |
| async def update_tree(self, tree_id: int, tree_data: Dict[str, Any]) -> Dict[str, Any]: | |
| self._check_connection() | |
| try: | |
| update_data = {k: v for k, v in tree_data.items() if k != 'id'} | |
| result = self.client.table('trees') \ | |
| .update(update_data) \ | |
| .eq('id', tree_id) \ | |
| .execute() | |
| if result.data: | |
| updated_tree = result.data[0] | |
| logger.info(f"Updated tree with ID: {tree_id}") | |
| return updated_tree | |
| else: | |
| raise Exception(f"Tree with ID {tree_id} not found") | |
| except Exception as e: | |
| logger.error(f"Error updating tree {tree_id}: {e}") | |
| raise | |
| async def delete_tree(self, tree_id: int) -> bool: | |
| self._check_connection() | |
| try: | |
| result = self.client.table('trees') \ | |
| .delete() \ | |
| .eq('id', tree_id) \ | |
| .execute() | |
| logger.info(f"Deleted tree with ID: {tree_id}") | |
| return True | |
| except Exception as e: | |
| logger.error(f"Error deleting tree {tree_id}: {e}") | |
| raise | |
| def get_tree_count(self) -> int: | |
| try: | |
| result = self.client.table('trees') \ | |
| .select("id", count="exact") \ | |
| .execute() | |
| return result.count if result.count is not None else 0 | |
| except Exception as e: | |
| logger.error(f"Error getting tree count: {e}") | |
| return 0 | |
| def get_species_distribution(self, limit: int = 20) -> List[Dict[str, Any]]: | |
| try: | |
| try: | |
| result = self.client.rpc('get_species_distribution', {'record_limit': limit}).execute() | |
| if result.data: | |
| return [{"species": row["species"], "count": row["count"]} for row in result.data] | |
| except Exception as rpc_error: | |
| logger.info(f"RPC not available, using Python aggregation") | |
| result = self.client.table('trees') \ | |
| .select("scientific_name") \ | |
| .not_.is_('scientific_name', 'null') \ | |
| .neq('scientific_name', '') \ | |
| .execute() | |
| species_count = {} | |
| for tree in result.data: | |
| species = tree.get('scientific_name', 'Unknown') | |
| species_count[species] = species_count.get(species, 0) + 1 | |
| distribution = [ | |
| {"species": species, "count": count} | |
| for species, count in species_count.items() | |
| ] | |
| distribution.sort(key=lambda x: x['count'], reverse=True) | |
| return distribution[:limit] | |
| except Exception as e: | |
| logger.error(f"Error getting species distribution: {e}") | |
| return [] | |
| def get_health_distribution(self) -> List[Dict[str, Any]]: | |
| try: | |
| return [] | |
| except Exception as e: | |
| logger.error(f"Error getting health distribution: {e}") | |
| return [] | |
| def get_average_measurements(self) -> Dict[str, float]: | |
| try: | |
| result = self.client.table('trees') \ | |
| .select("height, width") \ | |
| .not_.is_('height', 'null') \ | |
| .not_.is_('width', 'null') \ | |
| .execute() | |
| if not result.data: | |
| return {"average_height": 0, "average_diameter": 0} | |
| heights = [float(tree['height']) for tree in result.data if tree['height']] | |
| widths = [float(tree['width']) for tree in result.data if tree['width']] | |
| avg_height = sum(heights) / len(heights) if heights else 0 | |
| avg_width = sum(widths) / len(widths) if widths else 0 | |
| return { | |
| "average_height": round(avg_height, 2), | |
| "average_diameter": round(avg_width, 2) | |
| } | |
| except Exception as e: | |
| logger.error(f"Error getting average measurements: {e}") | |
| return {"average_height": 0, "average_diameter": 0} | |
| def backup_database(self) -> bool: | |
| return True | |
| def restore_database(self) -> bool: | |
| return True | |
| def _ensure_telemetry_table(self) -> None: | |
| try: | |
| self.client.table('telemetry_events').select('id').limit(1).execute() | |
| logger.info("Telemetry table verified") | |
| except Exception as e: | |
| logger.info(f"telemetry_events table not accessible: {e}") | |
| pass | |
| def log_telemetry(self, event: Dict[str, Any]) -> bool: | |
| try: | |
| payload = { | |
| 'event_type': event.get('event_type'), | |
| 'status': event.get('status'), | |
| 'metadata': event.get('metadata'), | |
| 'username': (event.get('user') or {}).get('username'), | |
| 'role': (event.get('user') or {}).get('role'), | |
| 'client': event.get('client'), | |
| 'timestamp': event.get('timestamp') | |
| } | |
| self.client.table('telemetry_events').insert(payload).execute() | |
| logger.info("Telemetry event inserted into Supabase") | |
| return True | |
| except Exception as e: | |
| logger.error(f"Failed to log telemetry: {e}") | |
| return False | |
| def get_recent_telemetry(self, limit: int = 100) -> List[Dict[str, Any]]: | |
| try: | |
| result = self.client.table('telemetry_events') \ | |
| .select('*') \ | |
| .order('timestamp', desc=True) \ | |
| .limit(limit) \ | |
| .execute() | |
| return result.data or [] | |
| except Exception as e: | |
| logger.error(f"Failed to fetch telemetry: {e}") | |
| return [] | |