-- Database structuur voor het radioshow systeem

-- Gebruikers tabel
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('radiomaker', 'radiostation') NOT NULL,
    biography TEXT,
    demo_url VARCHAR(255) DEFAULT NULL,
    notification_sound BOOLEAN DEFAULT TRUE,
    is_approved BOOLEAN DEFAULT FALSE,
    approval_token VARCHAR(64) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Shows tabel (voor de show zelf)
CREATE TABLE shows (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Afleveringen tabel (voor individuele uploads)
CREATE TABLE episodes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    show_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    episode_number INT,
    total_parts INT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (show_id) REFERENCES shows(id) ON DELETE CASCADE
);

-- Aflevering delen tabel
CREATE TABLE episode_parts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    episode_id INT NOT NULL,
    part_number INT NOT NULL,
    filename VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (episode_id) REFERENCES episodes(id) ON DELETE CASCADE,
    UNIQUE KEY unique_part (episode_id, part_number)
);

-- Show aanmeldingen tabel
CREATE TABLE show_signups (
    show_id INT NOT NULL,
    station_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (show_id, station_id),
    FOREIGN KEY (show_id) REFERENCES shows(id) ON DELETE CASCADE,
    FOREIGN KEY (station_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Download logs tabel
CREATE TABLE download_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    episode_id INT NOT NULL,
    part_id INT NOT NULL,
    station_id INT NOT NULL,
    downloaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (episode_id) REFERENCES episodes(id) ON DELETE CASCADE,
    FOREIGN KEY (part_id) REFERENCES episode_parts(id) ON DELETE CASCADE,
    FOREIGN KEY (station_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Likes tabel voor afleveringen
CREATE TABLE episode_likes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    episode_id INT NOT NULL,
    station_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (episode_id) REFERENCES episodes(id) ON DELETE CASCADE,
    FOREIGN KEY (station_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_like (episode_id, station_id)
);

-- Likes tabel voor shows
CREATE TABLE show_likes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    show_id INT NOT NULL,
    station_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (show_id) REFERENCES shows(id) ON DELETE CASCADE,
    FOREIGN KEY (station_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_like (show_id, station_id)
);

-- Notificaties tabel
CREATE TABLE notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    type VARCHAR(50) NOT NULL,
    content_id INT NOT NULL,
    station_id INT NOT NULL,
    message TEXT NOT NULL,
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (station_id) REFERENCES users(id) ON DELETE CASCADE
); 