CREATE TABLE IF NOT EXISTS radio_stations(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 name VARCHAR(190) NOT NULL,
 slug VARCHAR(190) NOT NULL UNIQUE,
 description TEXT NULL,
 genre VARCHAR(120) NULL,
 logo_image VARCHAR(255) NULL,
 stream_url TEXT NULL,
 stream_type ENUM('autodj','icecast','shoutcast','hls','mp3','aac','other') NOT NULL DEFAULT 'autodj',
 visibility ENUM('public','unlisted','private') NOT NULL DEFAULT 'public',
 status ENUM('active','offline','pending','blocked') NOT NULL DEFAULT 'pending',
 payment_status ENUM('pending','paid','waived','refunded') NOT NULL DEFAULT 'pending',
 price_paid DECIMAL(10,2) NOT NULL DEFAULT 0.00,
 currency CHAR(3) NOT NULL DEFAULT 'EUR',
 listener_count INT UNSIGNED NOT NULL DEFAULT 0,
 is_featured TINYINT(1) NOT NULL DEFAULT 0,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 approved_at DATETIME NULL,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 INDEX(user_id),INDEX(status),INDEX(is_featured),INDEX(payment_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS radio_station_tracks(
 station_id BIGINT UNSIGNED NOT NULL,
 track_id BIGINT UNSIGNED NOT NULL,
 weight INT UNSIGNED NOT NULL DEFAULT 10,
 sort_order INT NOT NULL DEFAULT 0,
 added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(station_id,track_id),
 INDEX(track_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payment_transactions(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 purpose VARCHAR(80) NOT NULL,
 reference_id BIGINT UNSIGNED NULL,
 provider VARCHAR(30) NOT NULL DEFAULT 'paypal',
 provider_reference VARCHAR(190) NULL,
 amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
 currency CHAR(3) NOT NULL DEFAULT 'EUR',
 status ENUM('pending','paid','failed','refunded','cancelled') NOT NULL DEFAULT 'pending',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 paid_at DATETIME NULL,
 INDEX(user_id),INDEX(purpose,reference_id),INDEX(status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO settings(setting_key,setting_value) VALUES
('default_theme','dark'),('brand_logo',''),('brand_icon',''),('front_banner',''),('admin_banner',''),
('radio_station_price','9.90'),('radio_station_currency','EUR'),('radio_station_requires_approval','1'),('radio_station_autodj_enabled','1')
ON DUPLICATE KEY UPDATE setting_value=setting_value;

CREATE TABLE IF NOT EXISTS site_daily_stats(
 stat_date DATE PRIMARY KEY,
 page_views BIGINT UNSIGNED NOT NULL DEFAULT 0,
 unique_visitors BIGINT UNSIGNED NOT NULL DEFAULT 0,
 session_starts BIGINT UNSIGNED NOT NULL DEFAULT 0,
 track_plays BIGINT UNSIGNED NOT NULL DEFAULT 0,
 radio_starts BIGINT UNSIGNED NOT NULL DEFAULT 0,
 jukebox_starts BIGINT UNSIGNED NOT NULL DEFAULT 0,
 uploads BIGINT UNSIGNED NOT NULL DEFAULT 0,
 registrations BIGINT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS jukebox_queue(
 user_id BIGINT UNSIGNED NOT NULL,
 track_id BIGINT UNSIGNED NOT NULL,
 sort_order INT NOT NULL DEFAULT 0,
 added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(user_id,track_id),
 INDEX(track_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO settings(setting_key,setting_value) VALUES
('stats_enabled','1'),('front_banner_enabled','1'),('admin_banner_enabled','1')
ON DUPLICATE KEY UPDATE setting_value=setting_value;

CREATE TABLE IF NOT EXISTS listening_events(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NULL,
 track_id BIGINT UNSIGNED NOT NULL,
 event_type ENUM('play','like','dislike','playlist_add','jukebox_add','share','skip') NOT NULL,
 event_weight DECIMAL(8,2) NOT NULL DEFAULT 1.00,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX(user_id),INDEX(track_id),INDEX(event_type),INDEX(created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO settings(setting_key,setting_value) VALUES
('recommendations_enabled','1'),('recommendation_discovery_percent','15'),
('random_playlist_boost_enabled','1'),('random_playlist_boost_count','1'),('random_playlist_boost_minutes','60')
ON DUPLICATE KEY UPDATE setting_value=setting_value;