# Project: Telegram Bot
# Author: Molla Samser
# Designer & Tester: Rima Khatun
# Website: https://rskworld.in
# Contact: hello@rskworld.in | +91 93305 39277
# Address: Nutanhat, Mongolkote, Purba Burdwan, West Bengal, India, 713147
# Copyright: © 2026 RSK World. All rights reserved.

import sqlite3
import logging
from config import DB_NAME

logger = logging.getLogger(__name__)

def init_db():
    """Initialize the database with necessary tables."""
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    
    # Users table
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS users (
            user_id INTEGER PRIMARY KEY,
            username TEXT,
            first_name TEXT,
            last_name TEXT,
            joined_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    
    # Feedback table
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS feedback (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER,
            message TEXT,
            timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')

    # Reminders table
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS reminders (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER,
            message TEXT,
            remind_at TIMESTAMP,
            status TEXT DEFAULT 'pending'
        )
    ''')
    
    # Users table - Ensure it has new columns if they don't exist
    try:
        cursor.execute("ALTER TABLE users ADD COLUMN msg_count INTEGER DEFAULT 0")
        cursor.execute("ALTER TABLE users ADD COLUMN xp INTEGER DEFAULT 0")
    except sqlite3.OperationalError:
        pass # Columns likely exist
        
    conn.commit()
    conn.close()
    logger.info("Database initialized.")

def add_user(user):
    """Add a new user to the database if they don't exist."""
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    
    try:
        cursor.execute(
            "INSERT OR IGNORE INTO users (user_id, username, first_name, last_name, msg_count, xp) VALUES (?, ?, ?, ?, 0, 0)",
            (user.id, user.username, user.first_name, user.last_name)
        )
        conn.commit()
    except Exception as e:
        logger.error(f"Error adding user: {e}")
    finally:
        conn.close()

def update_user_activity(user_id):
    """Increment message count and XP for a user."""
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    try:
        # Give 10 XP per message
        cursor.execute("UPDATE users SET msg_count = msg_count + 1, xp = xp + 10 WHERE user_id = ?", (user_id,))
        conn.commit()
    except Exception as e:
        logger.error(f"Error updating user activity: {e}")
    finally:
        conn.close()

def get_user_rank(user_id):
    """Get the user's statistics."""
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    try:
        cursor.execute("SELECT first_name, msg_count, xp FROM users WHERE user_id = ?", (user_id,))
        data = cursor.fetchone()
        return data # (first_name, msg_count, xp)
    except Exception as e:
        logger.error(f"Error getting user rank: {e}")
        return None
    finally:
        conn.close()

def add_reminder(user_id, message, remind_at):
    """Adds a reminder to the database."""
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    cursor.execute("INSERT INTO reminders (user_id, message, remind_at) VALUES (?, ?, ?)", (user_id, message, remind_at))
    conn.commit()
    conn.close()

def get_pending_reminders():
    """Fetches all pending reminders that are due."""
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    # Using CURRENT_TIMESTAMP for comparison
    cursor.execute("SELECT id, user_id, message FROM reminders WHERE status = 'pending' AND remind_at <= datetime('now', 'localtime')")
    reminders = cursor.fetchall()
    conn.close()
    return reminders

def update_reminder_status(reminder_id, status='sent'):
    """Updates the status of a reminder."""
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    cursor.execute("UPDATE reminders SET status = ? WHERE id = ?", (status, reminder_id))
    conn.commit()
    conn.close()

def get_stats():
    """Get bot statistics."""
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    try:
        cursor.execute("SELECT COUNT(*) FROM users")
        user_count = cursor.fetchone()[0]
        
        cursor.execute("SELECT COUNT(*) FROM feedback")
        feedback_count = cursor.fetchone()[0]

        cursor.execute("SELECT COUNT(*) FROM reminders")
        reminder_count = cursor.fetchone()[0]
        
        cursor.execute("SELECT SUM(msg_count) FROM users")
        total_messages = cursor.fetchone()[0] or 0
        
        cursor.execute("SELECT SUM(xp) FROM users")
        total_xp = cursor.fetchone()[0] or 0
        
        return {
            "user_count": user_count,
            "feedback_count": feedback_count,
            "reminder_count": reminder_count,
            "total_messages": total_messages,
            "total_xp": total_xp
        }
    except Exception as e:
        logger.error(f"Error getting stats: {e}")
        return {
            "user_count": 0,
            "feedback_count": 0,
            "reminder_count": 0,
            "total_messages": 0,
            "total_xp": 0
        }
    finally:
        conn.close()

def get_all_users():
    """Get all user IDs."""
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    cursor.execute("SELECT user_id FROM users")
    users = [row[0] for row in cursor.fetchall()]
    conn.close()
    return users

def save_feedback(user_id, message):
    """Save user feedback."""
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    try:
        cursor.execute("INSERT INTO feedback (user_id, message) VALUES (?, ?)", (user_id, message))
        conn.commit()
    except Exception as e:
        logger.error(f"Error saving feedback: {e}")
    finally:
        conn.close()

def get_leaderboard(limit=10):
    """Get top users by XP."""
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    try:
        cursor.execute("""
            SELECT user_id, first_name, xp, 
                   CAST((xp / 100) + 1 AS INTEGER) as level
            FROM users 
            ORDER BY xp DESC 
            LIMIT ?
        """, (limit,))
        leaderboard = cursor.fetchall()
        return leaderboard
    except Exception as e:
        logger.error(f"Error getting leaderboard: {e}")
        return []
    finally:
        conn.close()

def get_user_stats(user_id):
    """Get detailed user statistics."""
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    try:
        cursor.execute("""
            SELECT 
                u.first_name,
                u.msg_count,
                u.xp,
                CAST((u.xp / 100) + 1 AS INTEGER) as level,
                COUNT(DISTINCT f.id) as feedback_count,
                COUNT(DISTINCT r.id) as reminder_count
            FROM users u
            LEFT JOIN feedback f ON u.user_id = f.user_id
            LEFT JOIN reminders r ON u.user_id = r.user_id
            WHERE u.user_id = ?
            GROUP BY u.user_id
        """, (user_id,))
        stats = cursor.fetchone()
        return stats
    except Exception as e:
        logger.error(f"Error getting user stats: {e}")
        return None
    finally:
        conn.close()