 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/config/ |
<?php
/**
* Database configuration for SoundStudioPro
*
* SECURITY: This file now supports environment variables and external config files.
* Priority order:
* 1. Environment variables (if set)
* 2. External database.env.php file (if exists)
* 3. Fallback to hardcoded values (for backward compatibility)
*
* RECOMMENDED: Create config/database.env.php with your credentials
* and ensure it's protected by .htaccess
*/
// Check for environment variables first (most secure)
$db_host = $_ENV['DB_HOST'] ?? getenv('DB_HOST') ?: null;
$db_name = $_ENV['DB_NAME'] ?? getenv('DB_NAME') ?: null;
$db_user = $_ENV['DB_USER'] ?? getenv('DB_USER') ?: null;
$db_pass = $_ENV['DB_PASS'] ?? getenv('DB_PASS') ?: null;
// If environment variables not set, try to load from external config file
if (!$db_host || !$db_name || !$db_user || !$db_pass) {
$env_file = __DIR__ . '/database.env.php';
if (file_exists($env_file)) {
// Include external config file (should contain define() statements)
require_once $env_file;
// Re-check if values are now defined
$db_host = defined('DB_HOST') ? DB_HOST : null;
$db_name = defined('DB_NAME') ? DB_NAME : null;
$db_user = defined('DB_USER') ? DB_USER : null;
$db_pass = defined('DB_PASS') ? DB_PASS : null;
}
}
// Fallback to hardcoded values (for backward compatibility - NOT RECOMMENDED for production)
// TODO: Remove these hardcoded values and use environment variables or external config file
if (!$db_host || !$db_name || !$db_user || !$db_pass) {
// Log warning about using fallback credentials
error_log("WARNING: Using fallback database credentials. Please configure environment variables or database.env.php");
define('DB_HOST', 'localhost');
define('DB_NAME', 'gositeme_soundstudiopro');
define('DB_USER', 'gositeme_soundstudiopro');
define('DB_PASS', 'ttkKaHQunYYwgLCn6GxZ');
} else {
// Use values from environment or external config
if (!defined('DB_HOST')) define('DB_HOST', $db_host);
if (!defined('DB_NAME')) define('DB_NAME', $db_name);
if (!defined('DB_USER')) define('DB_USER', $db_user);
if (!defined('DB_PASS')) define('DB_PASS', $db_pass);
}
// Create database connection
function getDBConnection() {
try {
$pdo = new PDO(
"mysql:host=" . DB_HOST . ";dbname=" . DB_NAME . ";charset=utf8mb4",
DB_USER,
DB_PASS,
[
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false
]
);
// Explicitly set charset to utf8mb4 to handle special characters like musical symbols
$pdo->exec("SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci");
$pdo->exec("SET CHARACTER SET utf8mb4");
return $pdo;
} catch (PDOException $e) {
error_log("Database connection failed: " . $e->getMessage());
return null;
}
}
// Initialize database tables
function initializeDatabase() {
$pdo = getDBConnection();
if (!$pdo) return false;
try {
// Users table
$pdo->exec("
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
password VARCHAR(255) NOT NULL,
credits INT DEFAULT 5,
plan ENUM('free', 'starter', 'pro') DEFAULT 'free',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
");
// Music tracks table
$pdo->exec("
CREATE TABLE IF NOT EXISTS music_tracks (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
task_id VARCHAR(255) UNIQUE NOT NULL,
title VARCHAR(255) NOT NULL,
prompt TEXT NOT NULL,
music_type ENUM('music', 'lyrics', 'wav', 'vocal-removal', 'music-video', 'extend', 'stem-separation') NOT NULL,
model_version VARCHAR(10) DEFAULT 'v3',
duration INT DEFAULT 30,
status ENUM('processing', 'complete', 'failed') DEFAULT 'processing',
audio_url TEXT,
video_url TEXT,
lyrics TEXT,
metadata JSON,
price DECIMAL(10,2) DEFAULT 0.00,
is_public BOOLEAN DEFAULT FALSE,
is_featured BOOLEAN DEFAULT FALSE,
is_vip_sample BOOLEAN DEFAULT FALSE,
playlist_order INT DEFAULT 0,
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
)
");
// Add missing metadata columns if they don't exist
// Note: MySQL doesn't support IF NOT EXISTS for ALTER TABLE, so we check first
$columns_to_add = [
'genre' => 'VARCHAR(100)',
'style' => 'VARCHAR(100)',
'bpm' => 'INT',
'key' => 'VARCHAR(50)',
'time_signature' => 'VARCHAR(20)',
'mood' => 'VARCHAR(100)',
'energy' => 'VARCHAR(50)',
'instruments' => 'TEXT',
'tags' => 'TEXT',
'audio_quality' => 'JSON',
'generation_parameters' => 'JSON',
'processing_info' => 'JSON',
'cost_info' => 'JSON',
'waveform_data' => 'JSON',
'spectrum_analysis' => 'JSON',
'audio_segments' => 'JSON',
'error_details' => 'JSON',
'audio_analysis' => 'JSON',
'system_info' => 'JSON',
'share_token' => 'VARCHAR(64)',
'share_token_expires' => 'INT'
];
// Get existing columns
$existing_columns = [];
$stmt = $pdo->query("SHOW COLUMNS FROM music_tracks");
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$existing_columns[] = $row['Field'];
}
// Add missing columns
foreach ($columns_to_add as $column => $definition) {
if (!in_array($column, $existing_columns)) {
try {
$pdo->exec("ALTER TABLE music_tracks ADD COLUMN `$column` $definition");
} catch (PDOException $e) {
// Column might already exist, ignore error
error_log("Could not add column $column: " . $e->getMessage());
}
}
}
// Update music_type ENUM to include 'stem-separation' if it doesn't exist
try {
$stmt = $pdo->query("SHOW COLUMNS FROM music_tracks WHERE Field = 'music_type'");
$columnInfo = $stmt->fetch(PDO::FETCH_ASSOC);
if ($columnInfo && isset($columnInfo['Type'])) {
$currentEnum = $columnInfo['Type'];
if (strpos($currentEnum, 'stem-separation') === false) {
// Add 'stem-separation' to the ENUM
$pdo->exec("ALTER TABLE music_tracks MODIFY COLUMN music_type ENUM('music', 'lyrics', 'wav', 'vocal-removal', 'music-video', 'extend', 'stem-separation') NOT NULL");
error_log("Added 'stem-separation' to music_type ENUM");
}
}
} catch (PDOException $e) {
// ENUM might already include it, or table might not exist yet
error_log("Could not update music_type ENUM: " . $e->getMessage());
}
// User sessions table
$pdo->exec("
CREATE TABLE IF NOT EXISTS user_sessions (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
session_token VARCHAR(255) UNIQUE NOT NULL,
expires_at TIMESTAMP NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
)
");
// Credits transactions table
$pdo->exec("
CREATE TABLE IF NOT EXISTS credit_transactions (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
amount INT NOT NULL,
type ENUM('purchase', 'usage', 'bonus', 'refund') NOT NULL,
description VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
)
");
// Email logs table
$pdo->exec("
CREATE TABLE IF NOT EXISTS email_logs (
id INT AUTO_INCREMENT PRIMARY KEY,
recipient_email VARCHAR(255) NOT NULL,
recipient_name VARCHAR(255),
subject VARCHAR(255) NOT NULL,
email_type VARCHAR(50) NOT NULL,
status ENUM('sent', 'failed', 'pending') DEFAULT 'pending',
error_message TEXT,
sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
user_id INT,
order_id VARCHAR(100),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
)
");
// Security events table
$pdo->exec("
CREATE TABLE IF NOT EXISTS security_events (
id INT AUTO_INCREMENT PRIMARY KEY,
event_type VARCHAR(100) NOT NULL,
details TEXT,
user_id INT,
admin_id INT,
ip_address VARCHAR(45),
user_agent TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
FOREIGN KEY (admin_id) REFERENCES users(id) ON DELETE SET NULL
)
");
// User login history table
$pdo->exec("
CREATE TABLE IF NOT EXISTS user_login_history (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
ip_address VARCHAR(45),
user_agent TEXT,
login_success BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
)
");
// User follows table
$pdo->exec("
CREATE TABLE IF NOT EXISTS user_follows (
id INT AUTO_INCREMENT PRIMARY KEY,
follower_id INT NOT NULL,
following_id INT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (follower_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (following_id) REFERENCES users(id) ON DELETE CASCADE,
UNIQUE KEY unique_follow (follower_id, following_id)
)
");
// Audio variations table
$pdo->exec("
CREATE TABLE IF NOT EXISTS audio_variations (
id INT AUTO_INCREMENT PRIMARY KEY,
track_id INT NOT NULL,
variation_index INT NOT NULL,
audio_url TEXT,
duration INT,
title VARCHAR(255),
tags TEXT,
image_url TEXT,
source_audio_url TEXT,
stream_audio_url TEXT,
metadata JSON,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (track_id) REFERENCES music_tracks(id) ON DELETE CASCADE
)
");
// Add missing metadata column if it doesn't exist
$pdo->exec("
ALTER TABLE audio_variations
ADD COLUMN IF NOT EXISTS metadata JSON
");
// User variation preferences table - tracks which variation is saved/favorited
$pdo->exec("
CREATE TABLE IF NOT EXISTS user_variation_preferences (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
track_id INT NOT NULL,
variation_id INT NOT NULL,
is_main_track 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,
FOREIGN KEY (track_id) REFERENCES music_tracks(id) ON DELETE CASCADE,
FOREIGN KEY (variation_id) REFERENCES audio_variations(id) ON DELETE CASCADE,
UNIQUE KEY unique_user_track_preference (user_id, track_id)
)
");
// Track likes table
$pdo->exec("
CREATE TABLE IF NOT EXISTS track_likes (
id INT AUTO_INCREMENT PRIMARY KEY,
track_id INT NOT NULL,
user_id INT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (track_id) REFERENCES music_tracks(id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
UNIQUE KEY unique_like (track_id, user_id)
)
");
// Track plays table
$pdo->exec("
CREATE TABLE IF NOT EXISTS track_plays (
id INT AUTO_INCREMENT PRIMARY KEY,
track_id INT NOT NULL,
user_id INT,
ip_address VARCHAR(45),
played_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (track_id) REFERENCES music_tracks(id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
)
");
// Create performance indexes
$indexes = [
// Music tracks indexes
"CREATE INDEX IF NOT EXISTS idx_music_tracks_user_status ON music_tracks(user_id, status)",
"CREATE INDEX IF NOT EXISTS idx_music_tracks_created_at ON music_tracks(created_at)",
"CREATE INDEX IF NOT EXISTS idx_music_tracks_status ON music_tracks(status)",
"CREATE INDEX IF NOT EXISTS idx_music_tracks_task_id ON music_tracks(task_id)",
// User follows indexes
"CREATE INDEX IF NOT EXISTS idx_user_follows_follower ON user_follows(follower_id)",
"CREATE INDEX IF NOT EXISTS idx_user_follows_following ON user_follows(following_id)",
// Audio variations indexes
"CREATE INDEX IF NOT EXISTS idx_audio_variations_track ON audio_variations(track_id)",
// Track likes indexes
"CREATE INDEX IF NOT EXISTS idx_track_likes_track ON track_likes(track_id)",
// Track plays indexes
"CREATE INDEX IF NOT EXISTS idx_track_plays_track ON track_plays(track_id)",
// Credit transactions indexes
"CREATE INDEX IF NOT EXISTS idx_credit_transactions_user ON credit_transactions(user_id, created_at)",
// Email logs indexes
"CREATE INDEX IF NOT EXISTS idx_email_logs_sent_at ON email_logs(sent_at)",
"CREATE INDEX IF NOT EXISTS idx_email_logs_user ON email_logs(user_id)",
// Security events indexes
"CREATE INDEX IF NOT EXISTS idx_security_events_type ON security_events(event_type, created_at)",
"CREATE INDEX IF NOT EXISTS idx_security_events_user ON security_events(user_id)",
// User login history indexes
"CREATE INDEX IF NOT EXISTS idx_user_login_history_user ON user_login_history(user_id, created_at)",
"CREATE INDEX IF NOT EXISTS idx_user_login_history_ip ON user_login_history(ip_address)"
];
foreach ($indexes as $index_sql) {
try {
$pdo->exec($index_sql);
} catch (Exception $e) {
// Index might already exist, continue
}
}
// Security flags table
$pdo->exec("
CREATE TABLE IF NOT EXISTS security_flags (
id INT AUTO_INCREMENT PRIMARY KEY,
flag_type VARCHAR(100) NOT NULL,
flag_value TEXT,
severity ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
resolved_at TIMESTAMP NULL
)
");
// IP blacklist table
$pdo->exec("
CREATE TABLE IF NOT EXISTS ip_blacklist (
id INT AUTO_INCREMENT PRIMARY KEY,
ip_address VARCHAR(45) UNIQUE NOT NULL,
reason TEXT,
blocked_by INT,
blocked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
expires_at TIMESTAMP NULL,
FOREIGN KEY (blocked_by) REFERENCES users(id) ON DELETE SET NULL
)
");
// User login history table
$pdo->exec("
CREATE TABLE IF NOT EXISTS user_login_history (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
ip_address VARCHAR(45),
user_agent TEXT,
login_success BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
)
");
// Add security-related columns to users table
$pdo->exec("
ALTER TABLE users
ADD COLUMN IF NOT EXISTS is_admin BOOLEAN DEFAULT FALSE,
ADD COLUMN IF NOT EXISTS is_blocked BOOLEAN DEFAULT FALSE,
ADD COLUMN IF NOT EXISTS block_reason TEXT,
ADD COLUMN IF NOT EXISTS blocked_at TIMESTAMP NULL,
ADD COLUMN IF NOT EXISTS last_login_ip VARCHAR(45),
ADD COLUMN IF NOT EXISTS last_login_at TIMESTAMP NULL,
ADD COLUMN IF NOT EXISTS failed_login_attempts INT DEFAULT 0,
ADD COLUMN IF NOT EXISTS last_failed_login TIMESTAMP NULL
");
// User follows table (for social features)
$pdo->exec("
CREATE TABLE IF NOT EXISTS user_follows (
id INT AUTO_INCREMENT PRIMARY KEY,
follower_id INT NOT NULL,
following_id INT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY unique_follow (follower_id, following_id),
FOREIGN KEY (follower_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (following_id) REFERENCES users(id) ON DELETE CASCADE
)
");
// Track likes table (for track likes)
$pdo->exec("
CREATE TABLE IF NOT EXISTS track_likes (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
track_id INT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY unique_like (user_id, track_id),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (track_id) REFERENCES music_tracks(id) ON DELETE CASCADE
)
");
// Track comments table (for track comments)
$pdo->exec("
CREATE TABLE IF NOT EXISTS track_comments (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
track_id INT NOT NULL,
comment TEXT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (track_id) REFERENCES music_tracks(id) ON DELETE CASCADE
)
");
// Track views table (for view tracking)
$pdo->exec("
CREATE TABLE IF NOT EXISTS track_views (
id INT AUTO_INCREMENT PRIMARY KEY,
track_id INT NOT NULL,
user_id INT,
ip_address VARCHAR(45),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (track_id) REFERENCES music_tracks(id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
)
");
// Track shares table (for share tracking)
$pdo->exec("
CREATE TABLE IF NOT EXISTS track_shares (
id INT AUTO_INCREMENT PRIMARY KEY,
track_id INT NOT NULL,
user_id INT,
share_type VARCHAR(50) DEFAULT 'social',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (track_id) REFERENCES music_tracks(id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
)
");
// Track votes table (for upvote/downvote functionality)
$pdo->exec("
CREATE TABLE IF NOT EXISTS track_votes (
id INT AUTO_INCREMENT PRIMARY KEY,
track_id INT NOT NULL,
user_id INT NOT NULL,
vote_type ENUM('up', 'down') NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY unique_vote (track_id, user_id),
FOREIGN KEY (track_id) REFERENCES music_tracks(id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
INDEX idx_track_votes_track (track_id),
INDEX idx_track_votes_user (user_id)
)
");
// Track purchases table (for user purchases)
$pdo->exec("
CREATE TABLE IF NOT EXISTS track_purchases (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
track_id INT NOT NULL,
price_paid DECIMAL(10,2) NOT NULL,
credits_used INT NOT NULL,
purchase_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
download_count INT DEFAULT 0,
last_downloaded TIMESTAMP NULL,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (track_id) REFERENCES music_tracks(id) ON DELETE CASCADE,
UNIQUE KEY unique_purchase (user_id, track_id)
)
");
// User profiles table (for additional profile info)
$pdo->exec("
CREATE TABLE IF NOT EXISTS user_profiles (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL UNIQUE,
bio TEXT,
location VARCHAR(255),
website VARCHAR(255),
social_links JSON,
profile_image VARCHAR(255),
cover_image VARCHAR(255),
cover_position VARCHAR(50) DEFAULT 'center center',
profile_position VARCHAR(50) DEFAULT 'center center',
custom_url VARCHAR(255),
genres JSON,
music_style TEXT,
artist_highlights JSON,
influences TEXT,
equipment TEXT,
achievements JSON,
featured_tracks JSON,
artist_statement TEXT,
followers_count INT DEFAULT 0,
following_count INT DEFAULT 0,
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
)
");
// Add missing columns to user_profiles if they don't exist (for existing installations)
$pdo->exec("
ALTER TABLE user_profiles
ADD COLUMN IF NOT EXISTS cover_image VARCHAR(255),
ADD COLUMN IF NOT EXISTS cover_position VARCHAR(50) DEFAULT 'center center',
ADD COLUMN IF NOT EXISTS profile_position VARCHAR(50) DEFAULT 'center center',
ADD COLUMN IF NOT EXISTS custom_url VARCHAR(255),
ADD COLUMN IF NOT EXISTS followers_count INT DEFAULT 0,
ADD COLUMN IF NOT EXISTS following_count INT DEFAULT 0,
ADD COLUMN IF NOT EXISTS paypal_me_username VARCHAR(100)
");
// Track plays table (for play count tracking)
$pdo->exec("
CREATE TABLE IF NOT EXISTS track_plays (
id INT AUTO_INCREMENT PRIMARY KEY,
track_id INT NOT NULL,
user_id INT,
ip_address VARCHAR(45),
user_agent TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (track_id) REFERENCES music_tracks(id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
)
");
// Admin logs table (for tracking admin actions)
$pdo->exec("
CREATE TABLE IF NOT EXISTS admin_logs (
id INT AUTO_INCREMENT PRIMARY KEY,
admin_id INT NOT NULL,
action VARCHAR(100) NOT NULL,
target_user_id INT,
details TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (admin_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (target_user_id) REFERENCES users(id) ON DELETE SET NULL
)
");
// Site settings table (for global site configuration)
$pdo->exec("
CREATE TABLE IF NOT EXISTS site_settings (
id INT AUTO_INCREMENT PRIMARY KEY,
setting_key VARCHAR(100) UNIQUE NOT NULL,
setting_value TEXT,
setting_description TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
");
// Email logs table for tracking email activity
$pdo->exec("
CREATE TABLE IF NOT EXISTS email_logs (
id INT AUTO_INCREMENT PRIMARY KEY,
recipient_email VARCHAR(255) NOT NULL,
recipient_name VARCHAR(255),
subject VARCHAR(255) NOT NULL,
email_type VARCHAR(50) NOT NULL,
status ENUM('sent', 'failed', 'pending') DEFAULT 'pending',
error_message TEXT,
sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
user_id INT,
order_id VARCHAR(100),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
)
");
// User messages table (for direct messaging between users)
$pdo->exec("
CREATE TABLE IF NOT EXISTS user_messages (
id INT AUTO_INCREMENT PRIMARY KEY,
sender_id INT NOT NULL,
receiver_id INT NOT NULL,
message TEXT NOT NULL,
is_read BOOLEAN DEFAULT FALSE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (receiver_id) REFERENCES users(id) ON DELETE CASCADE,
INDEX idx_sender (sender_id),
INDEX idx_receiver (receiver_id),
INDEX idx_created (created_at),
INDEX idx_read (is_read),
INDEX idx_conversation (sender_id, receiver_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
");
// Insert default site settings if they don't exist
$defaultSettings = [
['global_player_enabled', '1', 'Enable global music player'],
['auto_play_enabled', '0', 'Enable auto-play for music player'],
['shuffle_enabled', '0', 'Enable shuffle mode for playlists'],
['ajax_enabled', '1', 'Enable AJAX navigation'],
['debug_mode', '0', 'Enable debug mode'],
['stripe_live_mode', '1', 'Use Stripe live mode'],
['auto_refresh_payment_methods', '0', 'Auto-refresh payment methods']
];
foreach ($defaultSettings as $setting) {
try {
$stmt = $pdo->prepare("
INSERT IGNORE INTO site_settings (setting_key, setting_value, setting_description)
VALUES (?, ?, ?)
");
$stmt->execute($setting);
} catch (Exception $e) {
// If setting_description column doesn't exist, try without it
try {
$stmt = $pdo->prepare("
INSERT IGNORE INTO site_settings (setting_key, setting_value)
VALUES (?, ?)
");
$stmt->execute([$setting[0], $setting[1]]);
} catch (Exception $e2) {
error_log("Failed to insert site setting: " . $e2->getMessage());
}
}
}
return true;
} catch (PDOException $e) {
error_log("Database initialization failed: " . $e->getMessage());
return false;
}
}
// User management functions
function createUser($name, $email, $password) {
$pdo = getDBConnection();
if (!$pdo) return false;
try {
$hashedPassword = password_hash($password, PASSWORD_DEFAULT);
$stmt = $pdo->prepare("
INSERT INTO users (name, email, password, credits, plan)
VALUES (?, ?, ?, 5, 'free')
");
return $stmt->execute([$name, $email, $hashedPassword]);
} catch (PDOException $e) {
error_log("User creation failed: " . $e->getMessage());
return false;
}
}
function authenticateUser($email, $password) {
$pdo = getDBConnection();
if (!$pdo) return false;
try {
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = ?");
$stmt->execute([$email]);
$user = $stmt->fetch();
if ($user && password_verify($password, $user['password'])) {
return $user;
}
return false;
} catch (PDOException $e) {
error_log("User authentication failed: " . $e->getMessage());
return false;
}
}
function getUserById($id) {
$pdo = getDBConnection();
if (!$pdo) return false;
try {
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([$id]);
return $stmt->fetch();
} catch (PDOException $e) {
error_log("Get user failed: " . $e->getMessage());
return false;
}
}
function updateUserCredits($userId, $credits) {
$pdo = getDBConnection();
if (!$pdo) return false;
try {
$stmt = $pdo->prepare("UPDATE users SET credits = ? WHERE id = ?");
return $stmt->execute([$credits, $userId]);
} catch (PDOException $e) {
error_log("Update credits failed: " . $e->getMessage());
return false;
}
}
// Music track management functions
function createMusicTrack($userId, $taskId, $title, $prompt, $musicType, $modelVersion = 'v5', $duration = 30) {
$pdo = getDBConnection();
if (!$pdo) return false;
try {
// Check user credits and plan
$stmt = $pdo->prepare("SELECT credits, plan, created_at FROM users WHERE id = ?");
$stmt->execute([$userId]);
$user = $stmt->fetch();
if (!$user || $user['credits'] < 1) {
error_log("Insufficient credits for user $userId to create track");
return false;
}
// Determine commercial rights and grace period eligibility
$user_plan = $user['plan'] ?? 'free';
$commercial_rights = ($user_plan !== 'free') ? 'full' : 'none';
// Check if user is within 30-day grace period (only for free users)
$grace_period_eligible = false;
if ($user_plan === 'free' && !empty($user['created_at'])) {
$account_created = new DateTime($user['created_at']);
$grace_expires = clone $account_created;
$grace_expires->modify('+30 days');
$now = new DateTime();
$grace_period_eligible = ($now <= $grace_expires);
}
// Deduct credit
$newCredits = $user['credits'] - 1;
$stmt = $pdo->prepare("UPDATE users SET credits = ? WHERE id = ?");
$stmt->execute([$newCredits, $userId]);
// Record credit transaction
$stmt = $pdo->prepare("
INSERT INTO credit_transactions (user_id, amount, type, description, created_at)
VALUES (?, -1, 'usage', 'Music track creation via createMusicTrack: $title', NOW())
");
$stmt->execute([$userId]);
// Create the track with commercial rights and grace period info
$stmt = $pdo->prepare("
INSERT INTO music_tracks (user_id, task_id, title, prompt, music_type, model_version, duration, commercial_rights, grace_period_eligible, user_plan_at_creation)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
");
$result = $stmt->execute([$userId, $taskId, $title, $prompt, $musicType, $modelVersion, $duration, $commercial_rights, $grace_period_eligible, $user_plan]);
if ($result) {
$track_id = $pdo->lastInsertId();
error_log("Credit deducted for user $userId: 1 credit, new balance: $newCredits. Track $track_id created with commercial_rights=$commercial_rights, grace_eligible=" . ($grace_period_eligible ? 'true' : 'false'));
}
return $result;
} catch (PDOException $e) {
error_log("Create music track failed: " . $e->getMessage());
return false;
}
}
function updateMusicTrack($taskId, $status, $audioUrl = null, $videoUrl = null, $lyrics = null, $metadata = null, $duration = null, $title = null, $tags = null, $modelName = null, $imageUrl = null) {
$pdo = getDBConnection();
if (!$pdo) return false;
try {
// Parse metadata to extract individual fields
$metadataArray = $metadata ? json_decode($metadata, true) : [];
// Build the update query dynamically based on what's provided
$updates = ['status = ?'];
$params = [$status];
// Only update title if provided AND track doesn't already have a user-provided title
if ($title !== null && $title !== '') {
// Check if track already has a title and if user has modified it
$checkStmt = $pdo->prepare("SELECT title, title_user_modified FROM music_tracks WHERE task_id = ?");
$checkStmt->execute([$taskId]);
$existingTrack = $checkStmt->fetch(PDO::FETCH_ASSOC);
$existingTitle = $existingTrack['title'] ?? null;
$userModified = $existingTrack['title_user_modified'] ?? 0;
// Only update if:
// 1. User hasn't explicitly modified the title AND
// 2. Title is empty or is a default value
if (!$userModified && (
!$existingTitle ||
$existingTitle === '' ||
$existingTitle === 'Untitled Track' ||
$existingTitle === 'Generated Track')) {
$updates[] = 'title = ?';
$params[] = $title;
}
// Otherwise preserve the user's title
}
// Only update audio_url if it's not null AND not empty
if ($audioUrl !== null && $audioUrl !== '') {
$updates[] = 'audio_url = ?';
$params[] = $audioUrl;
}
if ($videoUrl !== null) {
$updates[] = 'video_url = ?';
$params[] = $videoUrl;
}
if ($imageUrl !== null && $imageUrl !== '') {
$updates[] = 'image_url = ?';
$params[] = $imageUrl;
}
if ($lyrics !== null) {
$updates[] = 'lyrics = ?';
$params[] = $lyrics;
}
if ($metadata !== null) {
$updates[] = 'metadata = ?';
$params[] = $metadata;
}
if ($duration !== null) {
$updates[] = 'duration = ?';
$params[] = $duration;
}
if ($tags !== null) {
// Convert tags array to string if needed
$tagsValue = is_array($tags) ? implode(', ', $tags) : $tags;
if (!empty($tagsValue)) {
$updates[] = 'tags = ?';
$params[] = $tagsValue;
}
}
if ($modelName !== null && $modelName !== '') {
// Update model_version field with model_name
$updates[] = 'model_version = ?';
$params[] = $modelName;
}
// Update individual metadata fields to their own columns
if (isset($metadataArray['genre'])) {
$updates[] = 'genre = ?';
$params[] = $metadataArray['genre'];
}
if (isset($metadataArray['style'])) {
$updates[] = 'style = ?';
$params[] = $metadataArray['style'];
}
if (isset($metadataArray['bpm'])) {
$updates[] = 'bpm = ?';
$params[] = $metadataArray['bpm'];
}
if (isset($metadataArray['key'])) {
$updates[] = 'key = ?';
$params[] = $metadataArray['key'];
}
if (isset($metadataArray['time_signature'])) {
$updates[] = 'time_signature = ?';
$params[] = $metadataArray['time_signature'];
}
if (isset($metadataArray['mood'])) {
$updates[] = 'mood = ?';
$params[] = $metadataArray['mood'];
}
if (isset($metadataArray['energy'])) {
$updates[] = 'energy = ?';
$params[] = $metadataArray['energy'];
}
if (isset($metadataArray['instruments'])) {
$updates[] = 'instruments = ?';
$params[] = is_array($metadataArray['instruments']) ? implode(', ', $metadataArray['instruments']) : $metadataArray['instruments'];
}
if (isset($metadataArray['tags'])) {
$updates[] = 'tags = ?';
$params[] = is_array($metadataArray['tags']) ? implode(', ', $metadataArray['tags']) : $metadataArray['tags'];
}
// Also store the detailed metadata in JSON columns for advanced features
if (isset($metadataArray['audio_quality'])) {
$updates[] = 'audio_quality = ?';
$params[] = json_encode($metadataArray['audio_quality']);
}
if (isset($metadataArray['generation_parameters'])) {
$updates[] = 'generation_parameters = ?';
$params[] = json_encode($metadataArray['generation_parameters']);
}
if (isset($metadataArray['processing_info'])) {
$updates[] = 'processing_info = ?';
$params[] = json_encode($metadataArray['processing_info']);
}
if (isset($metadataArray['cost_info'])) {
$updates[] = 'cost_info = ?';
$params[] = json_encode($metadataArray['cost_info']);
}
if (isset($metadataArray['waveform_data'])) {
$updates[] = 'waveform_data = ?';
$params[] = json_encode($metadataArray['waveform_data']);
}
if (isset($metadataArray['spectrum_analysis'])) {
$updates[] = 'spectrum_analysis = ?';
$params[] = json_encode($metadataArray['spectrum_analysis']);
}
if (isset($metadataArray['audio_segments'])) {
$updates[] = 'audio_segments = ?';
$params[] = json_encode($metadataArray['audio_segments']);
}
if (isset($metadataArray['error_details'])) {
$updates[] = 'error_details = ?';
$params[] = json_encode($metadataArray['error_details']);
}
if (isset($metadataArray['audio_analysis'])) {
$updates[] = 'audio_analysis = ?';
$params[] = json_encode($metadataArray['audio_analysis']);
}
if (isset($metadataArray['system_info'])) {
$updates[] = 'system_info = ?';
$params[] = json_encode($metadataArray['system_info']);
}
$params[] = $taskId;
$sql = "UPDATE music_tracks SET " . implode(', ', $updates) . " WHERE task_id = ?";
$stmt = $pdo->prepare($sql);
$result = $stmt->execute($params);
if ($result) {
// Log successful updates for debugging
error_log("✅ Successfully updated music track: task_id=$taskId, status=$status, rows_affected=" . $stmt->rowCount());
} else {
// Log detailed error information
$errorInfo = $stmt->errorInfo();
error_log("❌ Failed to update music track: task_id=$taskId, status=$status, SQL=$sql, Error: " . json_encode($errorInfo));
}
return $result;
} catch (PDOException $e) {
error_log("❌ PDO Exception in updateMusicTrack: task_id=$taskId, status=$status, Error: " . $e->getMessage());
return false;
}
}
// Function to extract lyrics from saved task_results JSON file as fallback
// This is used when lyrics weren't saved during callback processing
function extractLyricsFromTaskResults($taskId) {
if (empty($taskId)) {
error_log("extractLyricsFromTaskResults: taskId is empty");
return null;
}
$resultFile = "task_results/{$taskId}.json";
if (!file_exists($resultFile)) {
error_log("extractLyricsFromTaskResults: File not found: $resultFile");
return null;
}
$jsonContent = file_get_contents($resultFile);
if (!$jsonContent) {
error_log("extractLyricsFromTaskResults: Could not read file: $resultFile");
return null;
}
$data = json_decode($jsonContent, true);
if (!$data || !is_array($data)) {
error_log("extractLyricsFromTaskResults: Invalid JSON in file: $resultFile");
return null;
}
$lyrics = '';
// COMPREHENSIVE EXTRACTION - Check ALL possible locations
// Priority 1: Check data['data']['data'] array items for 'prompt' field (MOST COMMON)
if (isset($data['data']['data']) && is_array($data['data']['data'])) {
foreach ($data['data']['data'] as $index => $item) {
if (is_array($item)) {
// Check prompt field (where API.Box sends lyrics)
if (isset($item['prompt']) && !empty($item['prompt']) && trim($item['prompt']) !== '') {
$lyrics = $item['prompt'];
error_log("extractLyricsFromTaskResults: Found lyrics in data.data.data[$index].prompt for task $taskId");
break;
}
// Check lyrics field
if (!$lyrics && isset($item['lyrics']) && !empty($item['lyrics']) && trim($item['lyrics']) !== '') {
$lyrics = $item['lyrics'];
error_log("extractLyricsFromTaskResults: Found lyrics in data.data.data[$index].lyrics for task $taskId");
break;
}
// Check text field
if (!$lyrics && isset($item['text']) && !empty($item['text']) && trim($item['text']) !== '') {
$lyrics = $item['text'];
error_log("extractLyricsFromTaskResults: Found lyrics in data.data.data[$index].text for task $taskId");
break;
}
}
}
}
// Priority 2: Check data['data'] directly (for some callback formats)
if (!$lyrics && isset($data['data']) && is_array($data['data'])) {
if (isset($data['data']['prompt']) && !empty($data['data']['prompt']) && trim($data['data']['prompt']) !== '') {
$lyrics = $data['data']['prompt'];
error_log("extractLyricsFromTaskResults: Found lyrics in data.data.prompt for task $taskId");
} elseif (isset($data['data']['lyrics']) && !empty($data['data']['lyrics']) && trim($data['data']['lyrics']) !== '') {
$lyrics = $data['data']['lyrics'];
error_log("extractLyricsFromTaskResults: Found lyrics in data.data.lyrics for task $taskId");
} elseif (isset($data['data']['text']) && !empty($data['data']['text']) && trim($data['data']['text']) !== '') {
$lyrics = $data['data']['text'];
error_log("extractLyricsFromTaskResults: Found lyrics in data.data.text for task $taskId");
}
}
// Priority 3: Check top-level fields
if (!$lyrics) {
if (isset($data['prompt']) && !empty($data['prompt']) && trim($data['prompt']) !== '') {
$lyrics = $data['prompt'];
error_log("extractLyricsFromTaskResults: Found lyrics in data.prompt for task $taskId");
} elseif (isset($data['lyrics']) && !empty($data['lyrics']) && trim($data['lyrics']) !== '') {
$lyrics = $data['lyrics'];
error_log("extractLyricsFromTaskResults: Found lyrics in data.lyrics for task $taskId");
} elseif (isset($data['text']) && !empty($data['text']) && trim($data['text']) !== '') {
$lyrics = $data['text'];
error_log("extractLyricsFromTaskResults: Found lyrics in data.text for task $taskId");
}
}
// Priority 4: Deep search - recursively search all arrays for prompt/lyrics/text fields
if (!$lyrics) {
$recursiveSearch = function($arr, $depth = 0) use (&$recursiveSearch, &$lyrics) {
if ($depth > 5) return; // Prevent infinite recursion
if (!is_array($arr)) return;
foreach ($arr as $key => $value) {
if (is_array($value)) {
$recursiveSearch($value, $depth + 1);
} elseif (in_array(strtolower($key), ['prompt', 'lyrics', 'lyric', 'text']) &&
is_string($value) && !empty(trim($value))) {
$lyrics = $value;
error_log("extractLyricsFromTaskResults: Found lyrics in recursive search at key '$key'");
return;
}
if ($lyrics) break;
}
};
$recursiveSearch($data);
}
// Clean up lyrics if found - PRESERVE STRUCTURE AND NEWLINES
if ($lyrics) {
// Normalize line breaks (convert \r\n and \r to \n)
$lyrics = str_replace(["\r\n", "\r"], "\n", $lyrics);
// Remove excessive blank lines (more than 2 consecutive newlines become 2)
$lyrics = preg_replace('/\n{3,}/', "\n\n", $lyrics);
// Clean up trailing whitespace on each line (but preserve the line structure)
$lines = explode("\n", $lyrics);
$lines = array_map('trim', $lines);
$lyrics = implode("\n", $lines);
// Remove excessive blank lines again after trimming
$lyrics = preg_replace('/\n{3,}/', "\n\n", $lyrics);
// Trim whitespace from start and end only
$lyrics = trim($lyrics);
if (!empty($lyrics)) {
error_log("extractLyricsFromTaskResults: Successfully extracted lyrics for task $taskId (length: " . strlen($lyrics) . " chars)");
return $lyrics;
}
}
error_log("extractLyricsFromTaskResults: No lyrics found in any location for task $taskId");
return null;
}
function getUserMusicTracks($userId, $limit = 50) {
$pdo = getDBConnection();
if (!$pdo) return [];
try {
$stmt = $pdo->prepare("
SELECT * FROM music_tracks
WHERE user_id = ?
ORDER BY created_at DESC
LIMIT ?
");
$stmt->execute([$userId, $limit]);
return $stmt->fetchAll();
} catch (PDOException $e) {
error_log("Get user music tracks failed: " . $e->getMessage());
return [];
}
}
function getMusicTrackByTaskId($taskId) {
$pdo = getDBConnection();
if (!$pdo) return false;
try {
$stmt = $pdo->prepare("SELECT * FROM music_tracks WHERE task_id = ?");
$stmt->execute([$taskId]);
return $stmt->fetch();
} catch (PDOException $e) {
error_log("Get music track failed: " . $e->getMessage());
return false;
}
}
// Database initialization is handled separately to avoid output issues
// Initialize database tables if not already done
if (!function_exists('databaseInitialized')) {
function databaseInitialized() {
return true;
}
initializeDatabase();
}
// Function to optimize database performance (can be called from any page)
function optimizeDatabasePerformance() {
$pdo = getDBConnection();
if (!$pdo) return false;
try {
// Create performance indexes if they don't exist
$indexes = [
"CREATE INDEX IF NOT EXISTS idx_music_tracks_user_status ON music_tracks(user_id, status)",
"CREATE INDEX IF NOT EXISTS idx_music_tracks_created_at ON music_tracks(created_at)",
"CREATE INDEX IF NOT EXISTS idx_music_tracks_status ON music_tracks(status)",
"CREATE INDEX IF NOT EXISTS idx_music_tracks_task_id ON music_tracks(task_id)",
"CREATE INDEX IF NOT EXISTS idx_user_follows_follower ON user_follows(follower_id)",
"CREATE INDEX IF NOT EXISTS idx_user_follows_following ON user_follows(following_id)",
"CREATE INDEX IF NOT EXISTS idx_audio_variations_track ON audio_variations(track_id)",
"CREATE INDEX IF NOT EXISTS idx_track_likes_track ON track_likes(track_id)",
"CREATE INDEX IF NOT EXISTS idx_track_plays_track ON track_plays(track_id)",
"CREATE INDEX IF NOT EXISTS idx_credit_transactions_user ON credit_transactions(user_id, created_at)",
"CREATE INDEX IF NOT EXISTS idx_email_logs_sent_at ON email_logs(sent_at)",
"CREATE INDEX IF NOT EXISTS idx_email_logs_user ON email_logs(user_id)",
"CREATE INDEX IF NOT EXISTS idx_security_events_type ON security_events(event_type, created_at)",
"CREATE INDEX IF NOT EXISTS idx_security_events_user ON security_events(user_id)",
"CREATE INDEX IF NOT EXISTS idx_user_login_history_user ON user_login_history(user_id, created_at)",
"CREATE INDEX IF NOT EXISTS idx_user_login_history_ip ON user_login_history(ip_address)"
];
foreach ($indexes as $index_sql) {
try {
$pdo->exec($index_sql);
} catch (Exception $e) {
// Index might already exist, continue
}
}
return true;
} catch (Exception $e) {
error_log("Database optimization failed: " . $e->getMessage());
return false;
}
}
// Cache cleanup disabled during development
function cleanupCache() {
// Cache cleanup disabled to prevent issues during development
return 0;
}