database.py

# database.py
import sqlite3
import os
import hashlib
import secrets
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from datetime import datetime, timedelta
from functools import wraps
from flask import g, session, jsonify, request
import logging

# Configure logging
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

# Database configuration
DATABASE_PATH = 'users.db'

def get_db():
    """Get database connection"""
    if 'db' not in g:
        g.db = sqlite3.connect(DATABASE_PATH)
        g.db.row_factory = sqlite3.Row
    return g.db

def close_db(e=None):
    """Close database connection"""
    db = g.pop('db', None)
    if db is not None:
        db.close()

def get_db_connection():
    """Get a new database connection (for use outside Flask context)"""
    conn = sqlite3.connect(DATABASE_PATH)
    conn.row_factory = sqlite3.Row
    return conn

def init_db():
    """Initialize database with required tables"""
    try:
        db = sqlite3.connect(DATABASE_PATH)
        db.executescript('''

            -- Users table

            CREATE TABLE IF NOT EXISTS users (

                id INTEGER PRIMARY KEY AUTOINCREMENT,

                email TEXT UNIQUE NOT NULL,

                password_hash TEXT NOT NULL,

                salt TEXT NOT NULL,

                is_confirmed BOOLEAN DEFAULT FALSE,

                confirmation_token TEXT,

                reset_token TEXT,

                reset_token_expires TIMESTAMP,

                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

                last_login TIMESTAMP,

                is_active BOOLEAN DEFAULT TRUE

            );

            

            -- User flashcards table

            CREATE TABLE IF NOT EXISTS user_flashcards (

                id INTEGER PRIMARY KEY AUTOINCREMENT,

                user_id INTEGER NOT NULL,

                term TEXT NOT NULL,

                translation TEXT,

                context_sentence TEXT,

                gapped_sentence TEXT,

                definition TEXT,

                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

                study_count INTEGER DEFAULT 0,

                last_studied TIMESTAMP,

                difficulty_level INTEGER DEFAULT 1,

                FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE

            );

            

            -- User study sessions table

            CREATE TABLE IF NOT EXISTS study_sessions (

                id INTEGER PRIMARY KEY AUTOINCREMENT,

                user_id INTEGER NOT NULL,

                session_type TEXT NOT NULL, -- 'flashcard', 'conversation', 'activity'

                duration_minutes INTEGER,

                cards_studied INTEGER DEFAULT 0,

                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

                FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE

            );

            

            -- User settings table

            CREATE TABLE IF NOT EXISTS user_settings (

                id INTEGER PRIMARY KEY AUTOINCREMENT,

                user_id INTEGER UNIQUE NOT NULL,

                preferred_model TEXT DEFAULT 'gemini:gemini-2.5-flash-latest',

                context_focus TEXT DEFAULT 'General/Social',

                voice_accent TEXT DEFAULT 'co.uk',

                daily_goal INTEGER DEFAULT 10,

                notification_enabled BOOLEAN DEFAULT TRUE,

                -- New settings for advanced features

                english_level TEXT DEFAULT 'B1', -- A1, A2, B1, B2, C1, C2

                study_goals TEXT, -- JSON: objectives like "business english", "technical vocabulary"

                preferred_content_types TEXT DEFAULT 'articles,videos', -- comma separated

                content_difficulty TEXT DEFAULT 'adaptive', -- 'easy', 'medium', 'hard', 'adaptive'

                study_schedule TEXT, -- JSON: preferred days/times

                auto_recommendations BOOLEAN DEFAULT TRUE,

                content_sources TEXT DEFAULT 'news,tech,business', -- preferred content sources

                FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE

            );

            

            -- User articles/content table

            CREATE TABLE IF NOT EXISTS user_articles (

                id INTEGER PRIMARY KEY AUTOINCREMENT,

                user_id INTEGER NOT NULL,

                title TEXT NOT NULL,

                content TEXT NOT NULL,

                source_url TEXT,

                source_type TEXT DEFAULT 'manual', -- 'manual', 'web_search', 'recommended'

                category TEXT, -- user interest category

                difficulty_level TEXT, -- estimated difficulty

                word_count INTEGER,

                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

                last_accessed TIMESTAMP,

                is_favorite BOOLEAN DEFAULT FALSE,

                study_progress REAL DEFAULT 0.0, -- 0.0 to 1.0

                FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE

            );

            

            -- User interests/preferences table

            CREATE TABLE IF NOT EXISTS user_interests (

                id INTEGER PRIMARY KEY AUTOINCREMENT,

                user_id INTEGER NOT NULL,

                interest_category TEXT NOT NULL,

                weight REAL DEFAULT 1.0, -- importance weight

                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

                FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,

                UNIQUE(user_id, interest_category)

            );

            

            -- Content recommendations table

            CREATE TABLE IF NOT EXISTS content_recommendations (

                id INTEGER PRIMARY KEY AUTOINCREMENT,

                user_id INTEGER NOT NULL,

                article_id INTEGER,

                recommendation_reason TEXT,

                relevance_score REAL,

                recommended_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

                viewed BOOLEAN DEFAULT FALSE,

                accepted BOOLEAN DEFAULT FALSE,

                FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,

                FOREIGN KEY (article_id) REFERENCES user_articles (id) ON DELETE CASCADE

            );

            

            -- Study plans table

            CREATE TABLE IF NOT EXISTS study_plans (

                id INTEGER PRIMARY KEY AUTOINCREMENT,

                user_id INTEGER NOT NULL,

                plan_name TEXT NOT NULL,

                target_level TEXT, -- A1, A2, B1, B2, C1, C2

                current_level TEXT,

                objectives TEXT, -- JSON string with objectives

                weekly_hours INTEGER DEFAULT 5,

                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

                is_active BOOLEAN DEFAULT TRUE,

                completion_percentage REAL DEFAULT 0.0,

                FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE

            );

            

            -- Study plan activities table

            CREATE TABLE IF NOT EXISTS study_plan_activities (

                id INTEGER PRIMARY KEY AUTOINCREMENT,

                plan_id INTEGER NOT NULL,

                activity_type TEXT NOT NULL, -- 'reading', 'flashcards', 'conversation', 'writing'

                content_reference TEXT, -- reference to article, flashcard set, etc.

                scheduled_date DATE,

                estimated_duration INTEGER, -- minutes

                actual_duration INTEGER,

                completed BOOLEAN DEFAULT FALSE,

                completed_at TIMESTAMP,

                difficulty_rating INTEGER, -- 1-5 user rating

                notes TEXT,

                FOREIGN KEY (plan_id) REFERENCES study_plans (id) ON DELETE CASCADE

            );

            

            -- User analytics table

            CREATE TABLE IF NOT EXISTS user_analytics (

                id INTEGER PRIMARY KEY AUTOINCREMENT,

                user_id INTEGER NOT NULL,

                metric_name TEXT NOT NULL,

                metric_value REAL NOT NULL,

                metric_date DATE NOT NULL,

                context_data TEXT, -- JSON with additional context

                FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE

            );

            

            -- Token usage tracking table for admin

            CREATE TABLE IF NOT EXISTS token_usage (

                id INTEGER PRIMARY KEY AUTOINCREMENT,

                user_id INTEGER,

                api_provider TEXT NOT NULL, -- 'groq', 'gemini', etc.

                input_tokens INTEGER DEFAULT 0,

                output_tokens INTEGER DEFAULT 0,

                operation_type TEXT, -- 'conversation', 'content_analysis', 'recommendation', etc.

                tokens_used INTEGER GENERATED ALWAYS AS (input_tokens + output_tokens) STORED,

                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

                FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE

            );

            

            -- Create indexes for better performance

            CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);

            CREATE INDEX IF NOT EXISTS idx_users_confirmation_token ON users(confirmation_token);

            CREATE INDEX IF NOT EXISTS idx_users_reset_token ON users(reset_token);

            CREATE INDEX IF NOT EXISTS idx_flashcards_user_id ON user_flashcards(user_id);

            CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON study_sessions(user_id);

            CREATE INDEX IF NOT EXISTS idx_settings_user_id ON user_settings(user_id);

            CREATE INDEX IF NOT EXISTS idx_articles_user_id ON user_articles(user_id);

            CREATE INDEX IF NOT EXISTS idx_articles_category ON user_articles(category);

            CREATE INDEX IF NOT EXISTS idx_interests_user_id ON user_interests(user_id);

            CREATE INDEX IF NOT EXISTS idx_recommendations_user_id ON content_recommendations(user_id);

            CREATE INDEX IF NOT EXISTS idx_study_plans_user_id ON study_plans(user_id);

            CREATE INDEX IF NOT EXISTS idx_plan_activities_plan_id ON study_plan_activities(plan_id);

            CREATE INDEX IF NOT EXISTS idx_analytics_user_date ON user_analytics(user_id, metric_date);

            CREATE INDEX IF NOT EXISTS idx_token_usage_user_id ON token_usage(user_id);

            CREATE INDEX IF NOT EXISTS idx_token_usage_provider ON token_usage(api_provider);

            CREATE INDEX IF NOT EXISTS idx_token_usage_date ON token_usage(created_at);

        ''')
        db.commit()
        db.close()
        logger.info("Database initialized successfully")
        return True
    except Exception as e:
        logger.error(f"Error initializing database: {e}")
        return False

