ALTER TABLE users
    ADD COLUMN IF NOT EXISTS storage_quota_gb decimal(8,2) NOT NULL DEFAULT 2.00 AFTER selected_radio_id;

CREATE TABLE IF NOT EXISTS media_categories (
    id int(11) NOT NULL AUTO_INCREMENT,
    user_id int(11) NOT NULL,
    radio_id int(11) NULL,
    scope enum('user','radio') NOT NULL DEFAULT 'radio',
    name varchar(120) NOT NULL,
    slug varchar(140) NOT NULL,
    color varchar(20) NOT NULL DEFAULT '#8ea2ff',
    content_type enum('music','jingle','bed','soundboard','voice_track','podcast','liner','top_horaire','chronique','spot') NOT NULL DEFAULT 'music',
    is_default tinyint(1) NOT NULL DEFAULT 0,
    cue_out_seconds decimal(6,2) NOT NULL DEFAULT 0.00,
    cross_seconds decimal(6,2) NOT NULL DEFAULT 0.00,
    fade_in_seconds decimal(6,2) NOT NULL DEFAULT 0.00,
    fade_out_seconds decimal(6,2) NOT NULL DEFAULT 0.00,
    created_at datetime NOT NULL,
    updated_at datetime NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_media_category_radio_slug (user_id, radio_id, slug),
    KEY idx_media_categories_user_radio (user_id, radio_id),
    KEY idx_media_categories_scope (user_id, scope, radio_id, content_type),
    KEY idx_media_categories_type (content_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS media_files (
    id int(11) NOT NULL AUTO_INCREMENT,
    user_id int(11) NOT NULL,
    radio_id int(11) NULL,
    scope enum('user','radio') NOT NULL DEFAULT 'radio',
    category_id int(11) NULL,
    content_type enum('music','jingle','bed','soundboard','voice_track','podcast','liner','top_horaire','chronique','spot') NOT NULL DEFAULT 'music',
    title varchar(190) NOT NULL,
    artist varchar(190) NOT NULL,
    album varchar(190) NULL,
    genre varchar(120) NULL,
    duration_seconds int(11) NULL,
    original_filename varchar(255) NOT NULL,
    stored_filename varchar(255) NOT NULL,
    relative_path varchar(255) NOT NULL,
    cover_path varchar(255) NULL,
    mime_type varchar(120) NOT NULL,
    size_bytes bigint(20) NOT NULL DEFAULT 0,
    checksum char(64) NULL,
    created_at datetime NOT NULL,
    updated_at datetime NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_media_stored_filename (stored_filename),
    KEY idx_media_files_user_radio (user_id, radio_id),
    KEY idx_media_files_scope (user_id, scope, radio_id, content_type),
    KEY idx_media_files_category (category_id),
    KEY idx_media_files_content_type (content_type),
    KEY idx_media_files_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
