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
/**
 * Verification Script: Check Subscription Period-Based Setup
 * Verifies that the migration was successful and system is ready
 */

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

$pdo = getDBConnection();

echo "<h1>Subscription Period-Based System Verification</h1>";
echo "<style>
    body { font-family: Arial, sans-serif; max-width: 900px; 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; }
    .warning { color: #ffc107; padding: 10px; background: #5a4a00; border-radius: 5px; margin: 10px 0; }
    table { width: 100%; border-collapse: collapse; margin: 20px 0; background: #2a2a2a; }
    th, td { padding: 12px; text-align: left; border-bottom: 1px solid #444; }
    th { background: #333; color: #667eea; }
</style>";

$all_good = true;

// Check 1: Database schema
echo "<h2>1. Database Schema Check</h2>";

try {
    $stmt = $pdo->query("DESCRIBE monthly_track_usage");
    $columns = $stmt->fetchAll(PDO::FETCH_COLUMN);
    
    $required_columns = ['subscription_period_start', 'subscription_id'];
    $missing = [];
    
    foreach ($required_columns as $col) {
        if (!in_array($col, $columns)) {
            $missing[] = $col;
            $all_good = false;
        }
    }
    
    if (empty($missing)) {
        echo "<div class='success'>✅ All required columns exist</div>";
        echo "<div class='info'>Found columns: " . implode(', ', $required_columns) . "</div>";
    } else {
        echo "<div class='error'>❌ Missing columns: " . implode(', ', $missing) . "</div>";
    }
} catch (Exception $e) {
    echo "<div class='error'>❌ Error checking schema: " . htmlspecialchars($e->getMessage()) . "</div>";
    $all_good = false;
}

// Check 2: Active subscriptions
echo "<h2>2. Active Subscriptions</h2>";

try {
    $stmt = $pdo->query("
        SELECT 
            us.id,
            us.user_id,
            us.plan_name,
            us.status,
            us.current_period_start,
            us.current_period_end,
            u.email
        FROM user_subscriptions us
        LEFT JOIN users u ON us.user_id = u.id
        WHERE us.status = 'active'
        ORDER BY us.created_at DESC
    ");
    $subscriptions = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    if (empty($subscriptions)) {
        echo "<div class='warning'>⚠️ No active subscriptions found</div>";
    } else {
        echo "<div class='success'>✅ Found " . count($subscriptions) . " active subscription(s)</div>";
        echo "<table>";
        echo "<tr><th>ID</th><th>User</th><th>Plan</th><th>Period Start</th><th>Period End</th><th>Usage Record</th></tr>";
        
        foreach ($subscriptions as $sub) {
            // Check if usage record exists for this period
            $period_start = $sub['current_period_start'];
            if (is_numeric($period_start)) {
                $period_start = date('Y-m-d H:i:s', $period_start);
            }
            
            $usage_stmt = $pdo->prepare("
                SELECT id, tracks_created, track_limit 
                FROM monthly_track_usage 
                WHERE user_id = ? AND subscription_period_start = ?
            ");
            $usage_stmt->execute([$sub['user_id'], $period_start]);
            $usage = $usage_stmt->fetch(PDO::FETCH_ASSOC);
            
            $has_usage = $usage ? '✅' : '❌';
            $usage_info = $usage ? "{$usage['tracks_created']}/{$usage['track_limit']}" : 'Missing';
            
            echo "<tr>";
            echo "<td>{$sub['id']}</td>";
            echo "<td>{$sub['email']} (ID: {$sub['user_id']})</td>";
            echo "<td>" . ucfirst($sub['plan_name']) . "</td>";
            echo "<td>" . date('M j, Y', strtotime($period_start)) . "</td>";
            echo "<td>" . date('M j, Y', strtotime($sub['current_period_end'])) . "</td>";
            echo "<td>{$has_usage} {$usage_info}</td>";
            echo "</tr>";
            
            if (!$usage) {
                $all_good = false;
            }
        }
        echo "</table>";
    }
} catch (Exception $e) {
    echo "<div class='error'>❌ Error checking subscriptions: " . htmlspecialchars($e->getMessage()) . "</div>";
    $all_good = false;
}

// Check 3: Usage records
echo "<h2>3. Usage Records</h2>";

try {
    $stmt = $pdo->query("
        SELECT 
            COUNT(*) as total,
            COUNT(CASE WHEN subscription_period_start IS NOT NULL THEN 1 END) as with_period,
            COUNT(CASE WHEN subscription_period_start IS NULL THEN 1 END) as without_period
        FROM monthly_track_usage
    ");
    $stats = $stmt->fetch(PDO::FETCH_ASSOC);
    
    echo "<div class='info'>";
    echo "Total usage records: {$stats['total']}<br>";
    echo "With subscription_period_start: {$stats['with_period']}<br>";
    echo "Without subscription_period_start: {$stats['without_period']}<br>";
    echo "</div>";
    
    if ($stats['without_period'] > 0) {
        echo "<div class='warning'>⚠️ Found {$stats['without_period']} usage record(s) without subscription_period_start</div>";
        echo "<div class='info'>These are old calendar-month records. They won't interfere but new records will use periods.</div>";
    } else {
        echo "<div class='success'>✅ All usage records have subscription_period_start</div>";
    }
} catch (Exception $e) {
    echo "<div class='error'>❌ Error checking usage records: " . htmlspecialchars($e->getMessage()) . "</div>";
    $all_good = false;
}

// Check 4: Unique constraints
echo "<h2>4. Database Constraints</h2>";

try {
    $stmt = $pdo->query("SHOW INDEXES FROM monthly_track_usage WHERE Key_name = 'unique_user_period'");
    $index = $stmt->fetch(PDO::FETCH_ASSOC);
    
    if ($index) {
        echo "<div class='success'>✅ Unique constraint 'unique_user_period' exists</div>";
    } else {
        echo "<div class='error'>❌ Unique constraint 'unique_user_period' is missing</div>";
        $all_good = false;
    }
} catch (Exception $e) {
    echo "<div class='error'>❌ Error checking constraints: " . htmlspecialchars($e->getMessage()) . "</div>";
    $all_good = false;
}

// Final summary
echo "<h2>Summary</h2>";

if ($all_good) {
    echo "<div class='success'><h2>✅ System is Ready!</h2>";
    echo "<p>All checks passed. The subscription period-based system is properly configured.</p>";
    echo "<p><strong>Next steps:</strong></p>";
    echo "<ul>";
    echo "<li>Update cron job schedule to run daily (0 2 * * *) instead of monthly</li>";
    echo "<li>Test creating a new subscription</li>";
    echo "<li>Verify webhook is receiving subscription events</li>";
    echo "</ul>";
    echo "</div>";
} else {
    echo "<div class='error'><h2>⚠️ Issues Found</h2>";
    echo "<p>Please review the issues above and fix them before using the system.</p>";
    echo "</div>";
}