def hash_password(password, salt=None):
    """Hash password with salt"""
    if salt is None:
        salt = secrets.token_hex(32)
    
    password_hash = hashlib.pbkdf2_hmac(
        'sha256',
        password.encode('utf-8'),
        salt.encode('utf-8'),
        100000  # iterations
    )
    return password_hash.hex(), salt

def verify_password(password, password_hash, salt):
    """Verify password against hash"""
    new_hash, _ = hash_password(password, salt)
    return new_hash == password_hash

def generate_token():
    """Generate secure random token"""
    return secrets.token_urlsafe(32)

def create_user(email, password):
    """Create new user account"""
    try:
        db = get_db()
        
        # Check if user already exists
        existing_user = db.execute(
            'SELECT id FROM users WHERE email = ?', (email,)
        ).fetchone()
        
        if existing_user:
            return {'success': False, 'message': 'Email already registered'}
        
        # Hash password
        password_hash, salt = hash_password(password)
        confirmation_token = generate_token()
        
        # For HF Spaces demo mode, auto-confirm emails
        is_hf_spaces = os.environ.get('SPACE_ID') is not None
        has_smtp_username = os.environ.get('SMTP_USERNAME') is not None
        has_smtp_password = os.environ.get('SMTP_PASSWORD') is not None
        has_smtp = has_smtp_username and has_smtp_password
        
        # Sempre auto-confirmar no HF Spaces ou quando SMTP não está configurado
        auto_confirm = is_hf_spaces or not has_smtp
        
        # Debug logging
        logger.info(f"Registration debug - SPACE_ID: {os.environ.get('SPACE_ID')}")
        logger.info(f"HF_Spaces: {is_hf_spaces}, SMTP_USER: {has_smtp_username}, SMTP_PASS: {has_smtp_password}")
        logger.info(f"SMTP configured: {has_smtp}, Auto-confirm: {auto_confirm}")
        
        # Insert user
        cursor = db.execute(
            '''INSERT INTO users (email, password_hash, salt, confirmation_token, is_confirmed) 

               VALUES (?, ?, ?, ?, ?)''',
            (email, password_hash, salt, confirmation_token, auto_confirm)
        )
        user_id = cursor.lastrowid
        
        # Create default user settings
        db.execute(
            '''INSERT INTO user_settings (user_id) VALUES (?)''',
            (user_id,)
        )
        
        db.commit()
        
        if auto_confirm:
            logger.info(f"User created and auto-confirmed: {email} (HF Spaces demo mode)")
            message = 'Account created and ready to use! (Demo mode - no email confirmation needed)'
        else:
            logger.info(f"User created: {email}")
            message = 'User created successfully. Please check your email for confirmation.'
            
        return {
            'success': True, 
            'user_id': user_id,
            'confirmation_token': confirmation_token,
            'message': message,
            'auto_confirmed': auto_confirm
        }
        
    except Exception as e:
        logger.error(f"Error creating user: {e}")
        return {'success': False, 'message': 'Internal server error'}

