"""
Educational Tutor Bot Database Utilities
Author: RSK World (https://rskworld.in)
Founded by: Molla Samser
Designer & Tester: Rima Khatun
Contact: info@rskworld.com, +91 93305 39277
Year: 2026
"""

import sqlite3
import os
from datetime import datetime, timedelta
from typing import List, Dict, Optional, Tuple

class DatabaseManager:
    """Database manager for Educational Tutor Bot"""
    
    def __init__(self, db_path: str = 'data/tutor_bot.db'):
        self.db_path = db_path
        self.init_database()
    
    def init_database(self):
        """Initialize database with required tables"""
        # Ensure data directory exists
        os.makedirs(os.path.dirname(self.db_path), exist_ok=True)
        
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        # Users table
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                session_id TEXT UNIQUE NOT NULL,
                name TEXT,
                email TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                last_active TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        # Learning sessions table
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS learning_sessions (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                session_id TEXT NOT NULL,
                subject TEXT NOT NULL,
                topic TEXT,
                difficulty TEXT DEFAULT 'Intermediate',
                questions_count INTEGER DEFAULT 0,
                correct_answers INTEGER DEFAULT 0,
                session_duration INTEGER DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (session_id) REFERENCES users (session_id)
            )
        ''')
        
        # Chat history table
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS chat_history (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                session_id TEXT NOT NULL,
                user_message TEXT NOT NULL,
                bot_response TEXT NOT NULL,
                subject TEXT,
                topic TEXT,
                difficulty TEXT,
                message_type TEXT DEFAULT 'question',
                tokens_used INTEGER DEFAULT 0,
                response_time INTEGER DEFAULT 0,
                rating INTEGER,
                timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (session_id) REFERENCES users (session_id)
            )
        ''')
        
        # Learning resources table
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS learning_resources (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                subject TEXT NOT NULL,
                topic TEXT NOT NULL,
                resource_type TEXT NOT NULL,
                title TEXT NOT NULL,
                description TEXT,
                url TEXT,
                difficulty TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        # User progress table
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS user_progress (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                session_id TEXT NOT NULL,
                subject TEXT NOT NULL,
                total_questions INTEGER DEFAULT 0,
                correct_answers INTEGER DEFAULT 0,
                study_time INTEGER DEFAULT 0,
                mastery_level REAL DEFAULT 0.0,
                last_studied TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (session_id) REFERENCES users (session_id)
            )
        ''')
        
        # Create indexes for better performance
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_users_session_id ON users(session_id)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_chat_history_session_id ON chat_history(session_id)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_learning_sessions_session_id ON learning_sessions(session_id)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_user_progress_session_id ON user_progress(session_id)')
        
        conn.commit()
        conn.close()
    
    def get_connection(self) -> sqlite3.Connection:
        """Get database connection"""
        conn = sqlite3.connect(self.db_path)
        conn.row_factory = sqlite3.Row
        return conn
    
    def create_or_update_user(self, session_id: str, name: str = None, email: str = None) -> int:
        """Create or update user record"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute('''
            INSERT OR REPLACE INTO users (session_id, name, email, last_active)
            VALUES (?, ?, ?, CURRENT_TIMESTAMP)
        ''', (session_id, name, email))
        
        user_id = cursor.lastrowid
        conn.commit()
        conn.close()
        return user_id
    
    def save_chat_message(self, session_id: str, user_message: str, bot_response: str,
                         subject: str = None, topic: str = None, difficulty: str = None,
                         tokens_used: int = 0, response_time: int = 0) -> int:
        """Save chat message to database"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute('''
            INSERT INTO chat_history 
            (session_id, user_message, bot_response, subject, topic, difficulty, tokens_used, response_time)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        ''', (session_id, user_message, bot_response, subject, topic, difficulty, tokens_used, response_time))
        
        message_id = cursor.lastrowid
        conn.commit()
        conn.close()
        return message_id
    
    def update_learning_session(self, session_id: str, subject: str, topic: str = None,
                               difficulty: str = 'Intermediate') -> int:
        """Update or create learning session"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute('''
            INSERT OR REPLACE INTO learning_sessions 
            (session_id, subject, topic, difficulty, questions_count, updated_at)
            VALUES (?, ?, ?, ?, 
                COALESCE((SELECT questions_count FROM learning_sessions 
                         WHERE session_id=? AND subject=? AND topic=?), 0) + 1,
                CURRENT_TIMESTAMP)
        ''', (session_id, subject, topic, difficulty, session_id, subject, topic))
        
        session_id_db = cursor.lastrowid
        conn.commit()
        conn.close()
        return session_id_db
    
    def update_user_progress(self, session_id: str, subject: str, correct: bool = False,
                           study_time: int = 0) -> None:
        """Update user learning progress"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        # Get current progress
        cursor.execute('''
            SELECT total_questions, correct_answers, study_time 
            FROM user_progress 
            WHERE session_id = ? AND subject = ?
        ''', (session_id, subject))
        
        current = cursor.fetchone()
        
        if current:
            # Update existing record
            new_total = current['total_questions'] + 1
            new_correct = current['correct_answers'] + (1 if correct else 0)
            new_study_time = current['study_time'] + study_time
            mastery_level = (new_correct / new_total) * 100 if new_total > 0 else 0
            
            cursor.execute('''
                UPDATE user_progress 
                SET total_questions = ?, correct_answers = ?, study_time = ?,
                    mastery_level = ?, last_studied = CURRENT_TIMESTAMP
                WHERE session_id = ? AND subject = ?
            ''', (new_total, new_correct, new_study_time, mastery_level, session_id, subject))
        else:
            # Create new record
            mastery_level = 100 if correct else 0
            cursor.execute('''
                INSERT INTO user_progress 
                (session_id, subject, total_questions, correct_answers, study_time, mastery_level)
                VALUES (?, ?, 1, ?, ?, ?)
            ''', (session_id, subject, 1 if correct else 0, study_time, mastery_level))
        
        conn.commit()
        conn.close()
    
    def get_user_progress(self, session_id: str) -> List[Dict]:
        """Get user learning progress"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute('''
            SELECT subject, total_questions, correct_answers, study_time, mastery_level, last_studied
            FROM user_progress 
            WHERE session_id = ?
            ORDER BY mastery_level DESC, last_studied DESC
        ''', (session_id,))
        
        progress = [dict(row) for row in cursor.fetchall()]
        conn.close()
        return progress
    
    def get_chat_history(self, session_id: str, limit: int = 50) -> List[Dict]:
        """Get chat history for a session"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute('''
            SELECT user_message, bot_response, subject, topic, difficulty, timestamp, rating
            FROM chat_history 
            WHERE session_id = ?
            ORDER BY timestamp DESC
            LIMIT ?
        ''', (session_id, limit))
        
        history = [dict(row) for row in cursor.fetchall()]
        conn.close()
        return history
    
    def get_learning_statistics(self, session_id: str) -> Dict:
        """Get comprehensive learning statistics"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        # Total sessions
        cursor.execute('''
            SELECT COUNT(DISTINCT subject) as subjects_studied,
                   SUM(questions_count) as total_questions,
                   COUNT(DISTINCT DATE(created_at)) as study_days
            FROM learning_sessions 
            WHERE session_id = ?
        ''', (session_id,))
        
        session_stats = dict(cursor.fetchone())
        
        # Progress by subject
        cursor.execute('''
            SELECT subject, 
                   SUM(total_questions) as questions,
                   AVG(mastery_level) as avg_mastery,
                   SUM(study_time) as total_time
            FROM user_progress 
            WHERE session_id = ?
            GROUP BY subject
        ''', (session_id,))
        
        subject_stats = [dict(row) for row in cursor.fetchall()]
        
        # Recent activity
        cursor.execute('''
            SELECT COUNT(*) as recent_messages
            FROM chat_history 
            WHERE session_id = ? AND timestamp >= datetime('now', '-7 days')
        ''', (session_id,))
        
        recent_activity = dict(cursor.fetchone())
        
        conn.close()
        
        return {
            'overview': session_stats,
            'by_subject': subject_stats,
            'recent_activity': recent_activity
        }
    
    def add_learning_resource(self, subject: str, topic: str, resource_type: str,
                             title: str, description: str = None, url: str = None,
                             difficulty: str = 'Intermediate') -> int:
        """Add learning resource to database"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute('''
            INSERT INTO learning_resources 
            (subject, topic, resource_type, title, description, url, difficulty)
            VALUES (?, ?, ?, ?, ?, ?, ?)
        ''', (subject, topic, resource_type, title, description, url, difficulty))
        
        resource_id = cursor.lastrowid
        conn.commit()
        conn.close()
        return resource_id
    
    def get_learning_resources(self, subject: str = None, topic: str = None,
                              difficulty: str = None) -> List[Dict]:
        """Get learning resources"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        query = "SELECT * FROM learning_resources WHERE 1=1"
        params = []
        
        if subject:
            query += " AND subject = ?"
            params.append(subject)
        
        if topic:
            query += " AND topic LIKE ?"
            params.append(f"%{topic}%")
        
        if difficulty:
            query += " AND difficulty = ?"
            params.append(difficulty)
        
        query += " ORDER BY created_at DESC"
        
        cursor.execute(query, params)
        resources = [dict(row) for row in cursor.fetchall()]
        conn.close()
        return resources
    
    def cleanup_old_data(self, days: int = 30) -> int:
        """Clean up old data (older than specified days)"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cutoff_date = datetime.now() - timedelta(days=days)
        
        cursor.execute('''
            DELETE FROM chat_history 
            WHERE timestamp < ?
        ''', (cutoff_date,))
        
        deleted_count = cursor.rowcount
        
        conn.commit()
        conn.close()
        return deleted_count

# Global database manager instance
db_manager = DatabaseManager()