CREATE TABLE IF NOT EXISTS radios (
    id int(11) NOT NULL AUTO_INCREMENT,
    user_id int(11) NOT NULL,
    name varchar(120) NOT NULL,
    slug varchar(160) NOT NULL,
    logo varchar(255) NULL,
    timezone varchar(80) NOT NULL DEFAULT 'Europe/Paris',
    stream_key varchar(80) NOT NULL,
    player_key varchar(80) NOT NULL,
    stream_url varchar(255) NULL,
    stream_mount varchar(120) NULL,
    provision_status varchar(40) NOT NULL DEFAULT 'local',
    provision_error text NULL,
    setup_status enum('created','active') NOT NULL DEFAULT 'created',
    created_at datetime NOT NULL,
    updated_at datetime NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_radios_name (name),
    UNIQUE KEY uniq_radios_slug (slug),
    UNIQUE KEY uniq_radios_user_slug (user_id, slug),
    UNIQUE KEY uniq_radios_stream_key (stream_key),
    UNIQUE KEY uniq_radios_player_key (player_key),
    KEY idx_radios_user_id (user_id),
    KEY idx_radios_setup_status (setup_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS radio_playlists (
    id int(11) NOT NULL AUTO_INCREMENT,
    radio_id int(11) NOT NULL,
    name varchar(160) NOT NULL,
    description text NULL,
    cover_path varchar(255) NULL,
    type varchar(60) NOT NULL DEFAULT 'manual',
    is_default tinyint(1) NOT NULL DEFAULT 0,
    is_active tinyint(1) NOT NULL DEFAULT 1,
    last_published_at datetime NULL,
    created_at datetime NOT NULL,
    updated_at datetime NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_radio_playlist_name (radio_id, name),
    KEY idx_radio_playlists_radio_id (radio_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS radio_playlist_items (
    id int(11) NOT NULL AUTO_INCREMENT,
    playlist_id int(11) NOT NULL,
    media_id int(11) NOT NULL,
    position int(11) NOT NULL DEFAULT 0,
    created_at datetime NOT NULL,
    updated_at datetime NOT NULL,
    PRIMARY KEY (id),
    KEY idx_radio_playlist_items_playlist (playlist_id),
    KEY idx_radio_playlist_items_media (media_id),
    KEY idx_radio_playlist_items_position (playlist_id, position)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS radio_schedule_slots (
    id int(11) NOT NULL AUTO_INCREMENT,
    radio_id int(11) NOT NULL,
    playlist_id int(11) NOT NULL,
    day_of_week tinyint NOT NULL,
    start_minute smallint NOT NULL,
    end_minute smallint NOT NULL,
    color varchar(20) NOT NULL DEFAULT '#77d09b',
    takeover_mode varchar(40) NOT NULL DEFAULT 'finish_current',
    created_at datetime NOT NULL,
    updated_at datetime NOT NULL,
    PRIMARY KEY (id),
    KEY idx_radio_day_time (radio_id, day_of_week, start_minute, end_minute),
    KEY idx_playlist (playlist_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS radio_schedule_runtime (
    radio_id int(11) NOT NULL,
    active_key varchar(160) NULL,
    active_slot_id int(11) NULL,
    active_playlist_id int(11) NULL,
    active_source varchar(40) NULL,
    last_status varchar(40) NULL,
    last_message text NULL,
    last_run_at datetime NULL,
    last_switch_at datetime NULL,
    created_at datetime NOT NULL,
    updated_at datetime NOT NULL,
    PRIMARY KEY (radio_id),
    KEY idx_active_slot (active_slot_id),
    KEY idx_active_playlist (active_playlist_id),
    KEY idx_last_run (last_run_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS radio_audio_settings (
    id int(11) NOT NULL AUTO_INCREMENT,
    radio_id int(11) NOT NULL,
    no_repeat_artist_minutes int(11) NOT NULL DEFAULT 15,
    no_repeat_title_minutes int(11) NOT NULL DEFAULT 60,
    apply_mode varchar(60) NOT NULL DEFAULT 'next_restart',
    created_at datetime NOT NULL,
    updated_at datetime NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_radio_audio_settings_radio_id (radio_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
