 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/ |
-- Database Updates for Credit System and Automatic Expiration
-- Run this script to update your database schema
-- 1. Update Users Table
ALTER TABLE users ADD COLUMN credits INT DEFAULT 0;
ALTER TABLE users ADD COLUMN subscription_expires DATETIME NULL;
ALTER TABLE users ADD COLUMN commercial_rights_expires DATETIME NULL;
ALTER TABLE users ADD COLUMN expiration_warning_sent TINYINT(1) DEFAULT 0;
ALTER TABLE users ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
-- 2. Create Credit Purchases Table
CREATE TABLE IF NOT EXISTS credit_purchases (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
package VARCHAR(50) NOT NULL,
credits INT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
payment_intent_id VARCHAR(255) NOT NULL,
expires_at DATETIME NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
INDEX idx_user_expires (user_id, expires_at),
INDEX idx_expires (expires_at)
);
-- 3. Create Credit Usage Table
CREATE TABLE IF NOT EXISTS credit_usage (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
track_id VARCHAR(255) NOT NULL,
credits_used INT DEFAULT 1,
usage_type ENUM('download', 'preview', 'commercial') DEFAULT 'download',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
INDEX idx_user_usage (user_id, created_at)
);
-- 4. Create Credit Transactions Table (if not exists)
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', 'expiration', 'refund') NOT NULL,
description TEXT,
stripe_payment_intent_id VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
INDEX idx_user_type (user_id, type),
INDEX idx_created_at (created_at)
);
-- 5. Update Track Purchases Table
ALTER TABLE track_purchases ADD COLUMN payment_method VARCHAR(20) DEFAULT 'credits';
ALTER TABLE track_purchases ADD COLUMN stripe_payment_intent_id VARCHAR(255);
-- 6. Create User Follows Table (if not exists)
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),
INDEX idx_follower (follower_id),
INDEX idx_following (following_id)
);
-- 7. Create Music Tracks Table (if not exists)
CREATE TABLE IF NOT EXISTS music_tracks (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
title VARCHAR(255) NOT NULL,
audio_url VARCHAR(500),
price DECIMAL(10,2) DEFAULT 1.00,
status ENUM('processing', 'complete', 'failed') DEFAULT 'processing',
duration INT DEFAULT 0,
genre VARCHAR(100),
music_type VARCHAR(100),
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_status (user_id, status),
INDEX idx_status (status)
);
-- 8. Create Track Purchases Table (if not exists)
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,
payment_method VARCHAR(20) DEFAULT 'credits',
stripe_payment_intent_id VARCHAR(255),
purchased_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,
UNIQUE KEY unique_purchase (user_id, track_id),
INDEX idx_user_purchases (user_id),
INDEX idx_track_purchases (track_id)
);
-- 9. Add indexes for better performance
CREATE INDEX idx_users_credits ON users(credits);
CREATE INDEX idx_users_subscription_expires ON users(subscription_expires);
CREATE INDEX idx_users_commercial_rights_expires ON users(commercial_rights_expires);
-- 10. Insert sample data for testing (optional)
-- Uncomment the lines below if you want to add test data
/*
-- Add test credits to existing users
UPDATE users SET credits = 10 WHERE credits = 0 LIMIT 5;
-- Set some test expiration dates
UPDATE users
SET subscription_expires = DATE_ADD(NOW(), INTERVAL 30 DAY),
commercial_rights_expires = DATE_ADD(NOW(), INTERVAL 30 DAY)
WHERE id IN (1, 2, 3);
*/
-- 11. Create logs directory if it doesn't exist
-- Note: This is a reminder to create the logs directory manually
-- mkdir -p /home/gositeme/domains/soundstudiopro.com/public_html/logs
-- 12. Set proper permissions for log files
-- Note: Run these commands after creating the logs directory
-- chmod 755 /home/gositeme/domains/soundstudiopro.com/public_html/logs
-- touch /home/gositeme/domains/soundstudiopro.com/public_html/logs/credit_expirations.log
-- touch /home/gositeme/domains/soundstudiopro.com/public_html/logs/user_credits.log
-- touch /home/gositeme/domains/soundstudiopro.com/public_html/logs/stripe_actions.log
-- touch /home/gositeme/domains/soundstudiopro.com/public_html/logs/track_purchases.log
-- touch /home/gositeme/domains/soundstudiopro.com/public_html/logs/track_purchase_errors.log
-- chmod 666 /home/gositeme/domains/soundstudiopro.com/public_html/logs/*.log
-- Database update complete!
-- Run this script in your MySQL database to update the schema