 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/cron/ |
<?php
/**
* Daily Subscription Sync from Stripe
*
* This cron job syncs all active subscriptions from Stripe to the database.
* It runs daily to catch any subscriptions that were missed due to webhook failures.
*
* Usage: Run hourly via cron (for faster recovery)
* 0 * * * * /usr/bin/php /home/gositeme/domains/soundstudiopro.com/public_html/cron/sync_subscriptions_from_stripe.php
*
* Or run every 6 hours if hourly is too frequent:
* 0 */6 * * * /usr/bin/php /home/gositeme/domains/soundstudiopro.com/public_html/cron/sync_subscriptions_from_stripe.php
*/
require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/../utils/subscription_helpers.php';
require_once __DIR__ . '/../config/subscription_plans.php';
$stripe_secret = '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';
$plans_config = require __DIR__ . '/../config/subscription_plans.php';
$pdo = getDBConnection();
$log_file = __DIR__ . '/../logs/subscription_sync.log';
// Log start
$log_entry = date('Y-m-d H:i:s') . " - Starting subscription sync from Stripe\n";
file_put_contents($log_file, $log_entry, FILE_APPEND | LOCK_EX);
// Get all users with Stripe customer IDs
$stmt = $pdo->query("SELECT id, email, stripe_customer_id FROM users WHERE stripe_customer_id IS NOT NULL AND stripe_customer_id != ''");
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
$total_users = count($users);
$synced_count = 0;
$error_count = 0;
foreach ($users as $user) {
try {
$customer_id = $user['stripe_customer_id'];
// Fetch subscriptions from Stripe
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, "https://api.stripe.com/v1/subscriptions?customer=" . urlencode($customer_id) . "&limit=10&status=all");
curl_setopt($ch, CURLOPT_HTTPHEADER, ['Authorization: Bearer ' . $stripe_secret]);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
$response = curl_exec($ch);
$http_code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
curl_close($ch);
if ($http_code !== 200) {
$error_count++;
$log_entry = date('Y-m-d H:i:s') . " - Error fetching subscriptions for user {$user['id']} ({$user['email']}): HTTP {$http_code}\n";
file_put_contents($log_file, $log_entry, FILE_APPEND | LOCK_EX);
continue;
}
$subscriptions_data = json_decode($response, true);
$subscriptions = $subscriptions_data['data'] ?? [];
if (empty($subscriptions)) {
continue; // No subscriptions for this user
}
// Process each subscription
foreach ($subscriptions as $stripe_sub) {
$stripe_sub_id = $stripe_sub['id'];
$status = $stripe_sub['status'];
// Only sync active/trialing subscriptions
if (!in_array($status, ['active', 'trialing'])) {
continue;
}
// Determine plan name
$plan_name = 'essential'; // Default
$price_id = null;
// Primary: Get plan from price ID
if (!empty($stripe_sub['items']['data'][0]['price']['id'])) {
$price_id = $stripe_sub['items']['data'][0]['price']['id'];
foreach ($plans_config as $plan_key => $plan_data) {
if (isset($plan_data['stripe_price_id']) && $plan_data['stripe_price_id'] === $price_id) {
$plan_name = $plan_key;
break;
}
}
// If no match, try by price amount
if ($plan_name === 'essential') {
$price_amount = $stripe_sub['items']['data'][0]['price']['unit_amount'] ?? 0;
$price_amount = $price_amount / 100;
foreach ($plans_config as $plan_key => $plan_data) {
if (abs($plan_data['price'] - $price_amount) < 0.01) {
$plan_name = $plan_key;
break;
}
}
}
}
// Check if subscription exists in database
$check_stmt = $pdo->prepare("SELECT id, plan_name, status FROM user_subscriptions WHERE stripe_subscription_id = ?");
$check_stmt->execute([$stripe_sub_id]);
$db_sub = $check_stmt->fetch(PDO::FETCH_ASSOC);
$period_start = date('Y-m-d H:i:s', $stripe_sub['current_period_start']);
$period_end = date('Y-m-d H:i:s', $stripe_sub['current_period_end']);
$pdo->beginTransaction();
try {
if (!$db_sub) {
// Create new subscription record
$track_limit = $plans_config[$plan_name]['tracks_per_month'] ?? 5;
$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();
// Initialize monthly track usage
$year_month = date('Y-m', $stripe_sub['current_period_start']);
$usage_stmt = $pdo->prepare("
INSERT INTO monthly_track_usage (
user_id, subscription_id, subscription_period_start,
`year_month`, tracks_created, track_limit, reset_at
)
VALUES (?, ?, ?, ?, 0, ?, NOW())
ON DUPLICATE KEY UPDATE
track_limit = VALUES(track_limit),
reset_at = NOW()
");
$usage_stmt->execute([
$user['id'],
$subscription_id,
$period_start,
$year_month,
$track_limit
]);
$log_entry = date('Y-m-d H:i:s') . " - Created subscription for user {$user['id']}: {$plan_name} (Stripe: {$stripe_sub_id})\n";
file_put_contents($log_file, $log_entry, FILE_APPEND | LOCK_EX);
} else {
// Update if status changed or plan updated
$needs_update = false;
$update_fields = [];
if ($db_sub['status'] !== $status) {
$update_fields[] = "status = ?";
$needs_update = true;
}
if ($db_sub['plan_name'] !== $plan_name) {
$update_fields[] = "plan_name = ?";
$needs_update = true;
}
if ($needs_update) {
$update_values = [];
if (in_array("status = ?", $update_fields)) {
$update_values[] = $status;
}
if (in_array("plan_name = ?", $update_fields)) {
$update_values[] = $plan_name;
}
$update_values[] = $stripe_sub_id;
$update_sql = "UPDATE user_subscriptions SET " . implode(", ", $update_fields) . ", updated_at = NOW() WHERE stripe_subscription_id = ?";
$update_stmt = $pdo->prepare($update_sql);
$update_stmt->execute($update_values);
$log_entry = date('Y-m-d H:i:s') . " - Updated subscription for user {$user['id']}: {$plan_name} (Stripe: {$stripe_sub_id})\n";
file_put_contents($log_file, $log_entry, FILE_APPEND | LOCK_EX);
}
}
// Update user plan if subscription is active
if (in_array($status, ['active', 'trialing'])) {
$user_plan_stmt = $pdo->prepare("UPDATE users SET plan = ? WHERE id = ?");
$user_plan_stmt->execute([$plan_name, $user['id']]);
}
$pdo->commit();
$synced_count++;
} catch (Exception $e) {
$pdo->rollBack();
$error_count++;
$log_entry = date('Y-m-d H:i:s') . " - Error syncing subscription {$stripe_sub_id} for user {$user['id']}: " . $e->getMessage() . "\n";
file_put_contents($log_file, $log_entry, FILE_APPEND | LOCK_EX);
}
}
} catch (Exception $e) {
$error_count++;
$log_entry = date('Y-m-d H:i:s') . " - Error processing user {$user['id']} ({$user['email']}): " . $e->getMessage() . "\n";
file_put_contents($log_file, $log_entry, FILE_APPEND | LOCK_EX);
}
}
// Log summary
$log_entry = date('Y-m-d H:i:s') . " - Sync complete. Users processed: {$total_users}, Subscriptions synced: {$synced_count}, Errors: {$error_count}\n";
file_put_contents($log_file, $log_entry, FILE_APPEND | LOCK_EX);
echo "Sync complete. Check {$log_file} for details.\n";