def authenticate_user(email, password):
    """Authenticate user login"""
    try:
        db = get_db()
        user = db.execute(
            '''SELECT id, email, password_hash, salt, is_confirmed, is_active 

               FROM users WHERE email = ?''', (email,)
        ).fetchone()
        
        if not user:
            return {'success': False, 'message': 'Invalid email or password'}
        
        if not user['is_active']:
            return {'success': False, 'message': 'Account is deactivated'}
        
        if not verify_password(password, user['password_hash'], user['salt']):
            return {'success': False, 'message': 'Invalid email or password'}
        
        if not user['is_confirmed']:
            return {'success': False, 'message': 'Please confirm your email before logging in'}
        
        # Update last login
        db.execute(
            'UPDATE users SET last_login = CURRENT_TIMESTAMP WHERE id = ?',
            (user['id'],)
        )
        db.commit()
        
        return {
            'success': True,
            'user_id': user['id'],
            'email': user['email'],
            'message': 'Login successful'
        }
        
    except Exception as e:
        logger.error(f"Error authenticating user: {e}")
        return {'success': False, 'message': 'Internal server error'}

def confirm_email(token):
    """Confirm user email with token"""
    try:
        db = get_db()
        user = db.execute(
            'SELECT id, email FROM users WHERE confirmation_token = ? AND is_confirmed = FALSE',
            (token,)
        ).fetchone()
        
        if not user:
            return {'success': False, 'message': 'Invalid or expired confirmation token'}
        
        db.execute(
            '''UPDATE users SET is_confirmed = TRUE, confirmation_token = NULL 

               WHERE id = ?''',
            (user['id'],)
        )
        db.commit()
        
        logger.info(f"Email confirmed for user: {user['email']}")
        return {'success': True, 'message': 'Email confirmed successfully'}
        
    except Exception as e:
        logger.error(f"Error confirming email: {e}")
        return {'success': False, 'message': 'Internal server error'}

