CREATE TABLE IF NOT EXISTS users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 role ENUM('listener','artist','pro_artist','moderator','admin','super_admin') NOT NULL DEFAULT 'listener',
 name VARCHAR(120) NOT NULL,email VARCHAR(190) NOT NULL UNIQUE,password_hash VARCHAR(255) NOT NULL,
 avatar VARCHAR(255) NULL,cover_image VARCHAR(255) NULL,bio TEXT NULL,language CHAR(2) NOT NULL DEFAULT 'en',
 status ENUM('active','pending','blocked') NOT NULL DEFAULT 'pending',email_verified_at DATETIME NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS email_verifications(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,token_hash CHAR(64) NOT NULL UNIQUE,expires_at DATETIME NOT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,INDEX(user_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS settings(setting_key VARCHAR(120) PRIMARY KEY,setting_value TEXT NULL,autoload TINYINT(1) NOT NULL DEFAULT 1) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS storage_connections(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NULL,provider ENUM('local','google_drive','songbox','soundcloud','s3','r2','onedrive','dropbox') NOT NULL DEFAULT 'local',display_name VARCHAR(120) NOT NULL,config_json LONGTEXT NULL,is_default TINYINT(1) NOT NULL DEFAULT 0,is_active TINYINT(1) NOT NULL DEFAULT 1,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,INDEX(user_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS genres(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,parent_id INT UNSIGNED NULL,slug VARCHAR(100) NOT NULL UNIQUE,name VARCHAR(120) NOT NULL,is_active TINYINT(1) NOT NULL DEFAULT 1,sort_order INT NOT NULL DEFAULT 0) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS tracks(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,genre_id INT UNSIGNED NULL,title VARCHAR(190) NOT NULL,artist_name VARCHAR(190) NOT NULL,album_name VARCHAR(190) NULL,description TEXT NULL,cover_image VARCHAR(255) NULL,storage_provider VARCHAR(50) NOT NULL DEFAULT 'local',storage_reference TEXT NULL,duration_seconds INT UNSIGNED NOT NULL DEFAULT 0,audio_quality VARCHAR(50) NULL,status ENUM('draft','published','hidden','pending') NOT NULL DEFAULT 'draft',play_count BIGINT UNSIGNED NOT NULL DEFAULT 0,like_count BIGINT UNSIGNED NOT NULL DEFAULT 0,dislike_count BIGINT UNSIGNED NOT NULL DEFAULT 0,share_count BIGINT UNSIGNED NOT NULL DEFAULT 0,rank_score DECIMAL(18,2) NOT NULL DEFAULT 0.00,rank_updated_at DATETIME NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,INDEX(user_id),INDEX(genre_id),INDEX(status),INDEX(rank_score)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS playlists(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NULL,title VARCHAR(190) NOT NULL,slug VARCHAR(190) NOT NULL UNIQUE,description TEXT NULL,cover_image VARCHAR(255) NULL,visibility ENUM('public','unlisted','private') NOT NULL DEFAULT 'public',is_system TINYINT(1) NOT NULL DEFAULT 0,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS playlist_tracks(playlist_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(playlist_id,track_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS reactions(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,track_id BIGINT UNSIGNED NOT NULL,reaction ENUM('like','dislike') NOT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,UNIQUE KEY uniq_user_track(user_id,track_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS promotions(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,track_id BIGINT UNSIGNED NULL,type ENUM('highlight','featured_track','top_songs_featured','radio_boost','banner') NOT NULL,price DECIMAL(10,2) NOT NULL DEFAULT 0.00,currency CHAR(3) NOT NULL DEFAULT 'EUR',status ENUM('pending','paid','active','expired','cancelled') NOT NULL DEFAULT 'pending',starts_at DATETIME NULL,ends_at DATETIME NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS memberships(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(120) NOT NULL,slug VARCHAR(120) UNIQUE,price DECIMAL(10,2) NOT NULL DEFAULT 0,currency CHAR(3) NOT NULL DEFAULT 'EUR',billing ENUM('monthly','yearly','lifetime') NOT NULL DEFAULT 'monthly',is_active TINYINT(1) NOT NULL DEFAULT 1,features_json LONGTEXT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS user_memberships(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,membership_id BIGINT UNSIGNED NOT NULL,status ENUM('active','cancelled','expired','pending') NOT NULL DEFAULT 'pending',provider VARCHAR(30) NULL,provider_reference VARCHAR(190) NULL,starts_at DATETIME NULL,ends_at DATETIME NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,INDEX(user_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS ads(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,title VARCHAR(190) NOT NULL,type ENUM('banner','html','sponsor') NOT NULL DEFAULT 'banner',placement VARCHAR(100) NOT NULL,target_url TEXT NULL,image_url TEXT NULL,html_code MEDIUMTEXT NULL,is_active TINYINT(1) NOT NULL DEFAULT 1,starts_at DATETIME NULL,ends_at DATETIME NULL,impressions BIGINT UNSIGNED NOT NULL DEFAULT 0,clicks BIGINT UNSIGNED NOT NULL DEFAULT 0,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
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;