database.py

"""
TutorOps Pro — Database Module
==============================
SQLite-based persistence for message logging, analytics, and conversation history.
Thread-safe and async-ready.
"""

import sqlite3
import threading
from pathlib import Path
from datetime import datetime, timedelta
from typing import Optional, List, Dict, Any
from contextlib import contextmanager

# Database file location
DB_PATH = Path(__file__).parent / "tutorops.db"

# Thread-local storage for connections
_local = threading.local()


def get_connection() -> sqlite3.Connection:
    """Get thread-local database connection."""
    if not hasattr(_local, 'connection') or _local.connection is None:
        _local.connection = sqlite3.connect(str(DB_PATH), check_same_thread=False)
        _local.connection.row_factory = sqlite3.Row
    return _local.connection


@contextmanager
def get_cursor():
    """Context manager for database operations."""
    conn = get_connection()
    cursor = conn.cursor()
    try:
        yield cursor
        conn.commit()
    except Exception as e:
        conn.rollback()
        raise e


def init_database():
    """Create tables if they don't exist."""
    with get_cursor() as cursor:
        # Messages table
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS messages (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                phone TEXT NOT NULL,
                content TEXT NOT NULL,
                response TEXT,
                response_time_ms INTEGER,
                timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
                is_from_user BOOLEAN DEFAULT TRUE
            )
        """)
        
        # Conversations table (tracks active conversations)
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS conversations (
                phone TEXT PRIMARY KEY,
                last_activity DATETIME DEFAULT CURRENT_TIMESTAMP,
                message_count INTEGER DEFAULT 0,
                first_contact DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        """)
        
        # Daily stats table
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS daily_stats (
                date TEXT PRIMARY KEY,
                total_messages INTEGER DEFAULT 0,
                total_responses INTEGER DEFAULT 0,
                avg_response_time_ms REAL DEFAULT 0,
                unique_users INTEGER DEFAULT 0
            )
        """)
        
        # Create indexes for performance
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_messages_phone ON messages(phone)")
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_messages_timestamp ON messages(timestamp)")
        
        # Students table (Phase 2)
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS students (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                phone TEXT UNIQUE NOT NULL,
                name TEXT,
                balance REAL DEFAULT 0,
                notes TEXT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        """)
        
        # Payments table (Phase 2)
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS payments (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                student_id INTEGER,
                phone TEXT,
                amount REAL NOT NULL,
                transaction_id TEXT,
                status TEXT DEFAULT 'pending',
                receipt_path TEXT,
                notes TEXT,
                verified_by TEXT,
                verified_at DATETIME,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (student_id) REFERENCES students(id)
            )
        """)
        
        # Create indexes for payments
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_payments_status ON payments(status)")
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_payments_phone ON payments(phone)")
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_students_phone ON students(phone)")
        
        # Scheduled messages table (Phase 4)
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS scheduled_messages (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                phone TEXT,
                message TEXT NOT NULL,
                send_at DATETIME NOT NULL,
                status TEXT DEFAULT 'pending',
                sent_at DATETIME,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        """)
        
        # Add label column to conversations if not exists
        try:
            cursor.execute("ALTER TABLE conversations ADD COLUMN label TEXT")
        except:
            pass  # Column already exists
    
    print("[Database] Initialized successfully")


# ============== MESSAGE OPERATIONS ==============

def log_message(
    phone: str,
    content: str,
    response: Optional[str] = None,
    response_time_ms: Optional[int] = None
) -> int:
    """Log a message and update conversation stats."""
    with get_cursor() as cursor:
        # Insert message
        cursor.execute("""
            INSERT INTO messages (phone, content, response, response_time_ms)
            VALUES (?, ?, ?, ?)
        """, (phone, content, response, response_time_ms))
        message_id = cursor.lastrowid
        
        # Update conversation
        cursor.execute("""
            INSERT INTO conversations (phone, message_count)
            VALUES (?, 1)
            ON CONFLICT(phone) DO UPDATE SET
                last_activity = CURRENT_TIMESTAMP,
                message_count = message_count + 1
        """, (phone,))
        
        # Update daily stats
        today = datetime.now().strftime("%Y-%m-%d")
        cursor.execute("""
            INSERT INTO daily_stats (date, total_messages, total_responses, unique_users)
            VALUES (?, 1, ?, 1)
            ON CONFLICT(date) DO UPDATE SET
                total_messages = total_messages + 1,
                total_responses = total_responses + ?
        """, (today, 1 if response else 0, 1 if response else 0))
        
    return message_id


