 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/ |
<?php
/**
* Admin Subscription Sync Tool
* Tool to sync subscriptions from Stripe for multiple users
*
* Features:
* - List all users with subscription issues
* - Search users by email, name, or user ID
* - Sync individual users
* - Batch sync multiple users
* - View subscription status comparison
*/
session_start();
require_once __DIR__ . '/config/database.php';
require_once __DIR__ . '/utils/subscription_helpers.php';
// Check if admin
if (!isset($_SESSION['is_admin']) || !$_SESSION['is_admin']) {
die("Admin access required");
}
$pdo = getDBConnection();
$stripe_secret = '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';
// Handle sync action
$sync_result = null;
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['sync_user_id'])) {
$user_id = (int)$_POST['sync_user_id'];
header("Location: /sync_subscription_from_stripe.php?user_id={$user_id}");
exit;
}
// Get search query
$search = $_GET['search'] ?? '';
$filter = $_GET['filter'] ?? 'all'; // all, with_stripe_id, with_subscription, issues
// Build query
$query = "
SELECT u.id, u.name, u.email, u.plan, u.stripe_customer_id,
us.id as subscription_id, us.plan_name, us.status, us.stripe_subscription_id,
us.current_period_end
FROM users u
LEFT JOIN user_subscriptions us ON u.id = us.user_id AND us.status IN ('active', 'trialing')
WHERE 1=1
";
$params = [];
if ($search) {
$query .= " AND (u.email LIKE ? OR u.name LIKE ? OR u.id = ?)";
$search_param = "%{$search}%";
$params[] = $search_param;
$params[] = $search_param;
if (is_numeric($search)) {
$params[] = (int)$search;
} else {
$params[] = 0;
}
}
if ($filter === 'with_stripe_id') {
$query .= " AND u.stripe_customer_id IS NOT NULL AND u.stripe_customer_id != ''";
}
if ($filter === 'with_subscription') {
$query .= " AND us.id IS NOT NULL";
}
if ($filter === 'issues') {
$query .= " AND (
(u.stripe_customer_id IS NOT NULL AND us.id IS NULL) OR
(us.status NOT IN ('active', 'trialing') AND u.plan != 'free')
)";
}
$query .= " ORDER BY u.id DESC LIMIT 100";
$stmt = $pdo->prepare($query);
$stmt->execute($params);
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
$page_title = 'Subscription Sync Tool';
include 'includes/header.php';
?>
<style>
.admin-tool-container {
max-width: 1400px;
margin: 40px auto;
padding: 20px;
}
.tool-header {
background: linear-gradient(135deg, #667eea, #764ba2);
padding: 30px;
border-radius: 16px;
margin-bottom: 30px;
color: white;
}
.tool-header h1 {
margin: 0 0 10px 0;
font-size: 2.5rem;
}
.tool-header p {
margin: 0;
opacity: 0.9;
font-size: 1.1rem;
}
.search-filters {
background: #2a2a2a;
padding: 25px;
border-radius: 12px;
margin-bottom: 30px;
display: flex;
gap: 20px;
flex-wrap: wrap;
align-items: center;
}
.search-input {
flex: 1;
min-width: 300px;
padding: 12px 20px;
background: #1a1a1a;
border: 1px solid rgba(102, 126, 234, 0.3);
border-radius: 8px;
color: white;
font-size: 1rem;
}
.filter-buttons {
display: flex;
gap: 10px;
flex-wrap: wrap;
}
.filter-btn {
padding: 10px 20px;
background: rgba(102, 126, 234, 0.2);
border: 1px solid rgba(102, 126, 234, 0.3);
border-radius: 8px;
color: white;
text-decoration: none;
transition: all 0.3s;
cursor: pointer;
}
.filter-btn:hover, .filter-btn.active {
background: rgba(102, 126, 234, 0.4);
border-color: #667eea;
}
.users-table {
background: #2a2a2a;
border-radius: 12px;
overflow: hidden;
}
.users-table table {
width: 100%;
border-collapse: collapse;
}
.users-table th {
background: #1a1a1a;
padding: 15px;
text-align: left;
color: white;
font-weight: 600;
border-bottom: 2px solid rgba(102, 126, 234, 0.3);
}
.users-table td {
padding: 15px;
border-bottom: 1px solid rgba(255, 255, 255, 0.1);
color: #a0aec0;
}
.users-table tr:hover {
background: rgba(102, 126, 234, 0.1);
}
.user-id {
color: #667eea;
font-weight: 600;
}
.user-email {
color: white;
}
.plan-badge {
display: inline-block;
padding: 4px 12px;
border-radius: 12px;
font-size: 0.85rem;
font-weight: 600;
}
.plan-essential { background: rgba(72, 187, 120, 0.2); color: #48bb78; }
.plan-starter { background: rgba(102, 126, 234, 0.2); color: #667eea; }
.plan-pro { background: rgba(118, 75, 162, 0.2); color: #764ba2; }
.plan-premium { background: rgba(237, 137, 54, 0.2); color: #ed8936; }
.plan-enterprise { background: rgba(237, 137, 54, 0.2); color: #ed8936; }
.plan-free { background: rgba(160, 174, 192, 0.2); color: #a0aec0; }
.status-badge {
display: inline-block;
padding: 4px 12px;
border-radius: 12px;
font-size: 0.85rem;
font-weight: 600;
}
.status-active { background: rgba(72, 187, 120, 0.2); color: #48bb78; }
.status-canceled { background: rgba(229, 62, 62, 0.2); color: #e53e3e; }
.status-past_due { background: rgba(255, 193, 7, 0.2); color: #ffc107; }
.issue-indicator {
color: #ffc107;
font-weight: 600;
}
.sync-btn {
padding: 8px 16px;
background: linear-gradient(135deg, #4299e1, #3182ce);
color: white;
border: none;
border-radius: 8px;
cursor: pointer;
font-weight: 600;
transition: all 0.3s;
text-decoration: none;
display: inline-block;
}
.sync-btn:hover {
transform: translateY(-2px);
box-shadow: 0 4px 12px rgba(66, 153, 225, 0.4);
}
.stats-grid {
display: grid;
grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));
gap: 20px;
margin-bottom: 30px;
}
.stat-card {
background: #2a2a2a;
padding: 20px;
border-radius: 12px;
border: 1px solid rgba(102, 126, 234, 0.2);
}
.stat-value {
font-size: 2.5rem;
font-weight: 800;
color: #667eea;
margin-bottom: 5px;
}
.stat-label {
color: #a0aec0;
font-size: 0.9rem;
}
.no-results {
text-align: center;
padding: 60px 20px;
color: #a0aec0;
}
.no-results i {
font-size: 4rem;
margin-bottom: 20px;
opacity: 0.5;
}
</style>
<div class="admin-tool-container">
<div class="tool-header">
<h1><i class="fas fa-sync-alt"></i> Subscription Sync Tool</h1>
<p>Sync user subscriptions from Stripe to fix database mismatches</p>
</div>
<?php
// Calculate stats
$total_users = count($users);
$with_stripe = 0;
$with_subscription = 0;
$with_issues = 0;
foreach ($users as $user) {
if (!empty($user['stripe_customer_id'])) $with_stripe++;
if (!empty($user['subscription_id'])) $with_subscription++;
if ((!empty($user['stripe_customer_id']) && empty($user['subscription_id'])) ||
(!empty($user['subscription_id']) && !in_array($user['status'], ['active', 'trialing']) && $user['plan'] !== 'free')) {
$with_issues++;
}
}
?>
<div class="stats-grid">
<div class="stat-card">
<div class="stat-value"><?= $total_users ?></div>
<div class="stat-label">Users Found</div>
</div>
<div class="stat-card">
<div class="stat-value"><?= $with_stripe ?></div>
<div class="stat-label">With Stripe ID</div>
</div>
<div class="stat-card">
<div class="stat-value"><?= $with_subscription ?></div>
<div class="stat-label">With Subscription</div>
</div>
<div class="stat-card">
<div class="stat-value"><?= $with_issues ?></div>
<div class="stat-label">Potential Issues</div>
</div>
</div>
<div class="search-filters">
<form method="GET" style="flex: 1; display: flex; gap: 10px;">
<input type="text"
name="search"
class="search-input"
placeholder="Search by email, name, or user ID..."
value="<?= htmlspecialchars($search) ?>">
<input type="hidden" name="filter" value="<?= htmlspecialchars($filter) ?>">
<button type="submit" class="sync-btn" style="white-space: nowrap;">
<i class="fas fa-search"></i> Search
</button>
</form>
<div class="filter-buttons">
<a href="?filter=all<?= $search ? '&search=' . urlencode($search) : '' ?>"
class="filter-btn <?= $filter === 'all' ? 'active' : '' ?>">
All Users
</a>
<a href="?filter=with_stripe_id<?= $search ? '&search=' . urlencode($search) : '' ?>"
class="filter-btn <?= $filter === 'with_stripe_id' ? 'active' : '' ?>">
With Stripe ID
</a>
<a href="?filter=with_subscription<?= $search ? '&search=' . urlencode($search) : '' ?>"
class="filter-btn <?= $filter === 'with_subscription' ? 'active' : '' ?>">
With Subscription
</a>
<a href="?filter=issues<?= $search ? '&search=' . urlencode($search) : '' ?>"
class="filter-btn <?= $filter === 'issues' ? 'active' : '' ?>">
<i class="fas fa-exclamation-triangle"></i> Issues
</a>
</div>
</div>
<div class="users-table">
<?php if (empty($users)): ?>
<div class="no-results">
<i class="fas fa-users"></i>
<h3>No users found</h3>
<p>Try adjusting your search or filter criteria</p>
</div>
<?php else: ?>
<table>
<thead>
<tr>
<th>User ID</th>
<th>Name</th>
<th>Email</th>
<th>DB Plan</th>
<th>Stripe Customer ID</th>
<th>Subscription Status</th>
<th>Subscription Plan</th>
<th>Period End</th>
<th>Action</th>
</tr>
</thead>
<tbody>
<?php foreach ($users as $user): ?>
<?php
$has_issue = false;
$issue_text = '';
if (!empty($user['stripe_customer_id']) && empty($user['subscription_id'])) {
$has_issue = true;
$issue_text = 'Stripe ID but no subscription';
} elseif (!empty($user['subscription_id']) && !in_array($user['status'], ['active', 'trialing']) && $user['plan'] !== 'free') {
$has_issue = true;
$issue_text = 'Mismatched status';
} elseif ($user['plan'] !== ($user['plan_name'] ?? 'free')) {
$has_issue = true;
$issue_text = 'Plan mismatch';
}
?>
<tr>
<td><span class="user-id">#<?= $user['id'] ?></span></td>
<td><?= htmlspecialchars($user['name'] ?? 'N/A') ?></td>
<td><span class="user-email"><?= htmlspecialchars($user['email']) ?></span></td>
<td>
<span class="plan-badge plan-<?= $user['plan'] ?? 'free' ?>">
<?= ucfirst($user['plan'] ?? 'free') ?>
</span>
</td>
<td>
<?php if ($user['stripe_customer_id']): ?>
<code style="font-size: 0.85rem; color: #48bb78;">
<?= htmlspecialchars(substr($user['stripe_customer_id'], 0, 20)) ?>...
</code>
<?php else: ?>
<span style="color: #a0aec0;">None</span>
<?php endif; ?>
</td>
<td>
<?php if ($user['subscription_id']): ?>
<span class="status-badge status-<?= $user['status'] ?? 'unknown' ?>">
<?= ucfirst($user['status'] ?? 'unknown') ?>
</span>
<?php else: ?>
<span style="color: #a0aec0;">No subscription</span>
<?php endif; ?>
</td>
<td>
<?php if ($user['plan_name']): ?>
<span class="plan-badge plan-<?= $user['plan_name'] ?>">
<?= ucfirst($user['plan_name']) ?>
</span>
<?php else: ?>
<span style="color: #a0aec0;">—</span>
<?php endif; ?>
</td>
<td>
<?php if ($user['current_period_end']): ?>
<?= date('M j, Y', strtotime($user['current_period_end'])) ?>
<?php else: ?>
<span style="color: #a0aec0;">—</span>
<?php endif; ?>
</td>
<td>
<?php if ($has_issue): ?>
<span class="issue-indicator" style="margin-right: 10px;">
<i class="fas fa-exclamation-triangle"></i> <?= $issue_text ?>
</span>
<?php endif; ?>
<form method="POST" style="display: inline;">
<input type="hidden" name="sync_user_id" value="<?= $user['id'] ?>">
<button type="submit" class="sync-btn">
<i class="fas fa-sync"></i> Sync
</button>
</form>
</td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
<?php endif; ?>
</div>
<div style="margin-top: 30px; padding: 20px; background: rgba(255, 193, 7, 0.1); border-radius: 12px; border: 2px solid rgba(255, 193, 7, 0.3);">
<h3 style="color: #ffc107; margin-bottom: 15px;"><i class="fas fa-exclamation-triangle"></i> Safety Guarantees</h3>
<ul style="color: #a0aec0; line-height: 2; margin: 0; padding-left: 20px;">
<li><strong style="color: white;">Credits are NEVER modified</strong> - User credits will be preserved</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> - Plan set to 'free' but credits and usage remain intact</li>
<li><strong style="color: white;">Only syncs subscription data</strong> - Plan, status, and track limits from Stripe</li>
</ul>
</div>
<div style="margin-top: 30px; padding: 20px; background: rgba(66, 153, 225, 0.1); border-radius: 12px; border: 1px solid rgba(66, 153, 225, 0.3);">
<h3 style="color: white; margin-bottom: 15px;"><i class="fas fa-info-circle"></i> How to Use This Tool</h3>
<ul style="color: #a0aec0; line-height: 2; margin: 0; padding-left: 20px;">
<li><strong>Search:</strong> Find users by email, name, or user ID</li>
<li><strong>Filter by Issues:</strong> Shows users with potential subscription mismatches</li>
<li><strong>Sync Button:</strong> Click to sync that user's subscription from Stripe</li>
<li><strong>Use Cases:</strong>
<ul style="margin-top: 10px; padding-left: 20px;">
<li>User has Stripe customer ID but no subscription record</li>
<li>Subscription status doesn't match database</li>
<li>Plan changed in Stripe but database wasn't updated</li>
<li>Transaction completed but webhook failed</li>
<li>After refunding a transaction (credits and usage preserved)</li>
</ul>
</li>
</ul>
</div>
</div>
<?php include 'includes/footer.php'; ?>