Database Operations

class SopelDB:
    """Main database interface for Sopel bots."""
    
    def __init__(self, config: 'Config', identifier_factory: 'IdentifierFactory' = None):
        """
        Initialize database connection.
        
        Args:
            config (Config): Bot configuration containing database settings
            identifier_factory (IdentifierFactory): Factory for creating identifiers
        """
    
    def connect(self) -> None:
        """Establish database connection and create tables if needed."""
    
    def close(self) -> None:
        """Close database connection."""
    
    # User/Nick data methods
    def get_nick_id(self, nick: str, create: bool = True) -> int:
        """
        Get numeric ID for a nickname.
        
        Args:
            nick (str): Nickname to get ID for
            create (bool): Whether to create new ID if nick doesn't exist
            
        Returns:
            Numeric ID for the nickname
        """
    
    def get_nick_value(self, nick: str, key: str) -> str | None:
        """
        Get stored value for a nickname.
        
        Args:
            nick (str): Nickname to get value for
            key (str): Key name for the stored value
            
        Returns:
            Stored value or None if not found
        """
    
    def set_nick_value(self, nick: str, key: str, value) -> None:
        """
        Store value for a nickname.
        
        Args:
            nick (str): Nickname to store value for
            key (str): Key name for the value
            value: Value to store (will be JSON serialized)
        """
    
    def delete_nick_value(self, nick: str, key: str) -> None:
        """
        Delete stored value for a nickname.
        
        Args:
            nick (str): Nickname to delete value for
            key (str): Key name of value to delete
        """
    
    def delete_nick_group(self, nick: str, key_prefix: str) -> None:
        """
        Delete all values for a nickname with keys starting with prefix.
        
        Args:
            nick (str): Nickname to delete values for
            key_prefix (str): Key prefix to match for deletion
        """
    
    # Channel data methods
    def get_channel_value(self, channel: str, key: str) -> str | None:
        """
        Get stored value for a channel.
        
        Args:
            channel (str): Channel name to get value for
            key (str): Key name for the stored value
            
        Returns:
            Stored value or None if not found
        """
    
    def set_channel_value(self, channel: str, key: str, value) -> None:
        """
        Store value for a channel.
        
        Args:
            channel (str): Channel name to store value for
            key (str): Key name for the value
            value: Value to store (will be JSON serialized)
        """
    
    def delete_channel_value(self, channel: str, key: str) -> None:
        """
        Delete stored value for a channel.
        
        Args:
            channel (str): Channel name to delete value for
            key (str): Key name of value to delete
        """
    
    # Plugin data methods
    def get_plugin_value(self, plugin: str, key: str) -> str | None:
        """
        Get stored value for a plugin.
        
        Args:
            plugin (str): Plugin name to get value for
            key (str): Key name for the stored value
            
        Returns:
            Stored value or None if not found
        """
    
    def set_plugin_value(self, plugin: str, key: str, value) -> None:
        """
        Store value for a plugin.
        
        Args:
            plugin (str): Plugin name to store value for
            key (str): Key name for the value
            value: Value to store (will be JSON serialized)
        """
    
    def delete_plugin_value(self, plugin: str, key: str) -> None:
        """
        Delete stored value for a plugin.
        
        Args:
            plugin (str): Plugin name to delete value for
            key (str): Key name of value to delete
        """
    
    # Utility methods
    def get_preferred_value(self, names: list) -> tuple | None:
        """
        Get the first available value from a list of names.
        
        Args:
            names (list): List of (type, name, key) tuples to check
            
        Returns:
            First found (type, name, key, value) tuple or None
        """

# Base model class
Base = declarative_base()

class NickIDs(Base):
    """Model for tracking nickname IDs."""
    __tablename__ = 'nick_ids'
    
    nick_id: int  # Primary key
    slug: str     # Normalized nickname
    canonical: str  # Original nickname form

class Nicknames(Base):
    """Model for nickname aliases and tracking."""
    __tablename__ = 'nicknames'
    
    nick_id: int  # Foreign key to NickIDs
    slug: str     # Normalized nickname  
    canonical: str  # Original nickname form

class NickValues(Base):
    """Model for user-specific data storage."""
    __tablename__ = 'nick_values'
    
    nick_id: int  # Foreign key to NickIDs
    key: str      # Data key name
    value: str    # JSON-serialized data value