def get_conversation_history(phone: str, limit: int = 5) -> List[Dict[str, Any]]:
    """Get recent messages for a phone number."""
    with get_cursor() as cursor:
        cursor.execute("""
            SELECT content, response, timestamp
            FROM messages
            WHERE phone = ?
            ORDER BY timestamp DESC
            LIMIT ?
        """, (phone, limit))
        
        rows = cursor.fetchall()
        return [dict(row) for row in reversed(rows)]


def get_conversation_context(phone: str, max_age_minutes: int = 30) -> List[Dict[str, str]]:
    """Get recent conversation for AI context (within time limit)."""
    cutoff = datetime.now() - timedelta(minutes=max_age_minutes)
    
    with get_cursor() as cursor:
        cursor.execute("""
            SELECT content, response
            FROM messages
            WHERE phone = ? AND timestamp > ?
            ORDER BY timestamp DESC
            LIMIT 5
        """, (phone, cutoff.isoformat()))
        
        rows = cursor.fetchall()
        context = []
        for row in reversed(rows):
            if row['content']:
                context.append({"role": "user", "text": row['content']})
            if row['response']:
                context.append({"role": "assistant", "text": row['response']})
        
        return context


# ============== ANALYTICS ==============

def get_stats() -> Dict[str, Any]:
    """Get overall statistics."""
    with get_cursor() as cursor:
        # Total messages
        cursor.execute("SELECT COUNT(*) as count FROM messages")
        total_messages = cursor.fetchone()['count']
        
        # Today's messages
        today = datetime.now().strftime("%Y-%m-%d")
        cursor.execute("""
            SELECT total_messages, avg_response_time_ms
            FROM daily_stats WHERE date = ?
        """, (today,))
        today_row = cursor.fetchone()
        
        # Unique users
        cursor.execute("SELECT COUNT(*) as count FROM conversations")
        unique_users = cursor.fetchone()['count']
        
        # Active conversations (last 30 mins)
        cutoff = (datetime.now() - timedelta(minutes=30)).isoformat()
        cursor.execute("""
            SELECT COUNT(*) as count FROM conversations
            WHERE last_activity > ?
        """, (cutoff,))
        active_convos = cursor.fetchone()['count']
        
        return {
            "total_messages": total_messages,
            "messages_today": today_row['total_messages'] if today_row else 0,
            "avg_response_time_ms": round(today_row['avg_response_time_ms'], 2) if today_row else 0,
            "unique_users": unique_users,
            "active_conversations": active_convos,
            "database_size_kb": round(DB_PATH.stat().st_size / 1024, 2) if DB_PATH.exists() else 0
        }


def update_response_time(date: str, response_time_ms: int):
    """Update average response time for a date."""
    with get_cursor() as cursor:
        cursor.execute("""
            UPDATE daily_stats SET
                avg_response_time_ms = (
                    (avg_response_time_ms * (total_responses - 1) + ?) / total_responses
                )
            WHERE date = ?
        """, (response_time_ms, date))


# ============== HEALTH CHECK ==============

def check_database_health() -> Dict[str, Any]:
    """Check database connectivity and integrity."""
    try:
        with get_cursor() as cursor:
            cursor.execute("SELECT 1")
            cursor.execute("PRAGMA integrity_check")
            integrity = cursor.fetchone()[0]
        
        return {
            "status": "healthy" if integrity == "ok" else "degraded",
            "integrity": integrity,
            "path": str(DB_PATH),
            "size_kb": round(DB_PATH.stat().st_size / 1024, 2) if DB_PATH.exists() else 0
        }
    except Exception as e:
        return {
            "status": "unhealthy",
            "error": str(e)
        }


# ============== STUDENT OPERATIONS (Phase 2) ==============

