migrations.py

"""
Database migration script for DBAB Dashboard support
Run this script to add the required tables and columns for the dashboard
"""

from sqlalchemy import text
from database.db import engine
import logging

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)


def run_migrations():
    """Run all database migrations"""
    with engine.connect() as connection:
        try:
            # Create users table
            logger.info("Creating users table...")
            connection.execute(text("""
                CREATE TABLE IF NOT EXISTS users (
                    id VARCHAR(255) PRIMARY KEY,
                    discord_id VARCHAR(255) UNIQUE NOT NULL,
                    discord_tag VARCHAR(255) NOT NULL,
                    avatar VARCHAR(255),
                    email VARCHAR(255),
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                )
            """))
            logger.info("✓ users table created")
            
            # Create guilds table
            logger.info("Creating guilds table...")
            connection.execute(text("""
                CREATE TABLE IF NOT EXISTS guilds (
                    id VARCHAR(255) PRIMARY KEY,
                    name VARCHAR(255) NOT NULL,
                    icon VARCHAR(255),
                    owner_id VARCHAR(255) NOT NULL,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                )
            """))
            logger.info("✓ guilds table created")
            
            # Add columns to appeals table
            logger.info("Updating appeals table...")
            
            # Check and add status column
            connection.execute(text("""
                ALTER TABLE appeals ADD COLUMN IF NOT EXISTS status VARCHAR(50) DEFAULT 'pending'
            """))
            logger.info("✓ status column added to appeals")
            
            # Check and add response column
            connection.execute(text("""
                ALTER TABLE appeals ADD COLUMN IF NOT EXISTS response TEXT
            """))
            logger.info("✓ response column added to appeals")
            
            # Check and add userDiscordTag column
            connection.execute(text("""
                ALTER TABLE appeals ADD COLUMN IF NOT EXISTS userDiscordTag VARCHAR(255)
            """))
            logger.info("✓ userDiscordTag column added to appeals")
            
            connection.commit()
            logger.info("\n✅ All migrations completed successfully!")
            
        except Exception as e:
            logger.error(f"❌ Migration failed: {e}")
            connection.rollback()
            raise


if __name__ == "__main__":
    logger.info("Starting database migrations for DBAB Dashboard...\n")
    run_migrations()