app/database.py

import sqlite3
import logging
from datetime import datetime
from app.config import DATABASE_PATH

logger = logging.getLogger(__name__)

def get_db_connection():
    conn = sqlite3.connect(DATABASE_PATH)
    conn.row_factory = sqlite3.Row
    return conn

def init_db():
    """Initialize database tables."""
    conn = get_db_connection()
    cursor = conn.cursor()
    
    # Cloned packs table
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS cloned_packs (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        original_pack_name TEXT,
        cloned_pack_name TEXT NOT NULL UNIQUE,
        title TEXT,
        sticker_type TEXT NOT NULL, -- 'static', 'animated', 'video'
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
    """)
    
    # User settings table
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS user_settings (
        user_id INTEGER PRIMARY KEY,
        personal_static_pack TEXT,
        personal_video_pack TEXT,
        personal_emoji_static_pack TEXT,
        personal_emoji_video_pack TEXT,
        default_emoji TEXT DEFAULT '😎',
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
    """)
    
    # General stats table
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS bot_stats (
        key TEXT PRIMARY KEY,
        value INTEGER DEFAULT 0
    )
    """)
    
    # Initialize sticker count if not exists
    cursor.execute("INSERT OR IGNORE INTO bot_stats (key, value) VALUES ('total_stickers_cloned', 0)")
    
    conn.commit()
    conn.close()
    logger.info("Database initialized successfully.")

def save_cloned_pack(user_id: int, original_pack_name: str, cloned_pack_name: str, title: str, sticker_type: str):
    """Save a cloned pack to database."""
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("""
        INSERT OR REPLACE INTO cloned_packs (user_id, original_pack_name, cloned_pack_name, title, sticker_type)
        VALUES (?, ?, ?, ?, ?)
        """, (user_id, original_pack_name, cloned_pack_name, title, sticker_type))
        conn.commit()
    except Exception as e:
        logger.error(f"Error saving cloned pack: {e}")
    finally:
        conn.close()

def get_user_packs(user_id: int):
    """Retrieve all packs cloned by a user."""
    conn = get_db_connection()
    cursor = conn.cursor()
    packs = []
    try:
        cursor.execute("""
        SELECT original_pack_name, cloned_pack_name, title, sticker_type, created_at
        FROM cloned_packs
        WHERE user_id = ?
        ORDER BY created_at DESC
        """, (user_id,))
        packs = [dict(row) for row in cursor.fetchall()]
    except Exception as e:
        logger.error(f"Error retrieving user packs: {e}")
    finally:
        conn.close()
    return packs

def delete_user_pack(user_id: int, cloned_pack_name: str):
    """Delete a cloned pack from the database."""
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("DELETE FROM cloned_packs WHERE user_id = ? AND cloned_pack_name = ?", (user_id, cloned_pack_name))
        conn.commit()
    except Exception as e:
        logger.error(f"Error deleting user pack: {e}")
    finally:
        conn.close()

def get_user_settings(user_id: int):
    """Get settings for a user, create default if none exists."""
    conn = get_db_connection()
    cursor = conn.cursor()
    settings = None
    try:
        cursor.execute("SELECT * FROM user_settings WHERE user_id = ?", (user_id,))
        row = cursor.fetchone()
        if row:
            settings = dict(row)
        else:
            # Create default settings
            cursor.execute("INSERT INTO user_settings (user_id) VALUES (?)", (user_id,))
            conn.commit()
            cursor.execute("SELECT * FROM user_settings WHERE user_id = ?", (user_id,))
            row = cursor.fetchone()
            if row:
                settings = dict(row)
    except Exception as e:
        logger.error(f"Error getting user settings: {e}")
    finally:
        conn.close()
    return settings

def update_user_settings(user_id: int, personal_static_pack: str = None, personal_video_pack: str = None, personal_emoji_static_pack: str = None, personal_emoji_video_pack: str = None, default_emoji: str = None):
    """Update user specific settings."""
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        # Get existing settings first to merge
        get_user_settings(user_id)
        
        updates = []
        params = []
        if personal_static_pack is not None:
            updates.append("personal_static_pack = ?")
            params.append(personal_static_pack)
        if personal_video_pack is not None:
            updates.append("personal_video_pack = ?")
            params.append(personal_video_pack)
        if personal_emoji_static_pack is not None:
            updates.append("personal_emoji_static_pack = ?")
            params.append(personal_emoji_static_pack)
        if personal_emoji_video_pack is not None:
            updates.append("personal_emoji_video_pack = ?")
            params.append(personal_emoji_video_pack)
        if default_emoji is not None:
            updates.append("default_emoji = ?")
            params.append(default_emoji)
            
        if updates:
            params.append(user_id)
            cursor.execute(f"""
            UPDATE user_settings
            SET {", ".join(updates)}, updated_at = CURRENT_TIMESTAMP
            WHERE user_id = ?
            """, params)
            conn.commit()
    except Exception as e:
        logger.error(f"Error updating user settings: {e}")
    finally:
        conn.close()

def increment_stickers_cloned(count: int = 1):
    """Increment the global stickers cloned count."""
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("""
        UPDATE bot_stats
        SET value = value + ?
        WHERE key = 'total_stickers_cloned'
        """, (count,))
        conn.commit()
    except Exception as e:
        logger.error(f"Error incrementing sticker stats: {e}")
    finally:
        conn.close()

def get_stats():
    """Retrieve database stats for the web dashboard."""
    conn = get_db_connection()
    cursor = conn.cursor()
    stats = {
        "total_packs_cloned": 0,
        "total_users": 0,
        "total_stickers_cloned": 0
    }
    try:
        # Cloned packs
        cursor.execute("SELECT COUNT(*) FROM cloned_packs")
        stats["total_packs_cloned"] = cursor.fetchone()[0]
        
        # Unique users
        cursor.execute("SELECT COUNT(DISTINCT user_id) FROM cloned_packs")
        stats["total_users"] = cursor.fetchone()[0]
        
        # Total stickers cloned
        cursor.execute("SELECT value FROM bot_stats WHERE key = 'total_stickers_cloned'")
        row = cursor.fetchone()
        if row:
            stats["total_stickers_cloned"] = row[0]
            
    except Exception as e:
        logger.error(f"Error retrieving database stats: {e}")
    finally:
        conn.close()
    return stats