def get_or_create_student(phone: str, name: str = None) -> Dict[str, Any]:
    """Get existing student or create new one."""
    with get_cursor() as cursor:
        # Try to find existing
        cursor.execute("SELECT * FROM students WHERE phone = ?", (phone,))
        row = cursor.fetchone()
        
        if row:
            return dict(row)
        
        # Create new student
        cursor.execute("""
            INSERT INTO students (phone, name) VALUES (?, ?)
        """, (phone, name or "Unknown"))
        
        cursor.execute("SELECT * FROM students WHERE id = ?", (cursor.lastrowid,))
        return dict(cursor.fetchone())


def get_all_students() -> List[Dict[str, Any]]:
    """Get all students."""
    with get_cursor() as cursor:
        cursor.execute("""
            SELECT s.*, 
                   (SELECT COUNT(*) FROM payments WHERE student_id = s.id) as payment_count
            FROM students s
            ORDER BY s.created_at DESC
        """)
        return [dict(row) for row in cursor.fetchall()]


def update_student(student_id: int, name: str = None, notes: str = None) -> bool:
    """Update student details."""
    with get_cursor() as cursor:
        updates = []
        params = []
        
        if name is not None:
            updates.append("name = ?")
            params.append(name)
        if notes is not None:
            updates.append("notes = ?")
            params.append(notes)
        
        if not updates:
            return False
        
        updates.append("updated_at = CURRENT_TIMESTAMP")
        params.append(student_id)
        
        cursor.execute(f"""
            UPDATE students SET {', '.join(updates)} WHERE id = ?
        """, params)
        return cursor.rowcount > 0


def update_student_balance(student_id: int, amount: float) -> float:
    """Add to student balance. Returns new balance."""
    with get_cursor() as cursor:
        cursor.execute("""
            UPDATE students SET 
                balance = balance + ?,
                updated_at = CURRENT_TIMESTAMP
            WHERE id = ?
        """, (amount, student_id))
        
        cursor.execute("SELECT balance FROM students WHERE id = ?", (student_id,))
        row = cursor.fetchone()
        return row['balance'] if row else 0


# ============== PAYMENT OPERATIONS (Phase 2) ==============

def create_payment(
    phone: str,
    amount: float,
    transaction_id: str = None,
    receipt_path: str = None,
    status: str = "pending"
) -> int:
    """Create a new payment record."""
    with get_cursor() as cursor:
        # Get or create student
        student = get_or_create_student(phone)
        
        cursor.execute("""
            INSERT INTO payments (student_id, phone, amount, transaction_id, receipt_path, status)
            VALUES (?, ?, ?, ?, ?, ?)
        """, (student['id'], phone, amount, transaction_id, receipt_path, status))
        
        return cursor.lastrowid


def get_pending_payments() -> List[Dict[str, Any]]:
    """Get all pending payments for review."""
    with get_cursor() as cursor:
        cursor.execute("""
            SELECT p.*, s.name as student_name, s.balance as student_balance
            FROM payments p
            LEFT JOIN students s ON p.student_id = s.id
            WHERE p.status = 'pending'
            ORDER BY p.created_at DESC
        """)
        return [dict(row) for row in cursor.fetchall()]


def get_all_payments(limit: int = 50) -> List[Dict[str, Any]]:
    """Get recent payments."""
    with get_cursor() as cursor:
        cursor.execute("""
            SELECT p.*, s.name as student_name
            FROM payments p
            LEFT JOIN students s ON p.student_id = s.id
            ORDER BY p.created_at DESC
            LIMIT ?
        """, (limit,))
        return [dict(row) for row in cursor.fetchall()]


def verify_payment(payment_id: int, verified_by: str = "admin") -> bool:
    """Mark payment as verified and update student balance."""
    with get_cursor() as cursor:
        # Get payment details
        cursor.execute("SELECT * FROM payments WHERE id = ?", (payment_id,))
        payment = cursor.fetchone()
        
        if not payment or payment['status'] != 'pending':
            return False
        
        # Update payment status
        cursor.execute("""
            UPDATE payments SET 
                status = 'verified',
                verified_by = ?,
                verified_at = CURRENT_TIMESTAMP
            WHERE id = ?
        """, (verified_by, payment_id))
        
        # Update student balance
        if payment['student_id']:
            update_student_balance(payment['student_id'], payment['amount'])
        
        return True