class ChannelValues(Base):
    """Model for channel-specific data storage."""
    __tablename__ = 'channel_values'
    
    channel: str  # Channel name (normalized)
    key: str      # Data key name  
    value: str    # JSON-serialized data value

class PluginValues(Base):
    """Model for plugin-specific data storage."""
    __tablename__ = 'plugin_values'
    
    plugin: str   # Plugin name
    key: str      # Data key name
    value: str    # JSON-serialized data value

from sopel import plugin

@plugin.command('remember')
@plugin.example('.remember pizza is delicious')
def remember_command(bot, trigger):
    """Store information for a user."""
    if not trigger.group(2):
        bot.reply("What should I remember?")
        return
    
    memory = trigger.group(2)
    bot.db.set_nick_value(trigger.nick, 'memory', memory)
    bot.reply(f"I'll remember that for you, {trigger.nick}!")

@plugin.command('recall')
@plugin.example('.recall')
def recall_command(bot, trigger):
    """Recall stored information for a user."""
    memory = bot.db.get_nick_value(trigger.nick, 'memory')
    if memory:
        bot.reply(f"You told me: {memory}")
    else:
        bot.reply("I don't have anything stored for you.")

@plugin.command('forget')
def forget_command(bot, trigger):
    """Delete stored information for a user."""
    bot.db.delete_nick_value(trigger.nick, 'memory')
    bot.reply("I've forgotten what you told me.")

@plugin.command('topic')
@plugin.example('.topic Welcome to our channel!')
@plugin.require_privilege(plugin.OP)
def set_topic_command(bot, trigger):
    """Set and remember channel topic."""
    if not trigger.group(2):
        # Recall stored topic
        stored_topic = bot.db.get_channel_value(trigger.sender, 'custom_topic')
        if stored_topic:
            bot.reply(f"Stored topic: {stored_topic}")
        else:
            bot.reply("No custom topic stored for this channel.")
        return
    
    topic = trigger.group(2)
    bot.db.set_channel_value(trigger.sender, 'custom_topic', topic)
    bot.db.set_channel_value(trigger.sender, 'topic_setter', trigger.nick)
    bot.reply(f"Topic stored: {topic}")

@plugin.command('stats')
def channel_stats(bot, trigger):
    """Show channel statistics."""
    # Get or initialize message counter
    count = bot.db.get_channel_value(trigger.sender, 'message_count') or 0
    count = int(count) + 1
    
    # Update counter
    bot.db.set_channel_value(trigger.sender, 'message_count', count)
    
    bot.reply(f"This channel has seen {count} messages!")

@plugin.command('apikey')
@plugin.require_admin()
def set_api_key(bot, trigger):
    """Set API key for weather plugin."""
    if not trigger.group(2):
        bot.reply("Usage: .apikey <your_api_key>")
        return
    
    api_key = trigger.group(2)
    bot.db.set_plugin_value('weather', 'api_key', api_key)
    bot.reply("API key stored successfully!")

@plugin.command('weather')
@plugin.example('.weather London')
def weather_command(bot, trigger):
    """Get weather using stored API key."""
    api_key = bot.db.get_plugin_value('weather', 'api_key')
    if not api_key:
        bot.reply("Weather API key not configured. Admin needs to set it.")
        return
    
    location = trigger.group(2) or 'London'
    
    # Store user's preferred location
    bot.db.set_nick_value(trigger.nick, 'weather_location', location)
    
    # Use API key to fetch weather...
    bot.reply(f"Weather for {location}: Sunny, 25°C")

@plugin.command('profile')
def user_profile(bot, trigger):
    """Show user profile with multiple data points."""
    nick = trigger.group(2) or trigger.nick
    
    # Get various user data
    location = bot.db.get_nick_value(nick, 'location')
    timezone = bot.db.get_nick_value(nick, 'timezone') 
    favorite_color = bot.db.get_nick_value(nick, 'favorite_color')
    join_date = bot.db.get_nick_value(nick, 'first_seen')
    
    profile_parts = [f"Profile for {nick}:"]
    
    if location:
        profile_parts.append(f"Location: {location}")
    if timezone:
        profile_parts.append(f"Timezone: {timezone}")
    if favorite_color:
        profile_parts.append(f"Favorite color: {favorite_color}")
    if join_date:
        profile_parts.append(f"First seen: {join_date}")
    
    if len(profile_parts) == 1:
        profile_parts.append("No profile data available.")
    
    bot.reply(" | ".join(profile_parts))