def get_user_settings(user_id):
    """Get user settings"""
    try:
        db = get_db()
        settings = db.execute(
            '''SELECT preferred_model, context_focus, voice_accent, daily_goal, notification_enabled

               FROM user_settings WHERE user_id = ?''',
            (user_id,)
        ).fetchone()
        
        if settings:
            return dict(settings)
        return None
        
    except Exception as e:
        logger.error(f"Error getting user settings: {e}")
        return None

def update_user_settings(user_id, settings):
    """Update user settings"""
    try:
        db = get_db()
        db.execute(
            '''UPDATE user_settings 

               SET preferred_model = ?, context_focus = ?, voice_accent = ?, 

                   daily_goal = ?, notification_enabled = ?

               WHERE user_id = ?''',
            (settings.get('preferred_model'), settings.get('context_focus'),
             settings.get('voice_accent'), settings.get('daily_goal'),
             settings.get('notification_enabled'), user_id)
        )
        db.commit()
        return True
        
    except Exception as e:
        logger.error(f"Error updating user settings: {e}")
        return False

def save_user_flashcard(user_id, flashcard_data):
    """Save flashcard to user's collection"""
    try:
        db = get_db()
        db.execute(
            '''INSERT INTO user_flashcards 

               (user_id, term, translation, context_sentence, gapped_sentence, definition)

               VALUES (?, ?, ?, ?, ?, ?)''',
            (user_id, flashcard_data.get('term'), flashcard_data.get('translation'),
             flashcard_data.get('context_sentence'), flashcard_data.get('gapped_sentence'),
             flashcard_data.get('definition'))
        )
        db.commit()
        return True
        
    except Exception as e:
        logger.error(f"Error saving flashcard: {e}")
        return False

def get_user_flashcards(user_id, limit=50):
    """Get user's flashcards"""
    try:
        db = get_db()
        flashcards = db.execute(
            '''SELECT * FROM user_flashcards 

               WHERE user_id = ? 

               ORDER BY created_at DESC 

               LIMIT ?''',
            (user_id, limit)
        ).fetchall()
        
        return [dict(card) for card in flashcards]
        
    except Exception as e:
        logger.error(f"Error getting user flashcards: {e}")
        return []

def record_study_session(user_id, session_type, duration_minutes=None, cards_studied=0):
    """Record a study session"""
    try:
        db = get_db()
        db.execute(
            '''INSERT INTO study_sessions (user_id, session_type, duration_minutes, cards_studied)

               VALUES (?, ?, ?, ?)''',
            (user_id, session_type, duration_minutes, cards_studied)
        )
        db.commit()
        return True
        
    except Exception as e:
        logger.error(f"Error recording study session: {e}")
        return False

# Authentication decorators
def login_required(f):
    """Decorator to require login"""
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if 'user_id' not in session:
            return jsonify({'error': 'Authentication required'}), 401
        return f(*args, **kwargs)
    return decorated_function

def get_current_user():
    """Get current logged in user"""
    if 'user_id' in session:
        try:
            db = get_db()
            user = db.execute(
                'SELECT id, email, is_confirmed FROM users WHERE id = ? AND is_active = TRUE',
                (session['user_id'],)
            ).fetchone()
            return dict(user) if user else None
        except Exception as e:
            logger.error(f"Error getting current user: {e}")
            return None
    return None

