 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/public_html/migrations/ |
<?php
/**
* Radio Station Licensing System - Database Migration
*
* This script creates all necessary database tables for the radio station
* licensing platform. Run this once to set up the complete system.
*
* Usage: php migrations/add_radio_station_system.php
* Or access via browser: /migrations/add_radio_station_system.php
*/
require_once __DIR__ . '/../config/database.php';
$pdo = getDBConnection();
// Enable error reporting for migration
error_reporting(E_ALL);
ini_set('display_errors', 1);
echo "<h1>Radio Station Licensing System - Database Migration</h1>\n";
echo "<pre>\n";
$errors = [];
$success = [];
try {
// Note: DDL statements (CREATE TABLE) can't be rolled back in MySQL
// So we don't use transactions for migrations
// $pdo->beginTransaction();
// ============================================
// 1. RADIO STATIONS TABLE
// ============================================
echo "Creating radio_stations table...\n";
$pdo->exec("
CREATE TABLE IF NOT EXISTS radio_stations (
id INT AUTO_INCREMENT PRIMARY KEY,
station_name VARCHAR(255) NOT NULL,
call_sign VARCHAR(50) UNIQUE,
station_type ENUM('local', 'regional', 'national', 'internet', 'podcast') NOT NULL DEFAULT 'local',
license_tier ENUM('local', 'regional', 'national', 'enterprise') NOT NULL DEFAULT 'local',
contact_name VARCHAR(255) NOT NULL,
contact_email VARCHAR(255) NOT NULL UNIQUE,
contact_phone VARCHAR(50),
website_url VARCHAR(500),
city VARCHAR(100),
state VARCHAR(100),
country VARCHAR(100) DEFAULT 'US',
timezone VARCHAR(50) DEFAULT 'America/New_York',
subscription_status ENUM('active', 'suspended', 'cancelled', 'trial') DEFAULT 'trial',
subscription_start_date DATE,
subscription_end_date DATE,
monthly_play_limit INT DEFAULT 500,
current_month_plays INT DEFAULT 0,
stripe_customer_id VARCHAR(255),
stripe_subscription_id VARCHAR(255),
billing_cycle_start DATE,
billing_cycle_end DATE,
format VARCHAR(100),
target_demographic VARCHAR(100),
broadcast_hours JSON,
logo_url VARCHAR(500),
description TEXT,
api_key VARCHAR(255) UNIQUE,
api_secret VARCHAR(255),
api_enabled BOOLEAN DEFAULT FALSE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
last_login TIMESTAMP NULL,
is_active BOOLEAN DEFAULT TRUE,
INDEX idx_subscription_status (subscription_status),
INDEX idx_license_tier (license_tier),
INDEX idx_api_key (api_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
");
$success[] = "radio_stations table created";
// ============================================
// 2. RADIO STATION USERS TABLE
// ============================================
echo "Creating radio_station_users table...\n";
$pdo->exec("
CREATE TABLE IF NOT EXISTS radio_station_users (
id INT AUTO_INCREMENT PRIMARY KEY,
station_id INT NOT NULL,
user_id INT,
email VARCHAR(255) NOT NULL,
password_hash VARCHAR(255) NOT NULL,
name VARCHAR(255) NOT NULL,
role ENUM('admin', 'manager', 'dj', 'viewer') DEFAULT 'viewer',
permissions JSON,
last_login TIMESTAMP NULL,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (station_id) REFERENCES radio_stations(id) ON DELETE CASCADE,
UNIQUE KEY unique_station_email (station_id, email),
INDEX idx_station_id (station_id),
INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
");
$success[] = "radio_station_users table created";
// ============================================
// 3. RADIO PLAYLISTS TABLE
// ============================================
echo "Creating radio_playlists table...\n";
$pdo->exec("
CREATE TABLE IF NOT EXISTS radio_playlists (
id INT AUTO_INCREMENT PRIMARY KEY,
station_id INT NOT NULL,
name VARCHAR(255) NOT NULL,
description TEXT,
is_active BOOLEAN DEFAULT TRUE,
is_default BOOLEAN DEFAULT FALSE,
start_date DATE,
end_date DATE,
rotation_type ENUM('sequential', 'random', 'weighted', 'time_based') DEFAULT 'random',
priority INT DEFAULT 0,
created_by INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (station_id) REFERENCES radio_stations(id) ON DELETE CASCADE,
INDEX idx_station_id (station_id),
INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
");
$success[] = "radio_playlists table created";
// ============================================
// 4. RADIO PLAYLIST TRACKS TABLE
// ============================================
echo "Creating radio_playlist_tracks table...\n";
$pdo->exec("
CREATE TABLE IF NOT EXISTS radio_playlist_tracks (
id INT AUTO_INCREMENT PRIMARY KEY,
playlist_id INT NOT NULL,
track_id INT NOT NULL,
position INT,
play_count INT DEFAULT 0,
last_played TIMESTAMP NULL,
weight DECIMAL(5,2) DEFAULT 1.0,
added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
added_by INT,
FOREIGN KEY (playlist_id) REFERENCES radio_playlists(id) ON DELETE CASCADE,
FOREIGN KEY (track_id) REFERENCES music_tracks(id) ON DELETE CASCADE,
UNIQUE KEY unique_playlist_track (playlist_id, track_id),
INDEX idx_playlist_id (playlist_id),
INDEX idx_track_id (track_id),
INDEX idx_last_played (last_played)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
");
$success[] = "radio_playlist_tracks table created";
// ============================================
// 5. RADIO PLAY LOGS TABLE
// ============================================
echo "Creating radio_play_logs table...\n";
$pdo->exec("
CREATE TABLE IF NOT EXISTS radio_play_logs (
id INT AUTO_INCREMENT PRIMARY KEY,
station_id INT NOT NULL,
track_id INT NOT NULL,
playlist_id INT,
played_at TIMESTAMP NOT NULL,
duration_played INT,
play_type ENUM('full', 'partial', 'intro', 'outro') DEFAULT 'full',
time_of_day TIME,
day_of_week INT,
listener_count INT,
market_share DECIMAL(5,2),
played_by INT,
source ENUM('manual', 'scheduled', 'api', 'auto') DEFAULT 'auto',
ip_address VARCHAR(45),
user_agent TEXT,
pro_reported BOOLEAN DEFAULT FALSE,
pro_report_date DATE NULL,
royalty_calculated BOOLEAN DEFAULT FALSE,
royalty_amount DECIMAL(10,2) DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (station_id) REFERENCES radio_stations(id) ON DELETE CASCADE,
FOREIGN KEY (track_id) REFERENCES music_tracks(id) ON DELETE CASCADE,
FOREIGN KEY (playlist_id) REFERENCES radio_playlists(id) ON DELETE SET NULL,
INDEX idx_station_played_at (station_id, played_at),
INDEX idx_track_played_at (track_id, played_at),
INDEX idx_pro_reported (pro_reported),
INDEX idx_royalty_calculated (royalty_calculated),
INDEX idx_played_at (played_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
");
$success[] = "radio_play_logs table created";
// ============================================
// 6. RADIO LICENSES TABLE
// ============================================
echo "Creating radio_licenses table...\n";
$pdo->exec("
CREATE TABLE IF NOT EXISTS radio_licenses (
id INT AUTO_INCREMENT PRIMARY KEY,
station_id INT NOT NULL,
track_id INT NOT NULL,
license_type ENUM('subscription', 'one_time', 'exclusive', 'non_exclusive') DEFAULT 'subscription',
start_date DATE NOT NULL,
end_date DATE NULL,
play_limit INT NULL,
status ENUM('active', 'expired', 'revoked', 'pending') DEFAULT 'active',
is_exclusive BOOLEAN DEFAULT FALSE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (station_id) REFERENCES radio_stations(id) ON DELETE CASCADE,
FOREIGN KEY (track_id) REFERENCES music_tracks(id) ON DELETE CASCADE,
UNIQUE KEY unique_station_track (station_id, track_id),
INDEX idx_station_status (station_id, status),
INDEX idx_track_status (track_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
");
$success[] = "radio_licenses table created";
// ============================================
// 7. RADIO ROYALTIES TABLE
// ============================================
echo "Creating radio_royalties table...\n";
$pdo->exec("
CREATE TABLE IF NOT EXISTS radio_royalties (
id INT AUTO_INCREMENT PRIMARY KEY,
play_log_id INT NOT NULL,
station_id INT NOT NULL,
track_id INT NOT NULL,
artist_id INT NOT NULL,
base_rate DECIMAL(10,4) NOT NULL,
play_count INT DEFAULT 1,
total_amount DECIMAL(10,2) NOT NULL,
platform_fee DECIMAL(10,2) DEFAULT 0,
artist_payout DECIMAL(10,2) NOT NULL,
payment_status ENUM('pending', 'calculated', 'paid', 'cancelled') DEFAULT 'pending',
payment_date DATE NULL,
payment_period_start DATE,
payment_period_end DATE,
calculated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
paid_at TIMESTAMP NULL,
FOREIGN KEY (play_log_id) REFERENCES radio_play_logs(id) ON DELETE CASCADE,
FOREIGN KEY (station_id) REFERENCES radio_stations(id) ON DELETE CASCADE,
FOREIGN KEY (track_id) REFERENCES music_tracks(id) ON DELETE CASCADE,
FOREIGN KEY (artist_id) REFERENCES users(id) ON DELETE CASCADE,
INDEX idx_artist_status (artist_id, payment_status),
INDEX idx_station_period (station_id, payment_period_start, payment_period_end),
INDEX idx_payment_status (payment_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
");
$success[] = "radio_royalties table created";
// ============================================
// 8. RADIO ANALYTICS TABLE
// ============================================
echo "Creating radio_analytics table...\n";
$pdo->exec("
CREATE TABLE IF NOT EXISTS radio_analytics (
id INT AUTO_INCREMENT PRIMARY KEY,
station_id INT NOT NULL,
track_id INT NOT NULL,
period_date DATE NOT NULL,
period_type ENUM('daily', 'weekly', 'monthly') DEFAULT 'daily',
play_count INT DEFAULT 0,
total_duration_played INT DEFAULT 0,
avg_listener_count INT DEFAULT 0,
peak_listener_count INT DEFAULT 0,
avg_time_of_day TIME,
requests INT DEFAULT 0,
skips INT DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (station_id) REFERENCES radio_stations(id) ON DELETE CASCADE,
FOREIGN KEY (track_id) REFERENCES music_tracks(id) ON DELETE CASCADE,
UNIQUE KEY unique_station_track_period (station_id, track_id, period_date, period_type),
INDEX idx_station_period (station_id, period_date),
INDEX idx_track_period (track_id, period_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
");
$success[] = "radio_analytics table created";
// ============================================
// 9. RADIO API LOGS TABLE
// ============================================
echo "Creating radio_api_logs table...\n";
$pdo->exec("
CREATE TABLE IF NOT EXISTS radio_api_logs (
id INT AUTO_INCREMENT PRIMARY KEY,
station_id INT NOT NULL,
api_key VARCHAR(255) NOT NULL,
endpoint VARCHAR(255) NOT NULL,
method VARCHAR(10) NOT NULL,
request_data JSON,
response_status INT,
response_time_ms INT,
ip_address VARCHAR(45),
user_agent TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (station_id) REFERENCES radio_stations(id) ON DELETE CASCADE,
INDEX idx_station_created (station_id, created_at),
INDEX idx_api_key (api_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
");
$success[] = "radio_api_logs table created";
// ============================================
// 10. MODIFY EXISTING TABLES
// ============================================
// Add radio fields to music_tracks
echo "Modifying music_tracks table...\n";
try {
$pdo->exec("ALTER TABLE music_tracks ADD COLUMN radio_enabled BOOLEAN DEFAULT TRUE");
$success[] = "Added radio_enabled to music_tracks";
} catch (PDOException $e) {
if (strpos($e->getMessage(), 'Duplicate column') === false) {
$errors[] = "Error adding radio_enabled: " . $e->getMessage();
}
}
try {
$pdo->exec("ALTER TABLE music_tracks ADD COLUMN radio_play_count INT DEFAULT 0");
$success[] = "Added radio_play_count to music_tracks";
} catch (PDOException $e) {
if (strpos($e->getMessage(), 'Duplicate column') === false) {
$errors[] = "Error adding radio_play_count: " . $e->getMessage();
}
}
try {
$pdo->exec("ALTER TABLE music_tracks ADD COLUMN radio_last_played TIMESTAMP NULL");
$success[] = "Added radio_last_played to music_tracks";
} catch (PDOException $e) {
if (strpos($e->getMessage(), 'Duplicate column') === false) {
$errors[] = "Error adding radio_last_played: " . $e->getMessage();
}
}
try {
$pdo->exec("ALTER TABLE music_tracks ADD COLUMN radio_royalty_rate DECIMAL(10,4) DEFAULT 0.01");
$success[] = "Added radio_royalty_rate to music_tracks";
} catch (PDOException $e) {
if (strpos($e->getMessage(), 'Duplicate column') === false) {
$errors[] = "Error adding radio_royalty_rate: " . $e->getMessage();
}
}
try {
$pdo->exec("ALTER TABLE music_tracks ADD INDEX idx_radio_enabled (radio_enabled)");
$success[] = "Added index on radio_enabled";
} catch (PDOException $e) {
if (strpos($e->getMessage(), 'Duplicate key') === false) {
$errors[] = "Error adding index: " . $e->getMessage();
}
}
// Add radio fields to users (artists)
echo "Modifying users table...\n";
try {
$pdo->exec("ALTER TABLE users ADD COLUMN radio_total_plays INT DEFAULT 0");
$success[] = "Added radio_total_plays to users";
} catch (PDOException $e) {
if (strpos($e->getMessage(), 'Duplicate column') === false) {
$errors[] = "Error adding radio_total_plays: " . $e->getMessage();
}
}
try {
$pdo->exec("ALTER TABLE users ADD COLUMN radio_total_royalties DECIMAL(10,2) DEFAULT 0");
$success[] = "Added radio_total_royalties to users";
} catch (PDOException $e) {
if (strpos($e->getMessage(), 'Duplicate column') === false) {
$errors[] = "Error adding radio_total_royalties: " . $e->getMessage();
}
}
try {
$pdo->exec("ALTER TABLE users ADD COLUMN radio_royalties_paid DECIMAL(10,2) DEFAULT 0");
$success[] = "Added radio_royalties_paid to users";
} catch (PDOException $e) {
if (strpos($e->getMessage(), 'Duplicate column') === false) {
$errors[] = "Error adding radio_royalties_paid: " . $e->getMessage();
}
}
// Note: DDL statements can't be rolled back, so we don't use commit/rollback
// $pdo->commit();
echo "\n";
echo "========================================\n";
echo "MIGRATION COMPLETE\n";
echo "========================================\n\n";
echo "SUCCESS:\n";
foreach ($success as $msg) {
echo " ✓ $msg\n";
}
if (!empty($errors)) {
echo "\nWARNINGS:\n";
foreach ($errors as $msg) {
echo " ⚠ $msg\n";
}
}
echo "\n";
echo "All radio station licensing tables have been created successfully!\n";
echo "You can now begin implementing the radio station features.\n";
} catch (Exception $e) {
// Check if transaction is active before trying to rollback
// Note: DDL statements can't be rolled back anyway
try {
if ($pdo->inTransaction()) {
$pdo->rollBack();
}
} catch (PDOException $rollbackError) {
// Ignore rollback errors - DDL can't be rolled back
}
echo "\n";
echo "========================================\n";
echo "MIGRATION FAILED\n";
echo "========================================\n\n";
echo "Error: " . $e->getMessage() . "\n";
echo "Stack trace:\n" . $e->getTraceAsString() . "\n";
exit(1);
}
echo "</pre>\n";