import os
import sqlite3
from pathlib import Path
from datetime import datetime
from typing import Optional, List
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart

DB_PATH = Path(os.environ.get('OUTPUT_DIR', './output')) / 'notifications.db'

def init_notifications_db():
    """Initialize notifications database"""
    conn = sqlite3.connect(str(DB_PATH))
    conn.execute("""CREATE TABLE IF NOT EXISTS notifications (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        type TEXT NOT NULL,
        title TEXT NOT NULL,
        message TEXT NOT NULL,
        status TEXT DEFAULT 'unread',
        channel TEXT DEFAULT 'web',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        read_at TIMESTAMP
    )""")
    conn.execute("""CREATE TABLE IF NOT EXISTS notification_preferences (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL UNIQUE,
        email_enabled BOOLEAN DEFAULT 1,
        sms_enabled BOOLEAN DEFAULT 0,
        web_enabled BOOLEAN DEFAULT 1,
        crawl_complete BOOLEAN DEFAULT 1,
        analysis_complete BOOLEAN DEFAULT 1,
        alert_triggered BOOLEAN DEFAULT 1,
        weekly_report BOOLEAN DEFAULT 1,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )""")
    conn.commit()
    conn.close()

def send_email(to_email: str, subject: str, html_body: str) -> bool:
    """Send email notification"""
    try:
        smtp_host = os.getenv('SMTP_HOST', 'smtp.gmail.com')
        smtp_port = int(os.getenv('SMTP_PORT', '587'))
        smtp_user = os.getenv('SMTP_USER')
        smtp_pass = os.getenv('SMTP_PASS')
        
        if not smtp_user or not smtp_pass:
            print("⚠️ SMTP credentials not configured")
            return False
        
        msg = MIMEMultipart('alternative')
        msg['Subject'] = subject
        msg['From'] = smtp_user
        msg['To'] = to_email
        
        msg.attach(MIMEText(html_body, 'html'))
        
        with smtplib.SMTP(smtp_host, smtp_port) as server:
            server.starttls()
            server.login(smtp_user, smtp_pass)
            server.send_message(msg)
        
        print(f"✅ Email sent to {to_email}")
        return True
    except Exception as e:
        print(f"❌ Email error: {e}")
        return False

def send_sms(phone: str, message: str) -> bool:
    """Send SMS notification via Twilio"""
    try:
        from twilio.rest import Client
        
        account_sid = os.getenv('TWILIO_ACCOUNT_SID')
        auth_token = os.getenv('TWILIO_AUTH_TOKEN')
        from_number = os.getenv('TWILIO_PHONE_NUMBER')
        
        if not all([account_sid, auth_token, from_number]):
            print("⚠️ Twilio credentials not configured")
            return False
        
        client = Client(account_sid, auth_token)
        msg = client.messages.create(
            body=message,
            from_=from_number,
            to=phone
        )
        
        print(f"✅ SMS sent to {phone}")
        return True
    except Exception as e:
        print(f"❌ SMS error: {e}")
        return False

def create_notification(user_id: int, notif_type: str, title: str, message: str, 
                       channel: str = 'web') -> int:
    """Create a notification"""
    init_notifications_db()
    conn = sqlite3.connect(str(DB_PATH))
    cur = conn.execute(
        """INSERT INTO notifications (user_id, type, title, message, channel)
           VALUES (?, ?, ?, ?, ?)""",
        (user_id, notif_type, title, message, channel)
    )
    notif_id = cur.lastrowid
    conn.commit()
    conn.close()
    return notif_id

def get_notifications(user_id: int, unread_only: bool = False) -> List[dict]:
    """Get user notifications"""
    init_notifications_db()
    conn = sqlite3.connect(str(DB_PATH))
    conn.row_factory = sqlite3.Row
    
    query = "SELECT * FROM notifications WHERE user_id = ?"
    params = [user_id]
    
    if unread_only:
        query += " AND status = 'unread'"
    
    query += " ORDER BY created_at DESC LIMIT 50"
    
    rows = conn.execute(query, params).fetchall()
    conn.close()
    
    return [dict(r) for r in rows]

def mark_as_read(notification_id: int) -> bool:
    """Mark notification as read"""
    init_notifications_db()
    conn = sqlite3.connect(str(DB_PATH))
    conn.execute(
        "UPDATE notifications SET status = 'read', read_at = ? WHERE id = ?",
        (datetime.utcnow(), notification_id)
    )
    conn.commit()
    conn.close()
    return True