def reject_payment(payment_id: int, reason: str = None) -> bool:
    """Mark payment as rejected."""
    with get_cursor() as cursor:
        cursor.execute("""
            UPDATE payments SET 
                status = 'rejected',
                notes = ?,
                verified_at = CURRENT_TIMESTAMP
            WHERE id = ? AND status = 'pending'
        """, (reason, payment_id))
        return cursor.rowcount > 0


def get_payment_stats() -> Dict[str, Any]:
    """Get payment statistics."""
    with get_cursor() as cursor:
        # Total verified
        cursor.execute("""
            SELECT COALESCE(SUM(amount), 0) as total 
            FROM payments WHERE status = 'verified'
        """)
        total_verified = cursor.fetchone()['total']
        
        # Pending count
        cursor.execute("SELECT COUNT(*) as count FROM payments WHERE status = 'pending'")
        pending_count = cursor.fetchone()['count']
        
        # Today's payments
        today = datetime.now().strftime("%Y-%m-%d")
        cursor.execute("""
            SELECT COALESCE(SUM(amount), 0) as total 
            FROM payments 
            WHERE status = 'verified' AND DATE(verified_at) = ?
        """, (today,))
        today_total = cursor.fetchone()['total']
        
        return {
            "total_verified": total_verified,
            "pending_count": pending_count,
            "today_total": today_total
        }


# ============== ADMIN OPERATIONS (Phase 2) ==============

def get_all_messages(limit: int = 100, offset: int = 0) -> List[Dict[str, Any]]:
    """Get all messages with pagination."""
    with get_cursor() as cursor:
        cursor.execute("""
            SELECT * FROM messages
            ORDER BY timestamp DESC
            LIMIT ? OFFSET ?
        """, (limit, offset))
        return [dict(row) for row in cursor.fetchall()]


def search_messages(query: str, limit: int = 50) -> List[Dict[str, Any]]:
    """Search messages by content or phone."""
    with get_cursor() as cursor:
        search = f"%{query}%"
        cursor.execute("""
            SELECT * FROM messages
            WHERE content LIKE ? OR phone LIKE ? OR response LIKE ?
            ORDER BY timestamp DESC
            LIMIT ?
        """, (search, search, search, limit))
        return [dict(row) for row in cursor.fetchall()]


def get_daily_stats_range(days: int = 7) -> List[Dict[str, Any]]:
    """Get daily stats for the last N days."""
    with get_cursor() as cursor:
        cursor.execute("""
            SELECT * FROM daily_stats
            ORDER BY date DESC
            LIMIT ?
        """, (days,))
        return [dict(row) for row in cursor.fetchall()]


def export_all_data() -> Dict[str, Any]:
    """Export all data as JSON-serializable dict."""
    return {
        "exported_at": datetime.now().isoformat(),
        "messages": get_all_messages(limit=10000),
        "students": get_all_students(),
        "payments": get_all_payments(limit=10000),
        "stats": get_stats(),
        "daily_stats": get_daily_stats_range(days=30)
    }


# Initialize on import
init_database()


if __name__ == "__main__":
    # Test database
    print("\n=== Database Test ===")
    
    # Log a test message
    msg_id = log_message(
        phone="+201234567890",
        content="Test message",
        response="Test response",
        response_time_ms=150
    )
    print(f"Logged message ID: {msg_id}")
    
    # Get history
    history = get_conversation_history("+201234567890")
    print(f"History: {history}")
    
    # Get stats
    stats = get_stats()
    print(f"Stats: {stats}")
    
    # Health check
    health = check_database_health()
    print(f"Health: {health}")


# ============== SCHEDULED MESSAGES (Phase 4) ==============

def create_scheduled_message(message: str, send_at: datetime, phone: str = None) -> int:
    """
    Create a scheduled message.
    If phone is None, message will be broadcast to all students.
    """
    with get_cursor() as cursor:
        cursor.execute("""
            INSERT INTO scheduled_messages (phone, message, send_at, status)
            VALUES (?, ?, ?, 'pending')
        """, (phone, message, send_at.strftime("%Y-%m-%d %H:%M:%S")))
        return cursor.lastrowid


