#!/usr/bin/env python3
"""
Weather Chatbot Notifications Module
====================================

Author: RSK World (https://rskworld.in)
Founded by: Molla Samser
Designer & Tester: Rima Khatun
Contact: +91 93305 39277, hello@rskworld.in, support@rskworld.in
Location: Nutanhat, Mongolkote, Purba Burdwan, West Bengal, India, 713147
Year: 2026

Description: Weather notifications and alerts system
"""

import json
import smtplib
import requests
from datetime import datetime, timedelta
from typing import Dict, List, Optional, Callable
from email.mime.text import MimeText
from email.mime.multipart import MimeMultipart
from dataclasses import dataclass
from enum import Enum

class NotificationType(Enum):
    """Notification types"""
    EMAIL = "email"
    SMS = "sms"
    WEBHOOK = "webhook"
    PUSH = "push"
    IN_APP = "in_app"

class AlertSeverity(Enum):
    """Alert severity levels"""
    LOW = "low"
    MEDIUM = "medium"
    HIGH = "high"
    CRITICAL = "critical"

@dataclass
class Notification:
    """Notification data structure"""
    user_id: int
    notification_type: NotificationType
    title: str
    message: str
    severity: AlertSeverity
    city: str
    alert_data: Dict
    timestamp: datetime
    delivery_methods: List[str]
    is_sent: bool = False
    sent_at: Optional[datetime] = None