@plugin.command('setprofile')
@plugin.example('.setprofile location New York')
def set_profile(bot, trigger):
    """Set profile information."""
    args = trigger.group(2)
    if not args:
        bot.reply("Usage: .setprofile <field> <value>")
        return
    
    parts = args.split(' ', 1)
    if len(parts) != 2:
        bot.reply("Usage: .setprofile <field> <value>")
        return
    
    field, value = parts
    valid_fields = ['location', 'timezone', 'favorite_color']
    
    if field not in valid_fields:
        bot.reply(f"Valid fields: {', '.join(valid_fields)}")
        return
    
    bot.db.set_nick_value(trigger.nick, field, value)
    bot.reply(f"Set {field} to: {value}")

@plugin.command('clearprofile') 
def clear_profile(bot, trigger):
    """Clear all profile data."""
    fields = ['location', 'timezone', 'favorite_color']
    
    for field in fields:
        bot.db.delete_nick_value(trigger.nick, field)
    
    bot.reply("Profile data cleared.")

@plugin.command('dbmigrate')
@plugin.require_owner()
def migrate_data(bot, trigger):
    """Migrate old data format to new format."""
    # Example: migrate old single-value storage to structured data
    
    all_nicks = []  # Would get from database query
    migrated_count = 0
    
    for nick in all_nicks:
        old_data = bot.db.get_nick_value(nick, 'old_format_data')
        if old_data:
            # Parse old format and convert
            try:
                # Convert old format to new structured format
                new_data = {'converted': True, 'original': old_data}
                bot.db.set_nick_value(nick, 'new_format_data', new_data)
                bot.db.delete_nick_value(nick, 'old_format_data')
                migrated_count += 1
            except Exception as e:
                bot.say(f"Migration failed for {nick}: {e}")
    
    bot.reply(f"Migrated {migrated_count} user records.")

@plugin.command('dbcleanup')
@plugin.require_owner()
def cleanup_database(bot, trigger):
    """Clean up old or invalid database entries."""
    # Clean up plugin data for unloaded plugins
    active_plugins = list(bot.settings.get('core', {}).get('exclude', []))
    
    # This would require direct database access for complex cleanup
    bot.reply("Database cleanup completed.")

@plugin.command('addtag')
@plugin.example('.addtag python programming')
def add_tag(bot, trigger):
    """Add tags to user profile."""
    if not trigger.group(2):
        bot.reply("Usage: .addtag <tag>")
        return
    
    tag = trigger.group(2).lower()
    
    # Get existing tags list
    tags = bot.db.get_nick_value(trigger.nick, 'tags') or []
    if isinstance(tags, str):
        tags = [tags]  # Handle legacy single tag format
    
    if tag not in tags:
        tags.append(tag)
        bot.db.set_nick_value(trigger.nick, 'tags', tags)
        bot.reply(f"Added tag: {tag}")
    else:
        bot.reply(f"You already have tag: {tag}")

@plugin.command('tags')
def show_tags(bot, trigger):
    """Show user's tags."""
    nick = trigger.group(2) or trigger.nick
    tags = bot.db.get_nick_value(nick, 'tags') or []
    
    if tags:
        tag_list = ', '.join(tags)
        bot.reply(f"Tags for {nick}: {tag_list}")
    else:
        bot.reply(f"{nick} has no tags.")

@plugin.command('findusers')
@plugin.example('.findusers python')
def find_users_by_tag(bot, trigger):
    """Find users with a specific tag."""
    if not trigger.group(2):
        bot.reply("Usage: .findusers <tag>")
        return
    
    search_tag = trigger.group(2).lower()
    
    # This would require a more complex database query
    # For now, showing the concept
    bot.reply(f"Users with tag '{search_tag}': (feature requires database query)")

# SQLite (default)
[core]
db_type = sqlite
db_filename = sopel.db

# MySQL
[core]  
db_type = mysql
db_host = localhost
db_port = 3306
db_user = sopel_user
db_pass = sopel_password
db_name = sopel_db

# PostgreSQL
[core]
db_type = postgresql
db_host = localhost  
db_port = 5432
db_user = sopel_user
db_pass = sopel_password
db_name = sopel_db

# Alternative: specify complete database URL
[core]
db_type = mysql
db_url = mysql://user:password@host:port/database

# PostgreSQL with SSL
db_url = postgresql://user:password@host:port/database?sslmode=require

# SQLite with absolute path
db_url = sqlite:///absolute/path/to/database.db