# Email functionality (for Hugging Face Spaces)
def send_confirmation_email(email, token):
    """Send confirmation email (simplified for HF Spaces with timeout)"""
    try:
        # For Hugging Face Spaces, we'll use environment variables for SMTP
        smtp_server = os.environ.get('SMTP_SERVER', 'smtp.gmail.com')
        smtp_port = int(os.environ.get('SMTP_PORT', '587'))
        smtp_username = os.environ.get('SMTP_USERNAME')
        smtp_password = os.environ.get('SMTP_PASSWORD')
        
        if not all([smtp_username, smtp_password]):
            logger.warning("SMTP credentials not configured - skipping email")
            return False
        
        # Create confirmation URL (will be updated with actual domain)
        base_url = os.environ.get('BASE_URL', 'http://localhost:7860')
        confirm_url = f"{base_url}/confirm-email?token={token}"
        
        # Create email
        msg = MIMEMultipart()
        msg['From'] = smtp_username
        msg['To'] = email
        msg['Subject'] = "Confirm your English Helper account"
        
        body = f"""

        <html>

        <body>

            <h2>Welcome to Dynamic English Study Studio!</h2>

            <p>Thank you for creating an account. Please click the link below to confirm your email address:</p>

            <p><a href="{confirm_url}" style="background-color: #4f46e5; color: white; padding: 10px 20px; text-decoration: none; border-radius: 5px;">Confirm Email</a></p>

            <p>If the button doesn't work, copy and paste this link into your browser:</p>

            <p>{confirm_url}</p>

            <p>This link will expire in 24 hours.</p>

            <p>If you didn't create this account, please ignore this email.</p>

        </body>

        </html>

        """
        
        msg.attach(MIMEText(body, 'html'))
        
        # Send email with timeout
        import socket
        
        # Set socket timeout to prevent hanging
        socket.setdefaulttimeout(10)
        
        server = smtplib.SMTP(smtp_server, smtp_port)
        server.starttls()
        server.login(smtp_username, smtp_password)
        text = msg.as_string()
        server.sendmail(smtp_username, email, text)
        server.quit()
        
        # Reset socket timeout
        socket.setdefaulttimeout(None)
        
        logger.info(f"Confirmation email sent to {email}")
        return True
        
    except Exception as e:
        logger.error(f"Error sending confirmation email: {e}")
        # Reset socket timeout on error
        try:
            import socket
            socket.setdefaulttimeout(None)
        except:
            pass
        return False

# --- CONTENT CURATION FUNCTIONS ---

def save_user_article(user_id, title, content, source_url=None, source_type='manual', category=None):
    """Save article/content for user"""
    try:
        db = get_db()
        word_count = len(content.split()) if content else 0
        
        cursor = db.execute(
            '''INSERT INTO user_articles 

               (user_id, title, content, source_url, source_type, category, word_count)

               VALUES (?, ?, ?, ?, ?, ?, ?)''',
            (user_id, title, content, source_url, source_type, category, word_count)
        )
        article_id = cursor.lastrowid
        db.commit()
        
        logger.info(f"Article saved for user {user_id}: {title}")
        return {'success': True, 'article_id': article_id}
        
    except Exception as e:
        logger.error(f"Error saving article: {e}")
        return {'success': False, 'message': 'Failed to save article'}

def get_user_articles(user_id, category=None, limit=50):
    """Get user's saved articles"""
    try:
        db = get_db()
        
        if category:
            articles = db.execute(
                '''SELECT * FROM user_articles 

                   WHERE user_id = ? AND category = ?

                   ORDER BY created_at DESC LIMIT ?''',
                (user_id, category, limit)
            ).fetchall()
        else:
            articles = db.execute(
                '''SELECT * FROM user_articles 

                   WHERE user_id = ? 

                   ORDER BY created_at DESC LIMIT ?''',
                (user_id, limit)
            ).fetchall()
        
        return [dict(article) for article in articles]
        
    except Exception as e:
        logger.error(f"Error getting user articles: {e}")
        return []

def update_user_interests(user_id, interests):
    """Update user's interests/categories"""
    try:
        db = get_db()
        
        # Clear existing interests
        db.execute('DELETE FROM user_interests WHERE user_id = ?', (user_id,))
        
        # Add new interests
        for interest, weight in interests.items():
            db.execute(
                '''INSERT INTO user_interests (user_id, interest_category, weight)

                   VALUES (?, ?, ?)''',
                (user_id, interest, weight)
            )
        
        db.commit()
        return True
        
    except Exception as e:
        logger.error(f"Error updating user interests: {e}")
        return False

