 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
/**
* Create Subscription Tables
* Sets up database tables for monthly subscription system
*/
session_start();
require_once 'config/database.php';
// Check if admin
if (!isset($_SESSION['is_admin']) || !$_SESSION['is_admin']) {
die("Admin access required");
}
$pdo = getDBConnection();
echo "<!DOCTYPE html><html><head><meta charset='UTF-8'><title>Create Subscription Tables</title></head><body>";
echo "<style>
body { font-family: Arial; padding: 20px; background: #1a1a1a; color: white; }
.success { color: #48bb78; }
.error { color: #e53e3e; }
.section { margin: 20px 0; padding: 15px; background: #2a2a2a; border-radius: 8px; }
</style>";
echo "<h2>🔧 Creating Subscription Tables</h2>";
$errors = [];
$success = [];
try {
// 1. Modify users.plan ENUM to include 'essential'
echo "<div class='section'>";
echo "<h3>Step 1: Modifying users.plan ENUM</h3>";
try {
// Check current ENUM values
$stmt = $pdo->query("SHOW COLUMNS FROM users WHERE Field = 'plan'");
$column = $stmt->fetch(PDO::FETCH_ASSOC);
$current_type = $column['Type'] ?? '';
if (strpos($current_type, 'essential') === false) {
$pdo->exec("ALTER TABLE users MODIFY COLUMN plan ENUM('free', 'essential', 'starter', 'pro', 'premium') DEFAULT 'free'");
$success[] = "✅ Added 'essential' to users.plan ENUM";
echo "<p class='success'>✅ Added 'essential' to users.plan ENUM</p>";
} else {
$success[] = "✅ 'essential' already exists in users.plan ENUM";
echo "<p class='success'>✅ 'essential' already exists in users.plan ENUM</p>";
}
} catch (Exception $e) {
$errors[] = "Error modifying users.plan: " . $e->getMessage();
echo "<p class='error'>❌ Error: " . htmlspecialchars($e->getMessage()) . "</p>";
}
echo "</div>";
// 2. Create user_subscriptions table
echo "<div class='section'>";
echo "<h3>Step 2: Creating user_subscriptions table</h3>";
try {
$pdo->exec("
CREATE TABLE IF NOT EXISTS user_subscriptions (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
stripe_subscription_id VARCHAR(255) UNIQUE,
stripe_customer_id VARCHAR(255),
plan_name VARCHAR(50) NOT NULL,
status ENUM('active', 'canceled', 'past_due', 'unpaid', 'trialing') DEFAULT 'active',
current_period_start DATETIME,
current_period_end DATETIME,
cancel_at_period_end BOOLEAN DEFAULT FALSE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
INDEX idx_user (user_id),
INDEX idx_stripe_subscription (stripe_subscription_id),
INDEX idx_status (status),
INDEX idx_period_end (current_period_end)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
");
$success[] = "✅ Created user_subscriptions table";
echo "<p class='success'>✅ Created user_subscriptions table</p>";
} catch (Exception $e) {
$errors[] = "Error creating user_subscriptions: " . $e->getMessage();
echo "<p class='error'>❌ Error: " . htmlspecialchars($e->getMessage()) . "</p>";
}
echo "</div>";
// 3. Create monthly_track_usage table
echo "<div class='section'>";
echo "<h3>Step 3: Creating monthly_track_usage table</h3>";
try {
// Check if table exists and has wrong structure
$table_exists = false;
try {
$check_stmt = $pdo->query("SHOW TABLES LIKE 'monthly_track_usage'");
$table_exists = $check_stmt->rowCount() > 0;
} catch (Exception $e) {
// Table doesn't exist, continue
}
if ($table_exists) {
// Try to drop if structure might be wrong
try {
$pdo->exec("DROP TABLE monthly_track_usage");
} catch (Exception $e) {
// If drop fails, try to alter instead
}
}
$pdo->exec("
CREATE TABLE IF NOT EXISTS monthly_track_usage (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
`year_month` VARCHAR(7) NOT NULL,
tracks_created INT DEFAULT 0,
track_limit INT DEFAULT 0,
reset_at DATETIME,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY unique_user_month (user_id, `year_month`),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
INDEX idx_user_month (user_id, `year_month`),
INDEX idx_year_month (`year_month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
");
$success[] = "✅ Created monthly_track_usage table";
echo "<p class='success'>✅ Created monthly_track_usage table</p>";
} catch (Exception $e) {
$errors[] = "Error creating monthly_track_usage: " . $e->getMessage();
echo "<p class='error'>❌ Error: " . htmlspecialchars($e->getMessage()) . "</p>";
}
echo "</div>";
// 4. Check if stripe_customer_id column exists in users table
echo "<div class='section'>";
echo "<h3>Step 4: Checking users table for stripe_customer_id</h3>";
try {
$stmt = $pdo->query("SHOW COLUMNS FROM users LIKE 'stripe_customer_id'");
if ($stmt->rowCount() == 0) {
$pdo->exec("ALTER TABLE users ADD COLUMN stripe_customer_id VARCHAR(255) NULL");
$pdo->exec("CREATE INDEX idx_stripe_customer ON users(stripe_customer_id)");
$success[] = "✅ Added stripe_customer_id column to users table";
echo "<p class='success'>✅ Added stripe_customer_id column to users table</p>";
} else {
$success[] = "✅ stripe_customer_id column already exists";
echo "<p class='success'>✅ stripe_customer_id column already exists</p>";
}
} catch (Exception $e) {
$errors[] = "Error with stripe_customer_id: " . $e->getMessage();
echo "<p class='error'>❌ Error: " . htmlspecialchars($e->getMessage()) . "</p>";
}
echo "</div>";
} catch (Exception $e) {
$errors[] = "Fatal error: " . $e->getMessage();
echo "<p class='error'>❌ Fatal Error: " . htmlspecialchars($e->getMessage()) . "</p>";
}
// Summary
echo "<div class='section'>";
echo "<h3>📊 Summary</h3>";
echo "<p><strong>Success:</strong> " . count($success) . " operations</p>";
echo "<p><strong>Errors:</strong> " . count($errors) . " operations</p>";
if (empty($errors)) {
echo "<p class='success' style='font-size: 18px;'><strong>✅ All database setup completed successfully!</strong></p>";
echo "<p>Next steps:</p>";
echo "<ol>";
echo "<li>Create Stripe Product and Price in Stripe Dashboard</li>";
echo "<li>Get the price_id (starts with 'price_')</li>";
echo "<li>Update subscribe_essential.php with the price_id</li>";
echo "</ol>";
} else {
echo "<p class='error'><strong>⚠️ Some errors occurred. Please review above.</strong></p>";
}
echo "</div>";
echo "<hr>";
echo "<p><a href='/admin.php?tab=purchase-validation' style='color: #667eea;'>← Back to Admin</a></p>";
echo "</body></html>";
?>