 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/public_html/ |
<?php
/**
* Sync Subscription from Stripe
* This script checks if a subscription exists in Stripe but not in the database,
* and creates/updates the database record accordingly.
*
* Usage:
* - Visit: sync_subscription_from_stripe.php?email=stevenberg450@gmail.com
* - Or: sync_subscription_from_stripe.php?customer_id=cus_TU1piJi9qLbFyS
* - Or: sync_subscription_from_stripe.php?user_id=3
*/
require_once __DIR__ . '/config/database.php';
require_once __DIR__ . '/utils/subscription_helpers.php';
$stripe_secret = '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';
header('Content-Type: text/html; charset=utf-8');
?>
<!DOCTYPE html>
<html>
<head>
<title>Sync Subscription from Stripe</title>
<style>
body {
font-family: Arial, sans-serif;
max-width: 1200px;
margin: 0 auto;
padding: 20px;
background: #1a1a1a;
color: #fff;
}
.section {
background: #2a2a2a;
padding: 20px;
margin: 20px 0;
border-radius: 8px;
}
.success { color: #48bb78; }
.error { color: #f56565; }
.info { color: #4299e1; }
.warning { color: #ffc107; }
pre {
background: #1a1a1a;
padding: 15px;
border-radius: 5px;
overflow-x: auto;
}
table {
width: 100%;
border-collapse: collapse;
margin: 10px 0;
}
th, td {
padding: 10px;
text-align: left;
border-bottom: 1px solid #444;
}
th {
background: #333;
}
</style>
</head>
<body>
<h1>Sync Subscription from Stripe</h1>
<div style="background: rgba(66, 153, 225, 0.1); border: 2px solid rgba(66, 153, 225, 0.3); padding: 20px; border-radius: 8px; margin-bottom: 20px;">
<h3 style="color: #4299e1; margin-top: 0;">⚠️ Important Safety Notes</h3>
<ul style="color: #a0aec0; line-height: 1.8;">
<li><strong style="color: white;">Credits are NEVER modified</strong> - User credits will be preserved regardless of subscription status</li>
<li><strong style="color: white;">Track usage is preserved</strong> - Existing tracks_created count will NOT be reset</li>
<li><strong style="color: white;">Refunded subscriptions</strong> - If subscription was refunded, plan will be set to 'free' but credits and usage remain</li>
<li><strong style="color: white;">Only syncs subscription data</strong> - Plan, status, and track limits are updated from Stripe</li>
</ul>
</div>
<?php
$pdo = getDBConnection();
$user_id = null;
$customer_id = null;
$email = $_GET['email'] ?? null;
$customer_id_param = $_GET['customer_id'] ?? null;
$user_id_param = $_GET['user_id'] ?? null;
// Step 1: Find user
echo '<div class="section">';
echo '<h2>Step 1: Finding User</h2>';
if ($user_id_param) {
$user_id = intval($user_id_param);
$stmt = $pdo->prepare("SELECT id, name, email, stripe_customer_id FROM users WHERE id = ?");
$stmt->execute([$user_id]);
$user = $stmt->fetch(PDO::FETCH_ASSOC);
if ($user) {
echo "<p class='success'>✓ Found user: {$user['name']} ({$user['email']})</p>";
$customer_id = $user['stripe_customer_id'];
if ($customer_id) {
echo "<p class='info'>Customer ID in database: {$customer_id}</p>";
} else {
echo "<p class='warning'>⚠ No Stripe customer ID in database</p>";
}
} else {
echo "<p class='error'>✗ User ID {$user_id_param} not found</p>";
echo '</div></body></html>';
exit;
}
} elseif ($customer_id_param) {
$customer_id = $customer_id_param;
$stmt = $pdo->prepare("SELECT id, name, email, stripe_customer_id FROM users WHERE stripe_customer_id = ?");
$stmt->execute([$customer_id]);
$user = $stmt->fetch(PDO::FETCH_ASSOC);
if ($user) {
echo "<p class='success'>✓ Found user: {$user['name']} ({$user['email']})</p>";
$user_id = $user['id'];
} else {
echo "<p class='warning'>⚠ Customer ID {$customer_id} not found in database. Will try to find by email in Stripe.</p>";
}
} elseif ($email) {
$stmt = $pdo->prepare("SELECT id, name, email, stripe_customer_id FROM users WHERE email = ?");
$stmt->execute([$email]);
$user = $stmt->fetch(PDO::FETCH_ASSOC);
if ($user) {
echo "<p class='success'>✓ Found user: {$user['name']} ({$user['email']})</p>";
$user_id = $user['id'];
$customer_id = $user['stripe_customer_id'];
if ($customer_id) {
echo "<p class='info'>Customer ID in database: {$customer_id}</p>";
} else {
echo "<p class='warning'>⚠ No Stripe customer ID in database</p>";
}
} else {
echo "<p class='error'>✗ Email {$email} not found in database</p>";
echo '</div></body></html>';
exit;
}
} else {
echo "<p class='error'>✗ Please provide email, customer_id, or user_id parameter</p>";
echo '<p>Usage examples:</p>';
echo '<ul>';
echo '<li>?email=stevenberg450@gmail.com</li>';
echo '<li>?customer_id=cus_TU1piJi9qLbFyS</li>';
echo '<li>?user_id=3</li>';
echo '</ul>';
echo '</div></body></html>';
exit;
}
echo '</div>';
// Step 2: Fetch subscriptions from Stripe
echo '<div class="section">';
echo '<h2>Step 2: Fetching Subscriptions from Stripe</h2>';
if (!$customer_id) {
// Try to find customer by email in Stripe
echo "<p class='info'>Searching for customer in Stripe by email...</p>";
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, "https://api.stripe.com/v1/customers?email=" . urlencode($user['email']) . "&limit=1");
curl_setopt($ch, CURLOPT_USERPWD, $stripe_secret . ":");
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
$response = curl_exec($ch);
$http_code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
curl_close($ch);
if ($http_code === 200) {
$customers = json_decode($response, true);
if (!empty($customers['data']) && count($customers['data']) > 0) {
$customer_id = $customers['data'][0]['id'];
echo "<p class='success'>✓ Found customer in Stripe: {$customer_id}</p>";
// Update database with customer ID
$stmt = $pdo->prepare("UPDATE users SET stripe_customer_id = ? WHERE id = ?");
$stmt->execute([$customer_id, $user_id]);
echo "<p class='success'>✓ Updated database with customer ID</p>";
} else {
echo "<p class='error'>✗ Customer not found in Stripe by email</p>";
echo '</div></body></html>';
exit;
}
} else {
echo "<p class='error'>✗ Error searching Stripe: HTTP {$http_code}</p>";
echo '<pre>' . htmlspecialchars($response) . '</pre>';
echo '</div></body></html>';
exit;
}
}
// Fetch subscriptions from Stripe
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, "https://api.stripe.com/v1/subscriptions?customer=" . urlencode($customer_id) . "&limit=10");
curl_setopt($ch, CURLOPT_USERPWD, $stripe_secret . ":");
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
$response = curl_exec($ch);
$http_code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
curl_close($ch);
if ($http_code !== 200) {
echo "<p class='error'>✗ Error fetching subscriptions: HTTP {$http_code}</p>";
echo '<pre>' . htmlspecialchars($response) . '</pre>';
echo '</div></body></html>';
exit;
}
$subscriptions_data = json_decode($response, true);
$subscriptions = $subscriptions_data['data'] ?? [];
if (empty($subscriptions)) {
echo "<p class='warning'>⚠ No subscriptions found in Stripe for customer {$customer_id}</p>";
echo '</div></body></html>';
exit;
}
echo "<p class='success'>✓ Found " . count($subscriptions) . " subscription(s) in Stripe</p>";
echo '</div>';
// Step 3: Check database and sync
echo '<div class="section">';
echo '<h2>Step 3: Checking Database and Syncing</h2>';
require_once __DIR__ . '/config/subscription_plans.php';
$plans_config = require __DIR__ . '/config/subscription_plans.php';
foreach ($subscriptions as $stripe_subscription) {
$stripe_sub_id = $stripe_subscription['id'];
$status = $stripe_subscription['status'];
$plan_name = 'essential'; // Default
// Try to determine plan name from price ID
if (!empty($stripe_subscription['items']['data'][0]['price']['id'])) {
$price_id = $stripe_subscription['items']['data'][0]['price']['id'];
echo "<p class='info'>Price ID from Stripe: {$price_id}</p>";
foreach ($plans_config as $plan_key => $plan_data) {
if ($plan_data['stripe_price_id'] === $price_id) {
$plan_name = $plan_key;
echo "<p class='success'>✓ Matched plan: {$plan_name}</p>";
break;
}
}
// If no match found, try to infer from price amount
if ($plan_name === 'essential') {
$price_amount = $stripe_subscription['items']['data'][0]['price']['unit_amount'] ?? 0;
$price_amount = $price_amount / 100; // Convert from cents
echo "<p class='warning'>⚠ Price ID not found in config. Price amount: \${$price_amount}</p>";
// Match by price amount
foreach ($plans_config as $plan_key => $plan_data) {
if (abs($plan_data['price'] - $price_amount) < 0.01) {
$plan_name = $plan_key;
echo "<p class='success'>✓ Matched plan by price: {$plan_name} (\${$plan_data['price']})</p>";
break;
}
}
}
}
// Check if subscription exists in database
$stmt = $pdo->prepare("SELECT * FROM user_subscriptions WHERE stripe_subscription_id = ?");
$stmt->execute([$stripe_sub_id]);
$db_subscription = $stmt->fetch(PDO::FETCH_ASSOC);
// Check for refunds - get recent invoices/charges
$has_refund = false;
$refund_warning = '';
if (in_array($status, ['canceled', 'past_due', 'unpaid'])) {
// Check if there are any refunds for this subscription
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, "https://api.stripe.com/v1/charges?customer=" . urlencode($customer_id) . "&limit=10");
curl_setopt($ch, CURLOPT_USERPWD, $stripe_secret . ":");
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
$charges_response = curl_exec($ch);
$charges_http = curl_getinfo($ch, CURLINFO_HTTP_CODE);
curl_close($ch);
if ($charges_http === 200) {
$charges_data = json_decode($charges_response, true);
foreach ($charges_data['data'] ?? [] as $charge) {
if (isset($charge['refunded']) && $charge['refunded'] === true) {
$has_refund = true;
$refund_warning = "⚠️ <strong>WARNING:</strong> This subscription has refunded charges. Credits and track usage will NOT be reset.";
break;
}
}
}
}
echo "<h3>Subscription: {$stripe_sub_id}</h3>";
if ($has_refund) {
echo "<div style='background: #ffc107; color: #000; padding: 15px; border-radius: 8px; margin-bottom: 15px; border: 2px solid #ff9800;'>";
echo "<p style='margin: 0; font-weight: bold;'>{$refund_warning}</p>";
echo "<p style='margin: 10px 0 0 0;'>The subscription will be set to 'free' plan, but existing credits and track usage will be preserved.</p>";
echo "</div>";
}
echo "<table>";
echo "<tr><th>Field</th><th>Stripe Value</th><th>Database Value</th><th>Action</th></tr>";
$period_start = date('Y-m-d H:i:s', $stripe_subscription['current_period_start']);
$period_end = date('Y-m-d H:i:s', $stripe_subscription['current_period_end']);
echo "<tr><td>Plan</td><td>{$plan_name}</td><td>" . ($db_subscription['plan_name'] ?? 'N/A') . "</td><td>-</td></tr>";
echo "<tr><td>Status</td><td>{$status}</td><td>" . ($db_subscription['status'] ?? 'N/A') . "</td><td>-</td></tr>";
echo "<tr><td>Period Start</td><td>{$period_start}</td><td>" . ($db_subscription['current_period_start'] ?? 'N/A') . "</td><td>-</td></tr>";
echo "<tr><td>Period End</td><td>{$period_end}</td><td>" . ($db_subscription['current_period_end'] ?? 'N/A') . "</td><td>-</td></tr>";
echo "<tr><td>User ID</td><td>{$user_id}</td><td>" . ($db_subscription['user_id'] ?? 'N/A') . "</td><td>-</td></tr>";
echo "</table>";
if (!$db_subscription) {
// Create subscription record
echo "<p class='warning'>⚠ Subscription not found in database. Creating record...</p>";
$track_limit = $plans_config[$plan_name]['tracks_per_month'] ?? 5;
$pdo->beginTransaction();
try {
$stmt = $pdo->prepare("
INSERT INTO user_subscriptions (
user_id, stripe_subscription_id, stripe_customer_id, plan_name, status,
current_period_start, current_period_end, created_at
) VALUES (?, ?, ?, ?, ?, ?, ?, NOW())
");
$stmt->execute([
$user_id,
$stripe_sub_id,
$customer_id,
$plan_name,
$status,
$period_start,
$period_end
]);
$subscription_id = $pdo->lastInsertId();
// Update user plan
$stmt = $pdo->prepare("UPDATE users SET plan = ?, stripe_customer_id = COALESCE(stripe_customer_id, ?) WHERE id = ?");
$stmt->execute([$plan_name, $customer_id, $user_id]);
// Initialize monthly track usage - PRESERVE existing tracks_created
$year_month = date('Y-m', $stripe_subscription['current_period_start']);
// Check if usage record already exists
$check_stmt = $pdo->prepare("
SELECT tracks_created FROM monthly_track_usage
WHERE user_id = ? AND subscription_period_start = ?
");
$check_stmt->execute([$user_id, $period_start]);
$existing_usage = $check_stmt->fetch(PDO::FETCH_ASSOC);
$preserve_tracks = $existing_usage ? (int)$existing_usage['tracks_created'] : 0;
$stmt = $pdo->prepare("
INSERT INTO monthly_track_usage (
user_id, subscription_id, subscription_period_start,
`year_month`, tracks_created, track_limit, reset_at
)
VALUES (?, ?, ?, ?, ?, ?, NOW())
ON DUPLICATE KEY UPDATE
track_limit = VALUES(track_limit),
tracks_created = COALESCE(tracks_created, VALUES(tracks_created))
");
$stmt->execute([
$user_id,
$subscription_id,
$period_start,
$year_month,
$preserve_tracks, // Preserve existing tracks, don't reset to 0
$track_limit
]);
$pdo->commit();
echo "<p class='success'>✓ Successfully created subscription record in database!</p>";
} catch (Exception $e) {
$pdo->rollBack();
echo "<p class='error'>✗ Error creating subscription record: " . htmlspecialchars($e->getMessage()) . "</p>";
}
} else {
// Update existing record
echo "<p class='info'>ℹ Subscription exists in database. Updating...</p>";
// Check if plan changed
$old_plan = $db_subscription['plan_name'];
$plan_changed = ($old_plan !== $plan_name);
if ($plan_changed) {
echo "<p class='warning'>⚠ Plan change detected: {$old_plan} → {$plan_name}</p>";
}
$stmt = $pdo->prepare("
UPDATE user_subscriptions
SET status = ?,
plan_name = ?,
current_period_start = ?,
current_period_end = ?,
updated_at = NOW()
WHERE stripe_subscription_id = ?
");
$stmt->execute([
$status,
$plan_name,
$period_start,
$period_end,
$stripe_sub_id
]);
// Get current user credits BEFORE updating (to preserve them)
$credits_stmt = $pdo->prepare("SELECT credits FROM users WHERE id = ?");
$credits_stmt->execute([$user_id]);
$user_credits = $credits_stmt->fetch(PDO::FETCH_ASSOC);
$current_credits = $user_credits ? (int)$user_credits['credits'] : 0;
// Update user plan if subscription is active
if ($status === 'active' || $status === 'trialing') {
// Only update plan, NEVER touch credits
$stmt = $pdo->prepare("UPDATE users SET plan = ? WHERE id = ?");
$stmt->execute([$plan_name, $user_id]);
echo "<p class='success'>✓ Updated user plan to {$plan_name}</p>";
echo "<p class='info'>ℹ User credits preserved: {$current_credits} credits (not modified)</p>";
} else {
// If subscription is canceled/past_due, set user to free plan
// BUT preserve credits and track usage
$stmt = $pdo->prepare("UPDATE users SET plan = 'free' WHERE id = ?");
$stmt->execute([$user_id]);
// Also update the subscription record's plan_name to 'free' for consistency
$update_sub_stmt = $pdo->prepare("
UPDATE user_subscriptions
SET plan_name = 'free',
updated_at = NOW()
WHERE stripe_subscription_id = ?
");
$update_sub_stmt->execute([$stripe_sub_id]);
echo "<p class='warning'>⚠ Subscription status is '{$status}', user plan and subscription plan_name set to 'free'</p>";
echo "<p class='info'>ℹ User credits preserved: {$current_credits} credits (not modified)</p>";
echo "<p class='info'>ℹ Track usage preserved (tracks_created not reset)</p>";
}
// Update track limit if plan changed and subscription is active
// BUT preserve existing tracks_created count
if ($plan_changed && ($status === 'active' || $status === 'trialing')) {
$track_limit = $plans_config[$plan_name]['tracks_per_month'] ?? 5;
// Get current tracks_created to preserve it
$usage_stmt = $pdo->prepare("
SELECT tracks_created FROM monthly_track_usage
WHERE user_id = ? AND subscription_id = ?
");
$usage_stmt->execute([$user_id, $db_subscription['id']]);
$current_usage = $usage_stmt->fetch(PDO::FETCH_ASSOC);
$preserve_tracks = $current_usage ? (int)$current_usage['tracks_created'] : 0;
$stmt = $pdo->prepare("
UPDATE monthly_track_usage
SET track_limit = ?,
tracks_created = COALESCE(tracks_created, ?),
updated_at = NOW()
WHERE user_id = ? AND subscription_id = ?
");
$stmt->execute([
$track_limit,
$preserve_tracks, // Preserve existing count
$user_id,
$db_subscription['id']
]);
echo "<p class='success'>✓ Updated track limit to {$track_limit} for current period</p>";
echo "<p class='info'>ℹ Preserved existing track usage: {$preserve_tracks} tracks created</p>";
}
echo "<p class='success'>✓ Successfully updated subscription record!</p>";
}
}
echo '</div>';
// Step 4: Verify
echo '<div class="section">';
echo '<h2>Step 4: Verification</h2>';
$stmt = $pdo->prepare("SELECT * FROM user_subscriptions WHERE user_id = ? ORDER BY created_at DESC");
$stmt->execute([$user_id]);
$db_subscriptions = $stmt->fetchAll(PDO::FETCH_ASSOC);
if (empty($db_subscriptions)) {
echo "<p class='error'>✗ No subscriptions found in database after sync</p>";
} else {
echo "<p class='success'>✓ Found " . count($db_subscriptions) . " subscription(s) in database:</p>";
echo "<table>";
echo "<tr><th>ID</th><th>Plan</th><th>Status</th><th>Period End</th><th>Stripe ID</th></tr>";
foreach ($db_subscriptions as $sub) {
echo "<tr>";
echo "<td>{$sub['id']}</td>";
echo "<td>{$sub['plan_name']}</td>";
echo "<td>{$sub['status']}</td>";
echo "<td>{$sub['current_period_end']}</td>";
echo "<td>{$sub['stripe_subscription_id']}</td>";
echo "</tr>";
}
echo "</table>";
}
echo '</div>';
echo '<div class="section">';
echo '<h2>Next Steps</h2>';
echo '<p>1. Visit <a href="account_settings.php?tab=credits" style="color: #4299e1;">account_settings.php?tab=credits</a> to see the subscription</p>';
echo '<p>2. Visit <a href="account_settings.php?tab=subscription" style="color: #4299e1;">account_settings.php?tab=subscription</a> to manage the subscription</p>';
echo '<p>3. Visit <a href="profile.php" style="color: #4299e1;">profile.php</a> to see subscription on profile</p>';
echo '</div>';
?>
</body>
</html>