def get_user_interests(user_id):
    """Get user's interests"""
    try:
        db = get_db()
        interests = db.execute(
            'SELECT interest_category, weight FROM user_interests WHERE user_id = ?',
            (user_id,)
        ).fetchall()
        
        return {interest['interest_category']: interest['weight'] for interest in interests}
        
    except Exception as e:
        logger.error(f"Error getting user interests: {e}")
        return {}

def create_study_plan(user_id, plan_name, target_level, current_level, objectives, weekly_hours=5):
    """Create new study plan"""
    try:
        db = get_db()
        
        cursor = db.execute(
            '''INSERT INTO study_plans 

               (user_id, plan_name, target_level, current_level, objectives, weekly_hours)

               VALUES (?, ?, ?, ?, ?, ?)''',
            (user_id, plan_name, target_level, current_level, objectives, weekly_hours)
        )
        plan_id = cursor.lastrowid
        db.commit()
        
        logger.info(f"Study plan created for user {user_id}: {plan_name}")
        return {'success': True, 'plan_id': plan_id}
        
    except Exception as e:
        logger.error(f"Error creating study plan: {e}")
        return {'success': False, 'message': 'Failed to create study plan'}

def get_user_study_plans(user_id):
    """Get user's study plans"""
    try:
        db = get_db()
        plans = db.execute(
            '''SELECT * FROM study_plans 

               WHERE user_id = ? 

               ORDER BY created_at DESC''',
            (user_id,)
        ).fetchall()
        
        return [dict(plan) for plan in plans]
        
    except Exception as e:
        logger.error(f"Error getting study plans: {e}")
        return []

def add_study_activity(plan_id, activity_type, content_reference, scheduled_date, estimated_duration):
    """Add activity to study plan"""
    try:
        db = get_db()
        
        db.execute(
            '''INSERT INTO study_plan_activities 

               (plan_id, activity_type, content_reference, scheduled_date, estimated_duration)

               VALUES (?, ?, ?, ?, ?)''',
            (plan_id, activity_type, content_reference, scheduled_date, estimated_duration)
        )
        db.commit()
        return True
        
    except Exception as e:
        logger.error(f"Error adding study activity: {e}")
        return False

def get_study_activities(plan_id, date_range=None):
    """Get activities for study plan"""
    try:
        db = get_db()
        
        if date_range:
            start_date, end_date = date_range
            activities = db.execute(
                '''SELECT * FROM study_plan_activities 

                   WHERE plan_id = ? AND scheduled_date BETWEEN ? AND ?

                   ORDER BY scheduled_date''',
                (plan_id, start_date, end_date)
            ).fetchall()
        else:
            activities = db.execute(
                '''SELECT * FROM study_plan_activities 

                   WHERE plan_id = ? 

                   ORDER BY scheduled_date''',
                (plan_id,)
            ).fetchall()
        
        return [dict(activity) for activity in activities]
        
    except Exception as e:
        logger.error(f"Error getting study activities: {e}")
        return []

def record_analytics_metric(user_id, metric_name, metric_value, context_data=None):
    """Record analytics metric"""
    try:
        db = get_db()
        
        db.execute(
            '''INSERT INTO user_analytics (user_id, metric_name, metric_value, metric_date, context_data)

               VALUES (?, ?, ?, DATE('now'), ?)''',
            (user_id, metric_name, metric_value, context_data)
        )
        db.commit()
        return True
        
    except Exception as e:
        logger.error(f"Error recording analytics: {e}")
        return False

def get_user_analytics(user_id, metric_name=None, days=30):
    """Get user analytics data"""
    try:
        db = get_db()
        
        if metric_name:
            analytics = db.execute(
                '''SELECT * FROM user_analytics 

                   WHERE user_id = ? AND metric_name = ? 

                   AND metric_date >= DATE('now', '-{} days')

                   ORDER BY metric_date DESC'''.format(days),
                (user_id, metric_name)
            ).fetchall()
        else:
            analytics = db.execute(
                '''SELECT * FROM user_analytics 

                   WHERE user_id = ? 

                   AND metric_date >= DATE('now', '-{} days')

                   ORDER BY metric_date DESC'''.format(days),
                (user_id,)
            ).fetchall()
        
        return [dict(metric) for metric in analytics]
        
    except Exception as e:
        logger.error(f"Error getting analytics: {e}")
        return []