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
require_once 'config/database.php';

$pdo = getDBConnection();

echo "<h2>🛒 Updating Sales Schema for Free User Business Logic</h2>\n";

try {
    // Update sales table to include new revenue tracking columns
    $pdo->exec("
        ALTER TABLE sales 
        ADD COLUMN IF NOT EXISTS revenue_recipient VARCHAR(20) DEFAULT 'artist',
        ADD COLUMN IF NOT EXISTS recipient_id INT,
        ADD COLUMN IF NOT EXISTS is_free_user_track BOOLEAN DEFAULT FALSE,
        ADD COLUMN IF NOT EXISTS quantity INT DEFAULT 1
    ");
    echo "<p>✅ Added revenue tracking columns to sales table</p>\n";
    
    // Create user_library table if it doesn't exist
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS user_library (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            track_id INT NOT NULL,
            purchase_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY unique_library_item (user_id, track_id),
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY (track_id) REFERENCES music_tracks(id) ON DELETE CASCADE
        )
    ");
    echo "<p>✅ Created user_library table</p>\n";
    
    // Add indexes for better performance
    $pdo->exec("
        ALTER TABLE sales 
        ADD INDEX IF NOT EXISTS idx_revenue_recipient (revenue_recipient),
        ADD INDEX IF NOT EXISTS idx_recipient_id (recipient_id),
        ADD INDEX IF NOT EXISTS idx_is_free_user (is_free_user_track)
    ");
    echo "<p>✅ Added performance indexes</p>\n";
    
    // Update existing sales records to set default values
    $pdo->exec("
        UPDATE sales 
        SET revenue_recipient = 'artist', 
            recipient_id = artist_id,
            is_free_user_track = FALSE,
            quantity = 1
        WHERE revenue_recipient IS NULL
    ");
    echo "<p>✅ Updated existing sales records with default values</p>\n";
    
    echo "<h3>📊 Current Sales Table Schema:</h3>\n";
    $result = $pdo->query("DESCRIBE sales");
    echo "<table border='1' style='border-collapse: collapse;'>\n";
    echo "<tr><th>Field</th><th>Type</th><th>Null</th><th>Key</th><th>Default</th></tr>\n";
    while ($row = $result->fetch()) {
        echo "<tr>";
        foreach ($row as $value) {
            echo "<td>" . htmlspecialchars($value) . "</td>";
        }
        echo "</tr>\n";
    }
    echo "</table>\n";
    
    echo "\n<h3>🎯 Business Logic Summary:</h3>\n";
    echo "<ul>\n";
    echo "<li><strong>Free Users:</strong> All sales revenue goes to platform (Admin ID = 1)</li>\n";
    echo "<li><strong>Paid Users:</strong> Sales revenue goes to the artist (85% after commission)</li>\n";
    echo "<li><strong>Revenue Tracking:</strong> Each sale records who gets the money</li>\n";
    echo "<li><strong>User Library:</strong> Purchased tracks are added to buyer's library</li>\n";
    echo "</ul>\n";
    
    echo "\n<p style='color: green; font-weight: bold;'>🎉 Sales schema update completed successfully!</p>\n";
    
} catch (Exception $e) {
    echo "<p style='color: red;'>❌ Error updating schema: " . $e->getMessage() . "</p>\n";
}
?>