Server : Apache/2 System : Linux server-15-235-50-60 5.15.0-164-generic #174-Ubuntu SMP Fri Nov 14 20:25:16 UTC 2025 x86_64 User : gositeme ( 1004) PHP Version : 8.2.29 Disable Function : exec,system,passthru,shell_exec,proc_close,proc_open,dl,popen,show_source,posix_kill,posix_mkfifo,posix_getpwuid,posix_setpgid,posix_setsid,posix_setuid,posix_setgid,posix_seteuid,posix_setegid,posix_uname Directory :  /home/gositeme/domains/soundstudiopro.com/private_html/

Upload File : current_dir [ Writeable ] document_root [ Writeable ]

<?php
// Database Update Runner
// This script safely applies the database updates for the credit system

echo "🔧 Running Database Updates for Credit System...\n\n";

try {
    require_once 'config/database.php';
    $pdo = getDBConnection();
    echo "✅ Database connection successful!\n\n";
    
    // Start transaction
    $pdo->beginTransaction();
    
    $updates = [
        // 1. Add columns to users table
        "ALTER TABLE users ADD COLUMN IF NOT EXISTS credits INT DEFAULT 0" => "Add credits column",
        "ALTER TABLE users ADD COLUMN IF NOT EXISTS subscription_expires DATETIME NULL" => "Add subscription_expires column",
        "ALTER TABLE users ADD COLUMN IF NOT EXISTS commercial_rights_expires DATETIME NULL" => "Add commercial_rights_expires column",
        "ALTER TABLE users ADD COLUMN IF NOT EXISTS expiration_warning_sent TINYINT(1) DEFAULT 0" => "Add expiration_warning_sent column",
        "ALTER TABLE users ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" => "Add updated_at column",
        
        // 2. Create credit_purchases table
        "CREATE TABLE IF NOT EXISTS credit_purchases (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            package VARCHAR(50) NOT NULL,
            credits INT NOT NULL,
            amount DECIMAL(10,2) NOT NULL,
            payment_intent_id VARCHAR(255) NOT NULL,
            expires_at DATETIME NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_user_expires (user_id, expires_at),
            INDEX idx_expires (expires_at)
        )" => "Create credit_purchases table",
        
        // 3. Create credit_usage table
        "CREATE TABLE IF NOT EXISTS credit_usage (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            track_id VARCHAR(255) NOT NULL,
            credits_used INT DEFAULT 1,
            usage_type ENUM('download', 'preview', 'commercial') DEFAULT 'download',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_user_usage (user_id, created_at)
        )" => "Create credit_usage table",
        
        // 4. Add columns to track_purchases table
        "ALTER TABLE track_purchases ADD COLUMN IF NOT EXISTS payment_method VARCHAR(20) DEFAULT 'credits'" => "Add payment_method to track_purchases",
        "ALTER TABLE track_purchases ADD COLUMN IF NOT EXISTS stripe_payment_intent_id VARCHAR(255)" => "Add stripe_payment_intent_id to track_purchases",
        
        // 5. Add columns to credit_transactions table
        "ALTER TABLE credit_transactions ADD COLUMN IF NOT EXISTS stripe_payment_intent_id VARCHAR(255)" => "Add stripe_payment_intent_id to credit_transactions",
        
        // 6. Add indexes for better performance
        "CREATE INDEX IF NOT EXISTS idx_users_credits ON users(credits)" => "Add credits index",
        "CREATE INDEX IF NOT EXISTS idx_users_subscription_expires ON users(subscription_expires)" => "Add subscription_expires index",
        "CREATE INDEX IF NOT EXISTS idx_users_commercial_rights_expires ON users(commercial_rights_expires)" => "Add commercial_rights_expires index"
    ];
    
    $success_count = 0;
    $error_count = 0;
    
    foreach ($updates as $sql => $description) {
        try {
            $stmt = $pdo->prepare($sql);
            $stmt->execute();
            echo "✅ $description\n";
            $success_count++;
        } catch (Exception $e) {
            // Check if it's a "duplicate column" error (which is okay)
            if (strpos($e->getMessage(), 'Duplicate column name') !== false || 
                strpos($e->getMessage(), 'Duplicate key name') !== false) {
                echo "ℹ️  $description (already exists)\n";
                $success_count++;
            } else {
                echo "❌ $description failed: " . $e->getMessage() . "\n";
                $error_count++;
            }
        }
    }
    
    // Commit transaction
    $pdo->commit();
    
    echo "\n📊 Update Summary:\n";
    echo "✅ Successful updates: $success_count\n";
    echo "❌ Failed updates: $error_count\n";
    
    if ($error_count == 0) {
        echo "\n🎉 All database updates completed successfully!\n";
        
        // Test the updates
        echo "\n🧪 Testing updated database structure...\n";
        
        // Test users table
        $stmt = $pdo->query("DESCRIBE users");
        $columns = $stmt->fetchAll(PDO::FETCH_COLUMN);
        
        $required_columns = ['credits', 'subscription_expires', 'commercial_rights_expires', 'expiration_warning_sent'];
        $missing_columns = [];
        
        foreach ($required_columns as $column) {
            if (!in_array($column, $columns)) {
                $missing_columns[] = $column;
            }
        }
        
        if (empty($missing_columns)) {
            echo "✅ Users table has all required credit columns\n";
        } else {
            echo "❌ Still missing columns: " . implode(', ', $missing_columns) . "\n";
        }
        
        // Test credit_purchases table
        try {
            $stmt = $pdo->query("SELECT COUNT(*) FROM credit_purchases LIMIT 1");
            echo "✅ credit_purchases table exists and accessible\n";
        } catch (Exception $e) {
            echo "❌ credit_purchases table test failed: " . $e->getMessage() . "\n";
        }
        
        // Test credit_usage table
        try {
            $stmt = $pdo->query("SELECT COUNT(*) FROM credit_usage LIMIT 1");
            echo "✅ credit_usage table exists and accessible\n";
        } catch (Exception $e) {
            echo "❌ credit_usage table test failed: " . $e->getMessage() . "\n";
        }
        
        echo "\n🚀 Database is ready for the credit system!\n";
        echo "\n📝 Next steps:\n";
        echo "1. Run ./setup_cron.sh to set up the cron job\n";
        echo "2. Test the cron job: php cron/expire_credits.php\n";
        echo "3. Test track purchases on the artists page\n";
        
    } else {
        echo "\n⚠️  Some updates failed. Please check the errors above.\n";
        echo "You may need to run some updates manually.\n";
    }
    
} catch (Exception $e) {
    if (isset($pdo)) {
        $pdo->rollBack();
    }
    echo "❌ Database update failed: " . $e->getMessage() . "\n";
    echo "\n🔧 Please check your database configuration and try again.\n";
}
?>