def get_due_scheduled_messages() -> List[Dict]:
    """Get all scheduled messages that should be sent now."""
    now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    with get_cursor() as cursor:
        cursor.execute("""
            SELECT id, phone, message, send_at, status
            FROM scheduled_messages
            WHERE status = 'pending' AND send_at <= ?
            ORDER BY send_at ASC
        """, (now,))
        return [dict(row) for row in cursor.fetchall()]


def get_pending_scheduled_messages() -> List[Dict]:
    """Get all pending scheduled messages."""
    with get_cursor() as cursor:
        cursor.execute("""
            SELECT id, phone, message, send_at, status, created_at
            FROM scheduled_messages
            WHERE status = 'pending'
            ORDER BY send_at ASC
        """)
        return [dict(row) for row in cursor.fetchall()]


def mark_scheduled_message_sent(msg_id: int) -> bool:
    """Mark a scheduled message as sent."""
    with get_cursor() as cursor:
        cursor.execute("""
            UPDATE scheduled_messages
            SET status = 'sent', sent_at = CURRENT_TIMESTAMP
            WHERE id = ?
        """, (msg_id,))
        return cursor.rowcount > 0


def cancel_scheduled_message(msg_id: int) -> bool:
    """Cancel a scheduled message."""
    with get_cursor() as cursor:
        cursor.execute("""
            UPDATE scheduled_messages
            SET status = 'cancelled'
            WHERE id = ? AND status = 'pending'
        """, (msg_id,))
        return cursor.rowcount > 0


def get_all_scheduled_messages(limit: int = 50) -> List[Dict]:
    """Get all scheduled messages (for admin)."""
    with get_cursor() as cursor:
        cursor.execute("""
            SELECT id, phone, message, send_at, status, sent_at, created_at
            FROM scheduled_messages
            ORDER BY created_at DESC
            LIMIT ?
        """, (limit,))
        return [dict(row) for row in cursor.fetchall()]


# ============== CONVERSATION LABELS (Phase 4) ==============

def label_conversation(phone: str, label: str) -> bool:
    """Add a label to a conversation."""
    with get_cursor() as cursor:
        cursor.execute("""
            UPDATE conversations SET label = ? WHERE phone = ?
        """, (label, phone))
        return cursor.rowcount > 0


def get_conversations_by_label(label: str) -> List[Dict]:
    """Get all conversations with a specific label."""
    with get_cursor() as cursor:
        cursor.execute("""
            SELECT phone, last_activity, message_count, label
            FROM conversations
            WHERE label = ?
            ORDER BY last_activity DESC
        """, (label,))
        return [dict(row) for row in cursor.fetchall()]


# ============== STUDENT NOTES (Phase 4) ==============

def add_student_note(phone: str, note: str) -> bool:
    """Add a note to a student's profile."""
    timestamp = datetime.now().strftime("%Y-%m-%d %H:%M")
    with get_cursor() as cursor:
        # Get existing notes
        cursor.execute("SELECT notes FROM students WHERE phone = ?", (phone,))
        row = cursor.fetchone()
        if row:
            existing = row['notes'] or ''
            new_notes = f"{existing}\n[{timestamp}] {note}" if existing else f"[{timestamp}] {note}"
            cursor.execute("""
                UPDATE students SET notes = ?, updated_at = CURRENT_TIMESTAMP
                WHERE phone = ?
            """, (new_notes, phone))
            return True
    return False


def update_student_name(phone: str, name: str) -> bool:
    """Update a student's name."""
    with get_cursor() as cursor:
        cursor.execute("""
            UPDATE students SET name = ?, updated_at = CURRENT_TIMESTAMP
            WHERE phone = ?
        """, (name, phone))
        return cursor.rowcount > 0


def adjust_student_balance(phone: str, amount: float, reason: str = None) -> bool:
    """Adjust a student's balance (positive or negative)."""
    with get_cursor() as cursor:
        cursor.execute("""
            UPDATE students 
            SET balance = balance + ?, updated_at = CURRENT_TIMESTAMP
            WHERE phone = ?
        """, (amount, phone))
        
        if cursor.rowcount > 0 and reason:
            add_student_note(phone, f"Balance adjusted by {amount}: {reason}")
        
        return cursor.rowcount > 0