services/database_service.py

from supabase import create_client
import os
from datetime import datetime
from typing import Optional, List, Dict
import json

class DatabaseService:
    def __init__(self):
        self.supabase = create_client(
            os.getenv("SUPABASE_URL"),
            os.getenv("SUPABASE_KEY")
        )
    
    def check_connection(self):
        """Check database connection"""
        try:
            self.supabase.table('health_check').select('*').limit(1).execute()
            return True
        except:
            return False
    
    # User methods
    def get_user(self, user_id: str) -> Optional[Dict]:
        """Get user by ID"""
        result = self.supabase.table('users') \
            .select('*') \
            .eq('firebase_uid', user_id) \
            .execute()
        
        return result.data[0] if result.data else None
    
    def create_user(self, user_data: dict) -> Dict:
        """Create new user"""
        result = self.supabase.table('users') \
            .insert(user_data) \
            .execute()
        
        return result.data[0] if result.data else None
    
    def update_user(self, user_id: str, user_data: dict) -> Dict:
        """Update user"""
        result = self.supabase.table('users') \
            .update(user_data) \
            .eq('firebase_uid', user_id) \
            .execute()
        
        return result.data[0] if result.data else None
    
    # Analysis methods
    def save_analysis(self, user_id: str, analysis_id: str, data: dict) -> Dict:
        """Save skin analysis"""
        record = {
            'id': analysis_id,
            'user_id': user_id,
            'conditions': json.dumps(data['conditions']),
            'pins': json.dumps(data['pins']),
            'skin_tone': json.dumps(data['skin_tone']),
            'recommendations': json.dumps(data['recommendations']),
            'created_at': datetime.utcnow().isoformat()
        }
        
        result = self.supabase.table('skin_analyses') \
            .insert(record) \
            .execute()
        
        return result.data[0] if result.data else None
    
    def get_analysis(self, analysis_id: str, user_id: str) -> Optional[Dict]:
        """Get analysis by ID"""
        result = self.supabase.table('skin_analyses') \
            .select('*') \
            .eq('id', analysis_id) \
            .eq('user_id', user_id) \
            .execute()
        
        if result.data:
            analysis = result.data[0]
            # Parse JSON fields
            analysis['conditions'] = json.loads(analysis['conditions'])
            analysis['pins'] = json.loads(analysis['pins'])
            analysis['skin_tone'] = json.loads(analysis['skin_tone'])
            analysis['recommendations'] = json.loads(analysis['recommendations'])
            return analysis
        
        return None
    
    def update_analysis(self, analysis_id: str, user_id: str, data: dict) -> Dict:
        """Update analysis"""
        update_data = {
            'conditions': json.dumps(data['conditions']),
            'pins': json.dumps(data['pins']),
            'recommendations': json.dumps(data['recommendations']),
            'updated_at': datetime.utcnow().isoformat()
        }
        
        result = self.supabase.table('skin_analyses') \
            .update(update_data) \
            .eq('id', analysis_id) \
            .eq('user_id', user_id) \
            .execute()
        
        return result.data[0] if result.data else None
    
    def delete_analysis(self, analysis_id: str, user_id: str):
        """Delete analysis"""
        self.supabase.table('skin_analyses') \
            .delete() \
            .eq('id', analysis_id) \
            .eq('user_id', user_id) \
            .execute()
    
    def get_user_analyses(self, user_id: str, limit: int = 10) -> List[Dict]:
        """Get user's analysis history"""
        result = self.supabase.table('skin_analyses') \
            .select('*') \
            .eq('user_id', user_id) \
            .order('created_at', desc=True) \
            .limit(limit) \
            .execute()
        
        analyses = []
        for row in result.data:
            row['conditions'] = json.loads(row['conditions'])
            row['pins'] = json.loads(row['pins'])
            analyses.append(row)
        
        return analyses
    
    # Match history methods
    def save_match_history(self, user_id: str, matches: dict, skin_tone: dict):
        """Save cosmetic match history"""
        record = {
            'user_id': user_id,
            'matches': json.dumps(matches),
            'skin_tone': json.dumps(skin_tone),
            'created_at': datetime.utcnow().isoformat()
        }
        
        self.supabase.table('match_history') \
            .insert(record) \
            .execute()
    
    # Cart methods
    def get_cart(self, user_id: str) -> Dict:
        """Get user's cart"""
        result = self.supabase.table('cart') \
            .select('*') \
            .eq('user_id', user_id) \
            .execute()
        
        if result.data:
            cart = result.data[0]
            cart['items'] = json.loads(cart['items'])
            return cart
        
        return None
    
    def update_cart(self, user_id: str, items: list) -> Dict:
        """Update user's cart"""
        # Check if cart exists
        existing = self.get_cart(user_id)
        
        if existing:
            # Update
            result = self.supabase.table('cart') \
                .update({
                    'items': json.dumps(items),
                    'updated_at': datetime.utcnow().isoformat()
                }) \
                .eq('user_id', user_id) \
                .execute()
        else:
            # Create
            result = self.supabase.table('cart') \
                .insert({
                    'user_id': user_id,
                    'items': json.dumps(items),
                    'created_at': datetime.utcnow().isoformat()
                }) \
                .execute()
        
        return result.data[0] if result.data else None
    
    # Order methods
    def create_order(self, order_data: dict) -> Dict:
        """Create new order"""
        order_data['items'] = json.dumps(order_data.get('items', []))
        order_data['created_at'] = datetime.utcnow().isoformat()
        
        result = self.supabase.table('orders') \
            .insert(order_data) \
            .execute()
        
        return result.data[0] if result.data else None
    
    def get_user_orders(self, user_id: str) -> List[Dict]:
        """Get user's orders"""
        result = self.supabase.table('orders') \
            .select('*') \
            .eq('user_id', user_id) \
            .order('created_at', desc=True) \
            .execute()
        
        orders = []
        for row in result.data:
            row['items'] = json.loads(row['items'])
            orders.append(row)
        
        return orders
    
    # Streak methods
    def get_streak(self, user_id: str) -> Optional[Dict]:
        """Get user's streak"""
        result = self.supabase.table('streaks') \
            .select('*') \
            .eq('user_id', user_id) \
            .execute()
        
        if result.data:
            streak = result.data[0]
            streak['badges'] = json.loads(streak['badges'])
            return streak
        
        return None
    
    def update_streak(self, user_id: str, streak_data: dict) -> Dict:
        """Update user's streak"""
        existing = self.get_streak(user_id)
        
        if existing:
            result = self.supabase.table('streaks') \
                .update({
                    'current_streak': streak_data['current_streak'],
                    'longest_streak': streak_data['longest_streak'],
                    'total_days': streak_data['total_days'],
                    'last_check_in': streak_data['last_check_in'],
                    'badges': json.dumps(streak_data.get('badges', [])),
                    'updated_at': datetime.utcnow().isoformat()
                }) \
                .eq('user_id', user_id) \
                .execute()
        else:
            result = self.supabase.table('streaks') \
                .insert({
                    'user_id': user_id,
                    'current_streak': streak_data['current_streak'],
                    'longest_streak': streak_data['longest_streak'],
                    'total_days': streak_data['total_days'],
                    'last_check_in': streak_data['last_check_in'],
                    'badges': json.dumps(streak_data.get('badges', [])),
                    'created_at': datetime.utcnow().isoformat()
                }) \
                .execute()
        
        return result.data[0] if result.data else None
    
    # GDPR compliance methods
    def log_privacy_action(self, log_data: dict):
        """Log privacy-related actions"""
        self.supabase.table('privacy_logs') \
            .insert(log_data) \
            .execute()
    
    def delete_user_data(self, user_id: str):
        """Delete all user data (GDPR)"""
        tables = ['skin_analyses', 'match_history', 'cart', 'orders', 'streaks', 'privacy_logs']
        
        for table in tables:
            self.supabase.table(table) \
                .delete() \
                .eq('user_id', user_id) \
                .execute()
    
    def get_all_user_data(self, user_id: str) -> Dict:
        """Get all user data for export (GDPR)"""
        data = {
            'profile': self.get_user(user_id),
            'analyses': self.get_user_analyses(user_id, limit=100),
            'orders': self.get_user_orders(user_id),
            'cart': self.get_cart(user_id),
            'streak': self.get_streak(user_id)
        }
        
        return data