services/supabase_service.py

import logging
import uuid
from typing import List, Dict, Any, Optional
from supabase import create_client, Client
from config import settings

logger = logging.getLogger("supabase_service")

# Try to initialize Supabase client
supabase_client: Optional[Client] = None
if settings.SUPABASE_URL and settings.SUPABASE_KEY:
    try:
        supabase_client = create_client(settings.SUPABASE_URL, settings.SUPABASE_KEY)
        logger.info("Supabase client initialized successfully.")
    except Exception as e:
        logger.error(f"Error initializing Supabase client: {e}")
else:
    logger.warning("Supabase URL or Key not set. Using mock in-memory database.")

# --- IN-MEMORY MOCK DATABASE ---
# Pre-populated with some mock locations in Guarenas-Guatire
MOCK_SHELTERS = [
    {
        "id": "s1",
        "name": "Refugio Polideportivo Los Naranjos",
        "capacity": 150,
        "occupancy": 45,
        "latitude": 10.4678,
        "longitude": -66.6210,
        "resources": {"food_boxes": 200, "medical_kits": 50, "water_liters": 1000}
    },
    {
        "id": "s2",
        "name": "Refugio U.E. Antonio José de Sucre",
        "capacity": 100,
        "occupancy": 80,
        "latitude": 10.4585,
        "longitude": -66.6025,
        "resources": {"food_boxes": 80, "medical_kits": 20, "water_liters": 400}
    },
    {
        "id": "s3",
        "name": "Refugio Centro Cultural Guatire",
        "capacity": 200,
        "occupancy": 110,
        "latitude": 10.4722,
        "longitude": -66.5412,
        "resources": {"food_boxes": 300, "medical_kits": 100, "water_liters": 2500}
    }
]

MOCK_DAMAGE_REPORTS = [
    {
        "id": "d1",
        "severity": "high",
        "latitude": 10.4621,
        "longitude": -66.6185,
        "photo_url": "https://images.unsplash.com/photo-1594897030264-ab7d87efc473?w=500",
        "status": "pending",
        "details": {
            "owner_name": "Juan Perez",
            "owner_contact": "+58 412 1234567",
            "exact_address": "Sector Oropeza Castillo, Calle 3, Casa Nro 12, Guarenas",
            "description": "Fallas estructurales graves en vigas principales y grietas en paredes de carga."
        }
    },
    {
        "id": "d2",
        "severity": "collapsed",
        "latitude": 10.4690,
        "longitude": -66.6102,
        "photo_url": "https://images.unsplash.com/photo-1580618672591-eb180b1a973f?w=500",
        "status": "verified",
        "details": {
            "owner_name": "Maria Rodriguez",
            "owner_contact": "+58 414 7654321",
            "exact_address": "Urbanización Vicente Emilio Sojo, Bloque 4, Guarenas",
            "description": "Derrumbe parcial del segundo piso de la vivienda unifamiliar."
        }
    }
]


# --- SERVICE INTERFACE FUNCTIONS ---

def save_damage_report(
    severity: str,
    latitude: float,
    longitude: float,
    photo_url: Optional[str],
    status: str,
    owner_name: str,
    owner_contact: str,
    exact_address: str,
    description: Optional[str]
) -> Dict[str, Any]:
    """
    Saves a damage report. Connects to Supabase if available, otherwise writes to in-memory store.
    """
    report_id = str(uuid.uuid4())
    
    if supabase_client:
        try:
            # PostGIS expects geometry as 'POINT(lng lat)' -> WGS84 EPSG:4326
            point_wkt = f"POINT({longitude} {latitude})"
            
            # 1. Insert into public damage_reports
            report_data = {
                "id": report_id,
                "severity": severity,
                "location": point_wkt,
                "photo_url": photo_url,
                "status": status
            }
            
            res_report = supabase_client.table("damage_reports").insert(report_data).execute()
            
            # 2. Insert private details
            details_data = {
                "id": report_id,
                "owner_name": owner_name,
                "owner_contact": owner_contact,
                "exact_address": exact_address,
                "description": description
            }
            res_details = supabase_client.table("damage_details").insert(details_data).execute()
            
            return {
                "id": report_id,
                "severity": severity,
                "latitude": latitude,
                "longitude": longitude,
                "photo_url": photo_url,
                "status": status,
                "details": details_data
            }
        except Exception as e:
            logger.error(f"Error saving to Supabase: {e}. Falling back to in-memory storage.")
            # Fallback to in-memory if DB fails during emergency
    
    # In-memory save
    new_report = {
        "id": report_id,
        "severity": severity,
        "latitude": latitude,
        "longitude": longitude,
        "photo_url": photo_url,
        "status": status,
        "details": {
            "owner_name": owner_name,
            "owner_contact": owner_contact,
            "exact_address": exact_address,
            "description": description
        }
    }
    MOCK_DAMAGE_REPORTS.append(new_report)
    logger.info(f"Saved damage report to in-memory store: {report_id}")
    return new_report


