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
/**
 * Migration Script: Calendar Month to Subscription Period-Based Usage
 * 
 * This script migrates the monthly_track_usage table from calendar month-based
 * tracking to subscription period-based tracking.
 * 
 * Changes:
 * - Adds subscription_period_start field
 * - Migrates existing data to use subscription periods
 * - Updates unique key to use subscription_period_start instead of year_month
 */

require_once __DIR__ . '/config/database.php';

$pdo = getDBConnection();

echo "<h1>Migration: Calendar Month → Subscription Period-Based Usage</h1>";
echo "<style>
    body { font-family: Arial, sans-serif; max-width: 800px; margin: 40px auto; padding: 20px; background: #1a1a1a; color: #fff; }
    .success { color: #48bb78; padding: 10px; background: #2d5016; border-radius: 5px; margin: 10px 0; }
    .error { color: #e53e3e; padding: 10px; background: #5a1a1a; border-radius: 5px; margin: 10px 0; }
    .info { color: #667eea; padding: 10px; background: #2a2a2a; border-radius: 5px; margin: 10px 0; }
</style>";

try {
    $pdo->beginTransaction();
    
    // Step 1: Add subscription_period_start column if it doesn't exist
    echo "<div class='info'>Step 1: Adding subscription_period_start column...</div>";
    try {
        $pdo->exec("ALTER TABLE monthly_track_usage ADD COLUMN subscription_period_start DATETIME NULL AFTER `year_month`");
        echo "<div class='success'>✅ Added subscription_period_start column</div>";
    } catch (Exception $e) {
        if (strpos($e->getMessage(), 'Duplicate column') !== false) {
            echo "<div class='info'>ℹ️ subscription_period_start column already exists</div>";
        } else {
            throw $e;
        }
    }
    
    // Step 2: Add subscription_id column for easier tracking
    echo "<div class='info'>Step 2: Adding subscription_id column...</div>";
    try {
        $pdo->exec("ALTER TABLE monthly_track_usage ADD COLUMN subscription_id INT NULL AFTER user_id");
        echo "<div class='success'>✅ Added subscription_id column</div>";
    } catch (Exception $e) {
        if (strpos($e->getMessage(), 'Duplicate column') !== false) {
            echo "<div class='info'>ℹ️ subscription_id column already exists</div>";
        } else {
            throw $e;
        }
    }
    
    // Step 3: Migrate existing data
    echo "<div class='info'>Step 3: Migrating existing usage records...</div>";
    
    // Get all active subscriptions
    $stmt = $pdo->query("
        SELECT us.id, us.user_id, us.plan_name, us.current_period_start, us.current_period_end
        FROM user_subscriptions us
        WHERE us.status = 'active'
    ");
    $subscriptions = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    $migrated = 0;
    foreach ($subscriptions as $sub) {
        // Find usage records for this user
        $usage_stmt = $pdo->prepare("
            SELECT * FROM monthly_track_usage
            WHERE user_id = ? AND subscription_period_start IS NULL
        ");
        $usage_stmt->execute([$sub['user_id']]);
        $usage_records = $usage_stmt->fetchAll(PDO::FETCH_ASSOC);
        
        foreach ($usage_records as $usage) {
            // Update to use subscription period
            $update_stmt = $pdo->prepare("
                UPDATE monthly_track_usage
                SET subscription_id = ?,
                    subscription_period_start = ?,
                    updated_at = NOW()
                WHERE id = ?
            ");
            $update_stmt->execute([
                $sub['id'],
                $sub['current_period_start'],
                $usage['id']
            ]);
            $migrated++;
        }
    }
    
    echo "<div class='success'>✅ Migrated {$migrated} usage records</div>";
    
    // Step 4: Drop old unique key and create new one
    echo "<div class='info'>Step 4: Updating unique constraints...</div>";
    try {
        // Drop old unique key
        $pdo->exec("ALTER TABLE monthly_track_usage DROP INDEX unique_user_month");
    } catch (Exception $e) {
        echo "<div class='info'>ℹ️ Old unique key doesn't exist or already dropped</div>";
    }
    
    // Create new unique key on user_id + subscription_period_start
    try {
        $pdo->exec("
            ALTER TABLE monthly_track_usage
            ADD UNIQUE KEY unique_user_period (user_id, subscription_period_start)
        ");
        echo "<div class='success'>✅ Created new unique key on (user_id, subscription_period_start)</div>";
    } catch (Exception $e) {
        if (strpos($e->getMessage(), 'Duplicate key') !== false) {
            echo "<div class='info'>ℹ️ Unique key already exists</div>";
        } else {
            throw $e;
        }
    }
    
    // Step 5: Add index for subscription_id
    try {
        $pdo->exec("ALTER TABLE monthly_track_usage ADD INDEX idx_subscription_id (subscription_id)");
        echo "<div class='success'>✅ Added index on subscription_id</div>";
    } catch (Exception $e) {
        if (strpos($e->getMessage(), 'Duplicate key') !== false) {
            echo "<div class='info'>ℹ️ Index already exists</div>";
        } else {
            throw $e;
        }
    }
    
    $pdo->commit();
    
    echo "<div class='success'><h2>✅ Migration Complete!</h2></div>";
    echo "<div class='info'>";
    echo "<p><strong>What changed:</strong></p>";
    echo "<ul>";
    echo "<li>Usage is now tracked per subscription period (not calendar month)</li>";
    echo "<li>Each user's limit resets on their individual billing date</li>";
    echo "<li>year_month field is kept for backward compatibility but subscription_period_start is now primary</li>";
    echo "</ul>";
    echo "</div>";
    
} catch (Exception $e) {
    $pdo->rollBack();
    echo "<div class='error'><h2>❌ Migration Failed</h2>";
    echo "<p>Error: " . htmlspecialchars($e->getMessage()) . "</p>";
    echo "<p>Rolled back all changes.</p></div>";
}