def get_preferences(user_id: int) -> dict:
    """Get notification preferences"""
    init_notifications_db()
    conn = sqlite3.connect(str(DB_PATH))
    conn.row_factory = sqlite3.Row
    
    row = conn.execute(
        "SELECT * FROM notification_preferences WHERE user_id = ?",
        (user_id,)
    ).fetchone()
    conn.close()
    
    if row:
        return dict(row)
    
    # Create default preferences
    conn = sqlite3.connect(str(DB_PATH))
    conn.execute(
        """INSERT INTO notification_preferences (user_id) VALUES (?)""",
        (user_id,)
    )
    conn.commit()
    conn.close()
    
    return {
        'user_id': user_id,
        'email_enabled': True,
        'sms_enabled': False,
        'web_enabled': True,
        'crawl_complete': True,
        'analysis_complete': True,
        'alert_triggered': True,
        'weekly_report': True
    }

def update_preferences(user_id: int, prefs: dict) -> bool:
    """Update notification preferences"""
    init_notifications_db()
    conn = sqlite3.connect(str(DB_PATH))
    
    updates = []
    values = []
    for key, value in prefs.items():
        if key != 'user_id':
            updates.append(f"{key} = ?")
            values.append(value)
    
    if updates:
        values.append(user_id)
        query = f"UPDATE notification_preferences SET {', '.join(updates)} WHERE user_id = ?"
        conn.execute(query, values)
        conn.commit()
    
    conn.close()
    return True

def notify_crawl_complete(user_id: int, job_id: int, url: str, pages_count: int, 
                         user_email: Optional[str] = None) -> bool:
    """Notify when crawl is complete"""
    prefs = get_preferences(user_id)
    
    title = f"✅ تم إكمال الزحف - {url}"
    message = f"تم زحف {pages_count} صفحات بنجاح"
    
    # Web notification
    if prefs.get('web_enabled') and prefs.get('crawl_complete'):
        create_notification(user_id, 'crawl_complete', title, message, 'web')
    
    # Email notification
    if prefs.get('email_enabled') and prefs.get('crawl_complete') and user_email:
        html = f"""
        <h2>{title}</h2>
        <p>{message}</p>
        <p>رابط المشروع: <a href="https://alinabil21-moharek2.hf.space/portal.html?job={job_id}">عرض النتائج</a></p>
        """
        send_email(user_email, title, html)
    
    return True

def notify_analysis_complete(user_id: int, job_id: int, geo_score: int, 
                            user_email: Optional[str] = None) -> bool:
    """Notify when analysis is complete"""
    prefs = get_preferences(user_id)
    
    title = f"✅ تم إكمال التحليل - درجة GEO: {geo_score}"
    message = f"درجة الرؤية في الذكاء الاصطناعي: {geo_score}/100"
    
    # Web notification
    if prefs.get('web_enabled') and prefs.get('analysis_complete'):
        create_notification(user_id, 'analysis_complete', title, message, 'web')
    
    # Email notification
    if prefs.get('email_enabled') and prefs.get('analysis_complete') and user_email:
        html = f"""
        <h2>{title}</h2>
        <p>{message}</p>
        <p><a href="https://alinabil21-moharek2.hf.space/portal.html?job={job_id}">عرض التحليل الكامل</a></p>
        """
        send_email(user_email, title, html)
    
    return True

def notify_alert(user_id: int, alert_type: str, message: str, 
                user_email: Optional[str] = None) -> bool:
    """Notify about alerts"""
    prefs = get_preferences(user_id)
    
    title = f"⚠️ تنبيه: {alert_type}"
    
    # Web notification
    if prefs.get('web_enabled') and prefs.get('alert_triggered'):
        create_notification(user_id, 'alert', title, message, 'web')
    
    # Email notification
    if prefs.get('email_enabled') and prefs.get('alert_triggered') and user_email:
        html = f"""
        <h2>{title}</h2>
        <p>{message}</p>
        """
        send_email(user_email, title, html)
    
    return True

def send_weekly_report(user_id: int, report_html: str, user_email: Optional[str] = None) -> bool:
    """Send weekly report"""
    prefs = get_preferences(user_id)
    
    if not (prefs.get('email_enabled') and prefs.get('weekly_report') and user_email):
        return False
    
    title = "📊 التقرير الأسبوعي - منصة محرك GEO"
    send_email(user_email, title, report_html)
    
    return True

# Initialize on import
try:
    init_notifications_db()
except Exception:
    pass