def get_damage_reports_in_bbox(
    min_lat: float, min_lng: float, max_lat: float, max_lng: float
) -> List[Dict[str, Any]]:
    """
    Retrieves public damage reports within a bounding box.
    """
    if supabase_client:
        try:
            # We can run an RPC or raw spatial query in Supabase.
            # PostgREST filter syntax: ST_Contains bounding box
            # To simplify, we can call an RPC function defined in our database, 
            # or do a bounding box query using PostgREST filters if available,
            # or query all and filter in Python for small datasets, or write custom RPC.
            # Let's use RPC 'get_damage_in_bbox' or query with PostgREST raw select.
            # PostgREST allows filtering on geometry.
            # For robustness, we will fetch and parse, or fall back if query syntax fails.
            
            # Let's request coordinates as lat/lng from PostGIS:
            # select('id, severity, photo_url, status, location')
            # By default PostGIS geometry returns GeoJSON or WKB.
            res = supabase_client.rpc(
                "get_damage_in_bbox", 
                {"min_lat": min_lat, "min_lng": min_lng, "max_lat": max_lat, "max_lng": max_lng}
            ).execute()
            return res.data
        except Exception as e:
            logger.error(f"Supabase spatial query failed: {e}. Filtering in-memory.")

    # In-memory bounding box filtering
    filtered = []
    for r in MOCK_DAMAGE_REPORTS:
        lat, lng = r["latitude"], r["longitude"]
        if min_lat <= lat <= max_lat and min_lng <= lng <= max_lng:
            # Strip private details unless authenticated
            # Return only public data for public map
            filtered.append({
                "id": r["id"],
                "severity": r["severity"],
                "latitude": lat,
                "longitude": lng,
                "photo_url": r["photo_url"],
                "status": r["status"]
            })
    return filtered


def get_shelters_in_bbox(
    min_lat: float, min_lng: float, max_lat: float, max_lng: float
) -> List[Dict[str, Any]]:
    """
    Retrieves shelters within a bounding box.
    """
    if supabase_client:
        try:
            res = supabase_client.rpc(
                "get_shelters_in_bbox", 
                {"min_lat": min_lat, "min_lng": min_lng, "max_lat": max_lat, "max_lng": max_lng}
            ).execute()
            return res.data
        except Exception as e:
            logger.error(f"Supabase spatial query for shelters failed: {e}. Filtering in-memory.")

    # In-memory bounding box filtering
    filtered = []
    for s in MOCK_SHELTERS:
        lat, lng = s["latitude"], s["longitude"]
        if min_lat <= lat <= max_lat and min_lng <= lng <= max_lng:
            filtered.append(s)
    return filtered


def save_shelter(
    name: str, capacity: int, occupancy: int, latitude: float, longitude: float, resources: Dict[str, Any]
) -> Dict[str, Any]:
    """
    Saves a new shelter.
    """
    shelter_id = str(uuid.uuid4())
    if supabase_client:
        try:
            point_wkt = f"POINT({longitude} {latitude})"
            shelter_data = {
                "id": shelter_id,
                "name": name,
                "capacity": capacity,
                "occupancy": occupancy,
                "location": point_wkt,
                "resources": resources
            }
            supabase_client.table("shelters").insert(shelter_data).execute()
            return shelter_data
        except Exception as e:
            logger.error(f"Error saving shelter to Supabase: {e}")
            
    new_shelter = {
        "id": shelter_id,
        "name": name,
        "capacity": capacity,
        "occupancy": occupancy,
        "latitude": latitude,
        "longitude": longitude,
        "resources": resources
    }
    MOCK_SHELTERS.append(new_shelter)
    return new_shelter