class WeatherNotificationSystem:
    """Advanced weather notification and alert system"""
    
    def __init__(self, db_manager=None, email_config: Dict = None, sms_config: Dict = None, push_config: Dict = None):
        self.db_manager = db_manager
        self.email_config = email_config or {}
        self.sms_config = sms_config or {}
        self.push_config = push_config or {}
        self.notification_queue = []
        self.alert_subscriptions = {}
        self.notification_handlers = {
            NotificationType.EMAIL: self._send_email_notification,
            NotificationType.SMS: self._send_sms_notification,
            NotificationType.WEBHOOK: self._send_webhook_notification,
            NotificationType.PUSH: self._send_push_notification,
            NotificationType.IN_APP: self._store_in_app_notification
        }
    
    def subscribe_to_alerts(self, user_id: int, city: str, alert_types: List[str], 
                          delivery_methods: List[str], thresholds: Dict = None) -> Dict:
        """
        Subscribe user to weather alerts for a specific city.
        
        Args:
            user_id: User ID
            city: City name
            alert_types: Types of alerts to subscribe to
            delivery_methods: Preferred delivery methods
            thresholds: Custom alert thresholds
            
        Returns:
            Subscription result
        """
        try:
            subscription_id = f"{user_id}_{city}_{datetime.now().timestamp()}"
            
            subscription = {
                'subscription_id': subscription_id,
                'user_id': user_id,
                'city': city,
                'alert_types': alert_types,
                'delivery_methods': delivery_methods,
                'thresholds': thresholds or self._get_default_thresholds(),
                'created_at': datetime.now().isoformat(),
                'is_active': True
            }
            
            # Store subscription
            if self.db_manager:
                result = self._store_subscription(subscription)
                if not result['success']:
                    return result
            
            # Add to memory subscriptions
            key = f"{user_id}_{city}"
            self.alert_subscriptions[key] = subscription
            
            return {
                'success': True,
                'subscription_id': subscription_id,
                'message': f'Successfully subscribed to alerts for {city}',
                'subscription': subscription
            }
            
        except Exception as e:
            return {'success': False, 'error': str(e)}
    
    def unsubscribe_from_alerts(self, user_id: int, city: str = None, subscription_id: str = None) -> Dict:
        """
        Unsubscribe user from weather alerts.
        
        Args:
            user_id: User ID
            city: City name (optional)
            subscription_id: Specific subscription ID (optional)
            
        Returns:
            Unsubscription result
        """
        try:
            if subscription_id:
                # Unsubscribe specific subscription
                if self.db_manager:
                    result = self._deactivate_subscription(subscription_id)
                    if not result['success']:
                        return result
                
                # Remove from memory
                self.alert_subscriptions = {
                    k: v for k, v in self.alert_subscriptions.items() 
                    if v.get('subscription_id') != subscription_id
                }
                
                return {'success': True, 'message': 'Unsubscribed successfully'}
            
            elif city:
                # Unsubscribe from specific city
                key = f"{user_id}_{city}"
                if key in self.alert_subscriptions:
                    subscription_id = self.alert_subscriptions[key]['subscription_id']
                    
                    if self.db_manager:
                        result = self._deactivate_subscription(subscription_id)
                        if not result['success']:
                            return result
                    
                    del self.alert_subscriptions[key]
                    return {'success': True, 'message': f'Unsubscribed from {city} alerts'}
                else:
                    return {'success': False, 'error': 'No subscription found for this city'}
            
            else:
                # Unsubscribe from all alerts for user
                user_subscriptions = {
                    k: v for k, v in self.alert_subscriptions.items() 
                    if v.get('user_id') == user_id
                }
                
                if self.db_manager:
                    for subscription in user_subscriptions.values():
                        self._deactivate_subscription(subscription['subscription_id'])
                
                # Remove from memory
                self.alert_subscriptions = {
                    k: v for k, v in self.alert_subscriptions.items() 
                    if v.get('user_id') != user_id
                }
                
                return {'success': True, 'message': 'Unsubscribed from all alerts'}
                
        except Exception as e:
            return {'success': False, 'error': str(e)}
    
    def create_weather_alert(self, city: str, alert_data: Dict, severity: AlertSeverity = AlertSeverity.MEDIUM) -> Dict:
        """
        Create and send weather alert to subscribed users.
        
        Args:
            city: City name
            alert_data: Weather alert data
            severity: Alert severity
            
        Returns:
            Alert creation result
        """
        try:
            # Find subscribers for this city
            city_subscribers = [
                sub for sub in self.alert_subscriptions.values() 
                if sub['city'].lower() == city.lower() and sub['is_active']
            ]
            
            if not city_subscribers:
                return {
                    'success': True,
                    'message': f'No subscribers found for {city}',
                    'notifications_sent': 0
                }
            
            notifications_sent = 0
            failed_notifications = []
            
            for subscription in city_subscribers:
                # Check if user wants this type of alert
                alert_type = self._determine_alert_type(alert_data)
                if alert_type not in subscription['alert_types']:
                    continue
                
                # Check thresholds
                if not self._check_alert_thresholds(alert_data, subscription['thresholds']):
                    continue
                
                # Create notification
                notification = Notification(
                    user_id=subscription['user_id'],
                    notification_type=self._get_primary_notification_type(subscription['delivery_methods']),
                    title=self._generate_alert_title(alert_data, severity),
                    message=self._generate_alert_message(city, alert_data, severity),
                    severity=severity,
                    city=city,
                    alert_data=alert_data,
                    timestamp=datetime.now(),
                    delivery_methods=subscription['delivery_methods']
                )
                
                # Send notification
                send_result = self._send_notification(notification)
                
                if send_result['success']:
                    notifications_sent += 1
                else:
                    failed_notifications.append({
                        'user_id': subscription['user_id'],
                        'error': send_result['error']
                    })
            
            # Store alert in database
            if self.db_manager:
                self._store_alert(city, alert_data, severity)
            
            return {
                'success': True,
                'message': f'Alert processed for {city}',
                'total_subscribers': len(city_subscribers),
                'notifications_sent': notifications_sent,
                'failed_notifications': failed_notifications
            }
            
        except Exception as e:
            return {'success': False, 'error': str(e)}
    
    def create_custom_notification(self, user_id: int, title: str, message: str, 
                                 notification_type: NotificationType, severity: AlertSeverity = AlertSeverity.LOW) -> Dict:
        """
        Create custom notification for a user.
        
        Args:
            user_id: User ID
            title: Notification title
            message: Notification message
            notification_type: Type of notification
            severity: Alert severity
            
        Returns:
            Notification creation result
        """
        try:
            notification = Notification(
                user_id=user_id,
                notification_type=notification_type,
                title=title,
                message=message,
                severity=severity,
                city='',
                alert_data={},
                timestamp=datetime.now(),
                delivery_methods=[notification_type.value]
            )
            
            result = self._send_notification(notification)
            
            return {
                'success': result['success'],
                'message': 'Custom notification created' if result['success'] else 'Failed to send notification',
                'notification_id': f"custom_{user_id}_{datetime.now().timestamp()}",
                'error': result.get('error')
            }
            
        except Exception as e:
            return {'success': False, 'error': str(e)}
    
    def get_user_notifications(self, user_id: int, limit: int = 50, unread_only: bool = False) -> Dict:
        """
        Get notifications for a user.
        
        Args:
            user_id: User ID
            limit: Maximum number of notifications
            unread_only: Get only unread notifications
            
        Returns:
            User notifications
        """
        try:
            if not self.db_manager:
                return {'error': 'Database not configured'}
            
            with self.db_manager.get_connection() as conn:
                cursor = conn.cursor()
                
                query = '''
                    SELECT * FROM notifications
                    WHERE user_id = ?
                '''
                params = [user_id]
                
                if unread_only:
                    query += ' AND is_read = 0'
                
                query += ' ORDER BY timestamp DESC LIMIT ?'
                params.append(limit)
                
                cursor.execute(query, params)
                rows = cursor.fetchall()
                
                columns = [desc[0] for desc in cursor.description]
                notifications = [dict(zip(columns, row)) for row in rows]
                
                return {
                    'success': True,
                    'notifications': notifications,
                    'count': len(notifications)
                }
                
        except Exception as e:
            return {'success': False, 'error': str(e)}
    
    def mark_notification_read(self, user_id: int, notification_id: int) -> Dict:
        """Mark notification as read"""
        try:
            if not self.db_manager:
                return {'success': False, 'error': 'Database not configured'}
            
            with self.db_manager.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    UPDATE notifications SET is_read = 1, read_at = ?
                    WHERE id = ? AND user_id = ?
                ''', (datetime.now(), notification_id, user_id))
                
                conn.commit()
                
                return {
                    'success': True,
                    'message': 'Notification marked as read'
                }
                
        except Exception as e:
            return {'success': False, 'error': str(e)}
    
    def _send_notification(self, notification: Notification) -> Dict:
        """Send notification using appropriate handler"""
        try:
            handler = self.notification_handlers.get(notification.notification_type)
            
            if not handler:
                return {'success': False, 'error': f'No handler for {notification.notification_type}'}
            
            result = handler(notification)
            
            if result['success']:
                notification.is_sent = True
                notification.sent_at = datetime.now()
                
                # Store notification in database
                if self.db_manager:
                    self._store_notification(notification)
            
            return result
            
        except Exception as e:
            return {'success': False, 'error': str(e)}
    
    def _send_email_notification(self, notification: Notification) -> Dict:
        """Send email notification"""
        try:
            if not self.email_config:
                return {'success': False, 'error': 'Email not configured'}
            
            # Get user email from database
            user_email = self._get_user_email(notification.user_id)
            if not user_email:
                return {'success': False, 'error': 'User email not found'}
            
            # Create email
            msg = MimeMultipart()
            msg['From'] = self.email_config.get('sender_email')
            msg['To'] = user_email
            msg['Subject'] = f"Weather Alert: {notification.title}"
            
            # Email body
            body = f"""
Weather Alert from RSK World Chatbot

{notification.title}

{notification.message}

City: {notification.city}
Severity: {notification.severity.value}
Time: {notification.timestamp.strftime('%Y-%m-%d %H:%M:%S')}

---
This is an automated weather alert from Weather Chatbot.
To manage your alert preferences, visit: https://rskworld.in/weather-chatbot

© 2026 RSK World. All rights reserved.
Contact: hello@rskworld.in | +91 93305 39277
            """
            
            msg.attach(MimeText(body, 'plain'))
            
            # Send email
            with smtplib.SMTP(self.email_config.get('smtp_server'), self.email_config.get('smtp_port', 587)) as server:
                server.starttls()
                server.login(self.email_config.get('sender_email'), self.email_config.get('sender_password'))
                server.send_message(msg)
            
            return {'success': True, 'message': 'Email sent successfully'}
            
        except Exception as e:
            return {'success': False, 'error': str(e)}
    
    def _send_sms_notification(self, notification: Notification) -> Dict:
        """Send SMS notification"""
        try:
            if not self.sms_config:
                return {'success': False, 'error': 'SMS not configured'}
            
            # Get user phone number from database
            user_phone = self._get_user_phone(notification.user_id)
            if not user_phone:
                return {'success': False, 'error': 'User phone number not found'}
            
            # SMS message (keep it short)
            sms_message = f"Weather Alert: {notification.title}. {notification.message}. City: {notification.city}. -RSK World"
            
            # Send SMS using service (example with Twilio)
            if self.sms_config.get('service') == 'twilio':
                try:
                    from twilio.rest import Client
                    
                    client = Client(self.sms_config.get('account_sid'), self.sms_config.get('auth_token'))
                    message = client.messages.create(
                        body=sms_message,
                        from_=self.sms_config.get('twilio_number'),
                        to=user_phone
                    )
                    
                    return {'success': True, 'message': 'SMS sent successfully', 'message_id': message.sid}
                except ImportError:
                    return {'success': False, 'error': 'Twilio package not installed. Install with: pip install twilio'}
            
            else:
                # Generic SMS service implementation
                return {'success': False, 'error': 'SMS service not implemented'}
            
        except Exception as e:
            return {'success': False, 'error': str(e)}
    
    def _send_webhook_notification(self, notification: Notification) -> Dict:
        """Send webhook notification"""
        try:
            # Get user webhook URL from database
            webhook_url = self._get_user_webhook(notification.user_id)
            if not webhook_url:
                return {'success': False, 'error': 'User webhook URL not found'}
            
            # Prepare webhook payload
            payload = {
                'notification_id': f"notif_{notification.user_id}_{datetime.now().timestamp()}",
                'user_id': notification.user_id,
                'title': notification.title,
                'message': notification.message,
                'severity': notification.severity.value,
                'city': notification.city,
                'timestamp': notification.timestamp.isoformat(),
                'alert_data': notification.alert_data
            }
            
            # Send webhook
            response = requests.post(webhook_url, json=payload, timeout=10)
            response.raise_for_status()
            
            return {'success': True, 'message': 'Webhook sent successfully'}
            
        except Exception as e:
            return {'success': False, 'error': str(e)}
    
    def _send_push_notification(self, notification: Notification) -> Dict:
        """Send push notification"""
        try:
            # Get user push tokens from database
            push_tokens = self._get_user_push_tokens(notification.user_id)
            if not push_tokens:
                return {'success': False, 'error': 'No push tokens found for user'}
            
            # Prepare push notification payload
            payload = {
                'title': notification.title,
                'body': notification.message,
                'data': {
                    'type': 'weather_alert',
                    'city': notification.city,
                    'severity': notification.severity.value,
                    'timestamp': notification.timestamp.isoformat()
                }
            }
            
            # Send push notifications (example with Firebase)
            if self.push_config and self.push_config.get('service') == 'firebase':
                try:
                    from firebase_admin import messaging
                    
                    message = messaging.MulticastMessage(
                        notification=messaging.Notification(
                            title=payload['title'],
                            body=payload['body']
                        ),
                        data=payload['data'],
                        tokens=push_tokens
                    )
                    
                    response = messaging.send_multicast(message)
                    
                    return {
                        'success': True,
                        'message': f'Push notifications sent: {response.success_count}',
                        'failed_count': response.failure_count
                    }
                except ImportError:
                    return {'success': False, 'error': 'Firebase Admin package not installed. Install with: pip install firebase-admin'}
            
            else:
                return {'success': False, 'error': 'Push service not configured'}
            
        except Exception as e:
            return {'success': False, 'error': str(e)}
    
    def _store_in_app_notification(self, notification: Notification) -> Dict:
        """Store in-app notification"""
        try:
            if not self.db_manager:
                return {'success': False, 'error': 'Database not configured'}
            
            with self.db_manager.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    INSERT INTO notifications (
                        user_id, title, message, severity, city,
                        alert_data, notification_type, is_read, timestamp
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
                ''', (
                    notification.user_id,
                    notification.title,
                    notification.message,
                    notification.severity.value,
                    notification.city,
                    json.dumps(notification.alert_data),
                    notification.notification_type.value,
                    False,
                    notification.timestamp
                ))
                
                conn.commit()
                
                return {'success': True, 'message': 'In-app notification stored'}
                
        except Exception as e:
            return {'success': False, 'error': str(e)}
    
    def _get_default_thresholds(self) -> Dict:
        """Get default alert thresholds"""
        return {
            'temperature_high': 35.0,  # Celsius
            'temperature_low': -5.0,
            'humidity_high': 85.0,    # Percentage
            'wind_speed_high': 15.0,  # m/s
            'pressure_low': 980.0,    # hPa
            'visibility_low': 1.0     # km
        }
    
    def _determine_alert_type(self, alert_data: Dict) -> str:
        """Determine alert type from weather data"""
        temp = alert_data.get('temperature', 0)
        wind_speed = alert_data.get('wind_speed', 0)
        humidity = alert_data.get('humidity', 0)
        description = alert_data.get('description', '').lower()
        
        if temp > 35:
            return 'high_temperature'
        elif temp < -5:
            return 'low_temperature'
        elif wind_speed > 15:
            return 'high_wind'
        elif humidity > 85:
            return 'high_humidity'
        elif 'rain' in description:
            return 'rain'
        elif 'snow' in description:
            return 'snow'
        elif 'storm' in description or 'thunder' in description:
            return 'storm'
        else:
            return 'general'
    
    def _check_alert_thresholds(self, alert_data: Dict, thresholds: Dict) -> bool:
        """Check if weather data meets alert thresholds"""
        temp = alert_data.get('temperature', 0)
        wind_speed = alert_data.get('wind_speed', 0)
        humidity = alert_data.get('humidity', 0)
        pressure = alert_data.get('pressure', 0)
        visibility = alert_data.get('visibility', 0)
        
        # Check against thresholds
        if temp > thresholds.get('temperature_high', 35):
            return True
        if temp < thresholds.get('temperature_low', -5):
            return True
        if wind_speed > thresholds.get('wind_speed_high', 15):
            return True
        if humidity > thresholds.get('humidity_high', 85):
            return True
        if pressure < thresholds.get('pressure_low', 980):
            return True
        if visibility < thresholds.get('visibility_low', 1):
            return True
        
        return False
    
    def _generate_alert_title(self, alert_data: Dict, severity: AlertSeverity) -> str:
        """Generate alert title"""
        alert_type = self._determine_alert_type(alert_data)
        
        titles = {
            'high_temperature': 'High Temperature Alert',
            'low_temperature': 'Low Temperature Alert',
            'high_wind': 'Strong Wind Alert',
            'high_humidity': 'High Humidity Alert',
            'rain': 'Rain Alert',
            'snow': 'Snow Alert',
            'storm': 'Storm Alert',
            'general': 'Weather Alert'
        }
        
        base_title = titles.get(alert_type, 'Weather Alert')
        
        if severity == AlertSeverity.CRITICAL:
            return f"🚨 {base_title}"
        elif severity == AlertSeverity.HIGH:
            return f"⚠️ {base_title}"
        else:
            return f"ℹ️ {base_title}"
    
    def _generate_alert_message(self, city: str, alert_data: Dict, severity: AlertSeverity) -> str:
        """Generate alert message"""
        temp = alert_data.get('temperature', 0)
        description = alert_data.get('description', 'Unknown conditions')
        
        message = f"Weather alert for {city}: {description}"
        
        if temp != 0:
            message += f" with temperature {temp}°C"
        
        if severity == AlertSeverity.CRITICAL:
            message += ". This is a critical weather alert. Please take immediate precautions."
        elif severity == AlertSeverity.HIGH:
            message += ". Please exercise caution and stay updated."
        
        return message
    
    def _get_primary_notification_type(self, delivery_methods: List[str]) -> NotificationType:
        """Get primary notification type from delivery methods"""
        type_mapping = {
            'email': NotificationType.EMAIL,
            'sms': NotificationType.SMS,
            'webhook': NotificationType.WEBHOOK,
            'push': NotificationType.PUSH,
            'in_app': NotificationType.IN_APP
        }
        
        for method in delivery_methods:
            if method in type_mapping:
                return type_mapping[method]
        
        return NotificationType.IN_APP  # Default fallback
    
    def _get_user_email(self, user_id: int) -> Optional[str]:
        """Get user email from database"""
        if not self.db_manager:
            return None
        
        try:
            with self.db_manager.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute('SELECT email FROM users WHERE id = ?', (user_id,))
                result = cursor.fetchone()
                return result[0] if result else None
        except:
            return None
    
    def _get_user_phone(self, user_id: int) -> Optional[str]:
        """Get user phone number from database"""
        if not self.db_manager:
            return None
        
        try:
            with self.db_manager.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute('SELECT phone FROM users WHERE id = ?', (user_id,))
                result = cursor.fetchone()
                return result[0] if result else None
        except:
            return None
    
    def _get_user_webhook(self, user_id: int) -> Optional[str]:
        """Get user webhook URL from database"""
        if not self.db_manager:
            return None
        
        try:
            with self.db_manager.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute('SELECT webhook_url FROM user_preferences WHERE user_id = ?', (user_id,))
                result = cursor.fetchone()
                return result[0] if result else None
        except:
            return None
    
    def _get_user_push_tokens(self, user_id: int) -> List[str]:
        """Get user push notification tokens"""
        if not self.db_manager:
            return []
        
        try:
            with self.db_manager.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute('SELECT token FROM push_tokens WHERE user_id = ? AND is_active = 1', (user_id,))
                results = cursor.fetchall()
                return [result[0] for result in results]
        except:
            return []
    
    def _store_subscription(self, subscription: Dict) -> Dict:
        """Store subscription in database"""
        if not self.db_manager:
            return {'success': False, 'error': 'Database not configured'}
        
        try:
            with self.db_manager.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    INSERT INTO alert_subscriptions (
                        subscription_id, user_id, city, alert_types,
                        delivery_methods, thresholds, created_at, is_active
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                ''', (
                    subscription['subscription_id'],
                    subscription['user_id'],
                    subscription['city'],
                    json.dumps(subscription['alert_types']),
                    json.dumps(subscription['delivery_methods']),
                    json.dumps(subscription['thresholds']),
                    subscription['created_at'],
                    subscription['is_active']
                ))
                
                conn.commit()
                return {'success': True}
                
        except Exception as e:
            return {'success': False, 'error': str(e)}
    
    def _deactivate_subscription(self, subscription_id: str) -> Dict:
        """Deactivate subscription in database"""
        if not self.db_manager:
            return {'success': False, 'error': 'Database not configured'}
        
        try:
            with self.db_manager.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    UPDATE alert_subscriptions SET is_active = 0
                    WHERE subscription_id = ?
                ''', (subscription_id,))
                
                conn.commit()
                return {'success': True}
                
        except Exception as e:
            return {'success': False, 'error': str(e)}
    
    def _store_alert(self, city: str, alert_data: Dict, severity: AlertSeverity) -> Dict:
        """Store alert in database"""
        if not self.db_manager:
            return {'success': False, 'error': 'Database not configured'}
        
        try:
            with self.db_manager.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    INSERT INTO weather_alerts (
                        city, alert_type, event, description, severity,
                        created_at, is_active
                    ) VALUES (?, ?, ?, ?, ?, ?, ?)
                ''', (
                    city,
                    self._determine_alert_type(alert_data),
                    alert_data.get('description', 'Weather Alert'),
                    json.dumps(alert_data),
                    severity.value,
                    datetime.now(),
                    True
                ))
                
                conn.commit()
                return {'success': True}
                
        except Exception as e:
            return {'success': False, 'error': str(e)}
    
    def _store_notification(self, notification: Notification) -> Dict:
        """Store notification in database"""
        if not self.db_manager:
            return {'success': False, 'error': 'Database not configured'}
        
        try:
            with self.db_manager.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    INSERT INTO notifications (
                        user_id, title, message, severity, city,
                        alert_data, notification_type, is_sent, sent_at, timestamp
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                ''', (
                    notification.user_id,
                    notification.title,
                    notification.message,
                    notification.severity.value,
                    notification.city,
                    json.dumps(notification.alert_data),
                    notification.notification_type.value,
                    notification.is_sent,
                    notification.sent_at,
                    notification.timestamp
                ))
                
                conn.commit()
                return {'success': True}
                
        except Exception as e:
            return {'success': False, 'error': str(e)}