""" 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 []